NỘI DUNG BÀI VIẾT
Hàm OFFSET Trong Excel Và Cách Sử Dụng Để Tham Chiếu Dữ Liệu Của Bảng Tính
Hàm OFFSET trong Excel thường được sử dụng để tính toán dữ liệu một ô, một dãy trong bảng tính dựa vào vùng tham chiếu được cho sẵn. Ngoài ra, bạn cũng có thể kết hợp hàm này với các hàm khác để hỗ trợ cho công việc của mình hiệu quả hơn. Sau đây bài viết sẽ hướng dẫn sử dụng hàm OFFSET trong Excel để tham chiếu dữ liệu của bảng tính nhé!
1 –Hàm OFFSET là gì?
Hàm OFFSET là hàm được dùng để tính toán dữ liệu một ô, một dãy trong bảng tính dựa vào một vùng tham chiếu được cho sẵn.
Hàm OFFSET được ứng dụng trong Excel để:
- Tính toán dữ liệu của một ô hay một dãy trong bảng tính nhanh hơn, tiện lợi hơn.
- Ngoài ra, hàm này còn được sử dụng kết hợp với các hàm khác để hỗ trợ hoàn thành công việc nhanh hơn.
2 – Công thức của hàm OFFSET
Cú pháp: =OFFSET(reference, rows, cols,[height],[width])
Trong đó:
- Reference: Được lấy làm vùng cơ sở tham chiếu để tạo ra vùng tham chiếu mới.
- Rows: Số dòng bên trên hoặc bên dưới reference, tính từ ô đầu tiên ở góc trên bên trái của reference.
- Cols: Là số cột bên trái hoặc bên phải của reference, tính từ ô đầu tiên ở góc trên bên trái của reference.
- Height: Là số dòng của vùng tham chiếu cần trả về.
- Width: Là số cột của vùng tham chiếu cần trả về (Width phải là số dương).
3 – Những điều lưu ý khi sử dụng hàm OFFSET
- Khi trang tính báo về lỗi #REF tức là đối số rows và cols của bạn đã khiến cho vùng tham chiếu được trả về vượt quá phạm vi quy định của trang tính.
- Nếu bạn không nhập đối số height và width thì kết quả tham chiếu trả về sẽ được mặc định có chiều cao và độ rộng giống với vùng tham chiếu.
- Còn nếu đối số height và width của bạn lớn hơn 1 thì bạn phải sử dụng công thức mảng hàm Offset nếu không thì trang tính sẽ báo lỗi #VALUE!. Đồng thời, bạn nên lưu ý về đối số height và width đều phải là số không âm nhé!
4 – Ví dụ cơ bản của hàm OFFSET
Bước 1: Trong bảng dữ liệu tính Excel, bạn nhập hàm =OFFSET(B3,3,2) vào ô tham chiếu muốn hiển thị kết quả.
Bước 2: Nhấn phím Enter để xem kết quả.
5 – Hàm OFFSET kết hợp với những hàm khác
Hàm OFFSET kết hợp với hàm SUM
Bước 1: Trong bảng tính Excel, bạn nhập hàm =SUM(B2:OFFSET(B9, -1,0)) vào ô muốn hiển thị kết quả để tính tổng giá trị thưởng ở cột B.
Bước 2: Nhấn phím Enter để hiển thị kết quả.
Hàm OFFSET kết hợp với hàm AVERAGE, MAX, MIN
Bạn có thể lồng các hàm AVERAGE, MAX, MIN cùng với hàm OFFSET
Bước 1: Để tính giá trị trung bình trong bảng, bạn hãy nhập hàm =AVERAGE(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1)).
Bước 2: Nhấn phím Enter để hiển thị kết quả.
Bước 3: Để tính số lớn nhất trong dải, bạn nhập =MAX(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1)).
Bước 4: Nhấn phím Enter để hiển thị kết quả.
Bước 5: Để tìm số nhỏ nhất trong dải, bạn nhập =MIN(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1)).
Bước 6: Nhấn phím Enter để hiển thị kết quả.
Hàm OFFSET kết hợp với hàm VLOOKUP
Bên cạnh đó, chúng ta có thể kết hợp hàm VLOOKUP với hàm OFFSET.
Bước 1: Bạn hãy nhập hàm =VLOOKUP(B1,A5:B11,2,FALSE) vào ô tham chiếu muốn hiển thị kết quả.
Bước 2: Nhấn phím Enter để hiển thị kết quả.
Như bạn đã biết, các tra cứu đơn giản theo chiều dọc và ngang trong Excel được thực hiện với hàm VLOOKUP hoặc hàm HLOOKUP. Tuy nhiên, các hàm này có quá nhiều hạn chế và thường là rắc rối lớn trong việc tạo công thức tra cứu mạnh hơn và phức tạp hơn. Vì vậy, để thực hiện tra cứu “một cách tinh vi hơn” trong các bảng Excel, bạn phải tìm kiếm các lựa chọn thay thế như hàm INDEX, MATCH và OFFSET.
Ví dụ 1. Công thức OFFSET cho một Vlookup dò bên trái trong Excel
Một trong những hạn chế đáng kể nhất của hàm Excel VLOOKUP là không có khả năng dò được giá trị bên trái của nó, có nghĩa là VLOOKUP chỉ có thể trả lại một giá trị ở bên phải cột tra cứu.
Trong bảng tra cứu mẫu, có hai cột – tên tháng (month) (cột A) và tiền thưởng (Bonus) (cột B). Nếu bạn muốn nhận được tiền thưởng cho một tháng nhất định, công thức VLOOKUP đơn giản này sẽ hoạt động mà không gặp trở ngại:
= VLOOKUP (B1, A5: B11, 2, FALSE)
Tuy nhiên, ngay khi bạn tráo đổi các cột trong bảng tra cứu, điều này sẽ ngay lập tức dẫn đến lỗi #N/A:
Để xử lý một tra cứu bên trái, bạn cần một hàm linh hoạt hơn mà không thực sự quan tâm đến vị trí của cột đó ở đâu. Một trong những giải pháp có thể sử dụng chính là kết hợp các hàm INDEX và MATCH. Một cách làm khác chính là sử dụng OFFSET, MATCH và ROWS:
=OFFSET(lookup table, MATCH(lookup value, OFFSET(lookup table, 0, 1, ROWS(lookup table), 1) ,0) -1, 0, 1, 1)
Trong ví dụ của chúng tôi, bảng tra cứu là A5: B9 và giá trị tra cứu nằm trong ô B1, vì vậy công thức trên sẽ triển khai thành:
= OFFSET (A5: B9, MATCH (B1, OFFSET (A5: B9, 0, 1, ROWS (A5: B9), 1), 0) -1, 0, 1, 1)
Tôi biết công thức trông hơi vụng về, nhưng nó thực sự được việc 🙂
Ví dụ 2. Làm thế nào sử dụng Vlookup dò bên trên trong Excel
Như trường hợp với Excel Vlookup không thể dò giá trị bên trái, chiều ngang – và hàm HLOOKUP – không thể dò giá trị bên trên để xuất ra một giá trị.
Nếu bạn cần quét một hàng phía trên để tìm một giá trị phù hợp, công thức OFFSET / MATCH / ROWS có thể giúp bạn một lần nữa, nhưng lần này bạn sẽ phải tăng cường hàm COLUMNS như sau:
=OFFSET(lookup table, 0, MATCH(lookup value, OFFSET(lookup table, ROWS(lookup table) -1, 0, 1, COLUMNS(lookup table)), 0) -1, 1, 1)
Giả sử rằng bảng tra cứu là E4: I5 và giá trị tra cứu là trong ô E1, công thức đi như sau:
= OFFSET (E4: I5, 0, MATCH (E1, OFFSET (E4: I5, ROWS (E4: I5) -1, 0, 1, COLUMNS (E4: I5)), 0) -1, 1, 1)
Ví dụ 3. tra cứu hai chiều (theo cột và giá trị hàng)
Tra cứu hai chiều trong Excel trả về một giá trị dựa trên các kết quả trùng khớp trong các hàng và cột. Và bạn có thể sử dụng công thức mảng tra cứu 2 chiều sau để tìm một giá trị tại giao điểm của một hàng và cột yêu cầu:
=OFFSET(lookup table, MATCH(row lookup value, OFFSET(lookup table, 0, 0, ROWS(lookup table), 1), 0) -1, MATCH(column lookup value, OFFSET(lookup table, 0, 0, 1, COLUMNS(lookup table)), 0) -1)
Biết rằng:
- Bảng tra cứu (lookup table) là A5: G9
- Giá trị phù hợp trên các hàng là B2
- Giá trị phù hợp trên các cột là B1
Bạn sẽ viết được công thức tra cứu hai chiều sau đây:
= OFFSET (A5: G9, MATCH (B2, OFFSET (A5: G9, 0, 0, ROWS (A5: G9), 1), 0) -1, MATCH (B1, T OFFT (A5: G9, 0, 0, 1, COLUMNS (A5: G9)), 0) -1)
Đó không phải là thứ dễ nhớ, phải không? Ngoài ra, đây là một công thức mảng, do đó đừng quên nhấn Ctrl + Shift + Enter để nhập chính xác dữ liệu.
Hàm offset kết hợp với hàm Match
Công thức:
=OFFSET(tham_chiếu_ô; MATCH(khóa_tìm_kiếm; dải_ô; loại_tìm_kiếm); cột_bù_trừ; chiều cao; chiều rộng)</loại_tìm_kiếm>
Trong đó:
+ tham_chiếu_ô: Ô cố định ban đầu để xác định hàng_bù_trừ và cột_bù_trừ.
+ MATCH(khóa_tìm_kiếm; dải_ô; loại_tìm_kiếm)
+ cột_bù_trừ: Số cột để bù trừ.
+ chiều cao: Chiều cao của dải ô trả về, bắt đầu từ đích bù trừ.
+ chiều rộng: Chiều rộng của dải ô trả về, bắt đầu từ đích bù trừ.
Ví dụ minh họa:
Cho bảng dữ liệu gồm các trường: MÃ SP, TÊN SP, HÃNG, GIÁ BÁN.
Kết hợp MATCH và OFFSET để tìm giá bán của sản phẩm.
Công thức:
=OFFSET($A$1;MATCH(F2;$B$2:$B$10;0);3)
Giải thích:
+ $A$1: Ô cố định để xác định hàng và cột bù trừ.
+ MATCH(F2;$B$2:$B$10;0): Xác định số hàng dựa vào vị trí của tên sản phẩm trong cột TÊN SP.
+ 3: Từ ô A1 đến cột D (GIÁ BÁN) là 3.
Kết hợp hàm MATCH và OFFSET
