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

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

Использование списка в качестве базы данных

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

Столбцы списков становятся полями базы данных. Заголовки столбцов становятся именами полей базы данных. Каждая строка списка преобразуется в запись данных.

Требования к базе данных (списку) на листе Excel.

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

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

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

В самом списке не должно быть пустых строк и столбцов. Это упрощает идентификацию и выделение списка.

Заголовки столбцов должны находиться в первой строке списка. Они используются при подведении итогов, поиске и сортировке данных.

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

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

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

Желательно также придерживаться следующих советов:

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

Важные данные не следует помещать у левого или правого края списка; после применения фильтра они могут оказаться скрытыми.

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

Для упорядочения ячеек по значениям (без учета формата) предусмотрен определенный порядок сортировки.

При сортировке текста, значения сравниваются посимвольно слева направо. Например, если в ячейке содержится текст "A100", то после сортировки она будет находиться после ячейки, содержащей "A1" и перед ячейкой, содержащей "A11."

Строки в списке можно сортировать по значениям ячеек одного или нескольких столбцов. Строки, столбцы или отдельные ячейки в процессе сортировки переупорядочиваются в соответствии с заданным пользователем порядком сортировки. Списки можно сортировать в возрастающем (от 1 до 9, от А до Я) или убывающем (от 9 до 1, от Я до А) порядке.

По умолчанию списки сортируются в алфавитном порядке. Для сортировки месяцев и дней недели в соответствии с их логическим, а не алфавитным порядком следует использовать пользовательский порядок сортировки. Такой порядок сортировки также используется, если требуется отсортировать список в другом, особом порядке. Например, если в одном из столбцов списка содержатся значения «Низкий», «Средний» или «Высокий», можно создать такой порядок сортировки, что строки, содержащие «Низкий», будут первыми, «Средний» — следующими и «Высокий» — последними. (При помощи Сервис, Параметры, Списки можно создать собственный порядок сортировки).

Для сортировки списков в одном столбце следует использовать кнопки «По возрастанию» и «По убыванию».

Для сортировки списка по двум или более столбцам необходимо:

• Указать любую ячейку в сортируемом списке.
• Выбрать Данные, Сортировка.
• Указать столбцы сортировки в полях Сортировать по и Затем по.

Чтобы отсортировать данные по более, чем трем столбцам одновременно, отсортируйте список сначала по трем наименее значимым столбцам. Например, если список содержит сведения о сотрудниках и его требуется отсортировать по полям «Отдел», «Должность», «Фамилия», «Имя» и «Отчество», выберите сначала «Имя» в поле Сортировать по, «Отчество» в поле Затем по и отсортируйте список. Затем выберите «Отдел» в поле Сортировать по, «Должность» в поле Затем по, «Фамилия» в поле В последнюю очередь, по и отсортируйте список.

• Выбрать другие параметры сортировки и нажать кнопку OK.
• Повторить шаги 2 - 4 для следующих более значимых столбцов.

Автофильтр. Для применения автофильтра необходимо:

Щелкнуть любую ячейку внутри списка.

Выбрать Данные, Фильтр. Если у вас нет данной команды то следует зайти во вкладку Вид, Нажать правой кнопкой мыши на панели инструментов, затем выбрать Настройка панели быстрого доступа, в вкладке Выбрать команды из выбрать все команды, Найти в списке команду Автофильтр и добавить ее на панель.

В ячейках с именами полей списка должны появиться кнопки со стрелками вниз.

Чтобы отфильтровать строки, содержащие определенное значение, надо нажать кнопку со стрелкой в столбце, в котором содержатся искомые данные.

Выбрать значение в списке.

Повторить шаги 3 и 4, чтобы ввести дополнительные ограничения для значений в других столбцах.

Чтобы отфильтровать список по двум значениям в одном столбце или применить отличные от равенства операторы сравнения, надо нажать кнопку со стрелкой, а затем выбрать пункт Текстовые фильтры + Настраиваемый фильтр. После этого на экране появится диалоговое окно «Пользовательский автофильтр». В этом окне необходимо будет сделать следующее:

В верхнем левом поле ввода щелкнуть кнопку со стрелкой вниз и выбрать один из следующих операторов сравнения: «равно», «не равно», «больше», «больше или равно», «меньше», «меньше или равно», «начинается с», «не начинается с», «заканчивается на», «не заканчивается», «содержит», «не содержит».

В правом верхнем поле ввода ввести соответствующее значение.

Если необходимо отобрать строки, удовлетворяющие одновременно двум условиям отбора, то надо щелкнуть флажок «И», а затем сформировать второе условие в нижней части окна.

Если необходимо отобрать строки, удовлетворяющие одному из двух условий отбора, то надо щелкнуть флажок «ИЛИ», а затем сформировать второе условие в нижней части окна.

Расширенный фильтр.

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

• Скопируйте из списка заголовки фильтруемых столбцов.
• Вставьте скопированные заголовки столбцов в первой пустой строке диапазона условий отбора.
• Введите в строки под заголовками условий требуемые критерии отбора. Убедитесь, что между значениями условий и списком находится как минимум одна пустая строка.
• Укажите ячейку в списке.
• Выберите пункт Фильтр в меню Данные, а затем — команду + Дополнительно + Расширенный фильтр.
• Чтобы показать результат фильтрации, скрыв ненужные строки, установите переключатель Обработка в положение. Фильтровать список на месте.
Чтобы скопировать отфильтрованные строки в другую область листа, установите переключатель Обработка в положение. Скопировать результаты в другое место, перейдите в поле. Поместить результат в диапазон, а затем укажите верхнюю левую ячейку области вставки.
• Введите в поле Диапазон критериев ссылку на диапазон условий отбора, включающий заголовки столбцов.

Чтобы убрать диалоговое окно Расширенный фильтр на время выделения диапазона условий отбора, нажмите кнопку свертывания диалогового окна:

Совет. Если на листе существует диапазон с именем Критерии, то в поле Диапазон условий автоматически появится ссылка на этот диапазон

Примеры условий отбора расширенного фильтра

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

Подведение промежуточных итогов в списке (базе данных).

1. Отсортировать список по столбцу, для которого необходимо подвести промежуточный итог. Например, чтобы просуммировать единицы продукции, проданные каждым лицом в списке продавцов, объемы продаж и количество проданного, необходимо отсортировать список по столбцу продавцов.
2. Указать ячейку в этом списке.
3. Выбрать команду Промежуточные Итоги в меню Данные.
4. Выбрать столбец, содержащий группы, по которым необходимо подвести итоги, из списка При каждом изменении в. Это должен быть тот столбец, по которому проводилась сортировка списка на шаге 1.
5. Выбрать функцию, необходимую для подведения итогов, из списка Операция.
6. Выбрать столбцы, содержащие значения, по которым необходимо подвести итоги, в списке Добавить итоги по.

Проверка данных при вводе

Существует возможность создания списка значений, ограничивающего вводимые в ячейку величины элементами списка. Чтобы пояснить ограничения, наложенные на ячейку, нужно задать сообщение. Это сообщение будет появляться при выборе ячейки. Если запущен помощник, то оно будет появляться в окне помощника.

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

После ввода данных можно отыскать ячейки, которые содержат некорректные значения. При нажатии кнопки «Обвести неверные данные» на панели инструментов «Зависимости» такие ячейки будут обведены. После исправления ошибок кружки исчезают.

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

Задание

1. Создайте в своей рабочей книге новый лист под названием П5. Заполните в нем базу данных с помощью Формы: Заполним базу данных под названием «Планеты Солнечной системы». Введите в 1-ю, 2-ю и 3-ю строки листа, следующие данные (таблица 2.1):

Таблица 2.1 − Планеты Солнечной системы

Планеты Солнечной системы
Планета	Период	Расстояние	Диаметр	Масса	Спутники
Солнце	0	0	13929	2000000	0

Щелкните по любой ячейке новой базы данных и выберите Данные, Форма.

Если у вас нет данной команды то следует зайти во вкладку Вид, Нажать правой кнопкой мыши на панели инструментов, затем выбрать Настройка панели быстрого доступа, в вкладке Выбрать команды из выбрать все команды, Найти в списке команду Форма и добавить ее на панель.

Затем при помощи формы добавьте новые записи так, чтобы в итоге получилась следующая таблица (таблица 2.2):

Таблица 2.2− Планеты Солнечной системы (дополнение)

Планеты Солнечной системы
Планета	Период	Расстояние	Диаметр	Масса	Спутники
Солнце	0	0	13929	2000000	0
Меркурий	0,241	58	4.9	0.32	0
Венера	0,615	108	12,1	4.86	0
Земля	1	150	12.8	6	1
Марс	1,881	288	6,8	0,61	2
Юпитер	11,86	778	142,6	1906,98	16
Сатурн	29,46	1426	120,2	570,9	17
Уран	84,01	2869	49	87,24	14
Нептун	164,8	4496	50,2	103,38	2

2. Внесите в ячейки B2:E2 следующие примечания (Рецензирование – Создать Примечание): «Период обращения по орбите в земных годах»; «Среднее расстояние от Солнца, в млн.км»; «Экваториальный диаметр, тыс.км»; «Масса в 10²⁴кг».
3. Скопируйте таблицу на Лист2 – Лист4

Область таблицы A2:F12 можно рассматривать как базу данных, т.к. она соответствует следующим ограничениям:

1. Первый ряд базы данных содержит неповторяющиеся имена полей.
2. Остальные ряды базы данных содержат записи, которые не являются пустыми рядами
3. Информация по полям (столбцам) является однородной, т.е. только цифры или только текст.
4. Лист 1. Щелкните любую ячейку внутри базы данных и выберите команду: Данные, Фильтр, Автофильтр. Если у вас нет данной команды то следует зайти во вкладку Вид, Нажать правой кнопкой мыши на панели инструментов, затем выбрать Настройка панели быстрого доступа, в вкладке Выбрать команды из выбрать все команды, Найти в списке команду Автофильтр и добавить ее на панель.

На именах полей должны появиться кнопки с изображением стрелок. Понажимайте эти кнопки и исследуйте их работу.

Нажмите кнопку на поле «Планета». Выберите пункт «Текстовый фильтр». В появившемся окне установите параметры так, чтобы отобрать планеты, названия которых начинаются с букв «С» или «Ю».

После этого нажмите кнопку на поле «Масса» и выбрав пункт «Текстовый фильтр», оставьте в списке только те планеты, масса которых <600.

5. Лист 2. Условия фильтрации: найдите все планеты, диаметр которых меньше 50 тыс.км и масса которых меньше 4*10²⁴ кг.
6. Лист 3. Найдите все планеты, находящиеся от Солнца на расстоянии не менее 100 млн. км., имеющих массу в диапазоне от 3*10²⁴ кг до 500*10²⁴ кг, а также не более 2-х спутников.
7. Лист 4. Теперь перейдем к созданию Расширенного фильтра. Для этого вначале скопируйте 1-й ряд базы данных (2-я строка) в строки 15 и 20 Вашего рабочего листа. В ячейке B16 (поле Период) укажите условие >10. В ячейке F16 (поле Спутники) укажите условие >=2. Щелкните любую ячейку внутри базы данных. Затем выполните команду Данные, Сортировка и фильтр,
Дополнительно. В появившемся окне установите флажок “Скопировать результат в другое место”. Поле “Исходный диапазон” должно иметь значение $A$2:$F$12. В поле «Диапазон условий» установите $A$15:$F$16. В поле «Поместить результат в диапазон» укажите $A$20:$F$25. Нажмите кнопку Ok. В итоге Вы должны отобрать 4 планеты: Юпитер, Сатурн, Уран, Нептун.
8. Скопируйте заголовки столбцов в строки Н15:М15 и Н20:М20. В ячейке Н16 (поле Период) укажите условие >10, в ячейке М17 (поле Спутники) укажите условие >=2. Затем выполните команду Данные, Фильтр, Дополнительно с правильным указанием параметров «Диапазон условий» и «Поместить результат в диапазон». Сравните два результата фильтрации. В чем отличия.
9. С использованием расширенного фильтра найдите планеты
(таблица 2.3):
а) «Диапазон условий»:

Таблица 2.3 – Диапазон условий

Планета	Период	Расстояние	Диаметр	Масса	Спутники
М*
С*
Земля


б) имеющие период обращения более 2-х земных лет и экваториальный диаметр менее 50 тыс.км., или имеющих спутников более 15.
10. При помощи Данные, Сортировка отсортируйте данные в таблице в порядке убывания количества Спутников, затем по возрастанию их расстояния от Солнца.
11. Сохраните книгу «ПР_№5_ФИО».
12. Новый лист назовите Полный ассортимент, он должен выглядеть так:



13. С помощью команды Данные + Фильтр + Автофильтр на листе Полный ассортимент отфильтруйте базу данных: оставьте только персональные ксероксы. Восстановите весь список.
14. С помощью команды Данные + Фильтр + Автофильтр на листе Полный ассортимент отфильтруйте базу данных: оставьте только профессиональные и профессиональные плюс факсы.
Восстановите весь список и отключите Автофильтр.
15. Выполнить в задание Ассортимент Подведение промежуточных итогов. Данные + Промежуточные итоги.

Контрольные вопросы:

1. Какая таблица может быть использована в качестве базы данных?
2. Как задать допустимые значения для элементов базы данных?
3. Как отсортировать данные по нескольким полям?
4. Как вывести первые несколько элементов базы данных?
5. Как настроить расширенный фильтр и показать промежуточные итоги?