Hàm INDEX là một hàm được sử dụng khá nhiều trong excel. Hàm INDEX có thể kết hợp với nhiều hàm khác như kết hợp với hàm MATCH để tìm kiếm giá trị trong vùng dữ liệu thay thế cho hàm VLOOKUP, kết hợp với hàm MAX/MIN và SUM… Vậy cách sử dụng hàm INDEX như thế nào? Mời bạn tìm hiểu nội dung ấy trong bài viết này.
Hàm INDEX dạng mảng trong excel
Mô tả
– Hàm INDEX cho phép trả về giá trị của một thành phần trong một bảng hoặc một mảng dữ liệu dựa trên số hàng và số cột được chỉ định.
– Hàm INDEX dạng mảng được sử dụng nếu đối số thứ nhất của hàm là một hằng số dạng mảng.
Cú pháp
– Cú pháp hàm INDEX dạng mảng: INDEX(array, row_num, [column_num])
Trong đó:
- array: một dãy các ô, có tên dải hoặc bảng. (bắt buộc)
- row_num: hàng thứ mấy trong mảng mà bạn muốn được trả về một giá trị. Nếu row_num bị bỏ qua, column_num được yêu cầu. (bắt buộc)
- column_num: cột thứ mấy để trả về một giá trị. Nếu column_num bị bỏ qua, row_num được yêu cầu. (tùy chọn).
Lưu ý
– Nếu cả hai đối số row_num và column_num đều được dùng thì công thức chỉ trả về giá trị trong ô tại giao điểm row_num và column_num.
– Các đối số row_num và column_num phải trỏ tới một ô trong mảng. Nếu không thì công thức sẽ trả về #REF! lỗi.
– Nếu bạn đặt row_num hoặc column_num bằng 0 thì công thức nhập sẽ trả về mảng cho toàn bộ cột hoặc hàng tương ứng.
Ví dụ
Tìm số lượng hàng của mẫu sản phẩm 4 bán ra ở tuần thứ 3 trong bảng excel dưới đây.
Hướng dẫn:
Nhập công thức: =INDEX (B2:D6,4,3)
hoặc =INDEX ($B$2:$D$6,G2,G1) như hình bên dưới.
Kết quả: Kết quả sẽ trả về số lượng hàng của sản phẩm ở vị trí hàng thứ 4, cột thứ 3 trong mảng B2:D6. Kết quả là bằng 19.
Hàm Index dạng tham chiếu trong excel
Mô tả
– Hàm INDEX dạng tham chiếu trong excel trả về ô tham chiếu tại giao điểm của hàng và cột đã xác định.
– Nếu tham chiếu được tạo thành các lựa chọn không liền kề, bạn có thể chọn lựa chọn để tìm kiếm.
Cú pháp
Cú pháp hàm INDEX dạng tham chiếu: =INDEX(reference, row_num, [column_num], [area_num])
Trong đó:
- reference: là một hoặc nhiều dải.
- row_num: là số thứ tự của hàng nằm trong khoảng mà từ đó trả về một ô tham chiếu, nó tương tự như dạng mảng.
- column_num: là số thứ tự cột để trả về một ô tham chiếu, cũng hoạt động tương tự như dạng mảng.
- area_num: là một tham số tùy chọn chỉ ra mảng từ đối số tham chiếu để sử dụng. Nếu bỏ qua, công thức INDEX sẽ trả lại kết quả cho mảng đầu tiên được liệt kê trong danh sách tham khảo.
Lưu ý
– Nếu bạn nhập nhiều hơn một phạm vi không liền kề với nhau thì hãy đặt các đối số tham chiếu trong ngoặc đơn hoặc là sử dụng dấu phẩy để ngăn tách. Ví dụ:(A1:B5,D1:F5).
– Nếu mỗi dãy trong tài liệu tham khảo chỉ chứa một hàng hoặc cột, đối số row_num hoặc column_num tương ứng là tùy chọn.
Ví dụ
Nhập công thức cho ô F1 như sau: =INDEX ((A2:D3, A5: D7), 3, 4, 2)
Kết quả trả về: Hàm INDEX tìm kiếm giá trị tại 2 vùng chọn là A2: D3 (vùng 1) và A5:D7 (vùng 2), trả giá trị tại giao điểm của hàng thứ 3, cột thứ 4 của vùng tìm kiếm thứ 2.
Kết hợp hàm INDEX và hàm MATCH trong excel
Thay vì sử dụng hàm tìm kiếm hàng dọc VLOOKUP thì người ta sử dụng kết hợp hàm INDEX và hàm MATCH với nhau. Sử dụng kết hàm INDEX và hàm MATCH có lợi hơn sử dụng hàm VLOOKUP là vì:
– Nếu sử dụng hàm VLOOKUP thì chúng ta chỉ có thể tra cứu dữ liệu từ trái qua phải còn INDEX và hàm MATCH không phân biệt cột chứa dữ liệu là cột nào, chỉ cần tìm ra vị trí.
– Dùng hàm INDEX sẽ giảm bớt độ rộng vùng phải quét do vậy tìm thấy kết quả nhanh chóng hơn.
– Hàm MATCH sử dụng để tìm kiếm vị trí có thể là tương đối hoặc tuyệt đối của 1 giá trị trong 1 dòng hoặc một cột của bảng tính.
– Cú pháp của hàm MATCH: MATCH (lookup_value, lookup_array, [match_type])
Trong đó:
- Lookup_value(bắt buộc): là giá trị bạn muốn tìm. Có thể là số, chữ, giá trị logic hoặc ô tham chiếu
- Lookup_array (bắt buộc): là mảng cần tìm kiếm.
- Match_type (tuỳ chọn): xác định loại tìm kiếm. Có thể nhận một trong các giá trị sau: 1, 0, -1. Khi đặt match_type bằng 0, giá trị trả về là chính xác, trong khi hai loại còn lại cho giá trị xấp xỉ.
– Được hiểu đơn giản là:
=MATCH(giá_trị_cần_tìm, mảng_tìm_kiếm, [kiểu_tìm_kiếm])
- Giá_trị_cần_tìm: giá trị cần được tìm kiếm vị trí trong mảng.
- Mảng_tìm_kiếm: mảng chứa giá trị cần tìm kiếm.
- Kiểu_tìm_kiếm: tìm kiếm giá trị chính xác hay tìm kiếm giá trị gần nhất:
– Khi kiểu tìm kiếm bằng 1: tìm giá trị lớn nhất mà giá trị đó bằng hoặc nhỏ hơn giá_trị_cần_tìm. Giá trị trong mảng_tìm_kiếm cần được sắp xếp tăng dần
– Khi kiểu tìm kiếm bằng 0: giá trị bằng, yêu cầu tìm kiếm chính xác
– Khi kiểu tìm kiếm bằng -1: tìm giá trị nhỏ nhất mà giá trị đó lơn hơn hoặc bằng giá_trị_cần_tìm.
VD1: Tìm doanh số
– Ví dụ: Cho thông tin như bảng dưới, tìm giá trị doanh số cửa hàng F kết hợp hàm INDEX và hàm MATCH.
Hướng dẫn:
– Nhập cú pháp cho C10: =INDEX($B$2:$C$7,MATCH(C9,$B$2:$B$7,0),2).
Trong đó:
- $B$2:$C$7 là vùng chúng ta tìm kiếm giá trị.
- MATCH(C9,$B$2:$B$7,0) để trả về số thứ tự dòng của cửa hàng D trong vùng tìm kiếm.
- 2 là vị trí của cột doanh số trong mảng tìm kiếm $B$2:$C$7.
– Kết quả: Kết quả công thức trả về tìm thấy doanh số của cửa hàng F là 900.
– Nếu bạn thay đổi giá trị trong ô C9 thì kết quả trong ô C9 cũng thay đổi tự động theo công thức.
VD2: Tìm tên cửa hàng khi biết doanh số
– Cũng cùng với dữ liệu như ở ví dụ trên, chúng ta hãy tìm kiếm tên cửa hàng khi biết trước được doanh số của nó.
Hướng dẫn:
– Nhập công thức cho ô C13 như sau: =INDEX($B$2:$B$7,MATCH(C12,$C$2:$C$7,0))
Trong đó:
$B$2:$B$7 là mảng tìm kiếm giá trị
=MATCH(C12,$C$2:$C$7,0) để tìm kiếm vị trí của doanh số biết trước.
– Kết quả: kết quả trả về giá trị là tên cửa hàng E.
Kết hợp hàm INDEX với hàm SUM trong excel
VD3: Tính doanh số cột C
– Tương tự cũng cùng số liệu với bảng ví dụ ở trên, hãy tính tổng doanh số hàng hóa ở cột C.
Hướng dẫn:
– Như vậy, để tìm kiếm giá trị trong vùng dữ liệu, ta dùng hàm INDEX, và muốn tính tổng thì ta lại dùng đến hàm SUM, do vậy chúng ta kết hợp 2 hàm này lại với nhau để thực hiện bài toán.
– Nhập công thức kết hợp hàm INDEX và hàm SUM như sau: =SUM(INDEX(A2:C7,,3))
Trong đó:
- INDEX(A2:C7,,3) là tìm kiếm giá trị trả về thuộc vùng tìm kiếm A2:C7 và cột thứ 3 của vùng tìm kiếm đó.
– Hoặc bạn cũng có thể sử dụng công thức: =SUM(C2:C7) để tính tổng doanh thu cột C.
VD4: Tính doanh số 3 cửa hàng đầu tiên
– Hãy tính doanh số của 3 cửa hàng trên cùng.
Hướng dẫn:
Có 3 cách để bạn tính tổng doanh số của 3 cửa hàng trên cùng như sau:
– Nhập công thức cho ô: =SUM(C2:C4)
– Nhập công thức cho ô: =SUM(C2:INDEX(A2:C7,3,3))
– Nhập công thức cho ô: =SUM(INDEX(A2:C7,1,3):INDEX(A2:C7,3,3))
Kết hợp hàm INDEX với hàm MIN/MAX trong excel
VD5: Tìm cửa hàng có doanh số thấp nhất
– Cũng với bài toán cũ, như với yêu cầu là kết hợp với hàm MAX/MIN để tìm kiếm cửa hàng có doanh số thấp nhất.
Hướng dẫn:
– Nhập công thức cho ô: =INDEX(B2:B7,MATCH(MIN(C2:C7),C2:C7,0))
Trong đó:
- MATCH(MIN(C2:C7),C2:C7,0) là tìm kiếm giá trị nhỏ nhất trong cột C.
- B2:B7 là vùng tìm kiếm bạn muốn trả kết quả về là tên cửa hàng.
VD6: Tìm cửa hàng có doanh số cao nhất
– Cùng với dữ liệu của bài toán cũ, hãy tìm cửa hàng có doanh số thấp nhất và doanh số cao nhất.
Hướng dẫn:
– Để tìm kiếm giá trị lớn nhất, chúng ta sử dụng kết hợp hàm MAX và hàm MATCH và kết hợp với hàm INDEX để tìm kiếm vị trí của cửa hàng trong vùng tìm kiếm để trả giá trị về.
– Nhập công thức cho ô như sau: =INDEX(B2:B7,MATCH(MAX(C2:C7),C2:C7,0))
Như vậy, với những hướng dẫn trên, hy vọng đã giúp bạn hiểu hơn về công thức của hàm INDEX và giúp bạn biết cách sử dụng hàm INDEX trong excel. Cùng với sử dụng kết hợp nhiều hàm khác với INDEX để tìm giá trị mà bạn được yêu cầu tìm kiếm.
