Инфоурок / Информатика / Другие методич. материалы / Лабораторная работа "Агрегирование данных" по дисциплине "Информационные технологии в профессиональной деятельности"
Обращаем Ваше внимание, что в соответствии с Федеральным законом N 273-ФЗ «Об образовании в Российской Федерации» в организациях, осуществляющих образовательную деятельность, организовывается обучение и воспитание обучающихся с ОВЗ как совместно с другими обучающимися, так и в отдельных классах или группах.

Педагогическая деятельность в соответствии с новым ФГОС требует от учителя наличия системы специальных знаний в области анатомии, физиологии, специальной психологии, дефектологии и социальной работы.

Только сейчас Вы можете пройти дистанционное обучение прямо на сайте "Инфоурок" со скидкой 40% по курсу повышения квалификации "Организация работы с обучающимися с ограниченными возможностями здоровья (ОВЗ)" (72 часа). По окончании курса Вы получите печатное удостоверение о повышении квалификации установленного образца (доставка удостоверения бесплатна).

Автор курса: Логинова Наталья Геннадьевна, кандидат педагогических наук, учитель высшей категории. Начало обучения новой группы: 27 сентября.

Подать заявку на этот курс    Смотреть список всех 216 курсов со скидкой 40%

Лабораторная работа "Агрегирование данных" по дисциплине "Информационные технологии в профессиональной деятельности"

библиотека
материалов

Лабораторная работа № 16

Агрегирование данных


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


Теоретическая часть

Агрегирование данных списков состоит в формировании промежуточных итогов, создании сводных и консолидированных таблиц. Агрегирование информации выполняется для списков MS Excel, в записях которых имеются поля с повторяющимися записями. Наиболее типичными для анализа данных являются различного рода статистические итоги.


Промежуточные итоги

Для формирования промежуточных итогов требуется предварительная сортировка списка по поля группировки записей (рис.1). Команда меню Данные – Структура – Промежуточные итоги позволяет сформировать итоги определённого вида по указанным полям при каждом изменении поля группировки.

По отдельному полю группировки за одно выполнение команды можно сформировать итоги по нескольким полям.

Тип итоговой операции выбирается с учётом типа данных:

  • поля числового типа – операции типа сумма, среднее, максимальное, минимальное значение, количество чисел, количество значений, произведение, дисперсия и отклонение;

  • поля типа дата/время – операции типа количество значений, максимальное, минимальное значение;

  • текстовые поля – операции типа количество значений.

hello_html_6605a3fe.png

Рис. 1. Диалоговое окно Промежуточные итоги

К полученным промежуточным итогам можно добавить новые итоги с сохранением предыдущих итогов – для этого в диалоговом окне Промежуточные итоги снять установку Заменить текущие итоги. Если установлено итоги под данными, итоги размещаются под детальными строками таблицы, иначе – над строками таблицы. Чтобы выводить каждую группу строк на отдельном листе, следует установить Конец страницы между группами (рис.1).

Для приведения списка в исходное состояние курсор устанавливается в таблицу, выполняется команда Данные – Структура – Промежуточные итоги и в появившемся диалоге нажимается кнопка Убрать всё (рис.1).

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

Сводная таблица обеспечивает формирование сводной (агрегированной) информации и представление табличных данных в структурированном виде, а также построение связанной со сводной таблицей сводной диаграммы. Источниками данных для формирования сводной таблицы являются:

  • список (база данных) MS Excel;

  • внешний источник данных (текстовый файл, содержащий табличные данные, реляционная таблица);

  • диапазоны ячеек электронной таблицы для консолидации;

  • другая сводная таблица MS Excel.

Построение сводной таблицы осуществляется с помощью команды Вставка – Сводная таблица.

Сводная таблица имеет набор параметров, влияющих на внешнее представление. Команда Сводная таблица - Параметры на вкладке Параметры (рис.2) вызывает диалоговое окно настройки формата сводной таблицы и данных внешнего вида источника.

hello_html_1319b51c.png

Рис. 2. Вызов диалогового окна Параметры сводной таблицы


Консолидация данных

Консолидация – особый способ вычисления итогов диапазона ячеек. Консолидированные данные могут находиться на одном и том же или нескольких листах рабочей книги, а также различных рабочих книг. При консолидации доступны все функции статистических итогов (сумма, максимум, минимум, среднее и т.д.).

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

Различают два вида консолидации:

  • консолидация по расположению ячеек – состав и порядок следования консолидируемых данных во всех диапазонах постоянный;

  • консолидация по категориям – на основании одинаковых подписей строк и столбцов для диапазонов ячеек.

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

При выполнении команды Данные – Консолидация выводится диалоговое окно Консолидация (рис.3), в котором выбирается функция итога, указываются диапазоны консолидируемых ячеек. Для добавления очередного диапазона ячеек нужно нажать «красную» кнопку при этом возможен переход на другие листы книги.

hello_html_m1e361139.png

Рис. 3. Диалоговое окно Консолидация


Практическая часть


Задание 1. Ознакомьтесь с теоретической частью лабораторной работы и основные моменты законспектируйте в тетрадь.


Задание 2. Сформируйте промежуточные итоги по полю Профессия. Вычислите средний тариф, средний разряд, количество сотрудников по профессиям.


Последовательность действий

  1. Откройте вашу рабочую книгу Excel, в которой вы работали на прошлом занятии, и выберите лист Картотека.

  2. Установите курсор в область списка на листе Картотека и отсортируйте список по полю Профессия с помощью команды меню Данные - Сортировка.

  3. Скопируйте лист Картотека.

  4. Установите курсор в область списка на листе Картотека(2) и выполните команду меню Данные – Структура – Промежуточные итоги, укажите:

  • При каждом изменении в – Профессия

  • Операция – Среднее

  • Добавить итоги по – Разряд работающего, Тариф

  • Итоги под данными.

  • Нажать кнопку Ok.

  1. Установите курсор в область списка на листе Картотека (2) и выберите команду меню Данные – Структура – Промежуточные итоги:

  • При каждом изменении в – Профессия

  • Операция – Количество

  • Добавить итоги по – Табельный №

  • Итоги под данными.

  • Нажать кнопку Ok.

На рис. 4 представлен внешний вид таблицы Картотека с промежуточными итогами.

hello_html_m612ce43a.png

Рис. 4. Промежуточные итоги


Задание 3. Постройте сводную таблицу для расчёта месячной зарплат рабочих при повременной форме оплаты труда. Установлена премия по разрядам работающих: 2 разряд 25%, 3 разряд 32%, 4 разряд 50% к тарифу. Вычеты из всех видов начислений составляют 13%.


Последовательность действий

  1. Выберите лист Картотека.

  2. Установите курсор в список на листе Картотека.

  3. Выполните команду меню Вставка – Сводная таблица.

hello_html_ma410582.png

  1. Разместите поля сводной таблицы, так как показано на рис. 5

hello_html_358e926c.png

Рис. 5. Размещение списка полей сводной таблицы

  1. Установите курсор в область сводной таблицы и задайте параметры сводной таблицы с помощью команды Параметры: Параметры - Сводная таблица – Параметры на вкладке Итоги и фильтры уберите указатель Показывать общие итоги для строк, а на вкладке Вывод выберите команду Классический макет сводной таблицы (разрешено перетаскивание полей) (рис.6).

hello_html_758a39c4.png

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

Сводная таблица представлена на рис. 7.

hello_html_48ff29ff.png

Рис. 7. Сводная таблица для списка Картотека

  1. Установите курсор в область сводной таблицы. На вкладке Параметры выберите команду Формулы – Вычисляемое поле. Создайте новое вычисляемое поле (рис. 8):

  • Имя поля – Зарплата, формула: =Тариф*168

  • Кнопка Добавить.

  • Закрыть окно – кнопка Ok.

hello_html_m4dc4c699.png

Рис. 8.

  1. Установите курсор в область сводной таблицы.

  2. Проверьте, чтобы на вкладке Параметры была активна команда Список полей. В диалоговом окне Список полей сводной таблицы уберите из области Значения поле Сумма по полю Тариф.

  3. Дважды щёлкните ЛКМ по полю Сумма по полю Зарплата, таким образом вызвав диалоговое окно Параметры поля значений (рис. 9), в котором измените имя поля на Месячная зарплата и с помощью кнопки Числовой формат выберите формат поля Денежный.

hello_html_72ccc7b7.png

Рис. 9. Окно настройки поля сводной таблицы


  1. Установите курсор в области сводной таблицы на поле Разряд работающего и выполните команду Формулы – Вычисляемый объект (рис. 10).

hello_html_7274aed8.png

Рис. 10. Вставка в сводную таблицу вычисляемого элемента

  • Введите имя поля – Премия;

  • Выберите поле Разряд работающего, раскройте список элементов (двойной щелчок ЛКМ на поле);

  • В окно формулы введите формулу: =’2’*0,25+’3’*0,32+’4’*0,5;

  • Нажмите кнопку Добавить;

  • Введите имя поля – Премия;

  • Выберите поле Разряд работающего, раскройте список элементов (двойной щелчок ЛКМ на поле);

  • В окно формулы введите формулу: =-0,13*(’2’*1,25+’3’*1,32+’4’*1,5);

  • Нажмите кнопку Добавить;

  • Нажмите кнопку OK

На рис. 11 представлен внешний вид таблицы Сводной таблицы с добавленными полями Премия и Вычеты.

hello_html_bd9b704.png

Рис. 11.

  1. Для просмотра выражения вычисляемого поля и вычисляемых объектов выберите команду Формулы – Вывести формулы на вкладке Параметры (рис. 12).

  2. Установите курсор в сводную таблицу.

  3. На вкладке Параметры, с помощью команды Список полей, измените положение полей, как показано на рис. 13.

  4. Установите курсор в сводную таблицу и на вкладке Параметры нажмите кнопку Сводная диаграмма. Измените на своё усмотрение формат области диаграммы (рис. 14).


Задание 4. Отчитайтесь по проделанной работе преподавателю и оформите отчёт по данной лабораторной работе, ответив письменно на контрольные вопросы (см. шаблон отчёта).


hello_html_m5bcb2d86.png

Рис. 12 Лист формул сводной таблицы


hello_html_m65366499.png

Рис. 13. Сводная таблица Зарплата работающих повременщиков


hello_html_5950edb.png

Рис. 14 Диаграмма сводной таблицы зарплата

9




Самые низкие цены на курсы переподготовки

Специально для учителей, воспитателей и других работников системы образования действуют 50% скидки при обучении на курсах профессиональной переподготовки.

После окончания обучения выдаётся диплом о профессиональной переподготовке установленного образца с присвоением квалификации (признаётся при прохождении аттестации по всей России).

Обучение проходит заочно прямо на сайте проекта "Инфоурок", но в дипломе форма обучения не указывается.

Начало обучения ближайшей группы: 27 сентября. Оплата возможна в беспроцентную рассрочку (10% в начале обучения и 90% в конце обучения)!

Подайте заявку на интересующий Вас курс сейчас: https://infourok.ru

Общая информация

Номер материала: ДВ-363669

Похожие материалы

Компьютер - вред или польза?

  • 21.01.2016
  • Просмотры: 1143
2017 год объявлен годом экологии и особо охраняемых природных территорий в Российской Федерации. Министерство образования и науки рекомендует в 2017/2018 учебном году включать в программы воспитания и социализации образовательные события, приуроченные к году экологии.

Учителям 1-11 классов и воспитателям дошкольных ОУ вместе с ребятами рекомендуем принять участие в международном конкурсе «Законы экологии», приуроченном к году экологии. Участники конкурса проверят свои знания правил поведения на природе, узнают интересные факты о животных и растениях, занесённых в Красную книгу России. Все ученики будут награждены красочными наградными материалами, а учителя получат бесплатные свидетельства о подготовке участников и призёров международного конкурса.

Конкурс "Законы экологии"