close

Вход

Забыли?

вход по аккаунту

?

Excel tutorials (2007)

код для вставкиСкачать
Excel Tutorials
(MS Excel 2007)
пѓј
пѓј
пѓј
пѓј
пѓј
Basic Excel
Data Analysis
Solver
XLSim
Tornado Diagram
1
Excel Basics
2
Excel tutorial:
Excel Information
• Excel training offered by University Computing
Service: http://www.cam.ac.uk/cs/courses/
• Excel handbooks available in JBS Computer Lab
• On-Line excel tutorials:
http://people.pwf.cam.ac.uk/mmh33/
• Book available from JBS library
пѓј B.V. Liengme, A Guide to Microsoft Excel for
Scientists and Engineers, Arnold Publisher,
London 1997.
3
Excel tutorial:
Some tips
•
•
•
•
•
•
•
Separate data, model (containing many formulas), and results
Ensure that you document and organize your models
properly so that others can easily understand your model and
repeat your experiments in the future
Use absolute and relative references but not use any numbers
in formulas
Ensure the model is scalable so that you can modify it easily
Validate your models carefully
Use Excel building functions such as SUMPRODUCT
Use DATA TABLE which allows you to repeatedly evaluate
a particular formula within the model while systematically
varying one or two input cells on which the formula depends
4
Excel tutorial:
Some basic Excel functions
•
•
•
•
•
•
•
•
FILLDOWN for editing efficiency
RELATIVE and ABSOLUTE REFERENCE for creating
formulas
IF for logical computations
SUM, MAX, MIN for computations
SUMPRODUCT for scaling-free calculations
RAND, RANDBETWEEN for simulation
DATA TABLE for parametric calculation/simulation
HLOOPUP, VLOOKUP for sampling
5
Excel tutorial:
Data Table
•
•
•
•
•
•
Create a table with values of the parameter in one column, say A2:A101
and copy the value of the output in B1
Select/highlight the whole table A1:B101
Invoke Dataпѓ Data Toolsпѓ What-If Analysisпѓ Data Table from Excel
menu and you get a dialogue box
Select column input cell, and click the input parameter cell, and then
click OK. You get the whole data table
WARNING: In order for Data Table to work, ensure that option
Automatic in Formulasпѓ Calculation is ticked.
You could have a Data Table without a parameter in simulation or a
Data Table with two parameters in a data table matrix. In the former, you
need to choose an empty cell for the column input cell and in the latter,
you need to correctly choose cells for both column input cell and row
input cell.
6
Excel Data Analysis
7
Excel tutorial:
Install Analysis ToolPak
•
•
•
•
•
•
•
•
Open Excel
Click the Office Button
Click Excel Options (on the taskbar at the bottom)
When the Excel Options dialog box appears: Select Add-Ins
from the list of options (on the pane on the left).
In the Manage box, select Excel Add-Ins (in the next to last
row)
Click Go
Select Analysis Toolpak and click OK. You should see Data
Analysis in Dataпѓ Analysis.
You may need the computer installation CD to finish this task
8
Excel Solver
9
Excel tutorial:
Install Solver
•
•
•
•
•
•
•
•
Open Excel
Click the Office Button
Click Excel Options (on the taskbar at the bottom)
When the Excel Options dialog box appears: Select Add-Ins
from the list of options (on the pane on the left).
In the Manage box, select Excel Add-Ins (in the next to last
row)
Click Go
Select Solver and click OK. You should see Data Analysis in
Dataпѓ Solver.
You may need the computer installation CD to finish this task
10
Excel tutorial:
Solver- design and build models
• Arrange data
• Allocate Change Cell
• Allocate Target Cell
• Allocate Constraint Cells
• Assign proper tags
• Add proper comments
• Add proper colours
11
Excel tutorial:
Link to Solver
• Go to Tools->Solver
• Set Target Cell
• Equal To
• By Changing Cells
• Subject to the Constraints: Add, Change,
Delete
• Set Solver Options
12
Excel tutorial:
Solver Options and Solution Reports
• Max Time
• Iterations
• Tolerance
• Assume Linear Model
• Use Automatic Scaling
• Assume Non-Negative
13
Excel tutorial:
View Solver Reports
•
Answer
•
Sensitivity
•
Limits
14
Excel tutorial:
Solver Enhancements
• Insert names: Highlight the area and then
insert name
• SUMPRODUCT(row_variables,
row_constraint_i)
• Add comments and proper tags
15
Excel XLSim
16
Excel tutorial:
Install Insight



Copy Insight Excel 2007.zip into your C folder.
Unzip Insight Excel 2007.zip.
Installation is accomplished by running Setup.exe
17
Excel tutorial:
Install XLSim
•
•
•
•
•
•
•
•
•
Open Excel and Click the Office Button
Click Excel Options (on the taskbar at the bottom)
When the Excel Options dialog box appears: Select Add-Ins from the list
of options (on the pane on the left).
In the Manage box, select Excel Add-Ins (in the next to last row)
Click Go
Select XLSim.xla from your Insight folder
Click OK
XLSim will appear in Add-Ins
If XLSim does not work, then Click the Office Button пѓ Excel
Optionпѓ Trust centerпѓ Trust Center Settingпѓ Macro Setting, and
then tick Trust acess to the VBA object model
18
Excel tutorial:
Conduct simulation









Go to Simulate пѓ Simulation (You are given an
editing window)
Add outputs
Choose simulation size
Click OK
You get another worksheet called Statistic
Go to Simulate пѓ Graphs
Choose Bin Size for histograms
Click histogram to generate a histogram
Click cumulative to generate a CD graph
19
Excel tutorial:
Parametric simulation










Determine which parameter for your sensitivity analysis
Select a few values for your parameter and store them in your
worksheet
Go to Simulate пѓ Parameterized Sim
You get a new dialog window
Select your output cell and give a name
Select the number of trials
Select the parameter cell used in your model
Give a name for your parameter
Select a range of values for your parameter that you stored before
Save this parametric simulation in a cell
20
Excel tutorial:
Parametric simulation




You can save and load parametric simulations
After creating or loading a parametric simulation, click OK for
experiments
Go to Simulate пѓ Common Graphs in order to plot either
histograms or CD graphs in one diagram. You could have a choice
of 3D histograms
By varying parameter values or changing ranges of parameter
values, you can conduct and refine your parametric simulation
experiments
For more detail, see Chapters 2 and 3 of Sam Savage,
Decision Making with Insight
21
Excel Tutorial:
Tornado Diagrams
22
Excel tutorial:
Experiment with Tornado Diagram
We use Customer Manag.xls as an example and assume that you have
already done your calculations in workbook “Sensitivity”. We want to
test how the system output is sensitive to various inputs.
•
Open Customer Manag.xls
•
Move to workbook “Sensitivity”
•
Open Tornado.xls and you see “TM” tab in the Excel menu bar
•
Go to Add-insпѓ TMпѓ Tornado Diagram and window “Tornado
Dialog” pops up
23
Excel tutorial:
Experiment with Tornado Diagram
• Move your curser to the box underneath “Data Range”
•
Select range A22:D25, which contains input parameters and the
ranges of values in a pre-specified format. The first row of this matrix
is for labels: Assumptions, Base, Low, High, each of the remaining
rows are for a particular input parameter. For each row other than the
first, the first column stores the parameter name, the second, third and
fourth store its base, low and high values.
• Move your curser to the box underneath “Result Cell”
• Select
P13, which stores the system output
• Click “OK”
• We are done!
24
Документ
Категория
Презентации
Просмотров
18
Размер файла
194 Кб
Теги
1/--страниц
Пожаловаться на содержимое документа