☰

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))

在整个范围内填充公式。

根据需要调整公式中的单元格引用。

相关内容