Az „Excel” fórum célja, hogy keretet adjon az Excel felhasználók széles táborának tapasztalataik megosztására, és lehetőséget a segítséget kérőknek. Az alábbi összefoglaló azért készült, hogy segítse a helyes kérdésfeltevést.
– Írd le szabatosan a problémát. Úgy fogalmazz, hogy ne csak te magad, de a szakértő is megértse, mire szeretnél választ kapni.
– Írd le, hogy milyen verziójú Excellel dolgozol. (Vagy ha nem – ill. nem csak – Excel, akkor micsoda?)
– Írd le, hogy milyen úton indultál el, és hol akadtál el rajta.
– A kérdés megértése szempontjából sokat segíthet, ha feltölteszt egy képet, amin látszik, hogy mit szeretnél, vagy illusztrálja azt.
– Még jobb, ha feltöltesz egy minta munkafüzetet valahová (pl. data.hu). Feltöltés előtt távolítsd el belőle a nem publikus adatokat.
– Ha a feladat jellege olyan, célszerű az "előtte" és "utána" állapotokat bemutatni. (Miből kellene csinálni mit?)
– Ha VBA kódon kell javítani, másold be a releváns kódrészt. Rövid kód mehet hozzászólásba, hosszú kód inkább ide: http://pastebin.com/
– Ha valami nem úgy működik, ahogy kellene, add meg a rendellenes viselkedés jellemzőit, a hibaüzenetet, és a hibát okozó programsort.
Advanced filter multiple criteria-val szűrök makroval adatokat. A szűrés szám formátum alapon működik.
Az adatsoromban viszont szöveges formátumok is vannak, amik számokat tartalmaznak. Ez azért van, hogy ha egy számsor nullával kezdődik az adatsorban, akkor maradjanak meg a kezdő nullák is az adatsorban, hiszen ha számformátumban van tárolva, akkor a kezdő nulla /nullák elvesznek.
Így viszont az advanced filter nem működik ezeken a szöveg alapú cellákon (amik egyébként számokat tartalmaznak).
Hogyan tudom a kettőt összehozni?
Vagy Hogyan tudom az alap adatsorban átalakítani a szöveges formátumú cellákat szám formátumúvá, hogy megtartsa a kezdő nullákat?
Ha vakinek van kedve foglalkozni ezzel esetleg, így a bejglik között :-)
Ha makróval van az adatbázis kapcsolat megoldva, akkor a "Application.ActiveWorkbook.Path" kóddal lehet lekérni az elérési útvonalat a fájl neve és kiterjesztése nélkül. Míg a "Application.ActiveWorkbook.FullName" kóddal az elérési utat és a nevet is lekéri. Attól függően, hogy az ember mit szeretne ellenőrizni.
Persze ez csak akkor működik, amíg otthon nem "ugyan oda" másolja az ember a fájlt. Vagyis teszem azt a pendrive meghajtóját otthon átnevezi Z: meghajtóra (mert pl. a cégnél is az a becsatolt hálózati meghajtó) és ugyan olyan mappa szerkezetet hoz létre, akkor nem működik az ellenőrzés.
Valamint van lehetőség számítógép nevet is ellenőrizni. Azt a "Environ("computername")" kód adja vissza. Nyilván ennek a hátránya, hogy új gép (vagy újratelepítés esetén) módosítani kell a kódon, hogy beengedje azt az eszközt is.
Szóval: Azt, hogy lehet megcsinálni, hogy ha a "B" fájlt áthelyezik vagy átmásolják más mappába, akkor már megszakadjon az "A" adatbázis excellel a kapcsolata?
"Másik lehetőség: külső adatbázist használsz, amiben az adatok vannak. Access-el hozod létre és jelszóval véded már megnyitás ellen is. Nyilván ezt sem vered nagydobra Mellé XLSB-be mented a munkalapot és csak egy darab kört raksz le, ami futtat egy makrót. Minden funkciót zárolsz és csak jelszóval engeded szerkeszteni a munkafüzetet és a makrókat is jelszóval véded.
Makróval nyitod meg az access fájlt és userformokkal kéred le belőle az adatokat. Nyilván sokkal macerásabb, a prtacr-t szerintem nem lehet letiltani. Az XLSB-t viszont nehezebb feltörni."
Van két excel fájlt. Az "A" egy adatbázis, ami folyamatosan frissül. A "B" pedig össze van kötve az "A" adatbázissal és annak az adatait használja. Ezek két különböző helyen vannak, különböző mappákban.
Azt, hogy lehet megcsinálni, hogy ha a "B" adatbázist áthelyezik vagy átmásolják más mappába, akkor már megszakadjon az "A" excellel a kapcsolata?
A nyomtatási kép beállításakor problémába ütköztem.
Van egy táblázatként formázott 50 soros tábla 4 oszloppal. (nem én készítettem az eredeti táblát.) A Nyomtatási terület meghatározásakor kijelölöm az egész táblát (4 oszlop 50 sor) Amikor megnézem a nyomtatási képet akkor először csak 3 oszloppal(?), majd (a kijelölésnek megfelelően) 4 oszloppal mutatja ugyanazt a táblát. (Tehát normálisan 4 oldal lenne, de 8 lesz belőle)
Ha a Nézet/Oldaltörés megtekintését bekapcsolom, látom a 3. oszlop után a szaggatott vonalat, ami alapján a nyomtatási képen először a "3 oszlopos" változat látszik. Ha a szaggatott vonalat áthúzom a "helyére" már csak a 4 oszlopos táblát jeleníti meg, de kicsi méretben, összenyomva és az Oldalbeállítás/Nagyítás menüpontban hiába növelem oldalszámban a szélességét/magasságát nem történik semmi.
Igen, lehet érdemes lenne akkor megismerkedni vele... csak nem sok időm van még azzal is külön foglalkozni, mert az excellel is bőven el vagyok maradva, ahhoz képest amit kellene tudnom (bár az excel kb pont az, amit örökké lehet tanulni, annyi sok minden van benne és kimeríthetetlen- tipikusan, minél jobban megismered, annál jobban jössz rá, hogy igazából semmit se tudsz még belőle:-) ) és akkor most kezdjem az Access-t is :) :-) :-)
Az Accesst kb. úgy képzeld el mint egy Excel táblázatot.
Csak abban az oszlopoknak nem betű jelei vannak, hanem nevei.
Pl. egy partnertörzs esetén:
cég kód
cég név
adószám
cím
weblap
kapcsolat tartó
telefonszám
e-mail cím
Alapértelmezetten két féle táblázat létezik (legalábbis így éjfél után több nem jut eszembe). Az egyiknél van egy úgynevezett egyedi azonosító érték. Ez az én példámban a "cég kód" nevű oszlop. Ide nem kerülhetnek be ismétlődő elemek, így a táblázat egy-egy sora egyértelműen azonosítható. A másik féle táblázatban pedig nincs ilyen egyedi azonosító. Hogy erre is mondjak egy példát: ha egyedi árakat akarsz rendelni valamelyik termékhez és valamelyik vevődhöz, akkor a következő oszlopokra lesz szükséged: termék kód, cég kód, ár. Itt egyik sem rendelkezik egyedi azonosítóval, mert lehet benne ismétlődés. Pl. a termék kód ismétlődik, ha ugyan annak a terméknek több vevőhöz is van egyedi ár rendelve. A vevőkód szintén ismétlődik, ha több termékből is kap egyedi árat. Az ár meg ismétlődik, ha több terméknek is ugyan az lesz az egyedi ára.
Az egyedi azonosítós arra jó, hogy rögtön az azonosítóval tudd azonosítani az adott sort. Én pl. sok helyen a vevő kóddal azonosítom a vevőinket. Fejből nem tudom semelyik vevőnknek sem a kódját (kivéve a sajátomat (nyilván mint magánszemély szereplek a számlázóban) :D, mert amúgy szabadon módosítható a számlázóban a kód), azt a számlázó generálja.
A régi vevőinknek (akiket úgy importáltunk a számlázó váltás előtt még csak számból állt a vevőkódja. Az újaknak van előtte egy "v" betű is. Amúgy pedig egy szám.
Ha van egy Access táblázatot, akkor abból SQL parancsokkal és makró segítségével tudsz végrehajtani lekérdezéseket. Ezeket úgy kell elképzelni, mint egy fajta szűrés. Pl. ha a cím mező nem egy mezőből áll, akkor tudsz szűrni könnyedén vármegyére is. És az egészben az a jó, hogy egy lekérdezésben akárhány feltételt összekapcsolhatsz. Azaz le tudod kérdezni azokat, aki Zala megyeiek, az e-mail címük "*@google.com" végződésű és a telefonszámúk "+36 83*" kezdetű. Ha megvan ez a lekérdezés, akkor tudsz vele dolgozni. Pl. ki tudod listáztatni csak a cég neveket és a hozzájuk tartozó várost. A többi adat is ott van a lekérdezésben, mert "lejön" az is azzal együtt, de neked nem muszáj minden egyes "oszlopot" kiírni egy cellába (vagy akárhova).
Az Access adatbázis annyiban különbözik az Exceltől, hogy abban statikus adatok vannak. Pl. ha csinálsz egy olyat, ahol "A" és "B" oldalakat, valamint a területet tűnteted fel, akkor neked a terület nem fog automatikusan átíródni, ha "A" vagy "B" oldalak megváltoztak. Sok esetben származtatott adatokat nem is nagyon szoktak eltárolni adatbázisban. (Vagyis ez már inkább optimalizáció függő. Ha bonyolult a számítás (mármint erőforrás igényes), akkor lehet, hogy jobban megéri beáldozni azt a néhány bájtot a végeredmény eltárolására, mint mindig kiszámolni azt). Ha meg nem erőforrás igényes a számítás, de rengeteg adatod van (viszont nem kell mindig az összes), akkor inkább számoljon a gép, ne pedig statikusan legyen ott a végeredmény.
Pl. van egy honlapon, ahova csináltam magamnak menet táblázatot (egyelőre csak metrikus normál és metrikus finom menetekhez). Gyakorlatilag elegendő a menet típust (metrikus vagy metrikus-finom), a névleges átmérőt és a menetemelkedést elmenteni. Az összes többi adatot ki lehet ezekből számolni. Mint. pl. az orsó átmérőjét, a menet mélységét, lekerekítést, magfuratot, stb. Így feleslegesen nem terheltem az adatbázist ezekkel. Úgyis, ha szeretnék egyszer egy beviteles kalkulátort csinálni, akkor már megvannak a számítások, csak át kell őket emelni a kalkulátorba.
A VBA-hoz és az SQL lekérdezésekhez kell egy kis programozási szemlélet. De rengeteg hasznos információ fent van a neten, ha az ember fia/lánya beszél angolul. Illetve az itteni közösség segítőkész, szerintem nem igazán szoktak lenni megválaszolatlan kérdések, ha azok jól vannak feltéve. Csak tudni kell, hogy miből mit szeretnél kihozni.
VBA-ban még elég gyerekcipőben járok, de egyelőre, amire nekem kell/kellett, +a net segítségével (beleértve Titeket is), többé - kevésbé elboldogultam. Az Access-t meg egyáltalán nem ismerem :-( (nem tudom egyébként mennyi idő lenne annyira megismerni, amennyire nekem kell)
Szerintem is egyszerűbb lenne minden adatot Accessben tárolni.
Nekem van egy ilyen lekérdező makróm.
A bal felső userformban ki lehet választani a vevőt, illetve a témakört. A userform hívja meg az adatbázis lekérdező makrót (kap 3 paramétert is bemenetnek)
Végül kilistázza az utolsó 10 darab e-mailt ami az adott vevőnek lett kiküldve az excelből.
A rec.Fields("NÉV").Value adja vissza az adott sorhoz tartalmazó NÉV oszlopban lévő adatot. Így csak azokat az adatokat íratom bele az "ertek" nevű változóba, amire szükségem van. Végül a MsgBox segítségével van kilistázva. Nekem ennyi elegendő volt.
Itt maga a táblázat adja hozzá az adatbázis elemeket is az Access dokumentumhoz automatikusan. Ahhoz másik függvény van meghívva.
Az alap makró nem tőlem származik azért is vannak benne angol kommentek (: viszont elég jól testre szabtam. Illetve most vettem észre, hogy el is van írva a függvény neve, de nem módosítom, mert tudja a franc, hogy hol hivatkoztam már rá.
Jó pár dolgot szerintem te is tudnál belőle meríteni.
Nézetem szerint a Listbox - ugyanúgy mint a Combobox - alapvetően a szűrési paraméterek bevitelére szolgáló eszköz. Az eredmény megjelenítésére mást érdemes használni.
Annál is inkább, mert a Listbox annyi oszlopot jelenít meg a forrásból, amennyit beállítasz, ami lehet csak egy, de lehet több egymás melletti oszlop is.
Amire te gondolsz, annak inkább a lekérdezés felel meg, ami az adatokból annyit jelenít meg, ami a feltételeknek megfelel. Lekérdezést létre hozhatsz Excel verziótól függően manuálisan ill. VBA-val, az újabb Excel verziókban pedig Power Queryben, szintén manuálisan és M nyelven "makróval".
Javaslom az Adatok - Lekérdezések menüpont tanulmányozását akár makrórögzítéssel is. Közös használatú munkafüzetekben vannak korlátozások, de talán ez nem érinti a lekérdezéseket.
Másrészt, ha már több felhasználó és közös adatállomány, miért nem ACCESS-t (ami adatbáziskezelő!) használsz az adatok tárolására, amihez adatbevitelhez és lekérdezéshez is használható és hozzákapcsolható azh Excel? Cégnél ez szerinten nem lehet nagy probléma. Ráadásul nagyon nem is kell az ACCESS makrózást megismerni, minden működhet Excelben, az ACCESS pedig elvégzi a konzisztencia és egyéb ellenőrzéseket. A jogosultságokat is be lehet állítani benne az adatbázis szerkezet létrehozásakor akár.
A lényeg az, hogy tudjanak szűrni, de ne álljon nekik össze a háttéranyag (amit berögzítenek adatbázis) szerkezete. Több tulajdonságot beviszünk egy ID-hoz (kb 15-öt). Ellenben a szűrés közben kb 5 tulajdonságra szűrnek le maximum. Csak a szűrés eredményét akarom, h lássák, ami a leszűrt 5 tulajdonság + még 1-2 adat a maradék 15-ből.
(itt jön a jogos kérdés, hogy akkor minek kell 15 féle tulajdonság, de élő példa nélkül nehéz elmondani, miért kell a maradék adat, ha azokra nem szűrűnk és nem is jelenítjük meg szűrés alkalmából, de maradjunk annyiban, h kell)
Ezért rögzíttetem be az adatokat Userformmal, nem pedig csak soronként manuálisan az adatsorba, + soronként beszúrva, hogy ne lássák a teljes adatbázis. A userform majd hogy nem a mi helyzetünkben csak ezért kell.
Illetve ha a listboxban látják a szűrés eredményét, az a teljes adatsor megjeleníti, az ID mind a 15 tulajdonságát, amit pont nem akarunk, h lássanak, mert akkor ugyan ott vagyunk ismét, mintha az adatbázisban rögzítenének manuálisan. - vagy ezt nem jól gondolom? (Még nem próbáltam ki egyelőre csak elméletben gondolkozok.)
Szóval a lényeg, hogy a bevitt 15 adatsort soha ne lássák egyben az egyes ID-k hoz.
"A Userformot úgy kell kialakítanod, ne fedje el a munkalap azon részét, ahova a szűrés eredményét szeretnéd megjeleníteni." Úgy szeretném, hogy egyik munkalapon lesz a Userform és nem ugyan oda jeleníti meg az adatokat, hanem ha elvégezzük a leszűrést, akkor a szűrés eredményét egy másik munkalapon lehet megnézni. Szóval, hogy a userform használata közben ne lehessen semmit látni, se a userform munkalapon, se a userform listbox-ban sem.
"A szűrt adatokat pedig átmásolod erre a területre - akár pl. a Range:AdvancedFilter használatával - másik helyre másolással." Igen, úgy gondolom én is, hogy a Userformmal csak megadom az advance filternek a szűrési feltételeit és aztán csináltatom meg az adnavce filterrel a leszűrést egy másik munkalapra, mint szűrési eredményt.
Szóval alapvetően a userformmal kialakított szűrési feltételek, amit majd használok az advance filterrel, kerüljön egy külön munkalapra és a listboxban se jelenítsen meg eredményt az userform.
"Egyébként milyen műveleteket szeretnél még a szűrt adatokkal végezni?" A leszűrt eredményből majd csak e-mailcímek és telefonszámok kellenek, amit használni kell tovább.
Olyat tervezek csinálni, h minden egyéni felhasználónak legyen egy saját excel-file a gépén, névreszólóan, amivel adatot tud bevinni (userformmal). Ezek az adatok, amiket a felhasználók, személyenként a saját, névreszóló excellel berögzítenek, egy, a szerveren lévő "gyűjtő" file-ba kerülnek. Eddig ezt, azt hiszem, meg tudom csinálni.
Az egyéni exceleknek, lenne egy olyan felülete, ahol az adatbevitel mellett, a már bent lévő adatsorban lehetne adatokat kinyerni. Erre szűrő funkció is kell. Ezt szintén userformmal akarom megoldani, listboxxal. Úgy gondolom, ez is menni fog.
Viszont, amit szeretnék, hogy a leszűrés alkalmával, a leszűrés eredménye, ne a listboxon belül jelenjen meg, hanem egy másik munkalapon. Ezt, hogyan kell? Tudnátok benne segíteni?
Netről gyorsan kifotóztam egy listboxot, csatolva, hogy mire gondolok.
A volt főnököm szeret több munkalapot is nézni egyszerre. Általában nem elmenti a munkalapokat és utána zárja be őket, hanem a bezárásra kattint és ha kérdezi, hogy menti a módosításokat, akkor menti csak el azokat.
Ha több ablak is nyitva van, akkor addig nem tesz fel ilyen kérdést az Excel, amíg az utolsót be nem zárod. Vagyis, ha nem jó sorrendben zárod be őket, akkor a szűrő és a rögzítés beállításait bukod.
Bár nem győződtem róla, de had kérdő jelezzem ezt meg.
Bármelyiken bármilyen módosítást eszközöl, ugyanazt kell látnod mindegyiken. Munkafüzet szinten ezek egymás tükörképei. (Épp ez a lényege ennek a funkciónak)
Vagy lehet, hogy ez a szűrőnél direkt így működik, hogy több konstellációt is lehessen nézni pl ugyanazon a munkalapon? ... Hmm... nem tudom.
A funkció hasznos tud lenni, de okoz bosszúságot is.
Pl. alap esetben a szűrések az 1-es ablakban fognak megjelenni. Illetve az ablaktábla rögzítés is. A 2-es ablak ilyen szempontból szűz lesz. Ha a kolléga nem figyel és az 1-est zárja be hamarabb és úgy menti a doksit, akkor ugrottak ezek a beállítások
Sose használtam ezt a funkciót, de bizony nem kizárt ez a lehetőség, ami ebben az esetben nem is rendellenesség. Hanem lehet, hogy hzs65 olvtársunk valamelyik kollégája így használta, és mentette el az 1-es fájt, ami a leírt módon nyílt meg, mikor hzs65 használni akarta. Hiszen hálózati meghajtóról közösen dolgoznak.
Ebben az esetben a következő megjelenés igazolhatja ezt a feltevést: Az alábbi képen a képernyő tetején a megnyitott fájl neve (nálam Teszt.xlsx) mögött :1 kell hogy látsszon, alul pedig mindkét fájl megjelenik :1 és :2 kiegészítéssel. Ha ez történik akkor megtaláltad az okot. És az is érthető, hogy ha hzs65 lezárja a 2-es fájlt (:2 kiegészítéssel), majd menti 1-est, akkor utána már nem nyílik meg mindkét ablak az újabb nyitáskor. Egészen addig, amíg az a bizonyos kolléga nem nyitja meg, majd menti az általa használt módon.
