Thành thạo Excel là ngày nay là điều mà khá nhiều doanh nghiệp yêu cầu từ phía khả năng các ứng viên. Và trong số những hàm cơ bản của Excel, bạn không thể không biết tới hàm OFFSET…

Hàm OFFSET trongcác hàm Excel là hàm trả về dữ liệu tham chiếu đến một vùng nào đó, được tính bằng 1 ô hay một dãy ô tương ứng bắt đầu và số dòng hoặc số cột được người thiết lập hàm chỉ định trước. Ta có thể chỉ định rõ số thông tin về hàng số cột của vùng tham chiếu trả về trong hàm OFFSET.

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

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

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

2. Những đối số tùy chọn của hàm OFFSET

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

Cả hai đối số height và width của hàm OFFSET trong các hàm Excel cơ bản luôn phải là số dương. Nếu một trong hai đối số này bị bỏ qua, thì height hoặc width của tham chiếu gốc tương ứng được sử dụng.

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

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

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

1. Hàm OFFSET và  hàm SUM

VÍ DỤ: MỘT CÔNG THỨC HÀM SUM / HÀM OFFSET THAY ĐỔI:

Vấn đề đặt ra ở đây 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 thực hiện 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 được cho sẵ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 dữ liệu 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 thể hiện bao gồm trong tổng số. Về cơ bản, có hai lựa chọn – hoặc thực hiện cập nhật các dải trong công thức hàm SUM mỗi lần thay đổi bằng tay hoặc bạn sẽ ứng dụng ngay hàm OFFSET làm điều này cho bạn.

MỘT CÔNG THỨC HÀM SUM / HÀM OFFSET THAY ĐỔI

Ứng dụng ngay hàm OFFSET

Khi phần dữ liệu ở ô đầ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 hàm SUM, bạn chỉ phải đưa ra các tham số cụ thể cho hàm OFFSET, và sau đó nó sẽ thực hiện tham chiếu cho đến ô cuối cùng của dải:

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

Từ đó, ta có mẫu công thức SUM / hàm 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 cuối cùng trông như sau:

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

Và nó thực sự hoạt động khá hiệu quả như trong hình được minh họa dưới đây:

nó thực sự hoạt động khá hiệu quả

Nó thực sự hoạt động khá hiệu quả

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

Trong ví dụ trên, giả sử bạn muốn xác định được 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 của mình (không quan tâm đến liệu có bất kỳ hàng mới tương ứng đượ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 một trong số các hàm Excel cơ bản - hàm 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)))

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

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

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

Được ứng dụng thực tế cùng với COUNTA, hàm OFFSET trong các hàm Excel 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 lập ngay danh sách theo mục có thể tự động cập nhật liên tục.

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

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

Trong đó:

Reference: là phần dữ liệu đượ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 ô trong hàm OFFSET có chứa mục đầu tiên có mặt trong trong dải (-đã được đặt tên). Xin lưu ý trong việc sử dụng các tham chiếu kiểu ô tuyệt đối ($), ví dụ Sheet1! $A$1.

Các tham số của Rows and Cols đều là 0, vì không tồn tại các cột hoặc các hàng được thêm vào.

Height là phần dữ liệu được coi như điểm mấu chốt ở đây. Tình huống là bạn sử dụng hàm COUNTA trongphần mềm Excel để tính toán số ô không rỗng trong cột có chứa các mục của dải đã được thực hiện đặt tên. Với đó, hãy lưu ý nho nhỏ rằng trong tham số của hàm COUNTA, bạn nên chỉ định rõ ràng 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 đã thực hiện tạo một dải động (đã đặt tên) với công thức hàm OFFSET ở trên, bạn có thể sử dụng hàm Excel Data Validation để có thể trực tiếp tạo danh sách theo mục – luôn tự động cập nhật ngay cả khi bạn thêm hoặc tiện tay xóa các mục từ Danh sách nguồn (bảng tính gốc).

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

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

V. Hàm OFFSET và VLOOKUP

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

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

Trong bảng tra cứu mẫu của chúng tôi, có tồn tại hai cột – tên tháng (month) (cột A) và các khoản tiền thưởng (Bonus) (cột B). Nếu bạn muốn biết mình sẽ nhận được tiền thưởng bao nhiêu cho một tháng nhất định, công thức của hàm 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 mà bạn thực hiện tráo đổi các cột trong bảng tra cứu, điều này sẽ trực tiếp dẫn đến lỗi #N/A:

trực tiếp dẫn đến lỗi #N/ATrực tiếp dẫn đến lỗi #N/A

Để xử lý một phần dữ liệu 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 hẳn đến vị trí của cột đó ở đâu. Một trong những giải pháp tối ưu nhất 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 hàm 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ụ, bảng tra cứu là A5: B9 và giá trị tra cứu được đặt nằm trong ô B1, vì vậy công thức trên sẽ có thể triển khai thành:

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

Công thức hàm OFFSET cho một Vlookup dò bên trái trong Excel

Công thức hàm OFFSET cho một Vlookup dò bên trái trong Excel

2. 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 hàm Excel Vlookup không thể dò giá trị bên trái, chiều ngang – và hàm HLOOKUP cũng tương tự – 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 kiếm ra một giá trị phù hợp, công thức hàm OFFSET / MATCH / ROWS có thể giúp bạn một lần nữa, nhưng lần này thì chính 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 dữ liệu hàm OFFSET là E4: I5 và giá trị tra cứu là vị trí 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)

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

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

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

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

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

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

  • Các bảng trong Excel
  • Hàm Index: Mặc dù về bản chất hàm này không giống y như hàm OFFSET, hàm INDEX cũng được sử dụng để thực hiện tạo các tham chiếu cho dải động. Không giống như hàm OFFSET, hàm INDEX không tự thay đổi các dữ kiện trong nó, vì vậy nó sẽ có ưu điểm là không làm chậm Excel của bạn.
  • Hàm Indirect: Sử dụng hàm INDIRECT trong Excel cơ bản khiến bạn có thể tạo tham chiếu cho dải động, từ nhiều nguồn ví dụ như các ô giá trị, giá trị và văn bản của ô, và thậm chí là các dải ô được đặt tên. Nó cũng có thể tự động thực hiện tham chiếu dữ liệu trong một bảng tính hoặc một trang tính Excel. 

VII. Kết

Những kiến thức trên là hành trang quan trọng cho bạn để có thể chinh phục các hàm Excel, đặc biệt là hàm OFFSET. Hãy đón đọc các bài viết sau của 123job để tìm hiểu thêm những kỹ năng quan trọng khác bạn nhé!