A Microsoft Excel számos képessége közé tartozik az adatok két listájának összehasonlítása, a listák közötti egyezések azonosítása és azon elemek azonosítása, amelyek csak egy listában találhatók. Ez akkor hasznos, ha pénzügyi nyilvántartásokat hasonlít össze, vagy ellenőrzi, hogy egy adott név szerepel -e az adatbázisban. Használhatja a MATCH funkciót az egyező vagy nem egyező rekordok azonosítására és megjelölésére, vagy használhatja a kondicionáló formázást a COUNTIF funkcióval. A következő lépésekből megtudhatja, hogyan használhatja mindegyiket az adatok egyeztetéséhez.
Lépések
1. módszer a 2 -ből: Rekordok azonosítása a MATCH funkcióval
1. lépés: Másolja az adatlistákat egyetlen munkalapra
Az Excel több munkalappal, vagy több munkafüzettel is dolgozhat, de könnyebben összehasonlíthatja a listákat, ha egyetlen munkalapra másolja a listájukat.
2. lépés. Adjon minden egyes listaelemnek egyedi azonosítót
Ha a két listája nem azonos az azonosításukhoz, előfordulhat, hogy hozzá kell adnia egy további oszlopot minden adatlistához, amely azonosítja az adott elemet az Excelben, hogy lássa, hogy egy adott lista eleme kapcsolódik -e egy elemhez a másik listában. Ennek az azonosítónak a jellege attól függ, hogy milyen adatokat szeretne egyeztetni. Minden oszloplistához szüksége lesz egy azonosítóra.
- Az adott időszakhoz kapcsolódó pénzügyi adatok, például az adónyilvántartások esetében ez lehet az eszköz leírása, az eszköz beszerzésének dátuma vagy mindkettő. Bizonyos esetekben a bejegyzést kódszámmal lehet azonosítani; ha azonban nem ugyanazt a rendszert használják mindkét listához, akkor ez az azonosító olyan egyezéseket hozhat létre, ahol nincsenek, vagy figyelmen kívül hagyja az egyezéseket.
- Bizonyos esetekben vehet ki elemeket az egyik listából, és egyesítheti őket egy másik listából származó elemekkel, hogy létrehozzon egy azonosítót, például egy fizikai eszköz leírását és a vásárlás évét. Egy ilyen azonosító létrehozásához két vagy több cella adatait összefűzi (hozzáadja, egyesíti) az ampersand (&) segítségével. Ha az F3 cella elemleírását a G3 cella dátumával, szóközzel elválasztva szeretné kombinálni, írja be a '= F3 & "" & G3' képletet az adott sor másik cellájába, például az E3 sorba. Ha csak az évet szeretné szerepeltetni az azonosítóban (mivel az egyik lista a teljes dátumot használja, a másik pedig csak az éveket), akkor az ÉV függvényt úgy kell megadnia, hogy a '= F3 & "" & YEAR (G3)' mezőt írja be helyette az E3 cellába. (Ne tegye bele az egyes idézeteket; ezek csak a példát jelzik.)
- Miután létrehozta a képletet, átmásolhatja az azonosító oszlop összes többi cellájába: válassza ki a képletet tartalmazó cellát, és húzza a kitöltő fogantyút az oszlop többi cellájára, ahová a képletet másolni szeretné. Amikor elengedi az egérgombot, minden cella, amelyre áthúzta, feltöltődik a képlettel, és a cellahivatkozások az adott sor megfelelő celláihoz vannak igazítva.
3. lépés Ha lehetséges, szabványosítsa az adatokat
Míg az elme felismeri, hogy az "Inc." és az "Incorporated" ugyanazt jelenti, az Excel nem, hacsak nem formázza át egyik vagy másik szót. Hasonlóképpen, a 11, 950 és 11, 999,95 dollár értékeket is elég közel tarthatja egymáshoz, de az Excel nem, hacsak nem mondja meg.
- Néhány rövidítéssel foglalkozhat, például a "Co" a "Company" és az "Inc" a "Incorporated" használatával, a LEFT string függvény használatával a további karakterek csonkításához. Más rövidítések, például az "Assn" a "Association" lehet a legjobb adatbeviteli stílus útmutató létrehozásával, majd egy program írásával, amely megkeresi és kijavítja a nem megfelelő formátumokat.
- Azon számsorok esetében, mint például az irányítószámok, ahol egyes bejegyzések tartalmazzák a ZIP+4 utótagot, míg mások nem, ismét használhatja a BALRA karakterlánc funkciót az elsődleges irányítószámok felismerésére és illesztésére. Ha azt szeretné, hogy az Excel felismerje a közeli, de nem azonos numerikus értékeket, akkor a KERES funkció segítségével a záróértékeket ugyanarra a számra kerekíti és illeszti.
- Az extra szóközök, például az egy helyett két szóköz beírása a szavak közé, eltávolíthatók a TRIM funkció használatával.
4. lépés: Hozzon létre oszlopokat az összehasonlítási képlethez
Ahogy oszlopokat kellett létrehoznia a listaazonosítókhoz, úgy oszlopokat kell létrehoznia ahhoz a képlethez, amely elvégzi az összehasonlítást. Minden listához egy oszlopra lesz szüksége.
Ezeket az oszlopokat például "Hiányzik?"
Lépés 5. Írja be az összehasonlító képletet minden cellába
Az összehasonlítási képlethez a MATCH függvényt fogja használni, amely egy másik Excel függvénybe, az ISNA -ba van ágyazva.
- A képlet "= ISNA (MATCH (G3, $ L $ 3: $ L $ 14, FALSE))", ahol az első lista azonosító oszlopának celláját összehasonlítják a második lista minden azonosítójával a hátha megfelel az egyiknek. Ha nem egyezik, hiányzik egy rekord, és az "IGAZ" szó jelenik meg ebben a cellában. Ha megegyezik, a rekord jelen van, és megjelenik a "FALSE" szó. (A képlet megadásakor ne adja meg a mellékelt idézőjeleket.)
- A képletet ugyanúgy másolhatja az oszlop többi cellájába, mint a cellaazonosító képletet. Ebben az esetben csak az azonosító cella cellahivatkozása változik, mivel a dollárjeleket a sor- és oszlophivatkozások elé helyezve a második cellaazonosítók listájában az abszolút hivatkozásokká válnak.
- Másolhatja az első lista összehasonlítási képletét a második lista oszlopának első cellájába. Ezután módosítania kell a cellahivatkozásokat úgy, hogy a "G3" helyére a második lista első azonosító cellájának hivatkozása kerüljön, a "$ L $ 3: $ L $ 14" pedig a lista első és utolsó azonosító cellájára. második lista. (Hagyja békén a dollárjeleket és a kettőspontot.) Ezt a szerkesztett képletet ezután átmásolhatja a második lista összehasonlító sorának többi cellájába.
Lépés 6. Rendezze a listákat, hogy szükség esetén könnyebben lássa a nem egyező értékeket
Ha a listák nagyok, előfordulhat, hogy rendezni kell őket, hogy az összes nem egyező értéket összeadja. Az alábbi allépésekben található utasítások a képleteket értékekre konvertálják, hogy elkerüljék az újraszámítási hibákat, és ha nagyok a listák, akkor elkerülhető a hosszú újraszámítási idő.
- A kijelöléshez húzza az egeret a lista összes cellájára.
- Válassza a Másolás lehetőséget az Excel 2003 Szerkesztés menüjében, vagy az Excel 2007 vagy 2010 Kezdőlap szalag Vágólap csoportjában.
- Válassza a Speciális beillesztés lehetőséget az Excel 2003 Szerkesztés menüjében vagy a Beillesztés legördülő menüben az Excel 2007 vagy 2010s kezdőlap szalag Vágólap csoportjában.
- Válassza az "Értékek" lehetőséget a Beillesztés másként listából a Speciális beillesztés párbeszédpanelen. Kattintson az OK gombra a párbeszédpanel bezárásához.
- Az Excel 2003 Adatok menüjében válassza a Rendezés lehetőséget, vagy az Excel 2007 vagy 2010 adatszalag Rendezés és szűrés csoportját.
- Válassza a "Fejléc sor" lehetőséget a "Saját adattartományom" listából a Rendezés párbeszédablakban, és válassza a "Hiányzik?" (vagy az összehasonlítási oszlop fejlécének tulajdonított neve), majd kattintson az OK gombra.
- Ismételje meg ezeket a lépéseket a másik listához.
Lépés 7. Hasonlítsa össze a nem egyező elemeket vizuálisan, és nézze meg, miért nem egyeznek
Amint azt korábban említettük, az Excel célja, hogy pontos adatok egyezését keressen, hacsak nem állítja be közelítő adatok keresésére. A nem egyezés olyan egyszerű lehet, mint a betűk vagy számjegyek véletlen átültetése. Ez független ellenőrzést is igényelhet, például annak ellenőrzését, hogy a felsorolt eszközöket először jelenteni kell -e.
2. módszer 2 -ből: Feltételes formázás a COUNTIF használatával
1. lépés: Másolja az adatlistákat egyetlen munkalapra
2. lépés. Döntse el, hogy melyik listában szeretné kiemelni a megfelelő vagy nem egyező rekordokat
Ha csak egy listában szeretné kiemelni a rekordokat, akkor valószínűleg az adott listára jellemző rekordokat szeretné kiemelni; vagyis olyan rekordok, amelyek nem egyeznek a másik lista rekordjaival. Ha mindkét listában ki szeretné emelni a rekordokat, akkor azokat a rekordokat kell kiemelnie, amelyek megfelelnek egymásnak. E példa alkalmazásában feltételezzük, hogy az első lista a G3 – G14, a második lista az L3 – L14 cellákat veszi fel.
3. lépés. Válassza ki a listából azokat az elemeket, amelyekben kiemelni szeretné az egyedi vagy a hozzájuk illő elemeket
Ha mindkét listában ki szeretné emelni a megfelelő elemeket, akkor egyenként ki kell választania a listákat, és minden listára alkalmazni kell az összehasonlítási képletet (a következő lépésben leírtak szerint).
4. lépés. Alkalmazza a megfelelő összehasonlítási képletet
Ehhez meg kell nyitnia a Feltételes formázás párbeszédpanelt az Excel verziójában. Az Excel 2003 programban ezt a Formátum menü Feltételes formázás menüpontjával választhatja ki, míg az Excel 2007 és 2010 rendszerben a Feltételes formázás gombra kattint a Kezdőlap szalag Stílusok csoportjában. Válassza ki a szabálytípust "Képlet" -ként, és írja be a képletet a Szabályleírás szerkesztése mezőbe.
- Ha az első listára egyedülálló rekordokat kíván kiemelni, akkor a képlet a következő: "= COUNTIF ($ L $ 3: $ L $ 14, G3 = 0)", a második lista cellatartományát abszolút értékként és a referenciaként kell megjeleníteni relatív értékként az első lista első cellájába. (Ne írja be a záró idézeteket.)
- Ha a második listára egyedülálló rekordokat kíván kiemelni, akkor a képlet a következő: "= COUNTIF ($ G $ 3: $ G $ 14, L3 = 0)", az első lista cellatartományát abszolút értékként és a referenciaként kell megjeleníteni relatív értékként a második lista első cellájába. (Ne írja be a záró idézeteket.)
- Ha ki szeretné emelni az egyes listák azon rekordjait, amelyek a másik listában találhatók, akkor két képletre lesz szüksége, egy az első listára és egy a másodikra. Az első lista képlete "= COUNTIF ($ L $ 3: $ L $ 14, G3> 0)", míg a második lista képlete COUNTIF ($ G $ 3: $ G $ 14, L3> 0) ". a korábban említett módon, válassza ki az első listát a képlet alkalmazásához, majd válassza ki a második listát a képlet alkalmazásához.
- Bármilyen formázást alkalmazzon, hogy kiemelje a megjelölt rekordokat. Kattintson az OK gombra a párbeszédpanel bezárásához.
Videó - A szolgáltatás használatával bizonyos információk megoszthatók a YouTube -lal
Tippek
- Ahelyett, hogy cellahivatkozást használna a COUNTIF feltételes formázási módszerrel, megadhat egy keresendő értéket, és megjelölhet egy vagy több listát az adott értékű példányokhoz.
- Az összehasonlítási űrlapok egyszerűsítése érdekében létrehozhat neveket a listához, például "Lista1" és "Lista2". Ezután a képletek írása során ezek a listanevek helyettesíthetik a fenti példákban használt abszolút cellatartományokat.
