Excel在市场营销与销售管理中的应用 - 万水书苑-出版资源网
39
0
0
全文
(2) 5.1 相关基本数据表格的建立 在商品销售与业绩提成数据分析中，需要先制作一些基本表格，本节介绍其中的“参数设置” 工作表、“本月销售数据”工作表以及“计划完成情况表”工作表的构建。. 5.1.1 建立“参数设置”工作表 在商品销售与业绩提成中，有一些基础性数据，它们需要多次被一些计算公式所引用。为此，可以 建立一个“参数设置”工作表，用来集中存储这些基本数据，其好处体现在三个方面：有效地简化计算 公式、便于基础数据的维护、适应政策的动态变化。 【例 5-1】 请根据本章整体实例的需要，首先设计如图 5-1 所示的“参数设置”数据表；然后对该 表格中的相关区域定义名称，以方便后面公式的引用；最后，对该工作表进行安全保护，以防止相关人. c c c c. 图 5-1. c. 标志制作要用到的知识点包括：表格格式设置、序列输入法、有效性设置、数字格式设置等，为了使数 c. 行名称定义和工作表安全设置。. 6. Chapter. 据显示清晰，表格中还取消了网格线的显示，同时边框也进行了适当设置。除此之外，本例中还要求进. c. 下面就根据以上的分析，分三个阶段介绍本例的整体操作。具体的操作步骤如下：. c. 1．创建“参数设置”工作表 “参数设置”表的建立相对简单，本节对其不作详细介绍，仅强调以下几点： c. （1）启动 Excel 2007，建立一个工作簿，并以“商品销售与业绩提成”为名保存。. 9. Chapter. 8. Chapter. 7. Chapter. “参数设置”工作表的显示效果. 问题分析：上述的表格包括了商品信息、销售员信息，以及不同情形下提成比例的确定方法。其中. 5. Chapter. 4. Chapter. 3. Chapter. 2. Chapter. 1. Chapter. 员对该表格中数据的无意识修改，或人为地故意破坏。. （2）将默认的 Sheet1 工作表的标签，改为“参数设置”，使之能“见名知意”。. 176.
(3) Excel 在销售业绩与提成计算中的应用. 第5章. （3）商品信息表中“销售状态”一栏，只有三种情况，所以可以设置下拉菜单进行输入。 （4）销售员信息表中的“编号”一栏，可以采用序列方式，利用鼠标拖动输入。 （5）注意该工作表的三个表格中行高、列宽、边框的设置，表格标题行中单元格填充色的设置， 以及数字格式的显示方式设置，还有 J4:J6 单元格区域的合并居中设置。 （6）调整表格的显示比例，使三个表格在屏幕上显示的内容完整、大小适当。 （7）在“Excel 选项”对话框中，取消本工作表中网格线的显示。. 2．为“参数设置”工作表中相关区域定义名称 为区域定义名称，有助于简化公式和其他操作，免去输入或选取单元格地址的烦琐。 下面以将图 5-1 中的销售员姓名列表，定义为名称“姓名”为例，介绍定义名称的方法（其实商品 编码列表定义为名称“商品编号”，与之操作一样）。操作步骤如下： （1）选取“参数设置”工作表中的 G2:G10 单元格区域。 （2）单击“公式”→“定义的名称”→“根据所选内容创建”按钮，如图 5-2 所示。. 图 5-2. “根据所选内容创建”按钮. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. 1. c. Chapter. （3）如图 5-3 所示，在弹出的“以选定区域创建名称”对话框中，选择“首行”复选框，使之为 选中状态，然后单击“确定”命令按钮。. 2 3 4 5. 图 5-3. “以选定区域创建名称”对话框. 6. 经过以上操作，G3:G10 单元格区域中的 10 个销售员姓名，就被定义为名称“姓名”，以后在公式 的编辑、定义“序列”类型的数据有效性等方面，都可以使用该名称。. 7. 3．对“参数设置”工作表进行安全保护. 8. 本工作表制作完成后，考虑到其中的商品销售状态、店面价格，销售员 1～2 月份累计销售额，以 及不同情形下的提成比例等数字都是重要的基础信息。为防止对以上数据的无意识修改，或人为故意. 9. 破坏，本工作表的安全性必须得到保护。具体操作步骤如下：. 177.
(4) （1）选定“参数设置”工作表，使之为当前活动工作表。 （2）单击“审阅”→“更改”→“保护工作表”命令按钮，如图 5-4 所示。. 图 5-4. “保护工作表”按钮. （3）如图 5-5 所示，在弹出的“保护工作表”对话框中输入保护密码（也可以什么也不输入）， 然后单击“确定”命令按钮。 （4）上面步骤（3）中如果输入了密码，还会再弹出如图 5-6 所示的“确认密码”对话框，在其中. c c c. 图 5-5. 图 5-6. “确认密码”对话框. c. （5）工作表被保护之后，无论是将光标定位到编辑栏，还是在单元格上双击鼠标，都无法使单元格. c c c. 图 5-7. c. 按钮，然后在“撤消工作表保护”对话框中，输入设置的保护密码，即可取消对其保护（如果前面没 c. 有输入密码，则单击“撤消工作表保护”按钮，就直接取消其保护，显然这样设置对工作表的保护很. 9. Chapter. 错误提示信息对话框. （6）如果想取消对工作表保护，如图 5-8 所示，可单击“审阅”→“更改”→“撤消工作表保护”. 8. Chapter. 7. Chapter. 6. Chapter. 5. Chapter. “保护工作表”对话框. 进入编辑状态，而是显示一个如图 5-7 所示的错误提示信息对话框。. 4. Chapter. 3. Chapter. 2. Chapter. 1. Chapter. 重复输入密码，然后单击“确定”命令按钮，完成对工作表的保护。. 不安全：你的安全保护，别人可以轻松地撤消掉）。. 178.
(5) Excel 在销售业绩与提成计算中的应用. 图 5-8. 第5章. “撤消工作表保护”按钮. Excel 提供了很多种不同层次的数据安全保护措施，上面介绍的仅仅只是对某一个工作表整体的保护，这 种保护有一定的弊端（采用这种方式的保护后，工作表中所有单元格均不能再编辑；而很多情况下，都只是 想保护含公式的部分单元格，而那些需要操作人员输入基础数据的单元格，还应该允许编辑）。Excel 中的保 护设置很多，具体还包括：对工作簿整体保护，对单元格进行保护，对屏幕窗体保护，只对工作表上部分单 元格的保护，对文件进行密码压缩保护等。这些保护方法，将在后续相关实例中补充讲解。. 5.1.2 建立“本月销售记录”工作表 建立“本月销售记录”工作表的目的，就是用来及时输入公司商品销售的业务记录。该表格中数 据准确、快速地输入，是事后进行销售分析和提成计算的基础。 【例 5-2】 请制作如图 5-9 所示的“本月销售记录”数据表。其中“销售日期”包含了 3 月份的 所有日期，销售记录共 98 条，包括了所有商品名称、销售类别和所有销售员名称（该表格数据录入到. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. 1. c. Chapter. 了第 100 行，数据已经具有一定的代表性，能够满足本章相关内容学习的需要，本章所讲的操作方法， 对于更多数据记录的数据分析，同样切实有效），限于纸张大小的限制，图 5-9 在进行屏幕截图的时. 2. 候，隐藏了其中的第 21～90 行。 问题分析：本表包括了销售日期、销售员名称、商品名称、数量、成交金额等信息，其中的字段. 3. 都是将来进行销售汇总与业绩提成计算所必需的。为了减少数据输入的工作量，表中部分字段设置了 公式（图中带填充颜色的列），它们能够依据其他数据自动得到结果。该表格的制作首先要建立框架，. 4. 然后再向其中输入数据记录，另外该数据表格还可以转换为 Excel 2007 中特定的“表”格式，以便进 行一些其他操作。. 5. 1．创建“本月销售记录”数据表的框架. 6. “本月销售记录”数据表框架的创建，包括输入并设置表格的列标题，以及相关单元格的格式，并 编辑好需要的计算公式，为后面的数据输入做好准备。操作步骤如下：. 7. （1）打开“商品销售与业绩提成”工作簿。. 8. （2）将 Sheet2 工作表的标签，改为“本月销售记录”，使之能“见名知意”。 （3）按照如图 5-10 所示的文字，建立表格的标题（要进行跨行合并）和列标签名称。. 9. （4）在需要计算结果的单元格中录入公式，其中：D2～F2 单元格中的商品名称、销售状态、 店面价格，需要根据 C2 单元格中输入的商品编码，利用 VLOOKUP 函数到“参数设置”工作表的. 179.
(6) 产品信息表区域查询而得到；H2 单元格中的折扣率，需要根据 F2 单元格中的店面价格和 G2 单元格 中的成交价格数据对比计算而得到；J2 单元格中的成交金额，是 G2 单元格中的成交价格和 I2 单元. c. 图 5-9. c. “本月销售记录”数据表的显示效果. c c. 图 5-10. 输入表格的标题和列标签名称. 根据以上的分析，相关单元格中的计算公式分别如下： c. . D2 单元格中公式为： =IF(C3="","",VLOOKUP(C3,参数设置!$A$3:$D$14,2,FALSE)). c. . c. . c. . 8. Chapter. F2 单元格中公式为： =IF(C3="","",VLOOKUP(C3,参数设置!$A$3:$D$14,4,FALSE)) H2 单元格中公式为：. c. =IF(E3="促销商品",(F3-G3)/F3,IF(G3<>F3,"数据错误","")) . 9. Chapter. E2 单元格中公式为： =IF(C3="","",VLOOKUP(C3,参数设置!$A$3:$D$14,3,FALSE)). 7. Chapter. 6. Chapter. 5. Chapter. 4. Chapter. 3. Chapter. 2. Chapter. 1. Chapter. 格中的数量进行相乘的结果。. J2 单元格中公式为： =IF(I3="","",G3*I3). 180.
(7) Excel 在销售业绩与提成计算中的应用. 第5章. 以上公式中，前面的 IF 函数的作用都是对公式计算的一种控制，只有当指定条件满足，才进行相 应的公式计算，因为这些公式在数据输入前，都要复制到下边的很多行。 （5）按下 Ctrl 键，选取上面输入公式 D2:F2、J2、H2 等单元格，然后向下一直拖动复制到第 100 行（该数字可更大，本章只是介绍操作方法，所以公式输到 100 行即可）。 （6）采用定义“序列”有效性的方法，为“销售员”和“商品编码”两列建立下拉式列表。其中， 序列的来源分别是前面定义的名称“姓名”和“商品编码”，二者的操作方法是一样的，下面仅仅以 “销售员”为例，介绍其下拉列表的操作。其建立方法为：选取 B2:B100 单元格区域，单击“数据” →“数据工具”→“数据有效性”按钮下边的箭头，从出现的菜单中选择“数据有效性”，然后在打 开的“数据有效性”对话框的“设置”选项卡中，如图 5-11 所示，在“允许”下拉列表中选择“序列”， 在“来源”文本框中输入“=姓名”。. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. 1. c. Chapter. 图 5-11. 为“销售员”字段设置“序列”有效性. 2. （7）为数据表进行格式设置（具体操作不再详述），内容包括：为数据表区域设置边框，为数据 标题设置字体效果，为字段列标签添加填充色，为含公式单元格添加填充色等。. 3. 经过以上操作，本月销售数据记录框架建立完毕，如图 5-12 所示，看上去单元格都是空白，其实 有填充色的那些都设置有公式，相关单元格的数字格式也已设置为所需格式。. 4 5 6. 图 5-12. “本月销售记录”数据表的最终框架设置效果. 7. 2．向“本月销售记录”数据表输入记录. 8. “本月销售记录”数据表的框架建立好之后，开始需要先输入一些测试数据。如果测试结果中， 公式计算和数据显示都没有问题，下面就可以按部就班地进行销售记录的输入了。输入时一定要认. 9. 真，要确保输入数据的准确性，只需要输入基本数据即可。 如图 5-13 所示，就是已经录入了两条销售记录后的“本月销售记录”表的效果。. 181.
(8) 图 5-13. 输入了两条销售记录的“本月销售记录”数据表. 3．把数据表设置为 Excel“表”格式 如果在数据录入过程中，还想适时了解商品销售方面的一些具体情况，比如：想了解截止到目 前本月总销售额是多少，截止到目前为止单笔交易的最大销售额是多少，截止到现在每个人员或者 每种商品的销售情况（也就是按销售员或商品名称进行筛选）等。 以上这些信息的获得，只需要将上面的数据表区域转换为特定的 Excel“表”格式就可以了。要将 上面的数据表区域，变为 Excel“表”格式，操作步骤如下： （1）选取数据表中已输入数据所在的区域，如图 5-13 中的 A2:J4 单元格区域。 （2）单击“插入”→“表”→“表”按钮，在弹出的如图 5-14 所示的“创建表”对话框中，检. c c. 图 5-14. “创建表”对话框. c. （3）单击“确定”按钮后，Excel“表”就创建完成，效果如图 5-15 所示。. c c c c. 图 5-15. c. 选箭头，便于对相关字段的数据筛选；在最下面增加了一个汇总行，其中默认的汇总函数为“求和”， c. 单击汇总结果所在单元格右侧的箭头（如图 5-15 右下角所示），还会弹出其他相关的汇总函数，便于. 9. Chapter. 根据数据区域转化的 Excel“表”格式. 在图 5-15 中，A2:J4 单元格区域被转换成了 Excel“表”的格式，其中每个字段旁边增加了一个筛. 8. Chapter. 7. Chapter. 6. Chapter. 5. Chapter. 4. Chapter. 3. Chapter. 2. Chapter. 1. Chapter. 查表数据的来源，保证其正确性，并选中“表包含标题”复选框。. 对已有数据的汇总分析。. 182.
(9) Excel 在销售业绩与提成计算中的应用. 第5章. 5.1.3 建立计划完成情况动态图表 到了月底，本月销售工作完成之后（甚至于在月中的任意一个日期），销售管理人员都需要了解 本月销售计划的完成情况。为此，本节制作一个“计划完成情况动态图表”。根据该图表，有关人员 能随时查看本月计划完成情况，以及本季度销售占全年比例。 【例 5-3】. 请根据“本月销售记录”工作表中的销售记录，制作一个“计划完成情况动态图表”. 工作表，要求制作效果如图 5-16 所示。. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. 1. c. Chapter. “计划完成情况动态图表”工作表的显示效果. 2. 图 5-16. 3. 问题分析：图 5-16 中两个图表的设计思路一样，下面以本月计划完成百分比图表为例说明。首先， 需要汇总出 3 月份目前销售总额，然后计算其占 3 月份销售计划的比例；而“温度计式”图表就是用. 4. 计算出的比例数字制作的一个柱形图，不过其格式进行了一定的设置——系列颜色与区域颜色采用了 对比色效果，系列的分类间距设置为“无”（这样才能使其充满整个横坐标轴）。下面分两个阶段介. 5. 绍其操作方法。. 1．创建作为图表来源的数据表 6. 在本例中，首先需要创建作为“温度计式”图表来源的数据表。操作步骤如下：. 7. （1）先按照如图 5-17 所示的格式，设置表格的框架，并输入其中的三个外部来源数据。 （2）利用公式，计算相关单元格 D6、D7、I6、I7 的运算结果，其中： . D7 单元格中公式为： “=D6/D5”。. . I6 单元格中公式为： “=D2+D6”。. . I7 单元格中公式为： “=I6/I5” 。. 9. D6 单元格中公式为： “=SUM(本月销售记录!J:J)” 。. 8. . 183.
(10) 图 5-17. 作为图表来源的数据表的原始框架. （3）设置单元格的边框和填充色，取消网格线显示，只保留自己设置的框线，最终效果设计如图 5-18. c c. 图 5-18. 创建好的作为图表来源的数据表. c. 2．制作“温度计式”任务完成比例图. c. 下面先来制作用来反应本月销售计划完成情况的“温度计式”图表，操作步骤如下： （1）选取 C7:D7 单元格区域。. c. （2）单击“插入”→“图表”→“柱形图”按钮下边的箭头，从弹出的“柱形图”类型列表中， 单击第一种图表的样式按钮，制作出一个柱形图，适当调整其大小和位置后，最终在工作表上的显示. c. 效果如图 5-19 所示。 （3）对上面制作的柱形图，按照以下操作，对其显示效果进行设置。. c. . . 在横坐标轴上单击右键，从快捷菜单中单击“删除”选项，取消图例的显示。. . 在图表区域上柱形以外的位置单击右键，从快捷菜单中单击“设置绘图区格式”选项，然后. c. 在图例上单击右键，从快捷菜单中单击“删除”选项，取消图例的显示。. c. 8. Chapter. . 从弹出的“设置绘图区格式”对话框中，选取“填充”选项卡，设置为茶色的“纯色填充”. 9. Chapter. 在标题“完成比例”上，双击鼠标使之进入编辑状态，将文字修改为“3 月份销售计划完成 百分比” ，并设置字号为 16 号大小。. 7. Chapter. 6. Chapter. 5. Chapter. 4. Chapter. 3. Chapter. 2. Chapter. 1. Chapter. 所示，每个数据表下面的空白位置为制作图表的预留位置。. 的效果，如图 5-20 所示。 184.
(11) Excel 在销售业绩与提成计算中的应用. 图 5-19. 第5章. 制作出柱形图的开始效果. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. 1. c. Chapter. 2 3 4. 图 5-20. 设置绘图区为茶色的“纯色填充”效果. 5. . 在图表的柱形（数据点）上单击右键，从快捷菜单中单击“设置数据点格式”选项，然后从. 6. 弹出的“设置数据点格式”对话框中，先选取“填充”选项卡，设置为蓝色的“纯色填充” 效果（参见图 5-20）；然后选取“系列选项”选项卡，在其中将“分类间距”调整到最左侧“无 . 在图表的数据点上单击右键，从快捷菜单中选取“添加数据标签”选项，让图表能够将完成. 7. 间距”，如图 5-21 所示（这一步骤的正确设置，是使柱形图变为温度计样式的最关键步骤）。. . 8. 计划的百分比数字显示出来。 在纵坐标轴上单击右键，从快捷菜单中单击“设置坐标轴格式”选项，然后从弹出的“设置. 9. 坐标轴格式”对话框中，选取“坐标轴选项”选项卡，在“最大值”、“主要刻度单位”、“次 要刻度单位”中全部选择为“固定” ，并分别输入“1.5” 、“0.1”和“0.02”，如图 5-22 所示。 185.
(12) 图 5-21. 设置数据系列之间无间距. 图 5-22. 设置纵坐标轴数字的最大值和刻度单位. 经过以上操作后，图 5-19 中的柱形图，就会呈现为如图 5-16 所示的“温度计式”效果。 用来反映本季度累计销售额占全年销售计划比例的图表，其制作方法与上面的一样。. c c. 到了月底，需要对本月的商品销售情况进行统计汇总，包括按日期进行汇总、按商品名称进行汇 总、按销售员进行汇总等。在 Excel 中，进行数据统计汇总的方法很多，例如分类汇总、数据透视、. c. 合并计算等，而本章重点介绍利用相关的工作表函数进行数据汇总。 除了进行数据的统计汇总之外，还需要制作相关的数据图表，以便直观地显示销售情况的变化趋势，或. c. 分析整体销售中各种商品的比例，以及销售人员各自销售业绩的比例。. c. 5.2.1 按日期汇总销售数据并制作折线图 【例 5-4】 请根据“本月销售记录”工作表中的销售记录，汇总出每天各种商品的销售总额；然 c. 后根据每天销售总额的大小，对其结果作出不同格式的标示；最后制作出用来反映每日销售额变化趋 势的折线图。制作好的数据表格与图表效果，要求如图 5-23 所示。. c. 问题分析：表中“日期”一列，可以按序列方法输入； “销售额”可以根据“本月销售记录”工作表 中的销售记录，利用 SUM 函数和 IF 函数构造组合公式求和得到，其中显示的数据条大小可利用条件格式. c. 化来设置；折线图表的制作中，对图表区域的填充颜色、数据系列的线条颜色与其他效果（如粗细、阴影 等） 、图表标题的文字格式和填充颜色、纵坐标数字显示方式以及横坐标轴的格式都做了一定的美化设置，. c. 操作中需要注意。. 9. Chapter. 8. Chapter. 7. Chapter. 6. Chapter. 5. Chapter. 4. Chapter. 3. Chapter. 2. Chapter. 1. Chapter. 5.2 销售数据统计汇总与图表制作. 本例在操作时，首先要按照日期对商品销售额进行汇总统计，计算出每天各种商品的销售总额；然后用. 186.
(13) Excel 在销售业绩与提成计算中的应用. 第5章. 设置条件格式化的方法，根据数字大小分别为它们设置了长度不等的数据条格式；最后，再制作用来反映每 日销售额变化趋势的折线图。具体操作步骤如下： （1）打开“商品销售与业绩提成”工作簿。. 图 5-23. 对商品销售数据按日期汇总并制作出每日销售额变化折线图的效果. （2）插入一个新的工作表，并命名为“按日期统计销售额”，然后在其中按如图 5-24 所示格式， 建立按日期汇总商品销售额的表格框架，其中：“日期”一列在 A3 单元格输入“2009-3-1”之后，后. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. 1. c. Chapter. 面的通过拖动即可得到；另外，通过自定义数字格式的方法，将“日期”一列中的日期都显示为 “2009-03-01”的样式；销售额区域设置为“货币型”的格式。. 2 3 4 5 6 7 8. 按日期汇总销售额的表格框架. 9. 图 5-24. （3）在 B3 单元格中输入如下公式，然后按下 Ctrl+Shift+Enter 组合键，按照数组公式输入。之后，在 187.
(14) B3 单元格中，将会统计汇总出“2009 年 3 月 1 日”的商品销售总额。 =SUM(IF(本月销售记录!$A$3:$A$100=A3,本月销售记录!$J$3:$J$100)) （4）选取 B3 单元格，向下拖动复制公式到 B33 单元格，获取其他日期的销售额。 （5）在 B34 单元格中输入公式“=SUM(B3:B33)”，计算 3 月份销售额的合计值。 （6）选取 B3:B33 单元格区域，单击“开始”→“样式”→“条件格式化”下边的箭头，从弹出的 下拉列表中选择“数据条”选项，然后再从随后展开的类型列表中，选择“浅蓝色数据条”按钮，如图 5-25 所示，为每日销售额数据进行条件格式化设置。. 为每日销售额设置数据条的大小标记. c. 至此，按日期汇总商品销售额的数据表已经制作完毕，效果如图 5-26 所示。. c c c c c c c c. 图 5-26. 按日期汇总商品销售额的数据表. （7）下面开始制作每日销售额变化趋势折线图。操作时，首先选取 A2:B33 单元格区域，然后单. 9. Chapter. 8. Chapter. 7. Chapter. 6. Chapter. 5. Chapter. 4. Chapter. 3. Chapter. 2. Chapter. 1. Chapter. 图 5-25. 击“插入”→“图表”→“折线图”命令按钮下面的箭头，弹出折线图类型的列表，在其中选择第一. 188.
(15) Excel 在销售业绩与提成计算中的应用. 第5章. 个“折线图”按钮后，一个反映每天销售额变化情况的折线图就制作出来；最后，再对其大小和位置 做适当调整，在工作表中的初始效果如图 5-27 所示。. 图 5-27. 制作出条形图的初始样式. （8）对上面制作出的折线图，按照以下的操作，对其显示效果再进行优化设置。 . 在图例上单击右键，从弹出的快捷菜单中单击“删除”选项，取消图例的显示。. . 在横坐标轴上单击右键，从快捷菜单中单击“设置坐标轴格式”选项，然后从弹出的“设置坐标 轴格式”对话框中，选取“数字”选项卡，在数字“类型”列表中，选取“dd”选项，如图 5-28 所示（这样设置之后，横坐标上日期只按两位格式显示其中的天，日期显示更清晰，如“2009-3-1” 将只显示“01” ） 。. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. 1. c. Chapter. 轴格式”对话框中，选取“坐标轴选项”选项卡，在“显示单位”下拉列表中，选取“10000” ，. c. 在纵坐标轴上单击右键，从快捷菜单中单击“设置坐标轴格式”选项，然后从弹出的“设置坐标. Chapter. . 如图 5-29 所示（这样设置后，纵坐标上的数字单位将变为“×10000” （万） ，这样数字显示更清. 2. 晰，如 30000 将只显示 3） 。. 3 4 5 6 7 8 9. 图 5-28. 设置横坐标轴日期为“dd”格式. 图 5-29. 设置纵坐标轴数字单位为 10000. 189.
(16) . 在标题“销售额”上，先双击鼠标使之进入编辑状态，将文字修改为“2009 年 3 月份每日销售额 变化趋势图” ，并设置字体为 14 号大小，黑体；然后单击右键，在快捷菜单中单击“设置图表标 题格式”选项，从弹出的“设置图表标题格式”对话框中，设置为白色的“纯色填充”效果。. . 在图表区域上单击右键，从快捷菜单中单击“设置图表区域格式”选项，然后从弹出的“设 置图表区域格式”对话框中，选取“填充”选项卡，设置为灰色的“纯色填充”效果。. c c. 图 5-30. 商品销售每日折线图的最终制作效果. c. 5.2.2 按商品名称做销售统计并绘制条形图 【例 5-5】 请根据“本月销售记录”工作表中的销售记录，按照商品名称对销售额进行汇总统计，. c. 计算出各种商品的本月销售额，然后确定销售额排序的前三名；最后制作用来反映各商品销售额对比. c. 的条形图。要求制作好的数据表格与图表效果如图 5-31 所示。. c c c c. 9. Chapter. 8. Chapter. 7. Chapter. 6. Chapter. 5. Chapter. 4. Chapter. 3. Chapter. 2. Chapter. 1. Chapter. 经过以上的所有设置后，图 5-27 中的条形图就会变为如图 5-30 所示的最终效果。. 图 5-31 190. 按商品名称进行的销售额汇总统计以及商品销售条形图的制作效果.
(17) Excel 在销售业绩与提成计算中的应用. 第5章. 问题分析：表格中“商品名称”一列，可从“参数设置”数据表中复制；“销售额”可根据“本 月销售记录”中的销售数据，利用 SUMIF 函数条件求和得到；“排名”可以使用 RANK 函数，前三 名的标注可通过条件格式化设置；图表制作中，对图表区域、图表标题、系列数据显示，以及纵、横 坐标轴格式需要做一定的优化设置。本例在操作时，首先要按照商品名称对商品销售额进行汇总统计； 然后进行排名计算，并利用条件格式化标示出前三名；最后，再制作用来反映各种商品销售额对比的 条形图。具体操作步骤如下： （1）打开“商品销售与业绩提成”工作簿。 （2）插入一个新的工作表，并命名为“按商品统计销售额”，然后在其中按如图 5-32 所示样式， 建立按商品名称统计销售额的表格框架，其中的各个商品名称，从“参数设置”数据表中复制得到； C15 单元格填充为黑色，表示该单元格无意义。 （3）在 B3 单元格中输入如下公式，然后按下回车键，B3 单元格中汇总核算到第一种商品“JVC GZ-HD7AC”的本月销售额。 =SUMIF(本月销售记录!D:D,A3,本月销售记录!J:J) （4）选取 B3 单元格，向下拖动填充柄，复制其公式一直到 B14 单元格，获取其他商品的本月销 售额。 （5）在 B15 中输入如下公式，计算所有商品的合计销售额。 =SUM(B3:B14) （6）在 C3 单元格中输入如下公式，然后按下回车键，B3 单元格中 “JVC GZ-HD7AC”的本月 销售额排名确定出来，本月为第 1 名。. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. 1. c. Chapter. =RANK(B3,$B$3:$B$14) （7）选取 C3 单元格向下拖动填充柄，复制其公式一直到 C14 单元格，获取其他商品的本月销售. 2. 额排名。 （8）选取 C3:C14 单元格区域，为它们进行条件格式化设置，规则为：当单元格中数据小于等于. 3. 3 时，设置单元格为红色填充效果，字体为加粗倾斜。 至此，按商品名称统计销售额的数据表已经制作完毕，效果如图 5-33 所示。. 4 5 6 7 8 9. 图 5-32. 按商品名称统计销售额的表格框架. 图 5-33. 按商品名称统计销售额的数据表. 191.
(18) （9）下面制作各种商品销售额数据对比情况的条形图。操作时，首先选取 A2:B14 单元格区域； 然后单击“插入”→“图表”→“条形图”命令按钮下面的箭头，从弹出的条形图类型列表中，单击 “二维簇状条形图”按钮，制作出一个反映各商品销售额的条形图；最后对其大小和位置，根据需要 适当调整，在工作表中的初始效果如图 5-34 所示。. 图 5-34. 制作出条形图的初始效果. . 在图例上单击右键，从弹出的快捷菜单中单击“删除”选项，取消图例的显示。. . 在横坐标轴上单击右键，从快捷菜单中单击“设置坐标轴格式”选项，然后从弹出的“设置坐标. c. 轴格式”对话框中，选取“数字”选项卡，在数字“类别”列表中，将数字类别由原来默认设置. c. 的“会计”格式，改为“常规”类型。 . c. 单击“设置图表标题格式”选项，从弹出的“设置图表标题格式”对话框中，设置填充为“纹. c. . c. 显示） ，颜色设置为由蓝色到黄色的渐变。. c. 在组成条形图的任意颜色条上单击右键，从快捷菜单中单击“设置数据标签格式”选项，然 后从弹出的“设置数据标签格式”对话框中，选取“标签选项”选项卡，如图 5-36 所示，将. 6. Chapter. . c. 其中的“值”和“数据标签外”两个选项设置为选中状态，而其余的设置为非选中状态。 经过以上的所有设置后，图 5-34 中的条形图已经变为如图 5-37 所示的最终效果。. 7. Chapter. 在图表区域上单击右键，从快捷菜单中单击“设置图表区域格式”选项，然后从弹出的“设置图 表区域格式”对话框中，选取“填充”选项卡，设置为“渐变填充”效果（可参见图 5-35 中的. 5. Chapter. 4. Chapter. 理”效果，如图 5-35 所示。. c. 5.2.3 按销售员和销售日期交叉统计销售额. c. 【例 5-6】 请根据“本月销售记录”工作表中的销售记录，对销售员和销售日期进行双向交叉统计 销售额，也就是汇总出每个销售员对应 3 月份期间每天的销售额合计数据，并对每个销售员的销售额和每. 9. Chapter. 8. Chapter. 在标题“销售额”上，先双击鼠标使之进入编辑状态，将文字调整为“2009 年 3 月份商品销 售条形图”，并设置字号为 20 号，华文琥珀字体，加粗效果；然后单击右键，在快捷菜单中. 3. Chapter. 2. Chapter. 1. Chapter. （10）对上面制作出的二维簇状条形图，按照以下操作，对其显示效果进行设置。. 天的销售额进行汇总，最终的制作效果如图 5-38 所示。 192.
(19) Excel 在销售业绩与提成计算中的应用. 图 5-35. 设置图表标题为“纹理”填充效果. 图 5-36. 第5章. 将“值”设置到数据标签外面. 2009年3 月 份商 品 销 售 条 形 图 56160. 索尼 HDR-FX7E. 75660. 索尼 HDR-FX1E. 85100. 松下 HDC-SD5GK. 129600. 松下 AG-DVC33. 70800. 松下 AG-DVC63. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. 1. c. 72900. 日立 DZ-GX20E. Chapter. 55355. 日立 DZ-GX3200E. 36480. 日立 DZ-HS303SW. 2. 132040. JVC GZ-MG575AC. 160080. JVC GZ-HD3AC. 92000. JVC GZ-MG555AC. 0. 100000. 150000. 200000. 250000. 4. 图 5-37. 50000. 3. 227360. JVC GZ-HD7AC. 商品销售条形图的最终制作效果. 5. 问题分析：观察图 5-38 所示的表格样式，有些读者可能觉得该表很像一个数据透视表。不错，本例中 的要求，其实利用数据透视表可方便地完成（关于数据透视表的操作以及应用，本书第 4 章中已经有详细. 6. 的介绍）。但是，在本例的操作实践中，不用数据透视表工具，而是采用函数的方法来解决，这样可使读 者更灵活地掌握相关函数的应用。. 7. 在如图 5-38 所示的交叉数据汇总中，在 B3 单元格中用 SUMPRODUCT 函数构造一个公式，根据“本 月销售记录”表中的原始数据，巧妙地设置对单元格的混合引用和绝对引用，然后向下、向右拖动填充柄，. 8. 即可通过快速复制公式而得到。操作步骤如下： （1）打开“商品销售与业绩提成”工作簿。. 9. （2）插入一个新的工作表，并命名为“按销售员和日期统计销售额”，然后在其中按如图 5-39 所 示格式，建立按销售员和销售日期交叉统计销售额的表格框架，其中：. 193.
(20) c. 按销售员和销售日期交叉统计销售额数据表效果. c c c c. . c c. 8. Chapter. “销售日期”一列的输入方法，与前面“按日期统计销售额”工作表的操作方法一样，也是 B2:I2 单元格区域中的销售员姓名，可以通过复制前面“参数设置”数据表中的销售员姓名， 然后再通过“选择性粘贴”中的“转置”而得到。. 9. Chapter. 销售员和日期交叉统计销售额的表格框架. 需要采用序列输入法输入，并设置为标准的长日期格式。. 7. Chapter. 6. c. 图 5-39. c. Chapter. 5. Chapter. 4. Chapter. 3. Chapter. 2. Chapter. 1. Chapter. 图 5-38. . 最下面一行和最右边一列都添加上浅黄色的填充效果，以便与其他行列区分。. . 将表格中整个 B3:J34 单元格区域的数字格式，都设置为“货币型”格式。. . 对整个表格的标题设置为跨行居中的效果，并适当地设置其字体和字号。. （3）适当调整表格的行高、列宽，以及表格的显示比例，使表格在屏幕上全面显示。. 194.
(21) Excel 在销售业绩与提成计算中的应用. 第5章. （4）下面计算图 5-39 中销售员的每天销售数据，通过利用 SUMPRODUCT 函数的汇总功能即可 实现。操作时，首先将光标定位到 B3 单元格，然后输入如下公式，最后按下回车键确认，获取第一个 销售员在 2009 年 3 月 1 日的汇总销售额。 =SUMPRODUCT((本月销售记录!$A$3:$A$100=$A3)*(本月销售记录!$B$3:$B$100=B$2)*(本月销售 记录!$J$3:$J$100)). 对于本步骤中的公式，关键点有三个：第一，要熟悉单元格不同引用方式的作用；第二，要熟悉 SUMPRODUCT 函数的功能与应用规则；第三，要知道其中“*”的作用。如果读者对该公式的理解感觉有困 难，可以参阅本书第 1 章基础知识中介绍单元格引用部分所举的“混合引用”的应用实例，以及介绍数据汇 总部分所举的 SUMPRODUCT 函数的应用实例，特别是实例中对公式和函数的详细说明部分。限于篇幅，此 处不再赘述。. （5）选取 B3 单元格，向下拖动填充柄，复制公式一直到 B33 单元格，获取第一个销售员每天的 汇总销售数据。 （6）选取 B3:B33 单元格，向右拖动填充柄，复制公式一直到 I3:I33 单元格区域，获取所有销售 员每天的汇总销售数据。 （7）在 B34 单元格中输入公式“=SUM(B3:B33)”，然后向右拖动填充柄，将公式一直复制到 I34 单元格，获得每个销售员 3 月份的合计销售额。. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. 1. c. 获得 3 月份每天的总计销售额，以及全月的总计销售额数字。. Chapter. （8）在 J3 单元格中输入公式“=SUM(B3:I3)”，然后向下拖动，将公式一直复制到 J34 单元格，. 2. 至此，按照销售员和日期交叉统计销售额的表格制作完毕，效果如图 5-38 所示。其中显示为“-” 说明销售员对应当天没有销售记录的情况，也就是结果为 0。. 3 4. 5.3 销售员本月业绩与提成的计算. 5. 5.3.1 确定本月销售员的业绩排行榜. 6. 【例 5-7】 本例的设计目标如下：首先对 3 月份各销售员的销售额进行汇总并排名；然后与 1～. 7. 2 月销售数据合计，计算出每个人的第一季度累计销售额（为后面季度销售奖的评比做数据准备）； 最后，还制作了反应各个销售员 3 月份以及第一季度销售额对比分析的一个双轴组合图表，从中既可. 8. 以看到 3 月份的销售额对比，也可以看到第一季度的累计销售额对比。请按照以上设计目标，制作出 如图 5-40 所示的数据表格与图表效果。. 9. 问题分析：该表中“3 月份销售额”的获得，可使用 SUMIF 函数对“本月销售记录”工作表中的 销售记录条件汇总而得到；“3 月份销售排名”利用 RANK 函数确定；“1-2 月累计销售额”可以链接 195.
(22) 引用“参数设置”工作表中的相应数字；“第一季度累计销售额”通过求和公式即可得到；排序表通 过复制原表，然后进行排序操作即可得到。另外，图 5-40 中的图表是一种双轴组合图表，应注意其操 作方法与技巧。下面分三个阶段介绍本例的操作。. 图 5-40. 销售员业绩排行榜以及相关数据的对比分析效果. 1．建立销售员业绩排行榜的表格框架. c. （1）插入一个新的工作表，并命名为“销售员业绩排行”。 （2）按如图 5-41 所示格式，建立“2009 年 3 月公司销售员业绩排行”的表格框架，其中销售员. c. 编号和姓名从前面复制得到，“备注”用来输入特定信息（表中 G5 单元格输入“今年 2 月新进人员”，. c. 说明该人因加入公司较晚，所以季度累计额偏低，有情可原）。. c c c. 图 5-41. 销售员业绩排行榜的表格框架. c. （3）为上面表格的 C3:F10 单元格区域添加浅绿色的填充色，准备在其中输入公式。. c. 2．计算销售员业绩排行表的相关项目 下面对图 5-41 中表格的相关项目进行公式计算。操作步骤如下： c. （1）3 月份销售额的计算。选取 C3 单元格，在其中输入如下公式，按下回车键确认公式后，. 9. Chapter. 8. Chapter. 7. Chapter. 6. Chapter. 5. Chapter. 4. Chapter. 3. Chapter. 2. Chapter. 1. Chapter. 本例的首要工作，是建立销售员业绩排行榜的表格框架。操作步骤如下：. 获取第一个销售员的 3 月份销售额；然后获取其他销售员 3 月份的销售额。 =SUMIF(本月销售记录!$B$3:$B$100,B3,本月销售记录!$J$3:$J$100) 196.
(23) Excel 在销售业绩与提成计算中的应用. 第5章. （2）3 月销售排名的确定。选取 D3 单元格，输入公式“=RANK(C3,$C$3:$C$10)”，按下回车 键确认公式后，获取第一个销售员的 3 月销售排名；然后，再选取 D3 单元格，并向下拖动填充柄， 复制公式一直到 D10 单元格，获取其他销售员的 3 月销售排名。 （3）获取 1～2 月的累计销售额。选取 E3 单元格，输入公式“=参数设置!H3”，按回车键确认 后，从“参数设置”表中获取第一个销售员 1～2 月的累计销售额；然后再选取 E3 单元格，并向下拖 动填充柄，复制公式一直到 E10 单元格，获取其他销售员 1～2 月的累计销售额。 （4）计算第一季度的累计销售额。选取 F3 单元格，在其中输入如下公式“=E3+C3”，按回车键 确认后，计算第一个销售员第一季度的累计销售额；然后选取 F3 单元格，并向下拖动填充柄，复制公 式一直到 F10 单元格，获取其他销售员第一季度的累计销售额。 至此，销售员业绩排行榜表格的所有项目已经计算出来，效果如图 5-42 所示。. 图 5-42. 已经计算出结果的销售员业绩排行榜表格. 3．制作销售额对比分析的双轴组合图表. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. 1. c. Chapter. 图 5-40 中的双轴组合图表，起到了两个作用，不仅用柱状图能按照由高到低的顺序显示各个销售. 2. 员 3 月份的销售额对比，而且还用折线图显示出了第一季度各个销售员的累计销售额对比；另外，由 于两类数据的大小相差较大，该图表还运用了双纵坐标轴的格式。. 3. 其操作步骤如下： （1）将上面表格中的“姓名” 、“3 月份销售额”、 “3 月销售排名”、“第一季度累计销售额”等数. 4. 据区域，采用“选择性粘贴”→“粘贴值”的方法，复制到原来表格下方。 （2）按照“3 月销售排名”字段，对复制后的表格排序；并在上面添加标题“按排名排序后的三. 5. 月份销售额以及一季度累计销售额”。最后制作的排序表格效果如图 5-43 所示。 （3）按下 Ctrl 键，选取 A15:B23 以及 D15:D23 单元格区域，然后单击“插入”→“图表”→“柱. 6. 形图”命令按钮下面的箭头，从出现的图表类型列表中，选择其中的第一种类型“二维簇状柱形图”， 制作出一个柱形图，调整位置后其效果如图 5-44 所示。 . 7. （4）对上面制作的柱形图，按照以下操作，调整图例位置，将其设置为组合图表。 在图例上单击右键，从快捷菜单中选择“设计图例格式”菜单项，然后在弹出的“设置图例. 8. 格式”对话框的“图例选项”选项卡中，将图例位置设置为“底部”，并将“显示图例，但不 与图表重叠”复选框选中，如图 5-45 所示。 在图表的“第一季度累计销售额”系列的某个柱子（数据点）上单击右键，从快捷菜单中单. 9. . 击“更改系列图表类型”选项，从弹出的“更改图表类型”对话框中，如图 5-46 所示，选择 197.
(24) 第一种折线图，然后单击“确定”按钮，将“第一季度累计销售额”系列的图表类型调整为 “折线图”类型。. c. 对原表格某些字段“粘贴值”后新制作的排序表格. c c c c c c. 图 5-44. c. （5）在如图 5-47 所示的组合图表中，由于两个数据系列的数字差别比较大，所以柱形图的显示 c. 比较“小”，下面的操作将为“第一季度累计销售额”再设置一个次纵坐标轴，并设置与主纵坐标轴. 9. Chapter. 制作出柱形图及其在工作表中的初始样式. 经过以上操作，上面的柱形图变成了“柱形-折线”组合图表，效果如图 5-47 所示。. 8. Chapter. 7. Chapter. 6. Chapter. 5. Chapter. 4. Chapter. 3. Chapter. 2. Chapter. 1. Chapter. 图 5-43. 不同的刻度，以便解决上面的问题。操作方法如下：. 198.
(25) Excel 在销售业绩与提成计算中的应用. 图 5-45 . 将图例设置在“底部”. 图 5-46. 第5章. 将第一季度销售额设置为“折线图”. 在折线图上单击右键，从快捷菜单中单击“设置数据系列格式”选项，然后从弹出的“设置 数据系列格式”对话框中，选取“系列选项”选项卡，设置系列绘制在“次坐标轴”，如图 5-48 所示。. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. 1. c. Chapter. 2 3 4 5 6. . “柱形-折线”组合图表. 图 5-48. 将系列绘制到“次坐标轴”. 7. 图 5-47. 在“设置数据系列格式”对话框中，继续再选取“数据标记选项”选项卡，设置数据标记为 8. “内置” ，并从类型中选择“ ”形状，如图 5-49 所示。 经过以上的所有设置操作之后，坐标轴“柱形-折线”组合图表制作完毕，效果如图 5-50 所示。从. 9. 图中可以看出，本月销售排行情况与第一季度的累计销售排行不是很一致。. 199.
(26) 250000. 1000000. 200000. 800000. 150000. 600000. 100000. 400000. 50000. 200000 0 殷高贵 高新华 周勤政 刘 华 杨小铮 于小慧 潘利红 罗铭娅. 0. 3月份销售额 第一季度累计销售额 图 5-49. 将数据标记设置为“ ”形状. 图 5-50. 双坐标轴“柱形-折线”组合图表效果. 5.3.2 按销售记录计算每笔交易的提成 【例 5-8】 请根据“本月销售记录”工作表中销售员的每笔销售记录，按照事先确定的提成方法， 依据销售状态和销售折扣，合理确定提成比例；然后根据提成比例，计算每笔销售记录的提成金额。. c c c c c c. 图 5-51. 计算出销售员提成数据的表格效果. c. 问题分析：该表中 A～J 列部分，可从“本月销售记录”表中复制得到，而 K 列和 L 列才是本节的主要 “劳动成果”，其中：K 列中“提成比例”，可根据 E 列的“销售状态”和 H 列的“折扣率”的内容，依据. c. “参数设置”表中的提成比例方法来确定；提成金额是成交金额与提成比例的乘积。另外，要注意 K 列和 L 列数字格式的设置。操作步骤如下：. c. （1）打开“商品销售与业绩提成”工作簿。 （2）插入一个新的工作表，并命名为“提成计算”；然后将“本月销售记录”表中的数据，全部. 9. Chapter. 8. Chapter. 7. Chapter. 6. Chapter. 5. Chapter. 4. Chapter. 3. Chapter. 2. Chapter. 1. Chapter. 要求最后制作好的数据表格，效果如图 5-51 所示。. 复制到“提成计算”工作表中。 200.
(27) Excel 在销售业绩与提成计算中的应用. 第5章. （3）在复制过来的表格右侧增加“提成比例”和“提成金额”两个字段。 （4）设置“提成比例”一列的数字格式为带两位小数的百分比格式，“提成金额”一列的数字格 式为“会计专用”格式；另外两列中的单元格都设置为一定颜色的填充效果。 （5）将表格标题换为“清莹摄像机专卖店 2009 年 3 月业务员销售提成”，并设置为整体跨行居中效 果。经过以上操作，最终建立的销售员提成计算数据表如图 5-52 所示。. 图 5-52. 销售员提成计算数据表的格式. 下面进行“提成比例”的公式确定，需要使用 IF 函数的嵌套应用。 （6）进行“提成比例”的计算。将光标定位到“提成计算”工作表的 K3 单元格中，在编辑栏中 输入如下公式（或者输完“=”后，然后用鼠标去点选“参数设置”工作表中的相关单元格引用），然. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. 1. c. Chapter. 后按下回车键，K3 单元格中得到第一笔销售记录的提成比例。最后选取 K3 单元格，向下拖动填充柄， 将公式一直复制到 K100 单元格，这样将得到下面所有销售记录对应的提成比例。. 2. =IF(E3="正常销售",参数设置!$L$3,IF(E3="新品推广",参数设置!$L$7,IF(E3="促销商品 ",IF(H3<0.01,参数设置!$L$4,IF(H3<0.03,参数设置!$L$5,参数设置!$L$6))))). 3 4. 上面公式中，没有直接输入“参数设置”表中提成比例的对应数字（3%、2%、1.5%、1%、4%），而是绝 对引用了它们所在的单元格（参数设置!$L$3、参数设置!$L$4、参数设置!$L$5、参数设置!$L$6、参数设. 5. 置!$L$7）；这样的目的是保证提成比例的数字可进行动态维护。例如，参数设置!$L$3 中的 3%若调整为了 5%， 此处就不必再修改公式了。. 6. （7）进行提成金额的计算。操作时，先将光标定位到 L3 单元格中，输入公式“=J3*K3”，然后. 7. 按下回车键，L3 单元格中得到第一笔销售记录的提成金额；最后，再次选取 L3 单元格，向下拖动填 充柄，将公式一直复制到 L100 单元格，这样将得到下面所有销售记录对应的提成金额。. 8 9. 这里的“提成金额”计算，也可以使用数组公式，且效率更高，请读者考虑。. 201.
(28) 经过以上所有操作，“提成计算”工作表制作出来，最终效果如图 5-51 所示。. 5.4 销售员的提成汇总与图表分析 【例 5-9】本例的设计目标为：对销售员的提成金额进行汇总核算，求出各个销售员的提成合计； 然后，根据提成合计来制作饼图和圆锥图，通过图表对比分析各人的销售提成数量。请按照这个设计. c c c. 图 5-53. c c. 两种图表，对它们的显示位置进行了适当调整，并最后进行组合处理。. c. 下面根据本例的设计目标，分成几个小节的内容，介绍该实例的操作方法。. 5.4.1 销售员提成核算表的建立. c. 本例首要工作是核算销售员的提成合计，以便作为制作图表的来源。操作步骤如下： （1）插入一个新的工作表，并命名为“提成核算与分析”。. c. （2）按如图 5-54 所示格式，建立销售员提成核算表的框架，销售员姓名从前面复制。. 8. Chapter. 7. Chapter. 6. Chapter. 5. Chapter. 人提成数据进行条件汇总而得到的；另外，为了显示的美观和对比更清晰，本节制作了饼图和棱锥图. c. （3）将光标定位到 B3 单元格中。 （4）在编辑栏中输入如下公式（也可以开始只输入“=”，对于后面公式中跨工作表的单元格引. 9. Chapter. 销售员提成核算与数据分析显示效果. 问题分析：该表中“提成合计”的数据汇总，是使用了 SUMIF 函数对“提成计算”工作表中的各. 4. Chapter. 3. Chapter. 2. Chapter. 1. Chapter. 目标，制作出如图 5-53 所示的数据表格与图表效果。. 用，可以通过点选“提成计算”工作表中的相应单元格来得到，这样可免去输入的麻烦），然后按下. 202.
(29) Excel 在销售业绩与提成计算中的应用. 第5章. 回车键，B3 单元格中汇总核算到第一个销售员的提成合计金额。 =SUMIF(提成计算!$D$3:$D$100,A3,提成计算!$N$3:$N$100) （5）选取 B3 单元格，向下拖动复制到 B10 单元格，获取其他人员的提成合计金额。 （6）在 B11 单元格中输入公式“=SUM(B3:B10)”，计算所有人员提成金额的合计。 至此，所有销售人员的提成合计以及提成的总金额汇总出来，如图 5-55 所示。. 图 5-54. 销售员提成核算表格框架. 图 5-55. 已经计算出结果的销售员提成核算表. 5.4.2 制作各销售员提成占总提成比例的饼图 利用饼图，公司本月全部销售提成中，各个销售人员的所占比例可以非常清晰地表示出来，并能 标注上各自所占百分比。操作步骤如下： （1）选取“提成核算与分析”工作表的 A2:B10 单元格区域。. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. 1. c. Chapter. （2）单击“插入”→“图表”→“饼图”命令按钮下面的箭头，弹出如图 5-56 所示的饼图类型 列表。. 2 3 4 5 6 7. 图 5-56. 选择数据后单击“分离型三维饼图”按钮 8. （3）单击图 5-56 中的“分离型三维饼图”按钮后，制作出一个分离型三维饼图，然后对其大小. 9. 和位置根据需要进行适当的调整，使其效果如图 5-57 所示。 （4）对上面制作的分离型三维饼图，按照以下操作，对其显示效果进行设置。. 203.
(30) 图 5-57 . 制作出分离型三维饼图的初始样式. 在标题“提成合计”上单击右键，从弹出的快捷菜单中单击“删除”选项，取消图表中标题 文字的显示。. . 在图例上单击右键，从弹出的快捷菜单中单击“删除”选项，取消图例的显示。. . 在图表区域上单击右键，从快捷菜单中单击“设置图表区域格式”选项，然后从弹出的“设 置图表区域格式”对话框中，分别选取“填充”和“边框颜色”选项卡，分别设置为“无填 充”和“无线条”，也就是设置图表所在区域取消填充色和边框颜色的显示效果。. . 在组成饼图的任意颜色块上单击右键，从快捷菜单中单击“设置数据标签格式”选项，然后 从弹出的“设置数据标签格式”对话框中，选取“标签选项”选项卡，如图 5-58 所示，将其. c. 而其余保持为非选中状态。. c c c c c c c c. 9. Chapter. 8. Chapter. 7. Chapter. 6. Chapter. 5. Chapter. 4. Chapter. 3. Chapter. 2. Chapter. 1. Chapter. 中的 “类别名称” 、 “百分比”、 “显示引导线”和“数据标签外”四个复选框设置为选中状态，. 图 5-58. 204. “设置数据标签格式”对话框中的“标签选项”选项卡.
(31) Excel 在销售业绩与提成计算中的应用. 第5章. 经过以上的所有设置操作之后，图 5-57 中的饼图显示为如图 5-59 所示的最终效果。. 图 5-59. 分离型三维饼图设置的最终显示效果. 5.4.3 制作销售员提成数据对比分析的棱锥图 利用棱锥图中锥体大小，可对各个销售员的提成数据进行对比分析。操作步骤如下： （1）选取“提成核算与分析”工作表的 A2:B10 单元格区域。 （2）单击“插入”→“图表”功能区右下角的“创建图表”折叠按钮（图 5-60 中所标示的小按 钮），弹出如图 5-60 所示的“插入图表”对话框，其中列出了所有图表类型。. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. 1. c. Chapter. 2 3 4 5. 选择数据后单击“簇状棱锥图”按钮. 6. 图 5-60. 制作出一个簇状棱锥图，对其大小和位置适当调整，显示效果如图 5-61 所示。. 8. 5.4.4 调整棱锥图并使之与饼图组合成一体. 7. （3）选取如图 5-60 所示“柱形图”列表区中的“簇状棱锥图”按钮后，单击“确定”按钮，. 9. 下面对如图 5-61 所示的簇状棱锥图进行格式的美化设置，并使之与上面的饼图组合起来，使之看 起来更为一体化。操作步骤如下：. 205.
(32) （1）对如图 5-61 所示的棱锥图，按照以下操作，对其显示效果进行设置。 . 将标题文字由“提成合计”修改为“销售提成分析” ，并在上面单击右键，从快捷菜单中单击 “设置图表标题格式”选项，然后在弹出的“设置图表标题格式”对话框中，设置填充颜色 为“蓝色”；并选中“对齐方式”选项卡，设置水平对齐方式为“左对齐”，文字方向为“竖. c. 图 5-61. 制作出的簇状棱锥图初始样式. 图 5-62. 设置文字水平左对齐，方向竖排. c. . c c. 在横坐标轴上单击右键，从快捷菜单中单击“字体”选项，然后从弹出的“字体”对话框中，. c c. 图，效果如图 5-66 所示。. 6. Chapter. . （2）选取棱锥图，并向上拖动放大，使其大小一直延伸到工作表的第 1 行，覆盖住原来上方的饼. c. （3）在棱锥图的图表区（不要在棱锥系列数据上面）上单击右键，从快捷菜单中单击“置于底层” 下的“下移一层” 选项，让下面的饼图显示出来，效果如图 5-67 所示。. 7. Chapter. 在图例上单击右键，从弹出的快捷菜单中单击“删除”选项，取消图例的显示。. 经过以上的所有设置操作之后，图 5-61 中的棱锥图显示为如图 5-65 所示的效果。. c. （4）调整两个图表在屏幕上的相对显示位置，使之显示的效果更加美观。 （5）按下 Shift 键，分别选取两个图表，使之都成为选中状态；然后单击右键，从快捷菜单中选. 8. Chapter. . 选取“字体”选项卡，将大小设置为 12，如图 5-64 所示。. 5. Chapter. 4. Chapter. 3. Chapter. 式改变为“常规”类型，如图 5-63 所示。. c. 择“组合”子菜单下的“组合”选项，让两个图表组合起来。 经过以上操作，工作表显示效果如图 5-68 所示，然后再设置棱锥图的颜色为绿色渐变效果，将工. 9. Chapter. 在纵坐标轴上单击右键，从快捷菜单中单击“设置坐标轴格式”选项，然后从弹出的“设置坐标 轴格式”对话框中，选取“数字”选项卡，在数字“类别”列表中，将数字类别由原来的会计格. 2. Chapter. 1. Chapter. 排”样式，如图 5-62 所示。. 作表的网格线设置为“无”的状态，就得到了本节最开始如图 5-53 所示的最终效果。 206.
(33) Excel 在销售业绩与提成计算中的应用. 图 5-63. 设置纵坐标轴数字为“常规”格式. 图 5-64. 第5章. 设置横坐标轴字体大小为“12”. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. 1. c. Chapter. 2 3. 图 5-65. 棱锥图设置后的最终显示效果 4 5 6 7 8 9. 图 5-66. 将棱锥图拖动放大并覆盖住原来上方的饼图. 207.
(34) c. 调整图表的显示层次，使之均显示出来. c c c. 调整两个图表的适当位置并进行组合后的效果. c. 图 5-68. c. 5.5 季度销售奖评比及奖金核算. c. 【例 5-10】 本例的设计目标为：根据各个销售员第一季度的总销售额，按照事先制定的季度销 c. 售奖评定规则，评比出各类季度销售奖的获奖人员名单，并按照核算方法为他们核发奖金。请按照上 述目标，制作出如图 5-69 所示的数据表格。. c. 问题分析：该表的设计主要应用了数据链接、RANK 函数、IF 函数嵌套、单元格引用方式的确定、. 9. Chapter. 8. Chapter. 7. Chapter. 6. Chapter. 5. Chapter. 4. Chapter. 3. Chapter. 2. Chapter. 1. Chapter. 图 5-67. 公式运算、公式复制等知识点，其中：. 208.
(35) Excel 在销售业绩与提成计算中的应用. 图 5-69. 第5章. 季度销售奖评比及奖金核算的结果. . H2:J5 区域为季度奖评比和奖金核算办法。. . C3:C10 单元格区域与前面“销售员业绩排行”工作表的 F3:F10 单元格区域链接，用来获取 第一季度的累计总销售额。. . D3:D10 单元格区域利用 RANK 函数，排出季度销售排名。. . E3:E10 单元格区域是根据 D3:D10 单元格区域中的排名情况，并依据 H2:J4 单元格区域中的 评奖规则，确定出的季度奖获奖结果。. . F3:F10 单元格区域是根据 E3:E10 单元格区域中的获奖情况，并依据 I2:J5 单元格区域中的奖 金核算标准，求出的各类奖金对应的奖励金额。. 下面根据本实例的操作流程，分成四个小节的内容，介绍其具体的操作步骤。. 5.5.1 季度销售奖评比工作表框架的建立 首先，需要进行季度销售奖评比工作表框架的创建，操作步骤如下：. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. 1. c. Chapter. （1）插入一个新的工作表，并命名为“季度销售奖评比”。 （2）按照如图 5-70 所示的格式，建立整个表格的总体框架，并进行格式设置。. 2 3 4 5. 季度销售奖评比表格框架. 6. 图 5-70. （3）从前面的相关工作表，复制销售员的编号和姓名；并根据公司季度奖的评定规则和奖金核算. 7. 公式，填充“季度销售奖评比”表格的 H3:J5 单元格区域。 经过以上处理，得到输入了基本信息的“季度销售奖评比”表格框架，如图 5-71 所示。. 8. 5.5.2 通过链接获取第一季度累计销售额. 9. 图 5-71 所示的表中，第一季度累计销售额可以从前面“销售员业绩排行”工作表中复制过来。但 是，在那里对应数据是由公式计算得出，不能直接粘贴，只能利用“选择性粘贴”中的“粘贴值”方 209.
(36) 法，不过这种方法又无法保证图 5-71 中的数据与“销售员业绩排行”工作表中的数据同步，也就是说， 如果前面销售记录单中的数据，由于一种特殊的原因（比如退货、数据输入错误等）而发生了一定的 调整，则“销售员业绩排行”工作表中“第一季度累计销售额”会发生相应变化，但是图 5-71 中对应 数据不变（因为是“粘贴值”的原因）。. 图 5-71. 输入了销售员姓名和季度奖评比政策的表格框架. 为了解决以上问题，这里采用数据链接的方法，使图 5-71 中“季度销售奖评比”表的 C3:C10 区域，与 “销售员业绩排行”工作表的 F3:F10 区域链接，以便实现数据同步。 操作步骤如下： （1）将光标定位到“季度销售奖评比”工作表的 C3 单元格。 （2）在编辑栏中输入公式“=销售员业绩排行!F3”（或者输完“=”后，然后用鼠标去点选“销 售员业绩排行”工作表中的 F3 单元格），然后按下回车键，C3 单元格中获取到第一个销售员的第一 c. （3）选取 C3 单元格，向下拖动复制到 C10 单元格，获取其他人员的第一季度总销售额，结果如. c. 图 5-72 所示。. c c c. 通过数据链接获取各销售员第一季度总销售额. c. 图 5-72. c. 上面 C3 单元格中的公式“=销售员业绩排行!F3”，实现了本表格中 C3 单元格的数值始终与“销售员业绩. c. 排行”工作表中的 F3 单元格保持同步动态变化。. c. 5.5.3 利用公式确定季度奖的评比结果. 9. Chapter. 8. Chapter. 7. Chapter. 6. Chapter. 5. Chapter. 4. Chapter. 3. Chapter. 2. Chapter. 1. Chapter. 季度总销售额。. 季度奖评比结果需要通过公式确定，其中要用到 RANK 和 IF 函数。操作步骤如下： 210.
(37) Excel 在销售业绩与提成计算中的应用. 第5章. （1）将光标定位到“季度销售奖评比”工作表的 D3 单元格。 （2）在编辑栏中输入公式“=RANK(C3,$C$3:$C$10)”，然后按下回车键，D3 单元格中得到了第 一个销售员的第一季度销售额排名。 （3）选取 D3 单元格，向下拖动复制到 D10 单元格，获取其他人员第一季度的销售额排名。 （4）将光标定位到“季度销售奖评比”表的 E3 单元格。 （5）在编辑栏中输入公式“=IF(D3<=1,$I$3,IF(D3<=3,$I$4,IF(D3<=5,$I$5,"")))”，然后按下回车 键，E3 单元格中得到了第一个销售员的季度评奖结果。. 上面公式中，没有直接输入评奖结果文字（“卓越奖” 、“精英奖”、“优秀奖”） ，而是绝对引用了它们所在 的单元格（$I$3、$I$4、$I$5）；这样的目的是保证评奖结果文字可进行动态维护。例如“卓越奖”若改为“卓 越之星”，此处就不必修改公式。. （6）选取 E3 单元格，向下拖动复制到 E10 单元格，获取其他人员的季度评奖结果。 经过以上操作，最终结果如图 5-73 所示。. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. c. Chapter. 1. c. Chapter. 2. 利用公式确定季度奖的评比结果. 3. 图 5-73. 4. 5.5.4 利用公式核算获奖人员的奖励金额. 5. 获奖人员的奖励金额需要通过公式计算，其中用到嵌套 IF 函数。操作步骤如下： （1）将光标定位到“季度销售奖评比”工作表的 F3 单元格。. 6. （2）在编辑栏中输入如下公式，然后按下回车键，核算第一个销售员的奖励金额。 =IF(E3=$I$3,C3*$J$3,IF(E3=$I$4,C3*$J$4,IF(E3=$I$5,C3*$J$5,""))). 7 8. 上面公式中，绝对引用的使用目的，与上面求获奖结果的应用的目的一样。. 9. （3）选取 F3 单元格，向下拖动复制到 F10 单元格，获取其他销售员的奖励金额。 211.
(38) 9. c. Chapter. 8. c. Chapter. 7. c. Chapter. 6. c. Chapter. 5. c. Chapter. 4. c. Chapter. 3. c. Chapter. 2. c. Chapter. 1. c. Chapter. 经过以上操作，最终工作表显示效果如图 5-74 所示。. 图 5-74. 212 利用公式确定季度奖的评比结果.
(39) Excel 在销售业绩与提成计算中的应用. 213. 第.
(40)
Outline
相關文件
按行业及在职员工数目抽选。对于在职员工为 20 人或以上的店铺，以及场所总 数较少的分层会进行全面统计。. 统计结果推算
[r]
[r]
[r]
某项货物的单位价格是指该项货物的贸易货值与其货量之比。季度单位价格指数是计算当季各
某项货物的单位价格是指该项货物的贸易货值与其货量之比。季度单位价格指数是计算当季各
某项货物的单位价格是指该项货物的贸易货值与其货量之比。季度单位价格指数是计算当季各
指固定资产(包括新、旧及场所自产自用之固定资产)之购置减固定资产销售后之数值。固定
