☰

Excel 中的 Countif 具有多个条件

tag-icon • tag-icon microsoft-excel
Excel 中的 Countif 具有多个条件

我有一个指数，记录了多年来许多国家的法律和秩序状况。对于每个国家，我想找出在给定的一年中，有多少国家领先，有多少国家落后。在下面的图片中，我试图展示我想要做的事情。我手动计算了阿尔巴尼亚 (ALB)。在样本中的四个国家中，1995 年有 3 个国家领先于阿尔巴尼亚，没有一个国家低于它。同样，在 1996 年，有两个国家高于阿尔巴尼亚，一个国家低于它。1997 年和 1998 年也是如此。

问题是我有 134 个国家和 16 年的数据。手动操作需要花费数周时间。我在 MS Excel 中尝试了以下公式，但没有成功：

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

有什么想法可以让我做到这一点吗？我愿意听取建议。我同时使用 MS Excel 和 Stata，因此任何平台都可以使用。

我的数据集的简化示例以及我想要实现的目标

答案1

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)

当然，我们做出了一些假设。

编辑

策略其实很简单，按 排序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）落后于另外两个观测值（即观测值 1 落后于观测值 2、3；观测值 4 落后于观测值 5、6；观测值 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）。

答案2

在 Excel 中COUNTIFS（末尾带有“S”）可用于计算多个条件，因此请尝试在 D2 中复制此公式

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

对于 E2，只需反转 < 至 >

答案3

只需按年份排序，然后应用公式 16 次 - 公式可能看起来像：=COUNTIF($C$2:$C$13,">"&C2)其中“C2-C13 is range of values for each year andC2”是需要与之比较的国家/地区（年份）值。我认为这不会花很长时间，对于国家/地区来说可能只需 1 分钟。

答案4

我使用 $i=1,...,n=164$ 来计数国家，使用 $t=1,...,16$ 来计数年份。

用 $s_{it}$ 表示国家 $i$ 在 $t$ 年的得分。用 $\alpha_{it}$ 表示在 $t$ 年领先于国家 $i$ 的国家数量，用 $\beta_{it}$ 表示在 $t$ 年落后于国家 $i$ 的国家数量。

显然（假设没有关系，你必须做出决定），

$$ \alpha_{it}+\beta_{it}= N-1,\;\; \forall t$$ 简单的方法：

1）分离（在 excel 中）您拥有的 $16$ 个观测值，获得 $16$ 个横截面系列，每个系列有 $164$ 个横截面。保留每个 $16$ 个向量中每个值的 ID，无论它们以何种方式存在（三个字母的国家 ID、年份）

2）将 16 个向量按大小排序，从最低得分最高分数 3）在每个 $16$ 个排序向量的右侧或左侧创建序列 ${1,2,3,...,164}$

因此最低分数 $s_{it}$ 与数字 $1$ 相关联，最高分数与数字 $164$ 相关联。

你刚刚根据每年的得分对每个国家进行了排名，表示排名$r_{it}$。

然后

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

您认为这需要花多长时间？

相关内容