Садржај
Екцел је ефикасан програм за обраду података. А једна од метода анализе информација је поређење две листе. Ако правилно упоредите две листе у Екцел-у, организовање овог процеса биће веома лако. Довољно је само пратити неке од тачака о којима ће данас бити речи. Практична примена ове методе у потпуности зависи од потреба особе или организације у одређеном тренутку. Стога треба размотрити неколико могућих случајева.
Поређење две листе у Екцел-у
Наравно, две листе можете упоредити ручно. Али биће потребно много времена. Екцел има сопствени интелигентни комплет алата који ће вам омогућити да упоредите податке не само брзо, већ и да добијете информације које није тако лако доћи вашим очима. Претпоставимо да имамо две колоне са координатама А и Б. Неке вредности се понављају у њима.
Формулисање проблема
Дакле, морамо да упоредимо ове колоне. Процедура за поређење два документа је следећа:
- Ако су јединствене ћелије сваке од ових листа исте, а укупан број јединствених ћелија је исти, а ћелије су исте, онда се ове листе могу сматрати истим. Редослед којим су вредности на овој листи сложене није толико битан.
- Можемо говорити о делимичној подударности листа ако су саме јединствене вредности исте, али је број понављања различит. Стога такве листе могу садржати различит број елемената.
- Чињеница да се две листе не подударају је назначена различитим скупом јединствених вредности.
Сва ова три услова истовремено су услови нашег проблема.
Решење проблема
Хајде да генеришемо два динамичка опсега да бисмо олакшали поређење листа. Сваки од њих ће одговарати свакој од листа. 
Да бисте упоредили две листе, урадите следеће:
- У посебној колони креирамо листу јединствених вредности које су специфичне за обе листе. За ово користимо формулу: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). Сама формула мора бити написана као формула низа.
- Хајде да одредимо колико пута се свака јединствена вредност појављује у низу података. Ево формула за ово: =ЦОУНТИФ(Листа1,Д5) и =ЦОУНТИ(Листа2,Д5).
- Ако су и број понављања и број јединствених вредности исти на свим листама које су укључене у ове опсеге, онда функција враћа вредност 0. Ово указује да је подударање КСНУМКС%. У овом случају, наслови ових листа ће добити зелену позадину.
- Ако се сав јединствени садржај налази на обе листе, онда се враћа формулама =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) вредност ће бити нула. Ако Е1 не садржи нулу, али је таква вредност садржана у ћелијама Е2 и Ф2, онда ће у овом случају опсези бити препознати као подударни, али само делимично. У овом случају, наслови одговарајућих листа ће постати наранџасти.
- А ако једна од горе описаних формула врати вредност различиту од нуле, листе ће бити потпуно неподударне.
Ово је одговор на питање како анализирати колоне за подударања користећи формуле. Као што видите, уз коришћење функција можете имплементирати скоро сваки задатак који на први поглед није везан за математику.
Пример тестирања
У нашој верзији табеле постоје три типа листа сваке горе описане врсте. Има делимично и потпуно подударање, као и неподударање.
За упоређивање података користимо опсег А5:Б19, у који наизменично убацујемо ове парове листа. Шта ће бити резултат поређења, разумећемо по боји оригиналних листа. Ако су потпуно другачији, онда ће то бити црвена позадина. Ако је део података исти, онда жути. У случају потпуног идентитета, одговарајући наслови ће бити зелене боје. Како направити боју у зависности од тога какав је резултат? Ово захтева условно форматирање.
Проналажење разлика у две листе на два начина
Хајде да опишемо још две методе за проналажење разлика, у зависности од тога да ли су листе синхроне или не.
Опција 1. Синхроне листе
Ово је лака опција. Претпоставимо да имамо такве листе.
Да бисте утврдили колико пута се вредности нису конвергирале, можете користити формулу: =СУМПРОИЗВОД(—(А2:А20<>Б2:Б20)). Ако смо као резултат добили 0, то значи да су две листе исте.
Опција 2: Промешане листе
Ако листе нису идентичне по редоследу објеката које садрже, потребно је да примените функцију као што је условно форматирање и обојење дупликата вредности. Или користите функцију ЦОУНТИФ, помоћу које одређујемо колико пута се елемент са једне листе појављује у другој.
Како упоредити 2 колоне ред по ред
Када упоређујемо две колоне, често морамо да упоредимо информације које се налазе у различитим редовима. Да бисмо то урадили, оператер ће нам помоћи АКО. Хајде да погледамо како то функционише у пракси. Да бисмо то урадили, представљамо неколико илустративних ситуација.
Пример. Како упоредити 2 колоне за подударања и разлике у једном реду
Да бисмо анализирали да ли су вредности које су у истом реду али у различитим колонама исте, пишемо функцију IF. Формула се убацује у сваки ред који се налази у помоћној колони у којој ће бити приказани резултати обраде података. Али уопште није потребно да га прописујете у сваком реду, само га копирајте у преостале ћелије ове колоне или користите маркер за аутодовршавање.
Требало би да запишемо такву формулу да бисмо разумели да ли су вредности у обе колоне исте или не: =ИФ(А2=Б2, „Подударање”, „”). Логика ове функције је врло једноставна: она упоређује вредности у ћелијама А2 и Б2, а ако су исте, приказује вредност „Поклапа се“. Ако су подаци другачији, не враћају никакву вредност. Такође можете проверити ћелије да видите да ли постоји подударање између њих. У овом случају, формула која се користи је: =ИФ(А2<>Б2, “Не подударају”, “”). Принцип је исти, прво се врши провера. Ако се испостави да ћелије испуњавају критеријум, онда се приказује вредност „Не одговара“.
Такође је могуће користити следећу формулу у пољу формуле да прикажете и „Подударање“ ако су вредности исте, и „Не подударање“ ако су различите: =ИФ(А2=Б2; „Подударање”, „Не подударање”). Такође можете користити оператор неједнакости уместо оператора једнакости. Само ће редослед вредности које ће бити приказане у овом случају бити мало другачији: =ИФ(А2<>Б2, “Не подударају”, “Поклапају”). Након употребе прве верзије формуле, резултат ће бити следећи.
Ова варијација формуле не разликује велика и мала слова. Стога, ако се вредности у једној колони разликују од других само по томе што су написане великим словима, онда програм неће приметити ову разлику. Да би поређење било осетљиво на велика и мала слова, потребно је да користите функцију у критеријумима ЕКСАЦТ. Остали аргументи остају непромењени: =ИФ(ТАЧНО(А2,Б2), „Подударање”, „Јединствено”).
Како упоредити више колона за подударања у једном реду
Могуће је анализирати вредности у листама према читавом низу критеријума:
- Пронађите оне редове који имају исте вредности свуда.
- Нађите оне редове у којима постоје подударања у само две листе.
Погледајмо неколико примера како поступити у сваком од ових случајева.
Пример. Како пронаћи подударања у једном реду у више колона табеле
Претпоставимо да имамо низ колона које садрже информације које су нам потребне. Суочени смо са задатком да одредимо оне редове у којима су вредности исте. Да бисте то урадили, потребно је да користите следећу формулу: =ИФ(АНД(А2=Б2,А2=Ц2), “подударање”, “ “).
Ако у табели има превише колона, само треба да је користите заједно са функцијом IF оператор ЦОУНТИФ: =ИФ(ЦОУНТИФ($А2:$Ц2,$А2)=3;”подударање”;” “). Број који се користи у овој формули означава број колона које треба проверити. Ако се разликује, онда треба да напишете онолико колико је тачно за вашу ситуацију.
Пример. Како пронаћи подударања у једном реду у било које 2 колоне табеле
Рецимо да треба да проверимо да ли се вредности у једном реду поклапају у две колоне од оних у табели. Да бисте то урадили, морате да користите функцију као услов OR, где наизменично уписују једнакост сваке колоне другој. Ево примера.
Користимо ову формулу: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Може доћи до ситуације када у табели има пуно колона. У овом случају, формула ће бити огромна и може потрајати доста времена за одабир свих потребних комбинација. Да бисте решили овај проблем, потребно је да користите функцију ЦОУНТИФ: =ИФ(ЦОУНТИФ(Б2:Д2,А2)+ЦОУНТИФ(Ц2:Д2,Б2)+(Ц2=Д2)=0; „Јединствени низ“; „Није јединствен низ“)
Видимо да укупно имамо две функције ЦОУНТИФ. Код првог наизменично одређујемо колико колона има сличност са А2, а код другог проверавамо број сличности са вредношћу Б2. Ако, као резултат израчунавања по овој формули, добијемо нулту вредност, то указује да су сви редови у овој колони јединствени, ако их је више, постоје сличности. Дакле, ако као резултат израчунавања по две формуле и сабирања коначних резултата добијемо нулту вредност, онда се враћа текстуална вредност „Јединствени низ“, ако је овај број већи, пише да овај низ није јединствен.
Како упоредити 2 колоне у Екцелу за подударања
Сада узмимо пример. Рецимо да имамо табелу са две колоне. Морате проверити да ли се поклапају. Да бисте то урадили, потребно је да примените формулу, где ће се функција такође користити IF, и оператера ЦОУНТИФ: =ИФ(ЦОУНТИФ($Б:$Б,$А5)=0, „Нема подударања у колони Б“, „Постоје подударања у колони Б“)
Нису потребне додатне радње. Након израчунавања резултата по овој формули, добијамо ако вредност трећег аргумента функције IF утакмице. Ако их нема, онда садржај другог аргумента.
Како упоредити 2 колоне у Екцелу за подударања и истакнути бојом
Да бисте лакше визуелно идентификовали одговарајуће колоне, можете их означити бојом. Да бисте то урадили, потребно је да користите функцију „Условно форматирање“. Да видимо у пракси.
Проналажење и истицање подударања по боји у више колона
Да бисте одредили подударања и истакли их, прво морате да изаберете опсег података у коме ће се провера извршити, а затим да отворите ставку „Условно форматирање“ на картици „Почетна“. Тамо изаберите „Дуплицате Валуес“ као правило за избор ћелије.
Након тога, појавиће се нови дијалог бок у којем у левој искачућој листи налазимо опцију „Понављање“, ау десној листи бирамо боју која ће бити коришћена за избор. Након што кликнемо на дугме „ОК“, биће изабрана позадина свих ћелија са сличностима. Затим само упоредите колоне на око.
Проналажење и истицање одговарајућих линија
Техника провере да ли се низови подударају је мало другачија. Прво, треба да направимо додатну колону и тамо ћемо користити комбиноване вредности помоћу & оператора. Да бисте то урадили, потребно је да напишете формулу у облику: =А2&Б2&Ц2&Д2.
Бирамо колону која је креирана и садржи комбиноване вредности. Затим вршимо исти редослед радњи који је горе описан за колоне. Дуплициране линије ће бити истакнуте бојом коју одредите.
Видимо да нема ништа тешко у тражењу понављања. Екцел садржи све потребне алате за ово. Важно је само вежбати пре него што све ово знање примените у пракси.