Plastik média

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.

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!

(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.

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)

Kiegészítenénk a júliusi ToNumber Excel bejegyzésünket. Ismeretes a jelenség: SAP-ből, vagy más adatforrásból Excelbe átadott oszlop formázása olyan, hogy hiába is próbáljuk átkonvertálni CTRL-1-el a számformátumot bármibe, nem lesz jó. Ez akkor baj, ha pl FKERES-ezni akarjuk a kérdéses oszlopot, vagy valami más hasznosságot hajtanánk végre. (FKERES különösen paraszt módon viselkedik ilyenkor, ugyanis #HIÁNYZIK-ra futnak a cellák — csak az lehet a gyanús, hogy nincsen egyetlen egyező érték sem.)

Annak idején már bemutattunk egy automatizált megoldást erre a problémára. Ma egy workaroundot mutatunk be azoknak, akik nem akarnak “telepíteni”, illetve közzéadjuk a ToNumber 2.0 változatát, ami képletezett cellákon is működik, valamint könnyedén lehet teljes oszlopra is alkalmazni.

Workaround

1. írjunk be valami semleges cellába egy 1-es számjegyet, üssük le a copy-t (win: CTRL-C mac: CMD-C)

2. jelöljük ki a problémás részt (akkor is működik, ha az oszlop tetején klikkeltünk!), majd jobbklikk, irányított beillesztés

3. kattintsuk be a “Szorzást” és hajtsuk végre a műveletet

ToNumber v2.0

Telepítsük a korábban már ismertetett módon a ToNumber_v2.xls állományt. Jelöljük ki a cellákat, vagy oszlopot, majd fenn a menüben az Xtras menüpont alatt válasszuk ki a megfelelő funkciót (képlettel vagy képlet nélküli formázást kívánunk-e).

Enjoy!

Akinek van Office 2007 SP1 tudása, kérem ossza meg, hogy abban a verzióban is fennállnak-e ezen jelenségek.

Penoff Gábornak köszönjük a folyamatos Excel segítséget, nélküle nem azok lennénk, akik!

FKERES, VLOOKUP vagy esetleg SVERWEIS?

Akinek valaha is gondot okozott angolról magyarra átállni Excel neveket illetően, esetleg megáldotta valaki egy német excellel, az biztos áldani fogja azt a jótét lelket, aki mindhárom nyelvbe összeállított egy referencia mátrixot (értsd: mindhárom nyelven megvan a táblázat). eFi át is konvertálta PDF-be, tőle szívható.

Köszönjük Dancsó Jánosnak, hogy megosztotta az információt! Namaste.

update 20080107: a c:\Program Files\Microsoft Office\OFFICE11\1038\funcs.xls eldugott file gyakorlatilag pontosan ezt teszi, mindenki nézze meg, akinek nem angol windows-a van. A tippet köszönjük TillZ olvasónknak.

Le is keywordözöm ezt a postot, mert fontos lehet bárkinek: excel – függvény – angol – english – deutsch – magyar – német – functions – crossreference – list – translation – convert

Tudtátok, hogy a forrás táblában az első oszlopot tudja csak keresni a VLOOKUP? Tehát nem lehet megadni neki azt, hogy van egy kurvanagy táblám, de annak az egyedi kulcsa mondjuk a negyedik oszlop. Természetesen nem gond, mert könnyű átképletezni a dolgot, csak éppen tudni kell azt, hogy ez így működik, és innentől lesz jó. Addig meg bámulhatjuk a #HIÁNYZIK cellákat és vakarhatjuk a fejünket, hogy akkor most vajon nem a cellaformázáson mászott-e el a dolog.

Kérdés: szerintetek az Office 2008-ban (Vista edition) történt-e bármi érdemi változás ezt illetően?

Kérdés: miért nincs az irányított beillesztésben opció arra, hogy beillesztés FKERES használatával? Kis kényelmi okosság.

Ha már Excel: Penoff Gábor Baján nyomja az Excel kódingot (szintén 2003-at használva!) és főzött egy kacsát ebédre.

Új rovatunkban Excel tippeket, érdekességeket mutatunk. Fns írja emailben:

Tudtad-e, hogy ha van egy egybefuggo adatteruleted (sok cella connected to each other), abban barmelyik cella szelere klikkolva az osszefuggo terulet relevans szelere ugrik a cellakurzor (azaz a bal/jobb szele az elso/utolso oszlopra, a teteje/alja az elso/utolso sorra)?

Mivel ez így leírva kissé bonyolultnak tűnik, fns készített egy apró screencast-et három klikkből (THANKS JING PROJECT!) és máris megvilágostunk!

Plastik media: idegen tollakkal és szolgáltatásokkal ékeskedő mash-up, ahol naponta több új rovat is beindul.

Íme a rettegett hibaüzenet:

A jelenség magyarázata (annak, aki tényleg képtelen elolvasni és/vagy értelmezni, ami oda van írva): valamely cellában, vagy cellákban, egy másik file-ra való hivatkozást talált a motor, viszont a file nincs a helyén. Tipikusan akkor fordul elő, ha átmozgattuk a hivatkozott Excel sheetet, vagy emailben kaptunk valamit, és nincs mögötte a forrás állomány. Számokat fogunk látni, de az Excel arra hivatkozik, hogy ezek nem feltétlen friss számok.

A “Szerkesztés” menü “Csatolások…” pontja alatt kaphatunk részletes elemzést erről:

A feljövő ablakban láthatjuk a hivatkozott állományt, és rendelkezhetünk a probléma kezelése felől:

A kollégáknak küldött esetben érdemes a bal alsó sarokban levő “Indító rákérdezés…” pont alatt a “Ne legyen rákérdezés …” pontot bekattintani és úgy elmenteni a lapot:

Ha kapott anyagból dolgozunk és meg akarjuk találni a kis gonosz cellákat, amik a külső lapokra hivatkoznak a “Szerkesztés” menü “Keresés… (Ctrl-F)” funkciójával tudunk rákeresni a kérdéses részre:

Látható, hogy a keresett szöveg a korábban megállapított file nevének egy részlete. A “Listába mind” gomb összegyűjti mindazon előfordulásokat, amik a külső file-ra mutatnak. Innen már egyszerű a debug.

Credit: a tudás Penoff Gábor szakember irányából érkezett.

Emelje fel a kezét, ki az, aki irodát bérel (valakitől) ahelyett, hogy ugyanennyi pénzért megvásárolná az ingatlant (egy ingatlant) részben saját erőből, részben bankkal finanszíroztatva, melynek TÖRLESZTŐ RÉSZLETE ugyanannyi, mint az ingatlan bérleti díja? Csak ugye ebben az utóbbi esetben van tőke törlesztés is, ami néhány év elteltével már eléri a “nem mindegy” státuszt.

Na, ez a nem mindegy.

Finanszírozási rovatunkat olvasták. Részletes számolást mindenki elvégezheti otthon, a szó most ismét a stúdióé!

Gyűlt már meg valakinek a baja azzal az Excel problémával, hogy kapott egy adatsort, amiben számok voltak, és az Excel nem tudta azokat összeadni? Például úgy, hogy kijelöli a számokat, vagy alul összeszummázza?

Nem tart sokáig, hogy rájöjjünk, valójában számok azok, csak Excel barátunk szövegnek értelmezi őket. Csúfos esetben még az is lehet, hogy a cella tulajdonságainál se tudjuk visszaállítani, aztán jöhetnek a koszos hekkelések, pl megszorozzuk a cellákat 1-el, aztán hátha úgy válik belőle szám típus, meg a hasonlók.

Penoff Gábor Excel szakembert hívtuk segítségül, hogy választ adjon erre a kihívásra. Ezt mondja:

“A probléma lehet mondjuk azért, mert a mező értéke string delimiterek, mondjuk idézőjelek közé zárva érkezik, de általában akkor csinál ilyet, ha a ThousandsSeparator vagy esetleg a DecimalSeparator nem egyezik az adatot exportáló és importáló rendszerekben.”

Mi a megoldás?

“Egy kis rutinka, ami annyit csinál, hogy végig megy a mindenkori kijelölés összes celláján, és ha a cella nem üres, megprobálja a tartalmát numerikus értékké konvertálni, illetve a cella formátumát General típusúra Text helyett. A rutin csak azokra az esetekre megoldás, ha a cellában text-ként szereplő számban az ezres és a decimális szeparátor karakterek az OS Regional Setting-jében meghatározott karakterek.”

Hol találom ezt a rutint?

“Lépjünk ki az Excelből, majd a C:\Program Files\Microsoft Office\OFFICE11\XLSTART mappába tegyük bele ezt a file-t kicsomagolva (ha nem 2003-as Office-t használunk, máshogy nézhet ki ugyanez, de csak keressük meg az XLSTART-ot). Újraindítás után meg fog jelenni a menuben egy Xtras pont, abban pedig a toNumber rutin.”

A rutin forrása a törés után olvasható. Penoff Gábornak pedig köszönjük, a szó ismét a stúdióé!

A teljes bejegyzés

Excelben ha másolok (!) egy dátum cellát két dokumentum között, akkor valami érthetetlen módon 2011-es év lesz belőle. Próbáltam mindenféle paste-elést, semmi. Az egyetlen megoldás az lett, hogy átmásoltam az egész dátum oszlopot, csináltam mellé egy újat, amin lehúztam ezt: =[source cell]-4*365-2, ami előállította a helyes dátumot. Másik dokumentumból paste-elek dátumot, akkor meg másik irányba mászik el a dátum, ott minden 2003 lesz. Mi ez?!

Kipróbáltam két üres dokumentumban, elvártnak megfelelően működött. Semmi nem mászott sehova. Ajajj, sejtem, ez megint olyan para lesz, hogy régóta használt Excel sheeteket jobb üresről felépíteni! Ki tudja, mi minden van a file bytekolbászban. Ecsém!

update: Megvan a megoldás, akit érdekel, a kommentekben megtalálja a leírást.

Szoktad használni a szegélyek rajzolása toolbart Excelben?

Dude, honestly? You’re sick.

Kérdés: milyen automatizmussal állítható elő a fenti táblából a lenti? A felhasználó csak a darab oszlopot tölti ki, aminek függvényében dinamikusan elkészül egy összefoglaló táblázat. Apró kozmetikai kérdés még, hogy milyen módszerrel lehet a nullás értékű sorokat üresként szerepeltetni. (Eddig IF() funkcióval dolgoztam, de tök bénán: IF((expression=0);expression;” “) — na ez a full gagyi, mert az expression helyén ott van ugye a képlet — kétszer. Illetve ezt akartam továbbterjeszteni olyanra, hogy ha bármilyen hibás eredményre fut, ne jelenítse meg a cellát. Például negatív ár, zéró osztó etc. esetén. Lehet, hogy erre van valamilyen beépített függvény, még nem túrtam.)

Thanks!