Ecel數(shù)據(jù)分析教程 第4章上機(jī)題及課后習(xí)題

上機(jī)題4

1、利用LEFT和RIGHT函數(shù)判斷客戶性別

本練習(xí)的目標(biāo)是在客戶登記表中，通過(guò)客戶身份證號(hào)碼(身份證號(hào)碼第17

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

MOD和IF函數(shù)的相關(guān)知識(shí)。原始數(shù)據(jù)如圖所示：

ABCD

客戶登記表

1

2姓名住址性別

3柳愿510775XXXXXXXXXX21江蘇省揚(yáng)州市

4楊源喜531772XXXXXXXXXX52江蘇省無(wú)錫市

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

6劉衛(wèi)中510736XXXXXXXXXX30湖北省武漢市

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

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

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

10劉艷麗510728XXXXXXXXXX66四川省宜宣市

11羅云潔510131XXXXXXXXXX21四川省德陽(yáng)市

12麥苗510723XXXXXXXXXX42廣東省潮州市

13張愛國(guó)510743XXXXXXXXXX32吉林省長(zhǎng)春市

14任正義510786XXXXXXXXXX18四川省綿陽(yáng)市

rm,?一、斗

15羅邁浩510765XXXXXXXXXX32四川省西昌市

16陳亦民510733XXXXXXXXXX42廣東省惠州市

17馬耀華510725XXXXXXXXXX15四川省樂山市

18李光東510621XXXXXXXXXX11福建省廈門市

操作步驟

①選擇D3單元格，在“公式|函數(shù)庫(kù)”選項(xiàng)卡中點(diǎn)擊“插入函數(shù)”按鈕，

在彈出的“插入函數(shù)”對(duì)話框中依次選擇“文本函數(shù)|RIGHT”選項(xiàng)，以插入該

函數(shù)，如圖所示：

X,云二

BCD插入由數(shù)

客戶登記表

b證號(hào)碼住址搜索團(tuán)數(shù)⑤：

=

XXXXXXXX21江蘇省揚(yáng)州市清城入一條簡(jiǎn)短說(shuō)明來(lái)才

XXXXXXXX52汀蘇省無(wú)錫市

XXXXXXXX23西藏自治區(qū)將薩市

XXXXXXXX30湖北省武漢市

XXXXXXXX25四川省成都市

XXXXXXXX23汀西省南昌市

XXXXXXXX21浙汀省溫州市選擇函數(shù)(N):

XXXXXXXX66四川省官■真布

XXXXXXXX21四川省德陽(yáng)市REPT

XXXXXXXX42廣東省翔州市IRIGHTI'

11ZIA1，-k_

②在“函數(shù)參數(shù)”對(duì)話框中，在“Text”文本框中選擇B3,在“Num_chars”

文本框中輸入2,如圖所示：

函數(shù)參數(shù)7X

RIGHT

TextB3■510775XXXXXXXXXX21'

Numchars22

"21"

從T文本字符串的最后開始返回指定個(gè)數(shù)的字符

Numchars要提取的字符數(shù)；如果忽略,為1

計(jì)算結(jié)果=21

有關(guān)該團(tuán)數(shù)的超勘(H)取消

③再次選中D3單元格后，鼠標(biāo)移動(dòng)到編輯欄，在已經(jīng)存在的

“=RIGHT(B3,2)”輸入外面嵌入LEFT函數(shù)“=LEFT(RIGHT(B3,2))”,如圖所示：

A=LEFT(RIGHT(B3,2))

B|CDE

客戶登記表

住址性別

(XXXXXXX21|江蘇省揚(yáng)州市|2*

(XXXXXXX52~|一一香克―由11

④再次選擇D3單元格后，鼠標(biāo)移動(dòng)到編輯欄，為已經(jīng)存在的公式

“=LEFT(RIGHT(B3,2))”繼續(xù)嵌套MOD函數(shù)和IF函數(shù)，如圖所示：

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

BCDEF

客戶登記表

住址性別

KXXXXXXX21|汀蘇省揚(yáng)州市I女J

KXXXXXXX52~反蘇留無(wú)—II

最后將該公式填充到D列余下的單元格就可以了。

說(shuō)明：MOD函數(shù)是取余函數(shù)，用于返回兩個(gè)數(shù)相除后的余數(shù)，語(yǔ)法格式為：

=MOD(number,divisor)

其中：

number：被除數(shù)

divisor：除數(shù)

本例中，該函數(shù)的被除數(shù)為L(zhǎng)EFT函數(shù)和RIGHT函數(shù)返回的數(shù)字，除數(shù)為

2,將返回1或2,分別表示奇數(shù)或偶數(shù)。

2、利用VLOOKUP函數(shù)制作個(gè)人簡(jiǎn)歷

本練習(xí)將使用查找與引用函數(shù)，利用員工個(gè)人資料檔案來(lái)制作個(gè)人簡(jiǎn)歷，會(huì)

用到IF函數(shù)、VLOOKUP函數(shù)和ISERROR函數(shù)。原始文件有兩個(gè)，其中存放數(shù)

據(jù)的文件如圖所示：

ABCDEFGHIJK

1員工檔案

部門算

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

2

3雷民消售部里28YF324四川上海財(cái)經(jīng)大學(xué)工商管理本科2008年6月141******23

4楊源宜人事部男21YF227上海復(fù)旦大學(xué)人力奧源管理碩士2009年11月142******42

5柳原消售部男24YF366北京北京物資學(xué)院工商管理本科2011年7月141******00

6馬羽后勤部女28YF587四川西南財(cái)經(jīng)大學(xué)會(huì)計(jì)本科2008年5月147******98

7侗明玉總經(jīng)辦女24YFU2四川四川大學(xué)工商管理碩士2008年3月145******50

8馬耀華技術(shù)部里28YF587重慶電子科技大學(xué)軟件開發(fā)博士2010年5月147******79

9羅遠(yuǎn)浩售后部男28YFU2湖南電子科技大學(xué)軟件開發(fā)碩士2010年5月142******37

操作步驟

在“簡(jiǎn)歷”工作表的D2單元格輸入函數(shù)公式：

=IF（ISERROR（VLOOKUP（B2,員工個(gè)人資料檔案!$A$3:$K$9,3,

FALSE））,"",VLOOKUP（B2,員工個(gè)人資料檔案!$A$3:$K$9,3,FALSE））

如圖所示（其中ISERROR（value）函數(shù)用于測(cè)試返回值是否有錯(cuò)）:

=IF（ISERROR（VLOOKUP（B2,員工個(gè)人資料檔案好A$3：$K$93

"FALSE））：二VLOOKUP（B2,員工個(gè)人資料檔案好A$3：$K$9,3,FALSE））

ABCDE

個(gè)人簡(jiǎn)歷

?姓名

上.................?

籍貫

3Bi.部門

畢業(yè)院校學(xué)歷

u專業(yè)入職時(shí)間

□

6聯(lián)系電話

該公式含義是，如果VLOOKUP函數(shù)返回的值錯(cuò)誤，則不會(huì)在單元格中顯

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

數(shù)返回的值正確）就顯示得到的結(jié)果。

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

更改VLOOKUP函數(shù)的返回列值就可以了。這樣，就得到了一個(gè)簡(jiǎn)歷中所有空

白單元格的查詢結(jié)果值。

現(xiàn)在，需要將整個(gè)簡(jiǎn)歷表復(fù)制到另外的地方，如圖所示：

單元格時(shí)使用的是絕對(duì)引用了，如果當(dāng)時(shí)使用了相對(duì)引用，則后面復(fù)制的這幾個(gè)

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

ABCDEFGHIJKLM

1個(gè)人簡(jiǎn)歷個(gè)人簡(jiǎn)歷

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

2JIJ

籍貫四川工號(hào)YF324部門銷售部籍貫四川工號(hào)YF587后勤部

3

畢業(yè)院校上海財(cái)經(jīng)大學(xué)學(xué)歷本科畢業(yè)院校西南財(cái)經(jīng)大學(xué)學(xué)歷本科

4

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

5

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

6

7

8個(gè)人簡(jiǎn)歷個(gè)人簡(jiǎn)歷

姓名馬耀華性5。里年齡28姓名羅遠(yuǎn)浩年齡28

9

籍貫重慶工號(hào)YF587部門技術(shù)部籍貫湖南工號(hào)YFU2部門售后部

10

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

11

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

12

聯(lián)系電話147******79聯(lián)系電話142******37

13

3、用日期與時(shí)間函數(shù)計(jì)算停車費(fèi)用

本練習(xí)將使用日期與時(shí)間函數(shù)計(jì)算停車費(fèi)用。原始數(shù)據(jù)如圖所示:

ABCDEFG__H__

I,綿州商城停車場(chǎng)計(jì)時(shí)收費(fèi)表

2I斷司：201眸2月11日

車牌號(hào)停車時(shí)間離開時(shí)1旬——l更廣時(shí)間I--------------應(yīng)收費(fèi)

______________________________________________喇小時(shí)|菠累積時(shí)數(shù)________

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_________

|停車總計(jì)：總計(jì)

21I員工簽S日期

空：經(jīng)理簽名日期

操作步驟

第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函數(shù)對(duì)H5:H19單元格區(qū)域求各。最終完成的表格

如圖所示：

ABCDEFGH

J綿州商城停車場(chǎng)計(jì)時(shí)收費(fèi)表

時(shí)間：2015年2月11日

景計(jì)時(shí)間

車牌號(hào)停車時(shí)間需開時(shí)間應(yīng)收費(fèi)

分神1小時(shí)1天數(shù)攝積時(shí)數(shù)

綿

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

車

停

總計(jì)

總計(jì)

￥16600

日

11siss期

日期

2

4、利用數(shù)學(xué)與三角函數(shù)管理原材料明細(xì)賬

本練習(xí)的目標(biāo)是在已經(jīng)提供了原材料明細(xì)賬表格的基礎(chǔ)上，通過(guò)數(shù)學(xué)和三角

函數(shù)，對(duì)數(shù)據(jù)進(jìn)行相應(yīng)處理，即可實(shí)現(xiàn)對(duì)原材料的管理。在Excel中使用

PRODUCT函數(shù)計(jì)算期初余額，使用SUM函數(shù)匯總結(jié)存的數(shù)量、金額，用

PRODUCT函數(shù)計(jì)算出單價(jià)、金額，用TRUNC函數(shù)對(duì)單價(jià)進(jìn)行截尾取整。原文

件如圖所示：

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

原材料明細(xì)賬

竭：材耗名稱e押§：口材料計(jì)量單位：干克

2017生證發(fā)出

月日字號(hào)單價(jià)金嵌單價(jià)金嵌單價(jià)金■

31月初結(jié)存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本月合計(jì)

操作步驟

①選擇G6單元格中輸入E6和F6之乘積，可用乘號(hào)，但最好用PRODUCT

函數(shù)“=PRODUCT(E6,F6)”，計(jì)算出3月5日購(gòu)入甲材料花費(fèi)的費(fèi)用，并填充

到G6:Gl5中，計(jì)算出其他日期購(gòu)入材料的費(fèi)用，如圖所示：

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

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

F材料明細(xì)賬

2詡：材料名和C現(xiàn)唱：甲材料計(jì)位：千克

32017,憑證1___________發(fā)上

4月亙!魚

金嵌金嵌

531月嶗結(jié)存!?

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單元格中計(jì)算發(fā)出材料的平均單價(jià)，因?yàn)樯显陆Y(jié)存的材料是按單價(jià)

120購(gòu)入的，而本月5日購(gòu)入的材料是按單價(jià)116.56購(gòu)入的，兩次單價(jià)不一樣,

所以需要算一個(gè)平均單價(jià)作為本月8日發(fā)出該材料的統(tǒng)一單價(jià)。但是，因?yàn)樯显?/p>

結(jié)存的數(shù)量和本月5日購(gòu)買的數(shù)量又不一樣，無(wú)法直接對(duì)兩次單價(jià)求平均值，只

好分別把結(jié)存的、本月5日購(gòu)入的算出總金額，再分別把結(jié)存的、本月5日購(gòu)入

的算出總數(shù)量，由這個(gè)總金額除以總數(shù)量來(lái)得出單價(jià)，因此在17單元格中應(yīng)輸

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

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

AECDEFG1HIJIL乂

1原材料明細(xì)賬

材料名福聞噌：甲材料計(jì)■單位：千克

2:

生證發(fā)出結(jié)Q

32017______?RA

4月日字號(hào)金嵌單價(jià)I金嵌單價(jià)金嵌

月初結(jié)存!

531300]120.0096.000.002

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

發(fā)出材科］1

8記6270118.951

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

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

的總金額，因?yàn)殡m然有了上月結(jié)存的數(shù)量和3月5日購(gòu)入的總金額，但這些總金

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

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

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

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

原材料明細(xì)賬

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

2017憑證螃6發(fā)

月日字號(hào)an單價(jià)金陵??單價(jià)金嵌

1月切結(jié)存800

[

5記5JS入材料350116.5€40.796.00

[32,117.32.

8記6發(fā)出材林

1________在1￡士邕

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

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

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

可以先在L7單元格輸入“=17”，將單價(jià)轉(zhuǎn)到L7單元格上，然后使用

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

M7▼：A=PRODUCT(K7,L7)

ABCDFGHIJILX

1原材料明細(xì)賬

材標(biāo)名楓C現(xiàn)噌：用材料計(jì)■^位：千克

2

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

4月日字號(hào)金嵌單價(jià)金嵌單位金酸

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

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

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

如圖所示：

原材料明細(xì)賬

關(guān)我：材料名稱無(wú)現(xiàn)帽：口材料計(jì)■單位：干克

J

3想商發(fā)E

4月日字號(hào)單價(jià)金嵌單價(jià)金璇單價(jià)金厥

531月初結(jié)存800120.00笫000.00

65記5購(gòu)入材督350116.5640.796.00

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

rr

89X1215人材料現(xiàn)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ā)出材標(biāo)-180118.9321,406.50940118.93111.789.50

本月告計(jì)

1631

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

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

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

下填充，結(jié)果如圖所示：

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

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

原材料明藏

蝴：材料名稱就現(xiàn)咯:口材料計(jì)M位：千克

35憑證發(fā)出

4單價(jià)金嵌單價(jià)金嵌單

521月初結(jié)存800120

e5記5也入材料350116.5640,796.00

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

S9記12購(gòu)人村模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應(yīng)入材料160116.251S.600.00?DIV/0!

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

1426iE61購(gòu)入材標(biāo)175122.3621,413.00wDIV/0!

lz29記71發(fā)出材料-180122.3€

即只有其后第一個(gè)有效單元格（19）能得到正確值，后面的幾個(gè)有效單元格

如111、113、115都不能得到正確值。為什么呢？因?yàn)镮ll（即17日）的數(shù)據(jù)依

賴于K9、M9、E10、G10這四個(gè)單元格的數(shù)據(jù)，而現(xiàn)在只有E10、G10有數(shù)據(jù)，

K9、M9都為空。

⑥在E16單元格輸入“=SUMIF（$D$6:$D$15,"購(gòu)入材料”,E6:E15）”，計(jì)算

本月購(gòu)入材料的總數(shù)量；然后向右側(cè)填充公式到G16,這就是為什么公式中

D6:D15單元格區(qū)域要使用絕對(duì)引用的原因，否則到G16單元格時(shí)，判斷條件所

在的單元格區(qū)域就會(huì)變成F6:F15了。不過(guò)，中間經(jīng)過(guò)的F16在計(jì)算完成后清空

后重新計(jì)算，因?yàn)椴豢赡苡袉蝺r(jià)求和的說(shuō)法，單價(jià)只能求平均。但如果使用

“=SUM（F6:F15）/COUNTA（F6:F15）”的方法來(lái)求平均單價(jià)，由于每期購(gòu)入的數(shù)

量不一致，會(huì)導(dǎo)致此種方法得出的結(jié)果與實(shí)際情況有出入（本例中此種方法得到

的值是118.754）因此正確的做法是用本月總計(jì)購(gòu)入金額除以本月總計(jì)購(gòu)入數(shù)量,

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

我們使用截尾取整函數(shù)TRUNC保留其2位小數(shù),即“=TRUNC（G16/E16,2）”，

如圖所示：

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

ABCDEFGHIJKLM

1原材料明細(xì)賬

材林名郡隹現(xiàn)喑甲材料計(jì)?單位：

2〒克

32017憑證發(fā)上存

4月字號(hào)單價(jià)金強(qiáng)9M單價(jià)金嵌皿單價(jià)金強(qiáng)

521月初結(jié)存500120.0096.000.00

65記5購(gòu)入材料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購(gòu)入材料175122.3621.413.00

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

3：月鼾畬，'118.57

ie31176672.25

17118.754T

18

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

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

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

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

選中E16單元格后直接按Ctrl+C復(fù)制，然后選擇H16,點(diǎn)擊“選擇性粘貼I

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

為該函數(shù)的第二個(gè)參數(shù)還是“購(gòu)入材料”，因?yàn)檫@個(gè)參數(shù)是無(wú)法自動(dòng)更新的，所

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

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

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

ABCDEFGHIJ

原材料麗麗

關(guān)利：材標(biāo)名腑c現(xiàn)稻：用材料計(jì)■捶位：干克

2017憑證陋發(fā)出

月日字號(hào)am單價(jià)金皺單價(jià)金厥9M單價(jià)金靛

：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購(gòu)入材將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本月合計(jì)1490118.57160882.75

⑧在K16輸入“=K5+E16-H16”，得到本月的結(jié)存數(shù)量；在M16輸入

“=M5+G16-J16”，得出本月的結(jié)存金額；最后再用結(jié)存總金額除以結(jié)存總數(shù)量,

就得到結(jié)存單價(jià)，對(duì)此單價(jià)使用四舍五入保留兩位小數(shù)的格式顯示

(=ROUND(M16/K16,2)),結(jié)果如圖所示：

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

ABICIDEF：G‘H’liJ，一

原材料明細(xì)賬

關(guān)別：材料名楓C現(xiàn)嚕：甲材將計(jì)■單位:千克

2017憑證照發(fā)出

月日字號(hào)單價(jià)金蹶單價(jià)金嵌9M單價(jià)金嵌

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

5x5也入材料350116.5640,796.00

3