Excel datu salīdzinājums. Kā salīdzināt divas slejas programmā Excel atbilstībām

Šī raksta lasīšana prasīs apmēram 10 minūtes. Nākamo 5 minūšu laikā programmā Excel varat viegli salīdzināt divas kolonnas un uzzināt, vai tajās ir dublikāti, izdzēst vai izcelt tos ar krāsu. Tātad laiks ir pienācis!

Excel ir ļoti spēcīga un ļoti forša lietojumprogramma liela datu apjoma izveidošanai un manipulēšanai. Ja jums ir vairākas darbgrāmatas ar datiem (vai tikai viena milzīga tabula), iespējams, vēlaties salīdzināt 2 kolonnas, atrast vērtību dublikātus un pēc tam veikt dažas darbības, piemēram, izdzēst, iezīmēt vai notīrīt saturu ... Kolonnas var būt vienā tabulā, būt blakus vai nav blakus, tās var atrasties uz 2 dažādām loksnēm vai pat dažādās grāmatās.

Iedomājieties, ka mums ir 2 cilvēku vārdu kolonnas - 5 vārdi kolonnā A un 3 nosaukumi kolonnā B... Ir nepieciešams salīdzināt nosaukumus šajās divās kolonnās un atrast dublikātus. Kā jūs saprotat, tie ir fiktīvi dati, kas ņemti tikai kā piemērs. Īstās tabulās mums ir darīšana ar tūkstošiem, ja ne desmitiem tūkstošu ierakstu.

A variants: abas kolonnas atrodas vienā lapā. Piemēram, kolonna A un kolonnu B.

B variants: Kolonnas atrodas uz dažādām loksnēm. Piemēram, kolonna A uz lapas 2. lapa un kolonnu A uz lapas 3. lapa.

Excel 2013, 2010 un 2007 ir iebūvēts rīks Noņemt dublikātu(Noņemt dublikātus), taču šādā situācijā tas ir bezspēcīgs, jo nevar salīdzināt datus 2 kolonnās. Turklāt tas var noņemt tikai dublikātus. Nav citu iespēju, piemēram, izcelšana vai krāsu maiņa. Un būtība!

Salīdziniet 2 kolonnas programmā Excel un atrodiet ierakstu dublikātus, izmantojot formulas

A variants: abas kolonnas atrodas vienā un tajā pašā lapā

Pamudināt: Lielās izklājlapās formulas kopēšana būs ātrāka, ja izmantojat īsinājumtaustiņus. Atlasiet šūnu C1 un nospiediet Ctrl + C(lai kopētu formulu uz starpliktuvi), pēc tam nospiediet Ctrl + Shift + End(lai atlasītu visas kolonnas C tukšās šūnas) un visbeidzot nospiediet Ctrl + V(lai ievietotu formulu visās atlasītajās šūnās).

B variants: abas kolonnas ir uz dažādām lapām (dažādās grāmatās)

Apstrādājot atrastos dublikātus

Lieliski, pirmajā kolonnā atradām ierakstus, kas ir arī otrajā kolonnā. Tagad mums kaut kas jādara ar viņiem. Manuāla visu ierakstu dublikātu skatīšana tabulā ir diezgan neefektīva un laikietilpīga. Ir labāki veidi.

Rādīt tikai kolonnu A dublikātus

Ja jūsu slejās nav galvenes, jums tās jāpievieno. Lai to izdarītu, novietojiet kursoru uz skaitļa, kas apzīmē pirmo rindu, un tas pārvērtīsies par melnu bultiņu, kā parādīts zemāk redzamajā attēlā:

Ar peles labo pogu noklikšķiniet un atlasiet Ievietojiet(Ievietot):

Sniedziet kolonnu nosaukumus, piemēram, Nosaukums"Un" Dublikāts?”Pēc tam atveriet cilni Dati(Dati) un nospiediet Filtrēt(Filtrs):

Pēc tam noklikšķiniet uz mazās pelēkās bultiņas blakus “ Dublikāts?“Lai paplašinātu filtra izvēlni; noņemiet atzīmi no visiem šī saraksta vienumiem, izņemot Dublikāts un nospiediet labi.

Tas ir viss, tagad jūs redzat tikai tos kolonnas elementus BET kas tiek dublēti kolonnā IN... Mūsu pētījumu tabulā ir tikai divas šādas šūnas, bet, kā jūs saprotat, praksē to ir daudz vairāk.

Lai atkal parādītu visas kolonnas rindas BET, kolonnā noklikšķiniet uz filtra simbola IN kas tagad izskatās kā piltuve ar nelielu bultiņu un atlasiet Izvēlēties visus(Izvēlēties visus). Varat arī to izdarīt caur plūsmu, noklikšķinot uz Dati(Dati)> Atlasīt un filtrēt(Kārtot un filtrēt)> Skaidrs(Notīrīt), kā parādīts zemāk esošajā ekrānuzņēmumā:

