Hàm Offset và công dụng của hàm Offset trong Excel

Bạn đã nghe qua hàm Offset trong Excel? Vậy, hàm Offset trong Excel là gì? Hàm này có công thức ra sao? Hàm Offset xử lý dữ liệu như thế nào? Cách sử dụng hàm Offset chuẩn xác nhất có thật sự khó? Tất tần tật về hàm Offset sẽ được bài viết thiết lộ với bạn ngay sau đây.

Hàm Offset và công dụng của hàm Offset trong Excel

Chắc hẳn một trong số các bạn đều đã được nghe qua hàm Offset. Vậy, hàm Offset trong Excel là hàm gì? Tại sao lại trở nên nóng sốt trong thời gian gần đây. 

Hàm Offset là gì?

Offset là hàm được tạo ra nhằm mục đích trả về giá trị tham chiếu của phép tính. Hay nói cách khác là trả về kết quả tính toán giá trị của của một ô hoặc một vùng nhiều ô dữ liệu được cài đặt sẵn. 

Hàm Offset được dùng trong các công việc với lượng số liệu đơn giản đến phức tạp. Hàm Offset có thể tính toán và trả về kết quả chính xác chỉ trong khoản chưa đến 1 giây. 

Công dụng của hàm trong Excel

Ngoài sự tiện lợi, nhanh chóng và chính xác như những hàm Excel khác. Hàm Offset có thêm cho mình 2 ưu điểm nổi bật riêng như sau:

• Xử lý và tính toán kết hợp với nhiều hàm khác hỗ trợ hoàn thành công việc nhanh hơn. Với hàm Offset, bạn có thể kết hợp nó với các hàm khác như: Sum, Average, Count,...

Với ưu điểm này, người dùng có thể sử dụng hàm Offset để tính toán các vùng dữ liệu lớn hơn. Mà những dữ liệu này nằm liền kề nhau trong bảng tính.

Tin tức về excel được nhiều người tìm nhất: tách chữ trong excel

• Hàm Offset xử lý dữ liệu linh động hơn

Nếu các hàm bình thường chỉ tính được trong phạm vi dữ liệu cho phép. Thì hàm Offset cho phép người dùng thiết lập linh động dữ liệu ngay cả khi số lượng dữ liệu lớn.

Với 2 ưu điểm này, hàm Offset được ứng dụng rất nhiều trong việc phân tích và xử lý dữ liệu thô. Nghĩa là người dùng sẽ cài đặt để tính tổng, trung bình hay đếm,... bất kì cụm ô nào mà mình muốn. 

Kể cả khi thay đổi giá trị và vị trí của ô cần tính, phép tính vẫn cho ra kết quả chính xác. Tất nhiên, hàm này sẽ buộc người dùng phải áp dụng một công thức đúng. Vậy, công thức của hàm Offset như thế nào?

Công thức của hàm Offset Excel là gì

 Công thức hàm Offset: OFFSET(reference, rows, cols,[height],[width])

Trong đó:

• Reference: vùng tham chiếu bắt buộc phải có. Thường là ô bắt đầu trong vùng số liệu cần tính. Vùng này phải bao gồm ít nhất 1 ô hoặc nhiều ô phải liền kề nhau. Rows: bạn muốn tính lên hoặc xuống bao nhiêu hàng kể từ ô tham chiếu đầu tiên (ô Reference). 
• Cols: bạn muốn tính lên hoặc xuống bao nhiêu cột kể từ ô tham chiếu đầu tiên (ô Reference). 
• Height: Chiều cao vùng dữ liệu cần tính, Height tính theo đơn vị hàng. 
• Width: Chiều rộng vùng dữ liệu cần tính, Width tính theo đơn vị cột. 

Một số lưu ý khi sử dụng Offset Excel là gì?

• Lỗi #REF!: Đây là lỗi khi vùng tham chiếu cần tính không nằm trong đường biên của bảng dữ liệu. Hàm sẽ trả về #REF!
• Lỗi #VALUE!: Là khi bạn đã nhập sai 1 trong 4 thông số. Hoặc Height và Width của bạn đã là số âm. 

Nếu bạn không nhập cả Height và Width thì hàm Offset sẽ tự trả về kết quả với Height và Width ngầm hiểu là 1. Giống như ô tham chiếu ban đầu. Kết quả sẽ cho ra với vị trí ô tham chiếu không dịch chuyển. 

Cách sử dụng hàm Offset trong Excel cực kì đơn giản 

Để đơn giản nhất về cách sử dụng hàm Offset trong Excel, bạn hãy theo dõi ví dụ sau: 

Cho bảng dữ liệu theo dõi doanh số bán hàng tại Điện Thoại Vui ngày 1/3/2023. Yêu cầu trả về doanh số của Nhân viên Lê Thị Ngọc Thảo vào ô D10. 

Xem ngay bài viết về cách tính tổng trong excel

Bước 1: Tại ô D10. Bạn nhập theo công thức:

=OFFSET(B3,3,2)

Trong đó: 

• Reference = B3: Cột tham chiếu để tìm Tên nhân viên. Đây cũng chính là cột mốc làm thước đo cho các chỉ số sau.
• Rows = 3: Vị trí hàng có tên Ngọc Thảo cách ô B3 3 dòng trở xuống dưới. 
•  Cols = 2; Doanh số của Ngọc thảo cách ô B3 2 cột về phía bên phải.
• Bước 2: Sau khi nhập xong bạn nhấn Enter.

Kết quả trả về sẽ là doanh số bán được củ Nhân viên tên Ngọc Thảo trong ngày 1/3/2023. 

Với ví dụ này, cách sử dụng Offset đã được đơn giản hóa nhất có thể. 

Tuy nhiên trên thực tế rất ít khi hàm này được sử dụng một mình. Khi công việc đòi hỏi phải xử lý lượng sự liệu khổng lồ với hàng triệu con số. Những thao tác đơn giản này có thể không đáp ứng yêu cầu tính toán.  Khi đó, người dùng có thể sử dụng hàm Offset này kết hợp với những hàm khác trong Excel.

Ngoài hàm Offset thì hàm RANK trong Excel cũng khá phổ biến. Click xem ngay cách dùng hàm countif hiệu quả.

Một số tính năng kết hợp của hàm Offset trong Excel

Với lượng dữ liệu lớn và yêu cầu tính toán một cách linh động với nhiều ô tính. Hàm này bắt buộc phải kết hợp với một số hàm khác. Bài viết này sẽ giới thiệu đến bạn việc sử dụng kết hợp hàm Offset và hai hàm phổ biến nhất chính là tính tổng (Sum) và trung bình (Average)

Kết hợp với hàm Sum 

Ví dụ, cho một bảng tính và có công thức tại ô G2 như sau:

G2 =Sum(OFFSET(C2,1,2,3,1))

Đối chiếu với Công thức OFFSET(reference, rows, cols,[height],[width])

Ta có:

Reference = C2; Rows = 1; Cols = 2; Height = 3 và Width = 1 

Dựa vào công thức trên có thể hiểu là: Tính toán Tổng (Sum của Vùng dữ liệu gồm 3 hàng(Height) và 1 cột(Width), vùng đó cách ô C2 1 hàng về phía dưới(Rows) và 2 cột về phía bên phải(Cols).

Khi đó, nội dung mà ô G2 hiển thị chính là: Tổng 3 mặt hàng A1, A2, A3 của ngày 5/1. 

Kết quả trả về sẽ được 17

Bên cạnh đó, cách để bạn kiểm tra xem mình đã nhập đúng các chỉ số hay chưa. Bạn chỉ cần dùng chuột kéo các ô bạn muốn tính tổng hoặc các ô mà mà đề bài yêu cầu. Ở ví dụ này là dãy ô từ E3 đến E5. Sau đó quan sát dưới thanh công cụ của Excel.

Nếu mục Sum hiển thị đúng bằng 17 và bằng với ô G2. Nghĩa là bạn đã sử dụng được hàm Offset kết hợp hàm Sum thành công. 

Kết hợp với hàm Average 

Tương tự như ví dụ hàm Sum. Sau đây là đề bài và các thao tác thực hiện vận dụng kết hợp hàm Offset và hàm Average nhé!

Đề bài: Sử dụng bảng dữ liệu Doanh số từ ngày 2/1 đến ngày 6/1 của 9 loại mặt hàng trên. Hãy tính Trung bình cộng của nhóm từ mặt hàng A3 đến mặt hàng A6 từ ngày 4/1 đến ngày 6/1. Kết quả trả về ô G2 như hình.

Với yêu cầu này các bạn thực hiện theo các bước:

Bước 1: Tại ô G2 bạn nhập công thức như sau: 

 G2=Average(Offset(B2,2,2,4,3))

Trong đó: 

• Reference = B2 chính là ô tham chiếu ban đầu, ô này thường không cố định mà tùy thuộc vào cách chọn của mỗi người.
• Rows = 2: mặt hàng A3 cách B2 một khoảng là 2 hàng
• Cols = 2: ngày 4/1 cách B2 một khoảng là 2 cột 
• Height = 4: chiều cao vùng tính trung bình chính bằng tổng số hàng. Theo đề bài ta có từ hàng A3 đến hàng A6 nghĩa là 4 dòng. 
• Width = 3: chiều rộng vùng tính trung bình là số cột thuộc vùng tính. Theo đề ta có từ ngày 4/1 đến ngày 6/1 nghĩa là 3 ngày. 

 Bước 2: Nhấn Enter và kiểm tra kết quả.

Tại đây, ô G2 sẽ trả kết quả bằng 5.58. Để kiểm tra, bạn hãy xem nếu kết quả này trùng với mục Average trên thanh công cụ. Khi bạn bôi vùng dữ liệu từ D4 đến F7 trong bảng tính. 

Mục Average cũng bằng 5.58 nghĩa là bạn đã thành công trong việc sử dụng kết hợp hàm Offset với hàm Average. 

Xem ngay bài viết về hàm rank trong excel. Đừng bỏ lỡ!

Ngoài hàm Sum và hàm Average, hàm này còn có thể kết hợp được cùng với rất nhiều hàm tính khác. Tùy vào từng yêu cầu và đặc tính riêng của dữ liệu. Hàm Offset sẽ được vận dụng linh hoạt theo những cách khác nhau. 

Với tính tiện lợi và chuẩn xác cao gần như tuyệt đối trong việc xử lý dữ liệu số. Hàm Offset đang dần được sử dụng rộng rãi hơn. Hy vọng rằng sau bài viết này, bạn đã có thể vận dụng cách sử dụng hàm Offset tính vào công việc và học tập. Đừng quên nhấn Share nếu thấy bài viết này hữu ích với bạn.