Nội dung
Hiện tại, Microsoft Excel có gần năm trăm hàm trang tính khả dụng thông qua cửa sổ Trình hướng dẫn hàm - nút fx trong thanh công thức. Đây là một tập hợp rất tốt, tuy nhiên, hầu hết mọi người dùng sớm hay muộn đều gặp phải tình huống trong đó danh sách này không chứa hàm họ cần - đơn giản vì nó không có trong Excel.
Cho đến nay, cách duy nhất để giải quyết vấn đề này là macro, tức là viết hàm do người dùng xác định của riêng bạn (UDF = User Defined Function) trong Visual Basic, điều này đòi hỏi kỹ năng lập trình phù hợp và đôi khi không dễ dàng chút nào. Tuy nhiên, với các bản cập nhật Office 365 mới nhất, tình hình đã thay đổi theo chiều hướng tốt hơn - một chức năng “wrapper” đặc biệt đã được thêm vào Excel LAMBDA. Với sự trợ giúp của nó, nhiệm vụ tạo các chức năng của riêng bạn giờ đây đã được giải quyết một cách dễ dàng và đẹp mắt.
Hãy xem xét nguyên tắc sử dụng của nó trong ví dụ sau.
Như bạn rất có thể đã biết, Excel có một số hàm phân tích cú pháp ngày cho phép bạn xác định số ngày, tháng, tuần và năm cho một ngày nhất định. Nhưng không hiểu sao lại không có chức năng xác định số tứ quý, cũng thường cần đến đúng không? Hãy sửa chữa khuyết điểm này và tạo với LAMBDA riêng chức năng mới để giải quyết vấn đề này.
Bước 1. Viết công thức
Hãy bắt đầu với thực tế là theo cách thủ công, chúng ta sẽ viết một công thức trong một ô trang tính để tính toán những gì chúng ta cần theo cách thủ công. Trong trường hợp số quý, điều này có thể được thực hiện, chẳng hạn như sau:
Bước 2. Kết thúc trong LAMBDA và thử nghiệm
Bây giờ đã đến lúc áp dụng hàm LAMBDA mới và đưa công thức của chúng ta vào đó. Cú pháp hàm như sau:
= LAMBDA (Biến1; Biến2; Giáo dục BiếnN ; Biểu hiện)
trong đó tên của một hoặc nhiều biến được liệt kê đầu tiên và đối số cuối cùng luôn là công thức hoặc biểu thức được tính toán sử dụng chúng. Tên biến không được giống địa chỉ ô và không được chứa dấu chấm.
Trong trường hợp của chúng tôi, sẽ chỉ có một biến - ngày mà chúng tôi tính số quý. Hãy gọi biến cho nó, giả sử, d. Sau đó, gói công thức của chúng ta trong một hàm LAMBDA và thay thế địa chỉ của ô A2 ban đầu bằng một tên biến giả tưởng, chúng ta nhận được:
Xin lưu ý rằng sau khi chuyển đổi như vậy, công thức của chúng tôi (trên thực tế, chính xác!) Bắt đầu tạo ra lỗi, vì bây giờ ngày ban đầu từ ô A2 không được chuyển sang nó. Để kiểm tra và độ tin cậy, bạn có thể chuyển các đối số cho nó bằng cách thêm chúng vào sau hàm LAMBDA trong dấu ngoặc đơn:
Bước 3. Tạo tên
Bây giờ cho một phần dễ dàng và thú vị. Chúng tôi mở Người quản lý tên chuyển hướng công thức (Công thức - Trình quản lý tên) và tạo tên mới bằng nút Tạo (Tạo ra). Nghĩ ra và nhập tên cho chức năng trong tương lai của chúng ta (ví dụ: Nomkvartala), và trong lĩnh vực này liên kết (Tài liệu tham khảo) sao chép cẩn thận từ thanh công thức và dán hàm của chúng tôi LAMBDA, chỉ không có đối số cuối cùng (A2):
Mọi điều. Sau khi nhấp vào OK hàm đã tạo có thể được sử dụng trong bất kỳ ô nào trên bất kỳ trang tính nào của sổ làm việc này:
Sử dụng trong các sách khác
LAMBDA và mảng động
Các chức năng tùy chỉnh được tạo bằng một chức năng LAMBDA hỗ trợ thành công công việc với các mảng động mới và các chức năng của chúng (LỌC, ĐỘC NHẤT, GRADE) được thêm vào Microsoft Excel vào năm 2020.
Giả sử chúng tôi muốn tạo một hàm mới do người dùng xác định sẽ so sánh hai danh sách và trả về sự khác biệt giữa chúng - những phần tử từ danh sách đầu tiên không có trong danh sách thứ hai. Cuộc sống là công việc, phải không? Trước đây, để làm điều này, họ đã sử dụng một trong hai hàm a la VPR (Vlookup), hoặc PivotTables, hoặc truy vấn Power Query. Bây giờ bạn có thể làm với một công thức:
Trong phiên bản tiếng Anh, nó sẽ là:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Đây là chức năng COUNTIF đếm số lần xuất hiện của từng phần tử của danh sách đầu tiên trong danh sách thứ hai, sau đó là hàm LỌC chỉ chọn những người trong số họ không có những lần xuất hiện này. Bằng cách gói cấu trúc này trong LAMBDA và tạo một dải ô đã đặt tên dựa trên nó với một tên, ví dụ: TÌM KIẾM PHÂN PHỐI - chúng ta sẽ nhận được một hàm thuận tiện trả về kết quả so sánh hai danh sách dưới dạng một mảng động:
Nếu dữ liệu nguồn không phải là thông thường, mà là các bảng “thông minh”, thì chức năng của chúng ta cũng sẽ xử lý được mà không gặp vấn đề gì:
Một ví dụ khác là phân tách động văn bản bằng cách chuyển nó sang XML và sau đó phân tích cú pháp từng ô bằng cách sử dụng hàm FILTER.XML mà chúng tôi đã phân tích cú pháp gần đây. Để không phải tạo lại công thức phức tạp này theo cách thủ công mỗi lần, sẽ dễ dàng hơn khi gói nó trong LAMBDA và tạo một dải động dựa trên nó, tức là một hàm mới nhỏ gọn và thuận tiện, đặt tên cho nó, chẳng hạn như RAZDTEXT:
Đối số đầu tiên của hàm này sẽ là ô chứa văn bản nguồn và đối số thứ hai - ký tự phân tách và nó sẽ trả về kết quả dưới dạng một mảng động ngang. Mã chức năng sẽ như sau:
= LAMBDA (t;d; TRANSPOSE (FILTER.XML (“
Danh sách các ví dụ là vô tận - trong bất kỳ tình huống nào bạn thường phải nhập cùng một công thức dài và rườm rà, hàm LAMBDA sẽ giúp cuộc sống dễ dàng hơn đáng kể.
Liệt kê đệ quy các ký tự
Tất cả các ví dụ trước đây chỉ cho thấy một mặt rõ ràng nhất của hàm LAMBDA - việc sử dụng nó như một “trình bao bọc” để gói các công thức dài trong đó và đơn giản hóa việc nhập chúng. Trên thực tế, LAMBDA có một khía cạnh khác, sâu hơn nhiều, biến nó thành một ngôn ngữ lập trình chính thức.
Thực tế là một tính năng cơ bản quan trọng của các hàm LAMBDA là khả năng triển khai chúng trong đệ quy - logic của các phép tính, khi trong quá trình tính toán, hàm gọi chính nó. Từ thói quen, nghe có vẻ rùng rợn, nhưng trong lập trình, đệ quy là một điều phổ biến. Ngay cả trong macro trong Visual Basic, bạn có thể triển khai nó và bây giờ, như bạn có thể thấy, nó đã đến với Excel. Hãy thử tìm hiểu kỹ thuật này bằng một ví dụ thực tế.
Giả sử chúng ta muốn tạo một hàm do người dùng định nghĩa sẽ xóa tất cả các ký tự đã cho khỏi văn bản nguồn. Tôi nghĩ rằng tính hữu dụng của một chức năng như vậy thì bạn không cần phải chứng minh - sẽ rất thuận tiện để xóa dữ liệu đầu vào bừa bãi với sự trợ giúp của nó, phải không?
Tuy nhiên, so với các ví dụ trước đó, không đệ quy, có hai khó khăn đang chờ đợi chúng ta.
- Chúng ta sẽ phải nghĩ ra một tên cho hàm của mình trước khi bắt đầu viết mã của nó, bởi vì trong đó, tên này đã được sử dụng để gọi chính hàm.
- Nhập một hàm đệ quy như vậy vào một ô và gỡ lỗi nó bằng cách chỉ định các đối số trong dấu ngoặc sau LAMBDA (như chúng ta đã làm trước đó) sẽ không hoạt động. Bạn sẽ phải tạo một hàm ngay lập tức "từ đầu" trong Người quản lý tên (Người quản lý tên).
Hãy gọi hàm của chúng ta, chẳng hạn, CLEAN và chúng ta muốn nó có hai đối số - văn bản được làm sạch và danh sách các ký tự bị loại trừ dưới dạng chuỗi văn bản:
Hãy tạo, như chúng ta đã làm trước đó, trên tab công thức в Người quản lý tên phạm vi đã đặt tên, đặt tên là XÓA và nhập vào trường Phạm vi xây dựng sau:
= LAMBDA (t; d; IF (d = ””; t; CLEAR (SUBSTITUTE (t; LEFT (d); ””); MID (d; 2; 255))))
Ở đây biến t là văn bản gốc cần xóa, và d là danh sách các ký tự cần xóa.
Tất cả đều hoạt động như thế này:
lặp 1
Phân đoạn SUBSTITUTE (t; LEFT (d); ””), như bạn có thể đoán, thay thế ký tự đầu tiên từ ký tự bên trái từ tập hợp d sẽ bị xóa trong văn bản nguồn t bằng một chuỗi văn bản trống, tức là xóa ký tự “ MỘT". Theo kết quả trung gian, chúng tôi nhận được:
Vsh zkz n 125 rúp.
lặp 2
Sau đó, hàm tự gọi và với tư cách là đầu vào (đối số đầu tiên) nhận những gì còn lại sau khi làm sạch ở bước trước và đối số thứ hai là chuỗi ký tự bị loại trừ bắt đầu không phải từ ký tự đầu tiên mà từ ký tự thứ hai, tức là “BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA. , ”Không có chữ“ A ”đầu tiên - điều này được thực hiện bởi hàm MID. Như trước đây, hàm lấy ký tự đầu tiên từ bên trái của các ký tự còn lại (B) và thay thế nó trong văn bản được cung cấp cho nó (Zkz n 125 rúp) bằng một chuỗi trống - chúng tôi nhận được kết quả trung gian:
125 ru.
lặp 3
Hàm tự gọi lại, nhận làm đối số đầu tiên những gì còn lại của văn bản sẽ được xóa ở lần lặp trước (Bsh zkz n 125 ru.), Và như đối số thứ hai, tập hợp các ký tự bị loại trừ được cắt bớt bởi một ký tự nữa để bên trái, tức là “VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.,” không có chữ cái đầu “B”. Sau đó, nó lại lấy ký tự đầu tiên bên trái (B) từ tập hợp này và xóa nó khỏi văn bản - chúng ta nhận được:
sh zkz n 125 ru.
Và như vậy - tôi hy vọng bạn có được ý tưởng. Với mỗi lần lặp, danh sách các ký tự bị xóa sẽ bị cắt bớt ở bên trái, và chúng tôi sẽ tìm kiếm và thay thế ký tự tiếp theo từ tập hợp bằng một khoảng trống.
Khi tất cả các ký tự hết, chúng ta sẽ cần thoát khỏi vòng lặp - vai trò này chỉ được thực hiện bởi hàm IF (NẾU), trong đó thiết kế của chúng tôi được bao bọc. Nếu không còn ký tự nào để xóa (d = ””), thì hàm sẽ không còn gọi chính nó nữa, mà chỉ cần trả về văn bản cần xóa (biến t) ở dạng cuối cùng của nó.
Lặp lại đệ quy của các ô
Tương tự, bạn có thể thực hiện một cách liệt kê đệ quy các ô trong một phạm vi nhất định. Giả sử chúng ta muốn tạo một hàm lambda có tên DANH SÁCH THAY THẾ để thay thế bán buôn các đoạn trong văn bản nguồn theo danh sách tham chiếu nhất định. Kết quả sẽ như thế này:
Những thứ kia. tại chức năng của chúng tôi DANH SÁCH THAY THẾ sẽ có ba đối số:
- ô có văn bản để xử lý (địa chỉ nguồn)
- ô đầu tiên của cột có các giá trị để tìm kiếm từ tra cứu
- ô đầu tiên của cột có các giá trị thay thế từ tra cứu
Hàm nên đi từ trên xuống dưới trong thư mục và thay thế tuần tự tất cả các tùy chọn từ cột bên trái Để tìm đến các tùy chọn tương ứng từ cột bên phải Thay thế. Bạn có thể thực hiện điều này với hàm lambda đệ quy sau:
Chuyển xuống ở mỗi lần lặp được thực hiện bởi một hàm excel tiêu chuẩn TRANH CHẤP (BÙ LẠI), trong trường hợp này có ba đối số - phạm vi gốc, dịch chuyển hàng (1) và dịch chuyển cột (0).
Chà, ngay sau khi chúng ta đến cuối thư mục (n = “”), chúng ta phải kết thúc đệ quy - chúng ta ngừng gọi chính mình và hiển thị những gì đã tích lũy sau tất cả các lần thay thế trong biến văn bản nguồn t.
Đó là tất cả. Không có macro hoặc truy vấn Power Query phức tạp - toàn bộ nhiệm vụ được giải quyết bởi một chức năng.
- Cách sử dụng các hàm mảng động mới của Excel: FILTER, SORT, UNIC
- Thay thế và xóa văn bản bằng chức năng SUBSTITUTE
- Tạo macro và các chức năng do người dùng xác định (UDF) trong VBA