Hàm OFFSET trong Excel – Kết hợp với hàm MATCH, Validation

Trong bài viết hôm nay, Kienit.com sẽ chia sẻ với các bạn OFFSET là gì và cách sử dụng hàm OFFSET trong Excel như thế nào. Mời các bạn cùng tham khảo bài viết dưới đây.

1/ Hàm OFFSET trong Excel là gì?

Đây là một hàm Excel nâng cao, hàm OFFSET là hàm dùng để tính giá trị của một ô hoặc một dãy dựa trên vùng tham chiếu được chọn.

Trong công việc, OFFSET được dùng để tính toán các vùng dữ liệu động hoặc kết hợp vác hàm khác.

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

Trong đó:

Reference: Là vùng tham chiếu bắt buộc phải nhập.

Rows: Là số dòng bên trên hoặc bên dưới reference

Cols: Là số cột bên trái hoặc bên phải reference

Height: Là số dòng của vùng tham chiếu cần trả về. Height phải là số dương.

Width: Là số cột của vùng tham chiếu cần trả về. Width phải là số dương.

Lưu ý:

2 tham số Cols và Rows có thể là số âm hoặc số dương và phải là số nguyên. Trong trường hợp nếu bạn nhập số Cols hoặc Rows vượt quá vùng giới hạn sẽ bị trả về kết quả là #REF!

2 tham số Height và Width bắt buộc là số dương. Nếu bạn thêm 2 tham số này là số âm thì cũng bị lỗi #REF!

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

Cũng là tính toán giá trị nhưng đây là hàm Excel nâng cao nên bạn cần theo dõi các ví dụ sau đây hiểu được các hoạt động và sử dụng như thế nào.

Cách hoạt động của OFFSET

Nhiều bạn mới nhìn vào sẽ thắc mắc về các tham số truyền vào công thức cho nên để minh họa cách hoạt động của hàm thì Kienit.com đã chuẩn bị một bảng giá trị sau đây. Mình lấy ô C2 ( tô màu vàng ) làm ô gốc.

Để tìm đến giá trị ô A1 thì mình nhập vào công thức của hàm OFFSET như sau:

=OFFSET(D6,-2,-1)

Trong đó:

• D6 là giá trị tham chiếu ( Reference ). Phải có tham chiếu làm gốc thì hàm mới hiểu di chuyển tìm giá trị mong muốn.
• -2 là là đi ngược lên 2 dòng.
• -1 là là đi ngược sang trái 1 cột

Một ví dụ khác là mình sẽ tìm ô E3 thì công thức lúc này sẽ là:

=OFFSET(D6,2,1)

Trong đó:

• D6 vẫn là ô tham chiếu
• 2 là số dòng từ ô tham chiếu đi xuống nên là dương 2
• 1 là số cột từ ô tham chiếu qua phải một ô nên là dương 1

Để dùng hàm OFFSET trong Excel, cách đơn giản là bạn phải truyền vào 3 tham số: ô tham chiếu để làm gốc, số dòng và số cột để hàm di chuyển đến giá trị cần tìm. 

Để đến với cách hoạt động của hàm OFFSET nâng cao thì mời các bạn đến với ví dụ dưới đây:

Ở ví dụ này mình sẽ thêm vào 2 tham số mới đó chính height và width. Trong đó height là 2 và width = 2

=OFFSET(D6,-2,-1,2,2)

Trong đó:

• D6 vẫn là ô tham chiếu gốc
• -2 là từ ô tham chiếu lên trên 2 dòng
• -1 là từ ô tham chiếu qua trái 1 cột
• 2 là chiều cột của mảng cần trả về là 2 dòng
• 2 là chiều rộng của mảng cần trả về là 2 cột

Kết quả cuối cùng là một mảng. Tuy nhiên có một số phiên bản Excel sẽ hiện ra #VALUE! Các bạn cứ yên tâm vì đây không phải lỗi đâu, vì khi thêm height và width nên hàm OFFSET sẽ trả về kết quả là dạng mảng nhưng không hiện hết trong một ô nên mới hiện ra kết quả #VALUE!

Để kiểm tra, các bạn nhấn vào công thức sau đó nhấn F2 để hiện ra công thức, bôi đen vùng công thức và nhấn F9. Bạn sẽ thấy các giá trị hiện ra theo dạng mảng không phải giá trị 1 ô. Giống như bạn nhập mảng A1:B3 thì cũng sẽ ra lỗi #VALUE!

Ví dụ sử dụng hàm OFFSET

