EXCEL常用知识分享

EXCEL是职场办公人员用的最多的工具，现对自己在工作上目前常用的内容进行总结梳理分享出来，后续有新的内容会持续更新。

EXCEL

• 数学函数
  • SUMIF(条件范围,条件,求和范围)
  • SUMIFS(求和范围,条件1范围,条件1,条件2范围,条件2,条件N范围,条件N,)
  • SUMPROUCT(array1,array2,array3,...) array1为数组，在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。所有数组维数必须一样
  • COUNTIF(条件范围，条件)
  • COUNTIFS(条件1范围,条件1,条件2范围,条件2,条件N范围,条件N)
  • ROUND(数值，保留的小数位数)
  • INT(数字) 取整
  • RANK(数值,范围,序别) 1-升序 0-降序
  • RANDBETWEEN(数字下限,数字上限)在指定范围内生成一组随机数据
  • RAND()直接生成一个0-1的随机数
  • QUARTILE(array,quart)返回数据集的四分位数
  • STDEV(number1,number2,...)计算基于给定样本的标准偏差（忽略样本中的逻辑值及文本）
  • SUBTOTAL(function_num,ref1,ref2, ...)返回数据清单或数据库中的分类汇总
• 文本函数
  • LOOKUP(①查找值，②查找值所在区域，③返回的结果)
  • LOOKUP（①查找值，②二维数组）
  • VLOOKUP
  • INDEX返回给定范围内行列交叉处的值，常和match搭配使用
  • MATCH(lookup_value, lookup_array, [match_type])给出制定值在指定范围内的所在位置
  • EVALUATE计算单元格中的文本算式，点击公式一定义名称
• 文本清洗
  • FIND(文本,范围,数值)在字符串中查找特定字符，数值表示查找第几个
  • LEFT(文本,截取长度)从左边截取
  • RIGHT(文本,截取长度)从右边截取
  • MID(文本,开始位,截取长度)从中间截取
  • CONCATENATE(文本1,文本2,....)合并字符函数
  • REPLACE(原字符串,开始位置,字符个数,新字符串)将一个字符串中的部分字符用另一字符串替换
  • SUBSTITUTE(字符串,原字符串,新字符串,替换序号)
  • TRIM()除了单词之间的单个空格外，清除文本中所有的空格
  • LEN(文本)文本的长度
• 时间函数
  • DATEDIF(开始日期,终止日期,比较单位)
    • "y"返回时间段中的整年数 "m”返回时间段中的整月数 "d"返回时间段中的天数 "md”参数1和2的天数之差，忽略年和月 "ym“参数1和2的月数之差，忽略年和日 "yd”参数1和2的天数之差，忽略年。按照月、日计算天数
• 逻辑函数
  • IFERROP(值，错误值)如果公式的计算结果错误，则返回指定的值，否则返回公式的结果
• 数据透视表
  • excel附件
• 条件格式和迷你图
  • 选择放入折线图的单元格--插入--迷你图
• 自定义名称
  • 数据--数据有效性--序列--选择数据
• 删除重复项
  • 数据--删除重复项
• 切片
  • 表或数据透视表选择--插入切片器（可点击筛选）
• 数据分析
  • 数据-数据分析-描述统计，输出数据描述（平均值，标准差，分位数，最大最小值，求和等）
