Excel SUM i INDIRECT Dynamic Range Formula

Microsoft Excel ima neke cool trike i koristeći SUM i INDIRECT formule dinamičkog opsega, samo su dva načina za lako manipulisanje podacima koji imate.

SUM - INDIRECT Pregled formule

Korišćenje funkcije INDIRECT u Excel formulama olakšava promenu opsega referenci ćelija korišćenih u formuli, bez potrebe da sami uređuju samu formulu.

INDIRECT se može koristiti sa nizom funkcija koje prihvataju referencu ćelije kao argument kao što su OFFSET i SUM funkcije.

U drugom slučaju, korištenjem INDIRECT-a kao argumenta za funkciju SUM može se kreirati dinamički opseg referenci ćelija koji se zatim sabira funkcija SUM-a.

INDIRECT to radi referiranjem podataka u ćelijama indirektno kroz srednju lokaciju.

Primer: SUM - INDIRECT Formula koristi se za ukupan dinamički opseg vrijednosti

Ovaj primjer zasniva se na podacima prikazanim na slici iznad.

Formulacija SUM-INDIRECT kreirana korišćenjem sledećih koraka tutorijala je:

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

U ovoj formuli, ugneženi argument INDIRECT funkcije sadrži reference na ćelije E1 i E2. Brojevi u tim ćelijama, 1 i 4, u kombinaciji sa ostalim argumentima INDIRECT-a, formiraju reference ćelija D1 i D4.

Kao rezultat toga, broj brojeva koji je totaliran funkcijom SUM su podaci sadržani u opsegu ćelija D1 do D4 - što je 50.

Promjenom brojeva lociranih u ćelijama E1 i E2; međutim, opseg koji treba da se izvrši može se lako promeniti.

Ovaj primjer će prvo koristiti gorenavedenu formulu da potpiše podatke u ćelijama D1: D4, a zatim promijeni sumirani opseg na D3: D6 bez izmjene formule u ćeliji F1.

01 od 03

Unošenje Formule - Opcije

Opcije za unos formule uključuju:

• upisivanjem gornje formule direktno u ćeliju F1 i pritiskom na taster Enter na tastaturi
• unesite funkciju INDIRECT kao argument pomoću dijaloga SUM funkcije

Većina funkcija u Excel-u ima dijalog, koji vam omogućava da unosite svaki argument funkcije na posebnu liniju bez potrebe za brutalnom sintaksicom .

U ovom slučaju, dijaloški okvir SUM funkcije može se koristiti za pojednostavljenje formule do određene mere. Budući da je funkcija INDIRECT ugnežena unutar SUM-a, funkcija INDIRECT i njegovi argumenti se još uvijek moraju uneti ručno.

Sledeći koraci koriste SUM dijaloški okvir za unos formule.

Unos podataka o tutorijalu

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

1. Unesite sledeće podatke u ćelije D1 do E2

Pokretanje SUM - INDIRECT Formule - Otvaranje dijaloga SUM funkcije

1. Kliknite na ćeliju F1 - ovde se prikazuju rezultati ovog primera
2. Kliknite na karticu Formule u meniju trake
3. Odaberite Math & Trig iz trake da biste otvorili funkciju padajuće liste
4. Kliknite na SUM na listi da biste otvorili dijaloški okvir funkcije

02 od 03

Unošenje funkcije INDIRECT - Kliknite za prikaz veće slike

INDIRECT formula mora biti upisana kao argument za funkciju SUM.

U slučaju ugneženih funkcija, Excel ne dozvoljava otvaranje dijaloga druge funkcije da unosi svoje argumente.

Funkcija INDIRECT, prema tome, mora se ručno uneti u liniju Number1 u dijalogu SUM Function.

1. U dijalogu kliknite na liniju Number1
2. Unesite sledeću INDIRECT funkciju: INDIRECT ("D" & E1 & ": D" & E2)
3. Kliknite OK da biste dovršili funkciju i zatvorili dijaloški okvir
4. Broj 50 bi trebalo da se pojavi u ćeliji F1, jer je to ukupno za podatke locirane u ćelijama D1 do D4
5. Kada kliknete na ćeliju F1, kompletna formula = SUM (INDIRECT ("D" i E1 & ": D" & E2)) se pojavljuje u traci formule iznad radnog lista

Prekidanje INDIRECT funkcije

Da bi kreirali dinamički opseg u koloni D koristeći INDIRECT, moramo kombinirati slovo D u argumentu INDIRECT funkcije s brojevima sadržanim u ćelijama E1 i E2.

Ovo se postiže sledećim:

• ampersand ( & ) se koristi za povezivanje ili povezivanje tekstualnih podataka (u ovom slučaju sa slovom D) sa referencom ćelije (E1 i E2)
• Dalje, tekstualni podaci koji se sabiraju sa reference ćelija moraju biti okruženi dvostrukim navodnicima ( "" )
• Na kraju, krajnje tačke opsega su odvojene debelom crevom (:)

Dakle, početnu tačku opsega definišu se znakovima: "D" i E1 .

Drugi skup znakova: ": D" & E2 kombinuje debelo crevo sa krajnjom tačkom. Ovo je učinjeno zato što je debelo crevo tekstualni znak i, stoga, mora biti uključeno unutar znakova navoda.

Treći ampersand u sredini koristi se za povezivanje dva dela u jedan argument :

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

03 od 03

Dinamično menja opseg SUM funkcije

Celokupna tačka ove formule je da olakšate promjenu opsega koji je totalirao funkcija SUM bez potrebe da uređujete argument funkcije.

Ako uključite funkciju INDIRECT u formuli, promena brojeva u ćelijama E1 i E2 promjenit će opseg ćelija pročitanih funkcijom SUM.

Kao što se može videti na gornjoj slici, to takođe rezultira promenljivim odgovorom odgovora u ćeliji F1, jer on iznosi novi raspon podataka.

1. Kliknite na ćeliju E1
2. Unesite broj 3
3. Pritisnite taster Enter na tastaturi
4. Kliknite na ćeliju E2
5. Unesite broj 6
6. Pritisnite taster Enter na tastaturi
7. Odgovor u ćeliji F1 bi trebalo da se promeni na 90 - što je ukupan broj sadržanih u ćelijama D3 do D6
8. Dalje testirajte formulu promenom sadržaja ćelija B1 i B2 na bilo koji brojevi između 1 i 6

INDIREKT i #REF! Error Value

#REF! vrednost greške će se pojaviti u ćeliji F1 ako argument INDIRECT funkcije:

• nije važeća oznaka ćelije
• sadrži spoljnu referencu na drugu radnu svesku i ta radna sveska nije otvorena
• odnosi se na opseg ćelija izvan granica radnog lista (red 1,048,576 ili XFD kolona)