Excel từ lâu nay luôn là một trong những công cụ “quốc dân” cho việc tính toán và phân tích dữ liệu một cách tối ưu nhất. Vài nếu như lâu nay bạn sử dụng Excel để tính toán dữ liệu nhưng chưa biết vận dùng hàm OFFSET một cách hiệu quả, chuyên nghiệp.
Vậy thì bạn hãy đọc ngay bài viết dưới đây, Divine Shop sẽ giúp bạn “nằm lòng” cách sử dụng hàm OFFSET tiết kiệm được rất nhiều thời gian, công sức trong việc tính toán trên Excel
Hàm OFFSET là gì?
Hàm OFFSET là một hàm được dùng để tính toán các dữ liệu trên một ô hoặc một dãy dựa vào vùng tham chiếu có sẵn. Cú pháp: =OFFSET(reference, rows, cols, [height], [width])
Trong đó:
- Reference: là một ô hoặc một dãy dùng để làm vùng tham chiếu ban đầu.
- Rows: Số hàng mà công thức di chuyển của vùng được tham chiếu ban đầu. Nếu như số hàng là số dương thì công thức di chuyển xuống phía dưới vùng tham chiếu và ngược lại.
- Cols: Số cột mà công thức di chuyển trong vùng tham chiếu ban đầu. Nếu số cột là số dương thì dữ liệu sẽ di chuyển bên phải vùng tham chiếu và ngược lại.
- Height: là chiều cao – được tính bằng số hàng bạn cần tham chiếu về.
- Width: là chiều rộng – được tính là số cột mà bạn cần tham chiếu về.
Lưu ý: Height, Width là hai điều kiện tự chọn, không bắt buộc trong cú pháp hàm OFFSET và hai đối số này không dùng số âm.
Hàm OFFSET thường được dùng trong những trường hợp nào?
Chắc hẳn, bạn sẽ thắc mắc làm sao để vận dụng hàm OFFSET một cách tối ưu hiệu quả nhất phải không nào? Tiếp theo, Divine Shop sẽ hướng dẫn bạn một trong số cách cơ bản để vận dùng hàm OFFSET. Trong ví dụ bên dưới đây yêu cầu bạn hãy tìm số lượng còn lại của chiếc iPhone 11 Pro Max 512GB dựa trên dữ liệu tồn kho ban đầu.
Bước 1: Bạn hãy nhập công thức như sau: =OFFSET(B1;4;1)
Bước 2: Tiếp tục nhấn Enter, để xem kết quả.
Ngoài ra, hàm OFFSET còn được vận dụng nâng cao hơn với việc lồng ghép các hàm tính toán có điều kiện để giúp tối ưu các bước thực hiện. Bạn hãy tiếp tục theo dõi bên dưới nhé!
Cách kết hợp giữa hàm OFFSET và SUM, COUNTA
Hàm OFFSET kết hợp với SUM
Ở ví dụ tiếp theo, Divine Shop sẽ kết hợp cùng hàm SUM để tính tổng dựa trên dữ liệu tham chiếu. Trong trường hợp này, yêu cầu bạn hãy tìm ra tổng số lượng bán được của sản phẩm AirPods Pro trong hai tháng.
Bước 1: Bạn hãy nhập vào với công thức như sau: =SUM(OFFSET(B1;5;1;1;2)). Ở đây bạn sẽ dùng hàm SUM để tính tổng và lồng ghép OFFSET để tham chiếu từ ô sản phẩm xuống 5 dòng và lấy giá trị từ cột đầu tiên với phạm vi là 1 hàng và 2 cột.
Bước 2: Sau đó nhấn Enter, bạn sẽ xem được kết quả.
Hàm OFFSET kết hợp với COUNTA
Tương tư, khi chúng ta thực hiện kết hợp OFFSET và hàm COUNTA có nghĩa bạn đang thực hiện tạo một danh sách động với hàm OFFSET. Hãy theo dõi ví dụ dưới đây để tạo danh sách động các sản phẩm.
Bước 1: Bạn sử dụng kết hợp công thức như sau: Chúng ta sẽ dùng hàm OFFSET bắt đầu tham chiếu ở ô B2 – không thay đổi về hàng và cột, tiếp tục sau đó cứ mỗi tham chiếu sẽ xuống 1 dòng và không đổi về cột. Lúc này việc sử dụng kết hợp COUNTA để đếm những ô có chứa nội dung và không phải là ô trống.
Cú pháp: =OFFSET(B2;0;0;COUNTA(B2:B8))
Bước 2: Sau đó bạn nhấn Enter, sẽ được hiển thị kết quả.
Bước 3: Tiếp theo, để thực hiện tạo danh sách động bạn hãy truy cập vào Data > Data Validation.
Bước 4: Tại hộp thoại Data Validation, bạn hãy chọn Settings > Allows: List > Source: =OFFSET($B$2;0;0;COUNTA($B$2:$B$8)).
Bước 5: Cuối cùng, sau khi nhấn OK bạn sẽ xem được danh sách động đã tạo.
Tạm kết
Qua bài viết trên, Divine Shop đã hướng dẫn bạn cách sử dụng hàm OFFSET đơn giản, chi tiết và kết hợp với việc sử dụng lồng ghép với các hàm tính toán sẽ giúp cho bạn tối ưu được thời gian xử lý dữ liệu một cách hiệu quả hơn. Chúc các bạn thực hiện thành công!
