Você pode usar essa fórmula de fórmula de matriz:
=MAX(IF(ISERROR(MATCH("F" & ROW($F$1:INDEX($F:$F,MATCH(1E+99,$F:$F))) & "|",$H$1:INDEX($H:$H,MATCH("ZZZ",$H:$H))&"|",0)),$F$1:INDEX($F:$F,MATCH(1E+99,$F:$F))))
Notas:
-
Sendo uma fórmula de matriz, ela deve ser confirmada com Ctrl-Shift-Enter em vez de Enter ao sair do modo Editar. Se feito corretamente, o Excel colocará
{}
em torno da fórmula. -
As células a serem ignoradas devem colocar um endereço de célula por vez em uma coluna. O meu está na coluna H.
Descrição:
-
Sendo uma fórmula de array e queremos limitar as referências ao conjunto de dados. O
$F$1:INDEX($F:$F,MATCH(1E+99,$F:$F))
fará isso. Ele criará um intervalo que inclui F1 para a última célula na coluna F que tenha um número. A coluna H Reference encontra cada célula do H1 até a última célula com uma string. -
A função If retornará uma matriz de números e
FALSE
. O Falso será onde o número da linha é igual à referência da célula. -
O
"|"
é adicionado para garantir que não obtemos falsos negativos. Sem ela, uma referência na coluna H deF2
excluiria todas as referências de célula que começam comF2
, incluindoF20
,F21
... -
O MAX () ignorará o
FALSE
s retornado na matriz e localizará o maior número restante na matriz retornada pela instrução IF.
VocêtambémpodeusaressafórmulasemelhantequenãoexigeoCtrl-Shift-Enter,maséinseridanormalmente.
=AGGREGATE(14,6,$F$1:INDEX($F:$F,MATCH(1E+99,$F:$F))/(ISERROR(MATCH("F" & ROW($F$1:INDEX($F:$F,MATCH(1E+99,$F:$F))) & "|",$H$1:INDEX($H:$H,MATCH("ZZZ",$H:$H))&"|",0))),1)
Ainda é considerada uma fórmula de matriz, por isso precisamos limitar as referências de dados.