Как в Excel создать диаграмму с динамикой темпов роста, где изменения показателей показаны стрелками?
Все просто — рисуем столбцы и добавляем к ним полосы повышения и понижения. Плюс рисуем графики с накоплением — первый для роста, второй график — уровень подписи.
1. Исходные данные
Предположим, нам нужно показать динамику темпов роста выручки:
Добавим в таблицу строку «подписи» — с суммой немного больше исходного значения. И строку «рост», где будет рассчитан прирост выручки к предыдущему периоду. В первой колонке проставляем #Н/Д для того, чтобы не значения этого столбца не выводились в диаграмме.
2. Вставляем гистограмму с накоплением
Выделяем таблицу с выручкой и новыми строками. Добавляем гистограмму с накоплением: меню Вставка → Гистограмма → Гистограмма с накоплением.
3. Рост и подписи превращаем в график с накоплением
Выделяем гистограмму мышкой, переходим в меню Конструктор → Изменить тип диаграммы → выбираем вид диаграммы Комбинированная, для данных «подписи» и «рост» выбираем тип диаграммы — график с накоплением. Благодаря этому график с малыми значениями «наложится» на график с большими значениями.
4. Добавляем подписи линии роста
Добавляем подписи для линии роста: выделяем на диаграмме линию роста, в меню переходим на вкладку Конструктор → Добавить элемент диаграммы → Метки данных → выбираем Слева.
Делаем линию роста на диаграмме невидимой: выделяем линию правой кнопкой мышки, нажимаем Формат ряда данных, назначаем тип линии = Нет линий.
5. Добавляем линии ряда данных, удаляем легенду
Выделяем столбцы гистограммы, переходим в меню Конструктор → Добавить элемент диаграммы → Линии → Линии ряда данных (если такая линия не появилась, проверьте, правильный ли у вас тип диаграммы — должна быть Гистограмма с накоплением).
Удаляем легенду.
6. Задаем тип стрелки
Задаем тип стрелки — выделяем линию правой кнопкой мышки → Формат линий ряда → задаем тип стрелки.
Готово! Подписи и эффекты добавлять по вкусу.
| Учебный курс | Математическая статистика и Статистика |
Анализ интенсивности динамики
Цель – получение навыков расчета показателей динамики в MS Excel.
Задание – с помощью MS Excel необходимо провести анализ рядов динамики, дать экономическую интерпретацию экономических показателей.
Для проведения маркетингового исследования необходимо проанализировать объем продаж продовольственных товаров в динамике.
Обсяг продажу продовольчих товарів
Объем продаж продовольственных товаров, тыс. грн.
Необходимо рассчитать все показатели динамического ряда(база сравнения – 2005 г), среднегодовые темпы роста и прироста по периодам:
Создадим нужную нам таблицу в MS Excel.
Определим абсолютные приросты. В ячейке С4 введем формулу =B4-$B$3 и растянем формулу на весь столбец.
В ячейку D5 (для 2005 года невозможно рассчитать цепным способом абсолютный прирост, потому что нет предыдущего периода – 2004 года ) используем формулу =B5-B4 и тоже растягиваем на весь столбец. Ниже представлены результаты расчета:
Определим темп роста:
В ячейке Е4 вводим формулу вводим формулу =B4/$B$3*100, растягиваем формулу на весь столбец.
В ячейке F5 (для 2005 года темп роста, как и абсолютный, невозможно рассчитать цепным способом, потому что нет предыдущего периода – 2004 года) вводим формулу =B5/B4*100 и растягиваем на весь столбец.
Определим темп прироста:
В ячейке G4 вводим формулу =E4-100 и растягиваем на весь столбец.
В ячейке H5 вводим формулу =F5-100 и тоже растягиваем формулу на весь столбец.
Определим абсолютное значение 1% прироста:
В ячейке I5 вводим формулу =B4/100. Растягиваем формулу на весь столбец. Внизу представлены результаты вычислений:
Определим средние показатели динамики.
Определение среднего уровня ряда. Так как у нас есть пропущенные уровни ряда, то средний уровень ряда целесообразно рассчитывать за период 2008-2012 годов. В ячейке В10 выбираем встроенную функцию СРЗНАЧ и выбираем нужный нам диапазон.
Найдем средний абсолютный прирост – в ячейке В11 введем формулу =(B8-B3)/8.
Найдем средний темп роста.
В ячейке В14 вводим формулу =(B4/B3)^(1/3).
Базисным способом – в ячейке В16 вводим формулу =(B8/B4)^(1/4).
Цепным способом – в ячейке B17 вводим формулу =((F5/100)*(F6/100)*(F7/100)*(F8/100))^(1/4).
Базисным способом – в ячейку В19 вводим формулу =(B8/B3)^(1/7).
Цепным способом – по формуле средней геометрической взвешенной: в ячейке В20 вводим формулу =((B14)^3*(B16)^4)^(1/7).
Найдем средний темп прироста
1. 2005-2008: в ячейке С14 вводим формулу =(B14-1)*100.
2. 2008-2012: в ячейке С16 вводим формулу =(B16-1)*100.
3. 2005-2012: в ячейке С20 вводим формулу =(B20-1)*100.
Результаты проведенных расчетов представлены ниже:
Таким образом на протяжении 2005-2008 годов наблюдалось постоянный рост объемов инвестиций, только в 2012 году объем инвестиций снизился по сравнению с 2011 годом на 25 тысяч гривен (по сравнению с 2005 годом увеличился на 15 тысяч гривен). Темп роста в 2012 году, рассчитанный базисным способом составляет 104,11%, что означает увеличение объема производства на 4,11%. Цепной темп прироста показал уменьшение объема производства по сравнению с 2011 годом на 6,17%. В 1
% прироста в 2012 году вмещалось 405 гривен. В период 2005-2012 объем инвестиций ежегодно увеличивался в среднем на 1875 гривен или на 0,58%.
Есть данные про объем инвестиций в маркетинговую компанию, в составе которой произошли изменения. Необходимо проанализировать объем инвестиций в период 2006-2012 годов.
Обсяг інвестицій в компанію
Объем инвестиций, тыс. грн.
Для этого задания существует два способа решения.
1 способ решения задания – выражения ряда динамики с помощью относительных показателей, взяв за базу период, в котором произошли изменения. Базой сравнения будет 2009 год.
В ячейке В6 вводим формулу =B3/$E$3*100. Растягиваем эту формулу до ячейки Е6.
В ячейке Н7 вводим формулу =H4/$E$4*100. Растягиваем эту формулу до ячейки Е7.
2 способ – перерасчет абсолютных показателей.
Для этого определяем в 2009 году коэффициент соотношения уровней двух рядов:
В ячейке B9 вводим формулу =E4/E3.
Перемножаем на этот коэффициент уровни первого ряда и получаем их соотношение с уровнями второго ряда.
2009 год – в ячейке В11 вводим формулу =B3*$B$9 и растягиваем эту формулу до ячейки D11.
Тогда получаем сравнительный ряд динамики объема производства в рамках концерна.
Ниже представлены полученные при вычислениях данные:
Для формирования маркетинговой стратегии необходимо провести динамический анализ выхода продукции предприятия А и основных факторов интенсивности производства за 2005-2009 года.
Прибыль, тыс. Грн..
Продуктивность труда, грн./чел.
Фонд заработной платы, тыс.грн.
Необходимо привести ряды динамики к одной основе, к общей базе сравнения.
Введем данные в диапазоне ячеек A1:F4.
Нужно осуществить сравнительный анализ трех представленных рядов динамики, используя их приведение к одной основе.
Приведем сравнительные ряды к одной основе, определив относительные уровни рядов: базисные темпы роста с постоянной базой сравнения – уровни за 2005 год:
В ячейке В7 вводим формулу =B2/$B$2*100, растягиваем эту формулу до ячейки F7.
В ячейке В8 вводим формулу =B3/$B$3*100, растягиваем эту формулу до ячейки F8.
В ячейке В9 вводим формулу =B4/$B$4*100, растягиваем эту формулу до ячейки F9.
Полученные данные в процентах приведены ниже:
Рассчитаем коэффициент опережения:
В ячейке В12 введем формулу =B7/B$9, растянем эту формулу до ячейки F12.
В ячейке В13 вводим формулу =B7/B$9, растягиваем эту формулу до ячейки F13.
Ниже представлены данные, полученные при вычислении:
Анализ таблиц приводит к таким выводам:
Сравнение темпов роста фонду заработной платы, прибыли и продуктивности труда говорят о опережающих темпах роста факторов результативности производства (в 1,16- 1,26 раз) по сравнению с темпами роста фонда заработной платы (ы 1,13 раз) на протяжении 2005-2008 годов и отстающий темп роста указанных показателей от фонда заработной платы в 2009 году. Это означает, что на предприятии до 2008 года имеет место позитивная динамика роста показателей результативности производства по сравнению к вложенным средствам в рабочую силу, но в 2009 году эта тенденция изменилась, что может объясняться кризисными явлениями в экономике, неэффективностью управления, непредсказуемыми затратами предприятия.
Увеличение прибыли по сравнению с увеличением фонда заработной платы составляло в относительном выражении 1.12 (1,2677:1,1333) в2008 году и 0,97 (1,1692:120,00) в 2009 году. Рост продуктивности труда по сравнению с ростом фонду заработной платы – 1,03 (1,1692:1,1333) в 2008 году и 0,99 (1,1923:120,00) в 2009 году.
Таким образом, темп роста факторов результативности производства (прибыльность, продуктивность труда) в 2009 году отставали от темпов роста фонда заработной платы. Это должно насторожить руководство и заставить принять все возможные меры для исправления ситуации в обратное направление.
Функция РОСТ используется для расчета прогнозируемого экспоненциального роста на основе принимаемых на вход известных массивов данных X и Y, и возвращает массив значений для зависимой переменной Y на основе полученных новых данных для массива независимой переменной X.
Метод прогнозируемого экспоненциального роста c использованием функции РОСТ
Пример 1. В ходе выполнения лабораторной работы студент должен определить зависимость между температурой количеством теплоты, содержащемся в веществе определенной массы. По условиям задания, необходимо провести 10 опытов, из которых было выполнено 8. Для получения остальных величин студент решил использовать метод прогнозируемого экспоненциального роста.
Таблица с исходными данными:
Выделяем диапазон ячеек B10:B11 и используем следующую функцию:
- B2:B9 – диапазон известных значений количества теплоты, полученные в результате проведения опытов;
- A2:A9 – диапазон температур, для которых проводились опыты;
- A10:A11 – диапазон температур, для которых необходимо вычислить предполагаемые значения количества теплоты.
Для ввода формулы используем комбинацию клавиш CTRL+SHIFT+Enter так как формула должна выполняться в массиве. В результате получим:
Визуально заметно явное несоответствие найденных величин диапазону уже известных значений. В Excel существует еще одна функция для прогнозирования на основе известных значений – ТЕНДЕНЦИЯ. Воспользуемся ей и сравним полученные результаты. Для этого выделяем диапазон ячеек C10:C11 и снова в массиве вводим функцию ТЕНДЕНЦИЯ:
Как видно, синтаксические записи функций РОСТ и ТЕНДЕНЦИЯ идентичны, однако они используют различные алгоритмы для вычислений. Для ввода функции ТЕНДЕНЦИЯ снова используем комбинацию клавиш CTRL+SHIFT+Enter. В результате получим:
То есть, в данном примере функция ТЕНДЕНЦИЯ дает более точный прогноз и целесообразно использовать именно ее.
Прогноз эффективности использования рекламного бюджета по функции РОСТ
Пример 2. За 10 дней до окончания 30-дневного месяца было решено определить общую прогнозируемую прибыль сети магазинов в месяц на основании имеющихся данных за прошедшие 20 дней, на протяжении которых прибыль за день в целом постоянно увеличивалась благодаря использованию эффективной рекламы. Необходимо рассчитать, превысит ли прибыль значение в 3 млн. рублей.
Для решения используем следующую формулу:
- B2:B21 – массив известных значений прибыли за день для первых 20 дней;
- A2:A21 – массив дней, для которых размер прибыли уже известен;
- A22:A31 – массив дней, для которых выполняется прогнозирование прибыли.
В результате имеем:
Для получения ответа на поставленный вопрос запишем следующую формулу:
=3000000;"Превысит 3 млн. руб";"Менее 3 млн. руб")’ > 
Прогноз прибыли за месяц с использованием функции РОСТ в Excel
Пример 3. Экономист развивающегося предприятия ведет учет прибыли, при этом в таблице содержатся три вектора данных: месяц, число сделок, общая сумма прибыли. Необходимо спрогнозировать прибыль на следующий месяц при двух условиях:
- Количество сделок будет равно показателю за предыдущий месяц;
- Количество сделок увеличится на 2.
Вводим функцию РОСТ и получаем ошибку #ЗНАЧ!:
Внимание! В данном случае для прогнозирования прибыли будет использовано сочетание двух факторов: номер месяца и число сделок. Поэтому в качестве аргумента [известные_значения_x] необходимо передать диапазон значений A2:B6, а в качестве аргумента [новые_значения_x] – диапазон A7:B7.
Для определения прибыли при условии, что число сделок составит 41, запишем следующую формулу:
Теперь увеличим количество сделок на 2-е:
Как и ожидалось, прогнозируемая прибыль увеличилась. Пример наглядно демонстрирует, что для увеличения точности предсказания можно использовать 2 и более зависящих друг от друга параметров.
Функция РОСТ в Excel и особенности ее использования
Функция РОСТ имеет следующую синтаксическую запись:
- известные_значения_y – массив данных, элементы которого характеризуют значения зависимой переменной y в уравнении y=bkx. Аргумент обязателен для заполнения.
- [известные_значения_x] – массив данных, элементы которого соответствуют известным значениям независимой переменной x в записи y=bkx. Аргумент является необязательным.
- [новые_значения_x] – массив с новыми значениями независимой переменной x, на основе которых функция выполняет расчет новых значений зависимой переменной y. Аргумент необязателен для заполнения.
- [конст] – данные логического типа (ИСТИНА или ЛОЖЬ), определяющие значение константы b в уравнении y=bkx. По умолчанию (если аргумент явно не указан), а также при явном указании логического ИСТИНА, коэффициент b вычисляется обычным способом. Если данный параметр принимает значение ЛОЖЬ, дальнейшие расчеты проводятся для уравнения y=kx, поскольку значение b принимается равным единице.
- Элементы массива известные_значения_y должны быть взяты из диапазона положительных чисел. При наличии отрицательных значений или значений, равных 0 (нулю), результатом выполнения функции РОСТ будет код ошибки #ЧИСЛО!.
- В качестве аргумента [известные_значения_x] может быть передано одно либо несколько множеств значений. Размерности множеств, передаваемых в качестве первого и второго аргументов должны совпадать, если используется единственная переменная. При вводе нескольких переменных в качестве аргумента известные_значения_y должен быть передан вектор. В Excel вектором считается интервал значений, высота которого составляет одну строку, либо ширина которого равна только одному столбцу).
- Функция РОСТ интерпретирует каждый столбец или каждую строку массива [известные_значения_x] в качестве отдельной переменной, если массив известные_значения_y содержит только один столбец или только одну строку соответственно.
- Если второй аргумент функции явно не указан, то по умолчанию используется массив данных <1;2;3;…;n>, размерность которого соответствует размерности массива известные_значения_y.
- Массив [новые_значения_x]должен быть аналогичен по своей структуре массиву [известные_значения_x], то есть содержать строку либо столбец для каждого элемента массива известные_значения_y.
- Если третий аргумент рассматриваемой функции явно не указан, считается, что он тождественен значению второго аргумента данной функции. Если второй и третий аргументы опущены, они оба являются массивами типа <1;2;3;…;n>с требованиями, указанными в пункте 4.
- Если массив значений передается в качестве константы массива, по правилам записи массивов в Excel необходимо использовать знак «;» для разделения значений, содержащихся в одной строке, и знак «:» для разделения строк.
- Функция РОСТ часто используется для аппроксимации (упрощения) значений независимой (x) и зависимой (y) переменных экспоненциальной кривой.
- Данная функция принадлежит к классу формул массивов, поэтому при ее использовании необходимо выделить соответствующее количество ячеек, а после ввода всех требуемых аргументов следует нажать сочетание клавиш Ctrl+Shift+Enter для корректного отображения результатов.
- В качестве функции экспоненциального роста используется уравнение типа y=bkx.
