4 Cách dùng hàm OFFSET trong Excel chi tiết, dễ hiểu nhất

Hàm OFFSET trong Excel là một hàm dùng để tạo ra một tham chiếu động đến một ô hoặc phạm vi ô trong bảng tính, dựa trên một điểm xuất phát cố định. Bạn có thể di chuyển tham chiếu theo hàng và cột, cũng như xác định kích thước của phạm vi tham chiếu. Hàm này rất hữu ích khi bạn cần trích xuất dữ liệu từ các vị trí khác nhau mà không thay đổi công thức ban đầu.

>>>> XEM NGAY: Cách dùng hàm TEXT trong Excel chi tiết nhất 2024

Công thức: =OFFSET(reference, rows, cols, [height], [width])

Ý nghĩa các tham số:

• reference: Ô hoặc phạm vi ô làm điểm xuất phát.
• rows: Số hàng cần di chuyển từ ô tham chiếu ban đầu. Giá trị dương di chuyển xuống dưới, giá trị âm di chuyển lên trên.
• cols: Số cột cần di chuyển từ ô tham chiếu ban đầu. Giá trị dương di chuyển sang phải, giá trị âm di chuyển sang trái.
• height (tùy chọn): Chiều cao (số hàng) của phạm vi ô trả về.
• width (tùy chọn): Chiều rộng (số cột) của phạm vi ô trả về.

Hàm OFFSET trong Excel giúp bạn lấy dữ liệu từ các vị trí khác nhau dựa trên tham chiếu động.

Dưới đây là các ứng dụng hữu ích của hàm OFFSET trong Excel

• Tạo phạm vi động: Hàm OFFSET có thể được dùng để tạo các phạm vi dữ liệu thay đổi kích thước tự động, ví dụ như trong các báo cáo và biểu đồ.
• Xây dựng bảng tổng hợp: Sử dụng OFFSET để tạo bảng tổng hợp dữ liệu từ các phạm vi khác nhau mà không cần thay đổi công thức khi dữ liệu thay đổi.
• Tính toán linh hoạt: OFFSET giúp thực hiện các phép tính trên các phạm vi dữ liệu thay đổi, như tính tổng hoặc trung bình cho các tháng cụ thể trong một năm.
• Tạo biểu đồ động: Kết hợp OFFSET với các hàm khác để tạo biểu đồ tự động cập nhật khi dữ liệu thay đổi hoặc thêm mới.

Các bước sử dụng hàm OFFSET trong Excel theo trình tự bài bản như sau:

• Bước 1: Chọn ô hoặc phạm vi ô làm điểm xuất phát cho hàm OFFSET.
• Bước 2: Nhập công thức OFFSET vào ô cần hiển thị kết quả, ví dụ: =OFFSET(A1, 2, 3, 1, 2) để lấy phạm vi 1 hàng và 2 cột, bắt đầu từ ô cách 2 hàng và 3 cột từ ô A1.
• Bước 3: Nhấn Enter để áp dụng công thức. Excel sẽ trả về giá trị hoặc phạm vi dữ liệu dựa trên các tham số đã nhập.
• Bước 4: Bạn có thể thay đổi các tham số trong công thức OFFSET để điều chỉnh phạm vi hoặc vị trí dữ liệu theo yêu cầu.

Ngoài ra, hàm OFFSET trong Excel có thể kết hợp đa dạng với các hàm khác như:

• Hàm SUM: Kết hợp OFFSET và SUM để tính tổng các giá trị trong một phạm vi động. Ví dụ: =SUM(OFFSET(A1, 1, 0, 5, 1)) tính tổng các giá trị trong phạm vi 5 hàng, 1 cột bắt đầu từ ô A2.
• Hàm AVERAGE: Sử dụng OFFSET với AVERAGE để tính giá trị trung bình của một phạm vi động. Ví dụ: =AVERAGE(OFFSET(B1, 0, 1, 3, 1)) tính giá trị trung bình của phạm vi 3 hàng, 1 cột bắt đầu từ ô C1.
• Hàm COUNT: Kết hợp OFFSET với COUNT để đếm số ô chứa số trong một phạm vi động. Ví dụ: =COUNT(OFFSET(C1, 0, 0, 10, 1)) đếm số ô chứa số trong phạm vi 10 hàng, 1 cột bắt đầu từ ô C1.
• Hàm MAX: Kết hợp OFFSET với MAX để tìm giá trị lớn nhất trong một phạm vi động. Ví dụ: =MAX(OFFSET(D1, 2, 0, 4, 1)) tìm giá trị lớn nhất trong phạm vi 4 hàng, 1 cột bắt đầu từ ô D3.
• Hàm MIN: Sử dụng OFFSET với MIN để tìm giá trị nhỏ nhất trong một phạm vi động. Ví dụ: =MIN(OFFSET(E1, 1, 1, 5, 2)) tìm giá trị nhỏ nhất trong phạm vi 5 hàng, 2 cột bắt đầu từ ô F2.

  

Dưới đây là một số lưu ý quan trọng trong quá trình sử dụng hàm OFFSET trong Excel

• Điểm xuất phát hợp lệ: Đảm bảo ô hoặc phạm vi ô bạn chọn làm điểm xuất phát (reference) là hợp lệ và tồn tại trong bảng tính.
• Kích thước phạm vi chính xác: Xác định chính xác số hàng và cột di chuyển để tránh lỗi tham chiếu hoặc kết quả không mong muốn.
• Kích thước phạm vi không âm: Đảm bảo các tham số chiều cao (height) và chiều rộng (width) của phạm vi không âm để tránh lỗi.
• Cập nhật dữ liệu: Khi dữ liệu thay đổi, hàm OFFSET sẽ tự động cập nhật. Đảm bảo công thức được thiết lập đúng để phản ánh thay đổi dữ liệu.
• Hiệu suất và độ phức tạp: Tránh lạm dụng hàm OFFSET trong các bảng tính lớn hoặc phức tạp vì nó có thể làm giảm hiệu suất của Excel.
• Kiểm tra lỗi: Luôn kiểm tra lỗi trong công thức OFFSET để đảm bảo tham chiếu không bị lỗi do các tham số không hợp lệ.