1 / 29

Lecture 7 Desktop Publishing IV – Spreadsheet Software

Lecture 7 Desktop Publishing IV – Spreadsheet Software. Introduction to Information Technology. Dr. Ken Tsang 曾镜涛 Email: kentsang@uic.edu.hk http://www.uic.edu.hk/~kentsang/IT/IT3.htm Room E408 R9. With thanks to Dr. A. Zhang, Dr. Haipeng Guo, and Dr. David Chen. Outline.

temple
Télécharger la présentation

Lecture 7 Desktop Publishing IV – Spreadsheet Software

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Lecture 7Desktop Publishing IV – Spreadsheet Software Introduction to Information Technology Dr. Ken Tsang 曾镜涛 Email:kentsang@uic.edu.hk http://www.uic.edu.hk/~kentsang/IT/IT3.htm Room E408 R9 With thanks to Dr. A. Zhang, Dr. Haipeng Guo, and Dr. David Chen

  2. Outline • What is Spreadsheet Software? • OpenOffice Calc Basics • Performing Calculations • Creating Chart

  3. Spreadsheet Software • Another widely used application software • Allows users to organize data in rows and columns and perform calculations on the data • Rows and columns collectively are called worksheets • Features from word processing • Spreadsheet organization • Up to 255 worksheets • 256 columns, and 65,536 rows • Letters identify columns (A … IV) • Numbers identify rows (1 …65536)

  4. Spreadsheet Software • Cells • A cell is the intersection of a column and a row • 256 * 65,536 cells • Identifies cells by the column and row, e.g. B6 • Contains three types of data: labels, values, and formulas • Calculations • Value – a number used in a calculation • Formula – performs calculation to generate values • Function – predefined formula

  5. Spreadsheet Software • Recalculation • One of the powerful features • Making manual changes can be time-consuming and may result in new errors • Making changes in an electronic worksheet is much easier and faster, and more accurate • Charting • Depicts data in graphical form • Line chart, shows a trend during a period of time • Column chart, displays bars of various lengths to show the relationship of data • Pie chart,shows the relationship of parts to a whole

  6. OpenOffice Calc • Tool to create professional spreadsheets and charts http://www.openoffice.org/product/calc.html • Calc window • Many elements - Title bar, Menu bar, Tool bars, Formula bar, Worksheets, and Status bar • Similar to OpenOffice Writer

  7. Calc Window • Formula bar • Name box • Function Wizard, Sum, Function • Cell entries (data) • View > Formula bar • Worksheets • Workbook • 3 worksheets by default • Cell address: A1, B6, E10, ...

  8. Calc Window • Status bar • View > Status bar • Statistics

  9. Working with Worksheets • Start a workbook • Click New icon or choose File > New • Enter text • Simply click a cell and type • Enter numbers • As values – right-aligned • As labels – left-aligned • Automatic increase • Drag the black square in the bottom-right corner of a cell

  10. Working with Worksheets • Formatting numbers • Add commas to separate thousands • Specify number of decimal places • Place a dollar sign ($) in front of the number • Display as a percent • Display as Date, time • Several other options • Format > Cells > Number

  11. Working with Worksheets • Selecting cells • F8 function key click the up-left first, press F8, click the lower-right • mouse to drag

  12. Working with Worksheets • Moving quickly between cells • Name Box • F5 • Resizing rows and columns • Dragging the side line • Format > Row/Column > Height/Width • Adding and renaming worksheets • Worksheet tabs • Add – Insert > Worksheet • Rename – right-click the tab and select Rename

  13. Performing Calculation • A distinguishing feature • Otherwise it is not more than a large table • Formula calculations • Must begin with equal sign “=” • Performs calculations and displays the result • Includes cell addresses • Visible in the cell entries of the formula bar after execution • Point mode • Enter a formula without typing cell addresses • By clicking cells or using arrow keys

  14. Formula Calculation • Example • Calculating the sub total for a number of textbooks. • The formula multiplies the quantity and price of each textbook and adds them together • Formula is shown in the formula bar after

  15. Performing Calculation • Cell addressing • Identified by the column and row, e.g. B6 • Records cell addresses in formulas in three ways • Relative referencing • Calling cells by just their column and row labels • Cell addresses will be changed when copy them e.g. C1 "=(A1+B1)"  C2 "=(A2+B2)" • Absolute referencing • Accomplished by placing dollar signs "$" e.g. "=($A$1+$B$1)" • Mixed referencing • Only the row or column is fixed. e.g. "=(A$1+$B2)"

  16. Performing Calculation • Reference operators refer to a cell or group of cells • Range operator “:” • TWO cell addresses separated by a colon • Refers to ALL the cells included in the reference e.g. “A1:C3” includes A1, A2, A3, B1, B2, B3, C1, C2, and C3. • Union operator “,” • Two or more cells separated by a comma • Refers ONLY to the cells (rather a range) • e.g. “A7,B8,C9” includes only cells A7, B8, and C9

  17. Performing Calculation • Linking worksheets • Use the value from a cell in another worksheet • Format: "sheet_name!cell_address" e.g.: "=A1+Sheet2.A2"

  18. Performing Calculation – Functions • Functions • Allow you to quickly perform calculations • More efficient way than typing formula • formula: "=D1+D2+D3+D4+D5+D6+D7+D8+D9+D10" • function: "=SUM(D1:D10)" • Format for using functions • Use an equal sign to begin a formula • Specify the function name • Enclose arguments within parentheses • Use a reference operator (a comma “,” or colon “:”) to separate arguments

  19. Functions

  20. Performing Calculation –Functions • The Sum icon • Automatically adds a column of numbers • Highlight all cells above current cell and add • For empty column, Sum adds the row values • Recalculations • Automatically recalculate when changing cell entries • Tools > Cell Content> Recalculate

  21. Performing Calculation –Sort • Sort on one column • Sort Ascending button • Sort Descending button • Sort on multiple columns • Highlight all columns • Data > Sort • Select 1st column (key word) in Sort By field • Select 2nd and 3rd column in Then By field. • Choose Header row or No header row box

  22. Creating Charts • Charting – Another important feature • Represent data in a visual format • Often makes it easier to see the relationship • Three popular types: • Line chart – shows a trend during a period of time • Column chart – displays bars of various lengths • Pie chart – shows the relationship of parts to a whole • Chart will automatically update if data changes • Chart Wizard • Easy way to create charts • Highlight all the cells and click Chart Wizard button

  23. Chart Wizard • Step 1: Chart Type • Choose the Chart type and the subtype if necessary from the first dialog box of Chart Wizard • Click Next after selection

  24. Chart Wizard • Step 2: Data Range • Select the data range (if different from the area highlighted) by clicking the icon on the right • You can choose to chart the data by columns or rows • You can also choose to make the first row or column to be the label or not • Press Next after you select

  25. Chart Wizard • Step 3: Data Series • Click the data series and modify the name and X or YValues in the data range • If you want to change select different range for name or categories, click the icon on the right to select • Press Next to move to the next step

  26. Chart Wizard • Step 4: Chart Elements • Enter the title and subtitle of the chart, and that of the X and Y axes • Other options for the grid lines and legend • Press Finish after selection to create the chart

  27. Editing Charts • Editing a chart • Right click the chart, select Edit in the pop-down menu, then a different menu will be generated for chart editing • If you want to edit the format of the chart, you can select any relating selection – Format > Title, Axis, Grid, Legend, Chart wall, Chart area, Chart types, Data ranges, etc. • Select an object in the chart, you can edit its properties -- Format > Object Properties • Note that any part of a chart you want to format can be selected

  28. Summary • Spreadsheet software • Allows users to organize data in rows and columns and perform calculations on the data • OpenOffice Calc • Windows • Worksheets • Performing calculations • Creating charts

More Related