Задание 1. Создание отчетной ведомости о результатах работы сети
магазинов.
Имеются данные по каждому
магазину за июнь, июль, август. С помощью мастера функций (функция СУММ)
посчитаем суммарную выручку по каждому магазину и в сумме по месяцам. Для
определения места магазинов по данным суммарной выручки необходимо
воспользоваться функцией РАНГ. Для определения средней выручки – СРЗНАЧ.
Начисленная премия определили с помощью функции ЕСЛИ,
Таблица 1.
Выручка сети
магазинов в млн. руб.
|
|
A
|
B
|
C
|
D
|
E
|
F
|
G
|
H
|
I
|
|
1
|
Магазин
|
Июнь
|
Июль
|
Август
|
Суммарная
выручка
|
Место
|
Средняя выручка
|
Процент
|
Начисленная
премия
|
|
2
|
1
|
225
|
455
|
534
|
1214
|
2
|
404,67
|
18
|
121,4
|
|
3
|
2
|
342
|
356
|
345
|
1043
|
5
|
347,67
|
16
|
-
|
|
4
|
3
|
432
|
357
|
454
|
1243
|
1
|
414,33
|
19
|
248,6
|
|
5
|
4
|
324
|
243
|
248
|
815
|
6
|
271,67
|
12
|
-
|
|
6
|
5
|
352
|
423
|
392
|
1167
|
3
|
389
|
18
|
58,35
|
|
7
|
6
|
421
|
354
|
351
|
1126
|
4
|
375,33
|
17
|
-
|
|
8
|
Итого
|
2096
|
2188
|
2324
|
6608
|
|
|
|
|
По результатам сделанных
расчетов получается, что магазин 3 с суммарной выручкой 1243млн. руб. занимает
1 место, последнее место занимает магазин 4 с суммарной выручкой 815млн. руб.
Премия магазинов
начисляется в зависимости от того, какое место он занимает. Получается, что
магазин 3 с суммарной выручкой 1243млн. руб. получает премию в размере
248,6млн. руб.(1243*0,2), а магазины 2, 4, 6 не получают премию.
Задание 2. Использование фильтра.
Для формирования выборки
используется Расширенный фильтр. Для того, чтобы Фильтр сортировал информацию
по заданному условию, создадим таблицу критериев с заданными условиями.
Таблица 2.
Таблица
критериев
|
для примера 1
|
для примера 2
|
для примера 3
|
для примера 4
|
|
|
|
|
Суммарная
выручка
|
Июнь
|
|
Август
|
Август
|
|
|
|
815
|
>400
|
ЛОЖЬ
|
>=300
|
<=400
|
|
|
|
для
примера 1
|
|
|
|
|
|
|
Магазин
с минимальной суммарной выручкой
|
|
|
|
|
Магазин
|
Суммарная
выручка
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
для примера 2
|
|
|
|
|
|
|
|
Магазины
с выручкой за июнь более 400 млн.рублей
|
|
|
|
Магазин
|
Июнь
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
для примера 3
|
|
|
|
|
|
|
|
Магазины,
у которых выручка за июнь превышает выручку за июль
|
|
Магазин
|
Июнь
|
Июль
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
для
примера 4
|
|
|
|
|
|
|
Магазин
с выручкой за август от 300 до 400 млн. рублей
|
|
|
|
Магазин
|
Август
|
|
|
|
|
|
После создания данной
таблицы для того, чтобы выводились заданные условиями ответы, необходимо
выполнить условия Фильтр / Расширенный Фильтр. После чего в появившемся поле
задать условия для примеров (Табл. 2). В результате программа выдает заданные
условиями ответы.
Задание 3. Работа с базой данных.
Функция БД возвращает
данные из списка, которые удовлетворяют условиям, определенным диапазоном
критериев. Диапазон критериев включает копию названия поля (табл. 3 это средняя
выручка). На поставленную задачу примера 1, а именно вывести количество
магазинов, имеющих среднюю выручку более 400млн. руб., воспользуемся формулой
БДСЧЕТ и зададим необходимые для расчета параметры. В итоге получаем ответ: 2
магазина имеют выручку более 400млн. руб.
Для примера 2 используем
функции. БДСУММ. Задаем необходимые для расчета условия в поле мастера функций
и получаем сумму выручки магазинов 1 и 5 (2381млн. руб.).
Таблица 3.
Таблица
критериев и выходных документов
|
1.
Таблица критериев для 1 примера
|
|
|
|
|
|
Средняя
выручка
|
|
|
|
|
|
|
>400
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Количество
магазинов, имеющих среднюю выручку более 400млн. руб.
|
|
2
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
2.
Таблица критериев для 2 примера
|
|
|
|
|
|
Магазин
|
|
|
|
|
|
|
|
1
|
|
|
|
|
|
|
|
5
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Выручка
за июнь магазинов 1 и 5
|
|
|
|
|
|
2381
|
|
|
|
|
|
|
Задание 4. Построение диаграммы.
Для характеристики
суммарной выручки каждого магазина можно построить диаграмму. Данные для
составления диаграммы возьмем из таблицы 1.
Для этого необходимо
вызвать Мастер Диаграмм и в пошаговом режиме указать:
- тип диаграммы –
круговая;
- вариант круговой
диаграммы – объемная;
- ряд – Суммарная
выручка;
- имя – Е2(суммарная
выручка в таблице Excel);
- значения Е3:Е8;
- подпись категорий А3:А8
(номера магазинов в таблице Excel);
- подписи значений –
категории и доли;
- легенда – добавить
легенду (Рис.1).
Таким образом, получается,
доля суммарной выручки магазина 1 в общем объеме суммарной выручки составляет
18%, доля суммарной выручки магазина 2 в общем объеме суммарной выручки
составляет 16%, доля суммарной выручки магазина 3 - 19%, доля суммарной выручки
магазина 4 - 12%, доля суммарной выручки
магазина 5 - 18%, доля суммарной выручки магазина 6 - 17% (Табл. 1).
Рис. 1.
Построение диаграммы
Построение гистограммы.
Для сравнительного
анализа данных выручки магазинов (табл. 1) построим гистограмму.
Для этого нужно вызвать
Мастер Диаграмм и в пошаговом режиме ввести:
- тип диаграммы –
гистограмма;
- вид диаграммы –
обычная;
- диапазон данных В3:D8;
- ряд со значением В3:В8
имеет имя июнь (В3);
- ряд со значением C3:C8 имеет имя июль (C3);
- ряд со значением D3:D8 имеет имя август (D3);
- название диаграммы –
Выручка магазинов;
- ось Х (категорий) –
Номер магазина;
- осьУ (категорий) –
Объем выручки.
Из построенной гистограммы
видно, что по магазину 1 наибольшая выручка была в августе месяце (534млн.
руб.), а например по магазину 6 – наибольшая выручка в июне месяце (421млн.
руб.).
Рис. 2
Построение гистограммы