Как сделать формулу без промежуточных столбцов.
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 То есть извлекается прилегающая к ключевому слову область, заключенная скобками и все что внутри этих скобок. Но эта формула не очень хорошая, иногда текст извлекается неправильно. (например обрезается посередине и т.д.) Потом в синем столбце другая формула - спрессовывает полученный результат. Как сделать то же самое, без промежуточных столбцов, просто указав в формуле целевую ячейку с ключевым словом и диапазон где ведется поиск ?
Здравствуйте. Помогите решить непростой вопрос. Работает формула массива:Код
=ЕСЛИ(ЕОШИБКА(ПОИСК("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
Ответить
Сообщение Здравствуйте. Помогите решить непростой вопрос. Работает формула массива:Код
=ЕСЛИ(ЕОШИБКА(ПОИСК("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
Группа: Админы
Ранг: Местный житель
Сообщений: 19174
Репутация:
4413
±
Замечаний:
±
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)));"")
Здравствуйте.Код
=ЕСЛИОШИБКА(ИНДЕКС(ЕСЛИОШИБКА($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
"Черт возьми, Холмс! Но как??!!" Ю-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
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4106
Репутация:
768
±
Замечаний:
0% ±
Excel 2013/2016
Лена, у тебя подглядел немного, сам чёто не до пер с утра что можно объединить источникиКод
=IFERROR(INDEX($A$4:$A$73&$B$4:$B$73&$C$4:$C$73&IFERROR(TRIM(LEFT(SUBSTITUTE(MID($D$4:$D$73&$E$4:$E$73;FIND($X$3&"(";$D$4:$D$73&$E$4:$E$73)+LEN($X$3);999);"),";REPT(" ";999);1);999))&"),";"");SMALL(IF(IFERROR(TRIM(LEFT(SUBSTITUTE(MID($D$4:$D$73&$E$4:$E$73;FIND($X$3&"(";$D$4:$D$73&$E$4:$E$73)+LEN($X$3);999);"),";REPT(" ";999);1);999))&"),";"")<>"";ROW($D$4:$D$73)-3);ROWS($Y$4:Y4)));"")
Лена, у тебя подглядел немного, сам чёто не до пер с утра что можно объединить источникиКод
=IFERROR(INDEX($A$4:$A$73&$B$4:$B$73&$C$4:$C$73&IFERROR(TRIM(LEFT(SUBSTITUTE(MID($D$4:$D$73&$E$4:$E$73;FIND($X$3&"(";$D$4:$D$73&$E$4:$E$73)+LEN($X$3);999);"),";REPT(" ";999);1);999))&"),";"");SMALL(IF(IFERROR(TRIM(LEFT(SUBSTITUTE(MID($D$4:$D$73&$E$4:$E$73;FIND($X$3&"(";$D$4:$D$73&$E$4:$E$73)+LEN($X$3);999);"),";REPT(" ";999);1);999))&"),";"")<>"";ROW($D$4:$D$73)-3);ROWS($Y$4:Y4)));"")
bmv98rus
Замечательный Временно просто медведь , процентов на 20 .
Ответить
Сообщение Лена, у тебя подглядел немного, сам чёто не до пер с утра что можно объединить источникиКод
=IFERROR(INDEX($A$4:$A$73&$B$4:$B$73&$C$4:$C$73&IFERROR(TRIM(LEFT(SUBSTITUTE(MID($D$4:$D$73&$E$4:$E$73;FIND($X$3&"(";$D$4:$D$73&$E$4:$E$73)+LEN($X$3);999);"),";REPT(" ";999);1);999))&"),";"");SMALL(IF(IFERROR(TRIM(LEFT(SUBSTITUTE(MID($D$4:$D$73&$E$4:$E$73;FIND($X$3&"(";$D$4:$D$73&$E$4:$E$73)+LEN($X$3);999);"),";REPT(" ";999);1);999))&"),";"")<>"";ROW($D$4:$D$73)-3);ROWS($Y$4:Y4)));"")
Автор - bmv98rus Дата добавления - 08.08.2021 в 08:50
прохожий2019
Дата: Воскресенье, 08.08.2021, 09:55 |
Сообщение № 4
Группа: Проверенные
Ранг: Старожил
Сообщений: 1242
Репутация:
317
±
Замечаний:
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);))
всегда хотелось обе функции объединить...Код
=ФИЛЬТР(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
Сообщение отредактировал прохожий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
Ответить
Сообщение прохожий2019, фот эта последняя формула - почему-то не работает. Выдает ошибку #ИМЯ Автор - dmitrijaltman8 Дата добавления - 08.08.2021 в 12:01
прохожий2019
Дата: Воскресенье, 08.08.2021, 12:13 |
Сообщение № 6
Группа: Проверенные
Ранг: Старожил
Сообщений: 1242
Репутация:
317
±
Замечаний:
0% ±
365 Beta Channel
Потому что ФИЛЬТР() появилась в июле прошлого года в 365 офисе
Потому что ФИЛЬТР() появилась в июле прошлого года в 365 офисепрохожий2019
Ответить
Сообщение Потому что ФИЛЬТР() появилась в июле прошлого года в 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
Ответить
Сообщение Pelena , bmv98rus , обе формулы - почему-то находят не все результаты. Моя формула нашла 308 результатов. А эти формулы - находят всего 196 результатов.Автор - dmitrijaltman8 Дата добавления - 08.08.2021 в 12:27
bmv98rus
Дата: Воскресенье, 08.08.2021, 12:43 |
Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4106
Репутация:
768
±
Замечаний:
0% ±
Excel 2013/2016
А эти формулы - находят всего 196 результатов.
что уже странно ибо более 69 найти не должно :-). Диапазоны изменили ? Как формула массива ввели?
А эти формулы - находят всего 196 результатов.
что уже странно ибо более 69 найти не должно :-). Диапазоны изменили ? Как формула массива ввели?bmv98rus
Замечательный Временно просто медведь , процентов на 20 .
Ответить
Сообщение А эти формулы - находят всего 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
Ответить
Сообщение bmv98rus, да, конечно. Диапазон протянул вниз на 9000 строк. Используется формула массива. Но все равно не находит 308 результатов - только 196 Автор - dmitrijaltman8 Дата добавления - 08.08.2021 в 13:08
Светлый
Дата: Воскресенье, 08.08.2021, 13:16 |
Сообщение № 10
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация:
510
±
Замечаний:
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));"")
На примере находит больше значений, чем предыдущие. *Подправил формулу под большую длину строк **Но все равно не находит 308 результатов - только 196
В данных я увидел искомые образцы, не привязанные к шаблону. Т.е. после образца идёт не "(", а ",),". Наши формулы не берут их во внимание. В одной строке может быть несколько включений образца. Моя формула находит все.
У меня такая массивная формула получилась:Код
=ЕСЛИОШИБКА(ИНДЕКС(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));"")
На примере находит больше значений, чем предыдущие. *Подправил формулу под большую длину строк **Но все равно не находит 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));"")
На примере находит больше значений, чем предыдущие. *Подправил формулу под большую длину строк **Но все равно не находит 308 результатов - только 196
В данных я увидел искомые образцы, не привязанные к шаблону. Т.е. после образца идёт не "(", а ",),". Наши формулы не берут их во внимание. В одной строке может быть несколько включений образца. Моя формула находит все. Автор - Светлый Дата добавления - 08.08.2021 в 13:16
bmv98rus
Дата: Воскресенье, 08.08.2021, 13:29 |
Сообщение № 11
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4106
Репутация:
768
±
Замечаний:
0% ±
Excel 2013/2016
На примере находит больше значений, чем предыдущие.
хм, даже больше чем в исходных данных?
На примере находит больше значений, чем предыдущие.
хм, даже больше чем в исходных данных?bmv98rus
Замечательный Временно просто медведь , процентов на 20 .
Ответить
Сообщение На примере находит больше значений, чем предыдущие.
хм, даже больше чем в исходных данных?Автор - bmv98rus Дата добавления - 08.08.2021 в 13:29
Pelena
Дата: Воскресенье, 08.08.2021, 13:30 |
Сообщение № 12
Группа: Админы
Ранг: Местный житель
Сообщений: 19174
Репутация:
4413
±
Замечаний:
±
Excel 365 & Mac Excel
Диапазон протянул вниз на 9000 строк
в формуле исходные данные подтягиваются из 2000 строк. Замените везде в формуле 2000 на нужное число
Диапазон протянул вниз на 9000 строк
в формуле исходные данные подтягиваются из 2000 строк. Замените везде в формуле 2000 на нужное числоPelena
"Черт возьми, Холмс! Но как??!!" Ю-money 41001765434816
Ответить
Сообщение Диапазон протянул вниз на 9000 строк
в формуле исходные данные подтягиваются из 2000 строк. Замените везде в формуле 2000 на нужное числоАвтор - Pelena Дата добавления - 08.08.2021 в 13:30
Светлый
Дата: Воскресенье, 08.08.2021, 13:48 |
Сообщение № 13
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация:
510
±
Замечаний:
0% ±
Excel 2013, 2016
даже больше чем в исходных данных?
Конечно же нет. В первоначальном файле Ваши формулы нашли 8 значений, моя 9. Ещё 111(343097845 34dfg fd7,).
даже больше чем в исходных данных?
Конечно же нет. В первоначальном файле Ваши формулы нашли 8 значений, моя 9. Ещё 111(343097845 34dfg fd7,).Светлый
Программировать проще, чем писать стихи.
Ответить
Сообщение даже больше чем в исходных данных?
Конечно же нет. В первоначальном файле Ваши формулы нашли 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(" - со скобкой)
Светлый, спасибо. Пока не могу сказать работает или нет. Поставил считать 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
Сообщение отредактировал 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
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4106
Репутация:
768
±
Замечаний:
0% ±
Excel 2013/2016
Ваши формулы нашли 8 значений, моя 9
ну как бы это вопрос к ТС, ибо в #1 заложено нахождение только одного значения, для одной строки.
Ваши формулы нашли 8 значений, моя 9
ну как бы это вопрос к ТС, ибо в #1 заложено нахождение только одного значения, для одной строки.bmv98rus
Замечательный Временно просто медведь , процентов на 20 .
Ответить
Сообщение Ваши формулы нашли 8 значений, моя 9
ну как бы это вопрос к ТС, ибо в #1 заложено нахождение только одного значения, для одной строки.Автор - bmv98rus Дата добавления - 08.08.2021 в 13:55
Светлый
Дата: Воскресенье, 08.08.2021, 14:03 |
Сообщение № 16
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация:
510
±
Замечаний:
0% ±
Excel 2013, 2016
Пока за 5 минут - сделано 10% - это очень долго
Можно уменьшить диапазон СТОЛБЕЦ(A:ALK) до СТОЛБЕЦ(A:GR), будет считать быстрее, но сумма длин ячеек в строке не должна превышать 200. *только одного значения, для одной строки
Я взял на себя инициативу искать все включения в каждой строке, т.к. в исходных данных увидел, что встречается по два раза.
Пока за 5 минут - сделано 10% - это очень долго
Можно уменьшить диапазон СТОЛБЕЦ(A:ALK) до СТОЛБЕЦ(A:GR), будет считать быстрее, но сумма длин ячеек в строке не должна превышать 200. *только одного значения, для одной строки
Я взял на себя инициативу искать все включения в каждой строке, т.к. в исходных данных увидел, что встречается по два раза.Светлый
Программировать проще, чем писать стихи.
Сообщение отредактировал Светлый - Воскресенье, 08.08.2021, 14:07
Ответить
Сообщение Пока за 5 минут - сделано 10% - это очень долго
Можно уменьшить диапазон СТОЛБЕЦ(A:ALK) до СТОЛБЕЦ(A:GR), будет считать быстрее, но сумма длин ячеек в строке не должна превышать 200. *только одного значения, для одной строки
Я взял на себя инициативу искать все включения в каждой строке, т.к. в исходных данных увидел, что встречается по два раза.Автор - Светлый Дата добавления - 08.08.2021 в 14:03
dmitrijaltman8
Дата: Воскресенье, 08.08.2021, 14:13 |
Сообщение № 17
Группа: Пользователи
Ранг: Участник
Сообщений: 61
Репутация:
0
±
Замечаний:
20% ±
2016
в формуле исходные данные подтягиваются из 2000 строк. Замените везде в формуле 2000 на нужное число
Это я уже менял.
в формуле исходные данные подтягиваются из 2000 строк. Замените везде в формуле 2000 на нужное число
Это я уже менял.dmitrijaltman8
Ответить
Сообщение в формуле исходные данные подтягиваются из 2000 строк. Замените везде в формуле 2000 на нужное число
Это я уже менял.Автор - dmitrijaltman8 Дата добавления - 08.08.2021 в 14:13
dmitrijaltman8
Дата: Воскресенье, 08.08.2021, 14:54 |
Сообщение № 18
Группа: Пользователи
Ранг: Участник
Сообщений: 61
Репутация:
0
±
Замечаний:
20% ±
2016
Можно уменьшить диапазон СТОЛБЕЦ(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 - без изменений. Буду использовать его)
Можно уменьшить диапазон СТОЛБЕЦ(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
Ответить
Сообщение Можно уменьшить диапазон СТОЛБЕЦ(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
Группа: Админы
Ранг: Местный житель
Сообщений: 19174
Репутация:
4413
±
Замечаний:
±
Excel 365 & Mac Excel
просто ссылалась на ячейку N3
в моём файле в столбце О смотрели формулу?
просто ссылалась на ячейку N3
в моём файле в столбце О смотрели формулу?Pelena
"Черт возьми, Холмс! Но как??!!" Ю-money 41001765434816
Ответить
Сообщение просто ссылалась на ячейку N3
в моём файле в столбце О смотрели формулу?Автор - Pelena Дата добавления - 08.08.2021 в 15:10
Светлый
Дата: Воскресенье, 08.08.2021, 15:17 |
Сообщение № 20
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация:
510
±
Замечаний:
0% ±
Excel 2013, 2016
Чтобы эта формула просто ссылалась на ячейку 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)))
Чтобы эта формула просто ссылалась на ячейку 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)))
Светлый
Программировать проще, чем писать стихи.
Ответить
Сообщение Чтобы эта формула просто ссылалась на ячейку 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