Ecel数据分析教程 第4章上机题及课后习题

上机题4

1、利用LEFT和RIGHT函数判断客户性别

本练习的目标是在客户登记表中，通过客户身份证号码(身份证号码第17

位奇数为男，偶数为女)，利用函数判断客户的性别，主要涉及到LEFT、RIGHT、

MOD和IF函数的相关知识。原始数据如图所示：

ABCD

客户登记表

1

2姓名住址性别

3柳愿510775XXXXXXXXXX21江苏省扬州市

4杨源喜531772XXXXXXXXXX52江苏省无锡市

5马羽510681XXXXXXXXXX23西藏自治区拉萨市

6刘卫中510736XXXXXXXXXX30湖北省武汉市

7侗明玉510682XXXXXXXXXX25四川省成都市

8秦桂荣510682XXXXXXXXXX23江西省南昌市

9孔婉晴510731XXXXXXXXXX21浙江省温州市

10刘艳丽510728XXXXXXXXXX66四川省宜宣市

11罗云洁510131XXXXXXXXXX21四川省德阳市

12麦苗510723XXXXXXXXXX42广东省潮州市

13张爱国510743XXXXXXXXXX32吉林省长春市

14任正义510786XXXXXXXXXX18四川省绵阳市

rm,♦一、斗

15罗迈浩510765XXXXXXXXXX32四川省西昌市

16陈亦民510733XXXXXXXXXX42广东省惠州市

17马耀华510725XXXXXXXXXX15四川省乐山市

18李光东510621XXXXXXXXXX11福建省厦门市

操作步骤

①选择D3单元格，在“公式|函数库”选项卡中点击“插入函数”按钮，

在弹出的“插入函数”对话框中依次选择“文本函数|RIGHT”选项，以插入该

函数，如图所示：

X,云二

BCD插入由数

客户登记表

b证号码住址搜索团数⑤：

=

XXXXXXXX21江苏省扬州市清城入一条简短说明来才

XXXXXXXX52汀苏省无锡市

XXXXXXXX23西藏自治区将萨市

XXXXXXXX30湖北省武汉市

XXXXXXXX25四川省成都市

XXXXXXXX23汀西省南昌市

XXXXXXXX21浙汀省温州市选择函数(N):

XXXXXXXX66四川省官■真布

XXXXXXXX21四川省德阳市REPT

XXXXXXXX42广东省翔州市IRIGHTI'

11ZIA1，-k_

②在“函数参数”对话框中，在“Text”文本框中选择B3,在“Num_chars”

文本框中输入2,如图所示：

函数参数7X

RIGHT

TextB3■510775XXXXXXXXXX21'

Numchars22

"21"

从T文本字符串的最后开始返回指定个数的字符

Numchars要提取的字符数；如果忽略,为1

计算结果=21

有关该团数的超勘(H)取消

③再次选中D3单元格后，鼠标移动到编辑栏，在已经存在的

“=RIGHT(B3,2)”输入外面嵌入LEFT函数“=LEFT(RIGHT(B3,2))”,如图所示：

A=LEFT(RIGHT(B3,2))

B|CDE

客户登记表

住址性别

