j***@gmail.com
2018-02-02 12:03:50 UTC
Has anybody tried using something like Excel to generate train graphs?
Excel is quite good for making tabular timetables, but turning the
tables into "string diagram" train graphs is a lot harder. I'm looking
for a (relatively) easy way to generate a train graph from the tabular
timetable. Part of the problem is I avoid anything by M$ like the plague
unless I absolutely have to use it, so I'm not familiar with all the ins
and outs of Excel.
Spacing the X axis by time is fine. One problem I've had is trying to
put the station names on the Y axis and space them by distance.
Another problem is intermediate stations which may not necessarily be
timetable points. "Express" trains don't have a time listed, so the line
on the chart is either broken or assumes a value of zero.
Cheers
David
I have an Excel spreadsheet that does a moderate job of it.Excel is quite good for making tabular timetables, but turning the
tables into "string diagram" train graphs is a lot harder. I'm looking
for a (relatively) easy way to generate a train graph from the tabular
timetable. Part of the problem is I avoid anything by M$ like the plague
unless I absolutely have to use it, so I'm not familiar with all the ins
and outs of Excel.
Spacing the X axis by time is fine. One problem I've had is trying to
put the station names on the Y axis and space them by distance.
Another problem is intermediate stations which may not necessarily be
timetable points. "Express" trains don't have a time listed, so the line
on the chart is either broken or assumes a value of zero.
Cheers
David
The technique I have used is to use a data-entry worksheet, similar to
the way Geoff Lambert described, although requiring separate columns for
the hours and the minutes.
This data is then processed through a series of intermediate sheets that
basically use the vlookup function to do the transposition so that the
distance in km becomes the y variable against the time being the x
variable. The km value for each Each timepoint is plotted against the
minutes into the day that distance point is reached. The intermediate
worksheet has one row for every minute of the day, and one column for
each train on the data entry sheet. Each intersection cell then
searches the entered data using vlookup to determine at what distance
that train was at that time.
Finally, a graph sheet is produced by plotting each train from the
transposed table. The graph enables the use to distinguish services by
using two different colours, and also identifies stopping points with a
circle.
Because its all in Excel it is quite easy to use, you get immediate
onscreen results and you dont need any extra software to print it.
It doesn't however handle the subtleties such as geoff describes like
offsets for crossing trains, trains travelling over midnight and station
names on the axis. but I am working on these.
I would offer to post it online, but because the solution required brute
force, the file is 22Mb in size. I could cut a CD and snailmail it to
anyone who would be genuinely interested.
Regards,
Chris Brownbill.
I am interested in acquiring this excel sheet. Can you post or send me a screen.
email: ***@vodamail.co.za