EXCEL全部试题整合

EXCEL全部单项操作题

1. 在Sheet5的A1单元格中输入分数 1/3。 A1单元格输入0 1/3

2. 在Sheet5中，使用函数，将A1单元格中的数四舍五入到整百，存放在B1单元格中。 B1单元格输入=ROUND(A1,-2)

3. 在Sheet4中，使用函数，将B1中的时间四舍五入到最接近的15分钟的倍数，结果存放在C1单元格中。 C1单元格输入=ROUND(B1*24/0.25,0)/24*0.25 =ROUND(B1*1440/15,0)/1440*15

4. 在Sheet5的A1单元格中设置为只能录入5位数字或文本。当录入位数错误时，提示错误原因，样式为“警告”，错误信息为“只能录入5位数字或文本”。 数据—数据有效性—允许文本长度等于5—出错警告：样式警告；错误信息：只能录入5位数字或文本。

5. 在Sheet5中设定A列中不能输入重复的数值。

数据—数据有效性—有效性条件自定义公式=COUNTIF(A:A,A1)=1 6. 在Sheet5中，使用函数，根据A2单元格中的身份证号码判断性别，结果为“男”或“女”，存放在B2单元格中。 B2单元格输入 =IF(MOD(MID(A2,17,1),2)=1,\男\女\

7. 在Sheet5的B1单元格中输入公式，判断当前年份是否为闰年，结果为TRUE或FALSE。 B1单元格=OR(AND(MOD(YEAR(TODAY()),4)=0,MOD(YEAR(TODAY()),100)<>0),MOD(YEAR(TODAY()),400)=0)

8. 在Sheet5中使用多个函数组合，计算A1:A10中奇数的个数，结果存放在B1单元格中。 （1） B1单元格输入=SUM(MOD(A1:A10,2))Ctrl+Shift+Enter数组公式

填充

（2） B1单元格输入=SUMPRODUCT((MOD(A1:A10,2)) （3） B1单元格输入=SUMPRODUCT(--(MOD(A1:A10,2)=1)) 以下几题都是利用条件格式对相应内容进行设置，操作自行完成： 91. 在Sheet1中，使用条件格式将性别列中为“女”的单元格中字体颜色设置为红色、加粗显示。

92. 在Sheet1中，使用条件格式将“采购数量”列中数量大于100的单元格中字体颜色设置为红色、加粗显示。

93. 在Sheet1中，使用条件格式将“语文”列中数据大于80的单元格中字体颜色设置为红色、加粗显示。

94. 在Sheet1中，使用条件格式将“瓦数”列中数据小于100的单元格中字体颜色设置为红色、加粗显示。

95. 在Sheet1中，使用条件格式将“预定日期”列中日期为2008-4-1后的单元格中字体颜色设置为红色、加粗显示。对C列，设置“自动调整列宽”。

EXCEL函数整合

一、IF函数的使用

1、题01（学历—学位填充）

H3输入 =IF(G3=\博士研究生\博士\硕士研究生\硕士\本科\学士\无\ 填充至相应单元格

2、题03（求折扣率）

E11输入公式=IF(B11<100,B$3,IF(B11<200,B$4,IF(B11<300,B$5,B$6)))填充至相应单元格

=IF(B11>=300,B$6,IF(B11>=200,B$5,IF(B11>=100,B$4,B$3))) 3、题04（求应付金额） 前提：HLOOKUP函数

G9输入=(IF(HOUR(F9)=0,1,IF(MINUTE(F9)>=15,HOUR(F9)+1,HOUR(F9))))*C9填

充至相应单元格 4、题05（优等生判定）

I2输入=IF(AND(C2>AVERAGE(C$2:C$39),D2>AVERAGE(D$2:D$39),E2>AVERAGE(E$2:E$39)),TRUE,FALSE)

更简单的：=AND(C2>AVERAGE(C$2:C$39),D2>AVERAGE(D$2:D$39),E2>AVERAGE(E$2:E$39))

5、题06（求闰年）

前提：理解闰年条件及MOD、AND和OR函数的使用

B2输入=IF(OR(AND(MOD(A2,4)=0,MOD(A2,100)<>0), MOD(A2,400)=0),\闰年\平年\ 填充至相应单元格 6、题07（是否为文本信息）

B22输入=IF(TYPE(B21)=2,TRUE,FALSE) =ISTEXT(B21)或=TYPE(B21)=2 7、题08（是否有资格评高工）

K3输入 =IF(AND(H3>20,I3=\工程师\ 填充至相应单元格 或者 =AND(H3>20,I3=\工程师\8、题10（温度较高城市）

D3输入 =IF(B3>C3,\杭州\上海\ 填充至相应单元格 二、COUNTIF函数的使用

1、题02（求男性人数、求大于40人数） 前提：计算年龄

B2输入 =COUNTIF(Sheet1!B2:B37,\男\B3输入=COUNTIF(Sheet1!D2:D37,\

2、题04（求停车费大小40的记录条数）

前提：HLOOKUP填充单价、停放时间和应付金额 J8输入=COUNTIF(G9:G39,\

3、题05（统计数学各个分数段的人数） 前提：（无） B2输入=COUNTIF(Sheet1!D$2:D$39,\

B3输入=COUNTIF(Sheet1!D$2:D$39,\B4输入=COUNTIF(Sheet1!D$2:D$39,\B5输入=COUNTIF(Sheet1!D$2:D$39,\B6输入=COUNTIF(Sheet1!D$2:D$39,\另外一种格式的写法如下：

=COUNTIF(Sheet1!D$2:D$39,\=COUNTIF(Sheet1!D$2:D$39,\=COUNTIF(Sheet1!D$2:D$39,\=COUNTIF(Sheet1!D$2:D$39,\=COUNTIF(Sheet1!D$2:D$39,\

4、题06（求“高等教育出版社”种类数、一定范围内的订购 数量）前提：（无）

L3输入 =COUNTIF(D3:D51,\高等教育出版社\

L4输入=COUNTIF(G3:G52,\=COUNTIF(G3:G52,\5、题07（统计吸烟人数） 前提：（无） B14输入=COUNTIF(B3:E12,\B15输入=COUNTIF(B3:E12,\

6、题08（统计“男性”、“高工”、“年龄>=10”人数）

前提：计算工龄

N3输入 =COUNTIF(D3:D66,\男\N4输入 =COUNTIF(I3:I66,\高级工程师\N5输入=COUNTIF(H3:H66,\三、RANK函数（题库01、05、09） 1、题01（学生成绩排名）

N3输入=RANK(M3,M$3:M$18,0)填充至相应单元格 2、题05（总分排名）

G2输入=RANK(G2,G$2:G$39,0)填充至相应单元格 3、题09（每个销售人员的销售总额排名） C2输入=RANK(B2,B$2:B$6)填充至相应单元格 四、SUMIF函数（题库03、06、09） 1、题03（各项统计求和）

J12输入=SUMIF(A$11:A$43,I12,B$11:B$43)填充至相应单元格 K12输入=SUMIF(A$11:A$43,I12,F$11:F$43)填充至相应单元格 2、题06（各用户支付总额）

L8输入=SUMIF(A$3:A$52,K8,I$3:I$52)填充至相应单元格 3、题09（每个销售人员的销售总额）

B2输入=SUMIF(Sheet1!K$3:K$26,A2,Sheet1!I$3:I$26)填充至相应单元格 五、REPLACE函数（题库02、08） 1、题02（电话号码升级）

G2输入=REPLACE(F2,5,0,8)填充至相应单元格 题08（员工代号升级）

C3输入=REPLACE(B3,3,0,0)填充至相应单元格 六、VLOOKUP函数（题库03）

D11输入=VLOOKUP(A11,F$3:G$5,2,FALSE)填充至相应单元格

七、HLOOKUP函数（题库04）

C9输入=HLOOKUP(B9,A$2:C$3,2,FALSE)填充至相应单元格 八、其它函数（提供参考资料，以课后自学为主） 1、YEAR函数（题库02、08） 2、NOW函数（题库02） 3、AND函数（题库02、04、06） 4、OR函数（题库02、04、06） 5、MOD函数（题库02、04） 6、MAX、MIN函数（题库04、10） 7、ABS函数（题库10） 8、DAVERAGE函数（题库07）

G25输入=DAVERAGE(A2:H18,E2,J4:L5) 9、DCOUNT函数（题库07）

G26输入=DCOUNT(A2:H18,,J9:L10) 10、ISTEXT函数（题库07） 11、TYPE函数（题库07）

B22输入=ISTEXT(B21)或者=TYPE(B21)=2 B22输入=IF(TYPE(B21)=2,TRUE,FALSE)

其中TYPE函数的返回值：1——数值 2——文本——误差值 64——数组

其他知识点、操作要点包括： 数组公式的使用、修改数组公式

高级筛选：复制原表相应内容生成筛选条件 数据透视图或数据透视表的使用

4——逻辑 16

七、HLOOKUP函数（题库04）

C9输入=HLOOKUP(B9,A$2:C$3,2,FALSE)填充至相应单元格 八、其它函数（提供参考资料，以课后自学为主） 1、YEAR函数（题库02、08） 2、NOW函数（题库02） 3、AND函数（题库02、04、06） 4、OR函数（题库02、04、06） 5、MOD函数（题库02、04） 6、MAX、MIN函数（题库04、10） 7、ABS函数（题库10） 8、DAVERAGE函数（题库07）

G25输入=DAVERAGE(A2:H18,E2,J4:L5) 9、DCOUNT函数（题库07）

G26输入=DCOUNT(A2:H18,,J9:L10) 10、ISTEXT函数（题库07） 11、TYPE函数（题库07）

B22输入=ISTEXT(B21)或者=TYPE(B21)=2 B22输入=IF(TYPE(B21)=2,TRUE,FALSE)

其中TYPE函数的返回值：1——数值 2——文本——误差值 64——数组

其他知识点、操作要点包括： 数组公式的使用、修改数组公式

高级筛选：复制原表相应内容生成筛选条件 数据透视图或数据透视表的使用

4——逻辑 16

本文来源：https://www.bwwdw.com/article/6mr6.html