Hàm OFFSET Và Các Kết Hợp Hàm Của Nó Trong Excel, Công Thức Ví Dụ

Trong bài viết này, học excel nâng cao online sẽ hướng dẫn về hàm OFFSET.

Hàm OFFSET – cú pháp và sử dụng cơ bản:

Cú pháp của hàm OFFSET như sau:

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

Trong đó, 3 đối số đầu tiên là bắt buộc còn 2 cái sau thì không. Tất cả các đối số có thể là các tham chiếu đến các ô khác hoặc là các kết quả được trả về bởi các công thức Excel khác.

Những đối số bắt buộc:

• Reference– một ô hoặc một dải ô liền kề mà sau đó nó có thể bị thêm/bớt (Có thể nói đó là điểm khởi đầu/làm mốc)
• Rows – Số hàng di chuyển từ điểm làm gốc, lên hoặc xuống. Nếu các hàng là một số dương, nghĩa là công thức di chuyển xuống phía dưới tham chiếu, trong trường hợp một số âm thì 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, lấy gốc là từ điểm xuất phát. Cũng như rows, cols có thể là số dương (ở bên phải tham chiếu) hoặc số âm (ở bên trái tham chiếu).

Những đối số tùy chọn:

• height – chiều cao, tính bằng số hàng, mà bạn muốn tham chiếu trả về
• Width – chiều rộng, tính bằng số cột, mà bạn muốn tham chiếu trả về.

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ì height hoặc width của tham chiếu gốc được sử dụng.

Và bây giờ, tôi có một ví dụ đơn giản về công thức OFFSET nhằm minh hoạ lý thuyết trên:

Ví dụ công thức OFFSET:

Công thức OFFSET sau trả về một tham chiếu ô dựa trên một điểm làm mốc, các hàng và cột mà bạn chỉ định:

= OFFSET (A1,3,1)

Công thức  lấy ô A1 làm điểm làm mốc (tham chiếu), sau đó di chuyển 3 hàng xuống (đối số rows) và 1 cột bên trái (đối số cols). Kết quả là, công thức OFFSET này trả về giá trị trong ô B4.

Hình ảnh ở bên trái cho thấy cách thức hàm hoạt động và ở bên phải thể hiện cách bạn có thể sử dụng công thức OFFSET trên dữ liệu thực tế. Sự khác biệt duy nhất giữa hai công thức là công thức thứ hai (bên phải) bao gồm một tham chiếu ô (E1) trong đối số hàng (rows). Nhưng nếu ô E1 có chứa số 3 và chính xác cùng một số xuất hiện trong các đối số hàng của công thức đầu tiên, thì cả hai sẽ đều trả về cùng một kết quả – giá trị trong B4.

Xem thêm: Tài liệu excel cơ bản 2018

Công thức Excel OFFSET – những điều cần nhớ:

• Hàm OFFSET trong Excel không thực sự di chuyển bất kỳ ô hoặc dải nào, mà điều nó làm chỉ là trả về một tham chiếu.
• Khi một công thức OFFSET trả về 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ố tham chiếu phải bao gồm một ô hoặc một dải ô liền kề, nếu không công thức của bạn sẽ trả về #VALUE! lỗi.
• Nếu các hàng (rows) được chỉ định và/hoặc cột (cols) di chuyển một tham chiếu đến rìa của bảng tính, thì công thức Excel OFFSET của bạn sẽ trả về #REF! lỗi.
• Hàm OFFSET có thể được sử dụng trong bất kỳ hàm Excel nào khác mà có chấp nhận một tham chiếu ô hoặc dải trong các đối số của nó.

Ví dụ, nếu bạn cố gắng sử dụng công thức tương tự thế này: = OFFSET (A1,3,1,1,3), thì nó sẽ trả về # VALUE! Lỗi, vì một dải để chuyển thành (1 hàng, 3 cột) không phù hợp với việc đặt chỉ trong một ô duy nhất. Tuy nhiên, nếu bạn nhúng nó vào hàm SUM, như thế này:

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

Công thức sẽ trả lại tổng các giá trị trong dải có 1 hàng bằng dải gồm 3 cột, bao gồm 3 hàng bên dưới và 1 cột bên phải ô A1, tức là tổng giá trị có trong các ô B4: D4.

Sử dụng OFFSET  trong trường hợp nào:

Bây giờ bạn đã biết hàm OFFSET làm được những gì, vậy nên bạn có thể tự hỏi mình “Tại sao lại phải dùng nó?” Tại sao không chỉ đơn giản là viết một tham chiếu trực tiếp như B4: D4?

Công thức OFFSET rất hữu ích nếu bạn muốn:

Tạo các dải động: Các tham chiếu như B1: C4 là tĩnh, có nghĩa là chúng luôn tham chiếu đến một dải nhất định. Nhưng với một số công việc thì sẽ dễ thực hiện hơn khi làm với dải động. Đây là trường hợp đặc biệt khi bạn làm việc với việc thay đổi dữ liệu, ví dụ: Bạn có một bảng tính, và mỗi tuần sẽ có một hàng hoặc cột mới được thêm vào trong đó.

Lấy dải từ ô gốc: Đôi khi, bạn có thể không biết địa chỉ thực của cả dải, mặc dù bạn có biết rằng nó bắt đầu từ một số ô. Trong các tình huống như vậy, sử dụng OFFSET trong Excel là 1 cách làm đúng.

Làm thế nào để sử dụng hàm OFFSET trong Excel – các công thức ví dụ:

Tôi hy vọng bạn đã không cảm thấy nản với đống lý thuyết ở trên. Dù sao, bây giờ chúng ta đang thực hiện phần thú vị nhất – áp dụng hàm OFFSET trong tình huống thực tế.

Hàm OFFSET và  hàm SUM:

Ví dụ chúng ta đã thảo luận cách đây một phút thể hiện cách sử dụng OFFSET và SUM trong Excel đơn giản nhất. Bây giờ, chúng ta hãy nhìn vào những hàm này ở một góc độ khác và xem những gì chúng có thể làm.

Ví dụ 1. Một công thức hàm SUM / OFFSET thay đổi:

Vấn đề nữa là khi làm việc với bảng tính cập nhật liên tục, bạn có thể muốn có một công thức SUM tự động chọn tất cả các hàng mới được thêm vào?

Giả sử bạn có dữ liệu nguồn tương tự như những gì bạn thấy trong hình bên dưới. Mỗi tháng sẽ có một dòng mới được thêm vào ngay phía trên công thức SUM, và tự nhiên, bạn muốn nó được bao gồm trong tổng số. Trên toàn bộ, có hai lựa chọn – hoặc cập nhật các dải trong công thức SUM mỗi lần thay đổi bằng tay hoặc bạn sẽ dùng hàm OFFSET làm điều này cho bạn.

Khi ô đầu tiên của dải được tính tổng (SUM) mà được tham chiếu trực tiếp trong công thức SUM, bạn chỉ phải đưa ra các tham số cho hàm OFFSET, và sau đó nó sẽ tham chiếu cho đến ô cuối cùng của dải:

• Reference – ô chứa tổng số, trong ví dụ ở đây là ô B9.
• Rows – ô trên ô chứa tổng số và nằm bên phải, bắt buộc 1 số âm: -1.
• Cols – là 0 vì bạn không muốn thay đổi cột.

Từ đó, ta có mẫu công thức SUM / OFFSET:

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

Rút gọn ví dụ trên, ta sẽ có công thức trông như sau:

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

Và nó thực sự hoạt động tốt như trong hình được minh họa dưới đây:

Xem thêm: Các ví dụ về hàm Sum trong Excel – Cách chỉ tính tổng các cột, hàng hoặc ô được hiển thị

Ví dụ 2. Công thức OFFSET  để tổng hợp N các dòng cuối danh sách:

Trong ví dụ trên, giả sử bạn muốn biết số tiền thưởng (Bonus) cho N tháng gần nhất chứ không phải là tất cả. Và bạn cũng muốn công thức chạy tự động (không quan tâm đến liệu có bất kỳ hàng mới được thêm vào trang tính hay không).

Với nhiệm vụ này, chúng ta sẽ sử dụng Excel OFFSET kết hợp với các hàm SUM và hàm COUNT / COUNTA:

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

hoặc là

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

Các thông tin sau đây có thể giúp bạn hiểu rõ các công thức tốt hơn:

• Reference – tiêu đề của cột có các giá trị mà bạn muốn tính tổng, trong ví dụ này là ô B1.
• Rows – để tính số hàng để tính offset, và bạn sử dụng thêm hàm COUNT hoặc COUNTA.

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

Nếu bạn muốn chọn hàm COUNTA, thì khi đó bạn không cần phải thêm 1, vì hàm này tính tất cả các ô không chứa giá trị rỗng, và hàng tiêu đề có giá trị văn bản được thêm một ô thêm mới mà công thức cần đến. Xin 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ính tương tự – và dòng tiêu đề tiếp theo là hàng có số. Đố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 / COUNTA.

• Cols – số cột điều chỉnh là 0.
• Chiều cao – số hàng được tổng hợp được chỉ định trong E1.
• Chiều rộng – 1 cột.

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

Theo cách tương tự như chúng tôi có thể tính tiền thưởng cho N tháng qua, bạn có thể nhận được giá trị trung bình cho N ngày, tuần hoặc năm cuối cùng cũng như tìm các 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 là tên của hàm đầu tiên:

= 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))

 

Lợi ích chính mà các công thức này hơn hàm AVERAGE(B5: B8) hoặc MAX (B5: B8) là bạn sẽ không phải cập nhật công thức mỗi khi bảng gốc của bạn được cập nhật. Bạn sẽ không cần phải quan tâm rằng bao nhiêu dòng mới được thêm vào hoặc xóa trong bảng tính Excel của bạn, mà sẽ có các công thức OFFSET sẽ luôn luôn tham chiếu đến tận những ô cuối cùng (đã quy định trước giới hạn nhỏ nhất và lớn nhất) trong cột.

Công thức OFFSET để tạo ra một dải động

Được sử dụng cùng với COUNTA, hàm OFFSET có thể giúp bạn tạo một dải động hữu ích trong nhiều tình huống, ví dụ như khi bạn muốn tạo danh sách theo mục có thể tự động cập nhật.

Công thức OFFSET cho một dải động như sau:

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

Trong đó:

• Reference: được biểu diễn dưới dạng tên bảng và theo sau là dấu chấm than, và địa chỉ của ô có chứa mục đầu tiên có mặt trong trong dải (-đã được đặt tên). Xin lưu ý việc sử dụng các tham chiếu ô tuyệt đối ($), ví dụ Sheet1! $A$1.
• Các tham số Rows and Cols đều là 0, vì không có các cột hoặc các hàng được thêm vào.
• Height là điểm mấu chốt ở đây. Tình huống là bạn 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. Với đó, hãy lưu ý rằng trong tham số của COUNTA, bạn nên chỉ định tên bảng bên cạnh tên cột, ví dụ: COUNTA (Sheet_Name!$A: $A).
• Width là 1 cột.

Một khi bạn đã tạo một dải động (đã đặt tên) với công thức OFFSET ở trên, bạn có thể sử dụng Excel Data Validation để tạo danh sách theo mục – 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).

Hàm OFFSET & VLOOKUP:

Như bạn đã biết, các tra cứu đơn giản theo chiều dọc và ngang trong Excel được thực hiện với hàm VLOOKUP hoặc hàm HLOOKUP. Tuy nhiên, các hàm này có quá nhiều hạn chế và thường là rắc rối lớn trong việc tạo công thức tra cứu mạnh hơn và phức tạp hơn. Vì vậy, để thực hiện tra cứu “một cách tinh vi hơn” trong các bảng Excel, bạn phải tìm kiếm các lựa chọn thay thế như hàm INDEX, MATCH và OFFSET.

Ví dụ 1. Công thức OFFSET cho một Vlookup dò bên trái trong Excel

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

Trong bảng tra cứu mẫu của chúng tôi, có hai cột – tên tháng (month) (cột A) và tiền thưởng (Bonus) (cột B). Nếu bạn muốn nhận được tiền thưởng cho một tháng nhất định, công thức VLOOKUP đơn giản này sẽ hoạt động mà không gặp trở ngại:

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

Tuy nhiên, ngay khi bạn tráo đổi các cột trong bảng tra cứu, điều này sẽ ngay lập tức dẫn đến lỗi #N/A:

Để xử lý một tra cứu bên trái, bạn cần một hàm linh hoạt hơn mà không thực sự quan tâm đến vị trí của cột đó ở đâu. Một trong những giải pháp có thể sử dụng chính là kết hợp các hàm INDEX và MATCH. Một cách làm khác chính là sử dụng OFFSET, MATCH và ROWS:

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

Trong ví dụ của chúng tôi, bảng tra cứu là A5: B9 và giá trị tra cứu nằm trong ô B1, vì vậy công thức trên sẽ triển khai thành:

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

Tôi biết công thức trông hơi vụng về, nhưng nó thực sự được việc 🙂

Ví dụ 2. Làm thế nào sử dụng Vlookup dò bên trên trong Excel

Như trường hợp với Excel Vlookup không thể dò giá trị bên trái, chiều ngang – và hàm HLOOKUP – không thể dò giá trị bên trên để xuất ra một giá trị.

Nếu bạn cần quét một hàng phía trên để tìm một giá trị phù hợp, công thức OFFSET / MATCH / ROWS có thể giúp bạn một lần nữa, nhưng lần này bạn sẽ phải tăng cường hàm COLUMNS 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: I5 và giá trị tra cứu là trong ô E1, công thức đi như sau:

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

Ví dụ 3. tra cứu hai chiều (theo cột và giá trị hàng)

Tra cứu hai chiều trong Excel trả về một giá trị dựa trên các kết quả trùng khớp trong các hàng và cột. Và bạn có thể sử dụng công thức mảng tra cứu 2 chiều sau để tìm một giá trị tại giao điểm của một hàng và cột yêu cầu:

=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)

Biết rằng:

• 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

Bạn sẽ 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, T OFFT (A5: G9, 0, 0, 1, COLUMNS (A5: G9)), 0) -1)

Đó không phải là thứ dễ nhớ, phải không? Ngoài ra, đây là một công thức mảng, do đó đừng quên nhấn Ctrl + Shift + Enter để nhập chính xác dữ liệu.

Hàm OFFSET – hạn chế và các lựa chọn thay thế

Hy vọng rằng các ví dụ công thức đã làm sáng tỏ cách sử dụng OFFSET trong Excel. Tuy nhiên, để tận dụng hiệu quả hàm này trong bảng tính làm việc của bạn, thì bạn không chỉ nên hiểu biết về thế mạnh của nó, mà còn phải cảnh giác với những hạn chế của nó.

Những hạn chế quan trọng nhất của hàm OFFSET như sau:

• Excel OFFSET là một hàm rất “khát dữ liệu”: Bất kì sự thay đổi dữ liệu gốc nào, các công thức OFFSET của bạn sẽ được tính lại, khiến cho Excel càng bận rộn hơn. Đây không phải là vấn đề cho một công thức đơn lẻ trong một bảng tính nhỏ. Nhưng nếu có hàng chục hoặc hàng trăm công thức trong một bảng tính, Microsoft Excel có thể mất một thời gian để tính toán lại.
• Excel công thức OFFSET rất khó để xem lại. Bởi vì các tham chiếu được trả lại bởi hàm OFFSET Excel là động, đặc biệt là với các công thức lớn (đặc biệt với các hàm OFFSET lồng nhau) có thể khá khó để gỡ lỗi.

Tìm hiểu thêm: Học VBA ở đâu?

Các lựa chọn thay thế cho việc sử dụng OFFSET trong Excel

Điều này là một trường hợp thường xảy ra trong Excel, những kết quả tương tự có thể đạt được qua một số cách khác nhau. Và đây là ba lựa chọn thay thế đơn giản tối ưu cho công cụ OFFSET:

1. Các bảng trong Excel:

Từ sau đời Excel 2002, chúng tôi đã có một tính năng thực sự tuyệt vời – với các bảng Excel đầy đủ, khác hẳn với các dải thông thường. Để tạo bảng từ dữ liệu có cấu trúc, bạn chỉ cần nhấp vào Insert > Table trên tab Home (Trang chủ) hoặc sử dụng tổ hợp phím Ctrl + T.

Bằng cách nhập một công thức trong một ô trong một bảng Excel, bạn có thể tạo ra một cột được tính tự động với công thức được sao chép cho tất cả các ô khác trong cột đó và điều chỉnh cho mỗi hàng trong bảng.

Hơn thế nữa, 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 một bảng tính có một tên duy nhất (mặc định là Table1, Table2, …) và bạn có thể đổi tên bảng của mình thông qua 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 Bonus 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 ô.

2. Hàm INDEX

Mặc dù không giống y như OFFSET, hàm INDEX cũng được sử dụng để tạo các tham chiếu cho dải động. Không giống như OFFSET, hàm INDEX không tự thay đổi, vì vậy nó sẽ không làm chậm Excel của bạn.

3. Hàm INDIRECT

Sử dụng hàm INDIRECT bạn có thể 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. Nó cũng có thể tự động tham chiếu một bảng tính hoặc một trang tính Excel. Bạn có thể tìm thấy tất cả các ví dụ về công thức trong hướng dẫn về hàm INDIRECT của Excel.

Nguồn: Ablebits, dịch và biên tập bởi blog.hocexcel.online

 

Ngoài ra để ứng dụng Excel vào công việc một cách hiệu quả thì bạn còn phải sử dụng tốt các hàm, các công cụ khác của Excel.

Một số hàm cơ bản thường gặp như:

• SUMIF, SUMIFS để tính tổng theo 1 điều kiện, nhiều điều kiện
• COUNTIF, COUNTIFS để thống kê, đếm theo một điều kiện, nhiều điều kiện
• Các hàm xử lý dữ liệu dạng chuỗi, dạng ngày tháng, dạng số…
• Các hàm dò tìm tham chiếu Index+Match, hàm SUMPRODUCT…

Một số công cụ hay sử dụng như:

• Định dạng theo điều kiện với Conditional formatting
• Thiết lập điều kiện nhập dữ liệu với Data Validation
• Cách đặt Name và sử dụng Name trong công thức
• Lập báo cáo với Pivot Table…

Rất nhiều kiến thức phải không nào? Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học EX101 – Excel từ cơ bản tới chuyên gia của Học Excel Online. Đây là khóa học giúp bạn hệ thống kiến thức một cách đầy đủ, chi tiết. Hơn nữa không hề có giới hạn về thời gian học tập nên bạn có thể thoải mái học bất cứ lúc nào, dễ dàng tra cứu lại kiến thức khi cần. Hiện nay hệ thống đang có ưu đãi rất lớn cho bạn khi đăng ký tham gia khóa học. Chi tiết xem tại: HocExcel.Online