Hàm Index Trong Excel

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

Bạn đang xem: Hàm index trong excel

Trong toàn bộ các tính năng của Excel mà sức khỏe của bọn chúng thường bị review thấp cùng không được áp dụng đúng cách, thì INDEX chắc chắn rằng sẽ xếp hạng nơi đâu đó trong vị trí cao nhất 10. Vày hàm này vô cùng nhạy và rất có thể kết phù hợp với các hàm khác nữa để giải quyết được rất nhiều vấn đề đặt ra.

Vậy hàm INDEX vào Excel là gì? Về cơ bản, một hàm INDEX đã trả về một ô tham chiếu vào một mảng hoặc một dải độc nhất định. Nói giải pháp khác, bạn thực hiện INDEX khi chúng ta biết (hoặc rất 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 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 biến đổi quan trọng so với cách mà bạn tính toán, phân tích và trình bày dữ liệu trong những 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ó hai phiên bản của hàm INDEX trong Excel – định dạng dải với định dạng tham chiếu. Với cả hai mẫu đều có thể được thực hiện 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 làm việc với mảng

Dạng mảng INDEX trả về quý hiếm 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 cùng cột mà bạn chỉ định.

Cấu trúc hàm

INDEX (array, row_num, )


*

*

array – là một trong những dãi các ô, mang tên dải hoặc bảng. row_num – là hàng lắp thêm mấy vào mảng mà bạn muốn được trả về một giá bán trị. Giả dụ row_num bị quăng quật qua, column_num là bắt buộc. column_num – là cột trang bị mấy để trả về một giá trị. Trường hợp column_num bị vứt qua, row_num là bắt buộc.

Ví dụ, công thức “= INDEX (A1: D6, 4, 3)” đã trả về giá trị tại giao điểm của hàng sản phẩm 4 cùng cột sản phẩm 3 trong mảng A1: D6, là quý giá 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 ví dụ sau:

*

Thay do nhập số hàng cùng cột trong công thức, bạn 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 thực hiện tài liệu tham khảo tuyệt đối hoàn hảo ($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 xào nấu công thức lên những ô khác. Ngoài ra, bạn có thể chuyển thay đổi một dải thành một bảng (Ctrl + T) và tìm hiểu thêm nó bằng tên bảng.

Dạng mảng với INDEX – điều 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 minh đố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 cái và row_num bị bỏ qua mất hoặc bằng 0, thì hàm INDEX trả về một mảng của tổng thể cột. Tương tự, ví như 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, phương pháp INDEX đang trả về toàn cục 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 đề xuất tham chiếu cho một ô trong mảng; nếu như không, bí quyết INDEX đang 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 cùng cột vẫn xác định.

Cấu trúc hàm

INDEX (reference, row_num, , )

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

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

Nếu mỗi dãy trong tài liệu tìm hiểu 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ố thiết bị tự của hàng nằm trong vòng mà từ kia trả về một ô tham chiếu, nó tương tự như dạng mảng. column_num – số trang bị tự cột nhằm 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 đã cho thấy mảng từ bỏ đối số tham chiếu để sử dụng. Nếu bỏ qua, phương pháp INDEX sẽ trả lại hiệu quả cho mảng đầu tiên được liệt kê trong danh sách tham khảo.

Ví dụ, cách làm = INDEX ((A2: D3, A5: D7), 3, 4, 2) trả về giá trị của ô D7, tại giao điểm của hàng đồ vật 3 và cột vật dụng 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 phương pháp 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ị bỏ qua, hàm INDEX trả về vùng được hướng đẫ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 ô vào tham chiếu; trường hợp không, cách làm INDEX sẽ trả về #REF! lỗi.

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

Các hàm hay được sử dụng trong excel

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

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

Nguồn dữ liệu (SourceData)

Tất cả những 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), 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 có tên SourceData.

*

Việc sử dụng các bảng hoặc các mảng được lấy tên có thể tạo cho công thức dài hơn một tí, mà lại 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ỉ việc chỉnh sửa một cái tên duy nhất.

Tất nhiên, không có gì bức tường ngăn bạn áp dụng mảng thông thường nếu bạn muốn. Trong trường vừa lòng này, bạn chỉ việc 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 ở vị trí thứ N từ bỏ danh sách

Đây là cách thực hiện cơ bản và là lấy ví dụ về hàm INDEX dễ nắm bắt nhất. Để lấy 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ố đó range là 1 trong những dải ô hoặc dải ô được để tên, với n là vị trí của tài liệu mà bạn có nhu cầu nhận.

Xem thêm: Code Huyền Thoại Hải Tặc 2021, Code Huyền Thoại Hải Tặc Mới Nhất

Khi làm việc với bảng Excel, bạn cũng có thể dùng trỏ chuột lựa chọn cột và Excel sẽ gửi tên của cột cùng rất 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 áp dụng cách tiếp cận tựa như với sự biệt lập duy tuyệt nhất là bạn sử dụng cả nhì – số hàng với số cột.

Và đó là một ví dụ khác: vào bảng tính mà shop 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 sắp xếp bảng theo cột Đường kính (Diameter) và thực hiện 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 hai vào danh sách Column_num là 3 vì chưng Diameter tại vị trí cột thứ 3 trong bảng.

Nếu bạn muốn trả lại tên của trái đất thay bởi vì đường kính, thì chúng ta phải đổi khác column_num thành 1. Với tự nhiên, bạn cũng 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 miêu tả trong hình mặt dưới:

*

nhấn được tất cả các quý giá trong một sản phẩm hoặc cột

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

Các bí quyết INDEX vì thế khó hoàn toàn có thể được sử dụng chính vì Excel không thể phù hợp với dải các giá trị được trả về bởi phương pháp trong một ô duy nhất, mà nắm vào kia 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 phù hợp với các hàm vào Excel khác, chẳng hạn như SUM hoặc AVERAGE, các bạn sẽ thu được hiệu quả tuyệt vời.

Ví dụ, chúng ta cũng có thể sử dụng bí quyết Index Excel sau nhằm tính nhiệt độ trung bình của toàn cầu trong hệ mặt trời:

= AVERAGE (INDEX (SourceData,, 4))

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

*

Tương từ bỏ như vậy, bạn 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 cân nặng hành tinh (Mass là cột thứ hai 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 nên thiết. Bạn chỉ cần viết = AVERAGE (dải) hoặc = SUM (dải) và cũng dìm được kết quả tương tự.

Khi thao tác với dữ liệu thực, bản lĩnh này hoàn toàn 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 nhưng bạn thực hiện để so sánh dữ liệu.

thực hiện INDEX với các hàm Excel khác (SUM, AVERAGE, MAX, MIN)

Từ những ví dụ trước, chúng ta có thể nghĩ rằng cách làm INDEX trong Excel trả về các giá trị, nhưng thực tiễn là nó trả về một tham chiếu đến ô có chứa giá trị. Với ví dụ này thể hiện 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 ý nghĩa tham chiếu, bạn cũng có thể sử dụng nó trong những hàm không giống để tạo ra một dải động. Cạnh tranh hiểu ư? Công thức tiếp sau đây sẽ nắm rõ mọi thứ:

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

Rõ ràng, tuy cách thức công thức INDEX quản lý và vận hành tỏ ra vượt phức tạp, tuy nhiên 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 2 lần bán 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 sắp xếp bảng theo cột Diameter từ lớn nhất đến bé dạ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 núm thể:

Trong trường hợp bạn muốn xác định 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ỉ việc sử dụng nhì hàm INDEX nhằm trả lại mục đầu tiên và mục ở đầu 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) giữa hai tài liệu được hướng dẫn và chỉ định trong ô B1 và ​​B2:

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

 

*

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

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

Lợi ích chủ yếu của bí quyết tiếp cận này là bạn không phải liên tục update tất cả những công thức vào bảng tính của khách hàng để bảo vệ chúng tham chiếu đến những dải một cách chủ yếu xác.

Một cách để xác định một dải cồn được áp dụng hàm OFFSET:

= OFFSET (Sheet_Name! $ A $ 1, 0, 0, COUNTA (Sheet_Name! $A :$A ), 1)

Một giải pháp có thể không giống là thực hiện 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à ô bao gồm chứa mục trước tiên của danh sách và dải động được tạo nên bởi cả hai cách làm sẽ giống hệt nhau.

Sự khác biệt ở đây đó là cách tiếp cận. Hàm INDEX search thấy một ô ở giao điểm của một hàng với cột nỗ lực thể, trong những lúc hàm OFFSET dịch chuyển từ điểm làm mốc bởi một số hàng cùng / hoặc những cột tốt nhất định. Hàm COUNTA, được áp 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 an toàn rằng chỉ phần đông ô có tài liệu được bao gồm trong dải sẽ đặt tên.

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

*

Mẹo: Cách dễ dàng nhất để tạo thành một list tùy tuyển chọn được cập nhật auto trong Excel là chế tạo ra một danh sách (đã đặt tên) với được liệt kê dựa vào một bảng. Trong trường thích hợp này, bạn sẽ không cần bất kỳ công thức tinh vi nào vì các bảng Excel là dải động.

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

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

Không có vụ việc với dò tìm bên trái.Không giới hạn size giá trị tra cứu.Không yêu cầu phân các loại (VLOOKUP với công dụng tương đối lúc yêu cầu thu xếp cột tra cứu giúp theo sản phẩm tự tăng dần).Bạn được tự do thoải mái chèn và sa thải các cột vào một bảng nhưng không cần cập nhật mỗi bí quyết liên quan.

Và ở đầu cuối nhưng không hề kém phần quan lại trọng, INDEX / MATCH không làm lừ đừ Excel của chúng ta như những hàm Vlookup đã làm.

Bạn áp dụng 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 lớn lookup against, 0))

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

*

Khóa học excel kế toán online

áp dụng hàm INDEX để lấy 1 dải từ bỏ 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à bạn cũng 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ó khá 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à PlanetsDMoonsD trong ví dụ này:

*

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

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

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

Hãy xem xét rằng bây chừ chúng ta đang thực hiện định dạng tham chiếu của hàm INDEX, với số trong đối số ở đầu cuối (area_num) cho bí quyết 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 đặt thành 2, trong phương pháp tính 2 lần bán kính trung bình của Moons do dải MoonsD đứng thứ 2 trong đối số tham chiếu.

*

Nếu bạn thao tác làm việc với nhiều list và không thích nhớ các số liên quan, chúng ta cũng 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, bạn sử dụng một số tên danh sách đơn giản và dễ dàng nhớ mà bạn muốn người sử dụng nhập vào ô B1 thay bởi số. Xin chú ý rằng, nhằm công thức làm việc chính xác, văn phiên bản trong B1 phải đúng đắn như nhau (không minh bạch 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 thiện hơn, chúng ta cũng có thể sử dụng Excel Data Validation để tạo list tùy lựa chọn để tránh lỗi chính 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ó vào hàm IFERROR đang nhắc fan dùng lựa chọn một mục từ danh sách theo mục, nếu không tồn tại 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 đó 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 lấy một ví dụ này cho chính mình 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 giỏi Excel vào trong công việc, họ không chỉ nắm rõ được những hàm mà lại còn cần sử dụng giỏi cả những công cố gắng của Excel. Hầu hết hàm nâng cao giúp áp dụng tốt vào các bước như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những cơ chế thường áp dụng là Data validation, Conditional formatting, Pivot table…