Encontre o valor zero em uma coluna e obtenha um valor acima / abaixo na próxima coluna

Question

Eu tenho uma lista de temperaturas de um sensor que inclui valores de reset de zero quando o sensor é desligado.

A       B       C
res      0
tmp   20,5
tmp   20,3
res      0
tmp   20,4
tmp   20,1
tmp   20,2
res      0
res      0
tmp   20,6

Consigo encontrar essas linhas com a seguinte fórmula e obter o valor de B , que é zero:

=INDEX(B1:10,MATCH("res",A1:A10,0))

Mas isso não me faz bem. O que eu gostaria é que a fórmula encontre a linha com res em A e, em seguida, examine a coluna B e encontre o primeiro valor diferente de zero acima ou abaixo, se nenhuma dessas linhas puder ser encontrada, por exemplo para A1 .

Então meus dados devem ficar assim:

A       B       C
res      0    20,5
tmp   20,5    20,5
tmp   20,3    20,3
res      0    20,3
tmp   20,4    20,4
tmp   20,1    20,1
tmp   20,2    20,2
res      0    20,2
res      0    20,2
tmp   20,6    20,6

Apreciará qualquer ajuda com isso.

    

Answer 1

Se você deseja permitir qualquer número de reconfigurações no início dos dados e não usar colunas auxiliares, a fórmula é um pouco complicado:

InsiraaseguintefórmulaemC1ectrl-enter/copy-paste/fill-down/preenchimentoautomáticonorestantedacolunadatabela:

= INDEX( (B:B), IFERROR( 1/(1/SUMPRODUCT(LARGE(ROW(A$1:A1)*(A$1:A1<>"res"),1))), ROW()-1+MATCH("tmp",A1:INDEX(A:A,ROWS(A:A)),0) ) )

Explicação:

A função LARGE(ROW(A$1:A1)*(A$1:A1<>"res"),1) é usada para retornar o maior número de linha (ou seja, mais próximo) que não seja res row, da linha atual para cima. (O SUMPRODUCT() encapsulando está ali apenas para garantir que os intervalos dentro sejam avaliados como matrizes, sem exigir que a fórmula seja inserida na matriz.) Se houver não , essas linhas que não são res , SUMPRODUCT(LARGE(…)) avalia para 0 .

Para um valor diferente de zero , IFERROR(1/(1/value),expression) é avaliado como valor (ou seja, o número de linha ascendente não superior a res ). Para um valor zero , ele é avaliado pelo resultado expressão (porque 1/(1/0)) resulta em um erro #DIV/0! ).

Assim, para cada valor de reinicialização consecutivo a partir da primeira linha, ROW()-1+MATCH("tmp",A1:INDEX(A:A,ROWS(A:A)),0) é avaliado. Isso simplesmente retorna o número da linha da primeira linha, da linha atual para baixo, que é uma leitura de temperatura válida. ( A1:INDEX(A:A,ROWS(A:A)) é simplesmente o intervalo da célula na coluna A correspondente à linha atual, ou seja, A1 para a fórmula inserida em C1 , até a última célula da coluna A , INDEX(A:A,ROWS(A:A)) .)

Finalmente, acabamos com INDEX((B:B),row_number) , que extrai a leitura de temperatura apropriada.

Percorrer a fórmula em C10 deve tornar o acima mais claro:

• ROW(A$1:A10)*(A$1:A10<>"res")
  → {1;2;3;4;5;6;7;8;9;10}*({"res";"res";"tmp";"tmp";"res";"tmp";"tmp";"tmp";"res";"res"}<>"res")
  → {1;2;3;4;5;6;7;8;9;10}*{FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE}
  → {1;2;3;4;5;6;7;8;9;10}*{0;0;1;1;0;1;1;1;0;0}
  → {0;0;3;4;0;6;7;8;0;0}
• 1/(1/SUMPRODUCT(LARGE({0;0;3;4;0;6;7;8;0;0},1)))
  → 1/(1/SUMPRODUCT(8)) (pois LARGE() retorna o n-ésimo maior valor, onde n é o segundo argumento) → 1/(1/8)
  → 1/0.125
  → 8
• =INDEX((B:B),IFERROR(8,…))
  → =INDEX(B:B,8)
  → =20,2

Notas:

• A fórmula multi-linha, prettificada, realmente funciona como está.
• Os parênteses em torno de (B:B) são necessários apenas para forçar o B:B a permanecer em sua própria linha.

Se você quiser uma fórmula mais simples, então você precisa usar uma coluna auxiliar ( D ):

InsiraaseguintefórmulaemC1ectrl-enter/copy-paste/fill-down/preenchimentoautomáticonorestantedacolunadatabela:

=IF(A1="res",IF(ROW()=1,D1,INDEX(C:C,ROW()-1)),B1)

Insira a seguinte fórmula em D1 e ctrl-enter / copy-paste / fill-down / preenchimento automático no restante da coluna da tabela:

=IF(A1="res",D2,B1)

Explicação:

• coluna auxiliar:

  • Se a linha atual contiver um valor de redefinição, a fórmula na coluna D copia o valor da célula diretamente abaixo.
  • Caso contrário, ele pega o valor da mesma linha da coluna B .
  • Trabalhando da última linha de dados na coluna para cima, isso leva a D1 avaliando a primeira leitura válida na coluna B .

• coluna principal:

  • Para uma linha de redefinição, a fórmula na coluna C verifica se está na primeira linha e, nesse caso, usa o valor da célula da coluna D correspondente à mesma linha, ou seja, D1 . Se a fórmula estiver em qualquer outra linha da coluna C , ela pegará o valor da célula imediatamente acima.
  • Se a linha for uma linha de temperatura válida, a fórmula agarra a leitura de temperatura da célula na coluna B correspondente à mesma linha.

Answer 2

Você pode apenas procurar o "res" e pegar o valor acima.
Mesmo se houver vários valores zero, eles devem apontar para o acima, que não é zero, já que acabamos de alterá-lo.

Então, em C1

=B1

Então C2

=IF(A2<>"res",B2,C1)

E preencha-o.
Isso funcionará enquanto o B1 não for 0.
Você poderia adicionar uma fórmula para cuidar disso também, mas como é uma célula, acho que é mais fácil verificá-la manualmente, já que você está colocando a outra fórmula ao lado dela.