Лабораторная работа 4
Цель работы: Получение навыков решения задач анализа средствами консолидации данных, связывания таблиц и сводных таблиц.
Часто возникает необходимость при решении различных экономических задач дублирование таблиц с идентичными в них данными, либо использование одних и тех же столбцов (строк) в структурно отличающихся между собой таблиц. Нередки случаи, когда требуется создание итоговой таблицы по определенному временному периоду на основе нескольких таблиц, например, построение таблицы результирующей прибыль предприятия за год, на основе расчетных таблиц по месяцам. В MS Excel имеется ряд инструментов для реализации таких целей.
Простейшим способом связывания диапазонов данных в разных таблицах осуществляется посредством копирования из таблицы с данными нужного диапазона в буфер обмена, а затем вставки в другую таблицу, которую хотите связать с искомой. В большинстве случаев рекомендуется в связываемую таблицу осуществлять копирование не данных, а лишь ссылки на ячейки той таблицы, где эти данные находятся. Реализуется данная операция в MS Excel через Главное меню – Правка – Специальная вставка – Ссылки.
При консолидации данных объединяются значения из нескольких диапазонов данных.
В MS Excel существует несколько способов консолидации данных: использование трехмерных формул, по положению и по категории.
Использование трехмерных формул является наиболее рациональным методом, который заключается в создании формул, содержащих ссылки на ячейки в каждом диапазоне объединенных данных. Понятие трехмерные формулы означает создание формул, которые содержат ссылки на несколько листов. Такие трехмерные ссылки можно использовать в формулах для любого типа и расположения данных. Для применения данного метода консолидации данных достаточно ввести формулу и включить в нее ссылки на исходные ячейки каждого листа, содержащего данные, для которых будет выполняться консолидация.
Консолидация по положению предполагает нахождение данных всех исходных областей в одном месте и их размещение в одинаковом порядке. Для упрощения работы с данными рекомендуется диапазонам данных на всех листах, используемых для консолидации присвоить имена. Осуществить консолидацию по положению можно через Главное меню – Данные – Консолидация. Для связывание с исходными данными требуется выбрать соответствующее поле.
Консолидация по категории производится аналогично методу Консолидация по положению. Особенность данного метода заключается в консолидации данных с одинаковыми заголовками рядов и столбцов (как и при консолидации по положению) находящихся на разных листах, имеющих различную организацию данных. В этом случае обязательно необходимо указывать в исходных диапазонах подписи консолидируемых столбцов (строк) таблиц, а применяя метод консолидации по категории отметить расположение этих подписей в исходных диапазонах данных. Более подробную информацию о практическом применении методов консолидации данных можно получить в справке MS Excel.
Отчет сводной таблицы используется в случаях, когда требуется проанализировать связанные итоги, особенно для сравнения нескольких фактов по каждому числу из длинного списка обобщаемых чисел. Благодаря интерактивности отчета сводной таблицы, вы можете изменять представление данных для просмотра дополнительных подробностей или вычисления других итогов, таких как количество или среднее значение.
В отчете сводной таблицы каждый столбец или поле исходных данных становится полем сводной таблицы, в котором подводятся итоги нескольких строк.
Создать отчет сводной таблицы можно с помощью мастера сводных таблиц и диаграмм, через Главное меню – Данные – Сводная таблица. Мастер предлагает выбрать исходные данные на листе или во внешней базе данных. Затем он создает на листе область отчета и предлагает список доступных полей. При перетаскивании полей из окна списка в структурированные области выполняются подведение итогов и автоматическое вычисление и построение отчета.
1) Создать таблицы по образцу табл. 4.1 (файл exmpl.doc) на двух разных листах одной книги путем простого связывания их данных, а затем получить тот же результат, расположив таблицы в разных рабочих книгах.
2) Создать отчеты о доходах воображаемой фирмы за три подряд идущих месяца, например, по шаблону вида табл. 4.2 (файл exmpl.doc) на трех подряд идущих листах одной рабочей книги.
На четвертом листе этой же книги консолидировать данные всех трех таблиц, просуммировав доходы фирмы за все месяцы по каждой статье, применив трехмерные формулы.
На пятом листе книги просуммировать доходы фирмы за все месяцы по каждой статье, но с помощью консолидации по положению.
Уважаемый посетитель!
Чтобы распечатать файл, скачайте его (в формате Word).
Ссылка на скачивание - внизу страницы.
