Trong bài đăng này,  Học Excel Online sẽ khám phá chủ đề về các hàm tùy chỉnh. Nó là ai? Khi nào chúng ta nên sử dụng chúng? Và làm thế nào để tạo ra chúng? Bài viết này, có hơn 600 hàm tiêu chuẩn đã có trong Power Query. Hãy cùng Học Excel Online tìm hiểu ngay sau đây.

Tuy nhiên, những hàm đó không phải lúc nào cũng thực hiện những gì chúng ta cần hoặc theo cách hiệu quả nhất, do đó chúng ta có thể phải chuyển sang các hàm tùy chỉnh để đạt được kết quả cuối cùng mong muốn.

Các chức năng tùy chỉnh là gì?

Chức năng là các quy trình hoặc các bước thực hiện một cách nhất quán công việc. Ví dụ: Hàm SUM trong Excel sẽ liên tục cộng các giá trị hoặc hàm VLOOKUP sẽ luôn trả về giá trị khớp tương đương từ danh sách.

Tuy nhiên, vì các hàm trong Excel thường tính toán các kết quả dạng số và văn bản, điều này không có nghĩa là trường hợp này xảy ra. Một hàm có thể được sử dụng để thực hiện nhiều tác vụ khác nhau và điều này đặc biệt đúng với Power Query. Chúng ta có thể sử dụng một chức năng tùy chỉnh để lấy dữ liệu từ một trang web hoặc để đọc dữ liệu trong một tệp. Từ góc độ Power Query, tốt hơn là bạn nên xem các hàm như bất kỳ tác vụ thông thường có thể được áp dụng lặp đi lặp lại.

Tôi đã suy nghĩ khá lâu về việc có nên đưa các chức năng tùy chỉnh vào loạt bài này không vì chúng có thể khá phức tạp. Nhưng tôi quyết định đó là giá trị để loại trừ. Bài đăng này sẽ liên quan đến một chút mã hóa và thay đổi mã M được tạo tự động, vì vậy hãy theo dõi cẩn thận.

Chúng ta sẽ giải quyết chức năng này bằng cách làm việc qua hai ví dụ (1) một hàm tùy chỉnh dễ dàng (2) hàm tùy chỉnh dựa trên chuyển đổi.

Downloads

Để làm việc cùng với các ví dụ bên dưới, hãy tải xuống các tệp mẫu. Nhấp vào Click here để đăng ký và có quyền truy cập vào phần Downloads.

Chúng ta sẽ sử dụng cùng một tệp như một bài trước. Các ví dụ bên dưới sử dụng tệp có tên Example 15 – If Function.xlsx.

Tệp ví dụ chỉ chứa một Bảng. Để tải dữ liệu vào Power Query, hãy chọn bất kỳ ô nào trong Bảng đó và nhấp vào Data -> From Table/Range.

Đặt tên hữu ích cho truy vấn – Tôi đã gọi nó là Data và đây là cách tôi sẽ đề cập đến truy vấn này sau trong bài đăng.

Chức năng tùy chỉnh dễ dàng

Trong ví dụ đầu tiên này, chúng ta sẽ tạo một hàm tùy chỉnh rất dễ dàng. Nó hoàn toàn là để minh họa quá trình để khi chúng ta đi vào ví dụ sau, nó sẽ không phải là một rắc rối để tìm hiểu.

Tạo truy vấn trống bằng cách nhấp vào Home -> New Source -> Other Sources -> Blank Query.

Đặt tên cho truy vấn, vì mục đích minh họa, tôi đã đặt là myFunction1 (không sử dụng tên thật, hãy chọn một cái gì đó mô tả hơn, nhưng nó sẽ hoạt động cho minh họa của chúng ta).

Mở Advanced Editor cho truy vấn đó bằng cách nhấp vào View -> Advanced Editor (or Home -> Advanced Editor).

Cửa sổ Advanced Editor sẽ giông như thế này:

Mặc dù đó là Truy vấn trống, chúng ta vẫn được cung cấp một số mã ban đầu. Câu lệnh let và in sẽ vẫn còn trong mã cuối cùng, nhưng chúng ta sẽ thay đổi phần còn lại của nó.

Trong ví dụ đầu tiên, mục tiêu của chúng ta là tạo một hàm tùy chỉnh sẽ nhân giá trị với 10.

Nhập văn bản sau vào Advanced Editor.

GHI NHỚ: Mã M có phân biệt chữ hoa chữ thường, vì vậy hãy nhập cẩn thận.

Chúng ta sẽ làm rõ mã sau một chút trong bài đăng, nhưng hiện tại, chỉ cần nhấp vào Done để đóng cửa sổ Advanced Editor.

Bây giờ chúng ta sẽ thấy những điều sau đây. Đây là nơi chúng ta có thể kiểm tra chức năng mà chúng ta đã tạo. Nhập một số vào Value box và nhấp vào Invoke.

Nếu chúng ta đã thực hiện mọi thứ một cách chính xác, nó sẽ tính toán giá trị chính xác và hiển thị lại cho chúng ta.

Bằng cách gọi hàm Power Query sẽ tạo ra một truy vấn có tên là Invoke Function, mà chúng ta không cần đến hàm này, vì vậy nó có thể bị xóa. Trong cùng một danh sách truy vấn, bạn sẽ truy vấn cái mà chúng ta đã tạo ở đầu (mà chúng tôi gọi là Data) và hàm chúng ta đã tạo (gọi là myFunction1).

Tiếp theo, mở Data query mà chúng tôi đã tạo lúc đầu. Tạo một Custom Column (Add Column -> Custom Column) và sử dụng chức năng tùy chỉnh mà chúng ta đã tạo.

Mã để sử dụng trong cửa sổ chức năng tùy chỉnh là:

=myFunction1([Value])

Lưu ý: Từ [Value} trong hàm là tên của cột được sử dụng làm tham số.

Sử dụng một hàm tùy chỉnh cũng giống như sử dụng một hàm Power Query tiêu chuẩn.

Giải mã M

Hãy giải mã mà chúng ta đã tạo trước đó để hiểu nó hoạt động như thế nào.

Khai báo tham số

Dòng đầu tiên khai báo các tham số (hoặc đầu vào) sẽ được sử dụng trong Hàm. Trong ví dụ của chúng ta, nó chỉ có một đầu vào, mà tôi gọi là Value.

Dòng thứ hai xác định điểm bắt đầu của các bước chuyển đổi; không cần phải thay đổi bất cứ điều gì trong dòng này.

Tiếp theo, chúng ta tạo một bước, được gọi là nameOfStep. Có một số quy tắc về các bước đặt tên, đặc biệt là về việc sử dụng dấu cách. Tuy nhiên, thay vì làm điều đó ngay bây giờ, việc luôn loại trừ khoảng trắng khi tạo các bước theo cách thủ công sẽ dễ dàng hơn.

nameOfStep sẽ lấy Value (tham số được tạo ở dòng đầu tiên) và nhân nó với 10.

Cuối cùng, chúng ta trả về kết quả của hàm. in phần cuối của các phép chuyển đổi. Sau câu lệnh in, chúng ta khai báo tên của bước sẽ được trả về. Trong trường hợp của chúng ta, chúng ta chỉ có một bước, vì vậy chúng ta sẽ trả về nameOfStep.

Nhiều tham số và kiểu

Khi sử dụng các hàm Power Query tiêu chuẩn, bạn sẽ nhận thấy chúng có thể có nhiều tham số và yêu cầu các kiểu dữ liệu cụ thể. Chúng ta có thể làm điều tương tự trong các hàm tùy chỉnh của riêng mình, tất cả đều được xử lý trong khai báo tham số ở đầu truy vấn:

Khai báo kiểu đầu vào

Để khai báo một kiểu dữ liệu văn bản, chúng ta thay đổi đoạn mã như sau:

Để chỉ cho phép một bảng làm tham số, chúng ta sẽ sử dụng:

Khá dễ dàng, phải không. Như bạn đã biết, có rất nhiều kiểu dữ liệu. Nếu bạn không chắc chắn về mã cho kiểu dữ liệu, hãy sử dụng truy vấn khác, thực hiện thay đổi kiểu dữ liệu thông qua giao diện người dùng bình thường, sau đó xem lại mã đã tạo để xác định thay đổi.

Khai báo kiểu dữ liệu đầu ra

Power Query cũng có kiểu dữ liệu cho đầu ra của hàm; điều này được nêu bên ngoài dấu ngoặc.

Ví dụ trên cho thấy hàm nhận một giá trị văn bản làm tham số và kết quả trả về là một kiểu dữ liệu bảng.

Khai báo nhiều tham số

Để khai báo nhiều tham số cho hàm, chúng ta liệt kê chúng bên trong dấu ngoặc.

Khai báo trên có:

• Hai tham số được gọi là (1) myText (với kiểu dữ liệu văn bản) và (2) myTable (với kiểu dữ liệu bảng).
• Đầu ra đã được khai báo là một kiểu dữ liệu số.

Các chức năng tùy chỉnh có chứa các phép biến đổi

Khi chúng ta nhập dữ liệu từ web trong một bài đăng trước, chúng ta đã sử dụng cùng một phương pháp. Hãy xem bài viết đó để thực hành thêm một chút. Tuy nhiên, lần này, chúng ta sẽ làm một thứ gì đó phức tạp hơn một chút.

Đối với ví dụ này, chúng ta sẽ tính toán % giao dịch trên tổng số lần mua hàng cho khách hàng đó. Vì vậy, nếu ABC giảm giá đã mua $122 thì phần trăm của tổng số đó là bao nhiêu phần trăm cho mỗi giao dịch. Hình dưới đây là kết quả cuối cùng mà chúng ta đang cố gắng tính toán. ABC có hai giao dịch, giao dịch đầu tiên là 37,7% và thứ hai là 62,3%.

Trong thuật ngữ Excel, sự chuyển đổi này giống như chia giá trị trong mỗi hàng cho SUMIFS của Customer.

Nhân bản truy vấn

Sao y Truy vấn Data mà chúng ta đã nhập lúc đầu. Điều này là do chúng ta cần (1) một truy vấn dưới dạng một hàm (2) một truy vấn để sử dụng hàm. Để thực hiện việc này, hãy nhấp chuột phải vào query trong danh sách truy vấn và nhấp vào Duplicate.

Đổi tên truy vấn

Như chúng ta đã thấy ở trên, tên mà chúng ta đặt cho truy vấn được sử dụng để gọi hàm.

Tôi đã đặt tên cho truy vấn là fnCustomerTotal. Đặt fn ở đầu đã trở thành thông lệ phổ biến cho các chức năng tùy chỉnh; nó giúp xác định một cách trực quan rằng nó là một chức năng.

Sử chuyển đổi

Xóa tất cả các bước, ngoại trừ bước Source.

Tiếp theo, nhấp vào Transform -> Group By. Chúng ta đã biết cách sử dụng chức năng Group By trong phần trước của loạt bài này. Trong cửa sổ Group By, nhập các cài đặt sau:

• Column: Customer
• New column name: Customer Total
• Operation: Sum
• Column: Value

Nhấp vào OK.

Lọc trên cột Customer để chọn một khách hàng (Giảm giá ABC trong ví dụ này).

Thay đổi truy vấn thành một hàm

Mở trình chỉnh sửa nâng cao:

Đây là phần khó khăn… thực hiện các thay đổi chính xác đối với mã M.

Khai báo các tham số là dòng đầu tiên trong mã. Chúng ta chỉ cần hai tham số (1) bảng để sử dụng và (2) tên khách hàng để lọc.

Vì chúng ta sẽ sử dụng một bảng được khai báo làm nguồn, chúng ta không cần bước Source đã tồn tại trong mã. Xóa dòng sau.

Trong dòng tiếp theo, vẫn có tham chiếu đến bước Source mà chúng ta vừa xóa. Chúng ta cần trỏ dòng đó đến tham số đã khai báo.

Thay đổi:

#”Grouped Rows” = Table.Group(Source, {“Customer”}, {{“Customer Total”, each List.Sum([Value]), type number}}),

Trở thành:

#”Grouped Rows” = Table.Group(sourceTable, {“Customer”}, {{“Customer Total”, each List.Sum([Value]), type number}}),

Bây giờ, hãy thay đổi tên được thay thế Customer thành đối số customerName đã khai báo: Thay đổi điều này:

#”Filtered Rows” = Table.SelectRows(#”Grouped Rows”, each ([Customer] = “ABC Discounts”))

Vào này:

#”Filtered Rows” = Table.SelectRows(#”Grouped Rows”, each ([Customer] = customerName))

Chúng ta có thể sử dụng cái này ở dạng hiện tại của nó. Nếu chúng ta muốn, nó sẽ trả về một Bảng chứa một giá trị duy nhất, thay vì một giá trị riêng lẻ. Nhưng chúng ta có thể thực hiện thêm bước để chỉ trả về giá trị.

Thực hiện các thay đổi sau đối với mã M. Thay đổi:

#”Filtered Rows” = Table.SelectRows(#”Grouped Rows”, each ([Customer] = customerName))

in

    #”Removed Columns”

Vào này:

#”Filtered Rows” = Table.SelectRows(#”Grouped Rows”, each ([Customer] = customerName)),

    CustomerTotal = Record.Field(#”Filtered Rows”{0},”Customer Total”)

in

    CustomerTotal

Record.Field là một hàm Power Query trả về một giá trị đơn lẻ từ một cột:

• #”Filtered Rows”{0} đại diện cho hàng đầu tiên từ bảng vì nó tồn tại sau bước Hàng được Lọc
• “Customer Total” là tên của cột

Cảnh báo: bạn có nhận thấy dấu phẩy bổ sung ở cuối dòng#”Removed Columns” không?  Nó cần phải ở đó, vì vậy hãy thêm nó vào, nếu bạn đã bỏ lỡ nó.

Mã M hoàn chỉnh sẽ như thế này:

(sourceTable as table, customerName as text) =>

let

    #”Grouped Rows” = Table.Group(sourceTable, {“Customer”}, {{“Customer Total”, each List.Sum([Value]), type number}}),

    #”Filtered Rows” = Table.SelectRows(#”Grouped Rows”, each ([Customer] = customerName)),

    CustomerTotal = Record.Field(#”Filtered Rows”{0},”Customer Total”)

in

    CustomerTotal

Nhấp vào Done để đóng cửa sổ Advanced Editor.

Kiểm tra

Hãy kiểm tra hàm. Chọn bảng Data query để sử dụng làm sourceTable và nhập Customer Name. Nhấp vào Invoke.

Nó sẽ trả về giá trị

Thật bất ngờ

Sử dụng hàm

Tất cả những gì còn lại bây giờ là chúng ta sử dụng chức năng tùy chỉnh. Mở truy vấn dữ liệu và thêm một Custom Column. Nhập thông tin sau vào công thức:

• Source là tên của bước (tức là sử dụng bảng như đã tồn tại sau khi bước Sourcre đã hoàn thành)
• [Customer] là cột khách hàng

Bấm OK để chấp nhận chức năng

Cuối cùng, thực hiện các chuyển đổi sau:

• Thay đổi cột mới thành kiểu dữ liệu %
• ắp xếp theo cột Customer
• Xóa cột Sold By

Cửa sổ Xem trước bây giờ sẽ giống như thế này, hiển thị % phân tích cho mỗi giao dịch của khách hàng.

Bước tiếp theo

Nếu bạn muốn đưa điều này lên cấp độ tiếp theo, tại sao không thử thay đổi chức năng để nó hoạt động trên bất kỳ cột nào, thay vì chỉ một cột có tên “Customer”. 

Kết luận

Trong bài đăng này, chúng ta đã biết cách tạo các hàm tùy chỉnh cơ bản và dựa trên chuyển đổi  Phương pháp này có thể được sử dụng bất cứ lúc nào chúng ta cần một giá trị hoặc các giá trị không nằm trong cùng một hàng. Một số ví dụ:

• Tổng số đang chạy
• % tổng thể
• Giá trị ở hàng trước hoặc hàng sau

Thao tác với mã M rất khó và dễ xảy ra sai sót, đặc biệt là khi chúng ta chưa tích lũy kinh nghiệm. Do đó, giống như rất nhiều tính năng khác của Power Query, lời khuyên tốt nhất là hãy thực hành.