Kathy는 창고의 모든 부품에 대한 정보가 포함 된 워크 시트를 가지고 있습니다. 이 시트에서 부품 번호는 12345 XXX 형식을 사용하여 A 열에 표시됩니다. 여기서 XXX는 위치 코드를 나타냅니다. 즉, 동일한 부품 번호에 대해 워크 시트에 여러 항목이있을 수 있지만 각 항목은 해당 부품의 다른 위치를 나타냅니다. Kathy는 위치 코드에 관계없이 각 부품 번호와 관련된 값을 합산하는 공식이 필요합니다. 따라서 부품 12345 ABC, 12345 DEF, 12345 GHI 등과 관련된 수량 열을 합산하는 방법이 필요합니다.

그녀는 위치 코드를 다른 열로 분할하지 않고이를 수행 할 방법이 필요합니다.

원하는 답을 얻는 방법은 여러 가지가 있습니다. 이 팁의 예를 위해 부품 번호가 A 열 (Kathy가 표시 한대로)에 있고 각 부품의 수량이 B 열에 있다고 가정합니다.이 수량은 a를 기준으로 합산되어야합니다. A 열의 각 셀에있는 부분. 또한 원하는 부품 번호 (위치 코드 제외)를 셀 D2에 입력 할 수 있습니다.

첫 번째 잠재적 솔루션은 다음과 같은 방식으로 SUMPRODUCT 함수를 사용하는 것입니다.

=SUMPRODUCT(--(VALUE(LEFT(A2:A49,FIND(" ",A2:A49)))=D2),B2:B49)

이 수식은 A2 : A49 범위의 값을 확인합니다. 이 범위가 실제 데이터의 범위를 반영하는지 확인해야합니다. A : A 및 B : B에서와 같이 A 및 B 열을 모두 보도록 수식을 일반화하면 열의 빈 셀에 수식을 적용하려고하므로 #VALUE 오류가 발생합니다. .

다음과 같은 배열 수식을 사용하여 비슷한 결과를 얻을 수 있습니다.

=SUM(B:B*(LEFT(A2:A49,5)=TEXT(D2,"@")))

다시 말하지만 이것은 배열 수식이므로 Shift + Ctrl + Enter를 눌러 입력해야합니다. 또한이 공식은 비교를 위해 D2의 값을 텍스트로 변환합니다. A 열에서 선택한 부분 문자열이 VALUE 함수를 사용하여 숫자 값으로 변환 되었기 때문에 이전 수식에서는 수행되지 않았습니다.

DSUM 함수를 사용하여 작업 수식을 구성 할 수도 있습니다. 부품 번호 (A 열)의 A1 셀에 열 머리글이 있다고 가정합니다.

이 열 머리글 (예 : “Part Num”)을 워크 시트의 다른 셀 (예 : D1 셀)에 복사합니다. D2 셀에 위치 코드없이 부품 번호와 별표를 차례로 입력합니다. 예를 들어 D2 셀에 “12345 *”(따옴표 제외)를 입력 할 수 있습니다. 해당 사양을 설정하면 다음 공식을 사용할 수 있습니다.

=DSUM($A$1:$B$49,$B$1,D1:D2)

이 수식은 B 열의 값을 합산해야하는 키로 D2 셀의 사양 (12345 뒤에 임의의 문자)을 사용합니다.

마지막으로, DSUM 접근 방식에서 사용한 것과 동일한 사양이 셀 D2에있는 경우 다음과 같은 방식으로 매우 간단한 SUMIF 함수를 사용할 수 있습니다.

=SUMIF(A:A,D2,B:B)

이 방법을 사용하면 수식에서 전체 열 범위 (A : A 및 B : B)를 사용할 수 있습니다.

부품 번호 (A 열)가 원하는 형식과 일치하지 않는 경우 수량을 찾기 위해 사용자 정의 함수를 생성하는 것이 좋습니다. 예를 들어 부품 번호가 항상 같은 길이가 아니거나 부품 번호에 숫자와 문자 또는 대시가 모두 포함될 수있는 경우 UDF가 적합합니다. 다음 예제는 훌륭하게 작동합니다. 값에 적어도 하나의 공백이 있는지 여부를 결정합니다. (Kathy는 위치 코드에서 부품 번호를 공백으로 구분한다고 표시했습니다.)

Function AddPrtQty(ByVal Parts As Range, PartsQty As Range, _   FindPart As Variant) As Long     Dim Pos As Integer     Dim Pos2 As Integer     Dim i As Long     Dim tmp As String     Dim tmpSum As Long     Dim PC As Long

PC = Parts.Count     If PartsQty.Count <> PC Then         MsgBox "Parts and PartsQty must be the same length", vbCritical         Exit Function     End If

For i = 1 To PC         Pos = InStr(1, Parts(i), " ")

Pos2 = InStr(Pos + 1, Parts(i), " ")



If Pos2 > Pos And Len(Parts(i)) > Pos + 1 Then             tmp = CStr(Trim(Left(Parts(i), Pos2 - 1)))

ElseIf Pos > 0 And Len(Parts(i)) > 0 Then             tmp = CStr(Trim(Left(Parts(i), Pos - 1)))

End If

If CStr(Trim(tmp)) = CStr(Trim(FindPart)) Then             tmpSum = tmpSum + PartStock(i)

End If     Next i

AddPrtQty = tmpSum End Function

함수를 사용하려면 워크 시트에서 두 개의 범위와 원하는 부품 번호를 사용하여 함수를 호출하십시오.

=AddPrtQty(A2:A49,B2:B49,"GB7-QWY2")

_ 참고 : _

이 페이지 (또는 ExcelTips 사이트의 다른 페이지)에 설명 된 매크로를 사용하는 방법을 알고 싶다면 유용한 정보가 포함 된 특별 페이지를 준비했습니다.

link : / excelribbon-ExcelTipsMacros [새 브라우저 탭에서 특별 페이지를 열려면 여기를 클릭하세요].

_ExcelTips_는 비용 효율적인 Microsoft Excel 교육을위한 소스입니다.

이 팁 (11469)은 Microsoft Excel 2007, 2010, 2013 및 2016에 적용됩니다.

Excel의 이전 메뉴 인터페이스에 대한이 팁의 버전은 여기에서 찾을 수 있습니다.

link : / excel-Summing_Based_on_Part_of_the_Information_in_a_Cell [셀 정보의 일부를 기반으로 합산].