Cách sử dụng hàm Offset trong Excel kèm ví dụ minh họa

Cách sử dụng hàm Offset trong Excel kèm ví dụ minh họa

Mục lục

Trong excel có rất nhiều hàm, nhưng bạn chỉ làm được một vài hàm đơn giản. Với hàm OFFSET cũng vậy, bạn đã  rõ cách dùng của hàm này cụ thể như thế nào chưa?
Bài viết này sẽ giúp giải quyết có các bạn 5 vấn đề về sử dụng hàm OFFSET hay gặp phải. Mời bạn đọc cùng theo dõi.

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

Hàm OFFSET trong excel trả về một ô hoặc phạm vi ô là số lượng hàng và cột được chỉ định từ một ô hoặc một phạm vi ô.

Trong Excel, hàm OFFSET được sử dụng với mục đích:

- Tiết kiệm thời gian trong việc tính toán dữ liệu của một ô.

- Hàm này kết hợp với hàm khác giúp bạn nâng cao hiệu suất làm việc. 

2. Cú pháp của hàm OFFSET?

=OFFSET (tham chiếu, hàng, cols, chiều cao, chiều rộng )

Trong đó: 

+ Tham chiếu: là khu vực mà bạn muốn tham chiếu đến phần nào đó

+ Hàng: có thể là tích cực hoặc tiêu cực.

+ Cols: Số lượng cột, ở bên trái hoặc bên phải, mà bạn muốn ô trên bên trái của tham chiếu được trả về để tham chiếu. Ví dụ, sử dụng cols bằng 2 chỉ định rằng ô trên bên trái của tham chiếu được trả về sẽ là hai cột ở bên phải của ô tham chiếu trên cùng bên trái . Cols có thể là tích cực hoặc tiêu cực.

+ Chiều cao: tùy chọn, theo số lượng hàng, mà bạn muốn tham chiếu được trả về. Chiều cao phải là một số dương. Nếu chiều cao bị bỏ qua, tham chiếu được trả về sẽ có cùng chiều cao với tham chiếu.

+ Chiều rộng: tùy chọn, theo số lượng cột, mà bạn muốn tham chiếu được trả về. Chiều rộng phải là một số dương. Nếu chiều rộng bị bỏ qua, tham chiếu được trả về sẽ có cùng chiều rộng với tham chiếu.

ham-OFFSET-1.png

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

3. Ví dụ về cách dùng hàm OFFSET

Ví dụ 1

- Trong ô B8 trả về giá trị của ô E6 thông qua ô tham chiếu cơ sở B5.

- Tại ô B8 các bạn nhập công thức:OFFSET(B5,1,3,1,1)

cach-su-dung-ham-offset-1.jpg
Ví dụ về cách sử dụng hàm offset. Hình 1

>> Xem thêm: Hàm Isnumber và các cách dùng Isnumber trong Excel

Ví dụ 2

- Hàm OFFSET bên dưới trả về ô có 3 hàng bên dưới và 2 cột bên phải ô A2. Hàm OFFSET trả về một ô vì cả chiều cao và chiều rộng đều được đặt thành 1.

- Ta được kết quả như hình dưới:

cach-su-dung-ham-offset-2.jpg
Ví dụ về cách sử dụng hàm offset trong excel. Hình 2

4. Hàm OFFSET thường kết hợp với hàm nào?

Nhìn phần lý thuyết bên trên thì rất nhiều bạn còn chưa hiểu và thắc mắc tại sao. Tuy nhiên, tôi sẽ minh họa ví dụ kỹ hơn phía dưới cho các bạn:

Hàm OFFSET kết hợp với hàm SUM

- Ví dụ: Tính tổng lương và thưởng của nhân viên Nguyễn Văn B bắt đầu từ ô B6 (lương của nhân viên đầu tiên).

- Áp dụng hàm: =SUM(OFFSET(B6,3,0,1,2) các bạn sẽ được kết quả như sau:

cach-su-dung-ham-offset-3.jpg
Ví dụ về cách sử dụng hàm offset trong excel. Hình 3

Hàm OFFSET kết hợp với hàm AVERAGE, MAX, MIN

- Bước 1: Cho ví dụ như mô tả dưới ảnh, để tính giá trị trung bình, bạn nhập công thức sau: =AVERAGE(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1)).

ham-OFFSET-2.jpg

Hình minh họa

- Bước 2: Nhấn Enter để hiển thị kết quả.

ham-OFFSET-3.jpg?

Hình minh họa

- Bước 3: Để tính số lớn nhất trong dải, bạn nhập công thức sau: =MAX(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1)).

ham-OFFSET-4.jpg

Hình minh họa

- Bước 4: Nhấn Enter để nhận kết quả.

ham-OFFSET-5.jpg

Hình minh họa

- Bước 5: Để tìm số nhỏ nhất trong dải, bạn nhập công thức sau: =MIN(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1)).

ham-OFFSET-6.jpg

Hình minh họa

- Bước 6: Nhấn Enter để nhận kết quả.

ham-OFFSET-7.jpg

Hình minh họa

Hàm OFFSET kết hợp với hàm VLOOKUP

- Bước 1: Trong ô tham chiếu muốn hiển thị kết quả, bạn nhập công thức =VLOOKUP(B1,A5:B11,2,FALSE).

ham-OFFSET-8.jpg

Hình minh họa

- Bước 2: Nhấn Enter để nhận kết quả. 

ham-OFFSET-10.jpg

Hình minh họa

>> Xem thêm: Cách dùng hàm address - Hàm trả về địa chỉ ô trong Excel

Ngoài ra, hàm OFFSET có thể kết hợp với nhiều hàm khác để tính toán và xử lý dữ liệu trong excel. Hàm sẽ giúp ích các bạn rất nhiều nếu các bạn biết áp dụng và kết hợp hàm một cách hiệu quả. Để nâng cao kiến thức cũng như kỹ năng áp dụng vào làm việc và học tập Unica còn mang đến bạn đọc những kiến thức xoay quanh lĩnh vực tin học văn phòng với các khoá học Powerpoint, học Word, học Excel từ cơ bản đến nâng cao,... đang được nhiều người quan tâm. Mời bạn đọc cùng khám phá.

Đánh giá :

Tags: Excel