Как скопировать таблицу с формулами в экселе на другой лист: Как копировать таблицу в Excel сохраняя формат ячеек

Как копировать таблицу в Excel сохраняя формат ячеек

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

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

Как скопировать таблицу с шириной столбцов и высотой строк

Допустим, у нас есть такая таблица, формат которой необходимо сохранить при копировании:

При копировании на другой лист привычным методом Ctrl+C – Ctrl+V. Получаем нежелательный результат:

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



Способ1:Используем специальную вставку

1. Выделяем исходную таблицу, которую нам необходимо скопировать, нажимаем на Ctrl+C.
2. Выделяем новую (уже скопированную) таблицу, куда нам необходимо формат ширины столбцов и нажимаем на ячейку правой кнопкой мыши, после чего в выпадающем меню находим раздел «Специальная вставка».
3. Выбираем в нем необходимый пункт напротив опции «ширина столбцов», нажимаем «ОК».

Она получила исходные параметры и выглядит идеально точно.

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

Секрет данного способа в том, что если перед копированием таблицы выделить ее столбцы вместе с заголовками, то при вставке ширина каждого столбца будет так же скопирована.

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

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

1. Выделяем целые строки листа, которые охватывают требуемый диапазон данных:
2. Ниже вставляем ее копию:

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

Вставка значений формул сохраняя формат таблицы

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

Чтобы решить такую задачу следует выполнить 2 операции, используя специальную вставку в Excel.

Операция I:

1. Выделяем исходную таблицу с формулами и копируем.
2. В месте где нужно вставить диапазон данных со значениями (но уже без формул), выбираем опцию «значения». Жмем ОК.

Операция II:

Так как скопированный диапазон у нас еще находится в буфере обмена после копирования, то мы сразу еще раз вызываем специальную вставку где выбираем опцию «форматы». Жмем ОК.

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

Полезный совет! Чтобы не выполнять вторую операцию можно воспользоваться инструментом «формат по образцу».

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

5 способ копирования таблицы в Excel. Пошаговая инструкция с фото

Автор Елизавета КМ На чтение 6 мин Опубликовано 18.12.2020

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

Содержание

1. Простое копирование без изменений
2. Если нужны только значения
3. Когда нужны и значения, и форматы
4. Копирование в виде рисунка
5. Полное копирование всего листа
6. Заключение

Простое копирование без изменений

Этот способ наиболее прост в использовании, он удобен, если в исходной таблице представлены простые данные без формул и ссылок.

Обратите внимание! Простое копирование ничего не меняет в исходной информации.

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

• Выделение ячеек. Как правило, используется либо указание диапазона ячеек левой кнопкой мыши, либо комбинация клавиатуры «Shift+стрелка». В результате часть ячеек листа обводится черной рамкой, и они дополнительно подсвечиваются темным оттенком.
• Копирование в буфер обмена. Буфер обмена – это специальная область в памяти компьютера, предназначенная для перенесения данных внутри приложения или между приложениями. Копирование в нее воспроизводится либо нажатием клавиш «Ctrl+C», либо «Ctrl+Insert» (эти комбинации эквивалентны). Также его возможно осуществить через соответствующий пункт контекстного меню или с помощью ленты программы.
• Указание места для вставки. Переходим туда, куда мы хотим скопировать данные, и указываем курсором ячейку, которая будет верхней левой ячейкой вставляемых данных. Следует быть осторожным, если место для вставки уже содержит какие-то данные. Они могут быть стерты.
• Вставка содержимого буфера обмена в указанную область. Это делается клавишами «Ctrl+V» либо «Shift+Insert» либо соответствующим пунктом контекстного меню или ленты программы.

Вызов контекстного меню при простом копировании

Если нужны только значения

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

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

Важно! Формулы и форматы при таком способе не сохраняются.

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

Копирование только значений

Когда нужны и значения, и форматы

Выбор ячеек для такого способа копирования остается прежним, но оно осуществляется либо с помощью контекстного меню (пункт «Специальная вставка»), либо с помощью ленты программы. Нажатием на сам значок «Специальная вставка» можно открыть целую диалоговую панель, где предоставляется больше вариантов копирования, и появляется возможность даже комбинировать данные с помощью операций. Например, можно переносимые данные не просто вставить в указанные ячейки, а прибавить к тем, что уже имеются на листе. Иногда это очень удобно.

