Trong bài viết này, Học Excel Online sẽ lấy ví dụ về hàm INDEX trong Excel, qua đó mô tả cho bạn những cách sử dụng hiệu quả công cụ này.
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ì INDEX 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.
Vậy hàm INDEX trong Excel là gì? Về cơ bản, một 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 và bạn muốn nhận giá trị thực của phần tử đó.
Điều này nghe có vẻ tầm thường, nhưng khi bạn nhận ra tiềm năng thực sự của hàm INDEX, thì nó có thể tạo nên những thay đổi quan trọng đối với cách mà bạn tính toán, phân tích và trình bày dữ liệu trong các bảng tính của bạn.
Bạn đang đọc: 6 tính năng hiệu quả nhất của hàm INDEX trong Excel
Tóm Tắt
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 hoàn toàn có thể được sử dụng trong tổng thể những phiên bản của Microsoft Excel 2013, Excel 2010, Excel 2007 và 2003 .
Xem ngay : Tài liệu hướng dẫn sử dụng excel 2003
INDEX làm việc với mảng
Dạng mảng INDEX trả về giá trị của một thành phần 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:
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_num hoặ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_num bị 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.
- 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 khoanh vùng phạm vi, hãy tách những khoảng chừ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).
Đị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á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 tiễn, nhưng nếu tích hợp với những hàm khác như MATCH hay COUNTA, nó hoàn toàn có thể tạo những 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.
Việc sử dụng những bảng hoặc những mảng được đặt tên hoàn toà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 động và dễ đọc hơn. Để kiểm soát và điều chỉnh bất kể công thức INDEX nào cho những 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ì nếu bạn muốn. Trong trường hợp này, bạn chỉ cần sửa chữa 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 thao tác với bảng Excel, bạn hoàn toà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 đượ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 hoàn toà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 tất cả chúng ta. Thông số row_num bị bỏ lỡ .
Tương tự như vậy, bạn hoàn toà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 thao tác với tài liệu thực, tính năng này hoàn toàn có thể có ích như thể một phần của những công thức phức tạp hơn mà bạn sử dụng để nghiên cứu và phân tích tài liệu .
- Sử dụng INDEX với các hàm Excel khác (SUM, AVERAGE, MAX, MIN)
Từ những ví dụ trước, bạn hoàn toàn có thể nghĩ rằng công thức INDEX trong Excel trả về những giá trị, nhưng thực tiễn là nó trả về một tham chiếu đến ô có chứa giá trị. Và ví dụ này biểu lộ thực chất thực sự của hàm INDEX .
Với tác dụng của một công thức INDEX mang tính tham chiếu, tất cả chúng ta hoàn toàn có thể sử dụng nó trong những 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 phương pháp công thức INDEX quản lý và vận hành tỏ ra quá phức tạp, nhưng nó có những ứng dụng thực tiễn, như trong những 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 lập số lượng giới hạn trên và số lượng 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 tiên phong và mục sau cuối bạn muốn .
Ví dụ : công thức sau trả về tổng những giá trị trong cột Diameter ( đường kính ) giữa hai tài liệu được chỉ định trong ô B1 và B2 :
= SUM (INDEX (SourceData [Diameter], B1): INDEX (SourceData [Diameter], B2))
- Công thức INDEX để tạo các dải động và danh sách theo mục
Điều này rất thường xảy ra. Khi bạn mở màn tổ chức triển khai tài liệu trong một bảng tính, bạn hoàn toàn có thể không biết có bao nhiêu mục bạn sẽ có .
Dù sao thì, nếu bạn có số những mục trong một cột đã cho đổi khác, từ A1 đến An, và bạn hoàn toàn có thể muốn tạo ra một dải được đặt tên – gồm có toàn bộ những ô có tài liệu. Tại đó, bạn muốn dải được kiểm soát và điều chỉnh tự động hóa khi bạn thêm mục mới hoặc xóa một số mục hiện có. Ví dụ : nếu bạn hiện có 10 mục, dải được đặt tên của bạn sẽ là A1 : A10. Nếu bạn thêm một mục nhập mới, khoanh vùng phạm vi tên sẽ tự động hóa lan rộng ra lên A1 : A11 và nếu bạn đổi khác dự tính và xóa dữ liệu mới được thêm vào, khoanh vùng phạm vi sẽ tự động hóa quay trở lại A1 : A10 .
Lợi ích chính của cách tiếp cận này là bạn không phải liên tục update tổng thể những công thức trong bảng tính của bạn để bảo vệ chúng tham chiếu đến những dải một cách đúng chuẩn .
Một cách để xác lập một dải động được sử dụng hàm OFFSET :
= OFFSET (Sheet_Name! $ A $ 1, 0, 0, COUNTA (Sheet_Name! $A :$A ), 1)
Một giải pháp hoàn toàn có thể khác là sử dụng Excel INDEX cùng với COUNTA :
= Sheet_Name! $ A $ 1: INDEX (Sheet_Name! $ A: $ A, COUNTA (Sheet_Name! $A :$A ))
Trong cả hai công thức, A1 là ô có chứa mục tiên phong của list và dải động được tạo ra bởi cả hai công thức sẽ giống hệt nhau .
Sự khác biệt ở đây chính là cách tiếp cận. Hàm INDEX tìm thấy một ô ở giao điểm của một hàng và cột cụ thể, trong khi hàm OFFSET di chuyển từ điểm làm mốc bởi một số hàng và / hoặc các cột nhất định. Hàm COUNTA, được sử dụng trong cả hai công thức, tính số lượng các ô không rỗng trong cột và đảm bảo rằng chỉ những ô có dữ liệu được bao gồm trong dải đã đặt tên.
Hình sau đây sẽ cho bạn thấy cách bạn hoàn toàn có thể sử dụng công thức Index để tạo một list tùy chọn .
Mẹo: Cách dễ nhất để tạo một danh sách tùy chọn được cập nhật tự động trong Excel là tạo một danh sách (đã đặt tên) và được liệt kê dựa trên một bảng. Trong trường hợp này, bạn sẽ không cần bất kỳ công thức phức tạp nào vì các bảng Excel là dải động.
- Sức mạnh của các hàm Vlookup khi kết hợp với INDEX / MATCH:
Thực hiện các hàm Vlookup theo chiều dọc trong Excel – đây là nơi mà hàm INDEX thực sự tỏa sáng. Nếu bạn đã từng thử sử dụng hàm VLOOKUP, bạn sẽ nhận thức được nhiều hạn chế của nó, chẳng hạn như không có khả năng kéo giá trị từ các cột qua bên trái của cột tra cứu hoặc giới hạn 255 ký tự cho một giá trị tra cứu. Và vì thế cho nên kết hợp index và match là giải pháp mà nhiều người lựa chọn để thay thê
Xem thêm: Sự kết hợp hàm INDEX và MATCH trong Excel – Sự thay thế tốt hơn cả hàm VLOOKUP
Xem thêm :
Hàm INDEX / MATCH tốt hơn VLOOKUP ở nhiều khía cạnh:
- Không có vấn đề với dò tìm bên trái.
- Không giới hạn kích thước giá trị tra cứu.
- Không yêu cầu phân loại (VLOOKUP với kết quả tương đối khi yêu cầu sắp xếp cột tra cứu theo thứ tự tăng dần).
- Bạn được tự do chèn và loại bỏ các cột trong một bảng mà không cần cập nhật mỗi công thức liên quan.
Và cuối cùng nhưng không kém phần quan trọng, INDEX / MATCH không làm chậm Excel của bạn như nhiều hàm Vlookup đã làm.
Bạn sử dụng INDEX / MATCH – hàm index kết hợp match theo cách sau:
= INDEX (column to return a value from, (MATCH (lookup value, column to lookup against, 0))
Ví dụ : nếu tất cả chúng ta mở bảng tài liệu gốc của tất cả chúng ta ra với tên hành tinh ( planet name ) trở thành cột tiên phong bên phải, thì hàm công thức INDEX / MATCH vẫn lấy giá trị tương thích từ cột bên trái mà không gặp bất kỳ trở ngại nào .
- Sử dụng hàm INDEX để lấy 1 dải từ danh sách các dải:
Một cách sử dụng mưu trí và hữu hiệu hàm INDEX trong Excel là bạn hoàn toàn có thể có được một dải từ một list những dải .
Giả sử, bạn có nhiều list với số lượng những mục khác nhau .
Trước hết, bạn đặt tên cho mỗi dải trong mỗi danh sách; Ví như có thể đặt là PlanetsD và MoonsD trong ví dụ này:
Tuy nhiên, bảng Moons còn chưa hoàn chỉnh, có 176 mặt trăng tự nhiên được biết đến trong Hệ Mặt trời của chúng ta, Jupiter có 63 và vẫn còn nhiều hơn nữa. Đối với ví dụ này, tôi chọn 11 cái ngẫu nhiên.
Giả sử rằng PlanetsD là dải 1 của bạn và MoonsD nằm trong dải 2 và ô B1 là nơi bạn đặt số dãy, bạn có thể sử dụng công thức Index sau để tính giá trị trung bình của các giá trị trong dải ô được chọn:
= AVERAGE (INDEX ((PlanetsD, MoonsD),,, B1))
Hãy quan tâm rằng giờ đây tất cả chúng ta đang sử dụng định dạng tham chiếu của hàm INDEX, và số trong đối số ở đầu cuối ( area_num ) cho công thức biết khoanh vùng phạm vi dải nào được lựa chọn .
Trong hình bên dưới, area_num (cell B1) được đặt thành 2, trong công thức tính đường kính trung bình của Moons vì dải MoonsD đứng thứ 2 trong đối số tham chiếu.
Nếu bạn thao tác với nhiều list và không muốn nhớ những số tương quan, bạn hoàn toàn có thể sử dụng thêm hàm IF để làm điều này cho bạn :
= AVERAGE (INDEX ((PlanetsD, MoonsD),,, IF (B1 = “planets”, 1, IF (B1 = “moon”, 2))))
Trong hàm IF, bạn sử dụng một số tên danh sách đơn giản và dễ nhớ mà bạn muốn người dùng nhập vào ô B1 thay vì số. Xin lưu ý rằng, để công thức làm việc chính xác, văn bản trong B1 phải chính xác như nhau (không phân biệt chữ hoa chữ thường) như trong các thông số của IF, nếu không công thức Index của bạn sẽ xuất ra #VALUE lỗi.
Thậm chí, để công thức thân thiện hơn, bạn có thể sử dụng Excel Data Validation để tạo danh sách tùy chọn để tránh lỗi chính tả và lỗi trang in:
Cuối cùng, để làm cho công thức INDEX của bạn hoàn toàn hoàn hảo, bạn có thể đặt nó trong hàm IFERROR sẽ nhắc người dùng chọn một mục từ danh sách theo mục, nếu không có lựa chọn nào được đưa ra:
= IFERROR (AVERAGE (INDEX ((PlanetsD, MoonsD),,, IF (B1 = “planet”, 1, IF (B1 = “moon”, 2)))), “Please select the list!”)
Trên đây là hướng dẫn cách bạn sử dụng công thức INDEX trong Excel. Tôi hy vọng những ví dụ này cho bạn vài cách để khai thác tiềm năng của hàm INDEX trong Excel trong bảng tính của bạn. Cảm ơn bạn đã đọc!
Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Những hàm nâng cao giúp áp dụng tốt vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table…
Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học:
Để hoàn toàn có thể ứng dụng tốt Excel vào trong việc làm, tất cả chúng ta không chỉ nắm vững được những hàm mà còn phải sử dụng tốt cả những công cụ của Excel. Những hàm nâng cao giúp vận dụng tốt vào việc làm như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH … Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table … Toàn bộ những kỹ năng và kiến thức này những bạn đều hoàn toàn có thể học được trong khóa học : EX101 – Excel từ cơ bản đến nâng cao dành cho người đi làm
Source: https://final-blade.com
Category : Kiến thức Internet
