Giới thiệu về hàm OFFSET và các cách kết hợp hàm OFFSET trong Excel

Trong các hàm tham chiếu trong Excel, hàm OFFSET là một hàm cực kì lợi hại vì nó có thể tạo nên những màn kết hợp ăn ý với các hàm tham chiếu cũng như các hàm tính toán khác. Vậy làm thế nào để sử dụng hàm OFFSET hiệu quả? Hãy cùng đi tìm hiểu với Gitiho nhé.

Giới thiệu về hàm OFFSET

Công thức hàm OFFSET

Công thức hàm OFFSET đóng vai trò hàm tham chiếu trong Excel như sau:

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

Trong công thức chúng ta nhìn thấy 3 đối số reference, rows và cols là 3 đối số bắt buộc người dùng phải nhập. 2 đối số còn lại, height và width, là 2 đối số tùy chọn, nghĩa là người dùng có thể nhập hoặc bỏ qua.

Các đối số bắt buộc trong công thức hàm OFFSET

• reference: Là một ô hoặc một dải ô liền kề mà sau đó nó có thể bị thêm/bớt. Nói cách khác, đối số này xác định điểm làm gốc của công thức hàm OFFSET.
• rows: Là số hàng di chuyển lên hoặc xuống từ điểm làm gốc (trong đối số reference). Nếu điền số dương vào trong đối số này, công thức sẽ di chuyển xuống phía dưới tham chiếu. Ngược lại, trong trường hợp điền một số âm vào đối số này thì công thức sẽ di chuyển lên phía trên tham chiếu.
• cols: Là số cột bạn muốn công thức di chuyển từ điểm làm gốc (trong đối số reference). Tương tự như với đối số rows, cols có thể là số dương để công thức di chuyển sang bên phải tham chiếu, hoặc số âm để công thức di chuyển sang bên trái tham chiếu.

Các đối số tùy chọn trong công thức hàm OFFSET

• height: Là chiều cao tính bằng số hàng mà bạn muốn hàm tham chiếu trong Excel trả về kết quả
• width: Là chiều rộng, tính bằng số cột, mà bạn muốn tham chiếu trả về.

Lưu ý: Cả hai đối số height và width luôn phải là số dương. Nếu một trong hai bị bỏ qua, thì Excel sẽ tự động dùng height hoặc width của tham chiếu gốc.

Để áp dụng được công thức hàm OFFSET được giới thiệu phía trên, chúng ta hãy cùng tìm hiểu một ví dụ cụ thể nhé.

Hình dưới đây miêu tả cách OFFSET Excel trả về kết quả từ công thức được nhập.

Hàm OFFSET được nhập theo công thức:

=OFFSET(A1,3,1)

Công thức OFFSET Excel này được hiểu là lấy ô tham chiếu bắt đầu từ điểm gốc A1, dịch chuyển ô tham chiếu 3 hàng xuống dưới, sau đó dịch ô tham chiếu 1 cột sang phải. Như vậy, ô tham chiếu của chúng ta là ô B4.

Cùng tìm hiểu một ví dụ khác về hàm OFFSET nhé.

Về bản chất, chúng ta vẫn sử dụng công thức OFFSET trong Excel như ở phần trên. Tuy nhiên, tại đối số rows, thay vì điền số 3, chúng ta điền ô E1. Excel sẽ tự động hiểu chúng ta nhập dữ liệu trong ô E1 vào đối số rows trong công thức hàm OFFSET. Vì ô E1 có chứa số 3 nên có thể nói, chúng ta đã gián tiếp nhập số 3 vào đối số rows.

Xem thêm: HƯỚNG DẪN VỀ LỆNH LIMIT VÀ OFFSET TRONG MySQL

Những điều cần ghi nhớ về hàm OFFSET

• Hàm OFFSET trong Excel không di chuyển bất kỳ ô hoặc dải nào, mà nó chỉ trả về một tham chiếu trong phạm vi dữ liệu.
• Đối với công thức hàm OFFSET trả về kết quả là một dải ô, các đối số rows và cols luôn luôn đề cập đến ô phía trên bên trái.
• Đối số reference phải là một ô hoặc một dải ô liền kề, nếu không Excel sẽ trả về lỗi #VALUE! khi chạy hàm OFFSET.
• Nếu đối số rows và cols xác định một ô tham chiếu ở rìa của trang tính Excel, thì công thức hàm tham chiếu trong Excel sẽ xuất hiện lỗi #REF!.
• Hàm OFFSET có thể được kết hợp lồng trong bất kỳ hàm Excel nào chấp nhận một tham chiếu ô hoặc dải trong các đối số của nó.

Để minh họa cho các lỗi có thể xảy ra trong quá trình chúng ta sử dụng hàm OFFSET làm hàm tham chiếu trong Excel, chúng mình có một ví dụ như sau: Nếu bạn sử dụng công thức =OFFSET(A1,3,1,1,3) thì sẽ thấy thông báo lỗi #VALUE!. Lí do là vì Excel không thể trả một dải chứa 1 hàng 3 cột về 1 ô duy nhất.

Tuy nhiên, công thức hàm trên sẽ hoạt động mượt mà khi bạn lồng công thức trên vào trong hàm SUM. Sử dụng công thức:

=SUM(OFFSET(A1,3,1,1,3))

Như vậy, chúng ta thu được kêt quả chính là tổng dải ô tham chiếu liền kề được xác định bởi hàm OFFSET. 

Các trường hợp sử dụng hàm OFFSET

Tạo các dải động bằng hàm OFFSET

Các tham chiếu trực tiếp ví dụ như A1:B4 là tham chiếu tĩnh, nghĩa là nó luôn tham chiếu đến phạm vi cố định. Do đó, bạn không thể thay đổi phạm vi tham chiếu. 

Nếu bạn cần thường xuyên cập nhật dữ liệu trên bảng Excel nhưng không muốn làm ảnh hưởng đến kết quả hàm tham chiếu trong Excel, hàm OFFSET là sự lựa chọn tuyệt vời. Nó cho phép bạn tạo các dải động, nên khi bạn thay đổi dữ liệu bảng tính hay thêm các hàng, cột mới vào bảng, bạn vẫn sẽ tìm được dữ liệu mình cần.

Lấy dải từ ô gốc bằng hàm OFFSET trong Excel

Với các bảng Excel có nhiều dữ liệu, bạn có thể quên mất địa chỉ thực của cả dải ô cần tìm kiếm thông tin. Tất cả những gì bạn nhớ là phạm vi tìm kiếm bắt đầu từ một số ô cụ thể. Trong tình huống này, hàm OFFSET sẽ cứu bạn khỏi việc mỏi mắt tra cứu dữ liệu đấy.

Xem thêm: Hướng dẫn cách dùng hàm OFFSET để tạo danh sách động trong Excel

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

Sử dụng hàm OFFSET lồng trong hàm SUM

Ở các phần trên, chúng ta đã nhắc đến một ví dụ đơn giản về cách sử dụng hàm OFFSET lồng trong hàm SUM rồi. Vậy cụ thể hơn cách kết hợp này làm được gì? Cùng nhau tìm hiểu nhé.

Sử dụng công thức hàm SUM kết hợp hàm OFFSET thay đổi

Công thức hàm SUM kết hợp hàm OFFSET thay đổi có chức năng tính tổng các ô tham chiếu trong OFFSET Excel ngay cả khi bảng tính được thay đổi và cập nhật dữ liệu liên tục. Như vậy, chúng ta không cần phải thay thế hàm SUM thủ công mỗi lần thêm hàng hoặc cột vào trang tính nữa.

