Hướng dẫn sử dụng hàm OFFSET trong Excel

Làm thế nào để sử dụng hàm OFFSET hiệu quả?.Hãy cùng tìm hiểu hướng dẫn sử dụng hàm OFFSET trong Excel trong bài viết dưới đây.

Trong các hàm tham chiếu trong Excel, hàm OFFSET rất quan trọng vì có thể tạo nên các hàm tham chiếu cũng như các hàm tính toán khác.

Giới thiệu về hàm OFFSET

Công thức hàm OFFSET đóng vai trò hàm tham chiếu trong Excel như sau:

=OFFSET(reference, rows, cols, [height], [width])

3 đối số reference, rows và cols là 3 đối số bắt buộc người dùng phải nhập. Trong công thức chúng ta nhìn thấy 2 đối số còn lại height và width là 2 đối số tùy chọn có thể nhập hoặc bỏ qua.

Các đối số bắt buộc trong công thức hàm OFFSET

• reference: đối số này xác định điểm làm gốc của công thức hàm OFFSET. Là một ô hoặc một dải ô liền kề mà sau đó nó có thể bị thêm/bớt.
• rows: Là số hàng di chuyển lên hoặc xuống từ điểm làm gốc
• cols: Là số cột bạn muốn công thức di chuyển từ điểm làm gốc cols có thể là số dương hoặc số âm

Các đối số tùy chọn trong công thức hàm OFFSET

• height: Là chiều cao tính bằng số hàng hàm tham chiếu trong Excel trả về kết quả
• width: Là chiều rộng, tính bằng số cột tham chiếu trả về.

Cả hai đối số height và width luôn phải là số dương. Trong excel có rất nhiều hàm, hàm OFFSET cũng vậy.

Cú pháp của hàm OFFSET là gì?

=OFFSET (tham chiếu, hàng, cols, chiều cao, chiều rộng )

Trong đó: 

+ Tham chiếu: là khu vực mà bạn muốn tham chiếu đến phần nào đó

+ Hàng: có thể là tích cực hoặc tiêu cực.

+ Cols: Số lượng cột, ở bên trái hoặc bên phải, mà bạn muốn ô trên bên trái của tham chiếu được trả về để tham chiếu.

+ Chiều cao: theo số lượng hàng mà bạn muốn tham chiếu được trả về. Chiều cao là một số dương.

+ Chiều rộng: tùy chọn, theo số lượng cột, mà bạn muốn tham chiếu được trả về.

5 vấn đề về sử dụng hàm OFFSET hiệu quả

Hàm OFFSET được nhập theo công thức:

=OFFSET(A1,3,1)

Công thức OFFSET Excel lấy ô tham chiếu bắt đầu từ điểm gốc A1 ô tham chiếu của chúng ta là ô B4.

• Hàm OFFSET trong Excel không di chuyển bất kỳ ô hoặc dải nào, nó chỉ trả về một tham chiếu trong phạm vi dữ liệu
• Đối số reference phải là một ô hoặc một dải ô liền kề
• Đối với công thức hàm OFFSET trả về kết quả là một dải ô đối số rows và cols
• Hàm OFFSET có thể được kết hợp lồng trong bất kỳ hàm Excel nào hoặc dải trong các đối số của nó.
• Nếu đối số rows và cols một ô tham chiếu ở rìa của trang tính Excel công thức hàm tham chiếu trong Excel sẽ xuất hiện lỗi #REF!.

Sử dụng công thức: =SUM(OFFSET(A1,3,1,1,3)) chúng ta thu được kêt quả chính là tổng dải ô tham chiếu liền kề được xác định bởi hàm OFFSET.

Công thức OFFSET Excel

Các trường hợp sử dụng hàm OFFSET

Tạo các dải động bằng hàm OFFSET ví dụ như A1:B4 là tham chiếu tĩnh bạn không thể thay đổi phạm vi tham chiếu. Nếu bạn thường xuyên cập nhật dữ liệu trên bảng Excel hàm OFFSET là sự lựa chọn tuyệt vời khi không muốn làm ảnh hưởng đến kết quả hàm tham chiếu trong Excel. OFFSET Excel cho phép bạn tạo các dải động bạn thay đổi dữ liệu bảng tính hay thêm các hàng, cột bạn vẫn sẽ tìm được dữ liệu mình cần.

Lấy dải từ ô gốc bằng hàm OFFSET trong Excel hàm OFFSET sẽ cứu bạn khỏi việc mỏi mắt tra cứu dữ liệu nếu quên mất địa chỉ thực của cả dải ô cần tìm kiếm thông tin.

Cách sử dụng hàm OFFSET

Sử dụng hàm OFFSET lồng trong hàm SUM

Công thức hàm SUM kết hợp hàm OFFSET có chức năng tính tổng các ô tham chiếu trong OFFSET Excel cả khi bảng tính được thay đổi. Chúng ta không cần phải thay thế hàm SUM thủ công vào trang tính nữa. Thay vì dùng hàm SUM cơ bản cần cập nhật hàm thủ công, thì công thức hàm SUM kết hợp hàm OFFSET thuận tiện hơn.

Nhập các tham số sau đây vào đối số trong công thức hàm OFFSET:

• reference: Ô B9 chứa giá trị tổng số liệu.
• rows: bắt buộc phải là số âm -1.
• Cols: Nhập 0 vì bạn không muốn thay đổi cột.

Công thức mẫu hàm SUM kết hợp hàm OFFSET:

=SUM(first cell:OFFSET(cell with total, -1,0))

Ta sẽ có công thức hàm OFFSET như sau:

=SUM(B2:OFFSET(B9, -1,0))

Sử dụng hàm OFFSET tổng hợp N dòng cuối của một danh sách:

Chúng ta sẽ sử dụng hàm OFFSET kết hợp với các hàm SUM và hàm COUNT/ COUNTA như sau:

 =SUM(OFFSET(B1,COUNT(B:B)-E2+1,0, E2,1)) hoặc

=SUM(OFFSET(B1,COUNTA(B:B)-E2,0,E2,1)))

Trong công thức hàm OFFSET:

• reference: Nhập tiêu đề của cột chứa các giá trị cần tính tổng
• rows: Sử dụng thêm hàm COUNT hoặc COUNTA để tính số hàng.
• cols: Nhập số cột điều chỉnh là 0.
• width: Nhập 1 tương ứng với số cột.
• height: Nhập ô E1 chứa số hàng được tổng hợp

Lưu ý rằng công thức này sẽ chỉ hoạt động chính xác trên một cấu trúc. Đối với các cấu trúc bảng khác nhau, bạn cần thực hiện một số điều chỉnh trong công thức OFFSET Excel / COUNTA hàm tham chiếu trong Excel.

Sử dụng hàm OFFSET lồng trong hàm AVERAGE, MAX, MIN

Bằng cách sử dụng hàm OFFSET, các công thức để tính là tên của hàm đầu tiên:

• Công thức hàm OFFSET lồng trong hàm AVERAGE để tính giá trị trung bình: =AVERAGE(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1))
• Công thức hàm OFFSET lồng trong hàm MAX để tìm giá trị nhỏ nhất trong bảng: =MIN(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1))
• Công thức hàm OFFSET lồng trong hàm MAX để tìm giá trị lớn nhất trong bảng: =MAX(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1))

Công thức hàm OFFSET luôn luôn tham chiếu đến ô cuối cùng trong cột. Bạn sẽ không phải lo về việc Excel trả sai kết quả hay trả lỗi.

Sử dụng hàm OFFSET kết hợp hàm COUNTA để tạo dải động

Hàm OFFSET kết hợp hàm COUNTA có thể giúp bạn tạo một dải động trong trường hợp bạn muốn tạo danh sách tự động cập nhật.

Nhập công thức hàm OFFSET kết hợp hàm COUNTA như sau:

=OFFSET(Sheet_Name!$A$1,0,0,COUNTA(Sheet_Name!$A:$A),1)

Sử dụng hàm OFFSET thay thế các hàm LOOKUP trong Excel

Hàm VLOOKUP trong Excel chỉ có khả năng trả lại một giá trị ở bên phải cột tra cứu, không có khả năng dò được giá trị bên trái của nó.

Giải pháp sử dụng hàm OFFSET kết hợp hàm MATCH và hàm ROWS với công thức như sau:

=OFFSET(lookup table, MATCH(lookup value,OFFSET(lookup table,0,1, ROWS(lookup table),1),0)-1,0,1,1)

Ta có công thức của hàm OFFSET như sau:

=OFFSET(A5:B11,MATCH(B1,OFFSET(A5:B11,0,1,ROWS(A5:B11),1),0)-1,0,1,1)

Sử dụng hàm OFFSET thay hàm HLOOKUP tra cứu bên trên trong Excel

Hàm HLOOKUP không thể dò giá trị trên hàng chứa giá trị tham chiếu trong phạm vi dữ liệu để xuất ra một giá trị. Bạn hãy sử dụng công thức hàm OFFSET kết hợp hàm MATCH và hàm ROWS, bạn cần thêm hàm COLUMNS để công thức dò tìm 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)

Sử dụng hàm OFFSET thay các hàm LOOKUP tra cứu hai chiều trong Excel

Tra cứu hai chiều trong Excel để tìm kiếm dữ liệu tại vị trí giao điểm các hàng và cột. Sử dụng công thức hàm OFFSET với mảng tra cứu 2 chiều như sau:

=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)

Để áp dụng công thức trên, chúng ta xác định các yếu tố sau:

• 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

Chúng ta có 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,OFFSET(A5:G9,0,0,1,COLUMNS(A5:G9)),0)-1)

Ấn tổ hợp phím Ctrl + Shift + Enter để Excel trả về kết quả chính xác.

Những nhược điểm của hàm OFFSET

Hàm OFFSET trong Excel cũng có một số nhược điểm. Hàm OFFSET có thể khiến Excel chạy chậm luôn luôn “khát dữ liệu”.

Hàm OFFSET có thể gây khó khăn cho việc sửa lỗi hàm các tham chiếu được trả lại bởi hàm OFFSET là động chúng ta sẽ tốn rất nhiều thời gian để tìm và sửa lỗi

Các giải pháp khắc phục nhược điểm của hàm OFFSET trong Excel là sử dụng công cụ bảng trong Excel. Bạn chỉ cần nhập một công thức hàm OFFSET vào một ô trong bảng sao chép ô xuống các ô phía dưới đã có thể tạo ra một bảng Excel thay đổi dữ liệu phù hợp cho mỗi hàng.

Mỗi bảng trong trang tính Excel mặc định là Table1, Table2 bạn hoàn toàn có thể đổi tên bảng khi vào tab Design> Properties group> Table Name.

Sử dụng hàm INDEX trong Excel cũng có thể được dùng làm hàm tham chiếu trong Excel đôi với các dải động. Hàm INDEX so với hàm OFFSET là hàm INDEX không tự thay đổi nên nó sẽ không làm chậm Excel của bạn.

Sử dụng hàm INDIRECT trong Excel giúp chúng ta tạo tham chiếu cho dải động từ nhiều nguồn các dải ô được đặt tên các ô giá trị, giá trị và văn bản của ô. Hàm INDIRECT cũng có thể tự động tham chiếu một bảng tính hoặc một trang tính Excel.

Có thể nói hàm OFFSET là hàm tham chiếu trong Excel lợi hại bậc nhất vì có thể kết hợp được với các hàm khác. Để sử dụng hiệu quả hàm OFFSET hãy đọc thêm các bài viết về hàm Excel. Các bạn cần nắm vững công thức của các hàm tham chiếu và hàm tính toán trong Excel để biết cách thực hiện dễ dàng nhất.