Полезные функции Google Sheets для повседневного использования
Электронные таблицы предлагают отличную платформу для управления таблицами данных. Но есть ли простые функции Google Sheets для ежедневных вычислений? Узнайте ниже.
Функция СУММ Google Таблиц
Я считаю, что наиболее необходимой операцией в таблицах является нахождение общей суммы различных значений. Первое, что приходит на ум, это добавить каждую интересующую ячейку:
=Е2+Е4+Е8+Е13
Но эта формула станет чрезвычайно трудоемкой, если учесть слишком много ячеек.
Правильный способ добавления ячеек — использовать специальную функцию Google Sheets — SUM, которая автоматически перечисляет все ячейки с помощью запятых:
=СУММ(Е2,Е4,Е8,Е13)
Если диапазон состоит из соседних ячеек, просто укажите его первую и последнюю ячейки, даже если где-то между ними есть пустые ячейки. Таким образом, вы избежите перечисления каждой ячейки в формуле SUM Google Таблиц.
Кончик. Другой способ добавить СУММУ — выбрать столбец с числами и выбрать СУММА под Формулы значок:
Результат будет вставлен в ячейку прямо под выбранным диапазоном.
Кончик. Наш Электроинструменты имеет функцию автосуммы. Один клик — и ваша активная ячейка вернет сумму значений из всего столбца выше.
Усложню задачу. Я хочу добавить числа из разных диапазонов данных на нескольких листах, например, А4:А8 из Лист1 а также Б4:Б7 из Лист2. И я хочу суммировать их в одной ячейке:
=СУММ(‘Лист1′!A4:A8;’Лист2’!B4:B7)
Как видите, я просто добавил еще один лист в формулу SUM Google Sheets и разделил два разных диапазона запятой.
Процентные формулы
Я часто слышу, как люди спрашивают о нахождении процентного соотношения различных сумм. Обычно это рассчитывается по процентной формуле Google Sheets следующим образом:
=Процент/Всего*100
То же самое работает и всякий раз, когда вам нужно проверить, какую часть представляет то или иное число от суммы:
=Часть/Всего*100
В моей таблице, где я веду учет всех продаж за последние 10 дней, я могу рассчитать процент каждой продажи от общего объема продаж.
Сначала я иду на E12 и нахожу общий объем продаж:
=СУММ(Е2:Е11)
Затем я проверяю, какую часть продаж в первый день составляют продажи в F2:
=E2/$E$12
Также рекомендую внести некоторые коррективы:
- Повернуть Е2 к абсолютной ссылке – $E$12 – чтобы убедиться, что вы делите продажи каждого дня на одну и ту же сумму.
- Примените формат процентного числа к ячейкам в столбце F.
- Скопируйте формулу из F2 во все ячейки ниже — до F11.
Кончик. Чтобы скопировать формулу, используйте один из способов, которые я упоминал ранее.
Кончик. Чтобы убедиться, что ваши расчеты верны, введите ниже F12:
=СУММ(F2:F11)
Если он возвращает 100% — все правильно.
Почему я рекомендую использовать процентный формат?
Ну, с одной стороны, чтобы не умножать каждый результат на 100, если вы хотите получить проценты. С другой стороны, чтобы избежать деления результатов на 100, если вы хотите использовать их для любых дальнейших непроцентных математических операций.
Вот что я имею в виду:
Я использую формат процентного числа в ячейках C4, B10 и B15. Все формулы Google Sheets, которые ссылаются на эти ячейки, намного проще. Мне не нужно делить на 100 или добавлять символ процента (%) к формулам в C10 и C15.
Этого нельзя сказать о C8, C9 и C14. Я должен сделать эти дополнительные настройки, чтобы получить правильный результат.
Формулы массива
Для работы с большим количеством данных в Google Sheets, как правило, используются вложенные функции и другие более сложные вычисления. Для этой цели в Google Таблицах также есть формулы массива.
Например, у меня есть таблица продаж на одного клиента. Мне любопытно найти максимальную продажу молочный шоколад к Смит проверить, могу ли я дать ему дополнительную скидку в следующий раз. Я использую следующую формулу массива в E18:
=ArrayFormula(MAX(IF(($B$2:$B$13=”Кузнец”)*($C$2:$C$13=”Молочный шоколад”),$E$2:$E$13,””)))
Примечание. Чтобы завершить любую формулу массива в Google Таблицах, нажмите Ctrl+Shift+Enter, а не просто Enter.
В результате я получил 259 долларов.
Моя первая формула массива в E16 возвращает максимальную покупку, сделанную Смитом — 366 долларов:
=ArrayFormula(MAX(IF(($B$2:$B$13=”Кузнец”),$E$2:$E$13)))
E17 показывает максимальную сумму денег, потраченную на молочный шоколад — 518 долларов:
=ArrayFormula(MAX(IF(($C$2:$C$13=”Молочный шоколад”),$E$2:$E$13)))
Теперь я собираюсь заменить все значения, используемые в формулах Google Таблиц, ссылками на их ячейки:
Вы заметили, что изменилось?
=ArrayFormula(MAX(IF(($B$2:$B$13=B18)*($C$2:$C$13=C18),$E$2:$E$13,””)))
Вот что у меня было раньше:
=ArrayFormula(MAX(IF(($B$2:$B$13=”Кузнец”)*($C$2:$C$13=”Молочный шоколад”),$E$2:$E$13,””)))
Точно так же, манипулируя значениями в ячейках, на которые вы ссылаетесь, вы можете быстро получить разные результаты в зависимости от разных условий, не меняя саму формулу.
Формулы Google Sheets для ежедневного использования
Давайте взглянем на еще несколько примеров функций и формул, удобных для повседневного использования.
Пример 1
Предположим, ваши данные записаны частично в виде чисел и частично в виде текста: 300 евро, итого – 400 долларов. Но вам нужно извлечь только числа.
Я знаю только функцию для этого:
=REGEXEEXTRACT(текст, регулярное_выражение)
Он вытягивает текст по маске с регулярным выражением.
- текст – это может быть ссылка на ячейку или любой текст в двойных кавычках.
- регулярное_выражение – ваша текстовая маска. Тоже в двойных кавычках. Он позволяет создавать практически любую текстовую схему.
Текст в моем случае представляет собой ячейку с данными (А2). И я использую это регулярное выражение: [0-9]+
Это означает, что я ищу любое количество (+) чисел от 0 до 9 ([0-9]) пишутся друг за другом:
Если в числах есть дроби, регулярное выражение будет выглядеть так:
“[0-9]*\.[0-9]+[0-9]+” для чисел с двумя десятичными знаками
“[0-9]*\.[0-9]+” для чисел с одним десятичным знаком
Примечание. Google Sheets видит извлеченные значения как текст. Вам нужно преобразовать их в числа с помощью функции ЗНАЧЕНИЕ или с помощью нашего инструмента Преобразовать.
Пример 2 — объединение текста с формулой
Формулы в тексте помогают получить аккуратную строку с некоторыми итогами — числами с их краткими описаниями.
Я собираюсь создать такие строки в строках 14 и 15. Для начала я объединяю ячейки в этих строках с помощью Формат > Объединить ячейки а затем подсчитайте сумму для столбца E:
=СУММ(Е2:Е13)
Затем я помещаю текст, который я хотел бы иметь в качестве описания, в двойные кавычки и объединяю его с формулой, используя амперсанд:
=”Общий объем продаж шоколада: “&СУММ(E2:E13)&” долларов”
Чтобы сделать мои числа десятичными, я использую функцию ТЕКСТ и устанавливаю формат: “#,##0”
Другой способ — использовать функцию СЦЕПИТЬ в Google Таблицах, как я использовал в A15:
=СЦЕПИТЬ(“Общая скидка для клиентов: “,ТЕКСТ(СУММА(F2:F13),”#.##”),”доллары”)
Пример 3
Что делать, если вы загружаете данные откуда-то, и все числа отображаются с пробелами, например 8 544 вместо 8544? Вы знаете, что Google Таблицы вернут их в виде текста.
Вот как превратить эти значения, записанные в виде текста, в «обычные числа»:
=ЗНАЧ(ПОДСТАВИТЬ(“8 544″,” “,””))
или же
=ЗНАЧ(ПОДСТАВИТЬ(A2,” “,””))
где A2 содержит 8 544.
Как это работает? Функция ПОДСТАВИТЬ заменяет все пробелы в тексте (проверьте второй аргумент — пробел в двойных кавычках) на «пустую строку» (третий аргумент). Затем VALUE преобразует текст в числа.
Пример 4
Есть некоторые функции Google Таблиц, которые помогают манипулировать текстом в ваших электронных таблицах, например, изменять регистр на регистр предложений. Если у вас есть что-то странное, например источник данныхты можешь получить Источник данных вместо:
Позвольте мне объяснить это подробно. Я беру первый символ в ячейке:
=ВЛЕВО(A1,1)
и переключите его на верхний регистр:
=ВЕРХНИЙ(ЛЕВЫЙ(A1,1))
Затем я беру оставшийся текст:
=ВПРАВО(A1,ДЛИН(A1)-1)
и принудительно перевести его в нижний регистр:
=НИЖНИЙ(ПРАВЫЙ(A1,ДЛИН(A1)-1))
Наконец, я объединяю все части формулы амперсандом:
=ВЕРХНИЙ(ЛЕВЫЙ(A1,1))&НИЖНИЙ(ПРАВЫЙ(A1,ДЛИН(A1)-1))
Кончик. Вы можете переключаться между делами в один клик с помощью соответствующей утилиты из нашего Power Tools.
Конечно, Google Таблицы могут предложить гораздо больше. Не бойтесь разных сложных формул — просто пробуйте и экспериментируйте. Ведь эти наборы инструментов позволяют решать множество разных задач. Удачи! 🙂
