Kohdealueen kuvaaminen Excelissä (Offset)

Kohdealueen kuvaaminen Excelissä (Offset)

Luin yläasteella erittäin lyhyen saksan ja syystä että en päässyt sitä koskaan treenaamaan, opin ulkoa vain muutaman merkityksettömän virkkeen. Kuten reittiohjeen: ”Gehen Sie diese Straße entlang und die erste Querstraße rechts und dann links und Sie sind da.”

Vitsikästä sinänsä, että sain kerran jopa hyödyntää täsmäosaamistani, kun saksankielinen turistipariskunta kysyi Helsingissä Sokoksen nurkalta reittiä Kauppatorille. Kykenin sopivasti sijoittamaan rechtsin ja linksin oikeisiin kohtiin ja ersten lisäksi muistin zweiterin. Seuraavaan heidän kysymykseensä – jota en lainkaan ymmärtänyt – saatoin käyttää toista oppimaani virkettä: ”Ich kann kein Deutch.” Eivät siis menneet nekään saksan tunnit hukkaan: olin saanut hyödyntää lähes 100 % saksan taidoistani.

Määränpään eli alueen kuvaaminen Excelissä

Excelissä voi kuvata reitin haluttuun osoitteeseen/alueeseen OFFSET (SIIRTYMÄ) –funktion avulla ja tästä täsmätiedosta on ollut minulle huomattavasti useammin hyötyä. Pelkän reitin lisäksi funktiolla määritetään myös määränpään koko eli korkeus ja leveys. Funktio siis kuvaa halutun etäisyyden päässä olevan halutun kokoisen alueen. Funktiolla saa hienoja juttuja aikaan erityisesti erilaisiin hakufunktioihin ja kaavioihin yhdistettynä.

Funktiolle annetaan seuraavat argumentit:

=OFFSET(solu josta aloitetaan; siirtymärivien lkm; siirtymäsarakkeiden lkm; korkeus riveinä; leveys sarakkeina)

Seuraava esimerkkifunktio antaa tuloksena alueen, joka on A5-solusta lähtien kolme riviä alempana ja 1 sarake oikealla ja alue on 1 riviä korkea sekä 6 saraketta leveä.

=OFFSET(A5; 3; 1; 1; 6)
=SIIRTYMÄ(A5; 3; 1; 1; 6)

offsetalue

Jos funktiossa käytetään negatiivisia rivi- ja sarakesiirtymiä, suunta on ylös ja vasemmalle. Funktiosta ei sellaisenaan ole mitään iloa, ja se antaakin virheilmoituksen soluun kirjoitettuna. Kaava antaa tuloksena alueen, joten aluetta voi käyttää hyödyksi muiden funktioiden yhteydessä.

Esimerkiksi =SUM(OFFSET(A5; 3; 1; 1; 6)) [=SUMMA(SIIRTYMÄ(A5; 3; 1; 1; 6))] laskee alueen luvut yhteen. Noh, eipä kaavasta ole tässäkään muodossa mitään iloa, sillä luvuthan voi laskea yhteen yksinkertaisesti kaavalla =SUM(B8:G8).

Todennäköisesti oletkin jo tähän mennessä arvannut jujun. OFFSET-funktiota käytetään tilanteissa, joissa halutaan muodostaa dynaamisesti sijainniltaan ja/tai kooltaan muuttuvia alueita esimerkiksi sen perusteella, mitä käyttäjä on syöttänyt.

Seuraavassa esimerkkitaulukossa lasketaan summa soluun D3 solujen D1 (tuote) ja D2 (laskettavien kuukausien lukumäärä) perusteella.

  • Solussa D3 on kaava:  =SUM(OFFSET(A5; F1; 1; 1; D2))
  • solussa F1 on apukaavana MATCH (VASTINE) -funktio, joka tutkii monennelta riviltä syötetty tuote löytyy, jotta OFFSET osaa siirtyä halutun määrän rivejä alas:
    =MATCH(D1;  A6:A15;  0)

Kokeile upotetussa taulukossa:

MATCH (VASTINE) -funktion voi mainiosti sijoittaa yhteenlaskukaavaan osoitteen F1 tilalle: =SUM(OFFSET(A5; MATCH(D1; A6:A15; 0); 1; 1; D2), jotta erillistä apukaavasolua ei tarvita.

Vastaa

Täytä tietosi alle tai klikkaa kuvaketta kirjautuaksesi sisään:

WordPress.com-logo

Olet kommentoimassa WordPress.com -tilin nimissä. Log Out / Muuta )

Twitter-kuva

Olet kommentoimassa Twitter -tilin nimissä. Log Out / Muuta )

Facebook-kuva

Olet kommentoimassa Facebook -tilin nimissä. Log Out / Muuta )

Google+ photo

Olet kommentoimassa Google+ -tilin nimissä. Log Out / Muuta )

Muodostetaan yhteyttä palveluun %s