Potencia tus automatizaciones con Excel
Como utilizar el poder de Excel para hacer tu robot sea +500% más rápido
Hace unos meses compartí en mi linkedin como mejoramos el rendimiento de un robot cambiando la lógica del desarrollador por funciones de Excel. El resultado final, un 500% más rápido.
Luego de esa publicación, por alguna razón me he encontrado con muchísimos más casos donde el robot realiza muchas iteraciones haciendo los procesos innecesariamente lentos, cuando Excel lo puede hacer mejor.
Los consejos que doy siempre son para casos concretos: Activar una configuración o cambiar un bucle por una fórmula.
Así que hoy voy a escribir una Guía que agrupe las técnicas de Excel que más he utilizado, junto con vídeos mostrando casos de uso para que puedas utilizarlas en tus futuros robots.
Entonces, lo que veremos hoy será:
🟰Formulas — Como delegar los cálculos a Excel
⚙️Funcionalidades — Funciones que necesitas activar para optimizar rendimiento
👨💻Código — Como crear código python para controlar cada parte de Excel
Empecemos!
Fórmulas en Excel
Las formulas de Excel son una de las funcionalidades más potentes de la aplicación y nos permiten hacer casi de todo, en relación a cálculos.
Estas son las formulas y funciones que veremos hoy:
🪞 =A1 — Referencia a otras celdas
➕ SUMA - SUMAR.SI — Suma un rango o una serie de números
🔎 BUSCARV — Busca un elemento en la primera columna de una tabla o rango y extrae un valor
🤝 INDICE - COINDICIR — Con indice puedes encontrar un elemento por su posición y con coincidir encuentras la posición de un elemento
❌ SI.ERROR - SI.ND — Estas funciones te permiten controlar errores como #!DIV/0! o #N/D
💡Utilizar una fórmula permite que se actualicen los datos ante cualquier modificación del usuario
🪞Referencia a otras celdas
Esta es la fórmula más sencilla que puedes usar pero nos ayudó a mejorar 10x la velocidad de nuestra automatización
Para utilizar esta "formula" simplemente tienes que colocar un igual (=) y la celda que quieres referenciar
Puede que parezca poco útil, pero te servirá cuando necesites copiar datos entre celdas rápidamente. Te dejo un video mostrando un caso de uso 👇
➕SUMAR.SI y SUMAR.SI.CONJUNTO
Cuando tenemos que sumar grandes conjuntos de datos, basados en criterios específicos, tenemos dos funciones que nos brinda Excel: SUMAR.SI y SUMAR.SI.CONJUNTO
Las fórmulas de sumas nos permiten sumar un rango de celdas rápidamente, evitando tener que extraer, iterar y luego sumar los datos por nuestra cuenta.
SUMAR.SI (SUMIF)
Con la función SUMAR.SI podemos sumar valores en un rango que cumpla con un único criterio o una única condición.
💭Esta fórmula le dice a Excel: De este rango de datos, suma solo los valores que sean igual a esta condición
La forma de escribir la fórmula es la siguiente:
En inglés, utiliza coma para separar los parámetros
=SUMIF(Rango, Criterio, Rango a sumar)
En Español, utiliza punto y coma para separar los parámetros
=SUMAR.SI(Rango; Criterio; Rango a sumar)
Veamos un ejemplo de como aplicarlo a un caso sencillo.
Digamos que queremos sumar el costo de todos los productos que tengan un id mayor a 5. La fórmula quedaría así:
Rango: B3:B12 es el rango de las celdas donde quiero que Excel revise la condición que quiero que se cumpla
Criterio: ">5" es la condición que se tiene que cumplir para sumar. Todos los valores mayor a 5
Rango a sumar: C3:C12 es el rango donde se encuentran los valores que se quieren sumar
Sin la fórmula, nosotros o nuestro robot tendría que recorrer fila a fila validando si el id es mayor a 5 y sumar los valores. Dependiendo la cantidad de datos, incluso para un robot puede ser lento, en cambio Excel lo puede hacer en un par de segundos
SUMAR.SI.CONJUNTO (SUMIFS)
La función SUMAR.SI.CONJUNTO permite sumar valores en un rango que cumpla con más de un criterio. Podemos definir tantos criterios como queramos.
💭Esta fórmula le dice a Excel: De este rango de datos, suma solo los valores que sean igual a esta condición, y a la vez igual a esta otra condición e igual a esta otra condición e igual…
La forma de escribir la fórmula de SUMAR.SI.CONJUNTO es la siguiente:
En inglés, utiliza coma para separar los parámetros y español, utiliza punto y coma para separar los parámetros
=SUMIFS(Rango a sumar, Rango 1, Criterio 1, Rango 2, Criterio2, …)
=SUMAR.SI.CONJUNTO(Rango a sumar; Rango 1; Criterio 1; Rango 2; Criterio2; …)
En el ejemplo, digamos que ahora queremos sumar el costo de todos los productos que tengan un id mayor a 5 y menor a 7. A diferencia del ejemplo anterior, necesitamos dos condiciones que se tienen que cumplir:
Rango a sumar: C3:C12 es el rango donde se encuentran los valores que se quieren sumar
Criterio 1: ">5" es la primera condición para decidir que valores sumar. Todos los valores mayor a 5
Rango 1: B3:B12 es el rango con los valores que se comparará con el criterio 1
Criterio 2: "<7" es la segunda condición para decidir que valores sumar. Todos los valores menor a 7
Rango 2: B3:B12 es el rango con los valores que se comparará con el criterio 2. En este ejemplo es el mismo rango 1 pero puede ser diferente
Para este caso agregamos Criterio 1 y Criterio 2, pero puedes agregar más criterios siguiendo la misma lógica.
🔎BUSCARV (VLOOKUP)
Esta es la función que todo usuario de Excel debe conocer y es bastante útil cuando quieres poblar tus datos con información de otro conjunto.
BUSCARV actúa como un puente, conectando distintos datos en base a un valor común, generalmente presente en una columna.
=vlookup("Valor a buscar", "Rango donde buscar", "Columna de matriz a extraer", "Coincidencia")
En este vídeo te explico un caso de uso de como utilizar esta función
🚨👀 Una de las limitantes de esta fórmula es que el dato a buscar tiene que estar siempre en la primera columna del rango
🤝INDICE-COINCIDIR (INDEX-MATCH)
Estas dos funciones son como Batman y Robin. Mientras que COINCIDIR te ayuda a encuentrar la posición de lo que quieres buscar, INDICE te trae el valor que existe en la posición que le indiques.
Esta formula es un reemplazo perfecto para BUSCARV, porque te permite buscar en cualquier columna de tu rango y no solo en la primera.
La sintaxis de cada una es la siguiente:
En Ingles, separamos los valores por coma
=INDEX(Rango, numero de la fila, numero de la columna)
=MATCH(Valor a buscar, rango donde buscar, tipo de coincidencia)
En español, separamos por punto y coma
=INDICE(Rango, numero de la fila, numero de la columna)
=COINCIDIR(Valor a buscar, rango donde buscar, tipo de coincidencia)
Te dejo un vídeo explicando un como utilizar esta función 👇
❌SI.ERROR
Algunas veces por falta de datos o datos erróneos, provocamos errores en cadena en diferentes datos de nuestra hoja de cálculo.
Supongamos que queremos calcular el porcentaje de variación entre un valor obtenido y un valor deseado. Puede darse un caso, donde aún no se tenga información de los valores deseados para ciertos registros y nos generará un error como indica la imagen.
El error #DIV/0! nos indica que no se puede dividir por cero y esto es porque en la columna Esperado no hay información.
Para controlar estos casos es cuando necesitamos utilizar la función SI.ERROR
SI.ERROR nos sirve para poder controlar los errores que ocurran en una fórmula. Sería una especie de try catch de la programación, pero en una fórmula de Excel
La sintaxis de cada una es la siguiente:
En Ingles, separamos los valores por coma y en español, con punto y coma
=IFERROR(formula, valor en caso de error)
=SI.ERROR(formula; valor en caso de error)
Para nuestro ejemplo, quedaría de la siguiente fórmula
Formula — Ingresamos la fórmula que nos genera un error cuando no existe datos para dividir
Valor en cado de error — Ingresamos un 0, para que nos muestre ese valor en lugar de #DIV/0!. Puedes usar cualquier valor que necesites
😅 Mi excel está en portugés, por eso en la imagen se ve un SEERRO, pero el funcionamiento es el mismo
❌SI.ND
La fórmula SI.ND cumple la misma función que SI.ERROR, pero en lugar de controlar cualquier error, controla los errores #N/D, como el que nos apareció en el video de INDICE y COINCIDIR
La sintaxis es la misma que SI.ERROR (en inglés, el error es "#N/A")
=IFNA(formula, valor en caso de error)
=SI.ND(formula; valor en caso de error)
Funcionalidades de Excel
Dedicar tiempo a entender las funcionalidades de las aplicaciones que automatizas es una cualidad escencial para un desarrollador RPA. Y entender las funcionalidades que tiene Excel es obligatorio.
Escribí acerca de entender las aplicaciones que automatizas en mi primer articulo:
Estas son las 3 funcionalidades que más me han ayudado en mis robots:
⌨️ Macros — Lenguaje de programación dentro de Excel
💾 Formato condicional — Resalta celdas o rango de celdas según una o más condiciones
🔢 Opciones de Cálculo — Elije si quieres que los cálculos se hagan Automático, Solo para tablas o Manual
💾Formato condicional
Más de alguna vez nos ha tocado aplicar estilos a nuestras hojas de cálculo para que se vean más bonitas o resalten algunas partes que son importantes.
En el video que te muestro a continuación te explico
Como utilizar formato condicional
Como utilizar formato condicional en Python y Rocketbot
Las ventajas de utilizar formato condicional respecto a un formato normal, es que te permite modificar cualquier valor en tus hojas sin tener que volver a formatear. Ya que el formato condicional se aplica automáticamente ante cualquier modificación.
🔢Opciones de Cálculo
Este consejo ya lo compartí en mi Linkedin y es que me ha ayudado muchísimo en el rendimiento de mis robots, haciendo que procesos que demoran cerca de 1 hora, pasen a demorar solo 3 minutos!!
Las opciones de cálculo le indican a Excel cuando realizar el cálculo sobre las fórmulas que existen en el documento. Si la tiene en automático, Excel se encargará de actualizar en tiempo real cada modificación que ocurra para que la puedas ver.
Al tenerlo de forma automática, Excel puede tardar según la cantidad de datos que tenga tu archivo y si estás haciendo modificaciones todo el tiempo, hacemos que Excel tenga que cálcular cada segundo.
Puedes modificar el tipo de cálculo por cada archivo desde la pestaña fórmula de Excel o de forma general y hacer que se actualice cada vez que se guarde el archivo.
Si quieres hacerlo con Rocketbot, puedes ver el código de la macro de igual forma como lo hicimos con el formato condicional. Luego de eso, convertirlo a Python y agregarlo al incio de tu robot.
Y eso es todo por hoy. Substack me indica que el post es muy largo para el email 😅 así que tendré que continuar en una parte 2 si les gusta este contenido.
Si encontraste valor en este newsletter, considera alguna de estas cosas:
1) Suscribete a mi newsletter — Si aún no lo has hecho, considera convertirte en un suscriptor pago. Puedes ver más sobre los beneficios dando clic acá:
2) Lee con tus colegas — El mejor agradecimiento es tu recomendación. Comparte este artículo con tu compañero que le pueda interesar y consigue membresías gratuitas a través del programa de referido
Ten una gran fin semana! 🚀
Danilo