浙江省计算机等级考试(二级)excel操作详解

浙江省计算机等级考试（二级）
EXECL操作详解
目录
第一章Excel二级完整题示例 (1)
01采购情况表(灯)-吸烟情况 (1)
02图书订购信息表 (1)
03公司员工人事信息表 (2)
04停车情况记录表 (3)
05学生成绩表 (4)
06零件检测结果表 (5)
07用户-年龄-电话升级 (5)
08采购表-折扣表 (6)
09教材订购情况表 (7)
10医院病人护理统计表 (7)
11员工资料表-职务补贴-折旧 (8)
12员工信息表-工龄-职称 (8)
13房产销售表 (9)
14学生成绩表-优等生 (9)
15学生成绩表(铅球)-贷款 (10)
16销售统计表 (11)
17公务员考试成绩表 (11)
18温度情况表 (12)
19通讯费年度计划表 (12)
20学生成绩表(1级)-投资情况表 (13)
第二章Excel二级题操作步骤参考 (14)
01采购情况表(灯)-吸烟情况 (14)
02图书订购信息表 (16)
03公司员工人事信息表 (17)
04停车情况记录表 (18)
05学生成绩表 (19)
06零件检测结果表 (20)
07用户-年龄-电话升级 (21)
08采购表-折扣表 (22)
09教材订购情况表 (23)
10医院病人护理统计表 (24)
11员工资料表-职务补贴-折旧 (25)
12员工信息表-工龄-职称 (26)
13房产销售表 (27)
14学生成绩表-优等生 (28)
15学生成绩表(铅球)-贷款 (29)
16销售统计表 (30)
17公务员考试成绩表 (31)
18温度情况表 (32)
19通讯费年度计划表 (33)
20学生成绩表(1级)-投资情况表 (34)
第一章Excel二级完整题示例01 采购情况表(灯)-吸烟情况
02 图书订购信息表
03 公司员工人事信息表
04 停车情况记录表
05 学生成绩表
06 零件检测结果表
07 用户-年龄-电话升级
08 采购表-折扣表
09 教材订购情况表
10 医院病人护理统计表
11 员工资料表-职务补贴-折旧
12 员工信息表-工龄-职称
13 房产销售表
14 学生成绩表-优等生
15 学生成绩表(铅球)-贷款
16 销售统计表
17 公务员考试成绩表
18 温度情况表
19 通讯费年度计划表
20 学生成绩表(1级)-投资情况表
第二章Excel二级题操作步骤参考
01 采购情况表(灯)-吸烟情况
操作步骤：
1)在Sheet1中，选定B3:B18单元格，选择菜单“格式”/“条件格式…”，在条件格式对话框设置
条件为单元格数据小于100，单击“格式”按钮，在弹出对话框上设置字体颜色为红色，加粗显
示。

2)在Sheet1中，选定H3:H18单元格，输入公式“=E3:E18*F3:F18*G3:G18”，按CTRL+SHIFT+ENTER，
编辑栏中显示“{=E3:E18*F3:F18*G3:G18}”。

3)A）在Sheet1中，在G25中输入公式“=DA VERAGE(A2:H18,5,J4:L5)”。

设置保留两位小数。

B）在Sheet1中，在G26中输入公式“=DCOUNT(A2:H18,2,J9:L10)”。

4)A）在Sheet2中，在B14中输入公式“=COUNTBLANK(B3:E12)”。

B）在Sheet2中，在B15中输入公式“=COUNTIF(B3:E12,"Y")”。

5)在Sheet2中，在B22中输入公式“=ISTEXT(B21)”。

6)A）将Sheet1中的“采购情况表”复制到Sheet3中。

B）创建条件区域，输入筛选条件。

在Sheet3的空白单元格，如J2:K3单元格，输入如图中所示的“产品”、“商标”、“白炽灯”、“上海”，为减少数据输入错误，输入内容可以从“采购情况表”
中复制得到。

C）使用高级筛选功能。

在Sheet3中，使活动单元格置于“采购情况表”中，选择菜单“数据”/“筛选”/“高级筛选”，如图中所示选择条件区域。

单击“确定”按钮。

7)在Sheet1中，选择菜单“数据”/“数据透视表和透视图”，按向导分别设置数据源类型、数据源
区域（Sheet1!$A$2:$H$18）、显示位置（选择“现有工作表”，选择Sheet4中A1单元格），在第3步界面上按“布局”按钮，在“数据透视表和数据透视图向导—布局”界面上，将“产品”拖至“行”，将“商标”拖至“列”，再将“采购盒数”拖至“数据”，“确定”后单击“完成”按钮。

02 图书订购信息表
操作步骤：
1)在Sheet4中，选定C2:C56单元格，选择菜单“格式”/“条件格式…”，在条件格式对话框设置
条件为单元格数据等于"女"，单击“格式”按钮，在弹出对话框上设置字体颜色为红色，加粗显示。

2)在Shee1中，在C3中输入公式“=IF(MID(A3,7,1)="1","计算机学院","电子信息学院")”。

其它单
元格作填充。

3)在Shee1中，在H3中输入公式“=4-COUNTBLANK(D3:G3)”。

其它单元格作填充。

4)在Shee1中，在I3中输入公式“=D3*$L$3+E3*$L$4+F3*$L$5+G3*$L$6”。

其它单元格作填充。

5)在Shee1中，在M9中输入公式“=COUNTIF(I3:I50,">100")”。

6)7）略。

03 公司员工人事信息表
操作步骤：
1)在Sheet4中，选定A1单元格，选择菜单“数据”/“有效性…”，在“数据有效性”对话框“设
置”选项卡上设置有效性条件，在“出错警告”选项卡上设置警告样式和错误信息。

确定后输入错误数据时会弹出错误提示。

2)在Shee1中，在B3中输入公式“=UPPER(A3)”。

其它单元格作填充。

3)在Shee1中，在F3中输入公式“=YEAR(NOW())-V ALUE(MID(G3,7,4))”。

其它单元格作填充。

4)在Shee1中，在E31中输入公式“=DGET(A2:J27,3,L3:M4)”。

其它单元格作填充。

5)在Shee1中，在N11中输入公式“=IF(L12=M12,TRUE,FALSE)”。

6)7）略。

04 停车情况记录表
操作步骤：
1)见03中1）。

使用“数据有效性”。

2)在Shee1中，在C9中输入公式“=HLOOKUP(B9,$A$2:$C$3,2,0)”。

其它单元格作填充。

3)在Sheet1中，选定F9:F39单元格，输入公式“=E9:E39-D9:D39”，按CTRL+SHIFT+ENTER，编
辑栏中显示“{=E9:E39-D9:D39}”。

4)在Shee1中，在G9中输入公式“=IF(F9=0,1,IF(MINUTE(F9)<15,HOUR(F9),HOUR(F9)+1)) *C9”。

其它单元格作填充。

公式也可“=IF(F9=0,1,HOUR(F9)+IF(MINUTE(F9)<15,0,1)) *C9”。

5)在Shee1中，在J8中输入公式“=COUNTIF(G9:G39,">=40")”。

在Shee1中，在J9中输入公式“=MAX(G9:G39)”。

6)7）略。

05 学生成绩表
操作步骤：
1）见01中1）。

使用“条件格式”。

2）在Shee1中，在B3中输入公式“=REPLACE(A3,1,0,"2009")”。

其它单元格作填充。

3）在Sheet1中，选定J3:J24单元格，输入公式“=E3:E24+F3:F24+G3:G24+H3:H24+I3:I24”，按CTRL+SHIFT+ENTER，编辑栏中显示“{=E3:E24+F3:F24+G3:G24+H3:H24+I3:I24}”。

4）在Shee1中，在K3中输入公式“=IF(J3>=350,"合格","不合格")”。

其它单元格作填充。

5）在Shee1中，在I28中输入公式“=DCOUNT(A2:K24,5,M2:N3)”。

在Shee1中，在I29中输入公式“=DGET(A2:K24,3,M7:N8)”。

在Shee1中，在I30中输入公式“=DA VERAGE(A2:K24,9,M12:M13)”。

在Shee1中，在I31中输入公式“=DMAX(A2:K24,9,M12:M13)”。

6）7）略。

06 零件检测结果表
操作步骤：
1）见03中1）。

使用“数据有效性”。

2）在Sheet1中，选定D3:D50单元格，输入公式“=B3:B50-C3:C50”，按CTRL+SHIFT+ENTER，编辑栏中显示“{=B3:B50-C3:C50}”。

3）在Shee1中，在E3中输入公式“=IF(D3<4,"合格","不合格")”。

其它单元格作填充。

4）在Shee1中，在K4中输入公式“=COUNTIF(D3:D50,0)”。

在Shee1中，在K5中输入公式“=COUNTIF(E3:E50,"合格")/COUNTA(E3:E50)”。

5）在Shee1中，在K9中输入公式“=FIND(J9,I9,1)”。

6）7）略。

07 用户-年龄-电话升级
操作步骤：
1）见03中1）。

使用“数据有效性”。

2）在Shee1中，在D2中输入公式“=2008-YEAR(C2)”。

