Задание 5_2

Задание 5_2

по теме: «Составление отчета. Составление итоговых отчетов. Составление консолидированных отчетов».

.

Ход работы

Задача 1. Фирма "Рога и копыта" закупила для своих подразделений мониторы и принтеры. Общие результаты покупки представлены в таблице, показанной на рис. Столбец общая стоимость рассчитать по формуле.

1. Получить общее количество и общую стоимость всех мониторов, общее количество и общую стоимость всех принтеров, а также общее количество и общую стоимость всех купленных изделий.

2. Определить средние значения цены мониторов и цены принтеров.

3. Рассчитать общее количество и общую стоимость каждой разновидности мониторов и принтеров.

4. Определите средние значения цены каждой разновидности мониторов и принтеров.

Задача 2. В таблице представлены сведения о ряде стран. Плотность населения рассчитайте по формуле.

2.1 Получить общую площадь и общее число жителей для каждого полушария Земли, а также эти показатели для всех представленных в таблице стран.

2.2 Определить средние значения площади стран и их населения для каждого полушария Земли

2.3 Рассчитать общую площадь и общее число жителей для каждой части света

2.4 Определить средние значения площади стран и их населения для каждой части света.

Задача 3. В таблице представлены сведения о трех акционерах фирмы "Купи-Продай". Общую стоимость рассчитайте по формуле.

3.1 Определить общее количество акций и их общую стоимость для каждого акционера;

3.2 Определить среднее значение количество акций у каждого акционера;

3.3 Определить общее количество акций каждого выпуска и их общую стоимость;

3.4 Определить общее количество акций каждого вида (привилегированная и обыкновенная) и их общую стоимость.

Задача 4. В таблице представлены сведения о прохождении автомобилями участков пути. Длину участка рассчитайте по формуле.

Получить:

4.1 общую длину участков пути, пройденных автомобилями каждой фирмы, и общее время движения автомобилей каждой фирмы;

4.2 среднюю длину участков пути, пройденных автомобилями каждой фирмы, и среднее время движения автомобилей каждой фирмы;

4.3 общую длину участков пути, пройденных автомобилями каждого вида (легковыми и грузовыми) и общее время движения этих автомобилей;

4.4 среднюю длину участков пути, пройденных автомобилями каждого вида (легковыми и грузовыми) и среднее время движения этих автомобилей;

4.5 общую длину участков пути, пройденных однотипными автомобилями (грузовыми фирмы Fiat, легковыми фирмы Nissan и т. д.), и общее время движения этих автомобилей;

4.6 среднюю длину участков пути, пройденных однотипными автомобилями (грузовыми фирмы Fiat, легковыми фирмы Nissan и т. д.), и

4.7 среднее время движения этих автомобилей.

Задача 5. Консолидация данных.

Консолидация позволяет обобщать информацию, расположенную на нескольких рабочих листах.

ПРИМЕР. На рабочих листах с именами "январь", "февраль", "март" приведены фамилии торговых агентов и количество сделок, которые они совершили в течение месяца. Построить сводку за первый квартал.

Решение. Чтобы освоить некоторые полезные приемы, начнем решать задачу "с нуля". Создадим новую рабочую книгу. Переименуйте листы: дайте им названия "Январь", "Февраль", "Март", "1 квартал".

Внесение заголовков одновременно в несколько листов. Выделите все листы с названиями месяцев: для этого перейдите на лист "Январь", нажмите клавишу Shift и, не отпуская ее, щелкните по ярлычку листа "Март". Будут выделены все листы рабочей книги, при этом активным листом останется "Январь". Введите в ячейку А1 "Фамилия И.О.", в

ячейку В1 "Сделки", в С1 "Объем" . Щелкните по ярлычку листа "Февраль", выделение с нескольких листов будет снято. Убедитесь, что в ранее выделенные листы внесен один и тот же текст в ячейки А1, В1, С1. Для иллюстративных целей поменяйте на листе "Февраль" содержимое ячеек: в В1 "Объем", а в С1 "Сделки".

Ввод информации в листы. Введите в листы с названиями месяцев информацию в соответствии с таблицей (названия месяцев вводить не нужно, они на ярлычках листов).

Консолидация. Прежде всего нужно выделить ячейку, которая будет служить верхней левой ячейкой для блока с результатами консолидации. Для этого перейдем на лист "1 квартал" и выделим ячейку А1.

Выберем в меню "Данные/Консолидация". Появится диалоговое окно "Консолидация". В выпадающем списке "Функция:" выставлено "Сумма", т.е. выбранные данные будут суммироваться. Но можно выбрать и другую итоговую функцию: посмотрите список.

Следующее поле: "Ссылка". Выполним последовательность действий: установив фокус ввода в этом поле, будем по очереди выделять диапазоны для консолидации; когда в этом поле появится очередной диапазон, щелкнем кнопку "Добавить" — адрес диапазона переместится в окно "Список диапазонов".

Итак, щелкнем мышью в поле "Ссылка:". Щелкнем по ярлычку листа "Январь" (в поле ввода появится "Январь!" — формируется адрес). Выделим блок А1:С4 (в поле ввода "Январь!$А$1 :$С$4") — вокруг блока бегущая пунктирная рамка. Щелкнем кнопку "Добавить" — адрес диапазона окажется в поле "Список диапазонов:". Аналогично добавим диапазоны "Февраль!$А$1:$С$3" и "Март!$А$1:$С$5". Список диапазонов консолидации сформирован.

В диалоговом окне имеется блок "Использовать в качестве имен" из двух флажков "подписи верхней строки" и "значения левого столбца". Установим оба флажка. Эти флажки нужно установить, потому что информация в таблице будет идентифицироваться по названиям строк и столбцов. Если бы таблицы по месяцам имели одинаковую структуру, но разные названия столбцов, например, на одном листе столбец называется "Сделки", а на другом — "Количество сделок", но их расположение в таблице одинаково, тогда следовало снять флажок "подписи верхней строки". Установите флажок "создавать связи с исходными данными". После щелчка по кнопке "ОК" на рабочем листе появится таблица:

Внесите изменения на одном из листов книги. Проверьте, изменилось ли значение в консолидированной таблице.

Задача6. Разместите на одном листе данные о Западном полушарии (задача2), а на втором – о Восточном. На третьем листе с помощью консолидации данных получите сведения по данным обеих таблиц: общую площадь и общее число жителей для каждого полушария Земли.