Hozzászólások
-
SzerzőBejegyzés
-
Pár éve készítettem egy videót, hogy PIVOT tábla felhasználásával hogyan kell UNPIVOT-ot csinálni.
Ezt találtad?
https://www.youtube.com/watch?v=ylb6hdzTfJgHa igen, akkor felfedezted az ExcelBázis youtube csatornáját. (Azaz az enyémet 🙂
Ennek módszernek az utolsó lépése statikus, tehát nem fog magától frissíteni.
Azóta már az Excelben megjelent a PowerQuery, amiben van beépített UNPIVOT, és ez viszont frissít, ha aza adatforrása táblázattá van alakítva.
Imre
Azt úgy kell megcsinálni, ahogy leírtam.
Ebben tettél előrelépést azzal, hogy a 0-1-2 lépést megcsináltad valahogy.
Innentől nehezedik a pálya. 🙂
A 3. lépésben azt kell megcsinálni, hogy a rossz/jó szövegeket összefűzni a kérdés sorszámával, így jelölve, hogy az adott jó vagy rossz válasz melyik kérdéshez tartozik.
Ezt a & operátorral lehet leggyorsabban megcsinálni.
Az utolsó, próba2 fájlban ez a G oszlopba kerül. =F oszlop& B oszlop
Ezzel az lesz, hogy pl. az G oszlopban az első kérdés soraiban ez lesz:
– rossz1
– jó1
– rossz1A 4. lépésben a H oszlopban azt kellene elérni, hogy egy kérdésen belül sorszámozzuk a jó/rosszx válaszokat.
Ez a legnehezebb pontja a bulinak.
Az a cél, hogy a sorszámozott csoportokon belül a rossz válaszok sorszámozását elölről kezdje mindig.
Erről készítettem egy videót pár éve.
https://www.youtube.com/watch?v=IwK60ek2WcE
Ha sikerül, akkor pl. a rossz1 első előfordulásához 1-et, a második előfordulásához 2-t fog adni, majd megy tovább a következő sorszámra.Az 5. lépésben az I oszlopba létrehozzuk az előző oszlopok felhasználásával a jó1, illetve a Rossz1..Rossz2…stb szövegeket csoportonként.
Ehhez megint csak össze kell fűzni, tehát:
=F oszlop & I oszlop
Eredményként az 1. csoportban ezek lesznek:
– rossz1
– jó1
– rossz2Ha idáig megvagy, akkor a tábla potenciálisan alkalmas arr, hogy az utolsó lépéseket PowerQuery-ben megcsináld.
Hát, én nem látom közelinek az áttörést. 🙂
Látom, hogy csináltál egy PIVOT táblát, de abba nem tetted bele az eredeti tábla fejlécét, így az első adatsort hiszi fejlécnek.PIVOT-al amegkapható eredmény nem az lesz, minta mit az előző fájlban megadtál mintaként, de talán használható úgy is, ezt te tudod.
A PIVOT táblát táblázatos elrendezésbe a Tervezés szalag Elrendezés menüjében lehet tenni.
OK, akkor függvénnyel nem fog menni.
Marad a makró vagy a PowerQuery.Én utóbbiban csinálnám.
UNPIVOT-olni kell az eredeti táblát, így kapsz egy 3 oszlopos táblát:
– Termék
– Ország
– Ár.Az oszlopokat már tudod szűrni.
Imre
Szűrsz a zödekre és a megmaradó sorok első cellájába beírod, hogy „jó”
Ezután még mindig szűrt állapotban lehúzod az aljáig.Ha a szűrést ezután feloldod, akkor csak a zöld sorokban lesz a „jó” szöveg.
Imre
Szia!
Egy kicsit mókolni kellett vele, de sikerült kitalálnom egy folyamatot, amivel az általad küldött minta alapján gyorsan megoldható.
A végső, lényeges lépéshez kellett a PowerQuery is.Kb így néz ki a folyamat:
0. Cellaegyesítést megszüntetni, majd kitölteni az üreseket a felette lévővel, az „A,B,C..” oszlopot törölni
1. Zöldekre szűrés és új oszlopba lehúzni, hogy „jó”
2. szűrni az új oszlop szerint az üresekre, és lehúzni oda, hogy „rossz”
3. Egy új oszlopba Összefűzni a sorszám és az előzőleg kitöltött oszlopot
4. Újabb oszlopban képlettel sorszámozni csoportonklént elölről kezdve a jó és rossz értékeket
5. Újabb oszlopban összefűzni ajó/rossz oszlopot az előzőleg megcsinált sorszámmal csak a rossz sorokban
6. PowerQuery-be importálni az eddigieket
7. Az utoljára megcsinált oszlop forgatása fejlécbe
8. Sorok rendezése
9. Jó válaszok oszlopának előre mozgatása
10 betöltésHa a valós adatfdorrás ugyanilyen, akkor 5-10 perc alatt megvan.
Imre
Szia!
Jól értem, hogy eredményként egy sort szeretnél, amiben két oszlop van?
Egyik a a kiválasztott termék, a másik a kiválasztott partner?
Sorokra szűrni OK, oszlopokra szűrni meg alapból nem nagyon lehet, de szerintem lenne menekülő út a makró előtt, ha esetleg 365 verziót használsz. Ott nemrég megjelentek újabb függvények, amivel meg lehet ezt csinálni.Ja, és tölts fel valami mintát is, hogy ne nekünk kelljen előállítani!
Köszi,
Imre
Szia!
Ezt a munkalap védelmével lehet elérni. A diagramoknak is van zárolt tulajdonsága, ami alapból be van kapcsolva
Ha a cellákat különben nem akarod védeni, akkor azoknak a zárolt tulajdonságát kikapcsolhatod.Imre
1. Kijelölöd a tartományt
2. begépeled a szöveget, ami az első cellában fog megjelenni
3. Ctrl+Enter-rel zárod leImre
Szia!
Táblázattá alakított tartomány fejléce nem tartalmazhat képletet, csak értéket.
Ez van.Lehetséges barkácsolások
– A táblázat fejléce felett egy árnyék fejlécet használsz, amiben lehetnek képletek, és a rendes fejléc sort elrejted.
A táblázat saját szalagján ezt meg lehet csinálni, csak egy pipa
Ennek csak optikai jelentősége van, nem fog fejlécként viselkedni, nem lehet autoszűrőt használni pl.– Eseménykezelő makró módosítja a fejléc celláit és értékként írja be
Ez szerintem túl „sokba kerül”. PL. meg kell tanulni, megcsinálni, kipróbálni, stb…– Nem használsz táblázatot.
Imre
Boldogság ! 🙂
Én az irodai gépemen néztem, amin történetesen 2019-es Excel van.
Most megnéztem, a forrásban xlsm fájlok vannak (a többi mellett)Nem számoltam meg, hogy összesen hány fájl van a mapparendszerben, de az biztos, hogy 97-et látott.
Ebből 43 db xlsm. (van közöttük egy csomó hullámjellel kezdődő nevű)Én nem tudok többet hozzátenni ehhez attól tartok…
Szia!
Csatlakoztam a fő mappához és nekem 97 sort olvasott be, azaz ennyi fájlt talált.
Nem tudok limitrőlAlapból minden fájlt beolvas, utána, az editorban tudod szűrni, hogy csak Excel fájlokat dolgozzon fel.
Nem tudom, hogy mi történik nálad.Másik gépen próbáltad?
Imre
Szia!
Én nem tudok ilyen limitről, és egy gyors keresgélés után a neten sem találtam.
Olyanról viszont tudok, hogy az útvonal egy fájlhoz nem lehet akármilyen hosszú.
Olyasmi rémlik, hogy Windows-ban 256 vagy 260 karakter a max.Nem lehet, hogy ez a baj?
Ha újat adsz valamelyik mappába, akkor azt hozzáfűzi az eredményhez?
Az összefűzést egyszer jól megcsinálja, de nem frissül, ha új fájlt teszel valamelyik mappába?
Szia!
Lehet erre VBA programot írni persze.
De én inkább a PowerQuery-t javaslom. Sokkal hamarabb megvan a fejlesztés ezzel.
Ismered?Imre
Szia!
Gondolom átkattintással hoztad létre a hivatkozást.
Amit bemásoltál, akkor van, ha a hivatkozott munkafüzet meg van nyitva.
Ez így elvileg nem adhat #HIV hibaüzenetet, ha a hivatkozott fájlt nem nevezted át közben.Kérdéses, hogy a hivatkozott fájl (vagy akár mindkét fájl) hol van fizikailag? A saját gépeden, vagy a hálózaton esetleg?
Ha hálózaton, akkor ez okozhat problémát.Javasolnék egy tesztet, hogy mi van akkor, ha mindkét fájl a te gépeden van. Szerintem akkor nem lesz hiba.
Ha esetleg ez lenne a helyzet, akkor a hellyel lehet baj. Én még nem találkoztam ezzel, de amikor rákerestem, akkor találtam egy ilyet, ami pont ate problémádat írja le az elején.
https://superuser.com/questions/629514/excel-linked-data-formula-turns-to-ref-upon-opening-workbookMegoldásként a Biztonsági központban javasolnak beállítást
Imre
Szia!
Csak sejtem, hogy mit szeretnél.
Az új oszlop jelenjen meg a másik lapon is, és abban legyenek benne a hivatkozások az előző lap most beszúrt oszlopának celláira, miközben a többi hivatkozás is működik.Ha ezt akarod, akkor ez nem fog menni olyan egyszerűen.
Mivel az oszlop beszúrás arrébb mozgatja az eredeti oszlopokat, a rájuk történő hivatkozások is megváltoznak. Ami eddig =MUNKA1!C1 volt, az innentől MUNKA1!D1 lesz. Az új C oszlopra pedig nem hivatkozik semmi. Tehát az Excel pont hogy leköveti, amit csináltál.Ha jól értem, akkor te azt szeretnéd, hogy a Munka2 C1-ben lévő képlet mindig a MUNKA1 C1-re mutasson, akkor is, ha MUNKA1-re beszúrsz a C oszlop elé egy új oszlopot.
Ilyen formulát ugyan lehetséges lenne készíteni (azt hiszem), de használni kellene benne az INDIREKT függvényt, ami 1000×1000 cellán fájdalmasan lassú lenne valószínűleg.
Ezért jobb híján, első lépésként használd Delila tanácsát, mindkét lapra szúrd be ugyanoda az oszlopot, majd a második munkalapon tedd bele a képletet, ami az első lap üres oszlopának celláit címzi.
Imre
Sziasztok!
Ez valójában egy szűrési feladat.
Ha 365-öt használsz, akkor a SZŰRŐ függvény lehet a barátod.
Ha korábbi verziót, akkor marad Delila makrója.Imre
Hoppá 🙂
Néhány gondolat:Én úgy csinálnám, hogy egy külön makrós fájlba tenném a vezérlést.
Ez azt jelenti, hogy ott van a makró és a munkatársak törzs adatai az email címükkel együtt.
Egy másik fájlban pedig a kérdőív sablon formában (üresen)A makró az elején megnyitja a kérdőív sablont, majd egy ciklussal végigmegy a munkatársi törzstáblán.
Minden cikluskörben
– kitölti a kérdőív elejét az adatokkal
– elmenti
– elküld egy levelet az aktuális munkatársnak, amibe becsatolja az elmentett kérdőívetAz email szövegét is előre le lehet tenni egy cellába a makrós munkafüzetben, de bele is lehet drótozni a kódba.
Javaslom, hogy sima text legyen, ne HTML, akkor kevesebb gondod lesz.
A megszólítást meg veheti a munkatársi törzstáblából megint.Hajrá!
Imre
Szia!
Ez csak makróval automatizálható.
Tudsz programozni Excelben?Imre
Próbáld ki 🙂
Szia!
Visszatöltöttem a fájlt.
Megoldás
36-37. sorban lévő képletek.Magyarázat
Az Excel nem dolgozik negatív dátumokkal. Esetleg úgy, ha átállítod 1904-es dátum formátumra, amit nem javaslok.De ha a perc értékeket átalakítod időre, majd szövegre, akkor jó lehet.
A) Egy perc értéket idő értékre (a megfelelő törtre) lehet alakítani, ha elosztjuk 24-el, majd 60-al.
Ezt szöveggé alakítjuk a SZÖVEG függvénnyel. Ha negatív a perc, akkor az abszolút értékével dolgozunk, és eléfűzzük a mínusz jelet.
=HA(B34>=0;SZÖVEG(B34/24/60;"óó:pp");"-"&SZÖVEG(ABS(B34)/24/60;"óó:pp"))Másik megoldás, amit te próbáltál, hogy az osztogatás helyett az IDŐ függvényt használod, és a többi ugyanaz.
Itt az a trükk, hogy az IDŐ függvény perc része lehet nagyobb, mint 60.
=HA(B34>=0;SZÖVEG(IDŐ(0;B34;0);"óó:pp");"-"&SZÖVEG(IDŐ(0;ABS(B34);0);"óó:pp"))Imre
Attachments:
You must be logged in to view attached files.Örülünk 🙂
Szia!
Megpróbálhatod a PADLÓ függvényt. Ez a megadott érték legközelebbi többszörösére kerekít lefelé.
Te a legközelebbi egész órára akarod lefelé, ha jól gondolom. Az egy óra az valójában az Excelben 1/24.Mondjuk A1-ben van egy dátum+idő érték. B1-be a képlet:
=PADLÓ(A1;1/24)Imre
Delila lapján D4 képlete
=DÁTUM(JOBB(B4;4)*1;KÖZÉP(B4;4;2)*1;BAL(B4;2)*1)+IDŐÉRTÉK(C4)Imre
Szia!
Többféleképpen is meg lehet csinálni, attól függ.
Első és legegyszerűbb, ha O365-öt használsz, mert akkor van egy SZÖVEGÖSSZEFŰZÉS függvény, ami segít.
=SZÖVEGÖSSZEFŰZÉS(elválasztójel; üreseket kihagyja; Tartomány)
Ez a tartományban lévő külön cellákat egybefűzi, és közéjük az elején megadott elválasztójelet teszi.
A második paraméter legyen mindig 1-es. Pl.:
=SZÖVEGÖSSZEFŰZÉS(" "; 1; D2:D20)
Ez a D2:D20-ban lévő cellákat összefűzi szóköz elválasztóval.A te problémádra a megoldás az, hogy az összefűző karakter az újsor vezérlő legyen, ami a 10-es kódú. Ezt csak így lehet:
=SZÖVEGÖSSZEFŰZÉS(KARAKTER(10);1; D2:D20)Ezután még be kell kapcsolni a cellára a „Sortöréssel több sorba” (Wrap text) funkciót.
Ha nincs O365, akkor csak egyesével lehet összefűzni, ami akkor jó, ha mindig ugyanannyit kell összefűzni, és kevésbé jó, ha ez soronként változik:
=E15 & KARAKTER(10) & E16 & KARAKTER(10) & E17 & KARAKTER(10) & E18Imre
Az Excel sok esetben automatikus típuskonverziót végez.
Ebben a példában két helyen is felfedezhető.
1. Ha a DÁTUM fv-en belül nme szorzol 1-el, akkor is működik, pedig nem kellene neki
2. Az időt, ami szöveg, simán hozzáadta hibátlanul a dátumhoz, pedig nem kellene neki.Ezek un. implicit átalakítások. Lehet rájuk támaszkodni, és valószínűleg mindig működni fognak, de:
A best practice az, hogy legyünk annyira explicitek, amennyire csak lehetünk.
Tehát a típusokat alakítsuk át mi, és utána használjuk tovább.
A szöveges időből igazi időt lehet csinálni az IDŐ függvénnyel, ahogy csináltad is, vagy az IDŐÉRTÉK függvénnyel.= IDŐÉRTÉK"10:42")A szöveges időből igazi időt csinál.
Tehát Delila képletének a végén ezt még meg lehet tenni, ha igazán szépen szertnéd csinálni:=DÁTUM(JOBB(B4;4)*1;KÖZÉP(B4;4;2)*1;BAL(B4;2)*1)+IDŐÉRTÉK(C4)Imre
Bocsánat, hogy beleszólok, de nem lesz jó.
Nem az a feladat, hogy úgy nézzen ki, mintha jó lenne, hanem, hogy valóban órában legyen a különbség.Ehhez az az elméleti ismeret kell, hogy az Excel a dátumokat és az időértékeket számként tárolja.
Ezért jó az a módszer, amikor összeadod a dátumot és az időt, akkor egy dátum+idő számot kapsz, amnke az egész résza a nap, tört rész az idő.
Ez akkor is igaz, ha a kettő különbségét nézzük.
Ha valódi óra, perc, stb.. értékekkel akarsz számolni, akkor fel kell használni azt az ismeretet, hogy:
– 1 nap = 1 egész
– 1 óra = 1/24
– 1 perc = 1/20/60
– stb…Tehát ha egy idő értéket nem törtként, hanem valóban órában akarsz számolni, mert pl. összegezni akarod vagy tovább akarsz számolni vele pl. óradíj*ledolgozott óra, akkor az idő értéket meg kell szorozni 24-el.
A konkrét példa esetén Delila másolatán az „I” oszlopban a helyes képlet.
=(H5-D5)*24És persze a formátumot átalános-ra vagy szám-ra kel tenni.
Törtek jönnek ki, amiket függvénnyel kerekíthetsz, vagy úgy is hagyhatsz.Imre
Szia!
A munkalap onChange eseményéhez lehet megírni a zárolási vagy feloldási műveletet.
Mit tudsz az Excel makrók eseménykezeléséről?Kicsit nézz utána, most csak ennyi időm volt.
Imre
Értem, látom!
Hajrá! 🙂
Szia!
Nem, az Excel nem tud ilyet (sem)
Az ebbe a témába kapcsolódó minden korábbi és jövőbeni kérdésre az lesz a válasz, hogy esetleg eseménykezelő makróval lehet valamit próbálni, de nem nagyon javaslom, nem megbízható megoldás.
Tudomásul kell venni, hogy sor vagy oszlop szintű finomhangolható jogosultságokat nem tartalmaz.Azt még el tudom képzelni, hogy a munkalaphoz csak felhasználói űrlapon keresztül lehessen hozzáférni, és ennek az űrlapnak az eseménykezelői nyitják és zárják a háttérben a munkalapvédelmet. Mondjuk a kitöltendő cellán egy jobb klikkre feljön egy űrlap, és elsőként ellenőrzi, hogy a megklikkelt cella üres-e. ha nem, akkor ezt közli, és nem enged semmit.
Ha az illető cella üres, akkor feloldja a védelmet, az űrlapba beírt értéket beteszi a cellába, majd ráteszi a védelmet, bezárja az űrlapot.
Csak egy ötlet, ami újabb VBA világot nyit meg.
Nézz utána!Imre
A Masterhez szerintem igen.
Szia!
Ezt munkalap eseménykezeléssel lehet megcsinálni, azaz makrós megoldás van csak.
A munkalap CHANGE eseményének kezelésekor megkapod, hogy melyik cellát változtatták.
Ha ez az A1, és a B1 még üres, akkor a B1-be beírja az aktuális időt, különben nem történik semmi.Ez nem védi a cellákat, tehát a B1 bárki által módosítható vagy törölhető lesz.
Csak A1 és B1 írásvédetté tétele Excel szinten elég macerás, mert munkalaphoz lehet védelmet rendelni, ami az aktuálisan zárolt cellákat védi. De remélem ezt ismered.
Ugyanezzel az eseménykezelővel viszont megoldható, hogy ha a B1-et változtatják, vagy törlik, akkor írja vissza az eredeti értékét, majd kérjen esetleg egy jelszót, ha mégis módosítani akarná.
Csak érzékeltetni szerettem volna a lehetőségeket.
Az utánanézéshez és a megvalósításhoz a te idődet kell használnod, hacsak nem „delila” megcsinálja neked, nekem most nincs több időm. Az eddigi kérdéseid alapján azt látom, hogy a makró téma nem ismeretlen a számodra.Imre
Szia!
A 365 webes verziójában már benne van ez a lehetőség, és 1-2 hónapon belül meg fog jelenni a desktop-ban is.
Nem emlékszem, hogy milyen verziód van.Különben elsősorban makrós megoldások vannak, amire adtam egy verziót egy korábbi fórumtémához.
https://excel-bazis.hu/forumok/topic/szures-begepelt-adat-alapjanImre
Mi is örülünk! 🙂
Szia!
Van az Excelben egy titkos függvény erre. A titkos azt jelenti, hogy nincs benne a hivatalos függvényjegyzékben.
Magyar neve: DÁTUMTÓLIG
Angol neve: DATEDIFHasználata:
=DÁTUMTÓLIG(kisebb dátum, nagyobb dátum, mértékegység)
A mértékegység lehetséges értékei csak angolul adhatók meg magyar esetben is:
„y” = évben lesz a különbség
„m” = hónapban lesz a különbség
„d” = napban lesz a különbség.Szóval neked 3x kell a függvényt használni ugyanarra a két dátumra (amik gondolom külön cellákban vannak), 3 féle mértékegységgel.
Imre
Persze, hogy működik 🙂
Szia!
Az első képletben (zöld) ezt kell írni: $F$3:F3
Azaz a tartomány első celláját rögzíted (mindkét része előtt van dollárjel), a másodikat engeded változni a lehúzáskor mindig az aktuális sorig.Imre
🙂 Kérdezz csak bátran!
Remélem nem mondod komolyan, hogy ha képlet volt, akkor maradjon képlet.
Az érték beillesztésnek a lényege, hogy a képleteket értékké alakítja.
Persze másik lényege, hogy megtartja a cél formátumot.Amúgy nálam lefut az a kód, nem tudom, h nálad miért nem, de szerintem felesleges erre makrót használni
Én például azt csináltam, hogy az érték beillesztés gombot kitettem az eszköztárra magamnak, így egy kattintás az értékbeillesztés.
1. Fájl -> Beállítások -> Gyorselérési eszköztár
2.Középső oszlopban a gyakori parancsok helyett a menüszalagon nem szereplő parancsok
3. Megkeresed az Értékek beillesztése sort és nyomsz rajta egy dupla klikket.
4. Megjelenik a gomb az eszköztáron felül az utolsó gomb után
5. EnnyiHa mindenképpen billentyűkombinációt szeretnél hozzá, akkor az eszköztáron ezt a gombot tedd az első nyolcba.
Például ha ez a hetedik, akkor Így ALT-7-el működni fog. Ez már majdnem a Ctrl-É 🙂Imre
-
A hozzászólás módosításra került: 1 year, 3 months telt el-
horvimi.
Ha jól értem, akkor a már ott lévő képleteket szeretnéd értékre cserélni.
Sub PasteasValue() Selection.Formula = Selection.Value End SubNyilván előtte ki kell jelölnöd az oszlopot.
Imre
Látom, értem, köszönöm.
Speciális használati eset.
Azért készíts gyakran biztonsági mentéseket.Ha lenne még ilyen, akkor arra is figyelj, hogy ha egy munkalapot másolsz másik munkafüzetbe, akkor azok a képletek, amik másik MUNKALAPRA hivatkoznak, vissza fognak hivatkozni az eredeti munkafüzetbe.
Emiatt indításkor frissítési kérdés jelenhet meg. Ellenőrizd az Adatok -> csatolások szerkesztése menüt, hogy aktív-e és mik vannak benne.Imre
Szia!
Nagyon kíváncsi lennék, hogy mire használod a szövegdobozokat.
Van mintád? Esetleg olyan, ami a problémás dobozokból is tartalmaz párat?Ezt a mondatot nem tudom értelmezni:
„Nézet menüben egy +Ablakos duplikációval emeltem be cellákat, ez lehet-a baj? ”Sejtem, hogy a Nézet menüben egy új ablakot kértél ugyanarra a munkafüzetre, de a cellák beemelését nem tudom elképzelni.
Copy/Paste, vagy hivatkozás? Vagy?Esetleg próbáld meg a kijelöléspanelt előhozni, és azzal megnézni, hogy tudsz-e velük valamit kezdeni?
Kezdőlap -> Keresés és kijelölés -> Utolsó menüpont VAGY
Lapelrendezés -> KijelöléspanelItt különben át is nevezheted őket (dupla klikk) sorrendet változtathatsz (vontatás), láthatóságukat beállíthatod, kijelölheted, ha nem látszik vagy nem találod, mert lefedi egy másik.
Bocs, lehet, hogy ismerted.Aztán meg lehet próbálni makróval megfogni és mondjuk törölni őket.
Én csak vizualizációkhoz használok ilyesmit, néha megjegyzésként, de különben az objektumok tömeges használata sok probléma forrása lehet és a fájlméretet is jelentősen növelheti.
Imre
Én azt csinálnám, hogy a képletet is makróval írnám, ugyanazzal, ami az F1-F2-F3-ban változtat, majd a makró értékké alakítaná az eredményeket. Így a képletet csak a makró ismeri.
Ez elég kommon megoldás.Imre
Ja, igen.
Csak írtam, nem próbáltam.Nagyon szívesen 🙂
Próbáld ezt:
=SZŰRŐ(Táblázat1;BAL(Táblázat1[foksz];2)="12");"nincs")Imre
Az irányított szűrő lehetséges megoldás, de az is statikus. Ha változtatsz a feltételen, akkor újra kell futtatnod.
De mivel mákod van, és 365 van neked, belekóstolhatsz az Excel új világába azzal, hogy használsz egy új függvényt.Ezt nézd meg:
https://excel-bazis.hu/tutorial/az-excel-szuro-fuggvenyeImre
Nagyon örülök! 🙂
Szia!
Egy komlett Excel tanfolyamot fogsz itt lenyomni ha jól látom 🙂
Különben ezen tényleg érdemes lenne elgondolkoznod, hogy a céget rá tudod-e venni.Szóval az összes kereső függvény (HOL.VAN, FKERES, XKERES…) csak az első találatig megy és ott megáll, hiába van több megfelelő érték.
A te problémádat szűréssel kell megcsinálni.
hogy a szűrés dinamikus legyen, azaz egy cellába írt pl. 12* után gyűjtse ki a megfelelőket, csak akkor lehetséges natív Excel eszközzel, ha Office 365-öt használsz.Ha nem, akkor lehet makróval automatizálni egy autofiltert például.
Szóval ezt nézd meg!
Imre
Teljesen hibás, ahogy próbálod.
A logikai függvények összetett használata mondjuk nem egyszerű.De látok még egy problémát, miszerint te csak azt az esetet fogalmaztad meg, amikor a bal oldali figura nyert, viszont a szettlistában a jobb oldali is nyert 3 szettet, így a meccset is.
Szóval a logika egy kicsit bonyolultabb szerintem.
Szabad szöveggel:(H6=11 és I6 <10) vagy (I6=11 és H6 <10) vagy (H6>10 és ABS(H6-I6)=2) vagy (I6>10 és ABS(I6-H6)=2)Utóbbi esetleg egyszerűsíthető:
(H6>10 vagy I6>10) és ABS(H6-I6)=2Először vegyük csak az első szettet, amit a H3-ba írt képlettel ellenőrzünk:
=HA(VAGY(VAGY(ÉS(H6=11;I6<10);ÉS(I6=11;H6<10));ÉS(VAGY(H6>10;I6>10);ABS(H6-I6)=2));0;1)Most épp magyaron áll az Excelem, nézd el nekem 🙂 Az angolban neked angol függvényneveket és a pontosvessző helyet vesszőt kell használnod.
Szóval a VAGY (OR) függvényt ugyanúgy kel használni, mint az AND-et. Akárhány paramétere lehet, bármelyik IGAZ, IGAZ-at fog adni.
Ha jól megnézed, akkor azt látod, hogy a két AND-et betettem egy OR-ba, aztán van ott még egy OR és azt nézem az IF-el.
A szabad szöveges egyszerűsített verzióját írtam le függvényekkel.
Jól megdolgoztattál így az éjszaka 🙂Ha sikerül ezt nálad beüzemelni, akkor én a helyedben azt csinálnám, hogy ugyanezt a képletet egy kis módosítással beírnám a
J3, L3, N3, P3 cellákba.Így, ha bármelyik nem OK, akkor ott van valami gond.
Ha mégis 1 cellában szeretnéd látni az 5 szett ellenőrzését, akkor egy meglehetősen bonyolult kifejezést kell készítened.
VAGY
Változtatsz a logikai vizsgálat kimenetén és ha egy szett rendben, akkor a kimenet legyen nulla, különben pedig 1-es.
Én most ezt a képletet küldöm.
Ezután írd le az 5 ellenőrzést összeadva. Ha mind jó, akkor nulla lesz a vége, különben pedig a hibás szettek számát kapod.A szettenkénti verziót megoldással visszatöltöm.
Imre
-
A hozzászólás módosításra került: 1 year, 3 months telt el-
Attachments:
You must be logged in to view attached files.Szia!
Gondolom a képletet az F5-be írod…
Egy kicsit kicsit kacifántosan írtad le, de azt hiszem értem.
Viszont szeretném látni legalább a egyik próbálkozásodat, lássam, hol rontod el.
Bekuldenéd a képletet?
Ha bemasoltad, utána jelod ki, és nyomd meg a CODE gombot fent!Köszi
Imre
Ez is gyönyörű megoldás!
Köszönjük!Csaba, arra kérlek, hogy ha nem annyira triviális függvényt vagy képletet használsz, akkor fűzz hozzá némi magyarázatot!
A célom az egész fórummal, hogy ne „csak” megoldásokat adjunk, amit a kérdező felhasznál vagy implementál, de igazából nem tudja, hogy mi történik.
A SZÖVEG függvényre gondolok itt most konkrétan. Ez nem annyira ismert. A PIVOT nem szorul magyarázatra, az több oldal lenne, ha nincs meg valakinek.Nem veszem el előled a lehetőséget 🙂
Köszi,
Imre
Ha belegondolsz, akkor egyértelmű. Az egyik összeadja a 11*-osokat, a másik a 12*-osokat.
Most még jobban örülünk 🙂
Én a képernyőképen az E3-ban látom a dátumot.
Kedves Erika!
Nem tudom, hogy hogyan próbáltál többet megtudni a függvényekről, de a lényeg, hogy több feltétel esetén a SZUMHATÖBB és hasonlók a feltételeket ÉS kapcsolatba hozzák, azaz minden feltételnek megfelelő sorokon csinálják a műveletet.
A te esetedben a leírt képlet összesíti az A oszlopot azokra a rekordokra, ahol a B oszlopban 11* ÉS ugyanakkor 12* van. Ez soha nem fog teljesülni. Neked VAGY kapcsolat kell. 11* vagy 12*
Ezt úgy kell, hogy SZUMHA(„11*”….) + SZUMHA(„12*”…)
Tehát két SZUMHA-t vagy egy feltételes SZUMHATÖBB-et összeadsz.Imre
Akkor örülünk! 🙂
Szia!
A SZUMHA tudja a joker karakteres feltételeket, tehát a csillagot is, de csak akkor, ha a feltételes oszlop szöveges típusú.
Neked viszont az első oszlopban lévő értékek valójában számok.Lényeges kérdés, hogy honnan és hogyan szerzed be az első 2 oszlopot (gondolom több oszlop is van), de valahol elromlik az adattípus, ha egyáltalán eredetileg szöveges volt valahol.
Utólag meggyógyítani úgy tudod, hogy az A oszlop típusát szövegessé teszed. Ez csak úgy oldható meg utólag, hogy:
1. Kijelölöd
2. Szövegből oszlopok
3. Elmész 2x tovább-al a 3.lépéshez és ott Szöveg típust választasz -> Befejezés
Az adataid átugranak balra és zöld háromszögek lesznek a bal felső sarokbanEzután, ha egy külön tartományba leírod egymás alá az összegzendő kategóriákat aposztoffal kezdve (’11*, ’12*, ’13*, stb…, akkor megírhatod mellé a SZUMHA függvényt és lehúzhatod, csak ne felejtsd el a tartomány rögzítést)
Én az E1-be írtam a 11*-ot, és a formula:=SZUMHA($A$1:$A$220;E1;$B$1:$B$220)Imre
2023-01-22-11:04 Hozzászólás: Munkafüzet megatározott oszlopainak exportálsa új excel munkalapra #9535Nem kell korábbi bejegyzést keresned, most egy új kérdést tettél fel. Én csak arra utaltam,, hogy ha akkor makróztál, akkor biztosan most is makrózni akarsz, hiszen az oszlopok átmásolása egy több lépéses folyamat.
Ennek vázlatos megoldását adtam meg.Imre
2023-01-21-22:33 Hozzászólás: Munkafüzet megatározott oszlopainak exportálsa új excel munkalapra #9533Szia!
Az eddig indított témáid alapján (4 éve 🙂 ), azt tippelem, hogy makrós megoldást keresel.
Számomra nem használod egyértelműen a kifejezéseket: Munkalap, munkafüzet, Mappa
Ahogy értelmezem a feladatot:
Egy munkafüzet egyik munkalapjáról több oszlopot, (összefüggő vagy nem összefüggő) át szeretnél másolni egy másik munkafüzet munkalapjára úgy, hogy az eredményben ne legyenek képletek.Két eset lehetséges:
Ha nem összefüggőek az oszlopok, akkor csak annyi a megoldás, hogy kijelölöd Ctrl nyomása mellett az oszlopokat, majd sima Ctrl+C majd átváltas a célhelyre és Ctrl+V. Az eredmények automatikusan értékek lesznek akkor is, ha a forrásban képletek voltak.Ha az oszlopok összefüggőek, akkor az oszlopok másolása után a célhelyre érték beillesztést csinálsz és kész.
Emiatt tulajdonképpen elég annyit megjegyezni, hogy érték beillesztés, mert ez minkét esetben működik.Ezt vedd fel rögzítővel, módosítsd, hogy az oszlop kijelölések igazodjanak a forrás tartomány hosszához, és kész.
Ha az oszlopok mindig mások, amiket másolni kell, vagy sok oszlopról van szó, akkor az is lehetséges, hogy egy tartományba, vagy a program elejébe leírod, hogy mely oszlopokat kell másolni, és ezt behelyettesíted a programba.
Ebbe az esetben már ciklust kell írni.Imre
Én azért vetettem fel ezt a megoldást, mert sokkal gyorsabb, mint ha soronként iterálsz végig 44 ezerszer.
Tehát Excel szinten oldom meg a problémát, de makróval írom meg a képletet.
A FORMULAR1C1 egy Excel képletet ír a kijelölésbe.
He azt érted, akkor nem kérdezhetsz olyat, hogy hivatkozhat-e saját magára (pl. RC), mert Excelben ilyen nincs (körkörös hivatkozás)
Én megadtam neked azt a képletet, ami pont azt csinálja, amit most újra megkérdeztél:
„Nincs arra valami megoldás, hogy a p oszlopban, ha talál kötőjelet azt cserélje le az o első 3 karakterére?”
=HA(BAL(P2;1)="-";HELYETTE(P2;"-";BAL(O2;3)))
Ha az a probléma, hogy makró közben új segédoszlopok keletkeznek, akkor másképp kel megoldanod.
Én azt javasoltam, hogy az oszlopokra bontás után:
1. Az első oszlop gondolom változatlan marad
2. A második-harmadik-negyedik lehet potenciálisan olyan, hogy kötőjellel kezdődik, ezért erre a 3 oszlopra csinálsz 3 ilyen IF-es számítást 3 újabb oszlopba. (javaslom értékké alakítani
3. Az első, változatlan és az utolsó 3 oszlopot átmásolod az első lapra, a helyére
4. Kitakarítod az átmeneti oszlopokat.A jelenlegi tudásoddal ezt meg tudod csinálni szerintem.
Ha ez nem felel meg, vagy meg akarsz küzdeni egy programozás szempontjából nagyobb kihívással, akkor memóriatömböket kell használnod.
1. Eszerint az eredeti M oszlopot beolvasod egy tömbbe, majd egy ciklusban szétdobod 4 külön tömbbe
2. a 4 külön tömböt feldolgozod egyesével és átalakítod vagy sem a tételeit az ismert szabályok szerint
3. A 4 tömböt visszaírod az első lapra, a helyére.Ha nem foglalkoztál még tömbökkel, szerintem ez nagy kihívás elsőre.
A FORMULA2R1C1 akkor van, amikor a rögzítővel nem egyszerű képletet, hanem tömbképletet veszel fel.
Ez jelen esetben most nem releváns.Ha érdekel, nézz utána a tömböknek, tömbfüggvényeknek, tömbképleteknek az Excelben.
Nem ugyanaz, mint a VBA memóriatömbök, de hasonló.
Ha megoldást akarsz, és az a prioritás, akkor azzal főzz, amid már megvan.
Ha tanulni akarsz, akkor menj a nehezebb irányba.Imre
A textsplit-et most felejtsd el, ha már van működő TextToColumns, csak érdekességként írtam.
A formula kérdésedet nem igazán értem, de sejtem.Ha képletet akarunk makróval írni egy oszlopba, akkor két lehetőség van:
Egyik, hogy beírod a z első cellába, és utána dupla klikkel a sarkán, lemásolod az aljáig.
Ha ezt felveszed makró rögzítővel, kapsz egy eredményt.Másik, hogy az oszlopot előre kijelölöd, és abba teszed be a képletet (formulát)
Ebbe az esetben elég azt csinálni, hogy csak a képletet veszed fel. Ha beírtad, rögtön megállítod a rögzítőt.
Ha ENTER-rel zártad le a beírást, akkor valami ilyesmi lesz:
Sub valami
Activecell.FORMULAR1C1 = „=és itt lesz a képlet R1C1 formátumban, angol függvénynevekkel”
End SubKonkrétan, a te esetedre:
1. A TextTo Colomns az „O” oszloptól megcsinálja a bontást, ami ha jól emlékszem 4 oszlop, tehát O-P-Q-R
2. Tehát az S oszlopba kell a képlet, amit megadtam (Ha….), ami lekezeli a kötőlejes tételeket
3. Próbáld ki!
4. Ha működik, akkor vedd fel rögzítővel: Jelöld ki az S2 cellát, írd bele a képletet, ENTER
5. a felvett kódot alakítsd át, hogy az egész oszlopra megcsinálja:
`Range(„S2:S” & usor).formulaR1C1 = amit felvett
Range(„S2:S” & usor).formula = Range(„S2:S” & usor).value ‘értékké alakítás`
6. Próbáld ki!
7. Ha működik, akkor másolással megcsinálhatod a többi oszlopra (T, U, V oszlopok?)
8. a 4 oszlopot átmásolhatod az első lapra.Imre
Én már nem követem a folyamatot, elvagytok nélkülem is 🙂
De tennék bele inputot.
Ha jól értem, az egyes részek az M oszlopban alulvonással vannak most elválasztva.
Ha jól értem, bármelyik részben lehet kötőjel.
Elég lényeges kérdés, hogy több részben is lehet kötőjel, és akkor többször kell a helyettesítést megcsinálni?
Remélem az első tagban nem lehet 🙂Ha ez igaz, akkor alaposan el kell gondolkodni a dolgon, hogy el lehet-e kerülni a soronkénti feldolgozást, ami a kezdeti sebesség problémát okozta, azaz valami oszlop formula. Vagy az egészet memóriatöbmbbe kell tenni, ott feldolgozni, és visszaírni.
Másik lehetőség, hogy előbb oszlopokra bontod, és a kapott 4(?) oszlop mellé ugyanannyi kalkulált oszlopot csinálsz, ami megvizsgálja, hogy tartalmaz-e kötőjelet, és ha igen, megcsinálja a cserét. Ebből talán 3 is elég lehet, ha kijelenthető, hogy az első oszlopban nem lehet kötőjel.
A kapott oszlopokat végül átmásolod értékként az előző lapra, a helyére, utána takarítasz.
A „TextToColumns” helyett ajánlom figyelmedbe az O365 „SZÖVEGFELOSZTÁS” (TEXTSPLIT) függvényét, ami az egész M oszlopot az alulvonás mentén oszlopokra bont. Például.:
=SZÖVEGFELOSZTÁS(M2;"_")
Ezt beírva (makróval) az M oszlop után valahová, mondjuk O2-be, kapsz egy oszlopokra bontott tömböt az M2 celláról
Ha az egész oszlopba akarod betenni (44e sor?), akkor előbb ki kell jelölni, és utána betenni a formulát. Javaslom felvenni, mert a tömbképletek formula makrója kicsit eltér a hagyományos formuláktól. Nekem ez lett:
Selection.Formula2R1C1 = "=TEXTSPLIT(RC[-1],""_"")"
A Formula2 a lényeg.
Különben pedig, ha a TextToColumns már működik, akkor ezzel nem biztos, hogy kell szenvedni.Végül ezen tömb után lehet megcsinálni a kötőjelek cseréjét formulával oszloponként.
Nekem 3 oszlopot csinált a szétbontás a te mintád alapján, ezért az első szabad oszlopom az R lett.
R2-be írtam a képletet, ami a feltételes cserét csinálja.
=HA(BAL(P2;1)="-";HELYETTE(P2;"-";BAL(O2;3)))
Megnézi, hogy a szétbontott oszlopok közül a másodikban van-e kötőjel, és ha igen, akkor a kötőjelet kicseréli az előző oszlop első 3 karakterére.
Mivel relatív címeket használ, ezt elég jobbra lemásolni annyiszor, ahányszor ezt akarod, és előállnak a megjavított oszlopok.Imre
Szia!
Kérdés, hogy milye verziójú Excelt használsz?
Milyen nyelven?A problémát csak sejtem.
Pl. X oszlopban adja össze azokat a számokat, amik nagyobbak, mint valamennyi?
Ilyesmi?Imre
-
A hozzászólás módosításra került: 1 year, 3 months telt el-
Delila megoldása újabb gyönyörű példája a brute force cellánkénti loopolás és az oszlopkba egy lépésben illesztett képletek sebesség különbségére.
Minden munkalap interakció (olvasás, de főleg az írás) lassú. Ez 44e soron már elég jól látszik.És ezen még tovább lehetne finomítani a 365 új függvényeivel, de ha így jó neked Norbi, akkor íme egy lehetséges megoldás.
Én az „M” oszlopra írtam volna egy univerzális formulát, aminek az erdménye vessszővel tagolt szövegrészek, amit szétdobtam volna például TEXTSPLIT függvénnyel, ami 365-ös.
Még ciklus sem kell hozzá.Imre
Makrós fájlt csak ZIP formátumban lehet feltölteni.
Megint bebizonyosodott, hogy csak egy részfeladatra érkezett a kérdés.
Abban a bizonyos M oszlopban feldolgozhatóság szerint nem konzisztens adatok vannak.211853+263018 A + jel micsoda, Elválasztó? C és D oszlopokba kerülnének? 236321 236332 Ugyanez a kérdés a szóközre 240543/240544 Itt meg a slash-re 241003,263018 Nahát, itt meg egy vessző van! 241735 + 241737 Még jobb, a szóköz-pluszjel-szóköz 241861,-879,240475 Végül itt az eredeti kérdés mintája, megint vesszővelÍgy meglehetősen problémás, ezt valahogy egységesíteni kellene, vagy minden létező esetet és a hozzá tartozó szabályt megadni.
Most az jutott eszembe, hogy lehetne csinálni egy kis segédtáblát, ahová felsorolod egymás alatti cellákba az összes lehetséges elválasztójelet, és képlettel vagy makróval minden elválasztót vesszőre cserélni. Ezután már egységesen lehetne tovább haladni, s szétdobni annyi oszlopra, amennyi vessző van benne.Imre
Szia!
Pár kérdés:
– Milyen Excel verziót használsz?
– Az egyes részek mindig ugyanannyi karakter hosszúak-e?
– Jól értem, hogy az egész eredetileg 1 cellában van, és ezt te 3 cellába szeretnéd?
– Tudnál feltölteni mintát, ami több adatot tartalmaz, de valós, vagy ahhoz közeli?Sokszor járunk úgy, hogy egyszerűsített adatot és kérdést kapunk, dolgozunk vele, majd kiderül, hogy nem is az a feladat valójában, ami a kérdésben szerepelt.
Feltételezve, hogy 1 cellában van és 3 cellát szeretnél, de nem fix a részek hossza, de a szabály az, amit írtál én ezt csinálnám:
1. Vessző mentén szétbontanám 3 oszlopra (Pl. szövegből oszlopok / Text2Columns paranccsal), esetleg a 356-ös Excel erre való függvényével úgy, hogy mindegyik szöveges maradjon
2. Kivenném a kötőjelet a középső elejéről (HELYETTE / SUBSTITUTE függvény)
3. A középső elejéhez fűzném az első elejét (BAL/LEFT és összefűzés & jellel)A válaszok és minta után konkrét megoldás is születhet.
Imre
Szia!
A legfelül lévő, éppen aktív form bezárógombjához tartozó eseménykezelőben bezárod a többi formot is.
Vagy egy másik bármilyen esemény kezelője esetén ugyanígy
Unload form_neve vagy form_neve.visible=FalseAttól függ, hogy mit szeretnél a későbbiekben.
Imre
Én ezt a módszert hasznátam már 2010-nél biztosan…
Nem értem Delila, hogy nálad mi lehet.Sziasztok!
Hát, szerintem egy kicsit elbonyolítottátok mindketten a dolgot.
Mindenesetre a kulcs ott van, hogy kell lennie valahol egy egyedi megfeleltetési táblának, amiben az első oszlop az „Értesítés” oszlop egyedi értékei, a második oszlopban pedig a hozzájuk tartozó kategória szövege.Ezután ez egy sima FKERES probléma.
Laci, ha ezt a segédtáblát nem akarod megjeleníteni a munkalapon, akkor ugyanúgy elkészítheted 2D tömbkonstansként is.
1. Hozd létre a segédtáblát, ahogy delila csinálta
2. Egy üres cellába írj egy egyenlőségjelet és utána jelöld ki a segédtáblát. A formula a tartomány címét fogja mutatni
pl.:=E2:F36
3. Nyomj egy F9-et. Erre a tartománycím átalakul egy 2D-s tömbkonstanssá, valahogy így:
={"ÜF"\"Raktár";"ÜF BP"\"Raktár";"SZI"\"Értékesítés";"ÜF email"\"Raktár";......stb}
Lehet, hogy neked a backslash helyett vessző lesz, ez nyelvi verziótól függ.
A backslash jelöl egy új oszlopot, a pontosvessző jelöl egy új sort.4. Jelöld ki az egészet egyenlőségjel nélkül, és a névkezelőben készíts egy új nevet, majd a névhez tartozó referenciába másold a vágólap tartalmát. Én a „próba” nevet adtam neki
5. A tábla „B” oszlopbába mehet egy FKERES, ami ebben a névtartományban keres.
Nálam:=FKERES([@Értesítés];proba;2;0)
Ha akarod, lehet HAHIBA fv-el fűszerezni6. Az elején készült E2:F36 tartomány törölhető.
Imre
Új eposzi jelzőt kaptam. Már megérte 🙂
Szia!
A képek az Excelben külön rétegen vannak, ezért ha manuálisan csinálsz másolást, akkor a méretezést egyesével kell csinálni.
Lehet méretezni a képet a cellához, illetve a cellát a képhez.
Makróval persze bizonyára lehet gyorsítani a dolgot.Az Excel 365-höz nemrég jelentette be a Microsoft, hogy nemsokára megjelenik a termékben egy új lehetőség, miszerint a kép a cella tartalma lesz, és nem külön rétegen. Talán méretezési opciókat is meg lehet majd adni.
Imre
Félreértetted a CELLA függvény első argumentumát.
Oda nem a fájl útvonala kell, vagy a neve, hanem az a konkrét szöveg, hogy:
Magyar esetben „filenév” így dupla idézőjelek között
Angol esetben „filename”Imre
Teljesen jól tetted fel a kérdést, meg is adtam a választ.
A fentiek érvényesek azonos munkafüzetbe másolásra is.
Kipróbáltam, teljesen jól működik.Te úgy látom, hogy nem próbáltad ki.
Nálam éppen angol az Excel.
Ha ez a baj, akkor a MID = KÖZÉP, a CELL= CELLA, „filename” = „filenév”, SEARCH = SZÖVEG.KERESImre
Szia!
én így szoktam:
=MID(CELL("filename";$A$1);SEARCH("]"; CELL("filename";$A$1))+1;40)A CELL (CELLA) függvény egy ritkán használt függvény. Segítségével különböző információk kérhetők le az aktuális munkafüzetről vagy munkalapról vagy valamelyik celláról.
Pl. a CELL(„filename”; cellacím) az aktuális munkafüzet útvonalát és az aktuális munkalap nevét adja vissza ebben a formátumban:
"C:\Users\DELL\Desktop\[pppp.xlsx]valami"Ebben megkeresem az első záró kapcsos zárójel pozícióját, és onnantól kezdve kiveszek 40 karaktert. Mivel a lapnév az Excelben max 35 karakter, ezért max annyit vesz ki.
Ha ezt a képletet beírod egy létező, elmentett munkafüzet egyik lapjára, akkor visszaadja a lapnevet.
Ha ezt a lapot átmásolod egy új munkafüzetbe, akkor hibát fog adni, mert az új munkafüzet még nincs mentve.
Ha mented, akkor meggyógyul, de még kell neki valami miatt egy frissítés.
Én kettőt szoktam kattintani a képletet tartalmazó cellán, de nem nyúlok hozzá, csak Enter-rel lezárom.
Kész.Imre
Tudom, hogy örökölted, azért mertem leírni 🙂
Szia!
Megnéztem, azt hiszem rájöttem mi volt a te problémád.
A fő ok az, hogy még az elején tartasz a PowerQuery felfedezésének.
Előbb utóbb jönnek olyan feladatok, amiket nem lehet kattintgatással megoldani.
Ez is ilyen, mert az egyes darabok ilyen bénán vannak megtervezve.A fő gond az volt, hogy a mintafájl feldolgozásánál a három oszlop létrehozása után át is kell nevezni őket.
De mivel az előző lépés a fejléc előreléptetése, ezért az utolsó 3 fejlécbe bekerül a mintafájl 3 konkrét értéke (Konkrét dátum, megrendelés száma és megrendelő neve).
Az átnevezés során ezeket keresi, de nem fogja megtalálni, mert minden darabban ezek változhatnak.Emiatt oszlopszám alapján kellett az átnevezést csinálni. Pl. a 13. oszlop neve legyen „valami”.
Ez csak M kóddal lehetséges most.Megcsináltam így, és az összefűzés szépen elkészült.
A mintafájl esetén a „Mit szabnak vele” oszlopból vettem ki a null értékeket, hogy eltűnjön minden olyan sor, ami nem kell.
ha ez nem jó, mert lehet olyan darab, ahol ez nincs kitöltve, akkor majd változtasd meg.Visszatöltöm a megoldást.
A mappa útvonalat a lekérdezések közül a „Mintafájl” nevű lekérdezés Forrás lépésében és a külső (utolsó) lekérdezés forrás lépésében tudod elvégezni.
Imre
Attachments:
You must be logged in to view attached files.Csomagolj össze 10-20 db ilyen fájlt, jó vegyesen, és küldd el nekem, megnézem
Valahogy eltüntetted az összefűző lépést a külső lekérdezésből.
A fejlécben az egyik tétel neve mellett kell lennie egy ide-oda mutató nyilacska ikonnak.
Elvileg azzal fűzi őket össze.
Így utólag még nem próbáltam, de talán jó lehet.Sziasztok!
Kicsit tesztelgettem.
Úgy is, hogy a lapvédelem bekapcsolásakor a feljövő menü végén engedélyeztem az autoszűrő használatát.
Sajnos, a lapvédelem egyértelműen tiltja a szűrő és szűrés törlése parancsokat.Emiatt vagy marad az egyesével, vagy egy kis makrócska kell, ami feloldja a védelmet, kikapcsolja a szűrést, majd visszateszi a védelmet. Persze ehhez tudni kell a lap jelszavát.
Ezt a makrócskát lehetne tenni az egyéni makrófüzetbe, és adni neki egy gombot az eszköztárra.
Így megvalósulna a címben lévő „Egy mozdulattal” érzés 🙂Imre
Szia!
Ez csak makróval oldható meg.
Itt van pl. egy megoldás, amit kis módosítással tudnál használni:
https://stackoverflow.com/questions/17241302/independently-sort-multiple-columns-in-excelA kód alatti egyik komment leírja a módosítást is, ha esetleg bármelyik oszlopban lenne üres cella is.
Ezen kívül vannak még megoldások is a neten, ha ezzel nem boldogulnál…
Imre
Szia!
Ha a Zöld szám oszlop üres lehet, akkor válassz egy másikat, ami nem lehet üres, és a Null-ok törlésével minden nem oda való sor kijön. Persze ennek igaznak kell lennie az összes (500) fájlra.
A kompatibilitási hiba lehet az Excel és így a PowerQuery verziók eltérése miatt, de ez nem olyan művelet, ami ne lett volna benne már régóta, ez nem okozhat hibát.
Mindegy hol próbálkozol, csak a forrás útvonalakat kell mindig átváltoztatni.
A fájlok is lehetnek teljesen különbözők akár otthon és a cégnél, nem számít, csak meg kell találni, vagy elő kell állítani az egyforma szerkezetet. Ha ez nem lehetséges, akkor a PQ-vel nem lehet megoldani a problémát.#”előző lépés neve”{1}[Column10] – Ezzel i a baj?
Nem értem mit akarsz a Shift-Space-el…Imre
Az 5. és a 6. oszlop is alkalmas a dologra.
Ha nem mindenhol van Zöld szám oszlop, de az oszlopokat különben mindegyikben ugyanúgy hívják, és mindegyikben a Megrendelés munkalaphoz kell csatlakozni, akkor ez nem baj, használd a szűrésre az 5. oszlopot. Ha nem lenne Zöld szám oszlop, akkor a 6. oszlop megy előre 5. oszlopnak, ami szintén alkalmas null-ok kiszűrésére.
Viszont a Dátum, Megrendelésszám, Megrendelő cellák kirakása oszlopokba konkrétan a 10, 11, 12 oszlopokat használja a második sorból. Ha nincs Zöld szám oszlop, akkor összesen csak 11 oszlop lesz, nem 12, és ez a három oszlop előbbre tolódik.
Jól látom ezt?
Ha igen, akkor ez elég nagy problémaHát, most nyeltem hármat, maradjunk annyiban…
Hiába hívom fel a figyelmet, számtalanszor szívattak már azzal, hogy nem a valódi mintát mutatják, az csak később derül ki.
Nem kel nekem valódi adat, de a szerkezet az fontos.Először is kezd elölről az egészet.
Én a most küldött mintákban egységesen azt látom, hogy ha a mintafájl átalakításnál a „zöldszám…” oszlopból kivennéd a „null” értékeket szűréssel, akkor a nem kellő sorok kapásból eltűnnének, mindegy is, hogy hol vannakA túlcsordulás hiba meg azért van szerintem, mert nincs 13 oszlop, csak 12
Imre
Az a fontos, hogy küszködj! 🙂
Nem kell táblázatot csinálni és nem kell átírni a cellát kötőjel nélkülire.
Nem a táblázattá alakítás miatt lett Column10 a K oszlop.
Valószínűleg az történik, hogy a mintafájl átalakítás nálad tartalmaz egy automatikus lépést, amivel az első sort fejlécbe lépteti.
Mivel abban a sorban csak a K1 van kitöltve, ezért azt a tartalmat lépteti a fejlécbe.
Töröld azt a lépést, ami a fejléc előléptetést csinálja! Az elején van.
Így minden marad a helyén, és felül Column1…Column10 fejlécnevek lesznek.Imre
Szia!
Kipróbáltam, nálam a Shift+ESC működött. A Ctrl-ESC-t nem próbáltam.
Ezek után nem tudom, hogy mit mondhatnék…Te próbáltad másik gépen?
Nekem 365 verzióm van.Imre
Nos, akkor van egy megoldás, amit értesz és meg is tudnád csinálni, és van egy még jobb, amit meg nem.
Egyik lehetséges út, hogy sikerül kiguglizni, és sikerül is implementálni.
Másik lehetséges út, hogy „valaki” megcsinálja neked 🙂
Az biztosan nem fér bele, hogy meg is tanítsa.A PowerPivot-DAX világában idáig eljutni, hogy az alapokat értsd és tudd alkalmazni, majd ezt a problémát is meg tudd oldani, vagy legalább értsd, egy hosszabb tanulási utat igényel.
Mi lesz a te utad?
Szia!
Ha jól értem, akkor az aktuális havit szeretnéd a teljes éves átlaggal összehasonlítani az adott vevőre.
Évesen azt értem, ahol tartunk az évben most, azaz amíg van adat az adatforrásban.Tehát a januárit is a jan-szept átlaggal szeretnéd összenézni.
Csak meg akarom érteni a feladatot.
Hagyományos pivot táblán belül ez nem nagyon csinálható meg.
Esetleg lenne egy ötletem, amit kipróbálnék:
Az adatforrásba tennék egy plusz oszlopot (vevőátlag), ami a sorokban az aktuális sorban lévő vevőre vonatkoztatott átlagot számolja az egész forgalom oszlopra. Ez egy ÁTLAGHA függvénnyel megcsinálható. Egy vevő minden előfordulásához ugyanazt az értéket fogja mutatni, de ez vevőnként más lesz.Ha ezt a oszlopot behúzod a PIVOT-ba és MAX vagy MIN fv-el összesíted, akkor vevőnként fogja mutatni ugyanazt a számot, amit a forrásban ismétlődve mutat.
Az aktuális havi és az átlag különbségét viszont már a PIVOT-on kívül kell megcsináld (sima kivonás), mert a számított mező csak SZUM függvényre képes és azt csinálja is, ami nekünk most nem jó.
A számított mező vevőnként ezt mutatná: SZUM(Forgalom) – SZUM(vevőátlag)
Neked meg ez kell: SZUM(forgalom) – MAX(vevőátlag)És ha már kell egy kalkulációt tenni a PIVOT mellé, akkor a vevőre vonatkozó ÁTLAGOT ott is kiszámolhatnád az említett ÁTLAGHA függvénnyel. Így nem kell az adatforrásba plusz oszlopt tenni, ami nem csak felesleges plusz annyi kalkuláció, amennyi sorod van, de jelentősen lassú is lenne (a sorok számától függóen)
Tehát a javaslatom az, hogy a meglévő PIVOT mellé csinálj egy ÁTLAGHA öszesítést, lés amellé pedig egy kivonást.
Ha mindenképpen a PIVOT-on belül szeretnéd megoldani, akkor POWERPIVOT + DAX a megoldás.
Imre
Ha az egyben átmásolás azt jelentette, hogy az egész munkalapot, nem pedig a tartományt, akkor simán lehet, hogy a munkalap objektum tartalmazza a linket valahol. Roppant kíváncsi lennék, bizonyára tanulságos.
de ha neked elég, hogy megoldódott, akkor boldogság van 🙂
Imre
Egyszerűen csak használd, amikor egy probléma adja, hogy PQ lehet a megoldás.
Ha valamit nem tudsz, de tudsz kérdezni, az már jó.
Kérdezd a G-t először.Annyi minden van, hogy megjegyezni a mikéntjét szerintem nem lehet, de lehetőségeket talán igen.
örülök, hogy segíthettem.
Annak örülök, hogy ha próbáltad.
A kulcslépést akkor valószínűleg nem találtad meg, de ez nem is elvárható, ha az elején jársz még a PQ-nek.
Szóval van lehetőség abszolút módon meghivatkozni egy „cellát” a táblában a sor és oszlopszámával. A megfogott értékből lehet csinálni egy új oszlopot.
Ezt kell csinálnod a Dátum, megrendelés szám, megrendelő cellákkal egyesével a J2, K2, L2 cellákból.
A hivatkozás úgy néz ki, hogy Táblanév{sor száma}[oszlop neve]]
A sorokat a PQ nullától sorszámozza, tehát az első sor a nulladik sor.
Például a dátum a második sor Column10 oszlopában van:
#"előző lépés neve"{1}[Column10]A mintafájlban a lépések
– Utolsó két sor törlése
– A dátumot a leírás szerint egy új oszlopba teszed
Új, egyéni oszlop, aminek a neve „Dátum” és a képlete: = #”előző lépés neve”{1}[Column10]
– Ugyanezt megcsinálni a megrendelés száma és a megrendelő cellákkal (Column 11 és Column 12)
– Most már törölhető az első két sor
– Fejléc előreléptetés
– Az új 3 oszlop átnevezéseEzután a külső, eredmény lekérdezésben ott lesz az összefűzött változat.
A mintát, ahol egy táblázatra építve megcsináltam ezt, csatolom neked.Imre
Attachments:
You must be logged in to view attached files.Szia!
Egy kicsit nézegetem, aztán meg tudtam csinálni PowerQuery-vel.
Ha valóban minden munkafüzet azonos szerkezetű, akkor menni fog:
– Mindegyikben a „Megrendelés” lapot kell feldolgozni
– Ugyanezek az oszlopok és ugyanennyi
– A J, K, L oszlopok teteje ugyanilyen
– A végén mindig 2 sor van, amiket törölni kellBeteszed az összeset egy mappába, és mappából összefűzést csinálsz
A belső mintafájl átalakításban kell betenni:
– Utolsó 2 sor törlése
– Új oszlopba a Dátumot, a megrendelés számot és a megrendelőt (Az egész oszlopban ugyanaz)
– Törölni az első 2 sort
– Fejlécbe léptetni az első sort
– Átnevezni az utolsó 3 oszlopotKész.
Imre
A v
Ja, ez egy makrós fájl (xlsm)?
Én még megpróbálnám, hogy csinálnék egy új munkalapot, majd az összes többit kitörölném, és menetenék.
Ezután is megnézném, hogy mit mutat.
Így próbálnám kideríteni, hogy a link forrása egy munkalapon van-e vagy valahol a makró oldalon?Imre
Szia!
Olvad el ezt, hátha valamelyik segít!
https://excel-bazis.hu/tutorial/szabadulj-meg-a-kulso-fantom-linkektolJelezz vissza!
Imre
Szia!
Hát enyhén szólva nem egyértelmű a feladat, de homályosan sejthető. Amit sejtek:
Részletfizetésről van szó?
Van egy számla egy eredeti összegről, és azt törleszti, így minden befizetéskor csökken a tartozása?
Emiatt a számlaszám minden befizetéskor ugyanaz?Ha ajó a modell, akkor ez megcsinálható viszonylag egyszerűen.
Pl. Az eredeti, teljes összeg pozitív, a befizetés negatív értékekkel, akkor egy sima összesítés számlaszámonkéntBárhoyg is, az eredeti összegnek meg kell lennie egy számlához, és a hozzá tartozó befizetéseket is azonosítani kell, és kapcsolnio az eredeti számlához.
A befizető is a számlához tartozik egyértelműen, de a számla azonosító fontosabb.Kb ilyesmire gondolok, de minta nélkül nem tudok tovább menni.
Imre
Szia!
Most vettem észre ezt a kérdést. Ha még nem találtál megoldást
A sima szöveg helyett (Body) formázott szöveget kel betenni ilyenkor, ami ráadásul HTML.
Ezért HTML kódot kell generálni VBA-bana tábla addataiból.Előny, ha értesz egy kicsit a HTML-hez, hogy a táblázat milyen leíró TAG-ekkel van jelölve.
A neten elég sok megoldás van erre.
Pl.:
https://stackoverflow.com/questions/48496195/sending-excel-table-in-mailbodyA formátumot nem őrzi meg, csak az értékekekt, de táblázat lesz.
ha formátumot is akarsz (más színű fejléc, vagy ilyesmi), akkor még az is feladat.Van itt egy másik megoldás, ami lehet, hogy megőrzi a formátumot, de nem próbáltam, csak ismerem ezt a holland fószert, elég jó kódokat tett közzé:
https://rondebruin.nl/win/s1/outlook/bmail2.htmImre
Szia!
A sebesség leginkább a lekérdezéshez használt adatkészlet méreténél szoott gondot okozni frissítéskor.
A lekérdezés megnyitása és létrehozása során nem annyira jellemző.Okozhat problémát az is, hogy az adatforrás, amihez csatlakozik, az a hálózaton van, és nincs megfelelő sávszélesség.
Milyen forráshoz csatlakozol?
Imre
Nagyon szívesen!
Imre
Hagyományos excel eszközökkel nem. Olyan formula nem létezik, ami saját maga értékét megváltoztatja.
Eseménykezelő makróval megcsinálható.Imre
-
A hozzászólás módosításra került: 1 year, 3 months telt el-
-
SzerzőBejegyzés
