Как вбить формулу в excel для всего столбца: Как быстро применить формулу ко всему столбцу или строке с / без перетаскивания в Excel?

Содержание

Ссылки на целые столбцы могут доставить вам неприятности с Excel

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

Уровень мастерства: Новичок

Смотреть учебник

Смотреть на YouTube и подписываться на наш канал

Скачать файл Excel

Вот рабочая тетрадь, которую я использую в видео.

Являются ли ссылки на целые столбцы хорошим вариантом?

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

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

Что такое ссылка на весь столбец?

Как бы это ни звучало, ссылка на весь столбец — это ссылка в формуле, которая указывает весь столбец данных от верха листа до низа. Он обозначается путем ввода буквы столбца, двоеточия, а затем снова буквы столбца (например, D:D ).

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

Преимущества использования ссылки на весь столбец

1. Легкость чтения

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

2. Автоматически включает дополнения

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

3. Избегайте ошибок длины диапазона

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

4. Простота записи

И, наконец, ссылки на целые столбцы легко вставить в формулу. В дополнение к тому, что просто набрать , вы также можете вставлять ссылки на целые столбцы в формулу, просто щелкнув букву столбца 9.0004 в заголовке или с помощью сочетания клавиш Ctrl + Пробел , когда выбрана ячейка из этого столбца.

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

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

1. Снижение производительности

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

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

Я провел несколько собственных тестов и убедился, что это правда.

При использовании функции СУММПРОИЗВ для списка из 1000 формул разница между использованием ссылки на весь столбец по сравнению с ссылкой на диапазон ячеек составила более 15 секунд . Это время, очевидно, увеличится для электронных таблиц с большим количеством данных/формул.

При использовании функции ФИЛЬТР , которая представляет собой формулу динамического массива, разница во времени составила почти 20 секунд .

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

2. Неправильные результаты

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

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

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

Альтернативные решения для ссылок на целые столбцы

Существует три альтернативы использованию ссылок на целые столбцы:

1. Таблицы Excel

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

Кроме того, новые данные, добавляемые или удаляемые в таблицу, автоматически учитываются во всех формулах, ссылающихся на столбец таблицы.

2. Динамические именованные диапазоны

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

В видео выше я показываю один из способов создания динамического именованного диапазона с помощью функций OFFSET и COUNTA.

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

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

3. Зоны разлива

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

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

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

Заключение

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

Если вам интересно узнать больше о таблицах Excel , ознакомьтесь с этим сообщением: Учебное пособие по таблицам Excel.

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

Как повторить формулу в Excel для всего столбца (5 простых способов)

Получите БЕСПЛАТНЫЕ продвинутые упражнения Excel с решениями!

Иногда мы применяем формулу в ячейке, а затем повторяем ту же формулу для всего столбца в Microsoft Excel . Но повторять одну и ту же формулу снова и снова для каждой клетки может быть утомительной и трудоемкой задачей. Когда у вас есть большой набор данных, иногда невозможно повторить формулу вручную. У вас возникли проблемы с повторением формулы для всего столбца в Excel? Этот учебник поможет вам узнать , как повторить формулу в Excel для всего столбца 5 простых способов . Давайте начнем!

Скачать практическую рабочую тетрадь

5 простых способов повторить формулу в Excel для всего столбца

1. Использование маркера заполнения

2. Использование опции «Заполнить вниз»

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

4. Применение метода копирования и вставки

5. Ввод формулы в несколько ячеек

То, что нужно запомнить

Заключение

Статьи по Теме

Загрузить рабочую тетрадь

Вы можете скачать рабочую книгу Excel отсюда.

5 простых способов повторить формулу в Excel для всего столбца

Чтобы повторить формулу для всего столбца в Excel, мы взяли набор данных, подобный следующему рисунку, где у нас есть имена из 6 учащихся и их оценки по математике и английскому языку . Мы добавим оценки одного учащегося, используя формулу Excel , а затем повторим формулу для всего столбца, используя Ручка заполнения , Заполнение вниз опция , Сочетание клавиш , Копирование и вставка метод, и Ввод формулы в несколько ячеек метод .

1. Использование ручки заполнения

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

Шаги:

  • Сначала выберите ячейку E5 и введите следующую формулу:

=C5+D5

  • Теперь нажмите Введите .
  • В результате вы увидите вывод, похожий на изображение ниже, на котором будут отображаться общие оценки John .
  • Во-вторых, выберите ячейку E5 и перетащите маркер заполнения на весь столбец Всего баллов .
  • Наконец, вы увидите результат, подобный приведенному ниже, где будут отображаться общие оценки всех учащихся.

Подробнее: Как повторить числовой шаблон в Excel (5 методов)

2. Использование опции заполнения вниз

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

Шаги:

  • Сначала выберите ячейку E5 и введите следующую формулу:

=C5+D5

  • Во-вторых, нажмите Введите .
  • В результате вы увидите вывод, подобный изображенному ниже, на котором будут отображаться общие оценки John .
  • После этого выберите все ячейки столбца Всего баллов , включая ячейку Е5 .
  • Кроме того, перейдите на вкладку Главная .
  • Затем щелкните раскрывающийся список Заполнить и выберите параметр Вниз .
  • В результате вы увидите вывод, подобный приведенному ниже, где будут отображаться общие оценки всех учащихся.

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

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

Шаги:

  • Сначала выберите ячейку E5 и введите следующую формулу:

=C5+D5

  • Во-вторых, нажмите Введите .
  • В результате вы увидите вывод, подобный изображенному ниже, на котором будет отображаться общее количество баллов 9. 0003 Джон .
  • После этого выделите все ячейки столбца Total Marks включая ячейку E5 .
  • Затем нажмите Ctrl+D .
  • Таким образом, вы увидите вывод, подобный показанному ниже, в котором будут отображаться общие оценки всех учащихся.

Подробнее: Как найти повторяющиеся числа в Excel (5 простых способов)

Аналогичные показания

  • Как повторить несколько строк в Excel (4 эффективных способа)
  • Как повторять строки вверху в Excel (3 подходящих способа)
  • [Исправлено!] Строки Excel для повторения в верхней части выделены серым цветом
  • Как повторить строку заголовка при прокрутке в Excel (6 способов)
  • Как повторять строки в Excel внизу (5 простых способов)

4.

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

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

Шаги:

  • Сначала выберите ячейку E5 и введите следующую формулу:

=C5+D5

  • Во-вторых, нажмите Введите .
  • В результате вы увидите вывод, подобный изображенному ниже, на котором будут отображаться общие оценки John .
  • После этого скопируйте ячейку E5 , нажав Ctrl+C , как показано ниже.
  • Кроме того, выберите ячейки столбца, кроме ячейки E5 , где вы хотите применить эту формулу, как показано ниже.
  • Следовательно, вставьте формулу, нажав Ctrl + V , и вы увидите вывод, подобный приведенному ниже, где будут отображаться общие оценки всех учащихся.

Подробнее: Как повторять строки в Excel на основе значения ячейки (4 простых способа)

5. Ввод формулы в несколько ячеек

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

Шаги:

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

=C5+D5

  • Во-вторых, нажмите Ctrl + Введите .
  • Таким образом, вы увидите вывод, подобный показанному ниже, в котором будут отображаться общие оценки всех учащихся.

Подробнее: Как повторить значения ячеек в Excel (6 быстрых методов)

Что нужно помнить

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

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