A dátumformátum is lehet a barátunk, ha a megfelelő függvényeket választjuk

Egy fejtörő sokféle megoldással, íme egy igen egyszerű logika

2015. szeptember 14. - Office Guru

Nem tudom már pontosan mikor, de valószínűleg 1-2 éve volt hasonló feladvány egy Exceles bajnokságon, amellyel ismét csak azt kívánom bemutatni, hogy a megszokott és ismert függvények kreatív felhasználásával egészen hihetetlen kérdésekre lehet egészen hihetetlen megoldásokat adni - azt a bizonyos feladatot emlékeim szerint akkor az összes résztvevő 10 percnél kevesebb idő alatt abszolválta, de ahogy általában lenni szokott, itt már nem a függvények összeállítása, egyszerűen a logika megtalálása a leghosszabb idő.

Adott a lenti példa és hozzá kapcsolódóan a feladat: adjuk meg B5:B7 cellákba azt, hogy az azonos sorokban szereplő dátumintervallumok és a B3:B4 cellákban megadott két dátum intervalluma között pontosan hány nap az átfedés!

hetvenhat.jpgPéldául az elsőnél mondjuk meg, hogy január 15. és május 30.-a közötti intervallumból hány nap esik január elseje és április elseje közé. Finom kis fejtörő, főleg ha tudjuk, hogy alig néhány percünk van rá - de ha megvan a logika, akkor villámgyorsan összedobhatjuk az eredményt. És ahogy Excelben általában mindig, most sem egyetlen megoldás létezik, a kérdésre a választ ugyanis kis eltökéltséggel még egy IF-fel is ki tudjuk csikarni a programból, de most inkább nyúljunk két jóval egyszerűbb, ámde egy ilyen megoldásnál fel sem merülő formulához, a MAX-hoz és a MIN-hez.

A MAX nevéből adódóan megadja, hogy egy adott tömbben melyik a legnagyobb érték, a MIN pedig értelemszerűen a legkisebbet fogja visszaadni, ami nekünk itt azért fog jól jönni, mert meg fogjuk tudni, hogy a két összehasonlítandó intervallum kezdő illetve záró időpontjai közül melyik a nagyobb és a kisebb, azaz végül lesz majd két dátumunk, ahonnan már könnyen eljuthatunk a megoldáshoz.

Elsőként tehát nézzük meg, hogy első összehasonlításunknál melyik záródátum a kisebb a két pár közül, azaz:

=MIN(B$4,$F5)

hetvenhet.jpgInnen pedig már tudni fogjuk, hogy április elseje a kisebbik záródátum. Ezután ki kell derítenünk, hogy melyik a nagyobb nyitódátum, ehhez jön majd segítségként a MAX, azaz:

=MAX(B$3,$E5)

hetvennyolc.jpgInnen pedig tudni fogjuk, hogy a nagyobb kezdődátum a január 15.-e, azaz ha ezt a két függvényt összedolgozzuk, akkor meg is van a közös intervallum, azaz a megoldás is. Na igen ám, de hogyan tegyük ezt?

Ha szimplán ezt véssük be a cellánkba:

=MIN(B$4,$F5)-MAX(B$3,$E5)

akkor kapunk már egy számot, de ez még nem helyes, hiszen nem lesz benne maga a kiinduló- illetve záródátum, ezért egyet mindenképpen hozzá kell majd adnunk.

=MIN(B$4,$F5)-MAX(B$3,$E5)+1

hetvenkilenc.jpgÉs ezzel kész is vagyunk, ott van az elvárt eredményünk. De módosítsunk egy kicsit a feladványon és tegyük fel, hogy olyan intervallumunk van az E5:F5-ben, amelynek nincs átfedése a kérdéses intervallumunkkal, lásd a példán:

nyolcvan.jpgEkkor az eredmény negatív szám lesz, ami nem értelmezhető, hiszen ilyenkor azt szeretnénk látni, hogy 0, azaz nincs átfedés - ismét egy feladat, amelyet másképp is meg tudnánk oldani, de térjünk csak vissza a MAX függvényre megint! A MAX ugyebár számokat, egy tömb értékeit hasonlítja össze és adja vissza nekünk a legnagyobbat, ergó ha a fenti eredményünket beépítjük egy MAX függvénybe, mint paraméter, majd felkínáljuk neki másik paraméterként a 0-t, akkor értelemszerűen a nulla nagyobb szám, mint a negatív szám, azaz mindig 0-t kapunk, ha nincs átfedés:

=MAX(0,MIN(B$4,$F5)-MAX(B$3,$E5)+1)

Sőt, ha a 0-t be sem írjuk, az Excel automatikusan nullához fogja hasonlítani a különbségünket, azaz ez lehet a végső megoldás:

=MAX(,MIN(B$4,$F5)-MAX(B$3,$E5)+1)

nyolcvanegy.jpgDe ez tipikusan az a probléma, amire szerintem bőven több, mint egy megoldás létezhet, azaz bátran írjatok, ha van egy jóval egyszerűbb, vagányabb ötletetek!

A bejegyzés trackback címe:

https://officeguru.blog.hu/api/trackback/id/tr607788546

Kommentek:

A hozzászólások a vonatkozó jogszabályok  értelmében felhasználói tartalomnak minősülnek, értük a szolgáltatás technikai  üzemeltetője semmilyen felelősséget nem vállal, azokat nem ellenőrzi. Kifogás esetén forduljon a blog szerkesztőjéhez. Részletek a  Felhasználási feltételekben és az adatvédelmi tájékoztatóban.

Nincsenek hozzászólások.