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 Exporting Data
10.4 Creating Pivot Tables from External Data
10.5 Using Excel as a Database
10.6 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.

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 the Import options in Excel 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.

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 for three consecutive weeks.

For each employee, the start date and hours worked for three weeks 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
Figure 10.5

The text has now been imported to Excel.
11
Web Addresses

To import data from a webpage, use the Data > Import External Data >
New Web Query
–
–
–
–

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 and information
for MSFT stock reported by Quicken
– http://www.quicken.com/investments/quotes/?p=MSFT
13
Figure 10.8

New Web Query window
14
Figure 10.9

The location of the import in the Excel workbook is specified.
15
Figure 10.10

The data is now imported into columns on a spreadsheet.
16
Databases

To import data from a database, choose Data > Import External Data

An External Data toolbar also automatically appears next to the data.

Edit Query allows us to modify what part of the database we have
imported.
– A query is a search for a particular set of data from our database, similar to
filtering.

The Data Range Properties option gives us the opportunity to fine tune
the data we see.
17
Figure 10.12

A database is created in Microsoft Access titled Books.

It has a short record of some books with the fields: ISN, Title, Author,
and Copyright Year.

We first specify the location for the imported data.
18
Figure 10.13

If the query is not edited, the data is imported directly to the worksheet.
19
Figure 10.14

To edit the query, we first specify the type of database we are using.
20
Figure 10.15

We then select a file from the list of files which match our data source.
21
Figure 10.16

We then select which columns of data we want to appear in our final
table.
22
Figure 10.17

We now define the query by filtering the data.
23
Figure 10.18

We now specify how to sort the data in our final table.
24
Figure 10.19

We select to view the results of our query in Excel.
25
Figure 10.20

The imported data has now been modified after editing the query.
26
Exporting Data

To export data from Excel to a database, you have to use an Add-In
called AccessLinks.
– This Add-In must be downloaded and installed from the Microsoft webpage:
http://www.microsoft.com/downloads.

Data menu option and select from the three exporting methods: MS
Access Form, MS Access Report, or Convert to MS Access.
27
Creating Pivot Tables from External Data

In the Pivot Table Wizard, select External data source from the list of
options to use external data for the pivot table.

When prompted to select the actual data click Get Data.
28
Figure 10.25

Select data from an MS Acess database with data for a University
Information System.

Select the tables and columns you wish to use for the pivot table data
source.
29
Figure 10.27

Complete the layout and other options from the Pivot Table Wizard (as
was shown in Chapter 6) to complete the pivot table.
30
Using Excel as a Database

Sorting

Filtering

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.

Highlight the entire database, including the field names, then choose
Data > Sort from the menu.

The data can be sorted in Ascending or Descending order.
Multiple sorts can be made on the selected data.

32
Figure 10.28

The names of students are recorded in no particular order. We can sort
our entire database by the field of Student Name to place the names in
alphabetical order.
33
Figure 10.29(b)

We select the Student Names field from the list and the Ascending order
option.
34
Figure 10.30

The table is now sorted by the students’ names.
35
Figure 10.31(a)

We can also sort by more than one field.
36
Figure 10.31(b)

The table is now sorted by Class Averages in Descending order, with the
Student Names also sorted in Ascending order.
37
Sorting Options

If you do not select field names (or if there are none), then you can select
the No header row option when sorting.

You can also specify to sort from left to right instead of top to bottom.
38
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 a group of entries in our database that are
equal to a particular data entry within a field.

Highlight the entire database including column titles, then choose Data >
Filter > Auto Filter.
39
Figure 10.36


We can Auto Filter the student table.
Drop down buttons will appear for each field.
40
Figure 10.37

We can filter for a particular value in a selected field.
41
Figure 10.38

We can also specify a Top 10 filter for a selected field.
42
Figure 10.39

Or we can specify a Custom filter for a selected field.
43
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)
44
DFunctions (cont)

The criteria parameter must include a field name and criteria cell.

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.
45
Figure 10.40

Let us find the minimum Exam 2 score for students with a Class Average
above 85.0.
46
Figure 10.43

The DMIN function is used
as follows:
– =DMIN(database, field,
criteria)
– =DMIN(B3:I23, G3,
I21:I22)

We refer to the criteria
specified in the additional
rows below the table.
47
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
48
COUNT Functions (cont)

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)
49
COUNT Functions (cont)

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.
50
Figure 10.45

A COUNTIF example:
51
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.
52
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 > Validation from the menu.

There are three main tabs in this window: Settings, Input Message, and
Error Alert.
53
Data Consolidation

Data consolidation allows you to compare and combine multiple sources
of data into a new spreadsheet.
– Select Data > Consolidate in a new worksheet to create the consolidated
table.
54
Summary

You can import text files, webpage information, and database tables into Excel
using the Import External Data wizard. Queries can be made to data being
imported from a database.

Using the Access Links Add-In, data can be exported to a Microsoft Access
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.

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.
55
Additional Links

(place links here)
56