Za poslední týden se mi sešly dva prakticky stejné požadavky – sledování revizních termínů v Excelu. Nejjednodušší je pochopitelně užití podmíněného formátování a správných vzorců. A právě ty mohou někomu činit potíže.
Mějme dva sloupce. V prvním budeme doplňovat nejzazší termín, tedy datum, do kdy musí být úkol splněn, ve druhém potvrzení o provedení. Termín revize budeme přitom porovnávat s aktuálním datem a do svých úvah zahrneme i svátky (typicky blázinec před koncem roku).
Snímek obrazovky byl pořízen k datu 7.4.2014. Tabulka svátků obsahuje aktuální rok a rok následující (všechny datumy jsou výsledky vzorců dynamicky se měnící s rokem ve druhém řádku). Podmínky ve Správci pravidel musí být zapsány v uvedeném pořadí (Excel použije barvu pro první vyhovující podmínku ve směru shora dolů). Funkce A() představuje logickou spojku AND, čili „a současně“. V úvodu této funkce testujeme buňky, zda-li jsou prázdné (srovnání s prázdným řetězcem). Dále od termínu revize odečítáme daný počet pracovních dní (funkce WORKDAY) a obdržené datum porovnáme s aktuálním datem. Mezi dvěma po sobě jdoucími pracovními dny beru jako rozdíl 2 dny (revize musí být provedena buď dnes, nebo zítra, tj. od dnešního rána, do zítřejšího večera). Dolary v adresách buněk nejsou až tak podstatné, drží se daných sloupců při rozkopírování. Nic by vám nemělo bránit přizpůsobit si řešení (jiné časové limity, sloupce nemusí ležet u sebe, jejich pořadí je prohozeno apod.). Pomůckou pro ověření správností vám může být i funkce NETWORKDAYS.
V okamžiku splnění úkolu zapíše správce vedle data revize písmeno A (nebo cokoliv jiného) a dojde k automatickému obarvení buňky s datem na zeleno. Pokud byla revize provedena předčasně, změní správce také datum. V praxi ovšem doporučuji udělat v dohledné době jinou věc – naplánovat další revizi a datum té poslední zapsat do jiného sloupce.
P.S. V jednom ze zadání byl z legrace dopsán požadavek „aby to na mě houkalo“. Technicky je to možné. Do funkce listu lze zapracovat přehrání zvukového souboru. K tomu by ale docházelo při každém přepočtu listu pro všechny odpovídající buňky. Dopadlo by to tedy jak v Arabele, když poddaní proměněni v automobily vyjadřovali protest princezně Xénii.
Tip: Ačkoliv se pohybujeme na půdě Excelu, s daným typem úlohy si může dost dobře poradit také Microsoft Outlook. V jeho kalendáři lze velmi pohodlně nastavit událost, její opakování, upozornění na blížící se termín, přiřadit barvu, doprovodný text nebo třeba tabulku či plánek údržby a další poznámky. Z pohledu synchronizace a dostupnosti je na tom asi lépe jeho konkurent – Google Calendar.
Soubor ke stažení:
excel-revize-terminy.zip