【Excel数据分析】职场小白必备技能（建议收藏），完全保姆级教程；包含数据导入、字符串日期等数据清洗，及数据统计信息，条件统计、区间统计等。

Excle进行数据分析是最简单最容易入手的，很多进入职场小白都需要。
只要学会以下的Excel数据分析，在职场中混就更加容易啦~

1、数据导入

1.1 将文件夹内所有excel数据整理到一个excel中

• 写一个vbs代码，将下面的代码拷贝到一个记事本里面，再保存为vbs格式的文件，双击就可以执行。

' 创建Excel应用程序对象
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False ' 如果不需要显示Excel界面，设置为False

' 定义文件夹路径
folderPath = "C:\Users\Desktop\1\" ' 请将此路径替换为你的文件夹路径，确保路径末尾有斜杠

' 获取文件夹中的Excel文件列表
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(folderPath)
Set colFiles = objFolder.Files

' 创建一个新的Excel工作簿来存储合并的数据
Set objWorkbook = objExcel.Workbooks.Add
Set objSheet = objWorkbook.Sheets(1)

' 初始化计数器
fileCount = 0
rowOffset = 1 ' 初始行偏移量，用于确定数据写入的起始行

' 遍历文件夹中的文件
For Each objFile In colFiles
    If LCase(objFSO.GetExtensionName(objFile.Name)) = "xlsx" Or LCase(objFSO.GetExtensionName(objFile.Name)) = "xls" Then
        ' 打开Excel文件
        Set sourceWorkbook = objExcel.Workbooks.Open(folderPath & objFile.Name)
        Set sourceSheet = sourceWorkbook.Sheets(1) ' 假设只处理第一个工作表
        
        ' 获取源工作表的数据区域
        Set sourceRange = sourceSheet.UsedRange
        
        ' 复制数据到目标工作表
        sourceRange.Copy
        objSheet.Cells(rowOffset, 1).PasteSpecial Paste:=-4163
        
        ' 更新行偏移量
        rowOffset = rowOffset + sourceRange.Rows.Count
        
        ' 关闭源工作簿
        sourceWorkbook.Close False
        
        ' 增加文件计数器
        fileCount = fileCount + 1
        
        ' 如果已经处理了指定数量的文件，则退出循环
        If fileCount = 7 Then Exit For
    End If
Next

' 清理剪贴板（可选，但推荐）
objExcel.CutCopyMode = False

' 保存合并后的工作簿
outputFilePath = folderPath & "MergedData.xlsx"
objWorkbook.SaveAs outputFilePath, 51 ' 使用Excel 2007及以上版本的格式保存

' 清理对象
Set sourceRange = Nothing
Set sourceSheet = Nothing
Set sourceWorkbook = Nothing
Set colFiles = Nothing
Set objFolder = Nothing
Set objFSO = Nothing
objWorkbook.Close False
objExcel.Quit
Set objSheet = Nothing
Set objWorkbook = Nothing

' 释放COM对象（可选，但推荐，特别是在长时间运行的脚本中）
' 注意：这里不需要再次设置objExcel为Nothing，因为上面的代码已经将其释放了

' 提示完成
MsgBox "数据合并完成，已保存到 " & outputFilePath, vbInformation

1.2 将多个shell页的内容合并到一个excel中

1. 打开Excel工作簿并按 Alt + F11 打开VBA编辑器。
2. 在VBA编辑器中，插入一个新模块（右键点击VBAProject > 插入 > 模块）。
3. 将以下代码粘贴到模块中：

Sub MergeSheetsToSummary()
    Dim ws As Worksheet
    Dim summarySheet As Worksheet
    Dim lastRowSummary As Long
    Dim lastRowSource As Long
    Dim rngToCopy As Range
    
    ' 创建一个新的工作表用于汇总
    Set summarySheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    summarySheet.Name = "Summary"
    
    ' 初始化汇总表的起始行
    lastRowSummary = 1
    
    ' 遍历所有工作表（除了汇总表本身）
    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> summarySheet.Name Then
            ' 找到源工作表的最后一行（假设数据在连续的列中，从A列开始）
            lastRowSource = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
            
            ' 设置要复制的范围（这里假设复制整个A列到最后一行有数据的行）
            ' 如果您的数据跨越多列，请调整这里的范围
            Set rngToCopy = ws.Range("A1:A" & lastRowSource)
            
            ' 将源工作表的数据复制到汇总表中
            rngToCopy.Copy Destination:=summarySheet.Cells(lastRowSummary, 1)
            
            ' 更新汇总表的最后一行
            lastRowSummary = lastRowSummary + rngToCopy.Rows.Count
        End If
    Next ws
    
    ' 可选：自动调整汇总表的列宽
    summarySheet.Columns("A:Z").AutoFit ' 假设数据最多到Z列，根据实际情况调整
    
    ' 提示完成
    MsgBox "所有工作表的内容已合并到'Summary'表中。", vbInformation