Также бывает, что таблица имеет большое количество столбцов различной ширины, и после копирования значений требуется много довольно кропотливой работы по установке нужных ширин. В этом случае в диалоге «Специальная вставка» есть специальный пункт «Ширина столбцов». Вставка при этом осуществляется в два этапа. Сперва вставляем только «ширину столбцов», чтобы «подготовить место», а затем копируем значения. Таблица получается точно такой же, как исходная, но вместо формул в ней остаются значения. Иногда удобно скопировать только ширину столбцов, чтобы таблица выглядела, как исходная, а значения вписывать в ячейки вручную. Кроме того, можно выбрать пункт «копирование с сохранением ширины столбцов» в контекстном меню. В результате вставка будет выполнена в один этап.

Копирование значений и форматов

Копирование в виде рисунка

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

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

Копирование в виде рисунка

Полное копирование всего листа

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

Открывается панель, в которой задается метод копирования. В частности, можно указать, в какую книгу необходимо вставить новый лист, переместить его или копировать, и указать место среди уже имеющихся листов, в которое будет осуществлен перенос. После нажатия на кнопку «OK» в указанной книге появится новый лист со всем содержимым копируемого листа.

Полное копирование листа

Заключение

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

Оцените качество статьи. Нам важно ваше мнение:

Скопируйте рабочий лист Excel и сохраните относительную ссылку на ячейку в формулах

спросил 10 лет, 10 месяцев назад

Изменено 4 года, 4 месяца назад

Просмотрено 356 тысяч раз

Другая проблема с копированием в Excel:

Как я могу скопировать рабочий лист из WorkbookA. xlsx в WorkbookB.xlsx без того, чтобы скопированный рабочий лист все еще ссылался на WorkbookA.xlsx, например. формула

=B!23 становится =[WorkbookA.xlsx]!B!23 при копировании.

Я хочу поддерживать «относительные» ссылки на ячейки вместо «абсолютных» ссылок на ячейки (я придумаю эту терминологию в мире Excel, если она еще не существует).

Другой возможной альтернативой, которую я не могу заставить работать, является возможность вставки только «значений» ячейки. Excel обрабатывает «значения» как вычисляемые значения, а не как фактические формулы в ячейке. Если я выбираю формулу вставки, она все равно дает абсолютные ссылки.

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

, как и сводного отчета, нам желательно просто скопировать и объединить листы обратно в исходный xlsx. А вот «абсолютная» отсылка доставляет немало хлопот.

• Microsoft-Excel
• Microsoft-Excel-2007
• Microsoft-Excel-2010
• макросы
• vba

7

Попробуйте использовать Ctrl + ~ для отображения формул. Затем используйте Ctrl + A , чтобы выделить все, скопировать и вставить в блокнот.

Наконец, скопируйте его из блокнота и вставьте в другую книгу.

2

Во многих случаях мне было проще сделать следующее:

• скопировать лист в новую книгу
• активировать новый лист в новой книге
• выбрать все ( Ctrl + A )
• найти/заменить на
  • найти: [WorkbookA. xlsx]!
  • заменить: <оставьте пустым>
• заменяет все

1

Неподписанный ответ прямо под этим ответом сработал для меня с очень небольшим изменением.

1. Создайте и сохраните электронную таблицу назначения.

2. Используйте «переместить», «скопировать» или перетащить страницу с формулами в новую таблицу. Это оставляет формулы на новой странице, указывающие на старый рабочий лист. Затем сохраните новую таблицу в том же месте, что и старую.

3. Затем перейдите на вкладку «Данные» > нажмите «Изменить ссылки». Опция не будет активна, если на странице нет ссылок.

4. В появившемся диалоговом окне выберите имя исходного файла и нажмите «Изменить источник».

5. В появившемся диалоговом окне открытия файла выберите имя новой электронной таблицы.

Нажмите «Закрыть», и все готово.

Или просто сделайте следующее:

Преобразуйте это:

 =database_feed!A1
 

на это:

 = ДВССЫЛ ("database_feed! A1")
 

и никаких изменений в ваших ссылках при копировании между рабочими листами.

Если у вас не так много ссылок на листы, другой альтернативой может быть использование

 = ДВССЫЛ ("'"&B1&"'!A1")
 

и введите название справочного листа в ячейку B1. Теперь у вас есть только одна ячейка для обновления при копировании в новую электронную таблицу.

2

Поскольку 99% ответов даже не касались первоначального вопроса, вот правильный ответ.

1. Скопируйте листы из исходного файла (Original.xlsx) в новый файл Excel (New.xlsx), как обычно. Как правило, я щелкаю правой кнопкой мыши по имени и выбираю «Переместить или скопировать…».

2. Сохраните второй — только что созданный файл (New.xlsx).

