Excel SUM i INDIRECT Formula dinamičke raspona

Microsoft Excel ima neke cool trikove i upotreba SUM i INDIRECT dinamičkih formula predstavljaju samo dva načina jednostavnog manipuliranja podacima koje imate.

SUM - INDIRECT Pregled formule

Upotreba funkcije INDIRECT u Excelovim formulama olakšava promjenu raspona referenci za ćelije koji se upotrebljavaju u formuli bez potrebe za uređivanjem same formule.

INDIRECT se može koristiti s brojnim funkcijama koje prihvaćaju referencu stanica kao argument kao što su OFFSET i SUM funkcije.

U potonjem slučaju, korištenjem INDIRECT kao argumenta za SUM funkciju može se stvoriti dinamički raspon referencija stanica koje SUM funkcija zatim dodaje.

INDIRECT to čini upućivanjem na podatke u ćelijama indirektno preko međuprostora.

Primjer: SUM - INDIRECT Formula koja se koristi za ukupan dinamički raspon vrijednosti

Ovaj se primjer temelji na podacima prikazanim na gornjoj slici.

SUM-INDIRECT formula izrađena pomoću sljedećih koraka:

= SUM (INDIRECT ("D" & E1 & ": D" & E2))

U toj formuli argumenti ugniježđene indirektne funkcije sadrže reference na stanice E1 i E2. Brojevi u tim stanicama, 1 i 4, u kombinaciji s ostatkom argumenata INDIRECT-a, čine ćelije D1 i D4.

Kao rezultat toga, raspon brojeva koji se zbroje SUM funkcijom su podaci sadržani u rasponu stanica D1 do D4 - što je 50.

Promjenom brojeva koji se nalaze u stanicama E1 i E2; međutim, raspon koji se treba zbrajati može se lako mijenjati.

Ovaj primjer će najprije koristiti gornju formulu kako bi se ukupno prikupili podaci u ćelijama D1: D4, a zatim promijenili zbirni raspon do D3: D6 bez uređivanja formule u ćeliji F1.

01 od 03

Unosom formule - Opcije

Opcije za unos u formulu uključuju:

• upisivanjem gornje formule izravno u ćeliju F1 i pritiskom tipke Enter na tipkovnici
• ulazeći u INDIRECT funkciju kao argument pomoću dijaloškog okvira SUM funkcije

Većina funkcija u programu Excel ima dijaloški okvir koji omogućuje unos svakog argumenata funkcije u zasebnu liniju bez brige o sintaksi .

U ovom slučaju dijaloški okvir SUM funkcije može se koristiti za pojednostavljenje formule u određenoj mjeri. Budući da je funkcija INDIRECT ugrađena unutar SUM-a, INDIRECT funkcija i njezini argumenti još uvijek moraju biti uneseni ručno.

Koraci u nastavku upotrebljavaju SUM dijaloški okvir za unos formule.

Unos podataka o vodiču

Podaci o stanicama D1 - 5 D2 - 10 D3 - 15 D4 - 20 D5 - 25 D6 - 30 E1 - 1 E2 - 4

1. Unesite sljedeće podatke u ćelijama D1 do E2

Pokretanje SUM - INDIRECT formule - Otvaranje dijaloškog okvira SUM Function

1. Kliknite na ćeliju F1 - ovdje će se prikazati rezultati ovog primjera
2. Kliknite karticu formule na traci izbornika
3. Odaberite Math & Trig s vrpce kako biste otvorili padajući popis funkcije
4. Kliknite SUM na popisu da biste otvorili dijaloški okvir funkcije

02 od 03

Unos INDIRECT funkcije - Kliknite da biste vidjeli veću sliku

Potrebno je unijeti indirektnu formulu kao argument za SUM funkciju.

U slučaju ugniježđenih funkcija, Excel ne dopušta otvaranje dijaloškog okvira druge funkcije za unos svojih argumenata.

Indirektna funkcija, dakle, mora biti unesena ručno u redni broj 1 dijaloškog okvira SUM Funkcija.

1. U dijaloškom okviru kliknite redak Number1
2. Unesite sljedeću INDIRECT funkciju: INDIRECT ("D" & E1 & ": D" & E2)
3. Kliknite U redu da biste dovršili funkciju i zatvorili dijaloški okvir
4. Broj 50 trebao bi se pojaviti u ćeliji F1, budući da je to ukupno za podatke koji se nalaze u ćelijama D1 do D4
5. Kada kliknete na ćeliju F1, formula formule = SUM (INDIRECT ("D" & E1 & ": D" & E2)) pojavljuje se u traci formule iznad radnog lista

Razbijanje INDIRECT funkcije

Da bismo stvorili dinamički raspon u stupcu D pomoću INDIRECT, moramo kombinirati slovo D u argumentu INDIRECT funkcije s brojevima sadržanim u stanicama E1 i E2.

To se postiže sljedećim:

• ( & ) se upotrebljava za povezivanje tekstualnih podataka (u ovom slučaju slovo D) s referencama ćelija (E1 i E2)
• Nadalje, tekstualni podaci koji se povezuju s referencama stanica moraju biti okruženi dvostrukim navodnicima ( "" )
• na kraju, krajnje točke dometa odvojene su dvotočkom (:)

Stoga je početna točka raspona definirana znakovima: "D" i E1 .

Drugi skup znakova: ": D" i E2 kombiniraju debelo crijevo s krajnjom točkom. To je učinjeno jer je debelo crijevo tekstualni lik i stoga mora biti uključeno u navodnike.

Treći znak u sredini koristi se za spajanje dvaju dijelova u jedan argument :

"D" & E1 & ": D" & E2

03 od 03

Dinamički promjena raspona SUM funkcije

Cjelokupna točka ove formule je olakšati promjenu raspona ukupnog SUM funkcije bez potrebe za uređivanjem argumenata funkcije.

Uključivanjem funkcije INDIRECT u formulu promjena brojeva u stanicama E1 i E2 mijenja se raspon ćelija koje se čitaju pomoću SUM funkcije.

Kao što se može vidjeti na gornjoj slici, to također rezultira odgovorom formule koji se nalazi u promjeni stanične faze, budući da iznosi novi raspon podataka.

1. Kliknite na ćeliju E1
2. Upišite broj 3
3. Pritisnite tipku Enter na tipkovnici
4. Kliknite na ćeliju E2
5. Upišite broj 6
6. Pritisnite tipku Enter na tipkovnici
7. Odgovor u ćeliji F1 trebao bi se promijeniti na 90 - što je ukupan brojevi sadržani u ćelijama D3 do D6
8. Nadalje provjerite formulu promjenom sadržaja ćelija B1 i B2 na bilo koji broj između 1 i 6

INDIRECT i #REF! Vrijednost pogreške

#REF! vrijednost pogreške pojavit će se u ćeliji F1 ako je argument INDIRECT funkcije:

• nije valjana referenca za ćeliju
• sadrži vanjsku referencu na drugu radnu knjigu i da radna knjiga nije otvorena
• odnosi se na raspon stanica izvan granica radnog lista (redak 1.048.576 ili stupac XFD)