Excel常用函数复习题库
📌 基础计算函数
题目1：单元格 B4
题目描述：
假设您有一个销售数据表，A列是产品编号，B列是单价，C列是数量。
请在B4单元格中编写公式，计算 B2单元格的单价 与 C2单元格的数量 相乘的结果。
数据表结构：
A B C D
1 ┌─────────────────────────────┐
│ 产品ID │ 单价 │ 数量 │ 金额 │
2 │ P001 │ 29.99 │ 5 │ │
3 │ P002 │ 49.50 │ 3 │ │
4 │ P003 │ 15.00 │ 10 │ │
└─────────────────────────────┘
答案： =B2*C2
题目2：单元格 E8
题目描述：
您需要计算1月份所有产品的销售总额。
请在E8单元格中编写公式，对 D2:D31区域 的所有销售额进行求和。
数据表结构（部分）：
A B C D E
1 ┌─────────────────────────────────────┐
│ 日期 │ 产品ID │ 单价 │ 销售额 │ 总计 │
2 │ 1月1日 │ P001 │ 29.99 │ 149.95 │ │
3 │ 1月1日 │ P002 │ 49.50 │ 148.50 │ │
4 │ 1月2日 │ P001 │ 29.99 │ 89.97 │ │
│ ... │ ... │ ... │ ... │ │
8 │ │ │ │ │ [在此写公式]│
└─────────────────────────────────────┘
答案： =SUM(D2:D31)
题目3：单元格 F12
题目描述：
您需要计算某部门员工的平均月薪。
请在F12单元格中编写公式，计算 E2:E20区域 所有薪资的平均值。
数据表结构：
A B C D E F
1 ┌─────────────────────────────────────────────┐
10│ 员工ID │ 姓名 │ 部门 │ 职位 │ 月薪 │ 平均薪资│
11│ E001 │ 张三 │ 销售部 │ 经理 │ 8500 │ │
12│ E002 │ 李四 │ 销售部 │ 专员 │ 6500 │ [在此写公式]│
13│ E003 │ 王五 │ 销售部 │ 专员 │ 6200 │ │
14│ ... │ ... │ ... │ ... │ ... │ │
20│ E010 │ 赵六 │ 销售部 │ 主管 │ 7200 │ │
└─────────────────────────────────────────────┘
答案： =AVERAGE(E2:E20)
题目4：单元格 H5
题目描述：
您需要找出本月单笔最高销售额。
请在H5单元格中编写公式，查找 G2:G50区域 中的最大值。
数据表结构：
F G H
1 ┌─────────────────────────┐
│ 订单号 │ 销售额 │ 最高额 │
2 │ ORD001 │ 1250 │ │
3 │ ORD002 │ 890 │ │
4 │ ORD003 │ 2100 │ │
5 │ ORD004 │ 1560 │ [在此写公式]│
│ ... │ ... │ │
50│ ORD049 │ 1870 │ │
└─────────────────────────┘
答案： =MAX(G2:G50)
📌 逻辑与查找函数
题目5：单元格 I3
题目描述：
您需要根据成绩判断是否及格。
请在I3单元格中编写公式：如果 H3单元格的成绩 ≥ 60，则显示"及格"，否则显示"不及格"。
数据表结构：
G H I
1 ┌─────────────────────────┐
│ 学号 │ 成绩 │ 结果 │
2 │ S001 │ 85 │ 及格 │
3 │ S002 │ 55 │ [在此写公式]│
4 │ S003 │ 72 │ │
5 │ S004 │ 90 │ │
└─────────────────────────┘
答案： =IF(H3>=60, "及格", "不及格")
题目6：单元格 K7
题目描述：
您需要根据产品ID查找对应的产品名称。
请在K7单元格中编写VLOOKUP公式：
- 查找值：J7单元格的产品ID
- 查找区域：A2:B100区域（A列是产品ID，B列是产品名称）
- 返回第2列
- 精确匹配
数据表结构：
A B J K
1 ┌─────────────────────────────┐
│ 产品ID │ 产品名称 │ 查询ID │ 查询结果│
2 │ P001 │ 鼠标 │ │ │
3 │ P002 │ 键盘 │ │ │
4 │ P003 │ 显示器 │ │ │
5 │ P004 │ 主机 │ │ │
6 │ ... │ ... │ │ │
7 │ P050 │ U盘 │ P003 │ [在此写公式]│
└─────────────────────────────┘
答案： =VLOOKUP(J7, A2:B100, 2, FALSE)
题目7：单元格 M10
题目描述：
您需要根据销售业绩计算提成比例：
- 业绩 ≥ 10000：提成5%
- 5000 ≤ 业绩 < 10000：提成3%
- 业绩 < 5000：提成1%
请在M10单元格中编写嵌套IF公式，根据 L10单元格的业绩 计算提成比例。
数据表结构：
L M
9 ┌─────────────────┐
│ 业绩 │ 提成比例│
10│ 12500 │ [在此写公式]│
11│ 4800 │ │
12│ 8500 │ │
13│ 15000 │ │
└─────────────────┘
答案： =IF(L10>=10000, "5%", IF(L10>=5000, "3%", "1%"))
📌 统计与文本函数
题目8：单元格 O15
题目描述：
您需要统计有效订单的数量（只统计数字类型的订单金额）。
请在O15单元格中编写公式，统计 N2:N200区域 中数值单元格的数量。
数据表结构：
N O
1 ┌─────────────────┐
│ 订单金额 │ 订单数 │
2 │ 1250 │ │
3 │ 未支付 │ │
4 │ 890 │ │
5 │ - │ │
6 │ 1560 │ │
│ ... │ │
15│ 2100 │ [在此写公式]│
└─────────────────┘
答案： =COUNT(N2:N200)
题目9：单元格 Q8
题目描述：
您需要将客户的姓和名合并成一个完整的姓名。
请在Q8单元格中编写公式，将 P8单元格的姓 和 R8单元格的名 合并，中间用空格隔开。
数据表结构：
P Q R
7 ┌─────────────────────────┐
│ 姓 │ 全名 │ 名 │
8 │ 张 │ [在此写公式] │ 三 │
9 │ 李 │ │ 四 │
10│ 王 │ │ 五 │
└─────────────────────────┘
答案： =P8&" "&R8 或 =CONCATENATE(P8, " ", R8)
题目10：单元格 S20
题目描述：
您需要提取员工邮箱的用户名部分（@之前的部分）。
请在S20单元格中编写公式，从 R20单元格的完整邮箱 中提取"@"符号之前的所有字符。
数据表结构：
R S
19┌─────────────────────────┐
│ 邮箱 │ 用户名 │
20│ zhangsan@company.com │ [在此写公式] │
21│ lisi@company.com │ │
22│ wangwu@company.com │ │
└─────────────────────────┘
答案： =LEFT(R20, FIND("@", R20)-1)
题目11：单元格 U5
题目描述：
您需要将销售额四舍五入到整数。
请在U5单元格中编写公式，将 T5单元格的销售额 四舍五入到最接近的整数。
数据表结构：
T U
4 ┌─────────────────┐
│ 销售额 │ 取整后 │
5 │ 1250.67│ [在此写公式]│
6 │ 890.45 │ │
7 │ 2100.89│ │
└─────────────────┘
答案： =ROUND(T5, 0)
题目12：单元格 W9
题目描述：
您需要获取当前日期。
请在W9单元格中编写公式，显示系统当前的日期。
数据表结构：
V W
8 ┌─────────────────┐
│ 项目 │ 制表日期│
9 │ 月报表 │ [在此写公式]│
10│ 周报表 │ │
└─────────────────┘
答案： =TODAY()
📌 综合应用
题目13：单元格 Y15
题目描述：
您需要计算销售人员的绩效等级：
- 如果销售额 ≥ 10000，且客户评分 ≥ 4.5，则为"A级"
- 如果销售额 ≥ 8000，且客户评分 ≥ 4.0，则为"B级"
- 否则为"C级"
请在Y15单元格中编写公式，根据 X15单元格的销售额 和 Z15单元格的评分 判断绩效等级。
数据表结构：
X Y Z
14┌─────────────────────────┐
│ 销售额 │ 绩效等级│ 评分 │
15│ 12500 │ [在此写公式] │ 4.7 │
16│ 7500 │ │ 4.2 │
17│ 9500 │ │ 3.8 │
└─────────────────────────┘
答案： =IF(AND(X15>=10000, Z15>=4.5), "A级", IF(AND(X15>=8000, Z15>=4.0), "B级", "C级"))
题目14：单元格 AA10
题目描述：
您需要计算某产品的销售总额，但需要排除退货的订单（金额为负数）。
请在AA10单元格中编写公式，对 Z2:Z100区域 中所有大于0的数值进行求和。
数据表结构：
Z AA
9 ┌─────────────────┐
│ 订单金额 │ 有效总额│
10│ 1250 │ [在此写公式]│
11│ -500 │ │ (退货)
12│ 890 │ │
13│ 1560 │ │
14│ -200 │ │ (退货)
└─────────────────┘
答案： =SUMIF(Z2:Z100, ">0")
Excel常用函数复习题库（补充篇）
📌 条件统计函数
题目15：单元格 C10
题目描述：
您需要统计销售部门（B列）中业绩达标（C列≥10000）的员工人数。
请在C10单元格中编写公式，使用COUNTIFS函数进行多条件统计。
数据表结构：
A B C D
9 ┌─────────────────────────────┐
│ 员工ID │ 部门 │ 业绩 │ 统计结果│
10│ E001 │ 销售部 │ 12500 │ [在此写公式]│
11│ E002 │ 技术部 │ 15000 │ │
12│ E003 │ 销售部 │ 8500 │ │
13│ E004 │ 销售部 │ 11000 │ │
14│ E005 │ 人事部 │ 9500 │ │
15│ E006 │ 销售部 │ 13200 │ │
└─────────────────────────────┘
答案： =COUNTIFS(B10:B15, "销售部", C10:C15, ">=10000")
计算结果： 3（E001、E004、E006）
题目16：单元格 F8
题目描述：
您需要统计市场部女性员工的人数。
请在F8单元格中编写公式，使用COUNTIFS函数进行双条件统计。
数据表结构：
D E F
7 ┌─────────────────────────┐
│ 部门 │ 性别 │ 统计结果│
8 │ 市场部 │ 男 │ [在此写公式]│
9 │ 市场部 │ 女 │ │
10│ 销售部 │ 女 │ │
11│ 市场部 │ 女 │ │
12│ 技术部 │ 男 │ │
13│ 市场部 │ 女 │ │
└─────────────────────────┘
答案： =COUNTIFS(D8:D13, "市场部", E8:E13, "女")
计算结果： 3（第9、11、13行）
📌 条件求和函数
题目17：单元格 I12
题目描述：
您需要计算销售部在2024年第一季度的销售总额。
请在I12单元格中编写公式，使用SUMIFS函数进行多条件求和。
数据表结构：
G H I J
11┌─────────────────────────────────┐
│ 部门 │ 日期 │ 销售额 │ 季度总计│
12│ 销售部 │ 2024/1/5 │ 25000 │ [在此写公式]│
13│ 销售部 │ 2024/2/10│ 18000 │ │
14│ 技术部 │ 2024/1/15│ 15000 │ │
15│ 销售部 │ 2024/3/8 │ 32000 │ │
16│ 销售部 │ 2024/4/5 │ 28000 │ │
17│ 销售部 │ 2024/1/20│ 19000 │ │
└─────────────────────────────────┘
答案： =SUMIFS(J12:J17, G12:G17, "销售部", H12:H17, ">=2024/1/1", H12:H17, "<=2024/3/31")
计算结果： 25000+18000+32000+19000 = 94000
题目18：单元格 L15
题目描述：
您需要计算华东地区产品A在3月份的销售总额。
请在L15单元格中编写公式，使用SUMIFS函数进行三条件求和。
数据表结构：
K L M N O
14┌─────────────────────────────────────────┐
│ 地区 │ 产品 │ 月份 │ 销售额 │ 条件求和│
15│ 华东 │ 产品A │ 3 │ 50000 │ [在此写公式]│
16│ 华东 │ 产品B │ 3 │ 30000 │ │
17│ 华南 │ 产品A │ 3 │ 45000 │ │
18│ 华东 │ 产品A │ 4 │ 55000 │ │
19│ 华东 │ 产品A │ 3 │ 48000 │ │
20│ 华北 │ 产品A │ 3 │ 42000 │ │
└─────────────────────────────────────────┘
答案： =SUMIFS(N15:N20, K15:K20, "华东", L15:L20, "产品A", M15:M20, 3)
计算结果： 50000+48000 = 98000
📌 排名函数
题目19：单元格 R8
题目描述：
您需要根据销售额对销售人员进行排名（降序排列，即销售额最高的排第1名）。
请在R8单元格中编写公式，对Q列销售额进行排名。
数据表结构：
P Q R
7 ┌─────────────────────────┐
│ 销售员 │ 销售额 │ 排名 │
8 │ 张三 │ 125000 │ [在此写公式]│
9 │ 李四 │ 98000 │ │
10│ 王五 │ 156000 │ │
11│ 赵六 │ 110000 │ │
12│ 孙七 │ 87000 │ │
13│ 周八 │ 132000 │ │
└─────────────────────────┘
答案： =RANK(Q8, $Q$8:$Q$13, 0)
补充说明： 第三个参数为0表示降序排列，为1表示升序排列
排名结果： 王五(1)、周八(2)、张三(3)、赵六(4)、李四(5)、孙七(6)
题目20：单元格 U10
题目描述：
您需要使用RANK.EQ函数进行中国式排名（出现相同成绩时，名次相同且不跳过名次）。
请在U10单元格中编写公式，对T列成绩进行排名。
数据表结构：
T U
9 ┌─────────────────┐
│ 成绩 │ 排名 │
10│ 85 │ [在此写公式]│
11│ 92 │ │
12│ 78 │ │
13│ 85 │ │
14│ 90 │ │
15│ 92 │ │
└─────────────────┘
答案： =RANK.EQ(T10, $T$10:$T$15, 0)
中国式排名结果：
- 92分：并列第1名（第11、15行）
- 90分：第3名（第14行）
- 85分：并列第4名（第10、13行）
- 78分：第6名（第12行）
📌 单条件统计函数
题目21：单元格 X13
题目描述：
您需要统计成绩优秀（≥90分）的学生人数。
请在X13单元格中编写公式，使用COUNTIF函数进行单条件统计。
数据表结构：
W X
12┌─────────────────┐
│ 成绩 │ 优秀人数│
13│ 85 │ [在此写公式]│
14│ 92 │ │
15│ 78 │ │
16│ 95 │ │
17│ 88 │ │
18│ 90 │ │
└─────────────────┘
答案： =COUNTIF(W13:W18, ">=90")
计算结果： 3（92、95、90）
题目22：单元格 AA9
题目描述：
您需要统计产品名称为"笔记本电脑"的订单数量。
请在AA9单元格中编写公式，使用COUNTIF函数进行文本匹配统计。
数据表结构：
Z AA
8 ┌─────────────────────────┐
│ 产品名称 │ 订单数量│
9 │ 笔记本电脑 │ [在此写公式]│
10│ 鼠标 │ │
11│ 笔记本电脑 │ │
12│ 键盘 │ │
13│ 笔记本电脑 │ │
14│ 显示器 │ │
└─────────────────────────┘
答案： =COUNTIF(Z9:Z14, "笔记本电脑")
计算结果： 3
📌 综合应用题目
题目23：单元格 AD12
题目描述：
您需要完成一个综合统计任务：
- 统计销售部业绩在10000-20000之间的人数
- 计算这些人的平均业绩
- 找出这些人的最高业绩
- 对这些人按业绩进行排名
请在相应单元格中编写公式：
数据表结构：
AB AC AD AE AF
11┌─────────────────────────────────────────┐
│ 部门 │ 姓名 │ 业绩 │ 排名 │ 统计区│
12│ 销售部 │ 张三 │ 12500 │ [排名] │ 符合条件人数│
13│ 技术部 │ 李四 │ 18000 │ │ 平均业绩│
14│ 销售部 │ 王五 │ 8500 │ │ 最高业绩│
15│ 销售部 │ 赵六 │ 15000 │ │ │
16│ 销售部 │ 孙七 │ 22000 │ │ │
17│ 销售部 │ 周八 │ 13500 │ │ │
└─────────────────────────────────────────┘
答案：
- 符合条件人数：
=COUNTIFS(AB12:AB17, "销售部", AC12:AC17, ">=10000", AC12:AC17, "<=20000") - 平均业绩：
=AVERAGEIFS(AC12:AC17, AB12:AB17, "销售部", AC12:AC17, ">=10000", AC12:AC17, "<=20000") - 最高业绩：
=MAXIFS(AC12:AC17, AB12:AB17, "销售部", AC12:AC17, ">=10000", AC12:AC17, "<=20000") - 排名：
=IF(AND(AB12="销售部", AC12>=10000, AC12<=20000), RANK.EQ(AC12, $AC$12:$AC$17, 0), "不参与排名")
计算结果：
- 符合条件：3人（张三12500、赵六15000、周八13500）
- 平均业绩：(12500+15000+13500)/3 = 13666.67
- 最高业绩：15000
- 排名：赵六(1)、周八(2)、张三(3)
题目24：单元格 AH15
题目描述：
您需要制作一个动态统计表：
- 根据选择的部门和业绩范围进行统计
- AI14单元格为部门选择（下拉菜单）
- AI15单元格为最低业绩要求
- AI16单元格为最高业绩要求
请在AH15单元格编写动态统计公式。
数据表结构：
AG AH AI
14┌─────────────────────────┐
│ 统计项 │ 结果 │ 条件选择│
15│ 人数 │ [在此写公式] │ 销售部 │
16│ 总额 │ │ 10000 │
17│ 平均 │ │ 50000 │
└─────────────────────────┘
数据源：
AF AG
19┌─────────────────┐
│ 部门 │ 业绩 │
20│ 销售部 │ 12500 │
21│ 技术部 │ 18000 │
22│ 销售部 │ 32000 │
23│ 销售部 │ 15000 │
24│ 市场部 │ 9500 │
25│ 销售部 │ 28000 │
└─────────────────┘
答案：
AH15（人数）： =COUNTIFS(AF20:AF25, AI15, AG20:AG25, ">="&AI16, AG20:AG25, "<="&AI17)
AH16（总额）： =SUMIFS(AG20:AG25, AF20:AF25, AI15, AG20:AG25, ">="&AI16, AG20:AG25, "<="&AI17)
AH17（平均）： =AVERAGEIFS(AG20:AG25, AF20:AF25, AI15, AG20:AG25, ">="&AI16, AG20:AG25, "<="&AI17)
动态计算：
当选择"销售部"，范围10000-50000时：
- 人数：4人（12500、32000、15000、28000）
- 总额：87500
- 平均：21875
🎯 函数要点总结
| 函数 | 用途 | 语法示例 |
|---|---|---|
| COUNTIF | 单条件计数 | =COUNTIF(范围, 条件) |
| COUNTIFS | 多条件计数 | =COUNTIFS(范围1, 条件1, 范围2, 条件2, ...) |
| SUMIFS | 多条件求和 | =SUMIFS(求和范围, 范围1, 条件1, 范围2, 条件2, ...) |
| RANK/RANK.EQ | 排名 | =RANK(数值, 范围, 排序方式) |
| AVERAGEIFS | 多条件平均 | =AVERAGEIFS(平均范围, 范围1, 条件1, ...) |
| MAXIFS | 多条件最大值 | =MAXIFS(范围, 条件范围1, 条件1, ...) |
使用技巧：
- 绝对引用： 在排名函数中使用
$锁定范围，如$Q$8:$Q$13 - 文本连接： 在条件中使用
&连接运算符，如">="&AI16 - 条件组合： 使用
AND()函数组合多个条件 - 动态范围： 结合下拉菜单创建动态统计表
评论区