Как скопировать формулы в эксель на все ячейки
Важно помнить о возможностях изменения относительной ссылки на ячейку при переходе или копировании формулы.
Перемещение формулы При этом ссылки на ячейки в формуле не изменяются независимо от типа ссылки на ячейку.
Копирование формулы: При копировании формулы изменяются относительные ссылки на ячейки.
Перемещение формулы
Выделите ячейку с формулой, которую необходимо переместить.
В группе Буфер обмена на вкладке Главная нажмите кнопку Вырезать.
Формулы можно скопировать и путем перетаскивания границы выделенной ячейки в левую верхнюю ячейку области вставки. При этом будут заменяться все существующие данные.
Выполните одно из указанных ниже действий.
Чтобы вировать формулу иформатирование: в группе Буфер обмена на вкладке Главная нажмите кнопку В виде вкладки.
Чтобы вировать только формулу:в группе Буфер обмена на вкладке Главная нажмите кнопку В paste(Главная), выберите специальная ветвь ищелкните Формулы.
Копирование формулы
Вы выберите ячейку, содержащую формулу, которую нужно скопировать.
В группе Буфер обмена на вкладке Главная нажмите кнопку Копировать.
Выполните одно из указанных ниже действий.
Чтобы вировать формулу и любое форматирование, в группе Буфер обмена на вкладке Главная нажмите кнопку В виде вкладки.
Чтобы вировать только формулу, в группе Буфер обмена на вкладке Главная нажмите кнопку Вировать ,выберите специальная ветвь ,а затем щелкните Формулы.
Примечание: В нее можно вклеить только результаты формулы. В группе Буфер обмена на вкладке Главная нажмите кнопку Вировать, выберите специальная ветвь ,а затем щелкните Значения.
Убедитесь, что ссылки на ячейки в формуле дают нужный результат. При необходимости переключить тип ссылки можно следующим образом:
Выделите ячейку с формулой.
В строке формул строка формул выделите ссылку, которую нужно изменить.
Для переключения между сочетаниями нажмите F4.
В таблице подводится информация об обновлении типа ссылки при копировании формулы, содержащей ссылку, на две ячейки вниз и на две ячейки вправо.
Копируемая формула
Первоначальная ссылка
Новая ссылка
$A$1 (абсолютный столбец и абсолютная строка)
A$1 (относительный столбец и абсолютная строка)
$A1 (абсолютный столбец и относительная строка)
A1 (относительный столбец и относительная строка)
Примечание: Формулы также можно копировать в смежные ячейки с помощью маркер заполнения . После проверки на то, что ссылки на ячейки в формуле дают нужный результат в шаге 4, вы выберите ячейку со скопированной формулой и перетащите его по диапазону, который вы хотите заполнить.
Перемещение формул очень похоже на перемещение данных в ячейках. Единственное, что нужно знать, — ссылки на ячейки, используемые в формуле, остаются нужными после перемещения.
Вы выберите ячейку с формулой, которую вы хотите переместить.
Щелкните Главная > вырезать (или нажмите CTRL+X).
Выйдите из ячейки, в которая должна вться формула, и нажмите кнопку Вировать (или нажмите CTRL+V).
Убедитесь, что ссылки на ячейки остаются нужными.
Совет: Вы также можете щелкнуть ячейки правой кнопкой мыши, чтобы вырезать и врезать формулу.
Совет: Формулу также можно скопировать и вкопировать в другую ячейку или на другой таблицу.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Как Работать с Формулами в Excel: Копирование, Вставка и Автозаполнение.
Электронные таблицы предназначены не только для финансовых специалистов и бухгалтеров; они также пригодятся фрилансерам или владельцам малого бизнеса, таким как вы. Электронные таблицы могут помочь вам выделить основные данные, касающиеся вашего бизнеса, понять какие продукты лучше продаются и организовать вашу жизнь.
В целом, предназначение 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.
Посмотрите, что я делаю в примере ниже:
- Я копирую (Ctrl+C) ячейку E2, в которой была формула, которая складывала ячейки B2, C2 и D2.
- Затем, я выбираю другие ячейки в столбце Е, щелкнув и потянув мышку вниз в пределах столбца.
- Я жму (Ctrl+V), что бы вставить туже Excel формулу во все выделенные ячейки в столбце E.
Как вы можете видеть на скриншоте выше, вставка формулы не вставляет значение ($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.
Копирование формул без сдвига ссылок
Предположим, что у нас есть вот такая несложная таблица, в которой подсчитываются суммы по каждому месяцу в двух городах, а затем итог переводится в евро по курсу из желтой ячейки J2.
Проблема в том, что если скопировать диапазон D2:D8 с формулами куда-нибудь в другое место на лист, то Microsoft Excel автоматически скорректирует ссылки в этих формулах, сдвинув их на новое место и перестав считать:
Задача: скопировать диапазон с формулами так, чтобы формулы не изменились и остались теми же самыми, сохранив результаты расчета.
Способ 1. Абсолютные ссылки
Способ 2. Временная деактивация формул
Чтобы формулы при копировании не менялись, надо (временно) сделать так, чтобы Excel перестал их рассматривать как формулы. Это можно сделать, заменив на время копирования знак "равно" (=) на любой другой символ, не встречающийся обычно в формулах, например на "решетку" (#) или на пару амперсандов (&&). Для этого:
- Выделяем диапазон с формулами (в нашем примере D2:D8)
- Жмем Ctrl+H на клавиатуре или на вкладке Главная — Найти и выделить — Заменить (Home — Find&Select — Replace)
Способ 3. Копирование через Блокнот
Этот способ существенно быстрее и проще.
Нажмите сочетание клавиш Ctrl+Ё или кнопку Показать формулы на вкладке Формулы (Formulas — Show formulas) , чтобы включить режим проверки формул — в ячейках вместо результатов начнут отображаться формулы, по которым они посчитаны:
Скопируйте наш диапазон D2:D8 и вставьте его в стандартный Блокнот:
Теперь выделите все вставленное (Ctrl+A), скопируйте в буфер еще раз (Ctrl+C) и вставьте на лист в нужное вам место:
Осталось только отжать кнопку Показать формулы (Show Formulas) , чтобы вернуть Excel в обычный режим.
Примечание: этот способ иногда дает сбой на сложных таблицах с объединенными ячейками, но в подавляющем большинстве случаев — работает отлично.
Способ 4. Макрос
Если подобное копирование формул без сдвига ссылок вам приходится делать часто, то имеет смысл использовать для этого макрос. Нажмите сочетание клавиш Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer) , вставьте новый модуль через меню Insert — Module и скопируйте туда текст вот такого макроса:
Для запуска макроса можно воспользоваться кнопкой Макросы на вкладке Разработчик (Developer — Macros) или сочетанием клавиш Alt+F8. После запуска макрос попросит вас выделить диапазон с исходными формулами и диапазон вставки и произведет точное копирование формул автоматически:
Как копировать в Excel ячейку с формулой: основные проблемы
А также типичные сценарии, когда вам все это требуется.
Копирование ячейки с формулой в Excel — это на самом деле не проблема. И тут бы можно было закончить заметку, но нет! Вы выделяете то, что нужно скопировать. И Excel послушно скопирует текст, формулу, формат, шрифт. Другими словам — все, что было в исходной ячейке. Но проблемы начинаются, когда вы захотите скопировать ячейку только с формулой или, например, содержимое ячейки без формулы. А нередко нужно скопировать и просто саму формулу в отрыве от ячейки. Часто это необходимо, например, для того, чтобы составлять таблицы для анализа различных данных и показателей (например, посещаемость страниц) в рамках SEO-продвижения сайта
Как поступить в таких случаях? – Читайте наше руководство!
Excel — удобный инструмент для работы маркетолога. Его можно использовать, например, для составления отчетов по таргетированной рекламе или контент-планов по SMM.
Копировать / вставить
Функцию копирования / вставки в Excel можно успешно использовать для любых формул. Вы можете применять ее для копирования формулы из одной ячейки или диапазона ячеек, а затем — для вставки формулы в другую ячейку или диапазон ячеек.
Копирование происходит через одноименную команду в меню, либо нажав сочетание клавиш Ctrl+C.
Функция копировать / вставить позволяет сразу же задействовать одну и ту же формулу к нескольким ячейкам. И это — без необходимости ручным способом прописывать формулу в первой, второй, третьей, четвертой ячейке.
Как меняются ссылки при копировании формул
При вставке формулы Excel автоматически исправит ссылки на ячейки в формуле, чтобы отразить новое местоположение формулы.
Пример: в ячейке A1 нашей таблицы есть формула, которая ссылается на ячейку B1. Мы хотим вставить какую-либо формулу в ячейку C1. В таком случае, по умолчанию, формула в ячейке C1 будет ссылаться на ячейку D1.
Важно: существуют несколько методов копирования / вставки формул в Excel, включая метод перетаскивания, использование ручки заполнения или команды «Заполнить вниз» или «Заполнить вправо». Прежде чем решить, какой способ будет наиболее эффективным, необходимо протестировать каждый их них.
Как скопировать и вставить формулу в Excel при изменении ссылки на одну ячейку?
Напишите формулу с любыми данными и скопируйте ее. Нужно знать несколько правил:
- Если нужно оставить ячейку неизменной — используйте символ $. Пример: $C$2.
- Если нужно оставить неизменным только столбик — используйте тот же символ. Пример: $C2
- Если нужно оставить неизменным только строчку — используйте тот же символ. Пример: C$2.
Не используйте символ $ в изменяемых ячейках! Просто перетащите или скопируйте такую вставку.
В Google и «Яндексе», соцсетях, рассылках, на видеоплатформах, у блогеров
Что происходит с ячейкой с формулой при копировании
Проделаем небольшой эксперимент:
- Откроем таблицу.
- Копируем формулу любым удобным способом.
- Вставляем ее через инструмент «Вставить функцию».
Да, мы работаем с формулой в текущей ячейке, и она формально вставилась. Далее можно добавить необходимые функции и увидеть подсказки по заданию входных значений… Но что-что пошло не так. А именно — формула изменила свое местоположение, и теперь она не работает в том виде, в котором нужно нам. Как решить эту проблему — читайте ниже
Формула после вставки перешла в соседнюю ячейку
Мы рекомендуем два решения этой проблемы:
- Поменять эту формулу на ту, что была до этого. Другими словами, заменить ее на исходный вариант.
- Задействовать автоматическую фильтрацию.
Итак, если новая формула переходит в соседнюю ячейку, то допустимо будет применить инструмент «Автофильтр»:
- Наводим стрелку в угол ячейки (обязательно в нижний, справа).
- Сразу же курсор примет вид символа +.
- Кликаем по этому плюсу и зажимаем правую кнопку.
- Не отпуская кнопки двигаем курсор вниз, вверх, либо — в любую сторону. Формула будет автоматически подтягиваться в новую ячейку.
Если требуется указать формулу неизменной, то нужно убедиться, что задействовано исходное значение. Сделайте это по такому шаблону:
Важно: независимо от того, куда вы переместите эту формулу, она постоянно будет ссылаться на A1 и A2.
Удаление $ перед 1 и 2 позволяет изменять строки, но не столбцы. Изменение $ перед A позволит менять столбцы, но не даст изменять строки.
Результат копирования: относительные и абсолютные формулы
Когда вы копируете формулу ячейки в Excel впервые, то сложно предсказать ее поведение. Ведь результат формулы будет зависеть и от еe вида.
Все виды формул в Excel можно разделить на два: относительные и абсолютные. Давайте смотреть в чем между ними разница.
Относительные формулы
Если вы используете такую формулу в таблице, то она относится к ссылкам на все ячейки, которые будут относительными по отношению к положению формулы в целевой ячейке.
Запомнить: при копировании относительной формулы все такие ссылки (напомним, что они ведут на ячейки) будут автоматом исправлены — чтобы соответствовать относительному положению формулы в ячейке назначения.
Пример: у нас имеется одна формула «=A1+B1» в ячейке C1. Мы копируем указанную формулу в ячейку C2, при этом – формула в ячейке C2 автоматически изменится на «=A2+B2».
Абсолютные формулы
Если вы используете такую формулу, то она относится к ссылкам на ячейки, которые статичны (фиксированы) и не изменяются, независимо от положения формулы в целевой ячейке.
Для создания абсолютной формулы, как вы уже поняли, применяется символ ($), который фиксирует ссылку на ячейку.
Пример: в нашей таблице имеется формула вида «=$A$1+$B$1» в ячейке C1. Мы хотим скопировать эту формулу в ячейку C2. Делаем это и видим: формула в ячейке C2 останется такой же, как и исходная формула «=$A$1+$B$1».
Запомнить: результат копирования ячейки с формулой зависит от того, являются ли ссылки внутри формулы относительными (либо же абсолютными).
Как скопировать и вставить формулу в Excel, фиксируя постоянную ссылку на одну ячейку?
Решений у этой задачи может быть несколько. Рассмотрим каждый вариант.
Предварительные условия: допустим, у нас есть формула вида:
И нам нужно скопировать её вниз, чтобы получить:
Важно знать: по умолчанию ячейки внутри формул Excel записываются как относительная ссылка. Другими словами, когда мы копируем формулу в другую ячейку, ссылка на ячейку в формуле автоматом поменяется.
Если вы не хотите, чтобы ячейка, на которую ссылается формула, менялась, вы можете обозначить ее как абсолютную ссылку — с помощью символа $.
Вот еще несколько важных моментов, которые нужно знать:
- Если поставить символ $ перед столбцом (например, $D62), то столбец будет заблокирован, но строка может изменяться,
- Если поставить символ $ перед строкой (например, D$62), то строка будет заблокирована, но столбец может изменяться.
- Если поставить символ $ перед обеими строками, то они будут заблокированы целиком. Например: $D$73.
Эксперимент: попробуйте выделить C62 и сразу нажмите на клавишу F4 до тех пор, пока не увидите необходимое вам значение.
Альтернативный вариант 1
Если вы не хотите ставить абсолютные ссылки на ячейки, есть альтернативный вариант: его смысл в том, чтобы скопировать текст формулы при помощи соответствующей функции и вставить его в ячейку.
- Перейдите к нужной ячейке.
- Нажмите F2.
- Нажмите CTRL+A (так вы выделите все содержимое ячейки).
- Нажмите CTRL+C (так вы скопируете все содержимое).
- Теперь кликните по какой-либо другой ячейке, куда нужно вставить формулу, и нажмите F2.
- Нажмите CTRL + V. Будет вставлена абсолютно идентичная формула.
- Теперь осталось просто перетащить формулу по всему диапазону.
Альтернативный вариант 2
Существует еще один вариант решения проблемы. Чтобы воспользоваться им, понадобится немного подправить формулу. Напомним, изначально она выглядела вот так:
Скопировать / вставить формулу, сохранив постоянную ссылку на одну ячейку, гораздо проще, если мы слегка изменим формулу
Попробуйте потянуть эту формулу вниз или вправо. Вы увидите, что ссылка осталась такой, какой была изначально. Опять же — вы можете просто удалить символ доллара перед «C». В таком случае значение 62 будет сохранено. Если же вы сотрете символ доллара перед 62, то сохранится «C».
Альтернативный вариант 3
Вы помните о том, что мы хотим скопировать формулу вниз? Другими словами, так, чтобы по диапазону ячеек все шло подряд. Вот так:
И вот еще один вариант решения этой задачи:
- Измените первую формулу (т.е. исходную формулу), закрепив C62.
- Направьте указатель мыши в ячейку, где находится первое вхождение формулы.
- Нажмите F2 (редактировать)
- Переместите курсор на C62.
- Нажмите F4 (закрепить).
Теперь первоначальная формула у нас будет выглядеть следующим образом:
Задача практически решена. Осталось лишь только копировать значения в соответствующие ячейки.
Важно:
- Все ссылки, которые ведут на С62, — абсолютные
- Все ссылки, которые ведут на ячейки К, — относительные.
Сложные вопросы в работе с Excel
Я хочу скопировать / вставить идентичную формулу. Как это сделать?
Допустим: вы не хотите, чтобы ссылки на ячейки менялись. А возвращаться назад и вставлять все знаки $$ — это слишком долго или не подходит по иным причинам. Есть другие возможности.
Для одной формулы
Просто скопируйте ее из панели формул и вставьте в нужное место.
Для нескольких формул
- Создайте пустой лист в рабочей книге.
- Вернитесь к первому рабочему листу и скопируйте нужный вам диапазон ячеек.
- На новом рабочем листе добавьте идентичный диапазон ячеек и вставьте его туда. Обратите внимание: все адреса ссылок в формулах остались прежними.
- Перетащите вставленный диапазон в тот же диапазон на новом рабочем листе. Именно на этот лист, в конечном итоге, и нужно будет вставить диапазон (внимание: речь про первый рабочий лист). Обратите внимание: все наши адреса ссылок остались прежними (внутри формул).
- Скопируйте этот диапазон формул на новый рабочий лист.
- Перейдите в нужный диапазон назначения на первом рабочем листе (это должен быть тот же диапазон адресов, из которого скопировали на новый рабочий лист) и примените команду вставки.
Обратите внимание: все ранее используемые адреса ссылок остались прежними (внутри формул).
Почему функция суммирования не работает?
Не видя вашего рабочего листа, сложно сделать на 100% корректный вывод и дать совет. Но наиболее частой причиной некорректной работы СУММ можно назвать наличие в ячейке текста (а не цифр).
Дело в том, что функция SUM (СУММ) всегда игнорирует любые текстовые ячейки.
Обычно текстовые числа легко обнаружить: они выровнены по левому краю и имеют зеленый маркер в углу. Проблема в том, что некоторые пользователи привыкли выравнивать текст по правому краю. Но Excel – умная программа: даже когда номер выровнен по правому краю, зеленый маркер также отображается. Некоторые пользователи идут еще дальше: они выбирают опцию «Игнорировать эту ошибку». В итоге — текст показывается как обычные числа (поэтому всегда обещайте внимание на зеленый уголок в ячейке):
Не нужно использовать конструкции типа =B7+B6+B5+B4+B3+B2. Вы получите ошибки. Но эту формулу можно задействовать для обнаружения текста, который маскируется под цифры.
Вот как решить проблему чисел, сохраненных как текст:
- Выберите диапазон чисел (не включая функцию SUM или СУММ).
- Используйте сочетание горячих клавиш Alt+D+E+F. Это приведет к запуску инструмента Text to Columns. В итоге текстовые числа автоматически переведутся в настоящие. Посмотрите на пример ниже:
Это наиболее вероятная причина некорректной работы функции суммирования. Но бывают и другие.
Например, возможно, что в рабочей книге установлен режим ручного расчета. Проверьте, исправляет ли он вводимые формулы. Если да, — откройте меню «Формулы», перейдите в «Параметры вычислений» и выберите «Автоматически».
Рабочую книгу очень просто перевести в режим вычислений. Если коллега перевел одну рабочую книгу в режим ручного расчета, а затем вы открываете эту книгу с пятью собственными книгами в ручном режиме, то и все пять книг автоматически уйдут в режим ручного расчета. Если указанная проблема для вас актуальна ежедневно или через день, то лучше исправить эту ситуацию: сделайте правый клик по кнопке «Параметры быстрого доступа» и добавьте ее в панель инструментов быстрого доступа.
Два чекбокса Manual (либо, в противоположном случае — Automatic) будут выводиться на панели инструментов. Соответственно, вы сможете очень быстро заметить, когда редактор находится в ручном режиме и наоборот.
Можно ли сделать фильтрацию дубликатов значений из уже настроенного диапазона?
Да, можно. В Excel есть специальная формула для этого:
Используйте эту формулу, чтобы провести фильтрацию дубликатов значений из заданного диапазона ячеек.
Вышеуказанную формулу допустимо задействовать в новом столбце рядом с диапазоном ячеек, который хотите проверить.
- Если вернулся «duplicate» — значение в этой ячейке уже было в диапазоне.
- Если вернулся «unique» — значение в этой ячейке еще не было в диапазоне.
Также можно применять опцию «Удалить дубликаты» на вкладке «Данные». Этот инструмент автоматически очистит все дубли из указанного диапазона, сохранив лишь уникальные значения.
Заключение: краткая инструкция
- При перемещении формул: все ссылки на сами ячейки остаются статическими, то есть неизменными.
- При копировании формулы: все относительные ссылки, ведущие на ячейки, становятся динамическими, то есть изменяются.
Давайте резюмируем самые частые сценарии копирования ячеек с привязкой к формуле кратко.
Как копировать в Excel ячейку с формулой
Выделите ячейку и воспользуйтесь сочетанием горячих клавиш Control+С, или используйте правую кнопку мыши для выделения. Вставьте ячейку с формулой туда, куда вам необходимо (при помощи команды Control+V или через меню «Вставить»).
Как копировать текст формулы
Вы можете воспользоваться, как минимум, двумя способами:
- Нажать F2 и скопировать текст формулы вручную и вставить его в нужное место.
- Скопировать ячейку, перейти в ячейку (куда вы хотите вставить формулу) и просто вставить её. Формула будет вставлена с соответствующими корректировками ссылок. Либо — можно скопировать ячейку с формулой и перейти в новую ячейку, воспользоваться сочетанием клавиш Alt+A+E+S и выделить формулу (это специальная вставка).
Если же нужно копировать формулу из «Эксель» в другое приложение, — нажмите клавишы Alt+. Вы увидите длинный список всех формул. Из него вы уже сможете скопировать любой необходимый вариант.
Как копировать текст формулы без результата
Чтобы скопировать текст формулы, а не ее результат, используйте один из двух приемов. Сначала выделите необходимую вам ячейку (обязательно с формулой), а затем:
- Нажмите клавишу F2. Выделите формулу в ячейке и задействуйте комбинацию гор. клавиш Ctrl+C (или используйте функцию копирования, вызываемую через меню).
- Выделите формулу на панели формул и нажмите Ctrl+C или кликните по иконке копирования в меню (находится она на панели инструментов).
Теперь вы можете вставить формулу как-текст в любую другую программу.
Помните важное: при вставке формулы внутри «Эксель» автоматически обновятся ссылки.
Как вставить одну и ту же формулу несколько раз
Да, вы можете вставить одну и ту же формулу несколько раз. Для этого можно использовать функцию автозаполнения ячеек.
Как скопировать формулы во все строки в Excel
- Перетащить или заполнить – ссылки на формулы будут меняться в каждой ячейке.
- Введите «статическую» формулу (например, $A$1), затем – перетащите её или заполните.
Как копировать в Excel вообще ячейку без формулы
Способов скопировать такую ячейку несколько:
- Выделите формулу на панели формул и нажмите Ctrl+C или кликните по иконке копирования в меню (панель инструментов).
- Перейдите в строку меню вверху и найдите выпадающее меню (речь о меню редактирования, конечно), а затем — просто скопируйте.
- Делаем правый клик по ячейке и сразу же в выпадающем меню находим функцию копирования.
- Выделите ячейку, а затем, когда содержимое ячейки будет выведено в новом окне вверху, скопируйте ее содержимое.
Если вы хотите скопировать формулу только в ячейку выше / ниже / правее / левее ячейки, в которой уже находится формула, — используйте перетаскивание. Для этого выделите курсором один угол ячейки и переместите ячейки через него. В результате — ячейки будут заполнены формулой.
Как использовать специальную вставку при фильтрации листов
Здесь все не так сложно:
- Примените нужные вам фильтры.
- Маркируйте цветным выделением столбик, содержащие данные.
- Удалите все фильтры. Для этого используйте сочетание горячих клавиш Alt+D, затем нажмите клавишу F, и после – клавишу S.
- Теперь нужно сделать сортировку столбика по цвету (его мы задействовали в шаге до этого).
- Спокойно применяем специальную вставку. Данные, которые расположены между фильтрами, никак не пострадают.
- Уберите маркирование цветом, которое мы сделали в предыдущих шагах. Удалите раскраску из первого шага.
- Снова примените фильтр.
Как копировать и вставлять текст и формулы в таблицу Excel
Самый простой способ — кликните по нужной ячейке и тут же нажмите клавишу F2, затем — просто вставьте текст. Либо — кликните по строке для ввода формулы (Fx), вставьте ее туда и нажмите Enter.
Вы также можете скопировать и вставить специальные значения.
Если вы копируете «из Excel в Excel», то можно вставлять специальные значения или сами формулы.