Hoe maak ik een dynamisch kaartbereik?
Ik heb een goede reden om een dynamisch kaartbereik te gebruiken. Soms komt het voor dat u een grafiek maakt en wanneer u deze bijwerkt, u het bereik handmatig moet wijzigen.
Zelfs als u bepaalde gegevens verwijdert, moet u het bereik ervan wijzigen. Misschien lijkt het erop dat het wijzigen van een kaartbereik geen probleem is. Maar hoe zit het als u uw gegevens regelmatig moet bijwerken?
U hebt een dynamisch bereik aan grafische afbeeldingen nodig.
Weet u zeker dat u een dynamisch kaartbereik nodig heeft?
Ja, 100%. Oké, laat me je iets laten zien.
Hieronder heb je een grafiek met het maandbedrag en als je het bedrag voor juni optelt, zijn de grafiekwaarden hetzelfde, er is geen verandering. Het punt is dat u het kaartbereik handmatig moet bijwerken om Jun in de grafiek op te nemen. Dus wat denk jij dat het gebruik van een dynamisch grafisch bereik tijd bespaart?
De gegevenstabel gebruiken voor dynamisch grafiekbereik
Als u Excel versie 2007 of hoger gebruikt, is het gebruik van een gegevenstabel in plaats van een normaal bereik de beste oplossing.
Het enige dat u hoeft te doen, is uw normale bereik naar een tabel converteren (gebruik de sneltoets Ctrl + T) en vervolgens deze tabel gebruiken om een diagram te maken. Elke keer dat u gegevens aan uw tabel toevoegt, wordt het diagram nu ook automatisch bijgewerkt.
In de bovenstaande tabel wordt de tabel automatisch bijgewerkt toen ik het bedrag voor juni toevoegde. Het enige dat u ertoe brengt de volgende methode te gebruiken, is dat wanneer u gegevens uit een tabel verwijdert, uw diagram niet wordt bijgewerkt.
De oplossing voor dit probleem is dat wanneer u gegevens uit het diagram wilt verwijderen, u eenvoudigweg die cel verwijdert met behulp van de verwijderoptie.
Dynamisch benoemd bereik gebruiken
Het gebruik van een dynamisch benoemd bereik voor een diagram is een beetje lastig, maar het is een unieke opzet. Als je dat eenmaal hebt gedaan, is het super eenvoudig te beheren. Daarom heb ik het hele proces in twee stappen verdeeld.
- Een dynamisch benoemd bereik maken.
- De diagrambrongegevens wijzigen in een dynamisch benoemd bereik.
Een dynamisch benoemd bereik creëren voor een dynamisch diagram
Om een dynamisch benoemd bereik te creëren, kunnen we de OFFSET-functie gebruiken.
Snelle introductie tot Offset: het kan de referentie retourneren van een bereik dat een opgegeven aantal rijen en kolommen uit een cel of een celbereik omvat. We hebben de volgende gegevens om een benoemd bereik te maken.
In kolom A hebben we maanden en bedragen in kolom B. En we moeten voor beide kolommen dynamische benoemde bereiken maken, zodat uw diagram automatisch wordt bijgewerkt wanneer u de gegevens bijwerkt.
Download dit bestand om mee te volgen .
Hier zijn de stappen.
- Ga naar het tabblad Formules -> Gedefinieerde namen -> Naambeheer.
- Klik op “Nieuw” om een benoemd bereik te maken.
- Voer nu in het nieuwe naamvenster de volgende formule in (ik zal je later vertellen hoe het werkt).
- =OFFSET(Blad2!$B$2,0,0,COUNTA(Blad2!$B:$B)-1,1)
- Noem uw bereik “bedrag”.
- Klik OK.
- Maak nu nog een benoemd bereik met behulp van de volgende formule.
- =OFFSET(Blad2!$A$2,0,0,COUNTA(Blad2!$A:$A)-1,1)
- Noem het ‘maand’.
- Klik OK.
Op dit moment hebben we twee benoemde bereiken: ‘maand’ en ‘bedrag’. Laat me je nu vertellen hoe het werkt. In de bovenstaande formules heb ik de telfunctie gebruikt om het totale aantal cellen met een waarde te tellen. Vervolgens gebruikte ik deze telwaarde als de offsethoogte om naar een bereik te verwijzen.
In het maandbereik gebruikten we A2 als uitgangspunt voor het verschuiven en tellen van het totale aantal cellen in kolom B met counta (-1 om de kop uit te sluiten) die verwijst naar A2: A7 .
De diagrambrongegevens wijzigen in het dynamische benoemde bereik
Nu moeten we de brongegevens wijzigen in de benoemde bereiken die we zojuist hebben gemaakt. Oh, het spijt me, ik vergat je te vertellen dat je een grafiek moet maken. Voeg een lijngrafiek in . Hier zijn de volgende stappen.
- Klik met de rechtermuisknop op uw diagram en selecteer ‘Gegevens selecteren’.
- Klik onder de bijschriften op Bewerken.
- Wijzig in Seriewaarden de bereikverwijzing naar het bereik met de naam ‘bedrag’.
- Klik OK.
- Klik op de horizontale as op Bewerken.
- Voer het bereik met de naam ‘maand’ in voor het aslabel.
- Klik OK.
Alles is gedaan. Gefeliciteerd, uw diagram heeft nu een dynamisch bereik.
Voorbeeld bestand
Laatste woorden
Het gebruik van een dynamisch bereik aan grafische afbeeldingen bespaart u enorm veel tijd en moeite. U hoeft uw databereik niet steeds opnieuw te wijzigen. Elke keer dat u uw gegevens bijwerkt, wordt uw diagram onmiddellijk bijgewerkt.