JLD Excel en Castellano - Usar Microsoft Excel eficientemente

jueves, septiembre 05, 2013

Calcular el primer día hábil del mes con Excel

Esta nota viene a colación de una consulta sobre cómo poner en una fila (o columna) de una hoja de Excel todos los días hábiles de un mes.

La idea de mi lector era crear una hoja que al activar la hoja, las fechas hábiles aparezcan en un rango determinado.

Excel cuenta con varias funciones para calcular fechas tomando en cuenta feriados: para calcular fechas, DIA.LAB, DIA.LAB.INTL ; para lapsos, DIAS.LAB, DIAS.LAB.INTL.

Para calcular el primer día hábil del mes combinamos las funciones DIA.LAB y FIN.MES de la siguiente manera

En la celda C2 ponemos el número de mes, en la celda C3 el año; en la celda C5 la fórmula

=DIA.LAB(FIN.MES(FECHA(C3,C2,1),-1),1)

Hace el cálculo de esta manera

FIN.MES calcula el último día del mes de la FECHA, tantos meses atrás o adelante como indique el segundo argumento de la función. En nuestro caso "'-1" indica un mes atrás, es decir 31/08/2013.

DIA.LAB calcula el día laboral antes o después de la fecha indicada, según el segundo argumento de la función. En nuestro caso "1" significa el primer día laboral después del 31/08/2013.

Si queremos poner todos los días hábiles del mes corriente en un rango de la hoja hacemos lo siguiente:

En la celda C1 ponemos la función HOY() y le asignamos el nombre definido "fechaActual" (o cualquier otro que les plazca).
El mes y el año lo obtenemos en las celdas C2 y C3 con las funciones =MES y =AÑO. A la celda C2 le asignamos el nombre definido "mesActual".
En la celda C6 ponemos esta fórmula y la copiamos 30 filas abajo (dado que el número máximo de días de un mes es 31)

=SI(MES(DIA.LAB(FIN.MES(fechaActual,-1),FILA()-5))=mesActual,DIA.LAB(FIN.MES(fechaActual,-1),FILA()-5),"")

La condición de la función SI evalúa si la fecha cae dentro del mes definido ("mesActual"), en caso positivo calcula la fecha del día hábil usando como numerador la expresión FILA()-5 (-5 porque empezamos en la fila 6; en caso de comenzar en otra fila hay que modificar la fórmula).
Si la condición no se cumple, la celda no muestra ningún valor.
Si queremos mostrar las fechas en una fila, usamos como numerador el expresión COLUMNA()-x. Por ejemplo, si la primer fecha aparece en la columna D, la fórmula será

=SI(MES(DIA.LAB(FIN.MES(fechaActual,-1),COLUMNA()-3))=mesActual,DIA.LAB(FIN.MES(fechaActual,-1),COLUMNA()-3),"")

Una acotación para el final: DIA.LAB tiene un tercer argumento opcional que permite usar una rango para señalar los días festivos, además de los fines de semana.

jueves, agosto 29, 2013

Cuando Excel se equivoca al ordenar datos

Hay muchos motivos por los cuales se pueden producir desajustes al ordenar una tabla en Excel. Cuando nos topamos con este problema, nuestra primera reacción es "¡hay un bug en Excel!". Pero los que "hemos fatigado los arduos senderos de Excel por años" (parafraseando al inmortal Borges), sabemos que en casi todos los casos la culpa recae sobre el usuario.

Este artículo de Microsoft contiene una lista de errores que pueden causar desajustes al ordenar datos.

Una vez dicho todo esto, veamos el siguiente caso

En esta tabla, los valores de la columna Porcentaje se obtienen de la tabla en la hoja "clientes", usando la función INDICE combinada con COINCIDIR.

Si observamos la fórmula en la columna "Porcentaje" veremos que Excel incluye la referencia a la hoja "ventas" a pesar de ser esta la hoja activa.

Esto se debe a que al construir la fórmula comenzamos en la hoja activa (Ventas), pasamos a la hoja "clientes" para señalar el rango de la matriz de búsqueda de INDICE y luego volvemos a "Ventas" para completar la fórmula.

Esta referencia a la hoja donde se encuentra la fórmula parece superflua pero inocua. Ahora veamos que pasa al ordenar la tabla por tipo de clientes. Como referencia recordemos que las ventas netas del Cliente 1 son 259,508

Las ventas neto del Cliente 1 ahora son 274,773!

Si nos fijamos en las fórmulas en la tabla ordenada por Tipo, veremos que, a pesar de que las referencias a la columna Tipo son relativas (por ejemplo, ventas!C3 para el Cliente 1), al ordenar la tabla éstas actúan como referencias absolutas.
En la imagen anterior podemos ver que el Cliente 10 se encuentra en la fila 3, pero la formula en la celda E3 se refiere a la celda C12, que era la fila del cliente antes de ordenar la tabla.

Para solucionar o evitar este problema lo que hacemos es eliminar la referencia a la hoja activa ("ventas" en nuestro caso) en las fórmulas. Nuestra fórmula ahora se verá así

=INDICE(clientes!$B$2:$B$4,COINCIDIR(C3,clientes!$A$2:$A$4,0))

Ahora, al ordenar los datos por tipo, no se producirá el desajuste

lunes, agosto 26, 2013

Power Pivot es la mejor novedad de Excel en 20 años

Si eres un usuario que utiliza fórmulas y características como:

BUSCARV()
SUMAR.SI()
SUMAR.SI.CONJUNTO()
Tablas dinámicas
Conexiones a bases de datos u fuentes externas
Tablas de Excel
Consolidación de múltiples datos, VBA, etc

entonces este articulo o post te resultará muy interesante pues te ayudará a hacer tus reportes y análisis de una manera nunca antes vista. Power Pivot es una extensión a las cosas que ya conoces sobre Excel.

pero…¿Qué es Power Pivot en realidad?

Power Pivot es la nueva herramienta (complemento de Excel 2010 y 2013) que permite a los usuarios de negocio tomar un papel mucho más importante en la creación de ideas que son relevantes para la toma de decisiones. Es una herramienta de auto-manejo de Inteligencia de Negocio.
Si eres uno de los miles de usuarios de Excel que lucha con la creación de nuevos códigos VBA o tratar de llegar a soluciones que son semi-dinámicas debido a las limitaciones del tradicional Excel, Power Pivot entonces hará tu vida más fácil en casi todos los sentidos.
Para conocer un poco mas el aspecto técnico sobre Power Pivot puedes visitar mi articulo sobre:

¿Qué es Power Pivot? (la pregunta del millón de dólares)

y un video animado explicando más sobre Power Pivot (activar CC)

pero ahora, vamos a la parte que nos interesa.

¿Qué valor puede aportarle Power Pivot a mi trabajo actual?

Antes de comenzar, tengo que decirte algo muy importante... Power Pivot es gratuito! No tienes que pagar absolutamente nada por el complemento.

Ok, ahora podemos comenzar. Voy a darte algunos puntos que necesitas saber sobre Power Pivot que te ayudarán a decidir si en realidad Power Pivot aporta o no valor a tu trabajo:

Limitante de filas/columnas en Excel ya no es un problema: recuerdas que solamente tienes ~1M de filas para trabajar en el Excel tradicional? y que con tan solo 100K ya tienes problemas con el Excel? pues ya no - es un problema del pasado con Power Pivot. Con Power Pivot puedes importar virtualmente una cantidad casi infinita de filas y columnas y seguir trabajando a una velocidad deslumbrante. Mira la imagen de abajo como prueba de una tabla cargada en Power Pivot con un poco mas de 161M de filas :) Imagen tomada de Powerpivotpro (mi buen amigo Rob Collie)
Con Power Pivot puedes trabajar con múltiples tablas: correcto! Power Pivot trabaja con tablas dinámicas y/o funciones de cubo y en vez de utilizar BUSCARV() o VLOOKUP() para "aplanar" las tablas, pues solamente puedes tomar 1 tabla para "pivotear", puedes utilizar múltiples tablas para trabajar y relacionar los datos. Puedes leer un ejemplo de Jorge aquí
Mejores y más flexibles fórmulas: alguna vez has intentado agregar un conteo distintivo en una tabla dinámica? es un problema muy serio y nunca se logra hacer algo dinámico o flexible, pero con DAX, el nuevo lenguaje de fórmulas para Power Pivot, puedes crear cualquier fórmula que te puedas imaginar. Te muestro un ejemplo aquí
Creando una aplicación de BI en la web (Con Excel!): Gracias a la integración de Power Pivot con SharePoint, es posible crear aplicaciones en la Web a partir de Excel que se auto refresquen (actualicen) con los datos necesarios de acuerdo a un horario. En corto, el reporte que creas en Excel ahora puede ser expuesto mediante un explorador en la web sin que hagas ningún cambio. Te dejo un ejemplo abajo de algo que he creado en Excel con Power Pivot y lo he colgado en mi sito de SharePoint para demostraciones. Más adelante te comentare sobre Power BI que es la nueva oferta de Office365 para crear reportes en la Web (que también puedes consumir desde tu iPad, iPhone, Android y como App de W8)

(puedes darle clic en la imagen para poder ver el reporte desde tu explorador o visitando mi sito demo

aquí

)

Con estos 4 puntos creo que tenemos muchos que asimilar pero falta MUCHO mas...(a revisar en próximos artículos a publicar en mi blog y en este blog)

Pero vamos a revisar otros puntos y dejaré algo sumamente interesante para el final que te va a dejar boquiabierto.

¿Cómo consigo Power Pivot?

Si tienes Excel 2010 (cualquier versión) puedes descargar el complemento desde la siguiente pagina y luego seleccionando la versión correcta de Power Pivot (32 o 64 bits)

Power Pivot para Excel 2010

Mientras que para Excel 2013 el complemento viene integrado en ciertas versiones de la aplicación. Para conocer más al respecto te invito a leer la siguiente entrada en mi blog: