Информационные технологии. Практическое задание 3. Тема 1.5. Информационные технологии в задачах управления

Выполнено практическое задание 3. Тема 1.5. Информационные технологии в задачах управления

Задание 3

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

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

3.2. Загрузите файл Microsoft Excel с расчетами в курсе для проверки преподавателем. Имя файла Задание 3.xlsx.

Ход выполнения задания

Создайте на листе Microsoft Excel таблицу с информацией о винчестерах.

Создайте формы для выборки критериев. Для этого выведите на экран панель Элементы управления формы и добавьте на Лист элементы управления Флажок, Переключатель, Поле со списком, Список .

Элемент Группа объединяет элементы управления для выбора. Задайте соответствующее название каждой группе элементов управления (рис. 5). Для размещения на Листе объекта Группы выбрать на панели Элементы управления формы элемент управления Группа и растянуть на Листе прямоугольник нужного размера. Изменить надпись, предлагаемую по умолчанию, на «Объем». Внутри созданной Группы разместить четыре элемента управления Флажок, подписав каждый (рис. 3). При помощи контекстного меню можно отформатировать элементы управления, изменив шрифт, цвет, линии.

Аналогично добавьте Группы для элементов управления «Скорость», «Цена», «Интерфейс», «Фирмы».

Разместите дополнительные сведения об объеме, названии фирмы, скорости, интерфейсе, цене, например, в ячейках L3:P9.

Для того чтобы введенные данные отобразились в элементе Поле со списком, свяжите их с соответствующими ячейками таблицы. Для связи элемента с ячейками щелкните правой кнопкой мыши по элементу Поле со списком «Цена» и в контекстном меню выберите пункт Формат элемента управления, где на вкладке Элемент управления в текстовом окне Формировать список по диапазону укажите ячейки, хранящие данные, например Р4:Р9, в поле Связь с ячейкой – Р10.

При нажатии на кнопку ОК элемент управления Поле со списком «Цена» отобразит все введенные в дополнительную таблицу цены на комплектующие. При выборе щелчком мыши одного из пунктов «Цена» в ячейке Р10 отображается его порядковый номер (от 1 до 6), который будет затем применяться при формировании критериев поиска.

Аналогично заполните элементы списка «Фирма» (свяжите с ячейкой М10), «Интерфейс» (свяжите с ячейкой О10), группа «Скорость» содержит три элемента Переключатель. Чтобы оперативно отслеживать информацию по выбору элемента, свяжите каждый Переключатель с ячейкой N10.

Группа «Объем» содержит элементы Флажок, которые позволяют выбирать один, несколько элементов или все имеющиеся элементы списка. Для отображения выбранных критериев поиска по объему постройте дополнительную таблицу.

Группа «Объем» содержит четыре элемента Флажок, свяжите каждый из элементов с ячейками L20:L23 соответственно. Так как элементам Флажок разрешено занимать несколько позиций одновременно, то установка или сброс галочек в группе «Объём» сопровождается выводом в связанные с ними ячейки (L20:L23) логических значений «ИСТИНА» (Флажок выбран) или «ЛОЖЬ» (Флажок сброшен).

В ячейках столбцов М и N будут отображаться числа, выбранные при помощи элементов Флажок. На рисунке 5 первый элемент Флажок имеет одинаковый диапазон нижней и верхней границ объема, равный 80 Гб. Для отображения искомого диапазона в таблице в ячейку М20 введите формулу =ECЛИ(L20;L4;" "), а в ячейку N20 – формулу =ECЛИ(L20;L4;" "). Для отображения границ третьего элемента Флажок с диапазоном от 160 до 250 Гб в ячейку М22 введите формулу =EСЛИ(L22;L6;" "), а в ячейку N22 – формулу =ECЛИ(L22;L7;" "). Аналогично введите формулы для оставшихся диапазонов «Объема». Таким образом, будут заполняться ячейки, определяющие верхнюю и нижнюю границы диапазона объема.

Для ячейки М19 найдите наименьшее значение по формуле =МИН(М20:М23), а для ячейки N19 найдите наибольшее значение =MAKC(N20:N23).

Вычислите критерии для отбора данных. Заполните ячейки Ml1:P11 текстовой информацией о выбранных комплектующих при помощи функции ВЫБОР (категория Ссылки и массивы) (рис. 9). В зависимости от того, какой элемент списка был выбран пользователем, в ячейке М10 выводится его порядковый номер: 1, 2, 3, 4, 5.

Для этого в ячейку М11 введите формулу для определения выбранной фирмы-производителя =ВЫБОР(М10;М4;М5;М6;М7;М8).

Аналогично определите значения ячеек N11 и O11 для атрибутов «Скорость» и «Интерфейс».

Для определения значения ячейки Р11 атрибута «Цена» необходимо выполнить дополнительные преобразования символьных данных в числовые. Для этого создайте дополнительную таблицу, заполните ее числовыми данными ячеек Р4:Р9. Символьный аргумент «не важно» заменить числом 10 000.

Введите в ячейку P11 формулу =ВЫБОР(Р10;Р19;Р20;Р21;Р22;Р23;Р24).

Заполните таблицу для вычисления критериев поиска (рис. 11). Для этого создайте таблицу с вычисляемыми по формулам критериями поиска. Заполните диапазон критериев (L15:Р15) пятью логическими выражениями. Структура логических выражений следующая: содержимое ячеек (L15:P15) подвергается чисто механическому сравнению, а результаты этих сравнений комбинируются друг с другом функциями «И», «ИЛИ» и т. п.

Так, формула в N15 =ИЛИ(E4=$N$11;$N$11=$N$6) означает, что отслеживается наступление по крайней мере одного из двух событий:

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

Когда переключатель находится в положении «любая», и поэтому вообще не имеет значения, какая скорость приведена в Е4.

Аналогично заполните остальные критерии за исключением атрибута «Объём». Так как здесь используется интервал значений, то формула будет выглядеть =И(С4>=$М$19;С4<=$N19).

В результате получается набор из пяти выражений. Первая запись базы данных, то есть (A4:G4), пройдёт этот фильтр, если каждое условие даст «ИСТИНА». А поскольку адресные ссылки на четвёртую строчку относительны в противовес остальным, абсолютным, то затем обработке подвергнутся и все остальные записи.

Для просмотра отобранных записей запустите механизм расширенного фильтра с исходным диапазоном (A3:G15), диапазоном условий (L14:Р15) и копированием результата в другое место, например (A17:G30).

Добавьте элемент управления Кнопка на Лист. Задайте имя кнопки – Произвести поиск.

Щелкните правой кнопкой мыши по Кнопке и в контекстном меню выберите пункт Назначить макрос... В появившемся диалоговом окне «Назначить макрос объекту» щелкните по кнопке Записать.

В окне «Запись макроса» щелкните кнопку ОК, начнется запись макроса.

Запишите макрос, проделав следующие действия: выберите вкладку Данные – кнопка Дополнительно, выставьте опции в диалоговом окне Расширенный фильтр:

- исходный диапазон (выделить ячейки A3:G15);

- диапазон условий (L14: Р15);

- выберите опцию «Скопировать в другое место» и укажите, куда поместить результат (A17:G30). Нажмите ОК и остановите запись.

Апробируйте работу Кнопки при различных критериях поиска.

Самостоятельно добавьте кнопку и запишите макрос для очистки данных после выбора согласно критериям отбора.