数据分析师笔试试卷一：Excel、SQL、Python、数据分析思维

原创

已于 2024-07-10 13:57:47 修改 · 3.9k 阅读

66 ·

CC 4.0 BY-SA版权

文章标签：

#数据分析 #python #excel #sql

于 2024-07-09 23:34:12 首次发布

一、题目：给定一份包含多个产品销售数据的 Excel 表格，包括产品 ID 、产品名称、销售日期和销售额。请编写公式计算出每个产品的总销售额，并按销售额从高到低进行排序。

使用 SUMIF 函数计算每个产品的总销售额：

假设你的数据从 A2 到 D7，其中 A 列是产品ID，B 列是产品名称，C 列是销售日期，D 列是销售额。假设产品名称从 B2 开始。

在 E 列输入产品名称列表（假设从 E2 开始），然后在 F2 输入以下公式：

=SUMIF($B$2:$B$7, E2, $D$2:$D$7)

这将计算产品名称（B 列）与 E2 中指定的产品名称匹配的所有销售额 (D 列)，并将结果显示在 F2 中。

使用排序功能排序产品：

选择 F2:F7 区域（或你的数据区域），然后点击“数据”选项卡上的“排序”，选择按 F2 列排序，并按销售额从大到小排序。

二、题目：请给出你常用的 Excel 函数，并指出你的使用场景。
我常用的 Excel 函数包括：

SUMIF / SUMIFS：
- 使用场景：用于根据条件对数据进行求和。比如，统计特定产品的销售额、特定时间范围内的销售总额等。
VLOOKUP / INDEX-MATCH：
- 使用场景：用于查找数据表中的值。VLOOKUP 用于在垂直列中查找值，而 INDEX-MATCH 更灵活，可以在任意方向上查找和返回值。常用于从大数据表中提取相关信息，如员工工资数据、客户信息等。
IF / IFERROR：
- 使用场景：IF 用于根据条件返回不同的值或执行不同的计算，IFERROR 用于处理错误，使公式在遇到错误时返回自定义值或空白。常用于条件判断和错误处理，如检查数据的有效性、处理异常情况等。
COUNTIF / COUNTIFS：
- 使用场景：用于计算符合特定条件的单元格数量。COUNTIF 用于单个条件，COUNTIFS 可以使用多个条件进行计数。常用于统计符合条件的数据个数，如统计某产品的销售数量、统计某时间段内的订单数等。
AVERAGE / AVERAGEIF / AVERAGEIFS：
- 使用场景：用于计算数据的平均值。AVERAGE 计算选定单元格范围的平均值，AVERAGEIF 根据条件计算特定单元格范围的平均值，AVERAGEIFS 可以使用多个条件进行平均值计算。常用于计算销售数据的平均销售额、评分数据的平均分等。
PivotTable：
- 使用场景：用于数据透视分析。可以对大量数据进行快速分析和汇总，包括计算总和、平均值、计数等，以便更好地理解数据的趋势和关系。
DATE / DATEVALUE / YEAR / MONTH / DAY：
- 使用场景：用于处理日期和时间数据。DATE 用于创建日期，DATEVALUE 将文本日期转换为日期值，YEAR / MONTH / DAY 用于提取日期的年、月、日部分。常用于分析时间序列数据、计算日期差异等。

这些函数覆盖了我在数据分析和报告制作中常用的核心功能，帮助我处理和分析大量数据，从而做出有效的业务决策和报告。

举例说明
当使用Excel时，经常会用到以下几个常用函数，它们在不同情况下有不同的应用场景：

SUMIF / SUMIFS
- 举例说明：
  假设你有一个销售数据表格，包括产品名称、销售日期和销售额。你想要计算每个产品的总销售额。在这种情况下，可以使用 SUMIF 函数来实现。
```
=SUMIF(B2:B100, "产品A", D2:D100)
```
  这个公式将计算在产品名称列（B列）中为“产品A”的所有销售额总和（D列）。

当处理 Excel 中的数据分析时，SUMIFS 和 SUMPRODUCT 是两个非常有用的函数，它们各自在不同的情况下有不同的用途和优势：

SUMIFS 函数

SUMIFS 函数用于根据多个条件对范围内的单元格进行求和。它的语法如下：

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

sum_range: 要求和的范围。
criteria_range1, criteria_range2, …: 包含要应用条件的范围。
criteria1, criteria2, …: 定义条件的值。

举例说明：

假设你有一个销售数据表格，包括产品名称、销售日期和销售额。你想要计算特定日期范围内每个产品的总销售额。可以使用 SUMIFS 函数来实现：

=SUMIFS(D2:D100, C2:C100, ">="&DATE(2024,1,1), C2:C100, "<="&DATE(2024,1,31))

这个公式将计算在 2024 年 1 月份内销售日期（C列）在指定范围内的所有销售额总和（D列）。

SUMPRODUCT 函数

SUMPRODUCT 函数在 Excel 中是一个非常强大和多功能的函数，主要用于对数组或范围中对应元素进行乘法后求和。除了简单的乘法求和外，SUMPRODUCT 还可以用于处理更复杂的数据分析和计算需求，以下是一些 SUMPRODUCT 函数的常见用法和示例：

简单乘法求和

假设有两列数据，分别是销售数量和销售单价，你想要计算总销售额：
```
=SUMPRODUCT(B2:B10, C2:C10)
```
这个公式将会计算每个销售数量（B 列）与其对应的销售单价（C 列）的乘积，并将所有乘积求和，得到总销售额。

条件求和

带条件的求和</

最低0.47元/天解锁文章