Telefonszámunk: 1-472-0679

Szerkesztés

A villámkitöltés elképesztő eszköz

A legutóbbi cikket a szövegdaraboló, más néven szövegből oszlopok (Text to columns) eszközről írtam. Az Excel 2013-as verziójában megjelent egy hihetetlenül hatékony parancs, ami az amúgy is szuper darabolási lehetőségeket tovább fejleszti, és ráadásul elképesztő módon fel is gyorsítja. A villámkitöltés (FlashFill) A villámkitöltő célja szintén az, […]

Tovább...

Speciális átalakítások szövegből oszlopok funkcióval

Egy ideje már terveztem, most eljött az ideje, hogy néhány kevésbé ismert lehetőséget bemutassak az Excel Szövegből oszlopok (Text to Columns) másnéven szövegdaraboló eszközéről. Feltételezem, hogy az olvasó az eredeti funkcionalitást már ismeri, azaz tudja, hogy ez az eszköz eredetileg arra aló, hogy egy szöveget egy elválasztó jel […]

Tovább...

Több munkalap szerkesztése egyszerre

Gyakran találkozom olyan munkafüzetekkel, ahol a munkalapok szerkezete megegyezik, és csak tartalmukban, esetleg adatmennyiség tekintetében térnek el egymástól. Tipikus példaként említhető, amikor minden hónapra van egy munkalap, de találkoztam már személyekhez tartozóan elkülönített lapokkal, és termékenként, partnerenként, vagy bármilyen kategória alapján különválasztott lapok is lehetségesek. Ilyen esetekben […]

Tovább...

A TRANSZPONÁLÁS (TRANSPOSE) függvény

A vágólap elforgatott beillesztés funkcióját mindenki ismeri. Ami a forrás tartományban vízszintesen volt, az a céltartományban függőlegesen lesz vagy fordítva. Az Excel különböző verzióiban más-más módokon lehet közvetlenül elérni, de az Irányított beillesztés párbeszédben már régóta ugyanott találjuk. Az a probléma, hogy ezzel az elforgatással a célhely […]

Tovább...

Hogyan szabaduljunk meg a kezdő aposztrófoktól?

Főleg importált adatok esetén fordul elő gyakran, hogy egy oszlopban minden adat aposztróffal kezdődik. Ezt arra használják, hogy mindenképpen szöveges formátumú legyen az adott oszlop. Extrém esetben az üres cellák is tartalmaznak legalább egy aposztrófot. Megszabadulni ezektől nem is olyan egyszerű, de a most ismertetett műveletsorral megoldható. Alap […]

Tovább...

Táblázat rendezése oszlopok alapján

A táblázatok ugyebár sorokból és oszlopokból állnak. Gyakori művelet, amikor a táblázat sorait egy vagy akár több oszlop alapján sorba rendezzük. Például név szerint vagy érték szerint növekvő vagy csökkenő sorba.

Az erre szolgáló gombokat is mindenki ismeri.

rendezes

Igen ám, de mi van akkor, ha a táblázat oszlopait szeretnénk rendezni valamelyik sorban lévő értékek alapján? A következő példában a nevekhez tartozó oszlopokat szeretnénk névsor szerint rendezni úgy, hogy a napok a helyükön maradjanak.

rendezes_elott

Rendezés előtt

rendezes utan

rendezés után

Megoldás

A rendezés előtt be kell állítani, hogy nem oszlop szerint (fentről lefelé) hanem sorok szerint (balról jobbra) szeretnénk rendezni. Ezt a rendezés párbeszédablakban lévő “Options…” (Beállítások) gomb mögött tudjuk megtenni.

  • Data -> Sort -> Options -> Left to Right
  • Adatok ->Rendezés ->Beállítások ->Balról jobbra

Oszlopok rendezése

Ezután jelöljük ki a táblázatot az első oszlop nélkül, hiszen a hét napjait ott szeretnénk hagyni.

Most jön a rendezés végrehajtása. (Sort / Rendezés gomb)

rendezes_parbeszed

A rendezés beállítása sor alapján

Ezt az Excel korábbi verzióiban is meg lehetett csinálni. A rendezés párbeszéd bal alsó sarkában ott van az ‘Options…’ gomb

Mindez működés közben

  • Oszlopok rendezése sorok szerint

Tovább...

Oszlopok sorrendjének változtatása villámgyorsan – szűréssel!?

Íme egy újabb gyöngyszem.  Július legvégén volt egy Excel VBA kurzusom, ahol arra szoktam buzdítani a résztvevőket, hogy bátran hozzanak saját problémákat, és ha belefér, megoldjuk őket, vagy legalább hozok rá elvi vagy gyakorlati megoldást a tanfolyamon. Milu azzal állt elő, hogy rendszeresen kap egy bizonyos riportot, ami mindig ugyanazokat az oszlopokat tartalmazza, de gyakran eltérő oszlop sorrendben. Mivel az lenne a feladat, hogy a kapott riportokat egy eredményfájlba gyűjtse, aminek az oszlopsorrendje adott,a másolás előtt  az aktuálisan kapott riportot a gyűjtőfájl oszlopsorrendjéhez kell igazítani. Mivel ismétlődő a feladat, jó lenne lemakrózni, hogy automatikusan futhasson. Kell tehát találni egy olyan megoldást, ami függetlenül az aktuális oszlopsorrendtől, mindig egy előre rögzített sorrend alapján elkészít egy másolatot, majd azt a gyűjtőfájl végére fűzi.

Minta oszlopsorrend

Minta táblázat

Hibas-sorrend

Hibás-sorrend

Ebben az anyagban kiemeltem az oszlopsorrend változtatására a lehetséges módszereket.

Sorrend változtatás vontatással vagy vágólappal

  • Kivágás -> Kivágott cellák beszúrása (Cut ->Paste Cut cells) vagy
  • Shift+vontatás

Probléma ha makrózni akarjuk: Nem tudjuk előre azt, hogy mely oszlopokat és hová kell tenni a javítás során.

Módosítás az oszlopok rendezésével

Mivel az Excel a 2007-es verziótól képes valamely sor alapján rendezni az oszlopokat, megoldás lehet, ha a helyes sorrendet az aktuális oszlopnevek fölé képezzük (kézzel vagy képlettel), majd a kapott számok alapján átrendezzük az oszlopokat.

oszlop-sorszamok

Helyes oszlop-sorszámok a táblázat tetején

  • Data -> Sort -> Options -> Left to Right
  • Adatok ->Rendezés ->Beállítások ->Balról jobbra
Oszlopok rendezése

Oszlopok rendezése

Sor-szerinti-rendezes

Sor-szerinti-rendezes

A helyes oszlop sorszámokat kézzel vagy a Hol.Van (Match) függvénnyel állíthatjuk elő. A Videóban megmutatom ezt a megoldást is.

Probléma ha makrózni akarjuk: Ha az oszlopnevek nem egyeznek meg az aktuális riportban a rögzített oszlopnevekkel. Ebben az esetben a Match függvény nem fogja megtalálni a nevet, és a helyes sorszám helyett hibát ad vissza.

Módosítás az irányított szűrő (Advanced Filter) segítségével

Ezt a megoldást Márti gyűjtőmunkájának köszönhetjük. Ebben az esetben az aktuális riport táblázat mellé vagy alá, akár másik munkalapra feltesszük a helyes sorrendű fejlécet, majd alkalmazzuk a speciális vagy irányított szűrőt.

  1. Kijelöljük vagy aktiváljuk a Riport táblázatot
  2. Elindítjuk az irányított szűrőt
    Iranyitott-szuro
  1. Beállítjuk az alábbiak szerint, és az eredmény a jó fejléc szerinti oszlopsorrend lesz
    Iranyitott szuro beallítas
Irányitott szűrő beállítás

Ha nem adunk meg minden oszlopot, akkor csak a megadottakat fogja átmásolni!

Probléma ha makrózni akarjuk: Ha az aktuális riportban lévő oszlopnevek bármelyike nem egyezik meg a rögzített oszlopnévvel. Ebben az esetben nem fog működni.

Letölthető minta dokumentum: oszlop_sorrend_modositas.xlsx

Mindez működés közben

  • Oszlopsorrend változtatása

Tovább...

Adatok másolása szűrt táblázatba

Gyakran látom a különböző Excel fórumokon azt a kérdést, hogy hogyan lehet adatokat beilleszteni egy olyan táblázatba, amelynek vannak elrejtett sorai? Ez leginkább szűrés alkalmával fordul elő. Tehát azt szeretnénk, hogy a szűrt táblázat egyik oszlopának egy kijelölt tartományába, egy másik tartományból másolhassunk adatokat.

A forrás és a céltartomány ugyanabban a táblázatban van

Sajnos erre a kérdésre nem is olyan egyszerű a válasz (de legalább van), mert az Excel nem igazán van felkészítve ilyesmi feladatra. Nézzük mi történik akkor, ha megpróbáljuk a szokásos beillesztést (CP=Copy-Paste)

  1. Szűrjük a táblát
  2. Kijelöljük a másolandó tartományt ->Másolás(Ctrl+C) Ezt tehetjük a szokásos módon, vagy a speciális kijelölés->Csak a látható cellák (Visible cells only)
  3. Kijelöljük a céltartományt (lényeges, hogy mérete megegyezzen a forráséval)
  4. Beillesztés (Ctrl+V)

Az eredmény

hiba1

Ugyanezt az üzenetet kapjuk akkor is, ha megpróbáljuk átvontatni a forrástartományt a céltartományra. Ha nem jelöljük előre ki a céltartományt, csak az első celláját, akkor pedig az történik, hogy a beillesztés a nem látható (szűrt) cellákba is megtörténik. ezt pedig semmiképpen nem szeretnénk.

hiba2

A megoldás

Szűrt állapotban a céltartomány celláiból át kell hivatkozni a forrástartomány celláira, majd érték beillesztéssel megszüntetni  a képletet. Ez csak akkor működik biztonságosan, ha ugyanabban a táblázatban van a két tartomány, és így biztosított, hogy közösek az elrejtett sorok.

megoldas

A forrás és céltartomány különböző táblázatban van

Ebben az esetben az sem biztos, hogy a forrás tartomány is szűrt, de ha igen, az valószínűtlen, hogy ugyanazok a sorok lesznek rejtettek, mint a céltartomány esetén. Erre a helyzetre az Excel felület nem kínál megoldást, tehát makrót (VBA kódot) kell készíteni. Ebben első lépésként meg kell vizsgálni, hogy a forrás és a céltartomány látható (nem szűrt) sorainak (és oszlopainak)  száma megegyezik-e, majd soronként másolhatunk úgy, hogy a céltartományban csak a látható sorokba illesztjük a forrás tartomány aktuális látható sorát. A Videóban látható mindez kicsit bővebben.

Következő rész: A makrós megoldás

Letölthető munkafüzet: beillesztes-szurt-tartomanyba

  • Nézzük, hogy lehet adatokat másolni csak a látható cellákba

Tovább...

Adott értéket tartalmazó sorok törlése

Ha egy táblázatban különböző oszlopokban többször is előforduló értéket keresünk, és az adott sorokat törölni szeretnénk akkor a következő, egyszerű eljárást használhatjuk:

  • Jelöljük ki a táblázatot vagy tartományt (Ctrl+A)
  • Keressük meg az adott érték (Ctrl+F), és a keresési párbeszédben gyűjtsük ki az összes találatot (Find All / Listába mind)
  • Zárjuk be a párbeszédablakot. A megtalált értékek kijelölve maradnak.
  • Ctrl + – (kötőjel vagy mínusz jel), ez előhozza a cella törlése párbeszédet
  • Egész sorok törlése
    Teljes-sor-torles

Ha adattáblává alakítjuk a tartományunkat, akkor a “Ctrl + -” parancs azonnal teljes sorokat töröl!

Ezt a módszert akkor is alkalmazhatjuk, ha a keresett cellában speciális érték van, pl. hibaüzenet, vagy képlet, esetleg feltételes formázás vagy beviteli szabály. De ugyanígy törölhetjük az üres cellákat tartalmazó sorokat is.

Ha csak egy oszlopban kellene keresnünk, akkor az autoszűrővel leszűrve és a kapott sorokat törölve is célt érhetünk, bár ez a módszer ebben az esetben is gyorsabb, illetve a keresés funkció miatt kicsit rugalmasabb, mivel nem csak pontos egyezésre tudunk keresni.

Ha pedig a keresendő cellák különböző oszlopokban is előfordulhatnak, akkor mindenképpen ez a nyerő.

A Video-ban mindez működés közben is látható

  • Sorok törlése cellatartalom szerint

Tovább...

Hibaüzenetek lecserélése – Hibakezelés

Ha adott egy olyan Excel tábla, amelyben a képletek hibaüzeneteket adnak vissza, felmerülhet az igény a hibaüzenetek megváltoztatására, törlésére.

Ezzel kapcsolatban már volt egy korábbi anyag, amelyben az IFERROR (HAHIBA) függvényt mutattuk be, ami pont erre való. De mit tehetünk akkor, ha már adott a hibákat tartalmazó táblázat, és a képletekhez nem nyúlhatunk?

Segéd oszlopban, külön képlettel vizsgáljuk a hibát, és ha fennáll, egyéb értéket adunk, különben megtartjuk az eredeti eredményt. A képen látható példában a hibaüzenetet nullára cseréljük.

A képlet angol verzióhoz

=IF(ISERROR(B2);0;B2)

A képet magyar verzióhoz

=HA(HIBÁS(B2);0;B2)

Ezt a megoldást akkor érdemes használni, ha a tráblázathoz új sorok is adódhatnak, és a hibakezelés képletet csak le kell húzni és kész.

Hibaüzenetek cseréje speciális kijelöléssel

  1. Válasszuk a speciális (irányított) kijelölést: F5->Special… (Irányított…)
  2. Jelöljük be azt, hogy képleteket, azon belül hibákat keresünk. Ez kijelöli az összes hibaüzenetet, ezek közül az elsőben lesz az aktív cella
  3. Gépeljük be azt az értéket, amit a hibák helyett látni szeretnénk, pl.: nulla
  4. Ctrl+ENTER-rel zárjuk le, ami az össze kijelölt cellába ugyanazt az értéket viszi be.

Mindez animációban

Tovább...