Absolute celverwijzing gebruiken in Excel

Wat te weten

• Gebruik een absolute celverwijzing om meerdere items te berekenen op basis van cellen elders en om de rij of kolom constant te houden.
• In deze vergelijking is de absolute celverwijzing A$12: =+B2+B2+A$12.
• De $ symbool “houdt” de rij of kolom constant, zelfs bij het kopiëren of vullen van een kolom of rij met dezelfde formule.

In dit artikel wordt uitgelegd hoe u een absolute celverwijzing gebruikt in Excel 2010 en later om te voorkomen dat formules automatisch worden aangepast aan het aantal rijen of kolommen dat u hebt gevuld.

Wat is een absolute celverwijzing in Excel?

Er zijn twee soorten manieren om naar een cel in een formule in Excel te verwijzen. U kunt een relatieve celverwijzing of een absolute celverwijzing gebruiken.

• Relatieve celverwijzing: Een celadres dat geen $ symbool voor de rij- of kolomcoördinaten bevat. Deze verwijzing werkt de kolom of cel automatisch bij ten opzichte van de oorspronkelijke cel wanneer u deze naar beneden of naar beneden vult.
• Absolute celverwijzing: Een celadres dat het $-symbool voor de rij- of kolomcoördinaten bevat. Dit “houdt” de referentierij of -kolom constant, zelfs wanneer een kolom of rij met dezelfde formule wordt gevuld.

Celverwijzingen kunnen een abstract concept lijken voor degenen die er nieuw zijn.

Absolute celverwijzing gebruiken in Excel

Er zijn een paar verschillende manieren om een ​​absolute referentie in Excel te gebruiken. De methode die u gebruikt, hangt af van welk deel van de verwijzing u constant wilt houden: de kolom of de rij.

1. Neem bijvoorbeeld een spreadsheet waarin u de werkelijke omzetbelasting voor meerdere artikelen wilde berekenen, op basis van referentiecellen elders in het blad.

2. In de eerste cel voert u de formule in met standaard relatieve celverwijzingen voor de artikelprijzen. Maar om de juiste omzetbelasting in de berekening te betrekken, zou u een absolute verwijzing voor de rij gebruiken, maar niet voor de cel.

   

3. Deze formule verwijst op verschillende manieren naar twee cellen. B2 is de cel links van de eerste btw-cel, ten opzichte van de positie van die cel. De A$12 referentiepunten naar het werkelijke btw-nummer vermeld in A12. Het $-symbool houdt de verwijzing naar rij 12 constant, ongeacht in welke richting u aangrenzende cellen vult. Vul alle cellen onder deze eerste om dit in actie te zien.

   

4. U kunt zien dat wanneer u de kolom vult, de cel in de tweede kolom relatieve celverwijzingen gebruikt voor de kostenwaarden in kolom B, waarbij de kolom wordt verhoogd om overeen te komen met de btw-kolom. De verwijzing naar A12 blijft echter constant dankzij het $-symbool, waardoor de rijverwijzing hetzelfde blijft. Vul nu de D-kolom vanaf de C2-cel.

   

5. Nu kun je zien dat voor het vullen van de rij aan de rechterkant de juiste staatsbelasting (Indiana) wordt gebruikt, omdat de kolomverwijzing relatief is (deze schuift er één naar rechts, net als de cel die je vult). De relatieve referentie voor kosten is nu echter ook één naar rechts verschoven, wat niet klopt. U kunt dit oplossen door de oorspronkelijke formule in C2 in plaats daarvan met een absolute verwijzing naar de B-kolom te laten verwijzen.

   

6. Door een $-teken voor “B” te plaatsen, heb je een absolute referentie voor de kolom gecreëerd. Als u nu naar rechts vult, ziet u dat de verwijzing naar de B-kolom voor kosten hetzelfde blijft.

   

7. Als u nu de D-kolom naar beneden vult, ziet u dat alle relatieve en absolute celverwijzingen precies werken zoals u het bedoeld had.

   

8. Absolute celverwijzingen in Excel zijn van cruciaal belang om te bepalen naar welke cellen wordt verwezen wanneer u kolommen of rijen vult. Gebruik het $ -symbool dat u nodig hebt binnen de celverwijzing om de rij- of kolomverwijzing constant te houden, om naar de juiste gegevens in de juiste cel te verwijzen. Wanneer u relatieve en absolute celverwijzingen op deze manier combineert, wordt dit een gemengde celverwijzing genoemd. Als u een gemengde verwijzing gebruikt, is het belangrijk om de kolom of rij van de brongegevens uit te lijnen met de kolom of rij waarin u de formule typt. Als u de rijverwijzing relatief hebt gemaakt, houd er dan rekening mee dat wanneer u naar de zijkant vult, het kolomnummer van de brongegevens samen met de kolom van de formulecel wordt verhoogd. Dit is een van de dingen die relatieve en absolute adressering ingewikkeld maken voor nieuwe gebruikers, maar als je het eenmaal hebt geleerd, heb je veel meer controle over het opnemen van brongegevenscellen in je formulecelverwijzingen.

Absolute celverwijzingen gebruiken om één celverwijzing vast te pinnen

Een andere benadering voor het gebruik van absolute celverwijzing is door deze toe te passen op zowel de kolom als de rij om de formule in wezen te “vastzetten” om slechts één enkele cel te gebruiken, waar deze zich ook bevindt. Met deze aanpak kunt u naar de zijkant of naar beneden vullen en blijft de celverwijzing altijd hetzelfde. Het gebruik van een absolute celverwijzing op zowel kolom als rij is alleen effectief als u slechts naar één cel verwijst in alle cellen die u vult.

1. Met behulp van dezelfde voorbeeldspreadsheet van hierboven, kunt u alleen verwijzen naar het belastingtarief voor één staat door het $ -symbool toe te voegen aan zowel de kolom- als de rijverwijzing.

   

2. Hierdoor blijven zowel de “A”-kolom als de “12”-rij constant, ongeacht in welke richting u de cellen vult. Om dit in actie te zien, vult u de volledige kolom voor ME-omzetbelasting nadat u de formule hebt bijgewerkt met de absolute kolom- en rijverwijzing. U kunt zien dat elke gevulde cel altijd de absolute referentie $A$12 gebruikt. Noch de kolom, noch de rij verandert.

   

3. Gebruik dezelfde formule voor de Indiana btw-kolom, maar gebruik deze keer de absolute verwijzing voor zowel kolom als rij. In dit geval is dat $B$12.

   

4. Vul deze kolom en je zult opnieuw zien dat de verwijzing naar B12 in geen enkele cel verandert, dankzij de absolute verwijzing voor zowel kolom als rij.

   

5. Zoals u kunt zien, zijn er meerdere manieren waarop u absolute celverwijzingen in Excel kunt gebruiken om vergelijkbare taken uit te voeren. Wat absolute verwijzingen u bieden, is de flexibiliteit om constante celverwijzingen te behouden, zelfs wanneer u een groot aantal kolommen of rijen vult. U kunt door relatieve of absolute celverwijzingen bladeren door de verwijzing te markeren en vervolgens op . te drukken F4. Elke keer dat u op F4 drukt, wordt de absolute verwijzing toegepast op de kolom, de rij, zowel de kolom als de cel, of geen van beide. Dit is een eenvoudige manier om uw formule aan te passen zonder het $ -teken te hoeven typen.

Wanneer u absolute celverwijzingen moet gebruiken

In bijna elke branche en elk vakgebied zijn er veel redenen waarom u absolute celverwijzingen in Excel wilt gebruiken.

• Vaste vermenigvuldigers gebruiken (zoals prijs per eenheid) in een grote lijst met items.
• Pas een enkel percentage toe voor elk jaar wanneer u jaarlijkse winstdoelen projecteert.
• Gebruik bij het maken van facturen absolute referenties om voor alle artikelen naar hetzelfde belastingtarief te verwijzen.
• Gebruik absolute celverwijzingen in projectbeheer om te verwijzen naar vaste beschikbaarheidspercentages voor individuele resources.
• Gebruik relatieve kolomverwijzingen en absolute rijverwijzingen om kolomberekeningen in uw cellen waarnaar wordt verwezen, af te stemmen op kolomwaarden in een andere tabel.

Als u een combinatie van relatieve en absolute verwijzingen voor kolommen of rijen gebruikt, moet u er alleen voor zorgen dat de positie van de brongegevenskolommen of -rijen overeenkomt met de kolom of rij van de doelcellen (waar u de formule typt) .