1 / 37

Mastering Spreadsheets: Formulas, Charts & Data Management

Explore the power of spreadsheets for data manipulation, visualization, and analysis. Learn to create formulas, charts, and manage cell references effectively to enhance productivity and decision-making. Dive into event-driven programming to automate processes and optimize workflows.

sherrylc
Download Presentation

Mastering Spreadsheets: Formulas, Charts & Data Management

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 21 Using & Managing Data:Spreadsheets (S&G, §§11.1–11.2) CS 100 - Lecture 21

  2. Some Important Applications of Computers • Spreadsheets • Databases • Symbolic & numeric computation • Networks • Artificial Intelligence CS 100 - Lecture 21

  3. Relation to Algorithms • Algorithmic problem solving is used in these applications • Algorithmic problem solving uses these applications CS 100 - Lecture 21

  4. Spreadsheet • Visual system for representing and manipulating tabular data • Often used for: • budgets & other financial data • grades • Useful for keeping dependant data consistent • Examples: Visicalc, Excel, Lotus 1-2-3 CS 100 - Lecture 21

  5. Column Cells Row Cursor Example Spreadsheet CS 100 - Lecture 21

  6. Cells • Each cell has a “name” • e.g. “D2” is the cell in column D and row 2 • Cells can contain character data • e.g., names, column headings • Cells can contain numeric data in various formats • e.g., integer, real, currency, date, time • Cells can contain formulas • e.g., “D2*E2” CS 100 - Lecture 21

  7. Relative & Fixed Cell References • A name of the form “D2” is a relative reference to the cell in column D and row 2 • A name of the form “$D$2” is a fixed reference to the cell in column D and row 2 • When a formula is copied into another cell: • the fixed references refer to the same cells as they originally did • the relative references refer to cells in the same relative positions CS 100 - Lecture 21

  8. Example • Suppose in G2 we have the formula:D2 * (1 + $C$8 / 100) * E2 • When we copy it to G3 it will be:D3 * (1 + $C$8 / 100) * E3 • When we copy it to G4 it will be:D4 * (1 + $C$8 / 100) * E4 • And so forth CS 100 - Lecture 21

  9. Run Excel Spreadsheet Example CS 100 - Lecture 21

  10. Beginning Spreadsheet CS 100 - Lecture 21

  11. Formula Entered in F2 CS 100 - Lecture 21

  12. Result of Formula in F2 CS 100 - Lecture 21

  13. Formula Copied into Rest of Column CS 100 - Lecture 21

  14. Result of Formulas in Column F CS 100 - Lecture 21

  15. Example Pie Chart of Column F CS 100 - Lecture 21

  16. Scatter Chart of Columns D vs. C CS 100 - Lecture 21

  17. Compute Sum of Column F CS 100 - Lecture 21

  18. Resulting Sum CS 100 - Lecture 21

  19. Formula for Projected Base Pay Increase CS 100 - Lecture 21

  20. Resulting New Pay CS 100 - Lecture 21

  21. Formula Relativized to Row 3 CS 100 - Lecture 21

  22. Formula Relativized to Row 4 CS 100 - Lecture 21

  23. Change Format of Column G CS 100 - Lecture 21

  24. Resulting Reformated Column G CS 100 - Lecture 21

  25. Insert Column for Merit Raises CS 100 - Lecture 21

  26. New Formula to Include Merit Raises CS 100 - Lecture 21

  27. Resulting Projected New Total CS 100 - Lecture 21

  28. Bar Chart of Old and New Pay CS 100 - Lecture 21

  29. Goal Seeking: Adjust Base Rate so Total = $12,000 CS 100 - Lecture 21

  30. Calculated Base Rate to Meet Goal CS 100 - Lecture 21

  31. Computer Science Issues CS 100 - Lecture 21

  32. Update Strategies • Reevaluate every cell • Reevaluate only those cells containing formulas • works on small spreadsheets • Reevaluate only those cells that depend on the changed cell • problem: indirect dependencies (ripple effect) CS 100 - Lecture 21

  33. Indirect Dependency CS 100 - Lecture 21

  34. Result CS 100 - Lecture 21

  35. Problem of Indirect Dependencies • Suppose change D2 • Could look for all cells with a formula involving D2, and reevaluate those • But suppose F9 depends on F2, and F2 depends on D2 • Bad solution: • check all cells & update those directly dependant on D2 • keep a list of them • check all cells & update those directly dependent on those in list • keep a list of these, and return to (3) • continue until there are no changes CS 100 - Lecture 21

  36. Better Solution • Keep with each cell a list of all other cells directly dependant on it • Update this list whenever a formula is changed • Whenever a cell’s value is changed: • reevaluate all the cells that depend directly on it • reevaluate all the cells that depend directly on those cells • and so forth CS 100 - Lecture 21

  37. Event-Driven Programming • Each cell contains either: • data • or a formula, which is a little program • these formulas are interpreted directly (not compiled into machine code) • Whenever a cell is modified: • it creates an event • which may trigger other events • must end (no circular dependencies) • There is no iteration except that provided through the built-in functions & tools CS 100 - Lecture 21

More Related