Как применить формулу ко всему столбцу в Google Таблицах (3 простых способа)
В Google Таблицах есть несколько действительно полезных формул, которые помогут автоматизировать работу и сэкономить много времени. А при работе с формулами в Гугл Таблицах вам часто нужно применять одну и ту же формулу для всего столбца (поскольку большую часть времени наши данные расположены вертикально).
Если у вас есть сотни или тысячи ячеек в столбце, вы не можете вручную применить формулу к каждой ячейке. Есть способы применить формулу ко всему столбцу в Google Таблицах.
В этом коротком руководстве я покажу вам несколько способов скопировать и применить формулу ко всему столбцу в Google Таблицах за несколько шагов.
Дважды щелкните маркер заполнения, чтобы скопировать формулу
В Google Таблицах есть функция дескриптора заполнения, которая может сэкономить ваше время, когда вам нужно применить формулу ко всему столбцу (она работает только со столбцами, а не со строками).
Предположим, у вас есть набор данных, показанный ниже, где есть формула в ячейке C2, и вы хотите применить эту же формулу ко всем ячейкам в столбце C (до C10).
Ниже приведены шаги для этого:
- Выберите ячейку, в которой уже есть формула (в этом примере ячейка C2)
- Поместите курсор в нижнюю правую часть выделения (ту, которая выглядит как маленький толстый синий квадрат). Вы заметите, что курсор изменится на значок плюса.
- Дважды щелкните левой кнопкой мыши (или клавиатурой)
Вышеупомянутые шаги мгновенно заполнят столбец той же формулой до последней заполненной ячейки.
Внимание: хотя этот метод применения формулы ко всему столбцу отлично работает, у него есть одно ограничение. Он будет заполняться только до последней непрерывной заполненной ячейки. Если у вас есть пустые строки (пустая соседняя ячейка), использование автозаполнения остановится прямо перед ним. Один из способов избежать этого — удалить любую пустую строку (или заполнить любую пустую ячейку) в наборе данных.
Копировать формулу с помощью маркера заполнения
Если у вас небольшой набор данных, вы также можете вручную перетащить маркер заполнения, чтобы убедиться, что он заполняет ячейки, и применить ту же формулу ко всему столбцу.
Этот метод может быть полезен, когда у вас есть несколько пустых ячеек / строк в наборе данных и вы не можете использовать метод двойного щелчка для копирования формул.
Ниже приведены шаги по перетаскиванию формулы во весь столбец (также работает для строк):
- Выберите ячейку, в которой уже есть формула (в этом примере ячейка C2)
- Поместите курсор в нижнюю правую часть выделения (ту, которая выглядит как маленький толстый синий квадрат). Вы заметите, что курсор изменится на значок плюса.
- Щелкните левой кнопкой мыши и перетащите, чтобы охватить все ячейки, в которые вы хотите скопировать формулу.
- Оставьте кнопку мыши / клавиатуры.
Хотя этот метод немного сложно использовать, если у вас большой набор данных, вы можете очень хорошо использовать его с меньшими (десятки или несколько сотен строк данных).
Применить формулу ко всему столбцу с помощью формулы массива
Еще один быстрый и эффективный метод применения формулы ко всему — использование формул динамического массива в Google Таблицах.
Этот метод не требует частого перетаскивания мышью и может быть весьма эффективным, если вам приходится часто копировать формулы.
Предположим, у вас есть набор данных, показанный ниже, где есть формула в ячейке C2, и вы хотите применить эту же формулу ко всем ячейкам в столбце C (до C10).
Ниже приведена формула, которая заполнит формулу во всем столбце (и вам нужно только поместить эту формулу в ячейку C2):
Поскольку это формула массива, она может обрабатывать массив диапазонов, а затем выдавать выходные данные во всем столбце (который имеет тот же размер, что и входные аргументы).
Одним из недостатков использования формулы массива для применения формулы ко всему столбцу является то, что вы не сможете удалить часть массива. Если вы попытаетесь удалить содержимое из любой ячейки, кроме той, в которую вы добавили формулу, ничего не произойдет. Однако вы можете удалить весь массив, выбрав ячейку C2 и нажав клавишу Delete.
Итак, есть несколько методов, которые можно использовать для применения формулы ко всему столбцу в Google Таблицах.
Как протянуть формулу в гугл таблицах?
При работе с гугл таблицей часто необходимо протянуть одну формулу на другие ячейки. Давайте рассмотрим два простых способа, как это можно это сделать быстро.
Перед нами две одинаковые таблицы, в которых есть формула в столбце «В» и её необходимо протянуть на оставшиеся ячейки.
Первый способ.
Первый шаг. Выделим ячейку «В5», в которой есть нужная формула. Обратим внимание, что в левом нижнем углу есть небольшой квадрат.
Второй шаг. Наводим курсор на этот квадратик, чтобы появился на экране черный крест, после чего зажимаем левую клавишу мыши и протягиваем на ячейки «С5» и «D5». В результате формулы появились в этих ячейках.
Второй способ.
Первый шаг. Выделим ячейку «В10», после чего нажмем одновременно на клавиатуре две клавиши «Ctrl+C», это обозначает, что мы копируем данную ячейку. После чего выделяем диапазон ячеек с «С10» по «D10». И нажимаем теперь на клавиатуре одновременно другие клавиши: «Ctrl+V». В результате формулы скопируются на нужный диапазон.
Как добавлять формулы и функции
В Google Таблицах можно выполнять различные виды вычислений, используя функции для создания формул. Полный список доступных функций можно найти здесь.
Нажмите «Создать копию» ниже, чтобы получить образец таблицы и тренироваться во время просмотра видео.
Как использовать формулы
- Откройте таблицу.
- Введите в ячейку знак равенства (=) и название функции. Примечание. Система может автоматически предлагать формулы и диапазоны на основе имеющихся данных.
- Окно с названием и синтаксисом вводимой формулы, а также примерами ее использования будет открыто на протяжении всего редактирования. Чтобы увидеть статью полностью, нажмите «Подробнее» в нижней части окна подсказок.
Совет. Вам доступны подсказки подходящих функций для формул, которые вы вводите. Следовать подсказкам не обязательно.
Чтобы включить или отключить подсказки, вверху страницы нажмите ИнструментыВключить подсказки формул.
Дополнительные возможности при создании формул
Режим выбора диапазона
- При редактировании формулы рядом с курсором появится значок выбора диапазона в виде серой скобки. После этого вы можете задать нужный диапазон с помощью стрелок на клавиатуре.
- Чтобы включить или выключить этот режим, используйте быстрые клавиши F2 или Ctrl + E. Когда режим отключен, вы можете перемещать курсор в поле ввода, нажимая стрелки на клавиатуре.
- Чтобы выбрать диапазон, можно также просто выделить нужные ячейки в таблице.
Редактирование диапазона
- Если в формуле выделен текст диапазона, нажмите F2 или Ctrl + E, чтобы перейти в режим выбора диапазона и внести необходимые правки.
- Чтобы внести изменения в любую часть диапазона внутри формулы, нажмите Shift + F2 или Shift + Ctrl + E во время редактирования текста.
Примечание. Диапазон, используемый в формуле, может содержать ячейки, которые расположены не рядом. Чтобы выбрать несколько ячеек, выделяйте их по очереди, удерживая клавишу Ctrl (Windows) или Cmd (macOS).
Исправления формулы
Если после применения формулы к диапазону возникнут ошибки, на экране появится окно «Рекомендуемая формула» с советами по их устранению.
Как принять или отклонить рекомендации:
- Нажмите «Принять» или «Отклонить» .
- Нажмите Ctrl + Ввод на компьютере с Windows или Cmd + Return, если у вас компьютер с macOS.
Как включить или выключить рекомендации в отношении формул:
- Вверху слева на странице нажмите ИнструментыАвтозаполнение.
- Выберите Включить исправления формул.
Функция, используемая в одной и той же ячейке с другой функцией, называется вложенной. Расчет начинается с функции самого нижнего уровня. Вложенная функция заключается в скобки и становится одним из компонентов функции уровнем выше.
Предположим, нужно вычислить абсолютное значение суммы чисел в диапазоне ячеек А1:А7. Чтобы подсчитать эту сумму, введите в ячейку формулу «=СУММ(A1:A7)».
Чтобы получить абсолютное значение, нужно объединить формулу суммы с формулой абсолютного значения. Введите обе формулы в одну ячейку. Это будет выглядеть так: «=ABS(СУММ(A1:A7))». Обратите внимание, что сначала будет вычислена функция «=СУММ()», а ее результат будет использован как аргумент функции «=ABS()».
Цветовое кодирование упрощает ввод и редактирование формул. Когда вы добавляете в формулу ячейки, они выделяются различными цветами. То же самое происходит, когда вы нажимаете на ячейку, содержащую законченную формулу.
Чтобы уменьшить или увеличить панель формул, нажмите на ее нижнюю часть и перетащите вверх или вниз.
Примечание. Чтобы изменить размер панели, вы также можете нажать на нее и использовать быстрые клавиши:
- Ctrl + стрелка вверх или Ctrl + стрелка вниз (Windows);
- Ctrl + Option + Стрелка вверх или Ctrl + Option + Стрелка вниз (macOS).
Вызывает динамически подключаемую библиотеку или программный ресурс. Google Таблицы не поддерживают эту функцию, так как ресурс может быть недоступен на всех устройствах.
Совет. Вместо этой функции можно использовать макросы или скрипты приложений Google Apps.
Позволяют работать с кубами данных в Excel.
Совет. Если вы хотите применять похожие функции, то можете воспользоваться коннекторами данных.
Возвращает информацию о файле документа, например путь к нему.
Примечание. В Google Таблицах сделан акцент на совместную работу в Интернете, и значительная часть информации, полученная этим способом, может быть недоступна или непонятна большинству пользователей.
Получает регистрационный идентификатор от Windows.
Примечание. Эта функция не поддерживается, так как Google Таблицы не связаны ни с одной операционной системой.
Получает данные от сервера автоматизации COM (Component Object Model).
Совет. Не у всех есть доступ к серверу COM, поэтому вы можете использовать макросы или скрипты приложений Google Apps.
Полностью зависит от Windows.
Примечание. Эта функция не поддерживается, так как Google Таблицы не связаны ни с одной операционной системой.
Массив формул в Google таблицах
Продолжаем изучать Google таблицы. Сегодня поговорим о формулах, и как правильно их применять в Google таблицах.
Как мы обычно применяем формулы в таблицах? Например, нам надо просто посчитать сумму с 2х столбцов. Мы пишем формулу:
И растягиваем ее до конца нужного фрагмента:
Получаем в каждой ячейке строки Total формулу =Ax+Bx. А если у нас строк 100, а если 1000? Тянуть такую формулу вниз можно будет до обеда… Но не расстраивайтесь, у нас есть выход. Достаточно всего одной формулы только в одной ячейке, и она автоматически заполнит весь столбец! Вам не нужно больше растягивать формулу на всю таблицу. Имя этой формуле ARRAYFORMULA().
Как она работает? Все просто. Вы записываете логическое выражение так же, как и обычно, только оборачиваете это формулой arrayformula(). Сейчас разберем это на примере. Пишем в первом столбце где нам необходимо посчитать сумму нашу формулу:
Для того что бы захватить всю таблицу, в конце столбцов убираем номера ячеек:
Не забываем закрыть формулу скобкой и нажимаем Enter:
Обратите внимание: Ниже формулы должны быть пустые ячейки, иначе вы получите сообщение об ошибке, и формула не сработает.
В нашем примере мы получили формулу, которая считает сумму столбцов на всю высоту листа. Таким образом написав формулу всего в одной ячейке, мы применили ее на весь лист.
Казалось бы, мы уже сегодня сделали большой шаг вперед и научились всего одной формулой считать значения по всему листу. Давайте не будет останавливаться на достигнутом. Я покажу вам еще один лайфхак для применения этой формулы (и не только).
Вернемся к нашему примеру. Если нам понадобится удалить строку номер 2 с нашими значениями, то формулу суммы нам придется писать заново в новой ячейке. Давайте запишем ее в первой строчке, вместе с названием столбца. А неужели так можно? Я вам скажу – Да! Не только можно, но и нужно. Для этого воспользуемся записью массива (не пугайтесь, на словах кажется что это все сложно, но разбирая наш пример по шагам, вы поймете что нет ничего страшного �� )
Что мы видим на картинке? В ячейку с названием столбца мы поместили, как нашу формулу, так и описание столбца. Разберем синтаксис написания = Если вы запишите таким образом значения в ячейку, то Google таблицы распознают тут массив, и поставят “Значение1” в первую ячейку этого столбца, “Значение2”, соответственно, во вторую и так далее.
Мы, в нашем примере показали, что “Total” нам необходимо записать в первой ячейке, а массив формул во второй. Теперь, мы можем удалять строки с цифрами, это никак не помешает нашей формуле высчитывать нужные нам значения от начала таблицы и до конца.
Мои рекомендации: Если у вас есть формула, которую необходимо применить ко всей длине листа, записывайте её в первую ячейку, обернув в массив формул =arrayformula() с использованием логического выражения =
Напоминаем вам, что у нас вы можете заказать сайт или мобильное приложение для вашего бизнеса.