Control y mantenimiento de vehículos en Excel: tu hoja lista para empezar

Llevar un control de cuándo se revisó cada coche, qué se le hizo y cuánto costó no es solo cuestión de memoria: en una hoja de Excel bien armada puedes ver de un vistazo cuánto gastas al año, qué vehículo te sale más caro y qué revisión toca pronto. Este tutorial te enseña a montar esa hoja desde cero, con encabezados, una lista desplegable para no inventar categorías, validaciones para que las fechas y los importes no se descontrolen, totales por vehículo con SUMAR.SI y un formato condicional que se pone rojo solo cuando toca revisión. Todo funciona desde Excel 2007 en adelante; las listas desplegables y la validación de datos son herramientas de toda la vida, y el formato condicional con fórmula existe desde Excel 2007.

Qué registra una hoja de mantenimiento y por qué te conviene llevarla

Una hoja de mantenimiento es, en el fondo, un diario de a bordo ordenado en filas: cada fila es una intervención (un cambio de aceite, una rotación de neumáticos, una ITV) y cada columna guarda un dato de esa intervención: cuándo se hizo, en qué coche, qué tarea fue, a qué kilometraje, cuánto costó y cuándo toca la próxima. Con esas pocas columnas, que después puedes ampliar, se pueden contestar preguntas que de memoria son imposibles: cuánto llevo gastado en el coche este año, qué revisión me toca el mes que viene o qué vehículo de la familia me sale más rentable mantener. Llevar este registro a mano funciona al principio, pero se pierde: por eso una hoja de Excel con cálculos automáticos y avisos de revisión es la forma más práctica de controlarlo, sobre todo si tienes más de un vehículo o si anotas revisiones de varios años.

Las columnas que necesita tu hoja y los datos que vas a anotar

La hoja de ejemplo de este tutorial tiene ocho columnas pensadas para cubrir lo esencial sin complicar la entrada de datos. Estas son las columnas, de izquierda a derecha: Fecha, Vehículo, Tipo de tarea, Kilometraje, Coste, Próximo recordatorio (km), Próximo recordatorio (fecha) y Notas. En la columna de tipo de tarea se usan categorías fijas (Aceite y filtros, Frenos, Neumáticos, Correa de distribución, Batería, ITV, Revisión general y Otros) para que las estadísticas tengan sentido, y la lista desplegable que montaremos después se encargará de que siempre elijas una de esas categorías. La columna de kilómetros se guarda como número entero, sin decimales, porque un odómetro no tiene décimas; la de coste, como moneda con dos decimales, y la de fecha, con el formato de fecha corto del sistema.

Los tipos de tarea: una lista para no inventar cada vez

Elegir categorías fijas antes de empezar es importante por dos motivos: hace comparables los datos cuando mires totales por tipo de tarea y permite crear una lista desplegable que evita errores de escritura ("Aceite" frente a "aceite" frente a "cambio de aceite"). Estas son las categorías que se usan en este tutorial y que después llevaremos a la lista desplegable:

• Aceite y filtros
• Frenos
• Neumáticos
• Correa de distribución
• Batería
• ITV
• Revisión general
• Otros

La columna de kilómetros y la de coste: qué formato les conviene

El kilometraje es siempre un número entero (el odómetro no marca 25.430,7 km), por lo que la columna se formatea con Ctrl+1 > Número > 0 decimales. El coste, en cambio, lleva siempre dos decimales porque en los talleres se trabaja con céntimos, y se aplica el formato Moneda con dos decimales, que en España se ve como 120,00 €. Cuando pegues datos de otra hoja o de un PDF, revisa que no entren con formato de texto; si Excel alinea el número a la izquierda en vez de a la derecha, es texto y las sumas no funcionarán.

Crear la hoja de mantenimiento desde cero

Empezamos con un libro en blanco y montamos la hoja principal, que llamaremos "Mantenimiento", con sus encabezados y seis filas de ejemplo. Lo único que necesitas saber para esta parte es escribir en una celda, seleccionar un rango y poner la fila en negrita.

Escribir los encabezados y los datos de ejemplo

Selecciona A1 y escribe el primer encabezado; al pulsar Entrar el cursor baja a A2, pero con Tab avanzas a la derecha por la misma fila, que es lo que interesa para escribir encabezados. Rellena A1:H1 con los ocho títulos en este orden: Fecha, Vehículo, Tipo de tarea, Kilometraje, Coste, Próx. km, Próx. fecha, Notas. Cuando los tengas, selecciona A1:H1 y pulsa Ctrl+N para ponerlos en negrita. Después, en las filas 2 a 7, copia los datos de ejemplo de la hoja siguiente.

