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

　　圖1

　　圖2

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

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

　　以上操作方式有三個缺點：

　　手工錄入所有菜單名

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

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

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

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

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

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

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

　　2. 單擊“插入”/“名稱”/“定義”，打開“定義名稱”對話框;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

　　圖3

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

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

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

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

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