Перенос данных в Excel — знакомая задача для многих пользователей. Очень часто после создания сложной таблицы становится очевидным, что эффективнее было бы расширить ее для более удобного анализа или построить диаграмму из имеющихся данных.
В этой статье будет показано несколько способов преобразования строк в столбцы (или столбцов в строки) в Excel. Эти решения работают во всех версиях Excel (2013, 2010, 2007 и ранее), охватывают множество возможных сценариев, объясняют типичные ошибки и показывают хорошие примеры использования формул транспонирования в Excel.
Предположим, вы создали электронную таблицу Excel, аналогичную той, которая показана в верхней части рисунка ниже. Названия стран расположены в столбцах, но список стран может быть очень длинным, поэтому нам будет намного удобнее преобразовать столбцы в строки, чтобы таблица умещалась на экране.
Примечание. Важно выбрать ячейку за пределами диапазона исходных данных, чтобы скопированная область и вставленная область не перекрывались. Например, если исходные данные состоят из 4 столбцов и 10 строк, транспонированная таблица будет состоять из 10 столбцов и 4 строк.
Примечание. Если вы перемещаете ячейки, содержащие формулы, убедитесь, что все эти ячейки используют абсолютные ссылки (со знаком $). Это необходимо, потому что формулы также будут транспонированы, а ссылки на ячейки будут обновляться автоматически.
Как вы только что видели, параметр «Транспонировать» в Excel позволяет преобразовывать строки в столбцы (или столбцы в строки) за секунды. Я уверен, что этот метод очень полезен, когда вам нужно один раз переставить таблицу. Используя этот метод, вы сохраняете исходные данные, и это еще один аргумент в его пользу.
Однако у описанного выше метода есть два существенных недостатка, которые не позволяют считать его идеальным решением для транспонирования данных в Excel.
Давайте посмотрим, как вы можете преобразовать строки в столбцы в Excel, используя уже знакомую технику Копировать> Вставить, чтобы транспонированные данные ссылались на исходные данные. Основное преимущество этого подхода заключается в том, что при изменении данных в исходной таблице транспонированная таблица обновляется, чтобы отразить эти изменения.
Результат будет примерно таким:
Это превратит вашу электронную таблицу во что-то немного пугающее (см. Изображение ниже), но не волнуйтесь, осталось всего 2 шага, и вы получите желаемый результат.
Быстрое, спокойное и элегантное решение, правда? Единственным недостатком этого подхода является то, что во время выполнения форматирование исходной таблицы будет потеряно, и вам придется восстанавливать его вручную (позже в этом уроке я покажу вам быстрый способ сделать это).
Если вы не ищете простых способов, вы можете использовать формулы для преобразования столбцов в строки и наоборот.
Как следует из названия, функция TRANSPOSE специально разработана для переноса данных в Excel. Если вы не очень разбираетесь в формулах в Excel и особенно в функциях массивов, будьте осторожны, чтобы выполнить эти шаги точно так, как написано.
В этом примере мы преобразуем таблицу, в которой перечислены штаты США с данными о населении:
Во-первых, вам нужно определить точное количество строк и столбцов в таблице. В нашем примере это 7 столбцов и 6 строк, включая заголовки. Давайте запомним эти числа и начнем преобразовывать строки в столбцы.
= ДОСТАВКА ($ A $ 1: $ G $ 6)
= ДОСТАВКА ($ A $ 1: $ G $ 6)
Примечание: всегда используйте абсолютные ссылки при работе с функцией TRANSPOSE).
Примечание. Убедитесь, что вы нажали Ctrl + Shift + Enter, так как это формула массива. Если вы просто нажмете Enter, чтобы ввести формулу, она не сработает.
Готовый! Наши данные транспонированы, 7 столбцов превратились в 6 столбцов, чего мы и хотели добиться.
Основное преимущество функции TRANSPOSE заключается в том, что преобразованная таблица остается связанной с источником данных, т.е любые изменения, которые вы вносите в исходные данные, также изменят транспонированную таблицу.
Наконец, такая же хорошая и простая, как функция TRANSPOSE, ей не хватает гибкости, и поэтому она не является лучшим решением во многих ситуациях.
Этот метод очень похож на предыдущий. Его главное преимущество перед функцией TRANSPOSE состоит в том, что он оставляет возможность изменять данные в транспонированной таблице, сохраняя связь с исходной таблицей.
В этом примере мы будем использовать небольшую таблицу, чтобы вы могли сосредоточиться на процессе транспонирования, а не на данных в таблице. Мы будем использовать слегка запутанную комбинацию функций КОСВЕННЫЙ и АДРЕС).
Предположим, у нас есть данные в 4 столбцах (A — D) и 5 строках (1-5):
= КОСВЕННЫЙ (АДРЕС (КОЛОНКА (A1); СТРОКА (A1)))
= КОСВЕННЫЙ (АДРЕС (КОЛОНКА (A1); СТРОКА (A1)))
Если данные не начинаются в строке 1 или столбце A, необходима немного более сложная формула:
= КОСВЕННО (АДРЕС (КОЛОНКА (A1) -СТОЛБЕЦ ($ A $ 1) + СТРОКА ($ A $ 1), СТРОКА (A1) -СТРОКА ($ A $ 1) + КОЛОНКА ($ A $ 1)))
= КОСВЕННЫЙ (АДРЕС (СТОЛБЕЦ (A1) -СТОЛБЕЦ ($ A $ 1) + СТРОКА ($ A $ 1), СТРОКА (A1) -СТРОКА ($ A $ 1) + СТОЛБЕЦ ($ A $ 1)))
Где A1 — верхняя левая ячейка исходной таблицы. Также обратите внимание на использование абсолютных и относительных ссылок.
это все! В вашей новой таблице все столбцы преобразованы в строки. Однако транспонированные ячейки выглядят очень плоскими и тусклыми по сравнению с исходными данными.
Но не расстраивайтесь, это вовсе не проблема. Вот как легко восстановить исходное форматирование:
Таким образом, метод с функцией ДВССЫЛ кажется более сложным решением проблемы преобразования строк в столбцы, чем функция ТРАНСПОРТИРОВКА).
Преимущества: вы можете безопасно редактировать любую ячейку в транспонированной таблице, при этом она останется связанной с исходной таблицей и будет автоматически обновляться всякий раз, когда вы вносите изменения в исходные данные.
Недостатки: На самом деле я вижу только один — не сохраняется форматирование исходных данных. Однако вы можете легко восстановить его, как показано выше.
Теперь вы понимаете, как работает функция КОСВЕННЫЙ, и, возможно, захотите более подробно понять, что на самом деле делает эта формула и почему мы использовали комбинацию с функцией АДРЕС. Если вас не интересуют технические подробности, вы можете пропустить следующий раздел.
Функция ДВССЫЛ используется для создания косвенной ссылки на ячейку. Например, если вы хотите вставить значение ячейки B1 в ячейку A8, запишите следующую формулу в ячейку A8:
= КОСВЕННО («B1»)
= КОСВЕННО («B1»)
Но реальная сила функции ДВССЫЛ в том, что она может преобразовать любую текстовую строку в ссылку, включая строку, созданную с помощью других функций и других значений ячеек. Вот чем мы сейчас займемся. Если вы это поймете, вы легко поймете все остальное
Думаю, вы помните, что в формуле мы использовали еще 3 функции: АДРЕС, СТОЛБЕЦ и СТРОКА).
Функция АДРЕС используется для получения адреса ячейки из указанных номеров строки и столбца. Помните: сначала строка, затем столбец. Например, ADDRESS (8; 1) вернет $ A $ 8.
Функция COLUMN возвращает номер столбца из указанной ссылки на ячейку. Например, COLUMN (A3) вернет 1, потому что столбец A является первым столбцом. Как вы понимаете, функция СТРОКА работает точно так же, только для строк.
Теперь позвольте мне напомнить вам всю формулу, которую мы использовали для преобразования строк в столбцы на листе Excel:
= КОСВЕННЫЙ (АДРЕС (КОЛОНКА (A1); СТРОКА (A1)))
= КОСВЕННЫЙ (АДРЕС (КОЛОНКА (A1); СТРОКА (A1)))
Как видите, в функции ADDRESS мы сначала устанавливаем столбец, а затем строку, и в этом весь фокус! Помните, что функция АДРЕС интерпретирует первое число как номер строки, а второе число как номер столбца. Другими словами, функция ADDRESS принимает номер столбца, возвращенный функцией COLUMN, и преобразует его в номер строки, затем берет номер строки и преобразует его в номер столбца, то есть меняет местами строки и столбцы.
Теперь, когда вы знаете, что делает каждая функция, давайте соберем логику всей формулы:
Совсем не страшно, правда?
Написать скрипт, который переводит строки в столбцы, несложно, если вы хорошо разбираетесь в VBA. Если нет, то в Интернете можно найти множество готовых макросов. Но если вы работаете с большими таблицами, помните, что метод транспонирования VBA ограничен 65536 элементами. Если ваш массив превышает этот предел, все ненужные данные будут автоматически потеряны.
