В этом видео мы рассмотрим, как рассчитывать и выделять срок годности.
Предположим, ваша компания начала какую-то программу членства, и ваш начальник только что отправил вам набор данных. Она дала вам список из 1000 человек, которые возобновили членство в прошлом году или около того, и она ищет несколько вещей.
Во-первых, она хочет, чтобы вы рассчитали дату истечения срока через год в будущем, в последний день того же месяца, когда членство было продлено.
Во-вторых, она хочет узнать, сколько дней осталось до истечения срока.
В-третьих, она хочет видеть статус «Срок действия истек» для любого членства, срок действия которого уже истек, и статус «Срок действия истекает» для любого членства, срок действия которого истекает в ближайшие 30 дней.
Наконец, она сказала, что было бы неплохо, если бы просроченные участники были выделены розовым цветом, а те, которые скоро истекают, - желтым.
И еще - может ли она съесть его перед обедом в полдень?
Умм, конечно.
Во-первых, давайте преобразуем эти данные в правильную таблицу Excel. Это значительно упростит ввод формул, поскольку Excel автоматически копирует их по мере продвижения.
Теперь посчитаем срок годности. Предполагается, что они произойдут в конце того же месяца, через год, но давайте сначала воспользуемся простым хаком, чтобы приблизиться. Как вы видели в предыдущих видеороликах, даты - это просто порядковые номера, поэтому мы можем просто ввести формулу, которая добавляет 365 дней к дате продления.
Это хорошее начало. Мы можем закончить черновой набросок решения и вернуться, чтобы исправить это позже.
Когда вы решаете более сложную задачу в Excel, рекомендуется проверить общий подход, а затем вернуться к деталям в конце. Не стоит с самого начала зацикливаться на мелочах, особенно если подход может измениться.
Теперь, когда у нас есть срок действия, мы можем рассчитать оставшиеся дни. Это требует автоматического обновления в будущем, поэтому мы будем использовать функцию СЕГОДНЯ, которая всегда возвращает сегодняшнюю дату.
Формула просто E5 минус СЕГОДНЯ (). Когда я нажимаю "возврат", до истечения срока действия остаются дни. Отрицательные числа указывают на то, что членство уже истекло.
Для статуса мы будем использовать простую вложенную формулу ЕСЛИ. Если осталось меньше нуля дней, членство истекло. В противном случае, если осталось меньше 30 дней, статус должен быть «Срок действия скоро истекает». В противном случае статус ничего.
=IF(F5<0,"Expired",IF(F5<30,"Expiring soon",""))
Затем нам нужно создать правила условного форматирования, которые выделяют эти значения.
Сначала выберите данные и установите активную ячейку в правый верхний угол. Затем создайте правило формулы, которое проверяет активную ячейку на значение «Срок действия истек». Столбец должен быть заблокирован.
Теперь повторите тот же процесс для членства, срок действия которого скоро истекает.
Выглядит хорошо. Нам просто нужно зафиксировать даты истечения срока, чтобы они приходили в последний день месяца.
Что ж, оказывается, есть классная функция под названием EOMONTH (для конца месяца), которая получает последний день месяца в прошлом или будущем.
Дата начала - дата обновления, месяцев - 12.
И вот, все, что хотел ваш босс, и у вас еще есть время выпить чашку кофе перед обедом.
Курс
Основная формулаСвязанные ярлыки
Вставить таблицу Ctrl + T ⌃ + T Развернуть или свернуть ленту Ctrl + F1 ⌘ + ⌥ + R Выбрать таблицу Ctrl + A ⌘ + A Переместить активную ячейку по часовой стрелке при выделении Ctrl + . ⌃ + .