其它单元格作填充。

3）在Shee1中，在G2中输入公式“=REPLACE(F2,5,0,"8")”。

其它单元格作填充。

4）在Shee1中，在H2中输入公式“=AND(D2>=40,B2="男")”。

其它单元格作填充。

5）在Shee2中，在B2中输入公式“=COUNTIF(Sheet1!B2:B37,"男")”。

在Shee2中，在B3中输入公式“=COUNTIF(Sheet1!D2:D37,">40")”。

6）7）略。

08 采购表-折扣表
操作步骤：
1）见01中1）。

使用“条件格式”。

2）在Shee1中，在D11中输入公式“=VLOOKUP(A11,$F$2:$G$5,2,0)”。

其它单元格作填充。

3）在E11中输入公式“=IF(B11<$A$4,$B$3,IF(B11<$A$5,$B$4,IF(B11<$A$6,$B$5,$B$6)))”。

其它单元格作填充。

4）在Shee1中，在F11中输入公式“=D11*B11*(1-E11)”。

其它单元格作填充。

5）在Shee1中，在J12中输入公式“=SUMIF(A11:A43,"衣服",B11:B43)”。

在Shee1中，在J13中输入公式“=SUMIF(A11:A43,"裤子",B11:B43)”。

在Shee1中，在J14中输入公式“=SUMIF(A11:A43,"鞋子",B11:B43)”。

在Shee1中，在K12中输入公式“=SUMIF(A11:A43,"衣服",F11:F43)”。

在Shee1中，在K13中输入公式“=SUMIF(A11:A43,"裤子",F11:F43)”。

在Shee1中，在K14中输入公式“=SUMIF(A11:A43,"鞋子",F11:F43)”。

6）7）略。

09 教材订购情况表
操作步骤：
1）见03中1）。

使用“数据有效性”。

2）在Sheet1中，选定I3:I52单元格，输入公式“=G3:G52*H3:H52”，按CTRL+SHIFT+ENTER，编辑栏中显示“{=G3:G52*H3:H52}”。

3）在Shee1中，在L2中输入公式“=COUNTIF(D3:D52,"高等教育出版社")”。

在Shee1中，在L3中输入公式“=COUNTIF(G3:G52,">110")-COUNTIF(G3:G52,">=850")”。

4）在Shee1中，在L8中输入公式“=SUMIF($A$3:$A$52,K8,$I$3:$I$52)”。

其它单元格作填充。

