Chap3.Data Extraction and Management

Introduction of Applied
Computing in Business
Trang, Ha Thi Thu (Ph.D)
SEM - HUST
LIST OF CONTENTS
01 02 04 05
WORKING BASIC DATA DATA
WITH FUNCTION IN 03 ANALYSIS MODELLING
SPREADSHEET EXCEL DATA EXTRACTION
AND
MANAGEMENT
2
Chapter 3: DATA EXTRACTION & MANAGEMENT
3.1. DATABASE OVERVIEW
3.2. DATA ORDER
3.3. DATA FILTER
3.4. DATABASE FUNCTION
3.5. DATA MANAGEMENT

3.1. DATABASE OVERVIEW
• Database means that the data must be in a table structure that has organized
records with labels and appropriate separation.
• In this Table, each row represents an individual record, and each column is a
different type of information (Field).
• For data that is in this structure, you can use Excel database functions to
manipulate and manage your tables much more efficiently.
3.2. DATA ORDERING
Method 1:
In the Taskbar, Choose Data  Sort & Filter  AZ (accending) or ZA (decending)
Method 2:
Home  Editing  Sort & Filter  Sort A to Z or Sort Z to A
3.2. DATA ORDERING
Method 3:
Data  Sort & Filter  Custom Sort
The Sort dialog box will appear.
Choose the column you want to sort by
• Sort On: sorting by Values, Cell Color, Font
Color , Cell Icon
• Order: sort by order from A to Z or Z to A, or
Smallest to Largest or Largest to Smallest or by
Custom List
• Click Add Level to add another column to sort
by.
3.2. DATA ORDERING
Method 3:
Data  Sort & Filter  Custom Sort Order  Custom List
3.2. DATA ORDERING
Method 4:
Data  Sort & Filter  Custom Sort  Sort left to right
3.3. DATA FILTERING
Auto Filter:
Data  Sort & Filter  Filter or Home  Editing  Sort & Filter  Filter.
3.3. DATA FILTERING
Auto Filter:
Data  Sort & Filter  Filter
Number and Text filter
3.3. DATA FILTERING
Auto Filter:
Data  Sort & Filter  Filter
Date filter
3.3. DATA FILTERING
Advance Filter:
Data  Sort & Filter  Advance Filter or Home  Editing  Sort & Filter 
Advance Filter.
• List Range: enter the address of the cell range or select it directly in
the worksheet by dragging through its cells.
• Criteria range: enter the reference for the criteria range, including the
criteria labels.
• Copy to: enter the address of the cell that is to form the upper-left
corner of the copied and filtered records or click this cell directly in the
worksheet.
3.3. DATA FILTERING
Building a criteria
• Create conditions and join them using AND and OR logical operators: The idea is that
every condition inside a row is connected with an AND logic, whereas every row is
connected with an OR.
AND/OR rule
customer name must begin with "MegaMart" AND
AND rule: OR rule: product name must begin with "Cookies"
customer name must begin with "MegaMart" customer name must begin with "MegaMart" OR
AND product name must begin with "Cookies" OR product name must begin with "Cookies" product name must begin with "Cookies" AND total
AND total must be greater than 500 OR total must be greater than 500. must be greater than 500.
3.3. DATA FILTERING
Building a criteria
• Use calculated criteria when filtering or
querying your list.
• Enter a logical formula that Excel can
evaluate as either TRUE or FALSE in the
Criteria Range under a made-up name that
is not any field name used in the data list
(Note, is not a field name in the data list)
• To use this calculated criterion, remember
to place the logical formula under a name
that isn’t used as a field name in the data
list itself.
3.3. DATA FILTERING
Example
3.4. DATABASE FUNCTIONS
Note: DCOUNT function counts matching records in a database using criteria and an optional field.
When a field is provided, DCOUNT will only count numeric values in the field.
Use DCOUNTA to count numbers or text values in a given field.
Note: The DGET function is

designed to extract a single value
based on matching criteria. When
more than one record matches
criteria, DGET will throw the
#NUM error.
3.5. DATA MANAGEMENT
Data Consolidate Data Cleaning Data Formatting

DATA CONSOLIDATE
• Consolidation tool allows you to gather together your data from separate worksheets
into a master worksheet.
• Consolidation function takes data from a series of worksheets or workbooks and
summaries it into a single worksheet that you can update easily.
DATA CONSOLIDATE
Consolidation steps:
• To start using the Consolidation tool, you need to select
an empty sheet in the workbook as your master
worksheet or add a new one if necessary.
• Choose Data --> Consolidate to view the Consolidate
dialog:
o Function: select the function that you want Excel to use to
consolidate the data (11 functions). For our data we want to add
up the values so we’ll set the Function to Sum.
o Reference: select the first data range to consolidate
o All references: select all the data sources to consolidate
o Top row: choose if you want to consolidate by row of data source
o Left column: choose if you want to consolidate by first left column
of data source (in our data, it is the value of “Mặt hàng” column)
If you want to update your consolidation table
automatically when the source data changes, select the
Create links to source data check box. If unchecked, you can
still update the consolidation manually.
DATA CLEANING
How to remove duplicates in Excel:
1. Click any single cell inside the data set.
2. On the Data tab, in the Data Tools group, click Remove Duplicates.
DATA CLEANING
3. Leave all check boxes checked and click OK.
(All check boxes ask Excel to remove all identical rows (blue) except for
the first identical row found (yellow).
DATA CLEANING
Note: To remove rows with the same values in certain columns, execute
the following steps. For example, remove rows with the same Last
Name and Country.  Check Last Name and Country and click OK.
CONDITIONAL FORMATTING
Conditional Formatting lets the user apply formatting to a cell or a
range of cells based on specific criteria.
There are various conditions that can be tested in order to apply the
formatting:
• Format cells based on their values
• Format cells that contain a certain text
• Format top/bottom-ranked cells in a range
• Format duplicates
• Format cells based on a formula
Conditional Formatting lets the user apply formatting to a cell or a
range of cells based on specific criteria.
Conditional Formatting with Formulas
You can use a formula to determine which cells to format. Formulas
that apply conditional formatting must evaluate to TRUE or FALSE.
Highlights all odd numbers by formular

=isodd(A1)
1. Select the range A1:E5.
2. Home  Styles group  Conditional
Formatting.
3. New Rule.
4. Select 'Use a formula to determine
which cells to format’.
5. Enter the formula =ISODD(A1)
6. Select a formatting style and click OK.
1. Select the range A1:E5.
2. Home  Styles group  Conditional
Formatting.
3. New Rule.
4. Select 'Use a formula to determine
which cells to format’.
5. Enter the formula =$C2=“USA”
6. Select a formatting style and click OK.

Chap3.Data Extraction and Management

Uploaded by

Copyright:

Available Formats

Chap3.Data Extraction and Management

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Chap3.Data Extraction and Management

Uploaded by

Copyright:

Available Formats

Introduction of Applied

3.2. DATA ORDER

3.3. DATA FILTER

3.4. DATABASE FUNCTION

3.5. DATA MANAGEMENT

Note: The DGET function is

Data Consolidate Data Cleaning Data Formatting

Highlights all odd numbers by formular

You might also like