Kā salīdzināt divus sarakstus programmā Excel

Excel ir efektīva datu apstrādes programma. Un viena no informācijas analīzes metodēm ir divu sarakstu salīdzināšana. Ja pareizi salīdzināsit divus sarakstus programmā Excel, šī procesa organizēšana būs ļoti vienkārša. Pietiek tikai sekot dažiem punktiem, kas šodien tiks apspriesti. Šīs metodes praktiskā īstenošana ir pilnībā atkarīga no personas vai organizācijas vajadzībām konkrētajā brīdī. Tāpēc jāapsver vairāki iespējamie gadījumi.

Divu sarakstu salīdzināšana programmā Excel

Protams, jūs varat salīdzināt divus sarakstus manuāli. Bet tas prasīs ilgu laiku. Programmai Excel ir savs inteliģentais rīku komplekts, kas ļaus ne tikai ātri salīdzināt datus, bet arī iegūt informāciju, ko nav tik viegli iegūt ar acīm. Pieņemsim, ka mums ir divas kolonnas ar koordinātām A un B. Dažas vērtības tajās atkārtojas.

Problēmas formulēšana

Tāpēc mums ir jāsalīdzina šīs kolonnas. Divu dokumentu salīdzināšanas procedūra ir šāda:

1. Ja katra no šiem sarakstiem unikālās šūnas ir vienādas un kopējais unikālo šūnu skaits ir vienāds un šūnas ir vienādas, tad šos sarakstus var uzskatīt par vienādiem. Secībai, kādā šajā sarakstā esošās vērtības ir saliktas, nav tik lielas nozīmes.
2. Par sarakstu daļēju sakritību var runāt, ja pašas unikālās vērtības ir vienādas, bet atkārtojumu skaits ir atšķirīgs. Tāpēc šādos sarakstos var būt atšķirīgs elementu skaits.
3. To, ka abi saraksti nesakrīt, norāda atšķirīga unikālo vērtību kopa.

Visi šie trīs nosacījumi vienlaikus ir mūsu problēmas nosacījumi.

Problēmas risinājums

Izveidosim divus dinamiskos diapazonus, lai atvieglotu sarakstu salīdzināšanu. Katrs no tiem atbildīs katram no sarakstiem.

Lai salīdzinātu divus sarakstus, rīkojieties šādi:

1. Atsevišķā kolonnā mēs izveidojam unikālo vērtību sarakstu, kas ir raksturīgas abiem sarakstiem. Šim nolūkam mēs izmantojam formulu: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). Pati formula ir jāraksta kā masīva formula.
2. Noteiksim, cik reižu katra unikālā vērtība parādās datu masīvā. Šeit ir formulas, kā to izdarīt: =COUNTIF(saraksts1,D5) un =COUNTI(saraksts2,D5).
3. Ja gan atkārtojumu skaits, gan unikālo vērtību skaits ir vienāds visos sarakstos, kas ir iekļauti šajos diapazonos, funkcija atgriež vērtību 0. Tas norāda, ka atbilstība ir XNUMX%. Šajā gadījumā šo sarakstu virsraksti iegūs zaļu fonu.
4. Ja viss unikālais saturs ir abos sarakstos, tas tiek atgriezts pēc formulām =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) vērtība būs nulle. Ja E1 nesatur nulli, bet šāda vērtība ir šūnās E2 un F2, tad šajā gadījumā diapazoni tiks atpazīti kā atbilstoši, bet tikai daļēji. Šajā gadījumā atbilstošo sarakstu virsraksti kļūs oranži.
5. Un, ja kāda no iepriekš aprakstītajām formulām atgriež vērtību, kas nav nulle, saraksti būs pilnīgi neatbilstoši.

Šī ir atbilde uz jautājumu, kā analizēt atbilstības kolonnas, izmantojot formulas. Kā redzat, izmantojot funkcijas, jūs varat īstenot gandrīz jebkuru uzdevumu, kas, no pirmā acu uzmetiena, nav saistīts ar matemātiku.

Testēšanas piemērs

Mūsu tabulas versijā ir trīs veidu saraksti no katra iepriekš aprakstītā veida. Tam ir daļēji un pilnīgi atbilstība, kā arī neatbilstība.

Lai salīdzinātu datus, mēs izmantojam diapazonu A5:B19, kurā pārmaiņus ievietojam šos sarakstu pārus. Par to, kāds būs salīdzināšanas rezultāts, mēs sapratīsim pēc sākotnējo sarakstu krāsas. Ja tie ir pilnīgi atšķirīgi, tad tas būs sarkans fons. Ja daļa datu ir vienādi, tad dzeltens. Pilnīgas identitātes gadījumā attiecīgie virsraksti būs zaļi. Kā izveidot krāsu atkarībā no tā, kāds ir rezultāts? Tam nepieciešams nosacījumformatējums.

Atšķirību atrašana divos sarakstos divos veidos

Aprakstīsim vēl divas metodes atšķirību atrašanai atkarībā no tā, vai saraksti ir vai nav sinhroni.

1. iespēja. Sinhronie saraksti

Šī ir vienkārša iespēja. Pieņemsim, ka mums ir šādi saraksti.

Lai noteiktu, cik reizes vērtības nesakrita, varat izmantot formulu: =SUMPRODUKTS(—(A2:A20<>B2:B20)). Ja mēs iegūstam 0, tas nozīmē, ka abi saraksti ir vienādi.

2. iespēja: jaukti saraksti

Ja saraksti nav identiski tajos esošo objektu secībā, jums ir jāpiemēro līdzeklis, piemēram, nosacījumformatējums un jākrāso dublikātu vērtības. Vai arī izmantojiet funkciju COUNTIF, izmantojot kuru mēs nosakām, cik reižu elements no viena saraksta parādās otrajā.

Kā salīdzināt 2 kolonnas rindu pēc rindas

Salīdzinot divas kolonnas, mums bieži ir jāsalīdzina informācija, kas atrodas dažādās rindās. Lai to izdarītu, operators mums palīdzēs IF Apskatīsim, kā tas darbojas praksē. Lai to izdarītu, mēs piedāvājam vairākas ilustratīvās situācijas.

Piemērs. Kā salīdzināt 2 slejas atbilstībām un atšķirībām vienā rindā

Lai analizētu, vai vērtības, kas atrodas vienā rindā, bet dažādās kolonnās, ir vienādas, mēs rakstām funkciju IF. Formula tiek ievietota katrā rindā, kas ievietota papildu kolonnā, kurā tiks parādīti datu apstrādes rezultāti. Bet katrā rindā tas noteikti nav jānorāda, vienkārši iekopējiet to atlikušajās šīs kolonnas šūnās vai izmantojiet automātiskās pabeigšanas marķieri.

Mums vajadzētu pierakstīt šādu formulu, lai saprastu, vai vērtības abās kolonnās ir vienādas vai nē: =IF(A2=B2, “Atbilst”, “”). Šīs funkcijas loģika ir ļoti vienkārša: tā salīdzina vērtības šūnās A2 un B2, un, ja tās ir vienādas, tā parāda vērtību “Sakrīt”. Ja dati atšķiras, tie neatgriež nekādu vērtību. Varat arī pārbaudīt šūnas, lai redzētu, vai starp tām ir atbilstība. Šajā gadījumā izmantotā formula ir: =IF(A2<>B2, “Neatbilst”, “”). Princips ir tāds pats, vispirms tiek veikta pārbaude. Ja izrādās, ka šūnas atbilst kritērijam, tiek parādīta vērtība “Neatbilst”.

Formulas laukā ir iespējams arī izmantot šādu formulu, lai parādītu gan “Sakrīt”, ja vērtības ir vienādas, un “Neatbilst”, ja tās atšķiras: =IF(A2=B2; “Sakrīt”, “Neatbilst”). Vienlīdzības operatora vietā varat izmantot arī nevienlīdzības operatoru. Tikai vērtību secība, kas tiks parādīta šajā gadījumā, būs nedaudz atšķirīga: =IF(A2<>B2, “Neatbilst”, “Sakrīt”). Pēc formulas pirmās versijas izmantošanas rezultāts būs šāds.

Šī formulas variācija nav reģistrjutīga. Tāpēc, ja vērtības vienā kolonnā atšķiras no citām tikai ar to, ka tās ir rakstītas ar lielajiem burtiem, programma šo atšķirību nepamanīs. Lai salīdzinājumam būtu reģistrjutīgs, kritērijos ir jāizmanto funkcija TIKAI. Pārējie argumenti tiek atstāti nemainīgi: =IF(PRECĪZI(A2,B2), “Atbilst”, “Unikāls”).

Kā salīdzināt vairākas atbilstības kolonnas vienā rindā

Sarakstos esošās vērtības var analizēt pēc vesela kritēriju kopuma:

1. Atrodiet tās rindas, kurām visur ir vienādas vērtības.
2. Atrodiet tās rindas, kurās ir atbilstības tikai divos sarakstos.

Apskatīsim dažus piemērus, kā rīkoties katrā no šiem gadījumiem.

Piemērs. Kā atrast atbilstības vienā rindā vairākās tabulas kolonnās

Pieņemsim, ka mums ir virkne kolonnu, kas satur mums nepieciešamo informāciju. Mēs saskaramies ar uzdevumu noteikt tās rindas, kurās vērtības ir vienādas. Lai to izdarītu, jums jāizmanto šāda formula: =JA(UN(A2=B2,A2=C2), "atbilst", " ").