5）在Shee2中，在B2中输入公式“=IF(OR(AND(MOD(A2,4)=0,MOD(A2,100)<>0),MOD(A2,400)=0),"
闰年","平年")”。

6）7）略。

10 医院病人护理统计表
操作步骤：
1）见03中1）。

使用“数据有效性”。

2）在Shee1中，在E3中输入公式“=VLOOKUP(E3,$K$2:$L$5,2,0)”。

其它单元格作填充。

3）在Sheet1中，选定H3:H30单元格，输入公式“=G3:G30-D3:D30”，按CTRL+SHIFT+ENTER，编辑栏中显示“{=G3:G30-D3:D30}”。

4）在Sheet1中，选定I3:I30单元格，输入公式“=F3:F30*H3:H30”，按CTRL+SHIFT+ENTER，编辑栏中显示“{=F3:F30*H3:H30}”。

5）在Shee1中，在N13中输入公式“=DCOUNT(A2:I30,6,K8:M9)”。

在Shee1中，在N22中输入公式“=DSUM(A2:I30,9,K17:K18)”。

6）7）略。

11 员工资料表-职务补贴-折旧
操作步骤：
1）见03中1）。

使用“数据有效性”。

2）在Shee1中，在G3中输入公式“=CONCA TENA TE(MID(E3,7,4),"年",MID(E3,11,2),"月",MID(E3,13,2),"日")”。

其它单元格作填充。

3）在Shee1中，在J3中输入公式“=VLOOKUP(H3,$A$2:$B$6,2,0)”。

其它单元格作填充。

4）在Sheet1中，选定K3:K38单元格，输入公式“=I3:I38*(1+J3:J38)”，按CTRL+SHIFT+ENTER，编辑栏中显示“{=I3:I38*(1+J3:J38) }”。

5）在Shee2中，在E2中输入公式“=SLN(B2,B3,B4*365)”。

在Shee2中，在E3中输入公式“=SLN(B2,B3,B4*12)”。

在Shee2中，在E4中输入公式“=SLN(B2,B3,B4)”。

6）7）略。

12 员工信息表-工龄-职称
操作步骤：
1）见03中1）。

使用“数据有效性”。

2）在Shee1中，在C3中输入公式“=REPLACE(B3,3,0,"0")”。

其它单元格作填充。

3）在Shee1中，在F3中输入公式“=2009-YEAR(E3)”。

其它单元格作填充。

在Shee1中，在H3中输入公式“=2009-YEAR(G3)”。

其它单元格作填充。

4）在Shee1中，在N3中输入公式“=COUNTIF(D3:D66,"男")”。

在Shee1中，在N4中输入公式“=COUNTIF(I3:I66,"高级工程师")”。

在Shee1中，在N5中输入公式“=COUNTIF(H3:H66,">=10")”。

5）在Shee1中，在K3中输入公式“=AND(H3>=20,I3="工程师")”。

其它单元格作填充。

6）7）略。

13 房产销售表
操作步骤：
1）见01中1）。

使用“条件格式”。

2）在Shee1中，在I3中输入公式“=F3*G3”。

其它单元格作填充。

3）在Sheet1中，选定J3:J26单元格，输入公式“=H3:H26*I3:I26”，按CTRL+SHIFT+ENTER，编辑栏中显示“{=H3:H26*I3:I26}”。

4）在Shee2中，在B2中输入公式“=SUMIF(Sheet1!$K$3:$K$26,A2,Sheet1!$I$3:$I$26)”。

其它单元格作填充。

5）在Shee2中，在C2中输入公式“=RANK(B2,$B$2:$B$6)”。

其它单元格作填充。

6）7）略。

14 学生成绩表-优等生
操作步骤：
1）见01中1）。

使用“条件格式”。

2）在Sheet1中，选定F2:F39单元格，输入公式“=C2:C39+D2:D39+E2:E39”，按CTRL+SHIFT+ENTER，编辑栏中显示“{=C2:C39+D2:D39+E2:E39}”。

在Sheet1中，选定G2:G39单元格，输入公式“=F2:F39/3”，按CTRL+SHIFT+ENTER，编辑栏中
显示“{=F2:F39/3}”。

3）在Shee1中，在H2中输入公式“=RANK(G2,$G$2:$G$39)”。

其它单元格作填充。

4）在Shee1中，在I2中输入公式
“=AND(C2>A VERAGE($C$2:$C$39),D2>A VERAGE($D$2:$D$39),E2>A VERAGE($E$2:$E$39))”。

其它单元格作填充。

5）在Shee2中，
在B2中输入公式“=COUNTIF(Sheet1!D2:D39,">=0")-COUNTIF(Sheet1!D2:D39,">=20")”。

在B3中输入公式“=COUNTIF(Sheet1!D2:D39,">=20")-COUNTIF(Sheet1!D2:D39,">=40")”。

在B4中输入公式“=COUNTIF(Sheet1!D2:D39,">=40")-COUNTIF(Sheet1!D2:D39,">=60")”。

在B5中输入公式“=COUNTIF(Sheet1!D2:D39,">=60")-COUNTIF(Sheet1!D2:D39,">=80")”。

在B6中输入公式“=COUNTIF(Sheet1!D2:D39,">=80")-COUNTIF(Sheet1!D2:D39,">=100")”。

6）7）略。

15 学生成绩表(铅球)-贷款
操作步骤：
1）见01中1）。

使用“条件格式”。

2）在Sheet1中，选定B3:B30单元格，输入公式“=REPLACE(A3:A30,5,0,"5")”，按CTRL+SHIFT+ENTER，编辑栏中显示“{=REPLACE(A3:A30,5,0,"5") }”。

3）在Shee1中，
在F3中输入公式“=IF(OR(AND(D3="男",E3<14),AND(D3="女",E3<16)),"合格","不合格")”。

在F3中输入公式“=IF(OR(AND(D3="男",G3>=7.5),AND(D3="女",G3>=5.5)),"合格","不合格")”。

其它单元格作填充。

4）在Shee1中，在K4中输入公式“=MIN(E3:E30)”。

在Shee1中，在K5中输入公式“=COUNTIF(F3:F30,"合格")”。

5）在Shee2中，在E2中输入公式“=PMT(B4,B3,B2,0,0)”。

在Shee2中，在E3中输入公式“=IPMT(B4/12,9,B3*12,B2,0)”。

6）7）略。

16 销售统计表
操作步骤：
1）见03中1）。

使用“数据有效性”。

2）在Shee1中，在F3中输入公式“=VLOOKUP(F3,$A$2:$C$10,2,0)”。

其它单元格作填充。

在Shee1中，在G3中输入公式“=VLOOKUP(F3,$A$2:$C$10,3,0)”。

其它单元格作填充。

3）在Sheet1中，选定L3:L44单元格，输入公式“=H3:H44*I3:I44”，按CTRL+SHIFT+ENTER，编辑栏中显示“{=H3:H44*I3:I44}”。

4）在Shee1中，在O3中输入公式“=SUMIF($K$3:$K$44,N3,$L$3:$L$44)”。

其它单元格作填充。

5）在Shee1中，在P3中输入公式“=RANK(O3,$O$3:$O$5)”。

其它单元格作填充。

6）7）略。

17 公务员考试成绩表
操作步骤：
1）见01中1）。

使用“条件格式”。

2）在Shee1中，在H3中输入公式“=IF(G3="博士研究生","博士",IF(G3="硕士研究生","硕士",IF(G3="
本科","学士","无")))”。

其它单元格作填充。

3）在Sheet1中，选定J3:J18单元格，输入公式“=(I3:I18/3)*60%”，按CTRL+SHIFT+ENTER，编辑栏中显示“{=(I3:I18/3)*60%}”。

选定L3:L18单元格，输入公式“=K3:K18*40%”，按CTRL+SHIFT+ENTER。

选定M3:M18单元格，输入公式“=J3:J18+L3:L18”，按CTRL+SHIFT+ENTER。

4）A)将Sheet1中“公务员考试成绩表”复制到Sheet2中。

B)在Sheet2中选定J3:J18单元格，输入公式“=(I3:I18/2)*60%”，按CTRL+SHIFT+ENTER，编辑
栏中显示“{=(I3:I18/2)*60%}”。

5）在Shee2中，在N3中输入公式“=RANK(M3,$M$3:$M$18)”。

其它单元格作填充。

6）7）略。

18 温度情况表
操作步骤：
1）见03中1）。

使用“数据有效性”。

2）在Shee1中，在D3中输入公式“=IF(B3>=C3,"杭州","上海")”。

其它单元格作填充。

3）在Sheet1中，选定E3:E17单元格，输入公式“=B3:B17-C3:C17”，按CTRL+SHIFT+ENTER，编辑栏中显示“{=B3:B17-C3:C17}”。

4）在Shee1中，在C19中输入公式“=MAX(B3:B17)”。

在Shee1中，在C20中输入公式“=MIN(B3:B17)”。

在Shee1中，在C21中输入公式“=MAX(C3:C17)”。

在Shee1中，在C22中输入公式“=MIN(C3:C17)”。

5）A)将Sheet1中“温度情况表”复制到Sheet2中。

B)在Sheet2中选定E3:E17单元格，输入公式“=ABS(B3:B17-C3:C17)”，按CTRL+SHIFT+ENTER，
编辑栏中显示“{=ABS(B3:B17-C3:C17)}”。

