Page 507 - Computer Software Application TP - Volume 1

COMPUTER SOFTWARE APPLICATION - CITS

TASK 4:
As an HR analyst at a company managing employee information, you have been provided with two tables in
your Excel workbook: “EmployeeData” and “DepartmentData”. The “EmployeeData” table contains details of
employees, including their ID, name, and department ID, while the “DepartmentData” table includes information
about departments, including their ID and name.
Your task is to perform the following operations:

1 Merge the “EmployeeData” and “DepartmentData” tables based on the department ID to create a consolidated
dataset.
2 Add a new column in the consolidated dataset containing the name of the department for each employee.
3 Organize the workbook queries to ensure efficient management.

Employee Data Table:
Employee ID Name Department ID
001 John Smith 101
002 Emily Brown 102
003 David Lee 101

Department Data Table:

Department ID Department Name
101 HR
102 Finance
103 Marketing

Step 1: Merge Tables
1 Open Microsoft Excel and navigate to the “Data” tab.

2 Click on “Get Data” > “From File” > “From Workbook” to import both “EmployeeData” and “DepartmentData” tables.

492

CITS : IT & ITES - Computer Software Application - Exercise 76