Data Handling and Business Intelligence Report

Data Handling and
Business Intelligence
Report

Table of Contents
PART 1............................................................................................................................................3
1.1 Use of excel of pre-processing the data.................................................................................3
1.1.1 Use of IF function in Excel.............................................................................................5
1.1.2 VLookup.........................................................................................................................6
1.1.3 Charts and Graphs...........................................................................................................7
PART 2............................................................................................................................................9
2.1 Using the audidealership.csv provided in conjunction with Weka give a specific example
of clustering.................................................................................................................................9
2.2 Explain the most common data mining methods that can be used in business with real
examples....................................................................................................................................10
2.3 Advantage and disadvantage of Weka over excel...............................................................12
REFERENCES..............................................................................................................................13

PART 1
1.1 Use of excel of pre-processing the data
Within Microsoft, MS Excel is an application that is very useful and very important software.
You will know its importance when you start doing a job. Microsoft Excel is a spreadsheet
program that is one of the applications under the Microsoft Office suite. Is a commercial
spreadsheet application created by Microsoft. It is used to make basic calculations, graphic tools,
pivot tables and macros. Similar to other applications in Excel, basic features are available to
create spreadsheets in which the collection of cells in the form of rows and columns are arranged
and in which to organize and manipulate the data (Niglas, 2007).
Here, the data is also shown in the form of line graph, chart and histogram. In Excel, we can
experiment with many types of facts from different aspects of data. MS Excel is used everywhere
from manufacturing industry to a small shop, government office.
In any office, people working on it know that which is the main speech meaning in this
application which is used the most:
1) Pivot Tables
PivotTables summarizes large amounts of Excel data from a database that is formatted where the
first row contains Heading and the second row contains Categories or Values. The way the data
is summarized is flexible but usually Pivot Tables will contain the values of some or all of the
categories. If you are new to creating Pivot Table, Excel 2013 can analyze your data and
recommend Pivot Table for you. Once you are comfortable with Pivot Tables, you can start from
scratch and make your own. To create a Pivot Table, make sure that your data has column
headings or table headers and no blank rows. Click on any cell in the range of cells or tables
(Berry and Linoff, 2004).
How this function works:
In the Launch Recommended Pivot Tables dialogue box, click on any Pivot table layout to get a
preview, then choose the one that works best for you and click OK. Excel will provide a

selection of recommended Pivot Tables for your data. Excel then places the Pivot Table on a new
worksheet and shows the field list so that you can rearrange the data according to your needs.
2) Conditional Formatting
Conditional formatting, as its name suggests, changes the format of the cell depending on the
content of the cell, or a range of cells, or any other cell or cells in the workbook. Conditional
formatting helps users quickly focus on important aspects of a spreadsheet or highlight errors and
identify important patterns in the data. Conditional format basic font and cell formatting such as
number format, font color and other font attributes, cell borders, and cell fill color can be
applied. In addition, there is a range of graphical conditional formats that help visualize data
using icon sets, color scales, or data bars.
The conditional format chosen is applied to a cell based on the position that you set or the
position that Excel produces by comparing the values of the cell in a range. So, for example, in a
list of employees' salaries, a conditional format can be applied to any salary in excess of a certain
amount, any employee who precedes a specific date, or anyone with a specific name. the
employee joins.
Graphical conditional formats will apply to the Salary column and, by default, will be based on
an analysis of the highest and lowest values in the list, but can be overridden if required.
3) Sorting and Filtering
Excel spreadsheets help us understand large amounts of data. To make it easier to find what you
need, you can reorder the data or simply select the data you need, based on the criteria you set
within Excel. Sorting and filtering your data will save you time and make your spreadsheet more
effective. Suppose you have a list of hundreds of records, including dates, ages, names, cities,
and more. You can quickly organize data according to your needs using the sort and filter
features of Excel. When you sort information in a worksheet, you can organize data quickly and
find values quickly. You can sort the entire worksheet or a range or table of data. Sorting can be
done by one or more columns (Yan, Lee and Li, 2009).