Cuando una hoja de cálculo de OpenOffice / LibreOffice Calc tiene pocas fórmulas a nadie le preocupa la optimización de las mismas. No es necesario.

Pero cuando el número y complejidad de las mismas va en aumento, todo parece que se vuelva más lento. Albert Einstein debió conocer las hojas de cálculo; le habrían ayudado a entender que el tiempo no es lineal, o al menos, la percepción que tenemos del mismo cuando nuestra hoja empieza a recalcular

Para evitar en lo posible estos problemas es necesario optimizar las fórmulas utilizadas, utilizando técnicas un poco más depuradas, y evaluando en cada caso qué estamos dispuestos a sacrificar en aras de una mayor velocidad y rendimiento.

Comenzamos con este una serie de artículos que esperamos nos ayuden a que nuestras hojas de cálculo sean más eficientes.

Siempre que se abre un libro de hojas de cálculo, se imprime, se exporta a PDF se recalculan muchas o todas las celdas de sus hojas. Si modificamos una celda que interviene en fórmulas contenidas en otras celdas, se recalculan las celdas que están alrededor o implicadas. Si la hoja tarda mucho en recalcularse, a veces desactivamos el recálculo automático. En algunas ocasiones podemos utilizar el ingenio para reducir el número de cálculos que tiene que realizar la hoja con sencillas técnicas, disminuyendo sensiblemente su carga de trabajo y mejorando su eficiencia.

Optimizar búsquedas

Comprobar si el dato a buscar se ha rellenado

Vamos a basarnos en un caso habitual: utilizar Calc para generar algún tipo de documento, estilo presupuesto, pedido, factura, etc. Y para ello vamos a utilizar el modelo de factura que presentamos en un artículo anterior.

La factura disponde de 30 líneas de detalle, en las cuales introducido un código de artículo, se realizan dos búsquedas con BUSCARV en una tabla situada en otra hoja llamada Datos del mismo libro para presentar la descripción del artículo y su precio.

Las celdas de la columna Descripción y Precio contendrán una fórmula del tipo:

=BUSCARV(B8;$Datos.$F$3:$H$50;2;FALSO())

Si la celda en la que insertamos el código está vacía o contiene un dato que no se encuentra, en las celdas de la descripción y del precio se mostrará el error #N/A que tanto nos desagrada.

La hoja de cálculo realizará en cada recálculo total dos búsquedas por cada línea, lo que supone un total 60 búsquedas, se encuentren las celdas de códigos llenas o vacías.

La solución que más se suele aplicar en estos casos para evitar que se muestre el mensaje de error es detectar si se produce el error con una fórmula similar a la siguiente:

=SI(ESERROR(BUSCARV(B8;$Datos.$F$3:$H$50;2;FALSO()));"";BUSCARV(B8;$Datos.$F$3:$H$50;2;FALSO()))

Esta fórmula mata dos pájaros de un tiro; tanto si la celda con el código está vacía como si contiene un dato no encontrado en la tabla, las celdas de la descripción y del precio se mostrarán como si estuvieran vacías, lo cual ofrece un aspecto muy limpio de nuestra hoja.

Con este sistema estamos forzando a que en cada recálculo total se realizen 60 operaciones de búsqueda, de las cuales si se obtiene en todos los casos un resultado positivo, se realizarán otras 60 para presentar el resultado. En total, un mínimo de 60 búsquedas y un máximo de 120.

Proponemos como alternativa la siguiente solución: en las celdas de descripción y precio insertamos fórmulas del tipo:

=SI(B8="";"";BUSCARV(B8;$Datos.$F$3:$H$50;2;FALSO()))

En este caso, si la celda del código a buscar está vacía no se produce ninguna búsqueda; si por el contrario contiene un valor no encontrado en la tabla, las celdas de la descripción y del precio lucirán el error #N/A, y si es un código existente, presentarán el resultado de la búsqueda.

Por lo tanto, si las celdas a buscar están vacías simplemente no se realiza ninguna búsqueda, lo que arroja un mínimo de 0 búsquedas; si contienen un dato, se realizarán como máximo 60 búsquedas. Las celdas con datos que no se encuentren en la tabla mostrarán un mensaje de error #N/A.

En resumen, tenemos las siguientes posibilidades:

Hemos mejorado el número de búsquedas frente a los dos casos anteriores, reduciendo la carga de trabajo de la hoja en cada proceso de recálculo total, a cambio de obtener un mensaje de error si el dato buscado no existe en la tabla en lugar de una celda vacía.

Utilizar una celda comodín

En el caso de que no sea aceptable que se presente el mensaje de error #N/A podemos recurrir a utilizar una celda (columna) comodín.

En esa celda, que más tarde ocultaremos o que puede estar situada en otra hoja del libro o en un lugar apartado de la hoja, insertamos la tercera fórmula de búsqueda que optimiza el número de búsqueda si el dato a buscar está vacío:

=SI(B8="";"";BUSCARV(B8;$Datos.$F$3:$H$50;2;FALSO()))

Esta será la celda comodín, en la que nos basamos para mostrar el resultado en las celdas Descripción y Precio, en las que insertaremos fórmulas similares a la siguiente (suponiendo que la celda comodín es la XB8):

=SI(ESERROR(XB8);"";XB8)

Con este sistema, obtenemos un resultado idéntico al segundo método en cuanto a limpieza de la hoja, pero aprovechando la optimización del número de búsquedas del tercer método.

Invitación

Si conoces algún sistema para optimizar el rendimiento de tus hojas de cálculo y quieres verlo publicado en este blog con tu nombre, envíalo a:

Si te ha gustado el artículo, puedes apoyarnos votando (justo aquí debajo a la derecha) y/o haciendo una visita a los enlaces de nuestros patrocinadores.