Trong nội dung nội dung bài viết này, Học Excel Trực tuyến sẽ lấy ví dụ về hàm INDEX trong Excel, qua đó mô tả cho mình những cách sử dụng hiệu suất cao dụng cụ này.
Chúng ta đang xem: Cách sử dụng hàm index
Trong toàn bộ công dụng của Excel mà sức mạnh của chúng thường bị Đánh Giá thấp và ko được sử dụng đúng cách dán dán, thì INDEX chắc khỏe sẽ xếp hạng ở đâu đó trong top 10. Bởi hàm này rất nhạy và thậm chí kết thích nghi với những hàm khác nữa để xử lý được nhiều vấn đề đề ra.
Vậy hàm INDEX trong Excel là gì? Về cơ các bạn dạng, 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, chúng ta sử dụng INDEX lúc chúng ta biết (hoặc thậm chí tính toán) vị trí của một thực thể trong dải và mình thích nhận giá trị thực của thực thể đó.
Điều này nghe mang vẻ tầm thường, nhưng lúc chúng ta nhận ra tiềm năng thực sự của hàm INDEX, thì nó thậm chí tạo ra những thay đổi quan trọng so với cách mà chúng ta tính toán, phân tích và trình diễn dữ liệu trong số bảng tính của doanh nghiệp.
Hàm INDEX – cú pháp và sử dụng cơ các bạn dạng
Sở hữu hai phiên các bạn dạng 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 thậm chí được sử dụng trong toàn bộ những phiên các bạn dạng 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ề giá trị của một thực thể trong một bảng hoặc một mảng dựa trên số hàng và cột mà chúng ta ko sử dụng.
Cấu trúc hàm
INDEX (array, row_num,
array – là một dãi những ô, mang tên dải hoặc bảng. row_num – là hàng thứ mấy trong mảng mà mình thích được trả về một giá trị. Nếu row_num bị bỏ qua, column_num là cần. column_num – là cột thứ mấy để trả về một giá trị. Nếu column_num bị bỏ qua, row_num là cần.
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.
Để sở hữu ý tưởng về tư thế hàm INDEX sinh hoạt trên dữ liệu thực, hãy xem ví dụ sau:
Thay vì như thế như vậy nhập số hàng và cột trong công thức, chúng ta thậm chí hỗ trợ 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 tìm hiểu thêm tuyệt đối ($B$2:$D$6) thay vì như thế như vậy tham chiếu tương đối (B2:D6) trong đối số mảng khiến cho cho mình đơn giản dễ dàng và đơn giản và giản dị sao chép công thức lên những ô khác. Ngoài ra, chúng ta thậm chí chuyển đổ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à INDEX – điều cần nhớNếu đối số mảng chỉ bao hàm một hàng hoặc cột, thì chúng ta thậm chí xác định hoặc ko 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 nữa thế nữa 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 hà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 trên trên đây là một ví dụ về công thức minh họa.Những đối số row_num và column_num phải tham chiếu tới một ô trong mảng; Nếu ko, 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,
reference – là một hoặc nhiều dải.
Nếu như người sử dụng nhập nhiều hơn nữa thế nữa một phạm vi, hãy tách những khoả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 tìm hiểu thêm 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 tìm.
row_num – số thứ tự của hàng nằm trong tầm 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 sinh hoạt tương tự như dạng mảng. area_num – một tham số tùy tìm đã cho thấy mảng từ đối số tham chiếu để sử dụng. Nếu bỏ qua, công thức INDEX sẽ trả lại hậu quả cho mảng trước tiên được liệt kê trong danh sách tìm hiểu thêm.
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 ko sử dụng trong đối số area_num.Toàn bộ những _num arguments (row_num, column_num và area_num) phải tìm hiểu thêm một ô trong tham chiếu; Nếu ko, 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 tới nay thường rất giản dị và chỉ minh hoạ cho quan niệm. Công thức thực sự của doanh nghiệp thậm chí phức tạp hơn nhiều so với đó, vì như thế như vậy vậy, nào nào hãy cùng nhau mày mò một vài sử dụng hiệu suất tối đa của INDEX trong Excel.
Những hàm thường sử dụng trong excel
Cách sử dụng hàm INDEX trong Excel- ví dụ, công thức
Mà thậm chí nó ko tồn tại nhiều ứng dụng thực tiễn, nhưng nếu kết thích nghi với những hàm khác như MATCH hay COUNTA, nó thậm chí tạo những công thức rất mạnh.
Nguồn dữ liệu (SourceData)
Toàn bộ những công thức Excel INDEX của siêu thị Shop chúng tôi (ngoại trừ công thức ở đầu cuối), siêu thị Shop chúng tôi sẽ sử dụng dữ liệu dưới trên trên đâ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 những mảng được đặt tên thậm chí làm cho công thức dài thêm hơn thế nữa một tí, nhưng nó cũng làm cho chúng linh hoạt và dễ đọc 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 doanh nghiệp, chúng ta chỉ việc chỉnh sửa một chiếc tên duy nhất.
Tất nhiên, ko tồn tại gì ngăn chặn chúng ta sử dụng mảng thường thì nếu mình thích. Trong trường hợp này, chúng ta chỉ việc 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
Trên trên đây là cách sử dụng cơ các bạn dạng 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, chúng ta chỉ việc 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à mình thích nhận.
Lúc thao tác với bảng Excel, chúng ta thậm chí sử dụng trỏ chuột tìm 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 giá trị của ô ở giao điểm của một hàng và cột đã cho, chúng ta sử dụng cách tiếp cận tương tự với sự khác lạ duy nhất là chúng ta sử dụng cả hai – số hàng và số cột.
Xem thêm: Bột Gelatin Là Gì ? Công Dụng Bột Gelatin? Cách Sử dụng Và Sắm Bột Gelatin Ở Đâu
Và trên trên đây là một ví dụ khác: Trong bảng tính mà siêu thị Shop chúng tôi lấy làm ví dụ, để tìm ra hành tinh to thứ 2 trong hệ mặt trời, chúng ta sắp xếp 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 ứng với SourceData trong ví dụ này. Row_num là 2 vì như thế như vậy chúng ta đang tìm kiếm mục thứ hai trong danh sách Column_num là 3 vì như thế như vậy Diameter ở vị trí cột thứ 3 trong bảng.
Nếu mình thích trả lại tên của hành tinh thay vì như thế như vậy đường kính, thì chúng ta phải thay đổi column_num thành 1. Và tự nhiên, chúng ta thậm chí 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 doanh nghiệp linh hoạt hơn, như được thể hiện trong hình dưới:
Nhận được toàn bộ những 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 thậm chí trả về một dải những giá trị từ toàn bộ những hàng hoặc những cột. Để lấy toàn bộ những giá trị từ một cột nhất định, chúng ta phải bỏ qua đối số row_num hoặc đặt nó là 0. Tương tự, để sở hữu được toàn bộ hàng, chúng ta bỏ qua hoặc 0 trong column_num.
Những công thức INDEX như vậy khó thậm chí được sử dụng chính vì như thế Excel ko thể thích ứng với dải những giá trị được trả về bởi công thức trong một ô duy nhất, mà thay vào đó thì những các bạn sẽ nhận được #VALUE! lỗi. Tuy nhiên, nếu như người sử dụng sử dụng INDEX kết thích nghi với những hàm trong Excel khác, ví dụ như SUM hoặc AVERAGE, những các bạn sẽ thu được hậu quả tuyệt vời.
Ví dụ, chúng ta thậm chí 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ì như thế như vậy Temperature là cột thứ 4 trong bảng của con người. Thông số kỹ thuật kỹ thuật row_num bị bỏ qua.
Tương tự như vậy, chúng ta thậm chí 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 tiễn, hàm INDEX trong công thức trên là ko quan trọng. Chúng ta chỉ việc viết = AVERAGE (dải) hoặc = SUM (dải) và cũng nhận được hậu quả tương tự.
Lúc thao tác với dữ liệu thực, tính năng này thậm chí hữu ích như là một phần của những công thức phức tạp hơn mà chúng ta sử 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ừ những ví dụ trước, chúng ta thậm chí 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 tới ô mang chứa giá trị. Và ví dụ này thể hiện thực ra thực sự của hàm INDEX.
Với hậu quả của một công thức INDEX mang tính chất chất tham chiếu, chúng ta thậm chí sử dụng nó trong số hàm khác để tạo ra một dải động. Khó hiểu ư? Công thức sau trên trên đây sẽ làm rõ mọi thứ:
Giả sử chúng ta mang một công thức = AVERAGE (A1: A10) trả về giá trị trung bình của những ô A1: A10. Thay vì như thế như vậy viết dải trực tiếp như trong công thức, thì chúng ta thậm chí thay thế 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ông thức trên sẽ mang lại hậu quả tương tự chính vì như thế hàm INDEX cũng trả về một tham chiếu tới ô A10 (row_num được đặt là 10, col_num bỏ qua). Sự khác lạ là công thức AVERAGE / INDEX sử dụng cho dải động và lúc chúng ta 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 hậu quả khác.
Rõ, tuy phương thức công thức INDEX vận hành tỏ ra quá phức tạp, nhưng nó mang những ứng dụng thực tiễn, như trong số ví dụ sau trên trên đâ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ử mình thích biết đường kính trung bình của n hành tinh to nhất trong khối khối hệ thống của siêu thị Shop chúng tôi. Vì như thế thế vậy, chúng ta sắp xếp bảng theo cột Diameter từ to nhất tới nhỏ nhất và sử dụng công thức Average / Index sau:
= AVERAGE(C5: INDEX (SouceData
Ví dụ 2. Tính tổng những giá trị giữa 2 mục rõ:
Trong trường hợp mình thích xác định giới hạn trên và giới hạn dưới trong công thức của tớ, chỉ việc sử dụng hai hàm INDEX để trả lại mục trước tiên và mục ở đầu cuối mình thích.
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 dữ liệu được ko sử dụng trong ô B1 và B2:
= SUM (INDEX (SourceData
Công thức INDEX để tạo những dải động và danh sách theo mục
Điều này rất thường xẩy ra. Lúc chúng ta chính thức tổ chức dữ liệu trong một bảng tính, chúng ta thậm chí ko biết mang bao nhiêu mục những các bạn sẽ mang.
Dù sao thì, nếu như người sử dụng mang số những mục trong một cột đã cho thay đổi, từ A1 tới An, và chúng ta thậm chí muốn tạo ra một dải được đặt tên – bao hàm toàn bộ những ô mang dữ liệu. Tại đó, mình thích dải được điều chỉnh tự động lúc chúng ta thêm mục mới hoặc xóa một vài trong những mục hiện mang. Ví dụ: nếu như người sử dụng hiện mang 10 mục, dải được đặt tên của những các bạn sẽ là A1: A10. Nếu như người sử dụng thêm một mục nhập mới, phạm vi tên sẽ tự động mở mênh mông lên A1: A11 và nếu như người sử dụng thay đổi dự định và xóa dữ liệu mới được thêm vào, phạm vi sẽ tự động quay trở lại A1: A10.
Thuận tiện chính của cách tiếp cận này là chúng ta ko phải liên tục update toàn bộ những công thức trong bảng tính của doanh nghiệp để đảm bảo chúng tham chiếu tới những dải một cách đúng mự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 giải pháp thậm chí 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à ô mang chứa mục trước tiên của danh sách 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 lạ ở trên trên đây này 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 rõ, trong lúc hàm OFFSET dịch rời từ điểm làm mốc bởi một vài trong những hàng và / hoặc những cột nhất định. Hàm COUNTA, được sử dụng trong cả hai công thức, tính số lượng những ô ko rỗng trong cột và đảm nói rằng chỉ những ô mang dữ liệu được bao hàm trong dải đã đặt tên.
Hình sau trên trên đây sẽ cho mình thấy cách chúng ta thậm chí sử dụng công thức Index để tạo một danh sách tùy tìm.
Mẹo: Cách dễ nhất để tạo một danh sách tùy tìm được update 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, những các bạn sẽ ko cần ngẫu nhiên công thức phức tạp nào vì như thế như vậy những bảng Excel là dải động.
Sức mạnh của những hàm Vlookup lúc kết thích nghi với INDEX / MATCH:
Tiến hành những hàm Vlookup theo chiều dọc trong Excel – trên trên đây là nơi mà hàm INDEX thực sự tỏa sáng sủa sủa. Nếu như người sử dụng đã từng thử sử dụng hàm VLOOKUP, những các bạn sẽ nhận thức được nhiều hạn chế của nó, ví dụ như ko tồn tại kinh nghiệm kéo giá trị từ những cột qua phía phía bên trái của cột tra cứu giúp vãn hoặc giới hạn 255 ký tự cho một giá trị tra cứu giúp vãn.
Hàm INDEX / MATCH tốt hơn VLOOKUP ở nhiều khía cạnh:
Ko tồn tại vấn đề với dò tìm phía phía bên trái.Ko giới hạn kích thước giá trị tra cứu giúp vãn.Ko yêu cầu phân loại (VLOOKUP với hậu quả tương đối lúc yêu cầu sắp xếp cột tra cứu giúp vãn theo thứ tự tăng dần).Chúng ta được tự do chèn và loại bỏ những cột trong một bảng mà ko cần update mỗi công thức liên quan.
Và ở đầu cuối nhưng vẫn ko xoàng cỏi phần quan trọng, INDEX / MATCH ko làm chậm Excel của doanh nghiệp như nhiều hàm Vlookup đã làm.
Chúng ta sử dụng INDEX / MATCH theo cách sau:
= INDEX (column to return a value from, (MATCH (lookup value, column to lookup against, 0))
Ví dụ: nếu chúng ta mở bảng dữ liệu gốc của con người ra với tên hành tinh (planet name) trở thành cột trước tiên phía phía bên phải, thì hàm công thức INDEX / MATCH vẫn lấy giá trị thích ứng từ cột phía phía bên trái mà ko gặp gỡ gỡ bất kì trở ngại nào.
Khóa học excel kế toán tài chính tài chính trực tuyến
Sử dụng hàm INDEX để lấy 1 dải từ danh sách những dải:
Một cách sử dụng thông minh và hữu hiệu hàm INDEX trong Excel là chúng ta thậm chí mang được một dải từ một danh sách những dải.
Giả sử, chúng ta mang nhiều danh sách với số lượng những mục ko giống nhau.
Trước hết, chúng ta đặt tên cho từng dải trong những danh sách; Ví như thậm chí đặt là PlanetsD và MoonsD trong ví dụ này:
Tuy nhiên, bảng Moons còn chưa tuyệt vời, mang 176 mặt trăng tự nhiên được tìm tới trong Hệ Mặt trời của con người, Jupiter mang 63 và vẫn tồn tại nhiều hơn nữa thế nữa nữa. So với ví dụ này, tôi tìm 11 chiếc ngẫu nhiên.
Giả sử rằng PlanetsD là dải 1 của doanh nghiệp và MoonsD nằm trong dải 2 và ô B1 là nơi chúng ta đặt số dãy, chúng ta thậm chí sử dụng công thức Index sau để tính giá trị trung bình của những giá trị trong dải ô được tìm:
= AVERAGE (INDEX ((PlanetsD, MoonsD),,, B1))
Hãy lưu ý rằng giờ trên đây 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 phạm vi dải nào được lựa tìm.
Trong hình 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ì như thế như vậy dải MoonsD đứng thứ 2 trong đối số tham chiếu.
Xem thêm: Sinh Năm 1976 Tuổi Gì, Mệnh Gì? Tổng Quan Về Người Sinh Năm 1976
Nếu như người sử dụng thao tác với nhiều danh sách và ko muốn nhớ những số liên quan, chúng ta thậm chí sử dụng thêm hàm IF để làm điều này cho mình:
= 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 trong những tên danh sách giản dị và dễ nhớ mà mình thích người tiêu dùng nhập vào ô B1 thay vì như thế như vậy số. Xin lưu ý rằng, để công thức thao tác đúng mực, văn các bạn dạng trong B1 phải đúng mực như nhau (ko phân biệt chữ hoa chữ thường) như trong số thông số kỹ thuật kỹ thuật của IF, nếu ko công thức Index của những 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 thậm chí sử dụng Excel Data Validation để tạo danh sách tùy tìm để tránh lỗi chính tả và lỗi trang in:
Sau cuối, để làm cho công thức INDEX của doanh nghiệp trọn vẹn tuyệt vời và tuyệt vời nhất, chúng ta thậm chí đặt nó trong hàm IFERROR sẽ nhắc người tiêu dùng tìm một mục từ danh sách theo mục, nếu ko tồn tại lựa tìm nào được tìm thấy:
= IFERROR (AVERAGE (INDEX ((PlanetsD, MoonsD),,, IF (B1 = “planet”, 1, IF (B1 = “moon”, 2)))), “Please select the list!”)
Trên trên trên đây là hướng dẫn cách chúng ta sử dụng công thức INDEX trong Excel. Tôi hy vọng những 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 trong bảng tính của doanh nghiệp. Cảm ơn chúng ta đã đọc!
Để thậm chí ứng dụng tốt Excel vào trong công việc, chúng ta không những là bắt vững được những hàm mà còn phải sử dụng tốt cả những dụng cụ của Excel. Những hàm nâng cao giúp vận dụng tốt vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những dụng cụ thường sử dụng là Data validation, Conditional formatting, Pivot table…
Phân mục: Tổng hợp
