Како да споредите две списоци во 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. Ајде да одредиме колку пати секоја единствена вредност се појавува во низата со податоци. Еве ги формулите за да го направите ова: =COUNTIF(Список1,Д5) и =COUNTI(Список2,Д5).
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. Синхрони листи

Ова е лесна опција. Да претпоставиме дека имаме такви списоци.

За да одредите колку пати вредностите не се споиле, можете да ја користите формулата: =SUMPRODUCT(—(A2:A20<>B2:B20)). Ако добиеме 0 како резултат, тоа значи дека двете списоци се исти.

Опција 2: Мешани листи

Ако списоците не се идентични по редоследот на објектите што ги содржат, треба да примените карактеристика како условно форматирање и обоени дупликати вредности. Или користете ја функцијата COUNTIF, со кој одредуваме колку пати елемент од една листа се појавува во втората.

Како да споредите 2 колони ред по ред

Кога споредуваме две колони, честопати треба да споредиме информации што се во различни редови. За да го направите ова, операторот ќе ни помогне АКО. Ајде да погледнеме како тоа функционира во пракса. За да го направите ова, ви претставуваме неколку илустративни ситуации.

Пример. Како да споредите 2 колони за совпаѓања и разлики во еден ред

За да анализираме дали вредностите што се во истиот ред, но различни колони се исти, ја пишуваме функцијата IF. Формулата се вметнува во секој ред сместен во помошната колона каде што ќе се прикажат резултатите од обработката на податоците. Но, воопшто не е неопходно да се препише во секој ред, само копирајте го во преостанатите ќелии од оваа колона или користете го маркерот за автоматско комплетирање.

Треба да запишеме таква формула за да разбереме дали вредностите во двете колони се исти или не: =IF(A2=B2, „Натпревар“, „“). Логиката на оваа функција е многу едноставна: ги споредува вредностите во ќелиите A2 и B2, а ако се исти, ја прикажува вредноста „Се совпаѓа“. Ако податоците се различни, тие не враќаат никаква вредност. Можете исто така да ги проверите ќелиите за да видите дали има совпаѓање меѓу нив. Во овој случај, формулата што се користи е: =IF(A2<>B2, „Не се совпаѓаат“, „“). Принципот е ист, прво се врши проверка. Ако се покаже дека ќелиите го исполнуваат критериумот, тогаш се прикажува вредноста „Не се совпаѓа“.

Исто така, можно е да се користи следнава формула во полето за формула за да се прикажат и „Match“ ако вредностите се исти и „Не се совпаѓаат“ ако се различни: =IF(A2=B2; „Соклопи“, „Не се совпаѓаат“). Можете исто така да го користите операторот за нееднаквост наместо операторот за еднаквост. Само редоследот на вредностите што ќе се прикажат во овој случај ќе биде малку поинаков: =IF(A2<>B2, „Не се совпаѓаат“, „Се совпаѓаат“). По користењето на првата верзија на формулата, резултатот ќе биде како што следува.

Оваа варијација на формулата е нечувствителна на букви. Затоа, ако вредностите во една колона се разликуваат од другите само по тоа што се напишани со големи букви, тогаш програмата нема да ја забележи оваа разлика. За споредбата да биде чувствителна на големи букви, треба да ја користите функцијата во критериумите Токму. Останатите аргументи се оставени непроменети: =АКО (EXACT(A2,B2), „Соклоп“, „Единствен“).

Како да споредите повеќе колони за совпаѓања во еден ред

Можно е да се анализираат вредностите во списоците според цел сет на критериуми:

1. Најдете ги оние редови кои имаат исти вредности насекаде.
2. Најдете ги оние редови каде што има совпаѓања во само две списоци.

Ајде да погледнеме неколку примери за тоа како да се продолжи во секој од овие случаи.

Пример. Како да најдете совпаѓања во еден ред во повеќе колони од табелата

Да претпоставиме дека имаме серија од колони кои ги содржат информациите што ни се потребни. Соочени сме со задача да ги одредиме оние редови во кои вредностите се исти. За да го направите ова, треба да ја користите следнава формула: =АКО(И(A2=B2,A2=C2), „совпаѓање“, „“).

Ако има премногу колони во табелата, тогаш само треба да ја користите заедно со функцијата IF оператор COUNTIF: =IF(COUNTIF($A2:$C2,$A2)=3;“совпаѓање“;““). Бројот што се користи во оваа формула го означува бројот на колони за проверка. Ако се разликува, тогаш треба да напишете онолку колку што е точно за вашата ситуација.

Пример. Како да најдете совпаѓања во еден ред во кои било 2 колони од табелата

Да речеме дека треба да провериме дали вредностите во еден ред се совпаѓаат во две колони од оние во табелата. За да го направите ова, треба да ја користите функцијата како услов OR, каде наизменично се пишува еднаквоста на секоја од колоните со другата. Еве еден пример.

Ја користиме оваа формула: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Може да има ситуација кога има многу колони во табелата. Во овој случај, формулата ќе биде огромна и може да биде потребно многу време за да се изберат сите потребни комбинации. За да го решите овој проблем, треба да ја користите функцијата COUNTIF: =IF(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0; „Единствена низа“; „Не единствена низа“)

Гледаме дека вкупно имаме две функции COUNTIF. Со првата наизменично одредуваме колку колони имаат сличност со А2, а со втората го проверуваме бројот на сличности со вредноста на Б2. Ако, како резултат на пресметување со оваа формула, добиеме нулта вредност, тоа покажува дека сите редови во оваа колона се единствени, ако повеќе, има сличности. Затоа, ако како резултат на пресметување со две формули и додавање на конечните резултати добиеме нулта вредност, тогаш се враќа текстуалната вредност „Единствена низа“, ако овој број е поголем, се пишува дека оваа низа не е единствена.

Како да споредите 2 колони во Excel за совпаѓања

Сега да земеме пример. Да речеме дека имаме табела со две колони. Треба да проверите дали се совпаѓаат. За да го направите ова, треба да ја примените формулата, каде што ќе се користи и функцијата IF, и операторот COUNTIF: =IF(COUNTIF($B:$B,$A5)=0, „Нема совпаѓања во колоната Б“, „Има совпаѓања во колоната Б“)

Не се потребни дополнителни активности. Откако ќе го пресметаме резултатот со оваа формула, ја добиваме вредноста на третиот аргумент на функцијата IF натпревари. Ако ги нема, тогаш содржината на вториот аргумент.

Како да се споредат 2 колони во Excel за совпаѓање и да се истакне со боја

За полесно визуелно да се идентификуваат соодветните колони, можете да ги истакнете со боја. За да го направите ова, треба да ја користите функцијата „Условно форматирање“. Ајде да видиме во пракса.

Наоѓање и истакнување совпаѓања по боја во повеќе колони

За да ги одредите совпаѓањата и да ги истакнете, прво мора да го изберете опсегот на податоци во кој ќе се изврши проверката, а потоа да ја отворите ставката „Условно форматирање“ на табулаторот „Дома“. Таму, изберете „Duplicate Values“ како правило за избор на ќелии.

После тоа, ќе се појави нов дијалог прозорец во кој во левата скокачка листа ја наоѓаме опцијата „Повторување“, а во десната листа ја избираме бојата што ќе се користи за селекција. Откако ќе кликнете на копчето „OK“, ќе се избере позадината на сите ќелии со сличности. Потоа само споредете ги колоните по око.

Наоѓање и истакнување линии за појавување

Техниката за проверка дали жиците се совпаѓаат е малку поинаква. Прво, треба да создадеме дополнителна колона и таму ќе ги користиме комбинираните вредности користејќи го операторот &. За да го направите ова, треба да напишете формула на формата: =A2&B2&C2&D2.

Ја избираме колоната што е креирана и ги содржи комбинираните вредности. Следно, ја извршуваме истата низа на дејства што е опишана погоре за колоните. Дупликат линии ќе бидат означени во бојата што ќе ја наведете.

Гледаме дека нема ништо тешко во барањето повторувања. Excel ги содржи сите потребни алатки за ова. Важно е само да вежбате пред да го спроведете сето ова знаење во пракса.