Форум поддержки пользователей. LibreOffice, Apache OpenOffice, OpenOffice.org

Форум поддержки пользователей. LibreOffice, Apache OpenOffice, OpenOffice.org

2 Декабрь 2021, 22:25 *
Добро пожаловать, Гость. Пожалуйста, войдите или зарегистрируйтесь.
Вам не пришло письмо с кодом активации?

Войти
Новости: Часто задаваемые вопросы по LibreOffice и Apache OpenOffice.org
 
   Начало   Помощь Поиск Войти Регистрация    задать вопрос  
Страниц: 1 2 3 4 »   Вниз
  Печать  
Автор Тема: Условное форматирование или фильтрация строки с несколькими условиями сразу?  (Прочитано 4426 раз)
0 Пользователей и 1 Гость смотрят эту тему.
Mric
Участник
**
Offline Offline

Сообщений: 19


« Стартовое сообщение: 11 Январь 2021, 21:00 »

Такой вопрос, есть таблица с данными и столбец с урлами. И мне нужно чтобы выдавало по фильтру или подсвечивало строки с двумя условиями (чтобы они одновременно учитывались) для столбца с урлами. Условно, чтобы урл содержал кусок из символов abc и кусок из символов nmc, если оба условия есть, то он показывает через фильтр все строки или подсвечивает такие строки (с такими урлами). В том же условном форматировании я так и не нашел как это сделать, там только одно условие работает.
Записан
eeigor
Форумчанин
***
Offline Offline

Пол: Мужской
Сообщений: 752



« Ответ #1: 11 Январь 2021, 21:29 »

Если честно, то не понял, что вам нужно. Но материал ниже - в тему... и для самостоятельной работы.

там только одно условие работает
Почему? Условий может быть много. На скриншоте два условия, кнопка добавить внизу.
В любом случае нужен осмысленный пример.

В прилагаемом файле показано, как форматировать при помощи УФ (лист 1) и то же - формулой STYLE (лист 2).
Надо только добавить формулы поиска тех подстрок, что вам нужны. И желательно при этом использовать регулярные выражения.
Для подсчета подстрок попробуйте формулу COUNTIF(диапазон;условие). В условии используйте регулярные выражения*, для чего включите соответствующую опцию в параметрах:
Параметры – LibreOffice Calc – Вычисления
 Подстановочные знаки в формулах
  Разрешить регулярные выражения в формулах

Стоит обратить внимание и на эту опцию:
Параметры – LibreOffice Calc – Вычисления
 Общие вычисления
  Условия поиска = и <> должны распространяться на всю ячейку


*Регулярные выражения на русском языке содержат много ошибок в примерах.

UPD:
Соединять всю эту "дребедень" можно по схеме: =IF(OR(AND(COUNTIF(...
OR – для условий ИЛИ, AND – для условий И. А если надо вернуть простой логический результат (1 или 0), то добавьте IF(...;1;0).


* Снимок экрана от 2021-01-11 21-27-23.png (56.91 Кб, 929x762 - просмотрено 20 раз.)
* MultiCF.ods (26.06 Кб - загружено 10 раз.)
« Последнее редактирование: 11 Январь 2021, 22:05 от eeigor » Записан

Ubuntu 18.04 LTS • LO 7.2.2.2 Community
eeigor
Форумчанин
***
Offline Offline

Пол: Мужской
Сообщений: 752



« Ответ #2: 12 Январь 2021, 00:31 »

Пример согласно сказанному выше.

* Пример.ods (14.54 Кб - загружено 6 раз.)
« Последнее редактирование: 12 Январь 2021, 00:51 от eeigor » Записан

Ubuntu 18.04 LTS • LO 7.2.2.2 Community
Mric
Участник
**
Offline Offline

Сообщений: 19


« Ответ #3: 12 Январь 2021, 11:56 »

Если честно, то не понял, что вам нужно. Но материал ниже - в тему... и для самостоятельной работы.

там только одно условие работает
Почему? Условий может быть много. На скриншоте два условия, кнопка добавить внизу.
В любом случае нужен осмысленный пример.

В прилагаемом файле показано, как форматировать при помощи УФ (лист 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
Форумчанин
***
Offline Offline

Пол: Мужской
Сообщений: 752



« Ответ #4: 12 Январь 2021, 12:23 »

=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".

* Пример.ods (153.22 Кб - загружено 7 раз.)
« Последнее редактирование: 12 Январь 2021, 12:51 от eeigor » Записан

Ubuntu 18.04 LTS • LO 7.2.2.2 Community
Mric
Участник
**
Offline Offline

Сообщений: 19


« Ответ #5: 12 Январь 2021, 12:35 »

=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
Форумчанин
***
Offline Offline

Пол: Мужской
Сообщений: 752



« Ответ #6: 12 Январь 2021, 12:43 »

Вот что означает относительная адресация в стиле R1C1:
IF(AND(COUNTIF(RC1;".*abc.*");COUNTIF(RC1;".*123.*"));1;0)

А вот то же самое в стиле A1 (на скриншоте выбран этот стиль):
IF(AND(COUNTIF($A4;".*abc.*");COUNTIF($A4;".*123.*"));1;0)

В окне УФ надо использовать именно относительную адресацию, однако в стиле именования ячеек "A1" это выглядит оч-чень ненаглядно и непонятно и традиционно вызывает сложность у новичков. Разберитесь с тем, что я написал выше: оба стиля именования диапазонов должны сначала стать понятными. Тогда можно задавать вопросы.

И эту формулу надо ввести или на листе в поле автофильтра и фильтровать по "единичкам" (см. пример), или в окне УФ (выделяет цветом).
Оба способа представлены в моём примере. Если сложно, используйте автофильтр прямо на листе.

Вы неправильно задаёте диапазон.

UPD:
И ваши ошибки я выделил красным цветом. И разделитель параметров - точка с запятой, а не запятая. И в вашей формуле, к тому же, четыре открывающих скобки и три закрывающих. Так вы далеко не уедете...


* Снимок экрана от 2021-01-12 12-56-04.png (127.14 Кб, 1144x664 - просмотрено 11 раз.)
« Последнее редактирование: 12 Январь 2021, 13:33 от eeigor » Записан

Ubuntu 18.04 LTS • LO 7.2.2.2 Community
eeigor
Форумчанин
***
Offline Offline

Пол: Мужской
Сообщений: 752



« Ответ #7: 12 Январь 2021, 13:03 »

Но только куда эту формулу вставлять?
Вот сюда
IF(AND(COUNTIF($A4;".*abc.*");COUNTIF($A4;".*123.*"));1;0)
А символ, что выделен красным, очень важен. И его не должно быть перед четвёркой. Вот так неправильно: $A$4

У вас же есть работающий пример. Изучайте...


* Снимок экрана от 2021-01-12 13-02-35.png (56.66 Кб, 929x762 - просмотрено 8 раз.)
« Последнее редактирование: 12 Январь 2021, 13:07 от eeigor » Записан

Ubuntu 18.04 LTS • LO 7.2.2.2 Community
Mric
Участник
**
Offline Offline

Сообщений: 19


« Ответ #8: 12 Январь 2021, 13:08 »

Цитата:
$'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 в конце формулы?


* sample.png (49.09 Кб, 1833x810 - просмотрено 7 раз.)
Записан
rami
Гуру
*******
Offline Offline

Пол: Мужской
Сообщений: 3 117


iMac, LibreOffice и Apache OpenOffice


« Ответ #9: 12 Январь 2021, 13:16 »

Попробуйте такую формулу: REGEX(G1;".*abc.*123.*")=G1 (будет искать строки типа: "......abc......123...", зависит от регистра)
Записан

eeigor
Форумчанин
***
Offline Offline

Пол: Мужской
Сообщений: 752



« Ответ #10: 12 Январь 2021, 13:25 »

И не очень понял, зачем нужны значения 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 • LO 7.2.2.2 Community
Mric
Участник
**
Offline Offline

Сообщений: 19


« Ответ #11: 12 Январь 2021, 13:32 »

Вот что означает относительная адресация в стиле 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
Форумчанин
***
Offline Offline

Пол: Мужской
Сообщений: 752



« Ответ #12: 12 Январь 2021, 13:37 »

Можно как-то эти выделенные ячейки выводить все списком в отдельном документе или вкладке? Причем чтобы вся строка ячейки копировалась, а не только ячейка.
Воспользуйтесь автофильтром. Хотя по цвету бэкграунда тоже можно, но это куда сложнее. В моём примере автофильтр уже установлен. Раскройте автофильтр в поле "Selected" и сбросьте флажок на нолике. OK.
Данные будут отфильтрованы. Копируйте, что хотите, и переносите, куда хотите. Перенесены будут только видимые данные*.
И если работаете с автофильтром, и если правите формулу в строке, то не забывайте распространять эту формулу на остальные ячейки диапазона данных.

Оказалось просто "Формулы" воспринимались только на русском
А в моём сообщении #6 на скриншоте виден флажок, на каком языке использовать формулы.

*Не уверен, что в Excel так же. Там есть функция типа "выделить только видимые ячейки".
« Последнее редактирование: 12 Январь 2021, 13:50 от eeigor » Записан

Ubuntu 18.04 LTS • LO 7.2.2.2 Community
eeigor
Форумчанин
***
Offline Offline

Пол: Мужской
Сообщений: 752



« Ответ #13: 12 Январь 2021, 13:47 »

Кстати, в Excel нет формулы типа REGEX(), и нельзя использовать регулярные выражения в формулах напрямую, но можно реализовать эту возможность через внешнюю библиотеку макросами (источник). В данном случае задача решается в LO Calc гораздо эффективнее (для новичков).
Записан

Ubuntu 18.04 LTS • LO 7.2.2.2 Community
Mric
Участник
**
Offline Offline

Сообщений: 19


« Ответ #14: 12 Январь 2021, 13:58 »

Можно как-то эти выделенные ячейки выводить все списком в отдельном документе или вкладке? Причем чтобы вся строка ячейки копировалась, а не только ячейка.
Воспользуйтесь автофильтром. Хотя по цвету бэкграунда тоже можно, но это куда сложнее. В моём примере автофильтр уже установлен. Раскройте автофильтр в поле "Selected" и сбросьте флажок на нолике. OK.
Данные будут отфильтрованы. Копируйте, что хотите, и переносите, куда хотите. Перенесены будут только видимые данные.
И если работаете с автофильтром, и если правите формулу в строке, то не забывайте распространять эту формулу на остальные ячейки диапазона данных.

Оказалось просто "Формулы" воспринимались только на русском
А в моём сообщении #6 на скриншоте виден флажок, на каком языке использовать формулы.

Я не заметил ваш пример, потом открыл и увидел, что не там флажок у меня стоит, что все формулы не на английском воспринимаются.

Да, с Selected я понял, он фильтранул только с галочкой на 1, если убрать с 0. Но как мне этот селектед и автофильтр у себя сделать? У меня нет значений 0 и 1 в списке автофильтра. Показал на скриншоте. Хотя формула применена и ячейки выделены цветом.


* sample2.png (52.96 Кб, 1871x927 - просмотрено 5 раз.)
Записан
Страниц: 1 2 3 4 »   Вверх
  Печать  
 
Перейти в:  

Powered by MySQL Powered by PHP Powered by SMF 1.1.21 | SMF © 2006-2009, Simple Machines Valid XHTML 1.0! Valid CSS!