Sadržaj
Čestitamo! Stigli ste do posljednjeg dana maratona 30 Excel funkcija u 30 dana. Bilo je to dugo i zanimljivo putovanje tijekom kojeg ste naučili mnogo korisnih stvari o Excel funkcijama.
30. dan maratona posvetit ćemo studiju funkcije INDIREKTNO (INDIRECT), koji vraća vezu specificiranu tekstualnim nizom. Pomoću ove funkcije možete stvoriti ovisne padajuće popise. Na primjer, prilikom odabira zemlje s padajućeg popisa određuje koje će se opcije pojaviti na padajućem popisu gradova.
Dakle, pogledajmo pobliže teorijski dio funkcije INDIREKTNO (INDIREKTNO) i istražiti praktične primjere njegove primjene. Ako imate dodatne informacije ili primjere, podijelite ih u komentarima.
Funkcija 30: NEIZRAVNO
funkcija INDIREKTNO (INDIRECT) vraća vezu specificiranu tekstualnim nizom.
Kako možete koristiti INDIRECT funkciju?
Budući da funkcija INDIREKTNO (INDIRECT) vraća vezu zadanu tekstualnim nizom, možete je koristiti za:
- Stvorite početnu poveznicu koja se ne pomiče.
- Stvorite referencu na statički imenovani raspon.
- Stvorite poveznicu koristeći informacije o listu, retku i stupcu.
- Napravite nepomični niz brojeva.
Sintaksa INDIRECT (INDIRECT)
funkcija INDIREKTNO (INDIRECT) ima sljedeću sintaksu:
INDIRECT(ref_text,a1)
ДВССЫЛ(ссылка_на_ячейку;a1)
- ref_text (link_to_cell) je tekst veze.
- a1 – ako je jednako TRUE (ISTINA) ili nije navedeno, koristit će se stil veze A1; a ako je FALSE (FALSE), onda stil R1C1.
Zamke INDIRECT (INDIRECT)
- funkcija INDIREKTNO (INDIRECT) se ponovno izračunava svaki put kada se promijene vrijednosti u Excel radnom listu. To može znatno usporiti radnu knjigu ako se funkcija koristi u mnogim formulama.
- Ako funkcija INDIREKTNO (INDIRECT) stvara vezu na drugu Excel radnu knjigu, ta radna knjiga mora biti otvorena ili će formula prijaviti pogrešku #REF! (#VEZA!).
- Ako funkcija INDIREKTNO (INDIRECT) upućuje na raspon koji premašuje ograničenje retka i stupca, formula će prijaviti pogrešku #REF! (#VEZA!).
- funkcija INDIREKTNO (INDIRECT) ne može referencirati dinamički imenovani raspon.
Primjer 1: Napravite početnu vezu bez pomicanja
U prvom primjeru stupci C i E sadrže iste brojeve, a njihove sume izračunate su pomoću funkcije IZNOS (SUM) također su isti. Međutim, formule su malo drugačije. U ćeliji C8 formula je:
=SUM(C2:C7)
=СУММ(C2:C7)
U ćeliji E8 funkcija INDIREKTNO (INDIRECT) stvara vezu na početnu ćeliju E2:
=SUM(INDIRECT("E2"):E7)
=СУММ(ДВССЫЛ("E2"):E7)
Ako umetnete redak na vrhu lista i dodate vrijednost za siječanj (siječanj), tada se iznos u stupcu C neće promijeniti. Formula će se promijeniti, reagirajući na dodavanje retka:
=SUM(C3:C8)
=СУММ(C3:C8)
Međutim, funkcija INDIREKTNO (INDIRECT) popravlja E2 kao početnu ćeliju, tako da je siječanj automatski uključen u izračun ukupnih iznosa stupca E. Završna ćelija je promijenjena, ali početna ćelija nije zahvaćena.
=SUM(INDIRECT("E2"):E8)
=СУММ(ДВССЫЛ("E2"):E8)
Primjer 2: Veza na statički imenovani raspon
funkcija INDIREKTNO (INDIRECT) može stvoriti referencu na imenovani raspon. U ovom primjeru plave ćelije čine raspon NumList. Osim toga, dinamički raspon također se stvara iz vrijednosti u stupcu B NumListDyn, ovisno o broju brojeva u ovom stupcu.
Zbroj za oba raspona može se izračunati jednostavnim davanjem njegovog naziva kao argumenta funkciji IZNOS (SUM), kao što možete vidjeti u ćelijama E3 i E4.
=SUM(NumList) или =СУММ(NumList)
=SUM(NumListDyn) или =СУММ(NumListDyn)
Umjesto upisivanja naziva raspona u funkciju IZNOS (SUM), možete se pozvati na ime napisano u jednoj od ćelija radnog lista. Na primjer, ako ime NumList je napisano u ćeliji D7, tada će formula u ćeliji E7 biti ovakva:
=SUM(INDIRECT(D7))
=СУММ(ДВССЫЛ(D7))
Nažalost, funkcija INDIREKTNO (INDIRECT) ne može stvoriti referencu dinamičkog raspona, pa kada kopirate ovu formulu u ćeliju E8, dobit ćete pogrešku #REF! (#VEZA!).
Primjer 3: Stvorite vezu koristeći informacije o listu, retku i stupcu
Možete jednostavno stvoriti vezu na temelju brojeva redaka i stupaca, kao i pomoću vrijednosti FALSE (FALSE) za drugi argument funkcije INDIREKTNO (NEIZRAVNO). Tako nastaje poveznica stila R1C1. U ovom smo primjeru linku dodatno dodali naziv lista – 'MyLinks'!R2C2
=INDIRECT("'"&B3&"'!R"&C3&"C"&D3,FALSE)
=ДВССЫЛ("'"&B3&"'!R"&C3&"C"&D3;ЛОЖЬ)
Primjer 4: Napravite nepomični niz brojeva
Ponekad morate upotrijebiti niz brojeva u Excel formulama. U sljedećem primjeru želimo izračunati prosjek 3 najveća broja u stupcu B. Brojevi se mogu unijeti u formulu, kao što je to učinjeno u ćeliji D4:
=AVERAGE(LARGE(B1:B8,{1,2,3}))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3}))
Ako trebate veći niz, vjerojatno nećete htjeti unijeti sve brojeve u formulu. Druga opcija je korištenje funkcije RED (REDAK), kao što je učinjeno u formuli polja unesenoj u ćeliju D5:
=AVERAGE(LARGE(B1:B8,ROW(1:3)))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))
Treća opcija je korištenje funkcije RED (Biti zajedno sa nekim INDIREKTNO (INDIREKTNO), kao što je učinjeno s formulom polja u ćeliji D6:
=AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ("1:3"))))
Rezultat za sve 3 formule bit će isti:
Međutim, ako su retci umetnuti na vrh lista, druga formula će vratiti netočan rezultat zbog činjenice da će se reference u formuli promijeniti zajedno s pomakom retka. Sada, umjesto prosjeka tri najveća broja, formula vraća prosjek 3., 4. i 5. najvećeg broja.
Korištenje funkcija INDIREKTNO (INDIRECT), treća formula zadržava točne reference retka i nastavlja prikazivati točan rezultat.