Kā salīdzināt divas Excel lapas (atšķirībām)

Skatieties video - kā salīdzināt divas Excel lapas atšķirībām

Divu Excel failu salīdzināšana (vai divu lapu salīdzināšana vienā failā) var būt sarežģīta, jo Excel darbgrāmatā vienlaikus tiek rādīta tikai viena lapa.

Tas kļūst grūtāk un rada kļūdas, ja jums ir daudz datu, kas jāsalīdzina.

Par laimi, programmā Excel ir dažas lieliskas funkcijas, kas ļauj atvērt un viegli salīdzināt divus Excel failus.

Šajā Excel apmācībā es parādīšu vairākus veidus, kā to izdarīt salīdziniet divus dažādus Excel failus (vai lapas) un pārbaudiet atšķirības. Jūsu izvēlētā metode būs atkarīga no tā, kā jūsu dati ir strukturēti un kādu salīdzinājumu meklējat.

Sāksim!

Divu Excel lapu salīdzināšana atsevišķos Excel failos (blakus)

Ja vēlaties salīdzināt divus atsevišķus Excel failus (vai divas lapas vienā darbgrāmatā), programmā Excel ir iebūvēta funkcija, lai to izdarītu.

Tas ir Skatīt blakus iespēja.

Tas ir ieteicams tikai tad, ja jums ir neliela datu kopa un manuāla šo failu salīdzināšana, iespējams, būs mazāk laikietilpīga un pakļauta kļūdām. Ja jums ir liela datu kopa, es iesaku izmantot nosacījuma metodi vai formulas metodi, kas tiks aplūkota vēlāk šajā apmācībā.

Apskatīsim, kā to izmantot, ja jums ir jāsalīdzina divi atsevišķi faili vai divas lapas vienā failā.

Pieņemsim, ka jums ir divi faili divus dažādus mēnešus un vēlaties pārbaudīt, kādas vērtības šajos divos failos atšķiras.

Pēc noklusējuma, atverot failu, tas, iespējams, aizņem visu ekrānu. Pat ja samazināsiet izmēru, augšpusē vienmēr redzēsit vienu Excel failu.

Izmantojot opciju skats blakus, varat atvērt divus failus un pēc tam tos sakārtot horizontāli vai vertikāli. Tas ļauj viegli salīdzināt vērtības, nepārslēdzoties uz priekšu un atpakaļ.

Tālāk ir norādītas darbības, lai divus failus līdzinātu un salīdzinātu:

1. Atveriet failus, kurus vēlaties salīdzināt.
2. Katrā failā atlasiet lapu, kuru vēlaties salīdzināt.
3. Noklikšķiniet uz cilnes Skats
4. Windows grupā noklikšķiniet uz opcijas “Skatīt blakus”. Tas kļūst pieejams tikai tad, ja ir atvērti divi vai vairāki Excel faili.

Tiklīdz noklikšķināsit uz opcijas Skats blakus, programma Excel sakārtos darbgrāmatu horizontāli. Abi faili būs redzami, un jūs varat brīvi rediģēt/salīdzināt šos failus, kamēr tie ir sakārtoti blakus.

Ja vēlaties failus sakārtot vertikāli, noklikšķiniet uz opcijas Kārtot visu (cilnē Skats).

Tiks atvērts dialoglodziņš “Sakārtot Windows”, kurā varat izvēlēties “Vertikāls”.

Šajā brīdī, ritinot uz leju vienā no darblapām, otra paliktu tāda, kāda tā ir. Varat to mainīt, lai, ritinot vienā lapā, vienlaikus ritinātu arī otra lapa. Tādējādi ir vieglāk salīdzināt rindas pa rindām un pamanīt atšķirības.

Bet, lai to izdarītu, jums ir jāiespējo Sinhronā ritināšana.

Lai iespējotu sinhrono ritināšanu, noklikšķiniet uz cilnes Skats (jebkurā darbgrāmatā) un pēc tam noklikšķiniet uz opcijas Sinhronā ritināšana. Šī ir pārslēgšanas poga (tādēļ, ja vēlaties to izslēgt, vienkārši noklikšķiniet uz tās vēlreiz).

Vairāku lapu salīdzināšana atsevišķos Excel failos (blakus)

Izmantojot opciju “Skatīt blakus”, vienā reizē varat salīdzināt tikai divus Excel failus.

Ja jums ir atvērti vairāki Excel faili, noklikšķinot uz opcijas Skatīt blakus, tiek parādīts dialoglodziņš Salīdzināt blakus, kur varat izvēlēties, kuru failu vēlaties salīdzināt ar aktīvo darbgrāmatu.

Ja vēlaties vienā reizē salīdzināt vairāk nekā divus failus, atveriet visus šos failus un pēc tam noklikšķiniet uz opcijas Kārtot visu (tas atrodas cilnē Skats).

Dialoglodziņā Windows sakārtošana atlasiet Vertikāli/Horizontāli un pēc tam noklikšķiniet uz Labi.

Tas sakārtos visus atvērtos Excel failus izvēlētajā secībā (vertikāli vai horizontāli).

Salīdziniet divas lapas (blakus) vienā un tajā pašā Excel darbgrāmatā

Ja vēlaties salīdzināt divas atsevišķas lapas vienā darbgrāmatā, nevarat izmantot funkciju Skatīt blakus (jo tā darbojas tikai atsevišķiem Excel failiem).

Bet jūs joprojām varat veikt to pašu salīdzinājumu.

To padara iespējamu “Jauna Windows funkcija programmā Excel, kas ļauj vienā darbgrāmatā atvērt divus gadījumus. Kad esat atvēris divus gadījumus, varat tos sakārtot blakus un pēc tam salīdzināt.

Pieņemsim, ka jums ir Excel darbgrāmata, kurā ir divas lapas diviem dažādiem mēnešiem (janvāris un februāris), un vēlaties tās salīdzināt, lai redzētu, kā ir mainījušies pārdošanas apjomi vienā veikalā.

Tālāk ir norādītas darbības, lai salīdzinātu divas Excel lapas:

1. Atveriet darbgrāmatu, kurā ir lapas, kuras vēlaties salīdzināt.
2. Noklikšķiniet uz cilnes Skats
3. Logu grupā noklikšķiniet uz opcijas “Jauns logs”. Tādējādi tiek atvērta tās pašas darbgrāmatas otrā instance.
4. Cilnē “Skats” noklikšķiniet uz “Sakārtot visu”. Tiks atvērts dialoglodziņš Windows sakārtošana
5. Atlasiet “Vertikāli”, lai salīdzinātu datus kolonnās (vai izvēlieties Horizontāli, ja vēlaties salīdzināt datus rindās).
6. Noklikšķiniet uz Labi.

Iepriekš minētās darbības abus darbgrāmatas gadījumus sakārtotu vertikāli.

Šobrīd abās darbgrāmatās būtu atlasīta viena un tā pati darblapa. Vienā no darbgrāmatām atlasiet otru lapu, kuru vēlaties salīdzināt ar aktīvo lapu.

Kā tas darbojas?

Noklikšķinot uz Jauns logs, tā atkal atver to pašu darbgrāmatu ar nedaudz citu nosaukumu. Piemēram, ja jūsu darbgrāmatas nosaukums ir “Tests” un jūs noklikšķināt uz Jauns logs, tā jau atvērtajai darbgrāmatai piešķirs nosaukumu “Tests - 1”, bet otrajai instancei - “Tests - 2”.

Ņemiet vērā, ka šīs joprojām ir viena un tā pati darbgrāmata. Ja kādā no šīm darbgrāmatām veicat izmaiņas, tas tiks atspoguļots abās.

Un, aizverot kādu atvērtā faila gadījumu, nosaukums atgriezīsies sākotnējā.

Ja vēlaties, varat arī iespējot sinhrono ritināšanu (cilnē Skats noklikšķinot uz opcijas Sinhronā ritināšana).

Salīdziniet divas lapas un iezīmējiet atšķirības (izmantojot nosacītu formatējumu)

Lai gan varat izmantot iepriekš minēto metodi, lai saskaņotu darbgrāmatas kopā un manuāli pārvietotos pa datiem pa rindām, tas nav labs veids, ja jums ir daudz datu.

Turklāt, veicot šo salīdzināšanas līmeni manuāli, var rasties daudz kļūdu.

Tātad, tā vietā, lai to izdarītu manuāli, varat izmantot nosacījuma formatēšanas iespējas, lai ātri iezīmētu atšķirības abās Excel lapās.

Šī metode ir patiešām noderīga, ja jums ir divas versijas divās dažādās lapās un vēlaties ātri pārbaudīt, kas ir mainījies.

Ņemiet vērā, ka jūs NEVAR salīdziniet divas lapas dažādās darbgrāmatās.

Tā kā nosacītā formatēšana nevar atsaukties uz ārēju Excel failu, salīdzināmām lapām jābūt vienā Excel darbgrāmatā. Ja tā nav, varat kopēt lapu no otra faila uz aktīvo darbgrāmatu un pēc tam veikt šo salīdzinājumu.

Šajā piemērā pieņemsim, ka jums ir divu mēnešu (janvāris un februāris) datu kopa divās dažādās lapās un vēlaties ātri salīdzināt šo divu lapu datus un pārbaudīt, vai šo vienumu cenas ir mainījušās.

