Обзор данных в Google-таблицах: формулы, запросы, сводные таблицы

HowTo Инструменты

Для стандартных действий в гугл-таблицах предусмотрены специальные команды. Например, подсчитать количество пустых ячеек в таблицы можно с помощью формулы =COUNTBLANK.

Во всей таблице:

countblank

В отдельном столбце или диапазоне:

countblank2

С помощью формулы =COUNTA можно посчитать, наоборот, ячейки, в которых значения есть:

counta

Очень полезны также такие расхожие формулы, как =MAX (максимальное значение), =MIN (минимальное значение), =SUM (сумма), =AVERAGE (среднее арифметическое), =MEDIAN (медиана). Их тоже можно применять ко всей таблице, так и к отдельному диапазону.

Запросы

В Гугл-таблицах есть также такой гибкий и удобный инструмент, как запросы. Их достоинство в том, что их можно делать сложными, комбинировать с их помощью данные под свои потребности и задавать нужные условия. Например, можно вывести список всех значений одной переменной в соответствии со значениями другой переменной. Подберем все значения из колонки A, которым соответствуют такие значения колонки B, которые больше 5:

query1

А теперь добавим сюда еще одно условия: чтобы при этом соответствующие значения в колонке C были больше 6. Собственно, есть только одно такое значение:

query2

Общий синтаксис таков:

=QUERY(ДИАПАЗОН;"select КОЛОНКА where УСЛОВИЕ")

  • Перед формулой всегда ставится знак =
  • Всё, к чему относится формула, заключается в круглые скобки
  • После указания диапазона ставится ;
  • Описание требуемых действий и условий заключается в двойные кавычки

Иными словами, например:

=QUERY(A1:C16;"select A where B > 5 and C > 6")

Попробуем найти значения А, которым соответствуют значения C меньше 5: =QUERY(A1:C16;"select A where C < 5")

query3

Очевидно, что результат неправильный, потому что он учитывает, в числе прочего, пустые ячейки. Поэтому добавим еще одно условие, чтобы исключить пустые ячейки: =QUERY(A1:C16;"select A where C < 5 and C > -1")

query4

Теперь всё правильно.

А теперь найдем все значения B, которым соответствует пустая ячейка в C:

=QUERY(A1:C16;"select B where C is null")

query5

Исключим пустые значения B из результатов: =QUERY(A1:C16;"select B where B > -1 and C is null")

query6

С результатами поиска по запросу также можно проводить различные операции – например, с помощью уже известных формул. Например, мы можем подсчитать, сколько результатов даст уже приводившийся запрос =QUERY(A1:C16;"select A where B > 5 and C > 6"). Для этого «завернём» его в формулу =COUNT:

=COUNT(QUERY(A1:C16;"select A where B > 5 and C > 6"))

countQuery

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

Работа на отдельном листе

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

newSheet

По умолчанию он будет создан следующей вкладкой после той, которая была открыта во время его создания. Но его можно при желании перетащить в любую другую позицию. Теперь мы можем на новом листе повторить все предыдущие операции с нашей таблицей. При этом указание диапазона будет теперь включать название листа, на котором находятся данные, с которыми мы работаем. После этого названия ставится знак !. Например: =COUNTBLANK(Лист8!A1:C16)

ref1

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

rename_upd

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

ref2

Если имя содержит пробелы, восклицательные знаки и т.п., то при ссылке на него его придётся заключать в одинарные кавычки: =MEDIAN('Ещё один пример'!C1:C16)

ref3

Наконец, при работе с запросами достаточно сделать отсылку к нужному листу только при указании диапазона: =QUERY('Ещё один пример'!A1:C16;"select A where C < 5 and C > -1")

ref4

Сводная таблица

Это еще один удобный способ посмотреть на соотношения данных во всей таблице или в отдельно взятом диапазоне. Чтобы это сделать, нужно сначала выделить все данные или нужную область, а потом выбрать меню Data > Отчет в виде сводной таблицы.

pivot1

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

pivot2