Автоматически копировать ячейки в Excel по условию

Обновлено: 21.11.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, который берет информацию из одного столбца на другом листе для автоматического обновления соответствующей строки или информации в мастер-листе.

Как скопировать строки на новый лист на основе критериев столбца в Excel?

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

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

Вы можете отфильтровать данные по указанному значению столбца, а затем скопировать отфильтрованные строки на новый лист вручную в Excel. Пожалуйста, сделайте следующее:

<р>1. Выберите столбец, на основе которого вы будете копировать строки, в нашем примере выберите столбец Fruit. Затем нажмите Данные > Фильтр.

<р>2. Щелкните стрелку рядом с указанным заголовком столбца, а затем отметьте только указанные фрукты в раскрывающемся списке и нажмите кнопку ОК.

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

<р>3. Теперь отфильтровываются только записи указанных фруктов. Скопируйте эти записи.

<р>4. Создайте новый лист, нажав кнопку или на панели вкладок листа.

<р>5. Затем вставьте скопированные записи в новый лист.

И тогда вы увидите, что все записи об Apple скопированы и сохранены в новый лист. См. снимок экрана ниже:

<р>6. Повторите шаги 2–5, чтобы скопировать другие записи фруктов на новые листы.

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

Скопируйте строки на новый лист на основе критериев столбца Kutools for Excel

Второй метод поможет вам пакетно скопировать строки на новый лист на основе критериев столбца с помощью утилиты Kutools for Excel Split Data.

Kutools for Excel — включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия на 30 дней, кредитная карта не требуется! Бесплатная пробная версия сейчас!

<р>1. Выберите диапазон, в который вы будете копировать строки на основе критериев столбца, и нажмите Kutools Plus > Разделить данные.

<р>2. В открывшемся диалоговом окне «Разделить данные на несколько рабочих листов» вам необходимо:

(1) Установите флажок Определенный столбец, щелкните раскрывающийся список ниже и укажите столбец, на основе которого вы будете копировать строки;

(2) Щелкните поле "Правила" и укажите значения столбца из раскрывающегося списка;

<р>3. Нажмите кнопку "ОК".

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

Kutools for Excel — включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия на 30 дней, кредитная карта не требуется! Получить сейчас

Демонстрация: копирование строк на новый лист на основе критериев столбца в Excel

Kutools for Excel включает в себя более 300 удобных инструментов для Excel, которые можно бесплатно попробовать без ограничений в течение 30 дней. Загрузите и получите бесплатную пробную версию прямо сейчас!

Одновременное копирование и вставка нескольких ячеек/диапазонов в Excel

Возможно, вы заметили, что Microsoft Excel не поддерживает одновременное копирование нескольких непоследовательных ячеек (оставающихся в разных столбцах). Но копирование этих ячеек/выборов по одной занимает много времени и утомительно! Утилита Kutools for Excel’s Copy Ranges может помочь сделать это легко, как показано ниже. Полнофункциональная бесплатная пробная версия на 30 дней!

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

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

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

Способ 1. Скопируйте результат фильтра

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

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

По завершении процесса фильтрации вы можете выполнить процесс копирования и вставки.

Выберите результат на листе.
Затем нажмите сочетание клавиш «Ctrl + C» на клавиатуре, чтобы скопировать диапазон.
Теперь щелкните другую ячейку на листе.
Затем нажмите сочетание клавиш «Ctrl + V», чтобы вставить эти ячейки.
Затем нажмите кнопку «Esc», чтобы выйти из режима копирования.

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

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

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

Способ 2. Использование макроса VBA

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

Нажмите сочетание клавиш «Alt + F11» на клавиатуре, чтобы открыть редактор Visual Basic.
Затем нажмите вкладку "Вставка" на панели инструментов.
После этого выберите пункт «Модуль» в подменю. Поэтому вы вставили новый модуль в этот проект.
На этом шаге скопируйте следующие коды VBA в новый модуль.

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

Здесь нажмите кнопку «Выполнить Sub» на панели инструментов или нажмите кнопку «Enter» на клавиатуре, чтобы запустить макрос.

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

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

Сравнение двух методов

В этой таблице мы покажем вам преимущества и недостатки двух методов.

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

Безопасность Excel вызывает беспокойство

Безопасность Excel очень важна для вас, и вам следует уделять больше внимания безопасности Excel. Многие пользователи потеряли свои данные и информацию в Excel по разным причинам. Если вы не можете гарантировать безопасность своих файлов, вы наверняка сильно пострадаете. Результат повреждения Excel может быть катастрофическим. Всякий раз, когда вы сталкиваетесь с ошибками в Excel, вы можете исправить повреждение данных Excel xls с помощью полезного стороннего инструмента. с помощью этого инструмента вы никогда не потеряете свои данные и информацию.

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

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

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

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

Примечание. С помощью этого метода можно выделить и скопировать несколько ячеек.

Кроме того, есть более простой способ сделать это:

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

Как скопировать и вставить условное форматирование?

Как и в предыдущем примере, вам нужно будет использовать параметр «Специальная вставка…». Чтобы скопировать и вставить ячейки с условным форматированием, выполните следующие действия:

Еще один способ копирования и вставки условного форматирования – использование параметра "Формат по образцу":

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

Кроме того, условное форматирование можно вставлять несколько раз. На шаге 3 дважды щелкните кнопку «Формат по образцу». Завершив вставку условного форматирования, снова нажмите кнопку, чтобы отключить функцию вставки.

Дополнительные часто задаваемые вопросы

Как отображать формулы в Excel вместо значений?

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

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

Создает ли Microsoft Excel точную копию формулы?

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

<р>2. В строке формул над таблицей выделите формулу и нажмите Ctrl + C (копировать).

<р>3. Выберите ячейку, к которой вы хотите применить формулу, и нажмите Ctrl + V (вставить).

Примечание. Если при двойном щелчке по ячейке курсор не появляется в ячейке, необходимо включить режим редактирования. Выберите «Файл» > «Параметры» > «Дополнительно» и в разделе «Параметры редактирования» установите флажок «Разрешить редактирование непосредственно в ячейках».

Существует ярлык для копирования формулы одной ячейки в несколько ячеек. Однако это применимо только в том случае, если ячейки расположены рядом друг с другом:

<р>2. Нажмите и перетащите курсор на соседние ячейки, в которые вы хотите скопировать формулу.

<р>3. Отпустите курсор, когда выделите ячейки.

Теперь формула применяется к группе ячеек.

Как заменить значения в Excel?

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

<р>2. Перейдите на вкладку «Главная» на ленте. В разделе «Редактирование» нажмите кнопку «Найти и выбрать».

<р>4. Теперь нажмите «Найти…» в раскрывающемся меню, чтобы открыть новое всплывающее окно.

<р>5. В диалоговом окне "Найти и заменить" выберите вкладку "Заменить".

<р>6. Теперь введите значение, которое должен найти Excel, в текстовом поле «Найти», а в текстовом поле «Заменить на» введите значение замены.

Примечание. Шаги 1–3 можно заменить сочетанием клавиш Ctrl + H.

Теперь вы можете сделать две вещи. Чтобы заменить значение только в одной ячейке, выполните следующие действия:

<р>2. Нажмите кнопку «Заменить», чтобы заменить значение этой ячейки новым значением.

Если вы хотите заменить все значения в выбранном диапазоне ячеек:

<р>2. Нажмите «Заменить все», чтобы заменить все старые значения новыми.

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

Почему Excel копирует значение, а не формулу?

По какой-то причине ваш Excel настроен на ручной пересчет. Вы должны вернуть это в автоматический режим:

<р>3. Теперь нажмите «Автоматически».

Как скопировать значение и формат в Excel?

Для этого можно использовать функцию "Специальная вставка":

<р>2. Нажмите Ctrl + C, а затем щелкните правой кнопкой мыши ячейку, в которую вы хотите вставить значения и форматы.

<р>3. Поместите курсор на маленькую стрелку рядом с Специальная вставка. В расширенном меню в меню «Вставить значения» выберите третий параметр в строке («Значения и исходное форматирование»).

Как показать значение в Excel?

Если значение ячейки скрыто и вы не видите строку формул, это значение можно отобразить следующим образом:

<р>2. Перейдите на вкладку «Вид» на ленте.

<р>3. В разделе Показать установите флажок Панель формул.

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

<р>2. Перейдите на вкладку Главная на ленте.

<р>3. В разделе "Число" нажмите на маленькую кнопку со стрелкой в правом нижнем углу.

<р>4. Выберите «Пользовательский» в разделе «Категория».

<р>5. Прокрутите ползунок вниз, вы должны увидеть запись с одной или несколькими точками с запятой («;»). Выберите эту запись и нажмите «Удалить».

Теперь должны появиться все скрытые значения в выбранном диапазоне ячеек.

Копирование значения в Excel без формулы

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

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

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

Были ли у вас когда-нибудь проблемы с копированием значений в Excel? Если да, то как вы подошли к этому вопросу? Дайте нам знать в разделе комментариев ниже.

Читайте также: