编按：业务越繁忙的公司应收账款就越庞大，统计应收账款账龄就成为了一部分会计人员的噩梦，尤其是会计新人的噩梦。本篇教程将提供一个完美的解决之道：Lookup函数与透视表结合实现账龄分档统计。该方法对按数字大小进行分档（分区间）的所有统计均有效。如果你曾为此类统计工作而哭泣，那么微笑吧。

从事财务工作的伙伴对于账龄统计表一定不陌生。财务部门根据时间段汇总每个客户的应收账款金额生成应收款账龄统计表，然后交给相关业务部门去催款。通常财务系统导出的数据并不能直接满足需要，还需要使用Excel来二次加工，这个过程所耗费的时间就因人而异了。

我们来看个实际的例子：

系统导出的数据可能有很多列，这里我们只保留了需要的三列数据，要根据这个数据源做出下图所示的汇总表：

我不知道各位是如何完成这个汇总表的，但是我见过有人这样做：首先筛选一个客户，再筛选该客户30天以内的交易信息，选择对应的金额，从Excel右下角的状态栏看合计金额，然后填入汇总表对应的位置；再筛选该客户31~60天的交易信息……如此四次，完成一家客户的数据汇总……

在她看来，自己已经操作得非常熟练了，完成这样一张表基本上一上午就能做完，而那些新来的人有时候一天还做不好呢。

假如懂得一些函数的运用，再会用透视表的话完成这个汇总表三分钟应该就够了，不信的话看看动画演示！

整个操作就两步：

第一步：通过公式把各笔交易欠款天数分成需要的4档。公式如下：

=LOOKUP(NOW()-B2,{0,31,61,91},{"欠款0-30天","欠款31-60天","欠款61-90天","欠款90天以上"})

第二步：利用透视表生成账龄表。

选中整个数据，插入透视表，客户简称作为行，账龄作为列，应收账款金额作为值。

下面重点为各位伙伴解释一下第一步的公式。

=LOOKUP(NOW()-B2,{0,31,61,91},{"欠款0-30天","欠款31-60天","欠款61-90天","欠款90天以上"})

（1）公式目的

公式目的只有一个，就是根据账期（当天日期与交易日期之间的天数）进行分类。实现这种分类的公式有多种，当前采用LOOKUP函数公式只是其中的一种。其他比较常见的是用IF函数来实现，但是公式会比较长，容易出现错误。

（2）LOOKUP函数公式的用法

结合本例我们来解释一下LOOKUP函数的用法。

LOOKUP函数常用格式=（查找值，查找区域，返回值）。

以本例来说，我们添加两个辅助列，F列“日期区间下限”，G列“显示内容”。F列作为查找区域，G列是要返回的值。

然后公式可以这样写：=LOOKUP(NOW()-B2,$E$2:$E$5,$F$2:$F$5)

很明显可以看到函数是由三个参数构成的。

第一参数：NOW()-B2。这部分表示当天日期与交易日期之间的间隔天数，也就是查找值。

第二参数：$E$2:$E$5，查找区间，当前区间中的数据是要查找的日期区间的下限值。比如我们需要找0-30天，下限就是0，要找31-60天，下限就是31，以此类推。第二参数必须以升序的方式存在，LOOKUP会自动判断第一参数属于哪一个对应的区间。

第三参数：$F$2:$F$5，返回值，对应查找区间要实际显示的内容。LOOKUP在判断出账期属于哪个区间后，会显示出对应的内容。

使用这个公式要注意两个地方：第二参数的区间只需要提供下限，并且是升序排列的方式。

（3）取消辅助列的公式用法

在实际使用的时候，我们可以不添加辅助列，这时候就需要把具体数据以常量数组的方式写在公式中。也就是用{0,31,61,91}取代$E$2:$E$5 ，用{"欠款0-30天","欠款31-60天","欠款61-90天","欠款90天以上"}取代$F$2:$F$5。

这里学到的LOOKUP公式属于一种自动分类套路公式。学会了它，再配合透视表的简单操作，很多原本非常麻烦的分类统计工作都可以变得非常简单。大家一定要了解、掌握这种方法！

＊＊＊＊部落窝教育－excel制作账龄统计表＊＊＊＊

原创：老菜鸟/部落窝教育（未经同意，请勿转载）

在我的实际工作中，需要对22名员工平时的工作表现做一份考核表，本次具体会分享如下几个Excel操作的小细节，希望对有计算需求的朋友带来方便。

