转载自百家号作者：解晴新生

工作中，有时候思路和方法很重要。一些问题，有的人解决起来很快，有些人却要加班加点。

譬如下面的这个问题：一个单元格中有多行内容（用“Alt + Enter”换行），但是，老板希望将这些内容拆分到多个单元格中，怎么办？

笨办法

你可以双击单元格，接着选中所有的内容，按“Ctrl + C”复制，然后选择新的单元格，按“Ctrl + V”粘贴，这样就可以自动将多行内容拆分到多个单元格中了。最后再给这些单元格添加边框即可。

但是，如果数据很多怎么办？这样一个一个复制，不是要加班到天亮了吗？

凡是需要多次重复的操作，也必然有更高效的办法。有人于是想到了函数和VBA，但又觉得有点复杂。其实，我们只要借助word，不论你有多少数据，都可以在1分钟内解决。

好办法

将Excel中的表格复制到Word中，然后再将Word中的表格复制回Excel就可以啦。

接着，如果你使用的是wps，可以选中第一列，点击“拆分并填充内容”，即可实现单个单元格的多行内容拆分到多个单元格中。

而如果你使用的是Excel，可以先“取消单元格合并”。

然后在数据上方添加一个空白行，在E2单元格输入公式“=IF(C2="",E1,C2)”，然后填充公式到其他单元格。

这个公式的意思是：如果同一行左侧的C列单元格中有内容，则填充这个内容；如果同行的C列单元格没有内容，则填充E列上一个单元格的内容。用这种方法补齐空白，比网络上常见的“分类汇总+定位空白”法要高效得多。

最后，将公式获取到的结果复制到C列，老板的要求就完成啦。

所以，尽管老板给你的是一份表格，最后的结果也是要在表格中呈现，你仍然可以借助其他工具。

相关阅读：《WPS：一列转多列和多列转一列》

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

Excel怎么设置只提取指定行中的数据？有些时候我们需要从一个excel文件中的数据库中提取指定的行或列中的数据。例如如图示，是国内所有上市公司的行业统计。但是现在我们只需要其中部分上市公司的行业统计，我们怎么办呢，下面分享一个技巧，需要的朋友可以参考下

有些时候我们需要从一个excel文件中的数据库中提取指定的行或列中的数据。例如如图示，是国内所有上市公司的行业统计。但是现在我们只需要其中部分上市公司的行业统计，我们怎么办呢，是一个个查找，然后复制吗，当然不是。我们可以做一个表格，只需要把我们需要的公司代码列复制进去就会自动显示其行业统计情况。也就是从浩如烟云的数据中，提取需要的数据对应的行中的数据。下面就以我刚刚解决的这个问题为示例详细说明一下。

1、打开源数据文件，把首行中的对应的数据，复制行到空白位置。

2、选中J2单元格，单击编辑栏中的"插入函数“，如图示。

3、弹出”插入函数“对话框，设置其类别为”逻辑“，并选择列表框中的IF选项。然后确定。

4、在弹出的”函数参数“对话框中，设置Logical_test参数为”I2=“”，将Value_if_true设置为“”，也就是空白内容。最重要的一步来了：将Value_if_false参数框中输入“VLOOKUP(I2,A2:B2846,2),然后确定。这个步骤是什么意思呢。就是用if函数判定I2单元格中的内容是不是不是空白。若是空白，那么J2单元格也是空白。若不是空白，就执行VLOOKUP函数。在A2:B2846区域中（因为源数据不改动，所以这里用的是相对引用）查找I2单元中的内容，找到后，在J2单元格中返回所找到的内容所在的行的第二列中的数据。

5、经过第4步后，我们在I2单元中输入任意证券代码就会在J2单元中显示其行业了。那么，现在我们需要的是在I列粘贴进去我们需要的证券代码后，其后会出现源数据所对应的第一行中的内容。所以要接着第6步。

6、选中J2单元格，注意是J2，不是I2哦.(I2是我们输入证券代码的地方），鼠标移动到单元格右下角，出现十字光标时，按住鼠标左键向右拖动到O2单元格。如图

7、这个时候你就会发现原来源数据中的C2-G2单元格中的内容就会自动填充到K2-O2单元格中。这个时候，如果我们需要的数据是按源数据中的数据从上到下排列的，就可以不用修改了。直接重复第6步中的方法，把J2-O2每个单元格下拉填充就完成了。如图示，先把需要的数据列复制到I列中，然后依次下拉填充。

8、但有时候，如果我们需要的不是按照源数据中的排序，比如说，回到我们现在用的这个例子，如果在I3中，输入000002就会出错。如图示。这是为什么呢，看一下J3中的公式就明白了。j3中的函数为VLOOKUP(I3,A3:B2847,2))，也就是搜索从A3到B2487区域中的内容。没有包含A2，所以我们输入000002在源数据中找不到。所以使用这个方法前，要先对需要的数据按源数据进行排序。不然，是不准确的。

10行代码提取复杂Excel数据

下面我们将分情况讨论如何利用集算器将Excel数据进行结构化。文中用到的函数请参看集算器文档《函数参考》。

1. 普通行式

先看最简单的情况：如下图所示，Excel文件中第一行是列标题，从第二行开始，每行是一条数据记录。

集算器处理这种文件也非常简单：

A1 打开“学生成绩表.xlsx”文件并导入成序表，选项@t表示文件第一行是列标题

A2 连接demo数据库

A3 将A1中的序表存入到demo数据库的xscj表中，由于表中的列名和序表中的字段名一样，所以只需指定数据表名即可。update函数的更详细用法请查阅函数文档。

A1中得到的序表如下图所示：

A2、A3两步连接数据库和将序表存入数据库的方法是通用的，所以后面的例子中将不再写这两步，只关注于如何把Excel中的数据结构化成序表。

2. 多行表头行式

大多数时候，Excel文件都不会象上例那么简单，表头往往比较复杂，有表名、项目名、页码、填表人、填写日期等等。比如这个样子：

对于这种表，我们在读取时就要跳过表头，直接从数据行开始读。

A1 打开文件并导入数据成序表，参数“1,5”表示读第一个 sheet，从第 5 行开始读，一直读到文件结尾

A2 将 A1 中读到的序表列名依次改为“序号、项目编码、项目名称、计量单位、数量、单价、合价”，即要存入的数据表的列名。

运行后 A2 中的序表如下：

3. 自由格式

有时Excel文件的数据并不是网格式的规则表，而是字段名后紧跟着字段值的自由格式，如下图的雇员信息表：

每个雇员信息占据9行，依次往下排列。对这种文件，该怎么结构化呢？请看：

A1 创建列名为“雇员 ID, 姓名, 性别, 职位, 生日, 电话, 地址, 邮编”的空序表

A2 打开 Excel 数据文件

A3 定义雇员信息所在单元格列号序列

B3 定义雇员信息所在单元格行号序列

A4 用 for 循环读取每个雇员信息

B4 A3.(~/B3(#))先算出当前雇员单元格编号序列, 再读出这些单元格值组成雇员信息序列。第一次循环时为 [C1,C2,F2,C3,C4,D5,C7,C8]，第二次循环时为[C10,C11,F11,C12,C13,D14,C16,C17]……每次行号加 9。$[A2.xlscell(] 与 "A2.xlscell(" 相同，都是表示一个字符串，它的好处是在 IDE 中编写程序时，如果 A2 单元格的编号发生了变化，$[A2.xlscell(]中的 A2 会自动变化，比如在 A2 前插入了一行，这个表达式就会变成 $[A3.xlscell(]，而用引号的话，就不会自动变了。

B5 判断雇员 ID 值是否为空，为空则退出循环，结束运行

B6 将一条雇员信息存入 A1 序表尾

B7 让雇员信息的行号序列都加上 9，读取下一条雇员信息

运行后得到的 A1 序表如下：

4. 交叉表

Excel中还有交叉表格式的数据，如下图：

数据结构化程序如下：

A1 打开文件并导入数据成序表，参数“1,2”表示读第一个 sheet，从第 2 行开始读，一直读到文件结尾。选项 @t 表示开始行是列标题。

A2 由于第二行第一个单元格是图片，读的数据为 null，第一列没有列标题，所以将第一列列名改为运货商。

A3 以运货商为分组，对序表数据进行行列转换，选项 @r 表示将列数据转换为行数据，转换后新的列名分别为“货主地区”、“订单数量”。

运行后得到的 A3 序表如下：

5. 主子表

在下图所示的员工信息登记表中，除了有员工本人的信息外，还有他的家庭成员信息。每个 sheet 保存一个员工的相关信息，所以有多少员工，就有多少个 sheet。

对这种主子表结构的数据，需要创建两个序表分别保存主表和子表的数据，集算器程序如下：

A1 创建列名为“身份证号, 姓名, 性别, 出生日期, 民族, 手机号, 部门, 家庭地址, 婚姻状况, 入职时间”的空序表，用于保存主表员工信息

A2 创建列名为“身份证号, 姓名, 关系, 工作单位, 联系电话”的空序表，用于保存子表员工家庭成员信息

A3 定义主表员工信息所在单元格序列

A4 打开 Excel 数据文件

A5 循环读取 Excel 文件各 sheet 数据

B6 读取员工信息序列

C6 将 B6 读取的员工信息保存到序表 A1

B7 从第 6 行开始读取员工家庭成员信息，只读指定的“家庭成员, 姓名, 关系, 工作单位, 联系电话”5 列

B8 将 B7 序表的家庭成员列改名为身份证号

C8 为 B8 序表的身份证号列赋值为员工信息中的身份证号

B9 将 B8 中的员工家庭成员信息保存到序表 A2

程序运行后，序表 A1 如下图所示：

序表 A2 如下图所示：

上面这些情况基本罗列了常见的 Excel 数据格式，如果遇到更复杂的文件，也可以灵活使用例子中的技巧予以应对。简单总结一下，集算器提供了非常灵活的在 excel 文件中定位和读取数据的功能，既可以成片读取网格数据，也可以精确定位单元格进行读取。再结合特有的“序表”对象，以往需要编写数千行代码才能完成的 Excel 数据结构化入库工作，现在只需要不到 10 行代码就可以胜任，简单情况下甚至只需要 2、3 行代码

在我们的Excel中，我们有时候需要用到隔行插入。

有两种方法，我们上次讲过一种就是排序法，今天我们就讲一下定位法：

隔行插入定位法是通过定位的方法选取特定单元格，然后再批量插入。

需要每十行隔开

下面就来看看我们是怎么做的吧！你也可以边看边试试哦！

现在我们酒店吃饭分桌为例，需要将公司的人员每10人分成一组

上面，我们需要将我们公司的人员每十人为一组。

就等于每十人我们需要插入一行空行，作为分隔。

①添加辅助列，在需要隔行的下方输入“1”

（此处A1、B1为标题行，向下数10个人，就是从C12开始，之后个位数每逢2输入一个数）

②F5（定位快捷键）——定位条件——常量

③鼠标右键，插入行——整行

这样隔行插入是不是快多了！

特别是数据比较多的时候，省时省力！

隔行只需要设置数据填充为公差，你也可以动手试一下哦！

更多Excel学习和问题交流请加群！558669700

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

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

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

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

问题与公式 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行。

如何将Excel中同一列的重复数据筛查出来？

在数据处理中，如果是对少量数据进行处理的话可以通过手动计算等方式进行，但是如果遇到几千上万的数据的时候就比较麻烦了。

小编遇到这样一个麻烦，表格中的某列数据有多重复的数据，我需要把所有重复的数据提取出来进行分析。

以excel2007版本为例讲解

第一步：选中A列数据，单击“开始”菜单，选择“条件格式命令”下面的“突出显示单元格规则”—“重复值

”如图：

第二步：将重复值设置为某种颜色，小编选择的是红色文本（即字体为红色）。如图：

第三步：对A列数据进行排序，排序依据选择“字体颜色”，次序选择颜色。如图设置：

排序后的结果

操作结果是把所有重复数据标记了颜色并通过排序的方式置顶，当然也可以通过筛查功能，将颜色数据筛查出来。

对于成千上万的大数据的处理，这个方法还是很有效果的。

到这里就结束了，小伙伴们觉得文章有用欢迎关注、收藏、评论。

大咖们不喜勿喷哦。

Excel有一些小技巧，简单又实用，隔行插行技巧就是其中之一，学会了隔行插行技巧，你就可以举一反三：隔两行插一行、隔五行插一行、隔十行插一行......都不是问题，想隔几行加一行，你说了算！

隔行插行多应用于工资条，下面我们以为工资条 插入空行间隔为例，教大家使用Excel隔行插行技巧。

下图为一份排列紧密的工资条excel表：

　　excel隔两行插一行之后的效果如下：

怎么样，是不是忽然变的很清爽。不要犹豫，一起来动手制作，俗话说：光看不练假把式，要动手练习，纸上谈兵谁都会，真正做了才能学到东西。

第一，在数据相邻的空白列输入序列数字：1、2、3……，截图如下：

第二，接着在K11开始的后面10行输入等差数列：2、4、6、8……20。

解释：我们需要隔两行插入一行，起始数字就是2，然后以2的倍数的数字下拉。如果是想实现每隔3行插入一行，就可以输入类似3、6、9、12这样的等差数列。

第三，执行“数据——排序——扩展选定区域”排序。

第四，弹出的“排序”对话框，设置截图如下：

OK，完成隔行插行任务，删除不需要的数字辅助列即可。

Excel有着很多很多的小技巧，我们会不定时的为大家更新好用的小技巧。对啦，不要等了，赶紧去练习吧。

Excel中的排序功能，除了排序，也能做很多其他的事情。让我们先看看案例。

从一个数据很多的表格中查找部分数据

领导要开会了，这时小王接到了一个任务。老板：我还有5分钟就要开会了，会前，请把这50个客户的联系方式准备好给我！小王：5分钟，从1000个人中提取50个人的电话？老板：是的。

5分钟！你可以吗？

用排序，1分钟就可以完成了！比Vlookup还好用。

步骤1：选中包含很多数据的大表格，选择按照“姓名”排序，次序设置为“自定义序列”。

步骤2：讲要查找的人员名单复制到“自定义序列”窗口中，“添加”为新序列，点击“确定”。

这样，我们需要的人员就都排列到表格最前面，人员的顺序也和老板要求的一模一样。

注意：

要表格中的姓名无法直接复制到自定义序列窗口中。你需要先把这些名字复制到记事本，然后再从记事本中复制到自定义序列窗口。如果你不希望改变原始表格的排序，可以先添加一个序号列，查找到需要的信息后，再按序号列重新排序。隔行插入数据或空行

老板：小王啊，把这份表格翻译一下吧。小王想，我得在每行后面自动添加一行，这样逐行翻译部容易出错。

这就要用到辅助列啦。在数据右侧输入奇数列，数据的下方输入偶数列，然后按照数值大小排列即可。

隔两行插入数据或空行

小王：不会函数，怎样快速制作工资条？

和隔行方法一样，只是创作辅助列的时候，需要创造3个序列。按照我们的需要，分别给第一、第二、第三个表输入数字“1”、“2”、“3”，然后在第一、第二、第三个表输入“4”、“5”、“6”，然后再分别填充每个表格的序号，最后再排序就好啦。

所以，不管隔几行插入数据，都会了吧。

隔行提取数据

小王翻译好了，怎么把奇数行的中文和偶数行的英文分开呢？

同样还是要自己创造一个辅助列。在第一个单元格输入“1”，第三个单元格输入“2”，然后选中前4个单元格进行填充，最后按照辅助列数字大小排序。这样，就可以实现隔行提取数据啦。

同样的，隔3行提取一行，也可以哦。

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

在办公室职场数据处理中，很多时候，要用到隔行取数或是隔列取数，下面小编分享下如何隔列取数要求：

关注小编，每天共同学习职场办公应用技能

步骤1，在F4单元格输入公式=SUM(VLOOKUP(A1,A1:O1,ROW(1:8)*2-1,FALSE))，而后三键结束，得出的，就是奇数列的和

公式解释：

1，这里用到VLOOKuP函数，VLOOKUP(要查询的值,查询区域，返回区域内的第几列,查询方式，精确查找还是模糊查找),

2，ROW(1:8)*2-1，因为要取奇数列的值，我们必须在VLOOKUP第三个参数内，形成一个1,3,5,7,9的数组，而且不能超出第二个参数的范围，所以我们用到这个公式，按F9键就可以看出，得到的结果为{1;3;5;7;9;11;13;15}而后外套VLOOKUP函数，相当于(VLOOKUP(A1,A1:O1,{1;3;5;7;9;11;13;15},FALSE)，通常情况下，第三个参数为单个值，因为求和，这里才会需要一个数组，

3，选择VLOOKUP函数的区域，得到的结果是{2;4;6;8;10;12;14;16}，z这里是ROW(1:8)*2的到的结果，VLOOKUP函数确定后，只能得到一个值，这里是一个一个的数值，并不能得到汇总求和的结果所以我们才外套一个sum，就相当于把所有值加起来，得到结果。

以上为小编个人理解，如有错误，欢迎探讨！欢迎关注小编！

我们用排序就可以实现隔行或隔列取值，方法很简单，只要给奇数行填充数字序列，偶数行保留空白，然后按照辅助的序列排序就可以了。不过，这一篇介绍的是如何用index函数实现隔行或隔列取值。

为什么可以用排序法，还要学习公式法呢？

这是因为，可以用公式做一个模板。以后就只要替换数据，就会自动获得隔行或隔列的数据，连同基于这些新数据所做的图表和各种汇总都可以自动更新，就像上面这张GIF图所示的一样。

而如果你用排序法，每次都要操作一次。这无疑增加了你加班的概率。

废话不多说了，直接进入主题。

隔行取值

隔列取值

公式注意事项

index函数共3个参数，第一个参数表示操作的范围。这个范围的第一行就是index函数指向的第一行。也就是说隔列取值的公式中的第一行是“刘庭”的数据，而不是表格的第一行。

index第2、3个参数表示行数和列数，这个行数和列数指的是相对于第一个参数范围是第几行第几列。

第一个参数范围，必须使用绝对引用，这样填充公式到其他单元格时才不至于出错。

输入左上角第一个公式，向右或向下填充公式，当公式返回结果“REF!”时，表明所有的结果都已经取完了。

相关文章：WPS Excel: 用index函数制作动态图表

谢谢阅读，每天学一点，省下时间充实自己。欢迎点赞、评论、关注和点击头像。