问题：

怎么对每个工龄阶段进行人数统计

答：

数字分组

（1）以每隔5年进行统计

定位数字，右键创建组，设置步长为5，就可以统计出每个工龄阶段的总人数了。

今天就讲解到这，下一篇教程再见。

想要月薪上万，就要先点赞，当然啦！分享也是不可少哟

Excel的数据透视表功能很强大，但从办公的角度来说，可能还是繁琐了一点。以下图为例，现在我们希望得到每个产品的数量及占比：

如果使用Excel的数据透视表，需要先“创建数据透视表”：

然后设置要统计的字段列和数值列：

由于需要在绝对值列的基础上，再多个“占比”，因此，还要在“数量”列上点右键选择“添加到值”，这样增加了一个“求和项”：

如要将“求和项：数量2”显示为百分比，可在“数量2”上单击，选择“值字段设置--值显示方式”中的“列汇总的百分比”，如下图：

这样的透视表终于生成：

现在我们如果改用国产软件foxtable，那就简单了。点击菜单“数据统计-分组统计”，如下图：

点击“统计”后直接得到结果：

同时统计多列？很简单，加上相应的分组列和统计列即可：

生成结果如下图：

如果按日期统计，还可以自动生成同比和环比数据：

生成结果如下图（由于原始表的数据都是1999年的，因而无法生成同比数据）：

以上还仅仅只是简单演示了分组统计方面的部分功能，更强大的“交叉统计”留到后面再说。

时间数据自动分组

如下所示：是一份商品销售明细表，有时间，区域，商品，数量，金额几个字段。

表格上面的数据是按每天来的，如果我们想按月汇总数据，或者按季度，按小时，按分钟来汇总数据，那么我们就能用到数据透视表的时间自动分组了。

首先我们创建一个常规的数据表，在菜单栏，插入，数据透视表，我们将时间字段拖入至行标签，将数量和金额放在值标签，在2015版本的Excel中会得到下面的结果，会自动将时间按照月进行分布，如下所示：

在低版本的Excel中可以不能自动按月进行汇总，得到的结果是这样的：

无论哪种显示，都没有关系。

单元格放在数据透视表的行标签的时间上，然后点击选项卡上面的数据透视表工具的分布，选择组选择

然后可以在字段里面选择1个，或者多个字段进行汇总，得到根据自己需求的不同结果

假如我们单选按日选择，可以进行天数选择，比如，我们每隔7天是一周，可以进行按周进行分类

得到的结果如下所示：

当然我们也可以选择季度和月份两个字段，得到的结果就是

因为我们现在的原始数据中没有时，分，秒，所以在这边无法进行时，分，秒汇总，如果原始数据中有时分秒，便可以进行更细一步的分析。

本节完，你学会了吗？

给坚持学习的自己点个赞和转发吧

------------------

欢迎关注，更多精彩内容持续更新中....

数据透视表可以帮我们将日期、时间、数字字段进行自动组合归类，大大方便了我们的日常工作。

日期分组可以将日期字段按照年、季度、月、日进行自动归类。各分类之间可以同时选择，也可单独选择。

记得很久以前想得到日期的年，季度，月，日需要用函数做辅助列先计算出来，然后将辅助列添加到数据透视表，现在不用这么费事啦！

如上图所示，数据源中只有日期字段，在用数据透视表进行分析时，将日期放到行字段，将鼠标放在日期字段任意位置右击选择【组合】即可对日期进行分组。

分组功能可以自动获得日期的最小值和最大值，如无特殊要求，最大值和最小值我们只要使用默认值即可。

我们可以按年、季度、月、日进行分组，省去了做辅助列的繁琐工作。

时间分组可以将时间字段按照小时、分、秒进行自动归类。各分类之间可以同时选择，也可单独选择。

和日期分组一样，现在不用再烦恼不会时间函数而无法计算时、分、秒了，数据透视表可以很好的帮我们完成分组。

如上图所示，数据源中只有时间一列字段，在用数据透视表进行分析时，将时间放到行字段，将鼠标放在时间字段任意位置右击选择【组合】即可对时间进行分组。

分组功能可以自动获得时间的最小值和最大值，如无特殊要求，最大值和最小值我们只要使用默认值即可。

我们可以按小时、分、秒进行分组，让辅助列见鬼去吧！

数字分组可以将包含数字的字段根据需要进行有规律的归类和无规律的归类，有规律的归类可以自动分组，无规律的归类需要手动分组。

有规律的数字分组最大特点是步长相等，这样我们只要自定义步长就可以快速实现数字区间的分组，最大值和最小值使用默认值即可。

如上图所示案例，我们将步长（及年龄间隔）设置为10，数据透视表会自动按照自定义的步长帮我们将数字分成若干区间，是不是很智能呢？

无规律的数字分组最大特点是步长不相等，这样我们只能手动选择区间然后右键单击【分组】，分组后将组名称修改一下即可。

如上图所示案例，18岁-19岁之间步长（时间间隔）为2年，我们将其归类为20岁以下，20岁-30岁之间步长为11年，我们将其归类为20-30，其它的年龄段依次类推。

上图为数字分组的一种特殊情况，正数（出货数量）和负数（退货数量）同在一个字段，那么怎样通过分组的方法将出货数量和进货数量分离开呢？

其实不难，具体步骤如下：

① 将数量同时放在行字段和值字段。行字段的数量用来分组，值字段的数量用来汇总。

②对行字段的数量进行分组，将最小值设置为0，最大值设置为99999（也可设置为数据的最大值）。这样数据透视表自动分成了大于0和小于0两个组。

③修改组名称，将小于0的组命名为「退货」，大于0的组命名为「出货」。

④最后将行字段的数量放到列字段即可。

辅助分组是通过添加辅助列的方法用公式根据分类依据先计算出各分类，然后将辅助列添加到数据透视表的行字段或列字段。

上图我们添加辅助列，然后用SUMIF结合VLOOKUP计算出客户的等级，如果您对SUMIF和VLOOKUP的使用方法有疑问，可以参见往期课程。

将辅助列放到数据透视表行字段，就实现了快速分组，辅助列分组适用于分组条件较复杂的情况。

在excel中可以利用FREQUENCY函数进行频数统计，利用“数据分析”中的“直方图”宏程序进行频数分析。

函数 FREQUENCY 可以计算数值在某个区域内的出现频率，然后返回一个垂直数组，

由于 函数FREQUENCY 返回一个数组，所以它必须以数组公式的形式输入。

通过频数分布函数可以对数据进行分组和归类，从而使数据的分布形态更加清楚地表现出来。

下图是某课程的学生成绩，我们对其进行统计分析。

我们把学生成绩进行分段0-60、60-70、70-80、80-90、90-100。

我们在C2：C5区域一次输入59、69、79、89作为频数的接收区域（输入的不是每组上限，上限不在本组内）,因为分5组，所以有4个分段点，最后一个不用分段点，但是在计算时要包含进去。

选中D2:D6区域，编辑栏中输入：=FREQUENCY（B2：B25，C2：C5），再按Ctrl+Shift+Enter组合键，得到频数分布结果。

重新整理得到频数分布表。

我们利用直方图也可以获得各组的频数，在菜单“数据”选项卡下“数据分析”选项。如果没有找到需要先在excel选项中的“自定义功能区”中把“开发工具”选中。

然后打开“开发工具”加载项，选中“分析工具库”和“分析工具库-VBA函数”复选框。

这时我们打开“数据”选项卡，单击“数据分析”，弹出“数据分析”对话框。我们选择“直方图”。

在弹出的“直方图”对话框中选工作表中B2:B25填入输入区域，C2:C5填入接收区域。在输出区域中选一个单元格作为输出图表的起始区域，我们这里选G11，同时选中“累计百分比”、“图表输出”。（选中相应单元格区域系统自动加上绝对引用符号）如下图所示：

单击确定按钮，得到频数分布表、直方图及累计频数图。

使用数据透视表做统计十分方便，但当它遇到了日期，就会得到如下的数据透视表，每一天的销量数据都单独列出来了。

这显然不是我们想要的结果。我们想要的是一份可以按照周、月或季度汇总的统计结果，就像下面这张图。

怎么办呢？总不能在源数据表中添加新的一列，然后输入相应的“月份”吧？

那么插入日程表呢？

插入日程表后，可以很方便地筛选出每个月的汇总数据，但也不能同时显示每个月的汇总结果。而且“插入日常表”功能只有高版本的Excel支持，低版本的Excel和WPS都不支持。

插入切片器呢？也不行。

正确地而又简单的，是使用数据透视表的分组功能。

数据透视表日期按月汇总

在数据透视表上随意选中一个日期，右键一下，选择“组合”，然后选择“步长”为“月”即可。

数据透视表日期按季度汇总

和按月汇总一样，在组合设置窗口中将步长设置为“季度”即可。如果同时选中“月”和“季度”，那么就会同时按照“月”和“季度”汇总，就像文章开头第二张图一样。

数据透视表日期按周汇总

数据透视表组合窗口中没有“周”这一选项，不过我们可以设置为“日”，然后输入“天数”为“7”。

分组之后，再插入切片器，切片器中也会出现按周、月、季度筛选的选项哦。

这种分组的方法，除了可以用于给日期分组，也可以用于给数字分组。例如，有1-1000个数字，你想统计“1-99”、“100-199”……之间的数字有多少个时。

相关阅读：《Excel进阶：切片器怎么用？怎么用切片器制作动态图表》

学习，为了更好的生活。欢迎点赞、评论、关注和点击头像。

（友情提醒，多图，请在wifi下观看！流量壕请无视本条）

天天都在跟Excel打交道，你的效率有多高？那些Excel里提升效率的神器，你会用多少？

说到提升Excel效率的神器，在小奚心中，神器榜排名第一的非数据透视表莫属了。

不用写公式，不用手工计算，数据透视表通过简单的拖拽就能完成各个维度你想要的数据分类汇总，可以说是基础的Excel操作里面最简单易上手，最实用，最常用的功能了。

话不多说，上目录。本文内容较长，干货较多，大家可以根据目录挑选自己想看的内容。

我是目录，目录是我

上篇：基础操作

01 创建数据透视表

【问】：老板给你公司今年的订单明细，让你告诉他每个销售今年的销售额是多少？

看见了吗？

创建透视表就是如此简单，通过拖拽，就能立马得到老板想要的结果。

有很多初次接触数据透视表的同学对透视表的四个字段（筛选器、行、列、值）的意思不太了解，小奚用一张图告诉大家：

字段拖放在不同的区域，就会以不同的显示方式显示汇总的结果，并且同一个区域内的顺序不同，在数据透视表内汇总的先后层次也会不同。这也就是为什么透视表能完成各个维度的数据汇总。

02 透视表转普通表格

【问】：小奚啊， 老板只需要最终的汇总结果，不需要把数据明细发给他，那么怎么把透视表转成普通表格发给老板呢？

这个很简单，只需要复制数据透视表，粘贴成值就行了，如果喜欢透视表的格式还可以像小奚一样粘贴一下格式。

03 更改值字段计算方式

【问】：老板说我不仅想看每个销售这一年销售的总金额，还想看他们这一年签了多少笔合同。

在前面我们用到的是求和的计算方式，对于这个问题，我们可能就需要用到计数的计算方式了。

订单编号是文本类型，我们可以看到将订单编号拖到值字段时Excel会自动计算，因为文本类型不能求和，而金额是数值，可以求和，也可以计数。

除此之外，值字段的计算方式还有以平均值，最大值，最小值等计算，但是在我们平时的运用中，最常用到的还是计数和求和这两种。

04 插入计算字段

【问】：老板说，我还想知道每个销售以现在的年销售额他们的提成能拿多少（按千分之七来算）？

这里我们有一种偷懒的做法，直接在透视表外面乘以0.007，对于这种方法会出现两个问题：

第一，有的同学会发现写完公式下拉后，数字并不会改变，仍然是第一个的计算结果，对于这种情况可以如此操作：【文件】-【选项】-【公式】-取消勾选【使用GetPivotData函数获取数据透视表引用】，如下图所示。

但即使是解决了第一个问题，也会发现一旦改变透视表的结构，我们的计算结果就不能使用了，因为它在透视表外面不会随着透视表的改变而改变。

所以，其实最好的方法是在Excel里面插入计算字段。

05 透视表排序与筛选

【问】：老板说，把每个销售以销售额降序排列，同时，给我筛选出总销售额排名前10的销售。（老板，您的需求不能一次说完吗？）

降序排列其实比较好办，只需要选中金额所在的地方，右键选择降序排列即可。

如果存在多个字段的情况下，怎么筛选出老板想要的呢？直接使用行标签去筛选是行不通的，解决方法是：将鼠标点在列标签外面一格，然后使用筛选功能，具体操作请看下图。

06 行列百分比汇总

【问】：前面说的都是数值，但是我想看百分比怎么办？

想要值以百分比的形式显示可以：【右键】-【值显示方式】-可选择相应的百分比选项。

数据透视表有多种数据百分比的显示方式，下面我们只挑选最常用的：总计百分比、行/列汇总百分比和父级汇总百分来看。

总计百分比

当我们分析各个项目占总值的百分比时，就可以用总计百分比。例如：老板想看销售1部在服装这个产品占整个公司的销售额的多少。

行/列汇总百分比

当我们想看某个数据在行字段或者列字段的垂直维度上的占比时，就需要用到行/列汇总百分比。

列汇总百分比，例如：老板想看每个销售在单个产品上的占比是多少，我们就需要拉列汇总百分比给他看。

行汇总百分比，例如：老板想知道单个销售在每个产品的上售卖金额占比是多少，我们就需拉行汇总百分比给他看。

父级汇总百分比

当我们不想看某个数据占全部的占比，只是想看它在细分维度的占比的时候就需要用到父级汇总占比。

例如：老板说我想知道王麻子的销售额在他们团队占比是多少。

07 修改行列字段顺序

【问】：小奚，透视表自己出来的行列字段的顺序有的时候并不是我们想要的顺序，是不是只能【右键】-【移动】-【上移/下移/移至开头或结尾】？

其实小奚曾经也是这么干的，当时字段还特别多，小奚拿着鼠标点右键，上移下移了无数次，差点没崩溃。

这时领导从小奚身后飘过，实在看不下去了，说了一句：“我都是直接拖就可以了。”此刻小奚的内心戏是：“领导，我错了，是我学艺不精，你就当我是在练手指的灵活度吧！”

恩，忍住笑，严肃脸。下面来看领导是怎么拖的，上动图（前部分为上下移动的操作方式，后部分为直接拖动的操作方式）。

丢了这么大个人，不扳回一城不是小奚的风格啊，所以小奚又潜心学习，一个关于修改字段的更高阶技能出现啦！

这个技能对于反复使用的字段比较方便，平时若用得少，直接拖动会更方便一些。

【先做一个辅助表】-【文件】-【选项】-【高级】-【常规】-【编辑自定义列表】-【选择最开始建立的辅助表】-以后就只需要排序就能按我们希望的字段顺序出现了。

08 刷新与更改数据源

刷新

【问】：如果我想修改源表的数据，透视表会自动更新吗？

默认是不会自动更新的，需要手动刷新，如果是在原基础上修改，不增加行列的话，我们只需要刷新就可以了，如果有多个数据透视表可以选择全部刷新。如果害怕自己忘记刷新，也可以设置【打开文件时刷新数据】

更改数据源

【问】：我的数据源表修改了，刷新了也没有出现我修改的东西怎么办？

这种情况，就是我上面说的增加了行或者列啦，只是刷新是不行的，还需要更改数据源。

更高级的用法

有的时候我们并不想每次都去修改数据源，那太过于麻烦。又怎么办呢？

把数据源把设置成“表格”就可以解决这个问题。

只要把数据源表设置成“表格”，不管增加行还是列都不需要再去更改数据源，只需要刷新即可（注意，只针对将数据源更改为“表格”之后建立的透视表有效，这也是为什么在动图的例子里小奚要重新建透视表的原因）。

并且通过动图可以看到，设置成“表格”后，如果在首行输入公式都不需要下拉，会自动匹配。

09 透视表的复制与删除

【问】：我想复制或者删除透视表怎么办？删除必须要把整个sheet删除掉吗？

复制透视表的情况其实蛮常见的，因为有的时候选取的数据源是相同的，需要做不同维度的汇总分类，如果不想重新新建sheet，那么复制透视表后在这基础上更改字段是最好的方法。

只需要全选透视表，复制粘贴即可。

删除透视表只需要全选透视表，直接按detele键就能全部删除。

中篇：美观与布局

10 透视表的三种布局

【问】：做为一个有追求的员工，只会Excel透视表默认的呆板展示方式简直不能忍，好吗？小奚呀，为什么别人的透视表和自己的就是长得不一样呢？

那我们就要从透视表的三类展示姿势开始说起了，这三类布局分别是：压缩形式、大纲形式、表格形式。在哪里找到这三类布局呢？

【设计】选项卡-【布局】菜单栏-【报表布局】（注意哟，给透视表穿上美丽外衣的大多数功能都是在【设计】选项卡实现，这里也是我们今天的主要阵地，大家可以自己研究研究这个选项卡的内容噢!）

压缩形式

有眼尖的同学已经发现啦，其实压缩形式就是我们Excel默认的透视表格式，它主要的特点呢就是：

无论叠加多少个行字段，都只占一列。如果对这个概念还不是特别明白，可以多和下面讲到的两个布局方式做对比，相信你很快就能明白啦。

大纲形式

大纲形式与压缩形式最重要的区别就是：大纲形式有几个行字段就会占几列，即行字段会并排显示，就如我们例子中的行字段有三个，那么大纲形式的布局就会占三列而不像压缩形式只占一列。

另外，大纲形式的分项汇总显示在每项的上方。

表格形式

表格形式的透视表是小奚最常用的一种形式。它的主要特点呢是：

1、与大纲形式一样，行字段会并排显示，有几个行字段会占几列；

2、与大纲形式不同的是，表格形式是有表格的（好像在说废话，记得看图找区别噢）；

3、与大纲形式第二个不同是表格形式的分项汇总是在每项的下方，而大纲形式是在上方。

以上的三种布局形式的特点，你都了解了吗？

11 显示和隐藏分类汇总

【问】：小奚啊，你的例子里面，我只想看每个销售细分到各省份客户的销售额，并不想看销售汇总的销售额，并且那些销售汇总放在里面看得我眼花，怎么解决呢？

嗯，确实是这样，有的时候我们并不需要看分类汇总，但是透视表会自动显示，有的时候我们甚至都不需要看总计，那么怎么隐藏和显示分类汇总和总计呢？还是在【设计】选项中哦！

看完动图大家应该比较清楚在哪里显示和隐藏分类汇总和总计了吧？

不过细心的同学应该已经发现动图里一个小问题了吧？在这里小奚要考一考大家哟！

【提问】：为什么小奚选择的是在组的顶部显示分类汇总，最后Excel还是在组的底部显示的分类汇总呢？要结合我们前面讲的内容哦，知道答案的同学请大声在留言区告诉我吧！

12 标签项重复显示

【问】：对于大纲形式布局和表格形式布局，因为行字段是并排显示，特别是前面的行字段，常常一个就对应了后面多个字段，让表格不好看，怎么优化呢？

哇！这个问题好，在这里小奚要告诉大家两个优化的方法：第一个是让我们的标签项重复显示，第二个是合并行标签。

先讲让标签项重复显示：【设计】选项卡- 【布局】-【报表布局】-【重复所有项目标签】。（注意，标签重复项只对大纲式和表格式有效，对压缩式无效，想想为什么？）

13 合并行标签

合并行标签也是回答上面的提问，这是另外一个优化行字段并排显示的方式。

当然，大多数的同学可能会对合并行标签更感兴趣一点，因为小奚发现，大多数的同学在操作Excel的时候，对合并单元格尤为热衷。（虽然合并单元格一直被称为Excel处理数据时的一大杀手）

好了，废话不多说，直接上操作：【右键】-【数据透视表选项】-【布局和格式选项卡】-【合并且居中排列带标签的单元格】（注意：合并行标签只对表格形式布局有效，对大纲式和压缩式无效，不信你可以试试哟！）

14 插入空行间隔

【问】：小奚啊，我们公司的透视表数据很多，老板说看得他密集恐惧症都要犯了，该怎么办呀？（同学，你是认真的吗？）

我们在每一项之间插入空行，对于透视表数据很多的情况可能会有所帮助。

具体操作：【设计】选项卡- 【布局】-【空行】-【在每个项目后插入空行】

对于插入空行，在展示的时候会比较好，因为看起来让人不那么累。但是在日常处理数据的时候，空行会带来一些麻烦，所以处理数据时还是建议大家不插入空行哟。

15 取消字段前+/-符

【问】：透视表行标签前面总是有+/-符号，看起来有些丑，可以隐藏吗？

当然可以呀，上操作：【分析】选项卡-【显示】-【+/-按钮】

16 刷新后格式保持不变

【问】：前两天一个同事一脸悲痛地来找我，说透视表的排版布局我都做好了要给领导看，行高和列宽都需要固定，不能变，但是每次我一刷新透视表列宽和格式就全变了，要崩溃了。

不知道工作中你是否也遇到了这样的情况呢？不要心急，一招就能帮你搞定：

【右键】-【数据透视表选�...

我们经常会遇到这样的情况，连续的多列数据，我们需要

将他们分组求和每隔几列取值或求和

你是怎么做的呢？还在用最原始的方法吗？

让我来告诉你个方法。只需要输入一个公式，拖动复制就可以全部搞定！省事省力还不易出错。

问题与公式 1

如下图所示，在A1:L6的区域内有每个月的销售数据，我们需要将每行数据按照季度汇总，也就是每三个单元格作为一组进行求和。

我们在M2单元格中输入以下公式：

=SUM(OFFSET($A2,,(COLUMN()-COLUMN($M$1))*3,,3))

然后向右、向下拖拉复制公式填充，这样就完成了所有的求和了。简单快捷吧？下面让我们来详细了解一下这个公式。

公式详解之Offset

这个公式里面用到了三个函数，SUM，OFFSET，COLUMN。

OFFSET函数的语法是OFFSET(reference, rows, cols, [height], [width])，它根据指定的参数来返回一个单元格或单元格区域的引用。也就是从一个起始单元格或区域开始，向上或向下，向左或向右移动几行或几列，然后再返回一个设定了高度和宽度的区域。

函数的最后两个参数用来指定返回区域的高度和宽度，如果省略，则返回的区域大小和第一个参数reference的大小一样。

如下图所示，在A1单元格输入公式=OFFSET(A1,4,1)

表示从A1单元格向下移动4个单元格，再向右移动一个单元格，就跳到了B5单元格。这里没有指定最后两个参数，则表示移动后的区域还是1行1列，那么返回的就是对B5单元格的引用。

公式详解之Column

COLUMN函数是用来返回列号，如果不加参数就表示返回当前单元格的列号。示例中COLUMN($M$1)就是表示返回M1单元格的列号，即13。

COLUMN()-COLUMN($M$1)，当在水平方向上拖动复制公式的时候，就生成一个序列0，1，2，3，乘以3就得到0，3，6，9。

我们这样做，是因为我们需要从A1单元格开始，向右移动0列、3列、6列、9列，从而定位到每个季度的第一个月，然后再设置移动后的区域宽度为3列，这样就得到了一个1行3列的区域，也就是每个季度的区域。

所以随着我们从M1单元格开始向右拖动复制公式，

OFFSET($A2,,(COLUMN()-COLUMN($M$1))*3,,3)

生成了对每组3个单元格的引用，再加上SUM函数就可以得到每个季度的和了。

公式详解之绝对引用与混合引用

提醒大家一定要注意公式中的绝对引用和混合引用。

$A2是混合引用，当拖动复制公式的时候，总是固定在A列，行号则根据公式所在的行号变化，这样可以保证我们的Offset总是从A列开始移动；

$M$1是绝对引用，不管将公式复制到哪儿这个地址都不变，这样COLUMN()的结果是变化的,COLUMN($M$1)的结果是不变的，最终才能生成等差序列。

公式中使用COLUMN()-COLUMN($M$1)的用意在于，当L列和M列中间插入其他信息时，我们就不需要更改公式了。否则如果有绝对数字的话，别人一改你的表格，公式计算结果可能就不正确了。

问题与公式 2

同样的例子，假如我们想要取一、四、七、十这几个月的值该怎么做呢？

如果是一次取一个单元格的值的话，只需要OFFSET就可以了，不需要SUM了。

在M1单元格输入以下公式：

=OFFSET($A2,,(COLUMN()-COLUMN($M$1))*3,,1)

大家注意，最后一个参数变成了1，这个1就表示区域的大小是1列，倒数第二个参数省略了，表示行号跟起始区域的大小一样，都是1行。

Hi，我是秋小叶~

我们在学习工作中难免会遇到一些奇葩表格，比如应该写在一列里面的内容，偏偏被拆成了两列……

碰到这种奇葩数据，压根没办法正常统计计算，只能先调整成正常的形式。如果你只会复制粘贴，那可得忙半天，甚至熬夜通宵才能搞定。

而如果你掌握一些整理数据的小技巧，那可就不一样了。别人半天的工作量，可能你只需要 10 秒！

今天 King 老师就来分享 6 个整理数据的万能公式。

01.行列互换

需要将数据的行和列对调过来时，可以用 TRANSPOSE 转置函数。

万能配方：

TRANSPOSE是数组函数，必须先选中区域，输入公式后按【Ctrl+Enter】完成输入才能生效。

案例第 10 行中因为源数据没有更多数据，产生了错误值，这些错误值不能单独删除，因为数组区域是一个整体，必须同时修改。

其实，普通的行列互换转置，用选择性粘贴更简单，还可以保留源格式。So，函数公式虽好，可不要贪多哦 ~

02.一行转多行

想要把一行数据转换成多行数据怎么办？比如，转换成固定 3 列的多行数据：

万能配方：

这是一个万能的函数公式，你不需要懂太多，只要会改参数就能套用。

=INDEX($A$1:$G$1,1,(ROW(A1)-1)*3+COLUMN(A1))

把红色的数据区域换成你的数据区域

把 3 换成一行希望填写的数据个数，比如一行 4 个，就换成 4。

03.一列转多列

