deel van een referentienummer gebruiken als selectiewaarde

[sherpa14]

Het betreft een oplijsting van gegevens waarvan een referentienummer in een kolom staat. Het referentienummer is opgebouwd uit een datum gevolgd door een tweelettercode. De lettercode duidt aan over welk soort (hier in dit geval) vorming het gaat. het ziet er uit als: 2020-10-15-BC , 2019-05-02-IC, ...
Graag wil ik een aantal berekeningen maken op basis van het jaartal (eerste 4cijfers van het referentienummer) en de laatste 2 letters van het referentienummer.
vb - het aantal vormingen met code BC tijdens het jaar 2018.
- het totaal aantal vormingen met code BC over de jaren 2018 tot en met 2020
- het aantal vormingen met code BC + code IC voor 2019, voor de periode 2018 tot ... enz....

 

[emields]

Waarschijnlijk vergeten een voorbeeldbestand bij te voegen?

 

[sherpa14]

Ben volop dit werkblad aan het opmaken. Het is dus nog onvolledig. een aantal getallen zijn nu lukraak ingevuld, maar ik hoop dat je zal begrijpen wat ik bedoel.

Bovenaan het werkblad, of voor mijn part in een ander werkblad wil ik dan graag de totalen van de verschillende selecties maken (jaartal + Vormingscode, x aantal jaren + code, ...)

Het cijfermateriaal (de gekleurde cellen) zijn de gegevens die gekopieerd zijn van andere bestanden zoals ze daar voorkomen. Wijzigen van opbouw van de oorspronkelijke bestanden is geen optie. de cijfergegevens van een nieuwe vorming wordt onderaan de lijst toegevoegd.

 

[Haije]

sherpa,

kijk eens of dit is wat je zoekt....

 

[sherpa14]

Dag Haije,

bij jouw formule loopt de periode steeds vanaf 2 jaar. 1 jaar kan niet geselecteerd worden. Of zie ik dat verkeerd?

Bedoeling is dat op basis van mijn gevraagde selectiecriteria de corresponderende rijen worden geselecteerd zodat daar dan (zie rijen 2t/m7) gemiddelde, modus, mediaan, min., max. kan worden berekend. Met jouw formule lukt dat alsnog niet.

 

[VenA]

Even de formule aanpassen.

=SUMPRODUCT((LEFT(zoekcriterium;4)>=""&I13)*(LEFT(zoekcriterium;4)[COLOR="#FF0000"]<=[/COLOR]""&J13)*(RIGHT(zoekcriterium;2)=I14))

Als je analyses van veel data wil maken dan kan je beter een echte tabel gebruiken zonder lege rijen. Je hebt dan veel makkelijkere mogelijkheden zoals filteren draaitabellen subtotalen etc.

 

[sherpa14]

@VenA

Begrijp het niet
waar moet ik dan die formule plaatsen, wat doet die formule nu eigenlijk ?
Misschien verklaren wat u met de formule doet, dan kan ik het misschien begrijpen om het te kunnen reproduceren in andere gevallen.

 

[VenA]

Het is een aanpassing van de formule in I16 in het bestand van @Haije in #4

 

[sherpa14]

@VenA
resultaat van deze formule is #NAAM?


Maar het lost eigenlijk mijn vraag niet op zoals ik heb beschreven in #5 onder "bedoeling"

 

[JEC.]

Vertaal alle functies naar Nederlandse functies

 

[VenA]

In #6 staat nog een tweede zin.

 

[sherpa14]

Vertaal alle functies naar Nederlandse functies

nu lukt het wel per jaar en voor meerder jaren. Dat zit alvast goed.

Nu nog mijn doel : Quote #5: Bedoeling is dat op basis van mijn gevraagde selectiecriteria de corresponderende rijen worden geselecteerd zodat daar dan (zie rijen 2t/m7) gemiddelde, modus, mediaan, min., max. kan worden berekend. Met jouw formule lukt dat alsnog niet.

 

[sherpa14]

In #6 staat nog een tweede zin.

Ik heb ondertussen alle lege rijen verwijderd. (was dit wat u bedoelde?)

 

[VenA]

Ben je in verschillende draadjes dezelfde soort vragen aan het stellen? https://www.helpmij.nl/forum/showthread.php/957249-modus-en-mediaan-met-uitsluiting-van-lege-cellen-en-getal-0

 

[sherpa14]

Ben je in verschillende draadjes dezelfde soort vragen aan het stellen? https://www.helpmij.nl/forum/showthread.php/957249-modus-en-mediaan-met-uitsluiting-van-lege-cellen-en-getal-0

Ik denk dat dit steeds wel duidelijk verschillende vragen zijn. En met verschillende vragen probeer ik uiteindelijk tot een oplossing te komen. Of mag dat niet?

 

[VenA]

Waarom reageer je daar dan niet meer?

 

[sherpa14]

Waarom reageer je daar dan niet meer?

Is al opgelost vanaf #5 van dat draadje en als dusdanig afgevinkt als 'opgelost' . U hoeft niet te reageren op een ander draadje in deze draad. Het spijt me dat ik niet uw oplossing heb voorgenomen.

 

[VenA]

Dan heb ik een nieuwe bril nodig.

Met een tabel. Als je de filterknoppen gebruikt in X11 en Y11 dan worden de waarden in de rijen 3 t/m 9 aangepast. Door de gebrekkige dataset heb ik verder niet gecontroleerd of het allemaal wel klopt.

 

[sherpa14]

Dan heb ik een nieuwe bril nodig.

Met een tabel. Als je de filterknoppen gebruikt in X11 en Y11 dan worden de waarden in de rijen 3 t/m 9 aangepast. Door de gebrekkige dataset heb ik verder niet gecontroleerd of het allemaal wel klopt.

Beste VenA,

Ik heb jouw laatste voorstel op mijn oorspronkelijk bestand losgelaten. In bijlage het resultaat. Toch een paar vragen hierover (kwestie om er zelf zoveel mogelijk van te kunnenleren, ondanks mijn opzoekwerk en mijn klein excel-verstand )

Resultaat tot nu toe:
Ik denk dat dit de weg is die moet ingeslagen worden. Alvast bedankt om die richting te duiden.
In mijn bestand wordt de filter ingesteld in de kolomtitels in kolom B en C
Ik vrees dat er nog een foutje moet inzitten: bij keuze kolom B: BC + kolom C: 2019 is het resultaat niet correct in de resultaattabel (cellen E4, F4, G4 + E5, F5 en G5) Daar is de uitkomst getal 4 terwijl in de gegevenstabel cijfer 4 van die selectie niet voorkomt.
Waar zit de fout?


Vragen ter verduidelijking van werkwijze
1. in de samenvattende tabel in de rijen 3 t/m 7 gebruik je de ene maal de functie AGGREGAAT en de andere maal SUBTOTAAL. Waarom dit verschil?
2. In deze functies worden functiegetallen en optiegetallen gebruikt. Waarom de ene maal functiegetal 5 en de andere maal 104 (dit laatste zou?? de verborgen rijen niet meetellen??) (-> is mij niet duidelijk)
3. Zijn alle formules zo opgemaakt dat het getal 0 + de lege cellen + de verborgen rijen (in functie van de filter-selectie) niet in rekening brengen?

Nogmaals mijn dank!

 

[VenA]

1. De functie SUBTOTAL() kent geen mediaan en modus met de functie AGGREGATE() zijn deze wel beschikbaar. In het bestand heb ik beide gebruikt zodat je kan zien wat er zoal aan mogelijkheden in Excel zijn.
2. Eenvoudig te vinden in de help. <F1> voor de help. Of even op internet zoeken. https://support.microsoft.com/en-us/office/aggregate-function-43b9278e-6aa7-4f17-92b6-e19993fa26df?ns=excel&version=90&syslcid=1033&uilcid=1033&appver=zxl900&helpid=xlmain11.chm60533&ui=en-us&rs=en-us&ad=us
3. De waarde 0 komt in het bestand niet voor. Lege cellen worden volgens mij standaard genegeerd.