Забыли?

?

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
```
Документ
Категория
Презентации
Просмотров
19
Размер файла
194 Кб
Теги
1/--страниц
Пожаловаться на содержимое документа