excel学习库

一、场景铺垫

你是一家物流公司的数据分析师，需要对包裹运输和客户服务数据进行分析。这些数据包含包裹单号、寄件人姓名、收件人姓名、发货城市、收货城市、发货日期、收货日期、运输时长（收货日期 - 发货日期）、包裹重量（千克）、运费、包裹类型（如文件、小包裹、大包裹等）、客户满意度评分（1 - 5 分）、客户是否投诉（是 / 否）、客户类型（如个人客户、企业客户等）等信息。你想要从这些数据中获取有用信息，例如计算每种包裹类型的平均运费、找出运输时长最长的发货城市（按平均运输时长）、统计不同客户类型的投诉比例、分析客户满意度评分与运费之间的关系等，以便优化物流线路、调整运费策略和提高客户服务质量。Excel 函数嵌套将帮助你高效地进行这些数据分析任务。

二、示例表格及函数嵌套

（一）计算每种包裹类型的平均运费（AVERAGEIF、SUM、IF、ISNUMBER、ROW）

公式原理 ：

• AVERAGEIF($K$2:$K$6,"文件",$J$2:$J$6)：AVERAGEIF函数用于计算满足指定条件（包裹类型为 “文件”）的单元格区域（运费区域$JJ）的平均值。它会在$KK这个包裹类型区域中查找 “文件”，每当找到时，就将对应的列中的运费纳入求平均值的计算范围。同样的原理适用于 “小包裹” 和 “大包裹” 等其他包裹类型平均运费的计算。

（二）找出运输时长最长的发货城市（按平均运输时长）（AVERAGEIF、SUM、MAX、IF、MATCH、INDEX、ROW）

公式原理 ：

• AVERAGEIF($D$2:$D$6,"北京",$G$2:$G$6)：AVERAGEIF函数用于计算满足指定条件（发货城市为 “北京”）的单元格区域（运输时长区域$GG）的平均值。它会在$DD这个发货城市区域中查找 “北京”，每当找到时，就将对应的列中的运输时长纳入求平均值的计算范围。同样的原理适用于 “广州”、“上海” 和 “深圳” 等其他发货城市平均运输时长的计算。

• MAX($B$2:$B$6)：MAX函数用于找出$BB$6$ 这个平均运输时长数据区域中的最大值，即平均运输时长最长的数值。

• IF(B2 = MAX($B$2:$B$6),"是","否")：这是一个条件判断函数。如果某个发货城市的平均运输时长（B2）等于前面计算出的最大值，就表示该发货城市的运输时长最长，返回 “是”；否则返回 “否”。

（三）统计不同客户类型的投诉比例（COUNTIF、SUM、OFFSET、MATCH、ROW）

公式原理 ：

• COUNTIF($N$2:$N$6,"个人客户",$M$2:$M$6,"是")：COUNTIF函数用于统计满足多个条件（客户类型为 “个人客户” 且客户是否投诉为 “是”）的单元格数量，这个数量就是个人客户的投诉人数。同样的方法用于计算企业客户的投诉人数。

• COUNTIF($N$2:$N$6,"个人客户")：COUNTIF函数用于统计个人客户的总人数。同样的方法用于统计企业客户的总人数。

• B2/C2：用投诉人数除以总人数得到投诉比例。这里计算个人客户的投诉比例，同样的方法用于计算企业客户的投诉比例。

（四）分析客户满意度评分与运费之间的关系（CORREL、OFFSET、MATCH、ROW）

公式原理 ：

• CORREL($J$2:$J$6,$L$2:$L$6)：CORREL函数用于计算两个数据区域（运费区域$JJ和客户满意度评分区域$LL）之间的相关系数。相关系数的取值范围在 - 1 到 1 之间，接近 1 表示正相关，接近 - 1 表示负相关，接近 0 表示无相关。通过这个系数可以分析出运费与客户满意度评分之间的关系。

作为物流公司的数据分析师，通过运用这些 Excel 函数嵌套对包裹运输和客户服务数据进行分析，可以深入了解不同包裹类型的运费情况、运输时长最长的发货城市、不同客户类型的投诉情况以及客户满意度评分与运费之间的关系等信息，从而优化物流线路、调整运费策略、改善客户服务以提高客户服务质量。