Power Query:If 語句 - 嵌套的 If 和多條件
在 Excel 的 Power Query 中,IF 語句是最常用的功能之一,用於檢查條件並根據結果返回特定值(TRUE 或 FALSE)。這個 IF 語句與 Excel 的 IF 函數有一些差異。在本教程中,我將介紹此 IF 語句的語法以及一些簡單和複雜的範例。
Power Query 的基本 If 語句語法
在 Power Query 中,語法如下:
- logical_test: 您要測試的條件。
- value_if_true: 如果結果為 TRUE,則返回的值。
- value_if_false: 如果結果為 FALSE,則返回的值。
在 Excel 的 Power Query 中,有兩種方式可以創建這種條件邏輯:
- 使用條件列功能來處理一些基本場景;
- 編寫 M 碼以處理更進階的場景。
在接下來的部分中,我將介紹一些使用此 If 語句的範例。
使用條件列的 Power Query If 語句
範例 1:基本 If 語句
這裡,我將介紹如何在 Power Query 中使用此 If 語句。例如,我有一份產品報告,如果產品狀態是「舊」,顯示 50% 折扣;如果產品狀態是「新」,顯示 20% 折扣,如下圖所示。
1. 從工作表中選擇數據表,然後在 Excel 2019 和 Excel 365 中,點擊 數據 > 從表格/區域,見截圖:
注意:在 Excel 2016 和 Excel 2021 中,點擊 數據 > 從表格,見截圖:
2. 接著,在打開的 Power Query 編輯器窗口中,點擊 添加列 > 條件列,見截圖:
3. 在彈出的 添加條件列 對話框中,請進行以下操作:
- 新列名稱:輸入新列的名稱;
- 然後,指定您需要的條件。例如,我將指定 如果 狀態 等於 舊 則 50% 否則 20%;
- 列名稱:評估您的 If 條件所針對的列。這裡,我選擇 狀態。
- 運算符:使用的條件邏輯。選項會根據所選 列名稱 的數據類型而有所不同。
- 文本:開頭是、開頭不是、等於、包含等。
- 數字:等於、不等於、大於或等於等。
- 日期:之前、之後、等於、不等於等。
- 值:用於比較的具體值。它與 列名稱 和 運算符 共同構成一個條件。
- 輸出:如果條件滿足,則返回的值。
- 否則:當條件為假時返回的另一個值。
4. 然後,點擊 確定 按鈕返回到 Power Query 編輯器窗口。現在,已添加了一個新的折扣列,見截圖:
5. 如果您想將數字格式化為百分比,只需點擊 折扣 列標題上的 ABC123 圖標,並根據需要選擇 百分比,見截圖:
6. 最後,請點擊 首頁 > 關閉並加載 > 關閉並加載 將此數據加載到新的工作表中。
範例 2:複雜 If 語句
使用此條件列選項,您還可以在 添加條件列 對話框中插入兩個或多個條件。請按以下步驟操作:
1. 選擇數據表,並通過點擊 數據 > 從表格/區域 進入 Power Query 編輯器窗口。在新窗口中,點擊 添加列 > 條件列。
2. 在彈出的 添加條件列 對話框中,請進行以下操作:
- 在 新列名稱 文本框中輸入新列的名稱;
- 在第一個條件字段中指定第一個條件,然後點擊 添加條款 按鈕以添加其他條件字段。
3. 完成條件後,點擊 確定 按鈕返回到 Power Query 編輯器窗口。現在,您將獲得一個包含所需結果的新列。見截圖:
4. 最後,請點擊 首頁 > 關閉並加載 > 關閉並加載 將此數據加載到新的工作表中。
通過編寫 M 碼實現 Power Query If 語句
通常,條件列對於一些基本場景很有幫助。有時候,您可能需要使用多條件與 AND 或 OR 邏輯。在這種情況下,您必須在自定義列中編寫 M 碼以處理更複雜的場景。
範例 1:基本 If 語句
以第一個數據為例,如果產品狀態是「舊」,顯示 50% 折扣;如果產品狀態是「新」,顯示 20% 折扣。對於編寫 M 碼,請按以下步驟操作:
1. 選擇表格,並點擊 數據 > 從表格/區域 進入 Power Query 編輯器窗口。
2. 在打開的窗口中,點擊 添加列 > 自定義列,見截圖:
3. 在彈出的 自定義列 對話框中,請進行以下操作:
- 在 新列名稱 文本框中輸入新列的名稱;
- 然後,輸入以下公式: if [Status] = "Old " then "50% " else "20% " 到 自定義列 公式 框中。
4. 然後,點擊 確定 關閉此對話框。現在,您將獲得所需的結果:
5. 最後,點擊 首頁 > 關閉並加載 > 關閉並加載 將此數據加載到新的工作表中。
範例 2:複雜 If 語句
通常,為了測試子條件,您可以嵌套多個 If 語句。例如,我有以下數據表。如果產品是「連衣裙」,給原價 50% 的折扣;如果產品是「毛衣」或「帽衫」,給原價 20% 的折扣;其他產品保持原價。
1. 選擇數據表,並點擊 數據 > 從表格/區域 進入 Power Query 編輯器窗口。
2. 在打開的窗口中,點擊 添加列 > 自定義列。在打開的 自定義列 對話框中,請進行以下操作:
- 在 新列名稱 文本框中輸入新列的名稱;
- 然後,輸入以下公式到 自定義列 公式 框中。
- = if [Product] = "Dress" then [Price] * 0.5 else
if [Product] = "Sweater" then [Price] * 0.8 else
if [Product] = "Hoodie" then [Price] * 0.8
else [Price]
3. 然後,點擊 確定 按鈕返回到 Power Query 編輯器窗口,您將獲得一個包含所需數據的新列,見截圖:
4. 最後,點擊 首頁 > 關閉並加載 > 關閉並加載 將此數據加載到新的工作表中。
OR 邏輯執行多個邏輯測試,如果任何邏輯測試為真,則返回真結果。語法如下:
假設,我有以下表格,現在我希望新列顯示:如果產品是「連衣裙」或「T恤」,則品牌為「AAA」,其他產品的品牌為「BBB」。
1. 選擇數據表,並點擊 數據 > 從表格/區域 進入 Power Query 編輯器窗口。
2. 在打開的窗口中,點擊 添加列 > 自定義列,在打開的 自定義列 對話框中,請進行以下操作:
- 在 新列名稱 文本框中輸入新列的名稱;
- 然後,輸入以下公式到 自定義列公式 框中。
- = if [Product] = "Dress" or [Product] = "T-shirt" then "AAA"
else "BBB"
3. 然後,點擊 確定 按鈕返回到 Power Query 編輯器窗口,您將獲得一個包含所需數據的新列,見截圖:
4. 最後,點擊 首頁 > 關閉並加載 > 關閉並加載 將此數據加載到新的工作表中。
AND 邏輯在單一 If 語句中執行多個邏輯測試。所有測試都必須為真才能返回真結果。如果任何測試為假,則返回假結果。語法如下:
以上述數據為例。我希望新列顯示:如果產品是「連衣裙」且訂單大於 300,則對原價應用 50% 的折扣;否則,保持原價。
1. 選擇數據表,並點擊 數據 > 從表格/區域 進入 Power Query 編輯器窗口。
2. 在打開的窗口中,點擊 添加列 > 自定義列。在打開的 自定義列 對話框中,請進行以下操作:
- 在 新列名稱 文本框中輸入新列的名稱;
- 然後,輸入以下公式到 自定義列公式 框中。
- = if [Product] ="Dress" and [Order] > 300 then [Price]*0.5
else [Price]
3. 然後,點擊 確定 按鈕返回到 Power Query 編輯器窗口,您將獲得一個包含所需數據的新列,見截圖:
4. 最後,您應該通過點擊 首頁 > 關閉並加載 > 關閉並加載 將此數據加載到新的工作表中。
好的,前面的例子很容易理解。現在,讓我們增加難度。您可以結合 AND 和 OR 形成任何您能想像的條件。在此類型中,您可以在公式中使用括號來定義複雜規則。
同樣以上述數據為例,假設我希望新列顯示:如果產品是「連衣裙」且其訂單大於 300,或者產品是「褲子」且其訂單大於 300,則顯示「A+」,否則,顯示「其他」。
1. 選擇數據表,並點擊 數據 > 從表格/區域 進入 Power Query 編輯器窗口。
2. 在打開的窗口中,點擊 添加列 > 自定義列。在打開的 自定義列 對話框中,請進行以下操作:
- 在 新列名稱 文本框中輸入新列的名稱;
- 然後,輸入以下公式到 自定義列公式 框中。
- =if ([Product] = "Dress" and [Order] > 300 ) or
([Product] = "Trousers" and [Order] > 300 )
then "A+"
else "Other"
3. 然後,點擊 確定 按鈕返回到 Power Query 編輯器窗口,您將獲得一個包含所需數據的新列,見截圖:
4. 最後,您應該通過點擊 首頁 > 關閉並加載 > 關閉並加載 將此數據加載到新的工作表中。
在 自定義列公式 框中,您可以使用以下邏輯運算符:
- = : 等於
- <> : 不等於
- > : 大於
- >= : 大於或等於
- < : 小於
- <= : 小於或等於
最佳辦公效率工具
🤖 | Kutools AI 助手:基於智能執行方式革新數據分析:智能執行 | 生成代碼 | 創建自訂公式 | 分析數據並生成圖表 | 調用 Kutools 函數… |
熱門功能:查找、標記重複值或識別重複項 | 刪除空行 | 合併列或單元格而不丟失數據 | 四捨五入無需公式 ... | |
高級 LOOKUP: 多條件 VLookup | 多值 VLookup | 多表查找 | 模糊查找 .... | |
高級下拉列表:快速創建下拉列表 | 依賴下拉列表 | 多選下拉列表 .... | |
列管理器: 添加特定數量的列 | 移動列 | 切換隱藏列的可見狀態 | 比較區域和列 ... | |
特色功能:網格聚焦 | 設計檢視 | 增強編輯欄 | 工作簿與工作表管理器 | 資源庫(自動文本) | 日期提取器 | 合併資料 | 加密/解密儲存格 | 按列表發送電子郵件 | 超級篩選 | 特殊篩選(篩選粗體/斜體/刪除線...) ... | |
頂級 15 種工具集: 12 個文本工具(添加文本、刪除特定字符、...) | 50+ 圖表 類型(甘特圖、...) | 40+ 實用 公式(基於生日計算年齡、...) | 19 個插入工具(插入QR碼、根據路徑插入圖片、...) | 12 個轉換工具(金額轉大寫、匯率轉換、...) | 7 個合併與分割工具(高級合併行、分割儲存格、...) | ... 還有更多 |
使用 Kutools for Excel 提升您的 Excel 技巧,體驗前所未有的高效。 Kutools for Excel 提供超過 300 種高級功能來提高生產力並節省時間。 點擊這裡獲取您最需要的功能...
Office Tab 將標籤式界面帶到 Office,讓您的工作更加輕鬆
- 在 Word、Excel、PowerPoint、Publisher、Access、Visio 和 Project 中啟用標籤式編輯和閱讀。
- 在同一窗口的新標籤中打開和創建多個文檔,而不是在新窗口中。
- 將您的生產力提高 50%,每天為您減少數百次鼠標點擊!