2. Консолидация

Консолидация данных предполагает объединение данных из разных источников. Чтобы подвести итоги и составить отчет по результатам нескольких листов, можно консолидировать данные из отдельных листов в основном листе. Листы могут находиться в той же книге, что и основной лист, или в других книгах. При консолидации данных они компонуются так, что их становится проще обновлять и обобщать на регулярной основе или по требованию. В Excel существует две возможности консолидации:

1. Консолидация по расположению. Её использовуют в том случае, если данные всех исходных областей имеют одинаковыу структуру, т.е. одинакое количество строк и столбцов, хранящих однотипные данные. Например, если имеются данные из нескольких листов, созданных на основе одного шаблона.

2. Консолидация по категории. Её используют в том случае, если требуется обобщить набор листов, имеющих одинаковые заголовки рядов и столбцов, но различную организацию данных. Этот способ позволяет консолидировать данные с одинаковыми заголовками со всех листов.

3. Таблица подстановки

 Таблицей подстановки данных называется диапазон ячеек, показывающий, как изменение значений подстановки влияет на возвращаемый формулой результат. Для построения таблицы подстановки необходимы:

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

• один или два списка значений, которые будет подставляться в ячейки ввода (исходные данные могут располагаться либо в столбце, либо в строке, либо в столбце и строке);

• формула подстановки, которая ссылается на ячейки ввода (для таблиц подстановки с одной переменной формул может быть несколько);

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

В Excel можно построить таблицы подстановки двух видов:

• Таблица подстановки с одной изменяющейся переменной

• Таблица подстановки с двумя изменяющимися переменными

4. Практические задания

   1. Занятие 1. Фильтрация,подведение итогов, таблицы подстановки

1. Создать рабочую книгу, оформить (на Листе1) таблицу «Переоценка основных средств производ­ства» (Рис. 6) и произвести вычисления.

2. Сформируйте и заполните накопительную ведомость по переоценке основных средств производства, которая приведена ниже. Значения балансовой, остаточной и восстановительной стоимостей объектов, а также стоимость износа считать в млн. руб.

Рис. 6. Исходные данные

3. В графы, которые не выделены другим цветом, введите исходные данные.

4. Составьте и введите формулы в графы, которые закрашены серым цветом.

5. Используя значения балансовой стоимости (БС) и износа объекта (ИО), рассчитайте:

   • Остаточная стоимость объекта (ОС) = БС- ИО

   • Восстановительная полная стоимость объекта (ВП)=БС*К

   • Восстановительная остаточная стоимость объекта (ВО)=ОС*К,

   • где К=3, если БС>500 млн.руб., иначе К=2.

6. Добавьте в ведомость новую графу Вид объекта и присвойте всем объекта Цех № 1 – Цех № 4 вид основ­ной, а всем остальным объектам присвоить вид вспомогательный.

7. Выполните сортировку ведомости по возрастанию видов объектов, а внутри каждого вида – по возрастанию наименования объектов.

8. Отберите с помощью Автофильтра только вспомогательные объекты. После анализа результатов фильт­рации верните таблицу в исходное состояние.

9. Скопируйте исходную таблицу на Лист2. Рассчитайте общую (суммарную) балансовую стоимость, износ и общую (суммарную) остаточную стоимость всех основных и вспомогательных видов объек­тов с помощью команды Итоги.

10. На Листе1 с помощью команды Расширенный фильтр сформируйте накопительную ведомость по тем объектам, балансовая стоимость которых > 500 млн. руб. Включите в новую ведомость следующие графы:

• наименование объекта;

• балансовая стоимость;

• остаточная стоимость;

• восстановительная полная стоимость.

11. Используя таблицы подстновок решите следующие задачи:

• Для разной величины процентной ставки (5%, 7%, 9%, 10%) и различных размеров ежемесячных взносов (7 000, 9 000, 10000) определить сроки накопления 500.000 руб.

• Определить срок погашения ссуды в 200 000 руб., взятой под разные проценты (5%,6%, 10%, 12%) при ежемесячных выплатах 12000 руб.

• Для разной величины процентной ставки (8.75%, 9%. 9.25%. 10%) определить размер ежемесячных выплат по ссуде 200.000 руб., выданной на 5 лет

• Для разной величины процентной ставки (8,75%, 9,00%, 9,25%,10%) и различных сроков погашения (5, 10, 15,20 лет) определить размер ежемесячных выплат по ссуде 200.000 руб

12. Результаты сохраните на рабочем диске и покажите преподавателю.