Mainiet krāsu vai iezīmējiet atrastos dublikātus

Ja atzīmes “ Dublikāts”Nepietiek jūsu mērķiem, un jūs vēlaties atzīmēt atkārtojošās šūnas ar citu fonta krāsu, aizpildījuma krāsu vai kādā citā veidā ...

Šajā gadījumā filtrējiet dublikātus, kā parādīts iepriekš, atlasiet visas filtrētās šūnas un noklikšķiniet Ctrl + 1 lai atvērtu dialogu Formatēt šūnas(Šūnas formāts). Piemēram, mainīsim šūnu dublikātu līniju aizpildīšanas krāsu uz spilgti dzeltenu. Protams, ar rīku varat mainīt aizpildījuma krāsu Aizpildiet(Aizpildīt krāsu) cilnē Mājas(Sākums), bet dialoglodziņa priekšrocība Formatēt šūnas(Šūnas formāts) ir tas, ka jūs varat vienlaikus konfigurēt visas formatēšanas opcijas.

Tagad jūs noteikti nepalaidīsit garām nevienu šūnu ar dublikātiem:

Noņemot dublikātu vērtības no pirmās kolonnas

Filtrējiet tabulu tā, lai tiktu parādītas tikai šūnas ar dublētām vērtībām, un atlasiet šīs šūnas.

Ja 2 salīdzināmās kolonnas atrodas uz dažādām lapām, tas ir, dažādās tabulās ar peles labo pogu noklikšķiniet uz atlasītā diapazona un atlasiet Dzēst rindu(Dzēst rindu):

Klikšķiniet uz labi kad Excel lūdz apstiprināt, ka tiešām vēlaties izdzēst visu darblapas rindu, un pēc tam notīriet filtru. Kā redzat, ir palikušas tikai rindas ar unikālām vērtībām:

Ja vienā kolonnā ir 2 kolonnas, tuvu viens otram (blakus) vai nav tuvu viens otram (nav blakus), tad dublikātu noņemšanas process būs nedaudz grūtāks. Mēs nevaram izdzēst visu rindu ar vērtību dublikātiem, jo tādējādi tiks dzēstas arī šūnas no otrās kolonnas. Tātad, lai kolonnā paliktu tikai unikāli ieraksti BET, dari šo:

Kā redzat, dublikātu noņemšana no divām Excel kolonnām, izmantojot formulas, nav tik grūta.

Diezgan bieži Excel lietotāji saskaras ar uzdevumu salīdzināt divas tabulas vai sarakstus, lai identificētu atšķirības vai trūkstošos vienumus. Katrs lietotājs tiek galā ar šo uzdevumu savā veidā, taču visbiežāk šī jautājuma risināšanai tiek tērēts diezgan daudz laika, jo ne visas pieejas šai problēmai ir racionālas. Tajā pašā laikā ir vairāki pārbaudīti algoritmi darbībām, kas ļaus salīdzināt sarakstus vai tabulu masīvus diezgan īsā laikā ar minimālu piepūli. Apskatīsim šīs iespējas tuvāk.

Programmā Excel ir diezgan daudz veidu, kā salīdzināt tabulas laukumus, taču tos visus var iedalīt trīs lielās grupās:

vienas lapas sarakstu salīdzinājums;

dažādu tabulu izvietoto tabulu salīdzinājums;

dažādu failu tabulu diapazonu salīdzinājums.

Tieši uz šīs klasifikācijas pamata vispirms tiek izvēlētas salīdzināšanas metodes un noteiktas konkrētas darbības un algoritmi uzdevuma veikšanai. Piemēram, veicot salīdzinājumu dažādās darbgrāmatās, vienlaikus jāatver divi Excel faili.

Turklāt jāsaka, ka ir lietderīgi salīdzināt tabulu vietas tikai tad, ja tām ir līdzīga struktūra.

1. metode: vienkārša formula

Vienkāršākais veids, kā salīdzināt datus divās tabulās, ir vienkārša vienlīdzības formula. Ja dati sakrīt, tad tie atgriež rādītāju TRUE, un, ja nē, tad - FALSE. Jūs varat salīdzināt gan skaitliskos datus, gan tekstu. Šīs metodes trūkums ir tāds, ka to var izmantot tikai tad, ja tabulas dati ir sakārtoti vai sakārtoti vienādi, sinhronizēti un tiem ir vienāds rindu skaits. Apskatīsim, kā šo metodi izmantot praksē, izmantojot divu tabulu, kas izvietotas vienā lapā, piemēru.

Tātad mums ir divas vienkāršas tabulas ar uzņēmuma darbinieku sarakstiem un viņu algām. Ir nepieciešams salīdzināt darbinieku sarakstus un identificēt neatbilstības starp kolonnām, kurās vārdi ir ievietoti.

Tam mums ir nepieciešama papildu kolonna uz lapas. Mēs tur ievadām zīmi «=» ... Tad mēs noklikšķinām uz pirmā salīdzināmā vienuma pirmajā sarakstā. Ielieciet simbolu vēlreiz «=» no tastatūras. Pēc tam otrajā tabulā noklikšķiniet uz tās kolonnas pirmās šūnas, kuru mēs salīdzinām. Rezultāts ir šāda veida izteiksme:

Lai gan, protams, katrā gadījumā koordinātas būs atšķirīgas, bet būtība paliks nemainīga.
Noklikšķiniet uz taustiņa Enter lai iegūtu salīdzināšanas rezultātus. Kā redzat, salīdzinot abu sarakstu pirmās šūnas, programma norādīja indikatoru "TRUE", kas nozīmē datu atbilstību.

Tagad mums ir jāveic līdzīga darbība ar pārējām abu tabulu šūnām kolonnās, kuras mēs salīdzinām. Bet jūs varat vienkārši nokopēt formulu, kas ievērojami ietaupīs laiku. Šis faktors ir īpaši svarīgs, salīdzinot sarakstus ar lielu virkņu skaitu.
Kopēšanas procedūru visvieglāk paveikt, izmantojot aizpildīšanas rokturi. Virziet kursoru virs šūnas apakšējā labā stūra, kur mēs saņēmām indikatoru "TRUE"... Šajā gadījumā tas jāpārveido par melnu krustu. Tas ir aizpildījuma marķieris. Nospiediet peles kreiso pogu un velciet kursoru uz leju līdz rindu skaitam salīdzinātajos tabulu masīvos.

Kā redzat, tagad papildu slejā visi datu salīdzināšanas rezultāti tiek parādīti divās tabulu masīvu kolonnās. Mūsu gadījumā dati nesakrita tikai vienā rindā. Salīdzinot tos, formula deva rezultātu "FALSE"... Visām pārējām līnijām, kā redzat, salīdzināšanas formula deva rādītāju "TRUE".

Turklāt ir iespējams aprēķināt neatbilstību skaitu, izmantojot īpašu formulu. Lai to izdarītu, atlasiet lapas elementu, kur tas tiks parādīts. Pēc tam noklikšķiniet uz ikonas "Ievietot funkciju".

Logā Funkciju vedņi operatoru grupā "Matemātisks" iezīmējiet vārdu SUMPRODUCT... Noklikšķiniet uz pogas "LABI".

Funkcijas argumentu logs ir aktivizēts SUMPRODUCT, kura galvenais uzdevums ir aprēķināt izvēlētā diapazona produktu summu. Bet šo funkciju var izmantot arī mūsu mērķiem. Tās sintakse ir diezgan vienkārša:
SUMPRODUCT (masīvs1; masīvs2; ...)

Kopumā kā argumentus var izmantot adreses līdz 255 masīviem. Bet mūsu gadījumā mēs izmantosim tikai divus masīvus, turklāt kā vienu argumentu.

Mēs ievietojām kursoru laukā "Masīvs1" un lapā atlasiet salīdzināto datu diapazonu pirmajā apgabalā. Pēc tam laukā mēs ieliekam zīmi "nav vienāds" (<> ) un atlasiet salīdzināto otrā reģiona diapazonu. Tālāk mēs iesaiņojam iegūto izteiksmi iekavās, kuru priekšā mēs ievietojam divas zīmes «-» ... Mūsu gadījumā mēs saņēmām šādu izteicienu:

- (A2: A7<>D2: D7)

Noklikšķiniet uz pogas "LABI".

Operators aprēķina un parāda rezultātu. Kā redzat, mūsu gadījumā rezultāts ir vienāds ar skaitli "viens" tas nozīmē, ka salīdzinātajos sarakstos tika atrasta viena neatbilstība. Ja saraksti būtu pilnīgi identiski, rezultāts būtu vienāds ar skaitli «0» .

Tādā pašā veidā jūs varat salīdzināt datus tabulās, kas atrodas dažādās lapās. Bet šajā gadījumā ir vēlams, lai līnijas tajās būtu numurētas. Pretējā gadījumā salīdzināšanas procedūra ir gandrīz tieši tāda pati kā iepriekš aprakstītā, izņemot to, ka, ievadot formulu, jums būs jāpārslēdzas starp lapām. Mūsu gadījumā izteiksme izskatīsies šādi:

B2 = 2. lapa! B2

Tas ir, kā redzat, pirms datu koordinātām, kas atrodas uz citām lapām, kas atšķiras no tā, kur tiek parādīts salīdzināšanas rezultāts, tiek norādīts lapas numurs un izsaukuma zīme.

2. metode: atlasiet šūnu grupas

Salīdzinājumu var veikt, izmantojot šūnu grupas atlases rīku. Tas arī ļauj salīdzināt tikai sinhronizētos un sakārtotos sarakstus. Turklāt šajā gadījumā sarakstiem vajadzētu atrasties blakus vienai un tai pašai lapai.

3. metode: nosacīta formatēšana

Salīdzinājumus var veikt, izmantojot nosacītā formatēšanas metodi. Tāpat kā iepriekšējā metodē, salīdzinātajiem apgabaliem jābūt tajā pašā Excel darblapā un jābūt sinhronizētiem savā starpā.

Ir vēl viens veids, kā izmantot nosacīto formatējumu, lai izpildītu konkrēto uzdevumu. Tāpat kā iepriekšējās opcijas, tas prasa abu salīdzināto apgabalu atrašanās vietu vienā lapā, taču atšķirībā no iepriekš aprakstītajām metodēm datu sinhronizācijas vai šķirošanas nosacījums nebūs vajadzīgs, kas šo opciju labvēlīgi atšķir no iepriekš aprakstītajām.

Ja vēlaties, jūs, gluži pretēji, varat krāsot neatbilstošos elementus un atstāt tos rādītājus, kas atbilst aizpildījumam, vienā krāsā. Šajā gadījumā darbību algoritms ir praktiski vienāds, bet iestatījumu logā dublētu vērtību izcelšanai pirmajā laukā parametra vietā "Atkārtojas" jāizvēlas parametrs "Unikāls"... Pēc tam noklikšķiniet uz pogas "LABI".

Tādējādi tiks izcelti tieši tie rādītāji, kas nesakrīt.

4. metode: sarežģīta formula

Varat arī salīdzināt datus, izmantojot sarežģītu formulu, kuras pamatā ir funkcija COUNTIF... Izmantojot šo rīku, varat saskaitīt, cik katrs elements no otrās tabulas atlasītās kolonnas tiek atkārtots pirmajā.

Operators COUNTIF pieder funkciju statistiskajai grupai. Tās uzdevums ir saskaitīt šūnu skaitu, kurās vērtības atbilst noteiktam nosacījumam. Šī operatora sintakse ir šāda:

COUNTIF (diapazons, kritērijs)

Arguments "Diapazons" ir masīva adrese, kurā tiek skaitītas atbilstošās vērtības.

Arguments "Kritērijs" norāda spēles nosacījumu. Mūsu gadījumā tas pārstāv konkrētu šūnu koordinātas pirmās tabulas apgabalā.

Protams, lai salīdzinātu tabulas rādītājus, šo izteicienu var pielietot jau esošajā formā, taču pastāv iespēja to uzlabot.

Izveidosim tā, lai vērtības, kas atrodas otrajā tabulā, bet ne pirmajā, tiktu parādītas atsevišķā sarakstā.

Vispirms nedaudz pārskatīsim savu formulu. COUNTIF, proti, mēs to padarīsim par vienu no operatora argumentiem JA... Lai to izdarītu, atlasiet pirmo šūnu, kurā atrodas operators COUNTIF... Formulu joslā pirms tā pievienojiet izteicienu "JA" bez pēdiņām un atveriet iekavas. Turklāt, lai mums būtu vieglāk strādāt, formulas joslā atlasiet vērtību "JA" un noklikšķiniet uz ikonas "Ievietot funkciju".

Atver funkciju argumentu logu JA... Kā redzat, loga pirmais lauks jau ir aizpildīts ar operatora vērtību COUNTIF... Bet mums šajā laukā jāpievieno kas cits. Mēs iestatām kursoru tur un pievienojam jau esošajai izteiksmei «=0» bez pēdiņām.
Pēc tam dodieties uz lauku "Vērtība, ja patiess"... Šeit mēs izmantosim vēl vienu ligzdotu funkciju - LĪNIJA... Mēs ievadām vārdu "LINE" bez pēdiņām, pēc tam atveriet iekavas un otrajā tabulā norādiet pirmās šūnas koordinātas ar uzvārdu un pēc tam aizveriet iekavas. Precīzāk, mūsu gadījumā laukā "Vērtība, ja patiess" ieguva šādu izteicienu:

RINDA (D2)

Tagad operators LĪNIJA sazināsies ar funkcijām JA rindas numurs, kurā atrodas konkrētais uzvārds, un gadījumā, ja tiks izpildīts pirmajā laukā norādītais nosacījums, funkcija JA izvadīs šo skaitli šūnā. Noklikšķiniet uz pogas "LABI".

Kā redzat, pirmais rezultāts tiek parādīts kā "FALSE"... Tas nozīmē, ka vērtība neatbilst operatora nosacījumiem JA... Tas ir, pirmais uzvārds ir abos sarakstos.

Izmantojot aizpildes marķieri, kopējiet operatora izteiksmi parastajā veidā JA visu kolonnu. Kā redzat, divām pozīcijām, kas atrodas otrajā tabulā, bet ne pirmajā, formula dod līniju numurus.

Mēs atkāpjamies no tabulas laukuma pa labi un aizpildām kolonnu ar numuriem secībā, sākot no 1 ... Ciparu skaitam jāatbilst rindu skaitam otrajā salīdzināšanas tabulā. Lai paātrinātu numerācijas procesu, varat arī izmantot aizpildes marķieri.

