8 полезных функций в Google Таблицах, о которых вы могли не знать

Google Sheets — чрезвычайно гибкий и выдающийся инструмент, который объединяет настройку и расчет данных в форме электронных таблиц. Он облачный, поэтому предлагает множество интерактивных функций, автоматизированный сбор данных и даже извлекает данные из сторонних API.

Если вы часто работаете с электронными таблицами, такими как Excel, iWork Numbers, Zoho Sheet или Open Office Calc, вы легко будете использовать Google Sheets. Если вы только начинаете знакомиться с Sheets или изучаете программу Spreadsheet, ниже приведены 8 полезных функций для ваших данных.

Дополнительные советы по работе с Google SpreadSheets можно получить здесь.

1. СЧЁТЕСЛИ и СУММЕСЛИ в Google Таблицах

SUMIF и COUNTIF немного проще, чем функция поиска. Если логическое выражение в CountIF или SumIF истинно, Google Sheets может подсчитать количество случаев или суммировать эквивалентное значение.

Рассматривая пример ниже, вы можете подсчитать количество проданных яблок с помощью следующего расчета:

=СЧЁТЕСЛИ(B2:B10;”Яблоко”)

Он показывает, что: Подсчитайте количество полей со словом Apple в ячейках B2–B10.

Общий вес проданных яблок можно рассчитать с помощью функции СУММЕСЛИ.

=СУММЕСЛИ(B2:B10;”Яблоко”;C2:C10)

Он ищет число от Apple, введенное в столбец B, а затем вычисляет эквивалентные значения суммы ячеек в столбце C.

2. ВПР и ГПР

Следующий пример докажет, что функции поиска Google Sheets являются лучшими. Они позволяют искать по строке слова, а затем считывать значение в эквивалентный столбец или строку. Это действительно полезно, если у вас есть разные наборы данных с одинаковыми объектами в электронной таблице. Например, строки данных о продуктах, персонале или проектах.

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

Вместо этого функция VLOOKUP ищет данные в таблице по вертикали, пока не найдет то, что есть общего у слов, а затем читает по горизонтали, чтобы найти соответствующее значение в соседнем столбце. VLOOKUP означает Vertical Lookup, потому что она ищет слова по вертикали, а затем по горизонтали, чтобы получить значение, в то время как Hlook up означает Horizontal Lookup, потому что она находит слова по горизонтали, а затем выдает значение по вертикали.

Сначала с помощью функции ВПР используйте следующую таблицу и вычисления:

=ВПР(F2;$A$2:$B$6;2;ложь)

В котором, “Ф2” является значением поиска “Яблоко” в ячейке F2. Поскольку мы используем VLOOKUP, “$А$2:$Б$6” расчета указывает Google Таблицам выполнять поиск по таблице данных за январь. “2” указывает на поиск слова «Apple» во втором столбце. “ЛОЖЬ” предположение, что если эквивалентное значение не найдено, мы пропустим его и выполним поиск в другом месте. Наконец, завершим расчет, найдя Apple в таблице данных за январь, и найдем значение 1003 во втором столбце.

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

=ВПР(F2;$C$2:$D$6;2;ложь)-ВПР(F2;$A$2:$B$6;2;ложь)

Второй расчет поиска аналогичен предыдущему, но теперь мы импортируем февральские данные и вычитаем из них январские данные. Итак, запишите тот же расчет поиска для февральских данных, что и для январских, просто подставив “$А$2:$Б$6” для “$C$2:$D$6” . Теперь вычтем февральское количество яблок (785) из январского количества (785) и получим значение -218.

Аналогично, ГПР представляет собой эквивалентную функцию, но читает по горизонтали и смотрит по вертикали.

3. ИМПОРТРАНЖ

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

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

В процессе извлечения данных из разных рабочих листов любые изменения данных на исходном листе автоматически отражаются на целевом листе.

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

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

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

=IMPORTRANGE(“URL”,”Лист1!B3:R11″)

4. ЕСЛИ ОШИБКА

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

Если вы поделитесь такими файлами с членами команды или клиентами, они могут быть недовольны. Кроме того, вам будет сложно избежать ошибок при выполнении работы. Функция ЕСЛИОШИБКА придет вам на помощь.

Поместите формулу в функцию ЕСЛИОШИБКА и укажите, какой текст отображать в случае ошибки. Видео выше демонстрирует использование ЕСЛИОШИБКА в ситуациях, когда вы управляете электронными таблицами для ценообразования продуктов или классификации студентов.

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

=iferror(D4/C4, 0) =iferror(VLOOKUP(A23,$A$13:$G$18,7,false),”Несоответствие идентификаторов”)

5. ФОРМУЛА МАССИВА

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

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

1. Рабочий лист становится медленным из-за отдельных функций в тысячах ячеек.
2. Внесение любых изменений в формулу потребует часов редактирования.
3. Копирование и вставка формул, не являющихся массивами, в указанные ячейки — утомительный процесс.

Предположим, что вы отвечаете за создание документа электронной таблицы с именами учеников, оценками по предметам и общим баллом ученика. Теперь, если вам нужно рассчитать баллы не только для нескольких учеников в классе, но и для всех учеников по всему городу, формула, основанная на обычном операторе `+`, будет отнимать много времени. Вы можете использовать ARRAYFORMULA, как указано ниже.

=ФормулаМассива(B2:B+C2:C+D2:D+E2:E+F2:F)

В этой формуле диапазон B2:B указывает бесконечность. Вы можете сделать его конечным, отредактировав диапазон до B2:B10 и т. д.

6. ФИЛЬТР

Хотя вы можете легко фильтровать и сортировать данные, перейдя в Данные > Создать фильтр, проще просто использовать функцию ФИЛЬТР, чтобы получить желаемые результаты. Параметры очень просты:

=ФИЛЬТР(фрагмент vi, имя_объекта1, имя_объекта отсутствует)

Раздел «другие условия» необязателен. По сути, это сравнения «истина/ложь» других ячеек для дальнейшей фильтрации результатов.

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

2. Введите диапазон и первое условие. Вы можете закрыть формулу здесь или ввести дополнительные условия, разделенные запятыми. В примере условием является выбор только значений в диапазоне F2:F14 выше 120 000 долларов.

3. Ваши результаты появятся под новым заголовком столбца (если он у вас есть).

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

7. ПРИСОЕДИНЯЙТЕСЬ

Функция JOIN используется для углубления строки значений в текст для удобства использования. Или просто синтезировать несколько ключевых значений или немного HTML.

Введите &, чтобы соединить значения разных ячеек, и используйте кавычки для любого текста, который вы хотите вставить.

Например, мы используем следующий расчет:

=A1 & ” ” & B1 & ” и ” & A2 & ” ” & B2

и результат «4 яблока и 5 груш»

Использование функции JOIN наиболее целесообразно при объединении нескольких значений. Вам просто нужно указать символ, который вы хотите добавить между значениями, и нужными вам значениями ячеек.

Например:

=СОЕДИНИТЬ(“,”,A1:A5)

У нас есть:

1,2,3,4,5

8. РАЗДЕЛЕНИЕ

У вас обратная проблема и вам нужно разделить элементы в ячейке? Просто используйте функцию SPLIT. Это противоположность функции JOIN. Например, вы можете захотеть разделить имена и фамилии, чтобы упростить сортировку данных в алфавитном порядке по фамилии.

Для SPLIT параметры следующие:

SPLIT(разбить_бан, разбить_фантаст, (разбить_тео_мей), (разбить_бант_на_банд))

Проще говоря, 'текст' это ячейка, которую вы хотите разделить, разделитель — символ, используемый для указания места разделения текста, а последние два параметра являются необязательными. разделить_по_каждому относится к тому факту, что вы хотите разделить по каждому совпадающему символу. удалить_пустой_текст удаляет пустой текст из результатов. По умолчанию установлено значение TRUE.

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

1. В первой пустой ячейке начните формулу с =РАЗДЕЛИТЬ(

2. Введите ячейку, которую вы хотите разделить.

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

4. Закройте формулу и заполните несколько ячеек в одной строке.

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

Джессика Таннер

Обновление 15 июля 2024 г.