Yksi tyypillisistä tehtävistä Microsoft Excel -käyttäjälle. Meillä on kaksi päivämääräaluetta, joiden tyyppi on "alku-loppu". Haasteena on määrittää, ovatko nämä vaihteluvälit päällekkäisiä, ja jos ovat, kuinka monella päivällä.
Leikkaa vai ei?
Aloitetaan ratkaisemalla kysymys, onko intervallien leikkauspiste periaatteessa? Oletetaan, että meillä on seuraavanlainen taulukko työntekijöiden työvuoroista:
On selvästi nähtävissä, että Jaroslavin ja Elenan työvuorot leikkaavat, mutta kuinka tämä lasketaan turvautumatta kalenteriaikataulun rakentamiseen ja visuaaliseen valvontaan? Toiminto auttaa meitä SUMPRODUCT (SUMPRODUCT).
Lisätään taulukkoomme toinen sarake kaavalla, joka antaa loogisen arvon TOSI, jos päivämäärät leikkaavat:
Kuinka monta päivää ylitys kestää?
Jos pohjimmiltaan ei ole helppoa ymmärtää, leikkaavatko aikavälimme vai eivät, mutta tietää tarkalleen kuinka monta päivää risteykseen osuu, tehtävästä tulee monimutkaisempi. Loogisesti on välttämätöntä "pumpata" jopa 3 eri tilannetta yhdessä kaavassa:
- intervallit eivät mene päällekkäin
- yksi intervalleista imee kokonaan toisen
- intervallit leikkaavat osittain
Ajoittain näen muiden käyttäjien toteuttavan tätä lähestymistapaa käyttämällä joukko sisäkkäisiä IF-toimintoja jne.
Itse asiassa kaikki voidaan tehdä kauniisti käyttämällä toimintoa MEDIAN (MEDIAANI) kategoriasta Tilastollinen.
Jos määrittelemme ehdollisesti ensimmäisen intervallin alun N1, ja loppu varten K1, ja toisen alku N2 ja lopettaa varten K2, niin yleisesti kaavamme voidaan kirjoittaa seuraavasti:
=MEDIAN(N1;K1+ 1;K2+1)-KEDIAANI(N1;K1+ 1;N2)
Kompakti ja tyylikäs, eikö? 😉
- Miten Excel itse asiassa toimii päivämäärien kanssa? Kuinka laskea päivämäärien välisten kalenteri- tai arkipäivien lukumäärä?
- Kuinka rakentaa kalenteriaikataulu (lomat, harjoitukset, työvuorot…) Excelissä ehdollisen muotoilun avulla?
- Yhden tai useamman ehdon tarkistaminen IF (IF) -funktioilla