vsebina
čestitke! Prispeli ste do zadnjega dne maratona 30 Excelovih funkcij v 30 dneh. Bila je dolga in zanimiva pot, na kateri ste izvedeli veliko koristnih stvari o Excelovih funkcijah.
30. dan maratona bomo posvetili študiju funkcije INDIRECT (INDIRECT), ki vrne povezavo, podano z besedilnim nizom. S to funkcijo lahko ustvarite odvisne spustne sezname. Na primer, ko izberete državo s spustnega seznama, določite, katere možnosti bodo prikazane na spustnem seznamu mest.
Oglejmo si torej pobližje teoretični del funkcije INDIRECT (POSREDNO) in raziščite praktične primere njegove uporabe. Če imate dodatne informacije ali primere, jih delite v komentarjih.
Funkcija 30: POSREDNO
funkcija INDIRECT (INDIRECT) vrne povezavo, ki jo določa besedilni niz.
Kako lahko uporabite funkcijo INDIRECT?
Od funkcije INDIRECT (INDIRECT) vrne povezavo, podano z besedilnim nizom, lahko jo uporabite za:
- Ustvarite začetno povezavo brez premikanja.
- Ustvarite referenco na statični poimenovan obseg.
- Ustvarite povezavo z uporabo podatkov o listu, vrstici in stolpcu.
- Ustvarite nepremikljivo matriko števil.
Sintaksa INDIRECT (POSREDNO)
funkcija INDIRECT (INDIRECT) ima naslednjo sintakso:
INDIRECT(ref_text,a1)
ДВССЫЛ(ссылка_на_ячейку;a1)
- ref_text (link_to_cell) je besedilo povezave.
- a1 – če je enako TRUE (TRUE) ali ni navedeno, bo uporabljen slog povezave A1; in če FALSE (FALSE), potem slog R1C1.
pasti POSREDNO (POSREDNO)
- funkcija INDIRECT (POSREDNO) se znova izračuna vsakič, ko se spremenijo vrednosti v Excelovem delovnem listu. To lahko zelo upočasni vaš delovni zvezek, če se funkcija uporablja v številnih formulah.
- Če funkcija INDIRECT (POSREDNO) ustvari povezavo do drugega Excelovega delovnega zvezka, ta delovni zvezek mora biti odprt, sicer bo formula javila napako #REF! (#POVEZAVA!).
- Če funkcija INDIRECT (POSREDNO) se sklicuje na obseg, ki presega omejitev vrstice in stolpca, bo formula javila napako #REF! (#POVEZAVA!).
- funkcija INDIRECT (INDIRECT) se ne more sklicevati na dinamični imenovani obseg.
Primer 1: Ustvarite nepremično začetno povezavo
V prvem primeru vsebujeta stolpca C in E enaka števila, njihove vsote pa so izračunane s funkcijo SUM (SUM) so prav tako enaki. Vendar so formule nekoliko drugačne. V celici C8 je formula:
=SUM(C2:C7)
=СУММ(C2:C7)
V celici E8 je funkcija INDIRECT (POSREDNO) ustvari povezavo do začetne celice E2:
=SUM(INDIRECT("E2"):E7)
=СУММ(ДВССЫЛ("E2"):E7)
Če na vrh lista vstavite vrstico in dodate vrednost za januar (januar), se znesek v stolpcu C ne bo spremenil. Formula se bo spremenila glede na dodano vrstico:
=SUM(C3:C8)
=СУММ(C3:C8)
Vendar funkcija INDIRECT (POSREDNO) določi E2 kot začetno celico, tako da je januar samodejno vključen v izračun skupnih vrednosti stolpca E. Končna celica se je spremenila, vendar začetna celica ni bila prizadeta.
=SUM(INDIRECT("E2"):E8)
=СУММ(ДВССЫЛ("E2"):E8)
Primer 2: povezava do statičnega poimenovanega obsega
funkcija INDIRECT (INDIRECT) lahko ustvari sklic na imenovan obseg. V tem primeru modre celice sestavljajo obseg NumList. Poleg tega se iz vrednosti v stolpcu B ustvari tudi dinamični obseg NumListDyn, odvisno od števila številk v tem stolpcu.
Vsoto za oba obsega je mogoče izračunati tako, da preprosto podate njeno ime kot argument funkciji SUM (SUM), kot lahko vidite v celicah E3 in E4.
=SUM(NumList) или =СУММ(NumList)
=SUM(NumListDyn) или =СУММ(NumListDyn)
Namesto vnašanja imena obsega v funkcijo SUM (SUM), lahko se sklicujete na ime, zapisano v eni od celic delovnega lista. Na primer, če ime NumList je zapisan v celici D7, potem bo formula v celici E7 takšna:
=SUM(INDIRECT(D7))
=СУММ(ДВССЫЛ(D7))
Na žalost funkcija INDIRECT (POSREDNO) ne more ustvariti sklica na dinamični obseg, zato boste, ko kopirate to formulo navzdol v celico E8, prejeli napako #REF! (#POVEZAVA!).
3. primer: ustvarite povezavo z uporabo informacij o listu, vrstici in stolpcu
Povezavo lahko preprosto ustvarite na podlagi številk vrstic in stolpcev, pa tudi z uporabo vrednosti FALSE (FALSE) za drugi argument funkcije INDIRECT (POSREDNO). Tako nastane stilska povezava R1C1. V tem primeru smo povezavi dodali še ime lista – 'Moje povezave'!R2C2
=INDIRECT("'"&B3&"'!R"&C3&"C"&D3,FALSE)
=ДВССЫЛ("'"&B3&"'!R"&C3&"C"&D3;ЛОЖЬ)
4. primer: ustvarite nepremikajočo matriko števil
Včasih morate v Excelovih formulah uporabiti niz številk. V naslednjem primeru želimo izračunati povprečje treh največjih števil v stolpcu B. Števila lahko vnesete v formulo, kot je to storjeno v celici D3:
=AVERAGE(LARGE(B1:B8,{1,2,3}))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3}))
Če potrebujete večjo matriko, potem verjetno ne boste želeli vnesti vseh števil v formulo. Druga možnost je uporaba funkcije ROW (VRSTICA), kot je storjeno v matrični formuli, vneseni v celico D5:
=AVERAGE(LARGE(B1:B8,ROW(1:3)))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))
Tretja možnost je uporaba funkcije ROW (STRING) skupaj z INDIRECT (POSREDNO), kot je storjeno s formulo polja v celici D6:
=AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ("1:3"))))
Rezultat za vse 3 formule bo enak:
Če pa so vrstice vstavljene na vrh lista, bo druga formula vrnila napačen rezultat zaradi dejstva, da se bodo sklicevanja v formuli spremenila skupaj s premikom vrstice. Zdaj formula namesto povprečja treh največjih števil vrne povprečje 3., 4. in 5. največjega števila.
Uporaba funkcij INDIRECT (POSREDNO), tretja formula ohranja pravilne sklice na vrstico in še naprej prikazuje pravilen rezultat.