Очень часто в моем компе бывает куча альбомов, которые хочется быстро и максимально экономично записать на двд диск. То есть заполнить диск объемом 4488 Мб как можно плотнее, чтоб отклонение от этой цифры (остающее свободное место) не превышало 1-3 Мб. Я уже пытался выполнить операцию в Экселе с помощью опции "Поиск решения". Но она работает как-то нестабильно, один раз подбирает, в последующие раз нет. Да и при по попытке редактирования параметров все время вылезает ошибка, хотя в удачном подборе стоят вроде аналогичные цифры. В интернете есть утилита для данной цели SizeMe. Она хоть и выполняет задачу за секунду, но погрешность у нее составляет 1% (по умолчанию она не меняется), а это целых 45 Мб (свободного места на записываемом диске). Пока не могу найти универсального решения в Экселе, чтоб меняя каждый раз лишь размеры получать результат с минимальной погрешностью (устроит 4487 Мб). В прилагаемой таблице в колонке В приведены размеры альбомов, в колонках C и D формулы. Видимо, я неправильно проставляю значения в "Поиске решения" (но оно туго поддается редактированию). Тоже самое хотелось бы иметь готовый макрос (кстати, не знаю куда его вписывать). Могу показать и другие таблицы с которыми эксперементировал, но чтоб не было путаницы, прилагаю пока одну. Пожалуйста помогите решить задачу подбора слагаемых для 4488.
Была аналогичная задача.
Решал следующим образом:
1. Сортируешь все записи по убыванию размера файла
2. Пишешь формулу, которая тупо складывает размеры верхнего файла с текущим - протягиваешь по столбику - можно назвать этот столбец накопительным итогом
3. В другом столбце простыс ЕСЛИ проверяешь - насколько твой накопительный итог приблизился к нужно цифре.
У меня при таком подходе получался наиболее близкий к ожидаемому итог.
Shadowmaker, пожалуйста приведите это на прилагаемой мной таблице. Вся проблема как раз в формуле. Я ее правильно написал? Вы пользовались опцией Поиск решения?
Из сети я взял пример пользования функцией Поиск решения, которая как раз решает подобные задачи (см. вложение). Тут, нажав на "Выполнить", получиться число максимально приближенное к 8500000000. Однако, когда я подставляю свои цифры (размеры папок), то получается чушь. Проверьте, подставя размеры из первой таблицы (из моего прошлого поста).
=A1+ЕСЛИ((4487-A1-A2)>0;A2;0)+ЕСЛИ((4487-A1-A2-A3)>0;A3;0)
И так далее, и так для каждой ячейки
С уважением, ZS5
ZS5, вот эту формулу, объясните, в какую (-ие) ячейку(-и) вписать и как ее привести в действие, чтобы одним кликoм Эксель мне показал (выделил) слагаемые максимально приближенные к 4488 Мб? А с опцией "Поиск решения" вы не пробовали?
показать (выделить) слагаемые одним кликом: ячейка с суммой - опция формулы-влияющие ячейки
Цитата: zs от 24.10.2014, 19:44
показать (выделить) слагаемые одним кликом: ячейка с суммой - опция формулы-влияющие ячейки
Неправильно. Я ведь просил провести операцию именно с моей таблицей https://forum.msexcel.ru/index.php?action=dlattach;topic=10476.0;attach=14349 (https://forum.msexcel.ru/index.php?action=dlattach;topic=10476.0;attach=14349)
а вы дали лишь сухой результат с неба взятыми тремя цифрами. Я так и не понял, какой алгоритм выискивает слагаемые? Вставьте мои 51 цифр так чтобы результат (скажем 6 или 9 слагаемых) высветился в ячейках зеленым цветом.
Эта задача близка к задачи оптимизации линейного раскроя, самый простой способ - это решать жадным алгоритмом, но не всегда будет найден оптимальный результат.
решал собственным алгоритмом, получились следующие результаты:
=2735,1+452,1+413,7+308,8+162,5+152,3+140+123,5
=1097,1+1047,9+656,2+628,6+579+305,9+173,3
=1075,3+924,1+586,6+277,6+179,9+179,1+153,4+146,6+122,9+114,5+102,9+97,1+91,1+88,6+82,5+82,5+73,4+58,7+51,2
=586+103,1+94,4+73,6+52,7+46,7+43,6+38,4+30,4+30,3+28,5+27,7+27,6+25,9+24,1+17,4
Есть наработки по нахождению слагаемых под нужную сумму: http://www.excelworld.ru/forum/10-5196-1
"Жадный" алгоритм тоже неплохо справляется на этих данных
Цитата: MCH от 24.10.2014, 21:24
Эта задача близка к задачи оптимизации линейного раскроя,
[/i]
Мне нужен не сухой результат, а порядок действий в Экселе. А именно, какую формулу и в какую ячейку вписать, чтобы в считанные секунды получить выделенные цветом ячейки - результаты (вариантов с сложением слагаемых для получения 4488 может быть несколько). Я проверил действительно у вас 4 варианта получились четко - 4488. Но как одним кликом получить выделенные ячейки (может сделаете захват экрана и я посомтрю ваш видеоролик)?
MCH, у вас подборка к трем двд получилaсь отменно. Но мне нужно знать в какую ячейку какую формулу вписать, чтобы все 3 варианта (что получились у вас) выделились разными цветами? И главное, на какую кнопку вы нажимаете, чтоб получить слагаемые? Важно отметить, что каждый файл (точнее его размер) попав в двд1 вторично уже не может быть использован т.к. он уже будет отсуствовать в компьютере после их записи на диск.
Формулу решения "жадным" алгоритмом я привел выше, каждому файлу присваивается свой номер диска.
Чтобы распределить ровно по 4488 я использовал собственную программу по раскрою, которую делал под заказ (до коммерческой реализации не довел, поэтому файл не выкладываю)
Решение находится нажатием на одну кнопку: https://yadi.sk/i/RqsTOc_ccGMcr
Если решение одним кликом вы провели собственной программой, которую не хотите нам показать, то, не имея доступа к подобному решению задач, я не смогу этим пользоваться (в будущем). Поэтому данный вариент нахождения слагаемых для меня отпадает. Поэтому рассмотрим второй вариант - посредством опции Поиск решения. Если не затруднит, приведите решение с помощью опции Поиск решения.
Я не выложил файл не потому, что я жадный, а по тому что оно не совсем подходит как готовое решение.
Пример решения формулами "жадным" алгоритмом я привел в 8 посте.
Если хотите более точно распределять файлы по дискам, то можно адаптировать одно из решений из темы "Подбор слагаемых под нужную сумму": http://www.excelworld.ru/forum/10-5196-1
Подойдет макрос Слэна или динамическое программирование (но оно только для целочисленных данных)
Если будет время, то попробую сделать решение различными вариантами
MCH, благодарю за старания. Вот тут https://forum.msexcel.ru/index.php?topic=4293.0 (https://forum.msexcel.ru/index.php?topic=4293.0) случайно не наша тема обсуждается? Там тоже говорят о программе "Раскрой" и чуть ниже программка Optmize. Ее как-то можно адаптировать к моей задаче?
Кстати, вашим "Раскрой"-ем подборка слагаемых была выполнена четко. A это https://www.youtube.com/watch?v=OiiPXEJM2n8 (https://www.youtube.com/watch?v=OiiPXEJM2n8) или это https://www.youtube.com/watch?v=S-W_eNsRiZ0 (https://www.youtube.com/watch?v=S-W_eNsRiZ0) тоже не то? Могу я увидеть вашу программу Раскрой?
Цитироватьчуть ниже программка Optmize
С помощью данной программы можно решить задачу раскроя, есть еще CuttingLine, но они используют как правило целочисленные данные (ваши данные легко адаптируются, достаточно все числа умножить на 10)
ЦитироватьМогу я увидеть вашу программу Раскрой?
На текущий момент программа не доделана и использовать ее не очень удобно, но алгоритмы по подбору суммы, которые в ней используются можно адаптировать под текущую задачу (сделаю позже)
MCH, в программе CuttingLine я пока не разобрался и не знаю как вписать туда в столбик все размеры. Если вы уже пользовались CuttingLine, то покажите, как в ней вы решили-бы мою задачу? А вот это http://infostart.zircool.ru/public/284488/ (http://infostart.zircool.ru/public/284488/), вы не знаете, что такое? Я не смог ее взять.
Хорошая статья по теме тут http://www.planetaexcel.ru/techniques/11/179/ (http://www.planetaexcel.ru/techniques/11/179/)
Но мне это пока тяжело поддается.
Решение данной задачи в CuttingLine см. во вложении.
По подбору слагаемых под нужную сумму я же дал ссылку на свою реализация, там 4 варианта решения
MCH, в Cutting Line я не понял как импортировать одним кликом список - колонку А (Экселя) с размерами? Тут что, по одному надо вводить данные? Если можно, покажите всю процедуру на видеоролике.
В сети существует куча он лайн калькуляторов, но нигде я не видел опции подбора слагаемых под нужную сумму. То есть ввел колонку с цифрами, повариться и дает результат. Вам попадались такие?
Пример решения задачи для целых чисел
MCH, в вашей таблице "Задача раскроя2" конечно все четко получилось. Но мне нужен не готовый результат для одного раза, а шаблон заполнения строк в Cutting Line, чтобы я сам мог в будущем вписывать туда свои данные. Если здесь надо заносить каждую цифру по отдельности, то это страшное неудобство. Разве нельзя импортировать список всё сразу? Так или иначе, покажите мне процесс подбора в Раскрое на видеоролике. А то я не разобрался куда там вписывать данные. Чуть ранее я послал вам в ЛС письмо со второй таблицей. Похоже оно не дошло. Поэтому, привожу линк повторно http://rusfolder.com/42093505 (http://rusfolder.com/42093505) Это моя папка с программами, точнее размеры файлов (17 Гб). Это должно уместится на 4 двд.
Цитата: Exc_fun от 26.10.2014, 14:53Но мне нужен не готовый результат для одного раза
Так я приложил рабочий код, который распределяет любые числа под нужную сумму
Введите в D2 искомую сумму и нажмите кнопку
MCH, и снова я ничего не понял. У вас голая таблица (с моими цифрами), в которой ничего не выделено. Нажатие на кнопку Целые числа (кстати, почему мои цифры с запятыми стали округленными?) выдает мне такое окно
(http://i65.fastpic.ru/big/2014/1026/f8/e1f86a8717ca74099ddaec60c8f061f8.jpg) (http://fastpic.ru/)
То есть я не могу увидеть результаты. И что мне делать?
Цитата: Exc_fun от 26.10.2014, 15:31...что мне делать?
В окне предупреждения Excel, скрин которого Вы приложили, по русски написано что надо сделать. В чём ещё вопрос-то?
Ну так включите макросы: https://yadi.sk/i/QMtp8fSScJ8rV
PS: Ситуация напоминает разговор немого с глухим, я тут стараюсь коды различные пишу, ссылки на решения даю, а ТС даже не знает как запустить макрос
Дальнейшее развитие темы мне уже не интересно
Миш, ответ не верный. Нужно макросы подписать! ;D
MCH, с ума сойти. Чтобы задействовать макрос, надо пройти весь этот лабиринт с Безопасностью и прочие ненужные преграды. Хорошо. Кажется, я начинаю разбираться в этой головоломке. К вашей таблице в Экселе Cutting Line имеет отношение или это чисто экселевская процедура? В будущем (когда соберуться файлы для записи очередного двд) мне достаточно ввести размеры папок в колонку А и указать желаемую сумму в D3 и всё? И почему нельзя убрать сразу в итоговой колонке и в желаемой сумме лишний ноль 44880 (разве это большая проблема сразу указывать 4488 и тоже самое со слагаемыми)?
Только что я вставил в колонку А другие цифры (слагаемые размеров для сборки нового двд) и при нажатии на кнопку Целые числа получил шиш.
(http://i67.fastpic.ru/big/2014/1027/a6/ee9854497f505715f72f8df1d4a4f3a6.jpg) (http://fastpic.ru/)
Что и где надо поменять в формуле, чтобы подобные ошибки впредь я преодолевал? Создается впечатление, что данная таблица одноразовая и при подмене цифр выдает ошибку.
Да, еще я хотел попросить сдвинуть колонку А на В, чтобы в А я указывал (для удобства и быстрой ориентации) названия альбомов. Что-то надо поменять в формуле, или нет?
Хотелось-бы все-же получить ответ на свои вопросы. А то мне дают готовую таблицу. Когда пытаюсь подставить другие цифры, Эксель выдает ошибку (скрин см. выше).
Здравствуйте. Пример использования "Поиск решения" для подбора слагаемых, но необходим мозговой штурм для его доработки, потому что в случае "Поиска решения" он подбирает не целые, а потом доли, а доли по всем слагаемым и приходится вначале помогать опции "Поиску решения", расставляя, где очевидно 1. А вот, как заставить, "Поиск решения" расставлять автоматически сначала единички, а потом дробную часть числа-необходима консультация знатоков Excel.
С уважением, ZS5?
1. Ошибку выдает, потому что Вы используете нулевые значения слагаемых, обхода данного бага в коде нет.
2. Данный алгоритм по подбору чисел основан на динамическом программировании, в основе которого работа с целыми числами. Поэтому все Ваши числа я умножил на 10, для того чтобы можно было его использовать (об этом я писал в личке, если бы Вы были более внимательны, то не задавали бы эти вопросы).
Ссылку на другие алгоритмы по подбору слагаемых (перебором, случайной подборкой, динамическим программированием, макросом Слэна) я приводил дважды. Там есть примеры и в коде достаточно комментариев, чтобы в нем разобраться
3. CuttingLine не имеет никакого отношения к приложенному файлу xls с решением.
4. Если Вам нужно использовать вместо столбца A столбец B с числами, то поправить это не сложно, код макроса не закрыт, достаточно нажать Alt+F11 и сделать нужные поправки.
Если Вам нужно сделать все лично под Вас, то есть раздел:
https://forum.msexcel.ru/index.php/board,10.0.html
PS: Если не разобрались с CuttingLine, то есть еще небольшая программа lin_raskr: http://monobit.ru/software/lineynyy-raskroy/files
в ней есть возможность импортировать данные из текстового файла или xls файла, но она тоже работает с целыми числами.
zs и МСН, сейчас я торопляюсь (ухожу). Поэтому, отвечу вам обстоятельно после разбора предлогаемых вами вариантов попозже (может к вечеру). Но в любом случае сразу благодарю.
МСН (http://xn--l1acl), если не затруднит, сделайте пожалуйста мне маленькую поправку в таблице Раскрой3 - сдвиньте столбик А на одну ячейку вправо (на В) высводбодив колонку А для имени файла. Я буду импортировать в Эксель сразу 2 колонки. В А будут имена файлов в В - их размеры. В этом случае ваша формула не пострадает? Сам я не смогу поменять формулу т.к. мои познания в Экселе пока не на высоте.
Кстати, утилита lin_raskr не запускается http://i66.fastpic.ru/big/2014/1028/01/bc61e776d6345b4b6a8096c232d88001.jpg (http://i66.fastpic.ru/big/2014/1028/01/bc61e776d6345b4b6a8096c232d88001.jpg) Откуда мне взять недостающий файл, как пишется в Ошибке?
Сделал решение тремя разными алгоритмами (динамическим программированием, жадным алгоритмом и с использованием макроса Слэна)
Решение динамическим программированием работает с дробными числами (округление до 3 знаков после запятой)
MCH, благодарю за таблицу Раскрой4. Это просто шик!
Еще у меня часто бывают в проекте видеоклипы разного формата и разрешения, которые нужно превратить в формат DVD-Video, максимально плотно заполнив диск (4488 или 8151 Мб). Программа авторинга TMPGEnc Authoring Works 4 делает это с очень большой погрешностью. Так, выставив конечный размер 4488 Mb, я получаю в итоге 3 или 3,5 Гб. Конечно, тут большое значение имеет продолжительность клипов, как общая так и конкретной единицы, в которых и формат картинки, видео+аудио битрейт и многие другие параметры сильно разняться. Чем больше общая продолжительность клипов, тем погрешность на выходе меньше. Фактически, калькулятор должен учитывать все необходимые для вывода итогового видео параметры исходников (каждого в отдельности, точнее). Выдернуть необходимые параметры в текстовом виде с каждого клипа (или всех вместе) несложно с помощью утилиты Mediainfo. Интересно, Эксель сможет выполнить такую сложную задачу?
Я пытался таблицу Раскрой4 применить для несколько иной задачи - подобрать имеющиеся слагаемые (в колонке В - продолжительность песен в часовом формате) для получения итоговой (желаемой) суммы - 80. Это для записи на CD (продолжительность 80 минут). Вроде Эксель выдает результат, но в дробном виде. Мне же надо в часовом формате напр. 1:19:19. Как можно одним кликом получить желаемый ответ?
Видимо, надо правильно вести исходные данные.
01:53 Excel понимает как 1 час 53 минуты, а нужно 00:01:53 - 1 минута 53 секунды
Pelena, благодарю за подсказку. Да, у вас четко работает с предложенным форматом. Но моя программа mp-3 тэгов экспортирует в Эксель именно в том виде, что я показал, т.е. в минутах и секундах. Если файл большой и имеет продолжительность 1 час 15 минут, к примеру, то это отображается, как 75 минут. А нельзя переделать вашу таблицу так, чтобы исходные минуты и секунды оставались как есть, но в итоговой ячейке (желаемая сумма) сразу отображалoсь в виде часов - 1:20:00 И еще, желательно, чтоб отобранные слагаемые (под сумму 80 минут) в колонке В закрашивались автоматически разными цветами. То же самое хотелось-бы чтоб отобранные ячейки выделялись цветoм в таблице Раскрой4 (см. вложение чуть выше).
То, что вопросы связаны вашим заданием, не причина для того, чтобы все проблемы валить в одну тему. Не нужно, здесь обсуждение только подбора слагаемых.
Немного изменил макрос, чтобы время правильно считалось и округлялось до секунд
vikttur, как раз таки все задачи, которые я предлогаю, точь в точь подходят в открытую мной же тему "Подбор слагаемых под нужную сумму". Что вам не нравиться?
MCH, в принципе ваша таблица повторяет предыдущую (Подобрать песни на 80 минут). Я просил, чтобы исходное время (минуты и секунды, без часов) оставались как есть, но итоговая цифра (Желаемая сумма) была выражена в часовом формате, напр. 1:20:00 или 1:19:56, т.е. как вы указали. Главное - оставить исходники, напр. 3:43, 2:52, как есть. При преобразовании же чтоб получалось час:минута:секунда. Да и выделение цветом в колонке В не помешало бы. А как мне самому научиться редактировать макрос?
Оффтоп, но нужный.
Позже сообщение будет удалено, поэтому, если есть желание ответить на него - в личке.
Что не нравится. Не потому, что я так хочу.
Вы ответ получили и ушли. Тема остается. Приходят другие посетители. В Вашей теме будет часть о заливке ячеек, но вряд ли кто-то станет в поиске делать запрос о суммировании, если нужно только закрасить ячейки.
Вывод: часть темы для форума становится бесполезной.
Общепринятое правило форумов: "один вопрос - одна тема". Подразумевается, что вопросы разных тематик не нужно размещать в одной теме, даже если они связаны этой темой. Иначе можно до абсурда дойти: можно в теме об автомобилях спрашивать о составе воздуха (как же - шины-то качаем!) или о нефтевышках в Иране.
Вы пришли сюда за помощью - помогите и Вы другим, будущим посетителям.
vikttur, ну я же не отошел от темы, когда говорю, что хочу закрасить в макросе ячейки в задаче Экселя. Тема то Excel. Я же не в Фотошопе прошу покрасить...
Вы только про нефть прочитали? А Выше?
ЦитироватьПриходят другие посетители. В Вашей теме будет часть о заливке ячеек, но вряд ли кто-то станет в поиске делать запрос о суммировании, если нужно только закрасить ячейки.
Вывод: часть темы для форума становится бесполезной.
Пример с раскраской
MCH, выделение цветом конечно привлекательнее, благодарю. А вообще, где в макросе меняется цвет ячеек для данной задачи (таблицы)? Я-бы их сделал светлыми. Если можно, покажите это на видеоролике, чтоб я сам мог в будущем регулировать цвет.
Еще я просил, чтобы исходные цифры (напр. 3:42, 2:51, 1:22) оставались как есть. У вас они в часовом формате 00:03:42, 00:02:51, 00:01:22. Продолжительность песен экспортируется в колонку В Экселя из программы Mp3Tag в виде (формате) трех цифр, без указания часов. Указание часов должно быть (как у вас) только в Итоговой и желаемой сумме. Никак нельзя добавить такое автоизменение формата времени, чтобы я лишним кликом не менял это значение?
Цвета в макросе не задаются, раскрасте ячейки как Вам нравится, цвет будет переносится.
Исправлять время из вида ч.мм в мм.сс лучше отдельно а не в макросе, иначе задача получится не универсальной, подходящей под любой случай по нахождению суммы слагаемых, а только под Вашу непосредственную хотелку.
Чтобы преобразовать время в правильный формат, достаточно все значения разделить на 60
МСН, вообщем, у меня уже всё получается. Для интересу вытащил один двд, в котором 37 альбомов. В экспортном файле быстро получил перечень и продолжительность каждого альбома. И расчитал как можно скомпоновать их по 1:20:00, т.е на сколько CD записать. Колонки G-V все точно отображают. Однако, начиная с колонки W и далее отображение формата некорректное - в десятичном формате, всё поделено на 24 и путаешься. Можно-ли и последующие 100 колонок (начиная с W и далее) сделать тоже в часовом формате (как от G до V)?
Exc_fun, а сами формат ячеек на "Время" не можете поменять?
ShAM, умножать или делить ячейку на 24 я конечно могу. Но в моем случае речь идет о сотне ячеек, расположенных впритык и по вертикали и по горизонтали. Проще один раз написать макрос (что я не умею), чем ломать голову над грудой цифр от которых по все стороны нет свободных ячеек.
В Вашем файле из сообщения №45 выделяете ячейки (зажав Shift) G5:G1 (именно снизу вверх), зажав Shift, жмете End, не отпуская Shift, стрелку вправо. Выделятся 5 строк, от столбца G и до конца. Формат ячеек ставите "Время", смотрите результат.
После этого тыкаете любую из кнопок, формат будет нормально отображаться.
ЗЫ: Пока писал успел бы 10 раз эти операции выполнить :)
ЗЫЗЫ: Или я не в том трамвае?
ShAM, да, действительно просто. Просто зацепился за ячейку W1, потянул её на W2, далее "Заполнить только форматы" и потянул до самой крайней правой колонки АМ, и снова "Заполнить только форматы". И все корректно отображается в часовом формате.
Гы, все хорошо. Лишь бы лишние макросы не писать. :)
Можно-ли в таблице "Подобрать на DVD" закрасить ячейки, которые мы получаем при нажатии на кнопку "Желаемая суммa", как это сделано во второй таблице "Подобрать на 80 минут"? Причем, чтобы цвета ячеек в колонкаx А и В я мог сам предварительно устанoвить в ячейках G,H,I (и.т.д. правее), как в это возможно во второй прилагаемой таблице?
Здравствуйте...Прошу помощи в изменении кода во вложенных файлах....Задача раскроя нравиться...НО хочется изменить ввод данных таблицей...(размер-столько-то,кол-во столько-то). Примерный пример во вложении....Спасибо...
Тема о подборе слагаемых, но не о вводе данных.
может это нужно: http://www.excelworld.ru/forum/3-21304-1