Hướng dẫn cách dùng hàm OFFSET trong Excel cực dễ hiểu

Trong Excel, hàm OFFSET là một công cụ linh hoạt giúp bạn tạo ra vùng tham chiếu động dựa trên vị trí ban đầu của dữ liệu. Nhờ khả năng linh hoạt theo hàng và cột, hàm này hỗ trợ tốt cho việc truy xuất dữ liệu, xây dựng phạm vi tự động và kết hợp với các hàm khác để tính toán hiệu quả hơn. Bài viết sau sẽ hướng dẫn bạn sử dụng OFFSET chi tiết và dễ hiểu.

Cách sử dụng hàm OFFSET trong Excel đơn giản

Hàm OFFSET trong Excel là hàm giúp bạn tạo ra một vùng tham chiếu mới dựa trên một ô hoặc một dãy trong bảng tính đã được xác định trước đó.

Hàm OFFSET thường được sử dụng khi cần:

• Lấy dữ liệu của một ô hoặc một vùng nằm cách vùng gốc một khoảng nhất định.
• Tạo các vùng dữ liệu động để kết hợp với những hàm khác (như SUM, AVERAGE, COUNTA…), giúp việc tính toán linh hoạt và tự động hơn.

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

Giải thích:

• reference (Bắt buộc): Ô hoặc vùng dữ liệu làm điểm bắt đầu để tạo tham chiếu mới.
• rows (Bắt buộc): Số hàng cần dịch chuyển lên/xuống tính từ reference.
• cols (Bắt buộc): Số cột cần dịch chuyển sang trái/phải tính từ reference.
• height (Tùy chọn): Số hàng của vùng kết quả muốn trả về.
• width (Tùy chọn): Số cột của vùng kết quả muốn trả về.

Cú pháp hàm OFFSET trong Excel

Ví dụ: Hãy sử dụng hàm OFFSET để lấy ra giá trị ô ở vị trí cột 2 dòng 1 trong bảng dữ liệu.

Bước 1: Tại ô bạn muốn hiển thị kết quả, nhập công thức =OFFSET(B3,1,2) để tạo tham chiếu mới dựa trên ô B3.

Nhập hàm OFFSET vào ô tính

Bước 2: Nhấn Enter để hiển thị giá trị tại vị trí mới sau khi dịch chuyển.

Kết quả giá trị tìm được

Ví dụ: Tính tổng giá trị thưởng các tháng trong bảng dữ liệu

Bước 1: Tại ô cần tính tổng, nhập công thức =SUM(B2:OFFSET(B9,-1,0)) để tạo phạm vi cộng từ B2 đến ô nằm ngay phía trên B9.

Nhập hàm SUM với OFFSET

Bước 2: Nhấn Enter để Excel thực hiện tính toán và trả về kết quả tổng giá trị thưởng trong cột B.

Kết quả tổng thưởng được tính

Ví dụ: Tính giá trị trung bình tiền thưởng của các tháng và tìm xem số tiền thưởng lớn nhất và nhỏ nhất trong bảng dữ liệu

Bước 1: Để tính giá trị trung bình của dải dữ liệu, nhập công thức =AVERAGE(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1)) vào ô cần hiển thị kết quả.

Tính giá trị trung bình bằng OFFSET

Bước 2: Nhấn Enter để xem kết quả trung bình.

Bước 3: Để xác định giá trị lớn nhất và nhỏ nhất trong cùng dải dữ liệu, bạn có thể sử dụng cú pháp:

• Giá trị lớn nhất: =MAX(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1))
• Giá trị nhỏ nhất: =MIN(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1))

Công thức tìm giá trị lớn nhất dải dữ liệu

Sau đó nhấn Enter để xem giá trị tương ứng.

Ví dụ: Tra cứu tiền thưởng tháng 5 bằng VLOOKUP

Bước 1: Tại ô bạn muốn xem kết quả, nhập công thức =VLOOKUP(B1,A5:B11,2,FALSE) để tra cứu giá trị dựa trên dữ liệu trong bảng A5:B11.

Dùng VLOOKUP tra cứu thưởng

Bước 2: Nhấn Enter để Excel trả về kết quả tương ứng với nội dung cần tìm trong ô B1.

Kết quả thưởng tháng cần tìm

Dưới đây là những lưu ý giúp bạn sử dụng hàm OFFSET chính xác hơn và hạn chế các lỗi thường gặp trong quá trình làm việc với Excel:

• Lỗi #REF! xuất hiện khi vùng tham chiếu vượt phạm vi trang tính: Lỗi này xảy ra khi bạn nhập giá trị rows hoặc cols quá lớn (hoặc quá nhỏ theo hướng âm), khiến vùng OFFSET dịch chuyển ra ngoài phạm vi cho phép của Excel. Khi đó Excel không thể xác định ô tham chiếu hợp lệ nên báo lỗi.
• Khi bạn không khai báo height và width, Excel sẽ tự hiểu vùng trả về có kích thước giống với vùng tham chiếu ban đầu.
• Trường hợp height hoặc width lớn hơn 1, bạn cần dùng công thức mảng; nếu không Excel sẽ báo lỗi #VALUE!. Ngoài ra, cả hai đối số này đều phải là số dương để công thức hoạt động đúng.

Việc nắm rõ cách vận dụng OFFSET sẽ giúp bạn thao tác dữ liệu linh hoạt và chủ động hơn trong nhiều tình huống. Khi đã quen với cấu trúc và cách thiết lập phạm vi tham chiếu, bạn có thể dễ dàng áp dụng hàm này cho các bảng tính phức tạp hơn.

Nếu muốn cập nhật thêm các thủ thuật hữu ích cho công việc hằng ngày, hãy theo dõi chuyên mục Kinh nghiệm tại Siêu Thị Điện Máy - Nội Thất Chợ Lớn.

Nếu bạn cần một chiếc máy tính bảng hoặc điện thoại để thao tác Excel tiện lợi hơn, hãy đến ngay Siêu Thị Điện Máy - Nội Thất Chợ Lớn để chọn thiết bị phù hợp với nhu cầu làm việc và học tập của bạn.