Set-2
|
● Q1. A. Attempt the question [1X3=3] |
||
|
● a)List and briefly describe two types of data analysis. |
||
|
● Descriptive Analysis: Summarizes past
data to understand what has happened. ●
Predictive
Analysis: Uses historical data to predict future outcomes. |
||
|
● b)Why is Excel commonly used for data analysis? |
||
|
● Excel is widely used for data analysis
due to its accessibility, user-friendly interface, powerful functions, and
tools for organizing, analyzing, and visualizing data. |
||
|
● c) What is the purpose of data validation in Excel? |
||
|
● Data validation in Excel ensures that
the data entered into a cell meets specific criteria, which helps maintain
data accuracy and consistency. |
||
|
B
QB.Attempt the question
[1X2=2] |
||
|
a)What
is exploratory data analysis (EDA) |
||
|
E EDA is an approach to analyzing data
sets to summarize their main characteristics, often using visual methods. It
is used to discover patterns, spot anomalies, test hypotheses, and check
assumptions. |
||
|
b) b)Explain the purpose of a slicer in
Excel. |
||
|
A slicer is a visual filtering tool
that allows users to segment and filter data in pivot tables and pivot
charts quickly. |
||
|
Q2Q2.Attempt any ONE out of TWO the
following.
[1X4=4] |
||
|
a)Explain how the UPPER, Trim, and Len
functions change the case of text strings
in Excel ?
|
||
|
UPPER-The Excel Text function “UPPER Function” will
change the text to all capital
letters (UPPERCASE). As a
result, the function changes all of the characters in a text string input to
upper case. =UPPER(Text) TRIM-TRIM function
will remove all spaces from a cell, with the exception of single spaces
between words. =TRIM(Select
Cell) LEN-LEN quickly
returns the character count in a given cell. The =LEN formula may be used to
calculate the number of characters needed in a cell =LEN(Select
Cell) |
||
|
b)What are the
essential components of a data analysis process? |
||
|
Data Collection:-Gathering relevant data from various sources,
such as databases, surveys, sensors, or other data repositories. Data
Cleaning:-Preprocessing the data to
handle missing values, remove errors, and ensure consistency. This step is
crucial for ensuring the accuracy and reliability of the analysis. Exploratory Data Analysis (EDA):-Investigating the data using graphical and
statistical methods to summarize its main characteristics, often involving
the creation of charts, graphs, and summary statistics. Data Transformation:-Modifying the data to suit the requirements of
the analysis. This may include normalizing, standardizing, or aggregating
data. Statistical Analysis:-Applying statistical methods to analyze the data,
test hypotheses, and infer properties of the underlying distribution. This
can involve descriptive statistics, inferential statistics, and hypothesis
testing. Machine Learning:-Using machine learning algorithms to build models
that can make predictions or classify data based on patterns identified in
the analyzed data. Data Visualization:-Creating visual representations of the data, such
as charts, graphs, and dashboards, to communicate findings effectively. Pattern Recognition:-Identifying patterns, trends, correlations, or
anomalies in the data that may not be apparent through simple inspection. Decision-Making:-Presenting the analyzed results to stakeholders
to support informed decision-making. This step involves translating the
insights gained from data analysis into actionable strategies. |
||
|
Q
Q3.Attempt any ONE out of TWO the following.
[1X4=4] |
||
|
a)What is
the difference between Power Query and traditional Excel formulas for data
cleaning? |
||
|
|
Power Query |
Traditional Excel |
|
Approach |
Utilizes a
visual interface for data
transformation. Users
interactively perform data
cleaning tasks by applying a series of steps in the Power Query Editor. It's more intuitive and doesn't require writing complex
formulas. |
Relies on formula-based approaches
where users write formulas
directly into cells to clean
and manipulate data. Users have to know the appropriate functions and syntax to
achieve desired outcomes |
|
Capabilities |
Offers
advanced data cleaning capabilities such as removing duplicates, splitting
and merging columns, handling errors and missing values, and transforming
data with ease. It's optimized for working with large datasets and complex
transformations |
Provides granular control over data cleaning
operations. Users can create custom formulas tailored to specific
requirements, though this might require a deeper understanding of Excel functions
and formulas. |
|
Ease of Use |
Designed to be
user-friendly
with a guided
approach to data cleaning. Users with varying levels
of Excel proficiency can perform complex transformations without writing
formulas. It's more accessible for beginners. |
Requires users to have a good understanding of
Excel functions and
formulas. While it
offers flexibility, users need to
know the correct functions and syntax to achieve
desired results, which might be
challenging for novices |
|
Integration |
Seamlessly integrates with Excel, allowing
users to load cleaned
data into Excel for
further analysis. Power Query
queries can be refreshed to update data automatically,
making it suitable for
dynamic datasets. |
Integrates
well with other Excel features
such as charts, pivot tables,
and macros. Users can
incorporate formula-based data cleaning processes into their existing Excel
workflows. |
|
Performance |
Optimized for
handling large datasets efficiently. It can process and manipulate large
volumes of data Withoutperformance
degradation, making
it suitable for analyzing
big data sets |
Performance
may degrade when working with very large datasets or complex formulas, especially
if formulas are applied to entire columns or ranges |
|
b)Explain
Unique,Counta with example |
||
|
UNIQUE: Function:
Returns a list of unique values in a range. Example: Suppose
you have a dataset with duplicate values: A Apple Banana Orange Apple Banana To find the
unique values: Formula
(for Excel 365 and later versions): =UNIQUE(A1:A5) This
formula returns a list of unique values {Apple, Banana, Orange}. COUNTA: Function:
Counts the number of non-empty cells in a range that contain any type of
data. Example: Suppose
you have a dataset with some empty cells: A Apple Orange Banana To
count the non-empty cells: Formula: =COUNTA(A1:A5) This formula
counts the number of non-empty cells in the range A1:A5, which is 3. |
||
|
Q4 Attempt any ONE out of TWO the following.
[1X4=4] |
||
|
a)How do
you apply conditional formatting to cells in Excel? |
||
|
Steps to Apply Conditional Formatting in Excel: 1.Select the Range of Cells:-Click and drag to
highlight the cells you want to format. For example, select cells A1 to A20. 2.Open the Conditional Formatting Menu:- 1.Navigate to the Home tab on the Excel ribbon 2.In the Styles group, click on Conditional
Formatting.
3.Choose a Conditional Formatting Rule: From the dropdown menu, you can choose from
several types of rules. Here are some commonly used ones in data analysis:
● Highlight Cell Rules: Highlights cells that meet
specific criteria such as greater than, less than, equal to, or between
specific values. ● Top/Bottom Rules: Highlights the top or bottom
values, percentages, or averages. ● Data Bars: Adds a visual bar inside the cells
proportional to the value of the cell. ● Color Scales: Applies a color gradient to the
cells based on their values. ● Icon Sets: Adds icons to cells to categorize them
based on specified criteria.
4.Define Rule Parameters:
● After selecting a rule type, a dialog box will
appear where you can set the parameters. ● For example, if you choose Highlight Cell Rules
> Greater Than, you would: 1. Enter a value (e.g., 50) in the "Greater
Than" field. 2. Choose a formatting style (e.g., fill color, text
color).
5.Apply the Rule:
● Click OK to apply the rule. ● The cells that meet the criteria will now be
formatted according to your specifications. |
||
|
b)What
are the key steps involved in building an Exploratory Data Analysis (EDA) project in
Excel? |
||
|
Data Collection : Gather the
relevant data that you want to analyze in Excel. This data can come from
various sources, such as spreadsheets, databases, or external data files.
Import the data into Excel and organize it in a structured format, such as a
table, for easy analysis.
Data
Cleaning : Clean the data by identifying and handling any missing,
inconsistent, or erroneous data. This may involve removing duplicates,
filling in missing values, correcting data entry errors, and standardizing
data formats.
Data
Exploration : Use Excel's built-in data analysis tools, such as filters,
pivot tables, and charts, to explore the data visually and gain insights. Generate
descriptive statistics, such as mean, median, mode, and standard deviation,
to summarize the data and identify trends, patterns, and outliers.
Data
Visualization : Create visually appealing and informative charts and graphs
in Excel to visually represent the data. Use various chart types, such as
bar charts, line charts, scatter plots, and heatmaps, to display different
types of data and relationships between variables.
Data
Interpretation : Interpret the results of your analysis and draw meaningful
conclusions from the data. Summarize your findings and insights clearly and
concisely, using charts, tables, and written explanations.
Reporting
: Create a comprehensive report or presentation in Excel to communicate your
findings and insights to stakeholders. Use Excel's formatting, styling, and
visualization features to create professional-looking reports that
effectively convey the results of your EDA.
Documentation : Document your
entire EDA process, including the data collection, cleaning, exploration,
visualization, hypothesis testing, interpretation, and reporting steps. This
documentation will help you reproduce your analysis in the future and enable
others to understand and replicate your findings. |
||
|
Q4Q5.Attempt any ONE out of TWO the
following.
[1X4=4] |
||
|
a)Explain Sum and SUMIF functions in Excel? |
||
|
SUM
is used for straightforward addition of numbers in a range. SUMIF adds the numbers in a range that
meet a specified condition.
The SUM function in Excel is used to
add up a range of numbers. It is one of the most commonly used functions in
Excel for performing arithmetic operations. Syntax: =SUM(number1,
[number2], ...) number1,
number2, ...: These are the numbers or ranges you want to sum. You can
include up to 255 arguments. Example: If
you have numbers in cells A1 to A5 and you want to calculate the total sum: =SUM(A1:A5) SUMIF
Function in Excel The
SUMIF function in Excel adds the cells specified by a given condition or
criteria. It is useful when you need to sum values based on specific
criteria. Syntax: =SUMIF(range, criteria, [sum_range]) range: The range of cells that you want to
apply the criteria to. criteria:
The condition that must be met for a cell to be included in the sum. sum_range
(optional): The actual cells to sum. If omitted, Excel sums the cells in the
range. Example: Suppose
you have sales data in columns A and B. Column A contains product names, and
column B contains sales figures. To sum the sales for a specific product,
say "Apples": =SUMIF(A2:A10, "Apples", B2:B10) This formula checks each cell in the range A2:A10
for the word "Apples". When a match is found, it adds the
corresponding value from the range B2:B10. |
||
|
b) b)What is the difference between COUNT ,COUNTIF
functions in Excel ? |
||
|
COUNT: Counts only
the cells with numerical values. Ignores text, logical values, errors,
and empty cells. COUNTIF: Counts the number of cells that meet a
specific condition within a range. Can count cells with numerical values,
text, or other criteria. Practical Examples COUNT Example: Count the number of numeric entries in
a range: =COUNT(A1:A10) COUNTIF Example: Count the number of cells in a range
that contain the text "Apple": =COUNTIF(C1:C10,
"Apple") |
||
|
Q6.Short Notes Attempt any TWO out of THREE the following. [2X2=4] |
||
|
a)Data Validation in Excel |
||
|
Data
validation ensures that users enter valid and consistent data into a
spreadsheet. It restricts the type of data that can be entered into a cell,
provides input messages, and generates error alerts. Example: To
restrict a cell to only allow whole numbers between 1 and 10: Select the cell(s). Go to Data > Data Validation. Set Allow to Whole number. Set Data to between. Enter Minimum
as 1 and Maximum as 10. |
||
|
b)Descriptive Statistics |
||
|
Descriptive
statistics summarize and describe the main features of a dataset. Mean: The
average value. Median: The middle value. Min/Max: The smallest/largest value. Range: The difference between the max
and min values. Unique: The count of unique values. COUNTA: Counts
the number of non-empty cells. |
||
|
c)Key Components of Data Analysis |
||
|
Data
Collection: Gathering data from various sources. Data Cleaning:
Removing or correcting errors and inconsistencies. Data Transformation: Converting data
into a suitable format for analysis. Data Modeling: Using statistical
models to analyze data. Data
Visualization: Presenting data through charts, graphs, and other visual
tools |
||
