[PDF] OpenOffice.org 3.3 Calc Guide Published 18 April 2011. Based

View - Download OpenOffice.org 3.3 Calc Guide

Previous PDF

Next PDF

OpenOffice.org 3.3

Calc Guide

Using Spreadsheets in OpenOffice.org 3.3

Copyright

This document is Copyright © 2005-2011 by its contributors as listed below. You may distribute it and/or modify it under the terms of either the GNU General Public License (http://www.gnu.org/licenses/gpl.html), version 3 or later, or the Creative Commons Attribution License (http://creativecommons.org/licenses/by/3.0/), version

3.0 or later. Note that Chapter 8, Using the DataPilot, is licensed under the Creative

Commons Attribution-Share Alike License, version 3.0.

Contributors

Rick BarnesPeter KupferMartin Fox

James AndrewKrishna AradhiAndy Brown

Stephen BuckBruce ByfieldNicole Cairns

T. J. FrazierStigant FyrwitfulIngrid Halama

Spencer E. HarpeRegina HenschelPeter Hillier-Brook

John KaneKirk AbbottEmma Kirsopp

Jared KobosSigrid KronenbergerShelagh Manton

Alexandre MartinsKashmira PatelAnthony Petrillo

Andrew PitonyakIain RobertsHazel Russman

Gary SchnablRob ScottJacob Starr

Sowbhagya SundaresanNikita TelangBarbara M Tobias

John ViestenzJean Hollis WeberStefan Weigel

Sharon WhistonClaire WoodLinda Worthington

Michele ZarriMagnus AdielssonSandeep Samuel Medikonda

Feedback

Please direct any comments or suggestions about this document to: authors@documentation.openoffice.org

Publication date and software version

Published 18 April 2011. Based on OpenOffice.org 3.3.

You can download

an editable version of this document from

Contents

Note for Mac users....................................................................................................8

Chapter 1

Introducing Calc..........................................................................................................9

What is Calc?...........................................................................................................10

Spreadsheets, sheets, and cells...............................................................................10

Parts of the main Calc window................................................................................10

Starting new spreadsheets......................................................................................18

Opening existing spreadsheets................................................................................20

Opening CSV files....................................................................................................21

Saving spreadsheets................................................................................................22

Password protection.................................................................................................24

Navigating within spreadsheets..............................................................................26

Selecting items in a sheet or spreadsheet...............................................................29

Working with columns and rows..............................................................................32

Working with sheets.................................................................................................33

Viewing Calc............................................................................................................34

Using the Navigator.................................................................................................38

Using document properties......................................................................................40

Chapter 2

Entering, Editing, and Formatting Data....................................................................43

Entering data using the keyboard...........................................................................44

Speeding up data entry............................................................................................46

Sharing content between sheets..............................................................................49

Validating cell contents............................................................................................50

Editing data..............................................................................................................52

Formatting data.......................................................................................................53

Autoformatting cells and sheets..............................................................................59

Formatting spreadsheets using themes...................................................................60

Using conditional formatting...................................................................................60

Hiding and showing data.........................................................................................61

Sorting records........................................................................................................63

Finding and replacing in Calc..................................................................................65

Chapter 3

Creating Charts and Graphs......................................................................................69

Creating a chart.......................................................................................................70

Editing charts...........................................................................................................74

Formatting charts....................................................................................................79

Formatting 3D charts...............................................................................................82

Formatting the chart elements................................................................................85

OpenOffice.org 3.3 Calc Guide3

Adding drawing objects to charts............................................................................87

Resizing and moving the chart................................................................................88

Gallery of chart types...............................................................................................89

Chapter 4

Using Styles and Templates in Calc...........................................................................98

What is a template?.................................................................................................99

What are styles?.......................................................................................................99

Types of styles in Calc..............................................................................................99

Accessing styles.....................................................................................................100

Applying cell styles................................................................................................101

Applying page styles..............................................................................................102

Modifying styles.....................................................................................................103

Creating new (custom) styles.................................................................................106

Copying and moving styles....................................................................................107

Deleting styles.......................................................................................................109

Creating a spreadsheet from a template...............................................................109

Creating a template...............................................................................................110

Editing a template..................................................................................................111

Adding templates using the Extension Manager...................................................113

Setting a default template.....................................................................................113

Associating a spreadsheet with a different template.............................................114

Organizing templates.............................................................................................115

Chapter 5

Using Graphics in Calc.............................................................................................117

Graphics in Calc.....................................................................................................118

Adding graphics (images)......................................................................................118

Modifying images...................................................................................................123

Using the picture context menu.............................................................................128

Using Calc's drawing tools....................................................................................130

Positioning graphics...............................................................................................133

Creating an image map..........................................................................................136

Chapter 6

Printing, Exporting, and E-mailing..........................................................................138

Quick printing........................................................................................................139

Controlling printing...............................................................................................139

Using print ranges.................................................................................................142

Page breaks............................................................................................................145

Printing options setup in page styles.....................................................................146

Headers and footers...............................................................................................148

Exporting to PDF...................................................................................................151

Exporting to XHTML..............................................................................................157

Saving as Web pages (HTML)................................................................................157

E-mailing spreadsheets..........................................................................................157

Digital signing of documents.................................................................................157

4OpenOffice.org 3.3 Calc Guide

Removing personal data........................................................................................158

Chapter 7

Using Formulas and Functions................................................................................159

Setting up a spreadsheet.......................................................................................160

Creating formulas..................................................................................................161

Understanding functions........................................................................................175

Strategies for creating formulas and functions.....................................................180

Finding and fixing errors.......................................................................................182

Examples of functions............................................................................................187

Using regular expressions in functions..................................................................191

Advanced functions................................................................................................192

Chapter 8

Using the DataPilot..................................................................................................193

Examples with step by step descriptions...............................................................194

DataPilot functions in detail...................................................................................214

Function GETPIVOTDATA......................................................................................236

Chapter 9

Data Analysis...........................................................................................................240

Consolidating data.................................................................................................241

Creating subtotals..................................................................................................243

Using "what if" scenarios......................................................................................245

Using other "what if" tools....................................................................................249

Working backwards using Goal Seek.....................................................................254

Using the Solver.....................................................................................................256

Chapter 10

Linking Calc Data......................................................................................................259

Why use multiple sheets?.......................................................................................260

Setting up multiple sheets.....................................................................................260

Referencing other sheets in the spreadsheet........................................................263

Referencing other documents: links to sheets in other spreadsheets...................266

Hyperlinks and URLs.............................................................................................267

Linking to external data.........................................................................................270

Linking to registered data sources........................................................................274

Embedding spreadsheets.......................................................................................278

Chapter 11

Sharing and Reviewing Documents..........................................................................283

Sharing documents (collaboration)........................................................................284

Recording changes.................................................................................................286

Adding comments to changes................................................................................288

Adding other comments.........................................................................................290

OpenOffice.org 3.3 Calc Guide5

Reviewing changes................................................................................................291

Merging documents...............................................................................................294

Comparing documents...........................................................................................295

Saving versions......................................................................................................295

Chapter 12

Calc Macros............................................................................................................298

Using the macro recorder......................................................................................299

Write your own functions.......................................................................................303

Accessing cells directly..........................................................................................310

Chapter 13

Calc as a Simple Database.......................................................................................313

Associating a range with a name...........................................................................315

Calc functions similar to database functions.........................................................330

Database-specific functions...................................................................................339

Chapter 14

Setting up and Customizing Calc.............................................................................341

Choosing options that affect all of OOo.................................................................342

Choosing options for loading and saving documents.............................................347

Choosing options for Calc......................................................................................350

Controlling Calc's AutoCorrect functions..............................................................358

Customizing the user interface..............................................................................358

Adding functionality with extensions.....................................................................366

Appendix A

Keyboard Shortcuts..................................................................................................369

Navigation and selection shortcuts........................................................................370

Function and arrow key shortcuts.........................................................................371

Cell formatting shortcuts.......................................................................................373

DataPilot shortcuts................................................................................................374

Appendix B

Description of Functions..........................................................................................375

Functions available in Calc....................................................................................376

Mathematical functions.........................................................................................376

Financial analysis functions...................................................................................381

Statistical analysis functions..................................................................................393

Date and time functions.........................................................................................401

6OpenOffice.org 3.3 Calc Guide

Logical functions....................................................................................................404

Informational functions..........................................................................................405

Database functions................................................................................................407

Array functions......................................................................................................408

Spreadsheet functions...........................................................................................410

Text functions.........................................................................................................414

Add-in functions.....................................................................................................417

Appendix C

Calc Error Codes......................................................................................................421

Introduction to Calc error codes............................................................................422

Error codes displayed within cells.........................................................................423

General error codes...............................................................................................424

OpenOffice.org 3.3 Calc Guide7

Note for Mac users

Some keystrokes and menu items are different on a Mac from those used in Windows and Linux. The table below gives some common substitutions for the instructions in this chapter. For a more detailed list, see the application Help.

Windows/LinuxMac equivalentEffect

Tools > Options

menu selectionOpenOffice.org >

PreferencesAccess setup options

Right-clickControl+clickOpen context menu

Ctrl (Control)z (Command)Used with other keys

F5Shift+z+F5Open the Navigator

F11z+TOpen Styles & Formatting window

8OpenOffice.org 3.3 Calc Guide

Chapter 1 Introducing Calc

What is Calc?

Calc is the spreadsheet component of OpenOffice.org (OOo). You can enter data (usually numerical) in a spreadsheet and then manipulate this data to produce certain results. Alternatively, you can enter data and then use Calc in a 'What if...' manner by changing some of the data and observing the results without having to retype the entire spreadsheet or sheet.

Other features provided by Calc include:

•Functions, which can be used to create formulas to perform complex calculations on data •Database functions, to arrange, store, and filter data •Dynamic charts; a wide range of 2D and 3D charts •Macros, for recording and executing repetitive tasks; scripting languages supported include OpenOffice.org Basic, Python, BeanShell, and JavaScript •Ability to open, edit, and save Microsoft Excel spreadsheets •Import and export of spreadsheets in multiple formats, including HTML, CSV,

PDF, and PostScript

NoteIf you want to use macros written in Microsoft Excel using the VBA macro code in OOo, you must first edit the code in the OOo Basic IDE editor. See Chapter 12 (Calc Macros).

Spreadsheets, sheets, and cells

Calc works with elements called spreadsheets. Spreadsheets consist of a number of individual sheets, each sheet containing cells arranged in rows and columns. A particular cell is identified by its row number and column letter. Cells hold the individual elements - text, numbers, formulas, and so on - that make up the data to display and manipulate. Each spreadsheet can have many sheets, and each sheet can have many individual cells. In Calc 3.3, each sheet can have a maximum of 1,048,576 (65,536 rows in Calc

3.2 and earlier) and a maximum of 1024 columns

Parts of the main Calc window

When Calc is started, the main window looks similar to Figure 1. NoteIf any part of the Calc window in Figure 1 is not shown, you can display it using the View menu. For example, View > Status Bar will toggle (show or hide) the Status Bar. It is not always necessary to display all the parts, as shown; show or hide any of them, as desired.

10OpenOffice.org 3.3 Calc Guide

Figure 1: Parts of the Calc window

Title bar

The Title bar, located at the top, shows the name of the current spreadsheet. When the spreadsheet is newly created, its name is Untitled X, where X is a number. When you save a spreadsheet for the first time, you are prompted to enter a name of your choice.

Menu bar

Under the Title bar is the Menu bar. When you choose one of the menus, a submenu appears with other options. You can modify the Menu bar, as discussed in Chapter 14 (Setting Up and Customizing Calc). •File contains commands that apply to the entire document such as Open, Save, Wizards, Export as PDF, and Digital Signatures. •Edit contains commands for editing the document such as Undo, Changes,

Compare Document, and Find and Replace.

•View contains commands for modifying how the Calc user interface looks such as Toolbars, Full Screen, and Zoom. •Insert contains commands for inserting elements such as cells, rows, columns, sheets, and pictures into a spreadsheet. •Format contains commands for modifying the layout of a spreadsheet such as Styles and Formatting, Paragraph, and Merge Cells. •Tools contains functions such as Spelling, Share Document, Cell Contents,

Gallery, and Macros.

•Data contains commands for manipulating data in your spreadsheet such as

Define Range, Sort, Filter, and DataPilot.

Chapter 1 Introducing Calc11

•Window contains commands for the display window such as New Window,

Split, and Freeze.

•Help contains links to the Help file bundled with the software, What's This?,

Support, Registration, and Check for Updates.

Toolbars

Calc has several types of toolbars: docked (fixed in place), floating, and tear-off. Docked toolbars can be moved to different locations or made to float, and floating toolbars can be docked. Four toolbars are located under the Menu bar by default: the Standard toolbar, the Find toolbar, the Formatting toolbar, and the Formula Bar. The icons (buttons) on these toolbars provide a wide range of common commands and functions. You can also modify these toolbars, as discussed in Chapter 14 (Setting Up and Customizing Calc). Placing the mouse pointer over any of the icons displays a small box, called a tooltip. It gives a brief explanation of the icon's function. For a more detailed explanation, choose Help > What's This? and hover the mouse pointer over the icon. To turn this feature off again, click once or press the Esc key twice. Tips and extended tips can be turned on or off from Tools > Options > OpenOffice.org > General.

Displaying or hiding toolbars

To display or hide toolbars, choose View > Toolbars, then click on the name of a toolbar in the list. An active toolbar shows a check mark beside its name. Tear-off toolbars are not listed in the View menu.

Palettes and tear-off toolbars

Toolbar icons with a small triangle to the right will display palettes, tear-off toolbars, and other ways of selecting things, depending on the icon. An example of a palette is shown in Figure 2. It is displayed by clicking the small triangle to the right of the Borders toolbar icon.

Figure 2: Toolbar palette

An example of a tear-off toolbar is shown in Figure 3. Tear-off toolbars can be floating or docked along an edge of the screen or in one of the existing toolbar areas. To move a floating tear-off toolbar, drag it by the title bar.

12OpenOffice.org 3.3 Calc Guide

Figure 3: Example of a tear-off toolbar

Moving toolbars

To move a docked toolbar, place the mouse pointer over the toolbar handle, hold down the left mouse button, drag the toolbar to the new location, and then release the mouse button.

Figure 4: Moving a docked toolbar

To move a floating toolbar, click on its title bar and drag it to a new location, as shown in Figure 3.quotesdbs_dbs19.pdfusesText_25

[PDF] open office calc formule

[PDF] gestion des salaires pdf

[PDF] exercice calcul bulletin de salaire

[PDF] cours salaires comptabilité

[PDF] les charges de personnel cours pdf

[PDF] rémunération pdf

[PDF] surface d'un segment de cercle

[PDF] comment calculer l'aire d'un disque tronqué

[PDF] aire d'un segment circulaire formule

[PDF] calcul de somme sigma

[PDF] somme 1/n(n+1)

[PDF] comment calculer la somme d'une série numérique

[PDF] comment calculer la somme d'une série

[PDF] somme double i/j

[PDF] garam