tft每日頭條

 > 科技

 > excel分類計算多列數據的公式

excel分類計算多列數據的公式

科技 更新时间:2024-12-25 13:16:42

來自網友的求助:一對多的數據表,統計一對一唯一項的數量。

案例:

下圖 1 是銷售人員的各項産品銷量表,有的銷售可能在不同時期多次售出同樣産品,比如下圖 2 這樣,于予菊就兩次售出過主機。

請統計出每位銷售出售的唯一産品類目數,效果如下圖 3 所示。

excel分類計算多列數據的公式(在一對多的冗長)1

excel分類計算多列數據的公式(在一對多的冗長)2

excel分類計算多列數據的公式(在一對多的冗長)3

解決方案:

1. 在 F2 單元格中輸入以下公式 --> 下拉複制公式:

=SUMPRODUCT(($A$2:$A$29=E2)/COUNTIFS($A$2:$A$29,$A$2:$A$29,$B$2:$B$29,$B$2:$B$29))

别被公式的長度吓壞,分解開來并不複雜。

公式釋義:

  • COUNTIFS($A$2:$A$29,$A$2:$A$29,$B$2:$B$29,$B$2:$B$29):
    • countifs 是多條件計數函數,語法為 COUNTIFS(區域1, 條件1, [區域2, 條件2],…);
    • 此處的公式旨在統計出區域内 A 和 B 列同時重複的次數,會生成一個由重複次數組成的數組;
  • $A$2:$A$29=E2:将 A 列的每個單元格與 E2 單元格的姓名比對,得到一組由 1 或 0 組成的數組;
  • ($A$2:$A$29=E2)/COUNTIFS(...):
    • 将上述兩個結果相除,分子中為 0 的,即姓名不匹配的,自然不會統計在内;
    • 如果姓名匹配,則看分母中的重複次數,例如,如果重複過 3 次,則相除後的數組中會出現 3 個 1/3;
  • SUMPRODUCT(...):最後用該函數将上述結果相加,重複多次的加起來結果也是 1,從而得到案例所需結果。

用 sumproduct 函數的好處是不必使用數組公式。

excel分類計算多列數據的公式(在一對多的冗長)4

excel分類計算多列數據的公式(在一對多的冗長)5

excel分類計算多列數據的公式(在一對多的冗長)6

2. 給标題加上篩選功能,分别篩選一下“于予菊”和“諸葛鋼鐵”的名字,這樣就能很清楚地核對結果,去除重複項,他們二位的産品類目數完全正确。

excel分類計算多列數據的公式(在一對多的冗長)7

excel分類計算多列數據的公式(在一對多的冗長)8

接下來再給大家介紹一個公式。

3. 在 F2 單元格中輸入以下公式 --> 按 Ctrl Shift Enter 結束:

=COUNT(0/(MATCH($B$2:$B$29,IF($A$2:$A$29=E2,$B$2:$B$29),)=ROW(A:A)))

公式釋義:

  • IF($A$2:$A$29=E2,$B$2:$B$29):如果 A 列的姓名與 E2 單元格相同,則返回對應的 B 列值;
  • MATCH($B$2:$B$29,...,):計算上述值在區域 $B$2:$B$29 的位置序号,遇到重複值也隻返回第一次出現的位置序号,也就是變相起到了去重作用;
  • ROW(A:A):生成自 1 開始的遞增自然數;
  • MATCH(...)=ROW(A:A):将位置序号與自然遞增數相比,如果相同則返回 true;
  • count(0/...):隻有當分母不為 0 時,即 true 時,被 0 除才有意義,計算出這些有意義的結果數量就是想要的答案

excel分類計算多列數據的公式(在一對多的冗長)9

excel分類計算多列數據的公式(在一對多的冗長)10

4. 下拉複制公式:

excel分類計算多列數據的公式(在一對多的冗長)11

很多同學會覺得 Excel 單個案例講解有些碎片化,初學者未必能完全理解和掌握。不少同學都希望有一套完整的圖文教學,從最基礎的概念開始,一步步由簡入繁、從入門到精通,系統化地講解 Excel 的各個知識點。

現在終于有了,以下專欄,從最基礎的操作和概念講起,用生動、有趣的案例帶大家逐一掌握 Excel 的操作技巧、快捷鍵大全、函數公式、數據透視表、圖表、打印技巧等……學完全本,你也能成為 Excel 高手。

,

更多精彩资讯请关注tft每日頭條,我们将持续为您更新最新资讯!

查看全部

相关科技资讯推荐

热门科技资讯推荐

网友关注

Copyright 2023-2024 - www.tftnews.com All Rights Reserved