Одна из многих возможностей Microsoft Excel - это возможность сравнивать два списка данных, выявлять совпадения между списками и определять, какие элементы находятся только в одном списке. Это полезно при сравнении финансовых записей или проверке, есть ли конкретное имя в базе данных. Вы можете использовать функцию ПОИСКПОЗ, чтобы идентифицировать и отмечать совпадающие или несовпадающие записи, или вы можете использовать условное форматирование с функцией СЧЁТЕСЛИ. Следующие шаги расскажут вам, как использовать каждый из них для сопоставления ваших данных.
Шаги
Метод 1 из 2: идентификация записей с помощью функции MATCH
Шаг 1. Скопируйте списки данных на один рабочий лист
Excel может работать с несколькими листами в одной книге или с несколькими книгами, но вам будет проще сравнивать списки, если вы скопируете их информацию на один лист.
Шаг 2. Дайте каждому элементу списка уникальный идентификатор
Если у ваших двух списков нет общего способа их идентификации, вам может потребоваться добавить дополнительный столбец в каждый список данных, который идентифицирует этот элемент в Excel, чтобы он мог видеть, связан ли элемент в данном списке с элементом. в другом списке. Природа этого идентификатора будет зависеть от типа данных, которые вы пытаетесь сопоставить. Вам понадобится идентификатор для каждого списка столбцов.
- Для финансовых данных, связанных с определенным периодом, таких как налоговые записи, это может быть описание актива, дата его приобретения или и то, и другое. В некоторых случаях запись может быть идентифицирована с помощью кодового номера; однако, если одна и та же система не используется для обоих списков, этот идентификатор может создавать совпадения там, где их нет, или игнорировать совпадения, которые должны быть выполнены.
- В некоторых случаях вы можете взять элементы из одного списка и объединить их с элементами из другого списка, чтобы создать идентификатор, например описание физического актива и год его покупки. Чтобы создать такой идентификатор, вы объединяете (добавляете, комбинируете) данные из двух или более ячеек с помощью амперсанда (&). Чтобы объединить описание элемента в ячейке F3 с датой в ячейке G3, разделенной пробелом, вы должны ввести формулу '= F3 & "" & G3' в другую ячейку этой строки, например E3. Если вы хотите включить в идентификатор только год (поскольку в одном списке используются полные даты, а в другом - только годы), вы должны включить функцию ГОД, введя вместо этого '= F3 & "" & ГОД (G3)' в ячейку E3. (Не включайте одинарные кавычки; они служат только для обозначения примера.)
- Создав формулу, вы можете скопировать ее во все другие ячейки столбца идентификатора, выбрав ячейку с формулой и перетащив маркер заполнения по другим ячейкам столбца, в который вы хотите скопировать формулу. Когда вы отпустите кнопку мыши, каждая ячейка, которую вы перетащили, будет заполнена формулой, а ссылки на ячейки будут настроены на соответствующие ячейки в той же строке.
Шаг 3. По возможности стандартизируйте данные
Пока разум признает, что "Inc." и "Incorporated" означают одно и то же, в Excel нет, если вы не переформатируете одно слово или другое. Точно так же вы можете считать, что такие значения, как 11 950 и 11 999,95 долларов, достаточно близки для совпадения, но Excel не будет этого делать, если вы не сообщите об этом.
- Вы можете работать с некоторыми сокращениями, такими как "Co" для "Company" и "Inc" для "Incorporated", используя строковую функцию LEFT для усечения дополнительных символов. Другие сокращения, такие как "Assn" для "Association", могут быть лучше решается путем создания руководства по стилю ввода данных и последующего написания программы для поиска и исправления неправильных форматов.
- Для строк чисел, таких как почтовые индексы, где некоторые записи включают суффикс ZIP + 4, а другие нет, вы снова можете использовать строковую функцию LEFT для распознавания и сопоставления только основных почтовых индексов. Чтобы Excel распознавал близкие, но не одинаковые числовые значения, вы можете использовать функцию ОКРУГЛ, чтобы округлить близкие значения до одного и того же числа и сопоставить их.
- Лишние пробелы, такие как ввод двух пробелов между словами вместо одного, можно удалить с помощью функции ОБРЕЗАТЬ.
Шаг 4. Создайте столбцы для формулы сравнения
Так же, как вам нужно было создать столбцы для идентификаторов списка, вам нужно будет создать столбцы для формулы, которая будет выполнять сравнение за вас. Вам понадобится по одному столбцу для каждого списка.
Вы захотите пометить эти столбцы чем-то вроде "Отсутствует?"
Шаг 5. Введите формулу сравнения в каждую ячейку
Для формулы сравнения вы будете использовать функцию ПОИСКПОЗ, вложенную в другую функцию Excel, ISNA.
- Формула имеет вид «= ISNA (MATCH (G3, $ L $ 3: $ L $ 14, FALSE))», где ячейка столбца идентификаторов первого списка сравнивается с каждым из идентификаторов во втором списке, чтобы посмотрите, соответствует ли он одному из них. Если он не совпадает, запись отсутствует, и в этой ячейке будет отображаться слово «ИСТИНА». Если оно совпадает, значит, запись присутствует, и будет отображаться слово «ЛОЖЬ». (При вводе формулы не включайте заключительные кавычки.)
- Вы можете скопировать формулу в оставшиеся ячейки столбца так же, как вы скопировали формулу идентификатора ячейки. В этом случае изменяется только ссылка на ячейку для ячейки идентификатора, поскольку размещение знаков доллара перед ссылками на строки и столбцы для первой и последней ячеек в списке идентификаторов вторых ячеек делает их абсолютными ссылками.
- Вы можете скопировать формулу сравнения для первого списка в первую ячейку столбца второго списка. Затем вам нужно будет отредактировать ссылки на ячейки так, чтобы «G3» был заменен ссылкой на первую ячейку идентификатора второго списка, а «$ L $ 3: $ L $ 14» был заменен на первую и последнюю ячейку идентификатора в второй список. (Оставьте знаки доллара и двоеточие в покое.) Затем вы можете скопировать эту отредактированную формулу в оставшиеся ячейки в строке сравнения второго списка.
Шаг 6. При необходимости отсортируйте списки, чтобы упростить поиск несовпадающих значений
Если ваши списки большие, вам может потребоваться отсортировать их, чтобы объединить все несовпадающие значения. Инструкции в подшагах ниже преобразуют формулы в значения, чтобы избежать ошибок пересчета, а если ваши списки большие, это позволит избежать длительного времени пересчета.
- Перетащите указатель мыши на все ячейки в списке, чтобы выбрать его.
- Выберите Копировать в меню Правка в Excel 2003 или в группе Буфер обмена на ленте Главная в Excel 2007 или 2010.
- Выберите «Специальная вставка» в меню «Правка» в Excel 2003 или в раскрывающемся списке «Вставить» в группе «Буфер обмена» на главной ленте Excel 2007 или 2010s.
- Выберите «Значения» из списка «Вставить как» в диалоговом окне «Специальная вставка». Щелкните ОК, чтобы закрыть диалоговое окно.
- Выберите «Сортировка» в меню «Данные» в Excel 2003 или в группе «Сортировка и фильтр» на ленте «Данные» в Excel 2007 или 2010.
- Выберите «Строка заголовка» из списка «Мой диапазон данных» в диалоговом окне «Сортировка по», выберите «Отсутствует?» (или имя, которое вы фактически дали заголовку столбца сравнения) и нажмите ОК.
- Повторите эти шаги для другого списка.
Шаг 7. Сравните несоответствующие элементы визуально, чтобы увидеть, почему они не совпадают
Как отмечалось ранее, Excel предназначен для поиска точных совпадений данных, если вы не настроили его для поиска приблизительных. Несоответствие может быть таким же простым, как случайное перемещение букв или цифр. Это также может быть что-то, что требует независимой проверки, например, проверка того, нужно ли в первую очередь сообщать о перечисленных активах.
Метод 2 из 2: условное форматирование с помощью COUNTIF
Шаг 1. Скопируйте списки данных на один рабочий лист
Шаг 2. Решите, в каком списке вы хотите выделить совпадающие или несовпадающие записи
Если вы хотите выделить записи только в одном списке, вы, вероятно, захотите выделить записи, уникальные для этого списка; то есть записи, не совпадающие с записями в другом списке. Если вы хотите выделить записи в обоих списках, вам нужно выделить записи, которые соответствуют друг другу. Для целей этого примера мы предположим, что первый список занимает ячейки с G3 по G14, а второй список занимает ячейки с L3 по L14.
Шаг 3. Выберите элементы в списке, в которых вы хотите выделить уникальные или совпадающие элементы
Если вы хотите выделить совпадающие элементы в обоих списках, вам придется выбирать списки по одному и применять формулу сравнения (описанную в следующем шаге) к каждому списку.
Шаг 4. Примените соответствующую формулу сравнения
Для этого вам нужно будет открыть диалоговое окно «Условное форматирование» в вашей версии Excel. В Excel 2003 для этого нужно выбрать «Условное форматирование» в меню «Формат», а в Excel 2007 и 2010 - нажать кнопку «Условное форматирование» в группе «Стили» на ленте «Главная». Выберите тип правила «Формула» и введите формулу в поле «Редактировать описание правила».
- Если вы хотите выделить записи, уникальные для первого списка, формула будет «= СЧЁТЕСЛИ ($ L $ 3: $ L $ 14, G3 = 0)», с диапазоном ячеек второго списка, отображаемым как абсолютные значения и ссылкой к первой ячейке первого списка как относительное значение. (Не вводите закрывающие кавычки.)
- Если вы хотите выделить записи, уникальные для второго списка, формула будет выглядеть так: «= СЧЁТЕСЛИ ($ G $ 3: $ G $ 14, L3 = 0)», с диапазоном ячеек первого списка, отображаемым как абсолютные значения и ссылкой в первую ячейку второго списка как относительное значение. (Не вводите закрывающие кавычки.)
- Если вы хотите выделить записи в каждом списке, которые находятся в другом списке, вам понадобятся две формулы: одна для первого списка, а другая - для второго. Формула для первого списка: «= СЧЁТЕСЛИ ($ L $ 3: $ L $ 14, G3> 0)», а формула для второго списка - СЧЁТЕСЛИ ($ G $ 3: $ G $ 14, L3> 0) ». Как отмечалось ранее, вы выбираете первый список, чтобы применить его формулу, а затем выбираете второй список, чтобы применить его формулу.
- Примените любое форматирование, чтобы выделить помечаемые записи. Щелкните ОК, чтобы закрыть диалоговое окно.
Видео - с помощью этой службы некоторая информация может быть передана YouTube
подсказки
- Вместо использования ссылки на ячейку с методом условного форматирования COUNTIF вы можете ввести значение для поиска и пометить один или несколько списков для экземпляров этого значения.
- Чтобы упростить формы сравнения, вы можете создать для своего списка имена, такие как «Список1» и «Список2». Затем при написании формул эти имена списков могут заменять абсолютные диапазоны ячеек, использованные в приведенных выше примерах.
