Lecture 4
Sponsored Links
This presentation is the property of its rightful owner.
1 / 48

Lecture 4 PowerPoint PPT Presentation


  • 52 Views
  • Uploaded on
  • Presentation posted in: General

Lecture 4. Advanced Topics. OVERVIEW. Importing Data Data Analysis Add-In Solver Macros. IMPORTING DATA. You may receive data that is not in Excel that needs to be opened in Excel For example a text file, that is smaller and more amenable to email

Download Presentation

Lecture 4

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Lecture 4

Advanced Topics


OVERVIEW

  • Importing Data

  • Data Analysis Add-In

  • Solver

  • Macros


IMPORTING DATA

  • You may receive data that is not in Excel that needs to be opened in Excel

  • For example a text file, that is smaller and more amenable to email

  • Load this file into Excel, maintain the main columns


Importing Data From Text File


Importing Data From Text File


Importing Data From Text File


Importing Data From Text File


Importing Data From Text File


Importing Data From Text File


Importing Data From Text File

  • Save and Close the file

  • Open the file again from the folder

  • Notice the changes we made in the text file

  • The file is still a text fileeven though we have modifying it in Excel


Importing Data From Text File

  • From the Excel window select Save As Excel Workbook


DATA ANALYSIS ADD-IN

  • Some of Excel’s tools are not loaded automatically but must be activated by the user

  • For example the Statistical tools called “Data Analysis”


Add-Ins


Add - Ins


Data Analysis


Data Analysis

  • Use Data Analysis to calculate Descriptive Statistics of the “Speed” column


Data Analysis


Data Analysis


Data Analysis


SOLVER

  • Solver enables users to set up operations research problems e.g. integer linear programming, non linear programming etc

  • Operations research problems typically involve systems of equations that involve inequalities

  • Therefore, for non-inequalities problems we can use it as alternative, to say matrices, to solve systems of equations.

  • Solver must be activated from Add-Ins prior to use.


Optimization Using Solver

  • Consider the following investment decision problem for a financial institution

  • Three new financial products yield returns of 6.5%, 7%, 9% respectively

  • Total investment amount in these products altogether cannot exceed $500K

  • Due to risk, product 3 investment cannot exceed twice product 2 amount and product 2 investment cannot exceed three times product 1

  • What is the optimal investment plan to maximize return on investment?


Optimization Using Solver


Optimization Using Solver


Optimization Using Solver


Optimization Using Solver


Optimization Using Solver


Optimization Using Solver


Optimization Using Solver


Optimization Using Solver


Optimization Using Solver


Optimization Using Solver


Optimization Using Solver

  • Note that the more objectives and constraints, the more complex the problem


Optimization Using Solver


MACROS

  • Macros are used to record and save the keystrokes used to perform a task

  • By so doing the user can then call the macro to run the same tasks on another dataset

  • This is especially advantageous for repetitive type tasks, e.g. a weekly or daily traffic update report

  • Before we can use macros we must activate the Developer tab through Excel Options


Activate Developer Tab

  • Format the traffic data, but save all work in a macro


Activate Developer Tab


Activate Developer Tab


Record Macro


Record Macro

  • Keystrokes:

    • Highlight top row

    • Bold

    • All Borders

    • Fill color (green)

    • Select: Date, Time, Vehicle

    • Format

    • AutoFit Column Width

    • Scroll back to left

    • Highlight first 30 rows of speed column

    • Fill with orange color

    • Developer

    • Stop Recording


Save As Macros Enabled File


Save As Macros Enabled File


View Your Macros


Calling Your Macro

  • Now open a new traffic data file

  • Copy and paste the data to the macros enabled file, making sure all columns and rows are lined up as in the macros enabled file

  • Run the macro from View Macro


Calling Your Macro


Call Your Macro


Call a Macro

  • The macro does the formatting on the new data for us in one shot

  • Note that we could have run it directly without copying over if we had selected the file name in the drop down

  • Students! Repeat the data import task, but this time save it as a macro

  • As we shall see macros can get VERY complex


References

  • http://office.microsoft.com/en-us/support/results.aspx?av=zxl

  • www.youtube.com


  • Login