Kuidas külmutada lahter Exceli valemis

Sageli seisavad kasutajad silmitsi vajadusega kinnitada valemis lahter. Näiteks esineb see olukordades, kus soovite kopeerida valemit, kuid nii, et link ei liiguks üles-alla sama arvu lahtrite kaupa, kui see algsest asukohast kopeeriti.

Sel juhul saate lahtri viite Excelis parandada. Ja seda saab teha mitmel viisil korraga. Vaatame lähemalt, kuidas seda eesmärki saavutada.

Mis on Exceli link

Leht koosneb rakkudest. Igaüks neist sisaldab konkreetset teavet. Teised lahtrid saavad seda arvutustes kasutada. Aga kuidas nad aru saavad, kust andmeid saada? See aitab neil linke luua.

Iga link tähistab ühe tähe ja ühe numbriga lahtrit. Täht tähistab veergu ja number tähistab rida. 

Linke on kolme tüüpi: absoluutne, suhteline ja segatud. Teine on vaikimisi seatud. Absoluutne viide on selline, millel on nii veeru kui ka veeru fikseeritud aadress. Vastavalt sellele on segatud see, kus fikseeritakse kas eraldi veerg või rida.

1 meetod

Nii veergude kui ka ridade aadresside salvestamiseks toimige järgmiselt.

1. Klõpsake valemit sisaldaval lahtril.
2. Klõpsake vajaliku lahtri valemiribal.
3. Vajutage klahvi F4.

Selle tulemusena muutub lahtri viide absoluutseks. Seda saab ära tunda iseloomuliku dollarimärgi järgi. Näiteks kui klõpsate lahtril B2 ja seejärel F4, näeb link välja selline: $B$2.

Mida tähendab dollarimärk enne aadressi osa lahtri kohta?

1. Kui see asetatakse tähe ette, näitab see, et veeruviide jääb samaks, olenemata sellest, kuhu valem on teisaldatud.
2. Kui numbri ees on dollarimärk, näitab see, et string on kinnitatud. 

2 meetod

See meetod on peaaegu sama, mis eelmine, ainult peate kaks korda vajutama F4. näiteks kui meil oleks lahter B2, siis pärast seda saab sellest B$2. Lihtsamalt öeldes õnnestus sel viisil joon korda saada. Sel juhul muutub veeru täht.

See on väga mugav näiteks tabelites, kus alumises lahtris on vaja kuvada ülevalt teise lahtri sisu. Selle asemel, et sellist valemit mitu korda teha, piisab rea parandamisest ja veeru muutumisest.

3 meetod

See on täiesti sama, mis eelmine meetod, ainult peate kolm korda vajutama klahvi F4. Siis on ainult viide veerule absoluutne ja rida jääb fikseerituks.

4 meetod

Oletame, et meil on absoluutne viide lahtrile, kuid siin oli vaja muuta see suhteliseks. Selleks vajuta F4 klahvi nii palju kordi, et lingis ei oleks ühtegi $-märki. Siis muutub see suhteliseks ja valemi teisaldamisel või kopeerimisel muutub nii veeru aadress kui ka rea ​​aadress.

Lahtrite kinnitamine suure vahemiku jaoks

Näeme, et ülaltoodud meetodid ei valmista üldse raskusi. Kuid ülesanded on konkreetsed. Ja näiteks mida teha, kui meil on korraga mitukümmend valemit, mille lingid tuleb muuta absoluutseteks. 

Kahjuks standardsed Exceli meetodid seda eesmärki ei saavuta. Selleks peate kasutama spetsiaalset lisandmoodulit nimega VBA-Excel. See sisaldab palju lisafunktsioone, mis võimaldavad teil Exceliga tavalisi ülesandeid palju kiiremini täita.

See sisaldab enam kui sada kasutaja määratud funktsiooni ja 25 erinevat makrot ning seda uuendatakse regulaarselt. See võimaldab teil tööd parandada peaaegu iga aspektiga:

1. Rakud.
2. Makro
3. Erinevat tüüpi funktsioonid.
4. Lingid ja massiivid.

Eelkõige võimaldab see lisandmoodul parandada linke korraga paljudes valemites. Selleks peate tegema järgmised toimingud:

1. Valige vahemik.
2. Avage VBA-Exceli vahekaart, mis ilmub pärast installimist. 
3. Avage menüü "Funktsioonid", kus asub valik "Lukusta valemid".
   

   

4. Järgmisena ilmub dialoogiboks, milles peate määrama vajaliku parameetri. See lisand võimaldab kinnitada veeru ja veeru eraldi, koos ning eemaldada ka juba olemasoleva kinnituse koos paketiga. Pärast vajaliku parameetri valimist vastava raadionupu abil peate oma toimingud kinnitama, klõpsates "OK".

Näide

Toome näite, et see oleks selgem. Oletame, et meil on teave, mis kirjeldab kauba maksumust, selle kogukogust ja müügitulu. Ja meie ees seisab ülesanne panna tabel koguse ja maksumuse põhjal automaatselt kindlaks, kui palju raha meil õnnestus ilma kahjumit maha arvata.

Meie näites peate selleks sisestama valemi =B2*C2. See on üsna lihtne, nagu näete. Tema näite abil on väga lihtne kirjeldada, kuidas saate määrata lahtri või selle üksiku veeru või rea aadressi. 

