lunes, mayo 02, 2016

Excel 2016

La encuesta "Qué versión de Excel usas" que llevé adelante las últimas semanas me deparó ciertas sorpresas. Los resultados fueron

En total participaron 234 usuarios. Lo sorprendente no es que el 39% de los encuestados usen Excel 2013 (mi intuición decía que la mayoría usaba Excel 2010) sino que Excel 2016 con el 31% ocupara el segundo lugar.
La consecuencia más inmediata de la encuesta es que he instalado Office 2016, que será desde ahora la "versión oficial" de este blog.

Sobre las novedades y mejoras pueden leer en el blog oficial de Microsoft y en muchos otros buenos sitios y blogs de colegas. Así que aquí haré sólo una reseña, como para salvar un poco el prestigio de este blog que viene a ocuparse del tema con notable atraso (Office 2016 fue anunciado el 22 de setiembre del 2015).

Obtener y transformar (alias Power Query)

Lo que hasta la versión anterior era un complemento ha pasado a ser parte integral de Excel y ha sido rebautizado como "Obtener y transformar" en la ficha Datos de la cinta

Power Query, o con su nuevo nombre Obtener y Transformar", se encuentra en estado de permanente desarrollo y se ha vuelto, para los que analizamos gran cantidad de datos de distintas fuentes, en una herramienta fundamental.


Nuevas Funciones

En la última actualización Microsoft ha agregado nuevas funciones, pero me apresuro a aclarar que sólo están disponibles en la versión Office 365 (por suscripción).  Supongo y espero que en breve también estén disponibles para la versión de escritorio.

Es curioso que Microsoft no haya actualizado la versión de escritorio ya que estas funciones están disponibles en la versión on-line que es gratuita.

UNIRCADENAS (en inglés: TEXTJOIN)

Al igual que CONCATENAR esta función nos permite unir textos alojados en distintas celdas, pero ingresando el rango de celdas como un único argumento en lugar de celda por celda. Además, podemos definir el separador con un único argumento y definir si ignorar celdas vacías.


Por ejemplo


CONCAT

Reemplaza a CONCATENAR y es muy similar a UNIRCADENAS, pero con menos funcionalidades (sólo permite usar rangos e ignora las celdas vacías por definición)


SI.CONJUNTO (en inglés: IFS)

En lugar de “anidar” funciones SI para obtener el resultado que corresponde al cumplimiento de una determinada condición, podemos escribir las condiciones y el resultado en una serie y el resultado será el de la primera condición que se cumpla.


El caso clásico para esta función es determinar un porcentaje de descuento en función del monto de la compra


CAMBIAR (en inglés: SWITCH)

Función familiar para los usuarios de Access. Permite establecer el resultado como comparación de un variable con una serie de variables posibles


A diferencia de SI.CONJUNTO sólo permite comparar igualdades, es decir, si la variable coincide plenamente o no con los valores de la lista de comparación.

MAX.SI.CONJUNTO (en inglés: MAXIFS) y MIN.SI.CONJUNTO (en inglés: MINFIS)

Mis memoriosos seguidores recordarán el post sobre máximos y mínimos condicionales. Con estas dos nuevas funciones podemos dejar de lado todos los artilugios y calcular los máximos o mínimos sujetos a condiciones en forma natural.



jueves, abril 28, 2016

Power Query - Actualización de abril 2016

Los consuetudinarios lectores de este blog conocen mi entusiasmo por las nuevas herramientas que Microsoft ha incorporado a Excel, en particular por Power Query.

Power Query se halla en estado de desarrollo constante y casi cada mes recibimos actualizaciones de Microsoft.

La del mes de abril incluye, entre otras, nuevas opciones que reseñaré aquí:
  • Posibilidad de anexar (append) varias tablas en una única operación.
  • Ordenar alfabéticamente las columnas existentes en la opción Elegir columnas.
  • Crear una columna de porcentaje con un solo clic.

Anexar varias tablas en una única operación

Hasta la versión anterior de Power Query si queríamos anexar más de dos tablas, teníamos que hacerlo en forma repetitiva o manipulando el código de la consulta en el editor. A partir de ahora podemos hacerlo en una única operación. Por ejemplo, si tenemos estas tres tablas de ventas


la opción Anexar (append) abre este nuevo diálogo


Ordenar alfabéticamente columnas existentes

Una gran ventaja cuando trabajamos con muchas columnas (más de las que se ven en el ejemplo)


Crear una columna de porcentaje con un solo clic.

La ventaja de esta nueva opción es que no necesitamos duplicar la columna sobre la cual queremos calcular el porcentaje. Lo que haremos es seleccionar la columna, y en la pestaña Add Column elegir la opción Percentage en Standard


lunes, abril 25, 2016

Suma condicional de fechas en Excel

Este post viene a colación de una consulta sobre cómo totalizar valores por fechas con más de una condición, más específicamente, entre dos fechas.
Excel pone a nuestra disposición varias herramientas: fórmulas, tablas y tablas dinámicas. Hacer el cálculo con tablas o tablas dinámicas no ofrece mayores inconvenientes. Por ejemplo, si hemos organizado los datos en forma de Tabla, podemos usar el filtro y la opción de Fila de Totales para ver el total de los valores del primer semestre del año



En una tabla dinámica creada con los mismos datos aplicamos también el filtro de fechas


Si queremos o tenemos que usar fórmulas, nos toparemos con dos "bemoles":

  1. la forma en que Excel maneja la fechas dentro de las fórmulas (tema que ya tratamos en el pasado remoto de este blog);
  2. el uso de operadores del tipo "mayor que", "igual", etc. dentro de fórmulas.
Si queremos hacer con funciones el cálculo mostrado más arriba podemos usar SUMAPRODUCTO o SUMAR.SI.CONJUNTO, ya que tenemos dos condiciones.

Con SUMAPRODUCTO usamos 

=SUMAPRODUCTO((B3:B62>=FECHA(2016,1,1))*(B3:B62<=FECHA(2016,3,31))*C3:C62)


La función FECHA convierte la fecha del criterio, por ejemplo 01/01/2016 en su número de serie (42370) ya que esa es la forma en que Excel maneja las fechas.

Si queremos usar SUMAR.SI.CONJUNTO usamos

=SUMAR.SI.CONJUNTO(C3:C62,B3:B62,">="&FECHA(2016,1,1),B3:B62,"<="&FECHA(2016,3,31))
donde podemos ver que concatenamos el operador ">=" con el numeral de la fecha usando el operador &.