Formuła ogólna
=SUM(firstcell:INDEX(data,rows,cols))
Podsumowanie
Aby zdefiniować zakres na podstawie wartości w innej komórce, możesz użyć funkcji INDEKS. W pokazanym przykładzie formuła w J7 to:
=SUM(C5:INDEX(data,J5,J6))
gdzie „dane” to nazwany zakres B5: G9.
Wyjaśnienie
Ta formuła opiera się na określonym zachowaniu INDEKSU - chociaż wydaje się, że INDEKS zwraca wartość w określonej lokalizacji, to w rzeczywistości zwraca odwołanie do tej lokalizacji. W większości formuł nie zauważysz różnicy - program Excel po prostu ocenia odwołanie i zwraca wartość. Ta formuła wykorzystuje tę funkcję do tworzenia zakresu dynamicznego na podstawie danych wejściowych z arkusza.
Wewnątrz funkcji sumy pierwsze odwołanie to po prostu pierwsza komórka w zakresie obejmującym wszystkie możliwe komórki:
=SUM(C5:
Aby uzyskać ostatnią komórkę, używamy INDEKSU. Tutaj dajemy INDEKSOWI nazwany zakres „dane”, czyli maksymalny możliwy zakres wartości, a także wartości z J5 (wiersze) i J6 (kolumny). INDEKS nie zwraca zakresu, zwraca tylko jedną komórkę w tej lokalizacji, E9 w przykładzie:
INDEX(data,J5,J6) // returns E9
Oryginalna formuła została zredukowana do:
=SUM(C5:E9)
co zwraca 300, czyli sumę wszystkich wartości w C5: E9.
Formuła w J8 jest prawie taka sama, ale używa ŚREDNIA zamiast SUMY do obliczenia średniej. Gdy użytkownik zmieni wartości w J5 lub J6, zakres jest aktualizowany i zwracane są nowe wyniki.
Alternatywa z OFFSETEM
Możesz zbudować podobne formuły za pomocą funkcji OFFSET, pokazanej poniżej:
=SUM(OFFSET(C5,0,0,J5,J6)) // sum =AVERAGE(OFFSET(C5,0,0,J5,J6)) // average
Funkcja OFFSET zwraca zakres, więc formuły mogą być łatwiejsze do zrozumienia. Jednak OFFSET jest funkcją ulotną i może powodować problemy z wydajnością, gdy jest używany w większych, bardziej złożonych arkuszach.
