Объединение и связывание таблиц для формирования итоговой ведомости

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

Создадим сводную таблицу (таблица 3) для анализа данных за три месяца, связывающую показатели дебита и добычи нефти за эти месяцы.

Таблица 3 – Сводные показатели за 3 месяца

Поместите эту таблицу на четвертом листе, дайте ему название «Анализ добычи за три месяца». Введите заголовок и шапку таблицы. Скопируйте содержимое столбца В с названиями скважин и строкой «Итого» из любого листа. Для этого выделите эти данные В3:В14, щелкните кнопку Скопировать на вставке Главная в разделе Буфер обмена, вернитесь на лист «Анализ добычи за три месяца» и в ячейке А1 щелкните кнопку Вставить.

Консолидация данных в электронных таблицах позволяет объединять дан­ные из областей-источников и выводитьих в область назначения. При консолидации могут использоваться различные функции, такие как суммирования, расчета среднего значения и др. Кроме того, можно создавать связи с исходными данными в областях-источниках или не создавать. При создании связей область назначения будет автоматически обновляться при внесении изменений в областях-источниках. Проверим и сравним, как работает консолидация в Excel в двух случаях: без создания связей с исходными данными и с их созданием.

Консолидируем данные из столбцов H и I за три года без создания связей. Для этого на листе «Анализ добычи за три года» щелкните на ячейке В3, выберите вкладку Данные, раздел Работа с данными, выберите кнопку Консолидация. В появившемся окне «Функция» выберите функцию Сумма. Затем в поле Ссылка определите области-источники, которые нужно консолидировать. Для этого перейдите на лист «Январь» и выделите ячейки Н5:I13. Потом в окне Консолидация нажмите кнопку Добавить. Перейдите на лист «Февраль», выделите такие же ячейки, щелкните Добавить в окне Консолидация. Аналогично для листа «Март» повторите те же действия. Проверьте, снят ли флажок Создавать связи с исходными данными. Нажмите кнопку ОК. Теперь на листе «Анализ добычи за три года» появятся итоговые данные за три месяца.

Теперь консолидируем данные из столбцов Н и I за три года, создав связи с исходными данными. Для решения этой задачи по аналогии скопируйте заголовок, шапку таблицы и исходные данные столбца А с листа «Анализ добычи за три года» на следующий лист, который назовите «Итог со связью». Выполните консолидацию данных по аналогии с предыдущей задачей. Затем установите флажок «Создавать связи с исходными данными» и нажмите ОК.

Вернитесь на лист «Январь». Внесите изменения в ячейку E6. Проследите, как идет пересчет по формулам на этом листе. Запомните новые значения расчетов. Вернитесь на лист «Анализ добычи за три года» и убедитесь, что в нем нет изменений. Теперь посмотрите лист «Итог со связью», Вы увидите в нем изменения. Это результат установления связей.

3. Работа со структурированной таблицей. Сравните внешний вид полученных таблиц. В таблице с листа «Итог 2» изменился вид экрана: в его левой вертикальной части появились символы структуры документа и некоторые строки стали невидимыми. Символы структуры бывают двух типов: кнопки с номерами уровней – кнопки 1 и 2, находящиеся в ле­вом верхнем углу экрана и знаки + (плюс) и/или - (минус), позволяющие соот­ветственно раскрывать или скрывать детали структурированного документа. Если щелкнуть на кнопке 2, то таблица «распахнется», предоставив воз­можность просмотреть консолидируемые данные за три месяца. Щелкнув по кнопке 1 можно скрыть исходные данные из таблиц-источников. Проверьте это на своей таблице. Щелкните по любому из знаков +(плюс). Результатом будет открытие одной из составляющих итоговой таблицы. Щелкнув по значку - (минус), вы скроете исходные данные из таблиц-источников.

Содержание отчета и его форма:

Ознакомьтесь с теоретическим обоснованием. Выполните все описанные пункты задания, письменно оформите отчет, который должен содержать:

- тему, цель лабораторной работы;

- виды ссылок в Excel;

- способы вызова Мастера формул;

- перечень изученных в данной работе возможностей Excel.

Контрольные вопросы и защита работы: