2015-05-10
2654
«Базы данных в MS Excel»
Цель работы: Научиться создавать базы данных в
Excel. Изучить возможности работы с базами данных.
Задание:
1. Оформление базы данных.
2. Создание формы.
3. Сортировка элементов базы данных.
4. Фильтрация данных.
5. Суммирование чисел в базе данных.
Пример выполнения задания:
Создать базу данных сотрудников предприятия, заголовок которой имеет вид:
| № | Фамилия | Имя | Отчество | Должность | Код |
| Иванов | Иван | Иванович | Менеджер |
Заполнить базу данных на 15 сотрудников.
1. Оформление базы данных производится так же, как и оформление любой таблицы в Excel. Только следует помнить, что таблица для базы данных не должна иметь пустых строк и пустых столбцов, а также не допускается объединение ячеек для данной таблицы.
В нашем случае заголовок имеет вид:
Теперь в ячейку A4 запишем:
=СТРОКА(А4)-3
и растянем эту формулу до ячейки А18.
Далее заполняем ячейки B4:F18 данными, получим:
2. Рассмотрим работу с формой базы данных, для этого добавим при помощи формы еще двух сотрудников. Выполним: \ Данные \ Форма...
|
|
|
получим диалоговое окно вида:
Теперь если мы хотим добавить запись в базу данных, то нажмем кнопку «Добавить» и получим:
Заметим, что поле ввода номера не доступно, так как там заложена формула. Мы заполняем только доступные нам поля и получаем:
Заметим, что для перехода из одного поля ввода в другое необходимо нажимать клавишу TAB.
Теперь для того, чтобы добавить еще одного сотрудника, снова нажмите кнопку «Добавить» и введите данные этого сотрудника.
Для завершения добавлений в базу данных необходимо нажать клавишу «Закрыть». Произойдет выход из режима «Форма», а также добавление сотрудников в базу данных.
Обращаем ваше внимание на то, что сотрудники добавляются не по алфавиту, а в конце списка.
В результате наша база данных приобретет вид:
3. Теперь рассмотрим сортировку базы данных.
Сортировать базу данных в Excel можно по столбцу любого типа в порядке возрастания или убывания. Допускается задание от одного до трех критериев сортировки.
Установите курсор в любую ячейку базы данных.
В меню «Данные» выберите пункт «Сортировка...»:
Получим диалоговое окно вида:
Теперь заполним поле «Сортировать по». Для начала отсортируем нашу базу данных по «Фамилиям» в порядке возрастания, получим:
Обратите внимание, что заданная нами формула для «№» позволяет сохранять правильную нумерацию независимо от сортировки.
Теперь отсортируем нашу базу данных по двум столбцам. Сначала отсортируем по «Должности», а потом по «Коду». Для этого в диалоговом окне сортировки наберем:
В результате получим:
|
|
|
В результате такой сортировки на первые позиции встали все «Координаторы», причем между собой они отсортированы по «Коду». В конце у нас «Менеджеры», которые тоже отсортированы между собой по «Коду».
4. Фильтрация базы данных предусмотрена для того, чтобы быстро извлекать из документа записи, которые соответствуют указанным критериям, а затем переносить эту информацию в другие части листа или применять в отчетах.
Для извлечения информации из базы данных по заданному критерию установите курсор в любую ячейку базы данных и выполните:
\ Данные \ Фильтр \ Автофильтр
В результате вы получите:
Обратите внимание, что в строке заголовка появились кнопки со стрелками.
Теперь с помощью фильтра оставим всех «Менеджеров» с «Кодом» из диапазона от 3755 до 3760. Для этого нажмем кнопку со стрелочкой в столбце «Должность» и выберем «Менеджер»:
Далее нажмем кнопку со стрелочкой в столбце «Код» и выберем «Условие», в результате получим диалоговое окно вида:
Заполнив это окно так, как показано на рисунке, получим:
Обратите внимание на то, что вы не удаляли ничего, а просто выбрали лишь те элементы, которые вам необходимы. Стрелочки в столбцах, по которым задавались критерии для фильтрации, выделены цветом, так что если вам необходимо отменить данную фильтрацию, достаточно у выделенных стрелочек выбрать раздел «Все».
Если вы хотите снять фильтр, то выберите:
\ Данные \ Фильтр \ Автофильтр
Примечание. Для того чтобы не только извлечь, но и переместить данные, выполните команду: \Данные\ Фильтр\Расширенный фильтр ... В открывшемся диалоговом окне включите опцию «Скопировать результат в другое место» и укажите, куда копировать результат.
5. Вы можете подводить промежуточный и окончательный итоги, анализируя любой список базы данных, содержащий числовую информацию. Для получения промежуточных итогов весь список должен быть разбит на отдельные группы записей. Чтобы программа Excel распознала эти группы, список следует отсортировать.
Для того чтобы изучить эту возможность, добавим в нашу базу данных еще один столбец «Зарплата» и отсортируем ее по «Фамилиям», получим:
Установим курсор в любую ячейку базы данных и выполним:
\ Данные\Итоги...
получим диалоговое окно вида:
Укажем подводить промежуточные итоги при каждом изменении «Должности», операция «Сумма», а итоги подводить только по «Зарплате» и нажмем кнопку «ОК», получим:
Теперь отменим итоги, для чего выполним \ Данные \ Итоги... и в появившемся диалоговом окне нажмем кнопку «Убрать все». Отсортируем базу данных по «Должности» и вновь установим итоги по тому же принципу, что и ранее, получим:
Для того чтобы вывести только промежуточные итоги, необходимо щелкнуть по кнопке со знаком «-» (
), которая расположена в левой части окна. В результате получим:
Оставшийся минус в левой части окна позволяет вам увидеть только итоговый результат.
