Retornando los registros superiores o inferiores sin filtro ni tabla dinámica en Excel

Devolver los primeros o últimos n registros de un conjunto de datos en Excel no es difícil, y existen muchas formas de hacerlo. Puedes filtrar, usar expresiones o incluso una tabla dinámica; tu elección puede depender de cómo estés utilizando los resultados. Pero ahora, gracias a las nuevas funciones de matrices dinámicas, puedes usar una sola expresión para devolver tantas columnas y filas de los datos fuente como desees. En este artículo, hablaré un poco sobre estas funciones. Luego, las aplicaremos para devolver los primeros y últimos n registros.

Estoy utilizando Microsoft 365 (desktop) en un sistema Windows 10 de 64 bits. Ambas funciones de matriz dinámica en esta solución están disponibles en Microsoft 365 y Excel 2021, Excel para la web, Excel para iPad e iPhone, Excel para tabletas y teléfonos Android. Para tu comodidad, puedes descargar el archivo .xlsx de demostración. Este artículo asume que tienes conocimientos básicos de Excel, pero incluso un principiante debería poder seguir las instrucciones sin problemas.

Poniéndonos al día

En el pasado, devolver los primeros o últimos n registros requería un poco de trabajo y ciertos conocimientos especializados. Podías usar un filtro avanzado, una expresión o incluso una tabla dinámica. Lo bueno de las nuevas funciones de matrices dinámicas es que no alteran los datos fuente; estas funciones crean un nuevo conjunto de datos.

Si no estás familiarizado con las formas antiguas de devolver los primeros o últimos n registros, es posible que quieras leer algunos de los siguientes artículos sobre este tema:

• Cómo mostrar los primeros n registros en una tabla dinámica de Excel utilizando las opciones incorporadas
• Cómo resaltar los primeros n valores en una hoja de cálculo de Microsoft Excel
• Sumar los primeros n valores en un rango utilizando el comportamiento de tabla dinámica en Excel
• Necesitamos una mejor solución: ¿Cómo extraer los primeros n registros en Excel?

No están necesariamente desactualizados, especialmente si quieres trabajar con los datos fuente en su lugar. Si quieres trabajar con un nuevo conjunto de datos, las nuevas funciones son la solución.

Cómo crear una ordenación personalizada en Excel

La Figura A muestra un conjunto de datos simple como una tabla. Queremos devolver los primeros y últimos n valores en la columna Valor. Una combinación de las funciones que veremos en la siguiente sección devolverá los registros n. El nombre de la hoja es Datos; lo necesitarás en un momento.

Figura A

Acerca de las funciones en Excel

Utilizaremos tres funciones: SORT(), SEQUENCE() e INDEX(). Las dos primeras son funciones de matriz dinámica y son bastante nuevas en Microsoft 365. INDEX() ha estado disponible durante mucho tiempo y es posible que ya estés familiarizado con ella. Pero primero, ¿qué es una función de matriz dinámica?

Si alguna vez has ingresado una expresión usando Ctrl + Mayús + Intro, entonces ya estás familiarizado con cómo Excel solía trabajar con matrices dinámicas. Gracias a la nueva función de matriz dinámica, este tipo de expresiones pueden ser mucho más fáciles de crear y mantener. Los resultados se "derraman" en las celdas de abajo, llenando tantas como sea necesario para completar los cálculos de la expresión. Eso se llama "rango de derrame". Si ves un error de derrame, significa que el rango necesario para completar la función no está disponible.

Ahora, pasemos a las funciones.

Cómo actualizar tu cuenta a iCloud+ y aprovechar sus opciones de privacidad y seguridad

SORT() devuelve una matriz ordenada utilizando la siguiente sintaxis:

SORT(array, [sort_index], [sort_order], [by_col])

donde array es el único argumento requerido e identifica el rango a ordenar. Los argumentos opcionales son los siguientes:

• sort_index: Un valor numérico que identifica la fila o columna por la cual ordenar
• sort_order: El número 1 para orden ascendente o -1 para descendente, siendo 1 el valor predeterminado
• by_col: Los valores lógicos TRUE para ordenar por fila y FALSE para ordenar por columna, siendo TRUE el valor predeterminado

Para devolver los primeros o últimos n registros, el conjunto de datos debe estar ordenado, y usaremos la función SORT() en lugar de un método manual.

SEQUENCE() devuelve una serie de valores utilizando la siguiente sintaxis:

=SEQUENCE(filas, [columnas], [inicio], [paso])

3 formas rápidas y fáciles de lidiar con celdas en blanco en Excel

donde filas es requerido y especifica el número de filas para llenar. Los argumentos opcionales son los siguientes:

• columnas: el número de columnas a devolver
• inicio: el primer número de la secuencia
• paso: la cantidad en que incrementar

En su forma más simple, podrías usar esta función para devolver una serie de valores fijos, pero realmente brilla cuando quieres devolver todas las columnas del conjunto de datos fuente. Al combinar las dos funciones, puedes devolver un conjunto de datos completo y ordenado.

La última función, INDEX(), devuelve un valor o una referencia a un valor de una tabla o rango utilizando la siguiente sintaxis:

INDEX(array, num_fila, [num_columna])

donde array es requerido y hace referencia a un rango o una matriz constante. Si array contiene solo una fila o columna, el argumento num_fila o num_columna correspondiente es opcional. Si array tiene más de una fila y más de una columna, y solo se utiliza num_fila o num_columna, INDEX() devuelve una matriz de toda la fila o columna en array. Los dos últimos argumentos pueden ser necesarios o no:

• num_fila es requerido a menos que esté presente num_columna. Selecciona la fila en array de la cual devolver un valor. Si se omite num_fila, se requiere num_columna.
• num_columna es opcional y selecciona la columna en array de la cual devolver un valor. Si se omite num_columna, se requiere num_fila.

Ahora, comencemos a usar estas funciones, comenzando por SORT()

Cómo utilizar la aplicación de traducción de Apple para conversaciones en diferentes idiomas

Cómo usar SORT() en Excel

Para devolver los primeros o los últimos n valores de cualquier conjunto de datos, necesitas un conjunto de datos ordenado. Gracias a SORT(), no es necesario hacer una ordenación manual. Para ver cómo funciona SORT(), lo utilizaremos para devolver un conjunto de datos ordenado del conjunto de datos de ejemplo (Figura A). Primero, debes copiar los encabezados de columna a otra área de la hoja, o incluso a otra hoja. Voy a usar otra hoja para que puedas ver lo fácil que es lograrlo. Copia las celdas de encabezado B2:F2 en una segunda hoja. Luego ingresa en B3 (de la segunda hoja) la siguiente función:

=SORT(Data!B3:F13, 2)

Figura B muestra los datos fuente ordenados por el campo Valor. Así es cómo funcionan los argumentos en la función SORT():

• Data! es el nombre de la hoja donde están los datos fuente.
• B3:F13 es el conjunto de datos original.
• 2 es el valor del índice que especifica la columna Valor, la segunda columna en B3:F13. Cambiando el valor del índice de 2 a 1, 3, 4 o 5, puedes mostrar una o todas las columnas.

Figura B

¿No fue fácil?

Cómo configurar Google Workspace para tus vacaciones: calendario

Si seleccionas estas referencias, Excel mostrará una referencia estructurada porque los datos fuente son una tabla. Querrás dar formato a los resultados porque la función SORT() no lo hace. Esto es un poco molesto cuando intentas usar esto en un tablero de control. Querrás agregar un procedimiento de VBA que aplique el formato necesario. Esta es la única limitación con la que me he encontrado.

Esa es una ordenación simple, pero para lograr un conjunto de datos superior o inferior, deberás agregar SEQUENCE() e INDEX().

La solución

La función SORT() puede devolver fácilmente el conjunto de datos completo en orden ascendente o descendente. También queremos limitar la cantidad de registros devueltos, por lo que agregaremos SEQUENCE() e INDEX() en la siguiente forma:

=INDEX(SORT(array, sort_index, sort_order), SEQUENCE(no_registros), SEQUENCE(primer_columna, ultima_columna))

Los argumentos para la primera función SEQUENCE() serán valores de entrada, por lo que el usuario puede cambiar la cantidad de registros devueltos. Eso significa que necesitamos celdas de entrada. Utiliza la Figura C como guía para configurar los rangos superior e inferior y el rango de la celda de entrada. Debido a que inserté filas en la parte superior para acomodar las celdas de entrada, las referencias de rango que has estado viendo serán diferentes, así que no te confundas.

Figura C

Cómo instalar SuiteCRM en tu negocio para gestionar clientes y oportunidades

Comencemos con el conjunto de datos superior, que requiere una ordenación descendente, expresada como -1 en el argumento sort_order de la función SORT(). En H6, ingresa la siguiente función:

=INDEX(SORT(B6:F16, 2, -1), SEQUENCE(I2), SEQUENCE(1, 5))

Devolverá un error de cálculo porque no hay un valor en I2, la celda de entrada. Ingresa 3 en I2 para devolver el conjunto de datos que se muestra en la Figura D. Ya sabes cómo funciona la función SORT(); en este caso, sirve como el argumento matriz de la función INDEX(). SEQUENCE(I2) es el argumento row_number y devuelve 3 porque el valor de entrada en I2 es 3. En consecuencia, devuelve un conjunto de datos descendente con tres filas. SEQUENCE(1, 5) especifica las columnas 1 a 5 en el conjunto de datos fuente. En resumen, SORT() devuelve un conjunto de datos completamente ordenado, pero las dos funciones SEQUENCE() lo limitan a tres filas e incluyen todas las cinco columnas.

Figura D

La Importancia de la Integración de las Redes Sociales en las Herramientas de CRM

La función para el conjunto de datos inferior funciona de manera similar, pero omite el argumento sort_order porque ascendente (1) es el valor predeterminado. Además, hace referencia a I3 como la celda de entrada. Podrías usar un solo celda de entrada y hacer que ambos conjuntos de datos hagan referencia a ella, pero de esta manera ambos conjuntos de datos pueden tener un número diferente de filas.

En N6, ingresa la siguiente función:

=INDEX(SORT(B6:F16, 2), SEQUENCE(I3), SEQUENCE(1, 5))

Los detalles básicos son prácticamente los mismos. El argumento sort_order predeterminado, que no se ingresa, devuelve un orden ascendente, por lo que obtienes los valores más bajos en la parte superior de la ordenación. Al hacer referencia a I3, determinas cuántas filas devolver. La Figura E muestra el valor de entrada de 4. En consecuencia, la función combinada devuelve las cinco columnas para las primeras cuatro filas, los cuatro valores inferiores en Valor.

Figura E

Cómo cambiar el espaciado de los iconos en el escritorio de Windows 11

Al principio, todo parece un poco más complejo de lo que te gustaría, pero una vez que te familiarices con las nuevas funciones de matrices dinámicas, más fáciles serán tus soluciones.

Mención destacada

Anteriormente mencioné que estas funciones podrían devolver un error si el rango de derrame no está disponible. Si esto sucede, selecciona el rango de derrame completo y elimina todo: datos, formatos, todo, y comprueba si eso no ayuda. Cabe mencionar las celdas fusionadas. Por alguna razón, eliminarlas no las borra por completo. Es posible que debas mover todo a una nueva hoja.

Estén atentos

La única limitación que he notado es la incapacidad de las funciones para mantener el formato. En un futuro artículo, compartiré un procedimiento de VBA que aplique el formato adecuado cuando cambies una celda de entrada.

En Newsmatic nos especializamos en tecnología de vanguardia, contamos con los artículos mas novedosos sobre Software, allí encontraras muchos artículos similares a Retornando los registros superiores o inferiores sin filtro ni tabla dinámica en Excel , tenemos lo ultimo en tecnología 2023.