如圖1和圖2所示，“選單”工作表中是常購菜名與單價，“明細”工作表是每日購買的菜名與數量，每日四種菜，菜名與數量各佔一行，G列是需要計算的結果。
圖1
圖2
常規操方式是每日將種選單名錄入單元格，再設定公式將每個單元格(即每種菜)的數量乘以“選單”工作表中對應的單價，然後彙總。公式如下：
=C2*選單!B3+D2*選單!B4+E2*選單!B6+F2*選單!B10
以上操作方式有三個缺點：
手工錄入所有選單名
手工查詢菜名對應的單價
每行使用不同公式，即每天需要重新輸入公式
是否有辦法解決這些重複工作呢?即不用每天錄入選單，也不用每天輸入公式即可完成所有需求。是的，利用資料有效性可以解決第一個問題，而陣列公式可以解決另兩個問題。
資料有效必性和陣列公式應用得範圍十分廣泛，且使用方法靈活。資料有效性可以對某些具有固定輸入專案的單元格透過下拉選擇來簡化輸入，而陣列公式往往可以將冗長的公式簡化得精煉無比，且能完成很多普通公式無法完成的工作表，將它與定義名稱和資料有效性等工具一起使用，更顯其功能的強大。
下面開始資料有效性與陣列公式結合，展示帳目製作之法。
第一步：定義名稱及設定資料有效性
1. 啟用“選單”工作表;
2. 單擊“插入”/“名稱”/“定義”，開啟“定義名稱”對話方塊;
3. 在名稱框中輸入“選單”，在“引用位置”框中輸入“=選單!$A$1:$A$10”，然後單擊“新增”。
注：這裡A1:A10區域的引用需要侃用絕對引用。
第二步：設定資料有效性
1. 啟用“明細”工作表，選擇B1:E1區域;
2. 單擊選單“資料”/“有效性”，開啟“資料有效性”對話方塊;
3. 在“設定”選項卡“允許”列表中選擇“序列”，“來源”文字框中處輸入“=選單”，最後單擊“確定”按鈕。
注：等號必須是半形狀態下輸入。
返回工作表中後，可以發現每個待錄入資料的單元格已經產生下拉選單，從中選擇菜名即可
以後每天製作明細表時，只需複製第一行即可產生同樣的下拉選單。當然也可以第一天設計表格式時即將後面的區域一次性複製好，讓所有奇數行都產生下拉選單供選擇。
第三步：函式巢狀及陣列公式
1.要F1單元格錄入以下陣列公式
=IF(MOD(ROW(),2),"菜價",SUM(IF(OFFSET(C1,-1,,,4)=選單!A$1:A$10,C1:F1)*選單!B$1:B$10))
注：這是一個陣列公式，所以不能直接敲Enter鍵，必須錄入以式後同時按Shift+Ctrl+Enter結束。
2. 將游標移動至F1單元格右下角，當出現十字游標時向下拖動、填充即可完成多日資料一次運算。
注：從圖3中可以看出，公式首尾自動產生了花擴號“{｝”，這正是陣列公式的特點。
圖3
公式解釋：MOD函式是用來返回兩數相除的餘數，ROW函式用於返回當前行的行號。在本例中MOD配合ROW函式可用於判斷公式所在行的奇偶性。對奇數行，公式返回結果“選單”，而偶數行則返回當日的購菜總價。
IF的第三引數用於計算每日的選單，它首先利用OFFSET函式引用本日的菜名，然後與“選單”工作表中的菜名進行比較，再將名稱同相的單價引用過來，並與數量相乘，透過SUM函式合計。
3.本例公式利用陣列解決奇數行為“菜價”，偶數行計算菜價的問題，且實現了自動查詢對應單價。但是利用Lookup函式還可以使用公式更簡化。公式如下：
=IF(ISTEXT(C1),"菜價",SUM(LOOKUP(OFFSET(C1,-1,,,4),選單!A$1:B$10)*C1:F1))
注：基於Lookup的特性，需要對“選單”工作表的資料以A列為基準升序排列。