在日常工作裡,經常會輸入日常的流水明細,如何根據流水明細彙總出數據,并便于查詢呢?比如下面這種表格,記錄了每日的銷售明細,在查詢區域選擇品種能自動彙總出總金額,并在明細表裡标記出彙總項目的明細。
大家先看下圖片,點擊品種,選擇一個品種後,總金額裡會自動彙總左側明細表符合條件的數據,并标記紅色填充。這裡主要運用了數據驗證、條件格式和函數SUMPRODUCT,分三個步驟實現如下效果,下面我們一起來看看吧。
第一步:設置查詢區域
在明細表格的右側創建查詢區域。
左側明細表格裡的品種是重複的,複制出來。
點擊菜單欄上“數據-删除重複值”。
移動鼠标到品種單元格内,點擊菜單欄上“數據-數據驗證”。
彈出窗口,設置驗證條件,在允許下方選擇“序列“,在來源裡選擇去除了重複值的數據列。
第二步:彙總金額
在總金額單元格内輸入函數公式“=SUMPRODUCT((B3:B26=F3)*C3:C26*D3:D26)”。
在這個公式裡有三個數據區域,分别為B3:B26=F3、C3:C26、D3:D26,當B3:B26區域中的值和F3單元格中的值相等時,返回1,否則返回0,然後三個數據對應元素先乘積,再彙合,從而計算得到相應品種的總銷售金額。
公式設置好後,我們選擇不同的品種,總金額随着選擇的品種自動彙總。
第三步:設置條件格式
選中明細數據表,點擊菜單欄上“條件格式-新建規則”。
彈出窗口,點擊“使用公式确定要設置格式的單元格”,在輸入框裡輸入公式“=IF($B3=$F$3,1,0)”,在格式中選擇顔色。
K列可以隐藏起來,在查詢區域裡選擇不同的選項,明細表裡根據選項自動填充紅色。
以上就是excel表格裡SUMPRODUCT彙總明細表計算金額的實例操作,并通過條件格式和數據驗證的設置讓查詢更省心、方便,你學會了嗎?
,更多精彩资讯请关注tft每日頭條,我们将持续为您更新最新资讯!