Inhoud
Om berekeningen met formules in de automatische modus te vergemakkelijken, worden verwijzingen naar cellen gebruikt. Afhankelijk van het type schrijven, zijn ze onderverdeeld in drie hoofdtypen:
- Relatieve links. Gebruikt voor eenvoudige berekeningen. Bij het kopiëren van een formule moeten de coördinaten worden gewijzigd.
- Absolute koppelingen. Als u complexere berekeningen moet maken, is deze optie geschikt. Gebruik voor het bevestigen het symbool “$”. Voorbeeld: $A$1.
- gemengde koppelingen. Dit type adressering wordt gebruikt in berekeningen wanneer het nodig is om een kolom of regel apart vast te leggen. Voorbeeld: $A1 of A$1.
Als het nodig is om de gegevens van de ingevoerde formule te kopiëren, worden verwijzingen met absolute en gemengde adressering gebruikt. Het artikel laat met voorbeelden zien hoe berekeningen worden gemaakt met behulp van verschillende soorten links.
Relatieve celverwijzing in Excel
Dit is een reeks tekens die de locatie van een cel bepalen. Links in het programma worden automatisch geschreven met relatieve adressering. Bijvoorbeeld: A1, A2, B1, B2. Als u naar een andere rij of kolom gaat, worden de tekens in de formule gewijzigd. Bijvoorbeeld startpositie A1. Als u horizontaal beweegt, verandert de letter in B1, C1, D1, enz. Op dezelfde manier treden er wijzigingen op wanneer u langs een verticale lijn beweegt, alleen in dit geval verandert het nummer - A2, A3, A4, enz. Als het nodig is om te dupliceren een berekening van hetzelfde type in een aangrenzende cel, wordt een berekening uitgevoerd met behulp van een relatieve referentie. Volg een paar stappen om deze functie te gebruiken:
- Zodra de gegevens in de cel zijn ingevoerd, verplaatst u de cursor en klikt u met de muis. Markering met een groene rechthoek geeft de activering van de cel aan en de gereedheid voor verder werk.
- Door op de toetscombinatie Ctrl + C te drukken, kopiëren we de inhoud naar het klembord.
- We activeren de cel waarnaar u gegevens of een eerder geschreven formule wilt overbrengen.
- Door op de combinatie Ctrl + V te drukken, brengen we de gegevens over die zijn opgeslagen op het systeemklembord.
Deskundig advies! Gebruik een lifehack om hetzelfde type berekeningen in de tabel uit te voeren. Selecteer de cel met de eerder ingevoerde formule. Beweeg de cursor over een vierkantje dat in de rechterbenedenhoek verschijnt en houd de linkermuisknop ingedrukt en sleep naar de onderste rij of uiterste kolom, afhankelijk van de uitgevoerde actie. Door de muisknop los te laten, wordt de berekening automatisch uitgevoerd. Deze tool wordt de automatische vulmarkering genoemd.
Relatieve link voorbeeld
Om het duidelijker te maken, kunt u een voorbeeld bekijken van een berekening met een formule met een relatieve referentie. Stel dat de eigenaar van een sportwinkel na een jaar werken de winst uit de verkoop moet berekenen.
De volgorde van acties:
- Het voorbeeld laat zien dat de kolommen B en C werden gebruikt om de hoeveelheid verkochte goederen en de prijs ervan in te vullen. Dienovereenkomstig, om de formule te schrijven en het antwoord te krijgen, selecteert u kolom D. De formule ziet er als volgt uit: = B2 *C
Let op! Gebruik een klein trucje om het proces van het schrijven van een formule te vergemakkelijken. Zet het "=" teken, klik op de hoeveelheid verkochte goederen, stel het "*" teken in en klik op de prijs van het product. De formule na het gelijkteken wordt automatisch geschreven.
- Druk op "Enter" voor het definitieve antwoord. Vervolgens moet u het totale bedrag aan winst berekenen dat u van andere soorten producten hebt ontvangen. Welnu, als het aantal regels niet groot is, kunnen alle manipulaties handmatig worden uitgevoerd. Om in Excel een groot aantal rijen tegelijk in te vullen, is er één handige functie die het mogelijk maakt om de formule over te zetten naar andere cellen.
- Beweeg de cursor over de rechter benedenhoek van de rechthoek met de formule of het eindresultaat. Het verschijnen van een zwart kruis dient als een signaal dat de cursor naar beneden kan worden gesleept. Zo wordt automatisch de ontvangen winst voor elk product afzonderlijk berekend.
- Door de ingedrukte muisknop los te laten, krijgen we de juiste resultaten in alle regels.
Door op cel D3 te klikken, kunt u zien dat de celcoördinaten automatisch zijn gewijzigd en er nu als volgt uitzien: =B3 *C3. Hieruit volgt dat de verbanden relatief waren.
Mogelijke fouten bij het werken met relatieve links
Deze Excel-functie vereenvoudigt ongetwijfeld de berekeningen aanzienlijk, maar in sommige gevallen kunnen er problemen optreden. Laten we een eenvoudig voorbeeld bekijken van het berekenen van de winstcoëfficiënt van elk artikel:
- Maak een tabel aan en vul in: A – productnaam; B – verkochte hoeveelheid; C – kosten; D is het ontvangen bedrag. Stel er zijn slechts 11 artikelen in het assortiment. Daarom, rekening houdend met de beschrijving van de kolommen, worden 12 regels gevuld en is het totale winstbedrag D
- Klik op cel E2 en voer in =D2 /D13.
- Nadat u op de knop "Enter" hebt gedrukt, verschijnt de coëfficiënt van het relatieve aandeel van de verkoop van het eerste artikel.
- Rek de kolom naar beneden en wacht op het resultaat. Het systeem geeft echter de fout "#DIV/0!"
De reden voor de fout is het gebruik van een relatieve referentie voor berekeningen. Als gevolg van het kopiëren van de formule veranderen de coördinaten. Dat wil zeggen, voor E3 ziet de formule er als volgt uit: =D3 /D13. Omdat cel D13 niet gevuld is en theoretisch een nulwaarde heeft, zal het programma een foutmelding geven met de informatie dat delen door nul onmogelijk is.
Belangrijk! Om de fout te corrigeren, is het noodzakelijk om de formule zo te schrijven dat de D13-coördinaten vastliggen. Relatieve adressering heeft een dergelijke functionaliteit niet. Om dit te doen, is er een ander type links - absoluut.
Hoe maak je een absolute link in Excel
Dankzij het gebruik van het $-symbool werd het mogelijk om de celcoördinaten vast te leggen. Hoe dit werkt, zullen we verder bekijken. Aangezien het programma standaard relatieve adressering gebruikt om het absoluut te maken, moet u een aantal acties uitvoeren. Laten we de oplossing voor de fout "Hoe de coëfficiënt te vinden bij de verkoop van verschillende artikelen" analyseren, de berekening uitvoeren met behulp van absolute adressering:
- Klik op E2 en voer de coördinaten van de link in =D2 /D13. Omdat de link relatief is, moet er een symbool worden ingesteld om de gegevens vast te leggen.
- Corrigeer de coördinaten van cel D Om deze actie uit te voeren, laat u de letter die de kolom aangeeft en het rijnummer voorafgaan door een "$"-teken te plaatsen.
Deskundig advies! Om het invoeren te vergemakkelijken, volstaat het om de cel te activeren voor het bewerken van de formule en meerdere keren op de F4-toets te drukken. Totdat je bevredigende waarden krijgt. De juiste formule is als volgt: =D2/$D$13.
- Druk op de knop "Enter". Als gevolg van de uitgevoerde acties zou het juiste resultaat moeten verschijnen.
- Sleep de markering naar de onderste regel om de formule te kopiëren.
Door het gebruik van absolute adressering in de berekeningen zullen de uiteindelijke resultaten in de overige rijen correct zijn.
Hoe een gemengde link in Excel te plaatsen
Voor formuleberekeningen worden niet alleen relatieve en absolute referenties gebruikt, maar ook gemengde. Hun onderscheidende kenmerk is dat ze een van de coördinaten vastleggen.
- Als u bijvoorbeeld de positie van een regel wilt wijzigen, moet u het $ -teken voor de letteraanduiding schrijven.
- Integendeel, als het dollarteken na de letteraanduiding wordt geschreven, blijven de indicatoren in de regel ongewijzigd.
Hieruit volgt dat om het vorige probleem met het bepalen van de verkoopcoëfficiënt van goederen met behulp van gemengde adressering op te lossen, het nodig is om het regelnummer vast te leggen. Dat wil zeggen, het $-teken wordt achter de kolomletter geplaatst, omdat de coördinaten ervan niet veranderen, zelfs niet in een relatieve verwijzing. Laten we een voorbeeld nemen:
- Voer voor nauwkeurige berekeningen in: =D1/$D$3 en druk op "Enter". Het programma geeft het exacte antwoord.
- Om de formule naar de volgende cellen in de kolom te verplaatsen en nauwkeurige resultaten te krijgen, sleept u de greep naar de onderste cel.
- Hierdoor zal het programma de juiste berekeningen geven.
Opgelet! Als u het $ teken voor de letter zet, geeft Excel een foutmelding “#DIV/0!”, wat betekent dat deze bewerking niet kan worden uitgevoerd.
"SuperAbsolute" adressering
Laten we tot slot kijken naar een ander voorbeeld van een absolute link – “SuperAbsolute” adressering. Wat zijn de kenmerken en verschillen. Neem het geschatte getal 30 en voer het in cel B2 in. Het is dit nummer dat het belangrijkste zal zijn, het is noodzakelijk om er een reeks acties mee uit te voeren, bijvoorbeeld om het tot een macht te verheffen.
- Voor de juiste uitvoering van alle acties voert u de volgende formule in kolom C in: =$B$2^$D2. In kolom D vullen we de waarde van de graden in.
- Nadat we op de knop "Enter" hebben gedrukt en de formule hebben geactiveerd, rekken we de markering in de kolom uit.
- We krijgen de juiste resultaten.
Het komt erop neer dat alle uitgevoerde acties verwezen naar één vaste cel B2, dus:
- Het kopiëren van een formule van cel C3 naar cel E3, F3 of H3 verandert het resultaat niet. Het blijft ongewijzigd - 900.
- Als u een nieuwe kolom moet invoegen, veranderen de coördinaten van de cel met de formule, maar het resultaat blijft ook ongewijzigd.
Dit is de eigenaardigheid van de "SuperAbsolute"-link: als u moet verplaatsen, verandert het resultaat niet. Er zijn echter situaties waarin gegevens worden ingevoegd uit bronnen van derden. De kolommen worden dus opzij geschoven en de gegevens worden op de oude manier in kolom B2 gezet. Wat gebeurt er in dit geval? Wanneer gemengd, verandert de formule volgens de uitgevoerde actie, dat wil zeggen, het zal niet langer naar B2, maar naar C2 wijzen. Maar aangezien de invoeging in B2 is gemaakt, zal het uiteindelijke resultaat verkeerd zijn.
Referentie! Om macro's van externe bronnen te kunnen invoegen, moet u de ontwikkelaarsinstellingen inschakelen (deze zijn standaard uitgeschakeld). Ga hiervoor naar Opties, open de lintinstellingen en vink het vakje in de rechterkolom naast “Ontwikkelaar” aan. Daarna opent de toegang tot vele functies die voorheen voor de ogen van de gemiddelde gebruiker verborgen waren.
Dit roept de vraag op: is het mogelijk om de formule van cel C2 aan te passen zodat het oorspronkelijke nummer wordt verzameld uit cel B, ondanks het invoegen van nieuwe gegevenskolommen? Om ervoor te zorgen dat wijzigingen in de tabel geen invloed hebben op de bepaling van het totaal bij het installeren van gegevens uit bronnen van derden, moet u de volgende stappen uitvoeren:
- Voer in plaats van de coördinaten van cel B2 de volgende indicatoren in: =INDIRECT(“B2”). Als gevolg hiervan ziet de formuleringssamenstelling er na het verplaatsen als volgt uit: =INDIRECT(“B2”)^$E2.
- Dankzij deze functie wijst de link altijd naar het vierkant met coördinaten B2, ongeacht of er kolommen worden toegevoegd of verwijderd in de tabel.
Het moet duidelijk zijn dat een cel die geen gegevens bevat altijd de waarde "0" heeft.
Conclusie
Door het gebruik van drie varianten van de beschreven koppelingen ontstaan er veel mogelijkheden die het werken met berekeningen in Excel vergemakkelijken. Lees daarom, voordat u met formules gaat werken, eerst de links en de regels voor de installatie ervan.