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

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

Найдите комбинацию чисел, равную заданной сумме, с помощью функции Solver.

Получить все комбинации чисел, равные заданной сумме

• Используя функцию, определяемую пользователем
• Благодаря умной функции – Kutools for Excel

Получите все комбинации чисел, сумма которых находится в диапазоне, с помощью кода VBA.

Найдите комбинацию ячеек, равную заданной сумме, с помощью функции Solver

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

Шаг 1. Включите надстройку Solver

1. Пожалуйста, перейдите на Файл > Опции, В Параметры Excel диалоговое окно, нажмите Надстройки на левой панели, затем нажмите Go кнопка. Смотрите скриншот:
   
2. Затем Надстройки появится диалоговое окно, проверьте Надстройка Solver и нажмите OK для успешной установки этой надстройки.
   

Шаг 2. Введите формулу

После активации надстройки «Поиск решения» вам необходимо ввести в ячейку B11 такую ​​формулу:

=SUMPRODUCT(B2:B10,A2:A10)

Шаг 3. Настройте и запустите Solver, чтобы получить результат.

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

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

=MakeupANumber(A2:A10,B2)

Функции: Если вы хотите отобразить результаты комбинации вертикально в столбце, примените следующую формулу:

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

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

Учитывая ограничения вышеупомянутой функции, мы рекомендуем быстрое и комплексное решение: функцию Kutools for Excel «Составить число», которая совместима с любой версией 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, на нашем веб-сайте представлены тысячи учебных пособий. нажмите здесь, чтобы получить к ним доступ. Спасибо за внимание, и мы с нетерпением ждем возможности предоставить вам еще больше полезной информации в будущем!

Статьи по теме: