Консолидация данных.
Для создания сводной таблицы на основе данных из разных таблиц используется команда Консолидация меню Данные.
Для создания консолидированной таблицы необходимо отвести отдельный лист рабочей книги. Расположив курсор мыши в ячейке, которая будет началом диапазона ячеек с итоговыми данными (сводной таблицы), необходимо активизировать эту команду и заполнить открывшееся диалоговое окно.
Сначала необходимо выбрать функцию (поле Функция), которая будет использоваться при объединении данных. Далее необходимо задать координаты всех диапазонов ячеек с данными, которые необходимо консолидировать (поле Ссылка). Если консолидированные диапазоны размещены на разных листах книги в одинаковых ячейках, то после введения первого диапазона нет необходимости выделять другие диапазоны, достаточно перейти на новый лист и нажать кнопку. Во время выделения диапазона окно консолидация разворачивается до размеров поля ввода. Этого самого результата можно достигнуть с помощью кнопки минимизации окна. После выделения каждого из диапазонов необходимо нажимать кнопку Добавить. В диапазон ячеек, который должен консолидироваться с другими, необходимо включать и заголовки строк, иначе можно потерять ориентацию в таблице.
В зависимости от того, по какому принципу должна осуществляться консолидация, обозначьте одну из опций, которая позволит использовать в качестве имен подписи верхней строки или значения левого столбца.
Если необходимо установить связь с выходными данными необходимо включить опцию Создавать связи с исходными данными. При этом произойдет структурирование таблицы. Консолидированные значения вычисляются на основе данных другого уровня структуры.
Подбор параметра.
Допустим, что известен размер вклада, который будет размещен на определённое время под заданный процент. Необходимо установить, во сколько раз увеличится вклад за указанное время, а также определить сумму выплат в конце периода. Потом можно будет определить также размер вклада, исходя из известных сумм выплат.
Пусть размер вклада составляет 150000 грн, срок вклада – 20 лет, процентная ставка – 5% (Рис.3). Коэффициент нарощения вычислен по формуле
(1+проц.ставка)^срок вклада,
а сумму выплат по формуле размер вклада * коэффициент нарощения.
Рис.3. Таблица для иллюстрации задачи.
При изменении размера вклада или процентной ставки изменяется и значение суммы выплат. Используя такую модель можно установить, как влияют исходные данные на конечный результат.
Допустим, что необходимо определить какими должны быть входные данные , чтобы через 20 лет сумма выплат составляла 500000 грн. Тут речь идёт или же об увеличении размера вклада, или об увеличении процентной ставки. Решить такую задачу можно с помощью команды Сервис → Подбор параметра.
Для использования этой функции необходимо выделить ячейку, в которой будет находиться результат (В6), и вызвать программу Подбор параметра. При этом откроется диалоговое окно, в котором необходимо заполнить соответствующие поля. В нашем случае ищем ответ на вопрос: каким должен быть размер вклада, чтобы через 20 лет сумма выплат составила 500000 грн.
В результате нажатия на кнопку ОК, на экране появится диалоговое окно Результат подбора параметра, которое информирует о том, что вычисление завершилось. Результат вычислений вносится в исходную таблицу после нажатия кнопки ОК этого диалогового окна (Рис.4).
Рис. 4. Результат подбора параметра в исходной таблице.