Transcript Using Excel as a database tool - Ohio State Computer Science and

Using Excel as a database tool
Objectives:
•
•
•
•
•
•
•
•
Sorting data
Filtering data
Using Subtotals
Pivot Tables
Importing and Exporting Files
Using Text Functions to Modify Data
Data Analysis Tools
Writing a Macro to Manipulate Data
CS&E 1111 Excel Database Features
A Spreadsheet or a Database Application?
Use a Spreadsheet for:
Use a Database
Management System
(DBMS) for:

Large amounts of data

Multiple tables of
related information

Complex querying
(multidimensional)



Small to medium
amounts of data
1-3 Tables of related
information
Simple sorting, filtering,
and subtotaling
Often analysts use both tools to solve a problem, keeping track
of data in a database application and copying the relevant
information for further analysis in Excel
CS&E 1111 Excel Database Features
To demonstrate many of the data manipulation
features of Excel consider the following data
table listing a variety of snack products
CS&E 1111 Excel Database Features
Excel can
import data from
both fixed width
and delimited files



Choose Data Ribbon, click From Text button in the
Get External Data group
Select the data file to be imported
Complete the steps of the Text Import Wizard

Be sure to specify field type so numbers like zip codes
won’t lose leading zeros.
CS&E 1111 Excel Database Features
4
Excel provides multiple level data sorting by values,
cell color, font color or cell icon
1. A simple single field Sort – use
Data Ribbon.
buttons from the
2. Use the Sort button to sort
or Custom Sort to set major
And minor sorts.
To sort by category then by supplier – first add the category
Level then the supplier level. Each level can be sored in
Ascending or Descending order.
CS&E 1111 Excel Database Features
Excel’s Filter tool allows the user to
specify criteria in multiple fields
•Select the Filter button from the Data ribbon
•In the desired field click on the drop down arrow
•Select the filter desired, and specify as needed
•Applying multiple filters will be treated as an AND
CS&E 1111 Excel Database Features
Advanced Filters allow the user to create OR
relationships in multiple fields or to vary criteria
based on calculations
•Setup your filter range and input the criteria. Criteria in the
same row acts as AND, criteria on adjacent rows acts as OR
•Select the Advanced button from the Sort & Filter Group of
the Data ribbon
CS&E 1111 Excel Database Features
Many of these same features and
more can be accessed by turning
your list into a Data Tables features
Using the Insert table from the insert ribbon – Excel
defines the area as a table and gives you multiple
tools for formatting and managing the data – from a
context specific ribbon
CS&E 1111 Excel Database Features
Excel can automatically create group Subtotals to
perform sums, counts, averages, etc.
1. Sort by your Group field
2. Select Subtotals button on
the Data ribbon
3. Choose Group field
(change)
4. Select a function
5. Select a Subtotal field
6. Click OK to calculate
7. Repeat the procedure to
calculate a 2nd aggregate
function
CS&E 1111 Excel Database Features
Data-Subtotals can be displayed on 3 levels
CS&E 1111 Excel Database Features
Pivot Table, a multidimensional summary




Select Pivot Table from the Insert ribbon – Tables group.
Specify Pivot Table as the object
Select the data range and whether the table will
be created on a new worksheet.
Arrange data fields on the table
CS&E 1111 Excel Database Features





Drag the field you wish to use
for rows to Row Labels box
Drag field you wish to use for
column to Column Label box
Drag fields you wish to
perform calculations to either
row/column as desired.
To change the calculation type
– right click on the value on
the table and select Value
Field Settings (or Field
Settings on Options ribbon.
Use Design ribbon to format
Arranging data
fields
CS&E 1111 Excel Database Features
The
resulting
Pivot Table
• Data can also be
displayed as a Pivot
chart.
• Both Pivot tables
and charts can be
used in Excel and in
Access.
• If the data is later
modified, the button
to update the values
Data summarized by
category by supplier
with discontinued filter
or insert Slicer
CS&E 1111 Excel Database Features
Using Text Functions to modify data
We often need to modify data formats for purposes
of importing or exporting files.
 Registrar’s site downloads: [email protected]
 Site login requires: jones.23 or last, first name
Use text
functions to
combine and
separate fields
=CONCATENATE(A2, ", ", B2)
=LEFT(C2, FIND("@",C2) -1)
CS&E 1111 Excel Database Features
Common text functions:
Syntax
Description
LEFT(text, [num_chars])),
Returns the number of characters specified starting from the
beginning of the text string
RIGHT(text, [num_chars])
Returns the number of characters specified starting from end
of the text string
MID(text, start_num,
num_chars)
MID returns a specific number of characters from a text
string, starting at the position you specify
CONCATENATE(text1,[text2],
Joins up to 255 text strings into one text string
…)
TRIM(text)
Removes all spaces from text except for single spaces between
words
VALUE(text)
Converts a text string that represents a number to a number.
TEXT(value, format_text)
The TEXT function converts a numeric value to text
LEN(text)
LEN returns the number of characters in a text string.
UPPER(text), LOWER(text),
Converts text to uppercase/lowercase
FIND(find_text, within_text,
[start_num])
FIND locates one text string within a second text string
CS&E 1111 Excel Database Features
Then there’s copying and “copying”




