Con el próximo año, es posible que desee realizar un seguimiento del progreso hacia algún tipo de objetivo. Este genial gráfico en Excel contiene elementos que indican si está en camino de alcanzar una meta. Di que quieres correr 250 millas este trimestre.
- La línea negra es la meta
- La línea azul es la línea "en camino": debes correr 2.7 millas por día para alcanzar el objetivo
- La línea verde son las millas reales recorridas
- La línea roja es la predicción de dónde terminarás el trimestre, según tu progreso hasta el momento. La línea roja es, con mucho, la parte más genial del gráfico.
A continuación se explica cómo crear el gráfico.
- Ingrese estos encabezados en la parte superior de una hoja de trabajo. En la celda A5, ingrese la primera fecha del gráfico.
- Ponga el puntero de celda en A5. Notará que hay un punto cuadrado en la esquina inferior derecha de A5. Este es el controlador de llenado. Con el mouse, agarre el controlador de relleno y arrastre hacia abajo. Observe la información sobre herramientas hasta que vea que ha arrastrado lo suficiente como para incluir la última fecha del trimestre.
- La fórmula para B2 es
=COUNT(A5:A200). Esto contará la cantidad de días en su rango.
- La fórmula para B3 es
=B1/B2. Esto calcula la distancia que debe correr cada día para alcanzar la meta.
- Use la columna B para ingresar qué tan lejos corrió cada día.
- Aunque la columna B contiene sus datos reales, no es adecuada para crear el gráfico. De hecho, creará un rango de gráfico completamente nuevo en las columnas C: F que contendrá los elementos necesarios para el gráfico.
- La columna C contendrá fechas. Si un eje del rango de su gráfico contiene números o fechas, debe dejar en blanco la celda superior izquierda del rango del gráfico.
Este es un error extraño en Excel: dejar esa celda en blanco permitirá que Intellisense de Excel reconozca el rango del gráfico más adelante. Por lo tanto, deje la celda C5 en blanco e ingrese los títulos de Distancia, Seguimiento y Objetivo en D5: F5.
- La fórmula en C5 es
=A5.
- Seleccione la celda C5. En el menú, seleccione Formato de celdas. En la pestaña Numérico, elija la categoría Fecha y luego el formato 14/3.
- Seleccione la celda C5. Arrastre el controlador de relleno hacia abajo para copiar esta fórmula. Arrastre hacia abajo hasta donde tenga datos en la columna A.
- Para las columnas D y E, las fórmulas de la fila 5 serán diferentes a las de las filas 6 y siguientes. Las fórmulas de la fila 5 proporcionarán un comienzo numérico. Las fórmulas en la fila 6 y siguientes incrementarán esos valores numéricos en la fila 5. La fórmula para D5 es
=B5- esto simplemente copiará la distancia del primer día al rango del gráfico.
- En un paso posterior, Excel agregará una línea de tendencia basada en los valores de la columna D. Si simplemente copiara D5 hacia abajo, terminaría con ceros para fechas futuras. Los ceros invalidarán la línea de tendencia. El truco es que debe completar las fechas futuras con valores de error # N / A.
En inglés, entonces, la fórmula básicamente dice: "Si ejecutamos hoy, agregue el valor de hoy al total de ayer. De lo contrario, coloque un N / A en esta celda.
Para lograr este resultado, ingrese
=IF(NOT(ISBLANK(B6)),D5+B6,NA())en la celda D6.
- Seleccione la celda D6. Haga doble clic en el controlador de relleno para copiar esta fórmula. El truco del doble clic funciona porque la columna a la izquierda de esta celda contiene valores que guiarán a Excel hasta dónde se debe copiar la fórmula.
- La fórmula en E5 necesita copiar el por día de B3. Ingrese
=B3en E5. - La fórmula para el resto de E necesita agregar B3 al día anterior. La fórmula para E6 podría ser,
=E5+B3pero esta fórmula no funcionaría cuando copia la celda a las otras filas. Siempre desea que la fórmula agregue B3 a la celda de arriba.Por lo tanto, use
=E5+$B$3en E6. Los signos de dólar aseguran que cuando copie la fórmula, siempre apunte a B3.
- Mueva el puntero de celda a E6 y haga doble clic en el controlador de relleno para copiar la fórmula hacia abajo.
- La fórmula para toda la columna F es
=$B$1. Este rango se utilizará para dibujar la línea de meta negra en el gráfico. Aquí hay un consejo para ingresar esta fórmula. Comience en F5. Escriba el signo igual. Con el mouse, toque B1. Luego, escriba la tecla F4 para colocar los signos de dólar.Presione Ctrl + Enter para aceptar la fórmula y permanecer en la celda actual. Ahora puede hacer doble clic en el controlador de relleno para copiar la fórmula.
- Finalmente, está listo para crear el gráfico. Seleccione el rango de C4: F94. Utilice Insertar - Gráfico en el menú o haga clic en el icono Asistente para gráficos en la barra de herramientas estándar.
- En el paso 1, elija un gráfico de líneas.
- El gráfico requerirá mucha personalización, pero nada de eso ocurre en el asistente, así que simplemente haga clic en Finalizar para crear este gráfico de aspecto horrible.
- Haga clic con el botón derecho en el fondo gris y elija Dar formato al área de trazado.
- Elija Efectos de relleno.
- Configure un degradado de 2 colores como se muestra aquí.
- Haga clic con el botón derecho en la línea amarilla de la serie Goal. Elija Formato de serie de datos.
- Elija una línea azul gruesa para la serie de goles.
- Repita los pasos 23-24 para cambiar la línea de seguimiento a una delgada línea negra.
- Es difícil formatear la serie Distancia, ya que solo hay 2 puntos y es casi imposible de ver. Si le resulta difícil hacer clic con el botón derecho en un elemento del gráfico, puede utilizar el menú desplegable de la barra de herramientas de gráficos.
- Una vez seleccionada la serie de distancias, use el icono Propiedades para abrir el cuadro de diálogo Formato para la serie seleccionada.
- Agregue un marcador a este diálogo. Cambie el color a azul oscuro y el grosor de la línea a medio.
- Para agregar la línea de tendencia, deberá poder hacer clic con el botón derecho en la serie de distancias. Esto es casi imposible de hacer en este momento. Entonces, sugiero que "hagas trampa". Ingrese un punto de datos falso de 20 en la celda B7. Esto le permitirá hacer clic derecho en la serie de distancias. Elija Agregar línea de tendencia.
- En el cuadro de diálogo Trendline, elija Linear Trendline.
- Seleccione la celda B7 y escriba la tecla Eliminar para borrar el valor falso allí. Ahora tendrá la línea de tendencia dibujada como una línea negra gruesa.
- Haga clic con el botón derecho en la línea de tendencia y elija Formato de línea de tendencia.
- Cambie la línea de tendencia a una línea roja discontinua con un grosor delgado.
- En la pestaña Opciones de la Línea de tendencia de formato, cambie el nombre de la serie a Predicción
Ahora tiene una tabla completa. Si se pierde un día de carrera, ingrese un cero en la columna B. Si se pierde un par de días, la línea de predicción roja mostrará que no está encaminado para alcanzar la meta.
Puede descargar el cuadro completo desde este enlace.
