У меня есть столбец чисел (скажем, это A1:A100), и мне нужно найти подмножество их, которые составляют определенную сумму.
2 ответа
25
Это возможно с помощью надстройки Solver *. Следующие шаги работали для меня в Excel 2007 и 2010.
- Назначьте ячейку для хранения результата (C1 для этого примера) - это целевая ячейка и столбец, который Excel может использовать для работы с нуля (B1:B100 для этого примера)
- В целевой ячейке введите формулу "= SUMPRODUCT (A1:A100, B1:B100)" (без кавычек). Это вычислит сумму A1 * B1+A2 * B2+... и т.д.
- Выберите Открыть решатель (вкладка «Данные», группа «Анализ»)
- Целевая ячейка должна быть очевидной ($ C $ 1 для этого примера)
- Для «Равно»: выберите «Значение:» и введите желаемое значение.
- В поле «Путем изменения ячеек» введите «$ B $ 1: $ B $ 100» (без кавычек, и может потребоваться инициализировать эти значения равными 0).
- Добавьте ограничение к ячейкам, которые можно изменить. В выпадающем меню выберите «bin» (Binary). Это ограничивает значения этих ячеек 0 (удаление соответствующей ячейки A из суммы) или 1 (добавление соответствующей ячейки A к сумме).
- Нажмите «Решить» и подождите. Числа, которые являются частью искомого подмножества, будут иметь 1 в столбце B
Если решение занимает много времени, вы можете помочь, удалив строки, которые, очевидно, не будут работать (сумма указана в долларах, и только одна строка имеет ненулевые центы)
Бонус: вы можете сделать так, чтобы Excel автоматически выделил нужные ячейки, добавив к ним условное форматирование. Выберите все ячейки, которые вы хотите отформатировать, и в (вкладка «Главная») >> (группа «Стили») >> условное форматирование >> выберите «Новое правило» «Использовать формулу, чтобы определить ячейки для форматирования». В формуле введите '= $ B1 = 1' (без кавычек), что будет иметь значение true, если соответствующая строка в столбце B равна 1. Для формата вы можете добавить все, что захотите (жирный, курсив, зеленая заливка и т.д.).
Другой простой способ найти важные строки - это отсортировать столбец B Z-> A, и все 1 окажутся наверху.
* Надстройка решателя может быть установлена с этими шагами
- Нажмите кнопку Microsoft Office, а затем нажмите Параметры Excel.
- Нажмите Надстройки, а затем в поле Управление выберите Надстройки Excel.
- Нажмите Go.
- В поле Доступные надстройки установите флажок Надстройка Solver и нажмите кнопку ОК. (Если надстройка Solver отсутствует в списке доступных надстроек, нажмите кнопку «Обзор», чтобы найти надстройку.)
- Если появится сообщение о том, что надстройка Solver в данный момент не установлена на вашем компьютере, нажмите кнопку «Да», чтобы установить ее.
2
Существует недорогая надстройка Excel SumMatch, которая будет выделять подмножество чисел, которые в сумме составляют целевую сумму.
