Получить комбинацию зная номер и количество чисел - Мир MS Excel

Регистрация

Напомнить пароль

= Мир MS Excel/Получить комбинацию зная номер и количество чисел - Мир MS Excel

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Получить комбинацию зная номер и количество чисел (Формулы/Formulas)

Получить комбинацию зная номер и количество чисел

Дата: Пятница, 15.08.2014, 00:48 | Сообщение № 1

Группа: Модераторы

Ранг: Старожил

Сообщений: 2290

Репутация: 766 ±

Замечаний: 0% ±

2019

Добрый вечер.

Может кто знает как получить или рассчитать формулами(макросом) комбинацию зная ее порядковый номер. Т.е есть номер комбинации, и количество цифр(например 8) - нужно рассчитать комбинацию которая "скрывается" под этим номером (номер = 32000 а комбинация: 7 3 4 6 2 1 8 5).
Комбинации могут быть любыми без повторений одинаковых чисел в ряду.
Что-то похожее было здесь:
[http://www.planetaexcel.ru/forum/index.php?PAGE_NAME=read&FID=8&TID=35378]
, но там комбинации только по возрастанию.
В приложенном примере есть перечень комбинаций для примера(ниже 7-й строки) и в 4-й строке формула для перепроверки результата.
Результат нужно получить без использования данных ниже 7-й строки.
Макрос для генерации комбинаций взял из соседней темы

К сообщению приложен файл: 6134268.xlsm (48.2 Kb)

Иногда все проще чем кажется с первого взгляда.

Сообщение отредактировал SLAVICK - Пятница, 15.08.2014, 23:05

Ответить

Дата: Понедельник, 18.08.2014, 23:22 | Сообщение № 2

Группа: Модераторы

Ранг: Старожил

Сообщений: 2290

Репутация: 766 ±

Замечаний: 0% ±

2019

пробую написать UDF ... пока получилось просчитать номер последовательности

. т.е. порядковые номера групп(столбец S) + Частично сами комбинации.
но пока по моему алгоритму строится меньше половины(правильно)

чувствую что где-то что-то пропустил... не могу найти

К сообщению приложен файл: 7638747.xlsm (89.1 Kb)

Иногда все проще чем кажется с первого взгляда.

Сообщение отредактировал SLAVICK - Понедельник, 18.08.2014, 23:22

Ответить

Дата: Вторник, 19.08.2014, 00:10 | Сообщение № 3

Группа: Админы

Ранг: Старожил

Сообщений: 2003

Репутация: 751 ±

Замечаний: ±

детально файл посмотреть не могу, пишу с телефона. но на вид классические перестановки. по порядковому номеру перестановки можно получить последовательность чисел без вычисления предыдущих перестановок. мое формульное решение было на планете в "избушке формулистов 2". поищите там. если не найдете, то подготовлю пример через неделю

Ответить

Дата: Вторник, 19.08.2014, 00:32 | Сообщение № 4

Группа: Админы

Ранг: Старожил

Сообщений: 2003

Репутация: 751 ±

Замечаний: ±

http://www.planetaexcel.ru/forum....GEN_1=5
сообщения от 9 января 2011 годо

Ответить

Дата: Вторник, 19.08.2014, 18:09 | Сообщение № 5

Группа: Модераторы

Ранг: Старожил

Сообщений: 2290

Репутация: 766 ±

Замечаний: 0% ±

2019

Спасибо

Не пришлось изобретать велосипед, но принцип работы такой же как и в моей функции - просто с логическим завершением

Есть один недостаток в том файле он создает комбинации перепутано т.е. не в такой последовательности, как мне нужно.
т.е. там идет первый столбец :66665666 а должно быть: сначала все 1, потом 2 ...(ну или в обратной последовательности) ... думаю разберусь <_<

<_<

Иногда все проще чем кажется с первого взгляда.

Ответить

Дата: Вторник, 19.08.2014, 18:27 | Сообщение № 6

Группа: Админы

Ранг: Старожил

Сообщений: 2003

Репутация: 751 ±

Замечаний: ±

Цитата SLAVICK, 19.08.2014 в 18:09, в сообщении № 5

должно быть: сначала все 1, потом 2 ...

Замените в формуле НАИМЕНЬШИЙ на НАИБОЛЬШИЙ

Ответить

Дата: Вторник, 19.08.2014, 23:14 | Сообщение № 7

Группа: Модераторы

Ранг: Старожил

Сообщений: 2290

Репутация: 766 ±

Замечаний: 0% ±

2019

Цитата MCH, 19.08.2014 в 18:27, в сообщении № 6

Замените в формуле НАИМЕНЬШИЙ на НАИБОЛЬШИЙ

Я наверное не совсем понятно сказал.
Я говорю о том, что последовательность не такая. Если поменять на "наибольший" - получается:

1 2 3 4 5 6
1 2 3 4 6 5
1 2 3 5 6 4
1 2 4 5 6 3
1 3 4 5 6 2
2 3 4 5 6 1
1 2 3 5 4 6
1 2 3 6 4 5
1 2 3 6 5 4
1 2 4 6 5 3

а должно быть:
1 2 3 4 5 6
1 2 3 4 6 5
1 2 3 5 4 6
1 2 3 5 6 4
1 2 3 6 4 5
1 2 3 6 5 4
1 2 4 3 5 6
1 2 4 3 6 5
1 2 4 5 3 6
1 2 4 5 6 3

Т.е. для 6-ти цифр у нас есть 6 уровней (для каждой цифры по уровню):
1.Самый крупный меняется раз в 120 строк и после того как поменялся больше обратно не меняется
2.меняется внутри 1-го каждые 24 строки(т.е по 5 раз)
3.меняется внутри 2-го каждые 6 строки(т.е по 4 раза)
4.меняется внутри 3-го каждые 2 строки(т.е по 3 раза)
5. - 6.й меняется внутри 4-го на каждую строку(чередуются между собой)
получается когда исчерпывается "подуровень" - меняется уровень, потом "подуровень" снова изменяется так же как и в предыдущем уровне...

Объяснил как мог

Посмотрите файл из моего поста выше - там под 7-й строкой массив данных.(смоделированный Вашим макросом из другой темы) - там четко видна структура.
А в файлах, о которых Вы написали немного другая логика... хотя итоговый набор данных будет одинаковый, но для конкретных строк будет разнится. например для строки 351:
формулой : 5 6 3 1 2 4
в массиве: 3 6 4 2 1 5

Цитата MCH, 19.08.2014 в 18:27, в сообщении № 6

Замените в формуле НАИМЕНЬШИЙ на НАИБОЛЬШИЙ

Я наверное не совсем понятно сказал.
Я говорю о том, что последовательность не такая. Если поменять на "наибольший" - получается:

1 2 3 4 5 6
1 2 3 4 6 5
1 2 3 5 6 4
1 2 4 5 6 3
1 3 4 5 6 2
2 3 4 5 6 1
1 2 3 5 4 6
1 2 3 6 4 5
1 2 3 6 5 4
1 2 4 6 5 3

а должно быть:
1 2 3 4 5 6
1 2 3 4 6 5
1 2 3 5 4 6
1 2 3 5 6 4
1 2 3 6 4 5
1 2 3 6 5 4
1 2 4 3 5 6
1 2 4 3 6 5
1 2 4 5 3 6
1 2 4 5 6 3

Т.е. для 6-ти цифр у нас есть 6 уровней (для каждой цифры по уровню):
1.Самый крупный меняется раз в 120 строк и после того как поменялся больше обратно не меняется
2.меняется внутри 1-го каждые 24 строки(т.е по 5 раз)
3.меняется внутри 2-го каждые 6 строки(т.е по 4 раза)
4.меняется внутри 3-го каждые 2 строки(т.е по 3 раза)
5. - 6.й меняется внутри 4-го на каждую строку(чередуются между собой)
получается когда исчерпывается "подуровень" - меняется уровень, потом "подуровень" снова изменяется так же как и в предыдущем уровне...

Объяснил как мог

Посмотрите файл из моего поста выше - там под 7-й строкой массив данных.(смоделированный Вашим макросом из другой темы) - там четко видна структура.
А в файлах, о которых Вы написали немного другая логика... хотя итоговый набор данных будет одинаковый, но для конкретных строк будет разнится. например для строки 351:
формулой : 5 6 3 1 2 4
в массиве: 3 6 4 2 1 5SLAVICK

Иногда все проще чем кажется с первого взгляда.

Сообщение отредактировал SLAVICK - Вторник, 19.08.2014, 23:15

Ответить

Сообщение

Цитата MCH, 19.08.2014 в 18:27, в сообщении № 6

Замените в формуле НАИМЕНЬШИЙ на НАИБОЛЬШИЙ

Я наверное не совсем понятно сказал.
Я говорю о том, что последовательность не такая. Если поменять на "наибольший" - получается:

1 2 3 4 5 6
1 2 3 4 6 5
1 2 3 5 6 4
1 2 4 5 6 3
1 3 4 5 6 2
2 3 4 5 6 1
1 2 3 5 4 6
1 2 3 6 4 5
1 2 3 6 5 4
1 2 4 6 5 3

а должно быть:
1 2 3 4 5 6
1 2 3 4 6 5
1 2 3 5 4 6
1 2 3 5 6 4
1 2 3 6 4 5
1 2 3 6 5 4
1 2 4 3 5 6
1 2 4 3 6 5
1 2 4 5 3 6
1 2 4 5 6 3

Т.е. для 6-ти цифр у нас есть 6 уровней (для каждой цифры по уровню):
1.Самый крупный меняется раз в 120 строк и после того как поменялся больше обратно не меняется
2.меняется внутри 1-го каждые 24 строки(т.е по 5 раз)
3.меняется внутри 2-го каждые 6 строки(т.е по 4 раза)
4.меняется внутри 3-го каждые 2 строки(т.е по 3 раза)
5. - 6.й меняется внутри 4-го на каждую строку(чередуются между собой)
получается когда исчерпывается "подуровень" - меняется уровень, потом "подуровень" снова изменяется так же как и в предыдущем уровне...

Объяснил как мог

Посмотрите файл из моего поста выше - там под 7-й строкой массив данных.(смоделированный Вашим макросом из другой темы) - там четко видна структура.
А в файлах, о которых Вы написали немного другая логика... хотя итоговый набор данных будет одинаковый, но для конкретных строк будет разнится. например для строки 351:
формулой : 5 6 3 1 2 4
в массиве: 3 6 4 2 1 5

Автор - SLAVICK
Дата добавления - 19.08.2014 в 23:14

Дата: Вторник, 19.08.2014, 23:29 | Сообщение № 8

Группа: Админы

Ранг: Старожил

Сообщений: 2003

Репутация: 751 ±

Замечаний: ±

можно написать udf, которая будет перебирать числа в лексиграфическом порядке, т.е. как Вам нужно. если чисел не много (не более 10-11) то можно сгенерировать достаточно быстро все перестановки в нужном порядке. формулой реализован немного другой алгоритм

Ответить

Дата: Среда, 20.08.2014, 01:02 | Сообщение № 9

Группа: Модераторы

Ранг: Старожил

Сообщений: 2290

Репутация: 766 ±

Замечаний: 0% ±

2019

Цитата MCH, 19.08.2014 в 23:29, в сообщении № 8

можно написать udf

Я как раз это и делаю

.
Вы, наверное, не поняли сути этой затеи. Мне не нужно получить массив итоговых данных(он у меня есть), мне нужно зная номер строки и количество цифр получить комбинацию(не обращаясь к готовому массиву), причем без перебора.С перебором не интересно

Иногда все проще чем кажется с первого взгляда.

Ответить

Дата: Среда, 20.08.2014, 22:44 | Сообщение № 10

Группа: Админы

Ранг: Старожил

Сообщений: 2003

Репутация: 751 ±

Замечаний: ±

Посмотрите пример по определению перестановки чисел по порядковому номеру сделанный на фрмулах, реализовать данный алгоритм в виде UDF не составит труда.

К сообщению приложен файл: perest_algo.xls (32.5 Kb)

Сообщение отредактировал MCH - Среда, 20.08.2014, 22:45

Ответить

Дата: Четверг, 21.08.2014, 00:00 | Сообщение № 11

Группа: Админы

Ранг: Старожил

Сообщений: 2003

Репутация: 751 ±

Замечаний: ±

Реализация в виде UDF

К сообщению приложен файл: PermNum.xls (35.0 Kb)

Ответить

Дата: Пятница, 22.08.2014, 00:37 | Сообщение № 12

Группа: Модераторы

Ранг: Старожил

Сообщений: 2290

Репутация: 766 ±

Замечаний: 0% ±

2019

Ваш код куда меньше того что получился у меня

Но у Вас в коде недостаток в том, что есть ограничение на 12 цифр... а у меня нет

Просто (объясняю не для Вас, а для тех кто не знает) VBA не очень дружит с функцией mod.... и лучше присваивать цифрам "Double" впрочем это легко исправить.

В моей функции есть 2 режима вывода - текст строка + массив(для формул массива) + можно вывести номер комбинации(также в строке и в массиве).

Правда Ваша формула меньше и шустрее. Моя немного корявая(я ее начал писать до того как увидел файл с формулой), но вроде работает

К сообщению приложен файл: PermNum-1-.xls (93.5 Kb)

Иногда все проще чем кажется с первого взгляда.

Ответить

Дата: Пятница, 22.08.2014, 00:38 | Сообщение № 13

Группа: Модераторы

Ранг: Старожил

Сообщений: 2290

Репутация: 766 ±

Замечаний: 0% ±

2019

Думаю тему можно закрывать <_<

<_<

Иногда все проще чем кажется с первого взгляда.

Ответить

Дата: Понедельник, 25.08.2014, 13:26 | Сообщение № 14

Группа: Админы

Ранг: Старожил

Сообщений: 2003

Репутация: 751 ±

Замечаний: ±

Цитата SLAVICK, 22.08.2014 в 00:38, в сообщении № 13

Но у Вас в коде недостаток в том, что есть ограничение на 12 цифр... а у меня нет

Это ограничение связано с тем, что я использовал у переменных тип Long, поэтому 13! уже не помещается в данный тип переменной, это легко обходится изменением типов.

Цитата SLAVICK, 22.08.2014 в 00:37, в сообщении № 12

VBA не очень дружит с функцией mod....

x mod y можно заменить на x-fix(x/y)*y

Цитата SLAVICK, 22.08.2014 в 00:37, в сообщении № 12

В моей функции есть 2 режима вывода - текст строка + массив

О форме вывода даже не заморачивался, сделал самое простое. Думаю, что в данной задаче более важен алгоритм, а не форма вывода.

Цитата SLAVICK, 22.08.2014 в 00:37, в сообщении № 12

и лучше присваивать цифрам "Double"

Для больших чисел это тоже не будет решением проблемы. Так для 18 чисел сможете получить комбинацию № 2432902008176639999, соответствующую числам: 18, 17, 16, 15, 14, 13, 12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 1, 2?

Ответить

Дата: Вторник, 26.08.2014, 00:50 | Сообщение № 15

Группа: Модераторы

Ранг: Старожил

Сообщений: 2290

Репутация: 766 ±

Замечаний: 0% ±

2019

Наверно поспешил закрывать тему :'(

:'(

Цитата MCH, 25.08.2014 в 13:26, в сообщении № 14

Так для 18 чисел сможете получить комбинацию № 2432902008176639999,

Мой Эксель для фактр(18) выдает "6 402 373 705 728 000", что на порядок меньше чем " 2432902008176639999" (это ближе к 20!).
Для числа 20 № 2432902008176639999 = 20|19|18|17|16|15|14|13|12|11|10|9|6|8|5|4|2|1|7|3

Моя функция(и Ваша если сделать то о чем я и Вы написали... т.е. поменять тип переменной) вполне корректно работает с 18!.
А вот с более 18 возникают некоторые проблемы... т.е комбинацию выдает, но она почему-то меняется не на каждую единицу :'(

:'(

.
Что же делать с такими цифрами?

По поводу

Цитата MCH, 25.08.2014 в 13:26, в сообщении № 14

x mod y можно заменить на x-fix(x/y)*y

Я знаю.

Я с этим уже сталкивался, просто написал обо что Ваша функция "спотыкалась" при вводе больших чисел (не для Вас, а для тех, кто захотел бы ее использовать) . Я например использовал Int(или WorksheetFunction.RoundDown) вместо Fix

Цитата MCH, 25.08.2014 в 13:26, в сообщении № 14

О форме вывода даже не заморачивался, сделал самое простое. Думаю, что в данной задаче более важен алгоритм, а не форма вывода.

Вы правы - это не важно. Я не хотел сказать, что Ваша функция хуже или лучше ... просто я писал свою функцию(не видя Вашей) для себя, и выложил как второй вариант решения проблемы.

Тогда, тема не закрыта... Как же сделать так чтобы правильно работало и с цифрами более 20!?

Наверно поспешил закрывать тему :'(

:'(

Цитата MCH, 25.08.2014 в 13:26, в сообщении № 14

Так для 18 чисел сможете получить комбинацию № 2432902008176639999,

Мой Эксель для фактр(18) выдает "6 402 373 705 728 000", что на порядок меньше чем " 2432902008176639999" (это ближе к 20!).
Для числа 20 № 2432902008176639999 = 20|19|18|17|16|15|14|13|12|11|10|9|6|8|5|4|2|1|7|3

Моя функция(и Ваша если сделать то о чем я и Вы написали... т.е. поменять тип переменной) вполне корректно работает с 18!.
А вот с более 18 возникают некоторые проблемы... т.е комбинацию выдает, но она почему-то меняется не на каждую единицу :'(

:'(

.
Что же делать с такими цифрами?

По поводу

Цитата MCH, 25.08.2014 в 13:26, в сообщении № 14

x mod y можно заменить на x-fix(x/y)*y

Я знаю.

Я с этим уже сталкивался, просто написал обо что Ваша функция "спотыкалась" при вводе больших чисел (не для Вас, а для тех, кто захотел бы ее использовать) . Я например использовал Int(или WorksheetFunction.RoundDown) вместо Fix

Цитата MCH, 25.08.2014 в 13:26, в сообщении № 14

О форме вывода даже не заморачивался, сделал самое простое. Думаю, что в данной задаче более важен алгоритм, а не форма вывода.

Вы правы - это не важно. Я не хотел сказать, что Ваша функция хуже или лучше ... просто я писал свою функцию(не видя Вашей) для себя, и выложил как второй вариант решения проблемы.

Тогда, тема не закрыта... Как же сделать так чтобы правильно работало и с цифрами более 20!?SLAVICK

Иногда все проще чем кажется с первого взгляда.

Сообщение отредактировал SLAVICK - Вторник, 26.08.2014, 00:59

Ответить

СообщениеНаверно поспешил закрывать тему :'(

:'(

Цитата MCH, 25.08.2014 в 13:26, в сообщении № 14

Так для 18 чисел сможете получить комбинацию № 2432902008176639999,

Мой Эксель для фактр(18) выдает "6 402 373 705 728 000", что на порядок меньше чем " 2432902008176639999" (это ближе к 20!).
Для числа 20 № 2432902008176639999 = 20|19|18|17|16|15|14|13|12|11|10|9|6|8|5|4|2|1|7|3

Моя функция(и Ваша если сделать то о чем я и Вы написали... т.е. поменять тип переменной) вполне корректно работает с 18!.
А вот с более 18 возникают некоторые проблемы... т.е комбинацию выдает, но она почему-то меняется не на каждую единицу :'(

:'(

.
Что же делать с такими цифрами?

По поводу

Цитата MCH, 25.08.2014 в 13:26, в сообщении № 14

x mod y можно заменить на x-fix(x/y)*y

Я знаю.

Я с этим уже сталкивался, просто написал обо что Ваша функция "спотыкалась" при вводе больших чисел (не для Вас, а для тех, кто захотел бы ее использовать) . Я например использовал Int(или WorksheetFunction.RoundDown) вместо Fix

Цитата MCH, 25.08.2014 в 13:26, в сообщении № 14

О форме вывода даже не заморачивался, сделал самое простое. Думаю, что в данной задаче более важен алгоритм, а не форма вывода.

Вы правы - это не важно. Я не хотел сказать, что Ваша функция хуже или лучше ... просто я писал свою функцию(не видя Вашей) для себя, и выложил как второй вариант решения проблемы.

Тогда, тема не закрыта... Как же сделать так чтобы правильно работало и с цифрами более 20!?

Автор - SLAVICK
Дата добавления - 26.08.2014 в 00:50

Дата: Вторник, 26.08.2014, 10:20 | Сообщение № 16

Группа: Админы

Ранг: Старожил

Сообщений: 2003

Репутация: 751 ±

Замечаний: ±

Цитата SLAVICK, 26.08.2014 в 00:50, в сообщении № 15

это ближе к 20!

Немного напутал, имел в виду 20!, а указал 18!

На стандартных типах можно расчитать до 27!
Для бОльших чисел нужно использовать длинную арифметику.
Только возникает вопрос, зачем нужны такие большие числа?

К сообщению приложен файл: PermNum2.xls (39.0 Kb)

Ответить

Дата: Вторник, 26.08.2014, 15:43 | Сообщение № 17

Группа: Модераторы

Ранг: Старожил

Сообщений: 2290

Репутация: 766 ±

Замечаний: 0% ±

2019

Спасибо.

Мы с Вами общались в другой теме. по оптимизации маршрута.
У меня возникла мысль делать не просто перебор, а обращаться к комбинации...
Я уже немного изменил там код... прохожу сначала по большим секциям... потом там где было найдено найменьшее значение - прохожу по меньшей секции и +- одна секция ... и.т.д.
Результат получается не всегда 100% попадание, но маршрут уменьшает почти до минимума(из 12149 до 8138 против 8041) ^_^

^_^

Больше описывать не буду - модераторы забанят

Напишу только что у меня уже получается работать там с количеством больше 20 точек... достаточно быстро(менее минуты.)

Иногда все проще чем кажется с первого взгляда.

Сообщение отредактировал SLAVICK - Вторник, 26.08.2014, 16:07

Ответить

Дата: Среда, 27.08.2014, 07:25 | Сообщение № 18

Группа: Админы

Ранг: Старожил

Сообщений: 2003

Репутация: 751 ±

Замечаний: ±

Цитата SLAVICK, 26.08.2014 в 15:43, в сообщении № 17

но маршрут уменьшает почти до минимума(из 12149 до 8138 против 8041)

Дайте пример данных, интересно самому потестировать

Думаю, что решение через перебор - тупиковое. Нужно рассмотреть другие алгоритмы решения задачи коммивояжера, например метод ветвей и границ (метод Литтла).

Ответить

Дата: Пятница, 29.08.2014, 10:31 | Сообщение № 19

Группа: Модераторы

Ранг: Старожил

Сообщений: 2290

Репутация: 766 ±

Замечаний: 0% ±

2019

Был в командировке. Выложил файл здесь в теме. В этой теме выкладывать не стал... т.к. модераторы скажут что файл не в той теме ^_^

^_^

.

Иногда все проще чем кажется с первого взгляда.

Ответить

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Получить комбинацию зная номер и количество чисел (Формулы/Formulas)

Страница 1 из 1
1

© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!