- Prohardver app (nem hivatalos)
- Samsung Galaxy A54 - türelemjáték
- Samsung Galaxy S23 és S23+ - ami belül van, az számít igazán
- Apróbb óra érkezik a OnePlustól
- Bemutatkozott a Poco X7 és X7 Pro
- Red Magic 10 Air - gaming telefon is lehet kecses
- iPhone topik
- Egyszerre legnagyobb és legkisebb is a Garmin Venu X1
- Rekord vékony lesz a Z Flip7 is
- Samsung Galaxy Watch7 - kötelező kör
-
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
gborisz
#53158
üzenetére
Szia,
Megnéztem a fájlt és csak egy apró probléma van vele. A harmadik lépés nem az előtte lévőre, hanem a legelsőre (a Forrás nevűre) hivatkozik, ahol még nem történt meg típuskonverzió.
Csak annyit kell csinálnod, hogy a szerkesztőlécben a Table.RenameColumns utáni "Forrás"-t, ami az első lépés neve lecseréled a másodikkal. Mivel annak a nevében van szóköz, ezért hashtaggel (#) kell kezdened és utána zárójelekben megadnod a lépés nevét, vagyis #"Típus módosítva" kell.
A Power Query makrószerűen dolgozik, az egymás után megadott lépéseket hajtja végre. Általában az újabb lépés bemenete az előző kimenete. Alapból minden függvény első paramétere az előző lépés neve, de itt lehetséges hogy nem mindig az előző lépés eredményét használjuk fel a következőben.
Tipikus példa szokott lenni amikor sok adat esetén a Table.Buffer-el memóriába tesszük az eredmény táblát és késöbbi lépésekben erre hivatkozunk vissza.
Egy másik fontos tudnivaló inkább csak az M-kódban látszik, ami
let -el kezdődik (kivével ha UDF-et csináltunk) és in-el végződik. Az in után megadott lépés neve lesz az eredmény, amely nem kötelező hogy az utolsó lépésé legyen. (Perverz módon meglehet adni az első, többnyire Forrás/Source nevű lépést is.)
Ez többnyire teszteléskor/fejlesztéskor hasznos, amikor a további átalakítások bizonytalanok. Ha vki ilyenben gondolkodik, akkor jobb megoldás a lekérdezés duplikálása hivatkozással (reference), ami az eredeti lekérdezés eredményéből indul ki.üdv
-
Mutt
senior tag
válasz
eszgé100
#53146
üzenetére
Szia,
...egyiket kovetve sem jartam sikerrel.
Mi történik a te esetedben? M-kódot tudod mutatni az első queryhez, ami az eredeti forrásfájlból dolgozik?Mindegyik video ugyanazt magyarázza el, vagyis hogy:
1. egy query-vel elkészíted a kommentek nélküli változatot, amit betöltesz egy munkalapra.
2. hozzáadod a szükséges oszlopokat a frissen betöltött táblázathoz, majd ezt a táblázatot is betöltöd Power Query-be és kijösz Power Query-ből úgy hogy ezt csak kapcsolatként (Close and load -> Only create connection) töltöd be
3. Visszamész Power Query editorba és az eredeti lekérdezésben állva összefűződ (Home -> Merge Queries) azt a másodikkal (amiben van a komment és csak kapcsolatként él).
4. Kibontod az új oszlopokat az összefűzés után.
5. Close and Load-al visszamész Excelbe, ahol duplán lesznek az új oszlopok. Tőrlőd a végéről a duplikáltakat.A videokról egy kis észrevétel:
1. Egyik sem hangsúlyozza igazán de kell egy kulcs mező ami alapján meg lehet találni a két helyen az azonos sorokat. Mindegyik esetben van egy ID oszlop, de PQ esetén akár több oszlop is használható elsődleges kulcsként.
2. A második videót kerüld, addig amíg M-kód közvetlen szerkesztésében nem vagy jártas.
3. A harmadikban szereplő oktató megbízható (mindhárom video helyes megoldást ad ettől függetlenül).Amit lehetne finomítani - főleg ha nagy adatsorod van - hogy a második query csak a kulcsmezőket és a kommenteket tartalmazza, minden egyéb adat csak a memóriát eszi feleslegesen, de ez minimális dolog.
üdv
-
Mutt
senior tag
Szia,
Neked kell sorba rendezni a dátumokat, erre van több megoldás is. A QuickSort elég gyors nagyobb adatsoron is.
Én még annyit kavartam, hogy ha előfordulnának ismétlődő szabad dátumok, akkor azt egy collection-el előbb kiszűrtem.
Private Sub FillDates2()
Dim ws As Worksheet
Dim cell As Range
Dim greenColor As Long
greenColor = RGB(0, 204, 102)
Set ws = ThisWorkbook.Sheets("2025")
Dim datumokColl As New Collection 'collection esetén csak egyedi értékek maradnak meg
Dim datumokArr() 'majd ebbe a tömbbe másoljuk át a kapott értékeket
Dim c As Long
On Error Resume Next 'collection leáll ha duplikáció van, így átugorjuk ezt
For Each cell In ws.UsedRange
If cell.Interior.Color = greenColor And IsDate(cell.Value) Then
datumokColl.Add cell.Value, CStr(cell.Value)
End If
Next cell
On Error GoTo 0
'ha van szabad dátum akkor lehet tovább menni
If datumokColl.Count > 0 Then
'a szabad dátumokat egy tömbbe kell másolni, létrehozzuk a megfelelõ méretû tömböt
ReDim datumokArr(1 To datumokColl.Count)
'átmásoljuk a collection tartalmát a tömbbe
For c = 1 To datumokColl.Count
datumokArr(c) = datumokColl(c)
Next c
'növekvõ sorba rendezzük a dátumokat
Call QuickSort(datumokArr, 1, datumokColl.Count)
'comboxhoz adjuk a dátumokat
For c = 1 To UBound(datumokArr)
Me.ErkezesiDatum.AddItem Format(datumokArr(c), "yyyy.mm.dd")
Me.TavozasiDatum.AddItem Format(datumokArr(c), "yyyy.mm.dd")
Next c
End If
End Sub
'https://stackoverflow.com/questions/152319/vba-array-sort-function
Public Sub QuickSort(vArray As Variant, inLow As Long, inHi As Long)
Dim pivot As Variant
Dim tmpSwap As Variant
Dim tmpLow As Long
Dim tmpHi As Long
tmpLow = inLow
tmpHi = inHi
pivot = vArray((inLow + inHi) \ 2)
While (tmpLow <= tmpHi)
While (vArray(tmpLow) < pivot And tmpLow < inHi)
tmpLow = tmpLow + 1
Wend
While (pivot < vArray(tmpHi) And tmpHi > inLow)
tmpHi = tmpHi - 1
Wend
If (tmpLow <= tmpHi) Then
tmpSwap = vArray(tmpLow)
vArray(tmpLow) = vArray(tmpHi)
vArray(tmpHi) = tmpSwap
tmpLow = tmpLow + 1
tmpHi = tmpHi - 1
End If
Wend
If (inLow < tmpHi) Then QuickSort vArray, inLow, tmpHi
If (tmpLow < inHi) Then QuickSort vArray, tmpLow, inHi
End Subüdv
-
Mutt
senior tag
válasz
gborisz
#53123
üzenetére
Szia,
Az adatsorod tizedespontot használ, miközben magyar Excel-t használsz, ahol tizedesvessző van, ezért szövegnek maradnak a számok.
Egyik megoldás a nyelvterület megadása a második lépésben. A zárójel elé írd be ezt: , "en-US"
Ha ez nem megy (túl régi az Exceled), akkor egy lépés kell a típus módosítás elé, ahol a tizedespontot vesszőre cseréljük. Jobb oldalt a Forrást kijelölöd, majd a 3 oszlopot ahol a törtszámok vannak, majd Kezdőlapon az értékek lecserélése (angolban Home -> Replace values) ablakban a pontot vesszőre cseréled.
Ezek után a típusot már a nyelvterület nélkül rendben fogja megismerni a PQ.
üdv
-
Mutt
senior tag
válasz
gborisz
#53117
üzenetére
Szia,
Power Query-hez az M-kód ennyi:
let
Forrás = Csv.Document(File.Contents("D:\a.txt"),[Delimiter="#(tab)", Columns=4, Encoding=1250, QuoteStyle=QuoteStyle.None]),
#"Típus módosítva" = Table.TransformColumnTypes(Forrás,{{"Column1", Int64.Type}, {"Column2", type number}, {"Column3", type number}, {"Column4", type number}}),
#"Oszlopok átnevezve" = Table.RenameColumns(#"Típus módosítva",{{"Column1", "Pontszám"}, {"Column2", "Koord1"}, {"Column3", "Koord2"}, {"Column4", "Magasság"}})
in
#"Oszlopok átnevezve"Az első sorban látható hogy a D: meghajtóról olvassa be az a.txt-t ezt kell átirni a kódban (újabb PQ változatokban ezt máshogy is lehet módosítani.
A munkafüzetben egy új lapot hoz létre az adatokkal (esetemben az új lap neve "a"), amiket az INDEX függvénnyel lehet a végső munkalap celláiba írni.
A2-ben a képlet:
=INDEX(a!$A:$A;SOR())
C2-ben=INDEX(a!$B:$B;SOR())
és igy tovább.A 12-es sortól pedig jönnek a lehetséges többi adatok.
A12-ben a képlet:=HA(SOR()-6>DARAB2(a!$A:$A);"";INDEX(a!$A:$A;SOR()-6))
Itt figyeljük, hogy van-e a másik lapon még adat és az elcsúszott sorszámot is módosítjuk.
A képletek lemásoljuk az első száz sorig és meg is van.Ha van új adat, akkor az a.txt fájlt felülírjuk és a Power Query menüben található frissítést megnyomjuk.
üdv
-
Mutt
senior tag
válasz
gborisz
#53117
üzenetére
Szia,
Ismétlődő feladatok automatizálására két lehetőség is van.
1. Makró használata. Akár fel is veheted a lépéseket, amit mutattál ahhoz a rögzítés is jó lesz.
2. Power Query 2013hoz még telepíteni kell innen.
Youtubeon van jó pár video txt fájl beolvasására. Pl. https://www.youtube.com/watch?v=FLzKnNmE4MsÜdv
-
Mutt
senior tag
válasz
Fferi50
#53093
üzenetére
Szia,
LAMBDA függvény nem debugolható a hagyományos képletkiértékelővel. Két nem tökéletes megoldás van:
1. Office áruházból az Excel labs kiegészítő.
2. Excel Lambda Explorer a https://www.decisionmodels.com/FastExcellambdaexplorer.htm oldalról.Nálad azért fut hibára a képlet, mert a LAMBDA-n belüli INDEX-ben az első sort is megadtad ami fejléc és nem alakitható számmá. Vagy a 2-es sortól indul a tartomány vagy current+1 -et használj.
Az elején a SORSZÁMLISTA-nál felesleges a transzponálás. A pontosvesszővel (angol Excelben sima vessző) elválasztott lista SOR listát eredményez (az értékek soronként jelennek meg). A backslash-el (\, Amerikai angol nyelvterületű Excelben pontosvesszővel) felsorolt lista OSZLOP listát ad.
SORSZÁMLISTA(8) és SORSZÁMLISTA(;8) egyszerübben megadja neked ami kell.üdv
-
#53092
Mutt
senior tag
Fire/SOUL/CD
#53090
Mutt
senior tag
válasz
Fire/SOUL/CD
#53090
üzenetére
Ilyen már volt korábban is az OFFSET függvénnyel. Excel 2010 előtt OFFSZET volt, csak azóta ELTOLÁS a magyar neve.
Majd rájönnek hogy a sok új függvényt kellhet lokalizálni. -
Mutt
senior tag
válasz
Fferi50
#53089
üzenetére
Sehogy nem kell, mert a LAMBDA első paramétere(I) mindig a változó(k), az utolsó pedig egy művelet amely használhatja a korábbi változó(ka)t.
A REDUCE a Javascriptben található azonos nevű array method-ot akarja másolni (sajna itt hiányzik az ott meglévő két hasznos belső változó az index és a teljes tömb), ahol az első paraméter mindig az eredmény változó, a második pedig a tömb aktuális elemének értéke. A LAMBDA ezen sorrendben kapja meg a REDUCE tól az értékeket.
-
#53088
Mutt
senior tag
Lasersailing
#53085
Mutt
senior tag
válasz
Lasersailing
#53085
üzenetére
Szia,
Hogy szokjuk az új függvényeket is, itt egy MS365-ben műkődő változat.
D7-ben van az oszlop szám. A SEQUENCE magyar megfelelője SORSZÁMLISTA.=REDUCE(0;SEQUENCE(INDEX($A$1:$ZZ$1;;$D$7));LAMBDA(state;current;state+INDEX($A$2:$ZZ$10000;current;$D$7)))üdv
-
Mutt
senior tag
Szia,
A https://learn.microsoft.com/en-us/officeupdates/ linken olvashatsz a különböző verziók frissítéséről. Ahogy nézem augusztus 13-án adták ki az utolsó frissítését több verzióhoz, de a géped nem biztos hogy azonnal telepítette.
Ha nincs automatikus újraszámolás, akkor előfordulhat hogy egy olyan munkafüzetet nyitottál meg amiben ez volt beállítva és ez program színtű beállítás, vagyis minden más füzetre érvényessé válik. Ellenőrízd, hogy a Képletek -> Számolási beállítások alatt az automatikus legyen aktív.
Ha lefagy (lassú) az Excel akkor lehetséges még 3 dolog:
1. a munkafüzetben túl sok cella van, ellenőrízd le a munkafüzet minden lapján végigmenve az utolsó aktív cellát. Nyomj F5-öt majd az ablakban válaszd az Irányított gombot és ott az utolsó cellát, OK-ra elvisz ahhoz a cellához. Lehet hogy olyan oszlophoz és sorhoz ahol soha és előtte sem volt adat. Ilyenkor vagy törlöd a valósan használt cellák ls az utolsónak mondott cella közötti oszlopokat/sorokat, vagy az használt cellákat kijelölöd és átmásolod egy új lapra és a régit tőrlöd.
2. Lehet hogy egy addin ragadt be, ehhez indítsd csökkentett (safe) módban az Excel-t. Nyomd le a bal CTRL-t majd indítsd az Excelt vagy parancssorban használd a /safe kapcsolót. Ezek után nézd nyisd meg a lassú fájlt és nézd meg hogy ilyenkor mit csinál.
3. Lehet hogy egy automatikus adatfrissítés van a fájlban. Csinálhatja egy makró (Alt+F11-el a Workbook open eseményét érdemes megnézni) vagy az Adatok fülön a Lekérdezések és kapcsolatok alatt lehet látni.üdv
-
Mutt
senior tag
Sziasztok,
Tegyük tisztába a változatokat. Remélem én sem tudom rosszul.
Három nagy kategória van.
1. Asztali változat, amelyet saját gépre kell telepíteni. pl. Office 2016, Office 2021 stb, az MS365 (ezt korábban Office365-nek hívták, de kb. 2 éve át lett keresztelve) is ilyen. Ezek fizetős változatok, amelyekből van több altípus, mint Standard/Pro/Small Business/Student/Preview etc. Többnyire jár/járt hozzájuk még extra tárhely szolgáltatás (OneDrive eltérő tárhelyekkel), itt lehet tárolni is adatokat és megosztani másokkal. (A)
A régebbi változatokért csak 1x kellett fizetni, de az MS365 már előfizetéses alapú.
2. MS365 for Web, ami ingyenesen használható butított MS365 böngészőben. https://office.com -on lehet belépni ingyenes regisztráció után. Az accounthoz járó OneDrive-ban lehet tárolni a fájlokat, megosztani másokkal. (B)
3. Mobil applikációk, ezek is ingyenesek, elég butítottak.Kinek mire van szüksége:
1. Otthoni használatra egy régebbi változat bőven jó (Office 2016 alá szerintem nem érdemes menni), csak egyszer kell fizetni érte és tudja azt ami kell.
2. Aki haladó annak érdemes lehet az MS365-re előfizetni (előfordulhat hogy munkahelye révén olcsóbban kaphatja meg, ezt a https://www.microsofthup.com oldalon lehet csekkolni). Belépve a Microsoft Accountba le kell tölteni a telepítőt.
3. Aki nagyon képben akar lenni, a ingyenesen átmehet az MS365 insider változatra, amelyben havonta jelennek meg új funkciók (többsége szerintem felesleges, kiforratlan). Ezt a telepített változatban a Account alatt lehet megtenni.Linkek:
A) https://www.microsoft.com/hu-hu/microsoft-365/buy/compare-all-microsoft-365-products
B) https://www.microsoft.com/hu-hu/microsoft-365/excel -
Mutt
senior tag
válasz
ny.janos
#53053
üzenetére
Szia,
Cseréld le erre az átlag számítást:
=Table.AddColumn(#"Oszlop elforgatva", "Átlag", each List.Average(List.Range(Record.ToList(_), 3, List.Count(Table.ColumnNames(#"Oszlop elforgatva")))), type number)Annyi, hogy a fenti képletben a 3-as azt jelenti, hogy ennyi oszlop van az eredmények előtt (a mintád szerint).
üdv
-
-
#52998
Mutt
senior tag
szotyika77
#52995
Mutt
senior tag
válasz
szotyika77
#52995
üzenetére
Szia,
Ezekkel is lehet próbálkozni (A1:A10 tartományban az adatok):
=SZORZATÖSSZEG(--(A1:A10))
=SZORZATÖSSZEG((A1:A10)*1)
=SZUM(--(A1:A10))
=SZUM(SZÁMÉRTÉK(A1:A10))
=REDUCE(0;A1:A10;LAMBDA(c;s;c+s))Az első 2 régi Excelekkel is megy.
A harmadik és negyedik tömbképlet amelyet Excel 2021 előtt Ctrl+Shift+Enter-rel kell bevinni, a negyedik csak Excel 2013-tól műkődik.
Az utolsó MS365-ben műkődik.üdv
-
Mutt
senior tag
válasz
dm1970
#52967
üzenetére
Szia,
Több HA esetén átláthatóbb megoldást add Excel 2019-től a HAELSŐIGAZ (angolul IFS) függvény.
A példaként mutatot képletet így lehet átültetni:
=HAELSŐIGAZ(BAL(A6;1)="k";"kék";JOBB(A6;3)="tét";"betét";NEM(HIBÁS(SZÖVEG.KERES("zö";A6)>0));"zöld";JOBB(A6;2)="ll";"toll";1;"")A képlet végén lévő 1;"" rész arra szolgál, hogyha a korábbi feltételek egyike sem teljesült, akkor ez fog és ilyenkor a üres cellát ad eredményül.
üdv
-
Mutt
senior tag
válasz
bozsozso
#52983
üzenetére
Szia,
Ha nem lennének intervallum átfedések, akkor simán SZUMHATÖBB (vagy régebbi Excelben SZORZATÖSSZEG) megadná az eredményt, de mivel lehetnek átfedések így tömb-képlet kell.
Ha nem MS365-öt használsz, akkor Excel 2016-tól használható az ÖSSZESíT függvény. D2 képlete (első 10 ezer sorban keresi az egyezést):
=HAHIBA(INDEX($K$1:$K$10000;ÖSSZESÍT(14;6;SOR($G$2:G$10000)/(($G$2:$G$10000=A2)*($H$2:$H$10000=B2)*($I$2:$I$10000<=C2)*($J$2:$J$10000>=C2));1));"")Ha MS365-ön vagy akkor használjuk az újabb függvényeket:
=LET(r;SZŰRŐ(K:K;(G:G=A2)*(H:H=B2)*(I:I<=C2)*(J:J>=C2));HAHIBA(INDEX(r;DARAB(r));""))
üdv
-
Mutt
senior tag
Szia,
Excel 2010-től van Power Pivot, amivel meg lehet csinálni.
A mintámban van 3 oszlop: termék, hét, jelölés (amiben egy X van amikor szükséges).Lépések:
1. Pivot beszúrása ablakban válaszd ki alul hogy adatmodelbe töltse be az adatokat.
2. A Pivot tervezőnél jobb klikk a tábla nevén (az én példámban Range) és válaszd ki az új mérték hozzáadaása opciót.
3. A felugró ablakban egy DAX képletet kell megadni. Ez az én példám alapján: =if(ISFILTERED(Range[Termék]);CONCATENATEX(CALCULATETABLE(VALUES(Range[Hetek]);NOT(ISBLANK(Range[Jelölés])));Range[Hetek];", ");"")
Kapcsos zárójelben az oszlopok nevei vannak.
A lényeg belül a CALCULATETABLE, ami egy olyan eredménytáblát állít elő hogy csak azon sorokat tartja meg ahol nem üres a [Jelölés] oszlop. Ha te ezt máshogy jelzed akkor ezen kell módosítani.
A CONCATENATEX összefűzi egymás a jelzett heteket (ha több lenne akkor vesszővel elválasztja őket).
Az egész egy IF-be van téve, hogy a végén a totál soroknál ne legyen felsorolva minden jelölt hét.4. Ezt az új értéket lehet a pivotba húzni. Az eredmény a példámon középen látszik.
A kimutatás rendes Pivotként műkődik, lehet a megszokott dolgokat csinálni. A DAX képlet a memóriában megcsinálja a számításokat minden frissítéskor.
üdv
-
Mutt
senior tag
válasz
dm1970
#52922
üzenetére
Szia,
Nem fog a copy-paste menni.
Excelben van Application.SendKeys "^V" illetve GUI-ban session.findById("wnd[0]").sendVKey V78.
Mindkettőnek az kell. hogy előtte a focus a beviteli mezőn legyen.
Nekem egyik módszerrel sem jött össze.Az hogy a vágólap vátlozik nem kellene, hogy gond legyen. Ha tudsz több infót/példát adni, akkor megnézem.
üdv
-
Mutt
senior tag
válasz
lanszelot
#52900
üzenetére
Szia,
Ahhoz, hogy mondjuk az első 3 sort rögzítsd ne jelöld ki a sorokat hanem állj a negyedik sorba és használd az ablaktábla rögzítést. Ha nem az első oszlopban vagy akkor a balra lévő oszlopok is rögzülnek.
Más szóval: Rögzítéshez állj egy cellába es attól balra és felfele rögzülnek a sorok és oszlopok.
Azért makacs a videón az Excel, mert a kijelölés legfelső sora és oszlopa mindig az A1 cella, amitől nincs semmi balra/ felfele ami rögzíthető. Ilyenkor a látható sorok felére teszi a rögzítést az Excel.
Üdv
-
Mutt
senior tag
-
Mutt
senior tag
válasz
ny.janos
#52804
üzenetére
Szia,
..fogalmam nincs, hogy miért csinálja azt, amit.
Én sem tudom megmondani, hogy miért csinálja a kibontásnál az átrendezést.Amit én javaslok, hogy a csoportositás után egyből nyerd ki a nyertes és vesztes adatait közvetlen hivatkozással. A tábla első sorára, ahol a nyertes van, [Páros]{0}-val, a vesztesre pedig [Páros]{1}-el tudsz hivatkozni. Oszlopnév megadással mélyebbre tudsz menni, pl. [Páros][Név]{0} a nyertes nevét adja.
A teljes M-kód:
let
Forrás = Excel.CurrentWorkbook(){[Name="Táblázat1"]}[Content],
#"Típus módosítva" = Table.TransformColumnTypes(Forrás,{{"Név", type text}, {"Páros sorszám", Int64.Type}, {"Teli", Int64.Type}, {"Tarolás", Int64.Type}, {"Összes", Int64.Type}, {"Üres", Int64.Type}}),
#"Sorok rendezve" = Table.Sort(#"Típus módosítva",{{"Páros sorszám", Order.Ascending}, {"Összes", Order.Descending}, {"Teli", Order.Descending}, {"Tarolás", Order.Descending}, {"Üres", Order.Ascending}}),
#"Sorok csoportosítva" = Table.Group(#"Sorok rendezve", {"Páros sorszám"}, {{"Adatok", each _, type table [Név=nullable text, Páros sorszám=nullable number, Teli=nullable number, Tarolás=nullable number, Összes=nullable number, Üres=nullable number]}}),
NyertesAdatok = Table.AddColumn(#"Sorok csoportosítva", "NyertesAdatok", each [Adatok]{0}),
VesztesAdatok = Table.AddColumn(NyertesAdatok, "VesztesAdatok", each [Adatok]{1}),
#"Kibontott NyertesAdatok" = Table.ExpandRecordColumn(VesztesAdatok, "NyertesAdatok", {"Név", "Teli", "Tarolás", "Összes", "Üres"}, {"Nyertes Név", "Nyertes Teli", "Nyertes Tarolás", "Nyertes Összes", "Nyertes Üres"}),
#"Kibontott VesztesAdatok" = Table.ExpandRecordColumn(#"Kibontott NyertesAdatok", "VesztesAdatok", {"Név", "Teli", "Tarolás", "Összes", "Üres"}, {"Vesztes Név", "Vesztes Teli", "Vesztes Tarolás", "Vesztes Összes", "Vesztes Üres"}),
#"Típus módosítva1" = Table.TransformColumnTypes(#"Kibontott VesztesAdatok",{{"Nyertes Név", type text}, {"Nyertes Teli", Int64.Type}, {"Nyertes Tarolás", Int64.Type}, {"Nyertes Összes", Int64.Type}, {"Nyertes Üres", Int64.Type}, {"Vesztes Név", type text}, {"Vesztes Teli", Int64.Type}, {"Vesztes Tarolás", Int64.Type}, {"Vesztes Összes", Int64.Type}, {"Vesztes Üres", Int64.Type}})
in
#"Típus módosítva1"üdv
-
Mutt
senior tag
válasz
Guitar14
#52796
üzenetére
Szia,
Itt az én változatom.
J-oszlop képlete:
=HA(2*SOR()<=DARAB2($A$1:$H$7);INDEX($A$1:$H$7;INT((SOR()-1)/4)+1;2*(MARADÉK(SOR()-1;4)+1));"")Ha az I-oszlop is kellene képlettel, akkor a képletben a 2*(MARADÉK(SOR()-1;4)+1) részt annyival kell módosítani, hogy 1-et ki kell vonni belőle, vagyis 2*(MARADÉK(SOR()-1;4)+1)-1
Ha esetleg lesz majd MS365-öd akkor pedig:
J-oszlop képlete. =OSZLOPHOZ(INDEX($A$1:$H$7;SORSZÁMLISTA(DARAB2(A:A));{2\4\6\8}))
I-oszlopé pedig: =OSZLOPHOZ(INDEX($A$1:$H$7;SORSZÁMLISTA(DARAB2(A:A));{1\3\5\7}))üdv
-
Mutt
senior tag
Szia,
Szerintem az alapokat kell elsajátítani/magabiztossan kezelni és utána lehet elővenni a haladó dolgokat, amelyek véleményem szerint az alábbiak lehetnek hasznossági sorrendben:
- táblázatok,
- kimutatás/pivot,
- Solver,
- tömbfüggvények (annó CSE bevitel, pl. SZORZATÖSSZEG/ÖSSZESÍT mindenre),
- Power Query/Power Pivot,
- dinamikus tömbök (spill, @ és # jelentése) illetve újabb függvények (LET, LAMBDA, XLOOKUP/XMATCH/BY...)
- VBA,
- DAX függvények (itt Power BI felé lehet kacsingatni)
- Office Script illetve Py szkript (ezek MS365-ben),
- Excel4 függvények (talán egyedül EVALUATE)Szerintem ne azt keresd, hogy mi a legfrissebb, mert nagyon sokan még régi verziójú Excelt használnak, nekik is mindig van megoldás a problémára. Az elmúlt években a Microsoft önti az újabb függvényeket az Excelbe, de ezek elterjedése lassan megy és nincs is mindenkinek szüksége rá.
pl. mindenki adott össze már számokat Excelben, 2 éve van a REDUCE amivel lehet összeadni is, de nem hinném hogy a SZUMHATÖBB (eseteg SZORZATÖSSZEG/ÖSSZESÍT) helyére fog valaha is lépni.Ha a fenti listában lévő dolgok újdonságok neked, és nincs időd mindet megismerni, akkor Power Query-vel kezdj szerintem.
Youtube-on én az alábbi csatornákat tudom javasolni:
1) Mike Girvin: https://www.youtube.com/@excelisfun
Erőssége: tömbfüggvények (kb. 4 évvel ezelőtt, van is könyve róla), az elmúlt időszakban főleg Power Query/Power Pivot/DAX és az új Excel függvényeket használja.Sok hosszabb videója van, ami alapokat magyaráz hogy a haladó dolgokat meg lehessen érteni.
2) Bill Jelen aka MrExcel: https://www.youtube.com/@MrXL
Erőssége: rövid videók a legfontosabb változásokról, illetve ha 4-5 évet visszamész a csatornán, akkor tipikus problémákra mutat megoldásokat. volt egy Duelling Excel sorozat, amiben jó példák voltak.
3) Mynda: https://www.youtube.com/@MyOnlineTrainingHub
Erőssége: változatos videók, alaposan bemutatva adott függvényt vagy problémát.
4) VBA-hoz Paul csatornája: https://www.youtube.com/@Excelmacromastery
Kezdőknek jó a csatorna, mert tippeket ad hatékony kód íráshoz. Hátránya, hogy szinte mindig vagy collection vagy class module lesz a vége.
5) DAX/Power BI/Power Query:
- Goodly: https://www.youtube.com/@GoodlyChandeep
- Marco/Alberto: https://www.youtube.com/@SQLBI
- BAS: https://www.youtube.com/@HowtoPowerBIüdv
-
-
Mutt
senior tag
válasz
p5quser
#52694
üzenetére
Szia,
Röviden: igen, a "col" egy változó (column-t rövidítettem).
Hosszan:
1) A képlet alapja a BYCOL függvény, amely egy tartománynak (első paraméter) az oszlopain egymás után lefuttatja a második paraméterben megadott függvényt. Az eredmény egy tömb lesz, legalább annyi elemmel ahány oszlop van.
2) A LAMBDA függvénnyel pedig saját függvényeket lehet létrehozni. Az első paraméter(ek) változók, az utolsó pedig az eredményt kiszámoló képlet. pl. LAMBDA(a,b,a+b) összeadja a két inputot. LAMBDA-t többnyire a névkezelőben szoktunk használni, és onnan a munkafüzetben bárhol el lehet érni.
Ebben az esetben a BYCOL függvény a LAMBA első változójának átadja az oszlop értékeit. A függvény pedig megnézi, hogy az adott oszlopban megtalálható-e a szöveg vagy sem. Az eredmény 0 vagy pozítiv szám lesz.
3) A FILTER függvénnyel azon mezők maradnak meg, ahol egy pozítiv szám volt az eredmény.
4) A végén a több lehetséges eredményt összefűzük egy mezőbe.üdv
-
Mutt
senior tag
válasz
p5quser
#52683
üzenetére
Szia,
Kaptál már választ, de én is bedobok egy csak friss Excelben műkődő változatot.
F2-ben a kereső szó és G2-ben a képlet:
=TEXTJOIN("; ";TRUE;FILTER($A$1:$D$1;BYCOL($A$2:$D$4;LAMBDA(col;SUM(--ISNUMBER(SEARCH("*"&F2&"*";col)))))))Google Sheets-ben ugyanezen függvények léteznek, szóval elméletben ott is műkődhet.
üdv
-
Mutt
senior tag
Mike Girvin az elmúlt időszakban jópár hasznos videót rakott fel Youtube-ra.
1. Microsoft Power Tool-ok adatelemzéshez: https://www.youtube.com/watch?v=OPRgygR0kio
2. Power Query haladó (M-nyelv): https://www.youtube.com/watch?v=HRFZOYWGhjoRegex függvényeket kap az Excel: https://www.youtube.com/watch?v=YFnXV2be9eg
Online súgóban még nincsenek fent, de Insider változatban már elérhető.Közben pedig látom, hogy már 2 éve van REDUCE/MAP/SCAN függvény is.
-
Mutt
senior tag
válasz
dm1970
#52660
üzenetére
Szia,
SAP GUI beállításoknál kapcsold ki a Notifcation-öket és nem kell többet okéznod.
A makrókat alapból tiltja a rendszer, de lehet engedélyezni őket Excelben a Fájl->Beállítások/Options->Adatvédelmi központ/Trust Center->Adatvédelmi központ beállítása/Trust Center Settings gomb alatt.
Nézd meg a makróbeállításokat és a megbízható helyeket.
üdv
-
Mutt
senior tag
válasz
dm1970
#52644
üzenetére
Szia,
Ahogy olvasom az erdeti kérdést, ha csak letöltesz egy riportot és OK-t kell nyomni, akkor még1xübb a dolog, mert akkor nem kell For-Next ciklus. A VBS szkritpet bemásolod egy VBA modulba, az Application-t mindenhol lecseréled SAPApplication-re.
A felugró ablakban (a wnd szám mondja meg melyik ablakban kell dolgozni, 0 az alap és 1-esével növekszik minden felugró ablakkal) OK-t (a btn utáni szám 0-szokott lenni OK-ra, de ezt a Script Tracker meg tudja mutatni) így lehet nyomni:
session.findById("wnd[1]/tbar[0]/btn[0]").pressüdv
-
Mutt
senior tag
válasz
dm1970
#52644
üzenetére
Szia,
Van programozási tapasztalatod (For - Next ciklus)?
Én MM/FI modulokban dolgozom, onnan tudok példát mutatni.
Egy komplex változat fent van a GitHub-on.
Illetve YT-on Varga Csongor videói is tudnak segíteni.Mielőtt tovább megyünk, pár tanács!
1. Óvatosan a szkriptekkel, mert nagy kárt tudsz okozni!
2. Ha nincs teszt környezeted, ahol tudod ellenőrízni a szkriptet, akkor nagyon legyél körültekintő.
3. A SAP bonyolult, a SAP szkript mégrosszabb. Előbb kérdezz vkit.
4. Használd a Scripting Trackert (letölthető innen), hogy kiderítsd melyik mező miről szól.Itt egy Móricka példa. MM02-es tranzakcióval a 93062409-es anyagnak a material group-ját LEDG101-re írtam át. A script ez lett:
If Not IsObject(application) Then
Set SapGuiAuto = GetObject("SAPGUI")
Set application = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(connection) Then
Set connection = application.Children(0)
End If
If Not IsObject(session) Then
Set session = connection.Children(0)
End If
If IsObject(WScript) Then
WScript.ConnectObject session, "on"
WScript.ConnectObject application, "on"
End If
session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").text = "mm02"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtRMMG1-MATNR").text = "93062409" 'ez az anyagszám
session.findById("wnd[0]/usr/ctxtRMMG1-MATNR").caretPosition = 8
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[0]/usr/tabsTABSPR1/tabpSP01/ssubTABFRA1:SAPLMGMM:2004/subSUB2:SAPLMGD1:2001/ctxtMARA-MATKL").text = "LEDG101" 'ennek a mezőnek az értékét akarom módosítani vmire
session.findById("wnd[0]/usr/tabsTABSPR1/tabpSP01/ssubTABFRA1:SAPLMGMM:2004/subSUB2:SAPLMGD1:2001/ctxtMARA-MATKL").setFocus
session.findById("wnd[0]/usr/tabsTABSPR1/tabpSP01/ssubTABFRA1:SAPLMGMM:2004/subSUB2:SAPLMGD1:2001/ctxtMARA-MATKL").caretPosition = 7
session.findById("wnd[0]/tbar[0]/btn[11]").press
session.findById("wnd[0]").sendVKey 0A példámban a szkriptet szeretném lefuttatni másik anyagszámokon (19-es sor) és akár más értéket megadni (24-es sor)
1. Excelben egy lapon megadom hogy mely anyagoknak milyen új értéket akarok megadni
pl.
2. Kell egy For - Next ciklus ami ezen végigmegy.
VBA Editorban egy új modulban ez például az A-oszlopban lévő értéket kiírja.Sub minta()
With ActiveSheet
lastrow = .Range("A" & Rows.Count).End(xlUp).Row
For c = 2 To lastrow
Debug.Print .Range("A" & c)
Next c
End With
End Sub3. Most már csak a SAP scriptet kell ide tenni. Az első 14 sor a SAP-hoz kapcsolódást csinálja, ezt elég egyszer megtenni. Vagyis ez a For-Next elé kerül. A többi lépés pedig bele a For-Next-be.
Annyi javítás kell, hogy az "Application" Excelben már foglalt, így más kell helyette használni pl. "SAPApplication".4. Nekem két változó értékem van az A és B-oszlopban, amit szeretném ha a szkript használna.
A lenti sornak a végén a fix anyagszám helyett kell nekem az A-oszlop értéke.session.findById("wnd[0]/usr/ctxtRMMG1-MATNR").text = "93062409"A helyes kód a fenti helyett ez lesz:
session.findById("wnd[0]/usr/ctxtRMMG1-MATNR").Text = .Range("A" & c)A
session.findById("wnd[0]/usr/tabsTABSPR1/tabpSP01/ssubTABFRA1:SAPLMGMM:2004/subSUB2:SAPLMGD1:2001/ctxtMARA-MATKL").text = "LEDG101"sor helyett pedig ezsession.findById("wnd[0]/usr/tabsTABSPR1/tabpSP01/ssubTABFRA1:SAPLMGMM:2004/subSUB2:SAPLMGD1:2001/ctxtMARA-MATKL").Text = .Range("B" & c)5. Érdemes egy visszajelzést is adni, hogy melyik sorban járunk és ha kell akkor meg is tudjuk szakítani a futtatást. Ezért a "Next c" elé kerüljön be ez.
DoEvents
.Range("C" & c) = "Kész"6. Rakj ki egy gombot és rendeld hozzá a makrót. Érdemes tesztelni 1-2 kombináción, ha van teszt environment akkor azon, ha nincs akkor a mentést sort kommenteld ki először.
A teljes minta kód:
Sub minta()
If Not IsObject(SAPApplication) Then
Set SapGuiAuto = GetObject("SAPGUI")
Set SAPApplication = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then
Set Connection = SAPApplication.Children(0)
End If
If Not IsObject(session) Then
Set session = Connection.Children(0)
End If
If IsObject(WScript) Then
WScript.ConnectObject session, "on"
WScript.ConnectObject SAPApplication, "on"
End If
With ActiveSheet
lastrow = .Range("A" & Rows.Count).End(xlUp).Row
For c = 2 To lastrow
session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "mm02"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtRMMG1-MATNR").Text = .Range("A" & c)
session.findById("wnd[0]/usr/ctxtRMMG1-MATNR").caretPosition = 8
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[0]/usr/tabsTABSPR1/tabpSP01/ssubTABFRA1:SAPLMGMM:2004/subSUB2:SAPLMGD1:2001/ctxtMARA-MATKL").Text = .Range("B" & c)
session.findById("wnd[0]/usr/tabsTABSPR1/tabpSP01/ssubTABFRA1:SAPLMGMM:2004/subSUB2:SAPLMGD1:2001/ctxtMARA-MATKL").SetFocus
session.findById("wnd[0]/usr/tabsTABSPR1/tabpSP01/ssubTABFRA1:SAPLMGMM:2004/subSUB2:SAPLMGD1:2001/ctxtMARA-MATKL").caretPosition = 7
session.findById("wnd[0]/tbar[0]/btn[11]").press
session.findById("wnd[0]").sendVKey 0
DoEvents
.Range("C" & c) = "Kész"
Next c
End With
End SubEzzel készen is vagy, de azért sok mindent lehet rajta fejleszteni mint például
- vannak felesleges sorok (általában a caretPosition-osok ilyenek)
- nincs semmilyen hibaellenőrzés (pl. nincs jogosultságod vmihez, nem jó érték van az inputban)
- nem annyira jó az első kliens első sessionjéhez csatlakozni, lehet hogy éppen másra használod.Ha kell segítség akkor dobd fel ide/keress meg.
üdv
-
Mutt
senior tag
válasz
nihill
#52611
üzenetére
Szia,
Excel 2013-tól van Power Pivot, ami DAX nyelvet használja.
A CONCATENATEX függvényt próbáld ki így:1. Beszúrás -> Kimutatásnál válaszd ki az adatmodellbe töltést (Add Data Model), ez a Power Pivot.
2. Csináld meg a kimutatást ahogy szeretnéd. Majd a kimutatás mezőknél jobb klikk a tábla nevén (példámban Tartomány) és új mérték/new measure opciót válaszd.
3. A képlet a példámban: =CONCATENATEX('Tartomány';[Név];",")
Ahol tartomány a tábla neve, [Név] pedig az oszlopneve amiben lévő adatot össze kell fúzni, végül pedig a felsorolás jele vessző. OK után ezt az új érték már be tudod húzni az értékekhez.
A végeredmény:
üdv
Ps. Aki Kimutatást használ annak érdemes alapból az adatmodelbe töltést választani minden esetben, mégha DAX-ot nem is fog használni, mert kapsz új aggregációs függvényeket és a lehetőséget hogy több táblát összekapcsolj.
-
Mutt
senior tag
-
Mutt
senior tag
Sziasztok,
A másik Excel forumban volt egy olyan kérés, hogy lehet-e e szürőlistában egyszerre több adatot megadni, ne kelljen egyesével válogatni.
A válasz, hogy nem lehet, de aki újabb verzión van azoknak a SZŰRŐ (FILTER) függvény tud segíteni.
A fenti példában az A:B oszlopban vannak adatok, D2-ben felsorolva a keresendő adatok.
F2-ben a képlet asztali gépen:
=SZŰRŐ(A2:B10000;SZÁM(SZÖVEG.KERES(A2:B10000;D2))*(A2:B10000<>""))SharePointon (a felhasználónál az van) nem műkődik ez. A J és K oszlopok segítenek.
J2-ben a képlet: =ISNUMBER(FIND(A2:A1000,D2))
K2-ben pedig =A2:A10000<>""Ezeket fel tudjuk használni a szűréshez:
=FILTER(A2:B10000,(J2:J10000)*(K2:K10000))üdv
-
-
Mutt
senior tag
válasz
erdey_a
#52062
üzenetére
Szia,
Én sem biztos, hogy értem a problémát, de talán olyan kell amit most bemutatok (Excel 2010-től működik).
Illetve lenne még egy másik lehetőség, ami nagyon új. Kb 3 hetes és csak béta tesztelőknek érhető el.
PIVOTBY függvény használata. Még én sem próbáltam ki, de 2 éven belül inkább már az lesz a standard.Mutatom, hogy mostani Excel-el mit tudnál megcsinálni.
A képen bal oldalt az adatsor ami tartalmazza, hogy adott napon adott dologgal milyen eredmények voltak.
Ezt egy táblázatban tárolom, aminek a neve "Adatsor".
Mellette van egy legördülő lista, ahol az issue-k közül lehet választani. Ez is táblázatban van, aminek a neve "Oszlop".
Jobb oldalt pedig van egy Pivot, egész pontosan egy Power Pivot egy számított mezővel, aminek a neve Hiba.Ez a "Hiba" mindig a legördülő listának megfelelő értéket mutatja.
Lépések:
1. Adatsoron állva kimutatás beszúrásánál egy plusz lépés kell. Bepipálni az adatmodel-hez a hozzáadást.
2. Megcsinálod a pivotodat de az érték mezőhöz még nem adsz hozzá semmit.
3. Állj bele a másik táblába, ahol a legördülő listából lehet választani a kivánt értéket. Majd Power Pivot menűben az "Add to data model"-t válaszd. Ha nincs Power Pivot menűd, akkor Feljesztő/Developer menűben a COM-in bövitmények között engedélyezd. Zárd be a Power Pivot ablakot.
4. Most menj vissza a kimutatáshoz és jobb klikk a táblázat nevén (ami nálam Adatsor) és válaszd az új eredmény létrehozását.
A felugró ablakban adsz neki egy nevet (nálam "Hiba") és kell egy képlet, ami ez:
=SUMX(CALCULATETABLE(Adatsor;INTERSECT(VALUES(Adatsor[Issue]);VALUES(Oszlop[Issue])));[Value])Ehhez egy kis súgó. A SUMX összegzi a megadott tábla adott oszlopát. A képlet végén található a [Value], ez az oszlop aminek az értékeit összeadja. Előtte pedig a CALCULATETABLE függvény segítségével állítjuk leszűrjük a teljes adatsort csak azon sorokra, ahol az "Issue" oszlopban azonos érték van, mint amit kiválasztottunk.
5. A frissen létrehozott "Hiba" eredményt add az érték mezőhöz.
Készen is vagy.Innentől csak válaszd ki a kivánt oszlopot és nyomj egy frissítést a kimutatáson.
üdv
-
Mutt
senior tag
válasz
MZsoltee
#52051
üzenetére
Szia,
...függvény, amivel meg tudnám állapítani egy napról, hogy az munkanap-e...
A HÉT.NAPJA (WEEKDAY) függvény használható arra, hogy hétvégéket és a munkanapokat meg lehessen különböztetni.
A =HA(HÉT.NAPJA(A2;11)>5;A2+8-HÉT.NAPJA(A2;11);A2) képlet megadja az első hétfőt a nap után, ha az hétvégére esik, különben pedig az eredeti dátumot adja vissza.
Mivel te 10-e utáni első munkanapot keresed, ezért a fenti képlet elegendő is (nincs olyan munkaszüneti nap idén ami 11-12-én lenne). Azonban a teljesség miatt, ha a kapott dátum munkaszüneti napra esne (pl. május 1 szerda), ezért kellhet még a KALK.MUNKANAP.INTL (WORKDAY.INTL) függvény is.
Az E2:E11 tartományba felvettem a magyar munkaszüneti napokat. Innentől már csak azt kell megnézni, hogy a fent kapott nap szerepel-e ebben a listában, ha igen akkor kell a KALK.MUNKANAP.INTL függvény is.
MS365-ben a LET függvénnyel a képlet átláthatóbb, de nem kötelező azt használni.
C2-ben a képlet:=LET(munkanap;HA(HÉT.NAPJA(A2;11)>5;A2+8-HÉT.NAPJA(A2;11);A2);
talalat;SZÁM(XHOL.VAN(munkanap;$E$2:$E$11));
HA(talalat;KALK.MUNKANAP.INTL(munkanap;1;1;$E$2:$E$11);munkanap))üdv
-
Mutt
senior tag
válasz
andreas49
#51654
üzenetére
Szia,
Az én változatomban a feladás figyelembevételéhez elég csak a P3-ban lévő képletet módosítani.
Kacifántos lett, remélem okés nálad is.=LET(game1;MAX(ELŐJEL(nyertszett(G3:I3))-DARABHA(G3:I3;"R");0);
game2;MAX(ELŐJEL(nyertszett(J3:L3))-DARABHA(J3:L3;"R");0);
game3;MAX(ELŐJEL(nyertszett(M3:O3))-DARABHA(M3:O3;"R");0);
total;KEREK.FEL(DARAB2(G3:O3)/3;0);
felad;DARABHA(G3:O3;"R");
win;game1+game2+game3;
win&"-"&MIN(total-win+felad;total))üdv
-
Mutt
senior tag
válasz
andreas49
#51657
üzenetére
Szia,
5 bejegyzéssel feljebb a double unary operátorról ("--", két negatívjel egymás után) volt szó, ami a leggyorsabb megoldás egy szövegként ábrázolt szám értékké alakítására. Más megoldások is vannak, ha értékké kell alakítani egy szövegként tárolt számot:
- eggyel szorozni,
- nullát hozzáadni,
- ÉRTÉK/VALUE függvényt használni.A névkezelőben a pontadott és pontkapott függvényekben én is használtam a double unary-t, de a fájlodban vmiért nem megy (ha kézzel újból beírom a kettő mínuszjelet akkor nálam megy).
Próbáld meg ezen változatot:pontadott
=LAMBDA(x;HAHIBA(INDEX(SZÖVEGFELOSZTÁS(x;;"–");1)+0;""))pontkapott
=LAMBDA(x;HAHIBA(INDEX(SZÖVEGFELOSZTÁS(x;;"–");2)+0;""))üdv
-
Mutt
senior tag
válasz
andreas49
#51645
üzenetére
Szia,
Kaptál már megoldást, én is mutatok egyet amellyel a M365-ben található újabb függvényeket (LAMBDA, LET, BYCOL) szeretném megismertetni.
Röviden pár szó róluk:
1. LAMBDA: ezzel saját függvényt lehet létrehozni, amivel a számításokat lehet egyszerúsíteni.
2. LET: itt változókat lehet létrehozni amik segítenek a számításokban anélkül hogy segédoszlopot kellene használni
3. BYCOL és BYROW: függvényeket lehet futtatni tömbökön amelyek oszlopban/sorban vannak.
Lépések:
1. A névkezelőben létrehoztam három saját függvényt a LAMBDA függvény segítségével.
pontadott képlete =LAMBDA(x;HAHIBA(--INDEX(SZÖVEGFELOSZTÁS(x;;"–");1);""))
pontkapott képlete =LAMBDA(x;HAHIBA(--INDEX(SZÖVEGFELOSZTÁS(x;;"–");2);""))
nyertszett pedig =LAMBDA(x;SZUM(HA(HAHIBA(BYCOL(x;pontadott)-BYCOL(x;pontkapott);0)>0;1;0)))Az első kettő a 6-2-es inputból (a függvényben x) 6-ot és 2-et ad vissza. Használata a füzetben pl. =pontadott(K3)
A harmadikban a BYCOL(x;pontadott) résszel elérhetjük, hogy mondjuk az J3:L3 tartomány celláiból megkapjuk az eredményt. Ha kivontjuk az adott és kapott pontokat egymásból, akkor megkapjuk hogy az adott szettet kinyerte meg. Van ott még hibakezelés, hogy az üres cellák ne okozzanak gondot.
2. A fenti függvények segítségével az R3 képlete:
=SZUM(BYCOL(G3:O3;pontadott))&"-"&SZUM(BYCOL(G3:O3;pontkapott))A Q3 képlete:
=LET(win;nyertszett(G3:O3);
total;DARAB2(G3:O3);
win&"-"&total-win)Itt a "win" nevű változóban tároljuk a nyert szettek számát. A "total" az összes játszottat adja meg, az eredményt pedig az utolsó soradja meg.
A P3 képlete:
=LET(win;ELŐJEL(nyertszett(G3:I3))+ELŐJEL(nyertszett(J3:L3))+ELŐJEL(nyertszett(M3:O3));
total;KEREK.FEL(DARAB2(G3:O3)/3;0);
win&"-"&total-win)Itt annyi változás van, hogy a 3-as szettekből kiderítjük kinyerte meg a gémet.
üdv
-
#51598
Mutt
senior tag
flatline#2
#51591
Mutt
senior tag
válasz
flatline#2
#51591
üzenetére
Szia,
Én YT-on Mike Girvin videoit javaslom (angol, de alapos)
Pl. https://www.youtube.com/playlist?list=PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtWA magyar Excel-t könnyedén át tudod kapcsolni angolra, elmented a fájlt és visszakapcsolod magyarra és látod a magyar függvényneveket. Vagy megoldás, hogy képlet fordítót használsz (weben https://hu.excel-translator.de/translator/ vagy formula translator-t Excel addon-ként is letöltheted).
üdv
-
Mutt
senior tag
válasz
erdey_a
#51587
üzenetére
Szia,
... az eredeti táblázat nem módosítható bizonyos okokból, a kimutatáshoz azonban szükséges pár új adat.
A forrás táblázat csak dátumot tartalmaz, de a kimutatásnak heti alapon kell történnie.Kaptál már javaslatot, de szerintem van egyszerűbb megoldás is, ha Excel 2013 vagy frissebbet használsz.
PowerPivot-ot javaslok.Két módszert is mutatok, mindegyiknél az első lépés, hogy a jelenlegi adatot a Power Pivot adatmodellhez hozzá kell adni. Jelöld ki/állj az adatsoron Excelben majd Power Pivot menű (ha nincs ilyened akkor a Fejlesztő/Developer menüben a COM Add-ins alatt tudod aktiválni) és "Add Data to Model".
1. módszer
A betöltött adat végén az "Add column" oszlopba kattintva lehet ún. számított oszlopot / calculated column felvenni, ami minden soron végigmenve elvégzi a számítást, amit a táblázat feletti szerkesztőlécben lehet megadni. A formátum "oszlopnév := képlet". Ha a hét száma kell, akkor a WEEKNUM függvény kell. (Power Pivot DAX függvényeket használ, amelyek többsége hasonlít az Excel megfelelőjére, de a lényeg hogy ezek nem Excel függvények.Ha ez megvan, akkor már csak Kimutatás/Pivot kell de a forrásnak az adatmodell-t kell megadni.
Ezek után csak a frissítés gombot kell használni, az új oszlop az adatmodellben automatikusan tenni fogja a dolgát.
2. módszer
Érdemes egy dátumnaptárat létrehozni. Ehhez Excelben a legegyszerűbb megoldás, ha egy új lapon felsorold az összes napot (érdemes az év minden napját megadni, de nem kötelező) és betöltöd az adatmodellbe. Adj új oszlopokat hozzá, ami téged érdekel:
pl. hónap sorszámmal =MONTH(Table2[Dátum])
hónap rövidített névvel =FORMAT(Table2[Dátum];"mmm")
hónap teljes névvel =Format(Table2[Dátum];"mmmm")
negyedév =INT(([Month]+1)/4)+1 & "Q"Ezek után a Design fülön érdemes a naptárat megjelölni.
Ezek után kapcsoljuk össze a két táblát. Design menű és Manage relationship.
Menjünk vissza az Excelbe (File -> Close) és megint adatmodellből készítjük a kimutatást. Itt a dátummal kapcsolatos mezőket a dátumnaptárból tegyük a kimutatásba, a többi jön az eredeti adatsorból. Mivel a két tábla össze van kapcsolva az Excel a megfelelő szűrést fogja mindig elvégezni.
üdv -
Mutt
senior tag
válasz
KAMELOT
#51532
üzenetére
Szia,
SZUMHATÖBB és SZORZATÖSSZEG-el segédoszlop nélkül is lehet számolni az előfordulásokat.
Hogy milyen gyakran az már macerásabb.
D-oszlopban van egy segédoszlop, ami az utolsó dupla1 napot keresi meg.
A képlet lényege MAXHA(A:A;B:B;1;C:C;1;A:A;"<"&A1)
Mellette az E-oszlopban megnézzük hogy hány nap telt el az utolsó dupla1 óta.
Erre az E-oszlopra van egy átlag függvény téve.
üdv
-
Mutt
senior tag
Szia,
Beszúrás menűben Bővítmények és az Office Store-ból tudsz telepíteni.
Másik megoldás, hogy egy rejtett munkalapra felsorolod a választható napokat. Ebből létrehozol egy Kimutatást (Pivot) és egy dátumválasztót (ami már ne a rejtett lapon legyen). Akár intervallumot is tudsz kezelni így.
üdv
-
Mutt
senior tag
válasz
RAiN91
#51526
üzenetére
Szia,
Én nem jöttem rá a logikára.
3 lapon 1-1 kimutatás, az eszköztárra kiraktam az összes kimutatás frissítése gombot, az utolsón pedig egy összesítőtábla aminek az eredményét látod a lenti táblázatban. Függetlenül hogy melyik lapon volt az utolsó módosítás, vagy melyiken álltam éppen ez lett az eredmény (pl. második lapon módosítottam utoljára, de az utolsó lapon állva indítottam a frissítést a sorrend 3-as lap, majd 1 és végül 2 volt):
Ha fontos a sorrend, akkor érdemes egy subroutine-ban megadnod a sorrendet és ezt hozzárendelni az adott eseményhez.
Ha nem makróról lenne szó, hanem képletekről, akkor pedig ha belenézel egy ZIP kezelővel az OpenXML formátumú fájlba, akkor lesz egy calcChain.xml amiben látható a cella sorrend.
üdv
-
Mutt
senior tag
válasz
csabbymtc
#51516
üzenetére
Szia,
Én másra gondoltam a kérdéseddel kapcsolatban mint a többiek.
Az én megoldásom pedig MS365-ben így néz ki.
A LET függvénnyel megússzuk a segédoszlopokat. A függvénnyel egy változóhoz értékeket lehet rendelni, majd a végén ezen változókkal műveletet csinálni. pl. =LET(a;1;a*2) az "a" nevű változónak 1-es értéket ad, majd eredményként a változó dupláját adja vissza.
Ezekután a teljes képlet:
=LET(betu;ÖSSZESÍT(15;6;SZÖVEG.KERES({1\2\3\4\5\6\7\8\9};A1);1)-1;
oszlop;BAL(A1;betu);
elvalaszto;SZÖVEG.KERES("-";A1);
start;KÖZÉP(A1;betu+1;elvalaszto-betu-1);
stop;KÖZÉP(A1;elvalaszto+1;255);
SZÖVEGÖSSZEFŰZÉS(",";IGAZ;oszlop&SORSZÁMLISTA(stop-start+1;;start)))A képletben kikeressük az oszlopazonosítót, ami betü(k)ből áll (példádban az X). Majd kikeressük a kötőjel előtti és utáni számokat (start és stop). A sorszámlista előállítja a két szám közötti számokat, amik elétesszük az oszlopazonosítót és az egészet összefűzzük.
Ha pedig nem a cellahivatkozásokat, hanem a cellák tartalmát akarod egy cellában látni, akkor a képlet ez (a mintámon az A4-B4 cellákon látod ezt):
=LET(betu;ÖSSZESÍT(15;6;SZÖVEG.KERES({1\2\3\4\5\6\7\8\9};A4);1)-1;
oszlop;BAL(A4;betu);
elvalaszto;SZÖVEG.KERES("-";A4);
start;KÖZÉP(A4;betu+1;elvalaszto-betu-1);
stop;KÖZÉP(A4;elvalaszto+1;255);
SZÖVEGÖSSZEFŰZÉS(",";HAMIS;INDEX(INDIREKT(oszlop&":"&oszlop;IGAZ);SORSZÁMLISTA(stop-start+1;;start))))Másik megoldás a Power Query használata, ami Excel 2013-tól van.
Az M-code ehhez:let
Forrás = Excel.CurrentWorkbook(){[Name="Táblázat1"]}[Content],
#"Típus módosítva" = Table.TransformColumnTypes(Forrás,{{"Lista", type text}}),
#"Oszlop megkettőzve" = Table.DuplicateColumn(#"Típus módosítva", "Lista", "Lista - másolat"),
#"Oszlop felosztása karakterátalakítás alapján" = Table.SplitColumn(#"Oszlop megkettőzve", "Lista - másolat", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Lista.1", "Lista.2", "Lista.3"}),
#"Érték felülírva" = Table.ReplaceValue(#"Oszlop felosztása karakterátalakítás alapján","-","",Replacer.ReplaceText,{"Lista.1", "Lista.2", "Lista.3"}),
#"Egyéni oszlop hozzáadva" = Table.AddColumn(#"Érték felülírva", "Egyéni", each {Number.From([Lista.2])..Number.From([Lista.3])}),
#"Kibontott Egyéni" = Table.ExpandListColumn(#"Egyéni oszlop hozzáadva", "Egyéni"),
#"Egyesített oszlop beszúrva" = Table.AddColumn(#"Kibontott Egyéni", "Egyesítve", each Text.Combine({[Lista.1], Text.From([Egyéni], "hu-HU")}, ""), type text),
#"Sorok csoportosítva" = Table.Group(#"Egyesített oszlop beszúrva", {"Lista"}, {{"Elemszám", each _, type table [Lista.1=nullable text, Lista.2=nullable text, Lista.3=nullable text, Egyéni=number, Egyesítve=text]}}),
#"Egyéni oszlop hozzáadva1" = Table.AddColumn(#"Sorok csoportosítva", "Egyéni", each [Elemszám][Egyesítve]),
#"Kinyert értékek" = Table.TransformColumns(#"Egyéni oszlop hozzáadva1", {"Egyéni", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Oszlopok eltávolítva" = Table.RemoveColumns(#"Kinyert értékek",{"Elemszám"})
in
#"Oszlopok eltávolítva"üdv
-
-
Mutt
senior tag
Szia,
Sok kérdés, próbálok meg válaszolni rájuk.
1. Szerintem Power Query-vel érdemes kezdeni. Könnyen tanulható, elég univerzális eszköz. Nem kell hozzá programozási ismeret.
Könyvből ami nekem bejött az "M, is for data monkey".
Youtube-on pedig kezdőknek Mike Girvin videoit javaslom és ha már jól megy (M-kódot akarsz írni), akkor Goodly-t javaslom.2. BI esetén Analysis for Excel-ből gondolva SAP BI-ról van szó. Én is sajna csak a SAP Forumokat tudom javasolni. Analysis-al sajna sok mindent nem lehet csinálni, mint összerakod a riportot majd VBA-val tudod automatizálni a frissítéseket, PQ-vel pedig manipulálni az adatokat. Webi esetén még kevesebb lehetőséged van.
Ha BI alatt Power BI-t értesz, akkor az én javaslatom, hogy előbb könyv az alapok miatt (implicit v. explicit measure, row v. filter context) aztán Youtube. Könyv esetén Marco Russo-t javaslom (unalmas, de alapos).
Youtube-ról szintén Marco Russo.
Ha vizualizációs tippek kellenek akkor Bas.
Hasznos ötletek pedig Goodly illetve Guy in a Cube csatornáin.3. SAP Scripting: ehhez kellene egy programozási nyelv ismerete, Excel miatt VBA a kézenfekvő, de Pyhton-al is lehet SAP-ot vezérelni, pár éve nem volt túl jó, mostanában nem néztem.
Scriptinghez nélkülözhetetlen Stefan kis Tracker tool-ja. Lehet vele scripteket felvenni, SAP képernyőn lévő objektumokat vizsgálni.
Youtube-on Csongor-nak van pár videója.
Illetve az én Github-omon is van egy minta script. Én inkább MM modulban dolgozok (vendor, material, PO stb), korábban ECC 6.0, most már S4/HANA verzióban.Ha VBA-t akarsz tanulni, akkor én John Walkenbach Excel 2013 Power Programming with VBA könyvét tudom javasolni. Még mindig érdemes a VBA-t megtanulni, mivel az asztali verziókban még hosszú ideig támogatva lesz és az alapok könnyűek.
Ha nem akarsz programozni tanulni, akkor alternatívák:
1. SAP-ba SQVI tranzakcióban lehet saját lekérdezéseket/riportokat csinálni táblák összekapcsolásával. Meg vannak a korlátai, de sokat segíthet ha sok táblából kell összeszedned az adatokat.
2. Vannak kész script toolok (Winshuttle, AutoIT, DataLoader).üdv
-
Mutt
senior tag
Szia,
Pár változat, ami első olvasatra műkődőképesnek tűnik.
Javaslom előbb egy próba fájlon kipróbálni.https://excel-dashboards.com/blogs/blog/how-to-force-workbook-close-after-inactivity-excel
https://excelribbon.tips.net/T008192_Forcing_a_Workbook_to_Close_after_Inactivity.html
üdv
-
-
Mutt
senior tag
válasz
#77257183
#51411
üzenetére
Szia,
"...a kiszámolt dátum az legyen ahová már megtörtént a gólos oszlopokba az adatbevitel..."
Ha a gólok oszlopa alapból üres és csak akkor kerül kitöltésre (akár 0-val ha már lement a mecs), akkor ez a képlet.
=NAGY(SZŰRŐ($A$1:$A$1000;(($B$1:$B$1000=J2)+($C$1:$C$1000=J2))*($D$1:$D$1000<>""));5)Kis magyarázat: vannak olyan feltételek, amelyek ha egyszerre teljesülnek az jó nekünk (pl. 2 számot eltaláltunk az 5-ös lottón), illetve olyanok amelyek egymástól függetlenül teljesülnek az a jó (pl. beakarok menni a lakásba, akkor az is jó ha van lakáskulcsom, de az is jó ha van vki otthon aki be fog engedni).
Az első az ÉS kapcsolat, a másik a VAGY.Excelben ÉS és VAGY függvények léteznek és érdemes használni őket, de nem műkődnek több cellán.
Ha több cellán kell ÉS kapcsolatot használnunk, akkor a SZORZÁS jelet kell használni.
(pl. (A1:A10="szombat")*(B1:B10="utazás") azon kombinációt adja vissza, ahol egy sorban szerepel szombat és melette utazás).VAGY-ot pedig az összeadással lehet szimulálni.
(pl. (A1:A10="szombat")+(B1:B10="utazás") azon sorokat adja vissza, ahol egyik oszlopban van szombat, és másikban bármi más, illetve azokat ahol a második oszlopban van az utazás és az elsőben bármi.)A focis esetben előbb megkeressük, hogy B és C-oszlopokban mikor látható a keresett csapat neve. Ez VAGY (összeadás) feltétel. Ezek után ahol IGAZ-at kaptunk megnézzük, hogy a gól oszlop nem üres-e. Ez már ÉS lesz, mert csak azok kellenek ahol van eredmény rögzítve.
üdv
-
Mutt
senior tag
Hibát vétettem, amikor az O1:O37-es tartományt 3 sorra és 12 oszlopra kellett bontani.
Az OSZLOPOK($A:A)*SOROK($1:1) nem jó eredményt ad a második és harmadik sorban.
A helyes képlet OSZLOPOK($A:A)+12*(SOROK($1:1)-1).A lenti képen látható, hogy az elsőnél nincs minden szám 36-ig felsorolva, míg a másiknál már van.
Aki pedig MS365-öt használ, annak pedig a SORSZÁMLISTA (SEQUENCE) függvény javasolt.
-
Mutt
senior tag
válasz
#77257183
#51386
üzenetére
Szia,
Excel verzió számot nem írtál, de 2 megoldás, amelyik mindegyike használ egy segédoszlopot.
1. MS365 esetén SZŰRŐ (FILTER) függvénnyel megkeressük, hogy mikor volt a csapat legkorábbi mérkőzése az utolsó 5-ből.
I2-ben a képlet (az első 1000 sort nézi, bővítsd ha szükséges, illetve nézi hogy otthon vagy vendégként játszott, ha csak egyik kell akkor a plusz jel előtti vagy utáni rész törölhető):
=NAGY(SZŰRŐ($A$1:$A$1000;($B$1:$B$1000=J2)+($C$1:$C$1000=J2));5)2. Korábbi verzió esetén az I2 képlete (itt csak otthon játszást nézi a képlet!):
=SZORZATÖSSZEG(NAGY(($B$2:$B$1000=J2)*($A$2:$A$1000);5))Ezek után a többi értéket SZUMHATÖBB és DARABHATÖBB-el lehet számolni.
Győzelem: =DARABHATÖBB(B:B;J2;F:F;1;A:A;">="&I2)+DARABHATÖBB(C:C;J2;F:F;2;A:A;">="&I2)
Vereség: =DARABHATÖBB(B:B;J2;F:F;2;A:A;">="&I2)+DARABHATÖBB(C:C;J2;F:F;1;A:A;">="&I2)
Szerzett gólok: =SZUMHATÖBB(D:D;B:B;J2;A:A;">="&I2)+SZUMHATÖBB(E:E;C:C;J2;A:A;">="&I2)
Kapott gólok: =SZUMHATÖBB(E:E;B:B;J2;A:A;">="&I2)+SZUMHATÖBB(D:D;C:C;J2;A:A;">="&I2)
Pontok: =SZUMHATÖBB(G:G;B:B;J2;A:A;">="&I2)+SZUMHATÖBB(G:G;C:C;J2;A:A;">="&I2)A képletekben az első rész otthoni, a második a vendégként elért értéket adja. Ha csak egyik kell akkor töröld a nem kívánt részt.
Ha szeretnél Excel-t tanulni, akkor ezt a feladatot Power Query-ben csoportosítással, majd sorbarendezéssel és pár oszlop beszúrásával is meg lehet oldani. Ehhez legalább Excel 2013-as verzió kell.
üdv
-
Mutt
senior tag
válasz
rovgab
#51401
üzenetére
Szia,
Nem írtad az Excel verziót, de a korábban kapott egymásba ágyazott HA-k helyett lehet mást is használni.
=KERES("A";A1:F1), ahol A1:F1 a 6 oszlop, ahol vannak a kódok. Csak 0 esetén hibát dob, ha
ez gond akkor HAHIBA-ba lehet tenni.Ha Excel 2019-ed vagy újabbad van, akkor pedig a HAELSŐIGAZ (angolul IFS) függvény is műxik,
=HAELSŐIGAZ(A1<>0;A1;B1<>0;B1;C1<>0;C1;D1<>0;D1;E1<>0;E1;F1<>0;F1;1;"Mind nulla")A második dolgot szerintem túlbonyolítod.
Ha a mintád valós, akkor a kód első 6 karaktere megadja a kívánt választ.
Ha ez a következtetés helyes, akkor ha O1:O37 tartományban vannak a keresett kódok és a fenti keresési táblát az U2:V7-es cellák tartalmazzák, akkor A1-ben a képlet - amit tudsz másolni - ez:
=FKERES(BAL(INDEX($O$1:$O$37;OSZLOPOK($A:A)*SOROK($1:1));6);$U$2:$V$7;2;0)Ha elnéztem vmit és mégis kell az A15:L32-es tartomány, akkor javaslom, hogy inkább egy oszlopba egymás alá másold be a kombinációkat és hozzájuk tartozó eredményt vhogy így:
Innentől egy FKERES megadja a választ, mint az előbb.Ha nem lehet egy oszlopba másolni, akkor jön a csúnya változat. P-oszlopban van ez a csúnya képlet, ami kikeresi hogy melyik sorban találta meg az O-oszlopban látható értéket.
=SZORZATÖSSZEG(($A$15:$L$15=O1)+($A$16:$L$16=O1)*2+($A$17:$L$17=O1)*3+($A$18:$L$18=O1)*4+($A$19:$L$19=O1)*5+($A$20:$L$20=O1)*6+($A$21:$L$21=O1)*7+($A$22:$L$22=O1)*8+($A$23:$L$23=O1)*9+($A$24:$L$24=O1)*10+($A$25:$L$25=O1)*11+($A$26:$L$26=O1)*12+($A$27:$L$27=O1)*13+($A$28:$L$28=O1)*14+($A$29:$L$29=O1)*15+($A$30:$L$30=O1)*16+($A$31:$L$31=O1)*17+($A$32:$L$32=O1)*18)Ekkor A1-ben a képlet:
=INDEX($M$15:$M$32;INDEX($P$1:$P$37;OSZLOPOK($A:A)*SOROK($1:1))) -
Mutt
senior tag
válasz
Zazunga
#51377
üzenetére
Szia,
A BK18-as cellában állsz és a képleten látszik, hogy a 19-es sorból olvassa az adatokat, ami hibás.
Írd át 18-asra, illetve érdemes lenne a KERES-ben rögzíteni a tartományt, mert el fog mászni másoláskor.
=HA(BI18="";"";DARAB(BI$18:BI18)+KERES(2^10;BE$18:BE$56))Érdemes lenne az félidős tábla végén (57-es sor?) az eredményt kiíratni és a második félidőben ezt az értéket továbbvinni, mint állandóan KERES-el visszanézni az eredményt.
üdv
-
Mutt
senior tag
válasz
Zazunga
#51368
üzenetére
Szia,
Ha a 3. sortól indul a gólszerzők felvitele, és a gólok a D:E oszlopokba kerülnek, akkor
D3-ban a képlet: =HA(B3="";"";DARAB(B$3:B3)) ezt húzhatod le.
Az eredeti kérdésre: "..hogyan tudom megtalálni az akár több üres sorral fentebb lévő utolsó gólértéket"
Változatok:
1. Mivel növekvő sorszámokról van szó ezért a MAX függvény tud segíteni. pl. a 7-es sorban: =MAX(D3:D6)
2. Legutolsó kitöltött cellaértéke: =KERES(2^10;D3:D6)üdv
-
Mutt
senior tag
Sziasztok,
51352-es kérdésben rangsor és korreláció számításhoz adtunk Excel függvényes megoldást, de közben készült egy Power Query (PQ) megoldás is.
A probléma az, hogy PQ-ben nincs se korrelációs, se rang.átlag függvény. Hasonlók vannak, de nem ugyanazok. Természetesen lehet saját függvényeket létrehozni és most ezt szeretném bemutatni azoknak akiket érdekel.
A saját függvény egy lekérdezés, csak úgy mint a többi, de itt nekünk kell az M-kódot (PQ nyelvezete) írni.
A korreláció függvény kódja ez lett://the formula is available on https://support.microsoft.com/en-US/office/correl-function-995dcef7-0c0a-4bed-a3fb-239d7b68ca92
(numbers1 as list, numbers2 as list) as number =>
let
avarage1 = List.Average(numbers1),
avarage2 = List.Average(numbers2),
x1 = List.Transform(numbers1, each _ - avarage1),
y1 = List.Transform(numbers2, each _ - avarage2),
x = List.Zip({x1, y1}),
nominator = List.Sum(List.Transform(x, each _{0} * _{1})),
denominator = Number.Sqrt(List.Sum(List.Transform(x, each _{0} * _{0})) * List.Sum(List.Transform(x, each _{1} *_ {1}))),
result = nominator / denominator
in
resultAz elején megadjuk hogy milyen inputokat fogad el a függvény, majd jönnek a számítási lépések (átlagot számolunk, majd eltérést az átlagtól, majd négyzetes eltérést és a végén már csak osztanunk kell.
Többször használtam a List.Transform iterátort, amely ahogy végig megy a lista elemein kiszámolja az értékeket a transzformációs függvény alapján.Használata utána már olyan egyszerű, mint az Excel függvényé.
Rangsorhoz van beépített megoldás (Table.AddRankColumn), de nem úgy működik mint a RANG.ÁTL, így ezt is inkább megcsináltam függvénnyel. Ennek egy kicsit csúnyább lett a kódja, mert próbáltam meg dinamikusra csinálni illetve hogy egyszerre számolja ki a rangsort két különoszlopra.
(input as table, sort1 as text, sort2 as text) as table =>
let
Source = input,
//memoize the original columns for rearrange
OriginalColumnOrder = Table.ColumnNames(Source),
//add index to be able to restore original
AddIndex = Table.AddIndexColumn(Source, "RowID", 1, 1, Int64.Type),
//sort the table by given criteria
SortBy1 = Table.Sort(AddIndex, {sort1, Order.Descending}),
//add ranking column
AddRankIndex1 = Table.AddIndexColumn(SortBy1, "Rank", 1, 1, Int64.Type),
//calculate the average rank (as excels RANK.AVG function)
GroupBy1 = Table.Group(AddRankIndex1, sort1, {
{Text.Combine({"Rank ", sort1}), each List.Average([Rank])},
{"Data", each _, type table}
}),
//get the list of the columns what we need for the next step
Columns1 = List.RemoveItems(Table.ColumnNames(GroupBy1[Data]{0}), {sort1, "Rank"}),
//by expanding the columns we are
Expand1 = Table.ExpandTableColumn(GroupBy1, "Data", Columns1),
//do the same things for the other column
SortBy2 = Table.Sort(Expand1, {sort2, Order.Descending}),
AddRankIndex2 = Table.AddIndexColumn(SortBy2, "Rank", 1, 1, Int64.Type),
GroupBy2 = Table.Group(AddRankIndex2, sort2, {
{Text.Combine({"Rank ", sort2}), each List.Average([Rank])},
{"Data", each _, type table}
}),
Columns2 = List.RemoveItems(Table.ColumnNames(GroupBy2[Data]{0}), {sort2, "Rank"}),
Expand2 = Table.ExpandTableColumn(GroupBy2, "Data", Columns2),
//restore the table to original sequence
OriginalSort = Table.Sort(Expand2, "RowID"),
RemoveExtraColumn = Table.RemoveColumns(OriginalSort, "RowID", MissingField.Ignore),
//reorder the columns to original logic
ReOrderColumns = Table.ReorderColumns(RemoveExtraColumn, List.Combine({OriginalColumnOrder, {Text.Combine({"Rank ", sort1})}, {Text.Combine({"Rank ", sort2})}}))
in
ReOrderColumnsItt ahogy látszik egy adattáblát adunk át a függvénynek és a két oszlop nevét, amelyekre sorrendet kell meghatározni. A sorrend meghatározás 4 lépésből áll:
1. csökkenő érték szerint sorba rendezzük a táblát
2. hozzáadunk egy sorrend (Rank) oszlopot a táblához.
3. csoportosítjuk érték szerint az adatokat és a sorrend átlagát számoljuk ki közben.
4. kibontjuk a csoportosítot adatokat.Ezt pedig így lehet használni.
üdv
-
Mutt
senior tag
Szia,
"...mi alapján működik a tartományok elkülönítése?"
A DARABHATÖBB esetén az első két páros feltétel ($B:$B;$B2 és $D:$D;$D2) válogatja ki, hogy az adott sor melyik tartományba kerül, mivel mindig az aktuális sor értékelőivel ($B2 és $D2 feltétel, ami lefelé húzva mindig az aktuális sorhoz fog igazodni) megegyező értékeket mutat. A DARABTELI/DARABHATÖBB kevésbé használt funkciója, hogy nem csak a konkrét egyezéseket lehet megszámolni hanem lehet kisebb vagy nagyobb relációt is használni és ezzel sorrendet is lehet számolni.
A korrelációhoz más megoldás kell, mivel oda cellatartományt (range) kell megadni, amit INDEX / ELTOLÁS / XKERES-al szoktunk meghatározni dinamikusan. Itt a segít az ÖSSZESÍT függvény, amely tud tömb-műveleteket elvégezni (ezek olyan műveletek amikor egy lépésben több cellával dolgozunk és eredményként az összes cellához kapott értéket megkapjuk). Előnye még ennek a függvénynek, hogy nem kell Control+Shift+Enter (CSE)-rel bevinni (a SZORZATÖSSZEG még ilyen), a többi függvényhez viszont Excel 2021-ig kell használni. Végül pedig egy hasznos opciója az ÖSSZESÍT-nek hogy a hibás értékeket tudja kezelni, ez a második paraméter (a 6 azt jelenti, hogy ugorja át a hibás értékeket).
A képletben HA(($B$2:$B$10000=$B2)*($D$2:$D$10000=$D2);1;0) a lényeg a tartományok kiválasztásánál, ugyanúgy mint a DARABHATÖBB-nél az aktuális sor értékelőit keresi meg a B és D-oszlopokban (pontosabban az első 10 ezer sorában). Ha egyezik akkor 1-et ad vissza, ha nem akkor 0-t. A két feltétel között szorzás van, ami az ÉS kapcsolatnak felel meg (csábító lenne az ÉS függvény használata helyette, de az nem add vissza tömböt. Megjegyzés: haa VAGY kapcsolatra lenne szükség akkor plusz jel kellene ide.). Szóval kapunk 10 ezer db eredményt amik között lesz 1 és 0, ezzel ha elosztjuk a sorszámokat, akkor ahol egyezés van ott számot kapunk minden más esetben a 0-val osztás miatt hibát. Ekkor jön a 6-os opciója az ÖSSZESÍT-nek, ami csak a számokat tartja meg ahonnan már csak a legkisebb és legnagyobb számokat kell megtartanunk hogy tudjuk mettől-meddig tart a tartomány.
A végén az INDEX-el használom ezeket (ELTOLÁS is lehetne ahogy Fferi mutatja, de ezzel az a gond, hogy lassítja a füzetet, miert minden esetben újraszámolja az értékeket feleslegesen. Angolul [L:https://www.youtube.com/watch?v=Jev5ATXwnOs]Mynda videóját[/L] javaslom erről).
üdv -
Mutt
senior tag
Szia,
Milyen Excel verziót használsz?
Egy 2010-től működő megoldás két segédoszloppal, hogy rövidebbek legyenek a képletek.
Az F2-ben az alábbi képlet van:
=DARABHATÖBB($B:$B;$B2;$D:$D;$D2;$C:$C;">"&$C2)+1/DARABHATÖBB($B:$B;$B2;$D:$D;$D2;$C:$C;"="&$C2)(Ez egyenlőség esetén nem pont azt a számot mutatja, mint a mintádban van 1,5 v. 2,5. Ha a te számod kell akkor ez a képlet:
=DARABHATÖBB($B:$B;$B2;$D:$D;$D2;$C:$C;">"&$C2)+HA(DARABHATÖBB($B:$B;$B2;$D:$D;$D2;$C:$C;"="&$C2)>1;1+1/DARABHATÖBB($B:$B;$B2;$D:$D;$D2;$C:$C;"="&$C2);1)A G2-ben a képlet majdnem ugyanaz csak a ">" és "=" jelek után a C2-t E2-re kell cserélned.
A korrellációhoz tudnunk kell hogy melyik sortól kezdődig és melyik sorig tart az összefüggő tartomány, erre van a két segédoszlop (min/max).
I2-ben a képlet:
=ÖSSZESÍT(15;6;SOR($C$2:$C$10000)/HA(($B$2:$B$10000=$B2)*($D$2:$D$10000=$D2);1;0);1)
J2-ben majdnem ugyanaz, csak a NAGY függvényt kell használni.
=ÖSSZESÍT(14;6;SOR($C$2:$C$10000)/HA(($B$2:$B$10000=$B2)*($D$2:$D$10000=$D2);1;0);1)Ezek után H2-ben a képlet:
=HA(SOR()=$J2;KORREL(INDEX($F$1:$F$10000;$I2):INDEX($F$1:$F$10000;$J2);INDEX($G$1:$G$10000;$I2):INDEX($G$1:$G$10000;$J2));"")üdv
-
Mutt
senior tag
Tegnap felrobbant az összes külföldi Excel-es MVP blogja/Youtube csatornája, ma pedig már a magyar tech fórumokon is jött a hír, hogy már Excelben is lehet Python-t használni.
Azonban még nem kell temetni se a VBA-t, se az OfficeScript-et, mert:
- még csak azok próbálhatják ki akik a BETA csatornára vannak feliratkozva,
- jelenlegi formájában adatok feldolgozásában és vizualizációban tud segíteni,
- kicsit körülményes a használata még (talán a régi Excel4-es makrókhoz hasonlít).Lássuk mi fog kisülni belőle. Amint lehet én is megnézem.
-
Mutt
senior tag
válasz
p5quser
#51315
üzenetére
Szia,
Ezt hoztam össze. A KeyDown-al nem, de a KeyUp-al megy amit akarsz.
Annyit kacifántoztam, hogy csak az első UP esetén ugrik a legutolsó elemre, hogy lehessen felfelé pörgetni a listát. Ehhez kell egy globális változó, bKeyUp nálam.Dim bKeyUp As Boolean 'igaz ha utoljára felfelé nyíl volt használva
Private Sub ComboBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
With ComboBox1
If KeyCode = vbKeyUp Then
'ha már nyomtak felfelé nyilat akkor kiléphetünk innen
If bKeyUp Then Exit Sub
'ha nem nyomtak akkor ugrunk a végére és beállítjuk hogy volt már felfelé nyíl használva
.ListIndex = .ListCount - 1
bKeyUp = True
Else
'ha más billentyüt nyomtak akkor elfelejtük hogy volt már felfelé nyíl nyomva
bKeyUp = False
End If
End With
End Sub
Private Sub UserForm_Initialize()
bKeyUp = False
With ComboBox1
.AddItem "Géza"
.AddItem "Paula"
.AddItem "Kriszta"
.AddItem "Aladár"
.AddItem "Blöki"
.AddItem "Maffia"
.AddItem "Mz/X"
.AddItem "Máris szomszéd"
.ListIndex = 0
End With
End Subüdv
-
Mutt
senior tag
Hali,
INDEX-el tudsz oszlopokat válogatni: a harmadik paraméter az oszlop választó és ha kapcsos zárójelben felsorolod, hogy a tartomány mely oszlopára van szükséged, akkor csak azok jelennek meg.
Angol nyelvű Excelben pontosvesszővel, magyarban backslash ("\") az oszlop elválasztó.
Ezek után szövegösszefűzéssel 1 cellába kiírattam az eredményt és azon megszámoltam az A-betüket.
Az egészet 1 lépésben is meg lehet csinálni ha nem kell a köztes eredmény. Microsoft365 esetén a LET függvénnyel még egyszerűsíteni is lehet:
Magyarul a képletek:
=SZÖVEGÖSSZEFŰZÉS("";IGAZ;INDEX(B2:L2;;{1\6\11}))
=HOSSZ(M2)-HOSSZ(HELYETTE(M2;"A";""))Ha Microsoft365-ön vagy akkor sima enter, korábbi változatokhoz Control-Shif-Enter kell az első képleten.
üdv
-
Mutt
senior tag
válasz
föccer
#51276
üzenetére
Szia,
Ha van időd, akkor nézz rá a Power BI-ra. A mondottak alapján jobb megoldás lehet, beépített time-inteligence funkciója segít az eltérő időintervallumok kezelésében, Power Query része makrót tud helyettesíteni és a DAX megoldja a számításokat viszonylag kis fájl méretben. Nálam 3 év adata kb. 1,5 millió sor és 30 oszlop 98 MB.
Hátrányt is mondok: DAX szivat ahol tud, Microsoft minden hónapban újabb változattal jön, amiben minden máshol van mint korábban, Power Queryben a legkisebb módosítás is újraszámolást eredményez ami miatt percekig nézem a homokórázó kurzort.
üdv
-
Mutt
senior tag
Szia,
Ahogy olvasom 2013-ban már addin-ként meg volt, csak aktiválni kell. Fájl menű Beállítások->Bővítmények ablakbal a Kezelésnél a COM-bővítményeknél engedélyezd a Power Map-et. Ezek után ha jól emlékszem, akkor lesz egy új Power menűcsoport és azon belül találod meg.
Vmi ilyen eredményt kapsz (a mintában irányítószám, város, állam/megye, utca adatok vannak, nincs GPS).
Nem Excel, de kezeli az Excel adatokat és manapság kedvelt a Power BI. A Desktop változata ingyenes, az online ára sem vészes.
Ebben pedig vmi ilyen eredményt kapsz.
üdv
-
Mutt
senior tag
válasz
bandus
#51244
üzenetére
Szia,
Ha OFFSZETet használsz akkor régi az Exceled, de 2016-tol az Adatok fülön az adatok beolvasása a Power Queryt indítja. Excel 2010hez külön lehet telepíteni.
Az adatsoron állva Adatok->Adatok beolvasása -> Táblázat/Tartományból betölti a PQt.
Ha nem konvertalja automatikusan akkor a fejlec bal oldalán az ABC123-ra kattintva a DateTime opciót válaszd.
A végeredményt Excelbe az adatok betöltése gombbal lehet elérni.YouTubeon sok videó van, de itt is segítünk.
Üdv
-
Mutt
senior tag
válasz
13128814
#51238
üzenetére
Szia,
2 hiba van a kódban.
1. VBA-ban angolul kell megadni a képleteket, vagyisG1 = "=IFERROR(VLOOKUP($A3,'\\win2012-adc\WORK\Termelés\SAP\2023\["
I3 = "'!$A$3:$S$400000,8,0),2)"2. Nem a Value tulajdonságot kell használnod, hanem a Formula-t. O365 esetén a Formula2 javasolt.
ujExcelDatum.Range("I3").Formula2 = iKepletHa a makrónak a célja, hogy az utolsó archivált fájlt és annak tartalmát akarod elérni akkor könnyebb/gyorsabb megoldás a Power Query használata. Youtube-on van sok video róla, de szívesen segítünk ha kell.
üdv
-
Mutt
senior tag
Szia,
Csináltam egy egyszerű adatbázist, ami az A21:I26 tartományban van.
A kérdésekre a megoldások pedig így néznek ki:
Az adatbázis függvényeknek 3 paramétere van, az első az adatbázis helye, a második hogy melyik oszlopból akarod az értéket megkapni (itt az adatbázis első sora az oszlop azonosítója azt kell megadni - lehet gépelni illetve cellahivatkozással megadni - pl. "Cég neve" kell és nem az hogy A-oszlop) az utolsó pedig hogy mely sorokra kell szűrni az adatbázisban. Ez az utolsó ami kicsit nehézkes, de egyszerűen csak ad meg az oszlop nevét amely alapján szűrni akarsz és alá az értéket (ami tartalmazhat operátorokat, pl. >10 vagy helyettesítő karaktereket pl. B*). Ha több értéket akarsz megadni, akkor egymás alá írd őket (ekkor VAGY kapcsolat lesz).
Az utolsó 2 kérdésre a legkönyebb a válasz, nézd a legutolsót, ahol csak a székhelyre kell szűrni. Megadtam az oszlop nevét és alá, hogy az 1-es érdekel minket (Budapest). Az AB.DARAB2 függvénynek pedig ez a két cella lesz az utolsó paramétere.
A 3-as kérdésben két feltétel szerint kell szűrni, székhely és ágazat. Egy sorban adtam meg a szűrési feltéleket, ezek ÉS kapcsolatot jelentenek vagyis csak azok a sorok lesznek érvényések ahol mindkét feltétel egyszerre teljesül. (Ha nem egy sorba írtam volna, akkor VAGY kapcsolata lesz, próbáld ki.)
A többi kérdés már ezek kaptafája, de itt annyi hogy van egy köztes lépés mert meg kell kapni előbb egy eredményt a végső lekérdezéshez. Igazából ha nem lenne kötelező csak adatbázis függvény használata, akkor az első kérdésnél a C3-as mezőben egy =MAX(H22:H26) képlet elég lenne és nem kellene a AB.MAX képlet.
Youtube-on Mynda szerintem jól elmagyarázza az egészet.
Még annyi hogy az első képletekben még az oszlop nevét közvetlenül megadtam ("Cég neve"), de a többinél már csak cella hivatkozást használtam. Te döntöd el melyik szimpi.
üdv
-
Mutt
senior tag
Szia,
Egyéb lehetőségek a már kapott megoldáson túl.
1. Az adatsoron állva Beszúrás menü, Kimutatás. Erről érdemes előbb interneten/youtubeon leirasokat nézned, de röviden a sorok dobozba a neveket, az értékbe pedig az összeadni kívánt oszlopot kell húzni.
2. MS365-os verziót használva pedig az EGYEDI függvénnyel ki tudod nyerni a neveket egy új helyre és mellé a már használt SZUMHA jöhet.Üdv
-
Mutt
senior tag
válasz
vilmaboy
#51038
üzenetére
Szia,
Nagy segítséget nem tudok adni, de anno csináltam pár animációt Excelben VBA-val.
https://github.com/viszi/codes/tree/master/Excel/Fun oldalon van pár, Clock/Maze/Langton's Ant/Game of life.
Makróval lehet csinálni vmit, de nem igazán evidens mivel az Excelben a megjelenítést befolyásolja hogy a gépnek illetve az Excelnek milyen műveleteket kell elvégeznie párhuzamosan.
A legtöbb esetben csak annyi kell, hogy mindig újabb adatokat adsz a grafikon alapját adó táblázathoz és az Excel ezt megjeleníti. A gond az időzítés, mert egy Sleep vagy Appliction.Wait nem mindig tart ugyanaddig.Bővítményt nem találtam ami igazán segít, olyanok vannak amelyek mozgó GIF-ekket használnak, de azt előbb elő kellene állítani hogy lehessen használni.
Az Excel nem igazán erre van kihegyezve, a 3Ds térképpel pedig én eddig csak szenvedtem.
Szerintem más eszközt kell keresned.üdv
-
Mutt
senior tag
válasz
föccer
#50989
üzenetére
Szia,
EGYEDI helyett régebben tömb-függvényt kellett használni. Illetve egy rejtett fülön egy Kimutatás is használható rá.
SORBA.RENDEZ helyett DARABTELI vagy ÖSSZESÍT/SZORZATÖSSZEG.
SZŰRŐ a legnehezebb de SZUMHA/ÖSSZESÍT/SZORZATÖSSZEG és INDEX. Vagy egy Kimutatás és Szeletelő (Slicer)
Mivel az újabb függvényeket a régi Excel nem tudja értelmezni, ezért mentéssel nem lehet a problémát megoldani. A képleteket kell átdolgozni, hogy a régi rendszer megértese a kérést.
Mellesleg Power Query Excel 2010-hez telepíthető és már a korai változatok is tudták az általad említett dolgokat. Persze továbbra is nem real-time mint egy függvény.
üdv
-
Mutt
senior tag
válasz
user112
#50980
üzenetére
Szia,
Excel változattól függően 2 megoldás.
A) Ha Microsoft 365-öd van:
O2-ben a képlet:
=INDEX(SORBA.RENDEZ(FÜGG.HALMOZÁS($B$1:$M$1;B2:M2);2;-1;1);1;{1\2\3})
R2-ben a képlet:
=INDEX(SORBA.RENDEZ(FÜGG.HALMOZÁS($B$1:$M$1;B2:M2);2;-1;1);2;{1\2\3})A függőleges halmozással a fejlécet és az aktuális sorban lévő értékeket egy tömbbe fogjuk össze. Ezt aztán csökkenő érték szerint sorba rendezzük. Az INDEX-el az első 3 sort (ezt a {1\2\3} rész csinálja) kiiratjuk.
B) Ha Office 2010 vagy újabban Power Query-vel (PQ) pár lépés. Itt annyi megkötés van, hogy az eredmény nem frissül automatikusan NEKED kell frissítened vagy beállítanod hogy adott időközönként, vagy esetleg a fájl megnyitásakor frissüljön.
A lépések:
1. PQ szerkesztőbe beolvastatni az adatokat, Adatok -> Adatok beolvasása -> Táblázatból/tartományból.
2. Első oszlopot kijelölöd, majd Átalakítás -> Többi oszlop elemi értékre alakítása.
3. Első oszlop továbbra is kijelölve és Kezdőlapon -> Csoportosítás szempontban a minden sor müvelet legyen.
4. Ezek után Oszlop hozzáadása -> Egyéni oszlop. A képlet pedig ez: =Table.AddIndexColumn(Table.Sort([Adatok], {{"Oszlop1", Order.Ascending}, {"Érték", Order.Descending}}),"Index",1,1)
Itt két dolgot csinálunk: csökkenő sorba rendezzük az értékeket és beszúrunk egy új oszlopot ("Index") az eredménybe, ami 1-től indulva egyesével növekszik.
5. Az új oszlop jobb felső sarkában kibontás ikonra kattinunk és kibontjuk sorokba az eredményt.
6. Az Index oszlop jobb felső sarkába kattintva beállítjuk a szúrőt, hogy csak a 3 vagy annál kisebb értékek érdekelnek minket.
7. Töröljük a felesleges oszlopokat.
8. Visszatöltjük Excelbe az eredményt.
üdv
-
Mutt
senior tag
válasz
Reinhardt
#50969
üzenetére
Szia,
Lineáris interpolálással meg lehet becsülni a köztes értéket.
Lépések:
1. Hónap kezdőnapjánk meghatározása. Az E-oszlopban a képlet: =EOMONTH(D2;-1)+1
2. Meghatározni, hogy ez előző leolvasás és a hónap kezdőnapja között hány nap telt el.
Az F-oszlop képlete: =E2-D3-1
3. A C-oszlopban van a két leolvasás közötti fogyás, ha elosztjuk a két leolvasás között eltelt napok számával akkor tudjuk a napi átlag fogyasztást. Ezt megszorozzuk a 2-es lépésben kapott napokkal megkapjuk hogy hó elejéig mennyi fogyhatott. Végül hozzáadjuk ezt az előző hónapi értékhez.G-oszlopban a képlet: =B3+INT(C2/(D2-D3)*F2)Ha segédoszlopok nélkül kell, akkor pedig az egész egybegyúrva (és kicsit egszerűsítve):
=B3+INT(C2/(D2-D3)*(EOMONTH(D2;-1)-D3))üdv
-
Mutt
senior tag
Szia,
Ahogy Fferi írta.
1. Tippre nem magyar nyelven van használva a program.
Esetleg nem az online változatban dolgoztatok?
2. Fájl -> Fiók -> Frissítési beállítások.
kb. 1 éve már Microsoft 365-nek hívják az Office 365-öt, így a telepített alkalmazások között is az M-betünél lesz.
üdv
-
Mutt
senior tag
válasz
TillaT
#50934
üzenetére
Szia,
Próbáld ki ezt az egyéni szamformátumot: 0;-0;"";"X"
Ettől még a szerkesztő lécen látszik a kijelölt cella eredeti tartalma. Ha nem akarod ott sem mutatni, akkor a cella védelemnél a rejtett opciót és a lapvédelmet kell használnod.Egy másik megoldás, hogy az összefűzésben az X-en kívüli (neked fontos) részeket a T nevű függvénybe teszed. Vhogy így: ="X"&T(ÜRES("saját szöveg"))
üdv
-
#50799
Mutt
senior tag
botond2225
#50796
Mutt
senior tag
válasz
botond2225
#50796
üzenetére
Szia,
Ha átszervezed a táblákat, akkor SZUMHATÖBB-el is meg lehet oldalni. Szerintem könnyebben kezelhető is lenne sorokban tárolni, mint oszlopokban.
üdv
-
Mutt
senior tag
válasz
andreas49
#50785
üzenetére
Szia,
Nézd meg ezt az UDF-et.
Használata: =Hasonlo(<cella amihez hasonlót keresünk>;<tartomány a hasonló szövegekkel>;<max eltérések száma>;<kis és nagybetű eltérjen>)Function Hasonlok(mit As Range, hol As Range, Optional max_elteres As Long = 2, Optional kisnagybetuazonos As Boolean = False) As Variant
Dim dictMit As Object
Dim dictHol As Object
Dim adat As Range
Dim c As Long, elteres As Long
Dim key As String, val As Long
Dim collEredmeny As New Collection
Dim arrEredmeny()
'late biding-gal létrehozunk két szótárt, ahol {betű:darabszám} párosokat tudunk képezni
Set dictMit = CreateObject("Scripting.Dictionary")
Set dictHol = CreateObject("Scripting.Dictionary")
'on error a collection miatt kell, mert kiakad ha egy már létező elemet akarunk újra felvenni
On Error Resume Next
'végigmegyünk a megadott tartomány elemein
For Each adat In hol
'átugorjuk ha véletlenül a tartomány rész az eredeti szöveg amihez hasonlókat keresünk
If adat.Address <> mit.Address Then
'az eredeti szöveget és hasonlóság miatt vizsgáltat felbonyjuk {betű:darabszám} párosokra
Call felbont(Trim(adat.Text), dictHol, kisnagybetuazonos)
Call felbont(Trim(mit.Text), dictMit, kisnagybetuazonos)
'megnézzük, hogy a két szövegben mely betük egyeznek és a darabszámukat csökkentjük a
'másik szövegben található darabszámmal
For c = 0 To dictMit.Count - 1
key = dictMit.Keys()(c)
If dictHol.exists(key) Then
val = dictHol(key)
If val >= dictMit(key) Then
dictHol(key) = val - dictMit(key)
dictMit(key) = 0
Else
dictMit(key) = dictMit(key) - val
dictHol(key) = 0
End If
End If
Next c
'eltéresek megszámolása
elteres = szamol(dictMit) + szamol(dictHol)
'ha a limit alatt vagyunk eltérésekben akkor elrakjuk a szöveget
If elteres <= max_elteres Then collEredmeny.Add adat.Text
End If
Next adat
On Error GoTo 0
'tömbként visszaadjuk a talált elemeket ha vannak, különben üres szöveget adunk
If collEredmeny.Count > 0 Then
ReDim arrEredmeny(1 To collEredmeny.Count)
For c = 1 To collEredmeny.Count
arrEredmeny(c) = collEredmeny.Item(c)
Next c
Hasonlok = arrEredmeny
Else
Hasonlok = ""
End If
End Function
Private Function felbont(s As String, o As Object, m As Boolean)
Dim c As String
Dim x As Long
'töröljük az eddigi tartalmat
o.RemoveAll
'ha szükséges akkor mindent nagybetűsre alakítunk
If m Then s = UCase(s)
'felszabdaljuk a szöveget {betu:darabszám} párosokra
While Len(s) > 0
c = Left(s, 1)
x = Len(s) - Len(Replace(s, c, ""))
o.Add c, x
s = Replace(s, c, "")
Wend
End Function
Private Function szamol(o As Object) As Long
Dim x As Long
'megszámoljuk hány esetben fordul elő NEM nullaszor egy betű
'ezek azok amelyek a másik szövegben nem voltak megtalálhatók
szamol = 0
For x = 0 To o.Count - 1
If o.Items()(x) > 0 Then szamol = szamol + 1
Next x
End Functionüdv
-
Mutt
senior tag
válasz
föccer
#50742
üzenetére
Szia,
{1,3} angol Excelben, magyarban helyesen {1\3}.
Vagy a mintádat nézve, a végén a DARAB2 helyett ez kell: SORSZÁMLISTA(;DARAB2(A1:K1)
Fontos, hogy a sorszámlista első paramétere üres, a másodikba kell a DARAB2, hogy egy oszlop elrendezésű tömbbe kapjuk meg a sorszámokat.A képletedben a SORSZÁMLISTA és DARAB2 közé kell még egy zárójel, ezért kapsz rossz eredményt.
Ez lenne a helyes:
=INDEX(SORBA.RENDEZ(SZŰRŐ(A1:K28;D1:D28=N16);3);SORSZÁMLISTA(MIN(N17;DARABHA(D1:D28;"="&N16)));SORSZÁMLISTA(;DARAB2(A1:K1)))Az INDEX-et a kapcsos zárójelek közötti értékkel vesszük rá, hogy több eredményt adjon vissza és mivel Microsoft365-ön vagy az eredmények "átfolynak" (spill) a szomszédos cellákba. (Ha nem 365-ön lennél (talán Office2021 is menne még), akkor viszont máshogy kellene csinálni.)
üdv
Új hozzászólás Aktív témák
Hirdetés
- Windows 10/11 Home/Pro , Office 2024 kulcsok
- Microsoft licencek KIVÉTELES ÁRON AZONNAL - UTALÁSSAL IS AUTOMATIKUS KÉZBESÍTÉS - Windows és Office
- Adobe Előfizetések - Adobe Creative Cloud All Apps - 12 Hónap - NYÁRI AKCIÓ!
- Sea of Thieves Premium Edition és Egyéb Játékkulcsok.
- Kaspersky, McAfee, Norton, Avast és egyéb vírusírtó licencek a legolcsóbban, egyenesen a gyártóktól!
- ÁRGARANCIA!Épített KomPhone Ryzen 7 7800X3D 32/64GB RAM RTX 5070Ti 16GB GAMER PC termékbeszámítással
- Lenovo ThinkCentre M720q/ Dell OptiPlex 3060- 3070/ Hp EliteDesk 800 mini, micro PC-Számla/garancia
- Eladnád a telefonod? KÉSZPÉNZES OKOSTELEFON FELVÁSÁRLÁS azonnali fizetéssel!
- AZONNALI SZÁLLÍTÁSSAL Eladó Windows 8 / 8.1 Pro
- Bomba ár! Fujitsu LifeBook S761 - i7-2GEN I 8GB I 320GB I 13,3" HD I HDMI I W10 I Garancia!
Állásajánlatok
Cég: PC Trade Systems Kft.
Város: Szeged
Cég: CAMERA-PRO Hungary Kft
Város: Budapest