Üks Microsoft Exceli kasutaja tüüpilisi ülesandeid. Meil on kaks kuupäevavahemikku tüüpi "algus-lõpp". Väljakutse on kindlaks teha, kas need vahemikud kattuvad ja kui jah, siis mitme päeva võrra.
Kas ristuda või mitte?
Alustuseks lahendame küsimuse, kas intervallide ristumiskoht on põhimõtteliselt olemas? Oletame, et meil on töötajate jaoks selline töövahetuste tabel:
On selgelt näha, et Jaroslavi ja Jelena töövahetused ristuvad, kuid kuidas seda arvutada ilma kalendrigraafiku ja visuaalse kontrollita? Funktsioon aitab meid SUMPRODUCT (TOODE SUMMA).
Sisestame oma tabelisse teise veeru valemiga, mis annab tõeväärtuse TRUE, kui kuupäevad ristuvad:
Mitu päeva on ülesõit?
Kui põhimõtteliselt pole lihtne aru saada, kas meie intervallid lõikuvad või mitte, vaid täpselt teada, mitu päeva täpselt ristumiskohta langeb, muutub ülesanne keerulisemaks. Loogiliselt võttes on vaja ühes valemis "pumbata" kuni 3 erinevat olukorda:
- intervallid ei kattu
- üks intervallidest neelab teise täielikult
- intervallid lõikuvad osaliselt
Aeg-ajalt näen seda lähenemist rakendamas teised kasutajad, kes kasutavad hunnikut pesastatud IF-funktsioone jne.
Tegelikult saab funktsiooni abil kõik ilusti tehtud MEDIAANI (MEDIAAN) kategooriast Statistiline.
Kui nimetame tinglikult esimese intervalli alguseks N1, ja lõpp jaoks K1, ja teise algus N2 ja lõpp jaoks K2, siis üldiselt saab meie valemi kirjutada järgmiselt:
=MEDIAAN(N1;K1+ 1;K2+1)-MEDIAAN(N1;K1+ 1;N2)
Kompaktne ja elegantne, kas pole? 😉
- Kuidas Excel tegelikult kuupäevadega töötab? Kuidas arvutada kalendri- või tööpäevade arvu kuupäevade vahel?
- Kuidas koostada tingimusvormingu abil Excelis kalendrigraafikut (puhkused, koolitused, vahetused…)?
- Ühe või mitme tingimuse kontrollimine funktsioonide IF (IF) abil