Inhoud
Vaak worden gebruikers geconfronteerd met de noodzaak om een cel in een formule vast te zetten. Het komt bijvoorbeeld voor in situaties waarin u een formule wilt kopiëren, maar zodat de koppeling niet hetzelfde aantal cellen op en neer beweegt als vanaf de oorspronkelijke locatie.
In dit geval kunt u de celverwijzing in Excel corrigeren. En dat kan op meerdere manieren tegelijk. Laten we eens nader bekijken hoe we dit doel kunnen bereiken.
Wat is een Excel-link?
Het blad is opgebouwd uit cellen. Elk van hen bevat specifieke informatie. Andere cellen kunnen het in berekeningen gebruiken. Maar hoe weten ze waar ze de gegevens vandaan moeten halen? Het helpt hen om koppelingen te maken.
Elke link duidt een cel aan met één letter en één cijfer. Een letter staat voor een kolom en een cijfer voor een rij.
Er zijn drie soorten links: absoluut, relatief en gemengd. De tweede is standaard ingesteld. Een absolute referentie is er een die een vast adres heeft van zowel een kolom als een kolom. Dienovereenkomstig is gemengd degene waar ofwel een afzonderlijke kolom of een rij is vastgelegd.
De 1 methode
Volg deze stappen om zowel kolom- als rijadressen op te slaan:
- Klik op de cel met de formule.
- Klik op de formulebalk voor de cel die we nodig hebben.
- Druk op F4.
Als gevolg hiervan verandert de celverwijzing in absoluut. Het is te herkennen aan het karakteristieke dollarteken. Als u bijvoorbeeld op cel B2 klikt en vervolgens op F4, ziet de link er als volgt uit: $B$2.
Wat betekent het dollarteken voor het gedeelte van het adres per cel?
- Als het voor een letter wordt geplaatst, geeft dit aan dat de kolomverwijzing hetzelfde blijft, ongeacht waar de formule is verplaatst.
- Als het dollarteken voor het nummer staat, geeft dit aan dat de tekenreeks is vastgezet.
De 2 methode
Deze methode is bijna hetzelfde als de vorige, alleen moet je twee keer op F4 drukken. als we bijvoorbeeld cel B2 hadden, wordt het daarna B$2. In eenvoudige woorden, op deze manier zijn we erin geslaagd om de lijn te repareren. In dit geval verandert de letter van de kolom.
Het is bijvoorbeeld erg handig in tabellen waar u de inhoud van de tweede cel van boven in de onderste cel moet weergeven. In plaats van zo'n formule vele malen uit te voeren, is het voldoende om de rij te repareren en de kolom te laten veranderen.
De 3 methode
Dit is volledig hetzelfde als de vorige methode, alleen moet je drie keer op de F4-toets drukken. Dan is alleen de verwijzing naar de kolom absoluut en blijft de rij vast.
De 4 methode
Stel dat we een absolute verwijzing naar een cel hebben, maar hier was het nodig om het relatief te maken. Om dit te doen, drukt u zo vaak op de F4-toets dat er geen $-tekens in de link staan. Dan wordt het relatief en wanneer u de formule verplaatst of kopieert, veranderen zowel het kolomadres als het rijadres.
Cellen vastzetten voor een groot bereik
We zien dat de bovenstaande methoden geen enkel probleem opleveren om uit te voeren. Maar de taken zijn specifiek. En wat te doen als we bijvoorbeeld enkele tientallen formules tegelijk hebben, waarvan de links in absolute moeten worden omgezet.
Helaas zullen standaard Excel-methoden dit doel niet bereiken. Om dit te doen, moet u een speciale add-on gebruiken genaamd VBA-Excel. Het bevat veel extra functies waarmee u veelvoorkomende taken met Excel veel sneller kunt uitvoeren.
Het bevat meer dan honderd door de gebruiker gedefinieerde functies en 25 verschillende macro's, en het wordt ook regelmatig bijgewerkt. Hiermee kunt u het werk met bijna elk aspect verbeteren:
- Cellen.
- Macro.
- Functies van verschillende typen.
- Links en arrays.
Met deze invoegtoepassing kunt u met name koppelingen in een groot aantal formules tegelijk herstellen. Om dit te doen, moet u de volgende stappen uitvoeren:
- Selecteer een bereik.
- Open het VBA-Excel-tabblad dat na installatie verschijnt.
- Open het menu "Functies", waar de optie "Formules vergrendelen" zich bevindt.
6 - Vervolgens verschijnt een dialoogvenster waarin u de vereiste parameter moet opgeven. Met deze add-on kunt u een kolom en een kolom afzonderlijk aan elkaar vastpinnen en ook een reeds bestaande vastzetten met een pakket verwijderen. Nadat de vereiste parameter is geselecteerd met behulp van het bijbehorende keuzerondje, moet u uw acties bevestigen door op "OK" te klikken.
Voorbeeld
Laten we een voorbeeld nemen om het duidelijker te maken. Laten we zeggen dat we informatie hebben die de kosten van goederen, de totale hoeveelheid en de verkoopopbrengst beschrijft. En we staan voor de taak om de tafel, op basis van de hoeveelheid en de kosten, automatisch te laten bepalen hoeveel geld we hebben verdiend zonder verliezen af te trekken.
In ons voorbeeld moet u hiervoor de formule = . invoerenB2*C2. Het is vrij eenvoudig, zoals je kunt zien. Het is heel gemakkelijk om haar voorbeeld te gebruiken om te beschrijven hoe u het adres van een cel of de afzonderlijke kolom of rij kunt corrigeren.
In dit voorbeeld kunt u natuurlijk proberen de formule naar beneden te slepen met de markering voor automatisch aanvullen, maar in dit geval worden de cellen automatisch gewijzigd. Dus in cel D3 zal er een andere formule zijn, waarbij de getallen respectievelijk worden vervangen door 3. Verder, volgens het schema - D4 - zal de formule de vorm aannemen = B4 * C4, D5 - op dezelfde manier, maar met de nummer 5 enzovoort.
Als het nodig is (in de meeste gevallen blijkt het), dan zijn er geen problemen. Maar als u de formule in één cel moet corrigeren zodat deze niet verandert tijdens het slepen, dan zal dit iets moeilijker zijn.
Stel dat we de dollarinkomsten moeten bepalen. Laten we het in cel B7 plaatsen. Laten we een beetje nostalgisch worden en de kosten van 35 roebel per dollar aangeven. Dienovereenkomstig, om de inkomsten in dollars te bepalen, is het noodzakelijk om het bedrag in roebels te delen door de dollarkoers.
Zo ziet het eruit in ons voorbeeld.
Als we, net als bij de vorige versie, proberen een formule voor te schrijven, dan zullen we falen. Evenzo zal de formule veranderen in de juiste. In ons voorbeeld zal het als volgt zijn: =E3*B8. Vanaf hier kunnen we zien. dat het eerste deel van de formule is veranderd in E3, en we stellen onszelf deze taak, maar we hoeven het tweede deel van de formule niet te veranderen in B8. Daarom moeten we van de verwijzing een absolute maken. U kunt dit doen zonder op de F4-toets te drukken, gewoon door een dollarteken te plaatsen.
Nadat we de verwijzing naar de tweede cel in een absolute hadden veranderd, werd deze beschermd tegen wijzigingen. Nu kunt u het veilig slepen met behulp van de handgreep voor automatisch aanvullen. Alle vaste gegevens blijven hetzelfde, ongeacht de positie van de formule, en niet-vastgelegde gegevens veranderen flexibel. In alle cellen worden de inkomsten in roebels die in deze regel worden beschreven, gedeeld door dezelfde dollarkoers.
De formule zelf ziet er als volgt uit:
=D2/$B$7
Opgelet! We hebben twee dollartekens aangegeven. Op deze manier laten we het programma zien dat zowel de kolom als de rij gerepareerd moeten worden.
Celverwijzingen in macro's
Een macro is een subroutine waarmee u acties kunt automatiseren. In tegenstelling tot de standaardfunctionaliteit van Excel, kunt u met een macro onmiddellijk een specifieke cel instellen en bepaalde acties uitvoeren in slechts een paar regels code. Handig voor batchverwerking van informatie, bijvoorbeeld als er geen manier is om add-ons te installeren (er wordt bijvoorbeeld een bedrijfscomputer gebruikt, geen persoonlijke).
Eerst moet u begrijpen dat het belangrijkste concept van een macro objecten zijn die andere objecten kunnen bevatten. Het object Werkboeken is verantwoordelijk voor het elektronische boek (dat wil zeggen, het document). Het bevat het object Spreadsheets, dat een verzameling is van alle bladen van een geopend document.
Dienovereenkomstig zijn cellen een Cells-object. Het bevat alle cellen van een bepaald blad.
Elk object wordt gekwalificeerd met argumenten tussen haakjes. In het geval van cellen wordt er in deze volgorde naar verwezen. Het rijnummer wordt eerst vermeld, gevolgd door het kolomnummer of de letter (beide formaten zijn acceptabel).
Een regel code die een verwijzing naar cel C5 bevat, ziet er bijvoorbeeld als volgt uit:
Werkmappen ("Boek2.xlsm"). Bladen ("Lijst2"). Cellen (5, 3)
Werkmappen ("Boek2.xlsm"). Bladen ("Lijst2"). Cellen (5, "C")
U kunt een cel ook openen met een object Netjes. Over het algemeen is het de bedoeling om een verwijzing naar een bereik te geven (waarvan de elementen overigens ook absoluut of relatief kunnen zijn), maar je kunt gewoon een celnaam geven, in hetzelfde formaat als in een Excel-document.
In dit geval ziet de regel er als volgt uit.
Werkmappen ("Boek2.xlsm"). Bladen ("Lijst2"). Bereik ("C5")
Het lijkt misschien dat deze optie handiger is, maar het voordeel van de eerste twee opties is dat je variabelen tussen haakjes kunt gebruiken en een link kunt geven die niet langer absoluut is, maar zoiets als een relatieve, die zal afhangen van de resultaten van de berekeningen.
Macro's kunnen dus effectief in programma's worden gebruikt. In feite zijn alle verwijzingen naar cellen of bereiken hier absoluut en kunnen ze daarom ook worden vastgelegd. Toegegeven, het is niet zo handig. Het gebruik van macro's kan handig zijn bij het schrijven van complexe programma's met een groot aantal stappen in het algoritme. Over het algemeen is de standaardmanier om absolute of relatieve verwijzingen te gebruiken veel handiger.
Conclusies
We hebben uitgezocht wat een celverwijzing is, hoe het werkt, waar het voor dient. We begrepen het verschil tussen absolute en relatieve verwijzingen en bedachten wat er moest gebeuren om het ene type in het andere te veranderen (in eenvoudige bewoordingen, het adres repareren of losmaken). We hebben uitgezocht hoe je dat meteen kunt doen met een groot aantal waarden. Nu heb je de flexibiliteit om deze functie in de juiste situaties te gebruiken.