實(shí)用Excel技巧分享：巧用函數(shù)制作一個(gè)自動(dòng)統(tǒng)計(jì)的進(jìn)銷存表

手機(jī)如何做表格：點(diǎn)擊查看

如何制作一個(gè)可以自動(dòng)統(tǒng)計(jì)實(shí)時(shí)庫(kù)存以及出入庫(kù)數(shù)量的進(jìn)銷存表格?其實(shí)并不需要多么高難度的技術(shù)，只需要掌握四個(gè)函數(shù)并且具備一些基本的Excel編輯和排版能力就可以自己做出來(lái)。

這四個(gè)函數(shù)就是：vlookup、iferror、sumif和if。接下來(lái)就讓老菜鳥(niǎo)帶你一步一步實(shí)現(xiàn)這個(gè)出入庫(kù)表的制作。

《進(jìn)銷存出入庫(kù)統(tǒng)計(jì)表》功能說(shuō)明：

實(shí)時(shí)統(tǒng)計(jì)功能：只需要按照規(guī)定的格式記錄出庫(kù)入庫(kù)流水表，即可自動(dòng)對(duì)最新庫(kù)存及出入庫(kù)數(shù)量進(jìn)行實(shí)時(shí)統(tǒng)計(jì)。

智能提醒功能：當(dāng)物品的庫(kù)存量低于安全庫(kù)存數(shù)量時(shí)進(jìn)行自動(dòng)標(biāo)注達(dá)到警示效果。

《進(jìn)銷存出入庫(kù)統(tǒng)計(jì)表》的構(gòu)成：

按照最基本的需求來(lái)說(shuō)，制作一個(gè)進(jìn)銷存出入庫(kù)表通常需要三個(gè)部分：基礎(chǔ)數(shù)據(jù)表（也叫基本信息表）、出入庫(kù)記錄表（也叫流水明細(xì)表）、庫(kù)存統(tǒng)計(jì)表（也叫結(jié)果查詢表）。以下分別來(lái)說(shuō)明這三個(gè)部分的做法。

一、基礎(chǔ)數(shù)據(jù)表

根據(jù)公司的實(shí)際需求進(jìn)行設(shè)計(jì)，把握一個(gè)基本原則，表格要能夠體現(xiàn)出物品的所有屬性，并且每個(gè)屬性單獨(dú)一列進(jìn)行存放。表格不要求美觀，一定不要出現(xiàn)合并單元格。

例如下圖就是一個(gè)比較規(guī)范的基礎(chǔ)數(shù)據(jù)表：

說(shuō)明：序號(hào)不是必須的，僅僅是為了便于查找；統(tǒng)計(jì)通常都是使用產(chǎn)品編碼作為唯一的依據(jù)，如果碰巧你所在公司的產(chǎn)品沒(méi)有編碼，那么序號(hào)可以作為編碼來(lái)使用。

為了確保統(tǒng)計(jì)數(shù)據(jù)的準(zhǔn)確性，當(dāng)有新產(chǎn)品的時(shí)候，需要在表格里增加記錄，如果有淘汰產(chǎn)品，則無(wú)需刪除原有記錄。

二、出入庫(kù)記錄表

通常出庫(kù)和入庫(kù)是分成兩個(gè)sheet進(jìn)行存放的，也可以合在一起存放，為了方便起見(jiàn)，我們合在一起來(lái)做示例。

表格中的數(shù)據(jù)列需要包含基本的產(chǎn)品信息以及出入庫(kù)的日期和數(shù)量，格式大致為：

在上方這個(gè)流水表中，只有藍(lán)色的A、E、F、G這幾列需要及時(shí)記錄。分類、名稱和單位這幾列等基本信息都是通過(guò)公式來(lái)自動(dòng)生成的，大家一定猜到了，該vlookup上場(chǎng)了！

不錯(cuò)，這正是vlookup大顯身手的時(shí)候，通過(guò)下面這張圖，可以看到，編碼后面的三列都是使用vlookup函數(shù)得到的。B2單元格公式為：=VLOOKUP($A2,基礎(chǔ)數(shù)據(jù)表!$B:$E,COLUMN(B1),0)

公式解讀：vlookup一共需要四個(gè)參數(shù)，基本格式為

=vlookup(查找值，查找區(qū)域，列數(shù)，精確查找)

第一個(gè)參數(shù)$A2表示想要查找的內(nèi)容，注意因?yàn)楣揭依吕虼嗽贏前面加了$對(duì)列進(jìn)行鎖定，防止右拉時(shí)發(fā)生錯(cuò)誤；

第二個(gè)參數(shù)基礎(chǔ)數(shù)據(jù)表!$B:$E表示要查找的區(qū)域（文章前面介紹的基礎(chǔ)數(shù)據(jù)表），注意這個(gè)區(qū)域是以編碼為首列的，因?yàn)榫幋a在基礎(chǔ)數(shù)據(jù)表的B列，所以區(qū)域也是從B列開(kāi)始而不是從A列開(kāi)始，這一點(diǎn)一定要記住，因?yàn)楹芏嘈率质褂胿lookup都在這個(gè)地方犯了錯(cuò)誤；

第三個(gè)參數(shù)表示返回的內(nèi)容為查找區(qū)域的第幾列，因?yàn)楣揭依晕覀兪褂胏olumn(B1)作為返回列數(shù)。

column的作用是得到參數(shù)的列號(hào)。我們要返回基礎(chǔ)數(shù)據(jù)表$B:$E 中的C列即第2列， 在整個(gè)參數(shù)基礎(chǔ)數(shù)據(jù)表中B1單元格的列號(hào)是2，因此這里用column(B1)表示要返回的列數(shù)。當(dāng)公式右拉時(shí)B1會(huì)變成C1，列號(hào)也就從2變成3，實(shí)現(xiàn)了一個(gè)公式右拉完成多列引用的目的。

最后一個(gè)參數(shù)0表示精確查找。

表格最后的三列日期與出入庫(kù)數(shù)量根據(jù)實(shí)際發(fā)生情況進(jìn)行記錄即可。正常情況下這個(gè)流水表就算是完成了，但是為了使用更加智能化，還可以對(duì)vlookup這部分進(jìn)行優(yōu)化。

當(dāng)我們?cè)谳斎氘a(chǎn)品編碼的時(shí)候，有可能錄入錯(cuò)誤（或者輸入的是基礎(chǔ)數(shù)據(jù)表中沒(méi)有的新編碼），這時(shí)候就會(huì)得到一些亂碼：

效果看上去不是太美觀，因此就需要請(qǐng)出另一個(gè)函數(shù)iferror來(lái)配合vlookup解決這個(gè)問(wèn)題，公式修改為：=IFERROR(VLOOKUP($A2,基礎(chǔ)數(shù)據(jù)表!$B:$E,COLUMN(B1),0),"編碼有誤請(qǐng)核查！")

可能有些朋友是第一次見(jiàn)到iferror這個(gè)函數(shù)，簡(jiǎn)單介紹一下：

=iferror(公式，公式結(jié)果錯(cuò)誤時(shí)顯示的內(nèi)容)，公式只需要兩個(gè)參數(shù)，第一個(gè)參數(shù)是一個(gè)公式，第二個(gè)參數(shù)是當(dāng)公式結(jié)果錯(cuò)誤時(shí)需要顯示的內(nèi)容。以本例來(lái)說(shuō)，第一參數(shù)就是vlookup，當(dāng)vlookup的結(jié)果正確時(shí)，iferror不發(fā)生作用，但是當(dāng)vlookup的結(jié)果錯(cuò)誤時(shí)，就會(huì)顯示需要的內(nèi)容，本例是顯示了一串文字：編碼有誤請(qǐng)核查！注意：如果要顯示的內(nèi)容是文本一定要加引號(hào)。

三、庫(kù)存統(tǒng)計(jì)表

這個(gè)庫(kù)存統(tǒng)計(jì)表的功能是對(duì)所有產(chǎn)品的庫(kù)存情況進(jìn)行實(shí)時(shí)顯示，大致有以下一些信息：累計(jì)出庫(kù)數(shù)量、累計(jì)入庫(kù)數(shù)量、當(dāng)前庫(kù)存數(shù)量；如果需要進(jìn)行缺貨提示的話還需要一個(gè)安全庫(kù)存數(shù)量以及是否缺貨的內(nèi)容。

這個(gè)統(tǒng)計(jì)表并不需要單獨(dú)再建立一個(gè)sheet，只需要在基礎(chǔ)數(shù)據(jù)表的后面添加剛才列出來(lái)的這些內(nèi)容就OK了，格式如下圖所示：

可以看到，在基礎(chǔ)數(shù)據(jù)表后面增加了六列內(nèi)容，其中只有初始庫(kù)存和安全庫(kù)存數(shù)是需要錄入的，累計(jì)出庫(kù)數(shù)量、累計(jì)入庫(kù)數(shù)量和是否缺貨都是通過(guò)公式來(lái)實(shí)現(xiàn)的，以下對(duì)這些字段做個(gè)簡(jiǎn)要的說(shuō)明：

初始庫(kù)存：也可以叫做庫(kù)存結(jié)轉(zhuǎn)，在啟用這個(gè)出入庫(kù)統(tǒng)計(jì)表的時(shí)候?qū)υ袔?kù)存進(jìn)行記錄。

累計(jì)出庫(kù)數(shù)量（G列）：使用公式=SUMIF(出入庫(kù)記錄表!A:A,B2,出入庫(kù)記錄表!F:F)統(tǒng)計(jì)所得：

公式解析：sumif函數(shù)需要三個(gè)參數(shù)，基本結(jié)構(gòu)為=SUMIF(條件區(qū)域, 條件, 求和區(qū)域)

第一個(gè)參數(shù)出入庫(kù)記錄表!A:A表示條件列；

第二個(gè)參數(shù)B2表示前面條件列應(yīng)該滿足的條件（對(duì)應(yīng)該行物品編碼）；

第三個(gè)參數(shù)出入庫(kù)記錄表!F:F表示對(duì)滿足條件的在此列求和。

同樣的方法將第三個(gè)參數(shù)出入庫(kù)記錄表!$F:$F換成出入庫(kù)記錄表!$G:$G得到累計(jì)入庫(kù)數(shù)量（H列）：

當(dāng)前庫(kù)存數(shù)量：用初始庫(kù)存-累計(jì)出庫(kù)數(shù)量+累計(jì)入庫(kù)數(shù)量 即可；

安全庫(kù)存數(shù)量：本例中都設(shè)置的是50，可以根據(jù)每個(gè)產(chǎn)品的情況進(jìn)行確定。此項(xiàng)需要手工輸入。

是否缺貨：這里用到了IF函數(shù)，公式為：=IF(I2>J2,"","缺貨")

If函數(shù)的基本格式為if(條件, 成立時(shí)需要的結(jié)果, 不成立時(shí)需要的結(jié)果)；

本例中條件為I2>J2，也就是判斷，當(dāng)前庫(kù)存數(shù)量大于安全庫(kù)存數(shù)時(shí)，得到空白，反正則得到缺貨兩個(gè)字。

同時(shí)對(duì)此列設(shè)置了條件格式，當(dāng)出現(xiàn)缺貨的情況時(shí)，使用顏色來(lái)得到醒目的效果。

設(shè)置方法為選擇k列，依次點(diǎn)擊【條件格式】→【突出顯示單元格規(guī)則】→【等于】：

在左邊的框內(nèi)輸入缺貨兩個(gè)字，右邊選擇需要得到的效果后，確定即可。

到這里，一個(gè)自動(dòng)統(tǒng)計(jì)的出入庫(kù)表就能夠輕松實(shí)現(xiàn)了！有了這個(gè)工具再也不用擔(dān)心上千個(gè)物品的倉(cāng)庫(kù)庫(kù)存算錯(cuò)了，一旦發(fā)現(xiàn)有缺貨的情況就告訴采購(gòu)去買，效率也提高了！

最后再說(shuō)明一點(diǎn)，類似這種出入庫(kù)統(tǒng)計(jì)表，設(shè)計(jì)思路大致上是相同的，根據(jù)實(shí)際運(yùn)用的情況可以進(jìn)行一些優(yōu)化，如果還有單價(jià)等信息，可以在基礎(chǔ)數(shù)據(jù)表進(jìn)行添加，然后利用數(shù)量*單價(jià)得到金額。

很好的利用數(shù)據(jù)有效性來(lái)規(guī)范數(shù)據(jù)的錄入，例如編碼要求具有唯一性，就可以設(shè)置有效性來(lái)防止重復(fù)錄入（這個(gè)方法如果你還不會(huì)的話可以留言）。

設(shè)置公式保護(hù)防止誤操作破壞了公式從而影響數(shù)據(jù)的準(zhǔn)確性等等……

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。