Category archives for "excel"

Maintaining colors in your Google documents

Let’s pick a color:

Not very useful, because later on I will never know what particular shade of gray did I use in my document from this very picker. There had been a number of occasions where I’d try to guesswork the color I’d used, since I want my document to be consistent.

I like the rich palette in general, but what if it had a section that read: “Colors in your document” and it’d populate automatically. You can get to a similar behaviour by using the “Custom” colors for this, but it’s just exactly one step more than what most people want and you can’t set default colors as custom colors (more on this later). Most people want to 1) select some colors for their document 2) next time they’re there, see their used colors in the document.

How do I know this? Because of all the years I’d been using Google Docs, there was never a single occasion where I’d define a custom color or saw someone else do it. Never. Even though I’d spent countless of minutes wasted on trying to find the color I’d used for highlights, it has never occured to me to define colors for a document. For two reasons: first, I’m not exclusively using GDocs. I’m using different software and different software tend to have different solutions for this. Second, when you’re doing actual work, you just can’t be bothered with defining custom colors.

One more thing: if you define a custom color in GDocs, make sure you actually are using a different color from the base palette, because if you tried to set a custom color of the base palette it will not get defined. Good luck with finding your used gray.

Egyszer vagy többször fordul elő a cella egy tartományban?

Nemrég posztoltam már egy nagyon hasznos Excel függvényt, amivel meg tudjuk mondani, hogy egy tartományban bizonyos típusú elemből hány darab fordul elő. Ma szerintem egy ugyanilyen, ha nem ennél is hasznosabb, függvény kerül ismertetésre, ami megmondja, hogy egy tartomány egy cellája csak egyszer, vagy egynél többször fordul-e elő. Borzasztó praktikus például abban az esetben, ha meg akarjuk mondani, hogy vannak-e duplán szereplő elemeink egy listában.

Másolható formában: =COUNTIF($B$3:$B3;B3)>1

Értelemszerűen a legelső elemünk a B3 cellában van, viszont, és ez a megoldás szépsége, nincs megadva a tartomány vége, így mindig addig nézi, ameddig húzzuk a képletet. A COUNTIF megszámolja, hogy a tartományban az adott értékből talált-e egynél többet. A >1 feltétel azért szerepel a végén, mert ez egy 0/1 állapotú IF szerkezet végeredményt ad belőle vissza, praktikusan ugyanazt az eredményt adja, mintha kiírtuk volna az IF-et, csak így mindig két állapota lesz a végeredménynek: TRUE vagy FALSE. Az első “TRUE” érték jelzi a duplikátumot értelemszerűen.

Remek cucc, érdemes kiírni egy papírra és a monitor mellé ragasztani.

/via Chandoo

Hány darab egyedi cella van egy tartományban?

Excelben nagyon tipikus problematika az, amikor számszerűsítenünk kell, hogy hány darab különféle elemünk van egy tartományban. Például kapunk egy listát mindenféle cikkszámmal és abból meg kell mondanunk, hogy hány cikkszámunk van összesen. A végeredmény:

=SUMPRODUCT(1/COUNTIF(B2:B12;B2:B12))

A B2:B12 természetesen csak példa, cseréljük nyugodtan a saját tartományunkra. A működése baromi ötletes: a COUNTIF függvényt saját magára értelmezzük, ennek eredménye egy tömb lesz, aminek minden eleme pontosan azt a számot tartalmazza, ahány darab van az adott elemből. Például egy ALMA, ALMA, KÖRTE esetében {2, 2, 1} lesz a válasz. Ennek vesszük a reciprokát, akkor {1/2, 1/2, 1} tömböt kapunk, amit végül összeadunk és megkapjuk, hogy kettő. (Klassz, mi?)

A középiskolás matematika fakultációs órákat – és az ott bemutatott leleményes megoldásokat – juttatta eszembe, főleg a COUNTIF és a reciprok volt nagyon ötletes. Egyébként innen túrtam ki a fentieket, nem saját megoldás.

Excel 2013

A Microsoft bemutatta az új Office csomagjának következő, 2013-as verzióját. Az Ars Technicán mindent alaposan végigolvashatunk, én most csak a 2010-es és a 2013-as ribbont hasonlítottam össze:

Háááát… 🙂 Nem egyértelmű a dolog, főleg azért, mert a kollaborációs részek hiányoznak belőle, tehát Google Docs mintára nem tudunk közösen dolgozni rajta, illetve ez nem a csomag fő funkciója, bár cloud alapú mentések mennek majd az Office.com-ra, de ha más szerkeszti éppen, akkor mindig “lock”-olt lesz a munkafüzetünk. Ennek az Office cloud-nak majd részletesebben is utána kell nézni, szerintem a Google Docs alapú megosztás működhetne itt is a legjobban, én nagyon is el tudok képzelni egy olyan világot, ahol elkezdek a gépemen szerkeszteni egy táblázatot, aztán azt megosztom valakivel, a háttérben pedig mindez gyönyörűen működik is – lehet, hogy ilyet tudni is fog a suite.

A dizájn metróssága nekem nagyon furcsa. Én már kezdtem megkedvelni a 2010-es felületet, most meg jön ez a … nem is tudom, mi. Talán a “dizájnmentesség” azért van, hogy touch felületen jobban eltaláljuk a gombokat? Nem tudom, nekem nagyon nyers, nagyon öcsi, nagyon jövő még ez a vizuális szerkezet, egyelőre még zabolátlannak látom, sokkal jobban tetszik az Apple túlrajzolt világa és szkeuomorfizmusa.

Egy fontos újítás lesz, ami a profibb Exceleseknek nem szolgál különösebb újítással, viszont a kézzel gépelőknek mindenképpen okos: elemzi, ha adatokat írunk be és megpróbál mintákat felismerni, aztán ez alapján kitalálni, hogy akarjuk továbbírni a dolgot. Az Arson hozott példa alapján apró string műveleteket helyettesít, hát engem nem hoz lázba, bármikor képes vagyok beírni egy képletet, nem lesz koszos tőle a kezem.

GB és MB értékek konvertálása Excelben KB-be függvényekkel

Három cellában egymás alatt:

Azt a feladatot kapjuk, hogy a fenti számokat konvertáljuk kilobyte-ba, azaz:

Először is egy technikai megjegyzés: tradicionálisan “gigabyte” alatt 1024 “megabyte”-ot, vagyis 1024×1024 byte-ot, azaz 1073741824 darab byte-ot értünk. Ezzel eltérően az informatikában újabban, egészen pontosan 2000 óta, gigabyte alatt a 10-es számrendszerbeli megfelelőt, azaz 1000000000 (10^9) byte-ot mondjuk, a kettő hatványnak megfelelő számot gibibyte-nak (GiB) hívjuk. Ezzel megegyező módon mebibyte (MiB), illetve kibibyte (KiB) a helyes elnevezés kettő hatványaira, táblázat szerint mindez (ja, és kis kezdőbetűvel írandó):

A fenti példánk klasszikus módon tehát azt feltételezi, hogy 1 GB az 1073741824 byte, holott könyv szerint 1000000000-nak kellene lennie, vagy itt kellene GiB-nek írnunk és úgy feldolgoznunk. Szóval ezzel azért legyünk tisztában. (Én egyébként személy szerint nem GiB-ezek, nekem a giga az mindig is kettő hatvány marad.)

A pontos képletet nem másolom ide, mert Wyctim megöl, egy eléggé erősen egymásba ágyazott IF és string műveletes függvény, ami szépen KiB-be konvertál át mindent. A poszt végéről zip-be csomagolva letölthető. Köszönet illeti Bacskó Gergelyt, aki elküldte nekünk.

Clippy, örülünk?

Excel: napi számokból heti riport készítése (2. rész)

Az előző részben a napokra lebontott fabatka értékesítéseket a dátum megigazítása után a SUMIF függvénnyel adogattuk össze. Problémát jelentett viszont az, hogy kézzel kellett végigírni a heteket ügyelve arra, hogy évenként újraképletezzünk. Ennek egy frappánsabb megoldását mutatjuk be, ahol nem kell kézzel törődnünk semmivel. Ott tartottunk tehát, hogy előállt a megigazított dátum oszlopunk és ebből akartuk a heti számokat összeadni:

A megoldás mindössze annyi, hogy erre a táblára az Excel részösszeg (SUBTOTAL) funkcióját engedjük rá, ami pontosan azt csinálja, amire nekünk itt szükségünk van, azaz azonos típusú dolgokat például összeadogat. Szerencsére a részösszeg az első nem egyezés után továbbáll, így a különböző évekhez tartozó azonos hetek nem lesznek összekeveredve. Kattintsunk rá az ikonra, ez a “Data” fül alatt van:

Álljunk bele a táblánkban valami olyan helyre, amiben az adataink vannak, nyomjuk meg a gombot, az Excel pedig okosan kitalálja egyből, hogy mi a tartományunk és milyen részei vannak. Így kell beparamétereznünk:

Fontos megértenünk, mi történik, hogy a saját munkalapunkon magunk is elboldoguljunk vele: a legelső részen mondjuk meg, hogy a heteket figyelje, illetve alatta azt, hogy fabatka mennyiségeket adja össze. Ha megvan, a végeredmény pontosan az, amire számítottunk:

A jobb felső sarokban levő “2”-es számmal tudjuk az összes blokkot becsukni, ez már gyakorlatilag a végeredményünk:

A megoldás előnye, hogy bármikor mélyebbre fúrhatunk, ha kell, nyithatjuk, csukhatjuk a blokkokat, tehát az adataink pontossága nem sérült. Aki haladóbb Excelben, az tudja, hogy a részösszeg gyakorlatilag egy egyszerűbb Pivot-tábla, vagy kimutatásdiagram. Így van, akár azzal is eljárhattunk volna, csináljuk is meg. Kezdjük onnan, ahonnan a mai posztunk indult, viszont tegyük mellé az éveket is. Ezt nagyon egyszerűen a =RIGHT(A2;4) függvénnyel tehetjük meg, ami az eredeti szöveges dátumból levágja a jobb oldali 4 számjegyet. Ha végighúzzuk, meg is vagyunk:

Álljunk bele a táblába, majd “Insert” – pivot-tábla:

Okézzuk csak le, új munkafüzetre helyezi. Itt ismét manuálisan kell megmondanunk, hogy mi történjen. A sorokba az éveket, azon belül is a heteket és napokat kérjük, ezeket pedig össze akarjuk vele adatni, tehát a megfelelő mezőket húzgáljuk be ennek megfelelően:

A végeredmény magáért beszél:

Természetesen a három módszer közül mindenki azt használja, ami a céljaihoz leginkább megfelel. Összefoglalásképpen újra: a megoldáshoz a kulcs a dátumok feldolgozása volt, aztán a hetek előállítása. Innen már részösszeg, pivot-tábla, sumif – kinek mi.

Reméljük bemutatónk mindenkinek hasznos volt, akik most ismerkednek az Excel és a számok csodálatos világával. Köszönet illeti még Kovács Ákost és Franky-t, akik vették a fáradtságot, hogy továbbgyurmázzák az eredetit és mutassanak más megoldásokat.

Apropó, én magam sem vagyok nagy mágus, sőt, a fenti posztok is inkább a saját személyiségfejlődésem építőkockái.

Excel: napi számokból heti riport készítése (1. rész)

Tételezzük fel, hogy kapunk egy olyan adatsort Excelben, ami az eltelt napok függvényében megmutat valamilyen számsort. Mondjuk egy fabatkát előállító cég az ügyviteli rendszerből lehúzott egy riportot, ami az egyes napokhoz az értékesített fabatkát rendeli valahogy így:

Azt a feladatot kapjuk, hogy ebből a táblázatból állítsuk elő a hetente értékesített fabatka mennyiségeket. Összeadhatnánk szépen kézzel, de ha több évre visszamenő adathalmazról van szó, borzasztó körülményes, hosszú és hibázási lehetőséget bőven tartalmazó lesz az eljárás.

Megoldás:

Használjuk az Excel WEEKNUM függvényét, ami egy adott dátumról megmondja, hogy az év hanyadik hetében van. Ha ugyanis minden nap mellé beírjuk az adott hét számát, akkor utána már csak az azonos számokhoz tartalmazó értékeket kell összeadni.

A fenti táblával van még egy probléma, mégpedig az, hogy a dátum sorokat nem dátumnak látja, hanem szövegnek, így nem működik rajta a WEEKNUM. Első lépésben tehát a 07-20-2011 típusú dátumot kell olyan formátumra alakítani, amit már tudunk kezelni. Ezt a DATE függvénnyel oldjuk meg, ami dátumot konstruál három paraméterből (év-hónap-nap). Példa: ha beírjuk egy üres cellába, hogy =DATE(2011;07;20), akkor megkapjuk a dátumot, amit már kedvünkre formázgathatunk, vele műveleteket végezhetünk. (Nem tartozik a megoldáshoz, de Excelben a dátum valójában egy szám, ami 1900. január 1. óta eltelt napokat mutatja. 1900.01.01. az első sorszámú, 01.02 a kettes és így tovább. Ezért van az, hogy ha beírjuk, egy cellába, hogy 5, aztán a cella formátumot dátumra állítjuk, akkor – régióbeállítástól függően – 1900.01.05. jelenik meg.)

Első lépésben tehát állítsunk elő dátumokat az első oszlop szövegéből a DATE paranccsal. Nem kell mást tennünk, mint kivágni a jobb oldalról 4 számot, megkapjuk az évet, aztán az első kettőt, ez lesz a hónap, végül a közepét, ami a napot jelenti. Ezt beadni a DATE három paraméterének és meg is van a dátumunk. Egy cella jobb oldalról a RIGHT paranccsal tudunk levágni, bal oldalról a LEFT-tel, középről pedig a MID műt. Fenti példánkban, ahol A2 cellától indulnak a dátumok, a képlet a következő lesz:

=DATE(RIGHT(A2;4);LEFT(A2;2);MID(A2;4;2))

Eltérő színnel emeltem ki a DATE egyes paramétereit. A LEFT/MID/RIGHT függvényeket nem elemzem túl, az adott cellában meg kell nekik adni, honnan és mennyi karaktert akarunk levágni. Ha ezt lehúzzuk a jobb oldali oszlopban, máris megvannak a dátumok, amikkel már dolgozhatunk:

Most akkor jöhet a WEEKNUM, azaz annak a megállapítása, hogy melyik dátum melyik hétre esik! A WEEKNUM kér egy dátum típusú forrás cellát, meg egy paramétert, hogy a hét kezdete milyen napra essen. A hétfő a jó választás (nálunk mindenképpen), így a 2-es számot adjuk be így:

Ha megvan a képlet, valószínűleg dátum formátumú lesz a cellánk, így a CTRL-1 billentyűkkel állítsuk át előbb a formátumot számra, majd utána húzzuk le, meg is lesznek a heteink szépen:

Innen nincs más hátra, mint összeadogatni az azonos számú heteket. De hogy csináljuk? Cselhez kell folyamodnunk. A probléma annyi, hogy a hetek száma ismétlődik, nekünk pedig egyesével kellenek majd a továbblépéshez. Egy új oszlopba írjuk végig a heteket egyesével az induló hét számával kezdve!

Egyszerűsítés: írjuk be az első számot, írjuk alá a másikat, jelöljük ki a két cellát, majd úgy húzzuk le a jobb alsó sarkát megragadva, az Excel automatikusan léptetni fogja a sorszámot!

Egy dologra kell vigyázni: az utolsó hét az 53-as, így ott állítsuk meg az írást, aztán kezdjük megint 1-ről, majd írjuk végig addig, ahány hetünk van. Ha több évnyi az adat, akkor évente mindig meg kell állni és 1-ről indítani. Vegyük észre azt is, hogy az óév 53. hete és az újév első hete valójában ugyanazok a hetek, így azokat a számokat majd kézzel kell összeadnunk. Kis munkát követően a táblánk immáron:

Most pedig jöhet a mágia! A SUMIF függvénnyel fogjuk összeadogatni az értékeket, mégpedig a most konstruált oszlopunk, mint kritérium alapján. A SUMIF a következőt mondja: adj meg egy kritériumot (például a hét számát), utána mondd meg, hol találok ilyeneket, én meg ahol találtam, összeadom a másik oszlopban levőket és visszaírom a végeredményt. Ez gyakorlatilag pontosan az, amire nekünk szükségünk van, azaz az összetartozó hetek értékesítési számait adja össze. A képlet csak elsőre tűnik bonyolultnak:

Első paraméter a kék, itt megmondtuk neki, hogy mi az a tartomány, ahol nézze, utána megmondtuk, hogy a kritérium, végül azt a tartományt, aminél össze kell adnia az értékeket, ha talált a kritériumnak megfelelő értéket. A dollár jelek ismerősek lehetnek már: ezzel tudjuk rögzíteni a tartományokat, hogy ne másszanak el. Az én példámban a 317-ik sorig mennek az értékek, azért ennyi.

Nagyon fontos: ezt a képletet is évenként konstruáljuk meg újra, mert ha nem, akkor a különböző évek értékesítési adatait megegyező sorszámú hetek esetén újra összeadja! Az én példámban pont úgy jött ki, hogy nincs azonos számú, eltérő évhez tartozó hét, de ez véletlen. Ha egy évnél több adatunk van, akkor évente csináljuk meg.

Egyébként készen is vagyunk: a félkövérrel kiemelt két oszlopban láthatjuk, hogy a cég az egyes hetekben mennyi fabatkát értékesített.

Mindezt a napi adatok alapján! Klassz! Aki szeretné, letöltheti természetesen a példa táblázatunkat is. A fenti megoldást egyébként eFi találta ki, a dokumentáláshoz szükséges erőt viszont Clippy biztosította. Házi feladat lesz majd az 53 és 1 hetek értékeit kézzel összeadni.

update: A poszt következő részében megmutatjuk, hogyan lehet mindezt ennél még egyszerűbben csinálni.

Excel 2010 újdonságok

Ma végre megléptem az ugrást és 2003 után felraktam a 2010-es Windows Excelt – a dolog érdekessége, hogy 2010 óta itt ül a gépemen és eszembe se jutott feltenni, hiába, ami nem romlott el, azt nem kell megjavítani. Egyáltalán nem kellett volna tőle tartanom, a Microsoft betegesen fél minden nagy változtatástól, néhány perc alatt rájövünk, hogy szinte teljesen ugyanaz a program (CTRL-1 cellaformázás röhejesen semmit nem változott hét év alatt).

Az egyetlen furcsaságot, az újrarendezgetett ikonokat befoglaló ribbont (menüszalag!) a CTRL-F1 billentyűzetkombinációval lehet összezárni, így sokkal több helyünk marad a képernyőn, ráadásul ugyanúgy lenyílik szépen minden, ha megkattintjuk az egyes menüket.

Ha valaki hozzám hasonlóan a programcsomag újdonságaira kíváncsi, olvassa el az Excel blog egyes bejegyzéseit, nagyon jók, tényleg. Egy gyűjtőoldalon pedig szépen össze van minden főbb újítás foglalva.

Kiemelek néhány dolgot, amit érdemes lesz mélyebben átnéznem: sparklines, slicers, new search filter, improved icon sets, data bar improvements, charting enhancements.

Ezek itt mind 2009-es bejegyzések, üdv tehát az időgépből.

#HIÁNYZIK

Az FKERES (angolul VLOOKUP) az a függvény, amivel általában le szoktam mérni, ki mennyire ugatja a programot — főleg, ha azt is tudják, hogy mennyire hasal el a különféle formázásokon és azt hogyan lehet javítani. Hasznos függvény, fejből megy.

Egy dolog mindig is zavart: ha nem talál (szed fel) értéket, akkor a #HIÁNYZIK üzenetet helyezi el a cellában. Ez érthető is valahol, de nagy mértékben megnehezíti a tájékozódást. Sőt, kifejezetten randává teszi a táblákat. Kézzel kitörölni gagyi, erre automatizmus kell.

Zavart annyira a dolog, hogy eFivel utánamásztunk. (Illetve én másztam utána, ő csak irányította a folyamatot az R9-ből.)

A megoldás:

=HA(HIBÁS(FKERES());"";FKERES())

Ha hibára fut, üres cellát ír be (ha-hibás-fkeres), különben ugyanezt az értéket. Rémesen egyszerű, csak én az ISERROR, magyarul HIBÁS résszel nem voltam tisztában. Értelemszerűen az FKERES() függvény paramétereit is be kell írni, de ezt már az olvasóra bízzuk. Sajnos nem tudom megválaszolni, hogy miért kell ilyen hekkelős módszerhez folyamodni, azt hittem, hogy ezt tiltani lehet a beállításokban.

Az eredmény viszont csodálatos! Bal oldalon a treatment előtt, jobb oldalon utána:

Rendes ember #HIÁNYZIK-kal nem release-el.

update: nem volt helytelen a feltételezésem, a fenti tényleg gányolás és 2007-es Excel óta van HAHIBA függvény, ami pontosan ennek a kiküszöbölésére való. Itt a megfejtés jóval egyszerűbb:

=HAHIBA(FKERES();"")

Thx Ivan!

Bahttext

(Meetup) A BAHTTEXT-ről szóló előadás tetszett és érdekes, kézzel fogható volt a maga módján, írok is róla egy összefoglalót: 2002-ben az angol nyelvű Excelbe építettek egy függvényt, ennek a neve a BAHTTEXT. Ez a függvény képes bármilyen numberikus számot számnévvé alakítani thai nyelven. Semmi más hasonló funkció nincs az Excel korabeli változatában, csak ez, tehát még angolul sem megy ugyanez.

Magyarul van némi help is hozzá extra infóval:

A mese úgy tartja, hogy a Microsoftnál levő, Excelen dolgozó kollégák annyira kedvelték a thai kajákat, hogy megírták ezt a függvényt, hogy a redmondi éttermek natív közönségét el tudják vele kápráztatni. A dolog olyan jól sikerült, hogy még a szabványok közé is bekerült a BAHTTEXT. A baht (บาท — magyarul bát) egyébként a thai-ok fizetőeszköze. A függvény tehát annyit csinál, hogy 58-at kiírja betűvel: ötvennyolc, mindezt törzsi nyelven.

Azt nem tudom, hogyan, vagy miért, de az OpenOffice kitalálta, hogy ebből a függvényből kell készíteni egy univerzálisat, ami gyakorlatilag egy egyszerű szintaktika segítségével bármilyen nyelv számát képes lesz számnévvé alakítani. Az OpenDocument Formula szabvány tervezetéből ugyanis a vélhető technikai nehézségek miatt kihagyták a BAHTTEXT függvényének általánosítását. Németh László ezen dolgozik és bemutatta azt, hogy milyen módszerrel állítható elő bármely nyelvre a függvény.

Egy kapcsolódó érdekesség, hogy kínaiul a hármas szám számnévként három függőleges rovátka, viszont ha mellé kerül egy másik hármas, akkor, vélhetően a könnyebb azonosítás miatt, a kínai nyelv vízszintesen rovátkol. Aztán megint függőlegesen és így tovább:


Németh László és az ő kínai betűi

Ahogy a Plastik média szerkesztője kommentálta Twitteren (akire, FYI, szavazzatok a versenyben!):

Függöny.

update 20090516: Zhaoman kiegészíti a fent elhangzottakat. Idézem:

“Egy kapcsolódó érdekesség, hogy kínaiul a hármas szám számnévként három függőleges rovátka”

Ez nem igaz. A hármas szám számnévként három vízszintes (!) vonal.
1:一
2:二
3:三
4:四
5:五
6:六
7:七
8:八
9:九
10:十

A kínai nyelv külön jelöli a tizes, százas, ezres és tízezres helyiértéket. Tehát a 33-at nem úgy írja le, hogy “3” “3”, hanem, hogy “3” “10” “3”, azaz háromszor tíz meg három. Helytelenül: 三三。 Helyesen: 三十三。A 33333 kínaiul így néz ki: 三万三千三百三十三 Évek jelölésénél eltérnek ettől.

Excel 3D

Gondolt már valaki arra, hogy az Excelben használható rajzoló eszköztár mögötti anti-aliasolt (!) poligonokból lightsourced 3D rutint írjon? Nos, Rákos Péter magyar programozó elkészítette ezt is:


Olyan, mint a Windowsra overlay-elt D3D!

Az eredeit cikk elolvasható itt, a példák letölthetők innen. Bármikor nyomjunk neki ESC-et és szedjük fillezett háromszögeire az objektumot egérrel! Nagyon vicces. Van még egy másik demó is, ami double-buffer (err.. sheet!) line vektort implementál, hahaha! (via Greg via email)