Microsoft Excel предоставляет возможность объединять и связывать рабочие листы таким образом, чтобы в результате ввода значений или выполнения вычислений в одной из таблиц, изменялось содержимое других таблиц.
Создадим сводную таблицу (таблица 3) для анализа данных за три месяца, связывающую показатели дебита и добычи нефти за эти месяцы.
Таблица 3 – Сводные показатели за 3 месяца
| A | B | C |
| Название скважины | Всего | Нефти, баррелей/сут |
| Итого |
Поместите эту таблицу на четвертом листе, дайте ему название «Анализ добычи за три месяца». Введите заголовок и шапку таблицы. Скопируйте содержимое столбца В с названиями скважин и строкой «Итого» из любого листа. Для этого выделите эти данные В3:В14, щелкните кнопку Скопировать на вставке Главная в разделе Буфер обмена, вернитесь на лист «Анализ добычи за три месяца» и в ячейке А1 щелкните кнопку Вставить.
Консолидация данных в электронных таблицах позволяет объединять данные из областей-источников и выводитьих в область назначения. При консолидации могут использоваться различные функции, такие как суммирования, расчета среднего значения и др. Кроме того, можно создавать связи с исходными данными в областях-источниках или не создавать. При создании связей область назначения будет автоматически обновляться при внесении изменений в областях-источниках. Проверим и сравним, как работает консолидация в Excel в двух случаях: без создания связей с исходными данными и с их созданием.
Консолидируем данные из столбцов H и I за три года без создания связей. Для этого на листе «Анализ добычи за три года» щелкните на ячейке В3, выберите вкладку Данные, раздел Работа с данными, выберите кнопку Консолидация. В появившемся окне «Функция» выберите функцию Сумма. Затем в поле Ссылка определите области-источники, которые нужно консолидировать. Для этого перейдите на лист «Январь» и выделите ячейки Н5:I13. Потом в окне Консолидация нажмите кнопку Добавить. Перейдите на лист «Февраль», выделите такие же ячейки, щелкните Добавить в окне Консолидация. Аналогично для листа «Март» повторите те же действия. Проверьте, снят ли флажок Создавать связи с исходными данными. Нажмите кнопку ОК. Теперь на листе «Анализ добычи за три года» появятся итоговые данные за три месяца.
Теперь консолидируем данные из столбцов Н и I за три года, создав связи с исходными данными. Для решения этой задачи по аналогии скопируйте заголовок, шапку таблицы и исходные данные столбца А с листа «Анализ добычи за три года» на следующий лист, который назовите «Итог со связью». Выполните консолидацию данных по аналогии с предыдущей задачей. Затем установите флажок «Создавать связи с исходными данными» и нажмите ОК.
Вернитесь на лист «Январь». Внесите изменения в ячейку E6. Проследите, как идет пересчет по формулам на этом листе. Запомните новые значения расчетов. Вернитесь на лист «Анализ добычи за три года» и убедитесь, что в нем нет изменений. Теперь посмотрите лист «Итог со связью», Вы увидите в нем изменения. Это результат установления связей.
3. Работа со структурированной таблицей. Сравните внешний вид полученных таблиц. В таблице с листа «Итог 2» изменился вид экрана: в его левой вертикальной части появились символы структуры документа и некоторые строки стали невидимыми. Символы структуры бывают двух типов: кнопки с номерами уровней – кнопки 1 и 2, находящиеся в левом верхнем углу экрана и знаки + (плюс) и/или - (минус), позволяющие соответственно раскрывать или скрывать детали структурированного документа. Если щелкнуть на кнопке 2, то таблица «распахнется», предоставив возможность просмотреть консолидируемые данные за три месяца. Щелкнув по кнопке 1 можно скрыть исходные данные из таблиц-источников. Проверьте это на своей таблице. Щелкните по любому из знаков +(плюс). Результатом будет открытие одной из составляющих итоговой таблицы. Щелкнув по значку - (минус), вы скроете исходные данные из таблиц-источников.
Содержание отчета и его форма:
Ознакомьтесь с теоретическим обоснованием. Выполните все описанные пункты задания, письменно оформите отчет, который должен содержать:
- тему, цель лабораторной работы;
- виды ссылок в Excel;
- способы вызова Мастера формул;
- перечень изученных в данной работе возможностей Excel.
Контрольные вопросы и защита работы:
