2017. december
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.