Как применить формулу к нескольким ячейкам в Excel

Обновлено: 01.07.2024

Для Excel ничто так не экономит время, как использование формул. На самом деле, это хлеб с маслом этого приложения Microsoft Office. Тем не менее, создание полезной формулы — самая сложная задача в Excel. Кроме того, вам нужно знать, как применить эту конкретную формулу к нескольким ячейкам в зависимости от количества данных.

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

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

Использовать автозаполнение

Оглавление

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

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

Выберите одну из ячеек на листе и введите формулу, которую хотите добавить. Предположим, что это =СУММ(A2:B2). После этого вы можете перетащить маркер автозаполнения вправо, чтобы заполнить формулой все строки.


Теперь перетащите маркер автозаполнения вниз до нужного диапазона. Это заполнит формулой все ячейки в столбце.

Другой способ сделать это — просто выбрать диапазон, к которому вы хотите применить формулу, и, в конце концов, нажать "Главная" > "Заполнить" > "Вниз и заполнить" > "Вправо".

Использовать VBA

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

  • Одновременно нажмите клавиши ALT и F11. Откроется Microsoft Visual Basic для окна приложения.
  • После этого просто нажмите «Модуль»> «Вставить», и в конечном итоге он будет вставлен в окно модуля. После этого вам нужно будет скопировать следующий VBA в окно.

Sub SetFormula()
'Updateby20140827
Dim < em>Rng As Range
Dim WorkRng As Range
< em>Вкл. Ошибка Возобновить Далее
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng. Адрес, тип:=8)
Application.ScreenUpdating = False
Для Каждого Rng In WorkRng
Rng.Value = (Rng.Value * 3) / 2 + 100
Далее
Application.ScreenUpdating = True
Конец Подраздел

  • После этого нажмите «Применить», и появится всплывающее окно, в котором вы можете позже щелкнуть, чтобы выбрать диапазон.
  • Теперь нажмите "ОК", и все выбранное поле получит формулу.

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

Чтобы сэкономить время при работе с формулами, воспользуйтесь приведенными ниже советами

Скопируйте формулу и не допускайте изменения ссылок

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

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

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

Используйте автозаполнение + вкладка для ввода функций

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

Используйте Ctrl+щелчок, чтобы ввести аргумент

Большинство пользователей Excel не хотят мириться с вводом запятых между аргументами. Поэтому в этом случае вам не нужно так много хлопот, потому что вы можете использовать Control + Click, чтобы ввести это в аргументы.

Выбрать сразу все формулы на листе

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

Как применить одну и ту же формулу к нескольким ячейкам в Excel

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

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

Давайте изучим методы.

Загрузить рабочий файл

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

Применение одной и той же формулы Excel к нескольким ячейкам (ссылки на ячейки изменятся)

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

1) Использование сочетания клавиш CTRL + Enter

Вы видите образец данных на рисунке ниже. Цены на некоторые товары (в евро) указаны в столбце «Цена (евро)». Также курсы валют показаны в диапазоне ячеек C2:E2. Я хочу показать цены на продукты в других валютах, таких как доллары США, фунты стерлингов и иены.

набор данных обменный курс

Я напишу только одну формулу в ячейке C6 и буду использовать ее в диапазоне ячеек C6: E10.

Позвольте мне показать вам, как это сделать.

Шаг 1

Выберите диапазон ячеек C6: E10.

выберите диапазон ячеек в excel

При выборе диапазона ячеек первая ячейка становится активной. Для нашего выбора ячейка C6 является активной ячейкой. Вы видите, что другие ячейки в диапазоне выделены серым цветом (не активны).

Шаг 2

Теперь введите знак равенства и напишите следующую формулу: =$B6*C$2. Эта формула будет автоматически введена в ячейку C6, так как C6 является активной ячейкой.

Ввод формулы в диапазон Excel

Шаг 3

Нажмите одновременно клавиши CTRL + Enter на клавиатуре. Вот что вы получите.

Нажмите CTRL и клавишу Enter на клавиатуре, чтобы ввести формулу во все ячейки диапазона». ширина=

