Transcript EIN 4905/ESI 6912 Decision Support Systems Excel

Spreadsheet-Based Decision Support Systems
Chapter 10: Working with Large Data
Prof. Name
Position
University Name
[email protected]
(123) 456-7890
Overview





10.1 Introduction
10.2 Importing Data
10.3 Creating Pivot Tables from External Data
10.4 Using Excel as a Database
10.5 Summary
2
Introduction

Import data from a text file, webpage, or database.

Create pivot tables using data from an external database.

Sort and filter a database table in Excel.

Create “official” Excel Tables to store and analyze data.

Work with database functions in Excel.

Apply database validation and consolidation in Excel.
3
Importing Data

Text Files

Web Addresses

Databases
4
Importing Data

In many cases, an Excel user may not be given data in a worksheet to
manipulate.

If this is the case, you can use Get External Data group of commands
on the Data tab of Excel Ribbon to transfer this data to a worksheet
where Excel analysis tools can be applied.

There are three sources from which you can import data into Excel
– Text files
– Web addresses
– Databases
5
Text Files

To import data from a text file, use the Text Import Wizard.

Click on: Data > Get External Data > From Text command. You are
then prompted to open a text file in Excel.

There are three steps to import text files using the wizard.
– Step 1: Specify how the data will be organized
– Step 2: Specify how to separate the data
– Step 3: Apply particular numerical formatting
6
Figure 10.1

Consider a text file which records the number of hours ten employees
worked during three consecutive weeks.

For each employee, the start date and hours worked are recorded.
7
Figure 10.2

Step 1: Specify that the text is delimited.
8
Figure 10.3

Step 2: Specify that the data is separated by tabs.
9
Figure 10.4

Step 3: Date formatting is applied to the “MDY” column.
10
Figures 10.5 and 10.6

The text has now been imported to Excel.

The employer can now use the Excel AVERAGE() function to compute the
averages.
11
Web Addresses

To import data from a webpage, use the Data > Get External Data >
From Web command on the Ribbon.
–
–
–
–

Enter the web address which contains the data you want to import.
Select what sections of data you want to import.
Click Import.
Select a cell in the worksheet for where you want this imported data to be
placed.
The data will be imported there and separated into columns as done in
the webpage.
12
Figure 10.7

The following web address contains current stock quotes reported by
CNN: http://money.cnn.com/

Paste the web
address into the
“Address” window
of the New Web
Query dialog box.
13
Figures 10.8 and 10.9

The location of the import in the Excel workbook is specified.

The data is now imported into columns on a spreadsheet.
14
Figure 10.10

We can now further format this data for future analysis.

To repeat a query:
– Select the range of queried data in the worksheet.
– Click on: Data > Connection > Refresh All > Refresh command.
15
Databases

To import data from an Access database, click on: Data > Get External
Data > From Access command on the Ribbon.

To import data from another database we click on: Data > Get External
Data > From Other Sources command on the Ribbon.

Use Edit Query to modify some parts of a database already imported.
– A query is a search for a particular set of data from our database, similar to
filtering.
16
Import Data from Access

A database, titled Books, is created in Microsoft Access.

It keeps a record of books. These are the fields of the database: ISBN,
Title, Author, and Copyright Year.

Click on: Data > Get External Data > From Access command.

Specify the location of the data imported.
17
Figure 10.12

The imported data is copied to the worksheet.
18
Import Data from Other Sources



Click on: Data > Get External Data > From Other Sources command.
Select From Microsoft Query from the drop-down menu.
Specify the type of database being used.
19
Figure 10.14

Select a file from the list of files which match the data source.
20
Figure 10.15

Select which columns of data should appear in the final table.
21
Figure 10.16

Define the query by filtering the data.
22
Figure 10.17

Specify how to sort the data in the final table.
23
Figure 10.18

Select to view the results of the query in Excel.
24
Figure 10.19

The imported data has now been modified after editing the query.
25
Creating Pivot Tables from External Data

Use the University database to create a table that summarizes the
distribution of students by department, and gender.
–
–
–
–
Click on: Insert > Tables > PivotTable command.
Select Use an external data source from the Pivot Table Dialog Box.
Select a location for the pivot table in the existing worksheet.
Click Choose Connection to specify where the actual data is saved.
26
Figure 10.21

Select data source as a MS Access database with data for a University
Information System.
27
Figure 10.22

Select one of the existing tables and queries of the selected database.
28
Figure 10.23

We can use the PivotTable Field List to build the pivot table.
29
Figure 10.24

Specify the layout and location of the pivot table and other options the
(as was shown in Chapter 6) to complete the pivot table.
30
Using Excel as a Database

Sorting

Filtering

Excel Tables

DFunctions

Data Validation

Data Consolidation
31
Sorting

Sorting is the ordering of all entries in a database by a particular field,
where a field is the name of a category.
32
Sorting

Highlight the entire database, including the field names, then click on
the Data > Sort & Filter > Sort command on the Ribbon.

Sort dialog box appears:

The entire database is sorted by Student Name. Names are listed in an
alphabetical order.
33
Figure 10.27

The table is now sorted by the students’ names.
34
Figure 10.28(a)

Sort the database by more than one field.
35
Figure 10.28(b)

The table is sorted by Class Averages in a Descending order, and by
Student Names in an Ascending order.
36
Sorting Options

My data has headers option informs Excel whether or not column
headers are included on the data set.

If we uncheck the My data has headers checkbox, there would only be
column names in the field list.
37
Sorting Options

We can also sort within a row.

The field lists show row numbers instead of column numbers.
38
Figure 10.32

The table is now sorted by Row 4 on Z to A order.
39
Filtering

Filtering differs from sorting because it selects a specified set of data
from the database instead of ordering the entire database.

Filtering allows us to select rows in a database by a specific value for
one or more fields.

Highlight the entire database including column titles, then choose Data >
Sort & Filter > Filter command on the Ribbon.

Drop-down arrows appear next to each field of the table.
40
Figure 10.33
41
Figure 10.34(a)

Use the filtered
field drop-down
menu to filter for
a particular
value in a
selected field.
42
Figure 10.34(b)

The database is filtered to only display data entries that have a Class
Average equal to 88.6.
43
Figure 10.35

On the filtered field drop-down menu, upon the selection of the Number
Filters option, a flyout menu appears that presents a number of options
we can use when filtering a database.
44
Figure 10.36

Use the Top 10 Items filter for the Class Average field.
45
Figure 10.37

Use the Custom filter on the Class Average field.
46
Excel Tables

Excel is designed to store data in a table.

A table is a range which contains data organized in rows and columns.

Each row and column has a heading to describe the content of the
table.

To create a table:
– Select a range of data.
– Click on the Insert > Tables > Table command on the Ribbon.
47
Figure 10.38
48
Figure 10.39


It is easy to add new columns and build calculated columns on Excel
tables.
Calculate the average of the grades students made on their assignments.
49
Figure 10.40

When a table is created using data from multiple sources, duplicated
rows or columns of data may be created.

To remove duplicates, click on the Table Tools Design > Tools >
Remove Duplicates command on the Ribbon.
50
DFunctions

There is a group of Excel functions which are tools specifically for
working with Excel as a database; we call these functions Dfunctions.

These are specific functions designed for use with databases. They
include
–
–
–
–

DSUM
DAVERAGE
DMIN
DMAX
Dfunctions differ from the previously described functions because they
specify certain criteria before performing the function.
– For example: =DSUM(database, field, criteria)
51
DFunctions (cont’d)

The criteria parameter is a range of cells that includes a cell or cells with
a field name and a cell or cells below the field name with a specified
criterion.

In preparation for using Dfunctions, we must add a few rows to our
database. These rows contain our criteria and must repeat our field
names.
52
Figure 10.41

Find the
minimum Exam
2 score for
students with a
Class Average
above 85.0.
53
Figure 10.42



Click on the Formulas > Function Library > fx Insert Function
command from the Ribbon.
Find the function category labeled Database.
We can now view all of the Dfunctions and choose DMIN from the list.
54
Figure 10.43

The DMIN function is used as follows:
– =DMIN(database, field, criteria)
– =DMIN(B3:I20, G3, I21:I22)

We refer to the criteria specified in the additional rows below the table.
55
Figure 10.44

The DMIN function uses the criteria in the Class Average field and the
criteria in the Attendance field.
56
COUNT Functions

Another group of functions, which are also useful to use with database
data in Excel, are the COUNT functions.

In this group there are four main functions
–
–
–
–
COUNT
COUNTA
COUNTBLANK
COUNTIF
57
COUNT Functions (cont’d)

The COUNT function simply counts the number of cells with numerical values in
a given range.
– =COUNT(range)

The COUNTA function counts all of the cells with data of any kind in a given
range of cells.
– =COUNTA(range)

The COUNTBLANK function will count the number of blank cells in a given
range.
– =COUNTBLANK(range)

The COUNTIF function will count the number of cells in a given range which
meet a specified criteria.
– =COUNTIF(range, criteria)

The criteria can contain some helpful characters such as “*” for a sequence of
unknown values or “?” as a single wild card value.
58
Figure 10.45

Using the COUNT, COUNTA, and COUNTBLANK functions
59
Figure 10.46

A COUNTIF example:
60
SUMIF Function

The SUMIF function will sum the values in a given range which meet a
specified criteria.
– =SUMIF(range, criteria, sum_range)

This function is also similar to the DSUM function we saw earlier in this
section.

Even though it is used in the same manner in which the DSUM function
is used, it only allows for single criteria.

Therefore, when using Excel as a database, we recommend using the
DSUM function instead of the SUMIF function so as to handle multiple
criteria.
61
Data Validation

Data validation enforces a certain format or type of data to be entered by
the user for particular input.
– To use data validation, select the cell(s) you want to validate and then click
on Data > Data Tools > Data Validation command on the Ribbon.

There are three main tabs in this window: Settings, Input Message, and
Error Alert.
62
Figure 10.48

There is a list of criteria including Whole Numbers, Dates, Text Lengths,
and others.
63
Figure 10.49

Depending on which criteria are selected from the list, we will have a few
more options to further specify these criteria.

For example, if we choose Whole Number from the list, we then choose
from a list of inequalities and provide some numerical bounds.
64
Figure 10.50

The Input Message tab allows us to create a comment that will appear
next to the cell after it is selected.

This message is intended to guide the user to enter the correct data.
65
Figure 10.51

The Error Alert allows us to display a message to the user if any data has
been incorrectly entered.
66
Figures 10.52 and 10.53


We have a record of customer orders. For each order, the date, quantity,
and price of the order are noted.
Maximum of 5 products can be sold in any one order.
– The Input Message for this validation appears when a cell in the Quantity
column is selected.
– The Warning Message created in the Error Alert tab will appear if we enter a
number greater than 5.
67
Figures 10.54 and 10.55



We could also validate the Date column in this example by choosing the
Date criterion.
Choose the Information alert type.
The Input Message and alert are shown.
68
Figure 10.56

With the Custom criterion, the user applies a formula to the first cell in
the selected range that is being validated.
69
Figures 10.57 and 10.58

The List criterion allows us to create a list box, or drop-down box, of
options for users to choose from as their entry value.

Then, we can set the Input Message and Error Alert so that the users
know they can only enter one option from the provided list.
70
Data Consolidation

Data consolidation allows you to compare and combine multiple sources
of data into a new spreadsheet.
– Select Data > Data Tools > Consolidate command on the Ribbon to create
the consolidated table.

Suppose that we have monthly sales recorded for various products in two
different marketing regions.
71
Figure 10.59(a)
72
Figure 10.59(b)
73
Figures 10.60 and 10.61

The data consolidation tool provides a list of functions that can be
applied to the data as it is consolidated.

After selecting the function, we must choose the references of the data
that we want to consolidate.
74
Figure 10.62

The consolidated table.
75
Summary

You can import text files, webpage information, and database tables into Excel
using the commands listed in the Data > Get External Data tab on the Ribbon.
Queries can be performed to data being imported from a database.

Pivot Tables can use external data, such as from databases, as their source.

Sorting and Filtering along with Dfunctions can be used on large data in Excel.

The “official” Excel Table makes the process of organizing and manipulating data
easier.

Data Validation allows you to enforce a certain format or type of data to be
entered by the user for particular input.

Data Consolidation compares and combine multiple sources of data into a new
spreadsheet.
76
Additional Links

(place links here)
77