Hôm nay tôi muốn giới thiệu với bạn hàm OFFSET của Excel thông qua 3 ví dụ thực tế.
Trước tiên, tôi sẽ mô tả cú pháp công thức, sau đó nói về cách dùng hàm OFFSET để giải quyết các vấn đề thực tế.
Giới Thiệu
Hàm OFFSET trả về tham chiếu đến một ô (gọi là ô đích) hoặc một vùng (vùng đích) cách một số hàng và cột xác định từ một ô tham chiếu (reference cell) hoặc vùng tham chiếu (reference range).
Hình dưới minh họa cách dùng OFFSET để trả về tham chiếu ô (bên trái) hoặc vùng (bên phải).
Ô được tô màu xanh lá là ô đích, các ô màu vàng tạo thành vùng đích.
Các ô màu xanh dương là ô tham chiếu.
Hình 1
Cú Pháp Của Hàm OFFSET Trong Excel
Cú pháp: OFFSET (reference, rows, cols, [height], [width])
| Reference | Bắt buộc. Ô hoặc vùng ô làm điểm bắt đầu offset. Các ô phải liền kề nhau nếu chỉ định một vùng. |
| Rows | Bắt buộc. Số hàng lên/xuống từ ô tham chiếu hoặc ô góc trên bên trái của vùng tham chiếu. Rows có thể dương hoặc âm. Xem bên trái Hình 1: nếu đổi thành OFFSET(C3, -1, -1) ô đích sẽ là B2 (lên 1 hàng so với C3). |
| Cols | Bắt buộc. Số cột trái/phải từ ô tham chiếu hoặc ô góc trên bên trái vùng tham chiếu. Giá trị Cols cũng có thể dương/âm. Nếu đặt B4 làm ô tham chiếu, C3 làm ô đích, công thức: OFFSET(B4, -1, 1). Cols dương vì C3 ở bên phải B4 một cột. |
| Height | Tùy chọn. Chỉ dùng khi đích là vùng. Cho biết vùng đích bao gồm bao nhiêu hàng. Height phải là số dương. Bên phải Hình 1: vùng đích có 2 hàng → Height = 2. |
| Width | Tùy chọn. Chỉ dùng khi đích là vùng (bên phải Hình 1). Chỉ số cột vùng đích chứa. Width phải là số dương. |
Bây giờ tôi sẽ chỉ cách dùng OFFSET giải quyết vấn đề thực tế.
Trường Hợp 1: Tra Cứu Phải Sang Trái (Right-to-Left) Bằng Kết Hợp OFFSET Và MATCH
Ai cũng biết VLOOKUP chỉ tra cứu trái sang phải. Giá trị tra cứu phải nằm ở cột đầu của table_array. Muốn thêm cột tra cứu mới hoặc đổi cột tra cứu thì phải dịch chuyển toàn bộ bảng hoặc cấu trúc lại dữ liệu.
Nhưng kết hợp OFFSET với hàm MATCH có thể khắc phục hạn chế này.
Hàm MATCH tìm một mục trong vùng ô và trả về vị trí tương đối của mục đó trong vùng.
Ví dụ vùng B3:B8 (Hình 2.1 - doanh thu các quốc gia theo năm). Công thức =MATCH("USA", B3:B8, 0) trả về 1 vì USA là mục đầu tiên (xem ô B10, C10).
Với vùng C2:F2, công thức =MATCH(2015, C2:F2, 0) trả về 3 vì 2015 là mục thứ 3 (ô B11, C11).
Quay lại hàm OFFSET. Nếu đặt ô B2 làm tham chiếu, ô E3 làm đích, công thức OFFSET như nào? E3 cách B2 1 hàng xuống và 3 cột phải. Công thức: =OFFSET(B2, 1, 3). Chú ý các số màu đỏ: chúng khớp với kết quả MATCH.
Đó là câu trả lời cho việc kết hợp OFFSET với MATCH: Hàm MATCH đóng vai trò là đối số thứ 2 hoặc thứ 3 của hàm OFFSET (xem ô C13).
Ô C14 demo dùng VLOOKUP lấy cùng dữ liệu. Phải biết doanh thu 2015 ở cột 4 của table_array B2:F8 trước khi viết VLOOKUP. Tức là phải nắm rõ cấu trúc dữ liệu. Đây là hạn chế khác của VLOOKUP. Nhưng dùng MATCH làm đối số OFFSET thì không cần biết chỉ số cột. Rất hữu ích khi có nhiều cột.
Hình 2.1
Xem ví dụ phức tạp hơn.
Giả sử bảng chứa Tên công ty, Tên liên hệ, Email cho nhiều công ty. Muốn lấy tên công ty từ tên liên hệ biết, hoặc lấy tên liên hệ từ email biết. Làm sao?
Xem Hình 2.2, vùng B5:E8 chứa thông tin công ty. Nhập đầu vào ở ô C2 và B3, nhờ công thức trong ô vuông đỏ, có thể lấy tên công ty từ tên liên hệ. Vùng D2:E4 demo lấy tên liên hệ từ email.
Tóm lại, hai ví dụ chứng minh ta có thể tra cứu phải sang trái và giá trị tra cứu không cần nằm ở cột cuối. Bất kỳ cột nào trong table_array đều có thể chứa giá trị tra cứu.
Hình 2.2
Trường Hợp 2: Tự Động Hóa Tính Toán Kết Hợp OFFSET Và COUNT
Trước khi nói tự động hóa tính toán khi thêm số mới vào cột, hãy xem cách tự động trả về số cuối cùng trong cột.
Xem hình dưới: dữ liệu nhập từ Nhân sự. Muốn lấy số cuối cùng ở cột B, công thức OFFSET: =OFFSET(C2, 9, 0).
Từ công thức thấy 9 là số then chốt. Miễn là tự động trả về số này, ta tự động xác định số cuối cột.
9 chính là số ô chứa số trong cột C.
Nếu quen hàm COUNT, biết nó đếm số ô chứa số trong vùng. Ví dụ =COUNT(C3:C11) đếm ô có số từ C3 đến C11.
Muốn biết số lượng số trong cả cột, dùng tham chiếu C:C (toàn bộ cột C).
Xem ô G4, H4: =COUNT(C:C) trả về đúng 9.
Thay 9 bằng COUNT(C:C) trong OFFSET → công thức mới: =OFFSET(C2, COUNT(C:C), 0) (ô H5). Kết quả trả về 87000 - chính là số cuối cột C.
Tiếp tục tự động hóa tính tổng. Muốn tổng tất cả số trong cột C. Công thức dùng SUM kết hợp OFFSET: =SUM(OFFSET(C2, 1, 0, 9, 1)).
9 là tổng số hàng vùng C3:C11 và cũng là số ô chứa số trong cột C.
Viết lại: =SUM(OFFSET(C2, 1, 0, COUNT(C:C), 1)).
Xem ô G10, H10: tổng lương 9 nhân viên = $521,700.
Thử nhập thêm $34,000 vào ô C12, cả ô G5 và G10 tự cập nhật thành $34,000 và $555,700.
Đây là tự động hóa: không phải cập nhật công thức ở G5 hay G10.
Lưu ý khi dùng COUNT: nó chỉ đếm ô chứa số. Ví dụ =COUNT(B:B) trả về 0 thay vì 9 vì cột B không có ô nào chứa số (ô G3, H3).
Cột D có 10 ô chứa số, COUNT(D:D) cũng trả về 10. Nhưng nếu lấy số cuối cột D như làm cho cột C, kết quả ra 0 (ô G8, H8). Sai vì ô D13 cách D2 là 11 hàng chứ không phải 10. Công thức =OFFSET(D2, COUNT(D:D) + 1, 0) ở ô G7 chứng minh điều này.
Tóm lại: các số phải liền kề nhau mới dùng COUNT kết hợp OFFSET để tự động hóa tính toán được.
Hình 3
Trường Hợp 3: Dùng OFFSET Tạo Vùng Động (Dynamic Range)
Giả sử muốn vẽ biểu đồ doanh số đơn vị bán hàng hàng tháng của công ty. Hình 4.1 cho thấy dữ liệu hiện tại và biểu đồ dựa trên dữ liệu đó.
Mỗi tháng, doanh số tháng mới sẽ thêm vào dưới số cuối cùng cột C. Có cách dễ dàng cập nhật biểu đồ tự động không?
Chìa khóa: dùng OFFSET tạo tên vùng động (dynamic range names) cho cột Units Sold. Vùng động sẽ tự động bao gồm dữ liệu mới khi nhập.
Hình 4.1
Để tạo vùng động: tab Formulas → chọn Name Manager hoặc Define Name.
Hộp thoại New Name hiện ra nếu bấm Define Name. Nếu chọn Name Manager, cũng phải bấm New để hiện hộp thoại New Name.
Hình 4.2
Ô Name: nhập tên vùng động. Ô Refers to: nhập công thức OFFSET: =OFFSET(Figure4!$C$2, 1, 0, COUNT(!$C:$C), 1) tạo vùng động dựa trên giá trị Units Sold ở cột C.
Mặc định tên áp dụng cho toàn bộ workbook và phải duy nhất. Nhưng ta muốn giới hạn phạm vi (scope) cho một sheet cụ thể. Chọn Figure4 ở ô Scope:. Bấm OK, vùng động được tạo. Sẽ tự động bao gồm dữ liệu mới khi nhập.
Chuột phải vào biểu đồ → chọn Select Data.
Hình 4.3
Trong hộp thoại Select Data Source, chọn Series1 → Edit.
Hình 4.4
Nhập =Figure4!Units như Hình 4.5.
Hình 4.5
Thử nhập 11 vào ô C13. Biểu đồ tự cập nhật, giá trị 11 đã được thêm vào.
Biểu đồ sẽ tự thay đổi khi dữ liệu mới được thêm.
Hình 4.6
Xem Thêm…
- Hàm Offset(…) Trong Excel Với Ví Dụ
Tải File Làm Việc
Tải file thực hành từ link dưới.
Excel-Offset-Function.rar