Eu tenho um intervalo de colunas (A1,B1,C1,D1). Essas colunas possuem valores de 1 a 20. Tenho uma fórmula em F1 assim:
Código:
=if($A1>7,"U",if($B1>7,"X",If($C1>7,"Y",if($D1>7,"Z",""))))
Esta fórmula coloca o valor de U,X,Y e Z se alguma dessas células (A1,B1,C1 ou D1) for maior que 7.
mas não funciona se houver duas células maiores que> 7.
O que devo fazer, então se houver duas colunas maiores que 7, o resultado final será um par de U,X,Y ou Z (separados por vírgula).
Obrigado.
Responder1
Isso pode ser feito com uma fórmula, mas é bastante longa e o VBA é provavelmente uma solução melhor. Além disso, isso não resolverá o possível caso em que você temtrêsvalores > 7. Mas vou mostrar o método da fórmula de qualquer maneira.
Vamos começar com o caso mais simples onde existe apenas uma célula > 7:
A fórmula em F1 é:
=IF(COUNTIF($A$1:D$1,">7")>1,"Double",IF($A1>7,"U",IF($B1>7,"X",IF($C1>7,"Y",IF($D1>7,"Z","")))))
A parte COUNTIF() conta o número de células no intervalo A1:D1 que são maiores que 7. Se esse total for maior que 1, IF() retorna "Double", que é apenas um espaço reservado para a fórmula maior que vem mais tarde. Se o total não for maior que 1, a fórmula usará sua instrução IF() aninhada para exibir o código da célula > 7.
Para o caso em que duas células são > 7, um IF() aninhado não funcionará. Precisamos saber quais colunas são > 7. Se tivermos esses dados,
Primeiro, construímos um array que lista as colunas > 7. Esta expressão
(A1:D1>7)*COLUMN(A1:D1)faz isso. A primeira parte (A1:D1>7)pergunta quais células são> 7 e avalia o array {TRUE,FALSE,TRUE,FALSE}. A segunda parte são os números das colunas de A1:D1 ou {1,2,3,4}. Multiplicar essas duas matrizes faz com que os valores lógicos TRUE e FALSE sejam convertidos em 1 e 0, então o resultado é a matriz {1,0,3,0} que são os números das colunas que possuem valor > 7.
Agora podemos usar esses números para pesquisar os códigos das colunas usando INDEX():
INDEX({"U","X","Y","Z"},LARGE((A1:D1>7)*COLUMN(A1:D1),2))
Aqui LARGE() retorna o segundo maior valor na matriz de números de colunas > 7 (que é 1), e INDEX() usa isso para retornar o primeiro elemento na matriz de letras - U.
Da mesma forma, esta expressão obtém os códigos correspondentes aos números da segunda e da primeira coluna maiores separados por vírgula:
INDEX({"U","X","Y","Z"},LARGE((A1:D1>7)*COLUMN(A1:D1),2))&","&INDEX({"U","X","Y","Z"},LARGE((A1:D1>7)*COLUMN(A1:D1),1)
Agora substituímos tudo isso na primeira equação acima, substituindo “Duplo”. Esta fórmula em F1 é uma fórmula de matriz e deve ser inserida comCtrlShiftEnter
=IF(COUNTIF($A$1:D$1,">7")>1,INDEX({"U","X","Y","Z"},LARGE((A1:D1>7)*COLUMN(A1:D1),2))&","&INDEX({"U","X","Y","Z"},LARGE((A1:D1>7)*COLUMN(A1:D1),1)),IF($A1>7,"U",IF($B1>7,"X",IF($C1>7,"Y",IF($D1>7,"Z","")))))
Eu disse que essa fórmula seria "bastante longa". Eu espero que isso ajude.
Responder2
Aqui estão algumas outras opções que encontrei alternativas às fórmulas acima:
=IFERROR(LEFT(IF($A1>7,"U, ","")&IF($B1>7,"X, ","")&IF($C1>7,"Y, ","")&IF($D1>7,"Z, ",""),(COUNTIF($A1:$D1,">7")-1)*3+1),"")
{=TEXTJOIN(",",TRUE,IF(A1:D1>7,MID("UXYZ",COLUMN(A1:D1)-COLUMN(A1)+1,1),""))}
=MID(IF($A1>7,", U","")&IF($B1>7,", X","")&IF($C1>7,", Y","")&IF($D1>7,", Z",""),3,99)
Se você tiver fórmulas retornando "" :
=MID(IF(N($A1)>7,", U","")&IF(N($B1)>7,", X","")&IF(N($C1)>7,", Y","")&IF(N($D1)>7,", Z",""),3,99)
Tive que postar uma segunda resposta porque essas fórmulas não ficam bem no comentário.