1 / 77

7.1 Introduction to Spreadsheets

7.1 Introduction to Spreadsheets. The o rigin of e lectronic s preadsheet Spreadsheet is a jargon used in accounting. Accountants presented business data such as item quantity, cost and revenue in rows and columns on a large sheet of paper.

raymondp
Download Presentation

7.1 Introduction to Spreadsheets

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. 7.1 Introduction to Spreadsheets • The origin of electronic spreadsheet • Spreadsheet is a jargon used in accounting. • Accountants presented business data such as item quantity, cost and revenue in rows and columns on a large sheet of paper. • The first electronic spreadsheet software VisiCalc developed in 1979: The first electronic spreadsheet - VisiCalc

  2. 7.1 Introduction to Spreadsheets • The functions of an electronic spreadsheet • Spreadsheets allows users to input and organize data, create charts and perform data analysis. • Examples of spreadsheet software: Microsoft Excel, Corel Quattro Pro, OpenOffice.org Calc Microsoft Excel 2007 Corel Quattro Pro

  3. 7.1 Introduction to Spreadsheets • The functions of an electronic spreadsheet • Data in a spreadsheet is presented in rows and columns. • Users can carry out many mathematical operations and advanced data analysis using hundreds of predefined functions. • Usage: accounting reports, financial models, inventory reports, quotation and personal mail lists. A chart is created by using an electronic spreadsheet Chart

  4. 7.1 Introduction to Spreadsheets The functions of an electronic spreadsheet Data in a spreadsheet is presented in rows and columns Users can carry out many mathematical operations and advanced data analysis using hundreds of predefined functions. Usage: accounting reports, financial models, inventory reports, quotation and personal mail lists. A chart is created by using an electronic spreadsheet Chart

  5. 7.2 Basic Operations of Microsoft Excel 2007 • Working environment of Microsoft Excel 2007 • A spreadsheet file can contain many worksheets and the data on each of them can be interdependent. • Basic storage unit is called cell, which is arranged into rows and columns. • Each row is identified by a row number (from 1 to 1,048,576) while each column is identified by a letter (from A to XFD). • Each cell has a unique cell address formed by a column letter followed by a row number, e.g. A1 is the cell address of the top left corner of the spreadsheet

  6. 7.2 Basic Operations of Microsoft Excel 2007 • Working environment of Microsoft Excel 2007 Office button Display menu for file manipulations and setting options Column Ribbon Display tabs and their command buttons in groups Name box Show the cell address or defined name of the active cell Formula Bar Input or edit the content in the active cell Quick Access Toolbar Show frequently used command buttons Active cell The selected cell for editing Zoom Slider Zoom in or out of the active worksheet Active worksheet The worksheet that is currently in use Worksheet Row Working environment of Microsoft Excel 2007

  7. 7.2 Basic Operations of Microsoft Excel 2007 • Data entry and data format • To enter data or formula into a cell: • Select a cell. • Enter data into the cell. • Press ‘Enter’ to complete the input. • Three ways to modify the data in an active cell: • Press the function key ‘F2’ and then change the cell content in the cell. • Double-click the cell and then change the cell content in the cell. • Click the Formula Bar and then change the cell content in the Formula Bar.

  8. 7.2 Basic Operations of Microsoft Excel 2007 • Data entry and data format • Microsoft Excel formats the input data to one of the predefined cell formats such as number, text or percentage. • Users can change the cell format through the command buttons in the ‘Number’ group. Change cell formats in the ‘Format Cells’ dialogue box. Data presented in different formats

  9. 7.2 Basic Operations of Microsoft Excel 2007 • Data entry and data format • Microsoft Excel may not display the input data in the ways you expect. • Student ID ‘00812’ may be displayed as ‘812’. • Class name ‘4E2’ may be displayed as ‘4.00E+02’. • It is better to define the data format of the cells before the data is entered.

  10. 7.2 Basic Operations of Microsoft Excel 2007 • Data entry and data format 3. The data is displayed exactly as what a user has entered. 1. Select the cells to be formatted as text. 2. Select the option ‘Text’ from the ‘Number Format’ menu. Define the cell format before entering data.

  11. 7.2 Basic Operations of Microsoft Excel 2007 • Data entry and data format Merge and centre Merge or split selected cells Number format Contain predefined number format Bottom align Middle align Top align Font size Font Percentage style Bold Decrease decimal Italic Underline Increase decimal Align text to right Borders Centre text Comma style Background colour Align text to left Text colour Formatting commands on the ‘Home’ tab

  12. 7.2 Basic Operations of Microsoft Excel 2007 • AutoFill • Allow user to create a series of values based on the value of one or more selected cells adjacent to each other. • Fill up cells adjacent to the source in two different ways • Copy and paste • Extending the series

  13. 7.2 Basic Operations of Microsoft Excel 2007 • AutoFill • Copy and paste • Microsoft Excel tries to determine the pattern of data in the source. • If Microsoft Excel fails to identify the pattern, it simply fills up the adjacent cells repeatedly with the value in the source. • Examples: • the source which is a single cell • textual data without any number or not related to time and date

  14. 7.2 Basic Operations of Microsoft Excel 2007 • AutoFill • Extending the series • If Microsoft Excel successfully determines the pattern of data in the source, the adjacent cells are filled with values by extending the series. • AutoFill can be used to create number sequences, time lists, day lists, week lists, month lists and regular text patterns. Use AutoFill to fill a series of cell.

  15. 7.2 Basic Operations of Microsoft Excel 2007 AutoFill Extending the series If Microsoft Excel successfully determines the pattern of data in the source, the adjacent cells are filled with values by extending the series. AutoFill can be used to create number sequences, time lists, day lists, week lists, month lists and regular text patterns. Use AutoFill to fill a series of cell.

  16. 7.3 Formula and Cell Reference • Formula • An expression stored in a cell that performs calculations on the data in a worksheet • Begins with an equal sign (=) and is followed by a series of constants • References and formulae are connected by operators. Parentheses in a formula works in a similar way as those in arithmetic expressions.

  17. 7.3 Formula and Cell Reference • Elements of a formula Elements of a formula

  18. 7.3 Formula and Cell Reference • Elements of a formula Elements of a formula

  19. 7.3 Formula and Cell Reference • Elements of a formula Simple examples of formula

  20. 7.3 Formula and Cell Reference • Cell reference • A basic element in an Microsoft Excel formula. • It is used when we need to perform calculations involving the information of cells on a worksheet. • In a formula, cell references can be used in a combination of numbers, functions and cell references. • Types of cell references • Relative cell reference • Absolute cell reference • Mixed cell reference

  21. 7.3 Formula and Cell Reference • Cell reference Formula: = B2*(1-B4) Formula: = B5-B1 Formula: = B6/B1 The data in row 7 is defined in the ‘Percent’ style. The profit of selling an item under different discount percentages.

  22. 7.3 Formula and Cell Reference • Relative cell reference • It is formed by a column letter and a row number, e.g. A1 and B5. • When a cell containing a relative cell reference is copied and pasted into another cell, Microsoft Excel calculates the relative position of this cell and the location of the target cell. The cell reference in the target cell is changed accordingly.

  23. 7.3 Formula and Cell Reference • Relative cell reference Formula: = A1+A2 Formula: = A3+A4 Formula: = B3+B4 The cell references change accordingly when a cell containing relative reference is copied and pasted into other cells.

  24. 7.3 Formula and Cell Reference • Absolute cell reference • It identifies a specific cell by adding a symbol ‘$’ in front of the column letter and the row number, e.g. $A$3 and $F$10. • When a formula is copied or moved to other cells, the absolute cell reference in the formula remains unchanged.

  25. 7.3 Formula and Cell Reference • Absolute cell reference Formula: = $A$1+$A$2 Formula: = $A$1+$A$2 Formula: = $A$1+$A$2 The absolute cell references remain unchanged after the formula is copied to other cells.

  26. 7.3 Formula and Cell Reference • Mixed cell reference • It has a ‘$’ either before the column letter or the row number, e.g. $A5 and A$1. • When a formula is copied to other cells, the column letter or the row number with a ‘$’ remains unchanged, while the other part of the reference without the ‘$’ changes in the same way as a relative cell reference.

  27. 7.3 Formula and Cell Reference • Mixed cell reference Formula: = $A1+A$2 Formula: = $A3+A$2 Formula: = $A3+B$2 When a formula contains a mixed cell reference is copied to other cells, the part of the reference with a ‘$’ remains unchanged while the part without the ‘$’ changes.

  28. 7.3 Formula and Cell Reference Mixed cell reference Formula: = $A1+A$2 Formula: = $A3+A$2 Formula: = $A3+B$2 When a formula contains a mixed cell reference is copied to other cells, the part of the reference with a ‘$’ remains unchanged while the part without the ‘$’ changes.

  29. 7.4 Functions • Statistical function • Conduct data analysis on large volume of data. • Useful for finding out the tendency, distribution and characteristics of the data to make the analysis much easier Descriptions of commonly used statistical functions

  30. 7.4 Functions • Statistical function Descriptions of commonly used statistical functions

  31. 7.4 Functions • Statistical function Descriptions of commonly used statistical functions

  32. 7.4 Functions • Statistical function Example of applications of statistical functions

  33. 7.4 Functions • Mathematical functions • Tools for manipulating numbers in a spreadsheet • e.g. They can be used to sum up a series of numbers and round up numbers. Descriptions of commonly used mathematical functions

  34. 7.4 Functions • Mathematical functions Descriptions of commonly used mathematical functions

  35. 7.4 Functions • Mathematical functions Example of applications of mathematical functions

  36. 7.4 Functions • Logical functions • Used in building conditional formula. • Logical functions such as the ‘AND’, ‘OR’ and ‘NOT’ functions are often used together with the ‘IF’ function. Descriptions of commonly used logical functions

  37. 7.4 Functions • Logical functions Descriptions of commonly used logical functions

  38. 7.4 Functions • Logical functions Descriptions of commonly used logical functions

  39. 7.4 Functions • Text, date and time functions • Manage data other than numbers such as text, date and time. • Examples: • Convert one data type to another such as from number to text. • Extract information from the data. Descriptions of commonly used text functions

  40. 7.4 Functions • Text, date and time functions Descriptions of commonly used text functions

  41. 7.4 Functions • Text, date and time functions Descriptions of commonly used text functions

  42. 7.4 Functions • Text, date and time functions Descriptions of commonly used date and time functions

  43. 7.4 Functions • Text, date and time functions Descriptions of commonly used date and time functions

  44. 7.4 Functions • Text, date and time functions Descriptions of commonly used date and time functions

  45. 7.4 Functions • Text, date and time functions The ‘Date’ function is used in cell B1. The ‘Time’ function is used in cell E3 Example of applications of the data and time functions

  46. 7.4 Functions Text, date and time functions The ‘Date’ function is used in cell B1. The ‘Time’ function is used in cell E3 Example of applications of the data and time functions

  47. 7.5 Creating Charts • Chart • Use to visualize the relationship between data. • Important tool for data analysis and presentation. • Selection of chart type • Each type of chart has advantage over the other types to present certain kind of information. • Should choose the chart type carefully.

  48. 7.5 Creating Charts A pie chart A line chart A column chart Examples of different types of charts

  49. 7.5 Creating Charts • Selection of chart type Purposes of different types of charts

  50. 7.5 Creating Charts • Creating a simple chart • The first task to create a chart is to prepare the data series, known as the chart data. • The following shows the guidelines for preparing chart data: • Arrange each set of chart data either in rows or columns. • Mark each data series with a title whenever possible. • Do not include any blank cells between data. • After that, users can start to create a chart.

More Related