Excel에서 주어진 합계에 해당하는 모든 조합을 찾으려면 어떻게 해야 하나요?

예산, 계획 또는 데이터 분석 목적으로 특정 합계를 만족시키는 숫자 목록 내의 가능한 모든 조합을 찾는 것은 많은 Excel 사용자가 직면할 수 있는 도전 과제입니다.

이 예제에서는 숫자 목록이 있으며, 목표는 이 목록에서 어떤 조합이 480의 합계를 이루는지 식별하는 것입니다. 제공된 스크린샷은 300+120+60, 250+120+60+50과 같은 조합을 포함하여 이 합계를 달성하는 다섯 가지 가능한 조합 그룹이 있음을 보여줍니다. 이 기사에서는 지정된 값으로 총합을 맞추는 숫자 목록 내의 특정 조합을 찾아내기 위한 다양한 방법을 살펴보겠습니다.

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

주어진 합계와 동일한 모든 숫자 조합 얻기

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

VBA 코드로 범위 내 합계를 가진 모든 숫자 조합 얻기

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

특정 숫자에 더해지는 셀 조합을 찾기 위해 Excel을 탐구하는 것이 어려워 보일 수 있지만, Solver 추가 기능 덕분에 매우 쉬워집니다. 우리는 Solver를 설정하고 올바른 셀 조합을 찾기 위한 간단한 단계를 안내하겠습니다. 복잡해 보였던 작업을 간단하고 실행 가능하게 만들 것입니다.

1단계: Solver 추가 기능 활성화하기

1. 다음으로 이동하십시오: 파일 > 옵션, Excel 옵션 대화 상자에서 왼쪽 창에서 추가 기능 을 클릭한 다음 이동 버튼을 클릭합니다. 스크린샷 보기:
   
2. 그러면 추가 기능 대화 상자가 나타나며 Solver 추가 기능 옵션을 선택하고 확인 을 클릭하여 이 추가 기능을 성공적으로 설치합니다.
   

2단계: 수식 입력하기

Solver 추가 기능을 활성화한 후에는 B11 셀에 다음 수식을 입력해야 합니다:

=SUMPRODUCT(B2:B10,A2:A10)

3단계: Solver를 구성하고 결과 얻기

1. 클릭 데이터 > Solver 를 통해 Solver 매개변수 대화 상자로 이동합니다. 대화 상자에서 다음 작업을 수행하십시오:
   • (1.) 클릭 버튼을 눌러 셀을 선택합니다. B11 수식이 위치한 목표 설정 섹션에서;
   • (2.) 그런 다음 To 섹션에서 Value Of를 선택하고 필요에 따라 목표 값 480을 입력합니다.
   • (3.) 변수 셀 변경 섹션에서 클릭하십시오. 버튼을 눌러 셀 범위를 선택하십시오. B2:B10 이는 해당 숫자를 표시할 위치입니다.
   • (4.) 그런 다음 Add 버튼을 클릭하십시오.
   • 
2. 그러면 조건 추가 대화 상자가 표시됩니다. 클릭 버튼을 눌러 셀 범위를 선택하십시오. B2:B10, 그리고 드롭다운 목록에서 BIN 을 선택하십시오. 마지막으로 확인 버튼을 클릭하십시오. 스크린샷 보기:
   
3. 대화 상자에서 Solver 매개변수 클릭 해결 버튼을 누르면 몇 분 후에 Solver 결과 대화 상자가 팝업되고, 주어진 합계 480과 일치하는 셀 조합이 B열에 1로 표시된 것을 확인할 수 있습니다. Solver 결과 대화 상자에서 Solver 솔루션 유지 옵션을 선택하고 확인 을 클릭하여 대화 상자를 종료합니다. 스크린샷 보기:
   

주어진 합계와 동일한 모든 숫자 조합 얻기

Excel의 더 깊은 기능을 탐구하면 특정 합계와 일치하는 모든 숫자 조합을 찾을 수 있으며, 생각보다 쉽습니다. 이 섹션에서는 주어진 합계와 동일한 모든 숫자 조합을 찾기 위한 두 가지 방법을 소개합니다.

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

특정 세트에서 주어진 값을 총합으로 도출하는 모든 가능한 숫자 조합을 발견하기 위해 아래에 설명된 사용자 정의 함수는 효과적인 도구로 작용합니다.

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

1. Excel에서 ALT + F11 키를 누르면 Microsoft Visual Basic for Applications 창이 열립니다.
2. 클릭 삽입 > 모듈, 그리고 모듈 창에 다음 코드를 붙여넣습니다.
   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단계: 사용자 정의 수식을 입력하여 결과 얻기

코드를 붙여넣은 후 코드 창을 닫고 워크시트로 돌아갑니다. 결과를 출력하기 위해 빈 셀에 다음 수식을 입력하고 Enter 키를 눌러 모든 조합을 얻습니다. 스크린샷 보기:

=MakeupANumber(A2:A10,B2)

팁: 조합 결과를 열에 세로로 나열하려면 다음 수식을 적용하십시오:

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

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

앞서 언급한 함수의 제한 사항을 감안하여, 우리는 신속하고 포괄적인 솔루션을 추천합니다: Kutools for Excel의 숫자 채우기 기능은 모든 버전의 Excel과 호환되며 양수, 소수, 음수를 효과적으로 처리할 수 있습니다. 이 기능을 사용하면 주어진 합계와 동일한 모든 조합을 빠르게 얻을 수 있습니다.

1. 클릭 Kutools > 텍스트 > 숫자 채우기, 스크린샷 보기:
   
2. 그런 다음 숫자 채우기 대화 상자에서 클릭 버튼을 눌러 사용하려는 숫자 목록을 선택하십시오. 원본 범위, 그리고 총합을 입력하십시오. 합계 텍스트 박스에 입력하십시오. 마지막으로 확인 버튼을 클릭하십시오. 스크린샷 보기:
   
3. 그리고 나서, 결과를 배치할 셀을 선택하라는 프롬프트 상자가 팝업됩니다. 그런 다음 확인, 스크린샷 보기:
   
4. 이제 주어진 숫자와 동일한 모든 조합이 아래 스크린샷에 표시된 것처럼 표시됩니다:
   

VBA 코드로 범위 내 합계를 가진 모든 숫자 조합 얻기

때때로, 특정 범위 내의 합계에 도달하는 숫자의 가능한 모든 조합을 식별해야 할 수도 있습니다. 예를 들어, 총합이 470에서 480 사이에 떨어지는 모든 가능한 숫자 그룹을 찾으려고 할 수 있습니다.

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

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

1. Excel에서 ALT + F11 키를 누르면 Microsoft Visual Basic for Applications 창이 열립니다.
2. 클릭 삽입 > 모듈, 그리고 모듈 창에 다음 코드를 붙여넣습니다.
   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단계: 코드 실행하기

1. 코드를 붙여넣은 후 F5 키를 눌러 이 코드를 실행합니다. 처음 팝업되는 대화 상자에서 사용하려는 숫자 범위를 선택하고 확인을 클릭합니다. 스크린샷 보기:
   
2. 두 번째 프롬프트 상자에서 하한 숫자를 선택하거나 입력하고 확인을 클릭합니다. 스크린샷 보기:
   
3. 세 번째 프롬프트 상자에서 상한 숫자를 선택하거나 입력하고 확인을 클릭합니다. 스크린샷 보기:
   
4. 마지막 프롬프트 상자에서 결과가 출력되기 시작할 셀을 선택합니다. 그런 다음 확인을 클릭합니다. 스크린샷 보기:
   

결과

이제 각 적격 조합은 선택한 출력 셀에서 시작하여 연속된 행에 나열됩니다.

Excel은 특정 총합에 도달하는 숫자 그룹을 찾기 위한 여러 가지 방법을 제공하며, 각 방법은 다르게 작동하므로 Excel에 대한 숙련도와 프로젝트 요구 사항에 따라 선택할 수 있습니다. 더 많은 Excel 팁과 트릭을 탐구하려는 경우, 우리 웹사이트는 수천 개의 자습서를 제공합니다. 읽어주셔서 감사합니다. 앞으로도 유용한 정보를 제공해 드릴 수 있기를 기대합니다!

관련 기사: