Hvernig á að frysta reit í Excel formúlu

Oft standa notendur frammi fyrir því að þurfa að festa hólf í formúlu. Til dæmis gerist það í aðstæðum þar sem þú vilt afrita formúlu, en þannig að hlekkurinn færist ekki upp og niður sama fjölda frumna og hann var afritaður frá upprunalegum stað.

Í þessu tilviki geturðu lagað frumutilvísunina í Excel. Og þetta er hægt að gera á nokkra vegu í einu. Við skulum skoða nánar hvernig á að ná þessu markmiði.

Hvað er Excel hlekkur

Blaðið er gert úr frumum. Hver þeirra inniheldur sérstakar upplýsingar. Aðrar frumur geta notað það í útreikningum. En hvernig skilja þeir hvaðan á að fá gögnin? Það hjálpar þeim að búa til tengla.

Hver hlekkur tilgreinir reit með einum bókstaf og einni tölu. Bókstafur táknar dálk og tala táknar línu. 

Það eru þrjár tegundir af hlekkjum: alger, afstæður og blandaður. Sá seinni er sjálfgefið stilltur. Alger tilvísun er sú sem hefur fast heimilisfang bæði dálks og dálks. Samkvæmt því er blandað sá þar sem annaðhvort sérstakur dálkur eða röð er fastur.

1 aðferðin

Fylgdu þessum skrefum til að vista bæði dálka- og línuföng:

1. Smelltu á reitinn sem inniheldur formúluna.
2. Smelltu á formúlustikuna fyrir reitinn sem við þurfum.
3. Ýttu á F4.

Þar af leiðandi mun frumutilvísunin breytast í alger. Það er hægt að þekkja það á einkennandi dollaramerkinu. Til dæmis, ef þú smellir á reit B2 og smellir síðan á F4, mun hlekkurinn líta svona út: $B$2.

Hvað þýðir dollaramerkið á undan hluta heimilisfangsins í hverri reit?

1. Ef það er sett fyrir framan bókstaf gefur það til kynna að dálkatilvísunin haldist óbreytt, sama hvert formúlan hefur verið færð.
2. Ef dollaramerkið er fyrir framan töluna gefur það til kynna að strengurinn sé festur. 

2 aðferðin

Þessi aðferð er nánast sú sama og sú fyrri, aðeins þú þarft að ýta tvisvar á F4. til dæmis, ef við hefðum klefi B2, þá verður það B$2 eftir það. Í einföldum orðum, þannig tókst okkur að laga línuna. Í þessu tilviki breytist bókstafurinn í dálknum.

Það er mjög þægilegt, til dæmis í töflum þar sem þú þarft að sýna innihald seinni reitsins frá toppi í neðsta reitnum. Í stað þess að gera svona formúlu oft er nóg að laga röðina og láta dálkinn breytast.

3 aðferðin

Þetta er alveg það sama og fyrri aðferðin, aðeins þú þarft að ýta þrisvar sinnum á F4 takkann. Þá verður aðeins tilvísun í dálkinn algjör og röðin verður áfram föst.

4 aðferðin

Segjum að við höfum algera tilvísun í frumu, en hér var nauðsynlegt að gera hana afstæða. Til að gera þetta, ýttu svo oft á F4 takkann að það eru engin $ merki í hlekknum. Þá verður það afstætt og þegar þú færir eða afritar formúluna breytist bæði heimilisfang dálksins og heimilisfang línunnar.

Festa frumur fyrir stórt svið

Við sjáum að ofangreindar aðferðir eru alls ekki í erfiðleikum með að framkvæma. En verkefnin eru ákveðin. Og, til dæmis, hvað á að gera ef við erum með nokkra tugi formúla í einu, hlekkina sem þarf að breyta í algjöra. 

Því miður munu staðlaðar Excel aðferðir ekki ná þessu markmiði. Til að gera þetta þarftu að nota sérstaka viðbót sem heitir VBA-Excel. Það inniheldur marga viðbótareiginleika sem gera þér kleift að framkvæma algeng verkefni með Excel miklu hraðar.

Það inniheldur meira en hundrað notendaskilgreindar aðgerðir og 25 mismunandi fjölvi, og það er einnig uppfært reglulega. Það gerir þér kleift að bæta vinnu með næstum hvaða þætti sem er:

1. Frumur.
2. Fjölvi
3. Aðgerðir af mismunandi gerðum.
4. Tenglar og fylki.

Sérstaklega gerir þessi viðbót þér kleift að laga tengla í miklum fjölda formúla í einu. Til að gera þetta verður þú að framkvæma eftirfarandi skref:

1. Veldu svið.
2. Opnaðu VBA-Excel flipann sem mun birtast eftir uppsetningu. 
3. Opnaðu valmyndina „Functions“, þar sem „Lock formúlur“ valmöguleikinn er staðsettur.
   

   

4. Næst birtist gluggi þar sem þú þarft að tilgreina nauðsynlega færibreytu. Þessi viðbót gerir þér kleift að festa dálk og dálk sérstaklega saman, og einnig fjarlægja þegar fyrirliggjandi festu með pakka. Eftir að nauðsynleg færibreyta hefur verið valin með samsvarandi útvarpshnappi þarftu að staðfesta aðgerðir þínar með því að smella á „Í lagi“.

Dæmi

Tökum dæmi til að gera það skýrara. Segjum að við höfum upplýsingar sem lýsa vörukostnaði, heildarmagni og sölutekjum. Og við stöndum frammi fyrir því verkefni að láta töfluna, byggt á magni og kostnaði, ákveða sjálfkrafa hversu mikið fé við náðum að afla án þess að draga frá tapi.

Í dæminu okkar, fyrir þetta þarftu að slá inn formúluna =B2*C2. Það er frekar einfalt eins og þú sérð. Það er mjög auðvelt að nota dæmi hennar til að lýsa því hvernig hægt er að laga heimilisfang reits eða einstaka dálk eða röð hennar. 

Auðvitað, í þessu dæmi, geturðu reynt að draga formúluna niður með því að nota sjálfvirka útfyllingarmerkið, en í þessu tilviki verður frumunum sjálfkrafa breytt. Svo, í reit D3 verður önnur formúla, þar sem tölunum verður skipt út fyrir 3, hver um sig. Ennfremur, samkvæmt kerfinu – D4 – mun formúlan hafa formið = B4 * C4, D5 – á svipaðan hátt, en með númer 5 og svo framvegis.

Ef það er nauðsynlegt (í flestum tilfellum kemur í ljós), þá eru engin vandamál. En ef þú þarft að laga formúluna í einum reit þannig að hún breytist ekki við að draga, þá verður þetta eitthvað erfiðara. 

Segjum að við þurfum að ákvarða dollaratekjurnar. Við skulum setja það í reit B7. Við skulum fá smá nostalgíu og gefa til kynna kostnaðinn við 35 rúblur á dollara. Í samræmi við það, til að ákvarða tekjur í dollurum, er nauðsynlegt að skipta upphæðinni í rúblur með gengi dollara.

Svona lítur það út í dæminu okkar.

Ef við, svipað og fyrri útgáfan, reynum að ávísa formúlu, þá mistakast við. Á sama hátt mun formúlan breytast í viðeigandi. Í dæminu okkar mun það vera svona: =E3*B8. Héðan getum við séð. að fyrri hluti formúlunnar hefur breyst í E3, og við settum okkur þetta verkefni, en við þurfum ekki að breyta seinni hluta formúlunnar í B8. Þess vegna þurfum við að breyta tilvísuninni í algera. Þú getur gert þetta án þess að ýta á F4 takkann, bara með því að setja dollaramerki.

Eftir að við breyttum tilvísuninni í seinni reitinn í algjöra, varð hún varin fyrir breytingum. Nú geturðu örugglega dregið það með því að nota sjálfvirka útfyllingarhandfangið. Öll föst gögn verða þau sömu, óháð staðsetningu formúlunnar, og óbundin gögn munu breytast á sveigjanlegan hátt. Í öllum hólfum verður tekjunum í rúblum sem lýst er í þessari línu deilt með sama dollargengi.

Formúlan sjálf mun líta svona út:

=D2/$B$7

Attention! Við höfum bent á tvö dollaramerki. Þannig sýnum við forritinu að það þarf að laga bæði dálkinn og línuna.

Frumuvísanir í fjölvi

Fjölvi er undirrútína sem gerir þér kleift að gera aðgerðir sjálfvirkar. Ólíkt hefðbundinni virkni Excel, gerir fjölvi þér kleift að stilla tiltekið reit strax og framkvæma ákveðnar aðgerðir í örfáum línum af kóða. Gagnlegt fyrir hópvinnslu upplýsinga, til dæmis ef engin leið er til að setja upp viðbætur (til dæmis er fyrirtækistölva notuð, ekki persónuleg).

Fyrst þarftu að skilja að lykilhugtakið í fjölvi er hlutir sem geta innihaldið aðra hluti. Workbooks hluturinn ber ábyrgð á rafbókinni (þ.e. skjalinu). Það inniheldur Sheets hlutinn, sem er safn allra blaða í opnu skjali. 

Samkvæmt því eru frumur frumuhlutur. Það inniheldur allar frumur á tilteknu blaði.

Hver hlutur er hæfur með rökum í sviga. Þegar um frumur er að ræða er vísað til þeirra í þessari röð. Línunúmerið er skráð fyrst og síðan dálknúmerið eða bókstafurinn (bæði sniðin eru ásættanleg).

Til dæmis myndi kóðalína sem inniheldur tilvísun í reit C5 líta svona út:

Vinnubækur(“Book2.xlsm”).Sheets(“List2“). Cells(5, 3)

Vinnubækur(„Book2.xlsm“).Sheets(“List2“). Cells(5, „C“)

Þú getur líka fengið aðgang að hólf með því að nota hlut Snyrtilegur. Almennt er ætlað að gefa tilvísun í svið (þar sem þættir geta líka verið algildir eða afstæðir), en þú getur einfaldlega gefið frumheiti, á sama sniði og í Excel skjali.

Í þessu tilviki mun línan líta svona út.

Vinnubækur(„Book2.xlsm“).Sheets(“List2“).Range(“C5“)

Það kann að virðast sem þessi valmöguleiki sé þægilegri, en kosturinn við fyrstu tvo valmöguleikana er að þú getur notað breytur innan sviga og gefið upp hlekk sem er ekki lengur algildur, heldur eitthvað eins og afstætt, sem fer eftir niðurstöðum útreikningunum.

Þannig er hægt að nota fjölvi á áhrifaríkan hátt í forritum. Reyndar verða allar tilvísanir í frumur eða svið hér algjörar og því einnig hægt að laga þær með þeim. Að vísu er það ekki svo þægilegt. Notkun fjölva getur verið gagnleg þegar skrifuð eru flókin forrit með miklum fjölda þrepa í reikniritinu. Almennt séð er staðlað leið til að nota alger eða afstæð tilvísanir miklu þægilegri. 

Ályktanir

Við komumst að því hvað frumutilvísun er, hvernig hún virkar, til hvers hún er. Við skildum muninn á algerum og afstæðum tilvísunum og komumst að því hvað þarf að gera til að breyta einni tegund í aðra (í einföldum orðum, laga heimilisfangið eða losa það). Við komumst að því hvernig þú getur gert það strax með miklum fjölda gilda. Nú hefur þú sveigjanleika til að nota þennan eiginleika við réttar aðstæður.