Как работает эта формула (=$B6*C$2)?

  • Обратите внимание, что в формуле используются смешанные ссылки на ячейки. Ссылка $B6 означает, что при копировании формулы вправо столбец B не изменится. Итак, это будет как B6, B6 и B6. Все это B6, так как при копировании формулы вправо строка не меняется. Когда формула будет скопирована, ссылка на ячейку будет B6, B7, B8, B9, B10. Это связано с тем, что строки меняются при копировании формулы.
  • Ссылка C$2 означает, что при копировании формулы вправо будут использоваться ссылки C2, D2 и E2. Поскольку ссылка на столбец C является относительной. И когда мы скопируем эту формулу вниз, ссылки будут C2, C2, C2, C2 и C2. Это потому, что строка 2 является абсолютной.
  • Давайте проверим ячейку в диапазоне. Я выбираю ссылку на ячейку D8 и нажимаю клавишу F2 (это переводит ячейку в режим редактирования) на клавиатуре. Формула работает отлично.

Пример Excel формула

2) Использование инструмента Excel «Ручка заливки» (перетаскивание)

Я буду использовать тот же пример, чтобы объяснить этот метод. На этот раз я выберу только ячейку C6 и введу следующую формулу: =$B6*C$2. Затем нажмите клавишу Enter. В ячейке C6 мы получим значение 7.10. Снова выберите ячейку C6.

Функция дескриптора заполнения Excel

Посмотрите на изображение выше. Вы увидите сплошной квадрат зеленого цвета в правом нижнем углу ячейки. Это инструмент «Ручка заполнения» в Excel.

  • Наведите указатель мыши на сплошной маркер заполнения. Большой белый знак "плюс" превратится в маленький черный значок.
  • Нажмите и удерживайте указатель мыши, а затем перетащите его вниз. Затем, используя тот же метод, перетащите вправо.
  • Это полный метод, показанный на изображении в формате gif.

Я не буду объяснять, как работает формула, потому что это уже сделано в приведенном выше методе.

3) Использование инструмента Excel Fill Handle (двойной щелчок)

Вы также можете дважды щелкнуть инструмент "Ручка заливки".Формула будет скопирована в ячейки ниже (до ячейки, в которой доступны данные соседнего столбца).

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

Что делать, если инструмент «Маркер заливки» не отображается?

Инструмент «Ручка заливки» отображается в ячейках, но не работает? Потом не активируется. Выполните следующие действия, чтобы активировать его:

  • Вкладка "Файл" > "Параметры"
  • Появится диалоговое окно «Параметры Excel» > нажмите кнопку «Дополнительно».
  • В разделе "Параметры редактирования" установите флажок "Включить маркер заполнения и перетаскивание ячеек".
  • Наконец нажмите OK. Готово.

Диалоговое окно

4) Использование команды "Копировать и вставить" (CTRL + C)

  • Выделите ячейку C6 (=$B6*C$2) и скопируйте ее с помощью сочетания клавиш CTRL + C или с помощью командной кнопки в группе команд Главная > Буфер обмена.

Команда копирования в MS Excel

Выбор диапазон Excel

  • Затем используйте команду «Главная» > «Буфер обмена» > «Вставить» или нажмите сочетание клавиш CTRL + V.

Вставить команду в MS Excel

Копировать и Вставить в MS Excel

5) Использование сочетания клавиш CTRL + D

  • Поместите эту формулу Excel в ячейку C6: =$B6*C$2 и нажмите клавишу ВВОД.

Вставка формулы Excel

  • Теперь выберите ячейку C6 и другие ячейки в столбце, к которым вы хотите применить формулу.

Excel диапазон выбран.

  • Теперь одновременно нажмите CTRL + D на клавиатуре. Весь столбец заполняется формулой в ячейке C6

Сочетание клавиш CTRL и D в Excel

Примечание. Используя сочетание клавиш CTRL + D, вы не можете применить формулу к диапазону. Это работает только для столбца.

6) Сочетание клавиш CTRL + R для применения формулы к столбцу справа

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

CTRL и Shift и стрелка вниз, чтобы выбрать весь столбец с данными

Теперь вы хотите применить этот набор формул к правому столбцу. Для этого можно использовать сочетание клавиш CTRL+R.

Выбор столбец в Excel

  • Теперь нажмите клавиши CTRL + R на клавиатуре. Это результат.

Нажмите CTRL и R, чтобы применить формулу левой колонки

Примечание. Этот ярлык нельзя применить более чем к одному столбцу.

7) Копирование формулы в несколько ячеек, но ячейки не являются соседними

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

  • Сочетание клавиш CTRL + C
  • Или с помощью сочетания клавиш CTRL + D
  • Скопируйте формулу в ячейку C6.
  • Нажмите и удерживайте клавишу CTRL на клавиатуре, а затем выберите все ячейки, к которым вы хотите применить формулу.

Выделение несмежных ячеек в Excel

  • Теперь нажмите CTRL + V на клавиатуре. Готово.

Выделение несмежных ячеек в Excel

  • Выберите ячейку (C6), где находится формула, и все остальные ячейки, к которым вы хотите применить формулу.

Работа с несмежными ячейками в Excel

  • Теперь нажмите CTRL + D на клавиатуре. Готово.

Применение формул в несмежных ячейках Excel

8) Использование таблицы Excel

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

Диапазон легко преобразовать в таблицу. Просто выберите ячейку в диапазоне и нажмите CTRL + T или используйте команду Вставка > Таблицы > Таблица

Excel таблица

Давайте посчитаем НДС для позиций этих продуктов.

  • Выберите ссылку на ячейку F3 > Введите знак равенства > Выберите первую ячейку столбца Цена за единицу > Символ умножения (*) > Выберите первую ячейку столбца Количество > Символ умножения (*) > Наконец, выберите первую ячейку столбца столбец "% НДС".

Вставить формулу в таблицу Excel

  • Нажмите клавишу «Ввод» > НДС будет рассчитан > появится раскрывающийся список > щелкните раскрывающийся список и выберите параметр «Перезаписать все ячейки в этом столбце этой формулой».

Применение одной и той же формулы к нескольким ячейкам таблицы Excel

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

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

Чтобы использовать обычные ссылки в формуле таблицы Excel, необходимо ввести ссылки вручную:

Использование обычных ссылок на ячейки в формуле таблицы Excel

Изучите несколько важных сочетаний клавиш

1) CTRL + SHIFT + END

Чтобы выделить весь диапазон после вставки формулы в ячейку C6, нажмите клавиши CTRL + SHIFT + END на клавиатуре.

2) CTRL + SHIFT + СТРЕЛКА ВНИЗ, чтобы выбрать данные столбца из ячейки, в которой вы находитесь

Это сочетание клавиш CTRL + SHIFT + СТРЕЛКА ВНИЗ выберет столбец до ячейки, в которой доступны данные. Дальнейшее нажатие на этот ярлык выделит все из вашего местоположения.

3) CTRL + ПРОБЕЛ выберет весь столбец

Похожие материалы

Заключение

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

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




< /p>



< /p>

Как применить одну и ту же формулу к нескольким ячейкам в Excel?

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

  • Повторное использование всего: добавляйте наиболее часто используемые или сложные формулы, диаграммы и другие объекты в избранное и быстро используйте их повторно в будущем.
  • Более 20 текстовых функций: извлечение числа из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
  • Инструменты объединения: несколько книг и листов в одну; Объединение нескольких ячеек/строк/столбцов без потери данных; Объединить повторяющиеся строки и суммировать.
  • Инструменты разделения: разделение данных на несколько листов в зависимости от значения; Одна рабочая книга для нескольких файлов Excel, PDF или CSV; Один столбец в несколько столбцов.
  • Вставить, пропуская скрытые/отфильтрованные строки; Подсчет и сумма по цвету фона; Массовая рассылка персонализированных электронных писем нескольким получателям.
  • Суперфильтр: создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировка по неделям, дням, частоте и т. д.; Фильтр по полужирному шрифту, формулам, комментариям.
  • Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

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


Потрясающе! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Сэкономьте 50 % своего времени и сократите количество кликов мышью каждый день!

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

<р>1. Выберите пустую ячейку, а затем введите нужную формулу, здесь я набираю =(A1*3)/2+100, а затем перетаскиваю маркер автозаполнения вправо, чтобы заполнить формулу в строке, см. снимок экрана:


<р>2. Затем перетащите маркер заполнения вниз до нужного диапазона. Смотрите скриншот:


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

стрелка синего правого пузыря
Применение одной и той же формулы к нескольким ячейкам с помощью VBA

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

<р>1. Нажмите клавиши Alt + F11, чтобы открыть окно Microsoft Visual Basic для приложений.

<р>2. Нажмите «Модуль» > «Вставить», чтобы вставить окно модуля, и скопируйте приведенный ниже код VBA в окно.

VBA: применение той же формулы к ячейкам.

<р>3. Нажмите кнопку «Выполнить», появится диалоговое окно KutoolsforExcel, в котором вы сможете выбрать диапазон. Смотрите скриншот:


<р>4. Нажмите OK, и ко всем выделенным ячейкам будет применена одна и та же формула.

Совет. Вы можете изменить формулу, изменив Rng.Value = (Rng.Value * 3) / 2 + 100 в приведенном выше VBA, чтобы удовлетворить ваши потребности.

стрелка синего правого пузыря
Примените одну и ту же формулу к нескольким ячейкам с помощью Kutools for Excel

Установив Kutools for Excel, вы можете использовать инструменты операций, чтобы быстро применить ту же формулу к ячейкам диапазона.

Kutools for Excel включает более 300 удобных инструментов Excel. Бесплатно попробовать без ограничений в течение 30 дней. Получить сейчас

<р>1. Выберите диапазоны, которые вам нужны, чтобы применить ту же формулу, и нажмите Kutools > Еще > Инструменты работы. Смотрите скриншот:


<р>2. В диалоговом окне «Инструменты операции» нажмите «Пользовательский» в разделе «Операция» и введите формулу в текстовое поле, вы можете увидеть результаты расчета в поле «Предварительный просмотр».


<р>3. Нажмите «ОК» или «Применить», после чего все выбранные диапазоны будут применены к введенной вами формуле. Смотрите скриншот:


Примечание:

<р>1. Если вы установите флажок «Создать формулы» в диалоговом окне «Инструменты операций», формулы будут сохранены в каждой ячейке. Смотрите скриншот:


<р>2. Если установлен флажок Пропускать ячейки формулы, операция будет пропускать ячейки формулы в диапазоне.

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


Рис. 1. Применение формулы ко всему столбцу

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

=(Цена * Количество) + Налог с продаж

В ячейке F2 мы применяем формулу =(C2*D2)+E2 для расчета общей суммы. Есть несколько способов научиться применять формулу ко всему столбцу.


Рисунок 2. Функции столбцов Excel

Перетаскивая маркер заполнения

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


Рис. 3. Перетаскивание маркера заполнения

Двойным щелчком маркера заполнения

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


Рис. 4. Дважды щелкните маркер заполнения

С помощью команды «Заполнить»

Использование команды «Заполнить» — еще один хороший способ применить формулу ко всему столбцу. Нам нужно сделать следующее для всего столбца;

  • После ввода формулы в ячейку F2
  • Нажмите горячие клавиши Ctrl+Shift+End. При этом будет выбрана последняя использованная ячейка во всем столбце.


Рис. 5. Выбор последней использованной ячейки во всем столбце

  • Перейдите в раздел "Редактирование группы" на вкладке "Главная", щелкните стрелку "Заливка" и выберите ВНИЗ или попеременно нажмите Ctrl+D .


Рис. 6. Использование команды "Заполнить"

  • Команда "Заполнить" применяет формулу ко всем выделенным ячейкам.


Рис. 7. Функция столбца

Мгновенное подключение к эксперту через нашу службу Excelchat

В большинстве случаев проблема, которую вам нужно решить, будет более сложной, чем простое применение формулы или функции. Если вы хотите сэкономить часы исследований и разочарований, попробуйте наш онлайн-сервис Excelchat! Наши эксперты по Excel доступны круглосуточно и без выходных, чтобы ответить на любой вопрос, который у вас может возникнуть. Мы гарантируем подключение в течение 30 секунд и индивидуальное решение в течение 20 минут.

Читайте также: