Страницы: 1
RSS
Выделение ячеек цветом для контроля сроков предоставления данных
 

Коллеги, добрый день. Прошу помощи.

Необходимо подготовить модель для контроля предоставления информации центрами финансовой ответственности (далее – ЦФО). Смысл модели в следующем: каждый ЦФО должен каждые два дня предоставлять отчетную информацию. В случае предоставления в соответствующей ячейке (строка "Наименование ЦФО", столбец "Текущая дата") вручную проставляется значение "отчет", в случае не предоставления ячейка остается пустой и наименование ЦФО должно быть подсвечено красным цветом. Проект модели во вложении. На текущий момент с применением функции условного форматирования мной сделано:
1) Реализован механизм подсветки желтым цветом столбца с текущей датой для внесения информации о предоставлении ЦФО отчетных данных. Подсветка динамична, т.е. каждый день подсвечивается столбец с актуальной датой.
2) Реализован механизм подсветки красным цветом наименования ЦФО1 в случае задержки предоставления информации более чем на 2 дня (ячейки для заполнения выделены синим цветом). Как сделано:
   a. с помощью лог. функции "Адрес" Excel в строке 3 ищет текущую дату и в ячейке B13 возвращает ссылку на ячейку с текущей датой в строке 3.
   b. с помощью лог. функций "Счётесли" и "Смещ" Excel проверяет наличие надписи "Отчет" в ячейках G4 и H4. В случае наличия надписи хотя бы в одной ячейке наименование ЦФО1 не подсвечивается цветом, в случае отсутствия – подсвечивается.
Следуя такому способу, задачу можно решить, прописав для каждой строки с наименованием ЦФО отдельное правило условного форматирования, задавая диапазоны для анализа с помощью лог. функции "Смещ". На мой взгляд такой подход приемлем, если используется короткий перечень ЦФО.
Как создать одно общее правило условного форматирования для массива данных, чтобы данная модель работала? Буду очень признателен за ответы.

 
BEBL, очень много инфы...можно было просто написать как сделать то и то одним уф?..

не уверен что правильно понял
Код
=ИНДЕКС($4:$9;СТРОКА()-3;ПОИСКПОЗ(СЕГОДНЯ();$3:$3;0)-1)<>"отчет"
Изменено: Mershik - 27.02.2020 10:50:30
Не бойтесь совершенства. Вам его не достичь.
 
Mershik, большое спасибо! Замечание по поводу кол-ва информации принято.
1) Как в данной формуле работает аргумент СТРОКА () -3, от какой строки отнимается 3?
2) Как сделать, чтобы анализировались 2 столбца на предмет наличия слово "отчет" (в прилагаемом файле выделены синим)?
 
Цитата
BEBL написал:
от какой строки отнимается 3?
от той в которой находится формула ....
Цитата
BEBL написал:
Как сделать, чтобы анализировались 2 столбца на предмет наличия слово "отчет" (в прилагаемом файле выделены синим)?
чет ничего не понял
Изменено: Mershik - 28.02.2020 12:24:10
Не бойтесь совершенства. Вам его не достичь.
 
Если в ячейках столбцов H или I есть слово "отчет", то наименование ЦФО не выделяется красным. Если слова "отчет" нет ни в одном из этих столбцов - выделяется.

Цитата
Mershik написал: от той в которой находится формула ....
В примере формула начинает работать с 4-й строки. Если отнять 3 строки, то получаем 1-ю строку, а она пустая. Если возможно, опишите пожалуйста подробнее механизм.
 
BEBL, таким способом  СТРОКА () -3 мы определяем номер строки в указанном нами диапазоне строк $4:$9 так как у нас не с первой строки  данные для обработка,  а с 4 т.е 1 нам дает номер по порядку в строках $4:$9 .т.е. 1 идет 4, 2 идет 5 и т.д.
в , если бы она была с первой строки и в А1 нужно так же было подсветить цветом просто было бы строка()

как вариант что бы не выдумывать нового ничего и если я правильно понял:
Код
=И(ИНДЕКС($4:$9;СТРОКА()-3;ПОИСКПОЗ(СЕГОДНЯ();$3:$3;0)-1)<>"отчет";ИНДЕКС($4:$9;СТРОКА()-3;ПОИСКПОЗ(СЕГОДНЯ();$3:$3;0)-2)<>"отчет")
Изменено: Mershik - 28.02.2020 14:13:59
Не бойтесь совершенства. Вам его не достичь.
 
Mershik,
Большое спасибо!!!
Страницы: 1
Наверх