Thủ thuật

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 trong excel là gì

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. 

  • 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.

ứng dụng của hàm offset trong excel là gì

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

cách sử dụng hàm offset trong excel là gì

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. 

công thức sử dụng hàm offset là gì

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.  

ví dụ minh họa để tính toán

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

=OFFSET(B3,3,2)

cách sử dụng hàm

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. 

cách sử dụng hàm để tính toán

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

kết hợp với hàm sum

Đố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

kết hợp cùng hàm sum

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.

sử dụng hàm trong 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.

kết hợp với hàm average

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

kết hợp giữa average để tính toán

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. 

sử dụng average để tính toán kết hợp

 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. 

cách kiểm tra kết quả

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. 

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 thủ thuật hay này hữu ích với bạn.

Bạn đang đọc bài viết Hàm Offset và công dụng của hàm Offset trong Excel tại chuyên mục Thủ thuật, trên website Điện Thoại Vui.

Hỏi và đáp