Условное форматирование

Автор лена89, 14 августа 2021, 16:12

0 Пользователи и 1 гость просматривают эту тему.

лена89

Добрый день!

Очень интересная задачка, вот понимаю, что где-то есть функция, но видимо это чистая математика, а я от неё далека.
Смотрите, в столбце М  имеется ряд чисел, отрицательных и положительных, которые являются результатом вычислений и связаны с другими столбцами. Я  могу их отфильтровать по возрастанию. Мне надо ВСЕГДА видеть  два числа , которые ближе всего к нулю. Я их выделила цветом. В условном форматировании есть такая возможность , надо выбрать условие МЕЖДУ  и цветом выделится в колонке два этих числа. Но вот загвоздка, - между какими числами? Это может быть и -3 и 2, это может быть и -1 и 5. Данные предыдущих столбцов экспериментально меняются. И меняются постоянно и вычисления. Я взяла для примера  фрагмент массива. Он гораздо больше. Там порядка 200 строк в массиве. И мне надо бегать вручную по этим строкам и искать два числа, которые ближе всего к нулю. Если бы я знала границы этих чисел, я бы применила выделение через условное форматирование. Но границы всегда разные.
Сломала голову как это сделать. Это возможно или нет? Заранее благодарю.
И ещё, возможно это важно. В колонке есть ошибочные строки. При нулевом эксперименте, в результате деления на ноль, происходит ошибка ( это нормально) . Но от этого избавляться нельзя. То есть, как есть. Только с этим можно работать и ничего исправлять нельзя.

eeigor

#1
Цитата: лена89 от 14 августа 2021, 16:12Это возможно или нет?
Max(Data<0):
   $M5=AGGREGATE(4;6;IF($M$5:$M$33<0;$M$5:$M$33;""))
Min(Data>0):
   $M5=AGGREGATE(5;6;IF($M$5:$M$33>0;$M$5:$M$33;""))

См. файл

UPD
Можно удалить второе FALSE-условие IF (зачёркнуто красным):
   $M5=AGGREGATE(4;6;IF($M$5:$M$33<0;$M$5:$M$33;""))
   $M5=AGGREGATE(5;6;IF($M$5:$M$33>0;$M$5:$M$33;""))
Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community

лена89

#2
Цитата: eeigor от 14 августа 2021, 17:00
Цитата: лена89 от 14 августа 2021, 16:12Это возможно или нет?
Max(Data<0):
 $M5=AGGREGATE(4;6;IF($M$5:$M$33<0;$M$5:$M$33;""))
Min(Data>0):
 $M5=AGGREGATE(5;6;IF($M$5:$M$33>0;$M$5:$M$33;""))
То есть, как я поняла, в условном форматировании, в ячейки МЕЖДУ надо ставить вот эти формулы? Но я не совсем понимаю, что означают в этом случае числа 4, 6, 5, 6 . Или Вы предлагаете , что числа будут высвечиваться вверху столбца в табличке? Но мне это неудобно. Надо, чтобы как-то числа загорались цветом в самом столбце. чтобы их сразу было видно. Я попробовала сейчас отфильтровать столбцы и значения в вашей таблице вверху сразу исчезли. Хотя, это было бы тоже неплохо (верху столбца) , хоть и неудобно. Но это, если больше никаких решений нет. Мне надо именно, чтобы как-то через условное форматирование, чтобы цвет работал. Может я туплю, и надо разобраться и поработать с вашими предложенными формулами.


eeigor

#3
Высвечиваются вверху (для наглядности) и вставлены в УФ. У вас рабочий пример. Попробуйте изменить данные в пределах представленного диапазона. Всё "загорается".  :)

Что касается фильтрации, то параметр 6 обрабатывает только ошибки.

Зачем фильтровать? В тексте выделено. Вверху выведено. Что ещё?

AGGREGATE(Function; Option; Array[; k])
 Function – obligatory argument. A function index or a reference to a cell with value from 1 to 19, in accordance with the following table.

Function index  Function applied
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN

6 PRODUCT
7 STDEV.S
8 STDEV.P
9 SUM
10 VAR.S
11 VAR.P
12 MEDIAN
13 MODE.SNGL
14 LARGE
15 SMALL
16 PERCENTILE.INC
17 QUARTILE.INC
18 PERCENTILE.EXC
19 QUARTILE.EXC

Option – obligatory argument. An option index or reference to a cell with value from 0 to 7 determines what to ignore in the range for the function.

Option index  Option applied
0 Ignore only nested SUBTOTAL and AGGREGATE functions
1 Ignore only hidden rows, nested SUBTOTAL and AGGREGATE functions
2 Ignore only errors, nested SUBTOTAL and AGGREGATE functions
3 Ignore hidden rows, errors, nested SUBTOTAL and AGGREGATE functions
4 Ignore nothing
5 Ignore only hidden rows
6 Ignore only errors
7 Ignore only hidden rows and errors
Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community

mikekaganski

Цитата: лена89 от 14 августа 2021, 16:12Мне надо ВСЕГДА видеть  два числа , которые ближе всего к нулю. Я их выделила цветом.

В примере выделены не два самых близких к нулю числа. -1,75 дальше от нуля, чем 0,576923076923077. Или имеется ввиду, что Вы хотите видеть одно положительное ближайшее к нулю, и одно отрицательное? И второй вопрос: а если в M ноль - это ближайшее к нулю? если да, то оно ближайшее неотрицательное? или неположительное?
С уважением,
Михаил Каганский

лена89

Цитата: eeigor от 14 августа 2021, 17:19Зачем фильтровать? В тексте выделено. Вверху выведено. Что ещё?

Это рабочая таблица, фильтровать приходится каждый столбец постоянно. Но числа столбца М , близкие к нулю , надо видеть оперативно. Чтобы они всегда горели. Мне, в принципе, нужна формула, а как в самом условном форматировании это все сделать - я разобралась бы. Мне просто хочется разобраться, - эти числа 4, 6, и так далее - это статические числа, или они меняются в зависимости от массива?

eeigor

@mikekaganski, в предложенном решении ноль будет отформатирован красным цветом, поскольку это условие идёт первым. Эти детали не учитываются.
Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community

eeigor

#7
@лена89, у вас всё работает. Числа - параметры.

Не понял: или вы хотите, чтобы после фильтрации "эти самые" записи никогда не скрывались?

UPD
Как такое возможно: никогда не скрывать записи, которые не выбраны фильтром? Если только ввести эти два значения в диапазон условий расширенного фильтра, а фильтровать вашими дополнительными условиями, начиная с 3-й строки диапазона условий. Другого решения не вижу.
Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community

лена89

Цитата: mikekaganski от 14 августа 2021, 17:33
Цитата: лена89 от 14 августа 2021, 16:12Мне надо ВСЕГДА видеть  два числа , которые ближе всего к нулю. Я их выделила цветом.

В примере выделены не два самых близких к нулю числа. -1,75 дальше от нуля, чем 0,576923076923077. Или имеется ввиду, что Вы хотите видеть одно положительное ближайшее к нулю, и одно отрицательное? И второй вопрос: а если в M ноль - это ближайшее к нулю? если да, то оно ближайшее неотрицательное? или неположительное?

Да, мне нужно видеть одно положительное и одно отрицательное число , ближайшие к нулю. Да, Вы правы. бывают моменты , когда в вычислениях получается 0 . Вот 0 то я в условном форматировании легко высвечиваю)))) Но мне надо , чтобы ( если нет 0) высвечивался интервал , близкий к 0. То есть - одно положительное, одно отрицательное. И ещё важный момент, который я упустила. бывает, что в столбце М ( в результате успешных экспериментов) только положительные числа. И в случае провалов ( только отрицательные) . Но такое бывает крайне редко и здесь условное форматирование ни к чему. Берется верхнее положительное ( если одни положительные) и нижнее отрицательное, если одни отрицательные. Это легко сделать, и там есть цветовой маркер , когда только отрицательные или только положительные и сразу смотришь туда, куда надо. НО, Это бывает крайне редко. в основном идет разброс вариантов и надо , ОЧЕНЬ, чтобы видеть ближайшие к нулю.

лена89

Цитата: eeigor от 14 августа 2021, 17:38
@лена89, у вас всё работает. Числа - параметры.

Не понял: или вы хотите, чтобы после фильтрации "эти самые" записи никогда не скрывались?

Ну, я просто первым делом начала фильтровать, записи скрылись и я решила, что если это таблица вверху столбца - окончательный вариант, то как-же так? Но сейчас буду разбираться. Потом , позже, отпишусь.

mikekaganski

Цитата: eeigor от 14 августа 2021, 17:00UPD
Можно удалить второе FALSE-условие IF (зачёркнуто красным)

Не уверен. Потому что IF возвратит FALSE (0) в этом случае, и оно станет максимальным для отрицательных, либо минимальным для положительных.

=$M6=AGGREGATE(4;6;$M$6:$M$33/($M$6:$M$33<0))
С уважением,
Михаил Каганский

mikekaganski

А что это за прикол такой: сначала отмасштабировать лист до 31%, а потом увеличивать шрифт и размер ячеек, чтобы это скомпенсировать?
С уважением,
Михаил Каганский

eeigor

#12
Цитата: mikekaganski от 14 августа 2021, 17:54А что это за прикол такой: сначала отмасштабировать лист до 31%
В моём примере я это поправил. Сталкивался и раньше. Откуда ноги растут, неясно.

Цитата: eeigor от 14 августа 2021, 17:38Как такое возможно: никогда не скрывать записи, которые не выбраны фильтром? Если только ввести эти два значения в диапазон условий расширенного фильтра, а фильтровать вашими дополнительными условиями, начиная с 3-й строки диапазона условий. Другого решения не вижу.
Да, это возможно. См. пример и скриншот.
Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community

лена89

Цитата: mikekaganski от 14 августа 2021, 17:54
А что это за прикол такой: сначала отмасштабировать лист до 31%, а потом увеличивать шрифт и размер ячеек, чтобы это скомпенсировать?

Ну, понимаете, я работаю (надеюсь, временно) с большим массивом таблиц, и мне надоедает постоянно переходить из одного конца в другой. Я делаю уменьшение масштаба, чтобы видеть таблицу целиком. И увеличиваю шрифт))) Я думаю, есть вариант умнее и комфортнее, но вот я же профан во всем этом. Поэтому я сюда к вам и хожу за помощью)))) Но, благодаря всем вам - я теперь  у нас самая умная в либреофисе.

kompilainenn

Цитата: лена89 от 14 августа 2021, 18:01Я думаю, есть вариант умнее и комфортнее, но вот я же профан во всем этом
Используйте сочетания клавиш Ctrl+Стрелки курсора для быстрого перехода по массивам данных
Поддержать разработчиков LibreOffice можно тут, а наш форум вот тут