Excel小技巧整理（持续更新）

2021-11-14 02:49:05

合并某列中相同单元格

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

使用方法

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

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

切换回表格窗口，在“视图”菜单下找到“宏”，选择“查看宏”并执行该宏
输入需要合并的事第几列，注意是数字而不是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

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

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

Function mygetnumber(cel As Range)

    With CreateObject("vbscript.regexp")

        .Pattern = "[^\d.-]+"

        .Global = True

        mygetnumber = .Replace(cel, " ")

    End With

End Function

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

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

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

=SUBTOTAL(,B$:B2)

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

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

详见附图

excel按固定的列数转置

主要是展示INDEX函数的用法

=INDEX(array, row_num, [column_num])

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

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

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

=INDEX($:$,,COLUMN(A1)+(ROW(A1)-)*)　　
其中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),),IF(INDEX(Data!$A:$C,QUOTIENT(ROW(A1)-,)*+COLUMN(A1)+,MOD(ROW(A1),))=,"","    "&INDEX(Data!$A:$C,QUOTIENT(ROW(A1)-,)*+COLUMN(A1)+,MOD(ROW(A1),)))," -    -    -    -    -    -   ")

拆开方便分析

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开头的学号，比如

=INDEX(信息列,MATCH(学号,一组学号列,))

其中0表示精确匹配

码农公寓