Eltérés keresése az Excelben – 5 egyszerű technika magyarázata

Ha nagy adatkészletekkel dolgozik a Microsoft Excel programban, elkerülhetetlen az adatok eltérései. Ezen ellentmondások azonosítása és kijavítása kritikus fontosságú a pontos jelentések és elemzések biztosításához.

A jó hírek? számos eszközt és technikát tartalmaz, amelyek segítenek megtalálni és megoldani ezeket a problémákat. Ebben a cikkben megismerheti a leghatékonyabb módszereket. Ez magában foglalja a szűrést, a feltételes formázást és a speciális képletek használatát.

Ha ezeket a technikákat beépíti a mindennapi munkafolyamatába, hatékonyabbá válik az adatok kezelése és elemzése. Végső soron jobb döntéshozatalhoz és projektjei jobb eredményéhez vezet.

Tartalomjegyzék

• Az eltérések megértése az Excelben
• 5 módszer az eltérések megtalálására az Excelben
• #1 – Eltérések keresése az Excel kézi ellenőrzésével
• #2 – Szűrő használata az Excelben az inkonzisztens adatok észlelésére és javítására
• 3# – Az eltérések kiemelése feltételes formázással
• #4 – Eltérések keresése a speciális Excel-függvényekkel
  • IF és IS függvények
  • VLOOKUP, HLOOKUP & XLOOKUP
  • MÉRKŐZÉS
• #5 – Excel-bővítmények használata eltérések észlelésére
• Hogyan hozhat létre adatérvényesítési szabályokat az eltérések megelőzése érdekében?
• Végső szavunk

Az eltérések megértése az Excelben

A sorok és oszlopok eltérései emberi beviteli hibákból, ismétlődő bejegyzésekből vagy az adatformázás szabálytalanságából adódhatnak.

Vessen egy pillantást a következő adatokra. Észreveheti, hogy Walter White és Wayne Cena nemét hibásan írják – Mael a Male helyett. Valójában ebben a forgatókönyvben egyszerű észrevenni és kijavítani a hibákat. De ez nem így van a több ezer sort és oszlopot tartalmazó táblázatoknál.

Az eltérések megtalálásához és kezeléséhez a következő szolgáltatásokat használhatja:

• Szűrők: Alkalmazzon szűrőket adott oszlopokra az egyedi értékek megtekintéséhez és az inkonzisztens adatok gyors észleléséhez.
• Feltételes formázás: Használjon formázási szabályokat a meghatározott feltételeknek megfelelő cellák vagy az oszlopok közötti nem egyező adatok kiemelésére.
• Speciális Excel-funkciók: Használjon olyan függvényeket, mint a MATCH, és hasonlítsa össze az adatkészleteket, és azonosítsa a köztük lévő eltéréseket.

Ha megérti, hogyan használhatja ezeket a funkciókat az Excelben, akkor hatékonyan észlelheti és kijavíthatja az adatkészletekben lévő eltéréseket.

5 módszer az eltérések megtalálására az Excelben

Öt fő módszerünk van az Excelben tapasztalható eltérések kezelésére:

1. Kézi áttekintés
2. Szűrő
3. Feltételes formázás
4. Speciális Excel-funkciók
5. Excel-bővítmények

Fussunk végig mindegyiken, hogy kitalálhassa az Önnek legmegfelelőbb módot.

#1 – Eltérések keresése az Excel kézi ellenőrzésével

Amikor a szolgáltatással dolgozik, a kézi áttekintés hasznos módszer lehet az adatok eltéréseinek azonosítására. Ez a folyamat megköveteli az adatkészletek alapos vizsgálatát az esetleges következetlenségek vagy hibák észlelése érdekében.

Íme néhány lépés, amelyet követhet a kézi ellenőrzés végrehajtásához:

