Netiešā funkcija programmā Excel
Netiešā funkcija Excel ir iebūvēta funkcija, ko izmanto, lai atsauktos un iegūtu šūnu vērtības no teksta virknes, šī formula ņem atsauci no norādītās šūnas, tai ir divi argumenti, pirmais arguments nav obligāts, bet otrais arguments nav obligāts, kas attiecas uz spēles veidu, šo funkciju var izmantot arī ar citu formulu.
Netiešajai funkcijai Excel ir vairāki lietojumi. Tas atgriež šūnas atsauci, kas norādīta ar teksta virkni. Piemēram, ja šūnā B1 ir virkne, pieņemsim, ka c4 un c4 diapazonam ir vērtība “Jessica”. Tagad, ja mēs izmantosim funkciju INDIRECT un nodosim atsauces tekstu kā B1, tā atgriezīs vērtību, ko satur šūnu diapazons c4, kas ir “Jessica”.
Excel funkcija INDIRECT ļauj netieši norādīt šūnas adresi. Kā jūs varat iepriekš minētajā NETIEŠAIS Excel piemērā, ja šūnā B1 ir teksts C4, šī netiešā Excel formula atgriež šūnas C4 saturu ( Jessica ). Šī funkcija ir ārkārtīgi noderīga funkcija. Funkciju INDIRECT varat izmantot ikreiz, kad vēlaties mainīt atsauci uz šūnu netiešās Excel formulas ietvaros, nemainot pašu formulu.
NETIEŠA Excel formula
Paskaidrojums
Ref_text ir atsauce uz šūnu, kurā ir A1 stila atsauce, R1C1 stila atsauce, nosaukums, kas definēts kā atsauce, vai atsauce uz šūnu kā teksta virkne.
a1 arguments nav obligāts
| a1 | Nepatiesa vai 0, ja ref_text ir R1C1 stilā |
| a1 | Izlaists vai True (1), ja ref_text ir A1 stilā |
Kā Excel programmā izmantot NETIEŠO funkciju?
Pirms adreses funkcijas izmantošanas Excel darbgrāmatā ņemsim NETIEŠU Excel piemēru:
Pieņemsim, ka vietnei www.xyz.com mums ir Google analīzes dati vietnes apmeklētājiem no cita mērķauditorijas kanāla piecās dažādās valstīs, kā parādīts tabulā:
Mēs varam izmantot funkciju INDIRECT programmā Excel, lai pārveidotu teksta virkni par atsauci, tādējādi atsaucoties uz šūnu H4 un izmantojot šo nosaukumu kā atsauci. Funkcijas INDIRECT izmantošana Excel programmā šajā gadījumā ir atsauce uz nosaukumu diapazoniem, izmantojot šūnas vērtības, tāpēc šūnā I4 mēs vienkārši varētu izmantot funkciju summa, lai aprēķinātu kopējo apmeklētāju skaitu no valsts Kanāda.
Tagad mēs varam tieši aprēķināt apmeklētāju summu no katras valsts, izmantojot summas funkciju, bet, izmantojot Excel funkciju INDIRECT, mēs varam aprēķināt dažādu valstu apmeklētāju summu, mainot nosaukuma atsauci, nevis mainot pašu netiešo Excel formulu.
Mēs esam izveidojuši vārdu diapazonu katram valsts viesim; to varēja viegli izdarīt, ierakstot vārdu diapazonu telpā tieši zem starpliktuves, kā parādīts zemāk.
Vēl viena metode ir tā, ka varat doties uz formulām-> vārdu pārvaldnieks -> atlasīt diapazonu, kuru vēlaties nosaukt -> noklikšķiniet uz vārdu pārvaldnieka -> ierakstiet nosaukumu diapazonu.
Līdzīgi tas izveidoja nosaukto diapazonu excel arī apmeklētājiem no citām valstīm.
Tagad, I4. Punktā, aprēķinot apmeklētāja summu no valsts Kanāda, izmantojot funkciju INDIRECT un izmantojot vārdu diapazonu H4 (Kanāda, nosaukums virs F3: F7), mēs iegūstam kopējo apmeklētāja summu.
Ja mēs izveidojam datu sarakstu, izmantojot datu validāciju valstīm, un lietojam šo sarakstu H4
Mainot dažādus valstu nosaukumus no Kanādas uz Indiju, ASV, Austrāliju vai Singapūru, mēs iegūstam apmeklētāja kopējo summu I4, izmantojot Excel funkciju INDIRECT.
Tātad katrā gadījumā mēs nemainām netiešo Excel formulu, kas atrodas šūnā I4; mēs mainām nosaukuma atsauci H4 un aprēķinām katras valsts apmeklētāju summu.
Mēs varam pāriet vienu soli tālāk un varam atsaukties uz darblapu līmeņa nosaukumiem, arī programmā Excel izmantojot INDIRECT. Piemēram, tagad mēs izmantosim funkciju INDIRECT, atsaucoties uz dažādām darbgrāmatas lapām.
Mēs esam izveidojuši dažādas lapas ar valstu nosaukumiem par kopējo pārdošanas skaitu, kas veikts katrā meklēšanas kanālā dažādās valstīs.
Indijai,
ASV,
Līdzīgi arī citām valstīm.
Atkal mēs aprēķināsim kopējo pārdošanas apjomu, kas veikts katrā kanālā, izmantojot funkciju INDIRECT programmā Excel dažādām lapām ar atsauci uz valstu nosaukumiem.
Šajā gadījumā, tā kā mums ir pārdošanas dati dažādās lapās, mums būs jāsavieno dažādi elementi, kas ir datu diapazons, tāpēc ref_text sāksies ar lapas nosaukumu, kas šajā gadījumā B4 ir Indija, tad mēs izmantojiet & operatoru, ko sauc par ampersandu, ko izmanto savienošanai.
Tātad mēs iegūstam Indijas pārdošanas kopējo summu; tagad, ja mainīsim lapas B4 nosaukumu no Indijas uz citām valstīm, piemēram, ASV, Singapūru vai Kanādu, mēs iegūsim šo valstu kopējo pārdošanas vērtību.
Atkal šajā gadījumā mēs vienkārši esam mainījuši ref_text vērtības šūnā B4, nemainot galveno netiešās Excel formulu, kas ir C4, lai iegūtu pārdošanas summu dažādām valstīm, kas atrodas dažādās lapās, izmantojot funkciju INDIRECT.
Tagad mēs izmantosim funkciju NETIEŠA, izmantojot atsauci R1C1, lai iegūtu pagājušā mēneša pārdošanas vērtību. Mums ir pārdošanas apjomi dažādiem mēnešiem B, C un D slejās trīs mēnešus Jan, Feb un Mar, kas var pieaugt līdz vairākiem mēnešiem, piemēram, aprīlim, maijam, jūnijam utt. Tātad pārdošanas galds vienmēr pieaugs atkarībā no gaidāmā mēneša izpārdošanas pievienošanas. Tātad šajā NETIEŠAJĀ Excel piemērā mēs vēlamies aprēķināt pēdējā mēneša pārdošanas apjomu.
Tātad, šeit pēdējais mēnesis ir Mar, un mēs vēlamies aprēķināt kopējo marta mēneša pārdošanas apjomu, kas ir 249 344 USD, izmantojot funkciju NETIEŠA.
Šajā NETIEŠAIS Excel piemērā mēs atradīsim funkcijas INDIRECT otrā argumenta izmantošanu
INDIRECT (atsauces_teksts, a1)
a1: kas ir neobligāts arguments, tā ir loģiska vērtība (Būla vērtība), kas norāda, kāda veida atsauces (ref_text) ir šūnā
ja a1 ir taisnība (1) vai izlaista, ref_text tiek uzskatīta par A1 stila atsauci
ja a1 ir nepatiesa (0), ref_text tiek interpretēts kā R1C1 stils
Ar ko A1 stils atšķiras no R1C1 stila?
Parasti funkcija INDIRECT programmā Excel izmanto A1 apzīmējumus. Katra šūnas adrese sastāv no kolonnas burta un rindas numura. Tomēr funkcija INDIRECT programmā Excel atbalsta arī R1C1 apzīmējumus. Šajā sistēmā šūnu A1 sauc par šūnu R1C1, šūnu A2 kā R2C1 utt.
Lai pārslēgtos uz R1C1 apzīmējumu, izvēlieties File-> opciju, lai atvērtu Excel opciju dialoglodziņu, noklikšķiniet uz cilnes formula un izvēles rūtiņas uz R1C1 atsauces stila opciju. Tagad jūs pamanīsit, ka visi kolonnu burti mainās uz cipariem. Pielāgojas arī visas jūsu formulu šūnu atsauces.
Tātad, lietojot R1C1 stilu funkcijā INDIRECT programmā Excel, mēs a1 vērtību nododam kā nepatiesu, un, ja mēs izmantojam A1 stilu, tad vērtību patiesi nododam a1.
Tagad šajā NETIEŠAJĀ Excel piemērā prasības dēļ izmantosim R1C1 stilu, jo kolonnas ar mēnesi pieaugs.
Izmantojot stilu R1C1, kopējā pārdošanas vērtība ir 10. rindā, un pēdējās kolonnas vērtība, kas atrodas kolonnā D, tiks aprēķināta, izmantojot funkciju COUNTA , kas piešķirs pēdējās kolonnas numuru, kurā ir vērtība. Tātad, skaits (10:10) šajā gadījumā dos 4, kuru kolonnas numurs R1C1 stilā. Tātad, mēs esam sazinājušies ar šo vērtību, lai iegūtu R10C4, kas satur vēlamo vērtību, kas ir pēdējā mēneša kopējā pārdošanas vērtība (249 344 USD).
Tā kā mēs šeit izmantojam R1C1 stilu, mēs nodosim nepatiesu vērtību a1 argumentam.
Tagad pievienosim vēl vienu kolonnu ar aprīļa izpārdošanas mēnesi.
Tātad iegūstiet jaunas pagājušā mēneša pārdošanas vērtības, kas atbilst aprīļa pēdējā mēneša vērtībai. Līdzīgi, ja pievienosim vairāk mēnešus, mēs vienmēr iegūsim jaunu pagājušā mēneša pārdošanas vērtību, nemainot netiešās Excel formulas katram mēnesim. Šī ir netiešās funkcijas Excel programmā specialitāte.
Kā pielāgot tabulas masīvu funkcijā INDIRECT Excel VLOOKUP?
Mums ir piecas valstis, meklēšanas kanāls un pārdošana, ko vietne veica, izmantojot dažādus kanālus no dažādām valstīm. Tagad šūnā B3 mēs vēlamies iegūt pārdošanu, izmantojot dabiskos meklēšanas vaicājumus no Amerikas Savienotajām Valstīm.
Katrai tabulai dažādām valstīm mēs esam nosaukuši katru tabulu ar īsu valsts nosaukumu, Ind Ind, Indija ASV, Kanāda Kan un tā tālāk .
Ja mēs tieši izmantojam funkciju NETIEŠAIS Excel vlookup, lai iegūtu vērtību, kā darīts zemāk
Mēs saņemam kļūdu, jo funkcija INDIRECT Excel vlookup nespēj atpazīt nodoto tabulas masīvu kā B2 šūnas vērtību, tāpēc mēs izmantosim funkciju INDIRECT programmā Excel, lai vlookup panāktu, ka table_array tiek atzīts par derīgu argumentu.
Ja mainīsim B1 un B2 vērtības, pēc pieprasījuma B3 iegūsim dažādas pārdošanas vērtības.
Piezīme. Vārdu diapazoni nav reģistrjutīgi. Tādējādi - Ind, IND un InD visi ir vienādi ar Excel.
Nākamajā INDIRECT Excel piemērā mēs izmantosim funkciju INDIRECT programmā Excel, lai izveidotu atkarīgu sarakstu. Mums ir saraksts ar darbinieka amatu uzņēmumā; tad katram amatam mums ir darbinieku vārdi.
Tagad šūnā I2 mēs izveidosim sarakstu ar dažādām darbinieku kategorijām kā viņu pozīcijas, un I3 mēs parādīsim sarakstu, kas būs atkarīgs no vērtības I2. Ja I2 ir režisors, tad I3 jāatspoguļo to direktoru vārdi, kurus Andrew un Micheal; ja I2 ir vadītājs, tad I3 jāatspoguļo vadītāja vārdi, kas ir Kamils, Deivids, Deiviss un Viljams, un tāpat kā pārraugam.
Šajā gadījumā atkal izmantosim funkciju INDIRECT, lai izveidotu atkarīgo sarakstu. Datu validācija, iestatījumos atlasot validācijas kritērijus kā sarakstu un avota laukā, programmā Excel izmantosim funkciju INDIRECT šādi.
= NETIEŠI ($ I $ 2)
Kad mēs atlasām direktoru vārdus, I3 atspoguļo sarakstu ar vārdiem Endrjū un Mišels, kad I2 ir vadītājs, tad I3 atspoguļo menedžera Kamila, Deivida, Deivisa un Viljama vārdus un līdzīgi vadītājam, kā parādīts attēlā.
Atceramās lietas
- Argumentam ref_text jābūt derīgai šūnas atsaucei, pretējā gadījumā INDIRECT atgriezīs #REF! Kļūda. Ref_text arguments var atsaukties uz citu darbgrāmatu.
- Ja ref_text attiecas uz citu darbgrāmatu (ārēju atsauci), otrai darbgrāmatai jābūt atvērtai. Ja avota darbgrāmata nav atvērta, INDIRECT atgriež #REF! Kļūdas vērtība.
- Ja ref_text attiecas uz šūnu diapazonu, kas pārsniedz rindu ierobežojumu 1 048 576 vai kolonnu ierobežojumu 16 384 (XFD), INDIRECT atgriež #REF! Kļūda.
