Telefonszámunk: 1-472-0679

Képletek

Számított feltétel szerinti összesítések

On 2013-10-31   /     /   Leave a comment

A feltétel szerinti összegzés (Sumif/Szumha), illetve a megszámlálás (Countif/Darabteli) függvényeket legtöbben ismerik. Sőt, az excel 2007-től ezek több feltételt is használni képes nagy-testvérei is egyre inkább bekerülnek a köztudatba. (SumIfs/Sumhatöbb, Countifs/Darabhatöbb). Ezekkel a függvényekkel remek riportokat lehet készíteni, de azért van néhány korlát, amiket nem tudnak átlépni.

Az oktatóanyagban ezt a táblázatot használjuk példaként.

mintatabla

Érdekesebb kérdések ehhez a táblázathoz

  • A nyereséges sorok száma
  • Az „Északi” nyereségesek száma
  • Az „Északi” nyereségesek összbevétele
  • Északiak és Déliek együttes bevétele vagy nyeresége vagy vesztesége.

Természetesen lehetne egy „Nyereség” nevű számított oszlopot tenni a végére, de most ezt szándékosan nem tesszük.

Mi a gond az ismert feltételes összesítő függvényekkel?

  • A feltételek csak konkrét értékek lehetnek (konstans, cellacím vagy számítás), de olyat már nem tudnak, hogy csak azokat a sorokat vegyék figyelembe, ahol pl. a ‘B’ oszlopban nagyobb érték van, mint a ‘C’ oszlopban
  • A feltételeket csak ÉS kapcsolatba tudják hozni, azaz egy következő feltétel tovább szűkíti az előző feltéteknek megfelelő sorok számát. A feltételeket nem képesek VAGY kapcsolatba tenni.

Akkor mi a megoldás?

  • Egyik lehetőség, hogy egy külön oszlopba elvégezzük a vizsgálatot. majd ezt az oszlopot értékeljük ki. Ha pl. a nyereséges sorok számát keressük, akkor az utolsó oszlopba írhatunk egy olyan képletet, ami nullát ad eredményül, ha nincs nyereség, különben pedig egyet. Ezen oszlop összesítésével megkapjuk a nyereséges cégek számát. Ez két lépés. Egyik a segédoszlop elkészítése, és  a második az összegzés.

A segédoszlop képlete lehetne egy HA() függvény, de Én ezt javaslom beírni, majd lehúzni az oszlopra:

=--(B2>C2)

A zárójelben lévő kifejezés TRUE/FALSE eredményt ad, a dupla mínusz jel pedig ezt alakítja át 0/1 értékekre.

  • Második lehetőség, hogy Adatbázis függvényt próbálunk bevetni, és a kritérium tartományt olyan számítással adjuk meg, ami a táblázat első sorában értékel ki. Az egész kritériumtartományt elnevezzük „Criteria” néven.

adatbazis-fuggveny-szamitott-feltetel
A függvény pedig a következő:

=DCOUNTA($A$1:$D$13;"Kateg";Criteria)
=DARAB2($A$1:$D$13;"Kateg";Criteria)

És újra a SUMPRODUCT (Szorzatösszeg)

  • Harmadik lehetőség, hogy az egészet egy képlettel oldjuk meg.
=SUMPRODUCT(--(B2:B13>C2:C13))
=SZORZATÖSSZEG(--(B2:B13>C2:C13))

Ez a képlet belül a ‘B’ és a ‘C’ oszlopot hasonlítja össze, és egy olyan tömböt ad eredményül, aminek annyi eleme van, ahány sora a két megadott tartománynak, de ott van benne TRUE, ahol a B>C, különben FALSE. A dupla mínusz jel a logikai értékeket 0/1 értékekké alakítja. Végül a SUMPRODUCT összeadja, az eredmény a tömbben szereplő egyesek összege, azaz a nyereséges sorok száma lesz.

Letölthető munkafüzet: szamitott-felt-szerinti-osszesitesek

A kapcsolódó videóban látható a megoldás menete, és a többi kérdésre is választ kapunk

  • Számított feltétel szerinti összegzés

Tovább... →
Back to Top

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
« Előző oldal
Következő oldal »