Лабораторная работа №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 |