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

Lecture 4 PowerPoint PPT Presentation


  • 45 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

Lecture 4

Advanced Topics


Overview

OVERVIEW

  • Importing Data

  • Data Analysis Add-In

  • Solver

  • Macros


Importing data

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 file1

Importing Data From Text File


Importing data from text file2

Importing Data From Text File


Importing data from text file3

Importing Data From Text File


Importing data from text file4

Importing Data From Text File


Importing data from text file5

Importing Data From Text File


Importing data from text file6

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 file7

Importing Data From Text File

  • From the Excel window select Save As Excel Workbook


Data analysis add in

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


Add ins1

Add - Ins


Data analysis

Data Analysis


Data analysis1

Data Analysis

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


Data analysis2

Data Analysis


Data analysis3

Data Analysis


Data analysis4

Data Analysis


Solver

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

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 solver1

Optimization Using Solver


Optimization using solver2

Optimization Using Solver


Optimization using solver3

Optimization Using Solver


Optimization using solver4

Optimization Using Solver


Optimization using solver5

Optimization Using Solver


Optimization using solver6

Optimization Using Solver


Optimization using solver7

Optimization Using Solver


Optimization using solver8

Optimization Using Solver


Optimization using solver9

Optimization Using Solver


Optimization using solver10

Optimization Using Solver


Optimization using solver11

Optimization Using Solver

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


Optimization using solver12

Optimization Using Solver


Macros

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

Activate Developer Tab

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


Activate developer tab1

Activate Developer Tab


Activate developer tab2

Activate Developer Tab


Record macro

Record Macro


Record macro1

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


Save as macros enabled file1

Save As Macros Enabled File


View your macros

View Your Macros


Calling your macro

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 macro1

Calling Your Macro


Call your macro

Call Your Macro


Call a 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

References

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

  • www.youtube.com


  • Login