Консолидация данных в Excel. 4_Консолидация. Связывание и консолидация рабочих таблиц
 Скачать 104.52 Kb.
|
|
Связывание и консолидация рабочих таблиц Цель занятия. Изучение технологии связей между таблицами и консолидации данных в MS Excel. Ход работы  Запустите редактор электронных таблиц Microsoft Excel. Создадим 3 таблицы с отчетами о прибылях за 1 квартал, 2 квартал и полугодие, в которой получим суммарные значения за два квартала. Сначала переименуйте рабочие листы книги EXCEL в листы с названиями 1 квартал, 2 квартал, Полугодие с помощью контекстного меню. Выделите эти листы, щелкнув мышкой по ярлычку первого листа 1 квартал и, нажав клавишу Shift, по ярлычку последнего листа Полугодие. Ярлычки выделятся, т. е. будет включен групповой режим работы. Все, что мы будем набирать в первом рабочем листе, будет одновременно набираться и на всех выделенных листах, включенных в группу. В групповом режиме создайте шапку таблицы, занесите формулу подсчета прибыли и задайте денежный формат для ячеек B3:B5 (см. рис. справа).После создания таблиц с общими элементами (они создавались на всех выделенных рабочих листах), отменим выделение групп, для чего с помощью контекстного меню, выберите команду Разгруппировать листы. Внесите исходные данные на лист 1 квартал: Доходы = 2340,58 Расходы = 750,33; на лист 2 квартал: Доходы = 3425,85 Расходы = 975,34.  Для расчета полугодовых итогов щелкните на листе Полугодие на ячейке B3, нажмите на знак равно =, затем перейдите на лист 1 квартал, щелкните на нем по ячейке B3, затем нажмите +, перейдите на лист 2 квартал, щелкните на нем по ячейке B3, затем нажмите Enter. Формула для расчета полугодового дохода примет следующий вид: ='1 квартал'!B3+'2 квартал'!B3. Для подсчёта полугодовых значений Расходов и Прибыли, скопируйте эту формулу в ячейки B4:B5, используя маркер заполнения. Результаты работы представлены на рис. справа. В Excel существует удобный инструмент для подведения итогов по таблицам данных сходной структуры, расположенных на разных листах или разных рабочих книгах, — Консолидация данных. При этом одна и та же операция (суммирование, вычисление среднего и др.) выполняется по всем ячейкам нескольких прямоугольных таблиц, и все формулы Excel строит автоматически. Вставьте новый лист и установите на нем курсор в ячейку А3. На ленте Данные, в группе Работа с данными нажмите на кнопку Консолидация. В появившемся окне «Консолидация» выберите функцию — Сумма. В строке «Ссылка» сначала выделите на листе 1 квартал диапазон ячеек АЗ:В5 и нажмите кнопку Добавить, затем выделите на листе 2 квартал диапазон ячеек АЗ:В5 и опять нажмите кнопку Добавить (см. рис. 13.4). В списке диапазонов будут находиться две области данных за первый и второй кварталы для консолидации. В поле «Использовать в качестве имён» активизируйте опцию (поставьте галочку) значение левого столбца и нажмите кнопку ОК, произойдет консолидированное суммирование данных за первый и второй кварталы. Сравните полученные данные с результатами вычислений на листе Полугодие (они должны быть одинаковые). Сохраните файл под именем Отчет о продажах в свою папку. Проведем Консолидацию данных для подведения итогов по таблицам неоднородной структуры. Для этого добавьте 2 новых листа, переименуйте их в 3 квартал и 4 квартал. Наберите отчет по отделам за третий и четвертый кварталы (используйте там, где нужно групповой режим аналогично п. 1), значения прибыли и всего подсчитайте по формулам:     Добавьте новый лист и назовите его Полугодовой по отделам. Установите курсор на ячейку АЗ и проведите консолидацию за третий и четвертый кварталы по заголовкам таблиц. Для этого откройте окно консолидации (см. п. 4), приведите его к виду (см. рис. справа) и нажмите ОК. Произойдет консолидация данных. Обратите внимание, что все данные корректно сгруппированы
по их заголовкам (по отделам). В левой части экрана появятся так
называемые кнопки управления контуром (иерархической структурой). С их помощью можно скрывать или показывать исходные
данные.Выполните задание о реализации хлебобулочных изделий хлебозавода N 1
Разместите таблицы на 3-х разных листах. На 4-ом подсчитайте итоговые данные по реализации продукции хлебозавода (аналогично п.3). На 5-ом листе - консолидированные данные (столбец «Цена» в консолидированной таблице удалите). Выполните задание об уплате потребителей за электроэнергию за 3 месяца.
Разместите таблицы на 3-х разных листах. На 4-ом подсчитайте итоговые данные (сумму) по расходу электроэнергии за три месяца. На 5-ом листе, используя консолидацию данных, получите сведения о среднем потреблении энергии каждым потребителем за три месяца. |