Методе за упоређивање табела у програму Мицрософт Екцел

Често често, корисници Екцела имају задатак да упоређују две табеле или листе да би идентификовали разлике или недостају елементи у њима. Сваки корисник се бави овим задатком на свој начин, али чешће се троши доста времена на решавање овог проблема, јер нису сви приступи овом проблему рационални. Истовремено, постоји неколико валидираних алгоритама за акцију које ће вам омогућити да упоређујете листе или табеле у релативно кратком времену уз минималан напор. Хајде да погледамо ове опције.

Прочитајте такође: Поређење два документа у МС Ворду

Методе поређења

Постоје прилично неколико начина за упоређивање табела са подручјима у Екцелу, али се могу поделити у три велике групе:

На основу ове класификације се, пре свега, одређују методе поређења, а одређене су и специфичне акције и алгоритми за обављање задатка. На пример, у поређењу са различитим књигама, истовремено морате отворити две Екцел датотеке.

Поред тога, треба рећи да поређење поља таблице има смисла само када имају сличну структуру.

Метод 1: Једноставна формула

Најједноставнији начин упоређивања података у две табеле је коришћење једноставне формуле једнакости. Ако се подаци подударају, онда даје ТРУЕ индикатор, а ако не, онда - ФАЛСЕ. Можете упоређивати и нумеричке податке и текст. Недостатак овог метода је то што се може користити само ако су подаци у табели наручени или сортирани идентично, синхронизовани и имају једнаки број линија. Да видимо како да користимо ову методу у пракси на примеру две табеле стављене на један лист.

Дакле, имамо две једноставне табеле са листама запослених у предузећу и њиховим платама. Неопходно је упоредити спискове запослених и идентификовати недоследности између ступаца у којима се налазе имена.

1. За ово нам је потребна додатна колумна на листу. Овде уносимо знак "=" . Затим кликните на име које желите упоредити на првој листи. Поново ставите симбол "=" са тастатуре. Затим кликните на прву ћелију колоне коју упоређујемо у другој табели. Добијен је израз следећег типа:

   =A2=D2

   Иако, наравно, у сваком конкретном случају, координате ће бити другачије, али суштина ће остати иста.

2. Кликом на тастер Ентер унесите резултате поређења. Као што видите, приликом упоређивања првих ћелија на обе листе, програм је показао индикатор "ТРУЕ" , што значи да се подаци подударају.
3. Сада морамо извршити сличну операцију са преосталим ћелијама обе табеле у колонама које упоређујемо. Али можете само копирати формулу која ће уштедети много времена. Посебно је овај фактор важан када поредимо листе са великим бројем линија.

   Најлакши начин за постизање копирања је коришћење ручке за пуњење. Померамо курсор у доњи десни угао ћелије, где имамо "ТРУЕ" индикатор. У овом случају мора се претворити у црни крст. Ово је маркер за пуњење. Кликните на леви тастер миша и превуците курсор низ бројева линија у упоредним таблицама.

4. Као што можете видети, сада у додатној колони приказани су сви резултати упоређивања података у две колоне таблица. У нашем случају, подаци нису одговарали само једној линији. Када их упоређујете, формула је резултирала резултатом "ЛИЕ" . За све друге линије, као што видимо, формула за упоређивање издата је индикатор "ТРУЕ" .
5. Поред тога, могуће је израчунати број неусклађености користећи посебну формулу. Да бисте то урадили, одаберите елемент листа на којем ће се излазити. Затим кликните на икону "Инсерт фунцтион" .
6. У прозору Визард оф Фунцтионс , под групом "Матхематицал" оператора изаберите име СУМПРОДУЦТ . Кликом на дугме "ОК" .
7. Активиран је прозор аргумента СУМПРОДУЦТ функције, чији је главни задатак израчунавање суме производа изабраног опсега. Али ова функција може се користити у наше потребе. Синтакса је прилично једноставна:

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

   Укупно, до 255 низова се могу користити као аргументи. Али у нашем случају ћемо користити само два поља, поред тога, као један аргумент.

   Поставили смо курсор у поље "Арраи1" и на листу изаберите упоредјени опсег података у првој области. После тога ставите знак "није једнак" ( <> ) у пољу и изаберите упоредјени распон друге области. Затим, довршите резултујући израз са заградама, пре него што ставимо два "-" знака. У нашем случају израз је био:

   --(A2:A7<>D2:D7)

   Кликом на дугме "ОК" .

8. Оператор израчунава и приказује резултат. Као што видите, у нашем случају резултат је једнак броју "1" , односно то значи да је у упоредним листама пронађена једна неусклађеност. Ако су листе потпуно идентичне, резултат би био једнак броју "0" .

На исти начин, можете упоређивати податке у таблицама које се налазе на различитим листовима. Али у овом случају пожељно је да се линије у њима нумерише. У супротном, поступак упоређивања је готово управо исти као што је горе описано, осим чињенице да када унесете формулу, морате прелазити између листова. У нашем случају израз ће изгледати овако:

=B2=Лист2!B2

То је, као што можете видети, пре координата података, који се налазе на другим листовима, различитим од места на којем се приказује резултат поређења, наведени су број листа и узвичник.

Метод 2: Избор група ћелија

Можете извршити упоређивање помоћу алата за избор ћелија групе. Са њим можете упоређивати само синхронизоване и наручене листе. Поред тога, у овом случају, листе треба поставити један поред другог на један лист.

1. Изабрали смо упоређене низове. Идите на картицу "Почетна" . Затим кликните на икону "Пронађи и одаберите " , која се налази на траци у оквиру "Измени" . Отвара се листа у којој изаберете ставку "Избор групе ћелија ..." .

   Поред тога, на други начин можете приступити и прозору за избор групе ћелија. Ова опција ће бити нарочито корисна за оне кориснике који имају верзију програма раније од Екцела 2007, јер ови методи не подржавају помоћу дугмета "Пронађи и означите" . Изаберите низове које желимо упоредити и притисните тастер Ф5 .

2. Активиран је мали пролазни прозор. Кликом на дугме "Изабери ..." у доњем левом углу.
3. Након тога, без обзира на две горе наведене опције које сте изабрали, почиње прозор за избор група ћелија. Поставите прекидач у положај "Избор по редовима" . Кликните на дугме "ОК" .
4. Као што можете видети, након тога неусаглашене вриједности линија ће бити истакнуте различитим нијансом. Поред тога, како се може процијенити из садржаја линије формуле, програм ће учинити једну од ћелија активних у назначеним неусклађеним редовима активним.

Метод 3: условно обликовање

Можете упоређивати примјеном методе условног обликовања. Као иу претходном поступку, поређене површине морају бити на истом Екцеловом листу и синхронизоване једни са другима.

1. Пре свега, бирати ћемо табелу коју ћемо сматрати главном, и у којој ће тражити разлике. Да урадимо друго у другом столу. Због тога одаберемо листу запослених који су у њој. Након преласка на картицу "Почетна" , кликнемо на дугме "Условно обликовање" , који има локацију на траци у "Стилес" блок. Са падајуће листе, идите на "Управљање правилима".
2. Прозор управитеља правила је активиран. Кликните на дугме "Креирај правило" у њему.
3. У прозору који се отвара, изаберемо ставку "Користите формулу" . У пољу "Формат Целлс" запишемо формулу која садржи адресе првих ћелија распона упоређених колона, одвојених знаком "није једнако" ( <> ). Само пре овог израза овог пута ће бити знак "=" . Поред тога, за све координате колона у овој формули, морате примијенити апсолутно адресирање. Да бисте то урадили, изаберите формулу помоћу курсора и притисните Ф4 три пута. Као што видите, око свих адреса колона долазило је знак за долар, што значи да се линкови претворе у апсолутне. За наш специфичан случај, формула има следећи облик:

   =$A2<>$D2

   Пишемо овај израз у горњем пољу. Након тога, кликните на дугме "Формат ..." .

4. Прозор "Формат Целлс" је активиран. Идите на картицу "Попунити" . Овдје у листи боја зауставимо избор боја, који желимо да обојимо оне елементе у којима се подаци не поклапају. Кликните на дугме "ОК" .
5. Враћајући се у прозор за прављење правила за форматирање, кликните на дугме "ОК" .
6. Након аутоматског преласка у прозор "Правила менаџера", кликните на дугме "ОК" и у њему.
7. Сада у другој табели, елементи који имају податке који се не подударају са одговарајућим вредностима прве таблице ће бити означени са изабраном бојом.

Постоји и други начин примјене условног обликовања задатка. Као и претходне верзије, он захтева локацију обе поређене области на једном листу, али за разлику од претходно описаних метода, услови синхронизације или сортирања података неће бити обавезни, што разликује ову опцију од претходно описаних.

1. Израђујемо избор области које треба упоређивати.
2. Прелазимо на табу под називом "Дом" . Кликом на дугме "Условно обликовање" . На активираној листи изаберите ставку "Правила за селекцију ћелија" . У следећем менију изаберемо позицију "Дупликате вредности" .
3. Покреће се прозор за подешавање избора дуплих вредности. Ако сте све урадили исправно, у овом прозору остаје само да кликнете на дугме "ОК" . Иако ако желите, можете одабрати другу боју истицања у одговарајућем пољу овог прозора.
4. Након што направимо одређену акцију, сви понављајући елементи ће бити означени са изабраном бојом. Ти елементи који се не подударају остају нијансирани у првобитној боји (подразумевано је бело). Стога, одмах можете визуелно видети која је разлика између низова.

Ако желите, можете, напротив, да нацртате неусаглашене елементе, а они индикатори који се подударају, оставите исту боју. У овом случају, алгоритам акција је скоро исти, али у прозору за подешавање за одабир дуплираних вредности у првом пољу, уместо параметра "Дуплицате" , изаберите опцију "Уникуе" . Након тога, кликните на дугме "ОК" .

Стога ће бити истакнути они индикатори који се не поклапају.

Лекција: Условно обликовање у Екцелу

Метод 4: Комплексна формула

Такође, можете упоређивати податке користећи комплексну формулу засновану на функцији ЦОУНТИФ . Помоћу овог алата можете израчунати колико се сваки елемент из изабране колоне из друге табеле понавља у првом.

Оператор ДРЖАВА се односи на статистичку групу функција. Њен задатак је да броји број ћелија чије вриједности задовољавају одређено стање. Синтакса овог оператора је:

=СЧЁТЕСЛИ(диапазон;критерий)

Аргумент "Ранге" је адреса матрице у којој се израчунавају одговарајуће вредности.

Аргумент критерија поставља услов усклађивања. У нашем случају, то ће представљати координате специфичних ћелија прве површине табле.

1. Изаберите први елемент додатне колоне у којој ће се рачунати број утакмица. Затим кликните на икону "Инсерт фунцтион" .
2. Стартује функционални чаробњак . Идемо у категорију "Статистички" . На листи се налази назив "САВЕТ" . Након што га изаберете, кликните на дугме "ОК" .
3. Покренут је прозор аргумената оператора САВЕТОВАЊА . Као што видите, имена поља у овом прозору одговарају називима аргумената.

   Поставите курсор у поље "Ранге" . Након тога, држећи леви тастер миша, изаберите све вредности колоне са именима друге табеле. Као што видите, координате одмах спадају у наведено поље. Али за наше потребе, ова адреса треба да буде апсолутна. Да бисте то урадили, изаберите координате у пољу и притисните Ф4 .

   Као што видите, веза је узела апсолутну форму, коју карактерише присуство доларских знакова.

   Затим идите у поље "Цритерион" , поставите курсор тамо. Кликнемо на први елемент са именима у првом опсегу таблица. У овом случају остављамо референтни релатив. Након што се приказује у пољу, можете кликнути на дугме "ОК" .

4. Резултат се приказује у елементу листа. Једнака је броју "1" . То значи да се на списку имена друге табеле појављује презиме "Гринев ВП" , који је први на листи првог поља таблице, једном.
5. Сада морамо направити сличан израз за све остале елементе прве табеле. Да бисте то урадили, извршите копирање помоћу маркера за пуњење, као што смо већ урадили. Поставите курсор у доњи десни део елемента листа, који садржи функцију ЦОУНТИФ , и након што га претворите у маркер пуњења, притискамо леви тастер миша и повуцемо курсор надоле.
6. Као што видите, програм је израчунао коинциденције упоређивањем ћелије прве таблице са подацима који се налазе у другом распону таблица. У четири случаја, резултат је био "1" , ау два случаја, "0" . То значи да програм у другој табели није могао пронаћи двије вриједности које су доступне у првом низу таблица.

Наравно, овај израз како би се упоређивали табеларни индикатори, можете примијенити у постојећем облику, али постоји могућност да је побољшате.

Ми ћемо направити вредности које су доступне у другој табели, али не у првој табели, бити приказане на посебној листи.

1. Прије свега, ми ћемо прерадити нашу формулу за ЦОУНТРИ , помало , чинимо то једним од аргумената ИФ оператора. Да бисте то урадили, изаберите прву ћелију у којој се налази оператор ЦОУНТРИ . У линији формула пре њега додамо израз "ИФ" без цитата и отворите конзолу. Даље, како би нам олакшали рад, селектовали смо вредност "ИФ" у линији формула и кликните на икону "Инсерт фунцтион" .
2. Отвара се прозор аргумента ИФ функције. Као што видите, прво поље прозора је већ испуњено вриједношћу оператора САВЕТОВАЊА. Али морамо да завршимо нешто друго у овој области. Поставили смо курсор и додали "= 0" без цитата на већ постојећи израз.

   Након тога идите на поље "Вредност ако је истина . " Овдје ћемо користити још једну угнежену функцију - СТРИНГ . Уносимо реч "ЛИНЕ" без цитата, затим отворите заграде и одредите координате прве ћелије са презименом у другој табели, а затим затворите заграде. Конкретно, у нашем случају у пољу "Вредност ако је истинита" добијен је следећи израз:

   СТРОКА(D2)

   Сада ће ЛИНЕ израз извештавати о функцији ИФ број линије у којем се налази одређено презиме, ау случају да је испуњено стање наведено у првом пољу, функција ИФ ће извести овај број у ћелију. Кликните на дугме "ОК" .

3. Као што видите, први резултат је приказан као "ФАЛСЕ" . То значи да вредност не задовољава услове ИФ оператера. То значи да је прво презиме присутно у оба листа.
4. Коришћењем маркера пуњења копирамо израз оператора ИФ на читаву колону на уобичајени начин. Као што видите, за две ствари које су присутне у другој табели, али не у првом, формула даје бројеве линија.
5. Користимо се од стола десно на десно и попуњавамо колону бројевима редом, почев од 1 . Количество номеров должно совпадать с количеством строк во второй сравниваемой таблице. Чтобы ускорить процедуру нумерации, можно также воспользоваться маркером заполнения.
6. После этого выделяем первую ячейку справа от колонки с номерами и щелкаем по значку «Вставить функцию» .
7. Отвара се Чаробњак за функције . Переходим в категорию «Статистические» и производим выбор наименования «НАИМЕНЬШИЙ» . Кликом на дугме "ОК" .
8. Функция НАИМЕНЬШИЙ , окно аргументов которой было раскрыто, предназначена для вывода указанного по счету наименьшего значения.

   В поле «Массив» следует указать координаты диапазона дополнительного столбца «Количество совпадений» , который мы ранее преобразовали с помощью функции ЕСЛИ . Делаем все ссылки абсолютными.

   В поле «K» указывается, какое по счету наименьшее значение нужно вывести. Тут указываем координаты первой ячейки столбца с нумерацией, который мы недавно добавили. Адрес оставляем относительным. Кликом на дугме "ОК" .

9. Оператор выводит результат – число 3 . Именно оно наименьшее из нумерации несовпадающих строк табличных массивов. С помощью маркера заполнения копируем формулу до самого низа.
10. Теперь, зная номера строк несовпадающих элементов, мы можем вставить в ячейку и их значения с помощью функции ИНДЕКС . Выделяем первый элемент листа, содержащий формулу НАИМЕНЬШИЙ . После этого переходим в строку формул и перед наименованием «НАИМЕНЬШИЙ» дописываем название «ИНДЕКС» без кавычек, тут же открываем скобку и ставим точку с запятой ( ; ). Затем выделяем в строке формул наименование «ИНДЕКС» и кликаем по пиктограмме «Вставить функцию» .
11. После этого открывается небольшое окошко, в котором нужно определить, ссылочный вид должна иметь функция ИНДЕКС или предназначенный для работы с массивами. Нам нужен второй вариант. Он установлен по умолчанию, так что в данном окошке просто щелкаем по кнопке «OK» .
12. Запускается окно аргументов функции ИНДЕКС . Данный оператор предназначен для вывода значения, которое расположено в определенном массиве в указанной строке.

    Как видим, поле «Номер строки» уже заполнено значениями функции НАИМЕНЬШИЙ . От уже существующего там значения следует отнять разность между нумерацией листа Excel и внутренней нумерацией табличной области. Как видим, над табличными значениями у нас только шапка. Это значит, что разница составляет одну строку. Поэтому дописываем в поле «Номер строки» значение «-1» без кавычек.

    В поле «Массив» указываем адрес диапазона значений второй таблицы. При этом все координаты делаем абсолютными, то есть, ставим перед ними знак доллара уже ранее описанным нами способом.

    Кликните на дугме "ОК" .

13. После вывода результат на экран протягиваем функцию с помощью маркера заполнения до конца столбца вниз. Как видим, обе фамилии, которые присутствуют во второй таблице, но отсутствуют в первой, выведены в отдельный диапазон.

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

При сравнении диапазонов в разных книгах можно использовать перечисленные выше способы, исключая те варианты, где требуется размещение обоих табличных областей на одном листе. Главное условие для проведения процедуры сравнения в этом случае – это открытие окон обоих файлов одновременно. Для версий Excel 2013 и позже, а также для версий до Excel 2007 с выполнением этого условия нет никаких проблем. Но в Excel 2007 и Excel 2010 для того, чтобы открыть оба окна одновременно, требуется провести дополнительные манипуляции. Как это сделать рассказывается в отдельном уроке.

Лекција: Как открыть Эксель в разных окнах

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

• ← СКЛ упити у Мицрософт Екцелу
• Рад са повезаним таблицама у Мицрософт Екцелу →