Excel函数应用与公式编写实操题库.docxVIP

Excel函数应用与公式编写实操题库

在数据处理的日常工作中，Excel函数与公式是提升效率、实现数据自动化分析的核心工具。无论是数据统计、条件判断，还是复杂的数据清洗与多表关联，熟练的函数运用能力都不可或缺。本文旨在提供一份贴近实际工作场景的Excel函数应用实操题库，通过由浅入深的练习，帮助读者巩固基础、拓展思路，真正将函数知识转化为解决实际问题的能力。每道题目均包含场景描述、需求分析及可能的解决方向，鼓励读者先独立思考，再对照提示与解答进行复盘。

一、基础概念与常用函数巩固

题目1：销售额的简单统计与排名

场景描述：某销售部门有一份包含10位销售人员的月度销售额数据（假设数据在A1:B11区域，A列为姓名，B列为销售额）。

需求：

1.计算所有销售人员的总销售额。

2.计算平均销售额。

3.找出最高销售额和最低销售额。

4.为每位销售人员的销售额在区域内进行降序排名。

提示：思考SUM、AVERAGE、MAX、MIN以及RANK.EQ（或RANK）函数的基本用法。注意排名函数的参数顺序和排序方式。

题目2：条件判断与等级划分

场景描述：学生成绩表中（假设数据在A1:C51区域，A列为学号，B列为姓名，C列为分数，分数范围0-100）。

需求：根据分数为每位学生评定等级，规则如下：90分及以上为A，80-89分为B，70-79分为C，60-69分为D，60分以下为E。结果显示在D列。

提示：IF函数可以进行条件判断，嵌套IF函数可以实现多条件分级。注意逻辑运算符的正确使用，以及各条件之间的顺序。

二、数据清洗与规范化

题目3：文本提取与合并

场景描述：一份客户信息表中，客户姓名（假设在A列）的格式为姓氏+名字（如张三、李四），电话号码（假设在B列）的格式不统一，部分包含区号且有不同分隔符（如____、021____、____）。

需求：

1.在C列提取客户的姓氏（假设均为单姓）。

2.在D列将B列的电话号码统一格式为纯数字，去除所有分隔符和可能的区号（若有区号，仅保留-或空格后的主体号码部分，假设区号后为8位数字）。

提示：文本提取可考虑LEFT或MID函数。文本清洗可能需要结合使用SUBSTITUTE函数去除特定字符，或使用RIGHT结合LEN等函数从右侧提取固定长度字符。思考如何判断电话号码是否包含区号。

题目4：重复值与缺失值处理

场景描述：一份员工信息表（假设数据在A1:E100区域，A列为员工ID，其他列为基本信息）。

需求：

1.在F列标识出A列中是否存在重复的员工ID，重复显示重复，不重复显示唯一。

2.在G列标识出B列（姓名）中是否存在空白单元格，空白显示缺失，否则显示正常。

提示：判断重复值可使用COUNTIF函数。判断缺失值可直接使用ISBLANK函数或结合IF函数检查单元格是否为空。

三、数据统计与分析

题目5：多条件求和与计数

场景描述：某店铺销售数据记录表（假设数据在A1:D1000区域，A列为日期，B列为产品类别，C列为销售额，D列为销量）。

需求：

1.计算2023年10月份（假设日期格式为YYYY-MM-DD），产品类别为电子产品的总销售额。

2.计算产品类别为服装且销量大于50件的交易记录有多少条。

提示：多条件求和使用SUMIFS函数，多条件计数使用COUNTIFS函数。注意日期条件的写法，可以使用DATE函数构造日期范围，或使用文本比较（需确保格式一致）。

题目6：数据的动态平均值与占比

场景描述：延续题目5的销售数据。

需求：

1.计算每个产品类别的平均销售额（不区分日期）。

2.计算每个产品类别的总销售额占所有产品总销售额的百分比，结果保留两位小数，并显示为百分比格式。

提示：分类平均可以使用AVERAGEIF函数。计算占比时，分子为某类别的销售额总和（可用SUMIF），分母为总销售额（可用SUM）。百分比格式可通过单元格格式设置，或使用TEXT函数转换。

四、逻辑判断与条件计算

题目7：复杂条件下的奖金计算

场景描述：某公司根据员工的月度销售额（假设在B列）和入职年限（假设在C列，单位：年）来计算月度奖金（结果在D列）。

需求：奖金规则如下：

*若销售额=____元：

*入职年限=5年，奖金为销售额的8%

*入职年限3-5年（含3年，不含5年），奖金为销售额的6%

*入职年限3年，奖金为销售额的5%

*若____元=销售额____元：

*入职年限=5年，奖金为销售额的5%

*入职年限5年，奖金为销售额的4%

*若销售额____元，奖金为销售额的2%

提示：这是一个典型的多条件嵌套逻辑问题，主要使用IF函数。注意判断条件的先后顺序，以及各层级条件的正确