1，这项“团队精神”评判是泛范围的（即评论时可评某人，也可以选择不评），这样一来，参评的人数并不相同，我们就无法简单粗暴地按收到多张评测纸就除以几来计算其平均分值，此时我们可以借助于=SUM('1:22'!C6)进行跨多张表格求和，用=COUNT('1:22'!$C6)来统计参评个数，

如下图共有22人参与评论（就有22份格式一致但内容不同的表）。

在汇总表格的C6储存格输入=SUM('1:22'!C6)，这是将表1--表22的C6格求和，然后拖拽此公式向右向入填充。操作细节：输入=SUM（之后点住名字表1，然后按住SHIFT键不放，再点到名字表-22，以此操作进行区域选择，到时步时公式显示为：＝SUM（'1:22'!，此时再在选区域中的任意一张中点选C6格，再“）”完结公式；

接下来是统计每项评的人数，由于每评一个人必须把10项全评完，这样我们就可以直接以C列为依据进行参评人数的统计了。公式为在N6中输入=COUNT('1:22'!$C6)，表式统计1-22份表中C6格为非空格的数量；

对22份格式一致的参评表格进行统计

接下来再奉献一个非常非常实用的复制+选择性粘帐贴组合，绝对实用：大家再看上图，每项原本最高分都10，可现在全部都是多人之和，也就是与表格表现方式不符。此时我们可以利用shift键（或鼠标拖拉）选择区域到N6：N27------->然后按右键点开“选择性粘贴”--->点选“公式”---->“除”---确认。（具体见下短视频)

当然，我们也可以使用同样的方式直接=AVERAGE('1:22'!C6)来进行求平均，此份主要讲了几个平时工作中比较实用的EXCEL小技巧，希望有用哈！

转载自百家号作者：奥德王打电脑

关键字统计查询是在Excel中比较常用的一个函数，比如产品推介会后收集到的与会名单统计。老板问你来了多少男的？多少女的？A类产品意向多少？B类产品意向多少？怎么办，这就要用到关键字统计查询了。

上图演示了关键字统计查询应用的效果，用到的Excel函数是COUNTIF。

COUNTIF ：计算某个区域中满足条件的单元格数目

Excel中统计关键字函数步骤使用如下：

第一步，准备好表格，源数据表和统计表格；

第二步，输入“=COUNTIF(表3[性别],"男")” ，回车；

COUNTIF函数参数解释：第一个参数是指要统计的数据区域，第二个参数是要统计的关键字（需要引号括起来）；

注意：Excel中函数参数中用到的符号，如逗号等都是半角英文符号，即在英文状态下输入；

第三步，其他统计项目依次填写对应函数式；

咦！有什么奇怪的东西混进来了，怎么性别统计都是零呢？

这就需要用到一些编程小知识了，需要在第二个参数中加一个通配符“*”号，像这样“=COUNTIF(表3[姓名],"张*")”。

通配符 “*” 星号，用来表示匹配任意字符。除了这个常用的公式符号，还有以下这些常用的符号：不等于 (<>)、大于 (>) 、小于 (<)、等于 (=)，用来对数值进行统计比较。

你学会了吗？有问题请留言。祝，安好。

可能会遇到的问题：

输入法问题导致的参数错误？输入参数用的各种符号时，请将输入法调至英文输入状态；为什么没能返回预期得值？请确保函数及其参数输入正确；为什么统计出来的没有进度条显示？给单元格设置个数据条件样式就可以了；

一份学生成绩单，需要分别统计出60分以下、60-69、70-79、80-89、90-100各阶段成绩的学生数量。下面分别介绍4种方法来统计符合要求的学生数量。

1、利用FREQUENCY函数

FREQUENCY函数为：计算数值在某个区域内的出现频率（个数）。函数语法为：

FREQUENCY(data_array, bins_array)：

Data_array 必需。 要对其频率进行计数的一组数值或对这组数值的引用。 如果 data_array 中不包含任何数值，则 FREQUENCY 返回一个零数组。Bins_array 必需。 要将 data_array 中的值插入到的间隔数组或对间隔的引用。 如果 bins_array 中不包含任何数值，则 FREQUENCY 返回 data_array 中的元素个数。使用FREQUENCY函数难点为函数返回的是一个数组，所以它必须以数组公式的形式输入。C2:C26 为学生成绩，E2：E6为成绩分数区间分割点，对应的分别在F2：F6统计数量。

1）选中F2：F6单元格区域（由于是数组函数，必须选中F2：F6单元格区域），选定后在F2输入公式：

=FREQUENCY(C2:C26,E2:E6)。

2）公式输入完后同时按Shift+Ctrl+Enter键，会在F2:F6中分别返回满足条件的成绩个数。

2、使用COUNTIFS函数

COUNTIFS函数为：统计满足某个条件的单元格的数量（个数）。函数语法为：

=COUNTIF(要检查哪些区域? 要查找哪些内容?)

这里将学生成绩按成绩范围各自计数，E2:F6为成绩范围，在G2输入公式：

=COUNTIFS($C$2:$C$26,">="&E2,$C$2:$C$26,"<="&F2)，然后向下拖动复制到G6即可。注意C2:C26为绝对引用，按F4加"$"符号。

3、利用数据透视表

数据透视表处理大量数据时比较常用的方法，而且比较直观。

4、利用分析工具库

首先加载分析工具库。打开Excel选项，点击加载项，在管理下拉列表选择excel加载项，点击转到，勾选分析工具库。在excel数据工具栏下会加载数据分析选项。

1）点击工具栏数据—数据分析，在数据分析对话框中选择直方图，确定。

2）在直方图对话框输入区域和接收区域分别输入数据区域，在输出区域选择一个空单元格，下面柏拉图、累积百分率、直方图3个选项任选一个，我这里选直方图，确定。

1、一列数据同时除以10000

复制10000所在单元格，选取数据区域 - 选择粘性粘贴 - 除

2、同时冻结第1行和第1列

选取第一列和第一行交汇处的墙角位置B2，窗口 - 冻结窗格

3、快速把公式转换为值

选取公式区域 - 按右键向右拖一下再拖回来 - 选取只保留数值。

4、显示指定区域所有公式

查找 = 替换为“ =”（空格+=号） ，即可显示工作表中所有公式

5、同时编辑所有工作表

全选工作表，直接编辑，会更新到所有工作表。

6、删除重复值

选取数据区域 - 数据 - 删除重复值

7、显示重复值

选取数据区域 - 开始 - 条件格式 - 显示规则 - 重复值

8、把文本型数字转换成数值型

选取文本数字区域，打开左上角单元格的绿三角，选取 转换为数值

9、隐藏单元格内容

选取要隐藏的区域 - 设置单元格格式 - 数字 - 自定义 - 输入三个分号;;;

10、给excel文件添加密码

文件 - 信息 - 保护工作簿 - 用密码进行加密

11、给单元格区域添加密码

审阅 - 允许用户编辑区域 - 添加区域和设置密码

12、把多个单元格内容粘贴一个单元格

复制区域 - 打开剪贴板 - 选取某个单元格 - 在编辑栏中点击剪贴板中复制的内容

13、同时查看一个excel文件的两个工作表

视图 - 新建窗口 - 全部重排

14、输入分数

先后输入 0 ，再输入 空格， 再输入分数即可

15、强制换行

在文字后按alt+回车键即可换到下一行

16、删除空行

选取A列 - Ctrl+g打开定位窗口 - 定位条件：空值 - 整行删除

17、隔行插入空行

在数据表旁拖动复制1~N，然后再复制序号到下面，然后按序号列排序即可。

18、快速查找工作表

在进度条右键菜单中选取要找的工作表即可。

19、快速筛选

右键菜单中 - 筛选 - 按所选单元格值进行筛选

20、让PPT的图表随excel同步更新

复制excel中的图表 - 在PPT界面中 - 选择性粘贴 - 粘贴链接

21、隐藏公式

选取公式所在区域 - 设置单元格格式 - 保护：选取隐藏 - 保护工作表

22、行高按厘米设置

点右下角“页面布局”按钮，行高单位即可厘米

23、复制时保护行高列宽不变

整行选取复制，粘贴后选取“保持列宽。

24、输入以0开始的数字或超过15位的长数字

先输入单引号，然后再输入数字。或先设置格式为文本再输入。

25、全部显示超过11的长数字

选数区域 - 设置单元格格式 - 自定义 - 输入0

26、快速调整列宽

选取多列，双击边线即可自动调整适合的列宽

27、图表快速添加新系列

复制 - 粘贴，即可给图表添加新的系列

28、设置大于72磅的字体

excel里的最大字并不是72磅，而是409磅。你只需要输入数字即可。

29、设置标题行打印

页面设置 - 工作表 - 顶端标题行

30、不打印错误值

页面设置 - 工作表 - 错误值打印为：空

31、隐藏0值

文件 - 选项 - 高级 - 去掉“显在具有零值的单元格中显示零”

32、设置新建文件的字体和字号

文件 - 选项 - 常规 - 新建工作簿时....

33、快速查看函数帮助

在公式中点击下面显示的函数名称，即可打开该函数的帮助页面。

34、加快excel文件打开速度

如果文件公式过多，在关闭时设置为手动，打开时会更快。

35、按行排序

在排序界面，点击选项，选中按行排序

36、设置可以打印的背景图片

在页眉中插入图片即要

来源：网络整理

百家号-【袁帅数据分析运营】运营者：袁帅，会展业信息化、数字化领域专家。新社汇平台联合创始人，永洪数据科学研究院MVP。认证数据分析师、网络营销师、SEM搜索引擎营销师、SEO工程师、中国电子商务职业经理人。畅销书《互联网销售宝典》联合出品人。

做项目，需要一个项目预算表，但预算和实际费用总有一些差异，所以我们需要一套能够自动统计的表格帮我们分析预算与实际费用的差异，以便我们清楚明确的知道各项费用的支出是否在合理范围之内，因此，我给大家分享一套能自动计算统计、自动生成曲线表、自动生成圆饼图的Excel表格。

预算表整体预览：

EXCEL表格中：预算、实际、差额、差异率，排名、曲线图、圆饼图均可自动生成；

自动统计计算：

费用曲线自动生成

饼形图自动生成

费用类别直接修改

表格获取方式：

1、评论+转发

于多表统计，以前也发布了一些相关文章，不过还是有读者不能很好掌握，今天我分享一种更简单的办法。

格式相同的多个表格，现在要统计所有人员的工资数据。

Step 01 新建一个空白的汇总表，点击汇总表任意空白单元格，再点击数据→合并计算，这时会弹出合并计算对话框。

Step 02 鼠标引用第一个表的区域，点击添加。

Step 03 重复添加剩下的所有表格，添加完毕以后，勾选首行和最左列，点击确定。

瞬间就统计出来，非常快。

Step 04 统一格式，搞定收工。

上一篇给大家分享了一下筛选功能的使用，特别要注意不能随意复制粘贴的原因和解决办法。有兴趣的朋友们可以点击或关注百家号，进去查看历史文章。

那么，今天给大家分享下EXCEL透视表功能的简单使用。通常情况下，我们需要做批量数据的统计、用excel出图表等等的时候，需要计数或者求和的结果作展示的时候都会用到。可以说是在大数据分析以及展示结果的时候，所必须会使用到的一个功能。

在此，让我们一起通过一个实例来看一下，excel数据透视表的具体使用方法。只需要简单几步，就可以完成一个简单的数据透视！

首先，我们打开一个要处理的EXCEL，比如需要统计各部门总工资。如下图的数据。通过1月到5月每个人的工资记录，来计算出部门工资的总数及每个月的走势。

第一步：选定A列到H列，即包含所有数据的列。

第二步：点击插入-数据透视表，出现一个创建数据透视表的小窗口，直接点击确定。

此时出现了一个新的sheet页，如下图。这里为了方便大家看全，我把表格横向缩小到了一起。实际上数据透视表字段是在EXCEL最右侧。

第三步：新sheet页的最右侧数据透视表字段，有一个选择要添加到报表的字段，可以看到原始表格的标题列。继续往下看，有四个区域，分别为筛选器，列，行，值。我们把月份点住，拖动到列的区域中。

再分别把部门、姓名拖动到行，部门在上。最后把工资拖动到值。

第四步：值里边默认是计数项，我们需要修改一下，工资是以求和来统计。点击计数项：工资，会出现值字段设置。打开后，选择求和，然后点击确定。

第五步：此时已经可以看到表中的数据都已经出现，每个部门每个人1月到5月工资以及总计的工资。可以点击技术部、科研部、运营部前边的-号，代表隐藏姓名；最后一列的总计，每一行代表这一行数据的总计，比如第一行代表技术部1月到5月的总计工资数目；最后一行的总计，每一列代表的是这一列数据的总计，比如1月的那一列代表1月各个部门的总计工资。这样就满足我们的需求了，可以看到每个部门在每个月以及合计的工资数目。

