Hàm Offset trong Excel – cú pháp và sử dụng cơ bản

CHÀO MỪNG BẠN ĐẾN VỚI THƯ VIỆN HỌC TẬP & ĐÀO TẠO CƠ BẢN

Hàm Offset trong Excel – cú pháp và sử dụng cơ bản

Excel

Bắt đầu sử dụng Excel

Công thức và hàm

Nhập và phân tích dữ liệu

Định dạng dữ liệu

Xử lý sự cố

Excel 2016

Công thức hàm Offset trong Excel sau trả về một tham chiếu ô dựa trên một điểm làm mốc, các hàng và cột mà bạn chỉ định.

1. Hàm Offset trong Excel 

Cú pháp của hàm Offset trong Excel như sau:

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

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

1.1. Những đối số bắt buộc của hàm Offset trong Excel

  • Reference– một ô hoặc một dải ô liền kề mà sau đó nó có thể bị thêm/bớt (Có thể nói đó là điểm khởi đầu/làm mốc)
  • Rows – Số hàng di chuyển từ điểm làm gốc, lên hoặc xuống. Nếu các hàng là một số dương, nghĩa là công thức di chuyển xuống phía dưới tham chiếu, trong trường hợp một số âm thì di chuyển lên phía trên tham chiếu.
  • Cols – Số cột bạn muốn công thức di chuyển, lấy gốc là từ điểm xuất phát. Cũng như rows, cols có thể là số dương (ở bên phải tham chiếu) hoặc số âm (ở bên trái tham chiếu).

1.2. Những đối số tùy chọn của hàm Offset trong Excel

  • height – chiều cao, tính bằng số hàng, mà bạn muốn tham chiếu trả về
  • Width – chiều rộng, tính bằng số cột, mà bạn muốn tham chiếu trả về.

Cả hai đối số height và width luôn phải là số dương. Nếu một trong hai bị bỏ qua, thì height hoặc width của tham chiếu gốc được sử dụng.

Và bây giờ, tôi có một ví dụ đơn giản về công thức OFFSET nhằm minh hoạ lý thuyết trên:

2. Công thức hàm Offset trong Excel

Công thức hàm Offset trong Excel sau trả về một tham chiếu ô dựa trên một điểm làm mốc, các hàng và cột mà bạn chỉ định:

= OFFSET (A1,3,1)

Công thức lấy ô A1 làm điểm làm mốc (tham chiếu), sau đó di chuyển 3 hàng xuống (đối số rows) và 1 cột bên trái (đối số cols). Kết quả là, công thức OFFSET này trả về giá trị trong ô B4.

Hình ảnh ở bên trái cho thấy cách thức hàm hoạt động và ở bên phải thể hiện cách bạn có thể sử dụng công thức hàm Offset trong Excel trên dữ liệu thực tế. Sự khác biệt duy nhất giữa hai công thức là công thức thứ hai (bên phải) bao gồm một tham chiếu ô (E1) trong đối số hàng (rows). Nhưng nếu ô E1 có chứa số 3 và chính xác cùng một số xuất hiện trong các đối số hàng của công thức đầu tiên, thì cả hai sẽ đều trả về cùng một kết quả – giá trị trong B4.

Công thức hàm Offset trong Excel

Công thức hàm Offset trong Excel

3. Hàm Offset trong Excel – những điều cần nhớ

  • Hàm Offset trong Excel không thực sự di chuyển bất kỳ ô hoặc dải nào, mà điều nó làm chỉ là trả về một tham chiếu.
  • Khi một công thức OFFSET trả về một dải ô, các đối số rows và cols luôn luôn đề cập đến ô phía trên bên trái.
  • Đối số tham chiếu phải bao gồm một ô hoặc một dải ô liền kề, nếu không công thức của bạn sẽ trả về #VALUE! lỗi.
  • Nếu các hàng (rows) được chỉ định và/hoặc cột (cols) di chuyển một tham chiếu đến rìa của bảng tính, thì công thức Excel OFFSET của bạn sẽ trả về #REF! lỗi.
  • Hàm OFFSET có thể được sử dụng trong bất kỳ hàm Excel nào khác 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ụ, nếu bạn cố gắng sử dụng công thức tương tự thế này: = OFFSET (A1,3,1,1,3), thì nó sẽ trả về # VALUE! Lỗi, vì một dải để chuyển thành (1 hàng, 3 cột) không phù hợp với việc đặt chỉ trong một ô duy nhất. Tuy nhiên, nếu bạn nhúng nó vào hàm SUM, như thế này:

= SUM (OFFSET (A1,3,1,1,3))

Công thức sẽ trả lại tổng các giá trị trong dải có 1 hàng bằng dải gồm 3 cột, bao gồm 3 hàng bên dưới và 1 cột bên phải ô A1, tức là tổng giá trị có trong các ô B4: D4.

4. Sử dụng hàm Offset trong Excel trong trường hợp nào

Bây giờ bạn đã biết hàm OFFSET làm được những gì, vậy nên bạn có thể tự hỏi mình “Tại sao lại phải dùng nó?” Tại sao không chỉ đơn giản là viết một tham chiếu trực tiếp như B4: D4?

Hàm Offset trong Excel rất hữu ích nếu bạn muốn:

Tạo các dải động: Các tham chiếu như B1: C4 là tĩnh, có nghĩa là chúng luôn tham chiếu đến một dải nhất định. Nhưng với một số công việc thì sẽ dễ thực hiện hơn khi làm với dải động. Đây là trường hợp đặc biệt khi bạn làm việc với việc thay đổi dữ liệu, ví dụ: Bạn có một bảng tính, và mỗi tuần sẽ có một hàng hoặc cột mới được thêm vào trong đó.

Lấy dải từ ô gốc: Đôi khi, bạn có thể không biết địa chỉ thực của cả dải, mặc dù bạn có biết rằng nó bắt đầu từ một số ô. Trong các tình huống như vậy, sử dụng OFFSET trong Excel là 1 cách làm đúng.

Bạn có thể thành thạo ngay kỹ năng tin học văn phòng từ giảng viên FPT- Arena chỉ với 280,000Đ