Transcript Microsoft Excel Chapter 5

Microsoft Excel
Chapters 4 & 5
Nagendra Vemulapalli
[email protected]
Announcements
• MyITLab Lesson# B is due on 02/11/2013 by
11.59pm
• The First Assignment is due on 02/08/2013 by
11.59pm
• The Second Assignment is also posted and is
due on 02/15/2013 by 11.59pm
7/21/2015
Nagendra Vemulapalli
2
Chart
• Chart gives visual representation of Excel sheet
workbook
• They allow us to interpret large amount of data
easily and quickly
• In excel charts are based on numeric values in
the cells called Data points
• A group of related data points that appear in
the rows or columns in the worksheet creates a
data series.
7/21/2015
Nagendra Vemulapalli
3
Basic chart types
• Pie – Compares parts with the whole
• Column – Shows/compares values across
different categories
• Line charts – Shows trends over time
• X-Y Scatter chart – compares pair of values,
usually looking for a trend
7/21/2015
Nagendra Vemulapalli
4
Choosing the correct chart type
• Choose the chart type that best fits the data
• For comparing values from different categories,
use
– Column, Bar, or Line Chart
• For comparing individual values to a whole
collection, use
– Pie or Doughnut Chart
7/21/2015
Nagendra Vemulapalli
5
What they look like
Pie
7/21/2015
Column chart
Nagendra Vemulapalli
6
What they look like..
Line Chart
X-Y Scatter plot
Instrument Sales 2005
Growth
60
45000
40000
50
35000
30000
Drum Set Sales
30
Guitar Sales
Quantity
Total Sold
40
25000
20000
15000
20
10000
10
5000
0
1965
0
1
2
3
4
1975
1980
1985
1990
1995
2000
2005
Year
Quarter
7/21/2015
1970
Nagendra Vemulapalli
7
Creating Pie chart
• Go to my lecture notes page and download the example1 file
and open it.
• Select A1:C2
• Go to Insert -> Charts group -> Create Chart button (tiny
square icon at the bottom right)
• Choose Pie and Click OK
• Choose Chart Layouts and choose Layout 2
• Change the title to Fruit Stand
• Choose Design -> Chart Styles -> Style 42
• Change the color of the sections to correspond with the data
it represents
• Click the section twice
• Go to the Format -> Shape styles ->Shape fill
7/21/2015
Nagendra Vemulapalli
8
Changing Chart Type
•
•
•
•
•
Go to Design-> Change Chart Type
Choose Pie in 3-D
Right Click on the chart and choose 3D Rotation
Tweak the value of Rotation Y and click Close
You can also change the style of data labels
– Single click / two slow clicks on a data label and right
click to show popup menu
7/21/2015
Nagendra Vemulapalli
9
Separating Parts of Pie(Exploding Pie)
• Single Part Separation/Explosion
– Make two slow clicks on one of the pieces
– Left click and drag it outward
• Full chart explosion/Separation
– Make sure nothing is selected
– Hold down the left button on one of the pieces and
drag it outward
7/21/2015
Nagendra Vemulapalli
10
Multi Series Chart
•
•
•
•
•
•
•
•
Switch to “Media sales” sheet
Select Range A1:D3
Go to Insert -> Charts group -> Create Chart
Choose Clustered column and Click OK
Choose Chart Layouts and choose Layout 9
Title the chart “International Media Sales”
Title X axis “Countries”
Title Y axis “Volume”
7/21/2015
Nagendra Vemulapalli
11
Manipulating the Chart
• In the “Design” tab choose “Select Data”
• Experiment with switching row/column.
7/21/2015
Nagendra Vemulapalli
12
Change to 3-D Stacked
• Design-> Change Chart Type->Choose Stacked 3D
• Right Click on the chart -> 3D Rotation…
• Tweak Rotation / Perspective
7/21/2015
Nagendra Vemulapalli
13
Move the Chart
• Design  Move Chart  New Sheet
• Now Title the new sheet “Media sales chart”
7/21/2015
Nagendra Vemulapalli
14
Line Chart
•
•
•
•
•
•
•
•
Switch to population sheet
Select A1:B32
Go to Insert -> Charts group -> Create Chart
Choose the First Chart in Line and Click OK
Choose Chart Layouts and choose Layout 1
Title the chart “Population”
Title y axis “Population”
For X axis title Go to Layout -> Labels -> Axis title ->
Primary Horizontal axis Title -> Title below axis ->
Name it “year”
7/21/2015
Nagendra Vemulapalli
15
Manipulating Data sources
• We Don’t want the year to plotted and we want
year to be X axis
• Go to Select Data source -> Legend entries ->
Select Year ->Delete
• Horizontal Axis Labels -> Edit -> Select Data
Range A2:A32
7/21/2015
Nagendra Vemulapalli
16
Regression/Trend Analysis
• Regression analysis is used to predict the
Future/unknown values with the help of given
Values/information
• In excel it is known as “Trendlines”
• More details here
7/21/2015
Nagendra Vemulapalli
17
Trend lines
• A trend line is Excel’s best guess at a mathematical
equation to represent the given data
• We can then use that equation to predict the
future.
• The possible types of trend lines are:
• Exponential
• Linear
• Log
• Polynomial
• Power
7/21/2015
Nagendra Vemulapalli
18
Adding the Trend Lines
• Select the series you want to add the trend line
to.
• Right click and choose Add Trend line
• Select the Trend line that “Best Fits” the Data
points
• Turn on “Display R-squared value on chart”
– R-squared value: “Close to 1.0” means better
approximation
– Note that the R-squared value does not always give
you the best result. Think in all perspectives
7/21/2015
Nagendra Vemulapalli
19
Alligator Example
• Download example2 file
– We will add a Trend line
– “Line of Best Fit” will be identified for a scattered plot:
– We will fit a line on the data points and figure out which
type of line will be the best model for the data
• Exponential
• Linear
• Log
• Polynomial
• Power
7/21/2015
Nagendra Vemulapalli
20
Alligator Example Cont’d – Scatter Chart
• Select the range A5:B29
• Creating the scatter chart:
– Insert ribbon
– Scatter
Nagendra Vemulapalli
7/21/2015
21
Scatter Chart
• Labeling the chart
– Design tab
– Chart Layouts drop down menu
– Select the first one that has labels for the title, xaxis, and y-axis
– Type the following:
• Chart Title: Alligator Regression
• X Axis Title  Length in Inches
• Y Axis Title  Weight in Pounds
7/21/2015
Nagendra Vemulapalli
22
Example cont’d – Adding a Trendline
• First, RIGHT click on one of the points on your
scatter chart
• Select “Add Trendline…”
Nagendra Vemulapalli
23
7/21/2015
Adding a Trendline Cont’d
• Remember that we want to examine the line
that best fits our points
• Exponential
• Fits nicely around the data points
• Exponential Curve that touches all of them and follows
the direction of points
Nagendra Vemulapalli
24
7/21/2015Date
Adding a Trendline Cont’d
• Linear
– A straight line
– Goes in the same direction but does not touch most
of the points!
– Therefore, it’s probably not the best one to use as
our model
Nagendra Vemulapalli
25
7/21/2015
Adding a Trendline Cont’d
• Logarithmic
– It does not seem to model our scattered data points
accurately
Nagendra Vemulapalli
26
7/21/2015
Adding a Trendline Cont’d
• Polynomial
– Most of the points are touched
– Moving in the same direction of the data
– Similar to the exponential
Nagendra Vemulapalli
27
7/21/2015
Adding a Trendline Cont’d
• Power
– Moves in the same direction of the data, but doesn’t
touch most of the points
Nagendra Vemulapalli
28
7/21/2015
Adding a Trendline Cont’d
• Narrow and compare our options
• Exponential “looks” better choice but wait try
increasing order of polynomial to 3!
Exponential
vs.
Polynomial
Nagendra Vemulapalli
29
7/21/2015
Predicting
• Predicting graphically
– Forecast forward
• Predict Numerically
– Display equation
– Increase precision
– Reformat Equation
– Insert into dataset
• Compare Results
Nagendra Vemulapalli
30
7/21/2015
Predicting Graphically
• Click on your Trendline to highlight it
• Right click
• Select “Format Trendline…”
Nagendra Vemulapalli
31
7/21/2015
Forecasting forward
Nagendra Vemulapalli
32
7/21/2015
Forecasting forward
• Enter 3.0
• Close
• Chart is extended to almost 150 inches and
almost 700 pounds (visually)
Nagendra Vemulapalli
33
7/21/2015
Predicting Numerically
• More exact numbers to base our future
estimates on
• Displaying an equation on the chart
• Right click on the Trendline
• Select “Format Trendline…”
Nagendra Vemulapalli
34
7/21/2015
Predicting Numerically
• Check the box to the left of “Display Equation on
chart”
• The equation will be displayed on your chart
• Increasing precision
–
–
–
–
–
Right click on the equation
“Format Trendline Label…”
“Number”
Decimal Places: 4
Close
• Precision has increasing
Nagendra Vemulapalli
35
7/21/2015
Reformatting equation
• Now we know the equation of the curve
y = 0.0008x3 - 0.1542x2 + 11.4730x - 277.8223
Let’s convert this into a formula in Excel.
• In cell B30, enter
=0.0008*A30^3-0.1542*A30^2+11.473*A30-277.8223
Note that ‘*’ is a multiplication and ‘^’ is a power.
• Autofill B31 and B32 and you can predict the
unknown values.
Nagendra Vemulapalli
36
7/21/2015