习惯而言，统计的数据都喜欢有高低顺序来浏览，方便一眼看出哪个部门的工资总额高低。我们可以再点击一下总计那列，然后点击排序，选择降序排列。这样就可以看到一个按高到低排序的工资图表了。

好了，本篇就给大家讲到这里，大家可以自己试着随意在四个区域里，把其他的标题也拖进去，看看会出现什么变化？其实看似枯燥的Excel工具也有非常有趣的一面，更多的技巧就留给大家自己开发吧！有什么问题欢迎留言给我们哦！

同样一个任务，一个人花了5小时，吃了很多苦，做到60分；另一个人花了5分钟，轻轻松松，做到了80分。你会给第一个人点赞，夸她棒吗？

在职场，过程不重要，结果才是最重要的。

1.格式相同的表格，要在第一个表汇总后面所有表格D列的总量。

格式相同的表，多少个都一样，一个SUM函数就秒杀了。

=SUM('4.1:4.7'!D3)

语法：

SUM(起始表格名称:结束表格名称!单元格)

表格名称没有规律也可以，在有特殊字符的情况下需要加''。

我们知道*是通配符，代表所有的意思，在这里也可以用*来实现统计。

=SUM('*'!D3)

2.同样也是格式相同，但要汇总的形式不一样，要汇总每一个月份的总销量。

这里是用SUM+INDIRECT函数的组合实现。

可能很多人一开始直接就用SUM，可惜得到的是错误值。A2&"!C:C"被当成文本处理，而不是区域。

=SUM(A2&"!C:C")

我们正常的引用都是直接引用，如区域C:C。间接引用就是通过第三者才能获得的，如INDIRECT("C:C")。也就是说，只要嵌套个INDIRECT函数就可以。

3.跟上一个例子很像，唯一的差别就是最后多一个合计的实发数量，而每一个表格的行数不确定。现在要获取每个表合计的实发数量。

这种有很多种处理方法，最简单的办法有两种。

01 查找每个表，最后一个数量。

=LOOKUP(9E+307,INDIRECT(A2&"!B:B"))

02 统计每个表的数量后再除以2。

=SUM(INDIRECT(A2&"!B:B"))/2

4.现在换成统计商品的销售量。

明细表

汇总表

难度瞬间就上来了，要结合好多函数。

=SUMPRODUCT(SUMIF(INDIRECT(ROW($1:$6)&"月!B:B"),A2,INDIRECT(ROW($1:$6)&"月!C:C")))

看起来很复杂，但实际上也不是太难理解。

INDIRECT(ROW($1:$6)&"月!B:B")这部分就是获取1-6月的B列

INDIRECT(ROW($1:$6)&"月!C:C")这部分就是获取1-6月的C列

SUMIF(1-6月的B列,A2,1-6月的C列)就是分别统计这6个表

SUMPRODUCT(SUMIF())就是将最终这6个表进行求和

看了这个技巧是不是很惊喜。惊喜就分享给你的朋友吧！

如果有一张上千行的销售表，像下图（各个地区按天统计的1至12月的物品销量），要统计每个月，每个地区的数据，当看到上千行的数据，是不是发愁无从下手呢，利用数据透视表，轻松搞定。

1、选中数据表中任意单元格，点击工具栏插入——数据透视表。弹出创建透视表对话框，点击确定。

2、在右边数据透视表字段对话框添加字段，这里勾选订购日期、地区、分类、销售额和成本。字段勾选根据数据分析的要求勾选。

勾选后，左边单元格会生成下图所示数据表。

3、但是日期是按天来统计，我们需要的是按月统计，选中行标签统计的某一天的单元格，例如2015/1/24，右键创建组，在组合对话框中，选择月，确定。

4、确定后生成下图所示的统计表，但是原始数据没有统计利润，这里我们为了说明问题，我们简单统计利润。

5、增加字段，统计利润，假设利润为销售额减去成本，点击数据透视表工具的分析菜单选项。

6、点击字段、项目和集——计算字段。

7、在插入计算字段对话框中，名称填写利润，公式填写=销售额-利润，确定。

8、则上千行的数据按月份统计完成。

这里只是数据透视表的基本用法，数据透视表还可以排序、筛选，还可以转换称图表，还有很多更强大用途。