Excel 에서 주어진 합계와 일치하는 모든 조합을 찾는 방법은 무엇인가요？

목록 내에서 특정 합계와 동일한 모든 가능한 숫자 조합을 찾는 것은 예산 책정， 계획 수립 또는 데이터 분석 목적 등 다양한 상황에서 많은 Excel 사용자가 마주치는 과제입니다。

이 예제에서는 숫자 목록이 주어졌으며, 목표는 이 목록에서 합계가 480 가 되는 조합을 식별하는 것입니다. 제공된 스크린샷은 이 합계를 달성하는 다섯 가지 가능한 조합 그룹이 있음을 보여줍니다. 예를 들어 300+120+60, 250+120+60+50 등의 조합이 있습니다. 본 문서에서는 Excel 에서 지정된 값과 동일한 합계를 이루는 숫자 조합을 정확히 찾아내는 다양한 방법을 살펴보겠습니다。

솔버 함수로 주어진 합계와 동일한 숫자 조합 찾기

주어진 합계와 동일한 모든 숫자 조합 가져오기

• 사용자 정의 함수 사용
• 스마트 기능 – Kutools for Excel 사용

VBA 코드로 특정 범위의 합계와 동일한 모든 숫자 조합 가져오기

솔버 함수로 주어진 합계와 동일한 셀 조합 찾기

Excel 에서 특정 숫자와 동일한 합계를 이루는 셀 조합을 찾는 작업은 처음에는 어렵게 느껴질 수 있지만， 솔버 추가 기능을 사용하면 매우 간단해집니다。 솔버를 설정하고 적절한 셀 조합을 찾는 간단한 단계를 안내해 드리겠습니다。 이를 통해 복잡해 보이던 작업이 쉽고 실현 가능하게 바뀝니다。

1 단계 1: 솔버 추가 기능 활성화

1. 파일File>옵션으로 이동한 후，Excel 옵션대화 상자에서 왼쪽 창의추가 기능을 클릭하고， 그 다음이동버튼을 클릭하세요。 스크린샷 참조：
   
2. 그러면추가 기능대화 상자가 나타납니다。 여기서솔버 추가 기능옵션을 선택한 후확인을 클릭하여 이 추가 기능을 성공적으로 설치합니다。
   

2 단계 2: 수식 입력

솔버 추가 기능을 활성화한 후 셀 B11 에 다음 수식을 입력해야 합니다：

=SUMPRODUCT(B2:B10,A2:A10)

3 단계 3: 솔버를 구성하고 실행하여 결과 얻기

1. 데이터Data>솔버를 클릭하여솔버 매개변수대화 상자로 이동합니다。 대화 상자에서 다음 작업을 수행하세요：
   • (1.)버튼을 클릭하여 수식이 있는 셀B11을(를)목표 설정섹션에서 선택하세요；
   • (2.)그런 다음대상섹션에서값을(를) 선택하고 필요에 따라 대상 값을 입력하세요480;
   • (3.)변수 셀 변경으로섹션에서버튼을 클릭하여 해당 숫자가 표시될 셀 범위B2:B10을(를) 선택하세요。
   • (4.)그런 다음추가버튼을 클릭하세요。
   • 
2. 그러면제약 조건 추가대화 상자가 표시됩니다。버튼을 클릭하여 셀 범위B2:B10을 선택한 후 드롭다운 목록에서bin을 선택합니다。 마지막으로확인버튼을 클릭하세요。 스크린샷 참조：
   
3. 솔버 매개변수대화 상자에서해결버튼을 클릭합니다。 몇 분 후솔버 결과대화 상자가 팝업되며, 주어진 합계 480 와 일치하는 셀 조합이 열 B 에서 1 로 표시됩니다。솔버 결과대화 상자에서 선택하세요솔버 해결책 유지옵션을 선택하고확인을 클릭하여 대화 상자를 종료하세요。 스크린샷 참조：
   

주어진 합계와 동일한 모든 숫자 조합 가져오기

Excel 의 고급 기능을 활용하면 특정 합계와 일치하는 모든 숫자 조합을 쉽게 찾아낼 수 있습니다。 이 섹션에서는 주어진 합계와 동일한 모든 숫자 조합을 찾는 두 가지 방법을 소개합니다。

사용자 정의 함수로 주어진 합계와 동일한 모든 숫자 조합 가져오기

특정 집합에서 주어진 값에 도달하는 모든 가능한 숫자 조합을 찾아내려면 아래에 설명된 사용자 정의 함수가 효과적인 도구가 됩니다。

1 단계 1: VBA 모듈 편집기를 열고 코드 복사

1. Excel 에서ALT + F11키를 누르면Microsoft Visual Basic for Applications창이 열립니다。
2. 삽입Insert>모듈을 클릭하고 모듈 창에 다음 코드를 붙여넣으세요。
   VBA 코드： 주어진 합계와 동일한 모든 숫자 조합 가져오기

   Public Function MakeupANumber(xNumbers As Range, xCount As Long)
   'updateby Extendoffice
       Dim arrNumbers() As Long
       Dim arrRes() As String
       Dim ArrTemp() As Long
       Dim xIndex As Long
       Dim rg As Range
   
       MakeupANumber = ""
       
       If xNumbers.CountLarge = 0 Then Exit Function
       ReDim arrNumbers(xNumbers.CountLarge - 1)
       
       xIndex = 0
       For Each rg In xNumbers
           If IsNumeric(rg.Value) Then
               arrNumbers(xIndex) = CLng(rg.Value)
               xIndex = xIndex + 1
           End If
       Next rg
       If xIndex = 0 Then Exit Function
       
       ReDim Preserve arrNumbers(0 To xIndex - 1)
       ReDim arrRes(0)
       
       Call Combinations(arrNumbers, xCount, ArrTemp(), arrRes())
       ReDim Preserve arrRes(0 To UBound(arrRes) - 1)
       MakeupANumber = arrRes
   End Function
   
   Private Sub Combinations(Numbers() As Long, Count As Long, ArrTemp() As Long, ByRef arrRes() As String)
   
       Dim currentSum As Long, i As Long, j As Long, k As Long, num As Long, indRes As Long
       Dim remainingNumbers() As Long, newCombination() As Long
       
       currentSum = 0
       If (Not Not ArrTemp) <> 0 Then
           For i = LBound(ArrTemp) To UBound(ArrTemp)
               currentSum = currentSum + ArrTemp(i)
           Next i
       End If
    
       If currentSum = Count Then
           indRes = UBound(arrRes)
           ReDim Preserve arrRes(0 To indRes + 1)
           
           arrRes(indRes) = ArrTemp(0)
           For i = LBound(ArrTemp) + 1 To UBound(ArrTemp)
               arrRes(indRes) = arrRes(indRes) & "," & ArrTemp(i)
           Next i
       End If
       
       If currentSum > Count Then Exit Sub
       If (Not Not Numbers) = 0 Then Exit Sub
       
       For i = 0 To UBound(Numbers)
           Erase remainingNumbers()
           num = Numbers(i)
           For j = i + 1 To UBound(Numbers)
               If (Not Not remainingNumbers) <> 0 Then
                   ReDim Preserve remainingNumbers(0 To UBound(remainingNumbers) + 1)
               Else
                   ReDim Preserve remainingNumbers(0 To 0)
               End If
               remainingNumbers(UBound(remainingNumbers)) = Numbers(j)
               
           Next j
           Erase newCombination()
   
           If (Not Not ArrTemp) <> 0 Then
               For k = 0 To UBound(ArrTemp)
                   If (Not Not newCombination) <> 0 Then
                       ReDim Preserve newCombination(0 To UBound(newCombination) + 1)
                   Else
                       ReDim Preserve newCombination(0 To 0)
                   End If
                   newCombination(UBound(newCombination)) = ArrTemp(k)
   
               Next k
           End If
           
           If (Not Not newCombination) <> 0 Then
               ReDim Preserve newCombination(0 To UBound(newCombination) + 1)
           Else
               ReDim Preserve newCombination(0 To 0)
           End If
           
           newCombination(UBound(newCombination)) = num
   
           Combinations remainingNumbers, Count, newCombination, arrRes
       Next i
   
   End Sub
   

2 단계 2: 사용자 정의 수식을 입력하여 결과 얻기

코드를 붙여넣은 후 코드 창을 닫고 워크시트로 돌아갑니다。 빈 셀에 다음 수식을 입력하여 결과를 출력한 후Enter키를 눌러 모든 조합을 가져오세요。 스크린샷 참조：

=MakeupANumber(A2:A10,B2)

팁： 조합 결과를 열 방향으로 세로로 나열하려면 다음 수식을 사용하세요：

=TRANSPOSE(MakeupANumber(A2:A10,B2))

강력한 기능으로 주어진 합계와 동일한 모든 숫자 조합 가져오기

앞서 설명한 함수의 제한 사항을 고려하여, 모든 Excel 버전과 호환되는 신속하고 포괄적인 솔루션인 Kutools for Excel 의 숫자 채우기 기능을 추천합니다。 이 기능은 양수， 소수， 음수까지 모두 처리할 수 있으며， 주어진 합계와 동일한 모든 조합을 빠르게 찾아냅니다。

1. Kutools Kutools>콘텐츠>숫자 채우기를 클릭하세요。 스크린샷 참조：
   
2. 그러면숫자 채우기대화 상자에서버튼을 클릭하여 사용할 숫자 목록을원본 범위에서 선택한 후합계텍스트 상자에 총 숫자를 입력합니다。 마지막으로확인버튼을 클릭하세요。 스크린샷 참조：
   
3. 그러면 결과를 표시할 셀을 선택하라는 메시지 창이 팝업됩니다。 이후확인을 클릭하세요。 스크린샷 참조：
   
4. 이제 주어진 숫자와 동일한 모든 조합이 아래 스크린샷과 같이 표시되었습니다：
   

