Как создать логарифмический график в 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 здесь .
Задание логарифмической шкалы в отчете с разбивкой на страницы (построитель отчетов)
Если данные пропорциональны логарифму, в диаграмме в отчете с разбивкой на страницы можно использовать логарифмическую шкалу. Это может улучшить внешний вид диаграммы и повысить удобство работы с данными. Большинство логарифмических шкал использует логарифм с основанием 10.
Эта функция доступна только для оси значений. Ось значений обычно является вертикальной осью (осью Y). Однако в линейчатых диаграммах это горизонтальная ось (ось X).
Если ось логарифмическая, все остальные ее свойства будут логарифмически масштабированы. Например, если задать на оси логарифмическую шкалу с основанием 10, установка интервала шкалы, равного 2, на самом деле создаст интервалы, равные 10 в степени 2, то есть 100. Это означает, что будут отображены значения осей 1, 100, 10000, а не значения по умолчанию 1, 10, 100, 1000, 10000.
Создать и изменить определение для отчета на страницу (RDL-файл) можно с помощью построителя отчетов (Майкрософт), построителя отчетов Power BI и конструктора отчетов в SQL Server Data Tools.
Задание логарифмической шкалы
- Щелкните правой кнопкой мыши ось Y диаграммы и выберите пункт Свойства вертикальной оси. Откроется диалоговое окно Свойства вертикальной оси .
- В поле Свойства осивыберите Использовать логарифмическую шкалу.
- В текстовом поле Основание логарифма введите положительное значение для основания логарифма. Если значение не задано, по умолчанию берется логарифм с основанием 10.
Как сделать логарифмический график в excel
Приветствую!
Столкнулся с интересной проблемой. Поиски по интернету результата не дали. Итак суть проблемы.
Можно построить график по любым значениям.
Можно в свойствах оси — выбрать значение Логирифмическая ось.
Можно перевести график в точечный и выбрать логарифмическая ось — для оси Х для простых значений.
Можно в конце концов перевести значения ячеек по функции LOG(х,n) и построить график по ним.
Внимание вопрос.
Как построить адекватную ось Х (именно горизонтальную!) для формата ДАТА (время) ?
Упорно выдает полную дичь. Формат ДАТА — никак не принимается логарифмическими функциями. Над ним нельзя сделать какие то вычисления.
Но график то нужен .
Ось Х, формат Даты, в логарифмическом масштабе.
Именно исторические даты (число, месяц, год). А не просто ряд 1-10-100-1000 лет
Про ось У — не пишите, там все понятно. Интересует ТОЛЬКО горизонтальная в формате времени в логарифмическом масштабе.
Зачем?
Задача. Надо построить исторический график каких то значений.
Ось Х — логарифмический формат (даты)
Ось У — логарифмический формат (простые значения). Это не проблема.
Т.е. по сути надо построить график loglog — логарифма логарифма
Ответа не нашел. Заранее спасибо.
Изменено: Rimzin — 30.10.2019 12:15:48
Пользователь
Сообщений: 1418 Регистрация: 22.12.2012
27.10.2019 13:28:03
Если даты правильные, то к ним можно применить логарифмический формат
| Цитата |
|---|
| Rimzin написал: исторические даты |
насколько исторические? Даты в числовом формате воспринимаются только после 01.01.1900 года
Пользователь
Сообщений: 9 Регистрация: 27.10.2019
27.10.2019 16:07:33
Секунда назад — это уже история ))
У меня вопрос не философский а конкретный
По прежнему актуален.
Пользователь
Сообщений: 1418 Регистрация: 22.12.2012
27.10.2019 16:14:46
Конкретный вопрос предполагает конкретный пример с конкретными датами
Пользователь
Сообщений: 9 Регистрация: 27.10.2019
27.10.2019 16:18:03
исторические даты в числовом формате с 01.01.1900 года
в виде логарифмической оси.
Как еще бОлее конкретнее описать?
Если кто не понимает что такое логарифмическая ось — логично предположить что он не сможет мне помочь ))
любая дата от LOG (Число.Мес.Год) — отображает только одну цифру 01.01.1900
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% и выше.