Excel商务数据处理与分析 第8章 营销决策分析.pdf

翻烹析Excel商务数据处理与分析8.1 市场需求量分析8.2 定价策略分析8.3 销售成本预测分析8.4 客户消费能力分析8.5 提高与技巧回8.1.1添加分析工具库A分析工具库是Microsoft Office Excel的一个加载项程序,默认情况下是隐藏的,要想使 用该功能，首先应该在Excel 2010中添加该分析工具库。在Excel中添加工具库的方法很简单,其具体操作如下。1.单击1.选择选项命令百J-Microsof t Excel 卜回.叵猛入页面布局俎S S S审阅飕喳50能。Od融区另存为 d打开T关闭有关市场需求量分析的信息E:201阵Excel商务数据分忻Excel商务数据分.信息最近所用文件新建翔酶工例权限任何人都能打开、发制和更改 此工作簿的任何部分.E xcel选项常规俎15m保存语言高我自定义功能区快速访问工具栏上学吸信任中心加胤页名称，2.转到Excel加载项活动应用程序加蠡顼C hinese C onversion AddinFoxitReader PD F C reator C OM Add-in分析工具寄分析工具厚-VBA4（l单击）I非活动应用程序掩顶Microsoft Actions Pane 3打印保存并发送帮助准备共享0拿啦件前,请切其包 含以下内容：文档属性和作者的姓名残障人士无法阅读的内容版本今天 10:05（自推话受打印向导 不可见内容日期（XML）页眉和它脚 隙藏工作表力C hinese C onversion Addin 发布者：Microsoft C orporation 蓊容性：没有可用的兼容性信息心G.OfficeOfficel4AD D INSTC SC C C C:.gin$C reatorx86FPC _E xcelAd d i r C:.Officel4UbraryAnalysisANAU C:.ficel4LibraryAnalysisATPVBAE C:.Officel4LibrarySOLVE RSOLVEC:.ficel4LibraryLabel Printlabelpr C:.)Microsoft OfficeOfficel4OFFR C:.fficeOfficel4LibraryE UROTOO C.Microsoft SharedSmart TagXM1 C:.)Microsoft OfficeOfficel4OFFR C:.)Microsoft OfficeOfficel4OFFR传号X rccramUk，(x86nMirrnrnft nffirQfficel4AD D INSTC SC C ONV.D LL女工2,：con、.d C hinese and Simplified C hine回8.1.1添加分析工具库3.选择可用加载项4.查看添加的数据分析工具回8.1.2利用指数平滑工具分析市场需求量A指数平滑法是一种改良的加权平均法，它是根据本期的实际值和预测值，并借助平滑系 数（a）进行加权平均计算，预测下一期的值。一次平滑主要用于对某一数据发展趋的分析，若要计算具体的预测值，还需进行二次平滑。下面将利用指数平滑工具，通过二次平滑 对2019年和2010年的市场需求量进行预测，涉及的公式有Yt+T=a/bJT、at=2*St（D-St（2）,b产（a/l-a）*（工（1）-，（2）,其具体操作如下。1.单击数据分析按钮Mpdf串眄翅漆器软鲜版O 6 o 国眼岛获取 外除医，0金全部扁新ZI排序 选,Ai 9排序和流选（2.单击卜T分列删除分级显示 重复项辱，效掘工具分析iJ8，AVAB CD T图书市场潜量的预测1 年）购买人数 购买频率市场潜在需求量（册一次指数平消a2007年8032402008年11055502009年15069002010 年16048002011 年241372382012 年260615601102013 年450836002014 年600618001201.、9875493312 2u.单击1097610967132J 110055500142018 年 I12008960015H 4 1*S heetl S heet2 S heet3 忙12019年预测需求蚩一 回8.1.2利用指数平滑工具分析市场需求量A3.设置指数平滑参数4.查看一次指数平滑结果D5bN=B5*C5C i D123456789101112131415 1 S he。图书市场潜:市场潜1L5.单击屏匹3购买人数 的买频率804.单击选埒123456789101112131415图表1预测（平滑结果一次指数平滑a=o 3二次指数平涓a=o.3#N/A240457767.1678.13709.5391304.86172911.458513393.4375534471.3645995947.409385634.22281包一次指数平滑U S heetl,S heet2 S heet3.已HiE测Gb值I n提醒Excel中的指数平滑法需要使用阻尼系数，阻尼系数越小，对近期实际预测结果的影响就越大。在实际应用中，阻尼系数是根据时间系列的变化特征来选取的，若时间序列数据波动不大，则阻尼系数 取值范围应介于0.10.3之间；若时间序列数据具有明显的变动倾向，则阻尼系数取值范围应介0.60.9 之间。回8.1.2利用指数平滑工具分析市场需求量5.设置二次平滑参数6.显示二次指数平滑结果回8.1.2利用指数平滑工具分析市场需求量7.计算a值8.计算b值H5 八=0.3/(1-0.3)*(E5-F5)E匚J LI _1预测输入后按“Enter”键2一次指数平滑a=o.3二次指数平滑a=o.3a俏 b侑 3N/A4240IN/A5457240674.OOl 93.OOl6767.1391.91142.307678.13654.54701.728709.539671.053748.0391304.8617697.99321911.73102911.458511122.801154700.12113393.4375532374.8613024412.01124471.3645993087.8646785854.86135947.409384056.3146237838.50145634.2228145380.0809535888.3615M 4 H S heetl A S heet2(S heet3 t0._Jj L 网 J回8.1.2利用指数平滑工具分析市场需求量A9预测2019年需求量10预测2020年需求量购买笏率E15 A=G14-H14*1CDEF图书市场潜量的预测市场潜在需求量册一次指数平渭a=o.3二次指数平滑a=o.川!Sheetl 人 SheZ2、Sheet33240IN/A5550240#N/A69004574640767.13723678.1361560709.5396836001304 F,1 E 69636002911.狈澳 浩禾 122,549333393.437553 I 374.1665804471.3645993087.1555005947.40938)4056.：896005634.2228145380.2019年手页测需求量_5771?.45 1&I围四100%-QE16 G=G14-H14*2CDE F73723678.13861560709.5399836001304.861710636002911.45851in11549333393.43755323712665804471.364599308,13555005947.40938405114896005634.222814538(152019年预测需求量162020年颈测需求量5670.53 117181一次指数平滑（预测结果k萍19208000216000 22/M 公式市核计具ttssg最大值(M)定义的名称小值(DCD33求解变量101112252000占IX八 也1330000302005000*96,000 26.1439200395009300 r126.000 30.1548000484508950 r160.000 34,1654000544506000 rH98,000 3&1718最大销售利 涧_1L选择回8.2.3计算并分析最优定价6.选择要插入的函数5.单击插入函数按钮回8.2.3计算并分析最优定价7.设置函数参数1.输入8.选择要插入的函数回8.2.3计算并分析最优定价10.设置函数参数9.选择参数组合方式1输入8.2.3计算并分析最优定价11.查看计算结果8.1 市场需求量分析8.2 定价策略分析8.3销售成本预测分析8.4 客户消费能力分析8.5 提高与技巧回8.3.1创建散点图散点图能表现出因变量随自变量而变化的大致趋势。下面将在销售成本预测分析工作 簿中利用散点图来查看每个月的销售成本合计数的变化情况，其具体操作如下。回8.3.1创建散点图4.输入图例名称3.选择图例项回8.3.1创建散点图5.添加坐标轴标题6.更改标题名称图表:3 一 A回8.3.1创建散点图7.添加次要网格线8.查看添加后的效果回8.3.2添加线性预测趋势线利用Excel进行数据分析时，除了能够根据数据建立图表外，还应该利用趋势线、误差线和 折线等工具进行分析，这样才能使数据分析结果一目了然。下面将利用趋势线来预测12月份的 销售成本，其具体操作如下。1.添加趋势线j，豆.闻 M 口 i畲插入 图表标髭生标轴标我数据标东 坐标轴 背昱 分析 层性-(图索1底组所选内容格式更发以匹配样式2.无射除所选起野或；如!选择,则删除所有境;线性趋势线为所选图表豕列添加指数总岩线为所困B衰浜列添加;线由骸通势线为所话图表系列添加 2衍IW5W3雌1.选择销售成本月份回8.3.2添加线性预测趋势线3.设置趋势线选项4单击）4.设置坐标轴格式回8.3.2添加线性预测趋势线5.设置坐标轴选项6.查看设置后的水平轴情能成本预澧分析.xkx-Microsoft Excel文件 11黏贴j，剪贴板字体厘诊如SI右曷 公式 初8 M同 视当-1 o 叵特色功能&C常观 A，二对开/式ST?16171-19202122232425262728293031G37A B CD销售成本.消售成本线性（徜售成本）工2.（设置后的水平轴）回8.3.3 INT和FORECAS T函数的使用F面将综合利用INT和FORECAST函数来计算12月份销售成本的预测值，其具体操作如下。2.设置函数参数1.选择函数回8.3.3 INT和FORECAS T函数的使用F14 二二 A=F0RECAS T(A14,F3:F13,A3:A13)A B C D EF12日松 销售成本采购成本 推广出用配库存量销售成本合计131 40.56 5.5 8 559.0642 50.48 6.58 8 570.0653 55.68 12.45 8 581.1364 60.54 10.25 8 684.7975 61.47 11.26 8 686.7386 62.85 15.36 8 _a2V21192 11:11 结一片10119 120.2 23.65 8 8159.851210 102.35 5.34 8 5120.691311 50 8.69自售成;3 5_I1 71.6911412 12月份本狞测伯：30.507636411516y q-x-3.查看计算结果FORECAS T X 4=nrr(FORECAS T(A14,F3:F13,A3:A13)|IAB CT D EF _I I12月份销徒龙本采的成本推广理胃销售成本合计，3140.56 1-八一)559.064250.48 6.58 8 570.065355.68 12.45 8 581.136460.54 10.25 8 684.797561.47 11.26 8 686.738662.85 15.36 8 692.219782.35 14.15 8 8112.510888.65 23.56 8 8128.21119120.2 23.65 8 8159.851210102.35 5.34 8 5120.691311.50 8.69 8 5_ _71,69.14I 1:12.12月份销售成本强测值：13,A3:A13)15 16良.忖 X S heetl S heet2 S heet3.。IH.1|四口四4.输入函数提醒INT函数，是将一个要取整的实数（可以为数学表达式）向下取整为最接近的整数。INT函数很少 单独使用，一般和其他公式嵌套在一起，完成计算。需要注意的是，INT函数是取整函数，不进行四舍 五人直接去掉小数部分取整，如INT02.86）,计算结果为回8.3.3 INT和FORECAS T函数的使用ABCDEFG12月份采购成本销售成本推广费用配送费库存费销售成本合计3140.565.58559.064250.486.588570.065355.6812.458581.136460.5410.258684.797561.4711.268686.738662.8515.368692.219782.3514.1588计算结果10888.6523.5688119120.223.6588159851210102.355.3485120691311508.6985711169141212月份销售成本预测值213015116收在4-!Sheet 1Sheet2/Sheet?A。/口 T1111E5.查看取整后的结果8.1 市场需求量分析8.2 定价策略分析8.3销售成本预测分析8.4客户消费能力分析8.5提高与技巧回8.4.1不同年龄段客户消费能力分析对客户的服务质量，将会直接影响销售增长和效益扩大。而要提升交易成功率，一个关键 因素就是分析不同年龄段客户的消费能力，做到有的放矢，最终促成交易。下面将在客户消 费能力分析工作簿中创建带折线的柱形图来分析不同年龄段客户的消费能力，其具体操作如ToL插入柱形图2.选择源数据回储。J!n文件款据 表格 出腺.表格百迷你留R5布曷公式由5 j阅视更开发工H行色功能&|同圜用 也列&曰均 晅片？5贴画口客户;醺8能力分析-Microsoft Excel柱形图切片8112345678年龄段客户数量j单击产 敢点图其砌=5025122283二维拉形图三维住形图Jl i M 15 iM1 2 3 4 2 3回8.4.1不同年龄段客户消费能力分析3.添加数据4.编辑数据系列2.输入区单击）5.更改数据系列的图表类型特色厂设计I布局格式 C更改图表类型iLl u;更改另存为 图表类型模板L,二行选择数据快速布局画移动图表图表布局图表样式vS口模板端柱开羯|E折线图图表2450.00A12年龄段350J _400.00350 00300.00250.00200.00150.00Ba=S ERIES（平均月消费金额（折线）”,，不同年CDiLimil l(选择)iSteiu)IS j C(A)函更改系列图表类型co“?s食馆图条形图面积图x Y儆点图）股价图曲面图图坏图 气泡图 雷达图管理模板）设置为默认图表G）柱形图确定 取消回8.4.1不同年龄段客户消费能力分析7.设置数据系列的填充颜色8.添加数据标签回8.4.2不同性别客户消费能力分析不同性别的客户，对于购买商品的要求有所不同。比如，男性客户的购买行为相对较少，且多是有目的性的，一旦决定购买某种商品，男性比女性更容易作出购买决定。下面将利用图 表和筛选功能，分析是男性客户还是女性客户的消费能力强，其具体操作如下。1.新建图标集规则2.设置图标集的显示方式E3 A 100AB C成交用姓名 性别年龄段I 期，表格格式.差适中万的椀翻陈万韵陈胤徐群陈一：方向A c=57形状,O0。Q o UC 4令7 O 一 突出显示单元敬则出）项目选取规则CD即 S8J8 条(B)色险|S 4日l OU100300200300500100100600600400200 O 0 OSS se BB B其他规则国).回平融叫 八*(3蓬希.oi oil ilil8 ol elll soil I1.选择新建格式规则I gm选择、回8.4.2不同性别客户消费能力分析3.设置文本对齐方式4.选择图表类型回乜*）一B客户;制S能力分析OO.xlsx-Microsoft Excel文件开加摘入丈面杉属公式2?据审阅视圉开发工具特色功能 G宗伏 11帖贴$B/U Z k笠要贴板字体 常规_:事*对齐方云文件侵入文面布局公式初 M阀姬开发口I特色功能 go圆O其他图表E34 100文本右对齐BC12姓名性别成交用将文字右对齐.zrvT购买力定援我渝 江凤凡二喜国援曲家峰江欢 万陈杨胡陈万蔡陈周徐蒋陈男女男女女男女女男男男女680138034608223332333332OOOOOOOOOOOOOOOO8OOOOOO 113235116642星级等级2 互 _5678,9 10姓名 位一 陈凤 杨凡 胡兰 陈喜 万国定 蔡媛媛 陈海滋 周家渝 徐峰13蒋江上L陈欢15张展1C;殁既立 付 不 就5a|-2 IUI心（2.箪击）包柱形图折戌图 St S3 TTzCH JUr取点图圆谊图”二建住形H!期延则三谯柱形S!四拄图回8.4.2不同性别客户消费能力分析6.仅筛选男性客户5.筛选设置成交用户分析A5 人扬凡B I C!D回8.4.2不同性别客户消费能力分析7.查看筛选结果8.女性客户的筛选数据图表5、FGHI2 35 8111213151617182122232425262728296005004003002001000更改数据号后的图表购就 IIl Il f:11111.11F里万江男杨凡男万国 定男周家 海男徐崎男蒋江男张展男攀顿里蒋程再陈幻8.1 市场需求量分析8.2 定价策略分析8.3 销售成本预测分析8.4 客户消费能力分析8.5提高与技巧回8.5.1创建带对比效果的柱形图如图8-80所示为带对比效果的柱表形图完成效果，通过这种图表，不仅可以清楚的展示各 个店名的业绩完成情况，而且还能对比出计划目标与实际完成的差异。创建此类图表的方法为：在每个店铺数据下方插入两个空行，然后分别将上半年和下半年的数据下移一行2 选择工作表中包含数据的单元格区域后，插入堆积柱形图，然后修改图表标题，按Delete”键删除网格线和水平轴标签；3 为全年销售计划数据添加货币样式的数据标签，并将分类间距设置为0%”，然后将 垂直轴的最大值设置为12000”；4将图表区与绘图区设置为相同的填充颜色，利用文本框在水平轴位置输入各店铺名称。回8.5.2采用自选形状作为折线图的数据点折线图的数据点，默认情况下为小方块、圆点、三角形和菱形等常规几何形状。有时为了 制作一张与众不同且具有专业性的折线图，可以对折线图中的数据点进行进一步设置。其方法 为：在插入选项卡的插图组中单击形状按钮，在打开的下拉列表中选择要绘制的 形状后，在工作表中拖动鼠标进行绘制。选择绘制好的形状后，按Ctrl+X组合键，然后单 击折线图中的数据点,按Ctrl+V组合键进行粘贴，即可将更改折线图上的数据点标记。更改折线图数据标记后的效果感谢聆听！学习进步!Excel商务数据处理与分析