Wat te weten
- Om meerdere items te berekenen op basis van cellen elders en de rij of kolom constant te houden, gebruikt u een absolute celverwijzing.
- In deze vergelijking is de absolute celverwijzing A$12:=+B2+B2+A$12.
- Het $ symbool "houdt" de rij of kolom constant, zelfs bij het kopiëren of vullen van een kolom of rij met dezelfde formule.
Dit artikel legt uit hoe je een absolute celverwijzing gebruikt in Excel 2010 en later om te voorkomen dat formules automatisch worden aangepast aan het aantal rijen of kolommen dat je 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 in zijn.
Absolute celverwijzing gebruiken in Excel
Er zijn een paar verschillende manieren om een absolute referentie in Excel te gebruiken. De methode die je gebruikt hangt af van welk deel van de verwijzing je constant wilt houden: de kolom of de rij.
-
Neem bijvoorbeeld een spreadsheet waarin u de werkelijke omzetbelasting voor meerdere artikelen wilde berekenen, op basis van referentiecellen elders in het blad.
Image -
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 je een absolute referentie voor de rij gebruiken, maar niet voor de cel.
Image -
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 referentie verwijst naar het werkelijke btw-nummer dat wordt 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.
Image -
Je kunt zien dat wanneer je 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.
Image -
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 verschuift 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 naar de B-kolom te laten verwijzen met een absolute verwijzing.
Image -
Door een $ symbool 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.
Image -
Als je nu de D-kolom naar beneden vult, zul je zien dat alle relatieve en absolute celverwijzingen precies werken zoals je het bedoeld had.
Image -
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 echter 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, ongeacht waar deze zich bevindt.
Met deze aanpak kun je naar de zijkant of naar beneden vullen en de celverwijzing blijft 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.
-
Als u dezelfde voorbeeldspreadsheet van hierboven gebruikt, kunt u alleen verwijzen naar het belastingtarief voor één staat door het $ -symbool toe te voegen aan zowel de kolom- als de rijverwijzing.
Image -
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.
Image -
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.
Image -
Vul deze kolom in en je zult opnieuw zien dat de verwijzing naar B12 in geen enkele cel verandert, dankzij de absolute verwijzing voor zowel kolom als rij.
Image -
Zoals je kunt zien, zijn er meerdere manieren waarop je 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 F4 te drukken 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 $ symbool 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.
- Gebruik vaste vermenigvuldigers (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' typ de formule opnieuw).
