În acest videoclip, vom analiza cum se calculează și se evidențiază datele de expirare.
Să presupunem că compania dvs. a început un fel de program de membru și șeful dvs. tocmai v-a trimis un set de date. Ți-a dat o listă de 1000 de persoane care și-au reînnoit calitatea de membru în ultimul an sau cam așa și caută mai multe lucruri.
În primul rând, ea dorește să calculați o dată de expirare cu un an în viitor, în ultima zi a aceleiași luni, calitatea de membru a fost reînnoită.
În al doilea rând, ea vrea să vadă câte zile mai rămân înainte de expirare.
În al treilea rând, ea dorește să vadă starea „Expirat” pentru orice membru deja expirat și „Expirând în curând” pentru orice membru care expiră în următoarele 30 de zile.
În cele din urmă, a spus că ar fi frumos să vedem membrii expirați evidențiați în roz, iar cei care expirau în curând evidențiați în galben.
De asemenea - o poate avea înainte de întâlnirea de prânz la prânz?
Umm, sigur.
În primul rând, să convertim aceste date într-un tabel Excel adecvat. Acest lucru va facilita introducerea formulelor, deoarece Excel le va copia automat, pe măsură ce mergem.
Acum să calculăm datele de expirare. Acestea ar trebui să fie la sfârșitul aceleiași luni, un an mai târziu, dar hai să folosim mai întâi un simplu hack pentru a intra în stadion. După cum ați văzut în videoclipurile anterioare, datele sunt doar numere de serie, deci putem introduce doar o formulă care adaugă 365 de zile la data reînnoită.
Acesta este un început bun. Putem termina de eliminat soluția și ne putem întoarce mai târziu.
Când rezolvați o problemă mai complexă în Excel, este o idee bună să validați abordarea generală, apoi reveniți la detalii la final. Nu doriți să vă blocați la un lucru mic chiar la început, mai ales dacă abordarea se poate schimba.
Acum, că avem o dată de expirare, putem calcula zilele rămase. Acest lucru trebuie actualizat automat în viitor, așa că vom folosi funcția TODAY, care revine întotdeauna data de azi.
Formula este pur și simplu E5 minus TODAY (). Când ajung la return, mai rămânem zile înainte de expirare. Numerele negative indică un abonament deja expirat.
Pentru stare, vom folosi o formulă IF cuibărită simplă. Dacă zilele rămase sunt mai mici de zero, calitatea de membru este expirată. În caz contrar, dacă zilele rămase sunt mai mici de 30, starea ar trebui să fie „Expiră în curând”. În caz contrar, statutul nu este nimic.
=IF(F5<0,"Expired",IF(F5<30,"Expiring soon",""))
În continuare, trebuie să creăm reguli de formatare condiționată care să evidențieze aceste valori.
Mai întâi, selectați datele și setați celula activă în colțul din dreapta sus. Apoi, creați o regulă de formulă care testează celula activă pentru valoarea „Expirat”. Coloana trebuie blocată.
Acum repetați același proces pentru membrii care „Expiră în curând”.
Arată bine. Trebuie doar să stabilim datele de expirare pentru a ateriza în ultima zi a lunii.
Ei bine, se pare că există o funcție interesantă numită EOMONTH (pentru sfârșitul lunii) care primește ultima zi a unei luni în trecut sau în viitor.
Data de începere este reînnoită, iar lunile sunt 12.
Și iată-l, tot ce își dorea șeful tău și încă mai ai timp pentru o ceașcă de cafea înainte de prânz.
Curs
Formula de bazăComenzi rapide asociate
Introduceți tabelul Ctrl + T ⌃ + T Extindeți sau restrângeți panglica Ctrl + F1 ⌘ + ⌥ + R Selectați tabelul Ctrl + A ⌘ + A Mutați celula activă în sensul acelor de ceasornic în selecție Ctrl + . ⌃ + .
