Как использовать формулы в Google Sheets
Сегодня я собираюсь представить формулы Google Sheets. Начну с элементов, из которых они состоят, напомню, как они вычисляются, и расскажу, чем отличаются простые формулы от сложных.
Это то что тебе нужно знать:
Суть формул Google Sheets
Перво-наперво — для построения формулы вам нужны логические выражения и функции.
Функция — это математическое выражение; каждый со своим именем.
Чтобы Google Таблицы знали, что вы собираетесь ввести формулу, а не число или текст, начните вводить знак равенства (=) в интересующую ячейку. Затем введите имя функции и остальную часть формулы.
Кончик. Вы можете проверить полный список всех функций, доступных в Google Sheets здесь.
Ваша формула может содержать:
- ссылки на ячейки
- именованные диапазоны данных
- числовые и текстовые константы
- операторы
- другие функции
Типы ссылок на ячейки
Каждой функции требуются данные для работы, и для указания этих данных используются ссылки на ячейки.
Для ссылки на ячейку используется буквенно-цифровой код — буквы для столбцов и цифры для строк. Например, А1 это первая ячейка в столбце А.
Существует 3 типа ссылок на ячейки Google Sheets:
- Родственник: A1
- Абсолют: $A$1
- Смешанный (наполовину относительный и наполовину абсолютный): $A1 или A$1.
Знак доллара ($) изменяет тип ссылки.
После перемещения относительные ссылки на ячейки изменяются в соответствии с ячейкой назначения. Например, B1 содержит =А1. Скопируйте его на C2, и он превратится в =В2. Так как он был скопирован на 1 столбец вправо и на 1 строку ниже, все координаты увеличились на 1.
Если формулы имеют абсолютные ссылки, они не изменятся после копирования. Они всегда указывают на одну и ту же ячейку, даже если в таблицу добавляются новые строки и столбцы или сама ячейка смещается куда-то еще.
Исходная формула в B1 =A1 =A$1 =$A1 =$A$1 Формула скопирована в C2 =B2 =B$1 =$A2 =$A$1
Таким образом, чтобы предотвратить изменение ссылок при копировании или перемещении, используйте абсолютные.
Чтобы быстро переключаться между относительными и абсолютными значениями, просто выделите любую ссылку на ячейку и нажмите F4 на клавиатуре.
Во-первых, ваша относительная ссылка – А1 – превратится в абсолют – $А$1. Нажмите F4 еще раз, и вы получите смешанную ссылку — 1 австралийский доллар. При следующем нажатии кнопки вы увидите $А1. Другой вернет все в исходное состояние – А1. И так далее.
Кончик. Чтобы изменить все ссылки сразу, выделите всю формулу и нажмите F4.
Диапазоны данных
Google Таблицы используют не только ссылки на отдельные ячейки, но и группы смежных ячеек — диапазоны. Они ограничены верхней левой и нижней правой ячейками. Например, А1:Б5 сигналы для использования всех ячеек, выделенных оранжевым цветом ниже:
Константы в формулах Google Sheets
Постоянные значения в Google Sheets — это те, которые не могут быть рассчитаны и всегда остаются неизменными. Чаще всего это цифры и текст, например 250 (количество), 08.03.2019 (свидание), Выгода (текст). Это все константы, и мы можем изменять их, используя различные операторы и функции.
Например, формула может содержать только постоянные значения и операторы:
=30+5*3
Или его можно использовать для вычисления нового значения на основе данных другой ячейки:
=А2+500
Однако иногда вам приходится изменять константы вручную. И самый простой способ сделать это — поместить каждое значение в отдельную ячейку и ссылаться на него в формулах. Затем все, что вам нужно сделать, это внести изменения в одну ячейку, а не во все формулы.
Итак, если положить 500 к B2, обратитесь к нему с помощью формулы:
=А2+В2
Получить 700 вместо этого просто измените число в ячейке B2, и результат будет пересчитан.
Операторы для формул Google Sheets
В электронных таблицах используются различные операторы для задания типа и порядка вычислений. Они делятся на 4 группы:
- арифметические операторы
- операторы сравнения
- операторы конкатенации
- ссылочные операторы
Арифметические операторы
Как следует из названия, они используются для выполнения математических вычислений, таких как сложение, вычитание, умножение и деление. В результате получаем цифры.
Арифметический оператор Операция Пример + (знак плюс) Сложение =5+5 – (знак минус) Вычитание
Отрицательное число
=5-5
=-5
* (звездочка) Умножение =5*5 / (косая черта) Деление =5/5 % (знак процента) Проценты 50% ^ (знак вставки) Показатель степени =5^2
Операторы сравнения
Операторы сравнения используются для сравнения двух значений и возврата логического выражения: ИСТИНА или ЛОЖЬ.
Оператор сравнения Условие сравнения Пример формулы = Равно =A1=B1 > Больше =A1>B1 < Меньше =A1
Операторы конкатенации текста
Амперсанд (&) используется для соединения (объединения) нескольких текстовых строк в одну. Введите ниже в одну из ячеек Google Sheets, и он вернется Самолет:
=”Воздух” и “крафт”
Или поставить Фамилия к А1 и Имя на B1 и получить Фамилия Имя текст со следующим:
=А1&”, “&В1
Операторы формулы
Эти операторы используются для построения формул Google Sheets и указания диапазонов данных:
Оператор формулы Действие Пример формулы: (двоеточие) Оператор диапазона. Создает ссылку на все ячейки между (и включая) первой и последней упомянутыми ячейками. B5:B15 , (запятая) оператор объединения. Объединяет несколько ссылок в одну. =СУММ(B5:B15;D5:D15)
Все операторы имеют разный приоритет (precedence), что определяет порядок вычисления формулы и, чаще всего, влияет на результирующие значения.
Порядок вычислений и приоритет операторов
Каждая формула в Google Sheets обрабатывает свои значения в определенном порядке: слева направо в зависимости от приоритета оператора. Операторы одного приоритета, например, умножение и деление, вычисляются в порядке их появления (слева направо).
Приоритет операторов Описание : (двоеточие)
(пространство)
(запятая) Оператор диапазона – Знак минус % Процент ^ Возведение в степень * и / Умножение и деление + и – Сложение и вычитание & Объединение нескольких текстовых строк в одну =
<>
<=
>= Сравнение
Как с помощью скобок изменить порядок вычислений
Чтобы изменить порядок вычислений в формуле, заключите в скобки часть, которая должна идти первой. Давайте посмотрим, как это работает.
Предположим, у нас есть стандартная формула:
=5+4*3
Поскольку опережает умножение, а за ним следует сложение, формула вернет 17.
Если мы добавим скобки, игра изменится:
=(5+4)*3
Формула сначала складывает числа, затем умножает их на 3 и возвращает 27.
Скобки из следующего примера означают следующее:
=(A2+25)/СУММ(D2:D4)
- рассчитать значение для A2 и добавить его к 25
- найти сумму значений из D2, D3 и D4
- разделить первое число на сумму значений
Надеюсь, вам не составит труда их обойти, так как порядок вычислений мы усваиваем с самого раннего возраста и вся арифметика вокруг нас выполняется именно так. 🙂
Именованные диапазоны в Google Sheets
Знаете ли вы, что вы можете маркировать отдельные ячейки и целые диапазоны данных? Это делает обработку больших наборов данных быстрой и легкой. Кроме того, вы будете гораздо быстрее ориентироваться в формулах Google Sheets.
Предположим, у вас есть столбец, в котором вы рассчитываете общий объем продаж по продукту и клиенту. Назовите такой диапазон Тотальная распродажа и использовать его в формулах.
Думаю, вы согласитесь, что формула
=СУММ(Всего_Продаж)
намного понятнее и легче читается, чем
=СУММ($E$2:$E$13)
Примечание. Вы не можете создавать именованные диапазоны из несмежных ячеек.
Чтобы определить диапазон, выполните следующие действия:
- Выделите соседние ячейки.
- Перейти к Данные > Именованные диапазоны в меню листа. Соответствующая панель появится справа.
- Задайте имя диапазона и нажмите «Готово».
Кончик. Это также позволяет вам проверять, редактировать и удалять все созданные вами диапазоны:
Выбор правильного имени для диапазона данных
Именованные диапазоны делают ваши формулы в Google Таблицах более удобными, четкими и понятными. Но есть небольшой набор правил, которым вы должны следовать, когда дело доходит до маркировки диапазонов. Название:
- Может содержать только буквы, цифры, символы подчеркивания (_).
- Не должно начинаться с цифры или с «истинных» или «ложных» слов.
- Не должно содержать пробелов ( ) или других знаков препинания.
- Должно быть от 1 до 250 символов.
- Не должно совпадать с самим диапазоном. Если вы попытаетесь назвать диапазон как А1:Б2могут возникнуть ошибки.
Если что-то пойдет не так, например, вы используете пробел в имени Тотальная распродажа, вы сразу получите сообщение об ошибке. Правильное название будет Тотальная распродажа или же Тотальная распродажа.
Примечание. Именованные диапазоны Google Sheets аналогичны абсолютным ссылкам на ячейки. Если вы добавляете строки и столбцы в таблицу, Тотальная распродажа диапазон не изменится. Переместите диапазон в любое место листа – и это не изменит результатов.
Типы формул Google Таблиц
Формулы могут быть простыми и сложными.
Простые формулы содержат константы, ссылки на ячейки на одном листе и операторы. Как правило, это либо одна функция, либо оператор, а порядок вычислений очень прост и понятен — слева направо:
=СУММ(А1:А10)
=А1+В1
Как только появляются дополнительные функции и операторы или немного усложняется порядок вычислений, формула усложняется.
Сложные формулы могут включать ссылки на ячейки, несколько функций, константы, операторы и именованные диапазоны. Их длина может быть огромной. Быстро “расшифровать” их может только их автор (но обычно только если он был построен не более недели назад).
Как легко читать сложные формулы
Есть трюк, чтобы ваши формулы выглядели понятными.
Вы можете использовать столько пробелов и разрывов строк, сколько вам нужно. Это не испортит результат и устроит все самым удобным образом.
Чтобы поместить строку разрыва в формулу, нажмите Alt+Enter на клавиатуре. Чтобы увидеть всю формулу, разверните Панель формул:
Без этих дополнительных пробелов и разрывных строк формула выглядела бы так:
=ArrayFormula(MAX(IF(($B$2:$B$13=B18)*($C$2:$C$13=C18), $E$2:$E$13,””)))
Согласны ли вы, что первый способ лучше?
В следующий раз я углублюсь в создание и редактирование формул Google Sheets, и мы немного больше попрактикуемся. Если у вас есть какие-либо вопросы, пожалуйста, оставьте их в комментариях ниже.
