Найти возможные значения, сумма которых равна заданной сумме

Question

У меня есть лист А с двумя столбцами: имя и сумма. В листе BI есть список итоговых сумм. Каждая сумма на листе B является суммой некоторой комбинации сумм на листе A. Мне обычно приходится брать общую сумму на листе B и находить суммы (и названия) на листе A, которые составляют эту сумму. Мой процесс теперь включает фильтрацию столбца «Сумма» на листе А до сумм, меньших, чем сумма на листе В, и суммирование различных комбинаций, пока я не найду правильную. Это очень утомительно и требует много времени.

Есть лучший способ сделать это?

Answer 1

Предполагая, что комбинации состоят из двух сумм только из SheetA, есть довольно простой способ сделать это.

Создайте новый лист - SheetC. В столбце A вставьте столбец "Сумма" из SheetA, например, из A2:A50 (в зависимости от длины списка).

Используйте Paste Special - Transpose, чтобы снова вставить столбец "Количество" в SheetC, но транспонировать его так, чтобы столбец превратился в строку, например, из B1:AX1.

Теперь вы будете иметь все свои суммы в SheetC дважды: один раз в столбце A и снова в строке 1.

Теперь вы можете легко использовать все ячейки в таблице, чтобы добавить каждую комбинацию. Поместите в ячейку B2 формулу =$A2+C$1 , затем скопируйте ее в каждую ячейку диапазона, например, B2:AX50.

Как только это будет сделано, вы можете перейти в SheetB, где есть комбинации, скопировать сумму в буфер обмена. Затем вернитесь в SheetC и найдите (Ctrl+F), вводя эту сумму из буфера обмена. Excel найдет его для вас где-то в таблице, и вы сможете увидеть суммы, из которых он сделан, и легко определить, какие суммы в SheetA это.

Затем вы можете уточнить этот метод с помощью MATCH() в SheetB, чтобы автоматизировать поиск значения в SheetC, которое соответствует каждому значению в SheetB. Используя возвращенное число, вы можете получить Excel, чтобы получить имена каждой из составляющих сумм по формуле INDEX() .