Hàm OFFSET trong Excel là một công cụ mạnh mẽ giúp bạn thao tác và truy xuất dữ liệu một cách linh hoạt. Bằng cách chỉ định khoảng cách và vị trí ban đầu, bạn có thể dễ dàng di chuyển và lấy dữ liệu từ ô hoặc dải ô khác. Hàm này thực sự hữu ích khi bạn muốn thay đổi vùng dữ liệu hiển thị trên bảng tính mà không cần thay đổi công thức. Hãy cùng aptech tìm hiểu chi tiết về hàm OFFSET để áp dụng vào công việc của bạn!
Mô tả
Hàm Offset trong Excel mang đến cho bạn khả năng linh hoạt trong việc tham chiếu và trích xuất dữ liệu. Bằng cách chỉ định một ô hoặc một phạm vi ô nhất định, hàm Offset cho phép bạn truy cập đến ô hoặc phạm vi ô đó, dựa trên số hàng và số cột được xác định trước.
Kết quả trả về của hàm Offset có thể là một ô đơn hoặc một phạm vi ô, dựa trên số hàng và số cột bạn đã chỉ định.
Cú pháp
=OFFSET(reference; rows; cols; [height]; [width])
Trong đó:
- reference: Là vùng dữ liệu mà bạn muốn sử dụng như một điểm khởi đầu (mốc) để di chuyển và truy xuất dữ liệu tới vùng tham chiếu trả về.
- rows: Là số hàng mà bạn muốn di chuyển lên trên hoặc xuống dưới vùng tham chiếu. Nếu giá trị của rows là dương, hàm sẽ di chuyển xuống phía dưới reference và nếu giá trị của rows là âm, hàm sẽ di chuyển lên trên reference.
- cols: Là số cột mà bạn muốn di chuyển sang trái hoặc sang phải vùng tham chiếu sau khi đã di chuyển theo rows. Nếu giá trị của cols là dương, hàm sẽ di chuyển sang phải số ô tương ứng với số cols và nếu giá trị của cols là âm, hàm sẽ di chuyển sang trái số ô tương ứng với số cols.
- height (tùy chọn): Là chiều cao, được tính bằng số hàng mà bạn muốn cho vùng tham chiếu trả về.
- width (tùy chọn): Là chiều rộng, được tính bằng số cột mà bạn muốn cho vùng tham chiếu trả về.
Lưu ý
- Khi sử dụng đối số rows và cols trong hàm OFFSET, nếu vùng tham chiếu trả về vượt quá biên giới của trang tính, hàm sẽ hiển thị giá trị lỗi #REF.
- Nếu bỏ qua đối số height và width, tham chiếu trả về sẽ có cùng chiều cao và chiều rộng với vùng tham chiếu mặc định.
- Nếu height và width lớn hơn 1, tức là hàm cần trả về nhiều hơn một ô, chúng ta phải sử dụng công thức mảng với hàm OFFSET, nếu không sẽ gây ra lỗi #VALUE!
- Lưu ý rằng đối số height và width phải là số dương.
- Hàm OFFSET thực tế không làm thay đổi hoặc di chuyển bất kỳ ô nào, nó chỉ đơn giản trả về một tham chiếu.
Ví dụ
- Hàm OFFSET trong Excel sẽ trả về tham chiếu của ô bắt đầu từ ô D8, khi mà chúng ta di chuyển xuống 3 hàng và sang phải 2 cột.
Dùng công thức hàm: =OFFSET(D8;3;2)
- Truy xuất tham chiếu của hai ô C10:C11, khi bắt đầu từ ô D8, di chuyển xuống 2 hàng và sang trái 1 cột.
Nếu chúng ta sử dụng công thức hàm OFFSET như sau: =OFFSET(D8;2;-1;2;1) thì hàm sẽ trả về lỗi #VALUE! do ví dụ này trả về tham chiếu của 2 ô nên các bạn cần áp dụng công thức mảng.
Để truy xuất tham chiếu của hai ô C10:C11, chúng ta có thể chọn bất kỳ hai ô liền kề nào trong cột và áp dụng công thức =OFFSET(D8;2;-1;2;1). Tuy nhiên, để đảm bảo kết quả chính xác, chúng ta cần nhấn tổ hợp phím Ctrl + Shift + Enter để chuyển công thức thành công thức mảng. Sau đó, chúng ta sẽ nhận được kết quả như mong muốn:
- Để tính tổng của phạm vi trả về bằng cách sử dụng hàm OFFSET kết hợp với hàm SUM.
Nếu phạm vi trả về là nhiều ô, ta cần sử dụng công thức mảng như ví dụ trước. Tuy nhiên, khi kết hợp với hàm SUM, ta không cần phải sử dụng công thức mảng nữa. Đơn giản chỉ cần nhập công thức =SUM(OFFSET(D8;2;-1;2;1))
- Để minh họa cách sử dụng hàm SUM kết hợp với OFFSET, ta có thể áp dụng một ví dụ cụ thể:
Khi các bộ dữ liệu thường xuyên thay đổi, việc sử dụng hàm OFFSET sẽ rất hữu ích, giúp tiết kiệm thời gian và công sức so với việc phải điều chỉnh lại công thức SUM mỗi khi dữ liệu thay đổi.
Giả sử chúng ta có một tập dữ liệu như ví dụ dưới đây.
Với mỗi ngày, chúng ta luôn có sự cập nhật dữ liệu mới, do đó chúng ta cần thêm các hàng mới vào bảng dữ liệu.
Để tránh việc phải điều chỉnh công thức hàm SUM mỗi khi thêm dữ liệu mới, hãy sử dụng hàm OFFSET để tự động thay đổi. Trong ô tính tổng, bạn có thể sử dụng công thức sau:
=SUM(D7:OFFSET(D14;-1;0))
Để tính tổng từ ô đầu tiên trong cột “Doanh thu” đến tham chiếu trả về của hàm OFFSET, chúng ta sử dụng hàm SUM trong Excel. Hàm SUM sẽ tính tổng các ô từ ô D7 đến tham chiếu trả về của hàm OFFSET. Sau đó, hàm OFFSET trả về vị trí ô “Tổng doanh thu” một hàng phía dưới, chính xác là ô D13 như hình minh họa.
Điều đó có nghĩa là khi bạn thêm hoặc xóa hàng trong bảng dữ liệu, công thức hàm OFFSET sẽ tự động cập nhật vị trí tương ứng ở phía trên của ô “Tổng doanh thu”. Kết quả là, hàm SUM sẽ tính tổng một cách linh hoạt hơn, mà không cần phải điều chỉnh công thức thủ công.
FAQ: Những câu hỏi thường gặp về Hàm Offset trong Excel
1. Hàm OFFSET có hỗ trợ việc truy xuất đến các ô ở nhiều trang tính khác nhau không?
Không, hàm OFFSET chỉ hoạt động trên cùng một trang tính. Nếu bạn muốn truy xuất đến các ô ở trang tính khác, bạn cần sử dụng công thức khác như INDIRECT.
2. Có bao nhiêu đối số tối đa trong hàm OFFSET?
Hàm OFFSET có tối đa 5 đối số. Đối số thứ 3, thứ 4 và thứ 5 là tùy chọn.
3. Tôi có thể sử dụng hàm OFFSET trong bảng dữ liệu có dạng bảng không?
Có, hàm OFFSET hoạt động tốt trong bảng dữ liệu có dạng bảng. Bạn chỉ cần thay đổi cú pháp tham chiếu tương ứng với cách sử dụng bảng.
4. Hàm OFFSET có thể trả về kết quả là một dải ô không liên tục không?
Không, hàm OFFSET chỉ trả về kết quả là một dải ô liên tục. Nếu bạn muốn truy xuất đến dải ô không liên tục, bạn cần sử dụng hàm khác như UNION.
Kết luận
Chúng ta đã tìm hiểu về chi tiết về cách dùng và ví dụ hàm Offset trong Excel và cách sử dụng nó để thao tác và truy xuất dữ liệu một cách linh hoạt. Hàm này mang lại sự tiện lợi và linh hoạt cho công việc của chúng ta, giúp tiết kiệm thời gian và công sức. Hãy thử áp dụng hàm OFFSET vào công việc của bạn, và xin vui lòng chia sẻ bài viết này để lan tỏa kiến thức về hàm OFFSET đến mọi người.
