Encuentre la fila anterior con un valor específico en la columna A

Question

Dada la hoja de muestra anterior, que tiene filas semánticas de "encabezado", "subencabezado" y "subtotal", estoy tratando de determinar una fórmula para ubicar la fila del subencabezado anterior en relación con la celda actual. Por ejemplo, si la fórmula se ingresara en F5, ubicaría la fila 2y, si se ingresara en F17, ubicaría la fila 13.

Las filas tienen el formato condicional como encabezado, subtítulo o subtotal, por la existencia de valores H, So Ten columna $A:$A, es decir, la fila del subtítulo nes una fila donde $An = "S". Ahora me gustaría extender este concepto a mis fórmulas.

Una fila de encabezado siempre irá seguida de un subtítulo (por lo que no tengo que preocuparme por encabezados y subtítulos que estén desordenados).

He probado lo siguiente:

1. =MATCH("S", OFFSET($A5, 0, 0, -ROW($A5), 1), 0)

   Esto siempre devuelve fila 2, porque MATCHdevuelve elprimerocoincide, en el conjunto, y no puedo limitar la OFFSETaltura (es decir, de forma recursiva, porque se desconoce la ubicación del subtítulo anterior);

2. {=LARGE(MATCH("S", OFFSET($A5, 0, 0, -ROW($A5), 1), 0), 1)}

   Esto también devuelve 2, porque, incluso en el contexto de una matriz (es decir, con ++ Ctrl) , solo devuelve el primer resultado;AltEnterMATCH

3. =LARGE(IF(OFFSET($A5, 0, 0, -ROW($A5), 1)="S", ROW(OFFSET($A5, 0, 0, -ROW($A5), 1))), 1)

   Esto devuelve 0, porque IFno se espera una matriz aquí, por lo que se expande OFFSET($A5, 0, 0, -ROW($A5), 1)a un único valor 0, que no coincide "S", y LARGEse trata FALSEcomo un número;

4. {=LARGE(IF(OFFSET($A5, 0, 0, -ROW($A5), 1)="S", ROW(OFFSET($A5, 0, 0, -ROW($A5), 1))), 1)}

   Esto devuelve #VALUE, porque la expansión de la matriz ocurre demasiado pronto, lo que deja -ROW($A5)como matriz , que no es un parámetro -{5}numérico válido (quería que el bit fuera una matriz, no el bit, pero Excel no puede distinguir).heightOFFSETIF(OFFSET(...)="S",...)-ROW($A5)

Actualmente mi objetivo es Excel 2010. Las versiones anteriores no son aplicables (aunque la compatibilidad con versiones posteriores es una ventaja). Estoy tratando de evitar VBA, ya que me resulta más difícil distribuir archivos *.xlsm que *.xlsx (además, ya sé cómo hacerlo con VBA).

¿Hay alguna otra cosa que pueda probar?

Answer 1

La forma más sencilla de hacerlo es hacer trampa y utilizar una fórmula mixta absoluta/relativa. Esta es una fórmula matricial (ingrese con CTRL+MAYÚS+ENTRAR) ingresada en la celda, B4pero podría ir a cualquier parte de la fila 4. Devolverá el número de fila de la marcada S.

=MAX(IF($A$1:A4="S",ROW($A$1:A4)))

Cuando se copia, la segunda parte de la referencia B4 and A4aumentará. Esto garantiza que obtendrá la fila con la coincidencia más grande que seaarribala fila actual. Puede ingresar esas fórmulas más rápidamente F4después de escribir/seleccionar el rango relevante. Esto hará que los signos de dólar recorra todas las opciones.

Imagen de rangos

Se utiliza para reemplazar sus fórmulas.

Después de leer un poco la pregunta (y según la edición de @SteveTaylor), parece que esto le sirve para actualizar sus fórmulas. Puede utilizar la fila que se devuelve desde arriba junto con INDEXpara obtener rangos de datos para sumar. Veo 2 fórmulas que se pueden reemplazar:

• Cálculo total para cada fila de datos etiquetada. En este caso, se puede hacer referencia dinámicamente a la fila del subtotal anterior.
• Cálculo total para la fila del subtotal. En este caso, los valores a sumar desde arriba se pueden hacer referencia dinámicamente.

Para los datos de una sola fila, puede usar la fórmula, comenzando F3como una fórmula matricial. Tenga en cuenta que cambié a usar, SUMPRODUCTlo que hace que sea mucho más fácil ir a más de 2 columnas.

=C3*SUMPRODUCT(INDEX(D:E,MAX(IF($A$1:A3="S",ROW($A$1:A3))),),D3:E3)

Para la fórmula de fila total, puede usar, comenzando en F11, nuevamente la fórmula matricial:

=SUM(F10:INDEX($F$1:F10, 1+MAX(IF($A$1:A11="S",ROW($A$1:A11)))))

¡Si quieres que una fórmula los gobierne a todos! luego puede combinarlos en un anidado IFsegún el valor de la columna A. Aquí está dicha fórmula matricial, a partir de F2la cual se puede copiar.

=IF(
  A2="S", 
  SUM(D2:E2), 
    IF(A2="T", 
      SUM(F1:INDEX($F$1:F1, 1+MAX(IF($A$1:A2="S",ROW($A$1:A2))))), 
      C2*SUMPRODUCT(INDEX(D:E,MAX(IF($A$1:A2="S",ROW($A$1:A2))),),D2:E2)))

Esta fórmula no diferencia entre una fila en blanco y una fila de "datos". Actualmente devuelve 0 para la fila espaciadora, lo cual está bien.

Imagen de resultados y fórmulas.para dos bloques de sus datos.