Telefonszámunk: 1-472-0679

Képletek

Feltételes összegzés és megszámlálás két dimenzióban

On 2013-09-27   /     /   Leave a comment

Az anyagban megmutatom, hogy lehet képlettel megoldani azt a feladatot, amikor a sor, illetve oszlop azonosítók alapján egyszerre kellene összesítést vagy megszámlálást csinálni. Volt egy korábbi anyag, ahol 2D keresést mutattam, de ez más.

Íme a probléma

2D-osszegzes-feladat

Tehát ugyanazok az értékek ismétlődgetnek a fejlécben, mint az első oszlopban. Nekünk pedig az alsó, összesített tábálázatot kell összehozni

Hű, ez nagyon érdekes. nem tudom, hogy mennyire gyakori probléma, de Nekem annyira megtetszett, hogy azt éreztem, ezt mindenképpen fel kell tenni az Excel Bázisra.

PIVOT?

Mivel ismétlődő fejléc elemek vannak, PIVOT-al nem lehet megcsinálni. Legalább is Én nem találtam rá megoldást. ha lenne valaki, aki IGEN, akkor commentben ossza már meg velünk!

MACRO?

Igen, makróval meg lehet csinálni. Egymásba ágyazott ciklusokkal. Már ha érted mire gondolok. Ez nagyobb táblázat esetén meglehetősen lassú, ráadásul tudni kell programozni, és a VBA nyelv ismerete is szükséges.

A megoldás – Képlet – SUM vagy SUMPRODUCT

Nem gondolnád, Igaz? Nézzük a feladatot konkretizálva az ‘A‘ kategóriára: Összesíteni kell az adattartomány azon elemeit, ahol a fejlécben ‘A‘ van és a sor elején is ‘A‘ van. Mivel az egyik feltétel vízszintesen van, a SUMIFS (Darabhatöbb) függvény nem jön számításba. Tömbökkel kell dolgoznunk, így esélyes, hogy tömbképlet lesz a megoldás.

Általánosan kb. így néz ki:

=ÖSSZEG((fejléc="A")  *  (Első oszlop="A") * (adatok))

A fenti kép alapján konkrétan, még mindig csak az „A” kategóriára

=SUMPRODUCT(($B$3:$B$12="A")*($C$2:$J$2="A")*($C$3:$J$12))

A SUMPRODUCT (SZORZATÖSSZEG) remek függvény. Kiválón alkalmas hasonló módon több feltétel szerinti összegzésre. Amíg nem volt 2007-es Excel, és nem nem volt SUMIFS (DARABHATÖBB), addig így lehetett csak hasonló problémákat megoldani.

Hogy működik?

Hát, ezt írásban meglehetősen hosszadalmas lenne elmagyarázni. Inkább nézd meg a videót. Abban elmondom, hogy mi a működés alapja, és megnézzük az összegzésen kívül a megszámlálást is.

Gyakorló munkafüzet: 2D-felt-osszegzes-megszamlalas

  • Két dimenziós összesítés

Tovább... →
Back to Top

Lista szűrése másik lista szerint

On 2013-09-19   /     /   Leave a comment

A feladat az, hogy egy szöveges oszlopot szűrjünk az alapján, hogy csak azok a sorok maradjanak (vagy ne maradjanak), amelyek tartalmazzák egy másik tartományba előre felrögzített szavak bármelyikét.

alap-problema

Tehát a bal oldali listában, ami valószínűleg egy hosszabb lista, jelölni, vagy szűrni szeretnénk azokat, amikben előfordul a jobb oldali lista bármely eleme.

Alakítsuk táblázattá!

Mielőtt bármelyik megoldásnak nekikezdenénk, mindkét tartományt alakítsuk táblázattá. Ennek több előnye is van.

  • Az új tételeket automatikusan beleveszi a hivatkozásba
  • Egyszerűbb megadni a lista és kritérium-tartományokat
  • Egyszerűbben hivatkozhatjuk őket akkor is, ha különböző lapokon vannak

Megoldás irányított szűrővel

Felállítunk egy kritériumtartományt, ahol a szűrendő szavak egymás alatt vannak, joker karakterekkel (*) kiegészítve az elején és a végén. Mivel egymás alatt vannak, VAGY kapcsolatban leszek egymással, tehát bármelyik előfordul a bal oldali oszlopban, azt szűrni fogja.

iranyitott-szuro-menu

iranyitott-szuro

Ha csak a táblázatok nevét használjuk, akkor a táblázatok fejléce nem fog szerepelni a hivatkozásban. Emiatt mögé kell írni az [#All] kiegészítést. Tehát:

List Range: adatok[#all]

Criteria range: szurolista[#all]

Ha változik a bal vagy a jobb oldali lista, vagy új elemekkel bővül, akkor természetesen újra kell futtatni a szűrést.

Megoldás képlettel

Ebben az estben a táblázattá alakított szűrőlista elemeihez nem kell joker karaktereket adni, csak egymás alá felsorolni a szűrendő szavakat. Az adatlistát pedig kiegészítjük egy új oszloppal, ahová képlettel megállapítjuk, hogy a kizárandó elemek előfordulnak-e az adott sorban lévő szövegben.

szurolista

A C4-be írt képlet megvizsgálja, hogy a szűrőlista bármely eleme benne van-e az aktuális sor „Megnevezés” oszlopában.

=SUM(IFERROR(SEARCH(szurolista;[@Megnevezés]);0))
=SZUM(HAHIBA(SZÖVEG.KERES(szurolista;[@Megnevezés]);0))

A fenti képlet tömbképlet, ezért a bevitel után CTRL+Shift+ENTER-el kell lezárni.

Eredményül a fenti képen a szerkesztőlécen látható módon, kapcsos zárójelek kerülnek a képlet elejére és végére, ami tömb határoló az excelben.

A képletet érvényesítsük a teljes oszlopra.

Ahol nullát kapunk, az azt jelenti, hogy egyik jobb oldalon felsorolt szó sem fordul elő a bal oldali lista aktuális sorában. Ennek megfelelően lehet a szűrés oszlopban szűrni a nullákat vagy a nem nullákat.

Letölthető minta munkafüzet: lista-szurese-lista-szerint

A videóban látható mindez működés közben, részletesebb magyarázatokkal, főleg ami a képlet működését illeti.

  • Lista szűrése másik lista szerint

Tovább... →
Back to Top

Az ‘ABC’ auto-kitöltése – érdekes és triviális

On 2013-09-17   /     /   Leave a comment

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.

  1. Begépeljük az ABC betűit egymás alá
  2. Beimportáljuk egy Egyéni listába

egyeni-lista

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.

A SOR ( ROW ) függvénynek bármely 65. sorban lévő cellát adhatjuk kezdőértéknek.

B65, C65, stb..

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

Tovább... →
Back to Top

Két dimenziós keresés – VLOOKUP-INDEX-MATCH

On 2013-09-10   /     /   Leave a comment

Előfordulhat olyan feladat, amikor egy táblázat első oszlopában és a fejlécében kell megkeresni egy-egy konkrét értéket, és a kettő által adott metszéspontban lévő adatot szeretnénk kivenni, úgy, ahogy ez az alábbi ábrán látható.

2D-kereses-alaptabla

A feladat során lényeges, hogy mind az első oszlop, mind a fejléc egyedi adatsort tartalmaz, ahol nincs ismétlődő elem. Ez azért fontos, mert a kereséshez a közismert függvényeket fogjuk használni, amelyek alapesetben az első előfordulást keresik.

Két megoldást ismertetnék. Itt azonnal a megoldás látható, a videóban pedig ott  a magyarázat is.

1. VLOOKUP-MATCH páros (Fkeres-Hol.Van)

A lényeg az, hogy az egész táblára nézve a  ‘B’ oszlopban keressük VLOOKUP-al a kívánt autómárkát (Renault), és azt, hogy melyik oszlopból adja vissza az eredményt, nem egy konstans számmal adjuk meg, hanem a MATCH függvénnyel megkérdezzük, hogy a fejlécben a kívánt hónap (Március) hányadik.

1. lépés:  a VLOOKUP

=VLOOKUP("Renault";$B$2:$H$12;4;0)

A VLOOKUP megkeresi az első oszlopban a „Renault” szót, és a negyedik oszlopból visszaadja a 266-os értéket.

2. lépés: a MATCH

=MATCH("Március";$B$2:$H$2;0)

A MATCH megadja, hogy a fejlécben a „Március” hányadik

3. lépés: Egybe rakva

=VLOOKUP("Renault";$B$2:$H$12;MATCH("Március";$B$2:$H$2;0);0)

Az 1. lépésben lévő VLOOKUP  képletben a 4-es helyére beletettük a komlett MATCH részt.

INDEX-MATCH páros (Index-Hol.Van)

Ugyanezt a feladatot az INDEX függvénybe ágyazott két MATCH függvénnyel is meg lehet oldani. Ugyanis az INDEX pont azt tudja, amire itt szükségünk van, azaz egy táblázat adott számú sorában és adott számú oszlopának metszéspontjában lévő értéket ad vissza.

=INDEX(táblázat, sor_szám, [oszlop_szám])

INDEX-peldatabla

Ezt a lehetőséget ritkábban használjuk, sokkal gyakoribb, hogy az INDEX csak egy dimenziós sorozatban (oszlopban) keres, és a második (oszlop) argumentumot meg sem adjuk.

Tehát ha az INDEX függvény sor és oszlop argumentumaiba MATCH függvényekkel kiszámoltatjuk azt, hogy a „Renault” hányadik az első oszlopban, és a „Március” hányadik az első sorban, akkor meg is kapjuk a kettő metszéspontjában lévő értéket.

=INDEX($B$2:$H$12;MATCH("Renault";$B$2:$B$12;0);MATCH("Március";$B$2:$H$2;0))

Letölthető minta dokumentum: Ketdimenzios-kereses

A videóban a megoldást interaktívvá tesszük, azaz választható az autómárka és a hónap, és a megoldást bemutatom Táblázattá alakított tartományon is.

  • Két dimenziós keresés akció közben

Tovább... →
Back to Top

Dátumból negyedév számítás – hagyományos és érdekes megoldás is!

On 2013-08-08   /     /   Leave a comment

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

Tovább... →
Back to Top

Dátumhoz kapcsolódó nap megjelenítése

On 2013-08-02   /     /   Leave a comment

Ha egy dátumhoz szeretnénk megmutatni az adott nap nevét is, akkor a következőket lehet tenni:

Triviális, de összetettebb megoldás

A dátumból képezzük a megfelelő függvénnyel a nap sorszámát, majd a kapott sorszám segítségével kikeressük egy segéd táblából a megfelelő napot.

Hét.Napja (WeekDay) függvény

Megadja, hogy az adott dátum a hét hányadik napjára esik

=Hét.Napja(dátum;2)

A második argumentum azt szabályozza, hogy a függvény melyik napot tekintse a hét első napjának. Ha a  hétfőt szeretnénk (mint általában), akkor ez legyen mindig 2.

A következő képlet megadja, hogy az aktuális nap a hét hányadik napjára esik.

=weekday(today();2)
=Hét.Napja(Ma();2)

Hogyan lesz ebből meg a nap neve?

Erre segédtáblákat használhatunk, amik lehetnek egy cellatartományban vagy akár elnevezett tömbkonstansokban, és ezekből az INDEX vagy az FKERES (Vlookup)  függvényekel kereshetjük meg a nap nevét.

=INDEX(napok_neve_tartomany;het.napja(datum;2))

A nap beállítása cellaformázással

Ha ‘A1’-ben van a dátum, akkor ‘A2’-be írjuk a következő képletet:

=SZÖVEG(A1;"nnnn")
=TEXT(A1;"nnnn")

 Az „nnnn” kóddal azt kérjük, hogy írja ki a nap hosszú nevét. Angol rendszerben természetesen ennek „dddd”-nek kell lennie.

Egyéb érdekességek

Ha nem tudjuk, hogy mi az aktuális nyelvi verzió napot jelentő formázó karaktere, akkor a Visual Basic felületen a parancs ablakban futtassuk az alábbi kódot:

Application.International(xlDayCode)

Ha az ország kódot változtatgatjuk, akkor a kódnak megfelelően írja ki a nap nevét a következő módosítással:

=SZÖVEG(A1;"[$-040e]nnnn")
=TEXT(A1;"[$-040e]nnnn")

A szögletes zárójelben a dollár után az adott ország kódja van hexa formátumban.
A 040e ( decimálisan 1038 ) magyarország kódja.

Országkódok

http://msdn.microsoft.com/en-US/goglobal/bb964664.aspx

Mind ez működés közben látható a videóban.

Letölthető munkafüzet: Nap_nevenek_megjelenitese

  • Dátumhoz tartozó nap neve

Tovább... →
Back to Top

Top n érték listázása vagy összegzése tömbképlettel

On 2013-05-26   /     /   1 Comment

Ha szükségünk lenne egy olyan képletre, ami egy tartományból listázza a valahány (három, négy, öt, stb…)  legnagyobb értéket, akkor íme a megoldás. Mivel a képletnek nem csak egy eredménye van, hanem több, a feladatot tömbképlettel tudjuk megoldani.

A megoldáshoz, illetve a leírás megértéséhez ismerni kell a LARGE (NAGY) függvényt, ami egy tartományból visszaadja a valahányadik legnagyobbat. A következő függvény pl. az A1:A20 tartományból visszaadja a második legnagyobbat.

=LARGE(A1:A20;2)

A három legnagyobb érték listázása

Tehát szükségünk van az első, a második és a harmadik legnagyobb értékre az A2:A10 tartományból. A Large függvénynek két tömböt kell megadni. Az egyik az adatokat tartalmazó tartomány, a másik pedig azoknak a számoknak a felsorolása tömbkonstansként vagy tartományi hivatkozásként, amennyiedik értékeket keressük. Utóbbiakat ezután hívjuk index számoknak.

Adatok tartománya: A2-A10

Index számok: E2:E4

  1. Jelöljünk ki három üres cellát
  2. Képlet
=LARGE(A2:A10;E2:E4)

Top-n-keplet

Mivel tömbképletről van szó, a bevitelt a Ctrl+Shift+Enter kombinációval kell zárni.

A három legnagyobb érték bekerült a három kijelölt cellába.

Ha az adatok módosulnak, és ez érinti a három legnagyobbat, akkor azok is módosulni fognak.

Az index számokat tömbkonstanssal is meg lehet adni.

Az anyaghoz tartozó videóban bemutatom a képlet létrehozását mindkét módszerrel.

  • Top n legnagyobb érték kiválogatása képlettel

Tovább... →
Back to Top

Képlet dinamikus változtatása legördülő lista alapján – három féle módszer

On 2013-05-06   /     /   Leave a comment

Gyakran előforduló feladat lehet, hogy feltételtől függően egy cellába változó képletet kellene írni vagy végrehajtani. Különösen igaz lehet ez abban az esetben, amikor a felhasználó állítja be pl. egy legördülő menüben, hogy mi legyen a feltétel, és ennek függvényében próbáljuk a képleteket dinamikusan összeállítani.

A modell

Egy munkalapon adott egy azonosító (ID) oszlop, és mellé egy másik munkalapról ki szeretnénk keresni a hozzá tartozó nevet. Igen ám, de azt, hogy melyik munkalapról kell kikeresni, azt egy legördülő menüből szeretnénk választani. Ez egy klasszikus VLOOKUP (FKERES) feladat, de a keresési tábla a legördülő menü szerint változó.

A modellt úgy állíthatjuk fel, hogy a különböző országokhoz tartozó adatok, amiben keresni akarunk, külön-külön munkalapokon vannak, és a munkalapok neve, megegyezik a legördülő menüből választható nevekkel, a mi példánkban „HU” és „DE”.

Dinamikus_keplet_modell

Tablazat_HU Tablazat_DE

A „HU” és „DE” lapokon az adatokat táblázattá (Listává) érdemes alakítani, hogy név szerint is lehessen hivatkozni a képletekben, és így a méretük sem kell, hogy egyforma legyen.

Megoldás HA (IF) függvénnyel

Legegyszerűbb megoldásként a képlet feltételes megadását választhatjuk, azaz HA a kiválasztott országkód a B11-es cellában van, akkor a C14-be a következő képletet írhatjuk:

=IF($B$11="HU";VLOOKUP(B14;HU!$A$2:$B$5;2;0);IF($B$11="DE";VLOOKUP(B14;DE!$A$2:$B$5;2;0);0))

Látható, hogy egymásba ágyazott IF függvényeket látunk, és a képlet a B11 tartalmától függően, vagy a HU vagy a DE munkalapról keres, ugyanabban a tartományban. Ezután a képletet lehúzhatjuk a többi ID-re is.

Előny

Az IF (HA) függvényt a legtöbben ismerik, tudják használni.

Hátrány

Több országkód esetén meglehetősen hosszú és összetett lehet a képlet. (91 karakter)

Névtartományok használata

Ha a magyar és a német munkalapokon lévő keresési tartományokat dinamikus névtartománnyá alakítjuk, (lásd a fenti ábrán: HU és DE), akkor a képlet jelentősen lerövidülhet. (71 karakter)

=IF($B$11="HU";VLOOKUP(B14;HU;2;0);IF($B$11="DE";VLOOKUP(B14;DE;2;0);0))

Megoldás CHOOSE (VÁLASZT) függvénnyel

A függvényről külön oktatóanyag készült: https://excel-bazis.hu/tutorial/a-valaszt-choose-fuggveny

Azért jön számításba, mert a példa szerint más-más munkalapokról kell keresnünk értékeket. Ha ezeket sorszámmal tudnánk ellátni, akkor máris elkészült egy, az előző (HA) szerkezetnél rövidebb képlet.

A sorszámot egy segédtábla, és VLOOKUP használatával állítjuk elő

Choose-lookup

Tehát a VÁLASZT függvény számára a sorszámot a C24 tartalmazza. Most csak a rövidebb, névtartományokat használó képletet írom le, ami a D27-be kerül. (53 karakter)

=CHOOSE($C$24;VLOOKUP(B27;HU;2;0);VLOOKUP(B27;DE;2;0))

Megoldás INDIRECT (INDIREKT) függvénnyel

Az INDIRECT függvényt leggyakrabban arra használjuk, hogy dinamikusan, szöveges hivatkozásokat képzünk, amiket Ő normál hivatkozásokká tud alakítani.

Nézzünk egy példát:

Egy oszlop utolsó kitöltött cellájának értéke

Itt az adja a kihívást, hogy nem tudjuk, hány sor van kitöltve az adott oszlopban. Viszont ki tudjuk számolni, pl. az „A” oszlopban:

=COUNTA(A:A)

Tehát össze kell állítanunk az „A” oszlop utolsó cellájának a címét, ami ugyebár tuti „A”-val kezdődik, és a sor része meg az előző függvény eredménye. Van nekünk egy remek összefűző jelünk, és már mehet is.

=INDIRECT("A"&COUNTA(A:A))

indirect-demo

Figyelem!

Ez a példa csak akkor helyes, ha az „A” oszlopbna minden sor ki van töltve!

Visszatérve a fenti példához, az INDIRECT függvénnyel azt használjuk ki, hogy az országkód kiválasztása után ugyanolyan képletet kell használni (VLOOKUP), csak a belseje lesz más, mert egyszer a HU, máskor a DE lapon vagy névtartományban kell keresni. ha a „HU” országkódot választottuk, akkor a „HU!A2:B5” tartományban, és „DE” kód esetén pedig a „DE!A2:B5” tartományban. Névtartományok használata esetén pedig a „HU” vagy a „DE” neveket használjuk.

indirect-megoldas

Próbáljuk belefűzni a képletbe dinamikusan a választott országkódot, ami a „B35”-ben van:

=VLOOKUP(B39;INDIRECT($B$35&"!A2:B5");2;0)

Letölthető munkafüzet: Valtozo-adatforras-modellek

Tovább... →
Back to Top

Dashboard alapvetés

On 2013-05-03   /     /   Leave a comment

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.

dashboard

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... →
Back to Top

Adott érték n-dik előfordulásának keresése egy oszlopban

On 2013-04-17   /     /   1 Comment

Találkoztam néhányszor azzal az igénnyel, hogy egy tartományban egy adott értéknek ne csak az első, hanem a második, harmadik, n-dik előfordulást is meg lehessen keresni.

Az Excelnek erre nincs külön fügvénye, és a VLOOKUP (FKERES), illetve a MATCH (HOL.VAN) függvények csak az első előfordulást tudják megkeresni.

Vannak az Interneten segédoszlopot használó, megkerülő megoldások, de egyszer csak ráakadtam egy régebbi bejegyzésre, aminek a tanulmányozása után arra jutottam, hogy ez lesz a helyes megoldás.

Az eredeti cikk egy ősrégi Microsoft tudásbázis bejegyzés, Excel 4.0 és Excel 97 verziókra vonatkoztatva.

Nézzük a megoldást

Az alábbi ábrán látjuk a mintaként használt táblázatot. Tehát a feladat az lenne, hogy az „alma” szó második vagy harmadik előfordulását is meg tudjuk találni, és esetleg kivenni mellőle a hozzá tartozó értéket ugyanabból a sorból.

n-dik-elofordulas-alaptabla

Az ötlet azon alapszik, hogy ha tudnánk képezni egy listát az „alma” szót tartalmazó sorokról (1;4;6), akkor ezek közül a SMALL (KICSI) függvénnyel kiválaszthatnánk az n-dik legkisebbet, ami megadná az n-dik előfordulás munkalap-sorszámát. Jelen példában, ha az {1;4;6} tömb 2-dik legkisebb elemét keresem, akkor egy cellába beírhatom a következő képletet:

=SMALL({1;4;6};2)

Eredményként a 4-et fogom kapni.

Hogy lehet az „almás” sorok tömbjét előállítani?

Természetesen tömbképlettel. Végignézzük az „A1:A6” tartományt, és ha bármelyik eleme egyenlő az  „alma” szóval, akkor kivesszük az aktuális sor számát, különben egy üres sztringet.

Jelöljünk ki 6 egymás alatti cellát (mert 6 elemből áll a példa táblázat), írjuk be a következő képletet, és a végén nyomjuk meg a Ctrl+Shift+ENTER kombinációt!

=IF((A1:A6)<>"alma";"";ROW(A1:A6))

A cellákban a képlet kapcsos zárójelek közé került: {=IF((A1:A6)<>”alma”;””;ROW(A1:A6))} , és a kijelölt cellákban ez lett az eredmény:

sorok-tombje

Tehát megkaptuk a 1;4;6 listát, igaz, hogy közben vannak üres cellák is, de az nem baj. Már csak az van hátra, hogy ebből a listából kiválasszuk a SMALL (KICSI) függvénnyel mondjuk a második legkisebbet, azaz az „alma” szó második előfordulásának  munkalap-sorszámát (4)

=SMALL(IF((A1:A6)<>"alma";"";ROW(A1:A6));2)

A végén látható, hogy a második legkisebbet keressük. Ne felejtsük el ezt is a Ctrl+Shift+Enter-el lezárni.

Amennyiben a táblázat a munkalap első sorában kezdődött, akkor a megtalált munkalap sorszám egyenlő lesz az adott tartományban elfoglalt sorszámmal.

És a mellette levő érték?

Ha valójában az n-dik előfordulás melletti értéket keressük, akkor a sorszám ismeretében az INDEX függvénnyel célt érünk, azaz az egészet beágyazhatjuk egy INDEX függvénybe, ami a „B” oszlopból kiveszi a megkapott sorszámú elemet. Ezt a képletet is természetesen a tömbképleteket megillető Ctrl+Shift+Enter-el kell lezárni.

=INDEX(B1:B6;SMALL(IF((A1:A7)<>"alma";"";ROW(A1:A7));2))

n-dik-elofordulas-eredmeny

És mi van akkor, ha a táblázat nem az első sorban kezdődik?

Akkor egy kicsit matekozni kell a Sorokkal, de azért megoldható. Ha kíváncsi vagy rá, nézd meg a Videó anyagot is!

Ertek-n-dik-elofordulasa

  • Érték n-dik előfordulása

Tovább... →
Back to Top
« Előző oldal
Következő oldal »