Fórmula do Excel para obter o primeiro e último valor não vazio em uma linha e retornar o cabeçalho da coluna

Question

Digamos que eu tenha uma planilha do Excel agregando votos em alguma pesquisa ao longo de um período de tempo, conforme o exemplo abaixo.

Como encontro o valor min e max em que as pessoas votaram em um determinado dia (o cabeçalho da coluna )?

         A        B   C   D   E   F   G    H     I   
   --------------------------------------------------
1  | Answers:   | 0 | 1 | 2 | 3 | 4 | 5 | Min | Max |
   --------------------------------------------------
2  | 2014-12-01 |   | 2 | 4 | 1 |   |   |  1  |  3  |
3  | 2015-01-01 |   |   | 1 | 4 | 2 |   |  2  |  4  |
                          ^       ^        ^-----^- Min: 2, Max: 4
                          |       |        
                          |       |_ 2 people voted "4"
                          |
                          |_ 1 person voted "2" on 2015-01-01

    

Answer 1

Para encontrar o valor min que as pessoas votaram na pesquisa (o cabeçalho da primeira célula não vazia), usei a seguinte fórmula (na célula H2 ):

=INDEX(B$1:G$1, MATCH(TRUE, INDEX(B2:G2<>"", 0), 0))

Para encontrar o valor max (na célula I2 ):

=LOOKUP(2, 1/(B2:G2<>""),B$1:G$1)

Sob o capô

A função de pesquisa tem a seguinte assinatura: LOOKUP( value, lookup_range, [result_range] )

O que a segunda fórmula faz é o seguinte:

1. Divida 1 por uma matriz de valores booleanos (true / false) (B2:G2<>"") ; essa é a parte 1/(B2:G2<>"") .
2. Acima, há um fato de que o Excel converte o booleano TRUE em 1 e FALSE a 0 , portanto, o resultado dessa etapa é uma lista de:
   • 1 , em que CELL_VALUE<>"" retornou TRUE e 1/TRUE => 1 )
   • e #DIV/0! erros, em que CELL_VALUE<>"" retornou FALSE e 1/FALSE => 1/0 => #DIV/0!
3. Agora, como usei 2 (você pode usar um número a partir de 1) como o valor de pesquisa, a fórmula corresponde ao último valor numérico no intervalo (como você se lembra, o intervalo é algo como [DIV/0!, 1, 1, 1, DIV/0!, DIV/0!] , é por isso que a pesquisa corresponderá ao item quarto ). BTW: observe que, se a fórmula não corresponder a algo, você ainda receberá um erro # N / A.
4. Nesta fase, sabemos em qual coluna estamos interessados ( quarto no intervalo). Portanto, o último bit que falta é dizer ao Excel que o intervalo de resultados é a primeira linha da planilha: B$1:G$1 e é aí que o Excel procurará o item quarto .

Você pode ler mais sobre as fórmulas de pesquisa do Excel aqui: link

Espero que isso ajude!

Jan