Cómo configurar el eje del gráfico en función de un valor de celda

"Otra vez no...", pienso cada vez que cambio los valores mínimo y máximo de un eje del gráfico. Es una tarea muy tediosa y sé que la volveré a hacer en el futuro. Solo toma unos segundos. , pero todo ese tiempo empieza a acumularse. Hay varios objetos del gráfico que podemos vincular a las celdas de la hoja de cálculo; Los datos de origen, los títulos de los gráficos y las etiquetas de datos se pueden vincular a las celdas, pero el eje del gráfico se establece codificando un número en la ventana de opciones de Formato de eje. Lo que realmente queremos es establecer el eje del gráfico en función del valor de una celda.

Bueno… no soy tan fácil de derrotar. Decidí crear una solución más dinámica. Recurrí a mi viejo amigo VBA y comencé a experimentar. A continuación encontrará el resultado de esos retoques; una fórmula que existe en la hoja de trabajo para controlar los valores mínimos y máximos de un eje del gráfico. Vincule esa fórmula a una celda y, de repente, es posible configurar el eje del gráfico en función de un valor de celda.

Con suerte, al final de esta publicación, tú también podrás compartir la felicidad automática de vinculación celular que ahora experimento.

Si no estás familiarizado con VBA, no te preocupes, te explicaré paso a paso.

Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.

Nombre de archivo: 0170 Gráfico Min Max basado en valor de celda.xlsm

Tener acceso

La solución

Quería una solución que:

• Se actualiza automáticamente cada vez que cambian los datos.
• No requiere interacción del usuario, es decir, no hace clic en ningún botón, pero se actualiza automáticamente cuando se vuelve a calcular la hoja de cálculo.
• Fácilmente portátil entre diferentes hojas de trabajo

Creo que logré lograr esto.

Las funciones definidas por el usuario (UDF para abreviar) son como funciones normales de Excel, como BUSCARV o SUMA, pero se han creado usando VBA. Las UDF están diseñadas para ser funciones de hoja de trabajo personalizadas para calcular el valor de una celda. Ya sea a propósito o por accidente, Microsoft ha hecho posible controlar varios objetos con UDF. Por ejemplo, es posible cambiar el color de la pestaña de una hoja de trabajo o cambiar el título de un gráfico (publicación de la próxima semana). Afortunadamente, el mínimo y el máximo Los valores del eje del gráfico se pueden controlar mediante una UDF. ¡Hurra!

La solución en acción

El siguiente.webp animado muestra la solución en acción. Los valores de la celda se aplican automáticamente al gráfico. Ya sea que estos valores se escriban en la celda o se creen mediante fórmulas, actualizarán el gráfico.

Función definida por el usuario para establecer el eje en función de un valor de celda

Para crear la UDF, haga clic en Desarrollador – Visual Basic (o el acceso directo ALT + F11).

Se abrirá la ventana del Editor de Visual Basic, haga clic en Insertar – Módulo

Agregue el siguiente código al módulo como se muestra. El código de las UDF debe estar dentro de un módulo estándar para funcionar.

El código VBA

Aquí está el código VBA para copiar en el módulo.

Función setChartAxis(sheetName As String, chartName As String, MinOrMax As String, _ ValueOrCategory As String, PrimaryOrSecondary As String, Value As Variant)'Crear variablesDim cht As ChartDim valueAsText As String'Establecer el gráfico que será controlado por la funciónSet cht = Aplicación .Caller.Parent.Parent.Sheets(sheetName) _ .ChartObjects(chartName).Chart'Set Valor del eje primarioSi (ValueOrCategory = "Value" Or ValueOrCategory = "Y") _ Y PrimaryOrSecondary = "Primario" Luego con cht.Axes (xlValue, xlPrimary) Si IsNumeric(Value) = True Entonces Si MinOrMax = "Max" Entonces .MaximumScale = Valor Si MinOrMax = "Min" Entonces .MinimumScale = Valor De lo contrario Si MinOrMax = "Max" Entonces .MaximumScaleIsAuto = True Si MinOrMax = "Min" Then .MinimumScaleIsAuto = True End If End WithEnd If'Establecer categoría de eje primarioIf (ValueOrCategory = "Category" Or ValueOrCategory = "X") _ And PrimaryOrSecondary = "Primary" Luego con cht.Axes(xlCategory, xlPrimary) If IsNumeric(Value) = True Entonces si MinOrMax = "Max" Entonces .MaximumScale = Valor Si MinOrMax = "Min" Entonces .MinimumScale = Valor De lo contrario Si MinOrMax = "Max" Entonces .MaximumScaleIsAuto = True Si MinOrMax = "Min" Entonces .MinimumScaleIsAuto = True End If End WithEnd If'Establecer valor del eje secundarioIf (ValueOrCategory = "Value" Or ValueOrCategory = "Y") _ Y PrimaryOrSecondary = "Secundario" Luego con cht.Axes(xlValue, xlSecondary) If IsNumeric(Value) = True Entonces Si MinOrMax = "Max" Entonces .MaximumScale = Valor Si MinOrMax = "Min" Entonces .MinimumScale = Valor De lo contrario Si MinOrMax = "Max" Entonces .MaximumScaleIsAuto = Verdadero Si MinOrMax = "Min" Entonces .MinimumScaleIsAuto = Verdadero Fin Si Termina ConEnd If'Establecer categoría de eje secundarioIf (ValueOrCategory = "Category" Or ValueOrCategory = "X") _ Y PrimaryOrSecondary = "Secundario" Entonces con cht.Axes(xlCategory, xlSecondary) Si IsNumeric(Value) = True Entonces si MinOrMax = "Max " Entonces .MaximumScale = Valor Si MinOrMax = "Min" Entonces .MinimumScale = Valor De lo contrario Si MinOrMax = "Max" Entonces .MaximumScaleIsAuto = True Si MinOrMax = "Min" Entonces .MinimumScaleIsAuto = True End If End WithEnd If'If es texto siempre mostrar "Auto"Si esNumeric(Valor) Entonces valorAsText = Valor De lo contrario valorAsText = "Auto"'Emite una cadena de texto para indicar los valoressetChartAxis = ValueOrCategory " " PrimaryOrSecondary " " _ MinOrMax ": " valueAsTextEnd Función

Eso es todo lo que necesitamos hacer para crear la UDF, ahora está lista para usar.

Usando la función

La función que hemos creado utiliza la siguiente sintaxis:

=setChartAxis(nombre de la hoja, nombre del gráfico, mínimo o máximo, valor o categoría, primario o secundario, valor)

• SheetName = el nombre de la hoja de trabajo que contiene el gráfico.
• chartName = el nombre del gráfico como una cadena de texto, o una referencia de celda a una cadena de texto. Si no está seguro del nombre de un gráfico, haga clic en el gráfico y observe el NameBox (el cuadro a la izquierda de la fórmula bar).
• MinOrMax = una cadena de texto de “Min” o “Max” para seleccionar si la función controlará el valor mínimo o máximo del eje.
• ValueOrCategory = una cadena de texto de “Valor” o “Categoría” para indicar qué eje ajustar (también aceptará “X” o “Y”)
• PrimaryOrSecondary = una cadena de texto de "Primario" o "Secundario" para indicar qué eje ajustar.
• Valor = un número o una cadena de texto. Un número ajustará el eje a ese valor, "Auto" restablecerá el eje a Automático.

Para la función el trabajo, el gráfico debe estar en el mismo libro que la función.

A diferencia de las funciones normales, las UDF no muestran el orden de los argumentos cuando se escriben en la barra de fórmulas. Por lo tanto, las mejores opciones son:

• Comience a escribir la UDF y luego presione Ctrl + Shift + A después de escribir el primer corchete, aparecerán los argumentos.
  
• Haga clic en el botón fx en la barra de fórmulas. En la ventana Insertar función, seleccione la función en la lista Definida por el usuario y luego haga clic en Aceptar.
  
  La ventana Argumentos de función mostrará el orden y los nombres de los argumentos.
  

La siguiente captura de pantalla muestra cómo se utiliza la UDF:

La fórmula en la celda G12 es:

=setChartAxis("Hoja1","Gráfico 2","Min","Valor","Primario",E12)

Restablecer el eje predeterminado

Si decide volver al cálculo automático del eje de Excel, no hay problema. Cualquier valor no numérico ingresado como Valor (o en la referencia de celda vinculada al valor) hará que el gráfico se restablezca a automático.

=setChartAxis("Hoja1","Gráfico 2","Min","Valor","Primario","Auto")

Hacer que la función esté disponible en todos los libros de trabajo.

Si desea que la UDF esté disponible en todos sus libros de trabajo:

• Incluya el código anterior dentro de un nuevo libro de trabajo
• Guarde el libro como un complemento de Excel (tipo de archivo .xlam).
• Instale el complemento que ha creado siguiendo estas instrucciones .

La función ahora está disponible en todos sus libros de trabajo. Si otras personas abren el libro de trabajo, podrán ver la función, pero no funcionará. Si desea que otros usuarios utilicen la función, guárdela en cada archivo individual.

Artículos Relacionados:

• Cómo cambiar imágenes según los valores de las celdas
• Cree títulos de gráficos dinámicos con formato personalizado
• Matrices dinámicas y funciones definidas por el usuario (UDF) de VBA

---