Công thức và cách sử dụng hàm OFFSET trong Excel đơn giản, dễ thực hiện

Công thức và cách sử dụng hàm OFFSET trong Excel đơn giản, dễ thực hiện!

Trong Excel hàm OFFSET được sử dụng để tham chiếu hay tính toán dữ liệu một ô, một dãy trong bảng tính. Hàm này sẽ tính toán dựa trên vùng tham chiếu được cho sẵn. Người dùng cũng có thể kết hợp hàm OFFSET với các hàm khác liên quan trong Excel để hỗ trợ công việc trên bảng tính được thuận tiện và tiết kiệm thời gian hơn. Nếu bạn chưa biết về cú pháp và cách sử dụng hàm OFFSET trong Excel hãy tham khảo ngay bài viết dưới đây từ Mega.

• Hàm OFFSET là hàm gì?
• Cú pháp và cách sử dụng hàm OFSET?
• Những lưu ý khi sử dụng hàm OFFSET trong excel
• Hàm OFFSET có thể kết hợp với những hàm nào?

 Công thức và cách sử dụng hàm OFFSET trong Excel đơn giản, dễ thực hiện!

I. Hàm OFFSET là hàm gì? Công thức hàm OFFSET trong Excel

Hàm OFFSET là hàm được dùng để tham chiếu hay 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 trước đó.

Hàm OFFSET trong excel được dùng để tính toán dữ liệu của một ô hay một dãy trong bảng tính nhanh và tiện lợi hơn. Ngoài ra, hàm OFFSET 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 chóng và hiệu quả hơn. Người dùng khi thao tác trên laptop chẳng hạn sẽ tiết kiệm được thời gian đáng kể khi thành thạo loại hàm này.

Hàm này được thực hiện trên hệ điều hành Windows 10 và phiên bản Excel 2010, 2013, Excel 2016, Excel 2019.

Cú pháp hàm OFFSET: =OFFSET(reference, rows, cols,[height],[width])

Trong đó:

+ Reference: Là vùng tham chiếu làm cơ sở cho hàm để tạo vùng tham chiếu mới.

+ Rows: Là 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 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ề. Height phải là số dương.

+ Width: Là số cột của vùng tham chiếu cần trả về. Width phải là số dương.

II. Những lưu ý khi sử dụng hàm OFFSET trong excel

Khi áp dụng công thức hàm OFFSET bạn cần chú ý một số điểm sau:

• Nếu các đối số rows và cols làm cho vùng tham chiếu trả về vượt khỏi phạm vi quy định của trang tính thì hàm sẽ báo lỗi #REF.
• Nếu bạn không nhập các đối số height và width thì tham chiếu trả về mặc định có cùng chiều cao và độ rộng với vùng tham chiếu.
• Nếu các đối số height và width lớn hơn 1 nghĩa là hàm phải trả về nhiều hơn 1 ô thì các bạn phải sử dụng công thức mảng hàm OFFSET nếu không sẽ nhận lỗi #VALUE.
• Đối số height và width phải là số dương (số không âm).
• 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.

III. Cách sử dụng hàm OFFSET trong excel

Ví dụ 1: Sử dụng hàm OFFSET để lấy ra giá trị ô ở vị trí cột 2 dòng 3 trong bảng sau.

Bước 1: Trong bảng dữ liệu tính Excel, thực hiện nhập hàm =OFFSET(B3,3,2) vào ô tham chiếu muốn hiển thị kết quả.

Nhập công thức như trên

Nhấn Enter để bảng tính hiển thị kết quả như hình dưới.

Kết quả hàm OFFSET trả về là 6000000

Ví dụ 2: Sử dụng hàm OFFSET và hàm SUM để tính tổng của cột D ở bảng bên dưới.

Bước 1: Trong bảng dữ liệu tính Excel, thực hiện nhập hàm =SUM(OFFSET(A3,1,3,5,1)) vào ô tham chiếu muốn hiển thị kết quả.

Sử dụng hàm OFFSET và hàm SUM để tính tổng

Trong đó:

SUM và OFFSET: Là lệnh hàm.

• A3: Là ô tham chiếu.
• 1: Là số hàng di chuyển xuống bắt đầu từ ô tham chiếu A3.
• 3: Là số cột được di chuyển sang phải bắt đầu từ ô tham chiếu A3.
• 5: Là tổng số hàng lấy dữ liệu tính kết quả.
• 1: Là tổng số cột lấy dữ liệu tính kết quả.

Nhấn Enter tổng sẽ hiển thị như hình dưới.

Kết quả là 24000000

Ví dụ 3: Hàm OFFSET kết hợp với hàm AVERAGE, MAX, MIN

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

Hàm OFFSET kết hợp với hàm AVERAGE

Nhấn Enter để hiển thị kết quả như hình dưới.

Với cách kết hợp này cũng áp dụng cho hàm MAX

Bước 2: Để tính số lớn nhất trong dải, bạn nhập hàm =MAX(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1)) như hình trên.

Nhấn Enter để hiển thị kết quả như hình 

Bước 3: Để tìm số nhỏ nhất trong dải, bạn nhập =MIN(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1)).

Hàm OFFSET kết hợp với hàm MIN

Nhấn Enter để hiển thị kết quả như hình dưới.

250000 là kết quả thấp nhất

Ví dụ 4: Hàm OFFSET kết hợp với hàm VLOOKUP

Trong những cách sử dụng hàm offfset chúng ta có thể kết hợp hàm VLOOKUP với hàm OFFSET.

Bước 1: Bạn nhập hàm =VLOOKUP(E2,A2:B8,2,FALSE) vào ô tham chiếu muốn hiển thị kết quả.

Hàm OFFSET kết hợp với hàm VLOOKUP

Nhấn Enter để hiển thị kết quả như hình

Ví dụ 5: Hàm OFFSET kết hợp với hàm MATCH 

Cho bảng dữ liệu gồm: MÃ SP, TÊN SP, HÃNG, GIÁ BÁN. Hãy 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)

Hàm OFFSET kết hợp với hàm MATCH

Trong đó:

+ $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 quả như hình trên

Ví dụ 6: 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 vô cùng hữu ích trong các trường hợp bạn muốn tạo danh sách tự động cập nhật. Việc bạn cần làm là đặt tên cho dải, sau đó tạo một danh sách theo mục để dễ dàng thao tác.

Đầu tiên, chúng ta cần xác định và đặt tên cho dải. Vào tab Formula > Defined Names > Define Name 

Thực hiện như hình trên

Sau đó 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)

Công thức hàm OFFSET kết hợp hàm COUNTA

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

• reference:được biểu diễn dưới dạng tên trang tính và theo sau là dấu chấm than kèm theo địa chỉ ô chứa mục đầu tiên có mặt trong trong dải. Bạn cần sử dụng các tham chiếu tuyệt đối ($) cho tên trang tính, ví dụ Sheet1!$A$1.
• Các tham số rowsvà cols đều là 0, vì không có cột hoặc hàng nào được thêm vào.
• height: Do chúng ta sử dụng hàm COUNTA để tính toán số ô không rỗng trong cột có chứa các mục của dải được đặt tên. Lưu ý điều này, bạn nên chỉ định tên bảng bên cạnh tên cột, ví dụ như COUNTA(Sheet_Name!$A:$A).
• width: là 1 cột

Sau khi đã đặt tên cho dải, bạn sử dụng Excel Data Validation để tạo danh sách theo mục, đảm bảo Excel luôn tự động cập nhật ngay khi bạn thêm hoặc xoá các mục từ Danh sách nguồn (bảng tính gốc). Bạn hãy vào tab Data > Data Tools > Data Validation hoặc nhấn mũi tên bên cạnh mục Data Validation > Data Validation.

Chọn Data Validation

Tại phần Allow kích chọn List và cố định vùng tại phần Source

Kết quả chúng ta sẽ có một dải động hiển thị dưới dạng danh sách theo mục như hình dưới.

IV. Tổng kết

Bài viết trên hướng dẫn cách sử dụng hàm OFFSET trong Excel để tham chiếu và tính toán dữ liệu. Nếu các bạn hiểu và thành thạo hàm này sẽ hỗ trợ trong công việc một cách nhanh chóng và tiện lợi hơn. Hy vọng bài viết sẽ hữu ích với bạn, chúc bạn thực hiện thành công!

Đừng quên truy cập website mega.com.vn để cập nhật các bài viết mới nhất liên quan đến thủ thuật excel cũng như tin học văn phòng bạn nhé!

Xem thêm >>>

Hướng dẫn cách dùng hàm DATEVALUE trong Excel dễ dàng, hiệu quả nhất

Cú pháp và cách sử dụng hàm UPPER trong Excel đơn giản

Hướng dẫn cách sử dụng hàm right trong excel cực chi tiết kèm ví dụ! 

copyright © mega.com.vn