DataPilot functions in detail
This part describes the use and options of the DataPilot in detail.
The database (preconditions)
The basis for work with the DataPilot is a list of raw data, similar to a database table, consisting of rows (data sets) and columns (data fields). The field names are in the first row above the list.
The data source could be an external file or database. For the simplest case, where data is contained in a Calc spreadsheet, Calc offers sorting functions that do not require the DataPilot.
For processing data in lists, the program needs to know where in the spreadsheet the table is. The table can be anywhere in the sheet, in any position. A spreadsheet can contain several unrelated tables.
Calc recognizes your lists automatically. It uses the following logic:
Starting from the cell you’ve selected (which must be within your list), Calc checks the surrounding cells in all 4 directions (left, right, above, below). The border is recognized if the program discovers an empty row or column, or if it hits the left or upper border of the spreadsheet.
This means that the described functions can only work correctly if there are no empty rows or columns in your list. Avoid empty lines (for example for formatting). You can format your list by using cell formats.
If you select more than one single cell before you start sorting, filtering or calling the DataPilot, then the automatic list recognition is switched off. Calc assumes that the list matches exactly the cells you have selected.
A relatively big source for errors is that you might declare a list by mistake and you sort your list. If you select multiple cells (for example, a whole column) then the sorting mixes up the data that should be together in one row.
In addition to these formal aspects, the logical structure of your table is very important when using the DataPilot.
When entering the data, do not add outlines, groups. or summaries. This becomes clear when we think about what we could have done wrong in our Sales list example in the section Sales volume overview. This will give you a list of bad ideas, that you can find very often among spreadsheet users who are not informed about the possibilities of processing lists within a spreadsheet.
First bad idea: You could have made several sheets. For example, you could have made a sheet for each group of articles. Analyses are then only possible within each group. Analyses for several groups would then be a lot of hassle.
Second bad idea: In the turnover list, instead of only one column for the amount, you could have made a column for the amounts for each employee. The amounts than had to be entered into the appropriate column. An analysis with the DataPilot would not be possible any more. In contrast, one result of the DataPilot is that you can get results for each employee if you have entered everything in one column.
Third bad idea: You could have entered the amounts in chronological order. At the end of each month you could have made a sum. In this case, a sorting of the list for different criteria is not possible, and you could not use the DataPilot. The rows with the sums would be treated by the DataPilot like any other amount you have entered. Getting monthly results is one of the very fast and easy features of the DataPilot.
You start the DataPilot with Data > DataPilot > Start. If the list to be analyzed is in a spreadsheet table, select one cell within this list. Calc recognizes and selects the list automatically for use with the DataPilot.
|Content on this page is licensed under the Creative Common Attribution-Share Alike 3.0 license (CC-BY-SA).|