Страницы: 1
RSS
Как найти в массиве данных значения сумма которых равна или максимально близка к нужной мне сумме.
 
Всем доброго времени суток.
В колонке А ряд значений. Среди всех этих значений мне нужно найти такие значениЯ(в любом количестве больше 1)  сумма которых совпадала бы, а если совпадений нет, то  была бы максимально близкой к нужной мне сумме(числу). В файле пример в котором нужное мне число равно 545. В колонке А выделены желтым ячейки сумма значений которых совпадает с 545 и выделены голубым ячейки сумма которых максимально близка к 545. ВОПРОС При помощи какой формулы можно  найти (выделить цветом или любым числом или символом ) ячейки сумма значений которых составляет нужную мне величину ,или максимально близка к ней. Если точных совпадений будет несколько, то для меня не имеет значение , какое из них найдет программа. Если максимально близких значений одинаковой величины будет так же несколько, то для меня не имеет значение , какое из них найдет программа.
Спасибо за помощь.
Изменено: solnce60 - 03.07.2021 05:59:40
 
solnce60, я вижу решение формулами через один из двух доп. листов.(см. файл)

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
solnce60, здравствуйте
Ищите темы на сайте по названию "Подбор суммы" и посмотрите инструмент "Поиск решения"
Есть и готовые быстрые решения в надстройке
Изменено: Jack Famous - 03.07.2021 10:19:34
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Спасибо за ценный совет.

JayBhagavan, спасибо большое за помощь. Получается, что для  решения моей задачи нужно задействовать количество колонок почти равное количеству строк. Не уверен, что мне это будет  удобно, ведь количество строк у меня может превышать и 1000 и даже 10 000. Может быть есть какой то метод ,не задействующий такое большое количество колонок.

Еще раз спасибо за помощь.
Изменено: vikttur - 03.07.2021 12:30:15
 
Вариант решения формулой. Считает только из 17 значений. На большее не хватает ресурсов Excel.
Код
=ПРАВБ(НАИМЕНЬШИЙ(ABS(МУМНОЖ(ОСТАТ(ЦЕЛОЕ(СТРОКА(1:131072)/2^(СТОЛБЕЦ(A:Q)-1));2);Ч(+A1:A17))-I1)*10^6+СТРОКА(1:131072);C2);6)
 
Светлый,
Спасибо большое за ценную информацию. Досадно.... думал моя задача для эксперта по эксель  решается легко и просто.... А оказалось , что все дело в технических ограничениях программы...
 
Если хватит ресурсов (у меня не хватает), то массивная формула подбирает из 32 чисел:
Код
=ПРАВБ(МИН(ABS(МУМНОЖ(ОСТАТ(ЦЕЛОЕ(СТРОКА(1:65536)/2^(СТОЛБЕЦ(A:P)-1));2);Ч(+A1:A16))+ТРАНСП(МУМНОЖ(ОСТАТ(ЦЕЛОЕ((СТРОКА(1:65536)-1)/2^(СТОЛБЕЦ(A:P)-1));2);Ч(+A17:A32)))-I1)*10^10+СТРОКА(1:65536)+ТРАНСП(СТРОКА(1:65536)-1)*65536);10)
 
Светлый,
Если под ресурсами Вы имеете в виду процессор, то у меня 4 ядерный .
Intel® Core™ i5-6400 CPU @
2.70GHz   2.70 GHz
8,00 ГБ (доступно: 7,70 ГБ)
64-разрядная операционная система, процессор x64
И мне нужно обрабатывать хотя бы  минимум 100 - 150  значений. Если не трудно сделайте мне формулу на 150 значений и я попробую. Спасибо.
Изменено: solnce60 - 03.07.2021 11:49:25
 
Цитата
solnce60 написал:
хотя бы  минимум 100 - 150
Технически данный алгоритм не даст использовать более 50 значений.
Такие задачи только макросом.
 
Цитата

Цитата
Спасибо большое за помощь. В большинстве случаев мне будет достаточно и 50 значений. Ваш вариант решения намного компактнее предыдущего варианта, но в нем  очень много формул. Естественно я не способен понять даже их малую часть. Но я пойму если Вы мне объясните, а лучше покажете в каком месте и в каких формулах нужно менять значение под которое мне нужно подогнать сумму значений ячеек. Например если мне нужно не 545, а 1249 , или какое то другое число. Если я научусь подставлять нужное мне значение  тогда я буду использовать Ваш файл с решением моей задачи как шаблон в котором буду только менять значение под которое мне нужно подгонять суммы значений ячеек. И еще если Вам не трудно, сделайте мне пожалуйста шаблон на 50 значений.
Спасибо еще раз за помощь.
Изменено: solnce60 - 03.07.2021 20:32:59
 
Цитата
solnce60 написал:
моя задача для эксперта по эксель  решается легко и просто
Есть шанс прославиться.  :)

Начните с темы Михаила (МСН) здесь.
Изменено: sokol92 - 03.07.2021 18:05:23
Владимир
 
Цитата
Если Вы советуете мне прославится путем решения задачи о рюкзаке.... то я бы с удовольствием потешил свое самолюбие таким образом....... но увы .... у меня наверное какие то когнитивные отклонения и поэтому  совсем не математический склад ума. Единственно на что его пока хватает... это на понимание некоторых элементарных понятий  екселя и применения их на практике.
Спасибо за высокую оценку моих умственных способностей. :D  
Изменено: solnce60 - 03.07.2021 20:32:37
 
Цитата
sokol92
Оптимальным решение моей проблемы мне видится
1. Вы создадите мне шаблон на 50 значений ( если конечно , это Вас не затруднит)
2. Покажете где и что нужно менять, что бы менять значение под которое нужно подгонять суммы значений ячеек.
Спасибо Вам большое за помощь.
Изменено: solnce60 - 03.07.2021 20:33:53
 
solnce60, вернитесь, приведите сами свои сообщения в порядок. Цитирование - действие, при котором тоже надо думать.
 
Создавать примеры - дело автора вопроса.

Скачайте файл из сообщения 6 указанной выше ссылки.
С Вашими данными из #1 макрос справляется за 0,008 секунды (лист "Ограниченный перебор"). Меняете столбец "A" листа на свои данные и нажимаете на кнопку "Нажми".
Владимир
 
sokol92,
Спасибо
 
А сообщения кто будет исправлять?
 
vikttur,
Убрал цитирование где смог и как смог. Спасибо за ценные замечания.
 
Цитата
solnce60 написал:
в каком месте и в каких формулах нужно менять значение
В ячейке I1 указываем сумму. Зелёным подсвечены суммируемые значения. В C2 можно выбрать вариант решения.
Максимально добился 19 значений в формуле.
Поэкспериментировал. Пользуйтесь решениями с макросами.
 
Светлый,
Спасибо за ценную информацию
Страницы: 1
Наверх