Ecel數據分析教程 第4章上機題及課后習題

上機題4

1、利用LEFT和RIGHT函數判斷客戶性別

本練習的目標是在客戶登記表中，通過客戶身份證號碼(身份證號碼第17

位奇數為男，偶數為女)，利用函數判斷客戶的性別，主要涉及到LEFT、RIGHT、

MOD和IF函數的相關知識。原始數據如圖所示：

ABCD

客戶登記表

1

2姓名住址性別

3柳愿510775XXXXXXXXXX21江蘇省揚州市

4楊源喜531772XXXXXXXXXX52江蘇省無錫市

5馬羽510681XXXXXXXXXX23西藏自治區(qū)拉薩市

6劉衛(wèi)中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西藏自治區(qū)將薩市

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))”繼續(xù)嵌套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員工檔案

部門算

姓名工號籍貫畢業(yè)院校專業(yè)學歷入職時間聯(lián)系電話

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重慶電子科技大學軟件開發(fā)博士2010年5月147******79

9羅遠浩售后部男28YFU2湖南電子科技大學軟件開發(fā)碩士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.部門

畢業(yè)院校學歷

u專業(yè)入職時間

□

6聯(lián)系電話

該公式含義是，如果VLOOKUP函數返回的值錯誤，則不會在單元格中顯

示類似“#VALUE!”這樣的錯誤提出，而是顯示為空，否則（即VLOOKUP函

數返回的值正確）就顯示得到的結果。

然后在編輯欄中選中該公式，復制到其他空白單元格中，每個新公式只需要

更改VLOOKUP函數的返回列值就可以了。這樣，就得到了一個簡歷中所有空

白單元格的查詢結果值。

現在，需要將整個簡歷表復制到另外的地方，如圖所示：

單元格時使用的是絕對引用了，如果當時使用了相對引用，則后面復制的這幾個

表又得全部重新改公式！最終的結果如圖所示：

ABCDEFGHIJKLM

1個人簡歷個人簡歷

姓名雷民倒！里年齡28姓名馬羽倒女年齡28

2JIJ

籍貫四川工號YF324部門銷售部籍貫四川工號YF587后勤部

3

畢業(yè)院校上海財經大學學歷本科畢業(yè)院校西南財經大學學歷本科

4

犯工商管理入職時間：2008年6月1日專業(yè)會計入職時間;2008年5月1日

5

聯(lián)系電話141******23聯(lián)系電話147******98

6

7

8個人簡歷個人簡歷

姓名馬耀華性5。里年齡28姓名羅遠浩年齡28

9

籍貫重慶工號YF587部門技術部籍貫湖南工號YFU2部門售后部

10

畢業(yè)院校:電子科技大學學歷博士畢業(yè)院校］電子科技大學學歷碩士

11

專業(yè)軟件開發(fā)入職時間如10年5月1日專業(yè)軟件開發(fā)入職時間：2010年5月1日

12

聯(lián)系電話147******79聯(lián)系電話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單元格

區(qū)域，注意選擇不帶格式填充；

第2步,在E5單元格輸入“=HOUR(C5-B5)”，并填充到E6:E19單元格區(qū)

域，注意選擇不帶格式填充；

第3步，在F5單元格輸入“=DAY(C5-B5)”，并填充到F6:F19單元格區(qū)域,

注意選擇不帶格式填充；

第4步，在G5單元格輸入

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

意選擇不帶格式填充；

第5步，在H5輸入“=G5*2"，并填充到H6:H19單元格區(qū)域，注意選擇不

帶格式填充。

第6步，在H20用SUM函數對H5:H19單元格區(qū)域求各。最終完成的表格

如圖所示：

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

/續(xù)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生證發(fā)出

月日字號單價金嵌單價金嵌單價金■

31月初結存500120.0096,000.00

5記5電入材料350116.56

8記6立二k打270

9記12題人材料S80123.15

11記15發(fā)出材料400

15記36誨入材料425115.45

17ic47二二匚13€0

19記53融人材料160116.25

22記54發(fā)出材料140

26記61貶入材料175122.36

29記71發(fā)出材料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___________發(fā)上

4月亙!魚

金嵌金嵌

531月嶗結存!?

350*116.5€

6(_5汨540,796.00,

發(fā)出材糕.

78記6-270

89記12由入材料S50123.1546.797.00

911記15發(fā)出材料-:二

1015記36用人材料425115.4549.066.25

17^47發(fā)出材料-

②在17單元格中計算發(fā)出材料的平均單價，因為上月結存的材料是按單價

120購入的，而本月5日購入的材料是按單價116.56購入的，兩次單價不一樣,

所以需要算一個平均單價作為本月8日發(fā)出該材料的統(tǒng)一單價。但是，因為上月

結存的數量和本月5日購買的數量又不一樣，無法直接對兩次單價求平均值，只

好分別把結存的、本月5日購入的算出總金額，再分別把結存的、本月5日購入

的算出總數量，由這個總金額除以總數量來得出單價，因此在17單元格中應輸

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

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

AECDEFG1HIJIL乂

1原材料明細賬

材料名福聞噌：甲材料計■單位：千克

2:

生證發(fā)出結Q

32017______?RA

4月日字號金嵌單價I金嵌單價金嵌

月初結存!

531300]120.0096.000.002

65記5也入材料13嗎116.5€40.796.00I

發(fā)出材科］1

8記6270118.951

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

③在J7單元格輸入公式"=PRODUCT(I7,H7)”，計算出3月8日發(fā)出材料

的總金額，因為雖然有了上月結存的數量和3月5日購入的總金額，但這些總金

額所對應的材料數量并沒有全部在3月8日發(fā)出去，所以要按3月8日實際發(fā)

出的數量，乘以平均單價，得到3月8日實際發(fā)出的材料金額，如圖所示：

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

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

原材料明細賬

關別:材箕名再犯規(guī)唱：目材料計■單位:千克

2017憑證螃6發(fā)

月日字號an單價金陵??單價金嵌

1月切結存800

