МЕТОДИЧЕСКИЕ УКАЗАНИЯ ПО ВЫПОЛНЕНИЮ СРСП
1. Задания 1 и 2 выполняются в объеме 8-10 страниц рукописного текста по каждому из заданий. Варианты задания 1, связанные с рассмотрением офисных приложений, в частности, СУБД, рекомендуется излагать применительно к СУБД MS Access. При этом не следует ограничиваться только списком литературы, приведенной в конце методических указаний. Могут быть использованы материалы из популярных журналов компьютерной тематики. Приветствуется поиск необходимой информации и в компьютерной сети Internet.
2. Выполнение заданий 4-9 сводится к составлению сценариев запросов к БД. Такие сценарии должны содержать подробное описание действий пользователя по выделению соответствующих диапазонов ячеек, выбору пунктов инструментального меню, заполнению полей диалоговых окон и прочее (см. примеры выполнения заданий). Подготовленные таким образом материалы должны обеспечить оперативное выполнение заданий на практических занятиях в компьютерных классах кафедры информатики.
3. Приветствуется вариант предварительного выполнения заданий 4-9 на компьютерах вне стен института. В этом случае в контрольную работу могли бы быть также включены распечатки с результатами запросов к учебной БД. Сама БД может быть предоставлена посредством копирования соответствующего файла на дискету студента.
4. В виде исключения допускается первая сдача контрольной работы с выполнением лишь заданий 1-3. В процессе же практических занятий студенты должны тщательно запротоколировать реализацию запросов к БД, после чего оперативно дополнить работу соответствующими сценариями. Однако, следует иметь в виду, что время на такое дополнение может быть ограничено всего лишь несколькими днями.
5. Задания 5-9 предполагают реализацию запросов к БД, связанных с поиском и обработкой данных, которые соответствуют заданным условиям-критериям поиска. Такие запросы в среде табличного процессора MS Excel могут выполняться различными способами:
· с использованием Формы, создаваемой при выборе пунктов инструментального меню Данные/Форма..., в которой следует щелкнуть мышью по кнопке Критерии. Далее надо ввести в соответствующее поле формы искомое значение, а для текстовых значений - хотя бы начальный уникальный фрагмент. Результатом поиска является отображаемая в форме первая от начала БД запись, в которой обнаружено совпадение с введенным искомым значением. Щелчками по кнопкам Следующая или Предыдущая, можно перейти к очередной или предыдущей такой записи;
· посредством использования операции Автофильтра (см. ниже примеры выполнения заданий 5 и 6);
· посредством выполнения операции Расширенного фильтра, который использует формируемый предварительно блок критериев поиска (см. ниже пример выполнения задания 7);
· с использованием функций категории Работа с базой данных, которые применяют механизм расширенной фильтрации с последующей обработкой ее результатов (см. ниже пример выполнения задания 8);
· путем построения сводной таблицы посредством диалога с Мастером сводных таблиц (см. ниже пример выполнения задания 9);
· с использованием пунктов инструментального меню Правка/Найти..., что применительно к таблице БД можно считать наименее эффективным.
6. В заданиях 7 и 8 требуется сформировать блок критериев, заполнив его заданными условиями выборки искомых записей. С этой целью предварительно необходимо зарезервировать диапазон ячеек для размещения блока критериев посредством вставки пустых строк над таблицей БД. Затем следует скопировать строку с наименованиями полей БД в первую строку блока критериев, например, с использованием папки обмена. Далее следует ввести, начиная со второй строки блока критериев, конкретные условия выборки записей. При этом следует иметь в виду, что комбинированный критерий фильтрации формируется из частных критериев в отдельных ячейках блока по правилу: объединение в строке – логической операцией И, в столбце – логической операцией ИЛИ. Полученные таким образом блоки критериев следует представить в контрольной работе в виде соответствующих рисунков.
7. Задание 8 предполагает использование функций категории Работа с базой данных, которые применяют механизм расширенной фильтрации с последующей обработкой ее результатов:
БДСУМ - суммирование значений в указанном столбце;
БСЧЁТ - подсчет числа значений в указанном столбце, который должен содержать не текстовые значения;
ДМАКС - нахождение максимального значения в указанном столбце;
ДМИН - нахождение минимального значения в указанном столбце;
ДСРЗНАЧ - вычисление среднеарифметического значения в указанном столбце.
Все вышеперечисленные функции имеют три аргумента:
· диапазон ячеек, занимаемых исходной БД;
· ячейка с именем поля-столбца, по которому после фильтрации производится суммирование, подсчет числа значений, поиск максимума и прочее;
· диапазон ячеек блока критериев фильтрации.
Следует иметь в виду, что при использовании функции БСЧЁТ в качестве имени поля, по которому производится подсчет числа записей, прошедших фильтрацию, следует указать поле не текстового типа, например, арифметического (см. ниже пример выполнения задания 8).
8. Перекрестный запрос к БД из задания 9 реализуется посредством диалога с Мастером сводных таблиц, состоящего из четырех шагов:
шаг 1 - подтверждение создания таблицы на основе данных, находящихся в списке или базе данных Microsoft Excel;
шаг 2 - выделения диапазона ячеек, занимаемых БД;
шаг 3 - разметка сводной таблицы посредством перетаскивания имен полей в соответствующие области создаваемой таблицы;
шаг 4 - выбор варианта расположения сводной таблицы (см. ниже пример выполнения задания 9).
ПРИМЕРЫ ВЫПОЛНЕНИЯ СРСП
СРСП №4. Провести двухуровневую сортировку БД, используя критерии: первичный - по убыванию количества детей; вторичный - по алфавиту групп семейного положения.
Сценарий сортировки
1. Выделение диапазона ячеек, занимаемого исходной базой данных, перетаскиванием мышью или клавишным аккордом [Shift]+[Ctrl]+[End] (с предварительным позиционированием ячейки, содержащей имя поля Фамилия).
2. Выбор пунктов инструментального меню Данные/Сортировка...
3.
рис. 1
4.
рис. 2
5. С целью подготовки к выполнению следующего задания - отмена результатов сортировки, например, щелчком мышью по соответствующей кнопке на стандартной панели инструментов или с помощью клавишного аккорда [Ctrl]+[z].
СРСП №5 Используя операцию автофильтра, провести выборку записей из БД согласно критерию - фамилии, состоящие из трех или четырех букв.
Сценарий запроса к БД
1. Выделение диапазона ячеек, занимаемого исходной базой данных.
2.
рис. 3
3. Выбор в раскрывающемся списке поля Фамилия позиции (Условие...)
4.
рис. 4
5. Визуальный контроль результатов фильтрации.
6. С целью подготовки к выполнению следующего задания - отмена результатов фильтрации посредством выбора в раскрывающемся списке поля Фамилия позиции (Все).
СРСП №6 Используя многошаговую операцию автофильтра, провести выборку записей из БД согласно критериям - женщины, имеющие трех и более детей.
Сценарий запроса к БД
1. Выбор в раскрывающемся списке поля Пол позиции ж. При этом используется автофильтр, установленный в задании 5.
2. Выбор в раскрывающемся списке поля Количество детей позиции (Условие...)
3.
рис. 5
4. Визуальный контроль результатов фильтрации.
5. С целью подготовки к выполнению следующего задания - отмена результатов фильтрации посредством выбора в инструментальном меню пунктов Данные/Фильтр/Отобразить все и выключение автофильтра повторным выбором пунктов меню Данные/Фильтр/Автофильтр.
СРСП №7. Используя операцию расширенного фильтра, выполнить одношаговую фильтрацию согласно критериям - женщины, имеющие трех и более детей.
Сценарий запроса к БД
1. Резервирование диапазона ячеек для размещения блока критериев посредством вставки четырех строк над таблицей исходной БД.
2. Копирование строки с наименованиями полей БД в первую строку блока критериев, например, с использованием папки обмена.
3. Внесение во вторую строку блока критериев условий выборки записей, как это изображено на рис. 6.
4. Выделение диапазона ячеек исходной БД.
5. Выбор в инструментальном меню пунктов Данные/Фильтр/Расширенный фильтр...
6.
рис. 7
7. Визуальный контроль результатов фильтрации.
8. С целью подготовки к выполнению следующего задания - отмена результатов фильтрации посредством выбора в инструментальном меню пунктов Данные/Фильтр/Отобразить все.
СРСП №8. Реализовать запрос к БД, используя функции категории Работа с базой данных. Привести расчетную формулу для подсчета числа работников, состоящих в браке и не имеющих детей.
Сценарий запроса к БД
1. Заполнение блока критериев новыми условиями выборки записей, как это показано на рис. 8.
2.
рис. 9
3.
рис. 10
4. Завершение диалога с Мастером функций, в результате чего в ячейку E86 должна быть введена формула: БСЧЁТ(A5:I84;G5;H1:I3), где G5 - ячейка имени поля с арифметическими значениями- окладами, используемыми для подсчета числа записей, удовлетворяющих условиям фильтрации.
5. Наблюдение в ячейке E86 результата запроса к БД.
СРСП №9. Реализовать перекрестный запрос к БД, используя операцию построения сводной таблицы: минимальные оклады по каждой группе семейного положения отдельно для женщин и мужчин.
Сценарий запроса к БД
1. Выбор в инструментальном меню пунктов Данные/Сводная таблица...
2. Реализация первого шага диалога с Мастером сводных таблиц - выбор варианта Создать таблицу на основе данных, находящихся в списке или базе данных Microsoft Excel.
3. Реализация второго шага диалога с Мастером сводных таблиц - выделение диапазона ячеек, занимаемых БД.
4. Реализация третьего шага диалога с Мастером сводных таблиц (см. рис. 12):
· перетаскивание имени поля Семейное положение в область строк сводной таблицы;
· перетаскивание имени поля Пол в область столбцов сводной таблицы;
· перетаскивание имени поля Оклад в область данных сводной таблицы;
· раскрытие списка вариантов вычислений в области данных двойным
· щелчком в соответствующем участке области данных и выбор позиции Минимум, как это показано на рис.11.
рис. 11
рис. 12
5. Реализация четвертого шага диалога с Мастером сводных таблиц - выбор варианта Поместить таблицу в новый лист.
6.
рис. 13
ПРИЛОЖЕНИЕ –Учебная база
Фамилия |
Имя |
Отчество |
Пол |
Дата рождения |
Должность |
Оклад |
Семейное положение |
Количество детей |
Агафонов |
Юрий |
Александрович |
м |
05.02.64 |
механик |
1 800р. |
женат |
3 |
Агеев |
Сергей |
Петрович |
м |
25.05.70 |
товаровед |
2 500р. |
женат |
2 |
Андреев |
Георгий |
Алексеевич |
м |
03.08.44 |
бухгалтер |
2 100р. |
разведен |
0 |
Андреев |
Юрий |
Дмитриевич |
м |
12.08.42 |
бухгалтер |
2 100р. |
вдовец |
5 |
Андреев |
Александр |
Алексеевич |
м |
22.09.55 |
продавец 2кат. |
2 450р. |
женат |
4 |
Андреев |
Александр |
Сергеевич |
м |
19.02.51 |
сторож |
1 300р. |
холост |
0 |
Аникина |
Ирина |
Григорьевна |
ж |
20.09.73 |
продавец 2кат. |
2 200р. |
незамужем |
4 |
Анисимов |
Григорий |
Сергеевич |
м |
07.07.64 |
грузчик |
1 200р. |
женат |
1 |
Аннушкин |
Сергей |
Петрович |
м |
02.11.49 |
зам.директора |
3 500р. |
вдовец |
2 |
Антоненко |
Иван |
Анатольевич |
м |
26.12.76 |
продавец 2кат. |
2 060р. |
женат |
1 |
Атоманюк |
Алексей |
Федорович |
м |
13.01.77 |
зав.секцией |
2 800р. |
женат |
3 |
Багирова |
Елена |
Константиновна |
ж |
09.12.74 |
продавец 1кат. |
2 400р. |
замужем |
1 |
Базина |
Людмила |
Петровна |
ж |
07.04.54 |
экономист |
2 900р. |
замужем |
1 |
Баранова |
Ирина |
Петровна |
ж |
03.05.77 |
бухгалтер |
1 960р. |
вдова |
5 |
Березкина |
Алла |
Сергеевна |
ж |
15.02.71 |
товаровед |
2 500р. |
замужем |
3 |
Бирюкова |
Александра |
Алексеевна |
ж |
18.09.80 |
продавец 2кат. |
2 060р. |
замужем |
4 |
Богданова |
Ирина |
Игоревна |
ж |
06.09.75 |
продавец 3кат. |
1 740р. |
вдова |
0 |
Бондарчук |
Сара |
Георгиевна |
ж |
08.12.68 |
зав.секцией |
2 800р. |
замужем |
1 |
Борисов |
Александр |
Алексеевич |
м |
16.05.75 |
экспедитор |
2 200р. |
женат |
1 |
Борисова |
Татьяна |
Александровна |
ж |
21.08.48 |
зав.секцией |
2 600р. |
вдова |
1 |
Бурова |
Татьяна |
Александровна |
ж |
11.05.61 |
кассир-контролер |
2 300р. |
замужем |
2 |
Васильева |
Татьяна |
Борисовна |
ж |
24.07.59 |
юрист |
2 900р. |
вдова |
2 |
Гарин |
Александр |
Петрович |
м |
22.01.65 |
продавец 1кат. |
2 400р. |
вдовец |
5 |
Гранина |
Нина |
Алексеевна |
ж |
21.10.78 |
администратор |
2 600р. |
замужем |
1 |
Грачева |
Алла |
Алексеевна |
ж |
09.09.71 |
гл.бухгалтер |
3 000р. |
разведена |
2 |
Григорьев |
Александр |
Сергеевич |
м |
26.04.60 |
коммерч.агент |
3 200р. |
холост |
2 |
Данилова |
Татьяна |
Александровна |
ж |
02.11.59 |
продавец 2кат. |
2 060р. |
замужем |
0 |
Дмитриева |
Ирина |
Игоревна |
ж |
01.11.65 |
бухгалтер |
1 960р. |
замужем |
1 |
Дорофеева |
Алла |
Сергеевна |
ж |
03.03.78 |
кассир-контролер |
2 400р. |
незамужем |
0 |
Дрозд |
Татьяна |
Александровна |
ж |
30.07.68 |
зам.зав.секцией |
2 600р. |
вдова |
4 |
Егорова |
Нина |
Алексеевна |
ж |
15.11.68 |
уборщица |
1 500р. |
замужем |
3 |
Ефимова |
Алла |
Сергеевна |
ж |
21.11.60 |
продавец 2кат. |
2 100р. |
замужем |
2 |
Жук |
Вера |
Алексеевна |
ж |
02.01.59 |
продавец 1кат. |
2 400р. |
незамужем |
1 |
Зверева |
Бэлла |
Гавриловна |
ж |
12.04.49 |
продавец 3кат. |
1 840р. |
замужем |
1 |
Иванова |
Нина |
Алексеевна |
ж |
10.03.76 |
продавец 2кат. |
2 100р. |
незамужем |
0 |
Ильина |
Людмила |
Николаевна |
ж |
04.08.75 |
уборщица |
1 500р. |
разведена |
0 |
Каро |
Наталия |
Константиновна |
ж |
11.07.64 |
продавец 3кат. |
1 750р. |
незамужем |
1 |
Ким |
Ирина |
Олеговна |
ж |
12.06.69 |
продавец 3кат. |
1 800р. |
незамужем |
0 |
Киреева |
Нина |
Борисовна |
ж |
03.03.70 |
фасовщица |
1 600р. |
замужем |
1 |
Китуничева |
Вера |
Дмитриевна |
ж |
07.07.64 |
ст.кассир |
2 500р. |
замужем |
2 |
Коваленко |
Анна |
Давыдовна |
ж |
17.07.73 |
продавец 3кат. |
1 800р. |
разведена |
3 |
Козлова |
Елена |
Николаевна |
ж |
26.02.63 |
продавец 3кат. |
1 900р. |
замужем |
1 |
Константинов |
Александр |
Олегович |
м |
11.02.55 |
бухгалтер |
2 000р. |
вдовец |
1 |
Лазаренко |
Алла |
Сергеевна |
ж |
01.06.65 |
продавец 3кат. |
1 900р. |
замужем |
1 |
Леонова |
Нина |
Алексеевна |
ж |
05.10.75 |
кассир |
2 200р. |
замужем |
0 |
Мартынихина |
Анна |
Дмитриевна |
ж |
01.10.65 |
приемщик посуды |
1 600р. |
замужем |
2 |
Милашевич |
Алла |
Александровна |
ж |
12.09.75 |
продавец 2кат. |
2 060р. |
замужем |
1 |
Минина |
Лиана |
Зурабовна |
ж |
02.11.75 |
кассир |
2 250р. |
замужем |
2 |
Минц |
Александр |
Сергеевич |
м |
07.02.56 |
охранник |
2 160р. |
разведен |
1 |
Михайлов |
Алексей |
Петрович |
м |
05.08.71 |
приемщик посуды |
1 600р. |
женат |
0 |
Москвина |
Алла |
Сергеевна |
ж |
01.08.77 |
зам.зав.секцией |
2 400р. |
замужем |
3 |
Мухина |
Наталья |
Олеговна |
ж |
29.06.62 |
кассир |
2 300р. |
замужем |
1 |
Наливайко |
Ирина |
Олеговна |
ж |
02.07.75 |
повар |
2 300р. |
замужем |
1 |
Нарышкина |
Елена |
Емельяновна |
ж |
25.06.75 |
фасовщица |
1 900р. |
замужем |
1 |
Никодимов |
Петр |
Александрович |
м |
31.01.61 |
продавец 3кат. |
1 900р. |
разведен |
0 |
Николаев |
Петр |
Алексеевич |
м |
05.12.39 |
кассир |
2 300р. |
вдовец |
1 |
Николаева |
Наталия |
Константиновна |
ж |
23.07.61 |
повар |
2 000р. |
замужем |
0 |
Озерова |
Нина |
Алексеевна |
ж |
05.06.64 |
продавец 3кат. |
1 900р. |
замужем |
1 |
Павлова |
Вера |
Олеговна |
ж |
17.10.59 |
продавец 3кат. |
1 800р. |
незамужем |
0 |
Петрова |
Анна |
Дмитриевна |
ж |
07.04.73 |
бухгалтер |
1 900р. |
незамужем |
0 |
Петрова |
Анна |
Юрьевна |
ж |
31.08.63 |
продавец 3кат. |
1 800р. |
замужем |
2 |
Петрушевич |
Ирина |
Олеговна |
ж |
22.01.80 |
продавец 3кат. |
1 800р. |
незамужем |
0 |
Пушкина |
Нина |
Алексеевна |
ж |
31.01.58 |
кассир |
2 300р. |
незамужем |
0 |
Рудакова |
Анна |
Дмитриевна |
ж |
20.04.72 |
продавец 1кат. |
2 300р. |
незамужем |
1 |
Салтыкова |
Нина |
Алексеевна |
ж |
15.03.76 |
кассир |
2 300р. |
незамужем |
0 |
Селедкина |
Ирина |
Олеговна |
ж |
14.02.69 |
директор |
4 000р. |
разведена |
1 |
Смирнова |
Елена |
Емельяновна |
ж |
24.01.79 |
кассир-контролер |
2 400р. |
разведена |
3 |
Соловьева |
Алла |
Сергеевна |
ж |
10.06.69 |
продавец 3кат. |
1 960р. |
разведена |
1 |
Сурикова |
Нина |
Игоревна |
ж |
27.12.78 |
зам.зав.секцией |
2 500р. |
разведена |
3 |
Тарасова |
Жанна |
Осиповна |
ж |
13.07.72 |
продавец 3кат. |
1 900р. |
разведена |
0 |
Трофимова |
Елена |
Николаевна |
ж |
10.09.79 |
продавец 1кат. |
2 400р. |
разведена |
0 |
Трушко |
Нина |
Олеговна |
ж |
03.04.72 |
корзинщица |
1 540р. |
замужем |
0 |
Уханова |
Эльвира |
Ивановна |
ж |
23.12.74 |
кассир |
2 300р. |
разведена |
1 |
Федоренко |
Нина |
Викторовна |
ж |
01.05.77 |
зам.зав.секцией |
2 500р. |
разведена |
2 |
Федоров |
Виктор |
Александрович |
м |
13.09.70 |
охранник |
2 100р. |
холост |
0 |
Чаадаев |
Борис |
Викторович |
м |
19.05.63 |
Ком. директор |
3 600р. |
холост |
0 |
Чупятов |
Петр |
Алексеевич |
м |
26.03.76 |
грузчик |
1 200р. |
разведен |
2 |
Шакерзанов |
Алексей |
Дмитриевич |
м |
17.04.72 |
сторож |
1 200р. |
женат |
0 |
Янковская |
Жанна |
Викторовна |
ж |
21.12.70 |
зав.секцией |
2 700р. |
разведена |
0 |