[EXCEL] Doorstroom trouwe klanten analyseren

[Verwijderd]

Ik heb een Excel-bestand met 6 kolommen: 2013 - 2014 - 2015 - 2016 - 2017 - 2018
In elke kolom staat een lijst met e-mailadressen van klanten die dat jaar 1 bepaald product hebben gekocht.
Sommige klanten doen dit ieder jaar, sommige hebben dit maar 1x gedaan en anderen hebben 1 x 2013 het product gekocht en daarna in 2018 weer.

(om verwarring te voorkomen: 2018 is voorinschrijving voor komend jaar)

Graag zou ik het verloop willen zien:
- welke mensen hebben ieder jaar het product aangeschaft?
- welke mensen hebben dit maar 1 keer gedaan?
- wanneer zijn mensen afgehaakt?
- welke mensen hebben een aantal jaren overgeslagen tussendoor?


Ik gebruik Windows 10 en office 365 Excel.


Ik heb al een tijd zitten proberen met de formule Vergelijken, maar dat lijkt gericht op 2 kolommen, ipv 6:
http://www.elroco.nl/snel-maillijsten-vergelijken-met-excel/

Verder nog wel andere dingen gevonden op internet die in de buurt komen, maar nog niet het juiste resultaat bereiken.

Is er iemand die mij de goede richting kan ophelpen?

[The Eagle]

Met SQL haal je dit soort dingen zo naar boven - wellicht wil je eea in Access gieten, mocht je niet met databases bekend zijn.

WIl je het toch in Excel doen: combinatie counts en vlookups moet je al een heel eind komen.

[EnerQi]

Draaitabel maken? (Pivot table in engels)

[breew]

Wat @EnerQi zegt: een draaitabel is 'the-way-to-go'. Je moet dat wel eerst de data van je huidige 'wide' formaat omzetten naar een 'long' formaat. Dat gaat supersimpel/-snel met de powerquery-addon van Excel. Hier zijn recentelijke meerdere topics over geweest in dit forum.

Succes! Laat maar weten als je ergens op vastloopt.

edit:
Zoals (bijna) altijd in excel, kun je je vraag op meerdere manieren beantwoorden. Mijn favoriet is de draaitabel, omdat die zo ontzettend flexibel is, en je er ook direct dynamische grafieken mee kunt presenteren.
Maar, uiteraard, kun je ook met formules aan de slag, of met SQL-queries, of met vba..
Pick your poison

[ Voor 34% gewijzigd door breew op 05-12-2017 12:55 ]

[Verwijderd]

Thanks voor de tips en hulp!

Ik heb nu de formule gebruik:
[=AANTAL.ALS(H:H;J2)+AANTAL.ALS(F:F;J2)+AANTAL.ALS(D:D;J2)+AANTAL.ALS(B:B;J2)+AANTAL.ALS(A:A;J2)]

Hierdoor kan ik in de kolom bijvoorbeeld '5' selecteren en heb ik direct een lijst met de mensen die ieder jaar het hebben aangeschaft. Door verschillende selecties te maken en dubbele weer te geven, kan ik snel filteren op verschillende groepen.

De draaitabel heb ik nog nooit mee gewerkt. Zojuist een uur mee zitten klooien, maar krijg daar niet echt iets nuttigs uit.
Als iemand daar een simpele uitleg voor heeft, die gebruik maakt van korte teksten (in mijn geval e-mailadressen) ipv cijfers in de tabel, dan houd ik mij aanbevolen.

[breew]

mhhh. bij nader inzien is een draaitabel wellicht overbodig, aangezien je al een wide-overzicht hebt...

Als je je aankopen even als 0 (=nee) of 1 (=ja) weergeeft, dan kom je met een paar (simpele?) formules een heel eind:


Kolom G

=ALS(SOM(B2:F2)=5;"ja";"nee")

Kolom H

