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.

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.

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óé!

Read the rest of this entry »