ORDENARPOR en descendente por acumulados

Revisando acciones realizadas con Power Query me encontré un caso que PQ resolvía de manera muy simple: ordenar países en descendente según sus ventas acumuladas:

No es el caso de este post, pero los pasos en el editor de Power Query serían agrupar por País, y luego ordenar el resultado por el campo ventas.

El asunto es que pensé si era posible obtener lo mismo empleando las nuevas funciones desbordadas... y esto es lo que conseguí.

En H3 escribimos:
=ORDENARPOR(UNICOS(Tabla1[País]);SUMAR.SI(Tabla1[País];UNICOS(Tabla1[País]);Tabla1[Ventas]);-1)

lo cual desborda el listado de países ordenados por las ventas acumuladas en descendente (de mayor a menor)!!.
Recordemos la sintaxis de ORDENARPOR:
=ORDENARPOR(matriz; por_matriz1; [orden1];...)
es decir, ordenamos los elementos de la matriz del primer argumento, según los elementos de la segunda matriz (segundo argumento), en el orden establecido con el tercer argumento...

Con nuestra fórmula.
1er argumento: matriz de elementos/países únicos: UNICOS(Tabla1[País])
2do argumento: matriz sobre la que ordenar la anterior, i.e., las ventas acumuladas por país: SUMAR.SI(Tabla1[País];UNICOS(Tabla1[País]);Tabla1[Ventas])
3er argumento: el tipo de ordenación, -1 para descendente.

Lo interesante del ejercicio es ver cómo SUMAR.SI desborda los acumulados de cada país... Podemos visualizarlo si este SUMAR.SI lo insertamos en K3

Para 'rizar el rizo' podemos ver esos acumulados ordenados de mayor a menor si aplicamos ORDENAR:
=ORDENAR(SUMAR.SI($B$3:$B$14;UNICOS(Tabla1[País]);$C$3:$C$14);1;-1)

Así queda demostrado que con las funciones UNICOS, ORDENARPOR y la clásica SUMAR.SI obtenemos el listado de países ordenados de mayor a menor según las ventas acumuladas.

Adicionalmente hemos conseguido ver esos acumulados (aunque hay formas más simples de lograrlo).

Seguir leyendo...

Gráfico desbordado

Aplicaremos hoy las fórmulas desbordadas a un gráfico estándar, convirtiendo a este en un gráfico desbordado, y por tanto dinámico...
Partimos de una sencilla tabla con datos de países y ventas (rango B2:C12):

El objetivo es tener un gráfico de columnas que muestre los datos acumulados de las ventas de cada país que exista en nuestra tabla...

Para ello en E3 insertamos la función:
=UNICOS(Tabla1[País])
la cual lista los países de manera única...

En la celda de al lado, en F3, añadimos un SUMAR.SI para acumular Ventas... pero le indicamos con # que se 'desborde' junto a UNICOS:
=SUMAR.SI(Tabla1[País];E3#;Tabla1[Ventas])
fíijate en el segundo argumento:
E3#

Si construimos un gráfico sobre el rango actual E2:F7, y posteriormente aumentase el número de países únicos listados, nuestro gráfico no incorporaría estos nuevos elementos.
Para solucionar este inconveniente usaremos los Nombres definidos 'desbordados'.

Entonces creamos un nuevo nombre definido que use el # para referirnos a nuestros rangos E2:E7 y F2:F7.
Nos quedarán estos dos nombres definidos:
ndPais =Hoja1!$F$4#
ndVentas =Hoja1!$G$4#

y

Fíjemosnos en el uso del # para referirnos nuevamente a los rangos desbordados...

Creamos ahora nuestro gráfico de columnas, con el diseño y formato deseado, empleando el rango actual existente E2:F7.
Con el gráfico generado accederemos a la Selección de datos para editar los rangos y poder añadir nuestros Nombres definidos (ndPais y ndVentas):

Entraremos alternativamente en la Edición de etiquetas y a Modificar la serie.

Al Editar las etiquetas del eje horizontal, nos aseguramos que el rango seleccionado queda escrito así:
=Hoja1!ndPais

esto es, el nombre de la hoja, exclamación !, y el nombre definido

De igual forma Modificamos la serie de Ventas, quedando:
=Hoja1!ndVentas

Listo!.

Si aumentásemos los registros en la tabla original, estos nuevos países se mostrarían automáticamente en nuestro gráfico...

Seguir leyendo...

Power Query conectado con Forms

Hace un par de días hablamos de esta aplicación, Forms, y su forma de operar (ver)... y cómo podíamos visualizar las respuestas enviadas por los usuarios haciendo una exportación a Excel de esa info.

Hoy veremos cómo conseguir, empleando Power Query, conectar con esa base de datos de Forms.

Para ello abriremos Forms de una manera alternativa, lo haremos desde Excel Online.
Así pues desde nuestra cuenta de Office abriremos un libro de Excel (importante recordar que por defecto los libros abiertos con excel Online se guardan en nuestro OneDrive!), y buscaremos en su menú Insertar > grupo Tablas > desplegable Forms > Nuevo formulario

Esto nos abrirá la aplicación Forms tal cual vimos en la entrada anterior, y donde podremos construir el formulario deseado; algo así, por ejemplo.

Terminamos de diseñar el Forms y Compartimos para obtener el link del formulario.

Lo interesante de crear de esta forma el Forms es que la base de datos queda incluida, como Tabla, en una hoja del Libro!!, en una hoja llamada 'Form1'.

Este paso ha sido importante, ya que tenemos una tabla de información en un libro de Excel guardado en OneDrive, al cual podremos acceder empleando, en nuestro ejemplo, Power Query.

Sabemos que con la sincronización correcta, podemos acceder a la ruta de nuestro OneDrive visto en nuestro explorador de Windows (OJO!!, siempre que estemos conectados a OneDrive !):

Con el fichero de OneDrive que contiene las respuestas del formulario identificado... procedemos a montar la conexión con Power Query.
Abrimos un libro de trabajo con nuestro Excel de escritorio y creamos la conexión.
Vamos a ficha Datos > grupo Obtener y transformar > desplegable Obtener datos > Desde un archivo > Desde un Libro

Esto es, conectamos con el libro como haríamos con un libro normal y corriente...

Elegimos la 'Tabla1' o la hoja a'Form1' y Cargamos... lo que nos devolverá en nuestro libro de trabajo los datos o respuestas enviadas por los usuarios...

Solo nos queda Actualizar la conexión para recuperar y analizar la información enviada por los usuarios a través de la URL del formulario...

Seguir leyendo...

Forms: Un formulario en la nube para Excel

Pensando estos días tristes sobre que escribir me ha chocado no haber dedicado nunca un post sobre el tema de esta interesante herramienta: Forms, que nos permite construir formularios, encuestas, cuestionarios, sondeos, ... accesibles desde cualquiera con una simple URL.

Algo interesante de la herramienta es que guarda las respuestas enviadas por los usuarios en una tabla dentro de un fichero Excel que, obviamente, podemos consultar.

Es una aplicación en sí misma para usuarios de Office 365, pero en nuestra caso vamos a aportarle algo más.

En este primer post veremos cómo crear este Forms.

Una primera forma consiste en acceder a nuestra cuenta de Office y buscar la aplicación:

Esto abrirá la aplicación que nos ofrece dos alternativas:
1-Crear formulario
2-Crear cuestionario

A efectos prácticos no hay muchas diferencias entre una u otra opción, a parte del simple sentido último: un formulario sirve para captar contactos o información, i.e., no hay respuestas correctas; y un cuestionario para responder preguntas donde si existen respuestas cerradas válidas.
En cuanto a crear objetos en uno y otro el asunto es simple, presionamos el botón de Agregar y nos ofrece alternativas:
- botones de opción
- cuadros de texto
- calificación
- campo de fecha
- y otros como: Ordenación, Likert (evaluaciones de opinión), Carga de archivos...

Un ejemplo podría ser el siguiente...

La creación es tan sencilla que creo no merece la pena detallar los pasos. Resaltar las opciones de hacer 'Obligatorio' o no la pregunta planteada.

Podemos igualmente cambiar el 'Tema', esto es, el fondo visible del Formulario.

Una vez finalizada la batería de cuestiones podremos Configurar opciones de este formulario...
A la derecha del botón 'Compartir' vemos tres puntos que presionaremos en busca de la 'Configuración'

Esta configuración es importante porque nos deja decidir sobre quién podrá rellenar el formulario o controlar fechas de inicio y fin,...

Ahora sí, estamos en disposición de compartir con el mundo nuestro formulario... para ello basta presionar el botón 'Compartir' (arriba a la derecha).

Esto abre un panel con opciones para obtener el vínculo del Formulario, o en qué forma obtendré ese vínculo:
- link
- código QR
- para insertar/embeber en algún lugar (código html)
- enviar por email

También tendremos opciones para permitir a otros usuario la opción de editar el formulario, o para compartirlo como plantilla...

Una vez compartido, ya en la nube, y con los usuarios rellenando los campos, podremos entrar al formulario y visualizar las respuestas dadas. Incluso tendremos la opción de exportar a Excel una tabla con las respuestas.

Y podremos analizar la información recogida...

En próximos post veremos una alternativa de creación de Forms desde un libro de Excel... y cómo recuperar la información grabada de una forma más 'profesional'.

Seguir leyendo...

Validación de datos dependiente con DESREF

Veremos hoy una forma muy interesante de montar una validación de datos dependiente, diferente a la clásica de INDIRECTO (ver ejemplo aquí), empleando la función DESREF.

Solo necesitamos una sola base de datos que cumpla la condición de tener los datos ordenados...

El primer paso es obtener un listado de elementos únicos del campo de Regiones...
Para esto usaré la nueva función desbordada UNICOS (o su alternativa clásica).
En E3 escribimos:
=UNICOS(TblaCCAA[CCAA])

Sobre esa fórmula desbordada montamos nuestra Validación de datos tipo lista en la celda G2:

Y ahora la última parte, donde según nuestra elección de la región en G2, se desplegarán solo los elementos correspondientes...
La fórmula buscada sería:
=DESREF(TblaCCAA[[#Encabezados];[Provincia]];COINCIDIR(G2;TblaCCAA[CCAA];0);0;CONTAR.SI(TblaCCAA[CCAA];$G$2))

Vemos su comportamiento ejecutándola en el rango G6...
El funcionamiento es simple, ubica el inicio del rango variable a mostrar en la primera coincidencia de la región buscada en G2
COINCIDIR(G2;TblaCCAA[CCAA];0)
con una altura del rango equivalente al número de veces que aparece repetida dicha región
CONTAR.SI(TblaCCAA[CCAA];$G$2)

Crearemos un nombre definido 'ndProvincias' con esta fórmula

Este nombre definido lo incluiremos directamente en la validación de datos tipo lista de la celda H2

Y listo.. ya disponemos de nuestra validación dependiente, con una gestión asombrosamente simple de nuevos elementos... más flexible que la forma clásica de INDIRECTO.

Seguir leyendo...

Contar valores de columnas entre tres primeros

Respondiendo, o al menos intentándolo, la duda de un lector que decía:

[...]Necesito una formula matricial? (se que se puede hacer de forma indirecta rellenando otra tabla auxiliar, pero creo que existen formulas que lo pueden hacer directamente) que cuenten el nº de exámenes en los que cada alumno ha sacado una de las 3 mejores notas. Por ejemplo, el alumno 1 tendría que dar 0 veces y  alumno 3 tendría que dar 4 veces.[...]

El objetivo es averiguar para cada elemento cuantas de sus calificaciones se encuentran, por examen, entre las tres primeras notas...
Algo bastante concreto, pero que al intentar dar respuesta me he encontrado con verdaderos problemas para solucionar el tema planteado.

Seguro que algún lector puede aportar una alternativa mejor (sin rangos auxiliares, y empleando fórmulas matriciales).

Tenemos el siguiente rango de notas para cuatro exámenes de nueve alumnos:

La mejor aproximación encontrada ha sido añadir la fórmula matricial en G3:G11:
=(C3:C11>=K.ESIMO.MAYOR(C3:C11;3))+
(D3:D11>=K.ESIMO.MAYOR(D3:D11;3))+
(E3:E11>=K.ESIMO.MAYOR(E3:E11;3))+
(F3:F11>=K.ESIMO.MAYOR(F3:F11;3))

Lo que obtenemos es un vector de V y F (0 y 1) con aquellos valores que cumplan la condición, columna a columna, de superar el tercer valor más alto... acaba la fórmula sumando por fila.

Válida para este ejemplo en el que solo debo evaluar cuatro exámenes... obviamente para un número alto de exámenes esta fórmula se hace muy tediosa...

Alternativas, basadas en rangos auxiliares, hay muchas.
La que más me gusto después de varios intentos es construir una matricial en J3:M11 con:
=--($C$3:$F$11>=K.ESIMO.MAYOR(INDICE($C$3:$F$11;0;COLUMNA($A:$D));3))

Terminamos sumando fila a fila en el rango I3:I11.

Esta matricial devuelve desglosado por fila y columna una matriz de 0 y 1 para aquellos elementos buscados.

Seguir leyendo...

La nueva aventura del Office Scripts en Excel

Hace un par de meses se hizo oficial el anuncio de la nueva herramienta de programación de Excel: Office Scripts, una Preview que puedes leer aquí

Aunque se llevaba un tiempo en pruebas, fue el 7-1-2020 cuando se lanzó al mundo esta herramienta llamada a reemplazar el VBA para Excel
:OO

Desde luego que no hay motivo para la preocupación a corto/medio plazo... nuestras macros en VBA seguirán a nuestro lado muchos años más (sirva de ejemplo que la programación en vb4, que tuvo su fin allá por 1993-creo haber leído alguna vez-, aún está disponible en nuestros libro de trabajo 27 años después!)

Tomémosnos esta nueva herramienta como una nueva oportunidad que nos abrirá un infinito mundo de posibilidades ;-)

Hasta el momento esta herramienta de Office Scripts está disponible para la versión Excel Online bajo licencias de Office 365 E3 y E5, pero como siempre, poco a poco se irá extendiendo al resto de versiones y público en general...

Si quieres jugar un poco con este lenguaje de programación deberás activar previamente desde tu cuenta de administrador de Office un item.
Así pues accede a tu cuenta de Office (o pídele a tu administrador que lo haga) desde aquí
Y una vez dentro presiona el botón de Admin.
NOTA: Asegúrate que tienes marcada la opción (arriba a la derecha) de 'El nuevo centro de administración' !!.

Dentro de administrador, en los menús de la izquierda busca 'Mostrar todos' y luego 'Configuración' y otra vez 'Configuración'

En la ventana de la derecha, entre las opciones de 'Configuración', buscaremos el item: Office Scripts

Esto abrirá un panel a la derecha con una casilla de verificación a marcar: Permitir a los usuarios automatizar tareas en Office la web

Una vez marcada la opción ya podemos salir del Administrador, y entrar en el Excel Online.
Observa que se nos advierte que la herramienta está disponible en el 'Excel en la web', y que de momento es una versión preliminar!!!.

Al entrar en Excel online veremos en la Cinta de opciones una nueva ficha 'Automatizar' con dos botones en un único Grupo: 'Herramientas de scripting':
Acciones de registro
Editor de código

El primer botón 'Acciones de registro' equivaldría al de Grabar macros de VBA y el segundo 'Editor de código' al de llamada del Editor de VB.

En ambos casos se abre un panel de control a la derecha con opciones diferentes.

Si llamamos a las 'Acciones de registro' comienzan a grabarse los pasos y acciones que realicemos...lo que vemos en la ventana inferior de 'Recorded actions' (acciones registradas).

Al finalizar presionaremos el botón Stop de parada

Al detener la grabación nos preguntará por el nombre de nuestro Script y por el Comentario a añadir, si así lo deseamos.
Finalmente Guardaremos / Save o empezaremos de nuevo el proceso de grabación / Restart

El segundo botón de trabajo llama al Editor de código, lo que abre el panel de control del 'Code editor' con opciones para editor Scripts existentes, crear Nuevos Scripts o visualizar ejemplos siguiendo los enlaces mostrados

Para ejecutar/lanzar Scripts existentes basta seleccionarlo y presionar el botón de 'Run'

En próximas entradas del blog veremos algunos ejemplos de Scripts en Excel Online y similitudes/diferencias con la programación en VBA.

Aprenderemos juntos en este nuevo camino...

Seguir leyendo...

Coincidencias entre listas con fórmulas desbordadas

Seguiremos descubriendo bondades de las fórmulas desbordadas (dynamic arrays), en esta ocasión para descubrir coincidencias entre dos listas:

Como notamos los países marcados en amarillo coinciden en ambas listas... y para recuperar un listado único de éstas, insertamos en F3 la fórmula desbordada:
=UNICOS(FILTRAR(Tbl_2019;CONTAR.SI(Tbl_2020;Tbl_2019)))

que incluye los países del 2019 que se encuentran en el listado de países de 2020, lo que se consigue con la función FILTRAR:
FILTRAR(Tbl_2019;CONTAR.SI(Tbl_2020;Tbl_2019))

filtra elementos de 'Tbl_2019' cuando la matriz generada con CONTAR.SI(Tbl_2020;Tbl_2019) sea 1.
Esta matriz-vector de 0 y 1 sería en el ejemplo:
{1;1;0;1;0;0;0}

lo que equivale asociar a cada elemento de la 'Tbl_2019' esos valores, así:
ES 1
FR 1
IT 0
DE 1
BE 0
PT 0
UK 0

De ahí que al FILTRAR, solo obtengamos las tres coincidencias listadas: ES, FR y DE.

Finalmente aplicamos UNICOS a este rango derramado resultado para evitar posibles duplicidades...

Si aún no tienes la suerte de disponer de estas funciones desbordadas (por temas de versión o canal), podrías llegar a algo similar con la siguiente función, insertada en H3 y luego copiada al resto del rango hasta H10 (por ejemplo):
=SI.ERROR(INDICE(Tbl_2019;K.ESIMO.MENOR(SI(CONTAR.SI(Tbl_2020;Tbl_2019)*NO(CONTAR.SI($H$2:H2;Tbl_2019));FILA(Tbl_2019)-MIN(FILA(Tbl_2019))+1;"");1));"")

ejecutada preferentemente matricialmente (Ctrl+mayusc+Enter).

La clave, entre otras cosas, de esta fórmula es la prueba lógica del SI empleado:
CONTAR.SI(Tbl_2020;Tbl_2019)*NO(CONTAR.SI($H$2:H2;Tbl_2019)

que retornaría un vector de 0 y 1 de acuerdo a las coincidencias entre listados, pero teniendo en cuenta los elementos ya listados en el rango H3:H10 anteriores...
Por ejemplo, ese producto en H5 sería:
{0;0;0;1;0;0;0}
esto es, de los países de lal Tbl_2019 solo nos interesaría el de la cuarta posición: DE, ya que los anteriores (ES y DE) ya están listados en H3:H4.

Sin duda una fórmula más compleja ;-)

Seguir leyendo...