Как в excel сделать напоминание по дате

Создаем «напоминалку» в Excel

Когда вы работаете с таблицей, содержащей даты, которые нужно не пропустить при их наступлении, неизбежно возникает вопрос о том, как это гарантированно сделать. В Excel можно использовать различные средства для создания “напоминалки”. Прочитав эту статью, вы узнаете о них.

Для наглядности возьмем совсем несложный пример. Предположим, в некоторой таблице отмечается дата регистрации заказа и дата, когда он должен быть выполнен. За сутки до даты выполнения необходимо перезвонить клиенту. Какие варианты “напоминалок” можно использовать?

Во-первых, можно таблицу раскрасить. Например, строки, относящиеся к заказу, по поводу которого пора звонить, должны окрашиваться красным. Строки заказов, срок исполнения которых отстоит от сегодняшней даты более чем на 1 день, должны быть окрашены голубым. Строки заказов, срок исполнения которых уже прошел, вообще не окрашиваются. Последовательность действий для реализации этой “задумки”:

  • выделите столбцы таблицы (можно целиком, чтобы не ограничивать количество строк с данными);
  • в меню команды Условное форматирование (вкладка Главная) выберите строку Управление правилами;
  • создайте правило в виде формул: “=$C2-СЕГОДНЯ()>1” (формат: заливка голубым) и “=$C2-СЕГОДНЯ()=1” (формат: заливка красным);
  • примените созданные правила.

Обратите внимание, что ссылка на ячейку “$С2” смешанная (запрет установлен на изменение номера столбца С). Использование функции “СЕГОДНЯ()” позволит автоматизировать форматирование, так как при каждом открытии файла будет проверяться текущая дата и строки в таблице будут менять цвет заливки. Если результат вам не понравиться, удалите правила, выбрав соответствующую команду в меню кнопки Условное форматирование.

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

Читать еще:  Как сделать детализацию чужого номера

  • вставьте дополнительный столбец, например, перед таблицей;
  • в первую ячейку столбца проверки вставьте формулу: “=ЕСЛИ(D2-СЕГОДНЯ()=1;”Позвонить”;” “)“;

  • скопируйте ее в остальные ячейки столбца;
  • установите фильтр (вкладка Данные, кнопка Фильтр);
  • используйте фильтр для столбца Напоминание.

После того, как всем позвонили, снимите фильтр со столбца Напоминание и продолжайте ввод данных о новых заказах.

В-третьих, можно формировать список звонков с помощью расширенного фильтра.

Плюсом этого решения является создание отдельного списка напоминаний. Минусом — необходимость удаления сформированного списка перед формированием нового на следующий день. Но эту проблему можно решить, создав макрос.

В статье было предложено три варианта создания “напоминалки”. Конечно, возможны и другие, более сложные варианты. Но даже рассмотренные приемы существенно облегчат вашу ежедневную работу.

Пример формулы для выделения цветом просроченных дат в Excel

Во многих фирмах отдельное внимание уделяется датам, выпадающим после определенного пройденного периода. С помощью условного форматирования можно легко составить отчет «После периода» на котором выделены пройденные даты.

Как сделать подсвечивание цветом ячеек с датами пройденного срока в Excel

Пример представлен ниже на рисунке в виде отчета, в котором даты за более чем 90 дней от текущей даты выделенные другим цветом заливки.

Чтобы составить аналогичный отчет с таким же автоматическим форматированием ячеек по условию выполните следующее:

  1. Выделите целевой диапазон ячеек (в данном примере A3:A8) и выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило». В результате чего появится окно для внесения всех необходимых настроек инструмента:
  2. В появившемся окне из верхней части где находится список опций выберите пункт: «Использовать формулу для определения форматируемых ячеек». Данная опция позволяет нам использовать собственные формулы для составления сложны правил условного форматирования. Формула должна содержать логическое выражение и соответственно возвращать логическое значение для каждой ячейки из выделенного диапазона. Если будет возвращено – ИНСТИНА, тогда к этой ячейке будет применятся правило и присваивается новый формат, который предварительно настроен этим же инструментом.
  3. В полю ввода формул введите логическое выражение представленное на этом шаге. Данная формула проверяет значение ячеек: будет ли их дата выпадать после 90 дней, пройденных от сегодняшнего дня. Отсчитывается от даты, указанной в целевой ячейке A3 выделенного просматриваемого диапазона. Если да (ИСТИНА) – сразу же применяется условное форматирование.
Читать еще:  Как сделать дренаж вокруг дома

  • Нажмите на кнопку «Формат» для вызова окна, в которому будут доступные все опции оформления формата: цвет фона и границы, размер шрифта и т.п. После указания желаемых настроек для оформления стиля форматирования нажмите кнопку ОК на всех открытых окнах, чтобы подтвердить все настройки и получить готовый результат.
  • А в результате выделились все даты актуальность которых превышает 90 дней.

    Информирование пользователя MS EXCEL о принадлежности ДАТЫ к определенному диапазону

    Используем Условное форматирование для подачи сигнала пользователю MS EXCEL о принадлежности даты к определенному диапазону.

    Предположим, что пользователь вводит некие даты событий. Требуется, чтобы EXCEL автоматически выделял ячейки следующим образом:

    • красным, если дата совпадает с сегодняшним днем;
    • оранжевым, если вводимая дата находится в пределах 1 недели от сегодняшнего числа;
    • зеленым, если вводимая дата находится в пределах 2 недель от сегодняшнего числа.

    Сначала создадим правило, по которому ячейка будет выделяться, если дата совпадает с сегодняшним днем (см. Файл примера ):

    • выделите диапазон, в который пользователь будет вводить данные;
    • вызовите инструмент Условное форматирование ( Главная/ Стили/ Условное форматирование/ Управление правилами ). Откроется окно Диспетчер правил условного форматирования;
    • нажмите Создать правило;
    • выберите Форматировать только ячейки, которые содержат;
    • в выпадающем списке выберите Равно;
    • введите формулу =СЕГОДНЯ() ;
    • нажав кнопку Формат выберите, например, красный шрифт;

    • нажмите ОК и вернитесь в Диспетчер правил условного форматирования;

    Теперь создадим правило, по которому ячейка будет выделяться, если дата находится в пределах 1 недели от сегодняшнего числа:

    • нажмите Создать правило;
    • выберите Форматировать только ячейки, которые содержат;
    • в выпадающем списке выберите Между;
    • введите формулы =СЕГОДНЯ()-7 и =СЕГОДНЯ()+7 ;
    • нажав кнопку Формат выберите, например, оранжевый шрифт;

    Проделайте аналогичные шаги для создания правила, по которому ячейка будет выделяться если дата находится в пределах двух недель от сегодняшнего числа, используйте формулы =СЕГОДНЯ()-14 и =СЕГОДНЯ()+14 .

    Читать еще:  Закончилась трудовая книжка как сделать запись

    ВНИМАНИЕ!
    Когда к диапазону ячеек применяются два или более правил Условного форматирования, приоритет обработки определяется порядком их перечисления в Диспетчере правил условного форматирования. Правило, расположенное в списке выше, имеет более высокий приоритет, чем правило, расположенное в списке ниже. Новые правила всегда добавляются в начало списка и поэтому обладают более высоким приоритетом, однако порядок правил можно изменить в диалоговом окне при помощи кнопок со стрелками Вверх и Вниз.

    Если мы расположим правила как показано на предыдущем рисунке, то при вводе сегодняшней даты она выделится зеленым цветом, а не красным, т.к. правило (Между =СЕГОДНЯ()-14 и =СЕГОДНЯ()+14 ) у нас идет первым и имеет наивысший приоритет. Для правильного отображения поменяем порядок критериев, используя соответствующие стрелочки.

    В результате получим вот такую картину.

    СОВЕТ:
    Чтобы найти все ячейки на листе, к которым применены правила Условного форматирования необходимо:

    • на вкладке Главная в группе Редактирование щелкните стрелку рядом с командой Найти и выделить;
    • выберите в списке пункт Условное форматирование;
    • будут выделены все ячейки, которым применены правила Условного форматирования.

    Источники:

    http://microsoft-help.ru/47-sozdaem-napominalku-v-excel.html

    http://exceltable.com/formuly/vydelenie-prosrochennyh-dat

    http://excel2.ru/articles/informirovanie-polzovatelya-ms-excel-o-prinadlezhnosti-daty-k-opredelennomu-diapazonu

    Ссылка на основную публикацию
    Статьи на тему: