Как перенести формулу с одного листа на другой в Excel
Обновлено: 05.07.2024
В Excel копирование данных с одного рабочего листа на другой — простая задача, но между ними нет никакой связи. Но мы можем создать связь между двумя рабочими листами или рабочими книгами, чтобы автоматически обновлять данные на другом листе, если они изменяются на первом рабочем листе. В этой статье объясняется, как это делается.
Автоматическое отображение данных на другом листе Excel
Мы можем связывать рабочие листы и автоматически обновлять данные. Ссылка — это динамическая формула, которая извлекает данные из ячейки одного листа и автоматически обновляет эти данные на другом листе. Эти связывающие рабочие листы могут находиться в той же книге или в другой книге.
Один лист называется исходным листом, откуда эта ссылка автоматически извлекает данные, а другой лист называется конечным листом, который содержит эту формулу ссылки и где данные автоматически обновляются.
Помните одну вещь: форматирование ячеек исходного листа и листа назначения должно быть одинаковым, иначе результат может выглядеть по-разному и может привести к путанице.
Два метода связывания данных на разных листах
Мы можем связать эти два листа двумя разными способами.
- Скопируйте и вставьте ссылку
- На исходном листе выберите ячейку, содержащую данные или которую вы хотите связать с другим листом, и скопируйте ее, нажав кнопку "Копировать" на вкладке "Главная" или нажав клавиши CTRL+C.
- Перейдите к целевому рабочему листу и щелкните ячейку, в которую вы хотите связать ячейку из исходного рабочего листа. На вкладке «Главная» нажмите кнопку со стрелкой раскрывающегося списка «Вставить» и выберите «Вставить ссылку» в разделе «Другие параметры вставки». Или щелкните правой кнопкой мыши ячейку на целевом листе и выберите "Вставить ссылку" в разделе "Параметры вставки".
- Сохраните работу или вернитесь к исходной книге и нажмите кнопку ESC на клавиатуре, чтобы удалить границу вокруг скопированной ячейки и сохранить работу.
- На целевом листе нажмите ячейку, которая будет содержать формулу ссылки, и введите знак равенства (=)
- Перейдите к исходному листу, щелкните ячейку, содержащую данные, и нажмите Enter на клавиатуре. Сохраните свою работу.
Используя эти два метода, мы можем связать лист и автоматически обновлять данные в зависимости от ваших требований. В этой статье мы обсудим некоторые примеры, используя следующие случаи.
Обновить ячейку на одном листе на основе ячейки на другом листе
Предположим, у нас есть значение 200 в ячейке A1 на Листе 1, и мы хотим обновить ячейку A1 на Листе 2, используя формулу связывания. Мы можем сделать это, используя те же два метода, которые мы рассмотрели.
С помощью метода копирования и вставки ссылки
Скопируйте значение ячейки 200 из ячейки A1 на Листе 1.
Перейдите на Лист2, щелкните ячейку A1 и щелкните стрелку раскрывающегося списка кнопки «Вставить» на вкладке «Главная» и выберите кнопку «Вставить ссылку». Он создаст ссылку, автоматически введя формулу =Sheet1!A1 .
Или щелкните правой кнопкой мыши ячейку на целевом листе Лист2 и выберите "Вставить ссылку" в разделе "Параметры вставки": будет автоматически создана формула связи.
Ввод формулы вручную
Мы можем вручную ввести формулу связывания в ячейку A1 на целевом рабочем листе Sheet2, чтобы обновить данные, вытащив их из ячейки A1 на Sheet1.
В ячейке A1 на Листе 2 вручную введите знак равенства (=), перейдите на Лист 1, щелкните ячейку A1 и нажмите клавишу ВВОД на клавиатуре. Следующая формула связывания будет обновлена на целевом листе, который свяжет ячейку A1 обоих листов.
=Лист1!A1
Обновлять ячейку на одном листе, только если первый лист соответствует условию
Вводя формулу связывания вручную, мы можем обновить данные в ячейке A1 на Листе 2 на основе условия, если значение ячейки A1 на Листе 1 больше 200.Мы можем сделать это, введя это логическое условие в функцию ЕСЛИ. Если ячейка A1 на Sheet1 соответствует этому условию, функция ЕСЛИ возвращает значение в ячейке A1 на Sheet2, в противном случае возвращается пустая ячейка.
Вот формула для связи ячеек обоих листов на основе этого условия. Мы введем эту формулу вручную в ячейку A1 Листа2
=IF(Лист1!A1>200,Лист1!A1,"")
Обновить ячейку на одном листе с другого листа с помощью раскрывающегося списка
Предположим, у нас есть раскрывающийся список в ячейке A1 на Листе 1, и мы можем обновить ячейку A1 на Листе 2, введя формулу ссылки в ячейку A1 на Листе 2.
В ячейке A1 на Листе 2 мы вручную введем эту формулу связывания, чтобы автоматически обновлять данные на основе значения ячейки, выбранного из раскрывающегося списка.
=Лист1!A1
Связать данные с реальным набором данных сложнее и зависит от вашей ситуации. Возможно, вам придется использовать методы, отличные от перечисленных выше. Если вы спешите и хотите, чтобы на вашу проблему ответил эксперт Excel, попробуйте наш сервис. Специалисты готовы помочь вам 24/7. Первый вопрос бесплатный.
Это сообщение не ответило на ваш вопрос? Получите решение, связавшись с экспертом.
У меня есть несколько листов с цветными ячейками (без данных, содержащихся в них), которые мне нужно сгруппировать вместе на одном листе. По сути, если у меня есть 3 окрашенные ячейки на одном листе и 2 цветные ячейки на другом, я бы хотел, чтобы на третьем листе отображались все 5 цветных ячеек и автоматически обновлялись по мере того, как новые ячейки становятся окрашенными/обесцвеченными.
У меня есть лист, на котором я рассчитываю макросы продуктов питания. Я сделал все свои расчеты. Одна из моих колонок — разрешенное количество еды. У меня есть еще одна область, где я ввожу разрешенные калории. Я хочу, чтобы у меня была возможность изменять потребление калорий и чтобы все "разрешенное количество пищи" автоматически обновлялось для меня.
Когда точка данных Excel достигает определенного значения, я хочу, чтобы это значение было отправлено в текстовом или SMS-сообщении моему оператору сотовой связи, чтобы я мог получить сообщение. Электронная таблица автоматически подключается к другому источнику данных и автоматически обновляется каждую секунду. Как мне это сделать?
Мне нужна помощь в создании листа Excel, который берет информацию из одного столбца на другом листе для автоматического обновления соответствующей строки или информации в мастер-листе.
При копировании формул в другое место можно выбрать параметры вставки для конкретных формул в ячейках назначения.
Вот как копировать и вставлять формулу:
Выберите ячейку с формулой, которую хотите скопировать.
Нажмите на ячейку, в которую хотите вставить формулу.
Если эта ячейка находится на другом листе, перейдите на этот лист и щелкните нужную ячейку.
Чтобы быстро вставить формулу с ее форматированием, нажмите + V. Или вы можете нажать стрелку рядом с кнопкой Вставить:
Нажав на стрелку, вы увидите список параметров. Вот наиболее часто используемые:
Формулы — для вставки только формулы, а не форматирования исходной ячейки.
Формулы и форматирование чисел — вставка только формулы и форматирования чисел (например, формат процентов, формат валюты и т. д.).
Сохранить исходное форматирование — вставка формулы, форматирования чисел, шрифта, размера шрифта, границы и заливки исходной ячейки.
Вставить значения — чтобы исключить формулу и вставить только результат.
Другие варианты, которые могут быть вам полезны:
Без границ — чтобы вставить формулу, числовое форматирование, шрифт, размер шрифта, заливку, но не границу оригинала.
Сохранить ширину исходных столбцов — вставка формулы, форматирования чисел, шрифта, размера шрифта, заливки, границы и ширины оригинала.
Транспонировать — используйте этот параметр при копировании нескольких ячеек. Если вы скопируете ячейки, расположенные рядом друг с другом в строке, этот параметр вставит их в столбец. Если ячейки находятся в столбце, они будут вставлены рядом друг с другом в строке. Эта опция вставляет формулу, числовое форматирование, шрифт, размер шрифта, штриховку, границу.
Проверьте и исправьте ссылки на ячейки в новом местоположении
После копирования формулы в новое место важно проверить правильность ссылок на ее ячейки. Ссылки на ячейки могли измениться в соответствии с используемым типом абсолютной или относительной ссылки.
Например, если вы скопируете формулу на две ячейки ниже и правее ячейки A1, используемые вами ссылки на ячейки будут обновлены следующим образом:
Эта ссылка:
$A$1 (абсолютный столбец и абсолютная строка)
A$1 (относительный столбец и абсолютная строка)
$A1 (абсолютный столбец и относительная строка)
A1 (относительный столбец и относительная строка)
Если ссылки на ячейки в формуле не дают нужного результата, попробуйте переключиться на другие типы ссылок.
Выберите ячейку с формулой.
Выберите ссылку, которую хотите изменить, в строке формул:
Нажмите F4, чтобы переключиться между абсолютной и относительной комбинацией ссылок, и выберите нужную.
Переместить формулу в другое место
В отличие от копирования формулы, при перемещении формулы в другое место на том же или другом листе ссылки на ячейки в формуле не меняются, независимо от того, какой тип ссылки на ячейку вы использовали.
Выберите ячейку с формулой, которую вы хотите переместить.
Нажмите на ячейку, в которую хотите вставить формулу.
Если эта ячейка находится на другом листе, перейдите на этот лист и щелкните нужную ячейку.
Чтобы вставить формулу с ее форматированием, на вкладке "Главная" нажмите "Вставить" или нажмите + V.
Сохранить исходное форматирование, чтобы вставить формулу, числовое форматирование, шрифт, размер шрифта, границу и заливку исходной ячейки.
Значения и исходное форматирование, чтобы вставить только значения и форматирование. Формула будет исключена.
Например, вам нужно скопировать формулы в выбранном диапазоне из текущей книги в другую книгу без ссылки, как это легко решить в Excel? Здесь мы поделимся с вами тремя методами.
- Копировать формулы из одной книги в другую без ссылки, изменяя формулы (6 шагов)
- Скопируйте формулы из одной книги в другую без ссылки, изменив формулы на текст (3 шага)
- Копировать формулы из одной книги в другую без ссылки с помощью точного копирования (3 шага)
- Копировать формулы из одной книги в другую без ссылки с помощью автотекста.
Копировать формулы из одной книги в другую без ссылки, изменяя формулы
Чтобы предотвратить изменение ссылок на формулы во время копирования, мы можем немного изменить формулы, а затем скопировать. Вы можете сделать следующее:
Примечание. Вы также можете открыть диалоговое окно «Найти и заменить», одновременно нажав клавиши Ctrl + H.
Теперь открывается диалоговое окно Microsoft Excel, в котором сообщается, сколько замен было сделано. Просто нажмите кнопку ОК, чтобы закрыть его. И закройте диалоговое окно «Найти и заменить». <р>3. Продолжайте выбирать диапазон, копируйте их и вставляйте в целевую книгу. <р>4. Продолжайте выбирать вставленные формулы и откройте диалоговое окно «Найти и заменить», нажав Главная > Найти и выбрать > Заменить.
Примечания:
(1) Этот метод требует открытия как рабочей книги с формулами, из которых вы будете копировать, так и целевой рабочей книги, в которую вы будете вставлять.
(2) Этот метод изменит формулы в исходной книге. Вы можете восстановить формулы в исходной книге, выбрав их и повторив шаги 6 и 7 выше.
Легко объединяйте несколько листов/книг в один лист/книгу
Может быть утомительно объединять десятки листов из разных книг в один лист. Но с Kutools for Excel’s Combine (рабочие листы и книги) утилита, вы можете сделать это всего за несколько кликов! Полнофункциональная бесплатная пробная версия на 30 дней!
Скопируйте формулы из одной книги в другую без ссылки, заменив формулы текстом
На самом деле, мы можем быстро преобразовать формулу в текст с помощью функции преобразования формулы в текст Kutools for Excel одним щелчком мыши. А затем скопируйте текст формулы в другую книгу и, наконец, преобразуйте текст формулы в настоящую формулу с помощью функции преобразования текста в формулу Kutools for Excel.
Kutools for Excel — включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия на 30 дней, кредитная карта не требуется! Получить сейчас
<р>2. Теперь выбранные формулы преобразуются в текст. Скопируйте их, а затем вставьте в целевую книгу. <р>3. Продолжайте выбирать вставленный текст, а затем нажмите Kutools > Контент > Преобразовать текст в формулу, чтобы преобразовать вставленный текст в формулу.
Kutools for Excel — включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия на 30 дней, кредитная карта не требуется! Получить сейчас
Копировать формулы из одной книги в другую без ссылки с помощью Exact Copy
Этот метод представит утилиту Exact Copy для Kutools for Excel, которая поможет вам с легкостью точно скопировать несколько формул в новую книгу.
Kutools for Excel — включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия на 30 дней, кредитная карта не требуется! Получить сейчас
<р>3. Теперь откроется второе диалоговое окно «Точное копирование формулы», перейдите в целевую книгу, выберите пустую ячейку и нажмите кнопку «ОК». См. снимок экрана выше.
Примечания.
(1) Если вы не можете переключиться на целевую книгу, введите адрес назначения, например [Book1]Sheet1!$H$2, в диалоговое окно выше. . (Книга1 — это имя целевой книги, Лист1 — это имя конечного рабочего листа, $H$2 — ячейка назначения);
(2) Если вы установили вкладку Office (есть бесплатная пробная версия), вы можете легко переключиться на целевую книгу нажав на вкладку.
(3) Этот метод требует открытия как рабочей книги с формулами, из которых вы будете копировать, так и целевой рабочей книги, в которую вы будете вставлять данные.
Kutools for Excel — включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия на 30 дней, кредитная карта не требуется! Получить сейчас
Копировать формулы из одной книги в другую без ссылки с помощью автотекста
Иногда может потребоваться просто скопировать и сохранить сложную формулу в текущей книге, а затем вставить ее в другие книги в будущем. Утилита Kutools for Excel Auto Text позволяет копировать формулу в виде автоматической текстовой записи и позволяет повторно использовать ее в других книгах одним щелчком мыши.
Kutools for Excel — включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия на 30 дней, кредитная карта не требуется! Получить сейчас
<р>2. В области навигации щелкните в крайнем левом углу области навигации, чтобы переключиться на область автотекста, щелкните, чтобы выбрать группу «Формулы», а затем нажмите кнопку «Добавить» вверху. См. снимок экрана ниже:
<р>3. В открывшемся диалоговом окне «Новый автоматический текст» введите имя для записи автоматического текста формулы и нажмите кнопку «Добавить». См. снимок экрана выше. <р>4. Откройте или переключитесь на целевую книгу, выберите ячейку, а затем щелкните автоматический текст формулы, формула будет сразу вставлена в выбранную ячейку.
Автоматический текст формулы можно повторно использовать в любое время в любой книге одним щелчком мыши.
Kutools for Excel — включает более 300 удобных инструментов для Excel.Полнофункциональная бесплатная пробная версия на 30 дней, кредитная карта не требуется! Получить сейчас
Демонстрация: копирование формул из одной книги в другую без ссылки
Kutools for Excel включает в себя более 300 удобных инструментов для Excel, которые можно бесплатно попробовать без ограничений в течение 30 дней. Загрузите и получите бесплатную пробную версию прямо сейчас!
Одновременно копировать и вставлять ширину столбца и высоту строки только между диапазонами/листами в Excel
Если для диапазона задана нестандартная высота строки и ширина столбца, как можно быстро применить высоту строки и ширину столбца этого диапазона к другим диапазонам/листам в Excel? Kutools for Excel's Copy Ranges утилита может помочь вам сделать это легко! Полнофункциональная бесплатная пробная версия на 30 дней!
При работе в Excel одной из наиболее распространенных проблем, с которыми мы сталкиваемся, является копирование листа Excel с формулами из одной книги в другую. Сегодня я покажу вам, как скопировать один или несколько листов с формулами из одной книги в другую с надлежащими иллюстрациями.
Как скопировать лист Excel с формулами в другую книгу
1. Скопируйте лист Excel с формулами в другую книгу, перетащив
У нас есть рабочий лист с именем Sheet1 в рабочей книге Workbook_1.
Теперь мы хотим скопировать этот Sheet1 из Workbook_1 в Workbook_2.
Шаг 1. Откройте две книги на своем компьютере.
Книга, которую вы хотите скопировать, и книга, в которую вы хотите скопировать.
Шаг 2. На панели инструментов Excel выберите параметр «Просмотр рядом» на вкладке «Вид».
Шаг 3. Выберите параметр «Просмотреть рядом». Это расположит две книги по вертикали следующим образом.
Шаг 4. Теперь нажмите Ctrl на клавиатуре и перетащите Лист1 из Workbook_1 в Workbook_2.
Он будет переименован с тем же именем в исходной книге в целевую книгу. Как и в моем случае, он был переименован в Sheet1(2) в Workbook_2.
Примечание. Если вы не нажмете Ctrl и продолжите перетаскивание, лист будет скопирован в целевую книгу, но будет потерян из исходной книги. Подобно вырезанию и вставке, которые мы делаем на наших компьютерах. Так что будьте осторожны.
Видите, вы успешно скопировали один лист Excel из одной книги в другую. Все, включая формулы Workbook_1, скопировано в Workbook_2.
2. Скопируйте лист Excel с формулами в другую книгу, нажав Ctrl + C и Ctrl + V
Если вы не хотите выполнять описанные выше шаги, вы можете использовать эти шаги и легко скопировать лист Excel из одной записной книжки в другую.
Шаг 1. Откройте рабочую книгу, из которой вы хотите скопировать лист.
Здесь я открываю Workbook_1.
Шаг 2. Нажмите на маленький треугольник в верхнем левом углу рабочего листа или нажмите Ctrl + A. Весь рабочий лист будет выделен следующим образом:
Шаг 3. Нажмите Ctrl + C на клавиатуре.
ИЛИ
Щелкните правой кнопкой мыши и выберите Копировать.
ИЛИ
Нажмите кнопку «Копировать» на вкладке «Главная» на панели инструментов Excel. См. рисунок.
< бр />
Шаг 4. Вы увидите, что граница листа выделена вот так. Это означает, что вы успешно скопировали рабочий лист.
Шаг 5. Теперь откройте вторую книгу (книгу, в которую вы хотите скопировать лист) и выберите самую верхнюю левую ячейку на листе этой книги.
Здесь я открыл Sheet1 из Workbook_2 и выбрал самую верхнюю левую ячейку.
Шаг 6. Теперь нажмите Ctrl + V на клавиатуре.
ИЛИ
Щелкните правой кнопкой мыши и выберите Вставить.
ИЛИ
Выберите параметр «Вставить» в крайнем левом углу вкладки «Главная» панели инструментов Excel. См. рисунок.
Шаг 7. Вы найдете все, начиная с Sheet1 Workbook_1, скопированного на Sheet1 Workbook_2.
Итак, вы успешно скопировали один лист из книги в лист другой книги. Все, включая формулы Workbook_1, скопировано в Workbook_2.
3. Скопируйте лист Excel с формулами в другую книгу с помощью диалогового окна «Переместить или скопировать»
Если вы не хотите использовать два вышеуказанных метода, вы можете использовать этот метод для копирования одного листа из книги Excel в лист другой книги.
Шаг 1. Наведите курсор мыши на Лист1 книги Workbook_1.
Шаг 2. Щелкните правой кнопкой мыши.
Затем выберите вариант «Переместить или скопировать».
Шаг 3. Появится небольшое поле с названием «Переместить или скопировать».
Изображение 13.
Шаг 4. В разделе В книги выберите книгу, в которую вы хотите скопировать. Я хочу скопировать в workbook_2, поэтому выбираю workbook_2.
И в параметре «Перед листом» выберите, где вы хотите разместить лист в Workbook_1.
Я хочу поместить его перед Листом1, поэтому выбираю Лист1.
И необходимо проверить параметр создания копии. (Если вы не отметите опцию создания копии, лист будет потерян из исходной книги. Будьте осторожны.)
Итак, моя коробка теперь выглядит так:
Примечание. Исходная книга (Workbook_1) и целевая книга (Workbook_2) должны оставаться в одной папке на вашем компьютере. В противном случае вы не найдете целевую книгу в поле выше.
Шаг 5. После успешного выполнения описанных выше шагов вы обнаружите, что в целевой книге создана копия листа из исходной книги.
Здесь, в моем случае, копия Листа 1 из Workbook_1 была создана в Workbook_2 (Лист1(2))
4. Скопируйте несколько листов Excel с формулами в другую книгу
Все перечисленные выше методы можно использовать для одновременного копирования одного листа из одной книги в другую.
Если вы хотите одновременно скопировать несколько листов из одной книги в другую, выполните следующие действия.
До этого у нас есть рабочая книга Workbook_1, которая на этот раз состоит из двух рабочих листов.
Лист1 и Лист2.
На этот раз мы скопируем оба листа в Workbook_2.
Шаг 1. Откройте две книги и выберите параметр «Просмотр рядом» на вкладке «Вид». Это расположит две книги по вертикали следующим образом.
Шаг 2. Дважды щелкните любой лист в левом нижнем углу исходной книги. Затем нажмите Ctrl и щелкните другие листы.
Здесь я сначала нажал на Sheet1 Workbook_1, а затем на Sheet2.
Если вам удастся это сделать, в верхнем левом углу будет написано Workbook_1 [Group].
< /p>
Шаг 3. Нажмите Ctrl и перетащите рабочие листы в целевую книгу (Workbook_2).
Не забудьте нажать Ctrl. В противном случае он будет скопирован в целевую книгу, но потерян из исходной книги. Например, функция «Вырезать и вставить» на компьютере.
Здесь, в моем случае, Sheet1 и Sheet2 были скопированы из Workbook_1 в Workbook_2.
5. Скопируйте лист Excel с формулами в другую книгу, сохранив ссылку
Давайте добавим новый столбец в наш набор данных.
Столбец E, который содержит средние оценки по физике и химии.
Поэтому он содержит формулу:
Теперь, если я скопирую этот лист в другую книгу любым способом, формулы будут скопированы.
Но любое изменение в Workbook_1 не приведет к такому же изменению в Workbook_2.
Например, если я изменю оценку первого учащегося по физике с 75 до 77 в Workbook_1, средняя оценка изменится с 77 до 78 в Workbook_1.
Но не изменится в Workbook_2.
Тогда как это исправить?
Теперь я скопировал лист из этой книги в другую книгу, Workbook_2, любым из способов, упомянутых выше.
Теперь внимательно посмотрите на функциональную панель скопированного Листа в Workbook_2.
< бр />
Excel автоматически помещает имя исходной книги перед именем листа. В моем случае это [Workbook_1.xlsx].
Теперь это означает, что ссылка на ячейку теперь принадлежит Workbook_1, хотя она находится в Workbook_2.
Теперь, если мы изменим что-либо в Workbook_1, эквивалентное изменение будет внесено в Workbook_2.
Таким образом, вы можете создать связь между исходным рабочим листом и скопированным рабочим листом в Excel. Просто поместите [Имя листа!] перед ссылкой на ячейку в исходном листе.
Заключение
Таким образом, используя эти методы, вы можете копировать один или несколько листов из одной книги в другую книгу. Вы знаете какой-нибудь другой метод? Дайте нам знать в разделе комментариев.
Читайте также: