A3

Document Sample
A3 Powered By Docstoc
					                         Part I
                   Hands-on Exercises




                         Part II

     Spreadsheet-Based Decision Support Systems

                     Chapter 5: Charts




Manuel Gonçalves
                                         mgoncalves@fe.unl.pt
FEUNL
                             Overview
   5.1 Introduction
   5.2 Creating Charts with Chart Wizard
   5.3 Working with Chart Options
   5.4 Creating Dynamic Charts
   5.5 Summary




                                    2
                           Introduction
   Creating a chart using the Chart Wizard

   Customizing charts using various chart options

   Creating dynamic charts




                                     3
    Creating Charts with the Chart Wizard
   Charts are used to take data from a table and
    transform it into a graphical illustration

   They are useful for displaying data patterns or
    results

   To create a chart, we will use the Chart Wizard
     – Insert > Chart from menu
     –      icon from standard toolbar




                                         4
                           The Chart Wizard
   There are four main steps in using the Chart Wizard:
     –   Step 1:   Chart Type
     –   Step 2:   Source Data
     –   Step 3:   Chart Options
     –   Step 4:   Chart Location


   Chart Type = bar graph, scattered graph, pie chart, etc
   Source Data = data range or various series
   Chart Options = title, axes, legend, gridlines, etc
   Chart Location = object or chart sheet




                                      5
                             Figure 5.1
   Table of data = Number Sold and Revenue Generated from
    various Car Models

   We create two charts: bar graph of Number Sold and pie chart of
    Revenue Generated




                                     6
               Step 1: Chart Type

   Each chart type has
    a set of chart sub-
    types.

   First we will select
    Column as our chart
    type.

   We can now choose
    a sub type that is
    either a 2D or 3D
    – Clustered Column
    – Stacked Column
    – 100% Stacked
      Column



                           7
                     Step 2: Source Data
   Source Data can be defined
    by a Data Range or with
    Series

   Data Range is the main data
    table we initially highlighted
    to be graphed.
     – Rows = comparison is made
       among rows of data from the
       table
     – Columns = comparison is
       made among columns of
       data from the table




                                     8
             Step 2: Source Data (cont’d)
   Series are the rows or
    columns of data from the table
    which are being compared.

   Series can be
     – Added
     – Removed
     – Named




                                     9
                       Step 3: Chart Options
   Tabs of the Chart Options step include:
     –   Titles
     –   Axes
     –   Gridlines
     –   Legend
     –   Data Labels
     –   Data Table




                                     10
            Step 3: Chart Options (cont’d)
   Titles options include chart, category (X) axis, and value (Y) axis titles




                                        11
           Step 3: Chart Options (cont’d)
   Axes options include Category (X) and Value (Y) axes
    – Automatic = default
    – Category = any grouping of axes values
    – Time-scale = dates or time units




                                      12
           Step 3: Chart Options (cont’d)
   Gridlines options include Category (X) and Value (Y) axes
    – Major gridlines
    – Minor gridlines




                                     13
           Step 3: Chart Options (cont’d)
   Legend options include showing or hiding the legend, and placement of
    the legend




                                    14
           Step 3: Chart Options (cont’d)
   Data Labels options include showing or hiding data values and labels




                                     15
           Step 3: Chart Options (cont’d)
   Data Table options include showing or hiding the data table




                                     16
                   Step 4: Chart Location
   The chart can be completed as
    – A new chart sheet; name is given
    – An object in a current sheet; name of sheet is selected




                                        17
                            Figure 5.6
   From the bar graph we see that the Aco3500 sold the most cars




                                    18
                            Figure 5.7
   We repeat the Chart Wizard steps to create the pie chart of Revenue
    Generated
   We see that the Cam3200 generated the most revenue




                                    19
              Working with Chart Options
   After creating a chart using the Chart Wizard, we can return to any of the
    Wizard steps by simply right-clicking on the chart.

   The Format Chart Area option also allows us to modify the general
    patterns, font, and properties of the chart.
     – Format Plot Area
     – Format Data Series
     – Format Axis




                                      20
                            Figure 5.9
   Data table shows plant growth based on minerals added or removed
    from the ground
                              Growth    Minerals
                               -1000      -10
                                -512       -8
                                -343       -7
                                -216       -6
                                -125       -5
                                 -64       -4
                                 -27       -3
                                  -8       -2
                                  -1       -1
                                   0        0
                                   1        1
                                   8        2
                                 27         3
                                 64         4
                                125         5
                                216         6
                                343         7
                                512         8
                                1000       10




                                       21
                   Figures 5.14 and 5.16
   Create the chart




   Chart Options can be used to switch x and y values for better data
    analysis                                          Minerals


                                                    1500


                                                    1000


                                                     500


                                                       0
                                  -15    -10   -5           0    5   10   15

                                                     -500


                                                    -1000


                                                    -1500




                                        22
                Creating Dynamic Charts
   A chart is linked directly to the Data Range specified in the Source Data
    step of the Chart Wizard.

   If any points in this range of data are modified, the chart is automatically
    updated to reflect a new corresponding data point.

   There will be three main Excel concepts used to create a dynamic chart:
     – Defining names
     – OFFSET function
     – COUNT function




                                       23
                      Dynamic Charts (cont)
   Create some range names using the OFFSET and COUNT functions and set the
    Series of the chart to these dynamic ranges.
     – =OFFSET(initial_data_location, 0, 0, COUNT(entire_column), 1)

   The rows_to_move and columns_to_move parameters are set to 0 because
    we are only interested in the column in which our reference_cell ( =
    initial_data_location) is located.

   The width is again set to 1, since we are interested only in one column.
   The height parameter is found using the COUNT function.
     – The COUNT function will review the entire column of the relative data and count how
       many cells have numeric values.
     – Thus the height of our range becomes dynamic as the amount of numeric values in
       the column increases.




                                             24
                          Figure 5.17
   Months and Units Sold may be dynamic values




                                   25
                          Figure 5.18
   The dynamic ranges are created for each column using the OFFSET
    and COUNT functions




                                  26
                           Figure 5.19
   The Series are set using these dynamic ranges




                                    27
                          Figure 5.20
   The chart is now dynamic




                               28
                                   Summary
   Excel Charts allow you to illustrate your data in order to perform better
    analysis.

   There are four basic steps in the Chart Wizard:
     –   Step 1:   Select the Chart Type and options.
     –   Step 2:   Verify or change the Data Range and define all Series.
     –   Step 3:   Determine the Chart Options.
     –   Step 4:   Choose the location of the chart.


   A chart can be modified after it is created by right-clicking on the chart or
    different parts of the chart. You can change basic settings as well as
    formatting.

   A dynamic chart can be created using the OFFSET and COUNT
    functions to create dynamic ranges used as Series in the Source Data.

                                            29
                         Additional Links
   (place links here)




                                30

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:5
posted:11/12/2011
language:English
pages:30