excel-formula - 使用 Excel 索引和匹配函数返回多个结果
问题描述
我正在测试细菌是否存在五个基因,并想报告基因型(存在的基因列表)。我在每列中列出了基因。如果基因存在于样本中,则为 1,如果不存在,则输入为 0。
基于这个线程,我使用了以下公式:
=INDEX($B$1:$H$1,MATCH(1,B2:H2,0))
它只返回第一个“1”的结果。因此,如果存在多个基因,它只会列出第一个。
如何更改公式以包含多个结果?
解决方案
您将需要和数组版本TEXTJOIN()
(Office 365 或更高版本):
=TEXTJOIN(", ",TRUE,IF($B2:$H2=1,$B$2:$H$2))
这是一个数组公式,退出编辑模式时必须使用 Ctrl-Shift-Enter 而不是 Enter 来确认。
或连接多个 IF 语句(所有版本):
=MID(IF(B2=1,", "&$B$1,"")&IF(C2=1,", "&$C$1,"")&IF(D2=1,", "&$D$1,"")&IF(E2=1,", "&$E$1,"")&IF(F2=1,", "&$F$1,"")&IF(G2=1,", "&$G$1,"")&IF(H2=1,", "&$H$1,""),3,999)
如果没有 Office 365,则可以使用此 UDF 模仿 TEXTJOIN 并使用上述公式:
Function TEXTJOIN(delim As String, skipblank As Boolean, arr)
Dim d As Long
Dim c As Long
Dim arr2()
Dim t As Long, y As Long
t = -1
y = -1
If TypeName(arr) = "Range" Then
arr2 = arr.Value
Else
arr2 = arr
End If
On Error Resume Next
t = UBound(arr2, 2)
y = UBound(arr2, 1)
On Error GoTo 0
If t >= 0 And y >= 0 Then
For c = LBound(arr2, 1) To UBound(arr2, 1)
For d = LBound(arr2, 1) To UBound(arr2, 2)
If arr2(c, d) <> "" Or Not skipblank Then
TEXTJOIN = TEXTJOIN & arr2(c, d) & delim
End If
Next d
Next c
Else
For c = LBound(arr2) To UBound(arr2)
If arr2(c) <> "" Or Not skipblank Then
TEXTJOIN = TEXTJOIN & arr2(c) & delim
End If
Next c
End If
TEXTJOIN = Left(TEXTJOIN, Len(TEXTJOIN) - Len(delim))
End Function
推荐阅读
- polymer-3.x - 聚合物 3 - 缺少装饰器
- android - Android Logcat 奇怪的行为
- ruby-on-rails - 在活动管理员中禁用特定模型的缓存
- node.js - 通过基于嵌套和子嵌套文档的过滤器选择一个字段,例如使用 mongo 的 Post->Comment->Likes
- bash - 组合多个 bash 脚本
- php - 根据列值获取正确的行
- .net-core - EF Core 3.0 附加文件映射和更新
- javascript - 如何在一周内获得第二天
- r - `format` 对 UTF-8 字符串有什么作用?
- php - Yii2 无法将本地时间转换为 GMT 时区