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

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

Задача

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

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

Теперь необходимо оценить результаты инвентаризации: если все в порядке, то списки №1 и №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. Для наглядности можно использовать Условное форматирование.

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (2 оценок)