• VBN
  • 自动根据Sheet名生成目录
    • Sub mulu() On Error GoTo Tuichu Dim i As Integer Dim ShtCount As Integer Dim SelectionCell As Range ShtCount = Worksheets.Count If ShtCount = 0 Or ShtCount = 1 Then Exit Sub Application.ScreenUpdating = False For i = 1 To ShtCount If Sheets(i).Name = "目录" Then Sheets("目录").Move Before:=Sheets(1) End If Next i If Sheets(1).Name <> "目录" Then ShtCount = ShtCount + 1 Sheets(1).Select Sheets.Add Sheets(1).Name = "目录" End If Sheets("目录").Select Columns("B:B").Delete Shift:=xlToLeft Application.StatusBar = "正在生成目录…………请等待！" For i = 2 To ShtCount ActiveSheet.Hyperlinks.Add Anchor:=Worksheets("目录").Cells(i, 2), Address:="", SubAddress:= _ "'" & Sheets(i).Name & "'!R1C1", TextToDisplay:=Sheets(i).Name Next Sheets("目录").Select Columns("B:B").AutoFit Cells(1, 2) = "目录" Set SelectionCell = Worksheets("目录").Range("B1") With SelectionCell .HorizontalAlignment = xlDistributed .VerticalAlignment = xlCenter .AddIndent = True .Font.Bold = True .Interior.ColorIndex = 34 End With Application.StatusBar = False Application.ScreenUpdating = True Tuichu: End Sub
    • 子主题
  • 全部表格冻结首行
    • Sub 全部表格冻结首行() Dim Sh As Worksheet For Each Sh In ThisWorkbook.Sheets Sh.Activate With ActiveWindow .SplitColumn = 0 .SplitRow = 1 .FreezePanes = True End With Next End Sub
  • 汇总多个sheet表
    • Sub CollecShtData() Dim Sht As Worksheet, Rng As Range, k&, Trow& Trow = Val(InputBox("请输入标题的行数", "提醒")) If Trow < 0 Then MsgBox "标题行数不能为负数。", 64, "警告": Exit Sub '取得用户输入的标题行数，如果为负数，退出程序 Application.ScreenUpdating = False Cells.ClearContents '清空当前表数据 Cells.NumberFormat = "@" '设置文本格式 For Each Sht In Worksheets '遍历表格 If Sht.Name <> ActiveSheet.Name Then '如果表格名称不等于当前表名则进行复制数据…… Set Rng = Sht.UsedRange k = k + 1 If k = 1 Then '如果是首个表格，则K为1，则把标题行一起复制到汇总表 Rng.Copy [a1].PasteSpecial Paste:=xlPasteValues Else '如K不等于1，扣除标题行后再复制黏贴到总表，只黏贴数值 Rng.Offset(Trow).Copy Cells(ActiveSheet.UsedRange.Rows.Count + 1, 1).PasteSpecial Paste:=xlPasteValues End If End If Next [a1].Activate Application.ScreenUpdating = True MsgBox "一共汇总了" & k & "个表格。" End Sub
  • 多个sheet分成独立表格文件
    • Sub Macro1() Dim sht As Worksheet Application.ScreenUpdating = False Application.DisplayAlerts = False For Each sht In Sheets sht.Copy ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & sht.Name & ".xlsx" ActiveWorkbook.Close Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub ####################################结束################################## Sub 拆分到工作簿2() Dim wk As Workbook, ss$, k% Application.DisplayAlerts = False For Each sht In ThisWorkbook.Sheets Set wk = Workbooks.Add k = k + 1 ThisWorkbook.Sheets(k).Copy Workbooks(2).Sheets(1) ss = ThisWorkbook.Path & "\" & sht.Name & ".xlsx" wk.SaveAs ss wk.Close Next Application.DisplayAlerts = True MsgBox "拆分工作簿完成！" End Sub
  • excel撤销保护工作表
    • Option Explicit Public Sub AllInternalPasswords() ' Breaks worksheet and workbook structure passwords. Bob McCormick ' probably originator of base code algorithm modified for coverage ' of workbook structure / windows passwords and for multiple passwords ' ' Norman Harker and JE McGimpsey 27-Dec-2002 (Version 1.1) ' Modified 2003-Apr-04 by JEM: All msgs to constants, and ' eliminate one Exit Sub (Version 1.1.1) ' Reveals hashed passwords NOT original passwords Const DBLSPACE As String = vbNewLine & vbNewLine Const AUTHORS As String = DBLSPACE & vbNewLine & _ "Adapted from Bob McCormick base code by" & _ "Norman Harker and JE McGimpsey" Const HEADER As String = "AllInternalPasswords User Message" Const VERSION As String = DBLSPACE & "Version 1.1.1 2003-Apr-04" Const REPBACK As String = DBLSPACE & "Please report failure " & _ "to the microsoft.public.excel.programming newsgroup." Const ALLCLEAR As String = DBLSPACE & "The workbook should " & _ "now be free of all password protection, so make sure you:" & _ DBLSPACE & "SAVE IT NOW!" & DBLSPACE & "and also" & _ DBLSPACE & "BACKUP!, BACKUP!!, BACKUP!!!" & _ DBLSPACE & "Also, remember that the password was " & _ "put there for a reason. Don't stuff up crucial formulas " & _ "or data." & DBLSPACE & "Access and use of some data " & _ "may be an offense. If in doubt, don't." Const MSGNOPWORDS1 As String = "There were no passwords on " & _ "sheets, or workbook structure or windows." & AUTHORS & VERSION Const MSGNOPWORDS2 As String = "There was no protection to " & _ "workbook structure or windows." & DBLSPACE & _ "Proceeding to unprotect sheets." & AUTHORS & VERSION Const MSGTAKETIME As String = "After pressing OK button this " & _ "will take some time." & DBLSPACE & "Amount of time " & _ "depends on how many different passwords, the " & _ "passwords, and your computer's specification." & DBLSPACE & _ "Just be patient! Make me a coffee!" & AUTHORS & VERSION Const MSGPWORDFOUND1 As String = "You had a Worksheet " & _ "Structure or Windows Password set." & DBLSPACE & _ "The password found was: " & DBLSPACE & "$$" & DBLSPACE & _ "Note it down for potential future use in other workbooks by " & _ "the same person who set this password." & DBLSPACE & _ "Now to check and clear other passwords." & AUTHORS & VERSION Const MSGPWORDFOUND2 As String = "You had a Worksheet " & _ "password set." & DBLSPACE & "The password found was: " & _ DBLSPACE & "$$" & DBLSPACE & "Note it down for potential " & _ "future use in other workbooks by same person who " & _ "set this password." & DBLSPACE & "Now to check and clear " & _ "other passwords." & AUTHORS & VERSION Const MSGONLYONE As String = "Only structure / windows " & _ "protected with the password that was just found." & _ ALLCLEAR & AUTHORS & VERSION & REPBACK Dim w1 As Worksheet, w2 As Worksheet Dim i As Integer, j As Integer, k As Integer, l As Integer Dim m As Integer, n As Integer, i1 As Integer, i2 As Integer Dim i3 As Integer, i4 As Integer, i5 As Integer, i6 As Integer Dim PWord1 As String Dim ShTag As Boolean, WinTag As Boolean Application.ScreenUpdating = False With ActiveWorkbook WinTag = .ProtectStructure Or .ProtectWindows End With ShTag = False For Each w1 In Worksheets ShTag = ShTag Or w1.ProtectContents Next w1 If Not ShTag And Not WinTag Then MsgBox MSGNOPWORDS1, vbInformation, HEADER Exit Sub End If MsgBox MSGTAKETIME, vbInformation, HEADER If Not WinTag Then MsgBox MSGNOPWORDS2, vbInformation, HEADER Else On Error Resume Next Do 'dummy do loop For i = 65 To 66: For j = 65 To 66: For k = 65 To 66 For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66 For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66 For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126 With ActiveWorkbook .Unprotect Chr(i) & Chr(j) & Chr(k) & _ Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _ Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n) If .ProtectStructure = False And _ .ProtectWindows = False Then PWord1 = Chr(i) & Chr(j) & Chr(k) & Chr(l) & _ Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _ Chr(i4) & Chr(i5) & Chr(i6) & Chr(n) MsgBox Application.Substitute(MSGPWORDFOUND1, _ "$$", PWord1), vbInformation, HEADER Exit Do 'Bypass all for...nexts End If End With Next: Next: Next: Next: Next: Next Next: Next: Next: Next: Next: Next Loop Until True On Error GoTo 0 End If If WinTag And Not ShTag Then MsgBox MSGONLYONE, vbInformation, HEADER Exit Sub End If On Error Resume Next For Each w1 In Worksheets 'Attempt clearance with PWord1 w1.Unprotect PWord1 Next w1 On Error GoTo 0 ShTag = False For Each w1 In Worksheets 'Checks for all clear ShTag triggered to 1 if not. ShTag = ShTag Or w1.ProtectContents Next w1 If ShTag Then For Each w1 In Worksheets With w1 If .ProtectContents Then On Error Resume Next Do 'Dummy do loop For i = 65 To 66: For j = 65 To 66: For k = 65 To 66 For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66 For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66 For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126 .Unprotect Chr(i) & Chr(j) & Chr(k) & _ Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _ Chr(i4) & Chr(i5) & Chr(i6) & Chr(n) If Not .ProtectContents Then PWord1 = Chr(i) & Chr(j) & Chr(k) & Chr(l) & _ Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _ Chr(i4) & Chr(i5) & Chr(i6) & Chr(n) MsgBox Application.Substitute(MSGPWORDFOUND2, _ "$$", PWord1), vbInformation, HEADER 'leverage finding Pword by trying on other sheets For Each w2 In Worksheets w2.Unprotect PWord1 Next w2 Exit Do 'Bypass all for...nexts End If Next: Next: Next: Next: Next: Next Next: Next: Next: Next: Next: Next Loop Until True On Error GoTo 0 End If End With Next w1 End If MsgBox ALLCLEAR & AUTHORS & VERSION & REPBACK, vbInformation, HEADER End Sub
• 其他
  • 快速在多个分表批量创建返回总表的超链接
    • 1.首先，鼠标右键单击任一工作表标签（最好不是总表），在弹出的右键快捷菜单中选择【选定全部工作表】。此时，所有的工作表均被选中，成为一个工作表组合。 2.按住Ctrl键，再点击"总表"的标签，即可将名称 3.鼠标选取需要创建超链接的任意一个分表的单元格，例如D1单元格，输入以下公式： =HYPERLINK("#'总表'!A1","返回总表") 第一个参数"#'总表'!A1"中的“总表”是链接目标表的名称，可以根据实际情况做修改，例如修改为“汇总表”等。 第二个参数是公式在单元格中的显示值，这里是“返回总表”。为"总表"的工作表从工作表组合中剔除 由于分表是工作表组合状态，因此在任一分表上输入以上公式，其它分表也会在相同的位置输入相同的公式，这就很便捷的完成了批量输入公式创建超链接的目的 4.鼠标右键单击任一工作表标签，在右键菜单中选择【取消工作表组合】，至此也就操作完成了。
  • EXCEL创建工作表目录并添加超链接
    • 首先需要在excel表最前面新建一个空白表格，标签改为目录。 打开公式选项卡中的名称管理器—新建—打开新建名称对话框—名称中输入目录—引用位置输入=get.workbook(1)—然后点确定。 在目录的工作表A1单元格中输入函数=INDEX(目录，ROW())，向下拖动鼠标填充。这里index第一个参数的目录就是刚才设置好的宏表函数get.workbook（1）的名称。 然后在B1单元格中输入目录，按回车后，ctrl+E填充数据。然后把B列中的数据剪切—粘贴到A列中。 最后就是建立超链接了，在B2单元格中输入函数=HYPERLINK("#"&A2&"!A1",A2)，向下填充即可完成超链接的创建了。对于HYPELINK函数，第一个参数表示要打开的文件名或者路径。第二个参数表示要在单元格中显示的字符。这里第一个参数表示跳转到A2单元格对应的工作表中的A1单元格。工作表名称的引用通常用感叹号和双引号表示。 这就是创建目录并建立超链接的方法，当然，也可以选中A2到A6单元格，然后ctrl+K打开超链接对话框手工建立超链接。