Сортировка и обработка списков

При работе с таблицами приходится сортировать и обрабатывать список данных. Список - это один из способов организации данных на рабочем листе. Список создается как помеченный ряд, состоящий из строк с однотипными данными. Данные, организованные в список на­зываются базой данных (БД). В Excel понятия список и база данных взаимозаменяемы. Строки таблицы - это записи базы данных, а столб­цы - поля базы данных. В каждом поле содержится информация опреде­ленного типа.

При работе с БД часто приходится сортировать или отбирать дан­ные по определенному признаку. Для такой обработки данных исполь­зуются операции: сортировка, фильтрация списков, формирование ито­гов.

При создании списка на рабочем листе Excel необходимо выпол­нять следующие правила:

1. На одном рабочем листе не следует помещать более одного списка, поскольку некоторые операции (например фильтрация) рабо­тают в определенный момент только с одним списком.

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

3. Имена столбцов должны располагаться в первой строке списка.

4. Для имен столбцов следует использовать шрифт, тип данных, выравнивание, формат отличные от тех, которые использовались для данных списка.

5. Чтобы отделить имена столбцов от данных следует разместить рамку по нижнему краю ячеек строки с именами столбцов. Не рекомен­дуется использовать пустые строки или пунктирные линии.

 

 

Сортировка данных

Список можно отсортировать по алфавиту, по значению или в хронологическом порядке в соответствии с содержанием определенного поля. Чтобы отсортировать весь список, достаточно выделить одну ячейку и выбрать в меню Данные команду Сортировка. Excel автома­тически выделит весь список.

Если в первой строке находятся имена полей, то они не будут включены в сортировку, а итоговая строка, если таковая имеется, будет включена в сортировку. Поэтому необходимо контролировать выделяе­мую область исходного списка для сортировки.

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

 

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

Ключом сортировки в этом случае является столбец с текущей ячейкой.

Отменить сортировку можно, выполнив команду Отменить Сор­тировка в меню Правка.

Фильтрация списков

С помощью фильтрации можно выводить и просматривать только те данные, которые удовлетворяют определенным условиям. Excel по­зволяет быстро и удобно просматривать требуемые данные из списка с помощью автофильтра.

Чтобы использовать автофильтр надо:

1. Выделить область списка для поиска с заголовками полей.

2. Выполнить команду Фильтр - Автофильтр в меню Данные. В заголовках каждого столбца появятся кнопки со стрелками вниз.

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

Критерии фильтра находятся в списке в алфавитном порядке. В дополнение к ним имеется еще пять ключей: Первые 10, Условия, Пус­тые, Непустые, Все. Эти ключи позволяют найти в списке десять запи­сей, ведущих по выбранному показателю; записи, удовлетворяющие ус­ловию, заданному пользователем; записи, не содержащие никакой информации; записи, ячейки выделенного поля которых не пусты.

Чтобы снова вывести на экран полный список, нужно в списке критериев фильтрации выделить строку Все или в меню Данные вы­брать команду Фильтр - Показать все. Для отказа от фильтрации - по­вторить Фильтр - Автофильтр в меню Данные.

Для фильтрации данных по сложному критерию, а также для по­лучения части исходного списка по нескольким заданным столбцам ис­пользуется команда Расширенный фильтр меню Данные. Отличие этой команды от команды Автофильтр состоит в том, что отфильтро­ванные записи можно вынести в другое место рабочего листа, не испор­тив исходный список.

Использование расширенного фильтра предполагает наличие диапазона критериев (условий). Удобнее всего диапазон критериев располагать над списком (рис. 5). Диапазон критериев (условий) должен состоять по крайне мере из двух строк: в верхней строке указываются имена полей (заголовки), во второй и последующих строках - условия отбора, за исключением вычисляемых условий (рис. 6). Заголовки в диапазоне критериев должны точно совпадать с заголовками столбцов в списке (заголовки в таблицу критериев лучше скопировать из исходной таблицы).

 

Рис. 5. Использование расширенного фильтра

Для задания таблицы поиска надо выделить область списка для поиска с заголовками полей.

Для формирования таблицы критериев необходимо:

1. Скопировать имена полей списка, по которым будет задаваться условие, в ту часть рабочего листа, где будет располагаться таблица критериев. При этом число строк в таблице определяется числом крите­риев поиска. Включение пустых строк в таблицу недопустимо.

2. Задать критерии поиска. Задание критериев поиска в виде кон­стант требует точной копии имен тех столбцов, которые задают условия фильтрации. Если критерии связаны оператором И, то они должны на­ходится в одной строке, если ИЛИ - в разных. Можно создать таблицу критериев, в которой задается вычисляемый критерий, по которому ведется поиск записей. Ввод вычисляемых критериев поиска в таблицу критериев требует выполнения следующих правил:

2.1 Формула должна выводить логические значения Истина или Ложь (рис. 6, 7). После выполнения поиска на экран выводятся только те строки, для которых результатом вычисления формулы будет Истина.

Рис. 6. Использование вычисляемого условия в расширенном фильтре

Рис. 7. Использование вычисляемого условия в расширенном фильтре

 

2.2 Формула должна ссылаться хотя бы на один столбец в списке.

Для задания таблицы (области)вывода - в свободное место ра­бочего листа скопировать имена тех полей, которые определяют вид выходного документа.

После чего выполнить команду Расширенный фильтр, по кото­рой на экран будет выведено диалоговое окно, где надо задать область поиска исходного списка, область критериев, указать выполнять фильт­рацию на исходном месте или перенести данные в другую область и ес­ли для отфильтрованных данных задана область вывода, то указать ее (рис.5).

В условии фильтрации можно использовать возвращаемое форму­лой значение. При задании формул в условиях заголовок блока условия оставляют незаполненным. Используемая в условии формула должна ссылаться либо на заголовок столбца, либо на соответствующее поле первой записи списка. Например, в формуле =С12>CP3HAЧ(SС$12:$С$17) С12 ссылается на соответствующее поле (столбец С) первой записи (стро­ка 12) списка.. При использовании заголовка столбца в формуле условия вместо имени диапазона, в ячейке будет выведено значение #ИМЯ? или #ЗНАЧ!. Эту ошибку можно не исправлять, так как она не повлияет на результаты фильтрации.

Задание к лабораторной работе №8

1. Откройте рабочую книгу Преподаватели_3.

2. Упорядочьте таблицу на листе Семестр_1 в алфавитном порядке фамилий:

• Выделите столбцы таблицы кроме первого;

• В меню Данные выберите Сортировка;

• В диалоговом окне Сортировка диапазона в поле Сортиро­вать по выбрать имя столбца с фамилиями, и опцию По возрастанию.

3. Выполните аналогичные действия с таблицей на листе Семестр_2, упорядочив ее по убыванию среднего балла.

4. На листе Итог, используя автофильтр, определите студентов средний балл которых в первом семестре выше 3, но меньше или равен 4.5:

· Выделите таблицу;

· Выполните команду Данные - Фильтр - Автофильтр;

· Щелкните по стрелке в столбце Семестр_1 и выберите строку Условие;

· Вдиалоговом окне в одном поле оператора выберите > и в со­седнем поле введите значение 3, в другом поле оператора выберите знак <= и введите значение 4,5;

· Выберите опцию И (так как должны выполняться оба условия одновременно); щелкните по кнопке ОК;

· Сохраните рабочую книгу под именем Ведомость_5.1;

Верните все записи таблицы, щелкнув по стрелке в столбце Семестр_1 и, выбрав значение Все.

5. Аналогично определите студентов, средний балл которых равен 3 во втором семестре и отличников по двум семестрам, сохраняя рабо­чие книги под именами Ведомость_5.2 и Ведомость_5.3.

6. Выполните задания пунктов 4-5, используя расширенный фильтр. Задайте на рабочем листе Итог таблицы поиска, вывода и кри­териев (рис.5 ). Выполните команду Данные - Фильтр - Расширен­ный фильтр, Каждый раз указывая нужную таблицу критериев и выво­да. Таблица поиска для всех трех случаев одна и та же. Сохраните рабо­чую книгу под именем Ведомость_5.4.

7. Используя логическую операцию ИЛИ, определите студентов, чьи фамилии начинаются либо на букву А, либо на Б, либо на В, (либо на любые другие буквы, если таковых не имеется в вашем списке студентов).

8. Одновременное использование логических операций И и ИЛИ, определите для диапазона критериев следующее условие: выбрать из списка записи о студентах, средний бал которых больше или равен 4, с фамилиями на А и на В (или на любые другие буквы), и о студентах, средний бал которых меньше или равен 3, с фамилиями на Б.

9. Используя расширенный фильтр, определите фамилии студентов, оценки которых выше среднего.

10. Используя расширенный фильтр, определите фамилии студентов, имеющих оценки от 4 до 5. Для диапазона критериев выберите логический оператор И. Диапазон критериев должен состоять только из вычисляемого условия с заголовком Вычисляемое условие (такого заголовка в исходном списке нет).

11. Сохраните рабочую книгу под именем Ведомость_5.5.

12. Переместите все файлы Ведомость в соответствующую папку на дискете.

Рекомендуемая литература

1. Курбатова, Е.А. Microsoft Excel 2003. Самоучитель.: - М.: Издательский дом «Вильямс», 2006. – 352с.: ил.

2. Маликова, Л.В. Практический курс по электронным таблицам МS Excel: учебное пособие / Л.В. Маликова, Пылкин А.Н., Жулева С.Ю. – 2-е изд. испр. и доп. – М.: Горячая линия – Телеком, 2006. – 256 с.: ил.

3. Романова, Ю.Д. Информатика и информационные технологии: учебное пособие / Ю.Д. Романова, И.Г. Лесничая, В.И. Шестаков, И.В. Мисинг, П.А. Музычкин; под ред. Ю.Д. Романовой. – 3-е изд., перераб. И доп. – М.: Эксмо, 2008. – С. 309-404. – (Высшее экономическое образование).

 

Оглавление

Введение. 3

Основные понятия. 3

Лабораторная работа №1. 4

Задание к лабораторной работе №1. 9

Лабораторная работа №2. 9

Задание к лабораторной работе №2. 12

Лабораторная работа №3. 13

Задание к лабораторной работе №3. 15

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

Задание к лабораторной работе №4. 17

Лабораторная работа №5. 18

Задание к лабораторной работе №5. 20

Лабораторная работа №6. 21

Задание к лабораторной работе №6. 22

Лабораторная работа №7. 22

Задание к лабораторной работе №7. 24

Лабораторная работа №8. 26

Задание к лабораторной работе №8. 30

Рекомендуемая литература. 31