Как согласовать различия между двумя массивами с уникальной маркировкой?

Вопрос или проблема

У меня есть 2 листа в Excel с массивами имен, привязанными к идентификатору, но имена не в том же порядке и могут быть перемешаны. Я хотел бы выяснить, появляются ли эти имена на втором листе с тем же тегом или нет.

Я пытался использовать функции поиска/сопоставления, но это не сработало, интересно, мог бы кто-то дать какие-то советы по этому поводу? Предпочтительно с объяснением формулы, чтобы я мог узнать что-то новое! Заранее спасибо.

Вот примеры листов, о которых идет речь. т.е. Как мне отметить, что в ID456 Хэнк отсутствует на втором листе.

Лист1

ID Name1 Name2 Name3 Name4 Name5
123 Джон Дэвид Джейн Нельсон Дин
456 Одри Хейзел Хэнк
789 Том Дик Гарри

Лист2

ID Name1 Name2 Name3 Name4 Name5
123 Дин Дэвид Джон Нельсон Джейн
456 Хейзел Одри
789 Гарри Том Дик

Я пытался использовать функции поиска/сопоставления/xmatch, но не могу это сделать.

Условное форматирование: Двойное сопоставление

  • Выберите Sheet1!B2:F4.

  • Выберите Главная–>Стили–>Условное форматирование–>Новое правило–>Использовать формулу для определения, какие ячейки форматировать.

  • Введите следующую формулу:

    =AND(B2<>"",ISERROR(MATCH(B2,INDEX(Sheet2!$B$2:$F$4,MATCH($A2,Sheet2!$A$2:$A$4,0),),0)))
    
  • Выберите цвет заливки.

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

Скриншот обоих листов

Я бы предложил использовать Power Query и его встроенные инструменты для работы со списками (предполагая, что ваши входные таблицы называются “Table1” и “Table2”:

let
    // Загрузить Table1 и развернуть, чтобы создать один столбец значений
    Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    UnpivotedTable1 = Table.UnpivotOtherColumns(Source1, {}, "Column", "Value"),
    
    // Преобразовать развернутые значения из Table1 в список
    List1 = UnpivotedTable1[Value],

    // Загрузить Table2 и развернуть, чтобы создать один столбец значений
    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    UnpivotedTable2 = Table.UnpivotOtherColumns(Source2, {}, "Column", "Value"),
    
    // Преобразовать развернутые значения из Table2 в список
    List2 = UnpivotedTable2[Value],

    // Найти элементы в List1, которые отсутствуют в List2
    NonMatchingItems = List.Difference(List1, List2)

in 
    NonMatchingItems

Предполагая, что у вас есть Excel 365, вы также можете использовать следующую формулу:

=LET(
    Array1, TOROW(Table1),
    Array2, TOROW(Table2),

    DiffArray, FILTER(Array1, (ISNA(MATCH(Array1, Array2, 0))) * (Array1 <> 0)),

    DiffArray
)

Ответ или решение

Когда речь идет о сравнении данных из двух различных листов Excel, где имена могут быть расположены в произвольном порядке, задача становится сложной, однако вполне решаемой при помощи инструментария Excel. Рассмотрим два метода — используя условное форматирование и Power Query.

Метод 1: Условное Форматирование

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

Шаги:

  1. Выделите диапазон данных на Sheet1, например, B2:F4.

  2. Перейдите в меню Главная → Стили → Условное форматирование → Новое правило → Использовать формулу для определения, какие ячейки форматировать.

  3. Введите следующую формулу:

    =AND(B2<>"", ISERROR(MATCH(B2, INDEX(Sheet2!$B$2:$F$4, MATCH($A2, Sheet2!$A$2:$A$4, 0), ), 0)))
    • Объяснение формулы:
      • B2<>"": Проверяем, что ячейка не пустая.
      • MATCH(..., INDEX(...), 0): Находит соответствие в Sheet2 по ID, возвращает позиции ячеек, где имя худо-бедно может находиться.
      • ISERROR(...): Если совпадений нет, возвращает TRUE, и условное форматирование применяется.
  4. Выберите цвет заливки, чтобы выделить найденные несовпадения.

Метод 2: Использование Power Query

Если вам нужен более мощный инструмент для работы с данными, рекомендуем использовать Power Query, встроенный в Excel. Этот метод позволяет создать более сложные запросы и преобразования данных.

Шаги:

  1. Импортируйте данные из обоих листов в Power Query:

    • Выберите данные и откройте Power Query через меню Данные → Получить и трансформировать данные.
  2. Примените следующий код в редакторе Power Query:

    let
       // Загрузите Table1 и преобразуйте в один столбец значений
       Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
       UnpivotedTable1 = Table.UnpivotOtherColumns(Source1, {}, "Column", "Value"),
    
       // Загрузите Table2 и преобразуйте в один столбец значений
       Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
       UnpivotedTable2 = Table.UnpivotOtherColumns(Source2, {}, "Column", "Value"),
    
       // Находим элементы в List1, которых нет в List2
       NonMatchingItems = List.Difference(UnpivotedTable1[Value], UnpivotedTable2[Value])
    in 
       NonMatchingItems
    • Объяснение кода:
      • UnpivotOtherColumns: Преобразует ваши таблицы в формат с двумя колонками — названия и значения.
      • List.Difference: Находит элементы, которые присутствуют в List1, но отсутствуют в List2.
  3. После выполнения запроса Power Query покажет все неполные значения, которые должны быть выделены.

Итог

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

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

Капча загружается...