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

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в СУРБД, поэтому мне пришлось бы использовать Excel для сравнения. Я могу сравнить его обычно с помощью, VLOOKUPно это занимает много времени, и также это не дает нужного мне формата. Я хотел бы решение, которое можно сделатьважнов формате, указанном выше.

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

Answer 1

Предполагая, что ваша информация представлена ​​так, как указано выше, вы можете использовать следующие три формулы в ячейках 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функции. Ограничьте ее близостью к вашему набору данных. В противном случае вы можете завалить свой компьютер огромным количеством вычислений на пустых ячейках.

Answer 2

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

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