Excel: перенос данных из столбцов в строки и наоборот

Есть обычная таблица, как можно передать все данные, чтобы столбцы стали строками, а строки стали столбцами? Я знаю три способа решения проблемы, каждый из которых по-своему удобен.

Специальная вставка

Выберите столбец или строку, скопируйте. В новом месте или на новом листе, куда будет помещена транспонированная таблица, щелкните правой кнопкой мыши «Специальная вставка».

Установите флажок, чтобы транспонировать. При использовании такой перестановки все формулы, все связи между таблицами «улетают». Следовательно, нам, скорее всего, также нужно передавать только значения (или значения и форматы) чисел.

Из спорных плюсов: сохранится вся конструкция ячеек, что требуется далеко не всегда. Но главный недостаток этого метода — довольно трудоемкий процесс. Что делать, если строк и столбцов больше 100? Передавать данные сто раз построчно?

Воспользуемся формулой

Намного более элегантное решение.

Функция АДРЕС (номер_строки; номер_столбца) предоставляет ссылку (адрес) ячейки с двумя числами, где первое — номер строки, второе — номер столбца. Эта запись = ADDRESS (1; 1) вернет нам ссылку на ячейку A1.

Используя функции ROW (ячейка) и COLUMN (ячейка), мы меняем порядок вывода функции ADDRESS — не (строка, столбец), а (столбец, строка).

В текущем виде формула = АДРЕС (СТОЛБЕЦ (A1); СТРОКА (A1)) вернет текст $ A $ 1, вам нужно преобразовать результат в ссылку, заключив все выражение в функцию ДВССЫЛ (link_in_text_view).

Решение:

= КОСВЕННЫЙ (АДРЕС (КОЛОНКА (A1); СТРОКА (A1)))

В английском Excel:

= КОСВЕННЫЙ (АДРЕС (КОЛОНКА (A1); СТРОКА (A1)))

Применяя формулу для ячейки A9 (в примере на рисунке), растягиваем ее до остальных. Результат:

И сразу видно 2 небольших недостатка этого метода:

• Пустые ячейки заполняются нулями, исправляются вручную;
• Формат ячейки — числовой, поэтому даты также нужно будет преобразовать вручную.

Однако для больших таблиц эта формула определенно быстрее, чем Специальная вставка.

Сводная таблица

Формула тоже не совсем то, что вам нужно. Мы трансформируем данные туда и обратно, даже если нам нужно работать только с самой таблицей.

Наиболее рациональное решение — это сводная таблица. Нам нужно исправить исходные данные, у каждого столбца должен быть заголовок!

Выделите таблицу, выберите в меню Вставить — Сводная таблица. Указываем, куда вставить новую таблицу (она может быть на новом листе или где-то на текущем), график — да / нет. OK. В настройках меняем места расположения блоков названий строк и названий столбцов. Результат: