Решение задачи

Система подготовки и управления данными торговой фирмы

Постановка задачи

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

Решение задачи

1. Создайте список клиентов фирмы в соответствии с приведенной таблицей на рабочем листе Клиенты (рис.1). Введите шапку таблицы. Данные вводите, используя форму данных (Данные – Форма) (рис.2). (В случае необходимости добавьте команду Форма на ленту Данные: Файл-Параметры-Настройка ленты…)

Для завершения ввода информации по одному клиенту, а также для введения новой записи в список используется кнопка Добавить. Для перемещения между полями используется клавиша TAB.

Рис. 1

2. Присвойте имя Фирма столбцу А. Для этого выделите его щелчком на заголовке и выполнить команду Формулы - Имя – Присвоить имя). Аналогично присваиваем имена столбцу B – Код и столбцу I – Скидка.

3. Создайте список товаров в соответствии с приведенным образцом таблицы (рис. 3). Этот список будет содержать данные о предлагаемых фирмой товарах. Рабочий лист назовите Товары.

Столбцам A, B, C присвойте соответственно имена – Номер, Товар, Цена.

4. Создайте на рабочем листе Заказы список заказов (рис. 4).

Введите шапку и значения столбцов D, F, H (остальные столбцы заполним позже, используя функцию ПРОСМОТР).

Для столбцов B, C, D, E, F, G, H, I, J, K, L задайте имена соответственно – Дата, Заказ, Номер2, Товар2, Количество, Цена2, Код2, Фирма2, Сумма, Скидка2, Оплата.

Рис. 4

В ячейке E2 должно быть представлено наименование товара, оно вводится автоматически с помощью следующей формулы: =ЕСЛИ(D2="";"";ПРОСМОТР(D2;Номер;Товар)). В остальные ячейки столбца эта формула копируется. Функция ЕСЛИ проверяет содержимое ячейки D2. Если в ней данные отсутствуют, то E2 тоже останется незаполненной. Если в D2 введен номер товара, то будет выполняться поиск номера товара в диапазоне Номер и в ячейку D2 возвратится соответствующее значение диапазона Товар.

Аналогичные формулы будут для ячеек цены, названия фирмы, скидки, а для суммы заказа и суммы оплаты будут расчетные формулы. Все эти формулы сведены в таблицу:

В ячейке	Имя	Ввести формулу
G2	Цена	=ЕСЛИ(D2="";"";ПРОСМОТР(D2;Номер;Цена))
I2	Название фирмы	=ЕСЛИ(H2="";"";ПРОСМОТР(H2;Код;Фирма))
J2	Сумма заказа	=F2*G2
K2	Скидка	=ЕСЛИ(H2="";"";ПРОСМОТР(H2;Код;Скидка))
L2	Уплачено	=J2-J2*K2

Столбцы В и С введите по образцу (рис.5), используя автозаполнение. В результате после заполнения Вы получите список заказов:

Рис. 5.

5. Создайте бланк-заказ. Образец его приведен на рис. 6.

Чтобы при заполнении заказа информация вставлялась автоматически, введите формулы в соответствующие ячейки:

В ячейке	Ввести формулу
E5	=ЕСЛИ($E$3="";"";ПРОСМОТР($E$3;заказ;фирма2))
I5	=ЕСЛИ($E$3="";"";ПРОСМОТР($E$3;заказ;код2))
E7	=ЕСЛИ($E$3="";"";ПРОСМОТР($E$3;заказ;товар2))
I7	=ЕСЛИ($E$3="";"";ПРОСМОТР($E$3;заказ;номер2))
E9	=ЕСЛИ($E$3="";"";ПРОСМОТР($E$3;заказ;количество))
H9	=ЕСЛИ($E$3="";"";ПРОСМОТР($E$3;заказ;цена2))
E11	=ЕСЛИ($E$3="";"";ПРОСМОТР($E$3;заказ;сумма))
I11	=ЕСЛИ($E$3="";"";ПРОСМОТР($E$3;заказ;скидка2))
D13	=ЕСЛИ($E$3="";"";ПРОСМОТР($E$3;заказ;оплата))
G3	Напишите формулу САМОСТОЯТЕЛЬНО

6. Введите таблицы с данными о работе филиалов фирмы. С этой целью скопируем таблицу с листа Товары на новый лист или книгу. Предположим, что филиалов три: в Киеве, Минске и Риге. Присвоим листам названия в соответствии с названиями городов. Сгруппировав три листа (используя клавишу CTRL), вставим таблицу сразу на все три листа. Изменим некоторые данные. Дополним таблицы новыми столбцами Количество заказов, Проданное количество и Объем продаж. Например, на листе Минск получится примерно такая таблица (Рис. 7):

	Рис. 7

7. Выполните консолидацию (без связи) по филиалам. Добавьте в полученную таблицу строку Всего и столбец Доля для определения значений долей в общем объеме продаж. Пример консолидации показан на рис. 8.

	Рис. 8
		Рис. 9

8. Постройте сводную таблицу на основе таблицы консолидации для того, чтобы определить какой товар как продавался. Отсортируйте построенную таблицу по объему продаж. Примерный вид сводной таблицы представлен на рис. 9.

Дата добавления: 2015-07-24