Om naar cellen en bereiken te kunnen verwijzen, is wat elke spreadsheettool laat werken. En Excel is de beste en krachtigste die er is.
In deze zelfstudie behandel ik alles wat u moet weten over het verwijzen naar cellen en bereiken in Excel. Afgezien van de basisreferentie op hetzelfde blad, zou het grootste deel van deze tutorial gaan over: hoe te verwijzen naar een ander blad of werkmap in Excel.
Hoewel er niet veel verschil is in hoe het werkt, verandert het formaat van die verwijzing een beetje wanneer u naar een ander blad in hetzelfde bestand verwijst of naar een volledig afzonderlijk Excel-bestand verwijst.
Er zijn ook enkele belangrijke dingen waarmee u rekening moet houden wanneer u naar een ander blad of andere externe bestanden verwijst.
Maar maak je geen zorgen… niets is te gek!
Tegen de tijd dat u klaar bent met deze zelfstudie, weet u alles wat er te weten valt over het verwijzen naar cellen en bereiken in Excel (in dezelfde werkmap of een andere werkmap).
Laten we beginnen!
Verwijzen naar een cel in hetzelfde blad
Dit is het meest elementaire referentieniveau waarbij u naar een cel op hetzelfde blad verwijst.
Als ik me bijvoorbeeld in cel B1 bevind en naar cel A1 wil verwijzen, zou de indeling zijn:
=A1
Wanneer u dit doet, is de waarde in de cel waarin u deze verwijzing gebruikt dezelfde als die in cel A1. En als u wijzigingen aanbrengt in cel A1, worden deze weergegeven in de cel waarin u deze verwijzing hebt gebruikt.
Verwijzen naar een cel in een ander blad
Als u naar een ander blad in dezelfde werkmap moet verwijzen, moet u het onderstaande formaat gebruiken:
Bladnaam! Celadres
Ten eerste heb je de bladnaam gevolgd door een uitroepteken dat wordt gevolgd door de celverwijzing.
Dus als u naar cel A1 in blad 1 moet verwijzen, moet u de volgende verwijzing gebruiken:
=Blad1!A1
En als u naar een celbereik in een ander blad wilt verwijzen, moet u de volgende indeling gebruiken:
Blad_naam!Eerste_cel:Laatste_cel
Dus als u naar het bereik A1:C10 in een ander blad in dezelfde werkmap wilt verwijzen, moet u de onderstaande verwijzing gebruiken:
=Blad1!A1:C10
Merk op dat ik u alleen de verwijzing naar de cel of het bereik heb laten zien. In werkelijkheid zou je deze in formules gebruiken. Maar het formaat van de hierboven genoemde referenties blijft hetzelfde
In veel gevallen heeft het werkblad waarnaar u verwijst meerdere woorden in de naam. Het kan bijvoorbeeld zijn: Projectgegevens of Verkoopgegevens.
Als u spaties of niet-alfabetische tekens hebt (zoals @, !, #, -, enz.), moet u de naam tussen enkele aanhalingstekens gebruiken.
Als u bijvoorbeeld naar cel A1 wilt verwijzen in het blad met de naam Verkoopgegevens, gebruikt u de onderstaande verwijzing:
='Verkoopgegevens'!A1
En als de naam van het blad Sales-Data is, moet u de onderstaande verwijzing gebruiken om naar cel A1 in dit blad te verwijzen:
='Verkoopgegevens'!A1
Wanneer u naar een blad in dezelfde werkmap verwijst en later de naam van het werkblad wijzigt, hoeft u zich geen zorgen te maken dat de verwijzing kapot gaat. Excel werkt deze referenties automatisch voor u bij.
Hoewel het geweldig is om het formaat van deze verwijzingen te kennen, is het in de praktijk niet zo'n goed idee om deze elke keer handmatig te typen. Het zou tijdrovend en zeer foutgevoelig zijn.
Ik zal u een betere manier laten zien om celverwijzingen in Excel te maken.
Automatisch een verwijzing naar een ander blad in dezelfde werkmap maken
Een veel betere manier om een celverwijzing naar een ander blad te maken, is door Excel eenvoudig naar de cel/het bereik te wijzen waarnaar u de verwijzing wilt maken en Excel deze zelf te laten maken.
Dit zorgt ervoor dat u zich geen zorgen hoeft te maken dat het uitroepteken of aanhalingstekens ontbreken of dat er een ander formaatprobleem opduikt. Excel maakt automatisch de juiste referentie voor je aan.
Hieronder staan de stappen om automatisch een verwijzing naar een ander blad te maken:
- Selecteer de cel in de huidige werkmap waar je de verwijzing nodig hebt
- Typ de formule totdat je de verwijzing nodig hebt (of een gelijk-naar-teken als je alleen de verwijzing wilt)
- Selecteer het blad waarnaar u moet verwijzen
- Selecteer de cel/het bereik waarnaar u wilt verwijzen
- Druk op Enter om het resultaat van de formule te krijgen (of blijf aan de formule werken)
De bovenstaande stappen zouden automatisch een verwijzing naar de cel/het bereik in een ander blad maken. U kunt deze verwijzingen ook zien in de formulebalk. Als je klaar bent, kun je gewoon op de enter-toets drukken en je krijgt het resultaat.
Als u bijvoorbeeld enkele gegevens in cel A1:A10 in een blad met de naam Verkoopgegevens hebt, en u wilt de som van deze waarden in het huidige blad krijgen, zijn de volgende stappen:
- Typ de volgende formule in het huidige blad (waar u het resultaat nodig hebt): =Sum(
- Selecteer het blad 'Verkoopgegevens'.
- Selecteer het bereik dat u wilt toevoegen (A1:A10). Zodra u dit doet, maakt Excel automatisch een verwijzing naar dit bereik aan (u kunt dat zien in de formulebalk)
- Druk op de enter-toets.
Wanneer u een lange formule maakt, moet u mogelijk naar een cel of een bereik in een ander blad verwijzen en vervolgens teruggaan naar het oorspronkelijke blad en daar naar een cel/bereik verwijzen.
Wanneer u dit doet, zult u merken dat Excel automatisch een bladverwijzing invoegt naar het blad waar u de formule hebt. Hoewel dit in orde is en geen kwaad kan, is het niet nodig. In zo'n geval kunt u ervoor kiezen om de verwijzing te behouden of handmatig te verwijderen.
Een ander ding dat u moet weten bij het maken van verwijzingen door het blad en vervolgens de cel / het bereik te selecteren, is dat Excel altijd een relatieve referentie (d.w.z. referenties met n0 $ teken). Dit betekent dat als ik de formule kopieer en plak (een met verwijzing naar een ander blad) in een andere cel, de verwijzing automatisch wordt aangepast.
Hier is een voorbeeld waarin relatieve verwijzingen worden uitgelegd.
Stel dat ik de volgende formule gebruik in cel A1 in het huidige blad (om te verwijzen naar cel A1 in een bladnaam SalesData)
=Verkoopgegevens!A1
Als ik nu deze formule kopieer en in cel A2 plak, verandert de formule in:
=Verkoopgegevens!A1
Dit gebeurt omdat de formule relatief is en wanneer ik deze kopieer en plak, worden de verwijzingen automatisch aangepast.
Als ik wil dat deze verwijzing altijd naar cel A1 in het SalesData-blad verwijst, moet ik de onderstaande formule gebruiken:
=Verkoopgegevens!$A$1
Het dollarteken voor het rij- en kolomnummer vergrendelt deze verwijzingen zodat deze niet veranderen.
Hier is een gedetailleerde zelfstudie waarin u meer te weten kunt komen over absolute, gemengde en relatieve verwijzingen.
Nu we hebben besproken hoe we naar een ander blad in dezelfde werkmap kunnen verwijzen, laten we eens kijken hoe we naar een andere werkmap kunnen verwijzen.
Hoe te verwijzen naar een andere werkmap in Excel
Wanneer u naar een cel of een bereik naar een andere Excel-werkmap verwijst, hangt de indeling van die verwijzing af van of die werkmap open of gesloten is.
En natuurlijk spelen de naam van het werkboek en de werkbladen ook een rol bij het bepalen van het formaat (afhankelijk van of je al dan niet spaties of niet-alfabetische tekens in de naam hebt).
Laten we dus eens kijken naar de verschillende formaten van externe verwijzingen naar een andere werkmap in verschillende scenario's.
Externe verwijzing naar een open werkmap
Als het gaat om het verwijzen naar een externe open werkmap, moet u de naam van de werkmap, de werkbladnaam en het cel-/bereikadres opgeven.
Hieronder vindt u de indeling die u moet gebruiken wanneer u naar een externe open werkmap verwijst:
='[Bestandsnaam]Bladnaam! Celadres
Stel je hebt een werkmap ‘ExampleFile.xlsx’ en je wilt verwijzen naar cel A1 in Blad1 van deze werkmap.
Hieronder vindt u de referentie hiervoor:
=[VoorbeeldBestand.xlsx]Verkoopgegevens!A1
Als er spaties zijn in de naam van de externe werkmap of de bladnaam (of beide), dan moet u de bestandsnaam (tussen vierkante haken) en de bladnaam tussen enkele aanhalingstekens plaatsen.
Hieronder staan de voorbeelden waarbij u de namen tussen enkele aanhalingstekens zou moeten hebben:
='[Voorbeeldbestand.xlsx]Verkoopgegevens'!A1 ='[Voorbeeldbestand.xlsx]Verkoopgegevens'!A1 ='[Voorbeeldbestand.xlsx]Verkoopgegevens'!A1
Hoe u een verwijzing naar een andere werkmap kunt maken (automatisch)
Nogmaals, hoewel het goed is om het formaat te kennen, is het het beste om het niet handmatig te typen.
Wijs in plaats daarvan Excel gewoon in de goede richting en het zal deze referenties voor u maken. Dit is een stuk sneller met veel minder kans op fouten.
Als u bijvoorbeeld enkele gegevens in cel A1:A10 hebt in een werkmap met de naam 'Voorbeeldbestand' in het blad met de naam 'Verkoopgegevens', en u wilt de som van deze waarden in het huidige blad krijgen, zijn de volgende stappen :
- Typ de volgende formule in het huidige blad (waar u het resultaat nodig hebt): =Sum(
- Ga naar de werkmap 'Voorbeeldbestand'
- Selecteer het blad 'Verkoopgegevens'.
- Selecteer het bereik dat u wilt toevoegen (A1:A10). Zodra u dit doet, maakt Excel automatisch een verwijzing naar dit bereik aan (u kunt dat zien in de formulebalk)
- Druk op de enter-toets.
Dit zou onmiddellijk de formule met de juiste verwijzingen creëren.
Een ding dat u zou opvallen bij het maken van een verwijzing naar een externe werkmap, is dat er altijd absolute verwijzingen zullen worden gemaakt. Dit betekent dat er een $-teken voor de rij- en kolomnummers staat. Dit betekent dat als u deze formule naar andere cellen kopieert en plakt, deze vanwege de absolute verwijzing naar hetzelfde bereik zou blijven verwijzen.
Als u dit wilt wijzigen, moet u de verwijzingen handmatig wijzigen.
Externe verwijzing naar een gesloten werkmap
Wanneer een externe werkmap is geopend en u naar deze werkmap verwijst, hoeft u alleen de bestandsnaam, bladnaam en het cel-/bereikadres op te geven.
Maar wanneer dit is gesloten, heeft Excel geen idee waar u zoekt naar de cellen / het bereik waarnaar u verwijst.
Dit is de reden waarom wanneer u een verwijzing naar een gesloten werkmap maakt, u ook het bestandspad moet specificeren.
Hieronder vindt u een verwijzing die verwijst naar cel A1 in het werkblad Blad1 in de werkmap Voorbeeldbestand. Aangezien dit bestand niet geopend is, verwijst het ook naar de locatie waar het bestand is opgeslagen.
='C:\Users\sumit\Desktop\[Voorbeeldbestand.xlsx]Blad1'!$A$1
De bovenstaande referentie heeft de volgende onderdelen:
- Bestandspad - de locatie op uw systeem of netwerk waar het externe bestand zich bevindt
- Bestandsnaam - de naam van de externe werkmap. Dit zou ook de bestandsextensie omvatten.
- Bladnaam - de naam van het blad waarin u naar de cellen/bereiken verwijst
- Cel-/bereikadres - het exacte cel-/bereikadres waarnaar je verwijst
Wanneer u een externe verwijzing naar een geopende werkmap maakt en vervolgens de werkmap sluit, zult u merken dat de verwijzing automatisch verandert. Nadat de externe werkmap is gesloten, voegt Excel automatisch ook een verwijzing naar het bestandspad in.
Impact van het wijzigen van de bestandslocatie op referenties
Wanneer u een verwijzing naar een cel/bereik in een extern Excel-bestand maakt en dit vervolgens sluit, gebruikt de verwijzing nu ook het bestandspad.
Maar als u de bestandslocatie wijzigt, verandert er niets in uw werkmap (waarin u de referentie maakt). Maar sinds je de locatie hebt gewijzigd, is de link nu verbroken.
Dus als u deze werkmap sluit en opent, wordt u verteld dat de koppeling is verbroken en dat u de koppeling moet bijwerken of volledig moet verbreken. Het zal u een prompt tonen zoals hieronder weergegeven:
Wanneer u op Bijwerken klikt, wordt er een andere prompt weergegeven waarin u de opties kunt kiezen om de links te bewerken (waarbij u het onderstaande dialoogvenster ziet)
Als u deze bestanden gekoppeld wilt houden, kunt u de nieuwe locatie van het bestand opgeven door op Waarden bijwerken te klikken. Excel opent een dialoogvenster voor u waarin u de nieuwe bestandslocatie kunt opgeven door daarheen te navigeren en het te selecteren.
Verwijzing naar een gedefinieerde naam (in dezelfde of externe werkmap)
Wanneer u naar cellen en bereiken moet verwijzen, is het een betere manier om gedefinieerde namen voor de bereiken te maken.
Dit is handig omdat het u gemakkelijk maakt om naar deze bereiken te verwijzen met een naam in plaats van een lang en ingewikkeld referentieadres.
Het is bijvoorbeeld gemakkelijker om =SalesData te gebruiken in plaats van =[Voorbeeldbestand.xlsx]Blad1′!$A$1:$A$10
En als u deze gedefinieerde naam in meerdere formules hebt gebruikt en u de verwijzing moet wijzigen, hoeft u dit slechts één keer te doen.
Dit zijn de stappen om een benoemd bereik voor een celbereik te maken:
- Selecteer alle cellen die u in het benoemde bereik wilt opnemen
- Klik op het tabblad Formules
- Klik op de optie Naam definiëren (deze bevindt zich in de groep Gedefinieerde namen)
- Geef dit bereik in het dialoogvenster Nieuwe naam een naam (in dit voorbeeld gebruik ik de naam SalesData). Onthoud dat je geen spaties in de naam mag hebben
- Houd het bereik als werkmap (tenzij u een sterke reden hebt om het op bladniveau te maken)
- Zorg ervoor dat de Verwijst naar het bereik correct is.
- Klik OK.
Nu is uw benoemde bereik gemaakt en kunt u het gebruiken in plaats van de celverwijzingen met celadressen.
Als ik bijvoorbeeld de som van al deze cellen in het SalesData-bereik wil krijgen, kunt u de onderstaande formule gebruiken:
=SOM(Verkoopgegevens)
En wat als u dit benoemde bereik wilt gebruiken in andere werkbladen of zelfs andere werkmappen?
Jij kan!
U hoeft alleen hetzelfde formaat te volgen dat we in de bovenstaande sectie hebben besproken.
Het is niet nodig om terug te gaan naar het begin van dit artikel. Laat me je alle voorbeelden hier zelf geven, zodat je het idee krijgt.
Werkmap versus benoemde bereiken op werkbladniveau: wanneer u een werkmapniveau met de naam bereik maakt, kan er slechts één bereik met die naam zijn. Dus als u ernaar verwijst vanuit externe werkmappen of werkbladen in dezelfde werkmap, hoeft u de werkbladnaam niet op te geven (aangezien deze beschikbaar is voor gebruik in de hele werkmap). Als u het genoemde bereik op het werkbladniveau gebruikt, kunt u het alleen in het werkblad gebruiken en terwijl u ernaar verwijst, moet u ook de werkbladnaam opgeven.Verwijzen naar de gedefinieerde naam in hetzelfde werkblad of dezelfde werkmap
Als u de gedefinieerde naam voor het werkmapniveau hebt gemaakt, kunt u deze overal in de werkmap gebruiken door alleen de gedefinieerde naam zelf te gebruiken.
Als ik bijvoorbeeld de som wil krijgen van alle cellen in het benoemde bereik dat we hebben gemaakt (SaledData), kan ik de onderstaande formule gebruiken:
=SOM(Verkoopgegevens)
Als u een genoemd bereik op werkbladniveau hebt gemaakt, kunt u deze formule alleen gebruiken als het benoemde bereik is gemaakt in hetzelfde blad waar u de formule gebruikt.
Als u het op een ander blad wilt gebruiken (bijvoorbeeld Blad 2), moet u de volgende formule gebruiken:
=SOM(Blad1!$A$1:$A$10)
En als er spaties of alfanumerieke tekens in de bladnaam staan, moet u de bladnaam tussen enkele aanhalingstekens plaatsen.
=SOM('Blad 1'!$A$1:$A$10)
Verwijzen naar de gedefinieerde naam in een andere werkmap (Open of Gesloten)
Als u in een andere werkmap naar een benoemd bereik wilt verwijzen, moet u de naam van de werkmap opgeven en vervolgens de naam van het bereik.
Als u bijvoorbeeld een Excel-werkmap hebt met de naam VoorbeeldFile.xlsx en een benoemd bereik met de naam SalesData, kunt u de onderstaande formule gebruiken om de som van dit bereik uit een andere werkmap te halen:
=SOM(VoorbeeldBestand.xlsx!Verkoopgegevens)
Als er spaties in de bestandsnaam staan, moet u deze tussen enkele aanhalingstekens gebruiken.
=SUM('Voorbeeldbestand.xlsx'!Verkoopgegevens)
Als u benoemde bereiken op bladniveau hebt, moet u zowel de naam van de werkmap als het werkblad opgeven wanneer u ernaar verwijst vanuit een externe werkmap.
Hieronder ziet u een voorbeeld van het verwijzen naar een benoemd bereik op bladniveau:
=SUM('[Voorbeeldbestand.xlsx]Blad1'!Verkoopgegevens)
Zoals ik hierboven ook al zei, is het altijd het beste om benoemde bereiken op werkmapniveau te maken, tenzij u een sterke reden hebt om een werkbladniveau één te maken.
Als u in een gesloten werkmap naar een benoemd bereik verwijst, moet u ook het bestandspad opgeven. Hieronder een voorbeeld hiervan:
=SUM('C:\Gebruikers\sumit\Desktop\Voorbeeldbestand.xlsx'!Verkoopgegevens)
Wanneer u een verwijzing naar een benoemd bereik in een geopende werkmap maakt en vervolgens de werkmap sluit, wijzigt Excel automatisch de verwijzing en voegt het bestandspad toe.
Een verwijzing naar een benoemd bereik maken
Als u veel benoemde bereiken maakt en ermee werkt, is het niet mogelijk om de naam van iedereen te onthouden.
Excel helpt u door u een lijst te tonen van alle benoemde bereiken die u hebt gemaakt en stelt u in staat deze met één klik in formules in te voegen.
Stel dat u een benoemd bereik SalesData hebt gemaakt dat u in een formule wilt gebruiken om alle waarden in het benoemde bereik op te tellen.
Hier zijn de stappen om dit te doen:
- Selecteer de cel waarin u de formule wilt invoeren.
- Voer de formule in tot het punt waar u het benoemde bereik moet invoegen
- Druk op de F3-toets op je toetsenbord. Dit opent het dialoogvenster Naam plakken met de lijst met alle namen die u hebt gemaakt
- Dubbelklik op de naam die u wilt invoegen.
De bovenstaande stappen zouden de naam in de formule invoegen en u kunt blijven werken aan de formule.
Opmerking: deze techniek werkt alleen voor de naambereiken die zich in dezelfde werkmap bevinden. Als u de namen in een externe werkmap wilt krijgen, moet u daarheen gaan, de naam controleren met behulp van het dialoogvenster Naam plakken en dan terugkomen en deze in uw formule gebruiken (in het juiste formaat met behulp van de bestandsnaam).Dit is alles wat u moet weten over hoe u naar andere bladen of werkmappen kunt verwijzen en hoe u een externe verwijzing in Excel kunt maken.
Ik hoop dat je deze tutorial nuttig vond.
