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

 

= Мир MS Excel/Как сделать формулу без промежуточных столбцов. - Мир MS Excel

Старая форма входа
  • Страница 1 из 2
  • 1
  • 2
  • »
Модератор форума: китин, _Boroda_, DrMini  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Как сделать формулу без промежуточных столбцов. (Формулы/Formulas)
Как сделать формулу без промежуточных столбцов.
dmitrijaltman8 Дата: Суббота, 07.08.2021, 22:27 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 61
Репутация: 0 ±
Замечаний: 20% ±

2016
Здравствуйте.
Помогите решить непростой вопрос.

Работает формула массива:
=ЕСЛИ(ЕОШИБКА(ПОИСК("ttx456s";$D4&$E4))=ИСТИНА;"";ЕСЛИ(ЕОШИБКА($A4&$B4&$C4&ПСТР($D4;ПОИСК("ttx456s(";$D4&$E4;1)+3;ПОИСК(")";ПРАВСИМВ($D4&$E4;ДЛСТР($D4&$E4)-НАЙТИ("ttx456s";$D4&$E4)-1);1)-1));"";$A4&$B4&$C4&ПСТР($D4&$E4;ПОИСК("ttx456s(";$D4&$E4;1)+7;ПОИСК(")";ПРАВСИМВ($D4&$E4;ДЛСТР($D4&$E4)-НАЙТИ("ttx456s";$D4&$E4)-1);1)-4)))


Которая вытаскивает текст в зеленый столбец по ключевому слову ttx456s
То есть извлекается прилегающая к ключевому слову область, заключенная скобками и все что внутри этих скобок.

Но эта формула не очень хорошая, иногда текст извлекается неправильно. (например обрезается посередине и т.д.)

Потом в синем столбце другая формула - спрессовывает полученный результат.
Как сделать то же самое, без промежуточных столбцов, просто указав в формуле целевую ячейку с ключевым словом и диапазон где ведется поиск ?
К сообщению приложен файл: 7744541.xlsx (18.1 Kb)
 
Ответить
СообщениеЗдравствуйте.
Помогите решить непростой вопрос.

Работает формула массива:
=ЕСЛИ(ЕОШИБКА(ПОИСК("ttx456s";$D4&$E4))=ИСТИНА;"";ЕСЛИ(ЕОШИБКА($A4&$B4&$C4&ПСТР($D4;ПОИСК("ttx456s(";$D4&$E4;1)+3;ПОИСК(")";ПРАВСИМВ($D4&$E4;ДЛСТР($D4&$E4)-НАЙТИ("ttx456s";$D4&$E4)-1);1)-1));"";$A4&$B4&$C4&ПСТР($D4&$E4;ПОИСК("ttx456s(";$D4&$E4;1)+7;ПОИСК(")";ПРАВСИМВ($D4&$E4;ДЛСТР($D4&$E4)-НАЙТИ("ttx456s";$D4&$E4)-1);1)-4)))


Которая вытаскивает текст в зеленый столбец по ключевому слову ttx456s
То есть извлекается прилегающая к ключевому слову область, заключенная скобками и все что внутри этих скобок.

Но эта формула не очень хорошая, иногда текст извлекается неправильно. (например обрезается посередине и т.д.)

Потом в синем столбце другая формула - спрессовывает полученный результат.
Как сделать то же самое, без промежуточных столбцов, просто указав в формуле целевую ячейку с ключевым словом и диапазон где ведется поиск ?
Автор - dmitrijaltman8
Дата добавления - 07.08.2021 в 22:27
Pelena Дата: Воскресенье, 08.08.2021, 07:54 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 19511
Репутация: 4620 ±
Замечаний: ±

