Text 7921, 148 rader
Skriven 2006-11-11 22:34:00 av Robert Wolfe (1:261/1)
Ärende: Manipulating lists in OpenOffice.org Calc
=================================================
 
Manipulating lists in OpenOffice.org Calc  By Bruce Byfield 
Created 2006-10-29 18:20 


When asked to explain the purpose of spreadsheets, most people think of
calculations first. And it's true that spreadsheets like Calcs have hundreds of
different functions for performing calculations. However, probably the most
common tasks in spreadsheets is manipulating lists.

Calc and other spreadsheets have few advantages over a word processor when you
are just making a list. In fact, unless you have a spreadsheet template set up
so that cells have word wrap and hyphenation, you might be better off using a
word processor if all you have is half a dozen items on the list. However, as
the number of list items creeps up into the hundreds and beyond into the lower
fringes of database territory, the different ways that you can manipulate lists
in spreadsheets starts to give them a distinct advantages. In Calc, searching,
the printing of selected items, and, to a lesser extent, privacy can all be
made easier through the use of outline group controls, filters, and customized
sorts.


Online group controls

Sheets, rows, and columns can all be hidden or shown through the sub-menus of
the Format menu. While elements are hidden, they are neither visible or
printed, but can still be selected for copying if you select the elements
around them. For example, if column B is hidden, it is copied when you select
columns A and C. When you need them again, you can reverse the process, and
show the elements again.

Individual cells can be hidden or shown via Format > Cell > Cell Protection.
However, if you are continually hiding and showing the same cells, you can the
processes easier by creating outline groups, which add a set of controls for
hiding and showing the cells in the group that are quick to use and always
available.

If the contents of cells falls into a regular pattern, such as four cells
followed by a total, then you can use Data > Outline > Autoutline to have Calc
add outline controls based on the pattern. Otherwise, you can set outline
groups manually by selecting the cells for grouping, then choosing Data >
Outline > Group from the Calc menus. The Group window gives you a choice of
whether to group the selected cells by rows and columns. 

When you close the window, the outline group controls are visible between
either the row or column headers and the edges of the editing window. The
controls resemble the tree-structure of a file-manager in appearance, and can
be hidden by selecting Data > Outline > Hide Details. They are strictly for
on-line use, and do not print.
The basic outline controls have plus or minus signs at the start of the group
to show or hide hidden cells. However, if one or more outline group is nested
in another, the controls have numbered buttons for hiding different levels of
group.

Should you no longer need a group, place the mouse cursor in any cell in it and
select Data > Outline > Ungroup. To remove all groups on a sheet, select Data >
Outline > Remove.


Filtering which cells are visible

A filter is a list of conditions that each entry has to meet in order to be
displayed. You can set three types of filters from the Data > Filter sub-menu:
Automatic filters add a drop-down list to the top row of a column that contains
commonly used filters. They are quick and convenient, and, because the
condition includes every unique entry in the selected cells, are almost as
useful with text as with numbers. In addition to each unique entry, automatic
filters include the option to display all entries, the ten highest numerical
values, and all cells that are empty or not-empty, as well as a standard
filter. Their drawback is that they are somewhat limited. In particular, they
do not allow regular expressions, so you cannot display contents that is
similar, but not identical by using automatic filters.  Standard filters are
more complex than automatic filters. You can set as many as three conditions as
a filter, combining them with the Boolean operators AND and OR. Standard
filters are mostly useful for numbers, although a few of the conditional
operators, such as = and < > can also be useful for text. Other conditional
operators for standard filters include options to display the largest or
smallest values, or a percentage of them. Useful in themselves, standard
filters take on added values when used to further refine automatic filters.
They are useful mainly for numbers.  Advanced filters are structure similarly
to standard filters. The differences are that advanced filters are not limited
to three conditions, and their criteria are not entered in a dialog window.
Instead, advanced filters are entered in a blank area of a sheet, then
referenced by the advanced filter tool to apply them. If you are not planning
to use filters very often, you may choose to delete the criteria after using
them. But if you are using filters regularly, then label the area for advanced
filters so that you can find it more easily. 


Sorting records

A sort arranges the visible cells on the sheet. In Calc, you can sort by up to
three criteria, with each criteria applied one after the other. Sorts are handy
when you are searching for a particular list item, and become even more
powerful after you have filtered data. 

In addition, a sort is often useful when you add new information. When a list
is long, it is usually easier to add new information at the bottom of the
sheet, rather than adding rows in the proper places. Once you have added
information, you can then sort it to update the sheet.

You can apply a sort by highlighting the cells it applies to, then selecting
Data > Sort. The selected cells can be sorted by the order of information in up
to three columns or rows, in either ascending (A-Z, 1-9) or descending order
(Z-A, 9-1). 

On the Options tab of the Sort window, you can choose the following options:
Case sensitive: If two entries are otherwise identical, one with an upper case
letter is placed before one with a lower case letter in the same position. 
Range contains labels: Does not include the column heading in the sort. Include
formats: A cell's formatting is moved with its contents. If formatting is used
to distinguish different types of cells, then use this option.  Copy sort
results to: Sets a spreadsheet address to which to copy the sort results. If a
range is specified that does not have the necessary number of cells, then cells
are added. If a range contains cells that already have content, then the sort
fails. 
Custom sort order: Select the box, then choose one of the sort orders defined
in Tools > Options > Spreadsheet > Sort Lists from the drop-down list. As you
can see from those already defined, a sort order is a standard type of list,
such as the days of the week or the months in a year. they can also be used for
automatically filling cells. 
Direction: Sets whether rows or columns are sorted. The default is to sort by
columns unless the selected cells are in a single column 


Conclusion

The makers of free spreadsheets often overlook manipulating lists in favor of
focusing on the development of functions. This focus is understandable, given
the complexity and challenge of implementing functions. However, it also means
that projects like Gnumeric continue to lack some simple features that many
users rely on heavily. The majority of users probably use less than a few dozen
functions -- but almost all of them are apt to write lists in spreadsheets.
After all, task assignments, project planning, inventories, and grades are
givens in the home and the work place. No doubt other free spreadsheets will
get around to list manipulation some day, but, for now, Calc remains the only
major spreadsheet to provide this basic functionality.


Bruce Byfield is a computer journalist who writes regularly for the Linux
Journal and Newsforge websites.

Links

Source URL: http://interactive.linuxjournal.com/node/1000114

--- BBBS/NT v4.00 MP
 * Origin: Omicron Theta (1:261/1)