Công thức chung
=SUM(INDEX(range,N(IF(1,(1,2,3)))))
Tóm lược
Để hàm INDEX trả về một mảng các mục cho một hàm khác, bạn có thể sử dụng một thủ thuật khó hiểu dựa trên hàm IF và N. Trong ví dụ được hiển thị, công thức trong E5 là:
=SUM(INDEX(data,N(IF(1,(1,2,3)))))
trong đó "dữ liệu" là phạm vi được đặt tên B5: B10.
Giải trình
Thật là khó một cách đáng ngạc nhiên khi hàm INDEX trả về nhiều hơn một giá trị cho một hàm khác. Để minh họa, công thức sau có thể được sử dụng để trả về ba mục đầu tiên trong phạm vi được đặt tên là "dữ liệu", khi được nhập dưới dạng công thức mảng nhiều ô.
(=INDEX(data,(1,2,3)))
Kết quả có thể được nhìn thấy trong phạm vi D10: F10, chính xác chứa 10, 15 và 20.
Tuy nhiên, nếu chúng ta bọc công thức trong hàm SUM:
=SUM(INDEX(data,(1,2,3)))
Kết quả cuối cùng là 10, trong khi nó phải là 45, ngay cả khi được nhập dưới dạng công thức mảng. Vấn đề là INDEX chỉ trả về mục đầu tiên trong mảng cho hàm SUM. Để buộc INDEX trả về nhiều mục thành SUM, bạn có thể bọc hằng số mảng trong các hàm N và IF như sau:
=SUM(INDEX(data,N(IF(1,(1,2,3)))))
trả về kết quả đúng là 45. Tương tự, công thức này:
=SUM(INDEX(data,N(IF(1,(1,3,5)))))
trả về đúng 60, tổng của 10, 20 và 30.
Kỹ thuật ít người biết đến này đôi khi được gọi là "dereferencing", bởi vì nó ngăn INDEX xử lý kết quả dưới dạng tham chiếu ô và sau đó loại bỏ tất cả trừ mục đầu tiên trong mảng. Thay vào đó, INDEX cung cấp một mảng đầy đủ các giá trị cho SUM trong. Jeff Weir có một lời giải thích tốt ở đây về stackoverflow.
Lưu ý với độc giả: Tôi không chắc chính xác tại sao điều này lại hiệu quả. Nếu bạn có thể cung cấp một lời giải thích rõ ràng, tôi sẽ cập nhật để đưa vào.
