实训项目：差旅费统计与分析

财务部助理小王向主管汇报 2018 年度公司差旅报销情况。本节将综合运用 WEEKDAY、LEFT、IF、VLOOKUP 以及 SUMIFS 多条件求和函数，完成一份极其硬核的企业级数据统计分析报告。

实训内容与业务需求

  • 1 在“费用报销管理”工作表的“日期”列中，将日期自定义格式设置为显示星期几。例如：2018年1月20日星期六。
  • 2 判定是否加班：如果“日期”列为星期六或星期日，则在“是否加班”列显示“是”，否则显示“否”。
  • 3 在“地区”列中统计出每个活动地点所在的省份或直辖市（提取前三个字，例如“北京市”、“浙江省”）。
  • 4 依据“费用类别编号”生成“费用类别”的具体内容（对照关系参考定义的“表4”）。
  • 5 统计 2018 年第二季度（4月1日至6月30日）发生在北京市的差旅费总额。
  • 6 统计 2018 年员工“钱顺卓”报销的“火车票”费用总额。
  • 7 计算出“飞机票”费用占所有总报销费用的比例，并保留2位小数的百分比。
  • 8 统计 2018 年发生在周末（是否加班="是"）的“通信补助”总金额。

核心知识分析

WEEKDAY 与星期判断

WEEKDAY(日期, 2) 中第二个参数“2”表示：星期一作为第1天，一直到星期日计为第7天。因此，只要判断该函数返回值 >=6，就可以准确判定是否为周末。

=IF(WEEKDAY(A3,2)>=6, "是", "否")

数据源与多条件求和 (SUMIFS)

多条件求和函数 SUMIFS(求和区, 条件区1, 条件1,条件区2, 条件2...)。当包含日期区间时，需要在双引号内使用比较运算符，如：">=2018-04-01"。

=SUMIFS(G3:G401, A3:A401, ">=2018-04-01", ...)

零散小知识汇总

  • 自定义星期格式代码：aaaa
  • 文本提取函数：=LEFT(C3, 3) 提取前三个字。
  • 占比的计算通常是 单项求和 / 整体求和：
    =SUMIF(...) / SUM(...)

标准操作流程（完成步骤）

  1. 在素材文件夹下打开 Excel.xlsx 文件，打开“费用报销管理”工作表，选定 A3:A401。在“开始”选项卡下的“数字”功能组中，单击右下角的展开按钮，弹出“设置单元格格式”对话框。在“分类”选择“自定义”，“类型”文本框中输入：
    yyyy"年"m"月"d"日"aaaa
    最后单击“确定”按钮。
  2. 选定 H3 单元格，在编辑栏输入公式：
    =IF(WEEKDAY(A3,2)>=6,"是","否")
    按“Enter”键确定，然后拖动填充柄向下填充至单元格 H401。
  3. 选定 D3 单元格，在编辑栏输入公式：
    =LEFT(C3,3)
    按 Enter 键确定，然后向下填充至单元格 D401。
  4. 选定 F3 单元格，在编辑栏输入公式：
    =VLOOKUP(E3,表4,2,0)
    按“Enter”键确定，然后向下填充至单元格 F401。
  5. 打开工作表“差旅成本分析报告”，选定 B3 单元格，在编辑栏输入公式：
    =SUMIFS(费用报销管理!G3:G401, 费用报销管理!A3:A401,">=2018-04-01", 费用报销管理!A3:A401,"<=2018-06-30", 费用报销管理!D3:D401,"北京市")
    按“Enter”键确定。
  6. 选定 B4 单元格，在编辑栏输入公式：
    =SUMIFS(费用报销管理!G3:G401, 费用报销管理!B3:B401,"钱顺卓", 费用报销管理!F3:F401,"火车票")
    按“Enter”键确定。
  7. 选定 B5 单元格，在编辑栏输入公式：
    =SUMIF(费用报销管理!F3:F401,"飞机票", 费用报销管理!G3:G401)/SUM(费用报销管理!G3:G401)
    按“Enter”键确定。
  8. 选定 B6 单元格，在编辑栏输入公式：
    =SUMIFS(费用报销管理!G3:G401, 费用报销管理!H3:H401,"是", 费用报销管理!F3:F401,"通信补助")
    按“Enter”键确定。
  9. 所有计算完成后，核对页面底部的最终结果集无误后，保存并关闭文档。

实战操作演示

配套素材下载
差旅成本分析报告 [最终结果集]
统计项目 统计信息
2018年第二季度发生在北京的差旅费用金额总计为： ¥ 31,420.47
2018年钱顺卓报销的火车票总计金额为： ¥ 1,871.60
2018年差旅费用金额中，飞机票占所有报销费用的比例为(保留2位小数)： 4.60%
2018年发生在周末(星期六和星期日)中的通信补助总金额为： ¥ 5,354.40

各项指标统计对应的公式参考：

B3 =SUMIFS(费用报销管理!G3:G401, 费用报销管理!A3:A401,">=2018-04-01", 费用报销管理!A3:A401,"<=2018-06-30", 费用报销管理!D3:D401,"北京市")
B4 =SUMIFS(费用报销管理!G3:G401, 费用报销管理!B3:B401,"钱顺卓", 费用报销管理!F3:F401,"火车票")
B5 =SUMIF(费用报销管理!F3:F401,"飞机票", 费用报销管理!G3:G401)/SUM(费用报销管理!G3:G401)
B6 =SUMIFS(费用报销管理!G3:G401, 费用报销管理!H3:H401,"是", 费用报销管理!F3:F401,"通信补助")