Difference between revisions of "C-CWEL Excel Practice"
(Created page with "i'm trying to think a little ahead. by about a month from now, i'd like to be able to help you, over a telecon, do some of the color cuts on at least a subset of our data, or ...") |
m |
||
| Line 1: | Line 1: | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | + | =Email from 23 Apr= | |
| − | + | The basic skills you'd need in excel are: | |
| − | |||
| − | |||
| − | |||
| − | |||
| − | ( | + | (1) '''Reading a text file into Excel'''. (I do have a screencapture tutorial on that -- again, developed for another team, but fundamentally the same concepts even if the first little bit isn't directly applicable : http://www.youtube.com/watch?v=nCJ3ctOGvNk&list=UUQfN0BTwaSZ6ABsQcieCpdA&index=1&feature=plcp |
| − | |||
| − | |||
| − | |||
| − | |||
| − | ( | + | (2) '''Convincing Excel how to ignore certain data'''. In my world, I need a placeholder for 'no data', so in most of my tables, -9.00 means 'no data available'. The most efficient way to convince Excel that that is 'no data' is just a global search and replace. There are some subtle tricks on this, which is why I am listing this as a separate skill. |
| − | |||
| − | |||
| − | 'no data' | ||
| − | ( | + | (3) '''Manipulating columns'''. For example, programming column E to be column B-D. More handling of "no data" issues here. (For a test set, I could limit this to just be objects where there are data in all columns, making the 'no data' issues non-existent. But for the "real thing", this will be an issue.) |
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | ( | ||
| − | |||
| − | |||
| − | this to just be objects where there are data in all columns, making the | ||
| − | 'no data' issues non-existent, | ||
| − | |||
| − | ( | + | (4) '''Plotting columns against other columns''', and setting limits on what is plotted such that the 'no data' objects don't appear. Personally, I think plotting in Excel is a gigantic pain in the @$$ and I never get it right on the first try. it is far easier for me to write code to make plots, so that is what I do. I will be of limited help on this; I will just end up poking buttons until I stumble on what works for your excel version. You need to pick 'xy plots' or 'scatter plots' or other words like those (exactly what you pick is dependent on your excel version.) There are several you tube tutorials from the general public that I can find that address making plots in Excel. (Again, for a test set, I could limit this to just be objects where there are data in all columns, making the 'no data' issues non-existent, but this will be an issue in our "real" data set.) |
| − | |||
| − | |||
| − | columns | ||
| − | |||
| − | ( | + | (5) '''Plotting lines on top of such plots''', NOT ones that just connect the points as plotted in the prior step, but instead can be represented by equations. You could create an additional pair of columns (or set of columns) on another sheet within the same workbook to overplot on the plot; that's what I would think would be the easiest thing to do. |
| − | |||
| − | |||
| − | the | ||
| − | |||
| − | |||
| − | would | ||
| + | (6) '''Identifying objects in different sectors of that plot'''. This is not particularly trivial in Excel, at least as I can think of off the top of my head. The same basic approach I used in my own code would probably be the easiest here -- add another excel column that is 1 ('yes') if the object passes the test and 0 ('no') if not. (And repeat for each of the series of tests.) The code that would automatically populate that column would probably be relatively complex, compared to the other items here. | ||
| − | NOW, keep in mind that the main reason | + | |
| − | better understanding of the Koenig color cuts, because | + | NOW, keep in mind that the main reason I want to do this is to give you a better understanding of the Koenig color cuts, because I sense that this is important to you. in terms of the skills you're going to need to do the rest of the things we need to do, #6 here is the least important, in part because for our BRC 38 catalog, Xavier has already run his color cuts, so we already have the set of yes/no categories for each object, so we need not reproduce all of that work. I would like to have you do at least a few cuts so that you get the idea. #6 really is the least critical, because for illustrative purposes, we could do step 6 by hand. |
| − | is important to you. in terms of the skills you're going to need to do the | ||
| − | rest of the things we need to do, #6 here is the least important, in part | ||
| − | because for our BRC 38 catalog, Xavier has already run his color cuts, so | ||
| − | we already have the set of yes/no categories for each object, so we need | ||
| − | not reproduce all of that work. I would like to have you do at least a few | ||
| − | cuts so that you get the idea. #6 really is the least critical, because | ||
| − | for illustrative purposes, we could do step 6 by hand. | ||
Revision as of 03:59, 9 May 2013
Email from 23 Apr
The basic skills you'd need in excel are:
(1) Reading a text file into Excel. (I do have a screencapture tutorial on that -- again, developed for another team, but fundamentally the same concepts even if the first little bit isn't directly applicable : http://www.youtube.com/watch?v=nCJ3ctOGvNk&list=UUQfN0BTwaSZ6ABsQcieCpdA&index=1&feature=plcp
(2) Convincing Excel how to ignore certain data. In my world, I need a placeholder for 'no data', so in most of my tables, -9.00 means 'no data available'. The most efficient way to convince Excel that that is 'no data' is just a global search and replace. There are some subtle tricks on this, which is why I am listing this as a separate skill.
(3) Manipulating columns. For example, programming column E to be column B-D. More handling of "no data" issues here. (For a test set, I could limit this to just be objects where there are data in all columns, making the 'no data' issues non-existent. But for the "real thing", this will be an issue.)
(4) Plotting columns against other columns, and setting limits on what is plotted such that the 'no data' objects don't appear. Personally, I think plotting in Excel is a gigantic pain in the @$$ and I never get it right on the first try. it is far easier for me to write code to make plots, so that is what I do. I will be of limited help on this; I will just end up poking buttons until I stumble on what works for your excel version. You need to pick 'xy plots' or 'scatter plots' or other words like those (exactly what you pick is dependent on your excel version.) There are several you tube tutorials from the general public that I can find that address making plots in Excel. (Again, for a test set, I could limit this to just be objects where there are data in all columns, making the 'no data' issues non-existent, but this will be an issue in our "real" data set.)
(5) Plotting lines on top of such plots, NOT ones that just connect the points as plotted in the prior step, but instead can be represented by equations. You could create an additional pair of columns (or set of columns) on another sheet within the same workbook to overplot on the plot; that's what I would think would be the easiest thing to do.
(6) Identifying objects in different sectors of that plot. This is not particularly trivial in Excel, at least as I can think of off the top of my head. The same basic approach I used in my own code would probably be the easiest here -- add another excel column that is 1 ('yes') if the object passes the test and 0 ('no') if not. (And repeat for each of the series of tests.) The code that would automatically populate that column would probably be relatively complex, compared to the other items here.
NOW, keep in mind that the main reason I want to do this is to give you a better understanding of the Koenig color cuts, because I sense that this is important to you. in terms of the skills you're going to need to do the rest of the things we need to do, #6 here is the least important, in part because for our BRC 38 catalog, Xavier has already run his color cuts, so we already have the set of yes/no categories for each object, so we need not reproduce all of that work. I would like to have you do at least a few cuts so that you get the idea. #6 really is the least critical, because for illustrative purposes, we could do step 6 by hand.
