Hàm Offset Trong Vba | Cùng Chuyên Mục:

Hàm OFFSET là gì? Hàm OFFSET để làm gì?

Hàm OFFSET là hàm thường được sử dụng để tham chiếu hoặc tính toán dữ liệu của một ô hay một dãy trong bảng tính dựa vào một vùng tham chiếu đã có sẵn trước đó.

Những ứng dụng của hàm OFFSET trong Excel:

Thường được sử dụng để tính toán dữ liệu của một ô hay 1 dãy trong bảng tính.
Có thể kết hợp được nhiều hàm thông dụng khác để hỗ trợ cho công việc.

Phân trang bảng tính

Hàm OFFSET cũng có thể phân trang bảng tính đơn giản, một nhiệm vụ thường được kết hợp với việc dùng tab Excel Developer. Điều này có thể khiến công việc xử lý nhóm dữ liệu lớn đơn giản, hiệu quả hơn.

Khi làm việc với bảng tính chứa tới 30.000 hàng dữ liệu, thật khó xem qua toàn bộ dữ liệu cùng lúc. Tạo menu thả xuống chấp nhận một phạm vi số từ 1 tới 300, cho phép bạn nhanh chóng tách dữ liệu theo trang với 100 kết quả mỗi trang.

Hàm OFFSET luôn có điểm neo được đặt ở ô dữ liệu trên cùng. Tuy nhiên, đối số thứ hai là offset theo chiều dọc. Giá trị này bằng số trang đã chọn trừ đi 100. Offset theo chiều ngang, đối số thứ ba có thể đặt là 0.

Đối số thứ 4 được đặt ở 100, đối số thứ 5 là số cột trong dataset. Phương pháp này khiến việc chia tách & quản lý bảng dữ liệu cực kỳ dễ dàng.

Dưới đây là một ví dụ về dữ liệu được phân trang. Nó bao gồm 500 mẫu thông tin được ghi lại từ một bộ cảm biến trong một trang tính và một báo cáo được đánh số trang trên trang tính kia.

Báo cáo dùng xác thực dữ liệu trong ô B1 để tạo menu thả xuống với các trang bên trong. Biểu thức sau ở ô A3 tạo phần nội dung của báo cáo:

=OFFSET(Sheet1!A2, (B1 - 1) * 100, 0, 100, 21)

Trên đây là một số cách dùng hàm offset trong Excel ít người biết nhưng chúng thật sự hữu ích, nhất là khi bạn cần xử lý số lượng lớn dữ liệu.

Hi vọng bài viết hữu ích với các bạn.

Bài 28 - Hàm Offset trong VBA, lấy địa chỉ của Cell, lấy trộm công thức của 1 Cell rất hay — Bài 28 – Hàm Offset trong VBA, lấy địa chỉ của Cell, lấy trộm công thức của 1 Cell rất hay

Offset là một hàm thú vị trong Excel. Và trong VBA chúng ta cũng có Offset dưới dạng 1 thuộc tính của đối tượng Cells, Range. Trong bài viết này chúng ta cùng tìm hiểu về thuộc tính OFFSET trong VBA Excel nhé.

Xem nhanh

Bản chất của Offset là việc di chuyển từ 1 vị trí này tới vị trí khác. Gồm có điểm gốc và điểm đến. Điểm đến sẽ cách điểm gốc bao nhiêu dòng, bao nhiêu cột, và theo hướng nào.

Offset trong VBA được viết như sau:

Expression có thể là 1 Range (vùng ô) hoặc 1 Cells (ô)

Khi viết thuộc tính Offset thì kèm theo dấu chấm, sau đó gọi tên thuộc tính theo cú pháp

Tham số bên trong Offset được viết trong cặp dấu ngoặc đơn, gồm:

Ví dụ1 : Range(“A1”).Offset(0, 1)

=> Điểm gốc là ô A1, di chuyển 0 dòng và 1 cột về bên phải => Kết quả là tham chiếu tới ô B1