=ALS(ISGETAL(VIND.ALLES("01";$B2&$C2&$D2&$E2&$F2;VIND.ALLES("10";$B2&$C2&$D2&$E2&$F2)));"ja";"nee")

Kolom I

=ALS(SOM(B2:F2)=1;"ja";"nee")

[Verwijderd]

Ja, dat is eigenlijk wat ik zoek.
Zo'n overzicht wat jij hebt toegevoegd.
Hoe kan ik dat destilleren uit kolommen met e-mailadressen, waarbij iedere kolom de e-mailadressen zijn van die jaargang?
Het zijn er duizenden e-mailadressen per jaar, dus die kan ik niet met de hand gaan tellen en per jaar een 1tje er achter zetten.

Als ik zo'n overzicht heb die jij hebt opgegeven, dan kan ik inderdaad nog veel makkelijker informatie er uit halen.

[breew]

Verwijderd schreef op woensdag 6 december 2017 @ 09:21:
Ja, dat is eigenlijk wat ik zoek.
Zo'n overzicht wat jij hebt toegevoegd.
Hoe kan ik dat destilleren uit kolommen met e-mailadressen, waarbij iedere kolom de e-mailadressen zijn van die jaargang?
Het zijn er duizenden e-mailadressen per jaar, dus die kan ik niet met de hand gaan tellen en per jaar een 1tje er achter zetten.

Het begint met een lijst maken van emailadressen... Je kunt dit doen met het geavanceerde filter van excel, maar die zuigt als je dara in meerdre kolommen staat.
Ik vind een macro altijd stukken handiger hiervoor...


NB: nooit zomaar code gebruiken die je niet begrijpt!! werk altijd in een kopie van je data!!
Ik heb (voldoende?) commentaar bij de code gezet, zodat je na kunt gaan wel iedere stap doet.

Uit de tuukendoos:

Visual Basic:

Option Explicit

Sub uniekeEmail()

'declararie
Dim c As Range            'cel in een geselecteerd gebied
Dim i As Long             'lusteller
Dim blnUniek As Boolean   'boolean, die aangeeft of een waarde uniek is, of niet
Dim arrEmail() As String  'array met daarin de unieke waarden van c

'initialisatie van de array met emailadressen
ReDim arrEmail(0 To 0)

'loop door alle geselecteerde cellen
For Each c In Selection
  'als een cel niet leeg is, dan
  If Not IsEmpty(c.Value) Then
    'zet de boolean op TRUE
    blnUniek = True
    'kijk of de waarde al in de array met emails voor komt
    'loop door de hele array
    For i = LBound(arrEmail) To UBound(arrEmail)
      'is de waarde uit de array gelijk aan de celwaarde?
      If arrEmail(i) = c.Value Then
        'zet de boolean op FALSE
        blnUniek = False
      End If
    Next i
    'is het inderdaad een unieke (nieuwe) waarde?
    If blnUniek = True Then
      'als de array nog leeg was (helemaal aan het begin)
      'dan de waarde invullen als de eerste waarde in de array
      If arrEmail(UBound(arrEmail)) = "" Then
        arrEmail(UBound(arrEmail)) = c.Value
      'als er al waarden in de array stonden, dan
      Else
        'herdimensioneer de array (met behoud van waarden),
        'zodat de nieuwe waarde kan worden toegevoegd
        ReDim Preserve arrEmail(0 To UBound(arrEmail) + 1)
        'voeg de nieuwe waarde toe
        arrEmail(UBound(arrEmail)) = c.Value
      End If
    End If
  End If
Next c

'print de array met emails in een bereik vanaf F2 naar beneden
ActiveSheet.Range("F2").Resize(UBound(arrEmail) + 1) = Application.Transpose(arrEmail)

'Klaar!! KOFFIE!!!

End Sub

Levert het volgende:


Daarna kun je met formules (of vba) gaan zoeken of een mailadres onder een bepaald jaar voorkomt.. dat gaat je vast lukken!

[ Voor 12% gewijzigd door breew op 07-12-2017 10:55 ]