In my previous incarnation as a programmer, I used pivot tables in Excel quite extensively. They're quite useful in my current career as an engineer. In fact, there were times when summarizing data tables in any other way would have proved exceptionally difficult, if not downright impossible, which is why I've been loathe to give up using Excel. And now I can do the same things using Open Office's Calc. Excerpt:

Creating a DataPilot

To begin creating an datapilot, highlight the range of cells you want to base it upon, then select Data -> DataPilot -> Start to open the DataPilot dialog window. Alternatively, choose the same menu item, then select a data source that you have already registered with OpenOffice.org using File -> New -> Database and a range of cells from it.

The DataPilot window gives you a diagram of the DataPilot that you are creating, and a list of columns from the data source. To create the general layout for the DataPilot, all you have to do is drag the columns to one of the blank spaces on the diagram. If you drag a column name to the Column fields or Row fields space, then it becomes the first cell in a row or column, just as you might expect from the name (in the first DataPilot above, Quantity was selected as the column, and no row was chosen). Similarly, if you drag a column name to the Data fields, it becomes the data in the DataPilot (in the first example above, the Price). The only potentially puzzling choice is the Page Fields, which is actually just the custom filter for changing the contents of the DataPilot on the fly (in the first example, the Country). If you make a mistake, you can drag the column back to the list of column building blocks on the right.

Once you have done the basic setup, you can also choose what function to use in the DataPilot. In the examples above, I simply used the default Sum function, which for many purposes is all that you need. However, you can also use another ten basic functions: Count, Average, Max, Min, Product, Count (Numbers Only), StDev (Sample), StDevP (Population), Var (Sample) and Var (Population). If necessary, you can find details about what these functions do in OpenOffice.org's online help.

Again, I can't stress enough how useful this tool can be to anyone who wades through piles of data. I plan on asking management if I can teach a short class on pivot tables here at work, as people simply blink and stare at me when I mention them. Okay, more than they usually blink and stare at me.

Posted by: Physics Geek at 11:15 AM | Comments (2) | Add Comment
Post contains 464 words, total size 3 kb.