Функции связывания и транспонирования в Excel исключают друг друга. Это означает, что транспонированные ячейки не будут работать как ссылки на вашем листе. Другими словами, любые изменения, которые вы вносите в исходные ячейки, не отражаются в транспонированной копии.
Однако в ваших проектах часто требуется как транспонировать, так и связать ячейки / столбцы. Так есть ли способ использовать обе функции? Конечно, есть, и мы представим вам четыре различных метода, как это сделать.
Можно с уверенностью сказать, что эти уловки являются частью промежуточных знаний об Excel, но если вы выполните шаги до буквы Т, не будет никаких проб и ошибок, даже если вы полный новичок.
Проблема склеивания
Для целей этой статьи предположим, что вы хотите перенести столбцы на один и тот же лист. Ну так что ты делаешь? Выделите столбцы, нажмите Ctrl + C (Cmd + C на Mac) и выберите место для вставки. Затем нажмите «Вставить», выберите «Специальная вставка» и установите флажок напротив «Транспонировать».
Но как только вы установите этот флажок, Вставить ссылку станет серым. К счастью, есть несколько формул и уловок, которые помогут вам обойти эту проблему. Давайте посмотрим на них.
TRANSPOSE - Формула массива
Основное преимущество этой формулы в том, что вам не нужно вручную перетаскивать ячейки. Однако у него есть определенные недостатки. Например, размер изменить непросто, а это означает, что вам нужно снова использовать формулу, если диапазон исходных ячеек изменится.
Однако аналогичные проблемы применимы и к другим формулам массива, и эта поможет вам довольно быстро решить проблему транспонирования ссылок.
Шаг 1
Скопируйте ячейки и щелкните верхнюю левую ячейку в области, в которую вы хотите вставить ячейки. Нажмите Ctrl + Alt + V, чтобы открыть окно Специальная вставка. Вы также можете сделать это с панели инструментов Excel.
Шаг 2
Открыв окно, отметьте «Форматы» в разделе «Вставить», выберите «Транспонировать» в правом нижнем углу и нажмите «ОК». Это действие меняет только форматирование, но не значения, и это необходимо по двум причинам. Во-первых, вы узнаете диапазон транспонированных ячеек. Во-вторых, вы сохраняете исходный формат ячеек.
Шаг 3
Необходимо выделить всю область вставки, и это можно сделать после вставки форматов. Теперь введите = TRANSPOSE («Исходный диапазон») и нажмите Ctrl + Shift + Enter.
Примечание: Важно нажимать Enter вместе с Ctrl и Shift. В противном случае программа не распознает команду правильно и автоматически генерирует фигурные скобки.
Связывание и транспонирование - ручной метод
Да, Excel - это автоматизация и использование функций, упрощающих работу с ячейками и столбцами. Однако, если вы имеете дело с довольно маленьким диапазоном ячеек, ручное связывание и транспонирование часто является самым быстрым решением. Однако, по общему признанию, есть место для ошибки, если вы недостаточно осторожны.
Шаг 1
Выделите свои ячейки и скопируйте / вставьте их, используя опцию Специальная вставка. На этот раз вы не ставите галочку напротив параметра «Транспонировать» и оставляете параметры в разделе «Вставить» по умолчанию.
Шаг 2
Нажмите кнопку «Вставить ссылку» в левом нижнем углу, и ваши данные будут вставлены в виде ссылок.
Шаг 3
А вот и самое сложное. Вам нужно вручную перетащить, а затем опустить ячейки в новую область. В то же время вы должны быть осторожны при обмене строками и столбцами.
Формула смещения
Это один из самых мощных инструментов для вставки, связывания и транспонирования ячеек. Однако это может быть непросто, если вы новичок в Excel, поэтому мы постараемся сделать шаги как можно более понятными.
Шаг 1
Вам нужно подготовить числа слева и сверху. Например, если есть три строки, вы будете использовать 0–2, а если есть два столбца, вы будете использовать 0–1. Метод - это общее количество строк и столбцов минус 1.
Шаг 2
Далее вам нужно найти и определить базовую ячейку. Эта ячейка должна оставаться нетронутой при копировании / вставке, поэтому вы используете специальные символы для ячейки. Допустим, основная ячейка - это B2: вам нужно вставить знак доллара, чтобы выделить эту ячейку. В формуле это должно выглядеть так: = СМЕЩЕНИЕ ($ B $ 2.
Шаг 3
Теперь вам нужно определить расстояние (в строках) между базовой ячейкой и целевой ячейкой. Это число должно увеличиваться при перемещении формулы вправо. По этой причине перед столбцом функции не должно быть знака доллара. Вместо этого первая строка фиксируется знаком доллара.
Например, если функция находится в столбце F, функция должна выглядеть так: = СМЕЩЕНИЕ (2 $ B $, 1 F $.
Шаг 4
Как и строки, столбцы также должны увеличиваться после связывания и транспонирования. Вы также можете использовать знак доллара, чтобы исправить один столбец, но позволяете строкам увеличиваться. Чтобы прояснить это, лучше всего обратиться к примеру, который может выглядеть следующим образом: = СМЕЩЕНИЕ ($ B $ 2, F $ 1, $ E2).
Как работать в Excel в Excel
Помимо данного метода есть также сторонние инструменты, которые вы можете использовать для связывания и транспонирования. И если данные методы не дают удовлетворительных результатов, возможно, лучше всего использовать один такой инструмент.
Вы использовали одно из этих приложений для транспонирования / связывания для выполнения этой операции в Excel? Вы остались довольны результатом? Поделитесь своим опытом в разделе комментариев ниже.
