[EXCEL] Automatisch grafiek van laatste gevulde kolom maken?

[teigetjuh]

We houden hier op kantoor een grafiek bij met het gebruik van een bepaalde systeem. De input komt gewoon uit een tekst bestand, dat wordt gedurende de dag netjes door het systeem gevuld.
De volgende dag wordt dit opgehaald en in een excel-sheet gegooid. Voor zover geen problemen.
Op 1 van de werkbladen wordt uit deze data een grafiek van die ingevoerde dag gemaakt. De oude data blijft in de sheet staan. Elke dag moet dus de range van de grafiek aangepast worden. Dit wordt helaas nog wel eens vergeten... Is er een mogelijkheid om een grafiek te maken van de laatste gevulde kolom?

[Obliterator]

Vast wel.. Kijk eens naar VBA (Visual Basic for Applications).

[Anoniem: 17989]

Maak in vb een nieuwe functie aan:

code:

Function LASTINCOLUMN(rngInput As Range)
    Dim WorkRange As Range
    Dim i As Integer, CellCount As Integer
    Application.Volatile
    Set WorkRange = rngInput.Columns(1).EntireColumn
    Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange)
    CellCount = WorkRange.Count
    For i = CellCount To 1 Step -1
        If Not IsEmpty(WorkRange(i)) Then
            LASTINCOLUMN = WorkRange(i).Value
            Exit Function
        End If
    Next i
End Function

Vervolgens gebruik je deze functie in een nieuw werkblad zodanig, dat er een rijtje staat met de laatste kolom getallen, en deze plot je.

[Lustucru]

Ik vraag me af of déze functie een oplossing biedt voor het probleem? Als ik het zo snel zie doet hij niets meer dan waarde retourneren van de laatste cel uit de eerste kolom van een meegegeven bereik. Ik zie zo niet wat ts daarmee opschiet.

Anyway, je zult wél met VBA aan de slag moeten. Misschien handig als je aangeeft hoe je blad is opgebouwd en hoe de data in de cel worden gezet. Neem eens met de macrorecorder op hoe je de range van een grafiek aanpast en pas de gegenereerde VBA code aan zodat hij altijd de laatste kolom pakt.

[Anoniem: 17989]

Niesje schreef op woensdag 08 februari 2006 @ 09:49:
Ik vraag me af of déze functie een oplossing biedt voor het probleem? Als ik het zo snel zie doet hij niets meer dan waarde retourneren van de laatste cel uit de eerste kolom van een meegegeven bereik. Ik zie zo niet wat ts daarmee opschiet.

Anyway, je zult wél met VBA aan de slag moeten. Misschien handig als je aangeeft hoe je blad is opgebouwd en hoe de data in de cel worden gezet. Neem eens met de macrorecorder op hoe je de range van een grafiek aanpast en pas de gegenereerde VBA code aan zodat hij altijd de laatste kolom pakt.

Correct, maar als je even verder denkt, dan zie je dat je precies dit nodig hebt.

Je hebt een blad met gegevens. Daar komt elke dag een kolom bij. Je wilt automaisch een grafiek maken van de laatste dag. Dus, je wilt op een nieuw werkblad altijd de laatste gegevens uit een kolom zien. Van die kolom maak je een grafiek, en je hebt precies wat je wilt.

[teigetjuh]

Hmmm ik ben geen vba-er, dus ik had eigenlijk gehoopt dat het op een wat simpelere manier zou kunnen.... Moet er dus binnenkort eens in gaan duiken....

[Lustucru]

Als je sheet strak is opgebouwd heb je geen VBA nodig
Als er geen lege kolommen tussenstaan haal je altijd de laatste waarden op in een hulpkolom met iets als

code:

VERSCHUIVING(blad1!A1;0;AANTAL(1:1)-1;1;1)

en klikken en slepen... Zitten er wel gaten e/o tekstwaarden tussen of is het aantal rijen niet iedere dag hetzelfde dan moet je de formule uitbreiden.

Op die hulpkolom baseer je dan de grafiek.

