10 szuper ügyes módszer az adatok tisztítására Excel -táblázatokban

Videó megtekintése - 10 módszer az adatok tisztítására Excelben

Az adatok képezik az Excelben végzett elemzések gerincét. És ami az adatokat illeti, rengeteg dolog hibázhat - legyen szó szerkezetről, elhelyezésről, formázásról, extra helyekről stb.

Ebben a blogbejegyzésben 10 egyszerű módszert mutatok be az adatok Excelben történő tisztítására.

#1 Szabaduljon meg az extra helyektől

A további terek fájdalmasan nehezen észlelhetők. Bár valahogy észreveheti a szavak vagy számok közötti extra szóközöket, a záró szóközök nem is láthatók. Itt van egy ügyes módja annak, hogy megszabaduljon ezektől az extra helyektől - Használja a TRIM funkciót.

Szintaxis: TRIM (szöveg)

Az Excel TRIM függvény a cellahivatkozást (vagy szöveget) használja bemenetként. Eltávolítja a kezdő és a záró szóközöket, valamint a szavak közötti további szóközöket (az egyes szóközök kivételével).

#2 Válassza ki és kezelje az összes üres cellát

Az üres sejtek pusztítást okozhatnak, ha nem kezelik előzetesen. Gyakran szembesülök a jelentések/irányítópultok létrehozásához használt adatkészlet üres celláival.

Érdemes minden üres cellát "0" -val vagy "Nem elérhető" -vel kitölteni, vagy egyszerűen csak kiemelni. Hatalmas adatkészlet esetén a manuális elvégzés órákig is eltarthat. Szerencsére van egy módja annak, hogy egyszerre kiválaszthatja az összes üres cellát.

1. Válassza ki a teljes adatkészletet
2. Nyomja meg az F5 billentyűt (ez megnyitja az Ugrás párbeszédpanelt)
3. Kattintson a Speciális… gombra (bal alsó sarokban). Ekkor megnyílik az Ugrás a speciális párbeszédpanel
   
4. Válassza az Üres lehetőséget, majd kattintson az OK gombra
   

Ezzel kiválasztja az adatkészlet összes üres celláját. Ha mindegyik cellába 0 -t vagy Nem elérhetőt szeretne beírni, csak írja be és nyomja meg a gombot Control + Enter (ne feledje, ha csak az enter billentyűt nyomja meg, az érték csak az aktív cellába kerül beillesztésre).

#3 A szövegként tárolt számok konvertálása számokká

Néha, amikor szöveges fájlokból vagy külső adatbázisokból importál adatokat, a számok szövegként kerülnek tárolásra. Ezenkívül néhány embernek szokása, hogy egy szám előtt aposztrófot (’) használ, hogy szöveggé tegye. Ez komoly problémákat okozhat, ha ezeket a cellákat használja a számításokban. Íme egy bolondbiztos módszer a szövegként tárolt számok számokká alakítására.

1. Írjon be minden üres cellába 1 -et
2. Válassza ki azt a cellát, ahová az 1 -et beírta, majd nyomja meg a Control + C billentyűkombinációt
3. Válassza ki azt a cellát/tartományt, amelyet számokká szeretne alakítani
4. Válassza a Beillesztés -> Speciális beillesztés lehetőséget (Billentyűparancs - Alt + E + S)
5. A Speciális párbeszédpanel beillesztése mezőben válassza a Szorzás lehetőséget (a műveletek kategóriájában)
   
6. Kattintson az OK gombra. Ez az összes szöveges formátumú számot számokká alakítja át.

A speciális műveletek beillesztésével sokkal többet tehet. Íme számos más módja annak, hogyan lehet szaporítani Excelben a Paste Special használatával.

#4 - Távolítsa el az ismétlődéseket

Az ismétlődő adatokkal két dolgot tehet - Jelölje ki vagy Töröld.

• Ismétlődő adatok kiemelése:
  • Válassza ki az adatokat, és lépjen a Kezdőlapra -> Feltételes formázás -> A cellák szabályainak kiemelése -> Ismétlődő értékek.
  • Adja meg a formázást, és az összes ismétlődő érték kiemelésre kerül.
    
• Ismétlődések törlése az adatokból:
  • Válassza ki az adatokat, és lépjen az Adatok -> Másolatok eltávolítása elemre.
  • Ha az adatok fejlécekkel rendelkeznek, győződjön meg arról, hogy a jobb felső sarokban lévő jelölőnégyzet be van jelölve.
  • Válassza ki azokat az oszlopokat, amelyekből az ismétlődéseket el szeretné távolítani, majd kattintson az OK gombra.
    

Ez eltávolítja az ismétlődő értékeket a listából. Ha azt szeretné, hogy az eredeti lista sértetlen legyen, másolja be az adatokat egy másik helyre, majd tegye ezt.

Összefüggő: A végső útmutató az ismétlődések megkereséséhez és eltávolításához az Excelben.

#5 Jelölje ki a hibákat

Kétféleképpen emelheti ki az Excelben az adatok hibáit:

Feltételes formázás használata

1. Válassza ki a teljes adathalmazt
2. Lépjen a Főoldalra -> Feltételes formázás -> Új szabály
3. Az Új formázási szabály párbeszédablakban válassza a „Csak tartalmazni kívánt cellák formázása” lehetőséget.
4. A szabályleírásban válassza a Hiba lehetőséget a legördülő menüből
5. Állítsa be a formátumot, majd kattintson az OK gombra. Ez kiemeli a kiválasztott adatkészlet bármely hibaértékét
   

A Go to Special használata

1. Válassza ki a teljes adatkészletet
2. Nyomja meg az F5 billentyűt (ez megnyitja az Ugrás a párbeszédablakhoz ablakot)
3. Kattintson a bal alsó sarokban található Speciális gombra
4. Válassza a Képletek lehetőséget, és törölje a jelölést az összes lehetőség közül, kivéve a Hibákat
   

Ez kijelöli az összes cellát, amelyben hiba van. Most manuálisan kiemelheti ezeket, törölheti vagy bármit beírhat.

#6 Módosítsa a szöveget kis/nagy/megfelelő kisbetűre

Amikor örököl egy munkafüzetet, vagy szöveges fájlokból importál adatokat, gyakran a nevek vagy a címek nem konzisztensek. Néha az összes szöveg kis/nagybetűs lehet, vagy a kettő keveréke. A három funkció használatával könnyedén konzisztenssé teheti mindezt:

LOWER () - Az összes szöveget kisbetűvé alakítja.
FELSŐ () - Az összes szöveget nagybetűvé alakítja.
PROPER () - Az összes szöveget megfelelő kis- és nagybetűvé alakítja.

#7 Adatok elemzése szöveg oszlopba használatával

Amikor adatokat szerez be egy adatbázisból, vagy importál egy szövegfájlból, előfordulhat, hogy az összes szöveg egy cellában van szűkítve. Ezt a szöveget több cellába is elemezheti az Excel Szöveg oszlop funkcióval.

1. Válassza ki az elemzni kívánt adatokat/szöveget
2. Ugrás az adatokhoz -> Szöveg oszlopba (ez megnyitja a Szöveg oszlopok varázslót)
   • 1. lépés: Válassza ki az adattípust (válassza az Elhatárolt lehetőséget, ha az adatok nincsenek egyenlő távolságban, és olyan karakterekkel vannak elválasztva, mint például vessző, kötőjel, pont …). Kattintson a Tovább gombra
     
   • 2. lépés: Válassza a Delimiter (az adatokat elválasztó karakter) lehetőséget. Az Egyéb opcióval előre definiált határolót vagy bármi mást választhat
     
   • 3. lépés: Válassza ki az adatformátumot. Válassza ki a célcellát is. Ha a célcellát nem választja ki, az aktuális cella felülírásra kerül
     

Összefüggő: Bontsa ki a felhasználónevet az e -mail azonosítóból szöveg segítségével az oszlopba.

#8 Helyesírás -ellenőrzés

Semmi sem csökkenti munkája hitelességét, mint egy helyesírási hiba.

Használja az F7 billentyűparancsot az adatkészlet helyesírás -ellenőrzéséhez.

Itt található egy részletes bemutató a Helyesírás -ellenőrzés használatáról Excelben.

#9 Az összes formázás törlése

Munkám során több adatbázist használtam az adatok Excel -ben történő megszerzésére. Minden adatbázisnak saját adatformázása volt. Ha minden adat a helyén van, a következőképpen törölheti az összes formázást egyszerre:

1. Válassza ki az adatkészletet
2. Lépjen a Kezdőlapra -> Törlés -> Formátumok törlése
   

Hasonlóképpen csak a megjegyzéseket, hiperhivatkozásokat vagy tartalmakat is törölheti.

#10 Használja a Keresés és csere lehetőséget az adatok tisztításához az Excelben

A keresés és csere elengedhetetlen az adattisztításhoz. Például kiválaszthatja és eltávolíthatja az összes nullát, megváltoztathatja a képletekben szereplő hivatkozásokat, megkeresheti és módosíthatja a formázást stb.

További információ arról, hogy a Keresés és csere hogyan használható az adatok tisztítására.

Ez a legjobb 10 technikám az adatok Excelben történő tisztítására. Ha további technikákat szeretne megtanulni, itt található az MS Excel csapat útmutatója - Tiszta adatok az Excelben.

Ha további technikákat használ, ossza meg velünk a megjegyzések részben!