Sisällys
Ensi silmäyksellä (etenkin ohjetta lukiessa) toiminto EPÄSUORISTA (Epäsuora) näyttää yksinkertaiselta ja jopa tarpeettomalta. Sen ydin on muuttaa linkiltä näyttävä teksti täysimittaiseksi linkiksi. Nuo. jos meidän on viitattava soluun A1, voimme joko tehdä tavallisesti suoran linkin (kirjoita yhtäläisyysmerkki D1:een, napsauta A1 ja paina Enter), tai voimme käyttää EPÄSUORISTA samaan tarkoitukseen:
Huomaa, että funktion argumentti – viittaus A1:een – kirjoitetaan lainausmerkkeihin, joten se on itse asiassa tekstiä tässä.
"No, okei", sanot. "Ja mitä hyötyä siitä on?"
Mutta älä tuomitse ensivaikutelman perusteella – se on petollinen. Tämä ominaisuus voi auttaa sinua monissa tilanteissa.
Esimerkki 1. Transponoi
Genren klassikko: sinun on käännettävä pystysuuntaista halkaisijaa
ura vaakasuoraan (transponoi). Tietenkin voit käyttää erityistä lisäosaa tai toimintoa TRANSP (TRANSPOSI) taulukkokaavassa, mutta voit pärjätä meidän kanssamme EPÄSUORISTA:
Logiikka on yksinkertainen: saadaksesi seuraavan solun osoitteen, liimaamme kirjaimen "A" erikoismerkillä "&" ja nykyisen solun sarakenumerolla, jonka funktio antaa meille KOLUMNI (SARAKE).
Käänteinen menettely on parempi tehdä hieman eri tavalla. Koska tällä kertaa meidän on muodostettava linkki soluihin B2, C2, D2 jne., on mukavampaa käyttää R1C1-linkkitilaa klassisen "meritaistelun" sijaan. Tässä tilassa solumme eroavat vain sarakkeen numerosta: B2=R1C2, C2 = R1C3, D2 = R1C4 ja niin edelleen
Tässä tulee esiin toinen valinnainen funktion argumentti. EPÄSUORISTA. Jos se on yhtä suuri VAHELEMINEN (VÄÄRÄ), voit asettaa linkin osoitteen R1C1-tilassa. Joten voimme helposti siirtää vaaka-alueen takaisin pystysuoraan:
Esimerkki 2. Summa väliltä
Olemme jo analysoineet yhtä tapaa summata tietyn kokoinen ikkuna (alue) arkilla funktion avulla HÄVITTÄMINEN (OFFSET). Samanlainen ongelma voidaan ratkaista myös käyttämällä EPÄSUORISTA. Jos meidän on tehtävä yhteenveto vain tietystä aikajaksosta, voimme liimata sen palasista ja muuttaa sen sitten täysimittaiseksi linkiksi, jonka voimme lisätä funktion sisään SUMMA (SUMMA):
Esimerkki 3. Smart table -pudotusluettelo
Joskus Microsoft Excel ei käsittele älykkäiden taulukoiden nimiä ja sarakkeita täydellisinä linkkeinä. Joten esimerkiksi kun yrität luoda avattavaa luetteloa (välilehti Tiedot – Tietojen validointi) sarakkeen perusteella Työntekijät älypöydältä Ihmiset saamme virheilmoituksen:
Jos "käärimme" linkin funktiollamme EPÄSUORISTA, niin Excel hyväksyy sen helposti ja avattava luettelomme päivittyy dynaamisesti, kun lisäät uusia työntekijöitä älykkään taulukon loppuun:
Esimerkki 4. Katkeamattomat linkit
Kuten tiedät, Excel korjaa automaattisesti kaavoissa olevat viiteosoitteet, kun se lisää tai poistaa arkille rivisarakkeita. Useimmissa tapauksissa tämä on oikein ja kätevää, mutta ei aina. Oletetaan, että meidän on siirrettävä nimet työntekijähakemistosta raporttiin:
Jos laitat tavallisia linkkejä (kirjoita =B2 ensimmäiseen vihreään soluun ja kopioi se alas), niin kun poistat esimerkiksi Dashan, saamme #LINKKI! virhe häntä vastaavassa vihreässä solussa. (#VIITE!). Jos toimintoa käytetään linkkien luomiseen EPÄSUORISTA ei tule sellaista ongelmaa.
Esimerkki 5: Tietojen kerääminen useilta arkeilta
Oletetaan, että meillä on 5 arkkia samantyyppisillä raporteilla eri työntekijöiltä (Mihail, Elena, Ivan, Sergey, Dmitry):
Oletetaan, että tavaroiden ja kuukausien muoto, koko, sijainti ja järjestys ovat kaikissa taulukoissa samat – vain numerot eroavat.
Voit kerätä tietoja kaikista taulukoista (älä summaa niitä, vaan laita ne toistensa alle "kasaan") yhdellä kaavalla:
Kuten näette, idea on sama: liimaamme linkin haluttuun arkin soluun ja EPÄSUORISTA muuttaa sen "liveksi". Lisäsin mukavuuden vuoksi taulukon yläpuolelle sarakkeiden kirjaimet (B,C,D) ja oikealle - rivinumerot, jotka on otettava jokaiselta arkilta.
karikot
Jos käytät EPÄSUORISTA (Epäsuora) sinun on muistettava sen heikkoudet:
- Jos linkität toiseen tiedostoon (liimaamalla tiedoston nimen hakasulkeisiin, arkin nimen ja solun osoitteen), se toimii vain, kun alkuperäinen tiedosto on auki. Jos suljemme sen, saamme virheilmoituksen #LINK!
- INDIRECT ei voi viitata dynaamiseen nimettyyn alueeseen. Staattisesti - ei ongelmaa.
- EPÄSUORA on haihtuva tai "haihtuva" funktio, eli se lasketaan uudelleen minkä tahansa arkin solun muutoksen osalta, eikä vain vaikuttavien solujen osalta, kuten normaaleissa funktioissa. Tällä on huono vaikutus suorituskykyyn ja on parempi olla hurahtamatta suuriin INDIRECT-pöytiin.
- Dynaamisen alueen luominen automaattisen koon muuttamisen avulla
- Summaus alueen ikkunasta arkille OFFSET-toiminnolla