Trong bài viết này, Học Excel Online vẫn lấy lấy ví dụ như về hàm INDEX vào Excel, thông qua đó mô tả cho chính mình những cách sử dụng kết quả công thế này.

Bạn đang xem: Cách dùng lệnh index trong excel

Trong toàn bộ các tác dụng của Excel mà sức mạnh của chúng thường bị review thấp và không được sử dụng đúng cách, thì INDEX chắc hẳn rằng sẽ xếp hạng chỗ nào đó trong top 10. Do hàm này hết sức nhạy và hoàn toàn có thể kết phù hợp với các hàm khác nữa nhằm giải quyết được nhiều vấn đề để ra.

Vậy hàm INDEX vào 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 tuyệt nhất định. Nói biện pháp khác, bạn áp dụng INDEX khi chúng ta biết (hoặc có thể tính toán) địa chỉ của 1 phần tử vào dải và bạn có nhu cầu nhận giá trị thực của bộ phận đó.

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


Xem nhanh


Hàm INDEX – cú pháp và sử dụng cơ bảnINDEX thao tá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 dạng của hàm INDEX trong Excel – định dạng dải cùng định dạng tham chiếu. Cùng cả hai mẫu mã đều rất có thể được áp dụng trong tất cả các phiên phiên bản của Microsoft Excel 2013, Excel 2010, Excel 2007 và 2003.

INDEX thao tá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 vào số hàng cùng cột mà các bạn chỉ định.

Cấu trúc hàm

INDEX (array, row_num, )


*

*

array – là 1 dãi những ô, mang tên dải hoặc bảng. row_num – là hàng máy mấy vào mảng mà bạn có nhu cầu được trả về một giá chỉ trị. Trường hợp row_num bị bỏ qua, column_num là bắt buộc. column_num – là cột sản phẩm mấy để trả về một giá trị. Nếu như column_num bị bỏ qua, row_num là bắt buộc.

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

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

*

Thay bởi nhập số hàng cùng cột vào công thức, chúng ta cũng có thể cung cấp cho tham chiếu ô để sở hữu đượ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 hoàn hảo ($B$2:$D$6) thay vì chưng tham chiếu tương đối (B2:D6) vào đối số mảng giúp bạn dễ dàng coppy công thức lên những ô khác. Ngoài ra, chúng ta 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 và INDEX – vấn đề cần nhớNếu đối số mảng chỉ bao gồm 1 hàng hoặc cột, thì bạn cũng 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 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ự, trường hợp mảng bao gồm nhiều cột cùng đối số column_num bị bỏ qua mất hoặc bằng 0, cách làm INDEX vẫn trả về cục 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 cùng column_num bắt buộc tham chiếu cho một ô trong mảng; ví như không, cách làm INDEX đã 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 trên giao điểm của hàng cùng cột vẫn xác định.

Cấu trúc hàm

INDEX (reference, row_num, , )

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

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

Nếu mỗi dãy trong tài liệu xem thêm chỉ đựng một hàng hoặc cột, đối số row_num hoặc column_num tương xứng là tùy chọn.

row_num – số thứ tự của hàng nằm trong vòng mà từ đó trả về một ô tham chiếu, nó tựa như như dạng mảng. column_num – số máy 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 thông số tùy chọn chỉ ra rằng mảng trường đoản cú đối số tham chiếu nhằm sử dụng. Nếu bỏ qua, công thức INDEX đã trả lại tác dụng 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 trang bị 3 với 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 để thành 0, thì một cách làm Excel INDEX trả về tham chiếu cho toàn bộ cột hoặc hàng tương ứng.Nếu cả nhị row_num column_num bị bỏ qua, hàm INDEX trả về vùng được chỉ định và hướng dẫn trong đối số area_num.Tất cả những _num arguments (row_num, column_num cùng area_num) phải tham khảo một ô trong tham chiếu; ví như không, bí quyết INDEX đã trả về #REF! lỗi.

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

Các hàm thường được sử 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ó khá nhiều ứng dụng thực tế, tuy thế nếu kết hợp với các hàm khác như MATCH hay COUNTA, nó hoàn toàn có thể tạo các công thức cực kỳ 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ừ phương pháp cuối cùng), công ty chúng tôi sẽ sử dụng tài liệu dưới đây. Để thuận tiện, nó được tổ chức trong một bảng mang tên SourceData.

*

Việc sử dụng những bảng hoặc các mảng được đặt tên có thể tạo nên công thức dài hơn nữa một tí, dẫu vậy nó cũng khiến cho chúng linh hoạt cùng dễ phát âm hơn. Để điều chỉnh ngẫu nhiên 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 cái tên duy nhất.

