项目10 水果超市销售数据分析（课件）-《计算机应用基础》同步教学（电子工业出版社）

项目10 水果超市销售数据分析 一、项目提出 二、项目分析 三、相关知识点 四、项目实施 五、总结与提高 项目提出 项目背景 小李在台州市的黄岩区、路桥区和椒江区各开了若干家水果超市连锁店 随着业务的不断扩大，对水果超市日常销售数据的管理也需要不断提高，他打算用Excel 2010来管理日常销售数据 项目提出 他制作了“销售记录”、“水果价格”和“水果店”三张工作表 项目提出 项目提出 需要解决以下问题 问题1 计算进价、售价、销售额和毛利润 问题4 设置数据验证和保护工作表 问题2 汇总各个区的销售额和毛利润，找出毛利润最大的水果名称 问题3 用“数据透视表”和“数据透视图”统计各区各水果的销售情况 5 利用VLOOKUP函数在“水果价格”工作表中查找各种水果的进价和售价 销售额＝售价×数量 毛利润＝(售价－进价)×数量 1、计算方法 项目分析 按“所在区”汇总销售额和毛利润，可以知道各个区的销售额和毛利润。 按“水果名称”汇总销售额和毛利润，再降序排列汇总后的毛利润，就可知道出毛利润最大的水果名称。 利用“嵌套分类汇总”可以统计各个区各水果店的销售额和毛利润。 2、数据分析 项目分析 使用“数据透视表”统计各个区的水果销售情况，再利用MAX函数找出各个区销售额最大的水果所对应的销售额，然后用VLOOKUP函数找出各个区销售额最大的水果所对应的水果名称。 使用“数据透视图”统计各个区各种水果的销售情况。 2、数据分析 项目分析 设置数据验证，使得在输入“所在区”、“水果店”和“水果名称”等字段的数据时，不必手工输入，只要在相应的下拉列表中选择即可。 对于“数量”字段，可以设置只允许输入大于0的整数，否则提示出错，并要求重新输入。 2、数据分析 项目分析 为了防止“销售记录”工作表中的“标题”行文字被选定和修改，可以锁定“标题”行文字所在的单元格区域。 启用“保护工作表”的功能后，这些被锁定的单元格区域就不能被选中和修改 2、数据分析 项目分析 3、操作流程图 项目分析 4、效果图 项目分析 4、效果图 项目分析 4、效果图 项目分析 4、效果图 项目分析 4、效果图 项目分析 4、效果图 项目分析 相关知识点 在工作表中，可以用列标志和行标志来引用单元格，也可以用自定义的名称来表示单元格、单元格区域。 1、单元格区域名称的定义 相关知识点 数据透视表是一种多维式表格，可快速合并和比较大量数据，它可以从不同角度对数据进行分析，以浓缩信息为决策者提供参考。 数据透视图是另一种数据表现形式，与数据透视表不同之处在于它可以选择适当的图形和色彩来描述数据的特性。 2、数据透视表和数据透视图 相关知识点 数据验证是一个可以在工作表中输入数据时产生提示信息的工具。它具有如下功能：给用户提供一个选择列表、限定输入内容的类型或大小、自定义设置等。 当用户所设计的表单或工作表要被其他人用来输入数据时，数据验证尤为有用。 3、数据验证 相关知识点 锁定单元格和保护工作表，是指将某块区域的一些单元格锁定并保护起来，保护后是无法进行选定、编辑和修改的，只有通过输入先前设置的正确密码后才可以重新开始选定、编辑和修改。 4、锁定单元格和保护工作表 相关知识点 主要功能：求出一组值中的最大值。 使用格式：MAX(number1,number2,…) 应用举例：输入公式“＝MAX(E44:J44,7,8,9,10)”，即可求出E44至J44单元格区域和数值7、8、9、10中的最大值。 MIN函数是求出一组值中的最小值，其使用方法和MAX函数类似。 ABS函数是返回某个数值的绝对值。 INT函数将数字向下舍入到最接近的整数。 5、MAX函数 项目实施 任务1、计算进价、售价、销售额和毛利润 1.定义单元格区域的名称 项目实施 任务1、计算进价、售价、销售额和毛利润 1.定义单元格区域的名称 项目实施 任务1、计算进价、售价、销售额和毛利润 2.利用VLOOKUP查找进价和售价 项目实施 任务1、计算进价、售价、销售额和毛利润 3.利用公式计算销售额和毛利润 销售额＝售价×数量 毛利润＝（售价－进价）×数量 设置进价、售价、销售额和毛利润的数字格式为“货币”，保留2位小数。 项目实施 任务1、计算进价、售价、销售额和毛利润 3.利用公式计算销售额和毛利润 项目实施 任务2、对销售额和毛利润进行分类汇总 1．创建3个“销售记录”工作表的副本 项目实施 任务2、对销售额和毛利润进行分类汇总 2．按“所在区”汇总销售额和毛利润 项目实施 任务2、对销售额和毛利润进行分类汇总 3．按“水果名称”汇总销售额和毛利润，并找出毛利润最大的水果名称 单击“排序和筛选”组中的“升序”按钮 ，对“水果名称”列中的数据进行升序排列 在“数据”选项卡中，单击“分级显示”组中的“分类汇总”按钮 ，打开“分类汇总”对话框，“分类字段”选择“水果名称”，“汇总方式”选择“求和”，“选定汇总项”选择“销售额”和“毛利润”字段。 单击分级显示符号 2 项目实施 任务2、对销售额和毛利润进行分类汇总 3．按“水果名称”汇总销售额和毛利润，并找出毛利润最大的水果名称 对“毛利润”列中的数据进行降序排列，找出毛利润最大的水果名称 项目实施 任务2、对销售额和毛利润进行分类汇总 4．利用“嵌套分类汇总”统计各个区各水果店的销售额和毛利润 项目实施 任务2、对销售额和毛利润进行分类汇总 4．利用“嵌套分类汇总”统计各个区各水果店的销售额和毛利润 项目实施 任务3、统计各区各水果的销售情况 1．使用数据透视表统计各个区各种水果的销售情况 项目实施 任务3、统计各区各水果的销售情况 1．使用数据透视表统计各个区各种水果的销售情况 项目实施 任务3、统计各区各水果的销售情况 2．利用MAX函数找出各个区销售额最大的水果所对应的销售额 在H4单元格中输入公式“＝B4”，拖动H4单元格的填充柄至J4单元格。 在H5单元格中输入公式“＝MAX(B5:B16)”，拖动H5单元格的填充柄至J5单元格 项目实施 任务3、统计各区各水果的销售情况 3．用VLOOKUP函数找出各个区销售额最大的水果所对应的水果名称 使用VLOOKUP函数时，要查找的对象（销售额最大值）必须位于查找数据区域的第1列，所以在使用VLOOKUP函数查找前，应先将“水果名称”列放在查找数据区域的右侧，这可以通过把“水果名称”列（A列）引用到“总计”列（E列）右侧的空白列（F列）中来实现。 项目实施 任务3、统计各区各水果的销售情况 3．用VLOOKUP函数找出各个区销售额最大的水果所对应的水果名称 将B5:F16单元格区域定义名称为“黄岩区”， 将C5:F16单元格区域定义名称为“椒江区”， 将D5:F16单元格区域定义名称为“路桥区”。 H6单元格公式“＝VLOOKUP(H5,黄岩区,5,FALSE)”，I6单元格公式“＝VLOOKUP(I5,椒江区,4,FALSE)”，J6单元格公式“＝VLOOKUP(J5,路桥区,3,FALSE)”， 项目实施 任务4、用数据透视图统计各区各水果的销售情况 项目实施 任务4、用数据透视图统计各区各水果的销售情况 项目实施 任务5、设置数据验证 1．设置“水果店”、“水果名称”和“所在区”等字段的数据验证 定义“水果店”工作表中的B3:B11单元格区域的名称为“水果店区域”， 定义“水果价格”工作表中的B3:B14单元格区域的名称为“水果名称区域”。 项目实施 任务5、设置数据验证 项目实施 任务5、设置数据验证 1．设置“水果店”、“水果名称”和“所在区”等字段的数据验证 项目实施 任务5、设置数据验证 2．设置“数量”字段的数据验证 项目实施 任务6、锁定单元格和保护工作表 总结与提高 总结与提高 （1）在很多函数的参数中都用到了单元格区域，为了操作方便，可以对这些单元格区域定义名称，当需要引用这些单元格区域时，直接引用它们的名称即可。在“名称管理器”中，可以对定义的单元格区域名称进行管理，如修改（编辑）名称、删除名称等。 （2）在使用VLOOKUP函数时，注意把要查找的内容定义在数据区域的第一列。实际应用中有很多需求可以使用VLOOKUP函数来解决。 总结与提高 总结与提高 （3）利用“嵌套分类汇总”，可以实现各种复杂的数据统计，如本项目中的统计各个区各水果店的销售额和毛利润。 （4）数据透视图是另一种数据表现形式，与数据透视表不同之处在于它可以选择适当的图形和色彩来描述数据的特性。利用数据透视图显示统计数据，更加直观明了。 总结与提高 总结与提高 （5）设置数据验证可以限制在单元格中输入数据的类型或大小等，当用户所设计的表单或工作表要被其他人用来输入数据时，数据验证尤为有用。 （6）为了防止某些单元格区域中的数据被选定和修改，可锁定这些单元格区域，启用“保护工作表”的功能后，这些被锁定的单元格区域就不能被选中和修改。 总结与提高 总结与提高 （1）AND函数 主要功能：返回逻辑值，如果所有参数值均为逻辑“真（TRUE）”，则返回逻辑“真（TRUE）”，反之返回逻辑“假（FALSE）” 使用格式：AND(logical1,logical2,…) 应用举例：在C5单元格中输入公式“＝AND(A5＞＝60,B5＞＝60)”，如果C5中返回TRUE，说明A5和B5中的数值均大于或等于60；如果返回FALSE，说明A5和B5中的数值至少有一个小于60。 总结与提高 总结与提高 （2）OR函数 主要功能：返回逻辑值，仅当所有参数值均为逻辑“假（FALSE）”时，返回逻辑“假（FALSE）”，否则返回逻辑“真（TRUE）” 使用格式：OR(logical1,logical2,…) 应用举例：在C62单元格中输入公式“＝OR(A62＞＝60,B62＞＝60)”，如果C62中返回TRUE，说明A62和B62中的数值至少有一个大于或等于60；如果返回FALSE，说明A62和B62中的数值都小于60。 总结与提高 总结与提高 （3）NOT函数 主要功能：对参数值求反，当要确保一个值不等于某一特定值时，可以使用NOT函数。 使用格式：NOT(logical) 应用举例：“＝NOT(FALSE)”的值为TRUE；“＝NOT(1＋1＝2)”的值为FALSE。 总结与提高 总结与提高 （4）SUM函数 主要功能：计算所有参数数值的和。 使用格式：SUM(number1,number2,…) 应用举例：在D64单元格中输入公式“＝SUM(D2:D63)”，即可求出D2:D63区域的总和。 总结与提高 总结与提高 （5）MOD函数 主要功能：返回两数相除的余数。结果的正负号与除数相同。 使用格式：MOD(number,divisor) 参数说明：number为被除数；divisor为除数。 应用举例：MOD(3,2)的值为1，MOD(-3,2)的值为1（负号与除数相同）。 总结与提高 总结与提高 （6）ROUND函数 主要功能：返回某个数字按指定位数四舍五入后的数字。 使用格式：ROUND(number,num_digits) 如果 num_digits 大于 0，则四舍五入到指定的小数位。 如果 num_digits 等于 0，则四舍五入到最接近的整数。 如果 num_digits 小于 0，则在小数点左侧进行四舍五入。 应用举例：ROUND(2.15,1)的值为2.2，ROUND(-1.475,2)的值为-1.48，ROUND(-21.5,-1)的值为20。 总结与提高 总结与提高 （7）YEAR函数 主要功能：返回某日期的年份值。返回值为1900到9999之间的整数。 使用格式：YEAR(serial_number) MONTH函数、DAY函数的用法与YEAR函数的用法类似，分别是返回某日期中的月和日。TODAY函数（该函数不需要参数）返回系统的当前日期。 总结与提高 总结与提高 （8）HOUR函数 主要功能：返回时间值的小时数。即一个介于0到23之间的整数。 使用格式：HOUR(serial_number) MINUTE函数的用法与HOUR函数的用法类似，返回时间值中的分钟数。 总结与提高 总结与提高 （9）REPLACE函数 主要功能：使用其他文本字符串并根据所指定的字符数替换某文本字符串中的部分文本。 使用格式：REPLACE(old_text,start_num,num_chars,new_text) 总结与提高 总结与提高 （10）MID函数 主要功能：返回文本字符串中从指定位置开始的特定数目的字符。 使用格式：MID(text,start_num,num_chars) 应用举例：MID("abcdefgh",3,2)值为“cd” CONCATENATE(text1,text2, ...)函数用于将几个文本字符串合并为一个文本字符串，或用&运算符代替函数CONCATENATE实现文本项的合并。如，假设A1、A2单元格的内容分别为“2020”、“9”，则CONCATENATE(A1,"年",A2,"月")或“A1&"年"&A2&"月"”的值为“2020年9月” 总结与提高 总结与提高 EXACT(text1,text2)函数用于测试两个字符串是否完全相同。 FIND(find_text,within_text,start_num)函数用于查找文本字符串，如FIND("cd","abcdeabcde")的值为3，FIND("cd","abcdeabcde",5)的值为8。 TEXT(value,format_text)函数用于将数值转换为按指定数字格式表示的文本。 如“TEXT(123.456,"$0.00")”的值为“$123.46” UPPER(text)函数用于将文本(text)转换成大写形式。LOWER(text)函数用于将文本(text)转换成小写形式。 总结与提高 总结与提高 （11）HLOOKUP函数 HLOOKUP函数的功能是：在数据表的首行查找指定的数值，并由此返回数据表当前列中指定行处的数值。 其用法与VLOOKUP函数类似 总结与提高 总结与提高 （12）DAVERAGE函数 主要功能：返回列表或数据库中满足指定条件的列中数值的平均值。 使用格式：DAVERAGE(database,field,criteria) DCOUNT、DSUM、DMAX、DMIN函数分别返回列表或数据库中满足指定条件的列中数值的单元格数目、总和、最大值、最小值。 总结与提高 总结与提高 （13）IS类函数 ISBLANK、ISERR、ISERROR、ISLOGICAL、ISNA、ISNONTEXT、ISNUMBER、ISREF、ISTEXT、ISEVEN和ISODD函数统称为IS类函数，可以检验数值的类型并根据参数取值返回TRUE或FALSE。 $$