1 / 17

CPSC 130 Computing With Spreadsheets

Explore list and data management using Excel, covering the data hierarchy, manipulating lists, filtering data, and working with pivot tables for summarization. Understand database issues and when to opt for a database management system.

carmensmith
Download Presentation

CPSC 130 Computing With 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. CPSC 130Computing With Spreadsheets List Management and Macros Week 7

  2. Outline • List and Data Management • Database Issues • In closing … Brenda Vander Linden and Aaron Armstrong

  3. List and Data Management • Recall the data hierarchy: • fields • records • files • Working with Excel lists (aka files) • Converting data to information Brenda Vander Linden and Aaron Armstrong

  4. A Data Hierarchy • Bit • Character, string and number • Field • Record • File • Database Brenda Vander Linden and Aaron Armstrong

  5. Information from Lists • Manipulating lists • Filtering lists • Similar to database query features • Summarizing lists with pivot tables • Similar to database report functions Brenda Vander Linden and Aaron Armstrong

  6. Feature Creep • Now we have facilities for sorting, input, etc., as well as a means to deal with multiple worksheets. • We get something approaching a database management system (DBMS), without having a one. Brenda Vander Linden and Aaron Armstrong

  7. Database Issues • Database - a collection of data that is: • persistent • too large to fit into main memory • Database Management System - a system that maintains and provides multi-user access to a database, and whose operation is: • efficient • convenient • safe Brenda Vander Linden and Aaron Armstrong

  8. Database Issues, cont. • When to use databases (and not Excel): • Large data sets • Formed input and output • Arbitrary and complicated queries • Multiple users • Security • CPSC-135 covers databases Brenda Vander Linden and Aaron Armstrong

  9. “All our knowledge brings us nearer to our ignorance, All our ignorance brings us nearer to death, But nearness to death, no nearer to God. Where is the Life we have lost in living? Where is the wisdom we have lost in knowledge? Where is the knowledge we have lost in information? The cycles of Heaven in twenty centuries Bring us farther from God and nearer to the Dust. [98] T. S. Eliot, Choruses From ‘The Rock’, Selected Poems (New York: Harcourt, Brace & World, 1964), p. 107. Brenda Vander Linden and Aaron Armstrong

  10. What is a macro? • A set of instructions telling Excel what commands to execute • Written in Visual Basic for Applications (VBA) • Recorded with the Macro Recorder Brenda Vander Linden and Aaron Armstrong

  11. Recording a macro • NameAndCourse macro • Shortcut key • Record macro • Stop recording Brenda Vander Linden and Aaron Armstrong

  12. Elements of a Macro • Sub/End Sub • Comments • Variables • Properties • With Statement Brenda Vander Linden and Aaron Armstrong

  13. Step Into • Executes one command of the macro • Other debugging commands available Brenda Vander Linden and Aaron Armstrong

  14. Personal Macro workbook • Opened every time Excel starts • Only on your machine • Always accessible Brenda Vander Linden and Aaron Armstrong

  15. User interaction functions • InputBox • MsgBox • Example: modifying another macro Brenda Vander Linden and Aaron Armstrong

  16. The If Statement Sub Pepsi() ActiveCell = _ InputBox("Enter something interesting") If ActiveCell = "Britney" Then MsgBox ("Try Pepsi") Else MsgBox ("Try Britney") End If End Sub Brenda Vander Linden and Aaron Armstrong

  17. The Do Statement Sub Redden() Range("A4").Select Do Until ActiveCell = "" Selection.Font.ColorIndex = 3 ActiveCell.Offset(1, 0).Select Loop End Sub Brenda Vander Linden and Aaron Armstrong

More Related