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

Question

Dada a planilha de exemplo acima, que possui linhas semânticas de 'cabeçalho', 'subcabeçalho' e 'subtotal', estou tentando determinar uma fórmula para localizar a linha do subcabeçalho anterior em relação à célula atual. Por exemplo, se a fórmula fosse inserida F5, ela localizaria a linha 2e, se inserida F17, localizaria a linha 13.

As linhas são formatadas condicionalmente como cabeçalho, subcabeçalho ou subtotal, pela existência de valores H, Sou Tem coluna $A:$A, ou seja, linha do subcabeçalho né uma linha onde $An = "S". Agora, gostaria de estender esse conceito às minhas fórmulas.

Uma linha de cabeçalho sempre será seguida por um subcabeçalho (assim não preciso me preocupar com cabeçalhos e subcabeçalhos que estão fora de ordem).

Eu tentei o seguinte:

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

   Isso sempre retorna row 2, porque MATCHretorna oprimeiromatch, no conjunto, e não posso limitar a OFFSETaltura (ou seja, recursivamente, porque a localização do subcabeçalho anterior é desconhecida);

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

   Isso também retorna 2, porque, mesmo no contexto de array (ou seja, com Ctrl+ Alt+ Enter), MATCHainda retorna apenas 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 IFnão está esperando um array aqui, então se expande OFFSET($A5, 0, 0, -ROW($A5), 1)para um único valor 0, que não corresponde "S", e LARGEtrata FALSEcomo 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 do array ocorre muito cedo, o que deixa -ROW($A5)como array , que não é um parâmetro -{5}numérico válido para (eu queria que o bit fosse um array, não o bit, mas o Excel não consegue distinguir).heightOFFSETIF(OFFSET(...)="S",...)-ROW($A5)

Atualmente estou visando o Excel 2010. Versões anteriores não são aplicáveis ​​(embora a compatibilidade futura seja um bônus). Estou tentando evitar o VBA, pois é mais difícil distribuir arquivos *.xlsm do que *.xlsx (além disso, já sei fazer isso com VBA).

Há 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, B4mas pode ir a qualquer lugar na linha 4. Ela retornará o número da linha daquela marcada S.

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

Quando copiada, a segunda parte da referência B4 and A4aumentará. Isso garante que você obtenha a linha com a maior correspondência possívelacimaa linha atual. Você pode inserir essas fórmulas mais rapidamente usando F4após digitar/selecionar o intervalo relevante. Isso fará com que os cifrões percorram todas as opções.

Imagem dos intervalos

Usado para substituir suas fórmulas

Depois de ler um pouco a pergunta (e com base na edição de @SteveTaylor), parece que sua utilidade é atualizar suas fórmulas. Você pode usar a linha retornada acima para INDEXobter intervalos de dados para somar. Vejo 2 fórmulas que podem ser substituídas:

• Cálculo total para cada linha de dados rotulada. Neste caso, a linha do subtotal acima pode ser referenciada dinamicamente.
• Cálculo total para a linha subtotal. Neste caso, os valores a serem somados acima podem ser referenciados dinamicamente.

Para dados de linha única, você pode usar a fórmula, começando F3como uma fórmula de matriz. Observe que mudei para o uso, SUMPRODUCTo que torna muito mais fácil ir para mais de 2 colunas.

=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 a fórmula de matriz:

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

Se você quer uma fórmula para governar todos eles! então você pode combiná-los em um aninhado IFcom base no valor em column A. Aqui está a fórmula de matriz, a partir da F2qual pode ser copiada.

=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 espaçadora, o que é bom.

Imagem de resultados e fórmulaspara dois blocos de seus dados.