Ví dụ 2: Cells(5, 3).Offset(-1, 2)

=> Điểm gốc là ô C5 (dòng 5 cột 3), di chuyển 1 dòng lên phía trên, 2 cột về bên phải => Kết quả tham chiếu tới ô E4 (dòng 4 cột 5)

* Chú ý:

Để biết kết quả của Range, Cells có phương thức Offset sẽ di chuyển tới đâu, lấy chính số dòng, số cột cộng trực tiếp tương ứng với tham số bên trong Offset. Kết quả của số dòng, cột mới chính là tọa độ của điểm mới sẽ di chuyển tới.

Ví dụ 1:

Range(“A1”) = Cells(1, 1)

Range(“A1”).Offset(0, 1) = Cells(1 + 0, 1 + 1) = Cells(1, 2) là dòng 1 cột 2, chính là ô B1

Ví dụ 2:

Cells(5, 3).Offset(-1, 2) = Cells(5 – 1, 3 + 2) = Cells(4, 5) là dòng 4 cột 5, chính là ô E4

Trong bảng danh sách nhân viên, hãy xác định mức thưởng cho nhân viên biết: Nếu nhân viên ở bộ phận kinh doanh có mức thưởng là 500:

Câu lệnh VBA là:

Trong ví dụ này, ta thấy câu lệnh:

bien_DoiTuong.Offset(0, 1).Value = Range(“F2”).Value

Câu lệnh này có ý nghĩa là kết quả được lấy vào vị trí ô tương ứng với ô bien_DoiTuong, cách ô đó 1 cột (chính là cột Mức thưởng) theo giá trị tại ô F2.

Khi sử dụng cú pháp For Each, chúng ta phải căn cứ vào biến đối tượng, kết quả phải có liên quan tới biến đối tượng. Do vậy dùng hàm Offset để di chuyển tới ô kết quả tương ứng theo ô Biến đối tượng.

Trong Excel, hàm OFFSET có chức năng trả về tham chiếu hoặc kết hợp với các hàm khác để tính toán có điều kiện. Vậy còn hàm OFFSET trong VBA thì được dùng như thế nào? Hãy để Gitiho hướng dẫn các bạn cách viết code VBA Excel cho hàm OFFSET trong bài viết dưới đây nhé.

Xem thêm: Giới thiệu về hàm OFFSET và các cách kết hợp hàm OFFSET trong Excel

XEM NHANH BÀI VIẾT

Hàm OFFSET trong VBA được viết như sau:

expression.Offset(_RowOffset_,_ColumnOffset_)

Trong đó:

Chúng ta có ví dụ các công thức hàm OFFSET trong VBA như sau:

Range(“A1”).Offset(0, 1)

Trong công thức code VBA Excel trên, điểm gốc là ô A1, di chuyển 0 dòng và 1 cột về bên phải điểm gốc. Vì vậy, kết quả là tham chiếu tới ô B1 trong trang tính Excel.

Cells(5, 3).Offset(-1, 2)

Trong công thức code VBA Excel này, điểm gốc là ô C5 (dòng 5 cột 3), di chuyển 1 dòng lên phía trên, 2 cột về bên phải. Từ đó ta có kết quả tham chiếu tới ô E4 (dòng 4 cột 5).

Lưu ý: Để biết điểm đến theo hàm OFFSET trong VBA, các bạn hãy cộng trực tiếp số dòng, cột di chuyển vào điểm gốc trong code VBA Excel này. Như vậy, các bạn sẽ tìm được tọa độ mới nơi Excel trả về kết quả.

Ví dụ như dưới đây:

Range(“A1”) nghĩa là Cells(1, 1)

Dựa vào đó, chúng ta tính được tọa độ mới của công thức hàm OFFSET trong VBA dưới đây:

Range(“A1”).Offset(0, 1) nghĩa là Cells(1 + 0, 1 + 1) = Cells(1, 2). Vậy là tọa độ mới ở dòng 1 cột 2, chính là ô B1 trong trang tính Excel.

Xem thêm: Hướng dẫn cách dùng hàm OFFSET để tạo danh sách động trong Excel

Chúng ta cùng tìm hiểu một ứng dụng của hàm OFFSET trong VBA giúp kế toán viên tính thưởng cho các nhân viên công ty như sau.

Để xác định mức thưởng chính xác cho các nhân viên, kế toán viên sử dụng code VBA Excel dưới đây:

Sub Thuong_BP_KinhDoanh()Dim bien_BoPhan As RangeSet bien_BoPhan = Range(“B2:B8”)Dim bien_DoiTuong As RangeFor Each bien_DoiTuong In bien_BoPhanIf bien_DoiTuong.Value = Range(“E2”).Value Thenbien_DoiTuong.Offset(0, 1).Value = Range(“F2”).ValueEnd IfNext bien_DoiTuongEnd Sub

Ta thấy trong công thức code có chứa hàm OFFSET trong VBA tại dòng

bien_DoiTuong.Offset(0, 1).Value = Range(“F2”).Value

Câu lệnh này có ý nghĩa như thế nào? Hàm OFFSET trong công thức code VBA Excel trên giúp Excel trả kết quả của code vào vị trí ô tương ứng với ô bien_DoiTuong, cách ô đó 1 cột (chính là cột Thưởng) theo giá trị tại ô F2.

Chúng ta vừa tìm hiểu cách viết hàm OFFSET trong VBA, quả thật là một thủ thuật rất hữu ích phải không nào? Không chỉ có hàm OFFSET mà chúng ta còn vận dụng được rất nhiều hàm khác vào code VBA Excel. Nếu các bạn muốn tìm hiểu những hàm đó là gì thì hãy nhanh tay truy cập blog Gitiho để đón đọc các bài viết của chúng mình nhé. Bên cạnh đó, hãy đăng kí khóa học Tuyệt đỉnh VBA để thành thạo bí kíp viết code VBA Excel nhé.

0 thảo luận

Sử dụng Offset trong VBA để chọn một ô hoặc dãy ô Excel

Trong bài này mình sẽ hướng dẫn cách sử dụng Offset trong VBA để chọn một ô hoặc một dãy ô Excel bằng VBA Code. Thuộc tính Offset VBA sẽ trả về một Ragne dựa vào vị trí của một Range khác (vị trí này ta gọi là điểm mốc, giống như mốc lộ giới vậy đó).

Đề bài: Cho một ô hoặc một dãy ô bất kì (có thể sử dụng Range, Cells, Rows để tạo), hãy tạo ra một dãy mới xuất phát từ vị trí của dãy đó.

Để giải bài này thì ta sử dụng hàm phương thức Offset

Tạo dataset tùy biến kích thước

Một trong số cách dùng phổ biến nhất của hàm OFFSET trong Excel là thu thập các bộ dữ liệu có thể thay đổi kích thước. Với một tập dữ liệu lớn, người ta có thể chỉ muốn xem xét một phần nhỏ dữ liệu tại thời điểm bất kỳ.

Ví dụ, cung cấp một báo cáo tự động tổng doanh số bán hàng của nhân viên, bạn cần lấy tổng số cho một hoặc nhiều tuần nhất định.

Viết một hàm SUM khác cho từng tổng số có thể khá dễ dàng. Tuy nhiên, dùng hàm OFFSET sẽ dễ dùng và tái sử dụng nhanh hơn.

Đầu tiên, nhập ngày tháng bạn muốn bắt đầu vào ô B6. Sau đó, nhập số ngày muốn chạy báo cáo vào B7. Tiếp theo, dùng hàm SUM và AVERAGE đơn giản để lấy nhiều dữ liệu hơn cho từng nhân viên theo thời gian lựa chọn:

=SUM(OFFSET(B2, 0, B6 - 1, 1, B7))

=AVERAGE(OFFSET(B2, 0, B6 - 1, 1, B7)

Điều chỉnh ngày tháng bắt đầu hoặc thời gian đơn giản như chỉnh sửa một ô, mà bạn có thể kéo các nhóm dữ liệu thay đổi kích thước dễ hơn nhiều.

Cách dùng hàm Offset để tính toán vùng dữ liệu động

Cú pháp của Offset VBA

Trước tiên hãy điểm qua một chút về cú pháp của nó đã nhé.

Bài viết này được đăng tại [free tuts .net]

Range.Offset(RowOffset, ColumnOffset)

Trong đó:

RowOffset là vị trí hàng so với Range.
- Hướng lên trên là số âm
- Hướng xuống dưới là số dương
ColumnOffset là vị trí cột so với Range.
- Qua bên trái là số âm
- Qua bên phải là số dương

Tham khảo hình ảnh dưới đây bạn sẽ hiểu rõ hơn.

Giả sử ô D6 là điểm mốc. Dựa vào điểm này mình muốn chọn F3 thì công thức sẽ là:

Range(“A6”).Offset(-3, 2).Select

Cho phép tạo báo cáo tùy chỉnh

Dùng phương pháp tương tự như trên để lập một báo cáo hoàn chỉnh bằng việc tùy chỉnh biến khá dễ. Bạn có thể tạo đối số đầu tiên trong hàm OFFSET tham chiếu sheet khác. Việc này cho phép bạn tách riêng mục nhập dữ liệu và báo cáo.

Hành động trên cũng giúp bạn có thể kéo các phần dữ liệu đã chọn từ trang tính này sang trang tính khác. Ngoài ra, nó cho phép bạn chỉ hiển thị thông tin liên quan đến báo cáo hiện tại. Việc thiết lập một báo cáo hiển thị tổng số hàng ngày bằng cách sử dụng cùng một dữ liệu từ trước khá đơn giản. Bạn có thể làm điều này mà không cần lấy dữ liệu cho những ngày không liên quan.

Hàm offset sau được đặt ở ô B5 sẽ lấy số ngày được chỉ định ở B2. Nó bắt đầu vào tuần được chọn ở B1 và chỉ hiện dữ liệu cho giai đoạn liên quan:

=OFFSET(Sheet1!B2, 0, (B1 - 1) * 7, 1, B2 * 7)

Có thể hạn chế các mục trong B1 và B2, đồng thời, tạo danh sách thả xuống trong Excel với số tuần có sẵn bằng cách sử dụng xác thực dữ liệu.

Công thức lập báo cáo chi tiết tự động với hàm offset và index match

Cú pháp và cách sử dụng hàm OFFSET trong Excel

Cú pháp hàm OFFSET

Cú pháp: =OFFSET(reference, rows, cols,[height],[width])

Trong đó:

Reference: là vùng tham chiếu dùng làm cơ sở cho hàm để tạo một vùng tham chiếu mới.
Rows: Là số dòng nằm bên trên hoặc bên dưới reference, được tính từ ô đầu tiên (ô ở góc trên bên trái) của reference.
Cols: Là số cột nằm bên trái hoặc bên phải reference, được tính từ ô đầu tiên (ô ở góc trên bên trái) của reference.
Height: Là số dòng của vùng tham chiếu cần được trả về. Height luôn phải là số dương
Width: Là số cột của vùng tham chiếu cần được trả về. Width luôn phải là số dương.

Lưu ý:

Nếu các đối số rows và cols làm cho vùng tham chiếu trả về vượt ra khỏi phạm vi của trang tính thì kết quả của hàm sẽ báo lỗi #REF.
Nếu bỏ qua các đối số là height và width thì tham chiếu sẽ trả về mặc định có cùng chiều cao và độ rộng với vùng tham chiếu.
Nếu các đối số là height và width >1 có nghĩa là hàm phải trả về nhiều hơn 1 ô thì các bạn phải sử dụng công thức mảng của hàm OFFSET nếu không kết quả sẽ nhận lỗi #VALUE!
Đối số height và width phải luôn là số dương.
Hàm OFFSET sẽ không di chuyển hay làm thay đổi bất kỳ phần nào được chọn vì hàm đó chỉ trả về một tham chiếu. Các bạn cũng có thể sử dụng hàm OFFSET kết hợp với các hàm cần đến một đối số tham chiếu

Cách sử dụng hàm OFFSET trong Excel

Ví dụ 1: Bạn hãy sử dụng hàm OFFSET để lấy ra giá trị ở vị trí cột 2 và dòng 3 trong bảng tính sau:

Bước 1: Trong bảng tính Excel, bạn hãy nhập hàm =OFFSET(B3,3,2) vào ô tham chiếu mà bạn muốn hiển thị kết quả.

Bước 2: Nhấn phím Enter để hiển thị kết quả trả về.

Ví dụ 2: Bạn hãy sử dụng hàm OFFSET và hàm SUM để tính tổng của cột D trong bảng dưới đây.

Cùng chuyên mục:

Cách chèn link ảnh vào Excel trên máy tính

Ngoài ra, bài viết còn hướng dẫn cách chèn link ảnh trực tuyến hàng loạt…

Cách tách và gộp ô trong Excel

Trong bảng tính Microsoft Excel, có thể sẽ có lúc các bạn cần tách ô…

Anh chị cho e hỏi cái dòng code “ActiveCell.Offset(1, 0).Range(“A1″).Select” có nghĩa là sao ah? có thể giải thích cho e chi tiết không ah.Khi nào thì sử dụng nó.Nó Có tác dụng gì?[GPECODE=vb]Sub diachituongdoi()ActiveCell.FormulaR1C1 = ” Ninh Thi Tien”ActiveCell.Offset(1, 0).Range(“A1”).SelectEnd Sub[/GPECODE]

Khi nào thì sử dụng nó.

Nó Có tác dụng gì?

[GPECODE=vb]

Sub diachituongdoi()

ActiveCell.FormulaR1C1 = ” Ninh Thi Tien”

ActiveCell.Offset(1, 0).Range(“A1”).Select

End Sub

[/GPECODE]

Hàm OFFSET trong Excel là hàm được sử dụng để tham chiếu hoặc tính toán dữ liệu của một ô hay một dãy trong bảng tính dựa vào một vùng tham chiếu đã có sẵn trước đó. Nếu như bạn chưa biết cách sử dụng hàm này thì hãy cùng Daotaotinhoc theo dõi bài viết dưới đây để hiểu rõ hơn về hàm OFFSET và cách sử dụng hàm OFFSET này bạn nhé!

Cách dùng hàm Offset, kết hợp offset và match, ứng dụng trong Excel

Các ví dụ về Offset trong VBA

Thuộc tính Offset được sử dụng để trả về một ô hoặc một dải ô, liên quan đến một ô hoặc dải ô được chỉ định.

Sử dụng Offset với Range

Bạn có thể sử dụng đoạn code sau: Sử dụng đối tượng Range và thuộc tính Offset để chọn ô B2, nếu ô A1 là mốc:

Range(“A1”).Offset(1, 1).Select

Sử dụng Offset với Cells

Tương tự, bạn cũng có thể kết hợp Offset và Cells.

Cells(4, 4).Offset(-1, -1).Select

Sử dụng Offset với một dãy ô

Những ví dụ trên thì điểm mốc là một ô nên rất dễ dàng. Nhưng trường hợp bạn có điểm mốc là một dãy ô thì sao nhỉ? Rất đơn giản, nó sẽ lấy ô đầu tiên trong dãy làm điểm mốc.

Ví dụ: Điểm mốc là A1:A5