jueves, 20 de junio de 2013

Las perpendiculares en un gráfico de Excel.

Recientemente un alumno de mis cursos online me preguntaba sobre la forma de construir unas perpendiculares en un gráfico de Excel, pero que además de serlo lo pareciera... como la mujer del Cesar, vamos ;-).
El objetivo obtener un gráfico como el de la imagen:

Las perpendiculares en un gráfico de Excel.


Lo primero que debemos tener claro, y recordar, es cómo se construyen dos rectas perpendiculares empleando dos ecuaciones del tipo:
y= m1x + b
y= m2x + c

sabiendo que si m1 la definimos como un cociente: n/d, y que corresponde a la pendiente de nuestra recta, su perpendicular m2 vendrá definida por el cociente inverso con signo contrario: -d/n. Por tanto, nuestras rectas perpendiculares tendrán esta forma:
y= n/d x + b
y= -d/n x + c

Además de matemáticamente, también es comprobable gráficamente que son perpendiculares, ya que si nos fijamos en los cuadros que recorren horizontal y verticalmente cada recta, verificamos que una de ellas por cada dos cuadros en horizontal sube tres en vertical, al contrario que su perpendicular, que recorre tres en horizontal por cada dos en vertical.
Conceptos geométricos a parte, nos centraremos ahora en la parte gráfica de Excel.

En primer lugar plasmaremos los pares de los puntos que representarán nuestras rectas, es suficiente dos puntos por recta (al fin y al cabo eso es lo que define una recta, no?):

Las perpendiculares en un gráfico de Excel.


En el ejemplo he decidido formularlo, aunque como se puede ver es fácil hacerlo de manera manual invirtiendo los pares de puntos.

El siguiente paso es generar un gráfico tipo dispersión con líneas rectas y marcadores (desde luego debe ser tipo dispersión!!!), seleccionando el rango A4:A5 y B4:B5. Conseguiremos el siguiente gráfico con una sóla serie (de momento):

Las perpendiculares en un gráfico de Excel.


Quizá tengas que presionar el botón de Cambiar entre filas y columnas

Ahora desde la Selección de datos accedemos a la ventana Seleccionar origen de datos y agregamos los valores de la otra recta:

Las perpendiculares en un gráfico de Excel.
haz clic en la imagen



El siguiente paso consiste en 'tramar' el área del trazado, lo que conseguimos fácilmente desde las Herramientas de gráficos > pestaña Presentación > grupo Ejes > botón Líneas de cuadrículas > opción Líneas verticales de la cuadrícula primarias > Líneas de división principales:

Las perpendiculares en un gráfico de Excel.



Un paso muy importante es asegurarse que el número de etiquetas en ambos ejes es el mismo!!! (en el ejemplo de -20 a 20 en intervalos de 5), si fuera diferente o con un intervalo distinto, los igualaríamos desde Dar formato a ejes > Opciones del eje.
En pares de datos como el del ejemplo, no es necesario, ya que el automático del asistente los asigna correctamente.


Otro paso más consiste en situar la Leyenda superpuesta en el ára del gráfico. Para ello seleccionamos la Leyenda y accedemos al Formato de Leyenda, y desde las Opciones de leyenda desmarcamos Mostrar la leyenda sin superponerla en el gráfico:

Las perpendiculares en un gráfico de Excel.
haz clic en la imagen


También podríamos optar por suprimirla. En todo caso este paso es importante, ya que para el siguiente paso es necesario que sólo aparezca en todo el área del gráfico el área del trazado. Es una manera de facilitar el trabajo siguiente.

Último paso. Seleccionamos el área del gráfico, y desde Herramientas de gráficos > pestaña Formato > grupo Tañamo asignamos las propiedades de Alto y Ancho con igual valor:

Las perpendiculares en un gráfico de Excel.


Si bien no es 100% exacto, el impacto visual lo parece.

Las perpendiculares en un gráfico de Excel.


He añadido un rectángulo (una forma rotada) para verificar la perpendicularidad, a falta de un medidor de ángulos, donde realmente se puede ver que la perfección es cuestión de micras...

En definitiva hemos logrado lo que esperábamos, no sólo tener representadas dos rectas perpendiculares en un mismográfico, si no que además de manera muy sencilla, lo parezcan.

2 comentarios:

  1. que hago para que esta formula funcione
    soy un principiante y me dejaron esta tarea:

    g) ANALISIS DE LAS VENTAS DEL VENDEDOR: Mediante el uso de la función Si anidada realizó el siguiente análisis en la celda J9, tomando en consideración los siguientes argumentos:
    Si la VENTA POR VENDEDOR (E9) es igual a la VENTA META POR VENDEDOR (B2) como valor verdadero dirá "VENDEDOR IDEAL", si la VENTA POR VENDEDOR (E9) es mayor a la VENTA META POR VENDEDOR (B2) como valor verdadero dirá "VENDEDOR SOBRESALIENTE", si la VENTA POR VENDEDOR (E9) es menor a la VENTA META POR VENDEDOR (B2) como valor verdadero dirá "VENDEDOR MALO". (7 Pto)
    Luego haciendo uso de la Opción de autorrelleno copie la fórmula para las celdas J10 hasta la celda J18.

    me la pasaron asi pero no me funciona gracias.


    =SI(E9=$B$2; “VENDEDOR IDEAL”;SI(E9>$B$2; “VENDEDOR SOBRESALIENTE”;SI(E9<$B$2; “VENDEDOR MALO”)))

    ResponderEliminar
    Respuestas
    1. Hola José, que tal , espero te encuentres bien.
      la función es correcta, responde a los requisitos que te piden.. ¿qué clase de error te devuelve para que no te funcione???
      Slds cordiales

      Eliminar