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

Вход

Регистрация

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

 

= Мир MS Excel/Подставить текст из массива данных - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подставить текст из массива данных (Формулы/Formulas)
Подставить текст из массива данных
mikeret Дата: Среда, 18.05.2022, 11:25 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 37
Репутация: 0 ±
Замечаний: 20% ±

Excel 2013
Перерыл форум, не нашел ответа
Как упростить формулу, когда используются множество вложенных фунций ПОДСТАВИТЬ?В примере я привел только 2 вложенных функции, но на практике у меня доходит до 100, возможно ли упростить с помощью других формул или формул массива?
Код
=ПОДСТАВИТЬ(ПОДСТАВИТЬ(C1;A1;B1);A2;B2)
К сообщению приложен файл: 0584566.xlsx(9.0 Kb)


Сообщение отредактировал Serge_007 - Среда, 18.05.2022, 11:32
 
Ответить
СообщениеПерерыл форум, не нашел ответа
Как упростить формулу, когда используются множество вложенных фунций ПОДСТАВИТЬ?В примере я привел только 2 вложенных функции, но на практике у меня доходит до 100, возможно ли упростить с помощью других формул или формул массива?
Код
=ПОДСТАВИТЬ(ПОДСТАВИТЬ(C1;A1;B1);A2;B2)

Автор - mikeret
Дата добавления - 18.05.2022 в 11:25
Serge_007 Дата: Среда, 18.05.2022, 11:42 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 15403
Репутация: 2536 ±
Замечаний: ±

Excel 2016
на практике у меня доходит до 100
Можете один реальный пример выложить на 5-7 вложений?


ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
на практике у меня доходит до 100
Можете один реальный пример выложить на 5-7 вложений?

Автор - Serge_007
Дата добавления - 18.05.2022 в 11:42
mikeret Дата: Среда, 18.05.2022, 12:04 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 37
Репутация: 0 ±
Замечаний: 20% ±

Excel 2013
Serge_007,
К сообщению приложен файл: 9902051.xlsx(9.5 Kb)
 
Ответить
СообщениеSerge_007,

Автор - mikeret
Дата добавления - 18.05.2022 в 12:04
Serge_007 Дата: Среда, 18.05.2022, 12:34 | Сообщение № 4
Группа: Админы
Ранг: Местный житель
Сообщений: 15403
Репутация: 2536 ±
Замечаний: ±

Excel 2016
Не универсально и неоптимально, но для примера подходит:
Код
="Марля"&ИНДЕКС(B$1:B$9;МИН(ЕСЛИ(ЕЧИСЛО(НАЙТИ(A$1:A$9;C1));СТРОКА($1:$9))))&ИНДЕКС(B$1:B$9;НАИМЕНЬШИЙ(ЕСЛИ(ЕЧИСЛО(НАЙТИ(A$1:A$9;C1));СТРОКА($1:$9));2))&ИНДЕКС(B$1:B$9;НАИМЕНЬШИЙ(ЕСЛИ(ЕЧИСЛО(НАЙТИ(A$1:A$9;C1));СТРОКА($1:$9));3))
К сообщению приложен файл: 20220518_mikere.xls(56.5 Kb)


ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
СообщениеНе универсально и неоптимально, но для примера подходит:
Код
="Марля"&ИНДЕКС(B$1:B$9;МИН(ЕСЛИ(ЕЧИСЛО(НАЙТИ(A$1:A$9;C1));СТРОКА($1:$9))))&ИНДЕКС(B$1:B$9;НАИМЕНЬШИЙ(ЕСЛИ(ЕЧИСЛО(НАЙТИ(A$1:A$9;C1));СТРОКА($1:$9));2))&ИНДЕКС(B$1:B$9;НАИМЕНЬШИЙ(ЕСЛИ(ЕЧИСЛО(НАЙТИ(A$1:A$9;C1));СТРОКА($1:$9));3))

Автор - Serge_007
Дата добавления - 18.05.2022 в 12:34
msi2102 Дата: Среда, 18.05.2022, 12:43 | Сообщение № 5
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 198
Репутация: 79 ±
Замечаний: 0% ±

