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

Обновлено: 05.01.2025

Знание этих простых приёмов сэкономит Вам уйму времени для более интересных занятий.

Выделяем все ячейки, в которые нужно вставить одинаковые данные

Вот самые быстрые способы выделить ячейки:

Выделяем целый столбец

Если данные в Excel оформлены как полноценная таблица, просто кликните по любой ячейке нужного столбца и нажмите Ctrl+Space.

Примечание: При выделении любой ячейки в полноценной таблице на Ленте меню появляется группа вкладок Работа с таблицами (Table Tools).

Если же это обычный диапазон, т.е. при выделении одной из ячеек этого диапазона группа вкладок Работа с таблицами (Table Tools) не появляется, выполните следующие действия:

Замечание: К сожалению, в случае с простым диапазоном нажатие Ctrl+Space выделит все ячейки столбца на листе, например, от C1 до C1048576, даже если данные содержатся только в ячейках C1:C100.

Выделите первую ячейку столбца (или вторую, если первая ячейка занята заголовком), затем нажмите Shift+Ctrl+End, чтобы выделить все ячейки таблицы вплоть до крайней правой. Далее, удерживая Shift, нажмите несколько раз клавишу со Стрелкой влево, пока выделенным не останется только нужный столбец.

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

Выделяем целую строку

Если данные в Excel оформлены как полноценная таблица, просто кликните по любой ячейке нужной строки и нажмите Shift+Space.
Если перед Вами обычный диапазон данных, кликните последнюю ячейку нужной строки и нажмите Shift+Home. Excel выделит диапазон, начиная от указанной Вами ячейки и до столбца А. Если нужные данные начинаются, например, со столбца B или C, зажмите Shift и понажимайте на клавишу со Стрелкой вправо, пока не добьётесь нужного результата.

Выделяем несколько ячеек

Удерживайте Ctrl и кликайте левой кнопкой мыши по всем ячейкам, которые нужно заполнить данными.

Выделяем таблицу целиком

Кликните по любой ячейке таблицы и нажмите Ctrl+A.

Выделяем все ячейки на листе

Нажмите Ctrl+A от одного до трех раз. Первое нажатие Ctrl+A выделяет текущую область. Второе нажатие, дополнительно к текущей области, выделяет строки с заголовками и итогами (например, в полноценных таблицах). Третье нажатие выделяет весь лист. Думаю, вы догадались, что в некоторых ситуациях вам потребуется всего лишь одно нажатие, чтобы выделить весь лист, а в некоторых – целых три нажатия.

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

Выделите нужную область (см. рисунок ниже), например, целый столбец.

Нажмите F5 и в появившемся диалоговом окне Переход (Go to) нажмите кнопку Выделить (Special).

В диалоговом окне Выделить группу ячеек (Go To special) отметьте флажком вариант Пустые ячейки (Blanks) и нажмите ОК.

Вы вернётесь в режим редактирования листа Excel и увидите, что в выбранной области выделены только пустые ячейки. Три пустых ячейки гораздо проще выделить простым щелчком мыши – скажете Вы и будете правы. Но как быть, если пустых ячеек более 300 и они разбросаны случайным образом по диапазону из 10000 ячеек?

Самый быстрый способ вставить формулу во все ячейки столбца

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

Преобразуйте диапазон в таблицу Excel. Для этого выделите любую ячейку в диапазоне данных и нажмите Ctrl+T, чтобы вызвать диалоговое окно Создание таблицы (Create Table). Если данные имеют заголовки столбцов, поставьте галочку для параметра Таблица с заголовками (My Table has headers). Обычно Excel распознаёт заголовки автоматически, если это не сработало – поставьте галочку вручную.
Добавьте новый столбец к таблице. С таблицей эта операция осуществляется намного проще, чем с простым диапазоном данных. Кликните правой кнопкой мыши по любой ячейке в столбце, который следует после того места, куда нужно вставить новый столбец, и в контекстном меню выберите Вставить > Столбец слева (Insert > Table Column to the Left).
Дайте название новому столбцу.
Введите формулу в первую ячейку нового столбца. В своём примере я использую формулу для извлечения доменных имён:

Если решите вернуться от таблицы к формату обычного диапазона, то выделите любую ячейку таблицы и на вкладке Конструктор (Design) нажмите кнопку Преобразовать в диапазон (Convert to range).

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

Вставляем одинаковые данные в несколько ячеек при помощи Ctrl+Enter

