Можете да намерите и маркирате дублиращите се записи между две колони, като използвате функцията за условно форматиране в Google Таблици.
Докато работите в Google Таблици с големи набори от данни, вероятно ще се сблъскате с проблем, при който трябва да се справите с много дублиращи се стойности. Докато някои дублиращи се записи са поставени умишлено, докато други са грешки. Това е особено вярно, когато си сътрудничите на един и същи лист с екип.
Когато става въпрос за анализиране на данни в Google Sheets, възможността за филтриране на дубликати може да бъде от съществено значение и удобна. Въпреки че Google Sheets няма естествена поддръжка за намиране на дубликати в листове, той предлага няколко начина за сравняване, идентифициране и премахване на дублиращи се данни в клетки.
Понякога искате да сравните всяка стойност в колона с друга колона и да разберете дали има дубликати в нея и обратно. В Google Sheets можете лесно да намерите дубликати между две колони с помощта на функцията за условно форматиране. В тази статия ще ви покажем как да сравните две колони в Google Sheets и да намерите дубликати между тях.
Намерете дублиращи се записи между две колони с помощта на условно форматиране
Условното форматиране е функция в Google Sheets, която позволява на потребителя да прилага специфични формати като цвят на шрифта, икони и ленти с данни към клетка или диапазон от клетки въз основа на определени условия.
Можете да използвате това условно форматиране, за да маркирате дублиращите се записи между две колони, като запълните клетките с цвят или промените цвета на текста. Трябва да сравните всяка стойност в колона с друга колона и да разберете дали някоя стойност се повтаря. За да работи това, трябва да приложите условно форматиране към всяка колона поотделно. Следвайте тези стъпки, за да направите това:
Отворете електронната таблица, която искате да проверите за дубликати в Google Таблици. Първо изберете първата колона (A), за да проверите с колона B. Можете да маркирате цялата колона, като щракнете върху буквата на колоната над нея.
След това щракнете върху менюто „Формат“ от лентата с менюта и изберете „Условно форматиране“.
Менюто за условно форматиране се отваря от дясната страна на google sheets. Можете да потвърдите, че диапазонът от клетки е това, което сте избрали под опцията „Приложи към диапазона“. Ако искате да промените диапазона, щракнете върху „иконата на диапазон“ и изберете различен диапазон.
След това щракнете върху падащото меню под „Правила за форматиране“ и изберете опцията „Персонализирана формула е“.
Сега трябва да въведете персонализирана формула в полето „Стойност или формула“.
Ако сте избрали цяла колона (B:B), въведете следната формула COUNTIF в полето „Стойност или формула“ под Правила за форматиране:
=countif($B:$B,$A2)>0Или,
Ако сте избрали диапазон от клетки в колона (да речем сто клетки, A2:A30), използвайте тази формула:
=COUNTIF($B$2:$B$30, $A2)>0Когато въвеждате формулата, не забравяйте да замените всички екземпляри на буквата „B“ във формулата с буквата на колоната, която сте маркирали. Добавяме знака „$“ преди препратките към клетките, за да ги направим абсолютен диапазон, така че да не се промени, прилагаме формулата.
В секцията Стил на форматиране можете да изберете стила на форматиране за маркиране на дублиращите се елементи. По подразбиране ще използва зеления цвят на запълване.
Можете да изберете един от предварително зададените стилове на форматиране, като щракнете върху „По подразбиране“ под опциите „Стил на форматиране“, след което изберете един от предварително зададените.
Или можете да използвате някой от седемте инструмента за форматиране (Удебелен, Курсив, Подчертаване, Зачертаване, Цвят на текста, Цвят на запълване) в секцията „Стил на форматиране“, за да маркирате дубликатите.
Тук избираме цвят за запълване на дублиращите се клетки, като щракваме върху иконата „Цвят на запълване“ и избираме „жълтия“ цвят.
След като изберете форматирането, щракнете върху „Готово“, за да маркирате клетките.
Функцията COUNTIF отчита колко пъти всяка стойност на клетка в „Колона A“ се появява в „Колона B“. Така че, ако даден елемент се появи дори веднъж в колона B, формулата връща TRUE. След това този елемент ще бъде маркиран в „Колона A“ въз основа на форматирането, което сте избрали.
Това не подчертава дубликатите, а по-скоро подчертава елементите, които имат дубликати в колона B. Това означава, че всеки осветен в жълто елемент има дубликати в колона B.
Сега трябва да приложим условно форматиране към колона B, използвайки същата формула. За да направите това, изберете втората колона (B2:B30), отидете в менюто „Формат“ и изберете „Условно форматиране“.
Друга възможност е да кликнете върху бутона „Добавяне на друго правило“ под екрана „Правила за условен формат“.
След това потвърдете диапазона (B2:B30) в полето „Приложи към диапазона“.
След това задайте опцията „Форматиране на клетки, ако...“ на „Персонализирана формула е“ и въведете формулата по-долу в полето за формула:
=COUNTIF($A$2:$A$30, $B2)>0Тук използваме колона А диапазон ($A$2:$A$30) в първия аргумент и „$B2“ във втория аргумент. Тази формула ще провери стойността на клетката в „колона B“ спрямо всяка клетка в колона A. Ако бъде намерено съвпадение (дубликат), тогава условното форматиране ще издигне този елемент в „колона B“
След това посочете форматирането в опциите „Стил на форматиране“ и щракнете върху „Готово“. Тук избираме оранжев цвят за колона B.
Това ще подчертае елементите в колона B, които имат дубликати в колона A. Сега, вие сте намерили и осветили дублиращи се елементи между две колони.
Вероятно сте забелязали, въпреки че има дубликат за „Arcelia“ в колона A, той не е подчертан. Това е така, защото дублираната стойност е само в една колона (A), а не между колоните. Следователно не е подчертано.
Маркирайте дубликати между две колони в един и същи ред
Можете също да маркирате редовете, които имат еднакви стойности (дубликати) между две колони, като използвате условно форматиране. Правилото за условно форматиране може да провери всеки ред и да подчертае редовете, които имат съвпадащи данни в двете колони. Ето как да направите това:
Първо изберете и двете колони, които искате да сравните, след това отидете в менюто „Формат“ и изберете „Условно форматиране“.
В екрана с правила за условен формат потвърдете диапазона в полето „Прилагане към диапазона“ и изберете „Персонализирана формула е“ от падащото меню „Клетки на формула, ако...“.
След това въведете формулата по-долу в полето „Стойност или формула“:
=$A2=$B2Тази формула ще сравнява двете колони ред по ред и ще подчертае редове, които имат идентични стойности (дубликати). Както можете да видите, въведената тук формула е само за първия ред от избрания диапазон, но формулата ще бъде приложена автоматично към всички редове в избрания диапазон от функцията за условно форматиране.
След това посочете форматирането от опциите „Стил на форматиране“ и щракнете върху „Готово“.
Както можете да видите, само редовете, които имат съвпадащи данни (дубликати) между две колони, ще бъдат маркирани, а всички други дубликати ще бъдат игнорирани.
Маркирайте дублиращи се клетки в множество колони
Когато работите с по-големи електронни таблици с много колони, може да искате да маркирате всички дубликати, които се появяват в множество колони, вместо само в една или две колони. Все още можете да използвате условно форматиране, за да маркирате дубликата в множество колони.
Първо изберете диапазона от всички колони и редове, които искате да търсите за дубликати, вместо само една или две колони. Можете да изберете цели колони, като задържите клавиша Ctrl, след което щракнете върху буквата в горната част на всяка колона. Като алтернатива, можете също да щракнете върху първата и последната клетка във вашия диапазон, като същевременно задържите клавиша Shift, за да изберете няколко колони наведнъж.
В примера избираме A2:C30.
След това щракнете върху опцията „Форматиране“ в менюто и изберете „Условно форматиране“.
В правилата за условен формат задайте правилата за формат на „Персонализирана формула е“ и след това въведете следната формула в полето „Стойност или формула“:
=countif($A$2:$C$30,A2)>Добавяме знака „$“ преди препратките към клетките, за да ги направим абсолютни колони, така че да не се промени, прилагаме формулата. Можете също да въведете формулата без знаците „$“, тя работи и в двата случая.
След това изберете форматирането, при което искате да маркирате дублиращите се клетки, като използвате опциите „Стил на форматиране“. Тук избираме „Жълт“ цвят на запълване. След това щракнете върху „Готово“.
Това ще подчертае дубликатите във всички избрани от вас колони, както е показано по-долу.
След като приложите условното форматиране, можете да редактирате или изтриете правилото за условно форматиране по всяко време.
Ако искате да редактирате текущото правило за условно форматиране, изберете която и да е клетка с условно форматиране, отидете на „Формат“ в менюто и изберете „Условно форматиране“.
Това ще отвори прозореца „Правила за условен формат“ вдясно със списък с правила за формат, приложени към текущата селекция. Когато задържите курсора на мишката върху правилото, то ще ви покаже бутона за изтриване, щракнете върху бутона за изтриване, за да премахнете правилото. Или, ако искате да редактирате правилото, което се показва в момента, щракнете върху самото правило.
Ако искате да добавите друго условно форматиране върху текущото правило, щракнете върху бутона „Добавяне на друго правило“.
Пребройте дубликатите между две колони
Понякога искате да преброите колко пъти стойността в една колона се повтаря в друга колона. Може лесно да се направи с помощта на същата функция COUNTIF.
За да намерите колко пъти дадена стойност в колона A съществува в колона B, въведете следната формула в клетка в друга колона:
=COUNTIF($B$2:$B$30,$A2)Въведете тази формула в клетка C2. Тази формула отчита колко пъти стойността в клетка A2 съществува в колоната (B2:B30) и връща броя в клетка C2.
Когато въведете формулата и натиснете Enter, ще се появи функцията за автоматично попълване, щракнете върху „Отметка“, за да попълните автоматично тази формула до останалите клетки (C3:C30).
Ако функцията за автоматично попълване не се появи, щракнете върху синия квадрат в долния десен ъгъл на клетка C2 и го плъзнете надолу, за да копирате формулата в клетка C2 в клетките C3:C30.
Колона „Сравнение 1“ (C) сега ще ви покаже колко пъти всяка съответна стойност в колона A се появява в колона B. Например, стойността на A2 или „Franklyn“ не се намира в колона B, така че Функцията COUNTIF връща „0“. И стойността „Loreta“ (A5) се намира два пъти в колона B, следователно, тя връща „2“.
Сега трябва да повторим същите стъпки, за да намерим дублирания брой на колона B. За да направите това, въведете следната формула в клетка D2 в колона D (Сравнение 2):
=COUNTIF($A$2:$A$30,$B2)В тази формула заменете диапазона от „$B$2:$B$30“ на „$A$2:$A$30“ и „$B2“ на „$A2“. Функцията отчита колко пъти стойността в клетка B2 съществува в колона A (A2:A30) и връща броя в клетка D2.
След това автоматично попълнете формулата до останалите клетки (D3:D30) в колона D. Сега „Сравнение 2“ ще ви покаже колко пъти всяка съответна стойност в колона B се появява в колона A. Например , стойността на B2 или “Stark” се намира два пъти в колона A, така че функцията COUNTIF връща “2”.
Забележка: Ако искате да преброите дубликатите във всички колони или няколко колони, просто трябва да промените диапазона в първия аргумент на функцията COUNTIF на няколко колони вместо само на една колона. Например променете диапазона от A2:A30 на A2:B30, което ще преброи всички дубликати в две колони вместо само в една.
Това е.