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

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

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

3、”“单元格”菜单项，在弹出的对话框中选择“图案”选项卡，选择一种底纹颜色。8、 在对话框中单击“边框”选项卡，在“线条样式”中选择粗实线，单击“预置”中的“外边框”按钮，给表格加上外边框。9、 再在“线条样式”中选细虚线，单击“预置”中的“内部”按钮，给表格加上内边框，单击“确定”按钮。10、 单击“工具”“选项”菜单项，在弹出的“选项”对话框中单击“视图”选项卡，单击“网格线”复选框使其不选中，单击“确定”按钮。11、 双击标签“Sheet2”，将工作表重命名为“成员分配表”。12、 参照图3-4-2输入成员分配表的数据。图3-4-2 成员分配表任务2 合并计算提成工资分析或说明：合并计算功

4、能可以将每个单独的工作表中的数据合并计算到一个工作表中，也可以合并到工作簿中，以便能够更容易地对数据进行定期或不定期的更新汇总。本任务利用合并计算功能计算销售利润提成表中的提成工资。步骤： 1、 单击工作表标签切换到“销售利润提成表”工作表。2、 光标放在D4单元格，输入公式：“=C4*B4”，按回车键确认。3、 用鼠标左键按钮住D4单元格右下角的填充柄向下拖拉，计算其余人员的销售额。4、 光标放在K4单元格，输入公式：“=VLOOKUP(A4,成员分配表!$A$2:$C$11,2)”，按回车键确认。5、 用鼠标左键按钮住K4单元格右下角的填充柄向下拖拉，填入其余人员的职位。6、 单击工作表标

5、签切换到“成员分配表”工作表。7、 光标放在C2中，输入公式：“=IF(B2="组长",7.5%,6%)”，按回车键确认。8、 用鼠标左键按钮住C4单元格右下角的填充柄向下拖拉，计算其余人员的提成利润分配。9、 单击工作表标签切换到“销售利润提成表”工作表。10、 光标放在E4中，输入公式：“=J4*VLOOKUP(A4,成员分配表!$A$2:$C$11,3)”，按回车键确认。11、 用鼠标左键按钮住E4单元格右下角的填充柄向下拖拉，计算其余人员的提成利润。12、 选择F4：F13数据区域，单击“数据”“合并计算”菜单项。13、 在弹出的“合并计算”对话框中“函数”下拉列表

6、中选择“求和”选项，然后单击“引用位置”文本框右侧的按钮。14、 用鼠标选择H4：H13数据区域后返回“合并计算”对话框，单击“添加”按钮，将H4：H13数据区域添加到“所有引用位置”列表框中。15、 在“合并计算”对话框中再次单击“引用位置”文本框右侧的按钮，用鼠标选择E4：E13数据区域，单击“添加”按钮，将E4：E13数据区域添加到“所有引用位置”列表框中，如图3-4-3所示。16、 单击“确定”按钮，即可求出提成额，如图3-4-4所示。图3-4-3 合并计算图3-4-4 计算提成额任务3 用单变量求解利润最大化分析或说明：利用单变量求解功能可以在给定公式的前提下，通过调整可变单元格中的

7、数值来寻求目标单元格中的目标值。本任务使用单变量求解来实现提成利润的最大化，即当A产品的利润达到10000时售出的数量是多少。步骤： 1、 用鼠标右键单击工作表标签，插入一个新工作，重命名为“销售利润表”。2、 参照图3-4-5输入标题和相关数据，设置单元格的格式，隐藏工作表中的网格线。3、 光标放在E5单元格，输入公式：“=C5*D5”，按回车键确认。4、 用鼠标左键按住E5单元格的填充柄把公式复制到E6和E7单元格。5、 光标放在G5单元格，输入公式：“=F5*D5”，按回车键确认。6、 用鼠标左键按住G5单元格的填充柄把公式复制到G6和G7单元格。7、 光标放在H5单元格，输入公式：“=

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

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

10、单元格的总计值。6、 选择数据区域“A3：K3”，单击“数据”“筛选”“自动筛选”菜单项，此时在每个列标题的右下侧都会出现一个下拉箭头。7、 单击“提成额”右侧的箭头，在弹出的下拉列表中选择“自定义”选项，弹出“自定义自动筛选方式”对话框，如图3-4-9所示。图3-4-9 自定义自动筛选方式8、 在对话框中按图3-4-9所示进行设置，单击“确定”按钮，即可筛选出满足条件的记录，如图图3-4-10所示。图3-4-10 筛选结果9、 单击“数据”“筛选”“自动筛选”菜单项，取消自动筛选。10、 在第3行行号上单击鼠标右键，选择“插入”菜单项，在标题行上面插入一行空行。11、 选择数据区域“A4：K

11、4”，单击“格式”“单元格”菜单项，在弹出的对话框中选择“对齐”选项卡。12、 在“对齐”选项卡中的“文本控制”中去掉“自动换行”复选框前的勾号，单击“确定”按钮返回工作表中，将表格的各列列宽调整到合适的大小。13、 在数据区域“C18：E19”中输入要查询的条件，如图3-4-11所示。14、 把光标放在要进行筛选的数据区域中的任一单元格中，单击“数据”“筛选”“高级筛选”菜单项。图3-4-11设置筛选条件15、 在弹出的“高级筛选”对话框中按图3-4-12进行设置。16、 单击“确定”按钮后即在数据区域“A21：K26”显示筛选结果。图3-4-12高级筛选项目总结Excel具有强大的数据处理

12、与分析功能。本项目完成销售利润提成表的制作，通过使用合并计算功能计算提成工资。同时，为了更好地了解销售利润情况，利用单变量求解实现利润最大化，最后利用筛选功能对销售利润表进行比较与分析。拓展延伸1、vlookup函数的使用函数格式：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)其中：Lookup_value ：需要在表格第一列中查找的数值Table_array：两列或多列数据，可以使用对区域的引用或区域名称。 Col_index_num：为 table_array 中待返回的匹配值的列序号。Col_index_

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