第二章 Excel二级题操作步骤参考.docx

第二章 Excel二级题操作步骤参考.docx

《第二章 Excel二级题操作步骤参考.docx》由会员分享，可在线阅读，更多相关《第二章 Excel二级题操作步骤参考.docx（23页珍藏版）》请在冰豆网上搜索。

第二章Excel二级题操作步骤参考

第二章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中输入公式“=DAVERAGE（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（））-VALUE（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中输入公式“=DAVERAGE（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中输入公式“=CONCATENATE（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>AVERAGE（$C$2:

$C$39）,D2>AVERAGE（$D$2:

$D$39）,E2>AVERAGE（$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）”。

7）略。