V tomto videu se podíváme na to, jak vypočítat a zvýraznit data vypršení platnosti.
Řekněme, že vaše společnost zahájila nějaký program členství a váš šéf vám právě poslal sadu dat. Dala vám seznam 1000 lidí, kteří si za poslední rok obnovili členství, a hledá několik věcí.
Nejprve chce, abyste vypočítali datum vypršení platnosti o jeden rok v budoucnu, poslední den téhož měsíce bylo členství obnoveno.
Zadruhé, chce zjistit, kolik dní zbývá do vypršení platnosti.
Za třetí, chce vidět stav „Vypršela platnost“ pro všechna již vypršela platná členství a „Vyprší brzy“ pro všechna vypršení platnosti členství v příštích 30 dnech.
Nakonec řekla, že by bylo hezké vidět členy s prošlou platností zvýrazněné růžově a ty, jejichž platnost brzy vyprší, zvýrazněny žlutě.
Také - může to mít před poledním zasedáním v poledne?
No jistě.
Nejprve převeďme tato data na správnou tabulku Excel. Díky tomu bude mnohem jednodušší zadávat vzorce, protože Excel je automaticky zkopíruje dolů.
Nyní vypočítáme data vypršení platnosti. Mělo by to být na konci téhož měsíce o rok později, ale nejdříve použijeme jednoduchý hack, abychom se dostali na hřiště. Jak jste viděli v dřívějších videích, data jsou jen pořadová čísla, takže můžeme zadat pouze vzorec, který přidá 365 dní k obnovenému datu.
To je dobrý začátek. Můžeme dokončit hrubování řešení a vrátit se to opravit později.
Když řešíte složitější problém v aplikaci Excel, je dobré ověřit celkový přístup a na konci se vrátit k podrobnostem. Nechcete se hned na začátku zaseknout na maličkosti, zvláště pokud se přístup může změnit.
Nyní, když máme datum vypršení platnosti, můžeme nyní vypočítat zbývající dny. V budoucnu to bude nutné automaticky aktualizovat, takže použijeme funkci DNES, která vždy vrací dnešní datum.
Vzorec je jednoduše E5 minus DNES (). Když stisknu návrat, dostaneme dny zbývající do vypršení platnosti. Záporná čísla znamenají, že členství již vypršelo.
Pro stav použijeme jednoduchý vnořený vzorec IF. Pokud zbývá dní méně než nula, členství vypršelo. Jinak, pokud zbývá dní méně než 30, měl by být stav „Brzy vyprší“. Jinak je stav nic.
=IF(F5<0,"Expired",IF(F5<30,"Expiring soon",""))
Dále musíme vytvořit pravidla podmíněného formátování, která tyto hodnoty zvýrazní.
Nejprve vyberte data a nastavte aktivní buňku do pravého horního rohu. Poté vytvořte pravidlo vzorce, které testuje aktivní buňku na hodnotu „Vypršela platnost“. Sloupec musí být uzamčen.
Nyní opakujte stejný postup pro členství, jejichž platnost brzy vyprší.
Vypadá to dobře. Musíme jen opravit data vypršení platnosti, abychom přistáli poslední den v měsíci.
Ukázalo se, že existuje skvělá funkce zvaná EOMONTH (na konci měsíce), která získá poslední den měsíce v minulosti nebo budoucnosti.
Počáteční datum je obnoveno a měsíce jsou 12.
A tady to máte, všechno, co váš šéf chtěl, a ještě před obědem máte čas na šálek kávy.
Kurs
Základní vzorecSouvisející zkratky
Vložit tabulku Ctrl + T ⌃ + T Rozbalit nebo sbalit pás karet Ctrl + F1 ⌘ + ⌥ + R Vybrat tabulku Ctrl + A ⌘ + A Přesunout aktivní buňku ve výběru Ctrl + . ⌃ + ve směru hodinových ručiček .
