在 Excel 中處理資料時，某些任務可能會顯得繁瑣。例如，您可能需要將一列全名分割為姓氏和名稱的單獨列，或使用特定逗號合併多個儲存格中的文字。這些並非複雜的分析挑戰，而是經常出現的基本資料處理任務。
好消息是，Excel 內建了專門針對這些情況的函數。然而，它們經常被忽視，因為它們不屬於 大多數人學習的標準 Excel 工具集包括我在內的許多人都參與其中。我在這裡介紹的函數並非進階計算，但如果你發現自己在進行重複的資料處理，這些函數可以為你節省一些時間。
快速連結
5. 文本分割
分離黏在一起的文本
如果您曾經收到過電子表格，有人將自己的姓氏和名字，甚至中間名的首字母都塞進一個單元格里，那麼您一定知道拆分這些數據有多麼痛苦。 TextSplit 正是解決了這個問題——它從單一單元格中提取文本，並根據您指定的分隔符號將其拆分到多個列中。
讓我們使用一個範例銷售電子表格。您會看到銷售代表的姓名列在一列中，分別為“Sarah Chen”、“Mike Johnson”和“Lisa Park”。 TextSplit 可以自動完成這項工作，無需手動將每個姓名重新輸入到單獨的欄位中。
公式如下：
=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])
以下是每位老師所做的工作：
- 文本： 包含要拆分的文字的儲存格。
- 列分隔符號： 分隔資料的字元（例如空格、逗號或分號）。
- row_delimiter（可選）： 在拆分為行和列時使用。
- ignore_empty（可選）： TRUE 忽略空值，FALSE 保留它們（預設為 FALSE）。
- match_mode（可選）： 控制大小寫敏感度（0 表示區分大小寫，1 表示不區分大小寫）。
- pad_with（可選）： 當結果長度不等時，用什麼來填滿空白儲存格？
例如，對於銷售代表姓名，我將使用以下公式將姓名拆分到單獨的欄位中：
=TEXTSPLIT(A2, " ")
函數會根據您的資料自動建立所需數量的列。雖然這種基本方法在大多數情況下都適用，但還有一些其他參數可以讓您更精細地控制 Excel 中的 TEXTSPLIT 函數.
4. 文字加入
將多個儲存格合併為一個儲存格
TEXTJOIN 與 TEXTSPLIT 相反。它從多個單元格中提取文本，並使用您選擇的分隔符號將它們合併到一個單元格中。當您需要建立連續的值（例如完整地址、產品描述或電子郵件清單）時，此功能非常有用。
公式如下：
=TEXTJOIN(分隔符號, 忽略空值, 文字1, [文本2], ...)
以下是每個參數控制的內容：
- 分隔符號： 分隔嵌入值的字元或文字（逗號、空格、破折號等）。
- 忽略空： TRUE 表示忽略空白儲存格，FALSE 表示將其包含在結果中。
- 文本1、文本2等： 您想要合併的儲存格或範圍（您可以指定單一儲存格或整個範圍）。
查看銷售電子表格，如果我有分別用於名字和地區的列，但需要將它們合併在一個列中，我會使用 TEXTJOIN。 忽略空 設定為 TRUE 表示自動跳過任何空白儲存格。
=TEXTJOIN(" - ", TRUE, B2, D2)在不同的文本整合方法之間進行選擇時，請理解 CONCAT 和 TEXTJOIN 函數之間的差異 它可以幫助您根據特定的資料整合需求選擇正確的工具。
3. 選擇學校
指定資料的特定列。
CHOOSECOLS 允許您從某個範圍中提取特定列，而無需複製貼上或建立引用。如果您擁有一個大型資料集，但只需要第 2、5 和 8 列進行分析，則此函數將提取您需要的部分並丟棄其餘部分。
根據銷售數據，我可能只想提取銷售人員和銷售人員姓名，而忽略訂單日期、產品類別和其他詳細資訊。 CHOOSECOLS 函數無需手動選擇和複製列，而是建立動態引用，並在來源資料變更時自動更新。
此函數遵循以下公式：
=CHOOSECOLS(array, col_num1, [col_num2], ...)
每個參數的工作原理如下：
- 數組： 包含來源資料的範圍或表格（可以是像 A1:F100 這樣的儲存格範圍或表格參考）。
- 列號1： 您要提取的第一列的編號（第一列為 1，第二列為 2，等等）。
- col_num2等： 您想要包含的附加列號（可選 - 您可以根據需要指定任意數量）。
例如，如果我想從第 2 列中提取銷售代表的姓名並從第 9 列中提取他們的狀態，我會使用：
=CHOOSECOLS(A1:I23, 2, 9)
該函數將兩列以流式數組的形式傳回，並自動調整大小以適應資料。這就是為什麼 CHOOSECOLS 是 Excel 函數可以幫你節省很多時間它消除了處理大型資料集時使用多個 VLOOKUP 公式或手動複製列的需要。
Excel 也具有 CHOOSEROWS 函數，其運作方式類似，但選擇特定的行而不是列，使用帶有行號的相同公式結構。
2. 拿取和放下
擷取部分數據
TAKE 和 DROP 配合使用，可以擷取資料範圍的特定部分。 TAKE 從資料集的開頭或結尾提取特定數量的行或列，而 DROP 則從開頭或結尾刪除行或列，保留剩餘部分。
這些函數是進行資料採樣的精準工具。無論您只需要前十行資料進行快速分析，還是想要刪除阻礙計算的標題行，這些函數都能輕鬆完成。
TAKE 使用以下公式：
=TAKE(數組, 行, [列])
DROP 遵循類似的模式：
=DROP(數組, 行, [列])
這兩個函數的參數作用如下：
- 數組： 您想要擷取或修改的來源資料範圍。
- 行： 要取得/刪除的行數（正數從頂部開始，負數從底部開始）。
- 列（可選）：
您想要取得或刪除的列數（左側為正數，右側為負數）。
若要取得前五行銷售數據，請使用以下公式：
=TAKE(A1:C100, 5)
若要刪除前 20 行並使用乾淨的數據，請嘗試：
=DROP(A1:C23, 20)
您可以組合行和列運算。例如，以下公式可得出前十行和前三列：
=TAKE(A1:F23, 10, 3)
這些函數非常有用，尤其是當你需要自動調整的動態資料子集時。學習 如何在 Excel 中使用 TAKE 和 DROP 函數 它為您提供了創建適應不斷變化的資料集大小的靈活報告的可能性。
1. 骨料
強大的運算能力，處理混亂的數據
AGGREGATE 將 19 種不同的統計函數的功能整合到一個靈活的公式中。它的獨特之處在於能夠忽略錯誤、隱藏行或篩選資料——而 SUM 或 AVERAGE 等標準函數則無法可靠地做到這一點。
如果您的數據包含一些 #N/A 錯誤，或者您篩選了某些區域，AGGREGATE 可以計算總和、平均值或其他統計數據，而不會影響您的結果。我發現它在處理可見性和數據品質頻繁變化的動態數據集時非常有用。
句子結構包括幾個部分：
=AGGREGATE(function_num, options, array, [k])
每個標準控制計算的不同面向：
- 函數編號： 1 到 19 之間的數字，指定要使用的函數（1=AVERAGE、4=MAX、9=SUM、12=MEDIAN 等）。
- 選項： 控制在計算過程中忽略的內容（0=無，1=隱藏行，2=錯誤值，3=隱藏行和錯誤，5=僅錯誤值，6=隱藏行和錯誤值）。
- 數組： 要計算的儲存格範圍。
- k（可選）：
- 僅與某些函數一起使用，例如 LARGE、SMALL 或 PERCENTILE。
為了匯總顯示的銷售額並忽略任何錯誤，我可以使用：
=AGGREGATE(9, 6, D2:D23)
數字 9 指定 SUM，數字 6 告訴函數忽略隱藏行和錯誤值。
這種強大的運算能力正是包含 AGGREGATE 的原因。 每個辦公室職員都應該知道的 Excel 函數列表—它處理簡單功能無法有效管理的現實世界數據的混亂。
值得使用的內建工具
最重要的 Excel 函數通常不是人們最先學習的。然而，它們解決了實際電子表格工作中出現的一些細微問題，包括處理雜亂的文字資料、從大型資料集中提取特定部分以及對不完整資料執行計算。我們討論的所有函數都不需要高階 Excel 技能。不過，TEXTSPLIT、CHOOSECOLS、TAKE 和 DROP 僅在 Microsoft 365 和 Excel 網頁版中可用。
下次當你發現自己需要重複清理資料或手動複製列時，請記住這些功能的存在。它們已經內建在 Excel 中，可以處理這些繁瑣的工作，讓你可以專注於資料真正想要表達的意思。
