Excel-werkbladen verbinden

Naast het aan elkaar verbinden van werkbladen kan Microsoft Excel ook koppelingen maken met andere werkboeken. Voor de goede orde; een werkboek is een bestand op schijf met de extensie .XLS. Elk werkboek kan weer bestaan uit een of meer werkbladen. Waarom zou u werkboeken koppelen? Stel u voor dat u van de regiokantoren west, oost, zuid en noord maandelijks overzichten ontvangt in een gestandaardiseerd formaat. U kunt de cijfers van alle regio's dan consolideren in een apart werkboek.

 

We vertelden al dat Excel standaard drie werkbladen opent. Deze heten Blad1, Blad2 en Blad3 en zijn als tabbladen onderin het venster te vinden. U kunt zelf werkbladen toevoegen, verplaatsen en verwijderen, maar ook hernoemen en ze zelfs van een kleur voorzien. Zo hernoemt u de bestaande werkbladen:

 

 

Nu ontbreekt natuurlijk een werkblad voor het vierde

kwartaal. Zo maakt u een nieuw werkblad:

 

 

Wilt u voortaan standaard vier werkbladen gebruiken, kies dan Extra >> Opties en ga naar het tabblad Algemeen. Geef bij Werkbladen in nieuwe werkmap het aantal van 4 op dat u standaard wilt laten maken als u een nieuwe werkmap opent.

 

U hebt nu een leeg werkboek met vier lege werkbladen, genaamd Q1 tot en met Q4. Het is mogelijk een vijfde werkblad te maken en hierop gegevens uit de andere werkbladen op te tellen. Om tijd en moeite te besparen doen wij het iets anders. U gaat enkele getallen invoeren en op elk werkblad een optelling van de cijfers tot nu maken.

 

 

U heeft nu een driedimensionaal werkboek gemaakt. Het bestaat uit vier werkbladen. In Q1 staat het getal 1000, in Q2 het getal 2000 en in Q3 het getal 1500. We nemen aan dat de cijfers van Q4 nog niet bekend zijn. Nu kunt u per werkblad in cel C3 een totaal berekenen van de cijfers tot dat moment:

 

 

De formule wordt: =B2+'Q1'!B2. Hij betekent: neem de waarde in B2 van dit werkblad en tel daarbij de waarde op van B2 uit het werkblad 'Q1'. Het resultaat is 3000.

 

 

Het geeft niet dat cel B2 in blad Q4 nog leeg is. Zodra hij gevuld wordt zal de optelling plaatsvinden. Probeer maar. Het is ook mogelijk een formule te maken die de waarde van een cel in meer werkbladen tegelijk optelt. Stel dat u de waarde van cel B2 in de werkbladen Q1, Q2 en Q3 wilt sommeren. Typ dan op een willekeurige plaats in een vierde werkblad =SOM('Q1:Q3'!B2).

 

U hebt in het voorgaande de waarden uit het huidige werkblad en die uit een ander werkblad gecombineerd. Zoals u hebt gezien zijn de formules heel eenvoudig. De verwijzing naar een cel in een ander werkblad is tweeledig. Als eerste komt de naam van het werkblad, zoals 'Q2', dan een uitroepteken als verbinding en tot slot de naam van de cel of het bereik. Bijvoorbeeld: =B2+'Q1'!B2. Via hetzelfde systeem is het mogelijk gegevens uit een ander werkboek te gebruiken. Een formule die een verbinding met een cel in een ander werkboek maakt ziet er bijvoorbeeld als volgt uit:

 

=SOM([2006_Q1.xls]Q1!B2:B15)

 

Zoals u ziet is de naam van het werkboek (een bestand) tussen blokhaken geplaatst. Direct daarachter komt de naam van het werkblad, dan een uitroepteken en dan de naam van de cel of reeks. Wanneer dit werkboek niet geopend is bevat de formule bovendien het volledige pad, bijvoorbeeld:

 

=SOM(['F:\Data\Excel\Overzichten\2006_Q1.xls]Q1'!B2:B15)

 

Omdat afwijkende tekens zoals dubbele punten en backslashes worden gebruikt plaatst Excel het gehele pad tussen enkele aanhalingstekens. Natuurlijk hoeft u dergelijke formules niet helemaal in te voeren. U kunt ze gewoon opbouwen zoals in de voorbeelden hierboven. Dat gaat als volgt:

 

 

Wanneer u het werkboek met daarin de verbinding naar een ander werkboek opent wordt de koppeling automatisch bijgewerkt. Daarbij krijgt u mogelijk een bekend venster te zien.

 

 

Dit venster ziet u altijd wanneer Excel koppelingen naar andere werkboeken vindt. Misschien hebt u het wel eens gezien, maar wist u niet wat het was. Of misschien heeft u op de knop Bijwerken geklikt en ging er iets mis. In het laatste geval bent u een logische, maar vervelende beperking van het systeem tegengekomen.

 

Het volledige pad en de bestandsnaam van het gekoppelde bestand wordt in de formule opgenomen. Dat betekent vanzelfsprekend dat het mis gaat wanneer de bestandsnaam wijzigt of wanneer u het bestand verplaatst. Het gaat ook mis wanneer u het werkboek met de koppeling(en) naar iemand anders stuurt. Die beschikt waarschijnlijk niet over de gekoppelde bestanden. In al deze gevallen kan de koppeling niet worden bijgewerkt. 

 

Als u een werkboek met externe verbindingen opent en klikt op Bijwerken, kan het zijn dat u een foutmelding krijgt dat de koppelingen niet kunnen worden bijgewerkt. Doe dan het volgende:

 

 

In bovenstaande situatie is de koppeling hersteld en wordt het nieuwe pad of de nieuwe naam bewaard wanneer u het werkboek opslaat. Als u het gekoppelde werkboek niet kunt vinden kunt u overwegen uw werkboek toch te openen met de knop Doorgaan of de koppeling te bewerken en deze vervolgens te verwijderen. Uiteraard zijn de getallen in uw werkboek dan niet meer gekoppeld aan een bronbestand en daarmee niet actueel of betrouwbaar. Mocht u deze functie later willen gebruiken, kies dan Bewerken >> Koppelingen.

 

U kunt in Excel van een groot aantal werkbladen gebruik maken. Deze zijn als tabbladen onderin het scherm te zien. Het is mogelijk formules te maken die gegevens uit andere werkbladen gebruiken. Reeksen getallen, zoals omzetcijfers van allerlei producten, kunt u op achterliggende werkbladen plaatsen. Op het eerste werkblad maakt u vervolgens formules die de achterliggende gegevens inzichtelijk maken. In plaats van eigen formules kunt u ook een draaitabel gebruiken.

 

Door een verbinding met externe Excel-bestanden te maken kunt u gegevens uit andere werkboeken in uw eigen werkboek gebruiken. Dit biedt meer flexibiliteit. Zo kunt u gestandaardiseerde Excel-werkboeken opstellen en die door anderen laten invullen. Bijvoorbeeld een vestigingsmanager. De retour gestuurde werkboeken koppelt u vervolgens aan een nieuw werkboek. Zo kunt u waarden uit de andere werkboeken tonen, optellingen doen of andere formules toepassen. Een voordeel van deze methode is dat tijdens het openen van uw werkboek alle koppelingen automatisch worden bijgewerkt.

 

U ziet dus steeds de meest recente cijfers, zonder dat u ze zelf heeft moeten intypen. Het nadeel is dat de gekoppelde werkboeken hetzelfde pad en dezelfde naam moeten behouden. Anders kan Excel ze niet vinden en kunnen de gegevens niet worden bijgewerkt.