Jeden z typických úkolů pro uživatele Microsoft Excel. Máme dva rozsahy dat typu „začátek-konec“. Úkolem je určit, zda se tyto rozsahy překrývají, a pokud ano, o kolik dní.
Protínají se nebo ne?
Začněme řešením otázky, zda v principu existuje průnik intervalů? Předpokládejme, že máme tabulku pracovních směn pro zaměstnance, jako je tato:
Je jasně vidět, že pracovní směny Yaroslava a Eleny se prolínají, ale jak to vypočítat, aniž byste se uchýlili k sestavení kalendáře a vizuální kontroly? Funkce nám pomůže SUMPRODUCT (SUMPRODUKT).
Vložme do naší tabulky další sloupec se vzorcem, který poskytne booleovskou hodnotu TRUE, pokud se data protnou:
Kolik dní je přejezd?
Pokud v zásadě není snadné porozumět tomu, zda se naše intervaly protínají nebo ne, ale přesně vědět, kolik dní přesně spadá do průsečíku, pak se úkol stává složitějším. Logicky je nutné „napumpovat“ až 3 různé situace v jednom vzorci:
- intervaly se nepřekrývají
- jeden z intervalů zcela pohltí druhý
- intervaly se částečně protínají
Čas od času vidím implementaci tohoto přístupu jinými uživateli pomocí hromady vnořených funkcí IF atd.
Vlastně vše se dá krásně udělat pomocí funkce MEDIAN (MEDIÁN) z kategorie Statistický.
Označíme-li podmíněně začátek prvního intervalu jako N1, a konec pro K1a začátek druhého N2 a skončit pro K2, pak lze obecně náš vzorec napsat jako:
=MEDIAN(N1;K1+ 1;K2+1)-MEDIAN(N1;K1+ 1;N2)
Kompaktní a elegantní, že? 😉
- Jak vlastně Excel pracuje s daty? Jak vypočítat počet kalendářních nebo pracovních dnů mezi daty?
- Jak sestavit kalendář (prázdniny, školení, směny…) v Excelu pomocí podmíněného formátování?
- Kontrola jedné nebo více podmínek pomocí funkcí IF (IF).