Excel трансп не работает

Обновлено: 07.07.2024

Вчера в марафоне 30 функций Excel за 30 дней мы считали количество столбцов в диапазоне, используя функцию COLUMNS (ЧИСЛСТОЛБ), а теперь пришло время для чего-то более востребованного.

13-й день марафона мы посвятим исследованию функции TRANSPOSE (ТРАНСП). При помощи этой функции Вы можете поворачивать свои данные, превращая вертикальные области в горизонтальные и наоборот. У Вас возникает такая необходимость? Сможете сделать это, используя специальную вставку? Смогут ли это сделать другие функции?

Итак, обратимся к информации и примерам по функции TRANSPOSE (ТРАНСП). Если у Вас есть дополнительные сведения или примеры, пожалуйста, делитесь ими в комментариях.

Функция 13: TRANSPOSE (ТРАНСП)

Функция TRANSPOSE (ТРАНСП) возвращает горизонтальный диапазон ячеек как вертикальный или наоборот.

Как можно использовать функцию TRANSPOSE (ТРАНСП)?

Функция TRANSPOSE (ТРАНСП) может изменять ориентацию данных, а также работать совместно с другими функциями:

Изменить горизонтальное расположение данных на вертикальное.
Показать лучший общий объём заработной платы за последние годы.

Чтобы изменить ориентацию данных, не создавая ссылок на исходные данные:

Используйте Paste Special (Специальная вставка) > Transpose (Транспонировать).

Синтаксис TRANSPOSE (ТРАНСП)

Функция TRANSPOSE (ТРАНСП) имеет следующий синтаксис:

array (массив) – это массив или диапазон ячеек, которые нужно транспонировать.

Ловушки TRANSPOSE (ТРАНСП)

Функция TRANSPOSE (ТРАНСП) должна быть введена как формула массива, нажатием Ctrl+Shift+Enter.
Диапазон, который получится в результате преобразования функцией TRANSPOSE (ТРАНСП), должен иметь то же количество строк и столбцов, сколько столбцов и строк соответственно имеет исходный диапазон.

Пример 1: Превращаем горизонтальные данные в вертикальные

Если на листе Excel данные расположены горизонтально, Вы можете применить функцию TRANSPOSE (ТРАНСП), чтобы преобразовать их в вертикальное положение, но уже в другом месте листа. Например, в итоговой таблице контрольных показателей вертикальное расположение будет более удобным. Используя функцию TRANSPOSE (ТРАНСП), Вы можете сослаться на исходные горизонтальные данные, не меняя их расположение.

Чтобы транспонировать горизонтальный диапазон 2х4 в вертикальный диапазон 4х2:

Выделите 8 ячеек, куда Вы хотите разместить полученный вертикальный диапазон. В нашем примере это будут ячейки B4:C7.
Введите следующую формулу и превратите её в формулу массива нажатием Ctrl+Shift+Enter.

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

Вместо TRANSPOSE (ТРАНСП), Вы можете использовать другую функцию для преобразования данных, например, INDEX (ИНДЕКС). Она не потребует ввода формулы массива, и Вам не придётся выделять все ячейки конечной области, при создании формулы.

Пример 2: Изменяем ориентацию без ссылок

Если Вы просто хотите изменить ориентацию Ваших данных, не сохраняя ссылку на изначальные данные, Вы можете использовать специальную вставку:

Выберите исходные данные и скопируйте их.
Выберите верхнюю левую ячейку области, куда необходимо поместить результат.
На вкладке Home (Главная) нажмите на выпадающее меню команды Paste (Вставить).
Выберите Transpose (Транспонировать).
Удалите исходные данные (по желанию).

Пример 3: Лучший общий объём заработной платы за прошедшие годы

Функция TRANSPOSE (ТРАНСП) может использоваться в сочетании с другими функциями, например, как в этой сногсшибательной формуле. Она была опубликована Харланом Гроувом в новостном блоке Excel в обсуждении вопроса о подсчёте лучшей общей суммы заработной платы за 5 прошедших лет (подряд!).

=MAX(MMULT(A8:J8, --(ABS(TRANSPOSE(COLUMN(A8:J8))-COLUMN(OFFSET(A8:J8,0,0,1,COLUMNS(A8:J8)-Number+1))-(Number-1)/2)<Number/2)))
=МАКС(МУМНОЖ(A8:J8; --(ABS(ТРАНСП(СТОЛБЕЦ(A8:J8))-СТОЛБЕЦ(СМЕЩ(A8:J8;0;0;1;ЧИСЛСТОЛБ(A8:J8)-Number+1))-(Number-1)/2)<Number/2)))

Как можно понять по фигурным скобкам в строке формул – это формула массива. Ячейка A5 названа Number и в этом примере число 4 введено, как значение для количества лет.

Формула проверяет диапазоны, чтобы увидеть достаточно ли в них последовательных столбцов. Результаты проверки (1 или 0) умножаются на значения ячеек, чтобы получить суммарный объём заработной платы.

Для проверки результата на рисунке ниже в строке под значениями зарплат показаны суммарные значения для каждой стартовой ячейки, при этом максимальное значение выделено жёлтым. Это более долгий путь к тому же результату, что предыдущая формула массива получает в одной ячейке!

Иногда требуется изменить направление, в котором располагаются ячейки. Это можно сделать путем копирования и вставки и применения команды "Транспонировать". Но в этом случае образуются повторяющиеся данные. Чтобы такого не происходило, можно вместо этого ввести формулу с функцией ТРАНСП. Например, на следующем изображении показано, как расположить горизонтально ячейки с A1 по B4 с помощью формулы =ТРАНСП(A1:B4).

Примечание: Если у вас есть текущая версия Microsoft 365, вы можете ввести формулу в левую верхнюю ячейку диапазона вывода, а затем нажать ввод, чтобы подтвердить формулу как формулу динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

Шаг 1. Выделите пустые ячейки

Сначала выделите пустые ячейки. Их число должно совпадать с числом исходных ячеек, но располагаться они должны в другом направлении. Например, имеется 8 ячеек, расположенных по вертикали:

Нам нужно выделить 8 ячеек по горизонтали:

Так будут располагаться новые ячейки после транспонирования.

