Trong Excel hỗ trợ bạn rất nhiều hàm tính toán trong các lĩnh vực khác nhau như: toán học, thống kê, kế toán… Hàm Offset trong Excel là hàm trả về tham chiếu đến một vùng nào đó, được tính bằng 1 ô hay một dãy ô bắt đầu và số dòng hoặc số cột được chỉ định trước. Bạn có thể chỉ định rõ số hàng và số cột của vùng tham chiếu trả về. Bài viết dưới đây sẽ giới thiệu cho bạn về hàm Offset và ứng dụng của hàm Offset trong Excel.
Video hướng dẫn cách dùng hàm Offset, hết hợp hàm Offset và Match, ứng dụng trong Excel
Hàm Offset – Định nghĩa và công thức
Đây là một trong những hàm rất hay của Excel và được nhiều người sử dụng. Hàm Offset dùng đề tham chiếu đến một vùng nào đó, bắt đầu từ một ô hoặc một dãy ô, với một khoảng cách được chỉ định.
Công thức của hàm Offset:
=OFFSET(reference, rows, cols, height, width)
Trong đó:
- reference: là vùng tham chiếu làm cơ sở cho hàm (làm điểm xuất phát) để tạo vùng tham chiếu mới.
- rows: là số dòng bên trên hoặc bên dưới reference, tính từ ô đầu tiên (ô ở góc trên bên trái) của reference. Ví dụ nếu rows là 3, sẽ có 3 dòng trả về và nằm bên dưới reference. Khi rows là số dương thì các dòng trả về nằm bên dưới reference, khi rows là số âm thì các dòng trả về nằm bên trên reference.
- cols: là số cột bên trái hoặc bên phải reference, tính từ ô đầu tiên (ô ở góc trên bên trái) của reference. Ví dụ nếu cols là 4 sẽ có 4 cột trả về và nằm bên phải của reference. Khi cols là số dương thì các cột trả về nằm bên phải reference, khi cols là số âm thì các cột trả về nằm bên trái reference.
- height: là số dòng của vùng tham chiếu cần trả về. Height phải là số dương.
- width: là số cột của vùng tham chiếu cần trả về. Width phải là số dương.
Chú ý:
- Nếu rows và cols làm cho vùng tham chiếu vượt ra khỏi phạm vi của bảng tính thì hàm OFFSET() sẽ báo lỗi #REF!.
- Hàm OFFSET() không di chuyển hay làm thay đổi bất kỳ phần nào được chọn vì hàm chỉ trả về một tham chiếu. Các bạn có thể sử dụng hàm OFFSET() kết hợp với các hàm cần đến một đối số tham chiếu.
Ví dụ:
– Tại ô B7, trả về giá trị của ô D5 thông qua ô tham chiếu cơ sở B4, dùng công thức: =OFFSET(B4;1;2;1;1)
=>Kết quả:
– Kết hợp hàm OFFSET() với hàm SUM() để tính tổng vùng bôi vàng C3-E5, với vùng tham chiếu là bảng C4-E7 ta dùng công thức: =SUM(OFFSET(C4:E7;-1;0;3;3))
=> Kết quả thu được:
Ứng dụng của hàm Offset trong Excel
HÀM OFFSET VÀ HÀM SUM:
Ví dụ chúng ta đã thảo luận cách đây một phút thể hiện cách sử dụng OFFSET và SUM trong Excel đơn giản nhất. Bây giờ, chúng ta hãy nhìn vào những hàm này ở một góc độ khác và xem những gì chúng có thể làm.
VÍ DỤ 1. MỘT CÔNG THỨC HÀM SUM / OFFSET THAY ĐỔI:
Vấn đề nữa 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 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 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 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 bao gồm trong tổng số. Trên toàn bộ, có hai lựa chọn – hoặc cập nhật các dải trong công thức SUM mỗi lần thay đổi bằng tay hoặc bạn sẽ dùng hàm OFFSET làm điều này cho bạn.
Khi ô đầ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 SUM, bạn chỉ phải đưa ra các tham số cho hàm OFFSET, và sau đó nó sẽ tham chiếu cho đến ô cuối cùng của dải:
- Reference – ô chứa tổng số, trong ví dụ ở đây là ô B9.
- Rows – ô trên ô chứa tổng số và nằm bên phải, bắt buộc 1 số âm: -1.
- Cols – là 0 vì bạn không muốn thay đổi cột.
Từ đó, ta có mẫu công thức SUM / 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 trông như sau:
= SUM (B2: (OFFSET (B9, -1, 0)))
Và nó thực sự hoạt động tốt như trong hình được minh họa dưới đây:
VÍ DỤ 2. CÔNG THỨC OFFSET ĐỂ TỔNG HỢP N CÁC DÒNG CUỐI DANH SÁCH:
Trong ví dụ trên, giả sử bạn muốn biết 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 (không quan tâm đến liệu có bất kỳ hàng mới đượ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 Excel 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)))
Các thông tin sau đây có thể giúp bạn hiểu rõ các công thức tốt hơn:
- Reference – tiêu đề của cột có các giá trị mà bạn muốn tính tổng, trong ví dụ này là ô B1.
- Rows – để tính số hàng để tính offset, và bạn sử dụng thêm hàm COUNT hoặc COUNTA.
COUNTA trả về số ô trong cột B có chứa số, từ đó bạn trừ đi các N tháng cuối cùng (giá trị ở trong ô E1) và thêm 1.
Nếu bạn muốn chọn hàm COUNTA, thì khi đó bạn không cần phải thêm 1, vì hàm này tính tất cả các ô không chứa giá trị rỗng, và hàng tiêu đề có giá trị văn bản được thêm một ô thêm mới mà công thức cần đến. Xin lưu ý rằng công thức này sẽ chỉ hoạt động chính xác trên một cấu trúc bảng tính tương tự – và dòng tiêu đề tiếp theo là hàng có số. Đối với các cấu trúc bảng khác nhau, bạn có thể cần thực hiện một số điều chỉnh trong công thức OFFSET / COUNTA.
- Cols – số cột điều chỉnh là 0.
- Chiều cao – số hàng được tổng hợp được chỉ định trong E1.
- Chiều rộng – 1 cột.
SỬ DỤNG HÀM OFFSET VỚI AVERAGE, MAX, MIN
Theo cách tương tự như chúng tôi có thể tính tiền thưởng cho N tháng qua, bạn có thể nhận được giá trị trung bình cho N ngày, tuần hoặc năm cuối cùng cũng như tìm các giá trị tối đa hoặc tối thiểu. Sự khác biệt duy nhất giữa các công thức là tên của hàm đầu tiên:
= AVERAGE (OFFSET (B1, COUNT (B: B) -E1 + 1,0, E1,1))
= MAX (OFFSET (B1, COUNT (B: B) -E1 + 1,0, E1,1))
= MIN (OFFSET (B1, COUNT (B: B) -E1 + 1,0, E1,1))
Lợi ích chính mà các công thức này hơn hàm AVERAGE(B5: B8) hoặc MAX (B5: B8) là bạn sẽ không phải cập nhật công thức mỗi khi bảng gốc của bạn được cập nhật. Bạn sẽ không cần phải quan tâm rằng bao nhiêu dòng mới được thêm vào hoặc xóa trong bảng tính Excel của bạn, mà sẽ có các công thức OFFSET sẽ luôn luôn tham chiếu đến tận những ô cuối cùng (đã quy định trước giới hạn nhỏ nhất và lớn nhất) trong cột.
