淺析Excel進(jìn)一銷一存系統(tǒng)

張丹丹

摘要：Exce12010是微軟Omce較為成熟的版本組件之一，該項(xiàng)目使用它制作了一套進(jìn)一銷一存系統(tǒng)。系統(tǒng)中根據(jù)目前通用條碼EAN-13：標(biāo)準(zhǔn)版的前綴部分關(guān)聯(lián)主鍵對(duì)應(yīng)的商品地區(qū)，該項(xiàng)目大量使用了查找函數(shù)，并比較各類查找函數(shù)間參數(shù)的使用特點(diǎn)。查詢卡更挖掘了多種查詢方式，例如：自動(dòng)生成商品名、自動(dòng)顯示庫(kù)存、月銷售量等內(nèi)容。最后將系統(tǒng)界面進(jìn)行保護(hù)和隱藏公式，使之完善成一套具有實(shí)際效用的小型貿(mào)易系統(tǒng)。

關(guān)鍵詞：查找函數(shù);條形碼;多條件求和;保護(hù)隱藏單元格

中圖分類號(hào)：TP311 文獻(xiàn)標(biāo)識(shí)碼：A

文章編號(hào)：1009-3044（2019）30-0059-02

該系統(tǒng)包含四張表，分別為：查詢卡、商品信息表、進(jìn)貨和銷售。四張表中數(shù)據(jù)相互關(guān)聯(lián)，鍵值分明，界面友好，查詢簡(jiǎn)易。圖1為該系統(tǒng)中的查詢卡：

鑒于主要知識(shí)點(diǎn)，我從《商品信息表》《查詢卡》《保護(hù)隱藏單元格》三個(gè)方面對(duì)該系統(tǒng)做出論述：

1商品信息表

圖2為《商品信息表》截圖，此表中<序號(hào)>使用公式=IF（D2=””，““，ROW（）-1）自動(dòng)生成，注意row（）省略參數(shù)，則返回所在單元格的行號(hào);否則返回所在參數(shù)區(qū)域的第一行單元格的行號(hào)。通?？墒褂胷ow（）自動(dòng)生成行號(hào)，或在條件格式中結(jié)合Modl（）生成不同的單元格格式。<商品分類>采用<數(shù)據(jù)有效性>——<序列>進(jìn)行設(shè)置，序列可引用也可寫入。

1.1 Match（）查找原則及論述

此表中<產(chǎn)地>使用index（）結(jié)合match（）自動(dòng)生成，前提是先從網(wǎng)上下載條形碼表，三位條形碼前綴表整理后如下圖：

圖3為條形碼前綴表，因?yàn)楹竺鎸atch_type設(shè)置為一1，所以我將該表按<前綴終>降位排序。查找原則是首先使用MATCH（100kup_value，lookup_array，match_type）根據(jù)前綴終定位行號(hào)。然后再使用index（）提供的列號(hào)定位查找地區(qū)。具體函數(shù)如下圖：

分析圖4中match（）參數(shù)：第一個(gè)參數(shù)提取所輸入條形碼的前三位作為查找參數(shù);第二個(gè)參數(shù)即查找區(qū)域，為《條形碼前綴》表的<前綴終>列，即圖3的B列;第三個(gè)參數(shù)match_type設(shè)置為-1，即查找>=lookup_value的最小數(shù)值。這樣就可以定位到圖3中B列的行號(hào)，即查找參數(shù)對(duì)應(yīng)的行號(hào)。再根據(jù)index（）提供的列號(hào)，即《條形碼前綴》表的地區(qū)列，也就是圖3的第3列，依據(jù)該函數(shù)行列交叉原則，定位到具體的地區(qū)。

Match（）注意點(diǎn)：它是excel的查找函數(shù)之一，它必須在單列多行或單行多列中進(jìn)行定位;它返回的行號(hào)或列號(hào)是相對(duì)查找區(qū)域的數(shù)值;它的參數(shù)match_type為l，lookup_array需升序排序，match_type為-1，按降序排序。

Match（）參數(shù)設(shè)置根據(jù)mach_type分為三類：

match_type=1，查找<=lookup_value的最大數(shù)值，lookup_ar-ray需升序排列

match_type=0，查找=lookup_value的第一個(gè)數(shù)值，無(wú)需排序

match_type=-1，查找>=lookup_value的最小數(shù)值，look-up_array需降序排列

