Saturs
Apsveicam! Jūs iekļuvāt maratona pēdējā dienā 30 Excel funkcijas 30 dienās. Tas ir bijis garš un interesants ceļojums, kura laikā jūs uzzinājāt daudzas noderīgas lietas par Excel funkcijām.
Maratona 30. dienā veltīsim funkcijas izpētei NETIEŠAIS (INDIRECT), kas atgriež teksta virknē norādīto saiti. Izmantojot šo funkciju, varat izveidot atkarīgus nolaižamos sarakstus. Piemēram, atlasot valsti nolaižamajā sarakstā, tiek noteikts, kuras opcijas parādīsies pilsētas nolaižamajā sarakstā.
Tātad, sīkāk aplūkosim funkcijas teorētisko daļu NETIEŠAIS (NETIEŠAIS) un izpētiet tā pielietošanas praktiskos piemērus. Ja jums ir papildu informācija vai piemēri, lūdzu, kopīgojiet tos komentāros.
30. funkcija: NETIEŠĀ
funkcija NETIEŠAIS (INDIRECT) atgriež saiti, kas norādīta teksta virknē.
Kā jūs varat izmantot funkciju INDIRECT?
Kopš funkcijas NETIEŠAIS (NETIEŠAIS) atgriež saiti, ko sniedz teksta virkne, varat to izmantot, lai:
- Izveidojiet sākotnējo saiti, kas nepārvietojas.
- Izveidojiet atsauci uz statisku nosaukumu diapazonu.
- Izveidojiet saiti, izmantojot lapu, rindu un kolonnu informāciju.
- Izveidojiet nemainošu skaitļu masīvu.
Sintakse INDIRECT (NETIEŠA)
funkcija NETIEŠAIS (INDIRECT) ir šāda sintakse:
INDIRECT(ref_text,a1)
ДВССЫЛ(ссылка_на_ячейку;a1)
- ref_text (saite uz šūnu) ir saites teksts.
- a1 – ja vienāds ar TRUE (TRUE) vai nav norādīts, tad tiks izmantots saites stils A1; un ja FALSE (FALSE), tad stils R1C1.
Slazdi INDIRECT (INDIRECT)
- funkcija NETIEŠAIS (NETIEŠAIS) tiek pārrēķināts ikreiz, kad mainās vērtības Excel darblapā. Tas var ievērojami palēnināt darbgrāmatas darbību, ja funkcija tiek izmantota daudzās formulās.
- Ja funkcija NETIEŠAIS (NETIEŠA) izveido saiti uz citu Excel darbgrāmatu, šai darbgrāmatai jābūt atvērtai, pretējā gadījumā formula ziņos par kļūdu #REF! (#LINK!).
- Ja funkcija NETIEŠAIS (NETIEŠĀ) atsaucas uz diapazonu, kas pārsniedz rindu un kolonnu ierobežojumu, formula ziņos par kļūdu #REF! (#LINK!).
- funkcija NETIEŠAIS (INDIRECT) nevar atsaukties uz dinamisko nosaukumu diapazonu.
1. piemērs: izveidojiet sākotnējo saiti, kas nepārvietojas
Pirmajā piemērā kolonnās C un E ir vienādi skaitļi, to summas aprēķinātas, izmantojot funkciju SUM (SUM) arī ir vienādas. Tomēr formulas nedaudz atšķiras. Šūnā C8 formula ir šāda:
=SUM(C2:C7)
=СУММ(C2:C7)
Šūnā E8 funkcija NETIEŠAIS (NETIEŠAIS) izveido saiti uz sākuma šūnu E2:
=SUM(INDIRECT("E2"):E7)
=СУММ(ДВССЫЛ("E2"):E7)
Ja ievietojat rindu lapas augšpusē un pievienojat janvāra (janvāra) vērtību, summa C ailē nemainīsies. Formula mainīsies, reaģējot uz rindas pievienošanu:
=SUM(C3:C8)
=СУММ(C3:C8)
Tomēr funkcija NETIEŠAIS (NETIEŠAIS) nosaka E2 kā sākuma šūnu, tāpēc janvāris tiek automātiski iekļauts kolonnas E kopsummu aprēķinā. Beigu šūna ir mainīta, bet sākuma šūna nav ietekmēta.
=SUM(INDIRECT("E2"):E8)
=СУММ(ДВССЫЛ("E2"):E8)
2. piemērs: saite uz statisku nosaukumu diapazonu
funkcija NETIEŠAIS (INDIRECT) var izveidot atsauci uz nosauktu diapazonu. Šajā piemērā zilās šūnas veido diapazonu NumList. Turklāt no B kolonnas vērtībām tiek izveidots arī dinamiskais diapazons NumListDyn, atkarībā no skaitļu skaita šajā kolonnā.
Abu diapazonu summu var aprēķināt, vienkārši norādot funkcijas nosaukumu kā argumentu funkcijai SUM (SUM), kā redzams šūnās E3 un E4.
=SUM(NumList) или =СУММ(NumList)
=SUM(NumListDyn) или =СУММ(NumListDyn)
Tā vietā, lai funkcijā ierakstītu diapazona nosaukumu SUM (SUM), varat atsaukties uz nosaukumu, kas rakstīts vienā no darblapas šūnām. Piemēram, ja vārds NumList ir ierakstīts šūnā D7, tad formula šūnā E7 būs šāda:
=SUM(INDIRECT(D7))
=СУММ(ДВССЫЛ(D7))
Diemžēl funkcija NETIEŠAIS (NETIEŠAIS) nevar izveidot dinamiskā diapazona atsauci, tāpēc, kopējot šo formulu šūnā E8, tiks parādīts kļūdas ziņojums. #REF! (#LINK!).
3. piemērs. Izveidojiet saiti, izmantojot lapu, rindu un kolonnu informāciju
Jūs varat viegli izveidot saiti, pamatojoties uz rindu un kolonnu numuriem, kā arī izmantojot vērtību FALSE (FALSE) otrajam funkcijas argumentam. NETIEŠAIS (NETIEŠI). Šādi tiek izveidota stila saite R1C1. Šajā piemērā saitei papildus pievienojām lapas nosaukumu – 'Manas saites'!R2C2
=INDIRECT("'"&B3&"'!R"&C3&"C"&D3,FALSE)
=ДВССЫЛ("'"&B3&"'!R"&C3&"C"&D3;ЛОЖЬ)
4. piemērs: izveidojiet skaitļu masīvu, kas nepārvietojas
Dažreiz Excel formulās ir jāizmanto skaitļu masīvs. Nākamajā piemērā mēs vēlamies aprēķināt vidējo 3 lielāko skaitļu kolonnā B. Skaitļus var ievadīt formulā, kā tas tiek darīts šūnā D4:
=AVERAGE(LARGE(B1:B8,{1,2,3}))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3}))
Ja jums ir nepieciešams lielāks masīvs, tad diez vai jūs vēlēsities formulā ievadīt visus skaitļus. Otrā iespēja ir izmantot funkciju ROW (ROW), kā tas izdarīts masīva formulā, kas ievadīta šūnā D5:
=AVERAGE(LARGE(B1:B8,ROW(1:3)))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))
Trešā iespēja ir izmantot funkciju ROW (STRING) kopā ar NETIEŠAIS (NETIEŠAIS), kā tas izdarīts ar masīva formulu šūnā D6:
=AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ("1:3"))))
Rezultāts visām 3 formulām būs vienāds:
Tomēr, ja rindas tiek ievietotas lapas augšpusē, otrā formula atgriezīs nepareizu rezultātu, jo atsauces formulā mainīsies līdz ar rindas maiņu. Tagad trīs lielāko skaitļu vidējā vietā formula atgriež vidējo vērtību no 3., 4. un 5. lielākā skaitļa.
Funkciju izmantošana NETIEŠAIS (NETIEŠAIS), trešā formula saglabā pareizās rindu atsauces un turpina rādīt pareizo rezultātu.