Tải xuống sổ làm việc mẫu
Hướng dẫn này trình bày cách sử dụng Hàm OFFSET trong Excel trong Excel để tạo độ lệch tham chiếu từ một ô ban đầu.
Tổng quan về chức năng OFFSET
Hàm OFFSET Bắt đầu với tham chiếu ô đã xác định và trả về tham chiếu ô một số hàng và cột cụ thể bù trừ với tham chiếu ban đầu. Tham chiếu có thể là một ô hoặc nhiều ô. Offset cũng cho phép bạn thay đổi kích thước tham chiếu một số hàng / cột nhất định.
(Chú ý cách các đầu vào công thức xuất hiện)
Hàm IFERROR Cú pháp và đầu vào:
| 1 | = OFFSET (tham chiếu, hàng, cột, chiều cao, chiều rộng) |
thẩm quyền giải quyết - Tham chiếu ô ban đầu mà bạn muốn bù đắp.
hàng - Số hàng cần bù.
cols - Số cột cần bù trừ.
Chiều cao - TÙY CHỌN: Điều chỉnh số hàng trong tham chiếu.
chiều rộng - TÙY CHỌN: Điều chỉnh số cột trong tham chiếu.
Chức năng OFFSET là gì?
Hàm OFFSET là một trong những hàm bảng tính mạnh mẽ hơn vì nó có thể khá linh hoạt trong những gì nó tạo ra. Nó cung cấp cho người dùng khả năng xác định một ô hoặc phạm vi ở nhiều vị trí và kích thước khác nhau.
THẬN TRỌNG: Hàm OFFSET là một trong những hàm dễ thay đổi. Hầu hết thời gian khi bạn làm việc trong bảng tính của mình, máy tính sẽ chỉ tính toán lại một công thức nếu các đầu vào đã thay đổi giá trị của chúng. Tuy nhiên, một hàm dễ bay hơi sẽ tính toán lại mỗi thời gian bạn thực hiện thay đổi cho bất kỳ ô nào. Cần thận trọng để đảm bảo rằng bạn không gây ra thời gian tính toán lại lớn do sử dụng quá nhiều hàm biến đổi hoặc có nhiều ô phụ thuộc vào kết quả của hàm biến động.
Ví dụ về hàng cơ bản
Trong mỗi lần sử dụng hàm OFFSET, bạn cần đưa ra điểm bắt đầu hoặc điểm neo. Hãy xem bảng này để giúp hiểu điều này:
Chúng tôi sẽ sử dụng “Bob” trong ô B3 làm điểm neo của chúng tôi. Nếu chúng tôi muốn lấy giá trị ngay bên dưới (Charlie), chúng tôi sẽ nói rằng chúng tôi muốn chuyển hàng bằng 1. Công thức của chúng tôi sẽ giống như
| 1 | = OFFSET (B3, 1) |
Nếu chúng tôi muốn thay đổi, đó sẽ là một sự thay đổi tiêu cực. Bạn có thể nghĩ rằng điều này là số hàng đang giảm, vì vậy chúng ta cần phải trừ đi. Do đó, để có được giá trị trên (Adam), chúng tôi sẽ viết
| 1 | = OFFSET (B2, -1) |
Ví dụ về cột cơ bản
Tiếp tục ý tưởng từ ví dụ trước, chúng tôi sẽ thêm một cột khác vào bảng của mình.
Nếu chúng ta muốn lấy giáo viên cho Bob, chúng ta có thể sử dụng công thức
| 1 | = OFFSET (B2, 0, 1) |
Trong trường hợp này, chúng tôi đã nói rằng chúng tôi muốn bù trừ hàng không (hay còn gọi là giữ nguyên trên cùng một hàng) nhưng chúng tôi muốn bù đắp 1 cột. Đối với các cột, số dương có nghĩa là bù sang phải và số âm có nghĩa là bù sang trái.
OFFSET và MATCH
Giả sử bạn có một số cột dữ liệu và bạn muốn cung cấp cho người dùng khả năng chọn cột nào để tìm nạp kết quả. Bạn có thể sử dụng hàm INDEX hoặc bạn có thể sử dụng hàm OFFSET. Vì MATCH sẽ trả về vị trí tương đối của một giá trị, chúng tôi sẽ cần đảm bảo điểm neo ở bên trái giá trị có thể đầu tiên của chúng tôi. Hãy xem xét bố cục sau:
Trong B2, chúng tôi sẽ viết công thức này:
| 1 | = OFFSET (B2, 0, MATCH (A2, $ C $ 1: $ F $ 1, 0)) |
TRẬN ĐẤU sẽ tìm "Tháng Hai" trong phạm vi C1: F1 và tìm nó trong 2NS tủ. Sau đó, OFFSET sẽ dịch chuyển 1 cột sang bên phải của B2 và lấy giá trị mong muốn là 9. Lưu ý rằng OFFSET không có vấn đề gì khi sử dụng cùng một ô có chứa công thức làm điểm neo.
LƯU Ý: Kỹ thuật này có thể được sử dụng để thay thế cho hàm VLOOKUP hoặc HLOOKUP khi bạn muốn trả về một giá trị từ bên trái / bên trên phạm vi tra cứu của mình. Điều này là do OFFSET có thể thực hiện hiệu số âm.
OFFSET để nhận một phạm vi
Bạn có thể sử dụng 4NS và 5NS các đối số trong hàm OFFSET để trả về một dải ô thay vì chỉ một ô đơn lẻ. Giả sử bạn muốn tính tổng 3 cột trong bảng này.
| 1 | = AVERAGE (OFFSET (A1, MATCH (F2, A2: A5,0), 1,1,3)) |
Trong F2, chúng tôi đã chọn tên của một học sinh mà chúng tôi muốn lấy điểm kiểm tra trung bình của họ. Để làm điều này, chúng tôi sẽ sử dụng công thức
| 1 | = AVERAGE (OFFSET (A1, MATCH (F2, A2: A5,0), 1,1,3)) |
MATCH sẽ tìm kiếm tên của chúng ta trong cột A và trả về vị trí tương đối, là 3 trong ví dụ của chúng ta. Hãy xem điều này sẽ được đánh giá như thế nào. Đầu tiên, OFFSET sẽ hoạt động xuống 3 hàng từ A1 và 1 cột đến đúng từ A1. Điều này đặt chúng tôi vào ô B3.
| 1 | = AVERAGE (OFFSET (A1, 3, 1, 1, 3)) |
Tiếp theo, chúng tôi sẽ thay đổi kích thước phạm vi. Phạm vi mới sẽ có B3 là ô trên cùng bên trái. Nó sẽ cao 1 hàng và cao 3 cột, cho chúng ta phạm vi B4: D4.
| 1 | = AVERAGE (OFFSET (A1,3, 1, 1, 3)) |
Lưu ý rằng mặc dù bạn có thể đặt các giá trị âm một cách hợp pháp trong các đối số offset, nhưng bạn chỉ có thể sử dụng các giá trị không âm trong các đối số định cỡ.
Cuối cùng, hàm AVERAGE của chúng tôi thấy:
| 1 | = AVERAGE (B4: D4) |
Do đó, chúng tôi nhận được giải pháp của chúng tôi là 86,67
OFFSET với SUM động
Bởi vì OFFSET được sử dụng để tìm tham chiếu, thay vì trỏ trực tiếp đến ô, nó hữu ích nhất khi bạn xử lý dữ liệu có các hàng được thêm vào hoặc bị xóa. Hãy xem xét bảng sau với Tổng số ở dưới cùng
| 1 | = SUM (B2: B4) |
Nếu chúng tôi đã sử dụng công thức SUM cơ bản ở đây là “= SUM (B2: B4)” và sau đó chèn một hàng mới để thêm bản ghi cho Bill, chúng tôi sẽ có câu trả lời sai
Thay vào đó, hãy nghĩ cách giải quyết vấn đề này theo quan điểm của Total. Chúng tôi thực sự muốn lấy mọi thứ từ ô B2 đến ô chỉ trên tổng số của chúng tôi. Cách chúng ta có thể viết điều này trong công thức là thực hiện độ lệch hàng -1. Do đó, chúng tôi sử dụng công thức này làm công thức cho tổng số của chúng tôi trong ô B5:
| 1 | = SUM (B2: OFFSET (B5, -1,0)) |
Công thức này thực hiện những gì chúng tôi vừa mô tả: bắt đầu ở B2 và chuyển đến 1 ô trên tổng số ô của chúng tôi. Bạn có thể thấy sau khi thêm dữ liệu của Bill, tổng số tiền của chúng tôi được cập nhật chính xác như thế nào.
TẮT để nhận N mặt hàng cuối cùng
Giả sử bạn đang ghi lại doanh số hàng tháng nhưng muốn có thể xem xét 3 tháng qua. Thay vì phải cập nhật công thức của bạn theo cách thủ công để tiếp tục điều chỉnh khi dữ liệu mới được thêm vào, bạn có thể sử dụng hàm OFFSET với COUNT.
Chúng tôi đã chỉ ra cách bạn có thể sử dụng OFFSET để lấy một loạt các ô. Để xác định có bao nhiêu ô chúng tôi cần phải di chuyển, chúng tôi sẽ sử dụng COUNT để tìm con số nằm trong cột B. Hãy xem bảng mẫu của chúng tôi.
| 1 | = SUM (OFFSET ($ B $ 1, COUNT (B: B) - $ E $ 1 + 1,0, $ E $ 1,1)) |
Nếu chúng tôi bắt đầu ở B1 và bù 4 hàng (số lượng trong cột B), chúng tôi sẽ kết thúc ở cuối phạm vi của chúng tôi, B5. Tuy nhiên, vì OFFSET không thể thay đổi kích thước bằng giá trị âm, chúng tôi cần thực hiện một số điều chỉnh để kết thúc ở B3. Phương trình tổng quát cho điều này sẽ là
| 1 | ĐẾM (…) - N + 1 |
Chúng tôi lấy tổng số của toàn bộ cột, trừ đi số lượng mà chúng tôi muốn trả về (vì chúng tôi sẽ thay đổi kích thước để lấy chúng) và sau đó cộng 1 (vì về cơ bản chúng tôi đang bắt đầu phần bù của mình ở vị trí 0).
Tại đây, bạn có thể thấy chúng tôi đã thiết lập một phạm vi để lấy Tổng, Trung bình và Tối đa của N tháng trước. Trong E1, chúng tôi đã nhập giá trị là 3. Trong E2, công thức của chúng tôi là
| 1 | = SUM (OFFSET ($ B $ 1, COUNT (B: B) - $ E $ 1 + 1,0, $ E $ 1,1)) |
Phần được đánh dấu là phương trình tổng quát của chúng ta mà chúng ta vừa thảo luận. Chúng tôi không cần phải bù trừ bất kỳ cột nào. Sau đó, chúng tôi sẽ thay đổi kích thước phạm vi thành chiều cao 3 ô (được xác định bởi giá trị trong E1) và rộng 1 cột. Sau đó, SUM của chúng tôi lấy phạm vi này và cho chúng tôi kết quả là $ 1,850. Chúng tôi cũng đã chứng minh rằng bạn có thể tính giá trị trung bình của giá trị tối đa của cùng một phạm vi này bằng cách chỉ cần chuyển hàm bên ngoài từ SUM sang bất kỳ tình huống nào yêu cầu.
OFFSET danh sách xác thực động
Sử dụng kỹ thuật được trình bày trong ví dụ trước, chúng ta cũng có thể tạo Dải ô được đặt tên có thể được sử dụng trong Xác thực dữ liệu hoặc biểu đồ. Điều này có thể hữu ích khi bạn muốn thiết lập một bảng tính nhưng đang mong đợi danh sách / dữ liệu của chúng tôi thay đổi kích thước. Giả sử cửa hàng của chúng tôi đang bắt đầu bán trái cây và chúng tôi hiện có 3 lựa chọn.
Để tạo menu thả xuống Xác thực dữ liệu mà chúng tôi có thể sử dụng ở những nơi khác, chúng tôi sẽ xác định phạm vi được đặt tên MyFruit là
| 1 | = $ A $ 2: OFFSET ($ A $ 1, COUNTA ($ A: $ A) -1, 0) |
Thay vì COUNT, chúng tôi đang sử dụng COUNTA vì chúng tôi đang xử lý các giá trị văn bản. Vì vậy, mặc dù vậy, COUNTA của chúng tôi sẽ cao hơn một vì nó sẽ đếm ô tiêu đề trong A1 và cho giá trị là 4. Tuy nhiên, nếu chúng tôi bù trừ 4 hàng, chúng tôi sẽ kết thúc trong ô A5 là ô trống. Sau đó, để điều chỉnh cho điều này, chúng tôi trừ đi 1.
Bây giờ chúng tôi đã thiết lập Dải ô được đặt tên, chúng tôi có thể thiết lập một số Xác thực dữ liệu trong ô C4 bằng cách sử dụng loại Danh sách, với nguồn:
| 1 | = MyFruit |
Lưu ý rằng menu thả xuống chỉ hiển thị ba mục hiện tại của chúng tôi. Sau đó, nếu chúng ta thêm nhiều mục hơn vào danh sách của mình và quay lại menu thả xuống, danh sách sẽ hiển thị tất cả các mục mới mà chúng ta không cần phải thay đổi bất kỳ công thức nào.
Thận trọng khi sử dụng OFFSET
Như đã đề cập ở đầu bài viết này, OFFSET là một hàm dễ bay hơi. Bạn sẽ không nhận thấy điều này nếu bạn đang sử dụng nó chỉ trong một vài ô, nhưng nếu bạn bắt đầu để nó tham gia vào hàng trăm phép tính và bạn sẽ nhanh chóng nhận thấy máy tính của mình dành một khoảng thời gian đáng kể để tính toán lại mỗi khi bạn thực hiện bất kỳ thay đổi nào. .
Ngoài ra, bởi vì OFFSET không đặt tên trực tiếp cho các ô mà nó đang xem, nên những người dùng khác sẽ khó đến sau này và thay đổi công thức của bạn nếu cần.
Thay vào đó, bạn nên sử dụng các Bảng (được giới thiệu trong Office 2007) cho phép tham chiếu cấu trúc. Những điều này đã giúp người dùng có thể cung cấp một tham chiếu duy nhất được tự động điều chỉnh kích thước khi dữ liệu mới được thêm vào hoặc xóa.
Tùy chọn khác để sử dụng thay vì OFFSET là hàm INDEX mạnh mẽ. INDEX cho phép bạn xây dựng tất cả các phạm vi động mà chúng tôi đã thấy trong bài viết này mà không gặp vấn đề là một hàm dễ bay hơi.
Ghi chú bổ sung
Sử dụng Hàm OFFSET để trả về giá trị ô (hoặc một dải ô) bằng cách bù trừ một số hàng và cột nhất định từ một tham chiếu bắt đầu. Khi chỉ tìm kiếm một ô duy nhất, các công thức OFFSET đạt được mục đích tương tự như Công thức INDEX, sử dụng một kỹ thuật hơi khác. Sức mạnh thực sự của Hàm OFFSET nằm ở khả năng chọn một loạt các ô sẽ được sử dụng trong một công thức khác.
Khi sử dụng Hàm OFFSET, bạn xác định ô bắt đầu ban đầu hoặc dải ô. Sau đó, bạn chỉ ra số hàng và cột cần bù trừ từ ô ban đầu đó. Bạn cũng có thể thay đổi kích thước phạm vi; thêm hoặc bớt các hàng hoặc cột.
Quay lại danh sách tất cả các hàm trong Excel
OFFSET trong Google Trang tính
Hàm OFFSET hoạt động giống hệt trong Google Trang tính cũng như trong Excel:
