Excel在財(cái)務(wù)中的應(yīng)用 第3版 課件 項(xiàng)目六 Excel在存貨管理中的應(yīng)用

項(xiàng)目六Excel在存貨管理中的應(yīng)用項(xiàng)目一

任務(wù)一采購(gòu)與成品入庫業(yè)務(wù)處理任務(wù)二銷售與車間領(lǐng)料業(yè)務(wù)處理任務(wù)三庫存管理任務(wù)四存貨明細(xì)賬任務(wù)五制定薪金等級(jí)知識(shí)目標(biāo)和能力目標(biāo)知識(shí)目標(biāo)：1.掌握進(jìn)銷存的業(yè)務(wù)處理流程。2.了解進(jìn)銷存管理中數(shù)據(jù)之間的關(guān)系。能力目標(biāo)：1.學(xué)會(huì)使用Excel設(shè)計(jì)進(jìn)銷存管理系統(tǒng)。2.掌握工作表數(shù)據(jù)之間的操作。工作任務(wù)分解及操作1.進(jìn)貨流程：采購(gòu)員接到缺貨信息后，分析缺貨信息是否合理，再將訂單下達(dá)給供應(yīng)商；材料送達(dá)后，實(shí)物入庫，根據(jù)入庫單登記庫存賬。2.銷售流程：接收客戶訂單，簽約銷售合同，向客戶發(fā)貨并收款；每筆銷售業(yè)務(wù)發(fā)生時(shí)都要及時(shí)更新庫存。3.庫存管理流程：材料采購(gòu)入庫、產(chǎn)品完工入庫、領(lǐng)料退貨等均是涉及庫存變化的業(yè)務(wù)，均需在進(jìn)出時(shí)及時(shí)記錄并處理。o1采購(gòu)與成品入庫業(yè)務(wù)處理任務(wù)一采購(gòu)與成品入庫業(yè)務(wù)處理1.建立基礎(chǔ)信息表名稱引用位置范圍供應(yīng)商名稱=OFFSET(基礎(chǔ)信息表!$A$2,0,0,COUNTA(基礎(chǔ)信息表!$A:$A)-1)工作簿客戶名稱=OFFSET(基礎(chǔ)信息表!$B$2,0,0,COUNTA(基礎(chǔ)信息表!$B:$B)-1)工作簿原材料=OFFSET(基礎(chǔ)信息表!$D$2,0,0,COUNTA(基礎(chǔ)信息表!$D:$D)-1)工作簿庫存商品=OFFSET(基礎(chǔ)信息表!$I$2,0,0,COUNTA(基礎(chǔ)信息表!$I:$I)-1)工作簿定義名稱為了方便輸入并防止輸入錯(cuò)誤，可以進(jìn)行“數(shù)據(jù)驗(yàn)證”設(shè)置。數(shù)據(jù)驗(yàn)證2.編制“采購(gòu)業(yè)務(wù)表”

單元格E4，輸入“=VLOOKUP(D4,基礎(chǔ)信息表!$D:$G,2,0)”，則E列將返回存貨對(duì)應(yīng)的單位。

單元格F4，輸入“=VLOOKUP(D4,基礎(chǔ)信息表!$D:$G,3,0)”，則F列將返回存貨對(duì)應(yīng)的類別。

單元格G4，輸入“=VLOOKUP(D4,基礎(chǔ)信息表!$D:$G,4,0)”，則G列將返回存貨對(duì)應(yīng)的規(guī)格型號(hào)。完成采購(gòu)業(yè)務(wù)明細(xì)表應(yīng)付總額：N4=L4+M4其中：存貨成本：L4=I4*J4+K4分析采購(gòu)數(shù)據(jù)1）按供貨商查看知識(shí)技能一：分類匯總1.顯示或隱藏明細(xì)數(shù)據(jù)（1）運(yùn)用任務(wù)窗格的加減號(hào)和級(jí)別號(hào)知識(shí)技能一：分類匯總1.顯示或隱藏明細(xì)數(shù)據(jù)（1）運(yùn)用任務(wù)窗格的加減號(hào)和級(jí)別號(hào)知識(shí)技能一：分類匯總1.顯示或隱藏明細(xì)數(shù)據(jù)（2）運(yùn)用功能區(qū)選項(xiàng)卡中的按鈕知識(shí)技能一：分類匯總2.編輯或清除分類匯總

