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

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

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

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

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

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

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

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

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

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

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

      =A2=D2

      Формула за упоређивање ћелија у Мицрософт Екцелу

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

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

    4. Сада морамо извршити сличну операцију са преосталим ћелијама обе табеле у колонама које упоређујемо. Али можете само копирати формулу која ће уштедети много времена. Посебно је овај фактор важан када поредимо листе са великим бројем линија.

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

    5. Филлер у програму Мицрософт Екцел

    6. Као што можете видети, сада у додатној колони приказани су сви резултати упоређивања података у две колоне таблица. У нашем случају, подаци нису одговарали само једној линији. Када их упоређујете, формула је резултирала резултатом "ЛИЕ" . За све друге линије, као што видимо, формула за упоређивање издата је индикатор "ТРУЕ" .
    7. Израчун резултата за целу колону у Мицрософт Екцелу

    8. Поред тога, могуће је израчунати број неусклађености користећи посебну формулу. Да бисте то урадили, одаберите елемент листа на којем ће се излазити. Затим кликните на икону "Инсерт фунцтион" .
    9. Идите у Чаробњак за функције у програму Мицрософт Екцел

    10. У прозору Визард оф Фунцтионс , под групом "Матхематицал" оператора изаберите име СУМПРОДУЦТ . Кликом на дугме "ОК" .
    11. Пребацивање на прозор аргумената функције СУМПРОДУЦТ у програму Мицрософт Екцел

    12. Активиран је прозор аргумента СУМПРОДУЦТ функције, чији је главни задатак израчунавање суме производа изабраног опсега. Али ова функција може се користити у наше потребе. Синтакса је прилично једноставна:

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

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

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

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

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

    13. Прозор аргумената функције СУМПРОДУЦТ у програму Мицрософт Екцел

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

    Резултат израчунавања функције СУМПРОДУЦТ у програму Мицрософт Екцел

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

    =B2=Лист2!B2

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

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

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

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

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

      Идите у прозор селекције групе ћелија у програму Мицрософт Екцел

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

    2. Активиран је мали пролазни прозор. Кликом на дугме "Изабери ..." у доњем левом углу.
    3. Прозор за пролаз у Мицрософт Екцелу

    4. Након тога, без обзира на две горе наведене опције које сте изабрали, почиње прозор за избор група ћелија. Поставите прекидач у положај "Избор по редовима" . Кликните на дугме "ОК" .
    5. Прозор одабира група ћелија у програму Мицрософт Екцел

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

    Подаци који нису усклађени у Мицрософт Екцелу

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

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

    1. Пре свега, бирати ћемо табелу коју ћемо сматрати главном, и у којој ће тражити разлике. Да урадимо друго у другом столу. Због тога одаберемо листу запослених који су у њој. Након преласка на картицу "Почетна" , кликнемо на дугме "Условно обликовање" , који има локацију на траци у "Стилес" блок. Са падајуће листе, идите на "Управљање правилима".
    2. Идите у прозор правила условног обликовања у програму Мицрософт Екцел

    3. Прозор управитеља правила је активиран. Кликните на дугме "Креирај правило" у њему.
    4. Менаџер правила за условно обликовање у програму Мицрософт Екцел

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

      =$A2<>$D2

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

    6. Идите у прозор за избор формата у програму Мицрософт Екцел

    7. Прозор "Формат Целлс" је активиран. Идите на картицу "Попунити" . Овдје у листи боја зауставимо избор боја, који желимо да обојимо оне елементе у којима се подаци не поклапају. Кликните на дугме "ОК" .
    8. Изаберите боју испуњења у прозору Формат Целлс у програму Мицрософт Екцел

    9. Враћајући се у прозор за прављење правила за форматирање, кликните на дугме "ОК" .
    10. Прозор за креирање правила за форматирање у програму Мицрософт Екцел

    11. Након аутоматског преласка у прозор "Правила менаџера", кликните на дугме "ОК" и у њему.
    12. Примена правила у управљачу правила у Мицрософт Екцелу

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

    Подаци који се не подударају означавају се условним форматирањем у програму Мицрософт Екцел

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

    1. Израђујемо избор области које треба упоређивати.
    2. Избор упоредјених таблица у програму Мицрософт Екцел

    3. Прелазимо на табу под називом "Дом" . Кликом на дугме "Условно обликовање" . На активираној листи изаберите ставку "Правила за селекцију ћелија" . У следећем менију изаберемо позицију "Дупликате вредности" .
    4. Прелазак на условно форматирање у Мицрософт Екцелу

    5. Покреће се прозор за подешавање избора дуплих вредности. Ако сте све урадили исправно, у овом прозору остаје само да кликнете на дугме "ОК" . Иако ако желите, можете одабрати другу боју истицања у одговарајућем пољу овог прозора.
    6. Прозор за подешавање избора дупликата у Мицрософт Екцелу

    7. Након што направимо одређену акцију, сви понављајући елементи ће бити означени са изабраном бојом. Ти елементи који се не подударају остају нијансирани у првобитној боји (подразумевано је бело). Стога, одмах можете визуелно видети која је разлика између низова.

    Дуплиране вредности су означене у Мицрософт Екцелу

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

    Подешавање селекције јединствених вредности у програму Мицрософт Екцел

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

    Јединствене вредности су истакнуте у Мицрософт Екцелу

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

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

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

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

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

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

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

    1. Изаберите први елемент додатне колоне у којој ће се рачунати број утакмица. Затим кликните на икону "Инсерт фунцтион" .
    2. Идите у Чаробњак за функције у програму Мицрософт Екцел

    3. Стартује функционални чаробњак . Идемо у категорију "Статистички" . На листи се налази назив "САВЕТ" . Након што га изаберете, кликните на дугме "ОК" .
    4. Идите у прозор аргумената функције ЦОУНТИФ у програму Мицрософт Екцел

    5. Покренут је прозор аргумената оператора САВЕТОВАЊА . Као што видите, имена поља у овом прозору одговарају називима аргумената.

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

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

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

    6. Прозор аргумента функције ЦОУНТИФ у програму Мицрософт Екцел

    7. Резултат се приказује у елементу листа. Једнака је броју "1" . То значи да се на списку имена друге табеле појављује презиме "Гринев ВП" , који је први на листи првог поља таблице, једном.
    8. Резултат израчунавања функције ЦОУНТИФ у програму Мицрософт Екцел

    9. Сада морамо направити сличан израз за све остале елементе прве табеле. Да бисте то урадили, извршите копирање помоћу маркера за пуњење, као што смо већ урадили. Поставите курсор у доњи десни део елемента листа, који садржи функцију ЦОУНТИФ , и након што га претворите у маркер пуњења, притискамо леви тастер миша и повуцемо курсор надоле.
    10. Филлер у програму Мицрософт Екцел

    11. Као што видите, програм је израчунао коинциденције упоређивањем ћелије прве таблице са подацима који се налазе у другом распону таблица. У четири случаја, резултат је био "1" , ау два случаја, "0" . То значи да програм у другој табели није могао пронаћи двије вриједности које су доступне у првом низу таблица.

    Резултат израчунавања колоне помоћу функције ЦОУНТИФ у програму Мицрософт Екцел

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

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

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

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

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

      СТРОКА(D2)

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

    4. Прозор прозора функције ИФ у Мицрософт Екцелу

    5. Као што видите, први резултат је приказан као "ФАЛСЕ" . То значи да вредност не задовољава услове ИФ оператера. То значи да је прво презиме присутно у оба листа.
    6. Вредност ФАЛСЕ формула ИФ у Мицрософт Екцелу

    7. Коришћењем маркера пуњења копирамо израз оператора ИФ на читаву колону на уобичајени начин. Као што видите, за две ствари које су присутне у другој табели, али не у првом, формула даје бројеве линија.
    8. Бројеви линија у Мицрософт Екцелу

    9. Користимо се од стола десно на десно и попуњавамо колону бројевима редом, почев од 1 . Количество номеров должно совпадать с количеством строк во второй сравниваемой таблице. Чтобы ускорить процедуру нумерации, можно также воспользоваться маркером заполнения.
    10. Нумерация строк в Microsoft Excel

    11. После этого выделяем первую ячейку справа от колонки с номерами и щелкаем по значку «Вставить функцию» .
    12. Убаците функцију у Мицрософт Екцел

    13. Отвара се Чаробњак за функције . Переходим в категорию «Статистические» и производим выбор наименования «НАИМЕНЬШИЙ» . Кликом на дугме "ОК" .
    14. Переход в окно аргументов функции НАИМЕНЬШИЙ в Microsoft Excel

    15. Функция НАИМЕНЬШИЙ , окно аргументов которой было раскрыто, предназначена для вывода указанного по счету наименьшего значения.

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

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

    16. Окно аргументов функции НАИМЕНЬШИЙ в Microsoft Excel

    17. Оператор выводит результат – число 3 . Именно оно наименьшее из нумерации несовпадающих строк табличных массивов. С помощью маркера заполнения копируем формулу до самого низа.
    18. Результат расчета функции НАИМЕНЬШИЙ в Microsoft Excel

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

    21. После этого открывается небольшое окошко, в котором нужно определить, ссылочный вид должна иметь функция ИНДЕКС или предназначенный для работы с массивами. Нам нужен второй вариант. Он установлен по умолчанию, так что в данном окошке просто щелкаем по кнопке «OK» .
    22. Окошко выбора вида функции ИНДЕКС в Microsoft Excel

    23. Запускается окно аргументов функции ИНДЕКС . Данный оператор предназначен для вывода значения, которое расположено в определенном массиве в указанной строке.

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

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

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

    24. Окно аргументов функции ИНДЕКС в Microsoft Excel

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

    Фамилии выведены с помощью функции ИНДЕКС в Microsoft Excel

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

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

    Сравнение таблиц в двух книгах в Microsoft Excel

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

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