☰

Excel: Найдите подмножество чисел, которые в сумме дают заданную сумму?

tag-icon • tag-icon microsoft-excel microsoft-office microsoft-excel-2007
Excel: Найдите подмножество чисел, которые в сумме дают заданную сумму?

У меня есть столбец чисел (допустим, A1:A100), и мне нужно найти подмножество, сумма которых равна определенной сумме.

решение1

Это возможно с надстройкой Solver*. Следующие шаги сработали для меня в Excel 2007 и 2010.

  1. Назначьте ячейку для хранения результата (C1 в этом примере) — это целевая ячейка, а также столбец, который Excel может использовать для черновой обработки (B1:B100 в этом примере).
  2. В целевой ячейке введите формулу "=SUMPRODUCT(A1:A100,B1:B100)" (без кавычек). Это вычислит сумму A1*B1+A2*B2+... и т. д.
  3. Выберите «Открыть решатель» (вкладка «Данные», группа «Анализ»).
  4. Целевая ячейка должна быть очевидна ($C$1 для этого примера)
  5. Для «Равно:» выберите «Значение:» и введите желаемое значение.
  6. В поле «Изменение ячеек» введите «$B$1:$B$100» (без кавычек, возможно, вам придется самостоятельно инициализировать эти значения до 0)
  7. Добавьте ограничение к ячейкам, которые можно изменять. В раскрывающемся списке выберите 'bin' (двоичный). Это ограничит значения этих ячеек до 0 (удаление соответствующей ячейки A из суммы) или 1 (добавление соответствующей ячейки A к сумме).
  8. Нажмите «Решить» и подождите. Числа, которые являются частью подмножества, которое вы ищете, будут иметь 1 в столбце B

Пример

Если решатель работает слишком долго, вы можете помочь ему, удалив строки, которые явно не будут работать (общая сумма указана в долларах, и только в одной строке есть ненулевые центы).

Бонус: Вы можете заставить Excel автоматически выделять ячейки, которые вы ищете, добавив к этим ячейкам условное форматирование. Выберите все ячейки, которые вы хотите отформатировать, и из (вкладки «Главная»)>>(группа «Стили»)>>Условное форматирование>>Новое правило выберите «Использовать формулу для определения ячеек для форматирования». В формуле введите «=$B1=1» (без кавычек), что будет оцениваться как «истина», если соответствующая строка в столбце B равна 1. Для формата вы можете добавить все, что захотите (жирный, курсив, зеленая заливка и т. д.).

Еще один простой способ найти важные строки — отсортировать столбец B Z->A, и все единицы окажутся наверху.

*Надстройку решателя можно установить, выполнив следующие действия.

  1. Нажмите кнопку Microsoft Office, а затем нажмите Параметры Excel.
  2. Нажмите «Надстройки», а затем в поле «Управление» выберите «Надстройки Excel».
  3. Нажмите «Перейти».
  4. В поле «Доступные надстройки» установите флажок «Надстройка Solver» и нажмите кнопку «ОК». (Если надстройка Solver отсутствует в поле «Доступные надстройки», нажмите кнопку «Обзор», чтобы найти надстройку.)
  5. Если появится сообщение о том, что надстройка Solver в данный момент не установлена ​​на вашем компьютере, нажмите «Да», чтобы установить ее.

решение2

Существует недорогая надстройка ExcelSumMatch, который выделит подмножество чисел, которые в сумме дают целевую сумму.

введите описание изображения здесь

Связанный контент