OFFSET

Hàm ứng dụng rất tốt trong việc tạo mảng động

Pham Nguyen Khanh

BASIC OFFSET

Công dụng:
(1) trả về dữ liệu (single cell) được truy cứu (tương tự các hàm VLOOKUP, HLOOKUP, LOOKUP, INDEX),
(2) trả về một vùng dữ liệu (multiple cells/range) được truy cứu.

Cấu trúc:

=OFFSET(reference, rows, columns, [height], [width])

Trong đó:
– reference (bắt buộc): single cell hoặc multiple cells, ô/dãy ô này được lấy làm vị trí xuất phát ($tuyệt$đối) để tham chiếu đến và trả về những ô/vùng khác;
– rows (bắt buộc): số hàng dịch chuyển
(1) rows > 0, từ reference di chuyển xuống,
(2) rows < 0, từ refrence di chuyển lên,
(3) rows = 0, nằm cùng hàng với reference (reference nằm ở hàng số 0).
– columns (bắt buộc): số cột dịch chuyển
(1) columns > 0, từ reference di chuyển sang phải,
(2) columns < 0, từ refrence di chuyển sang trái,
(3) columns = 0, nằm cùng cột với reference (reference nằm ở cột số 0).
___
Khi sử dụng đến [height] và [width] nghĩa là bạn cần trả về multiple cells, lúc này, trước khi gõ công thức cần bôi đen đúng số lượng cells muốn trả về, và kết thúc bằng Ctrl+Shift+Enter vì đây là hàm mảng.
– height (tuỳ chọn): số hàng muốn trả về
(1) bỏ qua = 1, trả về 1 hàng,
(2) height = n, trả về n hàng.
– widht (tuỳ chọn): số cột muốn trả về
(1) bỏ qua = 1, trả về 1 cột,
(2) width = n, trả về n cột.

Ví dụ minh hoạ:

Thao tác:
(1) Bôi đen range H13:I17,
(2) Nhập hàm như trên hình,
(3) Kết thúc hàm bằng Ctrl+Shift+Enter.

Giải thích OFFSET+MATCH: chúng ta cần biết đối số rows bằng bao nhiêu, MATCH sẽ giúp chúng ta tìm xem loại hàng “C” ở trong C3:C7, sau đó trả về vị trí của “C”. Và tại sao lại tham chiếu ô C2, vì reference luôn ở hàng 0 và cột 0, nhưng MATCH lại trả về giá trị nhỏ nhất là 1, khi đó loại hàng “A” ở hàng số 1, vậy đúng ý của chúng ta.
Bạn vẫn có thể tham chiếu C3 được, nhưng đến đối số rows nhớ -1 (MATCH()-1) nhé!

ADVANCED OFFSET

OFFSET + SUM + MATCH + ROWS

Thao tác:
(1) Tạo Data Validation tại H30, dữ liệu tham chiếu từ vùng C30:C34,
(2) Nhập hàm như hình,
(3) Kết thúc hàm bằng Enter.

Giải thích hàm:
Chúng ta cần tìm tổng, nên SUM sẽ làm hàm chính và được đặt ngoài cùng. OFFSET ở đây có sử dụng 2 đối số [width] và [height] nhưng tại sao lại kết thúc bằng Enter mà không phải Ctrl+Shift+Enter? Đơn giản là vì chúng ta không hiển thị các giá trị trả về mà chỉ cần chúng đóng vai trò là các số hạng để hàm SUM xử lý chúng.
Dùng MATCH để tìm vị trí ngày bán trong vùng C30:C34, MATCH trả về đối số rows, chúng ta có được vị trí hàng cần lấy ra dữ liệu.
ROWS trả về số hàng C30:C34, tiếp tục trừ cho MATCH(), lấy hiệu số này +1, trả về giá trị của đối số [height] – muốn lấy giá trị từ bao nhiêu hàng. Chúng ta không cần [width] vì tất cả giá trị muốn trả về đều nằm chung cột D.