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

Автозаполнение ячеек в Excel. Как протянуть формулы в Excel. Быстро заполнить дни недели.

Как найти и удалить повторы и дубликаты в Excel

Как посчитать количество символов в ячейке Excel? Работа с текстом

Полезнейшим свойством таблиц Excel является автозаполнение ячеек. Если вам нужно заполнить определенное значение (от 1 до 10), последовательность (к примеру дней) или формулу в большое количество ячеек построчно, это можно выполнить почти автоматически. Как сделать это быстро и удобно? Читайте далее.

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

Содержание

• 1. Простое автозаполнение ячеек
• 2. Создание собственного списка автозаполнения ячеек
• 3. Как быстро растащить автозаполнение
• 4. Как быстро заполнить формулой ячейки в столбе в 10 тыс строк
• 5. Сохранить форматы при протягивании формул
• Похожие статьи

1.

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

Попробуйте сделать задание из примера. Заполните в ячейки А1 и А2 числа 1 и 2 соответственно, затем наведите курсор мышки на нижний правый угол диапазона (см. рисунок ниже), появится черный прямой крест, который обозначает готовность к автозаполнению. Потяните его вниз, зажав левую кнопку мыши. Произойдет автозаполнение, при этом будет отображаться до какого числа вы можете растянуть данные.

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

2. Создание собственного списка автозаполнения ячеек

Индивидуально можно создать любую последовательность для заполнения. Нажмите круглую кнопку слева вверху (Файл) – Параметры – Дополнительно – Изменить списки. В появившимся окне добавьте свой список. Здесь можно создавать как текстовые, числовые, так и смешанные последовательности. После добавления последовательность будет доступна для способа в пункте 1.

3. Как быстро растащить автозаполнение

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

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

Моя любимая хитрость. Предположим, что у нас пример из пункта 3, но необходимо заполнить формулу в столбце В для 50 000 ячеек, при этом ни в левом ни в правом диапазоне нет сплошных данных. Пункт 3 не поможет. Что делать? Хватать крестик и тащить вниз как в пункте 1? Неет, это долго. Сперва копируйте формулу из ячейки В2 (ctrl+c). Затем находите конец диапазона в соседнем столбец (для примера ячейка А50000), либо мышью, либо сочетанием клавиш ctrl + стрелка вниз. Выделяем ячейку в столбце, где нужно заполнить формулу т.е. В50000. Нажимаем ctrl + shift + стрелка вверх — таким образом выделятся все данные в столбце В выше ячейки В50000. Теперь вставляем формулу, которую мы скопировали в самом начале.

5. Сохранить форматы при протягивании формул

Чтобы сохранить формат при протягивании ячеек, существует специальная функция «Параметры автозаполнения». Делаем автозаполнение, жмем на появившуюся кнопку и выбираем Заполнить только форматы. Очень удобно!

Как найти и удалить повторы и дубликаты в Excel

Как посчитать количество символов в ячейке Excel? Работа с текстом

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

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

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

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

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

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

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

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

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

В приведенном выше примере, чтобы убедиться, что формула скопирована правильно, давайте выберем какую-нибудь ячейку в столбце C, скажем, C4, и просмотрим ссылку на ячейку в строке формул. Как видно на скриншоте ниже, с формулой все в порядке — относительно строки 4, именно так, как и должно быть:

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

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

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

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

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

Дважды щелкните знак «плюс», чтобы заполнить весь столбец

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

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

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

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

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

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

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

Чтобы преобразовать диапазон ячеек в таблицу Excel, просто выделите все ячейки и нажмите Ctrl + T. Если вы предпочитаете визуальный способ, выберите диапазон, перейдите на вкладку Вставка > группу Таблицы на ленте Excel и нажмите кнопку Таблица .

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

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

Само собой разумеется, что дескриптор заполнения — это самый быстрый способ скопировать формулу в Excel. Но что, если вы хотите скопировать формулу Excel в несмежные ячейки или за пределы исходных данных? Просто используйте старый добрый способ копирования и вставки:

