Различные методы сопоставления данных в Excel
Существуют различные методы сопоставления данных в Excel, если мы хотим сопоставить данные в одном столбце, скажем, мы хотим проверить на дублирование, мы можем использовать условное форматирование на главной вкладке или иначе, если мы хотим сопоставить данные в двух или больше разных столбцов, мы можем использовать условные функции, такие как функция if.
- Метод №1 - Использование функции Vlookup
- Метод № 2 - Использование индекса + функции соответствия
- Метод № 3 - Создайте свое собственное значение поиска
Теперь давайте подробно обсудим каждый из методов.
Вы можете скачать этот шаблон Excel для сопоставления данных здесь - Шаблон для сопоставления данных Excel# 1 - Сопоставление данных с помощью функции ВПР
VLOOKUP используется не только для получения необходимой информации из таблицы данных, но и как инструмент согласования. Когда дело доходит до согласования или сопоставления данных, формула ВПР возглавляет таблицу.
Для примера посмотрите таблицу ниже.
У нас есть две таблицы данных, первая - это данные 1, а вторая - данные 2.
Теперь нам нужно согласовать, совпадают ли данные в двух таблицах или нет. Самый первый способ сопоставления данных - это функция SUM в Excel для двух таблиц, чтобы получить общие продажи.
Данные 1 - Таблица
Данные 2 - Таблица
Я применил функцию СУММ для столбца «Сумма продажи» таблицы. На самом начальном шаге мы получили разницу в значениях. Таблица данных 1 показывает общий объем продаж 2,16 214 и таблица данных 2 показывает общий объем продаж 2,10 214 .
Теперь нам нужно изучить это подробнее. Итак, применим функцию ВПР для каждой даты.
Выберите массив таблицы как диапазон данных 1 .
Нам нужны данные из второго столбца, а диапазон поиска - ЛОЖЬ, т.е. точное совпадение.
Результат представлен ниже:
В следующей ячейке вычтите исходное значение из полученного значения.
После вычитания получаем нулевой результат.
Теперь скопируйте и вставьте формулу во все ячейки, чтобы получить значения дисперсии.
В ячейках G6 и G12 мы получили различия.
В данных 1 у нас есть 12104 для даты 04 марта 2019 года, а в данных 2 - 15104 для той же даты, поэтому разница составляет 3000.
Точно так же для даты 18 марта 2019 года в данных 1 у нас есть 19351, а в данных 2 - 10351, поэтому разница составляет 9000.
# 2 - Соответствие данных с помощью функции ИНДЕКС + ПОИСКПОЗ
Для тех же данных мы можем использовать функцию ИНДЕКС + ПОИСКПОЗ. Мы можем использовать это как альтернативу функции ВПР.
Функция ИНДЕКС, используемая для получения значения из выбранного столбца на основе предоставленного номера строки. Чтобы предоставить номер строки, нам нужно использовать функцию ПОИСКПОЗ на основе значения ПРОСМОТР.
Откройте функцию ИНДЕКС в ячейке F3.
Выберите массив в качестве диапазона столбцов результата, то есть от B2 до B14.
Чтобы получить номер строки, откройте функцию MATCH в качестве следующего аргумента.
Выберите значение поиска как ячейку D3.
Затем выберите массив подстановки в качестве столбца Дата продажи в Data 1.
В типе соответствия выберите «0 - точное соответствие».
Закройте две скобки и нажмите клавишу ввода, чтобы получить результат.
Это также дает тот же результат, что и только ВПР. Поскольку мы использовали одни и те же данные, мы получили числа как есть
# 3 - Создайте свою собственную ценность поиска
Теперь мы увидели, как сопоставлять данные с помощью функций Excel. Теперь мы увидим другой сценарий реального времени. Для этого примера посмотрите на данные ниже.
В приведенных выше данных у нас есть данные о продажах по зонам и по дате, как показано выше. Нам нужно снова выполнить процесс сопоставления данных. Применим функцию ВПР, как в предыдущем примере.
У нас много разногласий. Давайте рассмотрим каждый случай отдельно.
В ячейке I5 мы получили дисперсию 8300. Давайте посмотрим на основную таблицу.
Несмотря на то, что в основной таблице значение 12104, мы получили значение 20404 из функции VLOOKUP. Причина этого в том, что функция ВПР может возвращать значение первого найденного значения поиска.
В данном случае значение поиска - дата, например 20 марта 2019 г. В приведенной выше ячейке для северной зоны на ту же дату у нас есть значение 20404, поэтому функция ВПР вернула это значение и для восточной зоны.
Чтобы решить эту проблему, нам нужно создать уникальные значения поиска. Объедините зону, дату и сумму продаж как в данных 1, так и в данных 2.
Данные 1 - Таблица
Данные 2 - Таблица
Теперь мы создали уникальное значение для каждой зоны с объединенным значением зоны, даты продажи и суммы продажи.
Использование этих уникальных значений позволяет применить функцию ВПР.
Примените формулу ко всем ячейкам, мы получим нулевую дисперсию во всех ячейках.
Таким образом, используя функции Excel, мы можем сопоставлять данные и находить отклонения. Перед применением формулы нам нужно посмотреть на дубликаты в поисковом значении для точного согласования. Приведенный выше пример является лучшей иллюстрацией повторяющихся значений в поисковом значении. В таких сценариях нам нужно создать наши собственные уникальные значения поиска и получить результат.