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

老范头

一、题目：给定一份包含多个产品销售数据的Excel表格，包括产品ID、产品名称、销售日期和销售额。请编写公式计算出每个产品的总销售额，并按销售额从高到低进行排序。使用SUMIF函数计算每个产品的总销售额：假设你的数据从A2到D7，其中A列是产品ID，B列是产品名称，C列是销售日期，D列是销售额。假设产品名称从B2开始。在E列输入产品名称列表（假设从E2开始），然后在F2输入以下公式：=SUMIF($B$2B$7,E2,$D$2D$7)1这将计算产品名称（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",D2100)1这个公式将计算在产品名称列（B列）中为“产品A”的所有销售额总和（D列）。当处理Excel中的数据分析时，SUMIFS和SUMPRODUCT是两个非常有用的函数，它们各自在不同的情况下有不同的用途和优势：SUMIFS函数SUMIFS函数用于根据多个条件对范围内的单元格进行求和。它的语法如下：SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)1sum_range:要求和的范围。criteria_range1,criteria_range2,…:包含要应用条件的范围。criteria1,criteria2,…:定义条件的值。举例说明：假设你有一个销售数据表格，包括产品名称、销售日期和销售额。你想要计算特定日期范围内每个产品的总销售额。可以使用SUMIFS函数来实现：=SUMIFS(D2100,C2:C100,">="&DATE(2024,1,1),C2:C100,"=DATE(2024,1,1))*(C2:C100=90,"优秀",IF(C2>=80,"良好",IF(C2>=60,"及格","不及格")))1这个公式根据分数（C2）返回相应的等级。使用IFERROR：=IFERROR(B2/C2,"除数不能为零")1这个公式将计算B2除以C2，如果C2是零，将返回自定义的错误消息“除数不能为零”。COUNTIF/COUNTIFS举例说明：假设你有一个订单记录表，包括产品名称和订单数量。你需要统计特定产品的订单数量。可以使用COUNTIF函数来实现单个条件的计数，或者使用COUNTIFS函数来进行多条件的计数。使用COUNTIF：=COUNTIF(B2:B100,"产品A")1这个公式将统计产品名称列（B列）中为“产品A”的订单数量。使用COUNTIFS：=COUNTIFS(B2:B100,"产品A",C2:C100,">100")1这个公式将统计产品名称列（B列）中为“产品A”且订单数量（C列）大于100的记录数量。AVERAGE/AVERAGEIF/AVERAGEIFS举例说明：假设你有一个学生成绩表格，包括每位学生的数学、语文和英语成绩。你需要计算每个科目的平均成绩。可以使用AVERAGE函数计算平均值，使用AVERAGEIF或AVERAGEIFS函数根据条件计算特定范围内的平均值。使用AVERAGE：=AVERAGE(B22)1这个公式将计算B2到D2单元格范围内的平均值，即数学、语文和英语成绩的平均分。使用AVERAGEIF：=AVERAGEIF(B2100,">80")1这个公式将计算B2到D100范围内所有大于80的数的平均值。使用AVERAGEIFS：=AVERAGEIFS(B2100,A2:A100,"张三")1这个公式将在A列中查找“张三”，并计算对应B到D列范围内的平均值。这些例子展示了常见的Excel函数如何在不同的数据分析和处理场景中使用，帮助你理解它们的具体应用。三、题目：假设有一个名为Sales的数据库表，包含字段OrderID,CustomerlD,ProductID,Quantity,Price,Source。MS1个SQL查询，列出每个客户的总消费金额及该客户在Source为"第三方"的消费金额。（请在此区域内编写SQL查询语句。)SELECTCustomerID,SUM(Quantity*Price)ASTotalAmount,SUM(CASEWHENSource='第三方'THENQuantity*PriceELSE0END)ASThirdPartyAmountFROMSalesGROUPBYCustomerID;123456789四、题目：现假设题目3中的Source字段存在多个同义值，如第三方／第三方平台／三方平台，你已经从数据库中导出对应的表格，试着编写一个Python函数，对表格进行数据清洗，最终实现题目3的结果。（请在此区域内直接手写Python代码。)importpandasaspddefclean_and_calculate_sales(dataframe):#将Source字段的多个同义值统一为'第三方'synonym_mapping={'第三方':['第三方','第三方平台','三方平台']}defclean_source(source):forkey,valuesinsynonym_mapping.items():ifsourceinvalues:returnkeyreturnsourcedataframe['Source']=dataframe['Source'].apply(clean_source)#计算每个客户的总消费金额和第三方消费金额result=dataframe.groupby('CustomerID').agg({'Quantity':'sum','Price':'sum','Source':lambdax:sum(x=='第三方')}).reset_index()result.columns=['CustomerID','TotalAmount','ThirdPartyAmount']returnresult#示例用法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)12345678910111213141516171819202122232425262728293031323334353637383940五、题目：如果你需要展示不同校区销售情况的对比，你会选择哪种类型的图表？并简述为什么？我会选择使用柱状图来展示不同校区的销售情况对比。理由：易于比较：柱状图能够直观地比较不同校区之间的销售数据，每个校区的销售额可以用柱子的高度表示，便于快速理解和比较各个校区的表现。适合多变量比较：如果需要同时比较多个不同的指标（如销售额、订单数量等），柱状图也可以通过不同的柱子颜色或者分组来展示，使得比较更加全面和有效。清晰表达趋势：如果需要展示不同校区的销售趋势（如季度或年度变化），柱状图可以轻松地表达时间序列的变化，每个时间点或者周期对应一个柱子。易于解释和分享：柱状图的视觉效果简单直观，不需要专业的统计知识也能快速理解，非常适合用于汇报和分享分析结果。因此，柱状图是展示不同校区销售情况对比的首选图表类型。六、题目：描述一个数据分析项目从开始到结束的一般流程，包括关键的步骤和考虑因素。（请在此区域内描述数据分析项目的流程。)数据分析项目通常包括以下一般流程，每个步骤都是关键的，影响着项目最终的质量和成果：确定业务目标和问题：确定项目的背景和目标，理解业务需求和问题，确保数据分析的方向和焦点明确。数据收集和理解：收集相关数据，并进行初步的数据理解。这包括数据的来源、结构、质量检查（如缺失值、异常值处理）等。数据准备和清洗：对数据进行清洗和预处理，包括但不限于去除重复值、处理缺失值、数据转换和格式化、合并数据集等，以确保数据质量和一致性。探索性数据分析（EDA）：通过统计学和可视化方法探索数据的特征、趋势和关系。这一步骤有助于深入理解数据，发现潜在的模式和见解。数据建模和分析：根据业务问题选择合适的数据分析技术和模型（如统计分析、机器学习算法），进行数据建模和分析，生成预测或解释性模型。解释和分享结果：解释分析结果，向利益相关者（如管理层、业务部门）沟通和分享洞见和建议。确保结果易于理解，并根据反馈进行必要的调整和补充分析。实施和监控：根据分析结果制定行动计划，并实施相关的业务策略或改进措施。同时，建立监控机制来跟踪和评估行动计划的效果和业务指标的变化。总结和反馈：总结项目的经验教训和成果，为未来的数据分析项目提供参考。收集用户反馈和建议，持续优化和改进数据分析的流程和方法。每个步骤都需要细致的计划和执行，同时考虑数据的质量、业务的复杂性以及利益相关者的需求，以确保项目能够达到预期的目标和效果。七、题目：作为一家教培机构的分析师，假设现在营收下滑30%，你该如何分析？面对教培机构营收下滑30%，作为分析师，可以采取以下分析步骤来理解和解决问题：数据收集与整理：收集营收数据的历史记录，包括每月或每季度的总营收情况。获取与营收相关的详细数据，如课程销售数据、学生人数、课程类别、价格策略等。确保数据的完整性和准确性，进行必要的数据清洗和预处理。趋势分析：分析营收的长期趋势和季节性变化，查看过去几个周期的数据，确认是否有周期性影响。检查营收下滑是否是突发性的，还是逐渐积累的趋势。成本分析：分析各项成本，包括人力成本、运营成本、市场推广费用等。确认成本是否出现了异常增加或者效率下降的情况。市场分析：分析竞争环境，包括其他教培机构的市场份额、课程特点和价格策略。考察市场需求的变化，如新兴教育技术、在线学习平台的竞争等因素对市场的影响。客户分析：分析学生群体的特征和行为，包括学生来源、学习偏好、消费习惯等。评估学生流失率和留存率，了解为何部分学生选择离开或选择竞争对手的原因。课程分析：分析不同课程的销售情况和利润贡献，识别哪些课程表现良好，哪些课程可能需要调整或淘汰。研究课程设置和更新的策略，以提高课程的吸引力和竞争力。策略制定：基于分析结果制定应对策略，可能包括优化课程组合、调整价格策略、改进市场营销策略、提升服务质量等方面。制定长期和短期的市场推广计划，以恢复营收增长势头并提高客户满意度。监控与调整：设立监控指标，跟踪执行策略后的效果。定期进行业绩评估和反馈，及时调整策略，确保营收的稳步增长和企业的长期发展。通过系统的数据分析和策略制定，教培机构可以更好地理解营收下滑的原因，并采取针对性的措施来应对市场挑战，实现业务的持续增长和发展。