Excel商务数据处理与分析 第10章 订单与库存分析.pdf

第10章订单与库存分析Excel商务数据处理与分析10.1客户订单记录统计表10.2产品出入库情况分析10.3 产品库龄分析10.4 提高与技巧回10.1.1按产品名称汇总订单A计算市场占有率的方法是，用一定时期内某产品的销售数量，除以该产品市场销售量总 和。下面将在客户订单记录定位表工作簿中，分别计算自家产品和竞争产品的市场占有 率，其具体操作如下。1.选择移动或复制命令2.复制工作表步入 RB布局以数客市那H 开5il*杼色比能 O C X 宗体 11 N,.停 货否11a sB/y，A*A,手塞至至.缉.。J 田，8支*厚彩.联以 剪贴板 fi|字体 GI 对开方式 G|数字 A-2,垂兀恪、辱H17 A=G17LF17AB CD E1序号2 1订单日期 订单编号2018/3/1 152654856025433000销售人员 赵明明产品名称T恤3 2插入q；小尾J6025433001芹丽牛仔祐4 3J6025433002沈星连衣裙5 4Q(0B 除 3 I重全名(fi)1移动或复制(M)、W查.代码M Q&加工().856025433003吕珏外套6 5856025433004张丽坟T恤7 6856025433005钱悦连衣褶8 7856025433006赵明明牛仔裱9 8856025433007钱悦连衣裙10 9856025433008李丽T恤11 10856025433009李丽连衣裙J4立副本Sh”t lSheet 2Sheet 3 够至最后)A=G17-F17移动支费制工作表口广xD E将选定工作表移至工作簿9：客户订单记录统计表Xl*x 下列选定工作表之前9)TU选择单击确定 r 取消乜。*。7 37儡售人员I产品名称)00 版明明 卜恤I.l 李丽 曲裤)02)034 5 6 7)0)0)0进沈星马珏分钱.赵.蔡rr丽丽悦连衣褶T恤.牛仔裤连衣褶河套侪套2g rR/*/a 1k?的 4管6025433013 230 i 156025433014：I：.二 季-(李丽 卫仔裤 张丽坟.外套 一上Afifl nn _Luu r i*-画 口同 wo%-0回10.1.1按产品名称汇总订单3.重命名工作表4.编辑工作表名称后排序H17 A=G17-F1I.3 国6 a 4.00.00。y瓢5 T1 对齐方式 心8 g|A B D E1 用号 订单日期 订单编号 销售人员产品名称进伤22018/3/1152654856025433000赵明明张丽坟3_21856025433001李丽4J1856025433002沈星5Z2Z（n）856025433003马珏65|里合名.856025433004张丽垃76移动或复制（m）T856025433005钱悦87|_856025433006赵明明98)66025433007钱悦1091A?冼拯 1025433008李丽1110|.025433009李丽12,工作表M筌1R色CD 856025433010钱悦13L右击1MKH)856025433011-z-钱悦14856025433012张明坟151V856025433013李丽85602543301416选定全部工作表付.Sheet】牛仔般连衣裙外套T恤连衣褶牛仔裤连衣裙T恤连衣裙 不套 T恤 外套牛仔禄 外套二|L 4伙耿 全部成 外部836 ,连I 3.单击J.六财 生 建序防近 JSSJJI 分析E2，G“T憧AB C DE1 2序号订单日期 订单编号 销售人员产品互称1选12018/3/1 52654856025433000 赵明明nL_13422018/3/1,52654856025433001 李丽曲德2018/3/1 52654856025433002 流星连氏褚5 42018/3/1 1152654856025433003 马珏,今一 选6 52018/3/352654856025433004 张丽：27 62018/3/3*152654856025433005 钱悦、891072018/3/3*152654856025433006 赵明明牛仔裤82018/3/352654856025433007 钱悦连衣褶92018/3/6152654856025433008 李丽T恤11 I 102018/3/6H52654856025433009 库丽连衣裙12 1)2018/3/6 152654856025433010 钱悦 外套H rn7TT 1018/3/6152654856025433011 钱悦I恤眄八八18/3/852654856025433012.张丽坟 卜套15F2018/3/8,52654856025433013|李丽牛仔裤16 齿2018/3/85 52654856025433014 张丽坟外套di m汇思丁单 Sheet l Sheet 2 Sheet 3 0 0 Au.r1回10.1.1按产品名称汇总订单A5.单击分类汇总按钮6.设置分类汇总刑意入 立面布局公式 S3?亩更椀照开虹具特色功爰公6 o 3 后外豳获取 全部瞄新-zi撵序外部阴6 .7的于：”墨3*篇送6里甘匚用 分列初除止 斓t防，高出 重复项静，排序的法 SQ&TJI通|JSS分t%敏的分析E2 卜套嘲镯E器创建明取:有组合分类汇总C1订单编号D 销售人员E 产品名称2 52654856025433003马珏3 52654856025433010钱悦45678952654856025433012张丽坟外套158/7052654856025433014张丽坟卜外套158单击15052654856025433016沈星外套158乙上 U-f 60*152654856025433001李丽牛仔裙8815870(152654856025433006赵明明牛仔祐881587052654856025433013李丽牛仔裤8816880回10.1.1按产品名称汇总订单A7.按产品名称进行分类汇总效果”2|3|CDEFGHIJK：1订单编号销售人员产品名称进价售价利涧邮费付款时间客户IL 252654856025433003马珏外套1582085002018/3/1ieihs3152654856025433010钱悦外套1582186。02018/3/7Qetilt4152654856025433012张丽坟外套1582287002018/3/8kdqe2552654856025433014张丽坟外套158208i15002018/3/8adosf6152654856025433016沈星外套1582186002018/3/8adafaG-7外套汇总1,080852654856025433001李丽牛仔裤881587002018/3/2wie02952654856025433006赵明明牛仔裤881587002018/3/4adosf10152654856025433013李丽军存裤881688002018/3/9kadiw211152654856025433015赵明明牛仔裤881587002018/3/10adksag1252654856025433017沈星:牛仔裤881688002018/3/8qeieqi-13牛仔裤汇总810_J1452654856025433002沈星连衣褶108188*8002018/3/1skdil S1552654856025433005钱悦连衣裙108210102*02018/3/41-kadiwZ1652654856025433007钱悦连衣裙*108*1807217152654856025433009李丽连衣裙10816860-汇总结果-18连衣裙汇，746回10.1.2使用函数统计销售总额5面将利用SUMIF函数，分别计算6名销售人员的实际销售额，其具体操作如下。1.重命名工作表2.选择插入的函数Fyf 开始 插入 页面布局 公式数据窗阅 视图 开发工具 特色功能 4 Q”市&二8 获取外据，全部扁新：插入函数搜索函数6):12345678910疆数据分析3.选择 应修实际集SUMISUMIFSBMIF(rang e,cri t eri a,L 对耨足条件的单元格求和SUMIFS SUMPRODUCT SUMSQ SUMX2MY2 SUMX2PY2或选择类别()：数学与三角函数 选择函数(B)：姓名2.选择1.重命名)有关i 7函留的都助4.请输入一条简短说明来描述您想做什么，“转到”L转到应(5.单击)确定取消，用 汇总订单 Sheet 销售额统计，Sheet 5 ni u=E4567811选择 M v Sh eet l销售额统计动态图表JO 110.002018;135.00自动碉整列宽l s67.50201&144.502。4.502090.00201&156.00106.001060.002018；1624.003024.006811,632.002018/1752.002350.00653,250.002018/1868.001068.0010680.002018;1954.002555.00884,840.002018；2065.001265.009015,850.002018；2155.001045.0010450.002018；2248.002252.00502.600.002018;H 4 h Sheet l Sheet 2.Sheet 3 均回10.2.1计算产品的出入库金额A5.输入嵌套函数6.复制公式|X t 产晶出入喇(J况分析.xkx Microsoft Excel箧 描人-广也*咯三-单元格SUJIIF X，力=IF(I3=D3,13*E3,D3*E3+(I3-D3)*C3)|GH IK1:登记2I入库金额入库时间出库数量出库金锁出库时间蛤360.002018/8/10123(I3-D3)*C3)|2018/8/20 李436.002018/8/1030 92018/8/20 李545.002018/8/10302018/8/20 李6175.002018/8/102018/8/20 李7247.502018/8/101.匹件j2018/8/20 驯J3 蜀=IF(13=D3,13*E3,D3*E3+(13-D3)*C3)GHIJK71247.502018/8/1030157.502018/8/20 李月8172.002018/8/106072.002018/8/20 李B975.002018/8/102030.002018/8/20 李月10640.002018/8/1045576.002018/8/20 驷11 24i.2018/8/1056168.002018/8/20 李月12110.002018/8/1032165.002018/8/20 驷1367.502018/8/102092.502018/8/26 李月1490.001881.002018/8/27 初4*VX O/复制1560.002018/.72.002018/8/28 赵彳1611,632.00551,320.002018/8/29 赵彳O/A V173,250.002018/8/106013,074.002018/8/30 赵；18680.002018/8/105340.002018/8/31 赵:19840.002018/8/10804,345.002018/9/1 赵彳205,850.002018/8/10684,420.002018/9/2 赵?21450.002018/8/1010450.002018/9/3 赵 1222,600.002018/8/105012.488.002018/9/4 赵刃M 4 Sheet l ySheet 2Sheet 3。提醒在Excel中输入公式时，像小括号()、中括号口是直接在英文状态下输入；而对于大括号,若不是 数组公式则直接在英文状态下输入，如果是数组公式，则在输入完公式后，按Ct rl+S川ft+Ent eF组合 键自动输入。回10.2.2按多条件方式对数据进行排序在处理数据的时候，有时需要对满足多个条件的数据进行排序，这样数据结构会更加清 晰明了。下面将在Sheet l工作表中，利用排序对话框进行多条件排序设置，其具体 操作如下。2.设置主要关键字1.单击排序按钮回10.2.2按多条件方式对数据进行排序自定义序列L自定义序列&）：奶奶 水饮水TX 机牛妪汁打酸泉迺有纯酸果苏疏矿啤（1.输入）3.输入自定义排序序列f才非呼结果人有机奶A I B C D|E F G H I产品出入库登记5.查看排序结果2产品名称规格期初单价期初库存数量入库单价入库数量入库金额入库时间出库数量31有机奶50ml6.00106.001060.002018/8/10124纯牛奶250ml5.00154.501567.502018/8/10205纯牛奶一箱55.001045.0010450.002018/8/10106酸奶一箱48.002252.00502,600.002018/8/10507酸奶200ml4.50204.502090.002018/8/10188果汁1L12.803012.8050640.002018/8/10459果汁250ml4.50204.501045.002018/8/103010果汁一箱68.001068.0010680.002018/8/10511苏打水一箱54.002555.00884,840.002018/8/108012苏打水500ml3.50303.5050175.002018/8/106013苏打水350ml3.00453.0080240.002018/8/105614碳酸饮料一箱52.002350.00653,250.002018/8/106015碳酸饮料500ml2.50503.001236.002018/8/1030回10.2.3计算安全库存、最大库存和最小库存量安全库存量是为了防止临时用量增加或供应商交货误期的不确定因素而准备的缓冲库存。安全库存的计算，是按一周的库存量来核算；最大库存是按月度最大用量和安全库存之和来 核算；最小库存是按两周的安全库存量来核算。下面将在Sheet 2工作表中，利用TRUNC 函数和公式来计算店铺的库存情况，其具体操作如下。1.输入公式2.复制公式SUMIF X 4=TRUNC(L3/4)+l)JKL UNO1安全库存量2月度最大用量月度最小用量J月度平均用量安全库存最大库存最小库323214122).3/4)[phone] f52521 16651076011选择后输入公式1855309110106010155101160103812501030132028146010384 4 X Sheet l Sheet 2ZSheet 3 匕J一。匚,13 A=TRUNC(L3/4)+DJ K LN 01015 5 1031160 10 38101250 10 3081320 2 831460 10_ 381015220 台割、上311630 艮市J 淖417170 20 95241860 35 48121930 10 2052065 40 53142180 20 47122230 4 154231 4 w Sheet l Sheet 2.Sheet 3 口4t r 回10.2.3计算安全库存、最大库存和最小库存量提醒TRUNC函数用于返回处理后的数值，该函数不对指定小数前或后的部分做相应舍入选择处理，而 是统统截去。其语法结构为：TRUNC(Number,Num_dig it s),其中，Number z表示需要截尾取整 的数字，必填；Num_dig it s,用于指定取整精度的数字，其默认值为0(零)。4 4.复制公式j文件:产品出入JC博兄分析j dsx-Microsoft Excel页面布局公式教客 视83开发工H 特色功嚏 a O占 0囹联取全部就哥r 外韵即.2乂重r主产升*二 Z 琲序 篇选,一 分列删除日 分皴显示.Ai。璃去 重复项舒 排国E法 双病工AN3 八=J3+M3JK L MN10155 10 3181112136010 38 10705010-8.858202复制).231460 10攵吸 J 10701516220 20 120 3125130 2 15 43417170 20 95 24194181960 35 48 127230 10 20 535回10.2.3计算安全库存、最大库存和最小库存量5.输入公式6.复制公式SUMIF后 X aT=M3*2KLMN|0|P12月度最小用量月度平均用量安全库存最大库存最小库存期末结存数量312122.31.263|=13*2|410451287 T521032861033971164选择后输入公式18304311OO910601612610510318111038107012103085813283231410381070H M Sheet l Sheet 2/Sheet 3 D1 mi 1 bl回10.2.4设置条件格式进行预警提示使用Excel中的条件格式功能，可以在很大程度上改进电子表格的美观性和可读性，它允 许指定多个条件来确定单元格的行为，并根据设定条件自动地应用单元格的格式。下面将在 Sheet 2工作表中，将期末结存数量低于安全库存的产品显示为黄色，其具体操作如下。1.引用工作表2.引用工作表中的数据SUMIF X J=Sheet l ID3+Sh eet l!F3-Sheet l113EFGH I1产品出入库登记2入库单价入库数量入库金Si入库时间出库数量1136.001060.002018/8/1011牛44.501567.502018/8/10 T20_ 5*45.0010450.002018/8/1010652.00502,600.00引用单元格5074.502090.00188*12.80501640.0045VX 3/V94.501045.002018/8/10301068.0010680.002018/8/1051155.00884.840.002018/8/1080123.5050H75.Ou2018/8/106013*3.0080240.002018/8/105614*50.00653.250.002018/8/1060Y 4“；Sheet l _Sheet 2 Sheet 3 力r hi回10.2.4设置条件格式进行预警提示3.查看计算结果 4.复制公式-P3，A=Sheet l ID3+Sheet l!F3-Sheet l 113LMN0PQ1010318615113810702033123085816201383236691438107020281512031251623216154【复制r5017952465184812722443192053510202053147928342147129224122215434830II 23 w Sheet l Sheet 2Sheet 3为7in Sheet l Sheet 2 Sheet 3榭数据祭9)色加曲示集二新建规则回.回10.2.4设置条件格式进行预警提示7.设置单元格填充颜色8.应用条件格式后的单元格背曷色:无颜色曼案颜色:自动 却案样式9：口I 选择）填遗果（1）.|其他颜色国匚二示例2.单击）一除一）r-Wi开始 插入 负面布局 公式 数据 市阅 视图 开发工具 特色功能T1-恒用条件格式的单元格1产品安全库存量2半年度用量月度最大用量月度最小用量月度平均用量安全库存最大库存3732 232 12 122 31 2i4268 75 10 45 12 1557 25 2 10 3：6195 65 10 33 9795 60 1 16 4 8255 55 30 43 11 i9360 110 10 60 16 1：1061 15 5 10 311230 60 10 38 10 112177 50 10 30 8!1349 20 2 8 314230 60 10 38 10 115720 220 20 120 31 2!1692 30 2 15 4：17570 170 20 95 24 1,18285 60 35 48 1210.1 客户订单记录统计表10.2 产品出入库情况分析10.3产品库龄分析10.4提高与技巧回10.3.1计算产品的库龄产品库龄的计算公式为:=IF(F2=/DATEDIF(E2,TODAY()/d)+l/),该公式表示,如果出库日期(F2)为空则库龄为入库日期到今日的天数，如出库日期(F2)不为空，则库龄为空。下面将利用公式在产品库龄分析.xl sx工作簿中，对Sheet l工作表中的产品库龄进行计算，其具体操作如下。SUK-X =IF(F3=*DATEDIF(E3,TODAY 0,d*)+1/*)CDEFG i1产品库龄分析表2单位入库数量入库日期出库日期3个2,0002018/1/10=IF(F3=*DATEDIF(E3,4个3202017/1/82017/4/185个2002017/5/96个3502018/3/27个1,0622017/5/92017/10f8个1,2302017/4/9心千，口彳刖J9个1202018/5/610个2602017/4/511个3002018/9/812个1582017/8/913个8652018/12/714个3252018/7/815个7892016/12/72017/8/816个1,0242017/8/9.一_XAA.hai*y/i a/ji 14刖入公式4 4 1 Sbaa 1 讣.a”-9 1j n*L JG3A=IF(F3=,DATEDIF(E3,TODAY 口，d)+1,CDEFG1产品库龄分析表2单位入库数量入库日期一_ 13个2,000_2018/1/IC|盯舁结乐|3794个3202017/1/82017/4/185个2002017/5/96个3502018/3/27个1,0622017/5/92017/10/18个1,2302017/4/99个1202018/5/610个2602017/4/511个3002018/9/812个1582017/8/913个8652018/12/714个3252018/7/815个7892016/12/72017/8/816个1,0242017/8/9 1 /_1_1 1-1八1 Shee1 AAt l Sheet 2 Sheen/si ry Zt a/jt 3、口2.真有结果回10.3.1计算产品的库龄3.复制公式G3:A=IF(F3=,DATEDIF(E3,TODAY。Jd)+1,)_G3(-G=IF(F3=*DATEDIF(E3,TODAY(),*d*)+l,*)CD f 1 廉_c_C D E FG1产品库龄分析表7 1个1,0622017/5/92017/10/1a单位入库数量入库日期出库日期库龄8个1,2302017/4/96553 1个2,0002018/1/10_37219个1202018/5/62634个3202017/1/82017/4/1810-i-2602017/4/56595个2002017/5/911个3002018/9/81386个3502018/3/2_5 112个1582017/角制-5337个1,0622017/5/92017/10/113 1个8652018/1攵中J J488个1,2302017/4/9_114个3252018/7/82009个1202018/5/615个7892016/12/72017/8/810个2602017/4/5_16个1,0242017/8/953311个3002018/9/8_ Ii1002017/10/447712个1582017/8/9ZSL个1,5002017/8/953313个8652018/12/720r1,1162018/4/829114个3252018/7/8个1,0002017/5/102018/1/115个7892016/12/72017/8/8121 1个3002018/1/1237716个1,0242017/8/922；41*23 _M-r M|Sheet 1,Sheet 2,Sheet 3、勾 的 Sheet l,Sheet 2 Shet 3、匕 j 4 川_向例;冢为法定区力“wa充月例：awarn iWmea 100%tts1 平均便 40 计E19 求id 6039 1 由JDS 100%Q-回10.3.2突出显示库龄长的产品对于库龄较长的产品，可以通过单元格的不同填充色来显示，单元格有底纹颜色表示产 品库龄较大，反之，就越小。下面将在产品库龄分析.xl sx工作簿中，对库龄超过1年的产 品进行突出显示，其具体操作如下。15贴板 Q 字体对开万式WxtG3-*，nfnng-i-2.二性击Lc D1产品库龄分：h衣总为常规L选择条件格式的类型 =7元格 编餐差套用衰悟格式好平均11；402.6 计数：19”1里位入库数量 _X总口咖出库修3|个2,0。1 3.选择 14个4s r/17o5卜2002017/5/96个3502018/3/27 1个1,0622017/5/98 个1,2302017/4/99个1202018/5/610个2602017/4/511个3002018/9/812:个1582017/8/913个8652018/12/714个3252018/7/815个7892016/12/716 1个1,024M M Shee1.AAt l i Sheet 2 Sheet 3。丁 1，选择g%项目选取规则（D笑出显示单元格娓适中数据祭&）14.emo曲日曰新建融9匹）-.同评融g-*uBaai（B）.r r|：100%等于（-文本包含.发生日喟品|重复值9）_具他碘（M）1g10.1 客户订单记录统计表10.2 产品出入库情况分析10.3产品库龄分析10.4提高与技巧回10.4.1突出显示某个数据记录有时需要强调图表中的某一 条数据记录，此时可单独为其设 置不同的格式。其方法为：选择 图表中的数据系列，此时所有数 据系列将全部被选中。再次单击 需要设置的数据系列，此时只有 该数据系列处于选中状态，然后 利用功能选项卡设置需要的格式 即可，如图所示。突出显示某个数据记录回10.4.2自定义数据的图标集条件格式中的图标集样式都 是呈组出现的，当需要自行定义 某个范围使用的图标时，首先选 择需使用图标集的单元格区域，然后单击条件格式，按钮，在 打开的下拉列表中选择图标集 选项，在打开的子列表中选择 其他规则选项。打开新建 格式规则对话框，在下方设置 范围、值的类型以及对应的图标 即可，如图所示。图标建）值9当值是 Mp50类型1数字-Jfl当 250 且 J 7 2001数字a H当 200 自定义的图标集客户代码客户性医意向购买量实际购买量转化率YC2012001新客户399 27368%YC2012002VIP客户54623443%YC2012003老客户700 28240%YC2012004新客户357 28881%YC2012005老客户385418348%YC2012006新客户644)21333%YC2012007VIP客户455 28563%自定义数据的图标集感谢聆听！学习进步!Excel商务数据处理与分析