3 hàm giúp bạn ngừng lãng phí thời gian trong Excel

Hầu hết mọi người đều nghĩ rằng tốc độ trong Excel đồng nghĩa với việc phải ghi nhớ hàng trăm công thức và hàm. Mọi thứ đã thay đổi, cho đến khi phát hiện ra rằng chỉ cần 3 hàm là có thể thay thế cả đống công thức mà mình từng phải vật lộn.

3. XLOOKUP

Bạn còn nhớ khi VLOOKUP bắt bạn phải đếm các cột chỉ để tìm ra chỉ mục cột không? Hay khi bạn muốn tra cứu dữ liệu ở bên trái cột chính và cuối cùng lại phải sắp xếp lại toàn bộ trang tính? Những rắc rối đó giờ đã không còn nữa.

XLOOKUP là giải pháp hiện đại của Excel để khắc phục mọi sai sót mà VLOOKUP mắc phải. Nó đơn giản và linh hoạt hơn, có sẵn trong Excel 365 và Excel 2021+. Thay vì vật lộn với số cột hoặc giới hạn hướng, bạn chỉ cần cho nó biết 3 điều: Tìm kiếm gì, tìm kiếm ở đâu và trả về kết quả gì.

Đây là cú pháp cơ bản:

=XLOOKUP(lookup_value, lookup_array, return_array)
=XLOOKUP(O2,I2:I200,L2:L200)

Như vậy là đủ để tìm kiếm một giá trị và trả về kết quả khớp. Khi muốn tìm kiếm một giá trị khác, bạn không cần phải thay đổi ô đã chỉ định làm giá trị tra cứu (trong trường hợp ví dụ là O2) trong công thức. Bạn chỉ cần thay đổi giá trị trong ô (trong trường hợp ví dụ là 3604) và kết quả sẽ được cập nhật ngay lập tức. Nếu Excel không tìm thấy kết quả khớp, mặc định nó sẽ hiển thị #N/A.

Nhưng XLOOKUP có thêm các thủ thuật hữu ích hơn nhiều.

2. SUMIFS/COUNTIFS

Nếu vẫn lọc dữ liệu mỗi khi cần tính tổng hoặc đếm nhanh, bạn đang làm việc quá sức. SUMIFS và COUNTIFS là những anh hùng thầm lặng, đặc biệt là đối với báo cáo bán hàng, ngân sách hoặc bất kỳ tập dữ liệu nào cần tính toán có điều kiện.

Hãy tưởng tượng sếp hỏi bạn: "Tổng doanh số của chúng ta tại Châu Á cho các sản phẩm trên 150 USD được đặt hàng trực tuyến là bao nhiêu?" Thay vì tạo ba bộ lọc và hy vọng không làm hỏng bảng tính, bạn có thể trả lời bằng một dòng:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2]...) =SUMIFS(Sales_column,Region_column,"Asia",Price_column,">150",SalesChannel_column,"Online")

Hoặc, trong một tập dữ liệu thực tế:

=SUMIFS(L2:L200,A2:A200,"Asia",J2:J200,">150",D2:D200,"Online")

Công thức này tính tổng tất cả các giá trị trong cột L, trong đó cột A bằng "Asia", cột J lớn hơn 150 và cột D bằng "Online".

Lưu ý: Hãy đảm bảo tiêu chí của bạn được đặt trong dấu ngoặc kép nếu bạn đang kiểm tra các giá trị văn bản.

COUNTIFS hoạt động theo cách tương tự, ngoại trừ việc nó trả về số hàng đáp ứng các điều kiện của bạn:

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]...)
=COUNTIFS(A2:A200,"Asia",J2:J200,">150",D2:D200,"Online")

Thay vì số tiền như trong SUMIFS, bạn sẽ nhận được số lượng đơn hàng từ Châu Á được đặt hàng trực tuyến và có đơn giá trên 150 USD.

SUMIFS và COUNTIFS cũng xử lý các ký tự đại diện. Ví dụ, bạn có thể đếm tất cả các quốc gia bắt đầu bằng chữ "T" và tất cả các mặt hàng ngoại trừ trái cây:

=COUNTIFS(B2:B200,"=A*",C2:C200, "Fruits")

Ký tự đại diện rất tiện dụng, nhưng chúng phụ thuộc vào dữ liệu sạch. Nếu tập dữ liệu của bạn có chữ viết hoa không nhất quán hoặc các ký tự bị ẩn, kết quả có thể bị sai. Việc dọn dẹp nhanh chóng bảng tính Excel lộn xộn của bạn sẽ giúp bạn tránh khỏi những rắc rối sau này.

1. FILTER

Hàm FILTER có thể là hàm thỏa mãn nhất mà Excel đã bổ sung trong nhiều năm qua. Bạn còn nhớ khi lọc dữ liệu đồng nghĩa với việc nhấp qua các menu, đặt điều kiện và hy vọng bạn không vô tình ẩn nhầm hàng không? Với FILTER, tất cả những điều đó được rút gọn thành một công thức duy nhất và cú pháp cơ bản rất đơn giản:

=FILTER(phạm vi cần lọc, tiêu chí lọc)

Giả sử bạn muốn xem doanh thu từ các đơn hàng có giá trên 600 USD:

=FILTER(Revenue_column,UnitsSold_column>600)

Hoặc, trong một tập dữ liệu thực tế:

=FILTER(L2:L200,J2:J200>=600)

Ngay lập tức, bạn sẽ chỉ thấy những số khớp với tiêu chí của mình. Nếu không có giá trị nào thỏa mãn, bạn cũng có thể thêm thông báo dự phòng thay vì nhận lỗi:

=FILTER(L2:L200,J2:J200>=600)

Cũng giống như SUMIFS, bạn không bị giới hạn ở một điều kiện. Bạn có thể sử dụng logic OR (ít nhất một điều kiện phải đúng) hoặc logic AND (tất cả các điều kiện phải đúng). Dưới đây là một số ví dụ đơn giản:

=FILTER(L2:L200,(J2:J200>=600)+(D2:D200="Online"),"No Match")
=FILTER(L2:L200,(J2:J200>=600)*(D2:D200="Online"),"No Match")

Công thức đầu tiên trả về các giá trị có giá trên 600 USD hoặc được bán trực tuyến. Công thức thứ hai chỉ trả về các giá trị trên 600 USD và được bán trực tuyến.

Mẹo: Khi sử dụng nhiều điều kiện, hãy đặt từng điều kiện trong dấu ngoặc đơn. Nếu không, Excel sẽ không biết cách đánh giá chúng một cách chính xác.

FILTER sẽ hiệu quả hơn khi được kết hợp với SORT. Giả sử ô O2 chứa "Meat" và bạn muốn sắp xếp tất cả các hàng khớp theo quốc gia:

=SORT(FILTER(A2:N200,C2:C200=O2,""),2,1)

Điều này cung cấp cho bạn tất cả các bản ghi bán hàng về thịt, được sắp xếp theo thứ tự tăng dần theo một cột được chỉ định (trong trường hợp ví dụ, chọn cột 2, tức là cột quốc gia). Thay đổi số 1 ở cuối công thức thành -1, và kết quả của bạn sẽ xuất hiện theo thứ tự giảm dần.