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.
Az Excel korábbi változataiban ezeket tömbképletként kellett bevinni (Ctrl + Shift + Enter). Az új verziókban így az O365-ben is már saját maga felismeri, hogy tömbképletként kell megoldani, nem kell a külön billentyűkombó.
Már csak az volt a bajom, hogy ha beírtam a példaképletet, #ÉRTÉK! hibaüzenetet kaptam. Aztán rájöttem, hogy a magyar excel más elválasztó karaktereket használ mint az angol (tizedes pont helyett vesszőt, elválasztó vessző helyett pontos vesszőt stb.). Nagy sokára találtam egy példa táblázatot, ahol kiderült, hogy a hatványok együtthatóit (a kapcsos zárójelben levő számokat) a magyarban hanyatt törtvonallal "" (alt-Q) kell beírni. És szuper jól működik a függvény, nem kell tömb képletet használni, ha simán egy cellába beírja az ember a lin.ill függvényt és a képletben megadja hányadfokú trendvonalra kíváncsi, az együtthatókat automatikusan egymás melletti cellákba írja.
Ez szerintem szuper. Még nem találkoztam korábban a LIN.ILL függvénnyel, most ránéztem a helpre. Ott is van róla szó a legvégén, de ez a magyarázat kifejezetten tetszik. Dícsérjük meg a MS-t, ritkán adódik rá lehetőség. Ez sokkal egyszerűbb, mint makrókkal operálni.
Köszi, kipróbálom! Időközben írtam a Microsoft supportnak is, és csodák csodája 24 órán belül válaszoltak. Még nem próbáltam ki, de valami olyat írtak, hogy a lin.ill függvény csak nevében lináris (elsőfokú), valójában tetszőleges fokszámra paraméterezhető. Ez elég elegéns megoldás lenne. Részlet a levelükből:
1. Készítse elő az adatokat:
Tegyük fel, hogy az "x" értékek az "A" oszlopban, az "y" értékek a "B oszlopban" vannak, és az adatok a 2. sortól a 100. sorig terjednek (pl. "A2:A100" és "B2:B100").
2. Számítsa ki az állandókat a LIN.ILL függvénnyel:
Válasszon ki egy üres területet (pl. "D1:G1"), és írja be a következő képletet:
=LIN.ILL(B2:B100;A2:A100^{1;2;3};IGAZ;IGAZ)
Itt az 'A2:A100^{1,2,3}' azt jelenti, hogy illeszkedjen egy köbös polinomhoz ( 'y = c1 + c2x + c3x² + c4x³'). - Nyomja meg a 'Ctrl + Shift + Enter' billentyűkombinációt a tömbképlet megadásához. Az Excel egy tömböt ad vissza, amely a "c1, c2, c3, c4" állandókat tartalmazza.
3. Dinamikus állandók használata képletekben:
Feltételezve, hogy a "LIN.ill" által visszaadott állandók a "D1:G1" tartományban vannak tárolva, ezeket az állandókat más cellákban is használhatja. Például az "x=2" előrejelzett értékének kiszámításához:
= $D$1 + $E$1*2 + $F$1*2^2 + $G$1*2^3
Ily módon az adatok frissítésekor a "LIN.ILL" automatikusan újraszámítja az állandókat, és frissíti a képleteket.
Sub TrendlineEquation() Dim objTrendline As Trendline Dim strEquation As String With ActiveSheet.ChartObjects(1).Chart Set objTrendline = .SeriesCollection(1).Trendlines(1) With objTrendline .DisplayRSquared = False .DisplayEquation = True strEquation = .DataLabel.Text Range("A5") = strEquation End With End With End Sub
Ez a makró az A5 cellába írja a trend egyenletet (a cellát tudod változtatni természetesen), ami szöveg formátumban tartalmazza az egyenletet.
Ebből szöveg darabolással ki tudod hámozni a számodra megfelelő megoldást, ha nem menne, légy szíves írj ide egy valós egyenlet szöveget, mert polinom függő a szétdarabolás.
Arra is lehet makrót írni és akkor egy futással lehet az egyenletnek megfelelő együtthatókat megadni.
A munkafüzetet természetesen makróbarátként kell menteni.
Lehet, hogy rosszul kérdeztem (vagy a válaszodat értem félre). Nem azt szeretném, hogy egy meglévő trendvonalat kibővítsek az adathalmazt megelőzően vagy utána. A problémám az, hogy az adathalmazomban mérési eredmények (az X tengelyen ultrahang sebesség, az Y tengelyen a hozzá tartozó szilárdság) van. Idővel ezek a mérési eredmények egy adott értéktartományon belül "sűrűsödnek", az x értékek egy tartományon belül változnak, tehát a táblázatban nincsenek érték szerint sorbarendezve, lehetnek ismétlődések is. Ahogy egyre több pontpár kerül a táblázatba a trendvonal (ami harmadfokú polinom) ezzel együtt változik.
Azt szeretném, ha egy szerkezetnél csak ultrahang sebességet mérek, a legutóbbi (aktuális) trendvonal függvényével tudjak szilárdságot becsülni. Ha újabb UH sebesség/szilárdság pontpárral bővül a táblázat (és ezzel módosul a trendvonal) a korábbinál pontosabb becslést lehetne kapni.
Sajnos nem látom, hogy közvetlenül lehetne paramétert kiolvasni a trend egyenlethez. Viszont a Trendline objektumhoz van Backward2 és Forward2 tulajdonság, amivel léptetni tudod a trendet az általad meghatározott egységgel.
Az Environ("COMPUTERNAME") megadja az adott gép nevét. Ezt azonban csak a fájl megnyitása közben (a makró futtatásakor) tudod lekérdezni és összehasonlítani az általad engedélyezettel.
Célszerűen a Workbook_Open eseménykezelőben lenne értelme elhelyezni. A fájlt rejtett ablakban nyitod meg és ha nem jogosult gépről nyitják meg, akkor üzenettel bezárod, ha pedig jogosult, akkor láthatóvá teszed az ablakot.
A jogosult gépek nevét persze tárolni kell valahol egy munkalapon vagy "fixen" beírni a makróba vagy egy külső fájlba, amit jogosultság ellenőrzésnél megnyitsz.
Az a kérdésem, hogyan/honnét lehet a trendvonal paramétereit kiolvasni? Ha van egy táblázatom, az abból készített grafikonhoz tudok olyan trendvonalat rendelni ami viszonylag jól illeszkedik. A trendvonal egyenletét is meg tudom jeleníteni amivel szeretnék számítást végezni (egy "x" változóhoz a trendvonal egyenletével kiszámolni "y"-t). Jelenleg csak úgy tudom megoldani, ha kézzel beírom a trendvonal egyenletét egy függvénybe. Ez egészen addig jó, amíg az adatokat tartalmazó táblázatban nem történik változás pl. új adatsor kerül bele. Ilyenkor a trendvonal változhat, de a kézzel bepötyögött képlet nyilván nem. Tudtok rá valami megoldást? Általános esetben kérdezem, ismerem a trend függvényt de az csak a lineáris regresszióhoz jó, általános (többfokú polinom) trendvonalhoz nem.
Azt meg tudtam csinálni, hogy ne engedje az excel bezárás előtt menteni a változtatásokat ás ne is lehessen lementeni "mentés másként"-el.
Így csináltam:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ThisWorkbook.Saved = True
If SaveAsUI.Saved = True Then
Cancel = True
MsgBox ("Nem lehet másként menteni")
ElseIf ThisWorkbook.Saved = True Then
a = InputBox("Jelszó:", "Tudnod kell a jelszót, h engedje a mentést")
If a = "123" Then
MsgBox ("Mentve")
Else
Cancel = True
MsgBox ("Nem lehet menteni")
End If
End If
End Sub
Ezzel egyidejűleg, azt hogyan oldom meg, hogy csak én és mondjuk a megadott felhasználók tudják megnyitni? Environ-ra gondolok, hogy gépneveket adok meg, akiknek így adok jogosultságot a megnyitásra. Fontos, h gépnév legyen, ne felhasználó név.
Ezt még, hogyan és hová teszem hozzá?
Illetve még annyi, hogy gépneven azt a vezérlőpulton belül kikeresve - "teljes eszköznév" mellett szereplő nevet értjük, vagy az excelnek van egy "belső" gépneve, amit saját magának használ?
A beviteli munkalapon az A oszlopban vannak a partnerek, B oszlopban a hozzájuk tartozó kiadások, a másik munkalap neve Munka2 és az A1:B200 tartományban vannak a partnerek és a keretek, akkor a képlet a 2. sor első üres cellájába:
Ha táblázattá alakítod az adatbeviteli részt, akkor a függvény minden új sornál automatikusan megjelenik. (Ehhez a beállítások - speciális - adattartomány végén a formázás és képletek folytatása.
Kedves Fórumtársak! Az alábbiakban kérnék tanácsot.
Az egyik munkalapon van egy tábla, melyben a partnerek és kifizetési adataik kerülnek folyamatosan rögzítésre. A másik munkalapon van a partnerlista és az adott partnerhez tartozó keretösszeg.
A cél az lenne, hogy a kifizetési adatok rögzítésekor azonnal lehessen látni, ha az adott partner kerete elfogyott.
Ezt most úgy oldottam meg, hogy készítettem egy a partnereket csoportosító és a kifizetési adataikat összegző kimutatást, melyet minden egyes tétel rögzítése után frissítek.
1.Az első kérdésem, van erre valamilyen függvényes megoldás? (Groupby függvényt nem találtam.) 2. Ha nincs, akkor automatizálható a frissítés, úgy makróval, hogy ha az A2:A1000 tartomány nem üres celláinak száma változik fusson le a "kimutatás frissítése" (Alt+F5) parancs? (A kimutatás ua. oldalon van, ahol a tételek rögzítése történik.)
Logikus amit írsz és ez nyilván a sima HA egymásba ágyazásra is igaz. Viszont próbáld ki lsz. hogy az utolsó értéknél is nagyobbat írsz az L6 cellába. Ekkor hibaüzenet lesz a HAELSŐIGAZ eredménye. Ezért van az első felételnél a ">6" nálam.
Üdv.
Ps. Örültem, hogy az általam mutatott verziót kitaláltam. :))
Érdekes ez a HAELSŐIGAZ függvény. Kicsit próbálgattam a sajám mintaadataimmal, hogy nem lehet-e leegyszerűsíteni a VAGY(B2=A2;SZUM(B2:C2)>A2...stb. formula helyett VAGY(SZUM(B2:C2)>=A2 alkalmazásával. Aztán egyszer csak meglepetés ért. A függvény az egyenlőségjel nélkül is megtalálta a helyes eredményt. Azt állítottam be, hogy a kumulált érték az 5. hétnél pontosan legyen egyenlő az L6 értékével. Ezt írtam be: =HAELSŐIGAZ(P6>L6;0;SZUM(P6:Q6)>L6;1;SZUM(P6:R6)>L6;2;SZUM(P6:S6)>L6;3;SZUM(P6:T6)>L6;4;SZUM(P6:U6)>L6;5;SZUM(P6:V6)>L6;6;SZUM(P6:W6)>L6;7), mire kiadta az 5. hetet.
Az eredmény persze logikus, hiszen a 6. hét az első, ami nagyobb, mint az L6 értéke, így az 5. hét a jó eredmény, akár kisebb, akár egyenlő L6 értékével.
Csináltam egy új munkalapot a kumulatív vevői igényeknek (mert végülis ez egyönmagában is hasznos infó), és azon a lapon már probléma nélkül működik a dolog:)
Nem feltétlenül kell hozzá segédsor, egymásba ágyazott ha függvényekkel is megoldható, ha rövidebb időszakra tekintesz előre. A példában 6 napra van a képlet összeállítva, ha ennél messzebb szeretnél látni, akkor folytatni kell a HA(VAGY képlettel a ">6" helyén és az utolsó napra kerül a hamis ágra ">x"
"Ha jól értem egy kumulatív vevői igény segédsort készítesz." Pontosan. És ahogy írtam, bármelyik sorba kumulálhatod az igényeket. És akkor a P7:W7 helyett a megfelelő sor számát írod.
Ha végképp nincs a közelben üres sor, akkor írhatod a nullát akár az Akár AA oszlopba, a kumulált adatokat meg az AB-től, és akkor a képletben a P7:W7 helyett AB7:AO7, illetve amelyik oszlopig tartanak az adataid.
Meg lehet oldani képlettel, de kell egy kis ügyeskedés.
1. a 7. sorban (ha üres, egyébként lehet a 8., 9., stb.) az O oszlopba írj nullát, majd a P oszlopba az =O7+P6 képletet, majd ezt töltsd ki jobbra végig.
2. Az M6 oszlopba írd be a következő képletet: =HOL.VAN(L6;P7:W7;IGAZ) Az általam hasraütéssel beírt mintában ez a 6. hét.
Szia! A kérdező 2000-es Excelében levő KEREK függvény pedig magasabb verziókban már nem létezik, pedig az a magyar szabályok szerint kerekített... Az "új" Kerekítés függvény erre sajnos nem alkalmas.
