Hirdetés
-
GAMEPOD.hu
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
-
Sutyi73
csendes tag
Sziasztok! Segítséget szeretnék kérni. Adott egy munkaidő nyilvántartási táblázat, amiben a BL5-ös cellában összegezni szeretném azoknak a celláknak az összegét amiben a tm + szám szerepel. Jelen példában tm 1,5 + tm 3 = 4,5
Köszönettel Sutyi73
Itt a minta:
[link] -
Mutt
senior tag
válasz
Sutyi73
#42801
üzenetére
Szia,
Excel 2007-től ez a tömbképlet használható:
=SZORZATÖSSZEG(HAHIBA((HELYETTE(B5:BK5;"tm";"")+0)*SZÁM(SZÖVEG.KERES("tm";B5:BK5));0))Control+Shift+Enter kell a képlethez.
Üdv
A tanácsaimat ingyen adom. Ha nem tetszik, akkor kérlek ne kritizáld! / https://github.com/viszi/codes/tree/master/Excel
-
sztanozs
veterán
válasz
Sutyi73
#42803
üzenetére
Azt jelenti, hogy a képlet megadása után nem sima entert, kurzornyilat nyomsz vagy elkattintasz a cellából, hanem Control+Shift+Enter-t egyszerre (pontosabban CTRL-t és SHIFT-et lenyomni és lenyomva tartani, utána/közben ENTER-t nyomsz).
JOGI NYILATKOZAT: A bejegyzéseim és hozzászólásaim a személyes véleményemet tükrözik; ezek nem tekinthetők a munkáltatóm hivatalos állásfoglalásának...
-
norby91
tag
Hali!
Röviden igy néz ki a Google táblázat lebutitva:
B oszlop függvény:
=HA(A1<="1999";1;KEREK.LE(A1/1000;0))Tehát, ha Az "A" oszlop értéke kisebb vagy egyenlő, mint 1999, akkor irjon 1-et, ha nagyobb, akkor az érték 1000-ed részét kerekitse lefelé.. Tehát 2000-től már 2-t kellene majd irnia.
Mindegyik érték mellé 1-es került.
Mi a hiba? Miért lett igaz az, hogy 55365<="1999" --> ezért került 1-es érték a cellába.
Esetleg ha van más megoldás, megköszönöm!
Előre is köszi.[ Szerkesztve ]
-
-
karlkani
aktív tag
Sziasztok!
Adott egy ~150000 soros adathalmaz, ami ~2000 felhasználó adatait tartalmazza. Minden adat külön sorban. Amre szükség lenne, nem egymás után van a szövegben, hanem a mintához hasonló módon.
Minta
........."id": 123456789,........."username": "Felhasználónév",.........A következőt kellene kinyerni a szövegből ("idézőjelek", "," nélkül, ahogy írom):
Felhasználónév 123456789Felhasználónév2 12345678910...Előre is köszönöm!
[ Szerkesztve ]
-
-
sztanozs
veterán
válasz
karlkani
#42812
üzenetére
Van bármi a sorokban a vessző után, illetve vannak még olyan sorok, ahol ez a struktúra, de nem kell az adat? Az "id" és az "username" szigorúan ilyen sorrendben fordul elő?
JOGI NYILATKOZAT: A bejegyzéseim és hozzászólásaim a személyes véleményemet tükrözik; ezek nem tekinthetők a munkáltatóm hivatalos állásfoglalásának...
-
Fferi50
Topikgazda
válasz
karlkani
#42812
üzenetére
Szia!
Ha ilyen sorrendben követik egymást az adatok és a : (kettőspont) az elválasztó, akkor az alábbi képleteket használhatod: (feltételezve, hogy az adatok az A1 cellától kezdődnek).
Felhasználónév oszlopra:=KÖZÉP(INDIREKT("A"&SOR()*8);SZÖVEG.KERES(":";INDIREKT("A"&SOR()*8))+2;HOSSZ(INDIREKT("A"&SOR()*8)))
Id oszlopra:=KÖZÉP(INDIREKT("A"&4+(SOR()-1)*8);SZÖVEG.KERES(":";INDIREKT("A"&4+(SOR()-1)*8))+2;HOSSZ((INDIREKT("A"&4+(SOR()-1)*8))))Ha nem az első sorban kezdődik, akkor a képletben az A után annyival nagyobb értéket írj, mint ahol az adatok kezdődnek. (Felhasználónévnél pl. "A" & 1+SOR(), Id-nél & 5 + SOR()-1, ha a 2. sorban kezdődnek az adatok.)
Ha a kettőspont után nem egy szóköz van, akkor a SZÖVEG.KERES függvény eredményéhez nem 2, hanem a szóközökkel megnövelt érték kell. (1 a kettőspont miatt, utána plusz a szóközök száma.)Ez a képlet húzható le végig, amikor elfogytak az adataid, hibát fog eredményezni, de gondolom ez nem olyan nagy baj.
Utána Ctrl+C és irányított beillesztés a képletes oszlopokra, hogy "maradandó" értéket kapj.Üdv.
[ Szerkesztve ]
-
karlkani
aktív tag
válasz
sztanozs
#42813
üzenetére
Nincs, ezekben a sorokban ennyi szerepel összesen. Egy felhasználónál ~100 sornyi adat van, változó. Az "id" minden esetben előrébb van az "username"-nél, a két adat közötti sorok száma is változó. Valami olyasmi kellene, hogy rákeresni az "id" szóra, majd találat után az adott cellában található számsort kiíratni egy cellába. Utána rákeresni az "username" szóra, a ott található nevet kiíratni az "id" számsorú cella mellé. Következő "id" számsor, az előző alá, mellé az adathalmazban következő "username" mezőben található név.
(#42814) Fferi50
Igen, ilyen sorrend van, az első "id" a 9. sorban van, a hozzá tartozó név a 69. sorban, de mint az előzőben írtam egy-egy felhasználónak nem azonos mennyiségű sora van a szövegben. -
Fferi50
Topikgazda
válasz
karlkani
#42815
üzenetére
Szia!
Akkor mutatnék egy szerintem használható megoldást, hogy érthető legyen a műveletek sorrendje, ezért 4 segédoszlop szükséges hozzá:
Első segédoszlop: meghatározzuk a username -k helyét:
A segédoszlop első cellájába írunk egy db 1-est. Ez ugyanígy lesz a második segédoszlopban is. Én a B oszloptól tettem a segédoszlopokat
A képlet a második cellától kezdve:=HOL.VAN("*username*";INDIREKT("$A" & B1+1 & ":$A$10000");0)+B1
A függvény megtalálja a username szövegrészt az A oszlopban, mindig az előző után keresi, ezért a tényleges helyhez hozzá kell adni az utolsó találat helyét.
A C oszlop 2. cellájának képlete: (az első cellában ugye ott van az 1)=HOL.VAN("*id*";INDIREKT("$A" & C1+1 & ":$A$10000");0)+C1
Ez pedig az id szövegrészt keresi, hasonló módon, mint az előző képlet.
A következő 2 oszlopban meghatározzuk az adott kereséshez tartozó tényleges cellaértékeket:
D2 cella képlete:=INDIREKT("A" & B2)
E2 cella képlete:=INDIREKT("A" & C2)
Ezután a következő két oszlopba kerülnek a "feleslegtől megtisztított" adatok:
F2 cella képlete:=KÖZÉP(D2;SZÖVEG.KERES(":";D2)+2;HOSSZ(D2))
G2 cella képlete:=KÖZÉP(E2;SZÖVEG.KERES(":";E2)+2;HOSSZ(E2))
Itt a SZÖVEG.KERES függvény eredményét módosítani kell, ahány szóköz van a kettőspont után (1+ szóközök száma).Ezután a két végleges oszlopot Ctrl+C majd beillesztés értékként művelettel véglegesíted. A segédoszlopok mehetnek a levesbe.
Üdv.
-
Fferi50
Topikgazda
válasz
zsolti_20
#42817
üzenetére
Szia!
Szerintem csak makróval (felhasználói függvénnyel) lehet.
Pl:Function fordit(hol As Range) As StringDim ch As Integer, sz As StringFor ch = Len(hol) To 1 Step -1sz = sz & Mid(hol, ch, 1)Nextfordit = szEnd FunctionEzt ugyanúgy használhatod, mint a beépített függvényeket.
Üdv.
-
ny.janos
tag
válasz
karlkani
#42815
üzenetére
Szia!
Ha nem visszatérő feladatról van szó, hanem csak most az egyszer kell az adatokat kinyerned, valamint az "id" és "username" ilyen struktúrában szerepel, akkor egy egyszerű sorbarendezéssel egymás alá kerül az összes id és ugyanazon sorrendbe szintén egymás alá az összes username. Innen aztán simán ki lehet másolni őket egy másik munkalapra egymás mellé.
Ezt követően egyszerűen eltávolítod a felesleges adatokat csere funkcióval * és ? helyettesítő karakterek használatával, vagy szövegfüggvénnyel kinyered azt a rész, amire szükséged van.
-
Mutt
senior tag
válasz
zsolti_20
#42820
üzenetére
Szia,
A1 cellában található szöveget/számot ezzel a tömbképlettel tudsz megfordítani:
=FŰZ(KÖZÉP(A1;HOSSZ(A1)-SOR(INDIREKT("$A$1:$A"&HOSSZ(A1)))+1;1))Dátumot szöveggé kell alakítani a SZÖVEG függvénnyel.
=FŰZ(KÖZÉP(SZÖVEG(A1;"ééééhhnn");HOSSZ(SZÖVEG(A1;"ééééhhnn"))-SOR(INDIREKT("$A$1:$A"&HOSSZ(SZÖVEG(A1;"ééééhhnn"))))+1;1))üdv
A tanácsaimat ingyen adom. Ha nem tetszik, akkor kérlek ne kritizáld! / https://github.com/viszi/codes/tree/master/Excel
-
karlkani
aktív tag
válasz
Fferi50
#42816
üzenetére
Szia!
Az a helyzet, hogy az "id" több szóban is előfordul a keresetteken kívül, így azokat is találatnak jelzi a c oszlopban. Pontos egyezésre kellene keresni ("id").
Köszönöm!
(#42819) ny.janos
Egyelőre ez lett a megoldás."username": "Felhasználónév","id": 123456789,Abban még segíthetnél, hogyan lesz ebből a kívánt érték. Csere képlettel balról sikerül eltávolítani a felesleget, a végekről a ", és , eltávolítása nem megy. Notepad++ megoldotta, de kíváncsi vagyok.
Köszönöm!
-
Mutt
senior tag
válasz
karlkani
#42810
üzenetére
Szia,
Excel 2010-től van a Power Query ami az alábbi lépésekkel használható.
Én csináltam egy minta txt fájlt, ami így néz ki:
1. Excelben az adatok fülön található beolvasással megnyitom ezt a minta fájlt.
2. Adatok beolvasására kattintva megjelenik a fájl tartalma a Power Query szerkesztőben
3. Az első jobb sarkában található szövegszűrővel rákeresünk az id és username kezdetű sorokra.
4. Szedjük szét az oszlop tartalmát két oszlopra kettőspont elválasztó szerint.
5. A sorokhoz adjunk egy azonosító oszlopot. Oszlop hozzáadása -> Indexoszlop
6. Egy újabb azonosító oszlopot vegyünk fel, de ez ne 1-ről hanem 2-ről induljon. Oszlop hozzáadása -> Indexoszlop -> EgyéniEzzel a két utolsó lépéssel azt értük el, hogy az egymás utáni sorokban lévő felhasználónév és azonosító azonos azonosítószámot kapott.
7. Kezdőlapon a Lekérdezések egyesítése opcióval megkerestetjük az azonos azonosítószámhoz tartozó adatokat. A képernyőt igy kell ehhez kitölteni:
8. Az új oszlop jobb sarkában lévő ikonra kattintva válasszuk ki hogy a második oszlop jelenjen meg.
9. Az első oszlopban szúrjünk a username-re és végül a felesleges oszlopokat töröljük ki.
10. Töltsük vissza Excelbe a készeredményt.
üdv
A tanácsaimat ingyen adom. Ha nem tetszik, akkor kérlek ne kritizáld! / https://github.com/viszi/codes/tree/master/Excel
-
Fferi50
Topikgazda
válasz
karlkani
#42823
üzenetére
Szia!
Akkor ki kell bővíteni a keresendő szót pl. "*id" & Karakter(34) & " :*" a "*id*" helyett.
Feleslegek eltávolításához használd a HELYETTE képletet. Ez minden megadott részletet eltávolíthatsz.
Pl. =HELYETTE(A2;KARAKTER(34);"") eltávolítja az összes idézőjelet. (Fontos, hogy függvénnyel add meg az idézőjelet.)
=HELYETTE(A2;",";"") a vesszőket távolítja el.Üdv.
-
csemege86
tag
Egy már adatokkal teli lapot szeretnék olvashatóbbát tenni úgy, hogy kétféle színnel mutassa a sorokat egymás után (pl. halványkék-sötétkék kombó). Viszont ehhez semmiféle előre beépített tábla sémát vagy cellaérték alapján történő feltételes formázást nem akarok használni a már meglévő adatok mennyisége és változatossága miatt. Egyszerűen csak két színt szeretnék.
Google-on ennyit találtam: Kezdőlap - Feltételes formázás - Új szabály - A formázandó cellák kijelölése képlettel - Értékek formázása, ha ez a képlet igaz: =MOD(ROW(),2)
Az excel kiírja, hogy a képlet hibát tartalmaz, pedig több helyen is csak ezt találtam hozzá.[ Szerkesztve ]
-
Delila_1
veterán
válasz
csemege86
#42826
üzenetére
Valószínűleg magyar Exceled van. Ebben a
=PÁRATLANE(SOR())
a páratlan sorokat színezi a megadott formátumod szerint. Formázás előtt a teljes sorokat kell kijelölnöd.Programozó: hibás programok megírására és kijavítására kiképzett szakember. Többet ésszel, mint ész nélkül.
-
3DFan
aktív tag
Szeretném hivatkozással megnyitni azt a munkalapot amiből adatokat nyer a jelenleg használt. Cellán jobb egér hivatkozások opciót használva megadom az elérési utat, de ha kattintok rá akkor csak pörög a homokóra, egyéb nem történik. Mi lehet a baja? Köszönettel.
Ui.: az állomány egyébként hibátlanul megnyitható.[ Szerkesztve ]
-
-
#42832
Fire/SOUL/CD
félisten
csemege86
#42828
válasz
csemege86
#42828
üzenetére
Hali!
Ha Office 2016/2019/365 használsz, akkor jól jöhet: Excel Functions Translator
Nem csak egy adott függvény nevét képes "lefordítani", hanem összetett függvény-kifejezéseket/képleteket is, pl ilyet is, amit Mutt kolléga itt eszközölt...
[ Szerkesztve ]
Mindenki tudja, hogy bizonyos dolgokat nem lehet megvalósítani, mígnem jön valaki, aki erről nem tud, és megvalósítja. (Albert Einstein)
-
Sesy
aktív tag
Sziasztok!
A következővel szeretném kiegészíteni egy meglévő makrómat:Munka2 lapon soronként vannak adatok, ha az első oszlopban lévő kódszám első két jegye 36 akkor át szeretném másolni a Munka1 lapra egy megadott sorba, ha nem 36 akkor megyek tovább a következő sorra.
Azt meg tudom csinálni, hogy ha van adat a sor első oszlopában akkor másolja át, de sehogy sem sikerül, hogy a fenti csak a fenti feltétel esetén másoljunk...
Az első oszlopban lévő kód az minden esetben hasonló formátumú:
36-003-1.1.1.1.2-0002398Az első szám határozná meg, hogy melyik munkalapra másoljuk ki az adatokat az adathalmazból.
Nem teljesen a fenti problémához tartozik, de talán tudtok segíteni:
Építész vagyok, az adataimat archicad programból nyerem ki. Ezek lehetnek építőanyag mennyiségek, tulajdonságok, épületszerkezetek méretei stb... Ezeket az adatokat tudom xls formátumba exportálni.
Ebből az xls-ből szeretném tovább rendezni az adatokat egy másik fájlba bizonyos feltételrendszer alapján (lásd fent például). Az archicadből kapott adatok dinamikusan változnak attól függően, hogy mekkora épületről van szó és ez mennyi építőanyagot, épületszerkezetet tartalmaz.
Azt szeretném megoldani, hogy ha exportálom az adatokat xls-be akkor ezt a fájlt ne kelljen megnyitnom ahhoz, hogy az általam rendszerezésre használt xls-ben frissüljenek az adatok. Ez egyszerű függvények esetén sikerült, de ha makrót használok akkor nem megy... és amit szeretnék azt makró nélkül vagy nagyon bonyolult lenne, vagy nem lehet megoldani.
Ha meghivatkozok egy cella értéket egy másik fájlból akkor ugye ott a hivatkozási útvonal lesz ha ezt a cellát átmásolom valahová akkor is az útvonalat másolom, nem az értéket, na ezt is jó lenne megoldani valahogy, hogy az értéket másoljam át...Lehet, hogy egy kicsit össze-vissza volt akkor elnézést
Előre is köszönöm a segítséget
Born stupid... Try again!
-
karlkani
aktív tag
Szia!
Köszönöm!
(#42825) Fferi50
Szia!Így már jó, viszont a tisztított adatoknál a felhasználónév előtt ott marad a
"és a végén a",, id után pedig a,. +3-ra növelve az elejéről sikerül eltüntetni a"-t. Hossznál be lehet állítani, hogy -2 karakter legyen, illetve id-nél -1 karakter? -
Delila_1
veterán
Egy rövid makró a 36 kezdetű cellák másolásához.
Sub Masolas()
Dim sor As Long, ide As Long
Sheets("Munka2").Select
sor = 1
Do While Cells(sor, 1) <> ""
If Left(Cells(sor, 1), 2) = "36" Then
ide = Sheets("Munka1").Range("A" & Rows.Count).End(xlUp).Row + 1 '***
Sheets("Munka1").Cells(ide, 1) = Cells(sor, 1).Value
End If
sor = sor + 1
Loop
End SubA csillagokkal jelzett sor határozza meg a Munka1 lapon az első üres sort.
Sok adat esetén érdemes a makró elején kikapcsolni a képernyő frissítését –Application.ScreenUpdating = False
–, a végén meg visszaállítani.
Szerk.: azt is megteheted, hogy szűröd az oszlopot a 36-os kezdetre, majd a szűrt állományt másolod a Munka1-re.[ Szerkesztve ]
Programozó: hibás programok megírására és kijavítására kiképzett szakember. Többet ésszel, mint ész nélkül.
-
Fferi50
Topikgazda
válasz
karlkani
#42835
üzenetére
Szia!
"a tisztított adatoknál a felhasználónév előtt ott marad a"és a végén a","
Erre írtam, hogy használd a HELYETTE függvényt. A módosított képlet:=HELYETTE(KÖZÉP(D2;SZÖVEG.KERES(":";D2)+2;HOSSZ(D2));KARAKTER(34);"")
illetve:=HELYETTE(KÖZÉP(E2;SZÖVEG.KERES(":";E2)+2;HOSSZ(E2));",";"")
A KÖZÉP függvénynek meg kell adni a harmadik paramétert, hogy hány karaktert emeljen ki, de ezt ennél egyszerűbben nem lehet. Hiába írsz -1 vagy -2 -t hozzá, mert az még biztosan nem a végét fogja lehagyni, mivel a teljes hosszt számolja.Üdv.
-
karlkani
aktív tag
válasz
Fferi50
#42837
üzenetére
Szia!
Ja, így oké, azt hittem elrontottam valamit a képleteddel. Az új képlet nem jó az username esetén (ott marad a végén a
,), mert a végén nem csak a"van, hanem",.Még egy kérdés. Szöveges dokumentumba (.txt) elég hülyén mutat egymás mellett a két adat. Összefűzném őket egy cellába, de nem szóköz lenne az elválasztó, hanem 3 tab. Megoldható?
-
Fferi50
Topikgazda
válasz
karlkani
#42838
üzenetére
Szia!
Légy már egy kicsit kreatívabb....=HELYETTE(HELYETTE(KÖZÉP(D2;SZÖVEG.KERES(":";D2)+2;HOSSZ(D2));KARAKTER(34);"");",";"")
Nem tudom, jó ötlet-e a Tab elválasztás. A Tab a Karakter függvénnyel állítható elő.
Ha 3 tab-ot szeretél, akkor a Sokszor függvényt is be kell vetned.=F2 & SOKSZOR(KARAKTER(9);3) & G2
Ne lepődj meg, a tab nem látszik a cellákban.Üdv.
-
karlkani
aktív tag
válasz
Fferi50
#42839
üzenetére
Az vagyok, csak túl sok minden került a képletbe, s nem tetszett neki...
Csak azért kell a tab elválasztás, hogy átmásolva .txt fájlba átláthatóbb legyen. Szuper a képlet, de nem teljes az öröm. Másolva, majd beillesztve idézőjelek közé teszi az adatokat, ha a tab karakter benne van a cellában. Simán összefűzve, szóközzel elválasztva a két cella tartalma, majd átmásolva jó, de amint bekerül a tab karakter a cellába már nem.Ez legyen a legkevesebb, Notepad++ programban néhány kattintással megoldom.
Köszönöm szépen a segítséget!
-
Fferi50
Topikgazda
válasz
karlkani
#42840
üzenetére
Szia!
"Másolva, majd beillesztve idézőjelek közé teszi az adatokat, ha a tab karakter benne van a cellában. Simán összefűzve, szóközzel elválasztva a két cella tartalma, majd átmásolva jó, de amint bekerül a tab karakter a cellába már nem."
Nem egészen értem, nálam nincs ilyen. Másolás - irányított beillesztés értéket ugyanúgy megtartja a tabokat láthatatlanul és nincs semmi idézőjel.Üdv.
-
csemege86
tag
válasz
Fire/SOUL/CD
#42832
üzenetére
Köszi, ez hasznos!
-
#42849
Fire/SOUL/CD
félisten
Mutt
#42848
tehát csak akkor kell idézőjel, ha szövegre utalóan adok meg értéket..
Új hozzászólás Aktív témák
Állásajánlatok
Cég: PCMENTOR SZERVIZ KFT.
Város: Budapest
Cég: HC Pointer Kft.
Város: Pécs