[19 мая!] Практическая онлайн-конференция «Компетенции XXI века» Подтвердить участие→
Конкурс разработок «Пять с плюсом» май 2021
Добавляйте свои материалы в библиотеку и получайте ценные подарки
Конкурс проводится с 1 мая по 31 мая

Практическое занятие № 19

Раздел 3. Прикладные программные средства. Тема 3.3. Электронные таблицы. Тема занятия: Структурирование, консолидация данных, построение сводных таблиц и диаграмм. Продолжительность работы 2 часа Цель: совместная обработка нескольких таблиц-списков, расположенных на разных листах рабочей книги. Задачи: 1. Формировать умение создавать диаграммы разного типа в программе MS Excel. 2. Формирование и развития у обучающихся познавательных способностей; развитие интереса к предмету; развитие умения оперировать ранее полученными знаниями; развитие умения планировать свою деятельность. 3. Воспитание умения самостоятельно мыслить, ответственности за выполняемую работу, аккуратности при выполнении работы, воспитание культуры общения и поведения.
Просмотр
содержимого документа

 

 

 

 

Теоретическое обоснование работы:

 

Список – это упорядоченный набор данных, база данных на рабочем листе.

Столбцы списка называются полями, строки – записями.

Ведение списка можно осуществлять в диалоговом окне (форме).

Над списками можно выполнять такие операции, как фильтрация и сортировка.

В процессе сортировки списка строки переупорядочиваются в соответствии с видом сортировки (по возрастанию или убыванию)

Фильтрация – это быстрый способ выделения подмножества данных списка для последующей работы с ним. В результате фильтрации списка на экран выводятся только те строки, которые содержат определенные значения, либо те, которые удовлетворяют некоторому набору условий поиска (критерию).

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

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

Консолидация – это объединение данных из одной или нескольких областей данных и вывод их в виде таблицы в итоговом листе. В Excel предусмотрено несколько способов консолидации данных. После создания итоговой таблицы с помощью команды Консолидация можно добавлять, удалять или изменять исходные области данных. Кроме этого, можно создать связи итоговой таблицы с исходными данными, с тем чтобы данные области назначения автоматически обновлялись при изменении данных в исходных областях. Основным фактором, влияющим на успешность консолидации, является способ размещения информации в рабочих листах. Если эти способы во всех рабочих листах одни и те же, в этом случае задача консолидации становится достаточно простой.

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

 

 

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

Задание.

1. Загрузите программу MS Excel 2013.

2. На листе рабочей книги (Лист1) создайте табл.1 с исходными данными приведенными ниже.

Таблица 1

https://studfile.net/html/2706/252/html_Oz5ycuooAr.gpDy/img-DoAvBR.jpg

3. Рассчитайте цену одного экземпляра по каждому наименованию книжной продукции путем ввода следующей формулы:

= F2/E2

4. Переименуйте Лист1 в Заказ. Для этого установите указатель на ярлык Лист1, нажмите правую кнопку мыши, в контекстном меню выберите команду Переименовать и вместо прежнего имени листа Лист1 введите новое имя Заказ.

5. Получите итоговую сумму по столбцу Сумма. Выполните команду Главная/Редактирование/Автосуммирование(∑)/Сумма.

 Примечание. В дальнейшем подобная информация будет выводиться в виде: выполните команду

Главная/Редактирование/Автосуммирование(∑)/Сумма

6. Создайте структуру построенной таблицы для скрытия детальных числовых данных. Для этого выделите столбцы с числовыми значениями и выполните следующие действия: Данные/Структура/Группировать/по столбцам. На экране структуры таблицы щелкните кнопку «», чтобы скрыть столбцы с числами, а затем кнопку «+» для показа скрытой информации:

https://studfile.net/html/2706/252/html_Oz5ycuooAr.gpDy/img-LQFS9y.jpg

Пример структуры для скрытия детальных числовых данных

7. Удалите структуру, выделив, ячейки с числовыми значениями и выполнив команду Данные/Разгруппировать/столбцы.

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

9. Переименуйте Лист2, Лист3, Лист4 в Январь, Февраль, Март, так как они будут содержать информацию о реализации продукции за первые три месяца 2011 года (табл. 2,3,4).

10. Сгруппируйте листы Январь, Февраль, Март для ввода общей для них информации (названия столбцов и наименование товара). Группа листов создается щелчком мышью на ярлыке листа при нажатой клавише CTRL.

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

Таблица 2

https://studfile.net/html/2706/252/html_Oz5ycuooAr.gpDy/img-j5NOWU.jpg

Таблица 3

https://studfile.net/html/2706/252/html_Oz5ycuooAr.gpDy/img-Jy6tKp.jpg

Таблица 4

https://studfile.net/html/2706/252/html_Oz5ycuooAr.gpDy/img-SOPq3G.jpg

12. Введите и размножьте формулы для подсчета стоимости и итоговых сумм во введенные таблицы. Для нахождения цены каждой продукции в таблице на листе Заказ используйте функцию ВПР:

=ВПР(Март!A3;Заказ!$B$2:$F$11;3;ложь)*B3

(для таблицы 4)

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

  • Добавьте новый лист переименуйте его в Консолидация.
  • Выделите ячейку на новом листе Консолидация, начиная с которой будут размещены итоговые данные (например, A1);
  • Выполните: команду Консолидация вкладки Данные группы/ Работа с данными.
  • в диалоговом окне Консолидация выберите в списке функций функцию Сумма;
  • в строку Ссылка введите абсолютную ссылку на консолидируемые данные (например, Январь!$A$2:$C$12) и нажмите кнопку Добавить;
  • повторите ввод и добавление данных для ввода всей консолидируемой информации (Март!$A$2:$C$12 и Февраль!$A$2:$C$12);
  • включите флажок значения левого столбца;
  • нажмите кнопку OK.

Таблица 5

https://studfile.net/html/2706/252/html_Oz5ycuooAr.gpDy/img-lBIXiV.jpg

14. Измените некоторые данные в одном из консолидируемых листов. Изменятся ли данные в итоговой таблице?

15. Установите связанную консолидацию данных. Для этого вставьте новый рабочий лист, переименуйте его в Консолидация 1, активизируйте ячейку начала формирования итоговой таблицы (например, A1), выполните все положения пункта 13, добавив флажок Создавать связи с исходными данными.

16. В полученной структурированной таблице просмотрите скрытые данные, нажав кнопки «2» или «+».

17. Измените некоторые данные в одном из консолидируемых листов. Изменятся ли данные в итоговой таблице на этот раз?

18. Постройте сводную таблицу, информирующую о сумме изданной литературы по каждому наименованию отдельно. Для этого:

  • активизируйте рабочий лист Заказ;
  • вызовите мастер сводных таблиц и диаграмм, выполнив команду Вставка/Таблицы/Сводная таблица;
  • в окне Создание сводной таблицы выберите источник, введите диапазон исходных данных для построения сводной таблицы, например, Заказ!$A$1:$G$11, установите переключатель Новый лист и нажмите кнопку ОК;
  • постройте сводную таблицу, перетащив в окне Список полей сводной таблицы поле Название в область полей строк, поле Квартал – в область полей столбцов, а поле Сумма – в область значений.

https://studfile.net/html/2706/252/html_Oz5ycuooAr.gpDy/img-WOhJZd.png

Пример сводной таблицы со списком полей

19. Измените исходные данные (сначала уберите, а затем добавьте одну строку в исходную таблицу) при этом проверьте обновления таблицы, выполнив команду Обновить все вкладки Данные группы Подключения (также можно выполнить аналогичные действия, используя вкладку Параметры группы Данные).

Примечание. При работе со сводными таблицами на ленте появляется дополнительная вкладка Работа со сводными таблицами – Параметры, Конструктор.

20. Постройте сводную диаграмму (разрезанная круговая) на основе сводной таблицы, выполнив команду Работа со сводными таблицами/ Параметры/Сервис/Сводная диаграмма.

https://studfile.net/html/2706/252/html_Oz5ycuooAr.gpDy/img-MHtQxO.jpg

Пример разрезанной круговой диаграммы.

 

 

 

 

 

 

 

 

 

 

 

По окончании практической работы студент должен:

  1. Написать отчет, который должен содержать (см. Приложение 1):
  • Тема занятия.
  • Цель работы.
  • Задание и его решение.
  • Ответы на контрольные вопросы.

 

 

Критерии оценки практической работы:

«5» (отлично) – Задания 1-2 + Контрольные вопросы

«4» (хорошо) – Задание 1+ Контрольные вопросы

«3» (удовлетворительно) – 1 задание или контрольные вопросы

«1» (плохо) – работа не сделана или не сдана

 

 

Вопросы для самоконтроля:

1. Как отобразить числа с символом денежной единицы, процента?

2. Какие способы автозаполнения таблицы вы знаете?

3. Как выполняется копирование формулы, распространение на соседние ячейки (автозаполнение) формулы?

4. Как разграничить таблицу?

5. Как задать цвет ячейкам и цвет текста?

 

 

Список литературы:

 

Основной:

  1. Михеева Е.В. Практикум по информатике: учеб. пособие для сред. Проф. Образования/Е.В. Михеева. -3-е изд., стер. - М.: Издательский центр «Академия», 2014. -192 с.

 

Дополнительной:

  1. Филимонова Е.В. Информационные технологии в профессиональной деятельности: Учебник. – Ростов н/Д: Феникс, 2004. – 352с. (серия «СПО».)

 

Интернет-источники:

  1. https://drive.google.com/file/d/1RgKVzpgf-fTfuGiTML_S0M7KhVM3Le57/view

 

 

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

Приложение 1

 

Практическая работа №17

 

Тема занятия: Структурирование, консолидация данных, построение сводных таблиц и диаграмм.

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

 

Контрольные вопросы:

1……

2……

3…….

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Информация о публикации
Загружено: 15 апреля
Просмотров: 49
Скачиваний: 1
Здобнова Екатерина Олеговна
Информатика, СУЗ, Уроки

Проверьте знания своих учеников интересными заданиями

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

Скачать материал