Development

Конспект формул Google Sheets

Leave a comment

Веб-оболонка для роботи з електронними таблицями Google Sheets, що входить до складу безкоштовного пакету програмного забезпечення у межах служби Google Диск, дозволяє обробляти табличні дані за допомогою формул і функцій, що перевершують за можливостями Excel.

Google Sheets function list //support.google.com/docs/table/...

Талмуд по формулам в Google SpreadSheet //habr.com (9 Novt 2012)

Вступ

//

 

Настройка

Базовая настройка десятичного разделителя может указывать запятую, поэтому в общих настройках Google Disk > Settings > General > Language можно установить предпочитаемый язык Preferred language например English/UKl.
Это установит по умолчанию для новых таблиц:
* формат даты DD/MM/YYYY
* формат числа 0.00.

Для существующего табличного документа настройка форматов выполняется File > Settings > General > Locale такой же установкой, например United Kingdom.

Значения

!!! Привычные значения

Число

Числовые литералы могут иметь только десятичное представление.
Другие представления реализуются из строкового представления функцией DECIMAL().

Строка

Строковые значение могут содержать последовательности произвольных кодов произвольной длины. Чаще всего это применяется для представления строк в кодировке Unicode UTF-16.

Строковые литералы помещаются в двойные кавычки "...".

Текст

Строковое значение может содержать текст из нескольких строк (multiple lines), при этом строки должны быть разделены специальным символом:

  • LF - Line Feed перевод строки
    • соответствует коду 10 (0x0A)
    • вводится в Google Sheets сочетанием клавиш Alt+Enter
    • копирование в буфер обмена (clipboard) текста с LF помещает текст в двойные кавычки, и все двойные кавычке в тексте удваивает
  • CR - Carriage Return возврат каретки
    • соответствует коду 13 (0x0D)
    • копирование в буфер обмена (clipboard) текста с CR сохраняет текст без изменений
  • CR LF последовательность используется по умолчанию для разделения строк в Windows  файлах, строковое значение с таким разделением может оказаться вставленным из буфера обмена или загруженным из файла

!!! Бинарные данные

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

Массив /Array

Большим преимуществом формул в Google-таблицах является возможность агрегировать данные в массив и использовать его в формуле как одно значение. Это позволяет многократно уменьшить количество формул на листе и концептуально упрощает их разработку. Главные свойства массива:

  • массив может быть одномерным горизонтальным, одномерным вертикальным или двумерным
  • величина любой из двух размерностей не ограничена
  • произвольный диапазон ячеек любого листа с помощью {} может быть представлен массивом
  • массив, вычисленный в ячейке, отображает свои значения на диапазон ячеек листа с отсчетом от исходной ячейки
    • значения заполняют только пустые ячейки (кроме первичной ячейки)
      • при наложении любого значения из массива на непустую ячейку листа в первичной ячейке возникнет ошибка #REF!

Флажок /Tick box

Флажок является способом представления значения логического типа. Он устанавливается в активном диапазоне ячеек командой меню Вставка/Флажок (Insert/Tick box).

  • ячейка с флажком может содержать одно из двух логических значений FALSE|TRUE, для других значений отображение флажка заменяется отображением значения
  • формат ячейки с флажком следует установить в Number/Automatic

Раскрывающийся список /Drop-down

Значение в ячейке можно выбирать из раскрывающегося списка, для этого к активной ячейке необходимо применить команду меню Вставка/Раскрывающийся список (Insert/Drop-down), при этом откроется панель Правила проверки данных. В панели списку назначается:

  • диапазон ячеек, в котором выпадающий список применяется
  • критерий
    • для критерия "Раскрывающийся список" список значений выбора
    • для критерия "...(из диапазона)" ссылка на диапазон содержащий значения выбора
  • Расширенные настройки
    • Стиль показа
      • Чип
      • Стрелка
      • Обычный текст

Формулы

Принципы записи формул в Google-таблицах наследуются от Microsoft Excel и во многом совпадают.

  • формула начинается со знака =
  • в формуле допускаются литералы, ссылки, именованные диапазоны, операторы и функции
  • числовые литералы
    • к числовым литералам и значениям применяют арифметические операторы +, -, *, /
  • ссылки в формулах используют нотацию A1 с синтаксисом [sheet!]A1[:B2]
    • ссылку на диапазон задает пара ссылок на левую верхнюю и правую нижнюю ячейки
    • ссылка на диапазон на другом листе должна начинаться именем листа перед !, при этом имя должно соответствовать правилам идентификатора
      • если имя листа не соответствует правилам идентификатора (начинается с цифры, содержит пробелы и недопустимые знаки) имя следует заключить в кавычки '...'!
    • именованные диапазоны Named ranges задаются именем
  • массив данных по ссылке определяется фигурными скобками вокруг ссылки {[sheet!]A1[:B2]}
    • последовательность ссылок соединенных символом , комбинирует диапазоны в общий массив горизонтально {[sheet1!]A1[:B2],[sheet2!]C3[:F4]}
      • если число строк в диапазонах не совпадает, возникает ошибка #VALUE!
    • последовательность ссылок соединенных символом ; комбинирует диапазоны в общий массив вертикально {[sheet1!]A1[:B2];[sheet2!]C3[:D8]}
      • если число колонок в диапазонах не совпадает, возникает ошибка #VALUE!
  •  литералы
    • числовые литералы
    • строковые литералы задаются в двойных кавычках "..."
      • символ кавычки в строковом литерале реализуются двумя кавычками подряд "...""..."
  • операторы
    • оператор конкатенации & приводит левое и правое значение к строковому типу для получения строкового результата
    • операторы +, -, *, /, ^ с числовыми и строковыми значениями приводят значения к числовым типам  для получения числового результата
    • операторы +, -, *, /, ^ с датами и числовыми значениями приводят значения к числовым меткам времени для получения даты или метки времени
  • функции используются по имени
    • допустимы встроенные функции
    • именованные функции  Named function
    • функции из проекта таблицы
    • набор параметров функции указывается в скобках (...) после имени, даже если это пустой набор
    • разделителем параметров в наборе служит ;
      • допускается разделитель , если десятичным разделителем установлена .

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

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

Ошибки

Если формула не может быть вычислена, ее значением становится ошибка:

  • #REF! неверная ссылка. Это часто является результатом удаления ячеек или именованных диапазонов, которые использовались в формуле
  • #NAME? сослались на именованный объект, который Google Таблицы не распознают. Обычно это результат неправильного написания имени встроенной функции ил
    #NUM! невозможно выполнить или отобразить результат числовой операции
  • #DIV/0! /
  • #N/A нужное вам значение недоступно. Это распространенная ошибка при использовании функций поиска, таких как ВПР, ГПР, ЗАПРОС или ИНДЕКС и ПОИСКПОЗ. Это сообщение не обязательно означает, что вы допустили ошибку: значение просто недоступно для извлечения
  • #ERROR! Google Таблицы не могут прочитать вашу формулу или даже угадать, что вы пытаетесь сделать. Обычно это указывает на наличие неожиданной опечатки; например, отсутствующий или лишний символ или оператор.
  • #VALUE! ошибка обычно указывает на то, что вы указали тип данных, отличный от требуемого вашей формулой.
Пустое значение формулы

Формула вычисляет значение, которое делает ячейку непустой (даже если значением является пустая строка "") и функция ISBLANK() для такой ячейки возвратит FALSE. Тем не менее получить в формуле пустое значение возможно выражением IFERROR(0/0) или IF(True,), при этом ISBLANK() будет возвращать TRUE, и ячейка будет отображаться пустой, но в отличие от действительно пустой ячейки вывод соседних ячеек на этой "пустой" ячейке будет обрезаться и вывод массива будет приводить к ошибке.

Результирующий массив

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

  • массив размещается в диапазоне ячеек, который соответствует размерности массива с отсчетом от первичной ячейки с формулой
  • значения заполняют ячейки диапазона, которые должны быть пустыми
    • при наложении любого значения массива на непустую ячейку в ячейке формулы возникнет ошибка #REF!

Функції в формулах

Набор функций в Google-таблицах частично совпадает с функциями Excel в английской нотации и существенно расширен.

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

Анализ значения /Info

Функции блока Info //support.google.com/docs/topic/3105471

  • ISBLANK(reference) возвращает True, если ссылка указывает на ячейку без значения; возвращает False, если ячейка содержит любое значение, в т.ч. пробельные символы, если reference ссылается на диапазон
  • TYPE(value) возвращает код типа значения записанного в ячейке или вычисленного с помощью формулы:
    • 1 числовое значение, включая даты
    • 2 строковое значение
    • 4 логическое значение
    • 16 ошибка вычисления формулы
    • 64 значение типа массив
    • 128 все остальные типы, например: встроенные картинки или диаграммы
  • FORMULATEXT(cell) смотри в разделе Lookup

Числовые /Math

Числовые функции относятся к блоку Math //support.google.com/docs/topic/3105474

  • Анализ числового значения
    • ISODD(value) проверяет нечетность числового значения value
    • ISEVEN(value) проверяет четность числового значения value
  • Численные операции
    • QUOTIENT(dividend, divisor) выполняет целочисленное деление dividend на divisor
    • ODD(value) возвращает ближайшее к value целое нечетное число
    • EVEN(value) возвращает ближайшее к value целое четное число
    • CEILING(value, [factor]) округляет value к ближайшему значению с шагом factor
  • Преобразование
    • BASE(value, base, [min_length]) конвертирует числовое value в строковое представление в системе счисления с основанием base
    • DECIMAL(value, base) преобразует строковое представление value числа в системе счисления с основанием base в числовое значение
  • Генераторы
    • SEQUENCE(rows, columns, start, step) возвращает двумерный массив из rows строк по columns колонок в каждой с последовательность значений от start с шагом step
    • RAND() возвращает случайное значение от 0 и меньше 1 с точностью 15 десятичных знаков после запятой
    • RANDBETWEEN(low, high) возвращает случайное целое значение в диапазоне от low по high
    • RANDARRAY(rows, columns) возвращает массив из rows строк по columns колонок случайных значений от 0 и меньше 1

Логические

Логические функции относятся к блоку Logical //support.google.com/docs/topic/3105413

  • IF(condition, true[, false]) возвращает выражение true, если condition равно или приводится к True, иначе возвращает выражение false
    • condition произвольное значение или выражение
    • true произвольное выражение (по умолчанию Blank*)
    • false произвольное выражение (по умолчанию False)
  • IFNA(value, alter) возвращает значение value, если оно не является ошибкой, иначе возвращает выражение alter
  • IFS(condition1, value1, [condition2, value2, …]) возвращает выражение valueN следующее за выражением conditionN со значением True, иначе возвращает ошибку #N/A.
  • SWITCH(expression, case1, value1, [case2, value2, ...] [, default]) возвращает выражение valueN следующее за вариантом caseN, равным expression или default, если равного варианта нет
  • AND(expression1, [expression2, ...]) возвращает результат логического И всех логических выражений в параметрах
  • OR(expression1, [expression2, ...]) возвращает результат логического ИЛИ всех логических выражений в параметрах
  • XOR(expression1, [expression2, ...]) возвращает результат исключающего ИЛИ всех логических выражений в параметрах
  • NOT(expression) возвращает результат логического НЕ expression

Строковые /Text

Строковые функции относятся к блоку TEXT //support.google.com/docs/topic/3105625

Строки в Google Sheets кодируются в Unicode UTF-16. Символы с кодами до 256 используют 1 байт, от 256 до 65536 используют 2 байта.
Символы с кодами от 65536 используют 4 байта и функции могут работать с ними некорректно!

  • LEN(text), LENB(text) возвращают длину строкового text в символах и байтах
  • CHAR(code) преобразует числовой код в символ согласно текущей версии таблицы Unicode
  • CODE(string) возвращает числовой код в таблице Unicode для первого символа в предоставленной строке
  • CLEAN(text) возвращает текст без непечатных символов ASCII
  • TRIM(text) удаляет пробелы в начале текста, конце текста и повторяющиеся пробелы внутри
  • MID(string, at, length) возвращает фрагмент строки string с позиции  at длиной  length символов
    • MIDB(string, at, length) аналогично MID() возвращает фрагмент строки string, но значение позиции  at и длины  length выражаются в байтах
  • SUBSTITUTE(text, search, replace [,occurrence]) возвращает копию text , в которой подстроки search заменены на  replace, но не более occurrence раз

//

  • JOIN(delimiter; reference; [reference2, ...]) объединяет элементы одного или нескольких одномерных массивов, используя разделитель
    • delimiter - The character or string to place between each concatenated value.
      • delimiter may be specified as blank, e.g. JOIN(,{1,2,3}).
        value_or_array1 - The value or values to be appended using delimiter.
    • value_or_array2, ... - [ OPTIONAL ] - Additional value or array to be appended using delimiter.

SPLIT(text; delimiter; [split_by_each]; [remove_empty_text]) делит текст по разделителю и формирует массив

  • delimiter строка, набор разделителей или  многосимвольный разделитель
  • split_by_each логический, по умолчанию TRUE специфицирует delimiter как набор символов, каждый из которых служит символом разделения, FALSE специфицирует многосимвольный разделитель
  • remove_empty логический, по умолчанию TRUE специфицирует удаление из массива пустых значений

    D1 Fx =SPLIT(A1;",";FALSE)
    A B C D E
    1 1,2,,,5,6,***
    2 1 2 5 6 ***

!!!Строковые с регулярными выражениями

REGEXMATCH(text, regular_expression) Whether a piece of text matches a regular expression.

  • text - The text to be tested against the regular expression.
  • regular_expression - The regular expression to test the text against.

REGEXEXTRACT(text, template) возвращает длину строкового текста в символах и байтах соответственно

  • text The input text.
  • template - The first part of text that matches this expression will be returned.

REGEXREPLACE(text, regular_expression, replacement) Replaces part of a text string with a different text string using regular expressions

  • text - The text, a part of which will be replaced.
  • regular_expression - The regular expression. All matching instances in text will be replaced.
  • replacement - The text which will be inserted into the original text.

Навигация /Lookup

//support.google.com/docs/topic/3105472

//

  • INDIRECT(text; [notation]) реализует ссылку заданную строковым значением; возвращает значение по ссылке на ячейку или массив по ссылке на область
    • text строка или ссылка на ячейку со строковым значением или область реализует ссылку заданную строкой в нотации A1 или R1C1
    • notation логический, по умолчанию TRUE специфицирует A1 нотацию, FALSE нотацию R1C1
  • INDEX(reference, [row], [column]) возвращает содержимое ячейки в диапазоне или массиве reference в строке row и столбце column
  • FORMULATEXT(cell) возвращает строку формулы в ячейке (включая = в начале) или ошибку
    • ошибка #N/A возникает если ячейка содержит значение без использования формулы

Функции анализа диапазонов и массивов /Array

//

  • COUNTBLANK(range) возвращает число пустых ячеек в диапазаное
  • COUNTIF(range, criterion),
    COUNTIFS(range, criterion, [range2, criterion2, ...]) подсчитывает число ячеек в диапазоне range удовлетворяющих условиям criterion
  • COUNTUNIQUE(value1, [value2, ...]) подсчитывает число уникальных значений
    • value может представлять значение или диапазон значений
  • SUMIF(range, criterion, [sum_range]) возвращает сумму значений, для которых проверка соответствия критерию была успешна
    • range массив проверяемых значений
    • criterion критерий проверки каждого значения проверяемого массива
      • значение любого типа используется для проверки на равенство
      • строка вида "<=10" и подобные используется как правая часть условия проверки числовых значений
      • строка вида "* Aug 202?" используется как строковый шаблон, если содержит подстановочные символы:
        • * любые символы любой длины ( ~* маскирование подстановочного символа)
        • ? один любой символ ( ~? маскирование подстановочного символа)
    • sum_range ссылка на диапазон (или начальную ячейку диапазона), значения которого будут суммироваться
      • если sum_range не задан, суммироваться будет проверяемое значение из range
  • SUMIFS(range, array1, criterion1, [criteria_range2, criterion2, ...]) возвращает сумму значений, для которых проверяемые значения удовлетворяют критериям (функция аналогична функции SUMIF(), но порядок параметров отличается)
    • range ссылка на диапазон значений для условного суммирования
    • arrayN массив или диапазон с такой же размерностью как range
    • criterionN критерий проверки каждого значения из arrayN (см. SUMIF())
    D3 Fx =SUMIFS(C1:C3,A1:A3,"USE",B1:B3,">30")
    A B C D E
    1 USD 29.5 50
    2 EUR 32.6 40
    3 USD 36.6 30 30
  • +

Функции /Filter

//

  • FILTER(array, condition1, [condition2, ...]) возвращает массив с отфильтрованными строками или колонками в соответствии с массивами условий
      • array массив или диапазон произвольной размерности
      • conditionN одномерные массивы или диапазоны, размерность которых совпадает c  array по одной из размерностей, содержащие только логические значения; значение FALSE в определенной позиции в любом из conditionN исключает строку или колонку этой позиции из array в результате
    D1 Fx =FILTER(A1:B3,C1:C3)
    A B C D E
    1 USD 36.9  USD 36.9
    2 CNH 5.1  EUR 40.3
    3 EUR 40.3

Функции обработки диапазонов и массивов /Array

//

  • TRANSPOSE(range) транспонирует массив (диапазон значений)
    D1 Fx =TRANSPOSE(A1:B3)
    A B C D E
    1 one 1 one two three
    2 two 2 1 2 3
    3 three 3
  • FLATTEN(range1,[range2,...]) объединяет все значения из одного или нескольких диапазонов в один столбец
  • WRAPROWS(range, columns [, fill]) выполняет построчную развертку одномерных данных range на заданное число колонок columns, недостающие ячейки в последней строке заполняются fill, иначе #N/A
    D1 Fx =WRAPROWS(FLATTEN(A1:A4,B1:C2),2)
    A B C D E
    1 0 4 5 1 2
    2 1 6 7 3 4
    3 2 5 6
    4 3 7 8
  • ARRAYFORMULA(array_formula) выступает в роли итератора, что позволяет вычислить массив значений, последовательно применяя формулу ко всем значениям в массиве указанном в аргументе. Формула может использовать массив в качестве аргументов функций не предназначенных для обработки массивов
  • array_formula формула, использующая один или несколько диапазонов ячеек одинакового размера, или функция, которая возвращает результат, превышающий одну ячейку
    D1 Fx =ARRAYFORMULA(A1:A2+B2:B3)
    A B C D E
    1 10 1 12
    2 20 2 23
    3 30 3
  • ARRAY_CONSTRAIN(array, rows, cols) возвращает копию массива array урезанную до rows строк и cols колонок

//

Статистические /Statistical

