Všeobecný vzorec
=SUM(firstcell:INDEX(data,rows,cols))
Zhrnutie
Ak chcete definovať rozsah na základe hodnoty v inej bunke, môžete použiť funkciu INDEX. V zobrazenom príklade je vzorec v J7:
=SUM(C5:INDEX(data,J5,J6))
kde „údaj“ je pomenovaný rozsah B5: G9.
Vysvetlenie
Tento vzorec sa spolieha na konkrétne správanie indexu INDEX - hoci sa zdá, že index INDEX vracia hodnotu na konkrétnom mieste, v skutočnosti vracia odkaz na dané miesto. Vo väčšine vzorcov by ste si rozdiel nevšimli - Excel jednoducho vyhodnotí referenciu a vráti hodnotu. Tento vzorec využíva túto funkciu na vytvorenie dynamického rozsahu na základe vstupu do hárka.
Vo funkcii súčtu je prvá referencia jednoducho prvou bunkou v rozsahu, ktorý pokrýva všetky možné bunky:
=SUM(C5:
Na získanie poslednej bunky používame INDEX. Tu dáme INDEXU pomenovaný rozsah „data“, čo je maximálny možný rozsah hodnôt, a tiež hodnoty z J5 (riadky) a J6 (stĺpce). INDEX nevráti rozsah, vráti iba jednu bunku na danom mieste, v príklade E9:
INDEX(data,J5,J6) // returns E9
Pôvodný vzorec sa redukuje na:
=SUM(C5:E9)
ktorý vráti 300, súčet všetkých hodnôt v C5: E9.
Vzorec v J8 je takmer rovnaký, ale na výpočet priemeru sa namiesto SUM používa PRIEMER. Keď používateľ zmení hodnoty v J5 alebo J6, rozsah sa aktualizuje a vrátia sa nové výsledky.
Alternatívne s offsetom
Podobné vzorce môžete vytvoriť pomocou funkcie OFFSET, ktorá je uvedená nižšie:
=SUM(OFFSET(C5,0,0,J5,J6)) // sum =AVERAGE(OFFSET(C5,0,0,J5,J6)) // average
Funkcia OFFSET je navrhnutá tak, aby vrátila rozsah, takže vzorcom je možno rozumieť jednoduchšie. OFFSET je však volatilná funkcia a pri použití vo väčších a zložitejších pracovných hárkoch môže spôsobiť problémy s výkonom.
