Az „Excel” fórum célja, hogy keretet adjon az Excel felhasználók széles táborának tapasztalataik megosztására, és lehetőséget a segítséget kérőknek. Az alábbi összefoglaló azért készült, hogy segítse a helyes kérdésfeltevést.
– Írd le szabatosan a problémát. Úgy fogalmazz, hogy ne csak te magad, de a szakértő is megértse, mire szeretnél választ kapni.
– Írd le, hogy milyen verziójú Excellel dolgozol. (Vagy ha nem – ill. nem csak – Excel, akkor micsoda?)
– Írd le, hogy milyen úton indultál el, és hol akadtál el rajta.
– A kérdés megértése szempontjából sokat segíthet, ha feltölteszt egy képet, amin látszik, hogy mit szeretnél, vagy illusztrálja azt.
– Még jobb, ha feltöltesz egy minta munkafüzetet valahová (pl. data.hu). Feltöltés előtt távolítsd el belőle a nem publikus adatokat.
– Ha a feladat jellege olyan, célszerű az "előtte" és "utána" állapotokat bemutatni. (Miből kellene csinálni mit?)
– Ha VBA kódon kell javítani, másold be a releváns kódrészt. Rövid kód mehet hozzászólásba, hosszú kód inkább ide: http://pastebin.com/
– Ha valami nem úgy működik, ahogy kellene, add meg a rendellenes viselkedés jellemzőit, a hibaüzenetet, és a hibát okozó programsort.
Madbazsi itt vagy még? Most jött egy ötletem, hogy hogy lehet esetleg egyszerűbben megoldani a problémát, lehet a jövőhét elején rá tudok szánni egy v. két órát a kisérletezgetésre.
Nem, mert a 11527-ben pont azt írod, hogy a feltételeket össze kell szorozni. Az én - nagy sokaságon futtatott - tesztjeim viszont azt mutatják, hogy jobb ha nem szorozgatunk a SUMPRODUCT-on belül, hanem odaadjuk neki a számolás minden elemét önálló vektorként.
Persze azt se felejtsük el, hogy 1 millió rekord esetén is csak tizedmásodpercekről beszélünk ;-)
Ha "csak" százezer sorból áll a tábla, akkor viszont hol a SUM hol a SUMPRODUCT a gyorsabb. 10 kisérletből: - a SUM gyorsabb 4 esetben (legnagyobb eltérés 14%) - pontosan azonos időt fut mindkét függvény 4 esetben - a SUMPRODUCT gyorsabb két esetben (7% legnagyobb eltérés).
Szóval úgy tűnik, hogy "kis" adattábláknál a SUM a jobb megoldás, nagy tábláknál a SUMPRODUCT annyira szétbontva amennyire csak lehet.
Persze jobb lett volna nagyobb mintán kisérletezni, úgy hogy csak egy oprendszer meg az excel fut, de sajnos a világ nem tökéletes :-)
Ha a SUMPRODUCT-ot szétbontottam úgy, hogy minden egyes elem önálló tömb a számolásban (vagyis nincs szorzásjel a SUMPRODUCT-on belül), akkor egy kicsit még gyorsabb lett.
A függvény amit futtattam: =SUMPRODUCT(--(YEAR(A2:A1000002)=2010), --(MONTH(A2:A1000002)>=7), --(MONTH(A2:A1000002)<=9), --(C2:C1000002="IGEN"),(B2:B1000002))
Csináltam egy gyors kisérletet, ha valakit érdekelnek az ilyen dolgok beteszem ide.
Szóval létrehoztam két táblázatot, mindegyikben 1 millió sorral. Az egyik táblázatot SUM függvénnyel, a másikat SUMPRODUCT függvénnyel összesítettem számos feltétel mentén. A függvények:
Ezekután lemértem, hogy melyik függvény mennyi idő alatt boldogul a számolással. Itt az eredmény: Jól látszik, hogy a SUMPRODUCT gyorsabb.
Ezek után átalakítottam a SUMPRODUCT függvényt a következőre: =SUMPRODUCT((YEAR(A2:A1000002)=2010)* (MONTH(A2:A1000002)>=7)* (MONTH(A2:A1000002)<=9)* (C2:C1000002="IGEN"),(B2:B1000002)) A változás annyi, hogy a számokat számoló részt (B2:B1000002) kivettem egy vessző mögé (vagyis egy külön tömböt képeztettem vele.
Ezt összehasonlítottam az első változat SUMPRODUCT-jával (SUMPRIODUCT1 a grafikonon) és ezt kaptam:
A SUMPRODUCT gyorsabb, ha kettébontottam a számolást, különböző vektorokra.
Mondjuk, nehéz leírni azt a pillanatot amikor néhány óra munka után egy képlet végre elkezdi azt csinálni, amit csinálnia kéne!
Még egy megjegyzés: Ha a negyedik negyedévet nézed akkor felesleges betennie a: HONAP(datum)<=12 részt, mert ez mindenre igazat ad. Ilyenkor elég csak azt ellenőrzizni, hogy a HONAP(datum)>=10. Nyilvánvaló okokból hasonló logika vonatkozik az első negyedévre is.
Függvények más függvényből való "fejlesztésekor" gyakran előfordul, hogy felesleges feltételek bentmaradnak, mert csak a régi függvényt tartalmát alakítjuk át, nem pedig a működését gondoljuk újra.
Most fogok elővenni valami nagy és nehéz tárgyat, mellyel erőbehatásokat lehet eszközölni a számítógép érzékeny részeire...grrrr 4 órakor kidob az ágy, mert az agyam nem hagy nyugodni és folytatom a táblázatot :-)
Amit ajánlottál: =SUMPRODUCT(--(YEAR(B13:B16)=2000* MONTH(B13:B16)>=7)*(MONTH(B13:B16)<=9*(F13:F16=M3)*(H13:H16=H5)))
remekül működik...de, csak ezzel a negyedévvel. Ha a 7 helyett 10-et, a 9 helyett 12-t írok akkor hibásan számol. Konkrétan megszámol mindegyik sort, a hónapoktól függetlenül.
Már mér ne kéne értened? Úgy látom jelentős időt rászántál arra, hogy a tömbfüggvényekkel foglalkozz és ez egy fontos része.
Szóval, leegyszerűsítve a problémát, mondhatjuk azt, hogy vannak esetek amikor az excel - makacs módon - egy logikai teszt eredményét IGAZ/HAMIS-nak veszi, nem pedig 1/0-nak.
Mivel a tömbfüggvények egyik fontos hasznosítási területe ez, meg kell oldani, hogy pld egy IGAZ értéket egy számmal szorozzunk. Erre szolgál a dupla negatív.
Más szóval: vannak esetek amikor IGAZ*2000 hibát ad. Ekkor lehet a következőt csinálni: --(IGAZ)*2000, és ez már nem hibát jelez, hanem 2000-t ad eredményül.
Mivel lusta vagyok megjegyezni, hogy milyen függvény-környezetben jön elő a hiba, én inkább - szinte - mindíg odateszem a dupla negatívot, abból baj nem lehet.
Megszokás... A dupla negatív átváltja a TRUE/FALSE értékeket számmá. Én mindíg be szoktam rakni a képletekbe, mert lusta vagyok megjegyezni, hogy mikor kell meg mikor nem :-)
A tartomány elnevezéseket már használtam máskor is, itt most nem jutott eszembe. A képlet elején a 2 kötőjel (--) miért van? Én nem tettem bele és működik.
