按條件求和,今天教好幾個公式。還有一些常用的,大部分人都會,就不寫了。
案例:
從下圖 1 的數據表中計算出符合 I、J 列條件的“淨增數”之和,並且將符合條件的行高亮顯示。
效果如下圖 2 所示。
公式 1:
=SUMIFS(G2:G28,A2:A28,I2,F2:F28,J2)
公式釋義:
sumifs 函數的作用是多條件求和,語法爲 SUMIFS(求和區域, 條件區域1, 條件1, [條件區域2, 條件2], ...);
這段公式表示對 G2:G28 按以下條件求和:A2:A28 區域的值爲 I2,且 F2:F28 區域的值符合 J2
公式 2:
=SUMPRODUCT((A2:A28=I2)*(F2:F28>10)*G2:G28)
公式釋義:
SUMPRODUCT 函數返回相應範圍或數組的乘積的總和;
(A2:A28=I2)*(F2:F28>10):中間的“*”相當于 and 函數作用;同時滿足兩個條件的結果爲 1,只要有一個條件不滿足即爲 0;
*G2:G28:將上述結果與區域相乘,用 sumproduct 對乘積求和,即可得出滿足所有條件的和
公式 3:
=DSUM(A1:G28,G1,I1:J2)
公式釋義:
dsum 是個數據庫函數,作用是返回列表或數據庫中滿足指定條件的區域中的數字之和;
語法爲 DSUM(數據庫區域, 需要返回的區域, 條件區域);
公式的含義是在數據庫 A1:G28 區域中返回標題爲 G1 的列,返回條件爲 I1:J2 區域
* 三個參數中的標題必須完全一致。
公式 4:
=SUM(FILTER(G2:G28,(A2:A28=I2)*(F2:F28>10)))
公式釋義:
filter 是 365 函數,作用是按條件篩選,語法爲 FILTER(要篩選的區域,篩選條件,[爲空時顯示的值]);
FILTER(G2:G28,(A2:A28=I2)*(F2:F28>10)):從 G2:G28 區域中篩選出同時符合 (A2:A28=I2) 和 (F2:F28>10) 的值;
sum(...):對上述篩選結果求和
高亮顯示符合條件的行:
1. 選中 A2:G28 區域 --> 選擇菜單欄的“開始”-->“條件區域”-->“新建規則”
2. 在彈出的對話框中選擇“使用公式確定要設置格式的單元格”--> 輸入以下公式 --> 點擊“格式”按鈕:
=($A2=$I$2)*($F2>10)
* 請務必注意單元格的絕對和相對引用。
3. 在彈出的對話框中選擇“填充”選項卡 --> 選擇所需的填充色 --> 點擊“確定”
4. 點擊“確定”。
效果如下。