一、问题：
二、表格内容
三、分析结果
四、具体的操作步骤：
-
销售工作表公式设计与数据验证
- 类别（Category）列公式：
- 在
Category列（假设为D列），根据ProductCode在Catalogue工作表中查找对应的类别。假设Sales工作表中ProductCode在C列，在D2单元格（假设数据从第二行开始）输入公式=VLOOKUP(C2,Catalogue!$A$2:$D$13,2,FALSE)，然后向下填充至数据末尾。此公式在Catalogue工作表的A2:D13区域（产品代码、类别、产品名称、价格列）中，根据Sales工作表中的产品代码查找对应的类别。
- 价格（Price）列公式：
- 在
Price列（假设为E列），同样根据ProductCode查找价格。在E2单元格输入公式=VLOOKUP(C2,Catalogue!$A$2:$D$13,4,FALSE)，向下填充。该公式在Catalogue工作表中查找产品代码对应的价格。
- 总计（Total (HK$)）列公式：
- 在
Total (HK$)列（假设为G列），总计等于价格乘以数量。在G2单元格输入公式=E2*F2，然后向下填充。这里利用了价格列和数量列的数据来计算每笔销售的总计金额。
- 现金（Cash (HK$)）列公式及数据验证：
- 公式：在
Cash (HK$)列（假设为H列），如果销售地点是 “Web”，则现金等于总计，因为网上购物只能用信用卡支付现金，相当于现金支付全部金额；如果销售地点不是 “Web”，则现金由用户输入，但需满足现金小于等于总计且现金是 50 的倍数（因为可能使用现金券支付剩余部分）。在H2单元格输入公式=IF(B2="Web",G2,IF(AND(H2<=G2,MOD(H2,50)=0),H2,""))，向下填充。这里的IF函数先判断销售地点，如果是 “Web” 就取总计金额，否则进一步判断输入的现金是否符合条件，如果不符合就显示空值（因为假设用户输入错误数据时先不显示错误提示，只显示空值，后续可通过数据验证提醒用户）。 - 数据验证：选中
H列（现金列），点击 “数据” 选项卡中的 “数据验证”。在 “设置” 选项卡中，允许选择 “自定义”，公式输入=AND(H2<=G2,MOD(H2,50)=0)。这样当用户输入现金数据时，如果不符合现金小于等于总计且是 50 倍数的条件，就会弹出提示框告知用户输入错误。
- 现金券（Voucher (HK$)）列公式及数据验证：
- 公式：在
Voucher (HK$)列（假设为I列），现金券金额等于总计减去现金（如果现金输入正确的话）。在I2单元格输入公式=G2 - H2，向下填充。 - 数据验证：选中
I列（现金券列），点击 “数据” 选项卡中的 “数据验证”。在 “设置” 选项卡中，允许选择 “自定义”，公式输入=I2=G2 - H2。这样当用户输入现金数据后，现金券列会自动计算并验证计算结果是否正确，如果不正确会弹出提示框。
-
创建分析工作表并进行数据分析和可视化
- 创建分析工作表：在 Excel 中插入一个新工作表，重命名为 “分析”。
- 按销售地点统计销售总额：
- 在 “分析” 工作表中，使用
SUMIF函数统计不同销售地点的销售总额。假设Sales工作表中销售地点在B列，总计在G列。在 “分析” 工作表的A2单元格输入 “销售地点”，B2单元格输入 “销售总额”。在A3单元格输入 “Ocean Park”，在B3单元格输入公式=SUMIF(Sales!$B$2:$B$18,"Ocean Park",Sales!$G$2:$G$18)，计算海洋公园的销售总额；在A4单元格输入 “Ocean Park Hotel”，在B4单元格输入公式=SUMIF(Sales!$B$2:$B$18,"Ocean Park Hotel",Sales!$G$2:$G$18)，计算海洋公园酒店的销售总额；在A5单元格输入 “Web”，在B5单元格输入公式=SUMIF(Sales!$B$2:$B$18,"Web",Sales!$G$2:$G$18)，计算网上商店的销售总额。
- 按类别统计销售总额：
- 在 “分析” 工作表中，继续统计不同类别的销售总额。在
A7单元格输入 “类别”，B7单元格输入 “销售总额”。在A8单元格输入 “Bag”，在B8单元格输入公式=SUMIF(Sales!$D$2:$D$18,"Bag",Sales!$G$2:$G$18)，计算包类产品的销售总额；在A9单元格输入 “Accessory”，在B9单元格输入公式=SUMIF(Sales!$D$2:$D$18,"Accessory",Sales!$G$2:$G$18)，计算配件类产品的销售总额；在A10单元格输入 “Toy”，在B10单元格输入公式=SUMIF(Sales!$D$2:$D$18,"Toy",Sales!$G$2:$G$18)，计算玩具类产品的销售总额。
- 创建图表：
- 选中
A2:B5单元格区域（销售地点和销售总额数据），点击 “插入” 选项卡，选择合适的图表类型，如柱状图，以直观展示不同销售地点的销售情况对比。 - 再选中
A7:B10单元格区域（类别和销售总额数据），同样插入合适的图表，如饼图，以展示各类产品销售总额的占比情况。
- 描述发现：
- 在 “分析” 工作表中，可以描述以下发现：
- 从销售地点来看，[销售总额最高的地点] 的销售额最高，可能是因为该地点的客流量较大或者产品展示更吸引人等原因。而 [销售总额最低的地点] 销售额较低，需要进一步分析是市场定位问题还是营销推广不足等因素导致，考虑是否需要调整营销策略或改善该地点的购物环境等。
- 从产品类别来看，[销售总额最高的类别] 是最受欢迎的产品类别，可能是由于该类产品的设计、实用性或与海洋公园主题的契合度较高。对于销售不佳的类别，如 [销售总额最低的类别]，可以考虑优化产品设计、调整价格策略或加强促销活动，以提高其销售额。同时，根据各类产品的销售趋势，合理安排库存，避免积压或缺货现象。例如，如果某类产品销售持续增长，可以适当增加库存；如果某类产品销售不佳且库存积压，可以考虑促销清货或改进产品。
- （英文版本：In the "Analysis" worksheet, the following findings can be described: From the perspective of sales locations, the sales volume at Ocean Park is the highest, which may be due to the larger passenger flow at this location or more attractive product displays. However, the sales volume at Ocean Park Hotel is relatively low, and further analysis is needed to determine whether it is caused by market positioning issues or insufficient marketing and promotion. Consider whether it is necessary to adjust the marketing strategy or improve the shopping environment at this location. From the perspective of product categories, "Bag" is the most popular product category, which may be due to the design, practicality of this type of product, or a higher degree of fit with the theme of the Ocean Park. For poorly-selling categories, such as "Accessory", optimization of product design, adjustment of price strategies, or strengthening of promotional activities can be considered to increase sales. At the same time, according to the sales trends of various product categories, reasonable inventory arrangements should be made to avoid overstocking or out-of-stock situations.）