Mình đã có chuẩn bị một ví dụ như hình dưới đây:

Bình thường để tính doanh thu trung bình các ngày thì sẽ dùng hàm AVERAGE. Tuy nhiên một khó khăn là hàm này sẽ không tự tính toánh giá trị trung bình khi có thêm một ngày mới.

Đầu tiên mình sẽ áp dụng hàm OFFSET và AVERAGER để tính doanh thu trung bình 2 ngày như sau:

=AVERAGE(OFFSET(B3,1,0,2,1))

Trong đó:

• Hàm AVERAGE là hàm tính giá trị trung bình, mình sẽ truyền vào mảng cần tính
• Hàm OFFSET để gom các giá trị dựa trên ô tham chiếu B3
• 1 là bắt đầu từ ô tham chiếu sẽ xuống một dòng
• 0 là do mình vẫn tính ở cột doanh thu, không dịch chuyển đi đâu
• 2 là chiều cao các giá trị mà OFFSET sẽ lấy ra. Nếu muốn tính trung bình bao nhiêu ô thì thay đổi tham số height này.
• 1 là chiều rộng vẫn là ở cột doanh thu

Để tính doanh thu trung bình số ngày ( 3 ngày ) khi thêm ngày mới vào, Kienit sẽ thêm hàm COUNTA để đếm các ô không trống.

=AVERAGE(OFFSET(B3,COUNTA(B4:B10)-2,0,3,1))

Trong đó:

• Hàm AVERAGE sẽ tính trung bình 3 ngày gần nhất
• OFFSET sẽ gom 3 cột được chọn để tính trung binh dựa vào ô tham chiếu
• B3 là ô tham chiếu cố định
• COUNTA(B4:B10)-2: Đếm các ô không rỗng trong từ B4:B10. Sau đó sẽ trừ 2 ô cuối cùng. Ví dụ từ B4:B10 ta có 5 ô, để hàm OFFSET xuống đúng 4 dòng ta phải trừ đi 2 ô cuối cùng.
• 0 là vẫn tính tại cột doanh thu
• 3 là chiều cao, là 3 ngày ta cần tính trung bình
• 1 là chiều rộng, vẫn là cột doanh thu

Đến một ví dụ khác, Kienit sẽ kết hợp hàm OFFSET và Validation như sau

Đầu tiên mình sẽ dùng Validation để tạo các tháng để chọn như sau:

• Bước 1: Vào tab Data chọn Validation
• Bước 2: Tại mục Allow chọn List và Source sẽ bôi đen toàn bộ các tháng
• Bước 3: Chọn OK để hoàn tất tạo Validation list cho các tháng

Tiếp theo là mình sẽ dùng hàm OFFSET và MATCH trong Excel như sau.

=AVERAGE(OFFSET(B3,MATCH(D4,A4:A9,0),0,3,1))

Trong đó:

• Hàm AVERAGE sẽ tính giá trị trung bình các tham số nhập vào
• OFFSET sẽ gom giá trị 3 ngày từ ô tham chiếu
• B3 là ô tham chiếu
• MATCH(D4,A4:A9,0): Hàm MATCH  sẽ dò tìm giá trị trong cột  tháng trùng với tháng được chọn trong khi nãy đã tạo bằng Validation list.
• 0 là vẫn cột doanh thu
• 3 là chiều cao, là số ngày cần tính tính trung bình.
• 1 là chiều rộng của cột.

 

Khi đã làm xong, bạn có thể thay đổi tháng tùy ý và kết quả tính trung bình sẽ theo tháng mà bạn đã đã chọn.

Bài viết đã chia sẻ đầy đủ về cách sử dụng hàm OFFSET trong Excel, cách kết hợp với các hàm khác như thế nào. Đây là một hàm Excel nâng cao nên các bạn cần áp dụng nhiều để quen việc kết hợp với các công thức khác.

Hãy theo dõi các bài viết mới tại chuyên mục học Excel online để cập nhật thêm kiến thúc về Excel mỗi ngày. Chúc các bạn sẽ thành công.

Bài viết xem thêm:

Hàm SUMPRODUCT tính tổng nhiều điều kiện trong Excel

Hàm REPT trong Excel, Hàm lặp lại ký tự, chuỗi hoặc số

Hàm REPLACE trong Excel, Cách thay thế 1 phần trong chuỗi ký tự

Hàm SUBSTITUTE trong Excel là gì? Ví dụ cách dùng dễ hiểu

Hàm TRIM trong Excel, Cách xóa khoảng trống dư thừa