Tengo una matriz de 150 columnas x 360 filas con números aleatorios (por ejemplo, A2 a ET361) en Excel.
¿Cómo calculo para cada columna (es decir, desde la celda B1 hasta ET1) cuántas filas son mayores que cero para las columnas anteriores?
Criterios:
B1 necesita calcular el número de celdas (A2 a A361) que son >0.
C1 necesita calcular el número de filas (A2:B2, A3:B3, ..., a A361:B361) donde la suma de cada fila es >0.
D1 necesita calcular el número de filas (A2:C3, ..., a A361:C361) donde la suma de cada fila es >0.
Intenté usar la fórmula CONTAR.SI, pero solo devuelve el número de celdas, no el número de filas.
¿Creo que necesito una fórmula FILAS() e IF() anidadas? Tampoco quiero crear otra matriz de 150 x 360 para solucionar este problema porque quiero ahorrar espacio en mi archivo de Excel.
Tampoco quiero usar macros ni VBA porque complican mi hoja de cálculo.
Tengo una complejidad adicional a toda la ecuación, por la cual la función subtotal no funciona.
Necesito que cada celda dentro de la matriz calcule el número de filas encima de ella para las cuales la suma de columnas para cada fila es mayor que cero. La solución de Barry no funcionará en este caso (la he probado) ya que la fórmula 'Subtotal' no funciona para celdas que tienen una fórmula 'subtotal'.
¿Tenemos otras alternativas?
Respuesta1
Si bien no he podido pensar en una solución de fórmula única (¡tal vez alguien más lo haga!), se me ocurrió algo que ocupa mucho menos espacio en la hoja de cálculo que otra matriz de 150 x 360.
La idea básica es calcular los totales acumulados en cada fila para una columna de datos y luego usarlos en una tabla de datos ("análisis de hipótesis") para generar los recuentos de todas las columnas.
El punto de partida es la columna de cálculos para las filas de una sola columna de datos.
Como se muestra en la captura de pantalla siguiente, configuré una hoja de trabajo con 10 columnas de datos.
Columna auxiliar
A la derecha de los datos, configuré la columna auxiliar L.
La celda L1 contiene COUNTIFlas filas de esa columna que tienen una suma mayor que cero.
Para las sumas de filas, en lugar de una simple suma de las columnas de cada fila (nuevamente, solo para la columna A), uso una suma del rango devuelto por la OFFSETfunción. Esta función tiene la forma
OFFSET(reference cell, number of rows to offset, number of columns to offset,
height of range to return, width of range to return)
La celda L3 tiene la primera de las SUM(OFFSET(...))expresiones. Calcula la suma de filas para el rango que está 0 filas hacia abajo desde la celda A2 y 0 columnas a la derecha, con una altura de 1 fila y un ancho igual al valor en la celda L2. En este caso, L2 tiene el valor de 1.
Esta fórmula se copia en 360 filas, calculando en cada caso la suma de un rango de 1 fila de alto y con un ancho determinado por el valor de la celda L2.
Por ejemplo, si el valor en L2 se cambiara a 2, entonces las fórmulas en la columna calcularían las sumas por filas de los valores en las columnas A y B para cada una de las 360 filas. Y la celda L1 mostraría el número de filas en el rango A2:B361 con una suma mayor que 0.
Tabla de datos
La funcionalidad de tabla de datos de Excel permite determinar rápidamente el impacto en un cálculo de variar el valor de una (o dos) de las entradas de ese cálculo. Se configura mediante el What-If Analysisbotón en la Data Toolssección de la Datapestaña de la cinta.
La imagen adjunta muestra la configuración de la tabla de datos.
La tabla de datos se creará en el rango R1:S10. En la parte superior de la tabla, en la celda S1, se encuentra la celda de resultados para la cual se variarán las entradas. En este caso, la celda de resultado contiene la fórmula =L1, que es solo una referencia a la COUNTIFfórmula en la parte superior de la columna auxiliar L.
Ingresé previamente los valores "qué pasaría si" en las celdas R2:R10. Los valores mostrados (1, 2, ..., 9) representan los anchos de los rangos que devolverá el OFFSET. Y la "celda de entrada de la columna" es la celda L1, la celda que determina el ancho de las filas que se suman en la columna auxiliar.
En pocas palabras, ingresamos los anchos del 1 al 9 (equivalente a las columnas "A", "A:B", "A:C", etc.) y la tabla de datos calcula el número de filas que tienen sumas mayores que 0. para cada uno de esos tramos de columnas.
La última imagen muestra los resultados finales. La tabla de datos ha calculado los recuentos de filas para cada columna de los datos de entrada, es decir, los recuentos de las sumas por filas (de las columnas anteriores) que son mayores que 0. Esos recuentos se devolvieron en las celdas S2:S10 de los datos. mesa. Transferí los recuentos a la primera fila de los datos originales usando la TRANSPOSEfunción.
La hoja de trabajo de ejemplo con todos los cálculos está disponible.aquí.
Respuesta2
Si entiendo correctamente lo que estás preguntando, quieres que la fila superior muestre, para cada columna, el número total decélulas individualescon un valor > 0 en todas las columnas anteriores. ¿Bien?
Si es así, esto es bastante simple usando CountIfy usando el $signo para bloquear la referencia.
En la celda B1, ingrese =CountIf($A2:A361,">0"). Haga clic y arrastre hacia la derecha. El $letrero bloquea el Apara que siempre cuente todo lo que hay entre la columna A y la columna actual. La fórmula se verá así al arrastrarla:
- C1
=Countif($A2:B361,">0") - D1:
=Countif($A2:C361,">0") - E1:
=Countif($A2:D361,">0") - etc...
CountIfPuede contar en un rango completo, no tiene que seleccionar simplemente una celda o fórmula a la vez. Entonces, al usarlo de esta manera, puedes contar fácilmente cada celda a la izquierda de la columna actual.
Respuesta3
OFFSETLa función le permite separar filas individuales dentro de un rango... y luego puede sumar cada fila SUBTOTALy contar las filas >0 con SUMPRODUCT, por lo que esta fórmula en B1 copiada debería hacer el trabajo sin celdas auxiliares.
=SUMPRODUCT((SUBTOTAL(9,OFFSET($A2:A2,ROW(A2:A361)-ROW(A2),0))>0)+0)
Que utiliza una técnica similar a la descrita.aquí[no hay filtrado aquí, pero aún es necesario utilizar SUBTOTAL para sumar cada rango generado por OFFSET]
Eso te dará los mismos resultados que la solución de Chuff.