用Excel制作商品銷售清單

1、用Excel制作商品銷售清單Excel高級應(yīng)用 作者 許興權(quán)也許有人認(rèn)為Excel就是做電子表格罷了，沒什么太強(qiáng)大的功能。讀者如果也是這種看法的話，那就請您試試本附件中的excel文檔吧。圖1是附件中Excel文檔剛打開時的狀態(tài)。圖1圖2是銷售三條商品記錄所開具的銷售單。在銷售清單數(shù)據(jù)錄入過程中，“品名”是由“大類”、“小類”、“品名”三級下拉列表選定的，這有利于商店對商品名稱的規(guī)范化管理，銷售清單中的“數(shù)量”、“單價”、“規(guī)格1”、“規(guī)格2”是手動輸入的數(shù)據(jù)，是每條銷售記錄“金額”的計算依據(jù)，其中“規(guī)格1”與“規(guī)格2”可以放空，當(dāng)“規(guī)格1”、“規(guī)格2”為空時，將自動不參與“金額”計算， “單

2、位”、“規(guī)格1單位”、“規(guī)格2單位”不但可以使用下拉列表框的形式選擇，而且可以直接使用手工輸入。圖2圖2中有三條銷售記錄，第一條記錄直接按盒銷售，金額由兩個數(shù)據(jù)相乘獲得，為15*2.842元；第二條記錄按件銷售，每件中有48盒的牛奶，因此金額由三個數(shù)據(jù)相乘獲得，為8*48*2.81075.20元；而第三條記錄則按每件中有6包，每包中有8盒的方式銷售，因此金額由四個數(shù)據(jù)相乘獲得，為8*6*8*2.81075.2元。本表格支持對商品按不同種方式銷售，銷售金額將根據(jù)輸入的數(shù)據(jù)自動計算，這樣的設(shè)計基本能滿足大部分的銷售情況的要求。如果您對它感興趣，您可以試著用它，并將它存儲為Excel模板，方便調(diào)用，

3、或許將會改變您的看法哦?？赡?，做出這樣的表格模板僅用到Excel的常用功能和不多的函數(shù)而已，VBA還沒派上用場呢。本文僅介紹該文檔的制作過程，方便讀者能跟據(jù)自己的要求修改文檔，能把文檔轉(zhuǎn)變?yōu)樽约耗苡玫臇|西，只要有時間“包學(xué)包會”哦。下面將按步驟介紹文檔的設(shè)計制作過程。步驟一打開Excel，把sheet1改名為“銷售單”，把sheet2改名為“品名”，刪除sheet3，在“品名”工作表中輸入商品的大類、小類、和品名，如圖3和圖4所示。本文的實例大類設(shè)計為“食品”、“百貨”、“五金”三種，食品有“蛋糕”、“牛奶”、“大米”三種小類，百貨設(shè)計有“絲織品”、“洗滌品”、“紙類”三種小類，五金設(shè)計

4、有“刀具”、“螺絲”、“門鎖”三種小類，讀者在設(shè)計自己的表格時，可以自己定義大類、小類，本文實例中各小類中都包含著一些具體的商品，也就是具體的品名，品名就是所銷售的各種商品的規(guī)范化的名稱。圖3圖4選定如圖5所示內(nèi)容，單擊菜單“插入”“名稱”“指定”，在彈出的對話框中按如圖6設(shè)置，定義“食品”、“百貨”、“五金”名稱，為備后期引用。定義了“食品”名稱后，今后在本工作薄引用的時候，就指向“蛋糕”、“牛奶”、 “大米”，同理，百貨名稱被引用時，就指向“絲織品”、“洗滌品”、“紙類”，五金名稱被引用時，就指向“刀具”、“螺絲”、“門鎖”。 圖5 圖6選定如圖7所示內(nèi)容，單擊菜單“插入”“名稱”“指定”

5、，在彈出的對話框中按如圖8設(shè)置，就定義了“蛋糕”、“牛奶”、“大米”、“絲織品”、“洗滌品”、“紙類”、“刀具”、“螺絲”和“門鎖”名稱，方便后期對名稱的引用。 圖7 圖8通過上述的名稱設(shè)定，本工作薄已有如圖9中所示的名稱，單擊列表中的名稱，再單擊引用位置，可以適當(dāng)修改所定義名稱所引用的位置(數(shù)據(jù)，當(dāng)然不改也可以，下拉框會出現(xiàn)空行等現(xiàn)象)。圖9這樣也就為“銷售單”工表作的“大類”“小類”“品名”的下拉列表框做好了準(zhǔn)備工作。步驟二編輯“銷售單”工作表，輸入如圖10的數(shù)據(jù)，按圖10設(shè)置表格的格式，選定隱藏不使用的行和列，把“銷售單”工作表編輯為如圖10的效果。圖10在“銷售單”工作表中選定A4:A