1. Щелкните ячейку с формулой, чтобы выбрать ее.
2. Нажмите Ctrl + C, чтобы скопировать формулу.
3. Выберите ячейку или диапазон ячеек, куда вы хотите вставить формулу (чтобы выбрать несмежные диапазоны, нажмите и удерживайте клавишу Ctrl).
4. Нажмите Ctrl + V, чтобы вставить формулу.
5. Нажмите Enter, чтобы завершить вставленные формулы.

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

Ввод формулы в несколько ячеек одним нажатием клавиши (Ctrl + Enter)

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

1. Выберите все ячейки, в которые вы хотите ввести формулу. Чтобы выделить несмежные ячейки, нажмите и удерживайте клавишу Ctrl.
2. Нажмите F2, чтобы войти в режим редактирования.
3. Введите формулу в одну ячейку и нажмите Ctrl + Enter вместо Enter. Вот и все! Формула будет скопирована во все выбранные ячейки, и Excel соответствующим образом скорректирует относительные ссылки на ячейки.

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

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

Как вы уже знаете, при копировании формулы вниз по столбцу в Excel вы можете использовать параметр «Заполнить без форматирования», который позволяет скопировать формулу, но сохранить существующее форматирование ячеек назначения. Эксель Функция копирования и вставки обеспечивает еще большую гибкость в отношении параметров вставки.

1. Выберите продажу, содержащую формулу.
2. Скопируйте эту ячейку, нажав Ctrl + C. Либо щелкните ячейку правой кнопкой мыши и выберите Копировать из контекстного меню или нажмите кнопку Копировать на вкладке Главная > Буфер обмена .
3. Выберите все ячейки, в которые вы хотите скопировать формулу.
4. Щелкните правой кнопкой мыши выбранные ячейки и выберите Формулы под Опции вставки :

Чтобы просмотреть дополнительные параметры вставки, щелкните стрелку под кнопкой Вставить на ленте. Например, вы можете выбрать Формулы и форматирование чисел , чтобы вставить только формулу и форматирование чисел, например процентный формат, денежный формат и т. п.:

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

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

Формулы Excel редко встречаются в электронной таблице в одиночестве. В большинстве случаев вы вводите формулу в одну ячейку, а затем копируете ее в другие ячейки того же столбца или строки, чтобы выполнить тот же расчет для группы данных. И если ваша формула содержит относительные ссылки на ячейки (без $), Excel автоматически настраивает их так, чтобы каждая формула работала с данными в своей строке или столбце. В большинстве случаев это именно то, что вы хотите. Например, если у вас есть формула =A1*2 в ячейке B1, и вы скопируете эту формулу в ячейку B3, формула изменится на =A3*2 .

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

Скопируйте или переместите одну формулу без изменения ссылок на ячейки

Если вам нужно скопировать или переместить только одну формулу, сделать точную копию несложно.

1. Выберите ячейку с формулой, которую вы хотите скопировать.
2. Выберите формулу в строке формул с помощью мыши и нажмите Ctrl + C, чтобы скопировать ее. Если вы хотите переместить формулу, нажмите Ctrl + X, чтобы вырезать ее.
3. Нажмите клавишу Esc, чтобы выйти из строки формул.
4. Выберите ячейку назначения и нажмите Ctrl + V, чтобы вставить туда формулу.

Как вариант, можно войти в режим редактирования и скопировать формулу в ячейку как текст:

1. Выберите ячейку с формулой.
2. Нажмите F2 (или дважды щелкните ячейку), чтобы войти в режим редактирования.
3. Выделите формулу в ячейке с помощью мыши и нажмите Ctrl + C, чтобы скопировать ее.
4. Выберите ячейку назначения и нажмите Ctrl+V. Это позволит вставить формулу точно, без изменения ссылок на ячейки, поскольку формула была скопирована как текст.

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

Скопируйте диапазон формул без изменения ссылок на ячейки

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

Способ 1. Используйте абсолютные или смешанные ссылки на ячейки

Если вам нужно сделать точную копию формул с относительными ссылками на ячейки (например, A1), лучшим способом будет изменить их на абсолютных ссылок ($A$1), чтобы зафиксировать ссылку на данную ячейку, чтобы он оставался статичным независимо от того, куда перемещается формула. В других случаях вам может понадобиться использовать смешанные ссылки на ячейки ($A1 или A$1) для блокировки столбца или строки. Пока нет особого смысла? Хорошо, давайте рассмотрим следующий пример.

Предположим, у вас есть таблица, в которой рассчитываются цены на фрукты в евро на основе цены в долларах США в столбце B и обменного курса в ячейке C2:

Как видно на снимке экрана выше, формула включает абсолютную ссылку на ячейку ($C$2) для фиксации обменного курса к ячейке C2 и относительную ссылку на ячейку B5, поскольку вы хотите, чтобы эта ссылка корректировалась для каждой строки. И этот подход хорошо работает до тех пор, пока формулы остаются в столбце C.

Но давайте посмотрим, что произойдет, если вам нужно, скажем, переместить цены в евро из столбца C в столбец F. Если скопировать формулы обычным способом, путем копирования/вставки ячеек, формула из ячейки C5 (= B5 *$C$2) изменится на = D5 *$C$2 при вставке в ячейку F5, что сделает ваши вычисления неверными!

Чтобы исправить это, просто измените относительную ссылку (B5) на смешанную ссылку $B5 (абсолютный столбец и относительная строка). Поместив знак доллара ($) перед буквой столбца, вы привязываете ссылку к столбцу B, независимо от того, куда перемещается формула.

Теперь, если вы скопируете или переместите формулы из столбца D в столбец F или любой другой столбец, ссылка на столбец не изменит , поскольку вы заблокировали его знаком доллара ($B5).

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

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

Способ 2. Копирование формул Excel без изменения ссылок через Блокнот

1. Войдите в режим просмотра формул, нажав сочетание клавиш Ctrl + `, или используя любой другой метод, описанный в разделе Как отобразить формулы в Excel.
2. Выберите все ячейки с формулами, которые вы хотите скопировать или переместить.
3. Нажмите Ctrl + C, чтобы скопировать формулы, или Ctrl + X, чтобы вырезать их. Используйте последний ярлык, если вы хотите переместить формулы в новое место.
4. Откройте Блокнот или любой другой текстовый редактор и нажмите Ctrl + V, чтобы вставить туда формулы. Затем нажмите Ctrl+A, чтобы выделить все формулы, и Ctrl+C, чтобы скопировать их как текст.
5. На листе Excel выберите верхнюю левую ячейку, куда вы хотите вставить формулы, и нажмите Ctrl + V.

Примечания:

• Вы можете вставлять формулы только на тот же рабочий лист , где находятся исходные формулы, если только ссылки не включают имя листа, в противном случае формулы будут нарушены.
• Рабочий лист должен быть в режиме просмотра формул . Чтобы убедиться в этом, перейдите на вкладку Формулы > группу Аудит формул и проверьте, включена ли кнопка Показать формулы .
• После вставки формул нажмите Ctrl + `, чтобы отключить режим просмотра формул.

Метод 3. Точное копирование формул с помощью функции поиска и замены Excel

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

1. Выберите ячейки с формулами, которые вы хотите скопировать.
2. На вкладке Главная перейдите в группу Редактирование и нажмите Найти и выбрать > Заменить… Или просто нажмите Ctrl + H, что является ярлыком для запуска диалогового окна «Найти и заменить» в Excel.
3. В диалоговом окне Find & Replace введите знак равенства (=) в поле Find what . В поле Заменить на введите какой-либо символ или строку символов, которые не используются ни в одной из ваших формул, например ‘, # или \.
   Целью этого шага является преобразование формул в текстовые строки, что не позволит Excel изменить ссылки на ячейки в процессе копирования.

   Примечание. Не используйте звездочку (*) или вопросительный знак (?) для замены, поскольку в Excel это подстановочные знаки, и их использование может затруднить последующие шаги.

4. Нажмите кнопку Заменить все и закройте диалоговое окно Найти и заменить . Все формулы в выбранном диапазоне превратятся в текстовые строки:
5. Теперь вы можете выбрать любые ячейки, нажать Ctrl + C, чтобы скопировать их, выбрать верхнюю ячейку текущего листа , куда вы хотите вставить формулы, и нажать Ctrl + V. Поскольку Excel не интерпретирует формулы без знак равенства как формулы, они будут скопированы точно, без изменения ссылок.
6. Используйте Найти и заменить еще раз, чтобы отменить изменение. Выберите обе области, с исходными формулами и скопированные (чтобы выбрать несмежные области, нажмите и удерживайте Ctrl). Нажмите Ctrl + H, чтобы открыть диалоговое окно «Найти и заменить». На этот раз введите обратную косую черту (\) (или любой другой символ, который вы использовали для первой замены) в поле Найти что и = в поле Заменить на и нажмите кнопку Заменить все . Сделанный!

Ярлыки для копирования формулы Excel в другие ячейки

1. Скопируйте формулу вниз

Ctrl + D — скопировать формулу из ячейки выше и настроить ссылки на ячейки.

Например, если у вас есть формула в ячейке A1, и вы хотите скопировать ее в ячейку A2, выберите A2 и нажмите Ctrl + D.

2. Скопируйте формулу справа

Ctrl + R — скопировать формулу из ячейки слева и настроить ссылки на ячейки.

Например, если у вас есть формула в ячейке A2, и вы хотите скопировать ее в ячейку B2, выберите B2 и нажмите Ctrl + R.

Совет. Оба вышеуказанных сочетания клавиш также можно использовать для копирования формул в несколько ячеек. Хитрость заключается в том, чтобы выбрать как исходную, так и целевую ячейки, прежде чем нажимать ярлык. Например, если вы хотите скопировать формулу из A1 в следующие 9 строк, выделите ячейки A1:A10 и нажмите Ctrl + D.

3. Скопируйте формулу точно вниз

Ctrl + ‘ — Копирует формулу из ячейки выше в текущую выделенную ячейку ровно и оставляет ячейку в режиме редактирования.

Это быстрый способ сделать точную копию формулы без изменения ссылок на ячейки . Например, чтобы скопировать формулу из ячейки A1 в ячейку A2 без изменения ссылок, выберите A2 и нажмите Ctrl + ‘.

Примечание. Не путайте сочетание клавиш Ctrl + ‘ (Ctrl + одинарная кавычка), которое точно копирует формулу из ячейки выше, с Ctrl + ` (Ctrl + клавиша со значком ударения), которая активирует режим отображения формул в Excel.

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

Вас также может заинтересовать

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

Применяется к

Smartsheet

• Про
• Бизнес
• Предприятие

Возможности

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

ПЛАНЫ

• Smartsheet
• Про
• Бизнес
• Предприятие

Разрешения

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

Эта справочная статья появляется в следующих курсах обучения

Курс обучения

Уровень 2: промежуточные пользователи

Узнайте, как собирать, отслеживать, автоматизировать и составлять отчеты о работе.

С помощью формул столбцов можно применять единые вычисления и выражения ко всем строкам листа. Формулы столбцов также будут автоматически применяться к новым строкам, добавленным в любом месте листа. Например, вы можете: 

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

Создание и редактирование формул столбца

Чтобы создать формулу столбца:

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

   ПРИМЕЧАНИЕ. Формулы столбцов не могут ссылаться на ячейки или диапазоны с определенными номерами строк, такими как абсолютные ссылки, конкретные ссылки на ячейки или диапазоны неполных столбцов. Вместо этого используйте @row, ссылки на столбцы и перекрестные ссылки.
    

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

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

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

Полный список функций Smartsheet см. в нашем списке функций.

Ограничения формулы столбца

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

Связанный контент

Статья справки

Создание и редактирование формул в Smartsheet

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