Google Docs – замечательный сервис, обеспечивающий совместную работу с документами. Несмотря на то, что это веб-сервис, он обладает довольно серьезным функционалом, которым славятся пакеты типа Microsoft Office или Openoffice. Например, возможностью использования формул и ссылок на ячейки в таблицах.
К тому же, Google Docs обладает еще одной возможностью, недоступной (в таком виде, как это реализовано в Google SpreadSheet) для стационарных офисных пакетов – работа с формами. Мы можем создать форму, данные из которой будут храниться в таблице и обрабатываться с учетом возможностей работы с обычными ячейками в таблице. Другими словами, данные из форм мы можем обрабатывать с помощью формул.
Но сам механизм реализации записи форм ставит некоторый барьер к этому. Дело в том, что каждый новый результат форм представляет собой строку таблицы, которая автоматически вставляется после предыдущей (строки данных формы). Ключевое слово – "вставляется". Т.е. в глазах табличного процессора происходит операция вставки строки в таблицу, а это значит, что ссылки в производных ячейках изменятся (хотя, будут ссылаться на те же данные) и никогда не будут ссылаться на данные формы. Другими словами, "съедут". Т.о. банальное =form!$B$2 после ввода данных в форму автоматически станет =form!$B$3.
Казалось бы, этот факт ставит крест на возможности обрабатывать данные из форм "авансом". Но есть уловка, позволяющая ссылаться на ячейки независимо от операций удаления/вставки рядов или колонок.
Мы можем использовать функцию =INDEX, чтобы ссылаться на ту или иную ячейку внутри заданного региона. Например, =INDEX(Form!$B$2:$C$102;Row(B2);Column(B2)-1) введенная в ячейку B2 (другого листа, естественно) будет ссылаться на ячейку Form!B3 вне зависимости от удаления/вставки рядов/колонок (внутри обозначенного региона!!!).
В качестве аргументов для =INDEX мы должны указать: регион; порядковый номер ряда в регионе, здесь используем функцию Row для определения этого номера (для заданной ячейки), не забываем вычитать единицу при необходимости; порядковый номер колонки в регионе (для Column те же замечания, что и для Row).
Вуаля, данные теперь обрабатываются в автоматическом режиме.
Для наглядности можно поиграться с примером: