• Privacywetgeving
    Het is bij Helpmij.nl niet toegestaan om persoonsgegevens in een voorbeeld te plaatsen. Alle voorbeelden die persoonsgegevens bevatten zullen zonder opgaaf van reden verwijderd worden. In de vraag zal specifiek vermeld moeten worden dat het om fictieve namen gaat.

aantal productcodes tellen in chaotische tabel

Status
Niet open voor verdere reacties.
H

Henk09

Gebruiker
Lid geworden
7 jan 2012
Berichten
23
Beste Help mij,

In bijgaand bestand staat een tabel met codes van verkochte producten in kolom C, in kolom B staan de data waarop ze verkocht zijn.
In kolom D wil ik berekenen hoeveel codes van producten er op een specifieke datum in de cellen van kolom C staan.
De tabel is nogal chaotisch opgemaakt met een wisselend aantal spaties en komma’s.
Ik heb onderstaande formule gebruikt om het aantal producten in cel C5 te tellen, dat werkt.

=LENGTE(SPATIES.WISSEN(C5))-LENGTE(SUBSTITUEREN(C5;" ";""))+1

Helaas blijkt nu natuurlijk dat de formule niet werkt voor cel C3 die leeg is, hier zou de formule als uitkomst leeg moeten blijven of een “0” moeten geven.

Ik heb het met deze formule geprobeerd

