01 trên 02
Hàm INDEX của Excel - Biểu mẫu mảng
Tổng quan chức năng Excel INDEX
Nói chung, hàm INDEX có thể được sử dụng để tìm và trả lại một giá trị cụ thể hoặc tìm tham chiếu ô tới vị trí của giá trị đó trong một trang tính.
Có hai dạng hàm INDEX có sẵn trong Excel: Biểu mẫu Mảng và Biểu mẫu Tham chiếu.
Sự khác biệt chính giữa hai dạng của hàm là:
- biểu mẫu mảng trả về giá trị dữ liệu nằm tại điểm giao nhau của một hàng và cột dữ liệu cụ thể;
- Biểu mẫu tham chiếu trả về tham chiếu ô của điểm giao nhau của một cột và hàng cụ thể.
Hàm INDEX của Excel - Biểu mẫu mảng
Một mảng thường được coi là một nhóm các ô liền kề trong một trang tính. Trong hình trên, mảng sẽ là khối ô từ A2 đến C4.
Trong ví dụ này, dạng mảng của hàm INDEX trong ô C2 trả về giá trị dữ liệu - Widget - được tìm thấy tại điểm giao nhau của hàng 3 và cột 2.
Cú pháp và đối số của hàm INDEX (Mảng mẫu)
Cú pháp của hàm liên quan đến cách bố trí của hàm và bao gồm tên, dấu ngoặc, dấu phẩy và các đối số của hàm .
Cú pháp cho hàm INDEX là:
= INDEX (Mảng, Row_num, Cột_num)
Mảng - Tham chiếu ô cho phạm vi ô được tìm kiếm theo hàm cho thông tin mong muốn
Row_num (tùy chọn) - Số hàng trong mảng mà từ đó trả về một giá trị. Nếu đối số này bị bỏ qua, Column_num là bắt buộc.
Column_num (tùy chọn) - Số cột trong mảng mà từ đó trả về một giá trị. Nếu đối số này bị bỏ qua, Row_num là bắt buộc.
- Đối với cả đối số Row_num và Column_num , có thể nhập số hàng và cột thực tế hoặc tham chiếu ô đến vị trí của thông tin này trong trang tính.
Ví dụ về hàm INDEX (Mảng mẫu)
Như đã đề cập, ví dụ trong hình trên sử dụng biểu mẫu Array của hàm INDEX để trả về thuật ngữ Widget từ danh sách kiểm kê.
Thông tin bên dưới bao gồm các bước được sử dụng để nhập hàm INDEX vào ô B8 của trang tính.
Các bước sử dụng tham chiếu ô cho đối số Row_num và Column_num , thay vì nhập trực tiếp các số này.
Nhập hàm INDEX
Các tùy chọn để nhập hàm và đối số của nó bao gồm:
- Nhập hàm đầy đủ: = INDEX (A2: C4, B6, B7) vào ô B8
- Chọn hàm và các đối số của nó bằng cách sử dụng hộp thoại hàm INDEX
Mặc dù có thể chỉ cần gõ chức năng hoàn chỉnh theo cách thủ công, nhiều người thấy dễ dàng hơn khi sử dụng hộp thoại để nhập các đối số của hàm.
Các bước dưới đây sử dụng hộp thoại để nhập các đối số của hàm.
Mở hộp thoại
Vì có hai dạng của hàm - mỗi hàm có các đối số riêng của chúng - mỗi biểu mẫu yêu cầu một hộp thoại riêng biệt.
Kết quả là, có một bước bổ sung trong việc mở hộp thoại hàm INDEX không xuất hiện với hầu hết các hàm Excel khác. Bước này liên quan đến việc chọn biểu mẫu Array hoặc bộ tham chiếu của biểu mẫu tham chiếu .
Dưới đây là các bước được sử dụng để nhập hàm INDEX và các đối số vào ô B8 bằng cách sử dụng hộp thoại của hàm.
- Bấm vào ô B8 trong trang tính - đây là nơi hàm sẽ được định vị
- Nhấp vào tab Công thức của trình đơn ribbon
- Chọn tra cứu và tham khảo từ ruy-băng để mở danh sách thả xuống chức năng
- Bấm vào INDEX trong danh sách để mở hộp thoại Chọn đối số - cho phép bạn chọn giữa các biểu mẫu mảng và tham chiếu của hàm
- Nhấp vào mảng, row_num, tùy chọn column_num
- Nhấp vào OK để mở hàm INDEX - Hộp thoại biểu mẫu mảng
Nhập các đối số của hàm
- Trong hộp thoại, nhấp vào dòng Array
- Đánh dấu các ô từ A2 đến C4 trong trang tính để nhập phạm vi vào hộp thoại
- Nhấp vào dòng Row_num trong hộp thoại
- Nhấp vào ô B6 để nhập tham chiếu ô đó vào hộp thoại
- Nhấp vào dòng Column_num trong hộp thoại
- Nhấp vào ô B7 để nhập tham chiếu ô đó vào hộp thoại
- Nhấn OK để hoàn thành chức năng và đóng hộp thoại
- Từ Gizmo xuất hiện trong ô B8 vì nó là thuật ngữ trong ô giao nhau với hàng thứ ba và cột thứ hai của phần khoảng không quảng cáo
- Khi bạn bấm vào ô B8, hàm đầy đủ = INDEX (A2: C4, B6, B7) xuất hiện trong thanh công thức phía trên trang tính
Giá trị lỗi chức năng chỉ mục
Các giá trị lỗi thường gặp liên quan đến hàm INDEX - Dạng mảng là:
#GIÁ TRỊ! - Xảy ra nếu đối số Row_num , Column_num không phải là số.
#REF! - Xảy ra nếu một trong hai:
- Đối số Row_num lớn hơn số hàng trong phạm vi đã chọn;
- Đối số Col_num lớn hơn số cột trong phạm vi đã chọn.
Hộp thoại Ưu điểm
Ưu điểm khi sử dụng hộp thoại để nhập dữ liệu cho các đối số của hàm bao gồm:
- Hộp thoại sẽ chăm sóc cú pháp của hàm - giúp việc nhập các đối số của hàm một cách dễ dàng hơn mà không phải nhập dấu bằng, dấu ngoặc hoặc dấu phẩy hoạt động như dấu phân cách giữa các đối số.
- Tham chiếu ô, chẳng hạn như B6 hoặc B7, có thể được nhập vào hộp thoại bằng cách trỏ , liên quan đến việc nhấp vào các ô đã chọn bằng chuột thay vì nhập chúng. Không chỉ dễ dàng hơn mà còn giúp giảm lỗi trong công thức do tham chiếu ô không đúng.
02 trên 02
Hàm INDEX của Excel - Biểu mẫu tham khảo
Hàm INDEX của Excel - Biểu mẫu tham khảo
Biểu mẫu tham chiếu của hàm trả về giá trị dữ liệu của ô nằm ở điểm giao nhau của một hàng và cột dữ liệu cụ thể.
Mảng tham chiếu có thể bao gồm nhiều dãy không liền kề như được hiển thị trong hình trên.
Cú pháp và đối số hàm INDEX (Tham chiếu biểu mẫu)
Cú pháp và đối số cho hàm tham chiếu hàm INDEX là:
= INDEX (Tham chiếu, Row_num, Column_num, Area_num)
Tham khảo - (bắt buộc) tham chiếu ô cho phạm vi ô được tìm kiếm theo hàm cho thông tin mong muốn.
- nếu nhiều phạm vi không liền kề được nhập cho đối số này, các dải phải được bao quanh bởi một tập hợp dấu ngoặc tròn riêng biệt như được hiển thị trong công thức INDEX: = INDEX ((A1: A5, C1: E1, C4: D5), B7 , B8) lấy từ hình trên
Row_num - số hàng trong mảng mà từ đó trả về một giá trị.
- tùy chọn cho một hàng
- cần thiết cho một cột và nhiều hàng
Column_num - số cột trong mảng mà từ đó trả về một giá trị.
- tùy chọn cho một cột
- cần thiết cho một hàng và nhiều cột
Ghi chú: Đối với cả đối số Row_num và Column_num , có thể nhập số hàng và cột thực tế hoặc tham chiếu ô đến vị trí của thông tin này trong trang tính.
Area_num (tùy chọn) - nếu đối số Tham chiếu chứa nhiều phạm vi không liền kề, đối số này sẽ chọn phạm vi ô nào cần trả về dữ liệu. Nếu bỏ qua, hàm sử dụng phạm vi đầu tiên được liệt kê trong đối số Tham chiếu .
- Phạm vi đầu tiên được nhập trong đối số Tham chiếu được đánh số 1;
- thứ hai là 2;
- thứ ba là 3, v.v.
Ví dụ về hàm INDEX (Mẫu tham chiếu)
Ví dụ trong hình trên sử dụng biểu mẫu Tham chiếu của hàm INDEX để trả về tháng 7 từ khu vực 2 của cơn thịnh nộ A1 đến E1.
Thông tin bên dưới bao gồm các bước được sử dụng để nhập hàm INDEX vào ô B10 của trang tính.
Các bước sử dụng tham chiếu ô cho đối số Row_num, Column_num và Area_num , thay vì nhập trực tiếp các số này.
Nhập hàm INDEX
Các tùy chọn để nhập hàm và đối số của nó bao gồm:
- Nhập hàm đầy đủ: = INDEX ((A1: A5, C1: E1, C4: D5), B7, B8) vào ô B10
- Chọn hàm và các đối số của nó bằng cách sử dụng hộp thoại hàm INDEX
Mặc dù có thể chỉ cần gõ chức năng hoàn chỉnh theo cách thủ công, nhiều người thấy dễ dàng hơn khi sử dụng hộp thoại để nhập các đối số của hàm.
Các bước dưới đây sử dụng hộp thoại để nhập các đối số của hàm.
Mở hộp thoại
Vì có hai dạng của hàm - mỗi hàm có các đối số riêng của chúng - mỗi biểu mẫu yêu cầu một hộp thoại riêng biệt.
Kết quả là, có một bước bổ sung trong việc mở hộp thoại hàm INDEX không xuất hiện với hầu hết các hàm Excel khác. Bước này liên quan đến việc chọn biểu mẫu Array hoặc bộ tham chiếu của biểu mẫu tham chiếu .
Dưới đây là các bước được sử dụng để nhập hàm INDEX và các đối số vào ô B10 bằng cách sử dụng hộp thoại của hàm.
- Bấm vào ô B8 trong trang tính - đây là nơi hàm sẽ được định vị
- Nhấp vào tab Công thức của trình đơn ribbon
- Chọn tra cứu và tham khảo từ ruy-băng để mở danh sách thả xuống chức năng
- Bấm vào INDEX trong danh sách để mở hộp thoại Chọn đối số - cho phép bạn chọn giữa các biểu mẫu mảng và tham chiếu của hàm
- Nhấp vào tham chiếu, tùy chọn row_num, column_num, area_num
- Nhấp vào OK để mở hàm INDEX - Hộp thoại biểu mẫu tham chiếu
Nhập các đối số của hàm
- Trong hộp thoại, nhấp vào dòng Tham chiếu
- Nhập một dấu ngoặc tròn mở " ( " trên dòng này trong hộp thoại
- Đánh dấu các ô A1 đến A5 trong trang tính để nhập phạm vi sau dấu ngoặc mở
- Nhập dấu phẩy để hoạt động như dấu phân cách giữa các phạm vi thứ nhất và thứ hai
- Đánh dấu các ô C1 đến E1 trong trang tính để nhập phạm vi sau dấu phẩy
- Nhập dấu phẩy thứ hai để hoạt động như dấu phân cách giữa phạm vi thứ hai và thứ ba
- Đánh dấu các ô từ C4 đến D5 trong trang tính để nhập phạm vi sau dấu phẩy
- Nhập dấu ngoặc tròn " ) " sau phạm vi thứ ba để hoàn tất đối số Tham chiếu
- Nhấp vào dòng Row_num trong hộp thoại
- Nhấp vào ô B7 để nhập tham chiếu ô đó vào hộp thoại
- Nhấp vào dòng Column_num trong hộp thoại
- Nhấp vào ô B8 để nhập tham chiếu ô đó vào hộp thoại
- Nhấp vào dòng Area_num trong hộp thoại
- Nhấp vào ô B9 để nhập tham chiếu ô đó vào hộp thoại
- Nhấn OK để hoàn thành chức năng và đóng hộp thoại
- Tháng 7 xuất hiện trong ô B10 vì nó là tháng trong ô giao nhau với hàng đầu tiên và cột thứ hai của vùng thứ hai (khoảng C1 đến 1)
- Khi bạn bấm vào ô B8, hàm đầy đủ = INDEX ((A1: A5, C1: E1, C4: D5), B7, B8) xuất hiện trong thanh công thức phía trên trang tính
Giá trị lỗi chức năng chỉ mục
Các giá trị lỗi thường gặp được kết hợp với hàm INDEX - Biểu mẫu tham chiếu là:
#GIÁ TRỊ! - Xảy ra nếu đối số Row_num , Column_num hoặc Area_num không phải là số.
#REF! - Xảy ra nếu:
- Đối số Row_num lớn hơn số hàng trong phạm vi đã chọn;
- Đối số Col_num lớn hơn số cột trong phạm vi đã chọn.
- Đối số Area_num lớn hơn số vùng trong phạm vi đã chọn.