Excel数据分析与实战

（一）数据分析

Excel数据分析是指使用Excel软件对数据进行整理、分析、可视化和解读，以便于做出基于数据的决策。（主流为Excel2016，国家大部分考试接使用此版本）以下是一些Excel数据分析的基本步骤和工具：

数据准备

1. 数据清洗：删除重复记录、修正错误、填补缺失值。
2. 数据排序：按照特定列的值对数据进行排序。
3. 数据筛选：根据特定条件筛选出需要分析的数据子集。

数据分析

1. 描述性统计分析：使用AVERAGE、SUM、MIN、MAX、COUNT、STDEV等函数来概括数据的基本特征。
2. 函数应用：使用VLOOKUP、HLOOKUP、INDEX、MATCH等进行数据查找；使用IF、SUMIF、COUNTIF等进行条件计算。
3. 数据分析工具包：使用Excel的分析工具包（Analysis ToolPak）进行假设检验、回归分析、时间序列分析等。

数据可视化

1. 图表制作：创建柱状图、折线图、饼图、散点图等来直观展示数据。
2. 条件格式：使用颜色、图标等视觉元素来强调数据的某些方面。
3. 数据透视表：动态地汇总、分析和探索大量数据。

数据建模

1. 预测分析：使用趋势线、线性回归等方法进行预测。
2. 优化问题：使用 Solver 插件来解决最优化问题。
3. 模拟分析：使用数据表、单变量求解、规划求解器等进行模拟。

实战应用

1. 市场分析：分析销售数据、客户数据来识别市场趋势和客户偏好。
2. 财务分析：进行财务报表分析、预算编制、成本分析等。
3. 运营分析：分析生产数据、库存数据来优化运营流程。
4. 人力资源分析：分析员工数据、绩效数据来进行人力资源规划。

高级功能

1. 宏和VBA：自动化重复性任务，创建自定义函数和工具。
2. Power Query：用于数据清洗和转换的强大工具。
3. Power Pivot：提供数据建模和复杂分析的高级功能。

（二）认识Excel

认知Excel指的是理解并掌握Excel软件的基本特性和功能，以及如何使用Excel进行数据处理和分析。（博主使用的是Excel2024介绍，大部分按键相同，也可以自行了解）以下是一些认知Excel的基础知识和技能：

1. 界面和基本操作：熟悉Excel的界面布局，包括菜单、工具栏、单元格、工作表和工作簿。掌握基本的操作，如打开、保存、关闭工作簿，以及如何创建、删除和重命名工作表。
2. 数据输入和编辑：学习如何在单元格中输入数据，包括文本、数字、日期和时间。了解如何编辑单元格内容，如复制、粘贴、剪切和删除数据。
3. 单元格格式：掌握如何设置单元格格式，包括字体、颜色、对齐方式、边框和背景。了解如何调整行高和列宽，以及如何隐藏或显示行和列。
4. 使用公式和函数：学习如何使用Excel的公式和函数来执行计算。了解常用的函数，如SUM、AVERAGE、MIN、MAX、COUNT、VLOOKUP和HLOOKUP等。
5. 数据排序和筛选：掌握如何对数据进行排序和筛选，以便于分析和查找信息。
6. 数据透视表：了解如何创建和使用数据透视表来汇总和分析大量数据。
7. 图表制作：学习如何根据数据创建不同类型的图表，如柱状图、折线图、饼图等，以便于直观展示分析结果。
8. 条件格式：了解如何使用条件格式来高亮显示特定条件的数据。
9. 宏和VBA：了解宏的概念和如何录制宏来自动化重复性任务。对于高级用户，学习VBA（Visual Basic for Applications）编程，以便于创建自定义的Excel工具和功能。
10. 数据分析和高级功能：探索Excel的高级分析功能，如数据分析工具包（Analysis ToolPak）、假设检验、回归分析等。通过学习和实践，认知Excel将帮助您更高效地处理和分析数据，提高工作效率，并为决策提供支持。

（三）数据的获取

外部数据的获取

获取文本数据是数据分析的第一步，尤其是在使用Excel进行数据分析时。以下是一些常见的文本数据获取方法：详细写文本获取和数据库获取

1. 手动输入：

• 直接在Excel中手动输入数据。这种方法适用于数据量较小的情况。

2. 复制和粘贴：

• 从其他应用程序（如文本编辑器、网页、PDF等）复制文本数据，然后粘贴到Excel中。

3. 从网络获取：

• 使用Excel的数据功能中的获取数据选项，可以从网络上的表格数据源（如HTML表格）直接导入数据。

4. 从数据库获取：

• 通过Excel的外部数据工具，可以连接到各种数据库（如SQL Server、MySQL、Oracle等），并导入数据。从数据库获取数据到Excel通常涉及以下步骤：

1. 打开Excel：
   • 打开Excel程序，并创建一个新的工作簿或打开一个现有工作簿。
2. 访问数据菜单：
   • 在Excel的菜单栏中，找到并点击“数据”标签，这通常位于界面的顶部。
3. 获取数据：
   • 在“数据”菜单中，找到“获取数据”组，点击“从数据库”按钮。在某些版本的Excel中，这个选项可能直接显示为“从其他源”或类似名称。
4. 选择数据库类型：
   • 在弹出的“获取数据”窗口中，选择您要连接的数据库类型，例如SQL Server、MySQL、Oracle、Access等。
5. 建立数据库连接：
   • 点击“连接”按钮，弹出“连接属性”对话框。在这里，您需要输入数据库的连接信息，包括服务器地址、数据库名称、用户名和密码等。
6. 测试连接：
   • 输入连接信息后，点击“测试连接”按钮以确保Excel能够成功连接到数据库。
7. 选择查询类型：
   • 在连接成功后，您可以选择“使用SQL语句”来编写自定义的SQL查询，或者使用“表、视图或SQL查询”来选择数据库中的表或视图。
8. 编写SQL查询（可选）：
   • 如果您选择了“使用SQL语句”，在弹出的SQL查询编辑器中编写您的SQL查询。例如，SELECT * FROM table_name。
9. 导入数据：
   • 确认查询或表选择后，点击“确定”按钮。Excel将执行查询并将结果导入到新的工作表中。
10. 调整数据：
    • 导入完成后，您可能需要对数据进行进一步的调整，比如调整列宽、设置单元格格式、删除不必要的列等，以确保数据的可读性和准确性。
11. 保存工作簿：
    • 在完成所有必要的调整后，不要忘记保存您的工作簿。请注意，从数据库获取数据到Excel可能需要安装适当的数据库驱动程序或使用Excel的数据库连接功能。此外，某些数据库可能需要特定的权限才能访问。如果您在连接过程中遇到问题，可能需要联系数据库管理员或检查您的连接设置。

5. 使用Power Query：

• Power Query是Excel的一个强大功能，可以用来从各种数据源（包括文本文件、网页、数据库等）中获取和转换数据。

6. 导入文本文件：

• Excel支持导入多种文本文件格式，如CSV（逗号分隔值）、TXT（纯文本文件）等。可以通过数据菜单中的从文本/CSV导入数据。在Excel中导入文本文件是一个相对简单的过程。以下是在Excel中导入文本文件的详细步骤：

1. 打开Excel：
   • 打开Excel程序，并创建一个新的工作簿或打开一个现有工作簿。
2. 访问数据菜单：
   • 在Excel的菜单栏中，找到并点击“数据”标签，这通常位于界面的顶部。
3. 从文本/CSV导入：
   • 在“数据”菜单中，找到“获取数据”组，点击“从文本/CSV”按钮。在某些版本的Excel中，这个选项可能直接显示为“从文本文件”或类似名称。
4. 选择文件：
   • 浏览并选择您想要导入的文本文件（如CSV、TXT等）。点击“导入”按钮。
5. 配置文件导入：
   • 在弹出的“导入数据”对话框中，您可以预览文件内容，并配置导入选项，如分隔符、列格式、数据类型等。
6. 指定数据格式：
   • 如果Excel没有正确识别列的格式，您可以通过点击列标题旁边的下拉菜单来指定正确的数据格式（如文本、日期、数字等）。
7. 选择导入位置：
   • 在对话框的底部，选择您希望数据导入的位置。您可以选择将数据导入当前工作表的一个新区域，或者创建一个新的工作表来放置数据。
8. 完成导入：
   • 确认所有设置都正确后，点击“导入”按钮。Excel将开始导入过程，并将文本文件的数据加载到指定的工作表中。
9. 调整数据：
   • 导入完成后，您可能需要对数据进行进一步的调整，比如调整列宽、设置单元格格式、删除不必要的列等，以确保数据的可读性和准确性。
10. 保存工作簿：
    • 在完成所有必要的调整后，不要忘记保存您的工作簿。

7. 使用VBA脚本：

• 对于高级用户，可以使用Excel的VBA（Visual Basic for Applications）来编写自定义的脚本来获取文本数据。

8. 使用API：

• 对于需要定期更新的数据，可以使用应用程序编程接口（API）来获取数据。这通常需要一些编程知识，但可以通过编写脚本或使用专门的工具来实现。

9. 扫描和OCR：

• 对于纸质文档，可以使用扫描仪进行数字化，然后使用光学字符识别（OCR）软件将扫描的图像转换为可编辑的文本数据。

10. 其他数据集成工具：

- 使用第三方数据集成工具，如Tableau、Alteryx等，可以连接到不同的数据源，并将数据导入Excel。

获取文本数据时，需要注意数据的格式和质量。数据可能需要进行清洗和格式化，以确保在Excel中的可用性和准确性。此外，还需要考虑数据的更新频率和来源的可靠性。

（四）公式和函数

在Excel中输入公式和函数是进行数据计算和分析的基础。公式和函数在此不详细介绍，博主只处理个人笔记所需要的，详细的会写在另一篇文章中。以下是一些基本的步骤和提示：

输入公式

1. 选择单元格：

   • 首先，点击您想要输入公式的单元格。

2. 输入等号：

   • 开始输入公式前，首先输入一个等号 =。这是告诉Excel接下来输入的是公式，而不是文本。

3. 输入公式内容：

   • 接着输入您的公式。公式可以包括数字、单元格引用、运算符（如 +、-、*、/）和其他函数。

4. 引用单元格：

   • 您可以通过点击单元格或手动输入单元格引用（如 A1）来引用单元格。

5. 使用自动完成：

   • 在输入公式的过程中，Excel会提供自动完成建议。您可以使用箭头键和Tab键来浏览建议，并按Enter键选择一个建议。

6. 查看公式结果：

   • 输入完公式后，单元格将显示计算结果。如果您想查看公式本身，只需点击该单元格即可。

输入函数

1. 选择单元格：

   • 点击您想要输入函数的单元格。

2. 插入函数：

   • 您可以手动输入函数，或者使用“公式”菜单中的“插入函数”选项。点击“插入函数”后，会弹出一个对话框，您可以在其中选择所需的函数。

3. 选择函数：

   • 在对话框中，从列表中选择一个函数，或者搜索特定的函数。

4. 填写函数参数：

   • 选定函数后，Excel会显示一个函数参数输入框。在这里，您需要输入函数的参数。参数可以是数字、文本、单元格引用或其他函数。

5. 确认函数：

   • 输入所有必要的参数后，点击“确定”或按Enter键，Excel将计算函数的结果并将其显示在单元格中。

公式和函数的常见操作

• 拖动填充柄：使用鼠标拖动单元格的填充柄（小方块），可以快速将公式应用到相邻的单元格。

• 复制和粘贴公式：您可以通过复制和粘贴来重复使用公式，但请注意，单元格引用可能会相对或绝对变化。

• 编辑公式：双击单元格或选中单元格后按F2键，可以编辑现有的公式。通过掌握公式和函数的输入，您可以充分利用Excel的计算和分析能力来处理数据。

一，使用数组公式

在Excel中，数组公式可以用来对多个数据执行计算，并将结果作为一个数组返回。数组公式可以用于单个单元格，也可以用于多个单元格（称为多单元格数组公式）。以下是如何在Excel中使用数组公式的步骤：

单个单元格数组公式

1. 选择单元格：

   • 点击您想要输入数组公式的单元格。

2. 输入公式：

   • 输入数组公式，通常涉及到区域（多个单元格）的计算。例如，如果您想计算A1到A10的和的平方，您可以使用数组公式 {=SUM(A1:A10)^2}。

3. 输入数组公式：

   • 使用Ctrl+Shift+Enter而不是单独的Enter键来完成数组公式的输入。Excel将在公式周围添加大括号 {} 来表示这是一个数组公式。

4. 查看结果：

   • 单元格将显示数组公式的计算结果。

多单元格数组公式

1. 选择单元格区域：

   • 选择您想要输入多单元格数组公式的区域。

2. 输入公式：

   • 在区域的一个角落的单元格中输入数组公式。例如，如果您想在一列中计算A1到A10的和的平方，您可以在B1中输入数组公式 =SUM(A1:A10)^2。

3. 输入数组公式：

   • 使用Ctrl+Shift+Enter而不是单独的Enter键来完成数组公式的输入。Excel将在公式周围添加大括号 {} 来表示这是一个数组公式，并将结果扩展到所选区域的所有单元格。

4. 查看结果：

   • 所选区域中的每个单元格将显示数组公式的计算结果。

数组公式的注意事项

• 内存和性能：数组公式可能会占用大量内存，尤其是在处理大型数据集时。这可能会导致Excel的运行速度变慢。

• 编辑数组公式：要编辑数组公式，您需要双击包含公式的单元格或选择该单元格并按下F2键。然后，您可以像编辑普通公式一样编辑数组公式。

• 错误值：如果数组公式中包含错误，Excel可能会显示一个错误值，如#N/A、#VALUE!等。

二，设置日期和时间数据

在Excel中设置日期和时间数据是管理数据的重要组成部分。以下是一些关于如何在Excel中设置和使用日期和时间数据的步骤和提示：

输入日期和时间

1. 输入日期：

   • 在单元格中直接输入日期，例如“2023-01-01”或“01/01/2023”，取决于您的区域设置。
   • 使用Excel的日期序列功能，只需输入“1/1/2023”并按Enter键，Excel会自动将其格式化为日期。

2. 输入时间：

   • 在单元格中直接输入时间，例如“12:30 PM”或“12:30:00”，取决于您的时间格式。
   • 要输入当前时间，按下Ctrl+Shift+；组合键。

设置日期和时间格式

1. 选择单元格：

   • 选择包含日期或时间的单元格。

2. 打开格式设置：

   • 右击选定的单元格，然后选择“格式单元格”，或者使用快捷键Ctrl+1。

3. 选择日期或时间格式：

   • 在“格式单元格”对话框中，选择“日期”或“时间”类别，然后从提供的格式中选择一个。

4. 自定义格式：

   • 如果预设的格式不符合您的需求，您可以选择“自定义”类别，并输入自定义的格式代码。例如，输入“yyyy-mm-dd”来显示日期为“2023-01-01”。

使用日期和时间函数

1. TODAY()：

   • 输入公式=TODAY()来获取当前的日期。

2. NOW()：

   • 输入公式=NOW()来获取当前的日期和时间。

3. DATE()：

   • 使用=DATE(year, month, day)函数来创建特定的日期。

4. TIME()：

   • 使用=TIME(hour, minute, second)函数来创建特定的时间。

5. DATEDIF()：

   • 使用=DATEDIF(start_date, end_date, unit)函数来计算两个日期之间的差异。

其他日期和时间功能

• 自动填充日期序列：选择一个日期单元格，然后将鼠标指针放在单元格的右下角填充柄上，拖动以创建日期序列。

• 排序和筛选日期：可以对日期进行排序和筛选，以便于分析。

• 条件格式：可以使用条件格式根据日期或时间的高亮显示数据。

三，认识数学函数

在Excel中，数学函数是一类用于执行数学运算的函数，它们可以帮助您对数据进行计算和分析。以下是一些常用的Excel数学函数及其用途：

1. SUM（求和）：

   • 用于计算一组数值的总和。
   • 语法：SUM(number1, [number2], ...)
   • 示例：=SUM(A1:A10) 计算A1到A10单元格的数值总和。

2. SUMIF（条件求和）：

   • 根据指定条件对范围内的数值进行求和。
   • 语法：SUMIF(range, criteria, [sum_range])
   • 示例：=SUMIF(B1:B10, ">50", A1:A10) 对A1到A10中与B1到B10中大于50的单元格对应的单元格进行求和。

3. SUMIFS（多条件求和）：

   • 根据多个条件对范围内的数值进行求和。
   • 语法：SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
   • 示例：=SUMIFS(A1:A10, B1:B10, ">50", C1:C10, "=1") 对A1到A10中满足B1到B10大于50且C1到C10等于1的条件的单元格进行求和。

4. AVERAGE（平均值）：

   • 用于计算一组数值的平均值。
   • 语法：AVERAGE(number1, [number2], ...)
   • 示例：=AVERAGE(A1:A10) 计算A1到A10单元格的数值平均值。

5. COUNT（计数）：

   • 用于计算一组数值中有多少个数字。
   • 语法：COUNT(value1, [value2], ...)
   • 示例：=COUNT(A1:A10) 计算A1到A10单元格中的数字数量。

6. COUNTIF（条件计数）：

   • 根据指定条件对范围内的数值进行计数。
   • 语法：COUNTIF(range, criteria)
   • 示例：=COUNTIF(A1:A10, ">50") 计算A1到A10中大于50的单元格数量。

7. COUNTIFS（多条件计数）：

   • 根据多个条件对范围内的数值进行计数。
   • 语法：COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
   • 示例：=COUNTIFS(A1:A10, ">50", B1:B10, "=1") 计算A1到A10中大于50且B1到B10等于1的单元格数量。

8. MIN（最小值）：

   • 用于找出一组数值中的最小值。
   • 语法：MIN(number1, [number2], ...)
   • 示例：=MIN(A1:A10) 找出A1到A10单元格中的最小数值。

9. MAX（最大值）：

   • 用于找出一组数值中的最大值。
   • 语法：MAX(number1, [number2], ...)
   • 示例：=MAX(A1:A10) 找出A1到A10单元格中的最大数值。

10. RAND（随机数）：

    • 用于生成一个大于等于0且小于1的随机数。
    • 语法：RAND()
    • 示例：=RAND() 生成一个随机数。

11. ABS（绝对值）：

    • 返回数值的绝对值。
    • 语法：ABS(number)
    • 示例：=ABS(-5) 返回5。

12. CEILING（向上取整）：

    • 将数字向上舍入到最接近的指定基数的倍数。
    • 语法：CEILING(number, significance)
    • 示例：=CEILING(2.5, 1) 返回3。

13. FLOOR（向下取整）：

    • 将数字向下舍入到最接近的指定基数的倍数。
    • 语法：FLOOR(number, significance)
    • 示例：=FLOOR(2.5, 1) 返回2。

14. ROUND（四舍五入）：

    • 将数字四舍五入到指定的位数。
    • 语法：ROUND(number, num_digits)
    • 示例：=ROUND(2.55, 1) 返回2.5。

15. RANK.EQ（等级）：

    • 返回某个数值在一列数值中的排位。
    • 语法：RANK.EQ(number, ref, [order])
    • 示例：=RANK.EQ(A2, A:A) 返回A2在A列中的排位。

16. INT（取整）：

    • 返回数值的整数部分。
    • 语法：INT(number)
    • 示例：=INT(8.9) 返回8。

17. MOD（取余）：

    • 返回两个数值相除后的余数。
    • 语法：MOD(dividend, divisor)
    • 示例：=MOD(10, 3) 返回1。

18. PI（圆周率）：

    • 返回圆周率的值。
    • 语法：PI()
    • 示例：=PI() 返回3.14159…

19. POWER（幂）：

    • 返回一个数的指定幂次方。
    • 语法：POWER(number, power)
    • 示例：=POWER(2, 3) 返回8。

20. SQRT（平方根）：

    • 返回数值的平方根。
    • 语法：SQRT(number)
    • 示例：=SQRT(16) 返回4。

21. SUMPRODUCT（乘积和）：

    • 计算两组数值的乘积之和。
    • 语法：SUMPRODUCT(array1, [array2], ...)
    • 示例：=SUMPRODUCT(A1:A5, B1:B5) 返回A1B1到A5B5的总和。

22. TRUNC（截断）：

    • 返回数值的小数部分被截断后的整数。
    • 语法：TRUNC(number)
    • 示例：=TRUNC(8.9) 返回8。

四，认识统计函数

Excel中的统计函数主要用于处理和分析数据集，包括计算平均值、标准差、方差、频率分布、概率分布等。以下是一些常用的统计函数及其用途：

1. AVERAGE（平均值）：

   • 用于计算一组数值的平均值。
   • 语法：AVERAGE(number1, [number2], ...)
   • 示例：=AVERAGE(A1:A10) 计算A1到A10单元格的数值平均值。

2. AVERAGEA（包含文本的平均值）：

   • 类似于AVERAGE函数，但它计算包括文本在内的所有单元格的平均值。
   • 语法：AVERAGEA(value1, [value2], ...)
   • 示例：=AVERAGEA(A1:A10) 计算A1到A10单元格的数值和文本的平均值。

3. STDEV（标准差）：

   • 用于计算一组数值的标准差。
   • 语法：STDEV(number1, [number2], ...)
   • 示例：=STDEV(A1:A10) 计算A1到A10单元格的标准差。

4. STDEVA（包含文本的标准差）：

   • 类似于STDEV函数，但它计算包括文本在内的所有单元格的标准差。
   • 语法：STDEVA(value1, [value2], ...)
   • 示例：=STDEVA(A1:A10) 计算A1到A10单元格的标准差，包括文本。

5. VAR（方差）：

   • 用于计算一组数值的方差。
   • 语法：VAR(number1, [number2], ...)
   • 示例：=VAR(A1:A10) 计算A1到A10单元格的方差。

6. VARA（包含文本的方差）：

   • 类似于VAR函数，但它计算包括文本在内的所有单元格的方差。
   • 语法：VARA(value1, [value2], ...)
   • 示例：=VARA(A1:A10) 计算A1到A10单元格的方差，包括文本。

7. COUNTIF（条件计数）：

   • 根据指定条件对范围内的数值进行计数。
   • 语法：COUNTIF(range, criteria)
   • 示例：=COUNTIF(A1:A10, ">50") 计算A1到A10中大于50的单元格数量。

8. COUNTIFS（多条件计数）：

   • 根据多个条件对范围内的数值进行计数。
   • 语法：COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
   • 示例：=COUNTIFS(A1:A10, ">50", B1:B10, "=1") 计算A1到A10中大于50且B1到B10等于1的单元格数量。

9. FREQUENCY（频率分布）：

   • 返回数值的频率分布。
   • 语法：FREQUENCY(data_array, bins_array)
   • 示例：=FREQUENCY(A1:A10, B1:B10) 返回A1到A10的频率分布。

10. NORM.DIST（正态分布）：

    • 返回指定平均值和标准差的正态分布概率。
    • 语法：NORM.DIST(x, mean, standard_dev, [true])
    • 示例：=NORM.DIST(50, 75, 10, TRUE) 返回50位于平均值为75、标准差为10的正态分布中的概率。抱歉，似乎我之前的回答被截断了。让我继续完成关于统计函数的介绍。

11. NORM.S.DIST（标准正态分布）：

• 返回指定z分数的标准正态分布概率。

• 语法：NORM.S.DIST(z, [cumulative])

• 示例：=NORM.S.DIST(1.645, TRUE) 返回z分数为1.645的累积概率，这通常用于确定置信区间。

12. NORM.INV（逆正态分布）：

• 返回指定概率的正态分布的z分数。

• 语法：NORM.INV(probability, mean, standard_dev)

• 示例：=NORM.INV(0.95, 75, 10) 返回平均值为75、标准差为10的正态分布中概率为95%的z分数。

13. POISSON.DIST（泊松分布）：

• 返回泊松分布的概率值。

• 语法：POISSON.DIST(x, mean, [cumulative])

• 示例：=POISSON.DIST(5, 3, TRUE) 返回平均值为3的泊松分布中，5个事件发生的累积概率。

14. BINOM.DIST（二项分布）：

• 返回二项分布的概率值。

• 语法：BINOM.DIST(number_s, trials, probability_s, [cumulative])

• 示例：=BINOM.DIST(5, 10, 0.2, TRUE) 返回10次试验中成功5次的累积概率。

15. CONFIDENCE.T（t分布置信区间）：

• 返回t分布的置信区间。

• 语法：CONFIDENCE.T(alpha, standard_dev, size)

• 示例：=CONFIDENCE.T(0.05, 10, 25) 返回平均值为10、标准差为25的t分布的95%置信区间。

16. CONFIDENCE.NORM（正态分布置信区间）：

• 返回正态分布的置信区间。

• 语法：CONFIDENCE.NORM(alpha, standard_dev, size)

• 示例：=CONFIDENCE.NORM(0.05, 10, 25) 返回平均值为10、标准差为25的正态分布的95%置信区间。

17. DEVSQ（方差的平方）：

• 计算数值的方差的平方。

• 语法：DEVSQ(number1, [number2], ...)

• 示例：=DEVSQ(A1:A10) 计算A1到A10单元格的数值方差的平方。

18. EXPON.DIST（指数分布）：

• 返回指定参数的指数分布的概率值。

• 语法：EXPON.DIST(x, lambda, [cumulative])

• 示例：=EXPON.DIST(3, 0.5, TRUE) 返回参数为0.5的指数分布中，x为3的概率。

19. LOGNORM.DIST（对数正态分布）：

    • 返回指定平均值和标准差的正态分布的概率。
    • 语法：LOGNORM.DIST(x, mean, standard_dev, [cumulative])
    • 示例：=LOGNORM.DIST(100, 5, 1, TRUE) 返回对数正态分布中，x为100的概率。

认识文本函数

Excel中的文本函数主要用于处理和分析文本数据。这些函数可以用于查找、替换、格式化、转换和操作文本字符串。以下是一些常用的文本函数及其用途：

1. CONCATENATE（连接）：

   • 将多个文本字符串或单元格文本合并为一个连续的文本字符串。
   • 语法：CONCATENATE(text1, text2, ...)
   • 示例：=CONCATENATE("姓名: ", A1, " 性别: ", B1) 将A1和B1单元格的文本合并。

2. TEXTJOIN（文本连接）：

   • 与CONCATENATE类似，但提供更多灵活性，可以指定分隔符和是否忽略空单元格。
   • 语法：TEXTJOIN(delimiter, [concatenate_nulls], text1, [text2], ...)
   • 示例：=TEXTJOIN(", ", TRUE, A1, B1, C1) 将A1、B1和C1单元格的文本合并，并使用逗号分隔，忽略空单元格。

3. TRIM（去除多余空格）：

   • 去除字符串前后的多余空格。
   • 语法：TRIM(text)
   • 示例：=TRIM(" Hello, World! ") 返回"Hello, World!"。

4. PROPER（首字母大写）：

   • 将每个单词的首字母转换为大写，其余字母转换为小写。
   • 语法：PROPER(text)
   • 示例：=PROPER("hello world") 返回"Hello World"。

5. UPPER（转换为大写）：

   • 将所有字符转换为大写。
   • 语法：UPPER(text)
   • 示例：=UPPER("hello world") 返回"HELLO WORLD"。

6. LOWER（转换为小写）：

   • 将所有字符转换为小写。
   • 语法：LOWER(text)
   • 示例：=LOWER("HELLO WORLD") 返回"hello world"。

7. LEN（长度）：

   • 返回文本字符串中字符的数量。
   • 语法：LEN(text)
   • 示例：=LEN("hello world") 返回11。

8. REPT（重复）：

   • 重复文本字符串指定的次数。
   • 语法：REPT(text, number)
   • 示例：=REPT("*", 5) 返回"*****"。

9. REPLACE（替换）：

   • 在文本字符串中替换指定的字符或字符串。
   • 语法：REPLACE(old_text, start_num, num_chars, new_text)
   • 示例：=REPLACE("hello world", 1, 1, "x") 返回"xello world"。

10. SUBSTITUTE（替换）：

    • 在文本字符串中替换指定的字符或字符串。
    • 语法：SUBSTITUTE(text, old_text, new_text, [instance])
    • 示例：=SUBSTITUTE("hello world", "o", "0", 2) 返回"hell0 w0rld"。

11. TRANSPOSE（转置）：

    • 转置单元格区域的内容。
    • 语法：TRANSPOSE(array)
    • 示例：=TRANSPOSE(A1:B2) 转置A1到B2单元格区域的内容。

