Cách sử dụng hàm Offset trong Excel để tham chiếu dữ liệu

Bạn đang không biết sử dụng hàm OFFSET trong Excel như thế nào? Bạn không biết ứng dụng hàm OFFSET sao cho hiệu quả nhất. Hãy cùng Box.edu tìm hiểu bài viết dưới đây để tìm câu trả lời cho bạn nhé!

Xem thêm: Tổng hợp kiến thức về hàm ROW trong Excel chi tiết nhất

Xem thêm: Cách sử dụng hàm Text trong Excel – Đơn giản, chuyên nghiệp

Mục lục bài viết

Hàm Offset trong Excel là gì? Ứng dụng

Hàm Offset trong Excel là gì?

Hàm Offset trong Excel được sử dụng để tham chiếu và tính toán các dữ liệu trong trang tính Excel. Phép tính này được áp dụng trên một ô hoặc 1 dãy dữ liệu trong bảng tính. Trong học Excel online thì hàm Offset sẽ dựa vào một vùng dữ liệu tham chiếu có sẵn trước đó để thực hiện tính toán. 

Ứng dụng hàm Offset trong Excel

  • Hàm được sử dụng trong phạm vi một ô hoặc một dãy dữ liệu trong bảng tính để thực hiện tính toán dữ liệu
  • Hàm Offset được kết hợp với các hàm thông dụng cơ bản khác để thực hiện tính toán chuẩn xác và hỗ trợ tối đa công việc của bạn
ham-Offset-trong-Excel
Ứng dụng hàm Offset trong Excel

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

Cú pháp 

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

Trong đó:

  • Reference, Rows, Cols: Đối số bắt buộc
  • Height, Width: Đối số tuỳ chọn

Ví dụ minh hoạ:

Cach-su-dung-ham-Offset-trong-Excel
Cách sử dụng hàm Offset trong Excel

Để hiểu chi tiết hơn về các đối số, tham chiếu đến các ô dữ liệu trong công thức. Ta cần hiểu chi tiết hơn về các đối số trên. Cụ thể:

Đối số bắt buộc:

  • Reference: Phạm vi một ô hoặc một vùng dải ô liền kề nhau. Bạn có thể thực hiện thao tác thêm/bớt. Hoặc có thể nói chi tiết, dễ hiểu hơn đó là điểm khởi đầu/làm mốc
  • Rows: Số hàng di chuyển từ điểm làm gốc. Số hàng di chuyển lên hoặc xuống tuỳ thuộc vào số hàng. Nếu các hàng là một số dương => Công thức di chuyển xuống phía dưới của tham chiếu. Nếu các hàng là một số âm => Công thức di chuyển lên phía trên tham chiếu.
  • Cols – Số cột bạn muốn công thức di chuyển. Trong đó bạn lấy gốc là từ điểm xuất phát. Số hàng di chuyển sang trái hoặc phải tuỳ thuộc vào số hàng. Nếu cols là số dương => Công thức di chuyển sang bên phải tham chiếu. Nếu Cols là số âm => Công thức di chuyển sang bên trái tham chiếu

Đối số bắt buộc:

  • Height: Tham chiếu bạn muốn trả về theo chiều cao được tính bằng số hàng 
  • Width: Tham chiếu bạn muốn trả về theo chiều rộng tính bằng số cột

Trong đó, cả hai đối số Height và Width sẽ luôn luôn là số dương. Nếu một trong hai đối số bị bỏ qua thì Height hoặc Width sẽ được tham chiếu gốc sử dụng trong công thức. 

Lưu ý:

  • Hàm OFFSET trong Excel sẽ không hỗ trợ người dùng thực sự di chuyển bất lỳ ô hoặc dải dữ liệu nào. Hàm chỉ làm là thực hiện tìm dữ liệu và trả về kết quả là một tham chiếu
  • Đối số Height và Width phải là số dương
  • Trường hợp bỏ qua các đối số HeightWidth thì tham chiếu trả về mặc định. Trong đó có cùng chiều cao và độ rộng với vùng tham chiếu.
  • Trường hợp công thức Offset trả về một dài ô trong đó các đối số rows và cols sẽ luôn đề cập đến các ô ở ngay phía bên trái.
  • Các đối số thực hiện tham chiếu bao gồm một ô hoặc một dải ô liền kề. Nếu trong công thức của bạn hiển thị kết quả lỗi #VALUE!
  • Với các hàm Rows được trực tiếp chỉ đinh hoặc các cột trong bảng tính thực hiện di chuyển một tham chiếu đến rìa của bảng tính. Trong đó công thức OFFSET sẽ trả về kết quả lỗi #REF!
  • Hàm Offset sẽ thực hiện sử dụng trong bất kỳ các hàm Excel cơ bản khác. Sự kết hợp các hàm này sẽ chấp nhận tham chiếu ô hoặc một dải ô trong các đối số của nó. 

