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
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:
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:
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:
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:
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:
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.
Ismerje meg, hogyan töltheti le és telepítheti a DAX Studio és a Tabular Editor 3 alkalmazást, és hogyan konfigurálhatja őket a LuckyTemplates és az Excel programban való használatra.
Ez a blog tartalmazza a Shape Map vizualizációt a LuckyTemplates térbeli elemzéséhez. Megmutatom, hogyan használhatja hatékonyan ezt a vizualizációt annak funkcióival és elemeivel.
Ebben az oktatóanyagban egy egyedülálló ötletet mutatok be a pénzügyi jelentésekkel kapcsolatban, amely az eredmények hozzárendelését jelenti a LuckyTemplates-en belüli táblázatsablonok előre meghatározásához.
Hozzon létre DAX-mértékeket a LuckyTemplates alkalmazásban meglévő mértékek vagy képletek használatával. Ezt nevezem mértékelágazási technikának.
Ebben a blogban fedezze fel a LuckyTemplates adatkészletet, a leghatékonyabb függvényhívást, amely M- és DAX-függvények ezreit juttatja a keze ügyébe.
A mai oktatóanyagban megosztok néhány adatmodellezési technikát a DAX-mérések jobb rendszerezéséhez a hatékonyabb munkafolyamat érdekében.
A LuckyTemplates egy nagyszerű eszköz a pénzügyi jelentésekhez. Itt található egy oktatóanyag, amely bemutatja, hogyan hozhat létre személyre szabott táblázatokat a LuckyTemplates pénzügyi irányítópultjához.
Ez az oktatóanyag megvitatja a Power Query nyelvi folyamatot, és azt, hogyan segíthet zökkenőmentes és hatékony adatjelentések létrehozásában.
Az egyik kedvenc technikámról fogok beszélni a LuckyTemplates egyéni ikonok körül, amely dinamikusan használja az egyéni ikonokat a LuckyTemplates vizualizációkban.
Ebben a blogban megmutatom, hogyan hozhat létre LuckyTemplates táblázatokat olyan képlet segítségével, amely egyesíti az UNION és a ROW függvényt.
