☰

Excel 中具有多個條件的 Countif

tag-icon • tag-icon microsoft-excel
Excel 中具有多個條件的 Countif

我有一個關於許多國家多年來法律和秩序狀況的指數。對於每個國家，我想了解某一年有多少國家領先，有多少國家落後。在下面所附的圖片中，我試著展示我想做的事情。我手動計算了阿爾巴尼亞 (ALB)。在樣本中的四個國家中，1995 年有 3 個國家領先阿爾巴尼亞，沒有一個國家低於阿爾巴尼亞。同樣，1996 年，有 2 個國家高於阿爾巴尼亞，1 個國家低於阿爾巴尼亞。 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）落後於其他兩個觀測值（即obs 1 落後於2、3；obs 4 落後於5、 6；等等）。這意味著有二觀察結果ahead1、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 $$

您認為這需要多長時間？

相關內容