Pam에는 두 개의 데이터 열이 있습니다. A 열에는 A, B, C 등과 같은 간단한 식별자가 있습니다. B 열에는 일련의 정수 값이 있습니다.

그녀는 식별자를 기준으로 데이터를 정렬하고 두 번째로 정수 값을 기준으로 정렬 할 수 있습니다. 이제 그녀는 C 열에 특정 식별자에 대한 모든 정수 값을 연결하는 공식을 원합니다. 따라서 A1 : A4에 모두 식별자 A가 포함되어있는 경우 셀 C1에서 B1 : B4의 모든 값을 연결하고 “11, 17, 19, 25″와 같이 쉼표로 나누기를 원합니다. 각 식별자의 행 수가 다를 수 있으므로 Pam은 연결 방법을 잘 모릅니다.

이를 수행하는 가장 쉬운 방법은 사용자 정의 함수로 생성 할 수있는 매크로를 사용하는 것입니다. 다음은 예입니다.

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 열에 대한 것인지 확인합니다. 그렇다면 A 열의 값을 기반으로 B 열의 값을 연결하기 시작합니다. 값이 A 열인 경우 그 위에있는 행의 값과 다른 경우 연결된 값의 문자열을 반환합니다.

식별자가 A 열에 있고 연결할 값이 B 열에 있다고 가정하면 다음을 C 열에 배치 할 수 있습니다.

=CatSame(C1)

필요한만큼 C 열에 복사하면 Pam이 원했던 그대로를 얻을 수 있습니다.

보다 다재다능한 기능은 VLOOKUP과 비슷하게 작동하지만 찾고있는 값과 일치하는 연결된 값 목록을 다시 가져 오는 기능입니다. 다음 함수를 고려하십시오.

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

이 함수는 최대 4 개의 인수를 사용합니다. 첫 번째는 조회에서 일치시킬 값입니다. Pam의 경우 이것은 A, B 또는 C와 같이 원하는 식별자입니다. 두 번째 인수는 일치 항목을 찾을 셀 범위입니다 (이 경우 A 열).

세 번째 인수는 오프셋입니다 (두 번째 인수의 범위에서)

연결하려는 값을 나타냅니다. 다음과 같은 방식으로 함수를 사용할 수 있습니다.

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

값 사이에 다른 구분 기호를 지정하려면 선택적 네 번째 인수를 사용하면됩니다. 예를 들어 다음은 대시가 각 값을 구분하는 문자열을 반환합니다.

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

지금까지의 솔루션은 매크로 사용에 중점을 두었습니다. 그 이유는 비교적 간단합니다. Pam이 필요로하는 것을 수행 할 수있는 공식 기반 솔루션이 없습니다. 중첩 된 IF 문을 사용하여 A 열에있는 내용을 평가하는 것은 IF 문을 중첩 할 수있는 깊이에 제한이 있기 때문에 제대로 작동하지 않습니다.

연결된 값이 A 열에있는 식별자의 마지막 인스턴스에있는 것을 염두에 두지 않으면 수식과 중간 결과를 사용할 수 있습니다. 먼저 다음 수식을 셀 C1에 넣습니다.

=B1

이 수식은 셀 C2로 이동해야합니다.

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

이 수식을 필요한만큼 많은 행에 복사합니다. 결국 C 열에 연결된 값의 길이가 점점 더 길어지고 각 실행에서 가장 긴 값이 A 열의 마지막 순차 식별자와 동일한 행에 있습니다. 그런 다음 열의 모든 해당 셀에 다음을 넣을 수 있습니다. D :

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

이 수식은 Pam이 시작하는 데 필요한 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 [가변 수의 셀에서 값 연결].