Tất nhiên, không tồn tại gì bức tường ngăn bạn thực hiện mảng thông thường nếu bạn muốn. Trong trường phù hợp này, bạn chỉ cần thay vậy tên bảng SourceData bởi tham chiếu mảng phù hợp hợp.

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

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

Xem thêm: Review Chi Tiết 15 Khách Sạn Phố Cổ Hội An Gần Phố Cổ Đẹp Mà Giá Dưới 800K

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

*

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

Và đó là một ví dụ như khác: vào bảng tính mà công ty chúng tôi lấy có tác dụng ví dụ, để tìm ra toàn 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à 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 xứng với SourceData trong lấy ví dụ này. Row_num là 2 vì ai đang tìm kiếm mục trang bị hai trong danh sách Column_num là 3 bởi Diameter tại phần cột thứ 3 vào bảng.

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

*

dấn được toàn bộ các cực hiếm trong một mặt hàng hoặc cột

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

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

Ví dụ, bạn cũng có thể sử dụng phương pháp Index Excel sau để tính ánh sáng trung bình của thế giới trong hệ khía cạnh trời:

= AVERAGE (INDEX (SourceData,, 4))

Trong phương pháp trên, đối số column_num là 4 do Temperature là cột sản phẩm công nghệ 4 trong bảng của bọn chúng ta. Thông số kỹ thuật row_num bị quăng quật qua.

*

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

= MAX (INDEX (SourceData,, 4))

= MIN (INDEX (SourceData,, 4))

Và tính tổng trọng lượng hành tinh (Mass là cột thứ hai 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 đề xuất thiết. Bạn chỉ việc viết = AVERAGE (dải) hoặc = SUM (dải) với cũng nhấn được tác dụng tương tự.

Khi thao tác làm việc với tài liệu thực, nhân kiệt này rất có thể hữu ích như là một phần của những công thức phức tạp hơn mà lại bạn áp dụng để phân tích dữ 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 có thể nghĩ rằng phương pháp INDEX vào Excel trả về các giá trị, nhưng thực tiễn là nó trả về một tham chiếu mang lại ô bao gồm chứa giá bán trị. Cùng ví dụ này thể hiện thực 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 ý nghĩa tham chiếu, bạn cũng có thể sử dụng nó trong các hàm khác để tạo thành một dải động. Khó hiểu ư? Công thức tiếp sau đây sẽ làm rõ mọi thứ:

Giả sử các bạn có một phương pháp = AVERAGE (A1: A10) trả về giá trị trung bình của những ô A1: A10. Thay do viết dải trực tiếp như trong công thức, thì chúng ta có thể thay cầm 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ách làm trên sẽ với lại kết quả tương tự chính vì hàm INDEX cũng trả về một tham chiếu cho ô A10 (row_num được đặt là 10, col_num bỏ qua). Sự khác biệt là bí quyết AVERAGE / INDEX sử dụng cho dải động và lúc bạn thay đổi đối số row_num trong INDEX, thì dải mà lại được xử lý bởi vì hàm AVERAGE sẽ biến đổi và cách làm sẽ trả về một hiệu 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 vượt phức tạp, nhưng mà nó có những ứng dụng thực tế, như trong các ví dụ sau đây:

Ví dụ 1. Tính trung bình của những 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 số 1 trong hệ thống của chúng tôi. Vì vậy, bạn thu xếp bảng theo cột Diameter từ lớn nhất đến bé dại nhất và thực hiện 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 nắm thể:

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

Ví dụ: phương pháp sau trả về tổng những giá trị trong cột Diameter (đường kính) thân hai dữ liệu được hướng đẫn trong ô B1 và ​​B2:

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

 

*

công thức INDEX nhằm tạo các dải cồn và danh sách theo mục

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

Dù sao thì, nếu như khách hàng có số những mục trong một cột đã cho thay đổi, tự A1 đến An, và bạn có thể muốn tạo ra một dải được đặt tên – bao gồm tất cả các ô bao gồm dữ liệu. Tại đó, bạn có nhu cầu dải được điều chỉnh tự động hóa khi chúng ta thêm mục bắt đầu hoặc xóa một số trong những mục hiện có. Ví dụ: nếu bạn hiện gồm 10 mục, dải được lấy tên của các bạn sẽ là A1: A10. Nếu bạn 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 A1: A11 với nếu bạn biến đổi ý định và xóa tài liệu mới có thêm vào, phạm vi sẽ auto quay quay trở về A1: A10.

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

Một phương pháp để xác định một dải rượu cồn được áp 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à thực hiện Excel INDEX với COUNTA:

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

Trong cả nhị công thức, A1 là ô bao gồm chứa mục trước tiên của list và dải cồn được tạo nên bởi cả hai bí quyết sẽ hệt nhau nhau.

Sự biệt lập ở đây đó là cách tiếp cận. Hàm INDEX kiếm tìm thấy một ô ở giao điểm của một hàng cùng cột cố kỉnh thể, trong những khi hàm OFFSET di chuyển từ điểm có tác dụng mốc bởi một số trong những hàng và / hoặc những cột tuyệt nhất định. Hàm COUNTA, được thực hiện trong cả nhị công thức, tính số lượng các ô ko rỗng trong cột và đảm bảo rằng chỉ rất nhiều ô có tài liệu được bao hàm trong dải sẽ đặt tên.

Hình dưới đây sẽ cho bạn thấy cách bạn cũng có thể sử dụng phương pháp Index để chế tạo một danh sách tùy chọn.

*

Mẹo: Cách đơn giản nhất để tạo một list tùy lựa chọn được cập nhật auto trong Excel là chế tạo ra một list (đã để tên) với được liệt kê dựa vào một bảng. Trong trường đúng theo này, bạn sẽ không cần ngẫu nhiên công thức phức tạp nào vì những bảng Excel là dải động.

sức mạnh của những hàm Vlookup khi kết phù 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à lại hàm INDEX thực thụ tỏa sáng. Nếu như bạn đã từng thử sử dụng hàm VLOOKUP, các bạn sẽ nhận thức được không ít hạn chế của nó, chẳng hạn như không có tác dụng kéo quý giá từ các cột qua phía bên trái của cột tra cứu giúp hoặc giới hạn 255 ký kết tự cho một giá trị tra cứu. Và vì vậy cho phải kết đúng theo index cùng match là giải pháp mà nhiều người lựa chọn để gắng thê

Hàm INDEX / MATCH xuất sắc 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 form size giá trị tra cứu.Không yêu cầu phân nhiều loại (VLOOKUP với tác dụng tương đối lúc 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à vứt bỏ các cột vào một bảng nhưng mà không cần update mỗi phương pháp liên quan.

Và cuối cùng nhưng không kém phần quan tiền trọng, INDEX / MATCH không làm lừ đừ Excel của công ty như những hàm Vlookup đang làm.

Bạn sử dụng INDEX / MATCH – hàm index kết hợp match theo phong cách sau:

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

Ví dụ: nếu chúng ta mở bảng dữ liệu gốc của chúng ta ra với tên trái đất (planet name) biến cột đầu tiên bên phải, thì hàm cách làm INDEX / MATCH vẫn lấy giá trị phù hợp từ cột phía 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 áp dụng thông minh và có lợi hàm INDEX trong Excel là chúng ta có thể có được một dải xuất phát điểm từ 1 danh sách những dải.

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

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

*

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

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

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

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

Trong hình bên dưới, area_num (cell B1) được để 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 vì dải MoonsD đứng vị trí thứ 2 trong đối số tham chiếu.

*

Nếu bạn làm việc với nhiều danh sách và không muốn nhớ những số liên quan, bạn cũng có thể sử dụng thêm hàm IF để triển khai điều này đến 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 số trong những tên danh sách đơn giản dễ dàng và dễ nhớ mà bạn có nhu cầu người dùng nhập vào ô B1 thay vị số. Xin chú ý rằng, để công thức làm việc chính xác, văn phiên bản trong B1 phải chính xác như nhau (không phân minh chữ hoa chữ thường) như vào các thông số của IF, còn nếu như không công thức Index của các bạn sẽ xuất ra #VALUE lỗi.

Thậm chí, để công thức thân thiện hơn, chúng ta có thể sử dụng Excel Data Validation để tạo danh sách tùy chọn để kị lỗi bao gồm tả và lỗi trang in:

*

Cuối cùng, để làm cho bí quyết INDEX của bạn trọn vẹn hoàn hảo, bạn có thể đặt nó trong hàm IFERROR đã nhắc người dùng lựa chọn một mục từ list theo mục, nếu không có lựa 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 phương pháp bạn sử dụng công thức INDEX trong Excel. Tôi hy vọng những lấy ví dụ này cho mình vài phương pháp để 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 các bạn đã đọc!

Để hoàn toàn có thể ứng dụng xuất sắc Excel vào vào công việc, bọn họ không chỉ nắm rõ được những hàm nhưng mà còn nên sử dụng tốt cả các công thế của Excel. Phần đa hàm cải thiện giúp áp dụng tốt vào các bước như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những hình thức thường sử dụng là Data validation, Conditional formatting, Pivot table…