Refactorizar fórmulas de Excel: técnicas para hojas limpias

Refactorizar fórmulas de Excel significa limpiar lo que ya funciona sin romperlo. En una hoja de cálculo, eso quiere decir coger una fórmula que hace lo correcto pero que parece escrita a las once de la noche antes de una entrega, y convertirla en algo que de verdad quieras volver a abrir el trimestre que viene. Biscuit ya te la trajo: esto es lo que realmente cambia las cosas.

Tu fórmula funciona, entonces ¿por qué no te convence?

La señal clásica de que necesitas refactorizar: abres un archivo que escribiste hace seis meses y tardas tres minutos en entender qué hace una sola celda. O copias una fórmula a una columna nueva y se rompe en silencio porque había una referencia de fila fija escondida dentro.

Refactorizar no cambia el resultado. Cambia la estructura para que el resultado siga siendo correcto cuando algo alrededor cambia: filas nuevas, columnas nuevas, una hoja que se renombra.

Sáltate esto si: tu archivo es un cálculo puntual que nadie más va a tocar. Refactorizar tiene un coste (tiempo, interrupciones) y si el beneficio es cero, el retorno también lo es.

Merece la pena cuando: varias personas usan el archivo, lo actualizas con regularidad, o las fórmulas se encadenan entre varias hojas.

Algunas señales de alarma que vale la pena atender:

• Una fórmula más ancha que la columna al 100% de zoom

• Más de dos niveles de IF anidados

• Números fijos repetidos en más de tres sitios

• Compañeros que te piden que les expliques qué hace una celda en vez de leerla ellos mismos

Cualquiera de estas ya justifica dedicar una sesión a limpiar la hoja.

Extraer método: divide una fórmula en piezas con nombre

Esta es la técnica de refactorización más útil que existe, tanto en programación como en hojas de cálculo. La idea: si una fórmula hace tres cosas a la vez, la divides en tres celdas, una por paso, y luego combinas los resultados.

Antes (una celda que lo hace todo):

=SI(Y(A2<>"",SIFECHA(A2,HOY(),"Y")>30),"Senior","Junior")

Después:

• Celda C2: =SIFECHA(A2,HOY(),"Y"), con el nombre "Edad en años"

• Celda D2: =Y(A2<>"",C2>30), con el nombre "¿Es senior?"

• Celda E2: =SI(D2,"Senior","Junior"), el resultado final

Así se ve en una celda real. Cada paso se entiende solo, sin ayuda. Si el umbral pasa de 30 a 25, cambias un número en una celda. Si SIFECHA falla porque falta una fecha, ves exactamente dónde.

La contrapartida: usas más columnas. En una hoja ya apretada, puede parecer un derroche de espacio. Si el ancho es un problema, crea una zona de celdas auxiliares en una hoja aparte y llámala algo como "Auxiliares" o "Cálculos", para que cualquiera que abra el archivo entienda de inmediato qué es.

Funciona igual en Excel y en Sheets: no hay ninguna restricción de versión para usar celdas auxiliares.

Sustituye variables temporales por el cálculo directo: deja de guardar lo que puedes calcular

Una variable temporal, en el lenguaje de las hojas de cálculo, es una celda que rellenas con un valor intermedio que nadie más consulta directamente: solo la necesitabas para llegar al siguiente paso.

A veces son útiles (ver arriba). Pero cuando tienes una cadena de cinco celdas auxiliares donde solo la última importa, y las intermedias nunca cambian, puedes colapsarlas en una sola fórmula.

Tres celdas auxiliares que construyen un solo resultado:

• =B2*C2, subtotal

• =D2*0,2, importe de impuestos

• =D2+E2, total

Colapsado:

=(B2*C2)+(B2*C2*0,2)

O con un rango con nombre para el tipo impositivo:

=B2*C2*(1+TipoImpuesto)

La regla: si una celda auxiliar solo la consume otra celda, pregúntate si esa fórmula es lo bastante corta para meterla directamente. Si sí, hazlo. Si no, conserva la celda auxiliar y ponle un nombre decente.

Dónde poner el límite: una fórmula que pasa de 80 caracteres suele ser demasiado larga para meterla directa. Ahí, el método "Extraer método" de antes te sirve mejor. Conserva las celdas auxiliares cuando de verdad ayudan a leer la hoja; quítalas cuando solo son ruido.

Encapsula los valores fijos: protege lo que no debería cambiar

En una hoja de cálculo, encapsular significa mantener tus valores fijos (tipos, umbrales, referencias) en un solo sitio, en vez de repartidos como números sueltos dentro de las fórmulas.

Es la fórmula que todo el mundo olvida y que todo el mundo busca.

Un tipo de IVA fijado como 0,21 en 47 fórmulas repartidas por un libro es una trampa de mantenimiento. Cambia el tipo y tienes que encontrar las 47. Se te escapan tres y tienes un error de cálculo.

Arreglo:

1. Crea una hoja Configuración con una celda llamada TipoIVA = 0,21

2. Referéncialo en cada fórmula: =B2*TipoIVA

3. Cuando cambie el tipo, actualizas una celda. El resto sigue solo.

Esto aplica igual a referencias de fecha (la fecha de hoy como ancla fija), multiplicadores de divisa y umbrales de categoría. Si un valor aparece más de dos veces y puede llegar a cambiar, merece un rango con nombre.

Cómo crear rangos con nombre:

• Excel: pestaña Fórmulas > Administrador de nombres > Nuevo

• Google Sheets: Datos > Rangos con nombre

El nombre importa. TipoImpuesto es claro. TI no lo es. tmp_3 tampoco. Usa nombres que alguien que no escribió la fórmula entienda en tres segundos.

Elimina la duplicación: el principio DRY aplicado a tus fórmulas

El principio DRY, no te repitas es uno de los más citados en ingeniería de software, y se aplica directamente a las hojas de cálculo. Si has copiado el mismo BUSCARV en 15 columnas con pequeñas variaciones, tienes un problema de mantenimiento esperando a explotar.

Dos caminos según tu situación:

Excel 365 / Google Sheets: usa BUSCARX o INDICE-COINCIDIR con expansión de matriz para traer varias columnas en una sola fórmula.

=BUSCARX(A2,TablaConsulta[ID],TablaConsulta[[Nombre]:[Estado]])

Esto devuelve Nombre y Estado a la vez, sin copiar y pegar columna por columna.

Excel más antiguo (2016 o anterior): crea una única llamada a COINCIDIR en una celda auxiliar y luego usa INDICE apuntando a esa posición:

• G2: =COINCIDIR(A2,TablaConsulta[ID],0), una sola coincidencia para todas las columnas

• H2: =INDICE(TablaConsulta[Nombre],G2)

• I2: =INDICE(TablaConsulta[Estado],G2)

Sigues referenciando el mismo resultado de COINCIDIR en todas partes. Si cambia la columna de búsqueda, actualizas una sola fórmula.

Una nota de versión que merece la pena: BUSCARX llegó a Excel en 2019 para suscriptores empresariales y a Excel 365. Si tu equipo usa Excel 2016, el enfoque INDICE-COINCIDIR es el que toca. Sheets soporta BUSCARX desde 2022 en todos los planes.

Sustituye condicionales complejos por algo más claro

Los IF anidados son donde las hojas de cálculo van a volverse ilegibles. A partir del tercer nivel pierdes a la mayoría de los lectores, incluido tú mismo dentro de seis meses.

Antes (cadena de IF anidados):

=SI(A2>10000,"A",SI(A2>5000,"B",SI(A2>1000,"C","D")))

Opción 1: SI.CONJUNTO (Excel 2016+ / Sheets)

=SI.CONJUNTO(A2>10000,"A",A2>5000,"B",A2>1000,"C",VERDADERO,"D")

Más plano, pero sigue siendo cuatro condiciones en una sola fórmula. Aceptable.

Opción 2: BUSCARV con una tabla de referencia (el enfoque infravalorado)

Crea una pequeña tabla de referencia:

• 10001 en adelante: Categoría A

• 5001 a 10000: Categoría B

• 1001 a 5000: Categoría C

• 0 a 1000: Categoría D

Y luego: =BUSCARV(A2,TablaUmbrales,2,VERDADERO)

El VERDADERO (coincidencia aproximada) recorre la tabla ordenada y devuelve la última categoría que encaja. La lógica ahora vive en la tabla, no escondida dentro de la fórmula. ¿Cambia un umbral? Editas una fila de la tabla.

Merece la pena invertir tiempo en: el enfoque de tabla de referencia, porque hace la lógica auditable y visible para cualquiera, incluso para quien no lee fórmulas. Sáltatelo si vas con prisa: SI.CONJUNTO va bien para tres condiciones o menos.

La función SI.MULTIPLE es otra opción disponible en Excel 2019+ y Sheets, útil cuando comparas valores exactos en vez de rangos. Se lee más limpio que SI.CONJUNTO para casos de coincidencia exacta.

El algoritmo de sustitución: cuando conviene reescribir en vez de parchear

A veces la fórmula no está mal, es que el enfoque entero está equivocado. Heredaste una fórmula de 200 caracteres que concatena texto con CONCATENAR y &, y necesitas añadir un campo más. Lo correcto no es alargarla. Es reescribirla con UNIRCADENAS.

Antes:

=A2&" | "&B2&" | "&C2&" | "&D2

Después:

=UNIRCADENAS(" | ",VERDADERO,A2:D2)

Si tu tabla cambia de cara mañana, esta fórmula aguanta. UNIRCADENAS gestiona celdas vacías, gestiona matrices, y no se rompe cuando añades la columna E.

La misma lógica aplica a:

• Cambiar BUSCARV por BUSCARX cuando necesitas coincidencia exacta y gestión de valores ausentes

• Cambiar el envoltorio SI.ERROR por la gestión de errores nativa de las funciones más nuevas

• Cambiar fórmulas matriciales con Ctrl+Mayús+Intro por matrices dinámicas nativas en Excel 365

El mismo principio se aplica a mayor escala: si mantienes un flujo de datos grande dentro de una hoja que se ha convertido en una maraña de referencias INDIRECTO, conexiones externas y rangos con nombre que apuntan a otros rangos con nombre, a veces lo correcto es sacar esa lógica a una herramienta apropiada (SQL, Python, Power Query) y dejar que la hoja solo muestre el resultado. Es una decisión más grande, pero pertenece a la misma familia de problemas.

Antes de empezar: las tres cosas que necesitas tener listas

Refactorizar fórmulas de Excel solo funciona si no rompes nada que no puedas recuperar. En una hoja de cálculo, eso significa:

1. Una copia de seguridad. Antes de cualquier sesión de refactorización, guarda una copia fechada del libro. No un "Guardar como" en la misma carpeta: un sitio aparte (copia en la nube, envío por correo a ti mismo, lo que sea). El día que algo salga mal, te lo agradecerás.

2. Saber cuáles deberían ser los resultados actuales. Antes de cambiar nada, anota los resultados clave: totales, cifras de resumen, valores de celdas importantes. Compáralos con las mismas celdas después de cada cambio. Una comprobación de diez segundos que detecta regresiones al instante.

3. Un cambio cada vez. No extraigas un método, encapsules tres rangos con nombre y aplanes un IF anidado en la misma sesión. Si algo se rompe, necesitas saber qué cambio lo causó. Haz una cosa, verifica, y pasa a la siguiente.

Estos tres pasos tardan unos cinco minutos en montarse. Han ahorrado bastante más de cinco minutos en cada sesión de refactorización donde algo salió mal de forma inesperada.

Cuando el refactor no termina nunca (y cómo pararlo)

El riesgo de refactorizar es el perfeccionismo. Puedes pasarte toda una tarde reorganizando un libro que ya funcionaba, y acabar con algo apenas más limpio a costa de media jornada.

Ponte un límite de tiempo antes de empezar: 45 minutos para esto. Prioriza las fórmulas que están causando problemas de verdad: las que la gente malinterpreta, las que fallan con el uso normal, las que bloquean una función nueva que necesitas añadir.

Todo lo demás: déjalo. Una fórmula que funciona, que nadie toca, que no ha dado un error en un año, está bien así. Suficientemente bueno es una parada legítima.

Un buen criterio: refactorizar no consiste en dejar el libro perfecto. Consiste en hacer que el siguiente cambio sea más fácil. Pregúntate antes de cada cambio: ¿esto hace que lo próximo que necesito hacer sea más rápido o más seguro? Si la respuesta es sí, hazlo. Si no, para. Cuando una fórmula queda bien refactorizada, nadie la nota: simplemente funciona. Buen chico, Biscuit.