Как проверить данные с двух листов в Excel 2016?

У меня есть необработанные данные из запроса Excel, который дает мне подробную информацию о номерах счетов (поле 2). У меня также есть список номеров счетов, которые я хочу ИСКЛЮЧИТЬ из необработанных данных.

Первоначально я думал, что смогу исключить их в SQL с условием NOT IN. Но потом мне сказали, что нужно исключить около 20 000 учетных записей.

Итак, я подумал, что могу экспортировать необработанные данные в Excel на одном рабочем листе и добавить номера счетов для исключения на другом листе, а затем заставить Excel проверить эти номера счетов и дать комментарий.

Рабочий лист 1 (необработанные данные):

---------------------------------
|Field 1|Field 2|Field 3|Field 4|
---------------------------------
|   1234| A1234B|    XYZ| 258.00|
---------------------------------
|   2678| B1234C|    ABC| 457.25|
---------------------------------
|   5465| C1234D|    DEF| 652.47|
---------------------------------
|   4587| D1234E|    GHI| 458.36|
---------------------------------
|   3589| E1234F|    JKL| 685.47|
---------------------------------

Рабочий лист 2 (исключаемые аккаунты):

---------
|Field 2|
---------
| A1234B|
---------
| J1234L|
---------
| K1234Z|
---------
| D1234E|
---------
| L1234M|
---------

Предполагаемый результат:

------------------------------------------
|Field 1|Field 2|Field 3|Field 4|Result  |
------------------------------------------
|   1234| A1234B|    XYZ| 258.00|Excluded|
------------------------------------------
|   2678| B1234C|    ABC| 457.25|        |
------------------------------------------
|   5465| C1234D|    DEF| 652.47|        |
------------------------------------------
|   4587| D1234E|    GHI| 458.36|Excluded|
------------------------------------------
|   3589| E1234F|    JKL| 685.47|        |
------------------------------------------

Первоначально я начал с сортировки номеров счетов на обоих листах, а затем запустил формулу:

=IFERROR(VLOOKUP(B2,ExcludedAccounts,2,FALSE), "Excluded")

Но потом я понял, что ищу только точное совпадение для каждой ячейки. Это будет неправильно.

Вопрос.
Какая формула используется для проверки сходства двух столбцов из разных рабочих листов во всем столбце?

Когда у меня есть флаг «Исключено» в другом столбце, я могу выделить ячейку с условным форматированием, а затем разделить эти учетные записи.


excel vlookup excel-match
person user1777929    schedule 15.12.2017    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Недостаточно представителей, чтобы оставить комментарий. Формула OP подходит, если «ExcludedRange» охватывает два столбца (поскольку он возвращает то, что находится во втором столбце), а второй столбец содержит только пробелы.

Я не уверен, что имеется в виду под нижеизложенным - не могли бы вы объяснить, в чем сходство...

...разные листы для сходства по всему столбцу

Это еще один способ сделать это с одним столбцом для исключенного диапазона.

=IF(ISERROR(VLOOKUP(A2,ExcludedAccounts,1,0)),"Excluded","")
person Kwakkers    schedule 16.12.2017
comment
Будьте осторожны с функцией ВПР, так как она требует от вас сортировки данных, что является дополнительным шагом, который вы должны обязательно выполнить. Я лично не использую его сам, но я согласен, что ответ @Kwakkers тоже должен работать. - person TotsieMae; 16.12.2017
comment
Привет, сортировка требуется только тогда, когда вы ищете приблизительное (параметр 4 = TRUE) совпадение. Мой пример — точное совпадение (параметр 4 = FALSE), поэтому сортировка не имеет значения. Ваше здоровье - person Kwakkers; 16.12.2017

arrow_upward
1
arrow_downward

Я думаю, что формула, которую вы на самом деле ищете,

=IF(ISNUMBER(MATCH(B2,ExcludedAccounts,0)),"Excluded","")

Когда я использовал вашу формулу, все, что я получил, были ответами «Исключено» для всего, потому что она возвращала ошибку (возможно, это было вызвано отсутствием информации в вашем вопросе, но я не знаю). РЕДАКТИРОВАТЬ: Как указано в другом ответе, который был отправлен, да, исходная формула стремилась вернуть значение из второго столбца, что означало бы, что ExcludedAccounts охватывает как минимум два столбца, а не предполагаемый один.

Есть несколько способов обработки вашего запроса. Однако моя приведенная выше формула ищет точное совпадение значения в B2 с этими значениями в именованном диапазоне ExcludedAccounts (который, как я предположил, равен Sheet2!A2:A6). Если MATCH найдет его, будет возвращено число (это число представляет строку в ExcludedAccounts, где было найдено значение из B2). Обернув это в функцию ISNUMBER и поместив в оператор IF, мы просим Excel предоставить нам результат «Исключено», если исключенная учетная запись найдена в списке, и результат «», если это не так. в списке ExcludedAccounts.

person TotsieMae    schedule 16.12.2017