Как да сравним два листа на Excel (за разлики)

Гледайте видео - Как да сравните два листа на Excel за разлики

Сравняването на два файла в Excel (или сравняването на два листа в един и същ файл) може да бъде трудно, тъй като работната книга на Excel показва само един лист наведнъж.

Това става по-трудно и податливо на грешки, когато имате много данни, които трябва да бъдат сравнени.

За щастие, има някои готини функции в Excel, които ви позволяват да отваряте и лесно да сравнявате два Excel файла.

В този урок по Excel ще ви покажа няколко начина да сравнете два различни Excel файла (или листа) и проверете за разлики. Избраният от вас метод ще зависи от това как са структурирани вашите данни и какъв вид сравнение търсите.

Да започваме!

Сравнете два листа на Excel в отделни файлове на Excel (един до друг)

Ако искате да сравните два отделни файла на Excel един до друг (или два листа в една и съща работна книга), има вградена функция в Excel, за да направите това.

Това е Изглед Рамо до рамо опция.

Това се препоръчва само когато имате малък набор от данни и ръчното сравняване на тези файлове вероятно ще отнеме по-малко време и е склонно към грешки. Ако имате голям набор от данни, препоръчвам да използвате условния метод или метода на формулата, обхванат по -късно в този урок.

Нека да видим как да използваме това, когато трябва да сравните два отделни файла или два листа в един и същ файл.

Да предположим, че имате два файла за два различни месеца и искате да проверите какви стойности са различни в тези два файла.

По подразбиране, когато отворите файл, е вероятно да заема целия ви екран. Дори и да намалите размера, винаги виждате един Excel файл отгоре.

С опцията изглед рамо до рамо, можете да отворите два файла и след това да ги подредите хоризонтално или вертикално. Това ви позволява лесно да сравнявате стойностите, без да превключвате напред и назад.

По -долу са стъпките за подравняване на два файла един до друг и за тяхното сравнение:

1. Отворете файловете, които искате да сравните.
2. Във всеки файл изберете листа, който искате да сравните.
3. Щракнете върху раздела Изглед
4. В групата на Windows щракнете върху опцията „Преглед рамо до рамо“. Това става достъпно само когато имате отворени два или повече Excel файла.

Веднага щом щракнете върху опцията Преглед рамо до рамо, Excel ще подреди работната книга хоризонтално. И двата файла ще бъдат видими и вие сте свободни да редактирате/сравнявате тези файлове, докато са подредени един до друг.

В случай, че искате да подредите файловете вертикално, щракнете върху опцията Подреди всички (в раздела Изглед).

Това ще отвори диалоговия прозорец „Подреждане на Windows“, където можете да изберете „Вертикално“.

В този момент, ако превъртите надолу в един от работните листове, другият ще остане такъв, какъвто е. Можете да промените това, така че когато превъртате в един лист, другият също да се превърта едновременно. Това улеснява сравняването ред по ред и откриването на разлики.

Но за да направите това, трябва да активирате Синхронно превъртане.

За да активирате синхронното превъртане, щракнете върху раздела Изглед (във всяка от работните книги) и след това щракнете върху опцията Синхронно превъртане. Това е бутон за превключване (така че ако искате да го изключите, просто кликнете отново върху него).

Сравняване на няколко листа в отделни файлове на Excel (Рамо до рамо)

С опцията „Преглед рамо до рамо“ можете да сравнявате само два файла на Excel наведнъж.

В случай, че имате отворени множество файлове на Excel, когато щракнете върху опцията Преглед рамо до рамо, тя ще ви покаже диалоговия прозорец „Сравняване рамо до рамо“, където можете да изберете кой файл искате да сравните с активната работна книга.

В случай, че искате да сравните повече от два файла наведнъж, отворете всички тези файлове и след това щракнете върху опцията Подреди всички (тя е в раздела Изглед).

В диалоговия прозорец Подреждане на Windows изберете Вертикално/Хоризонтално и след това щракнете върху OK.

Това ще подреди всички отворени Excel файлове в избрания ред (вертикален или хоризонтален).

Сравнете два листа (един до друг) в същата работна книга на Excel

В случай, че искате да сравните два отделни листа в една и съща работна книга, не можете да използвате функцията Преглед рамо до рамо (тъй като тя работи само за отделни файлове на Excel).

Но все пак можете да направите същото сравнение едно до друго.

Това става възможно чрез „Нова функция на Windows в Excel, което ви позволява да отваряте два екземпляра в една и съща работна книга. След като отворите два екземпляра, можете да ги подредите един до друг и след това да ги сравните.

Да предположим, че имате работна книга на Excel, която има два листа за два различни месеца (януари и февруари) и искате да ги сравните един до друг, за да видите как са се променили продажбите на магазин:

По -долу са стъпките за сравняване на два листа в Excel:

1. Отворете работната книга, която съдържа листовете, които искате да сравните.
2. Щракнете върху раздела Изглед
3. В групата Window кликнете върху опцията „New Window“. Това отваря втория екземпляр на същата работна книга.
4. В раздела „Изглед“ кликнете върху „Подредете всички“. Това ще отвори диалоговия прозорец Подреждане на Windows
5. Изберете „Вертикално“, за да сравните данните в колони (или изберете Хоризонтално, ако искате да сравните данните в редове).
6. Щракнете върху OK.

Горните стъпки биха подредили и двата екземпляра на работната книга вертикално.

Към този момент и в двете работни книги ще бъде избран един и същ работен лист. В една от работните книги изберете другия лист, който искате да сравните с активния лист.

Как работи това?

Когато щракнете върху Нов прозорец, той отново отваря същата работна книга с малко по -различно име. Например, ако името на вашата работна книга е „Тест“ и щракнете върху Нов прозорец, тя ще кръсти вече отворената работна книга „Тест - 1“, а вторият екземпляр - „Тест - 2“.

Обърнете внимание, че това са все същата работна книга. Ако направите някакви промени в някоя от тези работни книги, това ще бъде отразено и в двете.

И когато затворите всеки един екземпляр на отворения файл, името ще се върне към оригинала.

Можете също да активирате синхронното превъртане, ако искате (като щракнете върху опцията „Синхронно превъртане“ в раздела „Преглед“)

Сравнете два листа и подчертайте разликите (използвайки условно форматиране)

Въпреки че можете да използвате горния метод за подравняване на работните книги заедно и ръчно преминаване през данните ред по ред, това не е добър начин в случай, че имате много данни.

Освен това ръчното извършване на това ниво на сравнение може да доведе до много грешки.

Така че вместо да правите това ръчно, можете да използвате силата на условното форматиране, за да подчертаете бързо всички разлики в двата листа на Excel.

Този метод е наистина полезен, ако имате две версии в два различни листа и искате бързо да проверите какво се е променило.

Обърнете внимание, че вие НЕ МОГА сравнете два листа в различни работни книги.

Тъй като условното форматиране не може да се отнася до външен Excel файл, листата, които трябва да сравните, трябва да са в една и съща работна книга на Excel. В случай, че това не е така, можете да копирате лист от другия файл в активната работна книга и след това да направите това сравнение.

За този пример, да предположим, че имате набор от данни, както е показано по -долу за два месеца (януари и февруари) в два различни листа и искате бързо да сравните данните в тези два листа и да проверите дали цените на тези артикули са се променили или не.

По -долу са описани стъпките за това:

1. Изберете данните в листа, където искате да подчертаете промените. Тъй като искам да проверя как цените са се променили от януари до февруари, избрах данните в листа за февруари.
2. Щракнете върху раздела Начало
3. В групата Стилове щракнете върху „Условно форматиране“
4. В опциите, които се показват, щракнете върху „Ново правило“
5. В диалоговия прозорец „Ново правило за форматиране“ кликнете върху „Използвайте формула, за да определите кои клетки да форматирате“
6. В полето за формула въведете следната формула: = B2Jan! B2
7. Щракнете върху бутона Форматиране
8. В диалоговия прозорец Форматиране на клетки, който се показва, щракнете върху раздела „Попълване“ и изберете цвета, в който искате да подчертаете несъответстващите данни.
9. Щракнете върху OK
10. Щракнете върху OK

Горните стъпки незабавно ще подчертаят всички промени в набора от данни в двата листа.

Как работи това?

Условното форматиране подчертава клетка, когато дадената формула за тази клетка връща TRUE. В този пример сравняваме всяка клетка в един лист със съответната клетка в другия лист (направено с помощта на оператора, който не е равен на формулата).

