Chào các bạn, hôm nay mình muốn chia sẻ một mẹo nhỏ mà mình hay dùng để xử lý các mảng dữ liệu có thể thay đổi kích thước trong Excel, đặc biệt là khi kết hợp với các hàm khác.
Đôi khi chúng ta cần tham chiếu đến một vùng dữ liệu mà số hàng hoặc số cột có thể thay đổi tùy thuộc vào các yếu tố khác. Ví dụ, bạn có một danh sách sản phẩm và muốn tính tổng doanh thu cho 5 sản phẩm đầu tiên, nhưng con số '5' này lại được nhập ở một ô khác và có thể thay đổi.
Trong trường hợp này, sự kết hợp giữa INDIRECT và OFFSET thực sự hữu ích.
Giả sử bạn có dữ liệu bán hàng từ ô A1 trở xuống. Bạn muốn tính tổng doanh thu của N sản phẩm đầu tiên, với N được nhập ở ô C1.
Công thức có thể trông như sau:
=SUM(OFFSET(A1, 0, 0, C1, 1))Giải thích:
OFFSET(A1, 0, 0, C1, 1): Hàm này bắt đầu từ ôA1(tham số đầu tiên và thứ hai là 0, 0 nghĩa là không dịch chuyển hàng/cột). Nó tạo ra một vùng tham chiếu cóC1hàng (số lượng sản phẩm) và 1 cột (cột doanh thu).SUM(...): Hàm SUM sẽ tính tổng các giá trị trong vùng tham chiếu động được tạo ra bởi OFFSET.
Nếu bạn muốn làm cho nó mạnh mẽ hơn nữa bằng cách sử dụng tên vùng được định nghĩa (ví dụ: tên vùng là 'DanhSachSP' cho cột A), bạn có thể dùng INDIRECT:
=SUM(OFFSET(INDIRECT(