Копировать формулы в Excel - Чаевые

Видео: КАК КОПИРОВАТЬ ФОРМУЛУ БЕЗ ПОТЕРИ ССЫЛОК НА ЯЧЕЙКИ MS EXCEL

Содержание

Шаги
Совет
Предупреждение

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

Шаги

Метод 1 из 4. Перетащите, чтобы скопировать формулу в несколько ячеек

Введите формулу в одну ячейку. Любая формула начинается со знака = затем функция или алгоритм, который вы хотите использовать. Здесь мы будем использовать простую электронную таблицу в качестве примера и добавим столбцы A и B вместе:
Нажмите клавишу ВВОД, чтобы вычислить формулу. Когда вы нажимаете клавишу ввода, формула вводится и вычисляется. Хотя отображается только окончательный результат (19), на самом деле в электронной таблице все еще хранится ваша формула.
Щелкните правый нижний угол исходной ячейки. Переместите указатель мыши в правый нижний угол ячейки, которую вы только что отредактировали. Курсор превратится в знак + жирный.
Удерживайте и перетащите мышь вдоль столбца или строки, в которую вы хотите скопировать формулу из исходной ячейки. Удерживая компьютерную мышь, перетащите курсор вниз, вниз по столбцу или по горизонтали рядом с строкой, в которой вы хотите изменить формулу (выделенная область). Формула, которую вы ввели ранее, будет автоматически введена в только что выделенные вами ячейки. Относительные ссылки на ячейки обновляются автоматически на основе ячеек, имеющих одинаковое относительное положение. Используемая формула и результаты, отображаемые в нашем примере электронной таблицы, будут выглядеть следующим образом:
Дважды щелкните значок плюса, чтобы заполнить формулу из столбца. Вместо того, чтобы удерживать мышь и перетаскивать, вы можете переместить мышь в правый нижний угол и дважды щелкнуть, когда указатель превратится в знак +. На этом этапе формула будет автоматически скопирована во все ячейки столбца.
- Excel перестанет заполнять столбец, когда обнаружит пустую ячейку. Поэтому, когда в справочных данных есть пробел, вам придется повторить этот шаг, чтобы продолжить заполнение раздела столбца под пробелом.
рекламное объявление

Метод 2 из 4. Вставьте, чтобы скопировать формулу в несколько ячеек.

Введите формулу в одну ячейку. Любую формулу нужно начинать со знака = затем используйте желаемую функцию или алгоритм. Здесь мы возьмем простой пример электронной таблицы и добавим два столбца A и B вместе:
Нажмите клавишу ВВОД, чтобы вычислить формулу. Когда вы нажимаете клавишу ввода, формула вводится и вычисляется. Несмотря на то, что отображаются только результаты (19), в электронной таблице по-прежнему сохраняются ваши формулы.
Щелкните и скопируйте ячейку, содержащую исходную формулу (CTRL + C).
Выделите ячейки, в которые вы хотите скопировать приведенную выше формулу. Щелкните ячейку и используйте мышь или клавиши со стрелками для перетаскивания вверх / вниз. В отличие от метода перетаскивания за угол, ячейки, в которые вы хотите скопировать формулу, не должны находиться рядом с исходной ячейкой.
Вставить (CTRL + V). рекламное объявление

Метод 3 из 4. Правильно скопируйте формулу

Этот метод можно использовать для быстрого копирования формулы без изменения ссылок на ячейки. Иногда ваша большая электронная таблица заполнена формулами, и вы просто хотите их скопировать в яблочко. Преобразование всего в абсолютные ссылки на ячейки (описано в разделе, посвященном ссылкам на ячейки) не так уж и весело, особенно когда вам нужно вернуться к нему после того, как вы закончите. Используйте этот метод для быстрого переключения формул, в которых используются относительные ссылки на ячейки в другом месте, без изменения ссылок. В нашем примере таблицы столбец C нужно скопировать в столбец D:
- Если вы хотите скопировать формулу только в одну ячейку, перейдите к последнему шагу («Попробуйте разные методы») этого раздела.
Открывает окно поиска. В большинстве версий Excel это окно можно найти, щелкнув вкладку «Главная» в верхней части окна Excel, а затем щелкнув элемент «Найти и выбрать» в разделе «Редактирование» окна Excel. эту карту. Вы также можете использовать сочетание клавиш CTRL F.
Найдите и замените знак «=» другим символом. Введите «=», нажмите «Найти все», затем введите другой символ в поле «Заменить на». Любая формула (всегда начинающаяся со знака =) будет автоматически преобразована в строку символов, начинающуюся с определенного символа. Давайте всегда используйте символы, которых еще нет в вашей таблице. Например, замените его на # или & или на более длинную строку, например ## &.
- Не используйте * или? потому что они усложнят следующие шаги.
Скопируйте и вставьте ячейки. На этом этапе вы можете выбрать любые ячейки, которые хотите скопировать, и вставить их в другое место. Поскольку Excel больше не воспринимает это как формулы, теперь вы можете точно скопировать указанные выше ячейки.
Снова воспользуйтесь функцией «Найти и заменить», чтобы вернуть ее обратно. Теперь, когда у вас есть рецепт там, где вы хотите, вы можете использовать «Найти все» и «Заменить на», чтобы отменить его. В нашем примере электронной таблицы мы найдем строку «## &», заменим ее знаком «=», чтобы ячейки снова стали формулами и могли продолжить редактирование таблицы как обычно:
Попробуйте другие методы. Если по какой-либо причине описанный выше метод не работает или вы обеспокоены тем, что другое содержимое ячейки может быть случайно изменено опцией «Заменить все», есть несколько других способов:
- Чтобы скопировать формулу ячейки без изменения ее ссылок, выберите ячейку и скопируйте формулу, отображаемую в строке формул в верхней части окна (а не саму ячейку). Нажмите esc, чтобы закрыть строку формул, а затем вставьте формулу в любое место.
- Нажмите Ctrl` (обычно на той же клавише, что и ~), чтобы переключить электронную таблицу в режим формул. Скопируйте формулы и вставьте их в текстовый редактор, например Блокнот или TextEdit. Скопируйте его еще раз и вставьте обратно в желаемое место на листе. Нажмите Ctrl`, чтобы вернуться к нормальному виду.
рекламное объявление

Метод 4 из 4. Используйте абсолютные и относительные ссылки на ячейки

Используйте относительные ссылки на ячейки в формуле. В формуле Excel «ссылка на ячейку» - это адрес ячейки. Вы можете ввести или щелкнуть ячейку, которую хотите использовать при вводе формулы. В таблице ниже формула относится к ячейке A2:
Понять, почему они называются относительными ссылками. В формуле Excel относительная ссылка использует относительное положение адреса ячейки. Например, ячейка C2 имеет формулу «= A2», которая является относительной ссылкой на значение ячейки слева, на расстоянии одной ячейки. Если вы скопируете эту формулу в C4, она все равно будет ссылаться на левую ячейку, на одну ячейку дальше: теперь "= A4".
- Относительные ссылки также работают для ячеек, находящихся не в одной строке и столбце. Когда вы копируете ту же формулу из ячейки C1 в ячейку D6 (не на рисунке), Excel меняет ссылку на один столбец вправо (C → D) и пять строк вниз (2 → 7), а с «A2» на «B7». ".
Вы также можете использовать абсолютные ссылки. Предположим, вы не хочет, чтобы Excel автоматически изменил свою формулу. Затем вместо использования относительной ссылки на ячейку вы можете преобразовать ее в ссылку абсолютный путем добавления символа $ перед количеством столбцов или строк, которые вы хотите сохранить при копировании в любое место. В приведенных ниже примерах электронных таблиц исходная формула представлена крупным жирным шрифтом, и в то же время отображается результат копирования-вставки в другие ячейки:
Используйте ключ F4 для преобразования между абсолютным и относительным. Щелкните, чтобы выделить ссылки на ячейки в формуле. Затем нажмите клавишу F4, и символ $ будет автоматически добавлен или удален в вашей ссылке. Удерживайте нажатой клавишу F4, пока не появится требуемая абсолютная / относительная ссылка, и нажмите клавишу ввода. рекламное объявление

Совет

Если при копировании формулы в новую ячейку появляется синий треугольник, Excel обнаружил подозрительную ошибку. Пожалуйста, внимательно проверьте формулу, чтобы определить проблему.
Если вы случайно заменили символ = на? или * в методе "точно скопировать формулу", найдя "?" или " *" не принесут ожидаемых результатов. Исправьте это, выполнив поиск "~?" или "~ *" вместо "?" / " *".
Выделите ячейку и нажмите Ctrl (апостроф), чтобы скопировать формулу ячейки прямо над ней.