End Sub

4、关闭VBA编辑器并返回Excel。
5、按 Alt + F8 打开宏对话框，选择 MergeSheetsToSummary，然后点击“运行”。

2、数据清洗

• Excel数据清洗就是对一些函数的应用，对于函数不需要死记硬背，只需要理解，然后直接套公式。
• 以下分成几类函数来进行数据清洗

2.1 关联匹配

1、VLOOKUP 查找某值对应的值

• 作用：用于在表格或范围的第一列中查找特定值，并返回同一行中的另一列中的值。

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value（必需）：要查找的值。
• table_array（必需）：包含数据的表格或范围。查找值应在该范围的第一列中。
• col_index_num（必需）：要返回值的列号，相对于table_array的第一列。
• [range_lookup]（可选）：指定是否执行近似匹配。TRUE表示近似匹配（默认），FALSE表示精确匹配。

2、INDEX 查询到某值

• 作用：可以根据行号和列号从一个数组或区域中返回一个值或引用。它允许用户从多维数据范围中检索特定的值。

INDEX(reference, row_num, [column_num], [area_num])

• reference：必需。对一个或多个单元格区域的引用。
• row_num：必需。引用中某行的行号。
• column_num：可选。引用中某列的列号。
• area_num：可选。选择一个引用区域，从该区域中返回row_num和column_num的交集。

• 例如 ：=INDEX(F2:H6,3,2)
• 意思是：在 F2:H2 区域内，找到3行2列的值
• INDEX最好和Match等函数进行使用

3、MATCH 查找数据对应位置

• 作用：用于在指定数组或范围内搜索特定项，并返回该项在数组或范围中的相对位置。
• 注：当你需要查找某个值的位置，而不是该值本身时。match函数就特别有用。

MATCH(lookup_value, lookup_array, [match_type])

• lookup_value：你想要匹配的值。
• lookup_array：包含可能包含查找值的一维数组或范围。
• [match_type]：（可选）指定匹配的类型。这个参数是可选的，但如果不提供，Excel会使用默认值1。
  1 或省略：表示小于或等于查找值的最大值。数组中的值必须按升序排序。
  0：表示完全匹配。数组中的值不需要排序。
  -1：表示大于或等于查找值的最小值。数组中的值必须按降序排序。

4、Find、Search 查找文本在单元格中位置

• Find函数：用于查找字符串中某个字符或文本第一次出现的位置，且该函数是大小写敏感的。

语法结构：
FIND(find_text, within_text, [start_num])

• find_text：要查找的字符或文本。
• within_text：要在其中查找的字符串。
• [start_num]：可选参数，指定开始查找的位置，默认为1。如果要从字符串的特定位置开始查找，可以提供一个整数值作为这个参数的输入。

示例：

• 假设在单元格A1中有一个字符串“Hello, world!”，想要找到逗号的位置
• 可以在另一个单元格中输入公式=FIND(“,”, A1)，然后按下回车键，Excel将返回6，代表逗号在字符串中的位置。

2.2 字符串数据清洗

1、Trim、Ltrim、Rtrim 清除字符串空格

• Trim：用于去除字符串两端（开头和结尾）空白字符（包括空格、制表符、换行符等）
• Ltrim：用于去除字符串左边的空白字符串
• Rtrim：用于去除字符串右边的空白字符串

语法：

• TRIM(text)
• LTRIM(text)
• RTRIM(text)

2、Left、Right、Mid 截取字符串

• Left：用于从文本字符串的左侧开始提取指定数量的字符。
• Right：用于从文本字符串的右侧开始提取指定数量的字符。
• Mid：用于从文本字符串中的指定位置开始提取指定数量的字符。

语法：
LEFT(text, [num_chars])
RIGHT(text, [num_chars])

• text：必需。包含要提取字符的文本字符串。
• [num_chars]：可选。指定要提取的字符数量。如果省略，则默认提取1个字符。

MID(text, start_num, num_chars)

• text：必需。包含要提取字符的文本字符串。
• start_num：必需。指定提取字符的起始位置（文本中的第一个字符位置为1）。
• num_chars：必需。指定要提取的字符数量。

3、replace、substitute替换字符串

• replace作用：用于将字符串中的部分字符串用另一个字符串替换，且替换的位置和数量都是指定的。

语法结构：
=Replace（old_text，start_num，num_chars，new_text）

• old_text：原始字符串，即要进行替换操作的字符串。
• start_num：开始位置，即从原始字符串的哪个字符开始进行替换。
• num_chars：替换个数，即要替换多少个字符。
• new_text：替换字符串，即用来替换指定位置字符的新字符串。

示例：

• 假设A1单元格中的内容是“1234567890”，想要将第4个字符开始的3个字符替换为“abc”
• 可以在另一个单元格中输入公式=REPLACE（A1，4，3，“abc”），结果将是“123abc7890”。

• Substitute函数：用于将字符串中的部分字符串以新字符串替换

语法结构：
=Substitute（text，old_text，new_text，[instance_num]）

• text：原始字符串，即要进行替换操作的字符串。
• old_text：被替换字符串，即要被替换掉的字符串。
• new_text：替换字符串，即用来替换指定字符串的新字符串。
• instance_num：指定替换第几个，此参数可省略，省略时代表全部替换。如果指定了此参数，则只替换第几个出现的old_text。

示例：

• 假设A1单元格中的内容是“apple banana apple”，想要将第二个“apple”替换为“orange”
• 可以在另一个单元格中输入公式：
  =SUBSTITUTE（A1，“apple”，“orange”，2）
• 结果将是“apple banana orange”。如果省略instance_num参数，则所有“apple”都会被替换为“orange”。

4、Len、Lenb 返回字符串个数、字符数

• Len：返回文本字符串的字符个数
• Lenb：返回文本字符串中的字符数

2.3 逻辑运算 IF、AND、OR、NOT

• IF函数：用于根据指定的条件进行判断，并返回相应的值。

语法结构：
IF(logical_test, [value_if_true], [value_if_false])

• logical_test：表示计算结果为 TRUE 或 FALSE 的任意值或表达式。
• [value_if_true]：logical_test 为 TRUE 时返回的值。如果忽略，则返回 TRUE。
• [value_if_false]：logical_test 为 FALSE 时返回的值。如果忽略，则返回 FALSE。

示例：

• 判断成绩是否及格：=IF(A1>=60, “及格”, “不及格”)。
• 如果A1单元格的值大于或等于60，则返回“及格”，否则返回“不及格”。

• AND函数：用于检查是否所有指定的条件都为真（TRUE）。

语法结构：
AND(logical1, [logical2], …)

• logical1, [logical2], …：需要计算为 TRUE 或 FALSE 的 1 到 255 个条件。

示例1：

• 判断多个条件是否同时满足：=IF(AND(B2>6, C2>6, D2>6), “完成”, “未完成”)。
• 如果B2、C2和D2的值都大于6，则返回“完成”，否则返回“未完成”。

示例2：

• 与SUM函数组合使用：=SUM(IF(AND(C2:C10=“广州”, D2:D10>80),E2:E10))。
• 计算满足条件（地区为广州且销量大于80）的销量之和。

• OR函数：用于检查是否至少有一个指定的条件为真（TRUE）。

语法结构：
OR(logical1, [logical2], …)

• logical1, [logical2], …：需要计算为 TRUE 或 FALSE 的 1 到 255 个条件。

示例：

• 判断至少有一个条件是否满足：=IF(OR(A1>0, B1<5), “满足条件”,> “不满足条件”)。
• 如果A1的值大于0或B1的值小于5，则返回“满足条件”，否则返回“不满足条件”。

• NOT函数：用于对逻辑值求反。

语法结构：
NOT(logical)

• logical：其计算结果为 TRUE 或 FALSE 的任意值或表达式。

示例：

• 对逻辑值求反：=NOT(A1>60)。
• 如果A1的值大于60，则返回FALSE，否则返回TRUE。

2.4 基础统计类函数

1、COUNT：统计指定区域中非空单元格的个数。

2、COUNTA：统计指定区域中非空单元格的总数，包括数字、文本、错误值等。

3、COUNTBLANK：统计指定区域中空白单元格的个数。

2.5 条件统计类函数

1、COUNTIF：根据指定条件统计满足条件的单元格个数。

2、COUNTIFS：根据多个条件统计满足条件的单元格个数。

3、SUMIF：根据指定条件对满足条件的单元格求和。

4、SUMIFS：根据多个条件对满足条件的单元格求和。

5、AVERAGEIF：根据指定条件计算满足条件的单元格的平均值。

6、AVERAGEIFS：根据多个条件计算满足条件的单元格的平均值。

7、MAXIF（或MAXIFS）：根据条件计算满足条件的最大值（注意：某些版本的Excel中可能没有MAXIF函数，但可以使用MAXIFS或数组公式实现类似功能）。

8、MINIF（或MINIFS）：根据条件计算满足条件的最小值（同样，某些版本的Excel中可能没有MINIF函数，但可以使用MINIFS或数组公式）。

2.6 区间统计函数

• FREQUENCY：统计一组数据在不同区间内的分布情况。

2.7 排名与分布函数

• RANK：返回某个数值在一组数值中的排名。
• PERCENTILE：返回一组数据的某个百分位数。
• MODE：返回一组数据中出现次数最多的值（众数）。如果存在多个众数，则返回第一个出现的众数。在Excel的某些版本中，可以使用MODE.MULT函数返回所有众数。

2.8 乘积与求和函数

• SUMPRODUCT：计算两组或多组数据的对应元素乘积之和。这个函数不仅可以用于求和，还可以用于条件计数等复杂操作。
  Excel提供了多种处理时间的函数，这些函数使得用户能够轻松地对日期和时间数据进行操作和分析。以下是一些常用的Excel处理时间函数及其简要说明：

2.9 时间处理

1、当前日期和时间函数

• 函数：NOW()
• 功能：返回当前的日期和时间。
• 语法结构：=NOW()
• 注意：返回的结果为Windows系统中设置的日期和时间，如需自定义格式，可在格式中选择相应的日期和时间格式。

• 函数：TODAY()
• 功能：返回当前的日期。
• 语法结构：=TODAY()
• 注意：除了用TODAY函数获取当前日期外，还可以用快捷键Ctrl+;快速输入当前日期。返回的结果同样为Windows系统中设置的日期，如需自定义格式，可在格式中选择相应的日期格式。

2、日期和时间提取函数

YEAR()
功能：返回日期的年份。
语法结构：=YEAR(日期)

MONTH()
功能：返回日期的月份。
语法结构：=MONTH(日期)

DAY()
功能：返回日期的天数。
语法结构：=DAY(日期)

HOUR()
功能：返回时间中的小时数。
语法结构：=HOUR(时间)

MINUTE()
功能：返回时间中的分钟数。
语法结构：=MINUTE(时间)

SECOND()
功能：返回时间中的秒数。
语法结构：=SECOND(时间)

3、日期和时间构建函数

DATE()
功能：合并年、月、日三个参数，构建一个日期。
语法结构：=DATE(年, 月, 日)
注意：DATE函数中的所有参数都可以是直接输入的数字或单元格引用。参数“年”的值必须在1900~9999之间。

TIME()
功能：合并时、分、秒三个参数，构建一个时间。
语法结构：=TIME(时, 分, 秒)
注意：TIME函数中的所有参数都可以是直接输入的数字或单元格引用。对于参数“时”，任何大于23的值将除以24，将余数作为小时；对于“分”和“秒”，任何大于相应最大值（59）的值将被转换为更高级的时间单位。

4、日期和时间计算函数

DAYS()
功能：返回两个日期之间间隔的天数。
语法结构：=DAYS(终止日期, 开始日期)

EDATE()
功能：返回指定日期之后（或之前）多少个月数的具体日期。
语法结构：=EDATE(指定日期, 月数)
注意：月数为正值将生成未来日期，为负值将生成过去日期。

DATEDIF()
功能：计算两个日期之间的间隔，可以返回间隔的天数、月数或年数。
语法结构：=DATEDIF(开始日期, 终止日期, 比较单位)
注意：比较单位可以是"Y"（年）、“M”（月）或"D"（日）等。

EOMONTH()
功能：返回指定月份最后一天的日期。
语法结构：=EOMONTH(开始日期, 之前或之后的月份数)
注意：这个函数可以帮助用户快速找到某个月份的最后一天。

5、星期和年度周次函数

WEEKDAY()
功能：返回指定日期对应的星期几。
语法结构：=WEEKDAY(日期, [返回值类型])
注意：返回值类型决定了返回的星期几的表示方式。例如，如果设置为2，则返回值为1（星期天）到7（星期六）之间的整数。

WEEKNUM()
功能：返回对应日期是一年中的第几周。
语法结构：=WEEKNUM(日期, [返回值类型])
注意：返回值类型决定了周次的计算方式。例如，在中国通常使用从周一开始计算的周次（返回值类型为2）。

3、数据可视化