《Excel操作》-第12章EXCEL在物流管理中的应用

本章主要介绍Excel在物流管理方面的应用，包括库存结构分析、KPI统计表应用、仓库管理等。本章简介本章重点重点、难点动态库存结构图表的制作；统计多条件查询结果。本章难点动态库存结构图表的制作；统计多条件查询结果。12.1在库存结构分析中的应用12.2在KPI统计表中的应用12.3在仓库管理中的应用本章目录12.1.2应用组合框制作动态图表12.1.1制作库存结构分析图表12.1在库存结构分析中的应用12.1.1制作库存结构分析图表例12-1制作库存结构分析图表可以使用分离型饼图，分离型饼图可以更好地显示每个值占总数的百分比，而且还可以同时强调各个值。1选择A2:F6单元格区域，单击工具栏中的图表向导按钮 ，打开“图表向导”对话框。2根据向导的提示，制作如图12-1所示的“分离型三维饼图”。图11-2 取消“网格线”复选框12.1.1制作库存结构分析图表例12-1制作库存结构分析图表可以使用分离型饼图，分离型饼图可以更好地显示每个值占总数的百分比，而且还可以同时强调各个值。图12-2制作分离型三维饼图3设置图表标题的字体为“微软雅黑”，字号为“16”，颜色为“蓝色”；“数据标志”的字体为“宋体”，字号为“12”，字形为“加粗”；设置“颜色1”为“浅绿”、“颜色2”为“浅青绿”的双色渐变斜上底纹样式；为图表添加“阴影”、“圆角”边框，得到如图12-2所示的最终效果。12.1.2应用组合框制作动态图表从例12-1制作好的图表可以看到，图表只显示了2009年的库存结构，2010-2012年的库存结构并未显示，这时就要应用组合框来制作动态的图表，以便显示后面三年的库存结构。12.1.2应用组合框制作动态图表例12-2在例12-1制作好的图表基础上，制作一个如图12-3所示的动态图表，在该动态图表中，通过选择不同的年份，可以得到相应的库存结构饼图。具体操作步骤如下。1单击“视图”→“工具栏”菜单命令，在出现的下拉菜单中选择“窗体”选项，打开“窗体”工具栏。图12-3动态饼图12.1.2应用组合框制作动态图表例12-2在例12-1制作好的图表基础上，制作一个如图12-3所示的动态图表，在该动态图表中，通过选择不同的年份，可以得到相应的库存结构饼图。具体操作步骤如下。图12-4绘制组合框2单击“窗体”工具栏中的“组合框”按钮，在图表区中的空白位置拖动鼠标，画出一个大小合适的组合框后，释放鼠标。如图12-4所示。12.1.2应用组合框制作动态图表例12-2在例12-1制作好的图表基础上，制作一个如图12-3所示的动态图表，在该动态图表中，通过选择不同的年份，可以得到相应的库存结构饼图。具体操作步骤如下。图12-5设置控件格式3双击组合框，打开“设置控件格式”对话框，选择“控制”选项卡，在“数据源区域”文本框中选择A3:A6单元格区域，在“单元格链接”文本框中选择A8单元格，在“下拉显示项数”文本框中输入“4”，如图12-5所示，单击“确定”按钮。12.1.2应用组合框制作动态图表例12-2在例12-1制作好的图表基础上，制作一个如图12-3所示的动态图表，在该动态图表中，通过选择不同的年份，可以得到相应的库存结构饼图。具体操作步骤如下。图12-6单击“粘贴链接”按钮4复制B2:F2单元格中的数据，右击B7单元格，在弹出的快捷菜单中选择“选择性粘贴”命令，打开“选择性粘贴”对话框，单击对话框中的“粘贴链接”按钮，如图12-6所示。12.1.2应用组合框制作动态图表例12-2在例12-1制作好的图表基础上，制作一个如图12-3所示的动态图表，在该动态图表中，通过选择不同的年份，可以得到相应的库存结构饼图。具体操作步骤如下。图12-7设置辅助列后的效果5选中B8单元格，输入公式“=INDEX(B3:B6,A8)”，按Enter键确定。这样就完成了B列的辅助列设置。同样，在C8单元格中，输入公式“=INDEX(C3:C6,A8)”，按Enter键确定，完成了C列的辅助列设置。采用同样的方法完成D列、E列、F列的辅助列设置。设置完辅助列后的效果如图12-7所示。12.1.2应用组合框制作动态图表公式解析：公式“=INDEX(B3:B6,A8)”的作用是返回B3:B6单元格区域中以A8中的数字为行号的单元格中的值。如当A8中的数字为1时，返回B3:B6单元格区域中第一行的值，当A8中的数字为2时，返回B3:B6单元格区域中第二行的值。在前面“设置控件格式”对话框中，已经设置“单元格链接”为A8单元格，所以在组合框中选择不同的年份时，A8单元格中会出现响应的序号，如在组合框中选择2009年时，A8单元格中的序号为1，选择2010年时，A8单元格中的序号为2，以此类推。这样，选择组合框中的不同年份，在B8中就可以得到相应年份的B列中的数据。12.1.2应用组合框制作动态图表例12-2在例12-1制作好的图表基础上，制作一个如图12-3所示的动态图表，在该动态图表中，通过选择不同的年份，可以得到相应的库存结构饼图。具体操作步骤如下。图12-8“源数据”对话框6右击图表区，在弹出的快捷菜单中选择“源数据”选项，打开“源数据”对话框，选择B7:F8单元格区域作为数据区域，如图12-8所示。单击“确定”按钮，完成数据区域链接动态数据单元格的操作。12.1.2应用组合框制作动态图表例12-2在例12-1制作好的图表基础上，制作一个如图12-3所示的动态图表，在该动态图表中，通过选择不同的年份，可以得到相应的库存结构饼图。具体操作步骤如下。图12-92010年的商品库存结构饼图7图表制作完成后，在组合框中选择不同的年份，就可以得到对应年份的库存结构饼图，如点击组合框中的下拉三角形选择“2010年”后，得到如图12-9所示的商品库存结构饼图。12.2在KPI统计表中的应用12.2.1在KPI统计表中，求满足多种条件的值在物流公司的日常管理中，经常需要制作KPI（关键业绩指标）统计表，并在统计表中求出满足多种条件的值。比如，统计某个品牌在某个月，从某个出发地发往某个目的地，包装为“大箱”的货物数量。这类问题用Excel就可以很好地解决，下面通过具体的案例进行讲解。12.2.1在KPI统计表中，求满足多种条件的值例12-3在“速翔公司KPI统计表.xls”工作簿中，“订单详情表”工作表记录了该公司2013年上半年的订单详细情况，现要求在“统计结果”工作表中，输入查询品牌、查询月份、出发地、到达地、运输的方式和查询包装后，在“查询结果”单元格中显示符合条件的结果。例如，要统计“哥特”品牌6月份由北海发到柳州的中转仓货物中用了多少中箱的方法如下。在“订单详情表”工作表的L2单元格中，输入“月份”文字，作为L列的列标题。112.2.1在KPI统计表中，求满足多种条件的值例12-3在“速翔公司KPI统计表.xls”工作簿中，“订单详情表”工作表记录了该公司2013年上半年的订单详细情况，现要求在“统计结果”工作表中，输入查询品牌、查询月份、出发地、到达地、运输的方式和查询包装后，在“查询结果”单元格中显示符合条件的结果。例如，要统计“哥特”品牌6月份由北海发到柳州的中转仓货物中用了多少中箱的方法如下。图12-10提取所有订单的月份2在L3单元格中，输入公式“=MONTH(C3)”，按Enter键确认，从订单日期中提取对应的月份。拖动L3单元格右下角的填充柄，复制公式到L20单元格，得到所有订单的月份，如图12-10所示。12.2.1在KPI统计表中，求满足多种条件的值例12-3在“速翔公司KPI统计表.xls”工作簿中，“订单详情表”工作表记录了该公司2013年上半年的订单详细情况，现要求在“统计结果”工作表中，输入查询品牌、查询月份、出发地、到达地、运输的方式和查询包装后，在“查询结果”单元格中显示符合条件的结果。例如，要统计“哥特”品牌6月份由北海发到柳州的中转仓货物中用了多少中箱的方法如下。3切换到“统计结果”工作表，分别在B2、B3、B4、B5、B6和B7单元格中输入“哥特”、“6”、“北海”、“柳州”、“中转仓来货”和“中箱”作为查询的条件。12.2.1在KPI统计表中，求满足多种条件的值例12-3在“速翔公司KPI统计表.xls”工作簿中，“订单详情表”工作表记录了该公司2013年上半年的订单详细情况，现要求在“统计结果”工作表中，输入查询品牌、查询月份、出发地、到达地、运输的方式和查询包装后，在“查询结果”单元格中显示符合条件的结果。例如，要统计“哥特”品牌6月份由北海发到柳州的中转仓货物中用了多少中箱的方法如下。图12-11统计符合多种条件的结果4在B9单元格中输入公式“=SUMPRODUCT((订单详情表!B3:B20=B2)*(订单详情表!L3:L20=B3)*(订单详情表!D3:D20=B4)*(订单详情表!E3:E20=B5)*(订单详情表!F3:F20=B6)*(订单详情表!G3:G20=B7)*(订单详情表!J3:J20))”，按Enter键确认，得到符合条件的结果，如图12-11所示。12.2.1在KPI统计表中，求满足多种条件的值公式解析：在使用SUMPRODUCT函数时，可以直接输入需要满足的条件和计算范围，直接求出满足条件的值。在公式中，每个具备的条件要加以()，每个条件用*连接，最后()内是计算求和的单元格区域。12.3.2库存货品的先进先出管理12.3.1判断是否接货12.3在仓库管理中的应用12.3.1判断是否接货供应商生产完商品后，会送到指定的物流中心进行验收发货上市。但是如果离上市日期还很久的话，在物流中心就会造成大量的货品积压，从而使仓库爆仓。所以，物流中心需要限定供应商送货不得过于提前，不得超出规定的天数。下面举例讲解，在每个供应商送货到物流中心时，怎么利用Excel来判定到达的货品是否可以接收。12.3.1判断是否接货例12-4广西海吉星物流中心为南宁最大的水果批发与仓储中心，该物流中心规定水果在上市日期前15天以内到货的可以签收入库，否则提前送货被拒收。如果到货的第二天是节假日，则加上放假天数来判断。在“海吉星水果入库接收表.xls”工作簿中，“水果信息”工作表记录了最近上市的各种水果信息，包括水果类别、品种、品名代码和上市日期等信息，如图12-12所示。现要求在“查询结果”工作表中，把送货单上的品种或品名代码录入表格，并选择相应的“水果类别”，确认后，即可获得是否接货的查询结果，如逢节假日，要录入放假天数。图12-12“水果信息”工作表12.3.1判断是否接货在“查询结果”工作表中，选择B6单元格，单击“数据”→“有效性”，打开“数据有效性”对话框。单击“设置”选项卡，在“允许”下拉列表中选择“序列”选项；在“来源”输入框中输入水果类别序列“苹果，葡萄，哈密瓜，香蕉，芒果，提子，雪梨，西瓜”，如图12-13所示，单击“确定”按钮。选择B8单元格，单击“数据”→“有效性”，打开“数据有效性”对话框。单击“输入信息”选项卡，在“标题”文本框中输入“输入要查询的品种”，在“输入信息”文本框中输入“输入与‘水果信息’工作表一致的品种”，如图12-14所示，单击“确定”按钮。12图12-13输入“水果类别”序列图12-14设置B8单元格的输入提示信息12.3.1判断是否接货采用同样的方法，设置B10单元格的输入信息“标题”为“输入要查询的品名代码”，“输入信息”为“输入与‘水果信息’工作表一致的品名代码”。选择D2单元格，输入公式“=TODAY()”，按Enter键确认，得到当天的日期。34选择D8单元格，输入公式“=INDEX(水果信息!D:D,MATCH(查询结果!B8,水果信息!C:C,0))”，按Enter键确认，得到与B8单元格中输入品种相对应的水果的品名代码。512.3.1判断是否接货公式解析：在公式“=INDEX(水果信息!D:D,MATCH(查询结果!B8,水果信息!C:C,0))”中，先用MATCH函数返回B8单元格中的水果品种在“水果信息”工作表中的位置，接着用INDEX函数得到“水果信息”工作表中与该水果品种相对应的品名代码。12.3.1判断是否接货选择E8单元格，输入公式“=INDEX(水果信息!B:B,MATCH(查询结果!B8,水果信息!C:C,0))”，按Enter键确认，得到与B8单元格中输入品种相对应的水果类别。选择F8单元格，输入公式“=VLOOKUP(B8,水果信息!C:E,3,0)”，按Enter键确认，得到与B8单元格中输入品种相对应的水果的上市日期。467公式解析：这里用VLOOKUP函数查找在“水果信息”工作表中，与B8单元格中输入的品种相对应的水果的上市日期。12.3.1判断是否接货选择G8单元格，输入公式“=F8-D2”，按Enter键确认，得到与B8单元格中输入品种相对应的水果距离上市的天数。分别在D10、E10、F10和G10单元格中输入公式“=INDEX(水果信息!C:C,MATCH(查询结果!B10,水果信息!D:D,0))”、“=INDEX(水果信息!B:B,MATCH(查询结果!B10,水果信息!D:D,0))”、“=VLOOKUP(B10,水果信息!D:E,2,0)”和“=F10-D2”，得到与输入的品名代码相对应的水果的品种、水果类别、上市日期和距离上市的天数。89选择B1单元格，输入公式“=IF(LEFT(B6,1)=LEFT(E8,1),IF(ISERROR(IF(G8<=(15+B4),"可收","拒收"))，"",IF(G8<=(15+B4),"可收","拒收")),"")”，按Enter键确认，得到是否接收在B8单元格中输入品种水果的结果。10选择 B2 单元格，输入公式“=IF(LEFT(B6,1)=LEFT(E10,1),IF(ISERROR(IF(G10<=(15+B4),"可收","拒收"))，""，IF(G10<=(15+B4)，"可收"，"拒收")),"")”，按Enter键确认，得到是否接收在B10单元格中输入品名代码水果的结果。1112.3.1判断是否接货1在B8和B10单元格中，分别输入“黑美人26”和“XL44-28”，得到如图12-15所示的结果。验证结果如下。图12-15输入“品种”和“品名代码”后的结果12.3.1判断是否接货2在B8单元格中输入“仙人蕉40”，在B6单元格中选择“香蕉”，在B4单元格中输入0后，在B1单元格中可以得到“拒收”的结果，如图12-16所示；如果在B4单元格中，把放假天数改为1，则B1单元格中可以得到“可收”的结果，如图12-17所示。验证结果如下。图12-16放假天数为0时的结果图12-17放假天数为1时的结果12.3.1判断是否接货3验证结果如下。在B6单元格中选择“雪梨”，在B4单元格中输入3后，在B2单元格中可以得到“可收”的结果，如图12-18所示。图12-18根据“品名代码”查询得到的结果12.3.2库存货品的先进先出管理物流公司在配发库存货品时，可以根据货品入库时间的先后，将放在不同库位，不同入库日期的相同货品，实现先进先出。也就是说，在配发货品时，要先将先入库的货品配发出去。如果前一批不够配发，不足的数量再从下一批配发，把后到的货品先留存下来。12.3.2库存货品的先进先出管理例12-5在“维奇物流公司先进先出库存账.xls”工作簿中，“发货信息”工作表为该公司最近要发货的货物信息，包括发货地区、发货物品代码、发货件数等，如图12-19所示。“先进先出库存账”工作表为该公司的库存数据，包括入库日期、物品代码、库位等信息，如图12-20所示。该公司仓库中的货品按照入库日期分别存放，出库时需要将先入库的货品先发出，前一批发完才可以发下一批。现要求根据“发货信息”工作表中发货的数量，在“先进先出库存账”工作表中统计发货件数和剩余的件数。图12-19“发货信息”工作表图12-19“发货信息”工作表12.3.2库存货品的先进先出管理图12