Перейти к основному содержанию

Как найти в Excel все комбинации, равные заданной сумме?

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

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

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

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

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


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

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

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

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

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

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

=SUMPRODUCT(B2:B10,A2:A10)
Внимание: В этой формуле: 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)
Внимание: В этой формуле: A2: A10 это список номеров, и B2 это общая сумма, которую вы хотите получить.

Функции: Если вы хотите отобразить результаты комбинации вертикально в столбце, примените следующую формулу:
=TRANSPOSE(MakeupANumber(A2:A10,B2))
Ограничения этого метода:
  • Эта пользовательская функция работает только в Excel 365 и 2021.
  • Этот метод эффективен исключительно для положительных чисел; десятичные значения автоматически округляются до ближайшего целого числа, а отрицательные числа приведут к ошибкам.

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

Учитывая ограничения вышеупомянутой функции, мы рекомендуем быстрое и комплексное решение: функцию Kutools for Excel «Составить число», которая совместима с любой версией Excel. Эта альтернатива может эффективно обрабатывать положительные числа, десятичные и отрицательные числа. С помощью этой функции вы можете быстро получить все комбинации, равные заданной сумме.

Советы: Чтобы применить это Составить номер функция, во-первых, вам следует скачать Kutools for Excel, а затем быстро и легко примените эту функцию.
  1. Нажмите Кутулс > Содержание > Составить номер, см. снимок экрана:
  2. Затем в Составьте число диалоговое окно, щелкните кнопку, чтобы выбрать список номеров, который вы хотите использовать, из Источник данных, а затем введите общее число в Сумма текстовое окно. Наконец, нажмите OK кнопку, см. снимок экрана:
  3. Затем появится всплывающее окно с напоминанием о необходимости выбрать ячейку для поиска результата, а затем нажмите OK, см. снимок экрана:
  4. И теперь все комбинации, равные этому числу, отображаются, как показано ниже:
Внимание: Чтобы применить эту функцию, пожалуйста, скачайте и установите Kutools для Excel первый.

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


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

  • Перечислите или сгенерируйте все возможные комбинации
  • Скажем, у меня есть следующие два столбца данных, и теперь я хочу сгенерировать список всех возможных комбинаций на основе двух списков значений, как показано на скриншоте слева. Возможно, вы можете перечислить все комбинации одну за другой, если есть несколько значений, но, если есть несколько столбцов с несколькими значениями, которые необходимо перечислить, возможные комбинации, вот несколько быстрых приемов, которые могут помочь вам справиться с этой проблемой в Excel .
  • Создайте список всех возможных комбинаций из 4 цифр.
  • В некоторых случаях нам может потребоваться сгенерировать список всех возможных 4-значных комбинаций чисел от 0 до 9, что означает создание списка 0000, 0001, 0002… 9999. Чтобы быстро решить задачу списка в Excel, я предлагаю вам несколько уловок.