Выполнение операций со списками (базами данных) в табличном процессоре Excel

Понятие о списках (базах данных) Excel

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

Строка списка – это запись базы данных;

Столбец списка – это поле базы данных.

Название столбца при работе с базой данных называется именем поля.

 

ПРАВИЛА формирования списка (базы данных):

1. Имя поля (название столбца) должно занимать ровно одну ячейку.

2. Имена полей должны занимать верхнюю строку списка.

3. Между именами полей и данными не должно быть пустых строк.

4. Между списком и другими данными (вспомогательными параметрами, диапазонами критериев, другим списком и т.п.) должно быть не менее одной пустой строки и одного пустого столбца.

 

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

Сортировка данных в списке – это расположение данных в определенном порядке. Сортировать данные списка в Excel можно по одному, двум или трем полям.

 

Рассмотрим базу данных Студенты вида:

 

3.2.1. Сортировка по одному полю

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

 

3.2.2. Сортировка по нескольким полям

Щелкнуть мышью в любой ячейке внутри списка. Выбрать пункт меню Данные – Сортировка. В диалоговом окне Сортировка данных в выпадающем списке Сортировать по выбрать поле (столбец) для первичной сортировки; в выпадающем списке Затем по выбрать поле (столбец) вторичной сортировки, а в выпадающем списке В последнюю очередь по выбрать поле (столбец) следующей сортировки.

Например, если поля для сортировки выбраны так: 1. Номер группы; 2. Год рождения; 3. Фамилия; то данные сначала будут отсортированы по номеру группы; внутри группы по году рождения и, наконец, при одинаковых номере группы и годе рождения, по фамилиям в алфавитном порядке. Результат такой сортировки приведен ниже:

3.3. Фильтрация (выборка) данных списка

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

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

 

Рассмотрим таблицу базы данных Студенты вида:

3.3.1. Фильтрация данных с помощью автофильтра

Задание 1. Пусть необходимо из списка Студенты п. 3.3 выбрать студентов 12-й группы, получающих стипендию 800 рублей и более. Ход выполнения работы:

Щелкнуть мышью в любой ячейке внутри списка

Выбрать пункт меню Данные – Фильтр – Автофильтр. В заголовках столбцов списка появятся кнопки раскрывающегося списка ().

В столбце Группа щелкнем по такой кнопке и выберем из списка 12. Фильтрация по данному полю сразу будет произведена.

В столбце Стипендия щелкнем по кнопке и выберем из раскрывающегося списка Условие, поскольку необходимо выбрать студентов не с конкретной стипендией, а всех с размером стипендии ≥800. В появившемся диалоговом окне Пользовательский автофильтр в верхней левой строке щелкнем по кнопке и выберем из списка больше или равно, а в правой верхней строке выберем из раскрывающегося списка или введем с клавиатуры 800. Таким образом, сформировано условие «больше или равно 800». Поскольку на столбец Стипендия накладывается только одно условие, нажмем ОК. Фильтрация по заданным условиям будет выполнена.

Примечание. Отменить результаты фильтрации данных в таблице можно, щелкнув и выбрав Показать все.

 

Задание 2. Пусть из списка Студенты п. 3.3 необходимо выбрать студентов 11-й и 12-й групп 1987-го года рождения.

Предварительно отменив результаты предыдущей фильтрации, выполним следующее:

В столбце Группа щелкнем по кнопке и выберем из списка Условие; затем в диалоговом окне Пользовательский автофильтр в верхних строках описанным в Задании 1 способом сформируем условие «равно 11».Далее установим переключатель ИЛИ. В нижних строках сформируем условие «равно 12».

Пояснение: условия – студенты 11-й и 12-й групп – связаны логическим ИЛИ (а не логическим И), т.к. номер группы 11 или 12, а не 11 и 12 одновременно.

В столбце Год рождения щелкнем по кнопке и выберем из раскрывающегося списка 1987.

Фильтр покажет записи, удовлетворяющие таким условиям.

 

3.3.2. Фильтрация данных с помощью расширенного фильтра

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

 

ПРАВИЛА формирования диапазона условий для расширенного фильтра:

1. Между ячейками данных списка и ячейками диапазона условий должна быть хотя бы однапустая строка или столбец.
2. Для формирования диапазона условий необходимо скопировать заголовки столбцов, для которых задаются условия выборки (можно скопировать заголовки всех столбцов, но под заголовками тех столбцов, для которых условия фильтрации не задаются, ничего не записывается, т.е. ячейки остаются пустыми).
3. Под каждым заголовком с помощью знаков операций сравнения (>, <, =, <=, >=) записывается условие фильтрации по данному полю. В одной ячейке записывается только одно условие.
4. Если условия фильтрации связаны логическим И, ячейки, в которые записываются эти условия, располагают в одной строке, а если условия связаны логическим ИЛИ, они должны быть расположены в разных строках.

 

Задание 1. Пусть из базы данных Студенты п. 3.3 необходимо выбрать студентов 11-й и 12-й групп 1987-го года рождения.

Для формирования диапазона условий (правило № 2) в ячейки А12 и В12 копируем заголовки нужных нам столбцов – группа и год рождения соответственно. 11-ю строку оставляем пустой (правило № 1).

Условия для номера группы (11) и года рождения (1987) связаны между собой логическим И, поэтому записываем их в одной строке (правило № 4), в нашем случае 13-ой. В ячейке В13 (под заголовком год рождения) записываем условие =1987 или 1987. (Примечание: при записи условий знаки неравенств записывать обязательно, а знак равенства можно опустить). В ячейке А13 (под заголовком группа) записываем условие 11.

Теперь запишем условия для отбора студентов 12-й группы. С условиями для студентов 11-й группы они связаны логическим ИЛИ, поэтому должны быть записаны в другой строке (правило № 4). В 14-й строке записываем условия: для столбца группа (ячейка А14) – 12, а для столбца год рождения (ячейка В14) – 1987.

Диапазон условий сформирован:

Поскольку с нашем случае в списке студентов нет групп с номером, большим 12, то диапазон условий может быть записан и следующим образом:

 

Выполняем команду меню Данные – Фильтр – Расширенный фильтр.

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

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

Устанавливаем курсор в строке Диапазон условий и мышью в окне таблицы выделяем блок ячеек А12:В14 (или А12:В13, если выбран второй вариант записи условия для номера группы), в которые мы записали условия фильтрации.

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

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

Нажать ОК, и список будет отфильтрован в соответствии с заданными условиями.

Примечание. Отменить результаты фильтрации данных в таблице можно, выполнив команду меню Данные – Фильтр - Показать все.

 

Задание 2. Пусть из базы данных Студенты п. 3.3 нужно выбрать студентов 11-й и 12-й групп, получающих стипендию в размере от 500 до 800 рублей включительно.

Для формирования диапазона условий в ячейки А12 и В12 копируем заголовки столбцов группа и стипендия соответственно (правило № 2). 11-ю строку оставляем пустой (правило № 1).

Условия для номера группы (11) и размера стипендии (от 500 до 800) связаны между собой логическим И, поэтому (правило № 4) записываем их в одной строке (в нашем случае 13-й). В ячейке А13 (под заголовком группа) записываем условие 11. Условие для размера стипендии может быть сформулировано так «больше или равно 500, но меньше или равно 800». Эти два условия связаны логическим И, т.к. требуется их одновременное выполнение. Каждое из этих условий должно быть записано в отдельной ячейке (правило № 3), причем ячейки (по правилу № 4)должны располагаться на одной строке (в нашем случае 13-й) и заголовок для ячеек с этими условиями одинаков – стипендия. Поэтому для формирования условия «от 500 до 800» в ячейку С12 копируем еще раз заголовок стипендия. Под любым заголовком стипендия в одной ячейке (например, В12) записываем условие >=500, а в другой (например, С12) условие <=800.

Аналогичным образом записываем условия для отбора студентов 12-й группы, которые связаны логическим ИЛИ с условиями для студентов 11-й группы, поэтому (правило № 4) должны быть записаны в другой строке (в нашем случае 14-й).

Диапазон условий сформирован:

Поскольку с нашем случае в списке студентов нет групп с номером, большим 12, то диапазон условий может быть записан и следующим образом:

Выполняем команду меню Данные – Фильтр – Расширенный фильтр, указываем Исходный диапазон (А1:F10)и Диапазон условий (А12:С14 или А12:С13). Нажимаем ОК. Фильтрация по заданным условиям будет выполнена.

Задание 3. Пусть из приведенного списка необходимо выбрать студентов 11-й группы:

 

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

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

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

 

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

Вычисляемый критерий представляет собой формулу, записанную в строке области условий, которая может принимать одно из логических значений ИСТИНА или ЛОЖЬ. Формула строится с использованием адресов ячеек, встроенных функций, математических знаков операций и знаков сравнения.

 

ПРАВИЛО № 5: Если в расширенном фильтре используется вычисляемый критерия сравнения, то имя столбца, содержащего формулу вычисляемого критерия, должно обязательно отличаться от названия столбца в списке.

Задание. Пусть необходимо из базы данных Студенты п. 3.3 выбрать студентов 11-й и 12-й групп, стипендия которых больше средней.

Если на экране находятся результаты предыдущей фильтрации, отменим их, выполнив команду Данные – Фильтр – Отобразить все.

Скопируем названия столбцов (правило № 2), по которым будут записываться условия отбора (это столбцы Группа и Стипендия) в ячейки А12 и В12 соответственно.

Записываем условия по выбору номера группы: в ячейку А13 (под заголовком Группа) записываем 11, а в ячейку А14 записываем 12 (по правилу № 4).

Условие отбора студентов по размеру стипендии будем записывать с помощью вычисляемого критерия, т.к. необходимо осуществлять вычисление средней стипендии, поэтому название столбца Стипендия в ячейке В12 изменим, например, на СтипендияС (правило № 5). В ячейках В13 и В14 нужно записать условия отбора по размеру стипендии, а они для обеих групп одинаковы. Условие для вычисляемого критерия записывается по правилам формирования формул Excel, значит, оно должно начинаться со знака «равно», использовать функцию СРЗНАЧ (для вычисления средней стипендии) и содержать адрес ячейки F2 (самой первой из ячеек с данными по стипендии). Причем в функции СРЗНАЧ должны быть использованы абсолютные адреса ячеек, т.к. диапазон ячеек для расчета среднего неизменен, а при записи условия сравнения, наоборот, записывается относительный адрес, т.к. в процессе выборки должны просматриваться и сравниваться со средней величиной стипендии все ячейки, начиная с F2. Таким образом, вычисляемый критерий должен быть записан так: =F2>СРЗНАЧ($F$2:$F$10). Диапазон условий сформирован:

Примечание: после завершения формирования вычисляемого критерия в ячейках будет отображен результат вычисления: ИСТИНА или ЛОЖЬ, а саму формулу критерия можно просмотреть лишь в строке формул.

Выполняем команду меню Данные – Фильтр – Расширенный фильтр, указываем Исходный диапазон (А1:F10)и Диапазон условий (А12:В14). Нажимаем ОК. Фильтрация по заданным условиям будет выполнена.