- Motorola Razr 60 Ultra - ez a kagyló könnyen megfő
- Milyen okostelefont vegyek?
- Android alkalmazások - szoftver kibeszélő topik
- Honor 200 Pro - mobilportré
- Motorola Edge 50 Neo - az egyensúly gyengesége
- Samsung Galaxy Watch7 - kötelező kör
- Samsung Galaxy A53 5G - kevesebbet többért
- Netfone
- Poco F6 5G - Turbó Rudi
- Samsung Galaxy A54 - türelemjáték
-
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
bela85
#53564
üzenetére
Szia,
Jöttek már jó megoldások, én csak pár plusz dolgot, de nem a végeredményt mutatom.
Excel 2016-ban van SZÖVEGÖSSZEFŰZÉS (TEXTJOIN), amit tudunk használni ahhoz hogy a vegyes adatsorból csak a számokat kinyerjük.
B2-képlete:=KIMETSZ(SZÖVEGÖSSZEFŰZÉS("";0;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ÖZÉP(A2;SOR(INDIREKT("A1:A"&HOSSZ(A2)));1);" ")))A képlet lényege, hogy a cella tartalmán egyesével végigmegy és ahol számot vagy gondolatjelet lát azokat megtartja, a többit szóközre cseréli. A szövegösszefűzés az egészet egybeírja. Mivel lesznek elől, középen és hátul felesleges szóközök ezeket a KIMETSZ-el eltávolítjuk illetve középen pedig egyre csökkentjük.
Aki MS365-ös verziót használ, annak van már REDUCE függvénye, ami tudja ezt egyszerűsíteni. A képlet C2-ben:
=KIMETSZ(REDUCE("";SORSZÁMLISTA(HOSSZ(A2));LAMBDA(s;c;s&HA(SZÁM(--KÖZÉP(A2;c;1))+(KÖZÉP(A2;c;1)="-");KÖZÉP(A2;c;1);" "))))üdv
-
ny.janos
tag
válasz
bela85
#53559
üzenetére
Még mielőtt elfelejteném: a számlaszámokon kívül más szám nem lehet a cellában, mert akkor a kód nem fog megfelelően működni!
Az előző bejegyzésemben írt eredményhez az alábbit kellene tenned (a megoldást 2019-es excelben készítettem ,csak remélni tudom, hogy a PQ függvényei működnek a 2016-os verzióban is).
1. A fájlod adott munkalapjának egyetlen oszlopát (melyben az adataid vannak) alakítsd táblázattá, az oszlop fejlécét írd át Adat-ra, a táblázat neve pedig Táblázat1 legyen.
2. A táblázat bármely részén állva az Adatok menü Táblázatból vagy tartományból pontját válaszd.
3. A PQ felugró menüjének kezdőlapján balra fent rákattints a Speciális szerkesztő-re, majd kijelölve az ott található kódot, az alábbira cseréld:let
Forrás = Excel.CurrentWorkbook(){[Name="Táblázat1"]}[Content],
#"Indexoszlop hozzáadva" = Table.AddIndexColumn(Forrás, "Index", 1, 1, Int64.Type),
#"Érték felülírva" = Table.ReplaceValue(#"Indexoszlop hozzáadva","-","",Replacer.ReplaceText,{"Adat"}),
#"Érték felülírva1" = Table.ReplaceValue(#"Érték felülírva"," ","a",Replacer.ReplaceText,{"Adat"}),
#"Hozzáadott előtag" = Table.TransformColumns(#"Érték felülírva1", {{"Adat", each "a" & _, type text}}),
#"Hozzáadott utótag" = Table.TransformColumns(#"Hozzáadott előtag", {{"Adat", each _ & "a", type text}}),
#"Oszlop felosztása karakterátalakítás alapján2" = Table.SplitColumn(#"Hozzáadott utótag", "Adat", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Adat.1", "Adat.2", "Adat.3", "Adat.4"}),
#"Oszlopok eltávolítva2" = Table.RemoveColumns(#"Oszlop felosztása karakterátalakítás alapján2",{"Adat.1"}),
#"Oszlop felosztása karakterátalakítás alapján3" = Table.SplitColumn(#"Oszlopok eltávolítva2", "Adat.2", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Adat.2.1", "Adat.2.2"}),
#"Oszlop felosztása karakterátalakítás alapján4" = Table.SplitColumn(#"Oszlop felosztása karakterátalakítás alapján3", "Adat.3", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Adat.3.1", "Adat.3.2"}),
#"Oszlop felosztása karakterátalakítás alapján5" = Table.SplitColumn(#"Oszlop felosztása karakterátalakítás alapján4", "Adat.4", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Adat.4.1", "Adat.4.2"}),
#"Oszlopok eltávolítva3" = Table.RemoveColumns(#"Oszlop felosztása karakterátalakítás alapján5",{"Adat.2.2", "Adat.3.2", "Adat.4.2"}),
#"Hozzáadott utótag2" = Table.TransformColumns(#"Oszlopok eltávolítva3", {{"Adat.2.1", each _ & "00000000", type text}}),
#"Hozzáadott utótag3" = Table.TransformColumns(#"Hozzáadott utótag2", {{"Adat.3.1", each _ & "00000000", type text}}),
#"Hozzáadott utótag4" = Table.TransformColumns(#"Hozzáadott utótag3", {{"Adat.4.1", each _ & "00000000", type text}}),
#"Többi oszlop elemi értékekre bontva" = Table.UnpivotOtherColumns(#"Hozzáadott utótag4", {"Index"}, "Attribútum", "Érték"),
#"Oszlopok eltávolítva4" = Table.RemoveColumns(#"Többi oszlop elemi értékekre bontva",{"Attribútum"}),
#"Kinyert első karakterek1" = Table.TransformColumns(#"Oszlopok eltávolítva4", {{"Érték", each Text.Start(_, 24), type text}}),
#"Típus módosítva" = Table.TransformColumnTypes(#"Kinyert első karakterek1",{{"Index", type text}}),
#"Oszlop felosztása pozíció alapján" = Table.SplitColumn(#"Típus módosítva", "Érték", Splitter.SplitTextByRepeatedLengths(8), {"Érték.1", "Érték.2", "Érték.3"}),
#"Oszlopok egyesítve" = Table.CombineColumns(#"Oszlop felosztása pozíció alapján",{"Érték.1", "Érték.2", "Érték.3"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Érték"),
#"Sorok csoportosítva1" = Table.Group(#"Oszlopok egyesítve", {"Érték"}, {{"Előfordulás száma", each Table.RowCount(_), Int64.Type}, {"Indexsorok száma", each Text.Combine([Index],","), type nullable text}}),
Egyéni1 = #"Oszlopok egyesítve",
#"Egyesített lekérdezések" = Table.NestedJoin(Egyéni1, {"Érték"}, #"Sorok csoportosítva1", {"Érték"}, "Egyéni1", JoinKind.LeftOuter),
#"Kibontott Egyéni1" = Table.ExpandTableColumn(#"Egyesített lekérdezések", "Egyéni1", {"Előfordulás száma", "Indexsorok száma"}, {"Előfordulás száma", "Indexsorok száma"}),
#"Oszlopok átnevezve" = Table.RenameColumns(#"Kibontott Egyéni1",{{"Index", "Sorszám"}}),
#"Típus módosítva1" = Table.TransformColumnTypes(#"Oszlopok átnevezve",{{"Sorszám", Int64.Type}, {"Előfordulás száma", type text}}),
#"Sorok rendezve" = Table.Sort(#"Típus módosítva1",{{"Sorszám", Order.Ascending}}),
#"Típus módosítva2" = Table.TransformColumnTypes(#"Sorok rendezve",{{"Előfordulás száma", type text}}),
#"Sorok csoportosítva" = Table.Group(#"Típus módosítva2", {"Sorszám"}, {{"Számlaszámok", each Text.Combine([Érték]," ; "), type text}, {"Előfordulások száma számlaszámonként", each Text.Combine([Előfordulás száma]," ; "), type nullable number}, {"Indexsorok száma számlaszámonként", each Text.Combine([Indexsorok száma]," ; "), type nullable text}}),
#"Típus módosítva3" = Table.TransformColumnTypes(#"Sorok csoportosítva",{{"Előfordulások száma számlaszámonként", type text}})
in
#"Típus módosítva3"4. A menüben a Bezárás és betöltés - Bezárás és betöltés adott elyre parancsot válaszd, majd a táblázatot kijelölve adj meg egy olyan cellát a munkalapodon, ahol már nincs adat, amit felülírnál. Ugyanabba a sorba tedd, ahol a táblázatod fejadatai vannak.
-
ny.janos
tag
válasz
bela85
#53557
üzenetére
Nos, ha vannak olyan cellák, amelyben több számlaszám is szerepel, akkor már halmozódnak a problémák, azt nem tudom, hogy PQ-vel meg lehetne-e oldani (illetve feltehetően igen, de ahhoz lehet, hogy már Mutt tudása kell).
Abban az esetben, ha csak egyetlen számlaszám szerepel egy cellában és biztosan nincs benne más szám a számlaszám 16 vagy 24 karakterén túl, úgy működik az általam javasolt megoldás a 8 db nullával történő kiegészítéssel, ahogy azt Fire/SOUL/CD is írta.
-
#53556
Fire/SOUL/CD
félisten
bela85
#53554
válasz
bela85
#53554
üzenetére
ny.janos - Ezt sem neked írom, és a korábbi válaszaimat is hagyd figyelmen kívül!
Bocsesz!
Az azonos profilképünk ne tévesszen meg.
De. Rendesen benéztem, ezért válaszoltam nem is Neked...
(Megkeveredtem, mint vasorrú bába a mágneses viharban)De pl: Erste Banknál meg pont hogy az 2x8 karakter azonos és az utolsó 8 számnál van eltérés általában.
A 24 jegyű számlaszámokkal nem kell tenni semmi, viszont a 16 számjegyűeket 24 számjegyre kellene konvertálni (8 db nullával kibővíteni) és ezt mindkét tartományban elvégezni (amit keresel oszlopban meg amiben keresel oszlopban is).amit keresel: 12345678-12345678
amiben keresel: 12345678-12345678-00000000
Ha az elsőt keresed a másodikban, akkor lesz egyezés, de fordítva már nem, pedig a 2 számlaszám ugyanaz.Szóval ezt Tőled kérdem (nem ny.janos-tól
)
1. Ez a makró megfelelne számodra?
2. elvégezzem benne azt a módosítást, ami elvégzi a 24 számjegyre konvertálást? -
-
ny.janos
tag
válasz
bela85
#53542
üzenetére
Szia!
Fire/SOUL/CD kérdésein túl pár további:
5. Jól értem, hogy valójában ismétlődéseket szeretnél keresni számlaszám alapján, azaz tényleg az oszlopon belüli azonosságokat szeretnéd megjelölni (pl. színnel) a számlaszám alapján (oszlopon belül mindkettő vagy több cellát)?
6. A cellában a számlaszám 16 vagy 24 karakterén kívül előfordulhat, hogy más szám is szerepel?
7. Az azonosságot a számlaszám hány karaktere alapján szeretnéd vizsgálni? Elegendő az első 16 karakter? Vagy ha szerepel 2x8 és 3x8 formátumban is, azt ne mutassa azonosnak?A legjobb lenne, ha fiktív adatokkal tudnál egy több sort tartalmazó mintát mutatni.
-
Fferi50
Topikgazda
válasz
bela85
#53542
üzenetére
Szia!
Sajnos a "szabálytalanságokat" nagyon nehéz szabályba rendezni. Valószínűleg több lépésre lesz szükség és segédoszlopokra.
Amire én gondoltam, képlettel szerintem ki lehet a számlaszámokat szedni egy segédoszlopra. A képlet pedig attól függ, milyen verziót használsz és a számok egyformán vannak a szövegen belül - mint a mintádban "-" jellel elválasztva.
O365 esetén használhatod a SZÖVEGELŐTTE, SZÖVEGUTÁNA függvényeket.
Korábbi verzióknál a SZÖVEG.KERES és a JOBB és BAL szövegdaraboló függvényeket.
A függvényeket attól függően paraméterezed, ahány kötőjel van a szövegben. Ez nem lesz azért túl sok verzió.
Ezután DARABHA (DARABTELI) függvénnyel megszámolhatod az ismétlődéseket, majd ennek eredménye alapján használhatsz feltételes formázást az eredeti oszlopodra. A segédoszlopokat pedig el is lehet rejteni.
Frissítés:
Mivel 2016-os Exceled van, az O365 függvények nem használhatók. Marad a második verzió vagy a makró.
Üdv. -
#53546
Fire/SOUL/CD
félisten
bela85
#53545
válasz
bela85
#53545
üzenetére
4. Általában fix (szóközkötőjelszóköz), de nem minden esetben, mivel nem csak én töltöm a táblát.
Tehát lehet szóköz nélküli kötőjel is?xxxxxxxx-xxxxxxxx-xxxxxxxx-Teszt Elek-K&Hde akár más elválasztó is bekerülhet a képbe?
MBH + Gipsz Jakab + xxxxxxxx-xxxxxxxSorolj fel néhány elválasztó karaktert, amit tipikusan előfordulhat/használtok
(pl. + és - és & stb stb)Muszáj tudnunk ilyen infókat, mert csak úgy lehet rá korrekt megoldást adni.
Excel 2016 révén -ha kivitelezhető is- az biztosan makrós megoldás lesz. Cellaképlettel -szvsz- nem kivitelezhető (nem egyforma sorrend ill különböző elválasztók miatt)vegyes elválasztó nem lehet! (ilyen esetet nem lehet feldolgozni)
MBH - Gipsz Jakab + xxxxxxxx-xxxxxxx -
#53544
Fire/SOUL/CD
félisten
bela85
#53542
válasz
bela85
#53542
üzenetére
1. Milyen verziójú az Excel?
2. Akkor vannak/lehetnek ilyen cellák is? (
hol ugyanilyen sorrendben, hol másképp, (ha pl nem tudtuk a bankot...
)Gipsz Jakab - xxxxxxxx-xxxxxxx (hiányos)
MBH - Gipsz Jakab - xxxxxxxx-xxxxxxx (más sorrend)
xxxxxxxx-xxxxxxxx-xxxxxxxx - Teszt Elek - K&H (más sorrend)3. csak és kizárólag számlaszám alapján kellene keresni?
4. a név, bank, számlaszámot elválasztó karakter az fix?
pl itt esetedben " - " (szóközkötőjelszóköz) -
ny.janos
tag
válasz
bela85
#45818
üzenetére
Sziasztok!
bela85: Ma sem értem ide mostanáig, meg el is felejtettem, de örülök, hogy megoldódott.
Fferi50: köszönöm a kisegítést.
Delila_1: Nem jobb, nem rosszabb, más. Ha valamennyi oszlopban előfordulhat több figyelembe veendő érték (igen, talán, lehet stb.) valamint a nem érték helyett is lehetnek más nem számítandó értékek (pl. soha, semmikor stb.), a tömbképlet akkor is működik érdemi átalakítás nélkül. (Én úgy értelmeztem, hogy bela85-nek ilyen megoldásra van szüksége, ezért lett ez az általam javasolt megoldás.)
Én mindenesetre mindig örülök, ha több megoldást is látok, mert sokszor én is tanulok ezekből. Például hogy adott esetben másként, egyszerűbben is gondolkodhatok.
-
gyendre2
tag
válasz
bela85
#45822
üzenetére
Köszönöm mindkettőtöknek !
Viszont valamiért HIÁNYZIK eredményt ad vissza. És igen, G oszlop akart lenni.
A G oszlopban dátumok vannak, Dátumra is van formázva. Az oszlop indexet jól adtam meg, pontos egyezésre keresek az A oszlopból.
A táblázatot próbáltam fixálva és nélküle is, nem jó.
Az egyezés biztos megvan, Ctrl+F megtalálja mindkettőt ha beírom a pontos nevet.
Sajnos nem tudom megmutatni a táblázat, személyes adatok vannak benne. -
-
Fferi50
Topikgazda
válasz
bela85
#45813
üzenetére
Szia!
Szerintem ott a gond, hogy a NEM benne van a keresendő értékek között (L3:M3), így persze a NEM is megfelelőnek számít. A keresendő értékek azL2:N2cellákban vannak, a D-G oszlopot is ezekhez kellene hasonlítani. AzL3:M3cellákra nincs szükség. A NEM ne legyen benne a keresendő értékek között.
Tehát, aholL3:M3hivatkozás van, azt írd átL2:N2hivatkozásra.
Üdv. -
ny.janos
tag
válasz
bela85
#45808
üzenetére
Szia!
Bocs, nem voltam nap közben. Próbálkoztam, ez lett belőle (elképzelhető, hogy van egyszerűbb, elegánsabb megoldás is, most így estefelé ennyi tellett tőlem). Segédoszlop nélküli megoldás nem tudom, hogy létezhet-e (nekem nem sikerült ilyet alkotnom).
Bármelyik oszlopban számolja az IGEN, LEHET, TALÁN értékeket, a lista értelemszerűen bővíthető. A tömbképlet első fele a Krit.fő.állapot oszlopot vizsgálja, míg a második fele annyi DARABTELI függvénnyel, ahány krit. állapot oszlopod van azt, hogy ezek közül szerepel-e valamelyikben az elfogadható válaszok valamelyike.
A képletet természetesen Ctrl + Shift + Enter -el zárd le (tömbképlet továbbra is).
Utóbb jöttem rá, hogy a képen még használt két mínusz jelre nincs is szükség.
=(SZUM(DARABTELI(H2;$K$2:$K$4))>=1)*(SZUM(DARABTELI(D2;$K$2:$K$4);DARABTELI(E2;$K$2:$K$4);DARABTELI(F2;$K$2:$K$4);DARABTELI(G2;$K$2:$K$4))>=1) -
ny.janos
tag
válasz
bela85
#45783
üzenetére
Szia!
Egyetlen cellába írható képletre nincs ötletem, de ha beszúrsz egy segédoszlopot, melyben egyetlen tömbképletben a SZUM függvénybe ágyazva az egyes oszlopokra vonatkozóan külön-külön vizsgálod a DARABTELI függvénnyel, hogy valamely neked megfelelő válasz szerepel-e az adott oszlopban, akkor attól függően hogy hány vizsgálandó oszlopod van vagy 0-át (mindegyik oszlopban "nem" érték szerepel), vagy legfeljebb az oszlopok számának megfelelő számot, illetve e kettő közötti értéket kell kapj. Ha ezt az egész képletet egy HA függvénybe ágyazod, úgy hogy 0 értékre 0, míg ettől eltérő értékre 1 eredményt kapj, akkor már csak összesítened kell a segédoszlopod (a képletet természetesen továbbra is tömbképletként kell lezárd).
(A HA függvény helyett egy szimpla >=1 reláció vizsgálat is használható, ha 1-el való szorzással átalakítod az IGENeket 1-é, a NEMeket 0-vá.)
Ha esetleg nem lenne érthető, akkor dobj fel egy fiktív adatokról készült képernyőképet ahol az oszlop és sorazonosítók is láthatók, úgy tudok konkrétummal is segíteni. -
Fferi50
Topikgazda
válasz
bela85
#45725
üzenetére
Szia!
Szeretném megmutatni, hogy több oszlop és feltétel esetében is működik, az a fontos csak, hogy egyenlő méretűek legyenek az oszlopok:
Az A oszlopban sárga, amiben van "a" betű. A B oszlopban zöld ha az érték 10 és 1000 közé esik. A C oszlopban kék az a cella, ahol az A sárga (mert van benne "a" betű) és a B oszlop zöld (mert a két érték közé esik). Természetesen itt is ÉS feltétel van.
Üdv. -
-
Fferi50
Topikgazda
válasz
bela85
#45706
üzenetére
Szia!
Szerintem ebből a szerkezetből nem lehet azt az elrendezést PIVOT-tal megcsinálni. Talán POVERPIVOT használatával igen, de ahhoz nem értek.
Mivel ez a tábla eleve "kereszttábla", vissza kellene alakítani úgy, hogy a kritériumok is egy fő ismérv legyen, mint a város, úgy gondolom.
Egy ötlet, ami kimutatással is megy:
Üdv.
-
Fferi50
Topikgazda
válasz
bela85
#45704
üzenetére
Szia!
Szerintem megy ez függvénnyel is (egyszerűsített példa):
A Darabhatöbb képlet tartományait kell helyesen megválasztanod. Remélem ez már működik a 2007-ben.
Először az A8-tól kezdődő részt csináld meg. A B9 cella képlete húzható lefele és oldalt. Ezután az így kialakult adatokat másolás - irányított beillesztés - transzponálás tudod az A14-el kezdődő formába hozni.
Üdv. -
Delila_1
veterán
válasz
bela85
#44350
üzenetére
Az újonnan beírt adatok felülírják a már ott lévőket.
Ha képletek vannak a Munka2 lap B oszlopában, akkor valóban értéket kell beilleszteni a Munka1-re.
Ez változik:
Do While .Range("B" & sor) <> ""
.Range("B" & sor).Copy
Sheets("Munka1").Range("B3").PasteSpecial xlPasteValues
ActiveWindow.SelectedSheets.PrintOut Copies:=1
sor = sor + 1
Loop -
Delila_1
veterán
válasz
bela85
#44348
üzenetére
Modulba tedd a makrót.
Sub Nyomtatas()
Dim sor As Long
sor = 2
Sheets("Munka1").Select
With Sheets("Munka2")
Do While .Range("B" & sor) <> ""
Sheets("Munka1").Range("B3") = .Range("B" & sor)
ActiveWindow.SelectedSheets.PrintOut Copies:=1
sor = sor + 1
Loop
End With
End Sub -
Fferi50
Topikgazda
válasz
bela85
#44330
üzenetére
Szia!
Én úgy tapasztaltam, hogy a legelső előfordulást hagyja meg az ismétlődések törlése során.
Ezért célszerű előtte úgy rendezni, hogy az Elfogadott kerüljön előre.
Rendezésnél adhatsz meg egyéni sorrendet is (pl. Elfogadott - Javított - Beérkezett stb.)
Ezután a rendezés Adószám - státusz, majd utána az ismétlődések törlése.
Üdv.
Új hozzászólás Aktív témák
Hirdetés
Fferi50
- Adobe Előfizetések - Adobe Creative Cloud All Apps - 12 Hónap - NYÁRI AKCIÓ!
- Windows 10/11 Home/Pro , Office 2024 kulcsok
- Eladó steam/ubisoft/EA/stb. kulcsok Bank/Revolut/Wise (EUR, USD, crypto OK)
- Windows 10 11 Pro Office 19 21 Pro Plus Retail kulcs 1 PC Mac AKCIÓ! Automatikus 0-24
- Vírusirtó, Antivirus, VPN kulcsok
- Huawei Nova Y90 128GB, Kártyafüggetlen, 1 Év Garanciával
- BESZÁMÍTÁS! Apple Macbook Pro 15" 2019 i9 9980HK 32GB 500GB Radeon Pro 560X hibátlan működéssel
- ÁRGARANCIA!Épített KomPhone Ryzen 9 5900X 16/32/64GB RAM RTX 5070 12GB GAMER PC termékbeszámítással
- ÁRGARANCIA! Épített KomPhone i5 14600KF 32/64GB RAM RTX 5070 12GB GAMER PC termékbeszámítással
- Apple iPhone 14 Pro, Kártyafüggetlen, 1 Év Garanciával
Állásajánlatok
Cég: PC Trade Systems Kft.
Város: Szeged
Cég: PC Trade Systems Kft.
Város: Szeged