3. В новом файле в разделе «Данные» нажмите «Редактировать ссылки»

4. Во всплывающем окне выберите «Изменить источник. ..»

5. Найдите файл (New.xlsx) и нажмите «Открыть».

Все ссылки на оригинал (Original.xlsx) будут удалены.

ГОТОВО!

2

Приведенный ниже код можно адаптировать к вашим потребностям. Берет все формулы с листа на wb1 и применяет их к листу в новой книге. Формулы применяются как String s, поэтому ссылки на исходную книгу не вставляются. Кроме того, этот код работает очень быстро, потому что он не использует буфер обмена и не требует перебора ячеек.

 Подпрограммы копирования()
Dim wb1 как рабочая книга, wb2 как рабочая книга
Dim s1 как рабочий лист, s2 как рабочий лист
Dim formArr() как вариант
Установите wb1 = Эта рабочая книга
Установите s1 = wb1.Sheets("Лист1")
Установите wb2 = Workbooks.Добавить
Установите s2 = wb2.Sheets("Лист1")
formArr = s1.UsedRange.Formula
s2.Range("A1").Resize(UBound(formArr, 1), UBound(formArr, 2)).Formula = formArr
Конец сабвуфера
 

1

• скопировать рабочий лист в WorkbookB. xlsx
• открыть рабочий лист в новом файле
• выбрать все
• перейдите в меню Данные, нажмите на ссылку редактирования
• отредактировать ссылки так, чтобы ссылка на старый файл теперь была ссылкой на в настоящее время открытый файл

Это работает для меня.

Если вам нужно сделать это автоматически, потому что вы втягиваете листы в программу VBA. Используйте это:

 Общедоступный подпункт ChangeSource()
'
'Макрос источника изменений
' Измените ссылки, чтобы они указывали на текущую книгу.
'
    Затемнить все ссылки как вариант
    allLinks = ThisWorkbook.LinkSources (xlExcelLinks)
    Если Не Является Пустым(всеСсылки) Тогда
        Затемнить каждую ссылку до тех пор, пока
        For eachLink = 1 To UBound(allLinks)
            Если InStr(3, "В названии исходной книги найдена строка.", allLinks(eachLink)) Then
                ThisWorkbook.ChangeLink Name:=allLinks(eachLink), NewName:=ThisWorkbook.FullName, Type:=xlExcelLinks
            Конец, если
        Следующая ссылка
    Конец, если
Конец сабвуфера
 

Просто измените «Строка найдена в названии исходной книги». чтобы соответствовать старым ссылкам, которые вы хотите заменить. Вы можете удалить этот блок, если хотите заменить все ссылки.

1

Для этого должны быть открыты обе книги . Вы запускаете этот макрос, и он скопирует workbookA!sheet 1 в workbookB!sheet1 , а затем заменит все ссылки workbookA . Это грубо, но работает . Очевидно, вы можете изменить код, чтобы он соответствовал вашим именам WorkbookA.xlsx, но убедитесь, что они имеют правильное расширение и остаются в кавычках.

О, чтобы создать макрос, если вы не знаете, нажмите alt + F11 , чтобы вызвать редактор Visual Basic. Затем щелкните правой кнопкой мыши вставку WBA — модуль и скопируйте и вставьте приведенный ниже код в модуль. Затем нажмите F5 , чтобы запустить макрос. Если макрос не запускается, возможно, это связано с тем, что макросы не включены, поэтому сохраните его и снова откройте, а когда будет предложено включить макросы, включите их.

 Sub copysheetremoveWBref()
    Application.ScreenUpdating = Ложь
    'активировать WBA
    Application.Workbooks("workbooka.xlsx").Активировать
    'Выберите лист WBA1
    Application.Workbooks("workbooka.xlsx").Листы("Лист1").Выберите
    'скопировать WBA!лист1 в WBB!лист1
    Листы ("Лист1"). Копировать До: = Рабочие книги ("WorkbookB.xlsx"). Листы ("Лист2")
    'найти ссылки WBA и удалить их
    Cells.Replace What:="=[workbookA.xlsx]", Replace:="=", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ЗаменитьФормат: = Ложь
    Application.ScreenUpdating = Истина
Конец сабвуфера
 

1

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

если первая формула =J2 и она становится =[filepath]J2 , тогда просто найдите и замените всю новую электронную таблицу на [filepath] и ничего не замените. Это удаляет его и восстанавливает формулу до =J2 .

VB не требуется!

1

У меня была похожая проблема. Причина, по которой формулы были вставлены со ссылкой на WBA, заключалась в том, что вкладка (лист), над которой я работал в WBA, называлась иначе, чем в WBB. Для меня это всегда был «последний лист», но один назывался «MinFlow», а другой — «NormalFlow». Я переименовал оба в «Результаты», и копирование/вставка сработали так, как я хотел — «относительная вставка».

Выберите ячейки, которые хотите переместить. Теперь попробуйте переместить их, перетащив на другой рабочий лист (на другую вкладку).

Я знаю, он прокручивается. Вот сложная часть: просто нажмите cmd (mac) или alt (win), и это позволит вам перетащить ячейки на другую вкладку.

1

Здравствуйте, вот простое решение этой проблемы:

1. Скопируйте ячейки как обычно.
2. В формуле выберите и скопируйте текст, связывающий ее с предыдущей книгой [WorkbookA.xlsx].
3. Выберите все ячейки, которые вы хотите изменить, и нажмите CTRL+F и выберите вкладку замены.
4. Замените [WorkbookA.xlsx] пустым пространством (также ничего не пишите в поле Заменить на , нажмите Заменить все .

Вуаля — готово.

Еще один «трюк» — перед копированием исходного рабочего листа замените все квалификаторы формулы = другим набором символов (скажем, ###= ).

Скопируйте рабочий лист, затем после копирования замените квалификатор формулы обратно (заменив ###= с = ).

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

1. Скопируйте лист как обычно. (щелкните правой кнопкой мыши на вкладке и выберите «переместить или скопировать»). Это для форматирования.

2. Скопируйте все ячейки с исходного листа (используя Ctrl + A или верхний левый треугольник и Ctrl + C )

3. Вставить как значения в новую книгу (над листом «шаг-1») (Параметры вставки>123)

Откройте обе книги. В исходной книге (WorkbookA.xlsx) выберите лист, который вы хотите скопировать. Щелкните правой кнопкой мыши вкладку листа и выберите «Переместить или скопировать…». В диалоговом окне «Переместить или скопировать» выберите «WorkbookB.xlsx» в раскрывающемся списке «В книгу», выберите, где в этой книге вы хотите поместить ее, и установите флажок «Создать копию». (И нажмите «ОК».)

Сделайте копию листа, который вы хотите переместить, из которого в этом случае будет WorkbookA.xlsx. Переименуйте его в «Копия WorkbookA.xlsx». Теперь откройте эту новую книгу, а также книгу, в которую вы хотите переместить лист, на который в данном случае будет WorkbookB.xlsx. Щелкните правой кнопкой мыши листы в созданной вами копии рабочей книги, т. е. копию WorkbookA.xlsx, и выберите «переместить или скопировать», а затем переместите эти листы в WorkbookB.xlsx. Вы сделали !

2

Microsoft Excel — скопировать формулу на другой лист

спросил 4 года 11 месяцев назад

Изменено 4 года, 11 месяцев назад

Просмотрено 5к раз

Мне нужно скопировать формулу на другой лист Excel, который является частью той же книги. Я нажимаю на ячейку листа2 и набираю =, затем перехожу к ячейке листа1 и нажимаю клавишу ввода. тут начинается проблема: почему-то копируется не та формула, которую я вижу в строке формул кликнув по ячейке листа1, а совсем другая формула

• Microsoft-Excel
• функция рабочего листа

3

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

Это зависит от того, что вы спрашиваете. Что вы делаете, так это ссылаетесь на ячейку, поэтому формула на листе2 будет Sheet1!A1 или любой другой ячейкой, на которую вы указываете. Если вы действительно хотите получить формулу, то если вы используете Excel 2013 или более позднюю версию, вы можете установить формулу в ячейке листа на =FORMULATEXT(Лист1!B1)

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

1. В моем примере я установил Sheet1!A1 на 45 и Sheet1!B1 ‘=COS(A1)’ (без кавычек) Вот как выглядит Sheet1:

2. Затем я создал имя благодаря удобному трюку, который все еще доступен. Нажмите «Диспетчер имен» на вкладке «Формулы» и создайте новое имя с именем EVALUATE. Комплект Относится к: =EVALUATE(OFFSET(INDIRECT("RC",FALSE),0,-1)) Пример диспетчера имен

3. Наконец, я установил Sheet2!A1 на 90 Sheet2!B1 = FORMULATEXT(Sheet1!B1) и затем Sheet2!C1 на =EVALUATE Sheet2 Example

Вот фрагмент формул в Sheet2

Значение в Sheet2!C1 будет COS(90), а не COS(45), как это было в Sheet1.