Obtendo valores de eixo elegantes ou 'bons' em gráficos usando a função ou fórmula de pesquisa do Excel

Question

Estou tentando calcular automaticamente a escala mais elegante do eixo y (valor máximo e divisional) para alguns gráficos de barras. Estou usando o Excel integrado a uma ferramenta de painel (painéis SAP) para exibir dois gráficos lado a lado para facilitar a comparação. Eu preciso desses gráficos para ter escalas do eixo y idênticas para que possam ser comparadas facilmente.

Aqui está uma imagem para explicar o que quero dizer: dois gráficos que mostram dados diferentes, mas dimensionados de forma idêntica para ajudar na comparação:

É evidente que o eixo y deve ser dimensionado para acomodar o maior valor, seja à esquerda ou à direita. Nesse caso, o maior valor está no gráfico à esquerda. Eu usei uma função excel MAX para encontrar o maior valor (4.668), e então apliquei alguma lógica para a) obter um limite superior elegante (4.700) e um valor de divisão (1.200). Eu usei a função LEN para avaliar quantos dígitos o número tem, e a função CEILING para arredondá-la para o número inteiro 'legal' mais próximo.

Felizmente, meu pacote de painel permite personalizar os valores mínimo e máximo do eixo y, bem como a divisão. Eu posso definir isso apontando para uma célula.

A minha pergunta, então, é como escalar essa 'lógica' para produzir valores elegantes de max e de divisão para qualquer conjunto de dados, quer os números sejam baixos ou altos. O Excel faz a mesma coisa quando auto dimensiona seus eixos. Eu preciso replicar isso para que eu possa fazer isso programaticamente no Excel.

Para valores de 0 a 10, o eixo y máximo pode ser o próprio valor.

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

Como você pode ver, à medida que os valores em meus dados (que podem variar muito) aumentarem, preciso selecionar com elegância um valor y superior que ficará bem no meu gráfico. Qual é a melhor maneira de fazer isso?

Eu também não descobri a melhor maneira de escolher 4 ou 5 valores de divisão que devem ser números 'bons' (por exemplo, múltiplos de 2 ou 5, ou 10, ou 20, ou mesmo 2000).

Minha fórmula deve ser dimensionada muito bem, da mesma maneira que a escala automática do Excel produz ótimos resultados, independentemente da ordem de grandeza dos dados de entrada. Alguém pode sugerir a melhor forma de conseguir isso?

    

Answer 1

Depois de pensar muito, acredito ter criado uma solução para meu problema de escala de eixos. Existem muitos posts semelhantes em outros sites, mas muitas vezes o seu escalonamento de números foi bastante 'nervoso'. Minha solução é totalmente personalizável, e incentivo qualquer um a usá-la e adaptá-la para atender às suas necessidades.

A solução pode ser explorada em uma pasta de trabalho do Excel disponível aqui

O processo é conduzido por uma simples tabela de consulta que divide todos os valores entre 1 e 10 em agradáveis 'pedaços'. Eu usei múltiplos de 4, 5, 6 e 7, embora eu note que o Excel usa múltiplos de 9 também. Aqui está a tabela de pesquisa:

  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  

Para qualquer valor, encontre o mais próximo 'maxvalue', e isso lhe dirá o valor máximo para plotar em seu eixo, juntamente com o número de ticks e o tamanho do tick. Assim, por exemplo, se o maior valor em seus dados for 7.5, meu sistema retornará 8 como o valor máximo do gráfico, dividido em quatro marcas de 2.

É claro que ele pode manipular valores menores que 1 e maiores que 10, usando algumas funções log e pow . Teste você mesmo: abra a planilha , coloque um novo valor na caixa de entrada amarela e veja como os valores são atualizados.

Você pode personalizar a lista de pontos de interrupção para atender às suas necessidades. Eu incluí todas as fórmulas na planilha junto com algumas descrições para ajudar.