Macro y ejemplo: Aplicar filtros con botones y macros

Realizar filtros con botones y macro nos facilitará la elaboración de algunas consultas a tablas muy grandes cuando el usuario final no es usuario avanzado de excel. Cuando aplicamos autofiltros y se intenta aplicar algun filtro, vemos que puede no ser muy amigable al usuario, sobre todo si se trata de un usuario final no avanzado. Pero podemos hacerle la vida mucho mas sencilla

Public Sub AplicarFiltro()
Range("a2").AutoFilter
Range("a2").AutoFilter field:=2, Criteria1:=">5000"
End Sub

Con este código hacemos un filtro sencillo, la segunda linea elimina cualquier filtro que esté activo en el rango A2. En la tercera linea se indica como se creará el filtro, en este caso le decimos que se aplicara en la segunda fila contando desde A2 (en donde se aplicará el criterio sería B2), el criterio es valores mayores a 5000. El criterio siempre debe ir entre comillas a diferencia del campo field si lo escribimos directamente, si va como una variable como veremos mas adelante.

Macro: Obtener cantidad de dias laborales entre dos fechas en Excel 2003

Aqui tenemos el macro que realiza la misma función que "dias.lab" en Excel 2007 para ser ejecutado en Excel 2003 o para realizar cálculos en base a 6 días laborales si no tenemos Excel 2010. Con este macro se puede seguir el ejemplo anterior de la misma manera solo sustituyendo dias.lab por diaslab (sin punto).

Para ver como activar, utilizar y autorizar Macros en tu ordenador ve el siguiente post: Tip: Macros, que son y como activar y ejecutar Macros en excel.

Copiamos todo el siguiente macro en un módulo de Excel y listo.

Ejemplo: Obtener dias laborados en un periodo delimitado con funcion dias.lab

Ya vimos como realizar el calculo de una ficha final utilizando una fecha inicial y la cantidad de días laborales a sumarle a esa fecha inicial mediante un ejemplo práctico. Sin embargo esa manera no nos resulta útil si necesitamos saber cuantos días laborales hubo en dos fechas.

Si debemos cancelar sueldos en base a dias trabajados, descuentos por permisos, pago de bonos en base a días  laborados como bonos de alimentación (cestaticket, sodexo etc), locomoción o transporte entre otros, necesitamos conocer la cantidad de días laborados en un periodo dado, por ejemplo una quincena o un mes.

Para esto vamos a utilizar la función dias.lab (diferente de dia.lab). La función se utiliza casi de la misma forma que en el ejemplo anterior (la diferencia es que el anterior obtenemos la fecha final indicando cuantos días laborales deben haber, en este caso nos dice cuantos días laborales hay entre dos fechas), debemos tener una fecha inicial, una fecha final y una lista de días feriados para no descuadrar el calculo real. (Compatible con Excel 2007 en adelante, si posee Excel 2003 o necesita realizar cálculos en base a 6 días laborales por semana en Excel 2007, el próximo post sera sobre el macro para obtener este mismo efecto).

Antecedente: tenemos una lista de empleados y debemos cancelar el bono de alimentación (cesta ticket) en base a los días trabajados en un mes, sabemos que hay dos días feriados en el mes ademas de los permisos que solicitó cada trabajador.

Macro: Calcular fechas en base a días laborables en Excel 2003 o calcular en base a semanas con 6 días laborales

El post del ejemplo que publique anteriormente para calcular fechas en base a días laborales solo aplica para Excel 2007 y posteriores, sin embargo eso no es motivo para preocuparse, con este sencillo Macro conseguimos emular las mismas cualidades de la función dia.lab e incluso incluir la posibilidad de tener semanas con 6 días laborales (como la función dia.lab de Excel 2010)

Para ver como activar, utilizar y autorizar Macros en tu ordenador ve el siguiente post: Tip: Macros, que son y como activar y ejecutar Macros en excel

Tip: Macros, que son y como activar y ejecutar Macros en excel

Los Macros son funciones que podemos programar en un editor de Visual Basic para Excel que nos permite complementar la gran cantidad de funciones preestablecidas de Excel.

Para poder utilizar los Macros en Excel y sacarle el mayor provecho es necesario configurar la seguridad del mismo. Lamentablemente los Macros fueron aprovechados hace tiempo para ejecutar códigos maliciosos y por esto vienen por defecto deshabilitados.

2do Ejemplo: Calculando fechas en base a días hábiles con dia.lab

Para la mayoría de las empresas, es muy necesario hacer operaciones con los días laborables de un mes por diferentes razones; puede ser vencimientos de facturas, pago de bonos por días trabajados, fechas tope de créditos, entre muchas mas. Para ésto no hace falta tener un calendario y un lapicero para contar todos los días laborables que hubo en un mes o en un periodo delimitado, como demostraré a continuación (Compatible con Excel 2007 en adelante, si posee Excel 2003 o necesita realizar cálculos en base a 6 días laborales por semana en Excel 2007 siga este vinculo para ver como hacerlo).

Antecedente: tenemos nuestra empresa recuperadora de créditos, debemos elaborar un cronograma correcto de los clientes a los que debemos cobrar de la manera mas eficiente posible, realizando cobros luego de 5 días hábiles de vencer el crédito, luego 3 días hábiles y por ultimo 1 día hábil después de la última cobranza, sin perder las gestiones realizadas por no tomar en cuenta días festivos o fines de semana.

Ya tenemos la base de datos de los nombres y la fecha en que venció el crédito del cliente, por lo que vamos a empezar a calcular cuando debemos hacer los cobros.

1er Ejemplo: combinar Word y Excel para imprimir reportes dinámicos

Uno de los grandes problemas que había comentado de Excel es el problema para hacer reportes que sea estéticamente agradables a la vista y no estén descuadrados gracias a las celdas que no podemos mover individualmente, sin embargo la solución a esto es algo sencillo que nos ofrece Word, la correspondencia combinada.

Explicaré el ejemplo colocando un antecedente de que tenemos y que vamos a necesitar:

Tips para optimizar el uso de Excel

Otro punto importante antes de comenzar con formulas es la optimización de Excel. Son muchos los  detalles que podemos personalizar en Excel y a medida que se hagan necesarios los iré mencionando o profundizando dedicándolas un post completo.

Una de las herramientas que mas utilizo son los resultados rápidos en la barra de estado (parte inferior derecha de Excel). Es la que nos proporciona resultados de operaciones a vuelvo de pájaro realizando una selección de celdas que contengan números o datos. Podemos seleccionar entre varias operaciones bastante útiles cuando no queremos desperdiciar tiempo sacando resultados temporales cuando podemos obtenerlos aca.

Desventajas de Excel, no todo es tan bueno...

Así como las innumerables ventajas que nos regalan las hojas de cálculo para realizar muchas labores, también pueden ser un dolor de cabeza por ser un programa enfocado a celdas fijas, independientes e inmodificables, les muestro algunas de las desventajas en una pequeña lista, que seguramente a medida que se desarrolle el blog se irán viendo mas, pero rara vez será suficiente para abandonar el uso de una hoja de cálculo, solo sera necesario agregar mas pasos para sortear estas dificultades.

Ventajas de Excel u otras hojas de calculo

Entre los últimos tópicos introductorios que puedo yo escribir acerca de Excel son las ventajas y desventajas. La idea de todo esto es utilizar Excel de una manera muchísimo mas eficiente y desarrollar todo su potencial sin recurrir a un programa externo que muchas veces no hacen falta para poder resolver problemas que se nos presentan en la vida laboral.

Muchas veces nuestros problemas llegan a ser tan complejos que pensamos que no se pueden resolver con un programa que creemos tan sencillo como el Excel, esto radica en el poco conocimiento de formulas que existen y que puede lograr cada una, ya sea sola o combinada con otras.

Hay muchas opciones, ¿cual utilizar?

Esto es lo primero que debo definir antes que nada, la herramienta que yo siempre utilizo es Excel, se que hay hojas de cálculo incluidas en software libre, pero en mi experiencia las empresas e instituciones prefieren utilizar programas como MS Office por el soporte que ofrece, la estabilidad y la seguridad. Sin embargo esto no necesariamente es un problema, la mayoría de hojas de calculo son compatibles entre sí, las formulas son reemplazadas automáticamente por la forma genérica para ser aceptada en distintos idiomas, distintos programas y distintas plataformas (hasta aplicaciones móviles hacen este reemplazo).

1er Post... ¿Por que lo hago?

Hoy he sentido la imperiosa necesidad de comenzar con este blog al ver en 3 años de labor, lo sub utilizada que es la potente herramienta de hoja de calculo (excel y similares). Muchas empresas, instituciones y personas lo utilizan para cálculos simples, para horarios o para almacén de datos ordenados. Es mas que obvio que esa es una buena utilidad para esta herramienta, sin embargo al utilizarlo por ejemplo como almacén de datos, no utilizan filtros que hacen la búsqueda de datos mucho mas sencilla y ordenada. Siempre se puede llegar mas allá con una hoja de cálculo bien ejecutada. Ya tendré tiempo de explorar las múltiples opciones que pueden resultar útiles  para cualquier ente o empresas que pueda necesitarlo.