Совет. Другие языки - Google-Translated. Вы можете посетить English версия этой ссылки.
Войти
x
or
x
x
Регистрация
x

or

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

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


Найти и перечислить все комбинации, которые равны данной сумме, быстро и легко в Excel

Kutools for Excel's Составьте номер Утилита может помочь вам быстро и легко найти и перечислить все комбинации и конкретные комбинации, которые равны данной сумме. Нажмите, чтобы скачать Kutools для Excel!

Kutools for Excel: с более чем 300 удобными надстройками Excel, бесплатно попробовать без ограничений в 60-дни. Скачать и бесплатно пробную версию!


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

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

1, Выберите список номеров и определите в этом списке название диапазона - Range1 в Имя Box, и нажмите вводить чтобы завершить определение диапазона, см. снимок экрана:

2, После определения имени диапазона для списка номеров вам необходимо создать еще два имени диапазона в Менеджер имен поле, нажмите Формулы > Менеджер имен, В Менеджер имен диалоговое окно, нажмите Новинки , см. скриншоты:

3, В появившемся Новое имя диалоговое окно, введите имя List1 в Имя поле и введите эту формулу = СТРОКА (ДВССЫЛ ( "1:" & ЧСТРОК (Range1))) (Range1 это имя диапазона, которое вы создали в step1) в Относится к поле, см. снимок экрана:

4. Щелчок OK вернуться к Менеджер имен диалога, затем нажмите Новинки для создания другого имени диапазона в Новое имя диалоговое окно, введите имя List2 в Имя поле и введите эту формулу = СТРОКА (ДВССЫЛ ( "1:" & ^ ЧСТРОК 2 (Range1))) (Range1 это имя диапазона, которое вы создали в step1) в Относится к поле, см. снимок экрана:

5, После создания имен диапазонов, примените следующую формулу массива в ячейку B1:

=IF(ISNUMBER(MATCH(ROWS($1:1),IF(INDEX(MOD(INT((List2-1)/2^(TRANSPOSE(List1)-1)),2),MATCH(TRUE,MMULT(MOD(INT((List2-1)/2^(TRANSPOSE(List1)-1)),2),Range1)=$C$2,0),),TRANSPOSE(List1)),0)),"X",""), и нажмите Shift + Ctrl + Enter , а затем перетащите дескриптор заполнения в ячейку B8, последний номер списка, и вы увидите числа, общая сумма которых 480 помечены как X в столбце B, см. снимок экрана:

  • Заметки:
  • В приведенной выше длинной формуле: List1, List2 Range1 это имена диапазонов, которые вы создали в предыдущих шагах, C2 это конкретное значение, которое вы хотите добавить числа.
  • Если более чем одна комбинация значений имеет сумму, равную конкретному значению, указывается только одна комбинация.

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

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

1. Во-первых, вам нужно активировать эту решающее устройство добавить в, перейдите к филе > Опционы, В Параметры Excel диалоговое окно, нажмите Надстройки на левой панели, а затем нажмите Надстройка Solver из Неактивные надстройки приложений раздел, см. снимок экрана:

2, Затем нажмите Go для входа в Надстройки диалог, проверка Надстройка Solver и нажмите OK чтобы успешно установить эту надстройку.

3, После активации надстройки Solver вам необходимо ввести эту формулу в ячейку B9: = SUMPRODUCT (B2: B9, A2: A9)(B2: B9 представляет собой чистую ячейку столбца рядом с вашим списком номеров и A2: A9 это список номеров, который вы используете. ) и нажмите вводить ключ, см. снимок экрана:

4. Затем нажмите дата > решающее устройство , чтобы перейти к Параметр Solver диалоговое окно, в диалоговом окне выполните следующие действия:

(1.) Нажмите для выбора ячейки B10 где ваша формула из Задача цели раздел;

(2.) Тогда в к раздел, выберите Значение, и введите целевое значение 480 как вам нужно;

(3.) Под Изменение переменных ячеек раздел, нажмите для выбора диапазона ячеек B2: B9 где будут отмечены соответствующие номера.

5. Затем нажмите Добавить кнопку, чтобы перейти к Добавить ограничение диалоговое окно, нажмите для выбора диапазона ячеек B2: B9И выберите бункер в раскрывающемся списке см. снимок экрана:

6. Щелчок OK вернуться назад Параметр Solver диалога, затем нажмите Решить , через несколько минут, Результаты решения открывается диалоговое окно, и вы можете увидеть комбинацию ячеек, которые равны заданной сумме 480, помечены как 1. в Результаты решения диалога, выберите Храните решение Solver и нажмите OK для выхода из диалогового окна. Смотрите скриншот:

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


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

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

Чтобы получить правильный результат, сначала необходимо отсортировать список номеров в порядке убывания. Затем выполните следующие действия:

1, Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окна.

2. Щелчок Вставить > модуль, и вставьте следующий код в модуль Окно.

Код VBA: найдите комбинацию ячеек, которая равна заданной сумме:

Function GetCombination(CoinsRange As Range, SumCellId As Double) As String
'updateby Extendoffice
    Dim xStr As String
    Dim xSum As Double
    Dim xCell As Range
    xSum = SumCellId
    For Each xCell In CoinsRange
        If Not (xSum / xCell < 1) Then
            xStr = xStr & Int(xSum / xCell) & " of " & xCell & "  "
            xSum = xSum - (Int(xSum / xCell)) * xCell
        End If
    Next
    GetCombination = xStr
End Function

3, Затем сохраните и закройте это окно кода, а затем вернитесь на рабочий лист и введите эту формулу = Getcombination (A2: A9, C2) в пустую ячейку и нажмите вводить , вы получите следующий результат, который отображает номера комбинаций, которые равны данной сумме, см. снимок экрана:

  • Заметки:
  • В приведенной выше формуле, A2: A9 - это диапазон чисел и C2 содержит целевое значение, которое вы хотите равным.
  • Если более чем одна комбинация значений имеет сумму, равную конкретному значению, указывается только одна комбинация.

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

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

Советы:Чтобы применить это Составьте номер функция, во-первых, вы должны загрузить Kutools for Excel, а затем применить функцию быстро и легко.

После установки Kutools for Excel, сделайте следующее:

1. Щелчок Kutools > содержание > Составьте номер, см. снимок экрана:

2, Затем в Составить номер диалогового окна, нажмите кнопку, чтобы выбрать список номеров, которые вы хотите использовать из Источник данных, а затем введите общее количество в Сумма текстовое поле, см. снимок экрана:

3, Затем нажмите OK Кнопка, появится окно с предложением выбрать ячейку для поиска результата, см. скриншот:

4. Затем нажмите OKи теперь все комбинации, которые равны данному числу, были показаны, как показано на скриншоте ниже:

Нажмите, чтобы загрузить Kutools for Excel и бесплатную пробную версию.


Демо: найдите комбинацию ячеек, которая равна заданной сумме в Excel


  • Супер Формула Бар (легко редактировать несколько строк текста и формул); Макет чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон...
  • Объединить ячейки / строки / столбцы и хранение данных; Содержание сплит-клеток; Объедините дублирующиеся строки и сумму / среднее... предотвратить повторяющиеся клетки; Сравнить диапазоны...
  • Выберите Дубликат или Уникальный Ряды; Выберите пустые строки (все ячейки пусты); Супер найти и нечеткая находка во многих рабочих тетрадях; Случайный выбор ...
  • Точная копия Несколько ячеек без изменения формулы ссылки; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое ...
  • Любимые и быстро вставляемые формулы, Диапазоны, графики и рисунки; Шифрование ячеек с паролем; Создать список рассылки и отправлять электронные письма ...
  • Извлечение текстаДобавить текст, Удалить по позиции, Удалить пространство; Создание и печать промежуточных итогов подкачки; Преобразование содержимого ячеек и комментариев...
  • Суперфильтр (сохранить и применить схемы фильтров к другим листам); Расширенный поиск по месяцам / неделям / дням, частоте и более; Специальный фильтр жирным шрифтом, курсивом ...
  • Объединить рабочие тетради и рабочие листы; Объединение таблиц на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF...
  • Работает с Office 2007-2019 и 365 и поддерживает все языки. Это легко развернуть в вашей компании. Полнофункциональная 60-дневная бесплатная пробная версия.
вкладка kte 201905
  • Включить редактирование и чтение с вкладками в Word, Excel, PowerPoint, Издатель, Доступ, Visio и Проект.
  • Открывайте и создавайте несколько документов в новых вкладках одного и того же окна, а не в новых окнах.
  • Увеличивает вашу производительность на 50% и уменьшает сотни щелчков мышью для вас каждый день!
нижняя часть офиса
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Feroz · 2 months ago
    How do you do for the list of numbers like 480
  • To post as a guest, your comment is unpublished.
    Guilherme Dorn · 4 months ago
    Thank you very much! Resolved my problem correctly.
  • To post as a guest, your comment is unpublished.
    Miss Jones · 5 months ago
    Thank you so much for the VBA coding, it has solved a major headache trying to find combinations to equal an exact amount.
  • To post as a guest, your comment is unpublished.
    Stephanie · 6 months ago
    I have 1162 cells to find number x. Excel tells me that is too many variable cells. Very small data set! Any suggestions? Thanks!
  • To post as a guest, your comment is unpublished.
    a · 9 months ago
    Will the solver add-in not work if there are negative numbers in the list or if the value of number is 0? I'm trying to find a sum of numbers in a list that equate to zero with some numbers being negative and positive, but the solver does not work. I changed a couple numbers on my list to test to make sure I followed the steps correctly and it did work for the test. Please advise if there is a way to solve with negative and positive numbers to find a 0 value.
  • To post as a guest, your comment is unpublished.
    Fattir · 1 years ago
    Hello,
    Thanks this is very good,
    How can find the most approximate combinations if there is no exact value.
    Many thanks
  • To post as a guest, your comment is unpublished.
    Fattir · 1 years ago
    Hello,
    Many thanks for information;
    How can find the most approximate combinations if there is no exact value.
    Many thanks,
  • To post as a guest, your comment is unpublished.
    Igor Wilk · 1 years ago
    Would somebody know how to adjust the VBA Getcombination function so that no repetition should be allowed?

    For example, for numbers 1,2,3,4,5,13 if 14 is to be achieved than 1,13 is a solution, and not 14 of 1.
    • To post as a guest, your comment is unpublished.
      Ram · 1 years ago
      Function GetCombination(CoinsRange As Range, SumCellId As Double) As String
      'updateby Extendoffice 20160506
      Dim xStr As String
      Dim xSum As Double
      Dim xCell As Range
      xSum = SumCellId
      For Each xCell In CoinsRange
      If Not (xSum / xCell < 1) Then
      xStr = xStr & "1 of " & xCell & " "
      xSum = xSum - xCell
      End If
      Next
      GetCombination = xStr
      End Function
      • To post as a guest, your comment is unpublished.
        Shashanth · 6 months ago
        Hi Ram, this works fine but doesnot give the actual sum.
        EX: if i have 23,34,25,28,10,17&12 and i have a sum of 80(which is the sum of 23,28,17&12), I need a vba code which can find this combination (sum of 23,28,17&12) Can you please help me with this ?
      • To post as a guest, your comment is unpublished.
        ddddddd7 · 9 months ago
        hi it is giving me ambigious name error for the vba code
        any help cause i know nothing in VBA
  • To post as a guest, your comment is unpublished.
    alex · 1 years ago
    does anyone know if this works on google sheets
  • To post as a guest, your comment is unpublished.
    epp · 1 years ago
    Hi,

    My drouble with this formula is that it gives me one value for enough times to get the target value..
    In the list of different values there are some values which are equal to each other.

    E.g. I have 0,16 for 3 times(the first values in the list) and the formula gives me the answer that my target value is 593 of 0,16.

    Why does it not combine different values to get my target value? It only chooses one value and gives how many times it is to be the target value.

    Any help or idea?


    Thanks!
  • To post as a guest, your comment is unpublished.
    Dana · 1 years ago
    I am trying to determine the best blend of product and am unsure if this is the best way to do it. At most I use three products in a blend with 5 specifications each. All of the specifications are linear and can be averaged when blended. One blend is usually 45,000lbs and each batch is 30,000lbs. Most of the time our blends are 15k+30k but I would like to be able to calculate for the unusual blends using the increments all the way down to 2000lbs.
  • To post as a guest, your comment is unpublished.
    Lorena · 1 years ago
    The macro didn't work if there are more than one solution.
    Also, I didn't work if I find "0"
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello,Lorena,
      Before applying the above VBA code, you must sort the number list in descending order first.
      Second, the code is not work correctly to get the total number 0.
      Hope it can help you, thank you!
  • To post as a guest, your comment is unpublished.
    laura · 1 years ago
    Could you upload the excel?
  • To post as a guest, your comment is unpublished.
    Ruchir · 1 years ago
    Brilliant!!!
  • To post as a guest, your comment is unpublished.
    LL · 1 years ago
    I was able to get the example with Range1 to work with my range in 12 rows, but when I changed the range to 42 rows it did not work. I even restarted the entire process with the 42 row version and that didn't work either. Any ideas?
  • To post as a guest, your comment is unpublished.
    WL · 1 years ago
    HI, I downloaded Kutools but cannot get it to find all the combos less than a specified total.
  • To post as a guest, your comment is unpublished.
    Dori · 2 years ago
    Hi. The formula version didn't work for me either. It feels like it is missing a step. I do not see where the number specified in cell C2 comes into the formula.

    Thanks
    • To post as a guest, your comment is unpublished.
      skyyang · 2 years ago
      Hello, Dori,


      There is no formula in C2, it is just the specific value that you want numbers added up to.
  • To post as a guest, your comment is unpublished.
    tarra · 2 years ago
    how if i need more than one combination? thank you
  • To post as a guest, your comment is unpublished.
    DJ · 2 years ago
    I'm at best a advanced beginner at Excel. I tried everything and it didn't work. What could I be doing wrong?
  • To post as a guest, your comment is unpublished.
    Alan · 2 years ago
    Awesome. Couldn't get the large formula to work but the solver add-in worked perfectly. Saved me so much work.
  • To post as a guest, your comment is unpublished.
    Rick · 2 years ago
    Is there a way to expand the range as Thom says, to say up to 50 numbers, but to also only total six of the numbers out of the range that sum to the specified total? Currently it will provide all combinations that total to the specified total.

    thanks
  • To post as a guest, your comment is unpublished.
    nitin · 2 years ago
    Superb Man!!! Superb Man!!!
  • To post as a guest, your comment is unpublished.
    Thom · 2 years ago
    Is there a way to expand the range so that it includes more than 8 numbers? Also, I'm not sure how this function is working: "=ROW(INDIRECT("1:"&2^ROWS(Range1)))". If I try to expand "Range1" beyond 15 rows, I get an #Ref error. It works great with just the 8 numbers, but what if you wanted to include, say, 50 numbers or even 100.