VBA 코드로 특정 범위의 합계와 동일한 모든 숫자 조합 가져오기

때때로 특정 범위 내의 합계와 동일한 모든 가능한 숫자 조합을 찾아야 하는 상황이 발생할 수 있습니다. 예를 들어, 총합이 470 과 480 사이에 있는 모든 가능한 숫자 그룹을 찾아야 할 수도 있습니다。

특정 범위 내의 값과 동일한 합계를 이루는 모든 가능한 숫자 조합을 찾아내는 것은 Excel 에서 매우 흥미롭고 실용적인 과제입니다。 이 섹션에서는 이 작업을 해결하기 위한 VBA 코드를 소개합니다。

1 단계 1: VBA 모듈 편집기를 열고 코드 복사

1. Excel 에서ALT + F11키를 누르면Microsoft Visual Basic for Applications창이 열립니다。
2. 삽입Insert>모듈을 클릭하고 모듈 창에 다음 코드를 붙여넣으세요。
   VBA 코드： 특정 범위의 합계와 동일한 모든 숫자 조합 가져오기

   Sub Getall_combinations()
   'Updateby Extendoffice
       Dim xNumbers As Variant
       Dim Output As Collection
       Dim rngSelection As Range
       Dim OutputCell As Range
       Dim LowLimit As Long, HiLimit As Long
       Dim i As Long, j As Long
       Dim TotalCombinations As Long
       Dim CombTotal As Double
       Set Output = New Collection
       On Error Resume Next
       Set rngSelection = Application.InputBox("Select the range of numbers:", "Kutools for Excel", Type:=8)
       If rngSelection Is Nothing Then
           MsgBox "No range selected. Exiting macro.", vbInformation, "Kutools for Excel"
           Exit Sub
       End If
       On Error GoTo 0
       xNumbers = rngSelection.Value
       LowLimit = Application.InputBox("Select or enter the low limit number:", "Kutools for Excel", Type:=1)
       HiLimit = Application.InputBox("Select or enter the high limit number:", "Kutools for Excel", Type:=1)
       On Error Resume Next
       Set OutputCell = Application.InputBox("Select the first cell for output:", "Kutools for Excel", Type:=8)
       If OutputCell Is Nothing Then
           MsgBox "No output cell selected. Exiting macro.", vbInformation, "Kutools for Excel"
           Exit Sub
       End If
       On Error GoTo 0
       TotalCombinations = 2 ^ (UBound(xNumbers, 1) * UBound(xNumbers, 2))
       For i = 1 To TotalCombinations - 1
           Dim tempArr() As Double
           ReDim tempArr(1 To UBound(xNumbers, 1) * UBound(xNumbers, 2))
           CombTotal = 0
           Dim k As Long: k = 0
           
           For j = 1 To UBound(xNumbers, 1)
               If i And (2 ^ (j - 1)) Then
                   k = k + 1
                   tempArr(k) = xNumbers(j, 1)
                   CombTotal = CombTotal + xNumbers(j, 1)
               End If
           Next j
           If CombTotal >= LowLimit And CombTotal <= HiLimit Then
               ReDim Preserve tempArr(1 To k)
               Output.Add tempArr
           End If
       Next i
       Dim rowOffset As Long
       rowOffset = 0
       Dim item As Variant
       For Each item In Output
           For j = 1 To UBound(item)
               OutputCell.Offset(rowOffset, j - 1).Value = item(j)
           Next j
           rowOffset = rowOffset + 1
       Next item
   End Sub
   
   
   

2 단계 2: 코드 실행

1. 코드를 붙여넣은 후F5키를 눌러 이 코드를 실행합니다。 첫 번째 팝업 대화 상자에서 사용할 숫자 범위를 선택하고확인을 클릭하세요。 스크린샷 참조：
   
2. 두 번째 메시지 창에서 하한 값을 선택하거나 입력한 후확인을 클릭하세요。 스크린샷 참조：
   
3. 세 번째 메시지 창에서 상한 값을 선택하거나 입력한 후확인을 클릭하세요。 스크린샷 참조：
   
4. 마지막 메시지 창에서 결과가 출력될 시작 셀을 선택한 후확인을 클릭하세요。 스크린샷 참조：
   

결과

이제 각각의 조건을 충족하는 조합이 선택한 출력 셀부터 워크시트의 연속된 행에 나열됩니다。

Excel 에서는 특정 합계가 되는 숫자 그룹을 찾는 여러 가지 방법을 제공합니다. 각 방법은 작동 방식이 다르므로 Excel 에 익숙한 정도와 프로젝트 요구 사항에 따라 적절한 방법을 선택할 수 있습니다。 더 많은 Excel 팁과 기능을 알아보고 싶으시다면，저희 웹사이트에서 수천 개의 튜토리얼을 확인해 보세요。 읽어주셔서 감사드리며， 앞으로도 유용한 정보를 제공해 드릴 수 있기를 기대합니다！

관련 문서：