• Kezdje azzal, hogy világosan megérti azokat az adatkészleteket, amelyekkel dolgozik. Ismerkedjen meg a sorokkal, oszlopokkal és formázással.
• Görgessen végig az adatokon, és ügyeljen az egyes cellákban megjelenő információkra. Használhat olyan eszközöket, mint a cellák kiemelése kitöltési színnel, szövegformázás vagy feltételes formázás az adatok könnyebb olvashatóságának érdekében.
• Hasonlítsa össze az egyik sort a másikkal vagy az egyik oszlopot a másikkal. Keressen nem egyező információkat, ismétlődő bejegyzéseket stb., amelyek pontatlanságot okozhatnak az Excel-lapokon.
• Az eltérések azonosításakor hozzon létre egy listát, vagy használja megjegyzéseket a dokumentálásához. A későbbiekben ezek fontos referenciaként szolgálhatnak a problémák megoldásához.

Amikor manuálisan ellenőrzi az adatokat egy Excel-táblázat eltérései miatt, a türelem a kulcs. Ez a folyamat időigényes. De ez jó kiindulópont a lehetséges hibák azonosításához.

#2 – Szűrő használata az Excelben az inkonzisztens adatok észlelésére és javítására

Az inkonzisztens adatok észlelésének egyik legegyszerűbb módja az Excel szűrőjének használata .

A szűrővel elkülönítjük a hibás adatokat a Nem oszlopban a következő adatkészletben:

1. Válassza a Rendezés és szűrés lehetőséget a Kezdőlap lapon.

2. Válassza a Szűrő lehetőséget – győződjön meg róla, hogy a kurzor az adatok tartományában van.

3. Kattintson a legördülő lista nyílára az inkonzisztens adatokat tartalmazó oszlopban.

4. Most szüntesse meg az összes megfelelő egyező adat kijelölését. A mi esetünkben a férfi . Ezután kattintson az OK gombra a módosítások mentéséhez.

5. Az Excel munkalap most csak a helytelen értékű sorokat jeleníti meg. A javításukhoz kattintson a Kezdőlap lap Keresés és kiválasztás elemére .

6. Kattintson a Csere gombra , amikor megjelenik a legördülő lista.

7. Írja be a helytelen értéket a Find what mezőbe , és a megfelelőt a Csere erre mezőbe . Ezután kattintson az Összes cseréje elemre .

8. Megjelenik egy párbeszédpanel, amely megerősíti a változtatásokat. A folytatáshoz kattintson az OK gombra .

3# – Az eltérések kiemelése feltételes formázással

Az oszlopok és sorok közötti különbségek azonosításának másik módja a feltételes formázás.

Hozzáférhet olyan szabályokhoz, mint a Cellák kiemelése, felső/alsó, adatsávok, színskálák és vasalókészletek. Ezenkívül létrehozhat új szabályt, és kezelheti vagy törölheti a korábbi szabályokat.

Egy mintatáblázatot használunk a feltételes formázás erejének bemutatására:

1. Válassza ki a táblázatot vagy a kívánt sorokat és oszlopokat. Az A oszlopot (Vállalat) és a B oszlopot (Ország) választjuk. Ezután válassza a Feltételes formázás lehetőséget a Kezdőlap lapon.

2. Válassza ki a szabály típusát, majd a szabályt. A kiemelt cellaszabályokat és az ismétlődő értékeket választjuk …

3. Most ki kell választanunk, hogy a Duplicate vagy Unique értékeket jelöljük ki, és a Fill Color & Font elemet a cellák formázásához. Ha elkészült, kattintson az OK gombra .

4. Az összes ismétlődő érték most ki van jelölve a Light Red Fill with Dark Red Text felirattal.

#4 – Eltérések keresése a speciális Excel-függvényekkel

IF és IS függvények

A cellák összehasonlításához és a különbségek azonosításához használhatja az IF és IS függvényeket.

Például a képletet használtuk az =IF(A2=B2,"MATCH","NOT MATCHED")A2 és B2 cellák összehasonlítására. És húztuk a képletet, hogy más cellákra is alkalmazzuk. Tehát az azonos cellák a MATCH értéket adják vissza , egyébként NEM egyezik .

Kombinálhatja az IF-et az IS-függvényekkel, például az ISNUMBER vagy az ISTEXT bizonyos adattípusok ellenőrzéséhez. Például a képlet a Számot =IF(ISNUMBER(B2), "Number", "NaN")adja vissza a c oszlopban, ha B2 numerikus, egyébként pedig NaN értéket.

VLOOKUP, HLOOKUP & XLOOKUP

A VLOOKUP (függőleges) és a HLOOKUP (vízszintes) függvények hasznosak két táblázat különböző oszlopaiban vagy soraiban lévő értékek összehasonlításakor. Ehhez használja a következő képletet: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) vagy =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]).

Cserélje le a lookup_value értéket a másik táblában megkeresni kívánt értékre, a table_array mezőt a második tábla cellatartományára, a col_index_num vagy row_index_num értéket annak az oszlopnak vagy sornak az indexszámára, amelyből értéket szeretne visszaadni, a [tartomány_keresése] pedig HAMIS a pontos egyezések vagy IGAZ hozzávetőleges egyezés esetén.

A következő oktatóanyagban az E oszlop VLOOKUP funkcióját használjuk az A oszlopban és a D oszlopban lévő alkalmazottak nevének összehasonlítására, hogy levonjuk a fizetésüket a B oszlopból, ha egyezést találunk.

Az XLOOKUP a VLOOKUP és a HLOOKUP frissített változata, amely elérhető az Excel 2021 és Excel 365 verziókban. Az XLOOKUP képlet a következő: =XLOOKUP(lookup_value, lookup_array, return_array,[if_not_found]).

Helyettesítse a lookup_value értéket a másik táblában keresni kívánt értékkel, a lookup_array a keresendő cellatartománnyal a második táblázatban, a return_array azzal a tömbbel, amelyből értéket szeretne visszaadni, és az [if_nout_found] értéket egy szöveges értékkel, ha nincs egyezés. értékek találhatók.

Íme egy gyors minta az XLOOKUP-ból ugyanazokkal a táblázatokkal:

MÉRKŐZÉS

A MATCH függvény két lista összehasonlítására is használható az eltérések tekintetében.

A MATCH függvény =MATCH(lookup_value, lookup_array, [match_type])egy adott elemet keres egy tartományban, és visszaadja az elem relatív pozícióját az adott tartományon belül.

Itt megtaláljuk a görögdinnyék helyzetét az A2:A8 tömbben a MATCH segítségével. A match_type értéket 0 -ként használjuk , hogy megtaláljuk az első értéket, amely pontosan egyenlő a lookup_value értékkel .

#5 – Excel-bővítmények használata eltérések észlelésére

Az Excel különféle bővítményeket és beépített eszközöket kínál, amelyek segítségével hatékonyabban észlelheti és elemezheti az eltéréseket. Ebben a részben ezek közül néhány hasznos bővítményről fogunk beszélni.

Először fontolja meg a Spreadsheet Inquire bővítmény használatát. Ez segíthet két munkafüzet összehasonlításában, és cellánként kiemelheti a különbségeket. A Spreadsheet Inquire bővítmény engedélyezése:

1. Kattintson a Fájl fülre.

2. Válassza a Beállítások lehetőséget , majd kattintson a Bővítmények kategóriára.

3. A Kezelés mezőben válassza ki a COM-bővítmények elemet , majd kattintson az Ugrás gombra .

4. Jelölje be a Spreadsheet Inquire jelölőnégyzetet, majd kattintson az OK gombra .

5. Miután engedélyezte, lépjen az Érdeklődés lapra a Fájlok összehasonlítása parancs használatához .

Egy másik hasznos eszköz az eltérések észlelésére az Analysis ToolPak . Ez a bővítmény fejlett statisztikai funkciókat és adatelemző eszközöket biztosít, amelyek hasznosak lehetnek az eltérések elemzéséhez.

