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

Question

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

Как найти совпадающие и не совпадающие значения между двумя столбцами и отформатировать их следующим образом?

Входные данные:

| expected | match | forward | backward | actual |
|----------|-------|---------|----------|--------|
| a        |       |         |          | b      |
| b        |       |         |          | c      |
| c        |       |         |          | r      |
| d        |       |         |          | s      |
| e        |       |         |          |        |

Выход:

| expected | match | forward | backward | actual |
|----------|-------|---------|----------|--------|
| a        | b     | a       | r        | b      |
| b        | c     | d       | s        | c      |
| c        |       | e       |          | r      |
| d        |       |         |          | s      |
| e        |       |         |          |        |

forward - присутствует в expected но не в actual (SQL left outer join)

backward - присутствует в actual но не в expected (SQL right outer join)

expected это то, что я получаю от SQL запрос. У меня есть много сценариев, когда у меня нет actual столбец в RDBMS, поэтому мне пришлось бы использовать Excel для сравнения. Я могу сравнить это, как правило, используя VLOOKUP но это отнимает много времени, а также не дает желаемый формат. Я хотел бы решение, которое может быть сделано важно с форматом, как указано выше.

Я открыт для предложений.

0

microsoft-excel worksheet-function vba power-query

Источник

scientific_explorer 25 мар '19 в 06:23

2 ответа

Другие вопросы по тегам microsoft-excel worksheet-function vba power-query

Forward Ed 25 мар '19 в 11:14 2019-03-25 11:14 · Answer 1 · 2019-03-25 11:14

Предполагая, что ваша информация размещена, как указано выше, вы можете использовать следующие три формулы в C3, D3 и E3 соответственно:

C3
=IFERROR(INDEX(F:F,AGGREGATE(15,6,ROW($B$3:$B$7)/(COUNTIF($B$3:$B$7,$F$3:$F$7)>0),ROW(A1))),"")

D3
=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($B$3:$B$7)/(COUNTIF($C$3:$C$7,$B$3:$B$7)=0),ROW(A1))),"")

E3
=IFERROR(INDEX(F:F,AGGREGATE(15,6,ROW($F$3:$F$7)/((COUNTIF($C$3:$C$7,$F$3:$F$7)=0)*($F$3:$F$7<>"")),ROW(A1))),"")

скопируйте формулы в строку 7, и вы получите следующее:

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

Mike Honey 29 мар '19 в 08:07 2019-03-29 08:07 · Answer 2 · 2019-03-29 08:07

Для Power Query вашим ключевым преобразованием будет Merge Queries. Это похоже на соединение SQL и использует аналогичную терминологию. Первым шагом будет создание отдельных запросов для ожидаемых и фактических столбцов. Для списка совпадений я бы построил запрос с использованием Join Type = Inner. Для списка пересылки я бы построил еще один запрос, используя Join Type = Left Anti, а затем еще один запрос в обратном направлении, используя Right Anti.

Собрать все вместе в точности так, как вы это представляли, было бы немного неудобно (если это действительно необходимо), но я полагаю, что вы можете добавить индекс в каждый запрос и использовать объединение объединений (Join Type = Left Outer) в столбцах индекса, чтобы получить один результат из каждого запроса в каждой строке.