Hozzászólások
-
SzerzőBejegyzés
-
Na pont ezt akartam kérdezni, hogy ha csak ezt akarod, akkor csak írj rá egy egyszerű hivatkozást. Pl.:
=A16De akkor ezzel meg is vagy 🙂
Szia!
Elsőre ugyanazokat találtam, amiket már próbáltál és nem működött.
Aztán találtam egy VBA parancsos megoldást.1. Inítsd el az Excel-t
2. ALT-F11 -> Megnyitja a makró szerkesztőt (Laptopon lehet, hogy Fn-Alt-F11
3. Ctrl-G -> Megnyitja a VBA parancs ablakot (Immediate)
4. Gépeld be ezt vagy csak másold oda innen:Application.Speech.SpeakCellOnEnter = False5. A parancs végén nyomj egy ENTER-t!
6. Lépj vissza Excelbe és próbáld ki!Nagyon kíváncsi vagyok!
Imre
Szia!
Mivel nem konkrétan kérdezel, csak találgatni tudok.
Az irányított kijelölés során nem tudsz válogatni, a megadott választás alapján minden megfelelő cellát kijelöl.
Ezek után, ha nyomod a CTRL-t, akkor tudsz kattintgatni a cellákon, hogy melyik nem kell.Viszonylag kevés cella esetén kis területen ez jó lehet.
Sok cella esetén ez lassú vagy reménytelen.Imre
Sziasztok!
Delila, Verax, nagyon köszi az együttműködést!
Arra figyeljetek, hogy ha kódot (képletet) tesztek ki, akkor kijelölés után, ha megnyomjátok a CODE gombot fent, akkor formázatlan szövegbe teszi a képletet, ahonnan gond nélkül fog működni, ha kimásolják, az idézőjeleket nem cseréli nyomdaira. (Kivéve az angol-magyar függvénynevek és vessző-pontosvessző ügy…
És akkor ehelyett:
=HOSSZ(HELYETTE(A1;”,”;””))Így néz ki:
=HOSSZ(HELYETTE(A1;",";""))Köszi,
Imre
Hali!
Szerintem sikerült egy formulás megoldást összeraknom.
Az viszont fontos, hogy valahogy jelezni kell, hogy egy mondat hány darabból áll.
Ezt én egy külön oszlopba tettem (B oszlop), ahol a kezdő darabhoz beírtam, hogy hány darabból áll a mondat. A második és harmadik darabokat üresen hagytam. Ha csak egy darabból áll, az 1-et akkor is beírtam.
A C oszlopba írtam a képletet, ami a darabszámtól függő méretű tartományt összefűz egy szóközzel (max 3 jelenleg) akkor, ha az előtte lévő cellában egy szám van, különben üres (dupla macskaköröm)Ha jók az eredmények, akkor
1. mentsd el másként
2. A C oszlopban a képleteket alakítsd értékké
3. A B vagy C oszlopban szűrj az üresekre
4. Töröld ki a kapott sorokat (eredeti második és harmadik darabok
5. Törölheted az A és B oszlopokat.Imre
Attachments:
You must be logged in to view attached files.Örülünk! 🙂
Na megnéztem.
Nagyjából az van, amit gondoltam.
Ebből a formátumból elég nehéz szűrt összesítést csinálni.
Ezt a formátumot nevezik PIVOT formátumnak, amikor az elején van 1-pár oszlop, majd jönnek a fejlécben tételek, jelen esetben hónapnevek. Olyan, mint egy PIVOT tábla, aminek az OSZLOP részébe behúzták a Hónap tulajdonságot.
Írásban eléggé hosszadalmasan tudnám csak elmagyarázni a dolgot, de ezt a bizonyos UNPIVOT lépést mindenképpen bele kell majd tenni a Query-ben, hogy az évek ne vízszintesen, hanem függőlegesen legyenek.Megpróbálom leírni a T516-os lekérdezésre
———————————————-
1. visszamész a lekérdezés szerkesztőbe.
2. A végén, a további lekérdezések között ott van a T516, ami az összefűzött állapotot mutatja.
3. Menj az utolsó lépésére
4. Távolítsd el a December utáni 2 oszlopot (Összesen és Mértékegység)
5. Jelöld ki a Január előtti oszlopokat
6. Jobb klikk bármelyik fejlécen -> Többi oszlop elemi értékekre bontása
7. A hónapok és az értékek oszlopba kerültek, az első 4 oszlop sorai pedig januártól decemberig mwgismétlődnek annyiszor, ahány kombinácójuk van. Az értékek nem változnak.
8. Akár már így is betöltheted. Ha akarod, akkor előbb rászűrsz a 104-es típusú sorokraEgy olyasmi formátumot kapsz, amivel a Munka2-n próbálkoztál.
Ezt aztán akár szeletelőkkel szűrögetheted évre, hónapra, településre, sorszámra (ha nem szűrted le csak a 104-eseket)
Készíthetsz belőle diagramot.A többszörösen egymásba ágyazott XKERES formulánál nem nagyon értem, hogy mi volt a koncepciód, de az úgy nem jó, de ha az előző lépést (UNPIVOT)megcsinálod, akkor nem lesz rá szükség abban a formában, és elég lesz a SZUHATÖBB vagy egy PIVOT tábla.
Erre gondoltál?
Imre
Szia!
Minta adat nélkül nem tudom lekövetni a dolgot.
Az elejét kb. értem, bár már ott is kérdeznék.Ha jól értem, akkor mappából történő összefűzést csinálsz, azonos szerkezet Excel munkafüzetekből.
Az összefűzés után benne van az egyes fájok neve is egy oszlopban, amit két külön oszlopba teszel (Cég és Év)
Az összefűzés eredménye egy táblázat, amit szeletelővel szeretnél szűrni cégre és évre.Kérdezem, hogy az összefűzés sikeres? Azt gondolom, hogy igen.
Azt is gondolom, hogy nem kimutatást akarsz csinálni, hanem valamilyen diagramot, mivel x meg y tengelyről beszélsz.
Milyen adatokat akarsz ábrázolni?
Évre és hónapra összesített adatokat? mert én ezt tippelem. Ehhez egy PIVOT tábla kellene, de előtte az összefűzött adatokat UNPIVOT-álni kellene, mert ebben a formátumban, amit leírtál ez nem valósítható meg.A második problémát meg pláne nem tudom minta adat nélkül véleményezni.
Esetleg azt megkérdezném, hogy a hol.van függgvénnyel pontos egyezőségre keresel-e, és így az utolsó paramétere nulla-e?
Ezt az xkeres/szumhatöbb , illetve hol.van hibát látni kellene.Imre
Azért az már eléggé nagy varázslat lenne, szerintem ezt te is gondolhatod.
Honnan tudná az Excel, hogy egy adat az vezetéknév-e vagy sem?
Már a helyesírás ellenőrző is elég nagy dolog, hogy a gyakoribb keresztnevek elírásait felismeri esetleg.A vezetéknév-keresztnév felcserélés felismeréséhet ismerni kellen az „összes” vezetéknevet és az „összes” keresztnevet.
Ha meg tudod szerezni a magyar keresztnevek listáját, akkor meg lehet nézni, hogy a vezetéknév oszlopban ezek közül bármelyik szerepel-e. Persze itt újabb nehezítés megint az elírások, ékezetek, stb…
Azt nem tudom, hogy létezik-e vezetéknevek listája…Szerintem ezt egyelőre még az AI sem ugorná meg, bár nem tudom….
Megkérdeztem tőle, de pont azt válaszolta, amit én is gondoltam.I
Szia!
Helyesírás ellenőrzőt próbáltad?
F7-re indul az Excelben is.
A „Janos”-t és „Bela”-t kipróbáltam, kipróbáltam, ad rá javaslatot, ami ékezetes.
???Imre
Szia!
Az Excelben jelenleg nem lehet csak a szerkesztőléc betűtípusát változtatni.
Meg lehetne csinálni, a PowerBI-ban pl. sima Ctrl+egérgörgővel működik a dolog.Egy félmegoldás van, de azt én sem választanám, miszerint a teljes Excel betűméretet meg lehet változtatni.
Ezt a Fájl-> beállítások -> Általános részben találod.Imre
Szia!
Sajnos, ha a mukafüzet bármely lapján van egy Táblázattá alakított tartomány, akkor elszürkül az egyéni nézet.
Akkor lehet gyors megoldás, ha az elrejtendő segédoszlopok összefüggően, egymás mellett vannak.Ebben az esetben össze lehet őket csoportosítani és +/- jeleken kattintva ki-be csukni.
Adatok -> csoportosításImre
Szia!
Amíg feltöltöd a mintát, írnék pár gondolatot.
Már sokadszorra találkozom azzal. hogy munkaidőt akar valaki összesíteni, kivonni, stb.. Excelben
Az szokott lenni a probléma, hogy a dátumokat és időket annak gondolják, amit a cellában látnak (pl: 13:45)Nálad is hasonló lesz a probléma.
Az időértékek esetén mégnagyobb a homály.
Az időértékek valójában tört számok a napon belül. Ezt te leírod, hogy tudod, de nem alkalmazod.Először, hogy miért kaptál 7:23 percet a különbségre. Gondolom az ÓRA és a PERC függvényekkel próbálkoztál. Ez megint formátum probléma.
A különbség ugye több, mint 24 óra, azaz több, mint 1 nap. A 24 óra után elölről kezdődik az órák számolása, ugye ezt tudjuk.
A 31:23 az vallójában 24+7 óra és még 23 perc. Az egész napokat nem mutatja, csak a 24 óra többszöröse feletti időt, azaz a 7-et.
Ezt még megformázhatnád idő számformátummal, hogy ne csak a 24-óránál nagyobb időket mutasson.
Ennek a számformátuma magyar Excelben: [ó]:pp:mm
De ettől a cellában lévő szám nem változik meg.Ha minden cellát számmá alakítasz, és megnézed a tört számokat az egész mögött, akkor a matek jó lesz, csak a formátum nem stimmel. Ráadásul, ha a túlórát még ki is akarod fizettetni, akkor ugye 31 óra meg még egy kicsi lenne a fizetés alapja.
Ugyanez lehet a gond a 40 órás alap munkaidővel, mert 40:00 van a cellában, de ha számmá alakítod, akkor meg fogsz lepődni
a 1,66666666666667 eredményen. Igen, mert 40 óra az valójában 1 nap és még kb. 6 tized nap.Szumma szummárum, ha Excel időértékekből valódi időértékeket akarsz gyártani, akkor meg kell szorozni 24-el.
Excelben két idő különbségét igazi órára konvertálni tehát:
=(nagyobb idő – kisebb idő)*24Ha dátumra formázza a képlet eredményét, akkor formázd vissza számmá.
Tört számokat fogsz kapni, de az igazi órának ill. tört órának a számát.
A példádnál maradva Excel idő formátumban
(71:23 – 40:00) * 24 = 753:12Mindezeket számmá formázva
(2,97 – 1,66) * 24 = 31,38Az eredmény a lényeg, tehát 31 egész óra és még valamennyi tört óra. Ezt lehet szorozni a túlóra óradíjával vagy bármit csinálni vele.
Imre
Nézd. Ezt csak hályogkovácsolni lehet ebben a formában.
Nézzünk egy barkácsolást:
1. Mi lenne, ha a hibaüzeneteket úgy írnád be a kis táblák aljára, ahogy tanácsoltam, de a „Hiba:” szöveggel kezdődne.
2. A J és a fene tudja meddig terjedő cellákra (képletekre) tennél feltételes formázást, ami akkor lesz színes, ha a kimenete tartalmazza a Hiba szót. Ez már egy vizuális figyelmeztetés
3. Az F oszlopba a hibajelzésekhez (ha ennyire ragaszkodsz hozzá), akkor lehetne egy képlet, ami megnézi, hogy a J-akármeddig cellák között hány van, ami a Hiba szót tartalmazza. ha ez nagyobb, mint 0, akkor valahol hiba van, és te görgetheted a képernyőt, és keresheted a színes cellákat a hibaüzenettel.Nos, a 3. lépés formulája nem annyira egyszerű, mert az egy tömbképlet. Egy olyat raktam össze, ami 365 és korábbi Excel verziókban is menni fog. A tábla Hibajelzések oszlopába (F8) ezt a képletet írtam:
=SZORZATÖSSZEG(--SZÁM(SZÖVEG.KERES("Hiba";tbl_red_számol[@[Tok]:[m2]])))Ez most a Tok és a m2 oszlopok között kézi az aktuális sorban.
Működés nagyjából:
1. Megnézi, hogy a megadott tartományban bármely cella tartalmazza-e a „Hiba” szót, azaz a Szöveg.Keres fv számot ad-e vissza
Ez egy logikai tömb lesz IGAZ/HAMIS/HAMIS/IGAZ jellegű tömböt ad vissza
2. A dupla mínusz csak annyit tesz, hogy a HAMIS 0 lesz, az IGAZ pedig 1 -> 0/1/1/0
3 Végül ezt összegezve (az 1-eseket összeadva) megkapjuk, hogy hány cellában van benne az, hogy „Hiba”Azért SZORZATÖSSZEG és nem SZUM, mert régebbi Excelekben is működni fog így, sima ENTER-rel lezárva.
Imre
Hali!
Ez megint adatszervezési probléma.
Ha jól értem, akkor a redőnyszámolóban a minden sorban a J és M oszlopok bármelyikében lehet hiba.
Az Excel kereső függvényei az első találatot tudják csak visszaadni, tehát ezzel a koncepcióval nem nagyon fogysz célt érni.De lenne egy javaslatom.
A kis táblák utolsó soraiba, ahová a Hiba1, hiba2… szövegeket írtad, miért nem írod rögtön a megfelelő hibaüzenetet?
Ygy a J…M oszlopok képletei hiba esetén a hibaüzenetet írnák rögtön ki.Addig módosítja emberünk az input cellákat, míg ott nem lesz hiba.
Tehát nem kell ez a két lépcsős hibakeresés.
Mit gondolsz?
Imre
Hajrá!
Köszi, hog reagáltál!
🙂
Szia!
Többször futottam neki, de most szántam rá egy kis időt.
Gyönyörű formulát alkottál a T3-ban. 🙂De jól sejted, hogy ez nem az igazi. A probléma az, hogy a termékeket külön táblácskákban próbálod tárolni, ezért szembesülsz azzal, hogy a HA függvénnyel meg kel állapítanod, hogy melyikben kell keresni. Ez egy tervezési probléma.
Még időben vagy, de szerintem semmiképpen ne így csináld.A termékeket egy táblában kell tárolni minden tulajdonságukat egy oszlopba felvenni s ebben kell keresni.
A többi részhez én most nem szólok hozzá, de az is jól látod, hogy erre a feladatra nem igazán az Excel lenne a megfelelő megoldás.
De meg lehet csinálni, de adatbázis elvek mentén kell gondolkodnod, nem megjelenítés szintem.Értem én, hogy jó lenne látni a redőnyöket, meg a szúnyoghálókat, meg a… külön táblácskákban, de ez adatfeldolgozás szempontjából über szívás.
Szóval a termék törzs tábla egyben kell legyen és a fejléce valami ilyesmi:
Cikkszám, Megnevezés, szín, stb… , egyéb tulajdonságokAkár csak egy tulajdonságban eltérő termékek külön sorba mennek, különböző cikkszámmal.
Ez még csak a termék törzs.
Aztán az árakat is külön lenne jó tárolni az egyes választható dolgokhoz, mert az ajánlati ár több dologból áll össze.
Alap ár + szín felár +ez meg az…
Ezt most kapásból nem tudom, hogy hog csinálnám így Excelben, de esetleg a termék törzshöz hasonlóan lehetne egy árak tábla külön
Fejléce:
Cikkszám, opció, árEgy cikkszám annyiszor ismétlődően fel van sorolva ebben a táblában, ahány opció választható hozzá. Pl:
10020002, alap ár, 20.000
10020002, szín, 12.000
10020002, osztás, 5.200Ebben a táblában nem lehet FKERESSEL csak a cikkszám alapján keresni, mert ugye az ismétlődik, de ha összekombinálod (összefűzés) a cikkszámot és az opciót, akkor az már talán egyedi lesz.
És ezután az ajánlat összerakása során legördülőkben lehet választani az alap terméket és a plusz opciókat, és kikeresni a választáshoz tartozó tulajdonságokat.
És akkor még nem beszéltünk az árváltozásokról, amit nagyon alap szinten csak úgy lehet lekezelni, hogy az ajánlat összerakása után a képleteket értékké kell alakítani, hogy egy elkészült árajánlat nem tudjon megváltozni visszamenőleg, mert mondjuk augusztusban az egyik opciónak megváltoztattad az árát.
Szóval egy ilyet elég nagy kihívás natív Excelben elkészíteni mindenféle makró nélkül…
365-ös Excelben vannak olyan függvények, amik nagy segítséget tudnának adni, de még egyszer: Ez egy fejlesztés, ami nem nagyon bonyolult, de nem is annyira egyszerű. Az elején kell jól megtervezni és tesztelgetni rész megoldásokat, hogy ne kelljen azután folyamatosan foltozgatni.
Bocs, ha hosszú lett,
Imre
Sziasztok!
Ha jól értem, a feladat az, hogy hétfőtől péntekig minden nap külön színt kapjon.
Magán véleményem szerint ezt nem csinálnám, de ha ez kell, akkor ez kell.Ehhez viszont nem kell feltétlenül formula alapú szabály.
Ugyanarra a tartományra („C” oszlop adatai) kell csinálni 5 szabályt az 5 napnévre.
Tehát:
1. Kijelölni a C oszlopot. Nem feltétlenül az egészet, mert az lelassíthatja az egész munkafüzetet, csak annyit, amennyi adat várhatóan lesz benne mostanában vagy valaha)
2. Kezdőlap -> Feltételes formázás -> Cellakiemelési szabályok -> Egyenlő…
3. A Textbox-ba beírod, hogy Hétfő (kis/nagybetű nem számít), majd mellette a Formátum-ot lenyitod és választod az egyéni formátumot, és ott beállítod a Hétfőhöz tartozó színt.
4. Ezt megismétled Kedd-Péntekig csak más-más színekkelEnnyi
A hét napjait szövegesen a dátumból lehet formulával előállítani a SZÖVEG függvénnyel.
=SZÖVEG(„A1″;”nnnn”)Imre
Örülünk 🙂
Szia!
Miért nem az Excel mintát töltöd fel? Abban rögtön tudnánk ,megoldást csinálni és visszaküldeni neked.
Nos, akkor valahogy úgy van, ahogy gondoltam.
Valaki eldönti, hogy milyen határidőre vállaljátok, és ennek függvényében kell kitölteni a „lejár oszlopot”Szeretném elkerülni, hogy udvariatlannak vagy tiszteletlennek tűnjek, már jártam így jó szándékom ellenére.
Ez a VKERES vonal nem jó irány, próbáld meg megfontolni, amit tanácsolunk neked.A lényeg, hogy az esetet ismerve, valaki a határidő oszlopban hoz egy döntést, és ez alapján kell dátumot képezni, ami 10 vagy 5 vagy 1 nappal később van, mint a felvétel. Semmi értelme 3 dátumot képezni, majd azokból válogatni.
Ezt Verax javaslatára fix dátum beírással is lehetne csinálni, bár valljuk be, hogy macerás egy kicsit.Javaslatom egy kétszeres HA függvény. Tedd C2-be ezt, és húzd le, majd próbálgasd.
=HA(B2="10 napos";KALK.MUNKANAP(A2;10);HA(B2="5 napos";KALK.MUNKANAP(A2;5);KALK.MUNKANAP(A2;1)))A D-E-F oszlopok nem kellenek (Szerintem). Ha szeretnéd, az Excel fájlt is felcsatolom.
Imre
Hidd el nekem, sokkal hamarabb a végére járnánk, ha összeraknál egy kis mintát és feltöltenéd a te verziódat.
A statikusan átírt számokkal a VKERES-ben.Nem válaszoltál arra, hogy mi van a D oszlopban? Ha ott van a legördülő, akkor mi van benne?
Majdnem biztos, hogy nem a jó track-en jársz a VKERES-el.
De az is simán lehet, hogy még mindig nem értem, mert a fenti VKERES képleted mindig a megadott tartomány első (jelenleg a 4-es) sorában keres, mivel le van fixálva a tartomány hivatkozás. Ez nincs pariban azzal, amit én az előző hozzászólásban leírtam.
Én úgy képzeltem, hogy soronként működne a dolog, tehát:
1. Az aktuális sorban a C oszlopba megadsz egy felvétel dátumot
2. A F-G-H oszlopokba kiszámolod a 10-5-1 munkanappal későbbi dátumokat.
3. A D oszlopban(?) választasz egy legördülő menüből (mi is a menü tartalma?)
4. Az E oszlopról még nem esett szó. Ide jönne a VKERES? Tegyük fel.
Szóval ez a legördülőben választott érték függvényében az F vagy a G vagy a H oszlop aktuális sorából kiveszi a dátumotAmennyiben ez így van, akkor ez egy sima HA függvény. Ha nem, akkor nem értem az Excel modelledet.
Látod, erre gondoltam és gondolok mindig, amikor azt kérem, hogy pontosan fogalmazzunk, úgy, mintha egy teljesen kívülállónak beszélnénk. És minta nélkül még mindig lehetnek félreértések.
Sokszor előfordul, hogy valaki „csak” azt szeretné tudni, hogy hogy lehet egy bizonyos dolgot megcsinálni, ami az ő gondolatmenete szerint kell a folyamathoz, majd kiderül, hogy az alapgondolat nem jó, vagy túl bonyolult.
Ha csak azt szeretnéd tudni, hogy mit kell a 2-es helyett írni a VKERES-be, hogy lefelé másoláskor növekedjen, akkor azt válaszolom, hogy a SOR() függvényt.
Imre
Sziasztok!
Minta hiányában csak próbálom összerakni, hogy mit szeretnél.
Amit értek, az az, hogy a C oszlopban van egy munkafelvételi dátum, és az F-G-H oszlopokban ehhez képest 10-5-1 munkanappal későbbi dátumok vannak. (Megjegyzem, hogy ha a KALK.MUNKANAP függvénynek nem adod meg a 3. paramétert, akkor a Magyarországon gyakran előforduló munkanap áthelyezések és hétközbenre eső ünnepek miatt nem fog helyesen működni)Hol van a legördülő menu és mi van benne?
Talán a D oszlopban minden cellában van egy legördülő, ami 10;5;1 listát tartalmaz? Vagy 1;2;3-t?Hová írod a VKERES-t? A végére, mondjuk az I oszlopba? Mit szeretnél ott látni?
Talán az F-G-H oszlopok értékei közül azt, amit a legördülőből választottál?Én (is) csak találgatok.
Mindenesetre ha ezt szeretnéd, amit tippelek, akkor ez ugyan megoldható VKERES-sel talán valahogy, de nem biztos, hogy a legjobb választás, és biztosan nem úgy, ahogy próbáltad.
Imre
Majdnem.
Az eredményül kapott táblázatot korlátozottan, de formázhatod.
Azért korlátozottan, mert az egy „Táblázat” ha érted mire gondolok.
Ez a rész frissülni fog és a források aktuális adatai jelennek meg benne értékkéntHa hozzáadsz új oszlopokat, akkor azok kikalkulálódnak a frissítés után automatikusan és a méretileg is hozzáigazodik, mert az is egy táblázat része. Fontos, hogy új kalkulált oszlopokat csak a végére tegyél. Ott a képletek meg fognak maradni.
I
Szia!
A PowerQuery nem foglalkozik a formátumokkal és a képletekkel sem, simán értékként kezeli őket.
Tehát, ha egy Excel munkalapot/tartományt beolvasol PQ-ba, ott bármit csinálsz vele, akár hozzáfűzöl egy másik lekérdezést, az eredményként Excelbe visszatöltött tábla értékeket fog tartalmazni, és táblázattá lesz alakítva.Viszont frissíthető, azaz az eredeti forrásban ha megváltozik bármelyik adat, akár a formulák eredményei, akkor az az eredményben meg fog jelenni, főleg akkor biztosan, ha a források munkalapok és nem tartományok. De ha táblázattá vannak alakítva, akkor már jó lehet.
Imre
P.S
Nem is értem, hogyan tudtok meglenni PowerQuery nélkül különben… Simán kiváltható vele a makrózás bizonyos esetekben.
Van egy remek videókurzus, amit nem csak én ajánlokDe most nyáron készül egy akció is.
Imre
Akkor fogalmam sincs sajnos.
Ilyesmi akkor szokott lenni, amikor az Excel kapcsolatban van valami külső forrással és azt mondjuk indításkor frissíti.I
Nem tudom, hogy mi az a sales autopilot, de ez nem érdekes most.
A kérdéseimre nem válaszoltál. Egyikre sem.
I
Szia!
Kérdések:
1. Ugyanazon a lapon a táblázaton kívül, egy üres oszlop méretét ha változtatod, akkor azt megőrzi?
2. Az illető oszlop nem egy PIVOT táblában van?I
Szia!
Biztos, hogy valamire rosszul emlékszel.
A Pivot cache-t a memóriában tárolja, illetve mentéskor a fájlban valahol.
Ez a Pivot tábla adatforrásának egy meglehetősen jól tömörített verziója.
Ez SEHOL nem jelenik meg az Excel felületen!!!Ha a PIVOT alól kitörlöd az adatforrást, akkor csak a cache marad, de továbbra is működik a PIVOT, mert csak a cache-ből dolgozik. Aztán ha duplán kattintasz valamelyik összesített értékre, esetleg a végösszegre, akkor visszaadja az adatforrást egy külön lapra.
Szerintem ezt a cikket keresed, ahol mindez le van írva.
https://excel-bazis.hu/tutorial/pivot-cache-story
De neked ez nem alkalmas most.Küzdhetsz vele, de ennyi adat már problémás az Exelben, ahogy már írtam is.
A gond az, hogy ezután technológiát kellene váltani valamilyen adatbázis irányba és azt kezelni valamilyen programból, ami nem is biztos, hogy VBA.
Ezzel persze sok minden jár, de főleg egy halom tanulás és időHa ragaszkodsz az Excelhez, akkor esetleg azt kipróbálhatod, hogy a makrók úgy működjenek, hogy a nagy fájl már meg van nyitva és ne a makró nyissa meg, és esetleg ne is ő mentse el.
Így csak azt kell kivárni, amíg manuálisan megnyílik. Egyszer volt egy ilyen projektem, ami nagy, lassan nyíló Excelekkel dolgozott, és a makró csak azt ellenőrizte, hgy egy adott nevű Excel már nyitva van-e, és csak akkor indult, ha igen.Imre
Szia!
Ilyen mennyiségű adat kezelésére az Excel már nem igazán alkalmas.
Mit érsz azzal, ha a méretét nem szerkeszthető formában csökkented?
Én azt tippelem, hogy csak úgy magában is rémálom lehet vele dolgozni, még egy atomerőmű gépen is.Ha elmented XLSB formátumban (Bináris Excel munkafüzet), akkor csökkenhet a tárolási mérete, de a munka közben az eredeti méretet bontja ki a memóriába.
Aztán az is lehet, hogy ha PIVOT riportot akarsz készíteni ebből a nagy fájlból, akkor egy külön Excel-be teszed a Pivot táblát és a sok adatot tartalmazóra külső hivatkozást adsz meg. Így a Pivot-os Excel csak a kimutatást és az abból képzett tömörített Pivot Cache-t fogja tartalmazni, ami jóval kisebb lesz. Frissítéskor azonban ki kell várni természetesen.
Én most csak tapogatódzom, mert nem írtad le, hogy tulajdonképen mi a problémád azon kívül, hogy nagy a fájl. Az egyértelmű, hogy sokáig tart, amíg megnyílik meg elmenti, ezt nem nagyon lehet megúszni. Akár bele is fagyhat…
De pl. vannak-e benne képletek is?
A vele végezni kívánt műveletek alapján lehet javaslatokat tenni.
Tehát javaslom az egész problémát felvázolni, nem csak egy részét, hogy ne menjen tévútra a dolog és az időnket hatékonyan használjuk.De sok jóra ne számíts.
Imre
Szia!
Az ismétlődések eltávolítása csak függőleges oszlopon vagy tartományon működik, vízszintesen nem.
Ha jól látom, akkor Excel 365-öt használsz, abban már van EGYEDI függvény, ami viszont működik vizszintesen is, viszont az ismétlődések nélküli eredményt egy másik tartományba teszi.
Írtam erről egy anyagot még 2020-ban.Imre
A nullák ismétlését kell csinálni annyiszor, amennyi a legszélesebb érték a megadott tartományban.
Ez angol verzióba így néz ki (most épp ezen állok és lusta vagyok egy restartra csak emiatt:=TEXT(A1;REPT("0";MAX(LEN(A:A))))TEXT = SZÖVEG
REPT=SOKSZOR
LEN=HOSSZImre
Szia!
Natívan nem létezik un. date picker a 365-ben.
Vannak különböző Excel add-in-ek, amiket telepíteni kell, és csak azokon a gépeken fog működni, ahol ez fel van téve az Excel alá. A dokumentum nem fogja hordozni, hacsak nem egy makrós megoldást választasz, és a munkafüzetben benne van a makró is, ami nem a legjobb döntés.Ha esetleg még nem tetted meg, akkor keress rá erre:
„excel 365 date picker”Imre
Eseménykezelő makróval megoldható. Tehát makrós munkafüzetté válna.
Szerintem nem ér annyit a dolog, de ez csak magánvélemény.Imre
Excel 365-ben van már egy olyan függvény, hogy SZÖVEGFELOSZTÁS
Ha korábbi verzió, akkor makrózni kell.Imre
Szuper!
🙂Nagyon szívesen.
Örülünk 🙂Nagyon érdekes.
Ezt próbálnám elsőként
1. A Név mezőbe beírnám, hogy A64 -> Enter
2. Kezdőlap -> Formátum -> Sormagasság…Ha ez nem válik be
1. Kijelölném a munkalap összes celláját -> Ctrl+C
2. Nyitnék egy új munkalapot
3. Érték beillesztésha vannak formulák, azokat esetleg újra megcsinálnám, illetve az első sorokba átlehet hozni az előző lapról, majd lemásolni.
Imre
Szia!
A 2021 az jó hír, bár a FŰZ függvény 2018-as vagy még korábbi, ha jól emlékszem.
Viszont a 2021 már tartalmazza a SZŰRŐ függvényt is, illetve már új módon kezeli a tömböket, ezért kettő megoldással is szolgálhatok. Mindkettő tömbképletFŰZ-HA
=FŰZ(HA(A2:A8=A2;B2:B8;""))FŰZ-SZŰRŐ
=FŰZ(SZŰRŐ(B2:B8;A2:A8=A2))Csatoltam a fájlt is
Imre
Attachments:
You must be logged in to view attached files.Szia!
Kérdések:
Melyik Excel verziót használod?
Összeadni szeretnéd vagy osszefuzni? Gondolom utóbbi, csak a kérdés szövegében mindkettő szerepel
Osszefuzeskot a tagok között kell-e valami elválasztó karakter? Vessző, pontos vessző, bármi…
Örülnénk, feltoltenél egy minta munkafüzet, és az még gyonyorubb lenne, ha egy cellában megadnád manuálisan, hogy milyen eredményt szeretnél látni.
Köszi,
Imre
Örülünk 🙂
Szia!
Ha 365-os Excelt használsz, akkor ez nagyon egyszerűen megoldható
Az első, gyűjtő munkalapon lévő tartományt alakítás táblázattá
A többi, csak adott nevekhez tartozó lapokn használd a SZŰRŐ függvényt.
Mindkettőhöz találsz oktatoanyagot itt a bázison.
Imre
Szuper, ügyes vagy!
Örülünk 🙂Egy ilyen „egyszerűbb” esetben abból szoktam kiindulni, hogy ha hiba adódik, akkor az csak az lehet, hogy nincs ott pivot tábla. Ezért egyszerűen a
if Err.Number<>0
irányt választom.A K alatti táblázatok méretének megállapításához:
Ha mindegyik táblázattá (listobject) van alakítva (amit javaslok), akkor van a legegyszerűbb dolgunk, mert akkor az előbbiekhez hasonlóan a
Activecell.Offset(1,0).ListObject.Name
megmondja a nevét, ami a legyártandó pivot adatforrása lehet.Ha nincs táblázattá alakítva, akkor a K alatti cellától indulva kell bűvészkedi. A bonyolultság attól függ, hogy minden sora és oszlopa ki van-e töltve (legyen!!!)
Ebben az esetben a kezdőcella a K alatti cella, a vége pedig a lefelé majd jobbra irányított End-ekkel megállapítható:
Activecell.Offset(1,0).End(xlDown).End(xlToRight)Imre
Csatlakoznék Verax-hoz némi részlettel.
Én is a K-kat keresném. A K pozíciójához képest mindig ugyanannyi offset-tel kell lennie vagy nem lennie a kimutatásnak.
A PIVOT létezését én úgy szoktam megállapítani, hogy lekérdezem valamelyik tulajdonságát, és ha hibát ad, akkor nincs ott PIVOT.Próbáld ezt ki:
1. belekattintasz egy létező PIVOT-ba és az Immediate ablakba beírod:
?Activecell.PivotTable.Name
2. Válassz egy PIVOT-on kívüli cellát és ismételd meg a parancsot, hibát fogsz kapni nyilván.Menjünk közelebb.
1. Kattints az egyik K-ra, ahol van pivot a tábla mellett
2. Az előző parancsot offset-tel add ki
?Activecell.offset(sor, oszlop).pivottable.name
3. Ha nincs ott Pivot, akkor hibát fogsz kapni.Tehát a Pivot létezésének megállapítását klasszik on error resume next módszerrel meg lehet csinálni.
Ezután térhetsz rá a pivot legyártására.
Imre
Kedves Csokesz!
Sajnálom, hogy megbántottalak. Benne volt a pakliban.
Tudod, az INDIREKT használata elég ritka, kevesen élnek vele, mert elég bonyolult a használata, illetve annak megértése.
A kérdésed viszont nagyon egyszerű volt és ehhez képest verax megoldása túl bonyolult.Benéztem, még egyszer elnézésedet kérem, és persze megértem, ha emiatt kiábrándultál.
Imre
Na. Ezt megbeszéltük. Remélem nem riasztottuk el csokeszt 🙂
Sziasztok!
Szerintem ez a dolog túl van bonyolítva.
Csokesz-től kérdezném, hogy ezt az Excel füzetet te állítottad-e össze?
Tudod-e mire való és mit csinál az INDIREKT függvény?
Tudod-e, hogy miért változnak meg a dolgok, ha a B1-be másik számot írsz? (Ami valójában egy munkalapnév)Ha nem te készítetted a munkafüzetet, akkor az is esélyes, hogy „csak” használni szeretnéd.
Én csak abból indulok ki, hogy ha egy ilyen egyszerű összesítési problémában elakadtál, akkor lehet, hogy az INDIREKT sincs meg.
Ne vedd zokon azt, amit írok, igazából Verax-nak is írom. Mert az ő képleteiből tanulni akkor tudsz, ha megérted őket magyarázat nélkül.Mert ez a probléma felhasználói szintem
– Ismétlődések etávolítása + SZUMHA vagy
– PIVOT táblaFőleg, ha mindig ugyanazt a W6:X85 tartományt kell összegezni.
Tehát én azt tanácsolom, hogy
1. Másold át a W5:W85-öt az AD5-be
2. Jelöld ki
3. Adatok -> Ismétlődések eltávolítása
4. AE oszlopba írhatod a SZUMHA függvényt, amit Verax adott.Simán, egyszerűen.
Ha bárkit megbántottam, elnézést kérek 🙂
Imre
Szia!
Ne haragudj, de a leírásból nem nagyon értem, hogy mit szeretnél.
Próbáld meg úgy megnézni, amit írtál, hogy egy teljesen kívülálló olvassa.
A fórum szabályoknál leírtam, hogy ha lehet, töltsetek fel minta fájlt, amiben akár az is benne van manuális beírással, hogy mit szeretnél látni eedményképpen.A kérdésedre általálnos megoldásként jó lehet a SZUMHA függvény vagy PIVOT tábla, de lehet, hogy kell az FKERES is, mit tudom én! 🙂
Imre
2024-03-07-15:53 Hozzászólás: [Resolved] Két táblázat adatainak beillesztése és táblázat átméretezése #10538Látom közben elvoltatok ezzel, ezért csak egy tapasztalatot tennék ide.
Nem tudom miért, de már többször belefutottam abba, hogy egy makró jelentősen lassabban futott, amikor táblázatként kezeltem a kódban egy tartományt, mint amikor tartományként. Attól még táblázat maradt, de a Delila féle módszerrel méregettem meg, hogy mik a dimenziói.
Az egyik esetben min. 100x sebességkülönbséget mértem. (Egy hosszú ciklus volt, táblázatmanipulációkkal)
Ebben most nincsen ciklus, valószínűleg nem nagyon számít, csak tudjatok róla, hogy a hiba nem a ti készüléketekben van, ha ilyesmi előfordul
Imre
2024-03-05-10:31 Hozzászólás: [Resolved] Két táblázat adatainak beillesztése és táblázat átméretezése #10527Szia!
Az Excelt azért elküldhetnéd, akár Fake adatokkal, a kód önmagában legtöbbször kevés.
Nem elemeztem még a kódot, nem olyan rövid, de ha próbálni szeretném, nem szeretnék időt tölteni minta adat generálásra, táblázat átnevezésre, ráadásul az egy másik fájl lesz, amin lehet, hogy másképp működik.Legjobb, ha makrós fájl csatolsz adatokkal együtt, de előtte zip-elni kell, különben biztonság miatt nem fogadja el.
Ja, és még egy:
ha kódot teszel fel, akkor a beküldés előtt a kódot jelöld ki, és a szerkesztő tetején találsz egy „CODE” gombot.
ha ezzel formázod, akkor nem alakítja át és pl. a sima dupla idézőjeleket nem teszi nyomdai idézőjellé, amitől megint nem működik, ha bemásolom a VBA editorba, és keresés-cserét kell rajta csinálni, vagy kézzel javítgatni.Köszi,
Imre
Ez jó megoldás!
Sőt, sokkal jobb! Főleg, mert egyszerűbb! 🙂
Beragadtam ebbe e TAKE/DROP dolgba.Köszi!
Már jó ideje tanulmányozom az új tömbfüggvényeket.
A DROP és a TAKE csak az elejéről vagy negatív számok esetén a végéről dob el vagy tart meg sorokat.
Sajnos olyat nem tud, hogy az n-dik sorral csinálja.
Ezért kellett Salának az aktuális sornál kettévenni, majd megint összerakni.Imre
Hát, egyszerűbben semmiképpen, sőt!
Nekem csak úgy sikerült, hogy külön vizsgálom, hogy az első sorban vagyok-e és külön hogy az utolsóban, mert akkor mást kell csinálni, mint két részre szedni, ami különben jó gondolat.
Ha az elsőben vagyok, akkor azt kell eldobni, ha az utolsóban, akkor azt, különben szétszedés és halmozás, ahogy szuperül csináltad.=TRANSZPONÁLÁS( HA(SOR()=1; ELTÁVOLÍT(szamok;1); HA(SOR()=DARAB2(szamok); ELTÁVOLÍT(szamok;-1); FÜGG.HALMOZÁS(ÁTHELYEZ(szamok;SOR()-1);ELTÁVOLÍT(szamok;SOR()) ) ) ) )Attachments:
You must be logged in to view attached files.Örülünk 🙂
Az új, 365 tömbfüggvények, élükön a SZŰRŐ-vel és a # hivatkozással, igazi „GameChanger”-ek.
Az akárhányszintű listát is ezekkel lehet egyszerűsíteni.Én pedig válaszoltam rá, lehet, hogy nem kaptál róla értesítést…
Kérdések-válaszok számára a fórumot ajánlom, a post hozzászólások nem nagyon mennek…I
Szia!
Neked igazad van, bocsánat, alapból valóban csak „kezdődik” típusú szűrést tud.
De jó hír, 365 van, és egy átmeneti oszlopban használhatod a SZŰRŐ (FILTER) függvényt.Elég hosszadalmas lenne leírni, ezért becsatolok egy munkafüzetet.
Köszönöm a kérdést, azt hiszem, hogy ez lesz a következő videó témája.
Imre
Attachments:
You must be logged in to view attached files.Szia!
A nulla és az üres cella nem teljesen ugyanaz.
Egy egy kiváló példa rá.
Ha beírod a körte oszlopba a két nullát az üres cellákba, akkor meg fog javulni.Ha az üres cellákra akarsz szűrni, akkor egy sima, konstans szűrésnél egyszerű a helyzet.
A feltétel két dupla macskaköröm.=SUMIF(Táblázat28[Körte];"";Táblázat28[alma])De ha a feltételt egy cellából kell venni és abban üres van, mint a te esetedben, akkor úgy tűnik, azt nem használja feltételként. Ez azért van, mert ha hivatkozol egy üres cellára, akkor az nullát fog visszaadni és az nem egyenlő az üressel.
Ha ráhivatkozol a C3 cellára, mindegy, hogy a címével vagy a táblázat referenciával ( =C3 vagy =Táblázat28[@Körte] ), nullát fog visszaadni, nem üreset, mert ilyen érték jelenleg nincs megkülönböztetve az Excelben.
Ez különben elég gáz szerintem is.A megoldás az lehet, hogy ellenőrzöd a szűrésre használt cella ürességét, és ha nem üres, akkor mehet a rendes SUMIFS, különben az üresre szűrő SUMIFS.
=IF([@Körte]<>"";SUMIFS([alma];[Körte];[@Körte]);SUMIFS([alma];[Körte];""))Ez van.
Imre
Szia!
Én csak a kétszintű listával foglalkoztam, ahogy írtam is. Egy kis munka maradjon neked is 🙂
Ha az első lapon a táblázat utolsó sorának utolsó cellájára kattintasz, és nyomsz egy TAB-ot, akkor nyit egy új sort.
Az elején választhatsz a meglévőkből a listából, de be is írhatsz egy újat, mert a szabályt csak figyelmeztetésre állítottam.ha új gyümölcsfajtát írsz be, az meg fog jelenni a params lap első oszlopában, és a C oszloptól induló fejlécben is.
Ami nem automatikus, hogy a C2-től vízszintesen futó képletet kézzel kell kiterjeszteni az új tételre, hogy hozza a hozzá tartozó adagszámokat.
Ezek aztán már választható elemként megjelennek a második lapon.Ez ennyit tud.
Imre
Szia!
Az Excel 365 ezt már tudja. (kb. fél éve) Biztonság kedvéért most újra kipróbáltam. Tehát ha a tételek bármelyike tartalmazza a leütött betűt vagy betűket, akkor azokra fog szűkíteni a listában.
Korábbi verziók pedig nem tudják, és nem is fogják.
Ja, és az online Excel is tudja.
Imre
365 függvények vannak benne, ezért kérdeztem.
Imre
Nem semmi, mennyi melót beleraktál Verax!
Kíváncsi vagyok Gergő reakciójára.Mindig érdekelt, hogy hogy kell egyedi Ribbon-t készíteni azon túl, hogy az Excel beállításaiban kavarok.
De soha nem szántam erre időt.Te melyik Custom UI (RibbonX) editor-t használod?
Szóval gratula,
Imre
Sziasztok!
Egy kicsit foglalkoztam a problémával, kizárólag a kétszintű legördülő listára koncentrálva, mert a többi dolog nem tűnik nehéznek, és talán meg is oldódott már.
A fő kihívás az, hogy a második táblában sorról sorra kell megjeleníteni a legördülő menüket. Az első oszlop szabálya egyszerű, lehet mindig ugyanaz, ami a gyümölcsök egyedi listája. A második oszlopban viszont feltétel mindig változhat annak függvényében, hogy milyen gyümölcsöt választottak az előző oszlopban.Azért kérdeztem, hogy milyen Excel-t használsz, mert a 365-ben lévő tömbfüggvények, jelen esetben a FILTER (SZŰRŐ) segítségével megoldható a feladat.
Összességében viszont meglehetősen magas szintű dolgokat kellett bevetni:
– Táblázattá alakítás
– Abszolút oszlopcímzés táblázatban
– Tömb függvények: EGYEDI, SORBA.RENDEZ, SZŰRŐ
– Tömb hivatkozás
– OFFSET függvény
– Adatérvényesítési listaszabály tömbhivatkozó formulávalEzek közül nem tudom melyeket ismered.
Az egész megoldást lépésenként leírni meglehetősen hosszú lenne.
Leírás nélkül meg kevés esély van a megértésre, ami kellene az implementációhoz.Javaslom, hogy nézd meg a csatolt munkafüzetet, és jelezz vissza, hogy egyáltalán erre gondoltál-e
Most „csak” felhasználói leírást tudok adni:Első lap
—————
1. Az első lapon lévő tartományt táblázattá alakítottam. Az a neve, hogy „raktar”
2. Ennek előnye, hogy a hozzáadott új sorokat automatikusan felveszi magába, nem kell a hivatkozásokat utólag bővíteni.
Sajátossága, hogy a táblázatra nevekkel lehet hivatkozni
3. A táblázat első oszlopából csináltam egy egyedi listát a harmadik lap (params) első oszlopába egy tömb formulával
Ez a tartomány adja az első lap első oszlopába a legördülő lista szabály forrását (params!$A$1#)
A # azt jelent, hogy az A1-nél kezdődő, bármeddig tartó tömb.
Ezzel az első lap legördülő menüje megvan. Ha új sort akarsz felvenni az első lapon, akkor csak az első szabad sorba kell kattintani a tábla végén, és a listamenüből választani egy elemet.
Ha új elemet akarsz felvenni, akkor csak gépeld be. A szabály megengedi. Ezután az új elem magától meg fog jelenni a params lap első oszlopában és az első lap legördülő listájában is.
Az adagszámot és a mennyiséget kézzel írjátok.A második lap
——————-
Ezt is táblázattá alakítottam, csak az automatikus kiterjedés miatt
1. Az első két oszlopban van a két szintű legördülő lista
2. Az első szint forrása szintén a params lap A oszlopába lévő tömb.
3. A második szint hordozza ugye a fő kihívást.
Ehhez egy másik, speciális „önkarbantartó forrásrendszert kellett építeni tömbképletekkel a params lapra.
Ez a C oszlopban kezdődik, és annyi oszlopa van, ahány egyedi gyümölcsnév van az első lapon.
Az első sorban ezt a listát látod elforgatva, formulával létrehozva. Hivatkozása: $C$1#
A fejléc alatt van egy eléggé összetett hivatkozás, ami dinamikusan listázza a fejlécben lévő gyűmölcshöz tartozó adagszámokat, amelyek még nincsenek selejtezve. Ezt egy SZŰRŐ függvény csinálja. Ebben vannak az abszolút táblázat hivatkozások is)4. A legördülő szabály a 2. szinthez
Ez volt a legnehezebb, mert itt követni kell, hogy az aktuális sorban kiválasztott gyümölcs adagszámait mutassa.
Ehhez ki kell keresni a választott gyümölcsöt a fenti táblázatrendszer fejlécében, majd ettől egyel lejjebb kezdődő tömb lesz a listaszabály forrása.
A formulát az adatérvényesítés szabályában találod. (OFFSET)
De játszogattam vele a params lapon fejlesztés közben is, az L1-ben látod.Majd jelezz vissza, hogy erre gondoltál-e!
Imre
Attachments:
You must be logged in to view attached files.Szia!
Egy kérdés: Milyen verziójú Excel-t használsz?
Imre
Még az is lehet, hogyan a VLOOKUP helyett FKERES kell, vagy a vessző helyett pontos vessző a paraméterek között.
AI? Azt a mindenit!
Hogy magyaráztad el neki, hogy mit szeretnél?
ChatGPT (OpenAI) volt, vagy az Office-on belüli Copilot?
Tehát akkor te nem ismered a fent említett függvényeket…Ezt írtam a B7-be:
=VLOOKUP(B5;hivatkozás!$A:$B;2;0)Imre
Szia!
Kipróbáltam a B7-be írni egy VLOOKUP-ot.
Nem hibát adott, hanem a képlet maga maradt a cellában.
Ez azért van, mert a cella TEXT formátumra van állítva.Ha VLOOKUP beírása előtt General-ra állítod a típust, akkor jó lesz.
Legalább is nekem jó lett.Imre
Szia!
Én egyáltalán nem értek ehhez, nem is tudok segíteni, de esetleg PowerQuery-vel meg lehetne próbálni a feldolgozást.
Nézted már?Imre
Szia!
Nagyon szuperül definiáltad a feladatot! Ez egy két szintű legördülő listamenü.
A megoldást még 2015-ben publikáltam.https://excel-bazis.hu/tutorial/ketszintu-legordulo-lista-keszitese
Imre
Már ránézésre látszott, hogy ez lesz 🙂
Ha valóban megismerkednél az eszközzel mélyeben, akkor most szerencséd van, mert épp 30%-os akció lesz februárban a videótanfolyamaink oldalán. Itt találsz egy komoly anyagot a PIVOT táblákról.
https://videotanfolyam.pentaschool.hu/Imre
A feladat elsőre is érthető volt nekem, de így még egyértelműbb.
Megerősítem, hogy ez klasszikusan kimutatás (PIVOT tábla) feladat.
Lehet ugyan SZUMHA függvényezni is, de előbbi jobb és egyszerűbb szerintem ebben az esetben.Bármelyiket is választod, egy külön un. report tábla lesz az eredmény, ami kb. úgy néz ki, hogy első oszlopában van a termék ID-k egyedi listája, és mindegyik mellett egy második oszlopban a hozzá tartozó össz. darabszám.
Utánanéztél az általam javasolt megoldásoknak?
Eléggé hosszadalmas lenne így leírni lépésenként, illetve ezzel az egész bloggal és fórummal az a célom, hogy a felmerült kérdéseket az itt kapott iránymutatások alapján az illető önállóan meg tudja oldani, mert abból tanul igazán. Abból kevésbé, hogy lépésenként megkapja, mit csináljon. Tanár vagyok, ez van 🙂
Kivétel lehet ez alól az olyan probléma, ami jóval magasabb szintű ismerettel oldható csak meg. A PIVOT, illetve a te problémád nem ilyen.A PIVOT táblás megoldás kezdeti lépései
1. belekattintasz a táblád bármely cellájába
2. Beszúrás menü -> Kimutatás
3. OK
4. kapsz egy új oldalon bal oldalt egy üres valamit, jobb oldalon pedig a kimutatás összeállításához egy panelt, ahol fel fogod ismerni az oszlopneveket (Kimutatásmezők), illetve négy fehér, üres dobozt.
5. A termék ID mezőt vontasd bele a SOR dobozba. Ezzel megkapod az ID-k egyedi listáját. (Bár ha jól látom akár az Árunevet is odahúzhatod, mert 1:1 megfelelésűek)
6. A teljes készlet mezőt húzd az Érték (Szumma) dobozba és kész vagy
7. Ha az árú nettó összértékét is belehúzod az Érték dobozba, az előző alá, akkor azt is össze fogja adni termékenként, így nem kell szorozgatni az egységárral. Ez akkor ad egy harmadik oszlopot.Imre
Szia!
Első ránézésre ez egy klasszikus PIVOT tábla feladat. Magyar neve kimutatás.
Hegyekben állnak a leírások és videók erről az eszközről.Másik lehetőség a feltételes összegzés lenne (SZUMHA függvény)
Imre
Én is 32 bites verziót használok.
A teljesítmény különbség csak nagy méretű, kalkuláció-intenzív fájlonál jöhet elő.Ha már a függvények ennyire számítanak, akkor miért nem fontolod meg a 365 előfizetést?
21 óta is elég sok újabb függvány van, és lesz is, nem is akármilyenk…Imre
OK. De különben megoldódott az, amit akartál csinálni?
Szia!
Ne haragudj, de vagy én nem értek valamit, vagy te.
Megnéztem a csatolt fájlt. Egy Wiki oldalról behozod a magyarországi települések táblázatát.
Egyértelműen benne van, hogy melyik település milyen kategória és melyik vármegye.Munka1 lapon te H7-ben kiválasztod a várost, amihez CSAK egy típus és CSAK egy vármegye tartozik. Ezt te remekül meg is oldod FKERES-el a mellette lévő 2 cellában.
Nem értem hogy jön ide az INDIREKT?
Azt se, hogy a második táblázatban, ami a H12-nél kezdődik, miért csinálsz 3 legördülő listát akkor, amikor az első a város, amihez nem tartozhat több típus és több vármegye, amiből választani kellene.Ha nem ez lenne a sorrend, akkor már lenne értelme a feladatnak.
Például első oszlopban megyét választani, és a második szinten a legördülő csak azokat a városokat mutassa, amik a kiválasztott megyéhez tartoznak. Ha választasz várost, akkor a harmadik szinten szintén nem kell már legördülő, csak egy sima FKERES.Utóbbi verzióban ez egy 2 szintű, függő legördülő listamenü probléma, amire több lehetséges megoldás létezik.
Kettőről írtam cikket is itt a bázison. Lehet, hogy innen keverted be az INDIREKT-et, mert az egyikben ezzel van megoldva.De hangsúlyozom, a te általad vázolt sorendben nincs értelme, met ha kiválasztasz egy várost, akkor a hozzá tartozó típus csak 1 elemű és a megye is csak 1 elemű, tehát az FKERES elegendő megoldás.
Vagy valamit nem jól értek.
???Különben milyen Excel verziót használsz?
Imre
2024-01-17-01:26 Hozzászólás: [Resolved] Power Query dinamikus adatforrás dinamikus adattítpus #10377Valószínűleg nem értettem meg a problémát teljesen …
Mindenesetre jó hír, ha találtál megoldást.I
Sajnos nem fogsz találni rá varázslatos megoldást. A listából kiválasztott adat ugyanúgy csak egy sima érték, mintha begépelted volna.
Tehát csak cserével (Ctrl-H) tudod megoldani. Ezt lehet munkafüzet szinten is futtatni, tehát minden munkalapon cserélhető Pisti Bélára.Imre
2024-01-16-23:08 Hozzászólás: [Resolved] Power Query dinamikus adatforrás dinamikus adattítpus #10370Szia!
Ha jól értem, akkor a „revenue growth” és a „tax ratepercentage” oszlopok mindig ott vannak, de a többi az változhat.
Én megpróbálnám azt, hogy minden oszlopot először egészre állítanék, és utána explicit módon ezt a kettőt visszaraknám percent-re.Ehhez persze hozzá kell nyúlni a kódhoz. Van egy olyan függvény az M-ban, hogy Table.ColumnNames(#”Előző lépés”)
Ez egy listát állít elő a táblanevekből. Ezt felhasználva betehetsz egy egyedi sort a lépésekhez, ami ennek a listának minden elemét (each) Int64-re állítja= Table.TransformColumnTypes(#"Előző lépés", List.Transform(Table.ColumnNames(#"Changed Type"), each {_, Int64.Type}))Kipróbáltam.
Nálad is jó?Ez után a két adott nevű oszlopot már szokásos módon átállíthatod egyébre.
Imre
Szia!
A probléma a # hivatkozással, illetve annak értelmezésével van.
A képletben lévő A2# az A2-ben kezdődő teljes tömbre vonatkozik, aminek 3 oszlopa van. Viszont neked ebből csak az első kell.
Tehát a most tanult OSZLOPVÁLASZTÁS lesz a barátod újra. A képletnek csak az elejét írom ide:=HA(SZÁM(XKERES(<strong>OSZLOPVÁLASZTÁS(A2#;1)</strong>;iD;szamvitel))...Így az XKERES első paramétere az A2-ben kezdődő tömb első oszlopa lesz, és az XKERES-t mindegyikre megcsinálja az ID névtartományban, és az eredmény egy ugyanannyi elemű tömb.
mre
2024-01-05-16:14 Hozzászólás: [Resolved] N-edik találat visszaadása O365 szűrő és oszlopválasztás függvénnyel #10356Szia!
Köszi, hogy megosztottad.
Esetleg érdemes meggondolni, hogy az oszlopválasztás függvénynek ebben a konkrét esetben van-e jelentősége?
Szerintem akkor lenne, ha nem csak egy oszlopot szűrnél, hanem egy tartományt, és az eredmény sorok közül az n-dik előfordulás sorának bizonyos oszlopai. Mintha több n-dik FKERES lenne egymás utánSzóval szerintem most ennyi elég:
=HAHIBA(INDEX(SZŰRŐ(A1:A6;B1:B6=D1);D2);"nincs találat")Különben az N-dik előfordulást a SZŰRŐ után lehet csinálni INDEX-el, ahogy Te csinálod, vagy SORVÁLASZTÁS-al.
Ha már O365 🙂Imre
Szia!
Nagyon érdekes. Én csak annyit próbáltam, hogy a makróval a vágólapra másolt objektum bele kerül-e a Vágólap applikációba.
Nekem nem került bele. Neked igen?
Ennek ellenére lehet, hogy mégis történik valami, és ez bezavar a makrófutásba.
Mivel ez egy Exceltől és ezért VBA-tól is független applikáció, az én tudásom szerint csak a manuális kikapcsolás jön számításba.
Ennek módját frissítésként beleírtam a szöveges cikkbe, a videó beágyazás előtt találod.
https://excel-bazis.hu/tutorial/a-nem-felejto-windows-vagolapImre
2024-01-01-17:38 Hozzászólás: [Resolved] Kép beillesztése fájlba, választott almappából minden képet #10344Esetleg megosztanád velünk, hogy mi volt a fond, és mi lett a megoldás?
Nem az egész kód, csak a lényeg.
Így tudsz visszasegíteni. 🙂2023-12-29-22:32 Hozzászólás: [Resolved] Kép beillesztése fájlba, választott almappából minden képet #10340Valami miatt nem férek hozzá, a link kattintásakor be akar jelentkeztetni, de nem fogadja el a Microsoft bejelentkezési adataimat. Különben ugyanazzal simán be tudok lépni a OneDrive-ra közvetlenül.
Szóval mi a jelenség? Mi van a képek helyén?
Gondolom a te kódodról beszélsz, tehát ezzel lehet valami gond?
ActiveSheet.Shapes.AddPicture MyPic, msoFalse, msoTrue, x, y, w, hHelyi gépen nézve megcsinálja? Beilleszti a képeket, azok láthatók, csak Onedrive-on nem jelennek meg?
Ha kézzel teszel be képet, és feltöltöd, akkor látszódik? Ha így látszódik, akkor esetleg vedd fel rögzítővel, és próbáld az használni a makróban.
Milyen Excel verziót használsz?Imre
2023-12-27-23:52 Hozzászólás: [Resolved] Kép beillesztése fájlba, választott almappából minden képet #10338Szia!
Erre a feladatra rekurzív algoritmust szoktak használni, ami saját magát hívja meg, amíg talál alkönytárat.
Ehhez úgy láttam, hogy legtöbbször az FSO objektumot használják.
Én erről az oldalról vettem egy mintát:
https://exceloffthegrid.com/vba-code-loop-files-folder-sub-folders/
A konkrét megoldás:
Using File System Object (FSO) Late Binding – Method #2
Két Sub van benne. Az első csak egy felkészülés, adatok és pozíciók megadása, a második az, ami a rekurzió.
Egy kicsit hozzányúltam, hogy az első sub-ban megadott mappát vegye kiindulásnak, és az aktuális munkalapon az A1-es cellától lefelé, egymás alá listázza a mappa és almappák fájl útvonalait.
Ott nyúltam hozzá ahol magyar kommentet látsz.Sub loopAllSubFolderSelectStartDirectory() Dim FSOLibrary As Object Dim FSOFolder As Object Dim folderName As String 'Set the folder name to a variable folderName = "C:\Users\DELL\Documents\NovoData-Word-Excel\" 'Set the reference to the FSO Library Set FSOLibrary = CreateObject("Scripting.FileSystemObject") 'Az aktuális munkalap A1-es cellájától írja ki a fájlok útvonalait 'Elsőként törli az ott lévő tartalmat Range("A1").Select ActiveCell.CurrentRegion.Clear 'Another Macro must call LoopAllSubFolders Macro to start LoopAllSubFolders FSOLibrary.GetFolder(folderName) End Sub 'Don’t run the following macro, it will be called from the macro above Sub LoopAllSubFolders(FSOFolder As Object) Dim FSOSubFolder As Object Dim FSOFile As Object 'For each subfolder call the macro For Each FSOSubFolder In FSOFolder.subfolders LoopAllSubFolders FSOSubFolder Next 'For each file, print the name For Each FSOFile In FSOFolder.Files 'Insert the actions to be performed on each file 'Az aktuális cellába beírja az aktuális fájl útvonalát 'Majd lép akövetkező sorba ActiveCell.Value = FSOFile.Path ActiveCell.Offset(1, 0).Select Next End SubHa bemásolod egy modulba, majd az első sub-ban megváltoztatod a gyökér mappát, utána futtathatod.
A többi már összerakható az eredeti kódod alapján.Imre
Hűha, de aktívak itt páran karácsony előtt! 🙂
Ha valóban csak 2019-es verziód van, akkor Verax vagy Delila megfoldása lehet jó
Ha esetleg lenne 2021 vagy 365, akkor három (vagy csak kettő) függvénnyel megoldható a probléma.Szóval?
Imre
Szuper!
Ha működik, akkor örülünk!
Szia!
Ugyan nem adtál választ arra, hogy jól tippelem-e a feladatot, de tegyük fel.
Ha nincs, és nem is tudsz szerezni 365-ót, akkor nagy szívás.Illetve ahhoz képest nagy, hogy mennyire egyszerű lenne 365-ben.
Csak ízelítőként, így lehetne átalakítani a borzalmadat normális kinézetűvé.
=CHOOSECOLS(WRAPROWS(TOROW(A3:A272);5);1;2;3)Makrót biztosan nem írunk.
PowerQuery-s megoldáshoz tudok neked linkeket adni. Kipróbáltam őket, működnek. Az a baj, hogy nem pont ugyanazok, mint a te problémád, ezért kicsit hozzá kell nyúlni. Ha még semmit nem csináltál PQ-vel, akkor ez nem lesz egyszerű
https://radacad.com/convert-flat-list-to-table-in-power-bi-pivot-without-a-set-key-column-using-power-query
https://exceloffthegrid.com/power-query-unstacking-data-in-a-column/
https://gorilla.bi/power-query/transform-stacked-data-into-table/
https://www.thebiccountant.com/2019/02/28/unstacking-columns-in-power-bi-power-query-excel/Ezzel „csak” a halmozott formátum táblázattá alakítása lenne meg, ezután jöhetne a statisztika.
Ha jól értem, akkor csak a befejező időpontot kell vizsgálni, hogy hová esik?Imre
Szia téboly! 🙂
Megnéztem a csatolt Excel-t, de nem vagyok biztos abban, hogy mi a feladat.
Talán azért, mert minta eredményeket (manuálisan) nem írtál bele.Tippem:
Hívjuk a bal oldali ismétlődő blokkokat rekordoknak
Azt kellene megadni a színes részekben, hogy az egyes megadott időintervallumokban hány darab rekord található a bal oldalon?
Tehát pl. a sárga esetén H-P napokon 7:30 és 16h között hány rekord van a bal oldalon?
De mivel a rekordokban kezdő és vég idő van megadva, még az is kérdés, hogy melyiknek kell 7:30 és 16h között lennie? Mindkettőnek?Ha ezt kell csinálni, akkor ebben a formában az adatforrás szinte teljesen alkalmatlan a feldolgozásra.
Kivéve a manuális feldolgozást 🙂Adatforrás
Egy rekordon belül az adatoknak egymás mellett és nem egymás alatt kellene lenniük, így együtt alkotva egy olyan táblázatot, aminek a fejléce így néz ki:
Dátum, Nap, kezdési idő, befejezési időTehát a kötőjellel elválasztott, szövegesen megadott időket külön kell venni kezdő és befejező, valós időre.
A lentebb lévő rekordok tartalmaznak még plusz 2 elemet, amiben mindenhol az van, hogy:
„előírt terv alapján” és az, hogy „nem”
Ezek gondolom kihagyhatók, ami a statisztikát illeti.Átalakítás
Excel 365 függvényekkel elég gyorsan meg lehet csinálni az átalakítást.
Kérdés, hogy milyen verziód van?
Ha nem 365, akkor vagy manuális munka vagy makró, esetleg PowerQuery megoldás marad.Statisztika
A színes részeket meg úgy kellene előkészíteni, hogy a kezdési és befejezési idők külön cellákban legyenek.
Ezeket kell vizsgálni, hogy egy-egy rekord beleesik-e vagy sem. (Miután kiderült, hogy minek kell beleesnie)
Amíg kötőjel van az idők között, az sima szöveg lesz, ami mindenre alkalmatlan.Hát, elsőre ennyi.
Imre
Nagyon helyes, ez a jó irány!
Excel függvénnyel sehogy.
Makrót lehet rá írni.Imre
Szia!
Kicsit attól is függ, hogy Form control-t vagy ActiveX control-t tettél a lapra.
Én Form control-t tennék, ezért ebből indulok ki.Meg abból, hogy akkor valóban megvannak a checkboxok, gondolom hozzáigazítva a cellákhoz, meg van adva a szövegük is
Ezek alkotják az aktuális lapon a CHECKBOXES gyűjteményt.
Máshol nem lehetnek checkbox-ok
Ha ez igaz, akkor kijelölve a tartományt, az alábbi makró megcsinálja, amit szeretnél.Sub Checkbox_link() Dim i As Integer For i = 1 To Selection.Cells.Count ActiveSheet.CheckBoxes(i).LinkedCell = Selection.Cells(i).Address ActiveSheet.CheckBoxes(i).Interior.Color = RGB(255, 255, 255) Next End SubNálam (365) a checkbox-ok alapból átlátszóak, ezért, ha az alatta lévő cellára linkel, akkor a TRUE/FALSE szöveg összeolvad a vezérlő feliratával.
Ezért tettem bele egy sort, ami a vezérlő háttérszínét fehérré teszi, így letakarja a cellát.Imre
Szia!
Az a helyzet, hogy un. egyszerű megoldást nem fogsz találni, ne is keress!
Ez egy meglehetősen összetett probléma, ami láthatóan te is tapasztalsz. Csak pár felvetés:
– egyszer „út” van, máskor „utca”, harmadszorra „u.”
– van szóköz vagy nincsen.
– van pont vagy nincsen
– Vannak más közterület típusok is, mint tér, körút, köz, stb. és ezeknek a verziói
– Lehetséges emelet + ajtó
– stb…Általános szabályként egyedül az mondható el, hogy minden cím esetén a közterület neve (út, utca, stb..) utáni részre van szükséged. Ráadásul az eredeti is kell, de csak a közterületig.
Ezt szövegből oszlopokkal, vagy villámkitöltéssel nem nagyon lehet megoldani.
Egyedüli lehetőség az, hogy egy külön táblába begyűjtöd a lehetséges közterület elnevezéseket, minden lehetséges verzióban, és egy külön cellában (oszlopban) az aktuális sorban ezek mindegyikét keresed. Ha bármelyiket megtalálta, akkor a találattól jobbra kell mindent kivenni, majd egy másik cellába egy másik formulával a kivett rész előtti szöveget kell kivenni. Ez utóbbi már egyszerűbb.
Az első az egy tömbképlet, vagy egy sokszoros elágazás.A megoldáshoz fontos lehet, hogy melyik Excel verziót használod.
Ja, és persze lehet makrózni is.
Imre
Valószínűleg 2 db is elég, próbálgasd!
Na, akkor megint örülünk! 🙂
Próbálj meg mondjuk 5 db „z”-t írni ezekbe a cellákba.
???Imre
Nem ebben a rendszerben alap a reakció egy segítő válaszra, hanem bármelyikben
Egyrészt a válaszadó kap visszajelzést, és ami ennél is fontosabb, hogy a fórum olvasói hitelesítve látják a megoldást, ha vissza van igazolva a kérdező által.Én ilyenkor a témát RESOLVED-ra állítom, így azonnal látszik egy keresőben is pl., hogy ezen a linken megoldás van.
Van egy másik dolog, amit kérnék: Egy kérdés kerüljön egy topic-ba. Ebben a topic-ban kettőt kérdeztél egyszerre, ez nem jó. Látod az egyik megoldódott, a másikat visszük tovább.
Köszönöm.
A maradó kérdésre visszatérve, az utolsó hozzászólásod alapján látom, hogy te azt csinálod, hogy elmented CSV-ben, aztán Exceben nyitod ki, hogy megnézd az eredményt. Ez nem jó így, mert a CSV fájlokat, amennyiben a nyelvi rendszer szerinti elválasztóval vannak tagolva (neked ez pontosvessző), az Excel úgy nyitja meg, hogy rögtön oszlopokra bontja és cellánlént autokonvertálja. Nagy számoknál 11 és 15 karakter között tudományosra, 15 karakter felett pedig még rosszabb a dolog.
Javaslom, hogy ezt olvasd el:
https://excel-bazis.hu/tutorial/15-karakternel-hosszabb-szamok-az-excelbenHa a mentett csv-t jegyzettömbben nyitod ki, akkor abban szerintem jók lesznek a hosszabb számok.
Hacsak nem volt közöttük olyan, ami alapbó hosszabb 15 karakternél.
CSV tartalmat csaj így szabad ellenőrizni, és az a másik rendszer, amely számára előkészíted, az is szövegesen fogja olvasni.A rejtett oszlopok dologra csak az a megoldás, hogy (ahogy fent is írtam) azt a munkalapot átrakod egy új mnukafüzetbe, majd kiveszed a szükségtelen oszlopokat és sorokat (mert van benne bőven), majd jöhet a mentés másként CSV-be.
Ezután már nem kell elmenteni ezt az új munkafüzetet.Imre
Szuper, pipa 🙂
Szia!
A PIVOT táblák esetén valóban előfordul ilyesmi, hogy a page filter vagy a szeletelő olyan tételeket mutat, amik már nincsenek az adatforrásban, de valamikor ot voltak, és hiába frissítesz, ezek ne tűnnek el.
A megoldás a PIVOT tábla beállításaiban (jobb klikk a pivot tábla bármely celláján) az „Adat” fülön található.
Az utolsó előtti opcióban (Mezőnként megőrzendő elemek száma) a legördülőt „Semennyi”-re kell állítani, majd frissíteni a kimutatást.A CSV mentés nálam működik a „Számla” lapról.
Az lényeges, hogy Text formátumú legyen az oszlop, de az nálad rendben van.
1. A lapról csináltam egy másolatot új munkafüzetbe
2. Mentés másként -> CSV (vesszővel tagolt)Ha magyar rendszered van, akkor pontosvesszővel tagolt CSV-t csinál (nekem)
A CSV mentés nem foglalkozik a Táblázattal és az elrejtett oszlopokkal sem.
Mindent kitesz, ami az A1, illetve a Ctrl-End-el elérhető utolsó cella között van.
Nálad ez nagyon máshol van, mint a táblázat vége, és van egy rakás rejtett oszlopod is, amik belekerültek a CSV-be.Imre
P.S
A két korábbi, függőben lévő Topic-ra kérlek, hogy válaszolj, hogy lezárhassam
(Külső hivatkozás és negatív számos kerekítés)Szia!
Azt írja az MS (és mások is), hogy amennyiben a hivatkozott külső fájl a hivatkozóval egy mappában vagy annak almappájában van, akkor a hivatkozás (annak tárolása) relatív lesz automatikusan, akkor is, ha a formulában abszolút cím látszik.
Ha a hivatkozó fájl mappáját és annak almappáját (ha abban van a hivatkozott) áthelyezed másik gépre, másik útvonalra, a frissítés során megtalálja a hivatkozott fájlt és átírja az útvonalat. (Amennyiben engedélyezed a szerkesztést).
Tesztelted ezt már?
A fájlnevet makró nélkül nem lehet dinamizálni (külön cellából venni), mert ahhoz az INDIREKT függvényt kellene használni, ami külső linkeknél nem működik zárt munkafüzetek esetén.
Imre
Különben valóban vannak problémák az informatikában a kerekítéssel, de az ott kezdődik, amikor két rendszer nem azonos kerekítési módszert használ, és összehasonlítva az eredményeket, nem értjük mi okozza a különbséget. Pláne ha tulajdonképpen ugyanaz a rendszer és azon belül két eszköz mutat eltérést.
Pl. az Excel a kereskedői módszert használja, a VBA (ami szintén Excel valahol) a Bankár módszert, és a PowerQuery is, ami szintén benne van az Excelben.
Na, ez már probléma szerintem is.
Hát, haragudhatsz az Excelre, de csak azt a szabályt követi, amit te magad is leírtál pár bejegyzéssel korábban az abszolút értékeknél.
Tágabban fogalmazva a többféle lehetséges kerekítési módszer közül a leginkább elterjedt un. Kereskedői kerekítést használja.
Tehát 0,5-től a nullától elfelé kerekít, alatta pedig a nulla felé.
Így tanultuk a suliban is, nem értem miért vagy most ennyire ki.
A felfelé kerekítés abszolút értékre értendő. Így működik oszt annyi.Te pedig szeretnéd (?), hogy … mit is szeretnél?
Hogy 1,1 től 1,9-ig minden 1 legyen?
Akkor írj egy formulát IF-el és ha a kerekítendő szám >=0, akkor ROUND, különben ROUNDDOWN.Részletesebben akár a Wikin is, de sok sok helyen.
https://hu.wikipedia.org/wiki/Kerek%C3%ADt%C3%A9sImre
-
SzerzőBejegyzés
