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

1、Excel讲解及练习制作销售利润提成表项目四 制作销售利润提成表（替换原项目四）项目说明销售利润提成表是企业记录销售利润的表单，对销售利润进行记录是企业进行销售管理的重要组成部分，本项目完成销售利润提成表的制作并作相关计算。项目分析本项目要求建立销售利润提成表，通过使用合并计算功能计算提成工资。为了更好地了解销售利润情况，利用单变量求解实现利润最大化，最后利用筛选功能对销售利润表进行比较与分析。任务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

3、、 选择A3：K3数据区域，单击“格式”“单元格”菜单项，在弹出的对话框中选择“图案”选项卡，选择一种底纹颜色。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单元格右下角的填充柄向下拖拉，

5、填入其余人员的职位。6、 单击工作表标签切换到“成员分配表”工作表。7、 光标放在C2中，输入公式：“=IF(B2=组长,%,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所示

9、。任务4 筛选销售利润表分析或说明：为了更好地了解利润情况，还需要对销售利润表进行比较与分析。本任务用自定义筛选的方法筛选提成额为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、 选择数据区域“

11、A4：K4”，单击“格式”“单元格”菜单项，在弹出的对话框中选择“对齐”选项卡。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_nu

13、m 为 1 时，返回 table_array 第一列中的数值；col_index_num 为 2，返回 table_array 第二列中的数值，以此类推。Range_lookup：为逻辑值，指定希望 VLOOKUP 查找精确的匹配值还是近似匹配值：如果为 TRUE 或省略，则返回精确匹配值或近似匹配值。此时第一列中的值必须以升序排序；否则 VLOOKUP 可能无法返回正确的值（可以事先对工作表按升序进行排序）。如果为 FALSE，VLOOKUP 将只寻找精确匹配值。在此情况下，第一列的值不需要排序。例如，在工作表“销售记录”中已有各人的销售金额，如图3-4-13所示。现想在另一工作表“Shee

14、t1”中输入相同的销售金额，并且要求该表的销售金额会根据“销售记录”中的数据自动更新，而且两个工作表中人员的次序并不完全不一致（最后两人位置不同），如图3-4-14所示。图3-4-13销售记录表图3-4-14Sheet1表则只要在工作表“Sheet1”的D3单元格中输入公式：“=VLOOKUP(A3,销售记录!$A$3:$D$8,4,FALSE)”，然后复制公式到其余单元格即可完成任务。与VLOOKUP 相类似可以用 HLOOKUP在行中查找相匹配的数据。自我练习1、在模块三项目一中的“学生成绩表.xls”工作簿中插入一张新工作表，并重命名为“学生基本信息表”，将“成绩”工作中的“学号”、“姓名”和“性别”列数据复制到“学生基本信息表”中，再添加“系科”、“总分”和“平均分”三个列标题。用VLOOKUP函数填充“学生基本信息表”中的“总分”和“平均分”数据。