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

[agitopro]

Решение одним словом: транспонирование (transpose). Дальше ищущий может гуглить и найти данную статью.

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

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

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

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




Из спорного преимущества: сохранится все оформление ячеек, что требуется не всегда. Но главный недостаток способа - все-таки довольно трудоемкий процесс. Если строк и столбцов больше 100? Сто раз вот так переносить данные построчно?

1. Используем формулу

Гораздо более изящное решение.

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

С помощью функций СТРОКА(ячейка) и СТОЛБЕЦ(ячейка) меняем порядок выдачи у функции АДРЕС - не (строка, столбец), а (столбец, строка).

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

Решение:

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

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

=INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)))

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




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

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

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

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

Даже формула - не совсем то, что надо. Мы преобразовываем данные туда-сюда, хотя нам всего-то требуется поработать с самой таблицей.

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

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