Excel позволяет легко копировать формулу во всю строку или столбец, но вы не всегда получаете желаемые результаты. Если вы получите неожиданные результаты или ужасные ошибки #REF и / DIV0, это может сильно расстроить. Но не волнуйтесь - вам не нужно будет редактировать вашу электронную таблицу на 5000 строк ячейка за ячейкой. Из этой статьи вы узнаете, как легко копировать формулы в другие ячейки.
Шаги
Метод 1 из 4. Использование поиска и замены для точного копирования формул
Шаг 1. Откройте книгу в Excel
Иногда у вас есть большая электронная таблица, полная формул, и вы хотите точно их скопировать. Преобразование всего в абсолютные ссылки на ячейки было бы утомительным, особенно если вы просто захотите потом снова изменить их. Используйте этот метод для быстрого перемещения формул с относительными ссылками на ячейки в другое место без изменения ссылок. В нашем примере таблицы мы хотим скопировать формулы из столбца C в столбец D, ничего не меняя.
| Столбец А | Столбец B | Столбец C | Столбец D | |
|---|---|---|---|---|
| ряд 1 | 944 | Лягушки | = A1 / 2 | |
| 2 ряд | 636 | Жабы | = A2 / 2 | |
| 3 ряд | 712 | Тритоны | = A3 / 2 | |
| ряд 4 | 690 | Змеи | = A4 / 2 |
Если вы просто пытаетесь скопировать формулу в одну ячейку, перейдите к последнему шагу («Попробуйте альтернативные методы») в этом разделе
Шаг 2. Нажмите Ctrl + H, чтобы открыть окно поиска
Ярлык одинаков в Windows и macOS.
Шаг 3. Найдите и замените "=" другим символом
Введите «=» в поле «Найти», а затем введите другой символ в поле «Заменить на». Нажмите Заменить все чтобы превратить все формулы (которые всегда начинаются со знака равенства) в текстовые строки, начинающиеся с какого-либо другого символа. Всегда используйте символ, который вы не использовали в своей таблице.
Например, замените его на # или & или на более длинную строку символов, например ## &.
| Столбец А | Столбец B | Столбец C | Столбец D | |
|---|---|---|---|---|
| ряд 1 | 944 | Лягушки | ## & A1 / 2 | |
| 2 ряд | 636 | Жабы | ## & A2 / 2 | |
| 3 ряд | 712 | Тритоны | ## & A3 / 2 | |
| ряд 4 | 690 | Змеи | ## & A4 / 2 |
Не используйте символы * или?, Так как это затруднит дальнейшие действия
Шаг 4. Скопируйте и вставьте ячейки
Выделите ячейки, которые хотите скопировать, а затем нажмите Ctrl + C (ПК) или Cmd + C (Mac), чтобы скопировать их. Затем выберите ячейки, в которые хотите вставить, и нажмите Ctrl + V (ПК) или Cmd + V (Mac) для вставки. Поскольку они больше не интерпретируются как формулы, они будут скопированы в точности.
| Столбец А | Столбец 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. Снова используйте команду «Найти и заменить», чтобы отменить изменение
Теперь, когда у вас есть формулы там, где вы хотите, снова используйте «Заменить все», чтобы отменить изменение. В нашем примере мы будем искать символьную строку «## &» и снова заменять ее на «=», чтобы эти ячейки снова стали формулами. Теперь вы можете продолжить редактирование таблицы как обычно:
| Столбец А | Столбец 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. Попробуйте альтернативные методы
Если описанный выше метод по какой-либо причине не работает или вы беспокоитесь о случайном изменении содержимого другой ячейки с помощью параметра «Заменить все», вы можете попробовать еще несколько вещей:
- Чтобы скопировать формулу отдельной ячейки без изменения ссылок, выберите ячейку, затем скопируйте формулу, отображаемую в строке формул в верхней части окна (а не в самой ячейке). Нажмите Esc чтобы закрыть панель формул, затем вставьте формулу в нужное место.
- Нажмите Ctrl а также ` (обычно на той же клавише, что и ~), чтобы перевести электронную таблицу в режим просмотра формул. Скопируйте формулы и вставьте их в текстовый редактор, например Блокнот или TextEdit. Скопируйте их еще раз, затем вставьте обратно в электронную таблицу в нужное место. Затем нажмите Ctrl а также ` еще раз, чтобы вернуться в обычный режим просмотра.
Метод 2 из 4: заполнение столбца или строки формулой
Шаг 1. Введите формулу в пустую ячейку
Excel упрощает распространение формулы по столбцу или по строке, «заполняя» ячейки. Как и в случае с любой формулой, начните с = знак, а затем используйте те функции или арифметические операции, которые вам нужны. Мы воспользуемся простым примером электронной таблицы и добавим столбец A и столбец B вместе. Нажмите Входить или Возвращение для расчета формулы.
| Столбец А | Столбец B | Столбец C | |
|---|---|---|---|
| ряд 1 | 10 | 9 | 19 |
| 2 ряд | 20 | 8 | |
| ряд 3 | 30 | 7 | |
| ряд 4 | 40 | 6 |
Шаг 2. Щелкните правый нижний угол ячейки с формулой, которую вы хотите скопировать
Курсор станет жирным + подписать.
Шаг 3. Щелкните и перетащите курсор по столбцу или строке, в которую вы копируете
Введенная вами формула будет автоматически введена в выделенные вами ячейки. Относительные ссылки на ячейки будут автоматически обновляться, чтобы ссылаться на ячейку в том же относительном положении, а не оставаться в точности таким же. Вот наш пример электронной таблицы, в которой показаны использованные формулы и отображаемые результаты:
| Столбец А | Столбец B | Столбец C | |
|---|---|---|---|
| ряд 1 | 10 | 9 | = A1 + B1 |
| 2 ряд | 20 | 8 | = A2 + B2 |
| ряд 3 | 30 | 7 | = A3 + B3 |
| ряд 4 | 40 | 6 | = A4 + B4 |
| Столбец А | Столбец B | Столбец C | |
|---|---|---|---|
| ряд 1 | 10 | 9 | 19 |
| 2 ряд | 20 | 8 | 28 |
| 3 ряд | 30 | 7 | 37 |
| ряд 4 | 40 | 6 | 46 |
- Вы также можете дважды щелкнуть значок плюса, чтобы заполнить весь столбец вместо перетаскивания. Excel перестанет заполнять столбец, если увидит пустую ячейку. Если справочные данные содержат пробел, вам придется повторить этот шаг, чтобы заполнить столбец под пробелом.
- Другой способ заполнить весь столбец той же формулой - выбрать ячейки непосредственно под ячейкой, содержащей формулу, и затем нажать Ctrl + D.
Метод 3 из 4: копирование формулы в несколько ячеек путем вставки
Шаг 1. Введите формулу в одну ячейку
Как и в случае с любой формулой, начните с = знак, а затем используйте те функции или арифметические операции, которые вам нужны. Мы воспользуемся простым примером электронной таблицы и добавим столбец A и столбец B вместе. Когда вы нажимаете Входить или Возвращение, формула рассчитает.
| Столбец А | Столбец B | Столбец C | |
|---|---|---|---|
| ряд 1 | 10 | 9 | 19 |
| 2 ряд | 20 | 8 | |
| ряд 3 | 30 | 7 | |
| ряд 4 | 40 | 6 |
Шаг 2. Выделите ячейку и нажмите Ctrl + C. (ПК) или ⌘ Command + C (Mac).
Это скопирует формулу в буфер обмена.
Шаг 3. Выберите ячейки, в которые вы хотите скопировать формулу
Щелкните один из них и перетащите его вверх или вниз с помощью мыши или клавиш со стрелками. В отличие от метода заполнения столбца или строки, ячейки, в которые вы копируете формулу, не обязательно должны находиться рядом с ячейкой, из которой вы копируете. Вы можете удерживать Контроль при выборе для копирования несмежных ячеек и диапазонов.
Шаг 4. Нажмите Ctrl + V. (ПК) или ⌘ Command + V (Mac) для вставки.
Формулы появятся в выбранных ячейках.
Метод 4 из 4: Использование относительных и абсолютных ссылок на ячейки
Шаг 1. Используйте относительную ссылку на ячейку в формуле
В формуле Excel «ссылка на ячейку» - это адрес ячейки. Вы можете ввести их вручную или щелкнуть ячейку, которую хотите использовать при вводе формулы. Например, в следующей таблице есть формула, которая ссылается на ячейку A2:
| Столбец А | Столбец B | Столбец C | |
|---|---|---|---|
| 2 ряд | 50 | 7 | = A2 * 2 |
| 3 ряд | 100 | ||
| ряд 4 | 200 | ||
| ряд 5 | 400 |
Шаг 2. Понять, почему они называются относительными ссылками
В формуле Excel относительная ссылка использует относительное положение адреса ячейки. В нашем примере C2 имеет формулу «= A2», которая является относительной ссылкой на значение на две ячейки слева. Если вы скопируете формулу в C4, она по-прежнему будет относиться к двум ячейкам слева, где теперь отображается «= A4».
| Столбец А | Столбец B | Столбец C | |
|---|---|---|---|
| 2 ряд | 50 | 7 | = A2 * 2 |
| 3 ряд | 100 | ||
| ряд 4 | 200 | = A4 * 2 | |
| ряд 5 | 400 |
Это также работает для ячеек за пределами той же строки и столбца. Если вы скопировали ту же формулу из ячейки C1 в ячейку D6 (не показана), Excel изменит ссылку «A2» на ячейку, расположенную на один столбец вправо (C → D) и на 5 строк ниже (2 → 7), или « B7"
Шаг 3. Вместо этого используйте абсолютную ссылку
Допустим, вы не хотите, чтобы Excel автоматически изменял вашу формулу. Вместо использования относительной ссылки на ячейку вы можете сделать ее абсолютный путем добавления символа $ перед столбцом или строкой, которые вы хотите сохранить, независимо от того, где вы также копируете формулу. Вот несколько примеров электронных таблиц, показывающих исходную формулу более крупным жирным шрифтом и результат, когда вы копируете и вставляете ее в другие ячейки:
-
Относительный столбец, абсолютная строка (3 B $):
Формула имеет абсолютную ссылку на строку 3, поэтому она всегда относится к строке 3:
Столбец А Столбец B Столбец C ряд 1 50 7 = 3 млрд долларов 2 ряд 100 = 3 австралийских доллара = 3 млрд долларов ряд 3 200 = 3 австралийских доллара = 3 млрд долларов ряд 4 400 = 3 австралийских доллара = 3 млрд долларов -
Абсолютный столбец, относительная строка ($ B1):
Формула имеет абсолютную ссылку на столбец B, поэтому всегда ссылается на столбец B.
Столбец А Столбец B Столбец C ряд 1 50 7 = $ B1 2 ряд 100 = $ B2 = $ B2 ряд 3 200 = $ B3 = $ B3 ряд 4 400 = $ B4 = $ B4 -
Абсолютный столбец и строка ($ B $ 1):
Формула имеет абсолютную ссылку на столбец B строки 1, поэтому всегда ссылается на столбец B строки 1.
Столбец А Столбец 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 
579572 17 3 Шаг 4. Используйте клавишу F4 для переключения между абсолютным и относительным
Выделите ссылку на ячейку в формуле, щелкнув ее и нажав F4 для автоматического добавления или удаления символов $. Продолжайте нажимать F4 пока не будут выбраны нужные абсолютные или относительные ссылки, затем нажмите Входить или Возвращение.
Видео - с помощью этой службы некоторая информация может быть передана YouTube
подсказки
- Если вы копируете формулу в новую ячейку и видите зеленый треугольник, Excel обнаружил возможную ошибку. Внимательно изучите формулу, чтобы убедиться, что что-то пошло не так.
- Если вы случайно заменили символ = на? или * в методе "точное копирование формулы" поиск "?" или "*" не дадут ожидаемых результатов. Исправьте это, выполнив поиск по запросу "~?" или вместо "~ *".
- Выделите ячейку и нажмите Ctrl '(апостроф), чтобы заполнить ее формулой прямо над ней.