Ja tabulā ir pārāk daudz kolonnu, jums tas vienkārši jāizmanto kopā ar funkciju IF operators COUNTIF: =IF(COUNTIF($A2:$C2,$A2)=3;"atbilst";" "). Šajā formulā izmantotais skaitlis norāda pārbaudāmo kolonnu skaitu. Ja tas atšķiras, jums ir jāraksta tik daudz, cik tas atbilst jūsu situācijai.

Piemērs. Kā atrast atbilstības vienā rindā jebkurās 2 tabulas kolonnās

Pieņemsim, ka mums ir jāpārbauda, ​​vai vērtības vienā rindā sakrīt divās kolonnās no tabulā esošajām. Lai to izdarītu, funkcija ir jāizmanto kā nosacījums OR, kur pārmaiņus ierakstiet katras kolonnas vienādību ar otru. Šeit ir piemērs.

Mēs izmantojam šo formulu: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Var rasties situācija, kad tabulā ir daudz kolonnu. Šajā gadījumā formula būs milzīga, un visu nepieciešamo kombināciju atlasīšana var aizņemt daudz laika. Lai atrisinātu šo problēmu, jums ir jāizmanto funkcija COUNTIF: =IF(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0; “Unikāla virkne”; “Nav unikāla virkne”)

Mēs redzam, ka kopumā mums ir divas funkcijas COUNTIF. Ar pirmo mēs pārmaiņus nosakām, cik kolonnām ir līdzība ar A2, un ar otro mēs pārbaudām līdzību skaitu ar B2 vērtību. Ja, veicot aprēķinus pēc šīs formulas, mēs iegūstam nulles vērtību, tas norāda, ka visas šīs kolonnas rindas ir unikālas, ja vairāk, tad ir līdzības. Līdz ar to, ja, aprēķinot pēc divām formulām un saskaitot galarezultātus, iegūstam nulles vērtību, tad tiek atgriezta teksta vērtība “Unikālā virkne”, ja šis skaitlis ir lielāks, tiek rakstīts, ka šī virkne nav unikāla.

Kā salīdzināt 2 slejas programmā Excel atbilstībām

Tagad ņemsim piemēru. Pieņemsim, ka mums ir tabula ar divām kolonnām. Jums ir jāpārbauda, ​​​​vai tie atbilst. Lai to izdarītu, jums jāpielieto formula, kurā tiks izmantota arī funkcija IFun operators COUNTIF: =IF(COUNTIF($B:$B,$A5)=0, "B kolonnā nav atbilstības", "B kolonnā ir atbilstības"

Papildu darbība nav nepieciešama. Aprēķinot rezultātu pēc šīs formulas, mēs iegūstam funkcijas trešā argumenta vērtību IF sērkociņi. Ja tādu nav, tad otrā argumenta saturs.

Kā salīdzināt 2 slejas programmā Excel, lai atrastu atbilstību un izceltu ar krāsu

Lai būtu vieglāk vizuāli noteikt atbilstošās kolonnas, varat tās izcelt ar kādu krāsu. Lai to izdarītu, jums ir jāizmanto funkcija "Nosacītā formatēšana". Redzēsim praksē.

Atbilstību atrašana un izcelšana pēc krāsas vairākās kolonnās

Lai noteiktu atbilstības un izceltu tās, vispirms atlasiet datu diapazonu, kurā tiks veikta pārbaude, un pēc tam cilnē "Sākums" atveriet vienumu "Nosacītā formatēšana". Tur kā šūnu atlases noteikumu atlasiet “Vērtību dublikāti”.

Pēc tam parādīsies jauns dialoglodziņš, kurā kreisajā uznirstošajā sarakstā atrodam opciju “Atkārtot”, un labajā sarakstā atlasām krāsu, kas tiks izmantota atlasei. Pēc tam, kad noklikšķināsim uz pogas “OK”, tiks atlasīts visu šūnu fons ar līdzībām. Pēc tam vienkārši salīdziniet kolonnas ar acīm.

Atbilstošu līniju atrašana un izcelšana

Paņēmiens, lai pārbaudītu, vai stīgas atbilst, nedaudz atšķiras. Pirmkārt, mums ir jāizveido papildu kolonna, un tur mēs izmantosim kombinētās vērtības, izmantojot operatoru &. Lai to izdarītu, jums jāuzraksta veidlapas formula: =A2&B2&C2&D2.

Mēs atlasām kolonnu, kas tika izveidota un satur apvienotās vērtības. Tālāk mēs veicam to pašu darbību secību, kas aprakstīta iepriekš kolonnām. Dublētas līnijas tiks izceltas jūsu norādītajā krāsā.

Mēs redzam, ka atkārtojumu meklēšanā nav nekā sarežģīta. Programmā Excel ir visi šim nolūkam nepieciešamie rīki. Pirms visu šo zināšanu izmantošanas praksē ir svarīgi tikai praktizēt.