Hàm OFFSET là gì? Cách sử dụng hàm OFFSET trong Excel cơ bản

TÁC GIẢ Lyttn
, Tin tức
, Tháng 10 3, 2024
9:00 sáng

Nội dung bài viết

Hàm OFFSET trong Excel là một hàm hữu ích để giúp người dùng có thể tạo tham chiếu một cách nhanh chóng. Vậy công thức của hàm OFFSET là gì, nó có tính ứng dụng gì và cách sử dụng nó như thế nào? Hãy cùng với chúng tôi tìm hiểu về hàm Excel này ngay trong bài viết sau đây nhé.

Hàm OFFSET là gì?

Hàm OFFSET trong Excel là một hàm phân tích dữ liệu mạnh mẽ trong phần mềm Microsoft Excel, có công dụng giúp người dùng có thể tạo ra một tham chiếu động đến một phạm vi cụ thể trong bảng tính. Tham chiếu được trả về có thể sẽ là một ô hoặc là một phạm ô nhất định và người dùng cũng có thể nêu rõ số hàng và số cột cần được trả về.

Với hàm OFFSET, người dùng có thể tham chiếu đến một vùng dữ liệu thay đổi kích thước hoặc vị trí trong bảng tính của mình. Ví dụ, người dùng có thể sử dụng hàm OFFSET để tổng hợp dữ liệu từ một số hàng cuối cùng của một danh sách hoặc cũng có thể tạo ra một dải dữ liệu động mà có thể thay đổi kích thước dựa trên các điều kiện khác nhau.

Cú pháp của hàm OFFSET là gì?

Hàm OFFSET trong Excel có công thức như sau:

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

Trong đó, các đối số của hàm OFFSET Excel được giải thích như sau:

Reference (vùng tham chiếu): Là thông số bắt buộc, đây là vùng tham chiếu mà người dùng muốn chỉ định khoảng cách tham chiếu vào đó.
Rows (hàng): Là một thông số bắt buộc, đây là số hàng lên hoặc xuống mà người dùng muốn ô ở góc trên phía bên trái tham chiếu tới. Ví dụ, người dùng dùng số 5 làm đối số hàng, thì công thức sẽ xác định rằng ô ở góc trên bên trái trong tham chiếu cách vùng tham chiếu với khoảng cách là 5 ô về phía dưới.
Cols (cột): Cũng là một thông số bắt buộc, được xác định là số cột về bên trái hoặc bên phải mà người dùng muốn ô ở góc phía trên bên trái trong kết quả tham chiếu tới. Ví dụ, nếu thông số này được người dùng sử dụng là số 5, thì công thức sẽ xác định rằng ô ở góc phía trên bên trái trong phép tham chiếu cách vùng tham chiếu 5 cột về bên phía phải.
Height (Chiều cao): Đây là một thông số tùy chọn dùng để xác định chiều cao, thông số này được tính bằng số hàng mà người dùng muốn dùng cho tham chiếu trả về.
Width (Chiều rộng): Đây cũng là một thông số tùy chọn trong hàm OFFSET Excel, được dùng để giúp người dùng có thể xác định được độ rộng, thông số này được tính bằng số cột mà người dùng muốn dùng cho tham chiếu trả về.

Chú thích:

Nếu đối số hàng và cột trong công thức của người dùng làm cho vùng tham chiếu trả về vượt ra ngoài đường biên của trang tính, thì kết quả của hàm OFFSET Excel sẽ trả về giá trị lỗi #REF!.
Nếu đối số chiều cao và độ rộng không có trong công thức của người dùng, thì công thức sẽ tự động giả định rằng có cùng chiều cao và độ rộng của vùng tham chiếu.
Hàm OFFSET Excel không có khả năng di chuyển bất kỳ ô nào hoặc thay đổi vùng được chọn mà nó chỉ trả về một kết quả tham chiếu. Vì thế, người dùng có thể dùng hàm OFFSET Excel kết hợp với bất kỳ hàm nào mà đang cần đến một đối số tham chiếu.

Ứng dụng của hàm OFFSET trong Excel là gì?

Hàm OFFSET trong Excel là một công thức có tính linh hoạt cao, cho phép người dùng tạo ra các tham chiếu động đến các ô hoặc phạm vi ô khác nhau. Vì thế nó có tính ứng dụng cực cao trong các trường hợp mà người dùng muốn lọc dữ liệu hoặc tổng hợp dữ liệu. Chẳng hạn, người dùng có thể sử dụng trong những tính huống sau đây:

Có thể sử dụng Hàm OFFSET Excel để tạo ra các bảng tính tự động cập nhật, như trong các trường hợp mà số lượng hàm trong các bảng dữ liệu của họ có thể bị thay đổi theo thời gian.
Hàm Excel này cũng rất hữu ích trong việc giúp người dùng tạo các danh sách phụ thuộc, khi mà các lựa chọn trong một danh sách thả xuống của họ có thể thay đổi dựa trên những sự lựa chọn từ một danh sách khác.
Hàm OFFSET cũng có tính ứng dụng cao trong việc giúp người dùng có thể tạo ra các phạm vi đặt tên động. Điều này sẽ giúp việc quản lý dữ liệu của người dùng trở nên dễ dàng hơn trong các trường hợp khi mà những dữ liệu được thêm hoặc bớt đi.

Nhìn chung, hàm OFFSET trong Excel có nhiều tính ứng dụng khác nhau trong việc giúp người dùng có thể cập nhật dữ liệu tự động, lọc dữ liệu và tổng hợp dữ liệu. Để hiểu rõ hơn về hàm tham chiếu OFFSET Excel và nắm được cách để sử dụng nó, bạn hãy tiếp tục theo dõi nội dung bài viết này của chúng tôi nhé.

Cách sử dụng hàm OFFSET trong Excel chi tiết

Để nắm rõ về cách sử dụng hàm OFFSET trong Excel, bạn hãy theo dõi ví dụ dưới đây của chúng tôi nhé:

Ví dụ: Trong một bảng tính có chứa các giá trị như sau:

	A	B	C	D
1	Tên nhân viên	Mã số nhân viên	Thu nhập bình quân năm (vnđ)	Số năm làm việc
2	Nguyễn Văn A	123456	150.000.000	1
3	Nguyễn Thị B	123457	200.000.000	1.5
4	Trần Văn C	123458	250.000.000	2
5	Trần Thị D	123459	300.000.000	2.5

Yêu cầu 1: Hãy lấy giá trị Thu nhập bình quân năm của nhân viên Nguyễn Thị B trong bảng tính trên.

*Cách sử dụng hàm OFFSET trong Excel chi tiết*

Kết quả: Để lấy giá trị Thu nhập bình quân năm của nhân viên Nguyễn Thị B trong bảng tính trên, người dùng có thể sử dụng hàm OFFSET Excel. Người dùng hãy chọn 1 ô tính để xuất kết quả và nhập vào ô tính đó công thức: =OFFSET(A1,2,2). Kết quả sẽ là 200.000.000 (vnđ).

Yêu cầu 2: Hãy lấy giá trị mã nhân viên của nhân viên Nguyễn Thị B trong bảng tính trên.

*Cách sử dụng hàm OFFSET Excel chi tiết*

Kết quả: Để lấy giá trị mã nhân viên của nhân viên Nguyễn Thị B trong bảng tính trên, người dùng có thể sử dụng hàm OFFSET Excel bằng cách chọn 1 ô tính để xuất kết quả và nhập vào ô tính đó công thức: =OFFSET(A1,2,1). Kết quả sẽ là 123457.

Cách kết hợp hàm OFFSET với các hàm Excel khác

Người dùng có thể sử dụng hàm OFFSET Excel kết hợp với những hàm khác để tối ưu quy trình và đạt được hiệu quả cao nhất khi làm việc với những dữ liệu trong phần mềm Microsoft Excel. hãy cùng chúng tôi tìm hiểu qua về những hàm có thể kết hợp với hàm OFFSET trong Excel ngay trong nội dung dưới đây nhé:

Kết hợp hàm SUM với hàm OFFSET trong Excel

Người dùng có thể sử dụng hàm SUM với hàm OFFSET trong Excel để tính tổng của một phạm vi dữ liệu liền kề. Bởi hàm OFFSET sẽ cho phép bạn tạo ra một tham chiếu động đến một phạm vi cụ thể trong bảng tính, còn hàm SUM sẽ giúp bạn có thể tính được tổng giá trị của phạm vi đó. Sự kết hợp này sẽ mang đến cho bạn hiệu quả cao trong việc tính tổng dữ liệu cho các báo cáo định kỳ mà khi mà phạm vi dữ liệu thay đổi theo thời gian.

Bạn có thể áp dụng công thức sau đây để tính tổng một vùng dữ liệu mà vị trí và kích thước có thể thay đổi dựa trên các tham số mà bạn muốn:

=SUM(OFFSET(reference, rows, cols, height, width))

Yêu cầu 3: Tính tổng thu nhập bình quân năm của toàn bộ nhân viên

Kết quả: Để tính tổng thu nhập bình quân năm của toàn bộ nhân viên trong bảng tính trên, người dùng có thể kết hợp hàm SUM với hàm OFFSET Excel, bằng cách chọn 1 ô tính để xuất kết quả và nhập vào ô tính đó công thức:

=SUM(OFFSET(A2:A5;1;2)).

–>Kết quả sẽ là 750.000.000 (vnđ).

Kết hợp hàm Average, Min, Max với hàm OFFSET trong Excel

Trong bảng tính Excel, việc kết hợp hàm OFFSET với các hàm thống kê như Average, Min, Max sẽ giúp bạn tạo ra các phép thống kê tự động. Điều này sẽ cho phép bạn tính toán được các giá trị thống kê này trên một dải dữ liệu mà không cần cập nhật công thức mỗi khi dữ liệu có sự thay đổi.

Để tính giá trị trung bình của một dải dữ liệu mà số hàng và cột có thể thay đổi theo thời gian, bạn hãy sử dụng công thức:

=AVERAGE(OFFSET(reference, rows, cols, height, width))

Để tính giá trị lớn nhất của một dãy dữ liệu có số hàng và cột có thể thay đổi theo thời gian, bạn hãy sử dụng công thức:

=MAX(OFFSET(reference, rows, cols, height, width))

Để tính giá trị nhỏ nhất của một dãy dữ liệu có số hàng và cột có thể thay đổi theo thời gian, bạn hãy sử dụng công thức:

=MIN(OFFSET(reference, rows, cols, height, width))

Kết hợp hàm Vlookup với hàm OFFSET trong Excel

Sự kết hợp hàm VLOOKUP với hàm OFFSET trong Excel giúp bạn có thể tạo ra một công thức có khả năng cập nhật tự động khi các dữ liệu có sự thay đổi, nó đặc biệt hữu ích trong những bảng tính lớn. Hàm VLOOKUP cho phép bạn tìm kiếm và trả về giá trị từ một cột cụ thể dựa trên một khóa tìm kiếm, còn hàm OFFSET giúp trả về cho bạn một tham chiếu đến một phạm vi cụ thể được xác định bởi một số hàng và cột nhất định.

Trên đây là thông tin chi tiết về hướng dẫn cách sử dụng hàm OFFSET trong Excel mà chúng tôi muốn cung cấp đến cho bạn. Để sử dụng nó một cách hiệu quả nhất, bạn cần phải tuân thủ một số quy tắc cơ bản. Để biết được những điều cần lưu ý khi sử dụng hàm OFFSET Excel, bạn hãy tiếp tục theo dõi nội dung cuồi cùng của bài viết này nhé.

Những điều cần lưu ý khi sử dụng hàm OFFSET Excel

Để đạt được hiệu quả cao nhất khi sử dụng hàm OFFSET trong Excel, có một số lưu ý mà bạn cần phải nắm rõ khi thực hiện hàm này như sau:

Trong vùng tham chiếu, người dùng phải tham chiếu tới một ô hoặc một phạm vi các ô liền kề, nếu không hàm OFFSET Excel sẽ trả về cho bạn giá trị lỗi #VALUE!.
Hàng được sử dụng trong công thức này có thể là số dương hoặc cũng có thể là số âm. Khi nhập số dương cho giá trị này, thì công thức sẽ xác định là hàng ở vị trí bên dưới vùng tham chiếu bắt đầu. Còn khi nhập số âm cho giá trị này, thì công thức sẽ xác định là hàng ở vị trí bên trên vùng tham chiếu bắt đầu.
Cột được sử dụng trong công thức này cũng có thể là số dương hoặc số âm. Khi nhập số dương cho giá trị này, thì công thức sẽ xác định là cột ở vị trí bên phải vùng tham chiếu bắt đầu. Còn khi nhập số âm cho giá trị này, thì công thức sẽ xác định là cột ở vị trí bên trái vùng tham chiếu bắt đầu.
Đối với tham số chiều cao và chiều rộng, người dùng cần phải nhập vào giá trị là số dương, nếu không kết quả sẽ nhận về lỗi #VALUE!.
Việc sử dụng hàm OFFSET trong Excel có thể làm chậm đi tốc độ tính toán trong bảng tính Excel của bạn bởi vì nó sẽ tạo ra các tham chiếu động. Người dùng cũng có thể sử dụng những hàm INDEX hoặc hàm INDIRECT trong excel như một phương pháp thay thế cho hàm OFFSET trong Excel để nhận về cùng một kết quả mà không làm chậm đi tốc độ tính toán trong Microsoft Excel.

Xem thêm: Hàm đếm số lần xuất hiện trong Excel

Lời kết

Trong bài viết này, chúng tôi đã cung cấp cho bạn những thông tin chi tiết nhất về hàm OFFSET trong Excel, từ đó giúp bạn có thể hiểu được hàm OFFSET là gì và công thức của nó như thế nào. Bên cạnh đó, chúng tôi cũng đã cung cấp cho bạn những ví dụ về hàm OFFSET Excel cùng với những lưu ý quan trọng để giúp bạn có thể nắm được cách sử dụng hàm OFFSET trong Excel hiệu quả nhất.

Ngoài ra, để đạt được hiệu quả tốt nhất khi sử dụng hàm này và bảo vệ an toàn cho những dữ liệu của mình, người dùng cũng cần phải sử dụng những phiên bản phần mềm Microsoft Excel được cung cấp trong bản quyền trong những gói dịch vụ Microsoft 365.

Hiện nay, bạn có thể đăng ký sử dụng phần mềm này ngay tại MSO, chúng tôi hiện đang là đối tác hàng đầu của Microsoft được ủy quyền để cung cấp các gói dịch vụ Microsoft 365 bản quyền chính hãng tại thị trường Việt Nam. Cách thức đăng ký, bạn hãy liên hệ đến chúng tôi qua những thông tin liên hệ dưới đây để được hỗ trợ nhanh chóng nhất nhé.

———————————————————

Fanpage: MSO.vn – Microsoft 365 Việt Nam

Hotline: 024.9999.7777