Когато условното форматиране установи някаква разлика в данните, това подчертава това в листа Jan (този, в който сме приложили условното форматиране.

Имайте предвид, че съм използвал относителна препратка в този пример (A1, а не $ A $ 1 или $ A1 или A $ 1).

Когато използвате този метод за сравняване на два листа в Excel, не забравяйте следното;

• Този метод е добър за бързо идентифициране на разликите, но не можете да го използвате постоянно. Например, ако въведа нов ред в някой от наборите от данни (или изтрия ред), това ще ми даде неправилни резултати. Веднага щом вмъкна/изтрия реда, всички следващи редове се считат за различни и съответно се подчертават.
• Можете да сравнявате само два листа в един и същ Excel файл
• Можете да сравнявате само стойността (не разликата във формулата или форматирането).

Сравнете два файла/листа на Excel и получете разликите с помощта на формула

Ако се интересувате само от бързо сравняване и идентифициране на разликите между два листа, можете да използвате формула, за да извлечете само онези различни стойности.

За този метод ще трябва да имате отделен работен лист, където можете да извлечете разликите.

Този метод ще работи, ако искате да сравните две отделни работни книги на Excel или работни листове в една и съща работна книга.

Нека ви покажа пример, в който сравнявам два набора от данни в два листа (в една и съща работна книга).

Да предположим, че имате набора от данни, както е показано по -долу в лист, наречен Jan (и подобни данни в лист, наречен февруари), и искате да знаете какви стойности са различни.

За да сравните двата листа, първо поставете нов работен лист (нека го наречем „Разлика“).

В клетка A1 въведете следната формула:

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

Копирайте и поставете тази формула за диапазон, така че да обхваща целия набор от данни в двата листа. Тъй като имам малък набор от данни, ще копирам и поставя само тази формула в диапазон A1: B10.

Горната формула използва условие IF, за да провери за разлики. В случай, че няма разлика в стойностите, тя ще върне празна, а в случай, че има разлика, тя ще върне стойностите от двата листа в отделни редове в една и съща клетка.

Хубавото на този метод е, че той ви дава само разликите и ви показва точно каква е разликата. В този пример мога лесно да видя, че цените в клетка В4 и В8 са различни (както и точните стойности в тези клетки).

Сравнете два файла/листа на Excel и получете разликите с помощта на VBA

Ако трябва да сравнявате файлове или листове на Excel доста често, добре е да имате готов Excel макро VBA код и да го използвате винаги, когато трябва да направите сравнението.

Можете също така да добавите макроса към лентата с инструменти за бърз достъп, така че да имате достъп с един бутон и незабавно да знаете кои клетки са различни в различните файлове/листове.

Да предположим, че имате два листа Jan и Feb и искате да сравните и подчертаете разликите в листа Jan, можете да използвате кода по -долу VBA:

Sub CompareSheets () Dim rngCell As Range for each rngCell In Worksheets ("Jan"). UsedRange If Not rngCell = Worksheets ("Feb"). Ако Следващ rngCell End Sub

Горният код използва цикъла For Next, за да премине през всяка клетка в листа Jan (целия използван диапазон) и го сравнява със съответната клетка в листа за февруари. В случай, че открие разлика (която се проверява с помощта на израза If-Then), тя подчертава тези клетки в жълто.

Можете да използвате този код в обикновен модул във VB Editor.

И ако трябва да правите това често, по -добре е да запазите този код в работната книга за лични макроси и след това да го добавите към лентата с инструменти за бърз достъп. По този начин ще можете да направите това сравнение с едно натискане на бутон.

Ето стъпките за получаване на личната работна книга за макроси в Excel (тя не е налична по подразбиране, така че трябва да я активирате).

Ето стъпките за запазване на този код в личната работна книга за макроси.

И тук ще намерите стъпките за добавяне на този макро код към QAT.

Използване на инструмент на трети страни - XL Comparator

Друг бърз начин да сравните два Excel файла и да проверите за съвпадения и разлики е чрез използване на безплатен инструмент на трета страна, като XL Comparator.

Това е уеб-базиран инструмент, където можете да качите два Excel файла и той ще създаде сравнителен файл, който ще съдържа общите данни (или различни данни въз основа на избраната от вас опция).

Да предположим, че имате два файла, които имат набори от данни на клиенти (като име и имейл адрес) и искате бързо да проверите какви са клиентите, има файл 1, а не във файл 2.

По -долу е описано как сравнявате два файла на Excel и създавате отчет за сравнение:

1. Отворете https://www.xlcomparator.net/
2. Използвайте опцията Избор на файл, за да качите два файла (максималният размер на всеки файл може да бъде 5 MB)
3. Щракнете върху бутона Напред.
4. Изберете общата колона и в двата файла. Инструментът ще използва тази обща колона, за да търси съвпадения и разлики
5. Изберете една от четирите опции, независимо дали искате да получите съответстващи данни или различни данни (въз основа на Файл 1 или Файл 2)
6. Щракнете върху Напред
7. Изтеглете файла за сравнение, който ще съдържа данните (въз основа на опцията, която сте избрали в стъпка 5)

По -долу е видео, което показва как работи инструментът XL Comparator.

Едно притеснение, което може да имате, когато използвате инструмент на трета страна за сравняване на файлове на Excel, е относно поверителността. Ако имате поверителни данни и поверителността е наистина важна за тях, по -добре е да използвате други методи, показани по -горе. Обърнете внимание, че уебсайтът на XL Comparator споменава, че изтриват всички файлове след 1 час след сравнението.

Това са някои от методите, които можете да използвате, за да сравните два различни Excel файла (или работни листове в един и същ Excel файл). Надявам се, че сте намерили този урок за Excel полезен.