Добрый день, уважаемые форумчане. Есть файл с заявками, поступающими через форму, далее со всех листов организована подкачка данных на общий сводный. Листы 1/2/3 - это данные из форм Лист общая - объединение и автоматическое распределение данных по дате.
Есть задача сделать правило для условного форматирования, чтобы подсвечивались ячейки не содержащие текст, но не все, а только те, в строках которых есть хоть какие-то данные. (т.е. чтобы выделенный диапазон не подсвечивался в пустых строках) При этом, при добавлении новых строк - автоматически добавлялась бы, но только на определенные ячейки, а не на весь выделенный диапазон столбца.
На просторах не нашёл формул для условного форматирования.. Буду рад любому совету
Добрый день, уважаемые форумчане. Есть файл с заявками, поступающими через форму, далее со всех листов организована подкачка данных на общий сводный. Листы 1/2/3 - это данные из форм Лист общая - объединение и автоматическое распределение данных по дате.
Есть задача сделать правило для условного форматирования, чтобы подсвечивались ячейки не содержащие текст, но не все, а только те, в строках которых есть хоть какие-то данные. (т.е. чтобы выделенный диапазон не подсвечивался в пустых строках) При этом, при добавлении новых строк - автоматически добавлялась бы, но только на определенные ячейки, а не на весь выделенный диапазон столбца.
По всему диапазону не знаю как сделать, но если каждому столбцу давать свою формулу условного форматирования получится так: Предварительно убираем все текущие условные форматы, чтоб не запутаться. Для диапазона E2:E Выбираем диапазон - Формат - Условное форматирование - Ваша формула, в поле формулы вводим:
В данном виде формула смотрит допустим ячейку Е2 - если она пуста то далее смотрим А2 - если пуста и А2 - форматирование не применяем, если А2 не пуста то применяем форматирование , т.е. проверка идет только по 1 столбцу, если у вас рандомно заполняются не все столбцы, то нужно посложнее писать формулу. Сами посмотрите, возможно нужно будет использовать IFS [offtop]P.S. если даете тестовую таблицу то и доступ на неё откройте.[/offtop]
По всему диапазону не знаю как сделать, но если каждому столбцу давать свою формулу условного форматирования получится так: Предварительно убираем все текущие условные форматы, чтоб не запутаться. Для диапазона E2:E Выбираем диапазон - Формат - Условное форматирование - Ваша формула, в поле формулы вводим:
В данном виде формула смотрит допустим ячейку Е2 - если она пуста то далее смотрим А2 - если пуста и А2 - форматирование не применяем, если А2 не пуста то применяем форматирование , т.е. проверка идет только по 1 столбцу, если у вас рандомно заполняются не все столбцы, то нужно посложнее писать формулу. Сами посмотрите, возможно нужно будет использовать IFS [offtop]P.S. если даете тестовую таблицу то и доступ на неё откройте.[/offtop]Kashimirush
[offtop]И еще в вашей тестовой таблице есть ссылки на ваши рабочие таблицы в функции квери, а в таблицах доступ на редактирование есть, я бы так не стал рисковать)[/offtop]
[offtop]И еще в вашей тестовой таблице есть ссылки на ваши рабочие таблицы в функции квери, а в таблицах доступ на редактирование есть, я бы так не стал рисковать)[/offtop]Kashimirush
Работа, работа, перейди на Федота...
Сообщение отредактировал Kashimirush - Пятница, 27.09.2019, 12:07
Спасибо, логика в этом есть. Только проблема в том, что ArraFormula почему-то не протягивает. (либо по какой-то другой причине) - форматирование распространяется только на ячейку, в которой формула.
Спасибо, логика в этом есть. Только проблема в том, что ArraFormula почему-то не протягивает. (либо по какой-то другой причине) - форматирование распространяется только на ячейку, в которой формула.ZAA
Сообщение отредактировал ZAA - Понедельник, 30.09.2019, 13:36
Формула пишется в настройках условного форматирования, а не в ячейке. Вот рабочий пример Проверьте правильность диапазона который задаете в условном форматировании и в формуле.
Формула пишется в настройках условного форматирования, а не в ячейке. Вот рабочий пример Проверьте правильность диапазона который задаете в условном форматировании и в формуле.Kashimirush
Спасибо, разобрался. Вставлял изначально в условное форматирование, просто не указал корректный диапазон. Не будет ли документ тормозить от такой формулы? (1000 а то и 5000 ячеек)
p.s. проверил формулу при корректном диапазоне - будет работать и без arrayformula. Но, правильно ли я понимаю, что без неё не будет автоматически продлеваться формула при введении новых данных?
Спасибо, разобрался. Вставлял изначально в условное форматирование, просто не указал корректный диапазон. Не будет ли документ тормозить от такой формулы? (1000 а то и 5000 ячеек)
p.s. проверил формулу при корректном диапазоне - будет работать и без arrayformula. Но, правильно ли я понимаю, что без неё не будет автоматически продлеваться формула при введении новых данных?ZAA
Но, правильно ли я понимаю, что без неё не будет автоматически продлеваться формула при введении новых данных?
Нет arrayformula - выводит массив данных (если введете формулу допустим просто в ячейку без arrayformula, выйдет ответ только на 1 ячейку, с arrayformula выйдет весь диапазон) - раньше в условном форматировании нужно было вводить формулы в виде массивов, странно теперь работает и так, возможно что-то гугл поменял. Тем не менее диапазон форматирования вы задаете сами и форматироваться будут только те ячейки, которые вы указали в окне "Условного форматирования".
Но, правильно ли я понимаю, что без неё не будет автоматически продлеваться формула при введении новых данных?
Нет arrayformula - выводит массив данных (если введете формулу допустим просто в ячейку без arrayformula, выйдет ответ только на 1 ячейку, с arrayformula выйдет весь диапазон) - раньше в условном форматировании нужно было вводить формулы в виде массивов, странно теперь работает и так, возможно что-то гугл поменял. Тем не менее диапазон форматирования вы задаете сами и форматироваться будут только те ячейки, которые вы указали в окне "Условного форматирования".Kashimirush
Работа, работа, перейди на Федота...
Сообщение отредактировал Kashimirush - Понедельник, 30.09.2019, 13:59
Добавлю свой пятачок в тему. В принципе, всё немного проще. Формулу для условного форматирования достаточно прописать в простом виде для одной левой верхней ячейки диапазона. Без открытых (бесконечных) диапазонов типа E2:E и без формул массива.
При этом адрес ячейки E2 по условиям данной задачи должен быть полностью относительным, т.е. без знаков $. Тогда при "мысленном копировании" в правую соседнюю ячейку адрес E2 в ней будет учитываться уже как F2, а при рассмотрении следующей ячейки справа - как G2. Адрес же ячейки A2 должен выглядеть неизменно как А2 во всех трех случаях: и для E2, и для F2, и для G2. При обычном (на рабочем листе) копировании формулы из ячейки E2 - в ячейки F2:G2 такое постоянство адреса A2 достигается фиксацией абсолютного адреса столбца при помощи знака $, т.е. $A2
Итого формулу УФ для диапазона E2:G1500 (с тремя столбцами и кучей строк!) можно записать как формулу для одной(!) левой верхней ячейки: [vba]
Код
=IF(LEN(E2)=0;IF(LEN($A2)=0;FALSE;TRUE);FALSE)
[/vba] В остальных ячейках диапазона УФ "сыграет" автоматически - и фактически с учетом правил преобразования относительных и абсолютных адресов при копировании.
Добавлю свой пятачок в тему. В принципе, всё немного проще. Формулу для условного форматирования достаточно прописать в простом виде для одной левой верхней ячейки диапазона. Без открытых (бесконечных) диапазонов типа E2:E и без формул массива.
При этом адрес ячейки E2 по условиям данной задачи должен быть полностью относительным, т.е. без знаков $. Тогда при "мысленном копировании" в правую соседнюю ячейку адрес E2 в ней будет учитываться уже как F2, а при рассмотрении следующей ячейки справа - как G2. Адрес же ячейки A2 должен выглядеть неизменно как А2 во всех трех случаях: и для E2, и для F2, и для G2. При обычном (на рабочем листе) копировании формулы из ячейки E2 - в ячейки F2:G2 такое постоянство адреса A2 достигается фиксацией абсолютного адреса столбца при помощи знака $, т.е. $A2
Итого формулу УФ для диапазона E2:G1500 (с тремя столбцами и кучей строк!) можно записать как формулу для одной(!) левой верхней ячейки: [vba]
Код
=IF(LEN(E2)=0;IF(LEN($A2)=0;FALSE;TRUE);FALSE)
[/vba] В остальных ячейках диапазона УФ "сыграет" автоматически - и фактически с учетом правил преобразования относительных и абсолютных адресов при копировании.Gustav
Без открытых (бесконечных) диапазонов типа E2:E и без формул массива.
Круто,я не знал. У меня давненько такая задача была, написать формулу для форматирования, тогда я решил её массивом. Думал, что выводить гуглу нужно именно массив, чтобы он форматировал весь выбранный диапазон.
Без открытых (бесконечных) диапазонов типа E2:E и без формул массива.
Круто,я не знал. У меня давненько такая задача была, написать формулу для форматирования, тогда я решил её массивом. Думал, что выводить гуглу нужно именно массив, чтобы он форматировал весь выбранный диапазон. Kashimirush
Работа, работа, перейди на Федота...
Сообщение отредактировал Kashimirush - Понедельник, 30.09.2019, 16:01
В остальных ячейках диапазона УФ "сыграет" автоматически - и фактически с учетом правил преобразования относительных и абсолютных адресов при копировании.
Спасибо большое. Тогда ещё вопрос - "протянется" ли данное УФ вниз по стобцу без диапазона применения E2:E? у меня не протянулась, даже с добавлением arrayformula/
В идеале нужно, чтобы форматирование применялось только я заполненным строкам, чтобы таблица "не висла" каждый раз, применяя к 1000/10000 строк
В остальных ячейках диапазона УФ "сыграет" автоматически - и фактически с учетом правил преобразования относительных и абсолютных адресов при копировании.
Спасибо большое. Тогда ещё вопрос - "протянется" ли данное УФ вниз по стобцу без диапазона применения E2:E? у меня не протянулась, даже с добавлением arrayformula/
В идеале нужно, чтобы форматирование применялось только я заполненным строкам, чтобы таблица "не висла" каждый раз, применяя к 1000/10000 строкZAA
Сообщение отредактировал ZAA - Вторник, 01.10.2019, 12:18
Тогда ещё вопрос - "протянется" ли данное УФ вниз по стобцу без диапазона применения E2:E? у меня не протянулась, даже с добавлением arrayformula/
Давайте разберемся. Есть диапазон действия правила УФ, который задается вне формулы (т.е. в другом поле, нежели формула), а есть некий бесконечный диапазон, который может быть включен в саму формулу, например, E2:G. Поскольку моя формула простая и в ней нет бесконечных диапазонов, то я рассматриваю здесь только первый вариант.
Задавая диапазон действия правила УФ, можно написать в этом поле бесконечный диапазон E2:G и сохранить. И на первый взгляд всё нормально сохранится с этим бесконечным адресом, НО(!) если дальше снова переоткрыть это правило для редактирования, то увидим, что адрес превратился в "небесконечный" вида E2:G<номер последней имеющейся строки таблицы>. В моем случае при кол-ве строк таблицы = 1500 адрес диапазона УФ приобрел вид E2:G1500.
Если далее я буду вставлять в конец таблицы ПОЛНЫЕ строки (т.е. не прямоугольный диапазон в середине таблицы, а именно полные строки через всю таблицу - от левого края до правого края), например, с помощью: * кнопки "Добавить строки внизу" (в нижней части экрана) с указанием кол-ва строк * команды контекстного меню "Вставить строку" * команды контекстного меню "Вставить строки выше:" * команды контекстного меню "Вставить строки ниже:" * строки скрипта: sheet.appendRow([...]) то диапазон правила УФ будет автоматически изменяться с учетом нового номера самой последней строки.
Если же я буду вставлять в середину таблицы командой "Вставить ячейки" произвольный прямоугольный диапазон размером, скажем 4 строки х 3 столбца, ПЕРЕСЕКАЮЩИЙСЯ хоть одной общей ячейкой с диапазоном правила УФ, то это вызовет масштабную реорганизацию: как за счет появления новых правил УФ (по сути аналогичных исходному, но с другим диапазоном действия), так и за счет дробления адреса исходного диапазона на несколько областей, перечисленных через запятую. При регулярной вставке подобных диапазонов происходит дальнейшее дробление фактически одинаковых правил УФ, а их сопровождение становится сущим адом, пока наконец не принимается решение вычистить их - путем полного их удаления и воссоздания только одного с простой исходной формулой и простым же (но обновленным) адресом диапазона применения.
МОРАЛЬ: Расширяйте вашу таблицу по возможности только ПОЛНЫМИ строками и тогда первоначально настроенное (до последней строки!) правило УФ никуда не "съедет". Последняя строка при этом выполняет роль как бы аналога бесконечности.
Тогда ещё вопрос - "протянется" ли данное УФ вниз по стобцу без диапазона применения E2:E? у меня не протянулась, даже с добавлением arrayformula/
Давайте разберемся. Есть диапазон действия правила УФ, который задается вне формулы (т.е. в другом поле, нежели формула), а есть некий бесконечный диапазон, который может быть включен в саму формулу, например, E2:G. Поскольку моя формула простая и в ней нет бесконечных диапазонов, то я рассматриваю здесь только первый вариант.
Задавая диапазон действия правила УФ, можно написать в этом поле бесконечный диапазон E2:G и сохранить. И на первый взгляд всё нормально сохранится с этим бесконечным адресом, НО(!) если дальше снова переоткрыть это правило для редактирования, то увидим, что адрес превратился в "небесконечный" вида E2:G<номер последней имеющейся строки таблицы>. В моем случае при кол-ве строк таблицы = 1500 адрес диапазона УФ приобрел вид E2:G1500.
Если далее я буду вставлять в конец таблицы ПОЛНЫЕ строки (т.е. не прямоугольный диапазон в середине таблицы, а именно полные строки через всю таблицу - от левого края до правого края), например, с помощью: * кнопки "Добавить строки внизу" (в нижней части экрана) с указанием кол-ва строк * команды контекстного меню "Вставить строку" * команды контекстного меню "Вставить строки выше:" * команды контекстного меню "Вставить строки ниже:" * строки скрипта: sheet.appendRow([...]) то диапазон правила УФ будет автоматически изменяться с учетом нового номера самой последней строки.
Если же я буду вставлять в середину таблицы командой "Вставить ячейки" произвольный прямоугольный диапазон размером, скажем 4 строки х 3 столбца, ПЕРЕСЕКАЮЩИЙСЯ хоть одной общей ячейкой с диапазоном правила УФ, то это вызовет масштабную реорганизацию: как за счет появления новых правил УФ (по сути аналогичных исходному, но с другим диапазоном действия), так и за счет дробления адреса исходного диапазона на несколько областей, перечисленных через запятую. При регулярной вставке подобных диапазонов происходит дальнейшее дробление фактически одинаковых правил УФ, а их сопровождение становится сущим адом, пока наконец не принимается решение вычистить их - путем полного их удаления и воссоздания только одного с простой исходной формулой и простым же (но обновленным) адресом диапазона применения.
МОРАЛЬ: Расширяйте вашу таблицу по возможности только ПОЛНЫМИ строками и тогда первоначально настроенное (до последней строки!) правило УФ никуда не "съедет". Последняя строка при этом выполняет роль как бы аналога бесконечности.Gustav
Господа, тысячу извинений. У меня похожий вопрос. Можно тут его задать? В таблице применяю форматирование - подсветка красным если в одной ячейке число больше числа в другой ячейке. Что нужно сделать, чтобы форматирование не работало, когда ячейка пуста? Дело в том, что оно пустоту видит как число которое меньше.
Господа, тысячу извинений. У меня похожий вопрос. Можно тут его задать? В таблице применяю форматирование - подсветка красным если в одной ячейке число больше числа в другой ячейке. Что нужно сделать, чтобы форматирование не работало, когда ячейка пуста? Дело в том, что оно пустоту видит как число которое меньше.fopol
Скорее всего по правилам нужно было создать новую тему. А так для поиска пустых значений есть функция ISBLANK В вашем случае допустим если нужно закрашивать когда А больше В, для всех заполненных А, будет следующая формула:
Скорее всего по правилам нужно было создать новую тему. А так для поиска пустых значений есть функция ISBLANK В вашем случае допустим если нужно закрашивать когда А больше В, для всех заполненных А, будет следующая формула:
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
