Тема: Связанные таблицы. Расчет промежуточных итогов в таблицах MS Excel
Цель: Научиться работать в MS Excel со списками: создавать списки, сортировать, осуществлять выборки, подводить итоги, фильтровать данные. Научиться создавать сводные таблицы.
Приобретаемые навыки и умения: Освоить основные приемы работы со сводными таблицами.
Время выполнения: 2 часа.
Форма организации работы: практическая работа.
Правила по технике безопасности:
Категорически запрещается:
- Трогать разъемы соединительных кабелей;
- Прикасаться к питающим проводам и устройствам заземления; включать и выключать аппаратуру без разрешения преподавателя.
- При самопроизвольном отключении аппаратуры или появлении необычного звука немедленно сообщите об этом преподавателю.
- Недопустимо размещать на системном блоке, мониторе и периферийных устройствах посторонние предметы: книги, листы бумаги и т. п. Это приводит к постоянному или временному перекрытию вентиляционных отверстий.
- Запрещается внедрять посторонние предметы в эксплуатационные или вентиляционные отверстия компонентов компьютерной системы.
- Запрещается производить какие- либо операции, связанные с подключением или перемещением компонентов компьютерной системы без предварительного отключения питания.
Оборудование рабочего места:
- ПК.
- Методические рекомендации по выполнению практической работы.
- Программа MS Excel.
- Индивидуальные задания.
КРАТКИЕ ТЕОРЕТИЧЕСКИЕ СВЕДЕНИЯ:
РАБОТА СО СПИСКАМИ:
Список – это содержащаяся в рабочем листе MS Excel таблица, данные в строках которой имеют однородную структуру, т.е. в каждом столбце списка располагаются данные одного типа (число, текст, дата и т.п.). Для наименования ячейки, относящейся к некоторой строке списка и содержащей данные одного типа, используется термин поле. Поля списка могут иметь имена, которые должны располагаться в первой строке таблицы.
К традиционным задачам первичной логической обработки данных относятся сортировка и выборка по заданному критерию (фильтрация).
Создание списка:
1. 
Сформируйте заглавную строку. В каждом столбце этой строки введите название соответствующего поля записи.
2. Щелкните на любой из ячеек заглавной строки и выполните команду Данные – Форма. На экране появится диалоговое окно с полями, соответствующими заголовкам столбцов.
3. Вводите данные в каждую графу диалогового окна. Для ввода следующей строки с данными щелкните на кнопке Добавить.
4. Можно также построить список, вводя данные непосредственно в соответствующую ячейку списка.
5. 
Поиск записей в списке можно осуществлять с помощью кнопки Критерии. В открывшемся диалоговом окне вводят критерии поиска. Для поиска группы записей можно использовать знаки =,<,> и т.п.таблица, данные в строках которой имеют однородную структуру, т.е. рмацией: сортировать, осуществлять выборки, подводить итогиПоП
Сортировка списков:
1. Следует выделить любую ячейку в списке и выполнить команду Данные - Сортировка.
2. В открывшемся диалоговом окне выбрать столбец и порядок сортировки.
3. Если необходимо задать вторичную сортировку, задаем ее в поле Затем по и т.д.
4. 
Более детальную сортировку задают в диалоговом окне, вызываемом кнопкой Параметры.
Фильтрация данных:
1. Для просмотра данных, удовлетворяющих определенным условиям, можно использовать команду Данные – Фильтр - Автофильтр. При этом в заглавной строке каждого столбца появляются маленькие кнопки-стрелки. Щелчок на стрелке приводит к появлению списка, содержащего несколько команд, а также перечень всех элементов этого столбца, которые необходимо выбрать для просмотра:
Ø Первые 10 – для отображения наибольших или наименьших значений в списке;
Ø Условие – можно более конкретизировать условия фильтрации в открывшемся окне Пользовательский автофильтр.
2. Расширенный фильтр позволяет произвести отбор строк списка по сложным критериям, например таким, которые содержат вычисляемые выражения, а также для размещения результатов фильтрации в произвольном месте рабочего листа.
Подведение итогов:
1. Следует начать с сортировки по полю или полям, для которых требуется получить итоговые значения.
2. Далее выполняется команда Данные – Итоги, в появившемся диалоговом окне Промежуточные итоги в списке поля При каждом изменении в выбрать необходимое поле, по которому будут подводиться итоги.
В поле Операция можно получить список функций, которые можно применить для обработки итоговых значений. В списке Добавить итоги по нужно выбрать тот элемент, по которому должен быть подведен итог. Каждый промежуточный итог озаглавливается автоматически.
СВОДНЫЕ ТАБЛИЦЫ:
Сводная таблица – это таблица, которая используется для быстрого подведения итогов или объединения больших объемов данных. Меняя местами строки и столбцы, можно создать новые итоги исходных данных, отображая разные страницы, можно осуществить фильтрацию данных, а также отобразить детальные данные области. Создание сводных таблиц осуществляется с помощью Мастера сводных таблиц и диаграмм:
1. Перед построением сводной таблицы необходимо убрать все ранее созданные промежуточные итоги и наложенные фильтры.
2. Следует проанализировать, какую информацию необходимо извлечь из рассматриваемого списка.
3. 
Нужно установить курсор на одну из ячеек списка или выделить весь требуемый диапазон и выполнить команду Данные – Сводная таблица. В появившемся диалоговом окне выбираем источник данных и вид создаваемого отчета (сводная таблица иди сводная диаграмма):
4. В следующем окне указываем диапазон, содержащий исходные данные:
5. На третьем шаге мы определяем месторасположение сводной таблицы (на существующем листе в заданном диапазоне или на новом листе).
Здесь же с помощью кнопки Макет можно задать структуру сводной таблицы путем перетаскивания заданных справа полей выбранного для построения сводной таблицы диапазона в нужную область сводной таблицы:
Кнопка Параметры позволяет задать формат вычислений.
Полученная сводная таблица: (кнопки со стрелкой позволяют производить фильтрацию данных прямо в сводной таблице)
Построение сводной диаграммы:
Если на первом шаге в качестве вида создаваемого отчета выбрать сводную диаграмму, то последовательность действий будет та же, но вместо макета таблицы будет выведен макет диаграмм. Данные по осям и легенда будут задаваться тем же способом перетаскивания требуемого поля в требуемую область (самостоятельно постройте приведенную ниже сводную диаграмму):
Панель инструментов Сводные таблицы позволяет выполнить некоторые действия над сводной таблицей, например:
Ø Формат отчета – изменить внешний вид сводной таблицы;
Ø Сводная диаграмма – построить сводную диаграмму на основе сводной таблицы;
Ø Мастер сводных таблиц – запустить Мастер сводных таблиц для внесения изменений в уже созданную таблицу;
Ø Обновить данные – обновить сводную таблицу, если в исходную были внесены изменения (если было добавление строк, то только внутри диапазона) и т.п.
ЗАДАНИЕ:
1. Создайте следующую таблицу:
| Город | Дата отгрузки | Количество товара, шт. | Цена единицы товара, тыс.руб | Стоимость товара, тыс.руб |
| Минск | 12.01.2007 | 234 | 20 | |
| Минск | 12.01.2007 | 181 | 22 | |
| Гомель | 13.01.2007 | 240 | 24 | |
| Минск | 13.01.2007 | 200 | 23 | |
| Брест | 15.01.2007 | 300 | 22 | |
| Гомель | 15.01.2007 | 230 | 23 | |
| Брест | 17.01.2007 | 350 | 20 | |
| Гомель | 17.01.2007 | 250 | 20 | |
| Брест | 17.01.2007 | 340 | 23 |
2. С помощью Формы добавьте в таблицу две записи.
3. Проведите сортировку (результаты каждой сортировки скопируйте на новое место):
Ø по городу;
Ø по городу и дате отгрузки;
Ø по городу, дате отгрузки и количеству товара.
4. С помощью Пользовательского автофильтра выполните поиск товара, отгруженного в Гомель не позднее 15.01.2007.
5. С помощью Расширенного фильтра выполните поиск товаров, отгруженных в Минск в количестве не менее 200.
6. Подведите стоимостные итоги по одинаковым городам.
7. Постройте сводную таблицу, отображающую информацию о стоимостях партий товара, поставляемых в разные города, за различные периоды времени. Просчитайте суммарные значения стоимостей по датам и отдельным городам.
8. Постройте сводную диаграмму, отображающую информацию о стоимостях партий товара, поставляемых в разные города, за различные периоды времени.
ВОПРОСЫДЛЯ САМОКОНТРОЛЯ:
1. Какими способами можно создать список в MSExcel?
2. Как осуществляется поиск записи в списке через Форму?
3. Как упорядочить данные в списке? Как восстановить первоначальный список?
4. Какие способы выполнения фильтрации данных позволяют разместить результаты отбора на другом месте?
5. Как произвести фильтрацию данных в сводных таблицах?
6. Как создать сводную таблицу так, чтобы итоги подводились только по столбцам?
7. На основе чего можно построить сводную диаграмму?
8. Как можно обновить данные в сводной таблице, если в исходной таблице добавлены новые записи в середину диапазона?
Л.В. Гончарова, преподаватель УО «Краснобережский государственный аграрный колледж»