Microsoft Excel. Работа 2 Работа со списками данных. Цель занятия
 Скачать 1.34 Mb.
|
Букунов С.В. Кафедра прикладной математики и информатики Microsoft Excel . Работа № 2 - Работа со списками данных. 1. Цель занятия: Изучить основные возможности MS Excel по сортировке, фильтрации, группировке, отбору данных и созданию сводных таблиц. Научиться использовать команды (инструменты) фильтрации и сортировки данных в электронных таблицах. 2. Теоретические сведения Данные в электронных таблицах Excel могут быть организованы в виде списков, что дает пользователям дополнительные возможности по обработке и анализу данных. Списки Список – это ряд строк, в которых содержится определенный набор данных. Преимущество списка заключается в возможности гибко им манипулировать: искать данные, удовлетворяющие определенным условиям, отбрасывая при этом все остальные. Со списками связан ряд терминов, которыми неизбежно придется пользоваться. Сами списки часто называют базами данных. Все данные об одном элементе списка называются записью. Полем называется категория данных. Обычно каждая запись представляет собой одну строку данных, а каждое поле один столбец. Заголовки столбцов часто называют именами полей. Рекомендуется создавать не более одного списка на одном листе в книге. Необходимо также оставлять как минимум один пустой столбец и одну пустую строку между списком и другими данными в рабочей таблице, чтобы программа могла автоматически определять границы списка. Форма данных Форма данных (Data Form) – специальное средство программы Excel для просмотра, ввода, редактирования, удаления или поиска информации в списке. Каждая форма создается для конкретного списка данных. 3. Задание 3.1. Во вновь созданном документе Excel создайте 11 листов с названиями: «Содержание», «Страны», «Группировка», «Автофильтр», «Пользовательский фильтр_1», «Пользовательский фильтр_2», «Удаление», «Сортировка_1», «Сортировка_2», «Расчет» и «Свод_1». 3.2. На лист «Содержание» скопируйте свой вариант задания, а также включите в него свои персональные данные (фамилия, имя, номер группы). Букунов С.В. Кафедра прикладной математики и информатики 3.3. Сгруппируйте все листы кроме листа «Содержание». 3.4. На листе «Страны» создайте список стран в соответствии со своим вариантом задания (см. рис. 1). При создании списка используйте при необходимости специальные средства Microsoft Excel, такие как Автозаполнение и Списки ввода. Рис. 1. Начальный вид списка. 3.4. Разгруппируйте листы. 3.5. В набранном списке на листе «Страны» при помощи такого инструмента Excel, как Форма данных, добавьте информацию еще по двум странам: Канада (население – 22,9, площадь – 9984, столица – Оттава, денежная единица – канадский доллар, континент – Сев. Америка); Австралия (население – 23,7, площадь – 7659, столица – Канберра, денежная единица – австралийский доллар, континент – Австралия). 3.6. С помощью операции копирования добавьте информацию по двум новым странам в каждую таблицу на всех остальных листах. В результате выполнения п.п.3.1 ÷ 3.5 на всех листах, кроме первого, должна содержаться одна и та же таблица. 3.7. В таблице на листе «Группировка» сгруппируйте страны с одинаковой денежной единицей. Сгруппированная таблица должна иметь следующий вид. Рис. 2. Список стран, сгруппированный по денежным единицам. Букунов С.В. Кафедра прикладной математики и информатики 3.8. В таблице на листе «Автофильтр» при помощи автофильтра выберите страны, денежной единицей которых является евро. В результате эта таблица должна иметь следующий вид. Рис. 3. Список стран, денежной единицей которых является евро. 3.9. В таблице на листе «Пользовательский фильтр_1» создайте пользовательский автофильтр, с помощью которого выберите страны, в названии денежной единицы которых присутствует слово «доллар». В результате эта таблица должна иметь следующий вид. Рис. 4. Список стран, в названии денежной единицы которых есть слово «доллар». 3.10. В таблице на листе «Пользовательский фильтр_2» создайтееще один пользовательский автофильтр, с помощью которого выберите страны, население которых составляет от 10 до 50 млн. человек и площадь занимаемой территории не превышает 8000 тыс. кв. км. В результате эта таблица должна иметь следующий вид. 3.11. При помощи Формы данных на листе «Удаление» выберите для просмотра страны, расположенные в Европе, название которых заканчивается на «ия» . Букунов С.В. Кафедра прикладной математики и информатики Для этого выберите пункт Критерии, введите заданные критерии и нажмите на кнопку Далее. Удалите из таблицы все записи, соответствующие заданному критерию. После этого закройте Форму данных. 3.12. На листе «Удаление» из оставшихся записей с помощью Пользовательского фильтра выберите страны, площадь территории которых превышает 5000 тыс. кв. км. Удалите выбранные записи из таблицы. Снимите установленный фильтр. Результирующая таблица должна иметь следующий вид. 3.13. На листе «Сортировка_1» выполните сортировку списка стран мира, сначала расположив их по алфавиту, Букунов С.В. Кафедра прикладной математики и информатики а затем по величине занимаемой территории. 3.14. На листе «Сортировка_2» выполните сортировку списка стран мира одновременно по трем полям: «Континент», «Страна» (от «А» до «Я») и «Площадь» (по убыванию). 3.15. На листе «Расчет» выполните расчет промежуточных и итоговых сумм по количеству стран, принадлежащих тому или иному континенту. Букунов С.В. Кафедра прикладной математики и информатики Оставьте в созданной таблице только итоговые данные. Для этого скройте данные по отдельным странам, нажав на значок возле каждого диапазона стран. 3.16. На листе «Свод_1» рядом с исходной таблицей создайте сводную таблицу, показывающую количество стран на определенном континенте. 3.17. Создайте еще одну сводную таблицу, в которую помимо количества стран добавьте максимальное население страны и среднюю площадь всех стран на каждом континенте. Расположите таблицу на новом листе и дайте ему имя «Свод_2». Букунов С.В. Кафедра прикладной математики и информатики 3.18. Создайте еще одну сводную таблицу, содержащую сгруппированные данные о численности населения в странах каждого континента. Расположите таблицу на новом листе и дайте ему имя «Свод_3». 4. Основные сведения по выполнению работы 4.1. Группировка листов Для группировки непрерывного диапазона листов необходимо выделить первый лист диапазона, затем нажать на клавишу Shift и, удерживая ее, выделить последний лист диапазона. Для группировки листов в произвольном порядке, необходимо щелкать по вкладкам группируемых листов, удерживая нажатой клавишу Ctrl. После группирования листов вся информация, вводимая на каком-нибудь одном листе, будет дублироваться на всех листах, включенных в группировку. 4.2. Разгруппировка листов Р азгруппировать листы можно двумя способами: выделить любой из листов, входящих в группировку, нажать правую кнопку мыши, после чего в выпадающем контекстном меню выбрать пункт Разгруппировать листы; щелкнуть по вкладке любого листа, не входящего в группировку. Букунов С.В. Кафедра прикладной математики и информатики 4.3. Создание списка Для создания списка достаточно ввести данные в ячейки рабочего листа. Записи можно вводить в любом порядке. В программе Excel имеется набор функций, облегчающих обработку и анализ данных в списке. Чтобы использовать эти функции, необходимо следовать следующим рекомендациям: 1) Размер и расположение списка. На листе не следует помещать более одного списка. Между списком и другими данными листа необходимо оставить, по меньшей мере, одну пустую строку и один пустой столбец. В самом списке не должно быть пустых строк и столбцов. Важные данные не следует помещать у левого (правого) края списка. 2) Заголовки столбцов. Заголовки столбцов должны находиться в первой (начальной) строке списка. Атрибуты форматирования заголовков полей должны отличаться от формата, присвоенного строкам данных. 3) Содержание строк и столбцов. Список должен быть организован так, чтобы во всех строках в одинаковых столбцах находились однотипные данные. Перед данными в ячейке не следует вводить лишние пробелы. 4.4. Создание Формы данных Для создания и работы с формой данных используется кнопка Форма, расположенная на панели быстрого доступа. Если эта кнопка отсутствует на панели быстрого доступа, то необходимо изменить настройки самой панели. Для этого: щелкните на стрелку рядом с панелью быстрого доступа и выберите пункт Другие команды; в поле Выбрать команды из выберите пункт Все команды и в списке команд найдите кнопку Форма ; нажмите кнопку Добавить, а затем – ОК. Диалоговое окно Формы данных содержит текстовые поля, названия которых соответствуют названиям полей списка, и набор командных кнопок: Букунов С.В. Кафедра прикладной математики и информатики - ввода и удаления записей: Добавить, Удалить, - отмены предыдущей команды: Вернуть, - движения по списку: Назад, Далее, - поиска определенных записей: Критерии, - завершения работы с формой: Закрыть. Форма данных может включать все или часть полей списка. Максимальное число полей в отдельной форме – 32. 4.5. Ввод записей с помощью Формы данных Форма данных – удобное и наглядное средство для ввода новых записей в список. Программа Excel автоматически помещает данные в соответствующие ячейки рабочего листа при переходе к другой записи списка или закрытии формы. Для ввода первой записи в пустой список необходимо выделить ячейку, содержащую заголовок одного из полей списка и щелкнуть по кнопке Форма на панели быстрого доступа. При добавлении новых записей программа Excel помещает их в конец списка. Замечание: При добавлении записи, содержащей формулу, результат формулы не будет вычислен до нажатия клавиши ENTER или кнопки Закрыть. Совет 1: Для быстрого перехода к следующему полю новой записи используйте клавишу TAB, а для перемещения к предыдущему полю используйте сочетание клавиш SHIFT+TAB. Совет 2: Чтобы отменить добавление записи, щелкните по кнопке Вернуть перед нажатием клавиши ENTER или кнопки Закрыть. После щелчка по кнопке Закрыть или нажатия клавиши Enter отменить ввод записи нельзя. Букунов С.В. Кафедра прикладной математики и информатики 4.6. Ввод данных в режиме Автозаполнения Для ускорения ввода данных в ячейки списка программа Excel включает режим Автозаполнение. Режим Автозаполнение основан на использовании значений аналогичных полей в записях, ранее введенных в список. При вводе первых символов в поле автоматически подставляется подходящее значение, из числа введенных ранее. Добавленные символы отмечаются в поле ячейки синим фоном. Совет: Если вас устраивает значение подставленное инструментом Автозаполнение в ячейку, нажмите клавишу TAB или Enter для перехода к вводу следующего поля. Если необходимо ввести другое значение, просто продолжайте ввод поля. Замечание: Режим Автозаполнение включается автоматически при вводе данных в поле списка, если установлен флажок опции Автозавершение значений ячеек в разделе Параметры правки вкладки Дополнительно команды Параметры в меню Файл. 4.7. Ввод данных с помощью Списков ввода Список ввода, наряду с режимом Автозаполнение, относится к числу инструментов программы Excel, облегчающих операции ввода больших объемов данных в списки, в которых присутствуют повторяющиеся данные. Для вызова списка ввода необходимо щелкнуть правой кнопкой мыши по ячейке, в которую вводится новое значение, и выбрать строку Выбрать из раскрывающегося списка. В появившемся окне списка будут перечислены все ранее введенные значения для выбранного поля, из числа которых можно выбрать нужное значение. Букунов С.В. Кафедра прикладной математики и информатики Замечание: Список ввода и режим Автозаполнение используют только значения ранее введенных записей для выбранного списка данных. Поэтому для их применения необходимо ввести в список хотя бы одну запись. Совет: Если число вариантов значений отдельного поля списка ограничено, вы можете создать список допустимых вводимых значений, используя опции вкладки Параметры команды Проверка данных в меню Данные. 4.8. Группировка данных в списке Для того, чтобы сгруппировать данные в списке, необходимо выделить нужные строки или столбцы, после чего выбрать пункт Группироватьменю Данные. Для того, чтобы скрыть сгруппированные данные необходимо нажать на значок рядом с текущей строкой или столбцом. После этого сгруппированные данные окажутся скрытыми, а на его месте рядом с текущей строкой или столбцом появится знак . Для того, чтобы просмотреть все сгруппированные данные, необходимо щелкнуть по этому значку, в результате список сгруппированных данных снова раскроется. Букунов С.В. Кафедра прикладной математики и информатики 4.9. Разгруппировка данных в списке Для того, чтобы разгруппировать данные в списке, необходимо выделить нужные строки или столбцы, после чего выбрать пункт Разгруппировать меню Данные. 4.10. Просмотр (отбор) записей с помощью Формы данных Форма данных – удобное средство поиска и просмотра записей списка. В отдельный момент времени в форме данных отображается одна запись списка, называемая текущей записью. Для перехода к следующей или предыдущей записи списка используются кнопки Далее и Назад (не полосы прокрутки). По умолчанию в форме данных отображаются все записи списка. Используя опцию Критерии можно задать значения одного или нескольких полей, в этом случае при движении по списку отображаются только те строки, содержимое которых начинается с указанных данных. Например, если в качестве критерия поиска ввести буквосочетание Бо, то при последовательном нажатии на клавишу Далее будут найдены строки «Боливия» и «Болгария». Примечание. Для того, чтобы найти текст, который содержит только определенные символы, в критериях поиска можно использовать подстановочные знаки. ПОДСТАНОВОЧНЫЙ ЗНАК РЕЗУЛЬТАТ ПОИСКА ? (вопросительный знак) Любой символ. Например, по условию «с?н» будут найдены слова «сон» и «сын». * (звездочка) Любое количество символов. Например, по условию «*-восток» будут найдены слова «северо- восток» и «юго-восток». (тильда) перед знаками ?, * Вопросительный знак, звездочка или тильда. Букунов С.В. Кафедра прикладной математики и информатики или Например, по условию «91?» будет найден текст «91?». Совет. Для отказа от режима просмотра записей по заданным критериям, щелкните по кнопке Критерии в диалоговом окне Формы данных и очистите введенные ранее значения полей. 4.11. Поиск и просмотр записей с использованием Автофильтра Автофильтр – это средство быстрого выделения из списка требуемого набора записей. Для подключения Автофильтра к списку данных используется подменю Фильтр в меню Данные. При включении режима Автофильтр в выбранном списке справа от заголовков столбцов отображаются маленькие кнопки со стрелками. Критерий фильтрации Критерий фильтрации – это значение одного поля записи списка, которое должно присутствовать у всех выводимых на экран записей. Значения критериев фильтрации можно установить по одному или нескольким столбцам спискам. Номера заголовков всех отфильтрованных строк и столбцов, для которых были заданы критерии фильтрации, отображаются голубым цветом. Замечание. После задания значений фильтрации на экране отображаются только записи списка, соответствующие выбранному фильтру. Значения остальных записей остаются неизменными и доступными для всех ячеек рабочего листа. Их всегда можно отобразить вновь, отменив режим фильтрации. Совет 1. Если число отфильтрованных записей велико, при задании критериев Автофильтра выберите команду Первые 10. В этом случае на экране будут отображаться только первые 10 записей списка, удовлетворяющие выбранным критериям. Совет 2. Для отключения Автофильтра выберите еще раз подменю Фильтр меню Данные. 4.12. Поиск и просмотр записей по нескольким условиям В режиме Автофильтр программа Excel позволяет проводить поиск не только по совпадению значений одного или нескольких полей записи заданным критериям. При необходимости можно формировать поиск по нескольким логическим условиям. Этот режим получил название – Пользовательский автофильтр. При задании критерия для числовых данных используются логические операторы: больше, меньше, равно, не равно, меньше или равно, больше или равно. Необходимые критерии выбора можно выбрать в контекстном меню пункта Числовые фильтры после выбора соответствующего фильтра. Букунов С.В. Кафедра прикладной математики и информатики Необходимые критерии выбора для текстовых данных можно выбрать в контекстном меню пункта Текстовые фильтры после выбора соответствующего фильтра. Критерии можно объединять в одно условие с помощью операторов И или ИЛИ. При отборе записей для выполнения условия ИЛИ необходимо, чтобы выполнялся хотя бы один критерий. При задании условия И для отбираемых записей должны выполняться все критерии. Совет. После того, как список отфильтрован, вы можете скопировать отобранные записи в другой рабочий лист или книгу. Выделите записи, выберите команду Копировать в меню Правка, переместите курсор в первую ячейку области, куда копируется блок, и нажмите Enter. Букунов С.В. Кафедра прикладной математики и информатики 4.13. Удаление записи с предварительным уменьшением списка Для удаления отдельной записи из небольшого списка можно выделить группу ячеек и воспользоваться командой Удалить в меню Правка. Если список содержит десятки или сотни записей, то для удаления одной или группы записей списка удобнее всего использовать Форму данных или Автофильтр. Задав критерии поиска, можно отобразить в Форме данных или на экране только нужные записи списка и удалить их, используя кнопку Удалить в диалоговом окне Формы данных. Предупреждение. Удаление записи из списка с помощью Формы данных отменить нельзя. 4.14. Сортировка записей в списке по выбранному полю Записи в списке данных можно сортировать по значениям ячеек одного или нескольких полей. Строки, столбцы или отдельные ячейки в процессе сортировки переупорядочиваются в соответствии с заданным пользователем порядком сортировки. Списки можно сортировать в возрастающем (от 1 до 9, от А до Я) или убывающем (от 9 до 1, от Я до А) порядке. Для сортировки записей списка по выбранному полю используются кнопки Сортировка по возрастанию и Сортировка по убыванию пункта меню Данные. 4.15. Сортировка записей в списке по нескольким полям Для сортировки записей списков по нескольким полям используется команда Сортировка в меню Данные. При этом можно указать до трех полей, по которым осуществляется сортировка: Сортировать по, Затем по и В последнюю очередь, по. Примечание. Если сортировка ведется по нескольким столбцам, то строки с одинаковыми значениями в столбце, указанном в поле Сортировать по, сортируются в порядке, определяемом столбцом, указанным в поле Затем по. Строки с одинаковыми значениями в первых двух столбцах сортируются по столбцу, указанному в поле В последнюю очередь, по. Совет: Для сортировки столбцов (полей) списка по содержимому строк, выберите команду Сортировка в меню Данные, нажмите кнопку Параметры, установите переключатель Сортировать в положение Столбцы диапазона и нажмите кнопку OK. Выберите строки, по которым требуется отсортировать столбцы, в полях Сортировать по и Затем по. 4.16. Расчет промежуточных и итоговых сумм Обычный способ подсчета итоговых строк в программе Excel – использование функции Автосуммирования. В программе Excel существует другая возможность автоматического расчета промежуточных и итоговых сумм для структурированного списка данных. Для этого используется команда Промежуточный итог в меню Данные. Букунов С.В. Кафедра прикладной математики и информатики По умолчанию для расчета используется функция Суммирования. Однако пользователь может сам выбрать одну или несколько функций для расчета итогов. Значения общих и промежуточных итогов пересчитываются автоматически при каждом изменении данных в списке. Совет. Для группирования данных при расчете промежуточных итогов по умолчанию используется первая колонка списка. При необходимости измените значение поля При каждом изменении в диалогового окна Промежуточные итоги, отображаемого при выборе команды Данные → Промежуточный итог. Примечание. При автоматическом добавлении промежуточных и итоговых сумм в список, на экране слева отображается его структура. Эта структура описывает группировку данных в списке для вычисления промежуточных итоговых сумм. Сформированную структуру можно удалить – эта операция не приведет к удалению итоговых строк. 4.17. Создание сводной таблицы списка данных В программе Excel предусмотрена возможность создания сводной таблицы для обобщения и анализа данных из списка. Для этого используется Мастер сводных таблиц, вызываемый командой Сводная таблица меню Вставка. Мастер сводных таблиц достаточно хорошо обрабатывает однородные таблицы, т.е. таблицы, в которых нет строк с промежуточными итогами. Процедура построения сводной таблицы с использованием Мастера сводных таблиц включает следующие этапы: 1) Выбор источника данных: список, внешний источник, другая сводная таблица, выделенные диапазоны; 2) Задание диапазона ячеек, содержащих исходные данные; Букунов С.В. Кафедра прикладной математики и информатики 3) Задание параметров макета сводной таблицы; 4) Определение места размещения сводной таблицы: новый или существующий лист. Для создания сводной таблицы необходимо: щелкнуть указателем мыши на ячейку А1 (тем самым выбрав всю таблицу), чтобы Excel понял с какими данными нужно работать; выбрать команду Вставка → Сводная таблица; в открывшемся диалоговом окне указать параметры сводной таблицы, а именно: диапазон данных для анализа (в данном случае Excel сам выделит исходную таблицу, т.к. мы поставили фокус на ячейке А1) и местоположение сводной таблицы (для расположения на текущем листе нужно выбрать флаг На существующий лист и перевести указатель мыши в ту ячейку, где должна быть расположена сводная таблица, для расположения сводной таблицы на новом листе нужно выбрать флаг На новый лист). После нажатия на кнопку ОК на листе появится форма сводной таблицы и список ее полей. В верхней части диалогового окна Список полей сводной таблицы содержатся все поля данных из исходного списка. В нижней его части располагаются области для задания столбцов, строк и данных для создаваемой сводной таблицы. Букунов С.В. Кафедра прикладной математики и информатики Для построения сводной таблицы необходимо просто перетащить указателем мыши в соответствующие области в нижней части окна нужные поля из верхней части окна. Например, для построения сводной таблицы, содержащей данные по площади территории, занимаемой странами на отдельно взятом континенте необходимо перетащить поле Континент в область Названия строк, поле Страна – в область Названия столбцов и поле Площадь (тыс. кв. км) – в область Значения. Букунов С.В. Кафедра прикладной математики и информатики Для выбора способа получения итоговых значений в сводной таблице необходимо щелкнуть левой кнопкой мыши по нужному полю и в выпадающем контекстном меню выбрать пункт Параметры полей значений. Букунов С.В. Кафедра прикладной математики и информатики В открывшемся контекстном меню необходимо выбрать нужную операцию для обработки данных в выбранном поле. Примечание. При создании сводной таблицы можно воспользоваться несколькими внешними источниками данных. Например, базами данных, текстовыми файлами или любыми другими, отличными от книг Microsoft Excel. Кроме того, можно воспользоваться источниками данных в Internet. Для этого в Мастере сводных таблиц можно открыть файлы запроса, а также создать новые запросы с помощью мастера запросов или Microsoft Query. Совет. В программе Excel предусмотрен еще один вариант обобщения данных – консолидация данных. Она позволяет создать таблицу-сводку по одной или нескольким категориям данных, используя все или часть данных из списка или другого источника. Для вызова процедуры консолидации используется команда Консолидация в меню Данные. 4.18. Корректировка сводной таблицы списка данных Сводная таблица имеет страничную организацию, аналогичную рабочей книге Excel. После создания сводной таблицы ее структуру можно изменить путем корректировки расположения полей данных. Для этого достаточно в окне Список полей сводной таблицы просто перетащить удаляемое поле из той области, где оно находится, за ее пределы, а затем перетащить туда другое поле. Совет. Программа Excel не позволяет удалить сводную таблицу обычным способом: выделить и нажать клавишу Delete. Для удаления сводной таблицы используйте команду Удалить лист или выделите необходимые строки и выберите команду Удалить в меню Правка. |