12. VALUE（文本转换为数值）：

    • 将文本转换为数值。
    • 语法：VALUE(text)
    • 示例：=VALUE("123") 返回123。

认识逻辑函数

Excel中的逻辑函数主要用于处理和分析条件判断。这些函数可以用于测试真假条件、返回TRUE或FALSE值，以及执行基于条件的计算。以下是一些常用的逻辑函数及其用途：

1. AND（与）：

   • 返回所有给定的条件都为TRUE时，结果为TRUE。
   • 语法：AND(logical1, [logical2], ...)
   • 示例：=AND(A1>10, B1<20) 返回A1大于10且B1小于20时的结果。

2. OR（或）：

   • 返回至少一个给定的条件为TRUE时，结果为TRUE。
   • 语法：OR(logical1, [logical2], ...)
   • 示例：=OR(A1>10, B1<20) 返回A1大于10或B1小于20时的结果。

3. NOT（非）：

   • 反转给定条件的逻辑值。
   • 语法：NOT(logical)
   • 示例：=NOT(A1>10) 返回A1不大于10时的结果。

4. IF（条件判断）：

   • 根据指定的条件返回两个不同的值。
   • 语法：IF(condition, value_if_true, value_if_false)
   • 示例：=IF(A1>10, "大于10", "小于或等于10") 返回A1大于10时的结果。

5. IFERROR（错误处理）：

   • 当公式或函数返回错误时，返回指定的值。
   • 语法：IFERROR(value, value_if_error)
   • 示例：=IFERROR(A1/B1, "无法计算") 如果B1为0，则返回"无法计算"。

6. SWITCH（多条件判断）：

   • 根据不同的条件返回不同的值。
   • 语法：SWITCH(value, case1, result1, [case2, result2], ...)
   • 示例：=SWITCH(A1, 1, "一", 2, "二", 3, "三") 返回A1等于1、2或3时的结果。

7. ISBLANK（测试空白）：

   • 测试单元格是否为空白。
   • 语法：ISBLANK(value)
   • 示例：=ISBLANK(A1) 返回A1为空白的真值。

8. ISTEXT（测试文本）：

   • 测试值是否为文本。
   • 语法：ISTEXT(value)
   • 示例：=ISTEXT(A1) 返回A1为文本的真值。

9. ISNUMBER（测试数字）：

   • 测试值是否为数字。
   • 语法：ISNUMBER(value)
   • 示例：=ISNUMBER(A1) 返回A1为数字的真值。

10. ISNONTEXT（测试非文本）：

    • 测试值是否不为文本。
    • 语法：ISNONTEXT(value)
    • 示例：=ISNONTEXT(A1) 返回A1不为文本的真值。

11. ISERROR（测试错误）：

    • 测试值是否为错误。
    • 语法：ISERROR(value)
    • 示例：=ISERROR(A1/B1) 如果B1为0，则返回错误值。

12. ISEVEN（测试偶数）：

    • 测试值是否为偶数。
    • 语法：ISEVEN(value)
    • 示例：=ISEVEN(A1) 返回A1为偶数的真值。

13. ISODD（测试奇数）：

    • 测试值是否为奇数。
    • 语法：ISODD(value)
    • 示例：=ISODD(A1) 返回A1为奇数的真值。

（五）数据的透视表和透视图

在Excel中，数据透视表（PivotTable）和数据透视图（PivotChart）是强大的数据分析工具，它们允许用户通过拖放操作来重新排列和分析数据。

数据透视表（PivotTable）

数据透视表是一种交互式的数据表，它可以根据需要重新组织数据，以提供不同的视角和分析结果。以下是创建数据透视表的基本步骤：

1. 选择数据源：

   • 选择您想要分析的数据区域。

2. 插入数据透视表：

   • 在Excel的“插入”菜单中，选择“数据透视表”。
   • 在弹出的对话框中，选择要放置数据透视表的位置，可以选择现有工作表的一个单元格，或者创建一个新工作表。

3. 选择数据透视表位置：

   • 指定数据透视表的位置后，点击“确定”。

4. 添加字段：

   • 在数据透视表的行标签、列标签和值区域中，拖动字段来创建维度和聚合。
   • 行标签通常用于分组数据，列标签用于显示不同的类别，值区域用于计算汇总或聚合。

5. 格式化和自定义：

   • 可以使用格式工具来美化数据透视表，例如调整单元格格式、添加颜色、创建切片器等。

6. 更新和刷新：

   • 数据透视表会随着数据源的更新而更新，但也可以手动刷新数据透视表以获取最新的数据。

7. 编辑和操作
   在Excel中编辑数据透视表包括添加或删除字段、更改数据透视表布局、设置数据透视表样式等操作。以下是一些基本的编辑数据透视表的步骤：
   -1. 添加或删除字段：

   • 在数据透视表的“数据透视表字段列表”中，您可以看到所有可用的字段。
   • 要添加字段到行标签、列标签或值区域，只需将字段从“数据透视表字段列表”拖动到相应的区域。
   • 要删除字段，从行标签、列标签或值区域中拖动字段到“数据透视表字段列表”中的“<字段列表>”区域。
     -2. 更改数据透视表布局：
   • 在数据透视表的“数据透视表字段列表”中，您可以重新排列字段。
   • 拖动字段以改变它们在行标签、列标签和值区域中的顺序。
     -3. 设置数据透视表样式：
   • 在数据透视表的“设计”选项卡中，您可以找到“数据透视表样式”组。
   • 点击“数据透视表样式”组中的“其他”按钮，选择一个样式来改变数据透视表的外观。
     -4. 刷新数据透视表或更新数据源：
   • 在数据透视表的“分析”选项卡中，找到“刷新”按钮。
   • 点击“刷新”按钮来更新数据透视表中的数据，以反映数据源中的任何更改。
     -5. 重新选择数据源：
   • 如果数据源发生了变化，您可以重新选择新的数据区域作为数据透视表的数据源。
   • 在数据透视表的“分析”选项卡中，找到“数据”组，点击“选择数据”按钮。
   • 在弹出的“选择数据源”对话框中，选择新的数据区域，然后点击“确定”。
     -6. 更改数据透视表的布局和格式：
   • 在数据透视表的“设计”选项卡中，您可以找到“数据透视表样式”组，这里可以更改数据透视表的外观。
   • 在“数据透视表工具”中的“分析”选项卡中，您可以找到“数据透视表选项”组，这里可以设置数据透视表的行为，例如名称、布局、筛选条件等。
     -7. 使用切片器：
   • 切片器是一种控件，可以用来快速筛选数据透视表中的数据。
   • 在数据透视表的“分析”选项卡中，找到“插入切片器”按钮，点击它来创建切片器。
   • 拖动字段到切片器中，然后使用切片器来筛选数据透视表中的数据。
     -8. 使用筛选器：
   • 在数据透视表的“分析”选项卡中，找到“字段列表”按钮，点击它来打开“数据透视表字段列表”对话框。
   • 在“数据透视表字段列表”中，您可以拖动字段到“筛选”区域，然后使用下拉列表来筛选数据透视表中的数据。
     -9. 使用钻取和下拉列表：
   • 钻取是一种操作，允许您在数据透视表中查看更详细的数据。
   • 下拉列表是数据透视表中的一个控件，可以用来快速选择不同的值。
   • 在数据透视表中，右键点击单元格，选择“下拉列表”或“钻取”选项来使用这些功能。

数据透视图（PivotChart）

数据透视图是数据透视表的图形表示形式，它可以帮助用户更直观地理解数据。以下是创建数据透视图的基本步骤：

1. 选择数据透视表：

   • 在数据透视表中，确保数据已经正确分组和聚合。

2. 插入数据透视图：

   • 在数据透视表的“插入”菜单中，选择“数据透视图”。
   • 在弹出的对话框中，选择图表类型，然后点击“确定”。

3. 选择图表类型：

   • 在图表类型对话框中，选择您想要的图表类型，如柱状图、折线图、饼图等。

4. 自定义图表：

   • 数据透视图会自动创建，但您可以使用图表工具来自定义图表的外观，例如添加标题、轴标签、图例、数据标签等。

5. 更新和刷新：

   • 数据透视图会随着数据透视表的更新而更新，但也可以手动刷新数据透视图以获取最新的数据。

（六）数据分析与可视化

在Excel中绘制图形，您通常指的是创建图表来可视化数据。图表是数据的可视化表示，可以帮助您更直观地理解和分析数据。以下是创建图表的基本步骤：

1. 选择数据源：

   • 打开Excel工作簿，选择您想要分析的数据区域。这个区域可以是连续的单元格，也可以是已有的数据表。

2. 插入图表：

   • 在Excel的“插入”菜单中，找到“图表”选项，点击它。
   • 在弹出的图表选择器中，您将看到各种图表类型，如柱形图、折线图、饼图、散点图等。
   • 选择您想要的图表类型，然后点击“确定”以创建图表。

3. 调整图表：

   • 创建图表后，您可以使用图表工具来调整图表的外观和布局。
   • 例如，您可以更改图表的标题、轴标签、图例、数据标签等。

4. 格式化图表：

   • 使用格式工具来美化图表，例如调整单元格格式、添加颜色、创建切片器等。

5. 更新和刷新：

   • 数据透视表会随着数据源的更新而更新，但也可以手动刷新数据透视表以获取最新的数据。
   • 在图表的“图表工具”中的“设计”选项卡中，找到“数据”组，点击“选择数据”按钮。
   • 在弹出的“选择数据源”对话框中，选择新的数据区域，然后点击“确定”。