Excel商务数据处理与分析 第7章 销售费用分析.pdf

第7章销售费用分析Excel商务数据处理与分析目录7.1销售收入与成本分析7.2 各项费用明细支出对比表7.3 提高与技巧回7.1.1计算销售收入、成本、费用和税金下面将在销售费用分析表工作簿中首先利用求和函数（SU M）合计网店上半年的销 售收入、成本、费用和税金，然后通过逻辑函数（IF）和平均值函数（AVERAG E）计算销售 成本率、销售费用率和销售税金率，其具体操作如下。1.自动求和2.计算成本、费用和税金合计数毛）销售费用分析表.xlsxMicrosoft Excel,卅“页面布局一：数 阅 在圉 特色功能于X z三昙F 鱼29 A 通入函数最近使用的函数 X文本，3 加财务 e日嗷时间*国数犀今 置飞陵w 丫 定义的名称公式亩核计算 mu 取湎1IF X A=SU M（B4:B9）ABCD12绿珠小店2018年上半年销3月份销售收入销售成本销钱条用41月13564.35 4563.25456.3252月14563.215825.284025.3663月10246.873568.47286.3274月15563.472654.36521.35895月6月16685.4713685.214563.21 652.47356X1-263.98S=1.选择匚:10合计/平均|二SU M您国11SU M（fMimberl,number2L.-TJ w SheetlSheet2 Sheet3。射 Sheetl Sheet2 SheetsHI计算 1 100%Sn回7.1.1计算销售收入、成本、费用和税金3.单击插入函数按钮4.选择IF函数g 乂司 C J，捐告费尼分析表.xlsx-Microsoft Excel O1 叵文件开始 播入页面布局 公式JWS*阿 视29 特色功解A Q c月2.单击之，?号 旅里的诸登欧元格式翥入函数|一 H|U定义的名称公式亩核一言峰，的日期和时闻.卧 区白阴彰 F4 BCDEF12 L绿珠小店2018年上半年销售费用统计3销售收入销售成本销售菱用销害税金销售成本率413564354563.25456.32178.22514563.215825.284025.36285.63T610246.873568.47286.32142.84-1715563.47265436521.35302.65 1.选择）8 16685.474563.21652.47334.27913685.213562.31263.98184.261084308.5824736.886205.801427.8811丁 X Sheetl 定义的名称公式审核计尊rI 0 W欧元格式取消阻髭X A=AVERAGE(F4:F9)IFDEGFH区决方案12*珠小店2018年上半年销售费用统计单位：五3销售成本销售费用销售税金销售成本率销售娄用率销售税金与44563.25456.32178.2233.64%3.36%1.31%55825.28402536285.6340.00%27.64%1.96%63568.47286.32142.8434.82%2.79%1.39%72654.36521.35302.6517.06%3.35%1.94%84563.21652.47334.2727.35%3.91%2.00%q3羽41484.26J6JD3%,VERAGE(01.93%1.35%10T按Enter键_11AVERAGECnumberl,；number2,.)回7.1.2回归分析销售收入与成本回归分析是确定两种或两种以上变量之间相互依赖的定量关系的一种统计分析方法。下面 将通过LIN EST X O N C A TEN A TE TEST和C O RREL函数对销售收入和成 本进行回归分析，判断这两者之间是否存在定量关系，其具体操作如下。1.输入文本内容2.输入公式财务的日期和时间 由数库*A13 A国匪1数ABcD41月13564.354563.254563252月14563.215825.284025.3663月10246.873568.47286.3274月15563.47265436521.3585月16685.474563.21652.4796月13685.21356231263.9810合平均84308.5824736.886205.80111213回归函数A 1选择并输入14 M Sheetl(Sheet2 Sheet3 J D _ _ _ _ _Illi推入函数则 ECWflRBSX a 乂4 L0 伪财务，6日吸时间，修函数由定义的名称公式市祓计菖视硼影F解天方案.输,IF-X A=LINEST(C4:C9,B4:B9)2ABCUE52月14563.215825.284025.36285.63463月10246.873568.47:1374月15563.472654.3618910115月6月16685.4713685.214563.213562.31652.47263.986205.80334.27184.261427.88222合计/平均84308.58 24736.881213回归函数=U NEST(C4:C9,B4:B9)1415,Sheetl Sheet2,SheetsIII回7.1.2回归分析销售收入与成本A3.输入公式计算回归函数4.选择函数B13 A=LINEST(C4:C9,B4:B9)AB-L TD52月14563.215825.2840253663月10246.873568.47286.3274月15563.472654.36521.3585月16685.474563.21652.4796月13685.213562.31263.9810合计/平均84308.5824736.886205.80111213回归函数0.078525977 3019.411725141115.Sh按wCtrl+Shift+EnterM 组合键1川加1 平均值:1509.745125 计数：2求和：3019.490251国目用 100%G回7.1.2回归分析销售收入与成本提不CONCATENATE函数用于将两个或多个文本字符串合并为一个字符串，其语法结构为：CONCATENATE(t ext 1,t ext 2,.)o Text l,Text 2,为2-255个将要合并成单个文本项的文本项，注意：项与项之间必须用逗号隔开。这些文本项可以为数字、文本字符串或单个单元格的引用。5.设置函数参数6.查看计算结果短布局公式教据 事阀 姻桩色功能 a 将色为耗6.78二18柱上阳L2.选择4 月 15563.5 月 16685.SS其他图表矍用细5事售税全$.32178.225.36285.635.32142.841.35302.652.47334.272 QA1 Qzl 7A回7.1.3利用图表显示销售收入与成本的变化3.更改图表布局3?用分析裹Msx-Microsoft Excelrs文件产始一墙入 大血布有 公式 SJS 育田 视笑 特色功能猛叱骂快速布局更改另存为 色表残模板切丽/死选择克旗图表3SS1SAV工18000.00216000.00314000 00叵三丁1.选择18=菖，-5国I H勺国表工具回快速样式序三至表N当1WI苜后坐后轴网格法文件广妇 插入 三HI布号 公式0*精贴电-3233A小必应购S华文仿宋卵芈女行帮 R(2.选招a孑程式三j插入，井值赊 凌鹏.年元悟宙切观营持色功能文件L.j i,-,-系列捎售成本型.dL三百匕j二咨-/2名重设以匹配样式Q当前所选内容.单击【2二i图例 a理数家标筌.地模拟运苴表，造=SERIES(Sheet 1!$F$3,Sheetl!$A$4:$A$9,Sheet1l$F$4:F图表4坐房H销售收入与成本对比18000 0016000.0014000 0012000.0010000.0023456月份 i月2月3月4日;售癖Z5.设置销售成本率系列格式33.64%40.00%34.82%17.06%27.35%X24图表耳华文腺均A 号华文楷体1与隼文或节_ s华文宋体 月七即他寸绅I垩1.输入与成本对比加E系列横式1填充系列选项系列选项系列重叠(Q)阴影发光和柔化蝴分类间距世)无间距三维格式0-25*75重叁型大间距为系列绘制在 主坐标轴H)-;-。次坐标轴调2.单击选中J6.设置数据系列的绘制位置回7.1.3利用图表显示销售收入与成本的变化7.单击按钮8.更改图表类型国 J销总费用分析表,xlsx Microsoft Excel文件开始 插入 式而布局 公式 ms 亩面 根笑 特色功能tstt布嵩 格式吗单击1 物画更改另存为行/先在择蛰据快速布局快速柱式呼我图表招表布局色表样式图表 4 人=SERIES(Sheetl!$F$3,Sheetl 1$A$4:$A$9,Shed12345678910月份1月2月3月4月5月.6月%计/平均111213回归函数18000 00 16000.00 14000 0012000 00 10000.008000.00 6000 004000 00 2000.000.00K 4 X Sheatl Sh=t2 JSht3 JtJ_ 日 4 川_就绪|率均值:瑟8：诵示一计E28 共 109047.253 j通1口四 100%王回7.1.3利用图表显示销售收入与成本的变化9.设置次坐标轴垂直（值）轴格式10启定义次坐标轴的没直坐咏阳悟式TSHS=数字y Os 线空建发光和柔化边缘三维格式 对方方式坐标轴选项最小值最大值 主要刻度单位：9次要刻度隼位：0 逆序刻度值W）一对数刻度3 SU）叵二 显示单位包）：无日在图表上显示对度单位标茶母）主要刻度线类空Q）：外部臼 次要刻度线类必篡）无 0 坐标轴标英&）轴至3横坐标轴交叉：o自动）坐标轴值建）：o J 最大坐标轴值电）12.单击选中）自动 I固定。自动国）*固定9 自动）固定量）自动（Q）固定盘）0.0040.05 TJCH I _3：输（4,单击卜T二回7.1.3利用图表显示销售收入与成本的变化提不在对图表中的元素（如数据系列、坐标轴、图例、图表区及绘图区等）进行格式设置时，除了可以通过当前所选内容组进行设置外，还可以直接在图表中要设置的图表元素上单击鼠标右键进行设置。1L设置销售收入数据系列形状样式文件开始瘤入卬S!布局公式&毒河坂图特色不能12.设置销售成本数据系列形状样式te 喳入冲布号公式25需亩需双图特色功能设计 布局,SK冽销售成本浸苴所志内容悟式重设以匹配样式(；、tc)(Abe)Abe)Abe)(Abe Abe j?5昵式AbeAbeAbeU i上楼一塔 4下挈一层,选释窗格m前所选内容AbeAbeAbeAZAbe卷内容艺术字模式捧列a的为形状填充 A，、?形状轮8 中N:形叔原，土豆,图表44衣=SERIES(Sheetll$C$3,Sheetl!$A$4:$A$9,Sheet 11$(2二；B7H0Abe月份1月2月3月4月5月6月181614121086AbeAbeAbeAbeAbeBCD卜 be)-Abe10m份1AbeAbeAbeAOcAbeAbeAbeAbeAbeAbe .crtife.oo%V0.00%!500%0.00H500%)0 00%5.00%P均000%销售收入与成本对比18000.0016000 001120001设置40.0035 00%30.00%4000.002000.000.002500%r 20 00%15.00%-10.00%5 00%C CCN回7.1.3利用图表显示销售收入与成本的变化A13股置销售收入数据系列格式14调整数据系列间的分隔距离系列迭项填充t阴盘发光解化边爆 三维格式君ij选项系列重会位)分隔；2输入)一R%类间距d)无间距75系列绘制衣o主坐标轴9O次坐标轴6)里叠型大国陛3.单乐 关闭回7.1.3利用图表显示销售收入与成本的变化15.移动并放大图表囿.1销售收入与成本分析.3提身与技巧回7.2.1创建数据透视表统计各项费用数据透视表是一种可以快速汇总大量数据的交互式报表，是Excel中重要的分析性报告工具,通过数据透视表不仅可以汇总、分析、浏览和提供摘要数据，还可以快速合并和比较分析大量 的数据。下面将在各项费用支出明细对比表工作簿中对网店所售产品的费用构成进行分析,其具体操作如下。问力2单击上 三 数据总表格 图片用贴画 图表送你图切片器投施接文本天盾和贡脚皿 在号道小 能,，.%.表情 残图 流选罪送按 文本_C13 飞A 5.175_:A B C I DEF1 费用类型 费用名称背心单件背心两件装金丝绒短袖短款连帽卫衣2产品成本 产品基础成本 69.00 108.00 88.00 118.0=3产品成本 小礼品成本,i 2.00 5.00 3.00 H 5.q_4产品成本 包装辅料成本 1.00 3.00*2.00 3.C5 产品成本 吊牌 0.50 0.50 0.50 0.56产品成本 售后卡 0.50 0.50 0.50 0.E7物滂成本 仓储费用 2.00 5.00 5.00 4.C8物演成本 快递要用*8.00 8.00 8.00 Y 8.C9 人工成本 运营 4.14 6.48 5.28 7.C10 人工成本 美工 6.21 9.72 7.92 10.11人工成本 客服人员 H 3.11 4.86 3.96 5.312 人工成本-汽*2.07 3.24*2.64 3.E13 人工成本 L 选择-1*5.18 1 8.10 6.60 8,E2.设置数据透视表的显示位置回7.2.1创建数据透视表统计各项费用3.选择数据透视表的显示位置4.确认创建数据透视表E151617181920212223242526272829DShtl!$A$25|其他损失 网店扣点1-2735 6.21-108.00 13.955.009.72200.00100.0022.78h Sheetl Sheet2 Sheet3,一一f 10.邑 2.单考 J 200.C18.04 50.C23.E回7.2.1创建数据透视表统计各项费用5.显示空白的数据透视表6.选择要添加到报表的字段0噜弓川届哥更改报作计茸 数JE源 SIS同J*)-Jt各项建用受1明的对比表.xlsx-Microsoft.开始痛入文面W尾公式教方由切现更特色刁能UGS8XXB欢摇剧a表活动字段w好 瓶入切片26,用存陪选数安送财活动字段H E BA切片at，排序侬;选A25 A2MAi B C2627282930313233343536373839蓟据透视表2侬透湿表字般列表 X选择要;忝加到报表的字俊叵，A25A人行标签授作计算IMI 口诧丽t 口5+八 gffi显示若要生成报表，请从“数据透视表字段列表”中选择字段。L衣芸袖卫 S祢件件通脂 类名单两绒连 用用心心丝较 1费背莺金短 EonEBQ在以下区域同拖动学设报表诵送 H列标签U行标英 E数值一 推迟布局更敦25 I2627282930313233343536373839产品成本B-三求和项：背心单件 求和号包装辅料成本 产品基础成本 吊牌 售后卡 小礼品成本-拍摄和制作费用 模拍修图和后期其他特用731 AQ（单击选中6.92.07121.948次品率损失 108其他损失 0退换货损失 0踵则_ 13.948Sheetl,Sheet2 SheetT效振透视表字段列表选标要忝加到抿表的字设28.97/费用奕型 费用名林J背心单件 U背心两件盗 J全丝绒短油 J短裁连帽卫衣在以下区域回拖动字段囱 n/报表福送id行标英用用类型，推退布局更欷力列标筌工升倒 工效值求而质.更新回7.2.1创建数据透视表统计各项费用7.折叠整个字段8.筛选费用类型回7.2.1创建数据透视表统计各项费用9.展开整个字段10.调整字段次序口，J 7，/差用充一阚3对比表-xlsx-Microsoft Excel开始 描人 布局 公式 数据 亩阅 项园 特逗款连帽卫衣 求和项：金丝绒也袖 求和顶：背心单件 选择要家加到报表的字限文件学 活动字段：-Tti m r，宜，谟应穴注用皿活动字段空题IS豳a妙,z I弓晔 插A1273 118 0.5 0.5594288 0.5 0.53731690.5 至，责用类型,雳用锚I背心单件/背心两件装，金丝绒短油7裁连帽卫衣分组 N府用底选 为字葭覆量15.3411.44G拉张款边1己工11.88.84心V千巧丽酹3.542.642.07|露脐五分退独45.2235.5226.81322.：j在以下区域间推652.5/粽端送23223.617.613.810.627.926.21通行标签187.56140.96108.7831321.单击求和项一上移(U)下移(D)_移豕头(0 移至彖()丫移动潮8表落选黜移动到行标筌移动甥昉建E移动困数值X翻除字段ai5S(N).更新HTF和回7.2.1创建数据透视表统计各项费用提小开始 插入 灵茴布局 公式 数安 亩甩2单击选曜边行空计报表布息交行 E勃-E0列标SE 基边列C25布局他钱用 他瞥用 他费用 标签 产品成本教索透视表样式选项A 求和项:金丝绒B 次品率损失 H他损失2选择F电.选择108.00数据送祝表ED_网店扣点 _.工求和j臾：也款连帽卫衣I求和力:金丝绒甫Q求和j耻中长号13,95包装铺料成本 产品基础成本 吊牌后卡 小礼品成本拍摄和制作费用12731180,5942880.512.设置数据透视表样式在对数据透视表中各区域内的字段进行添加和删除时，除了可以直接在选择要添加到报表的字段 列表框中进行选择外，还可以使用拖曳的方式进行调整。如将鼠标指针移至字段列表框中任意一个字段 上，当其变为形状时，拖曳鼠标至行标签区域，即可将所选字段添加到该区域。若是在报表筛 选 列标签 行标签和z数值区域中，按照相同的操作方法，任意拖曳某一区域中的某个字 段至区域外，则表示删除该字段。回7.2.1创建数据透视表统计各项费用13.修改源数据14.刷新数据各项莠用支出明如花比表,xlsx Microsoft Excel国旧司 B-L)V刑 1更E布局 公式 数幅宙阅 视图特色功能6*5mn 三三参会计专用 A尹律砧姑 BZU-A*A*套叁专至，吗，/费 柱式格 36 字。;a 笠津步涉”q 对齐方式To?舞G 数字 G单石G2-A 218EF G1金丝绒短袖 88.00短款连帽卫衣 中长软淬帽P衣2118.00 1 218.0033.005.00 .5.0042.003.00 3.0056 0.500.50【修改数据0.500.5075.00*4：00 10.0088.008.00 8.0095.287.08 10.74107.9210.62*16.11113.96H5.31 8.06122.643.54 5.37136.608.85 13.43148.8011.80 17.90152.643.54 5.371RX17.AnX沼AC X2A RH回7.2.2使用数据透视图分析各项费用支出情况数据透视图是以图表的形式表示数据透视表中的数据。与数据透视表一样，在数据透视图 中可查看不同级别的明细数据，数据透视图创建的关键在于数据区域与字段的选择。下面将通 过数据透视图来直观显示各项费用支出情况对比，其具体操作如下。L插入数据透视图2.设置数据透视图位置J1X1 U透视表瓶入各项若用克力明玛对比图.xkx-Microsoft Excel页面布局 公式 效据 亩阀 被图 特色功能 u国日Q82击 单n S3E透眄电S3E1颤S%图表迷你图切片器超链播文本幅页盾和更肿 族选81 他授 文本选择声【34567891011121314费用类型产品成本本本 成成用本本 股成本成 础成料 基品辅工工工工工摄鬼用田 费将 作作HR 员员成后 人人提和背心单件 69.00 2.00 1.00 0.50I 0.50 2.00 8.00 4.14 6.21 3.117 8O 12.5.9036.oj1ABC费用类型建用在 为上足什 咎.有创窿数宗活现表及数京送祝匡 八，1十2产品成本不产品成车请送抒要分析的数据。选择一个表或区域6)产品成*5产品成本表/区懂 史!皿图1回面 国67产品成本 物流成本使用外部数据源)8物流成本1(C)910人工成本 人工成本连接名祢选择放置数据曲视表及数据透视图的位置/一-1。新工作表)1 单击e现有工作表a)T位置)：二，1112人工成本 人工成本13人工成本141 拍摄和制作费用 拍摄和制作蓬用 I-定J|_肖16展厂费用 w r y r j 53 j ttj+x J-ov 萋1718推广费用 免费试用 2.50 推广费用 赠送 2.00 1920推广费用 奖品 2.30 推广费用 站外宣传 6.2121其他费用 退换货损失 22其他费用 次品率损失 108.00 回7.2.2使用数据透视图分析各项费用支出情况3.添加字段4.添加字段j三 整工三用支匕明细对比tfi.xlsx-MkrosoFt ExceliA E布曷公式 SIS 丰间长图特色功装设计 布,唱式 分析 Q R o GBCD E F:.;:三二、三亍二二予星透视界1选捋要忝加到报表的字限表，请从“数据透 表”中选授字段。若要生成数据透视图，请用泮?背心单件E背心两件装I金丝缄坦他E短款隹帕卫衣中长菰连帽卫衣口 00停段（./报表筛选轴承园（分类）E物值在以下区域间拖动字段1U近宽松1恂的件装 求和须：露脐五分短袖 寸213298490882.941.470.50.552.54.412.205888.824.41839.84.9000.50.50019.95610.628222.941.477 2A1QU5.88窿力2Sha6十2 Shaa+3致生透视表字段列表选泽要添加到报表的字段.E向心中计：背心两件装 金丝缄运油 姮款连陶卫衣中长款连楣卫衣 J亮松T恤两件装/施侪五分姮油;套装,情儆，裤子在以下区域间拖动手段:/报表筛选E数值,轴字段（分类）责用名称推迟布目更新口图例字段（-r加值求和项.-求和项一 求旃:,，更赖回7.2.2使用数据透视图分析各项费用支出情况5.隐藏图例字段按钮6.取消选中所有数据吝I?强用文出国与？;比图xkx-Microsoft Excel,开始插入 莫酉布局 公式数据盲阅视圉 特色功能 设计 布局 杷式 S析公I/J rf)-_,位，用支出明比图.xlsx-Microsoft Excel?屡开E个字国喋沂密廿字技A切片，B5W 舱1QZ3B活动字修但一 j图表1二12.取消选中 P T/H显示报表篇选字段按钮，,国例字段按钮&)显M主标咕字段按钮凶/显不值字段按超00在以下区域同拖动营员:,来和顶盍上 求和顶套多求和项情0来和顶满3出如降!楼，口斗 出如诏.诲N its/tAA.J 1nlM 求起项.宛。工富沔、领求斤出后F三分反至求W次-套我求”号.毫占3II 全丝绒短油退款连幅卫衣一1中长蒸连帽卫衣 a宽松下曲两件装，雷航五分坦油 内春装卵供|国醛子_5.5 25.64 1.7714.1 4.42511.28 3.54/报表筛送.轴手段6类)期名前-n酬E妙值R R P回7.2.2使用数据透视图分析各项费用支出情况7.选择要筛选的数据8.更改图表布局回7.2.2使用数据透视图分析各项费用支出情况10.选择图表类型9.更改图表标题回7.2.2使用数据透视图分析各项费用支出情况11.添加数据标签12.设置数据透视图的背景墙颜色闰I V可.各发38用支为明更对比圉.xlsx-Microsoft Excel文件开始懂入瓦R布置公式数据亩间 特色功能茎列求和项精倡室%设或所选内容格式 21重设以匹配样式ZlL、h 1*I2单臬M图例昌数据标笠，“侬双格才等图区曲无脱解播内容的效疾标筌显示打开所送内容的数据标答其他数得标签选项却苜$A$7,Sheet56789101112求和项宽松他两件装求和项琴防五分姮袖求和顶套装求和喷情侣装求和I币推子7.1 销售收入与成本分析7.2 各项费用明细支出对比表7.3提高与技巧回7.3.1 数据透视表功能在工作表中成功创建数据透视表后，虽然方便了用户查看数据的需求，但是当数据太多时 就需要通过滚动页面才能查看所有的数据，此时，可以通过透视表功能来获得不同的视图效果,如调换字段的垂直或者水平视图，即将行移至列区域或者将列移至行区域，以便更好地观察数据。尔缸总 旧期(S)licrosoft Excel?8A文件3行标签4-白砂糖/创建组取消组合(U).厨切叠t)移刃(M)删除“日期(V)字国设数翦透视衰法项).视图特色功能日明则 iStf将10月下移。将10月移至刹口()34567891011A求和项:转用 行标奖 白砂糖椒梨用然精鲜W酸油香列标签二10月 11月 12月 总计F：C D E F578370 624130 719710 192221011000136401650041140201601980019800：田口92501665016650425501500016200210005220034960410404256011856019200300003600085200464800478800557200I L I S u400080001000022000567811月12月 辣橡需显示字段歹-表Q 11910idU 11月将“日期移至开头2.j择)将日期.移至列g4 51X IX16 1 Slieet4 Sheet 1 S二方e一 Sheet3-J 口AXI国，等，m_I，r_ AT a.等将行标签更换为列标签回7.3.2 在数据透视表中计算数据若某些字段需要计算结果时，可使用字段公式来完成。假设在某一工作簿中，销售额大于3 000元的订单，可以返回5%的折扣，则可以使用字段公式计算哪些订单可以返回折扣以及返回金额的大小。其方法为：在成功创建数据透视表的工作表中，单击数据透视表工具 选项选项卡，在计算组中单击域、项目和集按钮，在打开的下拉列表中选择计算字段选项。打开插入计算字段对话框，在名称文本框中输入折扣，在公式文本框中输入公式二销售额*IF（销售额3000,5%），如图所示，最后单击确定按钮便可在数据透视表 中自动显示计算结果。姨入计M字股插入字段更）（3.单击卜确定 关闭字段9：名称QP：公式也）：回7.3.3 将数据透视表拆分成多个分表在创建数据透视表的过程中，有时需要利用一个数据源重复地做多个数据透视表，为了操 作方便，可以使用拆分数据透视表功能，快速将总表拆分成多个分表。拆分数据透视表的操作 很简单，只需先创建数据透视表，再进行拆分操作即可。感谢聆听！学习进步!Excel商务数据处理与分析