Функции относятся к блоку Statistical //support.google.com/docs/topic/3105600

  • COUNT(value1, [value2, ...]) Возвращает количество числовых значений в наборе данных.
  • COUNTA(value1, [value2, ...]) Возвращает количество любых значений в наборе данных
  • AVERAGE(value1, [value2, ...]) возвращает среднее числовое значение в наборе данных, игнорируя текст
  • MEDIAN(value1, [value2, ...]) Возвращает медианное значение в числовом наборе данных.
  • MIN(value1, [value2, ...])
  • MAX(value1, [value2, ...])
  • SMALL(data, n) Возвращает n-й наименьший элемент из набора данных
  • LARGE(data, n)
  • COVAR(data_y, data_x) Вычисляет ковариацию набора данных.
  • CORREL(data_y, data_x) Вычисляет r, коэффициент корреляции продукта и момента Пирсона для набора данных.

Проектирование /Engineering

Функции относятся к блоку Engineering //support.google.com/docs/topic/3105395

  • DEC2HEX(number, [digits]) возвращает строковое hex-представление числа number
    • number числовое значение в диапазоне пятибайтового FFFFFFFFFFот -549755813888 по 549755813887
      • строковое значение будет неявно преобразовано к числовому из десятичного
    • digits- устанавливает число hex-символов в результате
      • если заданное число окажется недостаточным, возникает ошибк #NUM!
Битовые операции
  • BITOR(value1, value2)
  • BITAND: The BITAND function returns the bitwise boolean AND of two numbers. Learn more.
  • BITXOR: The BITXOR function is a bitwise XOR (exclusive or) of 2 numbers that returns a bit of “1” if 2 bits are different, and a bit of “0” otherwise.
  • BITLSHIFT: The BITLSHIFT function shifts the bits of the input a certain number of places to the left. Bits on the right are filled with zeroes (0s).
  • BITRSHIFT: The BITRSHIFT function shifts the bits of the input a certain number of places to the right.

Функции Google

Функции относятся к блоку Google //support.google.com/docs/topic/3105411

  • IMAGE(url, [mode], [height], [width]) !!!
  • SPARKLINE(data, [options]) создает в ячейке миниатюрную диаграмму
  • LAMBDA(name, formula_expression) !!! Вы можете создать и вернуть пользовательскую функцию с набором имен и формулой_выражения, которая их использует. Чтобы вычислить формулу_выражения, вы можете вызвать возвращаемую функцию с таким количеством значений, которое объявляет имя
  • MAP(...)
Миниатюрная диаграмма SPARKLINE

Функция SPARKLINE(array, [options]) создает в ячейке миниатюрную диаграмму

  • array ссылка или массив данных диаграммы
    • требования к размерности массива определяет тип диаграммы (см. "charttype" ниже)
    • вертикальный или горизонтальный одномерный массив считается последовательностью значений y, для которой автоматически порядковая шкала x
    • вертикальный или горизонтальный двумерный массив может включать две колонки или две строки, которые образуют пары значений x и y
  • options двумерный массив параметров диаграммы содержит имя параметра в первой колонке и значение во второй колонке; набор параметров определяется типом в параметре "charttype":
    • "line" по умолчанию, выбирает тип линейного графика по одномерному массиву значений (y) или двумерному массиву пар (x,y)

      • "xmin" устанавливает начальное значение горизонтальной шкалы x
      • "xmax" устанавливает конечное значение горизонтальной шкалы x
      • "ymin" устанавливает минимальное значение вертикальной горизонтальной шкалы y
      • "ymax" устанавливает максимальное значение вертикальной горизонтальной шкалы y
      • "color" устанавливает цвет линии
      • "empty" устанавливает способ отображения пустых значений
        • "zero"
        • "ignore"
      • "nan" устанавливает способ отображения нечисловых данных:
        • "convert"
        • "ignore"
      • "rtl" устанавливает обратное направление отсчета значений по x
        • false по умолчанию, значения x откладываются слева направо
        • true значения x откладываются справа налево
      • "linewidth" устанавливает толщину линии
    • "bar" выбирает тип горизонтальной нормированной гистограммы с накоплением и чередованием цветов, значения y берутся по модулю из одномерного массива

      • "max" устанавливает значение нормирования, которое по умолчанию равно сумме всех значений
      • "color1" устанавливает нечетный чередующийся цвет гистограммы
        "color2" устанавливает четный чередующийся цвет гистограммы
      • "empty" (см. "line")
      • "nan" (см. "line")
      • "rtl" (см. "line")
    • "column" выбирает тип вертикальной столбчатой гистограммы, значения y берутся из одномерного массива

       

      • "color" устанавливает основной цвет столбцов
      • "lowcolor" устанавливает цвет наименьшего значения
      • "highcolor" устанавливает цвет наибольшего значения
      • "firstcolor" устанавливает цвет первого значения
      • "lastcolor" устанавливает цвет последнего значения
      • "negcolor" устанавливает цвет для отрицательных значений
      • "empty" (см. "line")
      • "nan" (см. "line")
      • "axis" логическое значение false (по умолчанию) или true устанавливает режим отображения осей
      • "axiscolor" устанавливает цвет осей при отображении
      • "ymin" (см. "line")
      • "ymax" (см. "line")
      • "rtl" (см. "line")
    • "winloss" специальный подтип "column" гистограммы, в которой данные приводятся к трем значениям: -1, 0 и 1; параметры

Установка цвета в параметрах "color", "color1", "color2", "lowcolor",  "highcolor", "firstcolor", "lastcolor", "negcolor" допускает три способа:

  • код цвета 12 бит "#RGB"
  • код цвета 24 бит "#RRGGBB"
  • имя цвета: "black", "gray", "white", "red", "orange", "yellow", "green", "cyan", "blue", "purple", "magenta"

Функции /Web

Функции относятся к блоку Web //support.google.com/docs/topic/9199554

  • ENCODEURL(text) кодирует текст для использования в URL
  • HYPERLINK(url, [label]) создает интерактивную гиперссылку url со значением label строкового типа
  • IMPORTRANGE(url, range) импортирует из файла электронных таблиц по url заданный диапазон range
  • IMPORTDATA(url) импортирует данные по url в формате CSV (разделенные запятыми) или TSV (разделенные табуляцией)
  • IMPORTFEED(url, [query], [headers], [num_items]) импортирует канал RSS или ATOM

Запрос Google Visualization API

В Google Sheets реализованы мощные средства обработки массивов данных с помощью языка запросов Google Visualization API, синтаксис которого имеет очень много сходств с SQL.

QUERY(data, query, [headers]) выполняет запрос Google Visualization API к заданному диапазону данных и выводит результирующий массив данных

  • data диапазон используемый в качестве источника данных
    • данные в диапазоне обрабатываются построчно
    • каждый столбец данных может содержать значения типов:
      • логические
      • числовые
        • числовые в представлении даты и/или времени
      • строковые
    • значения в каждой колонке будут приведены к одному типу
      • если значения в колонке используют смешанные типы, итоговый тип будет определено большинством значений
        • типы значений меньшинства будут считаться нулевыми значениями
  • query строковый запрос на языке запросов Google Visualization API
  • headers числовой, количество заголовочных строк в массиве данных
    • по умолчанию -1, предоставляет функции возможность определить (угадать) число заголовочных строк автоматически

Язык запросов Google Visualization API

//developers.google.com/chart/interactive/docs/querylanguage

Запрос может включать несколько предложение, которые начинаются ключевыми словами SELECT, !!! в любом регистре. !!!

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

  • в качестве имени колонки следует использовать имя колонки в таблице в верхнем регистре
    • использование имен из строки заголовка описано в документации, но все эксперименты приводили к ошибке #VALUE!
  • выражения могут использовать имена всех колонок исходного массива данных
    • колонка за пределами исходного массива включенная в выражение приводит к ошибке #VALUE!
    • выражения могут использовать с числовыми данными арифметические операторы + - * /
    • выражения могут использовать с числовыми данными некоторые скалярные функции
    • конкатенация строковых значение не предусмотрена

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

  • =, !=, <> . Оба оператора сравнения != <> означают не равно.
    • Строки сравниваются по лексикографическому значению.
  • <=, <, >, >=
  • is null или is not null  Сравнение с null выполняется с помощью is null или is not null
  • Вы можете объединить несколько условий, используя логические операторы and , or , и not .Скобки можно использовать для явного определения приоритета.

// строковые

  • contains - совпадение подстроки. целое contains часть верно, если часть находится где-нибудь внутри целого . Пример: where name contains 'John' соответствует «Джон», «Джон Адамс», «Долговязый Джон Сильвер», но не «Джон Адамс».
  • starts with - совпадение префикса. значение starts with префикса , истинно, если префикс стоит в начале значения . Примеры: where dept starts with 'engineering' соответствует «инженерный» и «инженерный менеджеры». where dept starts with 'e' соответствует «engineering», «eng» и «e».
  • ends with - совпадение суффикса. значение ends with суффиксом , истинно, если суффикс находится в конце значения . Пример: where role ends with 'y' соответствует «ковбою», «мальчику» и «у».
  • matches - совпадение (preg) с регулярным выражением. haystack matches игле истинно, если регулярное выражение в игле соответствует стогу сена . Примеры: where country matches '.*ia' соответствует Индии и Нигерии, но не Индиане. Обратите внимание, что это не глобальный поиск, поэтому where country matches 'an' , это не будет соответствовать 'Канада'.
  • like — текстовый поиск, который поддерживает два подстановочных знака: %, который соответствует нулю или более символов любого типа, и _ (подчеркивание), который соответствует любому одному символу. Это похоже на оператор SQL LIKE. Пример: where name like fre%

GROUP BY агрегирует значения строкам, отобранных для результирующего массива, аналогично SQL

  • avg() Возвращает среднее значение всех значений в столбце для группы. number number
  • count() Возвращает количество элементов в указанном столбце для группы. Пустые ячейки не учитываются. Любой тип number
  • max() Возвращает максимальное значение в столбце для группы. Даты сравниваются с более ранними по размеру, string сравниваются по алфавиту с учетом регистра. Любой тип Тот же тип, что и столбец
  • min() Возвращает минимальное значение в столбце для группы. Даты сравниваются с более ранними по размеру, string сравниваются по алфавиту с учетом регистра. Любой тип Тот же тип, что и столбец
  • sum() Возвращает сумму всех значений в столбце для группы. number number

PIVOT преобразует отдельные значения в столбцах в новые столбцы

ORDER BY Сортирует строки по значениям в столбцах

LIMIT ограничивает количество возвращаемых строк заданным значением

OFFSET пропускает заданное количество первых строк.

LABEL Устанавливает метки столбцов

"SELECT A,B,B+C LABEL A 'X', B 'Y',B+C 'Z'"

format Форматирует значения в определенных столбцах, используя заданные шаблоны форматирования.

options Устанавливает дополнительные параметры.

В языке не используется предложение from, т.к. исходный массив данных нет требует выборки и объединения, как это реализуется в SQL.

Обмеження Google Drive

//support.google.com/drive/answer/37603

  • ​Максимум 10 миллионов ячеек или 18 278 столбцов (до столбца ZZZ) для файлов, созданных в Google Таблицах или преобразуемых в этот формат
  • Максимум 10 миллионов ячеек или 18 278 столбцов, если импортировать таблицу из Microsoft Excel. Ограничение одинаково для файлов Excel и CSV
    • При преобразовании документа из Excel в Google Таблицы, из файла в Google Таблицах будут удалены ячейки, содержащие больше 50 000 символов.

Джерела

 Google Sheets QUERY function: a spreadsheet cure-all you have yet to discover //www.ablebits.com
How to Use Curly Brackets to Create Arrays in Google Sheets //infoinspired.com

//

D1 Fx =
A B C D E F
1 10 1 12
2 20 2 23
3 30 3
4
5

//

Leave a Reply