Trong bài viết này, Học Excel Online sẽ lấy ví dụ về hàm INDEX trong Excel, qua đó mô tả cho bạn những cách sử dụng hiệu quả phương tiện này.
Trong tất cả những chức năng của Excel mà sức mạnh của chúng thường bị thẩm định thấp và ko được sử dụng đúng cách, thì INDEX kiên cố sẽ xếp hạng ở đâu đó trong top 10. Bởi hàm này rất nhạy và với thể kết hợp với những hàm khác nữa để khắc phục được nhiều vấn đề đặt 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 trong một mảng hoặc một dải nhất định. Nói cách khác, bạn sử dụng INDEX lúc bạn biết (hoặc với thể tính toán) vị trí của một phần tử trong dải và bạn muốn nhận trị giá thực của phần tử đó.
Điều này nghe với vẻ tầm thường, nhưng lúc bạn nhìn thấy tiềm năng thực sự của hàm INDEX, thì nó với thể tạo nên những thay đổi quan yếu đối 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.
Hàm INDEX – cú pháp và sử dụng cơ bản
Với hai phiên bản 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 với thể được sử dụng trong tất cả những phiên bản 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ề trị giá 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 và cột mà bạn chỉ định.
Cấu trúc hàm
INDEX (array, row_num, [column_num])
- array – là một dãi những ô, với tên dải hoặc bảng.
- row_num – là hàng thứ mấy trong mảng mà bạn muốn được trả về một trị giá. Nếu row_num bị bỏ qua, column_num là đề xuất.
- column_num – là cột thứ mấy để trả về một trị giá. Nếu column_num bị bỏ qua, row_num là đề xuất.
Ví dụ, công thức “= INDEX (A1: D6, 4, 3)” sẽ trả về trị giá tại giao điểm của hàng thứ 4 và cột thứ 3 trong mảng A1: D6, là trị giá trong ô C4.
Để với ý tưởng về cách hàm INDEX hoạt động trên dữ liệu thực, hãy xem ví dụ sau:
Thay vì nhập số hàng và cột trong công thức, bạn với thể phân phối tham chiếu ô để với đượ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 đối ($B$2:$D$6) thay vì tham chiếu tương đối (B2:D6) trong đối số mảng giúp bạn thuận tiện sao chép công thức lên những ô khác. Ngoài ra, bạn với thể chuyển đổi một dải thành một bảng (Ctrl + T) và tham khảo 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 gồm một hàng hoặc cột, thì bạn với thể 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 gồm nhiều hơn 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 gồ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 đâ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, [column_num], [area_num])
- reference – là một hoặc nhiều dải.
Nếu bạn nhập nhiều hơn 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 tham khảo 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 chọn.
- row_num – số thứ tự của hàng nằm từ 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 hoạt động tương tự như dạng mảng.
- area_num – một thông số tùy chọn chỉ ra mảng từ đối số tham chiếu để sử dụng. Nếu bỏ qua, công thức INDEX sẽ trả lại kết quả cho mảng trước 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ề trị giá 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 chỉ định trong đối số area_num.
- Tất cả những _num arguments (row_num, column_num và area_num) phải tham khảo 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 đều rất đơn thuần và chỉ minh hoạ cho khái niệm. Công thức thực sự của bạn với thể phức tạp hơn nhiều so với đó, vì vậy, hãy cùng khám phá một vài sử dụng hiệu quả nhất của INDEX trong Excel.
Cách tiêu dùng hàm INDEX trong Excel- ví dụ, công thức
Với thể nó ko với nhiều ứng dụng thực tế, nhưng nếu kết hợp với những hàm khác như MATCH hay COUNTA, nó với thể tạo những công thức rất mạnh.
Nguồn dữ liệu (SourceData)
Tất cả những công thức Excel INDEX của chúng tôi (ngoại trừ công thức cuối cùng), 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 với tên SourceData.
Việc sử dụng những bảng hoặc những mảng được đặt tên với thể làm cho công thức dài hơn 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 bất kỳ công thức INDEX nào cho những bảng tính của bạn, bạn chỉ cần chỉnh sửa một mẫu tên duy nhất.
Tất nhiên, ko với gì ngăn cản bạn sử dụng mảng thông thường nếu bạn muốn. Trong trường hợp này, bạn chỉ cần 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
Đây là cách sử dụng cơ bản 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, bạn chỉ cần 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à bạn muốn nhận.
Lúc làm việc với bảng Excel, bạn với thể tiêu dùng trỏ chuột chọn 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 trị giá của ô ở giao điểm của một hàng và cột đã cho, bạn sử dụng cách tiếp cận tương tự với sự khác biệt duy nhất là bạn sử dụng cả hai – số hàng và số cột.
Và đây là một ví dụ khác: Trong bảng tính mà chúng tôi lấy làm ví dụ, để tìm ra hành tinh to thứ Hai trong hệ mặt trời, bạn 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à Hai vì bạn đang tìm kiếm mục thứ hai trong danh sách
- Column_num là 3 vì Diameter ở vị trí cột thứ 3 trong bảng.
Nếu bạn muốn trả lại tên của hành tinh thay vì đường kính, thì bạn phải thay đổi column_num thành 1. Và tự nhiên, bạn với 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 thể hiện trong hình bên dưới:
- Nhận được tất cả những trị giá trong một hàng hoặc cột
Ngoài việc lấy ra một ô duy nhất, hàm INDEX với thể trả về một dải những trị giá từ toàn bộ những hàng hoặc những cột. Để lấy tất cả những trị giá từ một cột nhất định, bạn phải bỏ qua đối số row_num hoặc đặt nó là 0. Tương tự, để với được toàn bộ hàng, bạn bỏ qua hoặc 0 trong column_num.
Những công thức INDEX tương tự khó với thể được sử dụng bởi vì Excel ko thể thích hợp với dải những trị giá được trả về bởi công thức trong một ô duy nhất, mà thay vào đó thì bạn sẽ nhận được #VALUE! lỗi. Tuy nhiên, nếu bạn sử dụng INDEX kết hợp với những hàm trong Excel khác, chẳng hạn như SUM hoặc AVERAGE, bạn sẽ thu được kết quả tuyệt vời.
Ví dụ, bạn với thể 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ì Temperature là cột thứ 4 trong bảng của chúng ta. Thông số row_num bị bỏ qua.
Tương tự tương tự, bạn với thể 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ứ Hai trong bảng):
= SUM (INDEX (SourceData,, 2))
Từ ý kiến thực tế, hàm INDEX trong công thức trên là ko cần thiết. Bạn chỉ cần viết = AVERAGE (dải) hoặc = SUM (dải) và cũng nhận được kết quả tương tự.
Lúc làm việc với dữ liệu thực, tính năng này với 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à bạn 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, bạn với thể nghĩ rằng công thức INDEX trong Excel trả về những trị giá, nhưng thực tế là nó trả về một tham chiếu tới ô với chứa trị giá. Và ví dụ này thể hiện bản tính thực sự của hàm INDEX.
Với kết quả của một công thức INDEX mang tính tham chiếu, chúng ta với thể sử dụng nó trong những hàm khác để tạo ra một dải động. Khó hiểu ư? Công thức sau đây sẽ làm rõ mọi thứ:
Giả sử bạn với một công thức = AVERAGE (A1: A10) trả về trị giá trung bình của những ô A1: A10. Thay vì viết dải trực tiếp như trong công thức, thì bạn với thể 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 kết quả tương tự bởi vì 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 biệt là công thức 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à được xử lý bởi hàm AVERAGE sẽ thay đổi và công thức sẽ trả về một kết quả khác.
Rõ ràng, tuy cách thức công thức INDEX vận hành tỏ ra quá phức tạp, nhưng nó với những ứng dụng thực tế, như trong những 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 to nhất trong hệ thống của chúng tôi. Vì vậy, bạn 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 [Diameter], B1))
Ví dụ 2. Tính tổng những trị giá giữa Hai mục cụ thể:
Trong trường hợp bạn muốn xác định giới hạn trên và giới hạn dưới trong công thức của mình, chỉ cần sử dụng hai hàm INDEX để trả lại mục trước tiên và mục cuối cùng bạn muốn.
Ví dụ: công thức sau trả về tổng những trị giá trong cột Diameter (đường kính) giữa hai dữ liệu được chỉ định trong ô B1 và B2:
= SUM (INDEX (SourceData [Diameter], B1): INDEX (SourceData [Diameter], B2))
- 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 bạn khởi đầu tổ chức dữ liệu trong một bảng tính, bạn với thể ko biết với bao nhiêu mục bạn sẽ với.
Dù sao thì, nếu bạn với số những mục trong một cột đã cho thay đổi, từ A1 tới An, và bạn với thể muốn tạo ra một dải được đặt tên – bao gồm tất cả những ô với dữ liệu. Tại đó, bạn muốn dải được điều chỉnh tự động lúc bạn thêm mục mới hoặc xóa một số mục hiện với. Ví dụ: nếu bạn hiện với 10 mục, dải được đặt 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 mở rộng lên A1: A11 và nếu bạn thay đổi ý đị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.
Lợi ích chính của cách tiếp cận này là bạn ko phải liên tục cập nhật tất cả những công thức trong bảng tính của bạn để đảm bảo chúng tham chiếu tới những dải một cách xác thực.
Một cách để 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 với thể 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à ô với 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ẽ y sì nhau.
Sự khác biệt ở đây chính 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 cụ thể, trong lúc hàm OFFSET di chuyển từ điểm làm mốc bởi một số 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 bảo rằng chỉ những ô với dữ liệu được bao gồm trong dải đã đặt tên.
Hình sau đây sẽ cho bạn thấy cách bạn với thể sử dụng công thức Index để tạo một danh sách tùy chọn.
Mẹo: Cách dễ nhất để tạo một danh sách tùy chọn được cập nhật 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, bạn sẽ ko cần bất kỳ 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 lúc kết hợp với INDEX / MATCH:
Thực hiện những hàm Vlookup theo chiều dọc trong Excel – đây là nơi mà hàm INDEX thực sự tỏa sáng. Nếu bạn đã từng thử sử dụng hàm VLOOKUP, bạn sẽ nhận thức được nhiều hạn chế của nó, chẳng hạn như ko với khả năng kéo trị giá từ những cột qua bên trái của cột tra cứu hoặc giới hạn 255 ký tự cho một trị giá tra cứu. Và vì thế cho nên kết hợp index và match là giải pháp mà nhiều người lựa chọn để thay thê
Xem thêm: Sự kết hợp hàm INDEX và MATCH trong Excel – Sự thay thế tốt hơn cả hàm VLOOKUP
Hàm INDEX / MATCH tốt hơn VLOOKUP ở nhiều khía cạnh:
- Ko với vấn đề với dò tìm bên trái.
- Ko giới hạn kích thước trị giá tra cứu.
- Ko yêu cầu phân loại (VLOOKUP với kết quả 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à loại bỏ những cột trong một bảng mà ko cần cập nhật mỗi công thức liên quan.
Và cuối cùng nhưng ko kém phần quan yếu, INDEX / MATCH ko làm chậm Excel của bạn như nhiều hàm Vlookup đã làm.
Bạn sử dụng INDEX / MATCH – hàm index kết hợp 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 chúng ta ra với tên hành tinh (planet name) trở thành cột trước tiên bên phải, thì hàm công thức INDEX / MATCH vẫn lấy trị giá thích hợp từ cột bên trái mà ko gặp bất kì trở ngại nào.
- Sử dụng hàm INDEX để lấy Một 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à bạn với thể với được một dải từ một danh sách những dải.
Giả sử, bạn với nhiều danh sách với số lượng những mục khác nhau.
Trước hết, bạn đặt tên cho mỗi dải trong mỗi danh sách; Ví như với thể đặt là PlanetsD và MoonsD trong ví dụ này:
Tuy nhiên, bảng Moons còn chưa hoàn chỉnh, với 176 mặt trăng tự nhiên được biết tới trong Hệ Mặt trời của chúng ta, Jupiter với 63 và vẫn còn nhiều hơn nữa. Đối với ví dụ này, tôi chọn 11 mẫu ngẫu nhiên.
Giả sử rằng PlanetsD là dải Một của bạn và MoonsD nằm trong dải Hai và ô B1 là nơi bạn đặt số dãy, bạn với thể sử dụng công thức Index sau để tính trị giá trung bình của những trị giá trong dải ô được chọn:
= AVERAGE (INDEX ((PlanetsD, MoonsD),,, B1))
Hãy lưu ý rằng hiện tạ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 công thức biết phạm vi dải nào được lựa chọn.
Trong hình bên 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ì dải MoonsD đứng thứ Hai trong đối số tham chiếu.
Nếu bạn làm việc với nhiều danh sách và ko muốn nhớ những số liên quan, bạn với thể sử dụng thêm hàm IF để làm điều này cho 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 thuần và dễ nhớ mà bạn muốn người tiêu dùng nhập vào ô B1 thay vì số. Xin lưu ý rằng, để công thức làm việc xác thực, văn bản trong B1 phải xác thực như nhau (ko phân biệt chữ hoa chữ thường) như trong những thông số của IF, nếu ko 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, bạn với thể sử dụng Excel Data Validation để tạo danh sách tùy chọn để tránh lỗi chính tả và lỗi trang in:
Cuối cùng, để làm cho công thức INDEX của bạn hoàn toàn tuyệt vời, bạn với thể đặt nó trong hàm IFERROR sẽ nhắc người tiêu dùng chọn một mục từ danh sách theo mục, nếu ko với 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 cách bạn sử dụng công thức INDEX trong Excel. Tôi kỳ vọng những ví dụ này cho bạn 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!
Để với thể ứng dụng tốt Excel vào trong công việc, chúng ta ko chỉ nắm vững được những hàm mà còn phải sử dụng tốt cả những phương tiện của Excel. Những hàm tăng giúp ứng dụng tốt vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những phương tiện thường sử dụng là Data validation, Conditional formatting, Pivot table… Toàn bộ những tri thức này những bạn đều với thể học được trong khóa học: EX101 – Excel từ cơ bản tới tăng dành cho người đi làm
