excel - Concatene las celdas de la fila superior si la columna de abajo tiene 1

if-statement concatenation

Question

Estoy mirando una gran base de datos de 1s y 0s con columnas con nombre, como esta:

red    blue   green  orange purple
────── ────── ────── ────── ──────
0      0      1      0      1 
0      1      0      0      0 

Quiero concatenar todos los encabezados (por fila) donde la fila tiene un "1" debajo de ese encabezado. Así que, idealmente, el primero sería igual a "verde, púrpura" y el segundo simplemente leería "azul". Tengo una gran cantidad de datos, así que cualquier cosa con anidar cien funciones "IF" no tiene sentido.

He intentado

= SI (B1: B5 = 1, CONCATENAR (A1: A5), "")

y un par de cosas cercanas a eso, pero no encuentro una manera obvia de obtenerlo. Tampoco tengo tiempo o conocimiento suficiente para lidiar con VBA. Agradezco toda la ayuda, gracias!

Answer 1

La concatenación de cadenas en más de unas pocas celdas es mejor dejarla en una función definida por el usuario de VBA (también conocida como UDF ) incluso sin establecer criterios. Su situación de "anidamiento de cien funciones" IF " ciertamente lo pondría en esta categoría.

Toque Alt + F11 y cuando se abra el VBE, use inmediatamente los menús desplegables para Insertar ► Módulo ( Alt + I , M ). Pegue lo siguiente en el nuevo panel titulado algo como Libro1 - Módulo1 (Código) .

Public Function conditional_concat(rSTRs As Range, rCRITs As Range, Optional sDELIM As String = ", ")
    Dim c As Long, sTMP As String
    For c = 1 To Application.Min(rSTRs.Cells.Count, rCRITs.Cells.Count)
        If CBool(rCRITs(c).Value2) Then _
            sTMP = sTMP & rSTRs(c).Value & sDELIM
    Next c
    conditional_concat = Left(sTMP, Application.Max(Len(sTMP) - Len(sDELIM), 0))
End Function

Toque Alt + Q para volver a su hoja de trabajo. Use este UDF como cualquier función nativa de hoja de cálculo de Excel. La sintaxis es

conditional_concat(<range of strings>, <range of conditions>, [optional] <delimiter as string>)

La fórmula en G2 es,

=conditional_concat(A$1:E$1, A2:E2)

Rellene según sea necesario.

Answer 2

La forma en que lo haría sería agregar cinco columnas adicionales e ingresar explícitamente ''rojo'', ''azul'', ''verde'', ''naranja'', ''púrpura'' donde la columna correspondiente es un 1 para que pueda hacer:

Columna roja ''r'': =IF(col_red=1,"red,","")

Columna azul ''b'': =IF(col_blue=1,"blue,","")

Columna verde ''g'': =IF(col_green=1,"green,","")

columna naranja ''o'': =IF(col_orange=1,"orange,","")

Columna púrpura ''p'': =IF(col_purple=1,"purple,","")

y en otra columna concatene estas columnas (cambie las referencias a las columnas respectivas, etc.) =LEFT(F2 & G2 & H2 & I2 & J2,LEN(F2 & G2 & H2 & I2 & J2)-1) (Encontré la concatenación función con error debido a las filas erróneas

Da este resultado:

He experimentado un poco con funciones de matriz sin éxito, lo que puede ser una forma de resolver esto.