Power Query và Lệnh If Điều Kiện – Khóa học Excel Trực Tuyến

Ở bài viết trước, chúng ta đã khái quát qua cách thức hoạt động của Power Query. Bây giờ, hãy cùng Siêu Marketing đi chi tiết hơn vào lệnh If trong Power Query nào!

IF trong Excel là hàm không thể thiếu, là một trong những hàm cơ bản đầu tiên cần nắm vững. Mặc dù các Hàm trong Power Query đã được trình bày ở bài trước, nhưng phiên bản If cho Power Query lại chưa được nhắc đến. Chắc hẳn là có hàm if… đúng không nào? … Có vẻ như vậy.

Khi sử dụng lệnhif Power Query, chúng ta cần áp dụng một cách làm dựa trên kỹ thuật lập trình hơn là dựa vào các hàm như trong Excel. Nếu bạn có kinh nghiệm lập trình VBA hay các ngôn ngữ khác, bạn có thể sẽ thấy quen thuộc với cách làm này. Nhưng nếu chỉ sử dụng Excel ở mức độ cơ bản, đây có thể là một điều mới mẻ.

Dưới đây là hai phương án cho bạn:

  • Lựa chọn dùng Cột Điều Kiện cho những logic if đơn giản
  • Tạo mã M cho những logic if phức tạp hơn.

Bài viết này sẽ đề cập tới cả hai phương án trên.

Download

Để có tài liệu tham khảo cho bài học này, xin mời quay lại xem bài viết trước đây của chúng tôi về Power Query. Bạn cũng nên tải về các file mẫu để thực hành theo các ví dụ tiếp theo. Nhấp vào đây để đăng ký và tải về từ phần Download.

Bài viết sử dụng tệp mẫu với tên Example 15 – If Function.xlsx

Ví dụ minh họa

File chỉ bao gồm một Bảng. Hãy chọn bất kỳ một ô nào trong Bảng và click vào Data -> From Table/Range để nhập dữ liệu vào Power Query.

Chúng ta sẽ xem xét ví dụ với giả định sau:

Trường hợp 1: Chủ nhật được cộng thêm 10% phụ phí bảo hiểm (đơn giản)

Trường hợp 2: Chủ nhật, ngoài 10% phụ phí bảo hiểm, còn giảm giá 5% cho hai sản phẩm (Tiger & Farmhouse Bloomer) cũng trong ngày đó (phức tạp).

Logic If thông qua cột có điều kiện

Chúng ta hãy bắt đầu với Trường hợp 1 và sử dụng tính năng Cột Điều Kiện

Trường hợp 1 – Phụ phí ngày Chủ Nhật

Lựa chọn cột Ngày tháng, sau đó thêm cột hiển thị tên ngày bằng cách click vào Add Column -> Date -> Day -> Day Name.

1- Lệnh If trong Power Query dựa trên điều kiện
1- Lệnh If trong Power Query dựa trên điều kiện

Sau đó, click vào Add Column -> Conditional Column

Điền thông tin theo hướng dẫn sau trong cửa sổ Add Conditional Column:

Cột mới tên là: Sunday Premium

2- Lệnh If trong Power Query dựa trên điều kiện
2- Lệnh If trong Power Query dựa trên điều kiện

Trường hợp Day Name khi bằng Sunday

Hãy nhấn vào OK để xác nhận công thức bạn đã chọn.

Trong trường hợp được nêu trên, ta chỉ áp dụng một số lựa chọn từ hộp thoại Conditional Column.

Có một số lựa chọn khác mà bạn có thể sử dụng:

  • Equals: Tức là giá trị bằng nhau
  • Does not equal: Giá trị không bằng nhau
  • Begins with: Bắt đầu bằng
  • Does not begin with: Không bắt đầu bằng
  • Ends with: Kết thúc bằng
  • Does not end with: Không kết thúc bằng
  • Contains: Chứa
  • Does not contain: Không chứa

Trong hộp thoại Add Conditional Column, bạn có thể click vào nút 123ABC để thêm giá trị từ cột khác hoặc thêm tham số thay vì đặt giá trị cố định.

Bạn cũng có thể tạo nhiều câu lệnh điều kiện IF tùy ý bằng cách nhấp vào Add rule. Các câu lệnh này sẽ được thực hiện theo thứ tự: nếu câu lệnh đầu tiên không được kích hoạt, hệ thống sẽ chuyển sang câu lệnh tiếp theo. Tuy nhiên, nếu câu lệnh đầu tiên đã đúng thì các điều kiện sau sẽ không được xét đến.

3- Câu lệnh If Power Query cho logic có điều kiện
3- Câu lệnh If Power Query cho logic có điều kiện

Để thêm công thức mới, chọn Add Column -> Custom Column để mở ra hộp thoại Custom Column.

Nhập công thức dưới đây:

=[Value] * [Sunday Premium]

Sau khi chọn công thức Adjusted Value, nhấn OK để xác nhận.

4- Câu lệnh If Power Query cho logic có điều kiện
4- Câu lệnh If Power Query cho logic có điều kiện

Bạn cũng có thể nhân hai cột với nhau bằng cách lựa chọn Add Column -> Standard -> Multiply, nhưng cách làm với Custom Column được ưa chuộng hơn.

Chắc hẳn bạn thấy việc áp dụng logic điều kiện trở nên khá đơn giản phải không nào?

Tình huống 2 – Tính phí bảo hiểm ngày Chủ Nhật và giảm giá sản phẩm

Việc sử dụng Conditional Column với nhiều hơn một điều kiện có thể là thách thức, đôi khi là khá khó khăn.

Tôi đã xem qua nhiều biến thể để giải quyết tình huống số 2, tuy nhiên tôi sẽ không trình bày chúng đây… Vì thực tế, Conditional Column chỉ hợp với các yêu cầu đơn giản; nó không hỗ trợ việc sử dụng logic AND hay OR.

Thay vào đó, chúng ta sẽ tự viết code Power Query M. Đừng lo lắng, tôi tin bạn hoàn toàn có thể làm được điều đó

Logic IF trong Power Query bằng việc tự viết mã M

Hãy dành một ít thời gian để hiểu cách Power Query hoạt động trước khi bắt đầu với các ví dụ cụ thể.

Trong Excel, hàm IF sẽ được viết như sau:

=IF( [thing] = “thing 2”, [if true then do this], [if false then do this] )

Vậy nên, bạn sẽ thấy quá trình này dễ dàng hơn khi xem xét như sau:

Xóa bỏ các dấu ngoặc đơn và

thay thế nó với các lựa chọn sau:

Sau khi thay thế dấu phẩy đầu tiên bằng then và dấu phẩy cuối cùng bằng else, cũng như thay đổi “IF” thành if, chúng ta sẽ có cú pháp mã M như sau:

= if [thing] = “thing 2” then [do this if true] else [do this if false]

Với việc thực hành liên tục, bạn sẽ ghi nhớ cách viết này. Một điều tuyệt vời nữa là nó nghe có vẻ giống câu tiếng Anh thông thường, giúp việc hiểu nó trở nên dễ dàng hơn so với Excel.

Chúng ta cũng có thể chồng chất nhiều điều kiện if bằng cách dùng cùng phương pháp if – then – else, như được minh họa dưới đây:

= if [thing to to test #1] = “something else” then [do this if true]

else if [thing to to test #2] = “something else” then [do this if true] else 

[do this if false]

Bạn hoàn toàn có thể áp dụng nhiều điều kiện if lồng nhau. Quá trình này tương tự như thêm nhiều quy tắc trong Cột có điều kiện.

Thông qua việc sử dụng logic AND và OR trong mã cũng khá đơn giản, chỉ việc thêm vào các từ and hoặc or là được. Đoạn mã sau đây là một ví dụ về việc sử dụng logic and:

= if [thing to to test #1] = “something else” 

and [thing to to test #2] = “something else #2” 

then [do this if true] else [do this if false]

Khi ta kết hợp cùng lúc logic andor, phải sử dụng ngoặc để bảo đảm rằng các phần tử đã được đánh giá đúng thứ tự.

Đó là các nguyên tắc cơ bản về sử dụng if trong Power Query, và giờ đây hãy tới với các ví dụ cụ thể.

Bắt đầu lại từ bước đầu tiên bằng việc nạp Bảng vào Power Query và sau đó tạo thêm một cột có tên Week Day như đã làm trong ví dụ trước.

Tình huống 1 – Phí bảo hiểm Chủ Nhật

5- Câu lệnh If Power Query cho logic có điều kiện
5- Câu lệnh If Power Query cho logic có điều kiện

Để thêm một cột mới vào bảng, bạn cần chọn Add Column  -> Custom Column

Sau đó áp dụng công thức dưới đây:

6- Câu lệnh If Power Query cho logic có điều kiện
6- Câu lệnh If Power Query cho logic có điều kiện

= if [Day Name] = “Sunday” then [Value] * 1.1 else [Value]

Lưu ý rằng các từ if, thenelse đều được viết thường. Power Query phân biệt chữ hoa chữ thường, do đó nếu viết sai cú pháp này, công thức sẽ không thực hiện chính xác.

Chọn OK để áp dụng công thức.

Tình huống 2 – Phí bảo hiểm Chủ Nhật và giảm giá sản phẩm

Tùy thuộc vào dữ liệu, chúng ta có thể gặp một trong ba trường hợp dưới đây:

  • Áp dụng giảm giá sản phẩm và phí bảo hiểm Chủ Nhật
  • Chỉ có chi phí bảo hiểm Chủ Nhật
  • Không áp dụng cả phí bảo hiểm lẫn giảm giá

Cách viết công thức thứ nhất:

= if [Day Name] = “Sunday” and ([Product] = “Tiger” or [Product] = “Farmhouse Bloomer”) then 

[Value] * 1.1 * 0.95

còn nếu điều kiện không đúng sau whichClause thì kInstruction sẽ được thực hiện.

Nếu [Name] là “Sunday”

thì [Value] được nhân với 1.1

ngoài ra, giữ nguyên [Value]

7- Cú pháp lệnh If trong Power Query khi có điều kiện
7- Cú pháp lệnh If trong Power Query khi có điều kiện

Cần phải bổ sung một số dấu ngoặc ở đây để chắc chắn rằng biểu thức được thực hiện theo đúng trật tự. Phần mệnh or cần được xét trước, do đó chúng ta sẽ đặt nó trong ngoặc.

Phương án # 2:

Một cách làm khác mà bạn có thể áp dụng là:

= nếu [Day Name] chính là “Sunday” thì

nếu [Product] bằng “Tiger” hoặc [Product] bằng “Farmhouse Bloomer” thì [Value] nhân với 1.1 rồi nhân thêm 0.95

còn không thì [Value] chỉ nhân với 1.1

8- Biểu thức lệnh If trong Power Query khi áp dụng điều kiện
8- Biểu thức lệnh If trong Power Query khi áp dụng điều kiện

hoặc nếu không thì dùng [Value]

Kết luận

Logic điều kiện trong lệnh if của Power Query có một số khác biệt so với Excel. Dẫu vậy, nếu bạn luyện tập một vài lần, bạn sẽ nhanh chóng thành thạo. Chúc bạn thành công!

Trương Thành Tài
0
    0
    Đơn hàng
    Đơn hàng trốngQuay lại Shop