6）7）略。

19 通讯费年度计划表
操作步骤：
1）见01中1）。

使用“条件格式”。

2）在Shee1中，在D4中输入公式“=VLOOKUP(C4,$K$4:$L$12,2,0)”。

其它单元格作填充。

3）在Shee1中，在G4中输入公式“=INT((F4-E4)/30)”。

其它单元格作填充。

4）在Sheet1中，选定H4:H26单元格，输入公式“=D4:D26*G4:G26”，按CTRL+SHIFT+ENTER，编辑栏中显示“{=D4:D26*G4:G26}”。

5）在Shee1中，在G2中输入公式“=SUM(H4:H26)”。

在Shee1中，在F2中输入公式“=C2”。

选择菜单“格式”/“单元格…”，在“单元格格式”对话框的“数字”选项卡上设置分类为“特殊”，类型为“中文大写数字”。

6）7）略。

20 学生成绩表(1级)-投资情况表
操作步骤：
1）见01中1）。

使用“条件格式”。

2）在Sheet1中，选定J3:J57单元格，输入公式“=D3:D57+E3:E57+F3:F57+G3:G57+H3:H57+I3:I57”，按CTRL+SHIFT+ENTER，编辑栏中显示“{=D3:D57+E3:E57+F3:F57+G3:G57+H3:H57+I3:I57}”。

3）在Shee1中，在C3中输入公式“=MID(A3,8,1)”。

其它单元格作填充。

4）在Shee1中，在N2中输入公式“=COUNTIF(C3:C57,1)”。

在Shee1中，在N3中输入公式“=COUNTIF(J3:J57,">=60")”。

在Shee1中，在N4中输入公式“=SUMIF(C3:C57,1,J3:J57)/N2”。

5）在Shee1中，在B7中输入公式“=FV(B3,B5,B4,B2,0)”。

在Shee1中，在E7中输入公式“=PV(E3,E4,E2,0,0)”。

6）7）略。