Виявлення всіх можливих комбінацій чисел у списку, які в сумі дають певну суму, є проблемою, з якою можуть зіткнутися багато користувачів Excel під час складання бюджету, планування чи аналізу даних.

У цьому прикладі ми маємо список чисел, і мета полягає в тому, щоб визначити, які комбінації з цього списку дають суму 480. Наданий знімок екрана демонструє, що існує п’ять можливих груп комбінацій, які досягають цієї суми, включаючи такі комбінації, як 300+120 +60, 250+120+60+50 та інші. У цій статті ми розглянемо різні методи точного визначення конкретних комбінацій чисел у списку, які підсумовують визначене значення в Excel.

Знайдіть комбінацію чисел, що дорівнює заданій сумі, за допомогою функції Solver

Отримати всі комбінації чисел, що дорівнюють заданій сумі

• За допомогою функції, визначеної користувачем
• Розумною функцією – Kutools для Excel

Отримайте всі комбінації чисел, які мають суму в діапазоні, за допомогою коду VBA

Знайдіть комбінацію клітинок, яка дорівнює заданій сумі, за допомогою функції Розв’язування

Занурення в Excel для пошуку комбінацій клітинок, які в сумі дають певне число, може здатися складним, але надбудова Solver робить це легким. Ми розповімо вам прості кроки, щоб налаштувати Solver і знайти правильну комбінацію комірок, що зробить те, що здавалося складним завданням, простим і здійсненним.

Крок 1. Увімкніть надбудову Solver

1. Будь ласка, перейдіть на філе > Опції, В Параметри Excel діалогове вікно натисніть кнопку Add-Ins на лівій панелі, а потім натисніть Go кнопку. Дивіться знімок екрана:
   
2. Потім, Add-Ins з’явиться діалогове вікно, перевірте Надбудова 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 у стовпці B. Результати розв’язання діалогове вікно, будь ласка, виберіть Тримайте рішення для розв’язання і натисніть OK щоб вийти з діалогового вікна. Дивіться знімок екрана:
   

Отримати всі комбінації чисел, що дорівнюють заданій сумі

Вивчення глибших можливостей Excel дозволяє знаходити кожну комбінацію чисел, яка відповідає певній сумі, і це легше, ніж ви думаєте. Цей розділ покаже вам два методи пошуку всіх комбінацій чисел, що дорівнюють заданій сумі.

Отримайте всі комбінації чисел, що дорівнюють заданій сумі, за допомогою функції, визначеної користувачем

Щоб виявити кожну можливу комбінацію чисел із певного набору, які в сукупності досягають заданого значення, спеціальна функція, описана нижче, служить ефективним інструментом.

Крок 1. Відкрийте редактор модуля VBA та скопіюйте код

1. Утримуйте клавішу ALT + F11 клавіші в Excel, і він відкриває Microsoft Visual Basic для додатків вікна.
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. Введіть спеціальну формулу, щоб отримати результат

Після вставлення коду закрийте вікно коду, щоб повернутися до робочого аркуша. Введіть наступну формулу в порожню клітинку, щоб вивести результат, а потім натисніть Що натомість? Створіть віртуальну версію себе у ключ для отримання всіх комбінацій. Перегляньте скріншот:

=MakeupANumber(A2:A10,B2)

Чайові: Якщо ви хочете перерахувати результати комбінації вертикально в стовпці, застосуйте таку формулу:

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

Отримайте всі комбінації чисел, що дорівнюють заданій сумі, за допомогою потужної функції

Враховуючи обмеження вищезгаданої функції, ми рекомендуємо швидке та комплексне рішення: функцію «Створити число» Kutools для Excel, яка сумісна з будь-якою версією Excel. Ця альтернатива може ефективно обробляти додатні числа, десяткові та від’ємні числа. За допомогою цієї функції ви можете швидко отримати всі комбінації, які дорівнюють заданій сумі.

1. Натисніть Кутулс > зміст > Складіть номер, див. скріншот:
   
2. Потім у Складіть число діалогове вікно, натисніть для вибору списку номерів, який ви хочете використовувати, з Джерело даних, а потім введіть загальну кількість у Сума текстове вікно. Нарешті натисніть OK , див. знімок екрана:
   
3. Потім з’явиться вікно підказки, щоб нагадати вам вибрати клітинку, щоб знайти результат, а потім клацнути OK, див. скріншот:
   
4. І тепер усі комбінації, що дорівнюють цьому числу, відображаються, як показано на знімку екрана нижче:
   

Отримайте всі комбінації чисел, які мають суму в діапазоні, за допомогою коду VBA

Іноді ви можете опинитися в ситуації, коли вам потрібно визначити всі можливі комбінації чисел, які в сукупності дають суму в межах певного діапазону. Наприклад, ви можете шукати кожну можливу групу чисел, де загальна сума знаходиться між 470 і 480.

Виявлення всіх можливих комбінацій чисел, які підсумовують значення в межах певного діапазону, є захоплюючим і надзвичайно практичним завданням у Excel. У цьому розділі буде представлено код VBA для вирішення цього завдання.

Крок 1. Відкрийте редактор модуля VBA та скопіюйте код

1. Утримуйте клавішу ALT + F11 клавіші в Excel, і він відкриває Microsoft Visual Basic для додатків вікна.
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: Виконайте код

1. Вставивши код, натисніть F5 щоб запустити цей код, у першому діалоговому вікні, що з’явиться, виберіть діапазон чисел, які ви хочете використовувати, і натисніть OK. Дивіться знімок екрана:
   
2. У другому вікні підказки виберіть або введіть нижнє обмеження та клацніть OK. Дивіться знімок екрана:
   
3. У третьому вікні підказки виберіть або введіть максимальне значення та натисніть OK. Дивіться знімок екрана:
   
4. В останньому вікні підказки виберіть комірку виводу, з якої почнуть виводитися результати. Потім натисніть OK. Дивіться знімок екрана:
   

Результат

Тепер кожна кваліфікована комбінація буде перерахована в послідовних рядках на робочому аркуші, починаючи з вибраної клітинки виводу.

Excel пропонує кілька способів знайти групи чисел, які в сумі дають певну загальну суму. Кожен метод працює по-різному, тож ви можете вибрати один залежно від того, наскільки ви знайомі з Excel і що вам потрібно для вашого проекту. Якщо вам цікаво ознайомитися з іншими порадами та підказками щодо Excel, наш веб-сайт пропонує тисячі посібників. Дякуємо, що прочитали, і ми з нетерпінням чекаємо надати вам більше корисної інформації в майбутньому!

Статті по темі: