Hàm lấy dữ liệu có điều kiện trong Excel là gì? Cách sử dụng chi tiết

Việc xử lý và phân tích dữ liệu trong Excel trở nên phức tạp hơn khi bạn cần trích xuất thông tin theo những tiêu chí nhất định. Thay vì phải lọc thủ công từng dòng dữ liệu, các hàm lấy dữ liệu có điều kiện giúp bạn tự động hóa quy trình này một cách nhanh chóng và chính xác.

Bài viết này của TechCare.vn sẽ đi sâu phân tích các hàm quan trọng nhất để lấy dữ liệu có điều kiện trong Excel. Mỗi hàm đều có ưu điểm riêng phù hợp với từng tình huống cụ thể trong công việc.

Giới Thiệu Các Hàm Lấy Dữ Liệu Có Điều Kiện

Nhóm Hàm Tính Toán Có Điều Kiện

SUMIF và SUMIFS là hai hàm được sử dụng phổ biến nhất khi cần tính tổng các giá trị thỏa mãn điều kiện nhất định. SUMIF áp dụng cho một điều kiện duy nhất, trong khi SUMIFS cho phép thiết lập nhiều điều kiện đồng thời.

COUNTIF và COUNTIFS hoạt động tương tự nhưng thay vì tính tổng, chúng đếm số lượng ô dữ liệu đáp ứng tiêu chí. Điều này hữu ích khi bạn muốn biết có bao nhiêu giao dịch trên một mức giá trị, hoặc số lượng nhân viên đạt chỉ tiêu.

AVERAGEIF và AVERAGEIFS tính giá trị trung bình của các ô thỏa mãn điều kiện, giúp phân tích xu hướng trung tâm của tập dữ liệu được lọc.

Xem thêm: Các phím tắt trong Excel nhất định phải nắm khi làm văn phòng

Nhóm Hàm Tra Cứu Và Trích Xuất

VLOOKUP và HLOOKUP là những hàm tra cứu truyền thống, tìm kiếm giá trị trong cột hoặc hàng đầu tiên rồi trả về dữ liệu từ vị trí tương ứng. Mặc dù phổ biến nhưng chúng có nhiều hạn chế về hướng tìm kiếm.

INDEX và MATCH khi kết hợp với nhau tạo thành công cụ tra cứu linh hoạt hơn, cho phép tìm kiếm theo cả hai chiều và không bị giới hạn vị trí cột kết quả.

XLOOKUP là hàm hiện đại xuất hiện trong các phiên bản Excel gần đây, khắc phục hầu hết nhược điểm của VLOOKUP với cú pháp đơn giản hơn và khả năng tìm kiếm đa chiều.

Nhóm Hàm Lọc Động

FILTER (có trong Microsoft 365 và Excel 2021) cho phép lọc toàn bộ bảng dữ liệu dựa trên một hoặc nhiều điều kiện, trả về mảng kết quả tự động cập nhật khi dữ liệu nguồn thay đổi.

UNIQUE kết hợp với FILTER giúp loại bỏ các giá trị trùng lặp trong kết quả lọc.

Xem thêm: Cách tính phần trăm trên Excel

Các Hàm Lấy Dữ Liệu Có Điều Kiện Trong Excel Phổ Biến Và Ứng Dụng Thực Tế

Excel cung cấp nhiều hàm mạnh mẽ để tra cứu và trích xuất dữ liệu dựa trên điều kiện cụ thể, giúp tiết kiệm thời gian so với việc tìm kiếm thủ công. Dưới đây là ba hàm cơ bản nhưng cực kỳ hiệu quả mà bất kỳ người dùng chuyên nghiệp nào cũng nên nắm vững. Chúng không chỉ hỗ trợ tính toán mà còn tích hợp tốt với các công cụ khác trong Excel.

Hàm INDEX: Truy Xuất Giá Trị Chính Xác Từ Phạm Vi Dữ Liệu

Hàm INDEX được thiết kế để trả về giá trị tại vị trí cụ thể trong một mảng dữ liệu, thường kết hợp với các hàm khác để tạo điều kiện tra cứu động. Đây là lựa chọn lý tưởng khi bạn cần lấy dữ liệu từ bảng đa chiều mà không bị giới hạn bởi cấu trúc cột.

Cú pháp cơ bản: =INDEX(array, row_num, column_num)

• Array: Phạm vi ô chứa dữ liệu cần tra cứu (ví dụ: A1:D10).
• Row_num: Số thứ tự hàng chứa giá trị mong muốn.
• Column_num: Số thứ tự cột chứa giá trị.

Hàm Index

Ví dụ minh họa: Giả sử bạn có bảng dữ liệu từ A1 đến D10, với các thông tin về thành phố và dân số. Để lấy tên thành phố ở hàng 6, cột 3, nhập công thức =INDEX(A1:D10, 6, 3). Kết quả có thể là "Seoul" nếu dữ liệu khớp. Để nâng cao, kết hợp INDEX với hàm khác để tạo tra cứu động, tránh lỗi khi bảng dữ liệu thay đổi kích thước.

Lưu ý: Hàm này hoạt động tốt trên dữ liệu đã sắp xếp, và bạn nên sử dụng địa chỉ tuyệt đối (nhấn F4) để cố định phạm vi khi sao chép công thức.

Xem thêm: Cách giãn dòng trong Excel

Hàm MATCH: Xác Định Vị Trí Giá Trị Trong Phạm Vi Tìm Kiếm

Hàm MATCH chuyên dùng để tìm vị trí của một giá trị trong dãy dữ liệu, thường kết hợp với INDEX để bù đắp hạn chế của các hàm tra cứu khác. Nó giúp xác định hàng hoặc cột động, phù hợp cho bảng dữ liệu thay đổi thường xuyên.

Cú pháp: =MATCH(lookup_value, lookup_array, [match_type])

• Lookup_value: Giá trị cần tìm vị trí.
• Lookup_array: Phạm vi chứa giá trị tra cứu.
• Match_type: Có thể là 1 (tìm giá trị lớn nhất nhỏ hơn hoặc bằng), 0 (tìm chính xác), hoặc -1 (tìm giá trị nhỏ nhất lớn hơn hoặc bằng).

Hàm Match

Ví dụ: Trong dãy E8:E13 chứa các số điểm, để tìm vị trí của giá trị 30, dùng =MATCH(30, E8:E13, 0). Kết quả trả về số thứ tự hàng nếu tìm thấy. Ưu điểm lớn của MATCH là khả năng xử lý dữ liệu chưa sắp xếp với match_type=0, giúp tránh lỗi khi dữ liệu không theo thứ tự.

Mẹo chuyên sâu: Sử dụng MATCH trong công thức mảng để tìm nhiều vị trí cùng lúc, đặc biệt hữu ích trong báo cáo tài chính hoặc quản lý kho hàng.

Hàm VLOOKUP: Tra Cứu Dọc Và Trả Về Giá Trị Tương Ứng

Hàm VLOOKUP là công cụ kinh điển cho việc tra cứu theo cột, thường áp dụng trong bảng dữ liệu có cấu trúc rõ ràng như danh sách nhân viên hoặc sản phẩm.

Cú pháp: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• Lookup_value: Giá trị dùng để dò tìm.
• Table_array: Bảng dữ liệu (cố định bằng F4).
• Col_index_num: Thứ tự cột cần lấy dữ liệu.
• Range_lookup: TRUE (1) cho tra cứu gần đúng, FALSE (0) cho chính xác.

Hàm Vlookup

Ví dụ: Để lấy thông tin từ bảng G8:H11 dựa trên mã D4, dùng =VLOOKUP(D4, $G$8:$H$11, 2, 0). Kết quả hiển thị giá trị ở cột thứ 2. Hàm này hiệu quả cho dữ liệu sắp xếp theo cột đầu tiên, nhưng có thể chậm với bảng lớn – lúc này, cân nhắc chuyển sang XLOOKUP nếu dùng phiên bản Excel mới.

Cách Lấy Dữ Liệu Từ Sheet Khác Trong Excel Một Cách Hiệu Quả

Khi làm việc với nhiều sheet trong một file Excel, việc lấy dữ liệu chéo sheet là nhu cầu phổ biến. Bạn có thể sử dụng các hàm trên với tham chiếu sheet để thực hiện, giúp đồng bộ thông tin mà không cần copy-paste thủ công.

Sử Dụng Hàm VLOOKUP Để Lấy Dữ Liệu Chéo Sheet

Phù hợp cho dữ liệu nhỏ, tập trung vào tra cứu chính xác.

Ví dụ: Trong sheet hiện tại, nhập =VLOOKUP(D4,$G$8:$H$11,2,0) để lấy từ Sheet2.

Sau đó, kéo công thức xuống để áp dụng cho các ô khác.

Ưu điểm: Đơn giản, nhưng cần đảm bảo bảng nguồn không thay đổi cấu trúc.

Xem thêm: Các lỗi trong Excel

Áp Dụng Hàm MATCH Để Xác Định Vị Trí Từ Sheet Khác

Hàm này tập trung vào vị trí hơn là giá trị, thường kết hợp với INDEX.

Ví dụ: =MATCH(30,E8:E13,0) để tìm từ Sheet3.

Kết quả được hiển thị như hình bên dưới:

Kết Hợp Hàm INDEX Để Truy Xuất Dữ Liệu Linh Hoạt

Ví dụ: =INDEX(C7:E13,6,2) để lấy từ Sheet4. Hàm này vượt trội ở khả năng tra cứu hai chiều, lý tưởng cho báo cáo phức tạp.

Lưu ý chung: Luôn kiểm tra đường dẫn sheet (như Sheet1!) và sử dụng tên sheet nếu file có nhiều tab. Nếu file lớn, xem xét sử dụng Power Query để tối ưu.

Kinh Nghiệm Và Mẹo Sử Dụng

Tối Ưu Hiệu Suất Công Thức

Hạn chế sử dụng SUMIFS với vùng dữ liệu quá lớn (trên 100 nghìn hàng) vì có thể gây chậm file. Trong trường hợp này, cân nhắc dùng Pivot Table hoặc Power Query để xử lý dữ liệu trước.

Tránh lồng ghép quá nhiều hàm điều kiện trong một công thức. Việc này không chỉ khó đọc mà còn làm tăng thời gian tính toán. Nên tách thành nhiều cột trung gian nếu logic quá phức tạp.

Sử dụng Name Manager để đặt tên cho các vùng dữ liệu thường xuyên tham chiếu. Điều này giúp công thức dễ hiểu hơn và dễ bảo trì khi cần thay đổi.

Xử Lý Điều Kiện Phức Tạp

Khi cần lọc theo nhiều tiêu chí dạng "hoặc" trong cùng một cột, có thể dùng hàm SUM kết hợp nhiều SUMIF thay vì SUMIFS. Ví dụ: =SUMIF(B:B,"Hà Nội",C:C)+SUMIF(B:B,"Đà Nẵng",C:C).

Đối với điều kiện văn bản không phân biệt hoa thường, thêm dấu sao (*) vào tiêu chí. Ví dụ: "*hà nội*" sẽ khớp với "Hà Nội", "HÀ NỘI" hay "hà nội".

Khi làm việc với điều kiện ngày tháng, sử dụng hàm DATE để tạo tiêu chí thay vì nhập trực tiếp. Ví dụ: ">"&DATE(2024,1,1) đảm bảo định dạng ngày chính xác.

Kết Hợp Nhiều Hàm

XLOOKUP có thể lồng trong FILTER để tạo bảng tra cứu động. Ví dụ lọc danh sách đơn hàng của một khách hàng cụ thể rồi tra thêm thông tin từ bảng khác.

INDEX-MATCH kết hợp với IFERROR tạo công thức tra cứu an toàn, tự động xử lý các trường hợp không tìm thấy mà không gây lỗi hiển thị.

SUMIFS có thể đặt trong IFERROR để trả về 0 thay vì lỗi khi không có dữ liệu thỏa mãn: =IFERROR(SUMIFS(...) ,0).

Khắc Phục Lỗi Thường Gặp

Lỗi #N/A Trong Tra Cứu

Nguyên nhân phổ biến nhất là giá trị tra cứu không tồn tại trong vùng tìm kiếm. Kiểm tra kỹ xem có dấu cách thừa, ký tự ẩn hoặc định dạng khác biệt giữa hai giá trị.

Với VLOOKUP, lỗi này còn xảy ra khi tham số range_lookup được đặt FALSE nhưng dữ liệu không sắp xếp. Hoặc cột tra cứu nằm bên phải cột kết quả.

Giải pháp: Sử dụng TRIM để loại dấu cách thừa, CLEAN để xóa ký tự ẩn. Hoặc bọc công thức trong IFERROR để hiển thị thông báo tùy chỉnh.

Lỗi #VALUE! Trong Các Hàm Điều Kiện

Lỗi này thường xuất hiện khi vùng điều kiện và vùng tính toán không cùng kích thước trong SUMIFS hoặc AVERAGEIFS. Đảm bảo tất cả các vùng có cùng số hàng.

Nếu sử dụng phép toán trong tiêu chí (>, <, >=), cần đặt trong dấu ngoặc kép và nối với giá trị bằng dấu &. Ví dụ: ">"&E2 thay vì >E2.

Với hàm FILTER, lỗi xảy ra khi điều kiện không trả về mảng TRUE/FALSE hoặc kích thước điều kiện không khớp với mảng dữ liệu.

Lỗi #SPILL! Với Các Hàm Mảng

Lỗi này xuất hiện trong Excel 365 khi vùng kết quả của hàm FILTER hoặc UNIQUE bị chặn bởi dữ liệu có sẵn. Hàm không thể "tràn" ra các ô xung quanh.

Giải pháp: Xóa sạch các ô bên phải và bên dưới ô chứa công thức. Hoặc di chuyển công thức đến vùng trống rộng hơn trong sheet.

Kết Quả Không Chính Xác

Kiểm tra định dạng dữ liệu trong các cột điều kiện. Số lưu dạng văn bản sẽ không được tính toán đúng trong SUMIFS. Sử dụng VALUE() để chuyển đổi nếu cần.

Chú ý phân biệt tham chiếu tương đối và tuyệt đối khi sao chép công thức. Các vùng dữ liệu nguồn nên dùng $ để cố định.

Lời Khuyên Từ TechCare.vn

Đầu tư thời gian làm quen với XLOOKUP và FILTER nếu đang dùng Microsoft 365 hoặc Excel 2021. Hai hàm này giải quyết hầu hết nhu cầu lấy dữ liệu có điều kiện với cú pháp hiện đại và hiệu quả hơn nhiều so với các hàm cũ.

Tuy nhiên, vẫn nên nắm vững SUMIFS, COUNTIFS vì chúng tương thích với mọi phiên bản Excel và đủ mạnh cho phần lớn tác vụ thống kê cơ bản. Đừng cố gắng dùng công cụ mới nếu công cụ cũ đã giải quyết tốt vấn đề.

Tạo thói quen ghi chú hoặc comment trong các ô chứa công thức phức tạp. Sau vài tháng, chính bạn cũng có thể quên logic đã xây dựng. Một dòng chú thích ngắn sẽ tiết kiệm rất nhiều thời gian sau này.

Thực hành với dữ liệu thật từ công việc của bạn thay vì chỉ làm theo ví dụ mẫu. Các tình huống thực tế thường phức tạp và đa dạng hơn, giúp bạn phát triển kỹ năng giải quyết vấn đề tốt hơn.

Khi gặp khó khăn, hãy chia nhỏ vấn đề. Thay vì viết một công thức dài và phức tạp, tạo các cột trung gian để xử lý từng bước. Sau khi có kết quả đúng, bạn có thể tối ưu lại nếu cần.

Luôn kiểm tra kết quả bằng nhiều cách khác nhau. Dùng lọc tự động để đối chiếu, hoặc tạo Pivot Table song song để xác thực số liệu. Sai số nhỏ trong công thức có thể dẫn đến quyết định sai lầm lớn.

Việc thành thạo các hàm lấy dữ liệu có điều kiện trong Excel. Từ những tác vụ đơn giản như tính tổng doanh thu theo khu vực đến việc xây dựng dashboard tự động cập nhật, các hàm này là nền tảng không thể thiếu.

TechCare.vn khuyến khích bạn không ngừng thực hành và thử nghiệm với các hàm này trên dữ liệu thực tế. Sự thành thạo đến từ việc áp dụng kiến thức vào giải quyết vấn đề cụ thể, không phải chỉ đọc lý thuyết. Hãy bắt đầu từ những công thức đơn giản, sau đó dần dần nâng cao độ phức tạp khi đã tự tin hơn.