-
Fototrend
A Microsoft Excel topic célja segítséget kérni és nyújtani Excellel kapcsolatos problémákra.
Kérdés felvetése előtt olvasd el, ha még nem tetted.
Új hozzászólás Aktív témák
-
Grodd
tag
válasz butch3r #30300 üzenetére
Szia, ez nem olyan kompex, mint ahogy gondolod, valójában egy igen szimpla lekérdezés, pár egérkattintás az egész
Többféleképpen meg lehet oldani, de a makró az az eszköz, ami nekem utoljára eszembe jutna)
Más eszközökkel sokkal egyszerűbb.
A két leginkább alkalmas a Microsoft Query vagy a PowerQuery.Utóbbi kulturáltabb, de vagy Excel 2016 kell hozzá, vagy Office 365, vagy Excel 2010-2013 mellé PowerQuery add-in (ingyenesen tölrthető a Microsoft oldaláról)
Microsoft Query-ben egy pár egérkattintással több, és ha teljesen rugalmasra és automatizáltra akarod csinálni, akkor igényelni fog egy VBA sort is (PQuery-hez annyi sem kell).Persze megondható makróban is, nem nagy kunszt, ha feltétlenül ahhoz ragaszkodsz hozzá, de miért akarjunk fúróval szöget verni ?
Oszdd meg valahol a filet, és megcsinálom mindkét módon.
-
sztanozs
veterán
válasz butch3r #30300 üzenetére
Public Sub ttt()
Dim r1 As Range
Set r1 = Application.Intersect(Workbooks("Book1").Sheets("Sheet1").Range("B25:D65535"), Workbooks("Book1").Sheets("Sheet1").UsedRange)
Dim v1
v1 = r1.Value2
Dim r2 As Range
Set r2 = Application.Intersect(Workbooks("Book2").Sheets("Sheet1").Range("B25:D65535"), Workbooks("Book2").Sheets("Sheet1").UsedRange)
Dim v2
v2 = r2.Value2
Dim oldbound, newbound
oldbound = UBound(v1, 1)
newbound = oldbound + UBound(v2, 1)
Dim v_cel()
ReDim Preserve v_cel(1 To newbound, 1 To 3)
Dim ix, iy
For ix = 1 To UBound(v1, 1)
For iy = 1 To 3
v_cel(ix, iy) = v1(ix, iy)
Next
Next
For ix = 1 To UBound(v2, 1)
For iy = 1 To 3
v_cel(oldbound + ix, iy) = v2(ix, iy)
Next
Next
Dim r_cel As Range
Dim kezdosor
kezdosor = 10
Set r_cel = Workbooks("Book1").Sheets("Sheet2").Range("B" & kezdosor & ":D" & kezdosor + UBound(v1, 1) - 1)
r_cel.Value2 = v1
End SubJOGI NYILATKOZAT: A bejegyzéseim és hozzászólásaim a személyes véleményemet tükrözik; ezek nem tekinthetők a munkáltatóm hivatalos állásfoglalásának...
-
butch3r
senior tag
válasz sztanozs #30302 üzenetére
@Sztanozs Köszi, majdnem jó, de lehet én állítok be valamit rosszul, de csak 1ik sheetről másolja át adatokat. arról a sheetről amelyik az I oszloptól kezdődik nem.
@Grodd: Powerquerry-t sajnos nem is ismertem. Beszélek IT-val hogy felrakják e nekem. Makró mellett az szól, hogy nem fogom örökké én csinálni a frissítést, és makró minden gépen van, míg pquery nincs (perzse ha nekem lehet akkor másnak is lehet instalálni). A file-t nem tudom megosztani, tele van bizalmas adattald e köszi
-
sztanozs
veterán
válasz butch3r #30305 üzenetére
Ha debugerrel megállítod (F9 - brakepoint) az utolsó soron (v2 = r2.Value2) akkor visszaad valamit a range-re (Locals ablak - View > Locals Window)?
[ Szerkesztve ]
JOGI NYILATKOZAT: A bejegyzéseim és hozzászólásaim a személyes véleményemet tükrözik; ezek nem tekinthetők a munkáltatóm hivatalos állásfoglalásának...
-
sztanozs
veterán
válasz butch3r #30307 üzenetére
Igen, úgy tűnik valami nincs rendben a sheet-tel, mert a függvény alapján fel kellene vennie a 25-65535 sorban levő adatokat (pontosabban kiválasztani a kitöltött range-et). Biztos jó a workbook és a worksheet neve?
Csak akkor lesz Nothing, ha nincs ott semmi adat.
JOGI NYILATKOZAT: A bejegyzéseim és hozzászólásaim a személyes véleményemet tükrözik; ezek nem tekinthetők a munkáltatóm hivatalos állásfoglalásának...
-
kaposznyak
aktív tag
Sziasztok.
Segítség kéne.A feladat: Bankomtól letöltöttem az elmúlt évek levonásait és jóváírásait. Egy oszlop, + és - számokkal, időrendi sorrendben (alulról felfelé)
Egy grafikon kéne belőle, ami az egyenleg változását mutatná.
Tehát +ok felfelé viszik a vonalat, -ok lefelé, de töretlenül kúszik minél meredekebb szögben az ég felé:-)
Ne csak egy vízszintes tengely plusz vagy mínusz oldalára, húzzon egy vonalat (nekem elsőre csak ezt csinálta).Előre is kösz!
-
ny.janos
tag
válasz kaposznyak #30309 üzenetére
Ne a letöltött adatokból csináld a grafikont, hanem hozz létre egy görgetett összeget előbb. Abból olyan grafikont fogsz kapni, amilyet szeretnél. (Persze ha nem megfelelő mértékben tör az ég felé, az nem feltétlen az excel hibája.)
-
Grodd
tag
válasz kaposznyak #30309 üzenetére
nyjanos által említett görgetett összeget így hozhatod létre például , ha pl A oszlopban vannak az első sortól kezdve a terhelések/jóváírások, és B-be akarod a göngyölített összeget :
B1-be írd be:
=SZUM(A$1:A1)
És ezt a képletet húzd le. -
Grodd
tag
válasz kaposznyak #30313 üzenetére
Most látom, hogy azt írtad "alulról felfelé"
Ha ez azt jelenti, hogy a legújabb mozgások vannak felül és a legrégiebbek alul, akkor értelemszerűen fortítsd meg a lefixálást:=SZUM(A1:A$10000)
Ha 10000 sorba belefér a dolog.
De teheted rugalmassá is, úgy, hogy ne számítson a sorrend, ha egy oszlopban ott van az időpont is , akkor SZUMHA-val megoldhatod így is:
Ha A-ban van az terhelés/jóváírás időpontja, B-ben az összeg, akkor C1-be írd ezt, és húzd le:
=SZUMHA(A:A;"<="&A1;B:B)
Így akárhogy lehet keverve a sorrend, az adott időpontban aktuális egyenleget fogja mutatni a C oszlop
[ Szerkesztve ]
-
Fferi50
őstag
válasz Delila_1 #30282 üzenetére
Szia!
Amit gyanítottam, az bekövetkezett.... Szerintem egy sima kimutatással meg lehet oldani a problémát, nem kell hozzá bűvészkedni az idővel és a napokkal:
A kimutatás értékmezőit óra szerint csoportosítani kell, majd kiválasztani a maximumot és beállítani az Eltérés az előzőhöz. Ennyi lenne szerintem.
Üdv.
[ Szerkesztve ]
-
Delila_1
Topikgazda
válasz Fferi50 #30316 üzenetére
Szia!
Megkaptam a füzetet.
A fapados (képletekkel bevitt) eredmények jók, a kimutatással viszont eltérések vannak. A valahonnan letöltött adatokkal van némi gubanc, amit a kimutatás érzékel, a képletek simán veszik az akadályt. A kimutatáshoz először makróval kellene rendet vágni az adatok között.Mindenképp jó, hogy összeállítottad, eddig nem vettem észre az "Eltérés" opciót, és ez másoknak is hasznos lehet. Köszönöm.
Programozó: hibás programok megírására és kijavítására kiképzett szakember. Többet ésszel, mint ész nélkül.
-
Grodd
tag
válasz Stanlee #30250 üzenetére
Szia, bár látom, már születtek már megoldások, de az ilyeneket a legelegánsabb szerintem Beolvásás és átalakítás (Get&Transform)-mal csinálni, így egy lépésben leautomatizálhatod az egész folyamatot.
- Olvasd be a csv-t,
- fűzd össze a dátumot és az időt, (ha a nyelvi beállításaid nem egyeznek a forrásfájl-lal, erőszakold meg (Using locale vagy "Nyelvterület használata" / angol ),
- hívd be az órákat a minimum dátumtól addig amíg akarod (45 napot mondtál, tehát 45*24 = 1080), ezt egyesítsd az csv-vel, és töltsd ki az adatokat fill down-nal vagy fill-uppal (attól függően, hogy szeretnéd, ezt nem írtad le a feladatleírásban: vagyis ha pl 10 óra 55 perckor 1020 az érték, 11 óra 05 perckor 1021, akkor mi legyen 11 órakor az érték? Ha a korábbi, akkor fill up, ha a későbbi, akkor fill down)
- unpivotold, mert úgy egyszerűbb vele bánni, magyarban ezt "Oszlopok elemi értékekre bontása"-ra fordítótták , és copyzd le a lekérdezést, amiben told el az idő egy órával (#duration), és ezd merge-eld (egyesítsd) az eredetivel. Csinálj egy különbséget a két értékből, és utána vissza is pivotolhatod (magyarban ezt "Forgatott oszlop"-nak nevezték el )És kész is (beállíthatsz rá rendszeres frissítést, vagy amit akarsz)
Feltöltöttem ide (csak a forrás csv-t kell lecserélned, és ráfrissíteni a lekérdezésre) :
Ide meg egy általam kreált csv-t (beolvastam a képedet OCR-ral, de egyszerűbb lett volna, ha inkább feltöltöd a template-et valahova )
Csak meg kell adnod a logo.csv-d elérési útján a "logo" lekérdezésben a Forrás melleti kis fogaskerékre kattintva
Említetted, hogy még van más forrásból származó csv is, de ennek nem adtad meg a formátumát, de természetesen azt is bemerge-elheted ebbe, és végezhetsz vele akármilyen transzformációt.
(Ja, Excel 2016 kell hozzá vagy Office 365, ha korábbi exceled van, akkor szokás szerint PowerQuery bővítményt kell feltenni, de az ma már úgyis kötelező add-in a régebbi excelekhez )
[ Szerkesztve ]
-
csongi
veterán
Sziasztok!
Az alábbi képletet keresem.
Ha j16 >= 18 de kisebb mint 39 akkor a k2=20; Ha j6>=40 de kisebb mint 64 akkor a k2=15;Ha j16>=65 de kisebb mint 75 akkor k2= 10; Ha J6>76 akkor a k2 egyenlő 5-el. Minden más estben a k2 értéke 0.köszönöm.
Általános képlet kellene office 2007 de Libre oficeba is használnám, és Magyar nyelv. -
ny.janos
tag
válasz csongi #30319 üzenetére
K2 cella értéke 2 másik cella értékétől függ (J6 és J16), vagy csak elírtad?
A leírásod alapján mindegyik tartomány között van egy érték, ahol 0 lesz az eredmény (39, 64, 75, 76). Valóban ezt szeretnéd, vagy csak 18 alatti érték esetében szeretnél 0-át kapni?[ Szerkesztve ]
-
ny.janos
tag
válasz Sir Pocok #30277 üzenetére
Találtam egy érdekes megoldást a felvetett problémára (tömbfüggvény), gondoltam megosztom itt is az ötletet.
A te kérdésedre adaptálva az ötletet:
=SZUM(INT((ÉV(F5:F127)&HÓNAP(F5:F127))="20151")*I5:I127)[ Szerkesztve ]
-
ny.janos
tag
válasz csongi #30321 üzenetére
K2 cella képlete:
=HA(J16>76;5;HA(J16>=65;10;HA(J16>=40;15;HA(J16>=18;20;0))))A képletben beállított tartományok (ha valamit rosszul értettem, akkor módosíts rajta):
Office 2010-ben csináltam, de a HA függvénynek működnie kell a korábbiban is. Libre ofice működésről nincs információm.
[ Szerkesztve ]
-
PN
újonc
Hello, segítséget szeretnék kérni a következő megoldandó feladathoz:
Van egy oszlopom telefonszámokkal a következő formátumban: +36701234567
Szeretném kimásolni az előhívót egy másik oszlopba. Ennél a példánál azt, hogy "70"
Miként tudnám ezt úgy megoldani, hogy a műveletből makrót is tudjak készíteni?
A válaszokat előre is köszönöm!
-
D5
aktív tag
Sziasztok!
Erre tudnátok gyógyírt: (#30283) D5? -
cellpeti
veterán
semmi
[ Szerkesztve ]
Tigris, tigris, csóvafény...
-
mntn
tag
Közben rájöttem, nem gondoltam, hogy az üres cellát alapból kisebbnek nézi. De ha már itt vagyok, megkérdezném, hogy ti hogyan oldanátok meg a következőt.
Adott két oszlop: "hatarido" (A) és "teljesit" (B). "A" oszlop egy előre beírt későbbi dátum, amely adott napon a MA függvénnyel színezi a hozzá tartozó sort. (figyelmeztetésként, hogy hamarosan lejár)
A "B" oszlop egy teljesítési dátum, amely lehet a határidő előtti és utáni. Ehhez kellene olyat, hogy ha a határidő előtti a dátum, akkor ne fusson le az előző színezés, ha viszont utáni akkor meg vegye le róla. Remélem nem túl zavaros.[ Szerkesztve ]
-
m.zmrzlina
senior tag
Szeretnék készíteni egy listát arról, hogy egy munkalapon adott háttérszínű cellákban milyen cellaértékek fordulnak elő. Így próbálom:
For Each cella In ws_Kabelo.UsedRange
If cella.Interior.ColorIndex = 6 And cella.Value <> "" And Application.WorksheetFunction.CountIf(Worksheets("valami").range("D2:D50"), cella.Value) = 0 Then
Worksheets("valami").Cells(int_vege, 4).Value = cella.Value
int_vege = int_vege + 1
End If
NextEz a kód a "WorksheetFunction osztály CountIf tulajdonsága nem érhető el hibával kiakad.
Ha a For Each cella In ws_Kabelo.UsedRange helyett
pl: For Each cella In ws_Kabelo.Range("A1:Z100") formában adom meg a tartományt akkor lefut.Mi lehet ennek az oka?
-
DrojDtroll
addikt
Sziasztok!
Egy oszlopban vannak adataim. Feltételes formázás segítségével egy képlettel megkeresem azt a cellát amit módosítani akarok. Ez meg is van, csak nem a megkeresett cella formázását kellene módosítani, hanem az egy sorral lentebb lévőt. Mit kell hozzáírnom a feltételes formázás képletéhez? (=B3:B51-B2:B50=$B$53)
-
m.zmrzlina
senior tag
válasz m.zmrzlina #30335 üzenetére
Közben rájöttem. Előbb gondolkozni kellett volna.
-
[HUN]Tyson
senior tag
Üdv!
Egy igen egyszerű feladatot szeretnék megoldani, de csak bonyolultan sikerült, és biztos vagyok benne, hogy megy ez egyszerűen is.
A feladat: A és B oszlopban email címek vannak. C oszlopban szeretném kilistázni A azon elemeit, amelyek nem szerepelnek B-ben. Szóval B-t kell kivonni A-ból
Úgy oldottam meg, hogy A-t átmásoltam C-be, majd B és C oszlopokat kijelölve feltételes formázással kiszíneztem az azonosakat, C-t szín szerint rendeztem, majd kitöröltem a színeseket. Így megkaptam A azon elemeit, amik nem szerepelnek C-ben.
Dedós, de működik, csak jó lenne egyszerűbben. Ötlet? Előre is köszi! -
bsh
őstag
válasz [HUN]Tyson #30338 üzenetére
adatok - rendezés és szűrés - speciális - irányított szűrés
-
PETEE78
senior tag
válasz [HUN]Tyson #30338 üzenetére
szia!
adatok fül/szűrő/speciális.
más helyre másolja kijelöl
listatartomány, ha van fejléc a2:a8
szűrőtartomány b2:b8
hova másolja c2:c8majd c oszlop kijelöl fejléc nélkül.
ezután adatok fül ismétlődések eltávolítása
folytatja az aktuális kijelöléssel
ismétlődések eltávolítása gombennyi...
üdv,
Peti[ Szerkesztve ]
You are being revived
-
m.zmrzlina
senior tag
válasz m.zmrzlina #30335 üzenetére
Szóval az előbbi kérdésben leírt probléma megoldódott a For Each cella In ws_Kabelo.UsedRange.Cells használatával.
Tovább szeretném finomítani a dolgot hogy megadok egy szűkebb tartományt és csak ezen belül szeretnék kerestetni. Ha úgy csinálom pl hogy:
For Each cella In ws_Kabelo.Range("C2:AQ135").Cells akkor műdödik de mivel a kérdéses tartomány mindig más méretű ezért más módszerre van szükség.
Ismerem a tartomány bal felső celláját (ez mindig a C2) illetve integer tipusú változóban megvan, hogy hány sor hosszú és hány oszlop széles a tartomány amiben kerestetni akarok.
Ezek figyelembevételével hogyan adjam meg a For Each cella... sorban a tartomány határait.
[ Szerkesztve ]
-
ny.janos
tag
válasz DrojDtroll #30336 üzenetére
Cellákat kijelölöd B3-tól jelölöd ki B52-ig, a képlet pedig az alábbi.
=$B2-$B1=$B$53
Ezáltal a B3 cellát formázza B2 alapján, B10-et B9 alapján stb. A lényeg az, hogy a kijelölt tartományodnál eggyel feljebb lévő cellát (esetedben eggyel és kettővel feljebb lévőt) hivatkozz a képletben. Ezáltal eredményül a képletnek eleget tévő cella alatti cellát formázod.[ Szerkesztve ]
-
Delila_1
Topikgazda
válasz m.zmrzlina #30341 üzenetére
Például
kezdsor = 9: kezdoszlop = 3
sorok = 5: oszlopok = 4
Set terület = Range(Cells(kezdsor, kezdoszlop), Cells(kezdsor + sorok - 1, kezdoszlop + oszlopok - 1))
For Each cella In ws_Kabelo.területC9 a bal felső cella, a vizsgálandó terület 5 sor és 4 oszlop.
Programozó: hibás programok megírására és kijavítására kiképzett szakember. Többet ésszel, mint ész nélkül.
-
Delila_1
Topikgazda
válasz m.zmrzlina #30344 üzenetére
Próbálkoztam az offset-tel, de az nem jött össze, pedig elegánsabb lett volna.
Programozó: hibás programok megírására és kijavítására kiképzett szakember. Többet ésszel, mint ész nélkül.
-
ny.janos
tag
Ha jól értelmezem, akkor
- A oszlop adott celláját jelölni szeretnéd, ha az abban szereplő dátum mai, vagy múltbeli.
- Ha B oszlopban szerepel dátum (azaz a termék beérkezett), akkor vegye le a jelölést A-ról (értelmezésem szerint esetedben mindegy, hogy időben érkezett be vagy sem, mert ha B-ben dátum van, akkor nem szeretnéd A-t színezni. Ha rosszul értelmeztem, szólj!)
- Értelemszerűen ne legyen színezve A, ha az nincs kitöltve (én ezt úgy készítettem el, hogy másik színnel jelöltem, mellyel figyelmeztet, hogy nem került kitöltésre).A feltételes formázással jelölendő tartományt kijelölöd A oszlopban, és az alább formázásokat adod meg (a sorrendre figyelj):
-
Delila_1
Topikgazda
kezdet = "$C$9"
Set terület = Range(kezdet).Offset(0, 0, sor - 1, oszlop - 1)Így próbáltam, de nem fogadja el. Munkalapon ez a C9-től a sor-1, oszlop-1 tartományra vonatkozik.
Ott az =ofszet(C9,0,0,sor-1,oszlop-1) a terület.
Például a =SZUM(OFSZET(B2;0;0;3;2)) képlet összegzi a B2:C3 tartományt.[ Szerkesztve ]
Programozó: hibás programok megírására és kijavítására kiképzett szakember. Többet ésszel, mint ész nélkül.
-
Delila_1
Topikgazda
Sub adatok()
Balfelső = "$B$2": Végrehajtás Balfelső
Balfelső = "$C$9": Végrehajtás Balfelső
End SubEz hívja meg a Végrehajtás makrót, átadva a kezdő cella címét
Sub Végrehajtás(Balfelső)
sorok = 5: oszlopok = 4: osszeg = 0
jobbalsó = Range(Balfelső).Offset(sorok - 1, oszlopok - 1).Address
For Each cella In Range(Balfelső & ":" & jobbalsó)
osszeg = osszeg + cella
Next
Range(jobbalsó).Offset(1) = osszeg
End SubA jobb alsó cella alá kiírattam a tartományok összegét.
[ Szerkesztve ]
Programozó: hibás programok megírására és kijavítására kiképzett szakember. Többet ésszel, mint ész nélkül.