Combinando várias colunas com uma condição

Question

Eu tenho um intervalo de colunas (A1, B1, C1, D1). Essas colunas têm valores de 1 a 20. Eu tenho uma fórmula na 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 qualquer uma dessas células (A1, B1, C1 ou D1) for maior que 7.

mas não é possível lidar com duas células maiores que > 7.

O que devo fazer, portanto, se houver duas colunas maiores que 7, o resultado final será um par de U, X, Y ou Z (separados por vírgulas).

Obrigado.

    

Answer 1

Isso pode ser feito com uma fórmula, mas é bastante longo, e o VBA é provavelmente uma solução melhor. Além disso, isso não tratará do possível caso em que você tem três valores > 7. Mas eu vou te mostrar o método da fórmula de qualquer maneira.

Vamos começar com o caso mais simples em que há apenas uma célula > 7:

AfórmulaemF1é:

=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, o IF () retorna "Double", que é apenas um espaço reservado para o maior fórmula que vem depois. Se o total não for maior que 1, a fórmula usa sua instrução IF () aninhada para exibir o código da célula que é > 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,criamosumamatrizquelistaascolunas>7.Estaexpressão(A1:D1>7)*COLUMN(A1:D1)fazisso.Aprimeiraparte(A1:D1>7)perguntaquaiscélulassão>7eavaliaparaamatriz{VERDADEIRO,FALSO,VERDADEIRO,FALSO}.AsegundaparteéosnúmerosdacolunadeA1:D1ou{1,2,3,4}.MultiplicaressasduasmatrizesfazcomqueosvaloreslógicosVERDADEIROeFALSOsejamconvertidospara1e0,portanto,oresultadoéamatriz{1,0,3,0},quesãoosnúmerosdascolunasquetêmumvalor>7.

AgorapodemosusaressesnúmerosparapesquisaroscódigosdacolunausandoINDEX():

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, essa expressão obtém os códigos correspondentes ao segundo e ao primeiro maior número de colunas separados por uma 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 "Double". Esta fórmula em F1 é uma fórmula de matriz e deve ser digitada com Ctrl Deslocar Enter

=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 lhe disse que esta fórmula seria "bastante longa". Espero que isso ajude.

    

Answer 2

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)

Eu tive que postar uma segunda resposta porque essas fórmulas não parecem boas no comentário.