Bạn đang xem bài viết Cách Sử Dụng Hàm Index Để Lấy Mảng Tham Chiếu Trong Excel được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
Hàm INDEX sẽ trả về kết quả được tham chiếu đến một phần tử của một hoặc nhiều mảng. Và khi nhìn vào hàm INDEX thì bạn sẽ biết vị trí của phần tử trong mảng được sử dụng.
Cách sử dụng hàm INDEX trong ExcelBây giờ bạn có thể mở Excel lên và sử dụng lệnh =INDEX( thì bạn sẽ thấy 2 cú pháp được hiển thị. Như vậy, trong Excel hàm INDEX có 2 cách sử dụng sau đây:
Cú pháp=INDEX(array; row_num; [column_num])
👉Trong đó:
array: là một mảng giá trị hay một Range các Cells liên tiếp nhau bao gồm hàng và cột.
row_num: là vị trí hàng trong array.
[column_num]: là vị trí cột trong array
Mô tảKhi sử dụng dạng mảng thì hàm INDEX sẽ trả về một phần tử trong mảng, kết quả trả về là của giao điểm row_num và [column_num].
Ví dụ👉Kết quả của hàm trên, mình sẽ có giá trị của giao điểm hàng thứ 2 và cột thứ 6 là Cells F3 trong Range A2:F6.
Cú pháp:=INDEX(reference; row_num; [column_num]; area_num)
👉Trong đó:
reference: tham chiếu đến một hay nhiều Range giá trị.
row_num: là vị trí hàng trong reference.
[column_num]: là vị trí cột trong reference
area_num: vị trị của vùng giá trị trong reference. Nếu trong reference sử dụng nhiều Range thì giá trị area_num sẽ qui định row_num và [column_num] thuộc Range nào.
Mô tả:Dạng tham chiếu của hàm INDEX cho phép chọn nhiều Range nếu các Range đó không liền kề nhau. Kết quả trả về là giao điểm row_num và [column_num] và nằm trong Range số area_num của reference.
1) Sử dụng hàm =INDEX((A2:F4;A7:F8);2;6;2) thì:
reference là (A2:F4;A7:F8)
row_num = 2 (các bạn lưu ý, ở đâu mình lấy area_num = 2 nên row_num = 2 là vị trí hàng thứ 2 trong Range A7:F8.
[column_num] = 6
area_num = 2, tức là Range A7:F8. Trong reference bạn có thể thêm nhiều Range giá trị và Range đầu tiên là 1.
👉Kết quả là giao điểm hàng thứ 2 và cột thứ 6 trong Range A7:F8
2) Ngoài ra bạn có thể kết hợp hàm INDEX với các hàm khác để tính toán trong các Range riêng. Ví dụ: sử dụng hàm =SUM(INDEX((A2:F4;A7:F8);0;6;1)) sẽ tính được tổng giá Range thứ nhất của reference hoặc =SUM(INDEX((A2:F4;A7:F8);0;6;2)) để tính tổng giá Range thứ 2 của reference.
✅ Trong cả 2 dạng của hàm INDEX thì nếu row_num =0 hoặc [column_num]=0 thì hàm INDEX sẽ trả về mảng là toàn bộ mảng của cột hoặc hàng tương ứng. Và nếu sử dụng với hàm SUM() như ví dụ ở trên thì kết quả sẽ là tổng các giá trị trong mảng của cột hoặc hàng.
👉Thông thường nếu bạn sử dụng hàm INDEX mà để đối số row_num =0 hoặc [column_num]=0 thì hàm sẽ trả về kết quả là #VALUE!. Vì vậy sau khi nhập hàm xong bạn sử dụng tổ hợp phím[CTRL + SHIFT + ENTER] thì hàm sẽ trả về giá trị đầu tiên trong mảng.
OK! Qua bài viết về cách sử dụng hàm INDEX thì mình hy vọng sẽ giúp các bạn giải quyết được vấn đề đang gặp phải.
Nhóm Hàm Tham Chiếu Trong Excel (Bài 5)
Trong bài học này, chúng tôi sẽ trình bày 3 hàm trong nhóm hàm tham chiếu gồm hàm VLOOKUP, hàm HLOOKUP và hàm INDEX. Công dụng, cách sử dụng và ví dụ chúng tôi sẽ trình bày ngay bên dưới.
Nhóm hàm tham chiếu trong excel – Hàm VLOOKUPHàm VLOOKUP được sử dụng để trích xuất thông tin từ bảng tham chiếu theo chiều dọc. Hãy quan sát bảng tham chiếu 1 (Bảng chức vụ) ở hình bên trên để thấy rõ cách thể hiện thông tin của bảng này.
Cú pháp hàm VLOOKUP trong excel
VLOOKUP(Trị dò, Bảng tham chiếu, Chỉ số cột tham chiếu, Cách dò)Trong đó: Trị dò thuộc bảng chính; Chỉ số cột tham chiếu tính từ trái qua phải và bắt đầu từ 1; Cách dò là 0 nếu Trị dò là chuỗi hoặc ngày tháng năm, Cách dò là 1 nếu Trị dò là số.
Ví dụ sử dụng hàm VLOOKUP trong excel (Lập công thức cho cột CHỨC VỤ của bảng Danh sách nhân viên công ty ABC) dựa vào bảng CHỨC VỤ
B9 là Mã chức vụ ( Trị dò); B3:C6 là bảng chức vụ ( Bảng tham chiếu luôn luôn dùng địa chỉ tuyệt đối); 2 là Chỉ số của cột tham chiếu (cột Chức vụ trong bảng chức vụ); vì B9 chứa chuỗi nên Cách dò là 0.
Kết quả sau khi hoàn thành
Nhóm hàm tham chiếu trong excel – Hàm HLOOKUPNếu VLOOKUP dò tìm theo chiều dọc thì HLOOKUP dò tìm theo chiều ngang (Bảng khu vực)
Cú pháp hàm HLOOKUP trong excel
HLOOKUP(Trị dò, Bảng tham chiếu, Chỉ số dòng tham chiếu, Cách dò)Ví dụ sử dụng hàm HLOOKUP trong excel (Lập công thức cho cột KHU VỰC của bảng Danh sách nhân viên công ty ABC) dựa vào bảng KHU VỰC
Kết quả sau khi hoàn thành
Nhóm hàm tham chiếu trong excel – Bài tập thực hànhCho bảng tính như hình và lập công thức cho những cột tô màu vàng
Câu 1: SỐ NGÀY THUÊ = NGÀY ĐI – NGÀY ĐẾN
Câu 2: TIỀN PHÒNG = Đơn giá phòng (Tra trong bảng ĐƠN GIÁ PHÒNG) * SỐ NGÀY THUÊ
Câu 3: Dựa vào 2 ký tự cuỗi của MÃ THANH TOÁN và tra trong bảng ĐƠN GIÁ KHẨU PHẦN ĂN để lập công thức cho cột ĐƠN GIÁ KHẨU PHẦN ĂN
Câu 4: TIỀN ĂN = SỐ NGÀY THUÊ * ĐƠN GIÁ KHẨU PHẦN ĂN
Câu 5: THÀNH TIỀN = TIỀN PHÒNG + TIỀN ĂN
Câu 6: TỶ LỆ GIẢM dựa vào SỐ NGÀY THUÊ và tra trong bảng TỶ LỆ GIẢM
Câu 7: PHẢI TRẢ = THÀNH TIỀN – TỶ LỆ GIẢM
Hàm Index Trong Excel: Công Thức Và Cách Sử Dụng
Quản trị mạng đã tổng hợp đến bạn đọc những hàm cơ bản nhất trong Excel, cũng như hướng dẫn một loạt cách sử dụng hàm: tìm kiếm dữ liệu với hàm Vlookup, hàm điều kiện IF, hàm SUM… Đây là những hàm cơ bản nhất mà bạn sẽ bắt gặp khi làm việc với tập tin Excel, giúp chúng ta hoàn thành bảng thống kê, tính toán số liệu của mình.
1. Hàm Index dạng Mảng trong ExcelHàm Index dạng mảng dùng với trường hợp nếu đối số thứ nhất của hàm là một hằng số mảng. Hàm INDEX dạng mảng có cú pháp như sau:
=INDEX(Array,Row_num,[Column_num])
Trong đó:
Array: vùng ô hoặc một hàng số mảng nào đó bắt buộc.
Row_num: chọn hàng trong mảng từ đó trả về một giá trị.
Column_num: chọn cột trong mảng từ đó trả về một giá trị.
Bạn đọc lưu ý bắt buộc phải có ít nhất một trong hai đối số Row_num và Column_num.
Bảng Excel 1: Chúng ta có bảng danh sách các loại vải, tìm tên loại vải biết loại vải đó ở hàng 2 cột 2.
=INDEX(B2:C6,2,2)
Ngay sau đó chúng ta sẽ được trả về giá trị vị trí A2 tương ứng với loại hàng Vải thô.
2. Hàm Index Excel dạng tham chiếuHàm Index dạng tham chiếu trả về tham chiếu của ô nằm ở giao của một hàng và một cột cụ thể. Chúng ta có công thức Index dạng tham chiếu như sau:
=INDEX(Reference,Row_num,[Column_num],[Area_num])
Trong đó:
Reference: vùng tham chiếu bắt buộc.
Row_num: chỉ số hàng từ đó trả về một tham chiếu, bắt buộc.
Column_num: chỉ số cột từ đó trả về một tham chiếu, tùy chọn.
Area_num: số của vùng ô sẽ trả về giá trị trong Reference. Nếu Area_num được bỏ qua thì hàm INDEX dùng vùng 1, tùy chọn.
Cũng với bảng dữ liệu Excel bên trên chúng ta thực hiện nhập công thức như bên dưới. Sau đó cũng nhấn Enter. Nhấp chuột tại ô C7 và nhập công thức sau:
=INDEX(B2:C6,2,1,1)
Kết quả trả về sẽ là tên loại mặt hàng Vải thô ở dòng B2.
Như vậy, chúng tôi đã hướng dẫn bạn cách sử dụng hàm INDEX dạng mảng và dạng tham chiếu. Hàm Index có thể tham chiếu đến bất cứ ô nào trong Excel, và cách thực hiện cũng không quá khó. Bạn có thể sử dụng hàm này và kết hợp với các hàm khác trong Excel để sử dụng hiệu quả hơn trong dữ liệu bảng tính.
Hàm Offset Trả Về Tham Chiếu Trong Excel
Mô tả
Hàm OFFSET() trả về tham chiếu tới một vùng, cách một ô hoặc phạm vi ô với số hàng và số cột được các bạn chỉ định.
Tham chiếu trả về có thể là một ô hoặc một phạm vi ô mà các bạn có thể chỉ rõ số hàng và số cột cần trả về.
Cú pháp=OFFSET(reference, rows, cols, height, width)
Trong đó:
– reference: là vùng tham chiếu mà các bạn muốn dựa vào đó để tạo vùng tham chiếu mới. Reference phải được tham chiếu tới một ô hoặc phạm vi ô liên tiếp nhau, nếu không hàm sẽ trả về giá trị lỗi.
– rows: là số hàng, lên trên hoặc xuống dưới reference, tính từ ô đầu tiên của reference. Số hàng có thể là số dương (phía dưới reference) hoặc số âm (phía trên reference).
– cols: là số cột, bên trái hoặc bên phải reference, tính từ ô đầu tiên của reference. Số cột có thể là số dương (bên phải reference) hoặc số âm (bên trái reference).
– height: là chiều cao được tính bằng số hàng mà các bạn muốn có tham chiếu trả về.
– width: là độ rộng được tính bằng số cột mà các bạn muốn có tham chiếu trả về.
Ghi chú– Height và width phải là số dương.
– Nếu bỏ qua height và width thì hàm OFFSET() sẽ mặc định là height và width của reference.
– Nếu rows và cols làm cho vùng tham chiếu vượt ra khỏi phạm vi của bảng tính thì hàm OFFSET() sẽ báo lỗi #REF!.
– Hàm OFFSET() không di chuyển hay làm thay đổi bất kỳ phần nào được chọn vì hàm chỉ trả về một tham chiếu. Các bạn có thể sử dụng hàm OFFSET() kết hợp với các hàm cần đến một đối số tham chiếu.
Ví dụ1. Trong ô B8 trả về giá trị của ô E6 thông qua ô tham chiếu cơ sở B5.
– Tại ô B8 các bạn nhập công thức: =OFFSET(B5,1,3,1,1)
Ví dụ tính tổng lương và thưởng của nhân viên Nguyễn Thị Dung bắt đầu từ ô B6 (lương của nhân viên đầu tiên).
Áp dụng hàm: =SUM(OFFSET(B6,3,0,1,2)) các bạn sẽ được kết quả như sau:
Cập nhật thông tin chi tiết về Cách Sử Dụng Hàm Index Để Lấy Mảng Tham Chiếu Trong Excel trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!