Help with Excel charts?

Discussion in 'OT Technology' started by trouphaz, Jul 11, 2008.

  1. trouphaz

    trouphaz New Member

    Joined:
    Sep 22, 2003
    Messages:
    2,666
    Likes Received:
    0
    So, I have a chart I created. X axis comes from column A and Y comes from column D. I want to create a second graph that is identical to the first but now I want the Y to come from column G. Is there any sort of find and replace that I can use in a chart?

    I'm also going to have to recreate these same charts on another page in the workbook. Total I'm going to have 9 charts, each with 20 series... that's a whole lot of clicking in Excel's somewhat painful interface.



    The shitty format of my data (sar -d, modified to include the timestamp on every line instead of the first of a series, sorted by disk name and then time stamp) makes it painful to use the wizard. I'm ending up with rows 1-50 for series 1 (one line in my graph), 51-100 for series 2, etc up to 20 different series. In column A, rows 1-50 are identical to 51-100 (new disk, same times).
     
  2. crontab

    crontab (uid = 0)

    Joined:
    Nov 14, 2000
    Messages:
    23,441
    Likes Received:
    12
    gnuplot that shit...

    right click the chart, source data, replace D with G.

    but seriously, i use gnuplot to plot all of my data for myself. much easier than the pointy clicky shit. if it needs to be automated on a chronic basis, i give to a guy to rrdtool it

    but i'm sure there are macros for this.
     
  3. trouphaz

    trouphaz New Member

    Joined:
    Sep 22, 2003
    Messages:
    2,666
    Likes Received:
    0
    yeah, i was hoping for a macro, but i'm not all that familiar with creating them.


    i started messing around with rrdtool and some other stuff and just didn't like the graphs it was creating and didn't have the time to futz with it.
     
  4. trouphaz

    trouphaz New Member

    Joined:
    Sep 22, 2003
    Messages:
    2,666
    Likes Received:
    0
    I made a macro to do it. I used a shell script to generate the commands (the list goes to 1633, so this is only a snippet). I've included the ghetto loop I used in my script to create the macro.

    Code:
       ActiveSheet.ChartObjects("Chart 6").Activate
        ActiveChart.ChartArea.Select
        ActiveChart.SeriesCollection.NewSeries
        ActiveChart.SeriesCollection(1).Name = "='7-09'!$B$2"
        ActiveChart.SeriesCollection(1).Values = "='7-09'!$G$2:$G$49"
        ActiveChart.SeriesCollection.NewSeries
        ActiveChart.SeriesCollection(2).Name = "='7-09'!$B$50"
        ActiveChart.SeriesCollection(2).Values = "='7-09'!$G$50:$G$97"
        ActiveChart.SeriesCollection.NewSeries
        ActiveChart.SeriesCollection(3).Name = "='7-09'!$B$98"
        ActiveChart.SeriesCollection(3).Values = "='7-09'!$G$98:$G$145"
        <more of the same>
        End Sub

    Code:
    #!/usr/bin/bash
    typeset -i Y=2
    typeset -i C=1
    while [ $Y -le 850 ]
    do
    typeset -i X=$Y+24
        echo "    ActiveChart.SeriesCollection.NewSeries"
        echo "    ActiveChart.SeriesCollection(${C}).Name = \"='6-24'!\$B\$$Y\""
        echo "    ActiveChart.SeriesCollection(${C}).Values = \"='6-24'!\$G\$$Y:\$G\$$X\""
    Y=$X+1
    C=$C+1
    done
    
     

Share This Page