☰

Combinando varias columnas con una condición

tag-icon • tag-icon microsoft-excel
Combinando varias columnas con una condición

Tengo una variedad de columnas (A1, B1, C1, D1). Estas columnas tienen valores del 1 al 20. Tengo una fórmula en F1 como esta:

Código:

=if($A1>7,"U",if($B1>7,"X",If($C1>7,"Y",if($D1>7,"Z",""))))

Esta fórmula pone el valor de U,X,Y y Z si alguna de estas celdas (A1,B1,C1 o D1) es mayor que 7.

pero no puede manejarse si hay dos celdas mayores que >7.

¿Qué debo hacer? Si hay dos columnas mayores que 7, el resultado final será un par de U,X,Y o Z (separados por comas).

Gracias.

Respuesta1

Esto se puede hacer con una fórmula, pero es bastante larga y VBA probablemente sea una mejor solución. Además, esto no solucionará el posible caso en el que tengatresvalores > 7. Pero de todos modos te mostraré el método de la fórmula.

Comencemos con el caso más simple donde solo hay una celda > 7:

ingrese la descripción de la imagen aquí

La fórmula en F1 es:

=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","")))))

La parte CONTAR.SI() cuenta el número de celdas en el rango A1:D1 que son mayores que 7. Si ese total es mayor que 1, IF() devuelve "Doble", que es solo un marcador de posición para la fórmula más grande que viene. más tarde. Si el total no es mayor que 1, la fórmula usa su declaración IF() anidada para mostrar el código de la celda que es > 7.

Para el caso en el que dos celdas sean > 7, un IF() anidado no funcionará. Necesitamos saber qué columnas son > 7. Si tenemos estos datos,

ingrese la descripción de la imagen aquí

Primero, construimos una matriz que enumera las columnas > 7. Esta expresión (A1:D1>7)*COLUMN(A1:D1)hace eso. La primera parte (A1:D1>7)pregunta qué celdas son> 7 y se evalúa como la matriz {VERDADERO, FALSO, VERDADERO, FALSO}. La segunda parte son los números de columna de A1:D1 o {1,2,3,4}. Multiplicar estas dos matrices hace que los valores lógicos VERDADERO y FALSO se conviertan en 1 y 0, por lo que el resultado es la matriz {1,0,3,0}, que son los números de columna que tienen un valor> 7.

Ahora podemos usar estos números para buscar los códigos de columna usando INDEX():

INDEX({"U","X","Y","Z"},LARGE((A1:D1>7)*COLUMN(A1:D1),2))

Aquí LARGE() devuelve el segundo valor más grande en la matriz de números de columna> 7 (que es 1), e INDEX() lo usa para devolver el primer elemento en la matriz de letras: U.

De manera similar, esta expresión obtiene los códigos correspondientes a la segunda y primera columna más grandes separados por una coma:

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)

Ahora sustituimos todo eso en la primera ecuación anterior, reemplazando "Doble". Esta fórmula en F1 es una fórmula matricial y debe ingresarse conCtrlShiftEnter

=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","")))))

Les dije que esta fórmula iba a ser "bastante larga". Espero que esto ayude.

Respuesta2

Aquí algunas otras opciones que encontré alternativas a las fórmulas anteriores:

=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)

Si tiene fórmulas que devuelven "":

=MID(IF(N($A1)>7,", U","")&IF(N($B1)>7,", X","")&IF(N($C1)>7,", Y","")&IF(N($D1)>7,", Z",""),3,99)

Tuve que publicar una segunda respuesta porque estas fórmulas no se ven bien en el comentario.

información relacionada