Manipulating lists in OpenOffice.org Calc
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.
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
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.
Bruce Byfield (nanday)
Practical Task Scheduling Deployment
One of the best things about the UNIX environment (aside from being stable and efficient) is the vast array of software tools available to help you do your job. Traditionally, a UNIX tool does only one thing, but does that one thing very well. For example, grep is very easy to use and can search vast amounts of data quickly. The find tool can find a particular file or files based on all kinds of criteria. It's pretty easy to string these tools together to build even more powerful tools, such as a tool that finds all of the .log files in the /home directory and searches each one for a particular entry. This erector-set mentality allows UNIX system administrators to seem to always have the right tool for the job.
Cron traditionally has been considered another such a tool for job scheduling, but is it enough? This webinar considers that very question. The first part builds on a previous Geek Guide, Beyond Cron, and briefly describes how to know when it might be time to consider upgrading your job scheduling infrastructure. The second part presents an actual planning and implementation framework.
Join Linux Journal's Mike Diehl and Pat Cameron of Help Systems.
Free to Linux Journal readers.View Now!
|The Firebird Project's Firebird Relational Database||Jul 29, 2016|
|Stunnel Security for Oracle||Jul 28, 2016|
|SUSE LLC's SUSE Manager||Jul 21, 2016|
|My +1 Sword of Productivity||Jul 20, 2016|
|Non-Linux FOSS: Caffeine!||Jul 19, 2016|
|Murat Yener and Onur Dundar's Expert Android Studio (Wrox)||Jul 18, 2016|
- Stunnel Security for Oracle
- The Firebird Project's Firebird Relational Database
- SUSE LLC's SUSE Manager
- Murat Yener and Onur Dundar's Expert Android Studio (Wrox)
- Managing Linux Using Puppet
- My +1 Sword of Productivity
- Non-Linux FOSS: Caffeine!
- Doing for User Space What We Did for Kernel Space
- SuperTuxKart 0.9.2 Released
- Google's SwiftShader Released
With all the industry talk about the benefits of Linux on Power and all the performance advantages offered by its open architecture, you may be considering a move in that direction. If you are thinking about analytics, big data and cloud computing, you would be right to evaluate Power. The idea of using commodity x86 hardware and replacing it every three years is an outdated cost model. It doesn’t consider the total cost of ownership, and it doesn’t consider the advantage of real processing power, high-availability and multithreading like a demon.
This ebook takes a look at some of the practical applications of the Linux on Power platform and ways you might bring all the performance power of this open architecture to bear for your organization. There are no smoke and mirrors here—just hard, cold, empirical evidence provided by independent sources. I also consider some innovative ways Linux on Power will be used in the future.Get the Guide