База Данных (БД, DB – Data Base) – поименованная совокупность структурированных данных, относящихся к определенной предметной области.
В общем смысле термин база данных можно применить к любой совокупности связанной информации, объединенной вместе по определенному признаку.
Довольно часто возникает необходимость хранить и обрабатывать данные представленные в виде таблиц. Максимальный размер базы данных в MS Excel определяется возможностями версии MS Excel (число строк и число столбцов в ли сте).
Информация, хранящаяся в таблицах, организована в виде строк и столбцов.
В первой строке любой базы данных обязательно должны быть указаны имена полей. Каждая строка таблицы, называемая записью, содержит данные об одном объекте. В столбце, называемом полем, содержатся сведения о каком-либо свойстве всех объектов хранящихся в таблице.
MS Excel содержит широкий набор средств для обеспечения эффективного управления базами данных.
Основным назначением баз данных является быстрый поиск содержащейся в них информации. Методами работы с БД являются:
· Сортировка базы данных
Процесс упорядочения записей в базе данных называется сортировкой. При сортировке изменяется порядок следования записей в базе данных или таблице.
Команда Данные — Сортировкаустанавливает порядок строк в таблице в соответствии с содержимым конкретных столбцов.
· Фильтрация базы данных
Процесс поиска и отбора информации в базе данных MS Excel называется фильтрацией. В MS Excel есть два вида фильтра: Автофильтр и Расширенный фильтр.
В случае простых критериев для выборки нужной информации достаточно команды Автофильтр. При использовании сложных критериев следует применять команду Расширенный фильтр.
Обе команды вызываются в результате выбора команды Данные — Фильтр.
· Подведении итогов в базе данных
Один из способов обработки и анализа базы данных состоит в подведении различных итогов. С помощью команды Данные — Итоги можно вставить строки итогов в список, осуществив суммирование данные нужным способом. При вставке строк итогов Excel автоматически помещает в конец списка данных строку общих итогов.
После выполнения команды Данные — Итоги можно выполнить следующие операции:
ü выбрать одну или несколько групп для автоматического подведения итогов по этим группам;
ü выбрать функцию для подведения итогов;
ü выбрать данные, по которым нужно подвести итоги.
Кроме подведения итогов по одному столбцу, автоматическое подведение итогов позволяет:
ü выводить одну строку итогов по нескольким столбцам;
ü выводить многоуровневые, вложенные строки итогов по нескольким столбцам;
ü выводить многоуровневые строки итогов с различными способами вычисления для каждой строки;
ü скрывать или показывать детальные данные в этом списке.
Команда Итогивставляет в базу данных новые строки, содержащие специальную функцию.
ПРОМЕЖУТОЧНЫЕ ИТОГИ(номер_функции; ссылка), где
номер_функции - это число от 1 до 11, которое указывает, какую функцию использовать при вычислении итогов внутри списка.
| Номер функции | Функция |
| СРЗНАЧ | |
| СЧЁТ | |
| СЧЁТЗ | |
| МАКС | |
| МИН | |
| ПРОИЗВЕД | |
| СТАНДОТКЛОН | |
| СТАНДОТКЛОНП | |
| СУММ | |
| ДИСП | |
| ДИСПР |
ссылка - это интервал или ссылка, для которой подводятся итоги.
Цель занятия. Изучение возможностей пакета MS Excel при работе с базами данных. Приобретение навыков создания и обработки БД.
Задания:
1. Создать таблицу по классификации растений.
2. Выполнить сортировку данных электронной таблицы.
3. Осуществить поиск и отбор данных в таблице, используя Автофильтр.
4. Используя критерии поиска, выбрать данные из созданной таблицы с помощью Расширенного фильтра и разместить их в отдельной таблице.
5. Подвести промежуточные итоги, осуществив суммирование данных по полю «Цена».
Решение:
1. Создать и заполнить таблицу 1. Исходные данные представлены на рис.1
Рис.1
2. Выполнить сортировку записей поля «Наименование растений» по алфавиту на возрастание.
3. Выполнить многоуровневую сортировку по полям «Наименование растений» и «Период созревания»– на убывание, поле «Цена, руб.» - по возрастанию (рис.2)
Рис.2
Использование двух методов фильтрации списка для отбора данных.
4. Щелкнуть в любом месте базы данных, в нашем случае диапазон A2:D32.
5. Выполнить команду Данные 
Фильтр Автофильтр.В качестве условия отбора можно выбрать - либо любое значение из списка, либо пункт — Условие.
5.1 Осуществить выборку значений «ранние» и «поздние» по столбцу «Период созревания»:
ü щелкнуть по кнопке списка справа от поля «Период созревания»;
ü выбрать пункт Условие;
ü в диалоговом окне выбрать параметры, как показаны на рис. 3
Рис.3
ü проанализировать результаты;
ü скопировать результат на новый лист, лист назвать Автофильтр;
ü вернуться к исходному списку.
5.2 Осуществить выборку значений по двум столбцам:
ü по столбцу «Наименование растений» только яблонь и груш;
ü по столбцу «Цена, руб.» отобрать записи в диапазоне от 230 до 300;
ü скопировать результат на лист Автофильтр.
5.3 Убрать Автофильтр.
6. Использование команды Расширенный фильтр для отбора данных.
6.1 Задать критерия отбора данных отвечающих следующим требованиям: - выбрать из списка только яблони по цене более 220 рублей:
ü критерий отбора отобразить в четырех ячейках, как показано на рис.4;
Рис.4
ü щелкнуть в любом месте базы данных, в нашем случае диапазон A2:D32;
ü выполнить команду Данные Фильтр Расширенный фильтр;
ü в диалоговом окне указать параметры, как показано на рис.5;
Рис.5
ü проанализировать результат.
6.2 Используя расширенный фильтр, найти растения, которые удовлетворяют ценовому диапазону от 270 рублей И до 300 рублей (используется логическое действие И):
ü критерий отбора расположить на экране в четырех ячейках, согласно рис. 6;
Рис.6
ü выполнить действия аналогичные предыдущему заданию;
ü в списке «Классификация растений» изменить цвет ячеек, которые соответствуют условиям отбора в отфильтрованной таблице.
6.3 Используя расширенный фильтр, найти растения, которые удовлетворяют ценовому диапазону менее 220 рублей ИЛИ более 430 рублей (используется логическое действие ИЛИ).
ü критерий отбора расположить на экране в трех ячейках, согласно рис. 7;
Рис.7
ü выполнить действия аналогичные предыдущим заданиям;
ü в списке «Классификация растений» изменить цвет ячеек, которые соответствуют условиям отбора в отфильтрованной таблице.
Объяснить различия в написании критериев при использовании логических действий И, ИЛИ.
7. Самостоятельно отобрать данные из списка, используя расширенный фильтр, согласно следующим критериям:
ü выбрать яблони ранних сортов;
ü выбрать поздние сорта из всех наименований растений по цене выше среднего значения (СРЗНАЧ рассчитать в отдельной ячейке);
ü используя логические действия И, ИЛИ придумать и реализовать критерии по полю «Цена, руб.»;
ü используя функцию СЧЕТЕСЛИ, рассчитать количество ранних, средних, поздних сортов растений (разместить расчет в отдельных ячейках).
8. Подвести итоги по полю «Цена, руб.» для растений – яблоня, груша, вишня:
Рис.8
ü выполнить команду Данные Фильтр Итоги;
ü в диалоговом окне указать параметры согласно рис.8, т.е. рассчитать сумму по цене для растений;
ü проанализировать результаты.