Merwyn Englannista lähetti tämän ongelman.
Onko solussa B2 yksi kaava, joka voidaan kopioida toisistaan ja alaspäin kertotaulukon luomiseksi?
Merwynin tavoitteena on kirjoittaa yksi kaava B2: een, joka voidaan helposti kopioida kaikkiin 144 soluun kertolaskujen luomiseksi.
Hyökkäämme tähän Excel-aloittelijana ja katsotaan, mitä karhuja törmäämme. Ensimmäinen reaktio saattaa olla kaavan saaminen B2: ssa =A2*B1. Tämä kaava tuottaa oikean tuloksen, mutta se ei sovellu Merwynin tehtävään.
Kun kopioit tämän kaavan solusta B2 soluun C2, myös kaavassa mainitut solut siirtyvät yhden solun yli. Kaavasta, joka =A2*B1nyt oli, tulee =C1*B2. Tämä on Microsoft Exceliin suunniteltu ominaisuus, jota kutsutaan suhteelliseksi kaavaviitteeksi. Kun kopioit kaavaa, kaavan soluviitteet siirtävät myös vastaavan määrän soluja poikki ja alaspäin.
On aikoja, jolloin et halua, että Excel näyttää tämän käyttäytymisen, ja nykyinen tapaus on yksi niistä. Voit estää Exceliä vaihtamasta viittausta soluja kopioimalla lisäämällä "$" viitteen osan eteen, jonka haluat jäädyttää. Esimerkkejä:
- $ A1 kertoo Excelille, että haluat aina viitata sarakkeeseen A.
- B $ 1 kertoo Excelille, että haluat aina viitata riviin 1.
- $ B $ 1 kertoo Excelille, että haluat aina viitata soluun B1.
Tämän koodin oppiminen vähentää dramaattisesti laskentataulukoiden luomiseen tarvittavaa aikaa. Sen sijaan, että tarvitsisi syöttää 144 kaavaa, Merwyn voi käyttää tätä lyhennettä syöttämään yhden kaavan ja kopioimaan sen kaikkiin soluihin.
Merwynin kaavaa tarkasteltaessa =B1*A2ymmärrämme, että lausekkeen "B1" osan tulisi aina osoittaa rivin 1 numeroa. Tämä tulisi kirjoittaa uudestaan nimellä "B $ 1". Kaavan A2-osan tulisi aina osoittaa sarakkeessa A olevaa lukua. Tämä tulisi kirjoittaa uudestaan nimellä "$ A2". Joten uusi kaava solussa B2 on =B$1*$A2.
Kun olen kirjoittanut kaavan B2: een, voin kopioida ja liittää sen sopivaan alueeseen. Excel noudattaa ohjeita ja kopion tekemisen jälkeen löydän seuraavat kaavat:
- Solu M2 sisältää
=M$1*$A2 - Solu B13 sisältää
=B$1*$A13 - Solu M13 sisältää
=M$1*$A13
Jokaisella tällaisella kaavalla on oma nimi. Kaavoja, joissa ei ole dollarin merkkejä, kutsutaan suhteellisiksi kaavoiksi. Kaavoja, joissa sekä rivi että sarake on lukittu dollarimerkillä, kutsutaan absoluuttisiksi kaavoiksi. Kaavoja, joissa joko rivi tai sarake on lukittu dollarimerkillä, kutsutaan sekakaavoiksi.
On lyhytmenetelmä dollarin merkkien syöttämiseen. Kun kirjoitat kaavaa ja viimeistelet soluviitteen, voit vaihtaa 4 viitetyypin välillä painamalla näppäintä. Oletetaan, että aloitit kaavan kirjoittamisen ja kirjoitit =100*G87.
- Hit F4 ja kaava muuttuu muotoon
=100*$G$87 - Paina F4 uudelleen ja kaava muuttuu muotoon
=100*G$87 - Paina F4 uudelleen ja kaava muuttuu muotoon
=100*$G87 - Paina F4 uudelleen ja kaava palaa alkuperäiseen
=100*G87
Voit keskeyttää kaavan syöttämisen aikana jokaisessa soluviitteessä osuman F4, kunnes saat oikean viitetyypin. Näppäimet Merwynin kaavan syöttämiseksi yllä ovat =B1*A1.
Yksi suosikkiseoksistani sekakaavan viitteistä tulee esiin, kun minulla on pitkä luettelo sarakkeesta A. Kun haluan nopean ja likaisen menetelmän kaksoiskappaleiden löytämiseksi, kirjoitan joskus tämän kaavan soluun B4 ja kopioin sen sitten alas:
=VLOOKUP(A4,$A$2:$A3,1,FALSE)
Huomaa, että VLOOKUP-kaavan toinen termi käyttää sekä absoluuttista ($ A $ 2) että sekoitettua ($ A3) viittausta hakualueen kuvaamiseen. Englanniksi käsken Exceliä etsimään aina solusta $ A $ 2 nykyisen solun yläpuolella olevan sarakkeen A soluun. Heti kun Excel havaitsee päällekkäisen arvon, tämän kaavan tulos muuttuu arvosta # N / A! arvoon.
Luovasti käyttämällä $ -soluviittauksia voit varmistaa, että yksi kaava voidaan kopioida moniin soluihin oikeilla tuloksilla.
