Як параўнаць два слупкі ў Excel і выдаліць дублікаты (вылучыць, размаляваць, перамясціць)

Прачытанне гэтага артыкула зойме ў вас каля 10 хвілін. У наступныя 5 хвілін вы можаце лёгка параўнаць два слупка ў Excel і даведацца, ці ёсць у іх дублікаты, выдаліць іх або вылучыць колерам. Значыць, час прыйшоў!

Excel - гэта вельмі магутнае і сапраўды крутое прыкладанне для стварэння і апрацоўкі вялікіх аб'ёмаў дадзеных. Калі ў вас ёсць некалькі працоўных кніг з дадзенымі (або проста адна велізарная табліца), то вы, верагодна, хочаце параўнаць 2 слупкі, знайсці паўтаральныя значэнні, а затым зрабіць што-небудзь з імі, напрыклад, выдаліць, вылучыць або ачысціць змесціва. Слупкі могуць знаходзіцца ў адной табліцы, быць сумежнымі або несумежнымі, могуць размяшчацца на 2 розных лістах ці нават у розных кнігах.

Уявіце, што ў нас ёсць 2 слупкі з імёнамі людзей – па 5 імёнаў у слупку A і 3 імёны ў слупок B. Вам трэба параўнаць імёны ў гэтых двух слупках і знайсці дублікаты. Як вы разумееце, гэта фіктыўныя дадзеныя, узятыя выключна для прыкладу. У рэальных табліцах мы маем справу з тысячамі ці нават дзесяткамі тысяч запісаў.

Варыянт А: абедзве калонкі знаходзяцца на адным аркушы. Напрыклад, слупок A і калонка B.

Варыянт B: Слупкі на розных аркушах. Напрыклад, слупок A на аркушы Sheet2 і калонка A на аркушы Sheet3.

У Excel 2013, 2010 і 2007 ёсць убудаваны інструмент Выдаліць дублікаты (Выдаліць дублікаты), але ў гэтай сітуацыі ён бяссільны, бо не можа параўнаць дадзеныя ў 2 слупках. Больш за тое, ён можа выдаляць толькі дублікаты. Іншых варыянтаў, напрыклад вылучэння або змены колеру, няма. І кропка!

Далей я пакажу вам магчымыя спосабы параўнання двух слупкоў у Excel, што дазволіць вам знайсці і выдаліць дублікаты запісаў.

• Параўнайце 2 слупкі і знайдзіце дублікаты з дапамогай формул
  
  • Варыянт А: абодва слупкі знаходзяцца на адным аркушы
  • Варыянт Б: слупкі знаходзяцца на розных аркушах або ў розных сшытках
• Апрацоўка знойдзеных дублікатаў
  
  • Паказваць толькі паўтаральныя радкі ў слупку A
  • Змяніце колер або вылучыце знойдзеныя дублікаты
  • Выдаліць дублікаты з першага слупка

Параўнайце 2 слупкі ў Excel і знайдзіце паўтаральныя запісы з дапамогай формул

Варыянт А: абодва слупкі знаходзяцца на адным аркушы

1. У першай пустой ячэйцы (у нашым прыкладзе гэта вочка С1) пішам наступную формулу:

   =IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),"Unique","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;$B$1:$B$10000;0));"Unique";"Duplicate")

   

   У нашай формуле A1 гэта першая ячэйка першага слупка, якую мы збіраемся параўнаць. $1 B$ и $10000 B$ гэта адрасы першай і апошняй вочак другога слупка, з якімі будзем праводзіць параўнанне. Звярніце ўвагу на абсалютныя спасылкі - перад літарамі слупкоў і нумарамі радкоў стаіць знак даляра ($). Я выкарыстоўваю абсалютныя спасылкі, каб адрасы вочак заставаліся нязменнымі пры капіраванні формул.

   Калі вы хочаце знайсці дублікаты ў слупку B, змяніце спасылкі так, каб формула выглядала так:

   =IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),"Unique","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B1;$A$1:$A$10000;0));"Unique";"Duplicate")

   Замест гэтага "Толькі"І"Дубляваць» Вы можаце напісаць свае ўласныя этыкеткі, напрыклад, «Not found"І"знойдзены", або пакінуць толькі"Дубляваць' і ўвядзіце прабел замест другога значэння. У апошнім выпадку ячэйкі, для якіх не знойдзена дублікатаў, застануцца пустымі, і, мяркую, такое прадстаўленне дадзеных найбольш зручнае для далейшага аналізу.

2. Зараз давайце скапіруем нашу формулу ва ўсе вочкі слупка C, аж да ніжняга радка, які змяшчае даныя ў слупку A. Для гэтага перавядзіце паказальнік мышы ў правы ніжні кут ячэйкі C1, паказальнік прыме форму чорнага крыжыка, як паказана на малюнку ніжэй:Пстрыкніце і ўтрымлівайце левую кнопку мышы і перацягніце мяжу рамкі ўніз, вылучаючы ўсе вочкі, у якія вы хочаце ўставіць формулу. Калі ўсе неабходныя вочкі будуць вылучаныя, адпусціце кнопку мышы:

   

Савет: У вялікіх табліцах капіяванне формулы будзе хутчэй, калі выкарыстоўваць спалучэнні клавіш. Вылучыце клетку C1 і націсніце Ctrl + C, (каб скапіяваць формулу ў буфер абмену), затым націсніце Ctrl + Shift + End (каб выбраць усе непустыя вочкі ў слупку C) і нарэшце націсніце Ctrl + V (каб уставіць формулу ва ўсе вылучаныя вочкі).

3. Выдатна, цяпер усе паўтаральныя значэнні пазначаюцца як «Дубляваць«:

Варыянт B: два слупкі на розных аркушах (у розных сшытках)

1. У першай ячэйцы першага пустога слупка на аркушы Sheet2 (у нашым выпадку гэта слупок B) увядзіце наступную формулу:

   =IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),"","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;Лист3!$A$1:$A$10000;0));"";"Duplicate")

   Тут Sheet3 — назва аркуша, на якім размешчаны 2-і слупок, і $A$1:$A$10000 - гэта адрасы вочак ад 1-га да апошняга ў гэтым 2-м слупку.

2. Скапіруйце формулу ва ўсе ячэйкі слупка B (тое ж, што варыянт А).
3. Мы атрымліваем гэты вынік:

Апрацоўка знойдзеных дублікатаў

Выдатна, мы знайшлі запісы ў першым слупку, якія таксама прысутнічаюць у другім слупку. Цяпер трэба з імі нешта рабіць. Перагляд усіх дублікатаў запісаў у табліцы ўручную вельмі неэфектыўны і займае занадта шмат часу. Ёсць лепшыя спосабы.

Паказваць толькі паўтаральныя радкі ў слупку A

Калі вашы слупкі не маюць загалоўкаў, вам трэба іх дадаць. Для гэтага пастаўце курсор на лік, які прадстаўляе першы радок, і ён ператворыцца ў чорную стрэлку, як паказана на малюнку ніжэй:

Пстрыкніце правай кнопкай мышы і абярыце з кантэкстнага меню устаўка (Уставіць):

Дайце назвы слупкам, напрыклад, «Імя"І"Дублікат?» Затым адкрыйце ўкладку Дата (Дадзеныя) і націсніце фільтры (Фільтр):

Пасля гэтага націсніце маленькую шэрую стрэлку побач з «Дублікат?« адкрыць меню фільтра; зніміце ўсе пункты ў гэтым спісе, акрамя ДублявацьІ націсніце OK.

Вось і ўсё, цяпер вы бачыце толькі гэтыя элементы калонкі А, якія дублююцца ў слупку В. У нашай вучэбнай табліцы такіх клетак усяго дзве, але, як вы разумееце, на практыцы іх будзе значна больш.

Для паўторнага адлюстравання ўсіх радкоў слупка А, націсніце сімвал фільтра ў слупку В, які цяпер выглядае як варонка з маленькай стрэлкай, і абярыце выбраць усе (Абраць усё). Ці вы можаце зрабіць тое ж самае праз стужку, націснуўшы Дата (Дадзеныя) > Выбраць і адфільтраваць (Сартаваць і фільтраваць) > ясна (Ачысціць), як паказана на скрыншоце ніжэй:

Змяніце колер або вылучыце знойдзеныя дублікаты

Калі нататкі "Дубляваць»недастаткова для вашых мэтаў, і вы хочаце пазначыць дублікаты вочак іншым колерам шрыфта, колерам залівання або іншым спосабам...

У гэтым выпадку адфільтруйце дублікаты, як паказана вышэй, выберыце ўсе адфільтраваныя вочкі і націсніце Ctrl + 1каб адкрыць дыялогавае акно Фарматаваць вочкі (фармат ячэйкі). У якасці прыкладу давайце зменім колер залівання вочак у радках з дублікатамі на ярка-жоўты. Вядома, вы можаце змяніць колер залівання з дапамогай інструмента Fill Ўкладка (Колер залівання). Галоўная (Дадому), але перавага дыялогавага акна Фарматаваць вочкі (Фармат ячэйкі) у тым, што вы можаце наладзіць усе параметры фарматавання адначасова.

Цяпер вы дакладна не прапусціце вочак з дублікатамі:

Выдаленне паўтаральных значэнняў з першага слупка

Адфільтруйце табліцу так, каб паказваліся толькі ячэйкі з паўтаральнымі значэннямі, і вылучыце гэтыя ячэйкі.

Калі 2 слупкі, якія вы параўноўваеце, знаходзяцца на розных лістах, гэта значыць у розных табліцах пстрыкніце правай кнопкай мышы абраны дыяпазон і выберыце Выдаліць радок (Выдаліць радок):

прэс OKкалі Excel просіць вас пацвердзіць, што вы сапраўды хочаце выдаліць увесь радок ліста, а затым ачысціць фільтр. Як бачыце, засталіся толькі радкі з унікальнымі значэннямі:

Калі 2 слупкі знаходзяцца на адным аркушы, блізка адзін да аднаго (сумежныя) або не блізка адзін да аднаго (не суседнія), то працэс выдалення дублікатаў будзе крыху больш складаным. Мы не можам выдаліць увесь радок з паўтаральнымі значэннямі, бо пры гэтым будуць выдалены ячэйкі з другога слупка. Такім чынам, каб пакінуць толькі унікальныя запісы ў слупку А, зрабіць гэта:

1. Адфільтруйце табліцу, каб паказаць толькі паўтаральныя значэнні, і выберыце гэтыя вочкі. Пстрыкніце іх правай кнопкай мышы і абярыце з кантэкстнага меню Ачысціць змесціва (празрысты змест).
2. Ачысціце фільтр.
3. Вылучыце ўсе ячэйкі ў слупку А, пачынаючы з клеткі A1 аж да дна, які змяшчае дадзеныя.
4. націсніце Дата (Дадзеныя) і націсніце Сартаваць ад А да Я (Сартаванне ад А да Я). У якое адкрылася дыялогавым акне абярыце Працягнуць з бягучым выбарам (Сартаваць у межах вызначанага выбару) і націсніце кнопку чорны (Сартаванне):
5. Выдаліце ​​​​слупок з формулай, ён вам больш не спатрэбіцца, з гэтага часу ў вас будуць толькі унікальныя значэння.
6. Вось і ўсё, цяпер калонка А змяшчае толькі унікальныя даныя, якіх няма ў слупку В:

Як бачыце, выдаліць дублікаты з двух слупкоў у Excel пры дапамозе формул не так ужо і складана.