Как да съпоставим данните в Excel? Ръководство стъпка по стъпка (с примери)

Различни методи за съвпадение на данни в Excel

Има различни методи за съвпадение на данните в Excel, ако искаме да съпоставим данните в една и съща колона, да речем, че искаме да проверим за дублиране, можем да използваме условно форматиране от началния раздел или, ако искаме да съпоставим данните в две или повече различни колони можем да използваме условни функции като if функция.

  • Метод # 1 - Използване на функцията Vlookup
  • Метод # 2 - Използване на индекс + функция за съвпадение
  • Метод # 3 - Създайте своя собствена справочна стойност

Сега нека обсъдим подробно всеки от методите

Можете да изтеглите този шаблон за съвпадение на данни от Excel тук - Шаблон за съвпадение на данните в Excel

# 1 - Съвпадение на данни с помощта на VLOOKUP функция

VLOOKUP не се използва само за получаване на необходимата информация от таблицата с данни, а може да се използва и като инструмент за съгласуване. Когато става въпрос за съгласуване или съвпадение на данните, формулата VLOOKUP води таблицата.

За пример вижте таблицата по-долу.

Тук имаме две таблици с данни, първата е Data 1 , а втората е Data 2.

Сега трябва да се съгласуваме дали данните в две таблици съвпадат или не. Първият начин за съвпадение на данните е функцията SUM в Excel до две таблици, за да получите общите продажби.

Данни 1 - Таблица

Данни 2 - Таблица

Приложих функцията SUM и за двете колони на сумата за продажба на таблицата. В самата начална стъпка получихме разликата в стойностите. Таблица с данни 1, показваща общите продажби от 2,16,214, и таблица с данни 2, показваща общите продажби от 2,10,214 .

Сега трябва да разгледаме това подробно. И така, нека приложим функцията VLOOKUP за всяка дата.

Изберете масива на таблицата като обхват на данни 1 .

Нуждаем се от данните от втората колона и обхватът на търсене е FALSE, т.е. Точно съвпадение.

Резултатът е даден по-долу:

В следващата клетка се приспада първоначалната стойност с пристигаща стойност.

След приспадане получаваме резултата като нула.

Сега копирайте и поставете формулата във всички клетки, за да получите стойностите на дисперсията.

В клетка G6 и G12 получихме разликите.

В данни 1 имаме 12104 за датата 04-март 2019 г., а в данни 2 имаме 15104 за същата дата, така че има разлика от 3000.

По същия начин за датата 18 март 2019 г. в Данни 1 имаме 19351, а в Данни 2 имаме 10351, така че разликата е 9000.

# 2 - Съвпадение на данни с помощта на функцията INDEX + MATCH

За същите данни можем да използваме функцията INDEX + MATCH. Можем да използваме това като алтернатива на функцията VLOOKUP.

Функция INDEX, използвана за получаване на стойността от избраната колона въз основа на предоставения номер на реда. За да предоставим номера на реда, трябва да използваме функцията MATCH, базирана на стойността LOOKUP.

Отворете функцията INDEX в клетката F3.

Изберете масива като обхват на колоната за резултат, т.е. B2 до B14.

За да получите номера на реда, отворете функцията MATCH сега като следващ аргумент.

Изберете справочната стойност като клетка D3.

След това изберете справочен масив като колона Дата на продажбите в Данни 1.

В типа на съвпадението изберете „0 - Точно съвпадение“.

Затворете две скоби и натиснете клавиша Enter, за да получите резултата.

Това също дава същия резултат като VLOOKUP само. Тъй като използвахме същите данни, получихме числата, каквито са

# 3 - Създайте своя собствена стойност за търсене

Сега видяхме как да съпоставим данните с помощта на Excel функции. Сега ще видим различния сценарий в реално време. За този пример разгледайте данните по-долу.

В горните данни имаме данни за продажбите по зони и по дата, както е показано по-горе. Трябва отново да направим процеса на съвпадение на данните. Нека приложим функцията VLOOKUP, както е описано в предишния пример.

Имаме много отклонения. Нека разгледаме всеки отделен случай.

В клетка I5 получихме дисперсията от 8300. Нека разгледаме основната таблица.

Въпреки че в основната таблица стойността е 12104, ние получихме стойността 20404 от функцията VLOOKUP. Причината за това е, че VLOOKUP може да върне стойността на първата намерена справочна стойност.

В този случай нашата справочна стойност е дата, т.е. 20 март 2019 г. В горната клетка за Северна зона за същата дата имаме стойност 20404, така че VLOOKUP е върнал тази стойност и за Източна зона.

За да преодолеем този проблем, трябва да създадем уникални справочни стойности. Комбинирайте зона, дата и сума на продажбите както в данни 1, така и в данни 2.

Данни 1 - Таблица

Данни 2 - Таблица

Сега създадохме уникална стойност за всяка зона с комбинираната стойност на зона, дата на продажба и сума на продажбата.

Използването на тези уникални стойности позволява да се приложи функцията VLOOKUP.

Приложете формулата към всички клетки, ще получим дисперсията на нула във всички клетки.

По този начин, като използваме функции на Excel, можем да съпоставим данните и да намерим дисперсиите. Преди да приложим формулата, трябва да разгледаме дубликатите в справочната стойност за точно съгласуване. Горният пример е най-добрата илюстрация на дублиращи се стойности в справочна стойност. В такива сценарии трябва да създадем свои собствени уникални справочни стойности и да получим резултата.