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

Содержание

10 лайфхаков, которые помогут упростить работу в Excel

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

Лайфхаки сделаны на примере EXCEL 2013-2016, но будут работать и в более ранних/поздних версиях.

1. Сохранить формулу как значение. Копировать только данные без форматирования

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

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

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

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

Найти эту команду можно следующим образом: нажать стрелочку на данной панели ➤ выбрать меню Другие команды ➤ в разделе Все команды найти необходимую и добавить на панель. После этого данной командой можно будет воспользоваться кратким сочетанием клавиш <ALT>+порядковый номер данной команды на ленте. В моем случае <ALT>+1.

2. Скопировать формулу так, чтобы сохранились оригинальные ссылки

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

Чтобы формула заработала в скопированной ячейке нужно выполнить следующую последовательность действий: на исходной ячейке В1 нажимаем кнопку F2 ➤ в строке формул копируем данную формулу

При помощи команды Вставить вставляем данную формулу в нужную ячейку. И получаем нужные данные:

3. Быстро найти ячейки на листе, содержащие формулы

Если Вы работаете в своем документе, то вопрос этот скорее всего не возникнет. Но если нужно быстро разобраться в чужом – Вам эти знания скорее всего пригодятся. Чтобы буквально за одну секунду получить данные о том, какие ячейки содержат формулы, необходимо: зайти на вкладку Главная ➤ группа Редактирование ➤ меню Найти и выделить ➤ Формулы. Сразу по факту запуска команды на листе будут выделены все ячейки, содержащие формулы.

4. Создать в ячейке список

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

Как это сделать: заходим на вкладку Данные ➤ группа Работа с данными ➤ Проверка данных ➤ и в открытом окне на вкладке Параметры для типа данных выбираем Список, а источником может быть либо часть Вашего документа, либо введенные вручную через запятую данные. Также на этой вкладке Вы сможете для себя найти еще много интересных возможностей.

5. Быстро вставить или удалить строку/столбец

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

<Shift>+<пробел> — выделить всю строку
<Ctrl>+<пробел> -выделить весь столбец
<Ctrl>+<-> — удалить выделенный столбец/строку
<Ctrl>+<+> — добавить строку/столбец перед выделенным

6. Сортировка по нескольким столбцам

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

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

И в итоге получаем таблицу в ожидаемом виде:

7. Автоматически заполнить дни недели / рабочие дни и пр

Для того, чтобы мы не писали все эти данные вручную, создатели Excel о нас уже позаботились и нам достаточно в первую ячейку ввести Пн и за нижний правый уголок ячейки протянуть курсор до нужного места, после чего в возникшем контекстном меню выбрать нужную нам команду, в данному случае — команду Заполнить:

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

8. Быстро

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

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

Но если в таблице есть хоть одна пустая строка, именно на ней застопорится распространение формулы:

Для этого проще поступить так: через команду Копировать скопировать формулу верхней ячейки ➤ выделить весь интересующий диапазон ➤ воспользоваться командой Вставить, тогда все формулы правильно проставятся в весь выделенный диапазон:

9.

Умная таблица

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

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

10.

Сделать так, чтобы шапка документа печаталась на каждой странице

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

После этого шапка таблицы распечатается на каждом листе документа:

Вернуться к списку статей

Как применить формулу ко всему столбцу в Google Таблицах (3 простых способа)

Содержание:

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

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

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

Итак, приступим!

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

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

Предположим, у вас есть набор данных, показанный ниже, где есть формула в ячейке C2, и вы хотите применить эту же формулу ко всем ячейкам в столбце C (до C10).

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

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

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

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

Копировать формулу с помощью маркера заполнения

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

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

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

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

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

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

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

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

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

= ArrayFormula (A2: A11-B2: B11)

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

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

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

Надеюсь, вы нашли этот урок полезным!

Как скопировать формулу на весь столбец (2 метода) в Excel

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

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

Введите формулу в ячейку, которая находится в верхней части столбца/диапазона, в который вы хотите скопировать формулы
Выберите ячейку, содержащую формулу, затем нажмите маркер заполнения и удерживайте щелчок (ручка заполнения выглядит как знак креста/плюса и появляется при наведении курсора на крошечный синий квадрат в правом нижнем углу выделения ячейки)
Перетащите ручку заполнения вниз в нижнюю часть столбца/ диапазон, который вы хотите, чтобы ваши формулы копировали в
Отпустите щелчок, когда вы дойдете до последней ячейки, в которую вы хотите скопировать формулу
Альтернативный метод: Вы также можете копировать и вставлять ячейки, содержащие формулы, для копирования формул. Щелкните ячейку с формулой, нажмите Ctrl + C на клавиатуре, чтобы скопировать ее, затем выберите другую ячейку и нажмите Ctrl + V на клавиатуре, чтобы вставить формулу

