Cách để sử dụng hàm liệt kê danh sách trong Excel hiệu quả nhất

Hiện nay, trong Excel hàm Lookup hỗ trợ người sử dụng tìm kiếm giá trị theo điều kiện đưa ra. Tuy nhiên, hàm này có nhược điểm là chỉ đưa ra được kết quả của một trị giá xuất hiện sớm nhất. Vì vậy, bạn không thể thống kê được tất cả các giá trị cùng đáp ứng điều kiện. Ngoài ra,  hàm Lookup không hỗ trợ trích xuất để tập hợp thành một bảng dữ liệu khác hoặc chuyển sang Sheet khác. Như vậy, cần phải sử dụng các hàm gián tiếp khác để phục được hai nhược điểm này. Hàm liệt kê danh sách trong Excel được sử dụng dựa trên sự kết hợp của các hàm IFERROR, INDEX, SMALL.

Hàm IFERROR

Mục đích sử dụng hàm IFERROR để lấy những giá trị mong muốn khi điều kiện của hàm này cho kết quả lỗi.

Cấu trúc hàm

= IFERROR(value, value_if_error).

Giải thích các thuật ngữ

• • IFERROR: đây là tên gọi của hàm
  • Value: chọn vùng giá trị đang cần xác định có xuất hiện lỗi hay không.
  • Value_if_error: giá trị sẽ lấy về khi giá trị value báo lỗi.

Chú ý

Nếu giá trị của value cho là lỗi (#N/A, #REF,…) thì hàm IFERROR sẽ trả về là value_if_error, nếu không thì sẽ trả về là giá trị value.

Loại giá trị Value_if_error bạn có thể chọn là: để trống (“”); điền số 0; cụ thể hơn là dòng chữ để bạn ghi nhớ dễ dàng khi lỗi.

Hàm INDEX

Có hai loại hàm INDEX đó là INDEX dạng mảng và INDEX dạng tham chiếu:

Hàm INDEX dạng mảng

Hàm INDEX sẽ đưa ra dữ liệu của một ô trong vùng với nền tảng là chỉ số row và chỉ số array của vùng đó.

Cấu trúc hàm

=INDEX(array, row_num, [column_num]).

Trong đó:

• INDEX: là tên gọi của hàm
• Array: là nguồn dữ liệu bao gồm các cột giá trị.
• row_num: Chọn vào hàng để làm khởi đầu trả về một giá trị trong nguồn dữ liệu.

Chú ý

Trong hàm INDEX điều kiện cần thiết là phải sử dụng ít nhất một trong hai dữ liệu Row_num và Column_num. Nếu không đáp ứng đúng thì kết quả sẽ báo lỗi.

Hàm INDEX dạng tham chiếu

Hàm Index dạng tham chiếu đưa ra tham chiếu của ô nằm ở giao của một row và một column cố định được đưa ra.

Cấu trúc hàm

=INDEX(Reference,Row_num,[Column_num],[Area_num])

Trong đó:

• Reference: là vùng tham chiếu cố định.
• Row_num: chỉ số hàng từ đó đưa ra một tham chiếu có tính chất cố định.
• Column_num: chỉ số cột từ đó đưa ra một tham chiếu có tính chất tùy chọn.
• Area_num: số của vùng ô sẽ đưa ra giá trị tại vùng Reference. 

Chú ý

Nếu Area_num không cần dùng đến thì hàm INDEX dùng vùng tùy chọn.

Hàm SMALL

Hàm SMALL trả về giá trị nhỏ thứ k của nguồn dữ liệu đưa ra. Dùng hàm này để trả về giá trị với thứ hạng tương đối cụ thể trong tập dữ liệu.

Cấu trúc hàm

=SMALL(array,k).

Trong đó:

• SMALL: là tên gọi của hàm.
• Array: vùng dữ liệu có định dạng số mà bạn cần tìm ra giá trị nhỏ thứ k của nó.
• K: Vị trí (từ giá trị nhỏ nhất) trong vùng dữ liệu cần trả về.

Chú ý

• Khi để trống giá trị Array thì hàm đưa ra kết quả là giá trị lỗi (#NUM!)
• Nếu K ≤ 0 hoặc K nhiều hơn số điểm dữ liệu trong Array thì hàm cũng đưa ra giá trị lỗi #NUM!

Cách sử dụng hàm liệt kê danh sách trong Excel

Các hàm trực tiếp hiện nay không đáp ứng được yêu cầu liệt kê danh sách và sao chép. Vì vậy, chúng ta sẽ cần sự kết hợp chính xác các hàm IFERROR, INDEX, SMALL. Mục đích là để tạo ra được Hàm liệt kê danh sách trong Excel.

Ví dụ: cho bảng dữ liệu như bên dưới. Bài toán yêu cầu liệt kê danh sách chi tiết doanh thu theo của hàng với điều kiện là chi nhánh Hải Dương

Bạn cần liệt kê danh sách 3 mục doanh thu của Chi nhánh Hải Dương ở vào cột J.

Cấu trúc hàm:

=IFERROR(INDEX($G$1:$G$12,SMALL(IF($J$2=$B$3:$B$12,ROW($G$3:$G$12)),ROW(A1))),””)

Trong đó:

• $G$1:$G$12: là cột dữ liệu tham chiếu cần lấy giá trị để trả về.
• $J$2 là ô chứa điều kiện cần tìm.
• $B$3:$B$12 là cột dữ liệu chứa điều kiện tìm kiếm.

Để sao chép công thức ở cột J3 cho các dòng khác bạn cần sử dụng tổ hợp phím Ctrl + Shift + Enter. Như vậy, là bạn đã hoàn thành yêu cầu của bài toán rồi. Hiện nay, chưa có Hàm liệt kê danh sách trong Excel trực tiếp nào nên dù hơi phức tạp nhưng bạn vẫn có thể làm được đúng không nào.

Chú ý

• Để không bị lỗi trong quá trình sao chép công thức, thì các nguồn dữ liệu được dùng đến cần được đặt giá trị tuyệt đối “$..$”.
• Trong khi viết hàm INDEX nếu vùng dữ liệu tham chiếu nhiều, thì cách tốt nhất là bạn nên sao chép công thức cho toàn bộ cột. Hoặc sao chép sao cho ít nhất là bằng số dòng của vùng tham chiếu để đạt được kết quả tốt nhất.

Bài viết này giúp chúng ta nắm bắt được cấu trúc của các hàm IFERROR, INDEX, SMALL. Từ đó có thể sử dụng trực tiếp những hàm này để đáp ứng các yêu cầu ứng với từng hàm. Ngoài ra, sự kết hợp của chúng sẽ đưa đến cho chúng ta hàm liệt kê danh sách trong Excel. Ưu điểm của hàm này là mang lại kết quả chính xác và đáp ứng những điều kiện phức tạp. Chúng sẽ giúp cho việc làm báo cáo của bạn được dễ dàng và thuận tiện hơn.