Выделите на листе Excel ячейки, которые хотите заполнить одинаковыми данными. Быстро выделить ячейки помогут приёмы, описанные выше.

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

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

Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу . Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
Более 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. Нажмите Модуль > Вставить вставить Модуль window и скопируйте в него приведенный ниже 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. Нажмите Ok or Применить, то все выбранные диапазоны будут применять введенную вами формулу. Смотрите скриншот:

Примечание:

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

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

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

В первую очередь разберемся с формулами при создании правил условного форматирования.

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

Условное форматирование с помощью формулы

Рассмотрим следующий пример.

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

Использование стандартных шаблонов условного форматирования Использование стандартных шаблонов условного форматирования

Именно поэтому интересующее нас значение вынесем в отдельное поле.

Затем выделим диапазон с окладами (1) и создадим собственное правило условного форматирования (3). При задании условия (4) мышью укажем нужные ячейки в таблице (5). Затем зададим само форматирование (6), например, изменим фон на красный.

Создание собственного правила условного форматирования Создание собственного правила условного форматирования

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

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

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

Итак, скопируем формулу из правила условного форматирования и вставим ее в столбец рядом. Раскопируем формулу по диапазону.

Мы видим, что все значения одинаковы и являются ложью. Также пробежав по столбцу со значениями в строке формул увидим неизменную формулу с абсолютными ссылками. То есть весь столбец зависит от значения его первой ячейки (С6), в чем легко убедиться, изменив ее значение на удовлетворяющее условию.

Значение ячейки С6 меняет форматирование всего столбца Значение ячейки С6 меняет форматирование всего столбца

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

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

Интересующие нас значения будут ИСТИНОЙ, а значит в случае с условным форматированием такая ячейка ему подвергнется.

Осталось лишь скопировать формулу и изменить правило условного форматирования (1). Сразу можно изменить и само условное форматирование, поменяв фон на менее едкий, а также сделав шрифт полужирным (2).

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

1) Сделать зависимый выпадающий список, в который можно добавлять данные;

2) Добиться того, чтобы новые строки сразу попадали в сводную таблицу при обновлении;

3) Отображать на графике только данные за последние 7 дней, причем новые данные вносятся ежедневно;

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

5) В новом столбце сослаться на каждую пятую ячейку соседнего столбца

Как видите, задачи интересные и нетривиальные. Если Вы хотите научиться их решать в два счета, то стоит уделить несколько минут изучению функции СМЕЩ.

Синтаксис и алгоритм работы функции

Функция СМЕЩ умеет возвращать ссылку на диапазон заданного размера (высота и ширина), отстоящий от стартовой ячейки на заданное число строк и столбцов.

Функция имеет следующий синтаксис:

=СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам;[высота];[ширина])

Как видите, у неё есть 5 аргументов:

1) " Ссылка ". Обязательный аргумент. Указывает стартовую ячейку, от которой будет происходить смещение. Здесь можно указать ссылку на ячейку или на диапазон ячеек. Во втором случае смещение будет считаться от левой верхней ячейки диапазона;

2) " Смещ_по_строкам ". Обязательный аргумент. Указывает, на сколько строк нужно сместиться от заданной ячейки. Положительные числа означают смещение вниз, отрицательные - вверх, ноль - без смещения;

3) " Смещ_по_столбцам ". Обязательный аргумент. Указывает, на сколько столбцов нужно сместиться от заданной ячейки. Положительные числа означают смещение вправо, отрицательные - влево, ноль - без смещения;

4) " Высота" . Необязательный аргумент. Высота в строках возвращаемого диапазона. Если опустить - высота будет равна высоте диапазона, указанного в аргументе "Ссылка";

5) " Ширина" . Необязательный аргумент. Ширина в строках возвращаемого диапазона. Если опустить - ширина будет равна ширине диапазона, указанного в аргументе "Ссылка".

На рисунке ниже представлен алгоритм работы.

1) В качестве "Ссылки" задана ячейка B2.

2) От нее происходит смещение на 2 строки вниз (так как число 2 положительное). Мы оказываемся в ячейке B4.

3) Из ячейки B4 происходит смещение на 3 столбца вправо (так как число 3 положительное). В итоге мы попадаем в ячейку E4.

4) Принимая ее за верхнюю левую, выделяем диапазон из 3 строк и 4 столбцов. Результат - диапазон E4:H6.

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