Những tính năng hiệu quả nhất của hàm Index trong Excel (P1)

CHÀO MỪNG BẠN ĐẾN VỚI THƯ VIỆN HỌC TẬP & ĐÀO TẠO CƠ BẢN

Những tính năng hiệu quả nhất của hàm Index trong Excel (P1)

Excel

Bắt đầu sử dụng Excel

Công thức và hàm

Nhập và phân tích dữ liệu

Định dạng dữ liệu

Xử lý sự cố

Excel 2016

Trong tất cả các chức năng của Excel mà sức mạnh của chúng thường bị đánh giá thấp và không được sử dụng đúng cách, thì hàm Index trong excel chắc chắn sẽ xếp hạng ở đâu đó trong top 10. Bởi hàm này rất nhạy và có thể kết hợp với các hàm khác nữa để giải quyết được nhiều vấn đề đặt ra.

1. Nguồn dữ liệu (SourceData) của hàm Index trong excel

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.

Nguồn dữ liệu (SourceData) của hàm Index trong excel

Nguồn dữ liệu (SourceData) của hàm Index trong excel

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.

2. 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 trong Excel 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à 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:

Để 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_num là 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:

3. 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 trong Excel 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.

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.

4. Sử dụng hàm Index trong excel vớ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 trong Excel.

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))

Ví dụ 2.  Tính tổng các giá trị giữa 2 mục cụ thể

Trong trường hợp bạn muốn xác định giới hạn trên và giới hạn dưới trong công thức của mình, chỉ cần sử dụng hai hàm INDEX để trả lại mục đầu tiên và mục cuối cùng bạn muốn.

Ví dụ: công thức sau trả về tổng các giá trị trong cột Diameter (đường kính) giữa hai dữ liệu được chỉ định trong ô B1 và ​​B2:

= SUM (INDEX (SourceData [Diameter], B1): INDEX (SourceData [Diameter], B2)) 

Chúc bạn thực hiện thành công.

Tham khảo thêm 2 khóa học hữu ích do giảng viên quốc tế trường FPT Arena giảng dạy: