3 hàm Excel giúp xây dựng công thức tra cứu cực nhanh

Các công thức tra cứu chậm có thể làm giảm năng suất khi làm việc với những tập dữ liệu lớn. Để giải quyết vấn đề này, bài viết đã thử nghiệm các hàm tra cứu tối ưu của Microsoft Excel và cách kết hợp chúng để xây dựng những công thức cực nhanh, có thể xử lý dễ dàng ngay cả các bảng tính lớn.

Chúng tốt hơn nhiều so với VLOOKUP

Mọi người đã sử dụng VLOOKUP trong một thời gian dài, nhưng nó có những hạn chế với dữ liệu phức tạp. Mặt khác, XLOOKUP loại bỏ hầu hết các vấn đề này đồng thời mang lại hiệu suất và tính linh hoạt tốt hơn.

Khi sử dụng XLOOKUP, bạn chọn các cột tra cứu và trả về riêng biệt, nghĩa là cuối cùng bạn có thể nhìn sang bên trái của cột tra cứu mà không cần sắp xếp lại dữ liệu. Đó là một trong những lý do tại sao XLOOKUP tốt hơn VLOOKUP.

XLOOKUP có cú pháp sau:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Hãy cùng phân tích các tham số:

• lookup_value: Giá trị bạn đang tìm kiếm.
• lookup_array: Phạm vi hoặc mảng mà bạn sẽ tìm kiếm.
• return_array: Chứa các giá trị bạn muốn truy xuất.

Các tham số tùy chọn cho phép bạn kiểm soát tốt hơn:

• [if_not_found]: Cho phép bạn chỉ định thông báo lỗi tùy chỉnh thay vì hiển thị các lỗi Excel phổ biến như #N/A và loại bỏ nhu cầu sử dụng IFERROR.
• [match_mode]: Cung cấp kết quả khớp chính xác, ký tự đại diện hoặc kết quả khớp gần đúng.
• [search_mode]: Xác định Excel sẽ tìm kiếm từ tên đến họ hay từ họ đến tên.

Hãy xem một ví dụ liên quan đến văn phòng. Nếu có địa chỉ email của một nhân viên và cần tìm tên đầy đủ của họ, bạn sẽ phải nhìn sang bên trái. VLOOKUP không thể thực hiện việc này một cách dễ dàng vì cột "Full Name" nằm bên trái cột "AD Email". Nhưng bạn có thể thực hiện việc này với XLOOKUP.

Ví dụ, nếu có địa chỉ email uriah.bridges@bilearner.com trong ô J2, bạn có thể sử dụng công thức này để tìm tên đầy đủ:

=XLOOKUP(J2, J:J, D:D, "Employee Not Found")

Ở đây, công thức sẽ tìm kiếm email trong cột J và trả về tên tương ứng từ cột D. Nếu không tìm thấy email, công thức sẽ trả về kết quả "Employee Not Found" một cách rõ ràng thay vì lỗi #N/A khó chịu. Tính năng xử lý lỗi tích hợp này giúp công thức mạnh mẽ và thân thiện hơn với người dùng.

3. Kết hợp LET và XLOOKUP

Loại bỏ các phép tính lặp lại

Các công thức đôi khi có thể trở nên dài dòng và phức tạp, đặc biệt là khi bạn cần thực hiện cùng một phép tính nhiều lần trong một công thức. Điều này không chỉ khiến công thức khó đọc mà còn làm giảm hiệu suất, vì Excel phải tính toán cùng một kết quả nhiều lần.

Giải pháp cho vấn đề này là hàm LET, cho phép bạn khai báo các biến trực tiếp bên trong công thức. Bạn tính toán một lần, đặt tên cho nó, và sau đó chỉ cần sử dụng tên đó bất cứ khi nào bạn cần kết quả. Cú pháp rất đơn giản:

=LET(name1, name_value1, [name2, name_value2], calculation)

• name1: Tên cho phép tính đầu tiên của bạn (ví dụ: "rating").
• name_value1: Bản thân phép tính (ví dụ: công thức XLOOKUP).
• calculation: Công thức cuối cùng sử dụng tên bạn vừa định nghĩa.

Giả sử bạn có một nhiệm vụ yêu cầu gán trạng thái dựa trên xếp hạng hiện tại của một nhân viên. Nếu xếp hạng của họ (được tìm thấy bằng ID nhân viên) là năm, họ sẽ được xếp vào nhóm "Top Tier". Nếu xếp hạng là một, họ sẽ được xếp vào nhóm "On Watchlist". Ngược lại, họ sẽ được xếp vào nhóm "Standard".

Nếu không có LET, bạn sẽ phải viết hàm XLOOKUP 2 lần, như sau:

=IF(XLOOKUP(A2, A:A, AC:AC)=5, "Top Tier", IF(XLOOKUP(A2, A:A, AC:AC)=1, "On Watchlist", "Standard"))

Nhưng khi sử dụng LET, công thức trở nên gọn gàng hơn nhiều. Bạn chỉ phải tính toán XLOOKUP một lần và gán nó cho tên "rating".

=LET(rating, XLOOKUP(A2, A:A, AC:AC), IF(rating=5, "Top Tier", IF(rating=1, "On Watchlist", "Standard")))

Công thức này tra cứu ID nhân viên từ ô A2 trong cột A, lấy xếp hạng số của họ từ cột AC, sau đó phân loại hiệu suất.

Hàm LET lưu trữ tra cứu xếp hạng một lần, ngăn Excel lặp lại cùng một phép tính XLOOKUP trong các câu lệnh IF lồng nhau. Công thức này dễ đọc và gỡ lỗi hơn đáng kể.

2. INDEX và MATCH hoạt động tuyệt vời cùng nhau

Chúng tạo ra các kết hợp tra cứu nhanh nhất

Trước khi XLOOKUP ra đời, INDEX và MATCH là lựa chọn duy nhất để xây dựng các tra cứu nhanh. Ngay cả bây giờ, sự kết hợp này thường vượt trội hơn các hàm mới hơn về tốc độ. Trên các bảng tính có hàng chục nghìn hàng, sự kết hợp này nhanh hơn đáng kể vì nó chỉ xử lý những cột cụ thể mà bạn tham chiếu, chứ không phải toàn bộ bảng.

Không hàm nào tự thực hiện tra cứu hoàn chỉnh. Hàm MATCH có một điểm mạnh: Tìm vị trí tương đối (số hàng) của một giá trị trong một cột. Sau đó, hàm INDEX lấy số đó và lấy giá trị tương ứng từ một cột khác.

Trước tiên, hãy xem cú pháp MATCH. Nó cho bạn biết dữ liệu của mình nằm ở đâu.

=MATCH(lookup_value, lookup_array, [match_type])

• lookup_value: Giá trị bạn muốn tìm.
• lookup_array: Cột hoặc hàng đơn lẻ cần tìm kiếm.
• [match_type]: Sử dụng 0 để tìm kiếm chính xác, đây là giá trị bạn sẽ cần trong 99% trường hợp.

Tiếp theo là cú pháp INDEX, cú pháp này sẽ truy xuất kết quả bạn muốn dựa trên vị trí mà hàm MATCH cung cấp.

=INDEX(array, row_num, [column_num])

• array: Phạm vi ô hoặc mảng để truy xuất giá trị.
• row_num: Số hàng để truy xuất giá trị.

Giả sử bạn cần tìm vùng lương cho nhân viên có ID 3.427. Hãy sử dụng công thức sau:

=INDEX(F:F, MATCH(A2, A:A, 0))

Trong ví dụ này, phần MATCH trước tiên sẽ tìm số hàng của ID nhân viên nằm trong ô A2. Sau đó, hàm INDEX sẽ lấy số đó và trả về giá trị từ chính hàng đó trong cột vùng lương (cột F). Đây là một phương pháp gọn nhẹ và hiệu quả.

1. FILTER là hàm tra cứu tối ưu

Thay thế nhiều tra cứu cùng lúc

Các tra cứu tiêu chuẩn, kể cả XLOOKUP, đều có một hạn chế cơ bản - chúng dừng lại và trả về kết quả khớp đầu tiên tìm thấy. Nhưng nếu bạn cần danh sách tất cả các bản ghi khớp thì sao? Trong các phiên bản Excel trước năm 2021, điều này yêu cầu các công thức mảng phức tạp. Giờ đây, hãy sử dụng hàm FILTER thay thế. Nó trích xuất mọi bản ghi đáp ứng tiêu chí của bạn.

Nếu chưa bao giờ sử dụng hàm FILTER của Excel, bạn thực sự đang bỏ lỡ một điều tuyệt vời. Nó trả về một mảng các giá trị tự động tràn vào những ô bên dưới công thức. Điều này có nghĩa là bạn không còn cần phải đoán xem mình sẽ nhận được bao nhiêu kết quả nữa. Hàm sẽ tự động xử lý kích thước đầu ra cho bạn, điều này rõ ràng là tiết kiệm thời gian.

Sau đây là cú pháp của hàm FILTER:

=FILTER(array, include, [if_empty])

• array: Đây là toàn bộ phạm vi dữ liệu bạn muốn có kết quả. Đây có thể là một cột hoặc nhiều cột.
• include: Đây là phép thử logic của bạn. Đó là một dãy ô theo sau bởi một điều kiện, chẳng hạn như kiểm tra xem giá trị trong một cột có bằng "Active" hay không.
• [if_empty]: Một đối số tùy chọn cho nội dung hiển thị nếu không tìm thấy kết quả.

Giả sử một người quản lý, Peter O'Neill, muốn có danh sách tất cả nhân viên trực thuộc. Bạn cần lấy họ tên đầy đủ, ngày bắt đầu và mức lương của mỗi nhân viên có tên trong danh sách giám sát. Với FILTER, bạn có thể lấy toàn bộ danh sách này chỉ bằng một công thức thay vì phải thực hiện nhiều lần tra cứu riêng lẻ.

Đây là công thức hoàn thành công việc:

=FILTER(D:F, I:I="Peter Oneill", "No Employees Found")

Công thức này yêu cầu Excel trả về dữ liệu từ các cột D đến F cho mỗi hàng có giá trị trong cột I là "Peter O'Neill".

Kết quả sẽ tự động điền vào các ô bên dưới, tạo ra một bảng động, gọn gàng. Nhờ tính năng động này, thủ thuật Excel này sẽ chấm dứt hoàn toàn nỗi phiền toái khi phải thay đổi kích thước bảng.

Những phương pháp này bao quát mọi tình huống tra cứu mà bạn gặp phải. XLOOKUP là sự thay thế hiện đại cho VLOOKUP. Hãy sử dụng các kết hợp LET để tối ưu hóa, INDEX/MATCH để tăng tốc độ, và FILTER để tìm kiếm nhiều kết quả.