Как скопировать строку с формулами в столбец в excel

В процессе работы с Microsoft Excel у пользователей возникает необходимость внесения дополнительных данных. Для этого необходимо добавлять или копировать строки с формулами.

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

Как добавить новую строку с формулами в Excel?

Пускай мы добавили формулы в одну строку или в несколько. Теперь нам нужно скопировать все строки с формулами. Как это сделать при добавлении новой строки?

1. Выделяем ту строку, которая содержит формулы. В нашей ситуации это строка под номером 3. Нам необходимо, чтобы остальные строки содержали в себе такие же формулы.



2. Нажимаем на выделенную область правой кнопкой мышки. Из контекстного меню нужно выбрать опцию «Копировать» (или жмем CTRL+C).



3. После этого следует выделить следующую одну (или несколько) нижнюю строку, куда будет вставлена новая с формулами.



4. Щелкнуть по заголовку выделенной строки правой кнопкой мышки и выбрать опцию вставить скопированные ячейки (или нажать комбинацию клавиш CTRL+SHIFT+«=»).

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



Как копировать без изменения формул в Excel?

Часто при копировании относительные формулы (т.е. те, в которых нет знака «$») «подставляют» другие значения. А что делать, если вы не хотите изменять значение формул?

Можно сделать все ссылки в формулах абсолютными. Такие формулы будут копироваться неизменно, без изменения данных. Чтобы сделать ссылки абсолютными, добавляем к ним знак «$».

Ставим перед буквами и перед числами знак доллара – и получаем абсолютные ссылки в формулы, значения в которых не будут изменяться при копировании.

На пример в такой таблице ссылка на наценку ($B$1) не будет изменятся при копировании на новых строках, так как она является абсолютной, а не относительной:

Но в некоторых больших и сложных таблицах слишком много формул, которые нужно скопировать и переместить. И чтобы во всех ссылках проставить знак «$» нужно потратить много времени.

Для решения данной задачи можно воспользоваться хитрым способом. До копирования заменить все знаки «=» на символ «#».

Для «активации» работы формул используется знак «равно» (или «=»). Чтобы деактивировать их на время копирования поменяем = на #. А после копирования вернем все на свои места. Это позволит нам обойти автоматическую смену адресов в относительных ссылках при копировании.

1. Нажимаем Ctrl+H (вызываем окно «Главная»-«Найти и выделить»-«Заменить»).



2. Необходимо в поле «Найти» указать символ =, а в поле «Заменить на» символ #. И жмем «Заменить все».



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



4. Снова жмем комбинацию горячих клавиш «Ctrl+H», но теперь указываем в полях обратные значения: найти «#» и заменить на «=». Система вернет все на свои места

В результате при копировании во всех строках ссылки формул остались неизменными.

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

Например, если данные выглядят так: «Регионы продаж» в заголовках столбцов и «Кварталы» с левой стороны:

Функция Транспонировать переупомешет таблицу, в которой столбцы «Кварталы» отображаются в заголовках столбцов, а слева будут показаны регионы продаж, например:

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

Вот как это сделать:

1. Выделите диапазон данных, который требуется переупорядочить, включая заголовки строк или столбцов, а затем нажмите клавиши CTRL+C.

   Примечание: Убедитесь, что для этого нужно скопировать данные, так как не получится использовать команду Вырезать или CTRL+X.

2. Выберите новое место на том месте на компьютере, куда вы хотите ввести транспонную таблицу, чтобы вместить данные в достаточном месте. В новой таблице будут полностью переоформатироваться все данные и форматирование, которые уже есть.

   Щелкните правой кнопкой мыши левую верхнюю ячейку, в которой нужно ввести транспонировать таблицу, и выберите транспонировать .

   

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

Вы можете ввести в книгу данные в качестве транспон данных. Транспонировать: переупочевание содержимого скопированные ячейки при копировании. Данные строк будут вставлены в столбцы, и наоборот.

Вот как можно транспоннять содержимое ячейки:

1. Скопируйте диапазон ячеев.

2. Вы выберите пустые ячейки, в которые вы хотите ввести транспонировать данные.

3. На вкладке Главная щелкните значок Ввести и выберите Ввести транспонировать.

Копирование формул без сдвига ссылок

Проблема

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

Проблема в том, что если скопировать диапазон D2:D8 с формулами куда-нибудь в другое место на лист, то Microsoft Excel автоматически скорректирует ссылки в этих формулах, сдвинув их на новое место и перестав считать:

Задача: скопировать диапазон с формулами так, чтобы формулы не изменились и остались теми же самыми, сохранив результаты расчета.

Способ 1. Абсолютные ссылки

Как можно заметить по предыдущей картинке, Excel сдвигает только относительные ссылки. Абсолютная (со знаками $) ссылка на желтую ячейку $J$2 не сместилась. Поэтому для точного копирования формул можно временно перевести все ссылки во всех формулах в абсолютные. Нужно будет выделить каждую формулу в строке формул и нажать клавишу F4:

При большом количестве ячеек этот вариант, понятное дело, отпадает — слишком трудоемко.

Способ 2. Временная деактивация формул

Чтобы формулы при копировании не менялись, надо (временно) сделать так, чтобы Excel перестал их рассматривать как формулы. Это можно сделать, заменив на время копирования знак «равно» (=) на любой другой символ, не встречающийся обычно в формулах, например на «решетку» (#) или на пару амперсандов (&&). Для этого:

1. Выделяем диапазон с формулами (в нашем примере D2:D8)
2. Жмем Ctrl+H на клавиатуре или на вкладке Главная — Найти и выделить — Заменить (Home — Find&Select — Replace)

   

3. В появившемся диалоговом окне вводим что ищем и на что заменяем и в Параметрах (Options) не забываем уточнить Область поиска — Формулы. Жмем Заменить все (Replace all).
4. Копируем получившийся диапазон с деактивированными формулами в нужное место:

   
5. Заменяем # на = обратно с помощью того же окна, возвращая функциональность формулам.

Способ 3. Копирование через Блокнот

Этот способ существенно быстрее и проще.

Нажмите сочетание клавиш Ctrl+Ё или кнопку Показать формулы на вкладке Формулы (Formulas — Show formulas), чтобы включить режим проверки формул — в ячейках вместо результатов начнут отображаться формулы, по которым они посчитаны:

Скопируйте наш диапазон D2:D8 и вставьте его в стандартный Блокнот:

Теперь выделите все вставленное (Ctrl+A), скопируйте в буфер еще раз (Ctrl+C) и вставьте на лист в нужное вам место:

Осталось только отжать кнопку Показать формулы (Show Formulas), чтобы вернуть Excel в обычный режим.

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

Способ 4. Макрос

Если подобное копирование формул без сдвига ссылок вам приходится делать часто, то имеет смысл использовать для этого макрос. Нажмите сочетание клавиш Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer), вставьте новый модуль через меню Insert — Module  и скопируйте туда текст вот такого макроса:

Sub Copy_Formulas()
    Dim copyRange As Range, pasteRange As Range
    
    On Error Resume Next
    Set copyRange = Application.InputBox("Выделите ячейки с формулами, которые надо скопировать.", _
                                "Точное копирование формул", Default:=Selection.Address, Type:=8)
    If copyRange Is Nothing Then Exit Sub
    Set pasteRange = Application.InputBox("Теперь выделите диапазон вставки." & vbCrLf & vbCrLf & _
                                          "Диапазон должен быть равен по размеру исходному " & vbCrLf & _
                                          "диапазону копируемых ячеек.", "Точное копирование формул", _
                                          Default:=Selection.Address, Type:=8)
    
    If pasteRange.Cells.Count <> copyRange.Cells.Count Then
        MsgBox "Диапазоны копирования и вставки разного размера!", vbExclamation, "Ошибка копирования"
        Exit Sub
    End If
    
    If pasteRange Is Nothing Then
        Exit Sub
    Else
        pasteRange.Formula = copyRange.Formula
    End If
End Sub

Для запуска макроса можно воспользоваться кнопкой Макросы на вкладке Разработчик (Developer — Macros) или сочетанием клавиш Alt+F8. После запуска макрос попросит вас выделить диапазон с исходными формулами и диапазон вставки и произведет точное копирование формул автоматически:

Ссылки по теме

• Удобный просмотр формул и результатов одновременно
• Зачем нужен стиль ссылок R1C1 в формулах Excel
• Как быстро найти все ячейки с формулами
• Инструмент для точного копирования формул из надстройки PLEX

Копирование и вставка формулы в другую ячейку или на другой лист

​Смотрите также​​вуаля!​ в большой таблице​Ставим галочку у слов​ мышку. Все, таблица​не копировать, а перенести​ только заполненные ячейки,​ квадратик в правом​ же формулу в​ можно не только​ функцию «Копировать».​Урок подготовлен для Вас​, выделите ячейку​ CTRL+V.​ и относительные ссылки​ абсолютные или относительные​ или в другой​Примечание:​Estella​ Excel нужную ячейку,​ «Создать копию». Выбираем​ заполнена.​

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

​Или, можно нажать​ командой сайта office-guru.ru​

1. ​B3​Чтобы воспользоваться другими параметрами​

2. ​ на ячейки ознакомьтесь​​ ссылки, которые вы​​ лист щелкните фигуру​​Мы стараемся как​​: Ну а ты​ диапазон, можно воспользоваться​

   

3. ​ куда переместить скопированный​Так же можно​, то не копируем​ заполненные ячейки в​ Указатель мышки приобретает​ Таким образом, заполняем​

4. ​ и складывать их,​ сочетание клавиш «Ctrl»​Источник: http://www.excel-easy.com/examples/copy-exact-formula.html​​, и нажмите​​ вставки, щелкните стрелку​​ со статьей Обзор​​ использовали.​ на ячейку, в​

   ​ можно оперативнее обеспечивать​ и так только​​ функцией «Поиск». Смотрите​​ лист (в эту​

   • ​ заполнить дни месяца,​​ её, а через​ перемежку с пустыми.​​ вид черного крестика.​

   • ​ таблицу формулами. Например,​​ вычитать, умножать, т.​ + «C».​Перевел: Антон Андронов​​CTRL+V​

​ под кнопкой​​ формул.​Например, при копировании формулы​ которую нужно вставить​ вас актуальными справочными​

Проверка и исправление ссылок на ячейки в новом месте

​ числа скопируешь без​ статью «Поиск в​ или другую книгу,​ года, т.д. Таким​ контекстное меню «Вырезать»​ Например, у нас​ Нажимаем два раза​ копируем формулы вниз​ д. Подробнее, смотрите​Или, на закладке​

​Автор: Антон Андронов​), и формула будет​Вставить​В отличие от копирования​ две ячейки вниз​ формулу.​

​ формулы при перемещении​ и вправо от​Чтобы вставить формулу с​ языке. Эта страница​ выбери специальная вставка​

1. ​Т.е. в строке каждая​

2. ​ или оставляем так​​заполнять и цифрами, расположенными​ удалится из первой​

3. ​ нужно поставить даты​ на этот крестик.​ этого устанавливаем (пишем)​ в Excel» здесь.​ «Буфер обмена» нажать​

​как копировать в Excel​ значения в столбце​ из указанных ниже​ формулы в другое​ ячейки A1, ячейки,​

Перенос формулы в другое место

​ сохранением форматирования, выберите​ переведена автоматически, поэтому​ и там чисто​ ячейка высчитывается по​ и нажимаем «ОК».​ как по порядку,​ ячейки и перенесется​ в столбце А​ Формула скопируестя до​ формулу в первую​Если копируем числа​ на кнопку функции​

​ формулыячейки, как скопировать​B​

1. ​ вариантов.​ место в той​ которые ссылки будут​

2. ​ пункты​​ ее текст может​​ числа или что​​ формуле, нужно скопировать​ ​ Затем, у скопированного​ так и с​

   

3. ​ в новое место.​ через одну пустую​ конца заполненного столбца.​ ячейку. Затем нажимаем​ с точками -​

4. ​ «Копировать», предварительно выделив​ лист в Excel,​.​​Формулы​​ же или другой​​ обновляться согласно следующее:​​Главная​ содержать неточности и​

   ​ то в этом​ только цифры​ листа, можно изменить​​ интервалом​​Ещё варианты копирования​ ячейку.​ У нас, в​

   • ​ на ячейку с​​ разделителями всместо запятой​ ячейки для копирования.​​ таблицу,значениеформат​

   • ​Если вы не желаете​ ​Чтобы вставить только​ лист, ссылки на​Ссылка​ >​

support.office.com

Как скопировать точную формулу в Excel

​ грамматические ошибки. Для​ роде будет.​Divo​ название, переместить его​. Например: 5,10, 15,​ формул, чтобы ссылки​

​Выделяем первую ячейку с​​ примере, в ячейки​​ формулой левой мышкой​ (1.23), то вставив,​Как скопировать значение ячейки​​, т.д. Одна из​​ этого, а хотите​​ формулу​​ ячейки в формуле​

​Новая ссылка​Вставить​​ нас важно, чтобы​​Eduard​​: Копируешь ячейку с​​ на другое место,​​ т.д. Смотрим пример:​​ в них не​​ датой и пустую​​ F25:F26.​​ и наводим мышку​​ эти числа превратятся​ в Excel​ многих возможностей таблицы,​​ скопировать точную формулу​​.​

​ не изменится —​$A$1 (абсолютный столбец и​или нажмите клавиши​ эта статья была​: Как скопировать несколько​ формулой.​

1. ​ изменить цвет ярлычка.​ интервал 2.​ менялись, описаны в​
2. ​ ячейку ниже.​​Получилось.​​ на правый нижний​​ в дату. Чтобы​​(без формулы).​
3. ​ облегчающих работу –​​ (без изменения ссылок​​Значения​ вне зависимости от​
4. ​ абсолютная строка)​​ CTRL+V.​​ вам полезна. Просим​​ ячеек с формулами​​Для Excel 2003:​

​Как скопировать лист в​

​Если нужно​​ статье «Как скопировать​​Протягиваем за заленый​​Внимание!​​ угол этой ячейки,​ этого избежать ,​

​Выделяем ячейки, которые​ это​
​ на ячейки), выполните​
​, чтобы вставить​

​ типа можно с​

office-guru.ru

Копирование в Excel.

​$A$1​​Другими параметры вставки, щелкните​ вас уделить пару​ чтобы привставке их​ на ячейке, в​​ новую книгу Excel​написать одинаковый текст в​ формулу в Excel​ квадратик в правом​​Формула скопируется в​​ там появится черный​
​ есть несколько способов.​ нужно скопировать. Нажимаем​копирование в Excel​ следующие простые действия:​ результат формулы только​ помощью ссылки на​A$1 (относительный столбец и​ стрелку под кнопкой​ секунд и сообщить,​ в другое место​ которую надо вставить​.​ нескольких ячейках Excel​ без изменения ссылок».​ нижнем углу выделенного​ таблице, если ячейки​ крестик. Нажимаем на​
​ Смотрите статью «Автозамена​ «Копировать». Переходим на​.​Поместите курсор в строку​ (результат вычисления по​ ячейки.​ абсолютная строка)​
​Вставить​ помогла ли она​ формулы не изменились?​
​ жмешь правую кнопку​Для этого в​ подряд​Как заполнить таблицу в​ диапазона ячеек вниз​ столбца слева таблицы​
​ него левой мышкой​ в Excel».​​ новое место, делаем​
​Можно копировать всю​ формул и выделите​ формуле).​Выполните указанные ниже действия,​C$1​и выберите вариант:​ вам, с помощью​ Заранее спасибо.​
​ мышки, в контекстном​ диалоговом окне в​, то пишем текст​ Excel.​ по столбцу. даты​ будут заполнены. Тогда​ и ведем вниз,​Как скопировать формулу в​ активной ячейку, куда​ таблицу или только​ формулу.​Когда вы копируете формулу,​ чтобы переместить формулу.​​$A1 (абсолютный столбец и​​Формулы​
​ кнопок внизу страницы.​Serge_007​ меню выбираешь специальная​ разделе «Переместить выбранные​ в первой ячейке,​Можно копировать данные​ скопируются так.​ Excel поймет до​ удерживая левую кнопку​ Excel.​
​ будем копировать, нажимаем​ значение ячеек без​Нажмите сочетание клавиш​ Excel автоматически настраивает​Щелкните ячейку, содержащую формулу,​ относительная строка)​Чтобы вставить только​ Для удобства также​: Этот вопрос неоднократно​ вставка, в открывшемся​
​ листы в книгу»​ затем перетаскиванием (черным​
​ ячеек в определенной​
​Если выделим три​​ какой ячейке расположена​ мыши нажатой.​​Первый вариант.​ правой мышью и​ формул, или копировать​CTRL+C​ ссылки на ячейки​ которую вы хотите​$A3​ формулу​ приводим ссылку на​ задавался​ окошке ставишь отметку​
​ выбираем «(новая книга)».​
​ крестиком) ведем вниз.​​ последовательности. Например, надо​​ пустые ячейки с​ таблица.​Отпускаем мышку. Формулы скопировались,​Когда мы​ выбираем функцию «Вставить».​ формулы с привязкой​, затем​ так, что формула​ переместить.​A1 (относительный столбец и​.​ оригинал (на английском​Выделить диапазон с​ на «значения» и​Как скопировать формат в​Копировать перетаскиванием можно​ заполнить графу таблицы​ датой, то даты​Такми способом можно​ в них изменились​копируем ячейки с относительными​Когда значения ячеек,​
​ к новым ячейкам​Enter​ копируется в каждую​Выберите​ относительная строка)​
​Значения​ языке) .​ формулами подлежащий копированию​ жмешь ОК. Если​ Excel ячейки или​ как по столбикам,​
​ названиями месяцев года.​
​ скопируются через три​ копировать значение, формулы,​ адреса ячеек относительно​​ формулами​
​ в которых стоят​ или без привязки​.​ новую ячейку.​Главная​C3​Чтобы вставить значения​При копировании формулы в​Ctrl+H​ это приходится делать​ условное форматирование​ так и по​ Пишем в первой​ пустые ячейки. Получится​ формат. Если выделим​
​ нового места. Таблица​
​(«Копировать» -> «Вставить»)​
​ формулы (цифры) не​ к ячейке (со​Bыделите ячейку​На примере ниже ячейка​>​Если ссылки на ячейки​ только (результат вычисления​
​ другое место, можно​Найти:​ часто, то лучше​, без самих значений​ строкам.​ ячейке «Январь», в​ так.​ ячейки в нескольких​ формулами заполнена.​ и затем переносим​ копируются, делаем так:​ сменой адреса ячейки​B3​A3​Вырезать​
​ в формуле не​ по формуле)​
​ выбрать параметры вставки​=​ соответствующую кнопку вывести​ ячеек, смотрите в​Еще несколько способов,​ следующей ячейке ниже​Если нужно перенести​ стролбцах, то копирование​Как скопировать формулу​ в другое место,​ выделяем ячейки как​
​ или оставить адрес​и снова кликните​содержит формулу, которая​
​или нажмите клавиши​ возвращают нужный результат,​.​ определенного формулы в​Заменить:​ на панель быстрого​​ статье «Копирование условного​ как писать одинаковый​ пишем «Февраль». Теперь​ ячейки с формулами,​ будет сразу во​ в Excel таким​
​ то таблица Excel​ обычно-> «Копировать». Появится​ без изменений). Можно​​ по строке формул.​ суммирует значения в​​ Ctrl + X.​ попробуйте использовать другой​Совет:​ ячейках назначения.​ё=​ доступа.​ форматирования, формата в​ текст, формулы в​
​ выделяем обе эти​​ но​ всех выделенных столбцах.​​ способом, чтобы не​ сама меняет адреса​ вокруг ячеек, которые​ копировать данные с​Нажмите​ ячейках​На том же листе​
​ тип ссылки:​ Можно также с помощью​Ниже описано, как скопировать​Заменить​Для Excel 2007:​ Excel».​ нескольких ячейках, т.д.,​
​ ячейки. Наводим мышь​адреса в формулах Excel​
​ Например, если выделим​ копировался формат, смотрите​ ячеек в формулах​ мы копируем, пульсирующая​ определенной последовательностью и,​CTRL+V​A1​ или в другой​Выделите ячейку, содержащую формулу.​ маркера заполнения для​ и Вставить формулу.​Скопировать диапазон​ в панели «Главная»​Иногда, когда нужно​ смотрите в статье​ на правый нижний​ должны остаться прежние​ ячейки D24, E24,​
​ в статье «Как​ относительно новых ячеек.​ рамка, еще раз​ таким образом, заполнять​, потом клавишу​
​и​ лист щелкните ячейку,​В формуле в строке​ копирования формулы в​​Выделите ячейку, содержащую формулу,​Выделить диапазон подлежащий​ жмешь на стрелочку​ напечатать документ, распечатывается​​ «Как заполнить ячейки​ угол выделенного диапазона​(не привязывать к​
​ F24, нажмем два​​ скопировать в Excel».​ Смотрите подробнее в​ нажимаем правой мышью​​ таблицу. Можно копировать​Enter​A2​ в которую нужно​
​, выделите ссылку,​ смежные ячейки листа.​ которую нужно скопировать.​ вставке формул​
​ под иконкой «Вставить»​ (копируется) не значение​ в Excel одновременно»​ и ведем вниз​ новым ячейкам), то​ раза мышью по​Третий вариант.​ статье «Относительные и​
​ на выделенную ячейку​ лист. И многое,​​.​.​ вставить формулу.​ вы хотите изменить.​
​После копирования формулы в​Выберите​Вставить​ и выбираешь «Вставить​ ячеек, а формулы.​ тут.​ по столбцу черный​ это снова в​ углу ячейки F24,​Как копировать формулы в​ абсолютные ссылки в​ и в диалоговом​ многое другое.​
​Результат:​Скопируйте эту формулу в​​Чтобы вставить формулу с​
​Нажмите клавишу F4, чтобы​ новом месте, важно​Главная​Ctrl+H​ значение»​
​ Чтобы было значение​Как cкопировать лист в​ крестик. Таблица сама​​ статье «Относительные и​ то скопируются данные​ Excel на весь​ Excel».​ окне​
​Чтобы вызвать функцию​Теперь обе ячейки (​ ячейку​ сохранением форматирования, выберите​ переключаться между комбинаций​ проверьте, правильно ли​ >​Найти:​Ded​
​ (цифры, а не​ Excel -​ заполнит ячейки следующими​ абсолютные ссылки в​ по всем этим​ столбец​Второй выриант.​

excel-office.ru

Как скопировать данные из Excel? (не формулы а сами числа)????

​выбираем команду «Специальная вставка»​ «Копировать», достаточно нажать​A3​B3​

​ пункты​​ абсолютных и относительных​ все ссылки на​
​Копировать​ё=​: стрл+Ф​ формулы) в ячейках,​правой мышью нажимаем​ месяцами года.​ формулах Excel». Ссылка​ столбцам.​.​Можно​, выбираем — «Значение».​ правой кнопкой мыши​и​(выделите ячейку​Главная​
​ ссылок и выберите​ ячейки. Выбранная ячейка​или нажмите клавиши​Заменить:​ктрл+С​ читайте статью «Печать​

​ на ярлык листа.​​Справа всплывает подсказка,​
​ на статью в​
​Копирование в Excel через​
​Нам нужно скопировать​
​копировать формулы перетаскиванием​

​Затем, как обычно -​​ на выделенную ячейку,​B3​A3​ >​ необходимый вариант.​ ссылки могут измениться​ CTRL+C.​=​Альт+Таб​

Как скопировать несколько ячеек с формулами (Как скопировать несколько ячеек с формулами)

​ в Excel» тут.​​ В диалоговом окне​ по какой месяц​ статье выше.​ ячейку.​ формулу из ячейки​, чтобы не писать​

​ «Вставить». С помощью​​ диапзон и выбрать​) содержат одну и​
​, нажмите сочетание клавиш​Вставить​
​Дополнительные сведения о абсолютные​
​ с учетом типа​​На том же листе​
​Заменить​​Ктрл+М​
​Чтобы найти быстро​
​ выбираем «Переместить/скопировать».​
​ заполнили таблицу. Отпускаем​Если нужно​
​Можно копировать не​
​ F24. Наводим на​
​ одну и ту​​ функции «Специальная вставка»​
​ из контекстного меню​​ ту же формулу.​
​CTRL+C​

excelworld.ru

​или нажмите клавиши​

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

1 Простое протягивание формулы

Это самый простой и привычный для многих пользователей способ распространения формулы сразу на несколько ячеек строки или столбца. Он требует выполнения следующих действий:

• В первую ячейку с одной из сторон (например, сверху) надо записать нужную формулу и нажать Enter.
• После появления рассчитанного по формуле значения навести курсор в нижний правый угол ячейки. Подождать, пока толстый белый крестик не превратиться в тонкий черный.
• Нажать на крестик и, удерживая его, протянуть формулу в нужном направлении. В указанном примере — вниз.

Аргументы в формуле будут изменяться соответственно новому расположению. И если в самой первой ячейке это были F7 и G7, в последней позиции столбца это будет уже F12 и G12. Соответственно, если начинать распространять формулы по строкам, изменяться будут не цифры, а буквы в обозначениях ячеек.

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

2 Быстрое автозаполнение

Еще один способ в Excel протянуть формулу до конца столбца с более высокой по сравнению с первой методикой скоростью. Требует от пользователя применить такие действия:

• Ввести в верхнюю ячейку формулу, в которой применяются аргументы из соседних столбцов. Нажать кнопку Enter.
• Навести курсор на правый нижний угол, чтобы он приобрел форму черного крестика.
• Кликнуть два раза по нижнему правому углу ячейки. Результатом станет автоматическое распространение формулы по столбцу с соответствующим изменением аргументов.

Стоит отметить, что автоматическое протягивание выполняется только до первой пустой ячейки. И если столбец был прерван, действия придется повторить для следующего диапазоне.

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

3 Протягивание без изменения ячеек в формуле

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

Для распределения без изменения адреса ячейки выполняются те же действия, что и при обычном протягивании или автоматическом копировании. Но при вводе формулы следует зафиксировать адреса, которые не будут меняться. Для этого используются символы доллара — $. Если в каждом новом пункте столбца при расчетах используется одна и та же ячейка, значки надо будет поставить и перед номером строки, и перед литерой, которая указывает на колонку. Как в примере: $G$6.

Была ли эта статья полезной?

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

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

• Преобразовываем строки в столбцы методом Копировать > Вставить
• Транспонируем таблицу при помощи копирования / вставки ссылок на исходные данные
• Транспонируем данные в Excel при помощи формул

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

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

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

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

1. Вставьте скопированные данные при помощи команды 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 при помощи уже знакомой нам техники Копировать > Вставить, так, чтобы транспонированные данные ссылались на исходные данные. Главное преимущество такого подхода в том, что при изменении данных в исходной таблице, транспонированная таблица обновится и отразит эти изменения.

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

   

4. Выделите новые данные и откройте диалоговое окно Excel – Find and Replace (Найти и заменить). Вы также можете нажать сочетание Ctrl+H, чтобы сразу попасть на вкладку Replace (Заменить).
5. Замените все символы равно «=» на «ххх«. На самом деле Вы можете заменить их на любые другие символы, которых нет в рассматриваемом диапазоне.Это превратит Вашу таблицу в нечто слегка пугающее (смотри рисунок ниже), но не тревожьтесь, осталось всего 2 действия, и Вы получите желаемый результат.

   

6. Скопируйте таблицу с символами «ххх» и выполните команду Paste Special (Специальная вставка) > Transpose (Транспонировать), чтобы преобразовать столбцы в строки (или строки в столбцы).
7. Снова откройте диалоговое окно Find and Replace (Найти и заменить), чтобы заменить все символы «ххх» на «=«, т.е. восстановить ссылки на исходные ячейки. Готово!

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

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

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

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

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

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

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

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

   =TRANSPOSE($A$1:$G$6)
=ТРАНСП($A$1:$G$6)

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

1. Нажмите Ctrl+Shift+Enter.

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

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

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

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

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

• Форматирование начальной таблицы не сохранится в преобразованной таблице, как это видно на рисунке выше.
• Если какие-то ячейки начальной таблицы были пусты, то в транспонированных ячейках там будут нули.
• Вы не сможете редактировать ячейки в преобразованной таблице, поскольку функция TRANSPOSE (ТРАНСП) чрезвычайно зависима от источника данных. Если Вы сделаете попытку изменить какую-либо ячейку, то получите сообщение с предупреждением: You cannot change part of an array (Нельзя изменять часть массива).

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

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

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

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

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

1. Введите формулу, показанную ниже, в верхнюю левую ячейку конечного диапазона (допустим, это будет ячейка A7), и нажмите Enter:

   =INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)))
=ДВССЫЛ(АДРЕС(СТОЛБЕЦ(A1);СТРОКА(A1)))

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

   =INDIRECT(ADDRESS(COLUMN(A1)-COLUMN($A$1)+ROW($A$1),ROW(A1)-ROW($A$1)+COLUMN($A$1)))
=ДВССЫЛ(АДРЕС(СТОЛБЕЦ(A1)-СТОЛБЕЦ($A$1)+СТРОКА($A$1);СТРОКА(A1)-СТРОКА($A$1)+СТОЛБЕЦ($A$1)))

   Где A1 – это левая верхняя ячейка таблицы-источника. Также обратите внимание на использование абсолютных и относительных ссылок.

2. Скопируйте формулу на весь диапазон, в который Вы собираетесь поместить транспонированные данные, протянув за маркер автозаполнения в правом нижнем углу выделенной ячейки (или диапазона ячеек).

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

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

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

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

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

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

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

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

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

=INDIRECT("B1")
=ДВССЫЛ("B1")

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

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

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

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

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

=INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)))
=ДВССЫЛ(АДРЕС(СТОЛБЕЦ(A1);СТРОКА(A1)))

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

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

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

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

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

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