Excel讲解及练习-制作销售利润提成表

1、-. z.工程四 制作销售利润提成表替换原工程四工程说明销售利润提成表是企业记录销售利润的表单，对销售利润进展记录是企业进展销售管理的重要组成局部，本工程完成销售利润提成表的制作并作相关计算。工程分析本工程要求建立销售利润提成表，通过使用合并计算功能计算提成工资。为了更好地了解销售利润情况，利用单变量求解实现利润最大化，最后利用筛选功能对销售利润表进展比拟与分析。任务1 建立销售利润提成表和成员分配表分析或说明：本任务是建立如图3-4-1所示的销售利润提成表和成员分配表。步骤： 启动E*cel应用程序，单击文件另存为菜单项，在弹出的另存为对话框中，输入文件名销售利润提成表，文件类型为.*ls。

2、图3-4-1 销售利润提成表双击标签Sheet1，将工作表重命名为销售利润提成表。在A1单元格输入销售利润提成表，选择A1：K1数据区域，单击工具栏上的居中并合并按钮，使标题居中显示。单击格式单元格菜单项，在弹出的对话框中选择字体选项卡，把标题文字设为宋体、16号。参照图3-4-1在相应单元格中输入、售出数量、单价等其余数据。选择C4：I14数据区域，单击格式单元格菜单项，在弹出的对话框中选择数字选项卡，在分类中选择货币项，设置小数位数为1。选择A3：K3数据区域，单击格式单元格菜单项，在弹出的对话框中选择图案选项卡，选择一种底纹颜色。在对话框中单击边框选项卡，在线条样式中选择粗实线，单击预置

3、中的外边框按钮，给表格加上外边框。再在线条样式中选细虚线，单击预置中的部按钮，给表格加上边框，单击确定按钮。单击工具选项菜单项，在弹出的选项对话框中单击视图选项卡，单击网格线复选框使其不选中，单击确定按钮。双击标签Sheet2，将工作表重命名为成员分配表。参照图3-4-2输入成员分配表的数据。图3-4-2 成员分配表任务2 合并计算提成工资分析或说明：合并计算功能可以将每个单独的工作表中的数据合并计算到一个工作表中，也可以合并到工作簿中，以便能够更容易地对数据进展定期或不定期的更新汇总。本任务利用合并计算功能计算销售利润提成表中的提成工资。步骤： 单击工作表标签切换到销售利润提成表工作表。光标

4、放在D4单元格，输入公式：=C4*B4”，按回车键确认。用鼠标左键按钮住D4单元格右下角的填充柄向下拖拉，计算其余人员的销售额。光标放在K4单元格，输入公式：=VLOOKUP(A4,成员分配表!$A$2:$C$11,2)，按回车键确认。用鼠标左键按钮住K4单元格右下角的填充柄向下拖拉，填入其余人员的职位。单击工作表标签切换到成员分配表工作表。光标放在C2中，输入公式：=IF(B2=组长,7.5%,6%)，按回车键确认。用鼠标左键按钮住C4单元格右下角的填充柄向下拖拉，计算其余人员的提成利润分配。单击工作表标签切换到销售利润提成表工作表。光标放在E4中，输入公式：=J4*VLOOKUP(A4,成

5、员分配表!$A$2:$C$11,3)，按回车键确认。用鼠标左键按钮住E4单元格右下角的填充柄向下拖拉，计算其余人员的提成利润。选择F4：F13数据区域，单击数据合并计算菜单项。在弹出的合并计算对话框中函数下拉列表中选择求和选项，然后单击引用位置文本框右侧的按钮。用鼠标选择H4：H13数据区域后返回合并计算对话框，单击添加按钮，将H4：H13数据区域添加到所有引用位置列表框中。在合并计算对话框中再次单击引用位置文本框右侧的按钮，用鼠标选择E4：E13数据区域，单击添加按钮，将E4：E13数据区域添加到所有引用位置列表框中，如图3-4-3所示。单击确定按钮，即可求出提成额，如图3-4-4所示。图3

6、-4-3 合并计算图3-4-4 计算提成额任务3 用单变量求解利润最大化分析或说明：利用单变量求解功能可以在给定公式的前提下，通过调整可变单元格中的数值来寻求目标单元格中的目标值。本任务使用单变量求解来实现提成利润的最大化，即当A产品的利润到达10000时售出的数量是多少。步骤： 用鼠标右键单击工作表标签，插入一个新工作，重命名为销售利润表。参照图3-4-5输入标题和相关数据，设置单元格的格式，隐藏工作表中的网格线。光标放在E5单元格，输入公式：=C5*D5”，按回车键确认。用鼠标左键按住E5单元格的填充柄把公式复制到E6和E7单元格。光标放在G5单元格，输入公式：=F5*D5”，按回车键确认

7、。用鼠标左键按住G5单元格的填充柄把公式复制到G6和G7单元格。光标放在H5单元格，输入公式：=E5-G5”，按回车键确认。图3-4-5 销售利润表用鼠标左键按住H5单元格的填充柄把公式复制到H6和H7单元格。光标放在H5，单击工具单变量求解菜单项。在弹出的单变量求解对话框的目标单元格文本框中输入$H$5”，在目标值文本框中输入10000，在可变单元格文本框中输入$D$5”，如图3-4-6所示。单击确定按钮后弹出对话框，显示对单元格H5进展单变量求解的状态信息，如图3-4-7所示。图3-4-6 单变量求解图3-4-7 单变量求解状态图3-4-8 单变量求解结果单击确定按钮后得到求解结果，如图3

8、-4-8所示。任务4 筛选销售利润表分析或说明：为了更好地了解利润情况，还需要对销售利润表进展比拟与分析。本任务用自定义筛选的方法筛选提成额为100150元的记录。用高级筛选销售数量大于15、销售额大于500，提成额大于100的销售记录。步骤： 单击工作表标签切换到销售利润提成表工作表。光标放在I4中，输入公式：=D4-F4”，按回键计算存入总计。用鼠标左键按住I5单元格的填充柄把公式复制到I6和I7单元格。光标放在B14中，单击工具栏上的自动求和按钮，系统自动对数据区域B4：B13”求和，按回车键确认。用鼠标左键按住B13右下角的填充柄向右拖拉，计算其余单元格的总计值。选择数据区域A3：K3

9、”，单击数据筛选自动筛选菜单项，此时在每个列标题的右下侧都会出现一个下拉箭头。单击提成额右侧的箭头，在弹出的下拉列表中选择自定义选项，弹出自定义自动筛选方式对话框，如图3-4-9所示。图3-4-9 自定义自动筛选方式在对话框中按图3-4-9所示进展设置，单击确定按钮，即可筛选出满足条件的记录，如图图3-4-10所示。图3-4-10 筛选结果单击数据筛选自动筛选菜单项，取消自动筛选。在第3行行号上单击鼠标右键，选择插入菜单项，在标题行上面插入一行空行。选择数据区域A4：K4”，单击格式单元格菜单项，在弹出的对话框中选择对齐选项卡。在对齐选项卡中的文本控制中去掉自动换行复选框前的勾号，单击确定按钮

10、返回工作表中，将表格的各列列宽调整到适宜的大小。在数据区域C18：E19”中输入要查询的条件，如图3-4-11所示。把光标放在要进展筛选的数据区域中的任一单元格中，单击数据筛选高级筛选菜单项。图3-4-11设置筛选条件在弹出的高级筛选对话框中按图3-4-12进展设置。单击确定按钮后即在数据区域A21：K26”显示筛选结果。图3-4-12高级筛选工程总结E*cel具有强大的数据处理与分析功能。本工程完成销售利润提成表的制作，通过使用合并计算功能计算提成工资。同时，为了更好地了解销售利润情况，利用单变量求解实现利润最大化，最后利用筛选功能对销售利润表进展比拟与分析。拓展延伸1、vlookup函数的

11、使用函数格式：VLOOKUP(lookup_value,table_array,col_inde*_num,range_lookup)其中：Lookup_value：需要在表格第一列中查找的数值Table_array：两列或多列数据，可以使用对区域的引用或区域名称。 Col_inde*_num：为 table_array 中待返回的匹配值的列序号。Col_inde*_num 为 1 时，返回 table_array 第一列中的数值；col_inde*_num 为 2，返回 table_array 第二列中的数值，以此类推。Range_lookup：为逻辑值，指定希望 VLOOKUP 查找准确的

12、匹配值还是近似匹配值：如果为 TRUE 或省略，则返回准确匹配值或近似匹配值。此时第一列中的值必须以升序排序；否则 VLOOKUP 可能无法返回正确的值可以事先对工作表按升序进展排序。如果为 FALSE，VLOOKUP 将只寻找准确匹配值。在此情况下，第一列的值不需要排序。例如，在工作表销售记录中已有各人的销售金额，如图3-4-13所示。现想在另一工作表Sheet1中输入一样的销售金额，并且要求该表的销售金额会根据销售记录中的数据自动更新，而且两个工作表中人员的次序并不完全不一致最后两人位置不同，如图3-4-14所示。图3-4-13销售记录表图3-4-14Sheet1表则只要在工作表Sheet1的D3单元格中输入公式：=VLOOKUP(A3,销售记录!$A$3:$