Hướng Dẫn Sử Dụng Hàm OFFSET Trong Excel

1. Tạo Tập Dữ Liệu Có Kích Thước Thay Đổi

Một trong những ứng dụng phổ biến nhất của hàm OFFSET là lấy dữ liệu có kích thước thay đổi. Với một tập dữ liệu lớn, bạn có thể chỉ muốn xem một phần nhỏ của dữ liệu vào bất kỳ thời điểm nào.

Ví dụ, nếu bạn có báo cáo tổng doanh số hàng ngày của nhân viên, bạn có thể cần lấy tổng doanh số của một hoặc nhiều tuần cụ thể. Việc viết một hàm SUM khác nhau cho mỗi tổng số có thể dễ dàng, nhưng hàm OFFSET có thể nhanh hơn và dễ sử dụng hơn.

Đầu tiên, bạn nhập ngày bắt đầu vào ô B6. Sau đó, bạn nhập số ngày muốn chạy báo cáo vào ô B7. Sau đó, bạn có thể sử dụng hàm SUM và hàm AVERAGE để lấy thêm dữ liệu về mỗi nhân viên trong khoảng thời gian đã chọn:

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

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

Việc điều chỉnh ngày bắt đầu hoặc thời gian chỉ đơn giản là điều chỉnh một ô duy nhất, giúp việc lấy dữ liệu có kích thước thay đổi dễ dàng hơn.

Báo cáo doanh số đơn giản với ngày bắt đầu và số ngày để tính toán. Những điều này cho phép bảng tính tính toán tổng doanh số cho khoảng thời gian đã chọn và doanh số trung bình trong thời gian đó.

2. Tạo Báo Cáo Tùy Chỉnh

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

Việc này sẽ giúp bạn lấy các lát cắt dữ liệu từ một sheet sang một sheet khác và chỉ hiển thị thông tin liên quan đến báo cáo hiện tại của bạn. Việc thiết lập một báo cáo hiển thị tổng doanh số hàng ngày sử dụng cùng một dữ liệu trước đó rất đơ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 đây đặt trong ô B5 sẽ lấy số ngày được chỉ định trong ô B2, bắt đầu từ tuần được chỉ định trong ô B1, chỉ hiển thị dữ liệu cho khoảng thời gian liên quan:

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

Bạn có thể hạn chế các mục nhập trong ô B1 và B2 và 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.

3. Phân Trang Trong Bảng Tính

Hàm OFFSET cũng có thể thực hiện phân trang đơn giản trong bảng tính, một nhiệm vụ thường liên quan đến sử dụng tab Developer trong Excel. Điều này có thể làm cho việc làm việc với các tập dữ liệu lớn trở nên đơn giản và hiệu quả hơn.

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

Hàm OFFSET luôn có điểm neo đặt tại ô trên cùng bên trái của dữ liệu. Tuy nhiên, đối số thứ hai là độ lệch dọc, sẽ bằng số trang được chọn trừ đi một lần 100. Độ lệch ngang, đối số thứ ba, có thể được đặt là 0.

Đối số thứ tư được đặt là 100, và đối số thứ năm là số cột trong tập dữ liệu. Phương pháp này làm cho việc chia các khối dữ liệu cực kỳ lớn thành các phần dễ quản lý hơn.

Dưới đây là ví dụ về một tập dữ liệu được phân trang. Dữ liệu bao gồm 500 mẫu thông tin được ghi lại từ một tập hợp cảm biến trên một sheet, và một báo cáo phân trang trên sheet khác.

Báo cáo trong Excel với kết quả từ một loạt cảm biến được phân trang thành các trang với 100 kết quả.

Báo cáo sử dụng xác thực dữ liệu trong ô B1 để tạo danh sách thả xuống với các trang trong đó. Biểu thức sau đây trong ô A3 tạo nội dung của báo cáo:

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

4. Cắt Dữ Liệu Cho Biểu Đồ

Chúng ta có thể thêm biểu đồ vào báo cáo doanh số đã tạo trước đó bằng cách sử dụng hàm OFFSET. Biểu đồ có thể lấy thông tin biến đổi dựa trên dữ liệu được chọn trong hai danh sách thả xuống ở đầu sheet.

Khi thay đổi giá trị ở đầu sheet, biểu đồ sẽ tự động thay đổi để phản ánh các tuần đã chọn, cung cấp một tham chiếu trực quan nhanh chóng cho bất kỳ ai xem báo cáo.

Kết hợp biểu đồ và đồ thị với hàm OFFSET làm cho việc trực quan hóa dữ liệu phức tạp và thay đổi trở nên dễ dàng hơn. Nó cũng cho phép tạo các bảng điều khiển đơn giản trong Excel.

5. Lấy Thông Tin Theo Độ Lệch Ngày