(XXXXXXX21|江苏省扬州市|2*

(XXXXXXX52~|一一香克―由11

④再次选择D3单元格后，鼠标移动到编辑栏，为已经存在的公式

“=LEFT(RIGHT(B3,2))”继续嵌套MOD函数和IF函数，如图所示：

JIi=IF(MOD(LEFT(RIGHT(B3,2)),2)=L'男"，"女

BCDEF

客户登记表

住址性别

KXXXXXXX21|汀苏省扬州市I女J

KXXXXXXX52~反苏留无—II

最后将该公式填充到D列余下的单元格就可以了。

说明：MOD函数是取余函数，用于返回两个数相除后的余数，语法格式为：

=MOD(number,divisor)

其中：

number：被除数

divisor：除数

本例中，该函数的被除数为LEFT函数和RIGHT函数返回的数字，除数为

2,将返回1或2,分别表示奇数或偶数。

2、利用VLOOKUP函数制作个人简历

本练习将使用查找与引用函数，利用员工个人资料档案来制作个人简历，会

用到IF函数、VLOOKUP函数和ISERROR函数。原始文件有两个，其中存放数

据的文件如图所示：

ABCDEFGHIJK

1员工档案

部门算

姓名工号籍贯毕业院校专业学历入职时间联系电话

2

3雷民消售部里28YF324四川上海财经大学工商管理本科2008年6月141******23

4杨源宜人事部男21YF227上海复旦大学人力奥源管理硕士2009年11月142******42

5柳原消售部男24YF366北京北京物资学院工商管理本科2011年7月141******00

6马羽后勤部女28YF587四川西南财经大学会计本科2008年5月147******98

7侗明玉总经办女24YFU2四川四川大学工商管理硕士2008年3月145******50

8马耀华技术部里28YF587重庆电子科技大学软件开发博士2010年5月147******79

9罗远浩售后部男28YFU2湖南电子科技大学软件开发硕士2010年5月142******37

操作步骤

在“简历”工作表的D2单元格输入函数公式：

=IF（ISERROR（VLOOKUP（B2,员工个人资料档案!$A$3:$K$9,3,

FALSE））,"",VLOOKUP（B2,员工个人资料档案!$A$3:$K$9,3,FALSE））

如图所示（其中ISERROR（value）函数用于测试返回值是否有错）:

=IF（ISERROR（VLOOKUP（B2,员工个人资料档案好A$3：$K$93

"FALSE））：二VLOOKUP（B2,员工个人资料档案好A$3：$K$9,3,FALSE））

ABCDE

个人简历

?姓名

上.................।

籍贯

3Bi.部门

毕业院校学历

u专业入职时间

□

6联系电话

该公式含义是，如果VLOOKUP函数返回的值错误，则不会在单元格中显

示类似“#VALUE!”这样的错误提出，而是显示为空，否则（即VLOOKUP函

数返回的值正确）就显示得到的结果。

然后在编辑栏中选中该公式，复制到其他空白单元格中，每个新公式只需要

更改VLOOKUP函数的返回列值就可以了。这样，就得到了一个简历中所有空

白单元格的查询结果值。

现在，需要将整个简历表复制到另外的地方，如图所示：

单元格时使用的是绝对引用了，如果当时使用了相对引用，则后面复制的这几个

表又得全部重新改公式！最终的结果如图所示：

ABCDEFGHIJKLM

1个人简历个人简历

姓名雷民倒！里年龄28姓名马羽倒女年龄28

2JIJ

籍贯四川工号YF324部门销售部籍贯四川工号YF587后勤部

3

毕业院校上海财经大学学历本科毕业院校西南财经大学学历本科

4

犯工商管理入职时间：2008年6月1日专业会计入职时间;2008年5月1日

5

联系电话141******23联系电话147******98

6

7

8个人简历个人简历

姓名马耀华性5。里年龄28姓名罗远浩年龄28

9

籍贯重庆工号YF587部门技术部籍贯湖南工号YFU2部门售后部

10

毕业院校:电子科技大学学历博士毕业院校］电子科技大学学历硕士

11

专业软件开发入职时间如10年5月1日专业软件开发入职时间：2010年5月1日

12

联系电话147******79联系电话142******37

13

3、用日期与时间函数计算停车费用

本练习将使用日期与时间函数计算停车费用。原始数据如图所示:

ABCDEFG__H__

I,绵州商城停车场计时收费表

2I断司：201眸2月11日

车牌号停车时间离开时1旬——l更广时间I--------------应收费

______________________________________________喇小时|菠累积时数________

5绵A328562015/2/1011:502015/2/1112:35

6绵A8s6262015/2/118:522015/2/1117:20

7SA381SD2015/2/119:082015/2/1117:56—

8绵A377O12015/2/1111:122015/2/1113:11

9绵A0D0002015/2/1111:132015/2/1115:40

10^A37P112015/2/1111:182015/2/1113:06

11绵B625302015/2/1111:582015/2/1117:32

12维F3-632015/2/1112:002015/271115:03

13绵A474Y12015/2/1112:322015/2/1114:21

14SnAAWOOl2015/2/1113:502015/2/1118:09

15绵A695s62015/2/1114:112015/2/1115:23

16维A56DE42015/2/1115:522015/2/1119:--

17绵A2E3562015/2/1116:002015/2/1117:30

18绵A9785D2015/2/1116:002015/271120:16

19绵A066802015/2/1116:162015/2/1121:09_________

|停车总计：总计

21I员工签S日期

空：经理签名日期

操作步骤

第1步，在D5单元格输入“=MINUTE(C5-B5)”，并填充到D6:D19单元格

区域，注意选择不带格式填充；

第2步,在E5单元格输入“=HOUR(C5-B5)”，并填充到E6:E19单元格区

域，注意选择不带格式填充；

第3步，在F5单元格输入“=DAY(C5-B5)”，并填充到F6:F19单元格区域,

注意选择不带格式填充；

第4步，在G5单元格输入

“=F5*24+E5+IF(D5<15,0,IF(D5<30,0.5,l))”，并填充到G6:G19单元格区域,注

意选择不带格式填充；

第5步，在H5输入“=G5*2"，并填充到H6:H19单元格区域，注意选择不

带格式填充。

第6步，在H20用SUM函数对H5:H19单元格区域求各。最终完成的表格

如图所示：

ABCDEFGH

J绵州商城停车场计时收费表

时间：2015年2月11日

景计时间

车牌号停车时间需开时间应收费

分神1小时1天数摄积时数

绵

dA328562015/2/1011:502015/2/1112:35451

绵A8s6262015/2/118:522015/2/1117:20280

绵A381SD2015/2/119:082015/2/1117:56480

3绵A377O12015/2/1111:122015/2/1113:11590

绵A0D0002015/2/1111:132015/2/1115:40270

3绵A37Pli2015/2/1111:182015/2/1113:0648Q

1第B625302015/2/1111:582015/2/1117:32340

2绵F377632015/2/1112:002015/2/1115:03HH0

3J

绵A474Y12015/2/1112:322015/2/1114:214940o

4

^AAWOOl2015/2/1113:502015/2/1118:09191c

5名A695s62015/2/1114:112015/2/1115:23123o

6

绵A56DE42015/2/1115:522015/2/1119:47551o

/续A2E3562015/2/1116:002015/2/1117:30304o

8编A9785D2015/2/1116:002015/2/1120:16164O

9绵A066802015/2/1116:162015/2/1121:0953¥1

车

停

总计

总计

¥16600

日

11siss期

日期

2

4、利用数学与三角函数管理原材料明细账

本练习的目标是在已经提供了原材料明细账表格的基础上，通过数学和三角

函数，对数据进行相应处理，即可实现对原材料的管理。在Excel中使用

PRODUCT函数计算期初余额，使用SUM函数汇总结存的数量、金额，用

PRODUCT函数计算出单价、金额，用TRUNC函数对单价进行截尾取整。原文

件如图所示：

A,BC,—D£―_________G________H|I|JILM

原材料明细账

竭：材耗名称e押§：口材料计量单位：干克

2017生证发出

月日字号单价金嵌单价金嵌单价金■

31月初结存500120.0096,000.00

5记5电入材料350116.56

8记6立二k打270

9记12题人材料S80123.15

11记15发出材料400

15记36诲入材料425115.45

17ic47二二匚13€0

19记53融人材料160116.25

22记54发出材料140

26记61贬入材料175122.36

29记71发出材料180

31本月合计

操作步骤

①选择G6单元格中输入E6和F6之乘积，可用乘号，但最好用PRODUCT

函数“=PRODUCT(E6,F6)”，计算出3月5日购入甲材料花费的费用，并填充

到G6:Gl5中，计算出其他日期购入材料的费用，如图所示：

G6-：X✓A=PRODUCT(E6,F6)

A,BC|D,EF.G,HI,J

F材料明细账

2诩：材料名和C现唱：甲材料计位：千克

32017,凭证1___________发上

4月亘!鱼

金嵌金嵌

531月崂结存!।

350*116.5€

6(_5汨540,796.00,

发出材糕.

78记6-270

89记12由入材料S50123.1546.797.00

911记15发出材料-:二

1015记36用人材料425115.4549.066.25

17^47发出材料-

②在17单元格中计算发出材料的平均单价，因为上月结存的材料是按单价

120购入的，而本月5日购入的材料是按单价116.56购入的，两次单价不一样,

所以需要算一个平均单价作为本月8日发出该材料的统一单价。但是，因为上月

结存的数量和本月5日购买的数量又不一样，无法直接对两次单价求平均值，只

好分别把结存的、本月5日购入的算出总金额，再分别把结存的、本月5日购入

的算出总数量，由这个总金额除以总数量来得出单价，因此在17单元格中应输

入公式“=SUM(M5,G6)/SUM(K5,E6)”，如图所示:

17-：X✓A=SUM(M5,G6)/SUM(K5,E6)

AECDEFG1HIJIL乂

1原材料明细账

材料名福闻噌：甲材料计■单位：千克

2:

生证发出结Q

32017______«RA

4月日字号金嵌单价I金嵌单价金嵌

月初结存!

531300]120.0096.000.002

65记5也入材料13吗116.5€40.796.00I

发出材科］1

8记6270118.951

7--------------

③在J7单元格输入公式"=PRODUCT(I7,H7)”，计算出3月8日发出材料

的总金额，因为虽然有了上月结存的数量和3月5日购入的总金额，但这些总金

额所对应的材料数量并没有全部在3月8日发出去，所以要按3月8日实际发

出的数量，乘以平均单价，得到3月8日实际发出的材料金额，如图所示：

|J7m：X✓AP^PRODUCT(I7,H7)

D|B|F|G|H|IL1」K

原材料明细账

关别:材箕名再犯规唱：目材料计■单位:千克

2017凭证螃6发

月日字号an单价金陵««单价金嵌

1月切结存800

[

5记5JS入材料350116.5€40.796.00

[32,117.32.

8记6发出材林

1________在1£士邕

④在K7单元格输入“=K5+E6-H7”，得出3月8日发出材料后的库存数

量，同时可以根据已在17单元格中得出的平均单价乘以3月8日发出材料后的

库存数量，来算出3月8日发出材料后的库存金额，但为了财务科目的可读性,

可以先在L7单元格输入“=17”，将单价转到L7单元格上，然后使用

“=PRODUCT(K7:L7)”公式求出3月8日发出材料后的库存金额，如图所示：

M7▼：A=PRODUCT(K7,L7)

ABCDFGHIJILX

1原材料明细账

材标名枫C现噌：用材料计■^位：千克

2

32017凭证收入发E岸存

4月日字号金嵌单价金嵌单位金酸

531月工;生存800120.0096,000.00

65记5也人材糕350116.5€40.796.00

-8记6发出初判-270118.9532.117.32880118.95104.678.68

89记12也入材料380123.1546.797.00=K5+E6-H7=17

⑤使用同样方法计算3月II日、17日、22日、29日的发出和结存的单价、

数量和金额。具体方法是，选中I7:M7区域，然后向下填充到I15:M15单元格,

如图所示：

原材料明细账

关我：材料名称无现帽：口材料计■单位：干克

J

3想商发E

4月日字号单价金嵌单价金璇单价金厥

531月初结存800120.00笫000.00

65记5购入材督350116.5640.796.00

78记6发出材料-270118.9532,117.32B80118.95104.678.68

rr

89X1215人材料现123.15姐797.00'«DB70!«DIV/0!0«DIV/0!»DB70：

911记15发去材住-400120.224s.087.52B60120.22103.S8B.16

rr

1015£36也人材料425115.4549.066.25'«DB70!»DIV/0!0«DIV/0!«DIV/0：

1117£47发出材料-36011S.6442,710.96925118.64109.743.45

r

1219记53存人村科160116.2518.600.00'MDIV/0!sDIV/0!0FlV/0!«DIV/0：

1322迁54天士可以-140118.2916.湖.44^45118.29111.78100

'«DIV/0!r

1426记61175122.3621.413.00'«DIV/0!0«DIV/0!»DIV/0

1529记71发出材标-180118.9321,406.50940118.93111.789.50

本月告计

1631

然后手工将显示“#D1V/O!”错误的那儿行单元格清空内容就是了。注意：

此处向下填充公式时，一定要将I7:M7区域(也就是这一行)中所有公式一起选

中后向下填充，如果只选某一个单元格的公式向下填充，比如只选17单元格向

下填充，结果如图所示：

17：X✓A=SUM(M5,G6)/SUM(K5,E6)

|AB,C,DE,F,G编辑中二I_JI_1

原材料明藏

蝴：材料名称就现咯:口材料计M位：千克

35凭证发出

4单价金嵌单价金嵌单

521月初结存800120

e5记5也入材料350116.5640,796.00

78x6发出材模-Z70118.9532,117.32880118

S9记12购人村模380123.1546.797.00«DIV/0!

g11记15发出材模-400120.22

1015X36也入材料425115.4549,066.25•DIV/0!

17X47发出材群-360115.45

ii，

1219记53应入材料160116.251S.600.00«DIV/0!

1322记54发出材标-140116.25

1426iE61购入材标175122.3621,413.00wDIV/0!

lz29记71发出材料-180122.3€

即只有其后第一个有效单元格（19）能得到正确值，后面的几个有效单元格

如111、113、115都不能得到正确值。为什么呢？因为Ill（即17日）的数据依

赖于K9、M9、E10、G10这四个单元格的数据，而现在只有E10、G10有数据，

K9、M9都为空。

⑥在E16单元格输入“=SUMIF（$D$6:$D$15,"购入材料”,E6:E15）”，计算

本月购入材料的总数量；然后向右侧填充公式到G16,这就是为什么公式中

D6:D15单元格区域要使用绝对引用的原因，否则到G16单元格时，判断条件所

在的单元格区域就会变成F6:F15了。不过，中间经过的F16在计算完成后清空

后重新计算，因为不可能有单价求和的说法，单价只能求平均。但如果使用

“=SUM（F6:F15）/COUNTA（F6:F15）”的方法来求平均单价，由于每期购入的数

量不一致，会导致此种方法得出的结果与实际情况有出入（本例中此种方法得到

的值是118.754）因此正确的做法是用本月总计购入金额除以本月总计购入数量,

即在F16单元格使用上一步已算出的G16除以F16o其值在本例中是118.572,

我们使用截尾取整函数TRUNC保留其2位小数,即“=TRUNC（G16/E16,2）”，

如图所示：

F16▼:A=TRUNC(G16/E16,2)

ABCDEFGHIJKLM

1原材料明细账

材林名郡隹现喑甲材料计・单位：

2〒克

32017凭证发上存

4月字号单价金强9M单价金嵌皿单价金强

521月初结存500120.0096.000.00

65记5购入材料350116.5€40.796.00

78记6发出材糕-270118.9532.117.32B80118.95104.678.68

89记12由人材模SS0123.1546,797.00

911记15发出材称-400120.2248,087.52S60120.22103.33s.16

1015记36425115.4549.066.25

1117X47发口村林-118.6442.710.9692511S.64109.743.45

1219记53电人材料160116.2518.600.00

1322记54发出材料-140118.2916,560.4411S.29111.783.00

142€1261购入材料175122.3621.413.00

15291571发出材料-180118.9321.406.50»40118.93111.789.50

3：月鼾畲，'118.57

ie31176672.25

17118.754T

18

19此单兀格卬的公式与电子表信中该区域中的公式不同。

不过，此时Excel又自作聪明地指出F16单元格的公式与该区域(即该单元

格两侧单元格)的公式不同。不要理它，或选择“忽略错误”即可。

⑦使用相同的方法计算出本月发出材料的总数量、总金额和平均单价：先

选中E16单元格后直接按Ctrl+C复制，然后选择H16,点击“选择性粘贴I

粘贴公式”，你会发现新公式中单元格引用都是正确的，但结果值为0,这是因

为该函数的第二个参数还是“购入材料”，因为这个参数是无法自动更新的，所

以只有手动改成“发出材料”，结果就正确了。然后同样向右填充到J16,并把

中间116清空后输入公式“=TRUNC(J16/H16,2)”，如图所示：

116▼A=TRUNC(J16/H16,2)

ABCDEFGHIJ

原材料丽丽

关利：材标名腑c现稻：用材料计■捶位：干克

2017凭证陋发出

月日字号am单价金皱单价金厥9M单价金靛

：1月城存600120.0096.000.00

5记5电人材潜350116.★，40.796.00

8记6发出材料-270118.9532,117.32880118.95104,678.68

9记12存人材模580123.1546.797.00

11记15天二可包-400120.2248,087.52360120.22103.3S8.16

1:X36购入材将425115.4549,066.25

17iE47发出材称-360118.6442,710.96925118.64109.743.45

19记53有人材料160116.2518,600.00

22记54发出材料140118.2916.湖.44X5118.29111.78100

26©61融人材料175122.3621.413.00

29记71发出材料-180118.9321.406.50118.93111.789.50

176672.依即'119.17

I31本月合计1490118.57160882.75

⑧在K16输入“=K5+E16-H16”，得到本月的结存数量；在M16输入

“=M5+G16-J16”，得出本月的结存金额；最后再用结存总金额除以结存总数量,

就得到结存单价，对此单价使用四舍五入保留两位小数的格式显示

(=ROUND(M16/K16,2)),结果如图所示：

|L16&|=ROUND(M16/K16,2)

ABICIDEF：G‘H’liJ，一

原材料明细账

关别：材料名枫C现噜：甲材将计■单位:千克

2017凭证照发出

月日字号单价金蹶单价金嵌9M单价金嵌

|31月礴存800:二：：箕.000.00

5x5也入材料350116.5640,796.00

3