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

Вход

Регистрация

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

 

= Мир MS Excel/Подстановка случайных значений из диапазона - Мир MS Excel

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

Excel 2013
Здравствуйте, уважаемые эксперты!
Подскажите, какой формулой можно собрать случайные кодировки из цифр и букв по заготовленным шаблонам.
Есть столбец с необходимыми буквами (L:L) и столбец с нужными цифрами (M:M)
Нужно, чтобы в строке 3 выдавалось значение по образцу (выделен зелёной заливкой) забирая в качестве основы значения высвоей вышестоящей ячейки.
То есть, если я меняю значение ячейки в строке 2, о расположенная под ним ячейка в строке 3 меняет наполнение.
Также, если я меняю буквы или цифры в столбцах - они подставляются в формулу.

ЗЫ. В строке 2 "Ц" - означает (Ц)ифру в формуле, а "Б" - соответственно (Б)укву.
При обновлении - значения меняются но из того же диапазона по той же формуле.

Заранее спасибо.
К сообщению приложен файл: 1448473.xlsx(9Kb)
 
Ответить
СообщениеЗдравствуйте, уважаемые эксперты!
Подскажите, какой формулой можно собрать случайные кодировки из цифр и букв по заготовленным шаблонам.
Есть столбец с необходимыми буквами (L:L) и столбец с нужными цифрами (M:M)
Нужно, чтобы в строке 3 выдавалось значение по образцу (выделен зелёной заливкой) забирая в качестве основы значения высвоей вышестоящей ячейки.
То есть, если я меняю значение ячейки в строке 2, о расположенная под ним ячейка в строке 3 меняет наполнение.
Также, если я меняю буквы или цифры в столбцах - они подставляются в формулу.

ЗЫ. В строке 2 "Ц" - означает (Ц)ифру в формуле, а "Б" - соответственно (Б)укву.
При обновлении - значения меняются но из того же диапазона по той же формуле.

Заранее спасибо.

Автор - Strateg_ru
Дата добавления - 23.02.2016 в 12:37
Roman777 Дата: Вторник, 23.02.2016, 14:11 | Сообщение № 2
Группа: Проверенные
Ранг: Ветеран
Сообщений: 703
Репутация: 75 ±
Замечаний: 0% ±

Excel 2007, Excel 2013
Strateg_ru, честно говоря, не знаю как можно устроить стандартными ф-ями. Вот ф-я, заданная пользователем:
[vba]
Код
Function st(Rng1 As Range, RngB As Range, RngC As Range) As String
Dim i&, i_n&, k&
Dim rng As Range
Dim BC() As String
Dim B() As String
Dim C() As String
Dim maxB&, maxC&
i_n = Len(Rng1)
ReDim BC(i_n)
For i = 1 To i_n
  BC(i) = Mid(Rng1, i, 1)
Next i
For Each rng In RngB
  k = k + 1
  ReDim Preserve B(k)
  B(k) = rng
Next rng
maxB = k
k = 0
For Each rng In RngC
  k = k + 1
  ReDim Preserve C(k)
  C(k) = rng
Next rng
maxC = k
For i = 1 To i_n
  If UCase(BC(i)) = "Б" Then
     st = st & B(Int((maxB) * Rnd + 1))
  ElseIf UCase(BC(i)) = "Ц" Then
     st = st & C(Int((maxC) * Rnd + 1))
  End If
Next i
End Function
[/vba]
Или так даже чуть-чуть будет получше:


Много чего не знаю!!!!

Сообщение отредактировал Roman777 - Вторник, 23.02.2016, 14:28
 
Ответить
СообщениеStrateg_ru, честно говоря, не знаю как можно устроить стандартными ф-ями. Вот ф-я, заданная пользователем:
[vba]
Код
Function st(Rng1 As Range, RngB As Range, RngC As Range) As String
Dim i&, i_n&, k&
Dim rng As Range
Dim BC() As String
Dim B() As String
Dim C() As String
Dim maxB&, maxC&
i_n = Len(Rng1)
ReDim BC(i_n)
For i = 1 To i_n
  BC(i) = Mid(Rng1, i, 1)
Next i
For Each rng In RngB
  k = k + 1
  ReDim Preserve B(k)
  B(k) = rng
Next rng
maxB = k
k = 0
For Each rng In RngC
  k = k + 1
  ReDim Preserve C(k)
  C(k) = rng
Next rng
maxC = k
For i = 1 To i_n
  If UCase(BC(i)) = "Б" Then
     st = st & B(Int((maxB) * Rnd + 1))
  ElseIf UCase(BC(i)) = "Ц" Then
     st = st & C(Int((maxC) * Rnd + 1))
  End If
Next i
End Function
[/vba]
Или так даже чуть-чуть будет получше:

Автор - Roman777
Дата добавления - 23.02.2016 в 14:11
Strateg_ru Дата: Вторник, 23.02.2016, 16:00 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 42
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Roman777, прошу прощения - эту штуку не пойму... (((
Хотелось бы обычной формулой.
Может как-то через Поиск позиции и если она ИСТИНА - то вставить от Индекс (я Индекс уже настроил в том же примере - под столбцом цифр и букв), а если ЛОЖЬ - то соседний индекс...
У меня просто это целиком в голову не влезает...
 
Ответить
СообщениеRoman777, прошу прощения - эту штуку не пойму... (((
Хотелось бы обычной формулой.
Может как-то через Поиск позиции и если она ИСТИНА - то вставить от Индекс (я Индекс уже настроил в том же примере - под столбцом цифр и букв), а если ЛОЖЬ - то соседний индекс...
У меня просто это целиком в голову не влезает...

Автор - Strateg_ru
Дата добавления - 23.02.2016 в 16:00
Roman777 Дата: Вторник, 23.02.2016, 16:21 | Сообщение № 4
Группа: Проверенные
Ранг: Ветеран
Сообщений: 703
Репутация: 75 ±
Замечаний: 0% ±

Excel 2007, Excel 2013
Strateg_ru, к сожалению, стандартными формулами, эту задачу, скорее всего не решить. Мб кто-то ещё подскажет. Я же Вам предложил User Defined Function (UDF), тоесть та же ф-я, но написанная пользователем. Но для этого необходимо код ВБА добавить в личную книгу макросов или модуль самой книги (для этого формат книги должен поддерживать макросы). Прилагаю Вам пример в формате ".xls". Тут я добавил макрос в саму книгу и использовал эту написанную ф-ю для решения Вашей задачи.
К сообщению приложен файл: 1448473.xls(34Kb)


Много чего не знаю!!!!
 
Ответить
СообщениеStrateg_ru, к сожалению, стандартными формулами, эту задачу, скорее всего не решить. Мб кто-то ещё подскажет. Я же Вам предложил User Defined Function (UDF), тоесть та же ф-я, но написанная пользователем. Но для этого необходимо код ВБА добавить в личную книгу макросов или модуль самой книги (для этого формат книги должен поддерживать макросы). Прилагаю Вам пример в формате ".xls". Тут я добавил макрос в саму книгу и использовал эту написанную ф-ю для решения Вашей задачи.

Автор - Roman777
Дата добавления - 23.02.2016 в 16:21
buchlotnik Дата: Вторник, 23.02.2016, 17:12 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 2049
Репутация: 613 ±
Замечаний: 0% ±

2010, 2013, 2016 RUS / ENG
вот такой монстрик B)
Код
=ЕСЛИ(ПСТР(B2;1;1)="Б";ИНДЕКС($L$1:$L$13;СЛУЧМЕЖДУ(1;13));ЕСЛИ(ПСТР(B2;1;1)="Ц";ИНДЕКС($M$1:$M$10;СЛУЧМЕЖДУ(1;10));""))&ЕСЛИ(ПСТР(B2;2;1)="Б";ИНДЕКС($L$1:$L$13;СЛУЧМЕЖДУ(1;13));ЕСЛИ(ПСТР(B2;2;1)="Ц";ИНДЕКС($M$1:$M$10;СЛУЧМЕЖДУ(1;10));""))&ЕСЛИ(ПСТР(B2;3;1)="Б";ИНДЕКС($L$1:$L$13;СЛУЧМЕЖДУ(1;13));ЕСЛИ(ПСТР(B2;3;1)="Ц";ИНДЕКС($M$1:$M$10;СЛУЧМЕЖДУ(1;10));""))&ЕСЛИ(ПСТР(B2;4;1)="Б";ИНДЕКС($L$1:$L$13;СЛУЧМЕЖДУ(1;13));ЕСЛИ(ПСТР(B2;4;1)="Ц";ИНДЕКС($M$1:$M$10;СЛУЧМЕЖДУ(1;10));""))&ЕСЛИ(ПСТР(B2;5;1)="Б";ИНДЕКС($L$1:$L$13;СЛУЧМЕЖДУ(1;13));ЕСЛИ(ПСТР(B2;5;1)="Ц";ИНДЕКС($M$1:$M$10;СЛУЧМЕЖДУ(1;10));""))


слегка упростил:
Код
=ЕСЛИ(ПСТР(B2;1;1)="Б";ИНДЕКС($L$1:$L$13;СЛУЧМЕЖДУ(1;13));ЕСЛИ(ПСТР(B2;1;1)="Ц";СЛУЧМЕЖДУ(0;9);""))&ЕСЛИ(ПСТР(B2;2;1)="Б";ИНДЕКС($L$1:$L$13;СЛУЧМЕЖДУ(1;13));ЕСЛИ(ПСТР(B2;2;1)="Ц";СЛУЧМЕЖДУ(0;9);""))&ЕСЛИ(ПСТР(B2;3;1)="Б";ИНДЕКС($L$1:$L$13;СЛУЧМЕЖДУ(1;13));ЕСЛИ(ПСТР(B2;3;1)="Ц";СЛУЧМЕЖДУ(0;9);""))&ЕСЛИ(ПСТР(B2;4;1)="Б";ИНДЕКС($L$1:$L$13;СЛУЧМЕЖДУ(1;13));ЕСЛИ(ПСТР(B2;4;1)="Ц";СЛУЧМЕЖДУ(0;9);""))&ЕСЛИ(ПСТР(B2;5;1)="Б";ИНДЕКС($L$1:$L$13;СЛУЧМЕЖДУ(1;13));ЕСЛИ(ПСТР(B2;5;1)="Ц";СЛУЧМЕЖДУ(0;9);""))


и до кучи с доп таблицей
Код
=ИНДЕКС($G$9:$I$13;1;ПОИСКПОЗ(ПСТР(B2;1;1);{"Б";"Ц";""};0))&ИНДЕКС($G$9:$I$13;2;ПОИСКПОЗ(ПСТР(B2;2;1);{"Б";"Ц";""};0))&ИНДЕКС($G$9:$I$13;3;ПОИСКПОЗ(ПСТР(B2;3;1);{"Б";"Ц";""};0))&ИНДЕКС($G$9:$I$13;4;ПОИСКПОЗ(ПСТР(B2;4;1);{"Б";"Ц";""};0))&ИНДЕКС($G$9:$I$13;5;ПОИСКПОЗ(ПСТР(B2;5;1);{"Б";"Ц";""};0))
К сообщению приложен файл: 2400992-2-1-.xlsx(10Kb)


платная помощь:
ЯД: 410012595572239; WM: 311017577133
buchlotnik@mail.ru


Сообщение отредактировал buchlotnik - Вторник, 23.02.2016, 20:42
 
Ответить
Сообщениевот такой монстрик B)
Код
=ЕСЛИ(ПСТР(B2;1;1)="Б";ИНДЕКС($L$1:$L$13;СЛУЧМЕЖДУ(1;13));ЕСЛИ(ПСТР(B2;1;1)="Ц";ИНДЕКС($M$1:$M$10;СЛУЧМЕЖДУ(1;10));""))&ЕСЛИ(ПСТР(B2;2;1)="Б";ИНДЕКС($L$1:$L$13;СЛУЧМЕЖДУ(1;13));ЕСЛИ(ПСТР(B2;2;1)="Ц";ИНДЕКС($M$1:$M$10;СЛУЧМЕЖДУ(1;10));""))&ЕСЛИ(ПСТР(B2;3;1)="Б";ИНДЕКС($L$1:$L$13;СЛУЧМЕЖДУ(1;13));ЕСЛИ(ПСТР(B2;3;1)="Ц";ИНДЕКС($M$1:$M$10;СЛУЧМЕЖДУ(1;10));""))&ЕСЛИ(ПСТР(B2;4;1)="Б";ИНДЕКС($L$1:$L$13;СЛУЧМЕЖДУ(1;13));ЕСЛИ(ПСТР(B2;4;1)="Ц";ИНДЕКС($M$1:$M$10;СЛУЧМЕЖДУ(1;10));""))&ЕСЛИ(ПСТР(B2;5;1)="Б";ИНДЕКС($L$1:$L$13;СЛУЧМЕЖДУ(1;13));ЕСЛИ(ПСТР(B2;5;1)="Ц";ИНДЕКС($M$1:$M$10;СЛУЧМЕЖДУ(1;10));""))


слегка упростил:
Код
=ЕСЛИ(ПСТР(B2;1;1)="Б";ИНДЕКС($L$1:$L$13;СЛУЧМЕЖДУ(1;13));ЕСЛИ(ПСТР(B2;1;1)="Ц";СЛУЧМЕЖДУ(0;9);""))&ЕСЛИ(ПСТР(B2;2;1)="Б";ИНДЕКС($L$1:$L$13;СЛУЧМЕЖДУ(1;13));ЕСЛИ(ПСТР(B2;2;1)="Ц";СЛУЧМЕЖДУ(0;9);""))&ЕСЛИ(ПСТР(B2;3;1)="Б";ИНДЕКС($L$1:$L$13;СЛУЧМЕЖДУ(1;13));ЕСЛИ(ПСТР(B2;3;1)="Ц";СЛУЧМЕЖДУ(0;9);""))&ЕСЛИ(ПСТР(B2;4;1)="Б";ИНДЕКС($L$1:$L$13;СЛУЧМЕЖДУ(1;13));ЕСЛИ(ПСТР(B2;4;1)="Ц";СЛУЧМЕЖДУ(0;9);""))&ЕСЛИ(ПСТР(B2;5;1)="Б";ИНДЕКС($L$1:$L$13;СЛУЧМЕЖДУ(1;13));ЕСЛИ(ПСТР(B2;5;1)="Ц";СЛУЧМЕЖДУ(0;9);""))


и до кучи с доп таблицей
Код
=ИНДЕКС($G$9:$I$13;1;ПОИСКПОЗ(ПСТР(B2;1;1);{"Б";"Ц";""};0))&ИНДЕКС($G$9:$I$13;2;ПОИСКПОЗ(ПСТР(B2;2;1);{"Б";"Ц";""};0))&ИНДЕКС($G$9:$I$13;3;ПОИСКПОЗ(ПСТР(B2;3;1);{"Б";"Ц";""};0))&ИНДЕКС($G$9:$I$13;4;ПОИСКПОЗ(ПСТР(B2;4;1);{"Б";"Ц";""};0))&ИНДЕКС($G$9:$I$13;5;ПОИСКПОЗ(ПСТР(B2;5;1);{"Б";"Ц";""};0))

Автор - buchlotnik
Дата добавления - 23.02.2016 в 17:12
Strateg_ru Дата: Вторник, 23.02.2016, 18:49 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 42
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
buchlotnik, да, действительно, монстрик)
Но вы - просто монстрище!))
Спасибо Вам огромное!!! Ни полный, ни оптимизированный вариант у меня даже в голову не укладывается. Но всё работает как надо! Супер!

Всем спасибо!
Тему можно закрывать.
 
Ответить
Сообщениеbuchlotnik, да, действительно, монстрик)
Но вы - просто монстрище!))
Спасибо Вам огромное!!! Ни полный, ни оптимизированный вариант у меня даже в голову не укладывается. Но всё работает как надо! Супер!

Всем спасибо!
Тему можно закрывать.

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

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