Ofte, Excel-uzantoj havas taskon kompari du tabelojn aŭ listojn por identigi diferencojn en ili aŭ mankantaj aĵojn. Ĉiu uzanto okupas kun ĉi tiu tasko laŭ sia propra maniero, sed plej ofte iom da tempo pasas por solvi la specifan demandon, ĉar ne ĉiuj aliroj al ĉi tiu problemo estas racionalaj. Samtempe, estas pluraj pruvitaj agaj algoritmoj, kiuj permesos kompari listojn aŭ tablajn arojn en sufiĉe mallonga tempo kun minimuma konsiderinda peno. Ni konsideru detalojn ĉi tiujn opciojn.
En la sama maniero, vi povas kompari la datumojn en la tabloj kiuj troviĝas sur malsamaj folioj. Sed en ĉi tiu kazo, estas dezirinde, ke la linioj en ili estas numeritaj. La resto de la komparata procedo estas preskaŭ ĝuste la sama kiel priskribita supre, krom la fakto, ke kiam la formulo, necesas ŝanĝi inter tukoj. En nia kazo, la esprimo havos la sekvan formon:
= B2 = Listo2! B2
Tio estas, kiel ni vidas, antaŭ la datumaj koordinatoj, kiuj troviĝas sur aliaj folioj krom kie la komparata rezulto estas montrita, la folia nombro kaj ekkrio marko estas indikita.
Metodo 2: Selektado de grupoj de ĉeloj
Komparo povas esti farita uzante la ilon por la apartigo de grupoj de ĉeloj. Kun ĝi, vi ankaŭ povas kompari nur sinkronigitajn kaj menditajn listojn. Krome, en ĉi tiu kazo, listoj devas esti lokitaj unu apud la alia sur unu folio.
- Elektu komparitajn arojn. Iru al la langeto "Hejmo". Poste, alklaku la ikonon "Trovu kaj elektu", kiu situas sur la bendo en la redakta ilobreto. Estas listo en kiu vi devus elekti la pozicion "selektado de la grupo de ĉeloj ...".
Krome, la elekto de la grupo de ĉeloj povas esti atingita alimaniere. Ĉi tiu opcio estos speciale utila al tiuj uzantoj, kiuj pli frue havas la version de la programo ol Excel 2007, ĉar la metodo per la butono "Trovu kaj elektu" ne subtenas ĉi tiujn aplikaĵojn. Elektu arojn, kiuj volas kompari, kaj alklaku la klavon F5.
- Malgranda transira fenestro estas aktivigita. Alklaku la butonon "Emfazu ..." en ĝia malsupra maldekstra angulo.
- Post tio, kion ajn du el la supraj opcioj, kiujn vi elektis, la selekta fenestro de ĉelaj grupoj estas lanĉita. Instalu la ŝaltilon al la pozicio "Emfazu". Alklaku la butonon "OK".
- Kiel ni povas vidi, post tio, la malkonsekvencaj valoroj de la linioj reliefigos kun malsama nuanco. Krome, kiel povas esti juĝita de la enhavoj de la formulo linio, la programo faros aktiva unu el la ĉeloj en la indikitaj ne-koinciditaj linioj.
Metodo 3: Kondiĉa formatado
Vi povas kompari apliki kondiĉan formatan metodon. Kiel en la antaŭa metodo, komparitaj areoj devus esti sur sola Excel laboranta folio kaj esti sinkronigita inter si.
- Unue, ni elektas, kion la tabula regiono estos konsiderata la ĉefa, kaj en kio serĉi la diferencon. Laste ni faru en la dua tablo. Sekve, ni asignas liston de laboristoj en ĝi. Moviĝante al la langeto "Hejmo", alklaku la butonon "Kondiĉa Formatado", kiu havas lokon sur la bendo en la bloko "Stiloj". El la falmenuo, iru tra la "regulsistemo".
- La Fenestro pri Regulo de Regulo-Manaĝero estas aktivigita. Ni alklakas ĝin al la butono "Krei regulon".
- En la kuranta fenestro, ni elektas la pozicion "Uzu Formulon". En la kampo "Format Cell", skribu formulon enhavanta la adresojn de la unuaj ĉeloj de la teritorioj de komparitaj kolumnoj, apartigitaj per la signo "ne egala" (). Uste antaŭ ĉi tiu esprimo ĉi-foje staros la signo "=". Krome, al ĉiuj kolumnaj koordinatoj en ĉi tiu formulo, vi devas apliki absolutan adreson. Por fari tion, ni asignas la formulon kun la kursoro kaj alklaku la F4-klavon trifoje. Kiel ni povas vidi, la dolaro signo aperis proksime al ĉiuj kolumnaj adresoj, kio signifas turnante la ligojn al absoluta. Por nia aparta kazo, la formulo prenos la sekvan formon:
= $ A2 $ D2
Ni registras ĉi tiun esprimon en la supra kampo. Post tio, ni alklakas la butonon "Formato ...".
- La fenestro "ĉela formato" estas aktivigita. Ni iras al la langeto "Plenigu". Ĉi tie en la listo de koloroj ĉesas la elekton pri la koloro, kiun ni volas pentri tiujn elementojn, kie datumoj ne kongruas. Alklaku la butonon "OK".
- Revenante al la formato-regulo-fenestro, alklaku la butonon "OK".
- Post aŭtomata movado al la fenestro "Regulo", alklaku la butonon "OK" kaj en ĝi.
- Nun en la dua tablo, elementoj, kiuj havas datumojn malkonsentitajn kun la respondaj valoroj de la unua tablo-areo, estos reliefigitaj en la elektita koloro.
Estas alia maniero apliki kondiĉan formaton por plenumi la taskon. Kiel la antaŭaj opcioj, ĝi postulas la lokon de ambaŭ komparitaj areoj sur unu folio, sed kontraste al la antaŭe priskribitaj metodoj, la sinkroniga kondiĉo aŭ ordigaj datumoj ne estos deviga, kiu distingas ĉi tiun opcion de antaŭe priskribita.
- Ni produktas la elekton de areoj, kiujn vi bezonas kompari.
- Ni efektivigas la transiron al la langeto nomita "Hejmo". Ni alklakas la butonon "Kondiĉa Formatado". En la aktivigita listo, elektu la postenon "regulojn por la atribuo de ĉeloj". En la sekva menuo, faru la pozicion "ripetitan valorojn".
- La duplikataj valoroj estas lanĉitaj. Se vi ĉiuj estas ĝuste farita, tiam en ĉi tiu fenestro ĝi restas nur por alklaki la butonon "OK". Kvankam, se vi deziras, en la taŭga kampo de ĉi tiu fenestro, vi povas elekti alian koloron de la elekto.
- Post kiam ni produktas la specifitan agon, ĉiuj ripetaj elementoj reliefigos en la elektita koloro. Tiuj elementoj, kiuj ne koincidas, restos pentritaj en sia origina koloro (defaŭlte blanka). Tiel, vi povas tuj vidi vide por vidi, kia estas la diferenco inter tabeloj.
Se vi volas, vi povas, male, pentras la malkonsekvencajn elementojn, kaj la indikiloj, kiuj kongruas, foriras kun la plenigo de la antaŭa koloro. Samtempe, la algoritmo de ago estas preskaŭ la sama, sed en la agordoj de la duplikataj valoroj en la unua kampo, anstataŭ la "ripetita" parametro, elektu la "unikan" parametron. Post tio alklaku la butonon "OK".
Tiel, estas ĝuste tiuj indikiloj, kiuj ne koincidas.
Leciono: Kondiĉa formatado en Excel
Metodo 4: Ampleksa formulo
Ankaŭ komparu datumojn kun la helpo de kompleksa formulo, kies bazo estas la funkcio de la metro. Uzante ĉi tiun ilon, vi povas kalkuli kiom da ĉiu elemento de la elektita kolumno de la dua tablo ripetiĝas en la unua.
La operaciantoj referencas al la statistika grupo de funkcioj. Ia tasko estas kalkuli la nombron de ĉeloj, la valoroj en kiuj kontentigas la specifitan kondiĉon. La sintakso de ĉi tiu operatoro havas ĉi tiun specon:
= Horaroj (gamo; kriterio)
La argumento "gamo" estas la adreso de la tabelo, kiu kalkulas la koincidajn valorojn.
La argumento "Kriterioj" specifas la kondiĉon de la koincido. En nia kazo, ĝi estos la koordinatoj de la specifaj ĉeloj de la unua tabula areo.
- Ni asignas la unuan elementon de la aldona kolumno, en kiu la nombro de matĉoj estos kalkulita. Poste alklaku la ikonon "Enmetu funkcion".
- La funkcioj Sorĉisto komenciĝas. Iru al kategorio "statistika". Trovu en la listo la nomon "Counesscles". Post ĝia selektado, alklaku la butonon "OK".
- La fenestro de operatoro argumentas. Kiel ni vidas, la nomoj de la kampoj en ĉi tiu fenestro korespondas al la nomoj de la argumentoj.
Instalu la kursoron en la kampo "gamo". Post tio, tenante la maldekstran musbutonon, asignas ĉ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 farita de absoluta. Por fari ĉi tion, reliefigu la koordinatojn en la kampo kaj alklaku la F4-klavon.
Kiel vi povas vidi, la referenco prenis la absolutan formon, kiu estas karakterizita per la ĉeesto de dolaraj signoj.
Poste iru al la kampo "Kriterio" instalante la kursoron. Alklaku la unuan elementon kun la familinomoj en la unua tabula teritorio. En ĉi tiu kazo, lasu la ligilan parencon. Post kiam ĝi aperas en la kampo, vi povas alklaki la butonon "OK".
- La rezulto estas eligo al la folia elemento. I egalas al la nombro "1". Ĉi tio signifas, ke en la listo de nomoj de la dua tablo, la nomo "Grinev V. P.", kiu estas la unua en la listo de la unua tablo-tabelo, okazas unufoje.
- Nun ni bezonas krei similan esprimon kaj por ĉiuj aliaj elementoj de la unua tablo. Por fari tion, mi plenumos kopion, uzante la plenigadon, kiel ni jam faris antaŭe. Ni metas la kursoron al la malsupra dekstra parto de la folia elemento, kiu enhavas la funkcion de la metro, kaj post konvertado de ĝi al la plenigaĵo, krampo la maldekstra musbutono kaj tiri la kursoron malsupren.
- Kiel ni povas vidi, la programo faris kalkuladon de koincidoj, komparante ĉiun ĉelon de la unua tablo kun datumoj, kiuj troviĝas en la dua tabula gamo. En kvar kazoj, la rezulto estis "1", kaj en du kazoj - "0". Tio estas, la programo ne povis trovi la du valorojn en la dua tablo disponeblaj en la unua tablo-tabelo.
Kompreneble, ĉi tiu esprimo por kompari tablajn indikilojn, eblas apliki en la ekzistanta formo, sed eblas plibonigi ĝin.
Ni faros, ke la valoroj ekzistantaj en la dua tablo, sed ne haveblas en la unua, estis montritaj kiel aparta listo.
- Unue, ni recikligas nian formulon, sed ni faros ĝin unu el la argumentoj de la operatoro se. Por fari tion, ni reliefigas la unuan ĉelon, en kiu la operatoro estas la operatoro. En la formulo-ŝnuro antaŭ ĝi, ni aldonas la esprimon "se" sen citaĵoj kaj malfermu la krampon. Plue, tiel ke estis pli facile por ni labori, asigni la formulojn en la formulo vico kaj alklaki la "enmeti funkcion" ikonon.
- La funkcia argumenta fenestro malfermiĝas se. Kiel vi povas vidi, la unua fenestro de la fenestro jam estis plena de la taksado de la operaciisto. Sed ni devas aldoni ion alian en ĉi tiu kampo. Ni starigis tie la kursoron kaj la jam ekzistantan esprimon aldoni "= 0" sen citaĵoj.
Post tio, iru al la "signifo se vero" kampo. Ĉi tie ni uzos alian nestitan funkcion - linio. Enigu la vorton "ŝnuro" sen citaĵoj, tiam malfermi krampojn kaj indiki la koordinatojn de la unua ĉelo kun la familia nomo en la dua tablo, post kio ni fermas krampojn. Specife, en nia kazo en la "signifo se vera" kampo, la sekva esprimo okazis:
Vico (D2)
Nun la operatora ĉeno raportos funkciojn se la linia nombro en kiu la specifa familinomo situas, kaj en la kazo kiam la kondiĉo specifita en la unua kampo estos prezentita, la funkcio se ĉi tiu nombro estos montrata. Alklaku la butonon "OK".
- Kiel vi povas vidi, la unua rezulto estas montrita kiel "mensogoj". Ĉi tio signifas, ke la valoro ne kontentigas la kondiĉojn de la operatoro se. Tio estas, la unua familinomo ĉeestas en ambaŭ listoj.
- Uzante la plenigaĵon, kiu jam estas konata por kopii la esprimon de la operatoro se sur la tuta kolumno. Kiel ni vidas, en du pozicioj, kiuj ĉeestas en la dua tablo, sed ne en la unua, la formulo elsendas vicojn.
- Ni retiriĝas de la tablo-areo dekstre kaj plenigas la numeron de kolumno, ekde 1. La nombro de nombroj devas kongrui kun la nombro de vicoj en la dua komparebla tablo. Por rapidigi la numeran procedon, vi ankaŭ povas uzi la plenigaĵon.
- Post tio, ni reliefigas la unuan ĉelon dekstre de la parolanto kun la nombroj kaj alklaku la ikonon "Enmetu funkcion".
- Sorĉisto malfermiĝas. Iru al la kategorio "statistika" kaj produktas la nomon de la nomo "plej malgranda". Alklaku la butonon "OK".
- La funkcio estas la plej malgranda, kies argumentoj de la argumentoj estis malkaŝitaj, celas retiri la specifitan plej malgrandan valoron.
En la "tabelo" kampo, vi devus specifi la koordinatojn de la gamo de la laŭvola kolumno "la nombro de matĉoj", kiun ni antaŭe transformis uzante la funkcio se. Ni faras ĉiujn ligojn absolutaj.
En la kampo "K", ĝi indikas, kiu en la konto la plej malgranda valoro devas esti montrata. Ĉi tie ni indikas la koordinatojn de la unua ĉelo de la kolumno kun la numerado, kiun ni ĵus aldonis. Adreso forlasi parencon. Alklaku la butonon "OK".
- La operatoro montras la rezulton - Numeron 3. uste estas la plej malgranda el la numerado de la malkonsekvencaj linioj de tablaj tabeloj. Uzante la plenigaĵon, kopiu la formulon al la nazo mem.
- Nun, sciante la nombrojn de la vicoj de la nekompreneblaj elementoj, ni povas enmeti en la ĉelon kaj siajn valorojn per la indeksa funkcio. Elektu la unuan elementon de la folio enhavanta la formulon estas la plej malgranda. Post tio, iru al la liniaj formuloj kaj antaŭ la nomo de la "Plej malgranda" aldonas la nomon "Indekso" sen citaĵoj, tuj malfermas la krampon kaj metis punkton per komo (;). Tiam ni asignas la formulon nomon "indekso" en la linio kaj alklaku la ikonon "Alglui Funkcion".
- Post tio, malgranda fenestro malfermiĝas, en kiu necesas determini ĉu la referenca vidpunkto devus havi indeksan funkcion aŭ desegnita por labori kun tabeloj. Ni bezonas duan opcion. Ini estas instalita defaŭlte, tiel ke en ĉi tiu fenestro simple alklaku la butonon "OK".
- La argumenta fenestro prizorgas la indeksan funkcion. Ĉi tiu operatoro estas desegnita por produkti valoron situantan en specifa tabelo en la specifa linio.
Kiel vi povas vidi, la kampo "vico nombro" jam estas plena de la signifaj valoroj de la plej malgranda funkcio. De ekzistanta valoro tie, la diferenco inter la numerado de la Excel-folio kaj la interna numerado de la tabula regiono. Kiel ni vidas, ni nur havas ĉapelon sur la tablo valoroj. Ĉi tio signifas, ke la diferenco estas unu linio. Sekve, aldonu "-1" sen citaĵoj en la kampo "vico-numero".
En la kampo "Array", specifu la adreson de la valoro de la dua tablo-valoroj. Samtempe, ĉiuj koordinatoj faras absoluta, tio estas, ni metas la dolaran signon antaŭ la metodo priskribita de ni.
Alklaku la butonon "OK".
- Post retiriĝo, la rezulto sur la ekrano streĉas la funkcion per la plenigaĵo ĝis la fino de la kolumno. Kiel ni vidas, ambaŭ familinomoj, kiuj ĉeestas en la dua tablo, sed ne haveblas en la unua, estas forigitaj en aparta teritorio.
Metodo 5: Komparo de Arrays en malsamaj libroj
Komparinte la teritoriojn en malsamaj libroj, vi povas uzi la supran metodojn, ekskluzive tiujn opciojn, kie la lokigo de tabulaj areoj sur unu folio necesas. La ĉefa kondiĉo por la komparata procedo en ĉi tiu kazo estas la malfermo de la fenestroj de ambaŭ dosieroj samtempe. Por versioj de Excel 2013 kaj poste, same kiel por versioj por Excel 2007, ne ekzistas problemoj kun la efektivigo de ĉi tiu kondiĉo. Sed en Excel 2007 kaj Excel 2010 por malfermi ambaŭ fenestrojn samtempe, aldonaj manipuladoj necesas. Kiel fari ĝin diras en aparta leciono.
Leciono: Kiel malfermi Excel en malsamaj fenestroj
Kiel vi povas vidi, estas kelkaj ebloj kompari tablojn unu kun la alia. Kian opcion uzi dependas de kie troviĝas la tablo-datumoj relative unu al la alia (sur unu folio, en malsamaj libroj, sur malsamaj folioj), kaj ankaŭ de la uzanto volas ĉi tiun komparon esti montrita sur la ekrano.