Как исправить несколько ячеек в Excel

Обновлено: 21.11.2024

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

Видео: Проблемы с копированием и вставкой

В этом видео показаны две проблемы, которые могут возникнуть в Excel при копировании и вставке нескольких элементов, а также способы их устранения:

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

Письменные шаги находятся под видео.

Временная шкала видео

0:00 — Введение
0:15 — Копировать сообщение об ошибке
0:42 — избегайте сообщения об ошибке
1:54 — Проблема со вставкой
2:40 — Скопируйте 2 диапазона
3:23 — Вставка с помощью формул
4:13 — Получить рабочую тетрадь

Ошибка копирования для нескольких вариантов

Если вы выберете несколько групп ячеек на листе и попытаетесь их скопировать, Excel может отобразить сообщение об ошибке:

"Это действие не будет работать при множественном выборе"

Или в некоторых версиях Excel вместо этого вы увидите следующее сообщение об ошибке:

"Эту команду нельзя использовать для множественного выбора"

Почему вы видите ошибку копирования

Excel покажет это сообщение об ошибке копирования, если вы выбрали несколько диапазонов, и эти диапазоны не «совпадают».

Например, Excel покажет предупреждающее сообщение, если вы

выбрать ячейки в столбцах A:C
нажмите Ctrl и сделайте еще один выбор в разных столбцах, A:B
нажмите Ctrl+C, чтобы скопировать выбранные ячейки (или используйте другой метод для копирования)

Избегайте сообщения об ошибке копирования Excel

Чтобы избежать сообщения об ошибке копирования, обязательно выберите несколько регионов, которые ДЕЙСТВИТЕЛЬНО "совпадают".

Все выбранные регионы, которые вы хотите скопировать, должны находиться либо в:

те же столбцы
ИЛИ одинаковые строки

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

На снимке экрана ниже показан пример двух вариантов выбора, которые ДЕЙСТВИТЕЛЬНО совпадают.

оба варианта находятся в одних и тех же столбцах -- A:C
выборы имеют разные строки, и это нормально

При копировании этого множественного выбора не появится сообщение об ошибке

Вставить задачу для нескольких вариантов

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

ПРИМЕЧАНИЕ. Эта проблема может привести к серьезным проблемам, поскольку Excel не выводит предупреждающее сообщение. Если вы сразу не заметите, что формулы были изменены на значения, позже в вашей книге могут быть серьезные ошибки.

Пример проблемы с вставкой

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

На одном листе была именованная таблица Excel, в некоторых столбцах которой были формулы

В столбце G стоимость продукта была рассчитана по формуле ВПР.
В столбце H общая стоимость была рассчитана путем умножения количества на стоимость.

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

Сначала я скопировал заголовок и первые две строки данных (A1:H3)
На новом листе я выбрал ячейку A2 и вставил эти данные.
Затем я вернулся к столу и выбрал A5:H6
Затем я нажал клавишу Ctrl и выделил ячейки A8:H8.

Вернувшись на новый лист, я выбрал ячейку A4 и вставил эти данные.

Несколько минут спустя на новом листе я заметил проблему:

В ячейке F4 я изменил количество с 10 на 15.
Общая сумма в ячейке H4 не изменилась – она по-прежнему показывает общую стоимость 10 товаров.

Для устранения проблемы я выполнил следующие действия:

щелкнул вкладку "Формула" в верхней части Excel.
нажал команду "Показать формулы".

Вот скопированные данные с включенным параметром "Показать формулы"

В строках 2 и 3 были формулы

был скопирован и вставлен один диапазон

скопировано и вставлено несколько выделенных диапазонов

Как вставить формулы

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

Скопируйте несколько вариантов
Щелкните правой кнопкой мыши ячейку, с которой вы хотите начать вставку.
Во всплывающем меню нажмите "Специальная вставка".
В окне "Специальная вставка" нажмите "ОК".

При использовании этого метода все формулы также будут вставлены.

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

Получить образец файла

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

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel для Windows Phone 10 Еще. Меньше

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

В приведенном ниже примере таблицы мы хотим умножить все числа в столбце A на число 3 в ячейке C2. Формула =A2*C2 даст правильный результат (4500) в ячейке B2. Но копирование формулы вниз по столбцу B не сработает, потому что ссылка на ячейку C2 изменится на C3, C4 и так далее. Поскольку в этих ячейках нет данных, результат в ячейках с B3 по B6 будет равен нулю.

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

Использование символов $ сообщает Excel, что ссылка на C2 является «абсолютной», поэтому при копировании формулы в другую ячейку ссылка всегда будет на ячейку C2. Чтобы создать формулу:

В ячейке B2 введите знак равенства (=).

Нажмите ячейку A2, чтобы ввести ячейку в формулу.

Введите звездочку (*).

Нажмите ячейку C2, чтобы ввести ячейку в формулу.

Теперь введите символ $ перед C и символ $ перед 2: $C$2.

Совет. Вместо ввода символа $ можно поместить точку вставки до или после ссылки на ячейку, которую вы хотите сделать «абсолютной», и нажать клавишу F4, чтобы добавить символы $.

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

Дважды щелкните маленький зеленый квадратик в правом нижнем углу ячейки.

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

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

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

<р>1. Нажмите одновременно клавиши Alt + F11, чтобы открыть окно Microsoft Visual Basic для приложений.

<р>2. В окне Microsoft Visual Basic для приложений щелкните Вставить > Модуль. Затем скопируйте и вставьте приведенный ниже код VBA в окно модуля.

Код VBA: блокировка всех ссылок на ячейки в формулах одновременно

<р>3. Нажмите клавишу F5, чтобы запустить код, затем появится диалоговое окно Kutools for Excel, выберите диапазон ячеек с формулами, которые вам нужны, чтобы заблокировать ссылки внутри, и, наконец, нажмите OK кнопка. Смотрите скриншот:

Затем все ссылки на ячейки сразу блокируются в выбранных ячейках формулы. Смотрите скриншот:

Легко заблокировать все ссылки на ячейки в формулах сразу с помощью Kutools for Excel

Утилита Convert Refers в Kutools for Excel помогает быстро заблокировать все ссылки на ячейки в формулах сразу несколькими щелчками мыши.

Перед применением Kutools for Excel сначала загрузите и установите его.

<р>1. Выберите ячейки формулы, которые нужно заблокировать, затем нажмите Kutools > Еще > Конвертировать ссылки.

<р>2. В диалоговом окне "Преобразовать ссылки на формулы" выберите параметр "В абсолютные" в разделе "Преобразовать ссылки на формулы" и нажмите кнопку "ОК".

Затем все ссылки на ячейки блокируются (преобразовываются в абсолютные ссылки) в выбранных ячейках формулы одновременно. Смотрите скриншот:

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

При работе с электронными таблицами необходимо знать об относительных и абсолютных ссылках на ячейки.

Вот проблема: когда вы КОПИРУЕТЕ ФОРМУЛУ, содержащую ссылки на ячейки, что происходит со ссылками на ячейки?

Обычно ССЫЛКИ НА КЛЕТКИ ИЗМЕНЯЮТСЯ! Если скопировать формулу на 2 строки вправо, то ссылки на ячейки в формуле сместятся на 2 ячейки вправо. Если скопировать формулу на 3 строки вниз и на 1 строку влево, то ссылки на ячейки в формуле сместятся на 3 строки вниз и на 1 строку влево. Они называются относительными ссылками на ячейки, поскольку они изменяются относительно того места, куда вы копируете формулу.

Если вы не хотите, чтобы ссылки на ячейки менялись при копировании формулы, сделайте эти ссылки на ячейки абсолютными ссылками на ячейки. Поместите «$» перед буквой столбца, если вы хотите, чтобы она всегда оставалась неизменной. Поместите «$» перед номером строки, если вы хотите, чтобы он всегда оставался неизменным. Например, «$C$3» относится к ячейке C3, а «$C$3» будет работать точно так же, как «C3», если вы скопируете формулу. Примечание: при вводе формул вы можете использовать клавишу F4 сразу после ввода ссылки на ячейку для переключения между различными относительными/абсолютными версиями этого адреса ячейки.

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

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

Относительные и абсолютные ссылки на ячейки

Карин Стилл

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

Относительные ссылки на ячейки

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

=СУММ(B5:B8), как показано ниже, изменяется на =СУММ(C5:C8) при копировании в следующую ячейку.

Абсолютные ссылки на ячейки

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

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

Более сложный пример:

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

Проверьте формулу в ячейке E4. Сделав ссылку на первую ячейку $C4, вы предотвратите изменение столбца при копировании, но позволите строке измениться при копировании вниз, чтобы приспособиться к снижению цен на различные товары. Сделав ссылку на последнюю ячейку A$12, вы предотвратите изменение номера строки при копировании вниз, но позволите столбцу измениться и отразить скидку B при копировании. Смущенный? Посмотрите на рисунок ниже и результаты ячеек.

Теперь вы можете подумать, а почему бы просто не использовать 10% и 15% в реальных формулах? Разве это не было бы проще?Да, если вы уверены, что процент скидки никогда не изменится, что крайне маловероятно. Скорее всего, в конечном итоге эти проценты необходимо будет скорректировать. Ссылаясь на ячейки, содержащие 10% и 15%, а не на фактические числа, когда процентное значение изменяется, все, что вам нужно сделать, это изменить процентное значение один раз в ячейке A12 и/или B12 вместо того, чтобы перестраивать все ваши формулы. Excel автоматически обновит цены со скидками, чтобы отразить изменение процента скидки.

Краткий обзор использования абсолютных ссылок на ячейки:

$A1	Разрешает изменение ссылки на строку, но не ссылка на столбец.
A$1	Разрешает изменять ссылку на столбец, но не на строку.
$A$1	Не позволяет изменять ни столбец, ни ссылку на строку.

Существует ярлык для размещения абсолютных ссылок на ячейки в ваших формулах!

При вводе формулы после ввода ссылки на ячейку нажмите клавишу F4. Excel автоматически делает ссылку на ячейку абсолютной! Продолжая нажимать F4, Excel будет циклически перебирать все возможности абсолютной ссылки. Например, в первой формуле абсолютной ссылки на ячейку в этом руководстве, =B4*$B$10, я мог бы ввести =B4*B10, а затем нажать клавишу F4, чтобы изменить B10 на $B$10. Продолжая нажимать F4, вы получите 10 B$, затем B10 и, наконец, B10. Нажатие F4 изменяет только ссылку на ячейку непосредственно слева от точки вставки.

Я надеюсь, что этот учебник сделал эти типы ссылок на ячейки «абсолютно» понятными!

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