cee 3804: computer applications
DESCRIPTION
CEE 3804: Computer Applications. Databases and Pivot Tables. Dr. Trani. 1. Topics to be Covered. Database functions Pivot tables: Basics Pivot table reports. Database Functions a. Overview. Functions that provide pivot table functionality Dfunction( database,field,criteria ): - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: CEE 3804: Computer Applications](https://reader036.vdocuments.us/reader036/viewer/2022081513/56814e4a550346895dbbd204/html5/thumbnails/1.jpg)
CEE 3804: Computer Applications
Databases and Pivot Tables
Dr. Trani
![Page 2: CEE 3804: Computer Applications](https://reader036.vdocuments.us/reader036/viewer/2022081513/56814e4a550346895dbbd204/html5/thumbnails/2.jpg)
Spring 2013CEE 3804 Slide 2
1. Topics to be Covered
Database functionsPivot tables:
BasicsPivot table reports
![Page 3: CEE 3804: Computer Applications](https://reader036.vdocuments.us/reader036/viewer/2022081513/56814e4a550346895dbbd204/html5/thumbnails/3.jpg)
Spring 2013CEE 3804 Slide 3
Database Functions a. Overview
Functions that provide pivot table functionality
Dfunction(database,field,criteria):database:
• range of cells that make up the data
field:• name of field to perform operation
criteria:• range of cells that hold the criteria you want to hold with
![Page 4: CEE 3804: Computer Applications](https://reader036.vdocuments.us/reader036/viewer/2022081513/56814e4a550346895dbbd204/html5/thumbnails/4.jpg)
Spring 2013CEE 3804 Slide 4
Database Functions b. List of Functions
The list functions include the following:DAVERAGE(), DCOUNT(), DCOUNTA(), DGET(),
DMAX(), DMIN(), DPRODUCT(), DSTDDEV(), DSTDDEVP(), DSUM(), DVAR(), and DVARP().
Where:• DCOUNTA(): Returns the count of nonblank records• DGET(): Returns the value of a specified field for a
single matching record
Example:Use the Cardata file on the course notes
![Page 5: CEE 3804: Computer Applications](https://reader036.vdocuments.us/reader036/viewer/2022081513/56814e4a550346895dbbd204/html5/thumbnails/5.jpg)
Spring 2013CEE 3804 Slide 5
Database Example (Cardata.xls)
The syllabus has a file with car data. Name the file Cardata.xls
![Page 6: CEE 3804: Computer Applications](https://reader036.vdocuments.us/reader036/viewer/2022081513/56814e4a550346895dbbd204/html5/thumbnails/6.jpg)
Spring 2013CEE 3804 Slide 6
Database Example (Cardata.xls)
Create a separate section in the worksheet where the query will be done
Copy the sequence of titles to help you guide the query (see below)
![Page 7: CEE 3804: Computer Applications](https://reader036.vdocuments.us/reader036/viewer/2022081513/56814e4a550346895dbbd204/html5/thumbnails/7.jpg)
Spring 2013CEE 3804 Slide 7
Database Example (Cardata.xls)
Suppose we want to count the number of cars whose weight > 3000 lbs
Dcount(A1:H117,”Weight”,J6:Q7)
Database range Database field Criteria
![Page 8: CEE 3804: Computer Applications](https://reader036.vdocuments.us/reader036/viewer/2022081513/56814e4a550346895dbbd204/html5/thumbnails/8.jpg)
Spring 2013CEE 3804 Slide 8
Database Example (Cardata.xls)
Read the solution from the cell containing the database query
Note that 50 cars weigh more than 3,000 lbs
![Page 9: CEE 3804: Computer Applications](https://reader036.vdocuments.us/reader036/viewer/2022081513/56814e4a550346895dbbd204/html5/thumbnails/9.jpg)
Spring 2013CEE 3804 Slide 9
Database Example (Cardata.xls)
A variation to specify the database fieldDcount(A1:H117,4,J6:Q7)
Database rangeDatabase fieldColumn format
Criteria
![Page 10: CEE 3804: Computer Applications](https://reader036.vdocuments.us/reader036/viewer/2022081513/56814e4a550346895dbbd204/html5/thumbnails/10.jpg)
Spring 2013CEE 3804 Slide 10
Database Example (Cardata.xls)
A variation to specify the database range
Dcount(Cars,4,J6:Q7)
Database rangeVariable format
Database fieldColumn format
Criteria
![Page 11: CEE 3804: Computer Applications](https://reader036.vdocuments.us/reader036/viewer/2022081513/56814e4a550346895dbbd204/html5/thumbnails/11.jpg)
Spring 2013CEE 3804 Slide 11
Database Example (Cardata.xls)
Count the number of cars made in Japan whose weight > 2,700 lbs
Verify using the regular filters in Excel
Give it a try
![Page 12: CEE 3804: Computer Applications](https://reader036.vdocuments.us/reader036/viewer/2022081513/56814e4a550346895dbbd204/html5/thumbnails/12.jpg)
Spring 2013CEE 3804 Slide 12
Database Example (Cardata.xls)
Count the number of Sporty cars whose horsepower falls between 150 and 200 HP
This requires two sets of conditions for one variable (Horsepower)
![Page 13: CEE 3804: Computer Applications](https://reader036.vdocuments.us/reader036/viewer/2022081513/56814e4a550346895dbbd204/html5/thumbnails/13.jpg)
Spring 2013CEE 3804 Slide 13
Pivot Tables a. Overview
A Pivot Table is a powerful data-analysis tool that Excel offers
Pivot Tables summarize data in several fields:
data fieldrow fieldcolumn field
![Page 14: CEE 3804: Computer Applications](https://reader036.vdocuments.us/reader036/viewer/2022081513/56814e4a550346895dbbd204/html5/thumbnails/14.jpg)
Spring 2013CEE 3804 Slide 14
Pivot Tables b. Demonstration Example
![Page 15: CEE 3804: Computer Applications](https://reader036.vdocuments.us/reader036/viewer/2022081513/56814e4a550346895dbbd204/html5/thumbnails/15.jpg)
Spring 2013CEE 3804 Slide 15
Pivot Tables c. Creating Pivot Tables
There are four steps to creating a pivot table:Specify the type of source list to use for the
pivot tableIdentify the location of the dataDefine row, column, values, and filters fields for
the tableSelect a location, name, and other options of
the tableFilter Field
Row Field
Row Items
Column Field
Data Area
Data Field
![Page 16: CEE 3804: Computer Applications](https://reader036.vdocuments.us/reader036/viewer/2022081513/56814e4a550346895dbbd204/html5/thumbnails/16.jpg)
Spring 2013CEE 3804 Slide 16
Pivot Tables d. Data List or Table
Data list/table can be:Excel list or table on a worksheetA separate database file in Access, FoxPro, etc.A collection of lists with row and column labels
in one or more worksheetsAnother pivot table
An Excel data table must have labeled columns
![Page 17: CEE 3804: Computer Applications](https://reader036.vdocuments.us/reader036/viewer/2022081513/56814e4a550346895dbbd204/html5/thumbnails/17.jpg)
Spring 2013
Practice Pivot Tables
Retrieve the file called Construction_assets_cat.xls from the web site
CEE 3804 Slide 17
![Page 18: CEE 3804: Computer Applications](https://reader036.vdocuments.us/reader036/viewer/2022081513/56814e4a550346895dbbd204/html5/thumbnails/18.jpg)
Spring 2013CEE 3804 Slide 18
Excel Pivot Table Example
File contains the Caterpillar vehicles owned by a construction company
Three construction sitesThree types of vehicles (CAT 160H, CAT
725 and CAT 775F)Mileage, value and status (active or
not) are attributes contained in the data set
![Page 19: CEE 3804: Computer Applications](https://reader036.vdocuments.us/reader036/viewer/2022081513/56814e4a550346895dbbd204/html5/thumbnails/19.jpg)
Spring 2013
Pivot Table in Excel Ribbon (2013)
CEE 3804 Slide 19
![Page 20: CEE 3804: Computer Applications](https://reader036.vdocuments.us/reader036/viewer/2022081513/56814e4a550346895dbbd204/html5/thumbnails/20.jpg)
Spring 2013CEE 3804 Slide 20
Customizing Pivot Tables a. Specifying the Layout of a Pivot Table
The PivotTable toolbar simplifies the customizing of the pivot table:
enable/disable element selectionchange row/column/page/data
Pivot Table Fields
Areas to BuildPivot Table
![Page 21: CEE 3804: Computer Applications](https://reader036.vdocuments.us/reader036/viewer/2022081513/56814e4a550346895dbbd204/html5/thumbnails/21.jpg)
Spring 2013
Building the Pivot Table (Excel 2013)
Start by selecting from the Pivot Table fields
CEE 3804 Slide 21
Pivot Table isBuilt in the sameSpreadsheet
Construction SiteVariable gets addedTo Row Pivot Area
Adding a data fieldStarts the process
![Page 22: CEE 3804: Computer Applications](https://reader036.vdocuments.us/reader036/viewer/2022081513/56814e4a550346895dbbd204/html5/thumbnails/22.jpg)
Spring 2013
Building the Pivot Table (Excel 2013)
Adding more variables starts making the Pivot Table more useful
CEE 3804 Slide 22
A second set of rowsIs added to existingones
Vehicle is added to the Area
Adding a seconddata field (Vehicle)
![Page 23: CEE 3804: Computer Applications](https://reader036.vdocuments.us/reader036/viewer/2022081513/56814e4a550346895dbbd204/html5/thumbnails/23.jpg)
Spring 2013
Building the Pivot Table (Excel 2013)
Adding a numeric variable summarizes the data based on the Values field
CEE 3804 Slide 23
Vehicle is added to the Area
Adding a numericdata field (Miles)
![Page 24: CEE 3804: Computer Applications](https://reader036.vdocuments.us/reader036/viewer/2022081513/56814e4a550346895dbbd204/html5/thumbnails/24.jpg)
Spring 2013
Building the Pivot Table (Excel 2013)
You can change the summary in the Value area
CEE 3804 Slide 24
Change the Sum of MilesTo Average Miles
![Page 25: CEE 3804: Computer Applications](https://reader036.vdocuments.us/reader036/viewer/2022081513/56814e4a550346895dbbd204/html5/thumbnails/25.jpg)
Spring 2013CEE 3804 Slide 25
Customizing Pivot Tables d. Changing Excel’s Value/Data Field Summary
Eleven summary functions:• Sum: totals the values• Count: Counts the values• Average: Computes the average• Max: Computes the largest value• Min: Returns the smallest value• Product: Computes the product of the values• Count Nums: Counts the number of numeric data• StdDev: Computes the standard deviation of the data• StdDevp: Computes the standard deviation for a
population• Var and Varp: Computes the variance
![Page 26: CEE 3804: Computer Applications](https://reader036.vdocuments.us/reader036/viewer/2022081513/56814e4a550346895dbbd204/html5/thumbnails/26.jpg)
Spring 2013CEE 3804 Slide 26
Customizing Pivot Tables b. Changing Fields (Use your Imagination)
Option 1:
Option 2:
![Page 27: CEE 3804: Computer Applications](https://reader036.vdocuments.us/reader036/viewer/2022081513/56814e4a550346895dbbd204/html5/thumbnails/27.jpg)
Spring 2013CEE 3804 Slide 27
Customizing Pivot Tables c. Adding Filters
Adding categorical fields (non-numeric) to the Filter area allows you to execute quick filters
Categorical fieldsAre good filters
Adding the Status VariableTo be used as filter
Apply the filterusing this pulldown menu
![Page 28: CEE 3804: Computer Applications](https://reader036.vdocuments.us/reader036/viewer/2022081513/56814e4a550346895dbbd204/html5/thumbnails/28.jpg)
Spring 2013CEE 3804 Slide 28
Customizing Pivot Tables c. Adding Filters
Adding categorical fields (non-numeric) to the Filter area allows you to execute quick filters
Status field isUsed as filter
Selected “Active”Vehicles
![Page 29: CEE 3804: Computer Applications](https://reader036.vdocuments.us/reader036/viewer/2022081513/56814e4a550346895dbbd204/html5/thumbnails/29.jpg)
Spring 2013CEE 3804 Slide 29
Customizing Pivot Tables c. Adding Pivot Charts
You can also build Pivot Tables and Pivot Charts at the same time
![Page 30: CEE 3804: Computer Applications](https://reader036.vdocuments.us/reader036/viewer/2022081513/56814e4a550346895dbbd204/html5/thumbnails/30.jpg)
Spring 2013CEE 3804 Slide 30
Customizing Pivot Tables c. Adding Pivot Charts
Building Pivot Tables and Pivot Charts in one step Select the icon in the Insert Tab
The following steps are the same as those used to create a Pivot Table
![Page 31: CEE 3804: Computer Applications](https://reader036.vdocuments.us/reader036/viewer/2022081513/56814e4a550346895dbbd204/html5/thumbnails/31.jpg)
Spring 2013CEE 3804 Slide 31
Customizing Pivot Tables c. Filtering on the Fly in Pivot Charts
You can apply filters to Pivot Charts
Filter leaves outCat 775F vehicles
![Page 32: CEE 3804: Computer Applications](https://reader036.vdocuments.us/reader036/viewer/2022081513/56814e4a550346895dbbd204/html5/thumbnails/32.jpg)
Spring 2013CEE 3804 Slide 32
Customizing Pivot Tables e. Calculation Types
Excel can perform a number of calculations:Difference from, % Of, % Difference From, Running Total
in, % of row, % of column, % of total, Index
Percent Difference from East Region Percent of Row
![Page 33: CEE 3804: Computer Applications](https://reader036.vdocuments.us/reader036/viewer/2022081513/56814e4a550346895dbbd204/html5/thumbnails/33.jpg)
Spring 2013CEE 3804 Slide 33
Pivot Tables in Excel 2010
Look under Insert pull-down menu
Pivot Table information
![Page 34: CEE 3804: Computer Applications](https://reader036.vdocuments.us/reader036/viewer/2022081513/56814e4a550346895dbbd204/html5/thumbnails/34.jpg)
Spring 2013CEE 3804 Slide 34
Example in Excel 2010
Retrieve the file called Construction_assets_cat.xls from the web site
![Page 35: CEE 3804: Computer Applications](https://reader036.vdocuments.us/reader036/viewer/2022081513/56814e4a550346895dbbd204/html5/thumbnails/35.jpg)
Spring 2013CEE 3804 Slide 35
Creating Pivot Table (Excel 2007/2010)
![Page 36: CEE 3804: Computer Applications](https://reader036.vdocuments.us/reader036/viewer/2022081513/56814e4a550346895dbbd204/html5/thumbnails/36.jpg)
Spring 2013CEE 3804 Slide 36
Creating Pivot Table (Excel 2007/2010)
Pivot Table
Builder Panel
![Page 37: CEE 3804: Computer Applications](https://reader036.vdocuments.us/reader036/viewer/2022081513/56814e4a550346895dbbd204/html5/thumbnails/37.jpg)
Spring 2013CEE 3804 Slide 37
Adding Elements to the Pivot Table (Excel 2007)
![Page 38: CEE 3804: Computer Applications](https://reader036.vdocuments.us/reader036/viewer/2022081513/56814e4a550346895dbbd204/html5/thumbnails/38.jpg)
Spring 2013CEE 3804 Slide 38
Displaying Summaries in the Pivot Table (Excel 2007/2010)
Here we display the sum of the vehiclevalues for every type of vehicle andevery location
![Page 39: CEE 3804: Computer Applications](https://reader036.vdocuments.us/reader036/viewer/2022081513/56814e4a550346895dbbd204/html5/thumbnails/39.jpg)
Spring 2013CEE 3804 Slide 39
Finding the Average Value of Vehicles in the Fleet (Excel 2007/2010)
![Page 40: CEE 3804: Computer Applications](https://reader036.vdocuments.us/reader036/viewer/2022081513/56814e4a550346895dbbd204/html5/thumbnails/40.jpg)
Spring 2013CEE 3804 Slide 40
Finding the Average Value of Vehicles (Excel 2007/2010)
![Page 41: CEE 3804: Computer Applications](https://reader036.vdocuments.us/reader036/viewer/2022081513/56814e4a550346895dbbd204/html5/thumbnails/41.jpg)
Spring 2013CEE 3804 Slide 41
Try Other Formats of Your Own