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.
Valahol a trendlines method környékén érdemes keresni a helpben. Egyébként bő két éve készítettem programból grafikonokat. Azokra ráírattam a programmal az egyenletet is. Igaz, csak lineáris trenddel foglalkoztam. Nagyon sok grafikon volt egy-egy munkalapon, ezért kellett a program. A lényeg, hogy teljesen automatizálni tudtam.
Csak annyira összetett volt a feladat, hogy hosszabb idő kellene az előbányászásukhoz.
A diagramra ráhúzott függvény képletét ki tudod íratni a diagramra. Az alapadatokat ismered. A függvénnyel pedig ki tudod hozzá számolni az y adatokat. Ennél jobbat sajnos nem tudok. Tudom, persze sok adatsorozatnál ez eléggé hosszadalmas.
Illetve a TREND függvény idézett helpjét érdemes egy kicsit mélyebben megnézni, lehet, hogy segít valamit.
A 23087-ben felvetett eljárásra nincs valami ötleted? Azaz erre:
Abban lehet itt a segítségedre egy nálam jóbban képzett exceles, hogy elmondja, miként lehet a diagramra ráíratható függvény paramétereit lekérdezni. Ez speciel engen is érdekelne.
Van 2003-as és 2007-es egyaránt. De annyira nem szeretem a 2007-est, hogy lényegében csak a 2003-ast használom. Bár most éppen az itt emlegetett programom a laptomon fut, amin a 2007-es van telepítve, hogy az alatt a 3 óra alatt, amíg dolgozik, nyugodtan tudjak dolgozni az asztali gépemen,
Egyébként sajnos legkésőbb jövő nyáron át kell állnom, tehát biztos fogom tudni használni az érdekességeket. Kipróbálni meg most is tudom.
Most hogy újra nézem, biztos, hogy a poliniáis transzformációm egy marhaság:((((. De hogy mi lenne a jó, azt nem tudom. Így ha valaki nem súgja meg, marad, hogy az excellel illeszted a polinomiális függvényt, majd az így meghatároztt függvénnyel jelzel előre. Abban lehet itt a segítségedre egy nálam jóbban képzett exceles, hogy elmondja, miként lehet a diagramra ráíratható függvény paramétereit lekérdezni. Ez speciel engen is érdekelne.
Igen, egy kicsit félreérthetően fogalmaztam. Akkor most pontosabban. Szó sincs a harmadfokú függvény linearizálásáról, majd az eredmény vissza transzformálásáról. Ha ugyanis már megvan a harmadfokú függvény, akkor nem a forecast-t kell előre jelezni, hanem magával a harmadfoku függvénnyel.
Amiról én írtam, hogy adatpárjaid vannak és azokból akarsz előre jelezni az excel segítségével. Nos, ebben az esetben a függvények meghatározásával nem kell foglalkoznod. Azt rábízod az excelre. Te csak az adataidat teszed a lineárissá, persze csak akkor ha nem vagy megelégedve a jelenlegi linearításukkal. Írtad korábban, hogy nem akarsz veszkődni a grafikus függvénymeghatározással. Az adatok grafikus Pont(XY) diagramon való megtekintése azonban ajánlatos, mert ábrából a legkönnyebb eldönteni, hogy a függvényedhez milyen függvényt érdemes illeszteni. Nos ha ezt eldöntötted, akár az ábrából, akár hasra ütve, akkor kell az Y adataidat ennek megfelelően transzformálni. Teszem azt, ha másodfokú osszefüggést posztulálsz, akkor veszed mindegyik Y-nak a négyzetgyökét, ha harmadfokút akkor a gyök3-t, ha logarimusost, akkor a logaritmusát stb. Majd az XY' adatpárjaidra elvégzed alineáris a regressziót. Ha a korrelácó javult az eredeti adataidból számítotthoz képest, akkor érdemes volt transzformálnod, ha nem javult akkor vagy maradsz az eredeti adataidnál, vagy új transzformációt választasz. Ha meg vagy elégedve a javulással, akkor csinálod meg a lineáris előrejelzést,majd a kapott forecast visszatranszformálását az eredeti skálára, az előző transzformáció inverzével. Tehát ha előzőleg gyököt vontál, akkor most négyzetre emelsz stb.
A polinomiális előrejelzéshez úgy gondolom, hogy az Y=x+gyök(x)+gyök3(x)+gyök4(x)+..... transzformációt kellene elvégezni. De ezt azért csekkold, mert nem vagyok matematikus, lehet hogy tévedek. Azt azonban tudnod kell, hogy a polinomiális összefüggések csak az adott adathalmazon érvényesek. Ugyanis a polinomiális függvény első sorban arra használatos, hogy az adott adathalmazod összes kis kilengését baromi pontosan írja le. Ezért egy másik adathalmazon nyilvánvalóan hibás eredményeket fog előre jelezni. Ezért én a polinomiális függvényt csak az adathalmazban rejlő tendenciák felismerésére használtam, előrejelzésre sosem
Szia, ez épp jókor jött, amikor azt kezdtem tesztelgetni, hogy hogyan tudom a formulát alkalmazni, ha másik fájlból olvasom az egyik összegzendő adatsort. És sikerült a workbook és a worksheet nevét is beleillesztenem a képletbe.
A TREND függvényt polinomgörbe illesztésére is használhatja: ekkor a regresszió számítása ugyanazon változó különböző hatványainál történik. Tegyük fel például, hogy az A oszlop az y, míg a B oszlop az x értékeket tartalmazza. A C oszlopba beviheti az x^2, a D oszlopba az x^3 értékeket és így tovább. A regressziós görbe ezután a B-D, illetve az A oszlop értékei között számítható.
A feladat lényegét a #22993-ban hivatkozott http://data.hu/get/7127187/sumif_teszt.xls tesztfájl tartalmazza. Akkor éppen a Sumif tesztelésére készítettem, és az adatszerkezet bemutatására. Ezért a benne lévő program már nem aktuális, de az adatszerkezet érvényes.
Az első két oszlopban árucikkek kódja és neve szerepel, a többiben az egyes időszakokban történt értékesítések 3-3 adata. Csak éppen az éles adatfájlokban (amiből 30-nál több van, ezek az egyes üzletek adatai) átlag 20.000 sor szerepel és kereken 100 oszlop.
Ráadásul az árucikkek listája nem teljesen fedi egymást az egyes fájlokban, ugyanakkor egy fájlon belül a nevek ismétlődnek más kódszámmal.
Az egyes fájlok adatainak vízszintes és függőleges összegzésén túl kell készítenem egy olyan összegfájlt, amelyben a fájlokban előforduló összes – adattal rendelkező - árucikk (az üres tételek kihagyása miatt összesen kb. 10.000) egyszer szerepel, és annak adatait a fájlokból összesítve tartalmazza. Aztán kell egy olyan összesítő fájl, ami az egyes fájlokban (üzletekben) található oszlopösszegeket írja az egyes sorokba.
Tulajdonképpen egyszer már működött a program, de fájlonként 7-8 percig futott, összesen több mint 3 órán át. És mivel még kell egy kicsit finomítani (pl. az azonos nevű, eltérő kódú tételek összevonásával), ezért gondoltam, hogy akkor már meggyorsítom, nem mellesleg a formula- és a munkalap képletek használatával is ismerkedem.
Azért ha a feladat lényegét leírnád, lehet, többet tudnánk segíteni neked.
Hiszen az Excelben számos olyan eszköz van, amivel a táblázatokat könnyen lehet kezelni.
Pl. kimutatások készítése, amelyek utána pillanatok alatt variálhatók.
Nem biztos, hogy neked kell kitalálnod azokat a programsorokat, amiket már eleve beépítettek valamilyen excel funkcióba.
Én mindig arra törekszem, hogy először dolgozzon az Excel és csak utána jövök én. Teljesítmény szempontjából is általában ez a szerencsésebb.
Más: ha egy sort összegzőnek szeretnél és beírod a szum képletet, utána ebben a sorban a képletet kell/lehet módosítani a hozzáadandó tartomány címének megfelelően.
Továbbá: Hasznos, ha az összegző sor az adatok előtt van, mert ilyenkor az összegzőképletet csak egyszer kell beírnod, ami akár az oszlop végére is mutathat. Ha újabb adatot írsz a következő sorokba, az összeg automatikusan képződik.
Kösz, a helyzet az, hogy próbáltam a szum-al is a képletet, de azzal sem jött össze.
Az igazság az, hogy mintát mutatni nem érdemes. Az eredeti feladat egy sokezer soros, 98 oszlopos táblázat, sőt ebből is sok van. És az adatok gyűjtögetése ciklusban nagyon lassú, ezért szeretnék képleteket használni.
És amíg a megfelelő képleteket nem találom meg, addig kisméretű mintatáblákon próbálgatom, amelyekbe néhány sort, és néhány oszopot teszek csak.
És nem ragaszkodnék a formula képlethez, de az általad írt megoldás is megfelel nekem. Tökéletesen működik.
Egyébként meg éppen most elakadtam a következőnél:
With ws.Range(Cells(1, 1), Cells(1, utolsóoszl)) összws.Range(Cells(1, 1), Cells(1, utolsóoszl)).Value = .Value ' fejléc másolás End With
method range of object _worksheet failed hibaüzenetet kapok
Valószínű, hogy 1-2 óra alatt megtalálom a hibát, de hátha te kapásból átlátod.
Mindig új helyre töltöd be az új adatokat és azt szeretnéd látni a diagramon? Ha ez fontos, akkor a diagram forrását kell megadnod a makróban tartományként.
Van egy chartwizard tulajdonsága a chartnak, aminél a source paramétert kell megadni range("A1:B15") formában.
Ha elnevezted az adattartományt, akkor a név.referestorange kell a source-hoz (arra figyelj, hogy a név laphoz, vagy munkafüzethez van kötve, mert aszerint kell keresned).
Ha mindig ugyanoda töltöd be az új adatokat, akkor viszont sokkal egyszerűbb a helyzet:
Az adatokat betöltöd, utána kimutatásdiagram, létrehozod a diagramot.
Utána amikor frissíted az adatokat, frissül a diagram is.
Igaz, itt meg van kicsit kötve a kezed a diagram tipusait és formáját tekintve, de azért ez is elég kényelmes és gyorsan lehet pl. változtatni, hogy mit akarsz látni rajta.
A VBA-ban megadott formula képletekre ugyanaz a szabály vonatkozik, mint a munkalap képletekre - hiszen ugyanazok - , így a saját magával való összeadás formula megadással nem megy, ez csak a VBA-ban működik.
Áthidaló megoldásként egy másik sorban alkalmazhatod a képletet, majd értékké változtatva átmásolhatod az összegsorba, valahogy így:
with range(cells(ujsor,1),cells(ujsor,oszlopszam))
Még mindig a tartományi képleteken vagyok elakadva. A következő részt szeretném kiváltani egy egysoros paranccsal, tekintve, hogy nagyon sok sor, nagyon sok oszlopát szeretném gyűjteni:
For j = 1 To oszlopszám Cells(összegsor, j) = Cells(összegsor, j) + Cells(aktsor, j) Next i
Ez így ciklusban simán működik, de amint képletet próbálok hozzá kreálni, vagy körkörös hivatkozást jelez, vagy csak simán lenullázza az összegsorban lévő adataimat.
Ami nem működik nálam relatív címzésként makrónál, az az Excelben amúgy simán használható SHIFT+CTRL+lefelé nyíl, vagy oldalra nyíl. Ugyan jól jelöli ki az Excel látszólag, de látszik a VBA kódban, hogy fix cellanevekre hiányzik bent a pivotnál.
Sziasztok, hogyan tudom beállítani Excel 2007 angol nyelvű verzióban azt, hogy a rögzített makróm dinamikus tartományt tudjon kezelni?
Konkrétan: pivot táblát szeretnék úgy frissíteni minden egyes újabb forrásadatbetöltésnél, hogy az aktuális adattömböt vegye figyelembe.
Ami nem működik nálam relatív címzésként makrónál, az az Excelben amúgy simán használható SHIFT+CTRL+lefelé nyíl, vagy oldalra nyíl. Ugyan jól jelöli ki az Excel látszólag, de látszik a VBA kódban, hogy fix cellanevekre hiányzik bent a pivotnál.
Így legközelebb hiába fut le a lefelé és jobbra való kijelölés, a korábban fixen benne lévő cellanevekkel dolgozik a pivot a tábla frissítésénél.
Próbálkoztam azzal is, hogy túl járok a pivot eszén, elnevezést használva az adattartományra, így csak be kell frissítenem a pivotot, amikor újabb "target" adatforrásom van.
A gond csak az, hogy az elnevezést se csinálja meg jól a fent említett lefelé és oldalra való kijelöléssel. Pont ugyanez a gond a SHIFT+CTRL+End kijelöléssel is: mivel az Excel emlékszik rá, hogy korábban abban a cellában volt adattartalom, mert pl. korábban egy hosszabb adattartományom volt, akkor hiába van az újabb adattartományban kevesebb adatom, nem az utolsó sorig jelöli ki, hanem az üres cellákat is.
Ha esetleg valamilyen offsetes automatikus tartománykijelölés létezik a definiált "Target"-re, az is megoldaná a gondomat szerintem.
Az adattartomány az oszlopnevekkel az A7-es cellában kezdődik, nem A1-től. Az adatok A-tól P oszlopig terjednek, kb. 20 ezer soron. A munkalap neve Sales.
Megoldás lehet hogy linearizálod a magasabbrendű függvényed adataid és azon határozod meg a forecastot. Majd az eredményt visszaalakítod az eredeti függvény szerintire.
Van az előrejelzés (forecast) függvény, ami tök jó arra, hogyha a lineáris trend következő y-ját akarjuk kiszámolni. De mi van akkor, ha a trend nem lineáris, hanem másodfokú, harmadfokú stb? Ott milyen képletet kéne használni? (Ha grafikusan megrajzolom a trendvonalat, akkor kiírja az egyenletet, de ez többszáz esetben eléggé macerás kézzel bemásolni.)
Aha! Így tényleg készen vannak a BC oszlopok. Annyira nem mélyedtem el a feladatleírásban, hogy erre magamtól rájöjjek. De akárhogyis van, a lényeg, hogy sikeresen megoldottad :)))).
