Làm chủ hàm OFFSET trong Excel 2021

1. I - Nhập cùng lúc các ô có cùng giá trị (Ctrl + Enter)
2. II - Chọn toàn bộ dữ liệu trong bảng Excel
3. III - Hiển thị tất cả các công thức

I - Giới thiệu về hàm OFFSET

Mô tả: Hàm OFFSETđược sử dụng cho việc tính toán vùng dữ liệu động

Cấu trúc:

Trong đó:

Reference (Bắt buộc): Là vùng tham chiếu thường là ô bắt đầu

Rows (Bắt buộc): Số hàng, lên hoặc xuống

Cols (Bắt buộc): Số cột, về bên trái hoặc phải

Height (Tùy chọn): Chiều cao tính bằng số hàng

Width (Tùy chọn): Độ rộng, tính bằng số cột

II - Ví dụ 1

Sử dụng kết hợp hàm OFFSET để tính toán tổng doanh thu cho 3 tháng gần đây nhất của bảng dữ liệu sau. (Tính theo chiều dọc).

Phân tích dữ liệu

- Theo như yêu cầu đề bài ta sẽ tính tổng thu nhập 3 tháng gần đây (trong ví dụ là 6, 7 , 8).

- Để tính được tổng ta sẽ sử dụng hàm SUM. Nhưng ở đây nếu ta thêm dữ liệu ở tháng 9 thì 3 tháng gần đây sẽ là 7, 8, 9

- Để vùng dữ liệu (tham chiếu) được thay đổi ta cần sử dụng hàm OFFSET để thiết lập lại cho vùng dữ liệu

Bước 1: Gõ hàm =SUM(

Bước 2: Nhập hàm OFFSET(

Bước 3: Vùng tham chiếu reference là ô tiêu đề (C2)

Bước 4: Rows (số hàng lên xuống bắt đầu từ vị trí C2). Nếu lên ta nhập số âm nếu xuống ta nhập số dương. Vì vị trí ta muốn nhập là 3 dòng cuối cùng do đó ta dùng hàm COUNTA để đếm số ô có chứa chữ (kí tự) ta sẽ chọn các ô từ (C3:C14) sau đó trừ đi 2. Tại sao ta lại trừ đi 2 vì vị trí ta muốn lấy là trước 2 ô cuối cùng dó đó ta phải trừ đi 2. Nếu bạn tính tổng 5 tháng thì bạn phải trừ đi 4.

Bước 5: Cols (số cột qua trái hoặc phải từ vị trí C2). Nếu dịch qua trái ta dùng số âm, dịch qua phải ta dùng số dương. Vì cột ta tính chính là cột C lên ta không dịch do đó thông số này ta để là 0.

Bước 6: Height (chiều cao tính bằng số hàng). Số hàng ta cần tính trong ví dụ này là 3 tháng do đó thông số này ta điền là 3.

Bước 7: Width (độ rộng tính bằng số cột). Số cột ở đây ta cần tính chỉ có cột C do đó thông số này ta nhập là 1.

Đóng ngoặc hàm OFFSET và Hàm SUM ta được công thức hoàn chỉnh

III - Ví dụ 2

Sử dụng kết hợp hàm OFFSET để tính toán doanh thu trung bình cho 3 tháng gần đây nhất của bảng dữ liệu sau. (Tính theo chiều ngang).

Phân tích bài toán

- Theo như yêu cầu đề bài ta sẽ tính thu nhập trung bình cho 3 tháng gần đây (trong ví dụ là 6, 7 , 8).

- Để tính được thu nhập trung bình cho 3 tháng gần đây nhất ta sẽ sử dụng hàm AVERAGE.

- Để vùng dữ liệu (tham chiếu) được thay đổi ta cần sữ dụng hàm OFFSET để thiết lập lại cho vùng dữ liệu

Bước 1: Tại ô hiển thị kết quả gõ hàm =AVERAGE(

Bước 2: Nhập tiếp hàm OFFSET(

Bước 3: Vùng tham chiếu reference là ô tiêu đề (B3)

Bước 4: Rows (số hàng lên xuống bắt đầu từ vị trí B3). Nếu lên ta nhập số âm nếu xuống ta nhập số dương. Vì ta tính dữ liệu trong hàng nên thông số này không dịch chuyển và ta để =0.

Bước 5: Cols (số cột qua trái qua phải từ vị trí B3). Nếu ta nhập số âm cột sẽ dịch về phía bên trái còn nhập số dương cột sẽ dịch qua bên phải. Vì vị trí ta muốn nhập là 3 cột cuối cùng do đó ta dùng hàm COUNTA để đếm số ô có chứa chữ (kí tự) ta sẽ chọn các ô từ (C3:N3) sau đó trừ đi 2. Tại sao ta lại trừ đi 2 vì vị trí ta muốn lấy là trước 2 ô cuối cùng dó đó ta phải trừ đi 2. Nếu bạn tính tổng 5 tháng thì bạn phải trừ đi 4.

Bước 6: Height (chiều cao tính bằng số hàng). Số hàng ta cần tính trong ví dụ này là 1 hàng do đó thông số này ta điền là 1

Bước 7: Width (độ rộng tính bằng số cột). Số cột ở đây ta cần tính là 3 cột (có chứa giá trị cuối cùng) do đó thông số này ta nhập 3.

Đóng ngoặc hàm OFFSET và Hàm SUM ta được công thức hoàn chỉnh

IV - Ví dụ 3

Sử dụng kết hợp hàm OFFSET để tính toán tổng doanh thu cho 2 tháng liên tiếp tính từ tháng đang chọn (ta sẽ sử dụng Data Validation list để tạo danh sách chọn)

Nếu bạn nào chưa biết cách tạo Data Validation List có thể tham khảo bài viết này để tạo Datalist

Sau khi tạo xong data list chúng ta cùng bắt tay vào phân tích bài toán.

- Theo như yêu cầu đề bài ta sẽ tính tổng thu nhập cho 2 tháng liên tiếp bắt đầu từ tháng bạn đang chọn. Giả sử ta chọn tháng 3 thì kết quả khi đó là tổng của tháng 3 và tháng 4 và = 95

- Để tính được tổng thu nhập cho 2 tháng ta sẽ sử dụng hàm SUM.

- Để tìm được vị trí (rows) ta sẽ sử dụng hàm MATCH để tìm vị trí

Bước 1: Tại ô hiển thị kết quả (E4) gõ hàm =SUM(

Bước 2: Nhập tiếp hàm OFFSET(

Bước 3: Vùng tham chiếu reference là ô tiêu đề (C2)

Bước 4: Rows. Vì vị trí ta muốn chọn được ở đây phụ thuộc vào số liệu ở ô E3. Do đó ta dùng hàm MATCH để dò vị trí của E3 nằm ở đâu trong các tháng từ tháng 1 tới tháng 12 (B3:B14)

Bước 5: Cols (số cột qua trái hoặc phải từ vị trí C2). Nếu dịch qua trái ta dùng số âm, dịch qua phải ta dùng số dương. Vì cột ta tính chính là cột C lên ta không dịch do đó thông số này ta để là 0.

Bước 6: Height (chiều cao tính bằng số hàng). Số hàng ta cần tính trong ví dụ này là 2 tháng do đó thông số này ta điền là 2.

Bước 7: Width (độ rộng tính bằng số cột). Số cột ở đây ta cần tính chỉ có cột C do đó thông số này ta nhập là 1.

Đóng ngoặc hàm OFFSET và Hàm SUM ta được công thức hoàn chỉnh

Cảm ơn bạn bạn đã ghé thăm Website. gatinhoc.com chúc các bạn thành công!