Domina Excel: Cómo evaluar las últimas filas en un conjunto de datos en constante cambio

Newsmatic member Jeff has an interesting and challenging Microsoft Excel problem: He wants to apply a conditional count to the last seven rows of a data set that updates daily–that means the solution has to be dynamic. Right now, he can apply the conditional count, but he has to update the formula every day, which is tedious. I’ll show you the solution I suggested for Jeff: Use an OFFSET() function to accommodate the changing data range.

I’m using Microsoft Excel in Office 365 desktop on a Windows 10 64-bit system, but this solution will work in older versions and in the browser. You can work with your own data or download the demonstration .xlsx file (the .xls format won’t support this solution).

APRENDE MÁS:Precios y características de Office 365

¿Por qué necesitas Offset()?

Hemos mantenido el conjunto de datos simple: Dos columnas y una fila para cada día (Figura A). Los valores de estado son los valores condicionales sí y no. Además, es un objeto de tabla.

Figura A

Cómo utilizar la función XLOOKUP en Excel para búsquedas más flexibles y eficientes

Jeff utiliza una función de conteo para devolver la cantidad de valores Sí en las últimas siete filas. Usando referencias de celdas, debe actualizar la referencia todos los días para acomodar el nuevo registro diario. Jeff necesita una fórmula dinámica que ajuste automáticamente el rango de datos cuando se agrega un nuevo registro. Para eso, utilizaremos la función Offset().

¿Qué hace Offset()?

La función Offset() de Excel devuelve un rango especificado por un número específico de filas y columnas, dependiendo de una celda de anclaje. Esta función utiliza la siguiente sintaxis:

OFFSET(referencia, filas, columnas, [altura], [ancho])

En pocas palabras, estás construyendo un rango:

• referencia es la primera celda en el conjunto de datos. En nuestro caso, eso es C3, la primera celda de estado.

  Tres formas de resumir datos en Excel con poco esfuerzo

• filas es el número de filas hacia arriba o hacia abajo que deseas referenciar, basado en referencia.

• columnas es el número de columnas hacia la izquierda o hacia la derecha que deseas referenciar, basado en referencia.

• altura determina la cantidad de filas que deseas que tenga la referencia devuelta. Este es un argumento opcional y debe ser un número positivo.

• ancho determina la cantidad de columnas que deseas que tenga la referencia devuelta. Este es un argumento opcional y debe ser un número positivo.

Ahora, crearemos una función Offset() que Jeff pueda utilizar, comenzando con una celda de entrada para que el rango devuelto sea dinámico.

Cómo utilizar la celda de entrada

Jeff desea que el rango devuelto evalúe las últimas siete filas en el conjunto de datos. Podríamos ingresar 7 en la expresión, pero en su lugar, utilizaremos una celda de entrada para que Jeff pueda cambiar el tamaño del rango devuelto sobre la marcha; Jeff no necesita esta flexibilidad, pero tú podrías necesitarla. Figura B muestra la nueva celda de entrada. E3 es un rango con nombre, n_filas. (Selecciona E3 e ingresa n_filas en el Cuadro de nombres.)

Potencia tu currículum con el Asistente de Currículum de Microsoft Word

Figura B

Con la celda de entrada en su lugar, estás listo para la expresión que realiza el conteo.

Cómo escribir la expresión

La expresión que vamos a utilizar combina CountIf() y Offset(). La función Offset() devuelve un rango que es evaluado por CountIf(). Ingresa la siguiente expresión en G3 (Figura C).

=COUNTIF(OFFSET(C3,COUNTA(Table1[Status]),0,-n_filas-1),"Sí")

Figura C

Cómo desactivar la tecla de Windows en tu teclado

Quizás la forma más fácil de explicar cómo funciona esta expresión es evaluarla en partes:

• referencia es C3, la celda de anclaje, la celda superior izquierda.

• filas es la función CountA(), que devuelve el número de celdas en la columna de Estado (15). Este valor aumentará con cada nuevo registro. La referencia Table1[Status] podría ser una referencia de celda tradicional, pero necesitamos las propiedades dinámicas del objeto de tabla para que esta técnica funcione.

• columnas es 0 porque el conteo se realiza en la columna de Estado; no necesitamos expandir el rango devuelto por columnas.

• altura es -n_filas-1, la celda de entrada menos 1, que devuelve 6.

• ancho no es necesario porque no estamos expandiendo las columnas.

  Cómo descargar mapas sin conexión en Windows 10: Guía paso a paso

Ahora pongámoslo todo junto para ver cómo funciona:

=COUNTIF(OFFSET(C3,COUNTA(Table1[Status]),0,-n_filas-1),"Sí")
=COUNTIF(OFFSET(C3,15,0,6),"Sí")
=COUNTIF(C11:C17, "Sí")
=3

El número total de celdas es 15, y el rango devuelto comienza nueve filas hacia abajo desde allí en C11: 15-(7-1) = 9. El rango devuelto es C11:C17. Es por eso que el conjunto de datos debe ser un objeto de tabla: ajusta automáticamente la referencia de la última celda a medida que se agregan nuevas filas. Si estuvieras utilizando un rango de datos ordinario, esta solución no funcionaría.

Aquí tienes otra celda de entrada para probar

Jeff no necesita más flexibilidad, pero tú podrías necesitarla, así que agreguemos una celda de entrada más: los valores condicionales "Sí" y "No". Para esto, utilizaremos un control de validación.

1. Selecciona F3. Haz clic en la pestaña Datos y luego haz clic en Validación de datos en el grupo Herramientas de datos. Si es necesario, elige Validación de datos en el menú desplegable.

2. En el cuadro de diálogo resultante, elige Lista en el control Permitir e ingresa "Sí,No" en la lista de Origen (Figura D).

   Cómo configurar un servicio de reenvío de correo electrónico para simplificar tus cuentas

3. Haz clic en Aceptar.

Figura D

A continuación, modifica la expresión para hacer referencia a la nueva celda de entrada:

=COUNTIF(OFFSET(C3,COUNTA(Table1[Status]),0,-n_filas-1),F3)

Reemplaza "Sí" con F3, ese es el único cambio. Ahora puedes decidir si la función cuenta los valores "Sí" o "No" y determinar cuántas filas se contarán (Figura E).

Cómo crear una lista desplegable en una celda de Google Sheets

Figura E

Notarás que no utilicé un rango con nombre para F3 como lo hice para E3. Ambas opciones son válidas: puedes usar la referencia real o utilizar un rango con nombre para ambas celdas de entrada. Hay buenas razones para utilizar rangos con nombres, pero en este caso no importa. Podrías encontrarte con una situación en la que un rango con nombre ofrezca más flexibilidad que una referencia de celda.

Si tienes otra solución para Jeff, por favor comparte tus ideas en la sección de comentarios a continuación.

Envíame tu pregunta sobre Office

Respondo las preguntas de los lectores cuando puedo, pero no hay garantía. No envíes archivos a menos que se te solicite; las solicitudes iniciales de ayuda que llegan con archivos adjuntos se eliminarán sin leer. Puedes enviarme capturas de pantalla de tus datos para ayudar a aclarar tu pregunta. Al contactarme, sé lo más específico posible. Por ejemplo, "Por favor, soluciona mi libro de trabajo y corrige lo que está mal" probablemente no recibirá una respuesta, pero "¿Puedes decirme por qué esta fórmula no está devolviendo los resultados esperados?" podría hacerlo. Por favor, menciona la aplicación y la versión que estás utilizando. No recibo ningún reembolso de Newsmatic por mi tiempo o experiencia al ayudar a los lectores, y no solicito una tarifa de los lectores a los que ayudo. Puedes contactarme en [email protected].

¡Acelera tus PCs de oficina con Microsoft Endpoint Manager y despídete de System Center!

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 Domina Excel: Cómo evaluar las últimas filas en un conjunto de datos en constante cambio , tenemos lo ultimo en tecnología 2023.