Telefonszámunk: 1-472-0679

Excel 2013

Oszlopok sorrendjének változtatása villámgyorsan – szűréssel!?

On 2013-08-05   /     /   Leave a comment

Íme egy újabb gyöngyszem.  Július legvégén volt egy Excel VBA kurzusom, ahol arra szoktam buzdítani a résztvevőket, hogy bátran hozzanak saját problémákat, és ha belefér, megoldjuk őket, vagy legalább hozok rá elvi vagy gyakorlati megoldást a tanfolyamon. Milu azzal állt elő, hogy rendszeresen kap egy bizonyos riportot, ami mindig ugyanazokat az oszlopokat tartalmazza, de gyakran eltérő oszlop sorrendben. Mivel az lenne a feladat, hogy a kapott riportokat egy eredményfájlba gyűjtse, aminek az oszlopsorrendje adott,a másolás előtt  az aktuálisan kapott riportot a gyűjtőfájl oszlopsorrendjéhez kell igazítani. Mivel ismétlődő a feladat, jó lenne lemakrózni, hogy automatikusan futhasson. Kell tehát találni egy olyan megoldást, ami függetlenül az aktuális oszlopsorrendtől, mindig egy előre rögzített sorrend alapján elkészít egy másolatot, majd azt a gyűjtőfájl végére fűzi.

Minta oszlopsorrend

Minta táblázat

Hibas-sorrend

Hibás-sorrend

Ebben az anyagban kiemeltem az oszlopsorrend változtatására a lehetséges módszereket.

Sorrend változtatás vontatással vagy vágólappal

  • Kivágás -> Kivágott cellák beszúrása (Cut ->Paste Cut cells) vagy
  • Shift+vontatás

Probléma ha makrózni akarjuk: Nem tudjuk előre azt, hogy mely oszlopokat és hová kell tenni a javítás során.

Módosítás az oszlopok rendezésével

Mivel az Excel a 2007-es verziótól képes valamely sor alapján rendezni az oszlopokat, megoldás lehet, ha a helyes sorrendet az aktuális oszlopnevek fölé képezzük (kézzel vagy képlettel), majd a kapott számok alapján átrendezzük az oszlopokat.

oszlop-sorszamok

Helyes oszlop-sorszámok a táblázat tetején

  • Data -> Sort -> Options -> Left to Right
  • Adatok ->Rendezés ->Beállítások ->Balról jobbra
Oszlopok rendezése

Oszlopok rendezése

Sor-szerinti-rendezes

Sor-szerinti-rendezes

A helyes oszlop sorszámokat kézzel vagy a Hol.Van (Match) függvénnyel állíthatjuk elő. A Videóban megmutatom ezt a megoldást is.

Probléma ha makrózni akarjuk: Ha az oszlopnevek nem egyeznek meg az aktuális riportban a rögzített oszlopnevekkel. Ebben az esetben a Match függvény nem fogja megtalálni a nevet, és a helyes sorszám helyett hibát ad vissza.

Módosítás az irányított szűrő (Advanced Filter) segítségével

Ezt a megoldást Márti gyűjtőmunkájának köszönhetjük. Ebben az esetben az aktuális riport táblázat mellé vagy alá, akár másik munkalapra feltesszük a helyes sorrendű fejlécet, majd alkalmazzuk a speciális vagy irányított szűrőt.

  1. Kijelöljük vagy aktiváljuk a Riport táblázatot
  2. Elindítjuk az irányított szűrőt
    Iranyitott-szuro
  1. Beállítjuk az alábbiak szerint, és az eredmény a jó fejléc szerinti oszlopsorrend lesz
    Iranyitott szuro beallítas
Irányitott szűrő beállítás

Ha nem adunk meg minden oszlopot, akkor csak a megadottakat fogja átmásolni!

Probléma ha makrózni akarjuk: Ha az aktuális riportban lévő oszlopnevek bármelyike nem egyezik meg a rögzített oszlopnévvel. Ebben az esetben nem fog működni.

Letölthető minta dokumentum: oszlop_sorrend_modositas.xlsx

Mindez működés közben

  • Oszlopsorrend változtatása

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

Az aktív cella táblázatban van-e, és melyikben? – VBA

On 2013-07-26   /     /   Leave a comment

A makrófejlesztés egyik fontos része a „Bolond állóság” (FoolProof) biztosítása. Emiatt egy igényesebb VBA kód hibaellenőrzéseket is tartalmaz.

Az egyik ilyen eset lehet, hogy ellenőrizzük, az aktív cella egy korábban már táblázattá (Lista) alakított tartományban van-e?

Íme egy erre szolgáló függvény

[vb]
Function in_table() As String
Dim tname As String
tname = ""
On Error Resume Next
tname = ActiveCell.ListObject.Name
in_table = tname
End Function
[/vb]

Úgy működik, hogy ha az aktív cella benne áll egy táblázatban (listában), akkor létezik a .name tulajdonsága. Különben hibát ad, amit kezelünk.

A függvény a táblázat nevét, vagy üres stringet ad vissza.

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

Adatok másolása szűrt táblázatba

On 2013-05-20   /     /   Leave a comment

Gyakran látom a különböző Excel fórumokon azt a kérdést, hogy hogyan lehet adatokat beilleszteni egy olyan táblázatba, amelynek vannak elrejtett sorai? Ez leginkább szűrés alkalmával fordul elő. Tehát azt szeretnénk, hogy a szűrt táblázat egyik oszlopának egy kijelölt tartományába, egy másik tartományból másolhassunk adatokat.

A forrás és a céltartomány ugyanabban a táblázatban van

Sajnos erre a kérdésre nem is olyan egyszerű a válasz (de legalább van), mert az Excel nem igazán van felkészítve ilyesmi feladatra. Nézzük mi történik akkor, ha megpróbáljuk a szokásos beillesztést (CP=Copy-Paste)

  1. Szűrjük a táblát
  2. Kijelöljük a másolandó tartományt ->Másolás(Ctrl+C) Ezt tehetjük a szokásos módon, vagy a speciális kijelölés->Csak a látható cellák (Visible cells only)
  3. Kijelöljük a céltartományt (lényeges, hogy mérete megegyezzen a forráséval)
  4. Beillesztés (Ctrl+V)

Az eredmény

hiba1

Ugyanezt az üzenetet kapjuk akkor is, ha megpróbáljuk átvontatni a forrástartományt a céltartományra. Ha nem jelöljük előre ki a céltartományt, csak az első celláját, akkor pedig az történik, hogy a beillesztés a nem látható (szűrt) cellákba is megtörténik. ezt pedig semmiképpen nem szeretnénk.

hiba2

A megoldás

Szűrt állapotban a céltartomány celláiból át kell hivatkozni a forrástartomány celláira, majd érték beillesztéssel megszüntetni  a képletet. Ez csak akkor működik biztonságosan, ha ugyanabban a táblázatban van a két tartomány, és így biztosított, hogy közösek az elrejtett sorok.

megoldas

A forrás és céltartomány különböző táblázatban van

Ebben az esetben az sem biztos, hogy a forrás tartomány is szűrt, de ha igen, az valószínűtlen, hogy ugyanazok a sorok lesznek rejtettek, mint a céltartomány esetén. Erre a helyzetre az Excel felület nem kínál megoldást, tehát makrót (VBA kódot) kell készíteni. Ebben első lépésként meg kell vizsgálni, hogy a forrás és a céltartomány látható (nem szűrt) sorainak (és oszlopainak)  száma megegyezik-e, majd soronként másolhatunk úgy, hogy a céltartományban csak a látható sorokba illesztjük a forrás tartomány aktuális látható sorát. A Videóban látható mindez kicsit bővebben.

Következő rész: A makrós megoldás

Letölthető munkafüzet: beillesztes-szurt-tartomanyba

  • Nézzük, hogy lehet adatokat másolni csak a látható cellákba

Tovább... →
Back to Top

A VÁLASZT (CHOOSE) függvény

On 2013-05-11   /     /   2 hozzászólás

Ez a függvény kevéssé ismert, pedig szerintem nagyobb figyelmet érdemelne. Többszörös „HA” elágazásokat lehet vele sokkal egyszerűben megúszni. Úgy működik, hogy az első argumentumában megadott sorszámot (index) kiértékeli, és a másodiktól az n-edik argumentumok közül az annyiadikat választja.

choose (választ)

Általános használata

=CHOOSE(index; érték1; [érték2]; ...)

Ha az index 1, akkor a VÁLASZT függvény az érték1 argumentumot adja vissza, ha 2, az érték2 argumentumot, és így tovább.

Az első argumentum, ami 1-254 közé eshet, lehet konstans szám, számot visszaadó képlet, vagy cellahivatkozás.

Egyszerű, példa

=CHOOSE(2;"alma";"körte";"barack")

Ez a függvény az érték argumentumok közül a harmadikat, azaz a „barack” szót adja vissza.

Összetettebb példa

A függvényben rejlő igazi erő akkor derül ki, amikor megtudjuk, hogy az érték argumentumok nem csak konstans értékek vagy cellahivatkozások lehetnek, hanem tartományok, sőt, képletek is.

=CHOOSE(2;SUM(A1:A10);AVERAGE(A1:A10);MAX(A1:A10))

Az előző példa az index paraméter függvényében más-más műveletet végez el az A1:A10 tartománnyal.

Ha az index is valamilyen számítás, esetleg feltétel eredménye, akkor igen rugalmasan tudjuk használni összetett elágazások esetén.

További példák és részletek a függvénnyel kapcsolatban

http://office.microsoft.com/hu-hu/excel-help/valaszt-fuggveny-HP010342269.aspx

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

Intervallum tömbök

On 2013-04-13   /     /   Leave a comment

Ebben az anyagban leírtak megértéséhez hasznos lehet előbb ezt megnézni:

excel-tombok-tombkonstansok

Hogyan válaszolnánk meg Excel segítségével a következő kérdéseket:

  • Két egész szám között hány 3-al osztható szám van?
  • Két dátum között hány hétfő van?
  • Két dátum között hány dátum esik hétvégére

Megoldások

Első

Nézzük az első feladatot. Hagyományos módon ezt úgy csinálnánk, hogy képezzük egy oszlopba a növekvő értékeket, és mellette egy segédoszlopban logikai értékként megadnánk az oszthatóságot, majd feltételes összegzéssel megkapjuk az eredményt. Az ábrán látható, hogy a „B” oszlopban a hárommal való oszthatóságot vizsgáljuk.

oszthatosag-logikai-vizsgalatat

Ezután nincs több dolgunk, mint egy eredménycellába megszámolni a „B” oszlopban lévő TRUE értékek számát.

Mondjuk a „D2”-be írjuk be a képletet:

=COUNTIF(B2:B11;TRUE)

Második

A megoldást egyetlen képlettel is megadhatjuk, ami az egész tartományban vizsgálja az oszthatóságot, a visszakapott TRUE/FALSE értékeket 1-re és 0-ra alakítja, és összeadja az eredményt. Értelemszerűen csak ott lesz 1-es, ahol az érték osztható, és az 1-esek összegzése megadja, hogy hány szám van a tartományban, ami osztható 3-al.

=SUMPRODUCT(--(MOD(A2:A11;3)=0))

Tól-Ig tömbök képzése

Próbáljuk ki a következőket:

Egy cellába írjuk be a következő képletet, de mielőtt lezárnánk, nyomjuk meg az F9 billentyűt :

=SOR(INDIREKT("34:55"))

angol verzióban

=ROW(INDIRECT("34:55"))

A tartalom átváltozik erre

={34;35;36;37;38;39;40;41;42;43;44;45;46;47;48;49;50;51;52;53;54;55}

Tehát ez az összefüggés létrehozza a memóriában a kezdő és végérték közötti számok növekvő halmazát. (Ctrl+Z-vel vagy ESC-el lehet visszatérni.)

Ha a kezdő és a végérték nem konstans, akkor felvehetjük őket egy-egy cellába, és a képlet így alakul

=SOR(INDIREKT(A1 &":" & A2))

Látható, hogy az Indirekt függvénynek egy szövegre (stringre) van szüksége, ezt összefűzéssel állítjuk elő, belecsempészve a kettőspontot, mint a kezdő és a végérték elválasztójelét.

Ugyanez dátumokkal

A dátumokat ugyebár az Excel sorszámként kezeli. Egy egy szomszédos nap között pont egy egész különbség van. Két dátum közötti tömböt az előzőekhez hasonlóan képezhetjük, azaz a kezdő és végdátumot beírjuk két cellába, és az előző képletet beírva, majd F9-et nyomva ezt láthatjuk:

datumszamok-tombje

Tehát a 2013 január 1 és 5 közötti dátumszámok növekvő értékeit.

Mire jó ez az egész?

Ha visszatérünk a fenti feladatokhoz, akkor abban az esetben, ha nem akarjuk vagy nem lehetséges képezni a tömb elemeit külön a munkalapon, akkor ezt a megoldást alkalmazhatjuk a képletekben.

Hány hárommal osztható szám van két szám között (A1 és A2):

=SUMPRODUCT(--(MOD(ROW(INDIRECT(A1&":"&A2));3)=0))

Két dátum között hány hétfő van?

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT($A$1 & ":" & $A$2));2)=1))

A WEEKDAY függvény magyar verzióban a HÉT.NAPJA. Azt mondja meg, hogy egy dátum a hét hányadik napjára esik. A képlet végén 1-el hasonlítjuk össze, mert a Hétfő a hét első napja.

Két dátum között hány dátum esik hétvégére

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT($A$18&":"&$A$2));2)=6)+(WEEKDAY(ROW(INDIRECT($A$1&":"&$A$2));2)=7))

A hatodik nap a szombat, a hetedik nap pedig a vasárnap. A kettő közötti plusz jel a logikai VAGY műveletet jelenti.

Letölthető munkafüzet, Video

Ertekek-kozotti-szamolas-row-indirect

  • Növekvő számhalmazok

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