Реорганизация данных таблицы Excel

Question

У меня есть таблица, отформатированная так, как на прикрепленном изображении (Текущая таблица).

У меня вопрос, что один и тот же столбец содержит данные разного уровня (в данном случае Континент-Нация-Город). Обычно я вручную добавляю столбцы и, используя фильтры и простую формулу для копирования содержимого ячейки, я могу получить таблицу на втором изображении (Желаемая таблица).

Хотя это и работает для небольших объемов данных, когда речь идет о таблице с тысячами строк, это может занять довольно много времени.

Есть ли другой, более эффективный способ реорганизации таблицы из «Текущей таблицы» в «Желаемую таблицу»?

Текущая таблица:

Желаемый стол:

Answer 1

Это мой любимый прием в Excel, и вот как его реализовать.

Кажется, что шагов много, но они очень простые и обычно требуется около 10-20 секунд, чтобы сделать все это, когда вы знаете это. И вам определенно стоит изучить это на будущее, так как это нужно очень часто.

1. фильтр столбца "местоположение" только по оранжевому цвету

2. выберите все ячейки в новом столбце для континента (чтобы сделать это быстрее, выберите последнюю ячейку в новом столбце и нажмите Shift+ Control+ Up)

3. Главная -> Найти и выделить -> Перейти к спец. -> Только видимые ячейки -> ОК

4. Теперь, сохраняя активное выделение, начните вводить простую формулу:

   =[@[Location]]
   

   или просто:

   =B2
   

5. Нажмите Control+ Enter, чтобы скопировать формулу во все ранее выбранные ячейки. Теперь, поскольку, как я вижу, вы используете табличный диапазон, будьте осторожны, чтобы формула не была автоматически заполнена в других ячейках. Если это так, просто нажмите CRTL+Z один раз.

6. Выйти из фильтра.

7. Теперь выберите все ячейки в новом столбце «континент».

8. Главная -> Найти и выбрать -> Перейти к спец. -> Пробелы -> ОК

9. Нажмите =и затем Upи затем Control+ Enter. Это приведет к тому, что все элементы с пустыми ячейками в новом столбце «Континент» будут ссылаться на их материнский континент иерархии.

Теперь вы можете повторить те же действия для второго уровня иерархии в новой колонке "Нация", на этот раз отфильтровав зеленый цвет. И повторите это столько уровней иерархии, сколько у вас есть.

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

И наконец, только теперь вы можете снова отфильтровать строки оранжевого и зеленого цвета и удалить их. После этого переименуйте столбец "Location" в "City". И у вас должно получиться то, что называется "Flat table" и лучше всего подходит для дальнейшей аналитической работы с данными с PivotTalbes и т. д.