Imagen: muchomor, Getty Images/iStockphoto

Jeff de TechRepublic tiene un problema de Microsoft Excel interesante y desafiante: quiere aplicar un conteo condicional a las últimas siete filas de un conjunto de datos actualizado diariamente, lo que significa que la solución debe ser dinámica. Actualmente, puede aplicar el conteo condicional, pero tiene que actualizar la fórmula todos los días, lo cual es engorroso. Le mostraré la solución que le sugerí a Jeff: use una función OFFSET() para acomodar el rango de datos cambiante.

Uso Microsoft Excel en el escritorio de Office 365 en un sistema Windows 10 de 64 bits, pero esta solución funcionará en versiones anteriores y en el navegador. Puede trabajar con sus propios datos o descargar el archivo de demostración .xlsx (el formato .xls no es compatible con esta solución).

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

Índice
  • ¿Por qué necesita Compensación ()?
  • ¿Qué hace Offset()?
  • Cómo usar la celda de entrada
  • Cómo escribir la expresión
  • Aquí hay otra celda de entrada para probar
  • Envíame tu pregunta sobre Office
  • ¿Por qué necesita Compensación ()?

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

    Figura A

    Mantenemos el conjunto de datos simple a propósito.

    Jeff usa una función de conteo para devolver el número de valores Sí en las últimas siete filas. Usando referencias de celda, necesita 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 ello, 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, basado en una celda ancla. Esta función utiliza la siguiente sintaxis:

    DESPLAZAMIENTO(referencia, filas, columnas, [height], [width])

    En pocas palabras, construyes un rango:

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

    • Líneas es el número de filas hacia arriba o hacia abajo a las que desea hacer referencia, dependiendo de referencia.

    • collares es el número de columnas a la izquierda o a la derecha a las que desea hacer referencia, dependiendo de referencia.

    • la talla determina cuántas filas desea que sea la referencia devuelta. Este es un argumento opcional y debe ser un número positivo.

    • largo determina cuántas columnas desea que sea la referencia devuelta. Este es un argumento opcional y debe ser un número positivo.

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

    Cómo usar la celda de entrada

    Jeff quiere que el rango devuelto evalúe las últimas siete filas del conjunto de datos. Podríamos ingresar 7 en la expresión, pero en su lugar usemos una celda de entrada para que Jeff pueda cambiar el tamaño del rango devuelto sobre la marcha; Jeff no necesita esa flexibilidad, pero tú podrías. Figura B muestra la nueva celda de entrada. E3 es un rango con nombre, n_rows. (Seleccione E3 e ingrese n_filas en el cuadro Nombre).

    Figura B

    El valor en E3 determinará el número de filas en el rango devuelto.

    Con la celda de entrada en su lugar, está listo para la expresión que hace 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 evaluado por CountIf(). Introduzca la siguiente expresión en G3 (Figura C).

    =CONTAR.SI(DESPLAZAR(C3,CONTARA(Tabla1[Status]),0,-n_líneas-1) ,"Sí")

    Figura C

    Esta expresión cuenta los valores Sí en el rango devuelto.

    Quizás la forma más sencilla de explicar cómo funciona es evaluarlo por partes:

    • referencia es C3 la celda ancla, la celda superior izquierda.

    • Líneas es el CuentaA(), que devuelve el número de celdas en la columna Estado (15). Este valor aumentará con cada nueva grabación. La mesa[Status] La referencia puede ser una referencia de celda tradicional, pero necesitamos las propiedades dinámicas del objeto Table para que esta técnica funcione.

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

    • la talla es -n_rows-1, la celda de entrada menos 1, que devuelve 6.

    • largo no es necesario porque no estamos extendiendo las columnas.

    Ahora vamos a ponerlo todo junto para ver cómo funciona.

    =CONTAR.SI(DESPLAZAR(C3,CONTARA(Tabla1[Status]),0,-n_líneas-1) ,"Sí")
    =CONTAR.SI(DESPLAZAMIENTO(C3,15,0,6), "Sí")
    =CONTAR.SI(C11:C17, "Sí")
    =3

    El número total de celdas es 15 y el rango devuelto comienza con nueve filas hacia abajo de allí a C11: 15-(7-1) = 9. El rango devuelto es C11: C17. Esta es la razón por la que el conjunto de datos debe ser un objeto Tabla: ajusta automáticamente la referencia de la última celda cuando agrega nuevas filas. Si estuviera usando un rango de datos ordinario, esta solución no funcionaría.

    Aquí hay otra celda de entrada para probar

    Jeff no necesita más flexibilidad, pero usted podría, así que agreguemos otra celda de entrada: los valores condicionales Sí y No. Para ello, utilizaremos una comprobación de validación.

    1. Seleccione F3. Haga clic en la pestaña Datos y luego haga clic en Validación de datos en el grupo Herramientas de datos. Si es necesario, elija Validación de datos de la lista desplegable.

    2. En el cuadro de diálogo resultante, elija Lista en el control Permitir e ingrese si no en la lista Fuente (Figura D).

    3. Haga clic en Aceptar.

    Figura D

    Agregue un control de validación que ofrezca valores condicionales de Sí y No. Al limitar las opciones, evita errores.

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

    =CONTAR.SI(DESPLAZAR(C3,CONTARA(Tabla1[Status]),0,-n_líneas-1),F3)

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

    Figura E

    Determine el rango de conteo y la condición sobre la marcha.

    Notarás que no usé un rango con nombre para F3 como lo hice para E3. Funciona en ambos sentidos: puede usar la referencia real o usar un rango con nombre para las dos celdas de entrada. Hay buenas razones para usar rangos con nombre, pero en este caso no importa. Es posible que se encuentre en una situación en la que un rango con nombre proporcione más flexibilidad que una referencia de celda.

    Si tiene otra solución para Jeff, comparta sus pensamientos 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ías. No envíe archivos a menos que se le solicite; las solicitudes de ayuda iniciales que lleguen con archivos adjuntos se eliminarán sin ser leídas. Puede enviar capturas de pantalla de sus datos para ayudar a aclarar su pregunta. Cuando se comunique conmigo, sea lo más específico posible. Por ejemplo, "Por favor, resuelva los problemas de mi libro de trabajo y corrija lo que está mal" probablemente no obtendrá una respuesta, pero "¿Puede decirme por qué esta fórmula no arroja los resultados esperados?" fuerza. Mencione la aplicación y la versión que está utilizando. TechRepublic no me reembolsa por mi tiempo o experiencia cuando ayudo a los lectores, ni cobro una tarifa por los lectores a los que ayudo. Puede ponerse en contacto conmigo en [email protected]