Tìm kiếm thông tin cụ thể dựa trên dấu thời gian không phải là hiếm khi nói đến dữ liệu báo cáo hoặc dữ liệu được kéo từ các quy trình tự động theo thời gian. Khi xem xét một khoảng thời gian duy nhất, việc sử dụng các hàm điều kiện như IF hoặc IFS để chỉ lấy dữ liệu yêu cầu không khó. Nếu bạn cần lấy dữ liệu dựa trên một phạm vi ngày hoặc thời gian, rất dễ rơi vào một điều kiện lồng nhau lớn.

Sử dụng sự kết hợp của COUNTIF và OFFSET, việc lấy dữ liệu từ một phạm vi thời gian hoặc ngày rất đơn giản. Sử dụng một ô để đại diện cho ngày bắt đầu. Sau đó, sử dụng một ô khác để đại diện cho độ dài thời gian bạn muốn lấy.

Trong hàm OFFSET, chiều cao hoặc chiều rộng, tùy thuộc vào bố cục dữ liệu của bạn, có thể được đặt thành một giá trị sử dụng hàm COUNTIF trong Excel. Điều này cho phép bạn xác định có bao nhiêu mục nhập nằm trong phạm vi được yêu cầu và chỉ lấy dữ liệu liên quan.

Trong ví dụ này, chúng ta có một tập dữ liệu chứa các mục nhập qua nhiều năm. Chúng ta có thể sử dụng ngày được nhập trong ô B1 và hàm sau để chỉ lấy các mục nhập xảy ra sau ngày đó:

=OFFSET(Sheet1!U2, COUNTIF(Sheet1!V2:V501, "<" & B1), 0, 500 - COUNTIF(Sheet1!V2:V501, "<" & B1), 1)

Danh sách các giá trị được lọc theo một ngày được đặt ở trên chúng trong Excel.

Điều này thêm nhiều tiện ích cho báo cáo, cho phép bạn lọc các giá trị xảy ra trước hoặc sau một ngày nhất định. Nếu bạn sử dụng hàm COUNTIFS thay vì COUNTIF, bạn thậm chí có thể thêm một ngày kết thúc, cung cấp cho người dùng cuối nhiều biến đổi hơn.

6. Kết Hợp Với COUNTIF

Ngoài việc mở rộng phạm vi thời gian, hàm COUNTIF cũng phục vụ tốt cho việc lấy các lát cắt cụ thể của mọi loại dữ liệu được sắp xếp tốt với hàm OFFSET trong Excel. Bạn có thể đặt các đối số chiều cao và chiều rộng của hàm OFFSET bằng cách sử dụng COUNTIF. Ngoài ra, bạn có thể dễ dàng sửa đổi bất kỳ dữ liệu số nào với một giá trị có thể điều chỉnh từ đâu đó trên sheet của bạn.

Nếu bạn có một danh sách các giao dịch trong ngày được sắp xếp theo số tiền của các giao dịch, bạn có thể sử dụng kỹ thuật này để lấy mọi giao dịch trên một số tiền nhất định. Điều này có thể được thực hiện bằng cách sử dụng một hàm OFFSET đơn giản như sau:

=OFFSET(A2, COUNTIF(B2:B22, ">" & H3), 0, COUNTIF(B2:B22, ">" & H3), 4)

Một trong những điều quan trọng nhất cần lưu ý trong hàm này là đối số thứ hai đến thứ ba, COUNTIF(B2:B22, “>”

Một điều quan trọng khác đối với những người muốn sử dụng OFFSET theo cách này là nó chỉ hoạt động trong trường hợp dữ liệu bạn đang làm việc với được sắp xếp tốt. Dữ liệu không được sắp xếp sẽ không trả về kết quả bạn đang tìm kiếm. May mắn thay, Excel cũng bao gồm một bộ công cụ sắp xếp tự động để đưa dữ liệu của bạn vào thứ tự.

Bạn có thể sử dụng điều này để thực hiện các hành động từ việc cắt lát sản phẩm dựa trên giá đến việc chỉ lấy các hoạt động được thực hiện trên một số lần nhất định. Khả năng kiểm soát chính xác lượng dữ liệu được lấy cung cấp sự kiểm soát chi tiết về mẫu dữ liệu chính xác bạn lấy từ các bảng tính của mình. Điều này cho phép tạo ra nhiều báo cáo chi tiết, tương tác.

7. Ứng Dụng Thực Tế Của Hàm OFFSET

Cuối cùng, hàm OFFSET cho phép người dùng lấy thông tin từ bảng tính của họ với độ chính xác cao. Điều này tiết kiệm thời gian và cho phép người dùng thực hiện nhiều thao tác với ít hàm hơn.

Nó đơn giản hóa việc lấy dữ liệu chính xác bạn cần mà không cần phải sử dụng các điều kiện lồng nhau. Nó cũng giúp người dùng tiết kiệm thời gian và công sức trong việc tạo các báo cáo tùy chỉnh, bảng điều khiển và nhiều hơn nữa trong Excel.