Hoe om 'n sel in 'n Excel-formule te vries

Dikwels word gebruikers gekonfronteer met die behoefte om 'n sel in 'n formule vas te pen. Dit kom byvoorbeeld voor in situasies waar jy 'n formule wil kopieer, maar sodat die skakel nie dieselfde aantal selle op en af ​​beweeg as wat dit vanaf sy oorspronklike ligging gekopieer is nie.

In hierdie geval kan jy die selverwysing in Excel regmaak. En dit kan op verskeie maniere gelyktydig gedoen word. Kom ons kyk van naderby na hoe om hierdie doelwit te bereik.

Wat is 'n Excel-skakel

Die vel bestaan ​​uit selle. Elkeen van hulle bevat spesifieke inligting. Ander selle kan dit in berekeninge gebruik. Maar hoe verstaan ​​hulle waar om die data vandaan te kry? Dit help hulle om skakels te maak.

Elke skakel dui 'n sel aan met een letter en een nommer. 'n Letter verteenwoordig 'n kolom en 'n nommer verteenwoordig 'n ry. 

Daar is drie tipes skakels: absoluut, relatief en gemeng. Die tweede een is by verstek gestel. 'n Absolute verwysing is een wat 'n vaste adres van beide 'n kolom en 'n kolom het. Gevolglik is gemeng die een waar óf 'n aparte kolom óf 'n ry vasgestel is.

Die 1 metode

Volg hierdie stappe om beide kolom- en ryadresse te stoor:

1. Klik op die sel wat die formule bevat.
2. Klik op die formulebalk vir die sel wat ons benodig.
3. Druk F4.

As gevolg hiervan sal die selverwysing na absoluut verander. Dit kan herken word aan die kenmerkende dollarteken. Byvoorbeeld, as jy op sel B2 klik en dan op F4 klik, sal die skakel soos volg lyk: $B$2.

Wat beteken die dollarteken voor die deel van die adres per sel?

1. As dit voor 'n letter geplaas word, dui dit aan dat die kolomverwysing dieselfde bly, maak nie saak waarheen die formule geskuif is nie.
2. As die dollarteken voor die nommer is, dui dit aan dat die tou vasgespeld is. 

Die 2 metode

Hierdie metode is amper dieselfde as die vorige, net jy hoef F4 twee keer te druk. byvoorbeeld, as ons sel B2 gehad het, dan sal dit daarna B$2 word. In eenvoudige woorde, op hierdie manier het ons daarin geslaag om die lyn reg te maak. In hierdie geval sal die letter van die kolom verander.

Dit is baie gerieflik, byvoorbeeld in tabelle waar jy die inhoud van die tweede sel van bo in die onderste sel moet vertoon. In plaas daarvan om so 'n formule baie keer te doen, is dit genoeg om die ry reg te maak en die kolom te laat verander.

Die 3 metode

Dit is heeltemal dieselfde as die vorige metode, net jy hoef die F4-sleutel drie keer te druk. Dan sal slegs die verwysing na die kolom absoluut wees, en die ry bly vas.

Die 4 metode

Gestel ons het 'n absolute verwysing na 'n sel, maar hier was dit nodig om dit relatief te maak. Om dit te doen, druk die F4-sleutel soveel keer dat daar geen $-tekens in die skakel is nie. Dan sal dit relatief word, en wanneer jy die formule skuif of kopieer, sal beide die kolomadres en die ryadres verander.

Speld selle vas vir 'n groot reeks

Ons sien dat die bogenoemde metodes glad nie moeilik is om uit te voer nie. Maar die take is spesifiek. En, byvoorbeeld, wat om te doen as ons 'n paar dosyn formules gelyktydig het, waarvan die skakels in absolute verander moet word. 

Ongelukkig sal standaard Excel-metodes nie hierdie doelwit bereik nie. Om dit te doen, moet jy 'n spesiale addon genaamd VBA-Excel gebruik. Dit bevat baie bykomende kenmerke wat jou in staat stel om algemene take met Excel baie vinniger uit te voer.

Dit bevat meer as honderd gebruikergedefinieerde funksies en 25 verskillende makro's, en dit word ook gereeld bygewerk. Dit laat jou toe om werk met byna enige aspek te verbeter:

1. Selle.
2. Makro.
3. Funksies van verskillende tipes.
4. Skakels en skikkings.

Met hierdie byvoeging kan u veral skakels in 'n groot aantal formules gelyktydig regmaak. Om dit te doen, moet jy die volgende stappe uitvoer:

1. Kies 'n reeks.
2. Maak die VBA-Excel-oortjie oop wat na installasie sal verskyn. 
3. Maak die "Funksies"-kieslys oop, waar die opsie "Sluitformules" geleë is.
   

   

4. Vervolgens sal 'n dialoogkassie verskyn waarin u die vereiste parameter moet spesifiseer. Met hierdie byvoeging kan u 'n kolom en 'n kolom afsonderlik saam vaspen, en ook 'n reeds bestaande vasspeld met 'n pakket verwyder. Nadat die vereiste parameter gekies is met die ooreenstemmende radioknoppie, moet u u aksies bevestig deur op "OK" te klik.

voorbeeld

Kom ons neem 'n voorbeeld om dit duideliker te maak. Kom ons sê ons het inligting wat die koste van goedere, die totale hoeveelheid en verkoopsinkomste beskryf. En ons staan ​​voor die taak om die tabel, gebaseer op die hoeveelheid en koste, outomaties te laat bepaal hoeveel geld ons kon verdien sonder om verliese af te trek.

In ons voorbeeld, hiervoor moet jy die formule = invoerB2*C2. Dit is redelik eenvoudig, soos jy kan sien. Dit is baie maklik om haar voorbeeld te gebruik om te beskryf hoe jy die adres van 'n sel of sy individuele kolom of ry kan regmaak. 

Natuurlik, in hierdie voorbeeld kan jy probeer om die formule af te sleep met die outovulmerker, maar in hierdie geval sal die selle outomaties verander word. Dus, in sel D3 sal daar 'n ander formule wees, waar die getalle onderskeidelik deur 3 vervang sal word. Verder, volgens die skema – D4 – sal die formule die vorm aanneem = B4 * C4, D5 – soortgelyk, maar met die nommer 5 en so aan.

As dit nodig is (in die meeste gevalle blyk dit), dan is daar geen probleme nie. Maar as jy die formule in een sel moet regmaak sodat dit nie verander wanneer jy sleep nie, sal dit ietwat moeiliker wees. 

Gestel ons moet die dollar-inkomste bepaal. Kom ons plaas dit in sel B7. Kom ons raak 'n bietjie nostalgies en dui die koste van 35 roebels per dollar aan. Gevolglik, om die inkomste in dollars te bepaal, is dit nodig om die bedrag in roebels te verdeel deur die dollar-wisselkoers.

Hier is hoe dit in ons voorbeeld lyk.

As ons, soortgelyk aan die vorige weergawe, probeer om 'n formule voor te skryf, sal ons misluk. Net so sal die formule verander na die toepaslike een. In ons voorbeeld sal dit soos volg wees: =E3*B8. Van hier af kan ons sien. dat die eerste deel van die formule in E3 verander het, en ons het hierdie taak vir onsself opgestel, maar ons hoef nie die tweede deel van die formule na B8 te verander nie. Daarom moet ons die verwysing in 'n absolute een verander. Jy kan dit doen sonder om die F4-sleutel te druk, net deur 'n dollarteken te plaas.

