[PDF] OpenOffice.org 3.x Calc Guide Published 8 September 2010. Based

View - Download OpenOffice.org 3.x Calc Guide

Previous PDF

Next PDF

Calc Guide

Using Spreadsheets in OpenOffice.org

This PDF is designed to be read onscreen, two pages at a time. If you want to print a copy, your PDF viewer should have an option for printing two pages on one sheet of paper, but you may need to start with page 2 to get it to print facing pages correctly. (Print this cover page separately.)

Copyright

This document is Copyright © 2005-2010 by its contributors as listed in the section titled Authors. You may distribute it and/or modify it under the terms of either the GNU General Public License, version 3 or later, or the Creative Commons Attribution License, 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. All trademarks within this guide belong to their legitimate owners.

Authors

Rick BarnesPeter Kupfer

James AndrewKrishna Aradhi

Andy BrownStephen Buck

Bruce ByfieldMartin J. Fox

T. J. FrazierStigant Fyrwitful

Spencer E. HarpeRegina Henschel

Peter Hillier-BrookJohn Kane

KirkEmma Kirsopp

Jared KobosSigrid Kronenberger

Shelagh MantonAlexandre Martins

Kashmira PatelAnthony Petrillo

Andrew PitonyakIain Roberts

Hazel RussmanGary Schnabl

Rob ScottSowbhagya Sundaresan

Nikita TelangBarbara M Tobias

John ViestenzJean Hollis Weber

Stefan WeigelSharon Whiston

Claire WoodLinda Worthington

Michele ZarriMagnus Adielsson

Sandeep Samuel Medikonda

Feedback

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

Publication date and software version

Published 8 September 2010. Based on OpenOffice.org 3.2.

You can download

an editable version of this document from

Contents

Chapter 1

Introducing Calc.........................................................................9 What is Calc?....................................................................................10 Spreadsheets, sheets, and cells........................................................10 Parts of the main Calc window..........................................................11 Starting new spreadsheets...............................................................16 Opening existing spreadsheets.........................................................17 Opening CSV files.............................................................................18 Saving spreadsheets.........................................................................19 Navigating within spreadsheets........................................................23 Selecting items in a sheet or spreadsheet........................................27 Working with columns and rows.......................................................30 Working with sheets..........................................................................32 Viewing Calc.....................................................................................33 Using the Navigator..........................................................................38

Chapter 2

Entering, Editing, and Formatting Data...................................41 Entering data using the keyboard.....................................................42 Speeding up data entry.....................................................................45 Sharing content between sheets.......................................................48 Validating cell contents.....................................................................49 Editing data......................................................................................51 Formatting data................................................................................53 Autoformatting cells and sheets........................................................59 Formatting spreadsheets using themes............................................60 Using conditional formatting............................................................61 Hiding and showing data..................................................................63 Sorting records.................................................................................65 Finding and replacing in Calc...........................................................67

OpenOffice.org 3.x Calc Guide3

Chapter 3

Creating Charts and Graphs.....................................................72 Creating a chart................................................................................73 Editing charts...................................................................................78 Formatting charts.............................................................................84 Formatting 3D charts........................................................................87 Formatting the chart elements.........................................................91 Resizing and moving the chart..........................................................93 Gallery of chart types........................................................................95

Chapter 4

Using Styles and Templates in Calc........................................105 What is a template?........................................................................106 What are styles?..............................................................................106 Types of styles in Calc.....................................................................107 Accessing styles..............................................................................108 Applying cell styles.........................................................................109 Applying page styles.......................................................................111 Modifying styles..............................................................................111 Creating new (custom) styles..........................................................116 Copying and moving styles.............................................................117 Deleting styles................................................................................119 Creating a spreadsheet from a template.........................................119 Creating a template........................................................................120 Editing a template...........................................................................121 Adding templates using the Extension Manager.............................123 Setting a default template..............................................................124 Associating a spreadsheet with a different template......................125 Organizing templates......................................................................126

Chapter 5

Using Graphics in Calc...........................................................129 Graphics in Calc..............................................................................130 Adding graphics (images)...............................................................130

4OpenOffice.org 3.x Calc Guide

Modifying images............................................................................136 Using the picture context menu......................................................142 Using Calc's drawing tools..............................................................145 Positioning graphics........................................................................148 Creating an image map...................................................................151

Chapter 6

Printing, Exporting, and E-mailing........................................154 Quick printing.................................................................................155 Controlling printing........................................................................155 Using print ranges..........................................................................159 Page breaks.....................................................................................163 Headers and footers........................................................................164 Exporting to PDF............................................................................167 Exporting to XHTML.......................................................................173 Saving as Web pages (HTML).........................................................174 E-mailing spreadsheets...................................................................174 Digital signing of documents..........................................................175 Removing personal data.................................................................176

Chapter 7

Using Formulas and Functions...............................................177 Setting up a spreadsheet................................................................178 Creating formulas...........................................................................180 Understanding functions.................................................................197 Strategies for creating formulas and functions...............................203 Finding and fixing errors................................................................205 Examples of functions.....................................................................210 Using regular expressions in functions...........................................215 Advanced functions.........................................................................217

Chapter 8

Using the DataPilot................................................................218 Examples with step by step instructions.........................................219

OpenOffice.org 3.x Calc Guide5

DataPilot functions in detail............................................................241 Function GETPIVOTDATA...............................................................267

Chapter 9

Data Analysis..........................................................................271 Consolidating data..........................................................................272 Creating subtotals...........................................................................275 Using "what if" scenarios...............................................................277 Using other "what if" tools.............................................................281 Working backwards using Goal Seek..............................................288 Using the Solver.............................................................................290

Chapter 10

Linking Calc Data....................................................................294 Why use multiple sheets?................................................................295 Setting up multiple sheets..............................................................295 Referencing other sheets................................................................299 Referencing other documents.........................................................301 Hyperlinks and URLs......................................................................303 Linking to external data..................................................................307 Linking to registered data sources.................................................312 Embedding spreadsheets................................................................316

Chapter 11

Sharing and Reviewing Documents.........................................322 Sharing documents (collaboration).................................................323 Recording changes..........................................................................326 Adding comments to changes.........................................................329 Adding other comments..................................................................330 Reviewing changes.........................................................................332 Merging documents........................................................................335 Comparing documents....................................................................337 Saving versions...............................................................................337

6OpenOffice.org 3.x Calc Guide

Chapter 12

Calc Macros...........................................................................340 Using the macro recorder...............................................................341 Write your own functions................................................................345 Accessing cells directly...................................................................353

Chapter 13

Calc as a Simple Database......................................................357 Associating a range with a name....................................................359 Calc functions similar to database functions..................................375 Database-specific functions............................................................387

Chapter 14

Setting up and Customizing Calc...........................................389 Choosing options that affect all of OOo..........................................390 Choosing options for loading and saving documents......................396 Choosing options for Calc...............................................................400 Controlling Calc's AutoCorrect functions.......................................409 Customizing the user interface.......................................................410 Adding functionality with extensions..............................................420

Appendix A

Keyboard Shortcuts................................................................422 Navigation and selection shortcuts.................................................423 Function and arrow key shortcuts..................................................425 Cell formatting shortcuts................................................................426 DataPilot shortcuts.........................................................................427

OpenOffice.org 3.x Calc Guide7

Appendix B

Description of Functions........................................................428 Functions available in Calc.............................................................429 Mathematical functions..................................................................430 Financial analysis functions............................................................435 Statistical analysis functions...........................................................449 Date and time functions..................................................................458 Logical functions.............................................................................462 Informational functions...................................................................463 Database functions.........................................................................466 Array functions...............................................................................468 Spreadsheet functions....................................................................470 Text functions..................................................................................475 Add-in functions..............................................................................479

Appendix C

Calc Error Codes.....................................................................484 Introduction to Calc error codes.....................................................485 Error codes displayed within cells..................................................486 General error codes........................................................................487

8OpenOffice.org 3.x Calc Guide

Chapter 1 Introducing Calc

Using Spreadsheets in OpenOffice.org

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; two new types of charts - Bubble Charts and Filled Net Charts - have been introduced in OOo 3.2 •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.

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.x, each sheet can have a maximum of

65,536 rows and a maximum of 1024 columns, for a total of over 67

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