Copy – leave it up to office to decide?
Copy as a picture or as text – retains none of
the original features.
Embed - copies objects such as Excel
charts/worksheets in its original source
application format. If you double click on the
object, it allows you to edit the object using
the Excel features (displays Excel menus).
Link - Dynamically “links” to an object in
the source document – so it cannot be
modified independent of the original object.
CS&E 1111 Excel Database Features
Excel can export data into many commonly
used file formats.


Select File, the
Save As
Choose the
appropriate
format




CSV comma
delimited file
XML format
TXT format –
tab delimited
HTML – web
page
Select the file type from the
Save as type box
CS&E 1111 Excel Database Features
Data Analysis
Tools







Histograms
Regression Analysis
Moving Average
t-Tests
Random Number
Generation
Anova
Fourier Analysis
CS&E 1111 Excel Database Features
To Load/Use Data Analysis Tools

To Load the Analysis ToolPak for 1st time





From the File Ribbon select Options
Select Add-ins
Select Excel Add-ins in the Manage dropdown list
Click on the Go button at the bottom
Select one or more Add-ins to load
To use the tools click on
Data Analysis button
in the Analysis Group
of the Data Tab.
CS&E 1111 Excel Database Features
Data Analysis
Tools – Histogram
Select the appropriate data
analysis tool, fill in the
required parameters, view
the resulting tables/charts
Histogram
20
15
10
5
M
or
e
23
0
21
0
19
0
17
0
14
0
10
0
0
0
Frequency
25
Bin
CS&E 1111 Excel Database Features
An Excel Macro can record the steps we
take and save them for later execution
This comes in handy if you are performing the same
steps over and over again.
 Example – in a single macro can:





Can import data from one or more files
Sort the data
Manipulate the information info appropriate fields
To record a Macro go to the View Ribbon and click
on the Macro button, the Record Macro. The
computer generates VBA code for the steps.
Control key shortcuts and/or toolbar buttons can be
setup to execute a Macro
CS&E 1111 Excel Database Features
Using Mail Merge in MS Word with
Excel/Access lists to create customized
documents, labels & envelopes
Create individualized letters from one
template substituting “customized”
fields from a database or spreadsheet.
Filter out names from a mail list that
meet specific criteria.

CS&E 1111 Excel Database Features
1. The Data Source: Preparing your data

If using a spreadsheet make sure all
the data is in adjacent columns and
rows. It is recommended that you
name your data range
CS&E 1111 Excel Database Features
2. Link the data source to the document

Open Word and select the Mailings ribbon. Then
click on Select Recipients. Then choose whether
you are creating a new list, using an existing list
or Outlook contacts. If using an existing file –
select from the correct folder.
CS&E 1111 Excel Database Features
3. Setting up the template document &
performing the merge
Type your message into Word.
 Insert merge fields from the data source by
clicking on the Insert Merge Field button on the
Mailing Ribbon. All available fields will be
listed.
 When you are done – use the Preview Results
button and record buttons to see the merged
document.
 Click the Finish & Merge button to Edit, Print or
Emails documents

CS&E 1111 Excel Database Features
Excel data capabilities:

As a database for
limited amounts of
info:





Multiple Sorts
Filters & Advanced
Filters
Subtotals
Pivot Tables
Importing and
Exporting csv files

Other features
using Excel data




Data Analysis tools
(histogram)
Text functions to
manipulate data
Macros to
automate repeated
procedures
Mail merge to
create customized
mailings
CS&E 1111 Excel Database Features