Пользователь
0,0
рейтинг
9 ноября 2012 в 08:27

Разработка → Талмуд по формулам в Google SpreadSheet tutorial

Обычно мы пишем про хостинги, в частности про зарубежный shared хостинг в США. Но чтобы писать, нужно иметь аналитические данные под рукой. Вот как раз тут требуется помощь Google Docs, если файл получится предположительно меньше 400 000 строк.

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

Кратко о главном


Для того чтоб Excel, либо spreadsheet (таблица Google) поняли что написанное — это формула, необходимо поставить знак "=" в строку формул (Рисунок 1).

ok
Рисунок 1
Далее, начинаем писать формулу с клавиатуры либо выделяем мышкой те ячейки, с которыми мы собираемся работать.

Для определения ячейки может быть использовано 2 вида обозначений:
  • буквенно — цифровое (БУКВА = СТОЛБЕЦ; ЦИФРА = СТРОКА) например «А1».
  • стилем R1C1, в системе R1C1 и строки и столбцы обозначаются цифрами.

Адрес ячейки «B3» в такой системе будет выглядеть как R3C2 (R=row=строка, C=column=столбец). Для скриптов, например, используются оба стиля.
Там, где мы напишем "= формула", например, =SUM (A1:A10) и будет выводиться наше значение.
Общий принцип работы формул RC показан на Рисунке 2.

ok
Рисунок 2
Как видно из Рисунка 3, значения ячеек идут относительно той ячейки, в которой будет написана формула со знаком равно. Для сохранения эстетичного вида формул, в них прописаны символы [0], которые можно и не писать: R[0]C[1] = RC[1].

ok
Рисунок 3
Отличие Рисунка 2 от Рисунка 3 в том, что Рисунок 3 — это универсальная формулировка, не привязанная к строкам и столбцам (смотрите на значения строк и столбцов), чего не скажешь о рисунке 2. Но стиль RC в spreadsheet, в основном, используется для написания скриптов javascript.

Типы ссылок (типы адресации)


Для обращения к ячейкам используются ссылки, которые бывают 3-х типов:
  • Относительные ссылки (пример, A1);
  • Абсолютные ссылки (пример, $A$1);
  • Смешанные ссылки (пример, $A1 или A$1, они наполовину относительные, наполовину абсолютные).

Знак $ здесь как раз и указывает на тип ссылки. Различия между разными типами ссылок можно увидеть, если потянуть за маркер автозаполнения активной ячейки или диапазона ячеек, содержащих формулу со ссылками.

Относительные ссылки


Относительная ссылка «запоминает», на каком расстоянии (в строках и столбцах) вы щелкнули ОТНОСИТЕЛЬНО положения ячейки, где поставили "=" (смещение в строках и столбцах). Затем потянуть вниз за маркер автозаполнения, и эта формула скопируется во все ячейки, через которые мы протянули.

Абсолютные ссылки


Как было сказано выше, если потянуть за маркер автозаполнения формулу, содержащую относительные ссылки, Таблица пересчитает их адреса. Если же в формуле присутствуют абсолютные ссылки, их адрес останется неизменным. Проще говоря — абсолютная ссылка всегда указывают на одну и ту же ячейку.
Чтобы сделать относительную ссылку абсолютной, достаточно поставить знак «$» перед буквой столбца и адресом строки, например $A$1. Более быстрый способ — выделить относительную ссылку и нажать один раз клавишу «F4», при этом spreadsheet сам проставит знак «$». Если второй раз нажать «F4», ссылка станет смешанной типа A$1, если третий раз — $A1, если в четвертый раз — ссылка снова станет относительной. И так по кругу.

Смешанные ссылки


Смешанные ссылки являются наполовину абсолютными и наполовину относительными. Знак доллара в них стоит или перед буквой столбца или перед номером строки. Это самый сложный для понимания тип ссылки. Например, в ячейке записана формула «=A$1». Ссылка A$1 относительная по столбцу A и абсолютная по строке 1. Если мы потянем за маркер автозаполнения эту формулу вниз или вверх, то ссылки во всех скопированных формулах будут указывать на ячейку A1, то есть будут вести себя как абсолютные. Однако, если потянем вправо или влево — ссылки ведут себя как относительные, то есть spreadsheet начнет пересчитывать ее адрес. Таким образом, формулы, созданные автозаполнением, будут использовать один и тот же номер строки ($1), но изменится буквенное значение столбца (A, B, C...).

Посмотрим на пример суммирования ячеек с умножением на некий коэффициент.

Данный пример предусматривает наличие значения коэффициента в каждой вычисляемой ячейке (ячейки D8, D9,D10...E8,F8...). (Рисунок 4).
Красные стрелки показывают направление растягивания маркером заполнения формулы, которая находится в ячейки С2. В формуле обратите внимание на изменение ячейки D8. При растягивании вниз меняется лишь число символизирующее строку. При растягивании вправо изменяется лишь столбец.

ok
Рисунок 4
Упростим пример, применив знак $ (Рисунок 5).

ok
Рисунок 5
Но не всегда нужно закреплять все столбцы и строки, иногда используется закрепление только строки или только столбца.(Рисунок 6)

ok
Рисунок 6
Обо всех формулах можно почитать на официальном сайте support.google.com
Важно: Данные, которые необходимо обрабатывать в формулах, не должны находиться в разных документах, это возможно делать только при помощи скриптов.

Ошибки формул


Если вы неправильно напишете формулу, об этом вас известит комментарий о синтаксической ошибке в формуле (Рисунок 7).

ok
Рисунок 7
Хотя ошибки могут быть не только синтаксические, но и, например, математические, такие как деление на 0 (Рисунок 7) и другие (Рисунок 7.1, 7.2, 7.3). Для того чтобы увидеть примечание, в котором показана какая ошибка произошла, наведите курсор на красный треугольник в правом верхнем углу ошибки.

ok
Рисунок 7.1
ok
Рисунок 7.2
ok
Рисунок 7.3
Для удобства восприятия таблицы все ячейки с формулами будем окрашивать в фиолетовый цвет.
Для того чтобы увидеть формулы «в живую» необходимо нажать горячую клавишу Ctrl + или выбрать в меню сверху Вид (Просмотр) > Все формулы. (Рисунок 8).

ok
Рисунок 8

О том, как пишутся формулы


В формулировке формул в справочнике и в формулах, которые используются для работы на данный момент, присутствуют отличия. Они заключаются в том, что вместо «запятой», которая использовалась раньше во многих формулах, уже используется «точка с запятой» (изменения произошли более полугода назад).
Для того чтобы посмотреть, на что ссылается формула на данной странице (Рисунок 9), необходимо щелкнуть мышкой в строке формул справа от надписи Fx (Fx находится под основным меню, слева).

ok
Рисунок 9
ВАЖНО: Для правильного функционирования формул, они должны быть написаны ЛАТИНСКИМИ буквами. Русская (кириллическая) “А” или “С” и латинская “А” или “С” для формулы — это 2 разные буквы.

Формулы


Арифметические формулы.


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

Сложение, вычитание, умножение, деление.


  • Описание: формулы сложения, вычитания, умножения и деления.
  • Вид формулы: “Ячейка_1+Ячейка_2”, “Ячейка_1-Ячейка_2”, “Ячейка_1*Ячейка_2”, “Ячейка_1/Ячейка_2”
  • Сама формула: =E22+F22, =E23-F23, =E24*F24, =E25/F25.

Имеем начальные данные в диапазоне E22:H25, а результат в столбце D. На Рисунке 10 показана шапка, для всех данных, которые будут использоваться.

ok
Рисунок 10

Прогрессия.


  • Описание: формула для увеличения всех последующих ячеек на единицу (нумерация строк и столбцов).
  • Вид формулы: =Предыдущая ячейка + 1.
  • Сама формула: =D26+1

Напомним, если Вы хотите использовать диапазон, он будет суммировать все ячейки подряд, а если Вам нужно просуммировать ячейки в определенном порядке, то их нужно указать через “;” в нужном порядке. Имеем начальные данные для прогрессии в ячейке D26, а результат в ячейках E26:H26 (Рисунок 11) Используется для нумерации строк и столбцов.

ok
Рисунок 11

Округление.


  • Описание: формула для округления числа в ячейке.
  • Вид формулы: =ROUND(ячейка с числом); счетчик (сколько цифр надо округлить после запятой).
  • Сама формула: =ROUND(E28;2).

Имеем начальные данные в ячейке E28, а результат в ячейке D28 (Рисунок 12)

ok
Рисунок 12
Округление “ROUND” происходит по математическим законам, если после запятой стоит цифра 5 или больше, то целая часть увеличивается на единицу, если 4 и меньше, то остается неизменной, также округление можно сделать с помощью меню ФОРМАТ — > Числа -> «1000,12» 2 десятичных знака (Рисунок 13). Если же вам необходимо большее количество знаков, то нужно нажать ФОРМАТ — > Числа -> Персонализированные десятичные -> И указать количество знаков.

ok
Рисунок 13

Сумма, если ячейки идут не последовательно.


Наверное, самая знакомая функция
  • Описание: суммирование чисел, которые находятся в разных ячейках.
  • Вид формулы: =SUM(число_1; число_2;… число_30).
  • Сама формула: "=SUM(E30;H30)" пишем через ";" если разные ячейки.

Имеем начальные данные в ячейках E30 и H30, а результат в ячейке D30
ok
(Рисунок 14).
Сумма, если ячейки идут последовательно.
  • Описание: суммирование чисел, которые идут друг за другом (последовательно).
  • Вид формулы: =SUM(число_1: число_N).
  • Сама формула: =SUM (E31:H31)" пишем через ":" если это непрерывный диапазон.
  • Имеем начальные данные в диапазоне ячеек E31:H31, а результат в ячейке D31 (Рисунок15).

ok
Рисунок 15

Среднее арифметическое.


  • Описание: суммируется диапазон чисел и делится на количество ячеек в диапазоне.
  • Вид формулы: =AVERAGE (ячейка с числом либо число_1; ячейка с числом либо число_2;… ячейка с числом либо число_30).
  • Сама формула: =AVERAGE(E32:H32)

Имеем начальные данные в диапазоне ячеек E32:H32, а результат в ячейке D32 (Рисунок 16).

ok
Рисунок 16
Конечно, есть и другие, но мы идем дальше.

Текстовые формулы.


Из великого количества текстовых формул, с помощью которых можно сделать все, что угодно с текстом, самая востребованная, на мой взгляд — это формула для «склеивания» текстовых значений. Существует несколько вариантов ее исполнения:

Склеивание текстовых значений (формулой).


  • Описание: «склеивание» текстовых значений (вариант А).
  • Вид формулы: =CONCATENATE(ячейка с числом/текстом либо текст_1; ячейка с числом/текстом либо текст_2; …, ячейка с числом/текстом либо текст_30).
  • Сама формула: =CONCATENATE(E36;F36;G36;H36).

Имеем начальные данные в диапазоне ячеек E36:H36, а результат в ячейке D36 (Рисунок 17).
С помощью Google документов часто проводят опросы сотрудников или составляют социологические опросы через Google Forms (это специальные формы, которые можно создать через меню Вставка->Форма. После заполнения формы данные представляются в таблице. А далее, используют различные формулы для работы с данными, например, для склеивания Ф.И.О.).

ok
Рисунок 17

Склеивание числовых значений.


  • Описание: “склеивание” текстовых значений руками, без использования специальных функций (вариант B — ручное написание формулы, сложность формулы любая.).
  • Вид формулы: =ячейка с числом/текстом 1&" "&ячейка с числом/текстом 2&" "&ячейка с числом/текстом 3&" "& ячейка с числом/текстом 4 (" " — пробел, знак & означает склеивание, все текстовые значения пишутся в кавычках “”).
  • Сама формула: =E37&" "&F37&" "&G37&" "&H37.

Имеем начальные данные в диапазоне ячеек E37:H37, а результат в ячейке D36 (Рисунок 18 — склеенные числа).

ok
Рисунок 18

Склеивание числовых и текстовых значений.


  • Описание:«склеивание» текстовых значений руками, без использования специальных функций (вариант С — смешанный тип, сложность формулы любая).
  • Вид формулы: = «текст_1 » &ячейка_1&«текст_2»&ячейка_2&«текст_3»&ячейка_3
  • Важно: весь текст, который будет написан в “” будет неизменным для формулы.
  • Сама формула: =«Еще 1 » &E38&" использования "&F38&" как НАМ "&G38.

Имеем начальные данные “Еще 1”, “использования”, “как НАМ” и в диапазоне ячеек E38:G38, поэтому целесообразно использовать такой вид формулы, а результат в ячейке D36 (Рисунок 19).
Склеиваем текст и числовые значения.

ok
Рисунок 19

ЛОГИЧЕСКИЕ И ПРОЧИЕ


Перенос данных из любых листов одного и того же файла.


Мы подошли к самым интересным, на мой взгляд, функциям: ЛОГИЧЕСКИЕ И ПРОЧИЕ.
Одна из самых нужных формул:
  • Описание: перенос данных из любых листов одного и того же файла (для Excel можно как переносить из листа одной книги в другой лист той же книги, так и из листа одной книги в лист другой книги).
  • Вид формулы: = «Название_Листа»! ячейка_1
  • Сама формула:=Data!A15 (Data — лист, А15 — ячейка на том листе).

Имеем начальные данные на листе Data ячейка А15 (Рисунок 20), а результат на листе Formula в ячейке D41 (Рисунок 20.1).

ok
Рисунок 20
ok
Рисунок 20.1

Массив формул.


Большинство программ для работы с таблицами содержат два типа формул массива: «для нескольких ячеек» и «для одной ячейки».
Таблицы Google разделяют эти типы на две функции: CONTINUE (ПРОДОЛЖИТЬ) и ARRAYFORMULA.
Формулы массива для нескольких ячеек позволяют формуле возвращать несколько значений. Вы можете использовать их, даже не зная этого, просто вводя формулу, возвращающую несколько значений.
Формулы массива «в одной ячейке» позволяют записывать формулы с помощью ввода массива, а не выходных данных. При заключении формулы в состав функции =ARRAYFORMULA можно передать массивы или диапазоны функциям и операторам, которые, как правило, используют только аргументы, не принадлежащие массивам. Данные функции и операторы будут применяться по одному для каждой записи в массиве, и возвращать новый массив со всеми выходными данными.
Если вы хотите изучить вопрос более детально, вам следует посетить support.google.
Говоря простыми словами, для работы с формулами, которые возвращают массивы данных, во избежание синтаксических ошибок, необходимо заключать их в массив формул.

Суммирование ячеек с условием ЕСЛИ.


Для того чтобы оперировать логическими формулами, а они обычно содержат большие массивы данных, их помещают в массив формул ARRAYFORMULA (формула).
  • Описание: суммирование ячеек с условием ЕСЛИ (формула SUMIF).
  • Вид формулы: = SUMIF(‘Лист’! диапазон; критерии; ‘Лист’! суммарный_диапазон)

Для объяснения формулы подробно разберем пример: 3-м покупателям было поручено купить продукты по списку, но оплатить одной суммой. После того, как продукты пробили на кассе, получился список продуктов (Рисунок 21) в столбце А, а их количество в столбце B.
Задача, какой вид будет иметь фискальный чек, после распечатки (попросту нужно сложить продукты 3-х покупателей и узнать кол-во продуктов в сумме по каждой позиции)?

ok
Рисунок 21
Имеем начальные данные в листе Data (Рисунок 21), а результат на листе Formula в столбце D (Рисунок 22). В столбцах E, F, G показаны аргументы, применяемые в формуле, а в столбце H общий вид формулы, которая находится в столбце D и высчитывает результат.

ok
Рисунок 22
Пример выше показывает общий вид работы формулы “Сумма Если” с одним условием, но чаще всего используется “Сумма ЕСЛИ” (с множеством условий).

Суммирование ячеек ЕСЛИ, множество условий.


Продолжаем рассматривать задачу с продуктами на другом уровне.
Вечеринка только начинается, а после звонка друзей, вы начинаете понимать, что спиртного не хватит. И нужно его докупать. Каждый из друзей должен принести с собой горячительный напиток. Необходимо узнать количество бутылок пива, которое нужно принести, и дать задание своим друзьям.
  • Описание: сумма ЕСЛИ (с множеством условий).
  • Вид формулы: = SUMIF(‘Data’! диапазон_1&‘Data’! диапазон_2; критерии_1&критерий_2; ‘Data’! суммарный_диапазон).
  • Сама формула:=(ARRAYFORMULA(SUMIF((Data!E:E&Data!F:F);(B53&C53);Data!G:G)))

Имеем начальные данные на листе Data (Рисунок 23).

ok
Рисунок 23
Допустим, что на листе Formula, в ячейке В53 (критерий_1 = Пиво) должно быть название напитка, а ячейка С53 (критерий_2 = 2), это количество друзей, которые принесут Пиво. В итоге в ячейке D53 окажется результат, что нам нужно докупить 15 бутылок пива. (Рисунок 23.1) то есть, формула определит сумму по двум критериям — пиво и количество друзей.

ok
Рисунок 23.1
Если таких позиций будет больше, строки 16 и 21(Рисунок 24), то количество пузырей в колонке G суммируется (Рисунок 24.1).

ok
Рисунок 24
Итого:

ok
Рисунок 24.1

Теперь приведем более интересный пример:


Ха… вечеринка продолжается, и вы вспоминаете, что нужен торт, но непростой, а супер – мега торт, с разными специями, которые, как назло, еще и зашифрованы под цифровые обозначения. Задача состоит в том, чтобы купить специи в нужном количестве пакетиков каждой из специи. Нужное количество повар зашифровал в таблицу (Рисунок. 25.1), столбцы A и B (в соседних столбцах делаем наши вычисления).
Каждая специя имеет свой порядковый номер: 1,2,3,4. (Рисунок 25).

ok
Рисунок 25
Наша задача посчитать количество повторяющихся значений, в нашем случае, это числа от 1 до 4 в столбце B и определить сколько процентов приходится на каждую из специй.
  • Описание: подсчет количества одинаковых цифр в больших массивах при дополнительных условиях.
  • Вид формулы: СЧИТАТЬ ЕСЛИ(‘Formula’! диапазон_A55: А61+’Formula’! диапазон_B55:B61; УсловиеА”Специи”+УсловиеБ”число от 1 до 4”; Лист”Formula’! диапазон_B55:B61)/УсловиеБ ”число от 1 до 4”)


  • Сама формула: =((ARRAYFORMULA(SUMIF('Formula'!$A$55:$A$61&'Formula'!$B$55:$B$61; $F$55&$E59;'Formula'!$B$55:$B$61)))/$E59)

Имеем начальные данные в диапазоне A55:B61, условие отбора выбираем по ячейке F55 и E59:E62, а результат в диапазоне ячеек F59:F62 (подсчет количества повторов числовых значений при совпадении условий).

  • Описание: вычисление процента специй.
  • Вид формулы: Количество*100%/Общее_количество
  • Сама формула: =F58*$G$56/F$56

ok
Рисунок 25.1
В конечном итоге мы имеем сумму повторов и процент.
Для правильного написания формулы, вы должны полностью представлять, что вы ИМЕЕТЕ, что ХОТИТЕ ПОЛУЧИТЬ и в каком виде. Возможно, для этого вам предстоит изменить вид начальных данных.
Переходим к следующему примеру

Подсчет значений в объединенных ячеек.


Если в формулах используются значения в «объединенных ячейках», то указывается первая ячейка для объединенных данных, в нашем случае это столбец F, а ячейка F65 (Рисунок 26)
  • Описание: формула для подсчета значений, в которых присутствует символ @.
  • Вид формулы: СЧИТАТЬ ЕСЛИ(В столбце F листа “Formula” есть текст с содержимым @).
  • Сама формула: =COUNTIF('Formula'!F65:F68; "*@*").

    ok

Рисунок 26.
И наконец мы добрались до самых ужасных формул.

Подсчитывает количество чисел в списке аргументов.


Существует несколько видов таких подсчетов, они подходят для больших таблиц, в которых нужно считать количество одинаковых слов либо количество чисел. Но при правильном понимании этих формул с ними можно творить такие чудеса как, например: подсчет слов без учета слов исключений. Примеры ниже.
  • Описание: подсчет количества ячеек, содержащих цифры без текстовых переменных.
  • Вид формулы: COUNT(значение_1; значение_2; … значение_30)
  • Сама формула: =COUNT(E45;F45;G45;H45)

Имеем начальные данные в диапазоне ячеек E70:H70, а результат в ячейке D70 (Рисунок 27 — подсчет ячеек, содержащих числовые значения в диапазоне, в котором имеются ячейки с текстом).

ok
Рисунок 27.
Ячейки, содержащие текст и цифры также не считаются.

ok
Рисунок 27.1.

Подсчет количества ячеек содержащих цифры с текстовыми переменными.


  • Описание: подсчет количества ячеек, содержащих цифры с текстовыми переменными.
  • Вид формулы: COUNTA(значение_1; значение_2; … значение_30)
  • Сама формула: =COUNTA(E46:H46)

Имеем начальные данные в диапазоне ячеек E71:H71, а результат в ячейке D71 (Рисунок 28 — подсчет всех значений в диапазоне).

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

ok
Рисунок 28.1

Подстановка значений при условиях.


  • Описание: подстановка значений при условиях.
  • Вид формулы: "=IF(AND((Условие1);(Условие2)); Результат равен 0, если условие 1 и 2 выполняется; если не выполняется, то результат равен 1)"
  • Сама формула: "=IF(AND((F73=5);(H73=5));0;1)"

Имеем начальные данные в ячейках F73 и H73, а результат в ячейке D73 (Если F73=5 и H73 =5 то D73=0 во всех остальных случаях 1) (Рисунок 29).

ok
Рисунок 29.
ok
Рисунок 29.1
Усложним пример.
Посчитать количество ячеек, в которых написаны временные рамки без учета слов «автоответ», «занято», "-".
  • Вид формулы:"=COUNTA(Диапазон_А)-COUNTIF(Диапазон_А; «автоответ»)-COUNTIF(Диапазон_А; "-")-COUNTIF(Диапазон_А; «занято»)"

  • Сама формула: =COUNTA($E74:$H75)-COUNTIF($E74:$H75; «автоответ»)-COUNTIF($E74:$H75; "-")-COUNTIF($E74:$H75; «занято»)

Имеем начальные данные в диапазоне ячеек E74:H75, а результат в ячейке D74(Рисунок 30).

ok
Рисунок 30
Вот мы и подошли к концу нашего маленького ликбеза по формулам в Google SpreadSheet и у меня большие надежды, что я пролил свет на некоторые аспекты аналитической работы с формулами.
Формулы, честно говоря, были в прямом смысле выстраданы. Каждая из них создавалась в течение долгого времени. Надеюсь, вам понравилась моя статья и примеры, приведенные в ней.
И в завершение, в качестве подарка. И да простят меня разработчики!

Формула «УБИЙЦА ДОКУМЕНТА».


Если Вам необходимо скрыть документ от чужих глаз навсегда, то эта формула для Вас.
Сама формула:"=(ARRAYFORMULA(SUMIF($A:$A&$C:$C;$H:$H&F$2; $C:$C)))". $H:$H регулирует распространение формулы. После того как фомлулу запустите (Рисунок 31), ниже в ячейках она начнет размножать следующую функцию CONTINUE(ячейка; строка; столбец).

ok
Рисунок 31
Формула циклически добавляет в весь столбец формулы. Для того чтобы убить документ нужно немножко постараться, создать N-ое количество ячеек и прописать формулу в первых ячейках N-го количества столбцов. Все! Документ больше ни кто исправить и проверить не сможет!
Вот что говорит страница помощи гугла о загруженности и ограничениях — http://support.google.com/drive/bin/answer.py?hl=ru&p=spreadsheets_timeout&answer=2505921
Обещанный документ «Талмуд» по формулам в Google SpreadSheet шел как основа.

До новых встреч, с уважением Антон Пилюганов.
gasyoun @gasyoun
карма
19,0
рейтинг 0,0
Реклама помогает поддерживать и развивать наши сервисы

Подробнее
Реклама

Самое читаемое Разработка

Комментарии (78)

  • +2
    Спасибо за столь подробную статью! Не ожидал, но нашел и для себя новую информацию)
    • 0
      Около 70 часов ее писали, даже 1/5 накопленного опыта не успели изложить, но аудитория как-то вяло реагирует, поэтому видимо ошиблись темой.
      • +6
        Просто люди на работе. А Ваш материал требует основательного изучения, поэтому реакция сообщества будет, будет она, когда население сообщества закончит работу, уйдёт на перерыв или когда появится достаточно времени на подробное изучение статьи. Много кто добавил статью в закладки (я тоже) потому что эта статья (как мне кажется) очень подходит и в качестве справочного материала который будет востребован по мере необходимости.
      • 0
        Не ошиблись. По Google SpreadSheet очень мало такого рода информации (с одной стороны, учебной, а с другой — не только для начинающих). Ждем продолжения.
      • 0
        Я думаю, что о том, полетела ли статья, можно будет судить по количеству людей, которые добавят её в избранное.
        • 0
          329 — пока не густо.
      • +1
        Очень хорошая статья и тема актуальная, но не всем она нужна прямо сейчас. Кроме того, после индексации поисковиками она наверняка привлечет очень неплохой трафик и будет полезна многим. Так что время потрачено не зря.
  • +3
    Спасибо! Ещё бы по Query расписать! Делал для себя систему бюджета домашнего, очень активно пользовался запросами.

    p.s. Не пойму, почему Google не придумали название попроще, язык и пальцы сломаешь пока напишешь SpreadSheets.
    Google Drive
    Google Reader
    Google Calendar,
    хочу Google Tables
    • +1
      Да, я тоже каждый раз сверяю, не упустил ли какую буквы, название отвратное. А что именно по Query, широким мазком не умеем.
      • +1
        По query, хм, ну было бы здорово, если бы Вы описали основные аналоги операторов из MySQL.
        • 0
          Как-то не греет тема, уж больше давнишняя. Нет вменяемого cookbook по теме?
    • 0
      Это калька с русского.
      А широкие таблицы на английском просто и очевидно — spread sheets.
      • 0
        Google Sheets легко запомнить, а spread неудачное слово.
        • +3
          Повторяю — это интуитивное слово для english speaker сапиенсов.
        • +2
          А так будут всякие двусмысленные ассоциации)
      • 0
        Калька калькой, но вроде как не только для внутреннего рынка делалось. Название so so.
  • +1
    Никогда не мог нормально понять и оперировать с A1. При каждой возможности переключаю Excel в режим RC.
    • +8
      Хм. Никто из моих знакомых с RC не работает — только A1. И сам не понимаю RC. Неудобно же читать.
      • +1
        Я умер на таком действии:

        формула=предыдущее+1.

        Когда растягиваешь формулу по ячейкам, получается либо

        =RC[-1]+1 =RC[-1]+1 =RC[-1]+1 =RC[-1]+1 =RC[-1]+1 =RC[-1]+1

        либо

        =A1+1 =B1+1 =C1+1 =D1+1 =E1+1 =F1+1

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

        Это основная причина моей ненависти к экселю до тех пор пока я не обнаружил режим R1C1
  • 0
    спасибо, узнал кое-что новое, чего раньше делал более сложными методами.
  • 0
    Скажите пожалуйста, а есть ли в Гуглотаблицах возможность делать подстрочные и надстрочные символы? Раньше точно не было, за исключением цифорвых значений (0-9), да и то, их приходилось копировать с какой-нибудь html-страницы. Я вот не понимаю логику Google — неужели так сложно добавить поддержку под(над-)сточных символов? Тем более в таблицах! Чтобы писать в заголовке столбца нормальное nλ=840нм а не n(λ=840нм) или вообще nλ=840нм.
    Отсутствие этой функции, наравне с отстутствием возможности сделать отступ у картинки в текстовом редакторе Google (опять-таки, наверное это капец как сложно сделать...), пока отталкивает меня от повсеместного использования их продуктов. Так, время от времени…
    Это был маленький крик души, к качеству статьи вообще не относящийся! Статья вообще супер, если бы у вас была возможность дописать остальные 4/5 накопленного опыта, то это стал бы самый большой талмуд в рунете! И очень полезный.
    Спасибо за ваш труд.
    • 0
      Возможно можно попробовать реализовать через скрипт, не уверен правда, не сталкивались. Но попробовать можем.
  • 0
    ид формулы: "=IF(AND((Условие1);(Условие2)); Результат равен 0, если условие 1 и 2 выполняется; если не выполняется, то результат равен 1)"

    Кажется должно быть наоборот, 1 — если оба условия верны.
    • 0
      в эстетическом плане да Вы правы, должно быть наоборот, но вообще может быть написано что угодно.
      • 0
        Да не что угодно, значения возвращаются конкретные. Там синтаксис такой:
        IF(условие; значение_если_верно; значение_если_не_верно)
        Если значения опущены, возвращается 1 в случае, если условие выполняется, и 0 — если нет.
        условие записывается так:
        AND(условие1; условие2; условиеN)
        OR(условие1; условие2; условиеN)

        Формулу вообще можно без IF написать:
        =AND(C33>0;C34>0;C35>0)*G33
        вернёт либо G33, либо 0 (если не выполнится одно из условий)
        • 0
          Ты наверное путаешь excel и spreedsheed? При убранных значениях просто будет пустая ячейка (это типа будет True при совпадении), а при не совпадении будет FALSE в ячейке. Смысл использовать формулу вообще тогда?
          • 0
            Да нет, не путаю, просто не понял ваше предложение в статье, как, собственно, и первый ваш ответ (конец дня...).
            Теперь дошло. Вы там 0 и 1 намеренно поставили, как результат. А я попутал с автоматическим приведением true и false к 1 и 0 в арифметических операциях.
  • 0
    Описание: перенос данных из любых листов одного и того же файла (для Excel можно как переносить из листа одной книги в другой лист той же книги, так и из листа одной книги в лист другой книги).

    В spreadsheet тоже: support.google.com/drive/bin/answer.py?hl=ru&answer=155183
    • 0
      да согласен, в spreadsheet тоже можно переносить, но Вы забываете про ограничения Количество формул ImportRange: 50 формул перекрестных ссылок в книгах.
      И при любом документе с большим количеством информации, функция импортирования теряет свое первоначальное восхищение. Перенос больших документов производится с помощью скриптов.
      Формулы, предполагающие импорт данных (например, IMPORTRANGE), периодически пересчитываются и повышают уровень сложности, а значит большее время для пересчетов.
      • 0
        Importrange очень полезная функция. Но больше 10 importrange в одном документе начинает тормозить так, что вам даже не захочется открывать этот документ.
        Формула: =importRange(«Код импортируемого документа», «Название Листа!A:Z») (A:Z в данном случае это диапазон с А1 до последнего Z)
  • 0
    Спасибо. Просто отличная статья. Жду когда реализуется что-то типа ВПР (VLOOKUP по-моему в английской версии) и тогда полностью перейду на решение от Google. Пока же приходится частенько возвращаться к дорогому и избыточному Excel
    • +1
      функция давно реализована, однако опять же имеет ограничения по количеству ее применений.
      VLOOKUP(критерий_поиска; массив; индекс; порядок_сортировки)
      • 0
        Спасибо. Попробовал. Конечно, не так удобно реализовано с точки зрения интерфейса, но вполне приемлемо. А не подскажете, какое именно ограничение по количеству?
        Заранее благодарен.
  • 0
    А можно вопрос задать?
    Надо из диапазона ячеек выбрать самую первую не пустую снизу. Вот например:

    image

    Надо вернуть 123. Если удалить 123, должно возвращаться 222, и т.д.
    Как реализовать?
    • 0
      Я вижу много путей, но скажу самый простой (с одной формулой). Пусть все данные у нас в столбце А
      1. В B1 ставим формулу =IF(B2<>"";B2;IF(A1<>"";A1;""))
      2. Растягиваем формулу на весь столбец B (кроме последней ячейки)
      3. Ответ в B1
    • +1
      =INDEX(Лист1!A:A;MAX(IF(NOT(ISBLANK (Лист1!A:A));ROW(Лист1!A:A)))) где Лист1! А: А это лист с нужной колонкой.
      • +4
        Крут. Всегда знал, что настоящие ниндзя excel существуют и находятся среди нас.
        К своей радости из твоей формулы узнал 3 (!) новые функции.
      • 0
        Ага, спасибо за пример. Попробую сделать по аналогии.
        Я немного неточно вопрос задал: мне нужно в данном случае из 123 вычесть 222 (пропустив пустые строки), положив результат справа от 123. Головоломка, однако :)
        • 0
          Если у Вас пустые ячейки идут с определенной последовательностью, например, через одну, то Вы можете написать так =INDEX(Лист1!A:A;MAX(IF(NOT(ISBLANK (Лист1!A:A));ROW(Лист1!A:A)))-2), самая главная часть это -2 в конце, поэксперементируйте).
        • 0
          Для возрастающего списка =LARGE(A1:A18;1)-LARGE(A1:A22;2)
          • 0
            Универсальная формула =ARRAYFORMULA((INDEX(A1:A10;LARGE((A1:A10>0)*ROW(A1:A10);1)))-(INDEX(A1:A10;LARGE((A1:A10>0)*ROW(A1:A10);2)))) где A1:A10 нужный диапазон поиска.
          • 0
            Да, всё получилось, спасибо. Обрамил несколькими условиями и сделал ячейки со сдвигом, кроме первой ячейки диапазона:
            =IF(C29<>"";IFERROR(LARGE(FILTER(C24:C29;C24:C29<>"");1)-LARGE(FILTER(C24:C29;C24:C29<>"");2);"");"")
            =IF(C30<>"";IFERROR(LARGE(FILTER(C24:C30;C24:C30<>"");1)-LARGE(FILTER(C24:C30;C24:C30<>"");2);"");"")
            =IF(C31<>"";IFERROR(LARGE(FILTER(C24:C31;C24:C31<>"");1)-LARGE(FILTER(C24:C31;C24:C31<>"");2);"");"")

            А не подскажете, как можно поставить защиту на ячейки от самого себя? Чтобы случайно не повредить формулу? А то там на диапазон ставится только доступ для других пользователей…
            • 0
              пока знаю только извратный способ — сделать владельцем документа другой свой гугло-акк и защитить диапазон от остальных. Найдете способ лучше — маякните плиз :)
              • 0
                Так и не нашел.
                Согласитесь, кот на клавиатуре бед может больше наделать, чем другой пользователь :)
                • 0
                  соглашусь. В моем случае правда вместо кота выстапает годовалая дочь :)
                  • 0
                    я имея 2-х деток, уже привык к тому, что пропадает иногда все)
                    Мой метод отдельный док с формулами, из которого можно нарыть все что необходимо
                • 0
                  кстати, в гуглодокументах есть фича «файл — посмотреть историю изменений» — что позволяет востановить документ на любую не очень далекую дату. Правда не так удобна она конечно. Как прикрутить свой бекап по нормальному я тоже не нашел :(
                  • 0
                    сами бьемся над проблемой бекапа, пока глухо.
                    Я когда ухожу от компа всегда сворачиваю все документы. Чтоб дете часом не залезло.
                  • 0
                    Да, про историю знаю. Просто найти там что-то бывает проблематично, особенно когда проблема обнаруживается не сразу. Например, работаешь спокойно с документом, а в какой-то ячейке нет результата. Смотришь — формула попорчена. В историю — там работа, работа, работа… а когда удалил формулу непонятно. Может неделю назад, может месяц. По времени её проще заново написать.

                    В нативных документах всегда всё блокировал, кроме того, куда можно вводить данные.
                    • 0
                      поэтому господа, рабочая книга для Вас, а вот сводная для всех) JS макрос на перенос данных. Или ручками и все всегда будет в порядке.
  • 0
    Спасибо за sumif с множеством условий. Я вот обычно новые столбцы завожу, где ставлю большие if-ы, но чувствую что это – не комильфо.
    Из недавнего опыта поделюсь функцией INDIRECT. Если нужно сослаться на другой лист, а название этого листа содержится в ячейке (например А1), то можно написать что-то вроде =INDIRECT("'"&A1&"'!G2"). Это даст значение ячейки G2 на листе с именем, которое указано в A1.
    Спасибо за интересный материал!
  • +1
    Очень круто, спасибо!
    Как раз думал засесть почитать про табличные редакторы — и тут статья в руку
  • 0
    Мне пришлось применять формулы подобного уровня для решения задачи по ведению домашней бухгалтерии в spreadsheets:
    =SUM(IFERROR(FILTER(INDIRECT(B$11&"!$G2:$G500"),INDIRECT(B$11&"!$E2:$E500")=$A13, INDIRECT(B$11&"!$C2:$C500")=$T$5)),"")
    • 0
      Вы бы еще пример визуальный показали) Цены бы ему не было.
      • 0
        вот ссылка с примером на google spreadsheets
        • 0
          • 0
            Пример интересный, но во внутренностях не ковырялся.
  • 0
    Для «суммирования ячеек с условием ЕСЛИ» (то, что на рис. 21) — в данном конкретном случае мне кажется удобнее задействовать query.

    =QUERY(A1:B8;«SELECT A, SUM(B) GROUP BY A»)
    • 0
      есть один минус у данной формулы, ей нужны еще минимум 2 колонки, для самой формулы и для названия помимо строк результатов. И 2, если в диапазоне присутствует пустая строка, то она тоже появляется в результатах. Но спасибо за подсказку.
      • 0
        Это просто «концептуальная рыба».
        Заголовки колонок можно назначить свои собственные.
        От пустых строк можно избавиться соответствующими условиями.
        Но это уже излишние детали, которые скроют суть.
  • 0
    А есть ли в GDocs функция склейки массивов?
    Хотелось бы всунуть в функцию QUERY
    • 0
      Что Вы имеете ввиду под склейкой? И почему использовать именно query?
      Пример забыли указать, вот и возникают вопросы.
      • 0
        Вот пример таблицы
        docs.google.com/spreadsheet/ccc?key=0Ajn0Q1IsENSedGhaUnhEWGxiaGhBZzFqZHEzNHFEYnc&usp=sharing
        Есть Исходные данные (A1:G12), и есть развернутые данные (A19:G28), по которым можно строить отчеты.

        Множество [A19:G28] получается из множества [A1:G12], как объединение [A, B, C, E, F] и [A, B, D, -1*E, F]
        Вопрос: можно ли получить получить напрямую нужное мне множество?

        P.S. С т.з. SQL мне нужен оператор «UNION»
  • 0
    Благодаря любимому форуму planetaexcel, вот ответ на Ваш вопрос, для excel правда.

    Sub t()
    Dim sCon$, rs As Object, cn As Object
    Dim sSQL$
    Set rs = CreateObject(«ADODB.Recordset»)
    Set cn = CreateObject(«ADODB.Connection»)
    Select Case CLng(Split(Application.Version, ".")(0))
    Case Is < 12
    sCon = «Provider=Microsoft.Jet.OLEDB.4.0;Data Source=» & ThisWorkbook.FullName _
    & ";Extended Properties="«Excel 8.0;HDR=Yes;IMEX=1»";"
    Case Is >= 12
    sCon = «Provider=Microsoft.ACE.OLEDB.12.0;Data Source=» & ThisWorkbook.FullName _
    & ";Extended Properties="«Excel 12.0;HDR=Yes;IMEX=1»";"
    End Select ' thnx R Dmitry
    sSQL = «SELECT [N], [Дата], [Откуда], -1, [Вид расхода], [Сумма]» _
    & " FROM [Лист1$A1:F8] WHERE [Откуда]>""""" _
    & " UNION" _
    & " SELECT [N], [Дата], [Куда], 1, [Вид расхода], [Сумма]" _
    & " FROM [Лист1$A1:F8] WHERE [Куда]>""""" _
    & " ORDER BY [N]"
    cn.Open sCon
    Set rs = cn.Execute(sSQL)
    [a30].CopyFromRecordset rs
    End Sub
    • 0
      А есть ли примеры подобного кода на JS для GDocs?
      • 0
        поковырявшись в запросах GD не нашел я что-то там команду union или ее аналог.
        Есть pivot но при написании запроса пока что выдает ошибку, хотя выдавать не должен.
        Возможно плохо смотрел, но пока…
        Может кто помочь?
  • 0
    А можно в одной ячейке объединить строку и какую-то динамическую формулу (т.е. не просто указать ссылку на ячейку с результатом вычисления, а написать формулу прямо в ячейке вместе с текстом)?

    Псевдотекст: [Это сумма SUM(B3:B7) / а тут еще какой-то текст и формула]
    • +1
      Можно, например, с конкатенацией строк:

      ="Это сумма: "&SUM(F5:F9)&" руб."
      • 0
        Да, спасибо, работает.
  • 0
    Есть ли возможность сравнить два массива А и В, и получить те данные из массива А, которые отсутствуют в массиве В?
    • 0
      Да, функция VLOOKUP или ВПР по русски.

      VLOOKUP(критерий_поиска; массив; индекс; порядок_сортировки)
      однако опять же имеет ограничения по количеству ее применений.

      Если не разберетесь, пишите, консультирую.
  • НЛО прилетело и опубликовало эту надпись здесь
  • 0
    Здравствуйте!
    Да конечно можно.
    В екселе это делается так:
    =ДВССЫЛ(СЦЕПИТЬ(ПОДСТАВИТЬ(АДРЕС(1; ЧИСЛСТОЛБ(A1:E1);4);1;"");1)) — выведет данные из ячейки Е1
    Ну а дальше уже делайте с ней что необходимо.
    Для GD вариант будет такой:
    INDIRECT(CONCATENATE(SUBSTITUTE(ADDRESS(1;COLUMNS(A1:Z1);4);1;"");1)) — если в GD например 8 столбцов, то мы получим значение ячейки H1
    Но как выяснилось использовать данную формулу можно только как самостоятельную часть, т.е.
    например формула =SUM(A1;INDIRECT(CONCATENATE(SUBSTITUTE(ADDRESS(1;COLUMNS(A1:Z1);4);1;"");1))) сложит А1+Н1
    но не сработает в случае =SUM(A1:INDIRECT(CONCATENATE(SUBSTITUTE(ADDRESS(1;COLUMNS(A1:Z1);4);1;"");1))) сложения диапазона А1: Н1
    Для того чтоб вернуть ее к жизни нужно нашу большую формулу поместить в массив, т.е. заключить ее в круглые фигурные скобки {}
    =SUM(A1:{INDIRECT(CONCATENATE(SUBSTITUTE(ADDRESS(1;COLUMNS(A1:Z1);4);1;"");1))})
    Удачи!
  • 0
    А расширенный список отличий от Excel будет? Просто за все время работы с Sheets столкнулся только с отсутствием некоторого функционала.
  • 0
    А что именно Вас интересует? Особо если честно не заморачивались, функционал в таблицах понемногу растет, хотя довольно многого не хватает. Но впринципе решить можно все.
    • 0
      Если сможете, подскажите, пожалуйста, ответ.
      Есть набор данных в формате: 2 буквы + 6 чисел. Мне нужно вывести максимальное число из этого набора. Меня, вроде бы, устраивает обычный =SUBSTITUTE() и =MAX(). Но вот подружить это при помощи =ARRAYFORMULA() с наскока не удалось. Задача не так важна, чтобы тратить на нее больше получаса, но узнать решение интересно.
      • 0
        MAX(ARRAYFORMULA(VALUE(MID(A2:A5;3;6))))
        • 0
          спасибо. решил вопрос таким образом:
          ="CL" & MAX(ARRAYFORMULA(VALUE(IF(REGEXMATCH($1:$18, "\d{6}"), REGEXEXTRACT($1:$18, "\d{6}"), "0"))))

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