Глава 3. Ведение баз данных средствами электронных таблиц Excel

 

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

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

 

3.1. Создание списков

 

Примером работы со списками может послужить создание небольшой базы данных  А1: I40 о работниках фирмы. Название столбцов таблицы указаны в таблице 1.

Таблица 1.

Наименования полей базы данных о работниках фирмы

Столбец

Наименование поля

Пояснения

A

Фамилия

Фамилия работника

B

Имя

Имя работника

C

Отчество

Отчество работника

D

Пол

Пол работника

E

Дата рождения

Дата рождения работника

F

Должность

Должность, занимаемая работником

G

Оклад

Оклад работника в рублях

H

Семейное положение

Семейное положение работника

I

Количество детей

Количество детей в семье  работника

 

Примечание.
Пол кодируется буквами м или ж.

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

 

3.2. Создание формы

 

Для упрощенного ввода данных в таблицу применяются специальные формы. Для этого достаточно иметь строку заголовка таблицы и выполнить команду Данные\Форма (рис.1.11).

 

 

Рис.1.11. Диалоговое окно формы

 

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

 

3.3. Сортировка списка

 

Данные, получаемые в результате выполнения запроса, должны быть каким-либо образом отсортированы. Типичные примеры сор­тировки — это упорядочение фамилий сотрудников в алфавитном порядке, товаров в соответствии с ценой и т. д.

Мастер запросов  вызывается командой Данные\Сортировка, в результате чего откроется диалоговое окно Сортировка диапазона (рис.1.12).

В среде Excel 2000 предусмотрена трехуровневая сортировка, которая определяется в диалоговом окне Сортировка диапазона.

Рис.1.12.  Диалоговое окно для операции сортировки

Для определения порядка сортировки  необходимо выполнить следующие действия:

1.    В списке Сортировать по выберите первое поле, по которому следует произвести сортировку.

2.    Остановите переключатель по возрастанию или по убыванию, который по­зволяет определить тип порядка сортировки значений выбранного поля.

3.    При необходимости произвести сортировку по следующему полю повто­рите шаги 1, 2 и то же самое для всех последующих полей, подлежащих сортировке.

4.    Нажмите кнопку ОК.

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

 

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

 

Для этого:

1.     Выделите диапазон ячеек, занимаемых исходной базой данных, перетаскиванием мышью (с предварительным позиционированием ячейки, содержащей имя поля Фамилия).

2.     Выберите пункт горизонтального меню Данные/Сортировка...

3.     Заполните диалоговое окно Сортировка диапазона согласно рисунка 1.13.

 


Рис.1.13. Диалоговое окно Сортировка диапазона

4. Ниже на рисунке 1.14  приведен начальный фрагмент БД после сортировки.


Рис.1.14. Результат двухуровневой сортировки

5.    Отменить результаты сортировки, можно щелчком мыши по соответствующей кнопке  и  на стандартной панели инструментов.

 

3.4. Фильтрация данных

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

Фильтрация данных - это выбор данных по заданному критерию. Результатом фильтрации является временное скрытие записей, не удовлетворяющих критериям. Критерии для фильтрации записей списка бывают:

F   критерий на основе сравнения;

F   вычисляемый критерий.

В Excel есть три средства для поиска и фильтрации данных: форма данных, Автофильтр и Расширенный фильтр. Как Автофильтр, так и Расширенный фильтр скрывают строки рабочего листа целиком. Если около боковых границ списка имеются другие данные, они могут оказаться спрятанными вместе с записями списка. Нумерация записей отображается голубым цветом для индикации того, что на листе выведено только подмножество списка. Стрелки кнопок раскрывающихся списков тех полей, которые задействованы в критерии, также меняют цвет на голубой.

 

3.5. Автофильтр

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

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

F   все — отображать все значения (фильтр не применяется);

F   первые 10 - отображается только 10 первых значе­ний в столбце;

F   значения – будут выбраны только те записи, которые в данном столбце содержат указанное значение;

F   условие - создание собственных правил для фильтрации.

Остальные пункты меню перечисляют все элементы данного столбца.

Можно исключить любое поле из критерия. Для этого раскройте соответствующий список и выберите команду Все. Чтобы снять критерии целиком, выберите команду Данные\ Фильтр\ Отобразить все. Чтобы отменить режим Автофильтра, выберите команду Данные\ Фильтр\ Автофильтр.

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

Каждая часть условия может включать различные  операции сравнения (табл. 2). 

Таблица. 2.

Операции сравнения

Операции

Смысл

Пример

Результат поиска

=

=

> 

>=

 

< 

<=

 

<> 

Равно

Равно

Больше

Больше или равно

 

Меньше

Меньше или равно

 

Не равно

=200

=200

>200

>=200

 

<200

<=200

 

<>200

Поля со значениями, равными 200

Пустые поля

Поля со значениями больше 200

Поля со значениями больше или равно 200

Поля со значениями меньше 200

Поля со значениями меньше или равно 200

Поля со значениями, не равными 200

 

Используя операцию автофильтра, провести выборку записей из БД согласно критерию - фамилии, состоящие из трех или четырех букв.

 

Для этого:

1.         Выделите диапазон ячеек, занимаемый исходной базой данных.

2.         Выберите пункт горизонтального меню Данные \ Фильтр \ Автофильтр.  (Ниже на рис. 1.15 приведен начальный фрагмент БД после включения автофильтра с преобразованием всех наименований полей в раскрывающиеся списки).


Рис.1.15. Фрагмент базы данных

 

3.    Выберите в раскрывающемся списке поля Фамилия позиции (Условие...)

4.    Заполните диалоговое окно Пользовательский автофильтр согласно рисунка 1.16.


Рис.1.16. Диалоговое окно Автофильтра

 

5.    Отменить результаты фильтрации посредством выбора в раскрывающемся списке поля Фамилия позиции (Все).

Используя многошаговую операцию автофильтра, провести выборку записей из БД согласно критериям - женщины, имеющие трех и более детей.

 

1.    Выберите в раскрывающемся списке поля Пол позиции ж. При этом используется автофильтр.

2.     Выберите в раскрывающемся списке поля Количество детей позиции (Условие...)

3.    Заполните диалоговое окно Пользовательский автофильтр согласно рисунка 1.17.

.
Рис.1.17. Диалоговое окно Автофильтра

 

4.    Отмените результат фильтрации повторным выбором пунктов меню Данные\Фильтр\Автофильтр.

 

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

 

Расширенный фильтр требует большей работы, чем Автофильтр, однако и представляемые возможности поиска и фильтрации шире. Можно более свободно применять операции И/ИЛИ, а также составлять вычисляемые критерии.

Для использования Расширенного фильтра необходимо создать диапазон критериев, где задаются условия поиска данных. Верхняя строка диапазона должна содержать заголовки полей, которые в точности повторяют по написанию заголовки полей в области данных. Проще всего можно обеспечить точность совпадения путем копирования (команды Правка\ Копировать и Правка\ Вставить). Не требуется включать все имеющиеся заголовки и сохранять их порядок. Кроме того, диапазон критериев должен включать хотя бы одну пустую строку ячеек сразу под заголовками. В этой строке или строках записываются условия, причем все, что записано в столбце под заголовком поля, отно­сится именно к этому полю. Для ввода условий можно использовать не­сколько строк. Диапазон критериев может содержать константы, шаблоны, простые сравнения и формулы.

Критерий сравнения в расширенном фильтре формируется при соблюдении следующих требований:

F   состав столбцов области критериев определяется столбцами, по которым задаются условия фильтрации записей;

F   имена столбцов области критериев должны точно совпадать с именами столбцов исходного списка;

F   ниже имен столбцов располагаются критерии сравнения типа:

F   точного значения;

F   значения, формируемого с помощью операторов отношения;

F   шаблона значения, включающего символа * и (или) ?.

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

Правила формирования множественного критерия:

1.    Если критерии указываются в каждом столбце на одной строке, то они считаются связанными условием И.

2.    если условия записаны в нескольких строках, то они считаются связанными условием ИЛИ.

Для фильтрации списка с помощью расширенного фильтра необходимо:

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

2.       Выделите ячейку внутри списка или выделите список целиком, если соприкасается с другими данными.

3.        Выберите команду Данные\ Фильтр\ Расширенный фильтр. Появится диалоговое окно Расширенный фильтр ( рис.1.18).

Рис.1.18. Диалоговое окно Расширенного фильтра

 

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

5.       Введите ссылку или имя диапазона списка в поле Исходный диапазон.

6.       Введите ссылку или имя диапазона критериев в поле Диапазон критериев.

7.        Если выбрано положение скопировать результат в другое место, введите ссылку пли имя диапазона назначения в поле Поместить  результат в диапазон.

8.       Установите флажок Только уникальные записи, если не хотите, чтобы одинаковые записи повторялись (будет вы

политься только первая из всех удовлетворяющих критерию одинаковых записей).

9.       Нажмите ОК.

 

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

 

1.    Создайте диапазон ячеек для размещения блока критериев посредством вставки четырех строк над таблицей исходной БД.

2.    Скопируйте строки с наименованиями полей БД в первую строку блока критериев.

3.    Внесите во вторую строку блока условий выборки записей, как это изображено на рисунке 1.19.


Рис.1.19. Фрагмент условия выборки записей

 

4.    Выделение диапазона ячеек исходной БД.

5.    Выбор в инструментальном меню пунктов Данные\Фильтр\Расширенный фильтр...

6.    Заполните диалоговое окна Расширенный фильтр

7.    С целью подготовки к выполнению следующего задания - отмена результатов фильтрации посредством выбора в инструментальном меню пунктов Данные\Фильтр\Отобразить все.

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

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

Вычисляемые критерии используются при поиске записей по датам, попадающим в определенный интервал, по почтовому индексу, кото­рый входит в состав поля адреса, по некоторой величине, получаемой на основе значений одного или нескольких полей записи, по подсчету количества данных,  встречающихся в БД по заданному условию.

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

Критерий может быть составлен с помощью логических функций И, ИЛИ, НЕ. К этим функциям имеет смысл прибегать, если сложное условие нельзя или неудобно задавать путем добавления строк в диапазон критериев. Аргументами функций  И, ИЛИ, НЕ  являются логические выражения.

 

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

 

1.    Заполните блок критериев новыми условиями выборки записей, как это показано на рисунке 1.20.


Рис. 1.20. Фрагмент условия выборки записей

 

2.    Введите в ячейку A41 (под таблицей исходной БД) текста пояснения планируемого результата запроса, а в ячейку E41 с помощью Мастера функций - соответствующей расчетной формулы. Выберите в процессе диалога с Мастером функций категории функций Работа с базой данных и имени функции БСЧЁТ, как это показано на рисунке 1.21.


Рис.1.21. Диалоговое окно Мастера функций

 

3.     Заполните диалоговое окно с указанием трех аргументов функции согласно рисунка 1.22.


Рис.1.22. Диалоговое окно Мастера функции –шаг 2

 

4.     В ячейке E41  появится результат запроса к БД.

 

3.7. Сводная таблица

 

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

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

Создание сводной таблицы производится с помощью Мастера сводных таблиц, который вызывается командой Данные \ Сводная таблица или нажатием кнопки Мастер сводных таблиц  . На экране появится диалоговое окно  первого шага из трех  Мастера сводных таблиц и диаграмм. (рис. 1.23)

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

 

Рис. 1.23.  Первый шаг мастера сводных таблиц и диаграмм

 

На втором шаге указывается диапазон исходных данных для создания таблицы (рис.1.24). Если данные были предварительно выделены до запуска мастера, координаты нужного диапазона уже будут введены в поле Диапазон. В противном случае щелкните по кнопке на правой границе поля. Диалоговое окно свернется в узкую полоску, освободив экран. Выделите необходимый диапазон, и опять щелкните по кнопке справа для возвращения к диалоговому окну. Затем опять нажмите кнопку Далее для перехода к следующему шагу.

 

 

Рис.1.24. Второй шаг мастера сводных таблиц и диаграмм

 

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

F        разместить на новом листе;

F        разместить на существующем листе.

По умолчанию предлагается создать таблицу на новом листе. Оставим этот вариант и щелкнем на кнопке Готово для завершения процедуры (рис.1.25).

Рис.1.25. Третий шаг Мастера сводных таблиц и диаграмм

 

Как будет представлена информация, и какими свойствами будет обладать сводная таблица, определяется с помощью кнопок Макет и Параметры соответственно.

При нажатии кнопки Макет, появится диалоговое окно для определения структуры сводной таблицы (рис. 1.26).

 

Рис .1.26. Диалоговое окно Мастер сводных таблиц и диаграмм – макет

 

В центральной части этого окна расположены четыре области: Данные, Строка, Столбец и Страница, а в правой части окна присутствуют названия полей, которые имеются в указанном источнике данных. Конструирование сводной таблицы сводится к перетаскиванию мышкой названий полей в различные области. Области сводной таблицы имеют следующее назначение:

F   области Строка и Столбец предназначены для группировки данных.  Если название какого-то поля помещено в область Строка, то все записи источника группируются так, что каждая группа соответствует одному из значений поля, помещенного в область Строка. В область Строка быть  помещено   несколько   названий,   что  означает  вложенную группировку. Аналогичное назначение имеет область Столбец;

F   область Данные предназначена для выполнения групповых операций над данными. Если название какого-либо поля помешено в область Данные, то ко всем записям источника данных, входящим в группу, соответст­вующую определенным значениям полей из области Строка и Столбец, применяется указанная групповая операция. По умолчанию групповой операцией для числовых данных является суммирование. В область Данные может быть помещено несколько названий, что означает выпол­нение групповой операции над значениями каждого поля;

F   область Страница предназначена для фильтрации данных. Если название какого-либо поля помещено в область Страница, то для дальнейшей обработки из источника данных выбираются только те записи, которые имеют заданное значение в этом поле. Выбор значения осуществляется с помощью раскрывающегося списка,

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

Чтобы подчеркнуть, что в сводной таблице в области Данные будут содер­жаться не значения из источника данных, а результаты групповых операций над этими значениями, Excel автоматически припишет им имена Сумма по полю Сумма_в_счете и Сумма по полю Сумма_оплаты. Изменить эти имена на более понятные, необходимо сделать двойным щелчок на названии поля в области Данные. Появится диалоговое окно, приведенное на рисунке 1.27. В поле ввода Имя укажите новое название и нажмите кнопку ОК.

 

Рис.1.27.  Диалоговое окно Вычисление поля сводной таблицы

 

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

Таким образом, сводные таблицы позволяют производить не только простое суммирование, но и другие довольно сложные дополнительные операции с данными. Дополнительные операции описаны в таблице 3.

 

Таблица 3

Дополнительные вычисления

Название

Назначение

Отличие

Доля

Приведенное отличие

 

С нарастающим итогом в поле

Доля от суммы

по строке

Доля от суммы по столбцу

Доля от общей суммы

Индекс

Разность между результатом и элементом, заданными в спи­сках Поле и Элемент

Результат, деленный на указанный элемент указанного поля, выраженный в процентах

Разность между результатом и указанным элементом указанно­го поля, деленная на этот элемент, выраженная в процентах

Для указанного поля происходит накопление результата, т. ячейку вместо значения помещается сумма значения и величины из предыдущей ячейки (если она есть)

Результат, деленный на общий итог по строке, выраженный в процентах

 

Результат, деленный на общий итог по столбцу, выраженный в процентах

 

Результат, деленный на общий итог по сводной таблице, выраженный в процентах

Значение ячейки вычисляется по формуле:

(Результат) *(Общий итог)/((Итог по строке)*(Итог по столбцу))

 

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

 

1.     Выберите в горизонтальном меню пункт Данные\Сводная таблица...

2.     Реализуйте первый шаг диалога с Мастером сводных таблиц - выберите вариант Создать таблицу на основе данных, находящихся в списке или базе данных Microsoft Excel.

3.     Реализуйте второй шаг диалога с Мастером сводных таблиц - выделите диапазон ячеек, занимаемых БД.

4.     Реализуйте  третий  шаг  диалога  с   Мастером   сводных   таблиц (рис. 1.26):

F   перенесите имя поля Семейное положение в область строк сводной таблицы;

F   перенесите имя поля Пол в область столбцов сводной таблицы;

F   перенесите имя поля Оклад в область данных сводной таблицы;

F   раскройте список вариантов вычислений в области данных двойным щелчком в соответствующем участке области данных и выбор позиции Минимум, как это показано на рисунке 1.28.

 

Рис. 1.28. Диалоговое окно Мастера сводных таблиц

 

5.     Перед завершением третьего шага диалога с Мастером сводных таблиц - пронаблюдайте структуру создаваемой таблицы, как это показано на рис. 1.29.



Рис. 1.29.  Диалоговое окно Мастера сводных таблиц

 

6.     Реализуйте четвертый шаг диалога с Мастером сводных таблиц - выберите вариант  Поместить таблицу в новый лист.

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

 

 

Рис. 1.30. Фрагмент результата построения сводной таблицы

 

