Data Manipulation and Analysis

Data manipulation and analysis is a core skill in ICT, enabling students to process, explore, and derive insights from data using spreadsheets and database management systems. This topic introduces practical tools and techniques for solving real-world problems.

1. Spreadsheets: Basic Features and Formulas

a. Cell References

• Definition: Cell references are used in formulas to refer to values in other cells.

  • Relative: Changes when a formula is copied to another cell (e.g., A1).

  • Absolute: Remains constant when copied (e.g., $A$1).

  • Mixed: Partly relative, partly absolute (e.g., $A1 or A$1).

• Types:

• Example: =A1 + B1 adds values in cells A1 and B1.

b. Mathematical, Logical, and Relational Operators

• Mathematical: +, -, *, /, ^

• Logical: AND, OR, NOT

• Relational: =, <>, >, <, >=, <=

c. Functions

• Sum: =SUM(A1:A10)

• Average: =AVERAGE(B1:B10)

• If statements: =IF(C1>50,"Pass","Fail")

• Example: Calculate total sales, average scores, or conditional bonuses.

Remark: Mastering formulas and functions allows students to solve problems dynamically and efficiently.

2. Data Manipulation Techniques

a. Sorting

• Definition: Arrange data in ascending or descending order.

• Example: Sort student grades from highest to lowest.

b. Filtering

• Definition: Display only rows that meet certain criteria.

• Example: Show only orders with status = "Pending".

c. Searching

• Definition: Locate specific data in a dataset.

• Example: Find a customer record by ID or name.

d. Multi-worksheet Manipulation

• Definition: Apply formulas and functions across multiple sheets.

• Example: Consolidate monthly sales from multiple worksheets into a summary sheet.

Remark: These techniques enable dynamic exploration and updating of data, supporting informed decision-making.

3. Data Analysis Using Pivot Tables and What-If Scenarios

a. Pivot Tables

• Definition: Summarise, group, and analyse large datasets quickly.

  • Drag fields to rows, columns, values.

  • Apply functions: SUM, SUBTOTAL, AVERAGE.

• Features:

• Example: Analyse sales by product category and region.

b. Pivot Charts

• Definition: Graphical representation of pivot table data.

• Example: Show monthly revenue trends per product.

c. What-If Analysis

• Definition: Explore outcomes by changing input values.

• Example: Estimate profit if product prices increase by 10%.

Remark: Students learn to identify trends, make informed judgments, and produce meaningful predictions.

4. Database Management Systems (DBMS)

a. Creating and Maintaining a Simple Database

• Concepts: Tables, records, fields, primary keys.

• Example: A student database containing student ID, name, grade, and email.

b. Forms for Data Entry

• Definition: User-friendly interface for entering data into a database.

• Example: A form to input new student records instead of typing directly into a table.

c. Data Extraction and Manipulation

• Querying Data: Use filters, sorting, and selection to extract specific information.

  • SELECT * FROM Students; – Retrieve all records.

  • SELECT Name, Grade FROM Students WHERE Grade > 60; – Filtered query.

  • ORDER BY Grade DESC; – Sort results.

• SQL Basics:

• Reports: Summarise data for intended users, e.g., average grades per class.

Remark: Understanding DBMS concepts and query operations equips students to manage real-world data efficiently.

5. Summary

• Spreadsheets: Use cell references, operators, and functions to solve problems.

• Data manipulation: Filter, search, sort, and work across multiple worksheets.

• Data analysis: Pivot tables, pivot charts, and what-if scenarios to identify trends and make predictions.

• Databases: Create and maintain tables, use forms for input, query data, and generate reports.

• Skill Application: These techniques support critical thinking, informed decision-making, and real-world problem-solving.

Remark: Mastery of data manipulation and analysis prepares students for tasks in business, finance, research, and everyday IT use.