Using Excel on Exported data - Tips
Feb 14, 2018 23:15:22 GMT
Matthew, 86thechef, and 3 more like this
Post by Deleted on Feb 14, 2018 23:15:22 GMT
Few Tips I have used to make looking at the exported data in excel easier and more helpful.
I personally save all the data from my extracts into a single excel spreadsheet so that I can keep track of the changes I have made (as well as the original config)
There are two items I have added here,
A/=FIND(LEFT(TRIM(C5),1),C5)-1 This allows me to count the number of blank spaces at the beginning of each cell thus enabling me to identify control unit names, Adaption Types and Sub Types as well as the actual value.
In the picture below you can see in Column B there are values = to
4 = This means there are 4 blank spaces and this is the Control Unit Name and all the static details about the control unit.
8 = 8 Blank Spaces before any text which means this is the adaption channel name
12 = 12 blank Spaces and these are the Sub adaption channel names
16 = Actual Value that is set for each value
B/ simple excel code is =IF(F43=C43,"","Changed") so what this does is only show you the changed values between col C and F (which are different backups i have done)
All of the spreadsheet is filtered so that you can reduce, sort and find data easily.
Last bit of this section I also add a column at the start of the sheet with English in my case descriptions so as you can see below I can easily remember what each of these things do.
because the file is delimited with a semicolon ; not a comma (,) when you open in excel it leaves all the data in one column.
Go to the Data Menu in Excel, select Text to Column, tick the semicolon box and it will sort your data for you (see example below)
before
time;Accelerator pedal
0;1.387;49.98
307;0.742;48.89
after
time Accelerator pedal
0 1.387 49.98
307 0.742 48.89
If you want to insert a filter on each control unit (rather than one at the top of the sheet for all the data then
Select all the data in the control module, From the top excel menu go
Insert and select Table
and you will now have multiple filters for each control unit you have selected
1/ History Extracts,
I personally save all the data from my extracts into a single excel spreadsheet so that I can keep track of the changes I have made (as well as the original config)
There are two items I have added here,
A/=FIND(LEFT(TRIM(C5),1),C5)-1 This allows me to count the number of blank spaces at the beginning of each cell thus enabling me to identify control unit names, Adaption Types and Sub Types as well as the actual value.
In the picture below you can see in Column B there are values = to
4 = This means there are 4 blank spaces and this is the Control Unit Name and all the static details about the control unit.
8 = 8 Blank Spaces before any text which means this is the adaption channel name
12 = 12 blank Spaces and these are the Sub adaption channel names
16 = Actual Value that is set for each value
B/ simple excel code is =IF(F43=C43,"","Changed") so what this does is only show you the changed values between col C and F (which are different backups i have done)
All of the spreadsheet is filtered so that you can reduce, sort and find data easily.
Last bit of this section I also add a column at the start of the sheet with English in my case descriptions so as you can see below I can easily remember what each of these things do.
2/ Live Data extract in CSV all data in one cell
because the file is delimited with a semicolon ; not a comma (,) when you open in excel it leaves all the data in one column.
Go to the Data Menu in Excel, select Text to Column, tick the semicolon box and it will sort your data for you (see example below)
before
time;Accelerator pedal
0;1.387;49.98
307;0.742;48.89
after
time Accelerator pedal
0 1.387 49.98
307 0.742 48.89
3/ Multiple Filters
If you want to insert a filter on each control unit (rather than one at the top of the sheet for all the data then
Select all the data in the control module, From the top excel menu go
Insert and select Tableand you will now have multiple filters for each control unit you have selected
you can activate all you want with out credit