-
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
-
-
Fferi50
Topikgazda
-
Fferi50
Topikgazda
Szia!
A képleted nem pontos! Az FKERES függvénynek van egy negyedik paramétere, amelyik azt határozza meg, hogy pontos egyezést keressen-e vagy közelítőt. Ha ezt a paramétert elhagyjuk, akkor közelítő egyezést keres.
Ezért a képletedet a 4. paraméterrrel ki kell egészíteni:=HAHIBA(FKERES(DÁTUM($A3;B$2+1;1)-1;RTab;2;0);"")
Üdv. -
Fferi50
Topikgazda
-
Fferi50
Topikgazda
válasz
Pakliman
#48203
üzenetére
Szia!
A "kötekedésben" igazad van, a képletes megoldás is hibát okoz, ha a sor végéig ki van jelölve.
Ez lehet(ne) a javított függvényes változat:Public Function szamolo(rrange As Range) As LongDim s As String, cl As Range, xx As LongFor xx = 1 To rrange.Cells.Count - 1Set cl = rrange.Cells(xx)s = s & IIf(cl.Value = 1 And cl.Offset(0, 1).Value = "", "1", "")NextIf rrange.Cells(xx).Value = 1 Then s = s & "1"szamolo = Len(s)End Function
Itt nem lépünk ki a tartományból a számolás során.
Üdv. -
Fferi50
Topikgazda
-
Fferi50
Topikgazda
válasz
BEndre34
#48194
üzenetére
Szia!
Próbáld meg a következő tömbképletet az A2 cellába:=SZUM(($B2:$R2=1)*($C2:$S2=""))
A képlet záró oszlopát módosítsd a számodra megfelelő módon (pl. $BH2 és $BI2), fontos, hogy a második rész egy oszloppal legyen eltolva az elsőhöz képest.
A tömbképletet Shift + Ctrl + Enter kombóval kell lezárni, az Excel kapcsos zárójelbe teszi.
Ez a képlet húzható lefelé.
Üdv. -
-
Fferi50
Topikgazda
válasz
karlkani
#48128
üzenetére
Szia!
A figyelmetlenség netovábbja részemről...
Fussunk neki ismét:Sub modosit()Dim a As String, cl As RangeFor Each cl In ActiveSheet.UsedRange.Columns("A").Cells 'az A helyére írd az adataid oszlopátIf InStr(cl.Value, "/") > 0 Thena = cl.Texta = Left(a, InStrRev(a, "/")) & "valami1" & Mid(a, InStrRev(a, "/")) ' a valami1 legyen idézőjelek közötta = Left(a, InStrRev(a, ".") - 1) & "_valami2" & Mid(a, InStrRev(a, "."))cl.Value = aEnd IfNextEnd Sub
Üdv. -
Fferi50
Topikgazda
válasz
karlkani
#48124
üzenetére
Szia!
Igen, ilyen az amikor próba nélkül a régiből kiindulva alakítok át valamit.
Ez elvileg már jó lehet:Sub modosit()Dim a As String, cl As RangeFor Each cl In ActiveSheet.UsedRange.Columns("A").Cells 'az A helyére írd az adataid oszlopátIf InStr(cl.Value, "/") > 0 Thena = cl.Texta = Left(a, InStrRev(a, "/")) & "valami1" & Mid(a, InStrRev(a, "/")) ' a valami1 legyen idézőjelek közötta = Replace(a, ".", "_valami2.")cl.Value = aEnd IfNextEnd Sub
Ismételten bocs.
Üdv. -
Fferi50
Topikgazda
válasz
karlkani
#48119
üzenetére
Szia!
A módosított makró:Sub modosit()Dim a As String, cl As RangeFor Each cl In ActiveSheet.UsedRange.Columns("A").Cells 'az A helyére írd az adataid oszlopátIf InStr(cl.Value, "/") > 0 Thena = cl.Texta = Left(a, InStrRev(a, "/")) & "valami_" & Mid(a, InStrRev(a, "/") + 1) ' a valami_ legyen idézőjelek közötta=replace(a,".","_valami2.")cl.Value = aEnd IfNextEnd SubÜdv. -
Fferi50
Topikgazda
-
Fferi50
Topikgazda
válasz
ny.erno
#48093
üzenetére
Szia!
Az Excel valamiért nem ismeri fel automatikusan a kódolást, ezért meg kell "erőszakolni" egy kicsit.
[Itt találhatsz segítséget az átalakításhoz]
Szövegfájlként kell beolvasni és a szövegvarázslóban megkeresni az UTF-8 kódot, nagyon a vége felé lesz.
Üdv. -
Fferi50
Topikgazda
válasz
eszgé100
#48082
üzenetére
Szia!
Szerintem majdnem minden tanfolyamra elmondhatja valaki, hogy semmit sem ért....
Ha nem érintette azokat a témákat, ami őt érdekli, esetleg sok olyan dolog volt benne, ami neki már a kisujjában van stb.
Ezért azt javaslom, célratörően, a téged legjobban érdeklő témákban (függvényekben) nézz körül először az Excel Helpjében, aztán vagy azzal párhuzamosan a neten. Rengeteg példát, ismertetőt fogsz találni.
Olyan nincs, hogy részt veszel 1 db tanfolyamon és a kezedben lesz az Excel bölcsek köve.
Eredeti felvetésedhez kiegészítésként még annyit, hogy fontos a probléma megfogalmazása, ezután a kapcsolódó "modell" megalkotása majd ezután jöhet az Excel szerintem. Kérdés, mit szeretnél a befektetéseidről látni a táblázatban...
Üdv. -
Fferi50
Topikgazda
válasz
BEndre34
#48073
üzenetére
Szia!
Ha a mutatott képleted működik, akkor csak annyi a teendőd, hogy a SOR(1:1) helyett
OSZLOP()-1 kifejezést írsz és elhúzod jobbra a képletet.
Itt a teljes tömbképlet, az A oszlopban az ID, B oszlopban a hetek, nincs fejléc.:=HAHIBA(INDEX(Munka1!$B$1:$B$3376;KICSI(HA($A1=Munka1!$A$1:$A$3376;SOR(Munka1!$B$1:$B$3376)-SOR(Munka1!$B$1)+1);OSZLOP()-1));"")
Üdv. -
Fferi50
Topikgazda
válasz
lumpy92
#48039
üzenetére
Szia!
A diagramon jobb egérgomb - adatok kijelölése. A megjelenő párbeszéd panelen szerkesztés.
Ezután a megjelenő újabb párbeszédpanelen felcseréled az x és y adatsort:
Arra figyelj, hogy az elején ne maradjon ott az általa előszeretettel automatikusan beírt =1 érték.
OK és máris megcserélődött az x és y adatsor.
Üdv. -
Fferi50
Topikgazda
válasz
Geryson
#48032
üzenetére
Szia!
Bocs, de a számlázó program nem tudja összeadni ezeket az értékeket?
Ha ennyire sok adatod van, akkor ki lehetne tenni a kivételeket egy segédoszlopba.
Esetleg a segédoszlopban jelölni, hogy melyik cikkszámot kell összeadni és akkor egy két feltételes SZUMHATÖBB függvénnyel (DÁTUM, Cikkszám kell-e oszlop) össze tudod adni.
Üdv. -
Fferi50
Topikgazda
válasz
Geryson
#48029
üzenetére
Szia!
Így ismeretlenül számomra elképzelhetetlen amit írsz... Elhiszem, hogy így van, de biztos Excelben kell ezt megcsinálni?
Részösszeg függvény és szűrés használata nem lenne célravezetőbb, mivel megfelelő függvény használatával a kiszűrt tételek nem kerülnek be az összesítésbe?
Esetleg kimutatás és annak szűrése?
Talán mutass egy egyszerűbb példát, hogy mire szeretnéd használni, hátha akkor jobb ötleteink születnek.
Üdv. -
Fferi50
Topikgazda
válasz
Geryson
#48026
üzenetére
Szia!
Nem lenne egyszerűbb a "fordítottját" számolni és kivonni a teljes összegből? A 17 kritérium túl soknak tűnik.
Ha szöveges feltételekről van szó, akkor próbálkozhatsz a helyettesítő karakterekkel ( csillag és kérdőjel) esetleg, hogy kevesebb legyen a kritériumok száma.
Üdv. -
Fferi50
Topikgazda
Szia!
Kár, hogy a két képet nem csatoltad.
Ha vonaldiagramot csináltál, akkor pl. lehet esésvonalakat hozzáadni a Diagramtervezés menüben:
Esetleg tehetsz fel trendvonalat, annak az egyenletét, amiből ki lehet számoltatni a metszéspontot.
[itt találtam vba-s megoldást]
a lappy által mutatottra, ezt az adott munkafüzetbe lehet beépíteni. Utána makróbarátként kell elmenteni.
Üdv. -
Fferi50
Topikgazda
válasz
eszgé100
#47923
üzenetére
Ha jól látom, akkor a manualcheck változód a ciklus során nem változik, illetve a manual "Yes" esetén True lesz. Ez ugye szűrésnél rendben is van, de ha nincs szűrés, akkor egyetlen kézi ellenőrzésre szoruló tábla is megakasztja az összes többi bezárását is.
Ha jól gondolom, akkor a szűrés nélküli állapotban meg kellene vizsgálni, hogy az adott fájlhoz tartozik-e olyan sor, amelyben kézimunka szüksége.
Ezt a Countifs függvénnyel lehet megnézni szerintem, első feltétel a fájl neve a D oszlopon, második feltétel a yes a Manual Update oszlopon. Ha ez nem 0, akkor nem lehet a fájlt bezárni.
Üdv. -
Fferi50
Topikgazda
válasz
eszgé100
#47923
üzenetére
Szia!
Szerintem először nézd meg a Manual Update értékét.If Not manualcheck Then
Set scrange=ws.UsedRange.Columns("D").SpecialCells(xlCellTypeVisible).Find(what:=sPath, after:=Range("D" & counter))
If scrange.Row <= counter Then Excel.Workbooks(fileName).Close SaveChanges:=True
End If
Sőt, tulajdonképpen a keresési eredményt közvetlenül is lehet használni:If ws.UsedRange.Columns("D").SpecialCells(xlCellTypeVisible).Find(what:=sPath, after:=Range("D" & counter)).Row <= counter Then Excel.Workbooks(fileName).Close SaveChanges:=True
mivel legalább az adott sorban levő tételt meg fogja találni, tehát hibát nem okozhat a találat hiánya.
Üdv. -
Fferi50
Topikgazda
válasz
Delila_1
#47919
üzenetére
Szia!
Én nem a program leírás áttekinthetőségére gondoltam - persze az is nagyon fontos, hanem a folyamatra magára.
Természetesen a bemásolt programok tabulálása is fontos, de sajnos a szerkesztő nem igazán támogatja ezt és igen, előfordul figyelmetlenség is.
Ráadásul a Go To példád pont a hibakezeléssel kapcsolatos, amire én is azt írtam, hogy néha nem is oldható meg másként.
Mivel az utasításkészlet tartalmazza a Go To szerkezetet, programozási stílus függő is lehet az alkalmazása.
Üdv. -
Fferi50
Topikgazda
válasz
eszgé100
#47915
üzenetére
Szia!
1.)"a ciklus későbbi lépéseiben még szükség lesz rájuk, pl amikor egy workbookban van 20 worksheet, de nem egyszerre ömlesztve akarom őket kinyomtatni,"
ugyanakkor a ciklusban minden sornál ott van a Workbooks.Open, anélkül, hogy megnéznéd, nincs-e már megnyitva az adott file.
"mert utána akkor még kézzel is le kell válogatnom később"
másrészt, ha egy következő file másik munkalapját nyomtatod utána, akkor nem kell kézzel leválogatni az előzőtől?
2.a) szerintem alapvetően akkor van szükség GoTo utasításra, ha a makró/folyamat rosszul van megtervezve, megszervezve. Az ugrálás rontja az áttekinthetőséget és szerintem lassítja is a végrehajtást. Egy esetben látom indokoltnak, a futási hibák kezelésénél, ott ahol a hiba természete miatt külön hibakezelési rutinra van szükség az adott makrón belül. (Lásd: On Error Goto .. utasítás ).
2.b) Mod funkció -> egy osztás maradék eredményét adja vissza. Nálad azért 1 a feltétel értéke, mert mindig az adott ciklus utáni első hónapban nyomtatod a munkafüzetet (vagy ha úgy jobban tetszik, a ciklus első hónapjában). 3 havonta esetén az 1,4,7,10 hónapban. De mondhatnád azt is, hogy a 3,6,9,12 hónapban akarod nyomtatni, akkor a 0 maradék lenne a feltétel. Tehát te döntöd el, melyik hónapban kezdődjön a nyomtatási ciklus és a maradékot annak megfelelően használod feltételnek. Ugyanez igaz a többi ciklikus feltételre is.
3.a) Hibakezelésen tehát a felhasználói hibák vizsgálatát érted (amivel egyrészt megelőzheted fals adatok dokumentálását, másrészt program futási hibák keletkezését). Azt gondolom, erre az esetre érdemes egy külön függvényt írni, ami megizsgálja a kritikus összefüggéseket és logikai értéket ad vissza a vizsgálat eredményéről, amitől függően megy tovább a ciklus vagy elengedi azt a munkafüzetet/lapot.
Érdemes ettől függően azon is gondolkodni, hogyan kezeljük a futás idejű hibákat, mivel nem szeretnénk, ha ezek miatt utólag kellene a felhasználókkal hibát javíttatni.
3.b) Szűrés esetén a Darabteli függvény nincs tekintettel a szűrt állapotra valóban. Ebben az esetben a Save&close cella tartalma helyett meg kell nézned a szűrt területet makróval.
A D oszlop szűrt tartományát a következőképpen kapod meg:ActiveSheet.UsedRange.Columns("D").SpecialCells (xlCellTypeVisible)
A Find metódussal meghatározhatod a keresett érték helyét.Dim scrange As Range
Majd a nyomtatás után:Set scrange=ActiveSheet.UsedRange.Columns("D").SpecialCells (xlCellTypeVisible).Find(what:=sPath,after:=Range("D" & counter))If scrange.Row<=counter then --- save & close
Mivel nincs további találalat a szűrt tartományban, ezért az első találatra fog visszaugrani.
Üdv. -
#47907
Fferi50
Topikgazda
Fire/SOUL/CD
#47905
Fferi50
Topikgazda
válasz
Fire/SOUL/CD
#47905
üzenetére
Szia!
Lehet, hogy félreértettem valamit.
Volt egy ilyen mondatod: A modulban deklarált ugyanilyen nevű változó, csak a modulban található kódok számára látható.
Üdv. -
#47904
Fferi50
Topikgazda
Fire/SOUL/CD
#47901
Fferi50
Topikgazda
válasz
Fire/SOUL/CD
#47901
üzenetére
Szia!
Téves az az információd, hogy a modul elején publikusnak definiált változót csak az a modul tudja használni, amelyben definiálták. Mivel publikus, így valamennyi eljárás hozzá tud férni, ehhez meg kell adni a teljes nevét, ami Modulnév.Változónév forma és így bármilyen másik modul is tudja használni, sőt a globális modulok, amelyek nem egy-egy objektumhoz (munkalap, userform stb.) kapcsolódnak, a Modulnév nélküli változót is tudják használni.
Amire te gondolsz az a modul elején DIM utasítással létrehozott modulszintű változó. Az valóban csak az adott modulban használható.
Üdv. -
#47903
Fferi50
Topikgazda
Fire/SOUL/CD
#47901
Fferi50
Topikgazda
válasz
Fire/SOUL/CD
#47901
üzenetére
Szia!
Megfelelő kódfelépítéssel igen, de nem kifejezetten ajánlatos. A példát látod a képen:
Itt egy modulban van a függvény és a főprogram.
Ahhoz, hogy egy függvényben számolt változó értéket kapjon, a változót publikusnak kell definálni a modul elején, eljáráson kívül, majd meg kell hívni hozzá a függvényt, ezt látod a sárgával jelölt sorban.
Üdv. -
Fferi50
Topikgazda
válasz
eszgé100
#47881
üzenetére
Szia!
Apróságokat tennék hozzá, talán gyorsít valamit rajta:
1. Kérdés: ahol Save&Close =no ott nem kell bezárni a fájlt? Mert ebben az esetben sok-sok fájlod nyitva fog maradni.
Ha mégis be kell zárni, akkorIf CStr(saveandclose) = "yes" Then
Excel.Workbooks(fileName).Close SaveChanges:=True
Else: GoTo nextraw
End If
helyett javaslom:Excel.Workbooks(fileName).Close SaveChanges:= CStr(saveandclose) = "yes"
Ha nyitva kell hagyni, akkor is elég az IF-es sor a következőképpen:If CStr(saveandclose) = "yes" Then Excel.Workbooks(fileName).Close SaveChanges:=True
Nem kell hozzá ELSE és END IF.
2. Javaslat: én nagyon nem szeretem az ugrálást makrón belül, általában mindig meg lehet oldani e nélkül a feladatot. Nálad 2 cimke van: openworksheets és nextraw.
Egy új változó bevezetésével el lehet kerülni a cimkéhez ugrást.
Dim nyomtatni As Boolean
Ennek a változónak adunk értéket a Select Case utasításokon belül - ezt is egy picit egyszerűsítve:Select Case CStr(freq)
Case "4 weekly", "monthly"
nyomtatni = True
Case "2 monthly"
nyomtatni = Month(nextmonth) Mod 2 = 1
Case "3 monthly"
nyomtatni = Month(nextmonth) Mod 3 = 1
End Select
A két cimke helyére pedig:openworksheets: helyett:
If nyomtatni Then
.
.
nextraw: helyett
End If
Áttekinthetőbb és szerintem gyorsabb is lehet.
3. Kérdés:
Milyen szűrést szeretnél? Hol lenne helye a hibakezelésnek?Üdv.
-
Fferi50
Topikgazda
Szia!
Biztos, hogy dátum az amit ott kapsz? Szerintem szöveg, csak dátumnak gondolod.
Nézd meg a cellaformátumot légy szíves és próbáld átállítani számra.
Ha szám lesz belőle, akkor dátum és csak formátumot kell változtatni.
Ha marad ilyen, akkor szöveg és függvényekkel tudod dátummá alakítani segédoszlopban, pl.
=Dátum(jobb(A1;4);közép(A1;4,2);bal(A1;2))
Üdv. -
Fferi50
Topikgazda
válasz
ny.erno
#47869
üzenetére
Szia!
If Sheets.Count = 1 Then
Set sh2 = ActiveWorkbook.Sheets.Add(after:=sh1)
Else
Set sh2 = Sheets(2)
End If
Ez a rész akkor ad hozzá új munkalapot, ha csak egy lap van a munkafüzetben. Ha több, akkor a második munkalapot használja - amin elvileg az első futás után a pivot keletkezik.
Ugye első futás előtt követelmény, hogy csak 1 munkalap legyen a füzetben, így a futáskor létrehozott munkalap lesz a második.
Ismételt futás után már nem kell a pivotot létrehozni, az ott van a második munkalapon, csak aktualizálni kell.If .Value <> "" Then .CurrentRegion.ClearContents
If sh1.Range("D1").Value <> "" Then sh1.Range("D1").CurrentRegion.ClearContents
If sh1.Range("F1").Value <> "" Then sh1.Range("F1").CurrentRegion.ClearContents
Ez a 3 sor törli a második munkalap D1-es területét és az első munkalap D1 és F1 oszlopát.
Szerintem nem lenne szükség törlésre.
Mi miatt volt nálad a külön törlésekre szükség?
Üdv. -
Fferi50
Topikgazda
válasz
ny.erno
#47863
üzenetére
Szia!
Én az egyik futásnál ellenőriztem, hogy megvan-e mind a kétszázezer szám (ismétlődések összeadva + az egyedi) pontosan megvolt.
A pivottáblás makró, feltételek:
Első futtatásnál:
Csak 1 munkalap legyen a munkafüzetben, amelyiknek az A oszlopában ott vannak a számok. A1 cella fejléc.
Ekkor a makró létrehoz egy nevet - forras - a névkezelőben, ami beállítja a pivot forrását
Ezután létrehoz egy új munkalapot, arra a pivottáblát.
Az új D1 cellájától kezdve átmásolja a pivot eredményét.
Szűri 1 -re (azaz egyediek) - átmásolja az első munkalap D oszlopába
Szűri >1-re (azaz ismétlődők) - átmásolja az első munkalap F oszlopába
Ez kb. fél perc 200000 tételnél.
Ha a továbbiakban a változások kezelésére is ezt szeretnéd használni, akkor nincs más teendő, mint az új sorozatszámokat hozzáírni/felülírni az első munkalap A oszlopában, majd jöhet a
második/sokadik futtatás
Fontos! Ebben az esetben is az első munkalapon kell állnod, amikor a makrót indítod.
Az előző futás eredménye felülíródik a D és F oszlopokban.
Íme a makró:Sub tablas()
Dim sh1 As Worksheet, sh2 As Worksheet, pvt As PivotTable, tblsource As String, pvtfname As String, nm As Name
Application.ScreenUpdating = False
Set sh1 = ActiveSheet: pvtfname = sh1.Range("A1").Value
If Names.Count > 0 Then
Set nm = Names("forras")
End If
If nm Is Nothing Then Set nm = ActiveWorkbook.Names.Add(Name:="forras", RefersTo:="=OFFSET(" & sh1.Name & "!$A$1,0,0,COUNTA(" & sh1.Name & "!$A$1:$A$300000),1)")
If Sheets.Count = 1 Then
Set sh2 = ActiveWorkbook.Sheets.Add(after:=sh1)
Else
Set sh2 = Sheets(2)
End If
tblsource = Replace(Evaluate(Names("forras").RefersTo).Address(ReferenceStyle:=xlR1C1, external:=True), "[" & sh2.Parent.Name & "]", "")
If sh2.PivotTables.Count = 0 Then
Set pvt = sh1.Parent.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=tblsource, Version:=6).CreatePivotTable(tabledestination:=Replace(sh2.Range("A1").Address(ReferenceStyle:=xlR1C1, external:=True), "[" & sh2.Parent.Name & "]", ""), TableName:="Srszamok", Defaultversion:=6)
pvt.AddDataField pvt.PivotFields(pvtfname), "Darabszám", xlCount
pvt.PivotFields(pvtfname).Orientation = xlRowField
Else
Set pvt = sh2.PivotTables(1)
pvt.RefreshTable
End If
With sh2.Range("D1")
If .Value <> "" Then .CurrentRegion.ClearContents
If sh1.Range("D1").Value <> "" Then sh1.Range("D1").CurrentRegion.ClearContents
If sh1.Range("F1").Value <> "" Then sh1.Range("F1").CurrentRegion.ClearContents
.Resize(rowsize:=pvt.TableRange1.Rows.Count, columnsize:=pvt.TableRange1.Columns.Count).Value = pvt.TableRange1.Value
With .CurrentRegion
.AutoFilter field:=2, Criteria1:="1"
.Columns(1).SpecialCells(xlCellTypeVisible).Copy Destination:=sh1.Range("D1")
.AutoFilter field:=2, Criteria1:=">1"
.Columns(1).SpecialCells(xlCellTypeVisible).Copy Destination:=sh1.Range("F1")
.AutoFilter field:=2
End With
End With
sh1.Range("D1").Value = "Egyedi": sh1.Range("F1").Value = "Ismétlődő"
sh1.Activate
ActiveWindow.ScrollRow = 1
Range("D1").Select
MsgBox "Készen vagyunk!"
Application.ScreenUpdating = True
End Sub
Üdv. -
Fferi50
Topikgazda
válasz
ny.erno
#47856
üzenetére
Szia!
Közben találtam egy makró nélküli megoldást is, de ehhez pár műveletet el kell végezni :
1. Legyen az A oszlopnak fejléce - mondjuk Sorozatszám
2. Beszúrás - kimutatás - új lapra
Sorozatszám mező a Sorokhoz
Sorozatszám mező az Érték területre - mennyiség Sorozatszám
Elfogadható időn belül kész a kimutatás!
3. Az egész kimutatást a végösszeg sor nélkül kijelölni - beillesztés értéket egy új területre az új lapon.
4. Szűrő bekapcsolása az átmásolt adatokra
5. Szűrő - csak az 1 bekapcsolva - az egyedi értékek lesznek. Sorozatszám másolás - irányított beillesztés értéket - oda, ahol látni szeretnéd az egyedi sorozatszámokat
6. Szűrő - átállítás az 1 kivételével minden - az ismétlődő értékek maradnak. Sorozatszám másolás - irányított beillesztés - oda, ahol az ismétlődéseket szeretnéd látni.
Kétszázezer sorral kevesebb ideig tartott, mint ide leírni!
Persze usert ilyenre kérni nem lehet, tesztelem a hozzá kapcsolódó makrót, ha kész lesz felmásolom.
Üdv. -
Fferi50
Topikgazda
válasz
ny.erno
#47856
üzenetére
Szia!
Íme:Sub valogato()
Dim a, x As Long, y As Long, u As String, d, v As String
ActiveSheet.UsedRange.Columns("A").Copy Range("D1")
y = ActiveSheet.UsedRange.Rows.Count
Debug.Print "sort indul:" & Time
With Range("D1:D" & y)
.Sort key1:=Range("D1"), Header:=xlNo
Debug.Print "sort vége:" & Time
a = .Value
End With
u = ""
Debug.Print "Keresés indul: " & Time
d = ""
For x = 1 To y - 1
If a(x, 1) = a(x + 1, 1) Then
If d = "" Then
u = u & ";" & a(x, 1): d = a(x, 1)
Else
If a(x + 1, 1) <> d Then u = u & ";" & a(x, 1): d = a(x, 1)
End If
Else
If a(x, 1) <> d Then v = v & ";" & a(x, 1)
End If
DoEvents
If x Mod 1000 = 0 Then Application.StatusBar = "Készen van eddig " & x
Next
If a(x, 1) <> d Then v = v & ";" & a(x, 1)
Debug.Print "Keresés vége:" & Time
u = Mid(u, 2): v = Mid(v, 2)
a = Application.Transpose(Split(u, ";"))
Range("M1:M" & UBound(a)).Value = a
a = Application.Transpose(Split(v, ";"))
Range("F1:F" & UBound(a)).Value = a
Debug.Print "Visszaírás vége: " & Time
Application.StatusBar = False
MsgBox "Készen vagyunk"
End Sub
Az F oszlopba írja ki az ismétlődés nélküli értékeket.
Üdv. -
Fferi50
Topikgazda
válasz
ny.erno
#47853
üzenetére
Szia!
Azért remélem, hogy az Excel által talált duplikáció az igazi.
Persze ne feledjük, hogy az 123 szöveg és a 123 szám az nem egyforma az Excelben, ebből lehet eltérés.
Gondolom, a sorozatszámaidban betű is van és akkor nem játszik az előző megjegyzésem.
Üdv.
Ps. Remélem, könnyebb lesz az életed vele. -
Fferi50
Topikgazda
válasz
ny.erno
#47843
üzenetére
Szia!
Akkor próbáljuk meg makróval:Sub valogato()
Dim a, x As Long, y As Long, u As String, d
ActiveSheet.UsedRange.Columns("A").Copy Range("D1")
y = ActiveSheet.UsedRange.Rows.Count
Debug.Print "sort indul:" & Time
With Range("D1:D" & y)
.Sort key1:=Range("D1"), Header:=xlNo
Debug.Print "sort vége:" & Time
a = .Value
End With
u = ""
Debug.Print "Keresés indul: " & Time
d = ""
For x = 1 To 200000 - 1
If a(x, 1) = a(x + 1, 1) Then
If d = "" Then
u = u & ";" & a(x, 1): d = a(x, 1)
Else
If a(x + 1, 1) <> d Then u = u & ";" & a(x, 1): d = a(x, 1)
End If
End If
DoEvents
If x Mod 1000 = 0 Then Application.StatusBar = "Készen van eddig " & x
Next
Debug.Print "Keresés vége:" & Time
u = Mid(u, 2)
a = Application.Transpose(Split(u, ";"))
Range("M1:M" & UBound(a)).Value = a
Debug.Print "Visszaírás vége: " & Time
Application.StatusBar = False
MsgBox "Készen vagyunk"
End Sub
A makró az aktív munkalap A oszlopát átmásolja a D oszlopba majd rendezi. Ezután válogatja ki az ismétlődő értékeket és beírja az M oszlopba.
Az előrehaladást a státusz soron lehet követni (ez csak akkor látszik, ha a munkalap nézetben vagy).
A VBA nézet Immediate lapjára kiírja az egyes műveletek végrehajtási idejét. Nekem ez 200000 sor esetén alig több, mint 1 perc volt.
Üdv. -
Fferi50
Topikgazda
válasz
ny.erno
#47841
üzenetére
Szia!
Nem írtad, hogy milyen módszerrel vizsgálod a duplikációt, ami 30-40 percig tart.
Én csak Excel módszert tudok javasolni a 2016-os verzió alapján, feltételezve, hogy az A1 cellától kezdődnek az adataid.
1. a B1 cella képlete:=HA(DARABTELI($A$1:$A$200000;$A1)>1;$A1;"")
2. a B2 cella képlete:=HA(DARABTELI($A$1:$A$200000;$A2)>1;HA(DARABTELI($B$1:$B1;$A2)=1;"";$A2))
Ez a képlet húzható lefelé.
A B oszlopban így azok a számok maradnak, amelyek duplikálva vannak az A oszlopban, mégpedig az első előfordulásnak megfelelő sorban. Köztük "üres" cellák maradnak.
Ha utána a képleteket átalakítod értékké - másolás - irányított beillesztés értéket - akkor már tudsz a B oszloppal "rendezkedni".
Ha nem az első sorban kezdődnek az adataid, akkor annak megfelelően módosítsd a kezdő képlet celláit - figyelj a $ jelekre kérlek.
Üdv. -
Fferi50
Topikgazda
-
Fferi50
Topikgazda
Szia!
Próbáld megnézni, hogy makró melyik része okozza a lassulást.
Egyes részek előtt kiíratod Debug.Printtel az időt, a végén pedig szintén.
Ha még van lehetőség a régi konfigon megfuttatni, akkor ott is, ha nem, akkor is lehet látni, melyik programrésznél időzik el "indokolatlanul".
Üdv. -
-
#47810
Fferi50
Topikgazda
[CS]Blade2
#47807
Fferi50
Topikgazda
válasz
[CS]Blade2
#47807
üzenetére
Szia!
Makróval - eseménykezelővel - meg lehet oldani, hogy mindig az aktuális nézetnek megfelelően színezzen.
Viszont akkor makróbarátként - vagy binárisként - kell a fájlt menteni.
Üdv. -
-
-
Fferi50
Topikgazda
válasz
eszgé100
#47751
üzenetére
Szia!
Több lehetőség is van. A képen látható elrendezés esetén E2 képlete:=MAX(ROUNDUP(ISOWEEKNUM(D2)/4,0)*4,ISOWEEKNUM(D2))*7+C2
Másik ötletem:
Az év elején (vagy akár most) felrakod egy segéd táblázatba a negyedik hétfőket:
Ne kavarjunk bele, legyen I2=C2, majd I3 képlete =I2+28
Lehúzod, ameddig szükséges, majd az így létrejött képletes részt átalakítod értékké (másolás, irányított beillesztés értéket) - csak a biztonság kedvéért, nehogy megváltozzon valami miatt. Akár el is nevezheted a táblázatot.
Ezután az E2 képlete:=IFERROR(VLOOKUP(D2,$I$1:$I$29,1,0),INDEX($I$1:$I$29,MATCH(D2,$I$1:$I$29,1)+1))
Üdv. -
Fferi50
Topikgazda
válasz
eszgé100
#47746
üzenetére
Szia!
Nézetem szerint az alábbi módon lehetne megoldani a problémát:
A1 cellában van a január 1.
B1 cellában van meghatározva az év első hétfője, ezzel a képlettel:=HA(HÉT.NAPJA(A1;2)>=5;A1+8-HÉT.NAPJA(A1+7;2);A1-HÉT.NAPJA(A1;2)+1)
Ezek után a megfelelő hétfő meghatározása szerintem már egyszerű, csak az első hétfőhöz hozzá kell adni a kívánt hét számát 7-tel szorozva.
Remélem ezzel tudtam segíteni.
Az ISOWEEKNUM függvény és a mai dátum csak illusztráció. Ha tudjuk hanyadik hétről van szó, akkor elég azzzal szorozni.
Üdv. -
Fferi50
Topikgazda
válasz
Dilikutya
#47740
üzenetére
Szia!
Egy kicsit körülményes, de szerintem megéri kipróbálni.
1. lépés: Kijelölöd az eredeti munkalapon az A1 cellát. Majd képletek - Névkezelő - új név. A név nálam jokeplet. A hivatkozás pedig:=HAHIBA(HA(BAL(KÉPLETSZÖVEG(A1);5)="=HAHI";KÉPLETSZÖVEG(A1);"=HAHIBA(" & KÖZÉP(KÉPLETSZÖVEG(A1);2;HOSSZ(KÉPLETSZÖVEG(A1))) & ";"""")");"")Bezárod mentéssel a párbeszédpanelt. Ha visszanézed, akkor látni fogod, hogy a hivatkozásban szerepel a munkalap neve is.
2. lépés: beszúrsz egy új üres munkalapot.
Az A1 cella képlete:=jokeplet
Itt kell egy ellenőrzést csinálni a következőképpen: Az A1 cellán maradva (de nem belelépve!)
Képletek - képletkiértékelő - aláhúzva látod a jokeplet szöveget -belelépés - ekkor kibontja a képletet - itt azt kell látnod, hogy az eredeti munkalap A1 cellájára hivatkozik. Ha nem ezt látod, bezárod a kiértékelőt. Átmész az eredeti munkalapra és a Névkezelőben a hivatkozásban ismételten az A1 cellát kell megadnod.
Ezután már az új munkalapon is az A1 cellát kell látnod a képletkiértékelőben.
Ez azért fontos, mert így a képleted húzható oldalra és lefelé is!
3. lépés: Végighúzod a képletet a szükséges területre.
4. lépés: A végighúzott képletet másolod, majd irányított beillesztés - értéket erre a területre - így megkapod a szükséges képletet!
5. lépés: Az így kapott képleteket az eredeti munkalapodra másolhatod - de nem az értékeket fogod megkapni, hanem a szöveges képletet látod. Innen már csak türelem és gyorsaság kérdése a valódi képletté alakítás:
SAJNOS minden cellán végig kell menned F2 - Enter lenyomásával, hogy érvényre jusson az új képlet.
Természetesen a próbád egy másolaton és 1 oszlopon hajtsd végre - nehogy károsodjanak az adatok (és a hangulatod).
Annyira azért nem bonyolult, mint a leírás mutatja, könnyebb lesz végrehajtani, de nagyon fontos a név pontos megadása. Erre azért van szükség, hogy ne kelljen a képletet mindig teljesen kiírni.
Üdv. -
#47734
Fferi50
Topikgazda
Dark Archon
#47732
Fferi50
Topikgazda
válasz
Dark Archon
#47732
üzenetére
Szia!
Itt találod az MS leírást a VBA-hoz
Üdv. -
#47726
Fferi50
Topikgazda
Dark Archon
#47722
Fferi50
Topikgazda
válasz
Dark Archon
#47722
üzenetére
Szia!
A VBA-ban a nemzetközi formulát és függvényneveket kell használni ilyen esetben, ami azt jelenti, hogy angol függvényneveket és vesszőt a képletekben. Így hordozható a program.
Lehetséges helyi - pl. magyar - formulákat is megadni a FormulaLocal, FormulaR1C1Local tulajdonságokkal, ebben az esetben viszont más nyelvi környezetben vagy más beállításokkal nem fog futni a program, hibát fog okozni.
Üdv. -
Fferi50
Topikgazda
Szia!
A táblázatot ilyen formában képzelem el:
Ebből könnyen lehet szűrni, kimutatást készíteni többféle szempont alapján is.
Az már egy másik kérdés, hogy az adatbevitel milyen módon történik. Talán a legkevesebb hibaforrást tartalmazza egy adatérvényesítéseket tartalmazó munkalap, ahol csak kiválasztani kell a megfelelő értékeket. Ezeket pedig makróval lehet beíratni a megfelelő helyre. Természetesen ehhez a megfelelő listákat össze kell állítani. Így csak a kiválasztásokat kellene ezen a lapon megcsinálni, az eredmény magától töltődne az előző képen látott formában.
Egy példa:
Ez természetesen csak egy ötlet. Ha tetszik és tovább szeretnéd gondolni, akkor szerintem privátban lenne érdemes folytatni megfelelő mintával.
Üdv. -
Fferi50
Topikgazda
válasz
Dr. Mózes
#47674
üzenetére
Szia!
Ha van a táblázatodban fejléc, akkor használható az Adatok - Speciális menüpont (leánykori nevén Irányított szűrés).
Ezzel lehet vagy feltétel alkalmazásával is szűrni - minden feltételt külön sorba kell írni.
A szűrés eredménye maradhat helyben és kerülhet máshova is. Ez utóbbi esetben a menüpontot arról a munkalapról kell indítani, ahová az eredményt szeretnéd kapni.
Ilyen párbeszéd ablakot kapsz:
Érdemes egy kicsit a súgóját tanulmányozni.
Üdv. -
#47676
Fferi50
Topikgazda
Dark Archon
#47675
Fferi50
Topikgazda
válasz
Dark Archon
#47675
üzenetére
Szia!
A rövid válasz: igen.
A hosszabb: lehet Excel sablon alapján nyitni új munkafüzetet.
[Help itt]
Üdv. -
Fferi50
Topikgazda
Szia!
Csak azért, hogy legyen még egy megoldás:=INDEX($A$1:$A$6;HOL.VAN(C1;$A$1:$A$6;-1))
Amint látod, a HOL.VAN függvény a csökkenő sorrendbe rendezés alapján állapítja meg a kérdéses érték helyét, ezért a "szabvány" adataid csökkenő sorrendbe kell rendezni. A HOL.VAN függvény 3. paramétere itt -1!
Üdv. -
Fferi50
Topikgazda
válasz
bozsozso
#47663
üzenetére
Szia!
Ha szöveg van a cellában, akkor ebben a formában is meg kell találnia az értéket.
Ha számok vannak a táblázatban, amiben keresel, akkor az ÉRTÉK függvénnyel számmá kell alakítanod keresendő értéket, mivel a BAL függvény eredménye szöveg.
Mutasd meg légy szíves, hogyan keletkezik nálad a probléma.
Üdv. -
Fferi50
Topikgazda
válasz
botond187
#47645
üzenetére
Szia!
Ez egy annál is összetettebb probléma, mint amire gondolsz. Számvitelileg készletnyilvántartási és értékelési körbe tartozik. Nem oldható meg ilyen egyszerűen. Javaslom nézd meg a számviteli törvény készlet értékeléssel és számlázással kapcsolatos előírásait.
Szerintem sokkal egyszerűbb venni egy számviteli programot, amelyikben benne vannak a megfelelő modulok, csak fel kell paraméterezni azokat.
Csak egy a felvethető kérdések közül a leírtakkal kapcsolatban:
"- a beszerzési ár legyen a számlázásnál korábbi, ahhoz legközelebbi időpontban történt beszerzés ára." kicsit egyszerűsítve ezt írod.
Mi legyen akkor, ha az abban az időpontban beszerzett termék mennyiség már elfogyott? Ezt pl. honnan fogja tudni a számolás?
Tudni kell hozzá a számviteli értékelési politikát -- FIFO, LIFO vagy átlag -- és annak megfelelően kell a készletet is vizsgálni hozzá.
Egy szó mint száz, a leírt követelményeid nem elegendőek a feladat --> az értékesítési árrés <-- megfelelő, számvitelben is felhasználható kiszámításához. Ehhez egy sokkal konkrétabb, a számviteli elszámolásoknak megfelelő specifikáció szükséges.
Üdv. -
Fferi50
Topikgazda
válasz
ReSeTer
#47641
üzenetére
Szia!
A hibaüzenet - Type Mismatch - azt jelenti, hogy nem azonos típusú adatokkal szeretnél műveletet végezni. Ahogyan írtad is, a talalatsorszama változód értéke HIBA, hibához pedig nem lehet hozzáadni számot.
További megjegyzés:
A Match által visszaadott sorszám mindig a vizsgált tartományra vonatkozik.
Pl. Ha azA1:A100tartományra ad vissza 2-t, akkor az A2 celllában van a találat.
Ha azA2:A100tartományt vizsgálod és ott 2 az eredmény, akkor az A3 cellában van a találat,í mert az a tartomány 2. cellája!
A keresés ismétlésénél így a képleted szerint az A (2+1=3) cellától fog indulni a keresés, aminek az eredménye 1 lesz ---> vagyis a találatod ugyanabban az A3 cellában lesz.
Mivel most a találatsorszama eredménye 1, ezért a képleted alapján a következő keresési tartomány az A (1+1=2) cellától indul. Ez így szép kis végtelen ciklussá alakul át.
Vagyis meg kell jegyezned, hogy az előző találatnál honnan indult a keresési tartomány és abból az értékből kiindulva kell a következő keresési tartomány kezdő értékét beállítani.
Még egy megjegyzés:
Szerintem érdemes megnézned a Range Find metódusát is. Ez megkeresi a feltételnek megfelelő cellát és annak a sorát megkaphatod a Row tulajdonsággal. Jó kis példa is van a folytatólagos keresésre a HELP-ben. [link]
Üdv. -
Fferi50
Topikgazda
Szia!
Kérdés kérdést szül.
"Mindazon sorok, melyek megegyeznek a két fájlban, a hátterük zöldre vált."
Itt gondolom az azonosító egyezőségére gondolsz és a fő fájlban felülíródik az adat. Ezt kellene a fő fájlban zölddel jelölni.
Ha nem talál az aktualizáláshoz egyező azonosítót, akkor marad a fő fájl adata.
Ebben az esetben én egy munkafüzetbe tenném a két munkalapot. A fő munkalapon egy segédoszlopban FKERES függvénnyel megkeresném a mellék fájlban az azonosítót.
Ezután feltételes formázással színezném azokat a sorokat, ahol nem hiba az eredmény.
A következő aktualizálás előtt (mert gondolom lesz ilyen is), a segédoszlop értékét - a hibák kivételéve - átmásolnám a régi oszlopba. Ezután az új értékek kerülnének az aktualizálásról másolt munkalapra.
Üdv. -
Fferi50
Topikgazda
Szia!
Jó az ismétlődések eltávolítása.
Egy üres munkalapra másold fel először a második munkalap adatait, majd az első munkalapét alá. Tehát azt másolod először, amivel felül szeretnéd írni az értékeket.
Kijelölöd az egész adathalmazt. Ezután ismétlődések eltávolítása csak az azonosító oszlopát kijelölve.
Mivel ez a művelet a legelőször talált tételeket hagyja meg, a többször előforduló tételeknél az első marad meg - ezért kell először a felülírásra használandó adatokat bemásolni.
Üdv. -
Fferi50
Topikgazda
válasz
Fferi50
#47588
üzenetére
Botond187-nek a korábbi üzenet folytatása:
Az eredeti adathalmazt alakítsd át táblázattá, az első sor a fejléc.
Ezután a tömbképlet a következő:=INDEX(Táblázat1[[#Mind];[ár]];MAX((Táblázat1[[#Mind];[azon]]=$A$3)*(Táblázat1[[#Mind];[dátum]]<$AH$3)*SOR(Táblázat1[[#Mind];[azon]])))
Táblázat1: az átalakított halmaz neve.
ár: a táblázat árakat tartalmazó oszlopának felirata
azon: a táblázat azonosítókat tartalmazó oszlopának felirata
dátum: a táblázat dátumokat tartalmazó oszlopának felirata
A3 cella tartalmazza az azonosítót
AH3 cella tartalmazza a dátumot, aminél korábbit keresel
A tömbképletet Shift+Ctrl+Enter kombóval kell lezárni, az Excel kapcsos zárójelbe teszi.
Remélem, jól értelmeztem a kérdésed.
Üdv. -
-
Fferi50
Topikgazda
válasz
lenkei83
#47570
üzenetére
Szia!
Ez a függvény nem VBA-ban van megírva szerintem, hanem Visual Studioban. Ha elmész a szerző(k) oldalára https://www.instagram.com/knacksack_official/,fogsz találni rá példát, hogyan csinálta.
A VBA UDF értéket ad vissza és nem tudja módosítani a cellaformázást.
Üdv. -
#47521
Fferi50
Topikgazda
kriszwells
#47518
Fferi50
Topikgazda
válasz
kriszwells
#47518
üzenetére
Szia!
Kimutatást nézted?
Üdv. -
Fferi50
Topikgazda
válasz
-szabi-
#47508
üzenetére
Szia!
1. lépés: Átmásolod az egész oszlopot a másik oszlopba. Ezután Adatok - ismétlődések eltávolítása
2. lépés: Az új oszlop mellé a képlet: =Darabteli(régioszlop;ujoszlopcellája)
Pl. =Darabteli(A:A;C1)
A oszlop a régi, C oszlop az új, a képlet a D1 cellába kerül és húzható lefelé az oszlopon-
Üdv. -
#47507
Fferi50
Topikgazda
Lasersailing
#47505
Fferi50
Topikgazda
válasz
Lasersailing
#47505
üzenetére
Szia!
Nem egészen értem a kérdést.
Mit jelent az, hogy reggel 8-kor betöltjük, majd a mentés du. 2-kor történik meg? Milyen módon töltitek be?
Milyen fájlban tároljátok az adatokat?
Az Excelben nincs nyitva az a fájl reggel 8-tól délután 2-ig?
Milyen módon használjátok az "adatbázis" fájlt, közös használatú?
Elnézést a sok kérdés miatt, de ezeket minimum ismerni kell ahhoz, hogy valamilyen módszert ajánlhassunk a felülírások elkerülésére.
Üdv. -
Fferi50
Topikgazda
válasz
rvn_10
#47458
üzenetére
Szia!
Amit a kép alapján gondolok:
(zárójelben - nem szerencsés az egyesített cellák használata).
Az AA2 cellára tennék adatérvényesítést a megfelelő dátumlistával. Természetesen nem fontos az adatérvényesítés, de akkor lehetnek félreütések is.
INDEX -HOL.VAN függvény kombinációt tennék az AA4-AC4 cellába és lefelé, pl.
AA4 cellába:
=INDEX($B$4:$X$40000;SOR();HOL.VAN($AA$2;$A$2:$X$2;0))
AB4 cellába az oszlopszámhoz +1, AC4 cellába az oszlopszámhoz +2
Ez a képlet megkeresi a B oszlop adott sorához az AA2 cellaértéknek az oszlopában levő értéket. Amennyiben változtatod a dátumot, automatikusan változnak a kapcsolódó értékek is.
Üdv. -
Fferi50
Topikgazda
válasz
rvn_10
#47450
üzenetére
Szia!
Jó lenne látni az FKERES függvényedet is. Nem véletlenül ad HIÁNYZIK# eredményt, nem találja a keresett értéket. A hibát a HAHIBA függvény használatával (HAHIBA(Fkeres...;0)) küszöbölheted ki.
Második kérdésedre a válasz: ha jól értem, a függvény eredményéhez szeretnél megjegyzést fűzni. Ezt szerintem HA függvénnyel tudod megvalósítani pl. =HA(SZUM(D8:X8)>0;SZUM(D8:X8) & " Rendben";"HIBÁS ÉRTÉK")
De használhatsz feltételes formázást is.
Üdv.
Új hozzászólás Aktív témák
- Windows 10 11 Pro Office 19 21 Pro Plus Retail kulcs 1 PC Mac AKCIÓ! Automatikus 0-24
- Kaspersky, BitDefender, Avast és egyéb vírusírtó licencek a legolcsóbban, egyenesen a gyártóktól!
- Vírusirtó, Antivirus, VPN kulcsok GARANCIÁVAL!
- The Elder Scrolls Online Imperial Collector s Edition
- Windows, Office licencek kedvező áron, egyenesen a Microsoft-tól - Automata kézbesítés utalással is!
- Részletfizetés. BankMentes. Kamatmentes. Új noblechairs EPIC műbőr FEKETE - KÉK 3 év garancia!
- Xiaomi Mi 10T Pro 256GB, Kártyafüggetlen, 1 Év Garanciával
- BESZÁMÍTÁS! Gigabyte B650M R5 7600 16GB DDR5 512GB SSD RX 9070 XT 16GB NZXT H7 Flow fehér 750W
- BESZÁMÍTÁS! Gigabyte B660M i5 12400F 32GB DDR4 512GB SSD RTX 3070 8GB Kolink Observatory HF 750W
- Apple iPhone 17 Pro Max Deep Blue 256GB használt újszerű 100% akku (36 ciklus) garancia 2029.01.
Állásajánlatok
Cég: Laptopműhely Bt.
Város: Budapest