Excel公式:对于列中的值的每个实例,获取同一行中另一列的值 [英] Excel formula: For each instance of value in column, get value of another column in same row
问题描述
ID密钥值
1 10 20
2 5 30
3 10 20
4 10 20
如果键== 10和Value == 20,获取ID。
所以,我需要这样产生以下列表:1,3,4
本质上,我正在查看一个值是否在给定的范围内,另一个值在另一个范围内,给出了另一个范围内相应的值(同一行)。 / p>
我不能假设ID列永远是最左边的列。
您可以使用附加的用户定义函数。从您的工作表中调用它,如下所示:
= concatPlusIfs(A1:A4,,,1,10,2,20)
其中
- A1:A4是ID列表
- , 是分隔符
- 1是您的ID列和您的键列之间的偏移量(左侧为1列为-1)
- 10为您的密钥
- 2的标准是您的ID列和值列之间的偏移量
-
20是您的值
公共函数concatPlusIfs(rng As Range,sep As String,lgCritOffset1 As Long,varCrit1 As Variant,lgCritOffset2 As Long,varCrit2 As Variant ,可选的noDup As Boolean = False,可选skipEmpty As Boolean = False)As String
Dim cl As Range,strTemp As String
如果noDup然后'删除重复项,使用集合以避免他们
Dim newCol作为新集合
错误恢复下一个
对于每个cl在rng.Cells
如果skipEmpty =假O r Len(Trim(cl.Text))> 0然后
如果cl.Offset(,lgCritOffset1)= varCrit1和cl.Offset(,lgCritOffset2)= varCrit2 Then newCol.Add cl.Text,cl.Text
End If
Next
对于i = 0到newCol.Count
strTemp = strTemp& newCol(i)&
$ b对于每个cl在rng.Cells
如果skipEmpty = False或Len(Trim(cl.Text))> 0然后
如果cl.Offset(,lgCritOffset1)= varCrit1和cl.Offset(,lgCritOffset2)= varCrit2 Then strTemp = strTemp& cl.Text&如果
$ b结束If
$ b concatPlusIfs = Left(strTemp,Len(strTemp) - Len(sep))
结束功能
I am looking to solve the following problem in Excel:
ID Key Value
1 10 20
2 5 30
3 10 20
4 10 20
If key == 10 and Value == 20, get the ID.
So, I need this to produce the following list: "1,3,4"
Essentially, I'm looking to see if one value is in a given range, and another value is in another range, give me the corresponding value (same row) in another range.
I cannot assume that the ID column will always be the left most column.
You can use the attached User Defined Function for that purpose. Call it from your worksheet as follows:
=concatPlusIfs(A1:A4,",",1,10,2,20)
where
- A1:A4 is the ID list
- "," is the separator
- 1 is the offset between your id column and your key column (-1 for 1 column to the left)
- 10 is the criteria for your Key
- 2 is the offset between your id column and your Value column
20 is the criteria for your Value
Public Function concatPlusIfs(rng As Range, sep As String, lgCritOffset1 As Long, varCrit1 As Variant, lgCritOffset2 As Long, varCrit2 As Variant, Optional noDup As Boolean = False, Optional skipEmpty As Boolean = False) As String Dim cl As Range, strTemp As String If noDup Then 'remove duplicates, use collection to avoid them Dim newCol As New Collection On Error Resume Next For Each cl In rng.Cells If skipEmpty = False Or Len(Trim(cl.Text)) > 0 Then If cl.Offset(, lgCritOffset1) = varCrit1 And cl.Offset(, lgCritOffset2) = varCrit2 Then newCol.Add cl.Text, cl.Text End If Next For i = 0 To newCol.Count strTemp = strTemp & newCol(i) & sep Next Else For Each cl In rng.Cells If skipEmpty = False Or Len(Trim(cl.Text)) > 0 Then If cl.Offset(, lgCritOffset1) = varCrit1 And cl.Offset(, lgCritOffset2) = varCrit2 Then strTemp = strTemp & cl.Text & sep End If Next End If concatPlusIfs = Left(strTemp, Len(strTemp) - Len(sep)) End Function
这篇关于Excel公式:对于列中的值的每个实例,获取同一行中另一列的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!