excel学习库

一、场景铺垫

你是一家物流公司的运营主管，需要对运输成本和效率数据进行分析。这些数据包含运输单号、发货地、目的地、货物名称、货物重量（千克）、运输距离（千米）、运输方式（如公路运输、铁路运输、航空运输等）、运输日期、运输成本、运输时长（小时）、司机姓名（针对公路运输）、列车车次（针对铁路运输）、航班号（针对航空运输）等信息。你想要从这些数据中获取有用信息，例如计算每种运输方式的平均运输成本、找出运输效率最高的路线（按单位时间运输距离）、统计不同发货地的货物运输量、分析货物重量对运输成本的影响等，以便优化运输路线、调整运输方式和降低运营成本。Excel 函数嵌套将帮助你高效地进行这些数据分析任务。

二、示例表格及函数嵌套

（一）计算每种运输方式的平均运输成本（AVERAGEIF、SUM、IF、ISNUMBER、ROW）

公式原理 ：

• AVERAGEIF($G$2:$G$6,"公路运输",$I$2:$I$6)：AVERAGEIF函数用于计算满足指定条件（运输方式为 “公路运输”）的单元格区域（运输成本区域$II）的平均值。它会在$GG这个运输方式区域中查找 “公路运输”，每当找到时，就将对应的列中的运输成本纳入求平均值的计算范围。同样的原理适用于 “铁路运输” 和 “航空运输” 等其他运输方式平均运输成本的计算。

（二）找出运输效率最高的路线（按单位时间运输距离）（SUM、MAX、IF、MATCH、INDEX、ROW）

公式原理 ：

• $F$2/$J$2：计算北京 - 上海这条路线的单位时间运输距离，即运输距离除以运输时长。同样的方法用于计算广州 - 成都和深圳 - 北京等其他路线的单位时间运输距离。

• MAX($B$2:$B$6)：MAX函数用于找出$BB$6$ 这个单位时间运输距离数据区域中的最大值，即单位时间运输距离最高的数值。

• IF(B2 = MAX($B$2:$B$6),"是","否")：这是一个条件判断函数。如果某个发货地 - 目的地组合的单位时间运输距离（B2）等于前面计算出的最大值，就表示该路线是运输效率最高的，返回 “是”；否则返回 “否”。

（三）统计不同发货地的货物运输量（SUMIF、SUM、OFFSET、MATCH、ROW）

公式原理 ：

• SUMIF($B$2:$B$6,"北京",$E$2:$E$6)：SUMIF函数用于对满足条件（发货地为 “北京”）的数据进行求和，这里是对货物重量进行求和，得到北京发货地的货物运输量。同样的方法用于计算广州和深圳等其他发货地的货物运输量。

（四）分析货物重量对运输成本的影响（AVERAGEIF、SUM、IF、ISNUMBER、ROW）

公式原理 ：

• AVERAGEIF($E$2:$E$6,">=0",$E$2:$E$6,"<=50",$I$2:$I$6)：AVERAGEIF函数用于计算满足指定条件（货物重量在 0 - 50 千克之间）的单元格区域（运输成本区域$II）的平均值。它会在$EE这个货物重量区域中查找满足条件的货物重量，每当找到时，就将对应的列中的运输成本纳入求平均值的计算范围。同样的原理适用于 “51 - 100” 和 “101 - 150” 等其他货物重量区间平均运输成本的计算。

作为物流公司的运营主管，通过运用这些 Excel 函数嵌套对运输成本和效率数据进行分析，可以深入了解不同运输方式的成本、运输效率最高的路线、不同发货地的运输量以及货物重量与运输成本的关系等信息，从而优化运输路线、调整运输方式、合理安排资源以降低运营成本。