Как создать полулогарифмический график в Excel
Полулогарифмический график — это тип графика, в котором используется логарифмическая шкала по оси Y и линейная шкала по оси X.
Этот тип графика часто используется, когда значения переменной y имеют гораздо большую изменчивость по сравнению со значениями переменной x.
Это часто происходит в наборах данных в области финансов, экономики, биологии и астрономии среди других областей.
В следующем пошаговом примере показано, как создать полулогарифмический график в Excel для заданного набора данных.
Шаг 1: введите данные
Во-первых, давайте введем значения для поддельного набора данных:
Шаг 2: Создайте диаграмму рассеяния
Затем выделите значения данных:
На верхней ленте нажмите « Вставить ».Затем щелкните первый вариант в разделе «График рассеяния »:
Автоматически отобразится следующая диаграмма рассеяния:
Из графика видно, что значения переменной y имеют гораздо более высокую изменчивость, чем значения переменной x.
Это означает, что рекомендуется преобразовать ось Y в логарифмическую шкалу, чтобы более эффективно визуализировать значения Y.
Шаг 3: Измените шкалу оси Y
Затем щелкните правой кнопкой мыши ось Y. В появившемся раскрывающемся меню нажмите « Формат оси» :
В появившемся окне в правой части экрана поставьте галочку рядом с Логарифмическая шкала :
Ось Y будет автоматически преобразована в логарифмическую шкалу:
Ось x остается на линейной шкале, но ось y была преобразована в логарифмическую шкалу.
Обратите внимание, насколько проще интерпретировать значения y на этом графике по сравнению с предыдущим графиком.
Как построить логарифмический график в excel
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Как создать логарифмический график в Excel
Логарифмический график — это диаграмма рассеяния , в которой используются логарифмические шкалы как по оси X, так и по оси Y.
Этот тип графика полезен для визуализации двух переменных, когда истинная связь между ними подчиняется степенному закону. Это явление происходит во многих областях реальной жизни, включая астрономию, биологию, химию и физику.
В этом руководстве показано, как создать логарифмический график для двух переменных в Excel.
Пример: логарифмический график в Excel
Предположим, у нас есть следующий набор данных в Excel, который показывает значения двух переменных, x и y:
Используйте следующие шаги, чтобы создать логарифмический график для этого набора данных:
Шаг 1: Создайте диаграмму рассеяния.
Выделите данные в диапазоне A2:B11 .
На верхней ленте щелкните вкладку « Вставка ». В группе « Графики » нажмите « Разброс ».
Автоматически появится следующая диаграмма рассеяния:
Шаг 2: Измените шкалу оси x на логарифмическую.
Щелкните правой кнопкой мыши значения вдоль оси x и выберите « Формат оси» .
В новом всплывающем окне установите флажок рядом с Логарифмический масштаб , чтобы изменить масштаб по оси X.
Шаг 3: Измените шкалу оси Y на логарифмическую.
Затем щелкните по оси Y и повторите тот же шаг, чтобы изменить шкалу оси Y на логарифмическую. Результирующий график будет выглядеть так:
Обратите внимание, что ось x теперь охватывает от 1 до 10, а ось y — от 1 до 1000. Также обратите внимание, что связь между переменными x и y теперь выглядит более линейной. Это указывает на то, что две переменные действительно имеют степенную зависимость.
Вы можете найти больше учебников по Excel здесь .
5 способов расчета логарифмического тренда в Excel. + О логарифмическом тренде и его применении.
Логарифмический тренд применяется для прогнозирования временного ряда, данные которого вначале быстро растут или убывают, а затем постепенно стабилизируются.
Например, выводим новый товар на рынок, за счет роста клиентской базы продажи быстро растут, затем мы набираем постоянных клиентов, продажи стабилизируются, и новые клиенты уже не основной фактор роста, а основной фактор роста — это развитие продаж постоянным клиентам.
Или вводим продукцию в новую торговую точку, и по истечении определенного периода решаем увеличить количество фейсов на полке (т.е. увеличить размер полки для одного вида товара) (фейс — это единица продукции, которая стоит лицом к покупателю) или продублировать выкладку в другой части зала. Почему здесь лучше использовать логарифм? Потому что увеличение количества фейсингов на полке в 2 раза по одной группе товаров, к сожалению, не ведёт к увеличению продаж в 2 раза, причём с ростом количества фейсов темп прироста продаж уменьшаются для каждого последующего фейса. Именно поэтому для прогнозирования продаж для этой ситуации лучше всего использовать логарифмический тренд.
Логарифмический тренд – это функция y(x)=a*ln(x)+b, где
Значение x – это номера периода во временном ряду (например, номер месяца, квартала, дня; См. статью о временных рядах.)
y – это последовательность значений , которые мы анализируем и прогнозируем (например, объём продаж по месяцам.)
b – точка пересечения с осью y на графике;
a – это значение, на которое увеличивается следующее значение временного ряда;
Причем, если a>0, то динамика роста положительная,
При построении логарифмического тренда используют как положительные, так и отрицательные данные временных рядов.
Рассмотрим логарифмический тренд на примере построения прогноза продаж в Excel по месяцам.
Временной ряд — продажи по месяцам по новому товару
В этом временном раде у нас есть 2 переменных
1. Время — месяцы— x;
2. Объёмы продаж по месяцам — y;
Уравнение логарифмического тренда y(x)=a*ln(x)+b, где y — это объёмы продаж, x — месяцы.
5 способов расчета логарифмического тренда в Excel.
Как в Excel мы можем рассчитать коэффициенты логарифмического тренда?
1-й способ — с помощью графика.
Строим график в Excel и видим по оси x — наш временной рад (1, 2, 3. — ноябрь, декабрь, январь . ), по оси y объёмы продаж + добавляем на график линию тренда и уравнение тренда.
Получаем уравнение тренда y=2 673 493 ln(x) + 2 913 282
Для прогнозирования нам необходимо рассчитать значения тренда, как для анализируемых значений, так и для будущих периодов.
При расчете значений логарифмического тренда нам будут известны:
- Время — значение по оси Х;
- Значение «a» и «b» уравнения логарифмического тренда y(x)=a*ln(x)+b;
Рассчитываем значения тренда для каждого анализируемого периода времени от 1 до 13, а также для будущих периодов с 14 месяца до 20.
Например, для 14 месяца значение тренда рассчитывается по следующей схеме: в уравнение подставляем x=14 и получаем y=2 673 493 ln(14) + 2 913 282=9 968 782
20-го y=2 673 493 ln(20) + 2 913 282=10 922 350
2-й способ — с помощью функции Excel =Линейн().
Для расчета коэффициентов логарифмического тренда воспользуемся функцией Excel =ЛИНЕЙН() .
Для этого в функцию =ЛИНЕЙН() введем:
1. известные значения y – объем продаж;
2. известные значения x – номера периодов, причём введенные, как LN(номера периодов);
3. константа – вводим 1 для расчёта коэффициента b уравнения y(x)=a*ln(x)+b;
4. Статистика — 1 или 0;
Формула будит выглядеть вот так =ЛИНЕЙН(C2:O2;LN(C1:O1);ИСТИНА;ИСТИНА)
Теперь формулу вводим как формулу массива, выделяем 2 ячейки (подробнее о формулах массива) и нажимаем F2, а затем одновременно — клавиши CTRL + SHIFT + ВВОД.
Коэффициенты «а» и «b» логарифмического тренда y(x)=a*ln(x)+b рассчитаны;
Получаем уравнение тренда y=2673492*ln(x)+2913281
Для прогнозирования нам необходимо продлить линию тренда и определить её значения. При её продлении нам будет известен только один параметр — это время, т.е. значения по оси X.
Рассчитываем значения тренда с 1-го месяца (ноябрь) до 20 (июнь)— y=2673492*ln(14)+2913281=9968782
3-й способ — с помощью функции Excel =ТЕНДЕНЦИЯ().
Расчет значений логарифмического тренда с помощью функции Excel =ТЕНДЕНЦИЯ().
Для этого в функцию =ТЕНДЕНЦИЯ() вводим:
1. Известные значения y — объёмы продаж за анализируемый период;
2. Известные значений x — порядковые номера периодов (месяцев), причем введенные как LN(Известные значений x);
3. Новые значения x— порядковые номера периодов, для которых хотим рассчитать значения трендов, причем введенные как LN(Новые значения x);
4. Константа — ставим «1», если хотим рассчитать значения тренда y(x)=a*ln(x)+b с коэффициентом b.
Формула будет выглядеть вот так =ТЕНДЕНЦИЯ(C4:O4;LN(C2:O2);LN(Q2:W2);1)
Затем, вводим формулу =ТЕНДЕНЦИЯ(), как формулу массива. Для этого
1. Выделяем диапазон ячеек с 1-го по 20-й период, в первой ячейке введена формула =ТЕНДЕНЦИЯ();
2. Нажимаем F2, а затем одновременно — клавиши CTRL + SHIFT + ВВОД.
Значения логарифмического тренда с помощью формулы Excel =тенденция() рассчитаны.
4-й способ — функция Excel =предсказ().
Расчёт значений логарифмического тренда — с помощью функции Excel =предсказ()
Для этого вводим в функцию =предсказ(
1. X — номер периода, для которого рассчитываем прогноз, причем вводим как LN(x);
2. Известные значения y — объёмы продаж по месяцам, фиксируем диапазон, выделяем его и нажимаем F4. Получаем ссылку, как на картинке:
3. Известные значения x — порядковые номера периодов, для которых хотим рассчитать значения логарифмического тренда, причем вводим как LN(Известные значения x) + фиксируем выделенный диапазон, выделяем его и нажимаем F4;
Получаем формулу =ПРЕДСКАЗ(LN(Q2);$C$4:$O$4;LN($C$2:$O$2))
Протягиваем формулу, значения логарифмического тренда рассчитаны.
5-й способ — Forecast4AC PRO
Расчет значений логарифмического тренда — с помощью программы Forecast4AC PRO.
1. Устанавливаем курсор в начало временного ряда, выбираем в настройках программы:
— Что рассчитываем — значения тренда;
— Тренд – Логарифмический тренд;
— Временной ряд — месячный;
2. Заходим в меню программы и нажимаем «Start_Forecast» — готово, значения логарифмического тренда рассчитаны!
Для того чтобы рассчитать прогноз с учетом роста и сезонности, мы умножаем рассчитанные значения тренда на коэффициенты сезонности.
Коэффициенты сезонности рассчитаем с помощью программы Forecast4AC PRO (лист » Лист2FYMLn «) или по аналоги, как описано в данной статье, только для рассчета коэффициентов сезонности вместо линейного тренда используем логарифмический.
Теперь значения тренда умножаем на коэффициенты сезонности и прогноз готов.
Отношение прогноза к предыдущему периоду получилось 116%, т.е. прогнозируется рост на 16%.
Как мы можем скорректировать прогнозные значения логарифмического тренда?
Если нас рост не устраивает, и мы планируем, что он будет больше, мы можем увеличить рост, скорректировав коэффициенты логарифмического тренда.
Скорректируем значение «a» и «b» рассчитанного нами выше тренда y=2673492*ln(x)+2913281
При изменении значений «a» и «b» логарифмического тренда y(x)=a*ln(x)+b, получаем увеличение значений тренда, причем увеличение коэффициента «а» на 10% даёт больший рост, чем увеличение коэффициента «b» на 20%.
Теперь рассчитаем коэффициенты сезонности для логарифмического тренда с помощью Forecast4AC PRO (лист » Лист2FYMLn «). Умножим скорректированные значения тренда на сезонность. Также при прогнозировании стоит учесть дополнительные факторы, которые значительно влияют на объём продаж. Прогноз продаж готов!
С помощью программы Forecast4AC PRO вы сможете в Excel одним нажатием клавиши рассчитать значения логарифмического тренда, коэффициенты сезонности и прогноз для более чем 5000 строк одновременно.
Точных вам прогнозов!
Присоединяйтесь к нам!
Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:
- Novo Forecast Lite — автоматический расчет прогноза в Excel .
- 4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
- Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.
Тестируйте возможности платных решений:
- Novo Forecast PRO — прогнозирование в Excel для больших массивов данных.
Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.