1200字范文 > EXCEL数据报表完成财务表格汇总数据查询

EXCEL数据报表完成财务表格汇总数据查询

时间：2022-08-10 15:50:56

excel财务报表条件匹配，数据汇总

项目要求主要运用的excel公式具体实现时的思路按条件汇总思路解决困难项标签汇总，用标签汇总其他数据遇到的操作失误

项目要求

接手一个excel项目，为一家公司完成财务报表里的具体公式。

帅选出某一个月内发生的交易，客户，供货商；筛选某一客户的全部订单，某一供货商的全部订单，及财务信息；年度汇总，按月汇总。表格美化。

主要运用的excel公式

在实现具体功能时感受到excel公式的强大，既能完成功能，也能美化界面。

=IFERROR(INDEX(采购录入表!C:C,SMALL(IF(采购录入表!$D$4:$D$3000=$G$1,ROW(采购录入表!$D$4:$D$3000)),ROW($B1))),"")

要同时按下Ctrl +Shift +Enter作为结束

查询一列中与要求相同的第一项，如果查找出错则返回空，避免出现错误符号；=IF(INDEX(采购录入表!$D$4:$D$3000,MATCH(0,COUNTIF(B$2:B3,采购录入表!$D$4:$D$3000),)&"")="","",INDEX(采购录入表!$D$4:$D$3000,MATCH(0,COUNTIF(B$2:B3,采购录入表!$D$4:$D$3000),)&""))要同时按下Ctrl +Shift +Enter作为结束

查询一列中的不同项，汇总相同项只能出现一次，运用此公式在下拉时就能完成。=IFERROR(SUMIFS(采购录入表!G$4:G$3000,采购录入表!$A$4:$A$3000,$G$1,采购录入表!$D$4:$D$3000,$G4),"")

多条件求和；=IF(AND(E8="",D8=""),"",F7+D8-E8)

多条件判断，如果E8,D8表格内都没有数据，则返回空，否则执行加减操作；1、2公式组合按条件筛选出不同项。

具体实现时的思路

按项目最主要要求，我需要实现在点选月份，客户，供货商时在表格中生成与之对应的信息。起初使用LOOKUP函数查询，但是LOOKUP查找的数组需要按升序排序，否则查找到的结果就会某明奇妙。接着就使用INDEX查询，INDEX查询按照位置查找，不会存在排序问题，所以之后都使用INDEX查询，不会出错。

=index(array,row_num,column_num) ，其中array表示我们要引用的区域，row_num表示要引用的行数，column_num表示要引用的列数，最终的结果就是引用出区域内行列交叉处的内容。

我使用时主要查找一列内的内容，所以只需要得到行号就可以。那么接着就要得到具体行号

SMALL(IF(采购录入表!$D$4:$D$3000=$G$1,ROW(采购录入表!$D$4:$D$3000)),ROW($B1))

用来得到具体行号

SMALL函数的作用是：在一列数值中，按从小到大的顺序取第n个值。例如：=SMALL(A1:A10,5)，意思是，从A1:A10的10个数值，从小到大取第5个。

ROW函数的含义返回所选择的某一个单元格的行数。

IF(采购录入表!$D$4:$D$3000=$G$1,ROW(采购录入表!$D$4:$D$3000))

这个数组比较与条件$G$1相同则返回行号，不同就是FALSE。可以输入后按F9看数组比较结果。

ROW($B1)

结果=1

SMALL(IF(采购录入表!$D$4:$D$3000=$G$1,ROW(采购录入表!$D$4:$D$3000)),ROW($B1))

这样就返回第一个符合条件的行号。$表示锁定。公式下拉时列号不变行号+1，第二行就返回第二个符合条件的行号。

=IFERROR(INDEX(采购录入表!C:C,SMALL(IF(采购录入表!$D$4:$D$3000=$G$1,ROW(采购录入表!$D$4:$D$3000)),ROW($B1))),"")

这样就能筛选一列中与标签符合的具体数据。

按条件汇总思路

汇总就是要只保留不同标签，相同标签只出现一次。

=IF(INDEX(采购录入表!$D$4:$D$3000,MATCH(0,COUNTIF(B$2:B2,采购录入表!$D$4:$D$3000),)&"")="","",INDEX(采购录入表!$D$4:$D$3000,MATCH(0,COUNTIF(B$2:B3,采购录入表!$D$4:$D$3000),)&""))

用于筛选出不同。下面开始具体分析。

IF目标判断是否为空，是返回空，不是返回本身。

MATCH 函数可在单元格区域中搜索指定项，然后返回该项在单元格区域中的相对位置。例如，如果单元格区域 A1:A3 包含值 5、25 和 38，则以下公式：=MATCH(25,A1:A3,0)会返回数字 2，因为值 25 是单元格区域中的第二项。

COUNTIF(区域，条件）条件也可以是数组。

比如A1至A7单元格中的内容是 1,2,1,2,3,4,1 这样7个数

COUNTIF(A1:A7,A1:A7)={3,2,3,2,1,1,3} 即意思为在A1至A7单元格区域中,与A1相同的有3个,与A2相同的有2个,与A3相同的有3个,与A4相同的有2个,与A5相同的有1个,与A6相同的有1个,与A7相同的有3个这样的一个数组。

COUNTIF(B$2:B2,采购录入表!$D$4:$D$3000)

从第一行下拉就可以增加区域不增加条件，但是区域最后一行会增加上一次匹配到的不同项名称，这样在在执行COUNTIF时结果最后一项最大出现次数最多，在

MATCH(0,COUNTIF(B$2:B2,采购录入表!$D$4:$D$3000),)

就把已经出现排除。

以上就是筛选出不同项，但是没有按条件筛选出不同项。如筛选出某个月下发生过交易的供应商名单。

我没有想出一个具体解决的公式，但是我用两个公式解决了问题。首先我用公式筛选出某个月下的所有供货商交易的所有交易=IFERROR(INDEX(采购录入表!C:C,SMALL(IF(采购录入表!$D$4:$D$3000=$G$1,ROW(采购录入表!$D$4:$D$3000)),ROW($B1))),"")，

并保存在以固定列中，接着用上一个公式把这列中不同项筛选出来