Turinys
Sveikiname! Patekote į paskutinę maratono dieną 30 Excel funkcijų per 30 dienų. Tai buvo ilga ir įdomi kelionė, kurios metu sužinojote daug naudingų dalykų apie Excel funkcijas.
30 maratono dieną skirsime funkcijos studijoms NETIESIOGINĖS (NETIESIOGINĖ), kuri grąžina teksto eilutės nurodytą nuorodą. Naudodami šią funkciją galite sukurti priklausomus išskleidžiamuosius sąrašus. Pavyzdžiui, kai pasirenkama šalis iš išskleidžiamojo sąrašo, nustatoma, kurios parinktys bus rodomos miesto išskleidžiamajame sąraše.
Taigi, atidžiau pažvelkime į teorinę funkcijos dalį NETIESIOGINĖS (NETIESIOGINĖ) ir ištirti praktinius jos taikymo pavyzdžius. Jei turite papildomos informacijos ar pavyzdžių, pasidalykite jais komentaruose.
30 funkcija: NETIESIOGINĖ
Funkcija NETIESIOGINĖS (NETIESIOGINĖ) grąžina nuorodą, nurodytą teksto eilutėje.
Kaip galite naudoti NETIESIOGINĖS funkciją?
Nuo funkcijos NETIESIOGINĖS (NETIESIOGINĖ) grąžina nuorodą, pateiktą teksto eilutėje, ją galite naudoti norėdami:
- Sukurkite nekeičiančią pradinę nuorodą.
- Sukurkite nuorodą į statinį pavadintą diapazoną.
- Sukurkite nuorodą naudodami lapo, eilutės ir stulpelio informaciją.
- Sukurkite nekeičiamą skaičių masyvą.
Sintaksė NETIESIOGINĖ (NETIESIOGINĖ)
Funkcija NETIESIOGINĖS (INDIRECT) turi tokią sintaksę:
INDIRECT(ref_text,a1)
ДВССЫЛ(ссылка_на_ячейку;a1)
- ref_text (link_to_cell) yra nuorodos tekstas.
- a1 – jei lygi TRUE (TRUE) arba nenurodyta, tada bus naudojamas nuorodos stilius A1; o jei FALSE (FALSE), tai stilius R1C1.
Spąstai NETIESIOGINIAI (NETIESIOGINIAI)
- Funkcija NETIESIOGINĖS (NETIESIOGINĖ) perskaičiuojamas, kai pasikeičia reikšmės Excel darbalapyje. Tai gali labai sulėtinti darbaknygę, jei funkcija naudojama daugelyje formulių.
- Jei funkcija NETIESIOGINĖS (NETIESIOGINĖ) sukuria nuorodą į kitą „Excel“ darbaknygę, ta darbaknygė turi būti atidaryta arba formulė praneš apie klaidą #REF! (#LINK!).
- Jei funkcija NETIESIOGINĖS (NETIESIOGINĖ) nurodo diapazoną, kuris viršija eilučių ir stulpelių limitą, formulė praneš apie klaidą #REF! (#LINK!).
- Funkcija NETIESIOGINĖS (INDIRECT) negali nurodyti dinaminio pavadinimo diapazono.
1 pavyzdys: sukurkite nekeičiančią pradinę nuorodą
Pirmajame pavyzdyje C ir E stulpeliuose yra tie patys skaičiai, jų sumos apskaičiuotos naudojant funkciją SUMA (SUM) taip pat yra vienodi. Tačiau formulės šiek tiek skiriasi. C8 langelyje formulė yra tokia:
=SUM(C2:C7)
=СУММ(C2:C7)
E8 langelyje funkcija NETIESIOGINĖS (NETIESIOGINĖ) sukuria nuorodą į pradinį langelį E2:
=SUM(INDIRECT("E2"):E7)
=СУММ(ДВССЫЛ("E2"):E7)
Jei įterpsite eilutę lapo viršuje ir pridėsite sausio (sausio) vertę, tada C stulpelio suma nepasikeis. Formulė pasikeis, reaguodama į eilutės pridėjimą:
=SUM(C3:C8)
=СУММ(C3:C8)
Tačiau funkcija NETIESIOGINĖS (NETIESIOGINĖ) nustato E2 kaip pradžios langelį, todėl sausio mėn. automatiškai įtraukiamas į E stulpelio sumų skaičiavimą. Pabaigos langelis pasikeitė, bet pradžios langelis nebuvo paveiktas.
=SUM(INDIRECT("E2"):E8)
=СУММ(ДВССЫЛ("E2"):E8)
2 pavyzdys: nuoroda į statinį pavadintą diapazoną
Funkcija NETIESIOGINĖS (NETIESIOGINĖ) gali sukurti nuorodą į pavadintą diapazoną. Šiame pavyzdyje mėlynos ląstelės sudaro diapazoną NumList. Be to, iš B stulpelio verčių taip pat sukuriamas dinaminis diapazonas NumListDyn, atsižvelgiant į skaičių skaičių šiame stulpelyje.
Abiejų diapazonų sumą galima apskaičiuoti tiesiog nurodant jos pavadinimą kaip funkcijos argumentą SUMA (SUM), kaip matote langeliuose E3 ir E4.
=SUM(NumList) или =СУММ(NumList)
=SUM(NumListDyn) или =СУММ(NumListDyn)
Užuot įvedę diapazono pavadinimą į funkciją SUMA (SUM), galite nurodyti pavadinimą, įrašytą viename iš darbalapio langelių. Pavyzdžiui, jei vardas NumList parašyta langelyje D7, tada E7 langelio formulė bus tokia:
=SUM(INDIRECT(D7))
=СУММ(ДВССЫЛ(D7))
Deja, funkcija NETIESIOGINĖS (NETIESIOGINĖ) negali sukurti dinaminio diapazono nuorodos, todėl nukopijavę šią formulę į langelį E8, gausite klaidą #REF! (#LINK!).
3 pavyzdys: sukurkite nuorodą naudodami lapo, eilutės ir stulpelio informaciją
Galite lengvai sukurti nuorodą pagal eilučių ir stulpelių numerius, taip pat naudodami reikšmę FALSE (FALSE) antrojo funkcijos argumentui. NETIESIOGINĖS (NETIESIOGINĖ). Taip sukuriama stiliaus nuoroda R1C1. Šiame pavyzdyje prie nuorodos papildomai įtraukėme lapo pavadinimą – „Mano nuorodos“!R2C2
=INDIRECT("'"&B3&"'!R"&C3&"C"&D3,FALSE)
=ДВССЫЛ("'"&B3&"'!R"&C3&"C"&D3;ЛОЖЬ)
4 pavyzdys: sukurkite nekeičiamą skaičių masyvą
Kartais „Excel“ formulėse reikia naudoti skaičių masyvą. Šiame pavyzdyje norime apskaičiuoti 3 didžiausių skaičių B stulpelyje vidurkį. Skaičius galima įvesti į formulę, kaip tai daroma langelyje D4:
=AVERAGE(LARGE(B1:B8,{1,2,3}))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3}))
Jei jums reikia didesnio masyvo, vargu ar norėsite į formulę įvesti visus skaičius. Antrasis variantas yra naudoti funkciją EILĖ (ROW), kaip padaryta masyvo formulėje, įvestoje langelyje D5:
=AVERAGE(LARGE(B1:B8,ROW(1:3)))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))
Trečia galimybė yra naudoti funkciją EILĖ (STRING) kartu su NETIESIOGINĖS (NETIESIOGINĖ), kaip padaryta naudojant masyvo formulę langelyje D6:
=AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ("1:3"))))
Visų 3 formulių rezultatas bus toks pat:
Tačiau jei eilutės įterpiamos lapo viršuje, antroji formulė pateiks neteisingą rezultatą dėl to, kad formulės nuorodos pasikeis kartu su eilutės poslinkiu. Dabar vietoj trijų didžiausių skaičių vidurkio formulė pateikia 3, 4 ir 5 didžiausių skaičių vidurkį.
Naudojant funkcijas NETIESIOGINĖS (NETIESIOGINĖ), trečioji formulė išsaugo teisingas eilučių nuorodas ir toliau rodo teisingą rezultatą.