Задания для самостоятельного выполнения

 

ЗАДАНИЕ 1. Провести двухуровневую сортировку БД согласно критериям в таблице по вариантам. При этом на первом этапе провести сортировку согласно первичному критерию, а на втором этапе для записей, имеющих одинаковые значения первичного критерия, предусмотреть сортировку согласно вторичному критерию.

 

Таблица 4.Варианты индивидуальных заданий

Номер варианта

Критерии сортировки

Первичный

Вторичный

0

Вначале мужчины, а затем женщины

По убыванию возраста работника

1

По алфавиту наименований должностей

По возрастанию возраста работника

2

Вначале мужчины, а затем женщины

По алфавиту фамилий

3

По алфавиту наименований должностей

По убыванию окладов

4

Вначале мужчины, а затем женщины

По алфавиту наименований должностей

5

Вначале женщины, а затем мужчины

По убыванию количества детей

6

По алфавиту наименований должностей

Вначале женщины, а затем мужчины

7

Вначале женщины, а затем мужчины

По возрастанию окладов

8

Вначале мужчины, а затем женщины

По возрастанию количества детей

9

По алфавиту фамилий

По алфавиту имен

 

 

ЗАДАНИЕ 2. Используя операцию автофильтра, провести выборку записей из БД согласно приведенным в таблице  критериям фильтрации.

Таблица 5.Варианты индивидуальных заданий

Номер варианта

Критерии фильтрации

0

Фамилии, начинающиеся на “Ми” или “Ни”

1

Фамилии, начинающиеся с “Б”, и 3-й буквой “р

2

Не имеющие детей или имеющие более четырех детей

3

Продавцы всех категорий

4

Имеющие имя “Александр” или “Алексей”

5

Вдовцы или вдовы

6

Имеющие отчества “Александрович” или “Александровна”

7

Имеющие оклады от 2500 до 3000 руб.

8

Заведующие или их заместители любых подразделений

9

Холостые мужчины или незамужние женщины

 

ЗАДАНИЕ 3. Используя многошаговую операцию автофильтра, провести выборку записей из БД согласно приведенным в таблице 6 критериям фильтрации.

 

Таблица 6.Варианты индивидуальных заданий

Номер варианта

Критерии фильтрации

0

Мужчины с окладом выше 2000 руб.

1

Продавцы любых категорий с окладом ниже 2000 руб.

2

Женщины кассиры или кассиры-контролеры

3

Вдовы или разведенные женщины, имеющие детей

4

Незамужние или разведенные, не имеющие детей

5

Разведенные, имеющие детей

6

Вдовы и вдовцы с окладом ниже 2500 руб.

7

Незамужние продавцы 1-й и 2-й категорий

8

Продавцы любых категорий с именами Елена или Вера

9

Мужчины-бухгалтеры

 

ЗАДАНИЕ 4. Используя операцию расширенного фильтра, выполнить одношаговую фильтрацию согласно критериям таблицы 6. Соответствующий блок критериев расположить над таблицей БД.

 

ЗАДАНИЕ 5. Реализовать запрос к БД, используя функции категории Работа с базой данных.

Таблица 7.Варианты индивидуальных заданий

Номер варианта

Запрос к базе данных

0

Сумма окладов продавцов любых категорий

1

Количество вдов и вдовцов

2

Максимальный оклад у мужчин

3

Минимальный оклад у женщин

4

Количество женщин-продавцов 1-й категории

5

Средний оклад у заведующих любых подразделений

6

Общее количество детей у разведенных

7

Средний оклад у бухгалтеров

8

Количество холостяков с окладом выше 2500 руб.

9

Максимальное количество детей у вдовцов и вдов

 

ЗАДАНИЕ 6. Реализовать перекрестный запрос к БД, используя операцию построения сводной таблицы.

Таблица 8.Варианты индивидуальных заданий

Номер варианта

Запрос к БД

0 или 5

Количество работников в каждой должности отдельно для женщин и мужчин

1 или 6

Количество детей для различных групп семейного положения отдельно для женщин и мужчин

2 или 7

Средний оклад работников в каждой должности отдельно для женщин и мужчин

3 или 8

Максимальное количество детей для различных групп семейного положения отдельно для женщин и мужчин

4 или 9

Максимальный оклад в каждой должности отдельно женщин и мужчин

 

 

Вернуться к выбору                                      На главную страницу