Обща формула
=SUM(firstcell:INDEX(data,rows,cols))
Обобщение
За да определите диапазон въз основа на стойност в друга клетка, можете да използвате функцията INDEX. В показания пример формулата в J7 е:
=SUM(C5:INDEX(data,J5,J6))
където "данни" е наименуваният диапазон B5: G9.
Обяснение
Тази формула разчита на специфично поведение на INDEX - въпреки че изглежда, че INDEX връща стойността на определено местоположение, всъщност връща препратката към местоположението. В повечето формули няма да забележите разликата - Excel просто оценява референцията и връща стойността. Тази формула използва тази функция за конструиране на динамичен диапазон въз основа на въвеждането на работния лист.
Вътре в функцията за сумиране първата референция е просто първата клетка в диапазона, която покрива всички възможни клетки:
=SUM(C5:
За да получим последната клетка, използваме INDEX. Тук даваме на INDEX наименования диапазон "данни", който е максимално възможният диапазон от стойности, а също и стойностите от J5 (редове) и J6 (колони). INDEX не връща диапазон, той връща само една клетка на това място, E9 в примера:
INDEX(data,J5,J6) // returns E9
Оригиналната формула се свежда до:
=SUM(C5:E9)
което връща 300, сумата от всички стойности в C5: E9.
Формулата в J8 е почти същата, но използва СРЕДНА вместо SUM, за да изчисли средна стойност. Когато потребителят промени стойностите в J5 или J6, обхватът се актуализира и се връщат нови резултати.
Алтернатива с OFFSET
Можете да изградите подобни формули с функцията OFFSET, показана по-долу:
=SUM(OFFSET(C5,0,0,J5,J6)) // sum =AVERAGE(OFFSET(C5,0,0,J5,J6)) // average
OFFSET е проектиран да връща диапазон, така че формулите са може би по-лесни за разбиране. OFFSET обаче е променлива функция и може да причини проблеми с производителността, когато се използва в по-големи и по-сложни работни листове.
