Meglehetősen sokszor találkozok azzal, hogy több munkalapos munkafüzetekben a munkalap nevét az egyik cellában konstansként is beírják. ha a lap neve megváltozik, akkor azt a cellát is manuálisan változtatni kell. Gondoltam, itt az ideje, hogy közzétegyem a megoldást, amit persze megint nem Én találtam ki, bár annyira […]
Tovább... →Általános Tippek
Az Excel hatékony használatához nélkülözhetetlen, hogy gyorsan tudjunk navigálni, azaz egyik helyről a másik helyre ugrani a cellakurzorral. Ez a feladat munkalapon belül, munkalapok között, és munkafüzetek között fordul elő. Összegyűjtöttem néhány hasznos tippet , próbáljátok ki, és vezessétek be a mindennapokba, érdemes! Munkalapon belüli gyors navigációk […]
Tovább... →Az ‘autofill’ eszközt mindenki ismeri. Rengeteg dologra lehet használni, de ebben az esetben egy érdekes problémára mutatnék három megoldást.
Mit tennénk akkor, ha az ANGOL ABC-t szeretnénk a kitöltő eszközzel megoldani egy tartományban?
Egyéni lista – A triviális megoldás
Az első lehetőség adja magát. Egyéni listát (Custom List…) hozunk létre. Ezzel úgy fog működni a dolog, mint a hónapok vagy a hét napjai esetén. Ehhez egyszer le kell írni a listát egy tartományba, és onnan importálni.
Az egyéni listák szerkesztése Excel verziónként különböző helyen van az Excel beállítások között.
Excel 2007: Népszerű elemek -> Egyéni listák szerkesztése…
Excel 2010: Speciális -> Általános szakasz -> Egyéni listák szerkesztése…
Ha nagy nehezen megtaláltuk, akkor a beállító párbeszédablak már ismerős lehet, ez nem változott az Excel 2003-as, vagy talán még korábbi verziók óta.
- Begépeljük az ABC betűit egymás alá
- Beimportáljuk egy Egyéni listába
Ennek a megoldásnak az előnye az, hogy csak egyszer kell megcsinálni az importot, és bármely munkafüzetbe használhatjuk. Csak be kell írni egy cellába az ABC bármely betűjét, és lehúzni.
Kitöltés képlettel – első lehetőség
Ez a megoldás azon alapul, hogy a számítógép valójában a betűk kódját használja. Az „A” betű kódja a 65, a „B” betűé a 66, és így tovább. Az angol ABC-ben 26 betű van, az utolsó „Z” kódja a 90.
Kisbetűknél az „a” kódja a 97, a „z” kódja pedig a 122.
Az Excel CHAR() függvénye (magyar verziója a Karakter ) a kódból betűt állít elő. Az „A” betűt pl. így kapjuk meg:
=CHAR(65) =KARAKTER(65)
Hogy lehúzható és automatikusan kitöltődő legyen, a 65 helyére egy 65-től folyamatosan növekvő képletet kellene találni, ami a következő:
=CHAR(ROW(A65)) =KARAKTER(SOR(A65))
A fenti képletet beírva egy cellába, majd lehúzva, máris megvan az ABC.
Kitöltés képlettel – második lehetőség
Itt azt csináljuk, hogy a kezdőbetűt beírjuk a lista elejére, és a következőket már számoljuk úgy, hogy az előtte lévő kódját képezzük, majd ahhoz egyet hozzáadva visszaalakítjuk betűvé. Így kapjuk a következő betűt.
1. Egy cellába(mondjuk C2-be) írjuk be az „A” betűt
2. A következő cellába ezt a képletet:
=CHAR(CODE(C2)+1) =KARAKTER(KÓD(C2)+1)
Húzzuk lefelé a kitöltővel.
A Videóban mindez működés közben
Az ABC automatikus kitöltése
Talán már többen találkoztatok olyan táblázattal, amiben az egyik oszlopban olyan módon jelölték a csoportokat, ahogy az alábbi ábrán látható:
Az elképzelés, a szándék látszik, de az ilyen fajta csoportképzés legfeljebb nyomtatásban hasznos. Táblázatkezelés esetén lehetetlenné teszi a legegyszerűbb adatkezelő műveleteket is, mint pl. az Auto szűrő használata, vagy akár a PIVOT.
Mit kell tennünk ebben az esetben?
Mindenképpen ki kell egészíteni a hiányzó városneveket, azaz előállítani a javított táblázatot:
A feladat hagyományos módszerekkel (másolgatás) meglehetősen reménytelennek tűnhet egy olyan táblázatban, ahol mondjuk több tíz vagy száz város van.
A trükk
Ha ki tudnánk jelölni az összes üres cellát, majd egyszerre feltölteni mindegyiket a felette lévő adattal, az megoldaná a problémát.
A Videóból kiderül, hogyan is kell ezt csinálni.
Kategóriák gyors pótlása
Milyen jó lenne, ha minden új dokumentum nyitásakor (fehér lapocska) egy olyan üres munkafüzet nyílna meg, ami már tartalmaz néhány alap beállítást, formátumot, cellastílust, stb…
Sőt az sem lenne rossz, ha új munkalapot nyitunk, az szintén tartalmazhatna néhány beállítást, pl. élőfej-élőláb beállítást, oszlopszélességeket, stb…
Többször kérdezték már tőlem pl. azt, hogy lehetne-e alap beállításként a celláknak 2 tizedesjegy formátumot beállítani az általános (general) helyett?
Készíthetünk alap munkafüzet sablont és alap munkalap sablont is. Utóbbit fogja használni az Excel új munkalap beszúrásakor.
Minden a megfelelő helyen és a sablonfájl nevén múlik
A hely, ahová menteni kell a sablonfájlokat
XP oprendszer esetén
Minden felhasználónak:
C:\Program Files\Microsoft Office\<OfficeXX>\XLSTART
<OfficeXX> lehetséges értékei
| Office12: | Office 2003 |
| Office14: | Office 2007 |
| Office15: | Office 2013 |
Csak egy adott felhasználónak, pl. saját magunknak:
C:\Documents and Settings\<felhasználónév>\Application Data\Microsoft\Excel\XLStart
Vista oprendszertől
Minden felhasználónak ugyanaz, mint az előbb
Csak egy adott felhasználónak, pl. saját magunknak:
c:\Users\<felhasználónév>\AppData\Roaming\Microsoft\Excel\Xlstart\
Fájlnevek
Angol verzió esetén
Munkafüzet sablon: Excel 2003-ig Book.xlt, Excel 2007-től Book.xltx
Munkalap sablon: Excel 2003-ig Sheet.xlt, Excel 2007-től Sheet.xltx
Magyar verzó esetén
Munkafüzet sablon: Excel 2003-ig Munkafüzet.xlt, Excel 2007-től Munkafüzet.xltx
Munkalap sablon: Excel 2003-ig Munkalap.xlt, Excel 2007-től Munkalap.xltx
A munkafüzet sablonban beállíthatjuk, hogy hány munkalappal induljon (Célszerű eggyel), megadhatjuk a szükséges cellastílusokat,
Nézzük mindezt működés közben
Az alap sablon módosítása
A táblázatok ugyebár sorokból és oszlopokból állnak. Gyakori művelet, amikor a táblázat sorait egy vagy akár több oszlop alapján sorba rendezzük. Például név szerint vagy érték szerint növekvő vagy csökkenő sorba.
Az erre szolgáló gombokat is mindenki ismeri.
Igen ám, de mi van akkor, ha a táblázat oszlopait szeretnénk rendezni valamelyik sorban lévő értékek alapján? A következő példában a nevekhez tartozó oszlopokat szeretnénk névsor szerint rendezni úgy, hogy a napok a helyükön maradjanak.
Rendezés előtt
rendezés után
Megoldás
A rendezés előtt be kell állítani, hogy nem oszlop szerint (fentről lefelé) hanem sorok szerint (balról jobbra) szeretnénk rendezni. Ezt a rendezés párbeszédablakban lévő „Options…” (Beállítások) gomb mögött tudjuk megtenni.
- Data -> Sort -> Options -> Left to Right
- Adatok ->Rendezés ->Beállítások ->Balról jobbra
Ezután jelöljük ki a táblázatot az első oszlop nélkül, hiszen a hét napjait ott szeretnénk hagyni.
Most jön a rendezés végrehajtása. (Sort / Rendezés gomb)
A rendezés beállítása sor alapján
Mindez működés közben
Oszlopok rendezése sorok szerint
Feltételezve, hogy a vizsgált év január 1 és decmber 31 között tart, az adott dátumhoz kapcsolódó negyedévet a következő képletekkel lehet számítani:
Hagyományos módszer
A Hónap (MONTH) függvénnyel kivesszük a dátumból a hónapot,ehhez kettőt hozzáadunk, majd az így kapott eredményt hárommal osztjuk, és képezzük az eredmény egész részét.
Feltételezve, hogy az átalakítandó dátum az ‘A2’ cellában van, abból így számolhatunk negyedévet:
=INT((MONTH(A2)+2)/3)
Érdekes módszer
A Választ (CHOOSE) függvényről már egy másik anyagban említést tettem. Ebben az esetben is tökéletesen használható. Ha az adott dátum (A2) hónap értéke 1 vagy 2 vagy 3, akkor 1. negyedév, ha 4, 5, vagy 6, akkor második negyedév, stb. Tulajdonképpen pontosan úgy működik, mint ahogyan mi fejben számítjuk a negyedévet.
CHOOSE(MONTH(A2),1,1,1,2,2,2,3,3,3,4,4,4)
A választ fv. első paraméterében kiszámolja az ‘A2’-ben lévő dátum hónap értékét, majd az ezt követő argumentum listájából az annyiadikat választja.
Lássuk működés közben:
Negyedév számítása a VÁLASZT függvénnyel
Mostanában egyre gyakrabban lehet olvasni Excel témakörben is az un. Dashboard-okról. Magyarul „irányítópult„-nak szokták fordítani, ha egyáltalán lefordítják.
Mi az a DASHBOARD?
Sokat gondolkoztam azon, hogy egy mondatos definícióban hogy lehetne megfogalmazni. Végül is meg lehetne, de inkább mégis másként döntöttem. Annyi helyen lehet találkozni a kifejezéssel, és annyiféle szituációban használják (sokszor szerintem tévesen, vagy kissé nagyzolva), hogy inkább megpróbálom több mondatban körülírni.
Adott egy táblázatos adatforrás. Fejléc, adatrekordok, szokásos dolog. Ha a táblázat adataiból összesítő jelentést vagy jelentéseket (képlet vagy PIVOT), illetve grafikont vagy grafikonokat szoktunk készíteni. Ha ezekből valamilyen további szűrési/egyszerűsítési módszerrel kiemeljük a számunkra legfontosabb, un. kulcs információkat, máris kész a Dashboard, ami tulajdonképpen egy állapotjelentés, és döntések segítéséhez használhatjuk.
Nézzük egy mondatban:
A Dashboard kulcs információk egyszerű megjelenítése, amit segít a felhasználónak gyorsan, helyes döntéseket hoznia.
Egy Dashboard általában
- Kis méretű (legjobb, ha egy képernyőre kifér)
- Informatív, fókuszál a felhasználó számára legfontosann információkra
- Táblázatos és/vagy grafikus megjelenítésű
Interaktív Dashboard
A felhasználó a fenti ábra szerint az összesítési szempontokat vagy/és a szűrést interaktív vezérlőkön keresztül beállíthatja, így szabályozva, hogy mit szeretne látni. (legördülő menük, nyomógombok, rádiógombok vagy checkbox-ok, stb.)
A modell úgy működik, hogy dinamikusan leköveti a felhasználó által végzett szűrési beállításokat. A követést egyszerűbb esetben az Excel által nyújtott lehetőségeken keresztül végzi (Munkalap függvények, Pivot eszközök).
Nagyon hamar elérkezhetünk azonban oda, hogy a Dashboard mögött masszív VBA program működik, és az végzi az összesítés-szűrés-megjelenítés feladatot.
DEMO
A következő video a címével ellentétben, nem mutatja meg, hogy hogyan kell interaktív Dashboard-ot készíteni, de remekül bemutatja, hogy viselkedik működés közben.
Tovább... →Tömbök fogalma
A tömbök az Excel-ben értékek sorozatát jelentik. Általános felhasználás során nem igazán szoktunk foglalkozni a tömbökkel, hiszen azokat legtöbbször egyszerűen kijelöléssel határozzuk meg.
Ha például egy egyszerű összesítést (SUM /SZUM) alkalmazunk, akkor a fügvényvarázsló argumentum mutatja a kijelölt tartományt, mint tömböt.
Egy dimenziós tömbök
Az egy dimenziós tömbök egymás melletti vagy egymás alatti adatsorokat tartalmaznak. Próbáljuk meg a következőt!
- Egy cellatartományba egymás mellé balról jobbra írjunk be értékeket. (Pl.: B1:K1)
- Egy üres cellába írjunk egy egyenlőség jelet, majd jelöljük ki a B1:K1 tartományt (=B1:K1)
- Nyomjuk meg az F9 billentyűt
- Az Excel kibontja a kijelölést mint egy dimenziós tömböt
={1.2.3.4.5.6.7.8.9.10}
Próbáljuk ki ugyanezt egymás alatti értékekkel!
={1;2;3;4;5;6;7;8;9;10}
Sor és oszlop szeparátorok
A sorokat, illetve az oszlopokat elválasztó karakterek a vezérlő pult nemzetközi beállításaitól függenek. Emiatt a fenti példa különböző nyelvi beállítások esetén más elválasztó karaktereket eredményezhet.
Bővebben az Excel nemzetközi beállításainak vizsgálatáról egy korábbi oktatanyagban lehet olvasni:
Az Excel nemzetközi beállításainak lekérdezése
Két dimenziós tömbök
A két dimenziós tömbök valójában táblázatok, azaz több sort is tartalmaznak.
Töltsünk fel egy tartományt értékekkel, majd a fenti módszerrel jelöljük ki, majd nyomjuk meg az F9-et
={"A".10;"B".20;"C".30}
Az látható, hogy a sorokat a pontosvessző, az oszlopokat pont jelzi.
Saját tömb konstansok
A képletekben használhatunk a fentiek szerinti tömbkonstansokat. Ezt legegyszerűbben úgy tehetjük, hogy a névkezelőben nevet adunk a tömbkonstansoknak.
- Egy tartományba írjuk be a tömb elemeit (egy vagy két dimenzió)
- Egy üres cellába a fentiek szerint bontsuk ki a tömböt, majd másoljuk ki (Ctrl+C)
- Nyissuk meg a névkezelőt (Ctrl+F3), Hozzunk létre egy új nevet, és hivatkozásként illesszük be a tömböt
- A forrástartományt kitörölhetjük
Használati példák
A tömbkonstansokat használhatjuk kisebb keresési tartományként azokban az esetekben, ha nem szeretnénk a forrás tartományokat a munkafüzet celláiban tárolni.
Az elnevezett tömbökben lehet bármelyik kereső függvénnyel keresni, és az összes, tartományokon értelmezhető függvényt használhatjuk.
Konkrét példákat a kapcsolódó videóban lehet majd látni.
Tömbök a képletekben
Képletek belsejében gyakran találkozhatunk olyan részekkel, amelyek tömböt eredményeznek, és ezt a tömböt dolgozza fel a képlet külső része.
Példa: mennyi 4 betűs vagy annál rövidebb szöveg van egy oszlopban? (A szavak az M1:M7 tartományban vannak)
=SZORZATÖSSZEG(--(HOSSZ(M1:M7)<=4))
A képlet működését a Videóban mutatjuk be.
Video
Tömbkonstansok az Excelben
Ha egy táblázatban különböző oszlopokban többször is előforduló értéket keresünk, és az adott sorokat törölni szeretnénk akkor a következő, egyszerű eljárást használhatjuk:
- Jelöljük ki a táblázatot vagy tartományt (Ctrl+A)
- Keressük meg az adott érték (Ctrl+F), és a keresési párbeszédben gyűjtsük ki az összes találatot (Find All / Listába mind)
- Zárjuk be a párbeszédablakot. A megtalált értékek kijelölve maradnak.
- Ctrl + – (kötőjel vagy mínusz jel), ez előhozza a cella törlése párbeszédet
- Egész sorok törlése
Ezt a módszert akkor is alkalmazhatjuk, ha a keresett cellában speciális érték van, pl. hibaüzenet, vagy képlet, esetleg feltételes formázás vagy beviteli szabály. De ugyanígy törölhetjük az üres cellákat tartalmazó sorokat is.
A Video-ban mindez működés közben is látható
Sorok törlése cellatartalom szerint
