Telefonszámunk: 30-905-2076

Excel 2013

Számított feltétel szerinti megszámlálás egy tartományban

On 2013-01-04   /     /   Leave a comment

A feltétel szerinti megszámlálásra az Excel kínál két függvényt:

  • Darabteli (Countif)
  • Darabhatöbb (Countifs)

Ezekkel a függvényekkel az a probléma, hogy a megszámlálás feltételeként nem lehet képletet alkalmazni. De mit tegyünk, ha például meg kellene számolnunk  a páros számokat egy tartományban? A páros értékek megszámolásához képlet kell, amivel el tudjuk dönteni egy értékről, hogy páros-e. Erre több megoldás is van, de a legjobb a MARADÉK (MOD) függvény.

De hogy a feltételhez használt képletet hogyan építhetjük bele a megszámlálásba, az már nem olyan egyszerű. A példa alapján egyéb, hasonló probléma is megoldható, pl. az, hogy hány olyan sor van, ahol az egyik oszlopban kisebb vagy nagyobb érték van, mint egy másikban.

Számított feltétel szerinti megszámlálás – VIDEO

http://youtu.be/jZYb3KKV0Zc

Tovább... →
Back to Top

Logikai értékek változtatása 1-re vagy 0-ra

On 2013-01-04   /     /   Leave a comment

Az Excelben a logikai kifejezések és függvények eredménye alapértelmezésként „IGAZ” vagy „HAMIS” („TRUE”/”FALSE”).

Előfordulhat, hogy ezeket a megfelelő számra, azaz 1-re vagy 0-ra kell konvertálni, például azért, hogy megszámolhassuk őket. Az átalakítással sima összeadássá egyszerűsíthetjük a dolgot.

Az átalakításra leggyakrabban a dupla mínuszjelet használják, de tulajdonképpen bármilyen matematikai művelet megteszi, ami az eredeti értéket nem változtatja meg.

Legegyszerűbben úgy próbálhatjuk ki, hogy egy cellába beírjuk  a következőt:

Magyar: =- -IGAZ

Angol: =- -TRUE

Önmagában ennek persze semmi értelme, de képletekben alkalmazva már jól használható.

Néhány lehetőség az átalakításra (A1-ben van a logikai eredmény)

=–(A1)
=(A1)*1
=(A1)/1
=(A1)+0
=(A1)^1
=ABS(A1)

Például szeretnénk megtudni, hogy egy oszlopban (A1:A10) hány páros szám van.

  1. Egy segédoszlopban (B oszlop) vizsgáljuk  a párosságot
    =MARADÉK(A1;2)=0 (IGAZ/HAMIS eredményeket ad)
  2. Módosítjuk a dupla mínuszjellel: =–(MARADÉK(A1;2)=0) -> (0/1 eredményeket ad, a zárójel fontos!)
  3. Összeadjuk a segédoszlopot

Az egészet egy db speciális képlettel is meg lehet csinálni, de azt csak a VIDEO-ban lehet megnézni.

  • Páros számok megszámlálása egy tartományban

Tovább... →
Back to Top

Az Excel nemzetközi beállításainak lekérdezése

On 2013-01-04   /     /   3 hozzászólás

Gyakran előfordulhat, főleg Multi környezetben, hogy az Excel munkafüzeteket, alkalmazásokat több nyelvi környezetre is fel kell készíteni.

Az is előfordulhat, hogy az adott Excel nyelvi verzióban az érvényes beállításokat meg kell néznünk, hogy a képleteink vagy egyéb műveleteinket az Excel helyesen értelmezze.

Leggyakoribb nemzetközi eltérések szoktak lenni

  • Dátum, Idő, Pénznem karakterek és elválasztójelek
  • Lista szeparátor jel (A függvényargumentumok elválasztásához használjuk)
  • Ezres és tizedes elválasztó karakter

Összesen 45 ilyen nemzetközi beállítást tartalmaz az Excel. Készítettem egy táblázatot, amiben egy makró lefuttatásával Te is megnézheted, hogy a saját Excel-edben mik ezek a beállítások.

Töltsd le a fájlt, engedélyezd a makrókat, és nyomd meg a tetején a „Get values” gombot

Az Excel_nemzetközi_beállításai munkafüzet letöltése

proba

  • Excel nemzetközi beállításai Video

Tovább... →
Back to Top

A Windows felhasználó nevének lekérdezése

On 2012-12-18   /     /   Leave a comment

Ha szükségünk lenne arra a névre, amivel az aktuális felhasználó belépett a Windows-ba, akkor azt lekérdezhetjük a Windows környezeti változóiból. Ez a név nem feltétlenül egyezik meg az Excel-ben beállított felhasználónévvel.

[vb]

Sub winuser()

Dim user as string

user= Environ ("username")

msgbox user

End sub

[/vb]

Az összes környezeti változó listázása az ‘A’ oszlopba

[vb]

Sub environ_variables()

Dim i As Integer

Dim env_string As String

‘Kis takarítás, ha előzőleg már használták és elmentettés

Range("A:A").ClearContents

Cells(1, 1).Select

‘Az elsőtől kezdve az összes környezeti változó listázása az A oszlopba

i = 1

Do

env_string = Environ(i)

ActiveCell = env_string

ActiveCell.Offset(1, 0).Select

i = i + 1

Loop Until env_string = ""

End Sub

[/vb]

Eredmény

Látszik, hogy a listában változónév = érték formában kapjuk az adatokat. Egy konkrét változót a fenti „usermame” példához hasonlóan az Environ („valtozónév”) paranccsal kérdezhetünk le. A környezeti változók nevei nem érzékenyek a kis/nagy betűkre.

Tovább... →
Back to Top

Dokumentum útvonala vagy neve egy cellába, automatikus frissítéssel

On 2012-12-15   /     /   1 Comment

Azt talán sokan tudják, Excelben hogyan jelenítsék meg az aktuális dokumentum adatait a fejlécben vagy a láblécben. Néhány kattintással elhelyezhetjük az aktuális munkalap vagy dokumentum nevét, de akár a teljes elérési útvonalát.

De mit tegyünk akkor, ha egy cellába szeretnénk visszakapni az útvonalat vagy a fájl nevét?

A CELLA (Angolul CELL) függvény segítségével lekérhetjük az aktuális dokumentum teljes elérési útját. Argumentumként magyar verziónál a „filenév”,  angolnál a „filename” szót kell megadni.

Figyelem! A képletek másolásánál figyeljünk arra, hogy a függvény argumentumok elválasztó karaktere az általunk használt rendszerben micsoda. Előfordulhat, hogy a helyes működéshez módosítani kell ezt a karaktert, ami leggyakrabban vessző (Angol nyelvű operációs rendszernél) vagy pontosvessző (Magyar esetben).

Magyar

Teljes útvonal

=CELLA(„filenév”)

Csak a fájlnév

=KÖZÉP(CELLA(„filename”);SZÖVEG.KERES(„[„;CELLA(„filename”))+1; SZÖVEG.KERES(„]”;CELLA(„filename”))-SZÖVEG.KERES(„[„;CELLA(„filename”))-1)

Angol

Teljes útvonal

=CELL(„filename”)

Csak a fájlnév

=MID(CELL(„filename”);SEARCH(„[„;CELL(„filename”))+1; SEARCH(„]”;CELL(„filename”))-SEARCH(„[„;CELL(„filename”))-1)

Figyelem! Ha a dokumentum még nem volt elmentve, akkor a CELLA függvény hibaüzenetet ad!

A felhasznált CELLA / CELL függvény súgóját érdemes olvasgatni, nagyon érdekes dolgokat tud.

Tovább... →
Back to Top

Gyorsabb navigáció sok munkalap esetén

On 2012-12-05   /     /   Leave a comment

Ha sok munkalap van, akkor azok közül a nevük hosszától függően csak néhányat látunk, és a többit a Ctrl+PgUP/PgDown billentyűkkel érhetjük el, vagy a bal alsó sarokban lévő navigáló gombokkal tudjuk vízszintesen görgetni őket, hogy elérjük a hátrébb lévő, nem látható lapokat is.

Ha ezt a módszert túl lassúnak és körülményesnek tartjuk, próbáljuk ki, hogy az említett navigációs gombok bármelyikén jobb klikket nyomva, egy függőlegesen felnyíló menüben megkapjuk a munkalapok listáját.

Munkalap-navigacio-jobb-klikk

Tovább... →
Back to Top

Az univerzális Ctrl-ENTER

On 2012-12-04   /     /   Leave a comment

Az Excel egyik nagyon hasznos lehetősége a Ctrl-Enter. Több dologra is lehet használni, de végül is ez a több dolog mind ugyanaz, csak különböző szituációkban.

A Ctrl-Enter alapvetően a cellaszerkesztés lezárására való. Abban tér el a sima ENTER-től, hogy a szerkesztés lezárása után a szerkesztett cellán marad a cellakurzor. Tehát hatása ugyanaz, mint a szerkesztőléc bal oldalán a pipa ikon.

A Videóban a Crtl-Enter használatát lehet látni különböző szituációk esetén:

  • Egy cella szerkesztésének lezárása
  • Tartomány feltöltése egyforma adatokkal egy pillanat alatt
  • Képlet másolása formátum nélkül

  • A Ctrl+Enter használata

Tovább... →
Back to Top

Pontos sortörés a cellában – akár képlettel is

On 2012-12-02   /     /   3 hozzászólás

Az Excel mindegyik verziójában bekapcsolhatjuk a szöveg tördelését a cellaformázás párbeszéd ablakban, vagy az újabb verziókban a Kezdőlap szalagon a megfelelő nyomógombbal:

„Sortöréssel több sorba” vagy angolul „Wrap Text”

Ezzel a módszerrel az a probléma, hogy a sortöréseket automatikusan helyezi el, és mi nem tudjuk szabályozni. Pl.: Ha növeljük az oszlop szélességet, akkor a töréspontok elmozdulhatnak.

Barkácsmódszerrel úgy lehet a dolgot szabályozni, hogy a kívánt töréspontnál szóközöket teszünk a cellába, hogy ezzel kényszerítsük ki a sortörést.

Az igazi megoldás

A töréspont helyén a bal oldali ALT+ENTER kombinációt alkalmazzuk. Az így elhelyezett sortörés az oszlop szélességétől függetlenül mindig jó lesz.

Sortörés összefűzött szövegnél

Abban az esetben, ha a cella tartalmát több szöveg összefűzésével kapjuk, akkor is szabályozhatjuk a sortörés pozícióját, a következő módszerrel:

  1. A sortörés helyére befűzzük a KARAKTER(10) függvényt. Angol verzióban CHAR(10).
  2. Beállítjuk a „Sortörésseltöbb sorba” tulajdonságot a cellán.

Példa

Tovább... →
Back to Top

Szóközök vagy más karakterek megszámolása egy cellában

On 2012-12-02   /     /   Leave a comment

Ha meg szeretnénk számolni egy adott karakter előfordulásainak számát egy cellában, akkor a következő képletet használjuk.

A példa a szóközöket számolja, feltételezve, hogy az ‘A1’ cellában lévő szövegben számolunk.

Angol

=LEN(A1)-LEN(SUBSTITUTE(A1;” „;””))

Magyar

=HOSSZ(A1)-HOSSZ(HELYETTE(A1;” „;””))

Úgy működik, hogy az eredeti szöveg hosszából kivonja a szóközöktől mentesített szöveg hosszát. A szóköz mentesítés úgy történik, hogy lecseréli az összes szóközt az üres karakterre.

Ha előfordulhat az, hogy az eredeti szöveg elején vagy végén plusz szóközök vannak, és nem szeretnénk, hogy ez elrontsa az eredményt, akkor az eredeti cellát egy TRIM függvénybe érdemes ágyazni.

=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1;” „;””))

Ha nem szóközöket számolunk, akkor csak annyi a dolgunk, hogy a helyettesítő függvény második argumentumában kicseréljük a karaktert arra, amit számolni szeretnénk.

Amennyiben a keresett karakter egyszer sincs a cellában, a képlet nulla értéket ad.

Tovább... →
Back to Top

Vízszintes cellaegyesítés? SOHA!

On 2012-11-02   /     /   2 hozzászólás

(Excel2003, 2007, 2010, 2013)

Az Excel ma használt minden verziója lehetőséget ad arra, hogy vízszintes vagy függőleges irányba több cellát összevonjunk, pusztán a következő a teendőnk:

  1. Kijelölni az egyesítendő cellákat
  2. Megnyomni a megfelelő gombot az eszköztáron: Cellaegyesítés vagy Cellaegyesítés

Ha az első kijelölt cellában adat is volt, akkor azt az összevont cellákból keletkezett egyetlen cella közepére igazítja

Arra szoktuk legtöbbször használni, hogy táblázatok feletti címeket igazítsunk, de előfordulhat bárhol  a táblázatban, hogy élünk ezzel a formázási lehetőséggel.

Cellaegyesítés

Ebben a bejegyzésben megpróbállak benneteket erről lebeszélni.

Ha a táblázatunkat további adatfeldolgozásra szeretnénk használni, pl. szűrés, kijelölés, rendezés, PIVOT, stb.., akkor a táblázat adatterületén TILOS a fenti formázást alkalmazni. Egyszerűen csak azért, mert a vízszintesen egyesített cellák alatti részeket az Excel csak egyben tudja kezelni. Próbálj meg kijelölni egy oszlopban egy tartományt, amiben van legalább egy ilyen egyesített cella. Az eredmény valami hasonló lesz:

oszlop-kijeloles-egyesitett-cellakkal

Akkor mit használjunk ehelyett?

A cellaformázás menü (Ctrl+1) Igazítás lapján a vízszintes igazításnál válasszuk „A kijelölés közepére” (Center across selection) opciót.

kijeloles-kozepere

Ez nem egyesíti a cellákat, az adat marad a bal szélső cellában, de a látvány ugyanaz lesz, mint egyesítésnél, és a szegélyezésnél is szépen felismeri a helyzetet az Excel.

Tovább... →
Back to Top
« Előző oldal