Hàm Offset trong Excel rất hữu ích và được sử dụng rộng rãi trong Excel. Trong đó:

Hàm được dùng để tạo các dải ô: Khi các tham chiếu trong vùng dữ liệu là tĩnh thì có nghĩa là hàm đang tham chiếu đến một dải ô trên bảng tính nhất định. 

Tuy nhiên với một số công việc sẽ thực hiện áp dụng hàm trên một dải động. Với trường hợp đặc biệt khi bạn thực hiện làm việc với các thay đổi dữ liệu. 

Ví dụ như bạn có một bảng tính và thực hiện thêm mới cột hoặc hàng vào bảng tính đó hàng tuần. 

Hoặc hàm Offset thực hiện lấy dải từ ô gốc. Tuy nhiên bạn có thể thực hiện mà không biết địa chỉ của dải. Mặc dù trên bảng tính bạn đã biết nó bắt đầu từ một số ô. Trong trường hợp này bạn nên sử dụng hàm Offset để đạt hiệu quả và tính toán chính xác nhất. 

Cách sử dụng 

Hàm OFFSET

Sử dụng hàm Offset để lấy giá trị ô trong bảng tính. Vị trí ô nằm ở cột 2 dòng 3. 

Bước 1: Chọn ô trống trong bảng tính và nhập công thức hàm Offset theo cú pháp sau:

=OFFSET(B3,3,2) 

Cach-su-dung-ham-Offset-trong-Excel-1
Chọn ô trống trong bảng tính và nhập công thức hàm Offset theo cú pháp

Bước 2: Kết quả hiển thị như sau:

Cach-su-dung-ham-Offset-trong-Excel-2
Kết quả hiển thị

Hàm OFFSET và hàm SUM

Sự kết hợp giữa hàm Offset và Hàm Sum là dễ nhất. Hàm Sum sẽ thực hiện tự động chọn tất cả các hàm được thêm mới vào bảng dữ liệu. 

Giả sử bạn đã có bảng dữ liệu như sau:

Mỗi tháng sẽ có một dòng mới được thêm vào. Dòng được thêm vào vị trí ngay phía trên hàm SUM. Bạn muốn dòng được thêm vào này sẽ được tính bao gồm trong tổng số. 

Có hai lựa chọn dành cho bạn. Có thể thực hiện cập nhập trong dải công thức hàm SUM với mỗi lần thay đổi thủ công. Hoặc sử dụng hàm Offset để thực hiện điều này. 

Trong ô tính đầu tiện của bảng dữ liệu bạn có thể thực hiện tính tổng tham chiếu trực tiếp trong công thức hàm SUM. Tiếp tục đưa đến các tham số cho hàm Offset. Sau đó các hàm sẽ thực hiện tham chiếu đến ô cuối cùng của dải. 

Trong đó:

  • Reference: Ô dữ liệu chứa tổng số
  • Rows: Ô nằm ở phía trên ô chứa tổng số và nằm bên phải. Trong đó bắt buộc 1 số âm: -1
  • Cols = 0: Không thay đổi cột

Tổng hợp lại ta có mẫu công thức kết hợp giữa hàm Offset và hàm Sum như sau:

