У Пэм есть два столбца данных. В столбце A есть простые идентификаторы, такие как A, B, C и т. Д. В столбце B есть ряд целочисленных значений.

Она может сортировать данные по идентификатору и, во вторую очередь, по целочисленным значениям. Теперь она хочет, чтобы в столбце C была формула, которая объединит все целочисленные значения для определенного идентификатора. Таким образом, если все A1: A4 содержат идентификатор A, то в ячейке C1 она хотела бы, чтобы все значения в B1: B4 были объединены и разделены запятыми, например «11, 17, 19, 25». Поскольку количество строк для каждого идентификатора может быть разным, Пэм не знает, как проводить конкатенацию.

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

Function CatSame(c As Range) As String     Application.Volatile     sTemp = ""

iCurCol = c.Column     If iCurCol = 3 Then         If c.Row = 1 Then             sLast = ""

Else             sLast = c.Offset(-1, -2)

End If         If c.Offset(0, -2) <> sLast Then             J = 0             Do                 sTemp = sTemp & ", " & c.Offset(J, -1)

J = J + 1             Loop While c.Offset(J, -2) = c.Offset(J - 1, -2)

sTemp = Right(sTemp, Len(sTemp) - 2)

End If     End If     CatSame = sTemp End Function

Эта функция в основном принимает значение, которое ей передается (ссылка на ячейку), и проверяет, что ссылка на ячейку относится к столбцу C. Если это так, то она начинает объединять значения из столбца B на основе значений в столбце A. Это только возвращает строку объединенных значений, если значение столбца A отличается от значения в строке над ним.

Предполагая, что ваши идентификаторы находятся в столбце A, а ваши значения, которые необходимо объединить, находятся в столбце B, вы можете поместить следующее в столбец C:

=CatSame(C1)

Скопируйте это, насколько это необходимо, в столбец C, и вы получите именно то, что хотела Пэм.

Более универсальной функцией будет функция, которая будет работать как ВПР, но будет возвращать объединенный список значений, соответствующих тому, что вы ищете. Рассмотрим следующую функцию:

Function VLookupAll(vValue, rngAll As Range, _   iCol As Integer, Optional sSep As String = ", ")

Dim rCell As Range     Dim rng As Range     On Error GoTo ErrHandler

Application.Volatile     Set rng = Intersect(rngAll, rngAll.Columns(1))

For Each rCell In rng         If rCell.Value = vValue Then _           VLookupAll = VLookupAll & sSep & _           rCell.Offset(0, iCol).Value     Next rCell

If VLookupAll = "" Then         VLookupAll = CVErr(xlErrNA)

Else         VLookupAll = Right(VLookupAll, Len(VLookupAll) - Len(sSep))

End If ErrHandler:

If Err.Number <> 0 Then VLookupAll = CVErr(xlErrValue)

End Function

Эта функция принимает до четырех аргументов. Первое — это значение, которое вы хотите сопоставить при поиске. В случае Пэм это будет нужный вам идентификатор, например A, B или C. Второй аргумент — это диапазон ячеек, в котором нужно искать совпадения (в данном случае столбец A).

Третий аргумент — это смещение (от диапазона во втором аргументе)

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

=VLookupAll("B",A1:A99,1)

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

=VLookupAll("B",A1:A99,1,"-")

До сих пор решения были сосредоточены на использовании макросов. Причина этого относительно проста: не существует решения на основе формулы, которое могло бы сделать то, что нужно Пэм. Использование вложенных операторов IF для оценки того, что находится в столбце A, не будет работать, потому что вы ограничены в том, насколько глубоко могут быть вложены операторы IF.

Вы можете использовать формулу и промежуточный результат, если вы не против, чтобы объединенные значения находились в последнем экземпляре идентификатора в столбце A. Начните с помещения этой формулы в ячейку C1:

=B1

Эта формула должна войти в ячейку C2:

=IF(A2=A1,C1 & ", " & B2, B2)

Скопируйте эту формулу вниз на необходимое количество строк. В итоге вы получите все более длинный ряд конкатенированных значений в столбце C, причем самые длинные в каждом прогоне находятся в той же строке, что и последний последовательный идентификатор в столбце A. Затем вы можете поместить следующее во все применимые ячейки столбца D:

=IF(LEN(C2)>LEN(C1),"",C1)

Эта формула отображает только самые длинные строки из столбца C, что и нужно Пэм для начала.

_Примечание: _

Если вы хотите узнать, как использовать макросы, описанные на этой странице (или на любой другой странице на сайтах ExcelTips), я подготовил специальную страницу, содержащую полезную информацию.

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (9197) применим к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и новее) здесь:

link: / excelribbon-Concatenating_Values_from_a_Variable_Number_of_Cells [Объединение значений из переменного числа ячеек].