Excel 2007
Сделайте макросом и не мучайтесь
[vba]
Код
Sub Repl()
Dim arr1 As Variant, arr2 As Variant, n As Long, m As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
arr1 = ActiveSheet.Range("A1:B" & lr)
lr = Cells(Rows.Count, 3).End(xlUp).Row
arr2 = ActiveSheet.Range("C1:C" & lr)
For n = LBound(arr2) To UBound(arr2)
    For m = LBound(arr1) To UBound(arr1)
        If InStr(arr2(n, 1), arr1(m, 1)) > 0 Then arr2(n, 1) = Replace(arr2(n, 1), arr1(m, 1), arr1(m, 2))
    Next m
Next n
ActiveSheet.Range("C1:C" & lr) = arr2
End Sub
[/vba]
К сообщению приложен файл: 9902051.xlsm(19.3 Kb)
 
Ответить
СообщениеСделайте макросом и не мучайтесь
[vba]
Код
Sub Repl()
Dim arr1 As Variant, arr2 As Variant, n As Long, m As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
arr1 = ActiveSheet.Range("A1:B" & lr)
lr = Cells(Rows.Count, 3).End(xlUp).Row
arr2 = ActiveSheet.Range("C1:C" & lr)
For n = LBound(arr2) To UBound(arr2)
    For m = LBound(arr1) To UBound(arr1)
        If InStr(arr2(n, 1), arr1(m, 1)) > 0 Then arr2(n, 1) = Replace(arr2(n, 1), arr1(m, 1), arr1(m, 2))
    Next m
Next n
ActiveSheet.Range("C1:C" & lr) = arr2
End Sub
[/vba]

Автор - msi2102
Дата добавления - 18.05.2022 в 12:43
mikeret Дата: Среда, 18.05.2022, 14:04 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 37
Репутация: 0 ±
Замечаний: 20% ±

Excel 2013
Serge_007, спасибо за труд, но я привел неудачный пример, исправляюсь. На практике, первые слова могут быть любые и некоторые слова в тексте остаются без замены., т.е. нужен универсальный вариант
К сообщению приложен файл: 7801341.xlsx(9.6 Kb)
 
Ответить
СообщениеSerge_007, спасибо за труд, но я привел неудачный пример, исправляюсь. На практике, первые слова могут быть любые и некоторые слова в тексте остаются без замены., т.е. нужен универсальный вариант

Автор - mikeret
Дата добавления - 18.05.2022 в 14:04
mikeret Дата: Среда, 18.05.2022, 14:08 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 37
Репутация: 0 ±
Замечаний: 20% ±

Excel 2013
msi2102, спасибо за труд, но я в макросах не силен, у вас вставляется результат в ячейки с исходными данными, если можно исправтьте, чтоб результат был в соседнем столбце
 
Ответить
Сообщениеmsi2102, спасибо за труд, но я в макросах не силен, у вас вставляется результат в ячейки с исходными данными, если можно исправтьте, чтоб результат был в соседнем столбце

Автор - mikeret
Дата добавления - 18.05.2022 в 14:08
msi2102 Дата: Среда, 18.05.2022, 14:31 | Сообщение № 8
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 198
Репутация: 79 ±
Замечаний: 0% ±

Excel 2007
Конечно замерите строку
[vba]
Код
ActiveSheet.Range("C1:C" & lr) = arr2
[/vba]
на
[vba]
Код
ActiveSheet.Range("D1:D" & lr) = arr2
[/vba]
К сообщению приложен файл: 4729575.xlsm(20.6 Kb)


Сообщение отредактировал msi2102 - Среда, 18.05.2022, 14:31
 
Ответить
СообщениеКонечно замерите строку
[vba]
Код
ActiveSheet.Range("C1:C" & lr) = arr2
[/vba]
на
[vba]
Код
ActiveSheet.Range("D1:D" & lr) = arr2
[/vba]

Автор - msi2102
Дата добавления - 18.05.2022 в 14:31
mikeret Дата: Среда, 18.05.2022, 15:41 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 37
Репутация: 0 ±
Замечаний: 20% ±

Excel 2013
msi2102, спасибо большее, все работает, но если кто поможет с формулой, тоже буду благодарен, с ними лично мне удобнее работать, хоть они и более массивные


Сообщение отредактировал mikeret - Среда, 18.05.2022, 16:20
 
