621 группа МДК ДЗ на 17.03.2020 Практическая работа 8

Практическая работа 8. БАЗЫ ДАННЫХ В MS EXCEL

Цель работы: познакомиться с основными приемами создания базы данных в табличной форме и основными командами работы с базой данных – сортировкой, выборкой данных по поставленному условию, группировкой данных и вычислением итогов по сгруппированным данным.

База данных (БД) – объем какой-либо информации, структурированный определенным образом (обычно в виде таблицы).

Столбцы БД называются полями, а строки – записями.

Основные правила формирования БД в Excel:

1. БД располагается на листе таблицы, где не должно быть никаких других данных, т.к. изначально предполагается, что в БД будут добавляться новые записи.

2. Первая строка БД используется для задания имен полей. имя поля должно быть расположено в пределах одной ячейки. имена полей должны быть краткими.

3. Заголовочная строка БД должна быть выделена элементами форматирования (полужирный текст, рамка, другой шрифт).

4. В пределах одного поля данные должны быть одного типа (или числа, или текст, или даты).

5. В таблице с БД не должно быть пустых строй или столбцов.

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

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

1.1. Создание списка КЛИЕНТЫ.

Присвойте первому рабочему листу имя Клиенты и создайте таблицу:

В поле [Скидка %] установлен процентный формат

Для ввода данных в таблицу воспользуемся специальным диалоговым окном - формой данных. Для его вызова установим курсор в любую ячейку таблицы и выполним команду Данные/Форма. Если на вкладке Данные кнопки Форма нет, проще всего добавить ее на Панель быстрого доступа, выполнив Другие команды…/Все команды/Форма:

В окне формы все поля одной записи БД. Название окна соответствует ярлычку листа Excel. Если поле является вычисляемым, оно для ввода недоступно. Справа в форме расположен набор кнопок для работы с записями: Добавить, Удалить, Назад и т.д.

С помощью Формы добавьте в таблицу КЛИЕНТЫ две новых записи:

1. Создание списка ТОВАРЫ.

Перейдите на второй лист рабочей книги и присвойте ему имя ТОВАРЫ. Создайте таблицу:

Выполните для данных этой таблицы Настраиваемую сортировку: сначала по полю [Наименование товара] (по возрастанию), затем по полю [Цена] (по убыванию).

1. Создание списка ЗАКАЗЫ.

Перейдите на третий лист рабочей книги и присвойте ему имя ЗАКАЗЫ. Создайте таблицу:

Поле [Наименование товара] должно заполняться автоматически, беря данные из таблицы ТОВАР, в соответствии с полем [Номер товара]. Чтобы реализовать такую возможность используем функцию ПРОСМОТР:

СИНТАКСИС функции ПРОСМОТР:

ПРОСМОТР (искомое значение; просматриваемый вектор; вектор результатов)

Вектор в MS Excel - это массив, который содержит только одну строку или один столбец.

Искомое_значение - это значение, которое ПРОСМОТР ищет в первом векторе. Может быть числом, текстом, логическим значением, именем или ссылкой, ссылающимися на значение.

Просматриваемый_вектор - это интервал, содержащий только одну строку или один столбец. Значения могут быть текстами, числами или логическими значениями.

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

Для поля [Наименование товара] формула в ячейке Е2 будет следующая:

=ПРОСМОТР(D2;Товары!$A$2:$A$10;Товары!$B$2:$B$10),

где ячейка D2 – искомое значение, которое необходимо найти в просматриваемом векторе,

диапазон $A$2:$A$10 – просматриваемый вектор (столбец [Номер товара] в списке Товары,

$B$2:$B$10 – вектор результатов (столбец [Наименование товара] в списке Товары).

Скопируйте формулу в смежные ячейки E3:E12.

1.4 Используя функцию ПРОСМОТР, заполните самостоятельно поле [Фирма].

1.5 Заполним поле [Сумма]. Для этого в ячейку I2 введем формулу:

=ЕСЛИ(H2="";"";H2*ПРОСМОТР(D2;Товары!$A$2:$A$10;Товары!$C$2:$C$10)),

где H2 – ячейка с количеством,

D2 - просматриваемая ячейка,

диапазон $A$2:$A$10 – просматриваемый вектор (столбец [Номер товара] в списке Товары),

диапазон $C$2:$C$10 – вектор результатов (столбец [Цена] в списке Товары).

Здесь функция ЕСЛИ используется для контроля наличия данных в поле [Кол-во]: если ячейка Н2 = 0 (данные отсутствуют), то возвращаемое значение отсутствует (""), если ячейка Н2 содержит данные, то возвращаемое значение равно [Кол-во] * [Цена(USD)]

1.5 Рассчитайте значение поля [Скидка] как произведение данных из поля [Сумма] и поля [Скидка %] таблицы Клиенты.

1.6 Рассчитайте значения поля [Оплачено] = [Сумма]- [Скидка].

Результат выполнения задания:

Задание 2. Создание Бланка заказов.

Задача: Предусмотреть возможность печати Бланка Заказа, который может заполняться автоматически при внесении конкретного номера заказа.

1. Откройте 4-ый лист вашей рабочей книги. Дайте ему название Бланк и создайте на нем заготовку для накладной:

1. В область для номера заказа введите любой номер из списка Заказы.

2. В остальные выделенные области будем вносить формулы:

   • В область Дата внесите формулу, позволяющая вводить автоматически дату заказа:

= ПРОСМОТР (Ячейка Номер заказа из Бланка; поле Номер заказа из списка Заказы; поле Дата из этого же списка), т.е. примерно так:

= ПРОСМОТР (В2; Заказы!С2:С12; Заказы!В2:В12)

• В область Название фирмы внесите формулу, позволяющую вводить автоматически название фирмы заказчика.

= ПРОСМОТР (Ячейка Номер заказа из Бланка; поле Номер заказа из списка Заказы; поле Фирма из этого же списка)

• Аналогично заполним выделенные области Количество, Скидка, К оплате.

• В область Доверенность выдал внесите формулу, позволяющая вводить автоматически фамилию: = ПРОСМОТР (ячейка Название фирмы из Бланка, Поле Название фирмы из списка Клиенты, поле Контактная персона из этого же списка).

• Аналогично заполните область Телефон.

• В область Цена единицы внесите формулу:
  =ПРОСМОТР (ячейка Название товара из Бланка, поле Наименование товара из списка Товары, поле Цена из этого же списка)

1. Внесите в ячейку G11 для поля Оформил свою Фамилию.

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

Результат:

Принтер струйный Ц

02.02.2004

Поработайте со своим Бланком Заказа. Внесите другие номера заказов. Проверьте правильность результатов.

Задание 3. Фильтрация данных в таблице

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

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

Задача: Выбрать из таблицы Клиенты данные по Минску со скидкой 5%.

1 Подготовим на листе КЛИЕНТЫ область критериев поиска:

2 Выполним команду Данные/ Дополнительно:

Результат фильтрации появился в указанном месте – с 18 строки:

3 Из таблицы Заказы вывести данные о заказах в январе из Минска.

4 Вывести данные о заказах со скидкой меньше 200 фирмы Старт ОАО.