☰

使用 IF 和 AND 函數計算具有多個條件的跨 ARRAY 的 Excel MEDIAN

tag-icon • tag-icon microsoft-excel array microsoft-excel-365 if
使用 IF 和 AND 函數計算具有多個條件的跨 ARRAY 的 Excel MEDIAN

我想根據滿足條件來確定一列中位數的中位數二其他欄位。我正在使用 MS Excel。

我知道我必須使用 IF 函數將 MEDIAN 函數作為 ARRAY (ctrl + shft + Enter) 進行處理，如果我在 IF 語句中只使用 1 個條件，則效果很好。然而，一旦我嘗試合併2使用 AND 函數的條件，我得到的中位數為 0（即，它不起作用）。

如何根據多於一列的匹配條件計算一列中位數的中位數？

例子:

假設我想找出當 A = 1 且 B = x 時 C 中位數的中位數：

A   B   C
1   x   10
1   x   20
1   y   30
1   y   40
2   x   10
2   x   20
2   y   30
2   y   40
3   x   10
3   x   20
3   y   30
3   y   40
4   x   10
4   x   20
4   y   30
4   y   40
5   x   10
5   x   20
5   y   30
5   y   40

#Here is my attempt using IF and AND:
{=MEDIAN(IF(AND(A2:A21=1,B2:B21="x"),C2:C21))}    #DEOSN'T WORK: should be 15, but it's 0

#subcomponents of above work fine on their own:
{=MEDIAN(IF(A2:A21=1,C2:C21)) }    #equals 25 as expected
{=MEDIAN(IF(B2:B21="x",C2:C21))}   #equals 15 as expected

我該如何進行這項工作和/或我應該使用另一種方​​法？

答案1

避免使用該AND函數，IF而使用巢狀。

具體來說：

=MEDIAN(IF(A2:A21=1,IF(B2:B21="x",C2:C21)))

問題是，它AND在數組上下文中無法按您的預期工作 - 它不會將AND每對元素生成一個數組，而是將兩個數組的所有元素進行“與”操作以給出單個標量結果。

AND您的原始公式正在評估對「」的單一輸出的整個呼叫FALSE（因為並非兩個陣列中的每個元素都滿足比較）。

答案2

我不知道為什麼它有效，但經過一番折騰後，我發現以下內容實現了我的目標：

{=MEDIAN(IF((A2:A21=1)*(B2:B21="x"),C2:C21))}   #gives correct 15

（再次，記住使用 ctrl + shft + Enter 將其作為數組處理）

我的猜測是，該AND函數預設僅生成一個TRUE或FALSE，因此無法在這種情況下工作（這需要生成 1 和 0 的陣列）。但是，如果我將每一列作為它自己的陣列進行處理，我將為每個子條件產生一個由 1 和 0 組成的字串。如果我隨後將多個條件數組相乘，則結果本身就是由 1 和 0 組成的數組，其中僅當所需條件對於兩個相應的子條件都為 true 時才存在。

我確信有人可以在評論中確認/解釋這一點。

相關內容