Inhoud
Heeft u tabellen met gegevens in Excel waarvan de grootte kan worden aangepast, dat wil zeggen dat het aantal rijen (kolommen) in de loop van het werk kan toenemen of afnemen? Als de tabelgrootte "zweeft", dan moet je dit moment constant in de gaten houden en corrigeren:
- links in rapportformules die verwijzen naar onze tabel
- initiële reeksen draaitabellen die zijn gebouwd volgens onze tabel
- initiële reeksen grafieken gebouwd volgens onze tabel
- bereiken voor vervolgkeuzelijsten die onze tabel als gegevensbron gebruiken
Dit alles in totaal zorgt ervoor dat je je niet gaat vervelen 😉
Het zal veel handiger en correcter zijn om een dynamisch "rubberen" bereik te creëren, dat zich automatisch in grootte aanpast aan het werkelijke aantal rijen en kolommen met gegevens. Om dit te implementeren zijn er verschillende manieren.
Methode 1. Slimme tabel
Markeer uw celbereik en selecteer op het tabblad Home – Formaat als tabel (Home – Formaat als tabel):
Als u het gestreepte ontwerp dat als bijwerking aan de tafel is toegevoegd niet nodig heeft, kunt u dit uitschakelen op het tabblad dat verschijnt Constructeur (Ontwerp). Elke tabel die op deze manier wordt gemaakt, krijgt een naam die kan worden vervangen door een gemakkelijkere naam op dezelfde plaats op het tabblad Constructeur (Ontwerp) in het veld Tafel naam (Tafel naam).
Nu kunnen we dynamische links naar onze "slimme tabel" gebruiken:
- Tabel 1 – link naar de hele tabel behalve de kopregel (A2:D5)
- Tabel1[#Alle] – link naar de hele tabel (A1:D5)
- Tabel1[Peter] – verwijzing naar een bereikkolom zonder de eerste celkop (C2:C5)
- Tabel1[#Kopteksten] – link naar de “header” met de namen van de kolommen (A1:D1)
Dergelijke verwijzingen werken uitstekend in formules, bijvoorbeeld:
= SUM (Tabel1[Moskou]) – berekening van de som voor de kolom “Moskou”
or
=VPR(F5;Tabel 1;3;0) – zoek in de tabel naar de maand uit cel F5 en geef de St. Petersburg-som ervoor op (wat is VERT.ZOEKEN?)
Dergelijke koppelingen kunnen met succes worden gebruikt bij het maken van draaitabellen door op het tabblad te selecteren Invoegen – Draaitabel (Invoegen – Draaitabel) en het invoeren van de naam van de slimme tabel als de gegevensbron:
Als u een fragment van een dergelijke tabel selecteert (bijvoorbeeld de eerste twee kolommen) en een diagram van een willekeurig type maakt, worden deze bij het toevoegen van nieuwe regels automatisch aan het diagram toegevoegd.
Bij het maken van vervolgkeuzelijsten kunnen directe links naar slimme tabelelementen niet worden gebruikt, maar u kunt deze beperking gemakkelijk omzeilen met een tactische truc - gebruik de functie INDIRECTE (INDIRECT), die de tekst in een link verandert:
Die. een link naar een slimme tabel in de vorm van een tekststring (tussen aanhalingstekens!) verandert in een volwaardige link en de vervolgkeuzelijst neemt deze normaal waar.
Methode 2: Dynamisch benoemd bereik
Als het om de een of andere reden ongewenst is om van uw gegevens een slimme tabel te maken, kunt u een iets gecompliceerdere, maar veel subtielere en veelzijdigere methode gebruiken: maak een dynamisch benoemd bereik in Excel dat naar onze tabel verwijst. Vervolgens kunt u, net als in het geval van een slimme tabel, de naam van het gemaakte bereik vrij gebruiken in formules, rapporten, grafieken, enz. Laten we beginnen met een eenvoudig voorbeeld:
Taak: maak een dynamisch benoemd bereik dat zou verwijzen naar een lijst met steden en automatisch wordt uitgerekt en kleiner wanneer nieuwe steden worden toegevoegd of verwijderd.
We hebben twee ingebouwde Excel-functies nodig die in elke versie beschikbaar zijn − POICPOZ (WEDSTRIJD) om de laatste cel van het bereik te bepalen, en INDEX (INHOUDSOPGAVE) om een dynamische koppeling te maken.
De laatste cel zoeken met MATCH
VERGELIJKEN(opzoekwaarde; bereik; zoektype) – een functie die naar een bepaalde waarde in een bereik (rij of kolom) zoekt en het rangtelwoord teruggeeft van de cel waarin het werd gevonden. Bijvoorbeeld, de formule MATCH(“March”;A1:A5;0) geeft als resultaat het getal 4, omdat het woord “March” in de vierde cel in kolom A1:A5 staat. Het laatste functieargument Match_Type = 0 betekent dat we op zoek zijn naar een exacte match. Als dit argument niet is opgegeven, schakelt de functie over naar de zoekmodus voor de dichtstbijzijnde kleinste waarde - dit is precies wat met succes kan worden gebruikt om de laatste bezette cel in onze array te vinden.
De essentie van de truc is simpel. MATCH zoekt naar cellen in het bereik van boven naar beneden en zou in theorie moeten stoppen wanneer het de dichtstbijzijnde kleinste waarde vindt. Als u een waarde opgeeft die duidelijk groter is dan de beschikbare waarde in de tabel, dan zal MATCH het einde van de tabel bereiken, niets vinden en het volgnummer van de laatst gevulde cel geven. En we hebben het nodig!
Als er alleen getallen in onze array staan, kunnen we een getal specificeren als de gewenste waarde, die duidelijk groter is dan die in de tabel:
Voor een garantie kunt u het getal 9E + 307 gebruiken (9 keer 10 tot de macht 307, dus 9 met 307 nullen) - het maximale aantal waarmee Excel in principe kan werken.
Als er tekstwaarden in onze kolom staan, dan kunt u als equivalent van het grootst mogelijke getal de constructie REPEAT(“i”, 255) invoegen – een tekstreeks bestaande uit 255 letters “i” – de laatste letter van het alfabet. Aangezien Excel eigenlijk karaktercodes vergelijkt bij het zoeken, zal elke tekst in onze tabel technisch "kleiner" zijn dan zo'n lange "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy" regel:
Genereer een link met INDEX
Nu we de positie van het laatste niet-lege element in de tabel kennen, blijft het om een link te vormen naar ons hele assortiment. Hiervoor gebruiken we de functie:
INDEX(bereik; rij_getal; kolom_getal)
Het geeft de inhoud van de cel uit het bereik op rij- en kolomnummer, dwz de functie =INDEX(A1:D5;3;4) in onze tabel met steden en maanden van de vorige methode geeft 1240 – de inhoud uit de 3e rij en 4e kolom, dwz cellen D3. Als er slechts één kolom is, kan het nummer worden weggelaten, dwz formule INDEX(A2:A6;3) geeft “Samara” in de laatste schermafbeelding.
En er is een niet helemaal voor de hand liggende nuance: als de INDEX niet zoals gebruikelijk in de cel wordt ingevoerd na het =-teken, maar wordt gebruikt als het laatste deel van de verwijzing naar het bereik na de dubbele punt, dan geeft het niet langer de inhoud van de cel, maar het adres! Dus een formule als $A$2:INDEX($A$2:$A$100;3) geeft een verwijzing naar het bereik A2:A4 aan de uitgang.
En hier komt de MATCH-functie om de hoek kijken, die we in de INDEX invoegen om dynamisch het einde van de lijst te bepalen:
=$A$2:INDEX($A$2:$A$100; VERGELIJKEN(REP(“I”;255);A2:A100))
Een benoemd bereik maken
Het blijft om het allemaal in één geheel te verpakken. Een tabblad openen formule (Formules) En klik op de Name Manager (Naam Manager). Klik in het geopende venster op de knop creëren (nieuwe), voer onze bereiknaam en formule in het veld in RANGE (Referentie):
Het blijft om op te klikken OK en het kant-en-klare bereik kan in alle formules, vervolgkeuzelijsten of grafieken worden gebruikt.
- De functie VERT.ZOEKEN gebruiken om tabellen en opzoekwaarden te koppelen
- Een automatisch ingevulde vervolgkeuzelijst maken
- Een draaitabel maken om een grote hoeveelheid gegevens te analyseren