如圖1和圖2所示，“菜單”工作表中是常購(gòu)菜名與單價(jià)，“明細(xì)”工作表是每日購(gòu)買的菜名與數(shù)量，每日四種菜，菜名與數(shù)量各占一行，G列是需要計(jì)算的結(jié)果。

　　圖1

　　圖2

　　常規(guī)操方式是每日將種菜單名錄入單元格，再設(shè)置公式將每個(gè)單元格(即每種菜)的數(shù)量乘以“菜單”工作表中對(duì)應(yīng)的單價(jià)，然后匯總。公式如下：

　　=C2*菜單!B3+D2*菜單!B4+E2*菜單!B6+F2*菜單!B10

　　以上操作方式有三個(gè)缺點(diǎn)：

　　手工錄入所有菜單名

　　手工查找菜名對(duì)應(yīng)的單價(jià)

　　每行使用不同公式，即每天需要重新輸入公式

　　是否有辦法解決這些重復(fù)工作呢?即不用每天錄入菜單，也不用每天輸入公式即可完成所有需求。是的，利用數(shù)據(jù)有效性可以解決第一個(gè)問(wèn)題，而數(shù)組公式可以解決另兩個(gè)問(wèn)題。

　　數(shù)據(jù)有效必性和數(shù)組公式應(yīng)用得范圍十分廣泛，且使用方法靈活。數(shù)據(jù)有效性可以對(duì)某些具有固定輸入項(xiàng)目的單元格通過(guò)下拉選擇來(lái)簡(jiǎn)化輸入，而數(shù)組公式往往可以將冗長(zhǎng)的公式簡(jiǎn)化得精煉無(wú)比，且能完成很多普通公式無(wú)法完成的工作表，將它與定義名稱和數(shù)據(jù)有效性等工具一起使用，更顯其功能的強(qiáng)大。

　　下面開(kāi)始數(shù)據(jù)有效性與數(shù)組公式結(jié)合，展示帳目制作之法。

　　第一步：定義名稱及設(shè)置數(shù)據(jù)有效性

　　1. 激活“菜單”工作表;

　　2. 單擊“插入”/“名稱”/“定義”，打開(kāi)“定義名稱”對(duì)話框;

　　3. 在名稱框中輸入“菜單”，在“引用位置”框中輸入“=菜單!$A$1:$A$10”，然后單擊“添加”。

　　注：這里A1:A10區(qū)域的引用需要侃用絕對(duì)引用。

　　第二步：設(shè)置數(shù)據(jù)有效性

　　1. 激活“明細(xì)”工作表，選擇B1:E1區(qū)域;

　　2. 單擊菜單“數(shù)據(jù)”/“有效性”，打開(kāi)“數(shù)據(jù)有效性”對(duì)話框;

　　3. 在“設(shè)置”選項(xiàng)卡“允許”列表中選擇“序列”，“來(lái)源”文字框中處輸入“=菜單”，最后單擊“確定”按鈕。

　　注：等號(hào)必須是半角狀態(tài)下輸入。

　　返回工作表中后，可以發(fā)現(xiàn)每個(gè)待錄入數(shù)據(jù)的單元格已經(jīng)產(chǎn)生下拉菜單，從中選擇菜名即可

　　以后每天制作明細(xì)表時(shí)，只需復(fù)制第一行即可產(chǎn)生同樣的下拉菜單。當(dāng)然也可以第一天設(shè)計(jì)表格式時(shí)即將后面的區(qū)域一次性復(fù)制好，讓所有奇數(shù)行都產(chǎn)生下拉列表供選擇。

　　第三步：函數(shù)嵌套及數(shù)組公式

　　1.要F1單元格錄入以下數(shù)組公式

　　=IF(MOD(ROW(),2),"菜價(jià)",SUM(IF(OFFSET(C1,-1,,,4)=菜單!A$1:A$10,C1:F1)*菜單!B$1:B$10))

　　注：這是一個(gè)數(shù)組公式，所以不能直接敲回車鍵，必須錄入以式后同時(shí)按Shift+Ctrl+Enter結(jié)束。

　　2. 將光標(biāo)移動(dòng)至F1單元格右下角，當(dāng)出現(xiàn)十字光標(biāo)時(shí)向下拖動(dòng)、填充即可完成多日數(shù)據(jù)一次運(yùn)算。

　　注：從圖3中可以看出，公式首尾自動(dòng)產(chǎn)生了花擴(kuò)號(hào)“{｝”，這正是數(shù)組公式的特點(diǎn)。

　　圖3

　　公式解釋：MOD函數(shù)是用來(lái)返回兩數(shù)相除的余數(shù)，ROW函數(shù)用于返回當(dāng)前行的行號(hào)。在本例中MOD配合ROW函數(shù)可用于判斷公式所在行的奇偶性。對(duì)奇數(shù)行，公式返回結(jié)果“菜單”，而偶數(shù)行則返回當(dāng)日的購(gòu)菜總價(jià)。

　　IF的第三參數(shù)用于計(jì)算每日的菜單，它首先利用OFFSET函數(shù)引用本日的菜名，然后與“菜單”工作表中的菜名進(jìn)行比較，再將名稱同相的單價(jià)引用過(guò)來(lái)，并與數(shù)量相乘，通過(guò)SUM函數(shù)合計(jì)。

　　3.本例公式利用數(shù)組解決奇數(shù)行為“菜價(jià)”，偶數(shù)行計(jì)算菜價(jià)的問(wèn)題，且實(shí)現(xiàn)了自動(dòng)查找對(duì)應(yīng)單價(jià)。但是利用Lookup函數(shù)還可以使用公式更簡(jiǎn)化。公式如下：

　　=IF(ISTEXT(C1),"菜價(jià)",SUM(LOOKUP(OFFSET(C1,-1,,,4),菜單!A$1:B$10)*C1:F1))

　　注：基于Lookup的特性，需要對(duì)“菜單”工作表的數(shù)據(jù)以A列為基準(zhǔn)升序排列。