Метады параўнання табліц у Microsoft Excel

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

Чытайце таксама: Параўнанне двух дакументаў у MS Word

спосабы параўнання

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

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

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

Спосаб 1: простая формула

Самы просты спосаб параўнання дадзеных у двух табліцах - гэта выкарыстанне просты формулы роўнасці. Калі дадзеныя супадаюць, то яна выдае паказчык ПРАЎДА, а калі няма, то - ХЛУСНЯ. Параўноўваць можна, як лікавыя дадзеныя, так і тэкставыя. Недахоп дадзенага спосабу складаецца ў тым, што ім можна карыстацца толькі ў тым выпадку, калі дадзеныя ў табліцы ўпарадкаваны або адсартаваныя аднолькава, сінхранізаваныя і маюць роўнае колькасць радкоў. Давайце паглядзім, як выкарыстоўваць дадзены спосаб на практыцы на прыкладзе двух табліц, размешчаных на адным аркушы.

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

1. Для гэтага нам спатрэбіцца дадатковы слупок на лісце. Ўпісваем туды знак "=". Затым клікаем па першым найменні, якое трэба параўнаць ў першым спісе. Зноў ставім сімвал "=" з клавіятуры. Далей клікаем па першай вочку калонкі, якую мы параўноўваем, ў другой табліцы. Атрымалася выраз наступнага тыпу:

   = A2 = D2

   

   Хоць, вядома, у кожным канкрэтным выпадку каардынаты будуць адрознівацца, але сутнасць застанецца аднолькавай.

2. Пстрыкаем па клавішы Enter, Каб атрымаць вынікі параўнання. Як бачым, пры параўнанні першых вочак абодвух спісаў праграма паказала паказчык "ПРАЎДА", Што азначае супадзенне дадзеных.



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

   Працэдуру капіявання лягчэй за ўсё выканаць пры дапамозе маркера запаўнення. Наводзім курсор на правы ніжні кут ячэйкі, дзе мы атрымалі паказчык "ПРАЎДА". Пры гэтым ён павінен пераўтварыцца ў чорны крыжык. Гэта і ёсць маркер запаўнення. Ціснем левую кнопку мышы і цягнем курсор ўніз на колькасць радкоў у параўноўваных таблічных масівах.



4. Як бачым, цяпер у дадатковым слупку адлюстраваліся ўсе вынікі параўнання дадзеных у двух калонках таблічных масіваў. У нашым выпадку не супалі дадзеныя толькі ў адным радку. Пры іх параўнанні формула выдала вынік "ХЛУСНЯ". Па ўсіх астатніх радках, як бачым, формула параўнання выдала паказчык "ПРАЎДА".



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



6. У акне майстры функцый у групе аператараў "Матэматычныя" вылучаем найменне СУММПРОИЗВ. Пстрыкаем па кнопцы "OK".



7. Актывуецца акно аргументаў функцыі СУММПРОИЗВ, Галоўнай задачай якой з'яўляецца вылічэнне сумы твораў вылучанага дыяпазону. Але дадзеную функцыю можна выкарыстоўваць і для нашых мэтаў. Сінтаксіс у яе даволі просты:

   = СУММПРОИЗВ (массив1; массив2; ...)

   За ўсё ў якасці аргументаў можна выкарыстоўваць адрасы да 255 масіваў. Але ў нашым выпадку мы будзем выкарыстоўваць усяго два масіва, да таго ж, як адзін аргумент.

   Ставім курсор у полі "Массив1" і вылучаем на лісце параўноўваў дыяпазон дадзеных у першай вобласці. Пасля гэтага ў полі ставім знак "Ня роўнае" () І вылучаем параўноўваў дыяпазон другой вобласці. Далей обворачиваем атрыманае выраз дужкамі, перад якімі ставім два знака "-". У нашым выпадку атрымалася такі выраз:

   - (A2: A7D2: D7)

   Пстрыкаем па кнопцы "OK".



8. Аператар вырабляе разлік і выводзіць вынік. Як бачым, у нашым выпадку вынік роўны ліку "1", Гэта значыць, гэта азначае, што ў параўноўваных спісах было знойдзена адно несупадзенне. Калі б спісы былі цалкам ідэнтычнымі, то вынік б быў роўны ліку "0".

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

= B2 = Лист2! B2

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

Спосаб 2: вылучэнне груп вочак

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

1. Вылучаем Параўноўваныя масівы. Пераходзім ва ўкладку "Галоўная". Далей пстрыкаем па значку "Знайсці і вылучыць", Які размяшчаецца на стужцы ў блоку інструментаў "Рэдагаванне". Адкрываецца спіс, у якім варта выбраць пазіцыю "Вылучэнне групы вочак ...".

   

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

2. Актывуецца невялікае акенца пераходу. Пстрыкаем па кнопцы "Вылучыць ..." у яго ніжнім левым куце.



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



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

Спосаб 3: ўмоўнае фарматаванне

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

1. Перш за ўсё, выбіраем, якую таблічную вобласць будзем лічыць асноўнай, а ў якой шукаць адрозненні. Апошняе давайце будзем рабіць ў другой табліцы. Таму вылучаем спіс работнікаў, які знаходзіцца ў ёй. Перамясціўшыся на ўкладку "Галоўная", Пстрыкаем па кнопцы "Умоўнае фарматаванне", Якая мае месцазнаходжанне на стужцы ў блоку "Стылі". З выпадальнага спісу пераходзім па пункце "Упраўленне правіламі".



2. Актывуецца акенца дыспетчара правілаў. Ціснем у ім на кнопку "Стварыць правіла".



3. У запусцім акне вырабляем выбар пазіцыі "Выкарыстоўваць формулу". У полі "Фарматаваць вочкі" запісваем формулу, якая змяшчае адрасы першых вочак дыяпазонаў параўноўваных слупкоў, падзеленыя знакам "ня роўнае" (). Толькі перад дадзеных выразам на гэты раз будзе стаяць знак "=". Акрамя таго, да ўсіх да каардынатах слупкоў у дадзенай формуле трэба ўжыць абсалютную адрасаванне. Для гэтага вылучаем формулу курсорам і тройчы ціснем на клавішу F4. Як бачым, каля ўсіх адрасоў слупкоў з'явіўся знак даляра, што і азначае ператварэнне спасылак у абсалютныя. Для нашага канкрэтнага выпадку формула прыме наступны выгляд:

   = $ A2 $ D2

   Дадзены выраз мы і запісваем у вышэйпаказанае полі. Пасля гэтага пстрыкаю па кнопцы "Фармат ...".



4. актывуецца акно "Фармат вочак". Ідзем ва ўкладку "Заліванне". Тут у пераліку кветак спыняем выбар на колеры, якім хочам афарбоўваць тыя элементы, дзе дадзеныя не будуць супадаць. Ціснем на кнопку "OK".



5. Вярнуўшыся ў акно стварэння правілы фарматавання, ціснем на кнопку "OK".



6. Пасля аўтаматычнага перамяшчэння ў акно "Дыспетчара правілаў" пстрыкаем па кнопцы "OK" і ў ім.



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

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

1. Вырабляем вылучэнне абласцей, якія трэба параўнаць.



2. Выконваем пераход ва ўкладку пад назвай "Галоўная". Робім пстрычка па кнопцы "Умоўнае фарматаванне". У Актываваць спісе выбіраем пазіцыю "Правілы выдзялення вочак". У наступным меню робім выбар пазіцыі "Паўтаральныя значэння".



3. Запускаецца акно налады выдзялення паўтаральных значэнняў. Калі вы ўсё зрабілі правільна, то ў дадзеным акне застаецца толькі націснуць на кнопку "OK". Хоць пры жаданні ў адпаведным полі дадзенага акенца можна выбраць іншы колер вылучэння.



4. Пасля таго, як мы вырабім азначанае дзеянне, усе паўтаральныя элементы будуць выдзелены абраным колерам. Тыя элементы, якія не супадаюць, застануцца афарбаванымі ў свой першапачатковы колер (па змаўчанні белы). Такім чынам, можна адразу візуальна ўбачыць, у чым адрозненне паміж масівамі.

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

Такім чынам, будуць выдзелены менавіта тыя паказчыкі, якія не супадаюць.

Урок: Умоўнае фарматаванне ў Эксэля

Спосаб 4: комплексная формула

Таксама параўнаць дадзеныя можна пры дапамозе складанай формулы, асновай якой з'яўляецца функцыя СЧЁТЕСЛИ. З дапамогай дадзенага інструмента можна зрабіць падлік таго, колькі кожны элемент з абранага слупка другой табліцы паўтараецца ў першай.

аператар СЧЁТЕСЛИ ставіцца да статыстычнай групе функцый. Яго задачай з'яўляецца падлік колькасці вочак, значэння у якіх задавальняюць зададзеным умове. Сінтаксіс дадзенага аператара мае такі выгляд:

= СЧЁТЕСЛИ (дыяпазон; крытэр)

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

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

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



2. адбываецца запуск майстры функцый. Пераходзім у катэгорыю "Статыстычныя". Знаходзім у пераліку найменне "СЧЁТЕСЛИ". Пасля яго вылучэння пстрыкаем па кнопцы "OK".



3. Адбываецца запуск вокны аргументаў аператара СЧЁТЕСЛИ. Як бачым, назвы палёў у гэтым акне адпавядаюць назвах аргументаў.

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

   Як бачым, спасылка прыняла абсалютную форму, што характарызуецца наяўнасцю знакаў даляра.

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



4. У элемент ліста выводзіцца вынік. Ён роўны ліку "1". Гэта азначае, што ў пераліку імёнаў другой табліцы прозвішча "Грынёў В. П.", Якая з'яўляецца першай у спісе першага таблічнага масіва, сустракаецца адзін раз.



5. Цяпер нам трэба стварыць падобнае выраз і для ўсіх іншых элементаў першай табліцы. Для гэтага выканаем капіраванне, скарыстаўшыся маркерам запаўнення, як гэта мы ўжо рабілі раней. Ставім курсор у ніжнюю правую частку элемента ліста, які змяшчае функцыю СЧЁТЕСЛИ, І пасля пераўтварэння яго ў маркер запаўнення зацісканы левую кнопку мышы і цягнем курсор ўніз.



6. Як бачым, праграма зрабіла вылічэнне супадзенняў, параўнаўшы кожнае вочка першай табліцы з дадзенымі, якія размешчаны ў другім таблічным дыяпазоне. У чатырох выпадках вынік выйшаў "1", А ў двух выпадках - "0". Гэта значыць, праграма не змагла адшукаць ў другой табліцы два значэння, якія маюцца ў першым таблічным масіве.

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

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

1. Перш за ўсё, трохі перапрацуем нашу формулу СЧЁТЕСЛИ, А менавіта зробім яе адным з аргументаў аператара КАЛІ. Для гэтага вылучаем першую вочка, у якой размешчаны аператар СЧЁТЕСЛИ. У радку формул перад ёй дапісваем выраз "КАЛІ" без двукоссяў і адкрываем дужку. Далей, каб нам лягчэй было працаваць, вылучаем у радку формул значэнне "КАЛІ" і ціснем па абразку "Уставіць функцыю".



2. Адкрываецца акно аргументаў функцыі КАЛІ. Як бачым, першае поле акна ўжо запоўнена значэннем аператара СЧЁТЕСЛИ. Але нам трэба дапісаць сёе-тое яшчэ ў гэтае поле. Усталёўваем туды курсор і да ўжо існуючага выразе дапісваем "=0" без двукоссяў.

   Пасля гэтага пераходзім да поля "Значэнне калі ісціна". Тут мы скарыстаемся яшчэ адной укладзенай функцыяй - СТРОКА. ўпісваем слова "СТРОКА" без двукоссяў, далей адкрываем дужкі і паказваем каардынаты першай ячэйкі з прозвішчам ў другой табліцы, пасля чаго закрываем дужкі. Канкрэтна ў нашым выпадку ў полі "Значэнне калі ісціна" атрымалася наступнае выраз:

   СТРОКА (D2)

   цяпер аператар СТРОКА будзе паведамляць функцыі КАЛІ нумар радка, у якой размешчана канкрэтная прозвішча, і ў выпадку, калі ўмова, зададзенае ў першым полі, будзе выконвацца, функцыя КАЛІ будзе выводзіць гэты нумар у вочка. Ціснем на кнопку "OK".



3. Як бачым, першы вынік адлюстроўваецца, як "ХЛУСНЯ". Гэта азначае, што значэнне не задавальняе умовам аператара КАЛІ. Гэта значыць, першая прозвішча прысутнічае ў абодвух спісах.



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



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



6. Пасля гэтага вылучаем першую вочка справа ад калонкі з нумарамі і пстрыкаем па значку "Уставіць функцыю".



7. адкрываецца майстар функцый. Пераходзім у катэгорыю "Статыстычныя" і вырабляем выбар назвы "Найменшы". Пстрыкаем па кнопцы "OK".



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

   У полі "Масіў" варта паказаць каардынаты дыяпазону дадатковага слупка "Колькасць супадзенняў", Які мы раней пераўтварылі з дапамогай функцыі КАЛІ. Робім усё спасылкі абсалютнымі.

   У полі "K" паказваецца, якое па ліку найменшае значэнне трэба вывесці. Тут паказваем каардынаты першай ячэйкі слупка з нумарацыяй, які мы нядаўна дадалі. Адрас пакідаем адносным. Пстрыкаем па кнопцы "OK".



9. Аператар выводзіць вынік - колькасць 3. Менавіта яно найменшае з нумарацыі несупадаючымі радкоў таблічных масіваў. З дапамогай маркера запаўнення капіюем формулу да самага нізу.



10. Цяпер, ведаючы нумары радкоў несупадаючымі элементаў, мы можам ўставіць у вочка і іх значэння з дапамогай функцыі індэкс. Вылучаем першы элемент ліста, які змяшчае формулу Найменшыя. Пасля гэтага пераходзім у радок формул і перад найменнем "Найменшы" дапісваем назву "Індэкс" без двукоссяў, тут жа адкрываем дужку і ставім кропку з коскай (;). Затым вылучаем у радку формул найменне "Індэкс" і клікаем па піктаграме "Уставіць функцыю".



11. Пасля гэтага адкрываецца невялікае акенца, у якім трэба вызначыць, спасылачныя выгляд павінна мець функцыя індэкс або прызначаны для працы з масівамі. Нам патрэбны другі варыянт. Ён усталяваны па змаўчанні, так што ў дадзеным акенцы проста пстрыкаем па кнопцы "OK".



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

    Як бачым, поле "Нумар радка" ўжо запоўнена значэннямі функцыі Найменшыя. Ад ужо існуючага там значэння варта адняць рознасць паміж нумарацыяй ліста Excel і ўнутранай нумарацыяй таблічнай вобласці. Як бачым, над таблічных значэннямі ў нас толькі шапка. Гэта значыць, што розніца складае адзін радок. Таму дапісваем ў полі "Нумар радка" значэнне "-1" без двукоссяў.

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

    Ціснем на кнопку "OK".



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

Спосаб 5: параўнанне масіваў у розных кнігах

Пры параўнанні дыяпазонаў у розных кнігах можна выкарыстоўваць пералічаныя вышэй спосабы, выключаючы тыя варыянты, дзе патрабуецца размяшчэнне абодвух таблічных абласцей на адным аркушы. Галоўная ўмова для правядзення працэдуры параўнання ў гэтым выпадку - гэта адкрыццё вокнаў абодвух файлаў адначасова. Для версій Excel 2013 і пазней, а таксама для версій да Excel 2007 з выкананнем гэтай умовы няма ніякіх праблем. Але ў Excel 2007 года і Excel 2010 для таго, каб адкрыць абодва вокны адначасова, патрабуецца правесці дадатковыя маніпуляцыі. Як гэта зрабіць распавядаецца ў асобным ўроку.

Ўрок: Як адкрыць Эксэля ў розных вокнах

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