Hàm Offset

Ngoài các hàm công thức thông thường như SUM, AVERAGE thì Excel còn có các hàm hữu dụng khác để hỗ trợ bạn trong việc định dạng, tìm kiếm, tách dữ liệu...Vậy bài viết dưới đây sẽ giới thiệu đến các bạn cách sử dụng của hàm offset một trong những hàm được sử dụng phổ biến trong Excel.

Hàm offset là gì?

Hàm offset là một hàm của Excel và có chức năng tính toán cũng như phân tích dữ liệu được lấy từ một vùng tham chiếu sẵn có trong bảng tính Excel. Bạn có thể dễ dàng sử dụng hàm offset để phân tích dữ liệu và trả về tham chiếu tới một ô hoặc phạm vi bao gồm số hàng và số cột bạn đã lựa chọn. Vậy để hiểu hơn về cách sử dụng của hàm offset trong Excel các bạn hãy tiếp tục theo dõi các phần tiếp theo của bài viết nhé.

Cấu trúc của hàm offset trong Excel

Hàm offset trong Excel có cấu trúc như sau: =OFFSET(reference, rows, cols, height, width)

Trong đó:

  • reference: đây là vùng tham chiếu làm cơ sở hay chính là giá trị xuất phát bạn lựa chọn để hàm offset có thể để tạo vùng tham chiếu mới và trả về kết quả phân tích dữ liệu.

  • rows: đây chính là số dòng bên trên hoặc bên dưới của reference bạn vừa chọn, được tính từ ô đầu tiên góc bên trái của bảng tính Excel.. Khi sử dụng hàm offset và bạn chọn rows là số dương thì các dòng trả về nằm bên dưới reference, khi rows là số âm thì các dòng trả về sẽ nằm bên trên reference. Ví dụ nếu bạn chọn  rows là 2, sẽ có 2 dòng được trả về và sẽ nằm bên dưới giá trị tham chiếu reference.

  • cols: đây là là số cột bên trái hoặc bên phải reference, cũng được tính từ ô đầu tiên ở góc bên trái. Trong hàm offset khi cols là số dương thì các cột trả về nằm bên phải reference, còn ngược lại khi cols là số âm thì các cột trả về sẽ nằm bên trái của giá trị tham chiếu reference.

  • height: đây là số dòng của vùng tham chiếu cần trả về, height luôn lớn hơn 0

  • width: đây là số cột của vùng tham chiếu cần trả về. width cũng sẽ luôn lớn hơn 0

Những lưu ý khi sử dụng hàm offset

  • Trong cấu trúc của hàm offset thì reference, rows, cols là 3 đối số bắt buộc phải có còn các đối số còn lại bao gồm height và width các bạn có thể tùy chọn tùy thuộc vào yêu cầu của công việc.

  • Hàm offset sẽ trả về kết quả lỗi #VALUE nếu đối số tham chiếu reference bạn chọn không bao gồm một ô hoặc dải ô liền kề.

  • Nếu bạn chọn rows và cols làm cho vùng tham chiếu vượt ra khỏi phạm vi của bảng tính thì hàm offset sẽ trả về kết quả lỗi #REF!.

  • Khi sử dụng hàm offset trong Excel nó sẽ không làm di chuyển bất kỳ ô hoặc dải nào, mà hàm offset chỉ thực hiện thuật toán để trả về một tham chiếu khác.

  • Nếu hàm offset trả về kết quả là một dải ô thì các đối số rows và cols sẽ  luôn luôn đề cập đến ô phía trên bên trái.

  • Hàm offset cũng có thể được sử dụng kết hợp với các hàm Excel khác với điều kiện hàm đó có chấp nhận một tham chiếu ô hoặc dải trong các đối số của nó.

Ví dụ về hàm offset trong Excel

Ví dụ về hàm offset 1

Các phần tử của hàm offset vừa trình bày bên trên tương đối phức tạp nên để có thể hiểu kỹ hơn về cách sử dụng của hàm offset trong Excel chúng ta hãy cùng đi xét 1 ví dụ đơn giản dưới đây.

Với ví dụ trên chúng ta có thể thấy với công thức =OFFSET(E1,4,1) chúng ta có thể dễ dàng tìm được mức thưởng của tuần thứ 4 và chuyển sang vùng tham chiếu mới ở ô J3. Trong công thức trên bao gồm:

  • E1 là reference hay chính là vùng tham chiếu làm cơ sở hay chính là giá trị xuất phát bạn lựa chọn để hàm offset có thể để tạo vùng tham chiếu mới và trả về kết quả phân tích dữ liệu.

  • 4 là rows, chính là số dòng bên trên hoặc bên dưới reference bạn vừa lựa chọn để thực hiện hàm offset, do đây là số dương nên kết quả trả về sẽ là hàng thứ 4 bên dưới của reference.

  • 1 là cols, chính là số cột bên phải hoặc bên trái của reference, vì đây là số dương nên hàm offset sẽ chọn cột bên phải.

Vậy nếu các bạn muốn lấy giá trị tham chiếu của số tiền thưởng của tuần thứ 2, chúng ta có thể sử dụng công thức khác là: =OFFSET(E1,4,1), tương tự các bạn cũng có thể thực hiện như vậy đối với số tiền thưởng của các tuần khác.

Ví dụ về hàm offset 2

Ngoài ra thì hàm offset còn có thể kết hợp hiệu quả với hàm tính tổng SUM để tìm ra tổng giá ở vùng tham chiếu mà bạn lựa chọn. Để hiểu rõ hơn về cách sử dụng này các bạn có thể theo dõi ví dụ dưới đây.

Như các bạn có thể thấy với công thức =SUM(OFFSET(E1,1,1,4)) các bạn có thể dễ dàng tính được tổng số tiền thưởng của 4 tuần từ tuần 1 tới tuần 4 và kết quả trả về ra vùng tham chiếu mới tại ô J2. Trong cấu trúc trên bao gồm:

  • E1 là vùng tham chiếu làm cơ sở hay chính là giá trị xuất phát cũng tương tự như ví dụ bên trên.

  • 1 là số dòng bên dưới vùng tham chiếu cơ sở bạn vừa chọn. Điều này đồng nghĩa với việc hàm SUM sẽ bắt đầu tính tổng từ hàng thứ 1 ngay sau giá trị tham chiếu cơ sở.

  • Số 1 tiếp theo là cột bên phải vùng tham chiếu bạn vừa chọn.

  • 4 là số dòng bạn cần tính tổng số tiền thưởng.

Với ví dụ đơn giản về sự kết hợp giữa hàm offset và hàm tính tổng SUM chắc hẳn các bạn cũng đã hiểu hơn nhiều về cách sử dụng của hàm offset trong Excel. Hy vọng những thông tin mà bài viết vừa cung cấp cấp bao gồm hướng dẫn cùng với những lưu ý khi sử dụng hàm offset sẽ giúp ích cho các bạn khi làm việc với Excel.

giỏ hàng dbk 0 (0) sản phẩm.
DBK VIỆT NAM, https://dbk.vn/ham-offset.html,
21/15 đường số 17
Thu Đuc, HCM, 700000
Việt Nam
+84919219111