Подбор слагаемых для нужной суммы excel
Обновлено: 07.07.2024
Предположим, что вам нужно свести значения с более чем одним условием, например суммой продаж продуктов в определенном регионе. Это хороший случай для использования функции СУММЕСС в формуле.
Взгляните на этот пример, в котором есть два условия: мы хотим получить сумму продаж "Мясо" (из столбца C) в регионе "Южный" (из столбца A).
Вот формула, с помощью которая можно сопровождать эту формулу:
=СУММЕСС(D2:D11,A2:A11,"Южный",C2:C11,"Мясо")
Результат — значение 14 719.
Рассмотрим каждую часть формулы более подробно.
=СУММЕСЛИМН — это арифметическая формула. Она вычисляет числа, которые в этом случае находятся в столбце D. Прежде всего нужно указать расположение чисел.
=СУММЕСЛИМН(D2:D11,
Другими словами, вы хотите, чтобы формула суммировала числа в этом столбце, если они соответствуют определенным условиям. Это диапазон ячеок является первым аргументом в этой формуле — первым элементом данных, который требуется функции в качестве входных данных.
Затем вам нужно найти данные, отвечающие двум условиям, поэтому введите первое условие, указав для функции расположение данных (A2:A11) и условие ("Южный"). Обратите внимание на запятую между аргументами:
=СУММЕСЛИМН(D2:D11;A2:A11;"Южный";
Кавычка вокруг текста "Южный" указывает на то, что это текстовые данные.
Наконец, вы вводите аргументы для второго условия — диапазон ячеек (C2:C11), которые содержат слово "Мясо", а также само слово (заключенное в кавычки), чтобы приложение Excel смогло их сопоставить. В конце формулы введите закрываю скобки) и нажмите ввод. Результат — 14 719.
=СУММЕСЛИМН(D2:D11;A2:A11,"Южный";C2:C11,"Мясо")
Если вы ввели в Excel функцию СУММЕСС, если вы не помните аргументов, справка готова. После того как вы введете =СУММЕСС(, под формулой появится автозавершенная формула со списком аргументов в правильном порядке.
На изображении автозавершена формулы и списке аргументов в нашем примере sum_range — D2:D11, столбец чисел, которые нужно свести; criteria_range1 — A2. A11 — столбец данных, в котором находится "Южный" (критерий1).
По мере того, как вы вводите формулу, в автозавершении формулы появятся остальные аргументы (здесь они не показаны); диапазон_условия2 — это диапазон C2:C11, представляющий собой столбец с данными, в котором находится условие2 — “Мясо”.
Если вы нажмете кнопку СУММЕСС в автозавершении формул, откроется статья с дополнительной справкой.
Попробуйте попрактиковаться
Если вы хотите поэкспериментировать с функцией СУММЕСС, вот примеры данных и формула, в которую она используется.
Вы можете работать с образцами данных и формулами прямо в этой Excel в Интернете книге. Изменяйте значения и формулы или добавляйте свои собственные, чтобы увидеть, как мгновенно изменятся результаты.
Скопируйте все ячейки из приведенной ниже таблицы и вставьте их в ячейку A1 нового листа Excel. Вы можете отрегулировать ширину столбцов, чтобы формулы лучше отображались.
Это частный случай "задачи о рюкзаке", т.к. необходимо найти не оптимальный перечень слагаемых, а любую возможную комбинацию, удовлетворяющую заданным условиям. Думаю, что задача может иметь практическое применение.
У меня были некоторые наработки на эту тему, решил их доработать и опубликовать, а также хочется получить дополнительные материалы по эффективным алгоритмам.
Считаю, что данную задачу нужно решать с помошью макросов (все решения через поиск решения или формульные варианты не рассматриваю).
На текущий момент, самый быстрый способ решения, который мне попадался - это "макрос Слэна", но у него есть недостаток, решение не всегда может быть найдено. Как именно работает макрос я не разобрался, но думаю, что в основе лежит "жадный алгоритм" с перебором.
Если есть ссылки на хорошие алгоритмы или реализации, прошу поделиться
Прилагаю файл с разными решениями:
1. Случайная выборка, не самый лучший вариант, но может и он сгодиться
2. С применением динамического программирования. Сумма находится по целочисленным слагаемым, работает относительно быстро и если может быть решение - оно будет найдено. Скорость, а также объем выделяемой памяти сильно зависят от искомой суммы, в макросе введено ограничение - сумма не должна превышать 80 000 000. Подходит для решения дробных сумм, например, рубли с копейками, достаточно все суммы умножить на 100.
3. Преребор (brute force), возможно указать ограничения по количеству слагаемых, а также отсекает неоптимальные ветви решения, что позволило сделать приемлемый по скорости перебор, находит все решения подходящие под условия. Но для большого количества слагаемых метод не применим
4. Макрос Слэна, хороший быстрый алгоритм, но не всегда находит решение.
Реализация не окончательная, поэтому в готовые решеня не размещаю.
Хочется найти еще эффективные и быстрые варианты решения. Например, можно совместить частичный перебор с динамикой
UPD 27.02.2014: Обновлен алгоритм с перебором, стал существенно быстрее работать
Это частный случай "задачи о рюкзаке", т.к. необходимо найти не оптимальный перечень слагаемых, а любую возможную комбинацию, удовлетворяющую заданным условиям. Думаю, что задача может иметь практическое применение.
У меня были некоторые наработки на эту тему, решил их доработать и опубликовать, а также хочется получить дополнительные материалы по эффективным алгоритмам.
Считаю, что данную задачу нужно решать с помошью макросов (все решения через поиск решения или формульные варианты не рассматриваю).
На текущий момент, самый быстрый способ решения, который мне попадался - это "макрос Слэна", но у него есть недостаток, решение не всегда может быть найдено. Как именно работает макрос я не разобрался, но думаю, что в основе лежит "жадный алгоритм" с перебором.
Если есть ссылки на хорошие алгоритмы или реализации, прошу поделиться
Прилагаю файл с разными решениями:
1. Случайная выборка, не самый лучший вариант, но может и он сгодиться
2. С применением динамического программирования. Сумма находится по целочисленным слагаемым, работает относительно быстро и если может быть решение - оно будет найдено. Скорость, а также объем выделяемой памяти сильно зависят от искомой суммы, в макросе введено ограничение - сумма не должна превышать 80 000 000. Подходит для решения дробных сумм, например, рубли с копейками, достаточно все суммы умножить на 100.
3. Преребор (brute force), возможно указать ограничения по количеству слагаемых, а также отсекает неоптимальные ветви решения, что позволило сделать приемлемый по скорости перебор, находит все решения подходящие под условия. Но для большого количества слагаемых метод не применим
4. Макрос Слэна, хороший быстрый алгоритм, но не всегда находит решение.
Реализация не окончательная, поэтому в готовые решеня не размещаю.
Хочется найти еще эффективные и быстрые варианты решения. Например, можно совместить частичный перебор с динамикой
UPD 27.02.2014: Обновлен алгоритм с перебором, стал существенно быстрее работать MCH
Это частный случай "задачи о рюкзаке", т.к. необходимо найти не оптимальный перечень слагаемых, а любую возможную комбинацию, удовлетворяющую заданным условиям. Думаю, что задача может иметь практическое применение.
У меня были некоторые наработки на эту тему, решил их доработать и опубликовать, а также хочется получить дополнительные материалы по эффективным алгоритмам.
Считаю, что данную задачу нужно решать с помошью макросов (все решения через поиск решения или формульные варианты не рассматриваю).
На текущий момент, самый быстрый способ решения, который мне попадался - это "макрос Слэна", но у него есть недостаток, решение не всегда может быть найдено. Как именно работает макрос я не разобрался, но думаю, что в основе лежит "жадный алгоритм" с перебором.
Если есть ссылки на хорошие алгоритмы или реализации, прошу поделиться
Прилагаю файл с разными решениями:
1. Случайная выборка, не самый лучший вариант, но может и он сгодиться
2. С применением динамического программирования. Сумма находится по целочисленным слагаемым, работает относительно быстро и если может быть решение - оно будет найдено. Скорость, а также объем выделяемой памяти сильно зависят от искомой суммы, в макросе введено ограничение - сумма не должна превышать 80 000 000. Подходит для решения дробных сумм, например, рубли с копейками, достаточно все суммы умножить на 100.
3. Преребор (brute force), возможно указать ограничения по количеству слагаемых, а также отсекает неоптимальные ветви решения, что позволило сделать приемлемый по скорости перебор, находит все решения подходящие под условия. Но для большого количества слагаемых метод не применим
4. Макрос Слэна, хороший быстрый алгоритм, но не всегда находит решение.
Реализация не окончательная, поэтому в готовые решеня не размещаю.
Хочется найти еще эффективные и быстрые варианты решения. Например, можно совместить частичный перебор с динамикой
UPD 27.02.2014: Обновлен алгоритм с перебором, стал существенно быстрее работать Автор - MCH
Дата добавления - 25.06.2013 в 01:15
Например, можно использовать в ситуации, когда вам нужно найти вариант из каких различных чисел могла сложиться определнная сумма (может вы ищете из каких счетов могла сложиться сумма оплаты). Допустим, нужно найти по приведенным числам сумму 10:
Для начал включим надстройку или проверим, что она включена (в Excel 2013): Файл / Параметры, раздел Надстройки, выбрать Управление: Надстройки Excel, нажать Перейти. Отметить флагом Поиск решения, нажать ОК
На ленте на вкладке Данные появился Поиск решения:
Теперь запускаем Поиск решения. И заполняем:
Не очень частый, но и не экзотический случай. На моих тренингах такой вопрос задавали не один и не два раза 🙂 Суть в том, что мы имеем конечный набор каких-то чисел, из которых надо выбрать те, что дадут в сумме заданное значение.
В реальной жизни эта задача может выглядеть по-разному.
В некоторых случаях может быть известна разрешенная погрешность допуска. Она может быть как нулевой (в случае подбора счетов), так и ненулевой (в случае подбора рулонов), или ограниченной снизу или сверху (в случае блэкджека).
Давайте рассмотрим несколько способов решения такой задачи в Excel.
Способ 1. Надстройка Поиск решения (Solver)
Эта надстройка входит в стандартный набор пакета Microsoft Office вместе с Excel и предназначена, в общем случае, для решения линейных и нелинейных задач оптимизации при наличии списка ограничений. Чтобы ее подключить, необходимо:
и установить соответствующий флажок. Тогда на вкладке или в меню Данные (Data) появится нужная нам команда.
Чтобы использовать надстройку Поиск решения для нашей задачи необходимо будет слегка модернизировать наш пример, добавив к списку подбираемых сумм несколько вспомогательных ячеек и формул:
После ввода формулы ее необходимо ввести не как обычную формулу, а как формулу массива, т.е. нажать не Enter, а Ctrl+Shift+Enter. Похожая формула используется в примере о ВПР, выдающей сразу все найденные значения (а не только первое).
В открывшемся окне необходимо:
С помощью той же кнопки, при необходимости, создать ограничение на количество чисел в выборке. Например, если мы знаем, что сумма была разбита на 5 счетов, то:
После ввода всех параметров и ограничений запускаем процесс подбора кнопкой Найти решение (Solve). Процесс подбора занимает от нескольких секунд до нескольких минут (в тяжелых случаях) и заканчивается появлением следующего окна:
Теперь можно либо оставить найденное решение подбора (Сохранить найденное решение), либо откатиться к прежним значениям (Восстановить исходные значения).
Необходимо отметить, что для такого класса задач существует не одно, а целое множество решений, особенно, если не приравнивать жестко погрешность к нулю. Поэтому запуск Поиска решения с разными начальными данными (т.е. разными комбинациями 0 и 1 в столбце В) может приводить к разным наборам чисел в выборках в пределах заданных ограничений. Так что имеет смысл прогнать эту процедуру несколько раз, произвольно изменяя переключатели в столбце В.
И весьма удобно будет вывести все найденные решения, сохраненные в виде сценариев, в одной сравнительной таблице с помощью кнопки Отчет (Summary):
Способ 2. Макрос подбора
В этом способе всю работу делает макрос, который тупо перебирает случайные комбинации чисел, пока не наткнется на нужную сумму в пределах разрешенной погрешности. Добавлять столбец с нулями и единичками и формулы в этом случае не нужно.
Аналогично первому способу, запуская макрос несколько раз, можно получать разные наборы подходящих чисел.
и установить соответствующий флажок. Тогда на вкладке или в меню Данные появится нужная команда.
Способ 2. Макрос подбора
возможно, что и найдёте нужное Вам решение.
p.s. ваши значения не смотрел, но, думаю, что Вы понимаете, что далеко не всегда можно разместить файлы оптимально. (ну если общий объём разделить на объём одного диска, далеко не всегда может получится реальное число.
для простоты рассмотрите пример, когда есть 10 видеофайлов по 1.7 Гб (1740.8 Мб), общим объёмом 17408 Мб), казалось бы, дисков по 4488 Мб нужно всего 4 штуки (при делении получается 3.87), однако, больше двух файлов на один диск не влезет, поэтому для записи файлов потребуется 5 дисков.
В любом случае - успехов!
Я и сам когда-то заморачивался подобными подборами. Потом бросил.
и с укладкой файлов на диск есть еще один момент:
физический размер файла 1 байт на диске в зависимости от FAT (таблица размещения файлов) и внимание! от обьема диска может занимать от 512байт до 32МБ - это так называемый размер кластера файловой системы и все это связано с физической адресацией файлов и ограничениями на размеры самой FAT.
т.е. место на диске выделяется кусками кратными размеру кластера.
и если кластер 4Мб, то еще необходимо учитывать что 4МБ это не 4млн., а 4*1024*1024 байт.
как-то так.
интересно? - поищите в инете "размер кластера"
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете Подбор слагаемых для нужной суммы в Excel Serge_Bliznykov и IgorGo, я знаю, что в каждом двд остануться пустые мегабайты. Особенно в последнем. Но обратите внимание, как я плотно вручную (без помощи Экселя) заполнил файлами первые 12 двд (сортировка колонки С и выделение ячеек в колонке В покажет вам сумму в правом низу). Опция "Поиск решения" у меня вызывается (как активировать показано в Ютубе), но какую формулу вписать и куда - вот в чем вопрос?
сами откройте Ваш файл.
где там 12 ДВД?
в какой ДВД рзмером 28 Вы собираетесь записать файл размером 43?
и я говорил совсем о другом, преждечем решать задачу комбинирования файлов на ДВД, необходимо размеры файлов поравить с учетом размера кластера.
Читайте также: