Снощи в радиопредаването на Computer America на Крейг Кросман Джо от Бостън имаше въпрос:
Имам колона с номера на фактури. Как мога да използвам Excel за маркиране на дубликати?
Предложих да използвам условни формати и формулата COUNTIF. Ето подробности за това как да направите това да работи.
Искаме да настроим условно форматиране за целия диапазон, но е по-лесно да настроим условен формат за първата клетка в диапазона и след това да копираме този условен формат. В нашия случай клетка А1 има заглавие на номер на фактура, така че ще избера клетка А2 и от менюто изберете Форматиране> Условно форматиране. Диалоговият прозорец Условно форматиране започва с първоначалното падащо меню, което казва „Стойността на клетката е“. Ако докоснете стрелката до това, можете да изберете „Формула е“.
След като изберете „Формула е“, диалоговият прозорец променя външния вид. Вместо полета за „Между x и y“, сега има едно поле за формула. Тази кутия за формула е невероятно мощна. Можете да въведете всяка формула, която можете да измислите, стига тази формула да е TRUE или FALSE.
В нашия случай трябва да използваме формула COUNTIF. Формулата за въвеждане в полето е
=COUNTIF(A:A,A2)>1
На английски това казва, „прегледайте целия диапазон на колона А. Пребройте колко клетки в този диапазон са със същата стойност като това в А2. (Наистина е важно„ А2 “във формулата да сочи към текуща клетка - клетката, в която задавате условно форматиране. Така че - ако данните ви са в колона E и вие задавате първото условно форматиране в E5, формулата ще бъде =COUNTIF(E:E,E5)>0). След това сравняваме, за да видим дали това се брои е> 1. В идеалния случай, без дубликати, броят винаги ще бъде 1 - тъй като клетка А2 е в диапазона - трябва да намерим точно една клетка в колона А, която съдържа същата стойност като А2.
Щракнете върху бутона Форматиране …
Сега е време да изберете неприятен формат. В горната част на този диалогов прозорец Форматиране на клетки има три раздела. Разделът Шрифт обикновено е първи, така че можете да изберете удебелен, червен шрифт, но ми харесва нещо по-неприятно. Обикновено щраквам върху раздела Модели и избирам или ярко червено, или ярко жълто. Изберете цвета, след което щракнете върху OK, за да затворите диалоговия прозорец Форматиране на клетки.
Ще видите избрания формат в полето „Преглед на използвания формат“. Щракнете върху OK, за да затворите диалоговия прозорец Условно форматиране …
… и нищо не се случва. Еха. Ако за първи път настройвате условно форматиране, би било наистина хубаво да получите обратна връзка тук, че е работило. Но освен ако нямате достатъчно късмет, че 1098 в клетка А2 е дубликат на друга клетка, условието не е вярно и изглежда, че нищо не се е случило.
Трябва да копирате условното форматиране от А2 надолу в останалите клетки от вашия диапазон. С перваза на курсора в A2 направете Edit> Copy. Натиснете Ctrl + интервал, за да изберете цялата колона. Направете Edit> Paste Special. В диалоговия прозорец Специално поставяне щракнете върху Формати. Щракнете върху OK.
Това ще копира условното форматиране във всички клетки в колоната. Сега - накрая - виждате няколко клетки с червеното форматиране, което показва, че имате дубликат.
Информативно е да отидете в клетка A3 и да разгледате условния формат след копието. Изберете A3, натиснете od, за да изведете условно форматиране. Формулата в полето Формула е променена, за да преброи колко пъти A3 се появява в колона A: A.
Бележки
Във въпроса на Джо той имаше само 1700 фактури в диапазона. Настроил съм 65536 клетки с условно форматиране и всяка клетка сравнява текущата клетка с 65536 други клетки. В Excel 2005 - с повече редове - проблемът ще бъде още по-лош. Технически формулата в първата стъпка би могла да бъде:=COUNTIF($A$2:$A$1751,A2)>1
Също така, когато копирате условния формат в цялата колона, можете вместо това да изберете само редовете с данни, преди да направите Поставяне на специални формати.
| Повече ▼
Другият проблем, който описах след въпроса, е, че наистина не можете да сортирате колона въз основа на условен формат. Ако трябва да сортирате тези данни, така че дубликатите да са в една област, следвайте тези стъпки. Първо, добавете заглавие към B1, наречено „Дублиране?“. Въведете тази формула в В2: =COUNTIF(A:A,A2)>1.
С показалеца на клетката в B2 щракнете върху манипулатора за автоматично попълване (малкия квадрат в долния десен ъгъл на клетката), за да копирате формулата докрай в диапазона.
Вече можете да сортирате по низходяща колона B и възходяща A, за да имате проблемните фактури в горната част на диапазона.
Това решение предполага, че искате да маркирате И ДВЕТЕ от дублираните фактури, за да можете ръчно да разберете кое да изтриете или коригирате. Ако не искате да маркирате първият вариант на дубликата, можете да регулирате формула, за да бъде: =COUNTIF($A$2:$A2,A2)>1. Важно е да въведете знаците за долар точно както е показано. Това ще разгледа само всички клетки от текущата клетка нагоре, търсейки дублиращи се записи.
Благодаря на Джо от Бостън за въпроса!
