Excel: как транспонировать таблицу
Уважаемая редакция! Работая с Excel, мне часто приходится перестраивать таблицу, меняя строки и столбцы местами. Формулами это делать очень неудобно. Подскажите, как можно решить проблему?
В. Суворова, г. Харьков
Отвечает
Николай КАРПЕНКО , канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства
Операция, в которой строки и столбцы таблицы меняются местами, называется
транспонированием. В Excel такую операцию можно сделать, как минимум, двумя способами. К сожалению, вы не указали версию Excel, которую используете в своей работе, поэтому дать подробную инструкцию по работе с интерфейсом я не могу. Да это, скорее всего, и не нужно. Операция несложная, поэтому можно обойтись и без рисунков.Способ 1.
Транспонирование данных через специальную вставку:1) выделите блок таблицы, которую хотите транспонировать;
2) скопируйте данные в буфер обмена («
Ctrl+V» или «Ctrl+Ins»);3) поставьте указатель активной ячейки в начало блока, где должна находиться транспонированная таблица;
4) вызовите меню «
Правка → Специальная вставка» (в Excel 2007 функцию специальной вставки можно вызвать, нажав на маленький треугольник под иконкой «Вставка»). Появится окно параметров специальной вставки;5) в этом окне включите флажок «
Транспонировать»;6) нажмите «
ОК».На листе появится транспонированная таблица. Попробуйте изменить данные в исходной таблице. Обратите внимание, что содержимое транспонированной таблицы не изменилось.
Важно!
Транспонирование таблиц через функцию специальной вставки не устанавливает связь между источником данных и результатом. После специальной вставки обе таблицы будут автономны.В этом и заключается основной недостаток первого способа. Поэтому я предпочитаю пользоваться формулой-массивом. Вот как это сделать.
Способ 2.
Транспонирование данных через формулу-массив:1) выделите диапазон ячеек на рабочем листе для будущей
транспонированной таблицы;2) не снимая выделения (!), в первую ячейку диапазона запишите формулу «
=Трансп(блок)»;3) в качестве параметра «
блок» введите диапазон исходной таблицы. Указать диапазон можно, выделив его прямо на рабочем листе;4) после того как формула готова, нажмите «
Ctrl+Shift+Enter». Это важно, так как нам нужна не просто формула, а формула-массив.Все данные из первой таблицы будут автоматически отображаться в новой транспонированной таблице. Откорректируйте исходные данные. Все изменения появятся в транспонированной таблице.
Важно!
Транспонирование таблиц через функцию «=Трансп()» устанавливает связь между источником данных и результатом.В завершение темы хочу сделать пару замечаний по второму способу:
1. Вставить функцию «
=Трансп()» вы можете с помощью Мастера функций, выбирать адреса ячеек можно прямо по рабочему листу. Но есть одна тонкость. После того как в окне работы с Мастером формула готова, не нажимайте кнопку «ОК», иначе вы получите обычную формулу, а она для решения задачи не годится. Не закрывая окно Мастера, нажмите «Ctrl+Shift+Enter», и Excel внедрит на рабочий лист формулу-массив.2. Диапазон ячеек для транспонированной таблицы желательно выбрать с учетом размеров исходной таблицы. Например, если исходные данные занимали 4 строки и 3 колонки, то диапазон для транспонированной таблицы должен занимать 3 колонки и 4 строки. Если это требование не соблюдать, а диапазон указать «с запасом», ничего страшного не произойдет. Просто в лишних ячейках транспонированной таблицы вы увидите значения «
#Н/Д». Это означает, что для соответствующих ячеек функция «=Трансп()» не обнаружила данных.Важно!
Стереть значения «#Н/Д» обычным способом (например, клавишей «Del») не удастся. Excel запрещает удалять элементы формулы-массива.Чтобы устранить это ограничение, сделайте так:
1) выделите транспонированную таблицу;
2) скопируйте ее в буфер обмена («
Ctrl+V» или «Ctrl+Ins»);3) не снимая выделения, вызовите меню «
Правка → Специальная вставка» (в Excel 2007 это можно сделать, нажав на маленький треугольник под иконкой «/font>Вставка»);4) в окне параметров специальной вставки включите флажок «
Значения»;5) нажмите «
ОК».Теперь участок рабочего листа с формулой-массивом превратился в обычные значения. Вы сможете выделить ненужные ячейки и стереть их обычным способом (например, клавишей «
Del»).Вот и все. Надеюсь, что этот материал поможет решить проблему.
Жду ваших писем, вопросов, замечаний и предложений на
bk@id.factor.ua, nictomkar@rambler.ru или на форуме редакции www.bk.factor.ua/forum . Удачной работы!
