• 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.

draaitabel met datumrange

Status
Niet open voor verdere reacties.

AKingma

Gebruiker
Lid geworden
3 nov 2015
Berichten
94
Ik vraag me al lange tijd af of ik mijn draaitabellen niet efficienter kan maken...
Ik wil tabellen voor bijvoorbeeld een heel schooljaar, oftewel 'datum toekenning' (kolomnaam) ligt tussen 01-08-2016 en 31-07-2017 (of een willekeurige andere periode). Ik doe dat tot nu toe zo:
invoegen draaitabel, met in filter 'datum toekenning'. Vervolgens selecteer ik 'meerdere items selecteren', in zoekveld 8-2016, ok. Opnieuw naar filter, 9-2016, 'huidige selectie aan filter toevoegen', etc. Waarbij dan bij januari en februari nog een streepje voor de -1 of -2 komt, om te voorkomen dat november en december ook mee gaan doen. Het werkt, maar is best omslachtig.
Kan dit op de een of andere manier sneller, dus dat ik een draaitabel maak voor een bepaalde periode, zonder dat ik daarvoor een extra kolom in het databestand moet aanmaken waarin ik de periode eerst definieer? Ik maak namelijk zeer regelmatig tabellen, de ene keer voor een schooljaar, dan een kwartaal, trimester, etc., dus iedere keer opnieuw definieren wel record wel en niet moet worden meegenomen werkt ook niet.
 
Zonder een representatief voorbeeld bestandje toe te voegen (eventueel ontdaan van gevoelige info) wordt het lastig voor de helpers om hier iets zinnigs over te zeggen.
Door met slicers en tijdlijnen te werken in een draaitabel kun je voor iedere periode overzichten creëren.
Maar laat eerst maar eens zien wat je al hebt!

Edit:
Kijk ook eens even naar het laatst gepostte bestandje van dit draadje: http://www.helpmij.nl/forum/showthread.php/918791-Trendrapport-brandstofkosten-per-periode-analyse-brandstofsoort?p=5960679#post5960679
 
Laatst bewerkt:
dat andere draadje ziet er prachtig uit, maar ik heb geen idee hoe ik dat zou kunnen programmeren...
Ik wil meestal meerdere tabellen voor meerdere periodes naast elkaar laten zien (dus bijvoorbeeld trimester 1, 2 en 3 van 2017 in één oogopslag), en vaak ook meer variabelen dan nu meegenomen in de draaitabel, (nu alleen 'soort', maar bijvoorbeeld 'stadsdeel' in rijen (en soms ook nog 'schoolnaam' onder 'stadsdeel' en dan 'soort' in kolommen), en vaak ook meer filters, bijvoorbeeld 'herindicaties'=0, of juist 1... het wordt dan dus erg ingewikkeld zonder kennis van VBA...
 
Als je Excel 2013 of hoger hebt is het gebruik van een tijdlijn de oplossing:
 

Bijlagen

  • voorbeeldhelpmijdraaitabel_Gijs.xlsx
    18,2 KB · Weergaven: 147
Die kende ik nog niet en moet mij zeker gaan helpen... Alleen, in het voorbeeld dat ik stuurde stonden 3 datumvelden, toekenning, ingang en einde. In het originele bestand is nog een vierde datumveld: geboortedatum.
Als ik nu een draaitabel maak en met behulp van de helpfunctie, via hulpmiddelen draaitabellen/analyseren, klik op 'tijdlijn invoegen' dan krijg ik alleen geboortedatum te zien als optie waarvoor ik een tijdlijn zou kunnen invoegen. Dat is nu niet de minst interessante van het geheel... Enig idee hoe ik 'datum toekenning' daar wel in kan krijgen?

aanvullende info: in het originele bestand staat geboortedatum in kolom D, datum toekenning in K, en ingang en einde respectievelijk in M en N. Ze hebben allen dezelfde celeigenschappen (aangepast dd-mm-jjjj).
 
Kennis van VBA is ook niet direct nodig hoor.
Ik heb wat kolommetjes toegevoegd en wat gerommeld met slicers:
 

Bijlagen

  • voorbeeldhelpmijdraaitabel_Gijs1.xlsx
    21,2 KB · Weergaven: 84
Als er een representatief voorbeeld bestandje gevraagd wordt doe je er verstandig aan om meteen ervoor te zorgen dat de tabel dezelfde kolommen heeft als het origineel.
Dit helpt je niet erg verder zo. :confused:

Om een tijdlijn of slicer in te voegen, kun je naar de draaitabelvelden gaan door:
1 rechtsklik in draaitabel --> Lijst met velden weergeven.
2 dan niet het vinkje aanzetten bij "Datum toekenning" maar hierop rechtsklikken.
Nu kun je een slicer of tijdlijn invoegen.
 
Sorry Gijsbert, ik dacht irrelevante info weg te laten, maar die was toch niet zo irrelevant. Ik heb het gevoel dat ik er bijna ben. Bijgevoegd mijn voorbeeldbestandje, nu wel met de ook relevante kolommen (nog steeds niet alle, dat zijn er wel heel veel). Daarin lukt het. Het gekke is dat als ik dezelfde werkwijze (rechtsklikken op datum toekenning) in het originele bestand volg, dan heb ik alleen de mogelijkheid om een slicer in te voegen, maar dus niet een tijdlijn. En die is nu juist zo relevant. Enig idee wat daar mis kan gaan?
Bekijk bijlage voorbeeldhelpmijdraaitabel2.xlsx
 
Dan is de kolom met de data van 'datum toekenning' in het originele bestand vermoedelijk opgemaakt als tekst of anderszins. In ieder geval niet als een datum.
 
Helaas, zowel geboortedatum als datum toekenning, begin en einde zijn allen opgemaakt als Aangepast, dd-mm-jjjj. Bij geboortedatum krijg ik de mogelijkheid een tijdlijn in te voegen, bij de andere drie datumkolommen niet. Ik tast compleet in het duister.
Zoals je waarschijnlijk zult begrijpen gaat het om privacy-gevoelige gegevens, dus ik kan niet een stukje van het originele bestand sturen. Ik kan natuurlijk wel een kopie leegwissen en vullen met fictieve gegevens, maar vrees dat dat ook niet helpt om de oorzaak te vinden. Enig idee? Nu ik zo dichtbij ben is het wel heel zuur als ik het moet opgeven, het zou echt heel veel werk besparen.
 
Zoals je waarschijnlijk zult begrijpen gaat het om privacy-gevoelige gegevens
Dat begrijp ik.
Ik kan natuurlijk wel een kopie leegwissen en vullen met fictieve gegevens, maar vrees dat dat ook niet helpt om de oorzaak te vinden.
Dat begrijp ik niet. Als je het namelijk niet probeert weet je zeker dat je niet tot een oplossing zult komen.
Er zitten hier heel wat helpers die heel wat lastigere vraagstukken hebben opgelost als deze! Maar zonder representatief voorbeeld (dat is inderdaad een kopie waarbij gevoelige informatie is vervangen door fictieve gegevens) kom je niet verder.
 
Ik heb het geprobeerd, maar na het anonimiseren en inkorten van het bestand, kan ik wel een tijdlijn maken. Ik neem aan dat er dus ergens één of enkele records in het bestand zitten die de tijdlijnfunctie verhinderen. Enig idee hoe ik kan gaan zoeken in het origineel naar wat die tijdlijnfunctie tegenhoudt? ik voeg de anonieme ingekorte kopie nu wel bij, maar vrees dat dat dus niet helpt.Bekijk bijlage testtijdlijn_anoniemersakopie.xlsx
 
Ha Kingma

Ik neem aan dat er dus ergens één of enkele records in het bestand zitten die de tijdlijnfunctie verhinderen.
dat is precies wat ik denk dat fout gaat.
Stel dat er ergens een tikfoutje staat bijvoorbeeld datum: 08-08-x2010 (de x is een tikfout en de gehele waarde is dus nu een tekst en geen datum)
Je probleem is dat je mogelijk moet gaan zoeken in duizenden records.

Een eenvoudige manier om deze cellen op te sporen:
1 zet een filter op de kolommen.
2 Klik op filter pijltje en klik vervolgens op "Sorteren van nieuw naar oud" (als meer dan de helft geen datum is klik dan "sorteren van Z naar A")

De regels met cellen waarin een incorrecte waarde staat zal dan bovenaan staan, en die kun je dan corrigeren.
Overigens kun je in het zoekveld van de filterfunctie ook al snel zien wat een datum is en wat niet. Als Excel een waarde herkent als datum dan wordt deze gecategoriseerd per jaar.
Zie afbeelding.
plaatje tijdlijnprobleem.jpg
herhaal deze check/correctie per kolom en je zult zien dat je hierna wel tijdlijnen kunt invoegen voor je draaitabel.
 
Controleren of de datumvelden correct zijn middels filter is altijd mijn eerste stap als ik tabellen ga draaien. Dat hielp dus niet. Nu dacht ik dat de notatie wellicht nog ergens kon afwijken, dus de gehele kolom geselecteerd en alles op "aangepast, dd-mm-jjjj" gezet. Ook dat hielp helaas niet, ik kan nog steeds geen tijdlijn toevoegen. Frustrerend! Moet ik hier een nieuw lijntje voor beginnen in het forum?
 
Controleren of de datumvelden correct zijn middels filter is altijd mijn eerste stap als ik tabellen ga draaien
Hierover heb ik 2 opmerkingen:
1. je wekte de suggestie dat je dat juist niet had gedaan doordat je aangaf te denken dat er: 'één of enkele records in het bestand zitten die de tijdlijnfunctie verhinderen'.
2. En wat zijn al de vervolgstappen dan geweest die je hebt genomen? Graag alles vermelden wat je al geprobeerd hebt, anders wordt het een eindeloos draadje zo!

Een nieuw draadje starten lijkt me zinloos omdat het onderwerp van dit draadje nog steeds de lading dekt en anders missen helpers juist de achtergrondinformatie!
Hopelijk zijn er nog anderen met nieuwe invalshoeken?
 
Kan deze funktie überhaupt met lege cellen overweg ?

Ik ging naar find / special / constants / numbers

en zag in de datumkolommen een aantal lege cellen.


Terzijde: het BRIN-nummer is toch gekoppeld aan een organisatie/lokatie. Dan is het selekteren van de lokatie voldoende om het brin-nummer in de bijbehorende kolom te krijgen.
Een aparte keuzelijst maakt de kans op fouten alleen maar groter.

Suggestie:
- maak gebruik van intelligente tabellen met 1 kopregel die altijd in rij 1 van een werkblad staat.
De angst voor het effektief gebruik van rij 1 en vaak ook kolom 1 zou er in alle cursussen grondig uitgewerkt moeten worden.
 
Laatst bewerkt:
probeer eens om de betreffende datum kolommen met behulp van plakken speciaal goed te krijgen.

Zet in een vrije cel, bv XX1, een 1
selecteer xx1 en druk Ctrl+C
selecteer een datumkolom
klik met rechts in de selectie en kies voor plakken > plakken speciaal > vermenigvuldigen > enter
 
Dank voor de suggesties...
De suggestie van Haije geprobeerd, dat leidde in eerste instantie tot een kolom 'datum toekenning' met 5cijferige getallen (het aantal dagen sinds 1 januari 1900 als ik het wel heb). Daarmee lukte het niet. Vervolgens de 5 cijferige getallen veranderd middels celeigenschappen in datum (dus niet 'aangepast, dd-mm-jjjj), ook dat hielp niet.
SNB vraagt of de tijdlijnfunctie met lege cellen overweg kan? Die gedachte had ik vorige week ook al getest. Het antwoord is ja. Ik heb behalve datum toekenning ook geboortedatum, datum ingang, en datum einde in mijn bestand. Allen zijn op dezelfde manier opgemaakt: aangepast dd-mm-jjjj. Allen bevatten lege cellen, geboortedatum het minst, datum einde het meest. De tijdlijnfunctie werkt alleen bij geboortedatum en niet bij de andere datumvelden. Wel werkt 'datum als slicer' bij alle velden.
De terzijde van SNB: we vinden het handig om een aantal waarden die één op één gekoppeld zijn aan 'schoolnaam/locatie', altijd in beeld te krijgen. Dit zijn bijvoorbeeld BRIN en bestuur. Ik heb het zo geprogrammeerd dat deze automatisch worden ingevuld als de schoolnaam gekozen is. Ik snap dat de naam van mijn tabblad 'keuzelijsten' verwarrend is, maar hier staan dus alle waarden waaruit in de menu's gekozen kan worden. De administratie gebruikt alleen het tabblad 'registratie', dat dus gebruik maakt van de gegevens in 'keuzelijsten'. De suggestie begrijp ik niet helemaal.
Dan Gijsbert:
Ik heb gekeken naar lege cellen, die verklaren het niet.
celeigenschappen overal dd-mm-jjjj, helpt ook niet.

Het enige wat ik nu zelf nog kan bedenken is dit: het bestand is een samenvoeging van oude data, waar alles handmatig werd ingevoerd (eerste xx records), met kans op typefouten... en later ingevoerde data waarin zoveel mogelijk gewerkt is met getrapte keuzemenu's en automatisch invullen. Toen heb ik de oude data geschoond, en bij de nieuwe data voorwaardelijke opmaak ingevoerd (als datum toekenning al ingevoerd is en datum ingang na een bepaalde tijd nog niet, wordt de cel rood) etc. Het irritante is dat als ik op "regels beheren" klik, ik zie dat die regels soms op slechts 1 record slaan (en dan dus veelvuldig gedupliceerd zijn). Ook zitten er fouten tussen, bijv. =EN(OF(#VERW!="SBO";#VERW!="SO L";#VERW!="SO M";#VERW!="SO H");EN($N7610>VANDAAG();$N7610-90<VANDAAG())). Bij geboortedatum heb ik geen voorwaardelijke opmaak gebruikt.

vraag 1. zou dit het euvel kunnen zijn?
vraag 2. Is het mogelijk op een efficiente wijze de bezem door de regels te halen en hoe dan?
Het bestand is overigens eindig, binnen enkele maanden stappen we over op een nieuw systeem, maar de gegevens uit deze registratie zullen wel moeten worden ingelezen, bewaard.
 
Status
Niet open voor verdere reacties.
Steun Ons

Nieuwste berichten

Terug
Bovenaan Onderaan