Công thức chung
=SUM(firstcell:INDEX(data,rows,cols))
Tóm lược
Để xác định một phạm vi dựa trên một giá trị trong một ô khác, bạn có thể sử dụng hàm INDEX. Trong ví dụ được hiển thị, công thức trong J7 là:
=SUM(C5:INDEX(data,J5,J6))
trong đó "dữ liệu" là phạm vi được đặt tên là B5: G9.
Giải trình
Công thức này dựa trên một hành vi cụ thể của INDEX - mặc dù có vẻ như INDEX trả về giá trị tại một vị trí cụ thể, nhưng nó thực sự trả về tham chiếu đến vị trí đó. Trong hầu hết các công thức, bạn sẽ không nhận thấy sự khác biệt - Excel chỉ đơn giản đánh giá tham chiếu và trả về giá trị. Công thức này sử dụng tính năng này để tạo phạm vi động dựa trên đầu vào trang tính.
Bên trong hàm sum, tham chiếu đầu tiên chỉ đơn giản là ô đầu tiên trong phạm vi bao gồm tất cả các ô có thể:
=SUM(C5:
Để lấy ô cuối cùng, chúng tôi sử dụng INDEX. Ở đây, chúng tôi cung cấp cho INDEX phạm vi được đặt tên là "dữ liệu", là phạm vi giá trị tối đa có thể có và cũng là các giá trị từ J5 (hàng) và J6 (cột). INDEX không trả về một dải ô, nó chỉ trả về một ô duy nhất tại vị trí đó, E9 trong ví dụ:
INDEX(data,J5,J6) // returns E9
Công thức ban đầu được rút gọn thành:
=SUM(C5:E9)
trả về 300, tổng của tất cả các giá trị trong C5: E9.
Công thức trong J8 gần như giống nhau, nhưng sử dụng AVERAGE thay vì SUM để tính giá trị trung bình. Khi người dùng thay đổi giá trị trong J5 hoặc J6, phạm vi được cập nhật và kết quả mới được trả về.
Thay thế bằng OFFSET
Bạn có thể xây dựng một công thức tương tự với hàm OFFSET, được hiển thị bên dưới:
=SUM(OFFSET(C5,0,0,J5,J6)) // sum =AVERAGE(OFFSET(C5,0,0,J5,J6)) // average
OFFSET được thiết kế để trả về một phạm vi, vì vậy các công thức có lẽ đơn giản hơn để hiểu. Tuy nhiên, OFFSET là một hàm dễ bay hơi và có thể gây ra các vấn đề về hiệu suất khi được sử dụng trong các trang tính lớn hơn, phức tạp hơn.
