☰

Excel資料查詢

tag-icon • tag-icon microsoft-excel worksheet-function date
Excel資料查詢

我正在嘗試建立一個預算監控電子表格。我想按類別和月份提取條目總和。數據按如下行排列，並且隨著我下載支出而每月都會增長。這是範例資料：

Date       Category    Amount
03-15-21   Auto        500.00
02-12-21   House       375.00

最終，我將在列中顯示月份，在行中顯示類別，以便我們可以監控我們的預算。

如何按類別和月份提取資料？

我查看了 sumifs、sumproduct、index match，沒有找到解決方案。我很感激任何指導。

答案1

數據透視表！選擇整個表，按一下「插入」>「資料透視表」。依照提示操作，直到看到資料透視表欄位側欄。現在您必須將欄位拖曳到下面的正確方塊中。您可以嘗試一下，按照您喜歡的方式進行操作。但首先，

將過濾器留空。將日期拖曳到“列”部分。將類別拖曳到“行”部分。將金額拖曳到“值”部分。

如果 Excel 正確識別您的日期，資料透視表應每月自動分組。如果沒有，您也可以插入一個帶有標題「月份」的新欄位 B，並將公式新增=EOMONTH(A2,0)至 B2。在資料透視表中使用月份而不是日期。如果您在建立資料透視表後新增列，則必須重新整理資料透視表。如果 EOMONTH 仍然出現錯誤，請確保您的日期格式與您的區域日期格式設定相符。

提示： 資料透視表和圖表絕對是 Excel 中的一項瑰寶功能。一開始可能會讓人感到困惑，但一旦你掌握了它，你就永遠不會回頭。也許可以在 YouTube 上觀看「如何使用資料透視表」的快速影片。

答案2

您可以直接使用資料透視表：

在此輸入影像描述

答案3

這解決了這個問題：

在此輸入影像描述

怎麼運作的：

  • Y34 中輔助資料的公式：

    =TEXT(V34,"mmmm")

  • Z34 中唯一月份名稱的陣列 (CSE) 公式：

    {=IFERROR(INDEX($Y$34:$Y$49,MATCH(0,COUNTIF($Z$33:Z33,$Y$34:$Y$49),0)),"")}

  • AA33 中唯一類別名稱的陣列 (CSE) 公式：

    {=IFERROR(INDEX($W$34:$W$49,MATCH(0,COUNTIF($Z$33:Z33,$W$34:$W$49),0)),"")}

注意

  • 完成數組公式Ctrl+Shift+Enter。

  • 為了以後的整潔，您可以將輔助資料隱藏在 Y 列中。

  • 儲存格 AA34 中的公式：

    =SUMPRODUCT((TEXT($V$34:$V$49,"MMMM")=$Z34)*($W$34:$W$49=AA$33)*($X$34:$X$49))

在整個範圍內填寫公式。

根據需要調整公式中的儲存格引用。

相關內容