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

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

1. 使用 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 中。

2. 使用排序功能排序产品：

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

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

1. SUMIF / SUMIFS：

   • 使用场景：用于根据条件对数据进行求和。比如，统计特定产品的销售额、特定时间范围内的销售总额等。

2. VLOOKUP / INDEX-MATCH：

   • 使用场景：用于查找数据表中的值。VLOOKUP 用于在垂直列中查找值，而 INDEX-MATCH 更灵活，可以在任意方向上查找和返回值。常用于从大数据表中提取相关信息，如员工工资数据、客户信息等。

3. IF / IFERROR：

   • 使用场景：IF 用于根据条件返回不同的值或执行不同的计算，IFERROR 用于处理错误，使公式在遇到错误时返回自定义值或空白。常用于条件判断和错误处理，如检查数据的有效性、处理异常情况等。

4. COUNTIF / COUNTIFS：

   • 使用场景：用于计算符合特定条件的单元格数量。COUNTIF 用于单个条件，COUNTIFS 可以使用多个条件进行计数。常用于统计符合条件的数据个数，如统计某产品的销售数量、统计某时间段内的订单数等。

5. AVERAGE / AVERAGEIF / AVERAGEIFS：

   • 使用场景：用于计算数据的平均值。AVERAGE 计算选定单元格范围的平均值，AVERAGEIF 根据条件计算特定单元格范围的平均值，AVERAGEIFS 可以使用多个条件进行平均值计算。常用于计算销售数据的平均销售额、评分数据的平均分等。

6. PivotTable：

   • 使用场景：用于数据透视分析。可以对大量数据进行快速分析和汇总，包括计算总和、平均值、计数等，以便更好地理解数据的趋势和关系。

7. DATE / DATEVALUE / YEAR / MONTH / DAY：

   • 使用场景：用于处理日期和时间数据。DATE 用于创建日期，DATEVALUE 将文本日期转换为日期值，YEAR / MONTH / DAY 用于提取日期的年、月、日部分。常用于分析时间序列数据、计算日期差异等。

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

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

1. 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 函数的常见用法和示例：

1. 简单乘法求和

   假设有两列数据，分别是销售数量和销售单价，你想要计算总销售额：

   =SUMPRODUCT(B2:B10, C2:C10)
   

   这个公式将会计算每个销售数量（B 列）与其对应的销售单价（C 列）的乘积，并将所有乘积求和，得到总销售额。

条件求和

2. 带条件的求和

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

   =SUMPRODUCT((C2:C100 >= DATE(2024, 1, 1)) * (C2:C100 <= DATE(2024, 1, 31)) * (B2:B100 = "产品A") * D2:D100)
   

   这个公式中，每个条件用一个条件数组来表示，条件数组中的 TRUE 或 FALSE 将乘以对应的销售额数组，然后将所有乘积求和，得到符合条件的总销售额。

加权求和

3. 加权平均

   假设你有一个成绩表格，包括学生姓名、考试成绩和考试权重（不同科目的权重可能不同），你想要计算加权平均分数：

   =SUMPRODUCT(B2:B10, C2:C10) / SUM(C2:C10)
   

   这个公式首先计算每位学生的成绩乘以其对应的权重，然后将所有乘积求和，最后除以总权重的和，得到加权平均分数。

多条件计算

4. 多条件计数

   假设你有一个订单表格，包括产品名称、订单数量和订单状态（已完成、进行中等），你想要计算特定产品的已完成订单数量：

   =SUMPRODUCT((B2:B100 = "产品A") * (D2:D100 = "已完成"))
   

   这个公式中，每个条件用一个条件数组来表示，条件数组中的 TRUE 或 FALSE 将乘以对应的计数（1 或 0），然后将所有乘积求和，得到符合条件的已完成订单数量。

总结

• SUMIFS 主要用于根据多个条件进行求和，适合处理具有多个筛选条件的数据。
• SUMPRODUCT 主要用于对数组或范围中对应元素进行乘法后求和，适合进行复杂的数组运算和统计计算，如加权求和、多条件计算等。

2. VLOOKUP / INDEX-MATCH

   • 举例说明：
     假设你有一个员工信息表，包括员工姓名和对应的工资。你需要根据员工姓名查找他们的工资。可以使用 VLOOKUP 函数来实现，或者使用 INDEX-MATCH 组合来进行更灵活的查找。

     • 使用 VLOOKUP：

       =VLOOKUP("张三", A2:B100, 2, FALSE)
       

       这个公式将在 A 列（员工姓名）中查找“张三”，并返回对应的 B 列（工资）的值。

     • 使用 INDEX-MATCH：

       =INDEX(B2:B100, MATCH("张三", A2:A100, 0))
       

       这个组合公式将在 A 列中查找“张三”的位置，然后用 INDEX 函数返回相应的工资（B 列）。

3. IF / IFERROR

   • 举例说明：
     假设你有一个考试成绩表格，需要根据分数判断学生的等级，并处理可能的错误。可以使用 IF 函数来设置条件判断，使用 IFERROR 函数来处理公式中可能出现的错误情况。

     • 使用 IF：

       =IF(C2 >= 90, "优秀", IF(C2 >= 80, "良好", IF(C2 >= 60, "及格", "不及格")))
       

       这个公式根据分数（C2）返回相应的等级。

     • 使用 IFERROR：

       =IFERROR(B2/C2, "除数不能为零")
       

       这个公式将计算 B2 除以 C2，如果 C2 是零，将返回自定义的错误消息“除数不能为零”。

4. COUNTIF / COUNTIFS

   • 举例说明：
     假设你有一个订单记录表，包括产品名称和订单数量。你需要统计特定产品的订单数量。可以使用 COUNTIF 函数来实现单个条件的计数，或者使用 COUNTIFS 函数来进行多条件的计数。

     • 使用 COUNTIF：

       =COUNTIF(B2:B100, "产品A")
       

       这个公式将统计产品名称列（B列）中为“产品A”的订单数量。

     • 使用 COUNTIFS：

       =COUNTIFS(B2:B100, "产品A", C2:C100, ">100")
       

       这个公式将统计产品名称列（B列）中为“产品A”且订单数量（C列）大于100的记录数量。

5. AVERAGE / AVERAGEIF / AVERAGEIFS

   • 举例说明：
     假设你有一个学生成绩表格，包括每位学生的数学、语文和英语成绩。你需要计算每个科目的平均成绩。可以使用 AVERAGE 函数计算平均值，使用 AVERAGEIF 或 AVERAGEIFS 函数根据条件计算特定范围内的平均值。

     • 使用 AVERAGE：

       =AVERAGE(B2:D2)
       

       这个公式将计算 B2 到 D2 单元格范围内的平均值，即数学、语文和英语成绩的平均分。

     • 使用 AVERAGEIF：

       =AVERAGEIF(B2:D100, ">80")
       

       这个公式将计算 B2 到 D100 范围内所有大于80的数的平均值。

     • 使用 AVERAGEIFS：

       =AVERAGEIFS(B2:D100, A2:A100, "张三")
       

       这个公式将在 A 列中查找“张三”，并计算对应 B 到 D 列范围内的平均值。

这些例子展示了常见的 Excel 函数如何在不同的数据分析和处理场景中使用，帮助你理解它们的具体应用。

三、题目：假设有一个名为 Sales 的数据库表，包含字段 OrderID , CustomerlD , ProductID , Quantity , Price , Source 。 MS 1个 SQL 查询，列出每个客户的总消费金额及该客户在 Source 为"第三方"的消费金额。（请在此区域内编写 SQL 查询语句。)

SELECT
    CustomerID,
    SUM(Quantity * Price) AS TotalAmount,
    SUM(CASE WHEN Source = '第三方' THEN Quantity * Price ELSE 0 END) AS ThirdPartyAmount
FROM
    Sales
GROUP BY
    CustomerID;

四、题目：现假设题目3中的 Source 字段存在多个同义值，如第三方／第三方平台／三方平台，你已经从数据库中导出对应的表格，试着编写一个 Python 函数，对表格进行数据清洗，最终实现题目3的结果。（请在此区域内直接手写 Python 代码。)

import pandas as pd

def clean_and_calculate_sales(dataframe):
    # 将 Source 字段的多个同义值统一为 '第三方'
    synonym_mapping = {
        '第三方': ['第三方', '第三方平台', '三方平台']
    }
    
    def clean_source(source):
        for key, values in synonym_mapping.items():
            if source in values:
                return key
        return source
    
    dataframe['Source'] = dataframe['Source'].apply(clean_source)
    
    # 计算每个客户的总消费金额和第三方消费金额
    result = dataframe.groupby('CustomerID').agg({
        'Quantity': 'sum',
        'Price': 'sum',
        'Source': lambda x: sum(x == '第三方')
    }).reset_index()
    
    result.columns = ['CustomerID', 'TotalAmount', 'ThirdPartyAmount']
    
    return result

# 示例用法
if __name__ == "__main__":
    # 假设dataframe是你的Sales数据表格，这里用示例数据代替
    sales_data = {
        'CustomerID': [1, 1, 2, 3, 3],
        'Quantity': [2, 3, 1, 4, 5],
        'Price': [10, 20, 15, 8, 12],
        'Source': ['第三方', '第三方平台', '直销', '第三方', '三方平台']
    }
    
    df = pd.DataFrame(sales_data)
    cleaned_result = clean_and_calculate_sales(df)
    print(cleaned_result)

五、题目：如果你需要展示不同校区销售情况的对比，你会选择哪种类型的图表？并简述为什么？
我会选择使用柱状图来展示不同校区的销售情况对比。