Muidugi võite selles näites proovida valemit automaatse täitmise markeri abil alla lohistada, kuid sel juhul muudetakse lahtreid automaatselt. Niisiis, lahtris D3 on veel üks valem, kus numbrid asendatakse vastavalt 3-ga. Edasi, vastavalt skeemile - D4 - on valem kujul = B4 * C4, D5 - sarnaselt, kuid koos number 5 ja nii edasi.

Kui see on vajalik (enamikul juhtudel see selgub), siis pole probleeme. Kuid kui peate valemi ühes lahtris fikseerima, et see lohistamise ajal ei muutuks, on see mõnevõrra keerulisem. 

Oletame, et peame määrama dollari tulu. Paneme selle lahtrisse B7. Lähme veidi nostalgitsema ja näitame maksumust 35 rubla dollari kohta. Sellest lähtuvalt tuleb dollarites tulu määramiseks jagada rublades väljendatud summa dollari vahetuskursiga.

Nii näeb see meie näites välja.

Kui proovime sarnaselt eelmisele versioonile valemit ette kirjutada, siis kukume läbi. Samamoodi muutub valem sobivaks. Meie näites on see järgmine: =E3*B8. Siit näeme. et valemi esimene osa on muutunud E3-ks ja me seadsime endale selle ülesande, kuid valemi teist osa pole vaja B8-ks muuta. Seetõttu peame viite muutma absoluutseks. Seda saate teha ilma klahvi F4 vajutamata, lihtsalt pannes dollarimärgi.

Pärast seda, kui muutsime viite teisele lahtrile absoluutseks, sai see muutuste eest kaitstuks. Nüüd saate seda automaatse täitmise käepideme abil ohutult lohistada. Kõik fikseeritud andmed jäävad olenemata valemi asukohast samaks ja sidumata andmed muutuvad paindlikult. Kõigis lahtrites jagatakse sellel real kirjeldatud tulu rublades sama dollari vahetuskursiga.

Valem ise näeb välja selline:

=D2/$B$7

Tähelepanu! Oleme märkinud kaks dollarimärki. Sel moel näitame programmile, et nii veerg kui rida vajavad parandamist.

Lahtriviited makrodes

Makro on alamprogramm, mis võimaldab toiminguid automatiseerida. Erinevalt Exceli standardfunktsioonidest võimaldab makro mõne koodirea jooksul kohe määrata konkreetse lahtri ja teha teatud toiminguid. Kasulik teabe pakitöötlusel, näiteks kui pole võimalust lisandmooduleid installida (näiteks kasutatakse ettevõtte arvutit, mitte isiklikku).

Kõigepealt peate mõistma, et makro põhikontseptsioon on objektid, mis võivad sisaldada muid objekte. Objekt Töövihikud vastutab elektroonilise raamatu (st dokumendi) eest. See sisaldab objekti Sheets, mis on avatud dokumendi kõigi lehtede kogu. 

Vastavalt sellele on rakud rakkude objekt. See sisaldab kõiki konkreetse lehe lahtreid.

Iga objekt on kvalifitseeritud sulgudes olevate argumentidega. Lahtrite puhul viidatakse neile selles järjekorras. Esimesena on loetletud rea number, millele järgneb veeru number või täht (mõlemad vormingud on vastuvõetavad).

Näiteks koodirida, mis sisaldab viidet lahtrile C5, näeb välja selline:

Töövihikud (“Book2.xlsm”). Lehed (“Loend2”). Lahtrid (5, 3)

Töövihikud (“Book2.xlsm”). Lehed (“Loend2”). Lahtrid (5, “C”)

Lahtrile pääsete juurde ka objekti abil Korrastatud. Üldiselt on see mõeldud andma viidet vahemikule (mille elemendid, muide, võivad olla ka absoluutsed või suhtelised), kuid võite lihtsalt anda lahtri nime, samas vormingus nagu Exceli dokumendis.

Sel juhul näeb joon välja selline.

Töövihikud (“Book2.xlsm”). Lehed (“Loend2”). Vahemik (“C5”)

Võib tunduda, et see valik on mugavam, kuid kahe esimese valiku eeliseks on see, et saate kasutada muutujaid sulgudes ja anda lingi, mis pole enam absoluutne, vaid midagi suhtelist, mis sõltub lingi tulemustest. arvutused.

Seega saab makrosid programmides tõhusalt kasutada. Tegelikult on siin kõik viited lahtritele või vahemikele absoluutsed ja seetõttu saab neid ka nendega fikseerida. Tõsi, see pole nii mugav. Makrode kasutamine võib olla kasulik keerukate programmide kirjutamisel, mille algoritmis on palju samme. Üldiselt on absoluutsete või suhteliste viidete standardne kasutamine palju mugavam. 

Järeldused

Saime aru, mis on lahtriviide, kuidas see toimib, milleks see on. Saime aru absoluutsete ja suhteliste viidete erinevusest ning saime aru, mida tuleb teha, et muuta üks tüüp teiseks (lihtsate sõnadega, aadressi parandamine või selle eemaldamine). Mõtlesime välja, kuidas saate seda kohe teha suure hulga väärtustega. Nüüd on teil võimalus seda funktsiooni õigetes olukordades kasutada.