Как реализовать функции копирования и перемещения в Excel

Обновлено: 05.07.2024

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

Эта глава из книги

Эта глава из книги 

В этой главе

Основные сведения о формулах

Приоритет операторов

Управление вычислениями на рабочем листе

Копирование и перемещение формул

Отображение формул рабочего листа

Преобразование формулы в значение

Работа с именами диапазонов в формулах

Работа со ссылками в формулах

Форматирование чисел, дат и времени

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

Основные сведения о формулах

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

Excel не будет возражать, если вы используете пробелы между операторами и операндами в формулах. На самом деле это хорошая практика, потому что разделение элементов формулы таким образом может значительно облегчить их чтение. Также обратите внимание, что Excel также допускает разрывы строк в формулах. Это удобно, если у вас очень длинная формула, поскольку позволяет «разбить» формулу, чтобы она отображалась на нескольких строках. Чтобы создать разрыв строки в формуле, нажмите клавиши ALT+ВВОД.

Ввод и редактирование формул

Ввод новой формулы на лист кажется простым процессом:

Выберите ячейку, в которую вы хотите ввести формулу.

Введите знак равенства (=), чтобы указать Excel, что вы вводите формулу.

Введите операнды и операторы формулы.

Нажмите Enter, чтобы подтвердить формулу.

Однако в Excel есть три разных режима ввода, которые определяют, как Excel интерпретирует определенные нажатия клавиш и действия мыши:

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

Если нажать любую клавишу навигации на клавиатуре (например, Page Up, Page Down или любую клавишу со стрелкой) или щелкнуть любую другую ячейку на листе, Excel перейдет в режим точки. Этот режим используется для выбора ячейки или диапазона в качестве операнда формулы. Когда вы находитесь в точечном режиме, вы можете использовать любой из приемов выбора диапазона, которые вы изучили в главе 1 «Получение максимальной отдачи от диапазонов». Обратите внимание, что Excel возвращается в режим ввода, как только вы вводите оператор или любой символ.

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

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

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

Дважды щелкните ячейку.

Используйте строку формул, чтобы щелкнуть в любом месте текста формулы.

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

Использование арифметических формул

Арифметические формулы — наиболее распространенный тип формул. Они объединяют числа, адреса ячеек и результаты функций с математическими операторами для выполнения вычислений. В таблице 3.1 приведены математические операторы, используемые в арифметических формулах.

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

При перемещении или копировании ячейки Excel перемещает или копирует ячейку вместе с формулами и их результирующими значениями, форматами ячеек и комментариями.

Ячейки в Excel можно перемещать путем перетаскивания или с помощью команд "Вырезать" и "Вставить".

Перемещение ячеек путем перетаскивания

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

Наведите указатель мыши на границу выделения.

Когда указатель станет указателем перемещения , перетащите ячейку или диапазон ячеек в другое место.

Перемещение ячеек с помощью функций «Вырезать» и «Вставить»

Выберите ячейку или диапазон ячеек.

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

Выберите Главная > Вставить или нажмите Ctrl + V.

Скопируйте ячейки на листе с помощью команд "Копировать" и "Вставить".

Выберите ячейку или диапазон ячеек.

Выберите Копировать или нажмите Ctrl + C.

Выберите "Вставить" или нажмите Ctrl + V.

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

На вкладке "Главная" в группе "Буфер обмена" выполните одно из следующих действий:

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

Сочетание клавиш Можно также нажать Ctrl+X.

Чтобы скопировать ячейки, нажмите "Копировать" .

Сочетание клавиш Можно также нажать Ctrl+C.

Выберите верхнюю левую ячейку области вставки.

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

На вкладке "Главная" в группе "Буфер обмена" нажмите "Вставить" .

Сочетание клавиш Можно также нажать Ctrl+V.

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

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

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

По умолчанию Excel отображает кнопку «Параметры вставки» на листе, чтобы предоставить вам специальные параметры при вставке ячеек, например «Сохранить исходное форматирование». Если вы не хотите, чтобы эта кнопка отображалась каждый раз при вставке ячеек, вы можете отключить этот параметр. Перейдите на вкладку «Файл» и нажмите «Параметры». В категории "Дополнительно" в разделе "Вырезать, копировать и вставить" снимите флажок "Показывать параметры вставки при вставке содержимого".

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

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

Выполните одно из следующих действий:

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

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

Примечание. Excel заменяет существующие данные в области вставки при перемещении ячеек.

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

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

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

На вкладке "Главная" в группе "Буфер обмена" выполните одно из следующих действий:

Чтобы переместить выделение, нажмите Вырезать .

Сочетание клавиш Можно также нажать Ctrl+X.

Чтобы скопировать выделение, нажмите "Копировать" .

Сочетание клавиш Можно также нажать Ctrl+C.

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

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

Примечание. Если вы вставляете целые строки или столбцы, окружающие их строки и столбцы сдвигаются вниз и влево.

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

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

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

В разделе "Выбор" нажмите "Только видимые ячейки", а затем нажмите "ОК".

На вкладке "Главная" в группе "Буфер обмена" нажмите "Копировать" .

Сочетание клавиш Можно также нажать Ctrl+C.

Выберите верхнюю левую ячейку области вставки.

На вкладке "Главная" в группе "Буфер обмена" нажмите "Вставить" .

Сочетание клавиш Можно также нажать Ctrl+V.

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

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

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

Выберите диапазон ячеек, содержащий пустые ячейки.

На вкладке "Главная" в группе "Буфер обмена" нажмите "Копировать" .

Сочетание клавиш Можно также нажать Ctrl+C.

Выберите верхнюю левую ячейку области вставки.

На вкладке "Главная" в группе "Буфер обмена" нажмите стрелку под надписью "Вставить" и выберите "Специальная вставка".

Установите флажок Пропускать пробелы.

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

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

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

На вкладке "Главная" в группе "Буфер обмена" выполните одно из следующих действий:

Чтобы переместить выделение, нажмите Вырезать .

Сочетание клавиш Можно также нажать Ctrl+X.

Чтобы скопировать выделение, нажмите "Копировать" .

Сочетание клавиш Можно также нажать Ctrl+C.

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

На вкладке "Главная" в группе "Буфер обмена" нажмите "Вставить" .

Сочетание клавиш Можно также нажать Ctrl+V.

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

При вставке скопированных данных вы можете выполнить одно из следующих действий:

Вставьте только форматирование ячейки, например цвет шрифта или цвет заливки (но не содержимое ячеек).

Преобразование любых формул в ячейке в вычисляемые значения без перезаписи существующего форматирования.

Вставьте только формулы (но не расчетные значения).

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

На вкладке "Главная" в группе "Буфер обмена" нажмите "Копировать" .

Сочетание клавиш Можно также нажать Ctrl+C.

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

На вкладке "Главная" в группе "Буфер обмена" нажмите стрелку под надписью "Вставить" и выполните одно из следующих действий:

Чтобы вставить только значения, нажмите "Значения".

Чтобы вставить только форматы ячеек, нажмите "Форматирование".

Чтобы вставить только формулы, нажмите "Формулы".

Примечание. Если скопированные формулы содержат относительные ссылки на ячейки, Excel корректирует ссылки (и относительные части смешанных ссылок на ячейки) в повторяющихся формулах. Например, предположим, что ячейка B8 содержит формулу =СУММ(B1:B7). Если вы скопируете формулу в ячейку C8, повторяющаяся формула будет ссылаться на соответствующие ячейки в этом столбце: =СУММ(C1:C7). Если скопированные формулы содержат абсолютные ссылки на ячейки, ссылки в повторяющихся формулах не изменяются. Если вы не получили желаемых результатов, вы также можете изменить ссылки в исходных формулах на относительные или абсолютные ссылки на ячейки, а затем повторно скопировать ячейки.

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

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

На вкладке "Главная" в группе "Буфер обмена" выполните одно из следующих действий:

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

Сочетание клавиш Можно также нажать Ctrl+X.

Чтобы скопировать ячейки, нажмите "Копировать" .

Сочетание клавиш Можно также нажать Ctrl+C.

Выберите верхнюю левую ячейку области вставки.

На вкладке "Главная" в группе "Буфер обмена" нажмите стрелку под надписью "Вставить" и выберите "Сохранить ширину исходных столбцов".

Нужна дополнительная помощь?

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

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

Вот как копировать и вставлять формулу:

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

Нажмите на ячейку, в которую хотите вставить формулу.

Если эта ячейка находится на другом листе, перейдите на этот лист и щелкните нужную ячейку.

Чтобы быстро вставить формулу с ее форматированием, нажмите + V. Или вы можете нажать стрелку рядом с кнопкой Вставить:

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

Формулы — для вставки только формулы, а не форматирования исходной ячейки.

Формулы и форматирование чисел — вставка только формулы и форматирования чисел (например, формат процентов, формат валюты и т. д.).

Сохранить исходное форматирование — вставка формулы, форматирования чисел, шрифта, размера шрифта, границы и заливки исходной ячейки.

Вставить значения — чтобы исключить формулу и вставить только результат.

Другие варианты, которые могут быть вам полезны:

Без границ — чтобы вставить формулу, числовое форматирование, шрифт, размер шрифта, заливку, но не границу оригинала.

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

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

Проверьте и исправьте ссылки на ячейки в новом местоположении

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

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

Эта ссылка:

$A$1 (абсолютный столбец и абсолютная строка)

A$1 (относительный столбец и абсолютная строка)

$A1 (абсолютный столбец и относительная строка)

A1 (относительный столбец и относительная строка)

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

Выберите ячейку с формулой.

Выберите ссылку, которую хотите изменить, в строке формул:

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

Переместить формулу в другое место

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

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

Нажмите на ячейку, в которую хотите вставить формулу.

Если эта ячейка находится на другом листе, перейдите на этот лист и щелкните нужную ячейку.

Чтобы вставить формулу с ее форматированием, на вкладке "Главная" нажмите "Вставить" или нажмите + V.

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

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

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

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

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

Например, на этом рисунке предположим, что вы хотите скопировать формулу из B5 в C5. Новая формула в C5 должна относиться к значениям в столбце C, а не к столбцу B; иначе формула не имела бы особого смысла. Таким образом, когда формула B5 копируется в C5, она становится там =C3+C4.

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

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

Возможно, вам не всегда нужно, чтобы ссылки на ячейки в формуле менялись при перемещении или копировании. Другими словами, вам нужна абсолютная ссылка на эту ячейку. Чтобы сделать ссылку абсолютной, вы добавляете знак доллара перед буквой столбца и перед номером строки. Так, например, абсолютная ссылка на ячейку C1 будет =$C$1 .

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

Если вы хотите заблокировать только одно измерение ссылки на ячейку, вы можете поставить знак доллара только перед столбцом или только перед строкой. Например, =$C1 зафиксирует только букву столбца, а =C$1 зафиксирует только номер строки.

Об этой статье

Эта статья взята из книги:

Об авторе книги:

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

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