Algunos trucos para crear fórmulas eficientes

¿Te has parado a pensar si tus hojas de cálculo contienen fórmulas eficientes? Cuando digo “eficientes” no me refiero solamente a si devuelve el resultado que tiene que devolver, sino también a si te hacen trabajar lo mínimo posible en caso de que tengas que modificarlas.

Te muestro un ejemplo de lo que considero una fórmula no eficiente:

Como puedes ver, los resultados de las fórmulas son correctos, pero cada una hace referencia a un departamento que está escrito en formato de texto y además las referencias de los rangos son relativas.

¿Qué significa esto?

Pues que, si el nombre del departamento cambia, tienes que cambiar manualmente la fórmula para que coincida con éste. Puede parecer algo obvio, pero no te imaginas las consultas de este tipo que surgen en el foro (usuarios que cambian el departamento pero que no actualizan las fórmulas)…

¿Solución?

La forma más eficiente de escribir la fórmula anterior es introducir como argumento criterio, la referencia a la celda que contiene el nombre del departamento.

La fórmula quedaría así:

Esto es sólo un ejemplo muy sencillo de optimización de fórmulas. Te puedo asegurar que todavía hay muchos usuarios que preferirían cambiar a mano cada una de las fórmulas… No lo entiendo.

¿Cómo puedes distinguir las fórmulas eficientes de las que no lo son?

Básicamente yo utilizo tres criterios:

• Se pueden copiar rápidamente. Si copias una fórmula en otra celda sin tener que hacer ninguna modificación en ella, es una fórmula eficiente.
• Fáciles de comprender y actualizar. Si al modificar un valor de la hoja, se actualizan todas las fórmulas, son fórmulas eficientes.
• Intuitivas. Si de un simple vistazo a las fórmulas sabes de dónde se obtienen los criterios para el cálculo, se trata de fórmulas eficientes.

Observa la siguiente imagen. ¿Quieres optimizar al máximo el trabajo con estos datos para conseguir rellenar los datos que se piden?

Una fórmula ineficiente para calcular la celda H3 sería algo como esto:

El primero de los criterios anteriores dice que la fórmula debe copiarse sin tener que hacer modificaciones en los datos (referencias absolutas y relativas).

El segundo de los criterios indica que deben ser fáciles de actualizar, así que al cambiar un valor de la hoja se deben actualizar las fórmulas, es decir, que se acabó el utilizar criterios de texto (debes utilizar referencias de celdas).

Y el tercer criterio dice que debes saber de dónde se obtienen los datos, de una forma sencilla (utilizando tablas).

Pues manos a la obra:

Referencias absolutas y referencias relativas

Una de las características más interesantes de las fórmulas es la posibilidad de fijar las referencias o parte de ellas para que al copiarlas en otra celda mantengan el nombre. Es lo que se llama referencias absolutas. Por defecto Excel utiliza referencias relativas.

Para marcar una referencia como absoluta sólo tienes que colocar el signo $ delante de la fila o de la columna (o de las dos partes) según lo que quieras mantener fijo.

Échale un vistazo a este artículo si quieres saber más sobre la construcción de referencias absolutas, relativas y mixtas.

En el caso que te propongo, si en la celda H3 introduces la fórmula

=SUMAR.SI.CONJUNTO($D$2:$D$46;$B$2:$B$46;$G3;$C$2:$C$46;H$2)

ésta funcionaría correctamente, ya que está perfectamente optimizada. Observa las referencias que te pongo en otro color porque son las más importantes de la fórmula. Como ves, contienen un solo símbolo de $ (referencia mixta) lo que permite arrastrar hacia abajo y hacia la derecha sin que el resultado deje de ser correcto.

Si arrastro esta fórmula una celda hacia la derecha, se ve cómo la referencia H$2 es ahora I$2 pero $G3 se mantiene igual. Si la misma celda la arrastro hacia abajo, la referencia que cambia es ahora $G3, que se convierte en $G4. ¿Coges el concepto?

Nombres de rangos

La mejor forma de hacer que se entiendan las fórmulas es utilizar nombres de rangos. Se le llama nombre de rango al nombre que se le da a una o varias celdas y que sustituye a sus referencias… ¿y esto para qué sirve? Muy sencillo, en el ejemplo de hoy, en vez de escribir el rango $D$2:$D$46 para designar al conjunto de salarios, puedes sustituirlo por el nombre Suma_Salarios.

Para crear un nombre, simplemente selecciona las celdas que quieres sustituir e introduce el nombre que quieras darle en el cuadro de nombres.

Tienes una explicación más detallada aquí.

¿Qué fórmula crees describe mejor su resultado?

=SUMAR.SI.CONJUNTO($D$2:$D$46;$B$2:$B$46;$G3;$C$2:$C$46;H$2)

=SUMAR.SI.CONJUNTO(Salarios;Departamentos;$G3;Anos;H$2)

Tablas

Otra forma de utilizar las fórmulas de forma eficiente es mediante el uso de tablas. El uso de nombres funciona muy bien cuando los datos de referencia sabes que no van a aumentar, pero ¿qué ocurre si se contratan nuevos empleados y la lista con los salarios aumenta de filas?

En este caso tendrías dos opciones: utilizar rangos de celdas dinámicos o convertir el rango de celdas en una tabla.

Personalmente te recomiendo el uso de tablas porque, además de ser muy sencillas de utilizar, también contienen un conjunto de herramientas específicas para tablas y además te permite utilizar referencias de celdas estructuradas. Para más información haz clic aquí.

Observa la imagen. Así quedaría la tabla y sus correspondientes fórmulas:

Resumen

El objetivo que he perseguido con este artículo es que debes, siempre que te sea posible, construir fórmulas eficientes, de forma que puedas escribirlas, copiarlas, interpretarlas y actualizarlas lo más rápidamente posible. ¿Ya lo haces así? Dímelo en los comentarios.