Як параўнаць два спісы ў Excel

Excel - гэта эфектыўная праграма для апрацоўкі дадзеных. І адзін са спосабаў аналізу інфармацыі — параўнанне двух спісаў. Калі вы правільна параўнаеце два спісу ў Excel, арганізаваць гэты працэс будзе вельмі лёгка. Дастаткова толькі прытрымлівацца некаторых момантаў, пра якія сёння пойдзе гаворка. Практычная рэалізацыя гэтага метаду цалкам залежыць ад патрэб чалавека або арганізацыі ў канкрэтны момант. Такім чынам, варта разгледзець некалькі магчымых выпадкаў.

Параўнанне двух спісаў у Excel

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

Пастаноўка задачы

Такім чынам, нам трэба параўнаць гэтыя слупкі. Працэдура параўнання двух дакументаў выглядае наступным чынам:

1. Калі унікальныя ячэйкі кожнага з гэтых спісаў аднолькавыя, і агульная колькасць унікальных вочак аднолькавая, і ячэйкі аднолькавыя, то гэтыя спісы можна лічыць аднолькавымі. Парадак размяшчэння значэнняў у гэтым спісе не мае вялікага значэння.
2. Аб частковым супадзенні спісаў можна казаць, калі самі унікальныя значэння аднолькавыя, але розная колькасць паўтораў. Таму такія спісы могуць утрымліваць розную колькасць элементаў.
3. Той факт, што два спісы не супадаюць, паказвае розны набор унікальных значэнняў.

Усе гэтыя тры ўмовы адначасова з'яўляюцца ўмовамі нашай задачы.

Рашэнне задачы

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

Каб параўнаць два спісы, зрабіце наступнае:

1. У асобным слупку мы ствараем спіс унікальных значэнняў, характэрных для абодвух спісаў. Для гэтага выкарыстоўваем формулу: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). Сама формула павінна быць запісана ў выглядзе формулы масіва.
2. Давайце вызначым, колькі разоў кожнае унікальнае значэнне сустракаецца ў масіве даных. Вось формулы для гэтага: =COUNTI(Спіс1,D5) і =COUNTI(Спіс2,D5).
3. Калі і колькасць паўтораў, і колькасць унікальных значэнняў аднолькавыя ва ўсіх спісах, якія ўваходзяць у гэтыя дыяпазоны, то функцыя вяртае значэнне 0. Гэта азначае, што супадзенне складае XNUMX%. Пры гэтым загалоўкі гэтых спісаў набудуць зялёны фон.
4. Калі ўвесь унікальны кантэнт знаходзіцца ў абодвух спісах, то вяртаецца па формулах =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) значэнне будзе роўна нулю. Калі E1 не ўтрымлівае нуля, але такое значэнне змяшчаецца ў ячэйках E2 і F2, то ў гэтым выпадку дыяпазоны будуць прызнаныя супадаючымі, але толькі часткова. У гэтым выпадку загалоўкі адпаведных спісаў стануць аранжавымі.
5. І калі адна з формул, апісаных вышэй, вяртае ненулявое значэнне, спісы будуць цалкам несупадаючымі.

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

Прыклад тэставання

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

Для параўнання дадзеных мы выкарыстоўваем дыяпазон A5:B19, у які па чарзе ўстаўляем гэтыя пары спісаў. Аб тым, што будзе ў выніку параўнання, мы зразумеем па колеры зыходных спісаў. Калі яны зусім розныя, то гэта будзе чырвоны фон. Калі частка дадзеных супадае, то жоўты. У выпадку поўнай ідэнтычнасці адпаведныя загалоўкі будуць зялёнымі. Як зрабіць колер у залежнасці ад таго, які вынік? Гэта патрабуе ўмоўнага фарматавання.

Пошук адрозненняў у двух спісах двума спосабамі

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

Варыянт 1. Сінхронныя спісы

Гэта лёгкі варыянт. Дапусцім, у нас ёсць такія спісы.

Каб вызначыць, колькі разоў значэння не сышліся, можна скарыстацца формулай: =СУММАПРАДУКТ(—(A2:A20<>B2:B20)). Калі мы атрымалі 0 у выніку, гэта азначае, што два спісы аднолькавыя.

Варыянт 2: ператасаваныя спісы

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

Як параўнаць 2 слупкі радок за радком

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

Прыклад. Як параўнаць 2 слупкі на супадзенні і адрозненні ў адным радку

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

Нам варта запісаць такую ​​формулу, каб зразумець, супадаюць ці не значэння ў абодвух слупках: =КАЛІ(A2=B2, “Супадзенне”, “”). Логіка гэтай функцыі вельмі простая: яна параўноўвае значэння ў ячэйках A2 і B2, і калі яны супадаюць, выводзіць значэнне «Супадае». Калі даныя адрозніваюцца, яны не вяртаюць ніякага значэння. Вы таксама можаце праверыць клеткі, каб убачыць, ці ёсць супадзенне паміж імі. У гэтым выпадку выкарыстоўваецца наступная формула: =КАЛІ(A2<>B2, “Не супадае”, “”). Прынцып той жа, спачатку праводзіцца праверка. Калі выяўляецца, што вочкі адпавядаюць крытэрыю, то выводзіцца значэнне «Не супадае».

Таксама можна выкарыстоўваць наступную формулу ў полі формулы, каб адлюстраваць як «Супадзенне», калі значэнні аднолькавыя, так і «Не супадаць», калі яны розныя: =КАЛІ(A2=B2; “Супадзенне”, “Не супадае”). Вы таксама можаце выкарыстоўваць аператар няроўнасці замест аператара роўнасці. Трохі адрозніваецца толькі парадак значэнняў, якія будуць адлюстроўвацца ў гэтым выпадку: =КАЛІ(A2<>B2, “Не супадае”, “Супадае”). Пасля выкарыстання першага варыянту формулы вынік будзе наступным.

Гэты варыянт формулы неадчувальны да рэгістра. Такім чынам, калі значэння ў адным слупку адрозніваюцца ад іншых толькі тым, што яны напісаны вялікімі літарамі, то праграма гэтай розніцы не заўважыць. Каб зрабіць параўнанне з улікам рэгістра, трэба выкарыстоўваць функцыю ў крытэрах EXACT. Астатнія аргументы пакінутыя без зменаў: =КАЛІ(ДАкладны(A2;B2), “Супадзенне”, “Унікальны”).

Як параўнаць некалькі слупкоў на супадзенні ў адным радку

Прааналізаваць значэння ў спісах можна па цэламу набору крытэраў:

1. Знайдзіце тыя радкі, якія ўсюды маюць аднолькавыя значэнні.
2. Знайдзіце тыя радкі, дзе ёсць супадзенні ўсяго ў двух спісах.

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

Прыклад. Як знайсці супадзенні ў адным радку ў некалькіх слупках табліцы

Дапусцім, у нас ёсць шэраг слупкоў, якія змяшчаюць неабходную нам інфармацыю. Перад намі стаіць задача вызначыць тыя радкі, у якіх значэння супадаюць. Для гэтага трэба скарыстацца наступнай формулай: =КАЛІ(І(A2=B2,A2=C2), “супадзенне”, ” “).

Калі ў табліцы занадта шмат слупкоў, то вам проста трэба выкарыстоўваць яго разам з функцыяй IF аператар COUNTIF: =КАЛІ(COUNTIF($A2:$C2,$A2)=3;”супадзенне”;” “). Лік, які выкарыстоўваецца ў гэтай формуле, паказвае колькасць слупкоў для праверкі. Калі ён адрозніваецца, то вам трэба напісаць столькі, колькі адпавядае вашай сітуацыі.

Прыклад. Як знайсці супадзенні ў адным радку ў любых 2 слупках табліцы

Дапусцім, нам трэба праверыць, ці супадаюць значэнні ў адным радку ў двух слупках са значэннямі ў табліцы. Для гэтага вам трэба выкарыстоўваць функцыю ў якасці ўмовы OR, дзе па чарзе запісаць роўнасць кожнага са слупкоў другому. Вось прыклад.

Мы выкарыстоўваем гэтую формулу: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Можа ўзнікнуць сітуацыя, калі ў табліцы шмат слупкоў. У гэтым выпадку формула будзе велізарнай, і на падбор усіх неабходных камбінацый можа спатрэбіцца шмат часу. Каб вырашыць гэтую праблему, вам трэба выкарыстоўваць функцыю COUNTIF: =КАЛІ(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0; “Унікальны радок”; “Неўнікальны радок”)

Мы бачым, што ўсяго ў нас дзве функцыі COUNTIF. З першым мы па чарзе вызначаем, колькі слупкоў маюць падабенства з A2, а з другім правяраем колькасць падабенстваў са значэннем B2. Калі ў выніку разліку па дадзенай формуле мы атрымліваем нулявое значэнне, гэта сведчыць аб тым, што ўсе радкі ў гэтым слупку ўнікальныя, калі больш - ёсць падабенства. Такім чынам, калі ў выніку разліку па дзвюх формулах і складання канчатковых вынікаў мы атрымліваем нулявое значэнне, то вяртаецца тэкставае значэнне «Унікальная радок», калі гэты лік больш, то пішацца, што гэты радок не з'яўляецца унікальным.

Як параўнаць 2 слупкі ў Excel на супадзенні

Зараз возьмем прыклад. Дапусцім, у нас ёсць табліца з двума слупкамі. Трэба праверыць, ці супадаюць яны. Для гэтага трэба ўжыць формулу, дзе таксама будзе выкарыстоўвацца функцыя IF, і аператар COUNTIF: =IF(COUNTIF($B:$B,$A5)=0, «У слупку B няма супадзенняў», «У слупку B ёсць супадзенні»)

Ніякіх далейшых дзеянняў не патрабуецца. Вылічыўшы вынік па гэтай формуле, мы атрымаем калі значэнне трэцяга аргумента функцыі IF запалкі. Калі іх няма, то змесціва другога аргумента.

Як параўнаць 2 слупкі ў Excel на супадзенні і вылучыць колерам

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

Пошук і вылучэнне супадзенняў па колеры ў некалькіх слупках

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

Пасля гэтага з'явіцца новае дыялогавае акно, у якім у левым ўсплываючым спісе знаходзім опцыю «Паўтараецца», а ў правым спісе выбіраем колер, які будзе выкарыстоўвацца для вылучэння. Пасля націску кнопкі «ОК» будзе абраны фон усіх вочак з падабенствам. Затым проста параўнайце слупкі на вока.

Знаходжанне і выдзяленне адпаведных радкоў

Тэхніка праверкі супадзення радкоў крыху іншая. Спачатку нам трэба стварыць дадатковы слупок, і там мы будзем выкарыстоўваць камбінаваныя значэнні з дапамогай аператара &. Для гэтага трэба запісаць формулу выгляду: =A2&B2&C2&D2.

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

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