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

ТРЕБОВАНИЯ К ВЫПОЛНЕНИЮ ЗАДАНИЯ.

1. Выполнить задание в MS Excel 2007

2. Желательно выполнять пункты задания (где это не противоречит самому тексту задания) на отдельных листах рабочей книги

3. Начиная с 12 пункта задания необходимо включить в отчет о работе свои комментарии и выводы по результатам выполнениния.

4. Файл с выполненным заданием отправьте по почте: melamud@rea.ru и mmr556@yandex.ru

СОЗДАНИЕ ФРАГМЕНТА АВТОМАТИЗИРОВАННОЙ СИСТЕМЫ ПОДГОТОВКИ И УПРАВЛЕНИЯ ДАННЫМИ ТОРГОВОЙ ФИРМЫ

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

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

─ оформление списков клиентов фирмы,

─ оформление списков товаров, предлагаемых фирмой,

─ оформление списков заказов на поставку на основе электронного бланка-заказа,

─ консолидацию данных о продажах по нескольким филиалам,

─ оценку наиболее популярного товара по всем филиалам,

─ определение наиболее успешно работающего филиала,

─ определение товара имеющего наибольший оборот и долю в общих продажах.

Инструментарий:

Работа с таблицей как с базой данных (фильтры, имена полей), функции ЕСЛИ, ПРОСМОТР, Сводные таблицы, Консолидация.

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

1. Создадим список клиентов фирмы в соответствии с приведенной таблицей на рабочем листе Клиенты.

Рис. 1 Таблица Клиенты

2. Заголовки таблицы должны быть отцентрированы и слова в них должны переноситься по словам. Столбец Код заполнить прогрессией от 2101, 2102 и т.д.

3. Присвойте имена диапазонам ячеек, используя команду лента Формулы – блок Определенные имена – Присвоить имя: имя Фирма столбцу A (выделив его целиком на заголовке), столбцу B – Код, столбцу I – Скидка.

4. Создайте список товаров в соответствии с приведенным образцом таблицы. Каждому товару присвоим определенный номер, что позволит в дальнейшем автоматизировать некоторые операции. Рабочий лист – Товары. Столбцам A, B, C присвоим соответственно имена – Номер, Товар, Цена.

Рис. 2 Таблица Товары

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

Рис. 3 Таблица заказы

6. Для всех столбцов таблицы зададим имена соответственно – Месяц, Дата, Заказ, Номер2, Товар2, Количество, Цена2, Код2, Фирма2, Сумма, Скидка2, Оплата.

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

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

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

9. Создадим бланк-заказ по образцу:

Рис. 4 Бланк-заказ

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

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

12. Создайте еще несколько сводных таблиц по образцам на рис. 6, 7 для получения информации по следующим темам – месячные продажи в разрезе наименований, распределение товаров по клиентам, VIP-клиенты и т.п.:

Рис. 5 Сводная таблица 1	Рис. 6 Сводная таблица 2	Рис. 7 Сводная таблица 3

Рис. 8 Диаграмма «География клиентов»

13. Используя сводные таблицы определите составьте список «географии» клиентов и постройте круговую диаграмму по ней (рис. 8).

14. Составьте сводную таблицу, чтобы определить какой доход принесен каждой фирмой-клиентом.

15. Предположим, что фирма имеет три филиала. Введем на трех листах таблицы с данными о работе филиалов фирмы Санкт-Петербург, Архангельск и Нижний Новгород. Обратите внимание, что состав проданных товаров различается по филиалам. Рассчитаем Объем продаж, используя группировку листов[1]. Получится примерно такие три таблицы на разных листах (рис. 9):

16. Определим сколько товаров каждого наименования продано всеми филиалами, выполнив консолидацию по наименованию товаров. Для этого создадим новый лист – Итог. Поместив курсор в ячейку С3 этого листа, выполним команду Данные – блок Работа с данными – Консолидация. Функция – Сумма. С помощью поля Ссылка и кнопки Добавить подберем диапазоны ячеек с листов всех филиалов, начиная с заголовка Наименование товара. Установим опцию Создавать связи с исходными данными. Удалим в консолидированной таблице столбец Цена.

Рис. 9 Данные по филиалу	Рис. 10 Доли продаж по номенклатуре товаров

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

17. На основе этой таблицы построить круговую диаграмму по номенклатуре, снабдив ее значениями долей.

18. Подвести итоги продаж по месяцам с помощью инструмента Данные – Структура - Промежуточные итоги.

Рис. 11 Промежуточные итоги

19. На основе полученных итогов составьте график суммы продаж по месяцам и определите тенденцию продаж за три месяца, используя линию тренда. Постройте линии тренда с разными вариантами аппроксимации. Какая из линий в наибольшей степени близка к реальной картине аппроксимации?

рис. 12 Тенденции продаж

20. На основе таблиц Заказы и Клиенты и расширенного фильтра определите и выведите в отдельные таблицы:

- Все продажи мониторов,

- Все продажи принтеров,

- Все продажи конкретной фирмы в феврале,

- Все продажи фирме Финиш или Партия в феврале,

- Список заказов, в которых объем заказа превышал 20 единиц,

- Всех московских клиентов, у которых скидка больше 10%,

21. Используя функцию СЧЕТЕСЛИ подсчитайте сколько клиентов имеют скидку больше 10%, сколько клиентов из Москвы.

22. Сохраните созданный файл.

[1] Группировка рабочих листов – щелчок на ярлыке первого листа (например, Санкт-Петербург), нажать клавишу SHIFT и щелкнуть на ярлыке последнего листа.