특정 합계를 추가하는 목록 내에서 가능한 모든 숫자 조합을 검색하는 것은 예산 책정, 계획 또는 데이터 분석 목적으로 많은 Excel 사용자가 직면할 수 있는 문제입니다.

이 예에는 숫자 목록이 있으며 목표는 이 목록에서 합이 480이 되는 조합을 식별하는 것입니다. 제공된 스크린샷은 300+120과 같은 조합을 포함하여 이 합을 달성하는 60개의 가능한 조합 그룹이 있음을 보여줍니다. +250, 120+60+50+XNUMX 등. 이 문서에서는 Excel에서 지정된 값을 합산하는 목록 내 특정 숫자 조합을 찾아내는 다양한 방법을 살펴보겠습니다.

Solver 기능을 사용하여 주어진 합계와 동일한 숫자의 조합 찾기

주어진 합계와 동일한 숫자의 모든 조합을 가져옵니다.

• 사용자 정의 기능을 사용하여
• 스마트 기능으로 – Excel용 Kutools

VBA 코드를 사용하여 범위에 합계가 있는 모든 숫자 조합 가져오기

Solver 기능을 사용하여 주어진 합계와 동일한 셀 조합 찾기

특정 숫자에 해당하는 셀 조합을 찾기 위해 Excel을 사용하는 것은 어려운 것처럼 보일 수 있지만 Solver 추가 기능을 사용하면 매우 쉽습니다. 솔버를 설정하고 올바른 셀 조합을 찾는 간단한 단계를 안내하여 복잡한 작업처럼 보였던 작업을 간단하고 실행 가능하게 만듭니다.

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

1. 로 이동하십시오 입양 부모로서의 귀하의 적합성을 결정하기 위해 미국 이민국에 > 옵션 ,에 Excel 옵션 대화 상자에서 추가 기능 왼쪽 창에서 다음을 클릭하세요. Go 단추. 스크린 샷보기 :
   
2. 그런 다음 추가 기능 대화상자가 나타나면 솔버 애드 인 옵션을 클릭하고 OK 이 추가 기능을 성공적으로 설치합니다.
   

2 단계 : 수식 입력

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

=SUMPRODUCT(B2:B10,A2:A10)

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

1. Data > 해결사 로 이동합니다 솔버 매개 변수 대화 상자에서 대화 상자에서 다음 작업을 수행하십시오.
   • (1.) 클릭 셀을 선택하는 버튼 B11 귀하의 수식이 어디에 있는지 목표 설정 섹션;
   • (2.) 그런 다음 에 섹션에서 선택 가치, 목표 값을 입력하십시오. 480 당신이 필요로;
   • (3.) 아래 가변 셀 변경 섹션을 클릭하십시오 셀 범위를 선택하는 버튼 B2 : B10 해당 번호는 어디에 표시됩니다.
   • (4.) 그런 다음 추가 버튼을 클릭합니다.
   • 
2. 그런 다음 제약 추가 대화 상자가 표시되면 클릭하세요. 셀 범위를 선택하는 버튼 B2 : B10, 선택 큰 상자 드롭다운 목록에서 마지막으로 클릭하세요. OK 단추. 스크린 샷보기 :
   
3. . 솔버 매개 변수 대화 상자에서 해결 버튼, 몇 분 후 솔버 결과 대화상자가 팝업되며, B열에 주어진 합이 480인 셀의 조합이 1로 표시되는 것을 볼 수 있습니다. 솔버 결과 대화 상자, 선택하십시오 솔버 솔루션 유지 옵션을 클릭하고 OK 대화 상자를 종료합니다. 스크린 샷보기 :
   

주어진 합계와 동일한 숫자의 모든 조합을 가져옵니다.

Excel의 심층적인 기능을 탐색하면 특정 합계와 일치하는 모든 숫자 조합을 찾을 수 있으며 생각보다 쉽습니다. 이 섹션에서는 주어진 합계와 동일한 숫자의 모든 조합을 찾는 두 가지 방법을 보여줍니다.

사용자 정의 함수를 사용하여 주어진 합계와 동일한 숫자의 모든 조합을 가져옵니다.

주어진 값에 집합적으로 도달하는 특정 세트에서 가능한 모든 숫자 조합을 찾아내려면 아래에 설명된 사용자 정의 기능이 효과적인 도구 역할을 합니다.

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

1. 누르고있어. ALT + F11 Excel에서 키를 클릭하고 응용 프로그램 용 Microsoft Visual Basic 창.
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단계: 사용자 정의 수식을 입력하여 결과 얻기

코드를 붙여넣은 후 코드 창을 닫고 워크시트로 돌아갑니다. 빈 셀에 다음 수식을 입력하여 결과를 출력한 후 키를 누르세요. 엔터 버튼 모든 조합을 얻는 열쇠. 스크린샷 보기:

=MakeupANumber(A2:A10,B2)

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

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

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

앞서 언급한 기능의 한계를 고려하여 신속하고 포괄적인 솔루션인 Excel용 Kutools의 숫자 구성 기능을 권장합니다. 이 기능은 모든 Excel 버전과 호환됩니다. 이 대안은 양수, 소수 및 음수를 효과적으로 처리할 수 있습니다. 이 기능을 사용하면 주어진 합계와 동일한 모든 조합을 빠르게 얻을 수 있습니다.

1. 쿠툴 > 내용 > 번호 확인, 스크린 샷 참조 :
   
2. 그런 다음 번호 만들기 대화 상자를 클릭하십시오. 버튼에서 사용할 번호 목록을 선택합니다. 데이터 소스을 클릭 한 다음 총 수를 합계 텍스트 상자. 마지막으로 클릭 OK 버튼, 스크린 샷 참조 :
   
3. 그런 다음 결과를 찾을 셀을 선택하라는 메시지 상자가 나타나면 다음을 클릭하십시오. OK, 스크린 샷 참조 :
   
4. 이제 해당 숫자와 동일한 모든 조합이 아래 스크린샷과 같이 표시됩니다.
   

VBA 코드를 사용하여 범위에 합계가 있는 모든 숫자 조합 가져오기

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

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

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

1. 누르고있어. ALT + F11 Excel에서 키를 클릭하고 응용 프로그램 용 Microsoft Visual Basic 창.
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 키를 눌러 이 코드를 실행하고, 처음 팝업된 대화 상자에서 사용하려는 숫자 범위를 선택하고 OK. 스크린 샷보기 :
   
2. 두 번째 프롬프트 상자에서 하한 숫자를 선택하거나 입력하고 OK. 스크린 샷보기 :
   
3. 세 번째 프롬프트 상자에서 상한 숫자를 선택하거나 입력하고 OK. 스크린 샷보기 :
   
4. 마지막 프롬프트 상자에서 결과 출력이 시작되는 출력 셀을 선택합니다. 그런 다음 클릭 OK. 스크린 샷보기 :
   

결과

이제 각 적격 조합은 선택한 출력 셀부터 시작하여 워크시트의 연속 행에 나열됩니다.

Excel에서는 특정 합계를 합산하는 숫자 그룹을 찾는 여러 가지 방법을 제공하며, 각 방법은 다르게 작동하므로 Excel에 얼마나 익숙하고 프로젝트에 필요한지에 따라 하나를 선택할 수 있습니다. 더 많은 Excel 팁과 요령을 알아보고 싶다면 우리 웹사이트는 수천 개의 튜토리얼을 제공합니다. 읽어주셔서 감사합니다. 앞으로도 더 유용한 정보를 제공해 드릴 수 있도록 노력하겠습니다!

관련 기사: