Создавайте и редактируйте формулы Google Таблиц. Объяснение основных функций

Прочтите эту статью, чтобы узнать, как создавать и редактировать формулы в Google Таблицах. К концу вы поймете, как они работают, и получите глубокие знания о ключевых функциях Google Таблиц, таких как СУММ, ЕСЛИ, ВПР и других.

Формулы являются основой любой электронной таблицы. Они нужны вам для расчета и анализа ваших данных и даже для автоматизации некоторых задач. Позвольте мне познакомить вас с миром формул и функций. Я начну с основ и постепенно перейду к основным функциям, которые должен знать каждый пользователь Google Таблиц. Не стесняйтесь сделать копию таблицы практики следить за статьей.

Как создавать формулы в Google Таблицах

Формулы Google Таблиц представляют собой комбинацию операторов и функций, выполняющих вычисления. Каждая формула начинается со знака =, за которым следует функция, например СУММА, СРЗНАЧ или ЕСЛИ.

Вот как можно создавать формулы в Google Таблицах:

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

Кончик. Google Sheets предложит список всех подходящих функций, начиная с введенных вами букв. Вы можете найти полный список функций Google Таблиц. здесь.
Продолжите, добавив содержимое формулы: ссылки на ячейки или диапазоны. Самый простой способ сделать это — ввести их вручную или выбрать мышкой:
=СУММ(E2:E13)
Нажмите Enter, чтобы рассчитать и отобразить результат.

Как ссылаться на другие ячейки в формулах Google Таблиц

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

Ссылка на одну ячейку. Обратитесь к отдельным ячейкам, таким как E2, чтобы использовать их значение в своих расчетах.
Ссылка на несколько ячеек. В таких формулах, как =SUM(E2,E4,E8,E13) или =SUM(E2:E13), вы используете либо несмежные, либо соседние ячейки.
Используйте именованные диапазоны. Вы даже можете присвоить имя диапазону ячеек, например Sales2024, и использовать его в формулах, например =SUM(Sales2024). Подробнее об именованных диапазонах вы узнаете из этой нашей статьи.

Как ссылаться на другие листы в формулах Google Таблиц

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

Вот как вы ссылаетесь на данные из другого листа:

Начните формулу, как обычно, набрав =СУММ(
Затем перейдите к другому листу, щелкнув его вкладку.
Выберите ячейку или диапазон, на который вы хотите сослаться.
Формула автоматически обновится до =СУММ(Лист2!E2:E13), где Лист2 — это имя листа.
Нажмите Enter для подтверждения.

Кончик. Вы также можете вручную ввести ссылку как =Sheet_name!Cell_range.

Лист2!E2:E13

Примечание. Если имя вашего листа содержит пробелы, заключите его в одинарные кавычки:

='Октябрьские продажи'!E2:E13

Как редактировать формулы в Google Таблицах

Итак, ваша формула создана. В будущем, когда ваши данные изменятся, вы, возможно, захотите скорректировать эти диапазоны или добавить дополнительные условия. Например, измените =SUM(E2:E15) на =SUM(E2,E4,E8,E13), чтобы суммировать только определенные продажи.

Вот как можно редактировать формулы в Google Таблицах:

Дважды щелкните ячейку, содержащую формулу, или выберите ячейку и нажмите F2 или Enter.
Измените формулу непосредственно в ячейке или в строке формул над листом:
=СУММ(E2,E4,E8,E13)
Нажмите Enter, чтобы применить изменения.

Как перейти к определенной ссылке на ячейку

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

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

Как использовать вложенные функции в Google Таблицах

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

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

Пример 1. Использование прямых значений

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

=СУММ(40,50,55,20,10,88)

Пример 2. Использование ссылок на ячейки

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

=СУММ(E5,E8,E13)
=СУММ(E2:E13)

Пример 3. Использование вложенных функций

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

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

=СРЕДНЕЕ(E2:E13)

А в D16 вы можете округлить результат с помощью

=ОКРУГЛ(D15, -1)

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

=ОКРУГЛ(СРЕДНЕЕ(E2:E13),-1)

Здесь функция СРЗНАЧ используется в качестве аргумента функции ОКРУГЛ. Эта формула Google Таблиц сначала вычисляет среднюю сумму продажи, а затем округляет результат:

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

Как скопировать формулы в Google Sheets

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

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

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

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

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

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

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

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

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

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

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

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

Выберите ячейку, содержащую формулу, которую вы хотите скопировать.
Нажмите Ctrl+C (или Cmd+C на Mac), чтобы скопировать формулу.
Выберите ячейки, в которые вы хотите вставить формулу (вы можете использовать Ctrl/Command и щелкнуть, чтобы выбрать несколько несмежных ячеек).
Нажмите Ctrl+V (или Cmd+V на Mac), чтобы вставить формулу в выбранные ячейки.

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

Как отображать формулы в Google Таблицах

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

Чтобы отобразить формулы в Google Таблицах, выберите в меню «Вид» > «Показать» > «Формулы»:

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

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

Однако если вы работаете с большими и сложными электронными таблицами, возможно, вам стоит рассмотреть возможность использования нашего дополнения «Расширенный поиск и замена».

Расширенный поиск и замена: просмотр всех формул Google Таблиц.

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

Вот что вы можете сделать с дополнением:

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

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

Ключевые примеры формул Google Таблиц

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

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

1. СУММ — сложение значений

Функция СУММ — одна из самых простых, но полезных функций в Google Таблицах. Он суммирует значения в указанном диапазоне.

я уже использовал его ранее суммировать все продажи:

=СУММ(E2:E13)

Также вы можете складывать цифры по одному или нескольким условиям, например, подсчитывать общую стоимость товаров стоимостью более 100 долларов. Синтаксис таких формул немного сложнее, поэтому, если хотите узнать все об этом, перейдите к этой статье: СУММЕСЛИ в Google Таблицах с примерами формул.

2. СРЕДНЕЕ — вычисление среднего значения

Функция СРЗНАЧ вычисляет среднее значение набора чисел.

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

=СРЕДНЕЕ(E2:E13)

3. COUNT & COUNTA — подсчет ячеек

COUNT подсчитывает, сколько ячеек в диапазоне содержат числа. СЧЕТЗ подсчитывает все непустые ячейки:

=СЧЁТ(E2:E12)
=СЧЕТЗ(E2:E12)

Существует также функция СЧЕТЕСЛИ, которая подсчитывает количество ячеек, соответствующих определенному условию, например, всех ячеек, число которых превышает 50, или тех, которые содержат определенное слово или фразу. Эта функция немного сложнее, поэтому не стесняйтесь узнать о ней в этом сообщении в блоге: Функция СЧЕТЕСЛИ в электронной таблице Google с примерами формул.

4. ЕСЛИ, И, ИЛИ — условная логика

Функция ЕСЛИ помогает создавать логические тесты и возвращать значения в зависимости от того, истинно или ложно условие:

=IF(D2>100, «Выше ожидаемого», «Ниже ожидаемого»)

Эта формула проверяет, превышает ли значение в D2 100. Если да, результат будет выше ожидаемого, в противном случае — ниже ожидаемого:

Вы можете комбинировать его с AND или OR и даже вкладывать несколько IF для более сложных условий, например:

=ЕСЛИ(И(D2>100,E2>200),”Выше ожидаемого”,”Ниже ожидаемого”)

Чтобы узнать больше об этом, посетите эту статью: Функция ЕСЛИ в Google Таблицах — примеры использования и формулы.

5. CONCATENATE — соединение текста

Если вы хотите объединить текст из нескольких ячеек, вам пригодится CONCATENATE:

=СЦЕПИТЬ(B2,”: “,C2)

Эта формула объединит значения из B2 и C2 с двоеточием и пробелом между ними:

Вы также можете использовать оператор & для более простого подхода. Вы найдете это в следующей статье: Функция CONCATENATE в Google Таблицах для объединения текста из ячеек.

6. ВПР — поиск данных

Функция ВПР ищет значение в первом столбце диапазона и возвращает значение в той же строке из другого столбца.

=ВПР(B16,B1:E13,4,ложь)

Эта формула ищет фиолетовый цвет в столбце B и возвращает соответствующее значение из столбца E:

Однако эта функция имеет свои ограничения и требует обучения. Итак, вот полное руководство, к которому вы сможете привыкнуть: Google Sheets VLOOKUP с примерами.

7. Уникальные функции Google Таблиц

Google Sheets предлагает несколько уникальных функций, которых вы не найдете в Excel. Они включают в себя:

ФОРМУЛА МАССИВА: применить формулу ко всему диапазону ячеек одновременно.
ИМПОРТРАНЖ: импортируйте данные из другого файла Google Таблиц.
ЗАПРОС: выполняйте SQL-запросы, т. е. расширенный фильтр для ваших данных.

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

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

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

Таблица практики

Создавайте и редактируйте формулы Google Таблиц (сделай себе копию)