Автор работы: Пользователь скрыл имя, 24 Марта 2012 в 17:09, контрольная работа
Задание 1. Вычислить:
суммарную выручку по каждому магазину;
среднее значение выручки по каждому магазину;
итоговые значения выручки по всем магазинам за каждый месяц;
общая сумма выручки по всем магазинам за все месяцы;
место магазина по объему продаж;
процент суммарной выручки для каждого магазина в общем объеме выручки;
размер премии, выданной каждому магазину, исходя из следующих условий:
Создать отчетную ведомость о результатах работы сети магазинов.
1. Спроектируем форму ЭТ и введем в нее исходные данные:
Задание 1. Вычислить:
суммарную выручку по каждому магазину;
среднее значение выручки по каждому магазину;
итоговые значения выручки по всем магазинам за каждый месяц;
общая сумма выручки по всем магазинам за все месяцы;
место магазина по объему продаж;
процент суммарной выручки для каждого магазина в общем объеме выручки;
размер премии, выданной каждому магазину, исходя из следующих условий:
за первое место по объему продаж магазин получает премию в размере 20%;
за второе место – в размере 10%;
за третье место - в размере 5%;
остальным магазинам премия не начисляется.
Задание 2. Используя Расширенный Фильтр определить:
у каких магазинов суммарная выручка минимальная;
у каких магазинов выручка за июнь больше 400 млн. рублей;
у каких магазинов выручка за июнь превышает выручку за июль;
у каких магазинов выручка за август составляет от 300 до 400 млн. рублей.
Задание 3. Используя Функции базы данных определить:
сколько магазинов имеют среднюю выручку более 400 млн. рублей;
чему равна выручка 1 и 5 магазинов за июнь.
2. Математическое описание алгоритма:
Задание 1
суммарная выручка по каждому магазину рассчитывается по формуле:
, i = 1,6
где (SV) i - суммарная выручка i - го магазина.
S j - выручка магазина за j -тый месяц.
итоговое значение выручки по всем магазинам за месяц рассчитывается по формуле:
, i = 1,3
где (IS)i - итоговая сумма выручки за i - тый месяц
(VM)j - выручка j- го магазина за месяц.
общая сумма выручки рассчитывается по формуле
, где
(SV)i - сумма выручки i - го магазина
Все суммы определяются с помощью функции СУММ.
среднее значение выручки по каждому магазину рассчитывается по формуле
, i = 1,6
где (SR) i - средняя выручка i - го магазина за все месяцы
(VM)j - выручка j - го магазина за месяц.
Среднее значение выручки определяется с помощью функции СРЗНАЧ.
место магазина по объему продаж представляет собой ранг числа в списке чисел.
Ранг числа - это его величина относительно других значений в списке. Если список отсортирован, то ранг числа является его позицией.
В Excel ранг числа определяется с помощью функции РАНГ.
процент суммарной выручки магазина в общем объеме выручки определяется по формуле
, i = 1,6
где Pi - процент выручки магазина в общем объеме выручки;
OS - общая сумма выручки по всем магазинам за все месяцы;
(SV)i - сумма выручки i - го магазина за все месяцы.
размер премии определяется с помощью логической функции ЕСЛИ.
3. Электронная таблица с формулами:
Задание 2
Для формирования выборки по условию в Excel используется Расширенный Фильтр.
Для этого в свободной области ЭТ создается таблица критериев и выходной документ.
В таблице критериев указываются имена полей из ЭТ, по которым осуществляется поиск и условие поиска.
В выходном документе указываются имена тех полей из ЭТ, для которых осуществляется поиск.
Выходной документ, как и всякая электронная таблица должна иметь название.
ПРИМЕР. Определить, у каких магазинов суммарная выручка минимальная?
1) Создадим таблицу критериев (условие фильтрации). Для этого из ЭТ скопируем в клетку А14 имя поля, по которому будет осуществляться поиск, а в клетку А15 запишем условие поиска:
А
Диапазон таблицы критериев А14 : А15.
2) Спроектируем выходной документ, включающий в себя заголовок документа и имена полей из ЭТ, в которые будут записываться результаты фильтрации:
А В
Диапазон выходного документа А19 : B19.
Количество строк выходного документа определяется автоматически.
3) Выберите команду ДАННЫЕ – ФИЛЬТР - РАСШИРЕННЫЙ ФИЛЬТР и заполните поля в окне диалога:
ПРИМЕР. Определить, у каких магазинов выручка за июнь более 400 млн.руб.
1) Создадим таблицу критериев диапазоне В14 : В15
А В
2) Спроектируем выходной документ в диапазоне А23 : В23
А В С D E
3) Выберите команду ДАННЫЕ –ФИЛЬТР- РАСШИРЕННЫЙ ФИЛЬТР и заполните поля в окне диалога:
ПРИМЕР. Определить у каких магазинов выручка за июнь превышает выручку за июль.
1) создадим таблицу критериев в диапазоне С14 : С15 (при сравнении полей имя поля в таблице критериев не указывается)
A B C
2) Спроектируем выходной документ в диапазоне А28 : С28
A B C D
3) Выберите команду ДАННЫЕ – ФИЛЬТР - РАСШИРЕННЫЙ ФИЛЬТР и заполните поля в окне диалога:
ПРИМЕР. Определить, у каких магазинов выручка за август составляет от 300 до 400 млн. руб.
1) Создадим таблицу критериев в диапазоне D14 : E15
A B C D E
2) Спроектируем выходной документ в диапазоне A34 : B34
A B C D E F
3) Выберите команду ДАННЫЕ – ФИЛЬТР - РАСШИРЕННЫЙ ФИЛЬТР и заполните поля в окне диалога:
Задание 3
Для определения количественных соотношений в Excel используются функции категории РАБОТА С БАЗОЙ ДАННЫХ.
Функции Баз Данных (БД) имеют структуру:
Имя функции БД (база_данных;поле;критерий),
база_данных – это интервал ячеек с именами полей
поле – номер столбца в ЭТ или адрес ячейки, содержащий имя поля
критерий – это ссылка на интервал ячеек, задающих условия для функции.
Функция возвращает данные из списка, которые удовлетворяют условиям, определенным диапазоном критериев. Диапазон критериев включает копию названия поля, для которого выполняется подведение итогов, в списке. Ссылка на критерий может быть введена как интервал ячеек или как имя интервала.
ПРИМЕР. Определить, сколько магазинов имеют среднюю выручку более 400 млн.руб.
1) Диапазон базы данных (БД) А3 : G9
2) Имя поля, по которому осуществляется вычисление, G3
3) Таблицу критериев сформируем в диапазоне А42 : А43
4)
Сформируем выходной документ
В клетку А46 запишем формулу: =БСЧЕТ(A3:G9;G3;A42:A43)
ПРИМЕР. Определить, чему равна общая выручка 1-го и 5-го магазинов за июнь
1) Диапазон базы данных (БД) А3 : Е9
2) Имя поля, по которому осуществляется вычисление, B3
3) Таблицу критериев сформируем в диапазоне А49 : А51
А В
4) Сформируем выходной документ
В клетку А54 запишем функцию =БДСУММ(A3:E9;B3;A49:A51)
В Excel диаграммы строятся с помощью Мастера Диаграмм.
1. Построить круговую диаграмму, характеризующую суммарную выручку каждого магазина.
Для построения круговой диаграммы необходимо выделить в ЭТ столбцы с данными (без «шапки») – А4:А9 и Е4:Е9 (для выделения несмежных столбцов – удерживайте нажатой клавишу CTRL).
Вызовите Мастер Диаграмм и в пошаговом режиме укажите:
тип диаграммы – круговая;
вариант круговой диаграммы - объемная;
ряды – в столбцах;
название диаграммы – Выручка магазинов;
подписи данных - доля;
легенда - добавить легенду.
поместить диаграмму на имеющемся листе.
Вид готовой диаграммы:
В Excel существует возможность сравнительного анализа информации, представленной в графическом виде. Примером такого анализа является гистограмма.
2. Построить гистограмму распределения выручки для каждого магазина за июнь, июль и август.
Для построения гистограммы необходимо выделить в ЭТ столбцы с данными (включая «шапку» ) – А3:D9.
Вызовите Мастер Диаграмм и в пошаговом режиме введите:
тип диаграммы – гистограмма;
вид диаграммы – обычная;
ряды - в столбцах;
название диаграммы - Выручка магазинов;
ось Х (категорий) - Номер магазина;
ось У (значений) - Объем выручки.
Диаграмму можно поместить на отдельном листе или на листе с ЭТ.
Вид готовой диаграммы:
x