多年Excel使用经验汇总

1、取整

=Round()

2、单元格引用

=Indirect()

3、获取所有 sheet 名称

① 公式-中定义 get.workbook（1）

② =transpose（定义名）

③ 选择单元格后，Ctrl+Shift+Enter

4、超链接批量查看

=Hyperlink()

5、vlookup 匹配

=VLOOKUP(B2,‘G:\Edit Space\工作文件\3 月份工作文件\数据成果合集[【所有院校信息】【0131】院校代码（含省）.xls]sheet1’!$B$3:$C$2635,1,0)

6、字符替换

=substitute（需要替换的文本，旧文本，新文本，第 N 个旧文本）

7、一个区域内的指定计数

=Countif（条件区域，条件）

8、返回指定字符串长度

=mid(A1,1,2)

=MID(B2,1,LEN(B2)-1)

9、模糊匹配

=VLOOKUP(""&B1&"",A1:A7,1,0)

10、len、lenb 介绍

Len -一个单元格的字符串长度

Lenb -一个单元格的字节长度（包含的所有输入内容个数）（汉字算 2 个字节，1 个字符）

11、找到单元格中的第一个汉字所在位置

<公式以 CTRL+SHIFT+ENTER 三键结束（表示数组运算）>

=MATCH(1=1,MIDB(A1,ROW($1:$99),1)<>MID(A1,ROW($1:$99),1),)

12、求指定条件下的一组数据中的最大值

=F2/ MAX( INDIRECT( “f”&MATCH(H2,H:H,0)&":f"&(MATCH(H2,H:H,0)+(COUNTIFS( A:A,A2,B:B,B2,C:C,C2)-1))))

–MAX() 求一组数据中的最大值

–Indirect() 返回由文本字符串指定的引用，某个单元格的位置、所含的值

–Match() 找到一列数据中，某个值出现的第一个位置（模式 0） 大于某个值、小于某个值

–Countifs() 筛选满足某些条件下的数据的计数

13、判断一个单元格内是否包含“凡人”

=IF(ISERROR(FIND(“凡人”,a1)),“不含”,“含有”)

14、 生成随机数

=rand()

=randbetween(最小，最大)

15、方差

=var() 方差

=stdev() 标准差

16、两个表进行对比改动点

【条件格式】-【新建规则】-【只包含以下内容的单元格设置格式】，下面选项设为【不等于】，区域是【=Sheet2!A1】（注意，默认是绝对引用，改成相对）。

来自http://www.wordlm.com/Excel/wzsj/6612.html

17、Index() + Match()

Index+Match函数比vlookup函数有优势的几个用法

自从学会了INDEX+MATCH,我就再也不用VLOOKUP了

index函数

index(范围，行，列)
index函数是在指定的区域取数据，一共有3个参数，如果区域只有一列，第三个参数可以省略。

match函数

match(查询条件，查询域，查询方式)
match函数则是定位数据的位置，定位行或列。第三个参数用0表示精确查找。

18、VBA -将一个多sheet的表，拆分成每个sheet一个文件

Sub splitbook()

’ 将一个 workbook 拆分成多张表，不改变数据本身

Dim shet As Worksheet

Application.ScreenUpdating = False

For Each shet In Sheets()

shet.Copy

ActiveWorkbook.SaveAs (“G:\cach” & shet.Name & “.xlsx”) ‘ 配置生成的路径

ActiveWorkbook.Close

Next

Application.ScreenUpdating = True

End Sub

19、VBA -将一个工作簿内的所有sheet页内容进行合并

Sub merge_sheet()

’ 将一个工作簿下的多个 sheet 的内容合并（包括第一行，间隔 1 行）

Dim Arr, Myr&, Sht As Worksheet

Sheet1.Activate

Myr = [a65536].End(xlUp).Row + 2

For Each Sht In Sheets

If Sht.Name <> Sheet1.Name Then

Arr = Sht.UsedRange

Cells(Myr, 1).Resize(UBound(Arr), UBound(Arr, 2)) = Arr

Myr = [a65536].End(xlUp).Row + 2

End If

Next

End Sub

20、VBA - 破解工作表的密码保护

Sub DeletePW()

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, AllowFiltering:=True

ActiveSheet.Protect DrawingObjects:=False, Contents:=True, AllowFiltering:=True

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, AllowFiltering:=True

ActiveSheet.Protect DrawingObjects:=False, Contents:=True, AllowFiltering:=True

ActiveSheet.Unprotect

End Sub

21、VBA - 将一张表拆分为多个文件

Sub 拆分本表至若干新文件()

’ 保留表头

Dim arr, d As Object, k, t, i&, lc%, rng As Range, c%

c = Application.InputBox(“请输入拆分列号(拆分后将放在本文件所在文件夹):”, , 5, , , , , 1)

If c = 0 Then Exit Sub

Application.ScreenUpdating = False

Application.DisplayAlerts = False

arr = [a1].CurrentRegion

lc = UBound(arr, 2)

Set rng = [a1].Resize(, lc)

Set d = CreateObject(“scripting.dictionary”)

For i = 2 To UBound(arr)

If Not d.Exists(arr(i, c)) Then

Set d(arr(i, c)) = Cells(i, 1).Resize(1, lc)

Else

Set d(arr(i, c)) = Union(d(arr(i, c)), Cells(i, 1).Resize(1, lc))

End If

Next

k = d.Keys

t = d.Items

For i = 0 To d.Count - 1

With Workbooks.Add(xlWBATWorksheet)

rng.Copy .Sheets(1).[a1]

t(i).Copy .Sheets(1).[a2]、

.SaveAs FileName:=ThisWorkbook.Path & “” & k(i) & “.xlsx” ’ 输出路径+文件名

.Close

End With

Next

Application.DisplayAlerts = True

Application.ScreenUpdating = True

MsgBox “拆表完成，请查看路径：” & ThisWorkbook.Path & “” & “！”, 64, “提示”

End Sub

22、数据透视表使用

公司书架有本 Excel 透视表的书

【不懂 Excel 透视表 怎能做好数据分析（全彩插画+视频讲解）】（中国水利水电出版社）

可以翻阅，以及常用的快捷键可以熟悉

需要掌握的几个常用快捷键（win 系统下，mac 自行切换）

Ctrl+E、Ctrl+D、Ctrl+F/H、Ctrl+G、Ctrl+shift+↓、Ctrl+M、

筛选,格式刷自行设置快捷键、

23、VBA 学习课程

https://www.bilibili.com/video/BV1L4411Q7Ni

注. 前 11 讲学会即可，后面几讲是篇可视化控件内容的，正常办公用到的不多

24、表格配色，常用字体及视觉搭配

RGB配色表

建议的配色：

1.R 25 G 202 B 173           	2.R 140 G 199 B 181
3.R 160 G 238 B 225          	4.R 190 G 231 B 233
5.R 190 G 237 B 199

6.R 214 G 213 B 183          7.R 209 G 186 B 116
8.R 230 G 206 B 172          9.R 236 G 173 B 158
10.R 244 G 96 B 108 

建议的字号：

表头标题使用黑体

表格正文中文字体使用微软雅黑

表格正文英文字体使用Arial