Например, у меня есть следующий список чисел, и теперь я хочу знать, какая комбинация чисел в списке дает в сумме 480, на следующем скриншоте вы можете видеть там пять групп возможных комбинаций, которые в сумме равны 480, например 300 + 60 + 120, 300 + 60 + 40 + 80 и т. д. В этой статье я расскажу о некоторых методах, чтобы найти, какие ячейки дают в сумме определенное значение. в Excel.
- Найти комбинацию ячеек, равную заданной сумме, с помощью формул
- Найти комбинацию ячеек, которая равна заданной сумме, с помощью Solver Add -in
- Найти комбинацию ячеек, которая равна заданной сумме, с помощью функции, определяемой пользователем
- Найдите все комбинации, которые равны заданной сумме, с удивительной функцией
- Найдите ячейки комбинация, равная заданной сумме с формулами
- Найдите комбинацию ячеек, равную заданной сумме, с помощью надстройки Solver
- Найдите комбинацию ячеек, которая равна заданной сумме, с помощью функции, определяемой пользователем
- Найдите все комбинации, которые равны заданной сумме, с удивительной особенностью
- Демо: найдите комбинацию ячеек, которая равна заданной сумме в Excel
Найдите ячейки комбинация, равная заданной сумме с формулами
Во-первых, вам нужно создать так 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 и бесплатную пробную версию прямо сейчас!