3 TÍNH NĂNG HIỆU QUẢ NHẤT CỦA HÀM INDEX TRONG EXCEL

lop tin hoc tai thanh hoa

Hàm INDEX là gì?

Hàm INDEX sẽ trả về một ô tham chiếu trong một mảng hoặc một dải nhất định. Nói cách khác, bạn sử dụng INDEX khi bạn biết (hoặc có thể tính toán) vị trí của một phần tử trong dải.

lop tin hoc tai thanh hoa

**Hàm INDEX – cú pháp và sử dụng cơ bản**

Có hai phiên bản của hàm INDEX trong Excel – định dạng dải và định dạng tham chiếu. Và cả hai mẫu đều có thể được sử dụng trong tất cả các phiên

bản của Microsoft Excel 2013, Excel 2010, Excel 2007 và 2003.

Dạng mảng INDEX trả về giá trị của một phần tử trong một bảng hoặc một mảng dựa trên số hàng và cột mà bạn chỉ định.

Cấu trúc hàm

INDEX (array, row_num, [column_num])

array– là một dãi các ô, có tên dải hoặc bảng.
row_num– là 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 là bắt buộc.
column_num– là cột thứ mấy để trả về một giá trị. Nếu column_num bị bỏ qua, row_num là bắt buộc.

Ví dụ, công thức “= INDEX (A1: D6, 4, 3)” sẽ trả về giá trị tại giao điểm của hàng thứ 4 và cột thứ 3 trong mảng A1: D6, là giá trị trong ô C4.

Để có ý tưởng về cách hàm INDEX hoạt động trên dữ liệu thực, hãy xem ví dụ sau:

lop tin hoc tai thanh hoa

Thay vì nhập số hàng và cột trong công thức, bạn có thể cung cấp tham chiếu ô để có được công thức: “= INDEX ($B$2:$D$6,G2,G1)”

Lưu ý: Việc sử dụng tài liệu tham khảo tuyệt đối ($B$2:$D$6) thay vì tham chiếu tương đối (B2:D6) trong đối số mảng giúp bạn dễ dàng sao chép công thức lên các ô khác. Ngoài ra, bạn có thể chuyển đổi một dải thành một bảng (Ctrl + T) và tham khảo nó bằng tên bảng.

**Dạng mảng và INDEX – điều cần nhớ**

Nếu đối số mảng chỉ bao gồm một hàng hoặc cột, thì bạn có thể xác định hoặc không xác định đối số row_numhoặc column_num tương ứng.
Nếu đối số mảng bao gồm nhiều hơn một dòng và row_numbị bỏ qua hoặc bằng 0, thì hàm INDEX trả về một mảng của toàn bộ cột. Tương tự,
nếu mảng bao gồm nhiều cột và đối số column_num bị bỏ qua hoặc bằng 0, công thức INDEX sẽ trả về toàn bộ hàng. Dưới đây là một ví dụ về

công thức minh họa.

4. Các đối số row_num và column_num phải tham chiếu đến một ô trong mảng; Nếu không, công thức INDEX sẽ trả về #REF!lỗi.

Định dạng tham chiếu INDEX

Định dạng tham chiếu của hàm INDEX trả về ô tham chiếu tại giao điểm của hàng và cột đã xác định.

Cấu trúc hàm

INDEX (reference, row_num, [column_num], [area_num])

reference– là một hoặc nhiều dải.

Nếu bạn nhập nhiều hơn một phạm vi, hãy tách các khoảng bằng dấu phẩy và kèm theo đối số tham chiếu trong ngoặc đơn, 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.

row_num –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 –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 –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.

Ví dụ, công thức = INDEX ((A2: D3, A5: D7), 3, 4, 2) trả về giá trị của ô D7, tại giao điểm của hàng thứ 3 và cột thứ 4 trong khu vực thứ hai (A5: D7).

trung tâm tin học tại thanh hóa

**Định dạng tham chiếu INDEX – những điều cần nhớ**

Nếu đối số row_num hoặc column_num được đặt thành 0, thì một công thức Excel INDEX trả về tham chiếu cho toàn bộ cột hoặc hàng tương ứng.
Nếu cả hai row_num và column_num bị bỏ qua, hàm INDEX trả về vùng được chỉ định trong đối số area_num.
Tất cả các _num arguments (row_num, column_num và area_num) phải tham khảo một ô trong tham chiếu; Nếu không, công thức INDEX sẽ trả về #REF! lỗi.

Cả hai công thức INDEX chúng ta đã thảo luận cho đến nay đều rất đơn giản và chỉ minh hoạ cho khái niệm. Công thức thực sự của bạn có thể phức

tạp hơn nhiều so với đó, vì vậy, hãy cùng khám phá một vài sử dụng hiệu quả nhất của INDEX trong Excel.

Các hàm thường dùng trong excel

**Cách dùng hàm INDEX trong Excel- ví dụ, công thức**

Có thể nó không có nhiều ứng dụng thực tế, nhưng nếu kết hợp với các hàm khác như MATCH hay COUNTA, nó có thể tạo các công thức rất mạnh.

Nguồn dữ liệu (SourceData)

Tất cả các công thức Excel INDEX của chúng tôi (ngoại trừ công thức cuối cùng), chúng tôi sẽ sử dụng dữ liệu dưới đây. Để thuận tiện, nó được tổ

chức trong một bảng có tên SourceData.

hoc tin hoc van phong tai thanh hoa

Việc sử dụng các bảng hoặc các mảng được đặt tên có thể làm cho công thức dài hơn một tí, nhưng nó cũng làm cho chúng linh hoạt và dễ đọc hơn.

Để điều chỉnh bất kỳ công thức INDEX nào cho các bảng tính của bạn, bạn chỉ cần chỉnh sửa một cái tên duy nhất.

Tất nhiên, không có gì ngăn cản bạn sử dụng mảng thông thường nếu bạn muốn. Trong trường hợp này, bạn chỉ cần thay thế tên bảng SourceData

bằng tham chiếu mảng thích hợp.

Lấy dữ liệu ở vị trí thứ N từ danh sách

Đây là cách sử dụng cơ bản và là ví dụ về hàm INDEX dễ hiểu nhất. Để lấy một dữ liệu nhất định trong danh sách, bạn chỉ cần viết = INDEX (range,

n), trong đó range là một dải ô hoặc dải ô được đặt tên, và n là vị trí của dữ liệu mà bạn muốn nhận.

Khi làm việc với bảng Excel, bạn có thể dùng trỏ chuột chọn cột và Excel sẽ đưa tên của cột cùng với tên của bảng vô công thức:

trung tâm tin học tại thanh hóa

Để nhận giá trị của ô ở giao điểm của một hàng và cột đã cho, bạn sử dụng cách tiếp cận tương tự với sự khác biệt duy nhất là bạn sử dụng cả hai – số

hàng và số cột.

Và đây là một ví dụ khác: Trong bảng tính mà chúng tôi lấy làm ví dụ, để tìm ra hành tinh lớn thứ 2 trong hệ mặt trời, bạn sắp xếp bảng theo cột Đường

kính (Diameter) và sử dụng công thức INDEX như sau:

= INDEX (SourceData, 2, 3)

Array là tên bảng, hoặc một tham chiếu dải, tương ứng với SourceData trong ví dụ này.
Row_numlà 2 vì bạn đang tìm kiếm mục thứ hai trong danh sách
Column_num là 3 vì Diameter ở vị trí cột thứ 3 trong bảng.

Nếu bạn muốn trả lại tên của hành tinh thay vì đường kính, thì bạn phải thay đổi column_num thành 1. Và tự nhiên, bạn có thể sử dụng một tham chiếu

ô trong đối số row_num và/hoặc column_num để làm cho công thức INDEX của bạn linh hoạt hơn, như được thể hiện trong hình bên dưới:

Nhận được tất cả các giá trị trong một hàng hoặc cột

Ngoài việc lấy ra một ô duy nhất, hàm INDEX có thể trả về một dải các giá trị từ toàn bộ các hàng hoặc các cột. Để lấy tất cả các giá trị từ một cột nhất

định, bạn phải bỏ qua đối số row_num hoặc đặt nó là 0. Tương tự, để có được toàn bộ hàng, bạn bỏ qua hoặc 0 trong column_num.

Các công thức INDEX như vậy khó có thể được sử dụng bởi vì Excel không thể phù hợp với dải các giá trị được trả về bởi công thức trong một ô duy

nhất, mà thay vào đó thì bạn sẽ nhận được #VALUE! lỗi. Tuy nhiên, nếu bạn sử dụng INDEX kết hợp với các hàm trong Excel khác, chẳng hạn

như SUM hoặc AVERAGE, bạn sẽ thu được kết quả tuyệt vời.

Ví dụ, bạn có thể sử dụng công thức Index Excel sau để tính nhiệt độ trung bình của hành tinh trong hệ mặt trời:

= AVERAGE (INDEX (SourceData,, 4))

Trong công thức trên, đối số column_num là 4 vì Temperature là cột thứ 4 trong bảng của chúng ta. Thông số row_num bị bỏ qua.

lop tin hoc tai thanh hoa

Tương tự như vậy, bạn có thể tìm được nhiệt độ tối thiểu và tối đa:

= MAX (INDEX (SourceData,, 4))

= MIN (INDEX (SourceData,, 4))

Và tính tổng khối lượng hành tinh (Mass là cột thứ 2 trong bảng):

= SUM (INDEX (SourceData,, 2))

Từ quan điểm thực tế, hàm INDEX trong công thức trên là không cần thiết. Bạn chỉ cần viết = AVERAGE (dải) hoặc = SUM (dải) và cũng nhận được

kết quả tương tự.

Khi làm việc với dữ liệu thực, tính năng này có thể hữu ích như là một phần của các công thức phức tạp hơn mà bạn sử dụng để phân tích dữ liệu.

Sử dụng INDEXvới các hàm Excel khác (SUM, AVERAGE, MAX, MIN)

Từ các ví dụ trước, bạn có thể nghĩ rằng công thức INDEX trong Excel trả về các giá trị, nhưng thực tế là nó trả về một tham chiếu đến ô có chứa giá trị.

Và ví dụ này thể hiện bản chất thực sự của hàm INDEX.

Với kết quả của một công thức INDEX mang tính tham chiếu, chúng ta có thể sử dụng nó trong các hàm khác để tạo ra một dải động. Khó hiểu ư? Công

thức sau đây sẽ làm rõ mọi thứ:

Giả sử bạn có một công thức = AVERAGE (A1: A10) trả về giá trị trung bình của các ô A1: A10. Thay vì viết dải trực tiếp như trong công thức, thì bạn có

thể thay thế cả A1 hoặc A10 hoặc cả hai bằng các hàm INDEX như sau:

= AVERAGE(A1: INDEX (A1: A20,10))

Cả hai công thức trên sẽ mang lại kết quả tương tự bởi vì hàm INDEX cũng trả về một tham chiếu đến ô A10 (row_num được đặt là 10, col_num bỏ

qua). Sự khác biệt là công thức AVERAGE / INDEX sử dụng cho dải động và khi bạn thay đổi đối số row_num trong INDEX, thì dải mà được xử lý

bởi hàm AVERAGE sẽ thay đổi và công thức sẽ trả về một kết quả khác.

Rõ ràng, tuy cách thức công thức INDEX vận hành tỏ ra quá phức tạp, nhưng nó có các ứng dụng thực tế, như trong các ví dụ sau đây:

Ví dụ 1. Tính trung bình của các mục trong danh sách n mục đứng đầu:

Giả sử bạn muốn biết đường kính trung bình của n hành tinh lớn nhất trong hệ thống của chúng tôi. Vì vậy, bạn sắp xếp bảng theo cột Diameter từ lớn

nhất đến nhỏ nhất và sử dụng công thức Average / Index sau:

= AVERAGE(C5: INDEX (SouceData [Diameter], B1))