Excelin OFFSET-toiminto hidastaa työkirjan laskemista. On parempi vaihtoehto: INDEXin epätavallinen syntakse.
Tämä on kapealla kärjellä. On hämmästyttävän joustava toiminto nimeltä OFFSET. Se on joustava, koska se voi osoittaa erikokoiselle alueelle, joka lasketaan lennon aikana. Jos joku muuttaa alla olevan kuvan # Qtrs-pudotusvalikkoa H1: ssä 3: sta 4: een, OFFSETin neljäs argumentti varmistaa, että alue laajenee neljään sarakkeeseen.
Laskentataulukon gurut vihaavat OFFSETia, koska se on haihtuva toiminto. Jos siirryt täysin etuyhteydettömään soluun ja syötät luvun, kaikki OFFSET-toiminnot lasketaan. Vaikka tällä solulla ei olisi mitään tekemistä H1: n tai B2: n kanssa. Suurimman osan ajasta, Excel on erittäin varovainen tuhlata aikaa vain laskettavien solujen laskemiseen. Mutta kun otat käyttöön OFFSET-toiminnon, kaikki OFFSET-solut sekä kaikki OFFSETin alaviivat alkavat laskea jokaisen laskentataulukon muutoksen jälkeen.
Tuomitsin vuoden 2013 ModelOff-finaalit New Yorkissa, kun muutama australialainen ystäväni osoitti outoa kiertotapaa. Alla olevassa kaavassa on kaksoispiste ennen INDEX-funktiota. Normaalisti alla esitetty INDEX-toiminto palauttaisi 1403 solusta D2. Mutta kun laitat kaksoispisteen INDEX-funktion kummallekin puolelle, se alkaa palauttaa solun osoitteen D2 D2: n sisällön sijaan. Tämä on villiä, että se toimii.
Miksi tällä on merkitystä? INDEX ei ole epävakaa. Saat kaiken OFFSETin joustavan hyvyyden ilman aikaa imevää uudelleenlaskentaa uudestaan ja uudestaan.
Olen oppinut tämän vihjeen ensin Dan Mayohilta Fintegasta. Kiitos Access Analyticille tämän ominaisuuden ehdottamisesta.
Katso video
Videon transkriptio
Opi Excel podcastista, jakso 2048 -: Hakemisto korvaa haihtuvan offsetin!
Hei, podcastin kaikkia tämän kirjan vinkkejä. Napsauta oikeassa yläkulmassa olevaa ”i” päästäksesi soittolistaan!
Selvä, OFFSET on upea toiminto! OFFSET antaa meille mahdollisuuden määrittää vasemman yläkulman solu ja määrittää sitten muuttujien avulla siitä, kuinka monta riviä alas, kuinka monta riviä yli, ja määritellä sitten muoto, ok. Joten tässä, jos haluan lisätä tai tehdä keskimääräisen, sanotaan 3/4, tämä kaava täällä tarkastelee kaavaa. OFFSETin mukaan aloitamme B2: sta alkaen Q1: stä, menemme alas 0, yli 0, muoto on 1 rivi pitkä ja se on H1, toisin sanoen 3 solua leveä, kunnossa. Joten tässä tapauksessa kaikki, mitä todella teemme, on muuttaa sitä, kuinka monta solua lisäämme, aloitamme aina takaisin B2-, B3- tai B4-sarjoihin, kun kopioin alas, ja sitten se päättää, kuinka monta solua leveä. Selvä, OFFSET on tämä hieno asia, se tekee kaikenlaisia upeita toimintoja, mutta tässä on hässäkkä, se on epävakaa!Mikä tarkoittaa, että vaikka jotain ei olisikaan laskentaketjussa, Excel vie aikaa laskea se uudelleen, mikä hidastaa asioita.
Tämä hämmästyttävä INDEX-versio, oikein, normaalisti, jos pyytäisin näiden 4 solun INDEKSIä, 3, se palauttaa numeron 1403. Kuitenkin, kun laitan kaksoispisteen joko INDEXin eteen tai sen jälkeen, tapahtuu jotain aivan erilaista, tarkastelemme tätä kaavaa täällä. Joten 4 solun indeksi, minkä haluan, haluan kolmannen, mutta näet siellä: a. Joten aiomme aina siirtyä B2: E2: sta, ja näytän sinulle, jos menemme kaavoihin, arvioi kaava, kunnossa, joten täällä lasketaan luku 3. Ja tässä INDEX seuraavalla: sille, sen sijaan, että kerro meille 1403, joka on, kuinka INDEX normaalisti laskisi, se palauttaa D2 dollaria, ja sitten KESKIARVO tekee näiden 3 keskiarvon. Hämmästyttävää, tapa, jolla tämä toimii, ja lisäetu, se ei ole haihtuva, kunnossa,ja tätä voidaan käyttää jopa korvaamaan uskomattoman monimutkainen OFFSET.
Joten tässä tämän offsetin perusteella me perustamme näihin arvoihin. Jos valitsen Q2 ja Central, ok, nämä kaavat käyttävät MATCH ja COUNTIF selvittääkseen kuinka monta riviä alaspäin, kuinka monta saraketta yli, kuinka pitkä, kuinka leveä. Ja sitten OFFSET käyttää kaikkia näitä arvoja mediaanin selvittämiseen. Teemme vain testin varmistaaksemme, että se toimii, joten = sinisen alueen MEDIAN, parempi olla 71, kunnossa, ja valitsemme täältä jotain muuta, Q3 ja West, joten. Paina F2, aion vain vetää tämän yli ja muuttaa sen kokoa kirjoittaa kaava uudelleen, paina Enter, ja se toimii OFFSETin kanssa.
No, tässä on INDEX, minulla on kaksoispiste keskellä, INDEX vasemmalla puolella ja INDEX oikealla puolella, katsokaa, että tämä asia lasketaan Arvioi kaava. Joten se alkaa, arvioi, arvioi ja juuri tässä, että INDEX muuttaa sen soluosoitteeksi. Joten H16 on 1., länsi, Q3, ja oikealla puolella oleva arvioi, pari lisää, ja sitten oikealle hänelle se muuttuu I20: ksi. Joten viileä, viileä haihtumaton korvaamaan OFFSET INDEX-toiminnolla. Selvä, tämä vihje ja paljon muuta tässä kirjassa, napsauta kirjainta oikeassa yläkulmassa ostaaksesi kirjan.
Selvä yhteenveto: OFFSET, mahtava, joustava toiminto, kun hallitset, voit tehdä kaikenlaisia asioita. Osoita muuttuvaa vasemman yläkulman solua, osoita aluetta, jolla on vaihteleva muoto, mutta se on haihtuva, ja niin he laskevat jokaisessa laskelmassa. Excel tekee yleensä älykkään laskennan tai laskee laskettavat solut uudelleen, mutta OFFSET-toiminnolla se lasketaan aina. OFFSETin sijaan voit käyttää INDEX: tai: INDEX tai jopa INDEX: INDEX, milloin tahansa INDEX: ssä on kaksoispiste vieressä, se palauttaa solun osoitteen solun arvon sijasta. Ja etu on, että hakemisto ei ole epävakaa!
OK, haluan kiittää sinua vierailustasi. Nähdään ensi kerralla uudesta netcastista!
Lataa tiedosto
Lataa esimerkkitiedosto täältä: Podcast2048.xlsm
