Сравнение реальных списков в EXCEL

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

Задача

После инвентаризации драгоценных металлов в лаборатории был составлен список металлов с указанием их массы ( Список №1 ). По определению этот список не должен содержать повторяющихся значений.

Металлы в лабораторию поступали в течение нескольких лет по различным накладным. Движение материалов, как водится, велось в текстовом формате (WORD), причем разными людьми. В результате свода данных из накладных, получился длинный перечень повторяющихся значений (один металл мог приходить несколько раз) с соответствующими числовыми значениями (массами). Это Список №2 (см. Файл примера ).

Теперь необходимо оценить результаты инвентаризации: если все в порядке, то списки №1 и №2 должны совпадать и по наименованиям металлов, и по массам каждого металла.

Решение

Чтобы сравнить списки сделаем следующее:

• сформируем список уникальных значений из Списка №2 (см. статью Отбор уникальных значений ) и найдем сумму масс каждого металла;
• сформируем список уникальных значений из обоих списков (см. статьи Отбор уникальных значений из двух диапазонов и Сравнение 2-х списков );
• для каждого металла выведем массы металлов из обоих списков;
• сравним массы из двух списков для каждого из металлов.

В результате должны получить следующую таблицу:

Вроде все просто, но из-за того, что элементы Списка №2 могут содержать ошибки, то уже на первом шаге можем получить проблемы.

Как видно из рисунка выше, в списке уникальных значений, отобранных из Списка №2 , присутствуют повторы, а вместо значений масс отображается ошибка.

Причина - наличие опечаток, которые приводят к появлению похожих значений. Разберем типичные ошибки подробнее.

Типичные ошибки в списках

Лишние пробелы Текстовые значения «медь» и «медь » (на конце пробел), в EXCEL считаются совершенно справедливо разными значениями. Для удаления лишних пробелов существует функция СЖПРОБЕЛЫ() . Для проверки на лишние пробелы можно написать следующую формулу:

=ДЛСТР(СЖПРОБЕЛЫ(A3))=ДЛСТР(A3)

Если в значении есть пробел, например, в самом конце слова, то формула вернет ЛОЖЬ.

Английские буквы в русском слове Часто слово может содержать латиницу вперемешку с кириллицей. Обнаружить латинские буквы визуально бывает просто невозможно (буквы с, о, a есть в обоих алфавитах). Зато это можно сделать с помощью формулы массива : =СУММ(ЕСЛИ((КОДСИМВ(ПСТР($A3;СТРОКА(ДВССЫЛ("A1:A"&ДЛСТР($A3)));1))>=65)*(КОДСИМВ(ПСТР($A3; СТРОКА(ДВССЫЛ("A1:A"&ДЛСТР($A3)));1))<=122);1;0))=0

Подробности работы этой формулы в статье Определяем, есть ли в слове латиница/ заглавные/ строчные/ числа?

Опечатки в текстовых значениях Единого подхода для нахождения опечаток типа «паладий» (пропущена одна буква л), естественно, нет. Можно посоветовать скопировать список в WORD, пусть он подчеркнет ошибки. А можно внимательно посмотреть список уникальных значений: наверняка слова с опечатками расположены где-то рядом (если, конечно не опечатались в первой букве).

Перепутан разделитель целой и дробной части Довольно частая ошибка: пишут вместо 10,5 значение 10.5 (на американский манер). Избавиться от этой опечатки можно с помощью функции ПОДСТАВИТЬ() .

Для проверки на неправильный разделитель можно написать следующую формулу: =НЕ(ЕОШ(ПОДСТАВИТЬ(B3;".";",")-B3))

Визуально неправильные значения также легко обнаружить: текстовые значения выравниваются по левому краю, а числовые – по правому (если в формате ячеек установлено выравнивание по горизонтали «по значению»).

Неправильный формат значения Если числовое значение находится в ячейке с текстовым форматом, то, например, функция СУММ() его проигнорирует. Чтобы избежать этих ошибок читайте статью Преобразование чисел из текстового формата в числовой . Для проверки можно написать следующую формулу: =ЕЧИСЛО(B3)

Подобные ошибки часто появляются при работе с датами. Значение даты 05.11.2009 запросто может быть сохранено как 2009-ноября-5 или как 2009,11,5: см. статью Является ли значение датой?

Сравнение списков

После исправления ошибок можно приступать к сравнению списков. Значения масс для каждого металла из исправленного Списка №2 (список находится в файле примера на листе Исправленный в диапазоне A2:A53 ) просуммируем с помощью формулы: =СУММЕСЛИ(исправленный!$A$2:$A$53;E3;исправленный!$B$2:$B$53)

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

Сравнение масс можно производить элементарной формулой =F3=G3 . Для наглядности можно использовать Условное форматирование .