Az Analysis ToolPak engedélyezéséhez kövesse ugyanazokat a lépéseket, mint a Spreadsheet Inquire bővítmény engedélyezésekor. De válassza ki az Excel-bővítményeket a Kezelés mezőben, és jelölje be az Analysis ToolPak négyzetet.

Összefoglalva, az Excel különféle bővítményeket kínál az adatok eltéréseinek észleléséhez és kezeléséhez. Növelheti az adatok pontosságát, ha megismeri ezeket az eszközöket és képleteket.

Hogyan hozhat létre adatérvényesítési szabályokat az eltérések megelőzése érdekében?

Ha az Excelben adatérvényesítési szabályokat szeretne létrehozni, amelyek segíthetnek elkerülni az eltéréseket, kövesse az alábbi lépéseket:

1. Válassza ki a korlátozni kívánt cellákat. Ez lehet egyetlen cella, cellák tartománya vagy egy egész oszlop.

2. Az eszköztáron lépjen az Adatok fülre, és kattintson az Adatok érvényesítése gombra (két vízszintes négyzet, zöld pipa és egy piros áthúzott kör jelöli).

3. Az Adatellenőrzés ablakban győződjön meg arról, hogy a Beállítások lapon van. Itt lehetősége nyílik az érvényesítési kritériumok meghatározására az Ön igényei alapján.

Az Excelben különféle típusú érvényesítési feltételek állnak rendelkezésre, amelyek közül néhány a következőket tartalmazza:

• Egész szám – csak egész számokat engedélyez egy meghatározott tartományon belül.
• Tizedes – csak decimális számokat engedélyez egy meghatározott tartományon belül.
• Lista – a bevitelt az elfogadható értékek előre meghatározott listájára korlátozza.
• Dátum – egy adott dátumtartományon belüli dátumokat igényel.
• Idő – csak időket fogad el egy adott időtartományon belül.
• Szöveg hossza – korlátozza a beírt szöveg hosszát.
• Egyéni – lehetővé teszi egyéni érvényesítési szabályok létrehozását Excel-képletek használatával.

A megfelelő érvényesítési feltételek kiválasztása után szükség szerint adja meg a paramétereket. Például, ha a Teljes számot választotta , akkor a Minimum és Maximum értékeket egy adattartomány -ellenőrzővel kell beállítania, például között, egyenlő, kisebb, mint stb.

Az érvényesítési feltételek mellett egyéni hibaüzeneteket és beviteli tippeket is beállíthat, hogy segítse a felhasználókat az érvényesítési szabályok megértésében és betartásában. Ehhez váltson az Üzenetbevitel és a Hibafigyelmeztetés fülekre a kívánt üzenetek megadásához.

Az adatellenőrzési szabályok bevezetésével jelentősen csökkenti az adatok inkonzisztenciáinak valószínűségét, és javítja az Excel-munkalapok általános pontosságát.

Végső szavunk

Ebből a cikkből különböző módszereket ismerhet meg az Excel-adatok eltéréseinek megkeresésére és kezelésére. Foglaljuk össze a legfontosabb stratégiákat:

• Az oszlopokban található Szűrő legördülő menük használata egyedi értékek és inkonzisztens adatok azonosítására.
• Feltételes formázás alkalmazása a kijelölt cellákban az eltérések kiemelésére egy csomó szabálysablon segítségével.
• Speciális Excel-funkciók, például MATCH , VLOOKUP , XLOOKUP stb. függvények alkalmazása két listában vagy táblázatban található adatok keresztellenőrzésére.
• Excel-bővítmények használata eltérések észlelésére.
• Adatellenőrzési szabályok beállítása az adatokban előforduló hibák megelőzésére

Ezekkel a technikákkal az Excel készségkészletében az adatok pontosabbak és megbízhatóbbak lesznek.

Ne felejtse el rendszeresen gyakorolni ezeket a módszereket, hogy hatékonyabban észlelje az eltéréseket a táblázatokban.

Továbbra is finomítsa készségeit, és regisztráljon a webhelyünkre, és folytassa az új funkciók és funkciók felfedezését, hogy még jobban jártassá váljon az Excelben.