Nadat ons die verwysing na die tweede sel in 'n absolute een verander het, het dit beskerm teen veranderinge. Nou kan jy dit veilig sleep met die outovul-handvatsel. Alle vaste data sal dieselfde bly, ongeag die posisie van die formule, en onverbonde data sal buigsaam verander. In alle selle sal die inkomste in roebels wat in hierdie lyn beskryf word, deur dieselfde dollarwisselkoers gedeel word.

Die formule self sal soos volg lyk:

=D2/$B$7

Aandag! Ons het twee dollartekens aangedui. Op hierdie manier wys ons vir die program dat beide die kolom en die ry reggemaak moet word.

Selverwysings in makro's

'n Makro is 'n subroetine wat jou toelaat om aksies te outomatiseer. Anders as die standaardfunksionaliteit van Excel, laat 'n makro jou toe om onmiddellik 'n spesifieke sel te stel en sekere aksies in net 'n paar reëls kode uit te voer. Nuttig vir bondelverwerking van inligting, byvoorbeeld as daar geen manier is om byvoegings te installeer nie (byvoorbeeld 'n maatskappyrekenaar word gebruik, nie 'n persoonlike een nie).

Eerstens moet jy verstaan ​​dat die sleutelkonsep van 'n makro voorwerpe is wat ander voorwerpe kan bevat. Die Werkboeke-objek is verantwoordelik vir die elektroniese boek (dit is die dokument). Dit sluit die Sheets-objek in, wat 'n versameling van alle velle van 'n oop dokument is. 

Gevolglik is selle 'n selle-voorwerp. Dit bevat al die selle van 'n spesifieke vel.

Elke voorwerp is gekwalifiseer met argumente tussen hakies. In die geval van selle word daar in hierdie volgorde na hulle verwys. Die rynommer word eerste gelys, gevolg deur die kolomnommer of letter (albei formate is aanvaarbaar).

Byvoorbeeld, 'n reël kode wat 'n verwysing na sel C5 bevat, sal soos volg lyk:

Werkboeke(“Boek2.xlsm”).Sheets(“Lys2”).Selle(5, 3)

Werkboeke(“Boek2.xlsm”).Sheets(“Lys2”).Selle(5, “C”)

Jy kan ook toegang tot 'n sel gebruik deur 'n voorwerp te gebruik Netjies. Oor die algemeen is dit bedoel om 'n verwysing na 'n reeks te gee (waarvan die elemente, terloops, ook absoluut of relatief kan wees), maar jy kan eenvoudig 'n selnaam gee, in dieselfde formaat as in 'n Excel-dokument.

In hierdie geval sal die lyn so lyk.

Werkboeke(“Boek2.xlsm”). Blaaie(“Lys2”). Reeks(“C5”)

Dit mag lyk asof hierdie opsie geriefliker is, maar die voordeel van die eerste twee opsies is dat jy veranderlikes tussen hakies kan gebruik en 'n skakel kan gee wat nie meer absoluut is nie, maar iets soos 'n relatiewe een, wat sal afhang van die resultate van die berekeninge.

Makro's kan dus effektief in programme gebruik word. Trouens, alle verwysings na selle of reekse hier sal absoluut wees, en kan dus ook daarmee reggemaak word. True, dit is nie so gerieflik nie. Die gebruik van makro's kan nuttig wees wanneer komplekse programme met 'n groot aantal stappe in die algoritme geskryf word. Oor die algemeen is die standaard manier om absolute of relatiewe verwysings te gebruik baie geriefliker. 

Gevolgtrekkings

Ons het uitgepluis wat 'n selverwysing is, hoe dit werk, waarvoor dit is. Ons het die verskil tussen absolute en relatiewe verwysings verstaan ​​en uitgepluis wat gedoen moet word om een ​​tipe in 'n ander te verander (in eenvoudige woorde, maak die adres reg of ontspeld dit). Ons het uitgepluis hoe jy dit dadelik kan doen met 'n groot aantal waardes. Nou het jy die buigsaamheid om hierdie kenmerk in die regte situasies te gebruik.