Excel 高阶使用（含 ChatGPT）与数据可视化

时间：2024-10-27 15:57:16

在数据分析师的岗位要求中经常提及 Excel，不少数据分析师将其深入使用，成为了自己的利器和法宝。Excel 的强大之处在于，只需要掌握一些简单的技巧，就能够轻松完成复杂的任务。还可以通过 Excel 更直观、更生动地展示和解释数据，让数据变得更有说服力。对于想要在职场上脱颖而出的新人和小白，精通 Excel 可以作为自己的亮点之一。

Excel 高阶使用的技能

我们可以在简历和面试中可以亮出来下面的 Excel 技能包，从使用复杂的公式，再到酷炫可视化图表，可以让面试官为你点头称赞 ????。

快速处理数据

当小白面对要处理大量数据时，Excel 是一个非常强大的工具。其中分列、去重复项和导入数据是 Excel 中处理数据时最常用的功能之一，它们可以更快速地处理数据，节省大量时间和精力。此外，Excel 还有非常多的快捷键，可以有助于处理数据。

  • 分列

分列功能是按分隔符或者固定的宽度进行分列，分隔符可以是 Tab 键、逗号、空格、横杠、字符、文字等等，将包含多个维度数据的单元格(字段)分成多个单元格(字段)，方便后续的数据处理和分析。

​

  • 重复项

表格中常常会遇到重复项，不仅会占用储存空间，还容易造成数据混淆。Excel 不仅可以单列去重，还能多个列关联后进行重复判断，轻松移除冗余信息。

  • 导入数据

从外部来源（如数据库或文本、文件、网站）快速引入数据，以便后续进行数据处理。例如，将按照月份保存且字段相同的 Excel 文件放置在同一个文件夹，使用文件夹导入数据的方式实现数据合并，可以减少重复的手工复制粘贴工作。如果善于运用，Excel 还可以实现爬虫功能~

​

  • 快捷键

Excel 还有很多快捷键，可以帮助我们更高效地进行数据处理和分析。除了大家常用的快速复制、粘贴和剪贴等，像选择区域用到的人其实并不多。提醒一下快速保存是一个好习惯 ~ 推荐一些 Excel 常用的快捷键：

Excel 函数进阶

Excel 函数的功能非常强大，可以实现各种数据处理和分析任务，使日常工作更有效率，函数使用的考题也时常出现在面试环节中。最常用函数如：

  • VLOOKUP：在一个表格中查找某个数值，并返回与该数值相关联的其他数据；

  • COUNTIF：计算满足特定条件的单元格数量；

  • SUMIF：符合特定条件单元格数量进行求和。

举个使用 SUMIF 的例子????语法为：SUMIF(range, criteria, [sum_range])

  • range：需要检查的单元格范围。

  • criteria：用于筛选单元格的条件。

  • sum_range：需要求和的单元格范围。如果省略此参数，则默认使用 range 参数中指定的单元格范围进行求和。

假设有一张“销售数据”的表格，其中包含省份、城市、销售额。如果您想要计算某个省份总销售额，可以使用以下公式：=SUMIF(A:A, "广东省", C:C)，先筛选 A 列中省份是"广东省" 的行，再对筛选出的销售额 C 列进行求和 。Excel 中还有许多其他的内置函数，甚至可以使用自定义函数，帮助用户完成复杂的计算和数据处理任务。

​

数据透视表和复杂报表

当需要汇总大量数据时，可以使用 Excel 中的数据透视表和制作图表，也自己整合数据制作复杂报表。

  • 数据透视表

可以按照不同的维度对数据进行分类汇总，并且可以轻松地进行排序、筛选和计算聚合指标等操作。也通过制作各种类型的图表，我们可以更容易地看出数据之间的关系和趋势，从中洞察隐藏在数据背后的信息。

​

  • 复杂报表

制作复杂报表，不同 Sheet 可以对应数据源和数据展示，使得用户可以深入且多维度进行数据分析。????中国式报表<br/>复杂报表有个典型的应用，就是我们经常提到的中国式报表。中国式复杂报表=“复杂的报表结构+大量数据呈现”，会在单个报表中显示多层级的数据信息。

​

中国式报表是由于业务数据关系复杂，且没有良好的报表制作习惯产生的。常常是按老板想法来随意做报表，造成了“独特”的复杂需求，因此才有了中国式复杂报表的说法。虽然复杂报表的复用性较差，但 Excel 制作复杂报表可以看出其强大的计算功能、灵活的数据排列和整理方式、多样化的数据可视化选项、灵活的格式设置、数据的动态更新和自动化以及易于共享和交流。

录制宏和 VBA

当面对重复性的任务，Excel 的录制宏和 VBA 提高我们的工作效率。

  • 录制宏

通过记录我们在 Excel 中执行的操作，然后自动生成相应的 VBA 代码。这样，我们就可以通过运行这些代码来快速执行之前录制过的操作，而不必再手动一步步重复执行。录制宏可以用于任何可以手动完成的 Excel 操作，例如格式设置、数据清洗等等。

  • VBA（Visual Basic for Applications）

是 Excel 的一种内置的编程语言，用于在 Excel 中自动化任务、自定义功能和处理数据。VBA 允许用户编写和执行宏（macros），即一系列自动化的操作和命令。

​

需要注意的是，录制宏虽然方便易用，但它生成的代码往往较为笨拙，且不够灵活。因此，如果需要实现更为复杂的功能，还是需要使用 VBA 编写自己的代码。虽然使用 VBA 需要一定的编程知识和技巧，学习和掌握 VBA 后，可以更好地利用 Excel 的功能，并实现更高效的数据处理和自动化任务。

数据分析

工欲善其事必先利其器， Excel 中隐藏的“数据分析”工具值得我们挖掘。分析工具库中包括描述统计、方差分析、预测等。

​

例如：利用移动平均进行销售额预测

预测结果希望模型平滑能力强，能够更好消除随机干扰及误差。实际中可选择不同的 N 值进行预测比较，最终确定合适的 N 值。甚至可以在一次移动平均基础上再进行一次移动平均，此时称为二次移动平均技术。分析库工具赋予用户解析数据、发现洞察力并做出基于数据的决策的能力，使 Excel 成为数据分析中助力工具。

Excel 与 ChatGPT 的强强联手

ChatGPT 是当下最热门的智能工具，可以用自然语言理解和响应问题和任务。我们可以使用 ChatGPT 解释 Excel 概念并提供公式和函数的语法，还可以借助 ChatGPT 分析 Excel 数据、提供宏的 VBA 代码。如果还没有账户的，需要先创建。如果你有帐户，卷起袖子，让我们开始吧~

1.询问 ChatGPT 解释 Excel 概念

当开始新的聊天会话（或注销并重新进入）时，请记住告诉 ChatGPT，面对的问题以及背景。

2.使用 ChatGPT 编写 Excel 公式和函数

可以让 ChatGPT 解释 Excel 函数的语法并提供如何使用它们的示例。这是我问 ChatGPT 的一个例子：

3.将 ChatGPT 与 Excel 的 VBA 和宏一起使用

如果你对 Excel 宏或不熟悉 VBA 编程语言，ChatGPT 将帮助你。与创建公式时一样，可以先询问语法和用法。可以要求 ChatGPT 为我们创建宏。不过它实际上并没有触及我们的工作簿，但它能为我们提供所需的所有步骤。这是我给 ChatGPT 的一个示例提示，让它帮我写一个 VBA 的宏：

​

只要告诉 ChatGPT 我们需要的具体帮助或任务，无论是 Excel 的基本操作、公式的使用，还是更高级的 VBA 编程，它将为我们提供指导和解决方案。

4.如何集成 Excel 和 ChatGPT

再让我们看看将 ChatGPT 直接集成到我们的 Excel 工作簿中。 ChatGPT 提供官方 API 可以调用，一些供应商提供了 Excel 加载项，使我们可以从工作表仪表板使用该工具。这些加载项与 Excel 2016 和更新版本兼容。要查看可用内容，请执行以下步骤：

​

有需要的朋友可以动手实践一下，这边就不具体操作演示。需要提醒一下，ChatGPT 可以作为我们的小助手，但不能代替我们思考，不要过分依赖人工智能。之前的文章也提到，面对更加精细的逻辑分析与问题的判断，需要的是数据分析师的分析思维和逻辑思维。发现问题和价值的能力是数据分析师的真正价值所在，是程序与人类最大的区别，也是人工智能始终挂着“人工”的原因。

Excel 数据可视化工具

俗话说，“字不如表，表不如图”，图表比文字更直观、清晰，职场中也方便向上级汇报。这就是为什么我们需要可视化庞大的数据以将其从复杂变为简单、直观。

可视化案例

我们还可以使用 Excel 的图表，可视化我们的数据。Excel 数据可视化的几个值得夸赞的方面：

  • 用户友好性：Excel 提供了直观且易于使用的界面，可以通过简单的拖拽、选择和调整来创建各种图表，无需编写代码或进行复杂的配置。

  • 多样的图表类型：Excel 提供了丰富的图表类型，包括柱状图、折线图、饼图、散点图、热力图等等。

  • 数据处理能力：用户可以使用 Excel 的公式、函数和筛选器等工具对数据进行处理、计算和筛选，从而更好地准备数据用于可视化。

  • 交互性：Excel 的数据可视化可以通过添加交互功能增强用户体验，可以使用数据透视表、筛选器和条件格式等功能，为用户提供交互式的控制和数据切片。

下面是一些使用 Excel 进行创造性数据可视化的示例：

Excel 的易用性、丰富的图表类型、数据处理能力和与其他功能的集成性，为用户提供了一个的数据可视化平台。然而，对于大规模数据处理和复杂数据分析，可能需要其他专业工具或编程语言来实现更高级的功能。

和 商业智能 BI

Excel 单张表的数据处理量最高是 104 万条，普通电脑只要数据量达到几万条就会卡死。在数据量较多时，使用宏工具会耗占电脑较多内存，且经常出现带不动的情况。Excel 对于企业级的数据分析来说，着实有些不够用。如果 VBA 代码也无法解决数据需求，数据量太大想实现实时更新，使用 Excel 也束手无策怎么办？别担心，随着数据分析的需求越来越高，更高级的数据处理和数据分析工具应运而生，也就是现在很多公司的选择——商业智能化 BI 工具。

  • BI 分析工具能够快速处理海量数据，超大数据量下秒级查询，数据分析结果也能快速呈现。

  • 相较于 Excel 较为繁琐的操作过程，借助 BI 分析工具，很多统计分析功能的公式都已内置好，可以轻松通过自助式拖拽等方式设计出丰富的可视化图表。内置仪表板模板，需要的时候随时就可以直接复用。

  • 分析数据在 BI 可以应用于大屏展示（轮播、分屏）等，让领导无需手工干预便可对数据一目了然，且可通过移动端（手机、平板等）随时随地查看数据统计分析的成果。

之前文章里推荐过几款数据可视化工具，比如：

  • Power BI 拥有丰富的图库且能够制作交互式的报表；

  • Metabase 通过友好的用户体验和集成工具快速分析数据；

  • DataEase 支持多种数据源连接，可通过拖拉拽方式制作图表，并方便与他人分享。

数据可视化的技能也是很多岗位的进阶技能，是区分小白的分水岭。大家可以选择适合自己的工具进行数据可视化和分析，在数据探索的道路不断前进。

相关文章