- iPhone 16e - ellenvetésem lenne
- Xiaomi 15 - kicsi telefon nagy energiával
- Mobil flották
- Prohardver app (nem hivatalos)
- Samsung Galaxy S24 Ultra - ha működik, ne változtass!
- Poco F6 5G - Turbó Rudi
- Magisk
- Samsung Galaxy S25 Ultra - titán keret, acélos teljesítmény
- Fotók, videók mobillal
- Azonnali mobilos kérdések órája
-
Mobilarena
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
-
Mutt
senior tag
válasz
eszgé100
#50736
üzenetére
Szia,
DAX-ban van olyan hogy implicit és explicit measure. Mindegyiknek van előnye és hátránya, implicit könnyebben átlátható, de lassabb, az explicit gyorsabb de nehezebb is.
Amikor segédoszlopokról beszélsz, akkor az implicitet jelent. Explicit esetén nincs segédoszlop, hanem a képlet számolja real-time az eredményt azon adatok alapján amit a szűrők átadnak neki (itt fontos megemlítenem a row-context és filter-context koncepciót).
Tudsz nested IF-et használni és ha tudod egy képletben összerakhatod az összes ellenőrzést.
Nem javasolnám a 20+ oszlop létrehozását csak emiatt, de mivel még ismerkedsz vele szerintem nem gond ezen az úton elindulni. Azonban nem tudom elképzelni hogyan tudsz majd ilyen esetben szűrni, hiszen néha egyik, néha másik oszlop alapján kell majd neked eredmény.Power BI-ban lehet Python szkripteket futtatni, ahol már van regex, így ha túl bonyolult lenne DAX-al megoldani akkor ezen is lehet elindulni.
üdv
-
Mutt
senior tag
válasz
föccer
#50735
üzenetére
Szia,
Használd az INDEX függvényt és ott a sor értéknek add meg, hogy {1;2;3...} vagy mutatok egy automatikus
megoldást:
A képlet :
=INDEX(SORBA.RENDEZ(SZŰRŐ(A1:C23;B1:B23=1);3);SORSZÁMLISTA(MIN(5;DARABTELI(B:B;1)));{1;3})Hogyan műkődik:
1. SZŰRŐ(A1:C23;B1:B23=1) -el szűrők arra ahol B = 1 (sárga sorok)
2. sorbarendezem a C oszlop alapján, SORBA.RENDEZ(<előbbi lépés>;3)
3. Létrehozok egy számlistát annak megfelelően hogy maximum hány találatom lehet (ez a 3 sárga sor), de azt sem akarom hogy sokat legyen az eredmény ezért maximálom 5-ben.
4. Az INDEX utolsó paraméterének {1,3}-at adva pedig elrejtem a második oszlopot.üdv
-
Mutt
senior tag
válasz
eszgé100
#50731
üzenetére
Szia,
Power Tools-ban (BI/Query/Pivot) nincs alapból REGEX, így ezt a macerás képletet tudom ajánlani.
IS_ACCURATE =
var helyes_hossz = 20
var hossz = len([Minta]) = helyes_hossz //megfelelő a hossz?
var csoport1 = Not(ISERROR(VALUE(LEFT([Minta]; 3)))) //első 3 karakter szám?
var csoport2 = Not(ISERROR(SEARCH(MID([Minta];4;1);"ABCDEFGHIJKLMNOPQRSTUVWXYZ";1))) //4 karakter nagybetű?
var csoport3 = Not(ISERROR(VALUE(MID([Minta];5;8)))) //5-12 karakterek számok?
var csoport4 = Not(ISERROR(SEARCH(UPPER(MID([Minta];13;1));"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ";1))) //13 karakter helyes?
var csoport5 = Not(ISERROR(VALUE(MID([Minta];14;7)))) //14-20 karakterek számok?
var eredmeny = hossz * csoport1 * csoport2 * csoport3 * csoport4 * csoport5
return if(eredmeny;1;blank())[Minta] helyére írd be a nálad használt tábla+mezőnevet. A végén üres értéket adok vissza hibásakra, mert akkor egyből elrejti a Power BI a helytelen értékeket, de ha látni akarod és szűrni, akkor "return eredmeny" a vége.
üdv
-
Mutt
senior tag
válasz
Antonio
#50726
üzenetére
Szia,
Power Pivot-tal így tudod megkapni az eredményt.
1. Beszúrás -> Kimutatás -t válaszd és ott pipáld be alul az adatmodellbe töltést.
2. Hozd létre a kimutatást, majd a csoport fejlécén jobb klikk és válasz az új érték opciót.
3. A képlet pedig legyen ez:
=CALCULATE(COUNT(Table1[Model]);Table1[A (<4,0)]<4;Table1[B (>10)]>10)+0
ahol CALCULATE és COUNT DAX függvények, magyar nyelvű Excelben is angolul kell őket használni.
A Table1 a tábla neve ahol az adatok találhatók, ez lehet más nálad csak írd át. Ha nincs táblában akkor Range1 vagy hasonlót lehet használni. A szögletes zárójelek, pedig az oszlop azonosítók (ezeket is változtasd ha kell).A lényeg, hogy megszámoljuk az értékeket, de közben szűrünk is. Itt 2 koncepciót is használ a Power Pivot, az egyik az ún row-context (ami azt jelenti hogy figyeli milyen mezőket húztál be a kimutatásba és azok alapján is szűr, vagyis az országnak és modelnek megfelelő adatsort adja át a képletnek). A másik pedig hogy mi is adhatunk/vehetünk el szűrőket. Ezt tettem a COUNT után, de ehhez kellett a CALCULATE függvény.
A +0 a végén azért van hogy üres eredménynél 0-t írjon ki. Elhagyhatod ha úgy gondolod, és ekkor nem jelennek meg a felesleges modelek.
üdv
-
Mutt
senior tag
Szia,
PQ-vel nem tudod több lapra kiírni az eredményt.
Megoldások:
1. PQ-ben annyi query-t csinálni, mint ahány bolt van a listában és mindegyiket saját lapjára tölteni.
Elég 1x megcsinálni a query-t, majd másolgatni (duplikálni és a szűrést megfelelően beállítani).
2. Ha sima Pivot-tal elő tudod állítani a kívánt eredményt, akkor viszont azzal lehet munkalapokat létrehozni automatice. Nézd meg ezt az útmutatót.
3. Makró.üdv
-
#50628
Mutt
senior tag
LilProphet90
#50624
Mutt
senior tag
válasz
LilProphet90
#50624
üzenetére
Szia,
Próbáljátok ki a megoldásomat.
Ha megfelel, akkor lehet saját makrófüzetbe tenni vagy addin-t csinálni belőle és mindig elérhető lesz.Ahogy már írták a területi beállításoknál a listaelválasztó befolyásolja ezt.
További megoldások:
1. Tab delimeted-ben mentsetek és Notepad++ -ban Tab-ot cserélni pontosvesszőre. Lehet billentyűparancsot ehhez rendelni akár.
2. Excelben másik munkalapon a SZÖVEGÖSSZEFŰZÉS (TEXTJOIN)-al lehet a kivánt formátumot elérni. A kapott lapot lehet menteni szövegként (itt is talán Tab-os lesz a jobb) vagy az oszlopot kijelölve Notepad++ -ba másolni és ott menteni.üdv
-
Mutt
senior tag
válasz
RockHaRD
#50612
üzenetére
Szia,
Power Query fog akkor neked segíteni.
Youtube-on magyarul ezt a videosorozatot találtam, ami tud neked az első lépésekben segíteni. (Az első videóban pont a legbonyolultabb dolgot mutatják be, de érdemes kezdőként végignézni hogy lásd mi is ez.)PQ egy olyan "makrózási" lehetőség, ahol
- nem kell kódot írnod, többnyire csak kattintanod kell a megfelelő művelethez és közben
- adatokat tudsz átalakítani megtartva az eredeti adatsort/formátumot is.Előnye, hogy ha frissül az adatsorod akkor elég a lépéseket lefuttatni és megkapod az eredményt.
Nézd meg a videokat, ha gond van kérdezz nyugodtan. Ha van publikus mintád, akkor oszd meg és berakjuk a fájlba a lépéseket. Ha nem publikus és nem megy akkor keress privátban.
üdv
-
Mutt
senior tag
válasz
RockHaRD
#50581
üzenetére
Szia,
A másik problémám, hogy elég sok a /A /B házszám, és hiába állítottam a cellákat számokra, amikor növekvő sorrendbe szeretném tenni, akkor a / jeles nélküleket sorrendbe teszi, majd lá külön az összes olyat, amiben van / jel.
Erre is a Power Query-t tudom ajánlani.
Lépések
1. Eredeti lista betöltése PQ-be
2. Az oszlop duplikálása.
3. A duplikált oszlop számokra és betükre szedése
4. Számokat tartalmazó oszlop típusát egész számra állítani.
5. A / jelek tőrlése a másik oszlopban hogy egységes legyen.
6. Sorba rendezés előbb a számokat taralmazó, majd pedig az esetleges betüket tartalmazó oszlopok alapján.
7. Segédoszlopok tőrlése.
8. Eredmény visszatöltése Excelbe.üdv
-
Mutt
senior tag
válasz
RockHaRD
#50597
üzenetére
Szia,
Ha Excel365-öd van, akkor a SZŰRŐ és SZÖVEGÖSSZEFŰZÉS függvények fognak segíteni.
Az alsó táblázat első 4 oszlopa az EGYEDI függvénnyel készült, míg az utolsó oszlop az alábbi képlettel:
=SZÖVEGÖSSZEFŰZÉS(KARAKTER(10);IGAZ;SZŰRŐ(E3:E9;(A3:A9=A13)*(B3:B9=B13)*(C3:C9=C13)*(D3:D9=D13)))Hogy 10 ezer sornál mennyire lesz lassú azt nem tudom.
Excel 2010-től van Power Query (PQ), amivel egyszerübb mivel egy egyszerű csoportosítás.
1. Adatokat betöltöd PQ-be.
2. Kijelölöd az oszlopokat ami alapján akarsz csoportosítani majd múveletnek a "Minden sort" használod.
3. Utána kell egy új oszlop, amelyben az előzőleg megadott oszlopra és oszlopnévre hivatkozunk.
4. Az új oszlop jobb felső sarkában értékek kinyerése opciót válaszd és ott elválasztó karakternek a speciális részből a kocsiemelés+sorvisszát.
5. Az eredményt lehet Excelbe visszatölteni.üdv
-
Mutt
senior tag
válasz
andreas49
#50587
üzenetére
Szia,
Lehetséges-e megváltoztatni az igazításnál a behúzás egységének nagyságát?
Próbálj egyéni számformátumot használni. Az aláhúzás jel az utána megadott karakter szélességével tolja jobbra a cella tartalmát.
A _+_+# ##0;_+_+-# ##0;_+_+0;_+_+@ formátum két plusz jelnyi helyet hagy ki számok és szövegek előtt, vagy ha csak a szövegeket akarod igazatni akkor legyen a formátum _+_+@üdv
-
Mutt
senior tag
-
Mutt
senior tag
Szia,
Feri kódja nem nézi, hogy az ellenőrzés után van-e újabb timestamp. Nem tudom, hogy ez a valós adaton gondot okoz-e vagy sem. Ahhoz hogy helyben cserélhesd az adatokat másik makró kell, amit billentyűparanccsal vagy külön gombbal tudsz indítani. Vigyázz mert nem lehet visszavonni a makró módosításait!
Sub ellenor2()
Dim rngAdatok As Range
Dim adat As Range, adatSzoveg As String
Dim posEllenorzes As Long, posHatar As Long, posKovetkezoHatar As Long
Const ell = "ellenőrzés", hatar = "|"
Set rngAdatok = Selection.CurrentRegion 'kijelölt cellát tartalmazó tartomány használata
For Each adat In rngAdatok
posEllenorzes = InStr(1, adat, ell)
adatSzoveg = ""
'ha van benne "ellenőrzés" szöveg akkor nézzük át
If posEllenorzes > 0 Then
posHatar = InStrRev(adat, hatar, posEllenorzes) 'ellenőrzes előtti határjel helye
posKovetkezoHatar = InStr(posHatar + 1, adat, hatar) 'ellenőrzés utáni első határjel helye
If posKovetkezoHatar > posEllenorzes Then
adatSzoveg = Mid(adat, posHatar, posKovetkezoHatar - posHatar - 1)
End If
End If
adat = adatSzoveg
Next adat
End SubPower Query (PQ)-ben pedig több lépéses a dolog.
Az én mintám így néz ki:
Bal oldalt az adatsor, jobb oldalt pedig egy munkafázis ahol lehet módosítani hogy mire keressen.
1. Első lépésként mindkét táblát betöltöm PQ-be. A jobb oldalinak adtam egy ilyen nevet "tblMunkafazis".
2. Megkeressük hogy van-e "ellenőrzés" az adatsorban. Ehhez egy új oszlopot szúrunk be, neve nálam "Ellenorzes". A képlete pedig:
=Text.PositionOf([Adatok], tblMunkafazis[Munkafázis]{0})
3. Az ehhez tartozó pipe-ot is megkeressük. Az oszlop neve: "Határ", képlete:
=Text.PositionOf(Text.Start([Adatok],[Ellenorzes]),"|",Occurrence.Last)
4. Az ezutáni pipe helyének a képlete:
=Text.PositionOf(Text.Middle([Adatok],[Hatar]+1),"|")+[Hatar]
5. Minket csak azok érdekelnek ahol van ellenőrzés szöveg és ahol a két pipe eltér. Új oszlopba pedig kirakjuk az eredeti szöveg pipeok közötti részét. A képlet:
=if [Ellenorzes]>0 and [Kovetkezo hatar] > [Hatar] then Text.Middle([Adatok],[Hatar],[Kovetkezo hatar]-[Hatar]-1) else null
6. Azok a sorok ahol null van nem kellenek és a többi oszlopot is eltávolítjuk.
7. Eredményt Excel-be visszatöltjük.A teljes M-kód:
let
Forrás = Excel.CurrentWorkbook(){[Name="Táblázat1"]}[Content],
#"Típus módosítva" = Table.TransformColumnTypes(Forrás,{{"Adatok", type text}}),
#"Egyéni oszlop hozzáadva" = Table.AddColumn(#"Típus módosítva", "Ellenorzes", each Text.PositionOf([Adatok], tblMunkafazis[Munkafázis]{0}), Int64.Type),
#"Egyéni oszlop hozzáadva1" = Table.AddColumn(#"Egyéni oszlop hozzáadva", "Hatar", each Text.PositionOf(Text.Start([Adatok],[Ellenorzes]),"|",Occurrence.Last), Int64.Type
),
#"Egyéni oszlop hozzáadva2" = Table.AddColumn(#"Egyéni oszlop hozzáadva1", "Kovetkezo hatar", each Text.PositionOf(Text.Middle([Adatok],[Hatar]+1),"|")+[Hatar], Int64.Type),
#"Egyéni oszlop hozzáadva3" = Table.AddColumn(#"Egyéni oszlop hozzáadva2", "Eredmeny", each if [Ellenorzes]>0 and [Kovetkezo hatar] > [Hatar] then Text.Middle([Adatok],[Hatar],[Kovetkezo hatar]-[Hatar]-1) else null, type text),
#"Sorok szűrve" = Table.SelectRows(#"Egyéni oszlop hozzáadva3", each ([Eredmeny] <> null)),
#"Többi oszlop eltávolítva" = Table.SelectColumns(#"Sorok szűrve",{"Eredmeny"})
in
#"Többi oszlop eltávolítva"üdv
-
Mutt
senior tag
Szia,
Ha jól értelmezem a feladatot, akkor ez az UDF tud segíten a 2-es problémán.
Function Munkafazis(adat As Range, nev As String, munka As String) As Long
Dim fSplit
Dim r As Long, c As Long
Dim TimeStamp As Boolean
Dim m As Long
Munkafazis = 0
m = 0
For r = 1 To adat.Rows.Count
fSplit = Split(adat.Rows(r), " - ")
TimeStamp = False
If UBound(fSplit) > 1 Then
'menjünk végig a listán
For c = 0 To UBound(fSplit)
'ha a név egyezik akkor nézzük meg a munkafázist és a timestampet
If fSplit(c) = nev Then
'ha a munkafázis egyezik és már volt timestamp akkor számolhatjuk az elvégzett munkát
If TimeStamp Then
m = m + 1
Exit For
End If
If fSplit(c - 1) = munka Then
If Not TimeStamp Then TimeStamp = True
End If
End If
Next c
End If
Next r
Munkafazis = m
End Functionüdv
-
#50502
Mutt
senior tag
pero19910606
#50485
Mutt
senior tag
válasz
pero19910606
#50485
üzenetére
Szia,
Az én megoldásom azon alapul, hogy a márkát és típusokat egy szóköz és vmilyen szám kombója választja el egymástól.
Ezek alapján Excel változattól függően az alábbi képletek műkődhetnek.1. Microsoft 365 esetén SZÖVEGELŐTTE (TEXTBEFORE) függvény segít.
=SZÖVEGELŐTTE(A2; {" 1";" 2";" 3";" 4";" 5";" 6";" 7";" 8";" 9"})2. Régebbi Excelben pedig egy tömb-függvény, SUMPRODUCT (SZORZATÖSSZEG) segítségével keressük meg az első előfordulást.
=BAL(A2;SZORZATÖSSZEG(MIN(HAHIBA(SZÖVEG.TALÁL({" 1";" 2";" 3";" 4";" 5";" 6";" 7";" 8";" 9"};A2);1024))))3. A típust pedig a maradék szöveg, amit legegyszerűbben így lehet megkapni.
=HELYETTE(A2;B2;"")
üdv
-
#50462
Mutt
senior tag
Hellboy2387
#50457
Mutt
senior tag
válasz
Hellboy2387
#50457
üzenetére
Szia,
Ha a fájl továbbra is nulla méretű, akkor nincs remény.
Ha van mérete, akkor csinálj róla egy másolatot
1. amit próbálj megnyitni OpenOffice vagy LibreOffice programokkal
2. nevezd át ZIP-re és próbálj megnyitni. Az xl/worksheets mappában lesznek a lapok, a workbook.xml pedig a lapok tulajdonságait tartalmazza. Másold át egy frissen mentett (üres) xlsx-be amit előtte ZIP-re átnevezel.üdv
-
#50455
Mutt
senior tag
Hellboy2387
#50454
Mutt
senior tag
válasz
Hellboy2387
#50454
üzenetére
Recuva ingyenes változatát próbáld meg.
-
#50452
Mutt
senior tag
Flowtation
#50431
Mutt
senior tag
válasz
Flowtation
#50431
üzenetére
Szia,
Excel 2013-tól beépitett Power Queryvel lehet makró nélkül.
Lépések:
1. Mappa tartalmának beolvastatása.
2. Fájlnév vagy dátum szerinti sorbarendezés.
3. Első sor megtartása
4. Megmaradt fájl tartalmának feldolgozása.Látom sikerült PQ-vel.
üdv
-
Mutt
senior tag
válasz
Pityke78
#50409
üzenetére
Szia,
Képletes megoldást kaptál, de Kimutatással/Pivot-tal is meg lehet csinálni, csak nem a rendessel, hanem a Power Pivot-tal.
A legfontosabb lépés, hogy amikor a kimutatás létrehozását csinálod, akkor be kell ikszelni hogy az adatsor kereljön be az adatmodel-be. Ezzel elérhetővé válnak a DAX képletek.
Megcsinálod a Pivotot ahogy akarod és ez esetben a darab-nál a megszokott min/max/összeg mellett lesz distinct count.
Vagy akár a CONCATENATEX dax függvénnyel össze is fűzheted az értékeket.
üdv
-
Mutt
senior tag
válasz
föccer
#50338
üzenetére
Szia,
Az Excel akkor frissíti a függvények értékét, ha a függvényben használt paraméterek/változók megváltoznak. A munkalapon a függvényt paraméterek nélkül használod, így nem gondolja hogy frissíteni kellene az eredményt (az pedig hogy F9-re sem frissít az talán feature, ami 30 év után már nem lesz megoldva).
A megoldás, hogy belecsempésszük a képletbe a forrás cellág, G15-ben ezel a képlettel =HA(ÜRES(C3);Elso_halmaz_feltetele;Elso_halmaz_feltetele) már frissülnie kell az eredménynek.
Másik megoldás, hogy a névkezelőben módosítod az eredeit képletet erre.
=HA(MA();KIÉRTÉKEL(Feltételek!G12);KIÉRTÉKEL(Feltételek!G12))Itt trükk, hogy a MA egy olyan függvény amely minden cella módosításakor frissül, ezért rántja magával a képlet újraszámolását.
üdv
-
Mutt
senior tag
válasz
föccer
#50334
üzenetére
Szia,
Arégi Excel4-es makró függvények munkalapon relatív hivatkozásokat használnak. Feri mintjáján F1-ben van az SZAMOL függvény, amitől balra 3 cellával (C1-ben) van képlet szöveges változata.
Ha te F2-ben próbálod meg használni, akkor addig nem fog működni amíg a cellától balra 3-al nem adsz meg képletet kiértékeléshez.
Az 50302-ben feltett kérdésben próbálunk meg segíteni, de amit eddig írtál nekem is kínai. Ha akarsz/tudsz példát mutatni, akkor talán tudunk segíteni. PowerPivot nem megoldás, Power Query-t is kétlem mivel egy sort több "halmazba" nem lehet rendelni. Szerintem Python szkripttel, ha a VBA felejtős, lehetne megoldani a dolgot.
üdv
-
Mutt
senior tag
válasz
sniphoe
#50296
üzenetére
Szia,
Segédoszlopban az alábbi képlettel, ahol az érték 2 ott van két mássalhangzó (mivel a képletben a magánhangzókat soroltam fel):
=SZORZATÖSSZEG(--HIBÁS(SZÖVEG.TALÁL(KÖZÉP(A2;{2;4};1);"aáeéiíoóöőuúüű")))Microsoft365 verzió esetén pedig a SZŰRŐ (FILTER) függvény tud segíteni.
=SZŰRŐ(A2:A21;HIBÁS(SZÖVEG.TALÁL(KÖZÉP(A2:A21;2;1);"aáeéiíoóöőuúüű"))*HIBÁS(SZÖVEG.TALÁL(KÖZÉP(A2:A21;4;1);"aáeéiíoóöőuúüű")))
üdv
-
Mutt
senior tag
válasz
lumpy92
#49659
üzenetére
Szia,
Power Query-s (PQ) megoldást tudok javasolni ami Excel 2016-tól az Adatok (Data) fülön érhető el, a kulcsszó UNPIVOT, magyarul elemi értékek kinyerése. (Excel 2010-hez külön kell telepíteni.)
1. CVS fájl nálam így néz ki.
2. Egy üres Excel-ben Adatok -> Fájlból -> Text/CSV fájlból beolvasást választva megnyitjuk a CVS fájlt, majd az ablakban "Adatok átalakítása" opciót válaszd.
3. Ahogy látom a példád első értéke az évet jelöli, ezt kell eltárolnunk egy változóba. Ehhez bal oldalt kattints a lekérdezésben látható névre és jobb klikk megkettőzés opciót válaszd.
4. Középen van a szerkesztőléc, nyisd le és a "képlet" végére írd be hogy {0}. Ezzel az első sort választjuk ki. Ha nyomsz egy entert akkor látod is a változást. Most a képlet végére írd be még, hogy [Column1]. Ezzel pedig a sor "Column1" oszlopát választjuk ki. Ami marad az az évszám. Jobb oldalt a tulajdonságok alatt névnek adjál vmi értelmeset, én "evszam"-nak neveztem el.
5. Bal oldalt a lekérdezések alatt válaszd a másikat, mert azon fogunk dolgozni.
6. Kezdőlap -> Sorok eltvávolítás -> Legelső sorok eltávolítása opcióval az első sort távolítsuk el.
7. Kattins az első oszlop fejlécére a teljes oszlop kijelöléséhez és Átalaktás -> Oszlopok elemi értékre bontása -> Többi oszlop bontása opciót válaszd. (Ez az Unpivot.)
8. A kapott eredmény vmi ilyen. Az első oszlop továbbra is a napokat adja meg, a középsőből hónapokat csinálunk, az utolsó pedig az értékeid.
9. Hónapokat egy új oszlop beszúrásával csináljunk. A forrásban Column2 januárt, Column3 feburárt (és így tovább a többinél) jelölte, vagyis ha töröljük a "column" szöveget és kivonunk 1-et a számból akkor megvan a hónap sorszáma. A képlet pedig:=Number.From(Text.Replace([Attribútum],"Column",""))-1
10. Egy újabb egyéni oszlop beszúrásával csináljunk dátumot. A képlet:=#date(evszam,[Honap],[Column1])
11. A dátum oszlop bal felső sarkában az ABC123 azt jelenti, hogy nincs adattípus az oszlophoz kiválasztva. Kattints ide és válaszd a dátumot. (Haladó változat, hogy már a 10-es lépésben megadjuk az adattípusát az eredménynek, de maradjunk még a kezdőlépéseknél.)
12. Töröld a felesleges oszlopokat. Jelöld ki a dátum és érték oszlopokat, majd Kezdőlap -> Oszlopok eltávolítása további oszlopok eltávolítása.
13. A dátumok között lesznek érvénytelenek. Ezeket Kezdőlap -> Sorok eltávolítása -> Hibák eltávolításával tudod törölni.
14. Dátum oszlopot kijelölve, Kezdőlap -> Növekvő sorrendezés-sel tudod idősorba tenni az adatokat.
15. Végül Kezdőlap -> Bezárás és betöltéssel Excelbe kerülnek az adatok.Vége.
Ha elmented a fájlt, akkor a lenti lépések is mentődnek, igy ha lenne egy új adatsorod akkor egy frisstéssel azon is lehet használni.
üdv
-
Mutt
senior tag
válasz
stigma
#49625
üzenetére
Szia,
Refresh utána táblázat aktualizálódik, viszont, ha elérési úton változtatok egy másik mappára, akkor nem mindig frissül a táblázat (Képletek /Számolási beállítás /automatikusan van) és a régi elérési utakat dobja bele.
Itt kérdésem, hogyan adod meg a másik elérési útvonalat?
A Power Query-ben "hardkódolva" vannak az elérési útvonalak. Adatok -> Adatok beolvasása -> Adatforrás beállítása alatt lehet könnyedén módosítani a forrást ha kell. (Régen csak az M-kódban lehetett módosítani a forrást.)
Jobb megoldás azonban hogy a munkafüzet vmelyik cellájába írod be az útvonalat, majd ezt is betöltöd Power Query-be és ezt paraméterként használod a másik lekérdezésben. Nézd meg ezt a videót róla.
üdv
-
Mutt
senior tag
válasz
Salex1
#49623
üzenetére
Szia,
Makrós megoldást kaptál, itt egy Power Query-s (PQ).
1. Legyen egy táblázatban az adatsorod.
2. Adatok -> Táblázatból/Tartományból beszúrással bekerül PQ szerkesztőbe a lista.
3. A minta nem követte a 2-5-5-8-6-os logikát, azért inkább azt csináltam hogy kötőjelenként felosztottam, majd utána újra előállítottam a helyes adatokat. Kezdőlap -> Oszlop felosztása opciót használd.
4. Ez után lesz egy automatikus típus konverzió lépés, amelyet jobb oldalt a lépések listából érdemes törölnőd.
5. Állítsuk elő az általad kívánt azonosítókat. Jelöld ki az első és második oszlopot, majd Átalakítás -> Oszlopok egyesítése kell. Én ID1 nevet adtam az új oszlopnak.
6. Jelöld ki az újonnan létrehozott és a következő oszlopot, majd Oszlopok hozzádása -> Oszlopok egyesítése. Én ID2-nek neveztem az új oszlopot.
7. Még kell 2 újabb oszlop egyesítés, mindig a frissen létrehozott oszlophoz fűzve a szükségeset.
Vmi ilyen eredmény lesz.
8. Jelöld ki az ID1, ID2, ID3, ID4 oszlopokat, majd Kezdőlap -> Oszlopok eltávolítása -> További oszlopok eltávoltása menűvel töröljük a felesleges oszlopokat.
9. Jelöld ki az ID4 oszlopot, majd Kezdőlap -> Csoportosítási szempont opcíót álltsd így be.
10. Oszlop hozzáadása -> Egyéni oszlopot válaszd, a képlet legyen ez:=List.Combine({[Adatok][ID1],[Adatok][ID2],[Adatok][ID3],[Adatok][ID4]})
Egy kis magyarázat ehhez: a 9-es lépésben minden sorhoz hozzárendeltük a hozzátartozó, magasabb szintű azonosítókat. Ezeket nyerjük ki a fenti képlettel. PQ-ben több típus van (record, list, table). Record egy mező, ami nem jó nekünk, a list több record, amelyeket sorokba/oszlopokba tehetünk, a table pedig egy táblázat, ami megint nem lesz jó nekünk a megoldáshoz.
11. Az új oszlop fejlcében a jobb oldali ikonra kattinva válaszd a sorokba bontást.
12. Töröld a másik két oszlopot, nem kellenek már.
13. Vegyük ki az ismétlődéseket: Kezdőlap -> Sorok eltávolítása -> Ismétlődések eltávolítása
14. Töltsük vissza Excelbe a végeredményt, Kezdőlap -> Bezárás és betöltés.üdv
-
#49616
Mutt
senior tag
dellfanboy
#49589
Mutt
senior tag
válasz
dellfanboy
#49589
üzenetére
Szia,
vagy vmi jo YT csatorna?/blog?
YT-on ExcelisFun-t javaslom, ahol gyakorlati példákon keresztül mutat be megoldásokat. Évekkel ezelőtt volt egy sorozatuk MrExcel-el, Dueling with MrExcel, ahol Mike függvényekkel, míg Bill VBA-val oldotta meg ugyanazt a problémát.MrExcel-t is érdemes nézni YT-on, az elmúlt években inkább az új funkciókat magyarázza el.
Még tudom javasolni az alábbi csatornákat:
- MyOnlineTrainingHub
- Leila Gharani
- Excel Macro Masterytudtok olyan hirlevelet, ami excel feladatokat kuld gyakorlas cellal?
Nem ismerek ilyet, de nézegess fórumokat ahol láthatod a problémát és megoldás(oka)t is.
pl. ExcelForum.com, MrExcel.com, Chandoo.orgSzerintem azzal nincs gond, hogy csak ~20 függvény van rendszeresen használva.
1. Érdemes megnézni, hogy ugyanazt az eredményt el tudod-e érni máshogy/segédoszlopok nélkül.
2. Ha új Excel-ed van vagy Microsoft365-öd, akkor vannak új függvények amelyekkel ismerkedj. pl. XLOOKUP VLOOKUP helyett, FILTER AGGREGATE helyett.
3. Power Query-vel ismerkedj, főleg ha sok adattal dolgozol.
4. Készíts saját függvényeket (UDF) vagy addon-t hogy megkönnyítsd az ismétrlődő feladatok végrahajtását.Végül itt egy kis feladat.
Az Excel-ben kettes számrendszerből tízesbe értékeket a BIN.DEC függvénnyel lehet átváltani, de a függvény csak 511-ig ad helyes eredményt. Kellene egy képlet, amellyel bármilyen számot át lehetne váltani.
-
Mutt
senior tag
válasz
stigma
#49606
üzenetére
Szia,
Power Query ... Van arra lehetőség, hogy VBA kóddal ezeket kimentsem 1 listába/tömbbe bármibe?
Munkalapra - rejtsd el ha nem akarod hogy lássák - kell töltened és onnan tudod tömbbe tenni.
Olvasgatva a neten mások próbálkoztak adatmodel-be töltéssel és onnan különböző módokon kinyerni az adatot, de nehézkesnek tűnik szemben a munkalapra töltéssel.üdv
-
#49566
Mutt
senior tag
MasterMark
#49563
Mutt
senior tag
válasz
MasterMark
#49563
üzenetére
Szia,
Egy próba fájlon próbáld ki ezt a makrót.
Csak pontos egyezés esetén cseréli a képletben az első találatot (Count:=1 -et törölve ez a korlátozás megszűnik) pontossan arra amit megadtál.Sub UpdateRangeInFormulas()
Dim ws As Worksheet
Dim rng As Range
Dim rngFormulas As Range
Dim mit As String
Dim mire As String
Dim keplet As String
mit = Application.InputBox(Prompt:="Mit cseréljünk?", Title:="Keresendõ", Default:="A1:A10", Type:=2)
mire = Application.InputBox(Prompt:="Mire cseréljük?", Title:="Új érték", Default:="A1:A11", Type:=2)
For Each ws In ThisWorkbook.Worksheets
Set rngFormulas = Selection.SpecialCells(xlCellTypeFormulas, 23)
For Each rng In rngFormulas
keplet = rng.Formula2
If InStr(1, keplet, mit) > 0 Then
rng.Formula2 = Replace(keplet, mit, mire, Count:=1)
End If
Next rng
Next ws
End Subüdv
-
Mutt
senior tag
Szia,
A csökkentett árért megéri a tematika alapján.
Az óraszám nem sok így csodát ne várj. Amit tudok javasolni, hogy szerezz be VBA könyvet is (John Walkenbach könyve nagyon jó, de az összefoglalóban van magyar is).Leila YouTube videói jók, onnan ingyen is tudsz tanulni.
A VBA-t ma már mással is lehet helyettesíteni. Power Queryvel vagy Python-al. Ami érdekes lehet hogy van OfficeScript pár éve, ami az online változatokat is támogatja. Ez pedig JavaScripthez hasonló.
A VBA egyrejobban korlátok közé van szorítva, így előbb-utobb a fentiek valamelyikét is érdemes megismerni, megtanulni.
Udv
-
Mutt
senior tag
válasz
stigma
#49498
üzenetére
Szia,
Alternativaként javaslom a Power Query-ben a From Sharepoint Folder lekérdezést. Nekem ment miután beállítottam a jogosultságot.
A lekérdezést egyszer kell csak megcsinálni és utána VBA-ban 1 sorral lehet frissíteni (SPFajlok néven mentettem a saját lekérdezésemet):
ActiveWorkbook.Connections("Query - SPFajlok").Refreshüdv
-
Mutt
senior tag
válasz
bozsozso
#49493
üzenetére
Szia,
Lekérdezések kombinálása kell neked. Legyen mondjuk ez a kiinduló állapot.
Lépések:
1. A bal oldali adatsort töltsd be PQ-be, majd bármi módosítás nélkül Bezárás és betöltés opcióval gyere vissza excelbe.
2. Most állj a másik adatsorra és azt is töltsd be PQ-be. Bal oldalt látni fogod mindkét lekérdezést. Jobb oldalt pedig a lekérdezésekben használt lépések láthatóak. Itt érdemes a lekérdezésnek vmi értelmesebb nevet adni, de nem kötelező.
3. Válaszd ki bal oldalt az első lekérdezést, majd Fájl menü -> Lekérdezések egyesítése opciót válaszd ki.
Fent automatikusan megjelenik az aktuális lekérdezés, középen a legördülő listából válaszd ki a másikat.
4. Fent és lent is kattints arra az oszlopra amely alapján az egyesítést meg kell csinálni. (Itt akár több oszlopot is ki lehet jelölni, ekkor egy kis sorszám is megjelenik a fejlécben. Ekkor fontos hogy mindkét helyen azonos sorrendben legyenek kijelölve a dolgok).
5. Alul a csatlakozás (join) tipusa alapból jó, de lehet választani ha neked más egyesített halmaz kell.
6. OK-val kapsz egy új oszlopot, amelynek a jobb felső sarkába kattintva válaszd a kibontást és jelöld be azokat az oszlopokat amelyekre szükséged van. Érdemes alul - eredeti oszlopnév előtagból - kivenni a pipát.
7. Készen is vagy, lehet a bezárást és betöltést használni.
8. Ha zavar, hogy a lookup táblát egy új munkalapra betöltötte az Excel, akkor csak töröld azt a lapot. Ettől a lekérdezés még menni fog, de átváltozik kapcsolódásra (only connection), amely azt jelenti hogy minden lépés ami a lekérdezésben történik nem jelenik meg munkalapon, de továbbra is használható más lekérdezésekben.A fenti lépésekkel a teljes egyezéses FKERES-t tudod helyettesíteni. Sok adat esetén szignifikánsan gyorsabb. A közeltítő egyezéses FKERES (tipikus példa számok keresése egy tartományban) máshogy néz ki PQ-ben.
üdv
-
Mutt
senior tag
válasz
Misi_D
#49483
üzenetére
Szia,
A héten tanultam, hogy Pivot-ban a sor értékeket át lehet nevezni. Állj rá az "(üres)" cellára és fent a szerkesztő lécben (vagy F2-vel helyben) írd át egy szóközre az értéket.
Visszacsinálni ezt trükkös, de kb. 10-et visszaolvasva látsz tőlem egy makrós megoldást, illetve az eredeti kérdezőtől egy másikat.
üdv.
-
Mutt
senior tag
válasz
bozsozso
#49471
üzenetére
Szia,
Az A-oszlopban vannak egymás után az azonosítók. Egy Power Querys (PQ) megoldás, ami Excel 2016-tól működik.
1. A-oszlopban vmelyik elemen állsz, majd Adatok fülön Táblázatokból/tartományból ikonra kattintva betölti az adatokat PQ-be.
2. Átalakítás menüben válaszd a Transzponálást (ezzel minden azonosítót külön oszlopba tudunk tenni).
3. Oszlop hozzáadása -> Egyéni oszlop. A képlet pedig {44927..44927+365}
Kapcsos zárójelek listát jelentenek a PQ-ben, ha a két pont pedig felsorolást pl. {1..5} visszadja a számokat 1-től 5-ig. A 44927 pedig az Excelben 2023. jan. 1-et jelenti, ha dátumformátumot választunk.
4. Az új oszlop jobb felsősorkában lévő ikonra kattintva válaszd a kibontás új sorokba opciót.
5. Továbbra is ezen az oszlopon állva, de most a bal oldalt az ABC123-ra kattintva válaszd a Dátum formátumot.
6. Kezdőlapon Bezárás és betöltés ikont válaszd.üdv
-
Mutt
senior tag
válasz
nihill
#49466
üzenetére
Szia,
Pivot táblában át engedi írni az excel az értékeket, pl. "kutya"-t "macska"-ra.
28 év Excel használata során nem használtam ezt a dolgot, szóval tanultam vmit.
Nem találtam róla semmit, de ezzel a VBA kóddal a munkalap összes kimutatását rendbe tudod tenni.Sub GetBackTheOriginalItemNames()
Dim WB As Workbook
Dim WS As Worksheet
Dim PT As PivotTable
Dim PF As PivotField
Dim PI As PivotItem
Set WB = ThisWorkbook
For Each WS In WB.Worksheets
For Each PT In WS.PivotTables
For Each PF In PT.RowFields
For Each PI In PF.PivotItems
If PI.Name <> PI.SourceName Then PI.Name = PI.SourceName
Next PI
Next PF
Next PT
Next WS
End Subüdv
-
Mutt
senior tag
Hasznos új funkció kerül az Excel-be, egyelőre csak a tesztelőknek érhető el.
Adatok importálásakor, vágólapról másoláskor vagy gépeléskor választható hogy a számként vagy szövegként tárolja az adatot az alábbi esetekben:
1. ha van vezető nulla az adatban
2. legalább 15 számjegyből álló számsort lát
3. ha számok után van E betű (ezt a tudományos számformátum)MrExcel az alábbi YT videóban mutatja be ezt.
Most már csak azt kellene elérni, hogy ne próbálja meg bizonyos számokat dátummá alakítani.
-
Mutt
senior tag
válasz
föccer
#49346
üzenetére
Szia,
Mivel ez egy tömb-képlet (az új, előfizetéses változatban minden képlet tömb-képletként került feldolgozásra) azért a Formula2 tulajdonságot kell használni.
Itt a példa:
Sub ArrayFormula()
Dim rngSzuro As Range
Dim rngAdatok As Range
Dim rngCel As Range
Set rngSzuro = Range("A1:A10000")
Set rngAdatok = Range("B1:B10000")
Set rngCel = Range("G2:G4")
'kézzel van megadva minden tartomány a képleten belül
rngCel.Formula2 = "=TRANSPOSE(FILTER(B1:B10000,A1:A10000=F2))"
'változókból jönnek a tartományok
rngCel.Formula2 = "=TRANSPOSE(FILTER(" & rngAdatok.Address & "," & rngSzuro.Address & "=" & rngCel.Range("A1").Offset(, -1).Address(RowAbsolute:=False) & "))"
'R1C1 változatban
rngCel.Formula2R1C1 = "=TRANSPOSE(FILTER(" & rngAdatok.Address(ReferenceStyle:=xlR1C1) & "," & rngSzuro.Address(ReferenceStyle:=xlR1C1) & "=RC[-1]))"
End SubMicrosoft365 verzió esetén javasolt csak a Formula2-t használni, a korábbiaknák pedig a Formula vagy FormulaArray-t attól függően hogy sima vagy CSE képletről van szó.
üdv
-
#49264
Mutt
senior tag
istvankeresz
#49263
Mutt
senior tag
válasz
istvankeresz
#49263
üzenetére
Szia,
Ha Office2019-ed vagy Microsoft365-öd van akkor inkább az XKERES függvényt tudod használni, amely az FKERES újabb változata. Előnye, hogy tartományt (több cellát a sorból) tud visszaadni, míg az FKERES csak tömbfüggvényel tud egyszerre több értéket visszaadni.
üdv
-
#49209
Mutt
senior tag
bLaCkDoGoNe
#49203
Mutt
senior tag
válasz
bLaCkDoGoNe
#49203
üzenetére
Szia,
Excel 2010-től van Power Query, amivel meglehet csinálni amit írtál.
A TEMP fájlban kell megcsinálni az egészet. Az alábbi főbb lépések kellenek:
1. Beolvastatni az IN fájlban lévő munkalapokat.
Itt bemutatják, hogyan lehet ezt megcsinálni.
2. TEMP fájlban lévő listát beolvastani.
3. A TEMP lekérdezést összekapcsolni (Merge/Egyesítés) az IN-el, bal külső (left outer) csatlakozással ha
van olyan ID ami csak TEMP-ben van, vagy belső (inner join) csatlakozás ha csak azok a sorok érdekelnek
amelyek mindkét fájlban benne vannak. Ezen YT video tud segíteni elmagyarázni a dolgokat.
4. Az eredmény kibontása.
5. Ha van olyan ID ami csak a TEMP-ben van, de az IN-ben nincs akkor egy plusz oszlop hozzáadása kell.
Ha viszont nincs ilyen ID, akkor felesleges oszlop törlése.Lehet hogy kínai amit fentit írtam, de érdemes Power Query-vel megismerkedni.
Ha Microsoft365-öd van akkor már lehet egy újabb függvényed, a FÜGG.HALMOZÁS (angolul VSTACK), képes tartományokat összefüzni, így FKERES/VLOOKUP (vagy az újabb XLOOKUP) is tudja használni. Azért ezzel sem lesz olyan egyszerű.
Excel 2010-nél regebbi változatnál makró tud segíteni a túl sok munkalap miatt.
Ha nem boldogulsz Power Query-ben, akkor dobd fel ide mintát/küldd át magánban.
üdv
-
Mutt
senior tag
válasz
andreas49
#49138
üzenetére
Esetleg ez. Sok cellát tartalmazó lapon nem lesz gyors.
Sub Tisztit()
Dim wsCurrent As Worksheet
Dim rngData As Range
Dim cella As Range
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each wsCurrent In Worksheets
Set rngData = wsCurrent.UsedRange
For Each cella In rngData
If Not cella.HasFormula Then cella = Trim(cella)
Next cella
Next wsCurrent
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub -
#49135
Mutt
senior tag
Fire/SOUL/CD
#49134
Mutt
senior tag
válasz
Fire/SOUL/CD
#49134
üzenetére
Szia,
Nem kell hozzá semmi extra, egyszerúen csak a makrós Excel-t el kell menteni Excel-bővítmény (.xlam) formátumban. Ilyenkor az addin munkafüzete "eltúnik" az applikációból, de a VBA Editorban továbbra is látható és az IsAddin tulajdonsága True-ra állítódik. Ha látni akarjuk az addin-hoz tartozó munkafüzetet (ami akkor lehet hasznos ha ott tárolunk adatot (pl. változók/köztes számítások eredményei) és azon változtani akarunk akkor ezt az IsAddin-t kell False állítani.
Amire egy addinnál érdemes figyelni azok a cella hivatkozások, az Activesheet és hasonlók kerülendők.
Amivel lehet még bővíteni, az hogy automatikusan eléthető legyen menüben. Ehhez tudom javasolni a http://www.cpearson.com/Excel/CreateAddIn.aspx oldalon található leírást.
üdv
-
Mutt
senior tag
válasz
Roli78
#49119
üzenetére
Szia,
Tedd a lenti kódot egy modulba és lesz egy plusz függvényed (FindMatches), amely megszámolja hogy az első paraméterként megadott tartomány soraiban szerepel-e a második tartomány minden száma vagy sem.
Function FindMatches(data As Range, val As Range) As Long
Dim row As Long, col As Long
Dim i As Long, j As Long
Dim arrData, arrValues
Dim strData As String
arrData = data
arrValues = val
i = 0
For row = 1 To UBound(arrData, 1)
strData = "|"
For col = 1 To UBound(arrData, 2)
strData = strData & arrData(row, col) & "|"
Next col
j = 0
For col = 1 To UBound(arrValues, 2)
If InStr(1, strData, "|" & arrValues(1, col) & "|") > 0 Then j = j + 1
Next col
If j = UBound(arrValues, 2) Then i = i + 1
Next row
FindMatches = i
End Function
üdv
-
#49123
Mutt
senior tag
BigBadPlaYeR
#49102
Mutt
senior tag
válasz
BigBadPlaYeR
#49102
üzenetére
Felraktam egy újabb változatot, amelyben lehet választani hogy minden lapon keressen-e vagy sem.
Mindig először az aktuális lapon keres, majd utána sorban nézi a többi lapot. A rejtett munkalapokat átugorja.
Pár apróbb módosítás is van benne.A makró a Windows registry-be menti a választott beállításokat, hogy ne kelljen minden induláskor megadni őket. A HKEY_CURRENT_USER\Software\VB and VBA Program Settings\<appname> alatt lehet megtalálni ezeket.
-
#49101
Mutt
senior tag
[DDAY]F4C3
#49094
Mutt
senior tag
válasz
[DDAY]F4C3
#49094
üzenetére
Szia,
Egy induló változatot feltöltöttem ide
https://github.com/viszi/codes/tree/master/Excel/Forum/SearchFormDuplaklikk a találati listán az adott cellához dob.
üdv
-
Mutt
senior tag
válasz
tgumis
#49068
üzenetére
Szia,
PQ-ben igazából az adattípust tudod állítani (szöveg, egészszám, törtszám, dátum stb), ami a könnyebb olvashatóság miatt hoz is egy formátumot (amit a Windows Területi beállításaiból vesz ki, de a LOCALE opcóval eltérhetsz tőle).
Amikor az eredményt munkafüzetre küldi a PQ, akkor ha nincs cellaformázás (vagyis General/Általános-ra) van állítva, akkor használja az alapot, különben pedig a cellaformátumát.A mintád alapján 2 dolog lehet:
1. 2385-ös sortól lefelé lehet hogy számformátum van a cellákra állítva.
2. Dátum adattípus konverziónál hiba történt és valójában nem dátum adattípust használ. Amikor kiküldöd a munkalapra az adatsort akkor a Connections/Kapcsolatok alatt kiírja hogy pl. 3500 rows loaded (with 500 errors).üdv
-
#49076
Mutt
senior tag
Lasersailing
#49051
Mutt
senior tag
válasz
Lasersailing
#49051
üzenetére
Szia,
Próbáld ki esetleg ezt. Mielőtt meghívod a fájl megnyitót jegyeztesd meg az aktuális fájlt és utána aktiváld vissza.
Dim wbCurrent As Workbook
Set wbCurrent = ThisWorkbook<itt hívd meg a főlapos dolgot>
wbCurrent.Activate
üdv
-
#49042
Mutt
senior tag
Lasersailing
#49041
Mutt
senior tag
válasz
Lasersailing
#49041
üzenetére
Kiírás után <userform>.Show esetleg?
-
-
Mutt
senior tag
válasz
andreas49
#49005
üzenetére
Szia,
Itt egy makrós változat, amely a KIJELÖLT oszlopon végzi el az átalakítást és mellé írja be a kapott értékeket. Mivel nem írtad hogy mi történjen ha a formátum nem követi az "abc (def) [ghi]" formátumot ezért ahogy nekem logikusnak tűnt írtam meg a kódot. A kommentek alapján próbáld meg módosítani.
Sub Szetszed()
Dim cella As Range
Dim adatsor As Range
Dim pos1 As Long, pos2 As Long, pos3 As Long
Dim text1 As String, text2 As String, text3 As String
'a kijelölt és adatokat tartalmazó tartományt használjuk csak
Set adatsor = Application.Intersect(Selection, ActiveSheet.UsedRange)
'végig megyünk a cellákon
For Each cella In adatsor
text1 = ""
text2 = ""
text3 = ""
'keressük a szövegben a zárójelet
pos1 = InStr(1, cella, "(")
'ha van zárójel akkor a csonkoljuk a szöveget a talált pozícióig
If pos1 > 0 Then
'ha van zárójel akkor a csonkoljuk a szöveget a talált pozícióig
text1 = Trim(Left(cella, pos1 - 1))
'keressük a szögletes árójelet
pos2 = InStr(pos1, cella, "[")
If pos2 > 0 Then
'ha van szögletes, akkor kivesszük a szöveget a zárójel utáni pozíciótól kezdve
text2 = Replace(Trim(Mid(cella, pos1 + 1, pos2 - pos1 - 1)), ")", "")
text3 = Replace(Trim(Mid(cella, pos2 + 1, Len(cella) - pos2)), "]", "")
Else
'nincs szögletes zárójel, de sima volt
pos2 = InStr(pos1, cella, ")")
text2 = Trim(Mid(cella, pos1 + 1, pos2 - pos1 - 1))
text3 = Trim(Mid(cella, pos2 + 1, Len(cella) - pos2))
End If
Else
'nem volt zárójel tartsuk meg az eredeti szöveget
text1 = cella
End If
'eredeti cella melletti oszlopokba írjuk az eredményt
cella.Offset(, 1) = text1
cella.Offset(, 2) = text2
cella.Offset(, 3) = text3
Next cella
End Subüdv
-
Mutt
senior tag
válasz
andreas49
#49000
üzenetére
Szia,
Van hibaüzenet?
Ez egy UDF (saját függvény) vagyis be kell írni a munkafüzeten, ha neked olyan makró kell ami automatikusan kitölti helyetted a többi oszlopokat, akkor az már sub-routine.
Magyar Excel-ben SZÖVEGFELOSZTÁS a függvény neve, lehet hogy csak INSIDER-ben van még.üdv
-
Mutt
senior tag
válasz
Salex1
#48995
üzenetére
Szia,
Itt az én változatom a felosztásra:
Sub Atrendez()
Dim wsCel As Worksheet
Dim adatok, bont, aktualis()
Dim c As Long, i As Long
Dim oszlopok As Long, oszlopBont As Long
Dim sor As Long
Dim ertekek As String
'erre a munkalapra másoljuk az értékeket
Const cel = "Munka2"
'ezen nevú oszlopot kell sorokba bontani
Const bontani = "AH"
'a fenti oszlopnevet számmá alaktjuk
oszlopBont = Cells(1, bontani).Column
'beolvassuk a teljes adatsort
adatok = ActiveSheet.Range("A1").CurrentRegion
oszlopok = UBound(adatok, 2)
'cél munkalap beállítása
Set wsCel = Worksheets(cel)
'esetleg létező adatok törlése a cél munkalapról
wsCel.Cells.Clear
'erre szükség lehet a 11ezer sor kiírásakor
Application.ScreenUpdating = False
sor = 1
'végig megyünk a beolvasott adatokon
With wsCel
For c = 1 To UBound(adatok)
'egy átmeneti tömbbe (aktualis) beolvassuk az adatokat soronként
ReDim aktualis(1 To oszlopok)
For i = 1 To oszlopok
aktualis(i) = adatok(c, i)
Next i
'a bontani kívánt oszlopot feldolgozzuk, előtte levesszük a [ és ] jeleket
ertekek = Replace(Replace(aktualis(oszlopBont), "[", ""), "]", "")
bont = Split(ertekek, "','")
'ha üres volt a bontani kívánt érték akkor csak 1 sort kell írnunk
If UBound(bont) < 0 Then
.Cells(sor, 1).Resize(, oszlopok) = aktualis
sor = sor + 1
Else
'ha nem volt üres akkor visszont ismételni kell egymás után a dolgokat
For i = 0 To UBound(bont)
.Cells(sor, 1).Resize(, oszlopok) = aktualis
.Cells(sor, oszlopBont) = Replace(bont(i), "'", "")
sor = sor + 1
Next i
End If
Next c
End With
Application.ScreenUpdating = True
End Subüdv
-
Mutt
senior tag
válasz
andreas49
#48996
üzenetére
Szia,
Tedd be a fájlba a lenti UDF-et, majd használd így:
Function TextPart(InputText, Optional Separator As String = " ", Optional PartStart As Long, Optional PartEnd As Long)
'Separator ha nincs megadva akkor szóközként értelmezzük
Dim arraySplit
Dim vFelsoMeret As Long
Dim i As Long
Dim txtResult As String
'szétszedjük a szöveget az elválasztójel alapján
arraySplit = Split(InputText, Separator)
'megnézzük hogy hány részre szedhető
vFelsoMeret = UBound(arraySplit)
If PartEnd = 0 Then PartEnd = PartStart
'ha az utolsó utáni darabot kérik, akkor is az utolsót adjuk
If PartEnd >= vFelsoMeret + 1 Then PartEnd = vFelsoMeret + 1
'ha a legelső darab előtti kell, akkor is az elsőt adjuk vissza
If PartStart <= 0 Then PartStart = 0
'megadjuk a kért részt
If PartEnd > PartStart Then
txtResult = ""
For i = PartStart To PartEnd - 1
txtResult = txtResult & arraySplit(i - 1) & Separator
Next i
TextPart = txtResult & arraySplit(PartEnd - 1)
Else
TextPart = arraySplit(PartStart - 1)
End If
End Functionüdv
Ps. Microsoft365-ben van már szövegdaraboló függvény is.
-
Mutt
senior tag
válasz
andreas49
#48984
üzenetére
Szia,
Ezt próbáld meg. A kommentek alapján tudod módosítani.
Sub vissza()
Dim wsTOC As Worksheet
Dim ws As Worksheet
Dim i As Long, c As Long
Dim result As Range
Dim back As Range
'a munkalap neve, ahíol megtalálhatók a lapok nevei, ezt javítsd a megfelelőre
Const TOC = "Start"
Set wsTOC = Worksheets(TOC)
'végig futunk a munkalapokon
For i = 1 To Worksheets.Count
Set ws = Worksheets(i)
'csak más nevű munkalapok érdekelnek
If ws.Name <> TOC Then
'keressük meg helyét a munkalapnak az összesítőn
Set result = wsTOC.Range("A:A").Find(ws.Name)
'ha nincs meg akkor ugorjuk át
If Not result Is Nothing Then
Set back = ws.Range("A2")
'ha üres lenne a cella akkor írjuk bele ezt, ha nem kell akkor töröld ki
If back = "" Then back = "vissza"
'ha már lenne hivatkozás, akkor töröljük a korábbit
If back.Hyperlinks.Count > 0 Then back.Hyperlinks.Delete
'adjuk hozzá a linket
back.Hyperlinks.Add Anchor:=back, Address:="", SubAddress:="'" & TOC & "'!" & result.Address
End If
End If
Next i
'nem kötelező de szüntessük meg az objektumokat
Set back = Nothing
Set result = Nothing
Set ws = Nothing
Set wsTOC = Nothing
End Subüdv
-
-
Mutt
senior tag
válasz
PistiSan
#47021
üzenetére
Szia,
Alapokhoz nekem sokat segített az "M is for Data Monkey" nevű könyv. Régi, de jól elmondja a dolgokat.
Microsoft hivatalos doksija segít a függvényekben eligazodni.
Youtube-on pedig Mike Girvin csatornája kiváló forrás, illetve Mynda csatornája.
üdv
-
Mutt
senior tag
Szia,
Ha Excel 2016-osod vagy frissebbed van, akkor a Power Query-vel lehet automatizálni.
A másik megoldás, hogy mivel az első 14 lapon mindig 65 ezer sor van fixen, akár képlettel is megoldható.
Lenne egy input.xls és egy template.xlsx nevű fájlom. Az input amit a program kiad. A template-ben pedig egy sima hivatkozás lenne egy lapon az input első lapjára, majd a 65 ezredik sor után a második lapra és így tovább. Az utolsó lap esetén pedig mivel nem lesz minden sor vmi ilyen hivatkozás lenne:
=HA([input.xls]Sheet15!A1<>"";[input.xls]Sheet15!A1;"")Ezek után csak az input fájlt kell cserélgetned és a template-ben kapott eredményt elmenteni értékek beillesztése után.
Mindkét megoldás hátránya, hogy formátumokat nem másol.
Ha kell formátum, akkor VBA.üdv
-
Mutt
senior tag
-
-
Mutt
senior tag
válasz
pube111
#46859
üzenetére
Szia,
Az eredeti kódban van pár hiba:
1.var calendarID = spreadsheet.getRange("C4").getValue();
C4-es cellára hivatkozik a kód, de a minta fájlban inkább B3-ban van a naptár-azonosító.
2.var eventCal = CalendarApp.getCalendarById("calendarId");
Itt 2 hiba is van; nem kell aposztrófok közé tenni a calendarId-t a végén és helyesen is kell írni a változó nevét, ami calendarID.Ezen hibák miatt nem fog tudni csatlakozni a naptárhoz, nem tud beleírni. Ez eredményezi a cannot read hibaüzenetet.
Egy kicsit átdolgoztam a kódot.
function scheduleShifts() {
//csatlakozás a google tablázatok aktív munkalapjához
const spreadsheet = SpreadsheetApp.getActiveSheet();
//a C2-es cellában lévő naptárazonosító kiolvasása
const calendarID = spreadsheet.getRange("C2").getValue();
//csatlakozás a fent megadott naptárhoz
const eventCal = CalendarApp.getCalendarById(calendarID);
//utolsó használt sor azonosítója
const lastRow = spreadsheet.getLastRow();
if (lastRow >= 5) {
//5-ös sortól kezdve kiolvassuk az adatokat
for (let i = 5; i <=lastRow; i++) {
//ha az aktuális sor D oszlopa üres, akkor A,B,C oszlopok alapján létrehozzuk az eseményt
if (spreadsheet.getRange("D" + i).getValue() == "") {
let title = spreadsheet.getRange("C" + i).getValue();
let startTime = spreadsheet.getRange("A" + i).getValue();
let endTime = spreadsheet.getRange("B" + i).getValue();
eventCal.createEvent(title, startTime, endTime);
//D-oszlopba teszünk egy x-et
spreadsheet.getRange("D" + i).setValue("x");
}
}
}
}A sablon hozzá pedig így néz ki:
A kód az 5-ös sortól indulva minden eseményt hozzáad a naptárhoz, ahol a kész oszlop üres.
A kommentek alapján tudod finomítani.üdv
-
Mutt
senior tag
válasz
PistiSan
#46639
üzenetére
Szia,
Azért bedobom a megoldást, hogy hogyan lehet más formátumban lévő adatokat PQ-vel megismertetni.
A Nyelvterület használata (angolul Locale) opció kell neked.pl. 82.5-ből 82,5-t így tudsz importnál varázsolni.
Jobb klikk az oszlopon adattípus változtatásához és ott válaszd az utolsó opciót, majd válaszd ki az országot amelynek a formátumát használja az adatsor.
Ha sok ilyen van, akkor a fájlra alapértelmezetté is teheted a Fájl -> Lehetőségek és beállítások -> Lekérdezés beállításai alatt.
Nálam jobban/látványosabban ebben a YT videoban is elmagyarázzák.
Az adatforrásban ahhoz hogy ne másszon el az adat érdemes egy aposztrófot tenni az adat elé, így szövegként értelmezve nem próbálja meg az Excel dátummá alakítani a számokat.
üdv
-
Mutt
senior tag
válasz
Hege1234
#46599
üzenetére
Szia,
Segédoszlop nélkül nem olyan könnyű, de próbáld ki ezt:
=if(row()-1<=counta($B$3:$C$16);textjoin(" - ";true;$A$1;if(index(FILTER($B$3:$B$16;len($B$3:$B$16)+len($C$3:$C$16));row()-1)="";C$2;B$2);
text(index(FILTER($A$3:$A$16;len($B$3:$B$16)+len($C$3:$C$16));row()-1);"HH:mm");index(FILTER($B$3:$C$16;len($B$3:$B$16)+len($C$3:$C$16));row()-1));"")
üdv
-
#46597
Mutt
senior tag
zsoltzsolt
#46592
Mutt
senior tag
válasz
zsoltzsolt
#46592
üzenetére
Word Körlevél funkció (angolul Mail merge) kell neked.
-
#46591
Mutt
senior tag
anonymus89
#46590
Mutt
senior tag
válasz
anonymus89
#46590
üzenetére
Szia,
CSV-be mentéshez nézd meg a https://github.com/viszi/codes/tree/master/Excel/Useful/Export2CSV oldalon található kódomat.
Ha addin-né konvertálod, akkor folyamatosan elérhető lesz és akár saját menübe is kirakható.
üdv
-
Mutt
senior tag
válasz
pube111
#46306
üzenetére
Szia,
Talán vmi ilyen kell:
function sendEmails() {
var alapDatum = new Date(2021,2,1); // 0-11 a hónapok számozása
var maiDatum = new Date();
var ss=SpreadsheetApp.getActiveSpreadsheet();
var cellaA1 = ss.getRange("A1").getValue();
if (cellaA1.length == 0 && maiDatum > alapDatum) {
var emailAddress = 'valami@gmail.com';
var message = 'üzenet';
var subject = 'tárgy';
MailApp.sendEmail(emailAddress, subject, message);
}
}üdv
-
Mutt
senior tag
válasz
Hege1234
#46303
üzenetére
Szia,
A többiek adtak már megoldást a lap elnevezésre.
A .toString() metódus egy dátumon UTC formátumot eredményez. A munkalap függvényes megoldás talán a legegyszerübb.Az A1:A2-t tartományt így tudod megkapni: getRange("A1:A2") vagy getRange(1,1,2)
Több cella esetén azonban a getValue() helyett getValues()-t kell használni, ami egy 2-dimenziós tömböt ad vissza. Az első sor első oszlopát a [0][0] pozíció tartalmazza, míg a második sor első oszlopát a [1][0]-ban találni.üdv
-
Mutt
senior tag
válasz
Hege1234
#46301
üzenetére
Szia,
Ezt próbáld ki:
function onEdit(e) {
var oldValue;
var newValue;
var ss=SpreadsheetApp.getActiveSpreadsheet();
var activeCell = ss.getActiveCell();
newValue=e.value;
oldValue=e.oldValue;
if (oldValue !== undefined && newValue !== undefined) {
activeCell.setValue(oldValue + ', ' + newValue);
}
}Másolás során mind az oldValue, mind a newValue értéke "undefined". Az undefined pedig false-nak feleltethető meg, ezt tagadva az eredeti kód a 10-es soron fut le, ami törli a cella értékét.
üdv
-
Mutt
senior tag
válasz
p5quser
#46249
üzenetére
Szia,
Set -el próbáld meg a hivatkozást.
For Each cb In ActiveSheet.OLEObjects
If TypeName(cb.Object) = "CheckBox" And cb.Object.Value = True Then
Set cbi = ActiveSheet.OLEObjects(Replace(cb.Name, "CheckBox", "TextBox"))
Debug.Print cbi.Object.Text
Set cbi = Nothing
End If
Next cbüdv
-
Mutt
senior tag
válasz
leroyclub
#46250
üzenetére
Szia,
Az általad megadott megoldás, csak a legfrissebb Microsoft 365-ben használható.
Excel 2016-ban van Power Query (Adatok -> Adatok beolvasása -> Más forrásokból -> Weblapról), amivel pl. a https://www.napiarfolyam.hu/%C3%A1rfolyam/bitcoin/ oldalból lehet kinyerni az árfolyamot.
Pl. a BTC/HUF-ot így lehet megtalálni (nekem csak az oldal forrását nézve sikerült megtalálnom a megfelelő alelemet).
Lekérdezést ezek után kézzel, időzítve vagy akár a fájl megnyitásával is lehet frissíteni.
üdv
-
Mutt
senior tag
Szia,
Abban kéne segítség, hogy az megcsinálható, hogy kijelölök egy oszlopban cellákat, amikben van szöveg, és szövegek elég tegyen be sorszámokat a kijelölt cellákba.
Az alábbi makró tud segíteni, a kommentek alapján szerintem te is tudsz rajta igazítani.
Beletettem egy plusz opciót hogy tömegesen el lehessen távolítani a sorszámot.Sub Sequencing()
Dim num As Long
Dim changedCells As Long
Dim selectionArea As Range
Dim currentCell As Range
'kijelölés megjegyzése
Set selectionArea = Selection
'beviteli mező hogy lehessen a sorszámot megadni
num = Application.InputBox(Prompt:="Kezdő sorszám (-1 esetén törli a sorszámot): ", Title:="Számozás", Default:=1, Type:=1)
'mégsem esetén álljunk le
If num = 0 Then
Exit Sub
End If
For Each currentCell In selectionArea
'csak olyan cellák érdekelnek amelyek nem üresek és képletet sem tartalmaznak
If currentCell.Value <> "" And currentCell.HasFormula = False Then
If num = -1 Then
'töröljük a cella elejéről a sorszámot ha van
currentCell.Value = RemoveTrailingNumbers(currentCell.Value)
changedCells = changedCells + 1
Else
'hozzáadjuk a sorszámot a cella elejére
currentCell.Value = num & ". " & currentCell.Value
num = num + 1
changedCells = changedCells + 1
End If
End If
Next currentCell
'visszajelzés
If changedCells = 0 Then
MsgBox "Nincs módosítás", vbOKOnly, "Számozás"
Else
MsgBox changedCells & " cella lett változtatva", vbOKOnly, "Számozás"
End If
End Sub
Function RemoveTrailingNumbers(s As String) As String
Dim regEx As Object
Set regEx = CreateObject("VBScript.RegExp")
'^ - csak a szöveg elején lévő dolgokat nézi
'\d+ - számjegy ami legalább egyszer megtalálható
'\. - pontot keresük
'\s* - whitespacet (szóköz, tab, sortörtés) keresünk
regEx.Pattern = "^\d+\.\s*"
RemoveTrailingNumbers = regEx.Replace(s, "")
End Function
üdv -
Mutt
senior tag
Szia,
Microsoft 365 Insider-ben elérhetők a LAMBDA és LET függvények, amelyekkel megoldható a probléma.
A névkezelőben a FLIP függvényt hoztam létre, amelynek az a képlete:=LAMBDA(
ertek;
elvalaszto;
LET(
hosszusag;HOSSZ(ertek);
mennyi;1+(hosszusag-HOSSZ(HELYETTE(ertek;elvalaszto;"")))/hossz(elvalaszto);
kezdo;HAHIBA(ÖSSZESÍT(15;6;SORSZÁMLISTA(hosszusag)/(KÖZÉP(ertek;SORSZÁMLISTA(hosszusag);1)=bal(elvalaszto;1));SORSZÁMLISTA(mennyi)-1);0);
vege;HAHIBA(ÖSSZESÍT(15;6;SORSZÁMLISTA(hosszusag)/(KÖZÉP(ertek;SORSZÁMLISTA(hosszusag);1)=bal(elvalaszto;1));SORSZÁMLISTA(mennyi));hosszusag+1);
szoveg;KIMETSZ(KÖZÉP(ertek;kezdo+1;vege-kezdo-1));
ures;SZÖVEG.KERES(" ";szoveg);
SZÖVEGÖSSZEFŰZÉS(elvalaszto&" ";IGAZ;KIMETSZ(KÖZÉP(szoveg;ures+1;hosszusag)&" "&BAL(szoveg;ures)))))
Természetesen VBA-val is meg lehet oldani:
Function FlipVBA(ertek As Range, elvalaszto As String) As String
Dim fSplit1, fSplit2, data
Dim result As String, flipped As String
Dim c As Long
fSplit1 = Split(ertek, elvalaszto)
For Each data In fSplit1
flipped = ""
fSplit2 = Split(Trim(data))
For c = UBound(fSplit2) To 0 Step -1
If flipped = "" Then
flipped = Trim(fSplit2(c))
Else
flipped = flipped & " " & Trim(fSplit2(c))
End If
Next c
If result = "" Then
result = flipped
Else
result = result & elvalaszto & " " & flipped
End If
Next data
FlipVBA = result
End FunctionVégül pedig Power Queryvel is megoldható. A lényeg ott ez a képletsor:
Text.Combine(List.Reverse(Text.Split([Szöveg]," "))," ")üdv
-
Mutt
senior tag
válasz
kingzazza
#45493
üzenetére
Szia,
Szóval bruttósított összegnek kell pont 5-re vagy 0-ra végződnie kerekítés nélkül?
A 27%os áfánál csak minden 500-al osztható szám fog 5-re vagy 0-ra végződni kerekítésmentesen.
500 nettó esetén a bruttó 635, 1000-nél pedig 1270 lesz az érték.A korábban mutatott 10 EUR - 350 HUF árfolyam és -200 Ft árkülönbözet során a nettó érték 2598 Ft, amihez a legközelebbi 500-al osztható szám a 2500 Ft. Aminek a bruttója pedig 3175 Ft.
A gond ahogy látod, hogy ekkor viszont az árkülönbözet (325 Ft) nagyobb mint a megengedett.Így a megadott feltételek alapján soha nem lesz helyes megoldás.
A képlet azért: =TÖBBSZ.KEREKÍT((bruttó-225)/1,27;500)*1,27üdv
-
Mutt
senior tag
válasz
tylerddd
#45490
üzenetére
Szia,
Az ösztöndíj számításnál a többszörös HA-ba bele fogsz őrülni, de ha mégis kell, akkor legnagyobb értéktől kell menned lefelé. Vagyis előbb nézni, hogy az érték 4,8-nál nagyobb ha igen akkor összeg, ha nem akkor 4,6-nál nagyobb-e és így tovább.
Excel 2016-at jött az IFS (magyarul HAELSŐIGAZ) függvény, amivel kicsit könnyebb, de még így is ronda és könnyen elrontható:
pl. =HAELSŐIGAZ(D2>=4,8;35000;D2>=4,6;30000;D2>=4,4;25000;D2>=4,2;20000;D2>=4;15000;D2>=3,8;10000;D2>=3,6;5000;D2>=0;0)Jobb megoldás keresés közelítő találatra a segédtáblában.
=FKERES(D2;$A$26:$B$33;2)A lényeg, hogy a 4-ik paraméter üres/hamis ami az FKERESnek (VLOOKUP) azt mondja hogy nem kell pontos találatot keresnie elég egy közelit. Azonban ilyenkor a segédtáblának rendezettnek kell lennie (pont ahogy a mintán van.)
üdv
-
Mutt
senior tag
válasz
Smogus
#45431
üzenetére
Szia,
... folyamatos szűrést csinálnom, hogy az egyik munkalapon lévő sorok közül azok listázódjanak egy másik munkalapon, amelyeknek egy cellájában a "rögzíteni kell" felirat olvasató.
... úgy hogy semmilyen szűrést vagy makrót nem futat le a másik fülön.Ha a felhasználók saját profillal használják a gépeket (vagyis mindenki saját felhasználónév és jelszópárossal lép be a Windowsba), akkor egy Excel 4.0-ás képlettel (MUNKATERÜLETET.VESZ(26) vagy angolul a GET.WORKSPACE(26)) ki lehet olvasni az aktuális profil nevet és ezt fel lehet használni a szűréshez.
Az alábbi képen a koncepció látszik. Van 3 lap, 3 felhasználó névvel. Az Excel4.0-ás képlet a névkezelőben az ActiveUser változóhoz van rendelve. Majd pedig az egyszerűség kedvéért egy Office365-ben található függvénnyel le van szűrve a lista az megfelelő felhasználóval. Korábbi Excel változatotkban ÖSSZESÍT/INDEX párosok kellenek.
üdv
-
Mutt
senior tag
Szia,
Úgy néz ki, hogy a SZÖVEGÖSSZEFŰZÉS függvény hiányzik az Exceledből. Tudod helyettesíteni a FŰZ függvénnyel, de ekkor CTRL + SHIFT + ENTER-el kell bevinned a képletet.
=FŰZ(ÖSSZESÍT(15;6;SOR(INDIREKT("A"&MIN(A:A)&":A"&MAX(A:A)))/(1-SZÁM(HOL.VAN(SOR(INDIREKT("A"&MIN(A:A)&":A"&MAX(A:A)));A:A;0)));SOR(INDIREKT("A1:A"&MAX(A:A)-MIN(A:A)+1-DARAB(A:A))))&",")üdv
-
Mutt
senior tag
A másik fórumon jelent meg egy kérés, hogy hogyan lehetne egy számokat és szövegeket tartalmazó cellából a csak a számokat vagy szövegeket kinyerni.
Makró mentesen tömbfüggvénnyel és Power Queryvel is meg lehet oldani a dolgot.
Képlet B1-ben (Excel 2016 kell hozzá legalább):
=SZÖVEGÖSSZEFŰZÉS("";IGAZ;HA(SZÁM(--KÖZÉP(A2;SOR(INDIREKT("A1:A"&HOSSZ(A2)));1));KÖZÉP(A2;SOR(INDIREKT("A1:A"&HOSSZ(A2)));1);""))Képlet C1-ben:
=BAL(A2;HOL.VAN(HAMIS;SZÁM(--(KÖZÉP(A2;SOR(INDIREKT("A1:A"&HOSSZ(A2)));1)));0)-1)Képlet D1-ben (ugyanaz mint a B1, csak a HA(SZÁM részben van az IGAZ-HAMIS feltétel felcserélve):
=SZÖVEGÖSSZEFŰZÉS("";IGAZ;HA(SZÁM(--KÖZÉP(A2;SOR(INDIREKT("A1:A"&HOSSZ(A2)));1));"";KÖZÉP(A2;SOR(INDIREKT("A1:A"&HOSSZ(A2)));1)))Képlet E1-ben:
=KÖZÉP(A2;HOL.VAN(HAMIS;SZÁM(--(KÖZÉP(A2;SOR(INDIREKT("A1:A"&HOSSZ(A2)));1)));0);5000)üdv
-
Mutt
senior tag
Szia,
A kód elején a Kezd = Cells(8, 7) rész azt mondja, hogy a G8-as cellában lévő érték legyen a Kezd változó induló értéke, de ahogy a minta képből kivehető az a cella üres, így a Kezd-nek nem lesz értéke és ez el kihat a többi változóra is.
Kell egy ellenőrzés hogy üres/nem számot tartalmazó esetben mi történjen.
pl.If IsEmpty(Kezd) Or Not IsNumeric(Kezd) Then Kezd = 1üdv.
Ps.
Furcsa, hogy az UDF-ben megadsz egy vizsgálandó tartományt (Feltétel2) de azt nem használod fel sehol. -
Mutt
senior tag
Másik fórumon a kérdés az volt, hogy van-e olyan függvény amely megadja hogy egy listából mely számok hiányoznak.
pl. 1, 2, 4 esetén a 3-as hiányzik
Hogy hány szám hiányzik egy növekvő számsorból azt meg lehet kapni az alábbi képlettel
= (maximum érték - minimum érték) / lépésköz + 1 - számok darabszáma
A fenti esetben = (4-1) /1 + 1 - 3 = 1 db szám hiányzik.Ha csak 1 db hiányzik, akkor 2 HOL.VAN segít a megadni a hiányzó számot.
=HOL.VAN(HAMIS;SZÁM(HOL.VAN(SOR(INDIREKT("$A"&MIN(A:A)&":$A"&MAX(A:A)));A:A;0));0)+MIN(A:A)-1
Ha több hiányzik, akkor többet kell küzdenünk hogy egy cellában megjelenjenek a számok.
=SZÖVEGÖSSZEFŰZÉS(",";IGAZ;ÖSSZESÍT(15;6;SOR(INDIREKT("$A"&MIN(A:A)&":$A"&MAX(A:A)))/(1-SZÁM(HOL.VAN(SOR(INDIREKT("$A"&MIN(A:A)&":$A"&MAX(A:A)));A:A;0)));SOR(INDIREKT("$A1:$A"&MAX(A:A)-MIN(A:A)+1-DARAB(A:A)))))
Mindegyik esetben a SOR(INDIREKT("$A"&MIN(A:A)&":$A"&MAX(A:A)) függvény előállítja a teljes számsort a kezdő és végszám között egyesével (ha más lépésköz kellene, akkor Excel365-ben a SORSZÁMLISTA tudna segíteni).
A belső HOL.VAN megnézi hogy az előbb létrehozott számok az A-oszlopban hol találhatóak. Amelyik hiányzik ott hibát ad vissza, amelyet a SZÁM függvény HAMIS értékre fog lefordítani.
A külső HOL.VAN megnézi hogy hanyadik elemre kaptunk HAMIS értéket. Ezt a pozíciót hozzáadva a kezdőszámhoz megkapjuk hogy melyik hiányzott. -
#45334
Mutt
senior tag
m.zmrzlina
#45324
Mutt
senior tag
válasz
m.zmrzlina
#45324
üzenetére
Szia,
Most olvasva végig a leírásodat, ugyanazt csinálod amit én is javaslok és napi szinten használok. Nincs jobb megoldás, kivétel ha Office Script-re térnél át mert ott simán lehet tömbök méretét növelni.
2 megoldást tudok javasolni.
1. A kiTömb csak 1-dimenziós legyen és a beTömb indexét tartalmazza. Amikor pedig íratsz ki, akkor a kiTömb-ből kapott index-el a beTömb-ből olvasod ki az értékeket.
Memóriában nem fog sok helyet foglalni, lassitani sem igen fogja a feldolgozást, egyedül csak a kiírás lesz lassabb mivel nem tudod egy lépésben a tömb tartalmát kiírni. (Ez a megoldás nálam egy 3 percig futó makróból 2 percet vett el, szóval nem ideális ha sokat kell a lapon dolgozni. A 2-es opcióval gyors kiíratást elérsz, de oda kell figyelni a helyes indexek használatára!)2. Ne legyen probléma hogy a nem fixelt definiált tömbnek csak az utolsó méretét lehet változtatni. Képzeld el, hogy ez a tömb 90 fokkal el van forgatva az eredetihez képest. Az első sor innentől az első oszlopban lesz, a második sor a második oszlopban és így tovább. A kódod ilyenkor csak a hivatkozásban változik.
Az alábbi minta kód a kék listából kiszűri az adatot, egy dinamikusan változó tömbbe.
A sárga a dinamikus tömb eredeti (inverz) állapotát mutatja, de azt vissza lehet könnyedén konvertálni.
Sub ReDIM_Minta()
Dim minta As Range
Dim beTomb()
Dim kiTomb()
Dim oszlopok As Long, sorok As Long, i As Long, j As Long
Set minta = ActiveSheet.Range("A1").CurrentRegion
oszlopok = minta.Columns.Count
sorok = minta.Rows.Count
'erre nincs szükség, de látható hogy sorok és oszlopok szerint van a beTömb
ReDim beTomb(1 To sorok, 1 To oszlopok)
'adatok betöltése a tömbbe
beTomb = minta
'kiTomb-öt állítsuk be hogy annyi "sora" legyen mint az erdeti oszlop szám
ReDim kiTomb(1 To oszlopok, 1 To 1)
'az első sor a beTomb-ben egy fejléc másoljuk be a kitömb-be
For i = 1 To oszlopok
'itt látszik hogy csak az index sorrendet kell felcserélni
kiTomb(i, 1) = beTomb(1, i)
Next i
'szűréssel a nőket tartalmazó rekordokat tegyük be a kiTömb-be
For i = 2 To sorok
'ha a beTomb 4. oszlopában N van akkor
If beTomb(i, 4) = "N" Then
'növeljük a kiTomb utolsó dimenzióját 1-el
ReDim Preserve kiTomb(1 To oszlopok, 1 To UBound(kiTomb, 2) + 1)
'bemásoljuk az adatokat a beTomb-ből
For j = 1 To oszlopok
kiTomb(j, UBound(kiTomb, 2)) = beTomb(i, j)
Next j
End If
Next i
'konvertálatlan dump - sárga
ActiveSheet.Range("F1").Resize(UBound(kiTomb, 1), UBound(kiTomb, 1)) = kiTomb
'konvertált dump - zöld
ActiveSheet.Range("F10").Resize(UBound(kiTomb, 2), UBound(kiTomb, 1)) = Application.Transpose(kiTomb)
End Subüdv
-
Mutt
senior tag
válasz
lrobertoc
#45330
üzenetére
Szia,
Power Query-ben az "Elemi értékekre bontás" (angolul Unpivot) kell neked. Kijelölöd az első három fix oszlopot és, majd Átalakítás -> Többi oszlop elemi értékre alakítása opciót választod.
Ha a hónapokat tartalmazó szövegből pedig igazi dátumot akarsz, akkor kijelölöd az oszlopot és Átalakítás -> Dátum -> Elemzés (vagy ha új oszlopot akarsz akkor Oszlop hozzáadása -> Dátum -> Elemzés-t válaszd).
üdv
-
Mutt
senior tag
válasz
woolwich
#45303
üzenetére
Szia,
Nyiss egy üresl Excel fájlt és hivatkozz a másik fájl cellájára benne.
Ez a cella értéket (formátum és képletek nélkül) fogja megjeleníteni, még a rejtett cellákon/oszlopokon/sorokon is működik.Ha a rögzítést kiveszed a hivatkozásból, akkor másolva a képletet gyorsan megkapod a másik fájl tartalmát.
üdv
-
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
oszlopokat:
Új hozzászólás Aktív témák
Hirdetés
Fferi50- Kutya topik
- Milyen asztali (teljes vagy fél-) gépet vegyek?
- Dell notebook topic
- Direct One (műholdas és online TV)
- Háztartási gépek
- A Micron újszerű módszerrel javítja QLC-s SSD-jének sebességét
- Windows 11
- Bestbuy játékok
- Xbox tulajok OFF topicja
- sziku69: Fűzzük össze a szavakat :)
- További aktív témák...
- ROBUX ÁRON ALUL - VÁSÁROLJ ROBLOX ROBUXOT MÉG MA, ELKÉPESZTŐ KEDVEZMÉNNYEL (Bármilyen platformra)
- Microsoft licencek KIVÉTELES ÁRON AZONNAL - UTALÁSSAL IS AUTOMATIKUS KÉZBESÍTÉS - Windows és Office
- Számlás!Steam,EA,Epic és egyébb játékok Pc-re vagy XBox!
- Kaspersky, McAfee, Norton, Avast és egyéb vírusírtó licencek a legolcsóbban, egyenesen a gyártóktól!
- Gyermek PC játékok
- MacBook felvásárlás!! MacBook, MacBook Air, MacBook Pro
- Telefon felvásárlás!! Apple Watch Series 9/Apple Watch Ultra/Apple Watch Ultra 2
- BESZÁMÍTÁS! MSI B450M R5 5500 32GB DDR4 512GB SSD RTX 3060 12GB Rampage SHIVA Chieftec 600W
- Telefon felvásárlás!! Samsung Galaxy A16, Samsung Galaxy A26, Samsung Galaxy A36, Samsung Galaxy A56
- Lenovo Legion 5 Gaming. Az ár irányár, komoly érdeklődés esetén van lehetőség egyeztetésre
Állásajánlatok
Cég: Promenade Publishing House Kft.
Város: Budapest
Cég: PCMENTOR SZERVIZ KFT.
Város: Budapest