Ответить
Сообщениеmsi2102, спасибо большее, все работает, но если кто поможет с формулой, тоже буду благодарен, с ними лично мне удобнее работать, хоть они и более массивные

Автор - mikeret
Дата добавления - 18.05.2022 в 15:41
mikeret Дата: Понедельник, 23.05.2022, 09:30 | Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 37
Репутация: 0 ±
Замечаний: 20% ±

Excel 2013
Возможно ли тогда если не упростить фоурмулу, то хотя бы ее унифицировать? В данном примере мне приходится многократно копировать правую часть формулы (";$A$2;$B$2)") и вручную менять потом ссылки на (";$A$3;$B$3)"), (";$A$4;$B$4)") и т.д. Помимо лишних затрат времени, самое плохое, что такой метод может привести к пропущенной нумерации и как следствие к неправильному результату. Можно ли эту часть формулы заменить при помощи вспомогательных функций (ИНДЕКС, СТРОКА и прочие), чтоб потом можно было просто ее продублировать в необходимом количестве ничего уже не меняя после копирования?
Код
=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(C2;$A$2;$B$2);$A$3;$B$3);$A$4;$B$4);$A$5;$B$5);$A$6;$B$6);$A$7;$B$7);$A$8;$B$8);$A$9;$B$9);$A$10;$B$10);$A$11;$B$11);$A$12;$B$12);$A$13;$B$13);$A$14;$B$14);$A$15;$B$15);$A$16;$B$16);$A$17;$B$17);$A$18;$B$18);$A$19;$B$19);$A$20;$B$20);$A$21;$B$21);$A$22;$B$22);$A$23;$B$23);$A$24;$B$24);$A$25;$B$25);$A$26;$B$26);$A$27;$B$27);$A$28;$B$28);$A$29;$B$29);$A$30;$B$30);$A$31;$B$31);$A$32;$B$32);$A$33;$B$33);$A$34;$B$34);$A$35;$B$35);$A$36;$B$36);$A$37;$B$37);$A$38;$B$38);$A$39;$B$39);$A$40;$B$40);$A$41;$B$41);$A$42;$B$42);$A$43;$B$43);$A$44;$B$44);$A$45;$B$45);$A$46;$B$46);$A$47;$B$47);$A$48;$B$48);$A$49;$B$49);$A$50;$B$50)
 
Ответить
СообщениеВозможно ли тогда если не упростить фоурмулу, то хотя бы ее унифицировать? В данном примере мне приходится многократно копировать правую часть формулы (";$A$2;$B$2)") и вручную менять потом ссылки на (";$A$3;$B$3)"), (";$A$4;$B$4)") и т.д. Помимо лишних затрат времени, самое плохое, что такой метод может привести к пропущенной нумерации и как следствие к неправильному результату. Можно ли эту часть формулы заменить при помощи вспомогательных функций (ИНДЕКС, СТРОКА и прочие), чтоб потом можно было просто ее продублировать в необходимом количестве ничего уже не меняя после копирования?
Код
=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(C2;$A$2;$B$2);$A$3;$B$3);$A$4;$B$4);$A$5;$B$5);$A$6;$B$6);$A$7;$B$7);$A$8;$B$8);$A$9;$B$9);$A$10;$B$10);$A$11;$B$11);$A$12;$B$12);$A$13;$B$13);$A$14;$B$14);$A$15;$B$15);$A$16;$B$16);$A$17;$B$17);$A$18;$B$18);$A$19;$B$19);$A$20;$B$20);$A$21;$B$21);$A$22;$B$22);$A$23;$B$23);$A$24;$B$24);$A$25;$B$25);$A$26;$B$26);$A$27;$B$27);$A$28;$B$28);$A$29;$B$29);$A$30;$B$30);$A$31;$B$31);$A$32;$B$32);$A$33;$B$33);$A$34;$B$34);$A$35;$B$35);$A$36;$B$36);$A$37;$B$37);$A$38;$B$38);$A$39;$B$39);$A$40;$B$40);$A$41;$B$41);$A$42;$B$42);$A$43;$B$43);$A$44;$B$44);$A$45;$B$45);$A$46;$B$46);$A$47;$B$47);$A$48;$B$48);$A$49;$B$49);$A$50;$B$50)

Автор - mikeret
Дата добавления - 23.05.2022 в 09:30
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подставить текст из массива данных (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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