У цьому відео ми розглянемо, як розрахувати та виділити дати закінчення терміну дії.
Скажімо, ваша компанія розпочала якусь програму членства, а ваш бос щойно надіслав вам набір даних. Вона надала вам список із 1000 людей, які відновили членство протягом останнього року чи близько того, і вона шукає кілька речей.
По-перше, вона хоче, щоб ви розрахували термін придатності один рік у майбутньому, в останній день того ж місяця членство було поновлено.
По-друге, вона хоче побачити, скільки днів залишається до закінчення терміну дії.
По-третє, вона хоче бачити статус "Термін дії закінчився" для будь-якого членства, яке вже закінчилося, та "Термін дії закінчується незабаром" для будь-якого членства, що закінчується протягом наступних 30 днів.
Нарешті, вона сказала, що було б непогано бачити, що члени, яким закінчився термін дії, виділені рожевим кольором, а ті, що закінчуються, незабаром виділені жовтим.
Також - чи може вона взяти його перед обідньою зустріччю опівдні?
Мда, звичайно.
Спочатку перетворимо ці дані у відповідну таблицю Excel. Це полегшить введення формул, оскільки Excel автоматично копіює їх, як ми рухаємось.
Тепер давайте розрахуємо термін придатності. Це повинно бути наприкінці того ж місяця через рік, але давайте просто спочатку скористаємось простим рубанням, щоб потрапити на стадіон. Як ви бачили у попередніх відео, дати є лише серійними номерами, тому ми можемо просто ввести формулу, яка додає 365 днів до оновленої дати.
Це хороший початок. Ми можемо закінчити обробку рішення і повернутися, щоб виправити це пізніше.
Коли ви вирішуєте складнішу проблему в Excel, гарною ідеєю є перевірити загальний підхід, а потім повернутися до деталей наприкінці. Ви не хочете застрягти на дрібниці з самого початку, особливо якщо підхід може змінитися.
Тепер, коли у нас є термін придатності, ми тепер можемо розрахувати залишилися дні. Це потребує автоматичного оновлення в майбутньому, тому ми будемо використовувати функцію TODAY, яка завжди повертає сьогоднішню дату.
Формула просто E5 мінус СЬОГОДНЯ (). Коли я натискаю return, ми отримуємо дні, що залишились до закінчення терміну дії. Від’ємні цифри означають, що термін членства вже закінчився.
Для статусу ми будемо використовувати просту вкладену формулу IF. Якщо днів залишається менше нуля, членство закінчується. В іншому випадку, якщо днів залишається менше 30, статус повинен бути "Скоро закінчується". Інакше статус нічого.
=IF(F5<0,"Expired",IF(F5<30,"Expiring soon",""))
Далі нам потрібно створити правила умовного форматування, які б виділяли ці значення.
Спочатку виберіть дані та встановіть активну комірку у верхній правий кут. Потім створіть правило формули, яке перевіряє активну комірку на значення "Термін дії минув". Стовпець повинен бути заблокований.
Тепер повторіть той самий процес для членства, яке "скоро закінчується".
Це добре виглядає. Нам просто потрібно встановити термін придатності, щоб висадитися в останній день місяця.
Ну, виявляється, є крута функція під назвою EOMONTH (на кінець місяця), яка отримує останній день місяця в минулому чи майбутньому.
Дата початку - дата поновлена, а місяці - 12.
І ось у вас все, все, що хотів ваш начальник, і ви все ще встигаєте випити кави перед обідом.
Звичайно
Основна формулаПов’язані ярлики
Вставити таблицю Ctrl + T ⌃ + T Розгорнути або згорнути стрічку Ctrl + F1 ⌘ + ⌥ + R Виділити таблицю Ctrl + A ⌘ + A Перемістити активну комірку за годинниковою стрілкою у виді Ctrl + . ⌃ + .
