1 / 26

44221: Information Systems

44221: Information Systems. Lecture 7 (Week 9) Organising Data & Presenting Information By Ian Perry http://itsy.co.uk/ac/0607/Sem1/44221_IS/. Why use Multiple Worksheets?. ‘Old’ Spreadsheet Software enabled the user to build two-dimensional models of numerical data on a single worksheet.

efuru
Download Presentation

44221: Information Systems

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. 44221: Information Systems Lecture 7 (Week 9)Organising Data & Presenting Information By Ian Perry http://itsy.co.uk/ac/0607/Sem1/44221_IS/

  2. Why use Multiple Worksheets? • ‘Old’ Spreadsheet Software • enabled the user to build two-dimensional models of numerical data on a single worksheet. • these two-dimensional models could be quite large (typically 256 columns by 8192 rows), and finding you way around could be difficult. • Modern Spreadsheet software has introduced the idea of the ‘Workbook’, containing a number of ‘Worksheets’, which: • enables three-dimensional numerical data models to be constructed. • chich can ease the organisation of, and navigation around, large numerical data models.

  3. 3 Worksheets by default, but can easily add more. An Excel ‘Workbook’ Ü 256 Columns; ‘A’ to ‘IV’ Þ Ý Rows; ‘1’ to ‘65536’ ß

  4. Pull-down the ‘Insert’ Menu • Select ‘Worksheet’ To add a New Worksheet

  5. ‘Right-Click’ on a ‘Sheet’ Tab • Select ‘Rename’ • Type in the New Name To ‘Rename’ your Worksheets!

  6. Now have 5 Worksheets Renamed as: Qtr1, Qtr2, Qtr3, Qtr4, & YearSum. The ‘Qtr1’ Worksheet Note. The other 3 Quarters have exactly same layout, but different Sales & Profit data.

  7. Formula: =Qtr1!B8 Reference the Sales Total for Qtr1

  8. Formula: =Qtr1!C8 Reference the Profit Total for Qtr1 Note: Easiest way to do this is to copy the previous formula from B4 to C4.

  9. } Repeat for other 3 Quarters. Copy for ‘Profit’ Formula: =sum(B4:B7) Complete the Yearly Summary

  10. ‘Click-and-Hold’ on the Worksheet you wish to move, ‘Drag’ to new position, then let go. Re-order the Worksheets

  11. Charts • Why use Charts? • can reduce the complexity of a situation. • can make it easier to depict trends. • can make it easy to highlight significant data. • Can be misleading! • Something ‘pretty’ on screen can be pretty meaningless! • Need to use the ‘correct’ Chart type; i.e. for a specific purpose.

  12. Chart Type & Purpose? • Bar/Column Charts • show values of a single continuous variable for multiple separate entities, or for one variable sampled at discreet intervals. • Or for multiple continuous variables, so that bar heights can be compared. • Composite Bar Charts • each bar incorporates several data series so that it is possible tosee how the components contribute to the whole.

  13. Chart Type & Purpose? • Pie Charts • show the relative distribution of a single series of data among the parts that make up the whole. • The number of segments should not exceed 10 - in which case a Bar Chart would be more suitable. • Line Charts • show how two (or more?) continuous variables are related to each other. • Especially where these variables change over time.

  14. Chart Tips! • Don’t have too much data, or data of varying scales, on the same Chart. • Use 3D Charts with care as one data series can easily obscure another. • Make full use of labels and additional text to ‘explain’ your Charts. • Be careful when selecting scales - as you can easily exaggerate minor variations, or hide major ones.

  15. Slightly modified ‘Qtrs&Sum.xls’

  16. What Chart type should we use? • Look carefully at the data and decide how they might best be represented. • Sales for each of four Quarters: • a single continuous variable for multiple separate entities. • i.e. a Bar/Column Chart. • Sales is made up of Costs and Profit: • each bar incorporating several data series so that it is possible to see how the components contribute to the whole. • i.e. a Composite Bar Chart.

  17. Press the ‘Chart Wizard’ button. Highlight the data. Chart 1 – Sales by Quarter

  18. Select Chart Type. Press Next. Press Next. Chart Wizard – Steps 1 & 2

  19. Enter suitable titles for the Chart, X Axis, and Y Axis. Press Next. Press Finish. Chart Wizard – Steps 3 & 4

  20. The Completed Chart

  21. Press the ‘Chart Wizard’ button. Highlight the data. Chart 2 – Profit & Costs Note: Select the first column of data, then hold down the ‘Ctrl’ key whist selecting the other two.

  22. Select Chart Type. Press Next. Press Next. Chart Wizard – Steps 1 & 2

  23. Enter suitable titles for the Chart, X Axis, and Y Axis. Press Next. Press Finish. Chart Wizard – Steps 3 & 4

  24. The Completed Chart

  25. Complete this spreadsheet model; by adding a new Worksheet (with Formulae referencing other Worksheets), then add some Formulae to summarise the data, and finally create two Charts of the Summary data. This Week’s Workshop

  26. The Completed Spreadsheet Model

More Related