Получение элегантных или «хороших» значений осей на графиках с помощью Excel - функция поиска или формула

Question

Я пытаюсь автоматически рассчитать наиболее элегантный масштаб по оси Y (максимум и значение деления) для некоторых гистограмм. Я использую Excel, интегрированный с инструментом инструментальной панели (SAP Dashboards), чтобы отображать две диаграммы рядом для удобства сравнения. Мне нужно, чтобы эти диаграммы имели одинаковые масштабы по оси Y, чтобы их можно было легко сравнивать.

Вот картинка, поясняющая, что я имею в виду: две диаграммы, показывающие разные данные, но одинаково масштабированные для облегчения сравнения:

Ясно, что ось Y должна быть масштабирована, чтобы соответствовать наибольшему значению, будь то слева или справа. В этом случае самое большое значение находится на левом графике. Я использовал функцию Excel MAX, чтобы найти наибольшее значение (4668), а затем применил некоторую логику для а) получения элегантной верхней границы (4700) и значения деления (1200). Я использовал функцию LEN, чтобы оценить, сколько цифр имеет число, и функцию CEILING, чтобы округлить его до ближайшего «хорошего» целого числа.

К счастью, мой пакет инструментальной панели позволяет мне настраивать минимальное и максимальное значения по оси Y, а также деление. Я могу установить их, указывая на ячейку.

Мой вопрос, таким образом, заключается в том, как масштабировать эту «логику», чтобы получить элегантные значения max и деления для любого набора данных, будь то числа низкие или высокие. Excel делает то же самое, когда автоматически масштабирует ваши оси. Мне нужно повторить это, чтобы я мог сделать это программно в Excel.

Для значений от 0 до 10 осью max y может быть само значение.

11 - 15: I'd set the max to 15, which looks elegant.
16 - 20: 20
21 - 25: 25
etc (going up in 5s)
101 - 110: 110
111 - 120: 120
etc...
1001 - 1100: 1100

Как вы можете видеть, по мере увеличения значений в моих данных (которые могут сильно различаться) мне нужно элегантно выбрать верхнее значение y, которое будет хорошо смотреться на моем графике. Какой лучший способ сделать это?

Я также не нашел лучшего способа выбрать 4 или 5 значений деления, которые должны быть «хорошими» числами (например, кратными 2, или 5, или 10, или 20, или даже 2000).

Моя формула должна хорошо масштабироваться, во многом так же, как автоматическое масштабирование в Excel дает отличные результаты независимо от порядка входных данных. Кто-нибудь может подсказать, как лучше всего это сделать?

Answer 1

После долгих размышлений, я думаю, что я разработал решение моей проблемы масштабирования оси. Есть много похожих постов на других сайтах, но часто их масштабирование было довольно «нервным». Мое решение полностью настраиваемо, и я призываю всех использовать его и адаптировать его под свои нужды.

Решение можно найти в книге Excel, доступной здесь.

Процесс управляется простой справочной таблицей, которая делит все значения от 1 до 10 на приятные «куски». Я использовал кратные 4, 5, 6 и 7, хотя отмечу, что в Excel тоже используются кратные 9. Вот таблица соответствия:

  MaxValues   Ticks   Step  
 ----------- ------- ------ 
  10          5       2     
  8           4       2     
  7           7       1     
  6           6       1     
  5           5       1     
  4           4       1     
  3.5         7       0.5   
  3           6       0.5   
  2.5         5       0.5   
  2           4       0.5   
  1.8         6       0.3   
  1.5         5       0.3   
  1.4         7       0.2   
  1.2         6       0.2   
  1           4       0.25  

Для любого значения найдите самое близкое значение «maxvalue», и оно подскажет вам максимальное значение, отображаемое на вашей оси, а также количество тиков и размер тиков. Так, например, если наибольшее значение в ваших данных равно 7,5, моя система вернет 8 в качестве максимального значения для графика, разделенного на четыре тика по 2.

Конечно, он может обрабатывать значения меньше 1 и больше 10, используя некоторые функции log и pow . Проверьте сами: откройте электронную таблицу, поместите новое значение в желтое поле ввода и посмотрите, как эти значения обновляются.

Вы можете настроить список точек останова в соответствии с вашими потребностями. Я включил все формулы в электронную таблицу вместе с некоторыми описаниями, чтобы помочь.