Как настроить excel чтобы копировать формулы

Перемещение и копирование формулы

Важно помнить о возможностях изменения относительной ссылки на ячейку при переходе или копировании формулы.

Перемещение формулы При этом ссылки на ячейки в формуле не изменяются независимо от типа ссылки на ячейку.

Копирование формулы: При копировании формулы изменяются относительные ссылки на ячейки.

Перемещение формулы

Выделите ячейку с формулой, которую необходимо переместить.

В группе Буфер обмена на вкладке Главная нажмите кнопку Вырезать.

Формулы можно скопировать и путем перетаскивания границы выделенной ячейки в левую верхнюю ячейку области вставки. При этом будут заменяться все существующие данные.

Выполните одно из указанных ниже действий.

Чтобы вировать формулу иформатирование: в группе Буфер обмена на вкладке Главная нажмите кнопку В виде вкладки.

Чтобы вировать только формулу:в группе Буфер обмена на вкладке Главная нажмите кнопку В paste(Главная), выберите специальная ветвь ищелкните Формулы.

Копирование формулы

Вы выберите ячейку, содержащую формулу, которую нужно скопировать.

В группе Буфер обмена на вкладке Главная нажмите кнопку Копировать.

Выполните одно из указанных ниже действий.

Чтобы вировать формулу и любое форматирование, в группе Буфер обмена на вкладке Главная нажмите кнопку В виде вкладки.

Чтобы вировать только формулу, в группе Буфер обмена на вкладке Главная нажмите кнопку Вировать ,выберите специальная ветвь ,а затем щелкните Формулы.

Примечание: В нее можно вклеить только результаты формулы. В группе Буфер обмена на вкладке Главная нажмите кнопку Вировать, выберите специальная ветвь ,а затем щелкните Значения.

Убедитесь, что ссылки на ячейки в формуле дают нужный результат. При необходимости переключить тип ссылки можно следующим образом:

Выделите ячейку с формулой.

В строке формул строка формул выделите ссылку, которую нужно изменить.

Для переключения между сочетаниями нажмите F4.

В таблице подводится информация об обновлении типа ссылки при копировании формулы, содержащей ссылку, на две ячейки вниз и на две ячейки вправо.

$A$1 (абсолютный столбец и абсолютная строка)

A$1 (относительный столбец и абсолютная строка)

$A1 (абсолютный столбец и относительная строка)

A1 (относительный столбец и относительная строка)

Примечание: Формулы также можно копировать в смежные ячейки с помощью маркер заполнения . После проверки на то, что ссылки на ячейки в формуле дают нужный результат в шаге 4, вы выберите ячейку со скопированной формулой и перетащите его по диапазону, который вы хотите заполнить.

Перемещение формул очень похоже на перемещение данных в ячейках. Единственное, что нужно знать, — ссылки на ячейки, используемые в формуле, остаются нужными после перемещения.

Вы выберите ячейку с формулой, которую вы хотите переместить.

Щелкните Главная > вырезать (или нажмите CTRL+X).

Выйдите из ячейки, в которая должна вться формула, и нажмите кнопку Вировать (или нажмите CTRL+V).

Убедитесь, что ссылки на ячейки остаются нужными.

Совет: Вы также можете щелкнуть ячейки правой кнопкой мыши, чтобы вырезать и врезать формулу.

Совет: Формулу также можно скопировать и вкопировать в другую ячейку или на другой таблицу.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Источник

Как Работать с Формулами в Excel: Копирование, Вставка и Автозаполнение

Russian (Pусский) translation by Andrey Rybin (you can also view the original English article)

Электронные таблицы предназначены не только для финансовых специалистов и бухгалтеров; они также пригодятся фрилансерам или владельцам малого бизнеса, таким как вы. Электронные таблицы могут помочь вам выделить основные данные, касающиеся вашего бизнеса, понять какие продукты лучше продаются и организовать вашу жизнь.

В целом, предназначение Excel — сделать вашу жизнь легче. Этот урок, поможет вам сформировать базовые навыки по работе с ними. Формулы — это сердце Excel.

Тут перечислены ключевые навыки, которые вы получите в результате этого урока:

• Вы узнаете как написать вашу первую формулу в Microsoft Excel, чтобы автоматизировать математические вычисления.
• Как копировать и вставить рабочую формулу в другую ячейку.
• Как использовать автозаполнение, что бы быстро добавить формулу для целого столбца.

В электронной таблице ниже, вы можете видеть, почему Excel является таким мощным инструментом. Верхняя часть скриншота показывает формулы, которые используются в работе, а нижняя часть скриншота, показывает результаты работы этих формул.

Электронные таблицы упрощают данные с помощью формул, которые позволяют их изменять и обсчитывать.

В этом уроке вы узнаете как «приручить» ваши электронные таблицы и как управляться с формулами. Более того, то, чему вы научитесь можно использовать со многими электронными таблицами; работаете ли вы с Apple Numbers, Google Sheets, или Microsoft Excel — вы найдете здесь профессиональные советы по работе в формулами в электронных таблицах. Давайте начинать.

Как Работать с Формулами в Excel (Короткий Видео Урок)

Посмотрите скринкаст ниже, чтобы узнать, как лучше работать с формулами Excel. Я рассматриваю там все, начиная с того, как написать вашу первую формулу до того, как их копировать и вставлять.

Продолжайте читать дальше, что бы узнать больше о том, как работать с формулами и электронными таблицами.

Как Ориентироваться в Электронной таблице Excel

Давайте создадим нашу первую формулу в Microsoft Excel. Мы напишем формулу внутри ячейки, отдельный прямоугольник в электронной таблице. Excel это огромный набор строк и столбцов. Место в котором пересекаются строка и столбец называется ячейкой.

Когда строки (нумерованные элементы с левой стороны) и стоблцы (элементы обозначенные буквами в верху) пересекаются, получается ячейка.

Ячейка — это то место, куда мы можем забить данные, или формулу, которая наши данные будет обрабатывать. Каждая ячейка имеет свое имя, к которому мы можем обратиться, когда говорим о таблице.

Строки — это горизонтальные ряды, которые пронумерованы с левой стороны. Столбцы идут с лева на право и обозначены буквами. Когда строка и столбец пересекаются, образуется ячейка Excel. Если встречается столбец C cо строкой 3, то получается ячейка, которая обозначается как С3.

На скриншоте ниже, я в ручную напечатал название ячейки в ячейках, что бы наглядно пояснить вам как работают таблицы Excel.

Ячейки названные в соответствии с пересечением строк и столбцов; столбец F и строка 4 пересекаются и в результате получается ячейка F4.

Теперь, когда мы разобрались со структурой электронной таблицы, давайте двигаться дальше и запишем какую нибудь формулу в Excel и функцию.

Работа с Формулам и Функциями в Excel

Формулы и функции — это то, с помощью чего мы обрабатываем введенные нами данные. В Excel есть встроенные функции вроде =СРЗНАЧ которую используют для вычисления среднего, а так же простые формулы в виде операций таких как, сложение значений в двух ячейках. На практике эти термины используются как взаимозаменяемые.

В рамках этого урока, я буду использовать термин Формула, для обоих вариантов, так как мы часто в электронных таблицах работаем с их комбинацией.

Что бы записать вашу первую формулу, дважды щелкните на ячейке и нажмите на знак =. Давайте сделаем наш первый пример предельно простым, сложим две величины.

Посмотрите, как это выглядит в Excel, на картинке ниже:

Простая Формула и результат в Excel.

После того, как вы нажмете Ввод на клавиатуре, Excel рассчитает, то что вы ему напечатали. Это означает, что он вычислит по формуле, которую вы ввели, и выдаст результат. Когда мы сложили две величины, Excel рассчитал сумму и напечатал ответ.

Обратите внимание на Строку Формул, которая находится над таблицей и показывает вам формулу для сложения двух чисел. Формула по по прежнему остается не видимой в таблице, а мы видим результат.

Ячейка содержит «=4+4» в виде формулы, а в самой таблице показывается результат. Попробуйте использовать другие подобные варианты вычислений, такие как вычитание, умножение ( с символом *) и деление (с символом /). Вы можете печатать формулы либо в строке формул либо в ячейке.

Это предельно простой пример, но он иллюстрирует важную концепцию: внутри ячейки вашей электронной таблицы, вы можете создавать мощные формулы для обработки ваших данных.

Формулы Excel с Обращением к Ячейке

Есть два способа работы с формулами:

• Использовать формулы с данными введенными прямо в формуле, как в примере, который мы рассматривали выше (=4+4)
• Использовать формулы со ссылкой на ячейку, что бы было легче работать работать с данными, например как =А2+А3

Давайте взглянем на пример ниже. На скриншоте ниже, я ввел данные для продаж в моей компании для первых 3х месяцев 2016 года. Теперь, я их сложу, просуммировав продажи по всем трем месяцам.

В этом случае, я использую формулу, со ссылкой на ячейки. Я складываю ячейки B2, C2 и D2, чтобы получить сумму за три месяца.

Формулы с которыми мы работали, всего лишь малая часть из возможных в Excel. Что бы продолжить обучение, попробуйте какую-нибудь из этих встроенных функций Excel:

• =СРЗНАЧ — средние значения.
• =СЧЁТ подсчитывает число ячеек, которые содержат числовые данные.
• =ДЕНЬ возвращает значение для сегодняшнего числа.
• =СЖПРОБЕЛЫ удаляет лишние пробелы в строке, кроме пробелов между словами.

Как Копировать и Вставить Формулы в Excel

Теперь, когда мы написали несколько формул в Excel, давайте узнаем как их можно копировать и вставить.

Когда мы копирует и вставляем ячейку с формулой, мы не просто копируем величину, мы копируем формулу. Если мы вставим ее куда-то еще, мы копируем формулу в Excel.

Посмотрите, что я делаю в примере ниже:

1. Я копирую (Ctrl+C) ячейку E2, в которой была формула, которая складывала ячейки B2, C2 и D2.
2. Затем, я выбираю другие ячейки в столбце Е, щелкнув и потянув мышку вниз в пределах столбца.
3. Я жму (Ctrl+V), что бы вставить туже Excel формулу во все выделенные ячейки в столбце E.

Когда вы копируете и вставляете формулу в Excel, то у вас вставляется не величина, а вставляется та же формула, но в которой используются другие ячейки.

Как вы можете видеть на скриншоте выше, вставка формулы не вставляет значение ($21,933). Вместо этого, вставляется формула.

Формула, которую мы скопировали была в ячейке E2. Она складывала ячейки B2, C2, и D2. Когда мы вставляем ее, например, в ячейку E3, туда не вставляется то же самое, а туда вставляется формула, которая складывает значения в ячейках B3, C3, и D3.

В конечном итоге, Excel пердполагает, что вы хотите сложить три ячейки слева от текущей ячейки, что является идеальным решением в данном случае. При копировании и вставке формулы, он указывает на ячейки с использованием относительных ссылок (подробнее об этом ниже), вместо копирования и вставки формулы со ссылками на те же ячейки.

Как Работает Автозаполнение Формул в Excel

Автозаполнение формул — один из самых быстрых путей, применить формулу к другим ячейкам. Если все ячейки для которой вы хотите использовать формулу, находятся рядом друг с другом, вы можете использовать автозаполнение формул в своей электронной таблице.

Чтобы использовать автозаполнение в Excel, наведите мышку на выделенную ячейку с вашей формулой. Когда вы поместите курсов мышки в нижний правый угол, вы увидите, что курсор изменился и стал похож на знак плюс. Дважды щелкните по нему мышкой, чтобы создать автозаполнение формул.

Наведите мышку над нижним правым углом ячейки и дважды щелкните, когда увидите знак «+«, чтобы сделать автозаполнение формул.

Примечание: Проблема, связанная с использованием автозаполнения, заключается в том, что Excel не всегда хорошо угадывает, какие формулы следует заполнить. Обязательно проверьте, что автозаполнение добавляет правильные формулы в таблицу.

Абсолютные или Относительные Ссылки

Давайте продвинемся немного дальше в освоении Excel, и поговорим об отличии относительных ссылок от абсолютных.

В примере, где мы вычисляли сумму квартальных продаж, мы написали формулу с относительными ссылками. Вот почему наша формула работала правильно, когда мы сместили ее ниже. Она складывала три ячейки слева от текущей — а не те же ячейки, что были выше.

Абсолютные ссылки — «замораживают» ячейку к которой мы обращаемся. Давайте рассмотрим случай, когда абсолютные ссылки тоже могут пригодиться.

Давайте подсчитаем бонус за продажи для сотрудников за квартал. Основываясь на значениях продаж, мы вычисляем бонус с продаж в размере 2.5%. Давайте начнем с умножения продаж на проценты для бонуса для первого сотрудника.

Я подготовил ячейку в которую вставил значение в процентах для бонуса, и я умножу сумму продаж на проценты бонуса:

Мы написали формулу для умножения суммы продаж на проценты бонуса.

Теперь, когда мы рассчитали первый бонус с продаж, давайте переставим формулу вниз, что бы рассчитать все бонусы:

Упс! Хотя для первого случая бонусы рассчитались нормально, для других случаев расчеты не получились.

Однако, это не работает. Потому что проценты бонуса все время находятся в одной ячейке — H2; поэтому формула не работает, когда мы пытаемся сместить ее ниже. В каждой ячейке у нас получается ноль.

Вот, например, как Excel пытается рассчитать значения для F3 и F4:

Нам необходимо использовать абсолютную ссылку что бы все время использовалось умножение на ячейку H2, вместо смещения.

В конечном итоге, нам нужно изменить формулу, чтобы Excel все время использовал в произведении значение для бонуса, которое находится в строке H2. В этом случае мы используем абсолютную ссылку.

Абсолютные ссылки говорят Excel заморозить ячейку, которая используется в формуле, и не изменять ее при перемещении формулы в другие ячейки.

До этого момента, наша формула выглядела таким образом:

=Е2*Н2

Что бы «заморозить» использование в этой формуле значение из ячейки Н2, давайте преобразуем ее в абсолютную ссылку:

Заметьте, что мы добавили значок доллара в ссылке на ячейку. Это говорит Excel, что не важно, куда мы вставили нашу формулу, он должен все время использовать данные с бонусными процентами, которые записаны в ячейке Н2. Мы оставляем часть «E2» неизменной, потому что, когда мы тянем формулу вниз, мы хотим, чтобы формула адаптировалась к продажам каждого сотрудника.

Абсолютные ссылки позволяют нам зафиксировать определенную ячейку в формуле, даже когда мы тянем формулу вниз.

Абсолютные ссылки позволяют вам задать определенные правила для того, как ваши формулы будут работать. В этом случае мы использовали абсолютную ссылку, чтобы зафиксировать в формуле проценты для бонусов при расчете на каждого сотрудника.

Резюмируем и Продолжаем Обучение

Формулы — это то, что делает Excel таким мощным инструментом. Напишите формулу и потяните ее вниз, и вы избавите себя от большого количества ручной работы, при создании электронных таблиц.

Этот урок был создан в качестве введения в работу с электронными таблицами. Недавно мы создали и другие уроки, что бы помочь предпринимателям и фрилансерам, работать с электронными таблицами:

• В этом уроке, мы вскользь коснулись вопроса использования Математических формул; А здесь вы можете найти достаточно полное руководство по тому Как Работать с Математическими Формулами в Excel.
• Возможно вам приходится работать с «засоренной» таблицей или плохим массивом данных. Здесь вы узнаете как Найти и Избавиться от Дубликатов всего за пару кликов.
• Если вам приходится заниматься анализом и последующим представлением данных, вы можете использовать PowerPoint. Здесь вы узнаете Как Вставить Таблицу Excel в PowerPoint всего за 60 Секунд.

Как вы научились работать с формулами? Оставьте комментарий, если у вас есть советы или вопросы по таблицам Excel.

Источник