Data Manipulation and Analysis with Excel

1
Data Manipulation and Analysis with Excel
2
Basic Data Manipulation

• Excel has numerous data manipulation and analysis
  tools
• Data sorting
• GroceryData.xls available in Downloads section
• Data filtering
• Pivot tables and pivot charts
• Data tables and Goal Seek
• Data Analysis tool-pak

3
Sorting DataDataSort...

• Excel allows up to 3 sort fields
• Can sort ascending or descending for each sort
  field
• Excel will try to recognize whether or not you
  have field headings (facilitates sorting)
• Sorts physically move data in your spreadsheet
• Be careful if you have formulas that depend on
  current order of rows in range to be sorted
• Undo will unsort your data to the order just
  prior to the sort
• There are better ways to count data meeting
  conditions than to use sorting and manual
  counting
• filtering
• COUNTIF() function

4
Filtering DataDataFilterAutoFilter

• Restrict which rows you view in a range of data
• AutoFilter creates drop down selectors in each
  field
• Filtered ranges show in blue
• actual row s
• Data is NOT deleted, just hidden
• Advanced filter allows you to build complex
  logical conditions for filtering
• uses Criterion Ranges

5
Analyzing Data with Pivot Tables Pivot Charts

• Pivot tables are an extremely powerful and
  flexible method for exploring data arranged in a
  tabular form
• Allows you to quickly do counts, summations,
  averages, minimum, maximums, etc. of data by
  various grouping and subgrouping of category
  fields
• Lets analyze some data from a call center
• Tutorial and data file is available in Downloads
  section of course web
• ExcelPivotTutorial-MIS200.doc
• CallCenterPivot-MIS200.xls

6
Data Validation

• Maximize data integrity
• Simple user interface development
• Build validation rules at cell level
• Rules can be based on values in SAME sheet
• Stop, Warning, Information
• EXAMPLE OBLog-MIS200.xls

7
Conditional FormattingFormat cells based on
values or formulas
We used cell pattern color to show negative and
positive regions in the break even analysis.
8
Data Tables and Goal Seek

• We used Data Tables last time in our Break Even
  case to see impact on profit of changes in
  response rate
• see GreatThreads-HowToBuild.ppt and
  GreatThreads-Basic.xls
• that was a one-way Data Table
• could use two-way to see joint effect of changes
  in two variables (e.g. response rate and variable
  cost)
• Then we used Goal Seek to find the exact break
  even point (see same file listed above)

9
Worksheet Controls

• You can facilitate data entry with Worksheet
  Controls
• Available from the Forms toolbar
• List boxes, check boxes, spinners, scroll bars,
  etc.
• Easy way to create user interface in your
  spreadsheet with no programming
• Example MortgageLoan-WorksheetControls.xls

10
Descriptive Statistics in Excel

• AVERAGE(), STDEV(), MEDIAN()
• FREQUENCY()
• PERCENTILE()
• RANK(), PERCENTRANK()
• MIN(), MAX()

• Data Analysis Tool-Pak

Youll learn more about using these in QMM 250.
11
What is VBA?

• Visual Basic for Applications
• Common programming component shared among MS
  Office applications
• The Visual Basic programming language was the
  first tool that made it easy to develops
  Windows applications
• Allows creation and manipulation of application
  objects such as spreadsheets, databases,
  documents, mail, projects
• Facilitates application interoperability

12
What does VBA allow you to do?

• Create applications based on MS Office products
• Manipulate objects in object model of various
  products
• Customize way product appears to others
• Leverage capabilities of pre-built objects in
  various applications
• IT ALLOWS YOU TO DO STUFF IN AUTOMATED FASHION

Limited only by your imagination
13
The Excel Object Model

• All the things in Excel are objects that can be
  manipulated
• we can set object properties to control how
  things look
• we can use object methods to control how things
  behave
• We need to learn how to get at the objects and
  use them as we create applications
• All VBA compliant applications expose their
  object model for our use

14
Recording Macros

• Macros are another word for VBA subroutines
• macros prior to VBA were essentially keystroke
  recordings and specialized macro commands
• Useful for automating repetitive tasks
• Useful for learning VBA
• Really useful for learning details of object
  model
• A way to start to learn to write computer
  programs
• To use
• start recorder (Tools-Macro-Record New Macro)
• do stuff
• stop recorder (Push the stop button)
• go look/edit code in Visual Basic Editor
• Cant record logic
• Lets look at some simple examples
  Recording.xls
• Well do more with macros and VBA after the
  midterm
• for the midterm, just the info on these slides
  will be covered

15
Preparing for the Midterm

• By far the most useful way to spend your time is
  to
• READ AND DO ALL OF THE TUTORIALS THAT WERE
  ASSIGNED
• THIS IS ESPECIALLY TRUE FOR EXCEL SINCE ITS FAR
  LESS INTUITIVE THAN WORD
• MAKE SURE YOU READ THE INTRO TO COMPUTING
  SECTIONS AS WELL
• Make sure you bring a Scantron 882-E form.
• Now, go to the lab and do Tutorials and/or work
  on HW 3.