Построение диаграммы для сравнения выручки магазинов за квартал

Лабораторная работа №3.

Оформление квартальной отчётной ведомости.

Задание

Набрать таблицу квартальной отчетной ведомости для сети магазинов в соответствии с вариантом задания и образцом таблицы. Выполнить обработку введенных данных, в результате которой заполняются столбцы: "Суммарная выручка", "Среднемесячная выручка", " Место магазина ", " Процент ".

На основании полученных результатов определить столбец " Диапазоны ". В этом столбце определяются подынтервалы, в пределах которых распределяется средняя выручка магазинов сети. На основании значений, определенных в этом столбце формируется столбец " Частота ", в котором определяется частота попадания значений полученных в столбце " Среднемесячная выручка " в подынтервалы, выделенные в столбце " Диапазоны ".

Методика выполнения работы

1. Переименовать лист, на котором будет выполнена лабораторная работа.

2. Заголовок таблицы вводится в ячейку А1.

3. В ячейку А2 вводится номер квартала, определенный в задании.

4. В ячейку А4 ввести текст " Магазин 1 ".

5. "Протянуть" текст, введенный в ячейку А4, по ячейкам столбца А до получения строки с текстом " Магазин N ", где N равно числу магазинов, определенному в задании.

6. В ячейку В3 ввести название первого месяца квартала, указанного в задании.

7. "Протянуть" это название, обеспечив появление названий трех месяцев квартала.

8. Для формирования шапки таблицы ввести тексты:

· в ячейку Е3 " Суммарная выручка ";

· в ячейку F3 " Среднемесячная выручка ";

· в ячейку G3 " Место магазина ";

· в ячейку H3 " Процент ";

· в ячейку I3 " Диапазоны ";

· в ячейку J3 " Частота ".

9. В ячейку А(4+N) ввести текст " Итого ".

10. Введите числовые данные в ячейки от В4 до D(4+N-1).

11. Выделите ячейки от В4 до E(4+N) и нажмите на панели инструментов " Стандартная " кнопку " Автосумма ". Проверьте правильность суммирования, выполненного в столбце " Суммарная выручка " и в строке " Итого ".

12. Определите значения в ячейках столбца " Среднемесячная выручка ". Для этого в ячейку F4, используя мастера формул, введите формулу

=СРЗНАЧ(В4:D4)

Справка. Функция СРЗНАЧ возвращает среднее (арифметическое) своих аргументов.

Синтаксис

СРЗНАЧ (число1; число2;...)

Число1, число2,... - это от 1 до 30 аргументов, для которых вычисляется среднее.

Замечания

• Аргументы должны быть числами или именами, массивами или ссылками, содержащими числа.
• Если аргумент, который является массивом или ссылкой, содержит тексты, логические значения или пустые ячейки, то такие значения игнорируются; однако, ячейки, которые содержат нулевые значения, учитываются.

Совет. Вычисляя средние значения ячеек, следует учитывать различие между пустыми ячейками и ячейками, содержащими нулевые значения, особенно если не установлен флажок Нулевые значения на вкладке Вид (команда Параметры, меню Сервис). Пустые ячейки не учитываются, но нулевые ячейки учитываются.

Примеры

Если ячейки A1:A5 имеют имя Баллы и содержат числа 10, 7, 9, 27 и 2, то:

СРЗНАЧ(A1:A5) равняется 11

СРЗНАЧ(Баллы) равняется 11

СРЗНАЧ(A1:A5; 5) равняется 10

СРЗНАЧ(A1:A5) равняется СУММ(A1:A5)/СЧЁТ(A1:A5) и равняется 11

Если ячейки C1:C3 имеют имя ДругиеБаллы и содержат числа 4, 18 и 7, то:

СРЗНАЧ(Баллы; ДругиеБаллы) равняется 10,5

13. Протяните эту формулу по всем ячейкам столбца F от 4-й строки до строки с текстом " Итого ".

14. Для определения места магазина, определяемого по величине среднемесячной выручки, введем, используя " Мастер формул ", в ячейку G4 формулу

=РАНГ(F4;$F$4:$F$(4+N-1))

и протянем ее до строки (4+N-1). (В приводимом примере " =РАНГ(F4;$F$4:$F$8)".)

Справка

Функция РАНГ, относящаяся к категории статистических функций, возвращает ранг числа в списке чисел. Ранг числа - это его величина относительно других значений в списке. (Если список отсортировать, то ранг числа будет его позицией.)

Синтаксис

РАНГ (число; ссылка;порядок)

Число - это число, или ссылка на ячейку со значением числа, для которого определяется ранг.

Ссылка - это массив или ссылка на массив чисел. Нечисловые значения в ссылке игнорируются.

Порядок - это число, определяющее способ упорядочения.

