Hàm INDEX vào Excel là gì? Cú pháp và cách sử dụng hàm INDEX như vậy nào? cách thức kết đúng theo hàm INDEX cùng MATCH trong Excel để giảm bớt việc đo lường và tính toán trong công việc và học tập. Toàn bộ những sự việc trên đã được xử lý thông qua bài viết dưới đây. Hãy cùng Daotaotinhoc theo dõi nhé!
Tổng quan về hàm INDEX trong Excel
Cú pháp và phương pháp dùng hàm INDEX vào Excel
Những lỗi thường gặp gỡ khi sử dụng hàm INDEX và giải pháp khắc phục
Tổng quan lại về hàm INDEX trong Excel
Trong quá trình học tập hay các bước hàng ngày, có thể hẳn họ đã từng nghe đến các hàm vào Excel. Trong số đó, gồm một hàm linh thiêng hoạt cùng nó 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 rất nhiều vấn đề phức tạp đặt ra nhưng mức độ mạnh của những hàm này hay bị nhận xét thấp cùng không được sử dụng đúng cách. Hàm đó có lẽ là hàm INDEX.
Bạn đang xem: Cách sử dụng hàm index trong excel
Hàm INDEX là gì?
Hàm INDEX là hàm trả về mảng, hàm này giúp lấy những giá trị trên một ô nào đó giao thân cột cùng dòng. Có thể hiểu dễ dàng và đơn giản như sau, trong một mảng cho trước, nếu biết được vị trí (dòng và cột) của một trong những phần tử thì hàm INDEX đã lấy cực hiếm tại ô đó.
Hàm INDEX trong Excel là gì?
Trong Excel hàm INDEX là 1 trong những Function (chức năng) được sử dụng nhằm mục tiêu mục đích trả về một ô tham chiếu trong một mảng hoặc một dải tuyệt nhất định. Xuất xắc nói giải pháp khác, bạn sẽ sử dụng hàm INDEX khi chúng ta biết (hoặc hoàn toàn có thể tính toán) vị trí của một phần tử làm sao trong bảng tính Excel và bạn muốn nhận quý hiếm thực của bộ phận đó.
Hàm INDEX nâng cao trong Excel
Trong Excel, hàm INDEX nâng cao giúp cho bạn cũng có thể tạo một vùng tham chiếu rộng hơn, bao hàm nhiều vùng tham chiếu không giống nhau. Vậy điểm khác biệt giữa hàm INDEX cơ bản và cải thiện như thay nào? thuộc theo dõi phần sau đây nhé!
Cú pháp và phương pháp dùng hàm INDEX trong Excel
Cú pháp hàm INDEX cơ bản
Công thức của hàm INDEX cơ phiên bản như sau:
=INDEX(array;row_num;
Trong đó:
array: là vùng ô hoặc một sản phẩm số mảng như thế nào đó;row_num: là chọn hàng vào mảng từ đó trả về một giá trị;column: là chọn một cột trong mảng từ kia trả về một giá chỉ trị;Ví dụ tham khảo: Bạn hãy tìm cực hiếm của thành phần dòng trang bị 3, cột thứ hai trong mảng (mảng bao hàm 11 chiếc và cột) như hình dưới đây:
Bước 1: Tại vị trí ô G8 chúng ta nhập phương pháp sau:
=INDEX(B3:F13;3;2)
Bước 2: Nhấn phím Enter, ta đang có tác dụng như sau: phần tử ở cái thứ 3, cột thứ 2 trong mảng đã là: “Lê Thị C”
Tương tự, chúng ta cũng có thể tìm phần tử bất kỳ thuộc mảng này thông qua cú pháp đã cung ứng ở bên trên nhé.
Lưu ý: Lỗi #REF sẽ xẩy ra khi các bạn chọn add của bộ phận không bên trong mảng ban đầu.
Cú pháp hàm INDEX nâng cao
Khi vượt ra phạm vi của một mảng, bạn cũng có thể tìm thành phần của một vùng tham chiếu (có thể có 1 hay những mảng khác nhau) bởi công thứ tổng thể như sau:
=INDEX(reference;row_num;
Trong đó:
reference: đây là vùng tham chiếu (có thể phối kết hợp nhiều mảng lại với nhau);row_num: là chỉ số sản phẩm từ kia trả về một tham chiếu;area_num: là số của vùng vẫn trả về quý giá trong reference (có nghĩa là số máy tự của mảng trong vùng tham chiếu). Nếu như như area_num được bỏ qua thì hàm INDEX đang mặc định sử dụng vùng 1.Về mặt cú pháp, hàm INDEX nâng cấp không có sự không giống nhau nhiều đối với hàm cơ bản. Điểm khác biệt đó là vùng giá bán trị bao gồm thể có không ít hơn một mảng và tác dụng trả về sẽ phụ thuộc vào vào mảng mà bạn chọn.
Để dễ dàng nắm bắt hơn, bọn họ cùng quan gần cạnh ví dụ sau:
Ví dụ tham khảo: Giả sử họ có 2 mảng bao gồm mảng
Lúc này, bọn họ sẽ sử dụng công thức của hàm INDEX nâng cao, để tìm công dụng trả về theo điều kiện đưa ra như sau:
=INDEX((A3:F8;A9:F13);3;2;2)
Ở trong cách làm này, bọn họ có 2 mảng
Sau đó, thừa nhận phím Enter, hiệu quả trả về đã là:
Vậy thành phần của loại thứ 3, cột thứ hai của mảng thứ 2 là “Nguyễn Công I”
Kết thích hợp hàm INDEX với MATCH
Trong thực tế, vấn đề dùng hàm INDEX đối chọi thuần hay không được tận dụng tối đa hết tất cả sức mạnh mẽ của hàm này. Fan ta thường kết hợp giữa hàm INDEX và hàm MATCH để thực hiện những chuỗi tính toán phức tạp hơn cực kỳ nhiều.
Như những tò mò trên, vào cú pháp hàm INDEX sẽ sở hữu được 2 giá trị là địa điểm dòng (row_num) và vị trí cột (column_num). Ta hoàn toàn rất có thể thay cụ 2 quý hiếm này bằng cách sử dụng phép toán của hàm MATCH.
Ví dụ tham khảo: trong bảng lương bên dưới đây, các bạn hãy tìm chuyên dụng cho của nhân viên có mã NV là NV04 vào bảng bên dưới đây:
Như vậy, để tìm kiếm được chức vụ của nhân viên trong bảng này, bọn họ phải biết số mẫu và số cột của thành phần đó để áp dụng công thức hàm INDEX như khuyên bảo trên.
Khi đó, muốn kiếm được số mẫu và số cột ta áp dụng thêm công thức của hàm MATCH.
Bước 1: Để kiếm được số dòng, bọn họ phải tìm add của mã “NV04” tong hàng mã nhân viên, bí quyết như sau: =MATCH(“NV04”:B3:B13;0).
Và kết quả trả về là số 4. Vậy mã NV04 nằm tại đoạn dòng lắp thêm 4 trong dãy mã NV:
Bước 2: Để tìm được số cột, ta sẽ phải tìm add của “Chức vụ” trong dãy từ C6 mang đến F6, bí quyết như sau: =MATCH(“Chức vụ”;C2:F2;0).
Vậy tác dụng trả về là số 2. Vậy nhiều từ “chức vụ” này nằm ở phần thứ 2 trong dãy:
Bước 3: Ta kết hợp hàm INDEX với MATCH bằng công thức sau:
=INDEX(C3:F13;MATCH(“NV04”;B3:B13;0);MATCH(“Chức vụ”;C2:F2;0))
Bước 4: Trong công dụng trả về, nhân viên cấp dưới có mã số NV04 làm chức vụ “Trưởng Phòng”
Những lỗi thường gặp khi dùng hàm INDEX và giải pháp khắc phục
Lỗi #N/A hoặc lỗi #VALUE!
Nguyên nhân: Khi sử dụng công thức mảng nhưng mà không dấn Ctrl + Shift + Enter. Tựa như những đề cập trên, chúng ta cũng có thể khai báo row_num hoặc column_num là 0 để hàm trả về mảng quý giá cho toàn cục cột hoặc hàng tương ứng với điều kiện là bạn nhập hàm INDEX làm bí quyết mảng. Vào trường hòa hợp này, ko kể phiên phiên bản mới tốt nhất của Microsoft 365, đối với các phiên bạn dạng khác, Excel vẫn báo lỗi còn nếu như không làm đúng quy trình khi sử dụng công thức mảng.
Cách tự khắc phục:
Với hầu như phiên bản không nên phiên bạn dạng mới của Microsoft 365, các bạn sẽ phải nhập bí quyết mảng bên dưới dạng cách làm mảng kế thừa bằng phương pháp bạn lựa chọn vùng dữ liệu đầu ra, nhập công thức vào ô bên trên cùng, phía trái của phạm vi áp sạc ra rồi sau đó nhấn Ctrl + Shift + Enter.
Ví dụ: Khi bạn có nhu cầu hàm INDEX trả về mảng quý hiếm cho tổng thể cột 1 trong các mảng B3:F13, các bạn hãy làm theo các bước sau:
Bước 1: Bạn lựa chọn phạm vi đầu ra, ở chỗ này bạn lựa chọn 10 mặt hàng trong cột G, có địa chỉ cửa hàng là I3:I13.
Bước 2: Bạn nhập bí quyết ở ô phía trên cùng phía bên trái của phạm vi cổng đầu ra (ở đây là ô I3). Tại địa điểm ô I3 các bạn nhập công thức: =INDEX(B3:F13,0,1)
Bước 3: bạn nhấn Ctrl + Shift + Enter để xác nhận. Phía Excel vẫn chèn dấu ngoặc nhọn nghỉ ngơi đầu cùng cuối công thức cho bạn. Cuối cùng, bạn xem kết quả trả về như sau:
Lỗi #REF!
Nguyên nhân: Khi những đối số row_num và column_num ko trỏ tới ngẫu nhiên một ô nào trong mảng đang khai báo, ví dụ lúc mảng khai báo chỉ có 4 cột nhưng bạn lại vô tình khập column_num là 6.
Cách tương khắc phục: Bạn soát sổ lại bí quyết xem sẽ đúng không và chỉnh lại đến phù hợp, đặc biệt là các đối số column_num, row_num.
Như vậy, thông qua bài viết trên, Daotaotinhoc vừa share cho bạn cú pháp, cách dùng hàm INDEX cùng sự phối kết hợp giữa hàm INDEX cùng MATCH trong Excel. Nếu bạn có câu hỏi hay vướng mắc nào hay nhằm lại bình luận phía bên dưới ngay nhé! Cảm ơn chúng ta đã theo dõi cùng chúc chúng ta thành công!
Trong bài viết này, Học Excel Online vẫn lấy ví dụ như về hàm INDEX trong Excel, qua đó mô tả cho mình những biện pháp sử dụng kết quả công cố gắng này.
Trong toàn bộ các chức năng của Excel mà sức khỏe của chúng thường bị review thấp và không được sử dụng đúng cách, thì INDEX chắc chắn là sẽ xếp hạng ở đâu đó trong top 10. Vì chưng hàm này rất 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 nhiều vấn đề để ra.
Vậy hàm INDEX trong Excel là gì? Về cơ bản, một hàm INDEX đang trả về một ô tham chiếu trong một mảng hoặc một dải tốt nhất định. Nói phương pháp khác, bạn sử dụng INDEX khi chúng ta biết (hoặc hoàn toàn có thể tính toán) vị trí của 1 phần tử vào dải và bạn muốn nhận quý giá thực của phần tử đó.
Điều này nghe có vẻ tầm thường, nhưng khi chúng ta nhận ra tiềm năng đích thực của hàm INDEX, thì nó có thể tạo đề nghị những đổi khác quan trọng so với cách mà các bạn tính toán, so với 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ản
INDEX thao tác với mảng
Cấu trúc hàm
Hàm INDEX – cú pháp và sử dụng cơ bản
Có nhị phiên bạn dạng của hàm INDEX trong Excel – định hình dải với định dạng tham chiếu. Với cả hai mẫu mã đều có thể được áp dụng trong tất cả các phiên bản của Microsoft Excel 2013, Excel 2010, Excel 2007 với 2003.INDEX làm việc với mảng
Dạng mảng INDEX trả về quý giá của 1 phần tử vào một bảng hoặc một mảng dựa trên số hàng với cột mà các bạn chỉ định.
Cấu trúc hàmINDEX (array, row_num,
array – là 1 trong 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ị. Giả dụ row_num bị bỏ qua, column_num là bắt buộc. column_num – là cột thiết 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ách làm “= INDEX (A1: D6, 4, 3)” đang trả về quý giá tại giao điểm của hàng sản phẩm công nghệ 4 với cột máy 3 vào mảng A1: D6, là quý hiếm trong ô C4.
Để có phát minh về phương pháp hàm INDEX chuyển động trên dữ liệu thực, hãy xem lấy ví dụ sau:
Thay vì chưng nhập số hàng cùng cột vào 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 sử dụng tài liệu tham khảo tuyệt vời ($B$2:$D$6) thay vị tham chiếu tương đối (B2:D6) trong đối số mảng khiến cho bạn dễ dàng sao chép công thức lên các ô khác. Ko kể ra, chúng ta có thể chuyển đổi một dải thành một bảng (Ctrl + T) và xem thêm 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ỉ gồm một hàng hoặc cột, thì bạn cũng có thể xác định hoặc không khẳng định đối số row_num hoặc column_num tương ứng. Nếu đối số mảng bao hàm nhiều rộng một cái và row_num bị bỏ lỡ 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 hàm nhiều cột cùng đối số column_num bị bỏ qua mất hoặc bằng 0, bí quyết INDEX sẽ trả về tổng thể hàng. Dưới đó là một ví dụ về công thức minh họa. Những đối số row_num cùng column_num đề xuất tham chiếu đến một ô vào mảng; giả dụ 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ới cột sẽ xác định.
Cấu trúc hàm
INDEX (reference, row_num,
Nếu bạn nhập nhiều hơn một phạm vi, hãy tách các khoảng tầm bằng lốt phẩy và dĩ nhiên đối số tham chiếu vào ngoặc đơn, lấy ví dụ như (A1:B5,D1:F5).
Nếu mỗi dãy trong tài liệu tham khảo chỉ chứa một 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ố đồ vật 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ố sản phẩm công nghệ tự cột nhằm trả về một ô tham chiếu, cũng chuyển động tương từ như dạng mảng. area_num – một tham số tùy chọn chỉ ra mảng trường đoản cú đối số tham chiếu để sử dụng. Nếu vứt qua, cách làm INDEX vẫn trả lại tác dụng cho mảng trước tiên được liệt kê trong list tham khảo.Ví dụ, công thức = INDEX ((A2: D3, A5: D7), 3, 4, 2) trả về quý giá của ô D7, tại giao điểm của hàng máy 3 cùng cột sản phẩm công nghệ 4 trong khoanh vùng thứ nhị (A5: D7).
Cả hai cách làm INDEX chúng ta đã thảo luận cho tới lúc này đều rất dễ dàng và đơn giản và chỉ minh hoạ mang lại khái niệm. Bí quyết thực sự của bạn có thể phức tạp hơn những so cùng với đó, vì chưng vậy, hãy cùng tìm hiểu một vài ba sử dụng hiệu quả nhất của INDEX trong Excel.
Các hàm thường dùng trong excel
Cách sử dụng hàm INDEX vào Excel- ví dụ, công thức
Có thể nó không có tương đối nhiều ứng dụng thực tế, tuy nhiên nếu kết hợp với các hàm khác như MATCH tuyệt COUNTA, nó rất có thể tạo các công thức siêu mạnh.
Nguồn tài liệu (Source
Data)
Tất cả những công thức Excel INDEX của công ty chúng tôi (ngoại trừ phương pháp 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 triển khai trong một bảng có tên Source
Data.
Việc sử dụng những bảng hoặc những mảng được lấy tên có thể tạo cho công thức dài hơn một tí, nhưng nó cũng tạo cho chúng linh hoạt cùng dễ hiểu hơn. Để điều chỉnh ngẫu nhiên 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 áp dụng mảng thông thường nếu bạn muốn. Vào trường hợp này, bạn chỉ cần thay nạm tên bảng Source
Data bằng tham chiếu mảng thích hợp.
Đây là cách áp dụng cơ phiên bản và là lấy một ví dụ về hàm INDEX dễ dàng nắm bắt nhất. Để mang một tài liệu nhất định vào danh sách, bạn chỉ việc viết = INDEX (range, n), trong những số đó range là 1 trong dải ô hoặc dải ô được để tên, và n là địa điểm của dữ liệu mà bạn có nhu cầu nhận.
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ới Excel sẽ chuyển tên của cột với tên của bảng vô công thức:
Để nhận quý giá của ô làm việc giao điểm của một hàng cùng cột đã cho, bạn sử dụng cách tiếp cận tựa như với sự khác hoàn toàn duy tuyệt nhất là bạn áp dụng cả hai – số hàng và số cột.
Và đấy là một lấy ví dụ khác: trong bảng tính mà công ty chúng tôi lấy làm ví dụ, nhằm tìm ra toàn cầu lớn thứ hai trong hệ mặt trời, bạn thu xếp bảng theo cột Đường kính (Diameter) và sử dụng công thức INDEX như sau:
= INDEX (Source
Data, 2, 3)
Data trong lấy ví dụ như này. Row_num là 2 vì ai đang tìm kiếm mục vật dụng hai trong list Column_num là 3 bởi vì Diameter ở chỗ cột đồ vật 3 trong bảng.
Nếu bạn có nhu cầu trả lại thương hiệu của trái đất thay bởi đường kính, thì bạn phải thay đổi column_num thành 1. Cùng tự nhiên, chúng ta có thể sử dụng một tham chiếu ô trong đối số row_num và/hoặc column_num để gia công cho công thức INDEX của doanh nghiệp linh hoạt hơn, như được bộc lộ trong hình bên dưới:
Ngoài việc mang ra một ô duy nhất, hàm INDEX rất có thể trả về một dải những giá trị từ toàn thể các mặt hàng hoặc các cột. Để lấy toàn bộ các giá bán trị từ một cột tốt nhất định, chúng ta phải bỏ lỡ đối số row_num hoặc để nó là 0. Tương tự, để sở hữu được toàn bộ hàng, bạn bỏ qua hoặc 0 vào column_num.
Các công thức INDEX bởi thế khó có thể được sử dụng chính vì Excel không thể tương xứng với dải các giá trị được trả về bởi phương pháp trong một ô duy nhất, mà thế vào kia thì các bạn sẽ nhận được #VALUE! lỗi. Tuy nhiên, nếu như bạn sử dụng INDEX kết 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 tác dụng tuyệt vời.
Ví dụ, chúng ta cũng có thể sử dụng cách làm Index Excel sau nhằm tính ánh sáng trung bình của thế giới trong hệ phương diện trời:
= AVERAGE (INDEX (Source
Data,, 4))
Trong cách làm trên, đối số column_num là 4 vày Temperature là cột thiết bị 4 trong bảng của bọn chúng ta. Thông số row_num bị vứt qua.
Tương trường đoản cú như vậy, chúng ta có thể tìm được ánh sáng tối thiểu và tối đa:
= MAX (INDEX (Source
Data,, 4))
= MIN (INDEX (Source
Data,, 4))
Và tính tổng cân nặng hành tinh (Mass là cột thứ hai trong bảng):
= SUM (INDEX (Source
Data,, 2))
Từ ý kiến thực tế, hàm INDEX trong công thức trên là không nên thiết. Bạn chỉ việc viết = AVERAGE (dải) hoặc = SUM (dải) cùng cũng dìm được kết quả tương tự.
Khi thao tác với tài liệu thực, kỹ năng này có thể hữu ích như là một phần của những công thức tinh vi hơn mà lại bạn áp dụng để so sánh 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, chúng ta 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 cho ô có chứa giá trị. Cùng ví dụ này thể hiện bản 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 chất tham chiếu, bạn cũng có thể sử dụng nó trong những hàm không giống để tạo thành một dải động. Cực nhọc 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 vị viết dải thẳng như vào công thức, thì bạn cũng có thể thay thế cả A1 hoặc A10 hoặc cả nhị bằng các hàm INDEX như sau:
= AVERAGE(A1: INDEX (A1: A20,10))
Cả hai cách làm trên sẽ mang lại công dụng 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 vứt qua). Sự khác biệt là công thức AVERAGE / INDEX sử dụng mang đến dải động và khi bạn thay đổi đối số row_num vào INDEX, thì dải nhưng mà được xử lý bởi hàm AVERAGE sẽ biến hóa và công thức sẽ trả về một tác dụng khác.
Rõ ràng, tuy cách thức công thức INDEX vận hành tỏ ra vượt phức tạp, tuy thế 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 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 số 1 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 số 1 đến bé dại nhất và áp dụng công thức Average / Index sau:
= AVERAGE(C5: INDEX (Souce
Data
Ví dụ 2. Tính tổng các giá trị thân 2 mục cố thể:
Trong ngôi 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 phương pháp của mình, chỉ cần sử dụng nhị hàm INDEX nhằm trả lại mục thứ nhất và mục sau cùng bạn muốn.
Ví dụ: bí quyết sau trả về tổng những giá trị vào cột Diameter (đường kính) thân hai tài liệu được chỉ định và hướng dẫn trong ô B1 với B2:
= SUM (INDEX (Source
Data
Data
Điều này khôn xiết thường xảy ra. Lúc bạn ban đầ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 các bạn sẽ có.
Dù sao thì, nếu như khách hàng có số các mục vào một cột đang cho nỗ lực đổi, từ bỏ A1 mang lại An, và chúng ta có thể muốn tạo nên một dải được đặt tên – bao hàm tất cả những ô gồm dữ liệu. Tại đó, bạn có nhu cầu 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 vài mục hiện nay có. Ví dụ: nếu khách hàng hiện gồm 10 mục, dải được lấy tên của 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à nếu bạn chuyển đổi ý định với xóa tài liệu mới được 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 hẳn 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 doanh nghiệp để bảo đảm an toàn chúng tham chiếu đến những dải một cách thiết yếu xác.
Một phương pháp để 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 phương án 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ả nhị công thức, A1 là ô bao gồm chứa mục thứ nhất của list và dải rượu cồn được tạo nên bởi cả hai cách làm sẽ như nhau nhau.
Sự khác biệt ở đây đó là cách tiếp cận. Hàm INDEX tra cứu thấy một ô ở giao điểm của một hàng với cột núm thể, trong khi hàm OFFSET di chuyển từ điểm làm cho mốc bởi một trong những hàng và / hoặc những cột tốt nhất định. Hàm COUNTA, được sử dụng trong cả nhị công thức, tính con số các ô ko rỗng trong cột và đảm bảo rằng chỉ số đông ô có tài liệu được bao hàm trong dải đang đặt tên.
Hình tiếp sau đây sẽ cho mình thấy cách bạn có thể sử dụng cách làm Index để chế tạo một danh sách tùy chọn.
Mẹo: Cách dễ dàng nhất để chế tạo một list tùy tuyển chọn được cập nhật auto trong Excel là tạo một list (đã đặt tên) và được liệt kê dựa vào một bảng. Vào trường đúng theo này, bạn sẽ không cần bất kỳ công thức phức hợp nào vì các bảng Excel là dải động.
mức độ mạnh của những 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 – đó là nơi cơ mà hàm INDEX thực sự tỏa sáng. Nếu như bạn đã từng thử thực hiện 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ó chức năng kéo giá trị từ các cột qua bên trái của cột tra cứu vớt hoặc số lượng giới hạn 255 cam kết tự cho 1 giá trị tra cứu. Và chính vì như thế cho đề nghị kết hòa hợp index và match là chiến thuật mà nhiều người lựa lựa chọn để vắt 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. Ko giới hạn kích cỡ giá trị tra cứu. Ko yêu cầu phân nhiều loại (VLOOKUP với hiệu quả tương đối lúc yêu cầu bố trí cột tra cứu theo máy tự tăng dần). Các bạn được thoải mái chèn và đào thải các cột trong một bảng mà lại 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 thua kém phần quan liêu trọng, INDEX / MATCH không làm chậm rãi Excel của người tiêu dùng như nhiều hàm Vlookup vẫn làm.
Bạn sử dụng INDEX / MATCH – hàm index phối hợp match theo cách sau:
= INDEX (column lớn return a value from, (MATCH (lookup value, column to lớn lookup against, 0))
Ví dụ: nếu chúng ta mở bảng dữ liệu gốc của họ ra cùng với tên hành tinh (planet name) trở nên cột thứ nhất bên phải, thì hàm phương pháp INDEX / MATCH vẫn rước giá trị cân xứng từ cột phía bên trái mà không gặp bất kì trở ngại ngùng nào.
Khóa học excel kế toán tài chính online
sử dụng hàm INDEX để đưa 1 dải từ danh sách những dải:Một cách sử dụng thông minh và bổ ích hàm INDEX vào Excel là bạn có thể có được một dải xuất phát điểm từ một danh sách những dải.
Giả sử, bạn có khá nhiều danh sách với con số các mục khác nhau.
Trước hết, chúng ta đặt tên cho từng dải trong những danh sách; Ví như hoàn toàn có thể đặt là Planets
D với Moons
D 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 nghe biết trong Hệ mặt trời của chúng ta, dòng yamaha jupiter có 63 cùng vẫn còn nhiều hơn nữa. Đối với lấy một ví dụ này, tôi chọn 11 mẫu ngẫu nhiên.
Giả sử rằng Planets
D là dải 1 của bạn và Moons
D phía trong dải 2 với ô B1 là nơi bạn đặ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ị trong dải ô được chọn:
= AVERAGE (INDEX ((Planets
D, Moons
D),,, B1))
Hãy xem xét rằng bây chừ chúng ta đang sử dụng định dạng tham chiếu của hàm INDEX, và số trong đối số sau cuối (area_num) cho cách làm biết phạm vi dải nào được lựa chọn.
Trong hình mặt dưới, area_num (cell B1) được đặt thành 2, trong bí quyết tính 2 lần bán kính trung bình của Moons bởi dải Moons
D đứng thứ 2 vào đối số tham chiếu.
Xem thêm: Top 10 ứng dụng dịch từ tiếng anh sang tiếng việt chính xác nhất
Nếu bạn thao tác làm việc với nhiều list 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 để làm điều này cho bạn:
= AVERAGE (INDEX ((Planets
D, Moons
D),,, IF (B1 = “planets”, 1, IF (B1 = “moon”, 2))))
Trong hàm IF, các bạn sử dụng một vài tên danh sách dễ dàng và đơ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 vì số. Xin lưu ý rằng, để công thức làm việc chính xác, văn phiên bản trong B1 phải đúng chuẩn như nhau (không phân minh chữ hoa chữ thường) như trong các thông số kỹ thuật của IF, còn nếu không công thức Index của các bạn sẽ xuất ra #VALUE lỗi.
Thậm chí, nhằm công thức gần gũi hơn, bạn có thể sử dụng Excel Data Validation để tạo list tùy chọn để tránh lỗi chủ yếu tả với lỗi trang in:
Cuối cùng, để gia công cho công thức INDEX của bạn trọn vẹn hoàn hảo, bạn cũng có thể đặt nó trong hàm IFERROR vẫn nhắc bạn dùng chọn 1 mục từ danh sách theo mục, nếu không tồn tại lựa lựa chọn nào được gửi ra:
= IFERROR (AVERAGE (INDEX ((Planets
D, Moons
D),,, 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 thực hiện công thức INDEX vào Excel. Tôi hi vọng những lấy một 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 trong bảng tính của bạn. Cảm ơn bạn đã đọc!
Để rất có thể ứng dụng giỏi Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm nhưng còn yêu cầu sử dụng giỏi cả các công cầm của Excel. đều 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 phương tiện thường áp dụng là Data validation, Conditional formatting, Pivot table…Toàn bộ những kỹ năng và kiến thức này các bạn đều hoàn toàn có thể học được vào khóa học:EX101 – Excel từ bỏ cơ phiên bản đến cải thiện dành cho tất cả những người đi làm