Excel: Добавить таблицу к другой, где столбцы одинаковы, но строки отличаются

Question

Я часами пытался присоединить одну таблицу к другой в Excel. Я перепробовал 1000 вариантов VLOOKUP, но это не сработало. Столбцы (метрики) одинаковы для всех моих таблиц, но строки отличаются (выпали члены). Я хочу присоединить новые столбцы к старой таблице и получить широкую таблицу для всех лет.

Таблица 1 (2017)

|member|revenue|sales|profit|
|   1  |  10   |  20 |  10  |
|   2  |  10   |  20 |  10  |
|   3  |  10   |  20 |  10  |
|   4  |  10   |  20 |  10  |
|   5  |  10   |  20 |  10  |
|   6  |  10   |  20 |  10  |
|   7  |  10   |  20 |  10  |

Таблица 2 (2018 г., некоторые участники выбыли)

|member|revenue|sales|profit|
|   1  |  40   |  60 |  80  |
|   3  |  40   |  60 |  80  |
|   4  |  40   |  60 |  80  |
|   6  |  40   |  60 |  80  |
|   7  |  40   |  60 |  80  |

Результат: Добавить все столбцы Таблицы 2 в Таблицу 1.

2017                         2018
|member|revenue|sales|profit|revenue|sales|profit|
|   1  |  10   |  20 |  10  |  40   |  60 |  80  |
|   2  |  10   |  20 |  10  |  NA   |  NA |  NA  |
|   3  |  10   |  20 |  10  |  40   |  60 |  80  |
|   4  |  10   |  20 |  10  |  40   |  60 |  80  |
|   5  |  10   |  20 |  10  |  NA   |  NA |  NA  |
|   6  |  10   |  20 |  10  |  40   |  60 |  80  |
|   7  |  10   |  20 |  10  |  40   |  60 |  80  |

Answer 1

Примечание.

Напишите эту формулу массива в ячейке, E218заполните ее справа, затем нажмите F2и закончите с помощью Ctrl+Shift+Enter. Затем заполните ее, чтобы получить результаты.

{=IFERROR(VLOOKUP(A218,$A$210:$D$215,{2,3,4},FALSE),"No Match")}

Как это работает:

• {2,3,4}читает столбцы 2-4 в порядкеКонстанты массива, которые часто используются в формулах массива для одновременной обработки нескольких значений, а не одного значения.
• При необходимости измените ссылки на ячейки в формуле.
• Вы можете заменить No Matchна blanks.

Answer 2

Решение, предложенное @Rajesh S, должно работать. Сначала вам придется выбрать все три подходящие ячейки в строке вместе, а затем ввести формулу массива в строке формул. Таким образом, {2,3,4} из формулы массива распределятся по 3 столбцам в горизонтальном массиве.

Если вы не уверены в формуле массива и у вас есть только две таблицы, вы можете просто манипулировать СТОЛБЕЦ()-СТОЛБЕЦ(фиксированная ссылка на два столбца позади) в VLOOKUP, чтобы сгенерировать 2,3,4 только один раз, и вы сможете объединить две таблицы.

Если у вас возникнет необходимость разместить больше таблиц одну под другой, например, в будущем вы даже сможете добавить 2019, 2020 и т. д. одну под другой, то вам также может понравиться следующее решение.

В этом примере база Table1 находится в $A$3:$D$9, Table2 — в $A$11:$D$15 и Table3 — в $A$17:$D$21.

Используйте первую строку в качестве вспомогательной строки и укажите в ней размеры ячейки 1: ячейки 2 соответствующих таблиц точно так, как формула Excel понимает ссылку на ячейку, например, $A$11:$D$15.

Теперь вставьте следующую формулу в ячейку E3 и перетащите ее вниз и ниже до нужных ячеек.

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

Манипуляция с номерами столбцов для генерации последовательности 2,3,4 для ВПР будет работать только в том случае, если вы начнете формулу в столбце E. Если вы начнете с любого другого столбца, потребуется изменить формулу для корректной повторной генерации последовательности 2,3,4.

=IFERROR(VLOOKUP($A3,INDIRECT(E$1),IF(MOD(COLUMN(),3)=0,3,IF(MOD(COLUMN(),3)=1,4,2)),FALSE),"NA")