Komparu Metodojn en Microsoft Excel

Tre ofte, Excel-uzantoj alfrontas la taskon kompari du tablojn aŭ listojn por identigi diferencojn aŭ mankojn. Ĉiu uzanto traktas ĉi tiun taskon laŭ sia maniero, sed plej ofte oni pasigas sufiĉe grandan tempon por solvi ĉi tiun aferon, ĉar ne ĉiuj aliroj al ĉi tiu problemo estas raciaj. Samtempe, ekzistas pluraj provitaj agaj algoritmoj, kiuj ebligos vin kompari listojn aŭ tabelajn tabelojn en sufiĉe mallonga tempo kun minimuma penado. Ni rigardu pli detale ĉi tiujn eblojn.

Vidu ankaŭ: Komparo de du dokumentoj en MS Word

Komparaj metodoj

Estas tre kelkaj manieroj kompari tablajn spacojn en Excel, sed ili ĉiuj povas esti dividitaj en tri grandajn grupojn:

Surbaze de ĉi tiu klasifiko, unue oni elektas komparajn metodojn, kaj ankaŭ specifaj agoj kaj algoritmoj estas determinitaj por la tasko. Ekzemple, kiam oni komparas en malsamaj libroj, oni bezonas malfermi du Excel-dosierojn samtempe.

Krome, oni devas diri, ke kompari tabelajn areojn havas sencon nur kiam ili havas similan strukturon.

Metodo 1: simpla formulo

La plej facila maniero kompari datumojn en du tabeloj estas uzi simplan egalecan formulon. Se la datumoj kongruas, tiam ĝi donas la VENAN indikilon, kaj se ne, tiam FALSO. Vi povas kompari ambaŭ nombrajn kaj tekstajn datumojn. 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 la saman nombron da linioj. Ni vidu kiel uzi ĉi tiun metodon praktike kun la ekzemplo de du tabloj metitaj sur unu folion.

Do, ni havas du simplajn tablojn kun listoj de dungitoj kaj iliaj salajroj. Necesas kompari la listojn de dungitoj kaj identigi malkonsekvencojn inter la kolumnoj en kiuj estas metitaj la nomoj.

1. Por fari tion, ni bezonas aldonan kolumnon sur la folio. Ni eniras signon tie "=". Poste ni alklakas la unuan eron, kiun vi volas kompari en la unua listo. Ni denove metas la simbolon "=" de la klavaro. Tuj poste alklaku la unuan ĉelon de la kolumno, kiun ni komparas en la dua tablo. La rezulto estas esprimo 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 Enirupor akiri komparon rezultoj. Kiel vi povas vidi, kiam oni komparis la unuajn ĉelojn de ambaŭ listoj, la programo indikis indikilon "VERA", kio signifas datumojn.



3. Nun ni bezonas efektivigi similan operacion kun la aliaj ĉeloj de ambaŭ tabloj en la kolumnoj, kiujn ni komparas. Sed vi simple povas kopii la formulon, kio signife ŝparos tempon. Ĉi tiu faktoro estas precipe grava kiam oni komparas listojn kun granda nombro da linioj.

   La kopia proceduro plej facile plenumas per la pleniga markilo. Ni ŝvebas super la malsupra dekstra angulo de la ĉelo, kie ni ricevis la indikilon "VERA". Samtempe ĝi transformiĝu al nigra kruco. Ĉi tiu estas la markilo. Ni premas la maldekstran musbutonon kaj trenas la kursoron sur la nombron de linioj en la komparitaj tabelaj tabeloj.



4. Kiel vi povas vidi, nun en plia kolumno aperas ĉiuj rezultoj de komparo de datumoj en du kolumnoj de tabelaj tabeloj. En nia kazo, la datumoj sur nur unu linio ne kongruis. Komparante ilin, la formulo produktis la rezulton FALSO. Por ĉiuj aliaj linioj, kiel ni vidas, la kompara formulo produktis indikilon "VERA".



5. Krome eblas kalkuli la nombron da discrepancoj per speciala formulo. Por fari tion, elektu la elementon de la folio, kie ĝi aperos. Tiam alklaku la ikonon "Enmetu funkcion".



6. En la fenestro Funkciaj Sorĉistoj en grupo da telefonistoj "Matematika" elektu la nomon SUMPRODUCTO. Alklaku la butonon "Bone".



7. La fenestra funkcia argumento estas aktiva. SUMPRODUCTOkies ĉefa tasko estas kalkuli la sumon de la produktoj de la elektita gamo. Sed ĉi tiu funkcio uzeblas por niaj celoj. La sintakso estas sufiĉe simpla:

   = SUMPRODUCTO (array1; array2; ...)

   Entute, adresoj ĝis 255 tabeloj povas esti uzataj kiel argumentoj. Sed en nia kazo, ni uzos nur du tabelojn aldone kiel unu argumenton.

   Metu la kursoron sur la kampo "Array1" kaj elektu sur la folio la komparitajn datumojn en la unua areo. Post tio, metu signon en la kampon ne egala () kaj elektu la komparitan gamon de la dua regiono. Tuj poste, envolvu la rezultan esprimon inter krampoj antaŭ kiuj ni metas du signojn "-". En nia kazo, ĉi tiu esprimo rezultis:

   - (A2: A7D2: D7)

   Alklaku la butonon "Bone".



8. La telefonisto kalkulas kaj montras la rezulton. Kiel vi povas vidi, en nia kazo, la rezulto egalas al la nombro "1"tio estas, ke ĝi signifas, ke unu misprezento estis trovita en la komparitaj listoj. Se la listoj estus tute identaj, tiam la rezulto egalus al la nombro "0".

Sammaniere vi povas kompari datumojn en tabloj, kiuj troviĝas sur malsamaj littukoj. Sed ĉi-kaze estas dezirinde, ke la linioj en ili estu numeritaj. Alie, la kompara proceduro estas preskaŭ ekzakte la sama kiel priskribita supre, krom la fakto, ke kiam vi enigas la formulon, vi devas ŝanĝi inter folioj. En nia kazo, la esprimo aspektos tiel:

= B2 = Folio2! B2

Tio estas, kiel ni vidas, antaŭ la koordinatoj de la datumoj, kiuj situas sur aliaj folioj, krom kie la rezulto de la komparo estas montrita, la folia numero kaj ekkemia marko estas indikitaj.

Metodo 2: elektu celgrupojn

Komparo povas fariĝi per la selektilo de ĉelgrupoj. Ĝi ankaŭ povas esti uzata por kompari nur sinkronigitajn kaj ordigitajn listojn. Krome ĉi-kaze la listoj estu lokitaj unu apud la alia sur la sama folio.

1. Ni elektas la komparitajn tabelojn. Iru al la langeto "Hejmo". Tuj poste alklaku la ikonon Trovu kaj Resaltusituanta sur la rubando en la ilobreto "Redaktado". Listo malfermiĝas en kiu elekti pozicion "Elekto de grupo de ĉeloj ...".

   

   Krome ni povas atingi la deziratan fenestron por elekti grupon de ĉeloj alimaniere. Ĉi tiu opcio estos precipe utila por tiuj uzantoj, kiuj instalis version de la programo pli frue ol Excel 2007, ekde la metodo per la butono Trovu kaj Resaltu ĉi tiuj aplikoj ne subtenas. Ni elektas la tabelojn, kiujn ni volas kompari, kaj premu la klavon F5.

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



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



4. Kiel vi povas vidi, post ĉi tio la malsimpatiaj valoroj de la linioj resaltos kun malsama nuanco. Krome, kiel oni povas juĝi el la enhavo de la formulo-stango, la programo aktivigos unu el la ĉeloj situantaj en la specifitaj nerangigitaj linioj.

Metodo 3: kondiĉa formatado

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

1. Unue ni elektas, kiun tabloparton ni konsideros ĉefa, kaj en kiu serĉi diferencojn. Ni faru la lastan en la dua tablo. Tial ni elektas la liston de laboristoj lokitaj en ĝi. Per movo al la langeto "Hejmo"alklaku la butonon Kondiĉa formatadokiu situas sur la bendo en la bloko Stiloj. El la faliga listo, iru al Regularo-Administrado.



2. La fenestra administranto de reguloj estas aktivigita. Alklaku la butonon en ĝi Krei Regulon.



3. En la fenestro, kiu komenciĝas, elektu la pozicion Uzu Formulon. En la kampo "Formato Ĉeloj" skribu formulon enhavantan la adresojn de la unuaj ĉeloj de la intervaloj de la komparitaj kolumnoj, apartigitaj per signo "ne egala") Ĉi-foje nur ĉi tiu esprimo alfrontos. "=". Krome, absoluta adresado devas esti aplikita al ĉiuj kolumnaj koordinatoj en ĉi tiu formulo. Por fari tion, elektu la formulon per la kursoro kaj premu la klavon tri fojojn F4. Kiel vi povas vidi, dolara signo aperis proksime al ĉiuj kolumnaj adresoj, kio signifas igi ligojn al absolutaj. 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. Fenestro estas aktivigita Ĉela Formato. Iru al la langeto "Plenigi". Ĉi tie en la listo de koloroj ni ĉesigas la elekton pri la koloro, per kiu ni volas kolorigi tiujn elementojn, kie la datumoj ne kongruas. Alklaku la butonon "Bone".



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



6. Post aŭtomate translokiĝo al la fenestro Direktoro de Reguloj alklaku la butonon "Bone" kaj en ĝi.



7. Nun en la dua tabelo, elementoj kun datumoj kiuj ne koincidas kun la respondaj valoroj de la unua tabela areo estos reliefigitaj per la elektita koloro.

Ekzistas alia maniero apliki kondiĉan formatadon al la tasko. Kiel la antaŭaj opcioj, ĝi postulas la lokon de ambaŭ komparitaj areoj sur la sama folio, sed male al la antaŭe priskribitaj metodoj, la kondiĉo por sinkronigado aŭ ordigo de datumoj ne estos deviga, kio distingas ĉi tiun opcion de la antaŭe priskribita.

1. Ni elektas la areojn por kompari.



2. Iru al la nomata langeto "Hejmo". Alklaku la butonon Kondiĉa formatado. En la aktivigita listo, elektu la pozicion Reguloj pri Selektado de Ĉeloj. En la sekva menuo ni elektas pozicion Duplikataj Valoroj.



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



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

Se vi volas, kontraŭe, kolorigi la nekompatitajn elementojn, kaj tiuj indikiloj, kiuj kongruas, lasas la plenigon kun la sama koloro. En ĉi tiu kazo, la algoritmo de agoj estas preskaŭ la sama, sed en la fenestro de agordoj por reliefigi duplikatajn valorojn en la unua kampo anstataŭ la parametro Duobligi devus elekti "Unika". Post tio, alklaku la butonon "Bone".

Tiel ĝuste tiuj indikiloj kiuj ne koincidas.

Leciono: Kondiĉa formatado en Excel

Metodo 4: kompleksa formulo

Vi ankaŭ povas kompari datumojn per kompleksa formulo bazita sur la funkcio LANDO. Uzante ĉi tiun ilon, vi povas kalkuli kiom ripetas ĉiu elemento el la elektita kolumno de la dua tablo en la unua.

Funkciigisto LANDO rilatas al statistika grupo de funkcioj. Lia tasko estas kalkuli la nombron de ĉeloj, kies valoroj kontentigas difinitan kondiĉon. La sintakso de ĉi tiu telefonisto estas jena:

= COUNTIF (gamo; kriterio)

Argumento "Gamo" reprezentas la adreson de la tabelo, en kiu oni kalkulas la egalajn valorojn.

Argumento "Kriterio" starigas matĉan kondiĉon. En nia kazo, ĝi estos la koordinatoj de specifaj ĉeloj en la unua tabela areo.

1. Ni elektas la unuan elementon de la aldona kolumno, en kiu nombriĝos la nombro de matĉoj. Tuj poste alklaku la ikonon "Enmetu funkcion".



2. Komencante Funkciaj Sorĉistoj. Iru al la kategorio "Statistika". Trovu la nomon en la listo "COUNTIF". Post elekti ĝin, alklaku la butonon "Bone".



3. Lanco Fenestra Argumento Lanĉas LANDO. Kiel vi povas vidi, la nomoj de la kampoj en ĉi tiu fenestro respondas al la nomoj de la argumentoj.

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

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

   Poste iru al la kampo "Kriterio"fiksante tie la kursoron. Ni alklakas la unuan elementon kun familinomoj en la unua tabela gamo. En ĉi tiu kazo, lasu la ligon parenca. Post kiam ĝi montriĝas sur 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 tabela tabelo, okazas unufoje.



5. Nun ni bezonas krei similan esprimon por ĉiuj aliaj elementoj de la unua tablo. Por fari tion, ni kopios per la pleniga markilo, kiel ni jam faris antaŭe. Metu la kursoron en la malsupran dekstran parton de la folia elemento, kiu enhavas la funkcion LANDOkaj post konverti ĝin al pleniga markilo, tenu la maldekstran musbutonon kaj trenu la kursoron malsupren.



6. Kiel vi povas vidi, la programo kalkulis la koincidojn per komparo de ĉiu ĉelo de la unua tablo kun datumoj lokitaj en la dua tabela gamo. En kvar kazoj la rezulto aperis "1", kaj en du kazoj - "0". Tio estas, la programo ne povis trovi en la dua tabelo du valorojn, kiuj estas en la unua tabela tabelo.

Kompreneble ĉi tiu esprimo, por kompari tabelajn indikilojn, povas esti uzata en ĝia ekzistanta formo, sed ekzistas ebleco plibonigi ĝin.

Ni certigas, ke tiuj valoroj, kiuj estas en la dua tabelo, sed ne en la unua, estas montritaj en aparta listo.

1. Unue ni iom rebonigos nian formulon LANDOnome, ni igas ĝin unu el la argumentoj de la telefonisto SE. Por fari tion, elektu la unuan ĉelon, en kiu troviĝas la telefonisto LANDO. En la linio de formuloj antaŭ ĝi aldoni la esprimon SE sen citaĵoj kaj malfermu la krampon. Tuj poste, por plifaciligi nin labori, elektu la valoron en la formula stango SE kaj alklaku la ikonon "Enmetu funkcion".



2. La fenestro de funkciaj argumentoj malfermiĝas SE. Kiel vi povas vidi, la unua kampo de la fenestro jam plenas kun la valoro de la telefonisto LANDO. Sed ni bezonas aldoni ion alian al ĉi tiu kampo. Ni starigas la kursoron tie kaj aldonas al la ekzistanta esprimo "=0" sen citaĵoj.

   Post tio, iru al la kampo "Signifanta se vera". Ĉi tie ni uzos alian nestitan funkcion - LINIO. Enigu la vorton LINIO sen citiloj, tiam malfermu la krampojn kaj indiku la koordinatojn de la unua ĉelo kun la familinomo en la dua tablo, kaj poste fermu la krampojn. Specife, en nia kazo, sur la kampo "Signifanta se vera" La jena esprimo rezultis:

   LINE (D2)

   Nun la telefonisto LINIO raportos pri funkcioj SE la nombro de la linio, en kiu troviĝas aparta familinomo, kaj en la kazo, kiam la kondiĉo specifita en la unua kampo estas kontentigita, la funkcio SE aperos ĉi tiu numero en la ĉelo. Alklaku la butonon "Bone".



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



4. Uzante la plenigan markilon, ni kopias la esprimon de la telefonisto laŭ la kutima maniero SE sur la tuta kolumno. Kiel vi povas vidi, por du pozicioj, kiuj ĉeestas en la dua tabelo, sed ne en la unua, la formulo donas liniojn.



5. Ni foriras de la tablo-areo dekstren kaj plenigas la kolumnon per numeroj en ordo, komencante de 1. La nombro de nombroj devas kongrui kun la nombro de vicoj en la dua tabelo por esti komparata. Por rapidigi la nombran procezon, vi ankaŭ povas uzi la plenigitan markilon.



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



7. Malfermas Feature Sorĉisto. Iru al la kategorio "Statistika" kaj elektu la nomon "MULDA". Alklaku la butonon "Bone".



8. Funkcio LA MULINDAkies argumenta fenestro malfermiĝis, celas montri la plej malgrandan valoron specifitan en la konto.

   En la kampo Array precizigu la koordinatojn de la gamo de la aldona kolumno "Numero de Matĉoj"kiun ni antaŭe konvertis uzante la funkcion SE. Ni igas ĉiujn ligojn absolutaj.

   En la kampo "K" indikas, kiu konto devas esti montrata la plej malalta valoro. Ĉi tie ni indikas la koordinatojn de la unua ĉelo de la kolumno kun numerado, kiujn ni lastatempe aldonis. Ni lasas la adreson parenca. Alklaku la butonon "Bone".



9. La telefonisto montras la rezulton - numeron 3. Ĝi estas la plej malgranda el multnombraj vicoj de tabelaj tabeloj. Uzante la plenigan markilon, kopiu la formulon al tre fundo.



10. Nun, sciante la liniojn kaj nekompareblajn elementojn, ni povas enmeti en la ĉelon iliajn valorojn per la funkcio INDEX. Elektu la unuan elementon de la folio, kiu enhavas la formulon LA MULINDA. Post tio, iru al la linio de formuloj kaj antaŭ la nomo "MULDA" aldonu la nomon INDEX sen citaĵoj, tuj malfermu la krampon kaj metu punktan kompunon (;) Poste elektu la nomon en la linio de formuloj INDEX kaj alklaku la ikonon "Enmetu funkcion".



11. Post tio, malgranda fenestro malfermiĝas, en kiu vi devas determini la referencan vidon, havu funkcion INDEX aŭ desegnita por funkcii kun tabeloj. Ni bezonas la duan eblon. Ĝi estas instalita defaŭlte, do en ĉi tiu fenestro simple alklaku la butonon "Bone".



12. La funkcio argumenta fenestro komenciĝas INDEX. Ĉi tiu telefonisto celas elsendi valoron, kiu troviĝas en specifa tabelo en la specifita ĉeno.

    Kiel vi povas vidi, la kampo Linia numero jam plenplena de funkciaj valoroj LA MULINDA. De la valoro jam ekzistanta, la diferenco inter numerado de la Excel-folio kaj interna numerado de la tabela areo devas resti. Kiel vi povas vidi, ni havas nur kaplinion super la valoroj de la tablo. Ĉi tio signifas, ke la diferenco estas unu linio. Tial ni aldonas en la kampo Linia numero valoro "-1" sen citaĵoj.

    En la kampo Array precizigu la adreson de la gamo de valoroj de la dua tablo. Samtempe ni absolvas ĉiujn koordinatojn, tio estas, ni metas antaŭ ili la dolonan signon laŭ la maniero, kiun ni antaŭe priskribis.

    Alklaku la butonon "Bone".



13. Post montrado de la rezulto sur la ekrano, ni etendas la funkcion per la pleniga markilo al la fundo de la kolumno. Kiel vi povas vidi, ambaŭ familinomoj, kiuj ĉeestas en la dua tablo, sed ne estas en la unua, estas montritaj en aparta gamo.

Metodo 5: komparu tabelojn en malsamajn librojn

Komparante gamojn en malsamaj libroj, vi povas uzi ĉi-suprajn metodojn, krom tiuj ebloj, kie vi volas meti ambaŭ tabelajn areojn sur unu folion. La ĉefa kondiĉo por la kompara proceduro en ĉi tiu kazo estas malfermi la fenestrojn de ambaŭ dosieroj samtempe. Por versioj de Excel 2013 kaj postaj, kaj ankaŭ por versioj antaŭ Excel 2007, ne ekzistas problemoj kun ĉi tiu kondiĉo. Sed en Excel 2007 kaj Excel 2010, por malfermi ambaŭ fenestrojn samtempe, necesas pliaj manipuladoj. Kiel fari tion estas priskribita en aparta leciono.

Leciono: Kiel malfermi Excel en malsamaj fenestroj

Kiel vi povas vidi, estas kelkaj eblecoj kompari tabelojn inter si. Kiu opcio uzi dependas de kie ĝuste troviĝas la tabulaj datumoj rilate unu al la alia (sur unu folio, en malsamaj libroj, sur malsamaj littukoj), kaj ankaŭ pri kiel ĝuste la uzanto volas, ke ĉi tiu komparo aperu sur la ekrano.