Копируем в Excel оптимально
При работе в редакторе Excel может возникнуть ситуация, когда для облегчения труда требуется скопировать уже отформатированную, с настроенными под ширину листа столбцами, таблицу, с на другой лист или в другой файл. Вот тут-то начинаются сюрпризы. Например, надо скопировать некую таблицу:
Исходная таблица
При выполнении стандартной операции копирования Копировать – Вставить (Ctrl+C — Ctrl+V) получаем таблицу, но вид у нее немного не тот. 🙂
Копирование Ctrl+C — Ctrl+V
Дело в том, что при обычном копировании Excel переносит данные, форматирование ячейки (обрамление и заливку), но ширину столбца и строки оставляет ту, которая установлена на листе, в который выполняется вставка. В результате надо продолжать работы по растягиванию и сжиманию столбцов и строк, чтобы поместить таблицу на листе или привести ее в удобный для работы вид.
Рассмотрим, как избежать этих неприятностей, тем более что есть несколько обходных путей.
Первый вариант.
Оптимален, если требуется перенести всю таблицу целиком, по сути – весь лист. При этом и копировать следует весь лист, но, не выделяя его через столбцы или строки (долго придется выделять), а воспользовавшись специальной кнопкой, которой служит пересечение заголовков строк и столбцов таблицы.
Копирование выделением листа
Порядок действий таков:
- Выделили исходный лист Excel;
- Дали команду на копирование Ctrl+C;
- Перешли на новый лист или в новую книгу;
- Так же выделили его угловой кнопкой;
- Дали команду на вставку Ctrl+V.
Оптимальный результат копирования таблицы
При этом таблица вставится с сохранением всех настроек и будет полностью соответствовать оригиналу.
Второй вариант.
При помощи меню команды Вставить. В Excel команд на копирование-вставку данных не одна, а больше десятка. Во как! На любой случай жизни. Они доступны из выпадающего меню кнопки Вставить:
Меню команды Вставить
Рассмотрим, какие возможности нам предоставлены корпорацией Microsoft. Воспользуемся для примера все той же небольшой табличкой. В ней выполнено форматирование ячеек, текста и чисел, есть формула и обрамление. Посмотрим, как будет изменяться результат при копировании разными командами. Буду нумеровать кнопочки меню слева на право.
Вставка 1 – аналогична стандартной операции копирования Копировать – Вставить (Ctrl+C — Ctrl+V) получаем зауженную таблицу (как в начале статьи).
Вставка 2 – вставка формул. Выполняется копирование только данных и формул. Идет потеря форматирования и оформления.
Копирование только данных и формул
Вставка 3 – перенос формул и форматов чисел. Форматирование теряется.
Перенос формул и форматов чисел
Вставка 4 – сохранение исходного форматирования ячеек, форматов чисел и стиля таблицы. Настройки табличного поля теряются.
Вставка 5 – работает как предыдущий вариант – Вставка 4, но ещё и без рамок (обрамления ячеек).
Копирование без обрамления
Вставка 6 – копирование всего содержимого таблицы с сохранением ширины столбцов оригинала. Оптимальный вариант копирования!
Вставить значения 1 – осуществляется вставка только текущих значений ячеек, в том числе результатов вычислений (а не формул). На рисунке показано, что скопировались результаты в вычисляемом столбце, а не формулы. Это видно по строке формул (отмечено красным).
Копирование значений
Вставить значения 2 – осуществляется вставка только текущих значений ячеек (как в предыдущем варианте — Вставить значения 1) с сохранением форматов чисел.
Вставка значений с сохранением форматов чисел
Вставить значения 3 – переносятся значения ячеек и их исходное форматирование. Очень похоже на вариант копирования Вставка 4, но без переноса самих формул.
Копирование без формул с форматированием
Другие параметры вставки 1 – копирование только форматирования без переноса каких-либо данных и настроек ширины таблицы.
Копирование только форматирования
Другие параметры вставки 2 – копирование и вставка исходника в виде рисунка. На иллюстрации по маркерам видно, что скопированный объект представляет собой рисунок, который не привязан к столбцам (видно смещение). Он расположен над таблицей как отдельный объект.
Копирование и вставка исходника в виде рисунка
Вот такие основные возможности можно применить при копировании данных и таблиц в редакторе Excel.
Кстати, нюансы копирования строк и столбцов из одной таблицы в другую рассмотрены в статье «Копирование строк между таблицами Excel«.
Для получения анонсов статей удобно воспользоваться рассылкой, подписаться на которую можно .
Люблю такие статьи приносящие пользу. Спасибо!
Казалось бы — простое действие, но без знаний его нормально не сделаешь! Очень помогает при копировании данных из ячеек с расчетными функциями!
Спасибо! Работаю в Excel и предоставленная Вами информация очень для меня актуальна.
День добрый, как сделать так, чтобы скопировать таблицу, и вставлять ее по нужному количеству?
Кто знает, ответьте пожалуйста )
Вот так век живи, век учись… А я то думал, что хорошо знаю Еxel. Но как всегда где-то находится тайная кнопка, которая творит чудеса.
День добрый! Подскажите как скопировать лист если в таблице есть связи и нужно получить смещение ссылки на 1 ячейку вниз?
Сергей, доброго и Вам!
Я считаю, что при копировании листа мы берем его, как он есть, целиком и так же 1 к 1 вставляем на другой лист. А если нужно задать какие-либо смещения, то это уже опосля содеянного. 🙂
Добрый день!
Мне нужно скопировать таблицу в Excel и вставить в копируемом листе только ниже, к сожалению строки не сохраняют свой прежний вид и приходится растягивать в низ каждую. Все подсказки не подходят (перепробовала).
Юля, добрый вечер!
Как я понял, Вы копируете фрагмент на лист, в котором уже имеется таблица с данными.
К сожалению, в этом случае высота строк и ширина столбцов будет теряться. Можно разве что выделить группу строк, проведя мышью по номерам строк слева и увеличить их высоту для всего выделенного пакета одновременно на некоторую величину или двойным щелчком по границе номеров строк в выделенной области дать команду на автоматическое выравнивание высот в строках.
Не получается скопировать таблицу на новый лист.Не поместились нижние строки и правый столбец.
Олег, у вас просто таблица не влазит в заданную область печати. Попробуйте сделать немного меньше шрифт и ещё можно (при необходимости) уменьшить ширину столбцов и строк.
как вставить заливку цвета в ячейку, чтобы цифры в ней не менялись. я проверяю отчет, проверенные ячейки хотелось бы оперативно помечать зеленым. спасибо
Марианна, доброго дня!
Чтобы цыфры не менялись и ячейка окрасилась в какой-то цвет, следует применить к ней заливку цветом. Это можно сделать при помощи кнопки «Цвет заливки», расположенной в области «Шрифт» на вкладке «Главная». Только вначале поставьте на нужную ячейку прямоугольник курсора Excel.
Здравствуйте, потратил целый день на решение моей проблемы но так ни к чему и не пришел, помогите пожалуйста, у меня дедлайн, док нужен для работы, не знаю что делать!!!!
Ситуация такова: имеется огромный файл, 350 тысяч строк, данные в одном столбце А, длинная строчка с данными через запятую. Нужно перенести данные в другой файл, на первый лист его, не создав новый лист, а именно вставив данные в уже имеющийся пустой с названием. Во втором доке на двух листах подключен макрос для манипуляции данными, на первый из них как уже было сказано нужно сделать вставку
Что я ни делаю, выскакивает ошибка Данные не могут быть вставлены из за несоответствия размера и формы ……….
Что делать скажите пожалуйста? Я не продвинутый пользователь ексель, и если все таки решите мне помочь, пожалуйста, напишите подробно
Заранее спасибо!
GENA, доброго дня! По такому описанию довольно тяжело что-то советовать. Как я понял, нужно данные одного столбца перенести в один столбец другого файла. Или всю таблицу?
Так как есть какие-то макросы для обработки. то, наверное, таблица имеется.
В этом случае оптимальней всего выделить только нужные ячейки, скопировать их в буфер, после чего перейти в другой файл и там сделать вставку. Если будет ругаться, попробуйте через специальную вставку. Ещё как вариант — выделите диапазон, в когорый нужно вставлять. Кстати, раз есть макросы, стоить проверить, чтобы форматы ячеек, между которыми выполняется копирование, совпадали.
Все разобралась! Извиняюсь))
Здравствуйте! Помогите пожалуйста разобраться. У меня ворд и эксель 2010, я сделала связь с экселя скопировала определенные ячейки и в ворде сделала: вставка, специальная вставка, текст в формате rtf, и нажала связать. все прекрасно работало до того момента пока я то ли изменила имя файлов, то ли то что перенесла в другую папку, так и не поняла причину. в ворде выделенные области остались а связь исчезла, когда попробовала еще раз одну ячейку связать, открыла связи там весь этот список высветился но тип показывает Excel.Sheet.8, а у новых обновленных ячеек тип «лист», как мне восстановить связь?
Добрый день ,подскажите как скопировать вкладку так чтоб таблица как и в оригинале разворачивалась в большую и сворачивалась в маленькую
Добрый день! Очень доступные советы. Помогите, пожалуйста мне. Мне нужно скопировать несколько таблиц с группировкой последовательно на один лист. Когда помечаю весь лист на копирование, то вставить получается только спец.вставкой с потерей группировок, а мне нужна такая же таблица с группировками и еще несколько таблиц с группировками на том же листе ниже. Видела, что так можно, но у меня не получается. Спасибо)
Спасибо! Помогло!
Нет самой главной вставки! 🙁
Обожала Эксель 2003 за то, что скопировав несколько строк / столбцов, я просто нажимала на кнопку «вставить строку / столбец» и все скопированные строки / столбцы вставлялись без проблем не забивая уже имеющие данные. Эксель автоматически добавлял нужное количество строк / столбцов, соответствующее скопированным. В 2013 Экселе я никак не могу найти подобную вставку и жутко парюсь.
Может я просто не знаю как это сделать? Подскажите, пожалуйста!
Таня, здравствуйте! Ответ на Ваш вопрос я поместил в отдельной статье «Копирование строк между таблицами Excel«, которая уже опубликована в моем блоге.
Спасибо большое,что Вы такие есть,получилось,теперь намного меньше работы мне будет с этой вставкой.
Спасибо,1 вариант для меня самый лучший оказался.Можно вопрос,как перенести формулу столбца,у которого 10 строк на 1 строку десяти столбцов,проще говоря инф-ция которая находится сверху вниз сделать слева направо.спасибо.
извините,скопировать
Доброго дня!
Ну, первое, что приходит на ум, это когда строки таблицы преобразуются в столбцы и наоборот — возможность транспонирования. Это можно использовать не только для таблицы, но и для формулы. Ставите курсор на формулу, копируете её, переставляете курсор туда, куда её надо поместить и выбираете команду Вставка-Транспонировать.
Только обратите внимание, что количество ячеек в диапазоне копируемой функции, остается то же.
Удачи, пробуйте!
Извините,я не уточнила-у меня ексель 2003 года
А там тоже есть транспонирование, но расположено кажется в инструментах специальной вставки
Первый способ работает в Excel 2003. А ларчик просто открывался, я в шоке! Огромное СПАСИБО!!!
Афигеть!!!! Первый способ отлично сработал в Excel 2003! Огромное Вам СПАСИБО!!!
Здравсвуйте, при копированя целого листа на котором присутсвует автофильтр колонки, последний теряется на новом вставленном листе, то есть ячейка со стрелочкой вниз пропадает. Функция должна оставаться постоянно при копировании с листа на лист! как это сделать? приходиться каждый раз (на каждом новом скопированном листе вы делять диапазон и задавать тот фильтр через меню. Еxcel 2003, спасибо!
Здравствуйте, Дмитрий. Да, так и есть, автофильтр при копировании с листа на лист не переносится. Это же некий инструмент, который помогает упорядочить Ваши данные и сделать их более читабельными. А при копировании берутся только структура таблицы, ее наполнение (данные) и форматирование. Про инструменты товарищи из Microsoft как-то не подумали, может в будущих версиях добавят такую возможность.
Здравствуйте. Полезные советы. Спасибо! Но возникла проблема. Всегда копировал лист в exel и вставлял в word без проблем. Сечас ну не как. Одна или две буквы не вмещаются и перескакивают на вторую строчку. В итоге весь документ испорчен.
Очень буду благодарен совету.
Спасибо.
Скажите,пожалуйста, при копировании таблицы на другой лист,копируются также и скрытые столбцы. Возможно ли, чтобы при копировании скрытые столбцы удалялясь,не переносились бы?
Увы, нет, при копировании выполняется перенос таблицы или выделенной области в полном объеме.
Здравствуйте, подскажите как вставить таблицу с сохранением высоты строк?
Извините, но мне не понятен вопрос. Вероятно, вы имели в виду вставить скопированную таблицу? Но тогда скопированную откуда?
Тут от Вас требуется уточнение вопроса.
Все конечно понятно, но это работает на 2003 офисе. У меня проблема с тем что слетают стили оформления после вставки в 2007 офисе. Не пойму в чем проблема? Т.е. все работало и вдруг все сломалось.
Файл эксель редактируется на локальном ресурсе, потом некоторые введенные данные копируются в другой файл расположенный на другом компе, через буфер RDP. В итоге периодически сваливается форматирование ячеек в том файле. В чем трабл, не пойму. Вариантов копирования с учетом формата в 2007 нет.
Доброго дня.
Почему же только для 2003-го офиса? Описанное копирование работает для всех версий. Да, соглашусь, что статья писалась для варианта, когда оба документа (книги) Excel расположены на локальном компьютере. Однако, я хочу отметить, что перенос информации через буфер обмена RDP не всегда стабилен, что подтверждается кучей претензий в интернете. И что там теряется, а что нет, тяжело сказать, но видимо от этого многое зависит.
Владимир, Спасибо за описание! Но есть еще вопрос также связанный со стилем «вставки» значений. К примеру есть отфильтрованная таблица. Порядок после фильтрации к примеру такой — Идет значение под номером 2, затем значение 50. Надо изменить значения только в ячейке 2 и 50! Если я буду вставлять новое значение в ячейки 2 и 50 через «CTrl+v» то Ексель изменит только эти 2 ячейки. Скрытые ячейки между 2 — 50 он не тронет. Но, если же я сделаю тоже самое, но вставлю новые значения через «вставить как значения», Ексель кроме нужных мне 2 ячеек поменяет еще и все что между ними. Что есть вообще неприемлимо. Сперва думал это зависит от того отформатированы значения как «таблица» или просто как отфильтрованный диапазон. Но этот эффект есть и там и там. Это какая-то особая особенность функции «вставить как значения»? Спасибо
Андрей, здравствуйте. На вопрос, про особенность функции “вставить как значения”, могу сказать, что вероятно разработчики заложили такой алгоритм замены как для режима «вставить как значения», так и обычной ставки через “Ctrl+V”. Поэтому такие особенности надо просто учитывать при замене отфильтрованных значений таблицы.
Спасибо Вам за столь содержательную и познавательную информацию. Теперь я делаю отчеты в одно мгновенье. Желаю Вам дальнейших творческих успехов.
Скажите, пожалуйста, как сделать тоже самое, но чтоб не отражались формулы? Спасибо.
Извините, но вопрос несколько не понятен. «То же самое», это про какой вариант спрашиваете? Тут же их много.
Если при копировании у вас не видно результатов вычислений, а отображаются расположенные там формулы, то скорее всего происходит сбой по формату этих ячеек. Попробуйте разные варианты копирования через специальную вставку или после копирования подберите оптимальный вариант формата для столбца с формулами.
На последних версиях Office я такого не встречал (обычно все корректно), а вот лет 5 назад что-то такое было.
Извините, не работает! Точнее, работает в том случае, когда копируешь в одной книге. Если мне нужно завести новую книгу с такими же настройками, копируется только информация, но не форматирование. Так же не работают специальные вставки — их просто нет.
Уважаемый Юрий, воспользуйтесь первым вариантом и все получится!
Вначале открываете новую книгу, возвращаетесь к исходнику, выделяете угловой кнопкой, Ctrl+C, идете к чистой книге и листу, выделяете угловой кнопкой и Ctrl+V.