[РЕШЕНО] Условное форматирование по дате

Автор Alexcostariha, 16 февраля 2018, 09:04

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

Alexcostariha

Добрый день!
У меня есть таблица в которой расчитываются вознаграждения менеджерам по продажам. Условия нашей работы таковы, что клиент платит сначала предоплату, а потом, через какое-то время-месяц, два, три и т.д. - доплату. Соответственно менеджеры получают вознаграждение за свою работу в месяц предоплаты и оставшуюся часть -в месяц доплаты.
Таким образом в таблице имеются две колонки для оплаты - доплата и предоплата в рублях и колонка с датой доплаты.
Задача.
1) Нужно чтобы при заполнении ячейки с датой доплаты, скажем "G7"-дата "12.02.2018", соответствующая ей ячейка с суммой доплаты, допустим "J7", окрашивалась в определённый цвет-допустим оранжевый. В соседней с ней ячейке "K7"- нужно, чтобы появлялось название месяца доплаты - "февраль".
2) Бывает так, что клиент вносит 100% оплату. Тогда в колонке с суммой доплаты вычисляется запись "0 руб.". Нужно, чтобы в этом случае в ячейках касающихся доплаты (их три) появлялась запись "нет", а в соседней ячейке появлялся какой-нибудь значок - скажем треугольник.
Смотрите приложенный файл.


bigor

Одним форматированием, мне кажется, все ваши хотелки не сделаешь
вот что получилось:
ЦитироватьНужно чтобы при заполнении ячейки с датой доплаты, скажем "G7"-дата "12.02.2018", соответствующая ей ячейка с суммой доплаты, допустим "J7", окрашивалась в определённый цвет-допустим оранжевый. В соседней с ней ячейке "K7"- нужно, чтобы появлялось название месяца доплаты - "февраль".
месяц и треугольник появляются (месяца при условии что g? не содержит текст), ячейки закрашиваются, при условии, что содержат числа больше 0 :)
ЦитироватьТогда в колонке с суммой доплаты вычисляется запись "0 руб.". Нужно, чтобы в этом случае в ячейках касающихся доплаты (их три) появлялась запись "нет"
это можно сделать забив в них формулу, но  если будет доплата, то в эти ячейки будут вводить данные и формулы потрутся
Поддержать разработчиков LibreOffice можно можно тут, а наш форум вот тут

rami

Bigor, формула CHOOSE(...) не нужна, это проще сделать с помощью условного формата ячейки.
В формате ячеек назначьте код формата [>0]MMMM;[<0]"";"△" — дата превратится в полное название месяца, а ноль или пустая строка — в треугольник (только в LibreOffice, нужно включить настройку Обрабатывать пустую строку как ноль)

bigor

rami, красиво, но не работает, если в G? текст, например в G6, "нет", тогда в K6 тоже получаем "нет". А по условию в столбце G или текст или даты.

p.s. Я правда не нашел, где
Цитироватьнужно включить настройку Обрабатывать пустую строку как ноль)
Поддержать разработчиков LibreOffice можно можно тут, а наш форум вот тут

rami

Цитата: Bigor от 16 февраля 2018, 13:01красиво, но не работает, если в G? текст, например в G6, "нет", тогда в K6 тоже получаем "нет".
"нет" тоже можно сделать форматом DD.MM.YYYY;;нет, вместо того, что бы писать три буквы можно будет поставить в ячейке 0 или просто очистить её.

Настройка только в LibreOffice:

bigor

Цитировать"нет" тоже можно сделать форматом
теперь вообще красота :) Спасибо
Поддержать разработчиков LibreOffice можно можно тут, а наш форум вот тут

Alexcostariha

#6
Добрый день!
Спасибо за советы. Однако при занесении суммы в рублях в столбец "J"- в столбце "G"  появляется безумная дата, что мне не подходит.
Я поступил следующим образом: в двух столбцах "F" и "G" прописал формулу вида "=IF(J4>0;"да";"нет")".  
То есть если есть  доплата(а не вознаграждение) -"да", если не имеется - "нет"; смысл в том, чтобы поля были заполнены.
(Видимо надо было ссылаться на столбец  суммы доплаты "E"- но я уже сделал так, смысл не меняется)
Когда в столбце "G" ставится дата доплаты- формула стирается, но это не страшно. Зато по дате пишется название месяца в столбце "K".
Столбец "J" отформатировал через "Условное форматирование" по цвету, чтобы выделять сумму вознаграждения отличную от нуля.
Затем отформатировал столбец "K", чтобы выделять цветом месяц вознаграждения по доплате.
Здесь мне помогло Ваше форматирование - пишется только назавние месяца.
Получилось немного коряво- по идее там где в столбце "K", стоит "нет" надо бы какую нибудь пиктограмму и без слова "нет",
но у меня не получается.
Всё же за форматирование по названию месяца - спасибо.
Смотрите вложенный файл.

bigor

Про "Да" в столбце G в первоначальном условии ничего не было. Что бы в K, появился треугольник, а в G "Нет", условие должно быть "=IF(J4>0;"да";0)"
Поддержать разработчиков LibreOffice можно можно тут, а наш форум вот тут

Alexcostariha

#8
 1) Я проверил-работает! Супер-большое спасибо!
(Я подставлял ноль в формулу, но писал "0"- и не работало).
Что касается "да" или "нет"-так я в поиске решений :-).
Под условным форматированием в виде треугольника я имел в виду цветную пиктограмму в "Условном форматировании".
Всё-таки хочется красивостей. Однако и это решение тоже хорошее.

2) Ещё у меня не получилось обесцветить/изменить_цвет ячейки "K", когда в ней стоит "нет" или "△".
Можно ли тут что-то сделать?

bigor

#9
Для красивостей "△" можно раскрасить :) для этого в формате перед ним добавить [цвет], например [red]

у меня фон столбца "K" сбросился без проблем в вашем файле
Поддержать разработчиков LibreOffice можно можно тут, а наш форум вот тут

Alexcostariha

#10
Наверное я привередничаю-прошу извинить, если что.
Не знаю как у вас это получается - у меня и "да" и "треугольник" -всегда зелёные, только название месяца-неопределённого жёлтого.
Однако в вашем примере "да" даёт тоже обесцвечивание, а мне желательно, чтобы обесцвечивался только тругольник; а "да" оставалось зелёным.
См. фото.

bigor

добавьте еще одно условие в условном форматировании (см. Рисунок).
Только почему-то у меня условие наоборот работает :) выбираю не содержит "да" - выделяет ячейку которая содержит "да"
(Версия: 5.2.2.2
ID сборки: 1:5.2.2-0ubuntu2.1
Потоков ЦП: 4; Версия ОС: Linux 4.8; Отрисовка ИП: по умолчанию;
Локаль: ru-RU (ru_RU.UTF-8); Calc: group)
Поддержать разработчиков LibreOffice можно можно тут, а наш форум вот тут

Alexcostariha

#12
 Спасибо, всё получилось!
Только мне нужно было не окрасить"да"(оно и так зелёное), а  обесцветить ячейку с треугольником в графе K.
Она была превой в графе и видимо я сделал её зелёной по предыдущим ячейкам не разобравшись. Это и было камнем преткновения.
Я мучился пытаясь всё сделать через Условное форматирование. Потом догадался зайти в "Формат ячеек"(по правой кнопке мыши)
на ячейке с треугольником и в разделе ФОН поставить Без заливки. Дальше, всё как вы сказали, приступил к окрашиванию "да":
через "Условное форматирование"→Добавить условие→Значение→"Не содержит" - "да" → Создать стиль "ДаСтиль"(наследуем из предыдущего-зелёненького) →есть  зелёный фон и подправил Выравнивание по центру.
Ячейку с треугольником я потом окрасил  через ФОН в нежно голубой.
Дальше протягиваю эту ячейку вниз до конца таблицы.
Теперь: треугольник - красный на нежно голубом фоне, "да" - зелёный фон, название месяца - неопределённо жёлтый фон.


Слава богу теперь всё пряснилось.  :beer:

Итого имем. Три вида  Условного  форматирования. Одно - для ячеек "J" - выделять цветом числа больше нуля. Другое - двойное на три варианта: выделять цветом "треугольник" по умолчанию,  с форматированием - выделять цветом "да" и "название месяца". Ячейки графы "K"  имеют особый числовой формат:
[>0]MMMM;[<0]"";[RED]"△"
, чтобы появлялся треугольник на "ноль" и писалось название месяца. Ячейки "F"  имеют формулу "=IF(J5>0;"да";"нет")". Ячейки "G"  имеют формулу "=IF(J5>0;"да";0)", чтобы в ответ на "0"  в графе "K"  появлялся треугольник. Ячейки "F" и "G" имеют особое форматирование
[>0]DD.MM.YYYY;[<0]"";"нет"  
чтобы в ответ на ноль появлялось "нет", но формат даты сохранялся. Ячейки "G" ссылаются на "J", чтобы поймать "ноль". Ячейки "K"  ссылаются на "G". чтобы поймать ноль и выставить треугольник.
Ещё создано два Стиля пользователя, чтобы управляться со всеми этими форматами.
Особые форматы указанные в кодах, в Формате ячеек→Числа→Особая(категория) не видны и выглядят как пустые строки в самом низу столбца форматов.