WPS數據有效性與條件求和的搭配

 　　如圖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))

　　注：這是一個數組公式，所以不能直接敲回車鍵，必須錄入以式後同時按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列為基准升序排列。