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