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

Обновлено: 21.11.2024

Excel помогает людям и компаниям решать самые сложные задачи. Это приложение Microsoft также позволяет любому легко и просто вычислять некоторые проблемы, которые в противном случае заняли бы много времени вручную.

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

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

Если вам нужно применить ту же формулу, что и в этом случае, предположим, что C1=A1*2, C2=A2*2, C3=A3*3……Cn=An*2. Есть более простой способ. Это. Есть несколько хитрых способов сделать это, кроме перетаскивания формулы все время. Теперь давайте рассмотрим три простых способа сделать это, которые сэкономят вам время и энергию.

Выделить + Enter

Оглавление

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

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

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

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

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

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

Теперь выберите весь столбец. Это относится даже к строке. Выберите столбец и перейдите к другому шагу.

Get Home>Fill>Down — теперь та же формула будет легко применена ко всему столбцу, и будут получены те же результаты.

Kutools для Excel

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

Для выполнения таких операций вы можете использовать инструменты Kutools for Excel Operation. Этот инструмент имеет более 300 расширенных функций, направленных на упрощение работы с Excel. Этот инструмент может в конечном итоге упростить не только формулы, но и несколько других сложных задач, над которыми вы хотели бы работать с помощью Excel.

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

Выберите столбец или строку, к которым вы хотите применить данные. В нашем примере выберите столбец A.

Позже нажмите Kutools>больше>операция.

Снимок экрана ниже дает четкую картину.

Оттуда перейдите ко второму шагу. Щелкнув диалоговое окно инструментов операции, перейдите к выбору пользовательского в окне операции. Теперь введите сокращения вашей формулы или даже введите формулу полностью. В этом случае мы умножим на 2 в пустом поле пользовательского раздела. Наконец, нажмите кнопку «ОК» и подождите, как показано на снимке экрана ниже.

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

Полезные выводы

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

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

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

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

Формулы — это жизнь и кровь электронных таблиц 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.

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

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


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

    Функция суммы принимает входные данные в двух основных формах: отдельные ссылки на ячейки (например, = сумма (a1, b2, c3)) или массив ячеек (= сумма (A1: E1)). Мы будем использовать последний метод:

    Совет 1. Сочетание клавиш ALT + = (нажмите и удерживайте ALT, затем коснитесь =) автоматически создаст функцию суммирования. Когда это возможно, Excel угадывает, какие ячейки вы хотите суммировать, заполняя функцию суммирования.
    Совет 2. После использования сочетания клавиш ALT + = или ввода =sum() используйте клавиши со стрелками, чтобы выбрать соответствующую ячейку. Затем, удерживая нажатой клавишу SHIFT или CTRL + SHIFT, выберите нужный диапазон ячеек.
    Совет 3. Вместо использования клавиатуры вы также можете использовать мышь, чтобы перетащить и выделить нужный диапазон и завершить формулу.

    Суммировать весь столбец

    Чтобы суммировать весь столбец, введите функцию суммирования: =sum( , а затем выберите нужный столбец, либо щелкнув букву столбца в верхней части экрана, либо с помощью клавиш со стрелками, чтобы перейти к столбцу, и с помощью кнопки CTRL + ПРОБЕЛ, чтобы выделить весь столбец. Формула будет иметь вид =сумма(A:A).

    Суммировать всю строку

    Чтобы сложить весь столбец, введите функцию суммирования: =sum( , а затем выберите строку, либо щелкнув номер строки в левой части экрана, либо перейдя к нужной строке с помощью клавиш со стрелками и нажав клавишу SHIFT + ПРОБЕЛ, чтобы выделить всю строку. Формула будет иметь вид =сумма(1:1).

    Добавить сразу несколько столбцов или строк

    Чтобы суммировать столбцы или строки одновременно, используйте формулу вида: =сумма(A:B) или =сумма(1:2). Помните, что вы также можете использовать сочетания клавиш CTRL + ПРОБЕЛ для выбора всего столбца или SHIFT + ПРОБЕЛ для всей строки. Затем, удерживая нажатой клавишу SHIFT, с помощью клавиш со стрелками выберите несколько строк.

    Суммировать несмежные столбцы или строки сразу

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

    Обратите внимание на разницу,
    Функции множественной суммы:

    Запятая-разделитель:

    Суммировать все, кроме заголовка

    Часто вам нужно сложить весь столбец (или строку), кроме заголовка. В Excel 2013 нецелые записи, включенные в формулу суммы, автоматически игнорируются. Если вы включили ячейку A1 (заголовок) в функцию суммы: =sum(A:A), функция суммы будет работать правильно. Однако рекомендуется поддерживать обратную совместимость.

    Вместо этого можно использовать формулу =СУММ(A2:A1048576). Почему 1 048 576? В таблицах Excel всего 1 048 576 строк!

    Чтобы увидеть это, выберите ячейку в пустом столбце. Затем вы используете сочетание клавиш: CTRL + стрелка вниз, чтобы перейти к последней строке на листе.

    Добавить все строки, содержащие данные

    Вместо того, чтобы добавлять весь столбец в конец листа, вы можете добавить только строки, содержащие данные. Для этого сначала запустите функцию SUM. Затем выберите первую строку в столбце, содержащем данные, которые вы хотите суммировать, затем используйте CTRL + SHIFT + стрелка вниз, чтобы выбрать все ячейки в этом столбце (Примечание: будьте осторожны с пустыми ячейками. CTRL + SHIFT + стрелка перейдет к ячейка непосредственно перед пустой ячейкой)

    Магия автосуммирования

    Выберите ячейку выше/ниже или слева/справа от диапазона, который вы хотите суммировать. Затем используйте сочетание клавиш ALT + = или выберите Лента формул > Автосумма. Это автоматически создаст для вас формулу!

    Распространенные ошибки суммирования

    Для получения дополнительной информации об автосумме в Excel посетите веб-сайт Microsoft

    Практический лист Excel

    Попрактикуйтесь в функциях и формулах Excel с помощью наших 100 % бесплатных тренировочных листов!

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