萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 辦公室軟體 >  >> Excel 教學 >> Excel 綜合

Excel 資料庫函數全解析:D 函數族與 GETPIVOTDATA 實戰應用

資料庫函數 (D Functions) 核心概念

專為「結構化資料表」 (有標題列的列表) 設計,語法統一為:
D函數(database, field, criteria)

  • database:含標題列的完整資料區域 (如 A1:E100)。
  • field:要計算的欄位,可用「標題文字」 (如 "銷售額") 或欄位索引數 (如 5)。
  • criteria:條件區域,**必須包含標題列**,且至少一列條件 (可多列、多欄,同列為 AND、不同列為 OR)。

12 大 D 函數速查表

函數用途回傳值相對應一般函數
DAVERAGE符合條件記錄的算術平均數值AVERAGEIFS
DCOUNT符合條件且為數值的記錄筆數整數COUNTIFS
DCOUNTA符合條件且非空白的記錄筆數整數COUNTA+條件
DGET擷取符合條件的單一值 (若多筆報錯)單一值VLOOKUP/INDEX+MATCH
DMAX符合條件記錄的最大值數值MAXIFS
DMIN符合條件記錄的最小值數值MINIFS
DPRODUCT符合條件記錄的乘積數值無直接對應
DSTDEV符合條件記錄的樣本標準差數值STDEV.S+條件
DSTDEVP符合條件記錄的母體標準差數值STDEV.P+條件
DSUM符合條件記錄的總和數值SUMIFS
DVAR符合條件記錄的樣本變異數數值VAR.S+條件
DVARP符合條件記錄的母體變異數數值VAR.P+條件

實戰範例:銷售資料多條件統計

資料表 A1:E100 (標題:業務、產品、區域、數量、金額)。
條件區域 G1:I2:

業務產品區域
王小明筆電北區
李大華南區

含義: (業務=王小明 AND 產品=筆電 AND 區域=北區) OR (業務=李大華 AND 區域=南區)。

查詢公式

  • 總銷售額:=DSUM(A1:E100, "金額", G1:I2)
  • 平均單價:=DAVERAGE(A1:E100, "金額", G1:I2)
  • 筆數:=DCOUNT(A1:E100, "金額", G1:I2)
  • 最大單筆:=DMAX(A1:E100, "金額", G1:I2)

DGET 獨特用法:精確單值擷取

  • 條件必須唯一識別一筆記錄,否則回傳 #NUM! (多筆) 或 #VALUE! (零筆)。
  • 適合做「鍵值查詢」:條件區只放主鍵 (如發票號),DGET 直接抓取該筆的任一欄位值。

GETPIVOTDATA:從樞紐分析表抓取彙總值

GETPIVOTDATA(data_field, pivot_table, [field1, item1], [field2, item2], ...)
  • data_field:值區域欄位名稱 (如 "銷售額總和")。
  • pivot_table:樞紐表任一儲存格參考 (如 A3)。
  • field/item 對:篩選條件 (如 "業務","王小明","產品","筆電")。

優點:即使樞紐表重新整理、排序、展開/摺疊,公式仍能精準抓取正確彙總值,比直接參考儲存格穩健得多。

D 函數 vs *IFS 函數 選擇指南

比較面向D 函數*IFS 函數 (SUMIFS 等)
條件表達獨立條件區域 (可視化、易維護、支援 OR)公式內參數對 (AND 邏輯固定)
動態條件修改條件區儲存格即可需修改公式或用間接參考
計算效能大資料集較慢 (掃描整表)優化較好
相容性全版本支援Excel 2007+ (SUMIFS/COUNTIFS/AVERAGEIFS)
建議場景臨機分析、條件複雜/常變、需 OR 邏輯固定報表、大量資料、高效能需求
copyright © 萬盛學電腦網 all rights reserved