Excel小技巧整理（持续更新）
合并某列中相同单元格
单元格中提取数字
不受筛选影响的填充序列方法
excel按固定的列数转置
比VLOOKUP更好的搜索匹配方法——INDEX+MATCH
如何根据重复出现的某个关键字匹配出所有对应的内容
Excel中的自定义ConTxt函数合并同类项
INDIRECT函数通过字符串引用单元格
获取表格文件名、工作表名

参考https://jingyan.baidu.com/article/9158e00006db70a25512286f.html

使用方法

先给需要合并的列排序，这样相同数据会排在一起
在excel中按Alt+F11打开VBA窗口
- 一次性使用：直接插入新的模块，复制下文中的代码，然后转到excel主界面进行后续操作

- 想要保存模块并在接下来的excel文件中使用，可以参考下图将模块导出成bas文件，之后在在新的excel中打开VBA界面，左侧文件列表右键导入.bas文件

切换回表格窗口，在“视图”菜单下找到“宏”，选择“查看宏”并执行该宏（可能会提示你需要保存为启用宏的格式，另存为.xlsm格式）
输入需要合并的事第几列，注意是数字而不是ABC
确定，完成

VBA代码如下

Sub 合并相同用单元格()
Dim l%, i%
Application.DisplayAlerts = False
k% = InputBox("请输入合并单元格所在列")
l = [A65536].End(xlUp).Row
    For i = l To 2 Step -1
    If Cells(i, k) = Cells(i - 1, k)   Then
    Range(Cells(i - 1, k), Cells(i,   k)).Merge
    End If
Next
Application.DisplayAlerts = True
End Sub

单元格中提取数字

https://baijiahao.baidu.com/s?id=1575378393296614&wfr=spider&for=pc

参考链接中的步骤进行。

打开VBA方式也可以是右键标签页-查看代码

Excel小技巧整理（持续更新）
合并某列中相同单元格
单元格中提取数字
不受筛选影响的填充序列方法
excel按固定的列数转置
比VLOOKUP更好的搜索匹配方法——INDEX+MATCH
如何根据重复出现的某个关键字匹配出所有对应的内容
Excel中的自定义ConTxt函数合并同类项
INDIRECT函数通过字符串引用单元格
获取表格文件名、工作表名

Function mygetnumber(cel As Range)

    With CreateObject("vbscript.regexp")
        .Pattern = "[^d.-]+"
        .Global = True
        
        mygetnumber = .Replace(cel, " ")
        
    End With
    
End Function

View Code

使用方法是在单元格中输入函数名字，括号参数为单元格，即可使用

对于一个单元格中多位同学信息并且夹杂中文、数字和符号的情况，先提取所有数字，再类似于步骤4根据固定字符串长度来分割学号（数字），转换为步骤3的情况处理

不受筛选影响的填充序列方法

筛选前，在A2单元格输入以下公式，然后向下填充公式

=SUBTOTAL(103,B$2:B2)

公式表示：对B$2:B2区域未被隐藏的实际显示数据计数。其中的103就是个常数，不要更改

这样执行筛选后，不符合条件的行不参与序号编排。

详见附图

excel按固定的列数转置

主要是展示INDEX函数的用法

=INDEX(array, row_num, [column_num])

返回表格或数组中的元素值，此元素由行号和列号的索引值给定。当函数 INDEX 的第一个参数为数组常量时，使用数组形式。

array可以使某几行或者某几列的形式，比如$1:$3或者$A:$A

第一行放入数据，然后第二行之后的公式如下（右拉下拉填充）：

=INDEX($1:$1,1,COLUMN(A1)+(ROW(A1)-1)*10)　　
其中COLUMN和ROW函数用来作为计算参考

可以实现每行10列的转置效果，如图所示：

此方法可以用在需要规格整理打印的情况中，比如打印学生信息小纸片在一张大纸上。

在Data表中放入数据，如图所示

然后其他sheet（比如这里的Print表）输入下列公式并下拉填充）：

=IF(INDEX(Data!$A:$C,QUOTIENT(ROW(A1)-1,3)*4+COLUMN(A1)+1,IF(MOD(ROW(A1),3),MOD(ROW(A1),3),3))=0,"",INDEX(Data!$A:$C,QUOTIENT(ROW(A1)-1,3)*4+COLUMN(A1)+1,IF(MOD(ROW(A1),3),MOD(ROW(A1),3),3)))　

IF是判断当前索引内容是否为空，如果为空就返回空字符串而不是0
这里面主要的逻辑是
INDEX(Data!$A:$C,QUOTIENT(ROW(A1)-1,3)*4+COLUMN(A1)+1,IF(MOD(ROW(A1),3),MOD(ROW(A1),3),3))
索引区域是Data表的A:C列，行部分先对于当前单元格的行-1后取3的余数判断是第几组数据（4个一组，每组3行）
列部分需要对当前单元格的列求余数，但是余数为0的时候实际上应该刚好是每一组数据中的第三行，所以用IF判断为0的情况，替换为3

可以实现每行4列的转置效果，如图所示：

打印这个页面之后裁剪就能得到每个人的小信息卡片
更加复杂地，我们可以给每三行过后增加一个分割线，通过if和mod组合实现

代码如下

=IF(MOD(ROW(A1),4),IF(INDEX(Data!$A:$C,QUOTIENT(ROW(A1)-1,4)*4+COLUMN(A1)+1,MOD(ROW(A1),4))=0,"","    "&INDEX(Data!$A:$C,QUOTIENT(ROW(A1)-1,4)*4+COLUMN(A1)+1,MOD(ROW(A1),4)))," -    -    -    -    -    -   ")

拆开方便分析

IF(
　　MOD(ROW(A1),4),
　　IF(
　　　　INDEX(
　　　　　　Data!$A:$C,
　　　　　　QUOTIENT(ROW(A1)-1,4)*4+COLUMN(A1)+1,
　　　　　　MOD(ROW(A1),4)
　　　　)=0,
　　　　"",
　　　　"    "&INDEX(Data!$A:$C,QUOTIENT(ROW(A1)-1,4)*4+COLUMN(A1)+1,MOD(ROW(A1),4))
　　),
　　" -    -    -    -    -    -   "
)

比VLOOKUP更好的搜索匹配方法——INDEX+MATCH

https://www.ithome.com/html/office/371454.htm

VLOOKUP函数：纵向查找返回表中的值。缺点：查阅值需要位于查找区域的第一列，并且不能判断格式。

以下函数组合尤其适用于含0开头的学号匹配的情况，如果忽略错误转换成数字会丢失开头的0，这时候转换成文字格式若无法用vlookup匹配，可以试试此方法。

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

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

如下图需要根据c列的单元格寻找H列中对应行，用index索引G列对应行即可

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

这种方法不受列与列之间先后顺序的限制，而且可以匹配含0开头的学号，比如

=INDEX(信息列,MATCH(学号,一组学号列,0))
其中0表示精确匹配

如何根据重复出现的某个关键字匹配出所有对应的内容

参考https://zhidao.baidu.com/question/246936776785025124.html

match函数只能匹配符合条件的第一个位置，当需要根据一个关键字匹配多个单元格的时候，就需要用到数组了。

用参考链接中的问题来举例分析一下。

目的：找出下表中第二行的每个a对应的第一行中的数据

先上解法

在A4框框里输入的函数如下：

=INDEX(1:1,SMALL(IF($A2:$N2="a",COLUMN($A:$N),256),COLUMN(A1)))&""

我们先回顾下每个函数的功能：

IF(判断条件，条件为真的时候返回的值，[选填：条件为假的时候返回的值])：如果条件为真，该函数将返回一个值；如果条件为假，函数将返回另一个值。
SMALL(array,k)：返回数据集中的第 k 个最小值。使用此函数以返回在数据集内特定相对位置上的值。
INDEX(array, row_num, [column_num])：返回表或数组中元素的值，由行号和列号索引选择；当函数 INDEX 的第一个参数为数组常量时，使用数组形式。
COLUMN([reference])：返回给定单元格引用的列号，例如，公式=COLUMN （D10）返回4，因为第2列是第四列。对应的函数为ROW()，返回行号。

我们来分析一下函数的功能：

IF判断的条件为第二行的这一组数组中的每个元素是否等于“a”，是的话返回这个元素所在的列号，不是的话返回256（只要是个特别大的数就行，比所有的列号都要大）
- 对于数组中的每个元素做判断并把返回的值组成一个数组，比如第二行的数组中的第5个元素等于“a”，那么返回的数组的第5个元素为“a”
- 所以这里的IF返回的结果是一个数组：{256,256,256,256,5,6,7,8,9,256,256,12,13,14}
SMALL接受IF返回的数组，在其中选取从小到大排序后的第COLUMN(A1)、也就是第1个元素，所以这里选取的第一小的数字5
INDEX在第一行（1:1）中找SMALL返回的列号的元素并返回，比如5就对应着E列，也就是第一行的E列，即E1位置的数字7.
INDEX返回的值右边加了个 &"" ，目的是右拉找完了所有的等于a的元素位置后，填充的内容为空白。如果没有这个小尾巴，后面填充的会是数字0
往右拉函数进行填充，只有COLUMN(A1)中的A1会依次变成B1、C1、D1....，其列号依次为1、2、3、4...，指导SMALL选取IF返回的数组中第1、2、3、4....小的元素，即从小到大排列的a所在的列号，就指导整个函数取到了a所在的各个列的元素。

理解了相关用法后我们可以自己根据自己的功能进行修改，需要注意的是这里的函数由于用到了数组函数，所以输入后是使用“Shift+Ctrl+Enter”而不是直接回车结束，否则会出现意料之外的结果。

示例1：寻找不同的老师所带的学生的信息

比如我们想要寻找不同的老师所带的学生的信息，可以使用如下函数

=INDEX(学生数据!$B:$B,SMALL(IF(学生数据!$U:$U=测试!$A2,ROW(学生数据!$B:$B),5000),COLUMN(A1)))&""

使用的方法也是输入后往右拉填充，直到把所有的学生都列了出来。

相关数据说明：在sheet学生数据中的B列为学生姓名，U列为导师姓名，总数据小于5000行，导师的姓名数据在输入函数的这个“测试”sheet中的第A列，函数输入在C2的位置，往右拉填充了50个单元格（一个老师最多有不超过50个学生）

其中COLUMN(A1)和前面的一样，这个值会随着我们的填充而指引SMALL选取不同的元素，如果是下拉填充，应该选用ROW(A1)替代它。

在这里由于A列有多个老师的姓名，所以在C2输入函数后不仅可以右拉填充获取这个老师的所有学生，还可以下拉填充获取其他老师的学生（计算量还是很大的）。

在列出了老师的所有学生后我们可以使用TEXTJOIN函数来将其全部合并到一个单元格中。

语法为： TEXTJOIN(分隔符, ignore_empty, text1, [text2], …)

在这里我在B2输入 TEXTJOIN("、",C2:AZ2），因为到AZ刚好为50列，当然你也可以选择更多。

下拉填充合并每个老师的学生，可以看到学生的名字都被"、"连接了起来，且最后一个学生后面没有连接任何东西，很完美。

示例2：秒杀“筛选+复制+粘贴”的引用方法

对于一个数据表，我们想要筛选符合某个条件的行，然后把数据复制到一个新的sheet，传统的做法是Crtl+Shift+L打开筛选，根据条件选择需要的行，Ctrl+C复制，Ctrl+V粘贴。

很straightforward，但是如果我们需要建多个新sheet，每个sheet有独立的筛选条件，并且当主sheet的数据修改之后希望各个从sheet相应自动更新筛选后的内容呢？

使用上面提到的筛选重复项的方式就可以做到。

建立一个新的sheet（我们叫它“从sheet”），把主sheet（表名为“原始数据”）的第一行（表头）粘贴上去，然后在从sheet的A2单元格里输入

=INDEX('原始数据'!A:A,SMALL(IF('原始数据'!DZ$1:DZ$2151")="是",ROW($1:$2151),6666),ROW(A1)))&""

按下Shift+Ctrl+Enter然后下拉填充足够多的行数（最为保险的方法是和原始数据一样多的行数），我们就会获得“原始数据”表中的DZ列中标记了“是”的行对应的A列的内容（一般为名字、学号啥的）。

比如我的原始数据的DZ3、DZ5、DZ687标记了“是”，那么从sheet中A列就会显示原始数据中的A3、A5、A687单元格中的内容。

说完了效果，这里再提示一下公式中的一些细节：

首先由于是按列搜索、按列填充，所以和最初的公式要取一个行-列的对偶关系，主要体现在固定符号"$"的位置，和COLUMN()函数替换为ROW()，引用列换位引用行。

其次公式中的2151是原始数据中最大的行号，6666是一个任意的高于2151的数。

到这里我们根据条件筛选出了原始数据中的第一列的内容，还要追加把原始数据中对应行的其他列数据复制过来，使用的方法是在从sheet的B2单元格输入

=IFNA(VLOOKUP($A2,'原始数据'!$A:$DQ,COLUMN(),0),"")

公式中的COLUMN()会根据当前单元格判断所在列号，这样我们就可以往右拉填充，获取其他列的值。

公式中的IFNA()是防止遇到空单元格的情况，因为从Sheet中的A列里我们填充了可能几百行的公式用于筛选原始数据中的A列的值，但很多时候从Sheet中A列筛选到的数据只有几行或几十行，剩下的都是由 &"" 产生的空单元格。加上IFNA后，VLOOKUP如果遇到了空单元格，会报#N/A的错误，最终显示的结果就会是空单元格。这样我们就可以愉快得下拉填充全部使用的行了（双击单元格右下角的十字自动填充）

你可能发现了，其实从sheet的第一行也不需要从原始数据sheet手动复制过来，只需要在从sheet的A1输入 =‘原始数据’!A1 ，然后后面的列也可以通过上面的VLOOKUP()公式进行匹配搜索。

扩展1：多条件同时判断

有时候我们需要筛选的关键字不止一个，可以通过把IF函数里的判断改为OR()函数的组合形式，匹配上任何一个关键字都会被筛出来。

我们也可以吧判断换为AND()的组合形式，需要同时满足多个关键字的要求才能被筛选出来，一般用于多条件筛选，比如筛选“身高”行大于180的且“体重”行小于140的。

扩展2：自适应条件判断

对于不同的从Sheet有不同的筛选条件，我们可以把想要在“原始数据”sheet中筛选的列名写在从sheet表名上，这样复制新的sheet的时候，只需要改从sheet的表名就可以实现不同的筛选功能。

比如我有两个从sheet，一个叫“爱好打羽毛球”，一个叫“爱好打乒乓球”，在“原始数据”中分别有对应的X、Y两列筛选条件，我可以将这两个从sheet分别命名为“爱好打羽毛球X”、“爱好打乒乓球Y”，使用博客中介绍的CELL()函数获取工作表名称中的筛选列名。

比如示例2中的公式可以改写为

=INDEX('原始数据'!A:A,SMALL(IF(INDIRECT("'原始数据'!"&RIGHT(CELL("filename",A1),1)&"$1:"&RIGHT(CELL("filename"),1)&"$2151")="是",ROW($1:$2151),6666),ROW(A1)))&""

其中 RIGHT(CELL("filename",A1),1) 在“爱好打羽毛球X”工作表中会得到“X”，在“爱好打乒乓球Y”工作表中会得到“Y”，当然你也可以使用本博客中介绍CELL()函数时说的另一种提取工作表名的方法。

关于INDIRECT()函数的间接引用功能请参考本博客中的对应介绍部分。

Excel中的自定义ConTxt函数合并同类项

参考http://club.excelhome.net/thread-1220035-1-1.html

星光大神写的，其实功能比较简单，就是将数组中的元素都合并到一个单元格中。

这里转发他的图片来看看效果和功能，出处见参考链接

VBA代码如下

 1 Public Function ConTxt(ParamArray args() As Variant) As Variant
 2 Dim tmptext As Variant, i As Variant, cellv As Variant
 3 Dim cell As Range
 4 tmptext = ""
 5 For i = 0 To UBound(args)
 6     If Not IsMissing(args(i)) Then
 7         Select Case TypeName(args(i))
 8             Case "Range"
 9                 For Each cell In args(i)
10                     tmptext = tmptext & cell
11                 Next cell
12             Case "Variant()"
13                 For Each cellv In args(i)
14                     tmptext = tmptext & cellv
15                 Next cellv
16             Case Else
17                 tmptext = tmptext & args(i)
18         End Select
19     End If
20 Next i
21 ConTxt = tmptext
22 End Function

INDIRECT函数通过字符串引用单元格

说明

返回由文本字符串指定的引用。此函数立即对引用进行计算，并显示其内容。如果需要更改公式中对单元格的引用，而不更改公式本身，请使用函数 INDIRECT。

语法

INDIRECT(ref_text, [a1])

INDIRECT 函数语法具有以下参数：

Ref_text 必需。对包含 A1 样式的引用、R1C1 样式的引用、定义为引用的名称或对单元格的引用作为文本字符串的单元格的引用。如果 ref_text 不是有效的单元格引用, 则间接返回 #REF! 。
- 如果 ref_text 引用另一个工作簿 (外部引用), 则必须打开另一个工作簿。如果源工作簿未打开, 则间接返回 #REF! 。
  
  注意 Excel Web App 中不支持外部引用。
- 如果 ref_text 引用的单元格区域超出1048576的行限制或列限制 16384 (XFD), 则间接返回 #REF! 错误。
  
  注意此行为不同于早于Microsoft Office Excel 2007 的 Excel 版本, 这将忽略超过的限制并返回值。
A1 可选。一个逻辑值，用于指定包含在单元格 ref_text 中的引用的类型。
- 如果 a1 为 TRUE 或省略，ref_text 被解释为 A1-样式的引用。
- 如果 a1 为 FALSE，则将 ref_text 解释为 R1C1 样式的引用。

可以通过INDIRECT根据不同条件引用不同单元格的内容，比如INDIRECT("Sheet"&A1&"!B1")可以拖动填充使得不同单元格索引不同的Sheet（A1中放着表名）

演示视频可以参考https://www.zhihu.com/question/305821885/answer/552610770

获取表格文件名、工作表名

https://zhidao.baidu.com/question/1451706925963765380.html

https://baijiahao.baidu.com/s?id=1664635364215197213&wfr=spider&for=pc

=CELL("filename",[optional])会返回当前活动的工作表的引用路径，比如

这个工作簿（文件名）叫“提取工作表名.xlsm”，工作表（Sheet名）叫“有本事来提取我的名字啊”

CELL函数的第二个参数，可以是当前工作表的任意单元格，如果填写了当前工作表中的单元格，则会返回带有当前工作表名称的路径。

如果第二个参数不写，则函数返回的路径由当前哪个是活动工作表所定(鼠标点在哪个工作表哪个就是活动工作表，点击后需要按F9才刷新公式)

我们可以使用简单的搜索来提取工作表名

=MID(CELL("filename"),FIND("]",CELL("filename"))+1,256)

256指定了表名的最长长度，当然你不嫌麻烦的话，可以使用LEN()先计算下CELL()返回的字符串长度，然后减去FIND()函数找到的索引，获得表名长度。

参考链接第二个还介绍了用宏的方法，有兴趣的可以了解一下。

使用方法

VBA代码如下

单元格中提取数字

不受筛选影响的填充序列方法

excel按固定的列数转置

比VLOOKUP更好的搜索匹配方法——INDEX+MATCH

如何根据重复出现的某个关键字匹配出所有对应的内容

示例1：寻找不同的老师所带的学生的信息

示例2：秒杀“筛选+复制+粘贴”的引用方法

扩展1：多条件同时判断

扩展2：自适应条件判断

Excel中的自定义ConTxt函数合并同类项

INDIRECT函数通过字符串引用单元格

说明

语法

获取表格文件名、工作表名

相关推荐