Как растянуть формулу в Excel на весь столбец

Обновлено: 21.11.2024

Эта страница представляет собой поддерживаемый рекламодателями отрывок из книги Power Excel 2010–2013 от MrExcel — 567 разгаданных тайн Excel. Если вам нравится эта тема, подумайте о покупке всей электронной книги.

Проблема: я ввел функцию ВПР за январь. Мне нужно скопировать формулу в одиннадцать дополнительных столбцов.

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

  • Нажмите F4 три раза при вводе искомого значения. Это изменит A2 на $A2. Один знак доллара гарантирует, что поиск всегда будет возвращаться к столбцу A для искомого значения.
  • Нажмите F4 один раз при входе в таблицу поиска. Это изменит таблицу поиска на четыре знака доллара, $P$4:$AB$227. В качестве альтернативы сначала назовите таблицу поиска, тогда вам не придется использовать знаки доллара. См. «Рассмотрите возможность присвоения имени таблице поиска»

Большая проблема заключается в третьем аргументе. Я обнаружил, что в конечном итоге редактирую каждую скопированную формулу, чтобы изменить 2 на 3, затем на 4, затем на 5 и так далее.

У меня есть два решения для этого.

  • Введите временную строку с числами от 2 до 13, растянутыми по всей строке. Эта строка может быть над таблицей, которую вы пытаетесь построить. Затем, вместо указания 2 в качестве возвращаемого столбца, вы можете указать на B1 и дважды нажать F4, чтобы изменить его на B$1.

  1. Используйте временный столбец с номерами столбцов.
    • Другое решение — заменить ,2 на ,COLUMN(B1). Функция COLUMN возвращает номер столбца данной ячейки. Поскольку B1 находится во втором столбце, она вернет 2. Мне нравится говорить, что это самый необычный способ записи числа 2 в мире. Однако преимущество в том, что, когда вы копируете эту формулу вправо, ссылка внутри Функция COLUMN автоматически изменится на C1, которая находится в столбце 3. Использование этого метода позволяет ввести одну формулу, не имея временных значений в строке 1.

Дополнительные сведения. Второй метод замедлит работу функции ВПР, поскольку Excel должен вычислять функцию СТОЛБЦ в каждой строке таблицы поиска.

Альтернативный метод. Вы можете ускорить ВПР, если добавите один столбец функций ПОИСКПОЗ, а затем используете 12 столбцов невероятно быстрой функции ИНДЕКС. Однако прежде чем вы сможете это сделать, вам нужно узнать об этих двух невероятно загадочных функциях.

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

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

И в Excel есть несколько способов сделать это несколькими щелчками мыши (или сочетанием клавиш).

Давайте рассмотрим эти методы.

Это руководство охватывает:

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

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

Предположим, у вас есть набор данных, как показано ниже, где вы хотите рассчитать комиссию для каждого торгового представителя в столбце C (где комиссия будет составлять 15% от стоимости продажи в столбце B).

Формула для этого будет следующей:

Ниже показан способ применения этой формулы ко всему столбцу C:

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

Чтобы это работало, в соседнем столбце не должно быть данных и в нем не должно быть пустых ячеек. Если, например, в столбце B есть пустая ячейка (скажем, ячейка B6), то этот двойной щелчок с автозаполнением применит формулу только до ячейки C5

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

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

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

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

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

Ниже приведены шаги для этого:

  1. В ячейку A2 введите формулу: =B2*15%
  2. Выбрав ячейку, вы увидите маленький зеленый квадрат в нижней правой части выделения.
  3. Наведите курсор на маленький зеленый квадрат. Вы заметите, что курсор изменится на знак плюса
  4. Удерживая левую клавишу мыши, перетащите ее в ячейку, к которой нужно применить формулу.
  5. Отпустите клавишу мыши, когда закончите.

    6. Использование параметра «Заполнить вниз» (он находится на ленте)

    Еще один способ применить формулу ко всему столбцу – использовать параметр "Заполнить вниз" на ленте.

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

    Ниже приведены шаги по использованию метода заполнения:

    Вышеуказанные шаги берут формулу из ячейки C2 и заполняют ею все выбранные ячейки

    Добавление заливки на панель быстрого доступа

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

    Чтобы добавить его на панель быстрого доступа (QAT), перейдите к параметру «Заполнить вниз», щелкните его правой кнопкой мыши и выберите «Добавить на панель быстрого доступа»

    Теперь в QAT появится значок "Заполнить вниз".

    Использование сочетания клавиш

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

    Ниже приведены инструкции по использованию сочетания клавиш для заполнения формулы:

    1. В ячейку A2 введите формулу: =B2*15%
    2. Выделите все ячейки, к которым вы хотите применить формулу (включая ячейку C2).
    3. Удерживайте клавишу Control, а затем нажмите клавишу D.

    Использование формулы массива

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

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

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

    Это формула массива, которая возвращает 14 значений в ячейке (по одному для B2:B15). Но поскольку у нас есть динамические массивы, результат не будет ограничен одной ячейкой и будет распространяться на весь столбец.

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

    Но если это не так, возможно, это не лучший способ скопировать формулу во весь столбец

    Посредством копирования и вставки ячейки

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

    Ниже приведены шаги для этого:

    1. В ячейку A2 введите формулу: =B2*15%
    2. Скопируйте ячейку (используйте сочетание клавиш Control + C в Windows или Command + C в Mac).
    3. Выделите все ячейки, к которым вы хотите применить одну и ту же формулу (кроме ячейки C2).
    4. Вставьте скопированную ячейку (Ctrl + V в Windows и Command + V в Mac).

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

    Например, если ячейка C2 имеет синий цвет, все рассмотренные до сих пор методы (кроме метода формулы массива) будут не только копировать и вставлять формулу во весь столбец, но и вставлять форматирование (например, цвет ячейки, размер шрифта, полужирный/курсив)

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

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

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

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

    Автоматическое заполнение с помощью "Ctrl-R"

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

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

    Нажмите «Ctrl-R», чтобы автоматически заполнить все ячейки одной и той же формулой.

    Форматирование с помощью параметров автозаполнения

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

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

    Нажмите кнопку "Параметры автозаполнения", чтобы выбрать параметры автоматического заполнения ячеек. Вы можете скопировать только формулу, только форматирование или выбрать «Копировать ячейки», чтобы скопировать и то, и другое.

    • Чтобы скопировать формулу вниз по столбцу, выберите ячейку с формулой, перетащите курсор вниз, пока не будут выбраны все ячейки в столбце, в который вы хотите скопировать формулу, а затем нажмите "Ctrl-D".< /li>
    • Информация в этой статье относится к Microsoft Excel 2013 или Office 365, работающим в Windows 8. Она может незначительно или существенно отличаться в зависимости от других версий или продуктов.

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

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

    Рис. 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 минут.

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