Encontre a linha anterior com um valor específico na coluna A

Question

Dadaafolhadeamostraacima,quetemlinhassemânticas'header','subheader'e'subtotal',estoutentandodeterminarumafórmulaparalocalizaralinhadesubheaderanterioremrelaçãoàcélulaatual.Porexemplo,seafórmulafosseinseridaemF5,elalocalizariaalinha2e,seinseridaemF17,localizariaalinha13.

Aslinhassãoformatadascondicionalmentecomoumcabeçalho,subcabeçalhoousubtotal,pelaexistênciadosvaloresH,SouTnacoluna$A:$A,ouseja,alinha%subheadernéumalinhaemque$An="S" . Agora, gostaria de estender esse conceito para minhas fórmulas.

Uma linha de cabeçalho sempre será seguida por um subcabeçalho (por isso, não preciso se preocupar com cabeçalhos e subtítulos que estejam fora de ordem).

Eu tentei o seguinte:

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

   Isso sempre retorna linha 2 , porque MATCH retorna a correspondência primeiro , no conjunto, e não posso limitar a OFFSET height (ou seja, recursivamente, porque o subheader anterior a localização é desconhecida);

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

   Isso também retorna 2 , porque, mesmo no contexto da matriz (ou seja, com Ctrl + Alt + Enter ), MATCH ainda apenas retorna o primeiro resultado;

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

   Isso retorna 0 , porque IF não está esperando uma matriz aqui, então expande OFFSET($A5, 0, 0, -ROW($A5), 1) para um único valor 0 , que não corresponde a "S" , e LARGE trata FALSE como um número;

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

   Isso retorna #VALUE , porque a expansão de array ocorre muito cedo, o que deixa -ROW($A5) como array -{5} , que não é um parâmetro height numérico válido para OFFSET (eu queria que IF(OFFSET(...)="S",...) bit seja uma matriz, não o -ROW($A5) bit, mas o Excel não pode distinguir).

Atualmente, estou segmentando o Excel 2010. Versões anteriores não são aplicáveis (embora a compatibilidade com versões futuras seja um bônus). Eu estou tentando evitar o VBA, uma vez que é mais difícil para mim distribuir arquivos * .xlsm do que * .xlsx (além disso, eu já sei como fazer isso com o VBA).

Existe alguma outra coisa que eu possa tentar?

    

Answer 1

A maneira mais fácil de fazer isso é trapacear e usar uma fórmula mista absoluta / relativa. Esta é uma fórmula de matriz (insira com CTRL + SHIFT + ENTER) inserida na célula B4 , mas ela pode ir a qualquer lugar na linha 4. Ela retornará o número da linha da marcada S .

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

Quando copiado para baixo, a segunda parte da referência B4 and A4 aumentará. Isso garante que você obtenha a linha com a maior correspondência acima da linha atual. Você pode inserir essas fórmulas mais rapidamente usando F4 depois de digitar / selecionar o intervalo relevante. Isso fará com que os cifrões percam todas as opções.

Imagem de intervalos

Usadoparasubstituirsuasfórmulas

Depoisdeleraquestãoumpouco(ecombasenaediçãode@SteveTaylor),parecequeoseuusoparaissoéatualizarsuasfórmulas.VocêpodeusaralinharetornadaacimacomINDEXparaobterintervalosdedadosparasomar.Euvejo2fórmulasquepodemsersubstituídas:

• Cálculototalparacadalinharotuladadedados.Nessecaso,alinhadosubtotalacimapodeserreferenciadadinamicamente.
• Calculartotalparaalinhadosubestado.Nessecaso,osvaloresparasomaacimapodemserreferenciadosdinamicamente.

Paraosdadosdelinhaúnica,vocêpodeusarafórmula,começandoemF3comoumafórmuladematriz.NotequemudeiparaoSUMPRODUCT,oquetornamuitomaisfáciliramaisde2colunas.

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

Para a fórmula de linha total, você pode usar, começando em F11 , novamente fórmula de matriz:

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

Se você quiser uma fórmula para governá-los todos! então você pode combiná-los em um IF aninhado com base no valor da coluna A . Aqui está a fórmula de matriz, começando em F2 , que pode ser copiado.

=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 não diferencia entre uma linha em branco e uma linha de "dados". Atualmente, retorna 0 para a linha do espaçador, o que é bom.

Imagem de resultados de e fórmulas para dois blocos de seus dados.