Merwyn uit Engeland stuurde dit probleem op.
Is er een enkele formule in cel B2 die kan worden gekopieerd om een tafel van vermenigvuldiging te maken?
Het doel van Merwyn is om een enkele formule in B2 in te voeren die gemakkelijk naar alle 144 cellen kan worden gekopieerd om een referentietabel met vermenigvuldiging te maken.
Laten we dit aanvallen als een beginner in Excel en kijken welke valkuilen we tegenkomen. Iemands eerste reactie zou kunnen zijn om de formule in B2 te hebben =A2*B1. Deze formule levert het juiste resultaat op, maar is niet geschikt voor Merwyns opdracht.
Wanneer u deze formule van cel B2 naar cel C2 kopieert, worden de cellen waarnaar in de formule wordt verwezen, ook over één cel verplaatst. De formule die =A2*B1nu was , wordt =C1*B2. Dit is een functie die is ontworpen in Microsoft Excel en een relatieve formuleverwijzing wordt genoemd. Terwijl u een formule kopieert, verplaatsen de celverwijzingen in de formule ook een overeenkomstig aantal cellen naar links en naar beneden.
Er zijn momenten waarop u niet wilt dat Excel dit gedrag vertoont en het huidige geval is er een van. Om te voorkomen dat Excel de verwijzing verandert tijdens het kopiëren van de cellen, voegt u een "$" in voor het gedeelte van de verwijzing dat u wilt blokkeren. Voorbeelden:
- $ A1 vertelt Excel dat u altijd naar kolom A wilt verwijzen.
- B $ 1 vertelt Excel dat u altijd naar rij 1 wilt verwijzen.
- $ B $ 1 vertelt Excel dat u altijd naar cel B1 wilt verwijzen.
Het leren van deze code zal de tijd die nodig is om werkbladen te maken drastisch verminderen. In plaats van 144 formules in te voeren, kan Merwyn deze afkorting gebruiken om één enkele formule in te voeren en deze naar alle cellen te kopiëren.
Als we naar de formule van Merwyn kijken, =B1*A2realiseren we ons dat het "B1" -gedeelte van de uitdrukking altijd naar een getal in Rij 1 moet verwijzen. Dit moet worden herschreven als "B $ 1". De sectie "A2" van de formule moet altijd verwijzen naar een getal in kolom A. Dit moet worden herschreven als "$ A2". Dus de nieuwe formule in cel B2 is =B$1*$A2.
Nadat ik de formule in B2 heb ingevoerd, kan ik deze kopiëren en in het juiste bereik plakken. Excel volgt mijn instructies en na het maken van de kopie vind ik de volgende formules:
- Cel M2 bevat
=M$1*$A2 - Cel B13 bevat
=B$1*$A13 - Cell M13 bevat
=M$1*$A13
Elk van deze soorten formules heeft een naam. Formules zonder dollartekens worden relatieve formules genoemd. Formules waarbij zowel de rij als de kolom zijn vergrendeld met een dollarteken, worden absolute formules genoemd. Formules waarbij de rij of kolom is vergrendeld met een dollarteken, worden gemengde formules genoemd.
Er is een verkorte methode voor het invoeren van de dollartekens. Terwijl u een formule typt en een celverwijzing voltooit, kunt u op de toets drukken om tussen de 4 referentietypen te schakelen. Stel dat u een formule begon te typen en dat u typt =100*G87.
- Druk op F4 en uw formule verandert in
=100*$G$87 - Druk nogmaals op F4 en uw formule verandert in
=100*G$87 - Druk nogmaals op F4 en uw formule verandert in
=100*$G87 - Druk nogmaals op F4 en uw formule keert terug naar het origineel
=100*G87
U kunt tijdens het invoeren van een formule bij elke celverwijzing pauzeren om op F4 te drukken totdat u het juiste referentietype krijgt. De toetsaanslagen om de bovenstaande formule van Merwyn in te voeren zijn =B1*A1.
Een van mijn favoriete toepassingen van verwijzingen naar gemengde formules komt naar voren als ik een lange lijst met vermeldingen heb in kolom A.Als ik een snelle en vuile methode wil om duplicaten te vinden, voer ik deze formule soms in cel B4 in en kopieer deze vervolgens naar beneden:
=VLOOKUP(A4,$A$2:$A3,1,FALSE)
Merk op dat de 2e term van de VERT.ZOEKEN-formule zowel een absolute ($ A $ 2) als een gemengde ($ A3) verwijzing gebruikt om het opzoekbereik te beschrijven. In het Engels vertel ik Excel om altijd van cel $ A $ 2 naar de cel in kolom A net boven de huidige cel te zoeken. Zodra Excel een dubbele waarde tegenkomt, verandert het resultaat van deze formule van # N / A! tot een waarde.
Door creatief gebruik te maken van de $ in celverwijzingen, kunt u ervoor zorgen dat één enkele formule met de juiste resultaten naar veel cellen kan worden gekopieerd.
