Студопедия

Главная страница Случайная страница

КАТЕГОРИИ:

АвтомобилиАстрономияБиологияГеографияДом и садДругие языкиДругоеИнформатикаИсторияКультураЛитератураЛогикаМатематикаМедицинаМеталлургияМеханикаОбразованиеОхрана трудаПедагогикаПолитикаПравоПсихологияРелигияРиторикаСоциологияСпортСтроительствоТехнологияТуризмФизикаФилософияФинансыХимияЧерчениеЭкологияЭкономикаЭлектроника






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




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

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. Аналогичные формулы будут для ячеек цены, названия фирмы, скидки, а для суммы заказа и суммы оплаты будут расчетные формулы:

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

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

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


 

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-клиенты и т.п.:

Рис. 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 и щелкнуть на ярлыке последнего листа.


mylektsii.ru - Мои Лекции - 2015-2018 год. (0.009 сек.)Все материалы представленные на сайте исключительно с целью ознакомления читателями и не преследуют коммерческих целей или нарушение авторских прав Пожаловаться на материал