Anoniem: 17989 schreef op woensdag 08 februari 2006 @ 10:16:
[...]
Je hebt een blad met gegevens. Daar komt elke dag een kolom bij. Je wilt automaisch een grafiek maken van de laatste dag. Dus, je wilt op een nieuw werkblad altijd de laatste gegevens uit een kolom zien. Van die kolom maak je een grafiek, en je hebt precies wat je wilt.

het idee is duidelijk, maar je haalt rijen en kolommen door elkaar in je oplossing.

[CoRrRan]

Geavanceerde oplossing zonder VBA die wel enig uitzoekwerk vereist: dynamische ranges. Menu "Insert" --> "Names" --> "Define..." en voeg een nieuwe naam toe en vul de volgende formule in als referentie:

code:

=OFFSET(Sheet1!$A$1,,COUNTA(Sheet1!$1:$1)-1,COUNTA(INDIRECT("Sheet1!C"&COUNTA(Sheet1!$1:$1),FALSE)),1)

Uitleg specifiek bij dit voorbeeld: In Sheet1 staan vanaf rij 1 en kolom A de waarden. Geen headers voor de kolommen. In kolom A staan de x-waardes van je grafiek. Dynamisch wordt dan de laatste kolom gebruikt voor je y-waarden. Dit doe je eenvoudig door een scatter-grafiek te maken, met dan bij de y-waarden de naam van je formule op deze manier: NaamWorkbook.xls!NaamDynamischeRange.

Mocht je wel headers gebruiken in je sheet, dan kun je je formule zo aanpassen dat sommige "COUNTA"-formules een aantal rijen overslaan (door gewoon "-2" (bijv.) erachter te plakken en haakjes te gebruiken). Handig middel om dit op te zetten: bekijk eens goed wat de OFFSET-functie precies doet en zorg dat je daarmee naar de juiste kolommen refereert.

Misschien handig om een breakdown te geven:
OFFSET(arg1,arg2,arg3,arg4,arg5)

1. Sheet1!$A$1 -> Referentie cell
2. geen rijen opschuiven (dus "0" of leeg laten)
3. COUNTA(Sheet1!$1:$1)-1 -> zoveel kolommen opschuiven (dus het totaal aantal kolommen gebruikt in rij 1) minus 1
4. COUNTA(INDIRECT("Sheet1!C"&COUNTA(Sheet1!$1:$1),FALSE)) -> (Dit is de crux van de dynamische range.) Tel het aantal entries in de kolom met het nummer wat overeenkomt met het aantal entries in rij 1. (Ik heb hier gebruik gemaakt van de INDIRECT-functie om naar die kolom te kunnen refereren in de COUNTA-functie. De 2e parameter van de INDIRECT-functie (FALSE) geeft aan dat de eerste parameter in R1C1-style is. Dus het is niet cell "C nogwat", maar kolom met nummer "nogwat"
5. 1 -> De breedte van de range moet 1 zijn om een uitkomst te krijgen voor je grafiek.

Ik hoop niet dat dit te complex is voor je wensen, maar het doet precies wat jij wilt.

En ja, dit is niet iets wat ik zelf ook 1-2-3 doorhad.

Tipje: http://www.cpearson.com/excel/excelF.htm#DynamicRanges

Oh, en ik heb comma's gebruikt als scheidingstekens omdat ik hier in de States op die manier met Excel werk. In NL zou je dus de comma's moeten vervangen door punt-comma's.

[ Voor 19% gewijzigd door CoRrRan op 08-02-2006 22:38 ]

[Gulf_5]

Als er iedere dag een kolom bijkomt, kun je een hulprij en een hulpkolom maken.

In de hulprij zet je een formule die bijvoorbeeld een "X" geeft als de datum die boven (of ergens anders in) de kolom vermeld staat (die moet er dan wel zijn natuurlijk, maar dat zal niet zo moelijk te realiseren zijn lijkt me) de grootste uit de reeks is.

In de hulpkolom maak je een simpele HORIZ.ZOEKEN / HLOOKUP functie die die X opzoekt, en de waarden uit de laatste kolom geeft.
Grafiekje baseren op de hulpkolom en je hebt altijd de laatste kolomgegevens.