Основы

Введение

Электронные вычислительные таблицы (англ. spreadsheet) — программы, основной задачей которых является организация, обработка и хранение данных в табличной форме. Первые электронные таблицы появились на рубеже 70х и 80х годов. Система Lotus 1-2-3, появившаяся в начале 1983 года, уже имела более-менее всю существенную функциональность современных электронных таблиц.

В настоящее время наиболее широко распространены три системы электронных таблиц: Microsoft Excel, LibreOffice Calc и Google Spreadsheets. Они достаточно сильно совместимы между собой (хотя для сложных документов периодически возникают проблемы).

Все примеры в этом документе приведены для LibreOffice Calc, но также (возможно, с небольшими изменениями) совместимы и с остальными системами электронных таблиц.

Структура таблицы

Таблица состоит из ячеек. У каждой ячейки есть две координаты: столбец задаётся буквами, строка — цифрами. Левая верхняя клетка обозначается A1.

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

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

  • числовые литералы
  • текстовые литералы (выделяются двойными кавычками; двойная кавычка экранируется собой же)
  • ссылки на ячейки
  • диапазоны (прямоугольники) — две ссылки, разделённые двоеточием
  • аппликации функций вида FOO(a;b;c)

Абсолютный и относительный режимы адресации

Сразу отметим, что термины абсолютный режим адресации и относительный режим адресации, вообще говоря, не корректны по отношению к современным системам электронных таблиц. Они появлилсь в системе VisiCalc образца 1979 года, где имели вполне конкретный смысл, но уже в системе Lotus 1-2-3 потеряли свою актуальность. Закрепились они по какой-то случайной причине.

Если воспользоваться действием «скопировать и вставить» по отношению к некоторой ячейке, содержащей формулу, то все ссылки в этой формуле изменятся следующим образом:

  • если ни одна из компонент ссылки не помечена знаком $ (такая ссылка называется относительной), к ссылке прибавляется вектор сдвига ячейки назначения относительно исходной ячейки

  • если обе компоненты ссылки помечены спереди знаком $ (например, так: $A$1; такая ссылка называется абсолютной), то ссылка не менятеся

  • если только одна компонента ссылки помечена знаком $ (например, так: $A1 или A$1; такая ссылка называется смешанной), то к непомеченной компоненте прибавляется проекция на эту компоненту вектора сдвига ячейки

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

Таблица умножения и треугольник Паскаля

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

Таблица умножения делается так:

  • в ячейку A1 ставится 0
  • в ячейки A2 и B1 ставится формула =A1+1
  • формула из ячейки A2 копируется в нужное количество ячеек первого столбца
  • формула из ячейки B1 копируется в нужное количество ячеек первой строчки
  • в ячейку B2 записывается формула =$A2*B$1, которая затем копируется во всю внутреннюю часть таблицы

Треугольник Паскаля делается так:

  • в ячейку A1 ставится 1, которая копируется в нужное количество ячеек первой строчки и первого столбца
  • в ячейку B2 записывается формула =A2+B1, которая копируется во всю внутреннюю часть таблицы

Использование функций

Тривиальные функции типа SUM, COUNT, COUNTA и т.п. мы не будем рассматривать сколько-нибудь подробно (вам почти наверняка их демонстрировали в 7 классе).

Отметим, что слить два текста можно оператором & или функцией CONCATENATE (в русском варианте — СЦЕПИТЬ).

Получить случайное число можно функциями RANDBETWEEN (для целых числе) и RAND (для дробных). В русском варианте они называются СЛУЧМЕЖДУ и СЛЧИС.

Преобразовать пару чисел в ссылку напрямую можно функцией OFFSET (СМЕЩ в русской версии), прибавляющей к указанной ссылке указанный вектор.

Найти указанное значение в прямоугольнике ширины или высоты 1 можно при помощи функции MATCH (ПОИСКПОЗ). Обратите внимание, что для поиска по неупорядоченным данным нужно явно подать 0 на третий вход!

Приведём пример таблицы, получающей случайное значение из заданного массива:

  • ячейки с A1 по A10 заполняются какими-нибудь данными
  • в ячейку B1 записывается =OFFSET($A$1;RANDBETWEEN(0,9);0)

Если заменить 9 на COUNTA($A$1:$A$10)-1, то выбор будет осуществляться из множества произвольного размера, не превышающего 10 (главное, чтобы значения стояли подряд, начиная с ячейки A1).

Этот же метод легко обобщается на множества большего размера.

Случайная последовательность заданной длины

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

  • в ячейки с A1 по A10 записываются произвольные буквы/слова
  • в ячейку B1 записывается =COUNTA($A$1:$A$10)-1
  • в ячейку C2 записывается =OFFSET($A$1;RANDBETWEEN(0,$B$1);0)
  • формула из ячейки C2 копируется в ячейки с C2 по C100
  • в ячейку D2 записывается =C2&D1, затем эта ячейка копируется
  • диапазон с D2 по D100
  • в ячейку B2 записывается произвольное число от 1 до 100
  • в ячейку B3 записывается =OFFSET($D$1;$B$2;0)

Условное выражение

Электронные таблицы поддерживают условное выражение IF(C;A;B) (ЕСЛИ(C;A;B)). Оно работает стандартным образом: его значение равно A, если C истинно, и B — в противном случае. Сложные условия можно составлять при помощи функций AND, OR, NOT (И, ИЛИ, НЕ).

Наиболее стандартный способ подсчёта количества строчек, удовлетворяющих условию, — сделать столбец с формулой вида IF(условие;1;0) и просуммировать его. Можно (но не особо рекомендуется по причине неочевидного синтаксиса лямбда-выражений) воспользоваться функцией COUNTIF.

Аналогичным образом можно производить выборку данных, удовлетворяющих условию, для дальнейшей обработки: IF(условие;ссылка;"") (пустой текст игнорируется большинством агрегатных функций типа SUM, MAX, MIN и прочих).