Precisa do valor MAX de (ex 1-100 in F: F) MAS posso precisar retirar algumas células da lista

Question

Precisa do valor MAX de (ex 1-100 em F:F ). MAS posso precisar retirar algumas células da lista.

Por exemplo, tenho F:F . Cada célula conterá um valor de 1-100, mas eu posso não precisar de certas células, mas não as mesmas sempre.

Existe uma fórmula para procurar uma lista de (não funcional), talvez por exemplo:

=MAX(F:F)-(F4,F8,F163,F354,F574)

Eu sei que isso não funciona, mas vou adicionar e remover células dessa lista de tempos em tempos, já que a lista é muito extensa, por exemplo:

=MAX(F1:F5,F7:F16,F18:F41,F43:F66,F68:F91,F93:F197,F199:F219,F221:F291,F293:F312,F314:F900)

Eu sei que isso funciona, mas omg. Seria uma coisa horrível para se mudar toda vez. Existe uma maneira mais fácil?

    

Answer 1

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:

1. 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.

2. 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:

1. 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.

2. 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.

3. O "|" é adicionado para garantir que não obtemos falsos negativos. Sem ela, uma referência na coluna H de F2 excluiria todas as referências de célula que começam com F2 , incluindo F20 , F21 ...

4. 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.