相对应的，如果想要把一列数据按固定的行数转成多列怎么办呢？还是一样的配方，稍微改造一下公式结构，就能能套用：

万能配方：

这同样是一个万能的公式结构，只需要改变两个参数，就能套用

=INDEX($A$1:$A$7,ROW(A1)+(COLUMN(A1)-1)*3,1)

把红色的数据区域换成你的数据区域

把 3 换成一列希望填写的数据个数，比如一列 2 个，就换成 2。

04.多行转一行

一行转多行可以，那反过来多行转一行行不行？当然可以，看效果：

万能配方：

这里用的公式看起来更复杂了，但是要做的事情反而更简单了，只需要将下方公式中 3 个一样的数据区域，统一换成你要转换的数据源，就可以直接套用。

=INDEX($A$1:$B$3,INT((COLUMN(A1)-1)/COLUMNS($A$1:$B$3))+1,MOD(COLUMN(A1)-1,COLUMNS($A$1:$B$3))+1)

05.多列转一列

你应该已经猜到了，多列转一列，也很简单：

万能配方：

照猫画虎似的，将下方公式中的标红数据区域统一换掉，就能直接套用

=INDEX($A$1:$B$3,MOD(ROW(A1)-1,ROWS($A$1:$B$3))+1,INT((ROW(A1)-1)/ROWS($A$1:$B$3))+1)

06.交叉转换

下面这个表格中的数据就比较棘手了，所有的人员信息都分成了两列，一列是人员信息类别的名称，一列是具体的信息。怎么把他们转成一个人的信息为一行的数据记录呢？

这是一个函数公式的高级应用：

万能配方：

案例效果中涉及 3 个函数公式，都是非常经典的套路。

利用 COUNTIF 函数自动分组

=COUNTIF(数据区域,条件)

案例公式COUNTIF($B$2:B2,$B$2) 中第一个参数 $B$2:B2 表示锁定起点为 B2 ，但终点跟随公式自动变化的动态区域；第二个参数则是计数条件。公式含义为，计算 B 列第 2 行到当前公式所在行中，值和 B2 相等的单元格数量。结果就是每一行信息属于第几个人的。

利用LOOKUP 函数实现多条件查找匹配

=LOOKUP(1,0/((条件1)*(条件2)*(条件N)),匹配区域)

案例中的公式为 Lookup(1,0/(($D$2:$D$14=$F2)*($B$2:$B$14=G$1)),$C$2:$C$14)。用到了两个条件，D 列中的数据等于公式所在行的组名,B 列中的数据等于公式所在列的标题。返回同时满足 2 个条件时，C 列中的数据，也就是每一个人的具体信息。

利用 IFERROR 屏蔽错误值

=IFERROR(原公式,出错时返回另一个结果)

当原公式结果出错时，让整个公式结果等于横杠符号。实现暗度陈仓的效果。

这些技巧，是不是很好用呢？

行列互换

一行转多行

一列转多列

多行转一行

多列转一列

交叉转换

工作或生活中有没有遇到非常多的数据，鼠标滚动半天都看不到底？现在我们可以通过给数据分组的方法来让数据看起来更方便，接下来就一起学习下如何给数据分组吧。

我们以下面这个图表为例，来为大家演示下如何分组

第一步：选中需要折叠分组的数据，点击菜单栏中的【数据】找到【创建组】

第二步：在跳出的弹窗中根据实际的表格样式选择“行”或 “列”进行折叠（图中我们是对行进行分列，所以我们选择“行”）

第三步：选中“行”或“列”之后点击【确定】我们就分组完成了。分组完成后在表格最右侧会出现一个“-”和“+”号，点击这个“-”号 就能对数据进行折叠，点击这个“+”号 就能对数据进行展开，让数据看起来更方便。

备注：本示例是演示的对“行”进行分组，大家可以尝试对“列”分组，让自己在以后的工作或生活中更方便的对数据进行查看和总结。