Една от многото възможности на Microsoft Excel е възможността за сравняване на два списъка с данни, идентифициране на съвпадения между списъците и идентифициране на елементите, които се намират само в един списък. Това е полезно, когато сравнявате финансови записи или проверявате дали определено име е в база данни. Можете да използвате функцията MATCH за идентифициране и маркиране на съвпадащи или несъответстващи записи или можете да използвате форматиране на кондициониране с функцията COUNTIF. Следващите стъпки ви казват как да използвате всяка, за да съответства на вашите данни.
Стъпки
Метод 1 от 2: Идентифициране на записи с функцията MATCH
Стъпка 1. Копирайте списъците с данни в един работен лист
Excel може да работи с множество работни листове в една работна книга или с множество работни книги, но сравняването на списъците ще ви бъде по -лесно, ако копирате тяхната информация в един работен лист.
Стъпка 2. Дайте на всеки елемент от списъка уникален идентификатор
Ако двата ви списъка не споделят общ начин за идентифицирането им, може да се наложи да добавите допълнителна колона към всеки списък с данни, който идентифицира този елемент в Excel, така че да може да види дали елемент от даден списък е свързан с елемент в другия списък. Характерът на този идентификатор ще зависи от вида на данните, които се опитвате да съпоставите. Ще ви е необходим идентификатор за всеки списък с колони.
- За финансови данни, свързани с даден период, като например данъчни записи, това може да бъде описанието на актив, датата на придобиване на актива или и двете. В някои случаи запис може да бъде идентифициран с кодов номер; Въпреки това, ако една и съща система не се използва за двата списъка, този идентификатор може да създаде съвпадения, където няма такива, или да игнорира съвпадения, които трябва да бъдат направени.
- В някои случаи можете да вземете елементи от един списък и да ги комбинирате с елементи от друг списък, за да създадете идентификатор, като например описание на физически актив и годината, в която е закупен. За да създадете такъв идентификатор, трябва да обедините (добавите, комбинирате) данни от две или повече клетки, като използвате знака (&). За да комбинирате описание на елемент в клетка F3 с дата в клетка G3, разделена с интервал, трябва да въведете формулата '= F3 & "" & G3' в друга клетка в този ред, например E3. Ако искате да включите само годината в идентификатора (тъй като единият списък използва пълни дати, а другият използва само години), бихте включили функцията YEAR, като вместо това въведете '= F3 & "" & YEAR (G3)' в клетка E3. (Не включвайте единичните кавички; те са само за да посочат примера.)
- След като създадете формулата, можете да я копирате във всички други клетки на колоната с идентификатори, като изберете клетката с формулата и плъзнете дръжката за запълване над другите клетки на колоната, където искате да копирате формулата. Когато отпуснете бутона на мишката, всяка клетка, която плъзнете, ще бъде попълнена с формулата, като препратките към клетките ще бъдат коригирани към съответните клетки в същия ред.
Стъпка 3. Стандартизирайте данните, когато е възможно
Докато умът разпознава, че "Inc." и „Включен“означават едно и също нещо, Excel не го прави, освен ако не го накарате да форматира една или друга дума. По същия начин можете да считате стойности като $ 11, 950 и $ 11, 999.95 като достатъчно близки, за да съвпадат, но Excel няма да го направи, освен ако не го кажете.
- Можете да се справите с някои съкращения, като например „Co“за „Company“и „Inc“за „Incorporated, като използвате функцията за низ LEFT за отрязване на допълнителните знаци. Други съкращения, като например„ Assn “за„ асоциация “, може да са се решава чрез създаване на ръководство за стил на въвеждане на данни и след това се пише програма за търсене и коригиране на неподходящи формати.
- За низове от числа, като например пощенски кодове, където някои записи включват суфикса ZIP+4, а други не, можете отново да използвате функцията на низ отляво, за да разпознаете и съпоставите само първичните пощенски кодове. За да накарате Excel да разпознае числови стойности, които са близки, но не еднакви, можете да използвате функцията ROUND, за да закръглите стойностите за затваряне до един и същи номер и да ги съпоставите.
- Допълнителните интервали, като например въвеждане на две интервали между думи вместо едно, могат да бъдат премахнати с помощта на функцията TRIM.
Стъпка 4. Създайте колони за формулата за сравнение
Точно както трябваше да създадете колони за идентификаторите на списъка, ще трябва да създадете колони за формулата, която прави сравнението вместо вас. Ще ви трябва една колона за всеки списък.
Искате да обозначите тези колони с нещо като „Липсва?“
Стъпка 5. Въведете формулата за сравнение във всяка клетка
За формулата за сравнение ще използвате функцията MATCH, вложена в друга функция на Excel, ISNA.
- Формулата е под формата на "= ISNA (MATCH (G3, $ L $ 3: $ L $ 14, FALSE))", където клетка от колоната с идентификатори на първия списък се сравнява с всеки от идентификаторите във втория списък с вижте дали съвпада с един от тях. Ако не съвпада, липсва запис и думата „TRUE“ще се покаже в тази клетка. Ако съвпада, записът е наличен и ще се покаже думата „FALSE“. (Когато въвеждате формулата, не включвайте ограждащите кавички.)
- Можете да копирате формулата в останалите клетки на колоната по същия начин, по който сте копирали формулата за идентификатор на клетката. В този случай само препратката към клетката за клетката на идентификатора се променя, тъй като поставянето на знаците за долар пред препратките на ред и колона за първата и последната клетки в списъка на вторите идентификатори на клетки ги прави абсолютни препратки.
- Можете да копирате формулата за сравнение за първия списък в първата клетка на колоната за втория списък. След това ще трябва да редактирате препратките към клетките, така че „G3“да бъде заменено с препратката за първата клетка на идентификатора на втория списък и „$ L $ 3: $ L $ 14“да бъде заменена с първата и последната клетка на идентификатора на втори списък. (Оставете знаците за долар и двоеточие на мира.) След това можете да копирате тази редактирана формула в останалите клетки в реда за сравнение на втория списък.
Стъпка 6. Сортирайте списъците, за да видите по-лесно несъответстващите стойности, ако е необходимо
Ако вашите списъци са големи, може да се наложи да ги сортирате, за да съберете всички несъответстващи стойности. Инструкциите в подетапите по -долу ще преобразуват формулите в стойности, за да се избегнат грешки при преизчисляване, а ако вашите списъци са големи, ще се избегне дълго време за преизчисляване.
- Плъзнете мишката върху всички клетки в списък, за да го изберете.
- Изберете Копиране от менюто Редактиране в Excel 2003 или от групата Буфер в лентата Начало в Excel 2007 или 2010.
- Изберете Специално поставяне от менюто Редактиране в Excel 2003 или от падащия бутон Поставяне в групата Буфер в лентата Начало на Excel 2007 или 2010s.
- Изберете „Стойности“от списъка Поставяне като в диалоговия прозорец Специално поставяне. Щракнете върху OK, за да затворите диалоговия прозорец.
- Изберете Сортиране от менюто Данни в Excel 2003 или групата Сортиране и филтриране на лентата с данни в Excel 2007 или 2010.
- Изберете „Ред на заглавката“от списъка „Моят диапазон от данни има“в диалоговия прозорец Сортиране по, изберете „Липсва?“(или името, което всъщност сте дали заглавката на колоната за сравнение) и щракнете върху OK.
- Повторете тези стъпки за другия списък.
Стъпка 7. Сравнете визуално несъответстващите елементи, за да видите защо не съвпадат
Както бе отбелязано по -рано, Excel е проектиран да търси точни съвпадения на данните, освен ако не сте го настроили да търси приблизителни. Несъответствието ви може да бъде толкова просто, колкото случайно транспониране на букви или цифри. Това може да бъде и нещо, което изисква независима проверка, като например проверка, за да се види дали изброените активи трябва да бъдат отчетени на първо място.
Метод 2 от 2: Условно форматиране с COUNTIF
Стъпка 1. Копирайте списъците с данни в един работен лист
Стъпка 2. Решете в кой списък искате да маркирате съвпадащи или несъответстващи записи
Ако искате да маркирате записи само в един списък, вероятно ще искате да маркирате записите, уникални за този списък; тоест записи, които не съвпадат с записи в другия списък. Ако искате да маркирате записи в двата списъка, ще искате да маркирате записи, които съвпадат един с друг. За целите на този пример ще приемем, че първият списък заема клетки G3 до G14, а вторият списък заема клетки L3 до L14.
Стъпка 3. Изберете елементите в списъка, в които искате да маркирате уникални или съвпадащи елементи
Ако искате да маркирате съвпадащи елементи в двата списъка, ще трябва да изберете списъците един по един и да приложите формулата за сравнение (описана в следващата стъпка) към всеки списък.
Стъпка 4. Приложете подходящата формула за сравнение
За да направите това, ще трябва да получите достъп до диалоговия прозорец за условно форматиране във вашата версия на Excel. В Excel 2003 правите това, като изберете Условно форматиране от менюто Формат, докато в Excel 2007 и 2010 щракнете върху бутона Условно форматиране в групата Стилове на лентата Начало. Изберете типа правило като „Формула“и въведете формулата си в полето Редактиране на описанието на правилото.
- Ако искате да маркирате записи, уникални за първия списък, формулата ще бъде "= COUNTIF ($ L $ 3: $ L $ 14, G3 = 0)", като диапазонът от клетки на втория списък се изобразява като абсолютни стойности и препратката към първата клетка от първия списък като относителна стойност. (Не въвеждайте близките кавички.)
- Ако искате да маркирате записи, уникални за втория списък, формулата ще бъде "= COUNTIF ($ G $ 3: $ G $ 14, L3 = 0)", като диапазонът от клетки на първия списък се изобразява като абсолютни стойности и препратката към първата клетка на втория списък като относителна стойност. (Не въвеждайте близките кавички.)
- Ако искате да подчертаете записите във всеки списък, които се намират в другия списък, ще ви трябват две формули, една за първия списък и една за втория. Формулата за първия списък е "= COUNTIF ($ L $ 3: $ L $ 14, G3> 0)", докато формулата за втория списък е COUNTIF ($ G $ 3: $ G $ 14, L3> 0) ". Както отбелязано по -рано, избирате първия списък, за да приложите формулата му, и след това избирате втория списък, за да приложите формулата му.
- Приложете каквото форматиране искате, за да маркирате записите, които са маркирани. Щракнете върху OK, за да затворите диалоговия прозорец.
Видео - Използвайки тази услуга, може да се сподели част от информацията с YouTube
Съвети
- Вместо да използвате препратка към клетка с метода за условно форматиране COUNTIF, можете да въведете стойност, която да търсите, и да маркирате един или повече списъци за екземпляри на тази стойност.
- За да опростите формулярите за сравнение, можете да създадете имена за вашия списък, като "List1" и "List2". След това, когато записвате формулите, тези имена на списъци могат да заменят абсолютните диапазони от клетки, използвани в горните примери.
