5 hàm Excel cũ bạn nên ngừng sử dụng

Microsoft Excel chứa rất nhiều hàm, nhưng nếu vẫn dựa vào VLOOKUP, các câu lệnh IF lồng nhau hoặc CONCATENATE, bạn đang khiến bảng tính của mình trở nên khó quản lý hơn mức cần thiết. Microsoft có các hàm Excel mới, mạnh mẽ hơn, giúp bạn cảm thấy như một phù thủy bảng tính và giúp các công thức dễ đọc hơn.

Tin tốt là bạn không cần phải là chuyên gia Excel - chúng được thiết kế để thay thế những hàm cồng kềnh, lỗi thời mà bạn có thể đã quen thuộc. Cho dù bạn đang tra cứu dữ liệu, xây dựng danh sách động hay làm việc với văn bản, Excel đều có những công cụ tốt hơn được tích hợp sẵn giúp bạn tiết kiệm thời gian.

5. Thay thế hàm VLOOKUP hạn chế

XLOOKUP linh hoạt hơn nhiều

VLOOKUP đã là hàm được sử dụng phổ biến để tra cứu dữ liệu trong Excel nhiều năm, nhưng nó cũng có những hạn chế. Bạn chỉ có thể tìm kiếm ở cột ngoài cùng bên trái và trả về giá trị ở cột bên phải. Nếu cột tra cứu của bạn không nằm ở ngoài cùng bên trái, bạn sẽ phải sắp xếp lại toàn bộ bảng hoặc thêm các cột hỗ trợ.

XLOOKUP khắc phục tất cả những điều đó. Nó có thể tìm kiếm ở bất kỳ đâu trong bảng và trả về giá trị từ bất kỳ cột nào - cho dù là bên trái, bên phải hay bất kỳ vị trí nào khác. Thêm vào đó, cú pháp cũng đơn giản và dễ nhớ hơn.

So sánh cú pháp như sau:

VLOOKUP:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])​​

• lookup_value: Giá trị bạn đang tìm kiếm.
• table_array: Phạm vi chứa dữ liệu của bạn.
• col_index_num: Số cột cần trả về (tính từ bên trái).
• range_lookup: TRUE nếu khớp gần đúng, FALSE nếu khớp chính xác.

XLOOKUP:

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

• lookup_value: Giá trị bạn đang tìm kiếm.
• lookup_array: Cột bạn muốn tìm kiếm.
• return_array: Cột trả về kết quả.

Bạn cũng có thể sử dụng các tham số tùy chọn sau:

• if_not_found: Thông báo tùy chỉnh nếu không tìm thấy kết quả khớp.
• match_mode: Khớp chính xác (0) hoặc các loại khớp khác.
• search_mode: Tìm kiếm từ đầu đến cuối hoặc ngược lại.

4. Loại bỏ các câu lệnh IF lồng nhau lộn xộn

Hàm IFS gọn gàng hơn nhiều

Các câu lệnh IF lồng nhau nhanh chóng trở nên mất kiểm soát. Khi kiểm tra nhiều điều kiện, bạn sẽ nhận được một công thức đầy dấu ngoặc đơn và gần như không thể đọc hoặc khắc phục sự cố. Thiếu một dấu ngoặc đơn đóng, toàn bộ công thức sẽ bị hỏng.

Hàm IFS giải quyết vấn đề này bằng cách cho phép bạn kiểm tra nhiều điều kiện trong một công thức duy nhất, đơn giản. Vì vậy, không cần lồng nhau nữa; chỉ cần liệt kê các điều kiện và kết quả tương ứng theo thứ tự.

So sánh cú pháp như sau:

IF lồng nhau:

=IF(condition1, value1, IF(condition2, value2, IF(condition3, value3, value4)))

IFS:

=IFS(condition1, value1, condition2, value2, condition3, value3, TRUE, default_value)

• condition1, condition2, condition3: Kiểm tra logic để đánh giá.
• value1, value2, value3: Kết quả trả về nếu mỗi điều kiện là TRUE.
• TRUE, default_value: Giá trị mặc định tùy chọn khi không có điều kiện nào được đáp ứng.

3. Dừng sử dụng hàm CONCATENATE tẻ nhạt

Chuyển sang hàm TEXTJOIN mạnh mẽ

CONCATENATE buộc bạn phải tham chiếu từng ô riêng lẻ và nhập thủ công các dấu phân cách, chẳng hạn như dấu phẩy hoặc khoảng trắng, giữa mỗi ô. Nếu bạn đang kết hợp 5 ô, đó là 5 tham chiếu ô cộng với 4 dấu phân cách - tất cả đều được nhập riêng biệt.

TEXTJOIN xử lý việc này chỉ trong một lần. Bạn chỉ định dấu phân cách một lần, cho phép nó bỏ qua các ô trống, rồi chọn toàn bộ phạm vi. Việc viết nhanh chóng và dễ dàng chỉnh sửa hơn nhiều sau này.

Sau đây là so sánh cú pháp:

CONCATENATE:

=CONCATENATE(text1, " ", text2, " ", text3)

TEXTJOIN:

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

• delimiter: Ký tự hoặc văn bản cần chèn giữa mỗi giá trị (dấu phẩy, dấu cách, dấu gạch nối, v.v.).
• ignore_empty: TRUE để bỏ qua các ô trống, FALSE để bao gồm chúng.
• text1, [text2], ...: Các ô hoặc phạm vi bạn muốn kết hợp.

2. Quên việc tạo danh sách thủ công

Sử dụng các mảng động như FILTER và UNIQUE

Tạo danh sách được lọc thủ công có nghĩa là sao chép từng hàng một hoặc sử dụng AutoFilter, sau đó sao chép và dán kết quả vào một vị trí khác. Nếu dữ liệu nguồn thay đổi, bạn phải lặp lại toàn bộ quá trình. Việc này tốn thời gian và dễ xảy ra lỗi.

Các hàm mảng động như FILTER và UNIQUE tự động tạo danh sách và cập nhật theo thời gian thực khi dữ liệu nguồn của bạn thay đổi. Bạn chỉ cần viết công thức một lần, Excel sẽ xử lý phần còn lại. Hiện tại, nhiều người sử dụng hàm FILTER của Excel cho mọi thứ, vì không cần phải làm mới hoặc sao chép và dán thủ công.

Cú pháp cho cả 2 hàm:

FILTER:

=FILTER(array, include, [if_empty])

• array: Phạm vi dữ liệu bạn muốn lọc.
• include: Điều kiện xác định những hàng nào sẽ được bao gồm.
• if_empty: Thông báo tùy chọn hiển thị nếu không có kết quả nào khớp với điều kiện.

UNIQUE:

=UNIQUE(array, [by_col], [exactly_once])

• array: Phạm vi bạn muốn trích xuất các giá trị duy nhất.
• by_col: FALSE để so sánh các hàng (mặc định), TRUE để so sánh các cột.
• exactly_once: FALSE trả về tất cả các giá trị duy nhất, TRUE trả về các giá trị chỉ xuất hiện một lần.

1. Hoán đổi LEFT, RIGHT và MID

Vì TEXTSPLIT, TEXTBEFORE và TEXTAFTER trực quan hơn

LEFT, RIGHT và MID yêu cầu bạn phải đếm chính xác số ký tự bạn muốn trích xuất. Nếu định dạng văn bản của bạn thay đổi hoặc độ dài của nó thay đổi, bạn sẽ liên tục điều chỉnh số lượng ký tự và chỉ cần một phép tính sai sẽ phá vỡ công thức.

Các hàm văn bản mới hơn - TEXTSPLIT, TEXTBEFORE và TEXTAFTER - cho phép bạn trích xuất văn bản dựa trên dấu phân cách hoặc dấu hiệu cụ thể thay vì vị trí ký tự. Chúng nằm trong số các hàm Excel được sử dụng nhiều nhất vì tính linh hoạt và dễ hiểu khi nhìn thoáng qua.

Đây là cú pháp của từng hàm:

TEXTSPLIT:

=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])

• text: Chuỗi văn bản bạn muốn tách.
• col_delimiter: Ký tự phân tách các cột (dấu phẩy, dấu cách, dấu gạch nối, v.v...).
• row_delimiter: Ký tự tùy chọn phân tách các hàng.
• ign_empty: TRUE để bỏ qua các giá trị trống, FALSE để bao gồm chúng.
• match_mode: 0 cho phân biệt chữ hoa chữ thường, 1 cho không phân biệt chữ hoa chữ thường.
• pad_with: Giá trị sử dụng cho các ô trống trong kết quả.

TEXTBEFORE:

=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])

• text: Chuỗi văn bản cần tìm kiếm.
• delimiter: Ký tự hoặc văn bản đánh dấu vị trí dừng trích xuất.
• instance_num: Lần xuất hiện của dấu phân cách cần sử dụng (1 cho lần đầu tiên, 2 cho lần thứ hai, v.v...).
• match_mode: 0 cho phân biệt chữ hoa chữ thường, 1 cho không phân biệt chữ hoa chữ thường.
• match_end: 0 để tìm kiếm từ đầu, 1 để tìm kiếm từ cuối.
• if_not_found: Giá trị trả về nếu không tìm thấy dấu phân cách.

TEXTAFTER:

=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])

Các tham số giống như TEXTBEFORE, nhưng nó trích xuất văn bản sau dấu phân cách thay vì trước dấu phân cách.