Как сопоставить данные в Excel?

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

Существуют различные методы сопоставления данных в Excel, если мы хотим сопоставить данные в одном столбце, скажем, мы хотим проверить дублирование, мы можем использовать условное форматирование из домашней вкладки или иначе, если мы хотим сопоставить данные в двух или больше разных столбцов, мы можем использовать условные функции, такие как функция if.

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

Теперь давайте подробно обсудим каждый из методов.

Вы можете скачать этот шаблон Excel для сопоставления данных здесь — Шаблон для сопоставления данных Excel

# 1 — Сопоставление данных с помощью функции ВПР

VLOOKUP используется не только для получения необходимой информации из таблицы данных; скорее, его также можно использовать как инструмент согласования. Когда дело доходит до согласования или сопоставления данных, формула ВПР возглавляет таблицу.

Например, посмотрите на приведенную ниже таблицу.

Данные соответствия Пример 1

У нас есть две таблицы данных, первая — Данные 1 и второй Данные 2.

Теперь нам нужно согласовать, совпадают ли данные в двух таблицах или нет. Самый первый способ сопоставить данные — это функция SUM в Excel для двух таблиц, чтобы получить общие продажи.

Данные 1 — Таблица

Данные соответствия Пример 1-2

Данные 2 — Таблица

Данные соответствия Пример 1-3 Данные соответствия Пример 1-3

Я применил функцию СУММ для столбца «Сумма продажи» таблицы. На самом начальном шаге мы получили разницу в значениях. Данные 1 таблица, показывающая общий объем продаж 2,16,214, и Данные 2 таблица, показывающая общий объем продаж 2,10 214.

Теперь нам нужно изучить это подробнее. Итак, применим функцию ВПР для каждой даты.

Данные соответствия Пример 1-4

Выберите массив таблиц как Данные 1 спектр.

Данные соответствия Пример 1-5

Нам нужны данные из второго столбца, а диапазон поиска — ЛОЖЬ, т. Е. Точное совпадение.

Данные соответствия Пример 1-6

Результат представлен ниже:

Данные соответствия Пример 1-7

В следующей ячейке вычтите исходное значение из полученного значения.

Данные соответствия Пример 1-8

После вычитания получаем нулевой результат.

Данные соответствия Пример 1-9

Теперь скопируйте и вставьте формулу во все ячейки, чтобы получить значения дисперсии.

Данные соответствия Пример 1-10

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

Данные соответствия Пример 1-11

В Данные 1, у нас есть 12104 на дату 04 марта 2019 года, а в Данные 2, у нас есть 15104 на ту же дату, поэтому разница составляет 3000.

Аналогично на дату 18 марта 2019 г. Данные 1, у нас 19351, а в Данные 2, у нас 10351, значит разница 9000.

# 2 — Соответствие данных с помощью функции ИНДЕКС + ПОИСКПОЗ

Для тех же данных мы можем использовать функцию ИНДЕКС + ПОИСКПОЗ. Мы можем использовать это как альтернативу функции ВПР.

Функция ИНДЕКС используется для получения значения из выбранного столбца на основе предоставленного номера строки. Чтобы указать номер строки, нам нужно использовать функцию ПОИСКПОЗ на основе значения ПРОСМОТР.

Откройте функцию ИНДЕКС в ячейке F3.

Данные соответствия Пример 2-1

Выберите массив в качестве диапазона столбцов результата, то есть от B2 до B14.

Данные соответствия Пример 2-2

Чтобы получить номер строки, откройте функцию ПОИСКПОЗ в качестве следующего аргумента.

Пример 2-3

Выберите значение поиска как ячейку D3.

Пример 2-4

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

Пример 2-5

В типе соответствия выберите «0 — точное совпадение».

Пример 2-6

Закройте две скобки и нажмите клавишу ввода, чтобы получить результат.

Данные соответствия Пример 2-7

Это также дает тот же результат, что и только ВПР. Поскольку мы использовали одни и те же данные, мы получили числа как есть

# 3 — Создайте свою собственную ценность поиска

Теперь мы увидели, как сопоставлять данные с помощью функций Excel. Теперь мы увидим разные сценарии в реальном времени. В этом примере посмотрите на данные ниже.

Пример 3

В приведенных выше данных у нас есть данные о продажах по зонам и по дате, как показано выше. Нам нужно снова выполнить процесс сопоставления данных. Применим функцию ВПР, как в предыдущем примере.

Пример 3-1

У нас много разногласий. Давайте разберемся в каждом конкретном случае.

В ячейке I5 мы получили дисперсию 8300. Давайте посмотрим на основную таблицу.

Данные соответствия Пример 3-2

Несмотря на то, что значение основной таблицы — 12104, мы получили значение 20404 из функции VLOOKUP. Причина в том, что функция ВПР может возвращать значение первого найденного значения поиска.

В данном случае значение поиска — это дата, например, 20 марта 2019 г. В приведенной выше ячейке для северной зоны на ту же дату у нас есть значение 20404, поэтому функция ВПР вернула это значение и для восточной зоны.

Чтобы решить эту проблему, нам нужно создать уникальные значения поиска. Объединить Зона, дата и сумма продаж в обоих Данные 1 и Данные 2.

Данные 1 — Таблица

Пример 3-3

Данные 2 — Таблица

Данные соответствия Пример 3-4

Теперь мы создали уникальное значение для каждой зоны с объединенным значением зоны, даты продажи и суммы продажи.

Используя эти уникальные значения, давайте применим функцию ВПР.

Данные соответствия Пример 3-5

Примените формулу ко всем ячейкам; мы получим нулевую дисперсию во всех ячейках.

Данные соответствия Пример 3-6

Таким образом, используя функции Excel, мы можем сопоставить данные и найти отклонения. Перед применением формулы нам нужно посмотреть на дубликаты в поисковом значении для точного согласования. Приведенный выше пример является лучшей иллюстрацией повторяющихся значений в поисковом значении. В таких сценариях нам нужно создать наши собственные уникальные значения поиска и получить результат.

УЗНАТЬ БОЛЬШЕ >>

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован.