 |
9. 2. Пример простейшей отчетной ведомости
|
|
|
|
9. 2. Пример простейшей отчетной ведомости
Предположим, что вы – менеджер торговой сети магазинов и вам необходимо составить отчетную ведомость по результатам ее деятельности за летний период с простейшей обработкой данных (рис 9. 8).
Рис. 9. 8. Отчет продаж сети магазинов
В этой отчетной ведомости надо определить:
− суммарную и среднюю выручку каждого из магазинов за отчетный период;
− суммарную выручку всех магазинов за каждый месяц отчетного периода;
− место, которое занимает каждый из магазинов в суммарном объеме выручки;
− долю каждого из магазинов в суммарном объеме выручки;
− количество магазинов, имеющих суммарную выручку до 100 млн. руб., от 1000 млн. руб. до 1500 млн. руб., от 1500 млн. руб. до 2000 млн. руб. и свыше 2000 млн. руб.
При составлении данного отчета необходимо выполнить следующее действия:
1. Для нахождения суммарной выручки первого магазина введите в ячейку Е3 формулу:
=СУММ(B3: D3)
2. Выберите ячейку Е3, расположите указатель мыши на маркере заполнения и переместите его вниз на диапазон Е4: Е8. Это позволит найти суммарную выручку каждого из магазинов.
3. Для нахождения суммарного объема выручки всех магазинов за июнь введите в ячейку В9 формулу:
=СУММ(B3: В8)
4. Выберите ячейку В9, расположите указатель мыши на маркере заполнения и переместите его вправо на диапазон С9: Е9. Это позволит найти суммарную выручку сети магазинов как за каждый месяц в отдельности, так и за весь период в целом.
5. Для нахождения средней выручки первого магазина введите в ячейку G3 формулу
=СРЗНАЧ(В3: D3)
6. Выберите ячейку G3, расположите указатель мыши на маркере заполнения и переместите его вниз на диапазон G4: G8. Это позволит найти среднюю выручку каждого из магазинов.
|
|
|
7. Для определения доли объема выручки первого магазина по отношению к суммарной выручке всей сети магазинов введите в ячейку Н3 формулу:
=Е3/$E$9
8. Выберите ячейку Н3, расположите указатель мыши на маркере заполнения и переместите его вниз на диапазон Н4: Н8. Это позволит найти долю объема выручки каждого из магазинов по отношению к суммарной выручке всей сети магазинов.
9. Выберите диапазон Н3: Н8 и нажмите кнопку Процентный формат панели инструментов Форматирование. Это позволит установить процентный формат в формат в ячейках выбранного диапазона.
|
10. Для определения места первого магазина в суммарной выручке всей сети магазинов введите в ячейку F3 формулу:
=РАНГ(Е3; $E$3: $E$8)
11. Выберите ячейку F3, расположите указатель мыши на маркере заполнения и переместите его вниз на диапазон F4: F8. Это позволит найти место каждого магазина в суммарной выручке всей сети магазинов.
12. Для определения количество магазинов, имеющих суммарную выручку до 1000 млн. руб., от 1000 млн. руб., от 1500 млн. руб. до 2000 млн. руб. и свыше 2000 млн. руб., сначала заполните ячейки I3: I5 согласно рис. 4. 11, а затем выберите диапазон J3: J6 и введите в него формулу массива (не забудьте завершить ее ввод нажатием комбинации клавиш < Ctrl> +< Shift> +< Enter> ):
{=ЧАСТОТА(Е3: Е8; I3: I5)}
При составлении отчетной ведомости использовались функции СРЗНАЧ, РАНГ и ЧАСТОТА.
Функция СРЗНАЧ находит среднее арифметическое значение из указанного диапазона ячеек.
Синтаксис:
СРЗНАЧ(число1; число2; …)
Функция РАНГ возвращает ранг числа в списке чисел. Ранг числа – это его величина относительно других значений в списке. (Если список отсортировать, то ранг числа будет его позицией. ).
Синтаксис:
РАНГ(число; ссылка; порядок)
− число – число, для которого определяется ранг;
− ссылка – массив или ссылка на список чисел. Нечисловые значения в ссылке игнорируются;
|
|
|
− порядок – число, определяющее способ упорядочения. Если значение параметра порядок равно 0 или он опущен, то Excel определяет ранг числа так, как если бы ссылка была списком, отсортированным в порядке убывания. Если значение параметра порядок является любым ненулевым числом, то Excel определяет ранг числа так, как если бы ссылка была списком, отсортированным в порядке возрастания.
Функция РАНГ присваивает одинаковым числам одинаковый ранг.
Функция ЧАСТОТА возвращает распределение частот в виде вертикального массива. Для данного множества значений и данного множества карманов (т. е. интервалов в математическом смысле) частотное распределение подсчитывает, сколько исходных значений попадает в каждый интервал.
Синтаксис:
ЧАСТОТА (массив_данных; двоичный_массив)
− массив_данных – массив или ссылка на множество данных, для которых вычисляются частоты; если массив_данных не содержит значений, то функция ЧАСТОТА возвращает массив нулей;
− двоичный_массив – массив или ссылка на множество интервалов, в которые группируются значения аргумента массив_данных. Если двоичный_массив не содержит значений, то функция ЧАСТОТА возвращает количество элементов в аргументе двоичный_массив.
Вычисление значений с использованием условия
При составлении отчетной ведомости часто приходится пользоваться функциями СЧЁТЕСЛИ, СУММЕСЛИ и ПРОСМОТР, которые позволяют анализировать данные с применением условий.
Функция СЧЁТЕСЛИ возвращает количество непустых ячеек внутри интервала, удовлетворяющих заданному критерию.
Синтаксис:
СЧЁТЕСЛИ (диапазон; условие)
− диапазон – интервал, в котором нужно подсчитать ячейки;
− условие – условие в форме числа, выражения или текста, которое определяет, какие ячейки надо подсчитывать; например, критерий может быть выражен следующим образом: 17, “17”, “> 17”, “Компьютер”.
Рассмотрим пример. Пусть ячейки диапазона А1: А4 содержат следующие значения: яблоки, апельсины, персики, яблоки. Тогда =СЧЁТЕСЛИ(А1: А4; ”яблоки”) возвращает 2.
Пусть ячейки В1: В4 содержат 32, 54, 75 и 86 соответственно. Тогда =СЧЁТЕСЛИ(В1: В4; ”> 50”) возвращает 3.
|
|
|
Функция СУММЕСЛИ суммирует ячейки, заданные критерием.
Синтаксис:
|
|
|
