При запуске Gnumeric получаем достаточно стандартный вид приложения электронной таблицы (ЭТ), рис. 2.1.
Документ ЭТ состоит из листов, каждый лист электронной таблицы может иметь переменное число строк и столбцов (см. далее главу "Управление листами"), а количество листов может быть более 256 (как уже упоминалось во "Введении", сведений об ограничении количества листов ЭТ автору обнаружить не удалось).
(рис 2.1) Общий вид окна ЭТ Gnumeric
(рис 2.2) Простой вид диалога открытия файла
Для открытия файла можно использовать пиктограмму "Открыть файл" в верхней панели инструментов (с изображением "папки"), команду главного меню "Файл/Открыть" или комбинацию клавиш <CTRL>+<O> (буква O – от слова "Open"). В результате появится GTK-диалог открытия файла (рис. 2.2). Поскольку подобные диалоги присутствуют во многих кросс-платформенных GTK-приложениях (в частности, в GIMP и в Inkscape), рассмотрим некоторые особенности этого диалога.
Самая левая кнопка в верхней части диалогового окна "отвечает" за ввод или отображение имени открываемого файла. Если она нажата, в диалоге появляется текстовое поле ввода "Расположение:" (см. рис. 2.3), которое даёт возможность сразу ввести полное имя нужного файла.
Также в верхней части диалога расположена строка указания пути к нужному файлу, причём каждому каталогу в этом пути соответствует кнопка. Если первая кнопка в пути – кнопка со стрелкой, это означает, что путь строится относительно "домашних" каталогов (точка монтирования /home в POSIX-системах). Если на эту кнопку со стрелкой нажать, увидим абсолютный путь от начала дерева каталогов ("Файловая система" или точка монтирования /).
(рис 2.3) Диалог открытия файла со строкой ввода имени и просмотром содержимого каталога
Основная часть диалогового окна состоит из двух панелей. Левая панель – "Места" – состоит из трёх секций. Верхняя секция позволяет выбрать для открытия один из документов, с которым недавно работали ("Недавние документы").
Средняя секция показывает стандартный набор каталогов, в которых могут находиться пользовательские файлы. В этот стандартный набор входят домашний каталог пользователя, "Рабочий стол" пользователя, а также начало дерева каталогов ("Файловая система" или точка монтирования /).
В нижнюю секцию пользователь может добавлять свои часто используемые каталоги, выбрав их в правой панели и нажав кнопку "Добавить". Это работает для каталогов, отмеченных курсором (подсветкой), за исключением каталога Desktop ("Рабочий стол").
Важно заметить, что для перехода в новое "место" (в левой панели диалога) требуется одиночный щелчок левой кнопкой мыши, а для открытия каталога в правой панели диалога нужен двойной щелчок левой кнопки мыши.
По мере продвижения "вглубь" иерархии каталогов файловой системы в верхней части диалога увеличивается количество кнопок, показывающих путь к открываемому файлу.
(рис 2.4) Диалог открытия файла с дополнительными возможностями выбора типа файла и кодировки
Нажатие на кнопку "Advanced" ("Расширенный") открывает ещё две настройки в этом диалоге (рис. 2.4) – возможность выбора типа файла и возможность выбора кодовой страницы (кодировки) для текстовых файлов.
Пусть в выбранном каталоге находится текстовый файл формата CSV (Comma Separated Values – текст, разделённый запятыми). Тогда после выбора соответствующего типа и кодовой страницы и нажатия на кнопку "Открыть" файл окажется импортированным в лист ЭТ, причём Gnumeric правильно распознает текст и числа (рис. 2.5). Дело в том, что ЭТ автоматически выравнивает текст по левому краю ячеек, а числа – по правому. Более подробно форматирование данных в ячейках будет рассматриваться в главе "Управление ячейками".
Для сохранения результатов работы используются кнопка с пиктограммой "диска" или "дискеты" ("Сохранить текущую книгу"), либо команда главного меню "Файл/Сохранить", либо комбинация клавиш <CTRL>+<S> (буква S – от "Save"). Для изменения имени (включая расположение) или типа файла используется команда "Файл/Сохранить как..." или комбинация клавиш <SHIFT>+<CTRL>+<S>. При любом варианте вызова команды сохранения в первый раз открывается диалог сохранения файла (рис. 2.6). При последующих сохранениях он не открывается, если не выбрана команда "Сохранить как...".
О не сохранённых изменениях в файле свидетельствует символ "звёздочка" (*) в строке заголовка окна перед именем файла (в нашем примере получилось бы *d1.csv).
Щелчок левой кнопкой мыши по треугольничку слева от надписи "Просмотреть другие папки" переключает полный и краткий варианты диалога сохранения файла. В кратком варианте можно задать или изменить имя и тип файла, а в полном – ещё и выбрать каталог для сохранения.
(рис 2.5) Результат импорта текстового файла
(рис 2.6) Диалог сохранения файла (полный вариант)
Импорт файлов (открытие, загрузка) возможен из следующих известных форматов:
Экспорт файлов (сохранение, выгрузка) возможен в следующие распространённые форматы:
При экспорте в текстовый файл также возможно указание кодировки выходного файла, а также символа-разделителя и варианта окончания строки (в стиле UNIX, MacOS или Windows).
При экспорте в PDF экспортируются все листы с колонтитулами, форматированием и диаграммами (если они есть). Таким образом, экспорт в PDF равносилен печати всего документа в файл.
Для изменения названий (имён) листов ЭТ можно использовать контекстное меню (рис. 2.7), вызываемое щелчком правой кнопкой мыши по "ярлычку" листа. Выбор пункта "Управление листами..." вызывает диалог управления свойствами листов (рис. 2.8).
В принципе, все операции с листами, которые можно делать с помощью контекстного меню, выполняются в этом диалоге. Лист выбирается щелчком левой кнопкой мыши по соответствующей строчке в списке листов. Для изменения имени листа щёлкаем левой кнопкой мыши в столбце "Новое название" для выбранного листа, пишем нужный текст и нажимаем кнопку "Apply Name Changes" ("Изменить имя"). После этой операции новое имя листа переместится в столбец "Текущее название". Для изменения порядка следования листов перемещаем выбранный лист вверх или вниз по списку соответствующими кнопками справа. Кнопка "Insert" ("Вставить") вставляет новый лист перед выбранным, а кнопка "Append" ("Добавить" или "Присоединить") вставляет новый лист после последнего имеющегося. Кнопка "Удалить" позволяет удалить выбранный лист с возможностью восстановления случайно удалённого.
(рис 2.7) Контекстное меню листа ЭТ
(рис 2.8) Диалог управления листами в Gnumeric
Для изменения цвета "ярлычка" листа и названия листа служат кнопки "Цвет заливки" и "Цвет символов". Нажатие на часть кнопки со "стрелочкой" справа от пиктограммы открывает палитру цветов (рис. 2.9), из которой можно выбрать цвет из типового набора. Клеточки в нижнем ряду заполняются "пользовательскими" цветами. Для установки цвета, не входящего в типовой набор (пользовательского) нужно щёлкнуть по кнопке "Другой цвет..." в нижней части палитры и выбрать цвет с помощью GTK-диалога выбора цвета (рис. 2.10).
(рис 2.9) Типовой набор цветов в Gnumeric
(рис 2.10) GTK-диалог выбора цвета
GTK-диалог выбора цвета предоставляет несколько возможностей формирования цвета объекта. Во-первых, можно установить точное значение цвета в координатах HSV (Hue-Saturation-Value или "Тон-Насыщенность-Яркость", диапазон изменения значений компонента "Тон" от 0 до 360, остальных – от 0 до 100) или RGB (Red-Green-Blue или "Красный-Зелёный-Синий", диапазон изменения компонентов от 0 до 255). Во-вторых, можно установить HTML-эквивалент цвета в шестнадцатеричном выражении (поле "Наименование цвета"). В-третьих, цвет можно выбрать "на глаз" (визуально), вращая цветной треугольник "протягиванием" чёрного отрезка по цветному кольцу, а затем "перетаскивая" белый кружок внутри треугольника. Наконец, кнопка с изображением пипетки под цветным кольцом даёт возможность выбрать цвет произвольной точки экрана. При любом способе выбора все варианты "цветовых координат" изменяются согласованно, а под цветным кругом показываются образцы текущего и нового цвета объекта.
(рис 2.11) Результат управления листами
При изменении цвета "ярлычка" листа ЭТ разумно также изменить цвет названия листа, чтобы фон и текст были достаточно контрастны.
На рис. 2.11 показаны результаты изменения цвета фона, имени и порядка следования листов ЭТ.
Менять порядок листов можно простым "перетаскиванием" ярлычка, при этом появится вертикальная стрелка, указывающая на новую позицию перемещаемого листа.
Листы ЭТ в Gnumeric имеют дополнительные атрибуты защиты, видимости и направления (в диалоге управления листами на рис. 2.8 – столбцы слева от имени листа). Изменяются эти атрибуты простым щелчком левой кнопкой мыши в соответствующем столбце диалога.
Установка атрибута защиты предотвращает изменение данных в ячейках листа, снятие атрибута видимости позволяет скрыть лист в окне ЭТ и убрать его "ярлычок" (при этом в диалоге управления листами отображаются все листы). Изменение атрибута "Направление" меняет порядок столбцов таблицы с направления "слева направо" на направление "справа налево" (первый столбец оказывается справа), что может быть полезным при использовании соответствующих систем письменности.
Дополнительные возможности управления внешним видом листов ЭТ можно получить с помощью вложенного меню "Формат/Лист" через пункт "Формат" главного меню (рис. 2.12).
Назначение режимов, которые включаются и отключаются в этом вложенном меню, достаточно очевидно. Нужно только заметить, что режим "Использовать нотацию R1C1" приводит к изменению порядка формирования адреса. Если в стандартном варианте сначала указывается имя столбца (например, A), а потом номер строки (например, 3) и получается адрес типа A3, то в режиме адресации R1C1 сначала указывается номер строки (Row), а затем – номер столбца (Column) и тот же адрес будет выглядеть как R3C1.
Вернёмся к диалогу управления листами (рис. 2.8) и включим режим показа дополнительных свойств ("Показать дополнительный свойства листа"). В поле свойств листа диалога появятся новые столбцы "Напр.", "Строки" и "Столбцы" (рис. 2.13).
(рис 2.12) Вложенное меню "Формат/Лист"
(рис 2.13) Показ дополнительных свойств в диалоге управления листами ЭТ
Дело в том, что Gnumeric позволяет изменять максимальные значения количества строк и столбцов для листов ЭТ, в том числе и отдельно для каждого листа. Для этого используется вызов диалога "Изменить размер..." из контекстного меню листа ЭТ (рис. 2.14).
Перемещая ползунки, можно изменять количество строк и столбцов на листах ЭТ. При выключенном режиме "Применить изменение ко всем листам" новые настройки будут применены только к выбранному листу (выбирается тот лист, на ярлычке которого было вызвано контекстное меню).
Минимальное количество строк и столбцов, как видно из рис. 2.14 – 128, максимальное количество столбцов на листе – 16386, а строк – более 16 миллионов. Установим для одного из листов значения по минимуму, для другого – по максимуму и посмотрим на результат в диалоге управления листами (рис. 2.15).
(рис 2.14) Диалог изменения количествастрок и столбцов листа ЭТ
(рис 2.15) Листы с различным количеством строк и столбцов в Gnumeric
Для адресации ячеек может использоваться как нотация "A1", так и нотация "R1C1". Адрес текущей (активной) ячейки отображается над левым верхним углом таблицы.
Адреса ячеек используются как операнды в формулах. Использование в формулах конкретных значений является нежелательным (за редким исключением).
В ячейках могут содержаться числа, текстовые данные или календарные даты. Числа и даты автоматически выравниваются по правой границе столбца, а текст – по левой. Таким образом, можно всегда распознать ошибку ввода.
Для чисел десятичным разделителем является точка. Даты в "европейском" варианте лучше вводить с использованием символа "/" (например, 12/05/2007).
Если необходимо, чтобы числовые данные интерпретировались как текст (например, почтовые индексы или ИНН), то начинать ввод следует с символа "апостроф" – "'". Например, чтобы ввести почтовый индекс 143921, нужно набрать "'143921".
Ввод данных в ячейки листа таблицы осуществляется путем набора символов (букв) и цифр с последующим нажатием <ENTER>. При этом указатель активной ячейки ("рамка") смещается вниз на одну строку. Для перехода от ячейки к ячейке в любом "направлении" по листу ЭТ можно использовать клавиши управления курсором, а также щелчок левой кнопкой мыши по целевой ячейке. Переход на другую ячейку автоматически означает окончание ввода данных в текущей ячейке.
Курсор в ЭТ Gnumeric может быть четырёх видов. Варианты курсора и ситуации, при которых они используются, описаны в таблице ниже.
| Вид курсора | Ситуация, назначение |
|---|---|
 |
Стандартный курсор (режим позиционирования). В таком режиме происходит указание ячеек (щелчком левой кнопкой мыши) или выделение диапазона (блока) ячеек (протаскиванием мыши с нажатой левой кнопкой). |
 |
Курсор перемещения (при подведении курсора к рамке активной ячейки или выделенного диапазона). В таком режиме протаскивание мыши перемещает выбранный объект по листу ЭТ. |
 |
Курсор заполнения (может иметь вид "косого" белого крестика), появляется при позиционировании мыши в нижнем правом углу активной ячейки или выделенного диапазона (блока). При выделении диапазона ячеек в таком режиме происходит копирование содержимого активной ячейки (данных или формулы) на выделенный диапазон. |
 |