= SUM (first cell: (OFFSET (cell with total, -1,0)

Rút gọn công thức:

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

Kết quả hiển thị như sau:

Cach-su-dung-ham-Offset-trong-Excel-3
Hàm Sum sẽ thực hiện tự động chọn tất cả các hàm được thêm mới vào bảng dữ liệu

Sử dụng hàm OFFSET với AVERAGE, MAX, MIN

Sử dụng hàm Offset kết hợp với các hàm AVERAGE, MAX, MIN để thực hiện tính tiền thưởng trong tháng qua dựa theo bảng dữ liệu dưới đây. Bạn có thể thực hiện tính giá trị trung bình cho ngày, tuần hoặc năm. Sau đó tìm giá trị tối đa hoặc tối thiểu.

Sự khác biệt duy nhất giữa các công thức trong hàm là tên của hàm đầu tiên kết hợp cùng. Công thức cụ thể như sau:

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

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

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

Cach-su-dung-ham-Offset-trong-Excel-4
Sử dụng hàm OFFSET với AVERAGE, MAX, MIN

Hàm sẽ tự động cập nhập công thức mỗi khi bảng dữ liệu gốc của bạn được cập nhập và thêm mới. Banh không cần phải quan tâm trong đó có bao nhiêu dòng mới đúng. Công thức hàm Offset sẽ thực hiện tham chiếu đến các ô cuối cùng. Thực hiện quy định giới hạn nhỏ nhất và lớn nhất trong cột dữ liệu hiển thị. 

Hàm OFFSET & VLOOKUP

Hàm VLOOKUP trong Excel không thể dò giá trị bên trái, chiều ngang. Còn hàm HLOOKUP thì không thể dò được giá trị bên trên để xuất ra giá trị. 

Vì thế nếu bạn cần quét một hàng phía trên phù hợp với giá trị thì sử dụng công thức OFFSET / MATCH / ROWS. Tuy nhiên bạn cần phải tăng cường hàm COLUMNS theo cú pháp sau:

=OFFSET(lookup table, 0, MATCH(lookup value, OFFSET(lookup table, ROWS(lookup table) -1, 0, 1, COLUMNS(lookup table)), 0) -1, 1, 1)

Áp dụng cú pháp hàm vào ví dụ minh hoạ dưới đây để tra cứu dữ liệu ta có công thức sau:

= OFFSET (E4: I5, 0, MATCH (E1, OFFSET (E4: I5, ROWS (E4: I5) -1, 0, 1, COLUMNS (E4: I5)), 0) -1, 1, 1)

Cach-su-dung-ham-Offset-trong-Excel-5
Áp dụng công thức

Một số hạn chế khi sử dụng hàm Offset trong Excel 

  • Hàm Offset là một hàm có tính chất khát dữ liệu. Với bất kỳ sự thay đổi dữ liệu gốc nào sẽ được hàm Offset tính lại. Điều này làm cho Excel luôn luôn bật rộn. Nếu chỉ có một vài hàm tính đơn lẻ như vật thì sẽ không là vấn đề gì với Excel. Bộ xử lý của Excel vẫn có thể giải quyết đơn giản. Tuy nhiên nếu bảng tính có nhiều công thức tính như vậy thì Excel sẽ phải tốn nhiều thời gian và bộ nhớ để xử lý khối lượng dữ liệu, tính toán này. 
  • Đối với công thức OFFSET sẽ rất khó để bạn xem và kiểm tra lại. Bởi các tham chiếu sẽ được trả lại bởi hàm Offset tự động. Đặc biệt các công thức lớn sẽ là các hàm lồng nhau, khó gỡ lỗi khi xảy ra.
  • Hàm Offset và hàm Index được sử dụng để tạo các tham chiếu cho dải dữ liệu rộng. Mặc dù không giống y như nhau nhưng hàm Index sẽ không tự động thay đổi. Điều này làm cho Excel của bạn bị hoạt động chậm lại. Để tránh điều này xảy ra bạn cần lưu ý.
  • Khi sử dụng kết hợp với hàm Indirect bạn cần tham chiếu thêm cho dải động. Đối với nhiều nguồn ô giá trị và văn abrn của từng ô bạn cần đặt tên các dải ô đó. Điều này cũng có thể tự động tham chiếu trong một bảng tính hoặc trên một trang tính Excel. 
  • Nhập một công thức cho một bảng Excel bạn có thể tạo ra một cột được tính tự động. Công thức sẽ được sao chép cho tất cả các ô khác trong cùng một cột đó. Và bạn cần điều chỉnh cho mỗi hàng trong bảng tính
  • Bất kỳ công thức nào thực hiện tham chiếu dữ liệu của bảng có điều chỉnh tự động sẽ bao gốm tất cả các hàng mới. Bạn cần thêm vào bảng và thực hiện loại trừ các hàng đã được xoá trước đó. Tuy nhiên về mặt kỹ thuật với các công thức như vậy sẽ hoạt động trên các hàng hoặc cột trong bảng tính. Đây được gọi là dải động. Với một bảng tính sẽ có một tên duy nhất. Bạn có thể đổi tên bảng của mình trong Tab Table Name. Trong bảng tính công thức này sẽ bao gồm cột của bảng thay vì cả một dải ô trong trang tính.

Tổng kết 

Hy vọng những kiến thức vừa chia sẻ trong bài viết đã giúp bạn hiểu chi tiết về hàm Offset trong Excel. Đừng bỏ qua những lưu ý, hạn chế được chia sẻ chi tiết phía trên để giúp bạn tránh những lỗi hàm và ứng dụng hàm hiệu quả nhé! Ngoài ra còn rất nhiều kho tàng kiến thức về các lĩnh vực khác trong cuộc sống đang chờ bạn khám phá. Chúc bạn thành công!

5/5 - (5 bình chọn)
Xin chào các bạn! Mình là Hà Nguyên Phương Hiện đang là tác giả của Box.edu.vn website nổi tiếng chuyên review, đánh giá, giới thiệu các khóa học hay nhất hiện nay.