Nora ha llevado un registro meteorológico durante años. Cada hoja del libro de trabajo es un año separado, con la columna A en cada hoja con las fechas del año y la columna B con la cantidad de precipitación en ese día, si corresponde. A Nora le gustaría crear un gráfico que muestre la precipitación de este año frente a la precipitación del año pasado, hasta la fecha. Se pregunta si hay alguna manera de que este gráfico haga referencia automáticamente a los valores correctos de precipitación para ambos años según la fecha de hoy.

Hay un par de formas en que se puede satisfacer esta necesidad, dependiendo exactamente de lo que desee lograr. Si solo desea comparar la lluvia de este año con la precipitación del año pasado, por fecha, puede hacerlo fácilmente configurando algunos rangos dinámicos con nombre que definan los datos que desea usar.

Para empezar, supongamos que sus datos para 2015 están en una hoja de trabajo llamada 2015 y sus datos para 2016 (hasta ahora) están en una hoja de trabajo llamada 2016.

En cada hoja de trabajo, la fila 1 contiene encabezados, lo que significa que sus fechas realmente comienzan en la celda A2 y sus lecturas de precipitación en la celda B2.

Siga estos pasos para configurar los rangos:

  1. Muestre la pestaña Fórmulas de la cinta.

  2. En el grupo Nombres definidos, haga clic en la herramienta Administrador de nombres. Excel muestra el cuadro de diálogo Administrador de nombres.

  3. Haga clic en el botón Nuevo. Excel muestra el cuadro de diálogo Nuevo nombre. (Ver figura 1)

  4. En el campo Nombre, ingrese el nombre Año actual (tenga en cuenta que se trata de una sola palabra, sin espacios).

  5. En el campo Se refiere a, ingrese la siguiente fórmula:

  6. Haga clic en el botón Aceptar para terminar de crear el rango con nombre. El nuevo rango debería aparecer en el cuadro de diálogo Administrador de nombres.

  7. Vuelva a hacer clic en el botón Nuevo. Excel (nuevamente) muestra el cuadro de diálogo Nuevo nombre.

  8. En el campo Nombre, ingrese el nombre Año anterior (tenga en cuenta que, nuevamente, se trata de una sola palabra, sin espacios).

  9. En el campo Se refiere a, ingrese la siguiente fórmula:

  10. Haga clic en el botón Aceptar. El nuevo rango debería aparecer en el cuadro de diálogo Administrador de nombres.

  11. Haga clic en el botón Nuevo por tercera vez. Excel muestra el cuadro de diálogo Nuevo nombre.

  12. En el campo Nombre, ingrese el nombre Fechas.

  13. En el campo Se refiere a, ingrese la siguiente fórmula:

  14. Haga clic en el botón Cerrar para cerrar el cuadro de diálogo Administrador de nombres.

Con los rangos definidos, ahora puede crear el gráfico usando esos rangos:

  1. Muestre la pestaña Insertar de la cinta.

  2. En el grupo Gráficos, haga clic en la opción Columna. Excel muestra una lista desplegable.

  3. Haga clic una vez en el formato de columna agrupada. (Esta es la primera opción bajo el encabezado Columna 2-D). Excel crea un objeto gráfico en blanco en su hoja de trabajo.

  4. Haga clic con el botón derecho en el objeto de gráfico. Excel muestra un menú contextual.

  5. Elija Seleccionar datos en el menú contextual. Excel muestra el cuadro de diálogo Seleccionar fuente de datos, que debería estar completamente vacío porque no ha agregado ningún rango de datos al gráfico.

  6. En el lado izquierdo del cuadro de diálogo, debajo de Entradas de leyenda (serie)

encabezado, haga clic en el botón Agregar. Excel muestra el cuadro de diálogo Editar serie. (Ver figura 2)

  1. En el campo Nombre de la serie, ingrese 2015.

  2. En el campo Valores de la serie, ingrese esto: ‘2015’! PreviousYear.

  3. Haga clic en el botón Aceptar. La serie de datos aparece ahora en el cuadro de diálogo Seleccionar origen de datos.

  4. Haga clic, nuevamente, en el mismo botón Agregar en el que hizo clic en el paso 6. Excel muestra nuevamente el cuadro de diálogo Editar serie de datos.

  5. En el campo Nombre de la serie, ingrese 2016.

  6. En el campo Series Values, ingrese esto: ‘2016’! CurrentYear.

  7. Haga clic en el botón Aceptar. Esta segunda serie de datos aparece ahora en el cuadro de diálogo Seleccionar origen de datos.

  8. En el lado derecho del cuadro de diálogo, debajo de Horizontal (Categoría)

Encabezado Etiquetas de eje, haga clic en el botón Editar. Excel muestra el cuadro de diálogo Etiquetas de eje. (Vea la figura 3.)

  1. En el rango de etiquetas del eje, ingrese esto: ‘2016’! Fechas.

  2. Haga clic en el botón Aceptar. El cuadro de diálogo Seleccionar fuente de datos debe estar completo con la información necesaria. (Consulte la figura 4.)

  3. Haga clic en Aceptar para cerrar el cuadro de diálogo Seleccionar origen de datos.

Su gráfico actualizado, que muestra solo las fechas hasta la fecha de hoy, ahora debería estar visible. Puede continuar formateando el gráfico, como desee.

(Por ejemplo, probablemente querrá formatear las fechas en el gráfico para que no incluyan un año). Además, el gráfico es dinámico, de modo que cuando abra el libro de trabajo mañana, reflejará un día más del que tenía hoy.

Otra forma de manejarlo es reconsiderar cómo está almacenando sus datos.

En lugar de almacenar todas sus lecturas de precipitación en hojas de trabajo separadas (por año), colóquelas todas en una sola hoja de trabajo. Dado que Excel puede manejar más de un millón de filas de datos en una hoja de trabajo, no se encontrará con ninguna limitación práctica. (Un millón de filas representa más de 2.700 años.)

Ahora, en una hoja de trabajo diferente, puede usar dos fórmulas de matriz para calcular la precipitación acumulada para ambos años hasta la fecha. La siguiente fórmula matricial proporcionará la precipitación del año anterior:

=SUM(Data!B2:B1000*IF(Data!A2:A1000>=DATE(YEAR(NOW())-1,1,1),IF(Data!A2:A1000<=EDATE(NOW(),-12),1,0)))

Esto supone que las lecturas de precipitación originales están en una hoja de trabajo llamada Datos y que no se extienden más allá de 1000 filas. (Puede modificar cualquiera de estos, según sea necesario.) Para obtener la precipitación actual de este año, puede usar esta fórmula de matriz:

=SUM(Data!B2:B1000*IF(Data!A2:A1000>=DATE(YEAR(NOW()),1,1),IF(Data!A2:A1000<=NOW(),1,0)))

Recuerde: estas son fórmulas de matriz, por lo que deben ingresarse usando Ctrl + Shift + Enter. El valor único devuelto por cada fórmula representa la precipitación acumulada de cada año hasta la fecha. Estos dos valores se pueden usar en cualquier gráfico que desee.

ExcelTips es su fuente de formación rentable en Microsoft Excel.

Este consejo (13427) se aplica a Microsoft Excel 2007, 2010, 2013 y 2016.