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

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

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

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

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

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

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

3 . В появившемся диалоговом окне Новое имя введите имя List1 в поле Имя и введите эту формулу = ROW (INDIRECT (“1:” & ROWS (Range1))) ( Range1 – это имя диапазона, созданное на шаге 1) в Относится к полю , см. Снимок экрана:

4 . Нажмите OK , чтобы вернуться в диалоговое окно Диспетчер имен , затем продолжайте нажимать кнопку New , чтобы создать другое имя диапазона в в диалоговом окне Новое имя введите имя List2 в поле Имя и введите эту формулу = ROW (INDIRECT (“1:” & 2 ^ ROWS (Range1))) ( Range1 – это имя диапазона, созданное на шаге 1) в Ссылается в поле , см. снимок экрана:

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 и – это имена диапазонов, которые вы создали на предыдущих этапах, C2 – это конкретное значение, к которому вы хотите добавить числа.
  • Если несколько комбинаций значений имеют сумму e qual к определенному значению, отображается только одна комбинация.

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

Kutools for Excel ‘s Утилита Make Up A Number может помочь вам быстро и легко найти и составить список всех комбинаций и конкретных комбинаций, которые равны заданному числу суммы. Нажмите, чтобы загрузить Kutools for Excel!

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


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

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

1. Сначала вам нужно активировать эту надстройку Solver , перейдите в Файл > Параметры , в диалоговом окне Параметры Excel щелкните Надстройки на левой панели, а затем щелкните Надстройка Solver из раздела Неактивные надстройки приложений , см. Снимок экрана:

. Затем нажмите кнопку Go , чтобы войти в диалоговое окно Add-Ins , отметьте опцию Solver Add-in и нажмите ОК , чтобы успешно установить эту надстройку.

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

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

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

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

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

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

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

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


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

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

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

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

2 . Нажмите Вставить > Module и вставьте следующий код в окно Module .

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

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

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

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

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

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

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

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

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

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

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

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


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


Оцените статью
Frestage.ru
Добавить комментарий