Inhoud
Bij het gebruik van Microsoft Excel weet de gebruiker vaak niet van tevoren hoeveel informatie daardoor in de tabel zal staan. Daarom begrijpen we niet in alle situaties welk bereik moet worden gedekt. Een set cellen is immers een variabel begrip. Om van dit probleem af te komen, is het noodzakelijk om het genereren van het bereik automatisch te maken, zodat het uitsluitend is gebaseerd op de hoeveelheid gegevens die door de gebruiker is ingevoerd.
Automatisch celbereik wijzigen in Excel
Het voordeel van automatische bereiken in Excel is dat ze het gebruik van formules veel gemakkelijker maken. Bovendien maken ze het mogelijk om de analyse van complexe gegevens die een groot aantal formules bevatten, die veel functies bevatten, aanzienlijk te vereenvoudigen. U kunt dit bereik een naam geven, waarna het automatisch wordt bijgewerkt, afhankelijk van de gegevens die het bevat.
Hoe u automatisch bereik kunt wijzigen in Excel
Stel dat u een belegger bent die in een object moet investeren. Daarom willen we informatie krijgen over hoeveel u in totaal kunt verdienen voor de hele tijd dat het geld voor dit project zal werken. Om deze informatie te verkrijgen, moeten we echter regelmatig controleren hoeveel totale winst dit object ons oplevert. Maak hetzelfde rapport als in deze screenshot.
Op het eerste gezicht ligt de oplossing voor de hand: je hoeft alleen maar de hele kolom op te tellen. Als er vermeldingen in verschijnen, wordt het bedrag onafhankelijk bijgewerkt. Maar deze methode heeft veel nadelen:
- Als het probleem op deze manier wordt opgelost, is het niet mogelijk om de cellen in kolom B voor andere doeleinden te gebruiken.
- Zo'n tabel verbruikt veel RAM, waardoor het onmogelijk is om het document op zwakke computers te gebruiken.
Daarom is het noodzakelijk om dit probleem op te lossen door middel van dynamische namen. Om ze te maken, moet u de volgende reeks acties uitvoeren:
- Ga naar het tabblad "Formules", dat zich in het hoofdmenu bevindt. Er zal een sectie "Gedefinieerde namen" zijn, met een knop "Een naam toewijzen", waarop we moeten klikken.
- Vervolgens verschijnt er een dialoogvenster waarin u de velden moet invullen zoals weergegeven in de schermafbeelding. Het is belangrijk op te merken dat we de functie moeten toepassen = VERPLAATSING samen met de functie BEKIJKom een automatisch bijgewerkt bereik te creëren.
- Daarna moeten we de functie gebruiken SOM, die ons dynamisch bereik als argument gebruikt.
Na het voltooien van deze stappen, kunnen we zien hoe de dekking van de cellen die tot het "inkomen" -bereik behoren, wordt bijgewerkt als we daar nieuwe elementen toevoegen.
OFFSET-functie in Excel
Laten we eens kijken naar de functies die we eerder in het veld "bereik" hebben vastgelegd. De functie gebruiken: VERWIJDEREN we kunnen de hoeveelheid bereik bepalen, gegeven hoeveel cellen in kolom B zijn gevuld. De functieargumenten zijn als volgt:
- Begin cel. Met dit argument kan de gebruiker aangeven welke cel in het bereik als linksboven wordt beschouwd. Het zal naar beneden en naar rechts rapporteren.
- Bereik gecompenseerd door rijen. Met behulp van dit bereik stellen we het aantal cellen in waarmee een verschuiving moet plaatsvinden vanaf de cel linksboven in het bereik. U kunt niet alleen positieve waarden gebruiken, maar ook nul en minus. In dit geval kan de verplaatsing helemaal niet plaatsvinden of in de tegenovergestelde richting worden uitgevoerd.
- Bereik verschoven door kolommen. Deze parameter is vergelijkbaar met de vorige, alleen kunt u hiermee de mate van horizontale verschuiving van het bereik instellen. Hier kunt u ook zowel nul- als negatieve waarden gebruiken.
- De hoeveelheid bereik in hoogte. In feite maakt de titel van dit argument ons duidelijk wat het betekent. Dit is het aantal cellen waarmee het bereik zou moeten toenemen.
- De waarde van het bereik in de breedte. Het argument is vergelijkbaar met het vorige, alleen betreft het de kolommen.
U hoeft de laatste twee argumenten niet op te geven als dat niet nodig is. In dit geval is de bereikwaarde slechts één cel. Als u bijvoorbeeld de formule opgeeft: =VERSCHUIVING(A1;0;0), zal deze formule verwijzen naar dezelfde cel als die in het eerste argument. Als de verticale offset is ingesteld op 2 eenheden, verwijst de cel in dit geval naar cel A3. Laten we nu in detail beschrijven wat de functie betekent BEKIJK.
COUNT-functie in Excel
De functie gebruiken BEKIJK we bepalen hoeveel cellen in kolom B we in totaal hebben ingevuld. Dat wil zeggen, met behulp van twee functies bepalen we hoeveel cellen in het bereik zijn gevuld en bepalen we op basis van de ontvangen informatie de grootte van het bereik. Daarom zal de uiteindelijke formule de volgende zijn: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)
Laten we eens kijken hoe we het principe van deze formule correct kunnen begrijpen. Het eerste argument wijst naar waar ons dynamisch bereik begint. In ons geval is dit cel B2. Verdere parameters hebben nulcoördinaten. Dit suggereert dat we geen offsets nodig hebben ten opzichte van de cel linksboven. Het enige dat we invullen is de verticale grootte van het bereik, waarvoor we de functie hebben gebruikt als BEKIJK, waarmee het aantal cellen wordt bepaald dat bepaalde gegevens bevat. De vierde parameter die we hebben ingevuld is de eenheid. We laten dus zien dat de totale breedte van het bereik één kolom moet zijn.
Dus, met behulp van de functie BEKIJK de gebruiker kan het geheugen zo efficiënt mogelijk gebruiken door alleen die cellen te laden die enkele waarden bevatten. Dienovereenkomstig zullen er geen extra fouten zijn in het werk dat verband houdt met de slechte prestaties van de computer waarop de spreadsheet zal werken.
Dienovereenkomstig, om de grootte van het bereik te bepalen, afhankelijk van het aantal kolommen, moet u een vergelijkbare reeks acties uitvoeren, alleen in dit geval moet u de eenheid specificeren in de derde parameter en de formule in de vierde BEKIJK.
We zien dat je met behulp van Excel-formules niet alleen wiskundige berekeningen kunt automatiseren. Dit is slechts een druppel op een gloeiende plaat, maar in feite kunt u hiermee bijna elke handeling die in u opkomt automatiseren.
Dynamische grafieken in Excel
Dus in de laatste stap konden we een dynamisch bereik creëren, waarvan de grootte volledig afhangt van het aantal gevulde cellen dat het bevat. Nu kunt u op basis van deze gegevens dynamische grafieken maken die automatisch veranderen zodra de gebruiker wijzigingen aanbrengt of een extra kolom of rij toevoegt. De volgorde van acties is in dit geval als volgt:
- We selecteren ons assortiment, waarna we een grafiek van het type “Histogram met groepering” invoegen. U vindt dit item in de sectie "Invoegen" in de sectie "Grafieken - Histogram".
- We klikken met de linkermuisknop op een willekeurige kolom van het histogram, waarna de functie =SERIES() in de functieregel wordt getoond. In de schermafbeelding ziet u de gedetailleerde formule.
- Daarna moeten er enkele wijzigingen in de formule worden aangebracht. U moet het bereik vervangen na "Blad1!" naar de naam van het bereik. Dit resulteert in de volgende functie: =RIJ(Blad1!$B$1;;Blad1!inkomen;1)
- Nu blijft het om een nieuw record aan het rapport toe te voegen om te controleren of de grafiek automatisch wordt bijgewerkt of niet.
Laten we eens kijken naar ons diagram.
Laten we samenvatten hoe we het hebben gedaan. In de vorige stap hebben we een dynamisch bereik gemaakt waarvan de grootte afhangt van het aantal elementen dat het bevat. Om dit te doen, hebben we een combinatie van functies gebruikt BEKIJK и VERWIJDEREN. We hebben dit bereik een naam gegeven en vervolgens hebben we de verwijzing naar deze naam gebruikt als het bereik van ons histogram. Welk specifiek bereik je in de eerste fase als gegevensbron kiest, is niet zo belangrijk. Het belangrijkste is om het later te vervangen door de naam van het bereik. Zo kun je veel RAM besparen.
Benoemde bereiken en hun gebruik
Laten we nu in meer detail praten over het correct maken van benoemde bereiken en deze gebruiken om de taken uit te voeren die zijn ingesteld voor de Excel-gebruiker.
Standaard gebruiken we gewone celadressen om tijd te besparen. Dit is handig wanneer u een of meerdere keren een bereik moet schrijven. Als het altijd moet worden gebruikt of als het adaptief moet zijn, moeten benoemde bereiken worden gebruikt. Ze maken het maken van formules veel gemakkelijker en het zal voor de gebruiker niet zo moeilijk zijn om complexe formules te analyseren die een groot aantal functies bevatten. Laten we enkele van de stappen beschrijven die betrokken zijn bij het maken van dynamische bereiken.
Het begint allemaal met het benoemen van de cel. Om dit te doen, selecteert u het eenvoudig en schrijft u de naam die we nodig hebben in het veld van de naam. Het is belangrijk dat het gemakkelijk te onthouden is. Er zijn enkele beperkingen waarmee u rekening moet houden bij het benoemen:
- De maximale lengte is 255 tekens. Dit is voldoende om een naam toe te kennen die uw hartje begeert.
- De naam mag geen spaties bevatten. Daarom, als het meerdere woorden bevat, is het mogelijk om ze te scheiden met het onderstrepingsteken.
Als we later op andere bladen van dit bestand deze waarde moeten weergeven of toepassen om verdere berekeningen uit te voeren, dan is het niet nodig om naar het allereerste blad over te schakelen. U kunt gewoon de naam van deze bereikcel opschrijven.
De volgende stap is het maken van een benoemd bereik. De procedure is in principe hetzelfde. Eerst moet u een bereik selecteren en vervolgens de naam opgeven. Daarna kan deze naam worden gebruikt in alle andere gegevensbewerkingen in Excel. Benoemde bereiken worden bijvoorbeeld vaak gebruikt om de som van waarden te definiëren.
Daarnaast is het mogelijk om een benoemd bereik te maken met behulp van het tabblad Formules met behulp van de tool Naam instellen. Nadat we het hebben geselecteerd, verschijnt er een venster waarin we een naam voor ons bereik moeten kiezen en het gebied moeten specificeren waartoe het handmatig zal worden uitgebreid. U kunt ook specificeren waar dit bereik zal werken: binnen een enkel vel of in het hele boek.
Als er al een naambereik is gemaakt, is er een speciale service die een naammanager wordt genoemd om het te gebruiken. Het maakt het niet alleen mogelijk om nieuwe namen te bewerken of toe te voegen, maar ook om ze te verwijderen als ze niet langer nodig zijn.
Houd er rekening mee dat bij het gebruik van benoemde bereiken in formules, de formules na het verwijderen niet automatisch worden overschreven met de juiste waarden. Daarom kunnen er fouten optreden. Voordat u een benoemd bereik verwijdert, moet u er daarom voor zorgen dat het niet in een van de formules wordt gebruikt.
Een andere manier om een benoemd bereik te maken, is door het uit een tabel te halen. Om dit te doen, is er een speciale tool genaamd "Maken vanuit selectie". Zoals we begrijpen, moet u, om het te gebruiken, eerst het bereik selecteren dat we zullen bewerken en vervolgens de plaats instellen waar we de koppen hebben. Op basis van deze gegevens verwerkt Excel automatisch alle gegevens en worden titels automatisch toegekend.
Als de titel meerdere woorden bevat, scheidt Excel deze automatisch met een onderstrepingsteken.
Zo hebben we ontdekt hoe u dynamische benoemde bereiken kunt maken en hoe u hiermee het werk met grote hoeveelheden gegevens kunt automatiseren. Zoals u kunt zien, is het voldoende om verschillende functies en de programmatools te gebruiken die in de functionaliteit zijn ingebouwd. Er is helemaal niets ingewikkelds, hoewel het voor een beginner op het eerste gezicht misschien zo lijkt.