一個(gè)分類匯總雖然可以對(duì)多列進(jìn)行計(jì)算，但同時(shí)只能按一個(gè)字段匯總并執(zhí)行一種計(jì)算。知識(shí)技能一：分類匯總

在【匯總方式】下除了常規(guī)求和外，還可以求平均值、最大/最小值等；現(xiàn)以“計(jì)數(shù)”為例。知識(shí)技能一：分類匯總2.編輯或清除分類匯總

如果不再需要此分類匯總，可將其刪除。

需要說明的是，分類匯總一旦執(zhí)行全部刪除，Excel無法執(zhí)行“撤銷鍵入”操作，也就意味著無法回到分類匯總前的某項(xiàng)操作，實(shí)際工作中請(qǐng)注意數(shù)據(jù)的保存和備份。知識(shí)技能二：SUBTOTAL函數(shù)

通常，使用“分類匯總”命令創(chuàng)建的列表，可自動(dòng)生成SUBTOTAL函數(shù)。當(dāng)然，也可直接使用SUBTOTAL函數(shù)完成相關(guān)數(shù)據(jù)的匯總。

語法格式：SUBTOTAL(function_num,ref1,[ref2],...])。

function_num：可以指定1到11（包含隱藏值）或101到111（忽略隱藏值）之間的數(shù)字，用于指定使用何種函數(shù)在列表中進(jìn)行分類匯總計(jì)算。

ref：要進(jìn)行分類匯總計(jì)算的命名區(qū)域或引用。function_num（包含隱藏值）function_num（忽略隱藏值）對(duì)應(yīng)函數(shù)1101AVERAGE（算數(shù)平均值）2102COUNT（計(jì)算包含數(shù)字的單元格個(gè)數(shù)）3103COUNTA（計(jì)算不為空單元格的個(gè)數(shù)）4104MAX（一組值中的最大值）5105MIN（一組值中的最小值）6106PRODUCT（乘積）7107STDEV（估計(jì)基于樣本的標(biāo)準(zhǔn)偏差）8108STDEVP（整個(gè)樣本總體的標(biāo)準(zhǔn)偏差）9109SUM（求和）10110VAR(基于給定樣本的方差)11111VARP(基于整個(gè)樣本總體的方差)知識(shí)技能二：SUBTOTAL函數(shù)關(guān)于隱藏值的說明

實(shí)際運(yùn)用SUBTOTAL函數(shù)時(shí)，并不需要死記硬背每個(gè)數(shù)字具體對(duì)應(yīng)什么函數(shù)，當(dāng)單元格輸入“=SUBTOTAL(”后，Excel會(huì)給出參數(shù)供選擇關(guān)于隱藏值的說明2）按存貨名稱查看分析采購(gòu)數(shù)據(jù)先手動(dòng)將主分類字段進(jìn)行排序，Excel才會(huì)按此分類執(zhí)行匯總。2）按存貨名稱查看分析采購(gòu)數(shù)據(jù)知識(shí)技能三：排序

排序，是指按照指定的順序?qū)?shù)據(jù)重新排列組織，是數(shù)據(jù)整理的一種重要手段。通常，數(shù)據(jù)排序要求每列中的數(shù)據(jù)類型相同，而且不允許有空行或空列，也不能有合并的單元格。

1.排序操作（1）單列排序與多列排序（2）按顏色排序（3）自定義排序

1.排序操作2.SORT函數(shù)含義：SORT函數(shù)可對(duì)某個(gè)區(qū)域范圍或數(shù)組的內(nèi)容進(jìn)行排序。語法格式為：SORT(array,[sort_index],[sort_order],[by_col])其中：array是要排序的區(qū)域或數(shù)組；sort_index表示排序字段在所在array中的數(shù)字；sort_order表示所需排序順序的數(shù)字，默認(rèn)1表示升序，-1表示降序；by_col表示所需排序方向的邏輯值，默認(rèn)FALSE是按行排序，TRUE是按列排序。

3.SORTBY函數(shù)含義：如果想要對(duì)網(wǎng)格中的數(shù)據(jù)排序，最好用SORTBY函數(shù)。SORTBY函數(shù)是基于某范圍或數(shù)組中的值對(duì)一些列范圍或數(shù)組的內(nèi)容進(jìn)行排序。語法格式為：SORTBY(array,by_array1,[sort_order1])其中：array是要排序的區(qū)域或數(shù)組；by_array1是要進(jìn)行排序的范圍或數(shù)組的依據(jù)；sort_order表示所需排序順序的數(shù)字，默認(rèn)1表示升序，-1表示降序；因?yàn)榭梢詫?shí)現(xiàn)多字段排序，所以可根據(jù)需求添加by_array2和其對(duì)應(yīng)的sort_order2。3.編制“成品入庫表”單元格E4，輸入公式“=VLOOKUP(D4,基礎(chǔ)信息表!$I:$L,2,FALSE)”，則E列將返回存貨對(duì)應(yīng)的單位。單元格F4，輸入公式“=VLOOKUP(D4,基礎(chǔ)信息表!$I:$L,3,FALSE)”，則F列將返回存貨對(duì)應(yīng)的類別。單元格G4，輸入公式“=VLOOKUP(D4,基礎(chǔ)信息表!$I:$L,4,FALSE)”

則G列將返回存貨對(duì)應(yīng)的規(guī)格型號(hào)。3.編制“成品入庫表”

手工輸入H列“結(jié)轉(zhuǎn)數(shù)量”和I列“結(jié)轉(zhuǎn)單價(jià)”后，選擇單元格J4，輸入公式“=H4*I4”，即“結(jié)轉(zhuǎn)金額=結(jié)轉(zhuǎn)數(shù)量*結(jié)轉(zhuǎn)單價(jià)”o2銷售與車間領(lǐng)料業(yè)務(wù)處理

單元格F4，輸入“=IF(D4="材料",VLOOKUP(E4,基礎(chǔ)信息表!$D:$G,2,FALSE),IF(D4="商品",VLOOKUP(E4,基礎(chǔ)信息表!$I:$L,2,FALSE),“請(qǐng)檢查存貨名稱輸入是否正確”))”，返回存貨對(duì)應(yīng)的單位。同理返回G列存貨對(duì)應(yīng)的類別、H列存貨對(duì)應(yīng)的規(guī)格型號(hào)。1.編制“銷售業(yè)務(wù)表”任務(wù)二銷售與車間領(lǐng)料業(yè)務(wù)處理用IF函數(shù)區(qū)分不同的銷售業(yè)務(wù)

銷售業(yè)務(wù)，若實(shí)現(xiàn)主營(yíng)業(yè)務(wù)收入，減少的是庫存商品，若實(shí)現(xiàn)其他業(yè)務(wù)收入，減少的是原材料，故要增加“摘要”來區(qū)分不同的屬性。1.編制“銷售業(yè)務(wù)表”2.編制”車間領(lǐng)料表”同“采購(gòu)業(yè)務(wù)表”，設(shè)置E列、F列和G列相關(guān)公式選擇J4單元格，輸入“=H4*I4”知識(shí)技能三：多條件求和間的區(qū)別o3庫存管理任務(wù)三庫存管理1.編制“庫存管理表”

材料是采購(gòu)入庫的，數(shù)據(jù)來源為“采購(gòu)業(yè)務(wù)表”；產(chǎn)成品是車間生產(chǎn)完工后結(jié)轉(zhuǎn)入庫的，數(shù)據(jù)來源為“成品入庫表”。

材料的減少可能是車間領(lǐng)料，也可能是出售多余材料，所以數(shù)據(jù)來源包含“銷售業(yè)務(wù)表”和“車間領(lǐng)料表”；產(chǎn)品的減少一般來說是因?yàn)殇N售實(shí)現(xiàn)了主營(yíng)業(yè)務(wù)收入，所以數(shù)據(jù)來源為“銷售業(yè)務(wù)表”。2.編輯相關(guān)單元格公式（1）原材料的“入庫數(shù)量”和“入庫金額”（2）產(chǎn)成品的“入庫數(shù)量”和“入庫金額”（3）原材料的“出庫數(shù)量”和“出庫金額”（4）產(chǎn)成品的“出庫數(shù)量”和“出庫金額”（5）計(jì)算“期末數(shù)量”和“期末金額”3.錄入業(yè)務(wù)，美化單元格格式設(shè)置“預(yù)警”列【條件格式】

選擇單元格N4，輸入公式“=IF(K4<=M4,"*","")”3.錄入業(yè)務(wù)，美化單元格格式設(shè)置“預(yù)警”列【條件格式】

條件格式可以根據(jù)特定條件對(duì)數(shù)據(jù)進(jìn)行格式標(biāo)識(shí)，以更加直觀地獲取特定問題的視覺提示，常被應(yīng)用在單元格、表格和數(shù)據(jù)透視表中。“條件格式”的菜單欄由3塊內(nèi)容構(gòu)成：1．僅對(duì)部分單元格設(shè)置格式2.使用數(shù)據(jù)條/色階/圖標(biāo)集設(shè)置所有單元格格式3.新建/管理/清除規(guī)則

知識(shí)技能四：條件格式1．僅對(duì)部分單元格設(shè)置格式知識(shí)技能四：條件格式2．使用數(shù)據(jù)條/色階/圖標(biāo)集設(shè)置所有單元格格式知識(shí)技能四：條件格式3．新建/管理/清除規(guī)則知識(shí)技能四：條件格式

增設(shè)“安全”列，執(zhí)行【新建規(guī)則】，選中【使用公式確定要設(shè)置格式的單元格】，在“為符合此公式的值設(shè)置格式”中輸入“=$K1>$M1”，調(diào)整【填充】為綠色。知識(shí)技能四：條件格式條件格式規(guī)則的優(yōu)先級(jí)

列表中較高處規(guī)則的優(yōu)先級(jí)高于較低處的規(guī)則；因?yàn)槟J(rèn)情況下，新規(guī)則總是添加到列表的頂部，所以具有較高的優(yōu)先級(jí)。4.利用數(shù)據(jù)透視表進(jìn)行分析4.利用數(shù)據(jù)透視表進(jìn)行分析完成數(shù)據(jù)透視圖o4存貨明細(xì)賬任務(wù)四存貨明細(xì)賬1.編制“進(jìn)銷總記錄表”

分別從“采購(gòu)業(yè)務(wù)表”“成品入庫表”“銷售業(yè)務(wù)表”“車間領(lǐng)料表”“庫存管理表”中復(fù)制相關(guān)信息。關(guān)于“選擇性粘貼”的說明1.編制“進(jìn)銷總記錄表”知識(shí)技能五：數(shù)據(jù)的舍入1.ROUND/ROUNDUP/ROUNDDOWN函數(shù)ROUND函數(shù)用于對(duì)數(shù)值進(jìn)行四舍五入；ROUNDUP函數(shù)用于向上舍入（遠(yuǎn)離零）；ROUNDDOWN函數(shù)則是向下舍入（朝向零）。三者的語法格式均為：(number,num_digits)其中，number是要四舍五入的數(shù)字，num_digits表示四舍五入的位數(shù)，即計(jì)算精度。知識(shí)技能五：數(shù)據(jù)的舍入2.INT函數(shù)/TRUNC函數(shù)INT函數(shù)只有一位參數(shù)(number)，表示將數(shù)字向下舍入到最接近的整數(shù)；當(dāng)number是負(fù)數(shù)時(shí)只會(huì)朝著遠(yuǎn)離零的方向?qū)?shù)字舍入。TRUNC函數(shù)表示將數(shù)字的小數(shù)部分截去，以返回整數(shù)。語法格式為TRUNC(number,[num_digits])，不同于ROUND系列函數(shù)，num_digits是一個(gè)可選項(xiàng)，參數(shù)省略時(shí)，默認(rèn)值為零。知識(shí)技能五：數(shù)據(jù)的舍入第一列num_digits為-1，第二列為0，第三列為1。ROUNDDOWN和TRUNC函數(shù)的結(jié)果是一樣的，唯一的區(qū)別是公式中參數(shù)的寫法格式存在細(xì)微差別，因?yàn)門RUNC函數(shù)的num_digits是可選項(xiàng)，而在ROUNDDOWN函數(shù)中為必選項(xiàng)，即便省略，也要保留參數(shù)間的逗號(hào)。知識(shí)技能五：數(shù)據(jù)的舍入3.CEILING函數(shù)/FLOOR函數(shù)

CEILING函數(shù)用于向上舍入（遠(yuǎn)離零的方向）最接近指定參數(shù)的倍數(shù)；FLOOR函數(shù)用于（朝向零的方向）向下舍入最接近指定參數(shù)的倍數(shù)。

語法格式均為(number,significance)，其中，number是待舍入的數(shù)字，significance是基數(shù)。CEILING(2.5,2)=4FLOOR(-2.5,-2)=-4知識(shí)技能五：數(shù)據(jù)的舍入4.ODD函數(shù)/EVEN函數(shù)

ODD函數(shù)用于將正數(shù)向上舍入到最接近的奇數(shù)，將負(fù)數(shù)向下舍入到最接近的奇數(shù)；

EVEN函數(shù)用于將正數(shù)向上舍入到最接近的偶數(shù)，將負(fù)數(shù)向下舍入到最