Onlangs stond ik op het punt om een unieke combinatie van twee niet-aangrenzende kolommen in Excel te maken. Ik doe dit meestal met Duplicaten verwijderen of met Geavanceerd filter, maar ik dacht dat ik het zou proberen met de nieuwe UNIEKE functie die in 2019 naar Office 365 komt. Ik heb verschillende ideeën geprobeerd en geen enkele zou werken. Dus ging ik voor hulp naar de meester van Dynamic Arrays, Joe McDaid. Het antwoord is best gaaf, en ik weet zeker dat ik het zal vergeten, dus ik documenteer het voor jou en voor mij. Ik weet zeker dat ik over twee jaar Google zal vertellen hoe ik dit moet doen en besef "Oh, kijk! Ik ben degene die het artikel hierover heeft geschreven!"
Bekijk wat ik probeer te doen voordat u naar de functie UNIEK gaat. Ik wil elke unieke combinatie van Verkoopvertegenwoordiger uit kolom B en Product uit kolom C. Normaal gesproken zou ik deze stappen volgen:
- Kopieer de koppen van B1 en D1 naar een leeg gedeelte van het werkblad
- Kies in B1 Gegevens, Filter, Geavanceerd
- Kies Kopiëren naar een nieuwe locatie in het dialoogvenster Geavanceerd filter
- Specificeer de koppen uit stap 1 als het uitvoerbereik
- Kies het vakje voor Alleen unieke waarden
- Klik OK
Het resultaat is elke unieke combinatie van de twee velden. Merk op dat het geavanceerde filter de items niet sorteert - ze verschijnen in de oorspronkelijke volgorde.
Dit proces werd eenvoudiger in Excel 2010 dankzij de opdracht Duplicaten verwijderen op het tabblad Gegevens van het lint. Volg deze stappen:
- Selecteer B1: D227 en Ctrl + C om te kopiëren
-
Plak in een leeg gedeelte van het werkblad.
Maak een kopie van de gegevens, aangezien Duplicaten verwijderen destructief is - Kies Gegevens, verwijder duplicaten
- Schakel Datum uit in het dialoogvenster Duplicaten verwijderen. Dit vertelt Excel om alleen naar Rep en Product te kijken.
-
Klik OK
Vertel Duplicaten verwijderen om alleen rekening te houden met Rep en Date
De resultaten zijn bijna perfect - u hoeft alleen de kolom Datum te verwijderen.
De vraag: is er een manier om de functie UNIEK alleen naar kolommen B & D te laten kijken? (Als je de nieuwe UNIEKE functie nog niet hebt gezien, lees dan: UNIEKE functie in Excel.)
Als =UNIQUE(B2:D227)u ernaar vraagt , krijgt u elke unieke combinatie van vertegenwoordiger, datum en product, wat niet is wat we zoeken.
Toen Dynamic Arrays in september werden geïntroduceerd, zei ik dat we ons nooit meer zorgen hoefden te maken over de complexiteit van Ctrl + Shift + Enter-formules. Maar om dit probleem op te lossen, ga je een concept gebruiken dat hijsen heet. Hopelijk heb je nu mijn Excel Dynamic Arrays Straight To The Point e-book gedownload. Ga naar pagina 31-33 voor een volledige uitleg van tillen.
Neem een Excel-functie die een enkele waarde verwacht. =CHOOSE(Z1,"Apple","Banana")Zou bijvoorbeeld Apple of Banana retourneren, afhankelijk van of Z1 1 (voor Apple) of 2 (voor Banana) bevat. De functie CHOOSE verwacht een scalair als het eerste argument.
Maar in plaats daarvan ga je een matrixconstante van (1,2) doorgeven als het eerste argument voor CHOOSE. Excel voert de hijsoperatie uit en berekent CHOOSE tweemaal. Voor de waarde 1 wilt u de verkoopvertegenwoordigers in B2: B227. Voor de waarde 2 wilt u de producten in D2: D227.
Normaal gesproken zou in het oude Excel de impliciete intersectie de resultaten hebben verpest. Maar nu Excel resultaten naar veel cellen kan morsen, retourneert de bovenstaande formule met succes een array van alle antwoorden in B en D:
Ik heb het gevoel dat ik je intelligentie zou beledigen om de rest van het artikel te schrijven, want vanaf hier is het supereenvoudig.
Wikkel de formule van de vorige schermafbeelding in UNIEK en u krijgt alleen de unieke combinaties van verkoopvertegenwoordiger en productgebruik =UNIQUE(CHOOSE((1,2),B2:B227,D2:D227)).
Om uw begrip te controleren, probeert u de bovenstaande formule te wijzigen om alle unieke combinaties van drie kolommen te retourneren: Verkoopvertegenwoordiger, Product, Kleur.
Verander eerst de matrixconstante om te verwijzen naar (1,2,3).
Voeg vervolgens een vierde argument om te kiezen voor kleur terugkomt van E2: E227: =UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)).
Het zou leuk zijn om die resultaten te sorteren, dus gaan we naar Sorteren met een formule met SORT en SORTBY.
Normaal gesproken zou de functie om oplopend op de eerste kolom te sorteren, =SORT(Array)of zijn =SORT(Array,1,1).
Om op drie kolommen te kunnen sorteren, moet u paarsgewijs optillen met =SORT(Array,(1,2,3),(1,1,1)). Als u in deze formule bij het tweede argument van SORT komt, wil Excel weten op welke kolom moet worden gesorteerd. Verzend in plaats van één waarde drie kolommen binnen een matrixconstante: (1,2,3). Wanneer je bij het derde argument komt, specificeer 1 voor Oplopend of -1 voor Aflopend, stuur dan een matrixconstante met drie enen om Oplopend, Oplopend, Oplopend aan te geven. De volgende schermafbeelding laat zien =SORT(UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)),(1,2,3),(1,1,1)).
U kunt het Excel Dynamic Arrays-boek in ieder geval tot eind 2018 gratis downloaden via de link onderaan deze pagina.
Ik vind het bemoedigend om te ontdekken dat het antwoord op de vraag van vandaag een beetje ingewikkeld is. Toen Dynamic Arrays uitkwam, dacht ik meteen aan alle verbazingwekkende formules die door Aladin Akyurek en anderen op het prikbord waren gepost en hoe die formules veel eenvoudiger zouden worden in de nieuwe Excel. Maar het voorbeeld van vandaag laat zien dat er nog steeds behoefte zal zijn aan formule-genieën om nieuwe manieren te bedenken om de Dynamic Arrays te gebruiken.
Bekijk video
Download Excel-bestand
Om het Excel-bestand te downloaden: uniek-van-niet-aangrenzende-columns.xlsx
Excel-gedachte van de dag
Ik heb mijn Excel Master-vrienden om advies over Excel gevraagd. De gedachte van vandaag om na te denken:
"Regels voor lijsten: geen lege rijen, geen lege kolommen, kopteksten van één cel, zoals bij like"
Anne Walsh
