Kuinka jäädyttää solu Excel-kaavassa

Usein käyttäjät kohtaavat tarpeen kiinnittää solu kaavaan. Se ilmenee esimerkiksi tilanteissa, joissa halutaan kopioida kaava, mutta jotta linkki ei liiku ylös ja alas yhtä monta solua kuin se on kopioitu alkuperäisestä sijainnistaan.

Tässä tapauksessa voit korjata soluviittauksen Excelissä. Ja tämä voidaan tehdä useilla tavoilla kerralla. Katsotaanpa tarkemmin, kuinka tämä tavoite saavutetaan.

Mikä on Excel-linkki

Levy koostuu soluista. Jokainen niistä sisältää erityistä tietoa. Muut solut voivat käyttää sitä laskelmissa. Mutta miten he ymmärtävät, mistä tiedot saadaan? Se auttaa heitä luomaan linkkejä.

Jokainen linkki osoittaa solun, jossa on yksi kirjain ja yksi numero. Kirjain edustaa saraketta ja numero riviä. 

Linkkejä on kolmenlaisia: absoluuttinen, suhteellinen ja sekoitettu. Toinen on asetettu oletusarvoisesti. Absoluuttinen viittaus on sellainen, jolla on kiinteä osoite sekä sarakkeelle että sarakkeelle. Vastaavasti sekoitettu on se, jossa joko erillinen sarake tai rivi on kiinteä.

1 menetelmä

Voit tallentaa sekä sarake- että riviosoitteet seuraavasti:

1. Napsauta kaavan sisältävää solua.
2. Napsauta tarvitsemamme solun kaavapalkkia.
3. Paina F4.

Tämän seurauksena soluviittaus muuttuu absoluuttiseksi. Se voidaan tunnistaa tunnusomaisesta dollarimerkistä. Jos esimerkiksi napsautat solua B2 ja sitten F4, linkki näyttää tältä: $B$2.

Mitä dollarimerkki tarkoittaa ennen osoitteen solukohtaista osaa?

1. Jos se sijoitetaan kirjaimen eteen, se osoittaa, että sarakeviittaus pysyy samana riippumatta siitä, mihin kaava on siirretty.
2. Jos dollarimerkki on numeron edessä, merkkijono on kiinnitetty. 

2 menetelmä

Tämä menetelmä on melkein sama kuin edellinen, vain sinun on painettava F4 kahdesti. esimerkiksi jos meillä olisi solu B2, niin sen jälkeen siitä tulee B$2. Yksinkertaisesti sanottuna onnistuimme tällä tavalla korjaamaan linjan. Tässä tapauksessa sarakkeen kirjain muuttuu.

Se on erittäin kätevää esimerkiksi taulukoissa, joissa sinun on näytettävä toisen solun sisältö ylhäältä alassolussa. Sen sijaan, että tekisit tällaisen kaavan monta kertaa, riittää, että korjaat rivin ja annat sarakkeen muuttua.

3 menetelmä

Tämä on täysin sama kuin edellinen menetelmä, vain sinun on painettava F4-näppäintä kolme kertaa. Tällöin vain viittaus sarakkeeseen on absoluuttinen ja rivi pysyy kiinteänä.

4 menetelmä

Oletetaan, että meillä on absoluuttinen viittaus soluun, mutta tässä se oli tarpeen tehdä suhteelliseksi. Voit tehdä tämän painamalla F4-näppäintä niin monta kertaa, että linkissä ei ole $-merkkejä. Sitten siitä tulee suhteellinen, ja kun siirrät tai kopioit kaavan, sekä sarakkeen osoite että riviosoite muuttuvat.

Solujen kiinnitys laajalle alueelle

Näemme, että yllä olevat menetelmät eivät aiheuta vaikeuksia suorittaa. Mutta tehtävät ovat erityisiä. Ja esimerkiksi mitä tehdä, jos meillä on useita kymmeniä kaavoja kerralla, joiden linkit on muutettava absoluuttisiksi. 

Valitettavasti tavalliset Excel-menetelmät eivät saavuta tätä tavoitetta. Tätä varten sinun on käytettävä erityistä lisäosaa nimeltä VBA-Excel. Se sisältää monia lisäominaisuuksia, joiden avulla voit suorittaa yleisiä tehtäviä Excelillä paljon nopeammin.

Se sisältää yli sata käyttäjän määrittämää funktiota ja 25 erilaista makroa, ja sitä myös päivitetään säännöllisesti. Sen avulla voit parantaa työtä lähes kaikilla osa-alueilla:

1. Solut.
2. Makro.
3. Eri tyyppisiä toimintoja.
4. Linkkejä ja taulukoita.

Erityisesti tämän apuohjelman avulla voit korjata linkkejä useisiin kaavoihin kerralla. Voit tehdä tämän suorittamalla seuraavat vaiheet:

1. Valitse alue.
2. Avaa VBA-Excel-välilehti, joka tulee näkyviin asennuksen jälkeen. 
3. Avaa "Toiminnot" -valikko, jossa on "Lukitse kaavat" -vaihtoehto.
   

   

4. Seuraavaksi näyttöön tulee valintaikkuna, jossa sinun on määritettävä vaadittu parametri. Tämän lisäyksen avulla voit kiinnittää sarakkeen ja sarakkeen erikseen, yhdessä ja myös poistaa jo olemassa olevan kiinnityksen paketin kanssa. Kun vaadittu parametri on valittu vastaavalla valintanapilla, sinun on vahvistettava toiminnot napsauttamalla "OK".

esimerkki

Otetaan esimerkki selvemmäksi. Oletetaan, että meillä on tietoa, joka kuvaa tavaran kustannuksia, sen kokonaismäärää ja myyntituloja. Ja meidän edessämme on saada taulukko määrään ja kustannuksiin perustuen automaattisesti määrittämään, kuinka paljon rahaa onnistuimme ansaitsemaan vähentämättä tappioita.

Esimerkissämme tätä varten sinun on syötettävä kaava =B2*C2. Se on melko yksinkertaista, kuten näet. Hänen esimerkkinsä avulla on erittäin helppo kuvata, kuinka voit korjata solun tai sen yksittäisen sarakkeen tai rivin osoitteen. 

Tässä esimerkissä voit tietysti yrittää vetää kaavaa alas käyttämällä automaattisen täytön merkkiä, mutta tässä tapauksessa solut muuttuvat automaattisesti. Joten solussa D3 on toinen kaava, jossa numerot korvataan vastaavasti 3:lla. Lisäksi kaavion mukaan - D4 - kaava saa muotoa = B4 * C4, D5 - samalla tavalla, mutta numero 5 ja niin edelleen.

Jos se on tarpeen (useimmissa tapauksissa se osoittautuu), ei ole ongelmia. Mutta jos sinun on korjattava kaava yhdessä solussa, jotta se ei muutu vedettäessä, tämä on jonkin verran vaikeampaa. 

Oletetaan, että meidän on määritettävä dollaritulot. Laitetaan se soluun B7. Mennään hieman nostalgiseen ja ilmoitetaan 35 ruplaa dollarilta. Näin ollen dollareiden tulojen määrittämiseksi on tarpeen jakaa ruplamäärä dollarin vaihtokurssilla.

Tältä se näyttää esimerkissämme.

Jos yritämme edellisen version tavoin määrätä kaavan, epäonnistumme. Vastaavasti kaava muuttuu sopivaksi. Esimerkissämme se on seuraava: =E3*B8. Tästä voimme nähdä. että kaavan ensimmäinen osa on muuttunut E3:ksi, ja asetamme itsellemme tämän tehtävän, mutta meidän ei tarvitse muuttaa kaavan toista osaa B8:ksi. Siksi meidän on muutettava viittaus ehdottomaksi. Voit tehdä tämän painamatta F4-näppäintä, vain asettamalla dollarin merkin.

