Как сделать среднее значение в гугл таблицах

Добавил пользователь Alex
Обновлено: 04.10.2024

[adace-ad >Одним из правил хорошего тона в современном мире, помимо умения открывать пиво зажигалкой, считается знание основ Google Sheets. Поэтому давай-ка научимся правильно использовать и читать данные в Гугл таблицах. Неправильно использовать ты и сам сможешь научиться.

Основы

Создание и открытие файла

Как сохранять прогресс?

Совместимость с Excel

Можно откатиться на более раннюю версию

Откроется окно, где вы можете не только выбрать версию, но и увидеть её предварительный просмотр.

[adace-ad >Производит поиск по первому столбцу диапазона и возвращает значение из найденной ячейки. VLOOKUP Имеет четыре аргумента:

Запрос — то, что мы хотим найти в столбце
Диапазон — ячейки, внутри которых мы будем искать
Индекс – номер столбца (от начала диапазона), где нужно найти то, что мы вписали в запрос
Сортировка — логическое значение (истина/ложь). Чтобы вернуть точные совпадения ставьте 0

Как она работает?

Далее в ячейку B2 вставляем формулу: =VLOOKUP(A2;’Данные’!A1:C24;3;0) . Это позволит нам отображать 3 столбец (т.е. выручку) у выбранной нами пиццерии

Неточный поиск (наиболее близкое значение)

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

Полная формула: =VLOOKUP(D6;A6:B10;2;1)

Поиск по нескольким условиям (по двум столбцам)

Теперь пишем формулу в ячейке G2: =ArrayFormula(VLOOKUP(E2&F2;;2;0)) . Первый параметр — значения которые мы ищем, там ссылаемся на выпадающие списки E2 и F2. Далее идёт массив (ArrayFormula), который здесь используется как раз для объединения столбцов. Бэкслеш используется для отделения столбцов.

Функции INDEX и MATCH

Проблема функции ВПР (VLOOKUP), как и его аналога ГПР (HLOOKUP) в том, что они ищут данные по одному столбцу или строке. Но что делать, если нам нужен поиск по нескольким параметрам, т.е. и по строке и по столбцу одновременно? Функция, которая умеет выдавать содержимое ячейки из таблицы по номеру строки и столбца — это функция INDEX , а в качестве её параметров часто используется MATCH (русскоязычный аналог — ПОИСКПОЗ).

Поиск по нескольким параметрам (ВПР 2D)

Откроем наш пример:

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

Полная формула: =INDEX(B4:E12;MATCH(G5;B4:B12;0);MATCH(H5;B4:E4;0))

Теперь все данные подставляются. Как это работает? Первый параметр в функции INDEX — это массив поиска. Мы выбрали всю таблицу (вместе с названием столбцов) — B4:E12. Далее нужно указать еще два аргумента — строку и столбец. В обоих случаях мы используем для этого функцию MATCH

Важно!

Формула = INDEX ( A2:C24 ; MATCH ( F5 ; A2:A24 ; 0 ) ; MATCH ( G5 ; B2:B24 ; 0 ) ) выдаст ошибку по третьему параметру в формуле. Чтобы использовать оба параметра при поиске см. раздел VLOOKUP чуть выше.

Функция FILTER

[adace-ad >Функцией FILTER можно быстро отфильтровать нужные данные с таблицы, чтобы отобразить их в другом листе или таблице. FILTER отображает только те строки или столбцы в диапазоне, которые соответствуют заданным условиям. Так же можно быстро сделать выпадающий список, который при выборе из него элемента отобразит нужные данные.

Фильтр по нужным данным

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

Как это работает? В примере функция FILTER содержит два аргумента (может быть несколько условий, у нас оно одно)

Фильтр через выпадающий список

У нас получится выпадающий список с партнёрами:

Теперь в ячейке H4 вставляем функцию: = FILTER ( D2:F30 ; D2:D30 = H3 ) .

После этого у нас будут фильтроваться данные в зависимости от выбранного элемента в ячейке H3

Фильтр с чекбоксами

Дальше с помощью функции TRANSPOSE (она используется для транспортировки набора данных в другие ячейки) мы спарсим названия столбцов в наш перечень. Полная функция: =TRANSPOSE(‘Лист1’!A1:C1)

Огромное количество примеров функции ФИЛЬТР вы можете открыть в сборнике сайта Contributor (открыть в Goolge таблицах)

[adace-ad >Функция QUERY позволяет сделать выборку нужных строк из таблицы с помощью SQL-запроса и отсортировать их. Сложность в том, что нужно знать синтаксис SQL, но самую базовую выборку делать очень легко. В сети много примеров бездарного использования функции QUERY, мы же сейчас сделаем крутой выпадающий список с фильтрацией данных

Подготовка. Парсим данные. Делаем выпадающий список.

Основная часть. Делаем запросы. Фильтруем данные.

С помощью формулы IF сделаем заготовку для нашего фильтра.

В итоге у нас получится выбор данных при изменении нашего выпадающего списка для каждого значения

А сейчас объединим эти две формулы, чтобы был один полноценный запрос:

Теперь нужно это всё перенести в функцию query, чтобы выпадающий список фильтровал запросы. Для этого нужно удалить часть запроса в query (на скрине как раз выделена эта часть):

Результатом станет то, что при выбора Партнера и пиццерии (можно оставить все), функция query будет показывать нам отфильтрованные данные из основной таблицы:

Откроем наш пример таблицы:

Функция Imortrange имеет всего два аргумента: ссылка на таблицу (ключ) и диапазон , который будем копировать. Ключ — вот эта часть ссылки на таблицу с которой вы хотите скопировать данные. Его можно скопировать из адресной строки:

Это и будет ключом. Дальше идёт диапазон. Тут собственно мы копируем либо всю таблицу, либо её часть. В нашем примере мы копируем данные на соседний лист, но всё это сработает и в случае, если вы будете это делать на совершенно новую/другую таблицу.

Внутренняя ошибка импорта

Если возникает эта ошибка, значит у вас есть некоторая опечатка в формуле, хотя она по всем правилам написана правильно. Как такое получается?

Функция JOIN

[adace-ad >Объединяет значения в массиве данных, добавляя между ними разделитель. Два обязательных аргумента: разделитель и массив (или одно значение). Самый просто пример использования ниже:

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

Где взять еще примеры?

Огромное количество уникальных примеров вы можете найти в канале у Рената Шагабутдинова. Этот человек настоящий сенсей и виртуоз Гугл таблиц, автор трех книг, преподаватель в онлайн-школе Skillbox и человек, который автоматизировал бизнес-процессы в МТС и МИФ. Это действительно уникальная обновляемая (!) коллекция различных скриптов, формул. Обрати внимание, что нужен Телеграм. Ссылки в таблице ведут на канал в телеге, поэтому обзаводись. Открыть примеры в Google Sheets

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

Работа SEO-специалиста предполагает постоянную работу с огромным объемом данных. Те, кто давно уже работает в этой сфере используют специализированный платный софт. Если у вас такого нет, то Гугл таблицы могут стать вашим помощником.

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

Читайте, возможно откроете для себя таблицы с другой стороны.

1. Формула LEN

Эта формула позволяет проверить длину мета тега title или decription. Google обычно отображает в заголовке около 50-60 символов.

Пример использования LEN

2. Формула TRIM

Иногда в метатеге decription есть ненужные пробелы, которые вы можете быстро удалить с помощью формулы TRIM.

Пример использования функции TRIM

3. Формула IF

Формула IF используется для проверки того, является ли данное условие истинным или ложным. Его можно успешно использовать, например, когда вы хотите создать страницы по ключевым словам, но только тогда, когда у них более 200 запросов в месяц.

Пример использования IF

4. Формула IFERROR

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

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

Пример использования формулы IFERROR

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

Обратите внимание, что в условии добавлено INT(B6) — это сделано для того, чтобы гугл таблицы корректно обрабатывали значение количества запросов. INT означает, что мы преобразовываем его в целые числа.

5. Формула ARRAYFORMULA

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

Суть ее в том, что с ее помощью вы можете указать диапазоны ячеек..

Где формула с диапазоном — это, например, функция, которая возвращает нужный результат.

Пример использования ARRAYFORMULA

Обратите внимание, что диапазон указан в виде B2:B6. Мы один раз написали ее, оставшиеся ячейки заполнились автоматически.

6. Формула IMPORTXML

Позволяет быстро и легко извлечь заголовок сайта и его мета-описание.

Примеры работы формулы IMPORTXML: извлекли title, decription, h1

Полезные параметры для извлечения:

7. Формула SPLIT

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

Пример работы формулы SPLIT

8. Формула IMPORTRANGE

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

А в случае, внесения правок файле, который переносите — они автоматически подтянуться и изменятся.

Вот что получаем, когда в другой таблице импортируем данные с помощью формулы.

Результат работы формулы IMPORTRANGE

9. Формула VLOOKUP (ВПР)

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

Суть ее работы проще объяснить на примере.

Допустим у нас есть большая таблица с ключевыми словами и их частотностями. Мы хотим из нее получить частотности для нескольких из них.

Пример и результат работы формулы ВПР (VLOOKUP)

Разберем формулу =VLOOKUP(D2;A2:B8;2;0) и поймем что она сделала.

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

10. Функция для получения доступности страницы.

На десерт покажу как можно получать доступность страницы сайта с использованием редактора скриптов.

Путь, где находится редактор скриптов.

Откройте его и скопируйте туда следующую функцию.

Редактор скриптов, с примером вставленной функции.

Теперь вы можете ее использовать для определения статуса страницы.

Результат работы функции проверки статуса страницы.

Заключение.

Как видите, Google Таблицы — это приложение, с помощью которого вы можете упростить и ускорить свою работу.

Онлайн-сервис Google бесплатный.
Удобно реализована совместная работа — не надо тысячу раз пересылать друг другу файлы.
Изменения сохраняются автоматически.
Есть история версий — при необходимости можно откатить на тот момент, с которого все пошло не так.
Можно настроить автоматический импорт данных из сторонних источников — сервисов аналитики, рекламных кабинетов, кол-трекинга и т. д.

Начало работы с Google Sheets

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

Google Sheets поддерживает таблицы Excel. Открыть такой файл в сервисе можно двумя способами:

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

Панель инструментов Google Sheets

В сервисе Google этот элементы выглядит проще, чем в Excel. Однако, если вы привыкли работать с программой MS Office, понадобиться время, чтобы привыкнуть.

Кратко пройдемся по инструментам на панели. О некоторых из них мы поговорим подробнее позже, в соответствующих разделах.

Отменить и повторить последнее действие, распечатать документ, скопировать форматирование. Последний инструмент удобно использовать, когда нужно применить одинаковое форматирование к нескольким ячейкам. Выберите ячейку, кликните по инструменту на панели, а затем — по ячейке, которую надо отформатировать.
Изменить масштаб. Делает таблицу крупнее или мельче, диапазон — от 50 до 200 %.
Изменить формат данных в ячейках — выбрать денежный или процентный, увеличить и уменьшить количество знаков после запятой в числовом, выбрать другие форматы в выпадающем меню.
Шрифт.
Размер шрифта.
Форматирование текста — начертание, цвет текста и фона.
Форматирование ячеек — заливка цветом, границы, объединение.
Выравнивание текста — по-горизонтали, по-вертикали, настройки переноса и поворота.
Прочие инструменты:

добавить ссылку;
добавить комментарий;
вставить диаграмму;
создать фильтр;
использовать функции.

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

Операции с ячейками, строками и столбцами

Как закрепить строки

Как перемещать элементы таблицы

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

По клику программа создаст копию таблицы в том виде, в котором вам нужно.

Совместный доступ

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

Как удалить и восстановить таблицу

Как редактировать Google Sheets

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

Как защитить данные от редактирования

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

Комментарии и примечания

Форматы данных

Данные — текст и цифры, которые вы вносите в таблицу, могут отображаться в разных форматах. Вариантов представления данных несколько десятков, но все их можно свести к 7 основным:

текст;
число;
процент;
финансы;
валюта;
дата;
время.

Условное форматирование данных

Укажите диапазон.
Задайте условия форматирования.
Настройте формат — цвет и начертание текста, заливку ячейки.

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

Вы ведете контекстную рекламы и выгружаете в таблицы показатели для анализа эффективности. Чтобы вычислить малоэффективные объявления, нужно отыскать строки с CTR менее 1 %. Создайте правило для соответствующего столбца, по которому ячейки со значением меньше единицы будут подсвечиваться цветом.

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

Фильтры и сортировка

Сортировка. Вверху выпадающего списка находятся кнопки для быстрой сортировки листа в прямом или обратном алфавитном порядке по столбцу с выделенной ячейкой. Числовые значения отсортируются от меньшего к большему и наоборот соответственно.

Фильтры скрывают из таблицы данные, которые сейчас не нужны. Это удобно, когда работаешь с большими массивами — посмотреть публикации по отдельной площадке или типу контента в объемном контент-плане, проанализировать данные по достижению одной цели в аналитическом отчете.

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

Проверка данных

В правом верхнем углу ячеек с неверными данными появятся красные метки, при наведении на которые появляется окно с пояснениями.

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

Сводные таблицы

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

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

Мы делаем ставку на омниканальность – занимаемся комплексным продвижением бизнеса в интернете. Подробнее

Для визуализации данных существуют более функциональные и удобные инструменты — Google Data Studio, Power BI и другие. Однако иногда бывает полезно добавить диаграмму или график прямо в таблицу, чтобы наглядно представить данные.

Посмотрим, что еще мы можем сделать с диаграммой:

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

Настроек много, но все они интуитивно понятны.

Созданные в Google Sheets диаграммы можно сохранить как изображение и опубликовать на сайте, встроив в страницу код. Для этого откройте выпадающее меню в правом верхнем углу диаграммы и выберите соответствующие пункты.

Работа с функциями

Все функции вводятся по одному принципу:

Рассмотрим на примере простой функции, которую часто используют SEO-шники и специалисты по контекстной рекламе — ДЛСТР — вычисляет длину строки.

Подробно описывать каждую функцию не будем — их около 400. Скажем лишь, что в с помощью формул здесь можно сделать все тоже самое, что и в Excel. Конечно, там есть формулы, которых нет в сервисе Google, и наоборот, но таких не много. Например, функции GOOGLETRANSLATE, которая переводит текст с одного языка на другой, в Excel нет.

О полезных для интернет-маркетологов функций электронных таблиц MS Office мы уже писали, все они отлично работают в сервисе Google. Принцип работы этих функций тот же, но может отличаться синтаксис. Список всех формул Google Sheets с описанием и синтаксисом есть в справочнике.

Интеграция с другими инструментами Google

Неоспоримое преимущество Google Таблиц для интернет-маркетологов в том, что они могут взаимодействовать и обмениваться данными с другими сервисами Google. Посмотрим на примере двух продуктов — Google Формы и Google Analytics.

Взаимодействие с Google Forms

По клику создается новая таблица с таким же названием, как и у формы.

Интеграция с Google Analytics

С полученными данными можно работать — сортировать, фильтровать, обрабатывать с помощью формул и отображать в сводных таблицах.

Полезные дополнения Google Sheets

Всего сервис поддерживает более сотни горячих клавиш. Вот несколько самых полезных для Windows:

Ctrl + пробел — выделить столбец;
Shift + пробел — выделить строку;
Ctrl + Enter / D / R — заполнить диапазон / вниз / вправо;
Ctrl + K — вставить ссылку;
Home — перейти в начало строки;
Ctrl + Home — перейти в начало листа;
Ctrl + Backspace — перейти к активной ячейке.

Вот мы и рассмотрели ключевые возможности Google Sheets.

Активно пользуетесь Таблицами и знаете хитрости и лайфхаки, которые ускоряют и упрощают работу? Будет здорово, если вы поделитесь ими в комментариях и поможете другим работать эффективнее ;-)

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

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

Сортировка в Google таблицах

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

Справа появляется небольшая менюшка, в которой мы можем создать правило.

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

Поиск и удаление дублей в Google Таблицах

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

В пустую ячейку вводим функцию =(UNIQUE (A2:A1000) и получаем список ячеек с уникальным содержимым:

Данный метод крайне чувствителен в регистру и пробелам!

*Вместо настроить у вас будет синяя кнопка +Бесплатно

Приложение достаточно мощное. Оно может искать дубли в определенном диапазоне либо сравнивать столбцы и сразу совершать с ними какие-либо действия:

Мы остановимся на выделении цветом. Программа на английском языке, но с помощью Google переводчика и пары тестов точно разберетесь.

Итак, выделяем столбец, заходим в Дополнения ↣ Remove Duplicates ↣ Find duplicates or uniques.

Перед нами появляется выбор. Мы можем найти либо дубликаты, либо, наоборот, уникальные ячейки. Выбираем дубликаты и перед нами встает еще один выбор. Если выберем с 1st occurences , то мы найдем дубликаты, ВКЛЮЧАЯ первое их упоминание, грубо говоря, оригинал. Мы выберем просто поиск дубликатов, исключая первое упоминание, так как нам нужно оставить одну ячейку. Жмем нэкст.

Финальный слайд. Тут сильно останавливаться не будем, а сразу выберем Fill with color. Жмем Finish и видим количество найденных дубликатов в выбранном диапазоне.

Дополнение чувствительно к пробелам!

Теперь мы можем отсортировать по цвету наши дубли с помощью первого метода и оценить необходимость удаления.

Условное форматирование в Google таблицах

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

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

Диаграммы в Google таблицах

На сладенькое. Чтобы красиво все преподнести, воспользуйтесь интуитивно понятной функцией Google Таблиц — вставкой диаграммы. Разъяснения тут излишни, просто попробуйте!

Вывод

– Опыт работы более 3х лет.
– Специально разработанные проекты.
– Отлаженные процессы.

Читайте также: