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], [awidth])

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à awidth luôn phải là số dương. Nếu một trong hai bị bỏ qua, thì height hoặc awidth 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