Një nga aftësitë e shumta të Microsoft Excel është aftësia për të krahasuar dy lista të të dhënave, duke identifikuar ndeshjet midis listave dhe duke identifikuar se cilat artikuj gjenden në vetëm një listë. Kjo është e dobishme kur krahasoni të dhënat financiare ose kontrolloni nëse një emër i veçantë është në një bazë të dhënash. Mund të përdorni funksionin MATCH për të identifikuar dhe shënuar regjistrimet që përputhen ose që nuk përputhen, ose mund të përdorni formatimin e kushtëzimit me funksionin COUNTIF. Hapat e mëposhtëm ju tregojnë se si ta përdorni secilën për të përputhur të dhënat tuaja.
Hapa
Metoda 1 nga 2: Identifikimi i Regjistrimeve me Funksionin MATCH
Hapi 1. Kopjoni listat e të dhënave në një fletë pune të vetme
Excel mund të punojë me fletë pune të shumta brenda një libri të vetëm pune, ose me libra të shumëfishtë pune, por krahasimi i listave do të jetë më i lehtë nëse kopjoni informacionin e tyre në një fletë pune të vetme.
Hapi 2. Jepini secilit artikull të listës një identifikues unik
Nëse dy listat tuaja nuk ndajnë një mënyrë të përbashkët për t'i identifikuar ato, mund t'ju duhet të shtoni një kolonë shtesë në secilën listë të të dhënave që identifikon atë artikull në Excel, në mënyrë që të shikojë nëse një artikull në një listë të caktuar lidhet me një artikull në listën tjetër. Natyra e këtij identifikuesi do të varet nga lloji i të dhënave me të cilat po përpiqeni të përputheni. Për secilën listë të kolonave do t'ju duhet një identifikues.
- Për të dhënat financiare të lidhura me një periudhë të caktuar, të tilla si regjistrimet tatimore, ky mund të jetë përshkrimi i një aktivi, data kur është blerë aktivi, ose të dyja. Në disa raste, një hyrje mund të identifikohet me një numër kodik; megjithatë, nëse i njëjti sistem nuk përdoret për të dy listat, ky identifikues mund të krijojë ndeshje ku nuk ka ose të injorojë ndeshjet që duhet të bëhen.
- Në disa raste, ju mund të merrni artikuj nga një listë dhe t'i kombinoni ato me artikuj nga një listë tjetër për të krijuar një identifikues, siç është përshkrimi i pasurisë fizike dhe viti kur është blerë. Për të krijuar një identifikues të tillë, ju bashkoni (shtoni, kombinoni) të dhëna nga dy ose më shumë qeliza duke përdorur ampersand (&). Për të kombinuar një përshkrim të artikullit në qelizën F3 me një datë në qelizën G3, të ndarë nga një hapësirë, do të futnit formulën '= F3 & "" & G3' në një qelizë tjetër në atë rresht, siç është E3. Nëse dëshironi të përfshini vetëm vitin në identifikues (sepse njëra listë përdor data të plota dhe tjetra përdor vetëm vite), ju do të përfshinin funksionin VITI duke futur '= F3 & "" & YEAR (G3)' në qelizën E3 në vend. (Mos përfshini kuotat e vetme; ato janë atje vetëm për të treguar shembullin.)
- Pasi të keni krijuar formulën, mund ta kopjoni në të gjitha qelizat e tjera të kolonës identifikuese duke zgjedhur qelizën me formulë dhe duke tërhequr dorezën e mbushjes mbi qelizat e tjera të kolonës ku dëshironi të kopjoni formulën. Kur lëshoni butonin e miut, çdo qelizë që keni tërhequr do të mbushet me formulën, me referencat e qelizave të përshtatura në qelizat e duhura në të njëjtin rresht.
Hapi 3. Standardizimi i të dhënave aty ku është e mundur
Ndërsa mendja njeh se "Inc." dhe "Incorporated" nënkuptojnë të njëjtën gjë, Excel nuk e bën nëse nuk e riformatoni një fjalë ose tjetrën. Në mënyrë të ngjashme, ju mund të konsideroni vlera të tilla si $ 11, 950 dhe $ 11, 999.95 si aq afër sa të përputhen, por Excel nuk do ta bëjë nëse nuk ia tregoni.
- Mund të merreni me disa shkurtesa, të tilla si "Co" për "Company" dhe "Inc" për "Incorporated duke përdorur funksionin e vargut LEFT për të cunguar karakteret shtesë. Shkurtesat e tjera, të tilla si" Assn "për" Association ", mund të jenë më së miri trajtohet duke krijuar një udhëzues të stilit të futjes së të dhënave dhe më pas duke shkruar një program për të kërkuar dhe korrigjuar formatet e papërshtatshme.
- Për vargjet e numrave, siç janë kodet ZIP ku disa shënime përfshijnë prapashtesën ZIP +4 dhe të tjerat jo, mund të përdorni përsëri funksionin e vargut LEFT për të njohur dhe përputhur vetëm kodet ZIP kryesor. Që Excel -i të njohë vlerat numerike që janë afër por jo të njëjta, mund të përdorni funksionin ROUND për të rrumbullakuar vlerat e mbyllura në të njëjtin numër dhe për t'i përputhur ato.
- Hapësira shtesë, të tilla si shtypja e dy hapësirave midis fjalëve në vend të një, mund të hiqen duke përdorur funksionin TRIM.
Hapi 4. Krijo kolona për formulën e krahasimit
Ashtu si ju është dashur të krijoni kolona për identifikuesit e listës, do t'ju duhet të krijoni kolona për formulën që bën krahasimin për ju. Për secilën listë do t'ju duhet një kolonë.
Ju do të dëshironi t'i etiketoni këto kolona me diçka si "Mungon?"
Hapi 5. Futni formulën e krahasimit në secilën qelizë
Për formulën e krahasimit, do të përdorni funksionin MATCH të vendosur brenda një funksioni tjetër Excel, ISNA.
- Formula merr formën e "= ISNA (MATCH (G3, $ L $ 3: $ L $ 14, FALSE))", ku një qelizë e kolonës identifikuese të listës së parë krahasohet me secilin nga identifikuesit në listën e dytë me shikoni nëse përputhet me njërën prej tyre. Nëse nuk përputhet, një rekord mungon dhe fjala "TRUE" do të shfaqet në atë qelizë. Nëse përputhet, rekordi është i pranishëm dhe fjala "FALSE" do të shfaqet. (Kur futni formulën, mos përfshini kuotat mbyllëse.)
- Ju mund të kopjoni formulën në qelizat e mbetura të kolonës në të njëjtën mënyrë si keni kopjuar formulën e identifikuesit të qelizës. Në këtë rast, vetëm referenca e qelizës për qelizën identifikuese ndryshon, pasi vendosja e shenjave të dollarit para referencave të rreshtit dhe kolonës për qelizat e para dhe të fundit në listën e identifikuesve të qelizës së dytë i bën ato referenca absolute.
- Ju mund të kopjoni formulën e krahasimit për listën e parë në qelizën e parë të kolonës për listën e dytë. Atëherë do të duhet të redaktoni referencat e qelizës në mënyrë që "G3" të zëvendësohet me referencën për qelizën e parë identifikuese të listës së dytë dhe "$ L $ 3: $ L $ 14" të zëvendësohet me qelizën e parë dhe të fundit identifikuese të lista e dyte. (Lërini shenjat e dollarit dhe zorrën e trashë vetëm.) Pastaj mund ta kopjoni këtë formulë të redaktuar në qelizat e mbetura në rreshtin e krahasimit të listës së dytë.
Hapi 6. Rendisni listat për të parë më lehtë vlerat që nuk përputhen, nëse është e nevojshme
Nëse listat tuaja janë të mëdha, mund t'ju duhet t'i rendisni ato për t'i bashkuar të gjitha vlerat që nuk përputhen. Udhëzimet në hapat e mëposhtëm do t'i shndërrojnë formulat në vlera për të shmangur gabimet e rillogaritjes, dhe nëse listat tuaja janë të mëdha, do të shmangin një kohë të gjatë të rillogaritjes.
- Tërhiqeni miun mbi të gjitha qelizat në një listë për ta zgjedhur atë.
- Zgjidhni Kopjo nga menyja Edit në Excel 2003 ose nga grupi Clipboard i shiritit Home në Excel 2007 ose 2010.
- Zgjidhni Paste Special nga menyja Edit në Excel 2003 ose nga butoni zbritës Paste në grupin Clipboard të Excel 2007 ose 2010s fjongo Home.
- Zgjidhni "Vlerat" nga lista Paste As në kutinë e dialogut Paste Special. Klikoni OK për të mbyllur dialogun.
- Zgjidhni Rendit nga menyja Data në Excel 2003 ose grupi Rendit dhe Filtro të shiritit të të dhënave në Excel 2007 ose 2010.
- Zgjidhni "Rreshti i titullit" nga lista "Gama e të dhënave të mia ka" në dialogun Rendit sipas, zgjidhni "Mungon?" (ose emri që ju në të vërtetë i dhatë kokës së kolonës së krahasimit) dhe klikoni OK.
- Përsëritni këto hapa për listën tjetër.
Hapi 7. Krahasoni artikujt që nuk përputhen vizualisht për të parë pse nuk përputhen
Siç u tha më parë, Excel është krijuar për të kërkuar përputhje të sakta të të dhënave nëse nuk e vendosni atë për të kërkuar ato të përafërta. Mospërputhja juaj mund të jetë aq e thjeshtë sa një transferim aksidental i shkronjave ose shifrave. Mund të jetë gjithashtu diçka që kërkon verifikim të pavarur, siç është kontrollimi për të parë nëse pasuritë e listuara duheshin raportuar në radhë të parë.
Metoda 2 nga 2: Formatimi i kushtëzuar me COUNTIF
Hapi 1. Kopjoni listat e të dhënave në një fletë pune të vetme
Hapi 2. Vendosni se në cilën listë dëshironi të nënvizoni regjistrimet që përputhen ose që nuk përputhen
Nëse doni të nënvizoni regjistrimet në vetëm një listë, me siguri do të dëshironi të nënvizoni regjistrimet unike për atë listë; domethënë, rekordet që nuk përputhen me rekordet në listën tjetër. Nëse doni të nënvizoni regjistrimet në të dy listat, do të dëshironi të nënvizoni regjistrimet që përputhen me njëra -tjetrën. Për qëllimet e këtij shembulli, ne do të supozojmë se lista e parë merr qelizat G3 deri G14 dhe lista e dytë merr qelizat L3 deri L14.
Hapi 3. Zgjidhni artikujt në listën në të cilën dëshironi të theksoni artikujt unikë ose që përputhen
Nëse dëshironi të theksoni artikujt që përputhen në të dy listat, do të duhet të zgjidhni listat një nga një dhe të aplikoni formulën e krahasimit (të përshkruar në hapin tjetër) në secilën listë.
Hapi 4. Zbatoni formulën e duhur të krahasimit
Për ta bërë këtë, do të duhet të hyni në dialogun e Formatimit të Kushtëzuar në versionin tuaj të Excel. Në Excel 2003, ju e bëni këtë duke zgjedhur Formatimin e kushtëzuar nga menyja Format, ndërsa në Excel 2007 dhe 2010, ju klikoni butonin Formatimi i kushtëzuar në grupin Styles të shiritit Home. Zgjidhni llojin e rregullit si "Formula" dhe futni formulën tuaj në fushën Edit the Description Rule.
- Nëse doni të nënvizoni regjistrimet unike për listën e parë, formula do të ishte "= COUNTIF ($ L $ 3: $ L $ 14, G3 = 0)", me gamën e qelizave të listës së dytë të dhënë si vlera absolute dhe referencë në qelizën e parë të listës së parë si vlerë relative. (Mos futni kuotat e mbyllura.)
- Nëse doni të nënvizoni regjistrimet unike për listën e dytë, formula do të ishte "= COUNTIF ($ G $ 3: $ G $ 14, L3 = 0)", me gamën e qelizave të listës së parë të dhënë si vlera absolute dhe referencë në qelizën e parë të listës së dytë si vlerë relative. (Mos futni kuotat e mbyllura.)
- Nëse doni të nënvizoni regjistrimet në secilën listë që gjenden në listën tjetër, do t'ju duhet dy formula, një për listën e parë dhe një për të dytën. Formula për listën e parë është "= COUNTIF ($ L $ 3: $ L $ 14, G3> 0)", ndërsa formula për listën e dytë është COUNTIF ($ G $ 3: $ G $ 14, L3> 0) ". Si e vërejtur më parë, ju zgjidhni listën e parë për të aplikuar formulën e saj dhe më pas zgjidhni listën e dytë për të aplikuar formulën e saj.
- Aplikoni çfarëdo formatimi që dëshironi për të nxjerrë në pah regjistrimet që po shënohen. Klikoni OK për të mbyllur dialogun.
Video - Duke përdorur këtë shërbim, disa informacione mund të ndahen me YouTube
Këshilla
- Në vend që të përdorni një referencë qelizore me metodën e formatimit të kushtëzuar COUNTIF, mund të futni një vlerë për t'u kërkuar dhe të shënoni një ose më shumë lista për rastet e asaj vlere.
- Për të thjeshtuar format e krahasimit, mund të krijoni emra për listën tuaj, të tilla si "List1" dhe "List2". Pastaj, kur shkruani formulat, këto emra listash mund të zëvendësojnë vargjet absolute të qelizave të përdorura në shembujt e mësipërm.
