Откриването на всички възможни комбинации от числа в рамките на списък, които дават конкретна сума, е предизвикателство, което много потребители на Excel могат да срещнат, независимо дали за целите на бюджетиране, планиране или анализ на данни.

В този пример имаме списък с числа и целта е да идентифицираме кои комбинации от този списък дават сумата 480. Предоставената екранна снимка показва, че има пет възможни групи комбинации, които постигат тази сума, включително комбинации като 300+120 +60, 250+120+60+50, между другото. В тази статия ще проучим различни методи за определяне на специфичните комбинации от числа в списък, които дават сумарна определена стойност в Excel.

Намерете комбинация от числа, равна на дадена сума с функцията Solver

Вземете всички комбинации от числа, равни на дадена сума

• Чрез използване на дефинирана от потребителя функция
• Чрез интелигентна функция – Kutools за Excel

Вземете всички комбинации от числа, които имат сума в диапазон с VBA код

Намерете комбинация от клетки, която е равна на дадена сума с функцията Solver

Гмуркането в Excel за намиране на клетъчни комбинации, които дават конкретен брой, може да изглежда обезсърчително, но добавката Solver го прави лесно. Ще ви преведем през простите стъпки, за да настроите Solver и да намерите правилната комбинация от клетки, правейки това, което изглежда като сложна задача, лесно и изпълнимо.

Стъпка 1: Активирайте добавката Solver

1. Моля, посетете досие > Настроики, В Опции на Excel кликнете върху Добавки от левия панел, след това щракнете Go бутон. Вижте екранна снимка:
   
2. Тогава Добавки се появява диалогов прозорец, проверете Добавка Solver и щракнете върху OK за да инсталирате тази добавка успешно.
   

Стъпка 2: Въведете формулата

След като активирате добавката Solver, трябва да въведете тази формула в клетка B11:

=SUMPRODUCT(B2:B10,A2:A10)

Стъпка 3: Конфигурирайте и стартирайте Solver, за да получите резултата

1. Кликнете Дата > Решител за да отидете на Параметър за решаване в диалоговия прозорец, моля, направете следните операции:
   • (1.) Щракнете бутон, за да изберете клетката B11 където се намира вашата формула от Задайте цел раздел;
   • (2.) След това в Да се раздел, изберете Стойност наи въведете целевата си стойност 480 колкото ви трябва;
   • (3.) Под Чрез промяна на променливи клетки раздел, моля щракнете бутон за избор на диапазон от клетки B2: B10 където ще отбележи съответните ви числа.
   • (4.) След това щракнете Добави бутон.
   • 
2. След това, ан Добавяне на ограничение се показва диалогов прозорец, щракнете бутон за избор на диапазон от клетки B2: B10, и изберете кофа за боклук от падащия списък. Най-накрая щракнете OK бутон. Вижте екранна снимка:
   
3. в Параметър за решаване диалогов прозорец, щракнете върху Решаване на бутон, няколко минути по-късно, a Резултати от решаването изскача диалогов прозорец и можете да видите комбинацията от клетки, които са равни на дадена сума 480, са маркирани като 1 в колона Б. В Резултати от решаването диалогов прозорец, моля изберете Запазете Solver Solution и щракнете върху 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))

Вземете всички комбинации от числа, равни на дадена сума с мощна функция

Като се имат предвид ограниченията на гореспоменатата функция, препоръчваме бързо и изчерпателно решение: Kutools за Excel's Make up a Number функция, която е съвместима с всяка версия на Excel. Тази алтернатива може ефективно да обработва положителни числа, десетични и отрицателни числа. С тази функция можете бързо да получите всички комбинации, които са равни на дадена сума.

1. Кликнете Kutools > съдържание > Измислете число, вижте екранна снимка:
   
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, моля, нашият уебсайт предлага хиляди уроци щракнете тук за достъп до тях. Благодарим ви, че прочетохте и очакваме с нетърпение да ви предоставим още полезна информация в бъдеще!

Свързани членове: