UDF: для значений ячейки меньше, чем x в столбце, возвращайте все значения из первого столбца
Для набора данных, подобного следующему, я хотел бы, если значение в строке 1 равно заданному значению, возвращает все значения столбца идентификатора в строках, где значение ячейки в указанном столбце меньше 3. (Никакие значения не повторяются в столбец идентификатора или в заголовках строк 1).)
ID | X | Y | Z 123 | 1 | 2 | 5 456 | 2 | 6 | 4 789 | 6 | 1 | 2 например, если заголовок столбца = «X», возвращаемое значение «123, 456». Если Y, "123, 789". Если Z, "789". Я нашел вариацию «многотипной формулы» (в редактировании связанного ответа), которая приближается к моим потребностям, но мне трудно адаптировать ее.
Public Function MultiCat2( _ ByRef rRng As Excel.Range, _ Optional ByVal sDelim As String = "") _ As String Dim rCell As Range For Each rCell In rRng If rCell.Value < 3 Then MultiCat2 = MultiCat2 & sDelim & rCell.Text End If Next rCell MultiCat2 = Mid(MultiCat2, Len(sDelim) + 1) End Function
Например, если я запускаю функцию как есть на X, она возвращает значение «1, 2.» Результат всегда должен быть из столбца ID независимо от того, какой столбец будет оцениваться. Эта часть должна быть простой, но я не могу понять, как это сделать без смещения, что мне не помогает, потому что оцененный столбец будет переменным.
- Как превратить формулу Excel в UDF?
- Неустойчивая пользовательская функция не пересчитывается, как ожидалось (VBA / Excel)
- сравните 3 диапазона вместо 2
- Как использовать операторы сравнения в аргументах VBA Excel UDF?
- Excel VBA: Если ... затем выйти из UDF без изменения значения ячейки
Мне нужна простая английская логика: «Если значение ячейки в a1: d1 = X, MultiCat a1: a4, где значение ячейки в [выбранном столбце] равно <3." Я могу найти столбец, который хотел бы выполнить оценку, используя функцию Match, и у меня есть код, который, я думаю, мне нужно объединить результаты в одну ячейку.
Я просто не могу понять, как включить результат Match в функцию, или как заставить функцию конкатенации столбца ID.
- Excel UDF работает при вызове через sub, но всегда возвращает 0 на листе
- UDF для возврата отформатированного номера
- Excel VBA UDF выполняет в непосредственном окне, не работает на рабочем листе
- VBA UDF Excel 2010 #VALUE
- Ячейки, использующие UDF в Excel, не переучитывают
- UDF не работает с ссылками на ячейки, если локаль изменена
- Excel VBA UDF для конкатенации дает сообщение об ошибке
- Ссылка на внешнюю книгу в функции
Вы можете a) жестко закодировать столбец идентификатора в функции; b) добавить параметр, чтобы передать столбец идентификатора в функцию; c) передать имя заголовка столбца в функцию.
Option Explicit Public Function MultiCat2A(ByRef rRng As Excel.Range, _ Optional ByVal sDelim As String = ",") _ As String Dim c As Long, cRng As Range 'restrict rRng to the .UsedRange Set rRng = Intersect(rRng, rRng.Parent.UsedRange) 'set cRng to another column but equal to rRng Set cRng = Intersect(rRng.EntireRow, rRng.Parent.Columns("A")) For c = 1 To rRng.Count If rRng(c).Value < 3 Then MultiCat2A = MultiCat2A & sDelim & cRng(c).Text End If Next c MultiCat2A = Mid(MultiCat2A, Len(sDelim) + 1) If CBool(Len(sDelim)) Then Do While Right(MultiCat2A, Len(sDelim)) = sDelim MultiCat2A = Left(MultiCat2A, Len(MultiCat2A) - Len(sDelim)) Loop End If End Function Public Function MultiCat2B(ByRef cRng As Range, _ ByRef rRng As Excel.Range, _ Optional ByVal sDelim As String = ",") _ As String Dim c As Long 'restrict rRng to the .UsedRange Set rRng = Intersect(rRng, rRng.Parent.UsedRange) 'resize cRng to the same as rRng Set cRng = cRng(1, 1).Resize(rRng.Rows.Count, rRng.Columns.Count) For c = 1 To rRng.Count If rRng(c).Value < 3 Then MultiCat2B = MultiCat2B & sDelim & cRng(c).Text End If Next c MultiCat2B = Mid(MultiCat2B, Len(sDelim) + 1) If CBool(Len(sDelim)) Then Do While Right(MultiCat2B, Len(sDelim)) = sDelim MultiCat2B = Left(MultiCat2B, Len(MultiCat2B) - Len(sDelim)) Loop End If End Function Public Function MultiCat2C(ByVal sHdr As String, _ ByRef rRng As Excel.Range, _ Optional ByVal sDelim As String = ",") _ As String Dim c As Long, cRng As Range 'restrict rRng to the .UsedRange Set rRng = Intersect(rRng, rRng.Parent.UsedRange) 'find the column by header label c = Application.Match(sHdr, rRng.Parent.Rows(1), 0) 'offset cRng by its column vs rRng's column Set cRng = rRng(1, 1).Offset(0, c - rRng.Column) For c = 1 To rRng.Count If rRng(c).Value < 3 Then MultiCat2C = MultiCat2C & sDelim & cRng(c).Text End If Next c MultiCat2C = Mid(MultiCat2C, Len(sDelim) + 1) If CBool(Len(sDelim)) Then Do While Right(MultiCat2C, Len(sDelim)) = sDelim MultiCat2C = Left(MultiCat2C, Len(MultiCat2C) - Len(sDelim)) Loop End If End Function
В образце G2: G5, как,
=MultiCat2A(B2:B4) =MultiCat2B($A2:$A4, B2:B4) =MultiCat2C("ID", B2:B4) =MultiCat2C($A1, B2:B99)
Заполняйте по мере необходимости.
