Cách dùng hàm OFFSET trong Excel có thể bạn chưa biết

Bạn đã biết hàm OFFSET trong Microsoft Excel có thể giúp bạn làm nhiều việc khác, không chỉ riêng tham chiếu ô chưa? Dưới đây là những cách dùng OFFSET trong Excel độc đáo có thể bạn chưa biết.

Hàm OFFSET của Microsoft Excel là một công cụ linh hoạt và mạnh mẽ. Do tính phức tạp và số lượng các hàm tương tự nhau, đáng tiếc, hàm Excel này chưa được dùng đúng mức.

Hàm tương đối đơn giản này có thể giúp người dùng tiết kiệm thời gian và công sức. Nó đơn giản hóa việc lấy các điểm dữ liệu chính xác từ một dataset chính được sắp xếp tốt. Nó cũng cho phép tạo đơn giản các báo cáo chi tiết, trực quan hóa tương tác và bảng điều khiển phức tạp.

Tạo dataset tùy biến kích thước

Một trong số cách dùng phổ biến nhất của hàm OFFSET trong Excel là thu thập các bộ dữ liệu có thể thay đổi kích thước. Với một tập dữ liệu lớn, người ta có thể chỉ muốn xem xét một phần nhỏ dữ liệu tại thời điểm bất kỳ.

Ví dụ, cung cấp một báo cáo tự động tổng doanh số bán hàng của nhân viên, bạn cần lấy tổng số cho một hoặc nhiều tuần nhất định.

Viết một hàm SUM khác cho từng tổng số có thể khá dễ dàng. Tuy nhiên, dùng hàm OFFSET sẽ dễ dùng và tái sử dụng nhanh hơn.

Đầu tiên, nhập ngày tháng bạn muốn bắt đầu vào ô B6. Sau đó, nhập số ngày muốn chạy báo cáo vào B7. Tiếp theo, dùng hàm SUM và AVERAGE đơn giản để lấy nhiều dữ liệu hơn cho từng nhân viên theo thời gian lựa chọn:

=SUM(OFFSET(B2, 0, B6 - 1, 1, B7))

=AVERAGE(OFFSET(B2, 0, B6 - 1, 1, B7)

Điều chỉnh ngày tháng bắt đầu hoặc thời gian đơn giản như chỉnh sửa một ô, mà bạn có thể kéo các nhóm dữ liệu thay đổi kích thước dễ hơn nhiều.

Cho phép tạo báo cáo tùy chỉnh

Dùng phương pháp tương tự như trên để lập một báo cáo hoàn chỉnh bằng việc tùy chỉnh biến khá dễ. Bạn có thể tạo đối số đầu tiên trong hàm OFFSET tham chiếu sheet khác. Việc này cho phép bạn tách riêng mục nhập dữ liệu và báo cáo.

Hành động trên cũng giúp bạn có thể kéo các phần dữ liệu đã chọn từ trang tính này sang trang tính khác. Ngoài ra, nó cho phép bạn chỉ hiển thị thông tin liên quan đến báo cáo hiện tại. Việc thiết lập một báo cáo hiển thị tổng số hàng ngày bằng cách sử dụng cùng một dữ liệu từ trước khá đơn giản. Bạn có thể làm điều này mà không cần lấy dữ liệu cho những ngày không liên quan.

Hàm offset sau được đặt ở ô B5 sẽ lấy số ngày được chỉ định ở B2. Nó bắt đầu vào tuần được chọn ở B1 và chỉ hiện dữ liệu cho giai đoạn liên quan:

=OFFSET(Sheet1!B2, 0, (B1 - 1) * 7, 1, B2 * 7)

Có thể hạn chế các mục trong B1 và B2, đồng thời, tạo danh sách thả xuống trong Excel với số tuần có sẵn bằng cách sử dụng xác thực dữ liệu.

Phân trang bảng tính

Hàm OFFSET cũng có thể phân trang bảng tính đơn giản, một nhiệm vụ thường được kết hợp với việc dùng tab Excel Developer. Điều này có thể khiến công việc xử lý nhóm dữ liệu lớn đơn giản, hiệu quả hơn.

Khi làm việc với bảng tính chứa tới 30.000 hàng dữ liệu, thật khó xem qua toàn bộ dữ liệu cùng lúc. Tạo menu thả xuống chấp nhận một phạm vi số từ 1 tới 300, cho phép bạn nhanh chóng tách dữ liệu theo trang với 100 kết quả mỗi trang.

Hàm OFFSET luôn có điểm neo được đặt ở ô dữ liệu trên cùng. Tuy nhiên, đối số thứ hai là offset theo chiều dọc. Giá trị này bằng số trang đã chọn trừ đi 100. Offset theo chiều ngang, đối số thứ ba có thể đặt là 0.

Đối số thứ 4 được đặt ở 100, đối số thứ 5 là số cột trong dataset. Phương pháp này khiến việc chia tách & quản lý bảng dữ liệu cực kỳ dễ dàng.

Dưới đây là một ví dụ về dữ liệu được phân trang. Nó bao gồm 500 mẫu thông tin được ghi lại từ một bộ cảm biến trong một trang tính và một báo cáo được đánh số trang trên trang tính kia.

Báo cáo dùng xác thực dữ liệu trong ô B1 để tạo menu thả xuống với các trang bên trong. Biểu thức sau ở ô A3 tạo phần nội dung của báo cáo:

=OFFSET(Sheet1!A2, (B1 - 1) * 100, 0, 100, 21)

Trên đây là một số cách dùng hàm offset trong Excel ít người biết nhưng chúng thật sự hữu ích, nhất là khi bạn cần xử lý số lượng lớn dữ liệu.

Hi vọng bài viết hữu ích với các bạn.