V tomto videu sa pozrieme na to, ako vypočítať a zvýrazniť dátumy vypršania platnosti.
Povedzme, že vaša spoločnosť začala nejaký program členstva a váš šéf vám práve poslal súbor údajov. Dala vám zoznam 1 000 ľudí, ktorí si za posledný rok obnovili členstvo, a hľadá niekoľko vecí.
Najprv chce, aby ste vypočítali dátum vypršania platnosti o jeden rok v budúcnosti, v posledný deň toho istého mesiaca bolo členstvo obnovené.
Po druhé, chce zistiť, koľko dní zostáva do vypršania platnosti.
Po tretie, chce si pozrieť stav „Platnosť vypršala“ pre všetky už skončené členstvá a „Stav čoskoro vyprší“ pre všetky členstvá, ktorých platnosť vyprší nasledujúcich 30 dní.
Nakoniec povedala, že by bolo pekné vidieť členov s uplynutou platnosťou zvýraznených ružovou farbou a tých, ktorých platnosť čoskoro vyprší, zvýraznená žltou farbou.
Tiež - môže si ju dať pred obedným stretnutím na pravé poludnie?
Hm, jasné.
Najskôr konvertujme tieto údaje na správnu tabuľku programu Excel. Takto sa budú vzorce oveľa ľahšie zadávať, pretože program Excel ich bude za pochodu automaticky kopírovať nadol.
Teraz poďme vypočítať dátumy vypršania platnosti. Predpokladá sa, že budú na konci toho istého mesiaca o rok neskôr, najskôr však použijeme jednoduchý hack, aby sme sa dostali do guľového parku. Ako ste videli v predchádzajúcich videách, dátumy sú iba poradové čísla, takže môžeme zadať iba vzorec, ktorý k obnovenému dátumu pridá 365 dní.
Je to dobrý začiatok. Môžeme dokončiť hrubovanie riešenia a vrátiť sa ho opraviť neskôr.
Pri riešení zložitejšieho problému v programe Excel je dobré overiť celkový prístup a potom sa na konci vrátiť k podrobnostiam. Nechcete, aby ste hneď na začiatku uviazli na maličkosti, najmä ak sa prístup môže zmeniť.
Teraz, keď máme dátum vypršania platnosti, môžeme teraz vypočítať zostávajúce dni. To si v budúcnosti bude vyžadovať automatickú aktualizáciu, takže použijeme funkciu DNES, ktorá vždy vráti dnešný dátum.
Vzorec je jednoducho E5 mínus DNES (). Keď kliknem na návrat, zostávajú dni do vypršania platnosti. Záporné čísla znamenajú, že členstvo už vypršalo.
Pre stav použijeme jednoduchý vnorený vzorec IF. Ak zostávajúcich dní bude menej ako nula, členstvo zanikne. V opačnom prípade, ak zostávajúcich dní je menej ako 30, mal by byť stav „Čoskoro vyprší“. Inak stav nič.
=IF(F5<0,"Expired",IF(F5<30,"Expiring soon",""))
Ďalej musíme vytvoriť pravidlá podmieneného formátovania, ktoré zvýraznia tieto hodnoty.
Najskôr vyberte údaje a aktívnu bunku nastavte do pravého horného rohu. Potom vytvorte pravidlo vzorca, ktoré testuje aktívnu bunku na hodnotu „Platnosť vypršala“. Stĺpec musí byť uzamknutý.
Teraz zopakujte ten istý postup pre členstvá, ktorých platnosť čoskoro vyprší.
Toto vyzerá dobre. Potrebujeme iba opraviť dátumy vypršania platnosti, aby sme pristáli posledný deň v mesiaci.
Ukázalo sa, že existuje skvelá funkcia zvaná EOMONTH (na konci mesiaca), ktorá získa posledný deň v mesiaci v minulosti alebo v budúcnosti.
Dátum začatia je obnovený a mesiace sú 12.
A tam to máte, všetko, čo váš šéf chcel, a ešte pred obedom si čas na kávu dáte.
Samozrejme
Základný vzorecSúvisiace skratky
Vložiť tabuľku Ctrl + T ⌃ + T Rozbaliť alebo zbaliť pásku Ctrl + F1 ⌘ + ⌥ + R Vybrať tabuľku Ctrl + A ⌘ + A Presunúť aktívnu bunku vo výbere Ctrl + . ⌃ + v smere hodinových ručičiek .
