Sử dụng hàm offset trong excel để tham chiếu dữ liệu [bạn đã biết chưa]

Hàm Offset là một trong những hàm được sử dụng để tính toán hoặc tham chiếu dữ liệu của một dãy hay một ô trong bảng tính dựa vào một vùng tham chiếu đã có sẵn. Trong bài viết này, hãy cùng teky.edu.vn tìm hiểu hàm offset trong excel và những ứng dụng của hàm offset. Mời các bạn tham khảo nội dung bài viết dưới đây nhé.

Hàm OFFSET dùng để làm gì? 

Định nghĩa hàm offset là gì?

Hàm offset trong excel là hàm cho phép trả về phạm vi ô hoặc một ô là số lượng cột và số lượng hàng được chỉ định từ một phạm vi ô hoặc một ô.

Mục đích

Hàm offset excel được sử dụng với mục đích.

• Tiết kiệm thời gian cho người dùng trong việc tính toán bảng dữ liệu của một ô.
• Nâng cao hiệu suất làm việc khi kết hợp hàm offset với các hàm khác.
• Giúp bạn trở nên chuyên nghiệp hơn, biết sử dụng hàm offset nói riêng và các hàm excel khác nói chung, giúp bạn có cơ hội thăng tiến trong công việc.

>>> Xem thêm : Tìm hiểu về hàm sumifs trong excel? Cách dùng, những điều cần chú ý?

Cú pháp và một số lưu ý của hàm OFFSET trong excel?

Cú pháp hàm OFFSET trong excel?

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

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

Trong đó: 

• Reference: Được xem là vùng cơ sở tham chiếu để tạo ra vùng tham chiếu mới.
• Rows: Số dòng bên dưới bên trên hoặc reference, tính từ ô đầu tiên của reference ở góc trên bên trái.
• Cols: Là số cột bên phải hoặc bên trái của reference, tính từ ô đầu tiên của reference ở góc trên bên trái.
• Height: Số lượng dòng của vùng tham chiếu cần được trả về.
• Width: Số cột của vùng tham chiếu cần được  trả về (Width phải luôn là số dương).

Lưu ý: 3 đối số đầu tiên là bắt buộc phải có, còn 2 cái sau thì không bắt buộc. Tất cả các đối số có thể là các kết quả được trả về bởi các công thức Excel khác hoặc là các tham chiếu đến các ô khác.

Một số lưu ý khi dùng hàm Offset trong excel

• Nếu đối số cột và số hàng làm cho vùng tham chiếu trả về, vượt quá ngoài đường biên của trang tính Lúc này, hàm OFFSET sẽ trả về giá trị lỗi #REF! .
• Nếu đối số độ rộng và chiều cao được bỏ qua, thì nó được giả định có cùng độ rộng  và chiều cao với vùng tham chiếu.
• Nếu các đối số  chiều cao và độ rộng >1 nghĩa là hàm phải trả về nhiều hơn 1 ô thì lúc này, bạn phải sử dụng công thức mảng hàm OFFSET nếu không sẽ trả về giá trị lỗi #VALUE!.
• Hàm OFFSET không làm thay đổi hay di chuyển hay bất kỳ phần nào được chọn bởi vì hàm  OFFSET 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 một số hàm khác cần đến một đối số tham chiếu.

>>> Xem thêm : Hướng dẫn tạo hình nền Powerpoint đẹp cho bài thuyết trình

Ví dụ về cách dùng hàm offset trong excel

Hướng dẫn được thực hiện trên hệ điều hành Windows với phiên bản Excel 2007, 2010, 2013, 2016,2019 Bạn cũng có thể thực hiện tương tự trên hệ điều hành MacOS. 

Ví dụ 

Tính doanh thu bán được của nhân viên Lê Thị Ngọc Thảo, dựa vào bảng dữ liệu đã cho.

Bước 1: Dựa vào bảng Excel, chúng ta có công thức như sau =OFFSET(B3,3,2) vào 1 ô tham chiếu mà bạn muốn hiển thị kết quả.

Bước 2: Sau khi nhập công thức, các bạn nhấn phím Enter để hiển thị kết quả.

Hàm OFFSET thường được kết hợp với hàm nào?

Hàm offset còn có thể kết hợp với nhiều hàm khác. Chẳng hạn như hàm Sum, Average, Min, Max,  … mục đích để lấy dữ liệu mà bạn mong muốn.

>>> Xem thêm : Các trang web tải Powerpoint miễn phí – tiết kiệm thời gian hiệu quả

Hàm offset kết hợp với hàm Sum

Ví dụ: Tính tổng doanh thu bán được của nhân viên Lê Cao bằng của cột D theo bảng dưới đây.

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

Giải thích:

• SUM và OFFSET: Là các lệnh hàm.
• A3: Ô tham chiếu.
• 1: Số hàng di chuyển xuống, tính bắt đầu từ ô tham chiếu A3.
• 3: Là số cột được di chuyển sang phải, tính 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ả.

Bước 2: Sau khi nhập công thức, bạn nhấn phím Enter để hiển thị kết quả.

Hàm offset kết hợp với hàm Average, Min, Max 

Ví dụ: Kết hợp hàm Average với offset để tính giá trị trung bình với dữ liệu trong bảng.

Bước 1: Tại ô E5, bạn nhập hàm =AVERAGE(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1)).

Bước 2: Sau khi nhập công thức, các bạn nhấn phím Enter để hiển thị kết quả.

Tương tự với cách tính tiền thưởng cho N tháng trong bảng tính trên, chúng ta có thể tính giá trị trung bình cho N tuần, N ngày hoặc N năm cuối cùng. Bên cạnh đó, các bạn cũng có thể tìm các nhỏ nhất hoặc giá trị lớn nhất lồng ghép hàm offset trong bảng.

Để tìm giá trị lớn nhất trong bảng: =MAX(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1)).

Để tìm giá trị nhỏ nhất trong bảng: =MIN(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1))

Sử dụng hàm Offset thay thế các hàm Vlookup trong excel

Ví dụ: Kết hợp hàm Vlookup với offset để tính giá trị lớn nhất với dữ liệu trong bảng.

Bên cạnh đó, chúng ta có thể kết hợp hàm offset với hàm Vlookup.

Bước 1: Tại ô B2, các 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ả.

Sử dụng hàm offset thay hàm Vlookup để tra cứu dữ liệu bên trái trong file Excel. Như các bạn đã biết, hàm Vlookup chỉ có khả năng trả lại một giá trị nhất định ở bên phải cột tra cứu, đây là một trong những hạn chế lớn nhất của hàm Vlookup, nó không có khả năng dò được giá trị bên trái của nó.

Chúng ta tiếp tục áp dụng ví dụ trên cho bảng tiền thưởng theo tháng. Nếu bạn chỉ cần tìm kiếm số tiền thưởng của một tháng nào đó, áp dụng công thức hàm Vlookup cơ bản rất đơn giản.

=VLOOKUP(B10,A5:B11,2,FALSE)

Bước 2: Sau khi nhập công thức, các bạn nhấn phím Enter để hiển thị kết quả.

Hy vọng với nội mà teky.edu.vn đã chia sẻ và hướng dẫn về hàm offset trong excel. Giúp các bạn hiểu rõ hơn về hàm này, đồng thời áp dụng trong công việc cũng như trong học tập một cách. Chúc các bạn ngày càng thành thạo excel hơn, để phục vụ cho công việc tốt hơn nhé.