Countif no Excel com vários critérios

Question

Eu tenho um índice para as condições de lei e ordem em muitos países ao longo de vários anos. Para cada país, quero descobrir quantos países estão à frente e quantos países estão atrasados para um determinado ano. Na imagem anexada abaixo, tentei mostrar o que quero fazer. Eu manualmente fiz o cálculo para a Albânia (ALB). Do total de quatro países da amostra, 3 países estavam à frente da Albânia em 1995 e nenhum estava abaixo dela. Da mesma forma, em 1996, dois países estavam acima da Albânia e um país estava abaixo dela. O mesmo vale para 1997 e 1998.

O problema aqui é que eu tenho 134 países e 16 anos. Vou levar semanas para fazer isso manualmente. Eu tentei a seguinte fórmula no MS Excel sem sucesso:

=IF($B:$B=B2,COUNTIF($C$2:$C$17,"<"&C2))

Alguma idéia de como posso fazer isso? Estou aberto a sugestões. Estou usando simultaneamente o MS Excel e o Stata para que qualquer plataforma funcione para mim.

    

Answer 1

Um caminho no Stata:

clear all
set more off

*----- example data -----

input ///
country year law
1 1996 235
1 1997 25
1 1998 6445
2 1996 436
2 1997 2356
2 1998 224
3 1996 3129
3 1997 735
3 1998 836
end

list, sepby(country)

*----- what you want -----

sort year law
by year: gen ahead = _N - _n
by year: gen behind = _n - 1

sort country year
list, sepby(country)

Algumas suposições são feitas, é claro.

Editar

A estratégia é muito simples. Classifique os dados por year law . O resultado é:

. list, sepby(year)

     +-----------------------+
     | country   year    law |
     |-----------------------|
  1. |       1   1996    235 |
  2. |       2   1996    436 |
  3. |       3   1996   3129 |
     |-----------------------|
  4. |       1   1997     25 |
  5. |       3   1997    735 |
  6. |       2   1997   2356 |
     |-----------------------|
  7. |       2   1998    224 |
  8. |       3   1998    836 |
  9. |       1   1998   6445 |
     +-----------------------+

Agora só precisamos perceber que, para cada grupo year , a primeira observação (ie observações 1, 4 e 7) é superada por duas outras observações (ie obs 1 é ultrapassada por 2, 3; obs 4 é ultrapassado por 5, 6 e obs 7 é superado por 8, 9). Isto significa que existem duas observações que são ahead das observações 1, 4, 7.

Em seguida, por cada grupo year , a segunda observação (ou seja, observações 2, 5 e 8) é superada por uma outra observação. Isto significa que há uma observação ahead das observações 2, 5, 8.

Finalmente, por cada grupo year , a terceira observação (ou seja, observações 3, 6 e 9) é superada por zero outras observações. Isso significa que há zero observações ahead das observações 3, 6, 9.

Agora, vemos que, depois de apropriadamente sort ing os dados, por year group, precisamos apenas criar a sequência two, one, zero , para a variável ahead . Isto pode ser feito de várias maneiras. Eu escolhi fazê-lo usando subscrição (consulte help subscripting ) e as variáveis do sistema _n e _N (consulte help _variables ). _N é o número total de observações por grupo (3) e _n é a observação atual por grupo. Um exemplo: para a observação 1, o cálculo é 3-1 = 2 . Para a observação 2, é 3-2 = 1 e assim por diante.

A variável behind é calculada analogamente.

O resultado é:

     +----------------------------------------+
     | country   year    law   ahead   behind |
     |----------------------------------------|
  1. |       1   1996    235       2        0 |
  2. |       2   1996    436       1        1 |
  3. |       3   1996   3129       0        2 |
     |----------------------------------------|
  4. |       1   1997     25       2        0 |
  5. |       3   1997    735       1        1 |
  6. |       2   1997   2356       0        2 |
     |----------------------------------------|
  7. |       2   1998    224       2        0 |
  8. |       3   1998    836       1        1 |
  9. |       1   1998   6445       0        2 |
     +----------------------------------------+

No meu código, depois de calcular as novas variáveis, eu sort apenas para apresentar os dados em sua ordem de classificação original ( country year ).

    

Answer 2

No Excel, COUNTIFS (com um "S" no final) pode ser usado para contar com várias condições, portanto, tente essa fórmula em D2 copiada para baixo

=COUNTIFS(B:B,B2,C:C,">"&C2)

Para E2, basta inverter o < para >

    

Answer 3

Apenas classifique por ano e aplique a fórmula 16 vezes - a fórmula pode se parecer com: =COUNTIF($C$2:$C$13,">"&C2) onde 'C2-C13 is range of values for each year and C2' é o valor do país (para o ano) que precisa ser comparado. Eu acho que não demoraria muito pode ser de 1 minuto para o país.

    

Answer 4

Eu uso $ i = 1, ..., n = 164 $ para contar os países, $ t = 1, ..., 16 $ para contar os anos.

Denote $ s_ {it} $ a pontuação do país $ i $ no ano $ t $. Indique também $ \ alpha_ {it} $ o número de países que estão à frente de cuntry $ i $ no ano $ t $ e por $ \ beta_ {it} $ o número de países que estão atrás do país $ i $ no ano $ t $.

Obviamente (assumindo que não há vínculos, pelos quais você terá que tomar uma decisão),

$$ \ alpha_ {it} + \ beta_ {it} = N-1, \; \; \ forall t $$ O jeito ingênuo:

1) Separe (no excel), as observações de $ 16 $ que você tem, obtendo uma série de $ 16 $ de cortes transversais de $ 164 cada. Mantenha o ID para cada valor em cada um dos vetores de $ 16 $, da maneira que você os tiver (ID do país com três letras, ano)

2) Classifique cada um dos $ 16 $ vetores por magnitude, de menor para maior pontuação 3) À direita ou à esquerda de cada um dos vetores $ 16 $ classificados, crie a série $ {1,2,3, ..., 164} $

Portanto, a pontuação mais baixa $ s_ {it} $ está associada ao número $ 1 $ e a mais alta, ao número $ 164 $.

Você acabou de classificar cada país com base em sua pontuação para cada ano , indicando a classificação $ r_ {it} $.

Então

$$ \ alpha_ {it} = 164-r_ {it}, \; \; \; \ beta_ {it} = r_ {it} -1 $$

Quanto tempo você acha que vai levar você?