Шаг 2. Введите =ТРАНСП(

Не снимая выделение с пустых ячеек, введите =ТРАНСП(

Лист Excel будет выглядеть так:

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

Шаг 3. Введите исходный диапазон ячеек

Теперь введите диапазон ячеек, которые нужно транспоннять. В этом примере мы хотим транспоннять ячейки с A1 по B4. Поэтому формула для этого примера будет такой: =ТРАНСП(A1:B4) — но не нажимайте ввод! Просто остановите ввод и перейдите к следующему шагу.

Лист Excel будет выглядеть так:

Шаг 4. Нажмите клавиши CTRL+SHIFT+ВВОД

Теперь нажмите клавиши CTRL+SHIFT+ВВОД. Зачем это нужно? Дело в том, что функция ТРАНСП используется только в формулах массивов, которые завершаются именно так. Если говорить кратко, формула массива — это формула, которая применяется сразу к нескольким ячейкам. Так как в шаге 1 вы выделили более одной ячейки, формула будет применена к нескольким ячейкам. Результат после нажатия клавиш CTRL+SHIFT+ВВОД будет выглядеть так:

Советы

Вводить диапазон вручную не обязательно. Введя =ТРАНСП(, вы можете выделить диапазон с помощью мыши. Простой щелкните первую ячейку диапазона и перетащите указатель к последней. Но не забывайте: по завершении нужно нажать клавиши CTRL+SHIFT+ВВОД, а не просто клавишу ВВОД.

Нужно также перенести форматирование текста и ячеек? Вы можете копировать ячейки, вставить их и применить команду "Транспонировать". Но помните, что при этом образуются повторяющиеся данные. При изменении исходных ячеек их копии не обновляются.

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

Технические подробности

Функция ТРАНСП возвращает вертикальный диапазон ячеек в виде горизонтального и наоборот. Функцию ТРАНСП необходимо вводить как формула массива в диапазон, содержащий столько же строк и столбцов, что и аргумент диапазон. Функция ТРАНСП используется для изменения ориентации массива или диапазона на листе с вертикальной на горизонтальную и наоборот.

Синтаксис

Аргументы функции ТРАНСП описаны ниже.

Массив. Обязательный аргумент. Массив (диапазон ячеек) на листе, который нужно транспонировать. Транспонирование массива заключается в том, что первая строка массива становится первым столбцом нового массива, вторая — вторым столбцом и т. д. Если вы не знаете, как ввести формулу массива, см. статью "Создание формулы массива".

Функция ТРАНСП в Excel используется для транспонирования (изменения направления отображения) ячеек из горизонтального расположения в вертикальное и наоборот. Функция ТРАНСП при транспонировании диапазона ячеек или таблицы создает копию данного диапазона или таблицы, при этом исходные данные остаются неизменными.

Примеры использования функции ТРАНСП в Excel

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

Выделяем диапазон ячеек G2:L4 и вводим формулу для решения (использовать как формулу массива CTRL+SHIFT+Enter):

Единственный аргумент функции – диапазон ячеек A2:C7, значение каждой из которых будет умножено на число, содержащееся в ячейке E4 (5).

В результате мы имеем динамически умножаемою транспонированную матрицу.

Как переворачивать таблицу в Excel вертикально и горизонтально

Пример 2. В Excel создана таблица зарплат работников на протяжении года. В шапке исходной таблицы отображаются месяцы, а число работников офиса – 6 человек. В связи с этим ширина таблицы значительно превышает ее высоту. Перевернуть таблицу так, чтобы она стала более удобной для работы с ней.

Сначала выделяем диапазон ячеек A9:G21. Ведь для транспонирования приведенной таблицы мы выделяем область шириной в 6 ячеек и высотой в 12 ячеек и используем следующую формулу массива (CTRL+SHIFT+Enter):

A2:M8 – диапазон ячеек исходной таблицы.

Таблицу такого вида можно распечатать на листе А4.

Переворот таблицы в Excel без использования функции ТРАНСП

Пример 3. Вернуть транспонированную таблицу из предыдущего примера без использования функции ТРАНСП.

Выделить исходную таблицу и скопировать все данные (Ctrl+С).
Установить курсор в ячейку, которая будет находиться в левом верхнем углу транспонированной таблицы, вызвать контекстное меню и выбрать пункт «Специальная вставка».
В открывшемся окне установить флажок напротив надписи «Транспонировать» и нажать кнопку «ОК».

В результате будет добавлена таблица:

Приведенный выше способ позволяет получить транспонированную таблицу всего в несколько простых действий. Однако формула ТРАНСП полезна в случаях, когда над данными в таблицах требуется выполнить какое-либо действие (например, умножение, деление, сложение матриц, добавление каких-либо проверочных условий и т. д.).

Особенности использования функции ТРАНСП в Excel

Функция имеет следующий синтаксис:

Единственным аргументом, который является обязательным для заполнения, является массив. Он характеризует определенный диапазон ячеек, над которым производится операция транспонирования.

Транспонирование данных в Excel – это задача, знакомая многим пользователям. Очень часто после создания сложной таблицы, становится очевидным, что эффективнее было бы развернуть её для более удобного анализа или построения диаграммы по имеющимся данным.

В этой статье Вы найдёте несколько способов преобразовать строки в столбцы (или столбцы в строки) в Excel. Эти решения работают во всех версиях Excel (2013, 2010, 2007 и более ранних), охватывают множество возможных сценариев, объясняют наиболее типичные ошибки и показывают хорошие примеры использования в Excel формул для транспонирования.

Преобразуем строки в столбцы в Excel при помощи Специальной вставки

Предположим, Вы создали таблицу на листе Excel, похожую на ту, что показана в верхней части рисунка, расположенного ниже. Названия стран распределены по столбцам, но список стран может оказаться очень длинным, поэтому нам будет гораздо удобнее преобразовать столбцы в строки, чтобы таблица уместилась на экране.

Выделите строки и столбцы, которые требуется поменять местами. Если Вы хотите развернуть всю таблицу целиком, т.е. все ячейки с данными на листе, нажмите Ctrl+Home, а затем Ctrl+Shift+End.
Скопируйте выделенные ячейки. Для этого на выбор:
- кликните по ним правой кнопкой мыши и выберите Copy (Копировать) в контекстном меню.
- нажмите Ctrl+C.
- нажмите кнопку Copy (Копировать) на вкладке Home (Главная) в разделе Clipboard (Буфер обмена).
Выделите первую ячейку диапазона, в который Вы собираетесь скопировать транспонированные данные.

Замечание: Важно выбрать ячейку, которая находится за пределами диапазона исходных данных, чтобы скопированная область и область вставки не пересеклись. Например, если исходные данные состоят из 4 столбцов и 10 строк, то транспонированная таблица будет состоять из 10 столбцов и 4 строк.

Вставьте скопированные данные при помощи команды Paste Special (Специальная вставка) > Transpose (Транспонировать). Вы можете открыть диалоговое окно Paste Special (Специальная вставка) тремя способами:
- На вкладке Home (Главная) в разделе Clipboard (Буфер обмена), нажмите маленькую чёрную стрелку под кнопкой Paste (Вставить) и выберите команду Transpose (Транспонировать).
- Кликните правой кнопкой мыши по ячейке, в которую нужно вставить данные, и в контекстном меню выберите Paste Special (Специальная вставка) > Transpose (Транспонировать).
- (наконец, самый быстрый способ) Нажмите Ctrl+Alt+V, выберите опцию Transpose (Транспонировать) и кликните ОК.

Замечание: Если Вы транспонируете ячейки, содержащие формулы, убедитесь, что во всех этих ячейках использованы абсолютные ссылки (со знаком $). Это необходимо, поскольку формулы также будут транспонированы, а ссылки на ячейки автоматически обновлены.

Как Вы только что увидели, опция Transpose (Транспонировать) в Excel позволяет преобразовать строки в столбцы (или столбцы в строки) буквально за несколько секунд. Уверен, что этот способ очень полезен, когда требуется транспонировать таблицу один раз. Пользуясь этим методом, Вы сохраняете исходные данные, и это ещё один аргумент в его пользу.

Однако, вышеописанный метод имеет два существенных недостатка, которые не позволяют считать его идеальным решением для транспонирования данных в Excel.

Данный инструмент не доступен при копировании / вставке полнофункциональной таблицы Excel в отличие от простого диапазона. В этом случае Вам придётся скопировать таблицу без заголовков, либо преобразовать её в диапазон, т.е. кликнуть правой кнопкой мыши по любой ячейке таблицы и в контекстном меню выбрать Table (Таблица) > Convert to Range (Преобразовать в диапазон).
Более серьёзное ограничение этого метода состоит в том, что он создаёт копию исходных данных, и, если они изменятся, Вам придётся повторять процесс и транспонировать таблицу снова. Никто не захочет тратить своё драгоценное время на то, чтобы преобразовывать одни и те же строки и столбцы по нескольку раз. Согласны?

Как транспонировать таблицу, ссылающуюся на исходные данные

Давайте посмотрим, как можно преобразовать строки в столбцы в Excel при помощи уже знакомой нам техники Копировать > Вставить, так, чтобы транспонированные данные ссылались на исходные данные. Главное преимущество такого подхода в том, что при изменении данных в исходной таблице, транспонированная таблица обновится и отразит эти изменения.

Скопируйте строки, которые Вы хотите преобразовать в столбцы (или столбцы, которые хотите преобразовать в строки).
Выберите пустую ячейку на этом же или другом листе.
Откройте диалоговое окно Paste Special (Специальная вставка), как было показано в предыдущем примере, и нажмите Paste Link (Вставить связь) в нижнем левом углу диалогового окна.Результат будет примерно таким:

Быстрое, спокойное и элегантное решение, не так ли? Единственный недостаток этого подхода состоит в том, что в процессе выполнения форматирование исходной таблицы будет потеряно, и Вам придётся восстанавливать его вручную (далее в этом уроке я покажу быстрый способ сделать это).

Транспонируем данные в Excel при помощи формул

Если Вы не ищите лёгких путей, то можете использовать формулы для преобразования столбцов в строки и наоборот.

Преобразуем в Excel строки в столбцы при помощи функции TRANSPOSE (ТРАНСП)

Как можно понять из её названия, функция TRANSPOSE (ТРАНСП) специально разработана для транспонирования данных в Excel. Если у Вас нет большого опыта работы с формулами в Excel, и в частности с функциями массива, то, пожалуйста, внимательно следите за тем, чтобы выполнять следующие шаги в точности как написано.

В этом примере мы будем преобразовывать таблицу, в которой перечислены штаты США с данными о населении:

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

Посчитайте ячейки, которые Вы хотите транспонировать. В нашем случае это 7 столбцов и 6 строк.
Выберите пустой диапазон ячеек. Так как функция TRANSPOSE (ТРАНСП) меняет вертикальное на горизонтальное измерение диапазона, значит нужно выбрать такое количество строк и столбцов, сколько столбцов и строк соответственно имеет Ваша исходная таблица. В нашем примере мы выбираем 6 столбцов и 7 строк.
Нажмите F2, чтобы перейти в режим редактирования.
Запишите функцию TRANSPOSE (ТРАНСП) и укажите в скобках диапазон данных, который нужно транспонировать. В нашем случае это будет выглядеть так:

Замечание: Всегда используйте абсолютные ссылки для работы с функцией TRANSPOSE (ТРАНСП).

Замечание: Обязательно нажмите Ctrl+Shift+Enter, поскольку это формула массива. Если для ввода формулы просто нажать Enter, то она работать не будет.

Готово! Наши данные транспонированы, 7 столбцов превратились в 6 столбцов, что и требовалось получить.

Преимущества функции TRANSPOSE (ТРАНСП)

Главное преимущество функции TRANSPOSE (ТРАНСП): преобразованная таблица сохраняет связь с источником данных, т.е. какие бы изменения в исходных данных Вы ни вносили, транспонированная таблица также будет изменяться.

Недостатки функции TRANSPOSE (ТРАНСП)

И в завершение, какой бы хорошей и простой ни была функция TRANSPOSE (ТРАНСП), ей не хватает гибкости, и поэтому она становится не лучшим решением во многих ситуациях.

Преобразовываем строку в столбец или столбец в строку при помощи функции INDIRECT (ДВССЫЛ)

Этот метод очень похож на предыдущий. Его главное преимущество перед функцией TRANSPOSE (ТРАНСП) состоит в том, что он оставляет возможность изменять данные в транспонированной таблице, при этом сохраняя связь с исходной таблицей.

В этом примере мы будем использовать небольшую таблицу, чтобы Вы могли сосредоточить свое внимание на процессе транспонирования, а не на данных в таблице. Мы будем использовать немного замысловатую комбинацию функций INDIRECT (ДВССЫЛ) и ADDRESS (АДРЕС).

Предположим, у Вас есть данные в 4 столбцах (A – D) и в 5 строках (1 – 5):

Если данные начинаются не в строке с номером 1 и не в столбце A, то потребуется немного более сложная формула:

Вот и всё! В Вашей новой таблице все столбцы преобразованы в строки. Однако, транспонированные ячейки выглядят очень плоско и скучно по сравнению с исходными данными.

Но не расстраивайтесь, это вовсе не проблема. Вот как можно легко восстановить исходное форматирование:

Скопируйте исходную таблицу.
Выделите транспонированную таблицу.
Кликните по выделенной области правой кнопкой мыши и в разделе Paste Options (Параметры вставки) выберите Formatting (Форматирование).

Подводя итог, метод с функцией INDIRECT (ДВССЫЛ) кажется более сложным решением задачи по преобразованию строк в столбцы, чем с функцией TRANSPOSE (ТРАНСП).

Преимущества: Вы можете спокойно редактировать любую ячейку (ячейки) в транспонированной таблице, при этом она будет оставаться связанной с таблицей-источником и будет автоматически обновляться каждый раз, когда Вы будете вносить изменения в исходные данные.

Недостатки: На самом деле, я вижу только один – форматирование исходных данных не сохраняется. Впрочем, Вы можете легко восстановить его, как это показано выше.

Теперь Вы понимаете, как работает функция INDIRECT (ДВССЫЛ), и, возможно, захотите более подробно разобраться, что на самом деле делает эта формула и почему мы использовали комбинацию с функцией ADDRESS (АДРЕС). Если технические детали Вас не интересуют, следующий раздел Вы можете пропустить.

Функции INDIRECT (ДВССЫЛ) и ADDRESS (АДРЕС) – синтаксис и логика

Функция INDIRECT (ДВССЫЛ) служит для создания непрямой ссылки на ячейку. Например, если Вы хотите поместить значение ячейки B1 в ячейку A8, то записываете в ячейку A8 следующую формулу:

Но реальная сила функции INDIRECT (ДВССЫЛ) в том, что она может превратить любую текстовую строку в ссылку, в том числе строку, которую Вы создаёте при помощи других функций и значений других ячеек. Именно это мы и собираемся сейчас сделать. Если Вы поймёте это, то всё остальное поймёте с лёгкостью 🙂

Думаю, Вы помните, что в формуле мы использовали ещё 3 функции – ADDRESS (АДРЕС), COLUMN (СТОЛБЕЦ) и ROW (СТРОКА).

Функция ADDRESS (АДРЕС) используется для того, чтобы получить адрес ячейки из номеров строки и столбца, заданных Вами. Запомните: сначала строка, потом столбец. Например, АДРЕС(8;1) возвратит $A$8.

Функция COLUMN (СТОЛБЕЦ) возвращает номер столбца из заданной ссылки на ячейку. Например, СТОЛБЕЦ(A3) возвратит значение 1, поскольку столбец A – это первый столбец. Как не трудно догадаться, функция ROW (СТРОКА) работает точно так же, только для строк.

А теперь позвольте напомнить всю формулу, которую мы использовали для преобразования строк в столбцы на листе Excel:

Как видите, в функции ADDRESS (АДРЕС) мы задаём сначала столбец, а затем строку, и в этом кроется весь фокус! Помните, что функция ADDRESS (АДРЕС) понимает первое число как номер строки, а второе число как номер столбца. Другими словами, функция ADDRESS (АДРЕС) берёт номер столбца, возвращённый функцией COLUMN (СТОЛБЕЦ), и превращает его в номер строки, затем берёт номер строки и превращает его в номер столбца, то есть меняет местами строки и столбцы.

Теперь, когда Вы знаете, что делает каждая функция, давайте сведём воедино логику всей формулы:

функции COLUMN (СТОЛБЕЦ) и ROW (СТРОКА) возвращают номера столбца и строки соответственно;
функция ADDRESS (АДРЕС) меняет местами строку и столбец;
функция INDIRECT (ДВССЫЛ) выводит перевернутые данные в другую ячейку на листе Excel.

Совсем не страшно, правда?

Транспонируем данные при помощи макроса VBA

Не составит труда написать скрипт, который будет транспонировать строки в столбцы, при условии, что Вы достаточно хорошо владеете VBA. Если нет, то можете найти множество готовых макросов в интернете. Но если Вы работаете с большими таблицами, помните, что метод Transpose в VBA ограничен 65536 элементами. Если Ваш массив выйдет за рамки этого ограничения, то все лишние данные будут молча потеряны.

Читайте также: