Различни методи за съвпадение на данни в Excel
Има различни методи за съвпадение на данните в Excel, ако искаме да съпоставим данните в една и съща колона, да речем, че искаме да проверим за дублиране, можем да използваме условно форматиране от началния раздел или, ако искаме да съпоставим данните в две или повече различни колони можем да използваме условни функции като if функция.
- Метод # 1 - Използване на функцията Vlookup
- Метод # 2 - Използване на индекс + функция за съвпадение
- Метод # 3 - Създайте своя собствена справочна стойност
Сега нека обсъдим подробно всеки от методите
# 1 - Съвпадение на данни с помощта на VLOOKUP функция
VLOOKUP не се използва само за получаване на необходимата информация от таблицата с данни; по-скоро може да се използва и като инструмент за помирение. Когато става въпрос за съгласуване или съвпадение на данните, формулата VLOOKUP води таблицата.
Например погледнете таблицата по-долу.
Тук имаме две таблици с данни, първата е Data 1 , а втората е Data 2.
Сега трябва да се съгласуваме дали данните в двете таблици съвпадат или не. Първият начин за съвпадение на данните е функцията SUM в Excel до две таблици, за да получите общите продажби.
Данни 1 - Таблица
Данни 2 - Таблица
Приложих функцията SUM и за двете колони на сумата за продажба на таблицата. В самата начална стъпка получихме разликата в стойностите. Таблица с данни 1 показва общите продажби от 2,16,214, а таблицата с данни 2 показва общите продажби от 2,10,214 .
Сега трябва да разгледаме това подробно. И така, нека приложим функцията VLOOKUP за всяка дата.
Изберете масива на таблицата като обхват на данни 1 .
Нуждаем се от данните от втората колона и обхватът на търсене е FALSE, т.е. Exact Match.
Резултатът е даден по-долу:
В следващата клетка се приспада първоначалната стойност с пристигаща стойност.
След приспадане получаваме резултата като нула.
Сега копирайте и поставете формулата във всички клетки, за да получите стойностите на дисперсията.
В клетки 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, можем да съпоставим данните и да намерим дисперсиите. Преди да приложим формулата, трябва да разгледаме дубликатите в справочната стойност за точно съгласуване. Горният пример е най-добрата илюстрация на дублиращи се стойности в справочна стойност. В такива сценарии трябва да създадем свои собствени уникални справочни стойности и да стигнем до резултата.
