Các hàm Excel 365
share
tweet
Có thể nói Excel 365 đã tạo ra một cuộc cách mạng hóa về cách chúng ta quản lý và phân tích dữ liệu. Với các tính năng: tạo mảng động, các hàm chức năng mới (LAMBDA, LET, SCAN/REDUCE, BYROW, BYCOL, FILTER...) và khả năng tạo các hàm tùy chỉnh mạnh mẽ mà không cần có kiến thức chuyên sâu về lập trình (như VBA - Visual basic for application) là một bước ngoặt lớn. Trong số các tính năng nâng cao này, thì việc xuất hiện hàm LAMBDA là hết sức đặc biệt. Nó cho phép tạo ra các hàm phức tạp, có thể tái sử dụng trực tiếp trong môi trường công thức Excel, mở rộng đáng kể khả năng của Excel.
Trong các lĩnh vực như quản lý chuỗi cung ứng và quản lý tài chính, các mục nhập dữ liệu trùng lặp có thể dẫn đến các lỗi nghiêm trọng. Cụ thể, khi một mã hàng hóa tương ứng với nhiều mô tả hàng hóa hoặc ngược lại, sự mơ hồ dữ liệu (do trùng lắp) này có thể gây ra những hiểu lầm về hoạt động và tài chính.
Để giải quyết vấn đề này, hàm LAMBDA của Excel 365 là một giải pháp hữu hiêu, kết hợp với một loạt các hàm khác được gói trong một NAME RANGE (vùng được đặt tên). Cách tiếp cận này tạo ra một hàm tùy chỉnh hiệu quả trong việc xác định và lọc ra những trùng lắp từ hai danh sách: mã hàng hóa và mô tả hàng hóa.
Hàm "Filter_duplicate_item" (tên hàm do tác giả tư đặt) đã thể hiện sức mạnh của Excel 365.
Hãy cùng phân tích các thành phần của nó để hiểu cách nó hoạt động:
=LAMBDA(r_1, r_2,LET(A, FILTER(UNIQUE(r_1), BYROW(UNIQUE(r_1), LAMBDA(k, ROWS(UNIQUE(FILTER(r_2, r_1 = k))))) >= 2),B, REDUCE("", A, LAMBDA(m, n, IF(n = "", m, UNIQUE(VSTACK(m, FILTER(HSTACK(r_1, r_2), r_1 = n)))))),FILTER(B, CHOOSECOLS(B, 1) <> "")))
1. **LAMBDA(r_1, r_2,...**: LAMBDA nhận hai vùng dữ liệu, r_1 (mã hàng hóa) và r_2 (mô tả hàng hóa), làm đầu vào.
2. **LET(...**: Hàm LET cho phép đặt tên cho các tính toán trung gian. Nó đơn giản hóa các công thức phức tạp và có thể làm cho chúng hiệu quả hơn.
3. **A, FILTER(UNIQUE(r_1),...**: Biến 'A' là một mảng của các mã hàng hóa duy nhất từ r_1 với hai hoặc nhiều mô tả duy nhất tương ứng trong r_2.
4. **BYROW(UNIQUE(r_1), LAMBDA(k,...**: BYROW áp dụng một hàm LAMBDA cho mỗi hàng, lần lượt lọc r_2 để đếm các sự xuất hiện duy nhất liên kết với mỗi mã hàng hóa.
5. **B, REDUCE("", A, LAMBDA(m, n,...**: 'B' dần dần xây dựng một mảng của các mã hàng hóa trùng lắp và mô tả của chúng bằng cách sử dụng hàm REDUCE với LAMBDA trên mảng 'A'.
6. **UNIQUE(VSTACK(...**: UNIQUE và VSTACK hoạt động cùng nhau trong hàm REDUCE để tạo ra một danh sách duy nhất của mã hàng hóa và mô tả của nó.
7. **FILTER(B, CHOOSECOLS(B, 1) <> ""...**: Cuối cùng, hàm FILTER này loại bỏ bất kỳ hàng trống nào không chứa mã hàng hóa, đảm bảo sẽ thu được một mảng cuối cùng đạt tiêu chuẩn (không bị thừa dòng trống).
- UNIQUE: Trả về một danh sách các giá trị duy nhất từ một phạm vi đã chỉ định
- FILTER: Lọc một mảng dựa trên một mảng Boolean (đúng/sai).
- BYROW: Áp dụng một hàm lambda cho mỗi hàng trong một mảng đã cho.
- ROWS: Đếm số hàng trong một mảng.
- LET: Gán tên cho kết quả tính toán, cho phép tham chiếu dễ dàng hơn và có thể cải thiện hiệu suất.
- REDUCE: Giảm một mảng thành một giá trị đơn bằng cách áp dụng một hàm lambda cho mỗi phần tử lần lượt.
- VSTACK: Xếp chồng các mảng theo chiều dọc.
- HSTACK: Xếp chồng các mảng theo chiều ngang.
- CHOOSECOLS: Chọn các cột cụ thể từ một mảng.
Để triển khai hàm tùy chỉnh này trong Excel, hãy làm theo các bước sau:
1. **Định Nghĩa Các Phạm Vi Của Bạn**: Đặt tên cho phạm vi mã mục hàng và mô tả mục hàng của bạn lần lượt là 'ItemCodes' và 'ItemDescriptions'.
2. **Tạo Hàm LAMBDA**: Truy cập vào Trình quản lý tên từ tab Công thức, tạo một tên mới 'Filter_duplicate_item', và dán hàm LAMBDA vào trường 'Refers to'.
3. **Sử Dụng Hàm**: Sử dụng `=Filter_duplicate_item(ItemCodes, ItemDescriptions)` trong sổ làm việc của bạn để gọi hàm.
Vượt trội hơn VBA - Sự Đơn Giản Của Excel Hiện Đại
Mặc dù VBA vẫn là một công cụ mạnh mẽ để tạo User-Defined Functions (UDFs), tuy nhiên điều quan trọng là cần lưu ý các chức năng hiện đại được cung cấp bởi công thức của Excel 365 đã mang lại một lựa chọn đơn giản và trực quan hơn rất nhiều. Với bản cập nhật mới nhất, việc tạo UDFs trở nên dễ dàng hơn, cho phép bạn tối ưu hóa quy trình làm việc mà không cần đào sâu vào kỹ năng lập trình phức tạp.
Bằng cách xem xét từng phần của hàm và hiểu cách chúng được xây dựng, người dùng không chỉ giải quyết vấn đề cụ thể mà còn có cái nhìn sâu sắc vào việc xây dựng các hàm của riêng họ. Việc đi tìm hiểu chuyên sâu hàm 'Filter_duplicate_item' là một ví dụ điển hình về cách sử dụng các tính năng nâng cao của Excel 365 để thực hiện phân tích dữ liệu chuyên sâu, nâng cao tính chính xác của dữ liệu và tối ưu hóa quá trình ra quyết định. Như vậy, Excel 365 đã trao quyền cho người dùng công cụ và kiến thức để giải quyết các thách thức từ sự phức tạp của dữ liệu một cách hiệu quả và nhanh chóng.
Để tận dụng tối đa các khả năng như hàm 'Filter_duplicate_item' và các chức năng khác như đã thảo luận ở trên, việc cập nhật Excel 365 của bạn lên phiên bản mới nhất là cần thiết. Vì nó sẽ đảm bảo tính tương thích và chức năng, cho phép bạn khai thác sức mạnh của các hàm như LAMBDA, LET, UNIQUE, FILTER, BYROW, REDUCE, VSTACK, và CHOOSECOLS.
