Useimmat ihmiset tuntevat suhteellisten viittausten käyttämisen Excelissä^{(in Excel)} . Tämä johtuu siitä, että Excel^(Excel) -laskentataulukoiden soluviittaukset käyttävät oletuksena suhteellista viitemenetelmää.

Joskus suhteelliset viittaukset ärsyttävät kuitenkin. Se muuttaa soluviittauksia aina, kun kopioit soluja tai täytät sarakkeita ja rivejä. Jos et halua viitteiden muuttuvan, sinun on käytettävä absoluuttisia viittauksia tai sekaviittauksia (mukaan lukien suhteelliset ja absoluuttiset viittaukset).

Absoluuttisessa viittauksessa sekä sarake- että riviviittaukset ovat "lukittuja", joten kumpikaan niistä ei muutu, kun kopioit tai täytät kyseisestä solusta.

Tässä artikkelissa tarkastellaan, kuinka voit käyttää absoluuttisia viittauksia saadaksesi Excelin^(Excel) käyttäytymään haluamallasi tavalla tietojen kanssa.

Kuinka suhteelliset viittaukset^(References) toimivat Excelissä^(Excel)

Kun syötät arvoja Excel - laskentataulukkoon, jokaiselle solulle on määritetty tietty kirjain ja numero. Tämä edustaa kyseisen solun saraketta ja riviä.

Esimerkiksi arvo "1" alla olevassa laskentataulukossa on sarakkeessa A ja rivissä 2. Joten "viittaus" tähän soluun on A2 .

Jos haluat suorittaa laskutoimituksen seuraavassa solussa tämän solun perusteella, lisäämällä siihen 1, kirjoitat seuraavan kaavan:

=A2+1

Tämä kaava lisää arvon A2:sta kaavaan, laskee sen ja tulostaa sitten tuloksen soluun, jossa tämä kaava on.

Kun painat Enter , näet tuloksen.

Suhteellisen viittauksen avulla sinun ei tarvitse kirjoittaa tätä kaavaa joka toiseen soluun. Sinun tarvitsee vain vetää alkuperäisen kaavan solun kulmaa alas niin pitkälle kuin haluat. 

Seuraavassa solussa viittauksesta A2 tulee A3. Sen alla olevassa solussa A3 muuttuu A4:ksi. Toisin sanoen Excel tietää, että haluat lisätä 1:n edelliseen soluun, joten Excel päivittää numeron (riviviitteen) vastaavasti, kun vedät alas.

Tämä toimii samalla tavalla, jos vedät kaavan sarakkeiden poikki. Numeron päivittämisen sijaan Excel päivittää viittauksen jälkimmäisen osan (sarakkeen) viittaamaan aina sen yläpuolella olevaan soluun.

Oikealla oleva sarake sisältää B2:n, sen oikealla puolella C2 ja niin edelleen.

Tämä on yksinkertainen esimerkki siitä, kuinka suhteellinen osoitus toimii sekä sarake- että soluviittauksissa.

Kuinka absoluuttiset viittaukset^(References) toimivat Excelissä^(Excel)

^(Absolute)Excelin ^(Excel)absoluuttisten viittausten avulla voit viitata samaan soluun sen sijaan, että Excel päivittää^{(Excel automatically update)} rivi- tai sarakeviittaukset automaattisesti puolestasi. "Sekalainen" viittaa, jos lukitset vain rivin tai sarakkeen, ja "absoluuttinen viittaus" on, kun lukitset molemmat.

Katsotaanpa joitain esimerkkejä.

Oletetaan, että laskentataulukon ylimmällä rivillä on 10, ja haluat jokaisen sen alla olevan rivin kertovan tämän luvun vasemmalla olevan solun numerolla. 

Voit tehdä tämän syöttämällä kaavan, joka näyttää tältä:

=B$2*A3

Tämä lukitsee "2"-viittauksen, joten se ei muuta riviviittausta, jos vedät tämän kaavan sisältävän solun alas sen alla oleviin soluihin. Koska A3 pysyy "lukitsemattomana", sekä rivi- että sarakeviittaukset muuttuvat edelleen automaattisesti ja viittaavat aina vasemmalla olevaan soluun.

Huomaat, että tämä toimii vain, koska vedät alas saman sarakkeen soluihin. Joten sinun ei tarvitse lukita saraketta (B) asettamalla dollarimerkki ($) sen eteen.

Ongelma tässä on, että jos haluat käyttää samaa kaavaa alkuperäisen kaavan oikealla puolella, "B"-viittaus muuttuu, eikä kaava enää viittaa B2:een tarkoitetulla tavalla.

Katsotaanpa, kuinka käyttää absoluuttisia viittauksia sekaviittauksien sijaan, jotta täyttö molempiin suuntiin toimii oikein.

Absoluuttisten ^(Absolute) viittausten^(References) oikea käyttö Excelissä^(Excel)

Käyttääksesi oikeaa viittausta tässä kaavassa sinun on harkittava tarkasti, mitä yrität tehdä.

Tässä tapauksessa haluamme seuraavan käyttäytymisen oikealle täytettäessä.

• Viittaa aina arvoon solussa B2
• Viittaa aina sarakkeen A arvoon
• Siirrä sarakkeen A rivin viite kaavan nykyiseen riviin

Kun tarkastelet näitä käyttäytymismalleja, tiedät nyt, mitä sinun on "lukittava" ja mitä ei. Sekä "B" että "2" on lukittava (ennallaan). Myös sarake A on lukittava.

Joten kaavan B3 tulee näyttää tältä: =$B$2*$A3

Nyt kun vedät samaa solua joko alas tai ylös, kaava toimii tarkoitetulla tavalla.

Absoluuttisen viittauksen oikea käyttäminen voi olla hankalaa, joten on tärkeää harkita huolellisesti, kuinka haluat Excelin päivittävän kaavan, kun täytät sarakkeita tai rivejä^{(columns or rows)} kumpaankin suuntaan.

Pyöräily viitetyyppien^{(Reference Types)} läpi Excelissä^(Excel)

Voit nopeuttaa prosessia kirjoittaessasi kaavoja^{(typing formulas)} absoluuttisilla viittauksilla painamalla F4 - näppäintä, mikä tekee soluviittauksesta absoluuttisen.

Kohdistin voi olla soluviittauksen kummallakin puolella (tai jopa sen keskellä), kun painat F4, ja se muuntaa silti tämän yksittäisen viittauksen absoluuttiseksi.

Jos et halua absoluuttista (esimerkiksi sekoitettua sen sijaan), paina F4, kunnes viite näyttää haluamaltasi.

Jos haluat lisätä minkäänlaista viittausta muihin kaavan soluihin, aseta kohdistin sinne ja aloita F4-näppäimen selaaminen uudelleen.

Kun olet määrittänyt kaavan, paina Enter ja aloita laskentataulukon täyttäminen haluamaasi suuntaan. Jos asetat referenssit oikein, kaiken pitäisi toimia odotetulla tavalla.

How to Use Absolute References in Excel

Most people are familiar with using relative references in Excel. This is because cell references in Excel spreadsheets default to the relative reference method.

However, there are times when relative references get annoying. It will change cell references any time you copy cells or fill columns and rows. If you don’t want references to change, you’ll need to go with absolute references or mixed references (including relative and absolute references).

In an absolute reference, both the column and row references are “locked,” so neither of those change when you copy or fill from that cell.

This article will look at how you can use absolute references to make Excel behave the way you want with data.

How Relative References Work in Excel

When you enter values into an Excel spreadsheet, each cell has a specific letter and number assigned to it. This represents the column and row of that cell.

For example, the value of “1” in the spreadsheet below is in column A and row 2. So the “reference” to this cell is A2.

If you want to perform a calculation in the next cell based on this cell, by adding 1 to it, you will write the following formula:

=A2+1

This formula will insert the value from A2 into the formula, calculate it, and then output the result into the cell where this formula is.

When you press Enter, you’ll see the result.

With relative referencing, you don’t have to type this formula into every other cell. All you have to do is drag the corner of the cell with the original formula down as far as you’d like. 

