Ieng 423 design of decision support systems
This presentation is the property of its rightful owner.
Sponsored Links
1 / 16

IENG 423 Design of Decision Support Systems PowerPoint PPT Presentation


  • 72 Views
  • Uploaded on
  • Presentation posted in: General

IENG 423 Design of Decision Support Systems. Modeling with Excel Excel Basics Pivot Tables and Pivot Charts in Decision Support Systems. Pivot Tables. Excel is very good at: Letting your organize your data, And do calculations on that data …and show you the results,

Download Presentation

IENG 423 Design of Decision Support Systems

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Ieng 423 design of decision support systems

IENG 423Design of Decision Support Systems

Modeling with Excel

Excel Basics

Pivot Tables and Pivot Charts in Decision Support Systems


Pivot tables

Pivot Tables

Excel is very good at:

Letting your organize your data,

And do calculations on that data

…and show you the results,

…and redo the calculations, and…

Excel is not so good at:

Letting you change the layout of your data after you have set up the worksheet…

..at least in most cases


Pivot tables1

Pivot Tables

Excel provides a powerful tools to let you organize and present your data in different ways…

They are called “Pivot Tables”

Its very flexible in terms of what you want to display as column and what are rows

In fact, it is trivially easy to change that… make rows into columns and columns into rows

That is why they are called pivot tables… you can pivot you data tables (lists)


Pivot tables2

Pivot Tables

Does that impress everyone?

Everyone say “ooooh!”

Me either


Pivot tables3

Pivot Tables

But what does impress me…

… is that Pivot tables allow you to summarize data in a worksheet …

Across one, two or three dimensions…

…think crosstab tables

Like product output by factory and month


Pivot tables4

Pivot Tables

There’s a bit of a catch…

The data to be used as input for a pivot table must be organized like a data list (data base)

That is, each row must be represent one entity

A test event

Performance for a factory for a reporting period


Pivot tables5

Pivot Tables

To create a Pivot Table

Select a cell in the original data

Click on Data on the toolbar

The select Pivot Table and Pivot Chart Report….

Next you will see the Pivot Table/Pivot Chart Wizard


Pivot tables6

Pivot Tables

In the Pivot Table/Pivot Chart Wizard

Identify the source of data for your table

Indicate whether you want a Pivot Table or Pivot Chart

Click Next

Then confirm or select the data range to be input for your Pivot Chart

Click Next

Indicate whether you want your Pivot Table on an existing worksheet or a new worksheet

Click Finish


Pivot tables7

Pivot Tables

Now you will see a Pivot table shell (nothing in it), and a field list box

Drag and place the fields where you want them to build the table

As you drop the field header, Excel will start to populate the Pivot table

…but wait, there’s more…


Pivot tables8

Pivot Tables

Do CreatePivot Exercise on pg. 157 of Frye book

Open CreatePivot.xls


Pivot tables9

Pivot Tables

Explore Pivot Table Toolbar


Pivot tables10

Pivot Tables

You can

Filter data

Change the calculations done on Pivot table source data

Form Pivot Tables, just like other cells

Collapse/Expand subcategories

Rearrange the table as you please


Pivot charts

Pivot Charts

Pivot Charts are graphic representations of Pivot Tables

Pivot Charts are data charts just like we saw when we looked at Charts and Graphs…

…but tied to the data in the Pivot Table


Pivot charts1

Pivot Charts

You can select Pivot Chart when you are creating Pivot Table/Pivot Chart…

..or…

Select the Pivot Chart wizard after you have created you Pivot Table

Note: your Pivot Chart is tied to your Pivot Table…

…if you change the underlying Pivot Table, the Pivot Chart changes


Pivot tables and pivot charts

Pivot Tables and Pivot Charts

In a real world application it will not be unusual for you to want to create a Pivot Table or Pivot Chart from another (outside of Excel) data source

There are several ways to do this

One is to import a text file into Excel

Try importing Export.txt into Excel


  • Login