Формула Excel: для каждого экземпляра значения в столбце получить значение другого столбца в той же строке

Question

Я ищу, чтобы решить следующую проблему в Excel:

ID     Key     Value
1      10      20
2       5      30
3      10      20
4      10      20

Если ключ == 10 и значение == 20, получите идентификатор.

Итак, мне нужно, чтобы получить следующий список: "1,3,4"

По сути, я смотрю, находится ли одно значение в данном диапазоне, а другое значение в другом диапазоне, дайте мне соответствующее значение (ту же строку) в другом диапазоне.

Я не могу предположить, что столбец ID всегда будет самым левым столбцом.

Answer 1

Вы можете использовать прикрепленную пользовательскую функцию для этой цели. Назовите это с вашего рабочего листа следующим образом:

= ConcatPlusIfs(A1:A4 "",1,10,2,20)

где

• A1: A4 это список идентификаторов
• "," является разделителем
• 1 - это смещение между вашим столбцом id и вашим ключевым столбцом (-1 для 1 столбца слева)
• 10 является критерием для вашего ключа
• 2 - смещение между вашим столбцом id и вашим столбцом Value

• 20 является критерием для вашей ценности

  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
  

Answer 2

Я бы сказал, что это самая основная функция Excel, но, поскольку вы принимаете искусственное ограничение, что вы не можете решить, как будут упорядочены ваши столбцы, - тогда вам потребуется использовать что-то вроде HLOOKUP (при условии, что вы можете по крайней мере определить свой заголовки):

=IF(AND(HLOOKUP("Key",$A$1:$C$5,ROW(),FALSE)=10,HLOOKUP("VALUE",$A$1:$C$5,ROW(),FALSE)=20),HLOOKUP("ID",$A$1:$C$5,ROW(),FALSE),"")

Удачи.

EDIT / Сложение:

Используйте функцию мультивещания из: http://www.mcgimpsey.com/excel/udfs/multicat.html

Например, отсортируйте таблицу, чтобы избавиться от пробелов, а затем:

=multicat(C2:C5,",")

Или, если сортировка слишком сложна для вас - вы можете использовать эту модифицированную версию функции mcgimpsey, чтобы избавиться от пустых ячеек:

  Public Function MultiCat( _
        ByRef rRng As Excel.Range, _
        Optional ByVal sDelim As String = "") _
             As String
     Dim rCell As Range
     For Each rCell In rRng
        If rCell.Value <> "" Then
         MultiCat = MultiCat & sDelim & rCell.Text
         End If
     Next rCell
     MultiCat = Mid(MultiCat, Len(sDelim) + 1)
  End Function