Advies over inrichting van draaitabel / Excel formulier

[Deesoll]

Goedemiddag.

Momenteel ben ik bezig met een beschikbaarheidsformulier. Het ingevulde beschikbaarheidsformulier wordt (handmatig) verwerkt in een ander programma, helaas is het niet mogelijk om met dit programma analyses te maken. Daarom zou ik met het beschikbaarheidsformulier inzichtelijk willen maken hoeveel personen beschikbaar zijn op de diverse dagen. Het zou toch mogelijk moeten zijn om via draaitabellen/grafieken een grafiek te genereren, waarbij op de as de diverse dagdelen staan en in de grafiek zelf 3 lijnen met daarin het aantal personen die beschikbaar (1) of niet beschikbaar (3) zijn, danwel andere taken (2) hebben? Ik heb een bestandje toegevoegd aan de bijlage.

 

[VenA]

Met jouw opzet kan het niet. je hebt al een getransponeerde tabel. Als je hier een draaitabel van wil maken dan is het onmogelijk om er een leesbare grafiek van te maken.
Met een macro kan je hier vrij eenvoudig weer een tabel van maken en dan stelt het verder niets voor om er een grafiek van te maken.

Met de macro in een module

Sub VenA()
  ar = Sheets("ECO-ENO").Cells(7, 1).CurrentRegion
  ReDim ar1((UBound(ar) * 10) - 10, 2)
    For j = 2 To UBound(ar)
      For jj = 8 To 17
        ar1(t, 0) = ar(j, 5)
        ar1(t, 1) = ar(1, jj)
        ar1(t, 2) = ar(j, jj)
        t = t + 1
      Next jj
    Next j
    With Sheets("Database").ListObjects(1)
      .DataBodyRange.Delete
      .Range.Offset(1).Resize(UBound(ar1), 3) = ar1
    End With
End Sub

En in de bladmodule van het blad 'Chart1'

Private Sub Worksheet_Activate()
  VenA
  ThisWorkbook.RefreshAll
End Sub

 

[Deesoll]

Wow, dat ziet er goed uit, bedankt!

Ik moet eerlijk bekennen dat ik nog relatief onbekend ben met macro's, dus ook geen idee heb hoe zo'n code tot stand komt.
Mocht ik nu kolommen/rijen toevoegen of de aanwezige data wijzigen, moet ik handmatig de macro activeren en de tabelgegevens vernieuwen. Voor de macro is het mij gelukt om een "ververs"knop te maken, maar kan dit ook geautomatiseerd?

Toch maar eens een cursusjes VBA's volgen. Is dit iets wat zelf aan te leren valt middels instructies op het internet of is een cursus aan te raden? Qua programmeertaal ben ik een leek, maar wordt altijd wel enthousiast van dit soort dingen.

 

[VenA]

Rijen toevoegen of data wijzigen is geen probleem. Op het moment dat je de tab 'Chart1' activeert wordt de code uitgevoerd en de draaitabel ververst. Als je met kolommen gaat rommelen wordt het een ander verhaal.

Of het zelf te leren is kan ik niet beoordelen. Mij is het wel gelukt. Wat pittig voor een beginner maar hier kan je veel leesvoer over het gebruik van array's vinden. (en nog veel meer) http://www.snb-vba.eu/VBA_Arrays_en.html

 

[Deesoll]

Hoi VenA,

Inmiddels alweer 2 weekjes verder en ondertussen heb ik het bestand ook in gebruik, gaaf! Uiteraard moet er dan toch altijd nog weer wat gewijzigd gaan worden, onder andere het toevoegen en verwijderen van kolommen. Aangezien ik niet gelijk hier de hulp weer wou inroepen, ben ik zelf aan het puzzelen gegaan met de code welke jij gemaakt hebt. Het is inmiddels gelukt om kolommen toe te voegen en deze ook te laten verwerken. Toch snap ik de code nog niet voor de volle 100%.

Met name de letters "t" en "j" en "jj". Wellicht dat jij mij kan helpen de code te ontleden.

Sub VenA()
ar = Sheets("ECO-ENO").Cells(7, 1).CurrentRegion Hiermee definieer je een Array op de sheet ECO-ENO, vanaf cel A7.
ReDim ar1((UBound(ar) * 10) - 10, 2) Hiermee definieer je een nieuwe array en daar moet wat mee gebeuren? ReDim en Ubound snap ik nog niet helemaal wel ben ik erachter gekomen dat de 10 staat voor het aantal cellen wat meegenomen wordt, maar waar staat die 2 voor?
For j = 2 To UBound(ar)
For jj = 8 To 17 Welke cellen/kolommen geteld moeten worden, in dit geval dus 7.8 tot 7.17? Die reeks heb jij de naam JJ meegegeven?
ar1(t, 0) = ar(j, 5) Waar staat de T en J voor in dit geval? De 0 definieert de eerste kolom met waarden? De 5 voor de 5e cel vanaf de currentregion?
ar1(t, 1) = ar(1, jj)
ar1(t, 2) = ar(j, jj)
t = t + 1
Next jj
Next j
With Sheets("Database").ListObjects(1)
.DataBodyRange.Delete
.Range.Offset(1).Resize(UBound(ar1), 3) = ar1
End With
End Sub

 

[VenA]

Toch nog maar eens het linkje goed doornemen. Beter dan dat het daar staat kan ik het niet verwoorden.
Om te kijken wat er allemaal met de variabelen ar, ar1, j, jj en t gebeurt kan je de VB editor openen en zorg je ervoor dat het 'locals window' zichtbaar is. Met <F8> kan je dan door de code wandelen om te zien welke waarde welke variabele krijgt.