6 cách sử dụng không phổ biến của hàm OFFSET trong Excel

Hàm OFFSET của Microsoft Excel là một công cụ rất mạnh mẽ và linh hoạt. Tuy nhiên, do tính phức tạp và số lượng các chức năng tương tự, Đáng tiếc là nó thường không được sử dụng đúng mức.

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

1. Tạo bộ dữ liệu có kích thước thay đổi

Một trong những cách sử 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ó kích thước thay đổi. 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 bất kỳ thời điểm nào.

Ví dụ: đưa ra một báo cáo tự động về tổng doanh số bán hàng cho nhân viên hàng ngày, bạn có thể 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 mỗi tổng số có thể được thực hiện khá dễ dàng. Tuy nhiên, một chức năng OFFSET có thể nhanh hơn nhiều để dễ sử dụng và tái sử dụng.

Đầu tiên, nhập ngày trong tháng mà chúng ta muốn bắt đầu vào ô B6. Sau đó, chúng tôi nhập bao nhiêu ngày chúng tôi muốn chạy báo cáo vào B7. Sau đó, chúng ta có thể sử dụng hàm SUM và hàm AVERAGE đơn giản để lấy thêm dữ liệu về từng 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 khoảng thời gian cũng đơn giản như điều chỉnh một ô duy nhất, điều này có thể giúp việc kéo các tập dữ liệu có kích thước thay đổi dễ dàng hơn nhiều.

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

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

Điều này sẽ giúp bạn có thể kéo các lát 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 của mình. 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 bù sau được đặt trong ô B5 sẽ lấy số ngày được chỉ định trong B2. Nó bắt đầu vào tuần được chỉ định trong B1, chỉ hiển thị dữ liệu cho khoảng thời gian có 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 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.

Hàm OFFSET cũng có thể đạt được phân trang bảng tính đơn giản, một tác vụ thường được liên kết với việc sử dụng tab Nhà phát triển Excel. Điều này có thể giúp 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 rất nhiều.

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

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

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

Dưới đây là một ví dụ về 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 bộ cảm biến trên 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 sử dụng xác thực dữ liệu trong ô B1 để tạo danh sách thả xuống chứa các trang trong đó. Biểu thức sau trong ô A3 tạo phần thân của báo cáo:

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

4. Cắt dữ liệu cho đồ thị

Chúng ta có thể thêm biểu đồ vào báo cáo bán hàng mà chúng ta đã tạo trước khi 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 trình đơn thả xuống ở đầu trang tính.

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

Kết hợp các biểu đồ và đồ thị với chức năng offset giúp 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 rất nhiều. Nó cũng cho phép tạo các bảng thông tin đơn giản, trong Excel.

5. Kéo thông tin theo ngày tháng

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

Sử dụng kết hợp COUNTIF và OFFSET, việc lấy dữ liệu từ một khoảng thời gian hoặc ngày khá đơn giản. Sử dụng một ô duy nhất để biểu thị một ngày bắt đầu. Tiếp theo, sử dụng một ô khác để biểu thị khoảng thời gian bạn muốn lấy từ đó.

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ị bằng cách 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 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 tôi có một tập dữ liệu chứa các mục trong vài năm. Chúng ta có thể sử dụng ngày đã 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) 

Điều này bổ sung tiện ích đáng kể cho báo cáo, cho phép bạn lọc các giá trị xuất hiện trước hoặc sau một ngày nhất định. Thay vào đó, nếu bạn sử dụng hàm COUNTIFS, bạn thậm chí có thể thêm ngày kết thúc, giúp người dùng cuối có nhiều khả năng thay đổ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òn phục vụ tốt cho việc kéo các lát cắt cụ thể của tất cả các loại dữ liệu được sắp xếp hợp lý bằng Hàm OFFSET của Excel. Có thể đặt đối số chiều cao và chiều rộng của hàm OFFSET bằng COUNTIF. Ngoài ra, bạn có thể dễ dàng sửa đổi bất kỳ dữ liệu số nào có giá trị có thể điều chỉnh từ một nơi nào đó trên trang tính của mình.

Nếu bạn có một danh sách các giao dịch trong một ngày được sắp xếp theo số lượng giao dịch, bạn có thể sử dụng kỹ thuật này để kéo mọi giao dịch vượt quá 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 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 cuối cùng, COUNTIF(B2:B22, “<" & H3). Mục đích của đối số này là chỉ định số lượng hàng cần kéo. Để tránh các lỗi tiềm ẩn trong dữ liệu được kéo, điều này giới hạn số lượng hàng chỉ ở số phù hợp với yêu cầu.

Một cân nhắc 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ó sẽ chỉ hoạt động trong trường hợp dữ liệu bạn đang làm việc được sắp xếp hợp lý. Dữ liệu không có thứ tự sẽ không trả về kết quả mà bạn đang tìm kiếm. Rất may, Excel cũng bao gồm một bộ công cụ sắp xếp tự động để sắp xếp dữ liệu của bạn theo 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 các sản phẩm khác nhau dựa trên giá cho đến các hoạt động chỉ kéo chạy trong một số lần nhất định. Khả năng kiểm soát lượng dữ liệu được kéo chính xác mang lại khả năng kiểm soát chi tiết đối với mẫu dữ liệu chính xác mà bạn lấy từ bảng tính của mình. Điều này cho phép tạo nhiều báo cáo chi tiết, tương tác.

Đây là chức năng OFFSET thực sự hữu ích cho

Cuối cùng, chức năng 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 giúp tiết kiệm thời gian và cho phép người dùng thực hiện nhiều thao tác hơn với ít chức năng hơn.

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