10

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

2 ответа2

25

Это возможно с помощью надстройки 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» (Binary). Это ограничивает значения этих ячеек 0 (удаление соответствующей ячейки A из суммы) или 1 (добавление соответствующей ячейки A к сумме).
  8. Нажмите «Решить» и подождите. Числа, которые являются частью искомого подмножества, будут иметь 1 в столбце B

пример


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


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

Другой простой способ найти важные строки - это отсортировать столбец B Z-> A, и все 1 окажутся наверху.


* Надстройка решателя может быть установлена с этими шагами

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

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

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .