Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

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

 

= Мир MS Excel/Построить формулу с циклом для перебора вариантов сочетаний. - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Построить формулу с циклом для перебора вариантов сочетаний. (Формулы/Formulas)
Построить формулу с циклом для перебора вариантов сочетаний.
Yurasha Дата: Вторник, 01.11.2016, 23:39 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 2
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Здравствуйте!

Я здесь новичок и это мой первый пост.

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

...Требуется подобрать текстовые сочетания содержимого ячеек, но не все возможные, а подчиняющиеся порядковому чередованию:

A1+B1+C1+D1

Дано: 4 столбца с текстовыми значениями, количество строк во всех столбцах разное.

Пример:
______________
| -|A1|B1|C1|D1|
|-------------------
|1 | A | 1| a| X |
|2 | B | 2| b| Y |
|3 | C | _| c| Z |
|4 | D | _| d| _ |
|5 | E | _| _| _ |
--------------------
Хочу в итоговой колонке получить массив данных:

1 A1aX
2 A1aY
3 A1aZ
4 A1bX
5 A1bY
...
12 A1dZ
13 A2aX
...
24 A2dZ
25 B1aX
...
...
120 E2dZ

Конец.

Для упрощения решения могу предложить добавить в каждый исходный столбец количество символов для равного значения строк в каждом столбце:

| | A1|B1|C1|D1|
|-------------------
|1 | A | 1| a| X |
|2 | B | 2| b| Y |
|3 | C | $| c| Z |
|4 | D | $| d| $ |
|5 | E | $| $| $ |
--------------------

Впоследствии я смогу удалить из итоговой ячейки результаты, содержащие символы "$".
И сразу вопрос: можно ли создать такой макрос, который не будет зависеть от фиксированного числа строк в столбце, а будет запускать очередной цикл, когда "наткнется" на пустую ячейку?

В реальной задаче в столбце A1 будет 64 значения, в B1=32, C1=64, D1=16.
Знаю, что на лист весь вывод не поместится, поэтому, либо буду использовать в колонке A1 каждый раз по 2 значения (получится как раз 65536), либо попрошу опять же здесь помощи, чтобы реализовать переход на другой лист с помощью такого макроса (вычитал на одном из форумов):

[vba]
Код
set ws = activesheet
If ws.cols.count = 65... then
set ws = activeworkbook.sheets.add
end if
[/vba]

Буду благодарен если поможете реализовать идею, и объяснить принцип действия конкретного макроса.
Прикрепил сам файл с задачей, а то строки таблицы съезжают...
К сообщению приложен файл: Cycle_with_macr.xls(26Kb)


Сообщение отредактировал Yurasha - Среда, 02.11.2016, 16:58
 
Ответить
СообщениеЗдравствуйте!

Я здесь новичок и это мой первый пост.

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

...Требуется подобрать текстовые сочетания содержимого ячеек, но не все возможные, а подчиняющиеся порядковому чередованию:

A1+B1+C1+D1

Дано: 4 столбца с текстовыми значениями, количество строк во всех столбцах разное.

Пример:
______________
| -|A1|B1|C1|D1|
|-------------------
|1 | A | 1| a| X |
|2 | B | 2| b| Y |
|3 | C | _| c| Z |
|4 | D | _| d| _ |
|5 | E | _| _| _ |
--------------------
Хочу в итоговой колонке получить массив данных:

1 A1aX
2 A1aY
3 A1aZ
4 A1bX
5 A1bY
...
12 A1dZ
13 A2aX
...
24 A2dZ
25 B1aX
...
...
120 E2dZ

Конец.

Для упрощения решения могу предложить добавить в каждый исходный столбец количество символов для равного значения строк в каждом столбце:

| | A1|B1|C1|D1|
|-------------------
|1 | A | 1| a| X |
|2 | B | 2| b| Y |
|3 | C | $| c| Z |
|4 | D | $| d| $ |
|5 | E | $| $| $ |
--------------------

Впоследствии я смогу удалить из итоговой ячейки результаты, содержащие символы "$".
И сразу вопрос: можно ли создать такой макрос, который не будет зависеть от фиксированного числа строк в столбце, а будет запускать очередной цикл, когда "наткнется" на пустую ячейку?

В реальной задаче в столбце A1 будет 64 значения, в B1=32, C1=64, D1=16.
Знаю, что на лист весь вывод не поместится, поэтому, либо буду использовать в колонке A1 каждый раз по 2 значения (получится как раз 65536), либо попрошу опять же здесь помощи, чтобы реализовать переход на другой лист с помощью такого макроса (вычитал на одном из форумов):

[vba]
Код
set ws = activesheet
If ws.cols.count = 65... then
set ws = activeworkbook.sheets.add
end if
[/vba]

Буду благодарен если поможете реализовать идею, и объяснить принцип действия конкретного макроса.
Прикрепил сам файл с задачей, а то строки таблицы съезжают...

Автор - Yurasha
Дата добавления - 01.11.2016 в 23:39
Светлый Дата: Среда, 02.11.2016, 09:29 | Сообщение № 2
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 226
Репутация: 56 ±
Замечаний: 0% ±

Excel 2007
Макросы (VBA) в другом разделе. Или переименуйте тему.
А формульное решение Вашей задачи во вложенном файле.
Код
=СМЕЩ(A$1;ОТБР((СТРОКА()-1)/32/64/16);)&СМЕЩ(B$1;ОСТАТ(ОТБР((СТРОКА()-1)/64/16);32);)&СМЕЩ(C$1;ОСТАТ(ОТБР((СТРОКА()-1)/16);64);)&СМЕЩ(D$1;ОСТАТ(СТРОКА()-1;16);)

И универсальная:
Код
=СМЕЩ(A$1;ОСТАТ(ОТБР((СТРОКА()-1)/СЧЁТЗ(B:B)/СЧЁТЗ(C:C)/СЧЁТЗ(D:D));СЧЁТЗ(A:A));)&СМЕЩ(B$1;ОСТАТ(ОТБР((СТРОКА()-1)/СЧЁТЗ(C:C)/СЧЁТЗ(D:D));СЧЁТЗ(B:B));)&СМЕЩ(C$1;ОСТАТ(ОТБР((СТРОКА()-1)/СЧЁТЗ(D:D));СЧЁТЗ(C:C));)&СМЕЩ(D$1;ОСТАТ(СТРОКА()-1;СЧЁТЗ(D:D));)

А эту формулу можно протянуть по столбцам и получим все 64*32*64*16 сочетаний:
Код
=СМЕЩ($A$1;ОТБР(((СТРОКА()-1)+(СТОЛБЕЦ(A1)-1)*65536)/32/64/16);)&СМЕЩ($B$1;ОСТАТ(ОТБР(((СТРОКА()-1)+(СТОЛБЕЦ(A1)-1)*65536)/64/16);32);)&СМЕЩ($C$1;ОСТАТ(ОТБР(((СТРОКА()-1)+(СТОЛБЕЦ(A1)-1)*65536)/16);64);)&СМЕЩ($D$1;ОСТАТ(СТРОКА()-1;16);)
К сообщению приложен файл: 7322403.xls(32Kb)


Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Среда, 02.11.2016, 10:16
 
Ответить
СообщениеМакросы (VBA) в другом разделе. Или переименуйте тему.
А формульное решение Вашей задачи во вложенном файле.
Код
=СМЕЩ(A$1;ОТБР((СТРОКА()-1)/32/64/16);)&СМЕЩ(B$1;ОСТАТ(ОТБР((СТРОКА()-1)/64/16);32);)&СМЕЩ(C$1;ОСТАТ(ОТБР((СТРОКА()-1)/16);64);)&СМЕЩ(D$1;ОСТАТ(СТРОКА()-1;16);)

И универсальная:
Код
=СМЕЩ(A$1;ОСТАТ(ОТБР((СТРОКА()-1)/СЧЁТЗ(B:B)/СЧЁТЗ(C:C)/СЧЁТЗ(D:D));СЧЁТЗ(A:A));)&СМЕЩ(B$1;ОСТАТ(ОТБР((СТРОКА()-1)/СЧЁТЗ(C:C)/СЧЁТЗ(D:D));СЧЁТЗ(B:B));)&СМЕЩ(C$1;ОСТАТ(ОТБР((СТРОКА()-1)/СЧЁТЗ(D:D));СЧЁТЗ(C:C));)&СМЕЩ(D$1;ОСТАТ(СТРОКА()-1;СЧЁТЗ(D:D));)

А эту формулу можно протянуть по столбцам и получим все 64*32*64*16 сочетаний:
Код
=СМЕЩ($A$1;ОТБР(((СТРОКА()-1)+(СТОЛБЕЦ(A1)-1)*65536)/32/64/16);)&СМЕЩ($B$1;ОСТАТ(ОТБР(((СТРОКА()-1)+(СТОЛБЕЦ(A1)-1)*65536)/64/16);32);)&СМЕЩ($C$1;ОСТАТ(ОТБР(((СТРОКА()-1)+(СТОЛБЕЦ(A1)-1)*65536)/16);64);)&СМЕЩ($D$1;ОСТАТ(СТРОКА()-1;16);)

Автор - Светлый
Дата добавления - 02.11.2016 в 09:29
Yurasha Дата: Среда, 02.11.2016, 12:15 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 2
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Спасибо большое, Светлый!!!

Все супер, работает! Сейчас я попробую разобрать принцип действия этих формул. "Универсальная" решила эту задачу на все 100 процентов.

Спасибо еще раз! Удачи!!! =)
 
Ответить
СообщениеСпасибо большое, Светлый!!!

Все супер, работает! Сейчас я попробую разобрать принцип действия этих формул. "Универсальная" решила эту задачу на все 100 процентов.

Спасибо еще раз! Удачи!!! =)

Автор - Yurasha
Дата добавления - 02.11.2016 в 12:15
Светлый Дата: Среда, 02.11.2016, 13:18 | Сообщение № 4
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 226
Репутация: 56 ±
Замечаний: 0% ±

Excel 2007
Пожалуйста.
Но тему всё-таки надо переименовать. Вместо "макрос с циклом" написать "формулу".
И почитайте правила.
Успехов!
К сообщению приложен файл: 1537514.xls(96Kb)


Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Среда, 02.11.2016, 14:51
 
Ответить
СообщениеПожалуйста.
Но тему всё-таки надо переименовать. Вместо "макрос с циклом" написать "формулу".
И почитайте правила.
Успехов!

Автор - Светлый
Дата добавления - 02.11.2016 в 13:18
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Построить формулу с циклом для перебора вариантов сочетаний. (Формулы/Formulas)
Страница 1 из 11
Поиск:

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