Вопрос или проблема
У меня есть 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: Условное Форматирование
Условное форматирование позволяет визуально выделить ячейки, которые соответствуют определенным критериям. В вашем случае мы можем использовать формулы для проверки наличия имен из первого листа на втором листе, соблюдать порядок и заметить отсутствующие значения.
Шаги:
-
Выделите диапазон данных на Sheet1, например,
B2:F4
. -
Перейдите в меню Главная → Стили → Условное форматирование → Новое правило → Использовать формулу для определения, какие ячейки форматировать.
-
Введите следующую формулу:
=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, и условное форматирование применяется.
- Объяснение формулы:
-
Выберите цвет заливки, чтобы выделить найденные несовпадения.
Метод 2: Использование Power Query
Если вам нужен более мощный инструмент для работы с данными, рекомендуем использовать Power Query, встроенный в Excel. Этот метод позволяет создать более сложные запросы и преобразования данных.
Шаги:
-
Импортируйте данные из обоих листов в Power Query:
- Выберите данные и откройте Power Query через меню Данные → Получить и трансформировать данные.
-
Примените следующий код в редакторе 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
.
- Объяснение кода:
-
После выполнения запроса Power Query покажет все неполные значения, которые должны быть выделены.
Итог
Оба предложенных метода являются эффективными для решения вашей задачи — нахождения несовпадающих имен между двумя различными листами Excel. Условное форматирование будет более наглядным и простым в использовании, в то время как Power Query предлагает более мощные инструменты для управления данными. Выбор метода зависит от ваших предпочтений и сложности набора данных.