Excel formule voor klantenverlies

[Willempy]

Beste excel experts en/of puzzelaars!

Helaas ben ik genoodzaakt om externe hulp in te schakelen wat betreft een accountmanagersprobleem. Vandaar mijn bericht.

Ik heb een hele excellijst met klantnummers in de ene en de jaartallen 2014 t/m 2017 in de andere kolom. Als een een klant actief is geweest (dus een product heeft afgenomen) in 2014 en 2015 staat zijn klantnummer er 2 keer in. Als een klant actief is geweest in 2014, 2015 en 2016 staat zijn klantnummer er 3 keer in enzovoorts. Zie ook afbeelding.

Nu aan mij de vraag hoeveel (per jaar) er klantverlies is geleden. Als een klant dus een jaar niet actief is geweest, telt dit jaar als klantverlies. Een klant is dus in 2014 en 2015 actief en in 2016 inactief. Dan geldt dat dus als een klantverlies voor het jaar 2016. Hier breek ik al een tijdje mijn hoofd over want dit moet (blijkbaar) met een formule kunnen of een combinatie van meerdere formules.



Zo ziet de lijst er ongeveer uit, maar dan heb ik er ongeveer 7000. U ziet ook dat als een bedrijf in 2016 en 2017 bijvoorbeeld niks heeft afgenomen (inactief), er geen nieuwe rij met klantnummer bijstaat waarin staat de klant 0 heeft afgenomen. Anders had ik wellicht alle true/false kunnen optellen.

Ik heb gelezen dat het een trend is om hier je huiswerk op te knallen en dat dit niet de bedoeling is.. maar ik zit echt met me handen in me haar.
Heeft er iemand een idee hoe dit het beste aan te pakken? Alle hulp is welkom en wordt zeer gewaardeerd!

 

[Peter B]

Ik zou mijn handen uit mijn haar halen en een geschikt (Excel) voorbeeldje in elkaar draaien. Dan heb je op zeker sneller een passend antwoord!

 

[Torben]

Wil je:
Een lijst met alle jaren die voorkomen in de tabel en daarachter een getal dat aangeeft hoeveel van de verschillende klanten die voor komen in de lijst, dat jaar niét iets hebben gekocht?

Dit roept de volgende vragen op:
1: Als een bedrijf pas sinds kort klant is bij jullie, dan wil je hem in de jaren dat hij nog geen klant was tellen als klantverlies?
2: Als een klant tussendoor een jaartje overslaat omdat hij een enorme hoeveelheid heeft ingekocht van jullie product, wil je hem dan ook als klantverlies laten tellen?
3: Stel dat een klant zeer weinig nodig heeft maar altijd alles wat hij nodig heeft eens in de 3 jaar bij jullie koopt, dan kan je toch moeilijk allemaal jaren klantverlies noteren?
4: Moet er nog iets gedaan worden met de productafnames? Of zette je het alleen maar in de voorbeeld om te laten zien dat je de nullen niet weergeeft?