一份 Excel 公式速查表替代方案應該提供三項靜態 PDF 或單頁參考資料做不到的功能:可搜尋的函數紀錄、以廠商風格的簽名(用方括號標示可選引數),以及可直接將語法複製到資料編輯列而無需重新輸入的方式。Excel Formulas Cheat Sheet是一個精準的瀏覽器參考資料,涵蓋十二個經來源查證的日常函數:SUM、AVERAGE、IF、COUNTIF、SUMIF、XLOOKUP、INDEX、MATCH、TEXT、ROUND、CONCAT 以及 TODAY。它並不試圖重現整個 Excel 函數目錄,也不打算取代 Microsoft 的官方文件。而是為你在數學、邏輯、條件、查詢、文字、統計與日期等任務中真正會重複使用的形式,提供單一可搜尋的介面。你可以依函數名稱、分類、簽名或用途進行搜尋;閱讀簽名並留意哪些以方括號標示的引數是可選的;將範例與你的範圍、地區設定及 Excel 版本進行比對;接著複製引數語法,並用目標活頁簿中已知的測試案例來驗證完成的公式。
Excel 公式速查表替代方案應該提供哪些功能
大多數可列印的 Excel 速查表會將三十到四十個函數壓縮成密密麻麻的表格。這種排版對僅快速瀏覽一次的人來說很方便,但一旦你需要查詢某個特定的簽名、確認哪些引數是可選的,或需要一個能直接貼到儲存格的乾淨範例時,便立刻失效。一個實用的替代方案應能透過下列其中一個面向進行搜尋:函數名稱、「lookup」或「conditional」之類的分類、你依稀記得的簽名文字,或你想完成的任務(例如「四捨五入到兩位小數」、「統計大於 X 的儲存格」)。除了搜尋之外,介面還應顯示以廠商風格簽名表示的可選引數(以方括號標示)、提供至少一個實作範例,並讓你能夠在不夾帶多餘字元(例如不可見的空白字元)的情況下將語法複製到剪貼簿。
三個小功能往往決定了一份參考資料是否真正可用。第一,必須正視地區分隔符號的問題:許多歐洲版本的 Excel 使用分號而非逗號分隔引數,若參考資料默默地預設為逗號,貼上後的公式將無法正常運作。第二,它不應假裝能運算你的公式;靜態的參考資料比沙箱化的求值器更安全,因為你的活頁簿不會被上傳、開啟或執行。第三,必須誠實說明涵蓋範圍。十二個值得信賴的函數,勝過四百個未經查證就從說明頁搬來的條目,特別是當你之所以放棄說明頁,正是為了略過那個查證步驟。
如何使用 Excel Formulas Cheat Sheet
- 依名稱、分類或任務進行搜尋。直接輸入像「SUMIF」這樣的函數名稱即可命中;輸入「lookup」或「conditional」這類分類可取得篩選後的子集合;或輸入像「round to decimals」、「today's date」這類動詞型短語。搜尋會對標準化後的名稱、分類、語法與說明文字進行比對,因此即使不是完全相符,部分相符與同義詞通常也能順利找到結果。
- 閱讀簽名並留意哪些以方括號標示的引數是可選的。廠商風格的引數簽名會在可省略的引數外加上方括號。請將這些方括號視為標記符號而非實際字元;一般情況下不應將它們輸入到資料編輯列中。
- 將範例與你的範圍、地區設定及 Excel 版本進行比對。範例使用英文函數名稱、逗號作為清單分隔符號、A1 樣式的參照,以及前置的等號。請替換為你自己的範圍;若活頁簿的地區設定要求使用分號,請將逗號改為分號;並確認你的 Excel 版本是否確實提供該函數(XLOOKUP 是最常見、在較舊的永久授權版本中缺席的函數之一)。
- 複製語法,並透過已知測試案例驗證完成的公式。請直接複製顯示的簽名,而不是重新輸入。先貼到一個暫存儲存格,將預留位置替換為實際的範圍或數值,然後在你已知正確答案的資料列上執行該公式,之後才能將結果用於財務、營運或報表資料。
十二個已查證函數一覽
這份速查表刻意保持精簡。每個條目儲存唯一一個函數名稱、一個分類、引數簽名,以及取自底層產品紀錄的簡短用途說明。請將下表視為路由指引;若需取得精確的語法、地區設定調整與實作範例,請至該工具中閱讀對應的條目。
| Function | Category | Purpose |
|---|---|---|
| SUM | Math | Adds values across a range or a list of arguments. |
| AVERAGE | Statistics | Returns the arithmetic mean of numeric values. |
| IF | Logical | Returns one of two values depending on a logical test. |
| COUNTIF | Conditional | Counts cells that satisfy a single criterion. |
| SUMIF | Conditional | Sums values whose paired cells satisfy a criterion. |
| XLOOKUP | Lookup | Looks up a value in one array and returns the matching value from another. |
| INDEX | Lookup | Returns the value at a given row and column position inside a range. |
| MATCH | Lookup | Returns the relative position of a lookup value within a range. |
| TEXT | Text | Formats a numeric value as a text string using a format code. |
| ROUND | Math | Rounds a value to a specified number of digits, changing the stored value. |
| CONCAT | Text | Joins multiple text strings into one, without automatic delimiters. |
| TODAY | Date | Returns the current date and updates on each recalculation. |
在貼上之前需先處理的地區設定、方括號與比對陷阱
簽名中的方括號代表可選引數,幾乎從不應出現在公式本身之中。若你將「=MATCH(lookup_value, lookup_array, [match_type])」原樣貼上,Excel 會傳回錯誤,因為括號會被視為運算式的一部分。修正後的版本為「=MATCH(lookup_value, lookup_array, 0)」用於精確比對,或「=MATCH(lookup_value, lookup_array, 1)」用於已遞增排序資料的近似比對。在複製之前,請先習慣將簽名改寫為所需的最小形式。
第二個陷阱是引數分隔符號。速查表中的範例使用逗號,因為這是英文地區 Excel 的預設值。若你的活頁簿使用分號,與其重新輸入,不如用貼上後取代的方式:先去掉前置的等號,將每個介於兩個引數之間的逗號都改成分號,再加回等號。在地化也會影響函數名稱;某些安裝環境將「SUM」顯示為「SOMA」,將「IF」顯示為「WENN」。英文範例並沒有錯,但若你的範本依賴的是在地化名稱,則目的地的活頁簿可能需要接受該在地化名稱。
第三個陷阱是近似比對與精確比對的選擇。match_type 為 0 的 MATCH(或 match mode 為 0 的 XLOOKUP)會傳回精確比對的結果,這在未排序或部分排序的資料中是較安全的預設。type 1 假設為遞增排序,會傳回小於或等於查詢值的最大值,一旦出現任何一筆未排序的資料列破壞這個假設,結果就會誤導。type -1 則鏡像呈現遞減排序的行為。每當透過 Excel Formulas Cheat Sheet 搜尋到查詢類條目時,在貼到財務或營運工作之前,請先確認比對模式。
查詢、條件與日期類函數需要額外的稽核關注
XLOOKUP 是這份參考資料中最具彈性的條目。它接受獨立的查詢陣列與傳回陣列,並提供額外的可選引數,用於設定找不到時的訊息、比對模式以及搜尋模式(由上至下或由下至上)。其預設值雖然合理,但不見得總是你想要的:省略 match mode 時行為等同於精確比對,而省略 search mode 時則是由上至下掃描。當你複製四引數版本時,請確認這些預設值,並將「not found」改為明確的值,例如 0 或「n/a」,以避免下游公式繼承 #N/A 錯誤。
MATCH 與 INDEX 經常搭配使用。MATCH 會傳回相對位置;INDEX 則使用該位置,從不同的範圍中取出對應的值。這個組合公式在跨舊版本時比 XLOOKUP 更具可攜性,但同樣會繼承單獨使用 MATCH 時的近似比對注意事項。在將結果視為權威依據之前,這兩種形式都需要檢查來源範圍中是否有重複、空值、文字與數字型別不一致,以及任何錯誤值。
條件類函數依賴其準則語法。COUNTIF 會統計符合條件的儲存格;SUMIF 則將符合條件之儲存格在對應範圍中的數值加總。許多文字準則及像「>1000」或「<>0」這類運算子都需要加上引號。IF 函數會在兩個傳回值之間擇一,但不會驗證背後的業務規則是否正確。一個公式在五列得到正確答案、卻在其中一列得到錯誤答案,在螢幕上看起來仍然正常,卻會悄悄地毀掉報表。
其餘四個條目的行為與數學及查詢類家族不同,當公式還會餵入其他計算時,這些差異便會造成困擾。TEXT 會將數值轉換為格式化的文字,並會破壞後續的算術運算,而且會以字母順序而非數字大小進行排序。TODAY 相對於目前日期而言是 volatile 函數,每次重新計算時都會改變;若要列印時保持固定的快照,應將其以值的形式複製貼上,而不是保留為公式。ROUND 會改變儲存的值,而不僅僅是顯示格式;若你只想要顯示層級的四捨五入,請改用數值格式。CONCAT 會串接文字,但不會自動插入分隔符號,因此若要將姓、中名、名組合成單一字串,需要自行加入空格。
參考手冊的限制，以及廠商文件仍然勝出的地方
這份速查表刻意做得範圍很窄。它涵蓋十二個函式，而非完整的 Excel 函式目錄，也無法取代 Microsoft 依字母排序的參考手冊中其他 480 多個項目。它不會開啟、上傳、評估或修改您的工作簿，也無法得知您正在使用的 Excel 版本。函式可用性會有所不同：XLOOKUP 在某些較舊的永久授權版本中並不存在，而較新的動態陣列函式在以相容性模式儲存的工作簿中行為會不一樣。請將任何複製到的簽名視為起點，並針對您的目標版本對照廠商自家文件驗證關鍵語法。
涉及金錢、法規遵循、安全或大型資料集的決策，正確的做法是分層進行：將簽名貼到一個暫存工作表中，在您已知答案的資料列上驗證，稽核前例和從屬項目，並在部署前進行同儕審查。在修改任何重要的工作簿之前，先儲存一份可復原的副本；編輯後重新計算並檢查具代表性的資料列，而非只信任一個可見的儲存格，同時也要確認具名範圍。另一份速查表能讓「回想」這個步驟更快；但它無法取代「測試」這個步驟。
若要對語法和相容性進行獨立的交叉檢查，Microsoft Excel 函式字母排序參考手冊會以相同的廠商風格簽名列出每個已發行的函式。將該目錄與速查表中可篩選的十二個函式搭配使用，既能讓日常回想保持輕量，又不會在出現不熟悉的項目時放棄長尾需求。
延伸閱讀：無需上傳即可從 Excel 超連結中擷取 URL。
延伸閱讀：如何在本地將 Excel 檔案轉換為 CSV。