Công thức Excel: Tổng phạm vi với INDEX
Công thức Excel: Tổng phạm vi với INDEX

Công thức chung
=SUM(INDEX(data,0,column))
Tóm lược

Để tính tổng tất cả các giá trị trong một cột hoặc hàng, bạn có thể sử dụng hàm INDEX để truy xuất các giá trị và hàm SUM để trả về tổng. Kỹ thuật này hữu ích trong các trường hợp mà hàng hoặc cột được tổng hợp là động và các thay đổi dựa trên đầu vào của người dùng. Trong ví dụ được hiển thị, công thức trong H6 là:

=SUM(INDEX(data,0,H5))

nơi “dữ liệu” là phạm vi được đặt tên C5: E9.

Giải trình

Hàm INDEX tra cứu các giá trị theo vị trí. Ví dụ: công thức này truy xuất giá trị cho doanh số bán hàng của Acme trong tháng 1:

=INDEX(data,1,1)

Hàm INDEX có một hành vi đặc biệt và không rõ ràng: khi đối số số hàng được cung cấp dưới dạng 0 hoặc null, hàm INDEX truy xuất tất cả các giá trị trong cột được tham chiếu bởi đối số số cột. Tương tự như vậy, khi số cột được cung cấp dưới dạng 0 hoặc không có gì, INDEX truy xuất tất cả các giá trị trong hàng được tham chiếu bởi đối số số hàng:

=INDEX(data,0,1) // all of column 1
=INDEX(data,1,0) // all of row 1

Trong ví dụ về công thức, chúng tôi cung cấp dải ô được đặt tên là “dữ liệu” cho mảng và chúng tôi chọn số cột từ H2. Đối với số hàng, chúng tôi cố ý cung cấp số 0. Điều này khiến INDEX truy xuất tất cả các giá trị trong cột 2 của “dữ liệu”. Công thức được giải như sau:

=SUM(INDEX(data,0,2))
=SUM({9700;2700;23700;16450;17500})
=70050

Các tính toán khác

Bạn có thể sử dụng phương pháp tương tự cho các phép tính khác bằng cách thay thế SUM bằng AVERAGE, MAX, MIN, v.v. Ví dụ: để nhận giá trị trung bình trong tháng thứ ba, bạn có thể sử dụng:

=AVERAGE(INDEX(data,0,3))

Nhiều hơn một cột hoặc hàng

Để xử lý trả về nhiều hàng hoặc cột với INDEX, hãy xem cách tiếp cận được mô tả ở đây thành “dereference” INDEX.

https://exceljet.net/formula/sum-range-with-index