1 / 48

Advanced Excel Techniques: Data Import, Solver Optimization, and Macro Automation

This lecture covers advanced Excel topics including importing data from text files, using the Data Analysis Add-In for statistical analysis, and solving optimization problems with Solver. Learn how to activate key Excel features, perform essential data analysis, and apply Solver to maximize investment returns under constraints. Additionally, discover the power of macros for automating repetitive tasks, streamlining your workflow, and enhancing productivity. This session is crucial for anyone looking to elevate their Excel skills.

perrin
Télécharger la présentation

Advanced Excel Techniques: Data Import, Solver Optimization, and Macro Automation

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 4 Advanced Topics

  2. OVERVIEW • Importing Data • Data Analysis Add-In • Solver • Macros

  3. 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

  4. Importing Data From Text File

  5. Importing Data From Text File

  6. Importing Data From Text File

  7. Importing Data From Text File

  8. Importing Data From Text File

  9. Importing Data From Text File

  10. 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

  11. Importing Data From Text File • From the Excel window select Save As Excel Workbook

  12. 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”

  13. Add-Ins

  14. Add - Ins

  15. Data Analysis

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

  17. Data Analysis

  18. Data Analysis

  19. Data Analysis

  20. 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.

  21. 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?

  22. Optimization Using Solver

  23. Optimization Using Solver

  24. Optimization Using Solver

  25. Optimization Using Solver

  26. Optimization Using Solver

  27. Optimization Using Solver

  28. Optimization Using Solver

  29. Optimization Using Solver

  30. Optimization Using Solver

  31. Optimization Using Solver

  32. Optimization Using Solver • Note that the more objectives and constraints, the more complex the problem

  33. Optimization Using Solver

  34. 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

  35. Activate Developer Tab • Format the traffic data, but save all work in a macro

  36. Activate Developer Tab

  37. Activate Developer Tab

  38. Record Macro

  39. 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

  40. Save As Macros Enabled File

  41. Save As Macros Enabled File

  42. View Your Macros

  43. 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

  44. Calling Your Macro

  45. Call Your Macro

  46. 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

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

More Related