EXCEL中嵌套函數的設計思路

2024-01-12

1157

1. 嵌套函數的層級限制在EXCEL中，有400多個函數可供使用。有些需求可以通過單一函數完成，比如求和可以使用SUM函數。然而，對于大多數需求來說，單一函數是無法滿足的，需要將多個函數組合起來使用才

1. 嵌套函數的層級限制

在EXCEL中，有400多個函數可供使用。有些需求可以通過單一函數完成，比如求和可以使用SUM函數。然而，對于大多數需求來說，單一函數是無法滿足的，需要將多個函數組合起來使用才能實現目標。

在EXCEL中，為了達到某種目的，常常需要多個函數一起使用，其中某個或一些函數的結果作為另一個函數的參數，并且這個函數又可能作為其他函數的參數，就這樣一層一層地設計出公式，這種用法稱為嵌套。

根據EXCEL版本不同，嵌套函數的允許層數也不同。在2003版及以前，最多只能嵌套7層；而之后的版本允許最多64層嵌套。超過7層嵌套的公式相對較常見，但達到64層嵌套的幾乎看不到，一般嵌套得比較多的情況也只有十幾層。

嵌套函數是以某個函數的結果作為另一個函數的參數，因此這個結果必須滿足另一個函數的參數類型，否則會出錯。例如，如果要返回單價，VLOOKUP函數的第三個參數應該使用2，但如果使用COLUMN(A:A)作為參數時，結果為1，與預期的單價不符。

要解決這個問題，可以將第三個參數改為COLUMN(B:B)，即將公式修改為：VLOOKUP(D2,A:B,COLUMN(B:B),0)，這樣就可以得到正確的結果。

編輯嵌套函數可以從外向內或者從內向外進行，也可以結合兩種方式。最常見和簡單的嵌套函數之一是IF函數的嵌套。例如，如果要將A列的分數改成等級，當分數小于60時返回"不及格"，大于等于60小于80時返回"及格"，大于等于80小于90時返回"良"，大于等于90時返回"優"，可以使用IF函數來實現。

首先判斷第一層條件，即分數是否小于60，小于60則返回"不及格"，否則繼續判斷下一層條件，即分數是否小于80，在此范圍內返回"及格"，否則繼續判斷下一層條件，以此類推。公式如下：

IF(A2<60,"不及格",IF(A2<80,"及格",IF(A2<90,"良","優")))

利用多層嵌套函數完成公式的設計，并且可以向下填充應用于其他單元格。

舉例來說，假設現在的銷售記錄是記在一個單元格內，需要將每種水果的銷售數量提取出來，這里以"香蕉"為例。

由于在表中每天的銷售中，各種水果的銷售量和銷售品種都不固定，無法統一從某個字符開始提取。因此，我們首先需要判斷當天銷售中是否有"香蕉"這個品種，如果有再提取數量，沒有則返回空值或0。因此，首先判斷是否有"香蕉"，可以使用FIND函數來實現：

FIND(C$1,$B2)

為了方便提取其他水果的數量，在公式中使用了絕對引用符"$"進行限定，公式可以向右向下填充。

如果無法找到"香蕉"，公式會返回錯誤值。為了順利編寫公式，可以先選擇一個已知包含"香蕉"的單元格編寫公式，暫時忽略錯誤值。然后再完善公式，確定從哪個位置開始提取字符。

例如，公式中的5是一個臨時數字代用的：

MID(B2,FIND(C$1,$B2) 3,5)

然后根據實際情況確定需要提取多少個字符。在表中可以看到，需要提取的數量位于":"和","之間的數字。因此，需要先判斷","的位置才能確定要提取多少個字符。為了不影響原公式，我們可以在旁邊的輔助列中先進行判斷，然后將這部分公式復制到原公式中。

使用FIND函數來查找","的位置并得到結果。由于單元格中可能有多個","，這里返回的是第一個","出現的位置。因此，需要從"香蕉"后面開始查找，這就需要嵌套F