Excel 365 & Mac Excel
Здравствуйте.
=ЕСЛИОШИБКА(ИНДЕКС(ЕСЛИОШИБКА($A$4:$A$2000&$B$4:$B$2000&$C$4:$C$2000&ПСТР($D$4:$D$2000&$E$4:$E$2000;НАЙТИ($N$3&"(";$D$4:$D$2000&$E$4:$E$2000;1)+ДЛСТР($N$3&"(")-1;ПОИСК(")";$D$4:$D$2000&$E$4:$E$2000;НАЙТИ($N$3&"(";$D$4:$D$2000&$E$4:$E$2000;1))-НАЙТИ($N$3&"(";$D$4:$D$2000&$E$4:$E$2000;1)-ДЛСТР($N$3&"(")+3);"");НАИМЕНЬШИЙ(ЕСЛИ(ЕСЛИОШИБКА($A$4:$A$2000&$B$4:$B$2000&$C$4:$C$2000&ПСТР($D$4:$D$2000&$E$4:$E$2000;НАЙТИ($N$3&"(";$D$4:$D$2000&$E$4:$E$2000;1)+ДЛСТР($N$3&"(")-1;ПОИСК(")";$D$4:$D$2000&$E$4:$E$2000;НАЙТИ($N$3&"(";$D$4:$D$2000&$E$4:$E$2000;1))-НАЙТИ($N$3&"(";$D$4:$D$2000&$E$4:$E$2000;1)-ДЛСТР($N$3&"(")+3);"")<>"";СТРОКА($A$4:$A$2000)-3);СТРОКА(A1)));"")
К сообщению приложен файл: 3444728.xlsx (16.4 Kb)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеЗдравствуйте.
=ЕСЛИОШИБКА(ИНДЕКС(ЕСЛИОШИБКА($A$4:$A$2000&$B$4:$B$2000&$C$4:$C$2000&ПСТР($D$4:$D$2000&$E$4:$E$2000;НАЙТИ($N$3&"(";$D$4:$D$2000&$E$4:$E$2000;1)+ДЛСТР($N$3&"(")-1;ПОИСК(")";$D$4:$D$2000&$E$4:$E$2000;НАЙТИ($N$3&"(";$D$4:$D$2000&$E$4:$E$2000;1))-НАЙТИ($N$3&"(";$D$4:$D$2000&$E$4:$E$2000;1)-ДЛСТР($N$3&"(")+3);"");НАИМЕНЬШИЙ(ЕСЛИ(ЕСЛИОШИБКА($A$4:$A$2000&$B$4:$B$2000&$C$4:$C$2000&ПСТР($D$4:$D$2000&$E$4:$E$2000;НАЙТИ($N$3&"(";$D$4:$D$2000&$E$4:$E$2000;1)+ДЛСТР($N$3&"(")-1;ПОИСК(")";$D$4:$D$2000&$E$4:$E$2000;НАЙТИ($N$3&"(";$D$4:$D$2000&$E$4:$E$2000;1))-НАЙТИ($N$3&"(";$D$4:$D$2000&$E$4:$E$2000;1)-ДЛСТР($N$3&"(")+3);"")<>"";СТРОКА($A$4:$A$2000)-3);СТРОКА(A1)));"")
Автор - Pelena
Дата добавления - 08.08.2021 в 07:54
bmv98rus Дата: Воскресенье, 08.08.2021, 08:50 | Сообщение № 3
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4147
Репутация: 772 ±
Замечаний: 0% ±

Excel 2013/2016
Лена, у тебя подглядел немного, сам чёто не до пер с утра что можно объединить источники
=ЕСЛИОШИБКА(ИНДЕКС($A$4:$A$73&$B$4:$B$73&$C$4:$C$73&ЕСЛИОШИБКА(СЖПРОБЕЛЫ(ЛЕВСИМВ(ПОДСТАВИТЬ(ПСТР($D$4:$D$73&$E$4:$E$73;НАЙТИ($X$3&"(";$D$4:$D$73&$E$4:$E$73)+ДЛСТР($X$3);999);"),";ПОВТОР(" ";999);1);999))&"),";"");НАИМЕНЬШИЙ(ЕСЛИ(ЕСЛИОШИБКА(СЖПРОБЕЛЫ(ЛЕВСИМВ(ПОДСТАВИТЬ(ПСТР($D$4:$D$73&$E$4:$E$73;НАЙТИ($X$3&"(";$D$4:$D$73&$E$4:$E$73)+ДЛСТР($X$3);999);"),";ПОВТОР(" ";999);1);999))&"),";"")<>"";СТРОКА($D$4:$D$73)-3);ЧСТРОК($Y$4:Y4)));"")
К сообщению приложен файл: example2387.xlsx (21.3 Kb)

Замечательный Временно просто медведь , процентов на 20.
 
Ответить
СообщениеЛена, у тебя подглядел немного, сам чёто не до пер с утра что можно объединить источники
=ЕСЛИОШИБКА(ИНДЕКС($A$4:$A$73&$B$4:$B$73&$C$4:$C$73&ЕСЛИОШИБКА(СЖПРОБЕЛЫ(ЛЕВСИМВ(ПОДСТАВИТЬ(ПСТР($D$4:$D$73&$E$4:$E$73;НАЙТИ($X$3&"(";$D$4:$D$73&$E$4:$E$73)+ДЛСТР($X$3);999);"),";ПОВТОР(" ";999);1);999))&"),";"");НАИМЕНЬШИЙ(ЕСЛИ(ЕСЛИОШИБКА(СЖПРОБЕЛЫ(ЛЕВСИМВ(ПОДСТАВИТЬ(ПСТР($D$4:$D$73&$E$4:$E$73;НАЙТИ($X$3&"(";$D$4:$D$73&$E$4:$E$73)+ДЛСТР($X$3);999);"),";ПОВТОР(" ";999);1);999))&"),";"")<>"";СТРОКА($D$4:$D$73)-3);ЧСТРОК($Y$4:Y4)));"")
Автор - bmv98rus
Дата добавления - 08.08.2021 в 08:50
прохожий2019 Дата: Воскресенье, 08.08.2021, 09:55 | Сообщение № 4
Группа: Проверенные
Ранг: Старожил
Сообщений: 1395
Репутация: 364 ±
Замечаний: 0% ±

365 Beta Channel
всегда хотелось обе функции объединить...
=ФИЛЬТР(A4:A73&B4:B73&C4:C73&ФИЛЬТР.XML("< j>< i>"&ПОДСТАВИТЬ(ПОДСТАВИТЬ(D4:D73&E$4:E73;"(";"< /i>< i>(");")";")< /i>< i>")&"< /i>< /j>";"//i[substring(preceding::*[1], string-length(preceding::*[1]) - string-length('"&W3&"') +1) = '"&W3&"']");ЕСЛИОШИБКА(ПОИСК(W3&"(*)";D4:D73&E$4:E73);))
чёт не проснулся ещё
=ФИЛЬТР(A4:A73&B4:B73&C4:C73&ФИЛЬТР.XML("< j>< i>"&ПОДСТАВИТЬ(ПОДСТАВИТЬ(D4:D73&E$4:E73;"(";"@< /i>< i>(");")";")< /i>< i>")&"< /i>< /j>";"//i[contains(preceding::*[1],'"&W3&"@')]");ЕСЛИОШИБКА(ПОИСК(W3&"(*)";D4:D73&E$4:E73);))
К сообщению приложен файл: 7744541-1-.xlsx (19.4 Kb)


Сообщение отредактировал прохожий2019 - Воскресенье, 08.08.2021, 10:09
 
Ответить
Сообщениевсегда хотелось обе функции объединить...
=ФИЛЬТР(A4:A73&B4:B73&C4:C73&ФИЛЬТР.XML("< j>< i>"&ПОДСТАВИТЬ(ПОДСТАВИТЬ(D4:D73&E$4:E73;"(";"< /i>< i>(");")";")< /i>< i>")&"< /i>< /j>";"//i[substring(preceding::*[1], string-length(preceding::*[1]) - string-length('"&W3&"') +1) = '"&W3&"']");ЕСЛИОШИБКА(ПОИСК(W3&"(*)";D4:D73&E$4:E73);))
чёт не проснулся ещё
=ФИЛЬТР(A4:A73&B4:B73&C4:C73&ФИЛЬТР.XML("< j>< i>"&ПОДСТАВИТЬ(ПОДСТАВИТЬ(D4:D73&E$4:E73;"(";"@< /i>< i>(");")";")< /i>< i>")&"< /i>< /j>";"//i[contains(preceding::*[1],'"&W3&"@')]");ЕСЛИОШИБКА(ПОИСК(W3&"(*)";D4:D73&E$4:E73);))
Автор - прохожий2019
Дата добавления - 08.08.2021 в 09:55
dmitrijaltman8 Дата: Воскресенье, 08.08.2021, 12:01 | Сообщение № 5
Группа: Пользователи
Ранг: Участник
Сообщений: 61
Репутация: 0 ±
Замечаний: 20% ±

2016
прохожий2019, фот эта последняя формула - почему-то не работает.
Выдает ошибку #ИМЯ
 
Ответить
Сообщениепрохожий2019, фот эта последняя формула - почему-то не работает.
Выдает ошибку #ИМЯ
Автор - dmitrijaltman8
Дата добавления - 08.08.2021 в 12:01
прохожий2019 Дата: Воскресенье, 08.08.2021, 12:13 | Сообщение № 6
Группа: Проверенные
Ранг: Старожил
Сообщений: 1395
Репутация: 364 ±
Замечаний: 0% ±

365 Beta Channel
Цитата dmitrijaltman8, 08.08.2021 в 12:01, в сообщении № 5 ( писал(а)):
почему-то не работает

Потому что ФИЛЬТР() появилась в июле прошлого года в 365 офисе
 
Ответить
Сообщение
Цитата dmitrijaltman8, 08.08.2021 в 12:01, в сообщении № 5 ( писал(а)):
почему-то не работает

Потому что ФИЛЬТР() появилась в июле прошлого года в 365 офисе
Автор - прохожий2019
Дата добавления - 08.08.2021 в 12:13
dmitrijaltman8 Дата: Воскресенье, 08.08.2021, 12:27 | Сообщение № 7
Группа: Пользователи
Ранг: Участник
Сообщений: 61
Репутация: 0 ±
Замечаний: 20% ±

2016
Pelena, bmv98rus, обе формулы - почему-то находят не все результаты.

Моя формула нашла 308 результатов.
А эти формулы - находят всего 196 результатов.
 
Ответить
СообщениеPelena, bmv98rus, обе формулы - почему-то находят не все результаты.

Моя формула нашла 308 результатов.
А эти формулы - находят всего 196 результатов.
Автор - dmitrijaltman8
Дата добавления - 08.08.2021 в 12:27
bmv98rus Дата: Воскресенье, 08.08.2021, 12:43 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4147
Репутация: 772 ±
Замечаний: 0% ±

Excel 2013/2016
Цитата dmitrijaltman8, 08.08.2021 в 12:27, в сообщении № 7 ( писал(а)):
А эти формулы - находят всего 196 результатов.
что уже странно ибо более 69 найти не должно :-). Диапазоны изменили ? Как формула массива ввели?

Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщение
Цитата dmitrijaltman8, 08.08.2021 в 12:27, в сообщении № 7 ( писал(а)):
А эти формулы - находят всего 196 результатов.
что уже странно ибо более 69 найти не должно :-). Диапазоны изменили ? Как формула массива ввели?
Автор - bmv98rus
Дата добавления - 08.08.2021 в 12:43
dmitrijaltman8 Дата: Воскресенье, 08.08.2021, 13:08 | Сообщение № 9
Группа: Пользователи
Ранг: Участник
Сообщений: 61
Репутация: 0 ±
Замечаний: 20% ±

2016
bmv98rus, да, конечно.
Диапазон протянул вниз на 9000 строк.
Используется формула массива.
Но все равно не находит 308 результатов - только 196
 
Ответить
Сообщениеbmv98rus, да, конечно.
Диапазон протянул вниз на 9000 строк.
Используется формула массива.
Но все равно не находит 308 результатов - только 196
Автор - dmitrijaltman8
Дата добавления - 08.08.2021 в 13:08
Светлый Дата: Воскресенье, 08.08.2021, 13:16 | Сообщение № 10
Группа: Друзья
Ранг: Старожил
Сообщений: 1867
Репутация: 536 ±
Замечаний: 0% ±

Excel 2013, 2016
У меня такая массивная формула получилась:
=ЕСЛИОШИБКА(ИНДЕКС(A$4:A$703&B$4:B$703&C$4:C$703;НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(D$4:D$703&E$4:E$703;СТОЛБЕЦ(A:ALK);ДЛСТР(N$3)+1)=N$3&"(";СТРОКА($1:$700)*1000+СТОЛБЕЦ(A:ALK));СТРОКА(X1))/1000)&СЖПРОБЕЛЫ(ЛЕВБ(ПОДСТАВИТЬ(ПСТР(ИНДЕКС(D$4:D$703&E$4:E$703;НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(D$4:D$703&E$4:E$703;СТОЛБЕЦ(A:ALK);ДЛСТР(N$3)+1)=N$3&"(";СТРОКА($1:$700)*1000+СТОЛБЕЦ(A:ALK));СТРОКА(X1))/1000);ПРАВБ(НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(D$4:D$703&E$4:E$703;СТОЛБЕЦ(A:ALK);ДЛСТР(N$3)+1)=N$3&"(";СТРОКА($1:$700)*1000+СТОЛБЕЦ(A:ALK));СТРОКА(X1));3)+ДЛСТР(W$3);99);")";")"&ПОВТОР(" ";99));99));"")
На примере находит больше значений, чем предыдущие.
*Подправил формулу под большую длину строк
**
Цитата dmitrijaltman8, 08.08.2021 в 13:08, в сообщении № 9 ( писал(а)):
Но все равно не находит 308 результатов - только 196
В данных я увидел искомые образцы, не привязанные к шаблону. Т.е. после образца идёт не "(", а ",),". Наши формулы не берут их во внимание.
В одной строке может быть несколько включений образца. Моя формула находит все.

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

Сообщение отредактировал Светлый - Воскресенье, 08.08.2021, 13:42
 
Ответить
СообщениеУ меня такая массивная формула получилась:
=ЕСЛИОШИБКА(ИНДЕКС(A$4:A$703&B$4:B$703&C$4:C$703;НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(D$4:D$703&E$4:E$703;СТОЛБЕЦ(A:ALK);ДЛСТР(N$3)+1)=N$3&"(";СТРОКА($1:$700)*1000+СТОЛБЕЦ(A:ALK));СТРОКА(X1))/1000)&СЖПРОБЕЛЫ(ЛЕВБ(ПОДСТАВИТЬ(ПСТР(ИНДЕКС(D$4:D$703&E$4:E$703;НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(D$4:D$703&E$4:E$703;СТОЛБЕЦ(A:ALK);ДЛСТР(N$3)+1)=N$3&"(";СТРОКА($1:$700)*1000+СТОЛБЕЦ(A:ALK));СТРОКА(X1))/1000);ПРАВБ(НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(D$4:D$703&E$4:E$703;СТОЛБЕЦ(A:ALK);ДЛСТР(N$3)+1)=N$3&"(";СТРОКА($1:$700)*1000+СТОЛБЕЦ(A:ALK));СТРОКА(X1));3)+ДЛСТР(W$3);99);")";")"&ПОВТОР(" ";99));99));"")
На примере находит больше значений, чем предыдущие.
*Подправил формулу под большую длину строк
**
Цитата dmitrijaltman8, 08.08.2021 в 13:08, в сообщении № 9 ( писал(а)):
Но все равно не находит 308 результатов - только 196
В данных я увидел искомые образцы, не привязанные к шаблону. Т.е. после образца идёт не "(", а ",),". Наши формулы не берут их во внимание.
В одной строке может быть несколько включений образца. Моя формула находит все.
Автор - Светлый
Дата добавления - 08.08.2021 в 13:16
bmv98rus Дата: Воскресенье, 08.08.2021, 13:29 | Сообщение № 11
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4147
Репутация: 772 ±
Замечаний: 0% ±

Excel 2013/2016
Цитата Светлый, 08.08.2021 в 13:16, в сообщении № 10 ( писал(а)):
На примере находит больше значений, чем предыдущие.
хм, даже больше чем в исходных данных?

Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщение
Цитата Светлый, 08.08.2021 в 13:16, в сообщении № 10 ( писал(а)):
На примере находит больше значений, чем предыдущие.
хм, даже больше чем в исходных данных?
Автор - bmv98rus
Дата добавления - 08.08.2021 в 13:29
Pelena Дата: Воскресенье, 08.08.2021, 13:30 | Сообщение № 12
Группа: Админы
Ранг: Местный житель
Сообщений: 19511
Репутация: 4620 ±
Замечаний: ±

Excel 365 & Mac Excel
Цитата dmitrijaltman8, 08.08.2021 в 13:08, в сообщении № 9 ( писал(а)):
Диапазон протянул вниз на 9000 строк
в формуле исходные данные подтягиваются из 2000 строк. Замените везде в формуле 2000 на нужное число

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение
Цитата dmitrijaltman8, 08.08.2021 в 13:08, в сообщении № 9 ( писал(а)):
Диапазон протянул вниз на 9000 строк
в формуле исходные данные подтягиваются из 2000 строк. Замените везде в формуле 2000 на нужное число
Автор - Pelena
Дата добавления - 08.08.2021 в 13:30
Светлый Дата: Воскресенье, 08.08.2021, 13:48 | Сообщение № 13
Группа: Друзья
Ранг: Старожил
Сообщений: 1867
Репутация: 536 ±
Замечаний: 0% ±

Excel 2013, 2016
Цитата bmv98rus, 08.08.2021 в 13:29, в сообщении № 11 ( писал(а)):
даже больше чем в исходных данных?
Конечно же нет. В первоначальном файле Ваши формулы нашли 8 значений, моя 9. Ещё 111(343097845 34dfg fd7,).

Программировать проще, чем писать стихи.
 
Ответить
Сообщение
Цитата bmv98rus, 08.08.2021 в 13:29, в сообщении № 11 ( писал(а)):
даже больше чем в исходных данных?
Конечно же нет. В первоначальном файле Ваши формулы нашли 8 значений, моя 9. Ещё 111(343097845 34dfg fd7,).
Автор - Светлый
Дата добавления - 08.08.2021 в 13:48
dmitrijaltman8 Дата: Воскресенье, 08.08.2021, 13:49 | Сообщение № 14
Группа: Пользователи
Ранг: Участник
Сообщений: 61
Репутация: 0 ±
Замечаний: 20% ±

2016
Светлый, спасибо.
Пока не могу сказать работает или нет.
Поставил считать 9000 строк (два столбца) и формула медленно-медленно стала считать.
Пока за 5 минут - сделано 10% - это очень долго.

Мои формулы выглядят вот так:
=ЕСЛИ(ЕОШИБКА(ПОИСК("ttx456s";$D4))=ИСТИНА;"";ЕСЛИ(ЕОШИБКА($A4&$B4&$C4&ПСТР($D4;ПОИСК("ttx456s(";$D4;1)+3;ПОИСК(")";ПРАВСИМВ($D4;ДЛСТР($D4)-НАЙТИ("ttx456s";$D4)-1);1)-1));"";$A4&$B4&$C4&ПСТР($D4;ПОИСК("ttx456s(";$D4;1)+7;ПОИСК(")";ПРАВСИМВ($D4;ДЛСТР($D4)-НАЙТИ("ttx456s";$D4)-1);1)-4)))

Для каждого из двух столбцов с данными.

Потом для двух этих столбцов - идет сортировка без пропусков:
=ЕСЛИОШИБКА(ИНДЕКС($A$4:$A$9073&$B$4:$B$9073&$C$4:$C$9073&ЕСЛИОШИБКА(СЖПРОБЕЛЫ(ЛЕВСИМВ(ПОДСТАВИТЬ(ПСТР($D$4:$D$9073&$E$4:$E$9073;НАЙТИ($N$3&"(";$D$4:$D$9073&$E$4:$E$9073)+ДЛСТР($N$3);999);"),";ПОВТОР(" ";999);1);999))&"),";"");НАИМЕНЬШИЙ(ЕСЛИ(ЕСЛИОШИБКА(СЖПРОБЕЛЫ(ЛЕВСИМВ(ПОДСТАВИТЬ(ПСТР($D$4:$D$9073&$E$4:$E$9073;НАЙТИ($N$3&"(";$D$4:$D$9073&$E$4:$E$9073)+ДЛСТР($N$3);999);"),";ПОВТОР(" ";999);1);999))&"),";"")<>"";СТРОКА($D$4:$D$9073)-3);ЧСТРОК($O$4:O4)));"")

Тоже получается два столбца - с результатами.
Работает - секунд за 30 и находит все без исключения результаты.

Хотя бы - как в формуле - вместо ttx456s - записать ссылку на ячейку с этим словом: N3 ?
(там же еще идет упоминание "ttx456s(" - со скобкой)


Сообщение отредактировал dmitrijaltman8 - Воскресенье, 08.08.2021, 14:09
 
Ответить
СообщениеСветлый, спасибо.
Пока не могу сказать работает или нет.
Поставил считать 9000 строк (два столбца) и формула медленно-медленно стала считать.
Пока за 5 минут - сделано 10% - это очень долго.

Мои формулы выглядят вот так:
=ЕСЛИ(ЕОШИБКА(ПОИСК("ttx456s";$D4))=ИСТИНА;"";ЕСЛИ(ЕОШИБКА($A4&$B4&$C4&ПСТР($D4;ПОИСК("ttx456s(";$D4;1)+3;ПОИСК(")";ПРАВСИМВ($D4;ДЛСТР($D4)-НАЙТИ("ttx456s";$D4)-1);1)-1));"";$A4&$B4&$C4&ПСТР($D4;ПОИСК("ttx456s(";$D4;1)+7;ПОИСК(")";ПРАВСИМВ($D4;ДЛСТР($D4)-НАЙТИ("ttx456s";$D4)-1);1)-4)))

Для каждого из двух столбцов с данными.

Потом для двух этих столбцов - идет сортировка без пропусков:
=ЕСЛИОШИБКА(ИНДЕКС($A$4:$A$9073&$B$4:$B$9073&$C$4:$C$9073&ЕСЛИОШИБКА(СЖПРОБЕЛЫ(ЛЕВСИМВ(ПОДСТАВИТЬ(ПСТР($D$4:$D$9073&$E$4:$E$9073;НАЙТИ($N$3&"(";$D$4:$D$9073&$E$4:$E$9073)+ДЛСТР($N$3);999);"),";ПОВТОР(" ";999);1);999))&"),";"");НАИМЕНЬШИЙ(ЕСЛИ(ЕСЛИОШИБКА(СЖПРОБЕЛЫ(ЛЕВСИМВ(ПОДСТАВИТЬ(ПСТР($D$4:$D$9073&$E$4:$E$9073;НАЙТИ($N$3&"(";$D$4:$D$9073&$E$4:$E$9073)+ДЛСТР($N$3);999);"),";ПОВТОР(" ";999);1);999))&"),";"")<>"";СТРОКА($D$4:$D$9073)-3);ЧСТРОК($O$4:O4)));"")

Тоже получается два столбца - с результатами.
Работает - секунд за 30 и находит все без исключения результаты.

Хотя бы - как в формуле - вместо ttx456s - записать ссылку на ячейку с этим словом: N3 ?
(там же еще идет упоминание "ttx456s(" - со скобкой)
Автор - dmitrijaltman8
Дата добавления - 08.08.2021 в 13:49
bmv98rus Дата: Воскресенье, 08.08.2021, 13:55 | Сообщение № 15
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4147
Репутация: 772 ±
Замечаний: 0% ±

Excel 2013/2016
Цитата Светлый, 08.08.2021 в 13:48, в сообщении № 13 ( писал(а)):
Ваши формулы нашли 8 значений, моя 9
ну как бы это вопрос к ТС, ибо в #1 заложено нахождение только одного значения, для одной строки.

Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщение
Цитата Светлый, 08.08.2021 в 13:48, в сообщении № 13 ( писал(а)):
Ваши формулы нашли 8 значений, моя 9
ну как бы это вопрос к ТС, ибо в #1 заложено нахождение только одного значения, для одной строки.
Автор - bmv98rus
Дата добавления - 08.08.2021 в 13:55
Светлый Дата: Воскресенье, 08.08.2021, 14:03 | Сообщение № 16
Группа: Друзья
Ранг: Старожил
Сообщений: 1867
Репутация: 536 ±
Замечаний: 0% ±

Excel 2013, 2016
Цитата dmitrijaltman8, 08.08.2021 в 13:49, в сообщении № 14 ( писал(а)):
Пока за 5 минут - сделано 10% - это очень долго
Можно уменьшить диапазон СТОЛБЕЦ(A:ALK) до СТОЛБЕЦ(A:GR), будет считать быстрее, но сумма длин ячеек в строке не должна превышать 200.
*
Цитата bmv98rus, 08.08.2021 в 13:55, в сообщении № 15 ( писал(а)):
только одного значения, для одной строки
Я взял на себя инициативу искать все включения в каждой строке, т.к. в исходных данных увидел, что встречается по два раза.

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

Сообщение отредактировал Светлый - Воскресенье, 08.08.2021, 14:07
 
Ответить
Сообщение
Цитата dmitrijaltman8, 08.08.2021 в 13:49, в сообщении № 14 ( писал(а)):
Пока за 5 минут - сделано 10% - это очень долго
Можно уменьшить диапазон СТОЛБЕЦ(A:ALK) до СТОЛБЕЦ(A:GR), будет считать быстрее, но сумма длин ячеек в строке не должна превышать 200.
*
Цитата bmv98rus, 08.08.2021 в 13:55, в сообщении № 15 ( писал(а)):
только одного значения, для одной строки
Я взял на себя инициативу искать все включения в каждой строке, т.к. в исходных данных увидел, что встречается по два раза.
Автор - Светлый
Дата добавления - 08.08.2021 в 14:03
dmitrijaltman8 Дата: Воскресенье, 08.08.2021, 14:13 | Сообщение № 17
Группа: Пользователи
Ранг: Участник
Сообщений: 61
Репутация: 0 ±
Замечаний: 20% ±

2016
Цитата Pelena, 08.08.2021 в 13:30, в сообщении № 12 ( писал(а)):
в формуле исходные данные подтягиваются из 2000 строк. Замените везде в формуле 2000 на нужное число


Это я уже менял.
 
Ответить
Сообщение
Цитата Pelena, 08.08.2021 в 13:30, в сообщении № 12 ( писал(а)):
в формуле исходные данные подтягиваются из 2000 строк. Замените везде в формуле 2000 на нужное число


Это я уже менял.
Автор - dmitrijaltman8
Дата добавления - 08.08.2021 в 14:13
dmitrijaltman8 Дата: Воскресенье, 08.08.2021, 14:54 | Сообщение № 18
Группа: Пользователи
Ранг: Участник
Сообщений: 61
Репутация: 0 ±
Замечаний: 20% ±

2016
Цитата Светлый, 08.08.2021 в 14:03, в сообщении № 16 ( писал(а)):
Можно уменьшить диапазон СТОЛБЕЦ(A:ALK) до СТОЛБЕЦ(A:GR), будет считать быстрее, но сумма длин ячеек в строке не должна превышать 200.

Не работает.
Во-первых считает быстрее, но все равно около 10минут.
Во-вторых - эта формула тоже находит только 196 результатов, вместо 306.

Как поменять вот эту формулу в имеющемся файле - в столбце O ?:
=ЕСЛИ(ЕОШИБКА(ПОИСК("ttx456s";$D4&$E4))=ИСТИНА;"";ЕСЛИ(ЕОШИБКА($A4&$B4&$C4&ПСТР($D4;ПОИСК("ttx456s(";$D4&$E4;1)+3;ПОИСК(")";ПРАВСИМВ($D4&$E4;ДЛСТР($D4&$E4)-НАЙТИ("ttx456s";$D4&$E4)-1);1)-1));"";$A4&$B4&$C4&ПСТР($D4&$E4;ПОИСК("ttx456s(";$D4&$E4;1)+7;ПОИСК(")";ПРАВСИМВ($D4&$E4;ДЛСТР($D4&$E4)-НАЙТИ("ttx456s";$D4&$E4)-1);1)-4)))


Она как раз считает быстро и находит столько результатов - сколько и должно быть.

Но сейчас приходится туда текст вот этот вбивать "ttx456s" - каждый раз когда он меняется.
Чтобы эта формула просто ссылалась на ячейку N3, а не вбивать туда текст каждый раз.
(а промежуточный столбец R - без изменений. Буду использовать его)
 
Ответить
Сообщение
Цитата Светлый, 08.08.2021 в 14:03, в сообщении № 16 ( писал(а)):
Можно уменьшить диапазон СТОЛБЕЦ(A:ALK) до СТОЛБЕЦ(A:GR), будет считать быстрее, но сумма длин ячеек в строке не должна превышать 200.

Не работает.
Во-первых считает быстрее, но все равно около 10минут.
Во-вторых - эта формула тоже находит только 196 результатов, вместо 306.

Как поменять вот эту формулу в имеющемся файле - в столбце O ?:
=ЕСЛИ(ЕОШИБКА(ПОИСК("ttx456s";$D4&$E4))=ИСТИНА;"";ЕСЛИ(ЕОШИБКА($A4&$B4&$C4&ПСТР($D4;ПОИСК("ttx456s(";$D4&$E4;1)+3;ПОИСК(")";ПРАВСИМВ($D4&$E4;ДЛСТР($D4&$E4)-НАЙТИ("ttx456s";$D4&$E4)-1);1)-1));"";$A4&$B4&$C4&ПСТР($D4&$E4;ПОИСК("ttx456s(";$D4&$E4;1)+7;ПОИСК(")";ПРАВСИМВ($D4&$E4;ДЛСТР($D4&$E4)-НАЙТИ("ttx456s";$D4&$E4)-1);1)-4)))


Она как раз считает быстро и находит столько результатов - сколько и должно быть.

Но сейчас приходится туда текст вот этот вбивать "ttx456s" - каждый раз когда он меняется.
Чтобы эта формула просто ссылалась на ячейку N3, а не вбивать туда текст каждый раз.
(а промежуточный столбец R - без изменений. Буду использовать его)
Автор - dmitrijaltman8
Дата добавления - 08.08.2021 в 14:54
Pelena Дата: Воскресенье, 08.08.2021, 15:10 | Сообщение № 19
Группа: Админы
Ранг: Местный житель
Сообщений: 19511
Репутация: 4620 ±
Замечаний: ±

Excel 365 & Mac Excel
Цитата dmitrijaltman8, 08.08.2021 в 14:54, в сообщении № 19 ( писал(а)):
просто ссылалась на ячейку N3

в моём файле в столбце О смотрели формулу?

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение
Цитата dmitrijaltman8, 08.08.2021 в 14:54, в сообщении № 19 ( писал(а)):
просто ссылалась на ячейку N3

в моём файле в столбце О смотрели формулу?
Автор - Pelena
Дата добавления - 08.08.2021 в 15:10
Светлый Дата: Воскресенье, 08.08.2021, 15:17 | Сообщение № 20
Группа: Друзья
Ранг: Старожил
Сообщений: 1867
Репутация: 536 ±
Замечаний: 0% ±

Excel 2013, 2016
Цитата dmitrijaltman8, 08.08.2021 в 14:54, в сообщении № 19 ( писал(а)):
Чтобы эта формула просто ссылалась на ячейку N3, а не вбивать туда текст каждый раз.
Не проверяя на логику:
=ЕСЛИ(ЕОШИБКА(ПОИСК($N$3;$D4&$E4))=ИСТИНА;"";ЕСЛИ(ЕОШИБКА($A4&$B4&$C4&ПСТР($D4;ПОИСК($N$3&"(";$D4&$E4;1)+3;ПОИСК(")";ПРАВСИМВ($D4&$E4;ДЛСТР($D4&$E4)-НАЙТИ($N$3;$D4&$E4)-1);1)-1));"";$A4&$B4&$C4&ПСТР($D4&$E4;ПОИСК($N$3&"(";$D4&$E4;1)+7;ПОИСК(")";ПРАВСИМВ($D4&$E4;ДЛСТР($D4&$E4)-НАЙТИ($N$3;$D4&$E4)-1);1)-4)))

Программировать проще, чем писать стихи.
 
Ответить
Сообщение
Цитата dmitrijaltman8, 08.08.2021 в 14:54, в сообщении № 19 ( писал(а)):
Чтобы эта формула просто ссылалась на ячейку N3, а не вбивать туда текст каждый раз.
Не проверяя на логику:
=ЕСЛИ(ЕОШИБКА(ПОИСК($N$3;$D4&$E4))=ИСТИНА;"";ЕСЛИ(ЕОШИБКА($A4&$B4&$C4&ПСТР($D4;ПОИСК($N$3&"(";$D4&$E4;1)+3;ПОИСК(")";ПРАВСИМВ($D4&$E4;ДЛСТР($D4&$E4)-НАЙТИ($N$3;$D4&$E4)-1);1)-1));"";$A4&$B4&$C4&ПСТР($D4&$E4;ПОИСК($N$3&"(";$D4&$E4;1)+7;ПОИСК(")";ПРАВСИМВ($D4&$E4;ДЛСТР($D4&$E4)-НАЙТИ($N$3;$D4&$E4)-1);1)-4)))
Автор - Светлый
Дата добавления - 08.08.2021 в 15:17
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Как сделать формулу без промежуточных столбцов. (Формулы/Formulas)
  • Страница 1 из 2
  • 1
  • 2
  • »
Поиск:

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