· Если порядок равен 0 (нулю) или опущен, то Microsoft Excel определяет ранг числа так, как если бы ссылка была списком, отсортированным в порядке убывания.

• Если порядок - это любое ненулевое число, то Microsoft Excel определяет ранг числа так, как если бы ссылка была списком, отсортированным в порядке возрастания.

Замечания

РАНГ присваивает повторяющимся числам одинаковый ранг. Однако наличие повторяющихся чисел влияет на ранг последующих чисел. Например, для списка целых, если число 10 появляется дважды и имеет ранг 5, то 11 будет иметь ранг 7 (и никакое число не будет иметь ранг 6).

Примеры

Если ячейки A1:A5 содержат числа 7, 3,5, 3,5, 1 и 2 соответственно, то:

РАНГ(A2;A1:A5;1) равняется 3

РАНГ(A1;A1:A5;1) равняется 5

15. Для того чтобы определить процент, который представляет выручка i - го магазина в суммарной выручке всех магазинов, введем в ячейку Н4 формулу

=Е4/$Е$(4+N)

и протянем её по столбцу Н до строки (4+N). После чего преобразуем формат числовых данных, выделенных при протягивании ячеек, в процентный.

(Меню Формат è строка "Ячейки…" è ОД "Формат ячеек" è вкладка "Число" è в поле "Числовые форматы" выбираем строку "Процентный")

16. В столбце, отмеченном как " Диапазоны ", определим интервалы, в которые попадают значения среднемесячных выручек. В приведенном нами примере это интервалы: от 0 до 2500, от 2500 до 3000, от 3000 до 3500, от 4000 до 4500 и свыше 4500. Для определения частот попадания значений в определенные подынтервалы в столбце J - " Частота " выделяем столбец ячеек, соответствующий числу подынтервалов. Используя " Мастер формул ", вводим в ячейку J4 формулу

=ЧАСТОТА(F4:F8;I4:I9)

и для выполнения векторной операции нажимаем кнопки Ctrl+Shift+Enter.

Справка

Функция ЧАСТОТА вычисляет частоту появления значений в интервале значений и возвращает массив цифр. Функция ЧАСТОТА может быть использована, например, для подсчета количества результатов тестирования, попадающих в интервалы результатов. Поскольку данная функция возвращает массив, она должна задаваться в качестве формулы массива.

Синтаксис

ЧАСТОТА (массив_данных; массив_карманов)

Массив_данных — это массив или ссылка на множество данных, для которых вычисляются частоты. Если массив_данных не содержит значений, то функция ЧАСТОТА возвращает массив нулей.

Массив_карманов — это массив или ссылка на множество интервалов, в которые группируются значения аргумента массив_данных. Если массив_карманов не содержит значений, то функция ЧАСТОТА возвращает количество элементов в аргументе массив_данных.

Замечания

· ЧАСТОТА вводится как формула массива после выделения интервала смежных ячеек, в которые нужно вернуть полученный массив распределения.

· Количество элементов в возвращаемом массиве на единицу больше числа элементов в массив_карманов. Дополнительный элемент в возвращаемом массиве содержит количество значений, больших, чем максимальное значение в интервалах. Например, при подсчете трех диапазонов значений (интервалов), введенных в три ячейки, убедитесь в том, что функция ЧАСТОТА возвращает значения в четырех ячейках. Дополнительная ячейка возвращает число значений в массив_данных, больших, чем значение границы третьего интервала.

· ЧАСТОТА игнорирует пустые ячейки и тексты.

· Формулы, которые возвращают массивы, должны быть введены как формулы массивов.

Пример

Предположим, что на рабочем листе перечислены результаты тестирования в баллах. Баллы 79, 85, 78, 85, 83, 81, 95, 88 и 97 введены в ячейки A1:A9 соответственно. Тогда аргумент массив_данных содержит столбец этих баллов. Аргумент массив_карманов будет другим столбцом, задающим интервалы, в которые должны быть сгруппированы данные. В данном примере массив_карманов — это интервал ячеек C4:C6, который будет содержать значения 70, 79, 89. Если ввести функцию ЧАСТОТА как формулу массива, то можно подсчитать количество результатов тестирования, попадающих в интервалы 0-70, 71-79, 80-89 и 90-100. В этом примере предполагается, что все баллы — целые числа. Следующая формула вводится как формула массива после выделения четырех вертикально смежных ячеек для результата. Четвертое число (2) представляет собой счетчик значений (95 и 97), которые больше чем граница наибольшего интервала (89).

ЧАСТОТА(A1:A9;C4:C6) равняется {0:2:5:2}

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

Построение диаграммы для сравнения выручки магазинов за квартал

1. Построение диаграммы начинаем с выделения ячеек столбца А, в которых записаны названия магазинов, и столбца с суммарными выручками. (В приводимом примере это ячейки А4:А8 и ячейки Е4;Е8.)

Указатель мыши (УМ) в ячейку А4 è фиксируем левую клавишу мыши (ФЛКМ) и протягиваем УМ до ячейки А8, выделяя названия магазинов. Фиксируем клавишу Ctrl, переводим УМ в ячейку F4, ФЛКМ и протягиваем до ячейки F8, выделяя значения среднемесячной выручки магазинов.

2. Нажмите кнопку "Мастер диаграмм" на панели инструментов (ПИ) " Стандартная " или в меню " Вставка ". Выберите строку " Диаграмма …". Открывается окно " Мастердиаграмм (шаг 1 из 4): тип диаграммы ". На вкладке " Стандартные " выбираем тип диаграммы " Круговая " после чего в поле образцов диаграмм выбираем один из образцов, например, " Объемный вариант разрезанной круговой диаграммы ", и нажимаем кнопку " Далее ".

3. В окне " Мастердиаграмм (шаг 2 из 4): источник данных диаграммы " на вкладке " Диапазон данных " проверяем установку переключателя " Ряды в: ". Должен быть включен переключатель "столбцах". Нажимаем кнопку" Далее ".

4. В окне " Мастердиаграмм (шаг 3 из 4): параметры диаграммы " на вкладке "Заголовки" в поле ввода "Название диаграммы" вводим текст, который появляется в верхней части окна просмотра диаграммы.

5. На вкладке " Легенда " выключаем флажок " Добавить легенду " и переходим на вкладку " Подписи данных ". На этой вкладке среди переключателей группы " Подписи значений " выбираем и включаем переключатель " категория и доля ". Нажимаем кнопку" Далее ".

6. В окне " Мастердиаграмм (шаг 4 из 4): размещение диаграммы" проверяем состояние переключателей группы " Поместить диаграмму на листе: ". Должен быть включен переключатель " имеющемся ". После чего нажимаем кнопку" Готово ".

Дальнейшие шаги связаны с форматированием отдельных элементов диаграммы, появившейся на листе книги.

7. Переместим диаграмму под таблицу и согласуем ее размер с размером таблицы. Например, так, как показано на образце документа. Приступим к созданию ещё одной диаграммы, характеризующей распределение выручки торга по месяцам квартала.

Выделим в строке "Итого" ячейки (В9:D9) и нажмём кнопку " Мастердиаграмм" на ПИ " Стандартная ".

8. В окне " Мастердиаграмм (шаг 1 из 4): тип диаграммы ". На вкладке " Стандартные " выбираем тип диаграммы " Гистограмма ". В поле образцов диаграмм выбираем один из образцов, например, " Обычная гистограмма отображает значения различных категорий ", и нажимаем кнопку " Далее ".

9. В окне " Мастердиаграмм (шаг 2 из 4): источник данных диаграммы " на вкладке " Диапазон данных " проверяем установку переключателя " Ряды в: ". Должен быть включен переключатель "строках". Переходим на вкладку "Ряд" и в поле ввода "Подписи оси х" создаем ссылку на диапазон ячеек (В3:D3), в которых записаны названия месяцев отчётного квартала. Нажимаем кнопку" Далее ".

10. В окне " Мастердиаграмм (шаг 3 из 4): параметры диаграммы " на вкладке "Заголовки" в поле ввода "Название диаграммы" вводим текст "Распределение выручки торга по месяцам квартала", который появляется в верхней части окна просмотра диаграммы.

На вкладке " Легенда " выключаем флажок " Добавить легенду " и нажимаем кнопку" Далее ".

11. В окне " Мастердиаграмм (шаг 4 из 4): размещение диаграммы" проверяем состояние переключателей группы " Поместить диаграмму на листе: ". Должен быть включен переключатель " имеющемся ". После чего нажимаем кнопку" Готово ".

12. Согласуем положение и размеры вновь созданной диаграммы с положением и размерами таблицы и диаграммы, уже имеющимися на листе. Выполним редактирование отдельных элементов диаграммы.

13. Последним этапом редактирования, созданного документа, является подготовка его к печати. Для этого используется инструмент "Предварительный просмотр", вызываемый либо из меню "Файл", либо нажатием соответствующей кнопки на ПИ "Стандартная"

Замечание. Перед работой в режиме предварительного просмотра следует закончить работу в режиме редактирования диаграммы. Для этого выведем УМ из поля диаграммы и ЩЛК.

Варианты заданий

№	Число магазинов	Отчётный квартал	№	Число магазинов	Отчётный квартал
1	4	2	10	5	3
2	5	3	11	4	4
3	6	4	12	6	1
4	4	1	13	5	2
5	5	2	14	4	3
6	6	3	15	6	4
7	4	4	16	5	1
8	5	1	17	4	2
9	6	2	18	6	3