=ALS(C3="";"";LENGTE(SPATIES.WISSEN(C3))-LENGTE(SUBSTITUEREN(C3;" ";""))+1
Dat werkt niet.
Hoe kan ik dit probleem oplossen?

Alvast dank,

Henk
 

Bijlagen

Code: 
=(len(trim(c3))-len(substitute(c3;" ";""))+1)*(len(substitute(c3;" ";""))>0)
 
Als alles mogelijk is qua kommas en spaties, geeft deze volgens mij de gewenste resultaten:
Code: 
D3: =AFRONDEN.BENEDEN(LENGTE(SPATIES.WISSEN(SUBSTITUEREN(C3;",";" ")))/7;1)
 
Laatst bewerkt:
Kunt ook een als-formule ervoor toevoegen:
Code: 
=als(aantal.lege.cellen(C3)=1;0;uw formule)
 
Laatst bewerkt:
Waarom de ALS om alsnog een 0 te plaatsen als een cel leeg is? Formules in #2 en #3 doen dit al.
En "AANTAL.LEGE.CELLEN(C3)=1" is wat moeilijke manier om te zeggen C3<>""

Als TS geen 0 wil zien maar een "lege cel", kan de ALS-functie nog wel helpen:
Code: 
=ALS(C3="";"";AFRONDEN.BENEDEN(LENGTE(SPATIES.WISSEN(SUBSTITUEREN(C3;",";" ")))/7;1)
 
Laatst bewerkt:
Hartelijk dank voor de snelle reacties, mijn probleem is opgelost.
Toch gekozen voor de reactie van VenA omdat die voor mij het meest logisch leek. De andere oplossingen werken ongetwijfeld ook, daar ga ik nog naar kijken als wat meer tijd heb.
Helaas hebben niet alle codes 7 cijfers, zoals al gezegd de gegevens zijn wat chaotisch en soms onvolledig ingevoerd dus durfde niet op die oplossing te vertrouwen.

Erg blij met deze hulp,

Groet,

Henk
 
Wat je wilt, geen probleem. Jouw excelsheet.

Maar het voorbeeld was dus niet geheel representatief...

Nog 1 puntje wat me opviel. Als er ook komma's met een spatie ertussen voorkomen dan gaat de telling nog niet goed.
 
Hoi Alexcel,

je hebt gelijk, tabel was niet helemaal representatief. Heb er maar een klein stukje uit gepakt, de ingevoerde waarden waren erg rommelig en ik dacht dat dit voldoende was. En inderdaad je hebt gelijk, als er ook komma's met een spatie ertussen voorkomen dan gaat de telling nog niet goed. Zal degene die de waarden invoert daar op wijzen.
Dank voor je oplossing.

Groet,

Henk
 
Ik mag toch aannemen dat niemand dit handmatig op deze manier invoert?
 
Helaas VenA,

dat is wel het geval.
Sommige mensen gebruiken Excel alleen omdat er al een tabel staat en ze die dus niet meer hoeven aan te maken, maar maken verder absoluut geen gebruik van de mogelijkheden. Dan krijg je soms dit soort bestanden onder ogen en moet je er achteraf nog iets van zien te maken.
Dat lukt nu dus wel dankzij jullie oplossingen.
Dank daarvoor,

Henk
 
Omdat ik het wel een leuk probleem vond om nog even over na te denken, hierbij nog een optie voortbordurend op #2 die wel rekening houdt met alle combinaties van komma en spatie, en variabele code-lengtes:
Code: 
=(C3<>"")*(LENGTE(SPATIES.WISSEN(SUBSTITUEREN(C3;",";" ")))-LENGTE(SUBSTITUEREN(SPATIES.WISSEN(SUBSTITUEREN(C3;",";" "));" ";""))+1)
 
Hoi AlexCEL,

zou fijn zijn als het bestand hiermee helemaal foolproof wordt. Ga hem zeker uitproberen, dank voor de oplossing.

Henk
 
Als UDF:

Code: 
Function F_snb(c00)
   F_snb = UBound(Split(Application.Trim(c00))) + 1
End Function

In D3:
PHP: 
=F_snb(C3)

Of als Arrayformule:
PHP: 
=IF(C3="";0;SUM(N(MID(TRIM(C3);ROW(1:100);1)=" "))+1)
of
PHP: 
=(C3<>"")*(SUM(N(MID(TRIM(C3);ROW(1:100);1)=" "))+1
)
 
Laatst bewerkt:
Moet eerlijk toegeven dat de laatste formules mij enigszins ontgaan, ik weet niet wat UDF of PHP is, daarvoor is mijn kennis van Excel te basaal.Voor Array formules moet je afsluiten met Ctrl+Shift+Enter?? Helaas herkent mijn Nederlandse Excel ook de Engelse termen niet en heb geen vertaling kunnen vinden voor MID in de PHP codes. Krijg de formules dan ook niet aan het werken helaas.
Desalniettemin wel bedankt.
De laatste formule van AlexCEL is voor zover ik nu kan beoordelen inderdaad fool proof.

Groet en dank,

Henk
 
Een UDF is een User Defined Function. Dus een zelf gemaakte functie die je in formules kan gebruiken. Hier is enige kennis van van VBA voor nodig.

Door formules op te maken dmv codetages zoals Code en PHP zijn formules beter leesbaar, krijg je geen emoticons en vallen sommige tekens niet weg. MID() is versie afhankelijk DEEL() of MIDDEN().
 
Kom nogmaals terug op mijn vraag.
Heb intussen geprobeerd alle oplossingen op een rijtje te zetten in een tabel behalve de VBA oplossingen, die gaan me boven de pet. De array formules zijn intussen wel gelukt (zie bijlage).
Ben in de tabel die ik aangeleverd kreeg nog meer problemen tegen gekomen, er staan de gekste dingen in.
Het handigst zou zijn als de productcodes gescheiden werden door bijv. één komma en één spatie, dit gaat niet altijd goed, er ontbreken dus komma’s of spaties of er staan te veel komma’s en spaties.
De productcodes hebben normaal gesprokken 7 cijfers maar zijn als getal ingesteld en als de code dan met een 0 begint valt het eerste cijfer weg.
Het komt zelfs voor dat er alleen twee komma’s staan.
In de voorgestelde oplossingen gaat veel goed maar nog net niet alles, een cel met daarin alleen één of twee komma’s wordt ook als productcode geteld(zie bijlage) en komt daadwerkelijk in mijn bestand voor. Is daar ook nog een oplossing voor en/of zie ik nog meer problemen over het hoofd?

Groet,

Henk
 

Bijlagen

Code: 
=(len(trim(substitute(c11;",";" ")))-len(substitute(substitute(c11;",";" ");" ";""))+1)*(len(substitute(substitute(substitute(c11;",";"");" ,";" ");" ";""))>0)
 
Je kunt met zoeken/vervangen eenvoudig alle komma's vervangen door spaties.
Met de Trimfunktie in Excel worden vervolgens alle dubbele spaties verwijderd.
 
Code: 
=lengte(spaties.wissen(substitueren(c2;",";" ")))-lengte(substitueren(spaties.wissen(substitueren(c2;",";" "));" ";""))+als(spaties.wissen(substitueren(c2;",";" "))="";0;1)
of met ALS-formule vervangen door boolse waarden omzetten naar getallen.
Code: 
=LENGTE(SPATIES.WISSEN(SUBSTITUEREN(C2;",";" ")))-LENGTE(SUBSTITUEREN(SPATIES.WISSEN(SUBSTITUEREN(C2;",";" "));" ";""))[COLOR="#FF0000"]+(SPATIES.WISSEN(SUBSTITUEREN(C2;",";" "))<>"")[/COLOR]
 

Bijlagen

Laatst bewerkt:
Heb nu 3 werkende oplossingen van VenA en alphamax en de suggestie van snb met zoeken/vervangen de komma's te vervangen door spaties.
Denk dat ik toch maar kies voor één van de formules, als Excel het ook automatisch kan oplossen heb ik dat toch liever.
Nogmaals mijn hartelijke dank voor de aangedragen oplossingen,

Henk
 
Status
Niet open voor verdere reacties.
                    Steun ons                    

Nieuwste berichten

Terug
Bovenaan Onderaan