[

5記5JS入材料350116.5€40.796.00

[32,117.32.

8記6發(fā)出材林

1________在1￡士邕

④在K7單元格輸入“=K5+E6-H7”，得出3月8日發(fā)出材料后的庫存數

量，同時可以根據已在17單元格中得出的平均單價乘以3月8日發(fā)出材料后的

庫存數量，來算出3月8日發(fā)出材料后的庫存金額，但為了財務科目的可讀性,

可以先在L7單元格輸入“=17”，將單價轉到L7單元格上，然后使用

“=PRODUCT(K7:L7)”公式求出3月8日發(fā)出材料后的庫存金額，如圖所示：

M7▼：A=PRODUCT(K7,L7)

ABCDFGHIJILX

1原材料明細賬

材標名楓C現噌：用材料計■^位：千克

2

32017憑證收入發(fā)E岸存

4月日字號金嵌單價金嵌單位金酸

531月工;生存800120.0096,000.00

65記5也人材糕350116.5€40.796.00

-8記6發(fā)出初判-270118.9532.117.32880118.95104.678.68

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

⑤使用同樣方法計算3月II日、17日、22日、29日的發(fā)出和結存的單價、

數量和金額。具體方法是，選中I7:M7區(qū)域，然后向下填充到I15:M15單元格,

如圖所示：

原材料明細賬

關我：材料名稱無現帽：口材料計■單位：干克

J

3想商發(fā)E

4月日字號單價金嵌單價金璇單價金厥

531月初結存800120.00笫000.00

65記5購入材督350116.5640.796.00

78記6發(fā)出材料-270118.9532,117.32B80118.95104.678.68

rr

89X1215人材料現123.15姐797.00'?DB70!?DIV/0!0?DIV/0!?DB70：

911記15發(fā)去材住-400120.224s.087.52B60120.22103.S8B.16

rr

1015￡36也人材料425115.4549.066.25'?DB70!?DIV/0!0?DIV/0!?DIV/0：

1117￡47發(fā)出材料-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發(fā)出材標-180118.9321,406.50940118.93111.789.50

本月告計

1631

然后手工將顯示“#D1V/O!”錯誤的那兒行單元格清空內容就是了。注意：

此處向下填充公式時，一定要將I7:M7區(qū)域(也就是這一行)中所有公式一起選

中后向下填充，如果只選某一個單元格的公式向下填充，比如只選17單元格向

下填充，結果如圖所示：

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

|AB,C,DE,F,G編輯中二I_JI_1

原材料明藏

蝴：材料名稱就現咯:口材料計M位：千克

35憑證發(fā)出

4單價金嵌單價金嵌單

521月初結存800120

e5記5也入材料350116.5640,796.00

78x6發(fā)出材模-Z70118.9532,117.32880118

S9記12購人村模380123.1546.797.00?DIV/0!

g11記15發(fā)出材模-400120.22

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

17X47發(fā)出材群-360115.45

ii，

1219記53應入材料160116.251S.600.00?DIV/0!

1322記54發(fā)出材標-140116.25

1426iE61購入材標175122.3621,413.00wDIV/0!

lz29記71發(fā)出材料-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單元格區(qū)域要使用絕對引用的原因，否則到G16單元格時，判斷條件所

在的單元格區(qū)域就會變成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憑證發(fā)上存

4月字號單價金強9M單價金嵌皿單價金強

521月初結存500120.0096.000.00

65記5購入材料350116.5€40.796.00

78記6發(fā)出材糕-270118.9532.117.32B80118.95104.678.68

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

911記15發(fā)出材稱-400120.2248,087.52S60120.22103.33s.16

1015記36425115.4549.066.25

1117X47發(fā)口村林-118.6442.710.9692511S.64109.743.45

1219記53電人材料160116.2518.600.00

1322記54發(fā)出材料-140118.2916,560.4411S.29111.783.00

142€1261購入材料175122.3621.413.00

15291571發(fā)出材料-180118.9321.406.50?40118.93111.789.50

3：月鼾畬，'118.57

ie31176672.25

17118.754T

18

19此單兀格卬的公式與電子表信中該區(qū)域中的公式不同。

不過，此時Excel又自作聰明地指出F16單元格的公式與該區(qū)域(即該單元

格兩側單元格)的公式不同。不要理它，或選擇“忽略錯誤”即可。

⑦使用相同的方法計算出本月發(fā)出材料的總數量、總金額和平均單價：先

選中E16單元格后直接按Ctrl+C復制，然后選擇H16,點擊“選擇性粘貼I

粘貼公式”，你會發(fā)現新公式中單元格引用都是正確的，但結果值為0,這是因

為該函數的第二個參數還是“購入材料”，因為這個參數是無法自動更新的，所

以只有手動改成“發(fā)出材料”，結果就正確了。然后同樣向右填充到J16,并把

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

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

ABCDEFGHIJ

原材料麗麗

關利：材標名腑c現稻：用材料計■捶位：干克

2017憑證陋發(fā)出

月日字號am單價金皺單價金厥9M單價金靛

：1月城存600120.0096.000.00

5記5電人材潛350116.★，40.796.00

8記6發(fā)出材料-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發(fā)出材稱-360118.6442,710.96925118.64109.743.45

19記53有人材料160116.2518,600.00

22記54發(fā)出材料140118.2916.湖.44X5118.29111.78100

26?61融人材料175122.3621.413.00

29記71發(fā)出材料-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憑證照發(fā)出

月日字號單價金蹶單價金嵌9M單價金嵌

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

5x5也入材料350116.5640,796.00

3