Viena no daudzajām Microsoft Excel iespējām ir iespēja salīdzināt divus datu sarakstus, identificējot sarakstu atbilstību un nosakot, kuri vienumi ir atrodami tikai vienā sarakstā. Tas ir noderīgi, salīdzinot finanšu ierakstus vai pārbaudot, vai konkrēts vārds ir datu bāzē. Jūs varat izmantot funkciju MATCH, lai identificētu un atzīmētu atbilstošus vai neatbilstošus ierakstus, vai arī varat izmantot kondicionēšanas formatējumu ar funkciju COUNTIF. Tālāk norādītās darbības norāda, kā izmantot katru, lai tie atbilstu jūsu datiem.
Soļi
1. metode no 2: Ierakstu identificēšana, izmantojot funkciju MATCH
1. darbība. Kopējiet datu sarakstus vienā darblapā
Programma Excel var strādāt ar vairākām darblapām vienā darbgrāmatā vai ar vairākām darbgrāmatām, taču jūs varēsit vieglāk salīdzināt sarakstus, ja kopēsit to informāciju vienā darblapā.
2. solis. Piešķiriet katram saraksta vienumam unikālu identifikatoru
Ja jūsu abiem sarakstiem nav kopīga veidu, kā tos identificēt, iespējams, jums būs jāpievieno papildu sleja katram datu sarakstam, kas identificē šo vienumu programmā Excel, lai varētu redzēt, vai konkrētā saraksta vienums ir saistīts ar vienumu otrā sarakstā. Šī identifikatora raksturs būs atkarīgs no datu veida, kuru mēģināt saskaņot. Katram kolonnu sarakstam jums būs nepieciešams identifikators.
- Finanšu datiem, kas saistīti ar noteiktu periodu, piemēram, ar nodokļu uzskaiti, tas varētu būt aktīva apraksts, aktīva iegādes datums vai abi. Dažos gadījumos ierakstu var identificēt ar koda numuru; tomēr, ja abiem sarakstiem netiek izmantota viena un tā pati sistēma, šis identifikators var izveidot atbilstības, ja tādas nav, vai ignorēt atbilstības, kas jāizveido.
- Dažos gadījumos varat ņemt vienumus no viena saraksta un apvienot tos ar cita saraksta vienumiem, lai izveidotu identifikatoru, piemēram, fiziskā īpašuma aprakstu un iegādes gadu. Lai izveidotu šādu identifikatoru, jūs savienojat (pievienojat, apvienojat) datus no divām vai vairākām šūnām, izmantojot zīmi "&". Lai apvienotu vienuma aprakstu šūnā F3 ar datumu šūnā G3, atdalot ar atstarpi, ievadiet formulu '= F3 & "" & G3' citā šīs rindas šūnā, piemēram, E3. Ja vēlaties identifikatorā iekļaut tikai gadu (jo vienā sarakstā tiek izmantoti pilni datumi, bet otrā - tikai gadi), jūs iekļautu funkciju GADS, tā vietā šūnā E3 ievadot '= F3 & "" & YEAR (G3)'. (Neiekļaujiet atsevišķus citātus; tie ir tikai, lai norādītu piemēru.)
- Kad formula ir izveidota, varat to kopēt visās pārējās identifikatora kolonnas šūnās, atlasot šūnu ar formulu un velkot aizpildīšanas rokturi pār citām kolonnas šūnām, kurās vēlaties kopēt formulu. Atlaižot peles pogu, katra pārvietotā šūna tiks aizpildīta ar formulu, un šūnu atsauces tiks pielāgotas atbilstošajām šūnām tajā pašā rindā.
Solis 3. Ja iespējams, standartizējiet datus
Kamēr prāts atzīst, ka "Inc." un "Iekļauts" nozīmē vienu un to pašu, Excel to nedara, ja vien jūs nepārveidojat vienu vai otru vārdu. Tāpat jūs varat uzskatīt tādas vērtības kā $ 11, 950 un $ 11, 999,95 par pietiekami tuvām, lai tās atbilstu, bet Excel to nedarīs, ja vien to nepateiksit.
- Jūs varat tikt galā ar dažiem saīsinājumiem, piemēram, “Co” vārdam “Company” un “Inc” - “Incorporated”, izmantojot LEFT string funkciju, lai saīsinātu papildu rakstzīmes. Citi saīsinājumi, piemēram, “Assn” vārdam “Association”, vislabāk var būt risināja, izveidojot datu ievades stila rokasgrāmatu un pēc tam uzrakstot programmu, lai meklētu un labotu nepareizus formātus.
- Ciparu virknēm, piemēram, pasta kodiem, kur daži ieraksti ietver ZIP+4 sufiksu, bet citi ne, varat atkal izmantot funkciju LEFT string, lai atpazītu un saskaņotu tikai primāros pasta kodus. Lai programma Excel atpazītu tuvas, bet atšķirīgas skaitliskās vērtības, varat izmantot funkciju ROUND, lai noapaļotu aizvērtās vērtības līdz vienādam skaitlim un saskaņotu tās.
- Papildu atstarpes, piemēram, ierakstot divas atstarpes starp vārdiem, nevis vienu, var noņemt, izmantojot funkciju TRIM.
Solis 4. Izveidojiet slejas salīdzināšanas formulai
Tāpat kā jums bija jāizveido slejas saraksta identifikatoriem, jums ir jāizveido kolonnas formulai, kas salīdzina jūsu vietā. Katram sarakstam jums būs nepieciešama viena sleja.
Jūs vēlaties iezīmēt šīs slejas ar kaut ko līdzīgu "Trūkst?"
5. solis. Ievadiet salīdzināšanas formulu katrā šūnā
Salīdzināšanas formulai izmantosit funkciju MATCH, kas ir ievietota citā Excel funkcijā - ISNA.
- Formula izpaužas kā "= ISNA (MATCH (G3, $ L $ 3: $ L $ 14, FALSE))", kur pirmā saraksta identifikatora kolonnas šūna tiek salīdzināta ar katru otrā saraksta identifikatoru ar pārbaudiet, vai tas atbilst kādam no tiem. Ja tas neatbilst, trūkst ieraksta, un šūnā tiks parādīts vārds "TRUE". Ja tas atbilst, ieraksts ir pieejams, un tiks parādīts vārds "FALSE". (Ievadot formulu, neiekļaujiet pēdiņas.)
- Formulu var kopēt atlikušajās kolonnas šūnās tāpat kā šūnu identifikatora formulu. Šajā gadījumā mainās tikai šūnu atsauce identifikatora šūnai, jo dolāra zīmju ievietošana rindas priekšā un kolonnu atsauces pirmās un pēdējās šūnas otrā šūnu identifikatoru sarakstā padara tās par absolūtām atsaucēm.
- Jūs varat kopēt pirmā saraksta salīdzināšanas formulu otrā saraksta kolonnas pirmajā šūnā. Pēc tam jums būs jārediģē šūnu atsauces, lai “G3” tiktu aizstāts ar atsauci uz otrā saraksta pirmā identifikatora šūnu, bet “$ L $ 3: $ L $ 14” - ar pirmo un pēdējo identifikatora šūnu. otrais saraksts. (Atstājiet dolāra zīmes un kolu mierā.) Pēc tam šo rediģēto formulu varat nokopēt atlikušajās otrā saraksta salīdzinājuma rindas šūnās.
6. solis. Ja nepieciešams, kārtojiet sarakstus, lai vieglāk redzētu neatbilstošas vērtības
Ja jūsu saraksti ir lieli, jums, iespējams, vajadzēs tos kārtot, lai apkopotu visas neatbilstošās vērtības. Norādījumi, kas sniegti zemāk esošajās apakšposmās, formulas pārvērtīs par vērtībām, lai izvairītos no pārrēķina kļūdām, un, ja jūsu saraksti ir lieli, izvairīsies no ilga pārrēķināšanas laika.
- Velciet peli virs visām saraksta šūnām, lai to atlasītu.
- Programmā Excel 2003 izvēlnē Rediģēt atlasiet Kopēt vai Excel 2007 vai 2010 mājas lentes starpliktuves grupā.
- Programmas Excel 2003 izvēlnē Rediģēt atlasiet Ielīmēt speciāli vai nolaižamajā pogas Ielīmēt Excel 2007 vai 2010 sākuma mājas lentes starpliktuves grupā.
- Dialoglodziņa Īpašā ielīmēšana sarakstā Ielīmēt kā izvēlieties "Vērtības". Noklikšķiniet uz Labi, lai aizvērtu dialoglodziņu.
- Programmā Excel 2003 izvēlnē Dati atlasiet Kārtot vai lentes Datu grupa Kārtot un filtrēt programmā Excel 2007 vai 2010.
- Dialoga Kārtot pēc saraksta "Manam datu diapazonam ir" atlasiet "Galvenes rinda", atlasiet "Trūkst?" (vai nosaukumu, kuru jūs faktiski piešķīrāt salīdzinājuma slejas galvenei) un noklikšķiniet uz Labi.
- Atkārtojiet šīs darbības citam sarakstam.
7. solis Vizuāli salīdziniet neatbilstošos vienumus, lai noskaidrotu, kāpēc tie nesakrīt
Kā minēts iepriekš, programma Excel ir paredzēta precīzu datu atbilstības meklēšanai, ja vien neesat to iestatījis, lai meklētu aptuvenus datus. Jūsu neatbilstība var būt tikpat vienkārša kā nejauša burtu vai ciparu transponēšana. Tas varētu būt arī kaut kas tāds, kam nepieciešama neatkarīga pārbaude, piemēram, pārbaude, vai par uzskaitītajiem aktīviem vispirms ir jāziņo.
2. metode no 2: Nosacījuma formatēšana ar COUNTIF
1. darbība. Kopējiet datu sarakstus vienā darblapā
2. solis. Izlemiet, kurā sarakstā vēlaties izcelt atbilstošus vai neatbilstošus ierakstus
Ja vēlaties izcelt ierakstus tikai vienā sarakstā, iespējams, vēlēsities izcelt ierakstus, kas ir unikāli šim sarakstam; tas ir, ierakstus, kas neatbilst ierakstiem otrā sarakstā. Ja vēlaties izcelt ierakstus abos sarakstos, izceliet ierakstus, kas atbilst viens otram. Šajā piemērā mēs pieņemsim, ka pirmais saraksts aizņem šūnas G3 līdz G14, bet otrais saraksts aizņem šūnas L3 līdz L14.
Solis 3. Sarakstā atlasiet vienumus, kuros vēlaties izcelt unikālus vai atbilstošus vienumus
Ja vēlaties izcelt atbilstošos vienumus abos sarakstos, jums ir jāizvēlas saraksti pa vienam un katram sarakstam jāpiemēro salīdzināšanas formula (aprakstīta nākamajā darbībā).
4. solis. Izmantojiet atbilstošo salīdzināšanas formulu
Lai to izdarītu, jums savā Excel versijā ir jāpiekļūst dialoglodziņam Nosacītā formatēšana. Programmā Excel 2003 to darāt, izvēlnē Formatēt atlasot Nosacītā formatēšana, savukārt programmā Excel 2007 un 2010 noklikšķināt uz pogas Nosacītā formatēšana lentes Sākums grupā. Atlasiet kārtulas veidu "Formula" un ievadiet formulu laukā Rediģēt kārtulas aprakstu.
- Ja vēlaties izcelt ierakstus, kas ir unikāli pirmajam sarakstam, formula būtu "= COUNTIF ($ L $ 3: $ L $ 14, G3 = 0)", otrā saraksta šūnu diapazons tiek attēlots kā absolūtās vērtības un atsauce uz pirmā saraksta pirmo šūnu kā relatīvo vērtību. (Neievadiet pēdējās pēdiņas.)
- Ja vēlaties izcelt ierakstus, kas ir unikāli otrajam sarakstam, formula būtu "= COUNTIF ($ G $ 3: $ G $ 14, L3 = 0)", pirmā saraksta šūnu diapazons tiek attēlots kā absolūtās vērtības un atsauce uz otrā saraksta pirmo šūnu kā relatīvo vērtību. (Neievadiet pēdējās pēdiņas.)
- Ja vēlaties izcelt katra saraksta ierakstus, kas ir atrodami otrā sarakstā, jums būs nepieciešamas divas formulas - viena pirmajam sarakstam un otra otrajai. Pirmā saraksta formula ir "= COUNTIF ($ L $ 3: $ L $ 14, G3> 0)", bet otrā saraksta formula ir COUNTIF ($ G $ 3: $ G $ 14, L3> 0) ". Iepriekš minēts, jūs izvēlaties pirmo sarakstu, lai lietotu tā formulu, un pēc tam atlasiet otro sarakstu, lai lietotu tā formulu.
- Izmantojiet jebkādu formatējumu, lai atzīmētu atzīmētos ierakstus. Noklikšķiniet uz Labi, lai aizvērtu dialoglodziņu.
Video - izmantojot šo pakalpojumu, daļa informācijas var tikt kopīgota ar pakalpojumu YouTube
Padomi
- Tā vietā, lai izmantotu šūnu atsauci ar nosacījuma formatēšanas metodi COUNTIF, varat ievadīt meklējamo vērtību un atzīmēt vienu vai vairākus sarakstus šīs vērtības gadījumiem.
- Lai vienkāršotu salīdzināšanas veidlapas, savam sarakstam varat izveidot nosaukumus, piemēram, "Saraksts1" un "Saraksts2". Tad, rakstot formulas, šie sarakstu nosaukumi var aizstāt iepriekšējos piemēros izmantotos absolūtos šūnu diapazonus.
