如果下面的列有1,则连接顶行单元格 [英] Concatenate top row cells if column below has 1
问题描述
我正在查看一个具有命名列的1和0的大型数据库,如下所示:
红色蓝色绿色橙色紫色
──────────────────────────────────────────────────────────────────────────────────────────── 0 0 0
我想连接所有的标题(按行),其中行有一个在该标题之下。所以理想情况下,第一个等于绿色,紫色,第二个只会读取蓝色。我有大量数据,所以任何嵌套一百个IF功能的任何东西都没有意义。
我尝试过
= IF(B1:B5 = CONCATENATE(A1:A5),)
和几件事情接近,但我没有找到一个明显的方式为拿到它,为实现它。我也没有时间或足够的知识来处理VBA。感谢所有的帮助,谢谢!
超过几个单元格的字符串连接最好留给VBA用户即使没有设置条件,定义的功能(也就是UDF )。 kbd> + F11 ,当VBE打开时,我可以使用下拉菜单插入►模块( Alt + I ,中号)。将以下内容粘贴到新窗格中,名称类似于 Book1 - Module1(Code)。
公共功能condition_concat(rSTRs As Range,rCRITs As Range,可选sDELIM As String =,)
Dim c As Long,sTMP As String
对于c = 1 To Application.Min(rSTRs.Cells.Count, rCRITs.Cells.Count)
如果CBool(rCRITs(c).Value2)Then _
sTMP = sTMP& rSTR(c).Value& sDELIM
下一步c
conditional_concat = Left(sTMP,Application.Max(Len(sTMP) - Len(sDELIM),0))
结束函数
点击 Alt + Q 返回到您的工作表。像任何本地的Excel工作表函数一样使用这个UDF。语法是
conditional_concat(< strings的范围><条件范围>,[可选]<分隔符作为字符串>)
G2中的公式是,
= conditional_concat(A $ 1:E $ 1,A2:E2)
填充
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
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.
I've tried
=IF(B1:B5=1, CONCATENATE(A1:A5), "")
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!
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.
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
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>)
The formula in G2 is,
=conditional_concat(A$1:E$1, A2:E2)
Fill down as necessary.
这篇关于如果下面的列有1,则连接顶行单元格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!