Öt titkos vagy alig ismert Excel-funkció, amelyek színesebbé tehetik táblázatainkat

Van, amelyiket állítólag hibás működése miatt nem dokumentáltak

2015. szeptember 10. - Office Guru

Általánosságban elég nagy bizonyossággal kijelenthető, hogy a legjáratosabb Excel-felhasználók sem használják a programban rendelkezésre álló függvények jelentős részét (egy felmérés szerint az átlagfelhasználó az Excel 5%-át ismeri és használja), sőt, én rendszeresen találkozom azzal az amúgy igencsak becsülendő jelenséggel, amikor valaki az általa ismert formulák segítségével old meg egy olyan kérdést, amelyre egyébként már létezik egy kifejlesztett egyedi függvény is. A következőkben öt olyan függvényt mutatnék be, amelyek nem túlságosan ismertek, de szerintem roppant hasznosak lehetnek és sok területen szükség is lehet rájuk, hiszen beágyazásoktól, segédcelláktól kímélhetnek meg bennünket.

Az első bemutatásra kerülő függvény már csak a körülötte kialakult rejtélyesség miatt is a DATEDIF nevű formula, amely a Ribbon Formulas füle alatt található "Insert Function" listájában nincs is benne és egyedül talán valamelyik régebbi Excel verzióban volt dokumentált nyoma bárhol is a programban (ennek okáról tudok is egy jó pletykát, amely szerint egy felfedezett hiba kijavítása helyett vették ki a listából, azaz egy januári és egy márciusi dátum között nem egészen jó számot hoz a függvény). Ettől függetlenül ha cellába visszük fel, akkor működik és nagyon is jól működik, hiszen megadja nekünk két dátumot között a különbséget abban az értékben, amire szükségünk van - azaz az első paraméterként bedobjuk az első dátumot, második paraméterként a másodikat, majd harmadik paraméterként megadjuk, hogy napban, hónapban, évben vagy ugyanezekben úgy, mintha ugyanarról az évről beszélnénk (ezek a paraméterek az "m","y","d","ym","yd","md"), tehát például a

=DATEDIF(F6,F7,"m") az F6 és az F7 cellákban szereplő dátumok közötti különbséget adja meg hónapban.

otvenkilenc.jpgSok-sok kalkulációtól meg tudjuk magunkat kímélni, ha felírjuk elménk füzetének egyik lapjára a CONVERT formula nevét, ugyanis ennek segítségével 49 különböző mértékegység között tudunk átváltásokat végezni, persze csak egy bizonyos típuson belül (tehát kilométert ne akarjunk literre váltani). Be nem fogom most másolni ide az összes lehetőséget, aki szeretne elmélyülni a dologban, az hívja segítségül a Helpet a teljes listához.

hatvan.jpgMindenesetre példánkban az =CONVERT(E3,"kg","g") értelemszerűen ki fogja írni, hogy az E3 cellában tárolt 10 kilogramm pontosan hány grammnak felel meg.

Általában mit teszünk, ha azt szeretnénk megtudni, hogy egy adott Excel-fájlunkat éppen hova mentettünk vagy honnan nyitottunk meg? Vagy megpróbáljuk a folderek között levadászni vagy hozzáadjuk a Document Location parancsot a Quick Access Toolbarunkhoz - vagy használjuk az INFO függvényt! Ez ugyanis információkat szolgáltathat operációs rendszerünkről, Excelünk verziószámáról, kalkulációs metódusáról, nyitott munkalapjaink számáról és hát adott fájlunk könyvtárának elérési útjáról is.

Azaz az =INFO("directory") szépen meg fogja adni, honnan nyitottuk meg aktuális fájlunkat.

Majd későbbi példákban hozni fogok olyan kérdéseket, ahol rendkívül kapóra jön majd nekünk a DELTA formula, a formula amely alapvetően nem csinál semmi mást, mint összehasonlít két számot és kiírja, hogy egyenlőek-e vagy sem.

Tehát =DELTA(A1,A2) összehasonlítja az A1 cellában lévő számot A2-vel, majd ha ezek megegyeznek, 1-et ad vissza, eltérés esetén 0-t. Gondolom most néhány emberben felötlött a gondolat, hogy vajon mégis hol a fenébe lehetne ezt jól hasznosítani - jómagam két célra használtam már a múltban több ízben, az egyik esetben akkor, amikor nem számformátumban tárolt értékeket akartam Number formátummal bíró számokkal összehasonlítani, a másik esetben pedig akkor, amikor egy hatalmas listában a megegyező értékeket szerettem volna összeszámolni beágyazott függvénnyel segédcellák nélkül.

hatvanegy.jpgKönnyedén megkerülhető, ám szerintem elég szép megoldás az ERROR.TYPE függvény alkalmazása, amely konkrétan egy cellában lévő ERROR értékekből (pl. #NULL! vagy #VALUE!) számokat ad nekünk vissza, tehát mondjuk #NULL! esetén az ERROR.TYPE egy egyest fog nekünk kiírni. Ez akkor jöhet igazán jól, ha hibaüzenetek esetén inkább szöveget szeretnénk megjeleníteni, nem a megszokott és idegesítő #N/A-kat. Lásd példa.

hatvanketto_1.jpg

A bejegyzés trackback címe:

https://officeguru.blog.hu/api/trackback/id/tr597776674

Kommentek:

A hozzászólások a vonatkozó jogszabályok  értelmében felhasználói tartalomnak minősülnek, értük a szolgáltatás technikai  üzemeltetője semmilyen felelősséget nem vállal, azokat nem ellenőrzi. Kifogás esetén forduljon a blog szerkesztőjéhez. Részletek a  Felhasználási feltételekben és az adatvédelmi tájékoztatóban.

dez01 2015.09.11. 14:33:01

Én sokáig a HIBÁS képletet használtam 1 HA függvénybe ágyazva az Fkeres Hiányzik eltüntetésére.. De a HAHIBA függvény jobb és 1szerűbb......

fektoh 2015.09.11. 15:24:02

Csak részben kapcsolódik ide, de pl. egy nagyon általánosan használt függvény viszont bizonyos körülmények között hibás értéket ad vissza. Konkrétan a pénzügyes körökben méltán népszerű NPV-ről (net present value, nettó jelenérték) van szó. Az egyik magyarországi egyetem pénzügyi tanszéke jelezte is a problémát a Microsoftnak, akik viszont mélyen hallgatnak a dologról, mert nem akarnak brutális kártérítési pereket a nyakukba (mi van, ha egy sokmilliárd dolláros beruházás kalkulációjánál hibázik egy "kicsit" a függvény?). Aki pontos eredményt akar, inkább használja az NPV számítás normál képletes alakját.

Gyűrött Papír 2015.09.11. 15:25:15

Innen csókoltatom azt, aki a nemzeti excel változatban a függvények nevét magyaríttatta.
A deltát nem ismertem. Ügyes..

Norman Nailer 2015.09.11. 16:01:18

A Document Location egy hihetetlenül hasznos funkció vállalati környezetben (most akkor szerverről nyitottam meg a fájlt vagy saját gépről?), régebben Web (address) néven futott, a 2007-esben lett Document Location, a 2010-esben eltűnt (legalábbis nekem nem sikerült ráakadni), a 2013-asban úgy látom, megint ott van.

Metálvörös 2015.09.11. 16:03:39

A DATEDIF-es pletykát simán elhiszem, az Office 2010 még mindig úgy tudja, hogy 1900 szökőév volt, azaz volt benne február 29. Érdekes módon 2100-at már jól tudja.

szürkemókus 2015.09.11. 16:20:12

Nem kell hozzá függvényt megjegyezni, kivonom a korábbi dátumot a későbbiből, az eredmény cella formátumát pedig számra állítom :)

McZozo 2015.09.11. 16:23:53

ISERROR szerintem kicsit egyszerubb az altalad leirtak alapjan mint ez az ERROR. TYPE. Pl VLOOKUP fugvenyek eseteben siman hasznalhato hogy ne N/A t adjon valaszul, VLOOKUP ele rakod aztan zarojel vlookup, vesszo "Barmi" zarojel bezarva

McZozo 2015.09.11. 16:24:55

@szürkemókus: NA ez meg a masik amit irni akartam :) de ettol meg jo tudni

McZozo 2015.09.11. 16:26:52

En multkor fel napot azzal szivtam hogy megtalaljam azt a fugvenyt ami ket szoveges cellat tud ugy osszehasonlitani hogy megtalalja ha van kozte kisbetu nagybetu kulonbseg :)

Mérnork 2015.09.11. 16:29:21

Azt oldanák meg, hogy a txt-ből importált számokat ne akarja random módon dátummá alakítani, mer etől agyfaszt kapok...

SpadIa z oblakov 2015.09.11. 17:28:31

@Mérnork: Nálam ez olyankor fordul elő, ha az thousand separator pont. Ezt importálásnál be lehet állítani.

Hujber Tünde · http://7veszteseg.blog.hu 2015.09.11. 18:54:34

Nekem az INFO rosszul működik. Nem megfelelő könyvtárat ír ki.

D2 2015.09.11. 18:54:55

@szürkemókus: A kivonás két dátum közötti különbséget napokban adja meg, a függvénnyel másra jó: www.youtube.com/watch?v=F9dz30QkL7g
Ha magyar Exceled van a DÁTUMTÓLIG függvény súgóját nézd meg.

kőhányó 2015.09.11. 22:29:03

Kipróbáltam a "CONVERT" függvényt, ahogy leírtad. Hibás függvény üzenetet kaptam. Ha vesszőt (,) írsz az argumentumok közé ez akkor lép fel. Helyesműködéshez pontos-vesszőt (;) kell írni.

phls 2015.09.11. 23:29:54

"Általában mit teszünk, ha azt szeretnénk megtudni, hogy egy adott Excel-fájlunkat éppen hova mentettünk vagy honnan nyitottunk meg?"
Általában ilyenkor nem vinfost, hanem oprendszert használunk. Az tudja ugyanis.

mt-03 2015.09.11. 23:37:48

@Gyűrött Papír: hat ja, amikor ezt eloszor lattam nem akartam elhinni.

Beer Monster 2015.09.12. 00:10:21

@Mérnork: A simán begépelt bármit is át akarja időnként ötletszerűen alakítani bármi mássá. Azt hogyan lehet kikapcsolni, egyszer és mindenkorra?

Hijaszu 2015.09.12. 00:19:57

@Mérnork: mondjuk, amikor a csv-t importálod, akkor át lehet állítani, hogy melyik oszlop adatformátuma mi.

nyu 2015.09.12. 00:45:43

Ha aposztrófot (') teszel az érték elé, akkor fixen szöveg marad a cella típusa, nem fogja automatikusan számmá/dátummá konvertálni.

totalnick 2015.09.12. 07:58:55

még az office 2003-as időkben volt, hogy a week függvényre nagy szükségem volt. Egy adott hét az hányadik hét az évben.
És persze a magyar rendszerben rosszul számolt. Utánaolvastam fórumokon, és mások is panaszkodtak. Állítólag valaki írt is az MS Magyarországnak, hogy nem azt látja amit a falinaptárakban. A válasz: a naptárak rosszak. (aztán kussban kiadtak rá egy javító VB kódot, és a függvényt csak ezen keresztül szabadott meghívni)

Beeblebrox de Tom 2015.09.12. 08:43:03

@Mérnork: Próbáld meg a Contol Panel/Region and Language menüben English-re állítani a formátumot. A billentyűzet marad magyar, de megszűnik ez a végtelenül idegesítő dátumformázás.
Amióta így használom megváltozott az életem :-)

♔bаtyu♔ 2015.09.12. 08:53:03

@SpadIa z oblakov: Tévedés, kötőjel és perjelnél is dátumra alakítja, ha kijön belőle egy értelmes dátum. Pld. Szabolcs utca 9/11, vagy 9-11. Az aposztróf segít, csak éppen baromság, mert ott van. A régebbi Excel verzióknál alapból szöveges volt a formátum. Ma is ennek kellene lennie, hiszen minden adat ősformájában szöveg és majd ÉN eldöntöm minek szánom később.

♔bаtyu♔ 2015.09.12. 09:00:51

@Hijaszu: Csv formátumban alapból beolvassa és (sajnos) amit tud, dátumra konvertál. Utána már állíthatod a formátumot, az infót az Excel már elvesztette, nem tudja vissza állítani. Ezért kell a txt, mert azt nem ismeri fel, ott valóban elvégezheted te az állítgatásokat. (Szeparátor, oszlopformátumok)

Viszont sokszor kell a csv, mert Gizike ehhez nem ért, neki azonnal a táblázat kell.

Hijaszu 2015.09.12. 09:06:19

@♔bаtyu♔: Nem kettős kattintással kellene a CSV állományt megnyitni, hanem File > Import... . A CSV az TXT csak a kiterjesztése más. Azon meg nem kell csodálkozni, ha alapértelmezett beállítások vannak akkor amikor kettős kattintással nyitsz meg valamit.

Ez nem Excel hiba vagy rejtett feature. Az, hogy nem tudja valaki használni az amúgy normálisan működő funkciót, az nem programhiba.

♔bаtyu♔ 2015.09.12. 09:19:54

@Hijaszu: Igen, de az a gond, hogy amikor fizetnek érte, nem magyarázhatod el nekik, hogy az egy excel file, de dolgozzanak még rajta.

Ez nem hiba, hanem egy Excel baromság. Nem véletlenül nem volt benne a 2007 előtti verziókban.

Ők azt várják el, hogy megnyitják és ott van az excel táblázat.

Hijaszu 2015.09.12. 09:21:54

@♔bаtyu♔: Erre viszonylag egyszerű a megoldás. El kell távolítani a CSV kiterjesztést az Excelhez társítottak közül, aztán mindjárt nem tudják megnyitni kettős kattintással :D

♔bаtyu♔ 2015.09.12. 09:24:55

@Hijaszu: Lemaradt? Ha a csv-t az Excelből nyitod meg, akkor is azonnal beolvassa.

Na ez a "File-import....", mindenki szeretné elkerülni, akinek fizetnek azért, hogy excel file-t generáljon. Ennél talán még a txt is jobb, ami kikényszeríti azt, amit ez csinál. Bár ebben az esetben az sem jó. Azért fizet mert nem ért az excelhez, tehát jogos az, hogy te oldd meg.

♔bаtyu♔ 2015.09.12. 09:25:41

@Hijaszu: Persze, elutazol Miskolcra, Győrbe és eltávolítod:)))

Atzs · https://adatrendezo.hu 2015.09.12. 09:27:27

@♔bаtyu♔: A kérdés az, kinek kedvezzünk, aki kezdő vagy aki haladó. Utóbbi már felkészül ezekre a problémákra, viszont nagyon sok idejét elvenné, ha állítgatni kellene szövegről a formátumot. Többet, mint amit egy-egy csv beolvasásnál mégis elveszít.
Nálam a megoldást a decimális karakter pontra állítása jelentette. Aki jellemzően angol adattáblákkal dolgozik (pl. statisztikai programok kimenete) vagy angol programokat etet az eredményeivel, annak megfontolandó.

Hijaszu 2015.09.12. 09:30:30

@♔bаtyu♔: Nem teljesen értem, hogy mi a problémád, de abban látom, hogy egyetértünk, hogy nem az Excel a hibás. Ha hülye ügyfeleid vannak, azzal nyilván nem tud a Microsoft mit kezdeni. Vannak hülyeségei az Excelnek, de ez spec nem az, és összesen ennyit akartam leírni az eredeti hozzászólásommal.

Tényleg lehet, hogy vannak hülye ügyfelek, de akkor lehet mást megoldást találni. Lehet például az Excel állományt saját programból is írni meg olvasni. Igen, munkát kell bele fektetni, de mint megjegyezted, ezért fizetnek. El kell nekik magyarázni, hogy mit fognak kapni. Ha nem akarnak többet fizetni, tanulják meg használni a programot. Olyan nincs, hogy hülye vagyok a programhoz, nem fizetek semmit, és hirtelen minden működik. Ezt csak Magyarországon várják (várnák) el a vállalkozóktól, de ott se jön össze :D.

♔bаtyu♔ 2015.09.12. 09:55:53

@Atzs: Annak kedvezünk, aki FIZET.

♔bаtyu♔ 2015.09.12. 09:56:22

@Hijaszu: Nem hülyék, csak ez nem érdekli őket. Ezért is fizetnek engem.

omegaline 2015.09.12. 11:42:46

@Metálvörös: Ezt én is reklamáltam mindenhol, ahol szóba került. Az eredménye ismert, ezért kiegészítettem egy saját függvénnyel.

Beer Monster 2015.09.12. 20:02:49

@Atzs: Azt nem értem, kinek juthatott eszébe egy kvázi programnyelvben vesszőt használni tizedesjelnek. 21 éves programozó, akinek kimaradt az elmúlt 55 év?

Atzs · https://adatrendezo.hu 2015.09.12. 20:45:34

@Beer Monster: Márminthogy az Excel miért tizedesvesszőt használ? Erre gondolsz? Mert a felhasználók döntő többsége nem programozó. Ők a tizedesvesszőhöz vannak szokva. Úgy emlékszem, a franciáknál is tizedesvessző van, nem mi vagyunk az egyetlenek.
Nyilván elég alaposan meggondolták. Egyszerűbb lenne erőltetni a tizedespontot, mint néhány országban a CSV (comma separated value) állományban pontosvesszőt használni elválasztó karakternek. Nem mehetsz szembe a többséggel, ha a piacon akarsz maradni. Én legalábbis erre tippelek.
A VB az már programnyelv. Na ott tizedespont van. Teljesen azért nem szálltak el a fiúk.

Beer Monster 2015.09.13. 15:43:16

@Atzs: "Nyilván elég alaposan meggondolták"
:)

McZozo 2015.09.14. 09:45:58

@kőhányó: a te beallitasaid miatt lehet mert nalam tokeletesen megy vesszovel

McZozo 2015.09.14. 09:51:56

@nyu: Mikor txt bol mondjuk 500 000 sort importalsz be akkor eleg maceras manualisan aposztrifot tenni minden sor ele :)

Beer Monster 2015.09.14. 11:30:32

@McZozo: Na látod, ez az, amire a MS Office nem alkalmas. Ha használni akarod, akkor mást kell helyette.

nyu 2015.09.14. 17:42:50

@McZozo: Kb. két perc beszúrni az importálandó csv-t generáló programba, hogy f.WriteLine(s) helyett f.WriteLine("'"+s), ebből másfél perc, mire elindul a Visual Studio.

nyu 2015.09.14. 17:58:37

Vagy pl. ha Google Spreadsheetbe importálható csv-t generálsz, akkor ha macskakörmök közé teszed a stringet, akkor a stringben lévő sorvége karaktert nem kemény sortörésnek fogja nézni, hanem puha sortörésnek.
Így az egész string tartalma egy cellán belül több sorba kerül, mintha a cellán belül Alt-Entert nyomtál volna.

Ha nem teszed macskakörmök közé, akkor úgy értelmezi, hogy ott van a csv-ben az adott sor vége, és a string többi része a következő sor első cellájába fog kerülni.

Atzs · https://adatrendezo.hu 2015.09.15. 16:11:24

@nyu: Ehhez az kell, hogy nálad legyen a forráskód. Ez azért nem tipikus.

@McZozo: Meg kell nyitni valami szövegszerkesztőben, és a bekezdésvége jelet (sorvég karaktert) cserélni bekezdésvége jel+aposztrofra. Ha Wordben nyitjuk meg, akkor ^p cseréje ^p'-re. Csak nehogy docx-be mentsük vissza txt helyett. ;-)
Az első sort kell csak kézzel megcsinálni.