☰

Countif no Excel com vários critérios

tag-icon • tag-icon microsoft-excel
Countif no Excel com vários critérios

Tenho um índice das condições da lei e da 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 estão atrás num determinado ano. Na imagem anexa abaixo, tentei mostrar o que quero fazer. Fiz manualmente 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, 2 países estavam acima da Albânia e 1 país estava abaixo dela. O mesmo vale para 1997 e 1998.

O problema aqui é que tenho 134 países e 16 anos. Levarei semanas para fazer isso manualmente. 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 MS Excel e Stata, então qualquer plataforma funcionará para mim.

Exemplo simplificado do meu conjunto de dados e o que desejo alcançar

Responder1

Uma maneira 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 yeargrupo, a primeira observação (ou seja, observações 1, 4 e 7) é superada por duas outras observações (ou seja, obs 1 é ultrapassada por 2, 3; obs 4 é ultrapassada por 5, 6; e obs 7 é ultrapassado por 8, 9). Isto significa que existemdoisobservações que são aheaddas observações 1, 4, 7.

Em seguida, para cada yeargrupo, a segunda observação (isto é, observações 2, 5 e 8) é superada por uma outra observação. Isto significa que existeumobservação aheaddas observações 2, 5, 8.

Finalmente, para cada yeargrupo, a terceira observação (isto é, observações 3, 6 e 9) é superada por zero outras observações. Isto significa que existemzeroobservações aheaddas observações 3, 6, 9.

Então agora vemos que depois de sortinserir os dados apropriadamente, por yeargrupo, só precisamos criar a sequênciadois, um, zero, para a aheadvariável. Isto pode ser feito de várias maneiras. Eu escolhi fazer isso usando subscritos (veja help subscripting) e variáveis ​​de sistema _ne _N(veja 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 = 1e assim por diante.

A behindvariável é calculada de forma análoga.

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, após calcular as novas variáveis, sortvolto apenas para apresentar os dados em sua ordem de classificação original ( country year).

Responder2

No Excel COUNTIFS(com um “S” no final) pode ser usado para contar com múltiplas condições, então experimente esta fórmula em D2 copiada para baixo

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

Para E2 basta inverter o < para >

Responder3

Basta classificar por ano e aplicar a fórmula 16 vezes - a fórmula pode ser semelhante a: =COUNTIF($C$2:$C$13,">"&C2)onde 'C2-C13 is range of values for each year andC2` é o valor do país (para o ano) com o qual precisa ser comparado. Acho que não demoraria muito, talvez 1 minuto para o país.

Responder4

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$. Denote também $\alpha_{it}$ o número de países que estão à frente do país $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 (supondo que não haja vínculos, para os quais você terá que tomar uma decisão),

$$ \alpha_{it}+\beta_{it}= N-1,\;\; \forall t$$ A maneira ingênua:

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

2)Classifique cada um dos vetores $16$ por magnitude, demais baixopontuação paraAltíssimapontuação 3)À direita ou à esquerda de cada um dos vetores classificados $16$ crie a série ${1,2,3,...,164}$

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

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

Então

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

Quanto tempo você acha que vai demorar?

informação relacionada