Курсор редактирования. Появляется при переходе в режим редактирования содержимого ячейки. |
(рис 2.16) Контекстное меню ячейки ЭТ
Для редактирования содержимого ячейки следует нажать клавишу <F2> или сделать двойной щелчок мышки по ячейке, после чего редактируемая ячейка изменяет цвет фона. При редактировании возможны стандартные операции редактирования текста, а также изменение начертания и/или цвета отдельных символов текста в ячейке (при этом символы нужно выделять в строке ввода, а не в ячейке, см. пример рис. 2.22). Завершается редактирование нажатием клавиши <ENTER> или щелчком мыши в какой-либо другой ячейке. Для отмены изменений и возврата к предыдущему состоянию следует нажать клавишу <ESC>.
Управление представлением данных в ячейках осуществляется настройками форматов ячеек ("Формат/Ячейки..." в главном меню или вызов диалога "Изменить формат 1 ячейки..." через контекстное меню ячейки (рис. 2.16) с помощью правой кнопки мыши).
Изменения формата касаются активной ячейки или выделенного блока ячеек (включая строки и столбцы полностью). Диалог "Формат ячеек" (рис. 2.17) имеет несколько вкладок, обеспечивающих различные настройки параметров ячейки или диапазона ячеек.
(рис 2.17) Настройка представления чисел вячейках ЭТ
(рис 2.18) Настройка проверки правильности ввода данных в ячейку ЭТ
Возможность проверки правильности данных при вводе – очень полезная возможность. Пусть, например, в некотором диапазоне ячеек необходим ввод только целых чисел, причем пустая ячейка (отсутствие данных) не является ошибкой. С помощью настройки параметров проверки ("Формат ячеек: Проверка") обеспечим блокировку ошибочного ввода и появление предупреждения об ошибке (рис. 2.18).
Теперь, если в какую-то ячейку из этого диапазона попытаться ввести что-либо неправильное, появится соответствующее предупреждение (рис. 2.19).
Контекстное меню ячейки (рис. 2.16) позволяет установить для ячейки комментарий. Комментарий — это какой-то текст, поясняющий назначение или особенности содержимого ячейки. При вызове команды "Добавить комментарий" из контекстного меню появляется диалог редактирования комментария (рис. 2.20). В области ввода можно писать произвольный текст, который станет комментарием к ячейке после нажатия на кнопку "ОК".
При создании комментария можно управлять атрибутами текста. Включение/выключение режима "Переносить окно свойств" в описываемой версии не даёт какого-либо эффекта.
(рис 2.19) Сообщение об ошибке при неправильном вводе
(рис 2.20) Создание комментария для ячейки ЭТ
Важно, что для каждого комментария устанавливается автор (текущий пользователь). При изменении комментария другим пользователем сохранится история изменений.
Ячейки с комментарием обозначаются красным треугольничком в верхнем правом углу ячейки (рис. 2.21). Комментарий можно увидеть, если навести курсор на этот треугольничек. Для удаления комментария следует вызвать диалог "Удалить 1 комментарий" с помощью контекстного меню ячейки.
Интересной особенностью ЭТ Gnumeric является возможность управления атрибутами отдельных символов текста в ячейке. Для этого в режиме редактирования (по нажатию <F2> или двойному клику по ячейке) следует выделить нужные символы и использовать кнопки управления атрибутами ("Полужирный", "Наклонный", "Подчеркнутый" и "Передний план") в панели инструментов Gnumeric. Пример такой модификации текста показан на рис. 2.22.
(рис 2.21) Просмотр комментария ячейки
Операции изменения формата данных, цвета переднего плана, фона и обрамления, а также копирования, перемещения и вставки можно проводить не только с одиночными ячейками, но и с группами (блоками) ячеек. Для этого требуется выделить группу ячеек. Для выделения нескольких соседних ячеек используется "протаскивание" мыши с нажатой левой кнопкой от верхнего левого до правого нижнего угла выделяемого блока или используются клавиши управления курсором ("стрелки") при нажатой клавише <SHIFT>. На рис. 2.23 показан вид области листа ЭТ с выделенным блоком ячеек.
Весь выделенный блок оказывается в общей "рамке", а фон всех выделенных ячеек меняет цвет, за исключением ячейки, которая была активной до выделения блока. Для всех выделенных ячеек с помощью контекстного меню и кнопок панели инструментов можно установить одинаковый формат данных, обрамление и цвет фона и текста, шрифт и другие параметры, за исключением комментария. Комментарий всё равно будет устанавливаться только для активной ячейки.
Для снятия выделения достаточно перейти в любую ячейку ЭТ щелчком левой кнопки мыши или с помощью клавиш-"стрелок".
(рис 2.22) Пример выделения отдельных символов в ячейке B4
(рис 2.23) Пример выделения непрерывного блока ячеек
Если необходимо выделить несколько ячеек, расположенных в произвольных позициях листа, используется щелчок левой кнопкой мыши по нужным ячейкам при нажатой клавише <CTRL>. Пример такого выделения показан на рис. 2.24.
Возможно также проводить операции со всеми ячейками в строке (строках) или столбце (столбцах) листа ЭТ. Для выделения всей строки нужно щелкнуть левой кнопкой мыши по номеру строки (рис. 2.25).
Контекстное меню строки, получаемое после щелчка правой кнопкой мыши по номеру строки (рис. 2.26) отличается от контекстного меню ячейки. Для строк появляются операции "Вставить 1 строку" и "Удалить 1 строку".
(рис 2.24) Выделение нескольких поизвольных ячеек
(рис 2.25) Результат выделения строки
(рис 2.26) Контекстное меню строки
(рис 2.27) Диалог настройки высоты строки
(рис 2.28) Выделение соседних строк
Диалог "Строка/высота..." (рис. 2.27) позволяет задавать высоту строки в точках экрана (pixels), причём она автоматически пересчитывается в типографские пунктах (пт, 1 пт = 1/72 дюйма) с учётом разрешения экрана.
Явное указание высоты строки может потребоваться для точного форматирования документа, однако в большинстве случаев высота устанавливается автоматически исходя из размера шрифта и параметров форматирования.
При выборе операции "Вставить 1 строку" новая строка появляется перед выделенной (выделенная строка смещается "вниз", её номер увеличивается на 1). При выборе операции "Скрыть" строка становится невидимой и её номер пропадает (нарушается непрерывность номеров). Чтобы снова увидеть скрытую строку, нужно выделить соседние строки и из контекстного меню выбрать операцию "Показать" (предлагается поэкспериментировать самостоятельно).
Для выделения нескольких соседних строк можно "протащить" мышь по номерам строк или выделить одну строку, а затем использовать "стрелки" при нажатой клавише <SHIFT> (рис. 2.28).
(рис 2.29) Выделение нескольких произвольных строк
(рис 2.30) Выделение столбца
Тем же самым способом, каким выделяются произвольные ячейки, выделяются и произвольные строки (рис. 2.29), только щелкать мышью нужно по номерам строк.
Аналогично строкам, столбцы выделяются целиком при щелчке левой кнопкой по имени столбца (букве), как показано на рис. 2.30.
Контекстное меню столбца показано на рис. 2.31.
Диалог настройки ширины столбца (рис. 2.32) позволяет точно устанавливать значение этого параметра. Однако, в отличие от высоты строк, ширина столбцов не изменяется автоматически.
Пусть в ячейки ЭТ введён текст, как показано на рис. 2.33. Содержимое ячейки B2 "не помещается" в видимую ширину столбца (на самом деле текст никуда не пропадает, в чём легко убедиться в режиме редактирования).
Для подбора нужной ширины столбца можно воспользоваться диалогом настройки ширины столбца (рис. 2.32), можно "растянуть" столбец за правую границу области имени столбца (прямоугольник, в котором написана буква), а можно по этой правой границе дважды щелкнуть левой кнопкой мыши, вызвав таким образом операцию "Автоподбор ширины". Результат автоподбора ширины для столбца B показан на рис. 2.34.
(рис 2.31) Контекстное меню столбца
(рис 2.32) Диалог настройки ширины столбца
(рис 2.33) Пример недостаточной ширины столбца
(рис 2.34) Результат автоподбора ширины
(рис 2.35) Автоподбор ширины соседних столбцов
(рис 2.36) Выделение нескольких произвольных столбцов
Если выделить соседние столбцы, то двойной щелчок по правой границе последнего выделенного столбца приведёт к выполнению автоподбора ширины для всех выделенных столбцов (рис. 2.35, сравните с рис. 2.34).
При выделении нескольких произвольных столбцов с помощью клавиши <CTRL> (рис. 2.36) для "пустых" столбцов автоподбор ширины не действует.
После упражнений с выделением отдельных ячеек, строк и столбцов логично задаться вопросом: а нет ли возможности выделить сразу все ячейки листа? Оказывается, такая возможность тоже существует. Для выделения всех ячеек листа нужно щёлкнуть левой кнопкой мыши по "кнопке" над столбиком с номерами строк (самый верхний левый угол таблицы, над номером 1 и левее буквы A). Это равносильно выбору команды главного меню "Правка/Выделение/Все".
(рис 2.37) Выделение всех ячеек листа
Основное применение всех электронных таблиц – "программирование без программирования", то есть создание таких связей между ячейками, чтобы на основе исходных данных в одних ячейках получался конечный результат в других ячейках. Поэтому для дальнейшей работы с таблицей будут нужны исходные данные. Они могут быть введены вручную, экспортированы из внешнего файла (например, из текстового) или смоделированы (сгенерированы) с помощью соответствующих инструментов пакета.
Рассмотрим возможности Gnumeric по генерации исходных данных. Исходные данные, как правило, располагаются в непрерывном блоке ячеек (столбце или строке) и затем используются для каких-либо вычислений. Для определенности будем располагать исходные данные в столбце.
Для создания последовательностей исходных данных (векторов, таблиц) можно использовать возможности заполнения блоков данных, используя пункт главного меню "Данные" (рис. 2.38).
Вложенное меню "Заполнить" как раз и предоставляет различные способы создания блоков ячеек с исходными данными (рис. 2.39).
Здесь мы рассмотрим только три варианта – генерация последовательностей ("Автозаполнение"), создание прогрессий ("Серии...") и создание выборок последовательностей случайных чисел с различными видами функций распределения ("Случайные числа.../Некоррелированные...").
(рис 2.38) Пункт "Данные" главного меню Gnumeric
(рис 2.39) Вложенно меню "Заполнить"
Для начала сформируем последовательность натуральных чисел от 0 до 25. Для этого введем в ячейку A3 значение "0", а в A4 – значение "1" (без кавычек!), затем выделим ячейки от A3 до A28 (всего 25 ячеек) путем "протаскивания" мыши с нажатой левой кнопкой. Далее вызовем функцию автозаполнения ряда: "Данные/Заполнить/Автозаполнение" и пронаблюдаем результат.
Далее сформируем последовательность из 25 чисел, кратных 7, начиная с 0. По аналогии с предыдущим случаем, введем в ячейку B3 значение "0", а в ячейку B4 – значение "7", после чего повторим операции выделения и вызова функции автозаполнения и посмотрим на результат.
Такую же операцию можно провести с датами. Пусть известно, что 21 мая 2007 года и 28 мая этого же года были понедельниками. На какие даты будут приходиться понедельники в течение последующих 23 недель? (Аналогично предыдущему случаю, только вместо чисел – даты). Соответственно, вводим в ячейку C3 значение "21/05/07", в C4 – "28/05/07", выделяем снова 25 ячеек и вызываем автозаполнение. Затем устанавливаем формат дат как "dd/mm/yy" с помощью диалога "Формат ячеек". Результат показан на рис. 2.40.
(рис 2.40) Результаты использования автозаполнения
Нужно отметить, что с другими (не числовыми) данными функция "Автозаполнение" не работает. Таким образом, функция "Автозаполнение" продолжает зависимость, определенную по первым двум ячейкам диапазона, на все ячейки выделенного диапазона.
Следующая возможность автоматической генерации исходных данных – создание серий. Она может потребоваться, если заранее неизвестно количество ячеек для автозаполнения, или если это количество велико, а также если зависимость значения следующей ячейки от предыдущей не является линейной.
Рассмотрим примеры генерации серий чисел со значениями от 1 до 100. Диалог заполнения ячеек сериями значений ("Данные/Заполнить/Серии...") содержит три вкладки (рис. 2.41).
(рис 2.41) Настройка серий для заполнения ячеек данными
(рис 2.42) Результат заполнения сериями
Сформируем серию значений, линейно изменяющихся по столбцу (арифметическую прогрессию). Устанавливаем соответствующие параметры на вкладке "Серии", указываем в качестве начального значения число 1, приращение 10 и конечное значение 100 (рис. 2.41). Больше никаких дополнительных настроек не требуется. После нажатия на <ENTER> (или использования кнопки "ОК" в диалоге) наблюдаем результат (рис. 2.42). Видно, что последнее значение серии меньше указанного максимального значения. Однако если провести эксперимент с начальным значением, равным 0, то последним значением в серии будет 100. Таким образом, можно сделать очевидный вывод, что конечное значение в серии никогда не превышает указанного максимального значения.
Теперь в соседних столбцах создадим серии с ростом в 2 и в 3 раза при тех же начальных и конечных значениях (именно поэтому в качестве начального значения выбрана 1, а не 0). Коэффициент роста указывается как приращение. Результаты показаны на рис. 2.42).
С заполнением диапазона ячеек серией дат читателям предлагается разобраться состоятельно.
Следующая интересная возможность генерации исходных данных – заполнение диапазона ячеек случайными числами с различными функциями плотности распределения. Всего предлагается около 30 вариантов. Для использования этой возможности полезно иметь выделенный диапазон ячеек (блок). Можно реализовать двумерное поле случайных чисел, если выделить не часть строки или столбца. а прямоугольный диапазон и сделать соответствующие настройки в диалоге "Генерация случайных чисел" ("Данные/Заполнить/Случайные числа.../Некоррелированные...", рис. 2.43).
Диалог "Генерация случайных чисел" также имеет три вкладки.
(рис 2.43) Диалог генерации случайных чисел (однородное распределение)
В качестве примера создадим три вектора из 100 случайных чисел с разными распределениями: равномерным в диапазоне от 0 до 10 (как на рис. 2.43), нормальным со средним значением 5 и стандартным отклонением 2, а также с распределением $$x^2$$ со значением параметра $$v=2$$. Для полученных результатов установим формат с 4-мя десятичными знаками (рис. 2.44)
Рассмотренные возможности по генерации исходных данных будут полезны при дальнейшей работе с пакетом Gnumeric.
(рис 2.44) Три вектора случайных чисел
Формулы в электронных таблицах предназначены для вычислений значений в ячейках таблицы на основе данных, записанных в другие ячейки. Результатом работы формулы может быть число, дата, текст или отсутствие данных (т.е. после вычислений можно получать пустые ячейки). Формула записывается в той ячейке, в которой должен быть результат. Ввод формулы начинается с нажатия на символ "=" на клавиатуре, после чего вводятся числа, адреса ячеек и знаки операций. Например, формула "=3+5" даст всегда результат "8", а формула "=A3/12" будет давать различные результаты при изменении значения в ячейке A3.
Таким образом, при изменении содержимого ячеек, адреса которых используются в формулах, результаты пересчитываются автоматически. Эта особенность является ключевой для всех электронных таблиц. Из-за этого свойства крайне не рекомендуется использовать в формулах конкретные значения, если только они не являются неотъемлемой частью правил вычисления (например, если площадь круга вычисляется в евклидовой геометрии как $$\pi R^2$$, то двойка может использоваться в формуле вычисления площади круга, а вот конкретное значение радиуса – нет).
(рис 2.45) Исходные данные для задачи о заработках
Электронная таблица с хорошо составленными формулами представляет из себя мощное средство решения вычислительных задач и, по сути, является программой, выдающей результаты на основе содержимого ячеек с исходными данными.
Теперь рассмотрим некоторые примеры использования формул и адресации данных в таблицах Gnumeric.
Начнем вычисления в ЭТ с простой задачи. Пусть имеется список из 7 человек, для каждого из которых известны фамилия, инициалы, должность, оклад за день работы и число отработанных дней. Требуется вычислить заработок каждого лица.
Формируем таблицу, начиная с ячейки A3, в соответствии с рис. 2.45. Для исправления ошибок в ячейках электронной таблицы используется режим редактирования строки ввода, который включается клавишей <F2>. Завершение редактирования обеспечивается клавишами <ENTER> (с сохранением изменений) или <ESC> (без сохранения изменений).
Если при вводе информации ширина ячейки представляется недостаточной, ее можно скорректировать после завершения ввода всех данных. Как уже упоминалось выше, всегда есть возможность подобрать ширину столбца автоматически двойным щелчком левой кнопкой мыши на правой границе столбца в строке имен столбцов.
Для вычисления заработка нужно просто перемножить попарно числа из третьей (столбец C) и четвертой (столбец D) колонок. Результаты вычислений должны быть в пятой колонке (столбец E). С учетом возможностей ЭТ, формулу (т.е. правила) для вычислений можно написать один раз, а потом скопировать. Формулу надо писать там, где должен появиться первый результат (в нашем примере – в ячейке E4, под заголовком "Заработок"). Переводим указатель активной ячейки в клетку E4 и нажимаем клавишу "=" (указание на начало ввода формулы). После этого щелкаем левой кнопкой мыши по ячейке, в которой записан оклад за день (C4), нажимаем на клавиатуре знак операции (умножение – "*") и щелкаем левой кнопкой мыши по ячейке с количеством отработанных дней (D4), после чего нажимаем <ENTER>. В ячейке E4 появляется результат (число 1100), а переместив указатель активной ячейки на E4, в строке ввода увидим формулу =C4*D4
(рис 2.46) Результат расчёта заработка
Теперь скопируем эту формулу в оставшиеся ячейки. Поместив указатель активной ячейки на E4, наведем мышь на нижний правый угол указателя активной ячейки (там есть маленький черный квадратик). Двойной щелчок левой кнопкой мыши в этой позиции приведет к автоматическому копированию формулы до конца блока исходных данных. Результаты показаны на рис. 2.46.
Если изменить какие-то числа в столбцах C и D, то числа в столбце E будут автоматически пересчитываться.
Перемещая указатель активной ячейки по столбцу E заметим, что адреса ячеек в расчетной формуле изменяются. Это происходит потому, что в нашей формуле использованы относительные адреса ячеек. Формула просто перемножает содержимое ячеек, находящихся слева от ячейки с результатом. Таким образом, формула "запомнила" взаимное расположение ячеек с данными и с результатом и при копировании это взаимное расположение сохраняется. Это очень полезное свойство ЭТ, избавляющее от необходимости писать одну и ту же формулу много раз.
Если в какой-либо ячейке расчетного столбца (столбца "Заработок") перейти в режим редактирования (<F2>), то можно увидеть формулу. При перемещении текстового курсора по формуле будут подсвечиваться ячейки, содержащие данные для формулы (рис. 2.47).
На следующем этапе посчитаем налог на доходы физических лиц, который будет начислен на рассчитанные ранее значения заработка. Пусть ставка налога фиксирована и составляет 13%. Тогда наша таблица дополняется в соответствии с рис. 2.48 (здесь и в следующих иллюстрациях к этому примеру первый столбец "обрезан").
(рис 2.47) Просмотр формулы в режиме редактирования
(рис 2.48) Таблица для вычислений с параметром
Сумму налога легко сосчитать по правилу "Сумма налога = заработок*ставка_налога". Указав соответствующие адреса ячеек, в ячейке F4 записываем формулу =E4*D1 и копируем ее во все оставшиеся ячейки. При этом получается неожиданный результат (рис. 2.49).
В этом случае использование относительной адресации привело к ошибке – запомнив взаимное расположение ячеек результата и исходных данных (первого заработка в списке и ставки налога) программа ЭТ повторяет это взаимное расположение для остальных строк списка (в чем можно убедиться, войдя в режим редактирования, как показано на рис. 2.49). Чтобы не создавать дополнительный столбец с одним и тем же значением ставки налога, в соответствующей формуле надо использовать абсолютный адрес ячейки, содержащей параметр (в данном случае – значение ставки налога). Для указания абсолютного адреса к букве столбца или номеру строки добавляется префикс $ и формула для расчета суммы налога приобретает вид =E4*$D$1 (для добавления символов $ при редактировании формулы можно использовать клавишу <F4>). Отредактировав формулу в ячейке F4, копируем ее снова в оставшиеся ячейки и получаем правильный результат (рис. 2.50).
(рис 2.49) Неправильный результат вычислений с параметром
(рис 2.50) Правильные вычисления с параметром
В режиме редактирования теперь видно, что во всех ячейках при вычислении суммы налога происходит обращение к ячейке, содержащей ставку налога, независимо от строки таблицы (вспомним, что одинаковое количество знаков после запятой во всех ячейках можно получить, изменив формат представления чисел).
Итак, абсолютный адрес указывает программе ЭТ, что нужно всегда обращаться к одной и той же ячейке (если поставлено два префикса $), строке (если $ поставлен перед номером строки) или столбцу (если $ – перед буквой столбца). Использование абсолютных адресов позволяет работать с условно-постоянными величинами (ставка налога, курс валюты, текущая дата и пр.), причем их значения заносятся в таблицу только один раз, что экономит время и место.
(рис 2.51) Автосуммирование по столбцу
Нужно заметить, что формулы позволяют связывать между собой не только ячейки в пределах одного листа, но и ячейки на разных листах документа. В результате можно строить "трехмерные" электронные таблицы.
Полезная и часто используемая возможность электронных таблиц – автосуммирование. Для использования этой возможности нужно установить указатель активной ячейки в позицию, в которой нужно получить результат и нажать на панели инструментов Gnumeric кнопку ∑ (знак суммы). Программа автоматически определит непрерывный блок ячеек выше или слева от целевой и предложит вариант функции для вычисления результата. Обратите внимание, что диапазон ячеек указывается с использованием двоеточия, как показано на рис. 2.51.
В качестве упражнения посчитайте аналогичным образом сумму всех налогов.
Для проведения математических, тригонометрических, экономических или инженерных вычислений четырех действий арифметики явно недостаточно. Поэтому все электронные таблицы имеют большое число встроенных функций, которые могут включаться в состав формул. Каждая функция имеет имя и список аргументов, которые помещаются в скобки сразу за именем функции. Могут быть функции с пустым множеством аргументов, такие как pi() или today(), а могут быть функции с неограниченным количеством аргументов (такие как sum(...) или average(...)), однако для большинства функций количество аргументов фиксировано.
(рис 2.52) Диалог выбора функции (селектор функций)
В зависимости от назначения и синтаксиса функции, аргументом функции может быть число, текст, дата или логическое (булево) выражение . Чаще всего в качестве аргумента (или в составе аргумента) используются адреса ячеек. В случае нескольких аргументов разделителем аргументов является точка с запятой (символ ";"). Если в качестве аргумента используется текст, то он должен быть заключен в кавычки (символ "''"). Кроме того, в аргументах функций могут использоваться другие функции и арифметические выражения (например, возможны формулы типа "=2*sin(3*A3*pi()/4)").
Для вызова селектора встроенных функций используется либо главное меню ("Вставка/Функция"), либо кнопка $$f(x)$$ в панели инструментов. После этого появляется диалог выбора функции. На рис. 2.52 показан пример для логической функции if(), которая подробнее будет рассмотрена ниже.
При выборе функции сначала выбирается категория функций (в верхней части диалога), а затем – конкретная функция (в средней части диалога). В нижней части диалога приводится объяснение назначения и структуры функции. После нажатия на кнопку "OK" появляется диалог определения аргументов выбранной функции (помощник по формулам, рис. 2.53). Для приведенной в примере функции if() нужно определить три аргумента.
(рис 2.53) Составление формулы
Для определения аргументов нужно щелкнуть мышкой в поле ввода (в столбце "Функция/Аргумент", указать ячейку со значением аргумента (можно щелкнуть мышкой по нужной ячейке) и при необходимости дописать остальное. Завершается определение аргумента нажатием на <ENTER>, после чего с помощью щелчка мышкой переходим к определению следующего аргумента. По мере определения аргументов функция дописывается.
Окончательный результат построения функции if() показан на рис. 2.54. В данном случае проверяется ячейка C3. Если в ней содержится ненулевое значение, должно быть выведено сообщение "Не ноль!", в противном случай – сообщение "Ноль"
Кнопка $$f(x)$$ в этом диалоге позволяет вставить функцию в качестве аргумента формируемой функции. При необходимости можно редактировать ссылки типа "Лист1!B4", превращая их в ссылки типа "B4".
После окончательного определения всех аргументов и нажатия на кнопку "ОК" можно наблюдать результат работы функции. При необходимости можно редактировать функцию либо в строке ввода по нажатию клавиши <F2>, либо снова вызвать диалог определения аргументов функции, использовав кнопку f(x), когда ячейка с результатом функции является активной.
Далее кратко рассмотрим несколько основных групп функций, а потом перейдем к конкретными примерам.
(рис 2.54) Функция if() саргументами
Gnumeric содержит около 80 встроенных математических функций. Описывать все нет необходимости (использование и особенности функций типа abs(), sin(), tan() или pi() достаточно очевидны). Приведем здесь краткое описание некоторых более редких математических функций.
| Название, аргументы | Назначение |
|---|---|
| atan2(b1;b2) | Вычисляет арктангенс отношения $$b2/b1$$ без учета знаков аргументов. |
| beta(a;b) | Вычисляет бета-функцию (интеграл Эйлера $$I$$ рода) для положительных целых аргументов. |
| betaln(a;b) | Вычисляет натуральный логарифм бета-функции для положительных целых аргументов. |
| combin(n;k) | Вычисляет количество уникальных комбинаций из $$n$$ по $$k$$. |
| expm1(x) | Вычисляет $$exp(x)-1$$ с высокой точностью. |
| factdouble(m) | Вычисляет двойной факториал положительного числа $$m$$, т.е. $$m!!$$. Если m не целое, дробная часть обрезается. |
| hypot(a;b;...) | Вычисляет квадратный корень из суммы квадратов аргументов. |
| multinomial(x1;x2;...) | Вычисляет отношение факториала суммы аргументов к произведению их факториалов. |
| quotient(a;b) | Вычисляет целую часть результата деления $$a$$ на $$b$$. |
| roman(m;тип) | Преобразует натуральное число в "римскую" форму записи. Необязательный аргумент "тип" определяет вариант нотации (классический или краткий). |
| seriessum(X;n;m;коэффициенты) | Вычисляет сумму степенного ряда вида $$a*X^n+b*X^{n+m}+c*X^{n+2_m}+d*X^{n+3_m...}$$, т.е. $$X$$ – основание степени, $$n$$ – начальное значение показателя степени, $$m$$- инкремент показателя степени, а коэффициенты $$a,b,c...$$ - коэффициенты при каждом члене ряда, записанные в блоке ячеек таблицы. |
| sqrtpi(m) | Вычисляет квадратный корень из m*pi() (sqrt(m*pi()). |
| sumsq(x1;x2;...) | Вычисляет сумму квадратов аргументов. |
| sumx2my2(вектор1;вектор2) | Вычисляет сумму разностей квадратов соответствующих элементов векторов. Вектор1 и вектор2 – блоки ячеек одинаковой длины. |
| sumx2py2(вектор1;вектор2) | Вычисляет сумму сумм квадратов соответствующих элементов векторов. |
| sumxmy2( вектор1;вектор2) | Вычисляет сумму квадратов разностей соответствующих элементов векторов. |
Логических функций вообще немного, поэтому рассмотрим их все. Каждая логическая функция может быть аргументом другой, если это позволено синтаксисом.
| Название, аргументы | Назначение |
|---|---|
| false() | Аргументов не имеет. Всегда выдает логическое значение "ЛОЖЬ" (FALSE). |
| true() | Аргументов не имеет. Всегда выдает логическое значение "ИСТИНА" (TRUE). |
| and(условие1;условие2;...) | Имеет неограниченное количество аргументов (более одного). Выдает логическое значение "ИСТИНА" (TRUE), если выполняются все условия, приведенные в аргументах. Пример синтаксиса: $$and(c1>0,b4=3,d2<b4)$$, где $$c1, b4$$ и $$d2$$ – адреса ячеек. |
| or(условие1;условие2;...) | Имеет неограниченное количество аргументов (более одного). Выдает логическое значение "ИСТИНА" (TRUE), если выполняются хотя бы одно из условий, приведенных в аргументах. Синтаксис аналогичен функции and(). |
| not(условие) | Аргументом является условие или результат работы логической функции (логическое TRUE или FALSE). Выдает логическое значение "ИСТИНА" (TRUE), если условие не выполняется или аргумент установлен в FALSE. |
| xor(условие1;условие2;...) | Имеет неограниченное количество аргументов (более одного). Выдает логическое значение "ИСТИНА" (TRUE), если выполняется нечетное количество условий (исключающее ИЛИ). |
| if(условие;действие1;действие2) | Проверяет условие, и если оно выполняется, производится действие1 (возможна проверка еще какого-то условия, выполнение арифметических операций или вычисление по формуле с функциями, а также вывод текста). В противном случае выполняется действие2 (с теми же особенностями). |
Важным свойством функции if() является возможность использования результата работы одной функции if() в качестве аргумента другой функции if(). Однако количество таких вложений ограничивается максимально допустимой длиной текста в ячейке, а также здравым смыслом (формулы с количеством проверок условий более 7-8 крайне трудно анализировать в случае ошибок). Также в качестве условий в if() могут использоваться любые синтаксически допустимые комбинации логических функций.
Функций работы с комплексными числами в Gnumeric насчитывается более сорока, поэтому здесь рассмотрим только основные (и наиболее интересные с точки зрения автора).
| Название, аргументы | Назначение |
|---|---|
| complex(x1;x2;символ) | Формирует комплексное число из двух вещественных. Третий необязательный аргумент (символ) позволяет изменить обозначение мнимой единицы. Если он не указан, будет сформировано комплексное число вида $$x1+x2i$$. |
| imabs(complex) | Вычисляет модуль комплексного числа. Например, если в ячейке E3 записано комплексное число $$5+3i$$ (как результат функции complex()), то imabs(E3) выдаст 5,83095. |
| imargument(complex) | Вычисляет аргумент комплексного числа (показатель степени при экспоненциальном представлении). Для примера $$5+3i$$ выдаст значение 0,54042. |
| imreal(complex) | Выдает вещественную часть комплексного числа. |
| imaginary(complex) | Выдает мнимую часть комплексного числа. |
| imconjugate(complex) | Вычисляет комплексно сопряженное число. |
| imdiv(complex1;complex2) | Вычисляет целую часть результата деления двух комплексных чисел. |
| iminv(complex) | Выполняет преобразование $$1/z$$. |
| impower(complex;power) | Возводит комплексное число в степень, которая тоже может быть комплексным числом. |
| improduct(complex1;complex2;...) | Вычисляет произведение комплексных чисел (обычная операция умножения не работает!). |
Далее рассмотрим использование логических и математических функций, а также функций комплексного переменного на классическом примере вычисления корней квадратного уравнения с произвольными коэффициентами.
(рис 2.55) Решение квадратного уравнения
Итак, заданы три коэффициента $$A, B$$ и $$C$$ квадратного уравнения вида
$$A \cdot x^2+B \cdot x+C=0$$Требуется вычислить корни $$x_1$$ и $$x_2$$, которые в общем случае могут быть комплексными. Перед вычислением корней вычислим дискриминант $$D$$:
$$D=B^2-4 \cdot A \cdot C$$И затем воспользуемся формулой для вычисления корней:
$$\frac {x_{1,2}=-B+- \sqrt{D}}{2 \cdot A}$$Однако при отрицательном дискриминанте корни будут комплексными. Такое комплексное число будет иметь вещественную часть $$(-B/2A)$$ и мнимую часть (с точностью до знака)
$$\Im_{1,2}= +-\frac { \sqrt{D}}{2 \cdot A}$$В то же время для неотрицательных значений дискриминанта будут работать обычные правила вычисления корней (в соответствии с формулой (2.3)). Таким образом, в формуле для вычисления корня должна присутствовать проверка дискриминанта на отрицательность, и при отрицательном дискриминанте должно быть сформировано комплексное число. При неотрицательном дискриминанте используются обычные функции и арифметические действия.
После столь долгих рассуждений пора показать таблицу и формулы для вычислений (рис. 2.55).
Формулы для вычислений приведены ниже:
| Адрес ячейки, назначение | Формула |
|---|---|
| C6: Дискриминант | =C4^2-4*C3*C5 |
| C7: Корень X1 | =if(C6<0;complex(-C4/(2*C3);sqrt(abs(C6)));(-C4+sqrt(C6))/(2*C3)) |
| C8: Корень X2 | =if(C6<0;complex(-C4/(2*C3),-sqrt(abs(C6)));(-C4-sqrt(C6)/(2*C3)) |
Календарные функции в Gnumeric находятся в категории "Дата/Время", всего таких функций более 30. Основными являются функции "разложения" даты на составляющие – выделения из даты номера дня в месяце (функция day()), номера месяца в году (month()) и года (year()) – и функция обратного преобразования date(year(),month(),day()), которая конструирует данные типа "дата" из номера года, месяца и дня.
Также часто используются функции автоматического определения дня недели (weekday()), текущей даты (today()) и текущего момента времени (now()).
Имеются также интересные (и полезные) функции преобразования дат – date2unix() и обратная ей unix2date(). Они выполняют преобразование даты в количество секунд, прошедших с начала "эры UNIX", и наоборот ("эра UNIX" началась в полночь 1 января 1970 года).
Для знакомства с основными календарными функциями рассмотрим следующую задачу. Дан список лиц, для которых известны фамилии, пол и даты рождения. Определить:
Для этой задачи воспользуемся исходными данными (списком фамилий) из задачи про доходы и налоги. Пол установим в соответствии с фамилиями, даты рождения введем произвольно (как уже упоминалось, удобно вводить дату в виде 23/11/88, а программа приведет ее в нужный вид).
Для получения решения по пункту "a" необходимо проделать некоторые промежуточные вычисления. Сначала нужно для каждого лица сформировать дату рождения в текущем году на основании дня и месяца рождения, а также номера текущего года. Тогда первая формула (в ячейке D4 на рис. 2.56) будет иметь вид:
=DATE(YEAR(TODAY());MONTH(C4);DAY(C4))
Очевидно, что функция today() просто выдает текущую дату, а функция date() формирует дату из номера года, номера месяца и номера дня в месяце, определяемых соответственно, с помощью функций year(), month() и day(). Естественно, при желании вместо результатов работы функций можно использовать адреса ячеек, содержащих числа или просто числа. Соответственно, формула для окончательного результата по пункту "а" (в ячейке E4) будет иметь вид:
=IF(OR(WEEKDAY(D4;2)=6;WEEKDAY(D4:2)=7);"УРА!";"УВЫ...")
где функция weekday() вычисляет день недели для даты, указанной в первом аргументе. Второй аргумент функции weekday() определяет правила нумерации дней недели. В данном случае первый день недели соответствует понедельнику.
Для обработки всего списка просто копируем эти формулы вниз. Для получения решения по пункту "b" воспользуемся функцией вычисления разности дат – datedif(). Эта функция имеет три аргумента – начальную дату, конечную дату и строку-параметр, задающую единицы измерения разности. Значения параметра "y", "m" или "d" позволяют найти разность дат соответственно, в полных годах, полных месяцах и в днях. С учетом того, что возраст определяется в полных годах, запишем формулу для возраста в следующем виде:
=datedif(C4;TODAY();"y")
Для получения решения по пункту "c" снова нужно формировать даты, используя значения параметров возраста выхода на пенсию. Эти возрасты на момент написания книги составляют 60 лет для мужчин и 55 для женщин, однако они могут в любой момент измениться, поэтому конкретные числа в формулу записывать не будем. Дата выхода на пенсию формируется с использованием условия проверки пола. Итак, получаем формулу:
=IF(B4="М";DATE(YEAR(C4)+$G$1,MONTH(C4);DAY(C4));DATE(YEAR(C4)+$G$2;MONTH(C4);DAY(C4)))
Результирующая таблица показана на рис. 2.56.
Последний столбец (дата выхода на пенсию) приведен в формате "dd/mm/yyyy" для иллюстрации корректного выполнения вычислений.
(рис 2.56) Пример вычисление с датами
В тех случаях, когда использование функции if() становится неудобным по причине большого количества вложений или большой длины формулы, а также для повышения эффективности вычислений, в электронных таблицах используются функции поиска соответствий. К таким функциям относятся lookup(), vlookup(), hlookup(), match() и index(), находящиеся в категории "Поиск".
Понимание работы этих функций может сначала вызывать некоторые затруднения, однако они (вместе с if()) обеспечивают мощные средства обработки данных, поэтому их освоение стоит затраченного времени.
Функции lookup(), vlookup() и hlookup() в качестве одного из аргументов используют так называемые "ассоциативные массивы" (справочники). Ассоциативный массив – это структура данных, оформленная в виде таблицы, первый столбик которой содержит "ключи" – данные, которые участвуют в формировании условий. В следующих столбиках ассоциативного массива содержатся значения, соответствующие ключам. Таким образом, по значению ключа можно однозначно получить какие-то другие данные (в языках программирования такие структуры называются "хэш-таблицы").
В программах ЭТ ассоциативные массивы реализуются как блоки ячеек (справочные таблицы), содержащие минимум два столбца. Первый столбец содержит ключи, второй – значения, соответствующие ключам.
Для примера рассмотрим следующую задачу. В ралли участвуют гонщики на автомобилях, для которых известны марки и расход топлива в литрах на 100 км. Дан список, в котором указаны фамилии гонщиков и марки автомобилей. Известна протяженность трассы гонок в километрах L. Определить расход топлива для каждого участника гонок.
(рис 2.57) Справочник для задачи о ралли
(рис 2.58) Исходные данные задачи о ралли
Для решения задачи составим справочную таблицу для 7-ми разных марок а/м, и расположим ее в ячейках F3:G10 (рис. 2.57). Полезно соблюдать алфавитный порядок текстовых значений в первом столбике справочной таблицы.
Данные (список участников и марки их а/м) запишем в ячейки A3:B10 (рис. 2.58). Длину трассы L, которая является параметром, запишем в ячейку C1. Для вычисления полного расхода топлива используем формулу с функцией LOOKUP().
Формула в ячейке C4 будет выглядеть следующим образом.
(рис 2.59) Решение задачи о ралли
=LOOKUP(B4;$F$4:$F$10;$G$4:$G$10)*$C$1/100
Функция LOOKUP() считывает содержание ячейки, указанной в первом аргументе, ищет это значение в диапазоне ячеек (столбце), указанном во втором аргументе и выдаёт соответствие этому значению из диапазона ячеек (столбца), указанном в третьем аргументе. Таким образом, для получения результата по названию а/м находим расход топлива на 100 км и умножаем это значение на количество сотен километров. Принципиально важно указывать абсолютные адреса блоков ячеек справочной таблицы.
Итоговая таблица показана на рис. 2.59.
Ограничение функции lookup() – только один столбец соответствий. Более "мощной" является функция vlookup(), в которой второй аргумент определяет весь блок ячеек, содержащих ассоциативный массив (справочник), а третий аргумент указывает, в каком столбце ассоциативного массива нужно искать соответствие ключу. Четвёртый (необязательный) аргумент определяет порядок сортировки первого ("ключевого") столбца справочника. Если он не указан или равен 1 (логическая ИСТИНА), то первый столбец ассоциативного массива для функции vlookup() должен содержать числа, отсортированные по возрастанию, или текст, отсортированный в алфавитном порядке. Если значения в первом столбце не отсортированы, то четвертый аргумент должен быть установлен в 0. Еще одним большим достоинством функции vlookup() является возможность работы с диапазонами значений ключа.
Для примера рассмотрим вычисление суммы годового налога при прогрессивной налоговой шкале. Пусть при годовом доходе до 10000 у.е. ставка налога составляет 12%, до 30000 у.е. – 20%, до 50000 у.е. – 25% и при большем доходе – 35%. Для создания таблицы данных используем фамилии из предыдущего примера, а суммы годового дохода запишем такие, чтобы можно было реализовать все варианты ставок налога (рис. 2.60). Таблицу данных разместим в диапазоне A3:B10.
(рис 2.60) Исходные данные задачи о налогах
(рис 2.61) Справочник к задаче о налогах
Справочную таблицу размещаем в диапазоне E3:F7 (рис. 2.611), формула в ячейке C4 с использованием функции vlookup() выглядит следующим образом:
=VLOOKUP(B4;$E$4:$F$7;2)*B4
Во втором столбце ассоциативного массива находим ставку налога, соответствующую доходу, а затем получаем сумму налога, умножая ставку налога на величину дохода (рис 2.62).
(рис 2.62) Решение задачи о налогах
Важно, что VLOOKUP() выбирает значения, соответствующие нижней границе диапазона значений ключей, и используется минимальное значение ключа в интервале.
Четвертый аргумент функции VLOOKUP() также влияет и на возможность интервального просмотра. Если этот аргумент имеет значение ИСТИНА (1) или опущен, то интервальный просмотр работает, как описано выше. Если этот аргумент имеет значение ЛОЖЬ (0), то функция VLOOKUP() ищет точное соответствие. Если таковое не найдено, то возвращается значение ошибки #N/A (#Н/Д – "нет данных"). Таким образом, для использования возможности работы с интервалами значений в VLOOKUP() первый столбец справочной таблицы (ассоциативного массива) обязательно должен быть отсортирован по возрастанию.
Функция hLOOKUP() работает аналогично VLOOKUP(), только порядок следования "ключей" – не сверху вниз, а слева направо.
Теперь рассмотрим формат функций match() и index().
Функция MATCH(искомое_значение; искомый_массив; тип_сопоставления) — находит позицию (порядковый номер) искомого значения в одномерном массиве. Значение аргумента "тип сопоставления" – (-1, 0 или 1) – зависит от того, упорядочен ли массив (-1 — массив упорядочен по убыванию, находится место наименьшего значения, которое больше или равно искомому, 0 — массив может быть неупорядоченным, находится место первого значения, равного исходному, 1 — массив упорядочен по возрастанию, находится место наибольшего значения, которое меньше или равно искомому).
Функция INDEX(массив; номер_строки; номер_столбца) — находит значение элемента, находящегося в заданном массиве на пересечении заданных строки и столбца.
Далее рассмотрим пример. Пусть на основе эксперимента получена следующая зависимость посещаемости дискотеки от входной платы:
| Входная плата, у.е. | 1 | 1,5 | 2 | 2,5 | 3 | 3,5 | 5 |
| Количество посетителей | 200 | 175 | 160 | 140 | 124 | 110 | 70 |
(рис 2.63) Решение задачи о дискотеке
Определить оптимальную входную плату.
Очевидно, что оптимуму соответствует максимальная выручка. Соответственно, решение задачи состоит в том, чтобы подсчитать выручку в каждом случае, найти максимальную и написать формулу, показывающую входную плату, соответствующую максимальной выручке. То есть нужно определить номер столбика, в котором получается максимальная выручка и вывести значение входной платы.
Определяем выручку в каждом случае (умножив входную плату на количество посетителей), затем функцией MAX() находим наибольшую выручку. После этого функцией MATCH() определяем, на каком месте в массиве она находится и функцией INDEX() смотрим, какая входная плата находится на этом месте (рис. 2.63). Рядом с ячейками с результатами приведены формулы для получения этих результатов (используется функция expression()).
В этом примере участвует функция max(), которая относится к категории статистических функций, к знакомству с которыми теперь и перейдем.
Основные статистические функции – это min(), max() и average(), вычисляющие, соответственно, минимальное, максимальное и среднее значение для набора аргументов. Аргументы могут быть либо перечислены через точку с запятой (если данные находятся в несмежных ячейках), либо в качестве аргумента может быть использован диапазон ячеек. Также к статистическим функциям можно отнести функции sumif() и countif(), которые формально находятся в Gnumeric среди математических функций, а также функцию rank(), определяющее "рейтинг" какого-то значения в списке аналогичных значений.
В качестве примера рассмотрим следующую задачу.
Дан список участников соревнования среди студентов по бегу на 100 метров и метанию мяча. В таблице (рис. 2.64) указаны пол (юноша или девушка) и результаты. Определить места каждого участника в каждом виде соревнований, минимальный, максимальный и средний результаты в каждом виде соревнований, на сколько юноши (в среднем) метают мяч дальше, чем девушки.
Сразу под списком в соответствующих столбцах подсчитываем максимальный, минимальный и средний результаты (по столбцу С =Max(C2:C16), =Min(C2:C16), =AVERAGE(C2:C16) и аналогично по столбцу D).
Затем подсчитываем количество юношей и количество девушек. В ячейку B22 вносим формулу
=COUNTIF(B2:B16;"юноша"),
а в ячейку B23 формулу
=COUNTIF(B2:B16;"девушка")
В ячейки C22 и C23 записываем формулы для подсчета суммы результатов по метанию для юношей и девушек соответственно
=SUMIF(B2:B16;"юноша",D2:D16)
=SUMIF(B2:B16;"девушка",D2:D16)
После чего подсчитываем среднее значение в ячейках D22 и D23, разделив сумму результатов на количество участников в каждой группе. Затем подсчитываем разницу средних значений (рис. 2.64).
Для иллюстрации использования функции rank() рассмотрим следующую задачу: На основе данных из задачи о соревнованиях распределить места в соревнованиях по бегу среди юношей, а также определить, сколько человек показали результаты ниже среднего (т.е. время больше среднего).
Для распределения участников по местам как раз и потребуется функция rank(), а кроме того потребуется COUNTIF() для вычисления с условием. Однако условие для COUNTIF() обязательно должно быть текстом, поэтому при формировании условия с вычисляемыми данными целесообразно использовать текстовую функцию concatenate(). Результат использования этих функций показан на рис. 2.65.
(рис 2.64) Решение задачи о соревнованиях
Формула для определения места участника соревнований (ячейка C2) будет выглядеть следующим образом:
=rank(B2;$B$2:$B$7;1)
Абсолютные адреса диапазона использованы для обеспечения возможности копирования формулы. Третий аргумент установлен в "1", что обеспечивает обратный порядок распределения мест, т.е. чем меньше значение, тем лучше место (первое место – минимальный результат). Для обеспечения прямого порядка распределения мест (первое место – максимальный результат) третий аргумент нужно установить в "0" или не указывать.
(рис 2.65) Задача о распределении мест
Среднее время определяется с помощью функции average(), а условие для подсчета аутсайдеров формируется с помощью текстовой функции concatenate(), которая "сцепляет" строки для формирования одного значения строкового типа. В данном случае в ячейке B10 записана формула
=concatenate(">";B9).
Подсчет аутсайдеров выполняется по формуле (ячейка B11)
=countif(B2:B7;B10).
К статистическим функциям относятся также функции count() (подсчет количества числовых значений в диапазоне ячеек) и counta() (подсчет количества не пустых ячеек в диапазоне). Кроме того, большое число статистических функций вычисляют статистические параметры различных распределений и обеспечивают генерацию случайных чисел с заданными параметрами распределений.
Одна из типовых задач в офисной работе – изменения формы представления списков людей или организаций. Избежать трудоёмкой работы по переписыванию текста из одного вида в другой помогают функции работы с текстом (строковые функции).
Почему-то подобные задачи кажутся очень сложными, поэтому покажем пример решения и рассмотрим минимальный набор функций, необходимых для получения результата.
(рис 2.66) Пример преобразования списка
Пусть имеется список лиц с полными фамилиями, именами и отчествами (например, список группы слушателей каких-нибудь курсов). Для составления журнала полные имена и отчества не требуются, а требуются только инициалы. Задача заключается в преобразовании имён и отчеств в буквы инициалов.
На рис. 2.66 показан пример решения такой задачи. Следует обратить внимание, что имеются скрытые столбцы, в которых содержатся результаты промежуточных вычислений.
Алгоритм решения задачи может быть таким:
Строковые функции, которые понадобятся для решения данной задачи, описаны в таблице ниже.
| Название, аргументы | Назначение |
|---|---|
| len(str) | Вычисляет длину (количество символов) для строки str. |
| find(str1;str2;start) | Определяет позицию (номер символа), с которой начинается подстрока str1 в строке str2, начиная с позиции start. Если аргумент start не указан, поиск идёт с начала строки. |
| left(str;n) | Выделяет n символов с начала строки str. Если аргумент n не указан, функция возвращает первый символ строки. |
| right(str;n) | Выделяет n символов с конца строки str. Если аргумент n не указан, функция возвращает последний символ строки. |
| concatenate(str1;str2;...;strN) | Формирует одну строку из "фрагментов" – строк str1, str2, …, strN. |
В следующей таблице приведены формулы, использованные при решении задачи.
| Адрес ячейки, назначение | Формула |
|---|---|
| =len(A3) | B3: Длина исходной строки |
| =find(" ";A3) | C3: Позиция первого пробела (количество букв в фамилии) |
| =left(A3;C3) | D3: Строка "Фамилия" |
| =right(A3;B3-C3) | E3: Строка "Имя, отчество" |
| =len(E3) | F3: Длина строки"Имя, отчество" |
| =find(" ";E3) | G3: Позиция первого пробела в строке "Имя, отчество" (количество букв в имени) |
| =left(E3;G3) | H3: Строка "Имя" |
| =left(H3) | I3: Первая буква имени |
| =right(E3;F3-G3) | J3: Строка "Отчество" |
| =left(J3) | K3: Первая буква отчества |
| =concatenate(D3;" ";I3;".";K3;".") | L3: Результат |
Символьные (строковые) значения в формулах (в аргументах функций) следует указывать в кавычках.
В качестве других полезных строковых функций (по мнению автора) нужно отметить функции преобразования регистров lower() (переводит все символы строки в нижний регистр, т. е. в строчные буквы) и upper() (переводит все символы строки в верхний регистр, т. е. в прописные буквы), функцию mid(), которая позволяет вывести заданное количество символов строки, начиная с заданного символа, а также функцию value(), превращающую строку из символов-цифр в число.
Gnumeric обеспечивает базовые возможности по работе с матрицами. Список функций приведён в таблице ниже. Все эти функции в качестве аргументов используют диапазоны ячеек, в которые записаны элементы матриц.
| Название, аргументы | Назначение |
|---|---|
| transpose(matrix) | Выполняет транспонирование матрицы matrix (строки становятся столбцами и наоборот). Функция находиться в категории "Поиск". |
| mdeterm(matrix) | Вычисляет определитель квадратной матрицы. |
| minverse(matrix) | Вычисляет матрицу, обратную по отношению к исходной, при условии, что определитель не равен нулю. |
| mmult(matrix1;matrix2) | Вычисляет матричное произведение. Результирующая матрица имеет количество строк как в matrix1 и столбцов как в matrix2. |
Чтобы в результате операций с матрицами получить тоже матрицу (если это нужно), в "Помощнике по формулам" нужно установить режим "Ввести как функцию массива" (рис. 2.67).
Пример вычислений с матрицами показан на рис. 2.68.
В Gnumeric отсутствует функция для создания единичной матрицы, но такая матрица легко может быть получена умножением произвольной матрицы на обратную ей, что и проиллюстрировано на рис. 2.68 (здесь -0 указывает на то, что в результате погрешностей вычислений в связи с ограниченной точностью представления получилось вещественное отрицательное число с очень маленьким модулем).
(рис 2.67) Создание формулы в режиме работы с матрицами
(рис 2.68) Пример матриц и результатов операций с ними
При запуске Gnumeric получаем достаточно стандартный вид приложения электронной таблицы (ЭТ), рис. 2.1.
Документ ЭТ состоит из листов, каждый лист электронной таблицы может иметь переменное число строк и столбцов (см. далее главу "Управление листами"), а количество листов может быть более 256 (как уже упоминалось во "Введении", сведений об ограничении количества листов ЭТ автору обнаружить не удалось).
(рис 2.1) Общий вид окна ЭТ Gnumeric
(рис 2.2) Простой вид диалога открытия файла
Для открытия файла можно использовать пиктограмму "Открыть файл" в верхней панели инструментов (с изображением "папки"), команду главного меню "Файл/Открыть" или комбинацию клавиш <CTRL>+<O> (буква O – от слова "Open"). В результате появится GTK-диалог открытия файла (рис. 2.2). Поскольку подобные диалоги присутствуют во многих кросс-платформенных GTK-приложениях (в частности, в GIMP и в Inkscape), рассмотрим некоторые особенности этого диалога.
Самая левая кнопка в верхней части диалогового окна "отвечает" за ввод или отображение имени открываемого файла. Если она нажата, в диалоге появляется текстовое поле ввода "Расположение:" (см. рис. 2.3), которое даёт возможность сразу ввести полное имя нужного файла.
Также в верхней части диалога расположена строка указания пути к нужному файлу, причём каждому каталогу в этом пути соответствует кнопка. Если первая кнопка в пути – кнопка со стрелкой, это означает, что путь строится относительно "домашних" каталогов (точка монтирования /home в POSIX-системах). Если на эту кнопку со стрелкой нажать, увидим абсолютный путь от начала дерева каталогов ("Файловая система" или точка монтирования /).
(рис 2.3) Диалог открытия файла со строкой ввода имени и просмотром содержимого каталога
Основная часть диалогового окна состоит из двух панелей. Левая панель – "Места" – состоит из трёх секций. Верхняя секция позволяет выбрать для открытия один из документов, с которым недавно работали ("Недавние документы").
Средняя секция показывает стандартный набор каталогов, в которых могут находиться пользовательские файлы. В этот стандартный набор входят домашний каталог пользователя, "Рабочий стол" пользователя, а также начало дерева каталогов ("Файловая система" или точка монтирования /).
В нижнюю секцию пользователь может добавлять свои часто используемые каталоги, выбрав их в правой панели и нажав кнопку "Добавить". Это работает для каталогов, отмеченных курсором (подсветкой), за исключением каталога Desktop ("Рабочий стол").
Важно заметить, что для перехода в новое "место" (в левой панели диалога) требуется одиночный щелчок левой кнопкой мыши, а для открытия каталога в правой панели диалога нужен двойной щелчок левой кнопки мыши.
По мере продвижения "вглубь" иерархии каталогов файловой системы в верхней части диалога увеличивается количество кнопок, показывающих путь к открываемому файлу.
(рис 2.4) Диалог открытия файла с дополнительными возможностями выбора типа файла и кодировки
Нажатие на кнопку "Advanced" ("Расширенный") открывает ещё две настройки в этом диалоге (рис. 2.4) – возможность выбора типа файла и возможность выбора кодовой страницы (кодировки) для текстовых файлов.
Пусть в выбранном каталоге находится текстовый файл формата CSV (Comma Separated Values – текст, разделённый запятыми). Тогда после выбора соответствующего типа и кодовой страницы и нажатия на кнопку "Открыть" файл окажется импортированным в лист ЭТ, причём Gnumeric правильно распознает текст и числа (рис. 2.5). Дело в том, что ЭТ автоматически выравнивает текст по левому краю ячеек, а числа – по правому. Более подробно форматирование данных в ячейках будет рассматриваться в главе "Управление ячейками".
Для сохранения результатов работы используются кнопка с пиктограммой "диска" или "дискеты" ("Сохранить текущую книгу"), либо команда главного меню "Файл/Сохранить", либо комбинация клавиш <CTRL>+<S> (буква S – от "Save"). Для изменения имени (включая расположение) или типа файла используется команда "Файл/Сохранить как..." или комбинация клавиш <SHIFT>+<CTRL>+<S>. При любом варианте вызова команды сохранения в первый раз открывается диалог сохранения файла (рис. 2.6). При последующих сохранениях он не открывается, если не выбрана команда "Сохранить как...".
О не сохранённых изменениях в файле свидетельствует символ "звёздочка" (*) в строке заголовка окна перед именем файла (в нашем примере получилось бы *d1.csv).
Щелчок левой кнопкой мыши по треугольничку слева от надписи "Просмотреть другие папки" переключает полный и краткий варианты диалога сохранения файла. В кратком варианте можно задать или изменить имя и тип файла, а в полном – ещё и выбрать каталог для сохранения.
(рис 2.5) Результат импорта текстового файла
(рис 2.6) Диалог сохранения файла (полный вариант)
Импорт файлов (открытие, загрузка) возможен из следующих известных форматов:
Экспорт файлов (сохранение, выгрузка) возможен в следующие распространённые форматы:
При экспорте в текстовый файл также возможно указание кодировки выходного файла, а также символа-разделителя и варианта окончания строки (в стиле UNIX, MacOS или Windows).
При экспорте в PDF экспортируются все листы с колонтитулами, форматированием и диаграммами (если они есть). Таким образом, экспорт в PDF равносилен печати всего документа в файл.
Для изменения названий (имён) листов ЭТ можно использовать контекстное меню (рис. 2.7), вызываемое щелчком правой кнопкой мыши по "ярлычку" листа. Выбор пункта "Управление листами..." вызывает диалог управления свойствами листов (рис. 2.8).
В принципе, все операции с листами, которые можно делать с помощью контекстного меню, выполняются в этом диалоге. Лист выбирается щелчком левой кнопкой мыши по соответствующей строчке в списке листов. Для изменения имени листа щёлкаем левой кнопкой мыши в столбце "Новое название" для выбранного листа, пишем нужный текст и нажимаем кнопку "Apply Name Changes" ("Изменить имя"). После этой операции новое имя листа переместится в столбец "Текущее название". Для изменения порядка следования листов перемещаем выбранный лист вверх или вниз по списку соответствующими кнопками справа. Кнопка "Insert" ("Вставить") вставляет новый лист перед выбранным, а кнопка "Append" ("Добавить" или "Присоединить") вставляет новый лист после последнего имеющегося. Кнопка "Удалить" позволяет удалить выбранный лист с возможностью восстановления случайно удалённого.
(рис 2.7) Контекстное меню листа ЭТ
(рис 2.8) Диалог управления листами в Gnumeric
Для изменения цвета "ярлычка" листа и названия листа служат кнопки "Цвет заливки" и "Цвет символов". Нажатие на часть кнопки со "стрелочкой" справа от пиктограммы открывает палитру цветов (рис. 2.9), из которой можно выбрать цвет из типового набора. Клеточки в нижнем ряду заполняются "пользовательскими" цветами. Для установки цвета, не входящего в типовой набор (пользовательского) нужно щёлкнуть по кнопке "Другой цвет..." в нижней части палитры и выбрать цвет с помощью GTK-диалога выбора цвета (рис. 2.10).
(рис 2.9) Типовой набор цветов в Gnumeric
(рис 2.10) GTK-диалог выбора цвета
GTK-диалог выбора цвета предоставляет несколько возможностей формирования цвета объекта. Во-первых, можно установить точное значение цвета в координатах HSV (Hue-Saturation-Value или "Тон-Насыщенность-Яркость", диапазон изменения значений компонента "Тон" от 0 до 360, остальных – от 0 до 100) или RGB (Red-Green-Blue или "Красный-Зелёный-Синий", диапазон изменения компонентов от 0 до 255). Во-вторых, можно установить HTML-эквивалент цвета в шестнадцатеричном выражении (поле "Наименование цвета"). В-третьих, цвет можно выбрать "на глаз" (визуально), вращая цветной треугольник "протягиванием" чёрного отрезка по цветному кольцу, а затем "перетаскивая" белый кружок внутри треугольника. Наконец, кнопка с изображением пипетки под цветным кольцом даёт возможность выбрать цвет произвольной точки экрана. При любом способе выбора все варианты "цветовых координат" изменяются согласованно, а под цветным кругом показываются образцы текущего и нового цвета объекта.
(рис 2.11) Результат управления листами
При изменении цвета "ярлычка" листа ЭТ разумно также изменить цвет названия листа, чтобы фон и текст были достаточно контрастны.
На рис. 2.11 показаны результаты изменения цвета фона, имени и порядка следования листов ЭТ.
Менять порядок листов можно простым "перетаскиванием" ярлычка, при этом появится вертикальная стрелка, указывающая на новую позицию перемещаемого листа.
Листы ЭТ в Gnumeric имеют дополнительные атрибуты защиты, видимости и направления (в диалоге управления листами на рис. 2.8 – столбцы слева от имени листа). Изменяются эти атрибуты простым щелчком левой кнопкой мыши в соответствующем столбце диалога.
Установка атрибута защиты предотвращает изменение данных в ячейках листа, снятие атрибута видимости позволяет скрыть лист в окне ЭТ и убрать его "ярлычок" (при этом в диалоге управления листами отображаются все листы). Изменение атрибута "Направление" меняет порядок столбцов таблицы с направления "слева направо" на направление "справа налево" (первый столбец оказывается справа), что может быть полезным при использовании соответствующих систем письменности.
Дополнительные возможности управления внешним видом листов ЭТ можно получить с помощью вложенного меню "Формат/Лист" через пункт "Формат" главного меню (рис. 2.12).
Назначение режимов, которые включаются и отключаются в этом вложенном меню, достаточно очевидно. Нужно только заметить, что режим "Использовать нотацию R1C1" приводит к изменению порядка формирования адреса. Если в стандартном варианте сначала указывается имя столбца (например, A), а потом номер строки (например, 3) и получается адрес типа A3, то в режиме адресации R1C1 сначала указывается номер строки (Row), а затем – номер столбца (Column) и тот же адрес будет выглядеть как R3C1.
Вернёмся к диалогу управления листами (рис. 2.8) и включим режим показа дополнительных свойств ("Показать дополнительный свойства листа"). В поле свойств листа диалога появятся новые столбцы "Напр.", "Строки" и "Столбцы" (рис. 2.13).
(рис 2.12) Вложенное меню "Формат/Лист"
(рис 2.13) Показ дополнительных свойств в диалоге управления листами ЭТ
Дело в том, что Gnumeric позволяет изменять максимальные значения количества строк и столбцов для листов ЭТ, в том числе и отдельно для каждого листа. Для этого используется вызов диалога "Изменить размер..." из контекстного меню листа ЭТ (рис. 2.14).
Перемещая ползунки, можно изменять количество строк и столбцов на листах ЭТ. При выключенном режиме "Применить изменение ко всем листам" новые настройки будут применены только к выбранному листу (выбирается тот лист, на ярлычке которого было вызвано контекстное меню).
Минимальное количество строк и столбцов, как видно из рис. 2.14 – 128, максимальное количество столбцов на листе – 16386, а строк – более 16 миллионов. Установим для одного из листов значения по минимуму, для другого – по максимуму и посмотрим на результат в диалоге управления листами (рис. 2.15).
(рис 2.14) Диалог изменения количествастрок и столбцов листа ЭТ
(рис 2.15) Листы с различным количеством строк и столбцов в Gnumeric
Для адресации ячеек может использоваться как нотация "A1", так и нотация "R1C1". Адрес текущей (активной) ячейки отображается над левым верхним углом таблицы.
Адреса ячеек используются как операнды в формулах. Использование в формулах конкретных значений является нежелательным (за редким исключением).
В ячейках могут содержаться числа, текстовые данные или календарные даты. Числа и даты автоматически выравниваются по правой границе столбца, а текст – по левой. Таким образом, можно всегда распознать ошибку ввода.
Для чисел десятичным разделителем является точка. Даты в "европейском" варианте лучше вводить с использованием символа "/" (например, 12/05/2007).
Если необходимо, чтобы числовые данные интерпретировались как текст (например, почтовые индексы или ИНН), то начинать ввод следует с символа "апостроф" – "'". Например, чтобы ввести почтовый индекс 143921, нужно набрать "'143921".
Ввод данных в ячейки листа таблицы осуществляется путем набора символов (букв) и цифр с последующим нажатием <ENTER>. При этом указатель активной ячейки ("рамка") смещается вниз на одну строку. Для перехода от ячейки к ячейке в любом "направлении" по листу ЭТ можно использовать клавиши управления курсором, а также щелчок левой кнопкой мыши по целевой ячейке. Переход на другую ячейку автоматически означает окончание ввода данных в текущей ячейке.
Курсор в ЭТ Gnumeric может быть четырёх видов. Варианты курсора и ситуации, при которых они используются, описаны в таблице ниже.
| Вид курсора | Ситуация, назначение |
|---|---|
|
Стандартный курсор (режим позиционирования). В таком режиме происходит указание ячеек (щелчком левой кнопкой мыши) или выделение диапазона (блока) ячеек (протаскиванием мыши с нажатой левой кнопкой). |
|
Курсор перемещения (при подведении курсора к рамке активной ячейки или выделенного диапазона). В таком режиме протаскивание мыши перемещает выбранный объект по листу ЭТ. |
|
Курсор заполнения (может иметь вид "косого" белого крестика), появляется при позиционировании мыши в нижнем правом углу активной ячейки или выделенного диапазона (блока). При выделении диапазона ячеек в таком режиме происходит копирование содержимого активной ячейки (данных или формулы) на выделенный диапазон. |
|
Курсор редактирования. Появляется при переходе в режим редактирования содержимого ячейки. |
(рис 2.16) Контекстное меню ячейки ЭТ
Для редактирования содержимого ячейки следует нажать клавишу <F2> или сделать двойной щелчок мышки по ячейке, после чего редактируемая ячейка изменяет цвет фона. При редактировании возможны стандартные операции редактирования текста, а также изменение начертания и/или цвета отдельных символов текста в ячейке (при этом символы нужно выделять в строке ввода, а не в ячейке, см. пример рис. 2.22). Завершается редактирование нажатием клавиши <ENTER> или щелчком мыши в какой-либо другой ячейке. Для отмены изменений и возврата к предыдущему состоянию следует нажать клавишу <ESC>.
Управление представлением данных в ячейках осуществляется настройками форматов ячеек ("Формат/Ячейки..." в главном меню или вызов диалога "Изменить формат 1 ячейки..." через контекстное меню ячейки (рис. 2.16) с помощью правой кнопки мыши).
Изменения формата касаются активной ячейки или выделенного блока ячеек (включая строки и столбцы полностью). Диалог "Формат ячеек" (рис. 2.17) имеет несколько вкладок, обеспечивающих различные настройки параметров ячейки или диапазона ячеек.
(рис 2.17) Настройка представления чисел вячейках ЭТ
(рис 2.18) Настройка проверки правильности ввода данных в ячейку ЭТ
Возможность проверки правильности данных при вводе – очень полезная возможность. Пусть, например, в некотором диапазоне ячеек необходим ввод только целых чисел, причем пустая ячейка (отсутствие данных) не является ошибкой. С помощью настройки параметров проверки ("Формат ячеек: Проверка") обеспечим блокировку ошибочного ввода и появление предупреждения об ошибке (рис. 2.18).
Теперь, если в какую-то ячейку из этого диапазона попытаться ввести что-либо неправильное, появится соответствующее предупреждение (рис. 2.19).
Контекстное меню ячейки (рис. 2.16) позволяет установить для ячейки комментарий. Комментарий — это какой-то текст, поясняющий назначение или особенности содержимого ячейки. При вызове команды "Добавить комментарий" из контекстного меню появляется диалог редактирования комментария (рис. 2.20). В области ввода можно писать произвольный текст, который станет комментарием к ячейке после нажатия на кнопку "ОК".
При создании комментария можно управлять атрибутами текста. Включение/выключение режима "Переносить окно свойств" в описываемой версии не даёт какого-либо эффекта.
(рис 2.19) Сообщение об ошибке при неправильном вводе
(рис 2.20) Создание комментария для ячейки ЭТ
Важно, что для каждого комментария устанавливается автор (текущий пользователь). При изменении комментария другим пользователем сохранится история изменений.
Ячейки с комментарием обозначаются красным треугольничком в верхнем правом углу ячейки (рис. 2.21). Комментарий можно увидеть, если навести курсор на этот треугольничек. Для удаления комментария следует вызвать диалог "Удалить 1 комментарий" с помощью контекстного меню ячейки.
Интересной особенностью ЭТ Gnumeric является возможность управления атрибутами отдельных символов текста в ячейке. Для этого в режиме редактирования (по нажатию <F2> или двойному клику по ячейке) следует выделить нужные символы и использовать кнопки управления атрибутами ("Полужирный", "Наклонный", "Подчеркнутый" и "Передний план") в панели инструментов Gnumeric. Пример такой модификации текста показан на рис. 2.22.
(рис 2.21) Просмотр комментария ячейки
Операции изменения формата данных, цвета переднего плана, фона и обрамления, а также копирования, перемещения и вставки можно проводить не только с одиночными ячейками, но и с группами (блоками) ячеек. Для этого требуется выделить группу ячеек. Для выделения нескольких соседних ячеек используется "протаскивание" мыши с нажатой левой кнопкой от верхнего левого до правого нижнего угла выделяемого блока или используются клавиши управления курсором ("стрелки") при нажатой клавише <SHIFT>. На рис. 2.23 показан вид области листа ЭТ с выделенным блоком ячеек.
Весь выделенный блок оказывается в общей "рамке", а фон всех выделенных ячеек меняет цвет, за исключением ячейки, которая была активной до выделения блока. Для всех выделенных ячеек с помощью контекстного меню и кнопок панели инструментов можно установить одинаковый формат данных, обрамление и цвет фона и текста, шрифт и другие параметры, за исключением комментария. Комментарий всё равно будет устанавливаться только для активной ячейки.
Для снятия выделения достаточно перейти в любую ячейку ЭТ щелчком левой кнопки мыши или с помощью клавиш-"стрелок".
(рис 2.22) Пример выделения отдельных символов в ячейке B4
(рис 2.23) Пример выделения непрерывного блока ячеек
Если необходимо выделить несколько ячеек, расположенных в произвольных позициях листа, используется щелчок левой кнопкой мыши по нужным ячейкам при нажатой клавише <CTRL>. Пример такого выделения показан на рис. 2.24.
Возможно также проводить операции со всеми ячейками в строке (строках) или столбце (столбцах) листа ЭТ. Для выделения всей строки нужно щелкнуть левой кнопкой мыши по номеру строки (рис. 2.25).
Контекстное меню строки, получаемое после щелчка правой кнопкой мыши по номеру строки (рис. 2.26) отличается от контекстного меню ячейки. Для строк появляются операции "Вставить 1 строку" и "Удалить 1 строку".
(рис 2.24) Выделение нескольких поизвольных ячеек
(рис 2.25) Результат выделения строки
(рис 2.26) Контекстное меню строки
(рис 2.27) Диалог настройки высоты строки
(рис 2.28) Выделение соседних строк
Диалог "Строка/высота..." (рис. 2.27) позволяет задавать высоту строки в точках экрана (pixels), причём она автоматически пересчитывается в типографские пунктах (пт, 1 пт = 1/72 дюйма) с учётом разрешения экрана.
Явное указание высоты строки может потребоваться для точного форматирования документа, однако в большинстве случаев высота устанавливается автоматически исходя из размера шрифта и параметров форматирования.
При выборе операции "Вставить 1 строку" новая строка появляется перед выделенной (выделенная строка смещается "вниз", её номер увеличивается на 1). При выборе операции "Скрыть" строка становится невидимой и её номер пропадает (нарушается непрерывность номеров). Чтобы снова увидеть скрытую строку, нужно выделить соседние строки и из контекстного меню выбрать операцию "Показать" (предлагается поэкспериментировать самостоятельно).
Для выделения нескольких соседних строк можно "протащить" мышь по номерам строк или выделить одну строку, а затем использовать "стрелки" при нажатой клавише <SHIFT> (рис. 2.28).
(рис 2.29) Выделение нескольких произвольных строк
(рис 2.30) Выделение столбца
Тем же самым способом, каким выделяются произвольные ячейки, выделяются и произвольные строки (рис. 2.29), только щелкать мышью нужно по номерам строк.
Аналогично строкам, столбцы выделяются целиком при щелчке левой кнопкой по имени столбца (букве), как показано на рис. 2.30.
Контекстное меню столбца показано на рис. 2.31.
Диалог настройки ширины столбца (рис. 2.32) позволяет точно устанавливать значение этого параметра. Однако, в отличие от высоты строк, ширина столбцов не изменяется автоматически.
Пусть в ячейки ЭТ введён текст, как показано на рис. 2.33. Содержимое ячейки B2 "не помещается" в видимую ширину столбца (на самом деле текст никуда не пропадает, в чём легко убедиться в режиме редактирования).
Для подбора нужной ширины столбца можно воспользоваться диалогом настройки ширины столбца (рис. 2.32), можно "растянуть" столбец за правую границу области имени столбца (прямоугольник, в котором написана буква), а можно по этой правой границе дважды щелкнуть левой кнопкой мыши, вызвав таким образом операцию "Автоподбор ширины". Результат автоподбора ширины для столбца B показан на рис. 2.34.
(рис 2.31) Контекстное меню столбца
(рис 2.32) Диалог настройки ширины столбца
(рис 2.33) Пример недостаточной ширины столбца
(рис 2.34) Результат автоподбора ширины
(рис 2.35) Автоподбор ширины соседних столбцов
(рис 2.36) Выделение нескольких произвольных столбцов
Если выделить соседние столбцы, то двойной щелчок по правой границе последнего выделенного столбца приведёт к выполнению автоподбора ширины для всех выделенных столбцов (рис. 2.35, сравните с рис. 2.34).
При выделении нескольких произвольных столбцов с помощью клавиши <CTRL> (рис. 2.36) для "пустых" столбцов автоподбор ширины не действует.
После упражнений с выделением отдельных ячеек, строк и столбцов логично задаться вопросом: а нет ли возможности выделить сразу все ячейки листа? Оказывается, такая возможность тоже существует. Для выделения всех ячеек листа нужно щёлкнуть левой кнопкой мыши по "кнопке" над столбиком с номерами строк (самый верхний левый угол таблицы, над номером 1 и левее буквы A). Это равносильно выбору команды главного меню "Правка/Выделение/Все".
(рис 2.37) Выделение всех ячеек листа
Основное применение всех электронных таблиц – "программирование без программирования", то есть создание таких связей между ячейками, чтобы на основе исходных данных в одних ячейках получался конечный результат в других ячейках. Поэтому для дальнейшей работы с таблицей будут нужны исходные данные. Они могут быть введены вручную, экспортированы из внешнего файла (например, из текстового) или смоделированы (сгенерированы) с помощью соответствующих инструментов пакета.
Рассмотрим возможности Gnumeric по генерации исходных данных. Исходные данные, как правило, располагаются в непрерывном блоке ячеек (столбце или строке) и затем используются для каких-либо вычислений. Для определенности будем располагать исходные данные в столбце.
Для создания последовательностей исходных данных (векторов, таблиц) можно использовать возможности заполнения блоков данных, используя пункт главного меню "Данные" (рис. 2.38).
Вложенное меню "Заполнить" как раз и предоставляет различные способы создания блоков ячеек с исходными данными (рис. 2.39).
Здесь мы рассмотрим только три варианта – генерация последовательностей ("Автозаполнение"), создание прогрессий ("Серии...") и создание выборок последовательностей случайных чисел с различными видами функций распределения ("Случайные числа.../Некоррелированные...").
(рис 2.38) Пункт "Данные" главного меню Gnumeric
(рис 2.39) Вложенно меню "Заполнить"
Для начала сформируем последовательность натуральных чисел от 0 до 25. Для этого введем в ячейку A3 значение "0", а в A4 – значение "1" (без кавычек!), затем выделим ячейки от A3 до A28 (всего 25 ячеек) путем "протаскивания" мыши с нажатой левой кнопкой. Далее вызовем функцию автозаполнения ряда: "Данные/Заполнить/Автозаполнение" и пронаблюдаем результат.
Далее сформируем последовательность из 25 чисел, кратных 7, начиная с 0. По аналогии с предыдущим случаем, введем в ячейку B3 значение "0", а в ячейку B4 – значение "7", после чего повторим операции выделения и вызова функции автозаполнения и посмотрим на результат.
Такую же операцию можно провести с датами. Пусть известно, что 21 мая 2007 года и 28 мая этого же года были понедельниками. На какие даты будут приходиться понедельники в течение последующих 23 недель? (Аналогично предыдущему случаю, только вместо чисел – даты). Соответственно, вводим в ячейку C3 значение "21/05/07", в C4 – "28/05/07", выделяем снова 25 ячеек и вызываем автозаполнение. Затем устанавливаем формат дат как "dd/mm/yy" с помощью диалога "Формат ячеек". Результат показан на рис. 2.40.
(рис 2.40) Результаты использования автозаполнения
Нужно отметить, что с другими (не числовыми) данными функция "Автозаполнение" не работает. Таким образом, функция "Автозаполнение" продолжает зависимость, определенную по первым двум ячейкам диапазона, на все ячейки выделенного диапазона.
Следующая возможность автоматической генерации исходных данных – создание серий. Она может потребоваться, если заранее неизвестно количество ячеек для автозаполнения, или если это количество велико, а также если зависимость значения следующей ячейки от предыдущей не является линейной.
Рассмотрим примеры генерации серий чисел со значениями от 1 до 100. Диалог заполнения ячеек сериями значений ("Данные/Заполнить/Серии...") содержит три вкладки (рис. 2.41).
(рис 2.41) Настройка серий для заполнения ячеек данными
(рис 2.42) Результат заполнения сериями
Сформируем серию значений, линейно изменяющихся по столбцу (арифметическую прогрессию). Устанавливаем соответствующие параметры на вкладке "Серии", указываем в качестве начального значения число 1, приращение 10 и конечное значение 100 (рис. 2.41). Больше никаких дополнительных настроек не требуется. После нажатия на <ENTER> (или использования кнопки "ОК" в диалоге) наблюдаем результат (рис. 2.42). Видно, что последнее значение серии меньше указанного максимального значения. Однако если провести эксперимент с начальным значением, равным 0, то последним значением в серии будет 100. Таким образом, можно сделать очевидный вывод, что конечное значение в серии никогда не превышает указанного максимального значения.
Теперь в соседних столбцах создадим серии с ростом в 2 и в 3 раза при тех же начальных и конечных значениях (именно поэтому в качестве начального значения выбрана 1, а не 0). Коэффициент роста указывается как приращение. Результаты показаны на рис. 2.42).
С заполнением диапазона ячеек серией дат читателям предлагается разобраться состоятельно.
Следующая интересная возможность генерации исходных данных – заполнение диапазона ячеек случайными числами с различными функциями плотности распределения. Всего предлагается около 30 вариантов. Для использования этой возможности полезно иметь выделенный диапазон ячеек (блок). Можно реализовать двумерное поле случайных чисел, если выделить не часть строки или столбца. а прямоугольный диапазон и сделать соответствующие настройки в диалоге "Генерация случайных чисел" ("Данные/Заполнить/Случайные числа.../Некоррелированные...", рис. 2.43).
Диалог "Генерация случайных чисел" также имеет три вкладки.
(рис 2.43) Диалог генерации случайных чисел (однородное распределение)
В качестве примера создадим три вектора из 100 случайных чисел с разными распределениями: равномерным в диапазоне от 0 до 10 (как на рис. 2.43), нормальным со средним значением 5 и стандартным отклонением 2, а также с распределением $$x^2$$ со значением параметра $$v=2$$. Для полученных результатов установим формат с 4-мя десятичными знаками (рис. 2.44)
Рассмотренные возможности по генерации исходных данных будут полезны при дальнейшей работе с пакетом Gnumeric.
(рис 2.44) Три вектора случайных чисел
Формулы в электронных таблицах предназначены для вычислений значений в ячейках таблицы на основе данных, записанных в другие ячейки. Результатом работы формулы может быть число, дата, текст или отсутствие данных (т.е. после вычислений можно получать пустые ячейки). Формула записывается в той ячейке, в которой должен быть результат. Ввод формулы начинается с нажатия на символ "=" на клавиатуре, после чего вводятся числа, адреса ячеек и знаки операций. Например, формула "=3+5" даст всегда результат "8", а формула "=A3/12" будет давать различные результаты при изменении значения в ячейке A3.
Таким образом, при изменении содержимого ячеек, адреса которых используются в формулах, результаты пересчитываются автоматически. Эта особенность является ключевой для всех электронных таблиц. Из-за этого свойства крайне не рекомендуется использовать в формулах конкретные значения, если только они не являются неотъемлемой частью правил вычисления (например, если площадь круга вычисляется в евклидовой геометрии как $$\pi R^2$$, то двойка может использоваться в формуле вычисления площади круга, а вот конкретное значение радиуса – нет).
(рис 2.45) Исходные данные для задачи о заработках
Электронная таблица с хорошо составленными формулами представляет из себя мощное средство решения вычислительных задач и, по сути, является программой, выдающей результаты на основе содержимого ячеек с исходными данными.
Теперь рассмотрим некоторые примеры использования формул и адресации данных в таблицах Gnumeric.
Начнем вычисления в ЭТ с простой задачи. Пусть имеется список из 7 человек, для каждого из которых известны фамилия, инициалы, должность, оклад за день работы и число отработанных дней. Требуется вычислить заработок каждого лица.
Формируем таблицу, начиная с ячейки A3, в соответствии с рис. 2.45. Для исправления ошибок в ячейках электронной таблицы используется режим редактирования строки ввода, который включается клавишей <F2>. Завершение редактирования обеспечивается клавишами <ENTER> (с сохранением изменений) или <ESC> (без сохранения изменений).
Если при вводе информации ширина ячейки представляется недостаточной, ее можно скорректировать после завершения ввода всех данных. Как уже упоминалось выше, всегда есть возможность подобрать ширину столбца автоматически двойным щелчком левой кнопкой мыши на правой границе столбца в строке имен столбцов.
Для вычисления заработка нужно просто перемножить попарно числа из третьей (столбец C) и четвертой (столбец D) колонок. Результаты вычислений должны быть в пятой колонке (столбец E). С учетом возможностей ЭТ, формулу (т.е. правила) для вычислений можно написать один раз, а потом скопировать. Формулу надо писать там, где должен появиться первый результат (в нашем примере – в ячейке E4, под заголовком "Заработок"). Переводим указатель активной ячейки в клетку E4 и нажимаем клавишу "=" (указание на начало ввода формулы). После этого щелкаем левой кнопкой мыши по ячейке, в которой записан оклад за день (C4), нажимаем на клавиатуре знак операции (умножение – "*") и щелкаем левой кнопкой мыши по ячейке с количеством отработанных дней (D4), после чего нажимаем <ENTER>. В ячейке E4 появляется результат (число 1100), а переместив указатель активной ячейки на E4, в строке ввода увидим формулу =C4*D4
(рис 2.46) Результат расчёта заработка
Теперь скопируем эту формулу в оставшиеся ячейки. Поместив указатель активной ячейки на E4, наведем мышь на нижний правый угол указателя активной ячейки (там есть маленький черный квадратик). Двойной щелчок левой кнопкой мыши в этой позиции приведет к автоматическому копированию формулы до конца блока исходных данных. Результаты показаны на рис. 2.46.
Если изменить какие-то числа в столбцах C и D, то числа в столбце E будут автоматически пересчитываться.
Перемещая указатель активной ячейки по столбцу E заметим, что адреса ячеек в расчетной формуле изменяются. Это происходит потому, что в нашей формуле использованы относительные адреса ячеек. Формула просто перемножает содержимое ячеек, находящихся слева от ячейки с результатом. Таким образом, формула "запомнила" взаимное расположение ячеек с данными и с результатом и при копировании это взаимное расположение сохраняется. Это очень полезное свойство ЭТ, избавляющее от необходимости писать одну и ту же формулу много раз.
Если в какой-либо ячейке расчетного столбца (столбца "Заработок") перейти в режим редактирования (<F2>), то можно увидеть формулу. При перемещении текстового курсора по формуле будут подсвечиваться ячейки, содержащие данные для формулы (рис. 2.47).
На следующем этапе посчитаем налог на доходы физических лиц, который будет начислен на рассчитанные ранее значения заработка. Пусть ставка налога фиксирована и составляет 13%. Тогда наша таблица дополняется в соответствии с рис. 2.48 (здесь и в следующих иллюстрациях к этому примеру первый столбец "обрезан").
(рис 2.47) Просмотр формулы в режиме редактирования
(рис 2.48) Таблица для вычислений с параметром
Сумму налога легко сосчитать по правилу "Сумма налога = заработок*ставка_налога". Указав соответствующие адреса ячеек, в ячейке F4 записываем формулу =E4*D1 и копируем ее во все оставшиеся ячейки. При этом получается неожиданный результат (рис. 2.49).
В этом случае использование относительной адресации привело к ошибке – запомнив взаимное расположение ячеек результата и исходных данных (первого заработка в списке и ставки налога) программа ЭТ повторяет это взаимное расположение для остальных строк списка (в чем можно убедиться, войдя в режим редактирования, как показано на рис. 2.49). Чтобы не создавать дополнительный столбец с одним и тем же значением ставки налога, в соответствующей формуле надо использовать абсолютный адрес ячейки, содержащей параметр (в данном случае – значение ставки налога). Для указания абсолютного адреса к букве столбца или номеру строки добавляется префикс $ и формула для расчета суммы налога приобретает вид =E4*$D$1 (для добавления символов $ при редактировании формулы можно использовать клавишу <F4>). Отредактировав формулу в ячейке F4, копируем ее снова в оставшиеся ячейки и получаем правильный результат (рис. 2.50).
(рис 2.49) Неправильный результат вычислений с параметром
(рис 2.50) Правильные вычисления с параметром
В режиме редактирования теперь видно, что во всех ячейках при вычислении суммы налога происходит обращение к ячейке, содержащей ставку налога, независимо от строки таблицы (вспомним, что одинаковое количество знаков после запятой во всех ячейках можно получить, изменив формат представления чисел).
Итак, абсолютный адрес указывает программе ЭТ, что нужно всегда обращаться к одной и той же ячейке (если поставлено два префикса $), строке (если $ поставлен перед номером строки) или столбцу (если $ – перед буквой столбца). Использование абсолютных адресов позволяет работать с условно-постоянными величинами (ставка налога, курс валюты, текущая дата и пр.), причем их значения заносятся в таблицу только один раз, что экономит время и место.
(рис 2.51) Автосуммирование по столбцу
Нужно заметить, что формулы позволяют связывать между собой не только ячейки в пределах одного листа, но и ячейки на разных листах документа. В результате можно строить "трехмерные" электронные таблицы.
Полезная и часто используемая возможность электронных таблиц – автосуммирование. Для использования этой возможности нужно установить указатель активной ячейки в позицию, в которой нужно получить результат и нажать на панели инструментов Gnumeric кнопку ∑ (знак суммы). Программа автоматически определит непрерывный блок ячеек выше или слева от целевой и предложит вариант функции для вычисления результата. Обратите внимание, что диапазон ячеек указывается с использованием двоеточия, как показано на рис. 2.51.
В качестве упражнения посчитайте аналогичным образом сумму всех налогов.
Для проведения математических, тригонометрических, экономических или инженерных вычислений четырех действий арифметики явно недостаточно. Поэтому все электронные таблицы имеют большое число встроенных функций, которые могут включаться в состав формул. Каждая функция имеет имя и список аргументов, которые помещаются в скобки сразу за именем функции. Могут быть функции с пустым множеством аргументов, такие как pi() или today(), а могут быть функции с неограниченным количеством аргументов (такие как sum(...) или average(...)), однако для большинства функций количество аргументов фиксировано.
(рис 2.52) Диалог выбора функции (селектор функций)
В зависимости от назначения и синтаксиса функции, аргументом функции может быть число, текст, дата или логическое (булево) выражение . Чаще всего в качестве аргумента (или в составе аргумента) используются адреса ячеек. В случае нескольких аргументов разделителем аргументов является точка с запятой (символ ";"). Если в качестве аргумента используется текст, то он должен быть заключен в кавычки (символ "''"). Кроме того, в аргументах функций могут использоваться другие функции и арифметические выражения (например, возможны формулы типа "=2*sin(3*A3*pi()/4)").
Для вызова селектора встроенных функций используется либо главное меню ("Вставка/Функция"), либо кнопка $$f(x)$$ в панели инструментов. После этого появляется диалог выбора функции. На рис. 2.52 показан пример для логической функции if(), которая подробнее будет рассмотрена ниже.
При выборе функции сначала выбирается категория функций (в верхней части диалога), а затем – конкретная функция (в средней части диалога). В нижней части диалога приводится объяснение назначения и структуры функции. После нажатия на кнопку "OK" появляется диалог определения аргументов выбранной функции (помощник по формулам, рис. 2.53). Для приведенной в примере функции if() нужно определить три аргумента.
(рис 2.53) Составление формулы
Для определения аргументов нужно щелкнуть мышкой в поле ввода (в столбце "Функция/Аргумент", указать ячейку со значением аргумента (можно щелкнуть мышкой по нужной ячейке) и при необходимости дописать остальное. Завершается определение аргумента нажатием на <ENTER>, после чего с помощью щелчка мышкой переходим к определению следующего аргумента. По мере определения аргументов функция дописывается.
Окончательный результат построения функции if() показан на рис. 2.54. В данном случае проверяется ячейка C3. Если в ней содержится ненулевое значение, должно быть выведено сообщение "Не ноль!", в противном случай – сообщение "Ноль"
Кнопка $$f(x)$$ в этом диалоге позволяет вставить функцию в качестве аргумента формируемой функции. При необходимости можно редактировать ссылки типа "Лист1!B4", превращая их в ссылки типа "B4".
После окончательного определения всех аргументов и нажатия на кнопку "ОК" можно наблюдать результат работы функции. При необходимости можно редактировать функцию либо в строке ввода по нажатию клавиши <F2>, либо снова вызвать диалог определения аргументов функции, использовав кнопку f(x), когда ячейка с результатом функции является активной.
Далее кратко рассмотрим несколько основных групп функций, а потом перейдем к конкретными примерам.
(рис 2.54) Функция if() саргументами
Gnumeric содержит около 80 встроенных математических функций. Описывать все нет необходимости (использование и особенности функций типа abs(), sin(), tan() или pi() достаточно очевидны). Приведем здесь краткое описание некоторых более редких математических функций.
| Название, аргументы | Назначение |
|---|---|
| atan2(b1;b2) | Вычисляет арктангенс отношения $$b2/b1$$ без учета знаков аргументов. |
| beta(a;b) | Вычисляет бета-функцию (интеграл Эйлера $$I$$ рода) для положительных целых аргументов. |
| betaln(a;b) | Вычисляет натуральный логарифм бета-функции для положительных целых аргументов. |
| combin(n;k) | Вычисляет количество уникальных комбинаций из $$n$$ по $$k$$. |
| expm1(x) | Вычисляет $$exp(x)-1$$ с высокой точностью. |
| factdouble(m) | Вычисляет двойной факториал положительного числа $$m$$, т.е. $$m!!$$. Если m не целое, дробная часть обрезается. |
| hypot(a;b;...) | Вычисляет квадратный корень из суммы квадратов аргументов. |
| multinomial(x1;x2;...) | Вычисляет отношение факториала суммы аргументов к произведению их факториалов. |
| quotient(a;b) | Вычисляет целую часть результата деления $$a$$ на $$b$$. |
| roman(m;тип) | Преобразует натуральное число в "римскую" форму записи. Необязательный аргумент "тип" определяет вариант нотации (классический или краткий). |
| seriessum(X;n;m;коэффициенты) | Вычисляет сумму степенного ряда вида $$a*X^n+b*X^{n+m}+c*X^{n+2_m}+d*X^{n+3_m...}$$, т.е. $$X$$ – основание степени, $$n$$ – начальное значение показателя степени, $$m$$- инкремент показателя степени, а коэффициенты $$a,b,c...$$ - коэффициенты при каждом члене ряда, записанные в блоке ячеек таблицы. |
| sqrtpi(m) | Вычисляет квадратный корень из m*pi() (sqrt(m*pi()). |
| sumsq(x1;x2;...) | Вычисляет сумму квадратов аргументов. |
| sumx2my2(вектор1;вектор2) | Вычисляет сумму разностей квадратов соответствующих элементов векторов. Вектор1 и вектор2 – блоки ячеек одинаковой длины. |
| sumx2py2(вектор1;вектор2) | Вычисляет сумму сумм квадратов соответствующих элементов векторов. |
| sumxmy2( вектор1;вектор2) | Вычисляет сумму квадратов разностей соответствующих элементов векторов. |
Логических функций вообще немного, поэтому рассмотрим их все. Каждая логическая функция может быть аргументом другой, если это позволено синтаксисом.
| Название, аргументы | Назначение |
|---|---|
| false() | Аргументов не имеет. Всегда выдает логическое значение "ЛОЖЬ" (FALSE). |
| true() | Аргументов не имеет. Всегда выдает логическое значение "ИСТИНА" (TRUE). |
| and(условие1;условие2;...) | Имеет неограниченное количество аргументов (более одного). Выдает логическое значение "ИСТИНА" (TRUE), если выполняются все условия, приведенные в аргументах. Пример синтаксиса: $$and(c1>0,b4=3,d2<b4)$$, где $$c1, b4$$ и $$d2$$ – адреса ячеек. |
| or(условие1;условие2;...) | Имеет неограниченное количество аргументов (более одного). Выдает логическое значение "ИСТИНА" (TRUE), если выполняются хотя бы одно из условий, приведенных в аргументах. Синтаксис аналогичен функции and(). |
| not(условие) | Аргументом является условие или результат работы логической функции (логическое TRUE или FALSE). Выдает логическое значение "ИСТИНА" (TRUE), если условие не выполняется или аргумент установлен в FALSE. |
| xor(условие1;условие2;...) | Имеет неограниченное количество аргументов (более одного). Выдает логическое значение "ИСТИНА" (TRUE), если выполняется нечетное количество условий (исключающее ИЛИ). |
| if(условие;действие1;действие2) | Проверяет условие, и если оно выполняется, производится действие1 (возможна проверка еще какого-то условия, выполнение арифметических операций или вычисление по формуле с функциями, а также вывод текста). В противном случае выполняется действие2 (с теми же особенностями). |
Важным свойством функции if() является возможность использования результата работы одной функции if() в качестве аргумента другой функции if(). Однако количество таких вложений ограничивается максимально допустимой длиной текста в ячейке, а также здравым смыслом (формулы с количеством проверок условий более 7-8 крайне трудно анализировать в случае ошибок). Также в качестве условий в if() могут использоваться любые синтаксически допустимые комбинации логических функций.
Функций работы с комплексными числами в Gnumeric насчитывается более сорока, поэтому здесь рассмотрим только основные (и наиболее интересные с точки зрения автора).
| Название, аргументы | Назначение |
|---|---|
| complex(x1;x2;символ) | Формирует комплексное число из двух вещественных. Третий необязательный аргумент (символ) позволяет изменить обозначение мнимой единицы. Если он не указан, будет сформировано комплексное число вида $$x1+x2i$$. |
| imabs(complex) | Вычисляет модуль комплексного числа. Например, если в ячейке E3 записано комплексное число $$5+3i$$ (как результат функции complex()), то imabs(E3) выдаст 5,83095. |
| imargument(complex) | Вычисляет аргумент комплексного числа (показатель степени при экспоненциальном представлении). Для примера $$5+3i$$ выдаст значение 0,54042. |
| imreal(complex) | Выдает вещественную часть комплексного числа. |
| imaginary(complex) | Выдает мнимую часть комплексного числа. |
| imconjugate(complex) | Вычисляет комплексно сопряженное число. |
| imdiv(complex1;complex2) | Вычисляет целую часть результата деления двух комплексных чисел. |
| iminv(complex) | Выполняет преобразование $$1/z$$. |
| impower(complex;power) | Возводит комплексное число в степень, которая тоже может быть комплексным числом. |
| improduct(complex1;complex2;...) | Вычисляет произведение комплексных чисел (обычная операция умножения не работает!). |
Далее рассмотрим использование логических и математических функций, а также функций комплексного переменного на классическом примере вычисления корней квадратного уравнения с произвольными коэффициентами.
(рис 2.55) Решение квадратного уравнения
Итак, заданы три коэффициента $$A, B$$ и $$C$$ квадратного уравнения вида
$$A \cdot x^2+B \cdot x+C=0$$Требуется вычислить корни $$x_1$$ и $$x_2$$, которые в общем случае могут быть комплексными. Перед вычислением корней вычислим дискриминант $$D$$:
$$D=B^2-4 \cdot A \cdot C$$И затем воспользуемся формулой для вычисления корней:
$$\frac {x_{1,2}=-B+- \sqrt{D}}{2 \cdot A}$$Однако при отрицательном дискриминанте корни будут комплексными. Такое комплексное число будет иметь вещественную часть $$(-B/2A)$$ и мнимую часть (с точностью до знака)
$$\Im_{1,2}= +-\frac { \sqrt{D}}{2 \cdot A}$$В то же время для неотрицательных значений дискриминанта будут работать обычные правила вычисления корней (в соответствии с формулой (2.3)). Таким образом, в формуле для вычисления корня должна присутствовать проверка дискриминанта на отрицательность, и при отрицательном дискриминанте должно быть сформировано комплексное число. При неотрицательном дискриминанте используются обычные функции и арифметические действия.
После столь долгих рассуждений пора показать таблицу и формулы для вычислений (рис. 2.55).
Формулы для вычислений приведены ниже:
| Адрес ячейки, назначение | Формула |
|---|---|
| C6: Дискриминант | =C4^2-4*C3*C5 |
| C7: Корень X1 | =if(C6<0;complex(-C4/(2*C3);sqrt(abs(C6)));(-C4+sqrt(C6))/(2*C3)) |
| C8: Корень X2 | =if(C6<0;complex(-C4/(2*C3),-sqrt(abs(C6)));(-C4-sqrt(C6)/(2*C3)) |
Календарные функции в Gnumeric находятся в категории "Дата/Время", всего таких функций более 30. Основными являются функции "разложения" даты на составляющие – выделения из даты номера дня в месяце (функция day()), номера месяца в году (month()) и года (year()) – и функция обратного преобразования date(year(),month(),day()), которая конструирует данные типа "дата" из номера года, месяца и дня.
Также часто используются функции автоматического определения дня недели (weekday()), текущей даты (today()) и текущего момента времени (now()).
Имеются также интересные (и полезные) функции преобразования дат – date2unix() и обратная ей unix2date(). Они выполняют преобразование даты в количество секунд, прошедших с начала "эры UNIX", и наоборот ("эра UNIX" началась в полночь 1 января 1970 года).
Для знакомства с основными календарными функциями рассмотрим следующую задачу. Дан список лиц, для которых известны фамилии, пол и даты рождения. Определить:
Для этой задачи воспользуемся исходными данными (списком фамилий) из задачи про доходы и налоги. Пол установим в соответствии с фамилиями, даты рождения введем произвольно (как уже упоминалось, удобно вводить дату в виде 23/11/88, а программа приведет ее в нужный вид).
Для получения решения по пункту "a" необходимо проделать некоторые промежуточные вычисления. Сначала нужно для каждого лица сформировать дату рождения в текущем году на основании дня и месяца рождения, а также номера текущего года. Тогда первая формула (в ячейке D4 на рис. 2.56) будет иметь вид:
=DATE(YEAR(TODAY());MONTH(C4);DAY(C4))
Очевидно, что функция today() просто выдает текущую дату, а функция date() формирует дату из номера года, номера месяца и номера дня в месяце, определяемых соответственно, с помощью функций year(), month() и day(). Естественно, при желании вместо результатов работы функций можно использовать адреса ячеек, содержащих числа или просто числа. Соответственно, формула для окончательного результата по пункту "а" (в ячейке E4) будет иметь вид:
=IF(OR(WEEKDAY(D4;2)=6;WEEKDAY(D4:2)=7);"УРА!";"УВЫ...")
где функция weekday() вычисляет день недели для даты, указанной в первом аргументе. Второй аргумент функции weekday() определяет правила нумерации дней недели. В данном случае первый день недели соответствует понедельнику.
Для обработки всего списка просто копируем эти формулы вниз. Для получения решения по пункту "b" воспользуемся функцией вычисления разности дат – datedif(). Эта функция имеет три аргумента – начальную дату, конечную дату и строку-параметр, задающую единицы измерения разности. Значения параметра "y", "m" или "d" позволяют найти разность дат соответственно, в полных годах, полных месяцах и в днях. С учетом того, что возраст определяется в полных годах, запишем формулу для возраста в следующем виде:
=datedif(C4;TODAY();"y")
Для получения решения по пункту "c" снова нужно формировать даты, используя значения параметров возраста выхода на пенсию. Эти возрасты на момент написания книги составляют 60 лет для мужчин и 55 для женщин, однако они могут в любой момент измениться, поэтому конкретные числа в формулу записывать не будем. Дата выхода на пенсию формируется с использованием условия проверки пола. Итак, получаем формулу:
=IF(B4="М";DATE(YEAR(C4)+$G$1,MONTH(C4);DAY(C4));DATE(YEAR(C4)+$G$2;MONTH(C4);DAY(C4)))
Результирующая таблица показана на рис. 2.56.
Последний столбец (дата выхода на пенсию) приведен в формате "dd/mm/yyyy" для иллюстрации корректного выполнения вычислений.
(рис 2.56) Пример вычисление с датами
В тех случаях, когда использование функции if() становится неудобным по причине большого количества вложений или большой длины формулы, а также для повышения эффективности вычислений, в электронных таблицах используются функции поиска соответствий. К таким функциям относятся lookup(), vlookup(), hlookup(), match() и index(), находящиеся в категории "Поиск".
Понимание работы этих функций может сначала вызывать некоторые затруднения, однако они (вместе с if()) обеспечивают мощные средства обработки данных, поэтому их освоение стоит затраченного времени.
Функции lookup(), vlookup() и hlookup() в качестве одного из аргументов используют так называемые "ассоциативные массивы" (справочники). Ассоциативный массив – это структура данных, оформленная в виде таблицы, первый столбик которой содержит "ключи" – данные, которые участвуют в формировании условий. В следующих столбиках ассоциативного массива содержатся значения, соответствующие ключам. Таким образом, по значению ключа можно однозначно получить какие-то другие данные (в языках программирования такие структуры называются "хэш-таблицы").
В программах ЭТ ассоциативные массивы реализуются как блоки ячеек (справочные таблицы), содержащие минимум два столбца. Первый столбец содержит ключи, второй – значения, соответствующие ключам.
Для примера рассмотрим следующую задачу. В ралли участвуют гонщики на автомобилях, для которых известны марки и расход топлива в литрах на 100 км. Дан список, в котором указаны фамилии гонщиков и марки автомобилей. Известна протяженность трассы гонок в километрах L. Определить расход топлива для каждого участника гонок.
(рис 2.57) Справочник для задачи о ралли
(рис 2.58) Исходные данные задачи о ралли
Для решения задачи составим справочную таблицу для 7-ми разных марок а/м, и расположим ее в ячейках F3:G10 (рис. 2.57). Полезно соблюдать алфавитный порядок текстовых значений в первом столбике справочной таблицы.
Данные (список участников и марки их а/м) запишем в ячейки A3:B10 (рис. 2.58). Длину трассы L, которая является параметром, запишем в ячейку C1. Для вычисления полного расхода топлива используем формулу с функцией LOOKUP().
Формула в ячейке C4 будет выглядеть следующим образом.
(рис 2.59) Решение задачи о ралли
=LOOKUP(B4;$F$4:$F$10;$G$4:$G$10)*$C$1/100
Функция LOOKUP() считывает содержание ячейки, указанной в первом аргументе, ищет это значение в диапазоне ячеек (столбце), указанном во втором аргументе и выдаёт соответствие этому значению из диапазона ячеек (столбца), указанном в третьем аргументе. Таким образом, для получения результата по названию а/м находим расход топлива на 100 км и умножаем это значение на количество сотен километров. Принципиально важно указывать абсолютные адреса блоков ячеек справочной таблицы.
Итоговая таблица показана на рис. 2.59.
Ограничение функции lookup() – только один столбец соответствий. Более "мощной" является функция vlookup(), в которой второй аргумент определяет весь блок ячеек, содержащих ассоциативный массив (справочник), а третий аргумент указывает, в каком столбце ассоциативного массива нужно искать соответствие ключу. Четвёртый (необязательный) аргумент определяет порядок сортировки первого ("ключевого") столбца справочника. Если он не указан или равен 1 (логическая ИСТИНА), то первый столбец ассоциативного массива для функции vlookup() должен содержать числа, отсортированные по возрастанию, или текст, отсортированный в алфавитном порядке. Если значения в первом столбце не отсортированы, то четвертый аргумент должен быть установлен в 0. Еще одним большим достоинством функции vlookup() является возможность работы с диапазонами значений ключа.
Для примера рассмотрим вычисление суммы годового налога при прогрессивной налоговой шкале. Пусть при годовом доходе до 10000 у.е. ставка налога составляет 12%, до 30000 у.е. – 20%, до 50000 у.е. – 25% и при большем доходе – 35%. Для создания таблицы данных используем фамилии из предыдущего примера, а суммы годового дохода запишем такие, чтобы можно было реализовать все варианты ставок налога (рис. 2.60). Таблицу данных разместим в диапазоне A3:B10.
(рис 2.60) Исходные данные задачи о налогах
(рис 2.61) Справочник к задаче о налогах
Справочную таблицу размещаем в диапазоне E3:F7 (рис. 2.611), формула в ячейке C4 с использованием функции vlookup() выглядит следующим образом:
=VLOOKUP(B4;$E$4:$F$7;2)*B4
Во втором столбце ассоциативного массива находим ставку налога, соответствующую доходу, а затем получаем сумму налога, умножая ставку налога на величину дохода (рис 2.62).
(рис 2.62) Решение задачи о налогах
Важно, что VLOOKUP() выбирает значения, соответствующие нижней границе диапазона значений ключей, и используется минимальное значение ключа в интервале.
Четвертый аргумент функции VLOOKUP() также влияет и на возможность интервального просмотра. Если этот аргумент имеет значение ИСТИНА (1) или опущен, то интервальный просмотр работает, как описано выше. Если этот аргумент имеет значение ЛОЖЬ (0), то функция VLOOKUP() ищет точное соответствие. Если таковое не найдено, то возвращается значение ошибки #N/A (#Н/Д – "нет данных"). Таким образом, для использования возможности работы с интервалами значений в VLOOKUP() первый столбец справочной таблицы (ассоциативного массива) обязательно должен быть отсортирован по возрастанию.
Функция hLOOKUP() работает аналогично VLOOKUP(), только порядок следования "ключей" – не сверху вниз, а слева направо.
Теперь рассмотрим формат функций match() и index().
Функция MATCH(искомое_значение; искомый_массив; тип_сопоставления) — находит позицию (порядковый номер) искомого значения в одномерном массиве. Значение аргумента "тип сопоставления" – (-1, 0 или 1) – зависит от того, упорядочен ли массив (-1 — массив упорядочен по убыванию, находится место наименьшего значения, которое больше или равно искомому, 0 — массив может быть неупорядоченным, находится место первого значения, равного исходному, 1 — массив упорядочен по возрастанию, находится место наибольшего значения, которое меньше или равно искомому).
Функция INDEX(массив; номер_строки; номер_столбца) — находит значение элемента, находящегося в заданном массиве на пересечении заданных строки и столбца.
Далее рассмотрим пример. Пусть на основе эксперимента получена следующая зависимость посещаемости дискотеки от входной платы:
| Входная плата, у.е. | 1 | 1,5 | 2 | 2,5 | 3 | 3,5 | 5 |
| Количество посетителей | 200 | 175 | 160 | 140 | 124 | 110 | 70 |
(рис 2.63) Решение задачи о дискотеке
Определить оптимальную входную плату.
Очевидно, что оптимуму соответствует максимальная выручка. Соответственно, решение задачи состоит в том, чтобы подсчитать выручку в каждом случае, найти максимальную и написать формулу, показывающую входную плату, соответствующую максимальной выручке. То есть нужно определить номер столбика, в котором получается максимальная выручка и вывести значение входной платы.
Определяем выручку в каждом случае (умножив входную плату на количество посетителей), затем функцией MAX() находим наибольшую выручку. После этого функцией MATCH() определяем, на каком месте в массиве она находится и функцией INDEX() смотрим, какая входная плата находится на этом месте (рис. 2.63). Рядом с ячейками с результатами приведены формулы для получения этих результатов (используется функция expression()).
В этом примере участвует функция max(), которая относится к категории статистических функций, к знакомству с которыми теперь и перейдем.
Основные статистические функции – это min(), max() и average(), вычисляющие, соответственно, минимальное, максимальное и среднее значение для набора аргументов. Аргументы могут быть либо перечислены через точку с запятой (если данные находятся в несмежных ячейках), либо в качестве аргумента может быть использован диапазон ячеек. Также к статистическим функциям можно отнести функции sumif() и countif(), которые формально находятся в Gnumeric среди математических функций, а также функцию rank(), определяющее "рейтинг" какого-то значения в списке аналогичных значений.
В качестве примера рассмотрим следующую задачу.
Дан список участников соревнования среди студентов по бегу на 100 метров и метанию мяча. В таблице (рис. 2.64) указаны пол (юноша или девушка) и результаты. Определить места каждого участника в каждом виде соревнований, минимальный, максимальный и средний результаты в каждом виде соревнований, на сколько юноши (в среднем) метают мяч дальше, чем девушки.
Сразу под списком в соответствующих столбцах подсчитываем максимальный, минимальный и средний результаты (по столбцу С =Max(C2:C16), =Min(C2:C16), =AVERAGE(C2:C16) и аналогично по столбцу D).
Затем подсчитываем количество юношей и количество девушек. В ячейку B22 вносим формулу
=COUNTIF(B2:B16;"юноша"),
а в ячейку B23 формулу
=COUNTIF(B2:B16;"девушка")
В ячейки C22 и C23 записываем формулы для подсчета суммы результатов по метанию для юношей и девушек соответственно
=SUMIF(B2:B16;"юноша",D2:D16)
=SUMIF(B2:B16;"девушка",D2:D16)
После чего подсчитываем среднее значение в ячейках D22 и D23, разделив сумму результатов на количество участников в каждой группе. Затем подсчитываем разницу средних значений (рис. 2.64).
Для иллюстрации использования функции rank() рассмотрим следующую задачу: На основе данных из задачи о соревнованиях распределить места в соревнованиях по бегу среди юношей, а также определить, сколько человек показали результаты ниже среднего (т.е. время больше среднего).
Для распределения участников по местам как раз и потребуется функция rank(), а кроме того потребуется COUNTIF() для вычисления с условием. Однако условие для COUNTIF() обязательно должно быть текстом, поэтому при формировании условия с вычисляемыми данными целесообразно использовать текстовую функцию concatenate(). Результат использования этих функций показан на рис. 2.65.
(рис 2.64) Решение задачи о соревнованиях
Формула для определения места участника соревнований (ячейка C2) будет выглядеть следующим образом:
=rank(B2;$B$2:$B$7;1)
Абсолютные адреса диапазона использованы для обеспечения возможности копирования формулы. Третий аргумент установлен в "1", что обеспечивает обратный порядок распределения мест, т.е. чем меньше значение, тем лучше место (первое место – минимальный результат). Для обеспечения прямого порядка распределения мест (первое место – максимальный результат) третий аргумент нужно установить в "0" или не указывать.
(рис 2.65) Задача о распределении мест
Среднее время определяется с помощью функции average(), а условие для подсчета аутсайдеров формируется с помощью текстовой функции concatenate(), которая "сцепляет" строки для формирования одного значения строкового типа. В данном случае в ячейке B10 записана формула
=concatenate(">";B9).
Подсчет аутсайдеров выполняется по формуле (ячейка B11)
=countif(B2:B7;B10).
К статистическим функциям относятся также функции count() (подсчет количества числовых значений в диапазоне ячеек) и counta() (подсчет количества не пустых ячеек в диапазоне). Кроме того, большое число статистических функций вычисляют статистические параметры различных распределений и обеспечивают генерацию случайных чисел с заданными параметрами распределений.
Одна из типовых задач в офисной работе – изменения формы представления списков людей или организаций. Избежать трудоёмкой работы по переписыванию текста из одного вида в другой помогают функции работы с текстом (строковые функции).
Почему-то подобные задачи кажутся очень сложными, поэтому покажем пример решения и рассмотрим минимальный набор функций, необходимых для получения результата.
(рис 2.66) Пример преобразования списка
Пусть имеется список лиц с полными фамилиями, именами и отчествами (например, список группы слушателей каких-нибудь курсов). Для составления журнала полные имена и отчества не требуются, а требуются только инициалы. Задача заключается в преобразовании имён и отчеств в буквы инициалов.
На рис. 2.66 показан пример решения такой задачи. Следует обратить внимание, что имеются скрытые столбцы, в которых содержатся результаты промежуточных вычислений.
Алгоритм решения задачи может быть таким:
Строковые функции, которые понадобятся для решения данной задачи, описаны в таблице ниже.
| Название, аргументы | Назначение |
|---|---|
| len(str) | Вычисляет длину (количество символов) для строки str. |
| find(str1;str2;start) | Определяет позицию (номер символа), с которой начинается подстрока str1 в строке str2, начиная с позиции start. Если аргумент start не указан, поиск идёт с начала строки. |
| left(str;n) | Выделяет n символов с начала строки str. Если аргумент n не указан, функция возвращает первый символ строки. |
| right(str;n) | Выделяет n символов с конца строки str. Если аргумент n не указан, функция возвращает последний символ строки. |
| concatenate(str1;str2;...;strN) | Формирует одну строку из "фрагментов" – строк str1, str2, …, strN. |
В следующей таблице приведены формулы, использованные при решении задачи.
| Адрес ячейки, назначение | Формула |
|---|---|
| =len(A3) | B3: Длина исходной строки |
| =find(" ";A3) | C3: Позиция первого пробела (количество букв в фамилии) |
| =left(A3;C3) | D3: Строка "Фамилия" |
| =right(A3;B3-C3) | E3: Строка "Имя, отчество" |
| =len(E3) | F3: Длина строки"Имя, отчество" |
| =find(" ";E3) | G3: Позиция первого пробела в строке "Имя, отчество" (количество букв в имени) |
| =left(E3;G3) | H3: Строка "Имя" |
| =left(H3) | I3: Первая буква имени |
| =right(E3;F3-G3) | J3: Строка "Отчество" |
| =left(J3) | K3: Первая буква отчества |
| =concatenate(D3;" ";I3;".";K3;".") | L3: Результат |
Символьные (строковые) значения в формулах (в аргументах функций) следует указывать в кавычках.
В качестве других полезных строковых функций (по мнению автора) нужно отметить функции преобразования регистров lower() (переводит все символы строки в нижний регистр, т. е. в строчные буквы) и upper() (переводит все символы строки в верхний регистр, т. е. в прописные буквы), функцию mid(), которая позволяет вывести заданное количество символов строки, начиная с заданного символа, а также функцию value(), превращающую строку из символов-цифр в число.
Gnumeric обеспечивает базовые возможности по работе с матрицами. Список функций приведён в таблице ниже. Все эти функции в качестве аргументов используют диапазоны ячеек, в которые записаны элементы матриц.
| Название, аргументы | Назначение |
|---|---|
| transpose(matrix) | Выполняет транспонирование матрицы matrix (строки становятся столбцами и наоборот). Функция находиться в категории "Поиск". |
| mdeterm(matrix) | Вычисляет определитель квадратной матрицы. |
| minverse(matrix) | Вычисляет матрицу, обратную по отношению к исходной, при условии, что определитель не равен нулю. |
| mmult(matrix1;matrix2) | Вычисляет матричное произведение. Результирующая матрица имеет количество строк как в matrix1 и столбцов как в matrix2. |
Чтобы в результате операций с матрицами получить тоже матрицу (если это нужно), в "Помощнике по формулам" нужно установить режим "Ввести как функцию массива" (рис. 2.67).
Пример вычислений с матрицами показан на рис. 2.68.
В Gnumeric отсутствует функция для создания единичной матрицы, но такая матрица легко может быть получена умножением произвольной матрицы на обратную ей, что и проиллюстрировано на рис. 2.68 (здесь -0 указывает на то, что в результате погрешностей вычислений в связи с ограниченной точностью представления получилось вещественное отрицательное число с очень маленьким модулем).
(рис 2.67) Создание формулы в режиме работы с матрицами
(рис 2.68) Пример матриц и результатов операций с ними
Для получения официальных документов о завершении программы дополнительного профессионального образования (удостоверения о повышении квалификации, дипломов о профессиональной переподготовке и MBA) необходимо предоставить:
Внимание! Вы можете не заказывать доставку бумажной версии официального документы, а скачать его в электронном виде и распечатать самостоятельно. Информация о выданном документе в течение 1 месяца загружается в Федеральную информационную систему «Федеральный реестр сведений о документах об образовании и (или) о квалификации, документах об обучении» - ФИС ФРДО.
Доступ на новый сайт осуществляется с использованием адреса электронной почты, который был указан вами при регистрации на "старом". Мы постарались перенести все ваши данные с прежнего ресурса, однако не исключена вероятность потери части информации.
При возникновении проблемы со входом, воспользуйтесь функцией сброса пароля
Если вы обнаружите несоответствия, пожалуйста, сообщите нам.