Mric
Участник

Offline
Сообщений: 38
|
Такой вопрос, есть таблица с данными и столбец с урлами. И мне нужно чтобы выдавало по фильтру или подсвечивало строки с двумя условиями (чтобы они одновременно учитывались) для столбца с урлами. Условно, чтобы урл содержал кусок из символов abc и кусок из символов nmc, если оба условия есть, то он показывает через фильтр все строки или подсвечивает такие строки (с такими урлами). В том же условном форматировании я так и не нашел как это сделать, там только одно условие работает.
|
|
|
Записан
|
|
|
|
eeigor
|
Если честно, то не понял, что вам нужно. Но материал ниже - в тему... и для самостоятельной работы. там только одно условие работает Почему? Условий может быть много. На скриншоте два условия, кнопка добавить внизу. В любом случае нужен осмысленный пример. В прилагаемом файле показано, как форматировать при помощи УФ (лист 1) и то же - формулой STYLE (лист 2). Надо только добавить формулы поиска тех подстрок, что вам нужны. И желательно при этом использовать регулярные выражения. Для подсчета подстрок попробуйте формулу COUNTIF(диапазон;условие). В условии используйте регулярные выражения*, для чего включите соответствующую опцию в параметрах: Параметры – LibreOffice Calc – Вычисления Подстановочные знаки в формулах Разрешить регулярные выражения в формулах Стоит обратить внимание и на эту опцию: Параметры – LibreOffice Calc – Вычисления Общие вычисления Условия поиска = и <> должны распространяться на всю ячейку *Регулярные выражения на русском языке содержат много ошибок в примерах. UPD: Соединять всю эту "дребедень" можно по схеме: =IF(OR(AND(COUNTIF(... OR – для условий ИЛИ, AND – для условий И. А если надо вернуть простой логический результат (1 или 0), то добавьте IF(...;1;0).
|
|
« Последнее редактирование: 11 Январь 2021, 22:05 от eeigor »
|
Записан
|
Ubuntu 18.04 LTS • LibreOffice 7.3.2.2 Community
|
|
|
eeigor
|
Пример согласно сказанному выше.
|
|
« Последнее редактирование: 12 Январь 2021, 00:51 от eeigor »
|
Записан
|
Ubuntu 18.04 LTS • LibreOffice 7.3.2.2 Community
|
|
|
Mric
Участник

Offline
Сообщений: 38
|
Если честно, то не понял, что вам нужно. Но материал ниже - в тему... и для самостоятельной работы. там только одно условие работает Почему? Условий может быть много. На скриншоте два условия, кнопка добавить внизу. В любом случае нужен осмысленный пример. В прилагаемом файле показано, как форматировать при помощи УФ (лист 1) и то же - формулой STYLE (лист 2). Надо только добавить формулы поиска тех подстрок, что вам нужны. И желательно при этом использовать регулярные выражения. Для подсчета подстрок попробуйте формулу COUNTIF(диапазон;условие). В условии используйте регулярные выражения*, для чего включите соответствующую опцию в параметрах: Параметры – LibreOffice Calc – Вычисления Подстановочные знаки в формулах Разрешить регулярные выражения в формулах Стоит обратить внимание и на эту опцию: Параметры – LibreOffice Calc – Вычисления Общие вычисления Условия поиска = и <> должны распространяться на всю ячейку *Регулярные выражения на русском языке содержат много ошибок в примерах. UPD: Соединять всю эту "дребедень" можно по схеме: =IF(OR(AND(COUNTIF(... OR – для условий ИЛИ, AND – для условий И. А если надо вернуть простой логический результат (1 или 0), то добавьте IF(...;1;0). Пример подробнее такой, есть три урла в столбце: website.com/abc-ert-23d/ website.com/abd-bnm23d-123/ website.com/abc-ert123/ Нужно выделить цветом строку куда входят набор символов abc и 123, т.е. будет выделена последняя строка. Как я понял в условиях нужно выбрать формулу в "Условном форматировании" и записать, что-то типа: =IF(AND(COUNTIF($'List1'.$G2:G4;"abc")COUNTIF($'List1'.$G2:G4;"123")) но такая формула не работает, где-то ошибка. Или я ее не могу применять в "Условном форматировании"?
|
|
|
Записан
|
|
|
|
eeigor
|
=IF(AND(COUNTIF($'List1'.$G2:G4;"abc")COUNTIF($'List1'.$G2:G4;"123")) Как же так? У формулы IF() есть обязательный аргумент, а аргументы формулы AND( ) надо разделять точкой с запятой, а саму формулу заключать в скобки... Плюс, не ясно почему, лучше работает с установленным флажком " Условия поиска = и <> должны распространяться на всю ячейку", но тогда надо дополнять подстроки символами ".*". Полагаю, вы знаете, что такое квантификаторы (?, *, +). Файл с примером (Лист2) =IF(AND(COUNTIF($A4;" .*abc .*") ;COUNTIF($A4;" .*123. *") ); 1;0) $'List1'.$G2:G4 Ещё один нюанс. В окне УФ в формуле я указываю первую ячейку (а не диапазон) способом относительной адресации ($A4 - для первой ячейки). * Поиск нулевого или большего числа символов перед "*". Например, при поиске "Аб*в" будут найдены "Ав", "Абв", "Аббв", "Абббв" и т. д. + Поиск одного или большего числа символов перед "+". Например, при поиске "AX.+4" будет найдено "AXx4", но не "AX4". Всегда будет найдена самая длинная возможная строка, соответствующая данному искомому элементу в абзаце. Если в абзаце содержится строка "AX 4 AX4", выделяется весь фрагмент. ? Поиск нулевого числа или одного символа перед "?". Например, при поиске термина "Тексты?" будут найдены "Текст" и "Тексты", а при поиске "x(ab|c)?y" будут найдены "xy", "xaby" или "xcy".
|
|
« Последнее редактирование: 12 Январь 2021, 12:51 от eeigor »
|
Записан
|
Ubuntu 18.04 LTS • LibreOffice 7.3.2.2 Community
|
|
|
Mric
Участник

Offline
Сообщений: 38
|
=IF(AND(COUNTIF($'List1'.$G2:G4;"abc")COUNTIF($'List1'.$G2:G4;"123")) Спасибо за помощь. Если честно, я еще больше запутался, в чем ошибка моей формулы или я ее не туда применяю, или там еще значение 0,1 должно быть в конце, как в вашем примере? Получается вот так: =IF(AND(COUNTIF($'List1'.$G2:G4,".*abc.*"),COUNTIF($'List1'.$G2:G4,".*123.*")) Но только куда эту формулу вставлять?
|
|
|
Записан
|
|
|
|
eeigor
|
Вот что означает относительная адресация в стиле R1C1: IF(AND(COUNTIF(RC1;".*abc.*");COUNTIF(RC1;".*123.*"));1;0)
А вот то же самое в стиле A1 (на скриншоте выбран этот стиль): IF(AND(COUNTIF($A4;".*abc.*");COUNTIF($A4;".*123.*"));1;0)
В окне УФ надо использовать именно относительную адресацию, однако в стиле именования ячеек "A1" это выглядит оч-чень ненаглядно и непонятно и традиционно вызывает сложность у новичков. Разберитесь с тем, что я написал выше: оба стиля именования диапазонов должны сначала стать понятными. Тогда можно задавать вопросы.
И эту формулу надо ввести или на листе в поле автофильтра и фильтровать по "единичкам" (см. пример), или в окне УФ (выделяет цветом). Оба способа представлены в моём примере. Если сложно, используйте автофильтр прямо на листе.
Вы неправильно задаёте диапазон.
UPD: И ваши ошибки я выделил красным цветом. И разделитель параметров - точка с запятой, а не запятая. И в вашей формуле, к тому же, четыре открывающих скобки и три закрывающих. Так вы далеко не уедете...
|
|
« Последнее редактирование: 12 Январь 2021, 13:33 от eeigor »
|
Записан
|
Ubuntu 18.04 LTS • LibreOffice 7.3.2.2 Community
|
|
|
eeigor
|
Но только куда эту формулу вставлять? Вот сюда IF(AND(COUNTIF( $A4;".*abc.*");COUNTIF( $A4;".*123.*"));1;0) А символ, что выделен красным, очень важен. И его не должно быть перед четвёркой. Вот так неправильно: $A$4 У вас же есть работающий пример. Изучайте...
|
|
« Последнее редактирование: 12 Январь 2021, 13:07 от eeigor »
|
Записан
|
Ubuntu 18.04 LTS • LibreOffice 7.3.2.2 Community
|
|
|
Mric
Участник

Offline
Сообщений: 38
|
$'List1'.$G2:G4 Ещё один нюанс. В окне УФ в формуле я указываю первую ячейку (а не диапазон) способом относительной адресации ($A4 - для первой ячейки). * Поиск нулевого или большего числа символов перед "*". Например, при поиске "Аб*в" будут найдены "Ав", "Абв", "Аббв", "Абббв" и т. д. + Поиск одного или большего числа символов перед "+". Например, при поиске "AX.+4" будет найдено "AXx4", но не "AX4". Всегда будет найдена самая длинная возможная строка, соответствующая данному искомому элементу в абзаце. Если в абзаце содержится строка "AX 4 AX4", выделяется весь фрагмент. ? Поиск нулевого числа или одного символа перед "?". Например, при поиске термина "Тексты?" будут найдены "Текст" и "Тексты", а при поиске "x(ab|c)?y" будут найдены "xy", "xaby" или "xcy". Получилась такая формула для моего документа, не для примера выше: =if(and(countif(G2:G10001,".*developers.*"),countif(G2:G10001,".*chicago.*")),1,0) Я ее применил на таблицу (скрин которой в приложении к этому ответу). Но все равно не работает, галочки все выставлены. И не очень понял, зачем нужны значения 1 и 0 в конце формулы?
|
|
|
|
rami
|
Попробуйте такую формулу: REGEX(G1;".*abc.*123.*")=G1 (будет искать строки типа: "......abc......123...", зависит от регистра)
|
|
|
Записан
|
|
|
|
eeigor
|
И не очень понял, зачем нужны значения 1 и 0 в конце формулы? Просто, чтобы свести результат к простому логическому значению. Это удобно при фильтрации автофильтром и вообще... удобно. А в примере от @rami фиксирован порядок подстрок. В моём пример не важно, что идёт впереди, а что сзади. Плюс формула REGEX появилась, если не ошибаюсь, с версии LO Calc 6.2. REGEX(G1;".*abc.*123.*")=G1 Сравнение результата формулы с ячейкой (=G1) делается с той же целью: получить логический результат (те же 1 и 0). countif(G2:G10001," Такая формула работает с массивом ячеек (вы указали диапазон), а это ещё куда более сложная тема. Заметьте, я везде ссылаюсь на одну ячейку.
|
|
« Последнее редактирование: 12 Январь 2021, 13:52 от eeigor »
|
Записан
|
Ubuntu 18.04 LTS • LibreOffice 7.3.2.2 Community
|
|
|
Mric
Участник

Offline
Сообщений: 38
|
Вот что означает относительная адресация в стиле R1C1: IF(AND(COUNTIF(RC1;".*abc.*");COUNTIF(RC1;".*123.*"));1;0)
А вот то же самое в стиле A1 (на скриншоте выбран этот стиль): IF(AND(COUNTIF($A4;".*abc.*");COUNTIF($A4;".*123.*"));1;0)
В окне УФ надо использовать именно относительную адресацию, однако в стиле именования ячеек "A1" это выглядит оч-чень ненаглядно и непонятно и традиционно вызывает сложность у новичков. Разберитесь с тем, что я написал выше: оба стиля именования диапазонов должны сначала стать понятными. Тогда можно задавать вопросы.
И эту формулу надо ввести или на листе в поле автофильтра и фильтровать по "единичкам" (см. пример), или в окне УФ (выделяет цветом). Оба способы представлены в моём примере. Если сложно, используйте автофильтр прямо на листе.
Вы неправильно задаёте диапазон.
UPD: И ваши ошибки я выделил красным цветом. И разделитель параметров - точка с запятой, а не запятая. И в вашей формуле, к тому же, четыре открывающих скобки и три закрывающих. Так вы далеко не уедете...
Спасибо большое, работает все! Оказалось просто "Формулы" воспринимались только на русском) И можно еще вопрос? Можно как-то эти выделенные ячейки выводить все списком в отдельном документе или вкладке? Причем чтобы вся строка ячейки копировалась, а не только ячейка. Типа может фильтровать по цвету бекграунда ячейки?
|
|
|
Записан
|
|
|
|
eeigor
|
Можно как-то эти выделенные ячейки выводить все списком в отдельном документе или вкладке? Причем чтобы вся строка ячейки копировалась, а не только ячейка. Воспользуйтесь автофильтром. Хотя по цвету бэкграунда тоже можно, но это куда сложнее. В моём примере автофильтр уже установлен. Раскройте автофильтр в поле "Selected" и сбросьте флажок на нолике. OK. Данные будут отфильтрованы. Копируйте, что хотите, и переносите, куда хотите. Перенесены будут только видимые данные*. И если работаете с автофильтром, и если правите формулу в строке, то не забывайте распространять эту формулу на остальные ячейки диапазона данных. Оказалось просто "Формулы" воспринимались только на русском А в моём сообщении #6 на скриншоте виден флажок, на каком языке использовать формулы. *Не уверен, что в Excel так же. Там есть функция типа "выделить только видимые ячейки".
|
|
« Последнее редактирование: 12 Январь 2021, 13:50 от eeigor »
|
Записан
|
Ubuntu 18.04 LTS • LibreOffice 7.3.2.2 Community
|
|
|
eeigor
|
Кстати, в Excel нет формулы типа REGEX(), и нельзя использовать регулярные выражения в формулах напрямую, но можно реализовать эту возможность через внешнюю библиотеку макросами ( источник). В данном случае задача решается в LO Calc гораздо эффективнее (для новичков).
|
|
|
Записан
|
Ubuntu 18.04 LTS • LibreOffice 7.3.2.2 Community
|
|
|
Mric
Участник

Offline
Сообщений: 38
|
Можно как-то эти выделенные ячейки выводить все списком в отдельном документе или вкладке? Причем чтобы вся строка ячейки копировалась, а не только ячейка. Воспользуйтесь автофильтром. Хотя по цвету бэкграунда тоже можно, но это куда сложнее. В моём примере автофильтр уже установлен. Раскройте автофильтр в поле "Selected" и сбросьте флажок на нолике. OK. Данные будут отфильтрованы. Копируйте, что хотите, и переносите, куда хотите. Перенесены будут только видимые данные. И если работаете с автофильтром, и если правите формулу в строке, то не забывайте распространять эту формулу на остальные ячейки диапазона данных. Оказалось просто "Формулы" воспринимались только на русском А в моём сообщении #6 на скриншоте виден флажок, на каком языке использовать формулы. Я не заметил ваш пример, потом открыл и увидел, что не там флажок у меня стоит, что все формулы не на английском воспринимаются. Да, с Selected я понял, он фильтранул только с галочкой на 1, если убрать с 0. Но как мне этот селектед и автофильтр у себя сделать? У меня нет значений 0 и 1 в списке автофильтра. Показал на скриншоте. Хотя формула применена и ячейки выделены цветом.
|
|
|
Записан
|
|
|
|
|