理由：

易于比较： 柱状图能够直观地比较不同校区之间的销售数据，每个校区的销售额可以用柱子的高度表示，便于快速理解和比较各个校区的表现。

适合多变量比较： 如果需要同时比较多个不同的指标（如销售额、订单数量等），柱状图也可以通过不同的柱子颜色或者分组来展示，使得比较更加全面和有效。

清晰表达趋势： 如果需要展示不同校区的销售趋势（如季度或年度变化），柱状图可以轻松地表达时间序列的变化，每个时间点或者周期对应一个柱子。

易于解释和分享： 柱状图的视觉效果简单直观，不需要专业的统计知识也能快速理解，非常适合用于汇报和分享分析结果。

因此，柱状图是展示不同校区销售情况对比的首选图表类型。

六、题目：描述一个数据分析项目从开始到结束的一般流程，包括关键的步骤和考虑因素。（请在此区域内描述数据分析项目的流程。)

数据分析项目通常包括以下一般流程，每个步骤都是关键的，影响着项目最终的质量和成果：

1. 确定业务目标和问题： 确定项目的背景和目标，理解业务需求和问题，确保数据分析的方向和焦点明确。

2. 数据收集和理解： 收集相关数据，并进行初步的数据理解。这包括数据的来源、结构、质量检查（如缺失值、异常值处理）等。

3. 数据准备和清洗： 对数据进行清洗和预处理，包括但不限于去除重复值、处理缺失值、数据转换和格式化、合并数据集等，以确保数据质量和一致性。

4. 探索性数据分析（EDA）： 通过统计学和可视化方法探索数据的特征、趋势和关系。这一步骤有助于深入理解数据，发现潜在的模式和见解。

5. 数据建模和分析： 根据业务问题选择合适的数据分析技术和模型（如统计分析、机器学习算法），进行数据建模和分析，生成预测或解释性模型。

6. 解释和分享结果： 解释分析结果，向利益相关者（如管理层、业务部门）沟通和分享洞见和建议。确保结果易于理解，并根据反馈进行必要的调整和补充分析。

7. 实施和监控： 根据分析结果制定行动计划，并实施相关的业务策略或改进措施。同时，建立监控机制来跟踪和评估行动计划的效果和业务指标的变化。

8. 总结和反馈： 总结项目的经验教训和成果，为未来的数据分析项目提供参考。收集用户反馈和建议，持续优化和改进数据分析的流程和方法。

每个步骤都需要细致的计划和执行，同时考虑数据的质量、业务的复杂性以及利益相关者的需求，以确保项目能够达到预期的目标和效果。

七、题目：作为一家教培机构的分析师，假设现在营收下滑30%，你该如何分析？
面对教培机构营收下滑30%，作为分析师，可以采取以下分析步骤来理解和解决问题：

1. 数据收集与整理：

   • 收集营收数据的历史记录，包括每月或每季度的总营收情况。
   • 获取与营收相关的详细数据，如课程销售数据、学生人数、课程类别、价格策略等。
   • 确保数据的完整性和准确性，进行必要的数据清洗和预处理。

2. 趋势分析：

   • 分析营收的长期趋势和季节性变化，查看过去几个周期的数据，确认是否有周期性影响。
   • 检查营收下滑是否是突发性的，还是逐渐积累的趋势。

3. 成本分析：

   • 分析各项成本，包括人力成本、运营成本、市场推广费用等。
   • 确认成本是否出现了异常增加或者效率下降的情况。

4. 市场分析：

   • 分析竞争环境，包括其他教培机构的市场份额、课程特点和价格策略。
   • 考察市场需求的变化，如新兴教育技术、在线学习平台的竞争等因素对市场的影响。

5. 客户分析：

   • 分析学生群体的特征和行为，包括学生来源、学习偏好、消费习惯等。
   • 评估学生流失率和留存率，了解为何部分学生选择离开或选择竞争对手的原因。

6. 课程分析：

   • 分析不同课程的销售情况和利润贡献，识别哪些课程表现良好，哪些课程可能需要调整或淘汰。
   • 研究课程设置和更新的策略，以提高课程的吸引力和竞争力。

7. 策略制定：

   • 基于分析结果制定应对策略，可能包括优化课程组合、调整价格策略、改进市场营销策略、提升服务质量等方面。
   • 制定长期和短期的市场推广计划，以恢复营收增长势头并提高客户满意度。

8. 监控与调整：

   • 设立监控指标，跟踪执行策略后的效果。
   • 定期进行业绩评估和反馈，及时调整策略，确保营收的稳步增长和企业的长期发展。

通过系统的数据分析和策略制定，教培机构可以更好地理解营收下滑的原因，并采取针对性的措施来应对市场挑战，实现业务的持续增长和发展。