[Excel 2010] Aantal unieke waardes tellen

[Icephase]

Ondanks dat ik behoorlijk wat kennis van Excel heb, loop ik hier tegen een uitdaging aan.

Ik heb een lijst met allerlei attributen uit een clientsysteem, waarvan de belangrijkste het BSN-nummer en het productnummer zijn. Ik wil het aantal unieke waarden voor de combi BSN-product-jaar hebben. Dus als een client in dat jaar al met datzelfde product voorkomt, moet het een 0 zijn, en anders een 1.

Op zich heb ik hiervoor een werkende oplossing; ik maak een unieke ID van de combi BSN-product-jaar en laat met behulp van de volgende formule

code:

 =ALS(ISNB(VERT.ZOEKEN(AF7;$AF$1:$AF6;1;ONWAAR))=WAAR;1;0)

(waarbij in kolom AF de BSN-product-jaar combinaties staan) controleren of deze combi al eerder voorkwam en of deze dus uniek is. Zo ja -> 1 en anders een 0.

Grote probleem is dat deze berekening voor ruim 75.000 rijen echt superlang duurt (+/- 10 minuten) en bij iedere wijziging gaat Excel vrolijk aan het herberekenen en geeft de melding dat er onvoldoende bronnen zijn. Compleet onwerkbaar.

Mijn vraag: is er een makkelijkere manier om het aantal unieke combinaties te tellen? PowerPivot is niet beschikbaar (werkomgeving) en ook andere tooltjes zal ik niet kunnen gebruiken. Het moet echt vanuit Excel zelf geregeld worden.

De uitkomst dient overigens als input voor draaitabellen en draaigrafieken, dus ik kan de oplossing ook niet in die hoek zoeken... het moet echt in de tabel zelf gegenereerd worden.

[Logico]

Je kan het beste INDEX en MATCH gebruiken i.p.v. VERT.ZOEKEN, dat is een beter alternatief voor VERT.ZOEKEN. Je moet er even gevoel bij krijgen hoe het werkt, maar daarna doe je waarschijnlijk niet anders meer. Er zijn talloze websites/youtube filmpjes met uitleg, zou die er even bij pakken

[The_Vice]

hulp draaitabel, aantallen tellen, en dan terughalen in je originele tabel middels formule.
wel refreshen.

Haal in een hulpkolom het aantal van je unieke combinatie op, die je vervolgens als 1/x deelt. Den krijg je per item het unieke percentage deelname, dus totaal samen 1.
Onder aan optellen, of in een pivot, en je hebt het totaal weer 1 per unieke combinatie.

Mag een macro ook? Die heb je iets beter in de hand over wanneer die gaat "herberekenen" kan middels een knop of een on-event gebeurtenis.

[ Voor 81% gewijzigd door The_Vice op 17-11-2018 01:14 ]

[Reptile209]

Moet deze lijst heel vaak worden geüpdatet? Een makkelijke hack is anders: hele lijst kopieeren naar bijv. een ander tabblad. Alles selecteren, Data > Remove duplicates en klaar. Dit kan je ook in een macro gieten om het nog makkelijker te maken natuurlijk.

[Lustucru]

Icephase schreef op vrijdag 16 november 2018 @ 15:48:
Grote probleem is dat deze berekening voor ruim 75.000 rijen echt superlang duurt (+/- 10 minuten) en bij iedere wijziging gaat Excel vrolijk aan het herberekenen en geeft de melding dat er onvoldoende bronnen zijn. Compleet onwerkbaar.

2,8 miljard vergelijkingen duurt inderdaad wel even. Het voordeel van een fatsoenlijke VBA routine is dat je misschien het aantal vergelijkingen terug kunt dringen door je input slim te sorteren en door te lopen, én je kunt zelf bepalen wanneer je hem aftrapt.

[Patrick_6369]

Kun je met iets als dit uit de voeten?

https://exceljet.net/form...numeric-values-in-a-range
https://exceljet.net/form...ue-text-values-in-a-range

Het gebruikt geen VLOOKUP en is daarom wellicht minder zwaar? Ik gebruik zelf ook een freqyency-formule en die werkt super (alleen dan op een bestand met 70 rijen, dus dat is niet echt te vergelijken met jouw bestand).

[Icephase]

Logico schreef op zaterdag 17 november 2018 @ 00:31:
Je kan het beste INDEX en MATCH gebruiken i.p.v. VERT.ZOEKEN, dat is een beter alternatief voor VERT.ZOEKEN. Je moet er even gevoel bij krijgen hoe het werkt, maar daarna doe je waarschijnlijk niet anders meer. Er zijn talloze websites/youtube filmpjes met uitleg, zou die er even bij pakken

In het NL heet dit dus INDEX en VERGELIJKEN. Ik heb hier al eens eerder mee gewerkt, maar vond het vooral veel arbeidsintensiever dan VERT.ZOEKEN; daar heb je alles netjes in 1 formule terwijl met die andere twee je een soort tussenstappen gaat inbouwen.

Ik zal eens kijken of ik hier wat mee kan. Heb je nog concrete tips?

The_Vice schreef op zaterdag 17 november 2018 @ 00:48:
hulp draaitabel, aantallen tellen, en dan terughalen in je originele tabel middels formule.
wel refreshen.

Haal in een hulpkolom het aantal van je unieke combinatie op, die je vervolgens als 1/x deelt. Den krijg je per item het unieke percentage deelname, dus totaal samen 1.
Onder aan optellen, of in een pivot, en je hebt het totaal weer 1 per unieke combinatie.

Mag een macro ook? Die heb je iets beter in de hand over wanneer die gaat "herberekenen" kan middels een knop of een on-event gebeurtenis.

Een macro gaat hem niet worden, dat is uitgeschakeld hier. Daarnaast is mijn ervaring dat macro's niet per se sneller zijn dan formules.

Je andere suggestie ga ik eens even induiken. Die richting zat ik zelf al eens te denken, maar leek me intensiever dan een VERT.ZOEKEN-functie. Bedankt voor de tip.

Reptile209 schreef op zaterdag 17 november 2018 @ 01:31:
Moet deze lijst heel vaak worden geüpdatet? Een makkelijke hack is anders: hele lijst kopieeren naar bijv. een ander tabblad. Alles selecteren, Data > Remove duplicates en klaar. Dit kan je ook in een macro gieten om het nog makkelijker te maken natuurlijk.

Ja, de lijst moet on-the-fly moeten kunnen worden geupdatet, Duplicaten verwijderen werkt sowieso niet, want de andere kolommen in de tabel zijn wél uniek per regel en moeten behouden blijven. Anders was dit inderdaad het makkelijkst.

Lustucru schreef op zaterdag 17 november 2018 @ 15:49:
[...]


2,8 miljard vergelijkingen duurt inderdaad wel even. Het voordeel van een fatsoenlijke VBA routine is dat je misschien het aantal vergelijkingen terug kunt dringen door je input slim te sorteren en door te lopen, én je kunt zelf bepalen wanneer je hem aftrapt.

Zoals gezegd, VBA/macro's gaat lastig worden - vandaar ook mijn specifieke verzoek om het rechtstreeks binnen Excel op te lossen. Misschien kan ik thuis op mijn eigen pc wel eens hiermee gaan stoeien, om te kijken of het uberhaupt zou werken.

Patrick_6369 schreef op dinsdag 20 november 2018 @ 09:26:
Kun je met iets als dit uit de voeten?

https://exceljet.net/form...numeric-values-in-a-range
https://exceljet.net/form...ue-text-values-in-a-range

Het gebruikt geen VLOOKUP en is daarom wellicht minder zwaar? Ik gebruik zelf ook een freqyency-formule en die werkt super (alleen dan op een bestand met 70 rijen, dus dat is niet echt te vergelijken met jouw bestand).

Het gaat niet alleen om het aantal unieke waarden, ze moeten ook nog gekoppeld kunnen worden (via een draaitabel doe ik dat nu) aan andere attributen uit dezelfde tabel, zoals bijvoorbeeld leverancier. Ik kan dat natuurlijk ook opnemen in de unieke ID, maar dan wordt het qua presentatie lastig om het weer terug te vertalen naar een top-10 van leveranciers o.b.v. de hoeveelheid clienten...

Ook hier: ik zal eens gaan stoeien met deze formules. Misschien dat het met een kleine aanpassing wel een prima oplossing kan zijn.

Dank alvast allemaal!

[Lustucru]

Vergelijken() -index heb je niet eens nodig- of aantallen.als() zullen iets sneller zijn, maar je blijft ertegen aan lopen dat Excel ruim 2,8 miljard (n*n/2) vergelijkingen moet doen. Ik verwacht niet dat Excel bij een dergelijke operatie op een dataset van 75.000 regels ook maar een beetje bevredigend performt.

[Icephase]

Lustucru schreef op dinsdag 20 november 2018 @ 12:10:
Vergelijken() -index heb je niet eens nodig- of aantallen.als() zullen iets sneller zijn, maar je blijft ertegen aan lopen dat Excel ruim 2,8 miljard (n*n/2) vergelijkingen moet doen. Ik verwacht niet dat Excel bij een dergelijke operatie op een dataset van 75.000 regels ook maar een beetje bevredigend performt.

Ik ben hier ook bang voor.

Overigens geven sommige oplossingen wel netjes het aantal keer dat een waarde voorkomt, maar dat zoek ik niet precies (uiteindelijk wel, maar ik wil een tussenstap zodat de data mooi te presenteren is). Ik zoek echt naar een formule die checkt of de "ID" in de betreffende regel al eerder voorkwam (=0) of nog niet eerder voorkwam (=1). De 1-tjes kan ik dan simpelweg op laten tellen.

[Reptile209]

Icephase schreef op dinsdag 20 november 2018 @ 11:55:
[...]
Ja, de lijst moet on-the-fly moeten kunnen worden geupdatet, Duplicaten verwijderen werkt sowieso niet, want de andere kolommen in de tabel zijn wél uniek per regel en moeten behouden blijven. Anders was dit inderdaad het makkelijkst.
[...]

Daarom was ook mijn voorstel om op een kopie van de data te werken: daar kan je zonder problemen de duplicaten weggooien. Maar dan zit je dus al wel snel op een macro:
* Selecteer kolom met combi-codes
* Kopieer codes naar nieuw tabblad
* 'Remove duplicates'
* Tel aantal resultaten
* Verwijder tabblad

Overigens zou ik je sowieso aanraden om het niet helemaal 'on the fly' te laten doen, om te voorkomen dat je werkblad onwerkbaar wordt. Dus een macro-knopje met "update aantal codes" is wel aan te bevelen.

Nog beter wordt het om dit in een database te gieten .

[ Voor 16% gewijzigd door Reptile209 op 20-11-2018 15:37 ]

[Icephase]

Reptile209 schreef op dinsdag 20 november 2018 @ 15:36:
[...]

Daarom was ook mijn voorstel om op een kopie van de data te werken: daar kan je zonder problemen de duplicaten weggooien. Maar dan zit je dus al wel snel op een macro:
* Selecteer kolom met combi-codes
* Kopieer codes naar nieuw tabblad
* 'Remove duplicates'
* Tel aantal resultaten
* Verwijder tabblad

Ik zie nog steeds niet hoe dit aan mijn vraag tegemoet komt. Ik heb een "aantal resultaten" per willekeurig ander attribuut (aanbieder, jaar, whatever) nodig. Deze selectie moet dan 'on the fly' gemaakt kunnen worden.

Ondertussen ben ik bezig met de linkjes van @Patrick_6369. De ID's zijn tekst (want productcode kan letters bevatten) dus hij wordt wel ingewikkeld dan. Via INDEX en VERGELIJKEN heb ik al iets werkend, maar dit doet hetzelfde als VERT.ZOEKEN en duurt ook helaas net zo lang... Dus @Lustucru kon (jammer genoeg ) wel eens gelijk hebben...

[Reptile209]

Icephase schreef op dinsdag 20 november 2018 @ 15:40:
[...]


Ik zie nog steeds niet hoe dit aan mijn vraag tegemoet komt. Ik heb een "aantal resultaten" per willekeurig ander attribuut (aanbieder, jaar, whatever) nodig. Deze selectie moet dan 'on the fly' gemaakt kunnen worden.
[...]

Ah, zo. Dan had ik je niet goed begrepen.
Dan hou ik het bij: het kan vast in Excel, maar dat moet je niet willen. Gebruik voor data van deze omvang een database.

[Icephase]

Reptile209 schreef op dinsdag 20 november 2018 @ 15:44:
[...]

Ah, zo. Dan had ik je niet goed begrepen.
Dan hou ik het bij: het kan vast in Excel, maar dat moet je niet willen. Gebruik voor data van deze omvang een database.

Tja... Het komt juist uit een database, maar aangezien ik geen toegang tot die database heb zal ik het anders moeten oplossen. We hebben een rapportagetool, maar ik ben veel handiger in Excel. Plus Excel is voor mij easy maintenance, terwijl die rapportagetool veel ingewikkelder is. Daar wil ik op dit moment niet teveel tijd in steken; éérst zorgen dat er uberhaupt uit te halen is wat we willen weten. Daarna werken aan (proces)optimalisaties. Maar de traagheid was in dit geval wel een érg storende factor, vandaar de vraag.

/edit:
OK, ik heb even lopen stoeien met INTERVAL (Engels: FREQUENCY) als matrix-functie. Dit geeft in 1x het gewenste resultaat en daardoor hoef ik formules als VERT.ZOEKEN, VERGELIJKEN en INDEX niet te gebruiken. Volgens de help-bestanden van Excel zou INTERVAL ook sneller moeten werken dan bijvoorbeeld INDEX, VERGELIJKEN en AANTAL.ALS. Echter, ook deze (matrix)functie duurt gigantisch lang om te draaien, plus hij werkt (tot nu toe) alleen op waardes en niet op tekst.

De snelste (minst langzame) optie lijkt nu te zijn om met de VERGELIJKEN functie te kijken naar de eerste waarde van die bepaalde ID, dit te vergelijken met de RIJ van de ID en als ze niet overeen komen, is het géén unieke ID (dus: 0). Als ze hetzelfde zijn, is er blijkbaar geen eerdere waarde gevonden van dat ID en is hij dus uniek (1).
Dit is iets sneller dan mijn oplossing via VERT.ZOEKEN.

Als er nog tips zijn: ik houd me weer aanbevolen!

[ Voor 35% gewijzigd door Icephase op 20-11-2018 17:19 ]

[Lustucru]

Om een idee te geven van de snelheid die je kunt halen met VBA:

Onderstaand stukje code is klaar in een fractie van een seconde op een dataset van 75000 rijen waarin 10000 unieke waarden voorkomen. Dan staat er in kolom B achter ieder eerste voorkomen van een waarde een 1, alle duplicaten hebben een 0.

Visual Basic:

Option base 1
option explicit

Sub CheckIfExist()

    Dim dic As New Dictionary 'verwijzing nodig naar microsoft scripting runtime, anders late binding.
    Dim source As Variant
    Dim target(75000, 1) As Integer
    Dim t As Single, i As Long
    
    
    t = Timer 'onthoud het tijdstip waarop je begon
    
    source = Range("A1:A75000").Value2 'kopieer de waarden naar een array
    For i = 1 To 75000
        If dic.Exists(source(i, 1)) Then
           'de waarde is al eerder gevonden, dus een 0
            target(i, 1) = 0
        Else
            'de eerste keer dat een waarde gevonden wordt schrijf de waarde weg naar de dictionary en schrijf een 1
            dic.Add source(i, 1), 0
            target(i, 1) = 1
        End If
    Next i
    
    Range("B1:B75000") = target 'schrijf de telling terug naar het excelblad
    
    MsgBox Timer - t 'en laat zien hoe lang je erover deed.
    
End Sub

Dus toch maar eens IT aankijken dat soms VBA best wel nuttig kan zijn.

[The_Vice]

Icephase schreef op dinsdag 20 november 2018 @ 15:49:
[...]
Als er nog tips zijn: ik houd me weer aanbevolen!

Ik zal even wat dieper ingaan op de pivot table variant zoals ik die bedoel. Weet niet waar ik een kant en klaar Excel voorbeeld kan posten, dus even in text.
stel tabel:

XML:

letter  Cijfer  X       terughaal
AA      11      1       -
AA      11      1       -
AA      22      1       -
BB      11      1       -
AA      33      1       -
BB      44      1       -
BB      44      1       -
BB      44      1       -
BB      44      1       -

Maak daar een pivot table van met:
Rows:

• letter
• letter

Values

• Count of "X"

In de eerste cell van kolom "terughaal" maak je een verwijzing naar je pivot table count van AA, 11. Zoiets:

code:

=GETPIVOTDATA("X";Sheet2!$A$3;"letter";"AA";"Cijfer";11)

verander dit naar:
=1/GETPIVOTDATA("X";Sheet2!$A$3;"letter";A2;"Cijfer";B2)
om de invoer voor kolom letter en cijfer flexibel te maken.

En wat snelheid betreft, Excel doet een "refresh all & calculate" update van 75000 rijen als hierboven in minder dan een seconde.
In feite is het een wat houterige database query, maar evengoed werkbaar daar je nu op een combinatie van cellen een waarde kan zoeken. Met Vlookup kan dit niet, die kijkt maar in één cel.

[ Voor 4% gewijzigd door The_Vice op 20-11-2018 20:56 ]

[I-King]

Ik weet niet of je ervaring hebt met PowerQuery, maar als je toch naar oplossingen moet gaan zoeken bij IT voor externe oplossingen is dat ook een geweldige tool binnen Excel. Werkt een heel stuk sneller met dergelijke zaken, import van externe bronnen. Maar als je die optie niet hebt, dan heb ik helaas geen suggesties.

[Icephase]

Ik heb het nu voorlopig opgelost met de formule VERGELIJKEN in combinatie met een macro. VBA was uitgeschakeld, maar is door ICT geactiveerd op verzoek, dus hij gaat nu niet meer telkens berekenen. De macro start met een knop en leidt tot een kolom met vaste waarden.

Dit is voor nu de beste oplossing. PowerQuery, PowerPivot etc. ga ik misschien nog wel eens naar kijken maar niet nu. Toch dank aan iedereen voor het meedenken!

[hihans]

Anno 2020 met office 365

=AANTALARG(UNIEK( celbereik ))

[g0tanks]

Handig! Die functie kende ik nog niet. Het is ook beschikbaar in Excel 2019 en in het Engels is het:

code:

COUNTA(UNIQUE(range))

Verder zijn de oplossingen voor vorige versies van Excel al aangedragen, dus om vervuiling te voorkomen doe ik dit topic dicht.