Giả sử, chúng ta có bảng tính Excel phía bên dưới được cập nhật hàng tháng. Điều này nghĩa là bảng tính của chúng ta thay đổi liên tục. Để tính tổng tiền thưởng qua các tháng, chúng ta cần một hàm SUM thích ứng với những thay đổi trong bảng. Thay vì dùng hàm SUM cơ bản cần cập nhật hàm thủ công, công thức hàm SUM kết hợp hàm OFFSET thuận tiện hơn rất nhiều.

 

Bạn nhập trực tiếp ô đầu tiên của dải cần tính tổng vào đối số trong công thức hàm SUM, sau đó việc cần làm chỉ là đưa ra các tham số còn lại cho hàm OFFSET. Hàm tham chiếu trong Excel sẽ tự động tham chiếu cho đến ô cuối cùng của dải. Hãy nhập các tham số sau đây vào đối số trong công thức hàm OFFSET:

• reference: Ô B9 chứa giá trị tổng số liệu.
• rows: Ô phía trên ô chứa giá trị tổng và nằm bên phải, bắt buộc phải là số âm -1.
• Cols: Nhập 0 vì bạn không muốn thay đổi cột.

Từ công thức mẫu hàm SUM kết hợp hàm OFFSET:

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

Áp dụng vào trong ví dụ, ta sẽ có công thức hàm OFFSET như sau:

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

Và chúng ta thu được kết quả mĩ mãn.

Sử dụng hàm OFFSET tổng hợp N dòng cuối của một danh sách

Tiếp tục với ví dụ ở phần trên, giả sử thay vì tính tổng tiền thưởng tất cả các tháng, bạn chỉ muốn chạy một công thức tự động tính tổng số tiền thưởng cho N tháng gần nhất. 

Để làm được điều này, chúng ta sẽ sử dụng hàm OFFSET kết hợp với các hàm SUM và hàm COUNT/ COUNTA với công thức như sau:

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

hoặc

=SUM(OFFSET(B1,COUNTA(B:B)-E2,0,E2,1)))

Trong công thức hàm OFFSET:

• reference: Nhập tiêu đề của cột chứa các giá trị cần tính tổng, trong ví dụ này là ô B1.
• rows: Sử dụng thêm hàm COUNT hoặc COUNTA để tính số hàng.
• cols: Nhập số cột điều chỉnh là 0.
• height: Nhập ô E1 chứa số hàng được tổng hợp
• width: Nhập 1 tương ứng với số cột.

Hàm COUNT trả về số ô trong cột B có chứa giá trị số, do đó bạn trừ đi N tháng cuối cùng (giá trị ở trong ô E1) và cộng thêm 1.

Hàm COUNTA tính tất cả các ô không chứa giá trị rỗng, và hàng tiêu đề chứa giá trị văn bản là một ô thêm mới mà công thức cần đến. Vậy nên khi sử dụng hàm này, bạn không cần phải thêm 1. Lưu ý rằng công thức này sẽ chỉ hoạt động chính xác trên một cấu trúc bảng tương tự. Do đó, đối với các cấu trúc bảng khác nhau, bạn có thể cần thực hiện một số điều chỉnh trong công thức OFFSET Excel / COUNTA để kết hợp với các hàm tính toán và hàm tham chiếu trong Excel.

Xem thêm: CÁCH SUM NHIỀU SHEET TRONG EXCEL- TÍNH TỔNG TỪ NHIỀU SHEET TRONG 1 VÙNG

Sử dụng hàm OFFSET lồng trong hàm AVERAGE, MAX, MIN

Tương tự với cách tính tiền thưởng cho N tháng trong phần trên, chúng ta có thể tính giá trị trung bình cho N ngày, N tuần hoặc N năm cuối cùng. Bên cạnh đó, chúng ta cũng có thể tìm các giá trị lớn nhất hoặc nhỏ nhất trong bảng. Bằng cách sử dụng hàm OFFSET, sự khác biệt duy nhất giữa các công thức để tính các yêu cầu trên chỉ là tên của hàm đầu tiên:

• Công thức hàm OFFSET lồng trong hàm AVERAGE để tính giá trị trung bình: =AVERAGE(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1))
• Công thức hàm OFFSET lồng trong hàm MAX để tìm giá trị lớn nhất trong bảng: =MAX(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1))
• Công thức hàm OFFSET lồng trong hàm MAX để tìm giá trị nhỏ nhất trong bảng: =MIN(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1))

Công thức này không cần phải được cập nhật thủ công nếu bạn thêm các thay đổi vào bảng tính gốc. Bạn sẽ không phải lo về việc Excel trả sai kết quả hay trả lỗi mỗi lần cập nhật dữ liệu nữa, bởi công thức hàm OFFSET luôn luôn tham chiếu đến ô cuối cùng trong cột.

Sử dụng hàm OFFSET kết hợp hàm COUNTA để tạo dải động

Hàm OFFSET kết hợp hàm COUNTA có thể giúp bạn tạo một dải động vô cùng hữu ích trong các trường hợp bạn muốn tạo danh sách tự động cập nhật. Việc bạn cần làm là đặt tên cho dải, sau đó tạo một danh sách theo mục để dễ dàng thao tác.

Đầu tiên, chúng ta cần xác định và đặt tên cho dải. Chúng ta vào tab Formula > Defined Names > Define Name và nhập công thức hàm OFFSET kết hợp hàm COUNTA như sau:

=OFFSET(Sheet_Name!$A$1,0,0,COUNTA(Sheet_Name!$A:$A),1)

Trong công thức hàm OFFSET:

• reference: Được biểu diễn dưới dạng tên trang tính và theo sau là dấu chấm than kèm theo địa chỉ ô chứa mục đầu tiên có mặt trong trong dải (-đã được đặt tên). Bạn cần sử dụng các tham chiếu tuyệt đối ($) cho tên trang tính, ví dụ Sheet1!$A$1.
• Các tham số rows và cols đều là 0, vì không có cột hoặc hàng nào được thêm vào.
• height: Đây là điểm mấu chốt trong công thức. Do chúng ta sử dụng hàm COUNTA để tính toán số ô không rỗng trong cột có chứa các mục của dải được đặt tên. Lưu ý điều này, bạn nên chỉ định tên bảng bên cạnh tên cột, ví dụ như COUNTA(Sheet_Name!$A:$A).
• width: Là 1 cột.

Sau khi đã đặt tên cho dải, chúng ta sử dụng Excel Data Validation để tạo danh sách theo mục, đảm bảo Excel luôn tự động cập nhật ngay khi bạn thêm hoặc xoá các mục từ Danh sách nguồn (bảng tính gốc). Bạn hãy vào tab Data > Data Tools > Data Validation hoặc nhấn mũi tên bên cạnh mục Data Validation > Data Validation…
 

 Hộp thoại Data Validation hiện lên. Bạn chọn List ở phần Allow và điền dải ô cần tham chiếu vào phần Source, tại ví dụ là dải A2:A8 đặt trong tham chiếu tuyệt đối ($).

Vậy là chúng ta đã có một dải động hiển thị dưới dạng danh sách theo mục như dưới đây rồi.

Sử dụng hàm OFFSET thay thế các hàm LOOKUP trong Excel

Sử dụng hàm OFFSET thay hàm VLOOKUP để tra cứu bên trái trong Excel

Tại sao chúng ta lại sử dụng hàm OFFSET thay hàm VLOOKUP? Mình sẽ giúp bạn giải thích lý do nhé.

Hàm VLOOKUP trong Excel chỉ có khả năng trả lại một giá trị ở bên phải cột tra cứu, đồng nghĩa với việc một trong những hạn chế lớn nhất của hàm VLOOKUP là không có khả năng dò được giá trị bên trái của nó.

Chúng ta tiếp tục áp dụng ví dụ về bảng tiền thưởng theo tháng ở các phần trên. Nếu bạn chỉ cần tìm kiếm tiền thưởng của một tháng nhất định, công thức hàm VLOOKUP cơ bản không hề gặp khó khăn gì:

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

Tuy nhiên, một khi bạn tráo đổi các cột trong bảng với nhau, hàm VLOOKUP sẽ gặp lỗi #N/A như thế này:

Đây là lúc bạn cần tìm đến một hàm tham chiếu trong Excel khác linh hoạt hơn như hàm INDEX kết hợp hàm MATCH. Tuy nhiên, giải pháp chúng mình đề xuất chính là sử dụng hàm OFFSET kết hợp hàm MATCH và hàm ROWS với công thức như sau:

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

Áp dụng công thức vào ví dụ của chúng ta, phạm vi bảng tra cứu là A5: B11, giá trị cần tìm nằm trong ô B1. Ta có công thức cụ thể của hàm OFFSET như sau:

=OFFSET(A5:B11,MATCH(B1,OFFSET(A5:B11,0,1,ROWS(A5:B11),1),0)-1,0,1,1)

Nhìn qua công thức trên, chúng ta sẽ nghĩ nó thật phức tạp. Mặc dù vậy, chính sự phức tạp ấy sẽ đem lại hiệu quả tuyệt vời cho công việc tra cứu của chúng ta đấy.

Xem thêm: 

Cách dùng hàm VLOOKUP nhiều giá trị với một hoặc nhiều tiêu chí

Hướng dẫn cách dùng hàm Match tìm kiếm nhiều điều kiện

Sử dụng hàm OFFSET thay hàm HLOOKUP tra cứu bên trên trong Excel

Tương tự như hàm VLOOKUP không thể dò tìm giá trị bên trái, hàm HLOOKUP không thể dò giá trị bên trên hàng chứa giá trị tham chiếu trong phạm vi dữ liệu để xuất ra một giá trị.

Do vậy, hàng chứa giá trị cần tìm nằm ở phía trên hàng chứa tên giá trị cần tìm, bạn hãy sử dụng công thức hàm OFFSET kết hợp hàm MATCH và hàm ROWS tương tự như phần trên. Tuy nhiên, đối với trường hợp này, bạn cần thêm hàm COLUMNS để công thức dò tìm như sau:

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

Giả sử rằng bảng tra cứu là E4:K5 và giá trị tra cứu là trong ô E1, công thức đi như sau:

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

Một lần nữa, hàm OFFSET lại kết hợp mượt mà với các hàm tham chiếu khác để tạo nên công thức hiệu quả khắc phục nhược điểm của hàm LOOKUP.

Sử dụng hàm OFFSET thay các hàm LOOKUP tra cứu hai chiều trong Excel

Tra cứu hai chiều trong Excel nghĩa là chúng ta tìm kiếm dữ liệu tại vị trí giao điểm các hàng và cột. Bạn có thể sử dụng công thức hàm OFFSET với mảng tra cứu 2 chiều như sau:

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

Công thức trên quá dài dòng và khó hiểu nếu chúng ta không có một ví dụ thực tế. Vì vậy, hãy cùng áp dụng kiến thức vào trường hợp dưới đây: bảng tiền thưởng của các nhân viên trong một bộ phận.

Để áp dụng công thức trên vào ví dụ này, chúng ta xác định các yếu tố sau:

• Bảng tra cứu (lookup table) là A5:G9
• Giá trị phù hợp trên các hàng là B2
• Giá trị phù hợp trên các cột là B1

Như vậy, chúng ta có viết được công thức tra cứu hai chiều sau đây:

=OFFSET(A5:G9,MATCH(B2,OFFSET(A5:G9,0,0,ROWS(A5:G9),1),0)-1,MATCH(B1,OFFSET(A5:G9,0,0,1,COLUMNS(A5:G9)),0)-1)

Lưu ý: Đây là một công thức mảng, do đó chúng ta phải ấn tổ hợp phím Ctrl + Shift + Enter để Excel trả về kết quả chính xác.

Xem thêm: LÀM THẾ NÀO KHI FILE EXCEL NẶNG BẤT THƯỜNG

Những nhược điểm của hàm OFFSET và cách khắc phục

Nhược điểm của hàm OFFSET

Bên cạnh những lợi thế tuyệt vời của mình, hàm OFFSET trong Excel cũng có một số nhược điểm mà chúng ta cần lưu ý khi sử dụng.

Hàm OFFSET có thể khiến Excel chạy chậm

Hàm OFFSET là một hàm tham chiếu trong Excel luôn luôn “khát dữ liệu”, nghĩa là chỉ cần một thay đổi nhỏ tại bất kì ô nào trong phạm vi tham chiếu, các công thức hàm tham chiếu của bạn sẽ tự động chạy lại để cập nhật kết quả. Điều này có thể khiến Excel tốn thêm thời gian nếu bạn có quá nhiều công thức hàm OFFSET trong trang tính của mình.

Hàm OFFSET có thể gây khó khăn cho việc sửa lỗi hàm

Công thức hàm OFFSET rất khó để chúng ta kiểm tra lại. Lí do chính đến từ việc các tham chiếu được trả lại bởi hàm OFFSET là động. Đối với các công thức phức tạp, nguy cơ cao là chúng ta sẽ tốn rất nhiều thời gian để tìm và sửa lỗi đấy.

Các giải pháp khắc phục nhược điểm của hàm OFFSET trong Excel

Sử dụng công cụ bảng trong Excel

Bạn chỉ cần nhập một công thức hàm OFFSET vào một ô trong bảng, sau đó sao chép ô xuống các ô phía dưới. Vậy là bạn đã có thể tạo ra một bảng Excel với một cột công thức giống nhau nhưng có sự thay đổi dữ liệu phù hợp cho mỗi hàng.

Tuyệt vời hơn, bất kỳ công thức nào tham chiếu đến dữ liệu của bảng mà có điều chỉnh tự động sẽ bao gồm tất cả các hàng mới nào bạn thêm vào bảng hoặc loại trừ các hàng đã được xóa. Về mặt kỹ thuật, các công thức như vậy hoạt động trên các cột hoặc hàng của bảng, gọi là những dải động.

Mỗi bảng trong trang tính Excel có một tên duy nhất mặc định là Table1, Table2,… nhưng bạn hoàn toàn có thể đổi tên bảng của mình bằng cách vào tab Design> Properties group> Table Name.

Hình ảnh dưới đây thể hiện công thức SUM có tham chiếu đến cột Thưởng trong Bảng 3. Hãy chú ý rằng công thức này có bao gồm tên cột của bảng thay vì một dải ô.

Sử dụng hàm INDEX trong Excel

Mặc dù chức năng không giống hệt hàm OFFSET, nhưng hàm INDEX cũng có thể được dùng làm hàm tham chiếu trong Excel đôi với các dải động. Điểm mạnh của hàm INDEX so với hàm OFFSET là hàm INDEX không tự thay đổi, vì vậy nó sẽ không làm chậm Excel của bạn.

Sử dụng hàm INDIRECT trong Excel

Hàm INDIRECT giúp chúng ta tạo tham chiếu cho dải động từ nhiều nguồn như các ô giá trị, giá trị và văn bản của ô, và các dải ô được đặt tên. Ngoài ra, nó cũng có thể tự động tham chiếu một bảng tính hoặc một trang tính Excel. 

Xem thêm: Hướng dẫn giải bài tập Excel với hàm INDEX và hàm MATCH chi tiết

Tổng kết

Có thể nói hàm OFFSET là hàm tham chiếu trong Excel lợi hại bậc nhất khi kết hợp với các hàm khác. Vì vậy, để sử dụng hiệu quả hàm OFFSET, các bạn cần nắm vững công thức của các hàm tham chiếu và hàm tính toán trong Excel. Để làm được điều này, hãy đọc thêm các bài viết về hàm Excel trên blog Gitiho và nhanh tay đăng kí khóa học Tuyệt đỉnh Excel cùng chúng mình nhé.

Chúc các bạn áp dụng kiến thức bài viết thành công!