La función desbordada ORDENAR o SORT

Hoy veremos otra de las funciones desbordadas imprescindibles, la función ORDENAR (SORT).
Su sintaxis:
=ORDENAR(matriz;[ordenar_indice];[criterio_ordenación];[por_col])

siendo
matriz: El rango o matriz para ordenar
[ordenar_indice]: Un número que indica la fila o columna por la que ordenar
[criterio_ordenación]: Un número que indica el orden deseado:
1 para orden ascendente (predeterminado)
-1 para orden descendente
[por_col]: Un valor lógico que indica la dirección de ordenación deseada
FALSO para ordenar por fila (predeterminado)
VERDADERO para ordenar por columna

Veamos algunos ejemplos.
Partamos de una Tabla (llamada 'TblDatos') con información de empleados, departamentos y unidades vendidas, la cual queremos recuperar ordenada por unidades vendidas...
Con la fórmula desbordada siguiente:
=ORDENAR(TblDatos;3;1;FALSO)
Hemos indicado que nos ordene la matriz 'TblDatos' teniendo en cuenta la tercera columna (Uds vendidas en el ejemplo) en sentido ascendente y en sentido vertical, i.e., por fila.

Recuerda que estas funciones desbordadas (dynamics arrays) son volátiles, por lo que cambios en la matriz se reflejan automáticamente en nuestro rango desbordado.

Un uso muy frecuente de esta función es el empleo combinado o anidado con otras funciones desbordadas.

Por ejemplo, la siguiente función devuelve los departamentos registrados ordenados en sentido ascendente:
=ORDENAR(UNICOS(TblDatos[Departamento]))

El siguiente ejemplo muestra los registros ordenados en descendente, teniendo en cuenta el departamento, siempre que sea el departamento AAA o CCC y las unidades superen las 50
=ORDENAR(FILTRAR(TblDatos;((TblDatos[Departamento]="AAA")+(TblDatos[Departamento]="CCC"))*(TblDatos[Uds vendidas]>50));2;-1)

Un último ejemplo.
Listaremos horizontalmente, de manera única, los empleados que han trabajado en cada departamento
=TRANSPONER(ORDENAR(UNICOS(FILTRAR(TblDatos[Empleado];TblDatos[Departamento]=$F4));1;-1))

Prueba a eliminar la función UNICOS de la 'ecuación' y notarás la diferencia...
=TRANSPONER(ORDENAR(FILTRAR(TblDatos[Empleado];TblDatos[Departamento]=$F4);1;-1))

NOTA IMPORTANTE: Todas las fórmulas desbordadas NO funcionan dentro del contexto de Tabla!!, y por tanto SIEMPRE se deben emplear en celdas 'normales' de la hoja de cálculo.
:'(

Seguir leyendo...

La función desbordada FILTRAR o FILTER

Seguimos con las nuevas funciones desbordadas (dynamic arrays), y en esta ocasión hablaremos de la función FILTRAR o FILTER.
Con la siguiente sintaxis:
=FILTRAR(array; [include];[if_empty])
siendo
array: el rango de datos a mostrar cuyos elementos cumplan los criterios del siguiente argumento.
include: criterio/s a aplicar
if_empty: qué mostrar/hacer en caso de no haber elementos filtrados que cumplan los criterios.

Partamos de la siguiente tabla de empleados por departamento y ventas:

Sobre nuestra tabla o rango de datos podemos aplicar la función FILTRAR para recuperar los empleados de un departamento concreto (el AAA).
=FILTRAR(TblDatos[Empleado];TblDatos[Departamento]=B$3;"-")

O los que cumplan un criterio múltiple, por ejemplo, que pertenezcan al departamento AAA o al CCC:
=FILTRAR(TblDatos[Empleado];(TblDatos[Departamento]=B$3)+(TblDatos[Departamento]=C$3);"-")
empleando la suma de 'criterios' aplicamos un operador lógico O (como en tantas ocasiones cuando usábamos fórmulas matriciales)

O también aplicar un filtro para recuperar empleados que pertenezcan al departamento AAA y además hayan vendido más de 50 unidades.
=FILTRAR(TblDatos[Empleado];(TblDatos[Departamento]=B$3)*(TblDatos[Uds vendidas]>C$3);"-")
el producto de 'criterios' replica el operador lógico Y.

Por supuesto, está función desbordada la podemos combinar/anidar con cualquier otra... pero esto será en otra publicación del blog.

Seguir leyendo...

La función desbordada SECUENCIA o SEQUENCE

Continuamos con estas nuevas funciones de Excel, y hoy veremos la función desbordada SECUENCIA
La función SECUENCIA generará una lista de números secuenciales en una matriz o vector de tamaño controlado, como por ejemplo 10, 20, 30 y 40.
Su sintáxis
=SECUENCIA(filas;[columnas];[inicio];[paso])
siendo:
filas: indicamos el número de filas de la matriz a devolver,
columnas: indicamos el número de columnas de la matriz a devolver,
inicio: indica el primer valor de la secuencia,
paso: es el incremento dado entre valores seguidos.

Por ejemplo, la función
=SECUENCIA(10;1;10;10)
devuelve un vector de diez elementos con inicio en 10 e incrementando cada elemento de 10 en 10.

O también podríamos generar una matriz de 3 filas x 4 columnas comenzando en PI y de valores incrementados número E elevado a PI:
=SECUENCIA(3;4;PI();EXP(PI()))

Otro ejemplo más práctico sería obtener un número de columnas variable según el mes elegido...
En la celda B5 añadimos la fórmula desbordada
=SECUENCIA(1;DIA(FIN.MES(FECHA($A$1;$A$2;1);0));1;1)
y en B4
=ELEGIR(DIASEM(FECHA($A$1;$A$2;B5#);2);"L";"M";"X";"J";"V";"S";"D")
Atención a la almohadilla de B5 para que desborde asociada a la función SECUENCIA anterior.
En A1 disponemos el año y en A2 el mes a mostrar.

Además añadimos un formato condicional personalizado que dinamiza con color los sábados y domingos.

Seguir leyendo...

La función desbordada MATRIZALEAT o RANDARRAY

Hoy empezaremos a conocer cuáles son las nuevas funciones desbordadas.
Recuerda que solo están disponibles en ciertas versiones de Office 365 a día de hoy...

Comenzaremos con una sencilla como es MATRIZALEAT (RANDARRAY):
=MATRIZALEAT([filas];[columnas];[min];[max];[entero])

los argumentos son sencillos...
[filas] y [columnas] definen la dimensión de nuestra matriz aleatoria n filas x m columnas.
[mín] y [máx] determinan los valores entre los que se moverá cada elemento de la matriz.
[entero] determina si los valores devueltos tendrán o no decimales.

Esta función es bastante similar a la función estándar ALEATORIO.ENTRE (o ALEATORIO).

Son funciones volátiles, lo que implica que se recalcularán automáticamente ante cualquier cambio/edición de una celda en el libro.

El uso más básico consiste en generar elementos aleatorios... por ejemplo, una matriz de 2 filas x 4 columnas, de elementos entre 0 y 10 y solo admitiendo números enteros:
=MATRIZALEAT(2;4;0;10;VERDADERO)

Por supuesto podemos combinarlas con funciones como ELEGIR, BUSCARV, etc...
Por ejemplo:
=ELEGIR(MATRIZALEAT(10;1;1;3;VERDADERO);"España";"Francia";"Portugal")

Pero sin duda un uso curioso es la obtención de un listado de elementos únicos aleatorios sin repetición.
Se conseguirá, eso si, empleando otras funciones desbordadas aún no vistas...
=ORDENARPOR(SECUENCIA(10;1;1;1);MATRIZALEAT(10;1;1;10;1))
se puede comprobar que tenemos dispuestos aleatoriamente, de manera única, los diez primeros números...

Seguir leyendo...

Excel Fórmulas Desbordadas-Dynamics Arrays

Hoy es un día especialmente importante para Excel, y para todos nosotros...
Si eres suscriptor de Office 365 en el canal mensual, y tienes la última versión 1912 instalada, desde hoy tienes a tu disposición las esperadas fórmulas desbordadas/dynamics arrays !!.

Si tienes Office 365 con canales semianual o anual tendrás que esperar un poco más.
:'((

Si te preguntas qué es esto de las fórmulas desbordadas/dynamics arrays, debes saber que en mi opinión personal es el salto tecnológico más importante en la evolución de Excel desde hace muchos años...
Ten presente que estas funciones han estado en beta desde octubre del 2018 para los usuarios del programa Insiders de Excel, por lo que el rodaje está hecho y testado con suficiente contundencia.

A partir de ahora dispondrás de algunas nuevas funciones:
FILTRAR(array;include;[if empty])

MATRIZALEAT([filas];[columnas];[min];[max];[entero])

SECUENCIA(filas;[columnas];[inicio];[paso])

ORDENAR(matriz;[ordenar_indice];[criterio_ordenación];[por_columnas])

ORDENARPOR(matriz;por_matriz1;[orden1];...)

UNICOS(matriz;[por_columna];[exactly_once])

Otra novedad es que desde ahora te acostumbrarás a dos caracteres:
# (almohadilla-numeral) y @ (arroba)

La @ (arroba) de uso habitual en las formulaciones de Tablas aplicará ahora especialmente con la fórmulas desbordadas como operador de intersección implícita.
Esta intersección implícita siempre ha existido, pero quizá no eramos conscientes, ya que se realizaba 'silenciosamente'.
OJO!!, por que es posible que, desde ahora, veas que @ aparecen en algunas fórmulas cuando se abre en las fórmulas desbordadas-dinamycs arrays.
Es importante tener en cuenta que las fórmulas continuarán calculando de la misma manera que siempre!!.

El otro caracter importante es # (almohadilla-numeral) que permite trabajar sobre los valores desbordados por estas nuevas funciones.

No entraré en detalle ahora, por que voy a escribir una serie de artículos sobre estas funciones ;-)

Como verás ahora hablamos de desbordamientos o derrames (spill en inglés)... esta idea consiste en que una fórmula ha generado varios valores y esos valores se han colocado en las celdas vecinas!!, tan solo introduciendo la fórmula una sola vez en la celda superior. Y presionando únicamente Enter... ya no aplica la combinación matricial CSE (Ctrl+Mayusc+Enter).
Obviamente solo se puede editar la primera celda del área desbordada... el resto aparecerán en gris, siendo no editables.
Además al seleccionar cualquier celda del rango desbordado, Excel colocará un borde resaltado en torno al rango.

Una curiosidad a tener en cuenta es que las fórmulas desbordadas-dynamics arrays no se admiten en las tablas de Excel, por lo que deberemos colocarlas en la cuadrícula fuera de la tabla.

Otro aspecto a considerar es la superposición de fórmulas desbordadas, y es que no se pueden escribir si hay algo que bloquea el rango de celdas de salida esperado. Si esto ocurriera, Excel devolverá un error #SPILL! que indica que hay bloqueo.

Las fórmulas matriciales heredada escritas mediante Ctrl+Mayusc+Enter (CSE) aún se admiten por razones de compatibilidad con versiones anteriores, pero ya no se pueden usar!!.

Y otro punto a considerar es cuando trabajamos entre distintos libros. Excel ofrece soporte limitado para las fórmulas desbordadas entre libros. Solo se ofrece soporte para este escenario cuando ambos libros están abiertos!!.
Si cerramos el libro de origen, las fórmulas desbordadas vinculadas devolverán un error #REF! al actualizarse.

Seguir leyendo...