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

Загрузить PDF
Загрузить PDF

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

Метод 1
Метод 1 из 4:

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

Загрузить PDF
  1. {"smallUrl":"https:\/\/www.wikihow.com\/images_en\/thumb\/a\/a0\/579572-1-1.jpg\/v4-460px-579572-1-1.jpg","bigUrl":"https:\/\/www.wikihow.com\/images\/thumb\/a\/a0\/579572-1-1.jpg\/v4-728px-579572-1-1.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><\/div>"}
    Сначала введите знак равенства (=), а затем введите нужную функцию или математическую операцию. Например, вам нужно сложить значения столбцов A и B:


    Таблица
    Столбец A Столбец B Столбец C
    строка 1
    		 10 9 = A1 + B1
    строка 2
    		 20 8
    строка 3
    		 30 7
    строка 4
    		 40 6
  2. {"smallUrl":"https:\/\/www.wikihow.com\/images_en\/thumb\/2\/20\/579572-2-1.jpg\/v4-460px-579572-2-1.jpg","bigUrl":"https:\/\/www.wikihow.com\/images\/thumb\/2\/20\/579572-2-1.jpg\/v4-728px-579572-2-1.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><\/div>"}
    Нажмите клавишу «Enter», чтобы вычислить результат по введенной формуле. В ячейке, где вы ввели формулу, отобразится результат (в нашем примере вычисленная сумма 19), но формула будет храниться в электронной таблице.


    Таблица
    Столбец A Столбец B Столбец C
    строка 1
    		 10 9 19
    строка 2
    		 20 8
    строка 3
    		 30 7
    строка 4
    		 40 6
  3. {"smallUrl":"https:\/\/www.wikihow.com\/images_en\/thumb\/6\/66\/579572-3-1.jpg\/v4-460px-579572-3-1.jpg","bigUrl":"https:\/\/www.wikihow.com\/images\/thumb\/6\/66\/579572-3-1.jpg\/v4-728px-579572-3-1.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><\/div>"}
    Переместите указатель в правый нижний угол ячейки с формулой; указатель превратится в символ «+». [1]
  4. {"smallUrl":"https:\/\/www.wikihow.com\/images_en\/thumb\/9\/97\/579572-4-1.jpg\/v4-460px-579572-4-1.jpg","bigUrl":"https:\/\/www.wikihow.com\/images\/thumb\/9\/97\/579572-4-1.jpg\/v4-728px-579572-4-1.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><\/div>"}
    Удерживайте значок «+» и перетащите его по нужным ячейкам столбца или строки. Удерживайте кнопку мыши нажатой, а затем перетащите символ «+» вниз по столбцу или вправо по строке, чтобы выделить нужные ячейки. Введенная формула будет автоматически скопирована в выделенные ячейки. Так как здесь присутствует относительный адрес ячейки, адреса ячеек (в скопированных формулах) соответственно изменятся. В нашем примере (показаны формулы, которые автоматически поменялись, и вычисленные значения):


    Таблица
    Столбец 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. {"smallUrl":"https:\/\/www.wikihow.com\/images_en\/thumb\/b\/b9\/579572-5-1.jpg\/v4-460px-579572-5-1.jpg","bigUrl":"https:\/\/www.wikihow.com\/images\/thumb\/b\/b9\/579572-5-1.jpg\/v4-728px-579572-5-1.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><\/div>"}
    Дважды щелкните по «+», чтобы скопировать формулу во все ячейки столбца. Вместо того, чтобы перетаскивать символ «+», переместите указатель мыши в правый нижний угол ячейки с формулой и дважды щелкните по появившемуся значку «+». Формула скопируется во все ячейки столбца. [2]
    • Если в столбце есть пустые ячейки, автоматическое копирование формулы прекратится, как только будет достигнута первая пустая ячейка. В этом случае повторите описанные действия, чтобы скопировать формулу в ячейки, которые расположены под пустой ячейкой.
    Реклама
Метод 2
Метод 2 из 4:

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

Загрузить PDF
  1. {"smallUrl":"https:\/\/www.wikihow.com\/images_en\/thumb\/a\/a0\/579572-1-1.jpg\/v4-460px-579572-1-1.jpg","bigUrl":"https:\/\/www.wikihow.com\/images\/thumb\/a\/a0\/579572-1-1.jpg\/v4-728px-579572-1-1.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><\/div>"}
    Сначала введите знак равенства (=), а затем введите нужную функцию или математическую операцию. Например, вам нужно сложить значения столбцов A и B:


    Таблица
    Столбец A Столбец B Столбец C
    строка 1
    		 10 9 = A1 + B1
    строка 2
    		 20 8
    строка 3
    		 30 7
    строка 4
    		 40 6
  2. {"smallUrl":"https:\/\/www.wikihow.com\/images_en\/thumb\/2\/20\/579572-2-1.jpg\/v4-460px-579572-2-1.jpg","bigUrl":"https:\/\/www.wikihow.com\/images\/thumb\/2\/20\/579572-2-1.jpg\/v4-728px-579572-2-1.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><\/div>"}
    Нажмите клавишу «Enter», чтобы вычислить результат по введенной формуле. В ячейке, где вы ввели формулу, отобразится результат (в нашем примере вычисленная сумма 19), но формула будет храниться в электронной таблице.


    Таблица
    Столбец A Столбец B Столбец C
    строка 1
    		 10 9 19
    строка 2
    		 20 8
    строка 3
    		 30 7
    строка 4
    		 40 6
  3. 3
    Щелкните по ячейке с формулой, а затем нажмите CTRL+C, чтобы скопировать ее.
  4. 4
    Выделите ячейки, в которые будет скопирована формула. Нажмите на одну из ячеек, удерживайте кнопку мыши и проведите указателем по нужным ячейкам. Также можно удерживать клавишу «Ctrl» и щелкнуть по несмежным нужным ячейкам.
  5. 5
    Вставьте формулу в выделенные ячейки. Для этого нажмите CTRL+V.
    Реклама
Метод 3
Метод 3 из 4:

Точное копирование формулы

Загрузить PDF
  1. {"smallUrl":"https:\/\/www.wikihow.com\/images_en\/thumb\/5\/57\/579572-6-1.jpg\/v4-460px-579572-6-1.jpg","bigUrl":"https:\/\/www.wikihow.com\/images\/thumb\/5\/57\/579572-6-1.jpg\/v4-728px-579572-6-1.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><\/div>"}
    Воспользуйтесь этим методом, чтобы скопировать формулу без изменения адресов ячеек, которые входят в нее. Иногда таблица полна формул, и их нужно точно скопировать (то есть без изменений). Если менять относительные адреса ячеек на абсолютные адреса во всех формулах, можно потратить уйму времени (это тем более не приемлемо, если в будущем вам придется опять менять адреса). Этот метод подразумевает копирование формулы с относительными адресами ячеек в другие ячейки так, что формула не изменится. [3] Например, скопируем все содержимое столбца C в столбец D:


    Таблица
    Столбец A Столбец B Столбец C Столбец D
    строка 1
    		 944 Лягушки = A1 /2
    строка 2
    		 636 Жабы = A2 /2
    строка 3
    		 712 Тритоны = A3 /2
    строка 4
    		 690 Змеи = A4 /2
    • Если вы хотите скопировать просто одну формулу, перейдите к шагу «Воспользуйтесь альтернативным методом» этого раздела.
  2. {"smallUrl":"https:\/\/www.wikihow.com\/images_en\/thumb\/3\/38\/579572-7-1.jpg\/v4-460px-579572-7-1.jpg","bigUrl":"https:\/\/www.wikihow.com\/images\/thumb\/3\/38\/579572-7-1.jpg\/v4-728px-579572-7-1.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><\/div>"}
    В большинстве версий Excel щелкните по вкладке Главная в верхней части окна Excel, а затем щелкните по Найти и выбрать в разделе «Редактирование». [4] Также можно нажать клавиши CTRL+F.
  3. {"smallUrl":"https:\/\/www.wikihow.com\/images_en\/thumb\/7\/7e\/579572-8-1.jpg\/v4-460px-579572-8-1.jpg","bigUrl":"https:\/\/www.wikihow.com\/images\/thumb\/7\/7e\/579572-8-1.jpg\/v4-728px-579572-8-1.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><\/div>"}
    Введите «=», щелкните по «Найти все», а затем в поле «Заменить» введите любой другой символ. В этом случае все ячейки с формулами (в начале которых стоит знак равенства) автоматически превратятся в текстовые ячейки, которые начинаются с других символов. Обратите внимание, что нужно ввести символ, которого нет в ячейках таблицы. Например, замените знак равенства на символ # или &; также «=» можно заменить на несколько символов, например, на ##&.


    Таблица
    Столбец A Столбец B Столбец C Столбец D
    строка 1
    		 944 Лягушки ##& A1 /2
    строка 2
    		 636 Жабы ##& A2 /2
    строка 3
    		 712 Тритоны ##& A3 /2
    строка 4
    		 690 Змеи ##& A4 /2
    • Не используйте символы * или ?, чтобы не столкнуться с проблемами в дальнейшем.
  4. {"smallUrl":"https:\/\/www.wikihow.com\/images_en\/thumb\/8\/89\/579572-9-1.jpg\/v4-460px-579572-9-1.jpg","bigUrl":"https:\/\/www.wikihow.com\/images\/thumb\/8\/89\/579572-9-1.jpg\/v4-728px-579572-9-1.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><\/div>"}
    Выделите ячейки, которые хотите скопировать, и вставьте их в другие ячейки. Так как выделенные ячейки сейчас являются текстовыми ячейками, а не ячейками с формулами, последние будут скопированы без изменений.


    Таблица
    Столбец 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. {"smallUrl":"https:\/\/www.wikihow.com\/images_en\/thumb\/e\/e0\/579572-10-1.jpg\/v4-460px-579572-10-1.jpg","bigUrl":"https:\/\/www.wikihow.com\/images\/thumb\/e\/e0\/579572-10-1.jpg\/v4-728px-579572-10-1.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><\/div>"}
    Используйте функции «Найти» и «Заменить» еще раз, чтобы вернуться к прежним формулам. Скопировав формулы как текст, воспользуйтесь функциями «Найти все» и «Заменить на», чтобы вернуться к прежним формулам. В нашем примере найдем все символы ##& и заменим их на знак равенства (=), чтобы в ячейках появились формулы.


    Таблица
    Столбец 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. {"smallUrl":"https:\/\/www.wikihow.com\/images_en\/thumb\/8\/81\/579572-11-1.jpg\/v4-460px-579572-11-1.jpg","bigUrl":"https:\/\/www.wikihow.com\/images\/thumb\/8\/81\/579572-11-1.jpg\/v4-728px-579572-11-1.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><\/div>"}
    Если описанный здесь метод не привел к успеху или вы не хотите пользоваться им, чтобы случайно не поменять символы в других ячейках, примените другие способы:
    • Чтобы точно скопировать одну формулу, выделите ячейку с формулой, а затем скопируйте формулу, которая отображается в строке формул (а не в ячейке) в верхней части окна. Нажмите esc , чтобы закрыть строку формул, а затем вставьте формулу в нужные ячейки. [5]
    • Нажмите Ctrl ` (обычно этот символ находится на клавише с символом ~), чтобы перейти в режим просмотра формул. Скопируйте формулы, а затем вставьте их в простейший текстовый редактор, такой как Блокнот или TextEdit. Скопируйте формулы из текстового редактора и вставьте их в нужные ячейки электронной таблицы. [6] Еще раз нажмите Ctrl ` , чтобы вернуться в обычный режим работы с таблицей.
    Реклама
Метод 4
Метод 4 из 4:

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

Загрузить PDF
  1. {"smallUrl":"https:\/\/www.wikihow.com\/images_en\/thumb\/c\/cb\/579572-12-1.jpg\/v4-460px-579572-12-1.jpg","bigUrl":"https:\/\/www.wikihow.com\/images\/thumb\/c\/cb\/579572-12-1.jpg\/v4-728px-579572-12-1.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><\/div>"}
    Для этого введите адрес вручную или щелкните по нужной ячейке, когда будете вводить формулу. Например, в этой таблице есть формула с адресом ячейки A2:


    Относительные адреса ячеек
    Столбец A Столбец B Столбец C
    строка 2
    		 50 7 = A2 *2
    строка 3
    		 100
    строка 4
    		 200
    строка 5
    		 400
  2. {"smallUrl":"https:\/\/www.wikihow.com\/images_en\/thumb\/1\/11\/579572-13-1.jpg\/v4-460px-579572-13-1.jpg","bigUrl":"https:\/\/www.wikihow.com\/images\/thumb\/1\/11\/579572-13-1.jpg\/v4-728px-579572-13-1.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><\/div>"}
    В формуле такой адрес указывает на относительную позицию ячейки. Например, если в ячейке С2 есть формула «=A2», эта формула указывает на значение, которое находится двумя столбцами левее. Если скопировать формулу в ячейку С4, эта формула опять укажет на значение, которое находится двумя столбцами левее — то есть в ячейке С4 отобразится формула «= A4».


    Относительные адреса ячеек
    Столбец A Столбец B Столбец C
    строка 2
    		 50 7 = A2 *2
    строка 3
    		 100
    строка 4
    		 200 = A4 *2
    строка 5
    		 400
    • Этот принцип распространяется на ячейки в разных строках и столбцах. Например, если скопировать ту же формулу из ячейки C1 в ячейку D6 (не показана), в формуле появится адрес ячейки, которая расположена одним столбцом правее (C → D) и пятью строками ниже (2 → 7), а именно адрес «B7».
  3. {"smallUrl":"https:\/\/www.wikihow.com\/images_en\/thumb\/8\/8f\/579572-14-1.jpg\/v4-460px-579572-14-1.jpg","bigUrl":"https:\/\/www.wikihow.com\/images\/thumb\/8\/8f\/579572-14-1.jpg\/v4-728px-579572-14-1.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><\/div>"}
    Сделайте это, чтобы формула не менялась автоматически. Чтобы относительный адрес превратить в абсолютный, введите символ $ перед буквой столбца или номером строки, которые не должны измениться. [7] Далее приведены примеры (исходная формулы выделена жирным шрифтом; также показана эта формула, скопированная в другие ячейки):


    Относительный столбец, Абсолютная строка (B$3):
    В формуле присутствует абсолютная строка «3», то есть формула будет всегда ссылаться на третью строку.
    Столбец A Столбец B Столбец C
    строка 1
    		 50 7 = B$3
    строка 2
    		 100 = A$3 = B$3
    строка 3
    		 200 = A$3 = B$3
    строка 4
    		 400 = A$3 = B$3


    Абсолютный столбец, Относительная строка ($B1):
    В формуле присутствует абсолютный столбец «В», то есть формула будет всегда ссылаться на столбец «В».
    Столбец A Столбец B Столбец C
    строка 1
    		 50 7 = $B1
    строка 2
    		 100 = $B2 = $B2
    строка 3
    		 200 = $B3 = $B3
    строка 4
    		 400 = $B4 = $B4


    Абсолютные столбец и строка ($B$1):
    В формуле присутствует абсолютный столбец «В» и абсолютная строка «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. {"smallUrl":"https:\/\/www.wikihow.com\/images_en\/thumb\/2\/29\/579572-15-1.jpg\/v4-460px-579572-15-1.jpg","bigUrl":"https:\/\/www.wikihow.com\/images\/thumb\/2\/29\/579572-15-1.jpg\/v4-728px-579572-15-1.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><\/div>"}
    Используйте клавишу F4 , чтобы относительный адрес превратить в абсолютный. В формуле выделите адрес ячейки, щелкнув по ней, а затем нажмите клавишу F4 , чтобы ввести или удалить символ(ы) $. Нажимайте F4 до тех пор, пока не создадите нужный абсолютный адрес, а затем нажмите Enter . [8]
    Реклама

Советы

  • Если при копировании формулы в ячейке отобразился значок в виде зеленого треугольника, Excel обнаружил ошибку. Внимательно посмотрите на формулу, чтобы выяснить, что пошло не так. [9]
  • Если при точном копировании формулы вы случайно заменили знак равенства (=) на символ ? или *, поиск этих символов ничего не даст. В этом случае ищите символы ~? или ~*. [10]
  • Щелкните по ячейке и нажмите Ctrl ' (апостроф), чтобы скопировать в нее формулу из ячейки, которая находится над выбранной ячейкой. [11]
Реклама

Предупреждения

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

Дополнительные статьи

Как
пользоваться Microsoft Access
Как
редактировать документы на iPhone
Как
просматривать документы PDF в полноэкранном режиме
Как
получить доступ к рабочей электронной почте из дома
Как
узнать ключ продукта для Office
Как
преобразовать файл PDF в документ Word
Как
удалить горизонтальную линию в Word
Как
применить формулу ко всему столбцу в Google Таблицах на компьютере
Как
создать файл формата CSV
Как
отобразить скрытые строки в Google Таблицах на компьютере
Как
рисовать в PowerPoint
Как
изменять поля страницы в Word
Как
связать таблицы в Access
Как
распечатать часть таблицы в Excel
Реклама

Источники

  1. ↑ https://www.youtube.com/watch?v=Dq4khIULb6Q
  2. ↑ http://www.pryor.com/blog/copy-excel-formulas-down-to-fill-a-column/
  3. ↑ http://www.extendoffice.com/documents/excel/598-excel-copy-without-changing-formulas.html
  4. ↑ http://office.microsoft.com/en-us/excel-help/find-or-replace-text-and-numbers-on-a-worksheet-HP001216390.aspx
  5. ↑ http://www.quepublishing.com/articles/article.aspx?p=2024309&seqNum=4
  6. ↑ http://spreadsheetpage.com/index.php/tip/making_an_exact_copy_of_a_range_of_formulas_take_2
  7. ↑ http://office.microsoft.com/en-us/excel-help/move-or-copy-a-formula-HP010102342.aspx
  8. ↑ http://office.microsoft.com/en-us/excel-help/move-or-copy-a-Formula-HP010102342.aspx
  9. ↑ http://www.pryor.com/blog/copy-excel-formulas-down-to-fill-a-column/
  1. ↑ http://office.microsoft.com/en-us/excel-help/find-or-replace-text-and-numbers-on-a-worksheet-HP001216390.aspx
  2. ↑ http://www.quepublishing.com/articles/article.aspx?p=2024309&seqNum=4

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

На других языках
Эту страницу просматривали 142 811 раз.

Была ли эта статья полезной?

Реклама