Tālāk ir norādītas darbības, kā to izdarīt:

1. Lapā atlasiet datus, kuros vēlaties izcelt izmaiņas. Tā kā vēlos pārbaudīt, kā cenas ir mainījušās no janvāra līdz februārim, esmu izvēlējies datus februāra lapā.
2. Noklikšķiniet uz cilnes Sākums
3. Grupā Stili noklikšķiniet uz “Nosacījuma formatēšana”
4. Parādītajās opcijās noklikšķiniet uz “Jauns noteikums”
5. Dialoglodziņā “Jauns formatēšanas noteikums” noklikšķiniet uz “Izmantot formulu, lai noteiktu, kuras šūnas formatēt”
6. Formulas laukā ievadiet šādu formulu: = B2Jan! B2
7. Noklikšķiniet uz pogas Formatēt
8. Parādītajā dialoglodziņā Šūnu formatēšana noklikšķiniet uz cilnes Aizpildīt un atlasiet krāsu, kurā vēlaties izcelt neatbilstošos datus.
9. Noklikšķiniet uz Labi
10. Noklikšķiniet uz Labi

Iepriekš minētās darbības uzreiz izceltu visas izmaiņas datu kopā abās lapās.

Kā tas darbojas?

Nosacījuma formatējums izceļ šūnu, kad dotā šīs šūnas formula atgriež TRUE. Šajā piemērā mēs salīdzinām katru vienas lapas šūnu ar atbilstošo šūnu otrā lapā (tas tiek darīts, izmantojot formulā opciju, kas nav vienāda ar operatoru).

