A "születésnap" paradoxon
2014. december 29.

Szilveszteri móka

Tételezzük fel, hogy munkahelyünkön 23 ember dolgozik. KÉRDÉS: Mekkora a valószínűsége annak, hogy két ember egy napon született? (Felejtsük el február 29-ikét (szökőév), azt, hogy vannak ikrek, akik egy napon születtek; a probléma megoldása a lényeg.)

És a válasz: 50 százalék!
Meglepő, de így van. Lássuk!
Ha a közösség 366 főből állna, statisztikailag garantált, hogy ketten egy napon születtek, hiszen összesen 365 születésnap lehetséges egy évben.
Sokan azt válaszolják, hogy a kérdésre a helyes válasz 183, mert ez a legkisebb egész szám, amelyik nagyobb, mint 365/2 (=182,5). (Egy kicsit előre szaladva: a 183 egy egész más kérdésre adott helyes válasz: "Hány embernek kell részt vennie egy rendezvényen, hogy 50% legyen az esélye annak, hogy valakivel egy napon születtem?") Azonban, ha nincs kikötve, melyik két emberről van szó, hatalmas a különbség.
Hogyan számoltuk ki ezt?
A számításhoz alkalmazzuk a fordított valószínűséget -- azaz azt, hogy a csoportban nincs két ember, akinek egy napra esik a születésnapja.
Ennek a kiszámítása sokkal könnyebb, mint azé, hogy a munkahelyen legalább két embernek ugyanazon a napon van a születésnapja.
Annak a valószínűsége, hogy két ember nem ugyanazon a napon született:

Annak a valószínűsége, hogy három ember nem ugyanazon a napon született:

Annak a valószínűsége, hogy négy ember nem ugyanazon a napon született:

Ha így haladunk, amikor a 23-ik személy csatlakozik a társasághoz, az utolsó osztás 343/365, és az eredmény 0,5 alatt van (0,493). ez a valószínűsége, hogy 23 ember nem ugyanazon a napon született:

Ez azt jelenti, hogy mivel annak a valószínűsége, hogy senkinek sincs azonos születésnapja, 49,3%; tehát megfordítva 50,7% az esély arra, hogy legalább két ember azonos napon született.
Nézzük meg egy grafikonon:

A grafikonon jól látható, ha az előzőek mellett azt is feltételezzük, hogy a születések egyforma valószínűségűek, elég 57 embert összeszedni ahhoz, hogy 99% esélye legyen annak, hogy közülük ketten ugyanazon a napon születtek.
Nézzük a paradoxon mibenlétét. Az első gond, hogy még ha tanultunk is matematikát, valljuk be, a hatványkitevőket nem "vágjuk".
Egy példa: mekkora az esélye (valószínűsége) annak, hogy a fej vagy írás játékban egymás után 10 "fej" jöjjön ki?
Agyunk így gondolkodik: "50% az esély, hogy fejet dobjunk. Két fej dobása kétszer 'nehezebb', így 25% az esély. Tízszer egymás után? Talán tízszer kisebb az esély, így 50%/10, azaz 5%."
Aztán ha egy kicsit visszaemlékszünk a tanultakra, rájövünk, nem osztásról van itt szó, hanem hatványozásról, a 10 egymást követő fej valószínűsége nem 0,5/10, hanem 510, azaz kb. 0,01. De vissza az eredeti kérdéshez! Mekkora a valószínűsége, hogy egy 23 fős csoportban legyen két ember, aki ugyanazon a napon született? Egyszerűen: hasonlítsuk össze a résztvevőket. Ám ez nehéz ügy, lehet 1, 2, 3; vagy akár 23 találatot is kaphatunk! Olyan ez, mintha a "fej vagy írás" játékban arra lennénk kíváncsiak, hány "fejet" dobunk 23 dobásból. Válasz van bőven: az első dobásból vagy az ötödik dobásból vagy az utolsóban, vagy a másodikban és a harmadikban?
Hogyan oldjuk meg? Fordítsunk egyet: inkább azt nézzük, mekkora az esélye, hogy az összes feldobás eredménye "írás" lesz?
Ha ennek a valószínűsége 1% (bár azt mondanám, 0,523), akkor 99% a valószínűsége, hhogy legalább egy fejet kapunk. Nem tudjuk, hármat, tízet vagy huszonhármat kapunk-e, a lényeg, hogy lesz "fej".
Ha ezzel a megoldással dolgozunk, azaz, ha a valószínűséget kivonjuk 1-ből, jó úton haladunk. Ha az ellenkező valószínűséget számoljuk, azaz nem azt a módot keressük, hogyan találjuk meg az egy napon születetteket, hanem annak a valószínűségét, hogy mindenkinek más napon van a születésnapja, ez a megoldás.
Boldog új esztendőt!

Szövegek gyakorisága
2014. december 22.

Verzió: 2003, 2007, 2010, 2013

Tételezzük fel, hogy egy táblázat egyik oszlopának celláiban események regisztrálását végezzük. Ez lehet -- egy üzletkötő esetében -- helységek neve, cégek, személyek neve stb. A cellában azonban olykor nem csak egy helység neve vagy név áll (pl. Szeged, Pécs; ABC Kft., XY Bt.), hanem kombinációk is, mint például "Veszprém, Székesfehérvár érintésével", "ABC Kft. és XY Bt."

Ennek ellenére szeretnénk megtudni, hányszor szerepelt pl. "Pécs" vagy az "XY Bt".
A DARABTELI vagy SZUMHA függvények szóba sem jöhetnek, hiszen esetükben a kereséshez konkrét kritériumokra van szükség, ezért csak akkor kaphatnánk használható eredményeket, ha a keresett név egyetlen érték lenne a cellában.
Összetett számolások és összeadások elvégzésére kiválóan alkalmas a SZORZATÖSSZEG.
Mint ismeretes, a SZORZATÖSSZEG megadott tömbök megfelelő elemeit szorozza össze, majd kiszámolja a szorzatok összegét.
Az argumentumokban -- adott kritériumok lekérdezésére --felhasználhatunk más Excel függvényeket. Ebben az esetben is így járunk el.
Az utazási célokat tartalmazó oszlopban a SZÖVEG.KERES függvénnyel megkeressük az adott helysége.
A függvény a karaktersorozat pozícióját adja eredményként; ha nem találja, az eredmény az #ÉRTÉK! hibaüzenet.
Az adott utazási cél előfordulása tehát az utazási célokat tartalmazó oszlopban talált cellák száma, (amelyekben a SZÖVEG.KERES függvény eredménye nem hibaérték.
Mindezt egy képletbe foglalhatjuk.
Ha a városok neve a B2:B10 tartományban van, és a keresett helység az E2 cellában, a képlet:

=SZORZATÖSSZEG(NEM(HIBÁS(SZÖVEG.KERES(E2;$B$2:$B$10)))*1)
A képletben a
=NEM(HIBÁS(SZÖVEG.KERES(E2;$B$2:$B$10))) rész a B2:B10 tartomány minden egyes celláját megvizsgálja, és eredményként IGAZ értéket ad vissza, ha megtalálja a keresett értéket (itt "Győr"), amikor a SZÖVEG.KERES nem hibaértéket ad. HAMIS értéket kap az a cella, melyben a keresett név nem szerepel.
Azonban az IGAZ és HAMIS logikai értékekkel a SZORZATÖSSZEG nem tud mit kezdeni, ezért az eredményt megszorozzuk 1-gyel. Így egy olyan tömböt kapunk, melyben 1-esek (=IGAZ) és 0-k (=HAMIS) szerepelnek. Ezek összege adja meg, hogy hányszor voltunk az adott városban.
A képletek:
SZORZATÖSSZEG
A függvényt részletesen bemutattam itt.
NEM
Az argumentum értékének ellentettjét adja eredményül. Akkor használjuk, amikor biztosítani szeretnénk, hogy egy érték egy megadott értékkel ne egyezzen meg.
Ha az argumentum értéke HAMIS, a függvény visszatérési érték IGAZ, ha az argumentum értéke IGAZ, a visszatérési érték HAMIS.
HIBÁS
Típusellenőrző függvény. Eredménye IGAZ, ha az érték valamelyik hibaértékre hivatkozik (#HIÁNYZIK, #ÉRTÉK!, #HIV!, #ZÉRÓOSZTÓ!, #SZÁM!, #NÉV? vagy #NULLA!).
Az ellenőrző függvényekkel információt kaphatunk egy értékről, mielőtt számítást vagy más műveletet végeznénk vele.
Szintaxis: =HIBÁS(érték)
Érték: a vizsgálni kívánt érték. Lehet üres cella, hibaérték, logikai érték, szöveg, szám, illetve ezek bármelyikére mutató hivatkozás vagy név.
SZÖVEG.KERES
Egy karaktersorozatban egy másikat keres, és eredményül a találat első karakterének helyét adja a szöveg elejétől számítva.
A függvény a keresésnél nem tesz különbséget a kis- és a nagybetűk között.

Hiányzó elemek egy összefüggő tartományban
2014. december 18.

Verzió: 2003, 2007, 2010, 2013

Fél évvel ezelőtt már volt egy hasonló probléma: egy tartomány hiányzó elemeit akartuk valamilyen megoldással megtalálni. Az akkori megoldás a KICSI, HA, HIBÁS, HOL.VAN, SOR, ELTOLÁS függvényeket alkalmazta. Most egy "elegánsabb" megoldást kínálok.

Előfordulhat, hogy egy adatsorból (bizonylatok száma, számlaszámok stb.), melyek sorrendben követik egymást, szeretnénk kigyűjteni a hiányzó (például a nem beérkezett bizonylatok, a kiegyenlítetlen számlák számát) sorszámokat. Természetesen szükséges, hogy a számtartomány összefüggő legyen.
A példában összesen 7 tétel szerepel (A oszlop), de elképzelhető, milyen hasznos ez a megoldás, ha több száz sorról van szó. Látható, hogy az értékek nem sorba rendezettek, és egy üres cella is van a tartományban.
Ezért a képletnek kezelnie kell a rendezetlen listát és az üres cellákat.

A képlet (C1:C7):
{=KICSI(HA(DARABTELI($A$1:$A$7;SOR($1:$12))=0;SOR($1:$12);"");SOR(A1))}
A képlet tömbképlet, lényegéről már volt szó, részletesen itt.
Magyarázat:
(DARABTELI($A$1:$A$7;SOR($1:$12) :
Megszámolja az A1:A7 tartományban azokat a számokat, melyeket a SOR($1:$12) generál, azaz az {1;2;3;4;5;6;7;8;9;10;11;12} számokat,
HA(?))=0;SOR($1:$12);"");
ha azok = 0 (azaz ha hiányoznak), listát készít, ha nem = 0 (tehát nem hiányoznak a listából), "" jelet kapunk (azaz semmit),
KICSI(?)SOR(A1)
és ebben a cellában az első legkisebb hiányzó értéket "szállítja".
A SOR(A1) cellahivatkozás relatív, így a képlet lehúzható a C oszlopban, és a SOR(A1) SOR(A2) lesz, ami = 2-vel,
és így a 2. legkisebb hiányzó értéket adja eredményként, a SOR(A3) értéke 3, ez a 3. legkisebb érték, és így sorban.
A függvényekről:
KICSI
Szintaxis: =KICSI(tömb;k)
Tömb: az a számértékeket tartalmazó tömb vagy tartomány, amely értékek közül a k-adik legkisebbet kell meghatározni.
k: azt adja meg, hogy a legkisebbtől számítva nagyság szerint hányadik elemét kell meghatározni az adattömbnek vagy -tartománynak.
SOR
Egy hivatkozás sorának számát adja meg.
Szintaxis: =SOR(hivatkozás)
Hivatkozás: Az a cella vagy cellatartomány, amelyben meg kell állapítani a sor számát.
DARABTELI
Egy tartományban összeszámolja azokat a cellákat, amelyek eleget tesznek a megadott feltételnek.
Szintaxis: =DARABTELI(tartomány;feltételek)
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.
Feltételek: 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;"Szia"),
operátor idézőjelben, így pl. =DARABTELI(A1:A5;">100").
HA
A függvény egy feltételt vizsgál.
Más értéket ad vissza, ha a megadott feltétel kiértékelésének eredménye IGAZ, és másikat, ha HAMIS.
Szintaxis: =HA(logikai_vizsgálat;érték_ha_igaz;érték_ha_hamis)
Logikai_vizsgálat: tetszőleges érték vagy kifejezés, amely kiértékeléskor IGAZ vagy HAMIS eredményt ad.
Érték_ha_igaz: ezt az értéket adja a függvény eredményül, ha a logikai_vizsgálat eredménye IGAZ.
Érték_ha_hamis: ezt az értéket adja a függvény eredményül, ha a logikai_vizsgálat eredménye HAMIS.
És együtt!
A megoldás lényege a tömbképlet, amely egy tömböt (tehát több elemet) vizsgál egy képlettel.
{=KICSI(HA(DARABTELI($A$1:$A$7;SOR($1:$12))=0;SOR($1:$12);"");SOR(A1))}
1. lépés
Az A1:A7 tartományt vizsgálja, az eredmény a SOR(1:12) :
=KICSI(HA(DARABTELI({1;7;5;8;;10;12};{1;2;3;4;5;6;7;8;9;10;11;12})=0;SOR($1:$12);"");SOR(A1))
2. lépés
A DARABTELI (a tömbből származó eredményként) azokat az értékeket számolja meg, melyeket a SOR(1:12) képlet alapján az A1:A7 tartomány tartalmaz:
=KICSI(HA({1;0;0;0;1;0;1;1;0;1;0;1})=0;SOR($1:$12);"");SOR(A1))
azaz az A1:A7 tartományban egy darab 1 érték; nulla darab 2 érték, 0 darab 3 érték van stb.
3. lépés
A HA függvény logikai értékelésének eredménye IGAZ, ha a tömbben a szám = 0; HAMIS, ha nem:
=KICSI(HA({HAMIS;IGAZ;IGAZ;IGAZ;HAMIS;IGAZ;HAMIS;HAMIS;IGAZ;HAMIS;IGAZ;HAMIS};{1;2;3;4;5;6;7;8;9;10;11;12};"");SOR(A1))
4. lépés
A HA függvény érték_ha_igaz és érték_ha_hamis argumentuma alapján
=KICSI({"";2;3;4; "";6;"";"";9; "";11;""};SOR(A1))
Az eredmény a hiányzó számok tömbje.
5. lépés
Az utolsó SOR függvény az 1 értéket vizsgálja:
=KICSI({"";2;3;4; "";6;"";"";9; "";11;""};1)
6. lépés
A KICSI megtalálja az első legkisebb értéket a tömbben:
=2
Megjegyzések
A SOR leegyszerűsíti a dolgokat, sokkal egyszerűbb beírni, hogy SOR($1:$12) , mint megadni a tömböt így: {1;2;3;4;5;6;7;8;9;10;11;12}.
A képlet nem veszi számításba az ismétlődéseket, mivel a DARABTELI "jóvoltából" csak a zérókat, azaz a hiányzókat keresi a tartományban.
Mivel a képletben a SOR függvény szerepel, a tartomány méretét a sorok száma határozza meg, így Excel 2003: 1 és 65 536 között, Excel 2007 és felette 1 és 1 048 576 között.
A képlet csak zérónál nagyobb pozitív egész számokkal működik.
A #SZÁM! hibaérték megjelenítését egyszerűen megszüntethetjük, ha a HAHIBA függvényt beillesztjük a képletbe.
A példában a tartomány az A1:A7. Mi a helyzet, ha a hiányzó elemeket tartalmazó tartomány máshol van?
Értelemszerűen a tartomány kijelölése a valós tartomány.
Mivel a SOR függvényt alkalmazzuk, az első két esetben (a relatív hivatkozású sorok), mindig az első sortól kezdődnek (SOR($1?)), míg az utolsó a tartomány első cellája.
Így pl. az előző példa a B2:B8 tartományban:
{=KICSI(HA(DARABTELI($B$2:$B$8;SOR($1:$12))=0;SOR($1:$12);"");SOR(B2))}

Összefüggő dátumtartományok összegzése
2014. december 8.

Verzió: 2007, 2010, 2013

Tételezzük fel, hogy egy hosszabb listában dátumok sorrendjében beírt adataink vannak. A dátumok lehetnek napok, hetek, hónapok; a példában az egyszerűség kedvéért számokkal jelöljük őket. Azt szeretnénk, ha tetszés szerint kijelölt, összefüggő időszak adatait összegezhetnénk.

A hetek száma az A oszlopban, a bevételek a B oszlopban találhatók. Az E5 cellába írtuk be a kezdő dátumot (itt a hetek számát), az E6-ba pedig az utolsót. Helytakarékosság miatt a 20. és a 28. közötti sorokat elrejtettem.
A megoldásra három lehetőség is kínálkozik!

1. SZUMHATÖBB
Egy adott tartomány több feltételnek is eleget tevő celláit adja meg.
Szintaxis: =SZUMHATÖBB(összegtartomány; feltételtartomány1; feltétel1; [feltételtartomány2; feltétel2]; …)
B3:B29 rész: ez az a cellatartomány, melyben az értékesítés adatai szerepelnek. Ezeket kell összeadni a következő feltételek alapján.
1. feltétel: A3:A29 >= E5: E feltétel alapján a SZUMHATÖBB ellenőrzi, hogy a hetek száma az E5 cellában megadottnál nagyobb legyen.
2. feltétel: A3:A29 <= E6: E feltétel alapján a SZUMHATÖBB ellenőrzi, hogy a hetek száma az E5 cellában megadottnál kisebb legyen.
Együtt: a SZUMHATÖBB mindkét feltétel alapján összeadja az értékeket, azaz a kezdő hét (E5) és az utolsó hét (E6) közötti összegeket (B7:B18).
2. A következő megoldás a már többször tárgyalt SZORZATÖSSZEG segítségével történik.
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 (az A oszlopban a hetek száma, ha az nagyobb az E5 cellában megadott számnál, és kisebb az E6-nál) megszorozzuk a megfelelő (azonos sorban lévő) második oszlop (a B oszlopban lévő bevételek) értékével. Az összes érték összege adja az eredményt.
[!Az egyenlő és nagyobb, vagy egyenlő és kisebb meghatározás csak a stílus miatt nem szerepel, de az operátorok érthetővé teszik a megoldást!]
Azaz {0,0,0,0,1,1,1,1,1,1,1,1,1,1,1,1;0,0,0,0,0,0,0,0,0,0,0} * {58900,391854,97,626,238773,379456 stb.}
Így csak az 5 vagy annál nagyobb és a 16 vagy annál kisebb értékek kapnak "1" értéket az első tömbben
3. SZUM + ELTOLÁS
A "legszofisztikáltabb" megoldás a SZUM és az ELTOLÁS kombinációja.
Az 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.
Megadhatjuk a sorok és az oszlopok számát.
Szintaxis: =ELTOLÁS(hivatkozás;sorok;oszlopok;magasság;szélesség)
A hivatkozás az a hivatkozás, amelyhez képest az eredmény hivatkozás helyzetét az argumentumok meghatározzák.
A példában ez a B3 cella, az első értékesítési adat helye.
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.
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.
Magasság: az eredmény hivatkozás magassága a sorok számában mérve.
Szélesség: az eredmény hivatkozás szélessége az oszlopok számában mérve.
Az ELTOLÁS (korábban OFSZET) 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.
Az offset (ang.) egyik jelentése "eltol", az "arrébb tol" értelmében.
De vissza a példához!
A hivatkozás helye a B3, innen alkalmazzuk a függvényt.
A sor argumentum értéke az E5 cella értéke mínusz 1, azaz 4, tehát a visszakapott tartomány 4 sorral lejjebb kezdődik.
Elhagyjuk az oszlop argumentumot, hiszen ugyanabban az oszlopban maradtunk, viszont megadjuk a magasság argumentumot, ez SZUM(E6-E5+1) = 12.
Tehát a tartomány 12 cella magas (B7:B18).
A SZUM logikája ugyanez.
A képletet hivatkozások nélkül így is leírhattuk volna:
=SZUM(ELTOLÁS(B3;4;;12)) = 2 779 649.
Egy feladat, három megoldás! Ezért szeretem az Excelt.

Nyári időszámítás
2014. december 1.

Verzió: 2007, 2010, 2013

Nemrég (dehogynem!) véget ért a nyári időszámítás. Kérdés, mikor kezdődik jövőre?

Az biztos, hogy a nyári időszámítás az év márciusának utolsó vasárnapján kezdődik és októberének utolsó vasárnapján ér véget.
Így aztán a megfelelő dátum- és időfüggvényekkel könnyen kiszámíthatjuk a pontos időpontot.
A nyári időszámítás kezdetének sablonja:
=DÁTUM(<év>;3;31)-(HÉT.NAPJA(DÁTUM(<év>;3;31))-1)
és a vége:
=DÁTUM(<év>;10;31)-(HÉT.NAPJA(DÁTUM(<év>;10;31))-1)
Ha az "<év>" elemet egy konkrét évszámmal (pl. 2015), vagy egy évszámot tartalmazó cellahivatkozással helyettesítjük, megkapjuk a konkrét dátumot.
Így 2015-ben:

Mivel az Excelben a dátumok számokként jelennek meg, a DÁTUM a megfelelő értéket adja. Ez a 42029 megformázva 2015. 3. 29., vasárnap (B3).
A képletekről: minden esetben a hónap utolsó napját vesszük figyelembe, mind március, (hónap "3"), mind október (hónap "10") 31 napos.
Meg kell határozni, melyik napra esik a hónap utolsó napja. Erre használjuk a HÉT.NAPJA függvényt.
A HÉT.NAPJA függvény a dátumértéknek megfelelő napot adja eredményül. A visszaadott érték 1 és 7 közötti egész szám.
A függvényben a Vasárnap értéke 1, a Hétfőé 2 stb.
Például a mai dátum (2014. december 1.) így néz ki:
=HÉT.NAPJA(DÁTUM(2014;12;1);1) = 2, azaz hétfő.
A nyári időszámítás kezdete kiszámításakor a dátumértékét ("1"-re redukálva) kivonjuk a dátumértékből.
Például, ha március 31. vasárnapra esne, levonjuk a hét napjának az értékét ("1"), így 1 mínusz 1 (=0) a dátumértékből, ezért a nyári időszámítás pontosan március 31-én kezdődne.
Ha március 31. péntekre esne, a számítás: 6 - 1 (=5), ezt vonjuk ki az utolsó értékből (31 - 5), és a nyári időszámítás 26-án kezdődne.
A képlet alapján először egy számot kapunk, de a Cellaformázás? során megjelenő Cellák formázása párbeszédablakban választhatunk a Dátum vagy az Egyéni megoldások közül. A B6 cellában ez látható.

Számtani sorozat
2014. november 24.

Verzió: 2007, 2010, 2013

Szeretem az olyan matematikai "dolgokat", melyekhez nem értek, de az Excel megoldja. Így ugrott be a számtani sorozat (az összegzési képlet) sztorija.

Carl Friedrich Gauss (Braunschweig, 1777. április 30. - Göttingen, 1855. február 23.) német matematikus, természettudós és csillagász, a "matematika fejedelme".
A legenda szerint tehetsége már hároméves korában megmutatkozott, amikor fejben kijavított egy összeadási hibát, melyet apja akkor vétett, amikor papíron számolta a pénzügyeiket.
Egy másik híres történet szerint az általános iskolai tanára diákjait azzal akarta lefoglalni, hogy 1-től 100-ig adják össze az egész számokat. Gondolta, egy darabig elvannak az összeadásokkal. A fiatal Gauss másodpercek alatt előrukkolt a helyes megoldással: a számsor alá visszafele leírta a számokat, majd az oszlopokat összeadta, így azonos összegeket kapott:

Ez 50 számpár, eredménye 101, azaz 50*101 = 5050.
Persze elég volt az első két oszlop, és már tudta az eredményt.
Emlékszünk: ez a számtani sorozat.
Nézzük, hogyan megy ez Excelben?
Ha a végső értéket (legyen nálunk is 100), beírjuk a cellába, a megoldás:

Mert 100*101 = 10 100, ez osztva 2-vel = 5050.
Ha azt szeretnénk, hogy ne az 1 legyen a kezdő érték, mit Gauss tanára esetében, hanem egy tetszőleges szám (mondjuk 5), írjuk a kezdő értéket a B1, az utolsó értéket (mondjuk 12) pedig a B2 cellába.
Az eredmény: 5 + 6 + 7 + 8 + 9 + 10 + 11 +12 = 68.

Miről van szó? 5 + 12 = 17.
12 - 5 + 1 = 8,
17 * 8 = 136,
136/2 = 68.
Hogyan "áll ez össze"?
Ha visszatérünk Gauss megoldásához, ezt látjuk:

Az első sorban az elemek száma látható.
A =(B2+B3)*(B3-B2+1)/2 képletben az első művelet a kezdő és az utolsó érték összege (ez állandó), a második zárójelben lévő művelet az elemek számát adja meg (8).
("Köszönet" a Wikipédia Gauss és Számtani sorozat cikkeiért, az utóbbi magas nekem.)

Összegzés hónapok alapján
2014. november 17.

Verzió: 2007, 2010, 2013

Az új összefüggések megvilágítására az adatokat át kell rendezni, és ez sokszor fejtörést okozhat. A példában egy hosszabb adatsor hónapok szerinti rendezésének egy lehetősége látható.

Sokszor vannak olyan adataink, melyek esetében olykor nehéz meglátni a lényeget (a "big picture-t", ahogy az angol mondja).
Például van egy listánk napi dátumokkal és értékesítési adatokkal. Tételezzük fel, hogy egy másik elemzés számára a havi összesítésekre van szükség.
Bizony nem könnyű, még ha tudjuk is, hogy az első 20 sor a januári, a következő 18 sor a februári (majd így tovább) adatokat tartalmazza.
Még bonyolultabb, ha az adatok nincsenek sorba rendezve.
Nézzünk egy egyszerű példát, három hónap, összesen hét adat. (Persze, mint jeleztem, lehet 1800 sor is.)

Két képletet használunk a megoldáshoz: SZORZATÖSSZEG és HÓNAP.
A SZORZATÖSSZEG-et nem kell bemutatni, legutóbb, sőt előtte is foglalkoztam a függvénnyel.
A HÓNAP() a dátumérték argumentumnak megfelelő hónap értéket adja eredményül (1 = január, - 12 = december).
A hónap értékét az 1...12 tartományba eső egész értékként kapjuk (1 = január, ..., 12 = december).
Szintaxis
=HÓNAP(dátumérték)
Dátumok bevitele a DÁTUM függvénnyel, illetve más képletek vagy függvények eredményeként lehetséges.
A 2014. október 23. dátum beírására használjuk pl. a DÁTUM(2014;10;23) függvényt.
A =HÓNAP(2014;10;23) eredménye 10, mert a dátum októberben van.
Mint ismeretes, a SZORZATÖSSZEG egy adott tartomány értékeit összegzi.
Ha kombináljuk a két függvényt, megkaphatjuk a havi értékesítések összegét.

A D2 cellától kezdve írjuk be a hónapok számát (1, 2, 3), ezek a kritériumok, és cellahivatkozással [=] építjük be a képletbe, és az E2 cellába írjuk be a képletet:
=SZORZATÖSSZEG((HÓNAP($A$2:$A$8)=D2)*($B$2:$B$8))
Látható, hogy a dátumokat és az összegeket abszolút hivatkozással vittük be, így a képlet egyszerűen "lehúzható".
A 10. sorban az ellenőrzés látható.
Megjegyzés: ha úgy adódik, a HÓNAP függvény helyettesíthető az ÉV függvénnyel, csak akkor a D2:D4 cellákba a 2014, 2015, 2016 számokat kell írni.

Mennyi az annyi? (2. rész)
2014. november 10.

Verzió: 2007, 2010, 2013

A legutóbbi bejegyzésben a szorzások világában jártam, és a SZORZATÖSSZEG() bemutatásáig jutottam el.

Emlékeztetőül: a függvény 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.
Nem mondok nagy újságot, ha azt mondom, az Excel sorokból és oszlopokból áll, így a tömbök is lehetnek vagy csak vízszintesek, vagy csak függőlegesek, vagy vegyesek.

Az ábrán a 3x1 (B2:B4) és az 1x3 (C6:E6) méretű tömb szorzata végeredményben egy 3x3 méretű tömb (C8:E10).
Látható, hogy csak egyetlen eredmény van (1x1).
Tömb1 * Tömb2 = (B2:B4) * (C6:E6)
(0, 0, 1) * (0, 1, 0)
(0, 0, 1) * (0, 1, 0) = ({0,0,0},{0,0,0},{0,1,0})
A 0-val való szorzás egyedüli kivétele a tömb1 3. és a tömb2 2. elemével való szorzás.
Az előző rész példája alapján nézzük a következőt. Még egy oszlopunk van, és látható, hogy az ábécé nem szabályos, hanem A, B, A, D.

Csak azokat a cellákat akarjuk összeszorozni, melyek az első oszlopban az "A" betűt tartalmazzák.
Tehát a képlet:
=SZORZATÖSSZEG(B2:B5;C2:C5;(A2:A5="A")*1)
Az első két tömb érthető. Vizsgáljuk meg a harmadikat:
(A2:A5="A")*1
Az A2:A5 szintén tartomány (tömb3), melyhez egyenlőségjellel hozzáfűzzük, hogy az "A" betűvel legyen egyenlő.
Másként kifejezve, olyan feladatot adunk meg, melynek eredménye vagy IGAZ vagy HAMIS.
Mivel az Excel cellánként végzi a műveletet, az eredmény ilyen:
A2:A5=(A,B,A,D)
A2:A5=(IGAZ,HAMIS,IGAZ,HAMIS)
Mivel a logikai értékeket (IGAZ, HAMIS) számokként is kifejezhetjük, az eredmény így is leírható:
A2:A5=(1,0,1,0).
Ezt szeretnénk megkapni, de még mindig csak a logikai értékeknél tartunk. Ha a IGAZ, HAMIS megszorozzuk 1-gyel, az eredmény szám lesz ( az IGAZ 1, a HAMIS pedig 0.
Ha papírra vetnénk az egészet, ilyen lenne:
Tömb1 * Tömb2 * Tömb3 = (B2:B5) * (C2:C5) * ((A2:A5="A")*1) = 200
(10, 20, 30, 40) * (2, 4, 6, 8) * ((IGAZ, HAMIS, IGAZ, HAMIS)*1) = 200
(10 * 2 * 1) + (20 * 4 * 0) + (30 * 6 * 1) + (40 * 8 * 0) = 200
Mire használhatjuk ezt a függvény?
Az előző részben már látható volt a legkézenfekvőbb megoldás, egy művelettel kevesebbet kell elvégezni. mos néhány egyszerű dolog.
Cellák megszámlálása a SZORZATÖSSZEG() segítségével
A B2:C5 tartományban keressük a >15 és <20 közötti számokat.

A függvény segítségével megszámlálhatjuk kritériumainknak megfelelő cellák számát.
A logika:
=SZORZATÖSSZEG([kritérium1]*[kritérium2]* [tömb])
Adatok összegzése több feltétel alapján
Mivel ez nem szabályos SZORZATÖSSZEG képlet, kézzel vittük be a C8 cellába.

Azoknak a számoknak az összegére vagyunk kíváncsiak, melyek >25 és <75, azaz a 25 és 75 közöttieket.
Ezek: 40, 45, 50, 55 és 60. Összegük. 250.
Látható, hogy a kritérium most nem szöveg, operátorokkal határoztuk meg.
A SZORZATÖSSZEG() lehetőségei
A SZORZATÖSSZEG függvény alkalmazása különösen hasznos olyan esetekben, melyekben ugyanazokkal a cellákkal kell több szorzást, majd összeadást végezni.
Ilyen esetek lehetnek:
súlyozott átlag kiszámítása
két vagy több adatsor összegének kiszámítása
több kritériumon alapuló számítások.
Súlyozott átlag kiszámítása
A példában öt résztvevő öt feladatban elért eredményei láthatók.
A feladatok súlyozása (1, 1, 2, 2, 3) eltérő.

A =SZORZATÖSSZEG($C$4:$G$4;C8:G8) képletben az első tömb tartalmazza a súlyozás faktorait, melyek minden eredményre vonatkoznak (ezért az abszolút hivatkozás), míg a második tömb (az ábrán az 5. résztvevő (C8:G8) eredményeit tartalmazza.
SZORZATÖSSZEG() két kritérium
Négy régióban négy ügynök értékesít termékeket partnereknek.

Arra vagyunk kíváncsiak, hogy pl. Észak régióban a Termék_1 milyen forgalmat ért el.
A két kritériumot külön cellákba írtuk (B22 és B23), így cellahivatkozásként építhetők a képletbe.
Ahogy látható, a képlet először a B3:B20 tartományban keresi az "Észak" kifejezést (a B22 cellában megadott érték alapján),
majd ugyanígy a B23 cellában megadott "T_1" kifejezést keresi az E3:E20 tartományban,
majd a megfelelő értékeket (F3, F6) összeadja.
Ezzel a megoldással hatékony, dinamikus beszámolók készíthetők, hiszen a kritériumok kiválasztása egyszerű (pl. Érvényesítés).

Mennyi az annyi?
2014. november 3.

Verzió: 2007, 2010, 2013

Egyre gyakrabban kerül fel a Facebookra a következő feladvány: 5 + 5 * 5 + 5. Mennyi az eredmény? 55, 35 vagy 100? És meglepő a rossz válaszok (leginkább az 55) nagy száma. Nézzünk körül a szorzások világában.

Az Excelben nincs "szorzófüggvény", de többféle módon lehet szorozni. Szorzásra használható a csillag (*), de mi van, ha más műveleti jeleket is kell alkalmazni?
Ez olykor kemény diónak bizonyulhat. Íme, a lehetőségek.
1. Szorzás a szorzás jelével (*)
Két számot a "*" szoroz össze: háromszor öt, például, beírva = 3*5. Ugyanígy szorozható két (vagy több) szám két (vagy több) cellában: =A1*A2.
A * műveleti jelet más operátorokkal együtt is lehet használni, így összeadást (+), kivonást (-), osztást (/), a hatványozás jelét (^).
Az Excel a műveleteket az angolszász rövidítéssel "PEMDAS" mnemotechnikai rövidítés alapján végzi:
Parentheses, Exponents, Multiplication, Division, Addition, Subtraction -- magyarul: Zárójel, Kitevő, Szorzás, Osztás, Összeadás, Kivonás.
A rövidítés mondatban: "Please Excuse My Dear Aunt Sally".
A Facebookon terjedő példával, az =5 + 5 * 5 + 5 képletben az Excel először a szorzást (5*5) végzi el, majd az összeadásokat, így az eredmény 35.

2. Szorzás a SZORZAT() függvénnyel
Ha több számot kell összeszorozni, alkalmazható a SZORZAT() függvény, mely összeszorozza az argumentumként megadott összes számot, és eredménye a szorzat. A számokat (argumentumokat) pontosvesszővel választjuk el egymástól.
Így pl. a =SZORZAT(2;3;4;5) eredménye 120. (Szorzásjellel is elvégezhető.)
A =SZORZAT(2;4,5+1,5;4) képlet eredménye 48, megegyezik a 2*(4,5+1,5)*4 formula eredményével.
A SZORZAT függvény számokat, számokból álló tömböket, cellákat, cellatartományokat vagy ezek kombinációit kezeli.
Számolást csak számokkal, logikai értékekkel vagy szöveg formájú számokkal végez a program.
Ha az argumentum tömb vagy hivatkozás, akkor a tömbből vagy hivatkozásból csak a számokat szorozza össze.
Az üres cellákat, logikai értékeket, szövegeket és a hibaértékeket nem veszi figyelembe.

Ha a tartomány számokat tartalmaz, a kettőspontot alkalmazzuk a tartomány kijelölésére (B3), ez végeredményben a =B2*B3*B4*B5*B6.
Ugyanígy a B4 cellában a =SZORZAT(B2:B4;B5:B6) egyenlő (B2*B3*B4)*(B5*B6).
A számokat tömbállandókként is megfogalmazhatjuk (B6), ilyenkor a kapcsos zárójelet kézzel kell beírni.
Milyen értékeket kezel a SZORZAT függvény?
A számokat és a dátumokat a SZORZAT függvény mindig számokként kezeli.
Ugyanakkor a szöveges és logikai értékek kezelése eltérő, attól függ, hogy értékként tartalmazzák a cellák, vagy közvetlenül adjuk a függvényhez.
A táblázat összefoglalja, melyik értékeket veszi figyelembe az Excel a SZORZAT függvény alkalmazása során, ill. melyeket nem, vagy melyek eredményeznek hibaértéket.

Fontos:
Az Excel a SZORZAT függvény esetében az üres cellát úgy kezeli, mintha az értéke 1 lenne és nem 0 (zéró), mint azt sokan szeretnék (és talán logikusabb lenne?).
Így a C3 cellában a SZORZAT(A3;B3) eredménye 10.
Ha a B2 cella értéke 0, a =SZORZAT(A2;B2) eredménye 0: (10*0 = 0).
Ha azt akarjuk, hogy a függvény az üres cellát zérónak tekintse, a Microsoft javaslata alapján minden argumentumot szorozzunk meg 1-gyel (*1).
Így a C4 cella eredménye 10 helyett 0.
Arra nincs megoldás, ha üres cella van a tartományban.
Meg kell győződni arról, hogy a tartományban nincs üres cella.

3. Tartományok szorzata a SZORZATÖSSZEG() függvénnyel
A következő ábra egy képzelt számla részlete.

Az F oszlopban soronként összeszoroztuk a D oszlop mennyiségeit az E oszlopban lévő egységárakkal, így kaptuk meg a fizetendő összegeket, majd a végösszeget (F8) az egyes cellák összeadásával kaptuk meg.
Nem lehetne ezt egyszerűbben, egy lépésben megoldani?
De igen. Erre szolgál a SZORZATÖSSZEG() függvény, mely megadott tömbök megfelelő elemeit szorozza össze, majd kiszámolja a szorzatok összegét.
Az első tömb (oszlop) értékeit (itt a D oszlopban lévő darabszám) megszorozzuk a megfelelő (azonos sorban lévő) második oszlop (itt az E oszlopban lévő egységárak) értékével. Az összes érték összege adja az eredményt.

A =SZORZATÖSSZEG(D2:D6;E2:E6) összeszorozza az azonos sorban lévő cellák értékeit (D2*E2, D3*E3, D4*E4 stb.), és kiszámítja a szorzatok összegét.
Látható, hogy az eredmény ugyanaz.
Hogy is van ez?

Ha kijelöljük a C1:C4 tömböt, melynek elemei (10, 20, 30, 40), és beírjuk a SZORZATÖSSZEG(C1:C4) képletet, a "100" eredményt kapjuk.
De ugyanezt kapjuk a SZUM függvénnyel is, mondhatnánk. Így igaz. Ha csak egy tömbünk van, a SZORZATÖSSZEG függvény nem ad semmi pluszt.
Viszont a háttérben történik valami, mely nem észrevehető, ha egy tömbbel van dolgunk. Összeszorozza az adott tömböket:
ha több van, egymással (mint a "számlában"),
vagy ha csak egy tömbről van szó, 1-gyel.
Ezt így képzelhetjük el a példában:
(10*1) + (20*1) + (30*1) + (40*1) = 100.
Azaz a számlában:
Tömb1 * Tömb2 = (D2:D6) * (E2:E6) = (3, 7, 5, 2, 1) * (100, 110, 120, 130, 140) = 2070.
A szorzás mindig soronként történik, tehát a Tömb1 első értékét szorozza a Tömb2 első értékével, majd így tovább.
Tömb1 * Tömb2 = (3*100) + (7*110) + (5*120) + (2*130) + (1*140) = 2070.
Itt kell megemlíteni, hogy a tömböknek azonos méretűeknek kell lenniük.
Tehát ez nem működik: =SZORZATÖSSZEG(D2:D6;E2:E4).
A Tömb1 5 értéket, a Tömb2 3 értéket tartalmaz, az eredmény #ÉRTÉK! hibaérték lesz.
A SZORZATÖSSZEG függvény még nagyon sok mindent "tud". Erről legközelebb.

Dinamikus SZUM()
2014. október 27.

Verzió: 2007, 2010

Ha egy folyamatosan bővülő cellatartomány aktuális összegét szeretnénk megkapni, és NEM EXCEL 2013 van a gépünkön, a legegyszerűbbnek tűnő megoldás egy segédoszlop alkalmazása. Van azonban egy még ennél egyszerűbb megoldás is. Dinamikus képlettel számítjuk ki az összeget -- az értékek helye (pozíciója) alapján.

Tételezzük fel, hogy az ábra egy nagyobb táblázat része (dátumok, árucikkek vagy nevek és értékek sorozata. Itt most csak a folyamatosan bővülő értékek oszlopát látjuk.

Mi a trükk?
A SZUM képlet második részében a cellahivatkozást egy INDEX képlettel kell megoldani.
A =SZUM(B$2:INDEX(B:B;SOR()) képlet a színes háttérrel jelölt tartomány értékeit adja össze.
Ez a képlet tetszés szerint másolható és beilleszthető egy oszlop (itt a C) bármelyik cellájába, és mindig az éppen aktuális celláig számítja ki az összeget.
Hogyan működik?
A SZUM() a cellahivatkozásként megadott tartományt tömbként (mátrixként) kezeli, majd ez után adja össze az értékeket.
Ezt kell tudni, hogy az összegzést befolyásolhassuk, azaz dinamikus summázást végezzünk.
Ahogy már többször ismertettem, az INDEX() táblázatban vagy tartományban található érték hivatkozását vagy értékét adja vissza.
A függvénynek két alakja van:
a hivatkozásos forma, amely adott cellák hivatkozását adja eredményül, és
a tömbös forma, amely adott cella vagy cellatömb értékét adja eredményül.
Nézzünk egy egyszerű példást:

A függvény szintaxisa: =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 az INDEX a megadott tömb (B1:B5) 5. sorának értékét adja meg (500).
(A függvény argumentumai közül elhagytuk az oszlop_szám argumentumot, hiszen csak egy oszloppal van dolgunk.)
Tehát, ha a SZUM második részében az INDEX függvényt alkalmazzuk, és tömbként a megfelelő tartományt (oszlopot) adjuk meg.
A "baj" a sor_szám argumentummal van, hiszen a tartomány bővülésekor mindig újra meg kellene adni.
És itt jön a lényeg: a SOR().
A függvény egy hivatkozás sorának számát adja meg.

Az ábrán a képlet a B9 cellában: SOR(), az eredmény 9.
Itt elhagytuk a hivatkozás argumentumot, így eredményeként a cellák sorának számát kapjuk (9).

Látható hogy a B11 cellában a hivatkozás B9, és az eredmény is 9.
Ha elhagyjuk a cellahivatkozást, megkapjuk a dinamikus képletet.
(Mint jeleztem, ez az Excel 2013 előtti változatokban megoldás, az Excel 2013-ban készen kapjuk: Gyorselemzés > Összegek > Göngyölített összeg.)

Tömbök darabszáma
2014. október 20.

Verzió: 2007, 2010, 2013

Ha egy sorban egymást váltogatva jelennek meg értékek, hasznos lehet, ha tudjuk, hogyan képeznek csoportokat ezek az elemek.

Van egy munkalapunk, melyen a sorokban az "1" és "2" értékek egymást követik, csoportokat képezve. Tételezzük fel, hogy ezek a számok "kódolnak" valamit (pl. egymás után következő, két típusba sorolható események), és arra vagyunk kíváncsiak, hogy egy adott sorban (napon, héten stb.) hány ilyen csoport fordult elő. Ha a csoportot tömbnek hívjuk, már közelebb jutunk a megoldáshoz.

A képen az első sorban 1 tömb "1" (B2:E2) és 1 tömb "2" (F2:G2) látható, a második sorban 2 tömb "1" (C3 és F3), valamint 3 tömb "2" (B3, D3:E3 és G3), és így tovább.
Ha megszámoljuk ezeket a tömböket, az eredmény: 12 darab "1" tömb és 14 darab "2" tömb.
És ha egy sorban 100 vagy 1000 szám van? A számolgatás nem megoldás.
Az alkalmazott képlet az I9 cellában (itt keressük az "1" tömböket):
{=SZUM((B2:G8<>A2:F8)*(B2:G8=1))}
A J9 cellában (itt keressük a "2" tömböket):
{=SZUM((B2:G8<>A2:F8)*(B2:G8=2))}
A kapcsos zárójelekből látható, hogy itt tömbképletek szerepelnek, már többször írtam róluk, a leglényegesebb itt található.
Hogyan működnek itt?
1. lépés: összehasonlítjuk a B2:G8 tartományt az A2:F8 tartománnyal
A B2:G8<>A2:F8 lényege:
{1, 1, 1, 1, 2, 2;2, 1, 2, 2, 1, 2;2, 1, 2, 2, 2, 1;2, 1, 1, 2, 1, 1;2, 2, 2, 1, 2, 1;2, 1, 2, 2, 1, 2;1, 1, 1, 1, 1, 2} <> {0, 1, 1, 1, 1, 2;0, 2, 1, 2, 2, 1;0, 2, 1, 2, 2, 2;0, 2, 1, 1, 2, 1;0, 2, 2, 2, 1, 2;0, 2, 1, 2, 2, 1;0, 1, 1, 1, 1, 1}
eredménye pedig
{IGAZ, HAMIS, HAMIS, HAMIS, IGAZ, HAMIS;IGAZ, IGAZ, IGAZ, HAMIS, IGAZ, IGAZ;IGAZ, IGAZ, IGAZ, HAMIS, HAMIS, IGAZ;IGAZ, IGAZ, HAMIS, IGAZ, IGAZ, HAMIS;IGAZ, HAMIS, HAMIS, IGAZ, IGAZ, IGAZ;IGAZ, IGAZ, IGAZ, HAMIS, IGAZ, IGAZ;IGAZ, HAMIS, HAMIS, HAMIS, HAMIS, IGAZ}
[Csak könnyítésként: a lényeg az A oszlop! Ha összehasonlítunk egy cellát egy üres cellával, az eredmény 0, azaz HAMIS. Így az első sor: 1<>0: IGAZ; 1<>1: HAMIS; 1<>1: HAMIS; 1<>1: HAMIS; 2<>1 IGAZ; 2<>2: HAMIS].
2. lépés: csak az "1" értékek száma
(B2:G8<>A2:F8)*(B2:G8=1)
"Excel nyelven":
{IGAZ, HAMIS, HAMIS, HAMIS, IGAZ, HAMIS;IGAZ, IGAZ, IGAZ, HAMIS, IGAZ, IGAZ;IGAZ, IGAZ, IGAZ, HAMIS, HAMIS, IGAZ;IGAZ, IGAZ, HAMIS, IGAZ, IGAZ, HAMIS;IGAZ, HAMIS, HAMIS, IGAZ, IGAZ, IGAZ;IGAZ, IGAZ, IGAZ, HAMIS, IGAZ, IGAZ;IGAZ, HAMIS, HAMIS, HAMIS, HAMIS, IGAZ}*(B2:G8=1)
"Excel nyelven":
{IGAZ, HAMIS, HAMIS, HAMIS, IGAZ, HAMIS;IGAZ, IGAZ, IGAZ, HAMIS, IGAZ, IGAZ;IGAZ, IGAZ, IGAZ, HAMIS, HAMIS, IGAZ;IGAZ, IGAZ, HAMIS, IGAZ, IGAZ, HAMIS;IGAZ, HAMIS, HAMIS, IGAZ, IGAZ, IGAZ;IGAZ, IGAZ, IGAZ, HAMIS, IGAZ, IGAZ;IGAZ, HAMIS, HAMIS, HAMIS, HAMIS, IGAZ}*{IGAZ, IGAZ, IGAZ, IGAZ, HAMIS, HAMIS;HAMIS, IGAZ, HAMIS, HAMIS, IGAZ, HAMIS;HAMIS, IGAZ, HAMIS, HAMIS, HAMIS, IGAZ;HAMIS, IGAZ, IGAZ, HAMIS, IGAZ, IGAZ;HAMIS, HAMIS, HAMIS, IGAZ, HAMIS, IGAZ;HAMIS, IGAZ, HAMIS, HAMIS, IGAZ, HAMIS;IGAZ, IGAZ, IGAZ, IGAZ, IGAZ, HAMIS}
És az eredmény:
{1, 0, 0, 0, 0, 0;0, 1, 0, 0, 1, 0;0, 1, 0, 0, 0, 1;0, 1, 0, 0, 1, 0;0, 0, 0, 1, 0, 1;0, 1, 0, 0, 1, 0;1, 0, 0, 0, 0, 0}
3. lépés: összeadjuk az értékeket
=SZUM((B2:G8<>A2:F8)*(B2:G8=1))
Ez így néz ki:
SZUM({1, 0, 0, 0, 0, 0;0, 1, 0, 0, 1, 0;0, 1, 0, 0, 0, 1;0, 1, 0, 0, 1, 0;0, 0, 0, 1, 0, 1;0, 1, 0, 0, 1, 0;1, 0, 0, 0, 0, 0})
Így az eredmény az I10 cellában: 12, azaz 12 tömb "1".
A J10 cellában hasonló a megoldás.


Az INDEX/HOL.VAN kombináció
2014. október 13.

Verzió: 2007, 2010, 2013

Az FKERES sokak szemében már kissé elavult, főleg mióta olyan jobb választások állnak rendelkezésünkre, mint pl. az INDEX/HOL.VAN kombináció.

A függvény 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."
És mi van akkor, ha egy teljes sorban kell keresnünk, és a sor minden oszlopának (cellájának) az értékét kell megkapnunk (a "szokásos" egyetlen érték helyett)?
A példában a választott személy legjobb eredményére vagyunk kíváncsiak, tehát tényleg az összes oszlopban kell keresnünk.

Hogyan működik?
A képlet lényege az INDEX/HOL.VAN kombináció.
Most, hogy megjelent az FKERES e-book és munkafüzet, érdemes belemélyedni, hogyan lehet a legnépszerűbb függvény, az FKERES "gyengéit" kiküszöbölni.
Az INDEX/HOL.VAN kombináció is függőleges keresésre szolgál, amit az FKERES függvény végez.
INDEX($B$3:$F$10;HOL.VAN(B13;$B$3:$B$10;0);0);1)
Az INDEX képlet (röviden)
Táblázatban vagy tartományban található érték hivatkozását vagy értékét adja vissza.
A függvénynek két alakja van:
a tömbös forma, amely adott cella vagy cellatömb értékét adja eredményül és
a hivatkozásos forma, amely adott cellák hivatkozását adja eredményül.
=INDEX(tömb;sor_szám;oszlop_szám)
A tömb: $B$3:$F$10
A sor_szám argumentum pedig a HOL.VAN(B13;$B$3:$B$10;0) függvény.
A HOL.VAN képlet
A függvény a keresési_érték tömbben elfoglalt relatív pozícióját adja vissza.
=HOL.VAN(keresési_érték;tábla;egyezés_típus)
A függvény alkalmazásához szükségünk van a keresési_értékre és a táblára.
(Az egyezés_típus paraméter "0" [azaz HAMIS] -- ezzel jelezve, hogy pontos értékre van szükségünk.)
A keresési_érték a B13 cella tartalma.
Az egyszerűség kedvéért a neveket az Adatok > Érvényesítés megoldással választjuk, melyben
az Érvényességi feltétel "Lista", forrása a $B$3:$B$10 tartomány (a nevek).
Az ábrában ez a kiválasztott név "Éva".
A tábla a tömb első oszlopa ($B$3:$B$10).
Így a =HOL.VAN(B13;B3:B10;0) eredménye 3 (mert "Éva" harmadik a listában).
Röviden összefoglalva:
=INDEX(tömb;HOL.VAN képlet)
Vissza az INDEX részhez!
Oszlop_szám: 0!
Ha az oszlop_szám argumentum "0", az oszlopokban lévő összes értéket kapjuk eredményként.
Így ha "Évát" választjuk a lenyílóban, mind a négy hozzá tartozó értéket {73000, 70000, 57000, 86000} megkapnánk.
Ezért a NAGY függvénnyel a legnagyobb értéket hívjuk elő.

Éves adatok negyedéves bontásban
2014. október 6.

Verzió: 2007, 2010, 2013

Ha van egy adatsorunk (időrendben), könnyen készíthető negyedéves összegzés, segítségével áttekinthetőbb lesz egy beszámoló, kimutatás.

Közeledik az év vége. Ideje az összegzéseknek. Van egy táblázatunk, mely 5 termék havi összesítését tartalmazza. Ezt szeretnénk negyedéves bontásban megkapni.
A B14:F14 cellák tartalmazzák az éves összesítéseket, természetesen a B20:F20 is, de már a negyedéves összesítések alapján.
Azt szeretnénk, ha a 16:19 sorokban az összesítések a B16 cellába történő beírás után egyszerűen másolhatók legyenek a többi cellába (F2 vagy dupla kattintás).

Az ELTOLÁS nagyon alkalmas megoldás, dinamikus tartományt hozunk létre.

Nézzük a B16 cellában a képletet:
=SZUM(ELTOLÁS(B$2;3*SOROK(B$2:B2)-3;;3))
Ha nem ismerjük az ELTOLÁS függvényt, röviden:
A függvény 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. Megadhatjuk a sorok és az oszlopok számát.
Szintaxis
=ELTOLÁS(hivatkozás;sorok;oszlopok;[magasság];[szélesség])
Áthúztam az oszlopok és a szélesség argumentumokat, nincs rá szükségünk, hiszen a hivatkozás 1 oszlop szélességű (itt a B).
A képletben a hivatkozás a táblázat első cellája, a B2.
A sorok argumentum az eredmény bal felső cellája és a hivatkozás közötti függőleges távolság (lefelé vagy fölfelé) a sorok számában kifejezve.
Mivel egyszerűen akarjuk a feladatot megoldani, tehát nem szeretnénk minden negyedre szerkesztgetni a képletet, a sorok argumentumot dinamikusan oldottuk meg: "bevettük" a képletbe a SOROK függvényt, ami nagyon egyszerűen a tartományban a sor számát adja eredményül.
Mivel a B2:B2 tartomány egy sorból áll, a
=SZUM(ELTOLÁS(B$2;3*1-3;;3)) eredmény 18; vagy egyszerűen
=SZUM(ELTOLÁS(B;2;0;;3)) .
És még valami: a csodafegyver az abszolút és a relatív hivatkozás alkalmazása.
A $! Erről itt írtam részletesen.
Így az ELTOLÁS magyarul:
"A B2 cellától elindulva ne menj lefelé (azaz maradj a B2-ben), majd adj egy 3 sor magasságú tartományt"; ez a
=SZUM(B2:B4)
Nézzük a 2. negyedév képletét a B17 cellában:
=SZUM(ELTOLÁS(B$2;3*SOROK(B$2:B3)-3;;3)) = 17.
Látható, hogy a SOROK függvényben a tömb (hivatkozás) most B2:B3, így a függvény két sort vizsgál, hiszen a B2:B3 tartomány két sorból áll. Így a:
=SZUM(ELTOLÁS(B$2;3*2-3;;3)) képlet
=SZUM(ELTOLÁS(B$2;3;;3)) (eredménye 17) lesz.
Magyarul:
A B2 cellától elindulva menj lefelé 3 sort (ez a B5), majd adj egy 3 sor magasságú tartományt; ez a =SZUM(B5:B7)
És vízszintes elrendezésben?
Nincs gond. A SOROK helyett az OSZLOPOK alkalmazásával, amely egy hivatkozásban vagy egy tömbben lévő oszlopok számát adja eredményül.

Az ábrán az 5. termék első negyedévi eredménye megoldásának a képlete szerepel:
=SZUM(ELTOLÁS($B6;;3*OSZLOPOK(B$2:$B6)-3;1;3))
A képlet logikája megegyezik a függőleges megoldás logikájával, csak most az OSZLOPOK függvénnyel.

Legújabb Excel tipp