VietNetwork.Vn

Giống như trò chơi chiến hạm vậy.

Hàm OFFSET của Excel cho phép bạn tạo tham chiếu—theo nghĩa đen— bù trừ từ điểm bắt đầu. Nó cho phép tham chiếu điều chỉnh động theo những thay đổi trong dữ liệu và cấu trúc bảng tính của bạn.


1. Cú pháp OFFSET

Trước khi xem cách thức hoạt động trong thực tế, chúng ta hãy xem qua cú pháp. Hàm OFFSET có năm đối số:

=OFFSET(A,B,C,D,E)

Trong đó:

• A là tham chiếu ô của điểm bắt đầu,
• B là số hàng di chuyển xuống từ điểm A,
• C là số cột cần di chuyển sang phải từ điểm A,
• D (tùy chọn) là số hàng (chiều cao) được hiển thị trong kết quả và
• E (tùy chọn) là số cột (chiều rộng) được hiển thị trong kết quả.

Các giá trị B, C, D và E có thể là tham chiếu số hoặc ô.

Sử dụng giá trị âm trong B sẽ tạo ra độ lệch hướng lên trên và giá trị âm trong C sẽ tạo ra độ lệch hướng trái.

2. Một ví dụ cơ bản về OFFSET

Để minh họa cách OFFSET hoạt động, tôi sẽ sử dụng một ví dụ rất cơ bản và không thực tế. Gõ

=OFFSET(A1,2,3,2,3)
vào ô A6 sẽ sử dụng ô A1 làm điểm bắt đầu (A), di chuyển xuống 2 hàng (B), di chuyển qua 3 cột (C) và trả về một chuỗi các giá trị cao 2 ô (D) và rộng 3 ô (E):


Nếu tôi không nhập giá trị D và E vào công thức của mình, kết quả sẽ mặc định tham chiếu đến ô D3 (Deer) một mình. Điều này là do Excel cho rằng kết quả có cùng kích thước với tham chiếu nếu bạn không chỉ định chiều cao và chiều rộng.

Dựa trên nguyên tắc này, bạn cũng có thể xác định kích thước của kết quả trong giá trị A. Ví dụ, nhập

=OFFSET(A1:A3,2,3)
sẽ trả về kết quả cao bằng ba ô vì đây là kích thước của điểm bắt đầu A.

3. Sử dụng OFFSET trong thế giới thực

Bây giờ, tôi sẽ chỉ cho bạn cách sử dụng OFFSET trong các ví dụ thực tế.

3.1. Ví dụ thực tế 1

Ở đây, tôi có mười nhân viên và số lượng sản phẩm họ đã bán trong ba tuần. Tôi sẽ sử dụng OFFSET để tạo ba báo cáo dữ liệu.


Trong báo cáo dữ liệu đầu tiên (màu tím), tôi muốn Excel cho tôi biết số lượng đơn vị mà một nhân viên đã bán khi tôi nhập ID của họ và số tuần. Để thực hiện điều này, trong ô H4, tôi sẽ nhập

=OFFSET(B1,H2,H3)
trong đó B1 là điểm bắt đầu, H2 cho Excel biết cần phải bù trừ bao nhiêu hàng từ điểm bắt đầu (nói cách khác là ID nhân viên) và H3 cho Excel biết cần bù trừ bao nhiêu cột từ điểm bắt đầu (nói cách khác là số tuần). Lưu ý cách tôi sử dụng tham chiếu ô ở đây, vì điều này có nghĩa là dữ liệu mà hàm OFFSET lấy từ bảng của tôi có thể là dữ liệu động.

Tôi bắt đầu với ô B1 là ô đầu vào đầu tiên vì tôi muốn tuần đầu tiên là ô đầu tiên bên phải điểm bắt đầu đó. Vì vậy, bất cứ khi nào bạn sử dụng hàm OFFSET, hãy suy nghĩ cẩn thận về vị trí điểm bắt đầu của bạn.

Sau khi nhập công thức trên vào ô H4, tôi sẽ nhập 6 vào ô ID (ô H2) và 2 vào ô tuần (ô H3). Điều này sẽ cho tôi biết Ollie đã bán được bao nhiêu đơn vị trong tuần 2, vì hàm OFFSET đang kéo dữ liệu từ ô xuống sáu hàng và hai cột ngang qua điểm bắt đầu của tôi, ô B1.


Bây giờ tôi có thể thay đổi giá trị trong ô H2 và H3 tại bất kỳ thời điểm nào để lấy bất kỳ số liệu nào từ bảng.

Tiếp theo, tôi muốn tạo báo cáo nhân viên trong bảng màu xanh. Khi tôi nhập ID nhân viên, Excel sẽ tự động cho tôi biết tên nhân viên (sử dụng hàm VLOOKUP), tổng số tuần dữ liệu chúng tôi có (sử dụng hàm COUNTIF) và tổng số và trung bình các đơn vị bán được của nhân viên đó (sử dụng SUM và AVERAGE với OFFSET).

Đầu tiên, tôi sẽ nhập ID nhân viên vào ô H7 để bắt đầu. Chúng ta hãy nhập ID nhân viên 3, tức là Jason. Sau đó, tôi sẽ sử dụng hàm VLOOKUP để trả về tên Jason trong ô H8:

=VLOOKUP(H7,A2:B11,2)

Bây giờ, tôi sẽ sử dụng hàm COUNTIF với ký tự đại diện để yêu cầu Excel đếm số ô trong hàng 1 chứa từ "Tuần" theo sau là một số:

=COUNTIF(1:1,"Week*")

Bây giờ tôi có thể sử dụng thông tin này để tính tổng số đơn vị bán ra và trung bình của Jason. Đầu tiên, trong ô H10, tôi sẽ nhập

=SUM(OFFSET(B1,H7,1,1,H9))
trong đó B1 là điểm bắt đầu của tôi, H7 là số hàng cần bù xuống (ID nhân viên), số 1 đầu tiên là số cột cần bù sang phải, số 1 thứ hai là chiều cao của kết quả và H9 là chiều rộng của kết quả (tổng số tuần). Tuy nhiên, vì OFFSET được nhúng trong hàm SUM, nên kết quả sẽ chỉ có kích thước một ô.


Tôi có thể sao chép và dán công thức tương tự vào ô H11, nhưng đổi SUM thành AVERAGE, vì tôi sử dụng cùng một tham chiếu.

=AVERAGE(OFFSET(B1,H7,1,1,H9))

Bây giờ tôi có thể thay đổi giá trị trong ô H7 để tạo báo cáo cho bất kỳ nhân viên nào khác trong bảng dữ liệu của tôi. Hơn nữa, nếu tôi thêm một tuần nữa vào dữ liệu của mình, ô H9 sẽ tự động đổi thành 4 và các phép tính tiếp theo trong ô H10 và H11 cũng sẽ được cập nhật.

Cuối cùng, tôi muốn tạo báo cáo hàng tuần trong bảng màu cam. Điều này sẽ yêu cầu tôi nhập số tuần và Excel sẽ tạo tổng số đơn vị và trung bình đã bán trong tuần đó. Tôi sẽ bắt đầu bằng cách nhập 1 vào ô H14, do đó Excel sẽ tạo dữ liệu từ tuần 1. Sau đó, trong ô H15, tôi sẽ nhập

=SUM(OFFSET(B1,1,H14,10,1))
trong đó B1 là điểm bắt đầu, số 1 đầu tiên là số hàng cần bù xuống, H14 là số cột cần bù sang phải (số tuần), 10 là chiều cao của kết quả và số 1 thứ hai là chiều rộng của kết quả. Tuy nhiên, vì OFFSET được nhúng trong hàm SUM, nên kết quả sẽ chỉ có kích thước một ô.


Cuối cùng, tôi có thể sao chép và dán công thức đó vào ô H15 và đổi SUM thành AVERAGE.

=AVERAGE(OFFSET(B1,1,H14,10,1))

3.2. Ví dụ thực tế 2

Trong ví dụ này, tôi sẽ chỉ cho bạn cách sử dụng hàm OFFSET với một bảng được định dạng và cách nó hiển thị khi được sử dụng để trả về kết quả trong nhiều ô.

Tại đây, tôi có doanh số và lợi nhuận hiện tại trong năm nay trong một bảng được định dạng và tôi muốn bảng báo cáo của mình ở bên phải hiển thị tổng lợi nhuận của ba tháng gần nhất, cũng như lợi nhuận trung bình và tổng lợi nhuận trong cùng kỳ.


Đầu tiên, tôi cần Excel tính toán xem đã trôi qua bao nhiêu tháng đầy đủ cho đến nay, để khi tôi sử dụng OFFSET, tôi có thể sử dụng con số này để cho Excel biết tôi cần bù trừ bao xa so với điểm bắt đầu. Để thực hiện việc này, trong ô F1, tôi sẽ nhập

=MONTH(TODAY())
sẽ hiển thị số tháng hiện tại. Trong trường hợp này là tháng 11, tháng 11.


Bây giờ chúng ta có thể sử dụng số tháng này để hiển thị lợi nhuận của ba tháng qua. Trong ô F3, tôi sẽ nhập

=OFFSET(
và nhấp vào ô A1, điểm bắt đầu của tôi. Thao tác này sẽ tự động thêm tiêu đề cột tháng của bảng đã định dạng vào công thức:

=OFFSET(Table1[[#Headers],[Month]]
Bây giờ, tôi sẽ thêm dấu phẩy và tiếp tục công thức OFFSET của mình. Giai đoạn tiếp theo là cho Excel biết cần bù trừ bao nhiêu hàng xuống dưới. Đây sẽ là tháng hiện tại (ô F1) trừ đi ba:

=OFFSET(Table1[[#Headers],[Month]],SUM(F1-3),
Cuối cùng, tôi sẽ cho Excel biết rằng chúng ta cần bù hai cột sang bên phải và kết quả sẽ là chiều cao là ba hàng và chiều rộng là một cột.

=OFFSET(Table1[[#Headers],[Month]],SUM(F1-3),2,3,1)

Lưu ý cách các ô F4 và F5 chứa dữ liệu, mặc dù công thức chỉ có trong F3. Điều này được gọi là mảng tràn, vì dữ liệu đã tràn qua ô đang hoạt động.

Bây giờ, khi tháng chuyển sang 12 (tháng 12), Excel sẽ tự động di chuyển kết quả này xuống một ô vì giá trị trong ô F1 sẽ tăng thêm một.

Sau đó tôi có thể hoàn thành bảng của mình bằng cách nhập

=AVERAGE(F3#)
vào ô F7 và

=SUM(F3#)
vào ô F8.

Biểu tượng octothorp (#) là cách Excel tính toán mảng bị tràn.


4. Những điều cần nhớ

Có hai điều quan trọng cần nhớ khi sử dụng OFFSET:

• OFFSET là một hàm dễ bay hơi, nghĩa là nó luôn tìm cách cập nhật. Điều này có thể gây ra các vấn đề về hiệu suất nếu được sử dụng trong một bảng tính đặc biệt lớn hoặc trên máy tính có bộ nhớ thấp.
• Tất cả các ví dụ trên được sử dụng để minh họa những cách khác nhau mà bạn có thể sử dụng hàm OFFSET. Có thể có những cách khác hiệu quả hơn để bạn tạo ra cùng một kết quả, chẳng hạn như bằng cách sử dụng hàm INDIRECT. Tuy nhiên, sau khi bạn đã hiểu cách thức hoạt động của các hàm, bạn có thể quyết định cách nào là tốt nhất để sử dụng trong tình huống của mình!

Cá nhân tôi sử dụng hàm OFFSET để ghi lại và phân tích dữ liệu của đội bóng đá yêu thích của tôi. Xét cho cùng, Excel không chỉ dành cho kế toán—bạn có thể sử dụng nó ở nhà cùng với sở thích của mình !