Базы данных в MS Excel
Если курсор находится на каком-то элементе списка, то MS Excel воспринимает этот список как базу данных (БД). Первая строка списка воспринимается как заголовки полей, а строки – как конкретные записи. Одновременно автоматически определяются максимальные размеры БД по вертикали и горизонтали даже в том случае, если в списке имеются пропуски.
Для работы с БД используется пункт «Данные» главного меню. Рассмотрим операции, связанные с отбором данных и итоговыми вычислениями.
На сетевом диске находится учебная база данных «Кадры.xls».
Скопируйте этот файл в свою рабочую папку и откройте его.
КАЖДОЕ ИЗ ПОСЛЕДУЮЩИХ ЗАДАНИЙ НЕОБХОДИМО
ВЫПОЛНЯТЬ НА ОТДЕЛЬНОМ ЛИСТЕ!!!
Сортировка
Общие сведения
Стандартная сортировка не вызывает затруднений. Как правило, у большинства пользователей достаточно навыков, чтобы самостоятельно и без помощи преподавателя произвести ранговую сортировку по возрастанию или убыванию сразу по нескольким полям.
Но в практике делопроизводства не редки случаи, когда необходима сортировка в порядке, отличном от алфавитного.
Организацию собственного порядка сортировки рассмотрим на следующем примере.
Пусть нам необходимо отсортировать список работников по месту работы в следующем порядке:
– сначала должны идти работники планового отдела, затем отдела сбыта, далее отдела снабжения и, наконец, работники производственного отдела.
Выполнение поставленной задачи состоит из следующих этапов:
1. Создается собственный список сортировки:
Сервис > Параметры > Списки > В появившемся окне в поле «Элементы списка» через запятую напечатать нужных список (в данном случае: Плановый, Сбыта, Снабжения, Производственный > Щелкнуть кнопку «Добавить» > Ok.
Обратите внимание: элементы списка печатаются именно в том виде, в котором они присутствуют в списке – с большой буквы и соответствующем падеже.
2. Выполнение сортировки:
Данные > Сортировка > В качестве поля сортировки установить «Отдел» > Параметры > В раскрывающемся списке выбрать нужный список > Ok > Ok.
Варианты заданий
Имеется база данных «Кадры». Отсортировать ее в следующем порядке
1. По отделам: сначала - плановый, затем - сбыта, далее - производственный и, наконец, снабжения.
2. По количеству детей: сначала работники с одним ребенком, затем с двумя детьми и, наконец, работники у которых нет детей.
3. По месту проживания: сначала работники, проживающие по ул. Лебедева, затем по ул. Хевешская, далее по ул. Мира и т. д.
4. По именам: сначала Владимиры, затем Алексеи и далее все остальные.
5. По фамилиям: сначала Ивановы, затем Петровы и далее все остальные.
6. По количеству детей: сначала работники с двумя детьми, затем без детей и, наконец, работники у которых один ребенок.
7. По отделам: сначала - сбыта, затем - плановый, далее - снабжения и, наконец, производственный.
8. По отделам: сначала - снабжения, затем - сбыта, далее - производственный и, наконец, плановый.
9. По количеству детей: сначала работники с одним ребенком, затем с двумя и, далее, работники у которых детей нет.
10. По фамилиям: сначала Алексеевы, затем Петровы, далее Ивановы и, наконец, все остальные.
11. По месту проживания: сначала работники, проживающие по ул. Мира, затем по ул. Хевешская, далее ул. Лебедева и потом все остальные.
12. По именам: сначала Ольга, Вера, Елена и далее все остальные.
13. По именам: Иван, Олег, Петр и далее все остальные.
Фильтрация данных
Общие сведения
Для фильтрации (отсеивания) данных имеется два средства – «Автофильтр» и «Расширенный фильтр». Оба они вызываются посредством:
Данные > Фильтр.
Использование средства «Автофильтр» не вызывает трудностей. С помощью появившихся флажков можно по каждому полю установить критерий отбора на конкретное значение или произвести отбор по условию.
Средство «Расширенный фильтр» более мощное и позволяет производить отбор записей по комплексным условиям.
Пример 1.
Имеется база данных «Кадры». Необходимо получить сведения о работниках планового и производственного отделов, имеющих зарплату меньше 10000 рублей.
Более формально условие фильтрации записей в поставленной задаче можно записать следующим образом:
Отдел = «Плановый» И Оклад <10000
ИЛИ (1)
Отдел = «Производственный» И Оклад < 10000.
Чтобы использовать это условие для фильтрации его необходимо ввести в Excel. Вводить можно на тот же самый или на другой рабочий лист. Если условие вводится на тот же лист, то его обычно записывают над данными.
Условия записываются по следующим правилам:
– в качестве первой строки выписываются точные названия полей, для которых задаются условия;
– во второй (и последующих) строках записываются условия отбора;
– если условия отбора записаны в разных строках, то они объединяются по правилу «ИЛИ»;
– если условия записаны в одной строке, то они объединяются по правилу «И».
Пусть условия (1) размещены следующим образом:
| A | B | C | D |
| Отдел | Оклад | ||
| Плановый | <10000 | ||
| Производственный | <10000 | ||
Для выполнения фильтрации выполняются операции:
Курсор устанавливается в любое место списка данных > Данные > Фильтр > Расширенный фильтр > В появившемся окне поле «Исходный диапазон» будет уже заполнено адресом базы данных > В поле «Диапазон условий» указать $A$1:$C$3 > Ok.
Для того чтобы убрать результаты фильтрации:
Данные > Фильтр > Отобразить все
Пример 2.
Имеется база данных «Кадры». Необходимо получить сведения о работниках планового отдела, имеющих зарплату более 10000 и менее 15000 рублей.
Формально условие фильтрации записывается следующим образом:
Отдел = «Плановый» И Оклад >10000 И Оклад <15000 (2)
Оформить и разместить условие (2) в Excel можно следующим образом:
| F | G | H | |
| Отдел | Оклад | Оклад | |
| Плановый | >10000 | <15000 | |
При таком размещении во время фильтрации в поле «Диапазон условий» следует указать $F$1:$H$2.
Пример 3.
Имеется база данных «Кадры». Необходимо получить сведения о фамилиях всех работников организации.
Критерий фильтрации будет иметь вид названия поля и пустой ячейки под ним:
| F | |
| Фамилия | |
| Фамилия |
Кроме того, в отдельной ячейке для вывода результаты фильтрации необходимо указать название поля – в таблице это ячейка F9.
При таком размещении данных:
Д анные > Фильтр > Расширенный фильтр > В поле «Диапазон условий» указать $F$6:$F$7 > Установить флажок «Только уникальные записи» > Установить переключатель «Скопировать результат в другое место» > В поле «Поместить результат в диапазон» указать $F$9 > Ok.
Варианты заданий
Имеется база данных «Кадры».
С помощью средства «Расширенный фильтр» получить:
1. Список сотрудников планового и производственного отделов, имеющих хотя бы одного ребенка.
2. Список сотрудников отдела сбыта, проживающих по ул. Хевешская и Мира.
3. Список сотрудников с фамилиями «Иванов» и «Петров» и окладом в пределах от 7500 до 15000 руб.
4. Список всех пенсионеров с окладом менее 10000 рублей (учесть, что женщины являются пенсионерами с 55 лет, а мужчины с 60).
5. Список сотрудников всех отделов с окладом от 10000 до 17000 рублей.
6. Список всех имен сотрудников.
7. Получить список улиц, на которых проживают сотрудники организации.
8. Получить список фамилий сотрудников производственного отдела.
9. Определить улицы, на которых проживают работники отдела сбыта
10. Получить список всех пенсионеров, проживающих на ул. Водопроводная. Учесть, что женщины являются пенсионерами с 55 лет, а мужчины с 60.
11.Список женщин планового и производственного отделов с двумя детьми.
12. Список мужских имен
3.3. Средство «Итоги»
Общие сведения
Очень часто для списков необходимо произвести итоговые вычисления. Их можно выполнить с помощью стандартных функций типа СУММА, СРЕДЗНАЧ и т.д. Но в случае списков намного удобнее использовать средства «Итоги» и «Сводные таблицы».
Пример.
Имеется база данных «Кадры». Рассчитать фонд заработной платы по отделам.
Внимание!! Очень важно!!
Перед использованием средства «Итоги» необходимо предварительно отсортировать записи по полю группировки данных.
В рассматриваемом примере, таким полем будет являться поле «Отдел».
Непосредственно для расчетов:
Данные > Итоги > В появившемся окне в поле «При каждом изменении в» указать поле группировки (в данном случае «Отдел») > В поле «Операция» выбрать вид расчета (в данном случае – Сумма) > В поле «Добавить итоги по» поставить галочку на вычисляемое поле (в данном случае – Оклад) и снять галочки с остальных полей > Ok.
Система вернется в Excel. При этом в левой части таблицы появятся символы структуры сгруппированных данных. Это прежде всего уровни структуры (цифры 1, 2, 3 сверху) и флажки раскрытия/закрытия записей (+ или –). С их помощью можно скрыть лишние в данный момент записи и оставить только интересующие нас строки с итоговыми вычислениями.
В рассматриваемом случае можно оставить только четыре записи:
| Отдел | Оклад |
| Плановый Итог | |
| Производственный Итог | |
| Сбыта Итог | |
| Снабжения Итог |
Для того чтобы вернуть список в исходное состояние:
Данные > Итоги > Убрать все
Варианты заданий
Дана база данных кадры. С помощью средства «Итоги» рассчитать:
Вариант
а) Количество детей по отделам.
б) Количество сотрудников в отделах
Вариант
а) Среднее количество детей в каждом отделе.
б) Максимальную заработную плату по отделам.
Вариант
а) Количество детей по улицам.
б) Минимальную заработную плату по отделам.
Вариант
а) Среднее количество детей на каждой улице.
б) Фонд заработной платы по отделам.
Вариант
а) Количество детей для мужчин и женщин.
б) Среднюю заработную плату по улицам.
Вариант
а) Среднее количество детей для мужчин и женщин.
б) Суммарную заработную плату по улицам.
Вариант
а) Средний возраст сотрудников в отделах.
б) Максимальную заработную плату по улицам.
Вариант
а) Возраст самого старого сотрудника каждого отдела.
б) Минимальную заработную плату по улицам
Вариант
а) Возраст самого молодого сотрудника каждого отдела.
б) Среднюю заработную плату мужчин и женщин.
Вариант
а) Максимальный возраст мужчин и женщин.
б) Максимальную заработную плату мужчин и женщин.
Вариант
а) Минимальный возраст мужчин и женщин.
б) Минимальную заработную плату мужчин и женщин.
Вариант
а) Средний возраст мужчин и женщин.
б) Количество работников по улицам.
Вариант
а) Возраст самого старого сотрудника на каждой улице.
б) Количество детей по отделам.
Вариант
а) Возраст самого молодого сотрудника на каждой улице.
б) Суммарную заработную плату по улицам.
Вариант
а) Средний возраст сотрудников по улицам.
б) Фонд заработной платы по отделам.
Сводные таблицы
Общие сведения
Данное средство также позволяет производить итоговые вычисления, но по более сложным критериям группировки. Кроме того, здесь не обязательна предварительная сортировка.
Пример.
Имеется база данных «Кадры». Рассчитать среднюю заработную платы у мужчин и женщин в каждом отделе.
Для решения задачи выполняются операции:
Данные > Сводная таблица > Будет вызван мастер сводных таблиц > В мастере щелкнуть: «Далее» > «Далее» > «Готово» > Появится пустая заготовка сводной таблицы (см. рис. 3.1) > В эту заготовку из списка полей перетащить нужные данные > С помощью кнопки «Параметры поля» выбрать вид выполняемой операции (в данном случае – Среднее) > Выделить получившуюся таблицу и (с помощью команд «Формат» > «Ячейки») установить числовой формат с двумя знаками после запятой.
Рис. 3.1. Порядок формирования сводной таблицы
В результате должно получиться примерно следующее:
| Среднее по полю Оклад | Пол | ||
| Отдел | ж | м | Общий итог |
| Плановый | 14406,25 | 11500,00 | 12953,13 |
| Производственный | 10772,73 | 13444,44 | 11975,00 |
| Сбыта | 13692,31 | 11000,00 | 12346,15 |
| Снабжения | 11000,00 | 13600,00 | 12181,82 |
| Общий итог | 12673,08 | 12166,67 | 12430,00 |
Варианты заданий
Дана база данных кадры. С помощью средства «Сводные таблицы» рассчитать:
Вариант
а) Количество детей у мужчин и женщин по отделам.
б) Среднюю заработную плату у мужчин и женщин.
Вариант
а) Среднее количество детей по отделам у мужчин и женщин.
б) Суммарную заработную плату у мужчин и женщин.
Вариант
а) Максимальное и минимальное количество детей по отделам у мужчин и женщин.
б) Максимальную заработную плату у мужчин и женщин.
Вариант
а) Среднее количество детей для мужчин и женщин на каждой улице.
б) Минимальную заработную плату у мужчин и женщин.
Вариант
а) Общее количество детей для мужчин и женщин на каждой улице.
б) Среднюю заработную плату по улицам у мужчин и женщин.
Вариант
а) Средний возраст у мужчин и женщин в отделах.
б) Максимальную заработную плату по улицам у мужчин и женщин.
Вариант
а) Средний возраст мужчин и женщин по улицам.
б) Минимальную заработную плату по улицам у мужчин и женщин.
Вариант
а) Найти самых молодых мужчину и женщину на каждой улице.
б) Общую сумму заработной платы по улицам у мужчин и женщин.
Вариант
а) Найти самых молодых мужчину и женщину в каждом отделе.
б) Общий фонд заработной платы по улицам в каждом отделе.
Вариант
а) Самого старого мужчину и женщину на каждой улице.
б) Суммарную заработную плату у мужчин и женщин по отделам.
Вариант
а) Самого старого мужчину и женщину в каждом отделе.
б) Максимальную заработную плату у мужчин и женщин по отделам.
Вариант
а) Средний возраст сотрудников по улицам в каждом отделе.
б) Среднюю заработную плату по улицам у мужчин и женщин.
Вариант
а) Количество работников по улицам в каждом отделе.
б) Суммарную заработную плату по улицам в каждом отделе.
Вариант
а) Количество мужчин и женщин в каждом отделе.
б) Минимальную заработную плату у мужчин и женщин по отделам.
Вариант
а) Количество мужчин и женщин на каждой улице.
б) Общий фонд заработной платы по отделам.
Функции для работы с базами данных
Общие сведения
Библиотека Excel содержит тринадцать встроенных функций, позволяющих получить информацию из БД или произвести над ней необходимые вычисления. Все они находятся в категории Работа с базой данных.
Назначение функций очевидно из их названий. Например:
БДСУММ (База_данных; Поле; Критерий поиска);
БСЧЕТ (База_данных; Поле; Критерий поиска);
ДМИН (База_данных; Поле; Критерий поиска);
ДМАКС (База_данных; Поле; Критерий поиска);
ДСРЗНАЧ (База_данных; Поле; Критерий поиска).
Все функции имеют один и тот же формат:
– первый параметр представляет собой ссылку на диапазон ячеек, в котором расположены данные;
– второй параметр - ссылку на адрес, имя или содержимое ячейки с названием столбца в списке, к данным которого применяется данная функция;
– третий параметр представляет собой ссылку на критерии поиска.
Расчетные формулы, содержащие функции баз данных необходимо вводить в ячейки на той области рабочего листа, которая не будет в дальнейшем мешать дополнению и расширению списка.
Для удобства работы с функциями баз данных следует заранее присвоить имена диапазонам ячеек, содержащим данные списка (включая заглавную строку) и область критериев.
Порядок присвоения имен:
1. С помощью мыши выделить все ячейки, содержащие базу данных.
2. В строке формул в ячейку адреса текущей ячейки ввести имя базы данных (рис. 3.2):
Рис. 3.2. Порядок присвоения имени БД
Пример 1.
Имеется база данных «Кадры». Рассчитать среднюю заработную плату работников отдела снабжения.
Для решения в произвольном месте рабочего листа записывается условие отбора записей для расчетов:
| M | N | O | |
| Отдел | |||
| Снабжения | |||
| 12181,81 |
А в ячейку N13 ввести формулу:
=ДСРЗНАЧ(Данные;G5;N10:N11),
где G5 – адрес заголовка «Оклад»;
N10:N11 – адрес критерия фильтрации.
Пример 2.
Имеется база данных «Кадры». Определить количество пенсионеров, работающих в организации.
При решении задач, связанных возрастом, рекомендуется создать поле «Возраст». Для этого в ячейку L5 ввести название поля, т.е. – «Возраст», а в ячейку L6 ввести формулу: =2009-H6, которая затем копируется на весь столбец L.
Непосредственно для решения в свободном месте листа вводится условие фильтрации:
| M | N | O | P | |
| Пол | Возраст | |||
| м | >=60 | |||
| ж | >=55 | |||
А в ячейку N20 ввести формулу:
=БСЧЁТ(Данные;;N16:O18)
Примечание. Для функции БСЧЕТ в качестве заголовка поля можно указывать любое поле или даже просто не вводить его.
Варианты заданий
Дана база данных «Кадры». С функций работы с базами данных рассчитать:
Вариант
а) Общее количество мужчин в плановом и производственном отделах.
б) Количество работников планового отдела, проживающих на улице Хевешская и по проспекту Мира.
Вариант
а) Среднюю заработную плату женщин не пенсионеров.
б) Средний возраст мужчин с именами Алексей и Андрей.
Вариант
а) Средний возраст женщин с именами Ольга и Мария.
б) Количество детей у мужчин в плановом и производственных отделах.
Вариант
а) Среднюю заработную плату у пенсионеров мужчин.
б) Среднее количество детей в организации, приходящееся на одного работника.
Вариант
а) Суммарную заработную плату у мужчин.
б) Максимальное количество детей у мужчин с именами Олег и Сергей.
Вариант
а) Среднее количество детей у женщин, проживающих на ул. Водопроводная.
б) Максимальную заработную плату у мужчин в отделе сбыта.
Вариант
а) Общее количество детей у мужчин, проживающих на ул. Горького.
б) Минимальную заработную плату у женщин в производственном отделе.
Вариант
а) Среднюю заработную плату у женщин с двумя детьми.
б) Средний возраст у мужчин в производственном отделе.
Вариант
а) Максимальную заработную плату у мужчин в отделе сбыта.
б) Минимальный возраст у женщин в плановом отделе.
Вариант
а) Минимальную заработную плату мужчин без детей.
б) Самого молодого мужчину на ул. Лебедева.
Вариант
а) Общую сумму заработной платы в плановом отделе.
б) Самую старшую женщину в отделе сбыта.
Вариант
а) Общий фонд заработной платы для работников с одним ребенком.
б) Самого старого мужчину на ул. Володарского.
Вариант
а) Суммарную заработную плату у мужчин пенсионеров в производственном отделе.
б) Количество мужчин, у которых нет детей.
Вариант
а) Максимальную заработную плату у женщин пенсионеров.
б) Средний возраст женщин на ул. Яковлева.
Вариант
а) Среднюю заработную плату у мужчин без детей.
б) Количество работников с двумя детьми.
Консолидация данных
Общие сведения
Средство «Консолидация» представляет собой еще одну возможность для выполнения итоговых вычислений. С его помощью можно обобщить данные, расположенные на разных листах, или в разных местах одного листа. Единственное требование к консолидируемым данным – они должны иметь одинаковую структуру.
Недостатком метода является то, что консолидация возможна только по параметрам первого столбца данных.
Пример.
Дана база данных «Кадры». Определить средний оклад в производственном и плановом отделах.
Решение задачи состоит из следующих этапов.
а) Столбец, по которому выполняется консолидация, переставляется на первое место в исходной таблице. В нашем примере это столбец «Отдел».
б) Подготавливается шаблон для вывода результатов консолидации. В него включаются нужные столбцы и строки из исходной базы данных. Для рассматриваемого примера он будет иметь вид:
| N | O | P | ||
| Отдел | Оклад | |||
| Производственный | ||||
| Плановый | ||||
Примечание. Шаблон может быть размещен в произвольном месте листа или на другом листе. Главное требование к нему – это отсутствие конфликта с уже имеющимися данными.
в) Подготовленный шаблон выделяется (включая заголовки) и затем выполняются команды: Данные > Консолидация.
г) В появившемся окне «Консолидация» (рис. 3.3) необходимо:
Рис. 3.3. Окно Консолидация
– выбрать вид вычисления (в данном примере - функция «Среднее»);
– сформировать ссылку на базу данных. Для этого находясь в поле «Ссылка» обвести мышью базу данных и затем щелкнуть по кнопке «Добавить»;
– поставить галочки на переключатели «Подписи верхней строки» и «Значения левого столбца»;
– щелкнуть «Ok».
Должны появиться следующие результаты:
| N | O | P | ||
| Отдел | Оклад | |||
| Производственный | ||||
| Плановый | 12953,13 | |||
Варианты заданий
Имеется база данных «Кадры». С помощью средства консолидация определить:
Вариант 1
а) Количество работников, проживающих на ул. Хевешская, Мира и Горького.
б) Суммарную и среднюю заработную плату работников, проживающих на тех же улицах.
Вариант 2
а) Количество детей у работников планового и производственного отделов.
б) Суммарную и среднюю заработную плату у тех же работников.
Вариант 3
а) Суммарную и среднюю заработную плату у работников, имеющих детей.
б) Количество работников, имеющих детей.
Вариант 4
а) Количество работников с именами Иван, Петр и Алексей.
б) Суммарную и среднюю заработную плату тех же работников.
Вариант 5
а) Количество работников с именами Елена, Ольга и Людмила.
б) Суммарную и среднюю заработную плату работников тех же работников.
Вариант 6
а) Количество мужчин, работающих в организации.
б) Суммарную и среднюю заработную плату мужчин
Вариант 7
а) Количество женщин, работающих в организации.
б) Суммарную и среднюю заработную плату у женщин
Вариант 8
а) Количество работников, проживающих на ул. Хевешская, Мира и Горького.
б) Суммарную и среднюю заработную плату работников, проживающих на тех же улицах.
Вариант 9
а) Количество детей у работников планового и производственного отделов.
б) Суммарную и среднюю заработную плату у тех же работников.
Вариант 10
а) Суммарную и среднюю заработную плату у работников, имеющих детей.
б) Количество работников, имеющих детей.
Вариант 11
а) Количество работников с именами Иван, Петр и Алексей.
б) Суммарную и среднюю заработную плату тех же работников.
Вариант 12
а) Количество работников с именами Елена, Ольга и Людмила.
б) Суммарную и среднюю заработную плату работников тех же работников.
Вариант 13
а) Количество мужчин, работающих в организации.
б) Суммарную и среднюю заработную плату мужчин
Вариант 14
а) Количество женщин, работающих в организации.
б) Суммарную и среднюю заработную плату у женщин
