Меню
Разработки
Разработки  /  Информатика  /  Проверочные работы  /  Технология обработки информации с помощью электронных таблиц MS Excel. Анализ данных

Технология обработки информации с помощью электронных таблиц MS Excel. Анализ данных

В процессе выполнения практических примеров студент знакомится с возможностями электронных таблиц для консолидации и анализа данных, учится использовать трехмерные ссылки и составлять сводные таблицы.
10.03.2013

Описание разработки

Цель работы:

  • научить студентов осуществлять анализ данных, расположенных на нескольких листах

После проведения данного лабораторного занятия студент должен:

знать:

  • возможности анализа данных в электронных таблицах
  • понятие консолидация
  • понятие сводная таблица

уметь:

  • консолидировать данные
  • составлять сводные таблицы с помощью мастера
  • применять специальное копирование, трехмерные ссылки при анализе данных

I. Входное контрольное задание

Для проведения лабораторного занятия студент должен ответить на поставленные вопросы:

  1. Как установить ширину столбца по ширине данных?
  2. Как установить перенос слов в ячейке?
  3. Как просуммировать введенные числовые данные?
  4. Как увеличить или уменьшить разрядность числовых данных?
  5. Как переименовать лист?
  6. Как сгруппировать листы?
  7. Как объединить ячейки?

II. Содержание

Исходные табличные данные для проведения лабораторного занятия взяты из газеты "Мир информации", №34 и №36 за 2003г. В ней приводились данные о преступлениях, совершенных в Краснодарском крае за отдельные дни в сентябре 2003 года. Возникает проблема консолидации данных.

Консолидация данных – объединение данных, расположенных на разных листах.

Консолидировать данные в MS Excel 2003 можно несколькими способами:

  • применяя нужную по смыслу операцию при специальном копировании;
  • с помощью трехмерных ссылок;
  • используя инструмент Данные/консолидация;
  • создавая отчет сводной таблицы.
  1.  Для проведения лабораторного занятия необходимо подготовить три однотипных таблицы с числовыми данными. 
  2. Для быстрого отображения необходимых таблиц с исходными данными сгруппируйте (выделите) три листа (удерживая клавишу Ctrl, щелкните левой кнопкой мыши по ярлыкам листов). Не снимая выделения, начните отображать таблицу с рис. 1.
  3. Чтобы сэкономить время при подготовке таблиц с исходными данными,  во всех трех таблицах в диапазоне  B3:D10 набейте одинаковые числовые данные. Не снимая выделения с листов, отразите данные таблицы на первом листе, и они автоматически появятся на остальных листах.
  4. Итог в строке ВСЕГО подсчитайте с помощью функции СУММ (используйте автосумму)
  5. Переименуйте листы как показано на рис.1

Рисунок 1

Рассмотрим варианты объединения данных.

1. Специальное копирование

  • Выделите и скопируйте диапазон ячеек B3:D10  на листе 7-9_сент.
  • Просто вставьте  скопированный диапазон на лист консолидация в тот же диапазон (B3:D10)
  • Выделите и скопируйте диапазон ячеек B3:D10  на листе 21-24_сент.
  • Вставьте скопированный диапазон на лист консолидация в тот же диапазон (B3:D10) используя специальную вставку (в контекстном меню рядом с командой Вставить
  • В раскрывшемся окне выберите требуемую операцию Сложить и щелкните по кнопке ОК

Задача выполнена: данные из двух таблиц сложились на листе консолидация.

2. Трехмерные ссылки

  1. Очистите диапазон ячеек B3:D10 на листе консолидация 
  2. Выделите ячейку листа консолидация (В3), в которой должны получить суммарный результат данных, расположенных на предыдущих листах
  3. Введите формулу ='7-9_сент'!B3+'21-24_сент'!B3

Ход написания формулы:

  • Поставьте знак равенства в ячейку B3.
  • Щелкните по ярлыку листа 7-9_сент
  • Щелкните по ячейке В3
  • Щелкните по ярлыку листа 21-24_сент
  • Щелкните по ячейке В3
  • Нажмите клавишу Enter
  1. Остальные ячейки на листе консолидация заполняются формулами аналогично. Так как диапазоны ячеек одинаковые, воспользуйтесь автозаполнением для копирования формулы.
  2. Покажите полученные результаты преподавателю

3. Данные/консолидация

  1. Очистите диапазон ячеек B3:D10 на листе консолидация
  2. Выделите ячейку B3 листа консолидация и выберите команду меню Данные/Консолидация
  3. В появившемся диалоговом окне из раскрывающегося списка Функция выберите Сумма
  4. Установите курсор в поле Ссылка и, пользуясь кнопкой со стрелочкой в конце этого поля, перейдите к первому из листов с исходными данными, щелкнув мышью по его ярлыку. Выделите диапазон ячеек B3:D10.
  5. Вернитесь к окну консолидация с помощью все той же кнопки со стрелочкой,  щелкните по кнопке Добавить
  6. Повторите эту последовательность действий (3.4,3.5) для следующего листа с исходными данными
  7. Щелкните по кнопке ОК окна Консолидация.

Задача выполнена: данные из двух таблиц сложились на листе консолидация.

4. Сводные таблицы

Этот метод объединений данных сходен с консолидацией, однако обладает большей гибкостью в отношении реорганизации категорий. Создавать сводную таблицу помогает Мастер сводных таблиц. - смотри документ

III. ПЕРЕЧЕНЬ КОНТРОЛЬНЫХ ВОПРОСОВ

Для защиты лабораторного занятия студент должен ответить на поставленные вопросы:

  1. С помощью каких изученных средств программы осуществляется анализ данных?
  2. Как осуществляется специальная вставка?
  3. Что можно сделать с помощью специальной вставки?
  4. Что такое консолидация?
  5. Какие вам известны способы консолидации данных?
  6. Как построить итоговую таблицу?
  7. Что такое трехмерные ссылки?
  8. Как отображаются трехмерные ссылки, если имя листа содержит только буквы? если начинается с цифры? Проведите эксперимент.
  9. Как убрать итоги по столбцам и по строкам в итоговой таблице?
  10. Какие инструменты содержит панель Сводные таблицы?
  11. Как построить сводную диаграмму?
  12. Чем визуально сводная диаграмма отличается от обычной?

Содержимое разработки


Методические указания

для проведения лабораторного занятия по теме "Технология обработки информации с помощью электронных таблиц MS Excel. Анализ данных"


Цель работы:

  • научить студентов осуществлять анализ данных, расположенных на нескольких листах


После проведения данного лабораторного занятия студент должен:

знать:

  • возможности анализа данных в электронных таблицах

  • понятие консолидация

  • понятие сводная таблица


уметь:

  • консолидировать данные

  • составлять сводные таблицы с помощью мастера

  • применять специальное копирование, трехмерные ссылки при анализе данных



I. Входное контрольное задание

Для проведения лабораторного занятия студент должен ответить на поставленные вопросы:

  1. Как установить ширину столбца по ширине данных?

  2. Как установить перенос слов в ячейке?

  3. Как просуммировать введенные числовые данные?

  4. Как увеличить или уменьшить разрядность числовых данных?

  5. Как переименовать лист?

  6. Как сгруппировать листы?

  7. Как объединить ячейки?


II. Содержание

Исходные табличные данные для проведения лабораторного занятия взяты из газеты "Мир информации", №34 и №36 за 2003г. В ней приводились данные о преступлениях, совершенных в Краснодарском крае за отдельные дни в сентябре 2003 года. Возникает проблема консолидации данных.

Консолидация данных – объединение данных, расположенных на разных листах.

Консолидировать данные в MS Excel 2003 можно несколькими способами:

  • применяя нужную по смыслу операцию при специальном копировании;

  • с помощью трехмерных ссылок;

  • используя инструмент Данные/консолидация;

  • создавая отчет сводной таблицы.



  1. Для проведения лабораторного занятия необходимо подготовить три однотипных таблицы с числовыми данными.

  2. Для быстрого отображения необходимых таблиц с исходными данными сгруппируйте (выделите) три листа (удерживая клавишу Ctrl, щелкните левой кнопкой мыши по ярлыкам листов). Не снимая выделения, начните отображать таблицу с рис. 1.

Группировка листов позволяет отображать одну и ту же информацию одновременно на нескольких сгруппированных листах

  1. Чтобы сэкономить время при подготовке таблиц с исходными данными, во всех трех таблицах в диапазоне B3:D10 набейте одинаковые числовые данные. Не снимая выделения с листов, отразите данные таблицы на первом листе, и они автоматически появятся на остальных листах.

  2. Итог в строке ВСЕГО подсчитайте с помощью функции СУММ (используйте автосумму)

  3. Переименуйте листы как показано на рис.1

Рис.1. Данные о преступлениях, совершенных в Краснодарском крае в 2003г.


Рассмотрим варианты объединения данных.

1. Специальное копирование
  1. Выделите и скопируйте диапазон ячеек B3:D10 на листе 7-9_сент.

  2. Просто вставьте скопированный диапазон на лист консолидация в тот же диапазон (B3:D10)

  3. Выделите и скопируйте диапазон ячеек B3:D10 на листе 21-24_сент.

  4. Вставьте скопированный диапазон на лист консолидация в тот же диапазон (B3:D10) используя специальную вставку (в контекстном меню рядом с командой Вставить, см. рис.2) Рис.2 Контекстное меню

.

  1. В раскрывшемся окне выберите требуемую операцию Сложить и щелкните по кнопке ОК

Рис.3 Окно Специальная вставка

Задача выполнена: данные из двух таблиц сложились на листе консолидация.


2. Трехмерные ссылки
  1. Очистите диапазон ячеек B3:D10 на листе консолидация

  2. Выделите ячейку листа консолидация (В3), в которой должны получить суммарный результат данных, расположенных на предыдущих листах

  3. Введите формулу

='7-9_сент'!B3+'21-24_сент'!B3

Ход написания формулы:

  1. Поставьте знак равенства в ячейку B3.

  2. Щелкните по ярлыку листа 7-9_сент

  3. Щелкните по ячейке В3

  4. Щелкните по ярлыку листа 21-24_сент

  5. Щелкните по ячейке В3

  6. Нажмите клавишу Enter

  1. Остальные ячейки на листе консолидация заполняются формулами аналогично. Так как диапазоны ячеек одинаковые, воспользуйтесь автозаполнением для копирования формулы.

  2. Покажите полученные результаты преподавателю


3. Данные/консолидация
  1. Очистите диапазон ячеек B3:D10 на листе консолидация

  2. Выделите ячейку B3 листа консолидация и выберите команду меню Данные/Консолидация

  3. В появившемся диалоговом окне из раскрывающегося списка Функция выберите Сумма

  4. Установите курсор в поле Ссылка и, пользуясь кнопкой со стрелочкой в конце этого поля, перейдите к первому из листов с исходными данными, щелкнув мышью по его ярлыку. Выделите диапазон ячеек B3:D10.

  5. Вернитесь к окну консолидация с помощью все той же кнопки со стрелочкой, щелкните по кнопке Добавить

  6. Повторите эту последовательность действий (3.4,3.5) для следующего листа с исходными данными

Рис.4. Окно Консолидация

Если при консолидации данных диапазоны включают заголовки столбцов и строк, то для отображения их в таблице консолидации необходимо поставить соответствующие галочки в окне Консолидация – Использовать в качестве имен –подписи верхней строки, значения левого столбца.

Если поставить галочку рядом с полем Создавать связи с исходными данными, то таблица консолидации будет представлена как общая таблица всех данных с промежуточными итогами

  1. Щелкните по кнопке ОК окна Консолидация.

Задача выполнена: данные из двух таблиц сложились на листе консолидация.



4. Сводные таблицы

Этот метод объединений данных сходен с консолидацией, однако обладает большей гибкостью в отношении реорганизации категорий. Создавать сводную таблицу помогает Мастер сводных таблиц.

  1. Загрузите мастер сводных таблиц в меню Данные/Сводная таблица.

  2. На первом шаге укажите расположение данных, на основе которых создается отчет и выбирается его вид. Укажите, что данные находятся в нескольких диапазонах консолидации, и вид создаваемого отчета – сводная таблица (рис.5)


Рис.5 Мастер сводных таблиц – шаг1


  1. Щелкните по кнопке Далее. В появившемся окне выберите Создать одно поле страницы (рис.6) и перейдите к следующему шагу

Рис.6 Мастер сводных таблиц – шаг2


  1. В появившемся окне укажите диапазоны исходных данных (А1:D10) по принципу консолидации (п.3.4, 3.5) и перейдите к последнему шагу работы Мастера сводных таблиц.

  2. В последнем окне выберите месторасположение отчета – новый лист.

  3. Выберите вид формируемой таблицы, а также итоговую функцию с помощью кнопки макет (рис. 7,8)


Рис.7 Мастер сводных таблиц – шаг3


  1. По умолчанию программа применяет итоговую функцию Сумма. Для изменения итоговой функции надо щелкнуть мышью на заголовке Сумма по полю Значение

Проверьте, что применяете итоговую функцию Сумма, если нет, то измените в поле Операция (рис.8)

Рис.8 Изменение макета сводной таблицы

  1. Щелкните по кнопке ОК и завершите создание сводной таблицы щелчком на кнопке Готово в окне Мастера сводных таблиц. Получим таблицу, представленную на рис.9

Рис.9 Сводная таблица


  1. В сводной таблице в последней строке и в последнем столбце подводятся общие итоги в виде суммы значений элементов строк и столбцов. В данном примере они не имеют смысла. Скройте их, для этого щелкните по сводной таблице правой кнопкой и в контекстном меню выберите команду Мастер сводных таблиц. На последнем шаге щелкните по кнопке Параметры и в появившемся окне (рис. 10) уберите галочки рядом с общая сумма по столбцам, общая сумма по строкам

Рис.10 Параметры сводной таблицы

  1. В полях Строка и Столбец присутствуют значки раскрывающегося меню . Щелкните на них и получите список категорий (рис.11), отображенных в итоговой таблице. Проверьте все ли категории (строки таблиц) отражаются в итоговой таблице

Рис.11. Вспомогательные раскрывающиеся списки категорий

  1. В поле Столбец оставьте только столбец Зарегистрировано по краю. Для этого просто уберите галочки рядом с лишними столбцами в раскрывающемся списке.

  2. Для эффективного оформления полученной сводной таблицы предусмотрен специальный инструмент – автоформат сводной таблицы. Если панель инструментов Сводные таблицы не активна, щелкните правой кнопкой по таблице в контекстном меню выберите команду Показать панель сводной таблицы(рис.12)

  1. На панели щелкните по кнопке Формат отчета . Появится окно Автоформат с набором вариантов оформления отчетов (рис.13)

Рис.13 Автоформат сводной таблицы

  1. Выберите любой вариант оформления сводной таблицы, щелкните по кнопке ОК.

  2. Добавьте к своей работе диаграмму по сводной таблице. Для этого на панели Сводные таблицы щелкните по значку диаграммы Чтобы диаграмма была нагляднее, уберите в сводной таблице строку ВСЕГО (вернитесь к п.4.10). Ту же операцию можно выполнить прямо на листе с диаграммой, щелкнув по полю Строка.

  3. Покажите полученную таблицу и диаграмму преподавателю.



III. ПЕРЕЧЕНЬ КОНТРОЛЬНЫХ ВОПРОСОВ


Для защиты лабораторного занятия студент должен ответить на поставленные вопросы:


    1. С помощью каких изученных средств программы осуществляется анализ данных?

    2. Как осуществляется специальная вставка?

    3. Что можно сделать с помощью специальной вставки?

    4. Что такое консолидация?

    5. Какие вам известны способы консолидации данных?

    6. Как построить итоговую таблицу?

    7. Что такое трехмерные ссылки?

    8. Как отображаются трехмерные ссылки, если имя листа содержит только буквы? если начинается с цифры? Проведите эксперимент.

    9. Как убрать итоги по столбцам и по строкам в итоговой таблице?

    10. Какие инструменты содержит панель Сводные таблицы?

    11. Как построить сводную диаграмму?

    12. Чем визуально сводная диаграмма отличается от обычной?


14


-75%
Курсы профессиональной переподготовке

Учитель, преподаватель физики и информатики

Продолжительность 600 или 1000 часов
Документ: Диплом о профессиональной переподготовке
17800 руб.
от 4450 руб.
Подробнее
Скачать разработку
Сохранить у себя:
Технология обработки информации с помощью электронных таблиц MS Excel. Анализ данных (0.31 MB)

Комментарии 0

Чтобы добавить комментарий зарегистрируйтесь или на сайт