Wyprowadzanie eleganckich lub „ładnych” wartości osi na wykresach za pomocą Excela - funkcja wyszukiwania lub formuła

Próbuję automatycznie obliczyć najbardziej elegancką skalę osi y (wartość maksymalna i wartość podziału) dla niektórych wykresów słupkowych. Korzystam z programu Excel zintegrowanego z narzędziem deski rozdzielczej (SAP Dashboards), aby wyświetlać dwa wykresy obok siebie w celu łatwego porównania. Potrzebuję tych wykresów, aby miały identyczne skale osi Y, aby można je było łatwo porównywać.

Oto zdjęcie wyjaśniające, co mam na myśli: dwie wykresy przedstawiające różne dane, ale skalowane identycznie, aby ułatwić porównanie:

Oczywiście oś Y musi być skalowana, aby uwzględnić największą wartość, niezależnie od tego, czy znajduje się po lewej, czy po prawej stronie. W tym przypadku największa wartość znajduje się na lewym wykresie. Użyłem funkcji MAX Excela, aby znaleźć największą wartość (4 668), a następnie zastosowałem trochę logiki do a) wyprowadzenia eleganckiej górnej granicy (4700) i wartości podziału (1200). Użyłem funkcji LEN, aby ocenić, ile cyfr ma liczba, oraz funkcji CEILING, aby zaokrąglić w górę do najbliższej „ładnej” liczby całkowitej.

Na szczęście mój pakiet deski rozdzielczej pozwala mi dostosowywać wartości minimalne i maksymalne osi y, a także podział. Mogę je ustawić, wskazując na komórkę.

Moje pytanie brzmi zatem, jak skalować tę „logikę”, aby uzyskać eleganckie wartości maksymalne i wartości podziału dla dowolnego zestawu danych, niezależnie od tego, czy liczby są niskie, czy wysokie. Excel robi to samo, gdy automatycznie skaluje osie. Muszę to powtórzyć, aby móc to zrobić programowo w programie Excel.

Dla wartości od 0 do 10 maksymalna oś Y może być samą wartością.

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

Jak widać, gdy wartości w moich danych (które mogą się znacznie różnić) rosną, muszę elegancko wybrać górną wartość y, która będzie dobrze wyglądać na moim wykresie. Jak najlepiej to zrobić?

Nie znalazłem też najlepszego sposobu wyboru 4 lub 5 wartości podziału, które muszą być „ładnymi” liczbami (np. Wielokrotności 2, 5, 10, 20 lub 20, a nawet 2000).

Moja formuła musi się ładnie skalować, podobnie jak auto skalowanie Excela daje świetne wyniki bez względu na rząd wielkości danych wejściowych. Czy ktoś może zasugerować, jak najlepiej to osiągnąć?

Po wielu przemyśleniach, wydaje mi się, że opracowałem rozwiązanie problemu skalowania osi. Istnieje wiele podobnych postów na innych stronach, ale często ich skalowanie liczb było dość „nerwowe”. Moje rozwiązanie jest w pełni konfigurowalne i zachęcam wszystkich do korzystania z niego i dostosowywania go do własnych potrzeb.

Rozwiązanie można zbadać w skoroszycie programu Excel dostępnym tutaj

Proces ten oparty jest na prostej tabeli odnośników, która dzieli wszystkie wartości od 1 do 10 na przyjemne „fragmenty”. Użyłem wielokrotności 4, 5, 6 i 7, chociaż zauważam, że Excel używa także wielokrotności 9. Oto tabela odnośników:

  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  

Dla dowolnej wartości znajdź najbliższą „wartość maksymalną”, a to powie ci maksymalną wartość do wykreślenia na twojej osi, wraz z liczbą tyknięć i ich wielkości. Na przykład, jeśli największa wartość w twoich danych wynosi 7,5, mój system zwróciłby 8 jako maksymalną wartość dla wykresu, podzieloną na cztery tiki po 2.

Może oczywiście obsługiwać wartości mniejsze niż 1 i większe niż 10, używając niektórych funkcji log i pow . Sprawdź to sam: otwórz arkusz kalkulacyjny , wpisz nową wartość w żółtym polu wprowadzania i zobacz, jak wartości się aktualizują.

Możesz dostosować listę punktów przerwania do własnych potrzeb. Uwzględniłem wszystkie formuły w arkuszu kalkulacyjnym wraz z kilkoma opisami, które mogą pomóc.