Материал: Методические указания к выполнению лабораторной работы № 2 по дисциплине «Информатика» для студентов направления «Электроника и наноэлектроника». Кошелева Н.Н., Плотникова Е.Ю

Раздел №3 ms Excel: сортировка и фильтр данных

Цель: приобрести навыки сортировки базы данных; задания параметров фильтра данных в списке MS Excel.

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

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

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

Команда Данные/Сортировка устанавливает порядок строк в таблице в соответствии с содержимым конкретных столбцов.

Сортировка по возрастанию предполагает следующий порядок:

• Числа

• Текст, включая текст с числами (почтовые индексы, номера автомашин)

• Логические значения

• Значения ошибок

• Пустые ячейки

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

Фильтрация данных в списке

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

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

В Excel для фильтрации данных используются команда Фильтр.

Порядок работы

Рис. 8

Задание 1. Сортировка.

Выделите диапазон A2:F12, выполните команду Данные/Сортировка.

Изучите окно диалога Сортировка

Рис. 9

Замечание. С помощью раскрывающегося списка Сортировать по вы можете выбрать столбец для сортировки. Порядок устанавливает сортировку по возрастанию или по убыванию.

Выполните последовательно следующие сортировки:

Поле Family по возрастанию

Поле Age по убыванию

Поле GoodLearned по убыванию

Поле Grant по убыванию

Снова выполните сортировку поля Family по возрастанию.

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

Допустим в нашей базе данных среди студентов есть учащиеся с одинаковыми фамилией и именем. Добавьте в конец таблицы следующую запись: Борисов Георгий; дата рождения - 11.09.90; курс - 2; хорошист.

Выполните сортировку: первый уровень – поле Family по возрастанию, второй уровень – поле BirthDay по убыванию (от старых к новым). Просмотрите результат сортировки.

Измените в выполненной сортировке второй уровень – сортировка поля Year по убыванию. Сравните результаты двух сортировок.

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

Замечание. Обратите внимание – если в диалоговом окне Сортировка убрать галочку с пункта Мои данные содержат заголовки, то названия заголовков будут сортироваться вместе с данными!

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

Сделать сортировку чувствительной к использованию прописных и строчных букв Изменить направление сортировки (вместо сортировки сверху вниз установить сортировку слева направо)

Исправьте фамилии следующих студентов (начинаются с маленькой буквы): борисов Георгий (дата рождения - 31.10.91) и волков Александр.

В меню Параметры установите флажок Учитывать регистр.

Заново отсортируйте записи поля Family по возрастанию. Просмотрите результат.

Измените начало фамилий на заглавные буквы.

Задание 2. Фильтр данных.

Выделите заголовок таблицы (диапазон A2:F2).

Выполните команду Данные/Фильтры.

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

Рис. 10

Просмотрите выпадающее меню поля Year. Изучите команды, входящие в данное меню. Выполните следующие фильтры для отбора записей по значениям:

Фильтр: студенты первого курса (для этого необходимо снять галочку со значения поля «2»). Результат скопируйте в текстовый документ Фильтры. Затем отмените фильтр командой меню Снять фильтр с «Year»;Фильтр: студенты возраста 19 лет. Результат скопируйте в уже созданный текстовый документ Фильтры. Затем отмените фильтр;Фильтр: студенты второго курса возраста 16 лет (последовательно выполняется 2 фильтра). Результат скопируйте в текстовый документ. Затем отмените фильтр;Фильтр: студенты - хорошисты второго курса (последовательно выполняется 2 фильтра). Результат скопируйте в текстовый документ. Затем отмените фильтр;

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

Фильтр: студенты с возрастом «выше среднего». Результат скопируйте в уже созданный текстовый документ Фильтры. Затем отмените фильтр;

Фильтр: студенты с размером стипендии более 800 рублей. Результат скопируйте в уже созданный текстовый документ Фильтры. Затем отмените фильтр;

Фильтр: студенты с возрастом 16 лет или старше 19. Результат скопируйте в уже созданный текстовый документ Фильтры. Затем отмените фильтр;

Самостоятельно придумайте 5 сложных фильтров, зафиксируйте их в тетради и скопируйте результаты в текстовый документ Мои фильтры.

Раздел №4

Ms Excel: использование специальных функций

Цель: приобрести навыки использования специальных функций MS Excel для обработки базы данных.

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

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

• диапазон, задающий обрабатываемую базу данных;

• имя поля (столбца), значение которого обрабатывается функцией;

• диапазон, задающий критерий поиска.

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

Порядок работы

Задание 1. Правила работы функции БИЗВЛЕЧЬ.

Рассмотрим правила работы с этими функциями на примере одной из них, функции БИЗВЛЕЧЬ. Эта функция просматривает содержимое базы данных в поисках строки, удовлетворяющей установленному критерию. Если такая строка имеется, то из нее извлекается содержимое поля, указанного пользователем.

На рис.1 приведено решение задачи о поиске в базе данных, определяемой диапазоном А2:F13, студентки, о которой известно, что зовут ее Алена, что ей более 17 лет и что она учится на 2-м, 3-м или 4-м курсе. Нужно по этим данным установить фамилию студентки, а так же дату ее рождения. Критерий поиска для решения этой задачи построен в диапазоне А15:D16. Первая строка диапазона - копии заголовков некоторых столбцов базы данных, а именно тех столбцов, которые «участвуют» в формулировке критериев отбора нужной строки базы данных. Вторая строка диапазона - условия, которым должна удовлетворять искомая запись. Условие в ячейке А16 означает, что в поле «Family» искомой строки должен содержаться текст, оканчивающий словом «Алена», а начинается этот текст произвольным количеством произвольных символов (именно это и обозначается символом «*»). Условие в ячейке В16 определяет, что в поле «Age» нужной строки должно содержаться число, большее 17. Наконец, в ячейках C16 и D16 устанавливаются два условия на одно и то же поле «Year», (которые должны выполняться одновременно).

Результаты работы функции БИЗВЛЕЧЬ показаны в ячейках А20 и В20. В обеих этих ячейках содержатся формулы с вызовом этой функции. Различаются эти формулы только вычисляемым значением (т.е. вторым параметром функции). В ячейке А20 вычисляемым значением является поле «Family», а в ячейке В20 - поле «BirthDay». Текст этих формул выглядит следующим образом.

- Ячейка А20: =БИЗВЛЕЧЬ(А2:F13; A2; А15:D16)

- Ячейка B20: =БИЗВЛЕЧЬ(А2:F13; B2; А15:D16)

Замечание! После выполнения вычисления необходимо установить формат данных ячейки B20 – «Дата».

Если функция БИЗВЛЕЧЬ найдет более одной строки, удовлетворяющей критерию поиска, то результатом работы функции будет сообщение «#ЧИСЛО!», если же ни одна строка базы данных не удовлетворяет заданному критерию, то сообщением будет текст «#ЗНАЧ!».

Рис. 11

Задание 2. Использование функции БСЧЕТА.

В заключение, приведем в обзорном порядке еще несколько функций, ориентированных на работу с базами данных Excel. Функция БСЧЕТА позволяет подсчитать количество записей, удовлетворяющих заданному критерию. Например, для приведенной на рис. 2.11 базы данных можно подсчитать суммарное количество студентов, обучающихся на 2-м, 3-м и 4-м курсах, если в некоторую ячейку ввести текст следующей формулы:

=БСЧЕТА(A2:F13;A2;C15:D16).

Данная формула использует в качестве критерия диапазон C15:D16, где установлены нужные условия на значения поля «Year». В качестве второго параметра можно указать заголовок любого поля, не содержащего «пустых» (т.е. не заполненных) значений. (в нашем примере выбрано поле «Family»)

Задание 3. Использование функций ДМАКС и ДМИН

Функция ДМАКС позволяет найти запись с максимальным значением некоторого поля. Формула:

=ДМАКС(A2:F13;B2;C15:C16),

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

Задание 4. Использование функции БДСУММ.

Наконец, функция БДСУММ находит сумму чисел, расположенных в заданном столбце, при этом учитываются только записи, удовлетворяющие нужному критерию. Например, с помощью формулы:

=БДСУММ(A2:F13;F2;D15:D16)

можно определить сумму стипендий, выплачиваемых студентам первых 4-х курсов.

Раздел №5

Ms Excel: использование функций базы данных

Цель: приобрести навыки подведения итогов в базе данных MS Excel.

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

Один из способов обработки и анализа базы данных состоит в подведении различных итогов. С помощью команды Данные/Итоги можно вставить строки итогов в список, осуществив суммирование данные нужным способом. При вставке строк итогов Excel автоматически помещает в конец списка данных строку общих итогов.

После выполнения команды Данные/Итоги вы можете выполнить следующие операции:

1. выбрать одну или несколько групп для автоматического подведения итогов по этим группам

2. выбрать функцию для подведения итогов

3. выбрать данные, по которым нужно подвести итоги

Кроме подведения итогов по одному столбцу, автоматическое подведение итогов позволяет:

1. выводить одну строку итогов по нескольким столбцам

2. выводить многоуровневые, вложенные строки итогов по нескольким столбцам

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

4. скрывать или показывать детальные данные в этом списке

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

Синтаксис: ПРОМЕЖУТОЧНЫЕ.ИТОГИ (номер_функции; ссылка)

Номер_функции - это число от 1 до 11, которое указывает, какую функцию использовать при вычислении итогов внутри списка.

Ссылка - это интервал или ссылка, для которой подводятся итоги.

Если список с промежуточными итогами уже создан, его можно модифицировать, редактируя формулу с функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

Задание 1. Подведение итогов в базе данных. Функции 1 и 9.

Выделите поле Grant – диапазон F2:F13Выполните команду Данные/Итоги (Промежуточные итоги в версии 2007), подсчитайте сначала сумму, затем среднее значение размера выдаваемой стипендии. Для этого в веденной формуле исправьте номер функции с 9 на 1 (см. таблицу в теории).

Таблица 1