Obecný vzorec
=SUM(firstcell:INDEX(data,rows,cols))
souhrn
Chcete-li definovat rozsah na základě hodnoty v jiné buňce, můžete použít funkci INDEX. V zobrazeném příkladu je vzorec v J7:
=SUM(C5:INDEX(data,J5,J6))
kde „data“ je pojmenovaný rozsah B5: G9.
Vysvětlení
Tento vzorec spoléhá na konkrétní chování INDEXU - i když se zdá, že INDEX vrací hodnotu v určitém umístění, ve skutečnosti vrací odkaz na dané umístění. Ve většině vzorců byste si nevšimli rozdílu - Excel jednoduše vyhodnotí referenci a vrátí hodnotu. Tento vzorec používá tuto funkci k vytvoření dynamického rozsahu na základě vstupu do listu.
Uvnitř funkce součet je první reference jednoduše první buňkou v rozsahu, který pokrývá všechny možné buňky:
=SUM(C5:
K získání poslední buňky použijeme INDEX. Zde dáváme INDEXU pojmenovaný rozsah „data“, což je maximální možný rozsah hodnot, a také hodnoty z J5 (řádky) a J6 (sloupce). INDEX nevrací rozsah, vrací pouze jednu buňku na tomto místě, v příkladu E9:
INDEX(data,J5,J6) // returns E9
Původní vzorec je snížen na:
=SUM(C5:E9)
který vrací 300, součet všech hodnot v C5: E9.
Vzorec v J8 je téměř stejný, ale k výpočtu průměru používá místo PRŮMĚR PRŮMĚR. Když uživatel změní hodnoty v J5 nebo J6, rozsah se aktualizuje a vrátí se nové výsledky.
Alternativa s offsetem
Podobné vzorce můžete vytvořit pomocí funkce OFFSET, která je uvedena níže:
=SUM(OFFSET(C5,0,0,J5,J6)) // sum =AVERAGE(OFFSET(C5,0,0,J5,J6)) // average
OFFSET je navržen tak, aby vrátil rozsah, takže vzorce jsou snadněji srozumitelné. OFFSET je však nestálá funkce a při použití ve větších a složitějších listech může způsobit problémy s výkonem.
