Lejupielādējiet darbgrāmatas piemēru
Šī apmācība parāda, kā lietot Excel netiešā funkcija programmā Excel, lai no teksta izveidotu šūnu atsauci.
Netiešas funkcijas pārskats
Funkcija NETIEŠĀ Izveido šūnu atsauci no teksta virknes.
(Ievērojiet, kā parādās formulas ievades)
Funkcija INDIRECT Sintakse un ievades:
| 1 | = Netiešs (atsauces_teksts, C1) |
ref_text - Virkne, kas apzīmē šūnas atsauci vai diapazona atsauci. Virkne var būt R1C1 vai A1 formātā vai arī nosaukts diapazons.
a1 - pēc izvēles: norāda, vai atsauce ir R1C1 vai A1 formātā. FALSE attiecībā uz R1C1 vai TRUE / nobīdīts A1.
Kas ir netiešā funkcija?
Funkcija INDIRECT ļauj piešķirt teksta virkni un datoram interpretēt šo virkni kā faktisku atsauci. To var izmantot, lai atsauktos uz diapazonu tajā pašā lapā, citā lapā vai pat citā darbgrāmatā.
UZMANĪBU! NETIEŠĀ funkcija ir viena no gaistošajām funkcijām. Lielāko daļu laika, strādājot izklājlapā, dators pārrēķinās formulu tikai tad, ja ievades vērtības ir mainījušās. Gaistoša funkcija tomēr pārrēķinās katrs kad veicat izmaiņas jebkurā šūnā. Jāievēro piesardzība, lai nodrošinātu, ka neizraisāt lielu pārrēķināšanas laiku pārmērīgas gaistošo funkciju izmantošanas dēļ vai daudzu šūnu dēļ, kas ir atkarīgas no gaistošas funkcijas rezultāta.
Izveidojiet šūnas atsauci
Pieņemsim, ka vēlaties iegūt vērtību no A2, bet vēlaties pārliecināties, ka jūsu formula paliek uz A2 neatkarīgi no jaunu rindu ievietošanas/noņemšanas. Jūs varētu uzrakstīt formulu
| 1 | = NETIEŠA ("A2") |
Ņemiet vērā, ka mūsu funkcijas arguments ir teksta virkne “A2”, nevis šūnu atsauce. Turklāt, tā kā šī ir teksta virkne, nav jānorāda absolūta atsauce, piemēram, $ A $ 2. Teksts nekad nemainīsies, un tāpēc šī formula vienmēr norādīs uz A2 neatkarīgi no tā, kur tas tiek pārvietots.
NETIEŠS rindas numurs
Jūs varat apvienot teksta virknes un vērtības no šūnām kopā. Tā vietā, lai rakstītu “A2”, kā mēs to darījām iepriekš, mēs varam iegūt skaitlisku vērtību no šūnas B2 un izmantot to mūsu formulā. Mēs uzrakstītu šādu formulu
| 1 | = NETIEŠA ("A" un B2) |
Simbols “&” šeit tiek izmantots, lai teksta virkni “A” savienotu ar vērtību no šūnas B2. Tātad, ja B2 vērtība pašlaik būtu 10, tad mūsu formula būtu šāda
| 123 | = NETIEŠA ("A" un 10)= NETIEŠA ("A10")= A10 |
NETIEŠA kolonnas vērtība
Varat arī savienot kolonnas atsaucē. Šoreiz teiksim, ka mēs zinām, ka vēlamies iegūt vērtību no 10. rindas, bet vēlamies, lai mēs varētu mainīt, no kuras kolonnas izvilkt. Mēs ievietosim vēlamo kolonnas burtu šūnā B2. Mūsu formula varētu izskatīties šādi
| 1 | = NETIEŠA (B2 & "10") |
Ja B2 vērtība ir “G”, tad mūsu formula tiek vērtēta šādi
| 123 | = NETIEŠA ("G" un 10)= NETIEŠA ("G10")= G10 |
Netiešs r1c1 stils
Iepriekšējā piemērā kolonnas atsauces norādīšanai mums bija jāizmanto burts. Tas ir tāpēc, ka mēs izmantojām tā saukto A1 stila atsauci. A1 stilā kolonnas tiek apzīmētas ar burtu, bet rindas - ar cipariem. Absolūtās atsauces tiek norādītas, izmantojot “$” pirms vienuma, kuru vēlamies saglabāt absolūtu.
R1c1 rindas un kolonnas tiek sāktas, izmantojot numuru. Absolūtā atsauce uz a1 tiks uzrakstīta kā
| 1 | = R1C1 |
To var izlasīt kā “1. rinda, 1. sleja”. Relatīvās atsauces tiek dotas, izmantojot iekavas, bet skaitlis norāda pozīciju attiecībā pret šūnu ar formulu. Tātad, ja mēs rakstītu formulu šūnā A10 un mums būtu jāatsaucas uz A1, mēs rakstītu formulu
| 1 | = R [-9] C |
To var izlasīt kā “Šūna 9 rindas uz augšu, bet tajā pašā slejā.
Tas varētu būt noderīgi tāpēc, ka INDIRECT var atbalstīt r1c1 apzīmējumu izmantošanu. Apsveriet iepriekšējo piemēru, kurā mēs nolasījām vērtību no 10. rindas, bet vēlējāmies mainīt kolonnu. Tā vietā, lai dotu vēstuli, pieņemsim, ka mēs ievietojam skaitli šūnā B2. Tad mūsu formula varētu izskatīties šādi
| 1 | = NETIEŠA ("R10C" un B2, FALSE) |
Mēs izlaidām 2nd strīds līdz šim. Ja šis arguments tiek izlaists vai Patiess, funkcija novērtēs, izmantojot A1 stilu. Tā kā tas ir nepatiess, tas tiks novērtēts r1c1. Pieņemsim, ka B2 vērtība ir 5. Mūsu formula to novērtēs šādi
| 12 | = NETIESA ("R10C5", FALSE)= 10 ASV dolāri |
NETIEŠAS atšķirības ar A1 un r1c1
Atcerieties, ka mēs iepriekš parādījām, ka, tā kā šīs formulas saturs bija teksta virkne, tā nekad nemainījās?
| 1 | = NETIEŠA ("A2") |
Šī formula vienmēr apskatīs šūnu A2 neatkarīgi no tā, kur pārvietojat formulu. R1c1, jo jūs varat norādīt relatīvo pozīciju, izmantojot iekavas, šis noteikums nepaliek konsekvents. Ja ievietojat šo formulu šūnā B2
| 1 | = NETIEŠA ("RC [-1]") |
Tas aplūkos šūnu A2 (jo kolonna A ir viena pa kreisi no kolonnas B). Ja kopējat šo formulu šūnā B3, teksts iekšpusē paliks tāds pats, bet NETIEŠAIS tagad skatīsies uz šūnu A3.
NETIEŠI ar lapas nosaukumu
Jūs varat arī apvienot lapas nosaukumu savās netiešajās atsaucēs. Svarīgs noteikums, kas jāatceras, ir tas, ka ap vārdiem ir jāievieto pēdiņas, un lapas nosaukums no šūnas atsauces jāatdala ar izsaukuma zīmi.
Pieņemsim, ka mums bija šī iestatīšana, kurā mēs norādām lapas nosaukumu, rindu un kolonnu.
Mūsu formula, lai apvienotu to visu atsaucē, izskatītos šādi:
| 1 | = NETIEŠI ("" "& A2 &" '! "& B2 & C2) |
Mūsu formula tiks novērtēta šādi:
| 123 | = NETIESA ("" "&" Sheet2 "&" '! "&" B "&" 5 ")= NETIESA ("" "Sheet2 '! B5")= 'Lapa2'! B5 |
Tehniski, tā kā vārdam “Sheet2” nav atstarpju, mums tā nav vajag vienotās pēdiņas. Ir pilnīgi pareizi rakstīt kaut ko līdzīgu
| 1 | = Lapa2! A2 |
Tomēr nekaitē likt pēdiņas, kad tās nav vajadzīgas. Labākā prakse ir tos iekļaut, lai jūsu formula varētu apstrādāt gadījumus, kur tie varētu būt nepieciešami.
NETIEŠI citai darbgrāmatai
Mēs arī pieminēsim, ka INDIRECT var izveidot atsauci uz citu darbgrāmatu. Ierobežojums ir tāds, ka INDIRECT nesaņems vērtības no slēgtās darbgrāmatas, tāpēc šim konkrētajam lietojumam ir ierobežota praktiskums. Ja darbgrāmata, uz kuru norāda INDIRECT, nav atvērta, funkcija izsauks “#REF!” kļūda.
Rakstot darbgrāmatas nosaukumu, sintakse ir tāda, ka tai jābūt kvadrātiekavās. Izmantosim šo iestatījumu un mēģināsim iegūt vērtību no šūnas C7.
Mūsu formula būtu
| 1 | = NETIEŠS ("'[" & A2 & "]" & B2 & "'! C7") |
Vēlreiz pievērsiet uzmanību pēdiņu, iekavu un izsaukuma zīmes izvietojumam. Mūsu formula tiks novērtēta šādi:
| 123 | = NETIEŠS ("'[" & "Sample.xlsx" & "]" & "Kopsavilkums" & "'! C7")= NETIESA ("'[Paraugs.xslx] Kopsavilkums'! C7")= '[Paraugs.xlsx] Kopsavilkums'! C7 |
Netiešs, lai izveidotu dinamisko diapazonu
Ja jums ir liela datu kopa, ir svarīgi mēģināt optimizēt formulas, lai tās nepadarītu vairāk darba, nekā nepieciešams. Piemēram, tā vietā, lai atsauktos uz visu A sleju, mēs varētu vēlēties atsaukties uz precīzu šūnu skaitu mūsu sarakstā. Apsveriet šādu izkārtojumu:
Šūnā B2 mēs ievietojām formulu
| 1 | = COUNTA (A: A) |
Funkciju COUNTA datoram ir ļoti viegli aprēķināt, jo tā vienkārši pārbauda, cik šūnu kolonnā A ir kāda vērtība, nevis jāveic loģiskas pārbaudes vai matemātiskas darbības.
Tagad izveidosim formulu, kas apkopos A slejas vērtības, taču mēs vēlamies pārliecināties, ka tā aplūko tikai precīzu diapazonu ar vērtībām (A2: A5). Mēs uzrakstīsim savu formulu kā
| 1 | = SUMMA (NETIEŠA ("A2: A" un B2)) |
Mūsu INDIRECT gūst skaitli 5 no šūnas B2 un izveidos atsauci uz diapazonu A2: A5. Pēc tam SUM var izmantot šo diapazonu aprēķinam. Ja šūnā A6 pievienosim citu vērtību, skaitlis B2 tiks atjaunināts, un mūsu SUM formula tiks automātiski atjaunināta, iekļaujot šo jauno vērtību.
UZMANĪBU! Ieviešot tabulas Office 2007, ir daudz efektīvāk datus glabāt tabulā un izmantot strukturālu atsauci, nevis veidot formulu, kuru izmantojām šajā piemērā, jo netiešais ir nestabils. Tomēr tie var būt gadījumi, kad jums ir jāizveido vienumu saraksts un nevar izmantot tabulu.
Dinamiska diagrammu veidošana ar INDIRECT
Ņemsim iepriekšējo piemēru un spersim vēl vienu soli. Tā vietā, lai rakstītu formulu, lai sniegtu mums vērtību summu, mēs izveidosim nosaukumu diapazonu. Mēs varētu saukt šo diapazonu par “MyData” un likt tam atsaukties
| 1 | = NETIESA ("A2: A" & COUNTA ($ A: $ A)) |
Ņemiet vērā, ka, tā kā mēs to ievietojam nosauktajā diapazonā, mēs esam nomainījuši atsauci uz B2 un tā vietā ievietojuši funkciju COUNTA.
Tagad, kad mums ir šis nosauktais diapazons, mēs varētu to izmantot diagrammā. Mēs izveidosim tukšu līniju diagrammu un pēc tam pievienosim datu sēriju. Par sērijas vērtībām jūs varētu uzrakstīt kaut ko līdzīgu
| 1 | = 1. lapa! MyData |
Diagramma tagad izmantos šo atsauci diagrammas vērtībām. Tā kā A slejai tiek pievienotas vairāk vērtību, INDIRECT atsaucas uz arvien lielāku diapazonu, un mūsu diagramma turpinās atjaunināties ar visām jaunajām pievienotajām vērtībām.
Dinamiska datu validācija ar INDIRECT
Apkopojot lietotāju viedokli, dažreiz ir jāizdara viena izvēle, no kuras izvēlēties, atkarībā no iepriekšējās izvēles. Apsveriet šo izkārtojumu, kur mūsu pirmā sleja ļauj lietotājam izvēlēties augļus, dārzeņus un gaļu.
2nd slejā, mēs nevēlamies, lai būtu liels saraksts ar visām iespējamām izvēlēm, jo mēs jau esam nedaudz sašaurinājuši lietas. Tātad, mēs esam izveidojuši vēl 3 sarakstus, kas izskatās šādi:
Tālāk mēs piešķirsim katru no tiem šīs sarakstus nosauktajā diapazonā. T.i., visi augļi būs diapazonā ar nosaukumu “Augļi”, bet dārzeņi - “Dārzeņi” utt.
Atkal mūsu tabulā mēs esam gatavi iestatīt datu validāciju 2nd sleja. Mēs izveidosim saraksta veida validāciju, ievadot:
| 1 | = NETIEŠA (A2) |
Netiešais lasīs kolonnā A veikto izvēli un redzēs kategorijas nosaukumu. Mēs esam definējuši diapazonus ar šiem nosaukumiem, tāpēc INDIRECT ņems šo nosaukumu un izveidos atsauci uz vēlamo diapazonu.
papildu piezīmes
Izmantojiet funkciju INDIRECT, lai no teksta izveidotu šūnu atsauci.
Vispirms izveidojiet teksta virkni, kas apzīmē šūnas atsauci. Virknei jābūt vai nu parastajā A1 stila kolonnas burtā un rindas numurā (M37), vai R1C1 stilā (R37C13). Jūs varat ierakstīt atsauci tieši, bet parasti jūs atsaucaties uz šūnām, kas nosaka rindas un kolonnas. Visbeidzot, ievadiet izvēlēto šūnu atsauces formātu. TRUE vai izlaists A1 stila atsaucei vai FALSE R1C1 stilam.
Strādājot ar INDIRECT formulām, iespējams, vēlēsities izmantot ROW funkcija lai iegūtu atsauces rindas numuru vai SLEJAS funkcija lai iegūtu atsauces kolonnas numuru (nevis burtu).
Atgriezieties visu Excel funkciju sarakstā
NETIEŠI Google izklājlapās
Funkcija INDIRECT Google izklājlapās darbojas tieši tāpat kā programmā Excel:
