Trong hệ sinh thái Excel, hàm IF được xem là “xương sống” của mọi bảng tính, là công cụ đầu tiên mà bất kỳ ai làm quen với xử lý dữ liệu đều phải thành thạo. Tuy nhiên, khi chuyển đổi môi trường làm việc sang Power Query – công cụ trích xuất và biến đổi dữ liệu mạnh mẽ của Microsoft – nhiều người dùng Excel lâu năm thường cảm thấy lúng túng. Lý do đơn giản: Hàm IF trong Power Query hoạt động khác biệt hoàn toàn so với hàm IF truyền thống trong Excel.
Nếu bạn đang tìm cách áp dụng logic điều kiện (Conditional Logic) để làm sạch và xử lý dữ liệu lớn, bài viết này từ Thủ Thuật sẽ là cẩm nang toàn diện dành cho bạn. Chúng ta sẽ cùng khám phá hai phương pháp cốt lõi: sử dụng giao diện Conditional Column (dành cho người mới bắt đầu) và viết mã M Language (dành cho người dùng nâng cao) để xử lý các tình huống phức tạp.
Tại sao Logic IF trong Power Query lại khác biệt?
Trong Excel truyền thống, chúng ta quen thuộc với cấu trúc hàm: =IF(logical_test, value_if_true, value_if_false). Tuy nhiên, Power Query sử dụng ngôn ngữ công thức M, hoạt động dựa trên tư duy lập trình nhiều hơn.
Để thực hiện logic “Nếu… thì…”, bạn có hai lựa chọn:
- Conditional Column (Cột có điều kiện): Sử dụng giao diện đồ họa, không cần viết mã. Phù hợp cho các logic đơn giản.
- Custom Column với M Code: Viết lệnh trực tiếp. Phù hợp cho các logic phức tạp (kết hợp AND/OR, tính toán lồng nhau).
Chúng ta sẽ thực hành cả hai phương pháp này thông qua một bài toán thực tế dưới đây.
Chuẩn bị dữ liệu thực hành
Để bài hướng dẫn trực quan nhất, hãy giả định chúng ta có một bảng dữ liệu bán hàng. Bạn có thể tải dữ liệu vào Power Query bằng cách chọn vùng dữ liệu trong Excel và nhấn Data -> From Table/Range.
Bài toán đặt ra: Chúng ta cần tính toán giá trị đơn hàng mới dựa trên các quy tắc sau:
- Tình huống 1 (Cơ bản): Nếu đơn hàng rơi vào Chủ Nhật, áp dụng mức phụ thu (Premium) tăng thêm 10%.
- Tình huống 2 (Nâng cao): Nếu là Chủ Nhật, tăng 10% phụ thu, NHƯNG nếu sản phẩm là “Tiger” hoặc “Farmhouse Bloomer”, sẽ được giảm giá 5% (chồng chéo lên mức tăng giá).
Minh họa: Dữ liệu mẫu chứa thông tin ngày tháng và loại sản phẩm.
Phương pháp 1: Sử dụng Conditional Column (Không cần viết mã)
Đây là cách tiếp cận nhanh nhất nếu bạn không muốn đụng đến các dòng code. Chúng ta sẽ giải quyết Tình huống 1 (Tăng 10% giá vào Chủ Nhật).
Bước 1: Trích xuất thông tin Ngày trong tuần
Dữ liệu gốc thường chỉ có ngày tháng (Date). Để Power Query hiểu đâu là Chủ Nhật, bạn cần tách tên thứ ra.
- Chọn cột Date.
- Trên thanh công cụ, chọn Add Column -> Date -> Day -> Day Name.
Trích xuất tên ngày trong tuần trong Power Query
Bước 2: Thiết lập Cột có điều kiện
- Chọn Add Column -> Conditional Column.
- Hộp thoại xuất hiện, bạn điền các thông số như sau:
- New column name: Đặt tên là “Sunday Premium”.
- Column Name: Chọn cột
Day Namevừa tạo. - Operator: Chọn
Equals(Bằng). - Value: Nhập
Sunday(lưu ý chính xác chữ hoa/thường). - Output: Nhập
1.1(Tức là 110%). - Else: Nhập
1(Giữ nguyên giá).
Thiết lập thông số cho Conditional Column
Lưu ý: Giao diện Conditional Column cung cấp nhiều toán tử so sánh hữu ích khác như:
- Does not equal (Không bằng)
- Begins/Ends with (Bắt đầu/Kết thúc với)
- Contains (Chứa ký tự)
Nếu muốn thêm nhiều điều kiện (ví dụ: Thứ 7 tăng 5%, Chủ Nhật tăng 10%), bạn chỉ cần nhấn nút Add rule để thêm dòng điều kiện mới. Logic sẽ chạy từ trên xuống dưới; nếu điều kiện đầu tiên đúng, các điều kiện sau sẽ bị bỏ qua.
Thêm nhiều quy tắc trong Conditional Column
Bước 3: Tính toán giá trị cuối cùng
Sau khi có cột hệ số (1.1 hoặc 1), chúng ta tính giá cuối cùng.
- Chọn Add Column -> Custom Column.
- Nhập công thức:
=[Value] * [Sunday Premium].
Tính toán giá trị cuối cùng bằng Custom Column
Đánh giá: Phương pháp này trực quan, dễ dùng nhưng hạn chế khi gặp logic phức tạp (như AND/OR kết hợp).
Phương pháp 2: Sử dụng M Code (Custom Column)
Khi yêu cầu xử lý dữ liệu trở nên phức tạp hơn (Tình huống 2), việc dùng giao diện click chuột sẽ trở nên cồng kềnh. Đây là lúc sức mạnh của ngôn ngữ M lên ngôi.
Cấu trúc câu lệnh IF trong Power Query (Ngôn ngữ M)
Khác với Excel, cú pháp của M gần giống với ngôn ngữ nói tiếng Anh:
if [Điều kiện] then [Kết quả nếu đúng] else [Kết quả nếu sai]Quy tắc vàng: Ngôn ngữ M phân biệt chữ hoa chữ thường (Case Sensitive).
- Đúng:
if,then,else,and,or. - Sai:
IF,Then,Else,AND(Công thức sẽ báo lỗi ngay lập tức).
Giải quyết Tình huống 1 bằng M Code
Thay vì dùng Conditional Column, ta làm trực tiếp:
- Chọn Add Column -> Custom Column.
- Nhập công thức sau:
if [Day Name] = "Sunday" then [Value] * 1.1 else [Value]
Viết lệnh IF cơ bản trong Custom Column
Bạn sẽ thấy cú pháp này gọn gàng hơn nhiều so với việc tạo cột trung gian.
Giải quyết Tình huống 2: Logic phức tạp (AND/OR)
Yêu cầu:
- Ngày là Chủ Nhật.
- Sản phẩm là “Tiger” HOẶC “Farmhouse Bloomer”.
- Kết quả: Tăng 10% (phí chủ nhật) nhưng giảm 5% (ưu đãi sản phẩm) -> Hệ số:
1.1 * 0.95. - Các trường hợp Chủ Nhật còn lại: Chỉ tăng 10%.
- Ngày thường: Giữ nguyên giá.
Trong M Code, chúng ta sử dụng từ khóa and và or để kết nối các điều kiện. Dưới đây là 2 cách viết tối ưu:
Cách 1: Sử dụng toán tử logic kết hợp
Chúng ta gộp các điều kiện vào một dòng lệnh. Lưu ý sử dụng dấu ngoặc đơn () để phân tách thứ tự ưu tiên cho or.
if [Day Name] = "Sunday" and ([Product] = "Tiger" or [Product] = "Farmhouse Bloomer")
then [Value] * 1.1 * 0.95
else if [Day Name] = "Sunday"
then [Value] * 1.1
else [Value]
Logic IF kết hợp AND và OR trong Power Query
Cách 2: Sử dụng IF lồng nhau (Nested IF)
Cách này giúp logic tường minh hơn, dễ kiểm soát từng lớp điều kiện.
if [Day Name] = "Sunday" then
if [Product] = "Tiger" or [Product] = "Farmhouse Bloomer"
then [Value] * 1.1 * 0.95
else [Value] * 1.1
else [Value]
Sử dụng IF lồng nhau để xử lý điều kiện phức tạp
Giải thích:
- Đầu tiên kiểm tra xem có phải “Sunday” không.
- Nếu đúng là Sunday, tiếp tục kiểm tra tên sản phẩm.
- Nếu không phải Sunday, trả về giá trị gốc ngay lập tức.
Kết luận
Việc chuyển đổi tư duy từ hàm Excel truyền thống sang câu lệnh if trong Power Query có thể gây bỡ ngỡ ban đầu, đặc biệt là quy tắc viết thường (if thay vì IF). Tuy nhiên, một khi đã nắm vững, bạn sẽ thấy Power Query mang lại khả năng kiểm soát dữ liệu vượt trội.
- Sử dụng Conditional Column: Khi bạn cần xử lý nhanh, điều kiện đơn giản và không muốn gõ phím.
- Sử dụng Custom Column (M Code): Khi bạn cần xử lý các logic đa tầng, kết hợp nhiều điều kiện
and/orhoặc muốn tối ưu hóa các bước xử lý (tránh tạo cột thừa).
Hy vọng bài viết này giúp bạn tự tin hơn trong việc xử lý dữ liệu với Power Query. Hãy thực hành ngay với dữ liệu của bạn để ghi nhớ cú pháp nhé!
Nguồn tham khảo: Excel Off The Grid, Microsoft Learn.
Discussion about this post