Trong bài viết này, Học Excel Online đang lấy lấy một ví dụ về hàm INDEX trong Excel, qua đó mô tả cho bạn những giải pháp sử dụng hiệu quả công cố kỉnh này.

Bạn đang xem: Cách dùng hàm index trong excel

Trong toàn bộ các tính năng của Excel mà sức mạnh của bọn chúng thường bị nhận xét thấp cùng không được áp dụng đúng cách, thì INDEX chắc chắn sẽ xếp hạng ở đâu đó trong đứng top 10. Vì hàm này cực kỳ nhạy và rất có thể kết hợp với các hàm không giống nữa nhằm giải quyết được nhiều vấn đề để 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 vào một mảng hoặc một dải độc nhất vô nhị định. Nói cách khác, bạn áp dụng INDEX khi chúng ta biết (hoặc hoàn toàn có thể tính toán) địa chỉ của 1 phần tử trong dải và bạn muốn nhận cực hiếm thực của thành phần đó.

Điều này nghe dường như tầm thường, nhưng khi chúng ta nhận ra tiềm năng thực thụ của hàm INDEX, thì nó có thể tạo bắt buộc những chuyển đổi quan trọng đối với cách mà chúng ta tính toán, so sánh và trình diễn dữ liệu trong những bảng tính của bạn.


Xem nhanh


Hàm INDEX – cú pháp và thực hiện cơ bảnINDEX thao tác làm việc với mảngCấu trúc hàm

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

Có nhì phiên bản của hàm INDEX trong Excel – định dạng dải cùng định dạng tham chiếu. Và cả hai chủng loại đều hoàn toàn có thể được sử dụng trong tất cả các phiên bạn dạng của Microsoft Excel 2013, Excel 2010, Excel 2007 và 2003.

INDEX làm việc với mảng

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

Cấu trúc hàm

INDEX (array, row_num, )


*

*

array – là một dãi các ô, có tên dải hoặc bảng. row_num – là hàng máy 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 lắp thêm mấy nhằm trả về một giá chỉ trị. Ví như column_num bị vứt qua, row_num là bắt buộc.

Ví dụ, phương pháp “= INDEX (A1: D6, 4, 3)” đã trả về quý hiếm tại giao điểm của hàng thiết bị 4 cùng cột vật dụng 3 trong mảng A1: D6, là quý hiếm trong ô C4.

Để có phát minh về cách hàm INDEX vận động trên tài liệu thực, hãy xem lấy một ví dụ sau:

*

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

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

Dạng mảng cùng INDEX – điều cần nhớNếu đối số mảng chỉ gồm 1 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 hàm nhiều hơn một loại 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 cục cột. Tương tự, nếu mảng bao gồm nhiều cột cùng đối số column_num bị làm lơ hoặc bởi 0, cách làm INDEX đã trả về toàn thể hàng. Dưới đây là một lấy ví dụ như về phương pháp minh họa.Các đối số row_num với column_num yêu cầu tham chiếu mang đến một ô vào mảng; trường hợp không, phương pháp 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ới cột đang xác định.

Cấu trúc hàm

INDEX (reference, row_num, , )

reference – là 1 trong hoặc các dải.

Nếu chúng ta nhập nhiều hơn nữa một phạm vi, hãy bóc tách các khoảng bằng vệt phẩy và cố nhiên đối số tham chiếu trong ngoặc đơn, ví dụ (A1:B5,D1:F5).

Nếu mỗi hàng trong tài liệu tham khảo chỉ cất một 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ố vật dụng tự của hàng nằm trong vòng mà từ kia trả về một ô tham chiếu, nó giống như 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ừ bỏ như dạng mảng. area_num – một tham số tùy chọn chỉ ra rằng mảng trường đoản cú đối số tham chiếu để sử dụng. Nếu vứt qua, bí quyết INDEX đang trả lại tác dụng cho mảng đầu tiên được liệt kê trong list tham khảo.

Ví dụ, phương pháp = INDEX ((A2: D3, A5: D7), 3, 4, 2) trả về quý hiếm của ô D7, trên giao điểm của hàng thiết bị 3 với cột trang bị 4 trong khu vực thứ nhì (A5: D7).

*

Định dạng tham chiếu INDEX – những vấn đề cần nhớNếu đối số row_num hoặc column_num được để thành 0, thì một công thức Excel INDEX trả về tham chiếu cho tổng thể cột hoặc hàng tương ứng.Nếu cả hai row_num column_num bị quăng quật qua, hàm INDEX trả về vùng được hướng dẫn và chỉ định trong đối số area_num.Tất cả những _num arguments (row_num, column_num và area_num) phải xem thêm một ô trong tham chiếu; giả dụ không, phương pháp INDEX vẫn trả về #REF! lỗi.

Cả hai cách làm INDEX chúng ta đã bàn thảo cho đến thời điểm này đều rất đơn giản và dễ dàng và chỉ minh hoạ mang lại khái niệm. Phương pháp thực sự của chúng ta có thể phức tạp hơn những so với đó, bởi vậy, hãy cùng mày mò một vài ba sử dụng kết quả nhất của INDEX trong Excel.

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

Cách cần sử dụng hàm INDEX trong Excel- ví dụ, công thức

Có thể nó không có tương đối nhiều ứng dụng thực tế, nhưng lại nếu kết phù hợp với các hàm khác như MATCH giỏi COUNTA, nó có thể tạo những công thức khôn cùng mạnh.

Nguồn dữ liệu (SourceData)

Tất cả các công thức Excel INDEX của cửa hàng chúng tôi (ngoại trừ công thức cuối cùng), shop 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 các bảng hoặc các mảng chọn cái tên có thể làm cho công thức dài hơn nữa một tí, tuy nhiên nó cũng khiến cho chúng linh hoạt cùng dễ đọc hơn. Để đ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ỉ việc chỉnh sửa một chiếc tên duy nhất.

Tất nhiên, không có gì rào cản bạn sử dụng mảng thông thường nếu khách hàng muốn. Trong trường phù hợp này, bạn chỉ việc thay cố gắng tên bảng SourceData bởi tham chiếu mảng say đắm hợp.

Lấy dữ liệu tại phần thứ N trường đoản cú danh sách

Đây là cách áp dụng cơ bạn dạng và là lấy một ví dụ về hàm INDEX dễ hiểu nhất. Để lấy một tài liệu nhất định vào danh sách, bạn chỉ cần viết = INDEX (range, n), trong số đó range là một trong những dải ô hoặc dải ô được đặt tên, với n là vị trí của tài liệu mà bạn muốn nhận.

Xem thêm: Cách Dùng Hoa Đâu Biếc - Cách Sử Dụng Hoa Đậu Biếc Khô Hoặc Tươi

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

*

Để nhận cực hiếm của ô sinh hoạt giao điểm của một hàng và cột vẫn cho, bạn áp dụng cách tiếp cận tương tự như với sự biệt lập duy tuyệt nhất là bạn thực hiện cả nhì – số hàng với số cột.

Và đó là một lấy một ví dụ khác: trong bảng tính mà chúng tôi lấy làm cho ví dụ, nhằm tìm ra địa cầu lớn thứ hai trong hệ khía cạnh trời, bạn bố trí bảng theo cột Đường kính (Diameter) và áp 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 lấy một ví dụ này. Row_num là 2 vì bạn đang tìm kiếm mục vật dụng hai trong danh sách Column_num là 3 vì chưng Diameter ở vị trí cột trang bị 3 trong bảng.

Nếu bạn muốn trả lại tên của địa cầu thay bởi đường kính, thì bạn phải biến hóa column_num thành 1. Và tự nhiên, bạn cũng có thể sử dụng một tham chiếu ô vào đối số row_num và/hoặc column_num để gia công cho công thức INDEX của người tiêu dùng linh hoạt hơn, như được biểu đạt trong hình mặt dưới:

*

thừa nhận được toàn bộ các quý giá trong một mặt hàng hoặc cột

Ngoài việc mang ra một ô duy nhất, hàm INDEX hoàn toàn có thể trả về một dải những giá trị từ cục bộ các sản phẩm hoặc các cột. Để lấy toàn bộ các giá chỉ trị xuất phát điểm từ 1 cột độc nhất định, các bạn phải bỏ lỡ đối số row_num hoặc để nó là 0. Tương tự, để sở hữu được toàn cục hàng, bạn bỏ qua hoặc 0 trong column_num.

Các bí quyết INDEX vậy nên khó có thể được sử dụng chính vì Excel ko thể phù hợp với dải những giá trị được trả về bởi phương pháp trong một ô duy nhất, mà nắm vào kia thì bạn sẽ nhận được #VALUE! lỗi. Tuy nhiên, nếu như khách hàng sử dụng INDEX kết phù hợp với các hàm trong Excel khác, ví dụ như SUM hoặc AVERAGE, bạn sẽ thu được tác dụng tuyệt vời.

Ví dụ, bạn có thể sử dụng phương pháp Index Excel sau để tính ánh nắng mặt trời trung bình của địa cầu trong hệ mặt trời:

= AVERAGE (INDEX (SourceData,, 4))

Trong phương pháp trên, đối số column_num là 4 vì Temperature là cột thứ 4 vào bảng của chúng ta. Thông số row_num bị quăng quật qua.

*

Tương từ bỏ như vậy, chúng ta cũng có thể tìm được ánh sáng tối thiểu và buổi 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ừ cách nhìn thực tế, hàm INDEX trong công thức trên là không đề nghị thiết. Bạn chỉ cần viết = AVERAGE (dải) hoặc = SUM (dải) với cũng nhận được công dụng tương tự.

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

sử dụng INDEX với những hàm Excel khác (SUM, AVERAGE, MAX, MIN)

Từ các ví dụ trước, bạn cũng có thể nghĩ rằng phương pháp INDEX vào Excel trả về những giá trị, nhưng thực tiễn là nó trả về một tham chiếu đến ô gồm chứa giá bán trị. Với ví dụ này thể hiện bản chất thực sự của hàm INDEX.

Với công dụng của một phương pháp INDEX mang tính chất tham chiếu, chúng ta cũng có thể sử dụng nó trong số hàm không giống để tạo thành một dải động. Khó khăn hiểu ư? Công thức dưới đây sẽ làm rõ mọi thứ:

Giả sử bạn có một công thức = AVERAGE (A1: A10) trả về cực hiếm trung bình của các ô A1: A10. Thay vày viết dải thẳng như vào công thức, thì bạn có thể thay vắt cả A1 hoặc A10 hoặc cả hai bằng những hàm INDEX như sau:

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

Cả hai cách làm trên sẽ có lại kết quả tương tự bởi vì hàm INDEX cũng trả về một tham chiếu mang đến ô A10 (row_num được đặt là 10, col_num bỏ qua). Sự biệt lập là công thức AVERAGE / INDEX sử dụng mang đến dải cồn và khi bạn chuyển đổi đối số row_num trong INDEX, thì dải mà được xử lý vị hàm AVERAGE sẽ thay đổi và phương pháp sẽ trả về một tác dụng khác.

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

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

Giả sử bạn có nhu cầu biết đường kính trung bình của n hành tinh lớn nhất trong khối hệ thống của bọn chúng tôi. Do vậy, bạn bố trí bảng theo cột Diameter từ lớn nhất đến nhỏ tuổi nhất và sử dụng công thức Average / Index sau:

= AVERAGE(C5: INDEX (SouceData , B1))

 

*

Ví dụ 2. Tính tổng các giá trị thân 2 mục thay thể:

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

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

= SUM (INDEX (SourceData , B1): INDEX (SourceData , B2)) 

 

*

bí quyết INDEX nhằm tạo các dải cồn và list theo mục

Điều này cực kỳ thường xảy ra. Lúc bạn bước đầu tổ chức tài liệu trong một bảng tính, bạn có thể không biết bao gồm bao nhiêu mục bạn sẽ có.

Dù sao thì, nếu bạn có số những mục vào một cột đang cho thay đổi, tự A1 mang lại An, và bạn cũng có thể muốn tạo ra một dải được đặt tên – bao gồm tất cả những ô tất cả dữ liệu. Tại đó, bạn muốn dải được điều chỉnh tự động hóa khi các bạn thêm mục bắt đầu hoặc xóa một số trong những mục hiện tại có. Ví dụ: nếu khách hàng hiện bao gồm 10 mục, dải chọn cái tên của các bạn sẽ là A1: A10. Nếu như khách hàng thêm một mục nhập mới, phạm vi tên sẽ tự động hóa mở rộng lớn lên A1: A11 và nếu bạn chuyển đổi ý định với xóa dữ liệu mới nhận thêm vào, phạm vi sẽ tự động hóa quay trở về A1: A10.

Lợi ích chính của biện pháp tiếp cận này là bạn chưa phải liên tục cập nhật tất cả những công thức vào bảng tính của người tiêu dùng để bảo đảm an toàn chúng tham chiếu đến các dải một cách bao gồm xác.

Một phương pháp để xác định 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 chiến thuật có thể không giống là sử dụng Excel INDEX cùng rất COUNTA:

= Sheet_Name! $ A $ 1: INDEX (Sheet_Name! $ A: $ A, COUNTA (Sheet_Name! $A :$A ))

Trong cả hai công thức, A1 là ô tất cả chứa mục thứ nhất của danh sách và dải rượu cồn được tạo ra bởi cả hai công thức sẽ giống hệt nhau.

Sự biệt lập ở đây đó là cách tiếp cận. Hàm INDEX search thấy một ô sống giao điểm của một hàng với cột nắm thể, trong lúc hàm OFFSET dịch rời từ điểm có tác dụng mốc bởi một vài hàng với / hoặc những cột nhất định. Hàm COUNTA, được sử dụng trong cả nhì công thức, tính con số các ô không rỗng vào cột và bảo đảm an toàn rằng chỉ đa số ô có tài liệu được bao hàm trong dải vẫn đặt tên.

Hình sau đây sẽ cho mình thấy cách chúng ta cũng có thể sử dụng công thức Index để chế tạo một danh sách tùy chọn.

*

Mẹo: Cách dễ nhất để chế tạo một danh sách tùy tuyển chọn được cập nhật tự động trong Excel là tạo ra một danh sách (đã để tên) với được liệt kê dựa trên một bảng. Vào trường thích hợp này, các bạn sẽ không cần bất kỳ công thức tinh vi nào vì những 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 những hàm Vlookup theo chiều dọc trong Excel – đó là nơi mà hàm INDEX thực sự tỏa sáng. Nếu khách hàng đã từng thử thực hiện hàm VLOOKUP, các bạn sẽ nhận thức được nhiều hạn chế của nó, ví dụ như không có tác dụng kéo quý hiếm 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ý kết tự cho 1 giá trị tra cứu. Và vì thế cho cần kết vừa lòng index với match là giải pháp mà không ít người lựa lựa chọn để nuốm thê

Hàm INDEX / MATCH giỏi hơn VLOOKUP ở các khía cạnh:

Không có vấn đề với dò tìm mặt trái.Không giới hạn kích thước giá trị tra cứu.Không yêu mong phân một số loại (VLOOKUP với công dụng tương đối khi yêu cầu sắp xếp cột tra cứu giúp theo sản phẩm tự tăng dần).Bạn được thoải mái chèn và vứt bỏ các cột vào một bảng mà lại không cần update mỗi công thức liên quan.

Và ở đầu cuối nhưng không thua kém phần quan liêu trọng, INDEX / MATCH không làm chậm rì rì Excel của người sử dụng như những hàm Vlookup đang làm.

Bạn thực hiện INDEX / MATCH – hàm index kết hợp match theo cách sau:

= INDEX (column khổng lồ return a value from, (MATCH (lookup value, column to lookup against, 0))

Ví dụ: nếu chúng ta mở bảng tài liệu gốc của bọn họ ra với tên hành tinh (planet name) đổi thay cột trước tiên bên phải, thì hàm cách làm INDEX / MATCH vẫn đem giá trị tương xứng từ cột phía bên trái mà không gặp bất kì trở ngại nào.

*

Khóa học tập excel kế toán tài chính online

sử dụng hàm INDEX để đưa 1 dải từ danh sách các dải:

Một cách sử dụng thông minh và bổ ích hàm INDEX trong Excel là chúng ta cũng có thể có được một dải từ một danh sách các dải.

Giả sử, bạn có rất nhiều danh sách với số lượng các mục khác nhau.

Trước hết, chúng ta đặt tên cho mỗi dải trong những danh sách; Ví như hoàn toàn có thể đặt là PlanetsD với MoonsD trong lấy một ví dụ này:

*

Tuy nhiên, bảng Moons còn không hoàn chỉnh, bao gồm 176 mặt trăng tự nhiên được nghe biết trong Hệ khía cạnh trời của bọn chúng ta, dòng jupiter có 63 với vẫn còn nhiều hơn thế nữa nữa. Đối với ví dụ như này, tôi chọn 11 chiếc ngẫu nhiên.

Giả sử rằng PlanetsD là dải 1 của người sử dụng và MoonsD bên trong dải 2 cùng ô B1 là nơi bạn đặt số dãy, bạn cũng có thể sử dụng bí quyết Index sau để tính quý giá trung bình của các giá trị vào dải ô được chọn:

= AVERAGE (INDEX ((PlanetsD, MoonsD),,, B1))

Hãy xem xét rằng hiện nay chúng ta đang áp dụng định dạng tham chiếu của hàm INDEX, với số trong đối số cuối cùng (area_num) cho cách làm biết phạm vi dải như thế nào được lựa chọn.

Trong hình mặt dưới, area_num (cell B1) được đặt thành 2, trong cách làm tính 2 lần bán kính trung bình của Moons bởi dải MoonsD đứng vị trí thứ 2 trong đối số tham chiếu.

*

Nếu bạn thao tác với nhiều list và không thích nhớ những số liên quan, chúng ta có thể sử dụng thêm hàm IF để gia công điều này mang lại bạn:

= AVERAGE (INDEX ((PlanetsD, MoonsD),,, IF (B1 = “planets”, 1, IF (B1 = “moon”, 2))))

Trong hàm IF, chúng ta sử dụng một vài tên danh sách dễ dàng và dễ nhớ mà bạn có nhu cầu người sử dụng nhập vào ô B1 thay bởi vì số. Xin xem xét rằng, nhằm công thức thao tác chính xác, văn bản trong B1 phải đúng mực như nhau (không rõ ràng chữ hoa chữ thường) như vào các thông số kỹ thuật của IF, còn 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 mật hơn, chúng ta có thể sử dụng Excel Data Validation để tạo list tùy lựa chọn để kiêng lỗi thiết yếu tả và lỗi trang in:

*

Cuối cùng, để làm cho công thức INDEX của bạn trọn vẹn hoàn hảo, chúng ta cũng có thể đặt nó trong hàm IFERROR đang nhắc người dùng lựa chọn 1 mục từ danh sách theo mục, nếu không có lựa lựa chọn nào được gửi 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 bí quyết bạn sử dụng công thức INDEX vào Excel. Tôi mong muốn những ví dụ này cho mình vài cách để khai thác tiềm năng của hàm INDEX trong Excel vào bảng tính của bạn. Cảm ơn chúng ta đã đọc!

Để có thể ứng dụng xuất sắc Excel vào vào công việc, họ không chỉ nắm rõ được những hàm nhưng mà còn bắt buộc sử dụng giỏi cả các công ráng của Excel. Các hàm nâng cao giúp áp dụng giỏi vào các bước như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những dụng cụ thường thực hiện là Data validation, Conditional formatting, Pivot table…