欢迎关注天善智能，我们是专注于商业智能BI，大数据，数据分析领域的垂直社区，学习，问答、求职一站式搞定！

天善学院618活动正在进行中！天善学院618大礼包，大数据、数据分析与数据挖掘、商业智能、机器学习、深度学习、数据库与数据仓库、R语言、Python精品课程总有一款正中你下怀吧？全场6.18折，赠送微课。最好的投资是投资自己，名额有限，欢迎登录查看！

因为还没有熟练掌握用Ruby生成excel的技能，所以干脆学了一点VBA，写了段通过excel实时获取股票数据的代码。

方便在excel里使用各种公式计算涨跌幅。

分享如下：

效果如下：

本文作者：GalaxyRover

最新有订阅粉留言，大意如图所示，统计用的原始数据内一个部门内多个人，想整理成以人单独列表，如果手动摘录太费时间，有没有办法让Excel自动拆分提取？小编试着弄了一下，还真琢磨出来了，这里也分享给所有人，以后有类似的需求，大家都能够用Excel自动拆分，不需要手工摘录了。

点击任意数据（人员列表）然后切换选项卡到“数据”，选中“从表格”，弹出的数据来源默认即可，点击确定，生成带有选单的表格，并弹出查询编辑器。

小提示：

查询编辑器即Power Query，此功能需要以插件形式单独下载，安装到Excel 2010或Excel 2013版本中才能使用，而Excel 2016直接集成了该功能，所以在界面显示上也有所区别，本文以Excel 2016为例，2010、2013的用户可以根据实际情况操作。

在弹出的查询编辑器中，单击“列2”选中，切换到“转换”选项卡，点击“拆分列”中的“按分隔符”。

单击“选择或输入分隔符”下拉选单，选中“自定义”，并输入顿号“、”（注：本例中是人员是以顿号分割）点击确定。

这时，查询编辑器的数据已经变为多个列，人员名单已经独立分拆。我们特意留了一个错误，列2.2的第6行是以逗号“，”分割人员，所以没有被提取出来，所以建议大家在转换提取时，要统一分割符号。注意，这里使用分列和在工作表中使用分列有所不同。如果需要分列的右侧列中还有其他的内容，会自动扩展插入新的列，右侧已有数据列自动后延。而在工作中使用分列，右侧有数据时则会被覆盖掉。

接下来，按住Ctrl并单击所有列，在“转换”选项卡中点击“逆透视列”。

新转换的列表已经改成以人员为基准数据的拆分方案，但是多了一列“属性”单击选中后右键选择“删除”。

最后，点击“文件”中的“关闭并上载”，回到Excel界面，这样再看是不是已经通过这一系列的操作，数据提取转换过来了？部门重复罗列，但是人员名单为单独独立存在了。

——电脑爱好者

施老师：相信许多人经常会遇到一些不懂Excel的同事和老板，将一份好好的资料在Excel中填写的是乱七八糟，甚至有时在一个单元格中弄了N多个信息，又是中文，又是英文，又是数据。如果数据少的话可以手动一个一个的拎出来，如果数据多的话就麻烦了。今天就由宁双学好网施老师给大家分享一个不错的VBA，可以将单元格中混合数据包含中文、英文、数据信息单独提取出来。

混合数据预览

先看看混乱的数据吧！也许你的数据比这更乱，不过也没关系，我们可以轻松提取我们想要的中文或英文或数据。

开始提取

一、首先是【开发工具】-【查看代码】-【Visual Basic】；

二、在弹出的界面中点击【插入】-【模块】；

三、将下列代码复制到模块中。

Function MyGet(Srg As String, Optional n As Integer = False)

Dim i As Integer

Dim s, MyString As String

Dim Bol As Boolean

For i = 1 To Len(Srg)

s = Mid(Srg, i, 1)

If n = 1 Then

Bol = Asc(s) < 0

ElseIf n = 2 Then

Bol = s Like "[a-z,A-Z]"

ElseIf n = 0 Then

Bol = s Like "#"

End If

If Bol Then MyString = MyString & s

Next

MyGet = IIf(n = 1 Or n = 2, MyString, Val(MyString))

End Function

四、返回Excel界面，然后在需要提取单元格中录入公式

【=myget(A2,1)】是提取所有中文

【=myget(A2,2)】是提取所有英文

【=myget(A2)】是提取所有的数据

五、最后鼠标相应的往下拉就行。

喜欢施老师的干货文章请点赞，关注，分享，大家碰到中英文和数字混到一个单元格中，是怎样分离的，请在下方评论区留言写下你的看法，和施老师一起探讨。

问题来源

数据取整，是EXCEL数据处理的最常用的方式。可能大家最经常用的是INT函数，但，INT函数并不能满足所有的取整要求。比如，今天一位朋友要按照某位数的倍数取整，INT就解决不了了。

韩老师总结各种取整函数，大家可以收藏起来，遇到不同的取整要求，可以翻出来找合适的函数与方法。

九种方法分析一：INT取整

举例：

特征：

1、对正数，直接截掉小数取整；

2、对负数，截掉小数再-1取整。

二：TRUNC取整

特征：不管正数还是负数，都是直接截掉小数取整。

三：ROUND小数取整

第二个参数为0，采取四舍五入方式取整。

四：ROUND整数取整

第二个参数为负数，采取四舍五入方式取整。

五：ROUNDUP向上舍入函数

1、朝着远离 0（零）的方向将数字进行向上舍入。

2、如果第二个参数为 0，则将数字向上舍入到最接近的整数。

3、如果第二个参数小于 0，则将数字向上舍入到小数点左边的相应位数。

六：ROUNDDOWN向下舍入函数

1、朝着零的方向将数字进行向下舍入；

2、如果第二个参数为 0，则将数字向下舍入到最接近的整数；

3、如果第二个参数小于 0，则将数字向下舍入到小数点左边的相应位数。

七：MROUND函数

1、返回参数按指定基数舍入后的数值；

2、舍入的方式是“四舍五入”；

3、数值和基数参数的符号必须相同。如果不相同，将返回 #NUM 错误。

八：CEILING函数

1、 向上舍入（沿绝对值增大的方向）为最接近的指定基数的倍数。

2、如果数值为正值，基数为负值，则 返回 错误值 #NUM!；

3、如果数值为负，基数为正，则对值按朝向 0 的方向进行向上舍入；

4、如果数值和基数都为负，则对值按远离 0 的方向进行向下舍入。

九：FLOOR函数

1、将数值向下舍入（沿绝对值减小的方向）为最接近的指定基数的倍数

2、如果数值为正值，基数为负值，则 返回 错误值 #NUM!

3、如果数值为负，基数为正，则对值按远离 0 的方向进行向下舍入；

4、如果数值和基数都为负，则对值按朝向 0 的方向进行向上舍入。

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

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

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

以excel2007版本为例讲解

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

”如图：

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

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

排序后的结果

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

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

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

大咖们不喜勿喷哦。

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

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

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

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

问题与公式 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中的排序功能，除了排序，也能做很多其他的事情。让我们先看看案例。

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

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

5分钟！你可以吗？

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

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

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

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

注意：

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

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

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

隔两行插入数据或空行

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

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

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

隔行提取数据

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

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

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

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

Hi，大家好，我是胖斯基

又是一个突如其来的周三傍晚，风雨交夹，狂风相随……

想想那许久未更新的公众号，再摸摸那胸前的一整块腹肌，愧色依旧，还是码字减肥吧！

如何在Excel中巧妙的提取你需要的数据？

举个例子：

由于从财务系统导出的数据中，会计科目是一种全级显示，用””来展示层级的划分，但是实际在数据统计或应用的时候，需要用到末级科目，故：如何在批量数据中，快速提取末级科目的名称？

也许猴急如你，一上来就想着函数或VBA，可行是可行，但是这种霸王硬上弓的模式，往往因用力过猛而不能快速得到预期效果……

聪明人，应该懂得循序渐进！

1

查找替换法

曾经有那么一个快捷键，它可以在茫茫人海中帮你寻觅“对象”，活好话不多，Ta就是Ctrl+F。

如果你想起了Ta，那这个问题便迎刃而解，效果如下：

注意这里查找的时候，用到的查询内容是 【*\】，查找内容替换为空，过程进度的处理基本上是秒级。

说明：由于要获取的是末级科目名称，故希望在科目信息中，保留的内容是最后一个【\】后面的内容，所以这里查找的时候采用了通配符【*】，组合起来就是【*\】，故：凡是最后一个【\】前的内容都查询出来，并替换为空

想想，这种温柔的方式，是不是比直接上函数和VBA的来的更和谐？

2

快速填充法

当然条条大路通罗马，何必单恋一种法？

曾经也有那么一个智能填充工具，Ta有那么一点人工智能的特效，能够想你所想，取你所需……

Ta就是在Excel2013版及之后推出的一个快速填充工具（Ctrl+E）。如果你想起Ta，那基本也就是万花丛中笑春风般的效果，如下：

注意：这里手工输入了2条记录（目的在于给Excel更多判断的选择），然后手工拖拽，选择快速填充后，所有末级科目数据自动呈现出来，怎么样？处理进度上是不是也是秒级？

So，循序渐进是不是很重要？

慢慢来，你会发现不一样的精彩

3

函数法

这种方法呢不是特别提倡，毕竟不是人人都能上手就来函数，当然，你要是Excel大神，无妨，怎么开心怎么来。

函数1：=TRIM(RIGHT(SUBSTITUTE(B3,"",REPT(" ",200)),200))

函数2：=MID(B3,COUNT(,FIND("",B3,ROW($1:$99))),20)

函数3：=VLOOKUP("*",RIGHT(B3,ROW($3:$24)-{0,1}),2,)

具体函数逻辑就不在重复累赘说明了

胖斯基|说

在Excel中提取有效的数据，是一项频率极高的操作，其处理逻辑基本都已成为套路，学会灵活应用就好。

当然，依旧要敲黑板说明的是：观察Ta，循序渐进，温柔对待，也许灵巧的技巧，能解决你很大的问题……

更多精彩，敬请关注Excel老斯基

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

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

步骤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函数制作动态图表

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