Работа по предмету: автоматизированные информационные системы в экономике. Задание: СОЗДАНИЕ ФРАГМЕНТА АВТОМАТИЗИРОВАННОЙ СИСТЕМЫ ПОДГОТОВКИ И УПРАВЛЕНИЯ ДАННЫМИ ТОРГОВОЙ ФИРМЫ

Работа по предмету: автоматизированные информационные системы в экономике.
Задание: СОЗДАНИЕ ФРАГМЕНТА АВТОМАТИЗИРОВАННОЙ СИСТЕМЫ ПОДГОТОВКИ И УПРАВЛЕНИЯ ДАННЫМИ ТОРГОВОЙ ФИРМЫ

ПОСТАНОВКА ЗАДАЧИ
Создать фрагмент системы подготовки и управления данными торговой фирмы, включающий в себя:
─   оформление списков клиентов фирмы,
─   оформление списков товаров, предлагаемых фирмой,
─   оформление списков заказов на поставку на основе электронного бланка-заказа,
─   консолидацию данных о продажах по нескольким филиалам,
─   оценку наиболее популярного товара по всем филиалам,
─   определение наиболее успешно работающего филиала,
─   определение товара имеющего наибольший оборот и долю в общих продажах.
Инструментарий:
Работа с таблицей как с базой данных (фильтры, имена полей), функции ЕСЛИ, ПРОСМОТР, Сводные таблицы, Консолидация.
РЕШЕНИЕ ЗАДАЧИ
1.   Создадим список клиентов фирмы в соответствии с приведенной таблицей на рабочем листе Клиенты.

2.    Заголовки таблицы должны быть отцентрированы и слова в них должны переноситься по словам. Столбец Код заполнить прогрессией от 2101, 2102 и т.д.
3.    Присвойте имена диапазонам ячеек, используя команду лента Формулы – блок Определенные имена – Присвоить имя: имя Фирма столбцу A (выделив его целиком на заголовке), столбцу B – Код, столбцу I – Скидка.
4.    Создайте список товаров в соответствии с приведенным образцом таблицы. Каждому товару присвоим определенный номер, что позволит в дальнейшем автоматизировать некоторые операции. Рабочий лист – Товары. Столбцам A, B, C присвоим соответственно имена – Номер, Товар, Цена.

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

Функция ЕСЛИ проверяет содержимое ячейки D2. Если в ней данные отсутствуют, то E2 тоже останется незаполненной. Если в D2 введен номер товара, то будет выполняться поиск номера товара в диапазоне Номер и в ячейку D2 возвратится соответствующее значение диапазона Товар.
8. Аналогичные формулы будут для ячеек цены, названия фирмы, скидки, а для суммы заказа и суммы оплаты будут расчетные формулы:

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

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

В ячейке	Ввести формулу
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;заказ;оплата))

11.    Выполним анализ данных с помощью сводной таблицы. Новому рабочему листу присвоим имя Таблица и выполним команду Вставка – блок Таблицы – Сводная таблица. Диапазон – вся таблицаЗаказы. Структура сводной таблицы, как на рис 5. По полученной таблице можно сделать вывод о том, какой товар имеетнаибольший оборот.
12.    Создайте еще несколько сводных таблиц по образцам на рис. 6,7 для получения информации по следующим темам – месячные продажи в разрезе наименований, распределение товаров по клиентам, VIP-клиенты и т.п.:
13.    Используя сводные таблицы определите составьте список «географии» клиентов и постройте круговую диаграмму по ней (рис. 8).
14.    Составьте сводную таблицу, чтобы определить какой доход принесен каждой фирмой-клиентом.
15.    Предположим, что фирма имеет три филиала. Введем на трех листах таблицы с данными о работе филиалов фирмы Санкт-Петербург, Архангельск и Нижний Новгород. Обратите внимание, что состав проданных товаров различается по филиалам. Рассчитаем Объем продаж, используя группировку листов . Получится примерно такие три таблицы на разных листах (рис. 9):
16.    Определим сколько товаров каждого наименования продано всеми филиалами, выполнив консолидацию по наименованию товаров. Для этого создадим новый лист – Итог. Поместив курсор в ячейку С3 этого листа, выполним команду Данные – блок Работа с данными – Консолидация. Функция – Сумма. С помощью поля Ссылка и кнопки Добавить подберем диапазоны ячеек с листов всех филиалов, начиная с заголовка Наименование товара. Установим опцию Создавать связи с исходными данными. Удалим в консолидированной таблице столбец Цена.
Добавим в полученную консолидированную таблицу строку Всего и столбец Доля для определения значений долей продаж каждого товара в общем объеме продаж. Результат будет примерно как на рис. 10.На основе полученной таблицы можно определить, какой товар наиболее популярен. Изменив с помощью элементов структуры таблицу, можно расшифровать данные о нем.
17.    На основе этой таблицы построить круговую диаграмму по номенклатуре, снабдив ее значениями долей.
18.    Подвести итоги продаж по месяцам с помощью инструмента Данные – Структура - Промежуточные итоги.
19.    На основе полученных итогов составьте график суммы продаж по месяцам и определите тенденцию продаж за три месяца, используя линию тренда. Постройте линии тренда с разными вариантами аппроксимации. Какая из линий в наибольшей степени близка к реальной картине аппроксимации?
20.    На основе таблиц Заказы и Клиенты и расширенного фильтра определите и выведите в отдельные таблицы:
    Все продажи мониторов,
    Все продажи принтеров,
    Все продажи конкретной фирмы в феврале,
    Все продажи фирме Финиш или Партия в феврале,
    Список заказов, в которых объем заказа превышал 20 единиц,
    Всех московских клиентов, у которых скидка больше 10%,
21.    Используя функцию СЧЕТЕСЛИ подсчитайте сколько клиентов имеют скидку больше 10%, сколько клиентов из Москвы.
22.    Сохраните созданный файл.