Kun muutimme viittauksen toiseen soluun absoluuttiseksi, se suojattiin muutoksilta. Nyt voit turvallisesti vetää sitä käyttämällä automaattisen täytön kahvaa. Kaikki kiinteät tiedot pysyvät ennallaan kaavan sijainnista riippumatta ja sitomattomat tiedot muuttuvat joustavasti. Kaikissa soluissa tällä rivillä kuvatut ruplatulot jaetaan samalla dollarin vaihtokurssilla.

Itse kaava näyttää tältä:

=D2/$B$7

Huomio! Olemme osoittaneet kaksi dollarimerkkiä. Tällä tavalla näytämme ohjelmalle, että sekä sarake että rivi on korjattava.

Soluviittaukset makroissa

Makro on aliohjelma, jonka avulla voit automatisoida toimintoja. Toisin kuin Excelin vakiotoiminnot, makron avulla voit määrittää välittömästi tietyn solun ja suorittaa tiettyjä toimintoja vain muutamalla koodirivillä. Hyödyllinen tietojen eräkäsittelyssä, esimerkiksi jos ei ole mahdollista asentaa lisäosia (käytetään esimerkiksi yrityksen tietokonetta, ei henkilökohtaista).

Ensin sinun on ymmärrettävä, että makron avainkäsite on objektit, jotka voivat sisältää muita objekteja. Työkirjat-objekti vastaa sähköisestä kirjasta (eli asiakirjasta). Se sisältää Sheets-objektin, joka on kokoelma avoimen asiakirjan kaikista arkeista. 

Vastaavasti solut ovat Cells-objekteja. Se sisältää kaikki tietyn arkin solut.

Jokainen objekti on tarkennettu suluissa olevilla argumenteilla. Solujen tapauksessa niihin viitataan tässä järjestyksessä. Rivin numero luetellaan ensin ja sen jälkeen sarakkeen numero tai kirjain (molemmat muodot ovat hyväksyttäviä).

Esimerkiksi koodirivi, joka sisältää viittauksen soluun C5, näyttää tältä:

Työkirjat ("Kirja2.xlsm"). Taulukot ("Lista2"). Solut(5, 3)

Työkirjat ("Kirja2.xlsm"). Taulukot ("Lista2"). Solut(5, "C")

Voit käyttää solua myös objektin avulla Siisti. Yleensä se on tarkoitettu antamaan viittaus alueeseen (jonka elementit voivat muuten olla myös absoluuttisia tai suhteellisia), mutta voit antaa yksinkertaisesti solun nimen, samassa muodossa kuin Excel-dokumentissa.

Tässä tapauksessa viiva näyttää tältä.

Työkirjat("Kirja2.xlsm").Sheets("Lista2").Alue("C5")

Saattaa tuntua, että tämä vaihtoehto on kätevämpi, mutta kahden ensimmäisen vaihtoehdon etuna on, että voit käyttää muuttujia suluissa ja antaa linkin, joka ei ole enää absoluuttinen, vaan jotain suhteellista, mikä riippuu tuloksista. laskelmat.

Siten makroja voidaan käyttää tehokkaasti ohjelmissa. Itse asiassa kaikki viittaukset soluihin tai alueisiin ovat absoluuttisia, ja siksi ne voidaan myös korjata niiden avulla. Totta, se ei ole niin kätevää. Makrojen käyttö voi olla hyödyllistä kirjoitettaessa monimutkaisia ​​ohjelmia, joissa algoritmissa on useita vaiheita. Yleensä tavallinen tapa käyttää absoluuttisia tai suhteellisia viittauksia on paljon kätevämpi. 

Päätelmät

Selvitimme, mikä soluviittaus on, miten se toimii, mihin se on tarkoitettu. Ymmärsimme eron absoluuttisten ja suhteellisten viittausten välillä ja selvitimme, mitä on tehtävä, jotta yksi tyyppi muuttuisi toiseksi (yksinkertaisin sanoin, korjaa osoite tai irrota se). Selvitimme, kuinka voit tehdä sen välittömästi suurella määrällä arvoja. Nyt sinulla on joustavuus käyttää tätä ominaisuutta oikeissa tilanteissa.