6、13，單擊“數(shù)據(jù)”菜單“有效性”，分別按圖11、12設(shè)置填寫“設(shè)置”、“出錯警告”選項卡的相應(yīng)項。其中“設(shè)置”選項卡中“來源”填寫的內(nèi)容（食品,百貨,五金），各大類名稱在該位置列表，并用半角的“,”隔開。 圖11 圖12選定B4:B13，單擊“數(shù)據(jù)”菜單“有效性”，分別按圖13、14設(shè)置填寫“設(shè)置”、“出錯警告”選項卡的相應(yīng)項。其中“設(shè)置”選項卡中“來源”填寫的內(nèi)容“=INDIRECT(A4) ”，INDIRECT(A4)表示該單元格下拉列表的內(nèi)容來自以左邊所選大類名稱的引用(這個已經(jīng)在“品名”工作表中定義過了，這里直接引用)。 圖13 圖14上述步驟設(shè)定好了，單擊“確定”命令按鈕時，會彈出警

7、告框如圖15所示，這是因為左邊單元格的大類為空，沒有選定內(nèi)容所導(dǎo)致的，沒有關(guān)系，可以忽略，直接選擇“是”就可以，千萬不要選擇“否”，否則就做不出“小類”的下拉列表。圖15一樣的方法，先選定C4:C13，再按如圖16、17設(shè)置品名下拉列表。 圖16 圖17“數(shù)量”的數(shù)據(jù)有效性如圖18、19所示 圖18 圖19“單位”、“規(guī)格1單位”、“規(guī)格2單位” 的數(shù)據(jù)有效性如圖20、21所示。 圖20 圖21“數(shù)量”、“單價”、“規(guī)格1”、“規(guī)格2”，根據(jù)實際情況設(shè)置如圖22、23所示。 圖22 圖23通過以上的設(shè)置，銷售清單的設(shè)置就差“金額”和“合計”的還沒計算。步驟三設(shè)置K4單元格(金額)的運算公式，在

8、金額的計算中G4(規(guī)格1)、I4(規(guī)格2)參加計算時，先判斷它是否為空，若為空則默認(rèn)以1參與乘法計算，非空則以實際數(shù)據(jù)參與計算，如果整個乘法公式的積為0，則K4為空，否則顯示計算結(jié)果，所以在公式中使用二層的if嵌套，外層主要作用是修飾K4單元格，以防止計算結(jié)果為0時，在K4單元格中顯示“0”，內(nèi)層的if的作用是判斷“規(guī)格1”、“規(guī)格2”是否為空。K4的計算表達(dá)式：=IF(D4*F4*IF(G4=,1,G4)*IF(I4=,1,I4)=0,D4*F4*IF(G4=,1,G4)*IF(I4=,1,I4)K4編輯好了后，拖動填充句柄到K13，完成“金額”的計算，并把“金額”列定義為2位小數(shù)右對齊的顯

9、示方式?！昂嫌嫛眴卧竦膬?nèi)容是 =IF(SUM(K4:K13)=0,SUM(K4:K13)，使用if的作用是當(dāng)合計為“0”時，隱藏0。步驟四選定A4:J13和J2，設(shè)置“單元格格式”中的“保護(hù)”選項卡，去掉“鎖定”選項，如圖24所示。圖24選擇“工具”菜單“保護(hù)工作表”，密碼123，保護(hù)“銷售單”工作表單擊“品名”工作表，選擇“工具”菜單“保護(hù)工作表”，密碼123選擇“工具”菜單“保護(hù)工作薄”，密碼456，保護(hù)整個工作薄的格式。選擇“文件”菜單的“另存為”，選擇保存位置、輸入文件名、選擇文檔類型，完成保存。至此大功告成。小結(jié)，上述Excel文檔的制作的主要重點是名稱的定義、三級下拉列表的設(shè)計、數(shù)據(jù)有效性的設(shè)計、indirect()與if()函數(shù)的應(yīng)用，難點是名稱引用