在Excel中使用溢位公式時，COUNTIF無法在過濾的串列上正確計算

我正在使用COUNTIF來比較一個陣列和另一個陣列以計算頻率。其目的是創建一個列出事件頻率的溢位陣列。我的公式作業正常，但是當我試圖對另一列進行過濾時，公式回傳了#VALUE!錯誤。

下面是一個簡化的例子。

我和你正在一個表格中記錄誰贏得了比賽，贏家在Winner列中，所玩的游戲在Game列中。

=LET(
    KeyColumn, Table1[Winner],
    類別，SORT(UNIQUE(KeyColumn))。

    頻率，COUNTIF(KeyColumn,"="&Categories)。
    頻率
)

我看到你贏得更多，但我是個書呆子，所以體育不算數。我做了一個表格，其中有一列叫做Not Sports，并使用這個公式過濾了這個串列：

=LET(
    KeyColumn, Table1[Winner],
    FilterColumn,Table1[Game],
    FilterList, Table2[Not Sports],
    類別, SORT(UNIQUE(KeyColumn))。

    BoolFilterColumn, ISNUMBER(XMATCH(FilterColumn, FilterList))。
    FilteredKeyColumn, FILTER(KeyColumn,BoolFilterColumn),

    頻率，COUNTIF(FilteredKeyColumn,"="&類別)。
    頻率
)

我得到一個長度為FilteredKeyColumn的陣列，其中有#VALUE！錯誤。

我是不是做錯了什么？我怎樣才能解決這個問題呢？

注意：這只是我正在制作的一個公式的一部分，實際上我確實需要如圖所示的頻率陣列。

編輯：資料供參考

Table2

uj5u.com熱心網友回復：

你需要添加到countifs和轉置Not體育的串列，然后使用mmult使其成為一個1d陣列：

同樣通過添加CHOOSE，我們可以將獲勝者名單放在同一個公式中：

=LET(
    KeyColumn, Table1[Winner],
    FilterColumn,Table1[Game],
    FilterList, Table2[Not Sports],
    類別, SORT(UNIQUE(KeyColumn)),
    頻率, MMULT(COUNTIFS(FilterColumn,TRANSPOSE(FilterList),KeyColumn,Categories),SEQUENCE(COUNTA(FilterList),,1,0)) 。
    選擇({1,2},類別,頻率)
)

關于為什么會出現錯誤的說明，你正在向COUNTIFS傳遞一個陣列，而它只接受一個范圍。 這就是錯誤的原因。

uj5u.com熱心網友回復：

Scott Craner的答案很有效。在我的實際使用中，我需要將其擴展到多個過濾器標準。下面是一個受Scott啟發的公式：

=LET(
    KeyColumn, Table1[Winner],
    FilterColumn1,Table1[Game],
    FilterList1, Table2[Not Sports],

    Categories, SORT(UNIQUE(KeyColumn)),
    Bool2DKey, TRANSPOSE(KeyColumn)=Categories,
    Mask1, TRANSPOSE(ISNUMBER(XMATCH(FilterColumn1,FilterList1))。
    Bool2DMasked, Bool2DKey*Mask1,
    OnesArray, SEQUENCE(ROWS(KeyColumn),1,1,0),

    頻率, MMULT(Bool2DMasked,OnesArray),
    頻率
)

它創建了一個陣列Bool2DKey，它的高度是Categories的高度Table1，充滿了表示該列（表行）中類別匹配的布林值。

Bool2DKey被過濾器的一個布爾掩碼進一步過濾，在這個例子中Mask1。這可以擴展到更多的過濾器。

就像Scott所說的那樣。

就像Scott的例子一樣，我創建了一個陣列(稱為OnesArray)，在MMULT中用于對Bool2DKey的行進行求和。使用布爾陣列和MMULT可以忽略對COUNTIFS的需要。

這里是一個擴展的例子，我添加了更多的過濾器。

該公式是：

=LET(
    KeyColumn, Table1[Winner],
    FilterColumn1,Table1[Game],
    FilterColumn2,Table1[Rules],
    FilterColumn3,Table1[顏色]。
    FilterList1, Table2[非體育],
    FilterList2,Table3[規則],
    FilterList3,Table4[顏色]。

    分類，SORT(UNIQUE(KeyColumn))。
    Bool2DKey, TRANSPOSE(KeyColumn)=Categories,
    Mask1, TRANSPOSE(ISNUMBER(XMATCH(FilterColumn1,FilterList1))。
    Mask2, TRANSPOSE(ISNUMBER(XMATCH(FilterColumn2,FilterList2))。
    Mask3,TRANSPOSE(ISNUMBER(XMATCH(FilterColumn3,FilterList3))。
    Bool2DMasked, Bool2DKey*Mask1*Mask2*Mask3,
    OnesArray, SEQUENCE(ROWS(KeyColumn),1,1,0),

    頻率，MMULT（Bool2DMasked,OnesArray）。
    頻率
)

如果你想對一列進行求和（假設有一列名為Table1[count]的數值），公式為：

=LET(
    KeyColumn, Table1[Winner],
    SumColumn,Table1[Count],
    FilterColumn1,Table1[Game],
    FilterColumn2,Table1[Rules],
    FilterColumn3,Table1[顏色]。
    FilterList1, Table2[非體育],
    FilterList2,Table3[規則],
    FilterList3,Table4[顏色]。

    分類，SORT(UNIQUE(KeyColumn))。
    Bool2DKey, TRANSPOSE(KeyColumn)=Categories,
    Mask1, TRANSPOSE(ISNUMBER(XMATCH(FilterColumn1,FilterList1))。
    Mask2, TRANSPOSE(ISNUMBER(XMATCH(FilterColumn2,FilterList2))。
    Mask3,TRANSPOSE(ISNUMBER(XMATCH(FilterColumn3,FilterList3))。
    Bool2DMasked, Bool2DKey*Mask1*Mask2*Mask3,
    Value2DMasked, Bool2DMasked*TRANSPOSE(SumColumn),
    OnesArray, SEQUENCE(ROWS(KeyColumn),1,1,0),

    Sum, MMULT(Value2DMasked,OnesArray),
    平均值
)

標籤：

上一篇：將一個值寫入另一個作業表"下標超出范圍"錯誤

下一篇：用公式減去成對的單元格-excel