Hàm OFFSET trong Excel: Cách dùng để tham chiếu mọi vùng dữ liệu

Khi làm việc với Excel, bạn có thể gặp phải những tình huống cần di chuyển hoặc tham chiếu đến các ô và vùng dữ liệu không cố định, nhưng lại phải đảm bảo tính linh hoạt và chính xác. Hàm OFFSET trong Excel chính là công cụ giúp bạn làm điều này một cách dễ dàng. Hãy cùng AntBook đi tìm hiểu từ A – Z để giúp nâng cao hiệu suất và tiết kiệm thời gian làm việc với những dữ liệu phức tạp nhé!

1. Khái niệm hàm OFFSET trong Excel là gì?

Hàm OFFSET trong Excel là một công cụ cho phép bạn tham chiếu đến các ô hoặc dãy ô một cách linh hoạt, dựa trên một vùng tham chiếu đã có sẵn. Hàm này không chỉ giúp xác định vị trí của các ô, mà còn có thể tính toán và xử lý dữ liệu từ những ô hoặc dãy ô được chỉ định, nhờ vào khả năng dịch chuyển theo hàng hoặc cột.

Hàm OFFSET là hàm được sử dụng để tham chiếu hoặc tính toán dữ liệu

Ứng dụng của hàm OFFSET trong Excel:

• Di chuyển tham chiếu linh hoạt: Hàm OFFSET giúp bạn tính toán hoặc tham chiếu đến dữ liệu ở vị trí tương đối so với ô hiện tại, mang lại sự linh hoạt trong việc xử lý dữ liệu.
• Tích hợp với các hàm khác: Bạn có thể kết hợp hàm OFFSET với các hàm tính toán khác như SUM, AVERAGE, COUNT, giúp thực hiện các phép toán phức tạp trên các vùng dữ liệu động mà không cần phải thay đổi công thức liên tục.

Với khả năng hỗ trợ các thao tác dữ liệu thông minh và tự động, hàm OFFSET là một trong những công cụ không thể thiếu trong bộ công cụ của bất kỳ ai làm việc với Excel.

2. Công thức hàm OFFSET trong Excel là gì?

Cú pháp của hàm OFFSET trong Excel là:

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

Giải thích các tham số trong công thức:

• Reference: Đây là vùng tham chiếu cơ sở, từ đó bạn tính toán và tạo ra vùng tham chiếu mới. Tham số này chỉ định ô hoặc dãy ô bắt đầu.
• Rows: Số dòng cần dịch chuyển lên hoặc xuống từ ô bắt đầu của vùng tham chiếu (reference). Giá trị dương sẽ dịch xuống dưới, còn giá trị âm sẽ dịch lên trên.
• Cols: Số cột cần dịch chuyển sang trái hoặc phải từ ô bắt đầu của vùng tham chiếu. Tương tự như rows, giá trị dương dịch sang phải, còn giá trị âm dịch sang trái.
• Height (Tùy chọn): Số dòng của vùng tham chiếu cần trả về. Nếu không nhập, mặc định là 1.
• Width (Tùy chọn): Số cột của vùng tham chiếu cần trả về. Giá trị này phải là số dương. Nếu không nhập, mặc định là 1.

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

3. Hướng dẫn cách sử dụng hàm OFFSET trong Excel

Ví dụ 1: Lấy giá trị ô ở vị trí cột 2, dòng 3

Giả sử bạn có một bảng dữ liệu và bạn muốn lấy giá trị ô ở vị trí cột 2, dòng 3 từ ô tham chiếu B3.

Bước 1: Trong ô cần hiển thị kết quả, bạn nhập công thức sau: 

=OFFSET(B3, 3, 2)

Giải thích:

• B3: Là ô tham chiếu ban đầu.
• 3: Là số dòng dịch chuyển xuống dưới từ ô B3.
• 2: Là số cột dịch chuyển sang phải từ ô B3.

Nhập hàm =OFFSET(B3,3,2) vào ô tham chiếu muốn hiển thị kết quả

Bước 2: Nhấn phím Enter để hiển thị kết quả tại ô bạn chọn.

Nhấn phím Enter để hiển thị kết quả

Ví dụ 2: Tính tổng cột D bằng cách kết hợp hàm OFFSET và hàm SUM

Giả sử bạn muốn tính tổng các giá trị trong cột D, bắt đầu từ ô A3, với 5 dòng dữ liệu.

Bước 1: Trong ô cần hiển thị kết quả, bạn nhập công thức:

=SUM(OFFSET(A3, 1, 3, 5, 1))

Giải thích công thức:

• SUM: Là hàm tính tổng.
• OFFSET(A3, 1, 3, 5, 1): Sử dụng hàm OFFSET để xác định phạm vi dữ liệu cần tính tổng.
  • A3: Là ô tham chiếu ban đầu.
  • 1: Dịch chuyển 1 dòng xuống dưới từ A3.
  • 3: Dịch chuyển 3 cột sang phải từ A3.
  • 5: Chọn 5 dòng dữ liệu để tính tổng.
  • 1: Chỉ định rằng bạn chỉ muốn lấy dữ liệu từ 1 cột.

Nhập hàm =SUM(OFFSET(A3,1,3,5,1)) vào ô tham chiếu muốn hiển thị kết quả

Bước 2: Nhấn phím Enter để hiển thị kết quả tổng trong ô bạn đã chọn.

Enter để trả về kết quả như hình

4. Hàm OFFSET kết hợp với các hàm khác trong Excel

Hàm OFFSET trong Excel có thể kết hợp với nhiều hàm khác để thực hiện các phép toán phức tạp và linh hoạt hơn. Dưới đây là một số ví dụ về cách kết hợp hàm OFFSET với các hàm phổ biến như SUM, AVERAGE, MAX, MIN, và VLOOKUP.

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

Để tính tổng một phạm vi dữ liệu động, bạn có thể sử dụng hàm OFFSET với hàm SUM. Ví dụ:

Bước 1: Nhập công thức sau trong ô cần hiển thị kết quả:

=SUM(B2:OFFSET(B9, -1,0))

Giải thích:

• B2: Ô bắt đầu phạm vi tính tổng.
• OFFSET(B9, -1, 0): Dịch chuyển từ ô B9 lên trên 1 dòng (do tham số -1) để mở rộng phạm vi tính tổng.

Nhập hàm =SUM(B2:OFFSET(B9, -1,0))​ vào ô muốn hiển thị kết quả để tính tổng giá trị thưởng ở cột B

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

Kết quả trả về 2050000 như hình

4.2 Dùng hàm OFFSET và các hàm AVERAGE, MAX, MIN

Hàm OFFSET có thể kết hợp với các hàm thống kê như AVERAGE, MAX, MIN để tính toán các giá trị trung bình, giá trị lớn nhất và giá trị nhỏ nhất từ một phạm vi dữ liệu động.

Bước 1: Để tính giá trị trung bình trong một dải dữ liệu động, bạn sử dụng công thức:

=AVERAGE(OFFSET(B1, COUNT(B:B)-E2+1, 0, E2, 1))

Giải thích:

• OFFSET(B1, COUNT(B:B)-E2+1, 0, E2, 1): Dịch chuyển từ ô B1 và xác định phạm vi tính trung bình dựa trên số lượng dữ liệu trong cột B.

Hãy nhập hàm =AVERAGE(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1))

Bước 2: Nhấn Enter để hiển thị kết quả giá trị trung bình.

Kết quả trả về như hình

Bước 3: Để tính số lớn nhất trong phạm vi dữ liệu, bạn sử dụng công thức:

=MAX(OFFSET(B1, COUNT(B:B)-E2+1, 0, E2, 1))

Nhập =MAX(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1)) để tính số lớn nhất trong dài

Bước 4: Nhấn Enter để hiển thị kết quả giá trị lớn nhất.

Kết quả trả về

Bước 5: Để tính số nhỏ nhất trong dải, bạn sử dụng công thức:

=MIN(OFFSET(B1, COUNT(B:B)-E2+1, 0, E2, 1))

Để tìm số nhỏ nhất trong dải, bạn nhập =MIN(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1))

Bước 6: Nhấn Enter để hiển thị kết quả giá trị nhỏ nhất.

Kết quả hiển thị

4.3 Kết hợp hàm VLOOKUP và hàm OFFSET trong Excel

Hàm VLOOKUP có thể kết hợp với hàm OFFSET để tra cứu dữ liệu trong một vùng tham chiếu động, mang lại sự linh hoạt trong việc tìm kiếm dữ liệu.

Bước 1: Nhập công thức sau vào ô tham chiếu cần hiển thị kết quả:

=VLOOKUP(B1, A5:B11, 2, FALSE)

Giải thích:

• B1: Giá trị cần tìm kiếm.
• A5:B11: Vùng tham chiếu tìm kiếm.
• 2: Cột chứa giá trị cần trả về trong phạm vi tìm kiếm.
• FALSE: Thực hiện tìm kiếm chính xác.

Nhập hàm =VLOOKUP(B1,A5:B11,2,FALSE) vào ô tham chiếu muốn hiển thị kết quả.

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

Nhấn phím Enter để hiển thị kết quả

5. Lưu ý quan trọng khi sử dụng hàm OFFSET trong Excel

Khi sử dụng hàm OFFSET trong Excel, có một số điểm quan trọng bạn cần lưu ý để tránh các lỗi và đảm bảo tính chính xác của kết quả. Dưới đây là các vấn đề cần chú ý khi làm việc với hàm OFFSET:

Khi bạn sử dụng hàm OFFSET và nhập các đối số rows hoặc cols không hợp lệ, có thể khiến phạm vi tham chiếu vượt quá giới hạn của trang tính. Trong trường hợp này, Excel sẽ trả về lỗi #REF!, thông báo rằng vùng tham chiếu không hợp lệ vì nó nằm ngoài phạm vi cho phép. Để khắc phục lỗi này, bạn cần kiểm tra lại các giá trị của rows và cols để đảm bảo rằng vùng tham chiếu không vượt quá giới hạn của trang tính.

Nếu bạn không điền các đối số height và width trong công thức hàm OFFSET, Excel sẽ mặc định rằng vùng tham chiếu mới có cùng chiều cao và chiều rộng với vùng tham chiếu ban đầu. Điều này có thể gây ra sự không chính xác trong việc tham chiếu và tính toán nếu bạn mong muốn thay đổi kích thước vùng tham chiếu. Vì vậy, nếu bạn cần thay đổi chiều cao hoặc chiều rộng của vùng tham chiếu, hãy chắc chắn rằng bạn đã nhập đúng các giá trị cho height và width.

Nếu bạn chỉ định giá trị height và width lớn hơn 1, tức là bạn muốn vùng tham chiếu mở rộng hơn so với một ô đơn, Excel sẽ yêu cầu bạn phải sử dụng công thức mảng. Nếu không sử dụng công thức mảng trong trường hợp này, Excel sẽ trả về lỗi #VALUE!. Điều này có thể xảy ra khi bạn muốn lấy dữ liệu từ nhiều ô thay vì một ô duy nhất, và cần tính toán trên một phạm vi rộng hơn.

Các giá trị của đối số height và width trong hàm OFFSET phải luôn là số không âm, tức là số lớn hơn hoặc bằng 0. Nếu bạn nhập giá trị âm cho các đối số này, Excel sẽ không thể thực hiện phép toán và trả về lỗi #VALUE!. Hãy kiểm tra và đảm bảo rằng các giá trị của height và width luôn là số không âm khi bạn sử dụng hàm OFFSET.

Sử dụng hàm OFFSET trong Excel nên lưu ý để tránh các lỗi và đảm bảo tính chính xác 

Hàm OFFSET trong Excel là một công cụ iúp bạn linh hoạt tham chiếu và tính toán các dãy ô một cách chính xác và hiệu quả. Nhờ khả năng thay đổi phạm vi dữ liệu động, kết hợp với các hàm khác như SUM, AVERAGE, MAX, và VLOOKUP, hàm OFFSET không chỉ tiết kiệm thời gian mà còn nâng cao khả năng xử lý dữ liệu trong bảng tính của bạn. Tuy nhiên, như với bất kỳ công cụ nào trong Excel, để khai thác tối đa khả năng của hàm OFFSET, bạn cần hiểu rõ các quy tắc và lưu ý khi sử dụng.

Nếu bạn muốn nắm vững hơn về các hàm Excel và các kỹ thuật tính toán phức tạp, đừng bỏ qua sách Excel của AntBook. Sách này cung cấp hướng dẫn chi tiết, các bài tập thực hành và mẹo vặt giúp bạn nâng cao kỹ năng sử dụng Excel, từ cơ bản đến nâng cao. Hãy trang bị cho mình kiến thức vững vàng để trở thành một chuyên gia Excel và tối ưu công việc của mình!