В этой статье показано, как копировать формулы в Excel , но нажмите здесь, если хотите узнать, как копировать формулы в Google Sheets.

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

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

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

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

Автоматическая корректировка ссылок на ячейки при копировании формул

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

В качестве быстрого примера корректировки ссылки, которая происходит при копировании формул, о которой я подробно расскажу позже… обратите внимание на формулу, введенную в ячейку C2 на рисунке выше: =A2+B2

Когда эта формула копируется в ячейку под ней, скопированная формула скорректирует ссылки на строки и теперь будет: =A3+B3

Или, если бы эта формула была скопирована в ячейку справа от нее, скопированная формула скорректировала бы свои ссылки на столбцы и тогда была бы: =C2+D2

Существует способ предотвратить изменение этих ссылок, если вы хотите, но мы сохраним это на потом.

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

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

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

«Автоматическое создание ряда значений»

Автозаполнение по сравнению с ARRAYFORMULA

Если вы ищете способ применить формулу ко всему столбцу, возможно, вы искали единственную формулу, которая может справиться с этой задачей, не имея для копирования нескольких формул, и это можно сделать с помощью функции МАССИВФОРМУЛА в Excel.

Но в этой статье мы будем использовать автозаполнение/заполнение для копирования формул.

Копирование во весь столбец вместо диапазона ячеек

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

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

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

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

Автозаполнение в сравнении с копированием и вставкой формул

Следует отметить, что использование автозаполнения в формулах фактически аналогично копированию и вставке ячеек с формулами из одного места в другое (используя Ctrl + C для копирования и Ctrl + V вставить).

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

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

Дело в том, что автозаполнение и копирование/вставка делают одно и то же.

Ярлыки электронных таблиц

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

Если вы не хотите использовать ярлыки и предпочитаете просто перетаскивать маркер заполнения, это прекрасно.

Ярлыки выбора ячеек

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

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

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

Ctrl + Щелчок

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

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

Shift + клавиши со стрелками

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

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

Shift + Ctrl + клавиши со стрелками

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

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

Если в ячейках нет данных, выделение расширится до последней ячейки листа… а если в ячейках уже есть данные, то выделение расширится до края, где данные останавливаются (до достижения пустая ячейка).

Другими словами, этот метод позволяет быстро выбирать «кирпичики» данных или выбирать целые строки/столбцы при отсутствии данных.

Выбор целых столбцов или строк (каждой отдельной ячейки)

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

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

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

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

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

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

Ярлыки автозаполнения «Заполнить вниз» и «Заполнить справа»

Теперь, когда вы знаете, как быстро выбирать ячейки, вы можете эффективно использовать ярлыки «Заполнить вниз» и «Заполнить справа».

Сочетание клавиш для заполнения: (Ctrl + D)

Чтобы использовать сочетание клавиш для заполнения для копирования формул, выберите диапазон ячеек, в который вы хотите скопировать формулы, где верхняя ячейка вашего выбора содержит формулу, которая будет скопировано. Затем нажмите клавиши Ctrl+D. Это скопирует формулу, которая находится в верхней ячейке выделения, через весь выбранный диапазон/столбец.

Заполнить правой клавишей быстрого доступа: (Ctrl + R)

Существует также сочетание клавиш для использования автозаполнения по горизонтали. Это называется «заполнить справа», и сочетание клавиш — Ctrl + R.

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

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

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

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

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

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

Итак, давайте, наконец, приступим к рассмотрению примеров.

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

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

Формула: Приведенная ниже формула сначала вводится в ячейку C3, а затем копируется в диапазон C3: C9, для этого примера

= A3

Мы хотим скопировать эту формулу в ячейки ниже ячейки C3, и для этого выполните следующие действия:

Выберите ячейку C3
Щелкните и перетащите маркер заполнения вниз, пока не достигнете ячейки C9

Как показано на рисунке ниже, после перетаскивания маркера заполнения вниз формула была скопирована в диапазон C3:C9, а список имена в столбце A теперь можно просмотреть в столбце C.

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

Как заполнить строку формулами в Excel (Заполнить справа)

Теперь давайте рассмотрим пример, в котором используется автозаполнение по горизонтали. Здесь будет использоваться «заполнить справа», чтобы заполнить строку формулами, которые ссылаются на ячейки в другой строке.

На рисунке ниже показан список дат в строке 1, и мы хотим отобразить те же даты в строке 3.

Формула: Приведенная ниже формула сначала вводится в ячейку B3, а затем копируется в диапазон B3: h4, для этого примера

=B1

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

Выберите ячейку B3
Щелкните и перетащите маркер заполнения вправо до достижения ячейки h4

Как показано на рисунке ниже, после перетаскивания маркера заполнения вправо формула была скопирована в диапазон B3:h4, а список даты в строке 1 теперь можно просмотреть в строке 3.

Как заполнить столбец формулами (сочетая имя и фамилию)

В этом примере мы заполним столбец формулами, которые объединяют имя и фамилию. Объединение имен — распространенная задача, требующая копирования формулы вниз по столбцу в Excel.

На рисунке ниже показан список имен в столбце A и фамилий в столбце B.

Мы хотим объединить эти имена, чтобы они отображались в формате «Фамилия, Имя» в столбце D.

Формула: Приведенная ниже формула сначала вводится в ячейку D3, а затем копируется в диапазон D3:D9, например,

=B3&», «&A3

. Мы хотим скопировать эту формулу в ячейки ниже ячейки D3 и для этого выполните следующие действия:

Выберите ячейку D3
Щелкните и перетащите маркер заполнения вниз, пока не достигнете ячейки D9

Как показано на рисунке ниже, после перетаскивания маркера заполнения вниз формула была скопирована в диапазон D3:D9, а список первых /фамилии, которые были в столбцах A и B, теперь объединены в столбце D.

(Дополнительные сведения об использовании этой формулы для объединения имен/списков см. в этой статье об объединении столбцов.)

Как повторить формулу (Сложение и умножение)

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

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

На рисунке ниже показан список чисел в столбце A и другой список чисел в столбце B. E умножит эти два списка чисел.

В этом примере мы заполним/скопируем обе эти формулы одновременно.

Формула: Приведенные ниже формулы сначала вводятся в ячейки D3 и E3, а затем копируются в диапазоны D3:D9 и E3:E9, для этого примера

=A3+B3 вводится в ячейку D3

=A3*B3 введено в ячейку E3

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

Выберите ячейки D3 и E3
Нажмите и перетащите манипулятор заполнения вниз до строки 9

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

Как заполнить формулу в Excel (функция ЕСЛИ)

Вот еще один пример, показывающий, как заполнить формулу в Excel, который использует функцию ЕСЛИ для демонстрации быстрого копирования формул.

На приведенном ниже рисунке показан отчет об оценках учащихся, где имена учащихся указаны в столбце A, а их оценки — в столбце B. » если оценка в ячейке B3 больше 0,6 (60%)… и если оценка не больше 0,6, формула будет отображать слово «Неудовлетворительно».

=ЕСЛИ(B3>0.6,»Успешно»,»Неудачно»)

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

Выберите ячейку C3
Щелкните и перетащите маркер заполнения вниз, пока не достигнете ячейки C9

. Как показано на рисунке ниже, после перетаскивания маркера заполнения вниз формула была скопирована в диапазон C3:C9, и указание того, является ли учащийся сданным/неудовлетворительным, может теперь можно просмотреть в столбце C.

Как выполнить автозаполнение таблицы формул в Excel

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

В этом примере мы заполним таблицу формулами умножения, чтобы создать «Таблицу умножения».

На рисунке ниже показана таблица со списком чисел, который увеличивается вниз по столбцу A и по строке 1. Мы хотим скопировать нашу исходную формулу, введенную в ячейку B2, во всю таблицу ячеек, чтобы каждая из формул умножает значения, отображаемые в верхней/левой части каждого столбца/строки.

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

Обратите внимание на знаки доллара ($), которые я включил в формулу, которые блокируют столбец/строку, указанные непосредственно после него, от корректировки при копировании ячеек/формул. Это то, что называется абсолютной ссылкой.

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

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

Формула: Приведенная ниже формула сначала вводится в ячейку B2, а затем копируется в диапазон B2:K10, для этого примера

=$A2*B$1

Мы хотим скопировать эту формулу в ячейки ниже и правее ячейки B2, и для этого выполните следующие действия:

Выберите ячейку B2
Щелкните и перетащите маркер заполнения вниз и вправо, пока не достигнете ячейки K10

Как показано на рисунке ниже, после перетаскивания маркера заполнения вниз и вправо формула была скопирована в диапазон B2:K10.

Теперь вы знаете, как быстро и легко копировать формулы в столбцы и другие заданные диапазоны в Excel!

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

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

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

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

Содержание

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

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

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

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

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

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

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

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

Чтобы использовать ее,

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

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

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

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

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

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

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

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

Вот как это можно использовать:

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

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

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

Другой способ применить формулу ко всему столбцу — использовать сочетание клавиш Ctrl + D . Это в значительной степени работает как метод перетаскивания.

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

Кроме того, вы можете провести через весь столбец, а затем применить формулу к первой ячейке столбца. Затем вместо нажатия клавиши Enter вы нажимаете Ctrl + Enter.

Копировать и вставить формулу

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

Для этого

Выберите ячейку, в которую вы применили формулу.
Далее скопируйте его с помощью сочетания клавиш Ctrl + C в Windows. Нажмите Command + C , если вы используете Mac.
Теперь выберите ячейки ниже в столбце и нажмите Ctrl + V (Windows) или Command + V (Mac), чтобы вставить.