Nem Excel-bug, csak megértésre váró logika

2016. március 05. - Office Guru

A mai posztban kivételesen nem a megszokott módon fogok problémákra megoldásokat kínálni, hanem két érdekes és esetenként akár bugként is felfogható Excel-viselkedést mutatok be - mindkettővel együtt lehet élni és valószínűleg a többség nem is fog találkozni velük, de azért jobb tudni, hogy az Excel 2013-as verziója sem tökéletes (a 2016-os csomagban ezeket még nem teszteltem).

Az első ilyen érdekes kérdés bemutatását a következő táblázattal kezdjük, amely két város havi fix kiadásait mutatja be:

bug01.jpgMi történik, ha mondjuk Január oszlopban mindkét sort kijelölve egyesítem a cellákat a Ribbon Home füle alatt található Alignment szekció Merge & Center parancsával?

bug02.jpg

bug03.jpgEz az elvárt viselkedés, azt kaptuk, amit szerettünk volna, ha szummában összeadjuk a kiadásainkat, akkor a kalkuláció is helyes értéket mutat. Mi történik azonban, ha a Ribbonunk Home füle alatt található Format Painter segítségével ezt a Merged Cell formátumot átmásoljuk Februártól kezdve a többi hónapra is?

bug04.jpg

bug05.jpgMég mindig azt kaptuk, amit vártunk - vagy mégsem? Amit látunk, az elvileg az, amit szerettünk volna elérni, de ha például összeadjuk Április-Május-Június hónapok értékeit, láthatjuk, hogy 3000 helyett 4500 lesz az eredmény:

bug06.jpgA lenti állapotjelzőn is 4500-at mutat az automatikus kalkuláció, ahogy a SUM függvény is ezt az eredményt adja, de ha egyesével adjuk össze a cellákat, akkor azt a 3000-es értéket kapjuk, amire vártunk. Tehát a konkluzió az, hogy Format Painter segítségével semmiképpen ne másoljunk cellaegyesítést, mert az csak a formátumot viszi, a háttérben ugyanúgy ott tárolja annak a cellának az értékét, amelyről azt hisszük, hogy elveszett.

A másik mai érdekes kérdésem, amire a választ kerestem sokáig, nem más, mint a WEEKNUM függvényben megbúvó rejtély. A függvény önmagában két paraméterből áll, az első paramétereként megadott dátumról megmondja, hogy az az év hányadik hetében található, a második, nem kötelező paraméterben pedig megadhatjuk, hogy melyik nap kezdődik egy hét, ez alapesetben 1, vasárnap. Ennek tükrében nézzük meg ezt a kis táblát:

bug07.jpgMi a furcsa benne? Ha december 27-én kezdődött az 53. hét, akkor január elsején miért első hétről beszélünk, annak még nem az 53. hétnek kellene lennie? A megoldást a Microsoft hivatalos leírása vagy egyáltalán, a formula második paraméterének segítsége adja meg:

bug08.jpgMik a lehetőségeink? Az Excel 1,2,11,12,13,13,15,16,17 és 21 return_type lehetőségét adja meg nekünk, System 1 és System 2 (bár ez csak a 21-es return type-nél létezik) opcióval, ahogy láthatjuk is a legördülő kis segítségben. Ez lenne ezen opciók leírása:

bug09.jpgMaga az első két oszlop elég egyértelmű értékeket tartalmaz, viszont a harmadik, a System talán magyarázatra szorul. A Microsoft leírása szerint System 1 az a hétszámozási rendszer, amelyben az a hét az év első hete, amelyben január elseje megtalálható - ergó a példámban ezért vált 53 után egyre az Excel. A System 2 pedig az ISO8601:2000-es hétszámozási standardnek megfelelő számozás, ahol az év első csütörtökjét tartalmazó hét számít az első hétnek. Példánkat eszerint 21-es return typera átírva:

bug10.jpgEzzel a magyarázatot meg is leltük a furcsaságra, de azért a poszt végén említsük meg, hogy valójában a világon nem csak ez a kettő hétszámozási rendszer létezik, hanem van még két másik is. System 1 és System 2 opciót már tisztáztuk, de System 1-nek két változata van, az egyikben január elsején kezdődik az első hét és a második a következő hétfőn folytatódik, a másikban január elsején kezdődik szintén az első hét, de a második már vasárnap folytatódik. Ezen a három verzión kívül van a legegyszerűbb, amikor január elsején elindul az első hét és január 8-án folytatódik a második, napoktól függetlenül.

A bejegyzés trackback címe:

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

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.

rozsdamentes arcél 2016.03.06. 23:30:25

Ez a 21-es típus előrelépés a korábbi verziókhoz képest, a 2007-esben még nincs ilyen opció.

I_Isti 2016.03.07. 11:04:29

@rozsdamentes arcél: Szerintem csak egy használható megoldás van a "heti riport" problémára úgy általában, főleg ha több rendszeren, több verzión keresztül kell megoldani:
hét helyett mindenhol, ameddig lehet a hét kezdődátumát használjuk, és utána ha nagyon muszáj, akkor a végén fordítjuk át iso week-ké.
A 2007 -ben még nincs iso, csak béna hét számozás, ami véletlenül sem felel meg az iso-nak, így lehet elmélkedni azon, hogy ami tavaly még jó volt, az idén miért nem mutat normális eredményt.