Or you want a quick look: Các hàm liệt kê danh sách trong Excel
Sau đây ThuthuatOffice sẽ giới thiệu cho bạn một số hàm liệt kê danh sách trong Excel cực hiệu quả. Tăng cao hiệu suất làm việc của bạn. Cùng tìm hiểu ngay.
Bạn đang cần tìm kiếm một hàm nâng cao trong Excel để liệt kê ra một danh sách các giá trị theo điều kiện bạn đạt ra. Với những hàm cơ bản như hàm VLOOKUP hay HLOOKUP sẽ giải quyết vấn đề này chưa thật sự tốt. Chính vì thế, ThuthuatOffice sẽ đưa ra một số hàm liệt kê danh sách trong Excel mà bạn có thể cần để giải quyết các vấn đề của mình khi kết hợp chúng với các hàm cơ bản khác.
[external_link_head]
Các hàm liệt kê danh sách trong Excel
Có các hàm liệt kê danh sách trong Excel từ dạng mảng, tham chiếu hoặc theo điều kiện. Cùng ThuthuatOffice tìm hiểu chức năng và cách sử dụng các hàm này dưới đây.
Hàm INDEX
Hàm INDEX trong Excel trả về giá trị tại một vị trí nhất định trong một phạm vi hoặc mảng. Bạn có thể sử dụng INDEX để truy xuất các giá trị riêng lẻ hoặc toàn bộ hàng và cột. INDEX thường được sử dụng với hàm MATCH, trong đó MATCH định vị và cung cấp một vị trí cho INDEX.
Hàm INDEX có hai dạng: mảng và tham chiếu. Cả hai biểu mẫu đều có cách trả về giá trị giống nhau là một tham chiếu trong một mảng dựa trên vị trí hàng và cột nhất định.
Sự khác biệt là dạng tham chiếu của INDEX cho phép tham chiếu nhiều hơn một mảng, cùng với một đối số tùy chọn để chọn mảng nào nên được sử dụng. Hầu hết các công thức INDEX sử dụng dạng mảng, nhưng cả hai dạng đều được trình bày dưới đây để bạn đọc tham khảo.
Dạng mảng
Cú pháp của hàm INDEX như sau:
=INDEX (Array; row_num; [col_num])
Trong đó:
- Array: Dải ô hoặc mảng ô dữ liệu.
- row_num: Số hàng
- col_num(tùy chọn): Số cột
Nếu cả row_num và col_num đều được cung cấp, INDEX trả về giá trị trong ô tại giao điểm của row_num và col_num.
[external_link offset=1]Nếu row_num được đặt thành 0, INDEX trả về một mảng giá trị cho toàn bộ cột. Để sử dụng các giá trị mảng này, bạn có thể nhập hàm INDEX dưới dạng công thức mảng trong phạm vi ngang hoặc cung cấp mảng vào một hàm khác.
Nếu col_num được đặt thành 0, INDEX trả về một mảng giá trị cho toàn bộ một hàng. Để sử dụng các giá trị mảng này, bạn có thể nhập hàm INDEX dưới dạng công thức mảng trong phạm vi dọc hoặc cung cấp mảng vào một hàm khác.
Dạng tham chiếu
INDEX dạng tham chiếu, tham số đầu tiên là tham chiếu đến một hoặc nhiều phạm vi và đối số tùy chọn thứ tư, area_num, được cung cấp để chọn phạm vi thích hợp. Cú pháp cho biểu mẫu tham chiếu của INDEX là:
=INDEX(reference; row_num; [col_num]; [area_num])
Trong đó:
- reference: Mẫu tham chiếu.
- row_num: Số hàng.
- col_num(tùy chọn): Số cột.
- area_num: Đối số area_num được cung cấp dưới dạng một số hoạt động giống như một chỉ mục.
Cũng giống như dạng mảng của INDEX, dạng tham chiếu của INDEX trả về tham chiếu của ô tại giao điểm row_num và col_num. Sự khác biệt là đối số tham chiếu chứa nhiều hơn một dải ô và area_num chọn dải ô sẽ được sử dụng. Mảng đầu tiên bên trong tham chiếu là 1, mảng thứ hai là 2, v.v.
Hàm SMALL
Hàm SMALL của Excel trả về các giá trị số dựa trên vị trí của chúng trong danh sách được xếp hạng theo giá trị. Nói cách khác, nó có thể truy xuất các giá trị “nhỏ nhất thứ n”, giá trị nhỏ nhất đầu tiên, giá trị nhỏ nhất thứ 2, giá trị nhỏ nhất thứ 3, v.v.
Hàm SMALL trả về giá trị nhỏ nhất thứ n trong mảng.
Cú pháp: =SMALL(Array; n)
Trong đó:
- Array: là một dải ô để trích xuất các giá trị nhỏ nhất.
- n: nhập vào đây một số nguyên xác định thứ tự giá trị nhỏ nhất cần truy xuất.
Hàm SMALL hữu ích khi bạn muốn truy xuất giá trị thấp nhất thứ n từ một tập dữ liệu. Ví dụ, thời gian nhanh nhất, thứ hai hoặc thứ ba trong một cuộc đua, như được thấy trong ảnh chụp màn hình ở trên.
Để nhận giá trị lớn thứ n, hàm LARGE cũng thực hiện điều tương tự như với việc cho ra giá trị lớn nhất thứ n với cú pháp =LARGE(array; n).
Hàm IFERROR
Hàm IFERROR trong Excel trả về một kết quả tùy chỉnh khi một công thức tạo ra lỗi và một kết quả chuẩn khi không có lỗi nào được phát hiện. IFERROR là một cách thanh lịch để bẫy và quản lý lỗi mà không cần sử dụng các câu lệnh IF lồng nhau phức tạp hơn.
Hàm IFERROR giúp bẫy và xử lý lỗi cho các dữ liệu không xác định hoặc lỗi và trả về các giá trị bạn chỉ định cho các điều kiện lỗi.
Cú pháp: =IFERROR(value; value_if_error)
[external_link offset=2]Trong đó:
- value: Giá trị, tham chiếu hoặc công thức bạn muốn kiểm tra lỗi.
- value_if_error: Giá trị trả về nếu tìm thấy lỗi.
Hàm IFERROR “bắt” lỗi trong công thức và trả về kết quả hoặc công thức thay thế khi lỗi được phát hiện.
Sử dụng hàm IFERROR để bẫy và xử lý các lỗi do các công thức hoặc hàm khác tạo ra. IFERROR kiểm tra các lỗi sau: # N / A, #VALUE !, #REF !, # DIV / 0 !, #NUM !, #NAME ?, hoặc #NULL !. Đối với lỗi ###### thực ra nó không phải là lỗi dữ liệu mà là lỗi hiển thị trong Excel nên hàm IFERROR sẽ không bắt lỗi này.
Những ví dụ sau sẽ giúp bạn dễ hình dung hơn về hàm IFERROR này.
Ví dụ: nếu A1 chứa 10, B1 trống và C1 chứa công thức = A1 / B1, công thức sau sẽ bắt lỗi # DIV / 0! lỗi do chia A1 cho B1:
Cách liệt kê danh sách theo điều kiện trong Excel
Bạn cũng có thể sử dụng hàm IFERROR để bắt lỗi #N/A do hàm VLOOKUP tạo ra khi không tìm thấy giá trị tra cứu. Cú pháp có dạng như sau:
=IFERROR(VLOOKUP(value;data;column;0);”Không tìm thấy”)
Trong ví dụ sau đây, khi hàm VLOOKUP trả về một kết quả, hàm IFERROR sẽ trả về kết quả đó. Nếu hàm VLOOKUP trả về lỗi #N/A vì không tìm thấy giá trị tra cứu, hàm IFERROR trả về “Không tìm thấy”.
Chú ý:
- Nếu giá trị trống, nó được đánh giá là một chuỗi rỗng (“”) và không phải là một lỗi.
- Nếu value_if_error được cung cấp dưới dạng chuỗi trống (“”), không có thông báo nào được hiển thị khi phát hiện lỗi.
- Nếu IFERROR được nhập dưới dạng công thức mảng, nó sẽ trả về một mảng kết quả với một mục cho mỗi ô có giá trị.
- Trong Excel 2013+, bạn có thể sử dụng hàm IFNA để bẫy và xử lý các lỗi #N/A cụ thể.
Trên đây là bài viết tổng hợp cho bạn các hàm liệt kê danh sách trong Excel được sử dụng phổ biến trong việc tổng hợp dữ liệu. Nếu bạn sử dụng thành thạo và linh hoạt các hàm trên trong Excel, chắc chắn sẽ đem lại cho bạn hiệu quả rất nhiều trong công việc hằng ngày của mình như kiểm kê việc hàng hóa nhập xuất, v.v.
Nếu bạn đang có ý định tiếp xúc và phát triển thêm sự hiểu biết và thông thạo Excel có thể theo dõi một số bài viết khác dưới đây:
- 5 cách nhập ngày tháng nhanh trong Excel nhanh và chính xác
- Cách khóa giá trị trong Excel đơn giản chỉ với 1 phím bấm
Nếu thấy bài viết hữu ích hãy để lại Like & Share cũng như bình luận ở phần bên dưới nếu có bất kỳ thắc mắc cần giải đáp. Bạn đọc có thể theo dõi thường xuyên các bài viết hữu ích khác của ThuthuatOffice được đăng tải đều đặn trên trang.[external_footer]
