Fórmulas desbordadas: Pasar de Matriz a Vector

Hace ya algunos años ya expuse la forma, con funciones estándar, de pasar valores de una matriz n filas x m columnas a un vector de (n x m) filas y 1 columna (ver aquí).
Hoy mejoraremos aquella fórmula empleando la función Desbordada SECUENCIA

Comprobamos que sobre una matriz, en nuestro ejemplo, de 5 filas x 3 columnas (rango B2:D6) con un nombre definido asignado 'ndCiudades' podemos insertar en F2:
=INDICE(ndCiudades; TRUNCAR(SECUENCIA(CONTARA(ndCiudades);1;0;1/3))+1; RESIDUO(SECUENCIA(CONTARA(ndCiudades);1;0;1);3)+1)

Hubiera sido con el método tradicional:
=INDICE(ndCiudades; 1+ENTERO((FILA(A1)-1)/COLUMNAS(ndCiudades)); RESIDUO(FILA(A1)-1+COLUMNAS(ndCiudades);COLUMNAS(ndCiudades))+1)
La mejora viene en el desbordamiento que aparece al emplear SECUENCIA, la cual genera una lista de valores para recuperar datos por fila:
SECUENCIA(CONTARA(ndCiudades);1;0;1/3)
De 15 filas de alto y 1 una columna de ancho
Comenzando desde cero!!
Y con un paso de 1/3 (1 entre el número de columnas). Si lo prefieres: 1/COLUMNAS(ndCiudades)

Por otra parte para recuperar datos por columnas:
SECUENCIA(CONTARA(ndCiudades);1;0;1)
que genera una lista de 15 filas y una columna
Comenzando desde cero!!
Y con un paso de 1.
Ambas secuencias se ajustan, como se exponía en el post previo, con las funciones TRUNCAR (o ENTERO) y RESIDUO:

Estos vectores 'virtuales' conseguidos con SECUENCIA nos sirven para listar y recuperar con INDICE cada uno de los elementos de nuestro rango 'ndCiudades': TRUNCAR(SECUENCIA(CONTARA(ndCiudades);1;0;1/3))+1
con TRUNCAR nos quedamos con la parte entera de esos valores incrementados de 1/3 en 1/3... a cuya parte entera incrementamos con +1
RESIDUO(SECUENCIA(CONTARA(ndCiudades);1;0;1);3)+1
con RESIDUO (euqivale al resto de un cociente), al dividir entre el número de columnas del rango obtenemos la secuencia 1,2,3 correspondiente a nuestras columnas...

La ventaja de emplear SECUENCIA frente a la forma clásica es que automáticamente se desborda, ajustándose a las dimensiones de nuestra matriz.

Llegados a este punto, y como has podido comprobar en la imagen existen ciudades repetidas...
Así pués para obtener un listado de ciudades únicas ordenadas por repeticiones de mayor a menor... aplicaremos la siguiente fórmula (basada en la anterior).
Para facilitar el uso, la fórmula anterior la incluimos dentro de un nombre definido:

Tenemos en estos momentos dos nombres definidos:
ndCiudades =Hoja1!$B$2:$D$6
ndVector =INDICE(ndCiudades; TRUNCAR(SECUENCIA(CONTARA(ndCiudades);1;0;1/3))+1; RESIDUO(SECUENCIA(CONTARA(ndCiudades);1;0;1);3)+1)
Para nuestro objetivo de ordenar ciudades por apariciones necesitaremos llamar a UNICOS y ORDENARPOR.

Obviamente =UNICOS(ndVector) genera el listado de ciudades únicas... pero ordenadas según estaban en el origen.
Nosotros queremos llegar un poco más allá... queremos ordenarlas por número de veces que aparezcan.
Así nuestra fórmula deseada sería (en la celda F2):
=ORDENARPOR(UNICOS(ndVector);CONTAR.SI(ndCiudades;UNICOS(ndVector));-1)

Falta solo añadir un cálculo de conteo sencillo con CONTAR.SI sobre el rango anterior para verificar que el listado es el correcto... Así en G2 añadimos:
=CONTAR.SI(ndCiudades;F2#)

Alternativamente a esta técnica podemos emplear Power Query (como no). En este caso una herramienta ya vista en varias ocasiones en este blog como es Anular dinamización de columnas (Unpivot)
Como siempre, puesto que ya hemos asignado un nombre al rango de celdas, iremos a la ficha Datos > grupo Obtener y transformar > botón Desde tabla o rango; y una vez cargado los datos y ya en el Editor de Power Query nos moveremos hasta el menú Transformar.

Suelo recomendar eliminar el paso creado 'Tipo cambiado' que habrá asignado un type text a las columnas...
Usaremos el asistente de grabación de pasos... muy sencillos.
Primero seleccionamos las tres columnas y presionamos Anular dinamización de columnas (Unpivot), consiguiendo el listado de quince ciudades tal cual aparecen en el rango de celdas (en su caso repetidas)

A continuación seleccionaremos la columna 'Valor' y agruparemos desde el menú de Inicio > grupo Transformar > botón Agrupar por y en la ventana de agrupación indicaremos como 'operación' el Recuento de filas

El resultado es el listado de ciudades únicas con el número de veces que aparecen repetidas a su lado (en la columna 'Recuento')

Obvio el último paso...Aplicar un orden descendente sobre la columna 'Recuento' y Cargar y cerrar en la hoja de cálculo...

Muy potentes herramientas nuevas de las que disponer ;-)