Calcular el próximo recordatorio a 10.000 km

El recordatorio por kilómetros es la suma del kilometraje actual más 10.000 km, una cifra razonable para una revisión general. En F2 escribe la fórmula =E2+10000 y púlsala; al situarte en F2 verás 30000 como resultado. Ahora arrastra el controlador de relleno (el cuadradito de la esquina inferior derecha de la celda seleccionada) hasta F7 para copiar la fórmula al resto de filas. Para la fecha del próximo recordatorio, en G2 escribe =B2+365 y arrastra también hasta G7: Excel entiende que sumar 365 días a una fecha es un año después.

Una lista desplegable para que el tipo de tarea sea siempre el mismo

Escribir a mano el tipo de tarea cada vez lleva a errores: "Aceite" un día y "Aceite y filtros" otro, y las estadísticas se rompen. La solución es una lista desplegable en la columna C con las ocho categorías fijas. Validación de datos es una herramienta de toda la vida en Excel y funciona igual desde la versión 2007.

Crear la lista en una hoja auxiliar

Crea una hoja nueva llamada "Categorías" (clic derecho en la pestaña > Insertar > Hoja). En A1:A8 escribe las categorías en este orden, una por fila: Aceite y filtros, Frenos, Neumáticos, Correa de distribución, Batería, ITV, Revisión general, Otros. Para que la lista sea fácil de localizar cuando la necesites, selecciona A1:A8, ve al Cuadro de nombres (la caja a la izquierda de la barra de fórmulas) y escribe ListaTareas; al pulsar Entrar, el rango queda guardado con ese nombre.

Aplicar la lista desplegable en la hoja principal

Vuelve a la hoja "Mantenimiento", selecciona C2:C7 y abre la herramienta de validación.

En el cuadro que se abre, configura la pestaña "Configuración" como se muestra a continuación.

Que las fechas y los importes no se descontrolen

De nada sirve tener las categorías bien si alguien teclea "ayer" en la fecha o un coste con la coma corrida. Con la misma herramienta de Validación de datos se ponen límites a las fechas y a los importes para evitar errores a la hora de introducir los datos.

Validar las fechas del año en curso

Selecciona B2:B7 (las fechas) y abre Datos > Herramientas de datos > Validación de datos. Configura el cuadro así.

Validar el coste entre 0 y 5.000

Ahora selecciona D2:D7 y vuelve a abrir la herramienta. En esta ocasión el criterio es numérico.

Ver de un vistazo cuánto lleva gastado cada vehículo

Con los datos ya introducidos, llega la parte interesante: los totales. La función SUMAR.SI (en inglés SUMIF) suma un rango si se cumple una condición; PROMEDIO.SI (AVERAGEIF) hace lo mismo con la media. Ambas existen desde Excel 2007.

Total gastado por cada vehículo

A la derecha de la tabla, en K1 y L1, escribe los encabezados "Vehículo" y "Total gastado". En K2 y K3 anota los dos vehículos del ejemplo: "Coche A" y "Coche B". Ahora, en L2, escribe la fórmula:

=SUMAR.SI($B$2:$B$7;K2;$D$2:$D$7)

La fórmula dice: en el rango B2:B7 (los vehículos), busca el valor de K2 ("Coche A") y, donde coincida, suma el valor correspondiente del rango D2:D7 (los costes). Las referencias a B2:B7 y D2:D7 se han fijado con F4 (pulsar F4 con el cursor sobre la referencia la convierte en absoluta, $B$2:$B$7) para que al arrastrar hacia abajo no se desplacen; K2, en cambio, se queda relativa para que en L3 apunte a K3. Arrastra desde L2 hasta L3.

Coste medio por revisión de cada vehículo

En M1 escribe "Coste medio". La función PROMEDIO.SI funciona igual que SUMAR.SI pero calcula la media de los importes que cumplen el criterio.

=PROMEDIO.SI($B$2:$B$7;K2;$D$2:$D$7)

Escribe la fórmula anterior en M2 y arrastra hasta M3. El resultado: 286,25 para el Coche A (1145 entre 4 revisiones) y 295 para el Coche B (590 entre 2 revisiones).

Que las revisiones próximas se pongan en rojo solas

El formato condicional es la forma que tiene Excel de aplicar un aspecto (color, negrita, icono) automáticamente a las celdas que cumplen una condición. En este caso queremos que la fila entera cambie de color cuando falten 1.000 km o menos para la próxima revisión, comparando la columna de kilómetros actuales (E) con la de próximo recordatorio (F).

Crear la regla con "Valor de la celda"

Selecciona E2:E7 y abre el menú de formato condicional.

En el cuadro, elige "Aplicar formato únicamente a las celdas que contengan", Valor de la celda "menor o igual a" 1000 y define el formato con relleno rojo claro y texto en negrita. Esta regla es la más sencilla y tiñe solo la celda de kilómetros.

Aplicar la regla a toda la fila con una fórmula

Para que se coloree la fila entera, hay que aplicar el formato a un rango mayor y usar una fórmula como regla. Selecciona A2:H7 y vuelve a crear una regla nueva, esta vez del tipo "Usar una fórmula que determine las celdas a las que se va a aplicar formato".

La fórmula combina dos condiciones con Y (AND en inglés): que la diferencia entre próximo recordatorio y kilometraje actual sea menor o igual a 1.000, y que la celda del recordatorio no esté vacía. Las referencias a la columna se han fijado con $ ( $F2 y $F2) para que la regla compare siempre las mismas columnas al moverse por la fila, pero la fila se queda relativa para que cada fila se evalúe con sus propios datos. Con los datos del ejemplo, la fila 4 (Neumáticos a 25.000 km, próximo a 35.000) no se colorea, pero la fila 5 (ITV a 27.000 km, próximo a 37.000) sí, porque la diferencia es 10.000, no menor o igual a 1.000; para verla en rojo con los datos de ejemplo, baja el umbral a 11.000 o añade más filas.

Cuando la hoja falla: los errores que de verdad pasan

Tres tropiezos típicos al montar esta hoja, con su causa y su arreglo.

La lista desplegable deja pasar valores pegados de otra hoja

Pegar con Ctrl+V escribe el texto directamente y salta la validación. El arreglo es pegar con pegado especial: Ctrl+Alt+V > Validación, que reutiliza la regla de la celda destino. Si ya se ha pegado el valor no válido, selecciónalo y usa el icono de advertencia amarillo que aparece al lado de la celda para revertir.

El formato condicional no se aplica a las filas nuevas

Las reglas con un rango fijo (por ejemplo A2:H7) no crecen solas: al añadir la fila 8 con datos nuevos, la regla se queda en las filas anteriores. El arreglo es convertir la tabla en Tabla: selecciona A1:H7, ve a Insertar > Tablas > Tabla, marca "La tabla tiene encabezados" y acepta. A partir de ahí, el rango de la regla pasa a ser la columna entera de la tabla y se amplía automáticamente con cada fila nueva.

#¡REF! al arrastrar la fórmula del total por vehículo

Si al arrastrar =SUMAR.SI(B2:B7;K2;D2:D7) desde L2 hasta L3 aparecen almohadillas #¡REF! o el cálculo falla, es que las referencias se han movido al copiar. El arreglo es pulsar F4 sobre cada rango justo después de escribirlo para que queden absolutas ($B$2:$B$7 y $D$2:$D$7), dejando solo la celda del criterio (K2) como relativa. En Mac, F4 se obtiene con Cmd+T.

Lo que ya sabes hacer y el siguiente paso

Seis ideas para llevar a tu hoja:

• Encabezados fijos en la fila 1, en negrita con Ctrl+N.
• Lista desplegable con las ocho categorías en la columna C, alimentada por un rango con nombre.
• Validación de fechas en la columna B limitada al año en curso y validación de importes en la D entre 0 y 5.000.
• SUMAR.SI y PROMEDIO.SI con referencias absolutas para los totales por vehículo.
• Formato condicional con Y para colorear la fila cuando faltan 1.000 km o menos.
• Convertir los datos en Tabla para que la hoja crezca sola con cada revisión nueva.

El siguiente paso natural es añadir una columna de alertas que avise con un texto si la fecha del próximo recordatorio ya ha pasado. Para eso se anida un SI (IF en inglés) que compare la fecha de hoy, HOY() (TODAY), con la columna G y muestre "VENCIDA" cuando sea menor. Queda así en I2: =SI(G2<HOY();"VENCIDA";"OK") y se arrastra hasta I7.

Dudas al montar tu hoja de mantenimiento de vehículos