Kad nosacījuma formatējums konstatē jebkādas datu atšķirības, tas izceļ to Jan lapā (tajā, kurā mēs esam piemērojuši nosacījuma formatējumu.

Ņemiet vērā, ka šajā piemērā esmu izmantojis relatīvo atsauci (A1, nevis $ A $ 1 vai $ A1 vai A $ 1).

Izmantojot šo metodi, lai salīdzinātu divas Excel lapas, atcerieties sekojošo;

• Šī metode ir laba, lai ātri noteiktu atšķirības, taču jūs to nevarat izmantot pastāvīgi. Piemēram, ja kādā no datu kopām ievadīšu jaunu rindu (vai dzēstu rindu), tas man sniegtu nepareizus rezultātus. Tiklīdz es ievietoju/izdzēsu rindu, visas nākamās rindas tiek uzskatītas par atšķirīgām un attiecīgi iezīmētas.
• Vienā Excel failā var salīdzināt tikai divas lapas
• Jūs varat salīdzināt tikai vērtību (nevis formulas vai formatējuma atšķirības).

Salīdziniet divus Excel failus/lapas un iegūstiet atšķirības, izmantojot formulu

Ja vēlaties tikai ātri salīdzināt un noteikt atšķirības starp divām lapām, varat izmantot formulu, lai iegūtu tikai tās atšķirīgās vērtības.

Lai izmantotu šo metodi, jums ir jābūt atsevišķai darblapai, kurā varat iegūt atšķirības.

Šī metode darbotos, ja vēlaties salīdzināt divas atsevišķas Excel darbgrāmatas vai darblapas tajā pašā darbgrāmatā.

Ļaujiet man parādīt piemēru, kurā es salīdzinu divas datu kopas divās lapās (tajā pašā darbgrāmatā).

Pieņemsim, ka jums ir datu kopa, kas parādīta zemāk lapā ar nosaukumu Jan (un līdzīgi dati lapā, ko sauc par februāri), un vēlaties uzzināt, kādas vērtības atšķiras.

Lai salīdzinātu abas lapas, vispirms ievietojiet jaunu darblapu (sauksim šo lapu par “atšķirību”).

Šūnā A1 ievadiet šādu formulu:

= JA (Jan! A1Feb! A1, "Jan Value:" & Jan! A1 & CHAR (10) & "Feb Value:" & Feb! A1, "")

Kopējiet un ielīmējiet šo formulu diapazonam, lai tā aptvertu visu datu kopu abās lapās. Tā kā man ir neliela datu kopa, es kopēšu un ielīmēšu šo formulu tikai A1: B10 diapazonā.

Iepriekšminētā formula izmanto IF nosacījumu, lai pārbaudītu atšķirības. Ja vērtībās nav atšķirību, tā atgriezīsies tukša, un, ja ir atšķirība, tā atdos vērtības no abām lapām atsevišķās rindās vienā šūnā.

Šīs metodes labais ir tas, ka tā sniedz jums tikai atšķirības un precīzi parāda atšķirību. Šajā piemērā es viegli redzu, ka cena šūnās B4 un B8 ir atšķirīga (kā arī precīzās vērtības šajās šūnās).

Salīdziniet divus Excel failus/lapas un iegūstiet atšķirības, izmantojot VBA

Ja jums ir diezgan bieži jāsalīdzina Excel faili vai lapas, ieteicams iegūt gatavu Excel makro VBA kodu un izmantot to ikreiz, kad nepieciešams veikt salīdzinājumu.

Makro varat pievienot arī ātrās piekļuves rīkjoslai, lai piekļūtu ar vienu pogu un uzreiz zinātu, kādas šūnas atšķiras dažādos failos/lapās.

Pieņemsim, ka jums ir divas lapas janvāris un februāris un vēlaties salīdzināt un izcelt atšķirības Jāņa lapā, varat izmantot zemāk esošo VBA kodu:

Sub CompareSheets () Dim rngCell kā diapazons katrai rngCell darblapai ("Jan"). UsedRange If Not rngCell = Darblapas ("februāris"). Ja Next rngCell End Sub

Iepriekš minētais kods izmanto cilni Nākamajai, lai izietu cauri visām Jan lapas šūnām (visu izmantoto diapazonu) un salīdzinātu to ar atbilstošo šūnu februāra lapā. Gadījumā, ja tiek konstatēta atšķirība (kas tiek pārbaudīta, izmantojot paziņojumu Ja-tad), tas izceļ šīs šūnas dzeltenā krāsā.

Šo kodu varat izmantot parastajā modulī VB redaktorā.

Un, ja jums tas jādara bieži, labāk ir saglabāt šo kodu personīgās makro darbgrāmatā un pēc tam pievienot ātrās piekļuves rīkjoslai. Šādā veidā jūs varēsit veikt šo salīdzinājumu, noklikšķinot uz pogas.

Šeit ir norādītas darbības, lai iegūtu personīgo makro darbgrāmatu programmā Excel (tā nav pieejama pēc noklusējuma, tāpēc jums tā ir jāiespējo).

Tālāk ir norādītas darbības, lai šo kodu saglabātu personiskajā makro darbgrāmatā.

Un šeit jūs atradīsit darbības, kā pievienot šo makro kodu QAT.

Izmantojot trešās puses rīku - XL Comparator

Vēl viens ātrs veids, kā salīdzināt divus Excel failus un pārbaudīt atbilstības un atšķirības, ir izmantot bezmaksas trešās puses rīku, piemēram, XL Comparator.

Šis ir tīmekļa rīks, kurā varat augšupielādēt divus Excel failus, un tas izveidos salīdzināšanas failu, kurā būs ierastie dati (vai dažādi dati atkarībā no izvēlētās opcijas).

Pieņemsim, ka jums ir divi faili, kuriem ir klientu datu kopas (piemēram, vārds un e -pasta adrese), un vēlaties ātri pārbaudīt, kādi klienti ir, ir 1. fails, nevis 2. fails.

Tālāk ir aprakstīts, kā salīdzināt divus Excel failus un izveidot salīdzināšanas pārskatu.

1. Atveriet vietni https://www.xlcomparator.net/
2. Izmantojiet opciju Izvēlēties failu, lai augšupielādētu divus failus (katra faila maksimālais izmērs var būt 5 MB)
3. Noklikšķiniet uz pogas Tālāk.
4. Abos šajos failos atlasiet kopējo kolonnu. Šis rīks izmantos šo kopīgo kolonnu, lai meklētu atbilstības un atšķirības
5. Izvēlieties vienu no četrām opcijām neatkarīgi no tā, vai vēlaties iegūt atbilstošus datus vai dažādus datus (pamatojoties uz 1. vai 2. failu)
6. Noklikšķiniet uz Tālāk
7. Lejupielādējiet salīdzināšanas failu, kurā būs dati (pamatojoties uz to, kādu opciju izvēlējāties 5. darbībā)

Zemāk ir video, kas parāda XL Comparator rīka darbību.

Viena problēma, kas jums var rasties, izmantojot trešās puses rīku Excel failu salīdzināšanai, ir saistīta ar privātumu. Ja jums ir konfidenciāli dati un privātums tam ir patiešām svarīgs, labāk ir izmantot citas iepriekš parādītās metodes. Ņemiet vērā, ka vietnē XL Comparator ir minēts, ka tie izdzēš visus failus pēc vienas stundas salīdzināšanas.

Šīs ir dažas no metodēm, kuras varat izmantot, lai salīdzinātu divus dažādus Excel failus (vai darblapas tajā pašā Excel failā). Ceru, ka šī Excel apmācība jums šķita noderīga.