Цель работы:
- научить студентов осуществлять анализ данных, расположенных на нескольких листах
После проведения данного лабораторного занятия студент должен:
знать:
- возможности анализа данных в электронных таблицах
- понятие консолидация
- понятие сводная таблица
уметь:
- консолидировать данные
- составлять сводные таблицы с помощью мастера
- применять специальное копирование, трехмерные ссылки при анализе данных
I. Входное контрольное задание
Для проведения лабораторного занятия студент должен ответить на поставленные вопросы:
- Как установить ширину столбца по ширине данных?
- Как установить перенос слов в ячейке?
- Как просуммировать введенные числовые данные?
- Как увеличить или уменьшить разрядность числовых данных?
- Как переименовать лист?
- Как сгруппировать листы?
- Как объединить ячейки?
II. Содержание
Исходные табличные данные для проведения лабораторного занятия взяты из газеты "Мир информации", №34 и №36 за 2003г. В ней приводились данные о преступлениях, совершенных в Краснодарском крае за отдельные дни в сентябре 2003 года. Возникает проблема консолидации данных.
Консолидация данных – объединение данных, расположенных на разных листах.
Консолидировать данные в MS Excel 2003 можно несколькими способами:
- применяя нужную по смыслу операцию при специальном копировании;
- с помощью трехмерных ссылок;
- используя инструмент Данные/консолидация;
- создавая отчет сводной таблицы.
- Для проведения лабораторного занятия необходимо подготовить три однотипных таблицы с числовыми данными.
- Для быстрого отображения необходимых таблиц с исходными данными сгруппируйте (выделите) три листа (удерживая клавишу Ctrl, щелкните левой кнопкой мыши по ярлыкам листов). Не снимая выделения, начните отображать таблицу с рис. 1.
- Чтобы сэкономить время при подготовке таблиц с исходными данными, во всех трех таблицах в диапазоне B3:D10 набейте одинаковые числовые данные. Не снимая выделения с листов, отразите данные таблицы на первом листе, и они автоматически появятся на остальных листах.
- Итог в строке ВСЕГО подсчитайте с помощью функции СУММ (используйте автосумму)
- Переименуйте листы как показано на рис.1
Рассмотрим варианты объединения данных.
1. Специальное копирование
- Выделите и скопируйте диапазон ячеек B3:D10 на листе 7-9_сент.
- Просто вставьте скопированный диапазон на лист консолидация в тот же диапазон (B3:D10)
- Выделите и скопируйте диапазон ячеек B3:D10 на листе 21-24_сент.
- Вставьте скопированный диапазон на лист консолидация в тот же диапазон (B3:D10) используя специальную вставку (в контекстном меню рядом с командой Вставить
- В раскрывшемся окне выберите требуемую операцию Сложить и щелкните по кнопке ОК
Задача выполнена: данные из двух таблиц сложились на листе консолидация.
2. Трехмерные ссылки
- Очистите диапазон ячеек B3:D10 на листе консолидация
- Выделите ячейку листа консолидация (В3), в которой должны получить суммарный результат данных, расположенных на предыдущих листах
- Введите формулу ='7-9_сент'!B3+'21-24_сент'!B3
Ход написания формулы:
- Поставьте знак равенства в ячейку B3.
- Щелкните по ярлыку листа 7-9_сент
- Щелкните по ячейке В3
- Щелкните по ярлыку листа 21-24_сент
- Щелкните по ячейке В3
- Нажмите клавишу Enter
- Остальные ячейки на листе консолидация заполняются формулами аналогично. Так как диапазоны ячеек одинаковые, воспользуйтесь автозаполнением для копирования формулы.
- Покажите полученные результаты преподавателю
3. Данные/консолидация
- Очистите диапазон ячеек B3:D10 на листе консолидация
- Выделите ячейку B3 листа консолидация и выберите команду меню Данные/Консолидация
- В появившемся диалоговом окне из раскрывающегося списка Функция выберите Сумма
- Установите курсор в поле Ссылка и, пользуясь кнопкой со стрелочкой в конце этого поля, перейдите к первому из листов с исходными данными, щелкнув мышью по его ярлыку. Выделите диапазон ячеек B3:D10.
- Вернитесь к окну консолидация с помощью все той же кнопки со стрелочкой, щелкните по кнопке Добавить
- Повторите эту последовательность действий (3.4,3.5) для следующего листа с исходными данными
- Щелкните по кнопке ОК окна Консолидация.
Задача выполнена: данные из двух таблиц сложились на листе консолидация.
4. Сводные таблицы
Этот метод объединений данных сходен с консолидацией, однако обладает большей гибкостью в отношении реорганизации категорий. Создавать сводную таблицу помогает Мастер сводных таблиц. - смотри документ
III. ПЕРЕЧЕНЬ КОНТРОЛЬНЫХ ВОПРОСОВ
Для защиты лабораторного занятия студент должен ответить на поставленные вопросы:
- С помощью каких изученных средств программы осуществляется анализ данных?
- Как осуществляется специальная вставка?
- Что можно сделать с помощью специальной вставки?
- Что такое консолидация?
- Какие вам известны способы консолидации данных?
- Как построить итоговую таблицу?
- Что такое трехмерные ссылки?
- Как отображаются трехмерные ссылки, если имя листа содержит только буквы? если начинается с цифры? Проведите эксперимент.
- Как убрать итоги по столбцам и по строкам в итоговой таблице?
- Какие инструменты содержит панель Сводные таблицы?
- Как построить сводную диаграмму?
- Чем визуально сводная диаграмма отличается от обычной?