Inhoud
Wat zijn dynamische arrays
In september 2018 bracht Microsoft een update uit die een volledig nieuwe tool toevoegt aan Microsoft Excel: Dynamic Arrays en 7 nieuwe functies om ermee te werken. Deze dingen veranderen, zonder overdrijving, de gebruikelijke techniek van het werken met formules en functies radicaal en betreffen letterlijk elke gebruiker.
Overweeg een eenvoudig voorbeeld om de essentie uit te leggen.
Stel we hebben een eenvoudige tabel met gegevens over stadsmaanden. Wat gebeurt er als we een lege cel aan de rechterkant van het blad selecteren en daarin een formule invoeren die niet naar één cel, maar onmiddellijk naar een bereik linkt?
In alle eerdere versies van Excel, na het klikken op Enter we zouden de inhoud van slechts één eerste cel B2 krijgen. Hoe anders?
Welnu, of het zou mogelijk zijn om dit bereik in een soort aggregatiefunctie zoals =SUM(B2:C4) te stoppen en er een totaal voor te krijgen.
Als we meer complexe bewerkingen nodig hadden dan een primitieve som, zoals het extraheren van unieke waarden of Top 3, dan zouden we onze formule als matrixformule moeten invoeren met behulp van een sneltoets Ctrl+Shift+Enter.
Nu is alles anders.
Nadat we zo'n formule hebben ingevoerd, kunnen we gewoon klikken op Enter – en krijg als resultaat onmiddellijk alle waarden uXNUMXbuXNUMXbwaarnaar we verwezen:
Dit is geen magie, maar de nieuwe dynamische arrays die Microsoft Excel nu heeft. Welkom in de nieuwe wereld
Kenmerken van het werken met dynamische arrays
Technisch gezien wordt onze hele dynamische array opgeslagen in de eerste cel G4, waarbij het vereiste aantal cellen naar rechts en naar beneden wordt gevuld met zijn gegevens. Als u een andere cel in de matrix selecteert, is de link in de formulebalk inactief, wat aangeeft dat we ons in een van de "onderliggende" cellen bevinden:
Een poging om een of meer "onderliggende" cellen te verwijderen, leidt tot niets - Excel zal ze onmiddellijk opnieuw berekenen en vullen.
Tegelijkertijd kunnen we veilig naar deze "onderliggende" cellen verwijzen in andere formules:
Als u de eerste cel van een array kopieert (bijvoorbeeld van G4 naar F8), dan zal de hele array (de bijbehorende verwijzingen) in dezelfde richting bewegen als in reguliere formules:
Als we de array moeten verplaatsen, is het voldoende om te verplaatsen (met de muis of een combinatie van Ctrl+X, Ctrl+V), nogmaals, alleen de eerste hoofdcel G4 - daarna wordt deze overgebracht naar een nieuwe plaats en wordt onze hele array opnieuw uitgebreid.
Als u ergens anders op het blad naar de gemaakte dynamische array moet verwijzen, kunt u het speciale teken # ("pond") gebruiken na het adres van de leidende cel:
U kunt nu bijvoorbeeld eenvoudig een vervolgkeuzelijst maken in een cel die verwijst naar de gemaakte dynamische array:
Dynamische array-fouten
Maar wat gebeurt er als er niet genoeg ruimte is om de array uit te breiden, of als er al cellen zijn die al bezet zijn door andere gegevens op het pad? Maak kennis met een fundamenteel nieuw type fouten in Excel - #OVERDRACHT! (#SPIL!):
Zoals altijd, als we op het pictogram met een gele ruit en een uitroepteken klikken, krijgen we een meer gedetailleerde uitleg van de oorzaak van het probleem en kunnen we snel storende cellen vinden:
Soortgelijke fouten zullen optreden als de array van het blad gaat of een samengevoegde cel raakt. Haal je het obstakel weg, dan wordt alles meteen on-the-fly gecorrigeerd.
Dynamische arrays en slimme tabellen
Als de dynamische array verwijst naar een "slimme" tabel die is gemaakt met een sneltoets Ctrl+T of Home – Opmaak als tabel (Home - Opmaken als tabel), dan erft het ook zijn belangrijkste kwaliteit - automatisch aanpassen.
Bij het toevoegen van nieuwe gegevens aan de onderkant of aan de rechterkant, worden de slimme tabel en het dynamische bereik ook automatisch uitgerekt:
Er is echter één beperking: we kunnen geen verwijzing naar dynamisch bereik gebruiken in forums binnen een slimme tabel:
Dynamische arrays en andere Excel-functies
Oké, zeg je. Dit alles is interessant en grappig. Het is niet nodig, zoals voorheen, om de formule handmatig uit te rekken met een verwijzing naar de eerste cel van het oorspronkelijke bereik naar beneden en naar rechts en zo. En dat is alles?
Niet helemaal.
Dynamische arrays zijn niet zomaar een hulpmiddel in Excel. Nu zijn ze ingebed in het hart (of brein) van Microsoft Excel - de rekenmachine. Dit betekent dat andere ons bekende Excel-formules en -functies nu ook het werken met dynamische arrays ondersteunen. Laten we een paar voorbeelden bekijken om u een idee te geven van de diepte van de veranderingen die hebben plaatsgevonden.
transponeren
Om een bereik te transponeren (rijen en kolommen omwisselen) heeft Microsoft Excel altijd een ingebouwde functie gehad TRANSP (TRANSPONEREN). Om het echter te gebruiken, moet u eerst het bereik voor de resultaten correct selecteren (als de invoer bijvoorbeeld een bereik van 5×3 was, dan moet u 3×5 hebben geselecteerd), voer vervolgens de functie in en druk op de combinatie Ctrl+Shift+Enter, omdat het alleen kon werken in de matrixformulemodus.
Nu kun je gewoon één cel selecteren, dezelfde formule erin invoeren en op de normale . klikken Enter – dynamische array doet alles zelf:
Tafel van vermenigvuldiging
Dit is het voorbeeld dat ik gebruikte toen ik werd gevraagd om de voordelen van matrixformules in Excel te visualiseren. Om nu de hele tabel van Pythagoras te berekenen, volstaat het om in de eerste cel B2 te gaan staan, voer daar een formule in die twee arrays vermenigvuldigt (verticale en horizontale reeks getallen 1..10) en klik eenvoudig op Enter:
Lijmen en ombouwen van dozen
Arrays kunnen niet alleen worden vermenigvuldigd, maar ook aan elkaar worden gelijmd met de standaardoperator & (ampersand). Stel dat we de voor- en achternaam uit twee kolommen moeten extraheren en het springende geval in de originele gegevens moeten corrigeren. We doen dit met één korte formule die de hele array vormt, en dan passen we de functie erop toe PROPNACH (JUIST)om het register op te ruimen:
Conclusie Top 3
Stel dat we een aantal getallen hebben waaruit we de top drie resultaten willen afleiden, en ze in aflopende volgorde rangschikken. Dit wordt nu gedaan door één formule en, nogmaals, zonder enige Ctrl+Shift+Enter zoals eerder:
Als u wilt dat de resultaten niet in een kolom, maar in een rij worden geplaatst, volstaat het om de dubbele punt (regelscheidingsteken) in deze formule te vervangen door een puntkomma (elementscheidingsteken binnen één regel). In de Engelse versie van Excel zijn deze scheidingstekens respectievelijk puntkomma's en komma's.
VERT.ZOEKEN meerdere kolommen tegelijk extraheren
Functies VPR (VERT.ZOEKEN) nu kun je waarden niet uit één, maar uit meerdere kolommen tegelijk halen - specificeer gewoon hun nummers (in elke gewenste volgorde) als een array in het derde argument van de functie:
OFFSET-functie die een dynamische array retourneert
Een van de meest interessante en nuttige (na VERT.ZOEKEN) functies voor gegevensanalyse is de functie VERWIJDEREN (VERSCHUIVING), waaraan ik ooit een heel hoofdstuk in mijn boek en een artikel hier heb gewijd. De moeilijkheid bij het begrijpen en beheersen van deze functie is altijd geweest dat het als resultaat een array (bereik) van gegevens retourneerde, maar we konden het niet zien, omdat Excel nog steeds niet wist hoe het met arrays moest werken.
Nu is dit probleem verleden tijd. Zie hoe u nu, met behulp van een enkele formule en een dynamische array die wordt geretourneerd door OFFSET, alle rijen voor een bepaald product uit elke gesorteerde tabel kunt extraheren:
Laten we eens kijken naar haar argumenten:
- A1 – startcel (referentiepunt)
- ПОИСКПОЗ(F2;A2:A30;0) – berekening van de verschuiving van de startcel naar beneden – naar de eerst gevonden kool.
- 0 – verschuiving van het “venster” naar rechts ten opzichte van de startcel
- СЧЁТЕСЛИ(A2:A30;F2) - berekening van de hoogte van het geretourneerde "venster" - het aantal regels waar kool is.
- 4 — grootte van het “venster” horizontaal, dwz output 4 kolommen
Nieuwe functies voor dynamische arrays
Naast het ondersteunen van het dynamische array-mechanisme in oude functies, zijn er verschillende volledig nieuwe functies toegevoegd aan Microsoft Excel, specifiek aangescherpt voor het werken met dynamische arrays. Dit zijn in het bijzonder:
- GRADE (SOORT) - sorteert het invoerbereik en produceert een dynamische array op de uitvoer
- SORTEERPO (SORTEER OP) - kan een bereik sorteren op waarden van een ander
- FILTER (FILTER) - haalt rijen uit het bronbereik op die voldoen aan de opgegeven voorwaarden
- UNIEK (UNIEK) – haalt unieke waarden uit een bereik of verwijdert duplicaten
- SLMASSIEF (WILLEKEURIG) – genereert een reeks willekeurige getallen van een bepaalde grootte
- NAGEBOORTE (REEKS) — vormt een array uit een reeks getallen met een gegeven stap
Meer over hen - een beetje later. Ze zijn een apart artikel waard (en niet één) voor doordachte studie
Conclusies
Als je alles hebt gelezen wat hierboven is geschreven, dan denk ik dat je je al realiseert hoe groot de veranderingen zijn die hebben plaatsgevonden. Zoveel dingen in Excel kunnen nu eenvoudiger, eenvoudiger en logischer worden gedaan. Ik moet toegeven dat ik een beetje geschrokken ben van hoeveel artikelen er nu gecorrigeerd moeten worden hier, op deze site en in mijn boeken, maar ik ben bereid dit met een licht hart te doen.
Samenvattend de resultaten, profs dynamische arrays, kunt u het volgende schrijven:
- Je kunt de combinatie vergeten Ctrl+Shift+Enter. Excel ziet nu geen verschil tussen "gewone formules" en "matrixformules" en behandelt ze op dezelfde manier.
- Over de functie: SUMPRODUCT (SOMPRODUCT), die eerder werd gebruikt om matrixformules in te voeren zonder Ctrl+Shift+Enter je kunt het ook vergeten - nu is het gemakkelijk genoeg SOM и Enter.
- Slimme tabellen en bekende functies (SUM, IF, VLOOKUP, SUMIFS, etc.) ondersteunen nu ook volledig of gedeeltelijk dynamische arrays.
- Er is achterwaartse compatibiliteit: als je een werkmap met dynamische arrays opent in een oude versie van Excel, worden ze matrixformules (tussen accolades) en blijven ze werken in de "oude stijl".
Heb een nummer gevonden minnen:
- U kunt geen afzonderlijke rijen, kolommen of cellen uit een dynamische array verwijderen, dat wil zeggen dat deze als een enkele entiteit leeft.
- U kunt een dynamische array niet op de gebruikelijke manier sorteren Gegevens – Sorteren (Gegevens — Sorteren). Daar is nu een speciale functie voor. GRADE (SOORT).
- Een dynamisch bereik kan niet worden omgezet in een slimme tabel (maar u kunt wel een dynamisch bereik maken op basis van een slimme tabel).
Dit is natuurlijk niet het einde en ik weet zeker dat Microsoft dit mechanisme in de toekomst zal blijven verbeteren.
Waar kan ik downloaden?
En tot slot de hoofdvraag
Microsoft heeft in september 2018 voor het eerst een voorbeeld van dynamische arrays in Excel aangekondigd en getoond tijdens een conferentie Toepassen. In de komende maanden was er een grondige test en introductie van nieuwe functies, eerst op katten medewerkers van Microsoft zelf, en vervolgens op vrijwillige testers uit de kring van Office Insiders. Dit jaar begon de update die dynamische arrays toevoegt geleidelijk aan uitgerold te worden naar reguliere Office 365-abonnees. Ik heb hem bijvoorbeeld pas in augustus ontvangen bij mijn Office 365 Pro Plus (Monthly Targeted) abonnement.
Als uw Excel nog geen dynamische arrays heeft, maar u wilt er toch echt mee werken, dan zijn er de volgende mogelijkheden:
- Als je een Office 365-abonnement hebt, kun je gewoon wachten tot deze update je bereikt. Hoe snel dit gebeurt, hangt af van hoe vaak updates naar uw Office worden geleverd (een keer per jaar, een keer per half jaar, een keer per maand). Als je een zakelijke pc hebt, kun je je beheerder vragen om updates zo in te stellen dat ze vaker worden gedownload.
- Je kunt je aansluiten bij die Office Insiders-testvrijwilligers - dan ontvang je als eerste alle nieuwe functies en functies (maar er is natuurlijk een kans op meer bugs in Excel).
- Heb je geen abonnement, maar een boxed standalone versie van Excel, dan zul je in ieder geval moeten wachten tot de release van de volgende versie van Office en Excel in 2022. Gebruikers van dergelijke versies ontvangen alleen beveiligingsupdates en bugfixes, en alle nieuwe "goodies" gaan nu alleen naar Office 365-abonnees. Triest maar waar
In ieder geval, wanneer dynamische arrays in je Excel verschijnen – na dit artikel ben je er klaar voor 🙂
- Wat zijn matrixformules en hoe gebruik je ze in Excel?
- Venster (bereik) sommatie met behulp van de OFFSET-functie
- 3 manieren om een tabel in Excel te transponeren