Sisu
Esmapilgul (eriti spikri lugemisel) funktsioon KAUDSEID (Kaudne) tundub lihtne ja isegi mittevajalik. Selle olemus on muuta lingina näiv tekst täisväärtuslikuks lingiks. Need. kui meil on vaja viidata lahtrile A1, siis saame kas tavaliselt teha otselingi (sisestage D1-sse võrdusmärk, klõpsake A1 ja vajutage sisestusklahvi) või KAUDSEID samal eesmärgil:
Pange tähele, et funktsiooni argument – viide A1-le – sisestatakse jutumärkidesse, nii et tegelikult on siin tegemist tekstiga.
"Noh, okei," ütlete sa. "Ja mis kasu on?"
Kuid ärge otsustage esmamulje järgi – see on petlik. See funktsioon võib teid paljudes olukordades aidata.
Näide 1. Transponeerimine
Žanri klassika: peate pöörama vertikaalset diameetrit
soon horisontaalseks (transponeeri). Loomulikult võite kasutada spetsiaalset sisestust või funktsiooni ÜLEKANDMINE (TRANSPOSEERIDA) massiivivalemis, kuid saate hakkama ka meiega KAUDSEID:
Loogika on lihtne: järgmise lahtri aadressi saamiseks liimime tähe “A” erimärgiga “&” ja praeguse lahtri veeru numbri, mille funktsioon meile annab. VEERG (VEERG).
Pöördprotseduur on parem teha veidi teisiti. Kuna seekord peame moodustama lingi lahtritega B2, C2, D2 jne, on klassikalise “merelahingu” asemel mugavam kasutada R1C1 lingirežiimi. Selles režiimis erinevad meie lahtrid ainult veeru numbri poolest: B2=R1C2, C2=R1C3, D2=R1C4 ja nii edasi
Siin tuleb sisse teine valikulise funktsiooni argument. KAUDSEID. Kui see on võrdne VALETAMINE (VALE), siis saate määrata lingi aadressi režiimis R1C1. Nii saame horisontaalse vahemiku hõlpsalt tagasi vertikaalseks ümber tõsta:
Näide 2. Summa intervalli järgi
Oleme juba analüüsinud ühte võimalust, kuidas funktsiooni abil lehel etteantud suurusega akna (vahemiku) summeerida KÕRVALDAMINE (NIHE). Sarnast probleemi saab lahendada ka kasutades KAUDSEID. Kui meil on vaja andmeid kokku võtta ainult teatud vahemiku perioodist, siis saame need tükkidest liimida ja seejärel muuta täisväärtuslikuks lingiks, mille saame funktsiooni sisse sisestada SUM (SUM):
Näide 3. Nutika tabeli rippmenüü
Mõnikord ei käsitle Microsoft Excel nutikate tabelite nimesid ja veerge täislinkidena. Näiteks kui proovite luua ripploendit (tab Andmed – andmete valideerimine) veeru alusel Töötajad nutikast lauast Inimesed saame veateate:
Kui "mähime" linki oma funktsiooniga KAUDSEID, siis võtab Excel selle hõlpsalt vastu ja meie ripploendit uuendatakse dünaamiliselt uute töötajate lisamisel nutika tabeli lõppu:
Näide 4. Purustamatud lingid
Nagu teate, parandab Excel automaatselt valemite viiteaadresse, kui lisate lehele rida-veergud või kustutate need. Enamikul juhtudel on see õige ja mugav, kuid mitte alati. Oletame, et peame kandma nimed töötajate kataloogist aruandesse:
Kui paned tavalised lingid (esimesse rohelisse lahtrisse sisesta =B2 ja kopeeri see alla), siis näiteks Dasha kustutamisel saame #LINK! viga talle vastavas rohelises lahtris. (#REF!). Kui kasutate funktsiooni linkide loomiseks KAUDSEID sellist probleemi ei tule.
Näide 5: andmete kogumine mitmelt lehelt
Oletame, et meil on 5 lehte sama tüüpi aruannetega erinevatelt töötajatelt (Mihhail, Jelena, Ivan, Sergei, Dmitri):
Oletame, et kaupade ja kuude kuju, suurus, asukoht ja järjestus on kõikides tabelites samad – erinevad ainult numbrid.
Saate koguda andmeid kõigilt lehtedelt (ärge summeerige, vaid asetage need üksteise alla "hunnikusse") vaid ühe valemiga:
Nagu näete, on idee sama: liimime lingi antud lehe soovitud lahtrisse ja KAUDSEID muudab selle "live". Mugavuse huvides lisasin tabeli kohale veergude tähed (B,C,D) ja paremale ridade numbrid, mis tuleb igalt lehelt võtta.
Lõksud
Kui kasutate KAUDSEID (Kaudne) peate meeles pidama selle nõrkusi:
- Kui lingite mõne muu failiga (liimides nurksulgudesse faili nime, lehe nime ja lahtri aadressi), töötab see ainult siis, kui algfail on avatud. Kui me selle sulgeme, kuvatakse tõrketeade #LINK!
- INDIRECT ei saa viidata dünaamilisele nimega vahemikule. Staatilisel - pole probleemi.
- INDIRECT on lenduv või lenduv funktsioon, st see arvutatakse ümber lehe mis tahes lahtri mis tahes muutuse jaoks, mitte ainult lahtrite mõjutamiseks, nagu tavafunktsioonide puhul. See mõjub jõudlusele halvasti ja suurte KAUDSETE tabelitega on parem mitte end ära lasta.
- Kuidas luua dünaamilist ulatust automaatse suuruse määramisega
- Vahemiku akna summeerimine lehel funktsiooni OFFSET abil