In the next cell, the reference to A2 will become A3. In the cell below that, A3 will become A4. In other words, Excel knows that you want to add 1 to the previous cell, so Excel updates the number (the row reference) accordingly as you drag down.

This works in the same way if you drag the formula across columns. Instead of updating the number, Excel will update the latter part of the reference (the column) to always reference the cell above it.

The column to the right contains B2, to the right of that contains C2, and so on.

This is a simple example of how relative addressing works for both column and cell references.

How Absolute References Work in Excel

Absolute references in Excel let you reference the same cell, rather than letting Excel automatically update the row or column references for you. “Mixed” referencing is if you lock only the row or the column, and “absolute referencing” is when you lock both.

Let’s look at some examples.

Let’s say your spreadsheet has a “10” in a top row, and you want every row beneath it to multiply that number by the number in the cell to the left. 

To do this, you’d enter a formula that looks like this:

=B$2*A3

This locks the “2” reference so it won’t change the row reference if you drag the cell with this formula down to the cells below it. Since A3 remains “unlocked” both the row and column reference will still change automatically and always reference the cell to the left.

You will notice that this only works because you’re dragging down into cells in the same column. So you don’t have to lock column (B) by placing a dollar sign ($) in front of it.

The problem with this is that if you want to use the same formula to the right of the original formula, the “B” reference will change, and the formula will no longer reference B2 as intended.

Let’s take a look at how to use absolute references instead of mixed references to make filling in both directions work properly.

Correctly Using Absolute References in Excel

To use the correct referencing in this formula, you must consider precisely what you’re trying to do.

In this case, we want the following behaviors when filling to the right.

• Always reference the value in cell B2
• Always reference the value in column A
• Shift the reference of the row for column A to the current row of the formula

Looking at these behaviors, you now know what you need to “lock” and what you don’t. Both “B” and “2” need to be locked (unchanged). Also, column A needs to be locked.

So your formula in B3 needs to look like this: =$B$2*$A3

Now when you drag this same cell either down or up, the formula works as intended.

Correctly using absolute referencing can get tricky, so it’s essential to take the time to carefully consider how you want Excel to update the formula as you fill columns or rows in either direction.

Cycling Through Reference Types in Excel

You can speed along the process when you’re typing formulas with absolute references by pressing the F4 key, which will make the cell reference absolute.

The cursor can be on either side of the cell reference (or even in the middle of it) when you press F4, and it’ll still convert that single reference to absolute.

If you don’t want absolute (for example, mixed instead), keep tapping F4 until the reference looks the way you want.

If you want to add any kind of referencing to other cells in the formula, just place your cursor there and start cycling through F4 again.

Once you have configured your formula, just press Enter and begin filling your spreadsheet in any direction you like. If you set up your references right, everything should work just as expected.

Related posts

• Säilytä soluviittaukset, kun kopioit kaavan Excelissä

• Kuinka poistaa tyhjiä rivejä Excelissä

• Excelin Speak Cells -ominaisuuden käyttäminen

• Kuinka lisätä Excel-laskentataulukko Word-dokumenttiin

• Excelin mitä jos -analyysin käyttäminen

• Kuinka laskea Z-pisteet Excelissä

• Ryhmittele rivit ja sarakkeet Excel-laskentataulukossa

• Kuinka luoda useita linkitettyjä avattavia luetteloita Excelissä

• Kuinka poistaa ruudukkoviivat Excelissä

• 10 Excel-vinkkiä ja temppuja vuodelle 2019

• Kuinka luoda VBA-makro tai komentosarja Excelissä

• Kuinka jäljittää riippuvia Excelissä

• Kirjoita numerot nopeasti Wordissa ja Excelissä

• Kuinka kirjoittaa IF-kaava/-lauseke Excelissä

• 4 tapaa muuntaa Excel Google Sheetsiksi

• Käytä dynaamisten alueiden nimiä Excelissä joustaviin pudotusvalikkoihin

• Excel-tiedoston jakaminen helppoa yhteistyötä varten

• Kuinka yhdistää tietoja useisiin Excel-tiedostoihin

• Kuinka lajitella päivämäärän mukaan Excelissä

• Kuinka lisätä CSV tai TSV Excel-laskentataulukkoon