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

Обновлено: 22.01.2025

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

Вкладка 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 является определение нужных значений, с которыми будут проводиться операции. Предлагаю на простых примерах ознакомиться с тем, как можно зафиксировать эти значения.

У меня есть таблица с данными:

Работа с числами.

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

1. Первым делом, выделяем любую ячейку напротив первого значения и вводим формулу " =B2*2 ":

Главное, чтобы ячейка с формулой находилась на одной строке со значением, которое оно будет брать из ячейки. В нашем случае, ячейка E2 находится на одной строке с ячейкой B2 .

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

Скопировать значение ячейки E2 на все остальные ячейки в этом столбце можно нажав на правый нижний угол этой ячейки и переместив указатель мыши до последней строки:

3. В итоге получилось следующее:

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

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

Работа со значениями ячеек.

Давайте заменим цифру 2 из предыдущего примера на значение какой-нибудь ячейки. Я решил усложнить задачу и умножить его не на значение напротив, а на значение ниже:

В таком случае, нумерация в столбце B будет продолжаться после цифры 2, а в столбце E после цифры 4:

Каждый столбец использует свою нумерацию, не зависимо от других.

Здесь мы подходим к теме нашей статьи.

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

Разработчики предусмотрели функцию, которая начнет работать тогда, когда Вы напишите в формуле знак доллара " $ ". Но тут есть небольшие нюансы:

Если написать " $C4 ", то неизменным останется только столбец, в нашем случае это " C ", а значения после 4 также будут меняться по порядку.
Если написать " C$4 ", то неизменным останется строка " 4 ", а столбцы будут меняться при копировании.
Если написать " $C$4 ", то неизменными будут и строка и столбец.

Вернемся к нашей формуле и допишем знак доллара перед строкой и столбцом:

Теперь, при копировании, программа будет оставлять значение ячейки " C4 ", а значения столбца " B " будут изменяться согласно порядковому номеру. Что нам и было нужно.

Давайте еще выделим другую ячейку и посмотрим ее формулу:

Все верно! Ячейка C4 зафиксирована, а другая ячейка поменяла свой номер на номер строки.

Спасибо за прочтение этой статьи! Если понравилось - ставьте лайки. Задавайте вопросы в комментариях. Буду рад помочь!

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

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

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

Отображение или скрытие формул в ячейках активного листа с помощью параметров Excel

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

1. Нажмите Файл > Параметры в Excel 2010/2013 или щелкните Кнопка управления > Параметры Excel в Excel 2007;

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

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

3. Нажмите OK чтобы сохранить настройки.

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

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

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

Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!

1. Нажмите Kutools > Показать / Скрыть > параметры просмотра , чтобы открыть диалоговое окно "Параметры просмотра".

2. В открывшемся диалоговом окне «Параметры просмотра» установите флажок Формулы вариант (или снимите флажок Формулы вариант для скрытия формул) сначала нажмите кнопку Применить ко всем листам кнопку и, наконец, нажмите кнопку Ok кнопка. Смотрите скриншот:

Внимание: Если вы хотите отображать (или скрывать) формулы только на активном листе, установите (или снимите) флажок Формулы и нажмите Ok кнопку.

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

Освободи Себя параметры просмотра полезности Kutools for Excel позволяет быстро отображать или скрывать большинство настроек Microsoft Excel, таких как внутренние вкладки, панель формул, строка состояния, окна на панели задач, линии сетки, разрывы страниц, нулевые значения, вертикальная полоса прокрутки, горизонтальная полоса прокрутки, вкладки листов и т. д. Это сэкономит ваше время на поиске этих настроек, когда вам нужно их показать или скрыть.

Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now

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

Оба вышеуказанных метода покажут все формулы на текущем листе и автоматически изменят ширину столбца в Excel. Но иногда вы можете просто показать формулы в указанном диапазоне. В этом состоянии вы можете попробовать Kutools for Excel's Преобразовать формулу в текст утилита.

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

Внимание: Выделите текст формул и нажмите Kutools > Content > Преобразовать текст в формулу , чтобы скрыть формулу и показать рассчитанные результаты.

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

Внимание: Если вы хотите снова отобразить вычисленные результаты вместо формул, просто выберите диапазон, который вы хотите преобразовать, а затем щелкните значок Kutools > Content > Преобразовать текст в формулу.

Демо: показать или скрыть формулы в ячейках указанного диапазона / активного листа / всех листов в Excel

Один щелчок, чтобы удалить формулы, но сохранить значения / числа из выбранных ячеек в Excel

При применении формул в Excel, как правило, результат вычислений отображается в ячейке, а формула отображается в строке формул. Теперь Kutools для Excel К фактическому Утилита может помочь пользователям Excel быстро удалить все формулы, но сохранить результаты вычислений из указанных ячеек Excel одним щелчком мыши. Полнофункциональная бесплатная 30-дневная пробная версия!

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

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

1 Простое протягивание формулы

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

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

Аргументы в формуле будут изменяться соответственно новому расположению. И если в самой первой ячейке это были F7 и G7, в последней позиции столбца это будет уже F12 и G12. Соответственно, если начинать распространять формулы по строкам, изменяться будут не цифры, а буквы в обозначениях ячеек.

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

2 Быстрое автозаполнение

Еще один способ в Excel протянуть формулу до конца столбца с более высокой по сравнению с первой методикой скоростью. Требует от пользователя применить такие действия:

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