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

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

Но как только вы поставите галочку, Paste Link станет серым. К счастью, есть некоторые формулы и приемы, которые помогут вам обойти эту проблему. Давайте посмотрим на них.

TRANSPOSE – Array Formula

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

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

Шаг 1

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

Шаг 2

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

Шаг 3

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

“

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

Link and Transpose – Ручной метод

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

Шаг 1

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

Шаг 2

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

Шаг 3

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

ОФСЕТ Формула

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

Шаг 1

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

Шаг 2

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

Шаг 3

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

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

Шаг 4

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

Как Excel в Excel

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

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