Metodoj por kompari tabelojn en Microsoft Excel

Tre ofte, Excel-uzantoj alfrontas la taskon kompari du tablojn aŭ listojn por identigi diferencojn aŭ mankantajn elementojn en ili. Ĉiu uzanto traktas ĉi tiun taskon laŭ sia propra maniero, sed plej ofte oni pasigas sufiĉe grandan tempon por solvi ĉi tiun problemon, ĉar ne ĉiuj aliroj al ĉi tiu problemo estas raciaj. Samtempe estas pluraj pruvitaj agaj algoritmoj, kiuj ebligos vin kompari listojn aŭ tabelaranĝojn en sufiĉe mallonga tempo kun minimuma peno. Ni rigardu pli proksime ĉi tiujn eblojn.

Vidu ankaŭ: Komparo de du dokumentoj en MS Word

Metodoj de komparo

Estas multaj manieroj kompari tablespacojn en Excel, sed ĉiuj povas esti dividitaj en tri grandaj grupoj:

Estas sur la bazo de ĉi tiu klasifiko, ke unue komparaj metodoj estas selektitaj, kaj specifaj agoj kaj algoritmoj por plenumi la taskon estas determinitaj. Ekzemple, kiam vi faras komparojn en diversaj libroj, vi devas samtempe malfermi du Excel-dosierojn.

Aldone, oni diru, ke komparado de tablaj spacoj havas sencon nur kiam ili havas similan strukturon.

Metodo 1: simpla formulo

La plej facila maniero kompari datumojn en du tabloj estas uzi simplan egalecan formulon. Se la datumoj kongruas, tiam ĝi donas la VERA valoron, kaj se ne, tiam - Malvero. Eblas kompari ambaŭ ciferecajn datumojn kaj tekston. La malavantaĝo de ĉi tiu metodo estas, ke ĝi povas esti uzata nur se la datumoj en la tabelo estas ordigitaj aŭ ordigitaj sammaniere, sinkronigitaj kaj havas egalan nombron da linioj. Ni vidu kiel praktike uzi ĉi tiun metodon per la ekzemplo de du tabloj metitaj sur unu folion.

Do ni havas du simplajn tabelojn kun listoj de dungitoj kaj iliaj salajroj. Estas necese kompari la listojn de dungitoj kaj identigi nekonsekvencojn inter la kolonoj en kiuj estas lokitaj la nomoj.

1. Por tio ni bezonas ekstran kolumnon sur la folio. Enigu la afiŝon tie "=". Poste alklaku la unuan aĵon en komparo en la unua listo. Denove ni metas la simbolon "=" de la klavaro. Poste alklaku la unuan ĉelon de la kolumno, kiun ni komparas, en la dua tabelo. La esprimo estas de la sekva tipo:

   = A2 = D2

   

   Kvankam, kompreneble, en ĉiu kazo la koordinatoj estos malsamaj, sed la esenco restos la sama.

2. Alklaku la butonon Eniruakiri komparajn rezultojn. Kiel vi povas vidi, kiam oni komparas la unuajn ĉelojn de ambaŭ listoj, la programo indikis indikilon "VERA"kio signifas datumaranĝon.



3. Nun ni devas fari similan operacion kun la ceteraj ĉeloj de ambaŭ tabeloj en la kolumnoj, kiujn ni komparas. Sed vi povas simple kopii la formulon, kiu signife ŝparos tempon. Ĉi tiu faktoro estas aparte grava dum komparado de listoj kun granda nombro de linioj.

   La kopiado-proceduro estas plej facila por uzi per la pleniga tenilo. Ni metas la kursoron sur la maldekstran angulon de la ĉelo, kie ni ricevis la indikilon "VERA". Samtempe ĝi devas konvertiĝi al nigra kruco. Jen la pleniga markilo. Alklaku la maldekstran musbutonon kaj trenu la kursoron laŭ la nombro de linioj en la komparataj tabelaj linioj.



4. Kiel ni vidas, nun en la aldona kolumno ĉiuj rezultoj de datumaj komparoj en du kolumnoj de tabulaj tabeloj montras. En nia kazo, la datumoj ne kongruis nur en unu linio. Kompare, la formulo donis la rezulton "Malvero". Por ĉiuj aliaj linioj, kiel vi povas vidi, la komparo-formulo donis la indikilon "VERA".



5. Aldone, eblas kalkuli la nombron da diferencoj uzante specialan formulon. Por fari tion, elektu la elementon de la folio, kie ĝi estos montrata. Tiam alklaku la ikonon "Enmetu funkcion".



6. En la fenestro Funkcio majstroj en grupo de operatoroj "Matematika" elektu la nomon SUMPRODUTO. Alklaku la butonon "Bone".



7. La funkcia argumenta fenestro estas aktivigita. SUMPRODUTOkies ĉefa tasko estas kalkuli la sumon de la produktoj de la elektita intervalo. Sed ĉi tiu funkcio povas esti uzata por niaj celoj. Ia sintakso estas sufiĉe simpla:

   = SUMPRODUTO (tabelo1; tabelo2; ...)

   Entute vi povas uzi adresojn de ĝis 255 tabeloj kiel argumentojn. Sed en nia kazo ni uzos nur du arojn, krome, kiel unu argumento.

   Metu la kursoron sur la kampon "Massive1" kaj elektu la komparatan dataran intervalon en la unua areo sur la folio. Post tio ni markis sur la kampo. "ne egala" () kaj elektu la kompare distancon de la dua regiono. Poste, enigu la rezultan esprimon per krampoj, antaŭ kiu ni metas du signojn "-". En nia kazo, ni ricevas la jenan esprimon:

   - (A2: A7D2: D7)

   Alklaku la butonon "Bone".



8. La operatoro kalkulas kaj montras la rezulton. Kiel ni vidas, en nia kazo la rezulto egalas al la nombro "1"tio signifas, ke en komparitaj listoj troviĝis malegaleco. Se la listoj estus tute identaj, la rezulto egalus al la nombro "0".

En la sama maniero, vi povas kompari datumojn en tabeloj situantaj sur malsamaj folioj. Sed en ĉi tiu kazo estas dezirinde, ke la linioj en ili estu numeritaj. La cetero de la komparebla proceduro estas preskaŭ ekzakte sama kiel priskribita supre, escepte de la fakto ke kiam vi faras formulon, vi devas interŝanĝi inter la folioj. En nia kazo, la esprimo havas la sekvan formon:

= B2 = Folio2! B2

Tio estas, kiel ni vidas, antaŭ ol la koordinatoj de la datumoj, kiuj troviĝas sur aliaj folioj, diferencas de kie la rezulto de la komparo estas montrita, la nombro de la folio kaj la ekkrio estas indikitaj.

Metodo 2: Elektu Grupojn de Ĉeloj

Komparo povas esti farita uzante la ĉelan grupan elekta ilon. Per ĝi vi ankaŭ povas kompari nur sinkronigitajn kaj ordigitajn listojn. Krome, en ĉi tiu kazo, la listoj estu lokitaj unu apud la alia sur la sama folio.

1. Elektu la komparajn arojn. Iru al la langeto "Hejmo". Poste alklaku la ikonon "Trovi kaj reliefigi"kiu situas sur la bendo en la bloko de iloj Redaktado. Listo malfermiĝas en kiu vi devas elekti pozicion. "Elektanta grupo de ĉeloj ...".

   

   Krome, en la dezirata fenestro de selektado de grupo de ĉeloj oni povas aliri laŭ alia maniero. Ĉi tiu opcio estos speciale utila al tiuj uzantoj, kiuj instalis la version de la programo pli frue ol Excel 2007, ĉar la metodo per la butono "Trovi kaj reliefigi" Ĉi tiuj aplikaĵoj ne subtenas. Elektu la arojn, kiujn ni volas kompari, kaj premu la klavon F5.

2. Malgranda transira fenestro estas aktivigita. Alklaku la butonon "Emfazi ..." en ĝia malsupra maldekstra angulo.



3. Post tio, kiu ajn el la supraj du elektoj, kiujn vi elektas, fenestro por elekti grupojn de ĉeloj lanĉiĝas. Ŝanĝu la ŝaltilon al pozicio "Elektu laŭ vico". Alklaku la butonon "Bone".



4. Kiel vi povas vidi, post ĉi tio, la nekompareblaj valoroj de la vicoj estos emfazitaj per alia nuanco. Aldone, kiel oni povas juĝi per la enhavoj de la formullinio, la programo faros unu el la ĉeloj aktivaj en la specifitaj nekompareblaj linioj.

Metodo 3: Kondiĉa Formatado

Vi povas kompari uzante la kondiĉan formatan metodon. Kiel en la antaŭa metodo, la komparataj areoj devus esti sur la sama Excel-labortablo kaj esti sinkronigitaj unu kun la alia.

1. Unue ni elektas la tabelan spacon, kiun ni konsideros la ĉefan kaj kiun serĉi diferencojn. Laste ni faros en la dua tablo. Sekve, elektu la liston de dungitoj en ĝi. Moviĝante al la langeto "Hejmo", alklaku la butonon "Kondiĉa Formatado"kiu situas sur la bendo en la bloko "Stiloj". El la falmenuo, daŭrigu "Rega Administrado".



2. La regulo-fenestro estas aktivigita. Ni premas ĝin sur la butono "Krei regulon".



3. En la lanĉa fenestro elektu pozicion "Uzu formulon". En la kampo "Formati ĉelojn" skribu la formulon, kiu enhavas la adresojn de la unuaj ĉeloj de la teritorioj de la komparataj kolumnoj, apartigitaj per la signo "ne egala" (). Nur ĉi tiu esprimo havas signon ĉi-foje. "=". Aldone, absoluta adresado devas esti aplikita al ĉiuj kolumnaj koordinatoj en ĉi tiu formulo. Por fari tion, elektu la formulon per la kursoro kaj alklaku trifoje la klavon F4. Kiel vi vidas, dolara signo aperis proksime al ĉiuj kolumnaj adresoj, kio signifas igi ligilojn en absolutajn. Por nia aparta kazo, la formulo prenos la jenan formon:

   = $ A2 $ D2

   Ni skribas ĉi tiun esprimon en la supra kampo. Post tio alklaku la butonon "Formato ...".



4. Aktivigita fenestro "Formati ĉelojn". Iru al la langeto "Plenigi". Ĉi tie en la listo de koloroj ni ĉesas elekti la koloron per kiu ni volas kolori tiujn elementojn, kie la datumoj ne egalos. Ni premas la butonon "Bone".



5. Revenante al la fenestro por krei formatan regulon, alklaku la butonon. "Bone".



6. Post aŭtomate moviĝanta al la fenestro Administranto de Reguloj alklaku la butonon "Bone" kaj en ĝi.



7. Nun en la dua tabelo, elementoj, kiuj havas datumojn, kiuj ne kongruas kun la respondaj valoroj de la unua tabula areo, estos emfazitaj en la elektita koloro.

Estas alia maniero uzi kondiĉan formatadon por plenumi la taskon. Kiel la antaŭaj opcioj, ĝi postulas la lokon de ambaŭ komparataj areoj sur la sama folio, sed malkiel la antaŭe priskribitaj metodoj, la kondiĉo por sinkronigi aŭ ordigi la datumojn ne estos necesa, kio distingas ĉi tiun elekton de la antaŭe priskribitaj.

1. Faru selektadon de areoj, kiuj devas esti komparataj.



2. Efektivigi transiron al la langeto nomata "Hejmo". Alklaku la butonon. "Kondiĉa Formatado". En la aktivigita listo, elektu la pozicion "Reguloj por ĉela selektado". En la sekva menuo ni elektas pozicion. "Duplikataj valoroj".



3. La fenestro por agordi la elekton de duplikataj valoroj estas lanĉita. Se vi faris ĉion ĝuste, tiam en ĉi tiu fenestro restas nur alklaki la butonon. "Bone". Kvankam, se vi volas, vi povas elekti malsaman elekton koloron en la responda kampo de ĉi tiu fenestro.



4. Post kiam ni plenumas la specifitan agon, ĉiuj duplikataj elementoj emfazos en la elektita koloro. Tiuj elementoj, kiuj ne kongruas, restos koloraj laŭ sia originala koloro (defaŭlte blanka). Tiel, vi povas tuj vidi kia estas la diferenco inter tabeloj.

Se vi volas, vi povas, kontraŭe, pentri elementojn ne-koincidantajn, kaj tiuj indikiloj, kiuj kongruas, povas resti kun la sama kolora plenigo. En ĉi tiu kazo, la algoritmo de agoj estas preskaŭ la sama, sed en la agordoj fenestro por reliefigi duplikatajn valorojn en la unua kampo anstataŭ la parametro "Duobligi" elektu opcion "Unika". Post tio, alklaku la butonon "Bone".

Tiel, oni reliefigos tiujn indikilojn, kiuj ne egalas.

Leciono: Kondiĉa Formatado en Excel

Metodo 4: kompleksa formulo

Vi povas ankaŭ kompari datumojn uzante kompleksan formulon, kiu baziĝas sur la funkcio COUNTES. Uzante ĉi tiun ilon, vi povas kalkuli, kiom ĉiu elemento de la elektita kolumno en la dua tablo ripetas en la unua.

Telefonisto COUNTES rilatas al statistika grupo de funkcioj. Lia tasko estas kalkuli la nombron de ĉeloj kies valoroj kontentigas donitan kondiĉon. La sintakso de ĉi tiu operatoro estas jene:

= FASOJ (teritorio; kriterio)

Argumento "Montaro" estas la adreso de la tabelo en kiu kalkulas la kongruaj valoroj.

Argumento "Kriterio" fiksas la matĉan kondiĉon. En nia kazo, ĝi estos la koordinatoj de specifaj ĉeloj en la unua tabla spaco.

1. Elektu la unuan elementon de la aldona kolumno, en kiu kalkulos la nombron de matĉoj. Poste alklaku la ikonon "Enmetu funkcion".



2. Lanĉo okazas Funkcio majstroj. Iru al kategorio "Statistika". Trovu en la listo la nomon "KUNTAJ". Post kiam vi elektas ĝin, alklaku la butonon. "Bone".



3. La operatora argumenta fenestro estas lanĉita. COUNTES. Kiel vi povas vidi, la nomoj de la kampoj en ĉi tiu fenestro respondas al la nomoj de la argumentoj.

   Metu la kursoron sur la kampon "Montaro". Post tio, tenante la maldekstran musbutonon, elektu ĉiujn valorojn de la kolumno kun la nomoj de la dua tabelo. Kiel vi povas vidi, la koordinatoj tuj falas en la specifitan kampon. Sed por niaj celoj, ĉi tiu adreso devas fariĝi absoluta. Por fari tion, elektu la koordinatojn en la kampo kaj alklaku la klavon F4.

   Kiel vi povas vidi, la ligo prenis absolutan formon, kiu karakterizas la ĉeesto de dolaraj signoj.

   Poste iru al la kampo "Kriterio"fiksante la kursoron tie. Ni alklakas la unuan elementon kun familnomoj en la unua tabelaro. En ĉi tiu kazo, lasu la relativan ligon. Post kiam ĝi estas montrita en la kampo, vi povas alklaki la butonon "Bone".



4. La rezulto estas montrita en la folia elemento. I egalas al la nombro "1". Ĉi tio signifas ke en la listo de nomoj de la dua tablo la familinomo "Grinev V.P."kiu estas la unua en la listo de la unua tabildaro, okazas unufoje.



5. Nun ni bezonas krei similan esprimon por ĉiuj aliaj elementoj de la unua tabelo. Por fari tion, kopiu ĝin per la pleniga markilo, kiel ni faris antaŭe. Metu la kursoron en la suban dekstran parton de la folia elemento, kiu enhavas la funkcion COUNTES, kaj konvertinte ĝin al la pleniga markilo, tenu la maldekstran musbutonon kaj trenu la kursoron suben.



6. Kiel vi povas vidi, la programo kalkulis kongruojn komparante ĉiun ĉelon de la unua tabelo kun datumoj, kiuj troviĝas en la dua tabelaro. En kvar kazoj, la rezulto aperis "1", kaj en du kazoj - "0". Tio estas, la programo ne povis trovi en la dua tabelo la du valorojn en la unua tabula tabelo.

Kompreneble, ĉi tiu esprimo por kompari la tabulajn indikilojn povas apliki en la ekzistanta formo, sed estas ŝanco plibonigi ĝin.

Ni faru, ke tiuj valoroj haveblaj en la dua tabelo, sed mankas en la unua, montriĝu en aparta listo.

1. Unue ni redaktu nian formulon COUNTES, nome igi ĝin unu el la argumentoj de la operatoro SE. Por fari tion, elektu la unuan ĉelon en kiu troviĝas la operatoro COUNTES. En la formala stango antaŭ ĝi ni aldonas la esprimon "SE" sen citaĵoj kaj malfermu la krampon. Poste, por plifaciligi nin labori, ni elektas la valoron en la formala stango. "SE" kaj alklaku la ikonon "Enmetu funkcion".



2. La funkcia argumenta fenestro malfermiĝas. SE. Kiel vi povas vidi, la unua kampo de la fenestro estas jam plenigita kun la valoro de la operatoro. COUNTES. Sed ni devas aldoni ion alian en ĉi tiu kampo. Ni fiksas la kursoron tie kaj ni aldonas la jam ekzistantan esprimon "=0" sen citaĵoj.

   Post tio iru al la kampo "Valoro se vera". Ĉi tie ni uzos alian neston-funkcion - LINIO. Enigu la vorton "LINIO" sen citaĵoj, tiam malfermu la parentezojn kaj specifas la koordinatojn de la unua ĉelo kun la nomo en la dua tabelo, poste fermu la krampojn. Specife, en nia kazo sur la kampo "Valoro se vera" akiris la jenan esprimon:

   LINIO (D2)

   Nun la telefonisto LINIO raportos funkciojn SE la linia numero en kiu troviĝas la specifa familinomo, kaj en la kazo kiam la kondiĉo difinita en la unua kampo plenumas, la funkcio SE donos ĉi tiun numeron al la ĉelo. Ni premas la butonon "Bone".



3. Kiel vi povas vidi, la unua rezulto estas montrita kiel "Malvero". Ĉi tio signifas, ke la valoro ne kontentigas la kondiĉojn de la operatoro. SE. Tio estas, la unua familinomo ĉeestas en ambaŭ listoj.



4. Per la pleniga markilo, laŭ la kutima maniero ni kopias la esprimon de la operatoro SE sur la tuta kolumno. Kiel vi povas vidi, en du pozicioj en la dua tabelo, sed ne en la unua, la formulo donas liniojn.



5. Retiriĝu de la tablotago dekstre kaj plenigu la kolumnon kun numeroj en ordo, komencante de 1. La nombro de nombroj devas kongrui kun la nombro de vicoj en la dua komparata tablo. Por plirapidigi la numeradprocedon, vi povas ankaŭ uzi la plenigilon.



6. Post tio, elektu la unuan ĉelon dekstre de la kolumno kun numeroj kaj alklaku la ikonon "Enmetu funkcion".



7. Malfermas Funkciaj Sorĉisto. Iru al kategorio "Statistika" kaj elektu nomojn "LA NOMO". Alklaku la butonon "Bone".



8. Funkcio LA Malofta, kies argumentoj estas malfermitaj, desegnita por montri la plej malaltan valoron difinitan de la konto.

   En la kampo "Array" specifi la koordinatojn de la limo de la aldona kolumno "Nombro da matĉoj"kiujn ni antaŭe konvertis per la funkcio SE. Ni faras ĉiujn ligojn absolutaj.

   En la kampo "K" indiku kian konton plej malplej valoras montri. Ĉi tie ni indikas la koordinatojn de la unua ĉelo de la kolumno kun numerado, kiun ni ĵus aldonis. La adreso restas relativa. Alklaku la butonon "Bone".



9. La operatoro montras la rezulton - la nombro 3. Ĉi tio estas la plej malgranda numerado de neparencaj vicoj de tabelaj aranĝoj. Uzante la pleniga markilo, kopiu la formulon al la fundo.



10. Nun, konante la linio-numerojn de la ne-kongruaj elementoj, ni povas enigi la ĉelon kaj iliajn valorojn uzante la funkcion INDICE. Elektu la unuan elementon de la folio kun la formulo LA Malofta. Post tio iru al la formulo linio kaj antaŭ la nomo "LA NOMO" aldonu nomon INDICE sen citaĵoj, tuj malfermu la krampon kaj metu punktokomon (;). Tiam elektu la nomon en la formala stango. INDICE kaj alklaku la ikonon "Enmetu funkcion".



11. Post tio malfermiĝas malgranda fenestro, en kiu vi devas determini ĉu la referenco havos funkcion INDICE aŭ desegnita por labori kun tabeloj. Ni bezonas la duan eblon. I estas difinita defaŭlte, do en ĉi tiu fenestro simple alklaku la butonon. "Bone".



12. La funkcia argumento fenestro komenciĝas. INDICE. Ĉi tiu aserto estas desegnita por montri la valoron, kiu troviĝas en specifa tabelo en la specifa linio.

    Kiel vi povas vidi, la kampo "Linia nombro" jam plenigita per funkciaj valoroj LA Malofta. De la valoro kiu jam ekzistas tie, subtrahu la diferencon inter la numerado de la Excel-folio kaj la interna numerado de la tabula areo. Kiel vi povas vidi, super la tablaj valoroj ni havas nur ĉapon. Ĉi tio signifas, ke la diferenco estas unu linio. Tial ni aldonas en la kampo "Linia nombro" signifo "-1" sen citaĵoj.

    En la kampo "Array" specifi la adreson de la valoro de la dua tabelo. Samtempe ni faras ĉiujn koordinatojn absolutaj, tio estas, ni metas dolarajn signojn antaŭ ili laŭ la maniero priskribita de ni antaŭe.

    Ni premas la butonon "Bone".



13. Post elirado de la rezulto al la ekrano, ni streĉas la funkcion per la pleniga markilo ĝis la fino de la kolumno malsupren. Kiel vi povas vidi, ambaŭ familnomoj, kiuj ĉeestas en la dua tabelo, sed ne en la unua, estas montritaj en aparta intervalo.

Metodo 5: Komparante arojn en malsamaj libroj

Komparinte intervalojn en malsamaj libroj, vi povas uzi la metodojn listigitajn supre, ekskludante tiujn eblojn, kiuj postulas la lokon de ambaŭ tablaj spacoj sur unu folio. La ĉefa kondiĉo por efektivigi la komparan proceduron en ĉi tiu kazo estas malfermi la fenestrojn de ambaŭ dosieroj samtempe. Ne estas problemoj por versioj de Excel 2013 kaj poste, kaj ankaŭ por versioj antaŭ Excel 2007. Sed en Excel 2007 kaj Excel 2010, por malfermi ambaŭ fenestrojn samtempe, aldonaj manipuladoj necesas. Kiel fari tion oni priskribas en aparta leciono.

Leciono: Kiel malfermi Excel-on en diversaj fenestroj

Kiel vi povas vidi, estas multaj ebloj kompari tablojn unu kun la alia. Kiu opcio uzi dependas precize kie la tabulaj datumoj situas unu al la alia (sur unu folio, en diversaj libroj, en malsamaj folioj), kaj ankaŭ kiel la uzanto volas, ke ĉi tiu komparo estu montrita sur la ekrano.