Когато работите с големи количества данни, е лесно да загубите следите на данните и да ги видите да се появяват няколко пъти в таблицата. Някои дубликати са поставени умишлено, докато други са грешки. Каквито и да са случаите, може да искате тези дубликати да бъдат автоматично подчертани за вас.
Намирането на дублирани клетки в малка електронна таблица е лесно, но когато се занимавате с големи, сложни набори от данни, може да бъде доста трудно да го направите ръчно. За щастие, в Excel има вградени инструменти, които ви позволяват да маркирате дублирани стойности. В този урок ще ви покажем как да маркирате дублирани данни с функцията за условно форматиране в Excel.
Маркирайте дубликати с условно форматиране в Excel. Като цяло може да искате да намерите дубликати в Excel, защото повече от често дубликатите се появяват по погрешка и трябва да бъдат изтрити или дубликатите са важни за анализ и трябва да бъдат подчертани в Excel.
Има два метода за намиране на дублиращи се стойности с условно форматиране в Excel. Те са:
- Маркирайте дубликатите с помощта на правилото за дублирана стойност
- Маркирайте дубликати с помощта на персонализирана формула на Excel (COUNTIF и COUNTIFS)
Маркирайте дубликатите, като използвате правилото за дублирани стойности
Да приемем, че имаме този набор от данни:
Първо изберете диапазона от клетки, който съдържа дублиращи се стойности. След това отидете в раздела „Начало“, щракнете върху „Условно форматиране“ в секцията „Стилове“ на лентата. В падащото меню преместете курсора си върху първата опция за „Открояване на правилата на клетките“ и тя отново ще покаже списък с правила в изскачащо поле. Изберете опцията „Дублиращи се стойности“ тук.
След като щракнете върху Duplicate Values, ще се появи диалоговият прозорец Duplicate Values. Тук можете да изберете типа форматиране за дублираните стойности. Можете да избирате от цветове, за да запълните само клетките, само за шрифта, като рамка или персонализиран формат, ако предпочитате. След това щракнете върху „OK“, за да затворите диалоговия прозорец.
Тук избираме „Зелено запълване с тъмнозелен текст“ за нашия пример.
След като изберете типа форматиране, той ще подчертае всички дублиращи се стойности в избрания диапазон, както е показано по-долу.
Маркирайте Дублират Uпейте COUNTIF Formula
Друг метод за подчертаване на дублиращи се стойности е използването на условно форматиране с проста формула COUNTIF в една колона или в множество колони.
Изберете диапазона от данни, където искате да маркирате дубликатите. След това в раздела „Начало“ и кликнете върху опцията „Условно форматиране“. В падащото меню щракнете върху опцията „Ново правило“.
Това ще отвори диалогов прозорец Ново правило за форматиране.
В диалоговия прозорец Ново правило за форматиране изберете опцията „Използвайте формула, за да определите кои клетки да форматирате“ под списъчното поле Изберете тип правило, след което въведете следната формула COUNTIF, за да преброите дубликатите.
=COUNTIF($A$1:$C$11,A1)>1
След това щракнете върху бутона „Форматиране“, за да отидете в диалоговия прозорец Форматиране на клетки. В диалоговия прозорец Форматиране на клетки можете да изберете цвета на запълване от цветовата палитра за маркиране на клетките и след това щракнете върху „OK“. Тук избираме син цвят за запълване, за да форматираме дубликатите.
След това щракнете отново върху „OK“, за да затворите диалоговия прозорец. Формулата ще подчертае всички стойности на клетките, които се появяват повече от веднъж.
Винаги въвеждайте формулата за горната лява клетка в избрания диапазон (A1:C11). Excel автоматично копира формулата в другите клетки.
Можете също да дефинирате правила, както искате. Например, ако искате да намерите стойности, които се появяват само два пъти в таблица, въведете тази формула вместо това (в диалоговия прозорец Ново правило за форматиране):
=COUNTIF($A$1:$C$11,A1)=2Резултатът:
Понякога може да искате да видите само дубликатите и да филтрирате уникалните стойности. За да направите това, изберете диапазона, отидете на раздела Начало, щракнете върху опцията „Сортиране и филтриране“ в горния десен ъгъл на excel и изберете опцията „Филтър“.
След това първата клетка на всяка колона ще покаже падащо меню, където можете да дефинирате критерия за филтриране. Щракнете върху падащото меню в първата клетка на колоната и изберете „Филтриране по цвят“. След това изберете синия цвят.
Сега ще виждате само маркираните клетки и можете да правите каквото искате с тях.
Намерете и маркирайте дублиращи се редове в Excel с помощта на формула COUNTIFS
Ако искате да намерите и маркирате дублиращи се редове в Excel, използвайте COUNTIFS вместо COUNTIF.
Изберете диапазона, отидете на раздела „Начало“ и щракнете върху „Условно форматиране в групата Стилове. В падащото меню щракнете върху опцията „Ново правило“.
В диалоговия прозорец Ново правило за форматиране изберете опцията „Използвайте формула, за да определите кои клетки да форматирате“ под списъчното поле Изберете тип правило, след което въведете формулата COUNTIFS по-долу:
=COUNTIFS($A$1:$A$20,$A1,$B$1:$B$20,$B1,$C$1:$C$20,$C1)>1В горната формула диапазонът A1:A20 се отнася до колона A, B1:B20 се отнася до колона B и C1:C20 се отнася до колона C. Формулата отчита броя на редовете въз основа на множество критерии (A1, B2 и C1) .
След това щракнете върху бутона „Форматиране“, за да изберете стил на форматиране и щракнете върху „OK“.
Сега Excel подчертава само дублиращите се редове, както е показано по-долу.
Това е.