多年來，我一直在處理複雜混亂的電子表格，最終發現了四個 Excel 函數，它們可以自動執行大多數人手動執行的日常任務，每週節省我數小時的工作時間。對於任何經常處理資料的人來說，這些函數都是必不可少的，無論您是專業的資料分析師，還是希望簡化工作的普通使用者。
快速連結
4. XLOOKUP：電子表格中的進階搜索
X查找 它是 Microsoft Excel 和 Google Sheets 等電子表格程式中的高級搜尋功能，超越了傳統搜尋功能的功能，例如 VLOOKUP و 聯播提供。 X查找 更大的靈活性、更有效率的資料處理以及減少與遺留功能相關的常見錯誤。 X查找 對於金融分析師、資料科學家以及任何需要處理大量資料並快速準確地提取特定資訊的人來說，這是一個必備工具。透過使用 X查找您可以在特定範圍內搜尋某個值，並從另一個範圍傳回對應的值，無論列或行位於何處。它還支援 X查找 從右到左、從下到上搜索，比其他功能更通用。
告別 VLOOKUP：XLOOKUP 是完美的解決方案
幾年前，我發現了 XLOOKUP，就不再使用 VLOOKUP 了。 VLOOKUP 只能向右搜索，而且移動列時會崩潰，而 XLOOKUP 可以向任意方向搜索，並且非常靈活。 XLOOKUP 是 可以節省您時間的 Excel 函數 在電子表格中尋找特定數據。
在我的電腦組件定價資料中，我需要根據產品型號找到特定的 GPU 價格。使用 VLOOKUP 時，我需要重新建立整個表格。但使用 XLOOKUP，我只需輸入：
=XLOOKUP("技嘉 GeForce RTX 3060 12GB Gaming OC", C:C, D:D)
XLOOKUP 搜尋整個產品列，找到我的 GPU，並返回相應的價格。價格列在哪裡並不重要，即使我稍後添加更多列，它也不會崩潰。我經常使用它來跨不同工作表引用產品訊息，而無需重新格式化任何內容。
XLOOKUP 的基本公式是：
=XLOOKUP(查找值，查找數組，返回數組)
- 查找值： 您要搜尋的值。
- 查找數組： 您尋找價值的地方。
- 返回數組： 包含要傳回的值的列或行。
因此，就我而言，我想要尋找的值是「GIGABYTE GeForce RTX 3060 12GB Gaming OC」。我想在 C:C 列中尋找此值，並從找到匹配項的同一行中的 D:D 傳回對應的值。
我喜歡 XLOOKUP 的另一個原因是，如果我在公式末尾添加“,-1”，它會從下往上搜索，讓我能夠自動找到最新的價格條目。這樣就省去了每次刷新電子表格時手動排序資料的麻煩。
3. 使用我的功能 SUMIFS و COUNTIFS 在試算表中
專業處理多種標準
基本的 SUM 和 COUNT 函數足以完成簡單的任務，但在實際分析中卻顯得力不從心。當我需要在多種條件下分析定價資料時，我通常會使用 SUMIFS 和 COUNTIFS 函數。這些函數讓我可以輕鬆地對數百行資料進行細分。
假設我想統計亞馬遜美國站上可用的 AMD 處理器數量。我不需要手動篩選，而是輸入：
=COUNTIFS(F:F, "Amazon US", K:K, "AMD")
這立即顯示我的資料集中亞馬遜上列出了 14 款 AMD 處理器。這樣做的好處是，我可以根據需要編譯任意數量的基準測試。
對於價格分析，SUMIFS 函數的工作方式相同。為了計算當前庫存的所有英特爾處理器的總價值，我使用：
=SUMIFS(D:D, K:K, "Intel", G:G, "In Stock")
這將新增 D 列中品牌為「英特爾」且庫存狀態為「有貨」的所有價格。
SUMIFS 函數的語法是：
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2...)
- 總和範圍： 您想要要求和的列。
- 標準範圍1： 檢查條件的第一列。
- 標準1： 第一個範圍條件。
- 標準範圍2，標準2： 附加條款和條件（可選）。
COUNTIFS 函數的工作原理類似，只不過它計算匹配的行數而不是對值求和：
=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2...)
我更喜歡使用 SUMIFS 和 COUNTIFS 來快速生成報表，因為它們可以即時更新新數據，與我現有的公式完美契合，並且允許我保持所有內容的內聯，而無需創建單獨的數據透視表。這些工具可以實現準確且有效率的數據分析，節省開發複雜報表的時間和精力。對於每個希望快速輕鬆地從資料中提取有價值見解的資料分析師來說，使用 SUMIFS 和 COUNTIFS 等函數是一項必備技能。
2. 修剪和清潔：保持外觀的必要步驟
告別數據混亂
沒有什麼比充斥著多餘空格和隱藏字元的非結構化資料更能毀掉電子表格了。我的搜尋總是因為表單名稱末尾的多餘空格而失敗，這讓我深有體會。
TRIM 函數可以刪除文字開頭和結尾的多餘空格，以及單字之間的多餘空格。當我從不同來源匯入資料時，產品名稱中經常會出現不一致的空格。我沒有手動清理每個單元格，而是創建一個輔助列並使用：
=TRIM(C2)
然後我將滑鼠指標移到單元格的邊緣，直到它變成加號 (+)，然後將其向下拖曳到我希望 TRIM 函數操作的所有行。
1. TEXTBEFORE 和 TEXTAFTER：詳細解釋及其重要性
準確擷取所需數據
TEXTBEFORE 和 TEXTAFTER 函數是我最常用的 Excel 函數之一，可以用來清理雜亂的電子表格。 Excel 的現代文字函數擅長從非結構化文字字串中提取特定資訊。例如，我的價格列中有一些條目，例如“$177.52”、“178.33 USD”、“9055 比索”和“9645.50 PHP”，它們全都混雜在一起。
TEXTBEFORE 函數會擷取指定分隔符號之前的所有內容：
=TEXTBEFORE(D2, "美元")
這樣，該函數就立即從「178.33 USD」中提取出了「178.33」。
TEXTAFTER 函數反向工作，提取分隔符號後的所有內容：
=TEXTAFTER(C2, "AMD ")
這樣，我就從「AMD Ryzen 5 5700X 8-Core AM4 Processor」中提取了「Ryzen 5 5700X 8-Core AM4 Processor」這個函數。
對於複雜的提取，我會結合這兩個函數。例如，要取得 177.52 美元的數值價格：
=TEXTBEFORE(TEXTAFTER(D8, "$"), "USD")
TEXTBEFORE 和 TEXTAFTER 函數的一般語法為：
=TEXTBEFORE(文字, 分隔符號) 和 =TEXTAFTER(文字, 分隔符號)
這兩個函數帶來的巨大改進在於其精確度。我不再需要使用複雜的 MID、FIND 和 LEN 函數組合，而是可以使用簡單易讀的公式來實現清晰的提取。我經常使用這些函數來分離型號、提取產品規格，並從導入的文本中提取清晰的數據，而這些操作過去需要花費數小時的手動編輯。
這四個函數解決了 Excel 中一些最浪費時間的問題，例如使用靈活的搜尋功能來尋找資料、基於多個條件進行分析、清理導入的雜亂文字以及從複雜的文字字串中提取特定資訊。大多數人手動處理這些任務，花費數小時，而通常只需幾分鐘就能實現正確的公式。
您已經將這些功能應用於從組件定價分析到庫存管理報告等各種領域。無論您身處哪個行業，它們都能發揮作用，因為雜亂的數據和複雜的搜尋要求是普遍存在的難題。一旦您掌握了這些功能，您就會驚訝於自己以前是如何在沒有它們的情況下管理電子表格的。