這里我理解為，match_type設(shè)置為-1，便查找>=look-up_value的最小數(shù)值，因?yàn)樗遣檎掖笥诘扔趌ookup_value的最小數(shù)值，所以第二個(gè)參數(shù)一查找范圍必須降序排列，因此當(dāng)它找到第一個(gè)小于數(shù)，便終止這個(gè)小于數(shù)，定位臨上的那個(gè)大于數(shù)。同樣的，當(dāng)參數(shù)match_type設(shè)置為l，即查找<=look-up_value的最小數(shù)值，所以第二個(gè)參數(shù)查找范圍按升序排列，因?yàn)樗倚∮跀?shù)，所以當(dāng)?shù)谝粋€(gè)大于數(shù)出現(xiàn)，函數(shù)終止，這樣便定位在上一個(gè)小于數(shù)的行號(hào)上。

1.2庫(kù)存計(jì)算

表中<庫(kù)存>使用sumif（）實(shí)現(xiàn)，根據(jù)《商品信息表》條形碼分別計(jì)算《進(jìn)貨》表和《銷售》表的數(shù)量差，具體函數(shù)如圖5：

分析圖5公式：sumif（）中將《進(jìn)貨》表B列，即<條形碼>作為第一個(gè)參數(shù)：條件范圍;選擇《商品信息表》條形碼單元格作為第二參數(shù)：條件;第三參數(shù)：求和范圍為《進(jìn)貨》表<數(shù)量>列。同樣方法求出銷售數(shù)量。在此公式中，求和范圍均為整列相對(duì)引用，例如《進(jìn)貨》表或《銷售》表中I：I列，原因是這兩張表都需要不斷添加行記錄。

2《查詢卡》

查詢卡界面效果如圖1，界面制作采用左上邊框淺色雙線的和右下邊框深色雙線達(dá)到單元格突出的效果;反之，左上邊框深色單線和右下邊框淺色做出單元格凹進(jìn)的效果。界面右上方花朵是從網(wǎng)上下載的位圖，調(diào)整它位置大小后得到如圖效果。

2.1 VLookup（）查找原則及論述

此《查詢卡》中<輸入或選擇條形碼>相對(duì)應(yīng)顯示框中的條形碼是使用數(shù)據(jù)有效性引用《商品信息表》中<條形碼>列完成。<商品名>、<商品分類>、<產(chǎn)地>、<品牌><、售價(jià)>等等的自動(dòng)生成數(shù)據(jù)是通過(guò)使用VLOOKUP0實(shí)現(xiàn)的，例如<商品名>的顯示過(guò)程如圖6所示：

圖6公式分析：vlookup（）將表中《查詢卡》條形碼作為第一參數(shù)，即查找值;《商品信息表》的D：J列作為查找區(qū)域;返回《商品信息表》D：J列區(qū)域中第2列內(nèi)容，即商品名;第四個(gè)參數(shù)設(shè)定為精確匹配。

在這里，再將查找函數(shù)vlookup（）與match（）做參數(shù)比較：Match（）必須在單列多行或單行多列中進(jìn)行定位，因此查找區(qū)域除了排序不需做其他設(shè)置，而且match（）查找一般需結(jié)合其他函數(shù)進(jìn)行，如index（）。vlookup（）實(shí)際應(yīng)用中通常也涉及兩張數(shù)據(jù)表，首先兩張表關(guān)聯(lián)的查找值必須格式統(tǒng)一，如均為文本或數(shù)值或日期等格式，若不同，可將文本改成數(shù)值，方法可使用公式乘以1的方法。其次，我們知道vlookup（）第一個(gè)參數(shù)通常選定第一張表的一個(gè)主鍵為查找值;第二個(gè)參數(shù)為查找區(qū)域，通常在第二張表中框選，我們必須把該表中和第1參數(shù)相關(guān)聯(lián)的主鍵值放置查找區(qū)域的首列;第三個(gè)參數(shù)返回所查找單元格的內(nèi)容，在這里也是相對(duì)查找區(qū)域的列號(hào);最后一個(gè)參數(shù)是精確或模糊匹配，設(shè)置精確匹配是不需要排序工作的。

2.2 Sumifs（）實(shí)現(xiàn)月銷售量、額

最后<月銷售量><月銷售額>使用SUMIFS（）實(shí)現(xiàn)，該函數(shù)為多條件求和，以<月銷售量>為例，在此為兩個(gè)條件求和：篩選年月、篩選條形碼。公式如圖7所示：

圖7公式分析：sumifs（）第一個(gè)參數(shù)為求和范圍，在此為《銷售》表中I列，即<數(shù)量>列;第二個(gè)參數(shù)可視為一組，為條件范圍和條件：在此分別為《銷售》表中L列，即<年月>列和《查詢卡》中<選擇年月>對(duì)應(yīng)的日期單元格;第三個(gè)參數(shù)為一組：《銷售》表中c列，即<條形碼>列和《查詢卡》中<輸入或選擇條形碼>對(duì)應(yīng)的條形碼單元格。

這里我們將sumif（）和sumifs（）做參數(shù)對(duì)比：sumif（）三個(gè)參數(shù)分別為條件范圍、條件、求和范圍，sumifs（）參數(shù)分別為求和范圍、（條件范圍1、條件1）、（條件范圍2、條件2）……做多可以達(dá)到127個(gè)條件組。這兩個(gè)函數(shù)的條件參數(shù)，必須使用雙引號(hào)括起來(lái)，同時(shí)這兩個(gè)條件求和函數(shù)均可使用通配符：？或。，如果條件本身為？或*，需要在？或*前輸入符號(hào)～，當(dāng)然，所有的符號(hào)必須在雙引號(hào)內(nèi)，例如”～？”。

3保護(hù)隱藏單元格

將工作表保護(hù)和隱藏公式才能使系統(tǒng)更加完善，操作更加清晰，數(shù)據(jù)更加安全。

在此，以《查詢卡》為例（閱讀下文可參見圖1），使用excel制作完成系統(tǒng)，我們可以將查詢卡所有單元格保護(hù)和隱藏公式，這樣原始數(shù)據(jù)便不得修改，呈現(xiàn)只讀狀態(tài)，當(dāng)然這里除了<輸入或選擇條形碼>對(duì)應(yīng)的條形碼單元格和<選擇年月>對(duì)應(yīng)的日期單元格，因?yàn)檫@兩項(xiàng)作為實(shí)時(shí)查詢條件。制作過(guò)程中，我們首先將整張查詢卡選中，其次在<設(shè)置單元格格式>對(duì)話框中選擇<保護(hù)>選項(xiàng)卡，去掉<鎖定>、<隱藏>復(fù)選框前的鉤，這項(xiàng)工作是為了解除整張表格的鎖定和隱藏。然后按住Ctrl鍵，選中所有不連續(xù)的且需要保護(hù)和隱藏公式的單元格，回到<保護(hù)>選項(xiàng)卡，勾選<鎖定>、<隱藏>復(fù)選框，這樣就標(biāo)志這些選中的單元格需要進(jìn)行鎖定和隱藏。最后鼠標(biāo)選中該表中任一單元格即可，選擇<保護(hù)工作表>，默認(rèn)選項(xiàng)下按確定即可，當(dāng)然也可以為表格設(shè)置密碼。

《商品信息表》《進(jìn)貨》《銷售》根據(jù)實(shí)際使用按同步驟操作，因?yàn)樯婕安粩嗤巳碇刑砑佑涗?，因此僅設(shè)置自動(dòng)計(jì)算列和自動(dòng)生成列。

4結(jié)束語(yǔ)

Excel不局限于常規(guī)的存儲(chǔ)及處理數(shù)據(jù)，經(jīng)常會(huì)碰到較大數(shù)據(jù)的對(duì)比、查找等處理問(wèn)題，作為目前大部分平臺(tái)的數(shù)據(jù)導(dǎo)入后備軍，excel在導(dǎo)入導(dǎo)出過(guò)程中的運(yùn)用效率很高，如導(dǎo)出數(shù)據(jù)的比對(duì)關(guān)聯(lián)等，強(qiáng)大的宏運(yùn)用更是起到例如本文為實(shí)際應(yīng)用中的一個(gè)案例，進(jìn)一銷一存為典型的財(cái)務(wù)運(yùn)用，在使用過(guò)程中，如果關(guān)聯(lián)數(shù)據(jù)進(jìn)行查詢或自動(dòng)產(chǎn)生數(shù)據(jù)，使用查找函數(shù)可解決相關(guān)問(wèn)題。又如大量數(shù)據(jù)對(duì)比關(guān)聯(lián)，比如根據(jù)工號(hào)關(guān)聯(lián)員工信息，本文中介紹的indexO組合MatchO、vlookupO都是excel中很好的查詢函數(shù)，但在使用過(guò)程中注意每個(gè)函數(shù)的使用要點(diǎn)，并不斷挖掘使用方法。