CATEGORIES:

# Laboratory work ¹ 10

Calculating totals. Data consolidation.

Objective:Learn how to calculate totals, use the data consolidation, data structure.

For summary information in Excel, use the command Data/Results, which allows you to apply one of the aggregate functions to a certain field or fields. When this outcome may be formed not only to the entire list, but also to separate groups of records having the same value in one of the fields.
Before executing the Results list must be sorted by the field, which provides for grouping records. After sorting, we can summarize the list as a whole and totals for a group of records.

Team Data/Results... opens a dialog box subtotals containing three input fields. In the input field «At each change in» states (selected from the drop-down list) is the field on which to group the records for subtotals.
In the list of input fields «Operation» is selected function calculating totals. To summarize numerical fields use the following functions: Sum, Average, Max, Min, Multiplication, Quantity of numbers and functions that calculate the deviation from the mean.

To summarize the results for non-numeric fields is the default operation number of values, calculates the number of records. In the input field «Add up the results for» stand (left mouse button) columns that are summed up.
Besides the summary rows command Data/Results... forms the so-called structure that corresponds to the grouping of data to calculate the subtotals and grand totals.
Data Consolidation – tool that allows combining data from source areas and bringing them to the destination. When you consolidate data may have different functions: summations, the arithmetic mean, determine the maximum and minimum values, etc.

Consolidation can be performed without creating links to the source data or the creation of links. In the second case MS Excel automatically adjusts the totals when they change. To do this, select the first cell range to consolidate on the sheet in the spreadsheet window click the Add button Consolidation. Then repeat the same steps for the other intervals cells that you want to consolidate

JOB 1.Calculating totals

1. Open the file Full assortment created in the Laboratory work ¹ 9

2. With automatic calculation of totals, calculate the total number of copiers, faxes, and the total amount of each product:

A) Sort the data in the table in the form of goods command Data – Sort

B) Activate one of the cells in the table and choose Data – Results dialog box and select the operation Amount

C) In the "Add totals" field select the number and amount, in complete, click on OK

D) Display screen only summary data.

E) Restore displays the initial values.

3. Using the command Data – Results, answer how many different models for each product?

4. Find the number of product models, pre-sorted data by Goods.

5. Determine the total amount for each type of goods; pre-sort the data by column Name.

6. Which sum is necessary to have to buy the most expensive copier or fax machine?
7. Organize data by product and get the maximum amounts of goods.

8. What amount you need to have to buy the cheapest copier or fax machine? Find the minimum value of the sum of the goods.
9. How many different kinds of copiers and faxes? Find the number of product names.
10. Which average price of a copier or fax? Find the mean price of the goods.

11. Consider combining multiple outcomes to 2.3 points:

B) Re-open dialog box «Interim» and follow step 3 Mode Off «Replace current results»;

B) Displayed on the screen only summary data.

JOB 2.

1. Create a new worksheet and enter the data in the table (Table 35):

2. With automatic calculation of totals Data – Results turnover determine each salesperson for a specified period of time. As a result, the table will be supplemented by strings containing the totals for each salesperson:

2.1. Display on the screen only the totals;

2.2. Redisplay the original values.

3. Consider combining multiple outcomes. Add the table is another indicator the number of cars sold by each seller (3 lines) and the total of the cabin (1 line):

3.1. Display on the screen only the totals;

3.2. Remove the line to the totals.

Table 35 Company «Lucky Wheel»

 Shop assistant Brand Supplier Turnover Date Alimov Peugeot 605 Astana 11000,00 16.09 Dosov VW Passat Moskow 12200,00 14.09 Samalov Opel Fontera Kostanay 16000,00 13.09 Alimov Mercedes 180Ñ Àñòàíà 19000,00 15.09 Alimov Renault Safran Kostanay 17000,00 16.09 Samalov Mercedes 190 Astana 11000,00 12.09 Alimov BMW 325 Moskow 15000,00 15.09 Samalov Audi 100100 Kostanay 10999,00 15.09 Dosov BMW 520 Moskow 10500,00 12.09 Alimov Ford Mondeo Astana 12996,00 13.09

3. On a clean sheet of the electronic version of the table 36, this is a report card on the payroll, and enters the data into the table arbitrarily (¹, Name, Department, Job Title, Charge)

Table 36 Payroll

 ¹ Full name Department Position Charge Retained By issuing

Make payroll summary information indicating the departments and across the enterprise as a whole, retaining 10% of each employee.

JOB 3.

Data Consolidation

1. On a blank worksheet place the data in the following sequence:

• Rename a clean sheet on «January», enter the data for January (Figure 3);

• To calculate the total revenues, total expenses, use AutoSum;

• The final line in the balance now using the formula: Final Balance = Balance + Total revenue - Total expenses.

• Make a copy of the sheet (Rename/copy, check the box Create a copy)

• Rename the copy of the resulting sheet to «February,» and by removing only the previous data (formula should be retained), enter data for February in column B (Figure 4). Value of the initial balance sheet in February equals ending balance for January (for the previous month)

• Similarly, create lists of «March», just a block away from the Figure 5,6

My budget for the quarter (1 way )

1.In the sheet "Total for the quarter" pinch general revenues and expenses of various tables ( sheets January, February, March) , using the summation formula references to sheets.

My budget for the quarter (2 way)

2. For consolidated data, enter a new worksheet. Rename this sheet on Consolidation_1. The worksheet activate cell A1.

2.1. Execute Data Consolidation. In the dialog box «Consolidation» create a list of ranges consolidation (data sheets in January, February, March) and enable the option «in the left column»

2.2. Establish communication between the consolidated and source data. To do this, enable the option "create links to source data"

3. Change the data in the source tables and observe the changes on the sheet Consolidation_1

4. Enter a new leaf. Rename this sheet on Consolidation_2.

5. Perform consolidation worksheet data Consolidation_2. For a consolidated table include the options «in the left column» and «signature top line».

6. Compare the two consolidated table.

 Personal Budget January Personal Budget February Initial balance Initial balance Salary Salary Premium Premium Additional earnings Additional earnings Total revenue Total revenue Housing and utilities Housing and utilities Food Food Clothing Clothing Vacation Vacation Other expenses Other expenses Total expenditure Total expenditure Ending balance Ending balance

Figure 3 Figure 4

 Personal Budget March Personal Budget Total for the quarter Initial balance Initial balance Salary Salary Premium Premium Additional earnings Additional earnings Total revenue Total revenue Housing and utilities Housing and utilities Food Food Clothing Clothing Vacation Vacation Other expenses Other expenses Total expenditure Total expenditure Ending balance Ending balance

Figure 5 Figure 6

JOB 4.

1. On a blank worksheet, create a spreadsheet and enter the data shown in Figure 7, the next clean worksheet, create a table and enter the data shown in Figure 8, and the third worksheet, create a spreadsheet and enter the data shown in Figure 9:

2. Rename sheets: first –Karaganda, the second –Pavlodar and third –Taraz. To consolidate data pull the book Sheet 4. Rename this sheet consolidation. The worksheets activate the cell A4.

3. Execute Data Consolidation. In the dialog box, enable the following option. Keep in mind that in the dialog box «Link» is necessary to go consistently indicate the range of cells filled with data, completing the selection command «Add» button

4. After you click OK, activate that worksheet in which the results should be consolidated.

5. Save the file.

6. Establish communication between consolidated and source data. To do this, turn on the option to create a link with the original data.

7. Change the data in the source tables and watch the changes in the Consolidation sheet.

 Dealership "Lucky Wheel" Dealership "Lucky Wheel" Dealership "Lucky Wheel" branch Karaganda branch Pavlodar branch Taraz Brand number Brand number Brand number BMW Brand Brand ford BMW BMW Mercedes ford ford Peugeot Mercedes Mercedes Reno Peugeot Peugeot Volkswagen Reno Reno Audi Volkswagen Volkswagen Opel Audi Audi Porsche Opel Opel Ferrari Porsche Porsche Total Ferrari Ferrari

Figure 7 Figure 8 Figure 9

Date: 2015-12-24; view: 1996

 <== previous page | next page ==> Sort and list processing. AutoFilter and Advanced Filter. | Laboratory work ¹ 11
doclecture.net - lectures - 2014-2024 year. Copyright infringement or personal data (0.007 sec.)