Saturs
Bieži vien lietotāji saskaras ar nepieciešamību piespraust šūnu formulā. Piemēram, tas notiek situācijās, kad vēlaties kopēt formulu, bet tā, lai saite nepārvietotos uz augšu un uz leju par tādu pašu šūnu skaitu, cik tā tika kopēta no sākotnējās atrašanās vietas.
Šādā gadījumā varat labot šūnas atsauci programmā Excel. Un to var izdarīt vairākos veidos vienlaikus. Apskatīsim tuvāk, kā sasniegt šo mērķi.
Kas ir Excel saite
Lapa sastāv no šūnām. Katrs no tiem satur īpašu informāciju. Citas šūnas to var izmantot aprēķinos. Bet kā viņi saprot, no kurienes iegūt datus? Tas palīdz viņiem izveidot saites.
Katra saite apzīmē šūnu ar vienu burtu un vienu skaitli. Burts apzīmē kolonnu un cipars apzīmē rindu.
Ir trīs veidu saites: absolūtā, relatīvā un jauktā. Otrais ir iestatīts pēc noklusējuma. Absolūtā atsauce ir tāda, kurai ir fiksēta gan kolonnas, gan kolonnas adrese. Attiecīgi jaukts ir tas, kurā ir fiksēta atsevišķa kolonna vai rinda.
1 metode
Lai saglabātu gan kolonnu, gan rindu adreses, veiciet šīs darbības:
- Noklikšķiniet uz šūnas, kurā ir formula.
- Noklikšķiniet uz nepieciešamās šūnas formulas joslas.
- Nospiediet F4.
Tā rezultātā šūnas atsauce tiks mainīta uz absolūtu. To var atpazīt pēc raksturīgās dolāra zīmes. Piemēram, noklikšķinot uz šūnas B2 un pēc tam uz F4, saite izskatīsies šādi: $B$2.
Ko nozīmē dolāra zīme pirms adreses daļas šūnā?
- Ja tas ir novietots burta priekšā, tas norāda, ka kolonnas atsauce paliek nemainīga neatkarīgi no tā, kur formula ir pārvietota.
- Ja cipara priekšā atrodas dolāra zīme, tas norāda, ka virkne ir piesprausta.
2 metode
Šī metode ir gandrīz tāda pati kā iepriekšējā, tikai divas reizes jānospiež F4. piemēram, ja mums būtu šūna B2, tad pēc tam tā kļūs par B$2. Vienkāršiem vārdiem sakot, šādā veidā mums izdevās salabot līniju. Šajā gadījumā kolonnas burts mainīsies.
Tas ir ļoti ērti, piemēram, tabulās, kur apakšējā šūnā ir jāparāda otrās šūnas saturs no augšas. Tā vietā, lai vairākas reizes izpildītu šādu formulu, pietiek ar to, lai labotu rindu un ļautu kolonnai mainīties.
3 metode
Tas ir pilnīgi tāds pats kā iepriekšējā metode, tikai jums trīs reizes jānospiež taustiņš F4. Tad tikai atsauce uz kolonnu būs absolūta, un rinda paliks fiksēta.
4 metode
Pieņemsim, ka mums ir absolūta atsauce uz šūnu, bet šeit tas bija jāpadara relatīvs. Lai to izdarītu, nospiediet taustiņu F4 tik reižu, lai saitē nebūtu $ zīmes. Tad tas kļūs relatīvs, un, pārvietojot vai kopējot formulu, mainīsies gan kolonnas adrese, gan rindas adrese.
Šūnu piespraušana lielam diapazonam
Mēs redzam, ka iepriekšminēto metožu izpilde nerada nekādas grūtības. Bet uzdevumi ir konkrēti. Un, piemēram, ko darīt, ja mums vienlaikus ir vairāki desmiti formulu, kuru saites ir jāpārvērš par absolūtām.
Diemžēl standarta Excel metodes šo mērķi nesasniegs. Lai to izdarītu, jums ir jāizmanto īpašs papildinājums ar nosaukumu VBA-Excel. Tajā ir iekļautas daudzas papildu funkcijas, kas ļauj daudz ātrāk veikt parastos uzdevumus programmā Excel.
Tas ietver vairāk nekā simts lietotāja definētu funkciju un 25 dažādus makro, kā arī tiek regulāri atjaunināts. Tas ļauj uzlabot darbu gandrīz jebkurā aspektā:
- Šūnas.
- Makro
- Dažādu veidu funkcijas.
- Saites un masīvi.
Jo īpaši šī pievienojumprogramma ļauj vienlaikus labot saites daudzās formulās. Lai to izdarītu, jums jāveic šādas darbības:
- Izvēlieties diapazonu.
- Atveriet VBA-Excel cilni, kas parādīsies pēc instalēšanas.
- Atveriet izvēlni “Funkcijas”, kur atrodas opcija “Bloķēt formulas”.
6 - Pēc tam parādīsies dialoglodziņš, kurā jānorāda nepieciešamais parametrs. Šis papildinājums ļauj piespraust kolonnu un kolonnu atsevišķi, kopā, kā arī noņemt jau esošu piespraušanu ar pakotni. Kad nepieciešamais parametrs ir atlasīts, izmantojot atbilstošo radio pogu, jums jāapstiprina savas darbības, noklikšķinot uz “OK”.
Piemērs
Ņemsim piemēru, lai tas būtu skaidrāks. Pieņemsim, ka mums ir informācija, kas raksturo preču pašizmaksu, to kopējo daudzumu un pārdošanas ieņēmumus. Un mēs saskaramies ar uzdevumu padarīt tabulu, pamatojoties uz daudzumu un izmaksām, automātiski noteikt, cik daudz naudas mums izdevās nopelnīt, neatskaitot zaudējumus.
Mūsu piemērā šim nolūkam ir jāievada formula =B2*C2. Tas ir pavisam vienkārši, kā redzat. Ir ļoti viegli izmantot viņas piemēru, lai aprakstītu, kā varat labot šūnas vai tās atsevišķās kolonnas vai rindas adresi.
Protams, šajā piemērā varat mēģināt vilkt formulu uz leju, izmantojot automātiskās aizpildes marķieri, taču šajā gadījumā šūnas tiks automātiski mainītas. Tātad šūnā D3 būs vēl viena formula, kur skaitļi tiks aizstāti attiecīgi ar 3. Tālāk pēc shēmas – D4 – formula būs formā = B4 * C4, D5 – līdzīgi, bet ar numurs 5 un tā tālāk.
Ja vajag (vairumā gadījumu izrādās), tad problēmu nav. Bet, ja formula ir jālabo vienā šūnā, lai tā nemainītos, velkot, tad tas būs nedaudz grūtāk.
Pieņemsim, ka mums ir jānosaka dolāra ieņēmumi. Ieliksim to šūnā B7. Nedaudz nostalģiski iesim un norādīsim izmaksas 35 rubļi par dolāru. Attiecīgi, lai noteiktu ieņēmumus dolāros, summa rubļos ir jāsadala ar dolāra kursu.
Lūk, kā tas izskatās mūsu piemērā.
Ja mēs, līdzīgi kā iepriekšējā versijā, mēģināsim izrakstīt formulu, tad mums neizdosies. Tāpat formula mainīsies uz atbilstošo. Mūsu piemērā tas būs šādi: =E3*B8. No šejienes mēs varam redzēt. ka formulas pirmā daļa ir pārvērtusies par E3, un mēs sev uzstādām šo uzdevumu, bet mums nav jāmaina formulas otrā daļa uz B8. Tāpēc mums ir jāpārvērš atsauce par absolūtu. To var izdarīt, nenospiežot taustiņu F4, vienkārši ievietojot dolāra zīmi.
Pēc tam, kad atsauci uz otro šūnu pārvērtām par absolūtu, tā kļuva aizsargāta no izmaiņām. Tagad varat to droši vilkt, izmantojot automātiskās aizpildīšanas rokturi. Visi fiksētie dati paliks nemainīgi neatkarīgi no formulas pozīcijas, un nesaistītie dati mainīsies elastīgi. Visās šūnās šajā rindā aprakstītie ieņēmumi rubļos tiks dalīti ar to pašu dolāra kursu.
Pati formula izskatīsies šādi:
=D2/$B$7
Uzmanību! Mēs esam norādījuši divas dolāra zīmes. Tādā veidā mēs parādām programmai, ka gan kolonna, gan rinda ir jālabo.
Šūnu atsauces makro
Makro ir apakšprogramma, kas ļauj automatizēt darbības. Atšķirībā no Excel standarta funkcionalitātes, makro ļauj nekavējoties iestatīt konkrētu šūnu un veikt noteiktas darbības tikai dažās koda rindās. Noder informācijas pakešu apstrādei, piemēram, ja nav iespējas instalēt papildinājumus (piemēram, tiek izmantots uzņēmuma dators, nevis personīgais).
Vispirms jums ir jāsaprot, ka makro galvenais jēdziens ir objekti, kas var saturēt citus objektus. Objekts Darba burtnīcas ir atbildīgs par elektronisko grāmatu (tas ir, dokumentu). Tas ietver objektu Sheets, kas ir visu atvērtā dokumenta lapu kolekcija.
Attiecīgi šūnas ir šūnu objekts. Tajā ir visas noteiktas lapas šūnas.
Katrs objekts ir kvalificēts ar argumentiem iekavās. Šūnu gadījumā uz tām ir atsauces šādā secībā. Vispirms tiek norādīts rindas numurs, kam seko kolonnas numurs vai burts (pieņemami ir abi formāti).
Piemēram, koda rinda, kurā ir atsauce uz šūnu C5, izskatītos šādi:
Darbgrāmatas (“Grāmata2.xlsm”). Lapas (“Saraksts2”). Šūnas (5, 3)
Darbgrāmatas (“Grāmata2.xlsm”). Lapas (“Saraksts2”). Šūnas (5, “C”)
Varat arī piekļūt šūnai, izmantojot objektu Kārtīgi. Kopumā tas ir paredzēts, lai sniegtu atsauci uz diapazonu (kura elementi, starp citu, var būt arī absolūti vai relatīvi), bet jūs varat vienkārši dot šūnas nosaukumu tādā pašā formātā kā Excel dokumentā.
Šajā gadījumā līnija izskatīsies šādi.
Darbgrāmatas (“Grāmata2.xlsm”). Lapas (“Saraksts2”). Diapazons (“C5”)
Var šķist, ka šī opcija ir ērtāka, taču pirmo divu iespēju priekšrocība ir tāda, ka varat izmantot mainīgos lielumus iekavās un dot saiti, kas vairs nav absolūta, bet kaut kas līdzīgs relatīvai, kas būs atkarīga no rezultātiem aprēķinus.
Tādējādi makro var efektīvi izmantot programmās. Faktiski visas atsauces uz šūnām vai diapazoniem šeit būs absolūtas, un tāpēc tās var arī salabot ar tām. Tiesa, tas nav tik ērti. Makro izmantošana var būt noderīga, rakstot sarežģītas programmas ar lielu algoritma darbību skaitu. Kopumā standarta veids, kā izmantot absolūtās vai relatīvās atsauces, ir daudz ērtāks.
secinājumi
Mēs noskaidrojām, kas ir šūnas atsauce, kā tā darbojas, kam tā paredzēta. Mēs sapratām atšķirību starp absolūtajām un relatīvajām atsaucēm un noskaidrojām, kas jādara, lai vienu veidu pārvērstu par citu (vienkāršiem vārdiem sakot, labojiet adresi vai atspraudiet to). Mēs izdomājām, kā to var izdarīt uzreiz ar lielu vērtību skaitu. Tagad jums ir iespēja izmantot šo funkciju vajadzīgajās situācijās.