如果下面的列有 1，则连接顶行单元格 [英] Concatenate top row cells if column below has 1

问题描述

我正在查看具有命名列的 1 和 0 的大型数据库，如下所示:

I'm looking at a large database of 1s and 0s with named columns, like this:

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

我想连接所有标题(按行)，其中该行在该标题下方有一个1".所以理想情况下，第一个应该等于绿色，紫色"，第二个应该是蓝色".我有大量数据，所以任何嵌套一百个IF"函数的东西都是没有意义的.

I want to concatenate all the headings (by row) where the row has a "1" below that heading. So ideally the first one would equal "green, purple" and the second would just read "blue". I have a large amount of data so anything with nesting of a hundred "IF" functions doesn't make sense.

我试过了

=IF(B1:B5=1, 连接(A1:A5), "")

=IF(B1:B5=1, CONCATENATE(A1:A5), "")

还有一些接近于此的东西，但我没有找到一种明显的方法来获得它.我也没有时间或足够的知识来处理 VBA.感谢所有帮助，谢谢！

and a couple things close to that, but I'm not finding an obvious way to get it. I also don't really have time or enough knowledge to deal with VBA. I appreciate all help, thanks!

推荐答案

多个单元格上的字符串连接最好留给 VBA 用户定义函数(又名 UDF>) 即使没有设置标准.您嵌套一百个IF"函数"的情况肯定会属于这一类.

String concatenation over more than a few cells is best left to a VBA User Defined Function (aka UDF) even without setting criteria. Your situation of "nesting of a hundred "IF" functions" would certainly put it in this category.

点击 Alt+F11，当 VBE 打开时，立即使用下拉菜单插入 ► 模块 (Alt+I,M).将以下内容粘贴到名为 Book1 - Module1 (Code) 的新窗格中.

Tap Alt+F11 and when the VBE opens, immedaitely use the pull-down menus to Insert ► Module (Alt+I,M). Paste the following into the new pane titled something like Book1 - Module1 (Code).

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

点击 Alt+Q 返回到您的工作表.像使用任何本机 Excel 工作表函数一样使用此 UDF.语法是，

Tap Alt+Q to return to your worksheet. Use this UDF like any native Excel worksheet function. The syntax is,

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

G2 中的公式是，

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

根据需要填写.

这篇关于如果下面的列有 1，则连接顶行单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！