Как построить логарифмический график в excel
Перейти к содержимому

Как построить логарифмический график в excel

  • автор:

Как создать логарифмический график в Excel

Как создать логарифмический график в Excel

Логарифмический график — это диаграмма рассеяния , в которой используются логарифмические шкалы как по оси X, так и по оси Y.

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

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

Пример: логарифмический график в Excel

Предположим, у нас есть следующий набор данных в Excel, который показывает значения двух переменных, x и y:

Используйте следующие шаги, чтобы создать логарифмический график для этого набора данных:

Шаг 1: Создайте диаграмму рассеяния.

Выделите данные в диапазоне A2:B11 .

На верхней ленте щелкните вкладку « Вставка ». В группе « Графики » нажмите « Разброс ».

Автоматически появится следующая диаграмма рассеяния:

Шаг 2: Измените шкалу оси x на логарифмическую.

Щелкните правой кнопкой мыши значения вдоль оси x и выберите « Формат оси» .

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

Логарифмическая шкала для графика в Excel

Шаг 3: Измените шкалу оси Y на логарифмическую.

Затем щелкните по оси Y и повторите тот же шаг, чтобы изменить шкалу оси Y на логарифмическую. Результирующий график будет выглядеть так:

Логарифмический график в Excel

Обратите внимание, что ось x теперь охватывает от 1 до 10, а ось y — от 1 до 1000. Также обратите внимание, что связь между переменными x и y теперь выглядит более линейной. Это указывает на то, что две переменные действительно имеют степенную зависимость.

Вы можете найти больше учебников по Excel здесь .

Задание логарифмической шкалы в отчете с разбивкой на страницы (построитель отчетов)

Если данные пропорциональны логарифму, в диаграмме в отчете с разбивкой на страницы можно использовать логарифмическую шкалу. Это может улучшить внешний вид диаграммы и повысить удобство работы с данными. Большинство логарифмических шкал использует логарифм с основанием 10.

Эта функция доступна только для оси значений. Ось значений обычно является вертикальной осью (осью Y). Однако в линейчатых диаграммах это горизонтальная ось (ось X).

Если ось логарифмическая, все остальные ее свойства будут логарифмически масштабированы. Например, если задать на оси логарифмическую шкалу с основанием 10, установка интервала шкалы, равного 2, на самом деле создаст интервалы, равные 10 в степени 2, то есть 100. Это означает, что будут отображены значения осей 1, 100, 10000, а не значения по умолчанию 1, 10, 100, 1000, 10000.

Создать и изменить определение для отчета на страницу (RDL-файл) можно с помощью построителя отчетов (Майкрософт), построителя отчетов Power BI и конструктора отчетов в SQL Server Data Tools.

Задание логарифмической шкалы

  1. Щелкните правой кнопкой мыши ось Y диаграммы и выберите пункт Свойства вертикальной оси. Откроется диалоговое окно Свойства вертикальной оси .
  2. В поле Свойства осивыберите Использовать логарифмическую шкалу.
  3. В текстовом поле Основание логарифма введите положительное значение для основания логарифма. Если значение не задано, по умолчанию берется логарифм с основанием 10.

Где в 8 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

Для прогнозирования нам необходимо рассчитать значения тренда, как для анализируемых значений, так и для будущих периодов.

При расчете значений логарифмического тренда нам будут известны:

  1. Время — значение по оси Х;
  2. Значение «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 - прогноз в Excel - точно, легко и быстро!

  • Novo Forecast Lite — автоматический расчет прогноза в Excel .
  • 4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
  • Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

  • Novo Forecast PRO — прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *