Minden n-edik sor értéke
2017. szeptember 25.

Verzió: Excel 2013, 2010, 2007

Néha szükség lehet arra, hogy egy rendezett (strukturált) adatsorból előre meghatározott számú sorokból kapjuk meg az adatokat. Hogy érthetőbb legyek, mondjuk, egy összegző lista minden hatodik sorának értékét.

Ha a sorok közötti távolság (a növekedés) tényezője ismert, a dolog könnyen megoldható.
Valahogy így:

A módszer akkor lehet igazán hasznos, ha a forráslista több száz sorból áll.
A képlet a G3 cellában (ezt húztuk végig a G6-ig):
=ELTOLÁS($D$2;SOR(D2)*6-6;0)
Mi történik?
A megoldást két függvény adja, az ELTOLÁS() és a SOR().
Lássuk először az ELTOLÁS-t.
A függvény megadott magasságú és szélességű hivatkozást ad eredményül, egy másik hivatkozástól számított megadott számú sornyi és oszlopnyi távolságra.
Az eredményként visszaadott hivatkozás hivatkozhat egyetlen cellára vagy egy cellatartományra.
Megadhatjuk a sorok és az oszlopok számát.
Az ELTOLÁS valójában nem tol el (angolul offset egyetlen cellát sem és nem módosítja a kijelölést sem, hanem csak egy hivatkozást ad vissza.
Az ELTOLÁS függvényt minden olyan függvénnyel használhatjuk, amely hivatkozást vár argumentumként.
Szintaxisa:
=ELTOLÁS(hivatkozás;sorok;oszlopok;[magasság];[szélesség])
Hivatkozás: az a hivatkozás, amelyhez képest az eredmény hivatkozás helyzetét az argumentumok meghatározzák.
A példában ez a D2 cella, az Összeg.
A hivatkozásnak egyetlen cellára vagy egy cellatartományra kell vonatkoznia, ellenkező esetben a függvény az #ÉRTÉK! hibaértéket adja eredményül. (Ezért fontos itt az abszolút hivatkozás, hiszen a képletet több sorra [aG6-ig] is kiterjesztjük.)
Sorok: az eredmény bal felső cellája és a hivatkozás közötti függőleges távolság a sorok számában kifejezve.
Ha például az argumentum értéke 3, akkor az eredményül kapott hivatkozásban a bal felső cella 3 sorral lesz a hivatkozás alatt.
A sorok értéke lehet pozitív (a kezdőhivatkozás alatt) vagy negatív (a kezdőhivatkozás felett).
Példánkban ezt az argumentumot a SOR() függvénnyel fejezzük ki, rögtön szó esik róla.
Oszlopok: az eredmény bal felső cellája és a hivatkozás közötti vízszintes távolság az oszlopok számában kifejezve.
Ha például az argumentum értéke 3, akkor az eredményül kapott hivatkozásban a bal felső cella a bal felső cella 3 oszloppal jobbra lesz a hivatkozás mellett.
Az oszlopok értéke lehet pozitív (a kezdőhivatkozástól jobbra) vagy negatív (a kezdőhivatkozástól balra).
Példánkban az érték 0, hiszen az éves összegek ugyanabban az oszlopban (a D oszlopban) találhatók, azaz nincs "eltolás":
Magasság: az eredmény hivatkozás magassága a sorok számában mérve.
Nem kötelező megadni.
Szélesség: az eredmény hivatkozás szélessége az oszlopok számában mérve.
Nem kötelező megadni.
Mivel a példában egyetlen celláról van szó, ezt a két argumentumot elhagytuk.
A SOR() függvény egy hivatkozás sorának számát adja meg.
A hivatkozás: az a cella vagy cellatartomány, amelyben meg kell állapítani a sor számát.
Ha a hivatkozás argumentumot nem adjuk meg, akkor a függvény annak a cella sorának számát adja meg, amelyben a függvény található.
Példánkban ez a D2, azaz a második sor.
A megoldás menete. A szemléltetés kedvéért először a G3 cella (Észak összesen):
=ELTOLÁS($D$2;SOR(D2)*6-6;0)
ELTOLÁS($D$2;SOR(D2)*6-6;0)
ELTOLÁS($D$2;2*6-6;0)
ELTOLÁS($D$2;12-6;0)
ELTOLÁS($D$2;6;0)
2 287 000

Ahogy szó volt róla, az ELTOLÁS első argumentumát a SOR() függvény alkalmazásával oldottuk meg. Mi történik?
Mivel az egyes régiók összegei rendre 6 sornyi távolságra vannak egymástól (D8, D14, D20 és D26), és az első is pont ugyanennyire a hivatkozástól (D2), az aktuális sor számát megszorozzuk (itt) 6-tal, majd ebből kivonunk 6-ot. Így kapjuk meg az ELTOLÁS sorok argumentumának értékét.
EzÉszak esetében 12 - 6 = 6.
A 6. cellában (D8) lévő érték pedig 2 287 000.
Nézzük a következőt (G4: Dél összesen):
=ELTOLÁS($D$2;SOR(D3)*6-6;0)
ELTOLÁS($D$2;SOR(D3)*6-6;0)
ELTOLÁS($D$2;3*6-6;0)
ELTOLÁS($D$2;18-6;0)
ELTOLÁS($D$2;12;0)
1 215 000

Az aktuális sor a D3, és innen minden megy a maga módján.
Még egyszer: fontos, hogy a lista strukturált legyen, így tetszés szerint változhat a szorzó.

A VÁLASZTás szabadsága
2017. szeptember 18.

Verzió: Excel 2013, 2010, 2007

Néha előfordulhat, hogy azt szeretnénk, ha a felhasználó (akár a főnökünk, akár a munkatársunk) a képletekben ugyanazt a "matekot" használja, amit mi akarunk.
Erre kiváló megoldást kínál a VÁLASZT() függvény.

Egy nagyon egyszerű példa: tervezünk.

A folyamatot olyannyira szeretnénk leegyszerűsíteni, amennyire csak lehet. A negyedéves, majd a belőlük képzett éves összeg jövő évi megtervezése nem egyszerű. Azonban ha ciklikus, rendszeresen előforduló tételekről van szó (így pl. költségek), melyek zömében az előző évi tényleges adatokon alapulhatnak, megkönnyíthetjük a munkát.
Azt akarjuk, hogy termékenként egységes elvek alapján készüljön a terv, és lehetőség legyen a várható legjobb változat kiválasztására.
A példában 5 termék költségeinek tervezését látjuk, mint jeleztem roppant leegyszerűsített módon.
Három választási lehetőségünk van:
• 1 – Az előző évi költségek, de negyedévenként egyenlően;
• 2 – Az előző évi költségek 10%-os emelése;
• 3 – Az előző évi költségek 10%-os csökkentése.
Erre alkalmas a VÁLASZT() függvény alkalmazása.
Lássuk a függvényt:
A függvény az érték argumentumok közül az index sorszámút adja vissza.
Szintaxis: =VÁLASZT(index;érték1;érték2;...)
Az index: a kiválasztott argumentumot határozza meg.
Az érték1;érték2;...: 1 és 254 közötti egész szám, képlet vagy egy 1 és 254 közötti számot tartalmazó cellára utaló hivatkozás.
Az argumentumok lehetnek számok, cellahivatkozások, nevek, képletek, függvények vagy szövegek.
Példa:
A következő számokat színekkel szeretnénk helyettesíteni az alábbi táblázatban.
1- vörös; 2 - kék; 3 - zöld; 4 - barna

Értékek a cellában:
A VÁLASZT első argumentuma 1, így az első értéket kapjuk eredményként.
Ha a másodikat választjuk, a D2 értékét kapjuk

A VÁLASZT egyik fő hasznosítási területe a forgatókönyv- (vagy szcenárió-) elemzés.
Vissza a példához. A függvény elvi alapja:
VÁLASZT(A1;Egyenletes;Nő10;Csökken10),
melyben
A1 a cella, mely a módszer "kódját" tartalmazza (itt H7 és H11 között);
Egyenletes, ennek a képletnek a kódja 1, itt az első terméknél $G4/4, hiszen minden negyedév egyenlő;
Nő10, ennek a képletnek a kódja 2, itt a második terméknél C5*1,1, hiszen 10%-os növekedésről beszélünk;
Csökken10, ennek a képletnek a kódja 3, itt a harmadik terméknél D6*0,9, hiszen 10%-os csökkenésről beszélünk;

A felhasználó beírja a választott kódot, és kész. A tervezési módszerek számának csak a képzelet szab határt.
Persze új szintre emelhetjük a dolgot, ha az Érvényesítéssel előre definiált módon a felhasználónak csak ki kell választania a módszert.

A képlet (I4):
=VÁLASZT(HOL.VAN($H4;$B$12:$B$14;0);$G4/4;C4*1,1;C4*0,9)
A VÁLASZT függvény index argumentumát a HOL.VAN függvénnyel határoztuk meg.
A függvényről:
A függvény egy olyan elem tömbben elfoglalt relatív pozícióját adja vissza, amely megadott értékkel megadott módon egyezik. Kereshetünk számot, szöveget vagy logikai értéket, hivatkozást ilyen értékre. A megtalált elem helyét adja meg, és nem magát az elemet.
Szintaxis: =HOL.VAN(keresési_érték;tábla;egyezés_típus)
A keresési_érték: az az érték, amelynek segítségével a táblázatban a keresett érték megtalálható.
A tábla: azokat az értékeket tartalmazó összefüggő cellatartomány, amelyek között a függvény a keresési_értéket keresi.
Az egyezés_típus: értéke -1, 0 vagy 1.
Itt most csak a most alkalmazott típus:
Ha az egyezés_típus értéke 0, a függvény az első olyan értéket keresi meg, amely pontosan egyenlő a keresési_értékkel.
A táblának nem kell rendezettnek lennie.
Hogy néz ki ez esetünkben?
A keresési_érték: a H4:H8 tartományban az Érvényesítéssel megadott érték, itt H4.
A tábla: a kódokat tartalmazó tartomány (1, 2, 3: B12:B14).
Az egyezés_típus: pontos egyezést akarunk.
Innen már az ismert VÁLASZT érték argumentumai következnek.
A gyakorlatban:
=VÁLASZT(HOL.VAN($H4;$B$12:$B$14;0);$G4/4;C4*1,1;C4*0,9)
VÁLASZT(HOL.VAN(1;$B$12:$B$14;0);$G4/4;C4*1,1;C4*0,9)
VÁLASZT(1;$G4/4;C4*1,1;C4*0,9)
VÁLASZT(1;2541000/4;C4*1,1;C4*0,9)
VÁLASZT(1;635250;C4*1,1;C4*0,9)
635250

Még elegánsabb, ha definiált nevekkel oldjuk meg a feladatot. Mivel itt most a VÁLASZT() függvény lehetőségéről volt szó,ezt most elhagyom.

Üres cellák kiemelése
2017. szeptember 11.

Verzió: Excel 2013, 2010, 2007

Ritkán írok a feltételes formázás lehetőségeiről. Nem csupán azért, mert "túlszínezi" a lapot, esetleg mert fölösleges csicsa (gyakran találkozom vele), mert lelassítja a munkát (volatilis), ez utóbbi amúgy sem jelentős; egyszerűen nem jut eszembe. Most a hatékony munkavégzés egyik lehetséges szituációjában alkalmazzuk.

Ha kapunk egy adatbázis munkatársunktól vagy partnerünktől, vagy éppen az internetről töltöttünk le egy anyagot, hasznos dolog, ha felhasználás előtt néhány szempontból ellenőrizzük. Így megállapíthatjuk, vannak-e benne ismétlődések, számítási vagy egyéb hibák, hiányzó adatok, melyek további hibákat eredményezhetnek a feldolgozás során, és ekkor már nehezebb megtalálni a baj forrását.
Egy ilyen lehetőség, hogy vizuálisan kiemeljük az üres cellákat.
Az adatokban található üres cellák megjelenésének több oka is lehet. Így például:
hiányzik az adat;
véletlenül törölték az adatot;
az alkalmazott képlet eredménye üres cella.
Egy kisebb adatbázisban könnyű észrevenni, megtalálni ezeket a cellákat, több száz sor és oszlop esetében ez már nehézkesebb és (ez a lényeg!) megbízhatatlanabb.
Már többször volt szó üres cellák kijelöléséről.
A megoldásról röviden:
1. Jelöljük ki a teljes tartományt.
2. Kezdőlap > Szerkesztés > Keresés és kijelölés > Irányított kijelölés
Vagy egyszerűen nyomjuk meg a Ctrl + G billentyűkombinációt.(Megjelenik az Ugrás párbeszédablak. Válasszuk: Irányított.)
(Innen egyforma a megoldás.)
3. Jelöljük be az Üres cellák gombot. Nyomjuk: OK.
Az üres cellák "kijelölődtek".
És innen már sok minden tehetünk (törölhetjük őket, írhatunk beléjük stb.), viszont hosszabb időre nem tudjuk megtartani a kijelölést, ezért csak egy gyors alkalmazásra hasznos. Továbbá, (min majd látjuk) nem dinamikus.
Vegyük példaként egy kis adatbázist.

Látható, van néhány üres cellánk.
Az üres cellák kijelölése Feltételes formázás alkalmazásával
1. Jelöljük ki a táblázatot.
2. Kezdőlap > Stílusok csoport > Feltételes formázás > Új szabály

3. A feljövő Új formázási szabály párbeszédablakban válasszuk a Csak az adott tartalmú cellák formázása opciót.

4. A párbeszédablak alsó részében (Szabály leírásának szerkesztése a Csak az alábbi feltételt teljesítő cellák formázása első lenyílójában válasszuk: Üres cellák.

5. Pontosítsuk a feltételt (hogyan akarjuk kijelölni az üres cellákat). > Formátum

6. OK.
Kijelöltük az összes üres cellát.

Fontos! A feltételes formázás dinamikus. Ez azt jelenti, ha most kitöltünk egy üres cellát, eltűnik a választott háttér, és fordítva: ha törlünk egy adatot, a cella "kijelölődik".
(Nagy adatmennyiség esetén ez lassítja a számolás.)

Szeletelő, a lenyílók alternatívája
2017. szeptember 4.

Verzió: Excel 2013

Az Excel 2013 megjelenése óta már az Excel táblázatokban is alkalmazhatunk Szeletelőket az adatok szűrésére.
A Szeletelők valódi alternatívát jelentenek a hagyományos Érvényesítéssel megoldott kapcsolt (egymástól függő) lenyílók kiváltására. Ráadásul roppant egyszerű létrehozásuk.

Ha azt szeretnénk, hogy az alkalmazónak lehetősége legyen kiválasztani egy elemet egy listából, többnyire az Érvényesítés megoldásainak valamelyikét alkalmazzuk. Ha lehetőségünk van egy második elem választására, mely függ az elsődlegesen kiválasztott elemtől (például Márka > Típus) gyakran használjuk az Érvényesítés lenyílóit, mint itt.
Néhány képlet segítségével ez megoldható, de esetleg macerás.

Szerencsére van megoldás.

Akkor most lássuk a megoldás menetét.
Az adatokat Excel táblázatban készítsük el.
Ha nem így történt (pl. kapott adatok esetén), jelöljünk ki egy cellát a tartományban, majd Kezdőlap > Beszúrás > Táblázat.
Az eredmény:

Az Excel automatikusan elnevezi táblázatunkat (Táblázat1, de tetszés (és szükség) szerint átnevezhetjük: Táblázateszközök > Tervezés > Táblázat neve.

Azt szeretnénk, ha a felhasználó kiválaszthatná a Régiót, majd a Nevet, végül az ID-t.
Tehát három szeletelőre lesz szükségünk:
Beszúrás > Táblázat > Táblázateszközök > Tervezés > Szűrők csoport > Szeletelő.
A megjelenő párbeszédablakban kiválaszthatjuk, melyik oszlopokat akarjuk szűrni. Esetünkben ez a Régió, a Név és az ID.

Az Excel három vezérlőt szúrt be a lapra.

Az ismert Kivágás > Beillesztés megoldással bárhová helyezhetjük a Szeletelőket, itt most a szeletelő lapra kerültek, itt átméretezhetjük, elrendezhetjük őket.
Így:

Kattintsunk az első vezérlő valamelyik opciójára (pl. Észak). Látható, hogy a másik kettő elemei frissülnek, az érvényes opciók alapján.

Ha azt szeretnénk, hogy az érvénytelen (nem odaillő) opciókat ne lássuk, jobb egérgombbal kattintsunk a Szeletelőbe,
Válasszuk a Szeletelő beállításai opciót,

majd jelöljük be az Adatokat nem tartalmazó tételek elrejtése jelölőnégyzetet.
Az inaktív halványulás helyett most nem látjuk a nem valid tételeket.
Látható, hogy rengeteg beállítási lehetőségünk van, érdemes kísérletezni.
Elkészült a Szeletelő, lássuk, mit tudunk még kezdeni vele.
Ha azt akarjuk, hogy a mini kimutatásokat cellákban is megkapjuk (olyan jó öreg excelesen), szükségünk lesz egy segédoszlopra.
Segédoszlop
A segédoszlop célja az, hogy azonosíthassuk a szűrő alkalmazása után a látható és a rejtett sorokat.
Ehhez az ÖSSZESÍT() függvényt használjuk.
Fontos!
Mielőtt létrehoznánk a segédoszlopot, minden szűrőt törölni kell:
Jelöljük ki a táblázatot, Adatok > Rendezés és szűrés > Szűrők törlése.

Szúrjunk be egy új oszlopot, a fejléc legyen pl. Látszik
Az első sorba írjuk be a következő képletet:
=ÖSSZESÍT(3;5;[@Régió])
A képlet az ÖSSZESÍT() függvényt használja, részletesen itt volt róla szó.
Itt most csak annyit, a függvény lista vagy adatbázis összesítését adja eredményül.
A képletben a
3 jelentése: a függvény a DARAB2 függvény használja, azaz megszámolja a nem üres cellákat;
az 5 jelentése: a rejtett sorok mellőzése;
a [@Régió] megmondja, melyik tartományt számolja.
Ennek alapján az Excel 1-et ad a látható celláknak és nullát a rejtetteknek. Itt most nincs szűrés:

Ha manuálisan vagy a Szeletető gombjára kattintva szűrünk, rejtett sorok "keletkeznek", és értékük 0 lesz.
És itt az idő, hogy megkapjuk a releváns adatokat.
Készítsünk egy kis táblázatot:

Az egyes adatokat a következő képlet megfelelő alkalmazásával kapjuk meg, így a névhez tartozó forgalomét:
=INDEX(Táblázat1[Forgalom];HOL.VAN(1;Táblázat1[Látszik];0))
A képletben a
Táblázat1[Forgalom] a táblázat Forgalom oszlopának strukturált hivatkozása;
a HOL.VAN(1;Táblázat1[Látszik];0) megadja az első látható sor pozícióját, melyben az 1 a megkeresendő érték (a látható sorok),
Táblázat1[Látszik] a segédoszlop a képlettel;
0 a pontos egyezés.
Ugyanígy kapjuk meg a többi értéket, a képlet első részében változik a táblázat oszlopa.
Az INDEX/HOL.VAN kombóról nemrég volt szó.
A Szeletelő látványos és hasznos megoldás, most már a táblázatokban is alkalmazható (Excel 2013+), nem csupán a Kimutatásokban.

INDEX(), HOL.VAN() egy táblázatban, no meg az INDIREKT()
2017. augusztus 28.

Verzió: Excel 2013, 2010, 2007

Egy hagyományos keresés az INDEX() és a HOL.VAN() függvényekkel nem nagy dolog. Ám egy kis csavarral, az INDIREKT() bevonásával már érdekesebb a dolog.

Íme agy kis adatbázis értékesítők teljesítményeivel.

Szeretnénk látni az eredményeket személyekre lebontva egy lenyíló segítségével (Érvényesítés), természetesen termékenként.
Valahogy így:

A termékek listáját felvisszük az A10:A21 cellákba.
A már ismert INDEX/HOL.VAN/HOL.VAN kombót alkalmazom. A két függvény hihetetlen erejéről már többször volt szó, így most csak röviden.
A képlet INDEX részéhez az adattartomány (tömb) a táblázat értékesítési adatokat tartalmazó tartománya, a B2:M7 cellatartomány.
Itt alkalmazzuk a HOL.VAN függvényt, hogy megkapjuk a sor és oszlop metszéspontját:
=INDEX($B$2:$M$7;HOL.VAN($B$9;$A$2:$A$7;0);HOL.VAN(A10;$B$1:$M$1;0))
És így:

Emlékeztetőül az INDEX() szintaxisa:
Táblázatban vagy tartományban található érték hivatkozását vagy értékét adja vissza.
Két alakja van:
a tömbös forma, amely adott cella vagy cellatömb értékét adja eredményül, valamint
a hivatkozásos forma, amely adott cellák hivatkozását adja eredményül.
=INDEX(tömb;sor_szám;[oszlop_szám])
Tömb: cellatartomány vagy tömbállandó.
Sor_szám: a tömbben annak a sornak a száma, amelyikből az értéket meg szeretnénk kapni.
Oszlop_szám: a tömbben annak az oszlopnak a száma, amelyikből az értéket meg szeretnénk kapni.
A HOL.VAN():
A függvény egy olyan elem tömbben elfoglalt relatív pozícióját adja vissza, amely megadott értékkel megadott módon egyezik. Kereshetünk számot, szöveget vagy logikai értéket, hivatkozást ilyen értékre. A megtalált elem helyét adja meg, és nem magát az elemet.
Szintaxis: =HOL.VAN(keresési_érték;tábla;egyezés_típus)
A keresési_érték: az az érték, amelynek segítségével a táblázatban a keresett érték megtalálható.
A tábla: azokat az értékeket tartalmazó összefüggő cellatartomány, amelyek között a függvény a keresési_értéket keresi.
Az egyezés_típus: értéke -1, 0 vagy 1.
Itt most csak a most alkalmazott típus:
Ha az egyezés_típus értéke 0, a függvény az első olyan értéket keresi meg, amely pontosan egyenlő a keresési_értékkel.
A táblának nem kell rendezettnek lennie.
Hogy néz ki ez esetünkben?
Az Excel először a képletbe ágyazott két HOL.VAN eredményét számítja ki.
Az első HOL.VAN elemben a keresési_érték az Érvényesítéssel kiválasztott név a B9 cellában (itt Adél).
(Ez az INDEX sor_szám argumentuma.)
A tábla az A2:A7 tartomány. Látható, hogy a fejléc, az 1. sor nem a tábla része.
A második HOL.VAN elemben a keresési_érték az első Termék a függőleges tartománnyá alakított terméklistában, az A10 cellában.
(Ez az INDEX oszlop_szám argumentuma.)
A tábla a B1:M1 tartomány, azaz a fejléc.
Végül megkapjuk az INDEX eredményét:
=INDEX(B2:M7;1;1)
A tömb 1. sorának és 1. oszlopának metszéspontjában a 19 995 érték áll.
A képletet végighúzzuk. Ügyeljünk a cellahivatkozásokra, az abszolút hivatkozások használatának elmulasztása végzetes.
(Kizárólag a Termékek oszlopában nem, mert a képlet lefelé történő másolásának ez a lényege.)
Bizonyára nem az INDEX/HOL.VAN/HOL.VAN kombó lesz a leggyakrabban használt képletünk, de sokszor nagyon hasznos.
És akkor most a csavar.
Hozzunk létre egy Excel táblázatot ugyanezekkel az adatokkal. Legyen a neve: SalesData.

A megoldás (oszloppá alakítás,Érvényesítés) ugyanaz. Az ábrán Lilla eredményei.
Az oszlop meghatározását nem a második HOL.VAN függvénnyel végezzük, hanem az INDIREKT-tel.
(Előre definiáltuk a tartományt az INDEX számára, az A oszlopba rendezett termékeket pedig táblázatunk megfelelő oszlopai hivatkozásaiként hasznosítjuk.)
Így a képlet:
=INDEX(INDIREKT("SalesData["&A10&"]");HOL.VAN($B$9;SalesData[NÉV];0))
Rendben, az INDIREKT volatilis függvény, de kis és közepes adatbázisoknál ez nem lehet gond.
És nem kell az F4 billentyűvel bajlódni (megfeledkezni róla).
(Az INDIREKT() függvényről nemrég volt szó, a lényeg, hogy a függvény eredménye a szövegként megadott hivatkozás.)

Vágólap trükk
2017. augusztus 21.

Verzió: Excel 2013, 2010, 2007

Az Excelben (és a többi Office termékben) egyszerűen lehet elemeket (egy vagy több helyről) másolni, majd akár többször beilleszteni egy adott munkalap különböző területeire.

A példában négy adattartományunk van.

Szeretnénk őket kimásolni és különböző helyekre beilleszteni, ha lehet, akár többször is.
A megoldás menete:
1) Jelöljük ki az elsőnek másolandó tartományt (B2:D12), de a szokásos Ctrl + C helyett a billentyűkombináció legyen Ctrl + C + C (kétszer C). Ezzel megnyitjuk a Vágólapot.
Nem nyílt meg. Hogyan tudjuk ezt megoldani?
Kezdőlap > Vágólap. Kattintsunk a jobb alsó sarokban lévő nyílra

Megjelenik a Vágólap munkaablak.
A Beállításokra kattintva jelöljük be az Office vágólap megjelenítése a Ctrl C kombináció kétszeres lenyomására opciót.

Így a megfelelő beállítás út megkapjuk a kívánt eredményt (a kétszeres Ctrl + C után).

Ezt követően a többi tartományt a szokásos módon (Ctrl + C) egyenként másoljuk. Ezt kapjuk:

Most megnyithatjuk az(oka)t a munkalapo(ka)t, amely(e)kre beilleszteni akarjuk a tartományokat. Álljunk a kurzorral a megfelelő cellába, majd kattintsunk a Vágólapon a kiválasztott elemre.
A kiválasztott elemeket többször is beilleszthetjük.
Ha készen vagyunk, kattintsunk az Összes törlése gombra.

Érvényesítés: kapcsolt listák
2017. augusztus 14.

Verzió: Excel 2013, 2010, 2007

Olykor előfordul, hogy olyan lenyíló listát szeretnénk készíteni, melyben a megjelenő adatok valamilyen módon függenek egymástól. Azaz: az első lista alapján lehessen választani a másodikból.

Így például azt szeretnénk, ha (itt) három termékcsoport közül lehessen választani, és a második lenyílóban a kiválasztott csoport elemei jelenjenek meg.
A három termékcsoport (a forrás lapon: Zöldség, Gyümölcs, Csonthéjas.
A termékek listája:

Látható, a termékcsoportok a B1:D1 cellában vannak, az egyes termékek alattuk.
A legcélszerűbb, ha elnevezzük a tartományokat:
CsoportLista: B1:D1
TermékLista1: B2:B25 (a zöldségek)
TermékLista2: C2:B25 (a gyümölcsök)
TermékLista3: D2:B25 (A csonthéjasok)
(Az egyszerűség kedvéért mindegyikből csak néhány.)
A definiált nevek a forrás lapon:

A lenyílókat az Adatok > Adateszközök > Érvényesítés megoldással új munkalapon helyezzük el.
A B2 cellába kerülnek a termékcsoportok

A C2 cellába (ide jönnek az egyes csoportok termékei) a Forrás mezőbe írjuk be a következő képletet:
=INDIREKT("TermékLista"&HOL.VAN(B2;CsoportLista;0)).
Lássuk a képletet:
Az =INDIREKT() eredménye a szövegként megadott hivatkozás.
Azért alkalmazzuk, hogy az Érvényesítés forrása ezt tartományként és ne szövegként értelmezze.
A =HOL.VAN(B2;CsoportLista;0)) megkeresi a B2 cellában álló értéket (a CsoportListán belül)
A harmadik argumentum (0) jelzi, hogy csak pontos egyezést fogadunk el.
Az eredmény egy számként kifejezett pozíció (hely), azaz ha a CsoportLista tartományban megtalálja a B2 cellát, az eredmény 1.
Az & szimbólum összefűzi a két szöveget.
Ha mindhármat összerakja, és ha a B2 cellában a szöveg Gyümölcs, a HOL.VAN eredménye 2 (mivel ez a CsoportLista második cellája.
Így a "TermékLista"&HOL.VAN(B2;CsoportLista;0) = a TermékLista2 szöveg, így az
=INDIREKT("TermékLista") aTermékLista2 definiált nevű tartomány, azaz azoknak a termékeknek a listája, melyeket a Gyümölcs termékcsoport alá tettünk.

A tartományok neveit megváltoztathatjuk, ugyanígy méretüket is.

Vezérlő, másképp
2017. augusztus 7.

Verzió: Excel 2013, 2010, 2007

Előfordul (és milyen gyakran!), hogy az adatokat nem egységes formában, szerkezetben, azaz nem konzisztensen kapjuk meg. Legegyszerűbb példa az "x Ft + 27% áfa" vagy "az összeg tartalmazza az áfát" megoldás.

Egy egyszerű módszerrel láthatjuk a "bruttó-nettó, jutalékkal vagy nélküle stb." adatokat.
A példában azt mutatom be, hogyan lehet könnyen a kétféle összeget megjeleníteni.

Látjuk, hogy a C7 cellában a képlet:
=(C5+C6)*0,27*$C$2
A képletben látjuk, hogy az áfa a két tétel (anyag és díj) 27 százaléka, és ez szorozzuk a vezérlő értékével.
Azaz:
=(162 600 + 148 900)*0,27*1
Természetesen, ha a C2 cella nem tartalmaz értéket vagy 0, az eredmény:

Viszont nem látjuk a nullákat. Eltüntetésük legegyszerűbb módja:
Excel 2010+:
Fájl > Beállítások > Speciális > E munkalap megjelenítési beállítása
Vegyük ki pipát a Nulla megjelenítése a nulla értékű cellákban jelölőnégyzetből.

Excel 2007:
Windows gomb > Excel beállításai > Speciális
Innen megegyezik a fenti eljárással.
Persze képlettel is megoldhatjuk.
Másik vezérlő
Az 1 vagy zéró helyett használható szöveges érték is, röviden I az áfával együtt, N áfa nélkül.
A képlet összetettebb:
=HA($C$2="I";(C5+C6)*0,27;0)
Magyarul:
Ha a C2 cellában az I áll, aC5 és C6 összegét szorozd meg 0,27-tel, egyébként az eredmény 0.
Javíthatjuk, szépíthetjük, pl. a szorzót (az áfa kulcsot) külön cellába írva:

Zérókat is tartalmazó listák szűrése
2017. július 31.

Verzió: Excel 2013, 2010, 2007

Hogyan szűrjük helyesen a zérókat is tartalmazó listákat? Kellemetlen meglepetések érhetnek, ha a táblázatok beépített szűrőit (lenyílókat) nem megfelelően alkalmazzuk.

A lista egy "zöldséges" éppen kapható termékeinek árát sorolja fel régiónként. (Hívjuk így a piacokat.)
A nem kapható termékek ára 0.

Az AutoSzűrő vagy a beépített összehasonlító operátorok (így pl. "nagyobb, mint", "toplista") használatával megjeleníthetjük a kívánt adatokat úgy, hogy közben elrejthetjük a többi adatot.
Esetünkben a nem kapható termékeket, melyek ára 0 (zéró), nem akarjuk látni, azaz "kiszűrjük" a listából.

Ez az első lehetőség. SOHA NE ALKALMAZZUK!
De miért? Hiszen egyszerű. Kiszűrjük (elrejtjük) a nullát tartalmazó sorokat, azaz csak a nullánál nagyobb értékeket látjuk
Remekül is működik, ha csak ezt az egy oszlopot szűrjük.
A komoly gond akkor jelentkezik, ha több oszlopra alkalmazzuk a szűrőt.
Lássuk:
1. Szűrjünk a C oszlopban a Nyugat régióra.

2. Most következzen az E oszlop, "felejtsük a nullákat", a nem kapható termékeket.

Kivettük a jelölést a 0 melletti jelölőnégyzetből.
Minden rendben van. (Eddig.)
Látjuk azokat a sorokat, melyek a Nyugat régióban a 0-nál nagyobb áru termékeket tartalmazzák. Hiszen ezt akartuk.

Lépjünk tovább. Töröljük a szűrőt a régiókból, így az összes régiót látjuk.

Nyilván abból indulok ki, hogy az összes régió zérót nem tartalmazó sorait kapom meg.
Micsoda tévedés!

Több sorra számítottunk (a példa 70 sorból áll).
Ha rákattintok az Egységár szűrőre, látható, néhány ár nincs bejelölve, azaz rejtettek, jóllehet az ár nem zéró.

Mi történt?
Mint látható, a listákban az adott oszlop egyedi értékei jelennek meg, ezeket bejelölhetjük vagy éppen kivehetjük a listából. Ha egy vagy több elemet veszünk ki a listából (töröljük a pipát a jelölőnégyzetből), létrehoztuk a szűrési kritériumot, ez viszont az összes bejelölt elemre érvényes.
A kritérium nem veszi figyelembe a jelöletlen elemeket.
Amikor kivettük a zérót a Nyugat régió egységárai közül, ez csak a látható sorok számait (árait) veszi figyelembe.
Ha töröljük a szűrőt a Régiók oszlopból, az egységárra vonatkozó szűrő (E oszlop) nem változik meg.
Ez azt jelenti, hogy továbbra is szűrőt alkalmazunk (nullánál nagyobb értékek a Nyugat régióban).
E számok némelyike esetleg megtalálható más régiókra vonatkozó sorokban is, de így meglehetősen értelmetlen zagyvaságnak tűnik.
Mi a megoldás?
A leghelyesebb, ha a Számszűrőket alkalmazzuk.

A Számszűrők almenüjében számtalan opciót találunk (Egyenlő; Nem egyenlő; Nagyobb, mint; Toplista stb.)
E parancsok zöme a hagyományos AutoSzűrő menüt nyitja meg.
Két kritériumot határozhatunk meg az ÉS, továbbá a VAGY feltétellel.

Ebben az esetben az előbb ismertetett listát nem használja az Excel.
(Nem kell visszalépnünk, és újra kiválasztani a jelölőnégyzeteket.)
Összefoglalva:

INDEX() trükk
2017. július 24.

Verzió: Excel 2013, 2010, 2007

Mi van akkor, ha egy folyamatosan bővülő listában (mondjuk, napi bevételek) szeretnénk kiszámítani az utolsó érték százalékos növekedését vagy csökkenését a legkisebb értékhez viszonyítva?
Lehet ezt persze manuálisan is elvégezni, de minek? Itt az INDEX() függvény, automatikusan frissítünk ez utolsó érték beírásakor.

A lényeg, a képlettel megtaláljuk az utolsó értéket (itt a C oszlopban), majd elosztjuk a lista legkisebb értékével.

Szemléltetésül egy nagyon egyszerű példa. A lista a C3 cellában kezdődik.
A legkisebb érték: 10 (C5).
Az utolsó érték 70 (C11), ami éppen hétszerese 10-nek, azaz a növekedés 600%.
Hogyan működik a képlet?
=INDEX(C:C;DARAB2(C:C)+1)/MIN(C:C)-1
Mivel a lista bővül, a tartomány/tömb a teljes oszlop.
Így a függvény megszámolja a nem üres cellák számát, majd hozzáad 1-et, mert egy üres cella van a tömbben (C1).
Ezt elosztjuk C oszlopban található legkisebb értékkel (ez a 10 a C5 cellában), majd levonunk 1-et, hogy lássuk a változást.
(A cellát természetesen Százalék formátummá alakítjuk.
A "menet":
INDEX(C:C;DARAB2(C:C)+1)/MIN(C:C)-1
INDEX($C:$C;10+1+1)/MIN(C:C)-1
INDEX($C:$C10;11/MIN(C:C)-1
70/MIN(C:C)-1
70/10-1
7-1
600%

Ha azt szeretnénk, hogy a százalékos változást a lista első értékéhez viszonyítsuk (pl. hónap vagy hét első napja), a megoldás:

(A listát az 50 nyitja.)
A képlet:
=INDEX(C:C;DARAB2(C:C)+1)/$C$3-1
Az INDEX() nem volatilis, érzékeny függvény, nem számol "újra" az adatok változtatásakor, mint pl. az ELTOLÁS(), INDIREKT(), SOROK(), OSZLOPOK(), MA() stb.
A felhasznált függvényekről már többször volt szó. Itt az a trükk, hogy az INDEX() függvényben a sor_szám argumentumot a DARAB2() függvénnyel határoztuk meg.

Oldaltörések
2017. július 17.

Verzió: Excel 2013, 2010, 2007

Küldtünk/kaptunk már olyan Excel fájlt, mely kinyomtatva 20 oldal, és a felén csak 1-2 oszlop adata van? Bizony, bizony, bárhonnan is nézzük, ez kínos, bosszantó.

Hogy ez ne fordulhasson elő, a legtöbben az Oldaltörés megtekintése aktiválásával kerülik el a bajt, hogy a címzett ne bosszankodjon (tegyem hozzá, joggal) a potyára kinyomtatott papírok miatt.
Azonban az Excel van olyan "kedves", hogy ilyen esetekben felhívja a figyelmemet az oldaltörésekre, a lapméretre.
Lássuk!

Ezen a lapon szeretném látni az oldaltöréseket.
Egyszerű a dolog, rákattintok az Oldaltörés megtekintése gombra (Nézet >Munkafüzetnézetek csoport) és oda húzom a kék keretet, ahol szeretném megtörni az oldalt.
Lám, milyen szép.

Két dolog bántja a szemet, az egyiken könnyebb segíteni: ha "túllóg" (itt) a szöveg, vagy a számításokból egy vagy több oszlop (rendszerint az összegzések), szerkesztenünk kell, pl. az oszlopszélesség megválasztásával.
Ugyanakkor még nem teljes a siker.
A megoldás
Fájl > Beállítások > Speciális, gördüljünk le az E munkalap megjelenési beállítások csoporthoz.
Vegyük ki a pipát az Oldaltörés megjelenítése jelölőnégyzetből, majd OK.

És az eredmény:

Azonban ne feledjük, ez mindig csak a kiválasztott munkalapra érvényes.

Egyesített cellák "felkutatása"
2017. július 10.

Verzió: Excel 2013, 2010, 2007

Olykor szükséges, hogy megtaláljuk a lap egyesített celláit.

Leginkább akkor van erre szükség, amikor megpróbálunk egy oszlopot rendezni, aztán felugrik az "utálatos" ablak.

Nem szeretem az egyesített cellákat, csak fölösleges munkát okoznak, a dizájn érdekében más megoldásokkal is lehet dolgozni.
Ám ha mégis belefutunk, íme a gyors megoldás.
Az ábrán a B4:C4 és aB7:C7 cella az egyesített.
A megoldás:
1) Jelöljük ki az oszlopot. (Itt a B oszlopot.)
2) Ctrl + F (Keresés), majd kattintsunk: Egyebek.
3) Kattintsunk: Formátum
4) Kattintsunk az Igazítás fülre: A Szöveg elhelyezése felirat alatt vegyük ki a jelölést a Sortöréssel több sorba és a Lekicsinyítve, hogy beférjen jelölőnégyzetekből. Jelöljük be a Cellák egyesítésével jelölőnégyzetet.

5) Kattintsunk a Következő gombra.
Megtaláljuk az összes egyesített cellát.
Ha a Listába mind gombra kattintunk, az összes egyesített cellát látjuk.

Különösen nagy lapok esetében a kívánt cellára kattintva egyszerűen hozzáférünk.
A cellák szétválasztására több módszer kínálkozik, a legegyszerűbb (Excel 2010+):
Jelöljük ki a cellá(ka)t: Kezdőlap > Igazítás > Cellaegyesítés > Cellák szétválasztása.

Ennyi.

Rejtett sorok és oszlopok
2017. július 3.

Verzió: Excel 2013, 2010, 2007

Sokszor fordul elő, hogy egy munkalapnak csak egy része látható, esetleg nem akarjuk, hogy mind az 1 048 576 sort és 16 384 oszlopot "lássuk". Hogyan rejtsük el, ill. hogyan fedjük fel őket?

Ismert kép?

A munkalap jó része rejtett. Gyakran találkozunk ezzel a megoldással, és olykor szükségünk is lehet rá, hogy csak egy korlátozott méretű területet mutassunk. Példánkban ez at A1:F14 terület.
Hogyan működik?
1) Kattintsunk arra az oszlopra, melytől kezdve el akarjuk rejteni a a többit (itt F) > Ctrl + Shift + Jobbra nyíl. Kijelöltük az F oszloptól jobbra eső összes oszlopot. Jobb egérgombbal kattintsunk: Elrejtés.

Ezzel az F oszlopot követő oszlopokat elrejtettük.
2) Ugyanígy járunk el a sorok esetében is. Jelöljük ki a "kezdő" sort (itt ez a 14.) > Ctrl + Shift + Le Nyíl. A 15. sortól kezdve minden sort kijelöltünk. Jobb egérgomb: Elrejtés.
Az eredmény:

Ha azt szeretnénk, hogy később senki ne tudja felfedni ezeket a rejtett oszlopokat/sorokat, védjük jelszóval a munkalapot, de előtte biztosítsuk, hogy a látható terület ne legyen zárolt.
A megoldás:
Jelöljük ki a látható területet (Ctrl + A) > jobb gomb > Cellaformázás > Védelem.
Vegyük ki a pipát a Zárolt jelölőnégyzetből.

Mi a helyzet, ha egy ilyen munkalappal találkozunk?
1) Ellenőrizzük, védett-e a munkalap.
A Véleményezés fülön nézzük meg, megjelenik-e a Lapvédelem feloldás gomb.
Ha igen, a lap védett. Kattintsunk rá, és ha kéri a jelszót, adjuk meg.

Ha a Lapvédelem gombot látjuk, a megjelenő munkalap nem védett. Nincs semmi tennivaló.
Kattintsunk: Ctrl + A, kijelöltük a lapot, jobb egérgombbal kattintsunk az egyik oszlopra (A, B, C) > Felfedés.

A munkamenetet ismételjük meg a sorokkal.
Minden oszlopot és sort felfedtünk.
Persze megoldhatjuk a menüből is:
Kezdőlap > Cellák > Formátum > Láthatóság > Elrejtés és felfedés:

Ennyi.

Legújabb Excel tipp