Главная » 2015 » Август » 24 » Как копировать формулы в Excel
05:10
Как копировать формулы в Excel

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

3 методика:Копирование формулы в несколько ячеекТочное копирование формулыИспользование относительных и абсолютных ссылок на ячейки

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

Шаги

Метод 1 из 3: Копирование формулы в несколько ячеек

  1. 1 Введите формулу в одну ячейку. Начните с «=», а затем введите нужную вам функцию. Рассмотрим следующий пример, где сложим значения столбца А и столбца B: Пример таблицы Столбец A Столбец B Столбец C
    строка 1 10 9 =A1+B1
    строка 2 20 8
    строка 3 30 7
    строка 4 40 6
  2. 2 Нажмите Enter, чтобы формула сработала и отобразился результат вычислений. В нашем примере отобразится только результат: 19, но формула будет сохранена в таблице. Пример таблицы Столбец A Столбец B Столбец C
    строка 1 10 9 19
    строка 2 20 8
    строка 3 30 7
    строка 4 40 6
  3. 3 Щелкните по ячейке, в которую вы вводили формулу. В ее правом нижнем углу отобразится значок «+».[1]
  4. 4 Удерживая значок «+», перетащите ячейку по строке или столбцу, куда вы хотите скопировать формулу. Введенная формула автоматически скопируется и изменится согласно адресам соответствующих ячеек. В нашем примере (отображаются автоматически измененные формулы и результаты вычислений): Пример таблицы Столбец A Столбец B Столбец C
    строка 1 10 9 =A1+B1
    строка 2 20 8 =A2+B2
    строка 3 30 7 =A3+B3
    строка 4 40 6 =A4+B4


    Пример таблицы Столбец A Столбец B Столбец C
    строка 1 10 9 19
    строка 2 20 8 28
    строка 3 30 7 37
    строка 4 40 6 46
  5. 5 Щелкните по ячейке, в которую вы вводили формулу. В ее правом нижнем углу отобразится значок «+». Дважды щелкните по знаку «+», чтобы автоматически скопировать введенную формулу во все ячейки данного столбца.[2]
    • Автоматическое копирование остановится при достижении пустой ячейки. Если в ваших данных есть разрывы (пустые ячейки), повторите процесс копирования, начав с ячейки, следующей после пустой.

Метод 2 из 3: Точное копирование формулы

  1. 1 Используйте этот метод, чтобы быстро скопировать формулу без ее автоматического изменения (то есть без изменения адресов соответствующих ячеек). В этом случае не стоит вставлять абсолютные ссылки на ячейки (как описано в следующем разделе), так как это утомительно, особенно если вы хотите изменить их позже.[3] Вот новый пример, в котором столбец C нужно скопировать в столбец D (без изменений): Пример таблицы Столбец A Столбец B Столбец C Столбец D
    строка 1 944 Лягушки =A1/2
    строка 2 636 Жабы =A2/2
    строка 3 712 Тритоны =A3/2
    строка 4 690 Змеи =A4/2
    • Если вы просто пытаетесь скопировать формулу в одну ячейку, перейдите к последнему шагу этого раздела.
  2. 2 Откройте окно «Найти». Для этого в большинстве версий Excel перейдите на вкладку «Главная» и нажмите «Найти и выделить» в группе «Редактирование». [4]
  3. 3 Введите «=», нажмите «Найти все», затем введите другой символ в строке «Заменить на». Это автоматически изменит все формулы (которые всегда начинаются со знака «=») в текстовые строки, начинающиеся с какого-либо другой знака. Всегда используйте символ, который не используется в таблице. Например, замените «=» на «#», или «&», или «##». Пример таблицы Столбец A Столбец B Столбец C Столбец D
    строка 1 944 Лягушки ##&A1/2
    строка 2 636 Жабы ##&A2/2
    строка 3 712 Тритоны ##&A3/2
    строка 4 690 Змеи ##&A4/2
    • Не используйте символы «*» и «?», так как это усложнит процесс на более поздних стадиях.
  4. 4 Теперь вы можете выбрать любые ячейки, которые вы хотите скопировать, а затем вставить их в другом месте таблицы. Так как они уже не являются формулами, они будут точно скопированы. Пример таблицы Столбец A Столбец B Столбец C Столбец D
    строка 1 944 Лягушки ##&A1/2 ##&A1/2
    строка 2 636 Жабы ##&A2/2 ##&A2/2
    строка 3 712 Тритоны ##&A3/2 ##&A3/2
    строка 4 690 Змеи ##&A4/2 ##&A4/2
  5. 5 Теперь еще раз откройте окно «Найти». В нашем примере мы будем искать символы «##» и заменим их на «=», чтобы скопированные ячейки превратились обратно в формулы. Пример таблицы Столбец A Столбец B Столбец C Столбец D
    строка 1 944 Лягушки =A1/2 =A1/2
    строка 2 636 Жабы =A2/2 =A2/2
    строка 3 712 Тритоны =A3/2 =A3/2
    строка 4 690 Змеи =A4/2 =A4/2
  6. 6 Попробуйте альтернативные методы. Если описанный выше метод не сработал по какой-либо причине, или если вы беспокоитесь о случайном изменении содержимого других ячеек (при использовании функции «Заменить все»), есть несколько других методов, которые вы можете протестировать:
    • Чтобы скопировать формулу без изменения ссылок (адресов соответствующих ячеек), выделите ячейку, а затем скопируйте формулу, приведенную в строке формул (в верхней части окна, а не в самой ячейке). Нажмите Esc, чтобы закрыть панель формул, а затем вставьте формулу, куда необходимо.[5]
    • Нажмите Ctrl + ` (этот символ, как правило, расположен на клавише с символом ~), чтобы перевести таблицу в режим просмотра формул. Скопируйте формулы и вставьте их в текстовый редактор, например, в Блокнот. Скопируйте их еще раз, а затем вставьте в нужные ячейки. [6] Нажмите Ctrl + `, чтобы перевести таблицу из режима просмотра формул в обычный режим.

Метод 3 из 3: Использование относительных и абсолютных ссылок на ячейки

  1. 1 Используйте относительные ссылки в формуле. «Ссылка на ячейку» в формуле Excel относится к адресу ячейки, используемой в формуле. Вы можете ввести их вручную или щелкнуть по ячейке, которую вы хотите использовать при вводе формулы. Например, следующая таблица включает формулу со ссылкой на ячейку A1: Относительные ссылки Столбец A Столбец B Столбец C
    строка 1 50 7 =A1*2
    строка 2 100
    строка 3 200
    строка 4 400
  2. 2 Объяснение понятия «относительные ссылки». Если вы копируете ячейку, в которую ввели формулу, а затем вставляете ее в другую ячейку, вы не увидите тот же результат. Это происходит потому, что ссылка на ячейку является «относительной». При копировании формул Excel автоматически меняет ссылки на ячейки в соответствии с адресами соответствующих ячеек. Например, если вы используете функции «Копировать» и «Вставить», чтобы скопировать формулу из ячейки С1 в ячейку C3, в формуле ссылка на ячейку А1 поменяется на ссылку на ячейку A3: Относительные ссылки Столбец A Столбец B Столбец C
    строка 1 50 7 =A1*2
    строка 2 100
    строка 3 200 =A3*2
    строка 4 400
    • Это работает для любых ячеек. Например, если вы копируете ту же формулу из ячейки С1 в ячейку D6 (не показана), Excel изменит ссылку на ячейку A1 на ссылку на ячейку В6 (один столбец вправо [C→D] и 5 строк вниз [1→6]).
  3. 3 Используйте абсолютные ссылки в формуле. Допустим, вы не хотите, чтобы Excel автоматически изменял формулу. Это можно сделать при помощи абсолютных ссылок, введя символ $ перед строкой или столбцом, который вы хотите оставить без изменений при копировании формулы в другие ячейки.[7] Вот несколько примеров таблиц, где показана исходная формула (жирным шрифтом) и результаты ее копирования в другие ячейки: Относительный столбец, Абсолютная строка (B$1): Столбец A Столбец B Столбец C
    строка 1 50 7 =B$1
    строка 2 100 =A$1 =B$1
    строка 3 200 =A$1 =B$1
    строка 4 400 =A$1 =B$1


    Абсолютный столбец, Относительная строка ($B1): Столбец A Столбец B Столбец C
    строка 1 50 7 =$B1
    строка 2 100 =$B2 =$B2
    строка 3 200 =$B3 =$B3
    строка 4 400 =$B4 =$B4


    Абсолютные столбец и строка ($B$1): Столбец A Столбец B Столбец C
    строка 1 50 7 =$B$1
    строка 2 100 $B$1 $B$1
    строка 3 200 $B$1 $B$1
    строка 4 400 $B$1 $B$1
  4. 4 Используйте клавишу F4, чтобы переключаться между абсолютными и относительными ссылками. Выделите ссылку на ячейку в формуле. Нажмите F4 и символы $ будут автоматически добавлены или удалены. Нажимайте F4 до тех пор, пока не отобразятся нужные абсолютные или относительные ссылки, а затем нажмите Enter.[8]

Советы

  • Если вы копируете формулу в новую ячейку и видите зеленый треугольник, Excel обнаружил возможную ошибку. В этом случае проверьте правильность введения формулы.[9]
  • Если вы случайно замените символ «=» на «?» или «*» (в методе «точное копирование формулы»), поиск символов «?» или «*» не приведет к ожидаемым результатам. Исправьте это, выполнив поиск символов «~?» или «~*».[10]
  • Выделите ячейку и нажмите Ctrl + ' (апостроф), чтобы скопировать в эту ячейку формулу, расположенную в ячейке сверху.[11]
Категория: Вопросы и ответы | Просмотров: 1182 | | Рейтинг: 0.0/0
Всего комментариев: 0
Добавлять комментарии могут только зарегистрированные пользователи.
[ Регистрация | Вход ]