Mikor van Cheryl születésnapja?
2017. december 27.
2015 áprilisában "felrobbant" az internet: a szingapúri matematikai olimpia egyikfeladványának óriási visszhangja lett. Magyarul alig hallottunk róla, talán nem is csoda: a feladat a 14 (!) éveseknek szólt. (Itthon a ha
1 korsó sör + 1 korsó sör + 1 korsó sör = 27, és
1 Martini + 1 Martini + 1 korsó sör = 19,
akkor 1 fröccs + 1 kóla + 1 Mojito = ?
színvonalú fejtörők tarolták a "Száz felnőttből csak 1 képes megoldani ezt a feladatot" típusú megosztásokat.)

Év végi bejegyzésemben felelevenítem a feladványt.
Albert és Bernard találkozik Cheryllel. "Mikor van a születésnapod?", kérdezte Albert a lánytól.
Cheryl gondolkodott egy kicsit, majd így szólt: "Megmondom, de kis feladvánnyal nehezítve". Majd egy papírlapra 10 dátumot írt. Ezeket: május 15., május 16., május 19., június 17., június 18., július 14., július 16., augusztus 14, augusztus 15., augusztus 17. Nagyjából így:

"Az egyik az én születésnapom", mondta, és odaadta a lapot a fiúknak.
Majd Albert fülébe súgta a születésnapja hónapját – azaz csak a hónapot adta meg. Bernardnak viszont csak a napot súgta meg, tehát a hónap megnevezése nélkül.
"Ki tudod találni?", kérdezte Albertet.
Albert: "Nem tudom, mikor van a születésnapod, de azt tudom, hogy Bernard sem tudja."
Mire Bernard: "Először én sem tudtam, de most már tudom."
Erre Albert: "Akkor most már én is tudom."
(Innen a megoldás következik.)
A feladvány azon alapul, hogy a másik fél helyzetébe képzelve magunkat feltételeket fogalmazunk meg.
A legjobb, ha egy táblázatot készítünk (Végül is ez egy Excel blog!). Így:

1. lépés: Mit mondott Albert, aki csak a hónapot ismeri?
Albert: "Nem tudom, mikor van Cheryl születésnapja, de azt tudom, hogy Bernard sem tudja."
(Ne feledjük, Albert csak a hónapot ismeri, így a mondat első fele nem mond semmit. Mi eleve tudjuk ezt, hiszen a táblázatból látjuk, hogy minden hónapban több nap szerepel.)
Sokkal fontosabb azonban, hogy a mondat második felével azt állítja, Bernard sem tudja megmondani a teljes dátumot.
A kérdés: hogyan jutott Albert erre a következtetésre?
(Emlékezzünk: Bernard ismeri a napot, melyen Cheryl született.)
Bernard csak akkor tudná, ill. tudná kikövetkeztetni a teljes dátumot, ha Cheryl pl. 18-át adta volna neki. (Látjuk a táblázatban: a 18. és 19. az egyetlen nap, mely csak egyszer szerepel – június, ill. május.)
Mivel Albert határozottan azt állítja, hogy Bernard nem tudja a teljes dátumot, jobban mondva, nincs olyan lehetséges nap, amit kapott, melyből következtethetne a teljes dátumra, már tudjuk, hogy a hónap nem lehet május vagy június. Ezzel természetesen az összes májusi és júniusi napot kizártuk.
Azaz Albert azt állítja, tudja, hogy Bernard nem kaphatta a 18-ai vagy 19-ei dátumot, és csak annyit tud, hogy a hónap nem május vagy június.
A lehetséges (megmaradt) napokat kiemelve:

2. lépés: Nézzük, mit válaszolt Bernard Albert első állítására:
Bernard: "Először én sem tudtam, mikor van Cheryl születésnapja, de most már tudom".
Az előző mondathoz hasonlóan, első fele ennek sem mond semmit, megismétli, amit Albert mondott.
A mondat második rész azonban ad némi információt.
(Ne feledjük, amire eddig rájöttünk, arra Bernard is rájöhetett.)
Bernard eddig nem ismerte a pontos dátumot, most már tudja.
És most kell magunkat az ő helyébe képzelnünk, az ő perspektívájából néznünk a dolgot.
Ne feledjük, velünk ellentétben ő ismeri a napot.
Tehát ül az asztalnál, és ugyanezt a táblázatot nézi: látja, milyen lehetséges napok "állnak rendelkezésére". Ha – mondjuk – Cheryl azt súgta volna, hogy a nap 17., egyből látná, hogy csak egyetlen lehetséges dátum jöhet szóba. (Augusztus 17., ahogy az interneten ezt az internetes vitában sokan követelték. [Erről a megoldásról a végén.])
Csak abban az esetben nem tudná a pontos dátumot, ha a Cheryl által megadott nap 14. lenne. Mivel ismeri a teljes dátumot, július 14-ét és augusztus 14-ét is kihúzhatjuk.
Ez azt jelenti, Bernardnak a következő számok valamelyikét kellett kapnia: 15., 16. vagy 17. Ezek alapján táblázatunk így alakult:

Három lehetséges dátumunk maradt. (Ez már nem sok!) Szerencsére a harmadik állítás tartalmazza a megoldást.
Vissza Alberthez. Albert ugyanerre a következtetésre jutott:
3. Albert: "Akkor most már én is tudom Cheryl születésnapjának dátumát".
Természetesen Albert is tudja, hogy Cheryl születésnapja július 16., augusztus 15. vagy augusztus 17.
Ám ne feledjük, Albert tudja, melyik hónapban született Cheryl.
Nem mondhatná azt, amit mondott, ha Cheryl augusztust súgott volna a fülébe, hiszen augusztusban két dátumunk van. Így az augusztusi születésnapok is kiestek.
Ha azonban Cheryl azt mondta, hogy júliusban, Albert 100% biztonsággal állíthatná, hogy Cheryl július 16-án született, ez az egyetlen megmaradt dátum!
Cheryl születésnapja tehát július 16. Isten éltesse! (Ha kissé megkésve is.)
És akkor most lássuk az internetes vitában felmerült augusztus 17-i helyesnek állított megoldást!
Emlékeztetőül: Albert ismeri a hónapot, Bernard a napot. (Az állítások száma zárójelben.)
Albert: "Nem tudom, mikor van a születésnap, de azt tudom, (1) hogy Bernard sem tudja."
Mire Bernard: "(2) Először én sem tudtam, de most már tudom."
Erre Albert: "(3) Akkor most már én is tudom."
Nézzük a folyamatot:
1) Albert tudja, hogy Bernard nem tudja.
2) Albert kikövetkezteti, hogy Bernardnál nem lehet olyan egyedi (egyszer előforduló) szám, mint a 18. vagy 19. (Május 19. és június 18.)
3) Albert lekezeli Bernardot, mondván Bernard nem tudja a választ. Ez a probléma első állítása.
4) Bernard rájön, mire jött rá Albert: Bernardnál nincs 18. vagy 19. Na most, ha Albertnél a hónap június volna, tudná a választ, mert csak egyetlen dátum maradt júniusban: június 17. Így Bernard kikövetkezteti, hogy a hónap nem lehet június.
5) Bernard kijelenti, hogy tudja a választ. Ez a második állítás.
6) Ha Bernard ilyen magabiztos, egyetlen dátumot kell ismernie. Tudjuk már, hogy ez nem lehet 18. vagy 19. Milyen egyedi dátum (nap) van még? Van két 14., két 15., két 16. és két 17. De Bernard kizárta a június 17-ét. Így számára az augusztus 17. maradt. Szóval így jött ki az augusztus 17.
7) Albert dühös, mert Bernard győzött. Ezért Bernard helyébe képzeli magát, és végigmegy a fenti hat lépcsőn. Végül ő is rájön, amire mi: Bernardnál a 17. szerepel. Erre kijelenti, ő is tudja a választ.
Így a jó válasz augusztus 17.
De hogy kaphatunk egy kérdésre két különböző megoldást?
Attól függ, hogyan értelmezzük az első állítást. Ha Albertnek arra kellett következtetnie, hogy Bernard nem tudja a választ, a megoldás július 16.
Ám ha Albert valóban tudja, hogy Bernard nem tudja, azaz ezt tényként, és nem következtetésként közli, augusztus 17-ét kapjuk.
Ez a hajszálnyi eltérés – következtetés kontra tény – teljesen megváltoztatja a kérdés természetét.
Magyarán, ha tényként kezeljük, a választ az első két állításból kitalálhatjuk, míg a július 16-hoz mind a három állításra szükségünk van.
Tehát jó az augusztus 17.?
Nem. A kérdés szerzői elvetik ezt az alternatív megoldást.
Azt állítják, Bernard a beszélgetés kezdetén nem árulta el, hogy nem tudja a választ, így Albert ezt nem tudta tényként kezelni.
(És igazuk is van, ezért is szerepel három állítás a feladványban.)
Boldog új évet!

Fejlécek használata a táblázatban (strukturált hivatkozások)
2017. december 18.

Verzió: Excel 2013, 2010, 2007

Az Excel 2007 óta adatainkat táblázatokban kezelhetjük. Ez a lehetőség nagy segítség, ha az adatokat prezentálni vagy elemezni szeretnénk. (Hagyományos keresések, feltételes összegzések stb.) Ha olyan munkafüzetekkel dolgozunk, melyek folyamatosan bővülnek, nagyon fontos, hogy a felhasznált képletek konzisztenciájára ügyeljünk. A gyakorlatban ez azt jelenti, hogy lefelé vagy jobbra húzva (másolva) helyes eredményeket kapjunk. És ez sokszor csak azon múlik, helyesen használjuk-e a hivatkozásokat (abszolút, relatív vagy vegyes). Ha adataink azonban táblázatba rendezettek, a konzisztencia érdekében alkalmazhatjuk a strukturált hivatkozásokat és az oszlopfejléceket.

Vegyünk egy (itt) kicsi, de komplex, a táblázat előállítására és a strukturált hivatkozások bemutatására alkalmas példát.
Az adatok beírása után kijelöltük az adattartomány egyik celláját, majd Ctrl + T. A Táblázat rovatfejekkel jelölőnégyzet be van jelölve. OK. Ezt kaptuk. A táblázat nincs kész.

Látjuk, nincs kiszámítva a nettó (ár + szállítás) az áfa (nettó * 027) és az összes (nettó + áfa) fejléccel jelölt oszlop.
Először a nettót töltjük ki.
1) Az F2 cellába írjunk egyenlőségjelet, majd kattintsunk a D2 cellába, majd + E2, majd Enter. Ezt kapjuk:
=[@Ár]+[@Szállítás]
Látjuk, ez az oszlop az egész táblázat "kitöltődött".
Ugyanígy járunk el a többi oszlopban is:
G2: =[@Nettó]*0,27
H2: =[@Nettó]+[@Áfa]

Nos, készek a strukturált hivatkozások, a képen az utolsó oszlop látható.
Táblázatunknak a beszédes tbl_pld nevet adtuk, az alapból kapott Táblázat1 átnevezésével.
Lássuk a feladatot!
Azt szeretnénk, ha az ID- alapján összesítve megkapnánk az egyes tételeket, valahogyan így:

Először is egy olyan képletet kell írnunk a C14 cellába, melyet egyszerűen jobbra és lefelé másolva megkaphatjuk az összes szükséges és helyes(!) adatot.
(Ez lenne a konzisztens képletünk.)
Ha megnézzük a táblázatot, látható, hogy egy vevő (ID) többször is szerepelhet (több sorban).
Továbbá, a feltételes összegzésekre a feltételes összegzések (=SZUMHA(), =SZUMHATÖBB() alkalmasabbak, mint a hagyományos keresési függvény, mely csak az első találatot adja eredményként.
Az is látható, hogy az összesítésben más az oszlopok sorrendje, mint az adatokban, azaz olyan képletre van szükségünk, mely alkalmazkodik ehhez az eltéréshez.
Mindent egybevetve, ha az oly sokszor emlegetett =INDEX() és =HOL.VAN() függvénykombót "becsomagoljuk" a =SZUMHATÖBB() függvénybe, nem érhetünk el rossz eredményt.
A =SZUMHATÖBB() függvény egy adott tartomány több feltételnek is eleget tevő celláit adja meg.
Szintaxis:
=SZUMHATÖBB(összegtartomány; kritériumtartomány1; kritérium1; [kritériumtartomány2; kritérium2]; ...)
Az összegtartomány legalább egy összegzendő cella; tartalmazhat számokat, neveket, tartományokat vagy számokat tartalmazó cellahivatkozásokat.
Az alkalmazás figyelmen kívül hagyja a szöveges értékeket és az üres cellákat.
A kritériumtartomány1: a kapcsolódó kritériumok kiértékelésére használt első tartomány.
A kritérium1: a kritériumtartomány1 argumentum összeadandó celláit meghatározó, számként, kifejezésként, cellahivatkozásként vagy szövegként megadott kritérium, például: 32, ">32", B4, "Vezetéknév" vagy "32".
A kritériumtartomány2; kritérium2;...: a további tartományok és az azokhoz társított kritérium.
Esetünkben az első argumentum a táblázat tbl_pld[Összes] oszlopa.
A képlet ilyen lenne C14):
=SZUMHATÖBB(tbl_pld[Összes];tbl_pld[ID];$B14)
Így:
=SZUMHATÖBB(tbl_pld[Összes];tbl_pld[ID];$B14)
=SZUMHATÖBB($H$2:$H$11;tbl_pld[ID];$B14)
"Megtalálta" a H oszlopot.
=SZUMHATÖBB($H$2:$H$11;$B$2:$B$11;$B14)
"Megtalálta" a B oszlopot.
=SZUMHATÖBB($H$2:$H$11;$B$2:$B$11;"A777")
"Megtalálta" az A777 ID-t.
És az eredmény:
53 848.
Ha azonban az összesítés következő (D14) oszlopába akarjuk alkalmazni a képletet, akkor a tbl_pld[Ár] oszlopot kell használnunk, így
=SZUMHATÖBB(tbl_pld[Ár];tbl_pld[ID];$B14)
Látjuk, mivel a képletben az első argumentumot minden oszlopban újra kell írni, minden alkalommal új képletre van szükségünk.
Ez igazán nem kényelmes és jó megoldás, hogyan lehetne ezt dinamikusan megoldani?
Erre jó megoldás a sokszor ütősnek nevezett INDEX/HOL.VAN kombó. Már nagyon sok példában szerepelt, részletes és tömör összefoglalás itt olvasható. Most csak röviden.
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.
Azért elég érdekes, ha egy függvény vagy egy cella értékét (tömbös változat), vagy cellák hivatkozását (hivatkozásos forma) adja eredményként.
Esetünkben a hivatkozásos formát választjuk, mert ez lesz alkalmas a SZUMHATÖBB() első argumentumának.
Szintaxisa:
=INDEX(hivatkozás;sor_szám;[oszlop_szám];[terület_szám])
Hivatkozás: egy vagy több cellatartományra való hivatkozás.
Sor_szám: a hivatkozásban annak a sornak a száma, amelyikből az eredményhivatkozást meg szeretnénk kapni.
Oszlop_szám: a hivatkozásban annak az oszlopnak a száma, amelyikből az eredményhivatkozást meg szeretnénk kapni.
Terület_szám: a hivatkozásnak azt a tartományát jelöli ki, amelyből a sor_szám és oszlop_szám által meghatározott eredményhivatkozást meg szeretnénk kapni.
Az első kijelölt vagy megadott terület az 1-es számú, a következő a 2-es számú stb.
Ha a terület_szám argumentumot nem adjuk meg, akkor az INDEX az 1-es számú területet veszi figyelembe.
Ezzel az első argumentumunk dinamikus, ezt az =INDEX() biztosítja, mivel cellatartományt ad eredményként.
A =HOL.VAN() dinamikusan találja meg az odaillő oszlopot.
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.
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.
Esetünkben ez ilyen lesz:
SZUMHATÖBB(INDEX(HOL.VAN(…));tbl_pld[ID];$B14)
Az INDEX/HOL.VAN kombó biztosítja, hogy a =SZUMHATÖBB() a megfelelő oszlopból kapja a számokat.
Azaz az =INDEX() függvénnyel egy hivatkozást kapunk, és a =HOL.VAN() függvényt arra "kérjük", mondja meg az =INDEX() függvénynek, a fejléc alapján melyik oszlopra hivatkozzon.
Mivel a =HOL.VAN() egy elem tömbben elfoglalt relatív pozícióját adja vissza, azt "kérjük", mondja meg az összesítés oszlopfejléc megfelelő oszlopszámát a táblázatból.
Ha tehát az Ár oszlop a negyedik a táblában, az =INDEX() 4-et kap.
Az =INDEX() ezt az információt sz Ár mint oszlophivatkozást adja a =SZUMHATÖBB() függvénynek.
A függvényben ez a hivatkozás lesz az összegtartomány argumentum.
Lássuk a mentét:
Először cseréljük ki a =SZUMHATÖBB() első argumentumát egy =INDEX() függvénnyel.
Ezt kapjuk:
=SZUMHATÖBB(INDEX(tbl_pld;;4);tbl_pld[ID];$B14)
=SZUMHATÖBB(INDEX(tbl_pld;;4);tbl_pld[ID];$B14)
=SZUMHATÖBB(INDEX($A$2:$H$11;;4);tbl_pld[ID];$B14)
"Megtalálta" a táblázatot.
=SZUMHATÖBB($D$2:$D$11);tbl_pld[ID];$B14)
"Megtalálta" a D, a 4. oszlopot.
=SZUMHATÖBB($D$2:$D$11;$B$2:$B$11;$B14)
"Megtalálta" a B, az ID oszlopot.
=SZUMHATÖBB($D$2:$D$11;$B$2:$B$11;"A777")
"Megtalálta" az összegzésben az ID-t.
És az eredmény:
40000.
Az =INDEX() első argumentuma a kezdő tartományt, a táblázatot adja.
Második argumentuma, a sor_szám megadja, melyik sorból adja az eredményt.
Mivel az összes sorra szükségünk van, ezt az argumentumot üresen hagyjuk.
Az =INDEX() harmadik argumentuma az oszlop_szám. Ide 4-et írunk, mert az Ár a táblázat negyedik oszlopa.
Így az =INDEX() megadja a hivatkozást a 4. oszlopra
Itt azonban nem állhatunk le, mert az oszlop_szám argumentumot értékkel (hard coded) adtuk meg.
Ha jobbra húzzuk a képletet, a 4 mindig 4 marad, és az összesítő mindegyik oszlopában ezt az eredményt látjuk.
Ahogy már jeleztem, dinamikus megoldásra van szükség, hogy az Excel "kitalálja", az =INDEX() melyik oszlopot alkalmazza a képletben.
Erre szolgál a =INDEX(): megtudhatjuk, melyik oszlophoz tartozik a megfelelő fejléc.
A képlet:
=HOL.VAN(C$13;tbl_pld[#Fejlécek];0)
Ezzel a képlettel arra vagyunk kíváncsiak, a táblázatban hányadik oszlopban vannak a C13 (Összes) cellában megadott értékek. Az eredmény 8, és valóban a táblázat utolsó, 8. oszlopa tartalmazza ezeket a tételeket.
Figyeljük meg, relatív oszlophivatkozást alkalmazunk ©, így frissül a jobbra húzáskor, és abszolút sorhivatkozást ($13), ez rögzíti a fejlécsort.
Itt használjuk a strukturált hivatkozásokat, melyek a fejlécek sorára hivatkoznak.
A képlet a táblázat nevével kezdődik (tbl_pld), majd szögletes zárójelben a #Fejlécek.
Ezt be is írhatjuk, de az egérrel is kijelölhetők. A harmadik argumentum 0, mert pontos egyezésre van szükségünk.
Mivel a =HOL.VAN() eredménye 8, be kell illesztenünk az =INDEX() függvénybe.
Ezt kapjuk:
=INDEX(tbl_pld;;HOL.VAN(C$13;tbl_pld[#Fejlécek];0))
Ezzel megkaptuk az oszlophivatkozást, melyet a =SZUMHATÖBB() vár. Beillesztjük:
=SZUMHATÖBB(INDEX(tbl_pld;;HOL.VAN(C$13;tbl_pld[#Fejlécek];0));tbl_pld[ID];$B14)
Írjuk a C14 cellába.
Ha azt hisszük, ezt a képletet másolhatjuk jobbra, ill. lefelé húzással hát tévedünk.
Lehet használni a Másolással, Kitöltéssel, mert az Excel a tbl_pld[ID] hivatkozást abszolútnak értelmezi. Ám ha a kitöltőnégyzetet használjuk a jobbra húzáskor, ez megváltozik: tbl_pld[Ár], tbl_pld[Szállítás], tbl_pld[Áfa] lesz belőle. (Na jó, lefelé nem.)
Hogy ez ne fordulhasson elő, egy kis módosítás: egy oszlopra vonatkozó hivatkozássá alakítjuk.
=SZUMHATÖBB(INDEX(tbl_pld;;HOL.VAN(C$13;tbl_pld[#Fejlécek];0));tbl_pld[[ID]:[ID]];$B14)
És a kész munka.

Még szebbé alakíthatjuk, ha a cellahivatkozást is átalakítjuk:
=SZUMHATÖBB(INDEX(tbl_pld;;HOL.VAN(C$13;tbl_pld[#Fejlécek];0));tbl_pld[[ID]:[ID]];ID)
Ehhez z kell, ha az összegzés táblázatban az ID-ket tartalmazó tartományt definiált névvel ID-nek nevezzük.
Boldog Karácsonyt!

Napi eredmények változásai
2017. december 11.

Verzió: Excel 2013, 2010, 2007

Néhány héttel ezelőtt az =ELőJEL() függvény segítségével és "némi" feltételes formázással mutattam be egy megoldást a napi eredmények trendjeinek vizualizálására. Most egy olvasói kérdés alapján egy szinttel mélyebbre haladunk. Az akkori példában a napi eredmények egymást követő napok eredményeit mutatják, azaz nincs szünnap (vasárnap, ünnepek). Most erre is mutatunk megoldást.

Nézzük az eredeti példát:

A lényeg, a D oszlopban a változás nem más, mint az aktuális és a megelőző napi forgalom különbsége.
Az előző példához képest annyi a változás, hogy a szemléltetés érdekében a napokat is megjelenítettük. Az egység szombaton és vasárnap zárva tart.
Továbbá (a kérdésnek megfelelően) szeretnénk pontosan látni a változás mértékét az előző naphoz viszonyítva.
És továbbra is szeretnénk egy vizuális megoldást, mely mutatja, a napi forgalom nőtt, csökkent vagy változatlan az előző napihoz képest.
A feladat első része egyszerű.

A képlet a D4 cellában:
=HA(C4<>0;C4-C3;"")
Levezetve:
1) =HA(4900<>0;C4-C3;"")
2) =HA(IGAZ;C4-C3;"")
3)=HA(IGAZ;4900-C3;"")
4)=HA(IGAZ;4900-3100;"")
5) =HA(IGAZ;1800;"")
6) =1800
Ezt másoltuk (húztuk le) a többi cellába.
Ám van egy kis bökkenő!
Január 9-én, hétfőn nem nőtt 5100-zal a forgalom, mert az utolsó napon, pénteken 5400 volt, tehát a reális (valós) adat: 300!
(Azaz az üres cella/cellák gondot okoznak. (Ugyanez a helyzet január 16-án is.)
A képlet az üres cellákat is felhasználja a számításban, így pl. a január 9-i hétfő (D10) cellájában:
=HA(C10<>0;C10-C9;"")
Az előbbi módon levezetve (röviden)
4) =HA(IGAZ;5100-0;"")
Így természetesen 5100 az eredmény.
Azonban nekünk az utolsó értékesítési adatra van szükségünk, amik (itt) a pénteki adatok.
Tehát így:

Így már jó a január 10-i (hétfői) adat, hiszen ténylegesen 300-zal csökkent a forgalom az utolsó (pénteki) forgalomhoz viszonyítva, ugyanígy helyes a számítás egy hét múlva (D17).
De hogyan oldottuk meg?
A képlet a D4 cellában:
=HA(ÜRES(C4);"";C4-($C$3+SZUM($D$3:D3)))
Nézzük a megoldást: két új függvényt vetettünk be: =ÜRES(), =SZUM().
Az =ÜRES() az érték típusát vizsgálja meg, és a vizsgálat eredményétől függően IGAZ vagy HAMIS értéket ad eredményül.
A függvény eredménye az IGAZ logikai érték, ha az érték egy üres cellára mutató hivatkozás, míg minden más esetben a HAMIS logikai érték.
Általában a HA függvénnyel együtt alkalmazzuk.
Önmagában alkalmazva az eredmény IGAZ vagy HAMIS.
A levezetés a D4 cellában kezdődik, vegyük észre, hogy a táblázat kezdő cellái (C3 és D3 abszolút hivatkozásokként is szerepelnek -- mint a megoldás sarokkövei.
=HA(ÜRES(C4);"";C4-($C$3+SZUM($D$3:D3)))
Ez:
=HA(ÜRES(C4);"";C4-($C$3+SZUM($D$3:D3)))
Ebből:
1) =HA(HAMIS;"";C4-($C$3+SZUM($D$3:D3)))
(Hiszen a C4 cella nem üres benne az első érték, 4900.)
2) =HA(HAMIS;#HIÁNYZIK;4900-($C$3+SZUM($D$3:D3)))
3) =HA(HAMIS;#HIÁNYZIK;4900-(3100+SZUM($D$3:D3)))
(Megkaptuk az első nap értékét: C3 = 3100.)
4)=HA(HAMIS;#HIÁNYZIK;4900-(3100+0))
Innen kihagyva néhány lépést, egyszerű:
8) 4800-3100 = 1800
Ha megnézzük a D10 cellában, a két üres cella utáni hétfő mellett álló képletet, akkor látjuk igazán a =SZUM() értelmét ebben a megoldásban:
Az előző levezetés megfelelő sorától:
3) =HA(HAMIS;#HIÁNYZIK;5100-(3100+SZUM($D$3:D9)))
(A forgalom [C10] 5100. A kezdő érték a 3100 [C3]. És most kell összeadnunk az eddigi napi változásokat [D3:D9].)
4) =HA(HAMIS;#HIÁNYZIK;5100-(3100+2300))
(1800 +(-600) + 100 + 1000 + 0 + 0 = 2300. Ehhez jön a 3100, ez 5400. 5100 - 5400 = -300.)
És máris jó a megoldás. Egyetlen képlet, nincsenek egymásba ágyazott argumentumok, egyszerű.
A vizualizálást ismét a feltételes formázással oldhatjuk meg, most másképp, mint a kiinduló példában.

A megoldás menete
Jelöljük ki a D4:D19 (utolsó cella) tartományt.
Kezdőlap > Feltételes formázás > Új szabály.
A párbeszédablakban válasszuk az Összes cella formázása az értékük alapján opciót. A Formátumstílus menüből válasszuk: Ikonkészletek. Ikonstílus: Válaszuk a nekünk tetsző alakzatokat. OK.

A Típus lenyílóban ne feledjük a százalékot átállítani. Ha elsőre nem leszünk elégedettek a megoldással, a Szabályok kezelése menüben kattintsunk a Szabály szerkesztése gombra. Ekkor kezdhetünk minden előröl: ikonstílus, beállíthatjuk az értékek határait, eltüntethetjük őket (csak az ikon látszik) stb.

Ugorjunk különös helyekre (az Excelben)
2017. december 4.

Verzió: Excel 2013, 2010, 2007

Nem, ez nem kirándulás, az Excel-lap speciális hely(szín)eit kereshetjük fel. Már több ízben volt tárgyunk, most egy kis összefoglaló az Ugrás lehetőségeiről. (Azért nem feledjük, ez angolul az Excelben Go To.

Több lehetőségünk van az Ugrás párbeszédablak megjelenítésére:
1. A legegyszerűbb: F5.
2. Ha a billentyűkombinációkat kedveljük: Ctrl + G.
3. Ha a Menüszalag hívei vagyunk: Kezdőlap > Szerkesztés > Keresés és kijelölés > Ugrás, így

Az eredmény mindhárom esetben ugyanez, megkapjuk az Ugrás párbeszédablakot:

Az Ugrás párbeszédablakot leginkább a definiált nevekkel meghatározott hivatkozások közötti gyors navigálásra használjuk. Így pl. ha az egyik munkalapon m_név és m_jutalék definiált nevünk van, az Ugrás így jelenik meg:

Így aztán kiválaszthatjuk a kívánt tartományt.
Ennyi lenne? Nem. A bal alsó sarokban látható Irányított? gobra kattintva új párbeszédablak jelenik meg:

(Ez a párbeszédablak változott az idők során, a különböző Excel verziók eltérőek lehetnek. Ez egy 2013-as változat.)
Látható, rengeteg lehetőséggel "játszadozhatunk".
Az elmúl években különböző összefüggésekben már volt szó az üres cellák kiemeléséről, kitöltéséről, az elődök kimutatásáról, a képleteket tartalmazó cellák kiemeléséről, a sorkülönbségek kimutatásának lehetőségeiről. Most néhány újabb speciális helyről lesz szó.
Állandók
Ha ezt választjuk, az Excel azokat a cellákat választja ki, melyek állandókat tartalmaznak. Ezek gyakorlatilag azok a cellák, melyek értékeket és nem képleteket tartalmaznak. Ha erre e gombra kattintunk, szűkíthetjük a területeket: számok, szöveg, logikai értékek, hibák.
Nagyon hasznos megoldás, ha például a Lapvédelem megoldást akarjuk alkalmazni. (Alapbeállításban ugye minden cella zárolt. Ha a bevitelre szolgáló cellákat fel akarjuk oldani, ezzel a megoldással egyszerre megtehetjük az összes cellával.
Képletek
Ezt az opciót választva megkapjuk az összes képletet tartalmazó cellát. Ha erre e gombra kattintunk, most is szűkíthetjük a területeket: számok, szöveg, logikai értékek, hibák.
Ez nagyon kényelmessé és megbízhatóvá teszi munkánkat, ha gyorsan szeretnénk azonosítani a hibás képleteket.
Csak a látható cellák
Ezt az opciót választva megkapjuk a kijelölt tartomány látható celláit, azaz a szűréssel vagy manuálisan elrejtett sorok vagy oszlopok kizárásával.
Ezért nagyon hasznos, ha adatainkat szűrtük. Mondjuk, ha a szűrt tartományt másoljuk, majd beillesztenénk, az Excel a kijelölt tartomány összes celláját beilleszti, függetlenül attól, látható volt-e vagy rejtett.
Ha így történik, nincs baj, alkalmazzuk az Ugrás lehetőségét. Jelöljük ki a teljes (szűrt) tartományt, másolás előtt pedig Csak a látható cellák.
Az Excel frissíti a kijelölt tartományt, kizárja a rejtett cellákat.
Most már jöhet a Ctrl + C és Ctrl + V.
Adatok érvényesítése
Ha ezt az opciót választjuk, azokat a cellákat kapjuk meg, melyeket az Érvényesítéssel töltöttünk ki.
Nagyon jól jön, ha ellenőrizzük a munkát, és meg szeretnénk tudni, mely celláknál alkalmaztuk az Érvényesítést.
Ide tartozik, ha az Érvényesítés alkalmazása után meg akarjuk tudni, mely cellák nem felelnek meg az Érvényesítés szabályainak, alkalmazzuk az Érvényesítés > Érvénytelen adatok bekarikázása parancsot.
(Csak zárójelben: olyan cellákra is alkalmazhatjuk az Ényesítést, amelyek már tartalmaznak adatokat. Az Excel nem jelenít meg automatikusan figyelmeztetést abban az esetben, ha a meglévő cellákban érvénytelen adatok találhatók. Ilyen helyzetben az érvénytelen adatok megjelölésére beállíthatjuk, hogy az Excel bekarikázza az érvénytelen adatokat. Az érvénytelen adatok azonosítását követően a bekarikázások ismét elrejthetők. Az érvénytelen bejegyzések javítása után a bekarikázások automatikusan eltűnnek. A primitív példában a beállítás: 4 és 7 közötti egész számok.)

Ezek a legfontosabb és kevésbé olyan egyértelmű helyek az Excel-lapon, mint az Utolsó cella vagy Megjegyzések. De "kirándulni" mindig érdekes.

Az utolsó n oszlop adatainak összege
2017. november 27.

Verzió: Excel 2013, 2010, 2007

Előfordulhat, hogy olyan táblázattal dolgozunk, mely rendszeresen frissül. Ilyen, ha minden hónapban új oszlopot adunk hozzá, és az utolsó három hónap összegére vagyunk kíváncsiak. (Persze nem csupán három oszlopról lehet szó.)

Pontosítsunk: olyan képletre van szükségünk, mely az utolsó három oszlopban "működik"; nem akarunk minden alkalommal -- ha új oszlopot adunk az előzőkhöz -- új képletet, nem akarjuk módosítani (frissíteni) a képletet, nem, ugyanazzal akarunk dolgozni.
Képzeljük el a következőt: a táblázat (neve Táblázat1) a régiók összesített eredményeit mutatja, havi bontásban, melyet folyamatosan frissítünk. Most itt tartunk, és az utolsó három oszlop (Április, Május, Június adatait akarjuk összesíteni a C10 cellában.

Az ábra szemlélteti a feladatot.
A következő hónapban bekerül a Július, és ismét az utolsó három hónap (Május, Június, Július) összegére vagyunk kíváncsiak.

Az ábrán itt is kiszámoltuk (hagyományosan) az utolsó 3 oszlop összegét.
A C10 cella automatikusan frissült, a képlet:
=SZUM(INDEX(Táblázat1;0;OSZLOPOK(Táblázat1)-2):INDEX(Táblázat1;0;OSZLOPOK(Táblázat1)))
A megoldás:
A =SZUM() függvénybe (argumentumokként) beágyaztuk az =OSZLOPOK() és az =INDEX() függvényeket, így a képlet automatikusan összegzi a táblázat utolsó három oszlopának adatait, ráadásul akkor is, ha minden hónaphoz új oszlopot szúrunk be.
Lássuk a részleteket.
A képlet alapja a =SZUM() függvény. Mint jeleztem, a táblázat neve Táblázat1, így a cellák összegét a következő képlettel kaphatjuk meg:
=SZUM(Táblázat1).
Viszont nincs szükségünk az összes cella összegére, (most) csak az utolsó három oszlopban szereplőékre.
Ezért, hogy szűkítsük a táblázat hivatkozási területét, szükségünk van az =INDEX() függvényre.
A függvény magyarázata helyett egy egyszerű példa: ha csak a harmadik oszlop (ez a Február összegét akarjuk megkapni, az =INDEX() segítségével, a képlet:
=SZUM(INDEX(Táblázat1;0;3)), melyben
Táblázat1 a teljes táblázat,
0: minden sort figyelembe veszünk a tartományban,
3: a 3. oszlop adataira van szükségünk.

(Persze más lehetőségek is vannak, de most az =INDEX() a lényeg.)
Ha azonban nem a harmadik oszlopra van szükségünk, hanem az utolsóra, más megoldást kell választanunk, az argumentum nem lehet mindig változó szám, mint itt a 2, azt szeretnénk, ha az Excel (hát ezért van, nem?) magától találja meg a táblázatban az utolsó oszlop számát.
Itt jutunk el az =OSZLOPOK() függvényhez, mely egy hivatkozásban vagy egy tömbben lévő oszlopok számát adja eredményül.
Itt például ez a szám 8.

Ahogy a táblázatban egyre több/kevesebb oszlop lesz, frissül az =OSZLOPOK() függvény: 9, 10 és így tovább.
Ha össze akarjuk adni a táblázat utolsó oszlopának értékeit, itt a 8. oszlopét, a képlet:
=SZUM(INDEX(Táblázat1;0;OSZLOPOK(Táblázat1)))

A képletben
Táblázat1 a teljes táblázat,
0: minden sort figyelembe veszünk a tartományban,
OSZLOPOK(Táblázat1: az utolsó oszlop adataira van szükségünk.
Még mindig nem értünk a végére, hiszen nem az utolsó, hanem (most) az utolsó három oszlop összegét akarjuk megkapni.
Vegyünk be még egy =INDEX() függvényt a játékba.
Így az egyiket a kívánt tartomány első oszlopának meghatározására (ez az 5.), a másodikat az utolsó (ez a 7.)
(Az 1. oszlop [a sorok fejlécei] nem tartozik a tartományba.)
Nemrég volt szó az operátorokról.
A tartományoperátor két =INDEX() függvénnyel is működik, így a Május-Július adatait összegző képlet (2. ábra):
=SZUM(INDEX(Táblázat1;0;OSZLOPOK(Táblázat1)-2):INDEX(Táblázat1;0;OSZLOPOK(Táblázat1))).
A képletben az
INDEX(Táblázat1;0;OSZLOPOK(Táblázat1)-2: a Május oszlopra hivatkozik, az
INDEX(Táblázat1;0;OSZLOPOK(Táblázat1) a Július oszlopra hivatkozik.
Mivel az =OSZLOPOK() függvény minden esetben frissül, ha a táblázat bővül (vagy szűkül), a =SZUM() minden esetben az utolsó három oszlopot veszi figyelembe.
(Természetesen ha nem 3 hónap összegzéséről van szó, hanem hatról, 2 helyet 5-öt kell kivonnunk az első =INDEX() oszlop_szám argumentumából.)
Bónusz
Ezzel a trükkel az utolsó sorok adatai is összeadhatók, a =SOROK() függvénnyel, így:
INDEX(Táblázat1;SOROK(Táblázat1);0).
Az INDEX() függvényről:
A már többször szerepelt 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.

Keresés több oszlopban: FKERES és SZUMHATÖBB
2017. november 20.

Verzió: Excel 2013, 2010, 2007

Ha keresnünk kell, egyértelműnek tűnik az =FKERES() alkalmazása. Ha több kritériumról (feltételről) és oszlopról van szó, föltehetőleg a =SZUMHATÖBB() jut eszünkbe.
Ugyanakkor, ha a várt eredmény szöveg, azaz nem numerikus érték, a =SZUMHATÖBB() nem alkalmazható, mert eredménye csak szám lehet. Vagy mégis?

Most arról lesz szó, hogyan kereshetünk több oszlopban, több feltétel alapján, és hogyan kapunk szöveges értéket eredményként a két függvény kombinációjával.
Vegyünk egy nagyon egyszerű példát: munkatársak adatait tartalmazza a táblázat, melynek neve Táblázat1.

Azt szeretnénk, hogy a munkatársak vezeték- és keresztneve alapján kapjuk meg működési területüket (Régió).
Valahogy így:

Persze ha a beszámolóban a vezeték- és keresztnév helyett az ID állna, könnyű lenne a dolgunk, egy sima =FKERES() megoldaná.
Esetünkben azonban két oszlopunk van: vezeték- és keresztnév. Ha az eredmény, amit meg szeretnénk kapni, szám lenne, mint például az irányítószám, használhatnánk a =SZUMHATÖBB() függvényt.
Mivel a =SZUMHATÖBB() eredmény szám, és nem szöveg mint a régió, a függvény nem alkalmazható.
Továbbá: több oszlopról van szó, így az =FKERES() sem alkalmas erre a feladatra.
Ha külön-külön nem megy, mi lenne, ha együtt alkalmaznánk a két függvényt -- egy képletben?
A megoldás
Alapvető, hogy a =SZUMHATÖBB() segítségével megkapjuk a munkatársak egyedi ID-jét, hogy aztán ez az érték legyen az =FKERES() első argumentuma.
Magyarán: az =FKERES() keresési_értékét a =SZUMHATÖBB() adja meg.
A =SZUMHATÖBB() az ID-t a keresett munkatárs vezeték- és keresztnév oszlopaiból kapja, majd az =FKERES() ezt használja a kereséshez, melynek eredménye a Régió.
Összefoglalva: a =SZUMHATÖBB() az =FKERES() első argumentuma.
Lépések:
A Táblázat1 adatai sorba rendezettek (B oszlop). Az első lépés az egyedi ID-k kinyerése a =SZUMHATÖBB() függvénnyel.

A képlet:
=SZUMHATÖBB(Táblázat1[ID];Táblázat1[Vezetéknév];C18;Táblázat1[Keresztnév];D18)
A képletben
Táblázat1[ID]: az összegzendő számok oszlopa (B);
Táblázat1[Vezetéknév]: az első kritériumtartomány, a vezetéknév oszlopa (C);
C18: az első kritérium, a vezetéknév érték;
Táblázat1[Keresztnév]: a második kritériumtartomány, a keresztnév oszlopa (D);
D18: a második kritérium, a keresztnév érték.
Látható, megkaptuk a nevekhez tartozó ID-ket.
Már csak egy a dolgunk: meg kell kapnunk az ID-khez tartozó régiókat.
Fontos!
Meg kell jegyezni, ez a megoldás csak akkor működik, ha az összes ID egyedi, azaz nincs ismétlődő érték az oszlopban. (Csak ez az egy sor felel meg a =SZUMHATÖBB() kritériumainak.
Ha nem így van, kénytelenek vagyunk egy segédoszlopot beilleszteni, mely sorszámozza az adatokat, pl. 1, 2, 3 és így tovább.
Lássuk a =SZUMHATÖBB() függvényt az =FKERES() függvénybe ágyazva:

A képlet:
=FKERES(SZUMHATÖBB(Táblázat13[ID];Táblázat13[Vezetéknév];C18;Táblázat13[Keresztnév];D18);Táblázat1;5;0)
A képletben
SZUMHATÖBB(...): a munkatársak ID-je, az =FKERES() keresési_érték argumentuma;
Táblázat1: a táblázat mint a tábla argumentum;
5: az oszlop_szám, melyben az érték (Régió) áll;
0: az egyezés [tartományban_keres], pontos egyezést akarunk, ezért HAMIS.
Ezzel kész!
Ugyanígy kereshetünk például dátumokra (értékesítés), és ha akarjuk, összehasonlító operátorokkal dátumtartományokban is kereshetünk.
Az alkalmazott függvényekről már ezerszer volt szó, így csak röviden:
SZUMHATÖBB
A =SZUMHATÖBB() függvény egy adott tartomány több feltételnek is eleget tevő celláit adja meg.
Szintaxis:
=SZUMHATÖBB(összegtartomány; kritériumtartomány1; kritérium1; [kritériumtartomány2; kritérium2]; ...)
Az összegtartomány legalább egy összegzendő cella; tartalmazhat számokat, neveket, tartományokat vagy számokat tartalmazó cellahivatkozásokat.
Az alkalmazás figyelmen kívül hagyja a szöveges értékeket és az üres cellákat.
A kritériumtartomány1: a kapcsolódó kritériumok kiértékelésére használt első tartomány.
A kritérium1: a kritériumtartomány1 argumentum összeadandó celláit meghatározó, számként, kifejezésként, cellahivatkozásként vagy szövegként megadott kritérium, például: 32, ">32", B4, "Vezetéknév" vagy "32".
A kritériumtartomány2; kritérium2;...: a további tartományok és az azokhoz társított kritérium. Itt ez a Keresztnév oszlop, ill. a D18 cella.
FKERES
Az "F" a függőlegest jelenti, azaz az értékeket függőlegesen (egy oszlopon belül) lehet keresni.
A függvény egyben az Excel egyik legtöbbet alkalmazott képlete. Amikor az FKERES függvényt alkalmazzuk, lényegében ezt mondjuk a függvénynek: "Itt egy érték. Menj egy másik helyre, találd meg ezt az értéket, és mutasd meg azokat a szavakat vagy számokat, melyek egy cellában megegyeznek a keresett értékkel."
A függvény "anatómiája"

A függvény egy tömb bal szélső oszlopában keres egy megadott értéket.
Az így kapott sorból veszi az oszlop_szám argumentummal kijelölt cellát, és ennek tartalmát adja eredményül.
Szintaxis:
=FKERES(keresési_érték;tábla;oszlop_szám;[tartományban_keres])
Keresési_érték: a tömb első oszlopában megkeresendő érték. Ez lehet érték, hivatkozás vagy karaktersorozat.
Tábla: az adatokat tartalmazó cellatartomány.
Lehet tartományhivatkozás vagy egy tartomány neve idézőjelek között.
Oszlop_szám: a tábla azon oszlopának a táblán belüli sorszáma, amelyből az eredmény meg kívánjuk kapni.
Tartományban_keres: logikai érték, amellyel a függvény pontos vagy közelítő keresését adhatjuk meg.
Ha értéke IGAZ vagy hiányzik, akkor a visszaadott érték közelítő lehet, azaz ha pontos egyezést nem talált a függvény, akkor a következő legnagyobb, de a keresési_érték argumentumnál kisebb értéket adja vissza.
Ha az argumentum értéke HAMIS, akkor az FKERES pontos egyezést keres, és ha ilyen nincs, akkor a #HIÁNYZIK hibaértéket adja eredményül.
Ha a tartományban_keres értéke IGAZ vagy hiányzik, a tábla első oszlopában lévő értékeknek növekvő sorrendben kell elhelyezkedniük, különben a függvény hibás eredményt adhat.
A függvény nem tesz különbséget a kis- és nagybetűk között.
A függvényt leginkább a következőkre alkalmazzák:
1. Egy adattábla egyetlen értékének vagy értékkészleteinek (-sorozatainak) megkeresésére.
2. Egy másik adattartomány néhány egyedi jellemzőjű adatokból álló oszlopának hozzáadása egy adattáblához.
Sok esetben azonban nem tudjuk pontosan, mit keresünk. (Képzeljünk el egy sorok százaiból (ezreiből) álló dokumentumot.) Az FKERES függvényben a keresési_érték argumentumban használhatunk helyettesítő karaktereket.
Helyettesítő karakterek:
? - "kérdőjel": egyetlen karaktert helyettesíthet,
így pl. a ?épés lehet tépés, lépés.
* - "csillag": tetszőleges számú karaktert helyettesíthet,
így a *kész lehet cserkész, félkész, fürkész.
Ha a helyettesítő karaktert (? vagy *) keressük, egy "tilde" (~) karaktert kell a helyettesítő karakter elé tenni.
Így a Hol~? eredménye Hol?

Napi eredmények követése az =ELŐJEL() függvénnyel
2017. november 13.

Verzió: Excel 2013, 2010, 2007

Látványos megoldás napi eredmények vizualizálására, rögtön két megoldással. (Természetesen egyéb időszakok [hetek, hónapok stb.] is szóba jöhetnek.)

Az ábra egy kis részletet mutat: napi bevételek.

A D oszlopba szeretnénk egy mutatót, mely mutatja, a napi forgalom nőtt, csökkent vagy változatlan az előző napihoz képest.
1. megoldás

A képlet (D4)
=HA((C4-C3)<0;"Rosszabb";HA((C4-C3)>0;"Jobb";"Változatlan"))
Hogy látványosabb legyen, némi feltételes formázással "feldobtuk":
A megoldás:
Kezdőlap > Stílusok > Feltételes formázás

A megoldások (beágyazott =HA() függvények, feltételes formázás) annyira egyszerűek, hogy nem érdemes ragozni őket.
2. megoldás
A második megoldás az =FKERES() és a címben ígért =ELŐJEL() függvény lehet.
Némi előkészület kell hozzá.
Az =FKERES() függvényhez készült egy kis táblázat, mely az =ELŐJEL() függvény eredményeit adja a 2. oszlopból.
Az =ELŐJEL() függvény egy szám előjelét határozza meg.
Ha a szám pozitív, eredménye: 1. Ha a szám negatív, eredménye: -1. Ha a szám nulla (0), eredménye: 0.
Szintaxisa: =ELŐJEL(szám)
Szám: tetszőleges valós szám.

A képlet:
=FKERES(ELŐJEL(C4-C3);$C$14:$D$16;2)
Hogyan működik?
Az =ELŐJEL() függvény összeveti az aktuális cella eredményét az előzőével, majd az =FKERES() segítségével megkapjuk az adekvát szöveget a táblázat (C14:D16) 2. oszlopából.

Nullák előre!
2017. november 6.

Verzió: Excel 2013, 2010, 2007

Ne riasszon el a cím. Egyszerűen arról van szó, hogy gyakran szükségünk van a vezető, élen álló zérókra, gondoljunk csak azonosítókra, számlaszámokra, melyek zéróval kezdődnek. Hogy még bonyolultabb legyen, az értékek nem egyforma hosszúságúak.

A módszer egyszerű, vezető/élen álló nullákkal kel feltöltenünk az értékeket.
A probléma akkor keletkezik, ha a szövegértékek a munka során számokká konvertálódnak, és a zérók egyszerűen eltűnnek.

Szeretnénk visszakapni a nullákat.
Látható, hogy az F oszlopban a számok különböző hosszúságúak, míg az eredetiben 6 számjegyből álltak
Több lehetőségünk van.
Első megoldás: a =SZÖVEG() függvénnyel
Ez a legegyszerűbb (a legjobb?).
A szemléltetés miatt átrendeztem a táblázatot.

A képlet:
=SZÖVEG(B3;"000000")
A =SZÖVEG() függvény értéket alakít át adott számformátumú szöveggé.
Szintaxisa:
=SZÖVEG(érték;formátum_szöveg)
Érték: számérték vagy számértéket adó képlet, illetve számértéket tartalmazó cella hivatkozása. (Itt a B3:B7 cellák.)
Formátum_szöveg: egy numerikus formátum idézőjelbe tett szöveges karakterlánc, például "000000" vagy "éééé.hh.nn", vagy "# ##0,00".
A függvény az értéket átalakítja formázott szöveggé, és az eredményt már nem szám formában számolja.
Ha a Formátum menü Cellák formázása párbeszédpanel Szám panellapján formázunk egy cellát, akkor a számnak csak a formátuma változik meg, az értéke nem.
A példában a "000000" a számformátum, mely a számot 6 számjegyűvé formázza. A zérók a számformátumban számjegyhelyőrzők. Ha a szám nagyobb nullánál, megjelenik, ha kisebb, nullát kapunk.
Így ez az egyszerű képlet adja meg a zérók korrekt számát az eredeti értékek előtt, és 6 számjegyet alkot.
Fontos hangsúlyozni, a képlet eredménye szöveg. Számnak tűnik, de valójában szöveges érték.
Így használható az olyan keresési függvényben, mit az =FKERES() vagy HOL.VAN, ha az ID alapján keresnénk, és a nevet akarjuk megkapni.
Második megoldás: a =JOBB() függvénnyel
Előfordul, hogy a zérókkal feltöltendő szám szöveget is tartalmaz. Ebben az esetben nem működik a =SZÖVEG() függvény, hiszen számformátumot alkalmaz.
A =JOBB() függvény akkor alkalmazható, ha az érték számokat VAGY szöveget tartalmaz. Azaz mindkét esetre alkalmazható. Univerzális. Vagy mondjam, mint a svájci bicska?
A megoldás menete
Első lépés: a zérók
Először a vezető nullákat kell a számok elé tennünk. A kis példában a legkisebb szám háromjegyű, ezért három zérót kell az elejére tennünk.
Ezzel biztosítjuk, hogy minden szám legalább 6 számjegyű lesz.

Néhány szám hosszabb 6 számjegynél, de egyelőre rendben van.
Második lépés: a =JOBB() függvény: a fölösleges zérók eltüntetése
Most következik az egységesítés, a fölösleges zérók eltávolítása

A képlet:
=JOBB(C3;6)
A függvény a szöveg megadott számú utolsó karaktereit adja vissza.
Szintaxis:
=JOBB(szöveg;[hány_karakter])
Szöveg: az a szöveg, amelyből a karaktereket ki szeretnénk venni.
Hány_karakter: a kiemelni kívánt karakterek száma.
Így voltaképpen minden fölösleges (plusz) nullát eltávolítottunk.
Két lépésben mutattam be az eljárást, de össze is vonható:

Továbbra is fontos hangsúlyozni, a képlet eredménye szöveg. Számnak tűnik, de valójában szöveges érték.
Végül röviden a kombinált megoldás lehetőségéről. Betűkkel egészítettük ki az ID-ket:

És működik.
Nem szóltam a legkézenfekvőbb megoldásról, melyben a numerikus érték szám marad, mert ez lehet előny, de hátrány is. Ez a Cellaformázás.
Menete, jelöljük ki a formázandó cellákat, majd jobb egérgomb: Cellaformázás > Egyéni. A párbeszédpanelben a Típus mezőbe írjuk be a hat zérót.
Hátránya a megoldásnak, hogy szöveges érték keresésére NEM alkalmas! Más függvényekkel kombinálva érhetjük el ugyanazt az eredményt, mint a fentebb leírtakkal.

Értéksorok egyszerű szorzása egy szorzóval
2017. október 30.

Verzió: Excel 2013, 2010, 2007

Olvasói kérdésre, válaszolva. Ha van egy hosszabb listánk, melyben számokat szeretnénk egyszerűen és gyorsan (például) azonos összeggel megszorozni, nagyon könnyen megtehetjük.

Tételezzük fel, van egy árlistánk, nettó, áfa, bruttó bontásban. (A neveket az egyszerűség miatt elhagyom.)
Szeretnénk az árakat egy adott százalékkal megemelni (mi mást tehetnénk?).
Lehetne egy segédoszlopot is használni, de esetlegesen a layout nem engedné.
Ugyanakkor az is jó lenne, ha az új értékek a régiek helyére kerülnénk.
A Beillesztés beépített funkcióval "helyben" végezhetjük el az alapműveleteket, így a szorzást is.
A megoldás menete
1) Ha minden értéket pl. 10%-kal akarunk emelni, egy üres cellába írjuk be: "1,1". Ez a cella lehet ugyanazon a lapon, melyen a lista áll, de akár egy másikon is. Itt ez az F2.
2) Jelöljük ki ezt a cellát, majd másoljuk a Vágólapra (Ctrl + C billentyűkombináció).
3) Jelöljük ki a módosítandó cellatartományt, itt a B3:B9 cellákat.
4) Kezdőlap > Vágólap csoport > Beillesztés. A szimbólum alatti kis nyílra kattintva megnyílik a párbeszédablak. Válasszuk: Irányított beillesztés

5) Az új párbeszédablakban, a Művelet szekcióban jelöljük be a Szorzás opciót.

Az OK-ra kattintva ezt kapjuk. Az összes nettó tétel közvetlenül az eredeti cellában 10%-kal emelkedett, így természetesen a többi számított érték is.

Láthatjuk, további alapműveleteket is el tudnánk végezni, és más lehetőségeink is vannak.

DARABTELI használata az FKERES helyett
2017. október 23.

Verzió: Excel 2013, 2010, 2007

Ellenőriznünk kell, hogy adott nevek szerepelnek-e egy hosszú oszlopban felsorolt nevek között. Rengeteg lehetőség kínálkozik, a leginkább kézenfekvőbbnek az FKERES() tűnik, és nem alaptalanul.

Ugyanakkor néhány érv a DARABTELI() mellett szól.
Lássuk őket!
1. Gyorsabb.
2. Egyszerűbb.
3. Nehezebb hibázni használatával.
A példa a 60-as évek "hitlistái" alapján készült. Az egyszerűség kedvéért a számok címét elhagytam.

Hogyan működik a DARABTELI?
A függvény nagyon egyszerű: egy tartományban összeszámolja azokat a cellákat, amelyek eleget tesznek a megadott kritériumnak.
Szintaxis: =DARABTELI(tartomány;kritériumok)
Tartomány: egy vagy több összeszámolandó cella; tartalmazhat számokat, neveket, tömböket vagy számokat tartalmazó hivatkozásokat.
A függvény figyelmen kívül hagyja a szövegértéket és az üres cellát.
Kritériumok: az összeszámolandó cellákat meghatározó, számként, kifejezésként, cellahivatkozásként vagy szövegként megadott feltétel.
A feltétel lehet:
szám, így pl. =DARABTELI(A1:A5;100),
szöveg idézőjelben, így pl. =DARABTELI(A1:A5;"The Beatles"),
operátor idézőjelben, így pl. =DARABTELI(A1:A5;">100").
A kritériumok argumentumban használhatók a helyettesítő karakterek.
A kritériumokban nincs különbségtétel kis- és nagybetű között; a "The Rolling Stones" és a "The rolling StonEs" karakterlánc például ugyanazokat a cellákat fogja megtalálni.
Az eredmény szám, megegyezik az adott tartomány találatainak számával.
Alapértelmezésben a DARABTELI() függvény csak a pontos találatokat jeleníti meg, ezeket lehet finomítani az operátorokkal és helyettesítő karakterekkel.
De vissza: hányszor szerepelnek a keresett nevek a listában?

Ha a DARABTELI függvény eredménye 0, ez azt jelenti, hogy a keresett érték nincs a listában.
Ez történt Lady Gaga (már elnézést!) esetében.
Miért érdemes a DARABTELI() függvényt használni az FKERES() helyett?
(Ha csak azt akarjuk megtudni, szerepel-e az érték egy tartományban.)
Már általánosítottam három előnyt, nézzük tovább:
1. A DARABTELI() függvénynek csak két argumentuma van, ezért könnyű és egyszerű. Az FKERES() négy argumentuma több.
2. A DARABTELI() az összes előfordulás/találat számát adja eredményül, így látjuk, az FKERES() csak az elsőt.
3. Ha az érték nincs a listában, a DARABTELI() eredménye nulla (0). Nem kell aggódnunk, jó-e a képlet, az FKERES() eredmény hibaérték, ha el akarjuk kerülni, újabb függvényt kell bevetnünk (pl. HAHIBA().

Egyértelmű, Lady Gaga HIÁNYZIK, (nekem ugyan nem), hibaértéket kaptunk eredmény helyett.
Ne feledkezzünk meg a Nagy Testvérről!
A =DARABTELI() öccse (mert később "született"!) a =DARABHATÖBB().
(A rokoni kapcsolatot még jobban mutatja angol elnevezésük: COUNTIF és COUNTIFS.
A függvény kritériumokat alkalmaz több tartomány celláira, és megszámolja, hogy hány alkalommal teljesül valamennyi kritérium.
Szintaxis: =(DARABHATÖBB(kritériumtartomány1; kritérium1; [kritériumtartomány2; kritérium2]...)
Kritériumtartomány1: a kapcsolódó kritériumok kiértékelésére használt első tartomány.
Kritérium1: a megszámlálandó cellákat meghatározó, számként, kifejezésként, cellahivatkozásként vagy szövegként megadott feltétel.
Kritériumtartomány2, kritérium2, ...: a további tartományok és az azokhoz társított kritérium. Legfeljebb 127 tartomány-feltétel pár engedélyezett.
A függvény tehát több kritérium meghatározását teszi lehetővé, melyekkel több tartományt vizsgálhatunk.
Így a példában a 60-as évekből az 1963-as, 1964-es és 1965-ös listákat nézzük.

Látjuk, hogy az adott évben az adott együttesnek hány listavezető száma volt.
Fontos: Minden további tartománynak ugyanannyi sorral és oszloppal kell rendelkeznie, mint a kritériumtartomány1 argumentumnak,
és a tartományoknak nem kell egymással szomszédosnak lenniük.
A program minden tartomány kritériumát egyszerre csak egy cellára alkalmazza.
Ha az első cellák mindegyike megfelel a hozzá rendelt kritériumnak, a szám eggyel nagyobb lesz.
Ha a második cellák mindegyike megfelel a hozzá rendelt kritériumnak, ismét eggyel nő a szám, és ez így megy tovább, amíg az összes cella értékelése meg nem történik.
Ha a kritérium argumentuma egy üres cellára hivatkozik, a DARABHATÖBB függvény 0 (nulla) értékként kezeli az üres cellát.
A függvény egy kritériummal is alkalmazható, mint a DARABTELI.
De lépjünk egy szintet.
A DARABHATÖBB függvényt alkalmazhatjuk, ha ugyanaz a tartomány, azaz két vagy több önálló DARABHATÖBB függvényt adhatunk össze. Ez a módszer minden egyes kritériumot egyenként megszámol, majd összeadja az eredményeket.
Így ha kíváncsiak vagyunk, hány listás száma volt a Stones-nak ebben három évben, az eredmény: 6.

=DARABHATÖBB($B$2:$B$35;B38;$C$2:$C$35;C38)+DARABHATÖBB($B$2:$B$35;B38;$C$2:$C$35;C39)+DARABHATÖBB($B$2:$B$35;B38;$C$2:$C$35;C40)
Vagy, és ez egy újabb bejegyzés, a =SZUM() függvénybe csomagoljuk, és kapcsos zárójelekkel tömböket hozunk létre, kb. így:
=SZUM(DARABHATÖBB(tartomány;{kritérium1;kritérium2})

Cellatartományok
2017. október 16.

Verzió: Excel 2013, 2010, 2007

Ha a cellatartományok kerülnek szóba, csaknem mindenkinek a kettőspont jut eszébe: a kezdő és az utolsó cella közéhelyezve definiálja a tartományt. Vagy a dollárjel ($), valahogyan rögzíti a cellahivatkozást.
Ám ez csak a felszín.

A cellahivatkozásokról már többször volt szó, legrészletesebben itt, amikor kedvencünkről, a dollár jeléről írtam.
Az operátorokról azonban kevesebb szó esett.
Az operátorok vagy műveleti jelek határozzák meg, hogy milyen (számítási) műveleteket kell a képlet elemein végrehajtani, így pl. összeadást vagy szorzást; illetve cellatartományokat egyesíthetünk számítások céljaira.
Ebben a bejegyzésben erről lesz szó, de bevezetésként lássuk a műveleti jelek rendszerét.
Az Excel a következő négy operátortípust tartalmazza: számtani, összehasonlító, szövegösszefűző és hivatkozási operátor.
1. Aritmetikai operátorok
A legkézenfekvőbb: az alapvető matematikai műveletek (így pl. összeadás, kivonás, szorzás) elvégzésére, a számok kombinálására és a számszerű eredmények kiszámítására a következő számtani műveleti jelek használhatók: pluszjel, mínuszjel, csillag, perjel, százalékjel, kalap.
2. Összehasonlító operátorok
Két értéket hasonlíthatunk össze, az eredmény az IGAZ vagy a HAMIS logikai érték.

Összehasonlító operátorJelentésPélda
= (egyenlőségjel)EgyenlőA1=B1
> (nagyobb jel)Nagyobb mintA1>B1
< (kisebb jel)Kisebb mintA1
>= (nagyobb vagy egyenlő jel)Nagyobb vagy egyenlőA1>=B1
<= (kisebb vagy egyenlő jel)Kisebb vagy egyenlőA1<=B1
<> (nem-egyenlő jel)Nem egyenlőA1<>B1

3. Szövegösszefűző operátor
Az et- vagy és-jel (&) használatával több szöveges karakterláncot kapcsolhatunk össze, azaz több szöveges karakterláncot fűzhetünk egybe, így egyetlen szövegegységet hozhatunk létre.
Szöveges jel Jelentés Példa
& (és-jel) Két érték összekapcsolásával, azaz összefűzésével egyetlen folytonos szövegértéket hoz létre. ("munka"&"füzet")

4. Hivatkozási operátorok
Most érkeztünk mondanivalónkhoz.
A következő operátorokkal cellák tartományait egyesíthetjük számítások céljából.
Hivatkozási operátor Jelentés Példa
: (kettőspont) Tartományoperátor, amely a két hivatkozás közötti összes cellára (beleértve a két hivatkozást is) egyetlen hivatkozást eredményez. B2:D4
; (pontosvessző) Egyesítő operátor, amely több hivatkozást egyesít egyetlen hivatkozásba. SZUM(B5:B15;D5:D15)
(szóköz) Metszetoperátor, amely a két hivatkozásban közös cellákra mutató hivatkozást eredményez. B7:D7 C6:C8

A kevésbé ismert metszetoperátorról részletesen itt esett szó.
Néhány példa

Tartományoperátor
Nem bonyolult, ám ha az egyesítő operátorral együtt alkalmazzuk (nem szólva a metszetoperátorról!), komoly erőt jelent.

Az ábrából úgy tűnik, egyszerűen csak listázzuk a cellákat, pontosvesszővel elválasztva. Mint a mondatban: "Almát, körtét, szőlőt; diót, mogyorót, mandulát."
Látható, két tartomány összesítetünk egyetlenné.
A kevésbé ismert metszetoperátor a szóköz karakter. Emlékeztetőül:

A képlet: =SZUM(C2:C4 B3:E3) eredménye 6, a két tartomány (C2:C4 és B3:E3) metszéspontja, a C3 cella.
Ha definiált nevekkel "meghatározzuk" a tartományokat, minden keresési függvény helyett alkalmazható.
Részletes megoldás, leírás itt található.
Emlékeztető:

Függvények, melyek eredménye cellatartomány
Ha cellatartományokkal van dolgunk, az egyik nagy kihívás, ha nem ismerjük a tartományba bevonni kívánt cellák címét.
Szerencsére néhány függvény rendelkezésünkre áll.
INDIREKT()
A függvény eredménye a szövegként megadott hivatkozás.
ELTOLÁS()
Egy 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.
A függvénynek akár öt argumentuma is lehet.
=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 hivatkozásnak egyetlen cellára vagy egy cellatartományra kell vonatkoznia, ellenkező esetben az ELTOLÁS az #ÉRTÉK! hibaértéket adja eredményül.
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).
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).
Ha a sorok és az oszlopok által megadott mértékben eltolt hivatkozás túllóg a munkalap szélén, akkor az ELTOLÁS a #HIV! hibaértéket adja eredményül.
Magasság: az eredmény hivatkozás magassága a sorok számában mérve. A magasság csak pozitív szám lehet.
Szélesség: az eredmény hivatkozás szélessége az oszlopok számában mérve. A szélesség csak pozitív szám lehet.
Ha a magasság vagy a szélesség argumentumot nem adjuk meg, akkor a program a hivatkozás magasságát és szélességét használja.
Megjegyzés
Az ELTOLÁS valójában nem tol el 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.
A függvénnyel már rengetegszer foglalkoztunk.
Talán egy példa:

=SZUM(ELTOLÁS(C4;-2;-1;2;3))
Nézzük a képletet:
Az ELTOLÁS a hivatkozással kezdődik (C4), majd 2 cellányit felfelé mozog (C2), majd 1 cellányit vissza (B2).
A két utolsó argumentum kiszámítja az tartomány méretét. Így a B2-től indulva a magasság 2, a szélesség 3, ezért a végső cellatartomány B2: D3.
Az eredmény: 1 + 2 + 3 + 5 + 6 + 7 = 24.
INDEX()
A függvény megegyezik az ELTOLÁS első három argumentumával.
A fő különbségek:
1) az első argumentum a cellatartomány az eredménycellával együtt.
2) A függvény jellegéből eredően a számlálás 1-gyel kezdődik (azaz =INDEX(A1:D5;1;1), ez ugyanaz, mint az =ELTOLÁS(A1:D5;0;0)).

Súlyozott átlag
2017. október 9.

Verzió: Excel 2013, 2010, 2007

Ha értékek átlagát akarjuk kiszámítani, a leggyakoribb feltételezés, hogy minden érték egyforma fontosságú, azaz egyforma súllyal mérjük.
Ilyen (volt) például az iskolai eredmény, a jegyek egyforma értéket képviselnek, az átlag egyben súlyozott átlag is: 4, 4, 2, 2 =12, az átlag: 12/4=3, közepes!

Az életben a dolgok többnyire nem így mennek. Vannak fontos, és vannak kevésbé fontos dolgok.
Mit mond a Wikipédia?
"A súlyozott átlag a számtani közép általánosítása. A kettő között az a különbség, hogy az egyes értékeknek itt nem feltétlenül egyenlő a szerepe. Egyes értékek nagyobb súllyal eshetnek a latba, mint mások.
A súlyozott átlag általában valamilyen súlyokkal ellátott értékek számtani átlagára utal, de ennek mintájára meg lehet határozni az értékek súlyozott mértani átlagát és súlyozott harmonikus átlagát is." (Forrás: Wikipédia.)
Vegyünk egy nagyon egyszerű példát.

A képlet:
=SZUM(C3*D3;C4*D4;C5*D5;C6*D6;C7*D7;C8*D8;C9*D9)
Az egyszerűség miatt a "súlyokat" százalékban fejeztem ki, továbbá: összegük 100%!
Néhány megjegyzés:
1. A módszer roppant egyszerű, de egyben hihetetlenül unalmas, fárasztó.
2. Nagy a hibázás lehetősége.
3. Néhány tétel esetében esetleg alkalmazható.
4. Kell lennie a SZUM() függvénnyel egy egyszerűbb megoldásnak is.

A képlet:
{=SZUM(C3:C9*D3:D9)}
A trükk a tömbképlet: az Enter lenyomása helyett a Ctrl + Shift + Enter billentyűkombinációt alkalmaztuk. (A SZUM() függvény nem kezeli a tömböket.)
A tömbképletről már többször volt szó.
Letölthető demó munkafüzet itt,
megvásárolható a {Ctrl + Shift + Enter} Tömbképletek (Titkok - Trükkök - Tippek) CD-ROM.
Ez a megoldás már átvezet a súlyozott átlag kiszámításának korszerűbb megoldásához.

A képlet:
=SZORZATÖSSZEG(C3:C9*D3:D9)
A SZORZATÖSSZEG() az egyik kedvenc függvényem, már nagyon sokszor szerepelt,legutóbb itt.
Röviden:
A felhasznált függvény a =SZORZATÖSSZEG(), megadott tömbök megfelelő elemeit szorozza össze, majd kiszámolja a szorzatok összegét.
Az első tömb (oszlop) értékeit megszorozzuk a megfelelő (azonos sorban lévő) második oszlop értékével. Az összes érték összege adja az eredményt.
Szintaxis:=SZORZATÖSSZEG(tömb1;tömb2;tömb3;...)
Tömb1: Az első tömbargumentum, amelynek a szorzatösszegét ki szeretnénk számítani.
Tömb2, tömb3,...: A 2-255. tömbargumentum, amelynek a szorzatösszegét ki szeretnénk számítani.
Megjegyzés:
A SZORZATÖSSZEG függvény egy tömböt is értelmez, így példánkban.
Lássuk:

Eddig a példákban a "súlyok" összege pontosan 100 volt. Mi van akkor, ha ez nem így van?
Ugyanezek az adatok más súlyokkal, ha összeadjuk őket, az eredmény 200.

Látható, hogy abszurd eredményt kaptunk.
(A súlyok dupla értékkel szerepelnek, így a súlyozott átlag is kétszeres [72,6 * 2 = 145,2]).
Viszont 100 pontnál többet nem lehet tételenként kapni.
A hibás eredmény oka, hogy a súlyok összege nem 100 (itt százalék).
Mi a megoldás?

A SZORZATÖSSZEG eredményét elosztjuk a súlyok összegével, így a "súlyok" összege mindig 100 lesz.
=SZORZATÖSSZEG(C3:C9*D3:D9)/SZUM(D3:D9)
A különböző súlyok alkalmazásának jó példája a Súlyozott Átlagos Tőkeköltség vagy Átlagos Tőkeköltség (Weighted Average Cost of Capital) kiszámítása.
Az eddigi példákban közös volt, hogy a súlyokat pontosan meghatároztuk.
Az esetek egy részében azonban a pontos súlyok nem állnak rendelkezésünkre, ezeket ki kel számítanunk.
Vegyünk egy új példát:
Különböző (itt 3) termékeket különböző árakon értékesítünk.

Az átlagos ár 2 222, a képlet:
=SZORZATÖSSZEG(C3:C5;D3:D5)/SZUM(C3:C5).
Ha a SZORZATÖSSZEG eredményét elosztjuk a mennyiségek összegével, a súlyok (esetünkben a mennyiségek) összege 100.

Dinamikus összegzés
2017. október 2.

Verzió: Excel 2013, 2010, 2007

Előfordulhat, hogy egy lista adatait (értékek) valamilyen szempont alapján gyorsan, minden "bűvészkedés" nélkül kell dinamikusan összeadnunk. Íme egy szimpla megoldás.

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

Napi értékesítési adatokat látunk (eladott darabok).
Azt szeretnénk, hogy a kezdő dátumtól (C2) addig a sorig adja össze az adatokat, melyben a képlet áll. Esetünkben ez az E15.
A képlet =SZUM(C$2:INDEX(C:C;SOR())
Ezzel adtuk össze a C2:C15 tartomány adatait, azaz a képlet alapja a SZUM(C2:C15).
Mi a képlet előnye?
Különösen hasznos, ha hosszú listákkal van dolgunk: elegendő csak a kezdő érték pontos helyét ismernünk.
Az összesítésnél alkalmazott tartomány záró hivatkozását az INDEX() függvény adja.
A már többször szerepelt 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 példában a tömb a teljes oszlop, a sor_számot a SOR() függvény adja.
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 történik.
Így a számítás menete:
=SZUM(C$2:INDEX(C:C;SOR()))
SZUM($C$2:INDEX($C:$C;15))
,
hiszen a SOR eredménye 15. Így
SZUM($C2:$C$15)
SZUM($C$2:$C$15)
3 263

Természetesen a tartomány kezdő celláját mi határozzuk meg.
A képlet logikáját követve más eredményeket is megkaphatunk, így pl. a tartomány MAXimumát, MINimumát, ÁTLAGát stb.

(Meg kell jegyezni, ha csak kiváncsiak vagyunk az eredmény(ek)re, a tartomány kijelölése után ugyanezek láthatók az Állapotsoron is..)

Ennyi.

Legújabb Excel tipp