狠狠撸

IT445
Decision Support Systems
College of Computing and Informatics
1

Week 4
Chapter 3 – part1 (sections 3.2 to 3.6): Nature
of Data, Statistical Modeling, and Visualization
Analytics, Data Science, & Artificial Intelligence
Systems For Decision Support
Chapter 3, 5, 11 & 13: Getting Started with Data
Analysis
Excel Data Analysis For Dummies
This Presentation is mainly dependent on this textbook
2

Contents
o 3.2 – Nature of Data
o 3.3 – Simple Taxonomy of Data
o 3.4 – Art & Science of Data Preprocessing
o 3.5 –Statistical Modelling for Business Analytics
o 3.6 – Regression Modelling for Inferential Statistics
o Introducing Excel Tables and Cleaning Data using Excel
o Analyzing Data with Statistics and Inferential Statistics using Excel
3

Weekly Learning Outcomes
1. Understand the nature of data as they relate to business intelligence (BI) and analytics
2. Learn the methods used to make real-world data analytics ready
3. Describe statistical modelling and its relationship to business analytics
4. Learn about descriptive and inferential statistics
5. Demonstrate the ability in utilizing Excel to perform data preprocessing and data
analysis
4

Required Reading
? Chapter 3 (sections 3.2 to 3.6): “Nature of Data, Statistical Modeling, and Visualization” from “Analytics,
Data Science, & Artificial Intelligence: Systems for Decision Support”.
? Chapter 3, 5, 11 & 13: “Getting Started with Data Analysis” from “Excel Data Analysis For Dummies”
Recommended Video
? How to Clean Up Raw Data in Excel
https://www.youtube.com/watch?v=3es54FafNC0
? 10 Super Neat Ways to Clean Data in Excel
https://www.youtube.com/watch?v=e0TfIbZXPeA
? Business Analytics with Excel | Data Science Tutorial | Simplilearn
https://www.youtube.com/watch?v=W3vrMSah3rc
? Predictive Modelling in Excel – How to Create a Linear Regression Model from Scratch
https://www.analyticsvidhya.com/blog/2020/06/predictive-modeling-excel-linear-regression/
5

Chapter 3 – part1 (sections 3.2 to 3.6): Nature of
Data, Statistical Modeling, and Visualization
Analytics, Data Science, & Artificial Intelligence
Systems For Decision Support
6

3.2 Nature of Data
? Overview of the Nature of Data
? The Characteristics of Analytics–Ready Data
7

Overview of the Nature of Data
? Data is the main element for BI,
data science, and business
analytics. It can be small or large;
structured or unstructured.
? Modern-day data collection
mechanisms use radio frequency
identification (RFID)–based
computerized networks. These
automated data collection
systems collects volumes of data
and enhances its quality and
integrity.
8

Overview of the Nature of Data (cont.)
? Data must be analytics-ready, relevant, comply with quality/quantity metrics,
and structured in place with key variables and normalized values.
? Predictive algorithms require a flat file and target variable; thus data analytics
for prediction must be in that form.
? It is imperative to match the data to the needs and wants of a specific
predictive algorithm and/or a software tool.
9

B. The Characteristics of Analytics – Ready Data
o Source Reliability: The originality/appropriateness of data storage medium
o Content Accuracy: Data is a good match for the analytics problem
o Accessibility: Data is easily and readily obtainable
o Security & Privacy: Data is secured, accessible to those with authorization
o Richness: All required data elements are included in the data set
o Consistency: Data is accurately collected and combined/merged
o Currency/Timeliness: Data is up-to-date for a given analytics model
o Granularity: variables are defined at the lowest level of detail for their use
o Validity: match/mismatch between actual & expected values of a variable
o Relevancy: variables in the data set are relevant to the conducted study
10

3.3 Simple Taxonomy of Data
? Taxonomy of Data Overview
11

Taxonomy of Data Overview
? The term data refers to a collection of facts usually obtained as the result of experiments,
observations, transactions, or experiences.
? Data can be classified as structured, unstructured or semistructured. It can also be
classified as static or dynamic (i.e., temporal or time series).
? Unstructured/semistructured data is composed of any combination of textual, imagery,
voice, and Web content.
? Data types include textual, spatial, video/voice, and need to be converted into
categorical/numeric representation before processing by analytics methods.
? Predictive analytics/ data mining methods and machine-learning algorithms are very
selective about the type of data that they can handle.
? Incompatible data types lead to incorrect models or halt model development.
12

Taxonomy of Data Overview (cont.)
o Nominal Data: measurements of
simple codes assigned to objects as
labels, which are not measurements.
For example, the variable marital
status.
o Ordinal Data: codes assigned to
objects or events as labels that also
represent the rank order among
them. For example, the pain scale.
? Categorical/Discrete Data: labels of multiple classes used to divide a variable
into specific groups, and have finite number of values with no continuum.
13

Taxonomy of Data Overview (cont.)
? Numeric Data: measures on a specific
scale that allow for interim values.
o Interval Data: variables that can be
measured on interval scales. An
example is temperature Celsius scale
as there is not an absolute zero
value.
o Ratio Data: the ratio between a
magnitude of a continuous quantity
and a unit magnitude (E.g. mass,
length, time). The distinguishing
feature is the possession of a
nonarbitrary zero value.
14

3.4 Art & Science of Data Preprocessing
? Steps for Data Preprocessing
15

A. Steps for Data Preprocessing
? Data’s original form can be inaccurate. Data preprocessing can convert
the raw data to a form for analytics algorithms.
? Steps of Data Preprocessing:
1. Relevant data is collected, records/variables are selected, and the
multiple data sources are integrated/merged.
2. Data is cleaned. Values are identified, while missing ones are imputed
or ignored. Noisy values in the data are identified and smoothed out.
3. Data is transformed. It is normalized between a minimum and
maximum for all variables to mitigate potential bias. Or it undergoes
discretization.
4. Data reduction. One can use literature; consult experts; or a
combination of techniques to reduce data dimensions to more
manageable subsets.
16

3.5 Statistical Modelling for Business Analytics
? Statistics Overview
? Descriptive Statistics for
Descriptive Analytics
? Measures of Centrality Tendency
? Arithmetic Mean
? Median
? Mode
? Measures of Dispersion
? Range
? Variance
? Standard Deviation
? Mean Absolute Deviation
? Quartiles and Interquartile Range
? Box-and-Whiskers Plot
? Shape of a Distribution
17

Statistics Overview
? Statistics —mathematical techniques to
help interpret data—and statistical
methods (E.g., regression, k-means
clustering) is part of descriptive
analytics.
? Descriptive analytics includes statistics
& online analytics processing (OLAP)
? OLAP: Analyzing, characterizing, and
summarizing structured data stored in
organizational databases using cubes.
The OLAP branch of descriptive analytics
has also been called business
intelligence.
18

Statistics Overview (cont.)
? Statistics: Helps to characterize data,
either one variable at a time or
multivariable, all together using either
descriptive or inferential methods.
oDescriptive statistics is all about
describing the sample data.
oInferential statistics is about drawing
inferences or conclusions about the
characteristics of the population.
19

Descriptive Statistics for Descriptive Analytics
? Descriptive statistics describes the basic characteristics of the data at hand.
? Descriptive statistics summarizes data to draw understandable patterns, and doesn’t
allow making conclusions beyond the sample of the analyzed data.
? In business analytics, it helps understand and explain/present our data in a
meaningful manner using aggregated numbers, data tables, or charts/graphs.
? This helps in the decision-making processes, and data scientists to characterize and
validate the data for more sophisticated analytics tasks.
? Descriptive statistics allows analysts to identify data concertation, outliers, and
unexpectedly distributed data values for numeric variables.
? Methods in descriptive statistics are classified as measures of central tendency or
measures of dispersion.
20

Measures of Centrality Tendency
? Measures of centrality are the mathematical methods by which we estimate
or describe central positioning of a given variable of interest.
? A measure of central tendency is a single numerical value that describes a set
of data by simply identifying or estimating the central position within the data.
? The mean is the most commonly used measure of central tendency. Median
and mode are also used to describe the centrality of a given variable.
? Although, the mean, median, and mode are all valid measures of central
tendency, under different circumstances, one of these measures of centrality
becomes more appropriate than the others.
21

Arithmetic Mean
? The arithmetic mean is the sum of all the values divided
by the number of observations in the data set. It is used
with continuous or discrete data.
? For a given variable x, if we happen to have n values, we
can write the arithmetic mean of the data sample as
follows:
? The mean is calculable for both interval- and ratio-type
numeric data.
? One major downside is that the mean can be affected by
outliers, which pull the mean in their direction and bias the
centrality representation.
22

Median
? The median is the number in the middle of a given set of
data that has been arranged/sorted in order of magnitude.
? If the number of observations is an odd number just sort
the observations based on their values and pick the value
right in the middle.
? If the number of observations is an even number, identify
the two middle values, and then take the simple average of
these two values.
? The median is meaningful and calculable for ratio, interval,
and ordinal data.
? Contrary to the mean, the median is not affected by
outliers or skewed data.
23

Mode
? The mode is the observation or value that occurs
most frequently in a data set.
? The mode is most useful for data small data sets of
unique values.
? Although useful for nominal data, mode is not a good
representation of centrality, and should not be used as
the only measure of central tendency.
24

Which central tendency measure is the best?
? Use the mean when data doesn’t have outliers or
significant skewness
? Use the median when the data have outliers and/or it is
ordinal in nature
? Use the mode when the data are nominal.
? Best practice is to use all three together.
25

Measures of Dispersion
? Measures of dispersion are mathematical methods used to estimate the degree
of variation in a given variable.
? To describe this dispersion, a number of statistical measures are developed; the
most notable are range, variance, and standard deviation.
? The measures of data dispersion are important as they give us an indication of
how well the centrality measures represent the sample data.
? If the dispersion of values is large, the mean is not a good representation of the
data. Large dispersion indicates large differences between individual scores.
? It is a positive sign to see a small variation within each data sample, as it may
indicate homogeneity, similarity, and robustness within the collected data.
26

Range
? The range is the simplest measure of dispersion. It is
the difference between the largest and the smallest
values in a given data set (i.e., variables).
? We calculate range by simply identifying the
smallest value in the data set (minimum), identifying
the largest value in the data set (maximum), and
calculating the difference between them
? Range = maximum - minimum
27

Variance
? Variance is a method used to calculate the deviation of all data points in a given
data set from the mean.
? The larger variances indicate more dispersion, whereas smaller variances indicate
compression.
? The formula for a data sample can be written as:
? Because differences are squared, larger deviations from
the mean contribute more to the value of variance.
? Therefore, instead of variance, we use a more meaningful
dispersion measure, called standard deviation.
28

Standard Deviation
? The standard deviation is a measure of spread of
values within a set of data.
? It can be calculated by simply taking the square
root of the variations.
? The following formula shows the standard
deviation from a given sample of data points:
29

Mean Absolute Deviation
? Mean absolute deviation can also be used to measure dispersion in a data set. It is
a simpler way to calculate the overall deviation from the mean.
? The mean absolute deviation is calculated by measuring
the absolute values of the differences between each data
point and the mean, then summing them.
? This process provides a measure of spread without being
specific about the data point being lower or higher than the
mean.
? The following formula shows the calculation of the mean
absolute deviation:
30

Quartiles and Interquartile Range
? Quartiles help identify spread within a subset of the data.
? A quartile is a quarter of the number of data points given in a data set;
determined by sorting the data and splitting into four disjoint smaller sets.
? Quartiles are much less affected by outliers or skewness than the equivalent
measures in the whole data set.
? A common way of expressing quartiles is as an interquartile range. This
describes the difference between the third quartile (Q3) and the first quartile
(Q1), telling us the range of the middle half of the scores in the distribution.
? The quartile-driven descriptive measures (both centrality and dispersion) are
best explained with a popular plot called a box-and-whiskers plot.
31

Box-and-Whiskers Plot
? The box-and-whiskers plot is a graphical
illustration of several descriptive statistics
about a given data set.
oThe y-axis is the measure of magnitude (the
numerical value of the variable), and the x-axis
shows different classes such as categories.
oThe plot shows the centrality (mean, median)
as well as the dispersion (the density of the
data within the middle)
32

Box-and-Whiskers Plot (cont.)
o The minimum and maximum ranges (shown as
extended lines from the box; calculated as 1.5
times the upper or lower end of the quartile box)
oThe outliers that are larger than the limits of the
whiskers.
oThe length of whiskers on the side of the box
indicate the skewness.
33

Shape of a Distribution
? Distribution is the frequency of data points plotted over labels/numerical
ranges. A normal distribution; it is symmetric on both sides of the mean.
? As dispersion increases, so does standard deviation, and distribution looks
wider.
? There are two commonly used measures to calculate the shape
characteristics of a distribution: skewness and kurtosis.
34

Shape of a Distribution (Skewness)
? Skewness is a measure of asymmetry in a
unimodal distribution of the data.
? If the distribution sways left, then it
produces a positive skewness measure; if
the distribution sways right, then it
produces a negative skewness measure.
35
In Figure 3.9:
(c) represents a positively skewed distribution whereas (d) represents a negatively skewed distribution.
both (a) and (b) represent perfect symmetry and hence zero measure for skewness.

Shape of a Distribution (Kurtosis)
? Kurtosis measures the degree to which a distribution is more or less
peaked than a normal distribution.
? A positive kurtosis indicates a relatively peaked/tall distribution, a negative
kurtosis indicates a relatively flat/short distribution. As a reference point, a
normal distribution has a kurtosis of 3. The formula for kurtosis:
36

3.6 Regression Modelling for Inferential Statistics
? Overview
? How Do We Develop the Linear Regression Model?
? How Do We Know If the Model Is Good Enough?
? The Most Important Assumptions in Linear Regression
? Logistic Regression
? Time-Series Forecasting
37

Overview
? Regression is a statistical technique to model the dependence of a variable
(response or output variable) on one (or more) explanatory (input) variables.
? Relationships between variables can be represented as a linear function.
? Regression captures the functional relationship between characteristics of the
real world and describe this relationship with a mathematical model.
? It can be used for:
o Hypothesis Testing: Investigating the potential relationships between
different variables
o Prediction/Forecasting: Estimating response variable values based on
explanatory variables
38

Overview
? Correlation vs. Regression:
o Correlation makes no a prior assumption of variable dependency and gives an
estimate on the degree of association between the variables.
o Regression describes the dependence of a response variable on explanatory
variables where it assumes a causal effect from the explanatory variable(s) to
the response variable.
? Simple vs. Multiple Regression:
o Simple regression is when the equation is built between one response variable
and one explanatory variable.
o Multiple regression is the extension of simple regression when the
explanatory variables are more than one.
39

How Do We Develop the Linear Regression Model?
? To understand the relationship
between two variables, draw a
scatter plot where the y-axis is
the response variable values and
the x-axis is the explanatory
variable values.
? Simple regression tries to find
the signature of a straight line
passing through the plotted dots
in a way that minimizes distance
between dots and line.
40

? The most commonly used method to identify
regression is the ordinary least squares (OLS).
? OLS aims to minimize the sum of squared residuals
and leads to a mathematical expression for the
estimated value of the regression line (b
parameters).
41

? Simple linear regression:
? Ordinary Least Squares methods (OLS) determines values of two coefficients,
the simple equation is used to forecast the values of y for given values of x.
? The sign and the value of reveal the direction and the strengths of
relationship between the two variables.
? If the model is of a multiple linear regression type, then there would be one
coefficient multiplied with each additional explanatory variable. Then, summed
together to establish:
42

How Do We Know If the Model Is Good Enough?
? Regression model must be assessed for the degree it represents the response
variable. A fitting regression model predicts values close to observed values.
? For numerical assessment, , overall F-test, and root mean square error (RMSE)
are used in evaluating the fit of a regression model
? has an intuitive scale, and ranges from 0 to 1; 0 indicates the model’s
relationship/prediction is not good, and 1 indicates a perfect fit.
? An value of 0.3 in social sciences can be considered good enough fit, but a 0.7
in engineering might be considered as not a good enough fit.
? The improvement in the regression model can be achieved by adding more
explanatory variables or using different data transformation techniques, which
would result in comparative increases in an value.
43

The Most Important Assumptions in Linear Regression
? Linear regression models have highly restrictive assumptions. The validity of
the linear model depends on ability to comply with the assumptions:
1. Linearity: relationship between response and explanatory variables is linear.
2. Error Independence: Response variable errors are uncorrelated.
3. Error Normality: The errors of the response variable are normally distributed.
4. Constant Variance/Homoscedasticity: The response variables have the same
variance in their error regardless of explanatory variables’ values.
5. Multicollinearity: The explanatory variables are not correlated. This can be
triggered by having two or more perfectly correlated explanatory variables
presented to the model.
44

Logistic Regression
? Logistic regression is a probability-based classification algorithm using supervised
learning capable of predicting multiclass output variables.
? The logistic regression coefficients are estimated using the maximum likelihood
method. It is not possible to find a closed-form expression for the coefficient values
that maximizes the likelihood function.
? Thus, a starting solution is proposed, then parameters are continuously revised for
improvement, until no improvement can be achieved.
45

Logistic Regression
? The logistic function, f(y) is the core of
logistic regression,
? Which can take values only between 0
and 1.
? The following equation is a simple
mathematical representation of this
function:
46

Time-Series Forecasting
? A time series is a sequence of data
points of the variable of interest,
measured and represented at
successive points in time spaced at
uniform time intervals.
? A time series is developed when the
variable we are interested in might not
have distinctly identifiable explanatory
variables.
47

Time-Series Forecasting
? Time-series forecasting is the use of mathematical modeling to predict future
values of the variable of interest based on previously observed values.
? Time-series forecasting techniques are the averaging methods: simple average,
moving average, weighted moving average, and exponential smoothing.
? The accuracy of a method is usually assessed by computing its error via mean
absolute error, mean squared error, or mean absolute percent error.
48

Practical Portion
Chapter 3, 5, 11 & 13: Getting Started with
Data Analysis
49

Practical Portion
Chapter 3: Introducing Excel Tables
50

Chapter 3: Introducing Excel Tables
? Concepts
? Terms
? Building a Table
? Quick Statistical Measures Available on the Status Bar
? Analyzing Table Information
? Adding a column subtotal
? Sorting table records
? Filtering table records
? Clearing a Filter
? Applying a predefined AutoFilter
51

Concepts
Basic Concepts:
? A table is a kind of database: Microsoft
Access is a powerful database management
tool that lets you work with large, complex
databases.
? A table has its advantages: Because a table
is a collection of rows and columns on a
worksheet, it looks very much like a regular
Excel range
? A table makes data analysis easier: Tables
are also useful tools for analyzing your data.
52

Terms
Table Terms:
? Table column: A single type of information, such as names, addresses, or phone
numbers. In an Excel table, each column is the equivalent of a data-base field.
? Table row: A set of associated table cells, such as the data for a single contact. In an Excel
table, each row is the equivalent of a database record.
? Table cell: An item in a table column that represents a single instance of that column’s
data, such as a name, address, or phone number. In an Excel table, each cell is equivalent
to a database field value.
? Headers: The unique names you assign to every table column that serve to label the data
in each column. These names are always found in the first row of the table.
? Sort & Filter buttons: An Excel feature that gives you access to a set of commands that
perform various actions on a column, such as sorting or filtering the column data.
53

Building a Table
? Getting the data from an external source: The usual way to create an Excel table is
to import the information from an external source, such as another workbook, an -
Access database, a text file, or even a webpage.
Converting a range to a table:
o Decide whether you want your table to have column headers.
o Column headers must be unique and must be text or text formulas.
o Excel can often automatically identify the size and shape of the range that
contains your data.
Note: to avoid confusing Excel, make sure that you have no blank rows or columns in
your range.
54

Building a Table (cont.)
These images are for illustration only. Open Excel file ‘DS498_week4-Ch3’ and apply the
steps:
1. Select a cell within the range that you want to convert
to a table.
2. Choose Insert ? Tables ? Table or press Ctrl+T.
3. If Excel got it wrong, drag over the correct range.
4. If your range has labels that you want as column
headers, make sure the ‘My Table Has Headers’
checkbox is selected.
5. Click OK
Note: The Design tab is displayed whenever you select a cell
anywhere inside the table. It is full of useful table tools.
55

Quick Statistical Measures Available on the Status Bar
56

Analyzing Table Information
? Displaying simple statistics:
Average: 295.75 Count: 8 Sum: 2366
? Excel displays the status bar statistics when
you select any range, so you can get those
stats without bothering to convert a range to
a table
57

Adding a column subtotal
The image are for illustration only. Open Excel
file ‘DS498_week4-Ch3’ and apply the steps:
1. Select the data in the column you want to
total.
2. Click the Quick Analysis smart tag or press
Ctrl + Q. The Quick Analysis options appear.
3. Click the Totals tab.
4. Select the type of calculation you want to
use.
58

Technology Insights: How to Calculate Descriptive Statistics in
Microsoft Excel
59

Sorting table records
? Sorting enables you to get a feel for how your data is distributed overall. For example,
you might notice that most of the values cluster around the low end of the range of
values.
? Sorting enables you to identify certain types of trends in the data. For example, you
might notice that records (that is, rows) with low values in the sorted column all
come from the same geographic area, or that high values in another table all come
from the same division of the company.
? Sorting enables you to identify outliers, which are data points that are significantly
outside the norm for your data. For example, if your sort shows that most of your
column values lie between 1 and 100, but one row contains the value 250, you’ll
want to investigate why that value is so much greater than the others
60

Sorting table records
To sort a table based on the values in a column, follow these
steps:
1. Click the Sort & Filter button for the column you want to sort.
? Excel displays the Sort & Filter menu.
2. Select the sort option you want:
? Sort Smallest to Largest: Sorts the column values in
ascending numeric order.
? Sort Largest to Smallest: Sorts the column values in
descending numeric order
61

Sorting table records (cont.)
To use the Custom Sort command, follow -
these steps:
1. Click any Sort & Filter button in the table.
Excel displays the Sort & Filter menu.
2. Choose Sort by Color ? Custom Sort.
Excel displays the Sort dialog box.
3. Use the Sort By drop-down list to select the
field that you want to use for sorting.
4. Use the Sort On list to select Cell Values.
5. Use the Order list to select a sort order.
Note: If you’re using conditional formatting, you can also choose to sort on Cell Color,
Font Color, or Conditional Formatting Icon.
62

Filtering table records
To apply an Auto Filter to a table, follow these steps:
1. Click the Sort & Filter button for the column you want to filter.
Excel displays the column’s Sort & Filter menu. Above the OK and
Cancel buttons, you see a list of check boxes, where the name
of each check box is a unique value from the column.
2. Deselect the Select All check box to deselect all the check boxes.
3. Select the check box for each column value you want to see in the
filtered table.
Sort & Filter menu for the Division column, with only the 3 check
box selected.
4. Click OK.
63

Clearing a filter
1. To clear a filter from a single column, select the column’s Sort & Filter button
2. Then select the Clear Filter From “Column” command from the menu (where Column
is the name of the filtered column).
? If you have multiple filters applied to the table, you can clear all the filters in one fell
swoop by choosing Data ? Clear (look for it in the Sort & Filter group)
64

Applying a predefined AutoFilter
1. Click the Sort & Filter button for the column you want to filter.
Excel displays the column’s Sort & Filter menu.
2. Choose the X Filters command, where X refers to the type of data in the column.
You have three possible commands:
Number Filters: Appears when the column contains numeric data.
Date Filters: Appears when the column contains date values, time values,
or both.
Text Filters: Appears when the column contains text data.
65

Applying a predefined AutoFilter (cont.)
3. Select the filter operator you want to apply.
Excel displays a menu of predefined filter operators. The contents of the menu depend on
the data type of your column. For example, if your column contains numeric data, you see
the following operators: Equals, Greater Than, Less Than
Custom Filter: Displays the Custom
AutoFilter dialog box, which enables you
to create your own filter condition.
4. Complete the AutoFilter condition.
5. Click OK.
Excel filters your table according to your
custom AutoFilter.
66

Practical Portion
Chapter 5: Scrub-a-Dub-Dub: Cleaning Data
67

Chapter 5: Scrub-a-Dub-Dub: Cleaning Data
? Editing Your Imported Workbook
? Deleting columns/rows & Erasing
Content
? Formatting numeric values
? Replacing data in fields
? The CLEAN & CONCAT function
? The EXACT & LEN function
? The FIND & LOWER function
? The MID function
? The NUMBERVALUE function
? The PROPER & REPLACE function
? The SEARCH & UPPER function
? The SUBSTITUTE & VALUE function
? The TEXT & TRIM function
? The TEXTJOIN function
? Converting text function formulas to
text
? Using Validation to Keep Data Clean
68

Editing Your Imported Workbook
If you take a look at the workbook DS498_week4-Ch5 shown in Figure 5-1, you see that the
data, although some-what neatly formatted, suffers from quite a few problems:
? The data is a regular range, not an Excel table.
? The workbook has several blank rows and a blank
column.
? The “numbers” you see in columns B, C, and F are
text values. You see an error indicator in each cell,
and when you click the error icon, Excel tells you
the cell contains a number formatted as text.
? The column are poorly sized for the data they
contain. For example, columns A, B, C, and F are
too wide, whereas column E is too narrow (which
is why those pound signs (#) appear in some cells)
69

Deleting columns/rows & Erasing Content
? To delete unnecessary columns, either
o click the column header to select the entire column and then choose Home ?
Delete, or
o right-click the column header and then click Delete.
? To delete unnecessary rows, either
o click the row header to select the entire row and then choose Home ? Delete, or
o right-click the row header and then click Delete.
? To erase the contents of a cell or range that contains data you don’t need, select the
worksheet cell or range and then choose Home ? Clear ? Clear All.
Excel erases both the contents of the cells in the selected range and any formatting
assigned to those cells
70

Formatting numeric values
? Select the range you want to format, click the
Number Format drop-down list on the Home tab,
and then select the format you want to apply.
? OR
1. Select the range you want to format,
2. click the Number Format drop-down list on the
Home tab,
3. then select More Number Formats.
Excel displays the Format Cells dialog box with the
Number tab displayed.
4. Select a category and then select among the
options to specify the format you want to use.
While you’re here
71

Replacing data in fields
Some common reasons to replace text include:
? repeated characters,
? Misspelled words,
? Abbreviations or acronyms
1. To use Replace, choose Home ? Find & Select ?
Replace to open the Find and Replace dialog box with the
Replace tab displayed.
2. Enter incorrect text you want to find and then enter the correct text. You can
proceed by:
o Click Find Next to find the next instance. If you want to change, click Replace;
otherwise, click Find Next, OR
o If you’re absolutely certain that you want to replace every last instance, click
the Replace All button to make all the changes at one time
72

The CLEAN & CONCAT function
CLEAN
? You can use the CLEAN function to remove nonprintable characters from text or expunge
the line feeds and carriage returns, making the data display on a single line.
? The CLEAN function uses the following syntax: CLEAN(text)
? For example, to clean the text stored in cell A2: CLEAN(A2)
CONCAT
? The CONCAT function joins two or more chunks of text into a single string using the
following syntax: CONCAT(text1, text2, text3,...)
? For example, CONCAT(A1, " ", B1) tells Excel to take the text from cell A1, tack on a space,
and then add the text from cell B1.
? If A1 contains “Paul” and B1 contains “McFedries,” the CONCAT function returns the
following string: Paul McFedries
73

The EXACT & LEN function
EXACT
? The EXACT function compares two strings. If the two strings are the same, it returns the
logical value TRUE. If the two strings differ, the EXACT function returns FALSE.
? The EXACT function is case sensitive, and uses the following syntax: EXACT(text1,text2)
? For example, to check whether the two strings "Redmond" and "redmond" are the
same, use the following: EXACT("Redmond","redmond"). This should return FALSE.
LEN
? The LEN function counts the number of characters in a string, and uses the syntax: LEN(text)
? For example, to measure the length of the string in cell I81 reading Semper fidelis, use the
following formula: LEN(I81)
? The function returns the value 14. Spaces are counted as characters, too.
74

The FIND & LOWER function
FIND
? The FIND function finds the starting character position of one string within another
string, and uses the syntax: FIND(find_text,within_text,[start_num])
? For example, to find at what point the two-letter state abbreviation WA begins in the
string Redmond WA 98052, use the following: FIND("WA","Redmond WA 98052",1)
? This returns the value 9 because WA begins at the ninth position (spaces are counted).
LOWER
? If an all-uppercase column should really be all-lowercase, use the LOWER function.
? The function uses the following syntax: LOWER(text)
? For example, to convert the string PROFESSIONAL to professional, use the following
formula: LOWER("PROFESSIONAL"). The function returns “professional”.
75

The MID function
? The MID function returns a chunk of text from inside of a string. The function uses the
following syntax: MID(text, start_num, num_char)
? For example, to grab the text fragment tac from the string tic tac toe, use the following
formula: =MID("tic tac toe",5,3)
? For example, here are some part numbers that are imported from a database:
LDW-2125-X52
MP-9790-C78 ...
? Suppose you want to extract the four digits that appear between the hyphens (-), use the
FIND function to locate the first hyphen, and add 1 to get the starting point of the four-
digit numeric value. Here’s expression to use if the text is in cell A2: FIND("-", A2) + 1
? You can then plug this expression into your MID function: MID(A2, FIND("-", A2) + 1, 4)
For the first example string, this expression return 2125.
76

The NUMBERVALUE function
? The NUMBERVALUE function converts digits formatted as a string to a true
numeric value; the syntax: NUMBERVALUE(text, decimal_separator, group_separator)
? For example, the formula in the previous section to extract four digits from within a
string returns those digits as a string instead of a number. Assuming that one such string
resides in cell B2, you use the following expression to convert it to a number:
NUMBERVALUE(B2)
? As another example, suppose your imported data includes the following values as strings:
71.970,53 …
? To convert these strings to numbers, you use the following version of NUMBER-VALUE
(assuming that the first string is in B2): NUMBERVALUE(B2, ",", ".")
? This tells Excel to treat the comma (,) as the decimal separator and the period (.) as the
group separator . For the first string, this function returns the numeric value 71970.53.
77

The PROPER & REPLACE function
PROPER
? The PROPER function capitalizes the first letter of every word in a string. The function
uses the syntax: PROPER(text)
? For example, to capitalize the initial letters in the string SALES REPRESENTATIVE , use the
following formula: PROPER("SALES REPRESENTATIVE")
? The function returns the string Sales Representative.
REPLACE
? The REPLACE function replaces a portion of a string with new text. The function uses the
syntax: REPLACE(old_text, start_num, num_chars, new_text)
? For example, to replace the string man with the string Representative in the string
Salesman, use the following expression: REPLACE("Salesman",6,3," Representative")
? The function returns the string Sales Representative.
78

The SEARCH & UPPER function
SEARCH
? The SEARCH function performs a case-insensitive search to return the starting position of
a specified string within a larger string. The function uses the syntax:
SEARCH(find_text,within_text,start_num)
? For example, to identify the position at which the text fragment Churchill starts in the
string Mr. Churchill, use the following formula: SEARCH("Churchill","Mr. Churchill",1)
? The function returns the value 5.
UPPER
? The UPPER function returns an all-uppercase version of a string. The function uses the
following syntax: UPPER(text)
? For example, to convert the string professional to PROFESSIONAL, you can use the
following formula: UPPER("professional"). The function returns “PROFESSIONAL”.
79

The SUBSTITUTE & VALUE function
SUBSTITUTE
? The SUBSTITUTE function replaces occurrences of text in a string. The function uses the
syntax: SUBSTITUTE(text,old_text,new_text,instance_num)
? For example your data comes with a phone number column in the following format:
123 555 6789
? Assuming that one such string is in cell A2, here’s a SUBSTITUTE expression that replaces
all the spaces with dashes: SUBSTITUTE(A2, " ", "-")
VALUE
? The VALUE function converts a string that looks like a numeric value to an actual number.
The function uses the following syntax: VALUE(text)
? For example, to convert the string $123,456.78 — assume that this isn’t a numeric
value but a string — you use the following expression: VALUE("$123,456.78")
? The function returns the numeric value 123456.78.
80

The TEXT & TRIM function
TEXT
? The TEXT function formats a value and then returns the value as text. The function uses the
syntax: TEXT(value,format_text)
? For example, the formula: =TEXT(1234.5678,"$##,###.00") returns the text $1,234.57.
TRIM
? Some of the biggest causes of untidiness in imported data are extra characters.
? The aptly named TRIM function removes extra spaces, line feeds, carriage returns, and tabs
from a string. The function uses the syntax: TRIM(text)
? For example, to trim the text stored in cell A2, use the following syntax: TRIM(A2)
81

The TEXTJOIN function
? The TEXTJOIN function combines two or more strings into a single string with a specified
character — called the delimiter — between each of the original strings. The function
uses the syntax: TEXTJOIN(delimiter,ignore_empty, text1, text2,...))
? For example, suppose you imported data that includes separate fields for three types of
code: Manufacturer, Category, and Location. Also you can generate the values of a Part
Number field by combining these three codes into a single string, separated by hyphens.
If the three codes are in cells A2, B2, and C2, the TEXTJOIN expression is: =TEXTJOIN("-",
TRUE, A2, B2, C2)
82

Converting text function formulas to text
? When you’re using text functions to clean up imported data, the most common
technique is to start a new column beside the data and create a formula that uses a text
function to convert the first cell of the imported data into the format you want. You then
fill that formula down the column to get cleaned-up versions of all the cells.
? The aforementioned technique has one inconvenient fact: Your cleaned-up versions
appear as formula results instead of plain text or numbers. You can convert those
formulas to the strings and numbers that they return by:
1. Select the worksheet range that holds the formulas.
2. Choose Home ? Copy.
3. Choose Home ? Paste ? Paste Values.
? Because you left the formula range selected, the paste replaces the formulas with
the values they returned.
83

Using Validation to Keep Data Clean
? The Data Validation command enables you to
supply messages that give data input information
and error messages that attempt to help someone
correct data-entry errors.
? To use Data Validation, follow these steps:
1. Select the worksheet range where the to-be-
validated data will go.
2. Choose Data ? Data Validation. Excel displays
the Data Validation dialog box, as shown in
Figure 5-5.
84

3. On the Settings tab of the Data Validation
dialog box, use the controls in the Validation
Criteria area to describe what is valid data.
4. Fine-tune the validation.
5. (Optional) Consider expanding the scope of the
data validation.
85

6. Provide an input message from the Input
Message tab of the Data Validation dialog box.
7. Provide an error message from the Error Alert
tab of the Data Validation dialog box (Figure 5-8).
86

Practical Portion
Chapter 11: Analyzing Data with Statistics
87

Chapter 11: Analyzing Data with Statistics
? Counting Numbers
? Counting empty & nonempty cells
? Counting cells that match criteria
? Calculating average & conditional average
? Calculating median & mode
? Finding Rank
? Determining Nth Largest & Smallest Value
? Calculating the Standard Deviation
? Finding the Correlation
88

Counting numbers
? Take a look at the workbook DS498_week4-Ch11 for
the following analysis of data:
? The COUNT function tallies the number of cells
within a specified range that hold numeric values.
=COUNT(value1[, value2, ...])
? Here, value1, value2, and so on are cell or range
references.
? For example, to use the COUNT function to return
how many numeric values are in the range B3:B12 in
the worksheet shown in Figure 11-1, use the
following formula: =COUNT(B3:B12)
? As shown in cell E3, COUNT returns 6 (five numbers
plus one date).
89

Counting empty & nonempty cells
Nonempty Cells:
? The COUNTA function counts the number of cells
within a specified range that are nonempty.
? For example, to use COUNTA in the range B3:B12:
=COUNTA(B3:B12). COUNTA returns the value 9.
Empty Cells:
? The COUNTBLANK function counts the number of
cells within a specified range that are empty.
? For example, to use COUNTBLANK in the range
B3:B12: =COUNTBLANK(B3:B12). COUNTBLANK
returns the value 1.
90

Counting cells that match criteria
? If you have your own criteria for what should or
shouldn’t get counted, you can use the COUNTIF
function to apply that condition.
? The syntax is: =COUNTIF(range, criteria)
? For example, looking back at the range B3:B12,
suppose you want to know how many cells contain a
value greater than 800: =COUNTIF(B3:B12,">800")
This returns the value 3.
? You can use any of the standard logical
operators when building your criteria expression.
91

Calculating average & conditional average
Average
? The AVERAGE function uses the following syntax:
AVERAGE(number1[, number2, ...])
? For example, to determine the average of the
values in the range D3:D19, you use the following
formula: =AVERAGE(D3:D19)
Conditional Average
? The AVERAGEIF function averages cells in a range
that meet the condition you specify.
? AVERAGEIF takes up to three arguments:
=AVERAGEIF(range, criteria[, average_range])
? For example, if you want to get the average of
the values in the Gross Margin column, you use:
=AVERAGE(H3:H10)
92

Calculating median & mode
Median
? To calculate the median, you use Excel’s MEDIAN
function: MEDIAN(number1[, number2, ...])
? In the Product Defects, the median value of the
Defects column (D3:D19) is given by:
=MEDIAN(D3:D19)
Mode
? In the Product Defects, the mode value of the
defects column (D3:D19) is given by the formula
in cell I3: =MODE(D3:D19)
93

Finding Rank
Rank
? RANK.EQ determines an item’s rank relative to
other items in a list: RANK.EQ(number, ref[,
order])
? For example, in Figure 11-4 (range B3:B48) to find
the rank of the grade in cell B3: =RANK.EQ(B3,
$B$3:$B$48). The returned value is 6.
Rank Average
? With RANK.AVG, if two or more numbers have the
same rank, Excel averages the rank:
RANK.AVG(number, ref[, order])
? For example, in the list 100, 95, 90, 85, 85, 80, 70,
the RANK.AVG function ranks the number 85 as
4.5, which is the average of 4 and 5.
94

Determining Nth Largest & Smallest Value
LARGE Function
? LARGE returns the nth highest value in a list: LARGE(array, n)
? For example, given B3:B48, what’s the minimum mark
required to crack the top 10 grades?
? The LARGE function (D4) returns the value 75:
=LARGE(B3:B48, 10)
SMALL Function
? SMALL returns the nth smallest value in an array or range:
SMALL(array, n)
? For example, given B3:B48, what’s the lowest grade?
? The formula (D7) returns the value 40: =SMALL(B3:B48, 1)
95

? You can use the FREQUENCY function to return the number
of occurrences in each group:
FREQUENCY(data_array, bins_array)
? Here are the steps to follow:
1. Select the cells where you want the grouped frequency
distribution to appear.
2. Type =frequency(.
3. Enter or select the items you want to group.
4. Type a comma and then enter or select the list of
groupings.
5. Type )
6. Hold down Ctrl+Shift and then click the Enter button or
press Ctrl+Shift+Enter.
96

? In Excel, the variance is calculated using
VAR.S or VAR.P:
VAR.S(number1[, number2, ...])
VAR.P(number1[, number2, ...])
? Use VAR.S if data represents a sample;
use VAR.P if data represents the entire
population.
? For example, in the Product Defects
worksheet; calculate the variance of the
Defects column (D3:D19) with the
following formula (see cell H3):
=VAR.P(D3:D19)
97

Calculating the Standard Deviation
? To calculate the standard deviation, you can use
the STDEV.S or STDEV.P function.
STDEV.S(number1[, number2, ...])
STDEV.P(number1[, number2, ...])
? Use STDEV.S for sample; STDEV.P for entire
population.
? For example, in the Product Defects
worksheet shown earlier in Figure 11-7, I
calculated the standard deviation of the Defects
column (D3:D19) with the following formula (see
cell H3): =STDEV.P(D3:D19)
98

Finding the Correlation
? Correlation is a measure of the relationship
between two sets of data:
CORREL(array1, array2)
? Figure 11-8 shows a worksheet that has
advertising costs in the range C3:C14 and sales in
the range D3:D14. Cell F3 calculates the
correlation: =CORREL(C3:C14, D3:D14)
? CORREL returns the correlation coefficient,
between –1 and 1.
? The sign suggests whether the relationship is
positive (+) or negative (–).
99

Practical Portion
Chapter 13: Analyzing Data with Inferential Statistics
101

Chapter 13: Analyzing Data with Inferential
Statistics
? Determining the Regression
? Calculating the Correlation
? Sampling Data (recommended)
? Using the t-Test Tools (recommend)
? Calculating the Covariance (recommended )
102

Determining the Regression
1. Choose Data ? Data Analysis.
2. Use the Analysis Tools list to select the Regression tool and then click OK.
3. Identify your Y and X values.
4. (Optional) Set the constant to zero.
5. (Optional) Calculate a confidence level in your regression analysis.
6. Select a location for the regression analysis results.
10
3

Determining the Regression
7. Identify what data you want returned. Figure 13-9 shows the dialog box
8. Click OK. Figure 13-10 shows a portion of the regression analysis results.
10
4

Calculating the Correlation
To use the Correlation analysis tool, follow the steps:
2. Use the Analysis Tools list to select the
Correlation tool and then click OK.
3. Identify the range of X and Y values that you
want to analyze.
4. Select an output location.
? Figure 13-11 shows a completed version of
the dialog box.
5. Click OK.
? Figure 13-12 shows the correlation results for
list price versus units sold.
10
5

Recommended Part
Chapter 13: Analyzing Data with Inferential Statistics
106

Sampling Data
? When the population is huge, and analyses would take up too much time, you extract
a sample and use inferential statistics to draw conclusions about the population.
? To sample items from a worksheet, take the following steps:
2. In the Analysis Tools list, select Sampling and then click OK.
3. Use the Input Range box to specify the range of cells from which you want to extract
your sample.
10
7

Sampling Data (cont.)
4. Choose a sampling method (Periodic/Random). Figure 13-2 shows the
sampling dialog box.
5. Click OK. Figure 13-3 shows an example; sample data is the Defects
column (D3:D22).
10
8

Using the t-Test Tools
? The Excel Analysis ToolPak add-in provides three tools for working with t-values and t-
tests, which can be useful for inferences about very small data sets:
? Paired Two-Sample for Means: for performing a paired two-sample t-test.
? Two-Sample Assuming Equal Variances: for a two-sample test where you assume
that the variances of both samples equal each other.
? Two-Sample Assuming Unequal Variances: for a two-sample test where you assume
that the two-sample variances are unequal.
? Take a look at the workbook DS498_week4-Ch13 for performing the t-test.
10
9

To perform a t-test calculation, follow
these steps:
2. Use the Analysis Tools list to select
the t-test tool you want to use and
then click OK.
3. In the Variable 1 Range and Variable 2
Range input text boxes, identify the
sample values by telling Excel in what
worksheet ranges you’ve stored the
two samples.
11
0

4. Use the Hypothesized Mean
Difference text box to indicate
whether you hypothesize that the
means are equal.
5. In the Alpha text box, state the
confidence level for your t-test
calculation.
6. In the Output Options section,
indicate where the t-test tool results
should be stored.
7. Click OK.
11
1

Calculating the Covariance
The use the Covariance tool follow these steps:
2. Use the Analysis Tools list to select the
Covariance tool and then click OK.
3. Identify the range of X and Y values that you
want to analyze.
4. Select an output location.
? Figure 13-13 shows a completed version of
the dialog box.
5. Click OK after you select the output options.
? Figure 13-14 shows the covariance results for
the list price and units sold data.
11
2

Main Reference
? Application Case 3.1 to Application Case 3.4 from “Analytics, Data Science, & Artificial
Intelligence: Systems for Decision Support”.
? Practice examples of chapter Ch 3, 5, 11 and 13 from Excel Data Analysis For Dummies
using Excel
Week self-review exercises
113
? Chapter 3 (sections 3.2 to 3.6): “Nature of Data, Statistical Modeling, and Visualization”
from “Analytics, Data Science, & Artificial Intelligence: Systems for Decision Support”.
? Chapter 3, 5, 11 & 13: “Getting Started with Data Analysis” from “Excel Data Analysis For
Dummies”

狠狠撸

IT445_Week_4.pdf

Recommended

More Related Content

Similar to IT445_Week_4.pdf (20)

IT445_Week_4.pdf