Excel formula: Definirajte raspon na temelju vrijednosti ćelije -

Generička formula

=SUM(firstcell:INDEX(data,rows,cols))

Sažetak

Da biste definirali raspon na temelju vrijednosti u drugoj ćeliji, možete koristiti funkciju INDEX. U prikazanom primjeru formula u J7 je:

=SUM(C5:INDEX(data,J5,J6))

gdje je "podatak" imenovani raspon B5: G9.

Obrazloženje

Ova se formula oslanja na određeno ponašanje INDEX-a - iako se čini da INDEX vraća vrijednost na određenom mjestu, zapravo vraća referencu na mjesto. U većini formula ne biste primijetili razliku - Excel jednostavno procjenjuje referencu i vraća vrijednost. Ova formula koristi ovu značajku za izradu dinamičkog raspona na temelju unosa radnog lista.

Unutar funkcije zbroja, prva referenca je jednostavno prva ćelija u rasponu koja pokriva sve moguće stanice:

=SUM(C5:

Da bismo dobili posljednju ćeliju, koristimo INDEX. Ovdje dajemo INDEKSU imenovani raspon "podaci", što je maksimalno mogući raspon vrijednosti, a također i vrijednosti iz J5 (retci) i J6 (stupci). INDEX ne vraća raspon, vraća samo jednu ćeliju na tom mjestu, E9 u primjeru:

INDEX(data,J5,J6) // returns E9

Izvorna formula svedena je na:

=SUM(C5:E9)

koji vraća 300, zbroj svih vrijednosti u C5: E9.

Formula u J8 gotovo je ista, ali za izračun prosjeka koristi PROSJEČNO umjesto SUM. Kada korisnik promijeni vrijednosti u J5 ili J6, raspon se ažurira i vraćaju se novi rezultati.

Alternativa OFFSET-u

Slične formule možete izgraditi pomoću funkcije OFFSET, prikazane dolje:

=SUM(OFFSET(C5,0,0,J5,J6)) // sum =AVERAGE(OFFSET(C5,0,0,J5,J6)) // average

OFFSET je dizajniran za vraćanje raspona, pa su formule možda jednostavnije razumjeti. Međutim, OFFSET je hlapljiva funkcija i može uzrokovati probleme s performansama kada se koristi u većim, složenijim radnim listovima.

Zanimljivi članci...