Office 365/Excel 2016+ 公式

已完成

本單元介紹的三個新公式,您可以在 Office 365/Excel 2016+ 中找到,分別為 XLOOKUP()、FILTER() 和 LET()。 了解已新增到應用程式的新特徵和功能的另一種方法是,加入 Microsoft Office 測試人員計畫 (請參閱本課程模組結尾參考區域中的連結)。

XLOOKUP()

XLOOKUP() 是功能更強大的新版 VLOOKUP()。 而且更簡單、更快速也更有彈性。

XLOOKUP() 優於 VLOOKUP() 的原因如下:

  • 搜尋資料行和資料列結合 VLOOKUP() 及 HLOOKUP(),可更全面地搜尋。

  • 左側的搜尋資料行會取代 INDEX() MATCH() 模式,讓您可以使用最適合搜尋的組合。

  • 此公式更為強固,其不會在新增/刪除資料行時「中斷」。

XLOOKUP() 包含具有三個必要參數的語法。 此函式預設為執行完全相符的項目。

具有 XLOOKUP() 函式語法的 Excel 公式列螢幕擷取畫面。

XLOOKUP() 有下列功能:

  • 根據另一個資料行的值,從指定的資料行傳回值

  • 如果找不到任何結果,則傳回不同值

  • 從頂端或底部搜尋

XLOOKUP() 有六個參數,最後三個參數是選用參數:

  • lookup_value' - 此參數用於定義您想要尋找的值。

  • lookup_array' - 此陣列參數用於指定要尋找值的資料行。

  • 'return_array' - 此陣列參數用於定義要從中傳回值的資料行。

  • 'if_not_found' - 如果找不到相符結果,則傳回此選用值。

  • 'match_mode' - 此選用參數用於指定完全相符、第一個上/下或萬用字元搜尋。

  • search_mode' - 使用此選用參數,從頂端或底部指定搜尋。

XLookup() 範例的螢幕擷取畫面。

在上一個資料集範例中,請注意黑色方塊右側的 XLOOKUP() 公式,其中顯示傳回的結果。 這三個範例可回答下列問題:

  • 依識別碼尋找產品 - 示範尋找產品識別碼 = 109 的產品公式,其中產品結果位於產品識別碼資料行右側的資料行中。

  • 依郵遞區號尋找城市 - 範例公式示範尋找 CITY for ZIP = 21658,這些結果位於郵遞區號資料行左側的資料行中。

  • 依城市尋找最後一個產品 - 此公式示範如果找不到結果、完全相符項目以及 -1 代表要從底部至頂端搜尋資料表時,會傳回選擇性參數「找不到任何結果」。

FILTER()

FILTER() 是新的陣列函式。 將公式新增至單一儲存格會傳回資料表的子集,而其他值則會溢寫至結果內的其他儲存格。 FILTER() 會傳回資料列,並使用 和/或 邏輯允許多個條件。

FILTER() 有下列功能:

  • 傳回一或多個查閱值的多個比對結果

  • 篩選資料而不需要 [重新整理]{.underline}

  • 可以於其他 Excel 函式內以巢狀方式進行

下列詳細資料說明 FILTER() 所包含的三個參數:

  • array' - 此參數用於指定要篩選的資料行和資料列範圍。

  • include' - 此參數用於提供篩選規則準則。

  • if_empty' - 如果沒有任何資料列符合條件,則會傳回的選用參數值。

Filter() Single 範例的螢幕擷取畫面。

上一個資料集範例會在黑色方塊中顯示 FILTER() 公式,其中包含傳回的結果。 請注意,傳回的結果會使用資料表而非範圍。 在可行的情況下,建議您一律使用資料表。 上述範例會依 區域 = 西部 篩選 SalesTable 資料表,並傳回結果中所有相符的資料列。

Filter() Multiple 範例的螢幕擷取畫面。

此範例使用相同的資料集,但會將三個篩選條件套用至資料表。 公式會以下列準則篩選資料表 (資料列必須符合所有準則才會包含在內):

  • 產品 = Palma UM-01

  • 區域 = 美國西部

  • 收益 = 大於 1,215.00 美元

公式會使用乘法函數,因為邏輯比較會使用零 (0) 表示 False 或使用一 (1) 表示 True。 如果所有條件都是 TRUE,則 1 * 1 * 1 = 1。 不過,如果任何條件為零 (0) 或 False,則整個邏輯為 False。

星號 (*) 代表 AND 條件,而加號 (+) 符號則代表 OR 條件。

LET()

LET() 函式針對複雜計算提供相當大的彈性,並提供更簡單的方式來摘要公式的不同部分。 LET() 函式將使用變數的計算和值的儲存能力與 Excel 的原生公式語法相結合。

LET() 函式語法的圖表。

變數可用於將名稱指派給值或計算。 這些變數可用於重新叫用語法,而無須不斷重寫公式。 您可以在函式中定義最多 126 個不同的變數,但至少必須有三個元件 (變數、變數值、計算)。 您也可以利用 LET() 函式內的其他陣列函式,例如 FILTER() 函式。 下列範例是以先前的 FILTER() 範例為基礎,但現在已指定變數。

LET() 範例的螢幕擷取畫面。

在上述螢幕擷取畫面中,編號一到四為變數和定義。 最後一個陳述式是使用變數的計算。

  • ProductRange = 產品資料行範圍

  • Product = 要篩選的產品

  • RegionRange = 區域資料行範圍

  • Region = 要篩選的區域

  • Filter = 篩選產品與區域的資料表