Countif в Excel с несколькими критериями

Question

У меня есть индекс состояния правопорядка во многих странах за несколько лет. Для каждой страны я хочу выяснить, сколько стран впереди и сколько стран отстают на данный год. На рисунке ниже я попытался показать, что я хочу сделать. Я вручную сделал расчет для Албании (ALB). Из четырех стран в выборке 3 страны опередили Албанию в 1995 году, и ни одна не оказалась ниже нее. Аналогичным образом, в 1996 году 2 страны были выше Албании, а 1 страна - ниже. То же самое касается 1997 и 1998 годов.

Проблема здесь в том, что у меня 134 страны и 16 лет. У меня уйдут недели, чтобы сделать это вручную. Я попробовал следующую формулу в MS Excel безуспешно:

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

Есть идеи, как я могу это сделать? Я открыт для предложений. Я одновременно использую MS Excel и Stata, поэтому любая платформа будет работать для меня.

Answer 1

Один путь в Стате:

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)

Некоторые предположения сделаны, конечно.

редактировать

Стратегия действительно проста. Сортировать данные по year law . Результат:

. 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 |
     +-----------------------+

Теперь нам нужно только осознать, что в каждой year группе первое наблюдение (т.е. наблюдения 1, 4 и 7) опережает два других наблюдения (т. Е. Obs 1 опережает 2, 3; obs 4 опережает 5, 6 и obs 7 опережает 8, 9). Это означает, что есть два наблюдения, которые ahead наблюдения 1, 4, 7.

Далее, за каждый year группе второе наблюдение (то есть наблюдения 2, 5 и 8) перегнать один другого наблюдения. Это означает, что ahead наблюдениями 2, 5, 8 стоит одно наблюдение.

Наконец, для каждой year группы третье наблюдение (т.е. наблюдения 3, 6 и 9) опережает ноль других наблюдений. Это означает, что ahead наблюдениями 3, 6, 9 наблюдаются нулевые наблюдения.

Итак, теперь мы видим, что после соответствующей sort данных по year группе нам просто нужно создать последовательность два, один, ноль, для переменной ahead . Это можно сделать несколькими способами. Я решил сделать это, используя подписку (см. Справку по help subscripting) и системные переменные _n и _N (см. help _variables). _N - общее количество наблюдений на группу (3), а _n - текущее наблюдение на группу. Пример: для наблюдения 1 вычисление составляет 3-1 = 2 . Для наблюдения 2 это 3-2 = 1 и так далее.

behind переменная вычисляется аналогично.

Результат:

     +----------------------------------------+
     | 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 |
     +----------------------------------------+

В моем коде после вычисления новых переменных я снова sort чтобы представить данные в исходном порядке сортировки (год country year).

Answer 2

В Excel COUNTIFS (с "S" в конце) можно использовать для подсчета с несколькими условиями, поэтому попробуйте эту формулу в D2 скопировать вниз

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

Для E2 просто поверните <to>

Answer 3

Просто отсортируйте по году, а затем примените формулу 16 раз - формула может выглядеть следующим образом: =COUNTIF($C$2:$C$13,">"&C2) где 'C2-C13 is range of values for each year and C2` это значение страны (за год), которое необходимо сравнить с. Я думаю, что это не займет много времени, может быть, 1 минута для страны.

Answer 4

Я использую $ i = 1, ..., n = 164 $ для подсчета стран, $ t = 1, ..., 16 $ для подсчета лет.

Обозначим $ s_ {it} $ оценку страны $ i $ в году $ t $. Обозначим также $\alpha_ {it} $ количество стран, которые опережают страну $ i $ в год $ t $, и $\beta_ {it} $ количество стран, которые находятся позади страны $ i $ в год $ t $.

Очевидно (при условии отсутствия связей, по которым вам придется принимать решение),

$$ \alpha_ {it}+\beta_ {it} = N-1, \; \; \forall t $$ Наивный путь:

1) Отдельные (в Excel)$ 16 $ наблюдения, которые вы имеете, получая $ 16 $ серии поперечных сечений по $ 164 $ сечения каждая. Сохраняйте идентификатор для каждого значения в каждом из $ 16 $ векторов, независимо от того, как они у вас есть (трехбуквенный идентификатор страны, год)

2) Сортировать каждый из $ 16 $ векторов по величине, от наименьшей оценки к наивысшей оценке 3) Справа или слева от каждого из $ 16 $ отсортированных векторов создайте серию $ {1,2,3, ..., 164} $

Таким образом, наименьшая оценка $ s_ {it} $ связана с числом $ 1 $, а наивысшая - с $ 164 $.

Вы только что оценили каждую страну на основе ее оценки за каждый год, обозначив рейтинг $ r_ {it} $.

затем

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

Как вы думаете, сколько времени это займет у вас?