- Hamarosan megkezdődik a nubia 2,8K-s táblagépének szállítása
- Barátokká váltak az eddig rivális AI-óriások
- ASUS blog: Ideális olcsó utazós gép lett az új Vivobook S14
- Az Aura Displays hordozható monitorhármasa jól felturbózhatja a produktivitást
- Dual Mode-os IPS monitorral adott magáról életjelet a Gigabyte
- ASUS notebook topic
- Radeon RX 9060 XT: Ezt aztán jól meghúzták
- OLED TV topic
- Hisense LCD és LED TV-k
- HiFi műszaki szemmel - sztereó hangrendszerek
- Vezetékes FEJhallgatók
- AMD K6-III, és minden ami RETRO - Oldschool tuning
- Milyen belső merevlemezt vegyek?
- Azonnali alaplapos kérdések órája
- Raspberry Pi
-
PROHARDVER!
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
-
#48303
Fire/SOUL/CD
félisten
zsolti_20
#48301
-
-
Delila_1
veterán
válasz
zsolti_20
#47251
üzenetére
Egy saját függvénnyel megoldható.
Function Fordit(cella As Range)Dim x As Integer, ford As StringFor x = Len(cella) To 1 Step -1ford = ford & Mid(cella, x, 1)NextFordit = fordEnd FunctionKéplettel hivatkozol a megfordítandó szöveg cellájára: =Fordit(A1)
A füzetet makróbarátként kell elmentened.Van egy egyszerűbb, makrómentes módszer. Az első név mellé billentyűzetről beviszed a fordított szöveget, majd Kezdőlap, Szerkesztés, Kitöltés, Villámkitöltés.
-
Fferi50
Topikgazda
válasz
zsolti_20
#46999
üzenetére
Szia!
Itt egy pár sorral rövedebb makró:Sub splitel()
Dim cl As Range, clstr
For Each cl In ActiveSheet.UsedRange.Columns("A").Cells 'az A oszlop celláin végigmegyünk
clstr = Split(Replace(Replace(cl.Value, ",", "."), " ", "."), ".") 'átcserélünk minden elválasztó pontra és tömbbé alakítjuk az értéket
clstr(UBound(clstr)) = "." & clstr(UBound(clstr)) ' az utolsó tömbérték elé betesszük a pontot
cl.Offset(0, 1).NumberFormat = "@" 'szövegformátumú legyen a célcella
cl.Offset(0, 1).Value = Join(clstr, "") 'a B oszlopba összefűzzük az értéket
Next
ActiveSheet.UsedRange.Columns("B").AutoFit 'a B oszlop szélességét méretre igazítjuk
End Sub
Üdv. -
Delila_1
veterán
válasz
zsolti_20
#46997
üzenetére
A1-től másoltam a lehetséges értékeket, lefelé. A makró a B oszlopba írja az eredményeket.
Szöveg formátumú legyen a B oszlop.Sub Atalakitas()Dim sor As Integer, usor As Integer, b As Integer, szoveg As StringDim valtozo As String, split0 As String, split1 As String, eredmeny As Stringusor = Range("A" & Rows.Count).End(xlUp).RowFor sor = 1 To usorsplit0 = "": split1 = "": valtozo = ""szoveg = Cells(sor, 1)For b = 1 To Len(szoveg)If Mid(szoveg, b, 1) = "." Or Mid(szoveg, b, 1) = "," Thenszoveg = Left(szoveg, b - 1) & " " & Mid(szoveg, b + 1)End IfNextFor b = Len(szoveg) To 1 Step -1If Mid(szoveg, b, 1) = " " Thenvaltozo = "." & Mid(szoveg, b + 1) & ""szoveg = Left(szoveg, b - 1)Exit ForEnd IfNextOn Error Resume Nextsplit0 = Left(szoveg, InStr(szoveg, " ") - 1)split1 = Mid(szoveg, InStr(szoveg, " ") + 1)eredmeny = split0 & split1 & valtozoCells(sor, 2) = eredmenyNextEnd Sub -
Fferi50
Topikgazda
válasz
zsolti_20
#46819
üzenetére
Szia!
Háát, nem igazán tudom, mit is gondoljak.
Milyen Exceled van? Milyen Word? A PDF milyen nyelvű? Tudnál legalább képet mutatni?
A 689.700 - ról amit írsz, arra enged következtetni, hogy angol Excel és pont az ezres tagoló.
A 1,305.920 mint általános formátum arra utal, hogy azt szövegként értelmezte az Excel valószínűleg. Ha számolni szeretnél vele, akkor mi történik?
Viszont a 34.62 további kérdéseket vet fel, mert itt viszont tizedes pontnak tűnik a pont...
Szóval nem kicsi a rejtély.
Ámbátor ez a PDF-ből Word-ből Excelbe egy elég kacifántos megmozdulás.
Ha tennél fel példa fájlt (PDF-et), amiből kiindul az egész miskulancia, akkor talán több kiderülne.
Üdv.
(Most néztem Poirot a TV-ben, lehet, őt kellene megbízni az ügy kibogozásával...
) -
Fferi50
Topikgazda
válasz
zsolti_20
#46773
üzenetére
Szia!
Akkor ez ezreselválasztó (ThousandsSeparator) - az angolban. A cellaformázásban tudod megadni, hogy ne vessző, hanem pont legyen az elválasztó, illetve Általában is átírhatod a beállításoknál az ezreselválasztó jelet.
De: Nem világos, hogy a cella értéke 322650 mint szám, vagy 322,650 mint szöveg. Ha az első, akkor a fent írtak használhatók szerintem. Ha a második, akkor megy a szövegfüggvény.Üdv.
-
Pakliman
tag
válasz
zsolti_20
#46768
üzenetére
Szia!
A csatolt kép alapján (feltételezve, hogy azon nincs semmilyen formázás!) az egy számokból álló szöveg (mivel a cella bal oldalán van).
A vessző az az angol területi beállítás szerinti ezres elválasztó. Pontra cserélni csak úgy tudod, ha elosztod 1000-el vagy szövegként kezeled és BAL/JOBB függvényekkel "kettévágod" és középre teszel egy pontot.
Pl.: =IF(A1 <> "",AJ5/1000,"") vagy =IF(A1 <> "",LEFT(AJ5,3) & "." & RIGHT(AJ5,3),"") -
#45744
Fire/SOUL/CD
félisten
zsolti_20
#45743
-
#45742
Fire/SOUL/CD
félisten
zsolti_20
#45741
-
Mutt
senior tag
válasz
zsolti_20
#45297
üzenetére
Szia,
A Connection only visszatöltés során az eredmény nem fog megjelenni egyik lapon sem, de az eredmény használható további lekérdezésekben, kimutatásokban. Az ilyen (és minden más) betöltés továbbra is szerkeszthető marad. Adatok (Data) fülön a Lekérdezések és kapcsolatok (Queries and Connections) gombot használva láthatód az összes lekérdezést és ott jobb klikkel tudod szerkesztésre megnyitni.
üdv
-
Magnat
veterán
válasz
zsolti_20
#45300
üzenetére
Szia
csinálsz egy formot (Userform1), rá egy Listboxot (Listbox1), aztán:
Sub Popup()Dim cel As RangeDim selectedRange As RangeSet selectedRange = Application.SelectionUserForm1.ListBox1.ClearFor Each cel In selectedRange.CellsUserForm1.ListBox1.AddItem (cel.Value2)Next celUserForm1.ShowEnd SubEz a szelektált cellák tartalmát fogja bedobni a Listbox1-be.
Szerk.: Ha legördülő kell, akkor Listbox helyett Combobox kell.
-
Delila_1
veterán
válasz
zsolti_20
#45232
üzenetére
Erre már válaszoltam. A képlet
=JOBB(Munka1!A2;HOSSZ(Munka1!A2)-6)*1Az előző válaszhoz képest annyi a változás, hogy akkor azonos lapon volt a szöveg+szám, itt másikra kell a képlet. Még az is változott, hogy ott csoport szerepelt, itt meg group, ahol a karakterek száma eltér.
-
Mutt
senior tag
válasz
zsolti_20
#45217
üzenetére
Szia,
Power Query-s megoldás VBA-mentesen.
1. Alakítsd át a listát és a szűrőt 2 táblázattá.
2. Ezek után Adatok -> Táblázatból vagy tartományból opcióval töltsd be Power Query-be az egyik táblázatot.
3. Kezdőlap -> Bezárás és betöltés -> ... adott helyre majd válaszd a csak kapcsolatot.
4. 2-3-as lépést csináld meg a szűrővel
5. Kapcsolatok alatt jobb klikk a lista lekérdezésen és Szerkesztéssel menjünk vissza a Power Query szerkesztőbe.
6. Most számoljuk meg hogy hány eleme van az egyes csoportoknak. Ehhez a Kezdőlapon a Csoportosítási szempontot használd. Itt elég a sorokat megszámolni, de ha nem bánod akkor haladó üzemmódban inkább ezt állítsd be:
7. Az Elemek egy beágyazott tábla, ami tartalmazza az eredeti listádat és ebből ki fogjuk nyerni a tételeket.
(Természetesen lehet ezt sima lekérdezés egyesítéssel helyettesíteni, ha a hagyományos utat választanád.) Adj egy új oszlopot a táblázathoz, aminek ez legyen a képlete: =[Elemek][Tetel]
8. A Tetel oszlop jobb felső sarkában lévő ikonra kattints és válaszd a kibontást új sorokba. Ezzel visszakaptuk az eredeti listát, csak most már tudjuk hogy hány elem van egy csoportban.
9. Jöhet a Kezdőlap -> Lekérdezések egyesítése. Itt kiválasztjük a szűrő lekérdezést és a közös oszlopot kijelöljük. Alul javasolt a Belső (egyező sorok) opciót használni.
10. Most megint meg kell számolnunk, hogy egyes csoportokban hány elem van. Hasonlót csinálunk mint a 6-os lépésben.
11. Megint lett egy beágyazott táblánk, amiből megint kellenek a tételek, de nyerjük ki még az eredeti elemszámot is. A tételeket ugyanúgy mint a 7-es lépésben egy új oszloppal kapjuk meg.
12. Az eredeti elemszámhoz szintén egy új oszlop kell, aminek a képlete ez: =List.Min([Tetelek][Elemszám])
13. Már látszik, hogy nekünk csak azok a sorok kellenek, ahol az eredeti és az új elemszám azonos. Ehhez megint kell egy új oszlop. Képlete: =[Elemszám]=[EredetiElemszám]
14. Az új oszlopban szűrjünk rá a TRUE sorokra.
15. Töröljük a felesleges oszlopokat (csak a csoport és a tétel kell)
16. Tétel oszlop jobb felső sarkában megint kibontás sorokba.
17. Eredmény visszatöltése Excelbe, Kezdőlap -> Bezárás és betöltés.
18. Jobb klikk megint a Lista lekérdezésen és válasszuk a Betöltés helyét, majd Táblázat.Ez lett a végeredmény.
üdv
Ps. VBA-val limitáltan lehet módosítani a Power Query-t.
-
Delila_1
veterán
-
-
Delila_1
veterán
válasz
zsolti_20
#45192
üzenetére
Nem volt egyszerű, de végül sikerült.
Sub Eredmeny()
Dim sorSzuro As Integer, sorEredm As Integer, db As Integer, csoport As Integer
Dim usorLista, x As Integer, elso As Integer, ucso As Integer, nev As String, van
sorEredm = 2: csoport = 1
usorLista = Range("D" & Rows.Count).End(xlUp).Row
Kezd:
elso = Application.Match(csoport, Columns(1), 0)
ucso = Application.Match(csoport, Columns(1))
db = 0
For x = elso To ucso
If Application.WorksheetFunction.CountIf(Columns(4), Cells(x, 2)) > 0 Then db = db + 1
If db = ucso - elso + 1 Then
For sorSzuro = 2 To usorLista
nev = Cells(sorSzuro, "D")
On Error Resume Next
van = Application.Match(nev, Range(Cells(elso, "B"), Cells(ucso, "B")), 0)
If VarType(van) = vbError Then
On Error GoTo 0
Else
Cells(sorEredm, "F") = csoport
Cells(sorEredm, "G") = nev
sorEredm = sorEredm + 1
End If
Next
End If
Next
csoport = csoport + 1
If csoport > Application.WorksheetFunction.Max(Columns(1)) Then
Exit Sub
Else
GoTo Kezd
End If
End Sub -
#45193
the radish
senior tag
zsolti_20
#45191
the radish
senior tag
válasz
zsolti_20
#45191
üzenetére
Köszi, de nem látni akarom, hanem azt szeretném, ha a meglévő makró a makró
Public Const burnKey = "A_qTUOS_aAN-a6uUA!jER%rZE"
sorában elhelyezett kód helyett a a munkafüzet "KEY" munkalapjának "A1" cellájában elhelyezett kódot használná.Azaz valahogy így kéne:
Public Const burnKey = " Sheets("KEY").Range("A1") " -
Fferi50
Topikgazda
-
Delila_1
veterán
válasz
zsolti_20
#45008
üzenetére
A válasznál is írhattam volna. A megnyitott füzet lesz aktív, ennyit kell tudni hozzá.
Látod, nem váltok ide-oda a két füzet között, hanem másoláskor megadom, hogy melyik füzetből melyikbe másolok.
Ha mindenestől akarod másolni egyik füzetből a másikba a két tartományt, elég ez a két sor:ShInnen.Range("H4").Copy ShIde.Range("C339")
ShInnen.Range("J5:K20").Copy ShIde.Range("A342")Ha viszont PasteSpecial-lal másolsz, akkor új sorban kell megadnod a beillesztés helyét, ahogy írtam.
-
Delila_1
veterán
válasz
zsolti_20
#44998
üzenetére
Azt a füzetet, ahonnan másolni akarsz, Zsolti_Innen.xlsx-nek neveztem.
A Const utvonal = "F:\Eadat\Excel fórumok\PH\" sorban írd át a megadott értéket a saját útvonaladra, majd indíthatod a makrót. Ez a makró abban a füzetben legyen, ahova másolni akarsz.Sub Zsolti()
Dim ShInnen As Worksheet, ShIde As Worksheet
Const utvonal = "F:\Eadat\Excel fórumok\PH\"
Set ShIde = ActiveWorkbook.Sheets("Transactions")
Workbooks.Open utvonal & "Zsolti_innen.xlsx"
Set ShInnen = ActiveWorkbook.Sheets(6)
ShInnen.Range("H4").Copy
ShIde.Range("C339").PasteSpecial xlPasteValues
ShInnen.Range("J5:K20").Copy
ShIde.Range("A342").PasteSpecial xlPasteValues
Workbooks("Zsolti_innen.xlsx").Close False
End Sub -
Delila_1
veterán
válasz
zsolti_20
#44996
üzenetére
Ha jól látom, megnyitsz egy fájlt (sFileName), majd a 6. lapjának a H4 celláját bemásolod az eredetileg megnyitott füzet Transactions nevű lapjának a C339 cellájába. Utána (felesleges zárás-új nyitás után) a megnyitott füzet 6. lapján másolod a
J5:K20tartományt, amit az első füzet Transactions lapon az A342 cellába illeszted be.
Ennyi? -
Delila_1
veterán
válasz
zsolti_20
#44992
üzenetére
Ha PasteSpecial-t alkalmazol, azt is meg kell adnod, hogy mit illesszen be. Értéket, formátumot, mindent, oszlopszélességet, stb.
Érték beillesztésnélSh.Range("A204").PasteSpecial xlPasteValuesLátod, a hely meghatározása CSAK Range("A204"). Mivel 1 celláról van szó, nem kell tól-ig tartományt megadnod.
-
Delila_1
veterán
-
Delila_1
veterán
válasz
zsolti_20
#44976
üzenetére
A különböző területek együttes kijelölése a VBA-ban
xlBook.Sheets(6).Range("J5:K20, H4").Selectformában lehetséges, de másolni csak két lépésben tudsz.xlBook.Sheets(6).Range("J5:K20").Copy Sh.Range("A342")
xlBook.Sheets(6).Range("H4").Copy Sh.Range("A342")A beillesztésnél nincs szükség az Sh aktiválására, amint a fenti 2 sorban láthatod.
Annak mi értelme, hogy az Sh.Range("A342") helyre bemásolod előbb a 15 cellás tartományt, majd a H4 cellával felülírod? -
Fferi50
Topikgazda
válasz
zsolti_20
#44893
üzenetére
Szia!
Szerintem 2 malomban őrölünk. A képlet akkor is működik, ha 2, akkor is ha 12 a sorok száma. A lényeg, hogy az altáblák első oszlopa legyen egyenlő a főtábla első oszlopával, valamit az első sorok tartalmazzák a számokat.
Csak mindig a tartományokhoz kell igazítanod a legelsőt és utána húzhatod ameddig szükséges.
Ha felteszel egy mintafájlt két munkalappal, különböző kiindulási táblákkal, akkor szívesen megmutatom, hogyan gondoltam.
Üdv. -
Fferi50
Topikgazda
válasz
zsolti_20
#44887
üzenetére
Szia!
Bocs, de mégis azt mondom, hogy próbáld meg a következőképpen:
A mindenkori oldalrovatot másold át mindkét altáblához.
Ha az 1-12 és a bontás fix, akkor a mutatott képlet továbbra is jó lesz, csak a főtábla sorainak a számát kell megváltoztatnod (az INDEX első paraméterének az utolsó száma, illetve az első HOL.VAN függvény második paraméterének az utolsó értéke. -- $A$1:$M$12 ill. $A$1:$A$12 -- az eredeti mintában, a 12-t kell átírnod a tényleges hosszra.)
Üdv. -
Fferi50
Topikgazda
válasz
zsolti_20
#44882
üzenetére
Szia!
A két szétválasztó tábládat alapból ugyanolyan oldalrovattal kell megnyitni, mint amilyen a főtábládban van.
Ezután az INDEX - HOL.VAN párossal meg tudod keresni az odavaló adatot.
=INDEX(Főtábla, HOL.VAN(altábla második sor első cella (pl. a),Főtábla első oszlop,0),HOL.VAN(altábla.első sor második cella,Főtábla első sor,0))
Pl.
A képlet húzható lefelé és oldalt is.
Üdv. -
Fferi50
Topikgazda
válasz
zsolti_20
#44849
üzenetére
Szia!
Szerintem mégis egyszerűbb lesz, ha megpróbálod némi munkával a lehúzást. Erre gondoltam:
A $CJ$20 -$21-ben a sorszám elől kiveszed a $ jelet. Így ha húzod lefelé a képletet, akkor növekszik a sorszám is.
Az A9 cellára lehúzott képletben a Keres-cserél funkcióval kicseréled a CJ29-et CJ20-ra, a CJ30-at pedig CJ21-re. Ott már I178 vagy ilyesmi lesz, azt cseréled J172-re. stb.
Lehúzod A17-ig. Majd megismétled a cseréket.
Elismerem, hogy ez nem feltétlenül a leggyorsabb megoldás. DE az Indirekt vagy Eltolás függvények megfelelő paramétereinek a kiszámolgatása is időigényes. Figyelembe kell venni az ismétlődési periódust mind a soroknál, mind az oszlopoknál.
Üdv. -
Delila_1
veterán
-
Fferi50
Topikgazda
válasz
zsolti_20
#44631
üzenetére
Szia!
Csak ötlet: Lekérdezés után kitörlöd a fájlt. Ezután Dir() paranccsal ellenőrzöd, hogy létrejött-e már, a létrehozás időpontját pedig a FileDateTime paranccsal tudod megnézni. (Ez sajnos csak az eredeti időpontot mutatja, a módosításét nem, ezért kell kitörölni.)
Üdv. -
Delila_1
veterán
válasz
zsolti_20
#44578
üzenetére
Nem volt pontos a leírás a 2. feladatnál. Ha az első lapról azokat a sorokat kell törölnöd, ahol az A oszlop értéke megtalálható a második lap A oszlopában, akkor a lenti makró megoldja.
Sub KettesFeladat()
Dim sor As Long, usor As Long
usor = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
For sor = usor To 1 Step -1
If Application.WorksheetFunction.CountIf(Sheets("Sheet2").Columns(1), Sheets("Sheet1").Cells(sor, 1)) > 0 Then
Sheets("Sheet1").Rows(sor).Delete
End If
Next
End Sub -
eszgé100
őstag
válasz
zsolti_20
#44578
üzenetére
Szia, csak az elsőre van ötletem, mert épp most csinálok hasonlót
Public Sub CopyThenDelete()Workbooks.Open "Z:\Excel\egyik.xlsm"Workbooks("egyik.xlsm").Worksheets("Sheet2").Range("A1:A10").copy Workbooks("másik.xlsm").Worksheets("Sheet4").Range("B2")Workbooks("egyik.xlsm").Close SaveChanges:=FalseKill "Z:\Excel\egyik.xlsm"End Suba makrót a másik.xlsm-ből kell indítani. Workbooks.open... sor nem kell, ha már alapból is nyitva van az egyik.xlsm munkafüzet.
Kill-el vigyázz, mert véglegesen töröl, nem a lomtárba. Biztonság kedvéért csinálj biztonsági mentéseket az érintett fájlokról -
Fferi50
Topikgazda
válasz
zsolti_20
#44318
üzenetére
Szia!
Egy kis "csavarral" az INDIREKT függvény elkerülhető.
Lépések:
1. A névkezelőben a kecske munkalap A1:A10 tartományát elnevezed így: kecske
A kutya munkalap A1:A32 tartományát elnevezed így: kutya.
2. Ezután a képlet a B1 cellába:=HAHIBA(INDEX(VÁLASZT(HOL.VAN($A$1;{"kecske";"kutya"};0);kecske;kutya);SOR());"")
Ezt a képletet húzhatod lefelé a B oszlopban.
Ameddig van adat, addig kiírja az oszlopban, az adatok elfogytával "üres" lesz a cella.
(Nagyobb tartományt is adhatsz a nevekhez, hátha szeretnél még írni bele több adatot.)
Üdv. -
Mutt
senior tag
válasz
zsolti_20
#44128
üzenetére
Szia,
A körlevél készítő újabb lapot fog létrehozni a többi címnek a sablon szerint. Vagyis ha 14-nél több címed van, akkor egy újabb lapon folytatja újabb 14 címmel és így tovább.
A kész eredményt nyomtatás előtt le tudod ellenőrízni, hogy legyen lehetőség javításra. Ne a nyomtatást válaszd, hanem az új dokumentum késztését. Ha az jó akkor küldheted azt nyomtatásra.
üdv
-
Mutt
senior tag
válasz
zsolti_20
#43699
üzenetére
Szia,
Ha a lent látható eredmény tetszik, akkor Power Query-ben ezek a lépések kellenek.
1. Adatsort alakítsuk táblázattá, én az "orders" nevet adtam neki.
2. Adatsoron állva Adatok -> Adatok beolvasása -> Táblázatból/tartományból megnyitja a Power Query szerkesztőt.
3. Location oszlopon állva használjuk a növekvő sorbarendezést.
4. Kezdőlap -> Csoportosítási szempontokkal csináljunk egy ilyet.
5. Egy új egyéni oszlopot adjunk hozzá, hogy lássuk melyik rendelés mely helyeken található.
6. Egy másik egyéni oszlopban megszámoljuk, hogy rendelések hány helyre oszlanak el.
7. Ezen elemszám alapján csökkenő sorrendbe rendezzük a táblázatot, ezzel a végső eredményünkben előrébb lesznek azok a rendelések amelyek több helyen vannak.
8. A location-ben lévő listát ki kell nyernünk (én hülye módon elemek nevet adtam ennek az oszlopnak de a lényeg, hogy itt a helyek vannak).
Az eredmény vesszővel elválasztva ílyen lesz:
9. Most megint csoportosítunk, hogy az azonos helyen lévő rendeléseket tudjuk kezelni.
10. Megkapjuk, hogy hány csoportra oszthatók a rendelések. A következő két lépésben nevet adunk a csoportoknak. Előbb az Oszlop hozzáadása -> Indexoszlop -> 1-től indul opciót használjuk.
11. Majd egy új egyéni oszloppal eléírjuk, hogy "Group ". Itt annyi kavar van, hogy számot szöveggé kell alakítani.
12. Jobb klikk ezen az oszlopon és mozgassuk legelőre.
13. Van két felesleges oszlopunk (az index és a location-t tartalmazó), ezeket töröld.
14. Kell egy újabb egyéni oszlop a rendelési számok duplikáció mentes megjelenítéséhez.
15. Itt megint vesszővel elválasztva írassuk ki az értékeket.
16. Opcionális lépés: ahhoz hogy a rendelési számok sorba legyenek a szerkesztőlécen a képletbe List.Sort() függvényt kell beírni (a sárga rész).
17. Az elemszám oszlop jobb sarkába kibontés az Adatok-at válaszd.
18. Az adatok oszlop jobb sarkában megint kibontás és most SKU, qty, location oszlopok kellenek.
19. Most már csak összegezni kell a mennyiségeket. Jelöld ki a qty kivételével az oszlopokat és használd a csoportosítást.
20. Rendezd az oszlopok sorrendjét tetszés szerint.
21. Lehet a sorokat is rendezni tetszés szerint.
22. Kezdőlapon betöltés adott helyre az utolsó lépés.üdv
-
Fferi50
Topikgazda
válasz
zsolti_20
#43714
üzenetére
Szia!
Kell hozzá még 2 segédoszlop
Az elsőbe átmásolod a szállítás oszlopot - értékként fejlécestől.
Ezután Adatok - ismétlődések eltávolítása.
Ekkor megkapod az egyedi csomagokat.
Második segédoszlop 2. cella képlete (az első segéd nekem a P oszlop volt):=DARABTELI(Táblázat2[szállítás];P2)/(HOSSZ(P2)-HOSSZ(HELYETTE(P2;",";""))+1)
(az elején úgy jött a hivatkozás, hogy egérrel jelöltem ki a tartományt)
Ezt a képletet végighúzod. Megkapod, hogy az adott összetételű csomagból hány db van.
Ezt rendezheted nagyság szerint. Ezután a szűrődet a kimutatásban eszerint állítgatod.
Üdv. -
Fferi50
Topikgazda
válasz
zsolti_20
#43708
üzenetére
Szia!
Arra gondoltál, hogy az 1 tételes csomagoknál az egyes azonos tételeket kellene összesíteni?
Hány VGA, CPU, stb. ... csomag van?
Mert akkor csak annyit kell tenned, hogy bejelölöd a több elem kiválasztását a szűrőben. Kiválasztod az egy tételes szűrőket és máris látod, melyikből hány van.
De a kimutatásba is beveheted a csomagok darabszámát így:
A nevet az értékmező beállításai jobb egérgombos menüpontban változtathatod meg
Az eredmény pl.
Itt CPU és VGA egyedi megrendelések vannak. A csomag db összesen mutatja, melyikből hányat kell összeállítani.
Üdv.
-
Fferi50
Topikgazda
válasz
zsolti_20
#43702
üzenetére
Szia!
Akkor próbáld ki a következőket, kell hozzá két segédoszlop.
1. lépés: Alakítsd át táblázattá az adataidat
Rendezd az adataidat az alábbiak szerint:
1. szint order number legkisebbtől-legnagyobbig
2. szint SKU A-Z
2. lépés: Az első segédoszlop (E oszlop) fejléce (nálam szállítmány tartalma)
E2 cella képlete=HA(A1=A2;HA(A2<>A3; ";" & E1&","&B2;E1&","&B2);HA(A2<>A3; ";" & B2;B2))
Ez lehúzható az oszlopon, illetve a táblázat esetén automatikusan kitöltődhet.
Eredménye: Az egyes rendelésekhez tartozó tételek egymás mellé írása egy cellában, az utolsó rendelési tételnél a cella értéke ; (pontosvesszővel) kezdődik. Ennek jelentősége a második segédoszlopnál lesz.
3. lépés: A második segédoszlop (F oszlop) fejléce (nálam szállítás)
F2 cella képlete=INDEX(E$1:E$500; HOL.VAN("*;*";E2:E$500;0)+SOR()-1)
Ez is lehúzható az oszlopon vagy automatikusan kitöltődik.
Így a rendelések mellett minden sorban megtalálható a teljes rendelési sorozat.
4. lépés: Kijelölöd az A1 cellát. Beszúrás - kimutatás - eldöntöd, hogy új munkalapon vagy a meglevő munkalapon (ekkor válasz egy cellát a kimutatás helyének).
A mezőket a következőképpen válaszd ki:
order number -> sorok
SKU -> oszlopok
qty -> értékek - összeg
szállítás -> szűrők
Így néz ki:
Ha "eltünteted" a Kimutatásmezők párbeszédlapot, akkor megkapod a kimutatást:
Itt a kulcs a szállítás mező, ami szűrőként szolgál. A lenyílóval kiválaszthatod, hogy mely tételeket szeretnéd látni:
A RAM,GPU,VGA kombinációt választva ezt látod:
Az első tételt kiválasztva pedig ezt:
A megoldásnak működnie kell szerintem sok csomag és sok tétel esetén is. Amennyiben új csomagot vagy tételt adsz hozzá, a táblázat automatikusan bővül, mindössze a kimutatást kell frissítened és a szűrőket váltogatnod.
Megcsinálni rövidebb idő, mint ezt az egészet elolvasni. Üdv.
-
Fferi50
Topikgazda
válasz
zsolti_20
#43699
üzenetére
Szia!
Nem PowerQuery és gondolom hosszabb is annál.
1. lépés: Rendeld a 2 hatványait az egyes alkatrészekhez (Pl. CPU 1, RAM 2,VGA 4, GPU 8,..stb). Ez arra lesz jó, hogy a csomagok tartalmának egyezőségét vizsgáljuk. Csak akkor lesz a csomagok értéke egyforma, ha azonos alkatrészek vannak benne.
Hasznos az is, ha táblázattá alakítod az adatokat.
Ezután csinálj egy kimutatást: sorcimkék => order number
oszlopcimkék => SKU
értékek => összeg qty és összeg pontszám.
A pontszám végösszeget tartalmazó oszlop első celláját kijelölöd, jobb egérgomb - rendezés - rendezés csökkenő sorrendben (vagy növekvő sorrendben, ahogy tetszik).
Eredmény:
Az azonos pontszámú csomagok így egymás alá kerülnek. Ha az azonos pontszámú csomagokat kijelölöd, akkor a Kimutatáseszközök Elemzés csoportban a kijelöltek csoportosítása menüponttal csoportba foglalódnak. Majd a Kimutatáseszközök Tervezés csoportban a Részösszegek - Minden részösszeg megjelenítése a csoport alatt ponttal össze is adja az értékeket.
Üdv.
-
Mutt
senior tag
válasz
zsolti_20
#43541
üzenetére
Szia,
Ahhoz hogy kizárjuk azokat a csomagokat, amelyek olyan tételeket is tartalmaznak amelyek minket nem érdekelnek a korábban megadott lépéseknél a 9-estől kezdődően kell változtatnunk.
1-8. lépések a 43537-es komment alapján
9. A kapcsolat típusa Left Outer legyen.
10. Ezek után kell egy Add Column -> Custom column, aminek ez a képlete:
=List.Count([parts][Column1])
Az új "Matchings" column azt fogja majd egy összegzés után megadni, hogy a csomagban hány olyan tétel van ami érdekel minket.
11. Jöhet az összegzés a Home->Group by-al
Ahogy láthatod a második összesítő függvénnyel megnézzük hogy hány tétel van a csomagban amire szükséged van. A harmadik függvény pedig megmondja, hogy összesen hány fajta tétel volt a csomagban.
Ahol a kettő nem lesz azonos azok a csomagok nem kellenek. Ez lesz a következő lépés.12. Add column->Custom -ot használjuk megintm a képlet ennyi:
=[Matches]=[TotalLines]
13. Ahol az új oszlopban TRUE van azok kellenek. Oszlop jobb felső sarkában lévő szűrővel állítsuk be ezt.
14. Ha nem kellenek az alkatrészek nevei a csomagból felsorolva, akkor ugord át ezt a lépést.
Add column -> Custom -ot használjuk megint, a képlet pedig:=[Count][SKU]
A kapott oszlop jobb sarkába kattintva Extract values és válassz egy elválasztó jelet.
15. Ahhoz hogy megkapjuk a mennyiségeket pedig megint egy új oszlop kell, aminek ez lesz a képlete:
=List.Sum([Count][QTY])
16. Törlöd a felesleges oszlopokat a riportból és Home->Close&Load->Close&Load to.. -val betöltöd Excelbe.
üdv
-
Fferi50
Topikgazda
válasz
zsolti_20
#43541
üzenetére
Szia!
Tekintsünk el a tábládban levő egyéb oszlopoktól, maradjon csak a 3 oszlop, (Order No, SKU, Qty). A többi törölhető.
Próbáld meg a következőket:
1. lépés: A D2 cellába írd be ezt a képletet:=DARABTELI(A:A;A2)
és húzd végig az oszlopon lefelé. A D oszlop első cellájába írd mondjuk: Tételszám.
Erre azért van szükség, hogy azokat a csomagokat megtaláljuk, amelyben a keresett SKU egyedül fordul elő.
Tegyél automatikus szűrőt erre a 4 oszlopra.
A)Ha tehát egy SKU tételt keresel, akkor
a) az SKU-szűröd a tételre
b) a Tételszámot szűröd 1 -re.
Ezzel megkapod azokat a csomagokat, amelyeknél az adott SKU egyedül van.
B) Ha több SKU tételt keresel, akkor kell még egy kis munka, de csak elsőre:
Be kell szúrnod egy kimutatást, én egy másik oldalra szúrtam be. Lépések:
a) Kijelölöd az A1 cellát
b) Beszúrás - Kimutatás - Új munkalapra (vagy az adott munkalapra egy cellába is lehet)
c) A kimutatás mezőlistából az order number megy a sorokhoz
d) az SKU megy az oszlopokhoz
e) a Qty pedig az értékekhez (Összeg/Qty)
A kimutatás változásait már a lépések során is láthatod.
Ezután a mezőlistát becsukhatod (x a jobb sarokban).
A menüszalagon aktív a Kimutatáseszközök fül. Válaszd az Elemzést.
Itt pedig a szűrők menüsorokból a szeletelő beszúrása menüpontot. A szeletelő az SKU-ra legyen. Húzd olyan helyre, hogy lásd a kimutatást és a szeletelőt is.
Az előkészítés eddig tartott! (Tovább tartott leírni, mint megcsinálni
)
Ezután nincs más dolgod, mint a szeletelőből kiszedni azokat az SKU-kat, amelyeket nem szeretnél látni. Maradnak, amit látni akarsz. A kimutatás folyamatosan mutatja az eredményt.
Ha változik az SKU követelmény, akkor átállítod a szeletelőket.
Figyelem! Ez a módszer csak akkor működik, ha több tételt keresel! Ha csak egy SKU kell, akkor az A pontban leírt szűrőmódszer a megfelelő.
Íme a végeredmény:
Ha táblázattá alakítod az első 4 oszlopot, akkor automatikusan bővülni fog a kimutatásod is, csak a frissítés gombra kell rányomni az új sorok beírása után.
Üdv.
-
Mutt
senior tag
válasz
zsolti_20
#43529
üzenetére
Szia,
Ha Excel 2010 vagy újabbad van akkor van Power Query, amivel megoldható a dolog.
(Excel 2010-hez külön kell telepíteni az MS oldaláról.)Lépések:
1. Az első munkalapon a rendelés listát táblázattá alakítottam (előnye, hogy a mérete automatikusan változik amikor bemásolod a csomag adatokat). Én ezt a táblát "orders"-nek neveztem el, de nincs jelentősége.
2. A második munkalapon az A1 celléba beírtam a vesszővel felsorolt alkatrész listá és a cellának adtam egy nevet ("parts").
3. Maradj ezen a cellán és Data fülön "From table/range" (magyarul Adatok->Táblázatból/tartományból) opcióval megfog nyilni a Power Query szerkesztő.
4. Jobb oldalt az Applied Steps (Alkalamazott lépések) alatt lesznek felesleges lépések, töröld ki őket, csak a Source maradjon.
5. Transform -> Split column -> By delimiter-t (Átalakítás->Oszlop felosztása->Elválasztó alapján) indítsd el
6.Állítsd be, hogy vessző alapján szedje szét az adatokat és hogy sorokba tegye őket.
7. Home -> Close & Load -> Close & Load to .. (Kezdőlap->Bezárás&betöltés->..adott helyre) parancsot használd és a megjelenő ablakbak az Only create connection-t (Csak kapcsolat létrehozása).
8. Menj át az első lapra, ahol a csomag adatok vannak és állj rá az adatsorra, majd Data -> From table/range-t (Adatok->Táblázatból/tartományból) indítsd.
9. Home -> Merge queries -> Merge queries (Kezdőlap->Lekérdezések egyesítése) parancsot indítsd. Ez fel fog dobni egy ablakot, ahol látni fogod a csomagok listáját, alatt lesz egy legördülő listából válaszd ki a korábbi lépésekben létrehozott listát. A két listában a közös elem az alkatrészek, ezeket az oszlopokat jelöld ki. Majd alul a kapcsolat típusánál Inner Join (Belső) legyen. Ezt kellene látnod.
OK-t nyomva megkapod az első lapon lévő listából azokoat a sorokat, amelyek a második lapon szerepelnek.Már csak ki kell venni a duplikációkat.
10. Jelöld ki az "order number" oszlopot és Home -> Group by-t (Kezdőlap->Csoportosítási szempont) indítsd el. Majd állítsd be így.
A Count - All Rows páros csak akkor kell, ha érdekel a következő lépés amiben kiíratjuk hogy az adott csomagból téged mely alkatrészek érdekelnek.
11. Add column -> Custom column-t (Oszlop hozzáadása->Egyéni oszlop) indítsd el és töltsd ki így:
A képlet
=[Count][SKU]azt mondja meg a Power Querynek, hogy a Count nevezetű táblából (ezt az előző lépéssel hoztuk létre az SKU fejlécű oszlop adatati írja be az új oszlopba).12. Az újonnan létrehozott oszlopban jobb felső sarkában lévő gombra kattints és használd az Extract Values-t (Értékek kinyerése), majd válaszd ki hogy mi legyen az elválasztó elem a felsorolások között.
13. Töröld a Count oszlopot.
14. Home -> Close & Load -> Close & Load to .. jön megint, de most válaszd a Table opciót és add meg hogy hova töltödjön a végeredmény.
A sok lépés azért volt, mert ha módosítod bármelyik adatsort, akkor elég csak a Data -> Refresh all (Adatok->Összes frissítése) gombra kattintanod és előáll az új listád makrómentesen.
üdv
-
Fferi50
Topikgazda
válasz
zsolti_20
#43524
üzenetére
Szia!
Akkor ugye nem törölni kell a listából a nem megfelelő elemeket, hanem egy másik lapra kellene kiírni azokat, amelyek megfelelnek a keresési feltételnek?
(Hiszen ha töröljük az elemeket, akkor később hogyan használod újra a listát....)
Ha pl. 414,416,419 van megadva, akkor kell az a csomag, amiben mind a három van és kell az is amiben csak az egyik (akármelyik a 3 közül) van egyedül?
Ezt a három (kettő, vagy több) értéket egy cellába írnád be vagy többe egymás alá/mellé?
A "keresőlapon" megjelenhetnek a szűrővel kiválasztott tételek, vagy a listalapon érvényesüljön a szűrő?
Üdv. -
Fferi50
Topikgazda
válasz
zsolti_20
#43522
üzenetére
Szia!
Mindent lehet, csak világos szabályok kellenek hozzá.
Én pl. nem értem, ha a 414 van megadva, akkor miért csak a 20.sor marad? Honnan lehet tudni, hogy a 8 marad, az 1 pedig nem? Nem látok én másik 2 értéket az 1. sorban.....
A második példád is sántít eléggé, hiszen az első 3 sorban mindegyik érték közül szerepel egy-egy.
Szóval, mi is a szabály?
Üdv. -
Delila_1
veterán
válasz
zsolti_20
#43114
üzenetére
Az AA oszlopba sorold fel a fájlneveket, kiterjesztéssel.
A makró sorra megnyitja a fájlokat. Első esetben a B oszlopba írja be a képleteket, amik az első fájlból keresik ki az értékeket.
Ahhoz, hogy a következő fájlnál ne írja ezeket felül, a másodikban való kereséshez a képletek a C oszlopba kerülnek, és így tovább. Nézd meg a Case utasításoknál, hogy mi változik.
Az első sorában az oszlop helye (B: B), a másodikban a félkövér, aláhúzott karakterrel írt érték.
"=IFERROR(VLOOKUP(RC[-1],[" & FN & "]Munka1!C1: C2,2,0),"""")"
A Case 5-től majd beírod a többit.Sub Kigyujtes()
Dim WsGy As Worksheet, WsInnen As Worksheet, usorGy As Long, szamlalo As Integer
Dim FN As String, sor As Long, usorFajlnev As Long, utvonal As String
utvonal = "F:\Főmappa\Almappa\" 'ide jön a saját útvonalad, a végén \ legyen
Set WsGy = ActiveWorkbook.Sheets(1)
usorGy = WsGy.Range("A" & Rows.Count).End(xlUp).Row
usorFajlnev = WsGy.Range("AA" & Rows.Count).End(xlUp).Row
szamlalo = 1
For sor = 1 To usorFajlnev
FN = Cells(sor, "AA") 'itt vannak a fájlnevek kiterjesztéssel
Workbooks.Open utvonal & FN
szamlalo = szamlalo + 1
Set WsInnen = ActiveWorkbook.Sheets(1)
Select Case szamlalo
Case 2
WsGy.Range("B1:B" & usorGy).FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-1],[" & FN & "]Munka1!C1:C2,2,0),"""")"
Case 3
WsGy.Range("C1:C" & usorGy).FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-2],[" & FN & "]Munka1!C1:C2,2,0),"""")"
Case 4
WsGy.Range("D1:D" & usorGy).FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-3],[" & FN & "]Munka1!C1:C2,2,0),"""")"
Case 5
Case 6
Case 7
Case 8
Case 9
Case 10
Case 11
End Select
ActiveWorkbook.Close False
'ha a képletek helyett fix értékeket szeretnél, a lenti két sort aktiváld
' Range("B:K").Copy
' Range("B1").PasteSpecial xlPasteValues
Next
End Sub -
Fferi50
Topikgazda
válasz
zsolti_20
#43118
üzenetére
Szia!
Delila pont azt a kódrészt mutatta meg neked, amivel kitallózhatod a kívánt fájlt.
Az egész kódrészlet aSet w2 = Workbooks("2.xlsx").Sheets("Sheet1")helyébe kerül.
Ahol pedig azt írja, hogy fajnev megnyitása, oda kell betenni ezt a sort.Set w2=Workbooks.Open(fajlnev).Sheets("Sheet1")
Üdv. -
Fferi50
Topikgazda
válasz
zsolti_20
#43112
üzenetére
Szia!
Úgy látom, a Dic objectumod nem lett megkreálva, csak definiáltad. Gondolom a Dictionary objektumot szeretnéd használni.
Akkor kell egy ilyen sor, mielőtt hivatkoznál rá:Set Dic=CreateObject("Scripting.Dictionary")
Ezért hibás a másolás kezdeted így első ránézésre.
Üdv. -
Delila_1
veterán
válasz
zsolti_20
#43112
üzenetére
Inkább tegyél fel egy képet, amin látszik hogy milyen oszlopok vannak az gyűjtő-, és a többi füzetben. A többiből melyik adatokat kell bemásolni a gyűjtőbe.
A valós elrendezést küldd, ne úgy, hogy a gyűjtőben "például" a B oszlop adataihoz kell párosítani a többi füzet D oszlopának az adatait. Az adatok lehetnek kitaláltak, de az elrendezés NEM. -
Delila_1
veterán
válasz
zsolti_20
#43110
üzenetére
Set FD = Application.FileDialog(3)
With FD
.AllowMultiSelect = False 'letiltja a többszörös kijelölést
.Show 'Indítja a dialógboxot
If .SelectedItems.Count = 0 Then
MsgBox "Nem választottál fájlt, befejezzük.", vbInformation
Else
Fajnev= .SelectedItems(1)
'Fajnev megnyitása
w1.Activate 'ha kell, az első füzet aktívvá tétele
'műveletek
'megnyitott füzet bezárása
End If
End WithMásik módszer lehet, hogy a wb1-ben felsorolod egy oszlopban a fájlokat, és egy ciklusban nyitod meg és hajtod végre az utasításokat, zárod be a megnyitott füzetet. A megnyitandó fájl nevét a felsorolásból veszed.
sor=1
Do While Cells(sor,1)<>""
FN=Cells(sor,1)
Workbooks.Open utvonal & FN
'...
'...
sor=sor+1
Loop -
Delila_1
veterán
-
Delila_1
veterán
válasz
zsolti_20
#43102
üzenetére
Ügyelj rá, hogy az utvonal értékadása "\"-re végződjön.
Sub Lapnevek()
Dim FN As String, lap As Integer, WS As Worksheet, WB As Workbook
Dim utvonal As String
utvonal = "D:\Főmappa\Almappa\" 'megnyitandó fájl útvonala
FN = "Fájlnév.xlsx" 'megnyitandó fájl neve
Set WS = ActiveWorkbook.Sheets(1)
Workbooks.Open utvonal & FN
Set WB = ActiveWorkbook
WS.Activate
With Workbooks(WB.Name)
For lap = 1 To .Sheets.Count
Cells(lap, "A") = WB.Sheets(lap).Name
Next
End With
WB.Close False
End Sub
Teljesen kiment a fejemből...
)
Új hozzászólás Aktív témák
Hirdetés
Fferi50
- Windows, Office licencek kedvező áron, egyenesen a Microsoft-tól - Automata kézbesítés utalással is!
- Windows 10 11 Pro Office 19 21 Pro Plus Retail kulcs 1 PC Mac AKCIÓ! Automatikus 0-24
- Vírusirtó, Antivirus, VPN kulcsok
- Bontatlan - BATTLEFIELD 1 Collectors Edition - Játékszoftver nélkül
- Adobe Előfizetések - Adobe Creative Cloud All Apps - 12 Hónap - NYÁRI AKCIÓ!
- Honor Pad X8a 64GB Wifi,1 év Garancia
- 13-14" Új és használt laptopok , üzletitől a gamerig , kedvező áron. Garanciával !
- LG 65C3 - 65" OLED evo - 4K 120Hz 1ms - NVIDIA G-Sync - FreeSync Premium - HDMI 2.1 - PS5 és Xbox!
- Telefon Felvásárlás!! iPhone 14/iPhone 14 Plus/iPhone 14 Pro/iPhone 14 Pro Max
- BESZÁMÍTÁS! 32GB (2x16) G.Skill Trident Z RGB 6600MHz DDR5 memória garanciával hibátlan működéssel
Állásajánlatok
Cég: PC Trade Systems Kft.
Város: Szeged
Cég: CAMERA-PRO Hungary Kft
Város: Budapest