Метод перебора в excel
Вычисление числа комбинаций по формулам комбинаторики в Excel. Перебор всех вариантов перестановок, сочетаний и размещений для множеств без повторений и с повторениями.
Взаимосвязь некоторых распределений в MS EXCEL
Рассмотрим взаимосвязь Биномиального распределения, распределения Пуассона, Нормального распределения и Гипергеометрического распределения. Определим условия, когда возможна аппроксимация одного распределения другим, приведем примеры и графики.
Перестановки с повторениями: Комбинаторика в MS EXCEL
Подсчитаем в MS EXCEL количество перестановок с повторениями из n элементов. С помощью формул выведем на лист все варианты таких перестановок (английский перевод термина: permutations of multisets).
Сочетания с повторениями: Комбинаторика в MS EXCEL
Подсчитаем в MS EXCEL количество Сочетаний с повторениями из n по k (выборка с возвращением). Также с помощью формул выведем на лист соответствующие варианты Сочетаний (английский перевод термина: combinations with …
Разрезка на мерные длины
Требуется разрезать провод на куски определенной длины, так чтобы количество отходов было минимально. Задачу решим методом перебора всех возможных комбинаций разрезки.
Размещения без повторений: Комбинаторика в MS EXCEL
Подсчитаем в MS EXCEL количество Размещений из n по k и с помощью формул выведем на лист соответствующие варианты размещений (английский перевод термина: partial permutation или sequence without repetition).
Перестановки без повторений: Комбинаторика в MS EXCEL
Подсчитаем в MS EXCEL количество перестановок из n элементов. С помощью формул выведем на лист все варианты перестановок (английский перевод термина: permutation).
Размещения c повторениями: Комбинаторика в MS EXCEL
Подсчитаем в MS EXCEL количество Размещений с повторениями из n по k (выборка с возвращением). Также с помощью формул выведем на лист соответствующие варианты Размещений (английский перевод термина: sequence with …
Комбинаторика в MS EXCEL
Обзорная статья, в которой приведены основные функции MS EXCEL для вычисления количества перестановок, сочетаний и размещений. Рассмотрены варианты комбинаций без повторений и с повторениями (выборка с возвращением).
Комбинации элементов из нескольких множеств: Комбинаторика в MS EXCEL
Пусть имеется несколько множеств —
Сочетания без повторений: Комбинаторика в MS EXCEL
Подсчитаем в MS EXCEL количество сочетаний из n элементов по k. С помощью формул выведем на лист все варианты сочетаний (английский перевод термина: Combinations without repetition).
Добрый день всем.
Прошу подсказать, возможно ли выполнить перебор числовых значений в ячейке, с определенным шагом (в примере: ячейка D2, шаг 0.01, значение от 0,1 до 1) до того момента, чтобы в итоговых расчетах (в примере: F2) получался результат сопоставимый с эталоном (погрешность не более 1 % или самая минимальная по модулю. В примере: сравнение F2 с А2)?
Так как таких расчетов может быть разное количество и их надо сравнивать друг с другом, то мне удобнее было бы копировать строки, меняя значение эталона и исходных значений в каждой ( В примере: A2,B2,C2). Т.е. нажимать каждый раз кнопку расчета не самый оптимальный вариант.
Надеюсь описал свои трудности понятно.
2-я строка: эталон - 10 (A2),Выставляю свои значения в B2 и C2. Далее методом перебора в ячейке D1 с указанным выше шагом должно появится значение 0,81. Так как при этом значении получается самая маленькая погрешность.
В 3-й строке уже эталон изменился и при сохранении тех же значений в B3 и C3 не получится получить погрешность менее 1%, я это вижу и меняю исходные значения ( в данном случае ячейка B3). Тут следует перебор значений в D3 до 0.53. И так далее в новых строках.
Заранее спасибо за помощь.
Добрый день всем.
Прошу подсказать, возможно ли выполнить перебор числовых значений в ячейке, с определенным шагом (в примере: ячейка D2, шаг 0.01, значение от 0,1 до 1) до того момента, чтобы в итоговых расчетах (в примере: F2) получался результат сопоставимый с эталоном (погрешность не более 1 % или самая минимальная по модулю. В примере: сравнение F2 с А2)?
Так как таких расчетов может быть разное количество и их надо сравнивать друг с другом, то мне удобнее было бы копировать строки, меняя значение эталона и исходных значений в каждой ( В примере: A2,B2,C2). Т.е. нажимать каждый раз кнопку расчета не самый оптимальный вариант.
Надеюсь описал свои трудности понятно.
2-я строка: эталон - 10 (A2),Выставляю свои значения в B2 и C2. Далее методом перебора в ячейке D1 с указанным выше шагом должно появится значение 0,81. Так как при этом значении получается самая маленькая погрешность.
В 3-й строке уже эталон изменился и при сохранении тех же значений в B3 и C3 не получится получить погрешность менее 1%, я это вижу и меняю исходные значения ( в данном случае ячейка B3). Тут следует перебор значений в D3 до 0.53. И так далее в новых строках.
Заранее спасибо за помощь. Affiant
Прошу подсказать, возможно ли выполнить перебор числовых значений в ячейке, с определенным шагом (в примере: ячейка D2, шаг 0.01, значение от 0,1 до 1) до того момента, чтобы в итоговых расчетах (в примере: F2) получался результат сопоставимый с эталоном (погрешность не более 1 % или самая минимальная по модулю. В примере: сравнение F2 с А2)?
Так как таких расчетов может быть разное количество и их надо сравнивать друг с другом, то мне удобнее было бы копировать строки, меняя значение эталона и исходных значений в каждой ( В примере: A2,B2,C2). Т.е. нажимать каждый раз кнопку расчета не самый оптимальный вариант.
Надеюсь описал свои трудности понятно.
2-я строка: эталон - 10 (A2),Выставляю свои значения в B2 и C2. Далее методом перебора в ячейке D1 с указанным выше шагом должно появится значение 0,81. Так как при этом значении получается самая маленькая погрешность.
В 3-й строке уже эталон изменился и при сохранении тех же значений в B3 и C3 не получится получить погрешность менее 1%, я это вижу и меняю исходные значения ( в данном случае ячейка B3). Тут следует перебор значений в D3 до 0.53. И так далее в новых строках.
Заранее спасибо за помощь. Автор - Affiant
Дата добавления - 03.06.2014 в 11:17
= Мир MS Excel/Полный перебор - Мир MS Excel
Войти через uID
Войти через uID
Есть файл (прилагается), где реализован полный перебор.
Нужно реализовать перебор в таком же виде , но более полный. Перебор должен работать как счетчик, т.е. чтобы была возможность повторяемости элементов. Например, имеем числа 1, 2, 3, - на выходе должны получить:
111
112
113
121
122
123
131
132
133
211
.
.
Есть файл (прилагается), где реализован полный перебор.
Нужно реализовать перебор в таком же виде , но более полный. Перебор должен работать как счетчик, т.е. чтобы была возможность повторяемости элементов. Например, имеем числа 1, 2, 3, - на выходе должны получить:
111
112
113
121
122
123
131
132
133
211
.
. ArtiEx
Если совсем в лоб, быстро на коленках, не погружаясь в алгоритмы комбинаторики, то как-то так для четырех:
[vba]
Sub generate()
Dim i, j, k, l, m
For i = 1 To 4
For j = 1 To 4
For k = 1 To 4
For l = 1 To 4
If i <> j And i <> k And i <> l And j <> k And j <> l And k <> l Then
[b1].Offset(m) = i & j & k & l
m = m + 1
End If
Next l, k, j, i
End Sub
P.S. Невнимательно прочитал. А если повторы цифр не надо исключать, то тогда просто IF убираем и еще проще становится:
[vba]
Sub generateWithRepeat()
Dim i, j, k, l, m
For i = 1 To 4
For j = 1 To 4
For k = 1 To 4
For l = 1 To 4
[b1].Offset(m) = i & j & k & l
m = m + 1
Next l, k, j, i
End Sub
Если совсем в лоб, быстро на коленках, не погружаясь в алгоритмы комбинаторики, то как-то так для четырех:
[vba]
Sub generate()
Dim i, j, k, l, m
For i = 1 To 4
For j = 1 To 4
For k = 1 To 4
For l = 1 To 4
If i <> j And i <> k And i <> l And j <> k And j <> l And k <> l Then
[b1].Offset(m) = i & j & k & l
m = m + 1
End If
Next l, k, j, i
End Sub
P.S. Невнимательно прочитал. А если повторы цифр не надо исключать, то тогда просто IF убираем и еще проще становится:
[vba]
Sub generateWithRepeat()
Dim i, j, k, l, m
For i = 1 To 4
For j = 1 To 4
For k = 1 To 4
For l = 1 To 4
[b1].Offset(m) = i & j & k & l
m = m + 1
Next l, k, j, i
End Sub
Sub generate()
Dim i, j, k, l, m
For i = 1 To 4
For j = 1 To 4
For k = 1 To 4
For l = 1 To 4
If i <> j And i <> k And i <> l And j <> k And j <> l And k <> l Then
[b1].Offset(m) = i & j & k & l
m = m + 1
End If
Next l, k, j, i
End Sub
P.S. Невнимательно прочитал. А если повторы цифр не надо исключать, то тогда просто IF убираем и еще проще становится:
[vba]
Sub generateWithRepeat()
Dim i, j, k, l, m
For i = 1 To 4
For j = 1 To 4
For k = 1 To 4
For l = 1 To 4
[b1].Offset(m) = i & j & k & l
m = m + 1
Next l, k, j, i
End Sub
Я так понял, что нужны все комбинации (а не перестановки), так для 3 значений это будет 27 вариантов (3^3), для 4 значений - 256 вариантов (4^4), так для количества данных более 7 уже будет проблематично выводить на лист все варианты комбинаций
Можно решить формулами или макросом
Вариант алгоритма:
запускаем цикл от 0 до n^n-1 (где n - это количество значений)
переводим число из счетчика в n-значную систему счисления, тем самым получим полный перебор всех вариантов
Я так понял, что нужны все комбинации (а не перестановки), так для 3 значений это будет 27 вариантов (3^3), для 4 значений - 256 вариантов (4^4), так для количества данных более 7 уже будет проблематично выводить на лист все варианты комбинаций
Можно решить формулами или макросом
Вариант алгоритма:
запускаем цикл от 0 до n^n-1 (где n - это количество значений)
переводим число из счетчика в n-значную систему счисления, тем самым получим полный перебор всех вариантов MCH
Можно решить формулами или макросом
Вариант алгоритма:
запускаем цикл от 0 до n^n-1 (где n - это количество значений)
переводим число из счетчика в n-значную систему счисления, тем самым получим полный перебор всех вариантов Автор - MCH
Дата добавления - 20.03.2013 в 10:05
Добрый день ! Подскажите если знаете. )
Как сделать в excel перебор чисел с определенным шагом. Скажем хочу задать excel найти из диапазона цифр (от 587,01 до 591,99 с шагом 0,01) число при умножении которого на число из диапазона (от 3170,01 до 3200,99 с шагом 0,01) получим результат 1886000. Важно что бы все числа были точными до сотых без применения округлений. Т.е числа в которых после запятой встречаются цифры дальше сотых неприемлемы но и округления применять нельзя.
Заранее благодарен за ответ !
Добрый день ! Подскажите если знаете. )
Как сделать в excel перебор чисел с определенным шагом. Скажем хочу задать excel найти из диапазона цифр (от 587,01 до 591,99 с шагом 0,01) число при умножении которого на число из диапазона (от 3170,01 до 3200,99 с шагом 0,01) получим результат 1886000. Важно что бы все числа были точными до сотых без применения округлений. Т.е числа в которых после запятой встречаются цифры дальше сотых неприемлемы но и округления применять нельзя.
Заранее благодарен за ответ ! Rafting
Как сделать в excel перебор чисел с определенным шагом. Скажем хочу задать excel найти из диапазона цифр (от 587,01 до 591,99 с шагом 0,01) число при умножении которого на число из диапазона (от 3170,01 до 3200,99 с шагом 0,01) получим результат 1886000. Важно что бы все числа были точными до сотых без применения округлений. Т.е числа в которых после запятой встречаются цифры дальше сотых неприемлемы но и округления применять нельзя.
Заранее благодарен за ответ ! Автор - Rafting
Дата добавления - 10.04.2017 в 15:08
Вы имеете ввиду "Данные-Анализ-Подбор параметров" ?
Если да то возникает вопрос. Как в строчке "Значение" указать что число может быть любым НО все цифры после сотых должны быть "НУЛИ" Я пробовал так *,**0000000 в результате excel ругается.
Вы имеете ввиду "Данные-Анализ-Подбор параметров" ?
Если да то возникает вопрос. Как в строчке "Значение" указать что число может быть любым НО все цифры после сотых должны быть "НУЛИ" Я пробовал так *,**0000000 в результате excel ругается. Rafting
Rafting, опишите немного поподробнее хотелку. Что в итоге получить нужно? И почему именно так? _Boroda_
Да сделайте обыкновенную "таблицу умножения": в ячейки B1:SF1 с шагом 0,01 протяните 499 значений от 587,01 до 591,99 (колонки), в ячейки A2:A3100 - 3099 значений от 3170,01 до 3200,99. В ячейку B2 - формулу =$A2*B$1 и протяните на весь диапазон B2:SF3100. Дальше обычный Поиск (по Ctrl+F) и ищем значение 1886000.
Показал бы в файле, но файл получился 23 мегабайта. Но в принципе я всё и так рассказал.
P.S. Можно подсветить результаты условным форматированием с формулой: =ЦЕЛОЕ(B2)=1886000 . У меня поиском по кнопке "Найти все" нашлась 51 ячейка (в одной, правда, чуть меньше 1885999,9964, но поскольку отображалась как искомая строка, то тоже поучаствовала). Это было при ширине колонок 80 пикселей. А если увеличить ширину до, скажем, 100 пикселей (при этом в ячейках будут видны 3 знака после запятой), то останутся 50 честных вариантов.
Да сделайте обыкновенную "таблицу умножения": в ячейки B1:SF1 с шагом 0,01 протяните 499 значений от 587,01 до 591,99 (колонки), в ячейки A2:A3100 - 3099 значений от 3170,01 до 3200,99. В ячейку B2 - формулу =$A2*B$1 и протяните на весь диапазон B2:SF3100. Дальше обычный Поиск (по Ctrl+F) и ищем значение 1886000.
Показал бы в файле, но файл получился 23 мегабайта. Но в принципе я всё и так рассказал.
P.S. Можно подсветить результаты условным форматированием с формулой: =ЦЕЛОЕ(B2)=1886000 . У меня поиском по кнопке "Найти все" нашлась 51 ячейка (в одной, правда, чуть меньше 1885999,9964, но поскольку отображалась как искомая строка, то тоже поучаствовала). Это было при ширине колонок 80 пикселей. А если увеличить ширину до, скажем, 100 пикселей (при этом в ячейках будут видны 3 знака после запятой), то останутся 50 честных вариантов. Gustav
Показал бы в файле, но файл получился 23 мегабайта. Но в принципе я всё и так рассказал.
P.S. Можно подсветить результаты условным форматированием с формулой: =ЦЕЛОЕ(B2)=1886000 . У меня поиском по кнопке "Найти все" нашлась 51 ячейка (в одной, правда, чуть меньше 1885999,9964, но поскольку отображалась как искомая строка, то тоже поучаствовала). Это было при ширине колонок 80 пикселей. А если увеличить ширину до, скажем, 100 пикселей (при этом в ячейках будут видны 3 знака после запятой), то останутся 50 честных вариантов. Автор - Gustav
Дата добавления - 11.04.2017 в 13:33
Я уже делал как предлагает Gustav в итоге тратится очень много времени.
Конкретное описание хотелки:
Получаю смету на определенную сумму 1 886 000
Площадь работ по смете 3200 кв.м
Когда заношу данные в электронную систему, система выводит цену за 1 кв.метр равную 1 886 000/3200 = 589,38 (цифра округляется, реальный же результат 589,375. Система выводит значение до сотых) В дальнейшем система начинает ругаться на то что 3200*589,38 не равно 1 886 000. Допустимо менять значения 3200 и 589,38 в небольшом диапазоне. Главное что бы при умножении двух значений получался конкретный результат 1 886 000.
Так как задача регулярная, ищу способ как задать excel цифру которую нужно получить путем умножения числа X на Y. где X - это диапазон значений от 3100,00 до 3300,00 с шагом 0,01 а Y - это диапазон значений от 560,00 до 600,00 с шагом 0,01
Надеюсь понятно объяснил. Заранее благодарю за ответ.
Я уже делал как предлагает Gustav в итоге тратится очень много времени.
Конкретное описание хотелки:
Получаю смету на определенную сумму 1 886 000
Площадь работ по смете 3200 кв.м
Когда заношу данные в электронную систему, система выводит цену за 1 кв.метр равную 1 886 000/3200 = 589,38 (цифра округляется, реальный же результат 589,375. Система выводит значение до сотых) В дальнейшем система начинает ругаться на то что 3200*589,38 не равно 1 886 000. Допустимо менять значения 3200 и 589,38 в небольшом диапазоне. Главное что бы при умножении двух значений получался конкретный результат 1 886 000.
Так как задача регулярная, ищу способ как задать excel цифру которую нужно получить путем умножения числа X на Y. где X - это диапазон значений от 3100,00 до 3300,00 с шагом 0,01 а Y - это диапазон значений от 560,00 до 600,00 с шагом 0,01
Надеюсь понятно объяснил. Заранее благодарю за ответ. Rafting
Конкретное описание хотелки:
Получаю смету на определенную сумму 1 886 000
Площадь работ по смете 3200 кв.м
Когда заношу данные в электронную систему, система выводит цену за 1 кв.метр равную 1 886 000/3200 = 589,38 (цифра округляется, реальный же результат 589,375. Система выводит значение до сотых) В дальнейшем система начинает ругаться на то что 3200*589,38 не равно 1 886 000. Допустимо менять значения 3200 и 589,38 в небольшом диапазоне. Главное что бы при умножении двух значений получался конкретный результат 1 886 000.
Так как задача регулярная, ищу способ как задать excel цифру которую нужно получить путем умножения числа X на Y. где X - это диапазон значений от 3100,00 до 3300,00 с шагом 0,01 а Y - это диапазон значений от 560,00 до 600,00 с шагом 0,01
Надеюсь понятно объяснил. Заранее благодарю за ответ. Автор - Rafting
Дата добавления - 11.04.2017 в 14:05
Рассмотрим задачу коммивояжера (англ. Travelling Salesman Problem, TSP) заключающуюся в отыскании самого короткого маршрута, проходящего через заданные города по одному разу с последующим возвратом в исходный город (также рассмотрим вариант без возврата).
Задача
Найдем кратчайший путь между 5 городами, координаты которых известны. Рассмотрим замкнутый вариант задачи: коммивояжёру требуется посетить все города, после чего вернуться в исходный город.
Решение
Так как даны координаты городов, то сначала найдем расстояния между ними (см. файл примера, лист 5 городов ).
Расстояния рассчитаем с помощью формулы: = КОРЕНЬ((ИНДЕКС($C$7:$D$11;$F7+1;1)-ИНДЕКС($C$7:$D$11;G$6+1;1))^2 +(ИНДЕКС($C$7:$D$11;$F7+1;2)-ИНДЕКС($C$7:$D$11;G$6+1;2))^2)
Теперь создадим модель для Поиска решения .
Совет : Вводная статья про Поиск решения в MS EXCEL 2010 находится здесь .
Переменные (выделено зеленым) . В качестве переменных модели следует взять номера городов. Так как начальная и конечная точка известны (Город0), то переменных будет 4. Ограничения (выделено синим) . Необходимо, чтобы номера городов не повторялись. Это означает, что количество уникальных (неповторяющихся) номеров городов должно быть равно 4. Для этого используется формула для подсчета уникальных значений : = СУММПРОИЗВ(1/СЧЁТЕСЛИ(B16:B19;B16:B19))
Целевая функция (выделено красным) . Длина маршрута должна быть минимальной.
Примечание : для удобства настройки Поиска решения используются именованные диапазоны .
Выберите Эволюционный метод поиска решения, т.к. созданная модель не является линейной из-за наложенного на переменные ограничения.
Найденное Решение
Поиск решения найдет (должен найти) самый короткий маршрут, т.е. последовательность 0-1-4-2-3-0 (или обратную).
В этой простейшей задаче можно проверить, действительно ли этот маршрут имеет минимальную длину, путем перебора всех вариантов маршрутов. Это реализовано в файле примера .
Совет . Таблицы перебора перестановок от 1 до 5, от 1 до 6, … от 1 до 9 можно найти в этой статье Перебор всех возможных Перестановок в MS EXCEL .
Результаты Эволюционного метода сильно зависят от начальных условий и параметров его настройки (скорость изменения, размер совокупности). Повторный поиск, с теми же начальными условиями и параметрами, может привести к различным результатам. Убедиться в этом можно с помощью модели, созданной в файле примера на листе 11 городов.
Обнулите значения переменных модели на Листе 11 городов, запустите Поиск решения. После окончания поиска (может занять несколько минут) опять обнулите значения переменных и перезапустите Поиск решения: найденные решения могут серьезно отличаться.
Совет . Иногда, для того чтобы улучшить найденное решение, помогает следующий прием: запустите Поиск решения , сохраните найденное решение, измените параметры поиска (например, размер совокупности), повторно запустите Поиск решения.
В файле примера также приведено решение задачи для замкнутого и незамкнутого маршрута посещения 9 городов. Решения найдены с помощью Эволюционного метода со следующими параметрами: Целочисленная оптимальность 0%, Использовать автоматическое масштабирование, Скорость изменения варьировалась 0,25-0,5; Размер совокупности варьировался 10-50. Оба решения проверены с помощью таблиц перебора всех маршрутов (см. таблицы перебора перестановок ). Если при первом прогоне Поиска решения не удавалось найти оптимальное решение, то он запускался повторно, при этом 1 или 2 параметра изменялись. После второго прогона оптимальное решение, как правило, было найдено.
Вывод : задачу коммивояжера (граф полностью связный, гамильтоновый цикл) можно решить стандартным Поиском решения с помощью построения нелинейных моделей. При количестве вершин графа (городов)
Читайте также: