Загрузить PDF
Загрузить PDF
В Excel можно быстро скопировать формулу во множество ячеек одной строки или одного столбца, но результат не всегда будет таким, какой планировался. Если вы не добились желаемого результата или в ячейках появились сообщения #REF и /DIV0, почитайте, что такое абсолютные и относительные адреса ячеек, чтобы выяснить причину ошибки. Помните, что вносить изменения во всех ячейках таблицы, которая состоит из 5000 строк, не придется — для этого есть методы, которые позволяют автоматически обновить формулу в определенной ячейке или скопировать ее без изменения значений.
Шаги
-
Введите формулу в одной ячейке. Сначала введите знак равенства (=), а затем введите нужную функцию или математическую операцию. Например, вам нужно сложить значения столбцов A и B:
Таблица Столбец A Столбец B Столбец C строка 110 9 = A1 + B1 строка 220 8 строка 330 7 строка 440 6 -
Нажмите клавишу «Enter», чтобы вычислить результат по введенной формуле. В ячейке, где вы ввели формулу, отобразится результат (в нашем примере вычисленная сумма 19), но формула будет храниться в электронной таблице.
Таблица Столбец A Столбец B Столбец C строка 110 9 19 строка 220 8 строка 330 7 строка 440 6 -
Нажмите на маркер в нижнем правом углу ячейки. Переместите указатель в правый нижний угол ячейки с формулой; указатель превратится в символ «+». [1] X Источник информации
-
Удерживайте значок «+» и перетащите его по нужным ячейкам столбца или строки. Удерживайте кнопку мыши нажатой, а затем перетащите символ «+» вниз по столбцу или вправо по строке, чтобы выделить нужные ячейки. Введенная формула будет автоматически скопирована в выделенные ячейки. Так как здесь присутствует относительный адрес ячейки, адреса ячеек (в скопированных формулах) соответственно изменятся. В нашем примере (показаны формулы, которые автоматически поменялись, и вычисленные значения):
Таблица Столбец A Столбец B Столбец C строка 110 9 = A1 + B1 строка 220 8 = A2 + B2 строка 330 7 = A3 + B3 строка 440 6 = A4 + B4 Таблица Столбец A Столбец B Столбец C строка 110 9 19 строка 220 8 28 строка 330 7 37 строка 440 6 46 -
Дважды щелкните по «+», чтобы скопировать формулу во все ячейки столбца. Вместо того, чтобы перетаскивать символ «+», переместите указатель мыши в правый нижний угол ячейки с формулой и дважды щелкните по появившемуся значку «+». Формула скопируется во все ячейки столбца. [2] X Источник информации
- Если в столбце есть пустые ячейки, автоматическое копирование формулы прекратится, как только будет достигнута первая пустая ячейка. В этом случае повторите описанные действия, чтобы скопировать формулу в ячейки, которые расположены под пустой ячейкой.
Реклама
-
Введите формулу в одной ячейке. Сначала введите знак равенства (=), а затем введите нужную функцию или математическую операцию. Например, вам нужно сложить значения столбцов A и B:
Таблица Столбец A Столбец B Столбец C строка 110 9 = A1 + B1 строка 220 8 строка 330 7 строка 440 6 -
Нажмите клавишу «Enter», чтобы вычислить результат по введенной формуле. В ячейке, где вы ввели формулу, отобразится результат (в нашем примере вычисленная сумма 19), но формула будет храниться в электронной таблице.
Таблица Столбец A Столбец B Столбец C строка 110 9 19 строка 220 8 строка 330 7 строка 440 6 -
3Щелкните по ячейке с формулой, а затем нажмите CTRL+C, чтобы скопировать ее.
-
4Выделите ячейки, в которые будет скопирована формула. Нажмите на одну из ячеек, удерживайте кнопку мыши и проведите указателем по нужным ячейкам. Также можно удерживать клавишу «Ctrl» и щелкнуть по несмежным нужным ячейкам.
-
5Вставьте формулу в выделенные ячейки. Для этого нажмите CTRL+V.Реклама
-
Воспользуйтесь этим методом, чтобы скопировать формулу без изменения адресов ячеек, которые входят в нее. Иногда таблица полна формул, и их нужно точно скопировать (то есть без изменений). Если менять относительные адреса ячеек на абсолютные адреса во всех формулах, можно потратить уйму времени (это тем более не приемлемо, если в будущем вам придется опять менять адреса). Этот метод подразумевает копирование формулы с относительными адресами ячеек в другие ячейки так, что формула не изменится. [3] X Источник информации Например, скопируем все содержимое столбца C в столбец D:
Таблица Столбец A Столбец B Столбец C Столбец D строка 1944 Лягушки = A1 /2 строка 2636 Жабы = A2 /2 строка 3712 Тритоны = A3 /2 строка 4690 Змеи = A4 /2 - Если вы хотите скопировать просто одну формулу, перейдите к шагу «Воспользуйтесь альтернативным методом» этого раздела.
-
Откройте окно «Найти». В большинстве версий Excel щелкните по вкладке Главная в верхней части окна Excel, а затем щелкните по Найти и выбрать в разделе «Редактирование». [4] X Источник информации Также можно нажать клавиши CTRL+F.
-
Найдите и замените знак равенства (=) на другой символ. Введите «=», щелкните по «Найти все», а затем в поле «Заменить» введите любой другой символ. В этом случае все ячейки с формулами (в начале которых стоит знак равенства) автоматически превратятся в текстовые ячейки, которые начинаются с других символов. Обратите внимание, что нужно ввести символ, которого нет в ячейках таблицы. Например, замените знак равенства на символ # или &; также «=» можно заменить на несколько символов, например, на ##&.
Таблица Столбец A Столбец B Столбец C Столбец D строка 1944 Лягушки ##& A1 /2 строка 2636 Жабы ##& A2 /2 строка 3712 Тритоны ##& A3 /2 строка 4690 Змеи ##& A4 /2 - Не используйте символы * или ?, чтобы не столкнуться с проблемами в дальнейшем.
-
Скопируйте и вставьте ячейки. Выделите ячейки, которые хотите скопировать, и вставьте их в другие ячейки. Так как выделенные ячейки сейчас являются текстовыми ячейками, а не ячейками с формулами, последние будут скопированы без изменений.
Таблица Столбец A Столбец B Столбец C Столбец D строка 1944 Лягушки ##& A1 /2 ##& A1 /2 строка 2636 Жабы ##& A2 /2 ##& A2 /2 строка 3712 Тритоны ##& A3 /2 ##& A3 /2 строка 4690 Змеи ##& A4 /2 ##& A4 /2 -
Используйте функции «Найти» и «Заменить» еще раз, чтобы вернуться к прежним формулам. Скопировав формулы как текст, воспользуйтесь функциями «Найти все» и «Заменить на», чтобы вернуться к прежним формулам. В нашем примере найдем все символы ##& и заменим их на знак равенства (=), чтобы в ячейках появились формулы.
Таблица Столбец A Столбец B Столбец C Столбец D строка 1944 Лягушки = A1 /2 = A1 /2 строка 2636 Жабы = A2 /2 = A2 /2 строка 3712 Тритоны = A3 /2 = A3 /2 строка 4690 Змеи = A4 /2 = A4 /2 -
Воспользуйтесь альтернативным методом. Если описанный здесь метод не привел к успеху или вы не хотите пользоваться им, чтобы случайно не поменять символы в других ячейках, примените другие способы:
- Чтобы точно скопировать одну формулу, выделите ячейку с формулой, а затем скопируйте формулу, которая отображается в строке формул (а не в ячейке) в верхней части окна. Нажмите esc , чтобы закрыть строку формул, а затем вставьте формулу в нужные ячейки. [5] X Источник информации
- Нажмите Ctrl ` (обычно этот символ находится на клавише с символом ~), чтобы перейти в режим просмотра формул. Скопируйте формулы, а затем вставьте их в простейший текстовый редактор, такой как Блокнот или TextEdit. Скопируйте формулы из текстового редактора и вставьте их в нужные ячейки электронной таблицы. [6] X Источник информации Еще раз нажмите Ctrl ` , чтобы вернуться в обычный режим работы с таблицей.
Реклама
-
Используйте в формуле относительный адрес ячейки. Для этого введите адрес вручную или щелкните по нужной ячейке, когда будете вводить формулу. Например, в этой таблице есть формула с адресом ячейки A2:
Относительные адреса ячеек Столбец A Столбец B Столбец C строка 250 7 = A2 *2 строка 3100 строка 4200 строка 5400 -
Запомните, что такое относительный адрес ячейки. В формуле такой адрес указывает на относительную позицию ячейки. Например, если в ячейке С2 есть формула «=A2», эта формула указывает на значение, которое находится двумя столбцами левее. Если скопировать формулу в ячейку С4, эта формула опять укажет на значение, которое находится двумя столбцами левее — то есть в ячейке С4 отобразится формула «= A4».
Относительные адреса ячеек Столбец A Столбец B Столбец C строка 250 7 = A2 *2 строка 3100 строка 4200 = A4 *2 строка 5400 - Этот принцип распространяется на ячейки в разных строках и столбцах. Например, если скопировать ту же формулу из ячейки C1 в ячейку D6 (не показана), в формуле появится адрес ячейки, которая расположена одним столбцом правее (C → D) и пятью строками ниже (2 → 7), а именно адрес «B7».
-
Используйте в формуле абсолютный адрес ячейки. Сделайте это, чтобы формула не менялась автоматически. Чтобы относительный адрес превратить в абсолютный, введите символ $ перед буквой столбца или номером строки, которые не должны измениться. [7] X Источник информации Далее приведены примеры (исходная формулы выделена жирным шрифтом; также показана эта формула, скопированная в другие ячейки):
Относительный столбец, Абсолютная строка (B$3):
В формуле присутствует абсолютная строка «3», то есть формула будет всегда ссылаться на третью строку.Столбец A Столбец B Столбец C строка 150 7 = B$3 строка 2100 = A$3 = B$3 строка 3200 = A$3 = B$3 строка 4400 = A$3 = B$3 Абсолютный столбец, Относительная строка ($B1):
В формуле присутствует абсолютный столбец «В», то есть формула будет всегда ссылаться на столбец «В».Столбец A Столбец B Столбец C строка 150 7 = $B1 строка 2100 = $B2 = $B2 строка 3200 = $B3 = $B3 строка 4400 = $B4 = $B4 Абсолютные столбец и строка ($B$1):
В формуле присутствует абсолютный столбец «В» и абсолютная строка «1», то есть формула будет всегда ссылаться на столбец «В» и первую строку.Столбец A Столбец B Столбец C строка 150 7 = $B$1 строка 2100 $B$1 $B$1 строка 3200 $B$1 $B$1 строка 4400 $B$1 $B$1 -
Используйте клавишу F4 , чтобы относительный адрес превратить в абсолютный. В формуле выделите адрес ячейки, щелкнув по ней, а затем нажмите клавишу F4 , чтобы ввести или удалить символ(ы) $. Нажимайте F4 до тех пор, пока не создадите нужный абсолютный адрес, а затем нажмите Enter . [8] X Источник информацииРеклама
Советы
- Если при копировании формулы в ячейке отобразился значок в виде зеленого треугольника, Excel обнаружил ошибку. Внимательно посмотрите на формулу, чтобы выяснить, что пошло не так. [9] X Источник информации
- Если при точном копировании формулы вы случайно заменили знак равенства (=) на символ ? или *, поиск этих символов ничего не даст. В этом случае ищите символы ~? или ~*. [10] X Источник информации
- Щелкните по ячейке и нажмите Ctrl ' (апостроф), чтобы скопировать в нее формулу из ячейки, которая находится над выбранной ячейкой. [11] X Источник информации
Реклама
Предупреждения
- Возможно, у вашей версии Excel немного другой интерфейс, чем тот, который показан на скриншотах в этой статье.
Реклама
Источники
- ↑ https://www.youtube.com/watch?v=Dq4khIULb6Q
- ↑ http://www.pryor.com/blog/copy-excel-formulas-down-to-fill-a-column/
- ↑ http://www.extendoffice.com/documents/excel/598-excel-copy-without-changing-formulas.html
- ↑ http://office.microsoft.com/en-us/excel-help/find-or-replace-text-and-numbers-on-a-worksheet-HP001216390.aspx
- ↑ http://www.quepublishing.com/articles/article.aspx?p=2024309&seqNum=4
- ↑ http://spreadsheetpage.com/index.php/tip/making_an_exact_copy_of_a_range_of_formulas_take_2
- ↑ http://office.microsoft.com/en-us/excel-help/move-or-copy-a-formula-HP010102342.aspx
- ↑ http://office.microsoft.com/en-us/excel-help/move-or-copy-a-Formula-HP010102342.aspx
- ↑ http://www.pryor.com/blog/copy-excel-formulas-down-to-fill-a-column/
Реклама