之前，在牛闪闪的日子Q群，一个做酒店生意的小伙伴提出了这样的问题：

事实上，这个操作的思路在之前的技巧已经讲过，就是利用编号排序的功能，自动隔开数据。

具体操作如下

在数据列表的下方，填充8个1，8个2, 8个3…..以此类推。如下图所示：

接着只需要对序号列进行快速排序即可。

看动图操作

看着是不是很简单，瞬间将数据系列隔开8行。但大家有没有想过一个问题，如何产生8个1,8个2,8个3的数据系列呢？

下图红线处

很明显手工直接拖拽是不会出现的，那应该如何快速产生呢？方法很有多，牛闪闪推荐一种方法，利用求和快速产生。

方法如下

当产生这个数据系列后，再选择型黏贴贴成值之后，在利用排序就快速分列数据了。

总结

利用序号编号辅助分离的技巧绝对堪称职场经典教程，但对于一些特殊的数据分离，比如本例的要隔8行的操作，则需要对编号系列的产生提出更高的要求，这一定是需要在职场中进行积累的。

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(原公式,出错时返回另一个结果)

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

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

行列互换

一行转多行

一列转多列

多行转一行

多列转一列

交叉转换

一、初步设计账本

首先罗列出你日常生活中的开销，可以分为几个大类，如日常生活，固定支出等等，小编自己用的记账本制作如下：

新建Excel，编辑“账本”标题和其他内容设计。主要分为三个区域：

最左边：从A-H列，为记账区（表头分别为记录时间-类型-金额-子类型-子类型-支付来源-支付来源-备注）

中间为记账类型区：从I-J列，规范和快速录入数据（子类型设置）

最右边：从K-P列，用于各类数据统计并展示（当月时间-金额-资产名称-资产名称-原始本金-实际资产）

二、具体设置

【一、设置标题样式】

1、选中A-P列后，在“开始”菜单中，将字体修改为“微软雅黑”。

2、将字体加粗，点击字体下方的“B”按钮，加粗字体。

3、设置字体颜色为白色，点击字体下方的“A”按钮，选择白色作为字体颜色。

4、设置填充颜色为蓝色，点击字体下方的“油桶”按钮，选择蓝色作为背景色

【二、设置边框线颜色为白色】

点击字体颜色下方“边框”下拉框，选择“线条颜色”为白色。

再次点击“边框”下拉框，选择“所有框线”。选中标题(A-P列)，并应用边框线。

【三、子类型设置】

1、选中I1、J1单元格，点击“合并单元格后居中”。

2、按照下图内容，录入“类型”名称。录入类型，有2大好处：

A、为快速录入，提供基本数据。

B、为录入规范，提供原始数据纠正作用。

【四、设置类型-支出】

支出类型分为“收入”和“支出”，在输入金额，自动判断，避免手动录入错误。

使用公式：=IF(C2<0,"支出","收入")在B2单元格输入以上公式，C2代表单元格C2，其所在列指向“金额”。

【五、设置E列子类型】

此处就是上面说的快速录入，和录入规范性，在E列作为自动录入列，通过与D列配合完成。

使用公式：=VLOOKUP(D2,$I$2:$J$15,2,0)

【六、错误处理】

针对上面公式，假如并没有在D2单元格，使用了以上公式，输入非设置内容，将显示错误信息(#N/A)，为此在上面公式的基础上，完善显示功能。

公式：

=IFERROR(VLOOKUP(D2,$I$2:$J$15,2,0),"")

公式解析：如果出现错误信息，将返回空白字符，否则，按照上一步规则输出信息。

【七、设置F、G列子类型】

同样为规范录入，使其录入信息保持一致性，沿用上面2个步骤(步骤7、步骤8)，设置好“支付来源”。备注信息则根据实际情况，对当日支出或收入做补充说明。

三、最右边功能区制作

这部分功能，主要设计：

一、预算支出，用来计划某一段时间内，预计需要用掉的资金，“预算剩余”对“预算支出”进行反馈。

二、当日/月收入支出求和，利用K1单元格的时间，计算当月总支出和总收入。

三、设置资金多维度来源，自动计算当前各类财产资金和余额。

【一、设置M、N列支付类型】

此列对应前面步骤9，M列为N列的拼音简拼，和前面设置E列子类型，作用相同。

【二、L3单元格-预算剩余】

预算剩余有两套思路，计算当月的和计算全部记录的“预算剩余”。

当月预算剩余公式：

L2-ABS(SUMIFS(C:C,B:B,"支出" ,A : A ," >= "& DATE(YEAR(K1),MONTH(K1),1),A:A,"<"&DAT E (YEAR(K1),MONTH(K1)+1,1)))

表格中所有支出计算预算剩余：

=L2-ABS(SUMIFS(C:C,B:B,"支出"))

【三、L4、L5单元格-今日收入/支出】

根据左边收入和支出详情记录信息，利用公式，自动汇总当日收入/支出金额。

使用公式：=SUMIFS(C:C,A:A,TODAY(),B:B,"支出")

计算收入，则将公式修改为：=SUMIFS(C:C,A:A,TODAY(),B:B,"收入")即可。

【四、原始本金】

此部分无需公式，设置简单。原始本金作为第一次或这以后校准资金存在。其作用代表了当前所有资产余额。已分类“现金，工资卡-老公，工资卡-老婆...”等多项。可根据自己的资产，自定义分类。

【五、实际资产】

通过资产名称，关联G列的支付来源，自动计算该资产，从原始本金，到目前为止全部的金额，这就是前面不停的强调录入规范，录入一致性的重要性。

使用公式：

=O2-ABS(SUMIFS(C:C,G:G,N2,B:B,"支出")) + SUMIFS(C:C,G:G,N2,B:B,"收入")

【六、实际资产-总和】

对所有实际资产求和，计算出总金额。

使用公式：="实际资产"&SUM(P2:P6)

换行：使用Alt+Enter(回车)可换行

四、资金安全设密码

设置excel打开密码，一定程度上防止资金信息被泄露，所谓防君子不防小人，设置密码是很有必要的。点击左上角“文件”，“信息”窗口有密码打开文件功能。

（注明：本版本为office 2010，其他版本密码设置稍有不同。）

- END -

来源：四年之约

IT之家小白课堂，小白也能看懂的教程，欢迎收藏 :)

Excel的函数公式里，VLOOKUP是经典的查找引用函数。而CP组合INDEX+MATCH，操作上更灵活，很多时候能替代VLOOKUP。

举例，你有一份客户资料表，包括客户名称、区域、省份等。现在需要在销售明细表里，填入客户名称，自动输出对应的区域、省份。效果图如下。

怎么操作？可以通过VLOOKUP函数实现，不过IT之家下面讲解的案例，主要采用INDEX+MATCH。首先看下各个函数的基本含义，以及通俗语法。

1、INDEX函数：返回表中的值。

=INDEX（在哪儿找，第几行）

2、MATCH函数：返回指定数值在指定区域中的位置。

=MATCH（找谁，在哪儿找，匹配方式）

3、VLOOKUP函数：纵向查找返回表中的值。缺点：查阅值需要位于查找区域的第一列。

=VLOOKUP（找谁，在哪儿找，第几列，匹配方式）

下面看看销售报表案例里，INDEX+MATCH嵌套函数的写法。

A3处的公式如下

=INDEX(G:G,MATCH(C3,H:H,0))

这是一个嵌套函数，先计算MATCH函数，再将结果作为参数，计算INDEX函数。

首先，MATCH在H列查找匹配，结果为4，即C3的值在H列里位于第4行。

然后将MATCH的结果4作为参数，INDEX在G列查找返回结果G4“广东”。

在销售明细表里，把这个公式往下拉，那么每次输入客户名称，就会自动输出客户所在省份。这样可以避免手动输错的问题，也能节省一些时间。

同样的案例，用VLOOKUP函数怎么写？需要将案例中的G列、H列顺序对调，保证要查找的客户名称列，在查找区域首列。

A3处的公式如下。

=VLOOKUP(C3,G:H,2,0)

如果销售表、客户表不在同一张表里呢？很简单，公式前加入“工作表名称!”即可，如下。

=INDEX(客户!C:C,MATCH(D2,客户!D:D,0))

报表里还有一个非常重要的功能：数据验证（早期Office版本里叫数据有效性）。有什么作用？可以规范输入的数值格式，同时，还能提供下拉框来选择输入数值。

如果输入的数值不符合规定，则弹窗提示无法输入。本案例中，就能避免出现“中国联通”“联通”“联通公司”这样的多种写法。当多人维护同一份表格，再也不怕大家乱输数值了。同理，品名规格列也可以做数据验证。

操作路径：销售表里全选D列，菜单栏-数据-数据验证，允许选择“序列”，来源输入下面的公式。

=OFFSET(客户!$D$2,,,COUNTA(客户!$D:$D)-1)

公式含义：销售表里的客户名称列，只能输入客户表里已记录的客户名称。注意要加入绝对引用符号“$”，保证参数数值不变。

销售报表案例表格：点此下载

在平时用Excel进行各类数据处理的时候，很多朋友都会碰到的一个问题就是数据查询，说到查询函数大家可能也会说到的就是Vlookup函数。其实在Excel中还有其他更加实用的数据查找函数公式，今天我们就来完整的学习一遍Excel中12种查询方式。

场景1：正常情况下数据查找

案例：查找出对应人员的语文成绩

函数=VLOOKUP(F5,B:C,2,0)

场景2：向左数据查找

案例：根据学号查询出对应姓名

函数=INDEX(B:B,MATCH(G5,C:C,0))

场景3：多函数条件交叉查询

案例：求出赵二第三周考试成绩

函数=VLOOKUP(I6,B:G,MATCH(J6,B$2:G$2,0),0)

场景4：LOOKUP多条件查询

案例：求出B产品在京东平台的销量

函数=LOOKUP(1,0/(B:B=F6)*(C:C=G6),D:D)

场景5：数据等级区间查询

案例：求出销售额对应的提成比例

函数=LOOKUP(F5,$B$3:$C$6)

场景6：横向纵向区间数据查询

案例：根据当月销售额及完成比例求出对应提成

函数=INDEX(C3:F9,MATCH(I4,B3:B9),MATCH(I5,C2:F2))

场景7：根据规律自动提取数值对应系数

案例：按照规律根据我们需要的数值提取出对应的系数

函数=TREND(OFFSET(B1,MATCH(D3,A2:A6,1),,2,1),OFFSET(A1,MATCH(D3,A2:A6,1),,2,1),D3)

场景8：查找符合条件的最后一个数

案例：查找出王五最后一天的销售额

函数=LOOKUP(1,0/(B:B=J4),F:F)

场景9：通配符模糊查找

案例：查找出姓王的人的销售额

函数=VLOOKUP(G5&"*",C:D,2,0)

场景10：高级匹配查找

案例：从对应完整地址中提取所在城市的提成点数

=lookup(9^9.find(A$3:A$6,A10),B$3:B$6)

场景11：查找最后一个非空的单元格内容

案例：求出对应人员最近一次缴纳社保的月份

函数=LOOKUP(1,0/(E4:E10<>""),$A$4:$A$10)

场景12：轻松实现一对多查询

案例：轻松提取部门人员全天的门禁记录

函数=IFERROR(VLOOKUP(ROW(A1),A:D,4,0),"")，首先需要在A列做辅助列，函数=COUNTIF(B$2:B2,G$2)

现在Excel关于数据查询你学会了吗？

在Excel中查看和编辑表格中的数据时，如果表格较大行数较多，而且各行中又包含许多的列，则可能会感觉表格看起来比较累，而且很容易看串行。这时候我们可以考虑通过记录单来查看表格中各行的数据，记录单是把表格中各行的数据通过对话框的方式显示出来，在记录单中还可以编辑和查找表格中的数据。下面以Excel2007为例对记录单的使用方法作个简要介绍：

●使用记录单的前提是表格最上方应有标题行，标题行中不能有空单元格，而且表格中不能有空行，否则空行下方的表格内容将不会显示在记录单中。

●记录单的功能按钮默认情况下不会显示在Excel中，需要自行进行添加，添加方法是：点击下图所示的“自定义快速访问工具栏”按钮。

●再点击下拉菜单中的“其他命令”。

●此时会打开Excel选项对话框的“自定义”选项卡，在其中点击图示的“从下列位置选择命令”处的下拉框，再点击下拉菜单中的“不在功能区中的命令”选项。

●在下方的列表中找到并且点击选择“记录单”，其具体位置可参考下图中滑块所处的位置。

●选择“记录单”后，点击“添加”按钮，则“记录单”就会出现在对话框右侧的窗口中。

●再点击“确定”按钮，即可把“记录单”功能按钮添加到Excel的快速访问工具栏中。

●添加“记录单”功能按钮后，在要查看的表格中点选任意一个单元格。

●再在Excel的快速访问工具栏中点击刚才添加的“记录单”功能按钮。

●点击后就会打开记录单对话框，记录单中显示的是表格中某一行的内容，不会显示其他行的内容，所以不会出现看串行的情况。记录单中每个文本框对应表格该行中的一个单元格。通过拖动滑块或者点击“上一条”、“下一条”按钮及按键盘的上下方向键的方式都可以逐个显示表格中的各行。

●在记录单的各文本框中可修改对应单元格中的数据，刚修改数据后，表格中的数据实际还没有发生变化，还需要通过按键盘的回车键、按键盘的上下方向键、拖动记录单的滑块、点击记录单的“关闭”按钮这几种方式之一来确认修改。修改过程中按记录单的“还原”按钮可以恢复成未修改前的数据。

●点击“新建”按钮可以在表格下方添加一个新行，在记录单中为新行中的各单元格添加数据后，同样需要通过上述几种方式之一来确认添加。要注意的是：使用“新建”按钮后，Excel的撤销按钮中会删除新建之前的操作记录，即新建之前的操作记录不能恢复了。所以通常情况下不建议使用记录单中的“新建”按钮。

●点击“删除”按钮可以删除记录单中当前显示的行。和新建按钮差不多，在记录单中删除某一行后，Excel的撤销按钮中会删除之前的操作记录，Excel的撤销按钮会变成灰色，不能恢复删除的行，所以通常情况下也不建议使用记录单中的“删除”按钮。

●点击记录单的“条件”按钮还可以在表格中查找数据，查找时支持大于、小于等数学符号。

●例如要查找表格中年龄大于30的人，可以点击记录单的“条件”按钮后，在“年龄”文本框处输入“>30”，然后点击点击“下一条”、“上一条”按钮即可逐条查看符合条件的数据。查找后需要在“条件”中清除输入的查找条件，或者关闭记录单后重新打开，才能在记录单中再次查看全部表格数据。

总之，用记录单来查看较大的表格，可以让人感觉相对没有那么累，不容易看串行，还可以查找符合一定条件的数据，与通常使用的直接查看表格的方式相比还略带了点科技感和新鲜感。但要注意最好不要在记录单中修改数据，特别是使用记录单中的“删除”和“新建”按钮，以避免无法通过“撤销”操作恢复之前对表格的修改。

目录

员工考勤记录表格模板

公司通用员工打卡考勤记录明细表

单位考勤表

发私信考勤得到模版源文件

随时需要核对Excel数据的人，上辈子一定是折翼的天使。

尤其是面对各种清单数据，如果不会点数据核对诀窍。

那就只能脸上笑嘻嘻，心里哭唧唧。

不同的人在Excel中核对数据，都有自己的招式，可谓八仙过海各显神通。但是有一个清单核对功能——记录单功能，却少有人知道，因为这个功能不在默认的选项卡里，需要自定义添加。

记录单功能，可以将表格中的数据进行分组查看，避免看错位、看花眼，还能在记录单中进行数据数据筛选、添加，专治眼神不好这种病！

如下图所示，是一张库存清单，需要根据每个商品编号，核对商品的月销量、进价、售价等数据是否正确。下面我们将用记录单功能解决这个问题。

1.启用记录单功能

单击Excel的【文件】菜单中的【选项】按钮，在【自定义功能区】选项卡中，选中任意选中卡，新建一个组。然后找到【记录单】功能，添加到新建的组中。

此时就可以在新建的组中启用记录单功能了，如下图所示。

2.用记录单核对数据

启用记录单功能后，如下图所示，在记录单中将每个商品的数据以组的形式进行显示，妈妈再也不担心我核对数据时，将数据看错位了。单击【上一条】或【下一条】就可以一一核对每种商品的数据啦。

3.用记录单筛选数据

如果不需要一一核对数据，只需要核对某类数据，可以用记录单的条件筛选功能，将这类数据筛选出来进行核对。如下图所示，单击【条件】按钮。

输入筛选条件，如这里设置筛选出补货时长大于5天的数据。

输入条件后，按下Enter键，就可以进行数据查看了。如下图所示，将所有补货时长大于5天的数据筛选了出来，单击【下一条】按钮，可以依次核对所有补货时长大于5天的数据。

4.在记录单中添加数据

在核对数据时，突然发现少了一条数据怎么办？可以直接在记录单中进行添加。如下图所示，单击【新建】按钮。

输入商品的数据，然后再单击【新建】按钮，表示要将当前输入的商品数据添加到表格中。

结果如下图所示，上面步骤中新建的数据就成功添加到表格中了。

在Excel中，向一个数据量较大的表单中插入一行新记录的过程中，有许多时间白白花费在来回切换行和列的位置上。而Excel的"记录单"可以帮助您在一个小窗口中完成输入数据的工作，不必在长长的表单中进行输入。具体步骤如下：

在记录单的界面，可以对数据进行录入、删除和查询。在大型数据表中，使用记录单操作工作表中的数据，将会非常的方便、快捷，能极大的提高办公效率。

在使用Excel处理数据过程中，当碰到要输入的数据包含很多项属性，前面还好，当输入到个几十，几百行的时候，估计人都眼花缭乱，稀里糊涂的自己都不知道输入到哪个数据了。一种较常用的方式就是冻结标题栏，然后逐行输入，再慢慢向下移动滚动条，此方法虽然对于以前来说确实是一个不错的方式。但是当你掌握Excel另外一个命令——记录单，你就会发现，原来输入数据也可以这么有条理，而再也不会显得杂乱无序了。说了半天，可能你会说，我用了那么久的Excel，咋就没见过有记录单这个命令呢？今天小编就先教大家如何调出这个隐藏的贼深的命令，让它无法遁形，顺便教大家如何通过快捷键来激活该命令。

1、调出记录单命令：教程以Ecxel2013为例，在Excel主界面，单击菜单栏中的文件，然后选择最下端的选项命令，此时会弹出一个Excel选项窗口。在Excel选项窗口左侧栏中选择快速访问工具栏命令，然后在自定义工具栏中设置从下列位置选择命令参数为所有命令，在其中找到记录单命令，鼠标左键单击选中，然后单击添加按钮，将记录单命令添加到右侧栏中，单击确定键保存设置，此时我们即将记录单命令添加到了快速访问工具栏。

2、设置记录单快捷键：回到Excel主界面，我们可以在标题栏的左侧找到这个命令。此时按下键盘上的Alt键，我们可以发现有个数字标记的记录单这个命令，本教程是数字5，这就意味着以后只要按下组合键Alt+5即可激活记录单这个命令。注：这里的数字5与该命令添加到工具栏的顺序有关，例如保存按钮对应的是数字1，这说明按下组合键Alt+1即可激活保存命令。

如果对您有点帮助的话，请帮忙点个赞或分享一下再走，你的支持是我不懈努力地动力，谢谢！