Как настроить формулы в Google таблицах: подробный гайд
Перевод текстов, импорт данных, графики… Формулы сделают все за вас.
Дата публикации: 6 сентября 2022
Время чтения: 6 минут
Дарья Ракова Редакция «Текстерры»
В нашем подробном гайде по Google Таблицам для новичков мы затронули работу с формулами (функциями), но не остановились на ней подробнее, потому что это тема для отдельной статьи.
Формулы нужны, в первую очередь, для удобства работы с таблицами – они помогают делать автоматический подсчет различных показателей, структурировать данные, и даже переводить текст с одного языка на другой, объединять между собой разные массивы данных и так далее. При работе с большими объемами данных работать без формул просто невозможно – об этом вам расскажут на курсах интернет-маркетинга.
Здесь же мы поговорим о самых нужных для digital-специалиста формулах Гугл Таблиц.
Оглавление:
Оглавление:
Как вводить формулы
Есть два способа. Можно вводить формулы вручную:
- Выберите ячейку, в которой хотите сделать формулу. Наберите = и начинайте вводить формулу:
Сервис сам предложит варианты формул
- После того, как вы ввели формулу, заполните ее параметры. У разных формул они, соответственно, разные:
У некоторых функций нужно будет заполнить параметры и условия
- Дальше нужно выбрать диапазон (ячейку, столбец, строку), который формула будет использовать, чтобы преобразовывать данные:
Диапазон можно выбрать, открыв скобки и щелкнув на нужную ячейку, строку или столбец. А если нужно выбрать несколько отдельно стоящих ячеек/столбцов/строк подряд, надо зажать Shift и нажимать на них
Если все сделано правильно, формула заработает. Если нет, то появится значок ошибки, при нажатии на который можно получить расшифровку и инструкцию, как исправить ошибку:
Смотреть галерею
Есть десятки возможных ошибок, все можно исправить, прочитав расшифровку
Есть еще один способ ввести формулу – перейти в «Вставка» → «Функции» и выбрать формулу из списка:
Здесь представлены все возможные формулы Гугл-таблиц
Продвинем ваш бизнес
В Google и «Яндексе», соцсетях, рассылках, на видеоплатформах, у блогеров
Обзор важных функций для digital-специалиста
Формул и функций здесь очень много. Не будем останавливаться на простых (например, формула для суммирования), но расскажем о более сложных, но не менее нужных.
Список всех доступных функций – в справке Google.
Подсчет символов
=ДЛСТР(ячейка)
Эта формула считает количество символов в ячейке. Многие оформляют контент-планы в Google-таблицах. Учитывая то, что у большинства соцсетей есть ограничения по количеству символов в посте, эта формула очень кстати.
Работа формулы =ДЛСТР
Перевод текста из верхнего регистра в нижний (и наоборот)
=СТРОЧН(диапазон)
Формула переводит текст, написанный с использованием Caps Lock, в нижний регистр.
Работа формулы =СТРОЧН(диапазон)
=ПРОПИСН(диапазон)
Эта формула, наоборот, переводит текст в верхний регистр.
Работа формулы =ПРОПИСН(диапазон)
Эти формулы могут пригодиться во время, например, изучения аудитории конкурентов – если вы скопировали список имен и фамилий из сервиса, который парсит аккаунты, а он в верхнем регистре (что бывает), с помощью формулы можно будет легко перевести текст в нижний регистр.
Pepper Ninja, Popsters, Adinblog, Spywords. этими сервисами стоит пользоваться каждому предпринимателю
Импорт данных из других источников
=IMPORTHTML(«ссылка»; «table»; 0; «rus_RU»)
С помощью этой формулы можно перенести любую таблицу или список в свою Google Таблицу. Для этого нужно вставить в формулу ссылку, которая ведет на страницу, где содержится контент, который нужно импортировать.
Таким образом удастся импортировать тот контент, который изначально сделан в виде таблицы или списка – например, рейтинг спортивных команд
Чтобы импортировать список и сохранить его формат в виде списка, нужно в параметрах выбрать не «table», а «list». Все остальные параметры нужно оставить, как есть.
Ваша заявка принята.
Мы свяжемся с вами в ближайшее время.
Перевод текста
=GOOGLETRANSLATE(ячейка; «исходный_язык» или «auto»; «целевой_язык»)
Формула переводит с любого языка. И на любой язык. Большинство языков даже не нужно указывать в качестве исходного – сервис автоматически определяет язык оригинала, для чего нужно вписать команду «auto».
Трюк не сработал с чувашским языком, которого нет в сервисе Google Переводчик. Вероятно, с другими не очень популярными языками будет похожая история
Объединение ячеек
=CЦЕПИТЬ(диапазон;диапазон)
Формула позволяет объединить текст из двух ячеек в одной.
Чтобы текст не «склеился», поставьте между объединяемыми диапазонами аргумент “ “
Эта формула может пригодиться в разных ситуациях. Например, когда у вас есть список имен и номеров, которые нужно объединить в одну ячейку.
Еще эта функция пригодится для комбинирования разных ключевых фраз.
Как использовать ключевые слова в заголовках статей
Разделение фраз
=SPLIT(ячейка; «разделитель»; [тип_разделителя]; [выполнять_ли_удаление_пустых_ячеек])
То же самое, что объединение данных из ячеек, только наоборот. Если вы спарсили список контактов откуда-то, то в одной строке может оказаться несколько параметров: имя, место жительства, телефон. Формула поможет все разделить и избавиться от ненужных символов (запятые, цифры, пробелы).
Нужно указать тип разделителя, который будет удаляться, в нашем примере это “,”
Удаление лишних пробелов
=СЖПРОБЕЛЫ(ячейка)
Эта формула удаляет только лишние пробелы, оставляя нужные. Пригодится, тем, кто набирает большие объемы текста вручную.
На скриншоте можно заметить, что в некоторых местах текста было по 2-3 пробела, которые формула успешно удалила, оставив только один
Читайте также
Работа с графиками
=SPARKLINE(диапазон:диапазон)
Формула позволяет создавать мини-графики прямо в Таблицах. Это пригодится, если вы занимаетесь отслеживанием тех или иных показателей.
Нужно разместить все показатели горизонтально, слева направо, и в последней ячейке сгенерировать формулу
Чтобы поменять внешний вид формулы, нужно указать параметры. Например, здесь мы поменяли цвет на красный:
Можно указать любой другой цвет: indigo, black, grey, blue, purple, pink, yellow, green, orange
Также можно менять тип диаграммы – для этого нужно менять параметр «charttype». Поменяем «line» на «bar»:
А теперь поменяем на «column»:
Получается столбчатая диаграмма
Обо всех возможностях формулы читайте в Справке.
Работа с изображениями
=IMAGE(“ссылка”)
Формула позволяет вставить любую картинку из интернета в ячейку. Для этого не придется сохранять картинку к себе на компьютер:
Здесь мы вставили картинку с Unsplash
С помощью параметров можно менять размер и ориентацию картинки, чтобы она вписывалась в ячейку так, как нужно вам.
Обо всех возможностях этой формулы читайте в этом разделе Справки.
17 фишек Excel, с которыми будет проще запустить рекламу
Как Добавить Формулы и Функции в Электронные Таблицы Google
Электронные таблицы позволяют выполнять много различных вычислений, начиная с простейших и заканчивая сложными функциями, которые преобразуют ваши данные. Google Таблицы не уступают другим приложениям, которые могут творить магию с вашими данными и помогают вам вытаскивать из них саму суть, при помощи функций и формул. К счастью с формулами в Таблицах разобраться не сложно.
Часто термины формула и функция, обозначают одно и тоже, когда речь идет об электронных таблицах. Для простоты, я понимаю под формулой набор простых операторов, например ячейка A2 + ячейка B2.
С другой стороны Функции, трансформируют данные в таблице.Подставьте данные в функцию, уточните опции, и увидите различные результаты в зависимости от того, что у вас было в начале. В целом, функция — это оператор, который принимает данные и выдает измененные данные.
В этом уроке, я собираюсь разобрать формулы в Google Таблицах. Я покажу вам мощь формул и функций, и как вы можете использовать их для решения своих задач.
Что представляет из себя Функция в Google Таблицах?
Прежде чем мы перейдем к деталям, давайте немного поговорим о том, что функции могут сделать с вашими данными и почему они так важны в Таблицах.
Вы можете представить себе функции, как завод, который перерабатывает ваши данные. Вы помещаете данные в функцию, она их перерабатывает, и вы в итоге получаете результат, который отличается от того, что было на входе. Вместо того, чтобы перепечатывать результат снова и снова, позвольте функции делать трансформацию за вас.
Каждый раз, когда вы пропускаете данные через это заводик (функцию), вы получается ожидаемый результат в виде измененных данных.
Приложения электронных таблиц могут обрабатывать простые данные, но гораздо интереснее, когда вы используете данные, чтобы изменить их определенным более сложным образом. Давайте узнаем больше об использовании функций в Google Таблицах.
Как Создавать Формулы и Функции в Google Таблицах
В электронных таблицах, большинство формул и функций начинаются со знака равно (» inline»>=AVERAGE(A2:A5)
Это демонстрирует мощь функций в Google Таблицах. Вместо того, чтобы вручную складывать все значения, затем подсчитывать их количество, и потом делить, мы используем функцию AVERAGE, которая автоматически выполняет действия за нас.
Совет: Используйте Руководство
За что я люблю Таблицы, так это за простоту использования. Нет лучше примера в том, чем то, что вам предоставляется инструкция по использованию функции, когда вы набираете ее имя.
Чтобы увидеть руководство, просто напечатайте название и сделайте паузу. И у вас появится окошко, где будет написано как использовать данную функцию. Иногда этого вполне достаточно, чтобы понять как использовать новую незнакомую функцию.
Эта возможность увидеть синтаксис функции и руководство по ее использованию, одна из важнейших причин, которые делают Таблицы столь популярными. Ну а теперь, давайте разберемся с некоторыми функциями Google Таблиц, с которыми вам возможно будет интересно поработать.
Пять Формул Google Таблиц Которые Стоит Попробовать
Таблицы невероятно мощны, но не всегда известно, какую формулу Google Таблиц, вам нужно использовать для решения проблемы.
Функции Таблиц дают гамму возможностей. Я предлагаю вам взглянуть на несколько моих любимых функций.
1. =TRIM
Когда вы открываете данные внутри электронных таблиц, то они не всегда могут быть аккуратны. В них могут быть пробелы.
В таких случаях, функция TRIM, очень помогает сэкономить время. Она автоматизирует работу по очистке от промежуточных и конечных пробелов. Вместо того чтобы щелкать мышкой и работать с каждой ячейкой, используйте функцию TRIM, чтобы сделать все автоматически.
Чтобы использовать TRIM, просто напечатайте =TRIM, и укажите ячейку, которая нуждается в обработке. Нажмите ввод, и вы быстро очистите данные, что вы можете видеть на примере ниже.
Масштабируйте функцию TRIM растянув ее и запустив автоматизацию в нужных ячейка. Вспоминайте об этой функции Google Таблиц, каждый раз когда вы получаете данные, которые нужно очистить от лишних пробелов.
2. =PROPER
Продолжаем тему приведения данных в порядок. Вы можете получить данные в смешанной формате, например, когда у вас будут и заглавные и строчные буквы. Это работа для функции PROPER, которая избавит вас от мучений и перепечатываний вручную, и автоматически приведет все к нужному виду.
Чтобы использовать функцию PROPER, просто напечатайте =PROPER, и укажите на ячейку в которой нужно обработать данные. Нажмите ввод и первые буквы слов станут заглавными.
Как обычно, вы можете просто растянуть формулу на другие ячейки, чтобы также их обработать.
3. =GOOGLEFINANCE
Для тех кто следить за своим пакетом акции, функция =GOOGLEFINANCE — просто спаситель. Это функция, которая обращается непосредственно к сервису Google Finance, и извлекает от туда информацию о состоянии рынков акций, паевых фондов, рынков облигаций и др.
Функция GOOGLEFINANCE может еще использовать различные данные касающиеся безопасности, но вот здесь мы рассмотрим обычный пример со стоимостью акций компании Apple:
Это загрузит (и будет все время обновлять) данные по цене акции Apple, прямо в вашей электронной таблице. И вы можете это использовать, чтобы отслеживать стоимость вашего портфеля.
Чтобы узнать об использовании функции GOOGLEFINANCE более подробно, смотрите следующий урок:
Google Таблицы
Как Отслеживать Данные на Рынке Акций в Google Таблицах с помощью функции GOOGLEFINANCE
Andrew Childress
4. =GOOGLETRANSLATE
Сервис Google Переводчик — очень полезный инструмент, который позволяет быстро сделать перевод. Но еще лучше, когда вы можете сделать этот перевод прямо в таблице, благодаря функции =GOOGLETRANSLATE.
Допустим, что вам часто приходится загружать данные и систем, где присутствует текст на иностранном языке. Вам нужно их быстро перевести, и каждый раз когда вы загружаете данные, и поместить перевод в таблицу.
Вместо того чтобы постоянно перескакивать из таблицы на страниц Google Переводчика, копировать и вставлять туда-сюда слова, просто используйте функцию =GOOGLETRANSLATE прямо в таблице.
Простейший вариант использования функции — это просто напечатать =GOOGLETRANSLATE(A2), чтобы указать функции, чтобы она перевела текст в ячейке А2. Google, попытается угадать язык и представить перевод на языке, вашего аккаунта.
Также как и GOOGLEFINANCE, функция GOOGLETRANSLATE — отличный пример возможностей оналайновых Google Таблиц.Они используют возможности других сервисов Google, и позволяют обрабатывать с их помощью ваши данные.
5. =RANDBETWEEN
Когда я работаю над созданием упражнений по таблицам, один из моих любимых инструментов — это функция RANDBETWEEN, которая позволяет генерировать случайные числа, которые я потом использую в упражнении.
Чтобы использовать =RANDBETWEEN, откройте скобки и укажите нижний предел для величин и верхний предел. Например для генерации величин в диапазоне от 1 до 100, необходимо использовать = RANDBETWEEN(1,100). Посмотрите пример на скриншоте ниже.
Вы можете использовать такую функцию и для развлечения. Просто откройте новый Лист, и используйте данные для того, чтобы что-то построить, или вы можете сыграть в игру, кто лучше угадает число.
Полный Список Функций Google Таблиц
Я вам показал пять моих любимых функций в Google Таблицах, но мы с вам даже не рассмотрели и малую часть, того что вы можете здесь найти. Функций, с помощью которых вы можете изменить и обработать свои данные, в Google Таблицах гораздо больше.
Один из способов увидеть эти функции это пройти по ссылке на руководство по функциям Google Таблиц. Я насчитал свыше 400 функций Google таблиц, с которыми вы можете работать, и которые облегчат вам понимание ваших данных.
Когда вы работаете с электронной таблицей, может быть удобным увидеть все формулы, которые уже используются, если вы хотите их использовать,например, для другой ячейки. Чтобы их увидеть зайдите в Вид > Показывать формулы, или нажмите на клавиатуре горячие клавиши Ctrl + `, чтобы были отображены все формулы вместо результатов.
Также не забывайте про эту функцию, чтобы проверить формулы в вашей таблице. Одна неправильная формула может испортить все вычисления, поэтому старайтесь периодически проверять корректность ваших формул.
Опять же, Таблицы действительно очень удобны в том, чтоб вы можете узнать все о формуле прямо в приложении. А полный список формул показывает, насколько мощным инструментом являются Таблицы Google.
Повторяем материал и продолжаем обучение
Электронные таблицы — это чудо-лист, который позволяет вам сделать с ним все, начиная от вычислений и заканчивая организацией личной жизни. Если вы пока не знаете для чего вам могут пригодиться электронные таблицы, посмотрите уроки ниже, чтобы узнать лучше о том, как вы можете использовать их в своем следующем проекте.
10 популярных формул в Google Таблицах
В Google Таблицах существует множество формул, которые помогают быстрее и гораздо точнее сделать рутинную работу с данными. Сегодня мы разберем 10 самых популярных и полезных формул.
ПОЛЕЗНЫЕ СОВЕТЫ ОТ НАШИХ РАЗРАБОТЧИКОВ
Сумма
1. Сумма — самая простая, но очень важная и полезная операция. Формула суммы суммирует две и более цифр. Выглядит следующим образом:
сумм(B2;C2;D2)
В нашем примере мы суммируем ячейки B2, C2 и D2.
Счет
2. СЧЁТ Подсчитывает количество ячеек, которые имеют в себе номера. Эта формула подсчитывает только клетки, в которых есть цифры.
Пример: СЧЁТ (B2:B11; C2:C11; D2:D11)
Не хотите разбираться в непонятных формулах?
Запишитесь на консультацию. Мы подберем для вас решение и научим пользоваться
Длина строки
3. ДЛСТР (len) Используется для вычисления длинны строки. Потребуется один параметр — номер нужной строки.
Пример: ДЛСТР(A2)
Лишние пробелы
4. СЖПРОБЕЛЫ Удаляет все лишние пробелы между словами, за исключением единичных пробелов между словами. Такая функция используется для обработки текстов, скопированных из других источников, где в таких текстах содержатся лишние пробелы.
Содержит один параметр — номер нужной строки.
Возвращение строки
Существует три разновидности по типу размещения строки.
5.1. ПСТР Возвращает отрезок строки. Такая функция состоит из трех параметров:
— номер строки, из которой мы берем значения
— начальная позиция, начиная с которой мы берем эти значения
— количество знаков (значений) — длина подстроки
Выглядит это следующим образом: ПСТР(строка; начиная_с; длина_подстроки)
5.2. ЛЕВСИМВ В озвращает первый символ или символы в текстовой строке. Содержит два параметра: номер нужной строки и кол-во символов, которые возвращаем.
ЛЕВСИМВ(строка; число_символов)
Пример: ЛЕВСИМВ(B17 ; 1)
5.3. ПРАВСИМВ В озвращает текст, начиная с конца указанной строки.
Содержит два параметра: номер нужной строки и кол-во символов, которые возвращаем.
ПРАВСИМВ(строка; число_символов)
Пример: ПРАВСИМВ(B17 ; 7)
Не нашли ответа на свой вопрос?
Посмотрите другие наши решения для бизнеса
ВПР(VLOOKUP)
6. ВПР Производит поиск по первому столбцу диапазона и возвращает значение из найденной ячейки.
ВПР(запрос; диапазон; индекс; [отсортировано]) Запрос — это критерий, по которому выполняется поиск.
Диапазон — это диапазон, в первом столбце которого будет производиться поиск по запросу.
Индекс — это номер столбца (от начала диапазона), из которого нужно взять искомое значение.
Отсортировано – по умолчанию [ИСТИНА]. Указывает, отсортированы ли данные в столбце, в котором производится поиск (первом столбце из указанного диапазона). Как правило, рекомендуемое значение – ЛОЖЬ.
Пример: ВПР(24; B2:M10 ;1;ИСТИНА)
Если для параметра отсортировано указать значение ЛОЖЬ (рекомендуется), возвращается только точное совпадение. Если таких совпадений несколько, возвращается значение для первого из них.
Если точных совпадений нет, возвращается ошибка #Н/Д.
Если для параметра отсортировано не выбрано значение или выбрано ИСТИНА, возвращается значение, ближайшее к запрошенному (меньшее либо равное). Если все значения в столбце поиска больше указанного, возвращается ошибка #Н/Д.
ЕСЛИ (IF)
7. ЕСЛИ (IF) Возвращает истину или ложь в зависимости от результата логической проверки. Структура:
ЕСЛИ(логическое_выражение; значение_если_истина; значение_если_ложь)
Задается логическое выражение, например, А1=В1 и если они равны, то функция выдаст значение истина и если не равны, то ложь.
Пример: ЕСЛИ(B2 = В3; «истина»; «ложь»)
Подборка полезных формул для работы с Google Таблицами
Google Sheets — это удобный сервис для работы с данными в уже давно знакомом формате Excel от Microsoft. Чтобы преобразовать информацию из ячеек и проводить вычисления, вам понадобятся примерно те же формулы. Ниже мы подготовили подборку и пошаговые инструкции для выполнения расчетов в Google Таблицах.
Как вводить формулы в Google Таблице
Для начала создайте новую таблицу. В сервисе есть уже готовые шаблоны на разные случаи:
https://www.google.ru/intl/ru/sheets/about/
Выберите подходящий вариант или действуйте следующим образом:
- Перейдите на страницу sheets.google.com.
- Кликните на большой цветной значок «+» с подписью «Blank» и дождитесь загрузки пустой таблицы.
Либо просто перейдите по этой ссылке: sheets.google.com/create.
Далее переходите к оформлению таблицы. Подробная инструкция к форматированию ячеек есть здесь. Добавляйте текст, числа, комментарии, закрасьте ячейки подходящими цветами и не забудьте открыть доступ к документу для коллег. При необходимости импортируйте данные напрямую из таблицы Excel.
Теперь введите формулу. Для этого кликните на нужную ячейку и добавьте в нее знак «=», а также название функции. Функцией называют готовое действие, выраженное, как правило, одним словом. Полный список функций вы найдете здесь. Для того, чтобы вставить в ячейку готовую функцию, кликните «Вставка» → «Функции» и выберите из списка нужное действие.
Помимо функций, формула включает в себя значения ячеек, например, D7.
Google автоматически предложит готовую формулу, исходя из уже имеющихся данных. Обращайте внимание на всплывающие подсказки (следовать им необязательно). Со временем умные алгоритмы будут подбирать для вас более эффективные готовые решения.
Самая простая формула подойдет для вычисления общего значения двух ячеек. Например, =D7+D9. Для повторяющихся вычислений используйте готовые функции. К примеру, =AVERAGE поможет вычислить среднее из всех значений в ячейках:
Маркетинг
Читайте также:
Callday.Estate: как это было