Типична задача, която периодично възниква пред всеки потребител на Excel, е да сравни два диапазона с данни и да намери разлики между тях. Методът на решение в този случай се определя от вида на изходните данни.
Вариант 1. Синхронни списъци
Ако списъците са синхронизирани (сортирани), тогава всичко се прави много просто, защото всъщност е необходимо да се сравнят стойностите в съседни клетки на всеки ред. Като най-прост вариант използваме формула за сравняване на стойности, която произвежда булеви стойности на изхода TRUE (ВЯРНО) or ЛЪЖА (НЕВЯРНО):
Броят на несъответствията може да се изчисли по формулата:
=SUMPRODUCT(—(A2:A20<>B2:B20))
или на английски =SUMPRODUCT(—(A2:A20<>B2:B20))
Ако резултатът е нула, списъците са идентични. Иначе имат разлики. Формулата трябва да се въведе като формула за масив, т.е след като въведете формулата в клетката, не натискайте Въведете, И Ctrl + Shift + Enter.
Ако трябва да направите нещо с различни клетки, тогава друг бърз метод ще свърши работа: изберете двете колони и натиснете клавиша F5, след което в отворения прозорец бутона Маркирайте (Специален) - Разлики в линиите (Разлики в редовете). В най-новите версии на Excel 2007/2010 можете да използвате и бутона Намерете и изберете (Намиране и избор) – Избиране на група клетки (Отидете на Специални) етикет Начало (У дома)
Excel ще маркира клетки, които се различават по съдържание (по ред). След това те могат да бъдат обработени, например:
- запълнете с цвят или по някакъв начин визуално форматирайте
- чисти с ключ Изтрий
- попълнете всичко наведнъж с една и съща стойност, като я въведете и натиснете Ctrl + Enter
- изтрийте всички редове с избрани клетки с помощта на командата Начало — Изтриване — Изтриване на редове от листа (Начало — Изтриване — Изтриване на редове)
- и т.н.
Вариант 2: Разбъркани списъци
Ако списъците са с различни размери и не са сортирани (елементите са в различен ред), тогава трябва да отидете в обратната посока.
Най-простото и най-бързо решение е да активирате цветното подчертаване на разликите с помощта на условно форматиране. Изберете двата диапазона с данни и изберете в раздела начало – Условно форматиране – Правила за маркиране на клетки – Дублирани стойности:
Ако изберете опцията Повтарящ се, след което Excel ще подчертае съвпаденията в нашите списъци, ако опцията Уникален – различия.
Цветното подчертаване обаче не винаги е удобно, особено за големи маси. Освен това, ако елементите могат да се повтарят в самите списъци, тогава този метод няма да работи.
Като алтернатива можете да използвате функцията COUNTIF (COUNTIF) от категория Статистически, което отчита колко пъти всеки елемент от втория списък се среща в първия:
Получената нула показва разликите.
И накрая, "висш пилотаж" - можете да покажете разликите в отделен списък. За да направите това, ще трябва да използвате формула за масив:
Изглежда страшно, но върши работата перфектно 😉
- Маркирайте дубликатите в списъка с цвят
- Сравняване на два диапазона с добавката PLEX
- Забрана за въвеждане на дублиращи се стойности