Pēc tam atlasiet pirmo šūnu pa labi no kolonnas ar skaitļiem un noklikšķiniet uz ikonas "Ievietot funkciju".

Atveras Funkciju vednis... Pārejiet uz kategoriju "Statistika" un izvēlieties vārdu "VISMAZ"... Noklikšķiniet uz pogas "LABI".

Funkcija VISMAZ, kuru argumentu logs tika atvērts, ir paredzēts norādīt kontā norādīto mazāko vērtību.
Laukā "Masīvs" jums jānorāda papildu kolonnas diapazona koordinātas "Sakritību skaits" ko mēs iepriekš pārveidojām, izmantojot funkciju JA... Mēs padarām visas saites absolūtas.

Laukā "K" norāda mazāko parādāmo vērtību. Šeit mēs norādām nesen pievienotās kolonnas pirmās šūnas koordinātas ar numerāciju. Atstājiet adreses radinieku. Noklikšķiniet uz pogas "LABI".

Operators izsniedz rezultātu - skaitli 3 ... Tieši tā ir mazākā neatbilstošo tabulu masīvu rindu numerācija. Izmantojot aizpildīšanas rokturi, kopējiet formulu pašā apakšā.

Tagad, zinot nesakritošo elementu rindu numurus, mēs varam ievietot šūnā un to vērtības, izmantojot funkciju INDEKSS... Atlasiet pirmo lapas elementu, kurā ir formula VISMAZ... Pēc tam dodieties uz formulas joslu un pirms nosaukuma "VISMAZ" pievienojiet vārdu "INDEX" bez pēdiņām nekavējoties atveriet iekavas un ielieciet semikolu ( ; ). Pēc tam formulu joslā atlasiet nosaukumu "INDEX" un noklikšķiniet uz ikonas "Ievietot funkciju".

Pēc tam tiek atvērts neliels logs, kurā jānosaka, vai funkcijai jābūt atsauces formai INDEKSS vai paredzēti darbam ar masīviem. Mums ir nepieciešama otra iespēja. Tas ir instalēts pēc noklusējuma, tāpēc šajā logā vienkārši noklikšķiniet uz pogas "LABI".

Tiek atvērts logs Funkciju argumenti INDEKSS... Šis operators ir paredzēts, lai parādītu vērtību, kas atrodas noteiktā masīvā norādītajā rindā.
Kā redzat, lauks "Līnijas numurs" jau ir piepildīts ar funkciju vērtībām VISMAZ... Atšķirība starp Excel lapas numerāciju un tabulas apgabala iekšējo numerāciju būtu jāatņem no tur jau esošās vērtības. Kā redzat, galvene mums ir tikai virs tabulas vērtībām. Tas nozīmē, ka atšķirība ir viena līnija. Tāpēc mēs pievienojam laukā "Līnijas numurs" vērtība "viens" bez pēdiņām.

Laukā "Masīvs" norādiet otrās tabulas vērtību diapazona adresi. Šajā gadījumā mēs visas koordinātas padarām par absolūtām, tas ir, mēs to priekšā ievietojam dolāra zīmi jau aprakstītajā veidā.

Noklikšķiniet uz pogas "LABI".

Pēc rezultāta parādīšanas ekrānā paplašiniet funkciju, izmantojot aizpildes marķieri, līdz kolonnas beigām uz leju. Kā redzat, abi uzvārdi, kas atrodas otrajā tabulā, bet pirmajā nav, tiek parādīti atsevišķā diapazonā.

5. metode: masīvu salīdzināšana dažādās grāmatās

Salīdzinot diapazonus dažādās darbgrāmatās, varat izmantot iepriekš uzskaitītās metodes, izņemot tās opcijas, kas prasa, lai abas tabulu vietas tiktu ievietotas vienā un tajā pašā lapā. Galvenais nosacījums salīdzināšanas procedūras veikšanai šajā gadījumā ir abu failu logu atvēršana vienlaicīgi. Excel 2013 un jaunākas versijas, kā arī versijas pirms Excel 2007, ar šo nosacījumu nav problēmu. Bet programmā Excel 2007 un Excel 2010, lai vienlaikus atvērtu abus logus, nepieciešama papildu manipulācija. Kā to izdarīt, ir aprakstīts atsevišķā nodarbībā.

Kā redzat, ir vairākas iespējas salīdzināt tabulas savā starpā. Kuru opciju izmantot, ir atkarīgs no tā, kur tieši tabulas dati atrodas viens pret otru (vienā un tajā pašā lapā, dažādās grāmatās, dažādās lapās), kā arī no tā, kā lietotājs vēlas, lai ekrānā tiktu parādīts šis salīdzinājums.

Pieņemsim, ka vēlaties salīdzināt darbgrāmatas versijas, analizēt darbgrāmatu par problēmām vai neatbilstībām vai redzēt saites starp darbgrāmatām vai darblapām. Ja datorā ir instalēta programma Microsoft Office 365 vai Office Professional Plus 2013, programmā Excel ir pieejams pievienojumprogramma Izklājlapas pieprasīšana.

Cilnē Enquire esošās komandas varat izmantot, lai veiktu visus šos uzdevumus un daudz ko citu. Excel lentes cilnē Pieprasīt ir pogas tālāk aprakstītajām komandām.

Ja jūs neredzat Jautāt cilni Excel lentē skatiet sadaļu Spreadsheet Enquire pievienojumprogrammas ieslēgšana.

Salīdziniet divas darbgrāmatas

The Salīdziniet failus komanda ļauj redzēt šūnu atšķirības starp divām darbgrāmatām. Lai palaistu šo komandu, programmā Excel ir jābūt atvērtām divām darbgrāmatām.

Rezultāti ir krāsu kodēti pēc satura veida, piemēram, ievadītās vērtības, formulas, nosauktie diapazoni un formāti. Ir pat logs, kurā var parādīt VBA koda izmaiņas pa rindām. Atšķirības starp šūnām tiek parādītas viegli lasāmā tīkla izkārtojumā, piemēram:

The Salīdziniet failus komanda izmanto Microsoft izklājlapu salīdzināt, lai salīdzinātu abus failus. Operētājsistēmā Windows 8 varat sākt izklājlapu salīdzināšanu ārpus programmas Excel, noklikšķinot uz Izklājlapu salīdzinājums uz Lietotnes ekrāns. Operētājsistēmā Windows 7 noklikšķiniet uz Windows Sākt pogu un pēc tam> Visas programmas > Microsoft Office 2013 > Office 2013 rīki > Izklājlapu salīdzinājums 2013.

Lai uzzinātu vairāk par izklājlapu salīdzināšanu un failu salīdzināšanu, lasiet rakstu Darbgrāmatas divu versiju salīdzināšana.

Analizējiet darbgrāmatu

The Darbgrāmatu analīze komanda izveido interaktīvu pārskatu, kurā parādīta detalizēta informācija par darbgrāmatu un tās struktūru, formulām, šūnām, diapazoniem un brīdinājumiem. Šeit redzamajā attēlā redzama ļoti vienkārša darbgrāmata, kurā ir divas formulas un datu savienojumi ar Access datu bāzi un teksta failu.

Rādīt darbgrāmatas saites

Darbgrāmatas, kas savienotas ar citām darbgrāmatām, izmantojot šūnu atsauces, var mulsināt. Izmantojiet, lai izveidotu interaktīvu, grafisku darbgrāmatas atkarību karti, ko izveido savienojumi (saites) starp failiem. Saites veidi diagrammā var ietvert citas darbgrāmatas, Access datu bāzes, teksta failus, HTML lapas, SQL Server datu bāzes un citus datu avotus. Attiecību diagrammā varat atlasīt elementus un atrast vairāk informācijas par tiem, kā arī velciet savienojuma līnijas, lai mainītu diagrammas formu.

Šī diagramma parāda pašreizējo darbgrāmatu kreisajā pusē un savienojumus starp to un citām darbgrāmatām un datu avotiem. Tas parāda arī darbgrāmatas savienojumu papildu līmeņus, sniedzot priekšstatu par darbgrāmatas datu izcelsmi.

Rādīt darblapu saites

Vai jums ir daudz viena no otras atkarīgu darba lapu? Izmantojiet, lai izveidotu interaktīvu, grafisku savienojumu (saišu) karti starp darblapām tajā pašā darbgrāmatā un citās darbgrāmatās. Tas palīdz iegūt skaidrāku priekšstatu par to, kā jūsu dati var būt atkarīgi no šūnām citās vietās.

Šī diagramma parāda attiecības starp darblapām četrās dažādās darbgrāmatās ar atkarībām starp darblapām vienā darbgrāmatā, kā arī saitēm starp darblapām dažādās darbgrāmatās. Novietojot rādītāju virs mezgla diagrammā, piemēram, darblapā ar nosaukumu "West" diagrammā, tiek parādīts balons ar informāciju.

Rādīt šūnu attiecības

Lai iegūtu detalizētu, interaktīvu diagrammu par visām saitēm no atlasītās šūnas uz šūnām citās darblapās vai pat citās darbgrāmatās, izmantojiet Šūnu attiecības rīks. Šīs attiecības ar citām šūnām var pastāvēt formulās vai atsaucēs uz nosauktajiem diapazoniem. Diagramma var šķērsot darblapas un darbgrāmatas.

Šajā diagrammā ir parādīti divi šūnu attiecību līmeņi šūnai A10 uz Book1.xlsx 5. lapas. Šī šūna ir atkarīga no citas darbgrāmatas Book2.xlsx 1. lapas 1. šūnas C6. Šī šūna ir precedents vairākām šūnām citās tā paša faila darblapās.

Lai uzzinātu vairāk par šūnu attiecību skatīšanu, izlasiet sadaļu Saites starp šūnām.

Notīriet lieko šūnu formatējumu

Vai esat kādreiz atvēris darbgrāmatu un atradis, ka tā tiek lēnām ielādēta vai arī tā ir kļuvusi milzīga? Iespējams, ka formatējums ir piemērots tām rindām vai kolonnām, kuras jūs nezināt. Izmantojiet Notīriet lieko šūnu formatēšanu komandu, lai noņemtu lieko formatējumu un ievērojami samazinātu faila lielumu. Tas palīdz izvairīties no izklājlapas uzpūšanās, kas uzlabo Excel ātrumu.

Pārvaldiet paroles

Ja izmantojat funkcijas Pieprasīt, lai analizētu vai salīdzinātu ar paroli aizsargātas darbgrāmatas, jums būs jāpievieno darbgrāmatas parole paroļu sarakstam, lai Enquire varētu atvērt saglabāto darbgrāmatas kopiju. Izmantojiet Darbgrāmatu paroles komandu Jautāt cilni, lai pievienotu paroles, kas tiks saglabātas jūsu datorā. Šīs paroles ir šifrētas, un tām varat piekļūt tikai jūs.

Rakstā sniegtas atbildes uz šādiem jautājumiem:

Kā salīdzināt divas tabulas programmā Excel?
Kā salīdzināt sarežģītas izklājlapas programmā Excel?
Kā salīdzināt tabulas programmā Excel, izmantojot funkciju VLOOKUP ()?
Kā ģenerēt unikālus rindu identifikatorus, ja to unikalitāti sākotnēji nosaka vērtību kopa vairākās kolonnās?
Kā iesaldēt šūnu vērtības formulās, kopējot formulas?

Strādājot ar lielu informācijas daudzumu, lietotājam var nākties saskarties ar tādu uzdevumu kā divu tabulu datu avotu salīdzināšana. Uzglabājot datus vienā grāmatvedības sistēmā (piemēram, sistēmās, kuru pamatā ir 1C Enterprise, sistēmās, kas izmanto SQL datu bāzes), datu salīdzināšanai var izmantot sistēmā vai DBVS iebūvētās iespējas. Parasti tam ir pietiekami iesaistīt programmētāju, kurš datu bāzē ierakstīs vaicājumu vai programmatūras pārskata mehānismu. Pieredzējis lietotājs, kuram ir prasme rakstīt 1C vai SQL vaicājumus, var apstrādāt arī vaicājumu.

Problēmas sākas, kad jums steidzami jāveic datu salīdzināšanas uzdevums, un programmētāja iesaistīšanās un viņa pieprasījuma vai programmas pārskata savlaicīga rakstīšana var pārsniegt uzdevuma risināšanai noteiktos termiņus. Vēl viena tikpat izplatīta problēma ir nepieciešamība salīdzināt informāciju no dažādiem avotiem. Šajā gadījumā problēmas paziņojums programmētājam izklausīsies kā divu sistēmu integrācija. Šāda uzdevuma risināšanai būs nepieciešama augstāka programmētāja kvalifikācija, un tas prasīs arī vairāk laika nekā izstrāde vienā sistēmā.

Norādīto problēmu risināšanai ideāls paņēmiens ir datu salīdzināšanai izmantot Microsoft Excel izklājlapu redaktoru. Lielākā daļa izplatīto vadības un normatīvo grāmatvedības sistēmu atbalsta eksportēšanu uz Excel formātu. Šim uzdevumam būs nepieciešama tikai noteikta lietotāja kvalifikācija darbam ar šo biroja komplektu, un tam nebūs nepieciešamas programmēšanas prasmes.

Apskatīsim Excel tabulu salīdzināšanas problēmas risinājumu, izmantojot piemēru. Mums ir divas tabulas ar dzīvokļu sarakstiem. Izkraušanas avoti - 1C Enterprise (būvniecības grāmatvedība) un tabula programmā Excel (pārdošanas uzskaite). Tabulas atrodas Excel darbgrāmatā attiecīgi uz pirmās un otrās lapas.

Mūsu uzdevums ir salīdzināt šos sarakstus pēc adreses. Pirmajā tabulā ir visi mājas dzīvokļi. Otrajā tabulā ir tikai pārdoti dzīvokļi un pircēja vārds. Galīgais mērķis ir katra dzīvokļa pirmajā tabulā parādīt pircēja vārdu (tiem dzīvokļiem, kas ir pārdoti). Uzdevumu sarežģī fakts, ka katrā tabulā dzīvokļa adrese ir konstruktīva un sastāv no vairākiem laukiem: 1) ēkas (mājas) adrese, 2) sekcija (ieeja), 3) stāvs, 4) numurs uz grīdas (piemēram, no 1 līdz 4) ...

Lai salīdzinātu divas Excel tabulas, mums jānodrošina, lai katra abu tabulu rinda būtu identificēta ar vienu lauku, nevis ar četriem. Šādu lauku varat iegūt, savienojot četru adrešu lauku vērtības ar funkciju Sistēma (). Funkcijas Concatenate () mērķis ir apvienot vairākas teksta vērtības vienā rindā. Funkcijas vērtības tiek uzskaitītas, izmantojot simbolu ";". Vērtības var būt vai nu šūnu adreses, vai patvaļīgs teksts, kas norādīts pēdiņās.

1. solis. Ievietosim tukšu kolonnu "A" pirmās tabulas sākumā un uzrakstīsim formulu šīs kolonnas šūnā pretī pirmajai rindai ar datiem:
= CONCATENATE (B3; "-"; C3; "-"; D3; "-"; E3)
Vizuālās uztveres ērtībai starp apvienojamo šūnu vērtībām esam iestatījuši simbolus "-".

2. solis. Kopējiet formulu šādās A slejas šūnās.

4. solis. Lai salīdzinātu Excel tabulas pēc vērtībām, izmantojiet funkciju VLOOKUP (). Funkcijas VLOOKUP () mērķis ir atrast vērtību tabulas kreisajā kreisajā kolonnā un atgriezt šūnas vērtību, kas atrodas tās pašas rindas norādītajā kolonnā. Pirmais parametrs ir vēlamā vērtība. Otrais parametrs ir tabula, kurā tiks meklēta vērtība. Trešais parametrs ir kolonnas numurs, no kura vērtība tiks atgriezta atrastajā rindā. Ceturtais parametrs ir meklēšanas veids: false ir precīza atbilstība, true ir aptuvena atbilstība. Tā kā izejas informācija jānovieto pirmajā tabulā (tieši šajā tabulā bija jāpievieno pircēju vārdi), tad tajā tiks ierakstīta formula. Veidosim formulu brīvajā kolonnā pa labi no tabulas iepretim pirmajai datu rindai:
= VLOOKUP (A3; 2. lapa! $ A $ 3: $ F $ 10; 6; FALSE)
Kopējot formulas, Smart Excel automātiski maina šūnu adresāciju. Mūsu gadījumā katrai rindai mainīsies vēlamā vērtība: A3, A4 utt., Un tabulas, kurā tiek veikta meklēšana, adresei jāpaliek nemainīgai. Lai to izdarītu, tabulas adreses parametrā salabojiet šūnas ar simboliem "$". Vietnes "Sheet2! A3: F10" vietā mēs izveidojam "Sheet2! $ A $ 3: $ F $ 10".

Pēc pievienojumprogrammas instalēšanas jums būs jauna cilne ar komandu, lai izsauktu funkciju. Noklikšķinot uz komandas Diapazonu salīdzināšana parādās dialoglodziņš parametru ievadīšanai.

Šis makro ļauj salīdzināt jebkura lieluma tabulas ar jebkuru kolonnu skaitu. Tabulu salīdzināšanu var veikt vienā, divās vai trīs kolonnās vienlaikus.

Dialoglodziņš ir sadalīts divās daļās: pa kreisi pirmajai tabulai un pa labi otrajai.

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

Norādiet tabulas diapazonus.
Atzīmējiet izvēles rūtiņu (izvēles rūtiņa / izvēles rūtiņa) zem izvēlētā tabulu diapazona, ja tabulā ir galvene (galvenes rinda).
Atlasiet kreisās un labās tabulas kolonnas, pēc kurām tiks veikts salīdzinājums (ja tabulu diapazonos nav galvenes, kolonnas tiks numurētas).
Norādiet salīdzināšanas veidu.
Atlasiet opciju rezultātu parādīšanai.

Tabulu salīdzināšanas veids

Programma ļauj jums izvēlēties vairākus tabulu salīdzināšanas veidus:

Atrodiet vienas tabulas rindas, kuras nav citā tabulā

Kad ir atlasīts šāda veida salīdzinājums, programma meklē rindas no vienas tabulas, kuras citā nav. Ja jūs salīdzināt tabulas pēc vairākām kolonnām, tad darba rezultāts būs rindas, kurās ir atšķirība vismaz vienā no kolonnām.

Atrodiet atbilstošas līnijas

Izvēloties šāda veida salīdzinājumus, programma atrod rindas, kas atbilst pirmajā un otrajā tabulā. Rindas tiek uzskatītas par sakritīgām, ja vienas tabulas atlasītajās salīdzināšanas kolonnās (1, 2, 3) esošās vērtības pilnībā sakrīt ar otrās tabulas kolonnu vērtībām.

Programmas darba piemērs šajā režīmā attēlā ir redzams labajā pusē.

Atbilst tabulām, pamatojoties uz atlasītajām

Šajā salīdzināšanas režīmā, iepretim katrai pirmās tabulas rindai (izvēlēta kā galvenā), tiek kopēti otrās tabulas atbilstošās rindas dati. Ja nav atbilstošu rindu, rinda galvenās tabulas priekšā paliek tukša.