-
Fototrend
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
-
ny.janos
tag
válasz Dilikutya #47740 üzenetére
Ha egybefüggő területet alkotnak a képletezett cellák (azaz nincs közöttük olyan, hogy 10 oszlop képleteket tartalmaz, majd 3 nem stb.), akkor a következőket tenném:
1.
Alt + .
-al átváltanám excelben a cellékat értékről képletre,
2. átmásolnám a képleteket wordbe,
3. csere funkcióval cserélném az alábbiakat,
a)^t=
csere;"")^t=
-re
b)^p
csere;"")^p
-re
c)=
csere=HAHIBA(
-re
4. wordből visszamásolnám az adatokat excelbe,
5. (a word idézőjel sajátossága miatt) bármely cellából kijelölve már excelben cserélném valamennyi cellában a”„
-t""
-re,
6.Alt+.
Remélem működik, és nem írtam el / hagytam ki semmit.
[ Szerkesztve ]
-
ny.janos
tag
válasz ny.janos #47741 üzenetére
Ha az oszlopaidra nem igazak az általam feltételezettek, akkor az én megoldási javaslatom az alábbi:
1. A táblázatod munkalapját (neve az én példámban eredeti) másold le egy új munkalapra (neve az én példámban masolat).
2. Minden adat kijelölése utánCtrl+G
, irányított, állandók-at pipálod csak be, majd delete gomb.
3.Alt+.
4. Teljes tartományt kijelölöd, Ctrl+H, az egyenlőségjeleket cseréled valami olyan karaktersorozatra (az én példámban ###, ami garantáltan nem fordul elő egyik képletedben sem.
5. Új munkalapon A1 cella képletének megadod a következőt (szintén kell a végére egy speciális karakersorozat, ami nem szerepel a képleteidben - én másikat használtam - &&&& -, mint előbb, de akár azonos is lehet):=HA(ÜRES(masolat!A1);eredeti!A1;"=HAHIBA("&HELYETTE(masolat!A1;"###";"=")&";&&&&)")
6. A teljes tartományt kijelölöd ezen a munkalapon és csere funkcióval cseréled az utóbbi karaktersorozatot - &&&& - két darab idézőjelre (így lesz a hiba esetén üres a cellád).
7. Kijelölöd a teljes tartományt, másolást követően beilleszted egy jegyzettömbbe, majd onnan kimásolva visszailleszted az eredeti munkalapodra.
8. A segéd munkalapokat törlöd.[ Szerkesztve ]
-
ny.janos
tag
válasz Brandynew #48715 üzenetére
Van itt egy kis tévedés a részedről, de a függvény súgó egyértelmű:
"A függvény a kezdő_periódus és a vég_periódus között egy kölcsönre visszafizetett összes kamat halmozott értékét adja meg."
Ha te ezzel szemben a havonta fizetett kamat mértékét szeretnéd megtudni, akkor a halmozott kamatból ki kell vonnod az eggyel korábbi hónap halmozott kamat értékét.
Ahelyett, hogy részletekbe mennék, megosztom egy korábbi táblázatomat, amit tetszőlegesen átalakítasz majd a saját szád íze szerint. A működést viszont meg fogod érteni.
[link] -
ny.janos
tag
válasz jerry311 #48728 üzenetére
Egy gondolatébresztő a korábban felvetett Power Query megoldáshoz: Ha az összes csv fájlt beolvasod mintából és a fájloknak a nevében szerepel a dátum, akkor a fájlnév részének kinyerésével és dátummá alakításával lesz egy adathalmazod, melyben szerepel a Name, ID, Status adatok mellett a dátum is. Az ID és a dátum oszlop összevonásával készíthetsz egy új oszlopot. Ezután a státuszt meg tudod keresni a VLOOKUP-al a PQ által előállított adathalmazban, ha az ID cella és fejlécként szereplő dátum cella összevont adatára keresel.
Ha az egyes csv fájlok nem tartalmaznak több 10e sort így a több, mint egymillió soros korlátot várhatóan nem léped túl, akkor nem is foglalkoznék havonta külön munkalappal, hanem az évet és a hónapot kiemelném egy-egy cellába a munkalap tetején, és annak felhasználásával képezném a fejlécben a dátumot. Így ha változtatod az évet és a hónapot, akkor mindig az aktuális értéket fogja dátumnak megfelelően kiolvasni a VLOOKUP a PQ által beolvasott csv fájlok összességéből. -
ny.janos
tag
válasz BigBadPlaYeR #48748 üzenetére
Szia!
Feltételezéssel élek, ha ez nem igaz, akkor a javasolt megoldás feltételezhetően nem használható a számodra:
Ha a kérdésed oka az, hogy aki a táblázatba adatokat rögzít, az rendszeresen elfelejti valamely hozzá tartozó további adatok töltését, akkor adatérvényesítésben próbálkozhatsz képlettel lekorlátozni az adatbevitelt. Ha arra a cellára teszed az adatérvényesítést, melyet mindenképpen tölteni kell annak, aki az adatot rögzíti, akkor csak azt követően fogja tudni azt kitölteni, ha a többit már kitöltötte.
A képletben az ÉS és a DARAB2 függvények kombinációját próbálnám a helyedben (az ÜRES függvény a NEM függvénnyel kombinálva érdekes módon nem működik az adatérvényesítésben).[ Szerkesztve ]
-
ny.janos
tag
válasz BigBadPlaYeR #48759 üzenetére
Akkor lehet, hogy valamiért az én excel verzióm nem szereti, mert nálam ez nem működött, de örülök, hogy nálad igen.
Ha több cella kitöltését szeretnéd, akkor használd az előbbi függvényt egy ÉS függvénybe ágyazva, pl:=ÉS(NEM(ÜRES(A1));NEM(ÜRES(B1));NEM(ÜRES(C1));NEM(ÜRES(D1)))
-
ny.janos
tag
válasz Melorin #48882 üzenetére
Ahogy a táblázatodat nézem, a központi adatbázisból nyert árakhoz tartozó érvényeségi dátum kezdete és vége könnyedén kinyerhető PowerQuery-vel. Ha ez megvan, akkor az alapján meg tudod keresni az adott termék adott napon érvényes árát nem kell hozzá semmilyen átalakítás. Ezeket a videókat nézd meg és válassz olyan megoldást, ami neked megfelelő.
[link] [link] [link] [link] [link] -
ny.janos
tag
válasz MasterDeeJay #49507 üzenetére
Szia!
Konkrét megoldást nem fogok tudni javasolni, mert ilyen szinten nem értek a PowerQuery-hez, de biztos vagyok benne, hogy mivel az ismétlődés 15 oszloponként van, azzal meg lehet oldani, hogy összesen 16 oszlopba kerüljön az összes adatod, ahol az első oszlopban ismétlődnek a rendszámok.
Talán Mutt tud neked ebben segíteni, ő kimondottan profi a PQ-ben. -
ny.janos
tag
válasz MasterDeeJay #49512 üzenetére
Ha minden rendszám csak 1 sorban szerepel, ahogy írod, akkor a rendszámok A oszlopban történő felsorolásával és hozzá minden lehetséges dátum kombináció megadásával kezdenék a helyedben. Mutt pár hozzászólással feljebb részletezte, hogy miként lehet PQ-vel egyszerűen valamennyi rendszámhoz egy intervallumban szereplő valamennyi dátumot hozzárendelni külön-külön sorban.
Ha ezzel megvagy, akkor az oszlopazonosítókat kell felvinned ebbe a táblázatba a, majd az index és hol.van fügvényekkel kombinálnod.Töltöttem fel egy mintafájlt, ezen egyszerűbb megérteni, mint ha képernyőfotót tennék fel.
Arra figyelj mindenképpen, hogy ha a táblázat második sorában lenne olyan szám, mely megegyezik valamelyik dátummal, akkor ez a javasolt megoldás hibát fog okozni!
[ Szerkesztve ]
-
ny.janos
tag
válasz szricsi_0917 #49518 üzenetére
Szia!
Ha az oszlopaidból igen sok van és nem csak 2-szer ismétlődik, hanem esetleg több 10-szer is, akkor érdemes lehet elgondolkodni egy segédmunkalapban szerintem. Itt megnézheted, mire gondolok.
-
ny.janos
tag
válasz Czmorek #50031 üzenetére
Szia!
Egyéni számformátummal is megoldhatod a nullák elrejtését, HA függvény sem kell hozzá.
-
ny.janos
tag
Szia!
Akkor kezdem előröl az előbbi elveszett hozzászólás után.
A dátum átalakításához (a kérdésed alapján végzett) kísérletezgetésem alapján megfelelő megoldás lehet a szövegből oszlopok átalakítás, ha az utolsó lépésben megadod a dátum formátumát. Ha a dátumaid több formátumban vannak (ahogy írtad), akkor többször egymás után kell az átalakítást elvégezned. Az átalakítás a dátum formátumú cellákat nem fogja elrontani, a nem dátum formátumúakat ellenben megfelelő formátumra fogja alakítani. Az egyetlen (általam azonosított) probléma, ha a dátumok között NHÉ és HNÉ formátum egyaránt szerepel. Ez esetben összetettebb megoldást kellene keresni. Az átalakítás mikéntjéről Horváth Imre ebben a bejegyzésben írt részletesen.
Alkalmas lehet az átalakításra a Power Query is, de míg a szövegből oszlopok átalakítás a 202212.10 hibás formátummal is megbirkózik, addig előbbivel ezt nekem nem sikerült kezelnem (biztos lehet, csak az általam nem ismert megoldást igényelne).A megelőző hozzászólásod kapcsán (főként az alapján, hogy a bemeneti adataid több fájl több munkalapján találhatóak, de a struktúra azonos) szintén a Power Query alkalmazását javasom. A betöltés történhet akár az általad készített fájlba (ez esetben nem kell a bemeneti adatokat tartalmazó fájlokat megnyitnod) és azt is be tudod állatíni, hogy az adatfrissítés megtörténjen automatikusan, mikor a fájlodat megnyitod. A megoldásról itt találsz egy részletes, könnyen érthető, képernyőképekkel illusztrált leírást.
Sok sikert!
[ Szerkesztve ]
-
ny.janos
tag
Sziasztok!
Igazából nem konkrét feladathoz kapcsolódik, csak a kíváncsiságom nem hagy nyugodni: ismer valaki egyszerű módot arra, hogy Power Query-vel egy adott dátumhoz tartozó hét számát az ISO 8601 szabvány szerint határozzuk meg?
Excelben a HÉT.SZÁMA függvény második paraméterének megfelelő megválasztásával (21) simán működik, ugyanakkor Power Query-ben a Date.WeekOfYear függvény csak a január 1-t tartalmazó hét első hétként történő értelmezését ismeri.
[ Szerkesztve ]
-
ny.janos
tag
válasz Krant.ia #50316 üzenetére
Szia!
Használhatod az adatok beolvasása és átalakítása menüpontot (korábbi verziókban Power Query kiegészítő) is.
Oszlop hozzáadása példákból menüpontot válaszd. Az első kettő értéket gépeld be zárójel és szóköz nélkül, amilyen eredményt kapni szeretnél. Ebből automatikusan felismerésre kerül, hogy a szóközöket és a zárójeleket kell eltávolítani és a képletet megírja helyetted a program. Betöltés után meg is vannak a kívánt adataid. -
ny.janos
tag
válasz Fferi50 #50410 üzenetére
A DARABHATÖBB függvény alkalmazása remek megoldás (nekem magamtól nem jutott volna eszembe, a DARABTELI-vel próbálkoztam volna, de mint rájöttem az nem alkalmas arra, hogy
DARABTELI($A$2:$A$7&$B$2:$B$7;A2&B2)
formában használjam).
Ha megengeded kiegészíteném egy ötlettel, mely esetén nem kell a második segédoszlop:=1/DARABHATÖBB($A$2:$A$7;A2;$B$2:$B$7;B2)
Ez esetben értelemszerűen az összeget kell használni a kimutatásban a maximum helyett.[ Szerkesztve ]
-
ny.janos
tag
válasz pero19910606 #50485 üzenetére
Szia!
Wikipediáról az automárkák letölthetőek PQ-vel, vagy akár másolhatóak is (bár PQ esetén a megfelelő stuktúra biztosabb.
Ha megvannak az autómárkáid (az én példámban B2:B369 tartomány), és H oszlopban vannak az általad írt adatok, akkor
I2 cella képlete:=BAL(Munka1!$H2;SZÖVEG.KERES(" ";Munka1!$H2)-1)
J2 cella képlete:=INDEX($B$2:$B$369;HOL.VAN("*"&Munka1!$I2&"*";$B$2:$B$369;0))
I oszlop egy segédoszlop, a várt eredmény a J oszlopban található. (Ha lenne olyan automárka, melynek az első tagja megegyezik egy másik márka első tagjával, akkor lesznek benne hibák.)[ Szerkesztve ]
-
ny.janos
tag
Szia!
Tömbképlettel pl. szorzatösszeg függvénnyel, de nem bonyolítanám ezzel. Inkább szumha függvényt használnék, vagy kimutatást. De PowerQuery-vel is megoldható.
#Szerk: közben rájöttem, hogy félreértelmeztem, hogy mit szeretnél. De PQ az általad kértekre is megfelelő lehet.
[ Szerkesztve ]
-
ny.janos
tag
válasz pero19910606 #50485 üzenetére
-
ny.janos
tag
Legfeljebb még nem, mert eddig nem használtad. Ha több problémát is sikerül megoldani vele (SZORZATÖSSZEG), akkor a megoldások keresése során elkezd vele egyre gyakrabban szemezgetni az ember (én legalábbis így vagyok vele).
Ha van kedved hozzá, akkor rákereshetsz a fórumban a függvény nevére, érdekes megoldásokra lehet bukkanni a használatával. -
ny.janos
tag
Szia!
Az ellenőrzés munkafázis egy sorban egyszer fordulhat elő, vagy többször? Függvénnyel arra lehet megoldást találni, ha csak egyszer szerepel, mert ez esetben ismert, hogy meddig (következő | jel) kell a nevet keresni. Ha viszont többször is lehet ellenőrzés munkafázis cellán belül, akkor úgy gondolom, hogy mindenképpen darabolni kellene a cellákat (kivéve persze a makrós megoldást - amihez én nem értek).
Jól értem, hogy adott személyhez az összes sort szeretnéd vizsgálni egyszerre, hogy melyekben szerepel az ellenőrzés munkafázisnál az ő neve és ezek darabszámára vagy kíváncsi az összes sorból? Ha igen, akkor Power Query irányába is el lehet indulni szerintem. -
ny.janos
tag
Még egy kérdés: az
[időbélyeg] - [munkafázis] - [név] - [pozició]
struktúra minden eleme mindig megjelenik a cellában, vagy előfordulhat olyan, hogy valamelyik adat hiányzik?
Azaz lehet olyan, hogy a következő időbélyeg előtt ennyi áll?[időbélyeg] - [név] - [pozició]
Ha utóbbi előfordulhat, akkor munkafázis pozíció teljesen hiányzik, vagy a szögletes zárójel megvan, de nem tartalmaz adatot? -
ny.janos
tag
válasz TillaT #50532 üzenetére
Ha a feltételes formázásban írod meg a képletet és kijelöléssel hivatkozol a cellára, akkor a DÁTUM függvény 3. paramétere abszolút hivatkozás ($A$5) lesz. Azt kézzel át kell írni vegyes hivatkozásra, hogy mindig az adott sort vizsgálja, de minden esetben az A oszlopot.
-
ny.janos
tag
Kijelölöd az adataidat, majd az adatok menü adatok beolvasását táblázatból vagy tartományból pontot választod (PQ). PQ-ben a kezdőlapon az oszlop felosztását választod
A betöltés után a nevekből és a munkafázisból csinálsz egy táblázatot, majd aSZORZATÖSSZEG((SZÁM(SZÖVEG.KERES([NÉV];[PQ által létrehozott tartomány]))*(SZÁM(SZÖVEG.KERES([munkafázis];[PQ által létrehozott tartomány])))))
függvénnyel megkeresed mi mennyiszer fordul elő (ha egy sorban ugyanaz a név ugyanahhoz a munkafázishoz többször szerepelne, akkor azt duplán fogja számolni az összesítésben).A PQ betöltés munkalapját elrejted, ha neked szeretnéd látni.
[ Szerkesztve ]
-
ny.janos
tag
Szerintem nem egyedi esetről van szó. Soha nem próbáltam még így kijelölni, de most megnéztem és Win 10, 2019 Office esetén sem működik. Szerintem máshol sem fog.
Javaslom, hogy tanulj meg billentyűkombinációkat (Ctrl, Shift, A, Space, "kurzormozgató nyilak", End, Home,Page Up, Page Down) használni a kijelöléshez az egér helyett, hidd el sokban meg fogod könnyíteni a saját munkádat.
Itt találsz egy csomó billentyűkobinációt összeszedve egy csokorba, köztük a kijelölésre vonatkozóakat is. -
ny.janos
tag
Persze a makrós megoldás is tetszés szerint bővíthető, de azért is tettem ki végül a megoldásomat, mert azzal akár az összes dolgozó, összes munkafázisban történő részvétele összeszámolható egyetlen függvénnyel megfelelő előkészítés mellett.
Ha megengeded, hogy őszinte legyek, elsőre úgy gondoltam közzé sem teszem a megoldást, mert a - nem szándékosan, hanem rosszul megfogalmazott - struktúra miatt úgy éreztem, hogy feleslegesen akarok segíteni és nem hiányzik nekem, hogy tovább gondolkodjak a megoldáson, mert majd újra kiderül valami, ami nem úgy van, ahogy elsőre tűnt. Aztán meggondoltam magam, mert bármelyikünk hibázhat és a megoldások sokszínűsége segíthet egy későbbi probléma megoldásában másnak is, illetve én azokból tanultam sok mindent, amit itt a fórumon olvastam.A PQ-t mindig azért tartom nagyszerű megoldásnak, mert olyan dolgokat lehet vele viszonylag könnyedén megoldani sok esetben, melyhez vagy nagyon bonyolult, sokszorosan egybeágyazott függvények kellenének, vagy makrót kellene segítségül hívni. Mivel én utóbbihoz még annyi ismerettel sem rendelkezem, hogy egy kapott kódot hova kellene másolni (azt tudom, hogy az összefoglalóban meg tudnám nézni, de magamtól tényleg nem tudom), ezért mindig örülök, ha létezik programozási ismeret nélküli megoldás a PQ segítségével.
A te esetedre visszatérve: ha az általam javasolt megoldást úgy módosítod, hogy PQ-ben a beolvasás után felveszel egy indexoszlopot, majd a darabolást követően szűrsz azokra a sorokra, amelyek tartalmazzák az ellenőrzés kódot, akkor betöltés után ebből a táblából az index sorszám alapján az eredeti adataid mellé egy külön oszlopba ugyanúgy hozzá tudod olvasni a kinyert részletét az adatodnak (a módszert - PQ, index-hol.van, fkeres-sor függvények pedig tetszőlegesen megválaszthatod)
-
ny.janos
tag
Kicsit nyakatekert, de működő megoldás lehet, hogy a HAHIBA függvényedben hiba esetére nem üres eredményt ("") kérsz, hanem 0 értéket (feltételezem, hogy mivel szöveges eredményt kapsz egyébként a leírásod alapján, így 0 eredményed csak hiba esetén lesz).
Ezt követően kettő részösszeg (vagy összesít függvény) eredményét kombinálod. Előbb darab2-vel (103) összeszámolod az összes sorodat és ebből kivonod darab-al (102) a 0-k mennyiségét. Ha az oszlopban nem szeretnéd látni a nullákat, akkor egyéni cellaformátummal eltünteted a 0 értékek megjelenítését.[ Szerkesztve ]
-
ny.janos
tag
Szia!
Makróval sok minden lehetséges, így értelemszerűen erre is tudnak biztosan megoldást írni azok, akik értenek a mekróhoz.
Ugyanakkor sokszor elhangzott már itt a fórumban, hogy a színnel történő kódolás nem egy excel logikára felépített megoldás, ezért célszerű az ilyen megoldásokat kerülni. Ugyanis ha a színezés alapján lenne szükséged valamilyen műveletre, akkor az csak autoszűrővel és részösszeg/összesít függvényekkel illetve makróval oldható csak meg. Több kategóriából adatokat nyerni pedig értelemszerűen csak makróval lehet, vagy sokszor kell az autoszűrőn módosítani.Fentiek miatt célszerű megoldás lehet, hogy az eddigi táblázataidat átalakítod oly módon, hogy hozzáadsz egy új oszlopot a tábládhoz és a valamilyen kódokat (számok, betűk, konkrét szövegek stb.) rendelsz mind egyes színhez. Ha a színezést továbbra is szeretnéd látni, akkor feltételes formázásban rendeld a színezést az új oszlopként hozzáadott kódokhoz. Ezt követően a manuális színezés eltávolítható.
A fájlaid adatainak összefűzésére pedig az Adatok - adatok beolvasása - fájlból - mappából menüpontot (korábbi excel verziókban Power Query kiegészítőként volt telepíthető) javaslom. Ezzel az összes fájlod adataiból egy összesített adattáblát fogsz kapni (a feltételes formázást ebben is megadhatod).
-
ny.janos
tag
válasz picur10 #50808 üzenetére
Szia!
Én más struktúrában gondolkodnék és akkor a megoldás is egyszerűbbé válik (a megoldásomban a pivot tábla adja a lényeget, illetve a bevétel/kiadás előjelhelyes összeggé alakítása).
A fájlt itt találod. -
ny.janos
tag
Sziasztok!
2019-es excelben adott egy PowerQuery-vel beolvasott táblázat, amelynek a formátumát egyéni táblázatstílussal formáztam, az oszlopok szélességét az általam kívántak szerint beállítottam.
Azonban amint frissítésre kerülnek az adatok, a táblázatstípus megváltozik, az öszlopszélességek módosulnak.
Zárolt cellákon a lapvédelmet próbáltam, de akkor a frissítés sem futtatható.A kérdésem: ismertek módszert arra, hogy a PQ frissítés a beállított formátumot ne változtassa meg?
-
ny.janos
tag
Sziasztok!
Milyen formátumkódot kell megadnom ahhoz, hogy ha egy szám esetében azt szeretném, hogy legfeljebb 2 tizedes jelenjen meg, de ha nincs tizedes, akkor az egész számot lássam csak? Gondolom megoldható, csak én nem jövök rá.
Ha a 0,## formátumot használom, akkor a tizedesvessző megjelenik akkor is, ha egész a szám, aminek értelemszerűen nem kellene. Hogy tudom opcionálissá tenni a tizedesvesszőt is?Köszi.
-
ny.janos
tag
válasz dellfanboy #51835 üzenetére
Nincsenek egynél kisebb értékeim, így csak az első felére lenne szükségem kettő tizedessel, de a
#,#0
formátummal mindenképpen van legalább 1 tizedes ott is, ahol egész az érték, sőt az egy tizedes helyett pedig megjelenik még egy 0 a végén eképpen:227,20
Ha csak#,##
-t használok, akkor pedig ez lesz az eredmény pl.240,
. Ugyanakkor a vessző már nem kellene.[ Szerkesztve ]
-
ny.janos
tag
válasz Fferi50 #51837 üzenetére
Zseniális ötlet, köszönöm szépen! (Bár sokszor használom a feltételes formázást, itt és most valamiért nem jutott eszembe ezt használni.)
Mindenesetre elég gáz, hogy úgy tűnik ezek szerint, hogy nincs egyszerűen használható beépített formátumkód, ami ezt egy lépésben megcsinálná.
Új hozzászólás Aktív témák
Állásajánlatok
Cég: Promenade Publishing House Kft.
Város: Budapest
Cég: Alpha Laptopszerviz Kft.
Város: Pécs