如圖1和圖2所示，“菜單”工作表中是常購菜名與單價(jià)，“明細(xì)”工作表是每日購買的菜名與數(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è)問題，而數(shù)組公式可以解決另兩個(gè)問題。
數(shù)據(jù)有效必性和數(shù)組公式應(yīng)用得范圍十分廣泛，且使用方法靈活。數(shù)據(jù)有效性可以對(duì)某些具有固定輸入項(xiàng)目的單元格通過下拉選擇來簡(jiǎn)化輸入，而數(shù)組公式往往可以將冗長(zhǎng)的公式簡(jiǎn)化得精煉無比，且能完成很多普通公式無法完成的工作表，將它與定義名稱和數(shù)據(jù)有效性等工具一起使用，更顯其功能的強(qiáng)大。
下面開始數(shù)據(jù)有效性與數(shù)組公式結(jié)合，展示帳目制作之法。
第一步：定義名稱及設(shè)置數(shù)據(jù)有效性
1. 激活“菜單”工作表;
2. 單擊“插入”/“名稱”/“定義”，打開“定義名稱”對(duì)話框;
3. 在名稱框中輸入“菜單”，在“引用位置”框中輸入“=菜單!$A$1:$A$10”，然后單擊“添加”。
注：這里A1:A10區(qū)域的引用需要侃用絕對(duì)引用。
第二步：設(shè)置數(shù)據(jù)有效性
1. 激活“明細(xì)”工作表，選擇B1:E1區(qū)域;
2. 單擊菜單“數(shù)據(jù)”/“有效性”，打開“數(shù)據(jù)有效性”對(duì)話框;
3. 在“設(shè)置”選項(xiàng)卡“允許”列表中選擇“序列”，“來源”文字框中處輸入“=菜單”，最后單擊“確定”按鈕。
注：等號(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ù)是用來返回兩數(shù)相除的余數(shù)，ROW函數(shù)用于返回當(dāng)前行的行號(hào)。在本例中MOD配合ROW函數(shù)可用于判斷公式所在行的奇偶性。對(duì)奇數(shù)行，公式返回結(jié)果“菜單”，而偶數(shù)行則返回當(dāng)日的購菜總價(jià)。
IF的第三參數(shù)用于計(jì)算每日的菜單，它首先利用OFFSET函數(shù)引用本日的菜名，然后與“菜單”工作表中的菜名進(jìn)行比較，再將名稱同相的單價(jià)引用過來，并與數(shù)量相乘，通過SUM函數(shù)合計(jì)。
3.本例公式利用數(shù)組解決奇數(shù)行為“菜價(jià)”，偶數(shù)行計(jì)算菜價(jià)的問題，且實(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)升序排列。