Как вставить ссылку и транспонировать функции в Excel

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

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

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

Проблема вставки

Для целей этой статьи предположим, что вы хотите перенести столбцы на один и тот же лист. Так что же ты делаешь? Выделите столбцы, нажмите Ctrl + C (Cmd + C на Mac) и выберите место вставки. Затем нажмите «Параметры вставки», выберите «Специальная вставка» и установите флажок перед «Транспонировать».

Но как только вы установите флажок, опция «Вставить ссылку» станет неактивной. К счастью, есть некоторые формулы и приемы, которые помогут вам обойти эту проблему. Давайте посмотрим на них.

ТРАНСП — Формула массива

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

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

Шаг 1

Скопируйте ячейки и щелкните верхнюю левую ячейку в области, в которую вы хотите вставить ячейки. Нажмите Ctrl + Alt + V, чтобы открыть окно «Специальная вставка». Вы также можете сделать это с панели инструментов Excel.

Шаг 2

Открыв окно, установите флажок «Форматы» в разделе «Вставить», выберите «Транспонировать» в правом нижнем углу и нажмите «ОК». Это действие транспонирует только форматирование, а не значения, и есть две причины, по которым вам нужно это сделать. Во-первых, вы узнаете диапазон транспонированных ячеек. Во-вторых, вы сохраняете исходный формат ячеек.

Шаг 3

Необходимо выделить всю область вставки, и вы можете сделать это после вставки форматов. Теперь введите =ТРАНСПОЗ («Исходный диапазон») и нажмите Ctrl + Shift + Enter.

Примечание: Важно нажать Enter вместе с Ctrl и Shift. В противном случае программа не распознает команду правильно и автоматически сформирует фигурные скобки.

Связывание и транспонирование – ручной метод

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

Шаг 1

Выделите ячейки и скопируйте/вставьте их, используя опцию «Специальная вставка». На этот раз вы не ставите галочку напротив «Транспонировать» и оставляете параметры в разделе «Вставить» по умолчанию.

Шаг 2

Нажмите кнопку «Вставить ссылку» в левом нижнем углу, и ваши данные будут вставлены в виде ссылок.

Шаг 3

Вот и самое сложное. Вам нужно вручную перетащить ячейки в новую область. При этом нужно быть осторожным при обмене строк и столбцов.

Формула СМЕЩЕНИЯ

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

Шаг 1

Вам нужно подготовить цифры слева и сверху. Например, если имеется три строки, вы будете использовать значения 0–2, а если столбцов два, вы будете использовать 0–1. Метод представляет собой общее количество строк и столбцов минус 1.

Шаг 2

Далее вам нужно найти и определить базовую ячейку. Эта ячейка должна оставаться неизменной при копировании/вставке, поэтому для ее обозначения используются специальные символы. Допустим, базовая ячейка — B2: вам нужно вставить знак доллара, чтобы выделить эту ячейку. В формуле это должно выглядеть так: =СМЕЩ($B$2.

Шаг 3

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

Например, если функция находится в столбце F, она должна выглядеть так: =СМЕЩ($B$2, F$1.

Шаг 4

Как и строки, столбцы также должны увеличиваться после связывания и транспонирования. Вы также можете использовать знак доллара, чтобы исправить один столбец, но позволить строкам увеличиваться. Чтобы прояснить это, лучше всего обратиться к примеру, который может выглядеть следующим образом: =СМЕЩ($B$2, F$1, $E2).

Как преуспеть в Excel

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

Использовали ли вы одно из этих приложений для транспонирования/связывания для выполнения этой операции в Excel? Довольны ли вы результатом? Поделитесь своим опытом в разделе комментариев ниже.