В OpenOffice Calc реализована возможность вычисления нескольких простых показателей без применения функций и формул.

К таким показателям относятся:

Сумма . Вычисляется только сумма чисел диапазона или ячеек. Аналогична функции SUM .
Количество . Подсчитывается количество ячеек диапазона, содержащих только числовые данные (кроме текста).
Количество2 . Подсчитывается количество ячеек диапазона, содержащих любые значения (текст, числа, даты и т.д.). Похож на показатель «Количество», но с более расширенным функционалом.
Максимум . Показывает максимальное значение в диапазоне.
Минимум . Показывает минимальное значение в диапазоне.
Среднее значение . Вычисляется среднее арифметическое значение в диапазоне.

Вычисление данных показателей в OpenOffice Calc без применения функций и формул происходит следующим образом.

В табличном редакторе Calc бесплатного офисного приложения OpenOffice, выделяется диапазон, в котором нужно подсчитать один из перечисленных ранее показателей.
Курсор мыши наводится на панель в правой половине нижней границы рабочего листа, и нажимается правая кнопка мыши. Из списка со всеми показателями выбирается один показатель.

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

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

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

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

Практикум

Рассчитаем в Calc, без применения функций и формул, сумму, минимальный, максимальный и средний ежедневный приход денежных средств, а также количество дней отчетного периода, используя условные исходные данные по приходу денежных средств в кассу розничного магазина за период с 1 по 15 ноября 2012 года.

Выделим диапазон C2:C16.
Наведем курсор на нижнюю правую панель под рабочим листом. В появившемся списке выберем «сумма». В результате появится суммой диапазона 12 843 191.

Минимум , максимум , среднее значение вычисляются аналогично.

Для знакомства с функциональными возможностями показателя «количество2» выделим диапазон B2:B16. Результат будет равен 15. В отличие от показателя «количество», показатель «количество2» более расширен по функционалу и подсчитывает количество любых значений в ячейках диапазона.

Дата	День недели	Оборот, руб.
01.11.2012	Четверг	689 585
02.11.2012	Пятница	897 123
03.11.2012	Суббота	854 789
04.11.2012	Воскресенье	956 871
05.11.2012	Понедельник	874 965
06.11.2012	Вторник	788 924
07.11.2012	Среда	789 634
08.11.2012	Четверг	875 614
09.11.2012	Пятница	879 321
10.11.2012	Суббота	879 219
11.11.2012	Воскресенье	951 478
12.11.2012	Понедельник	889 743
13.11.2012	Вторник	873 215
14.11.2012	Среда	858 147
15.11.2012	Четверг	784 563

Логические функции

Функция в OpenOffice Calc	Описание	Аналог в MS Excel
AND(логическое_значение_1;	Возвращает значение "ИСТИНА", если все аргументы	И(логическое_значение_1;
логическое_значение_2;...логи-	имеют значение "ИСТИНА". Если хотя бы один эле-	логич._значение_2;...логи-
ческое_значение_30)	мент имеет значение "ЛОЖЬ", возвращается значение	ческое_значение_30)
	ЛОЖЬ.
	Условия для



	столбца или строки. Значение "ИСТИНА" возвращает-
	ся, если значения всех ячеек диапазона возвращают ре-
	зультат "ИСТИНА".
	Возвращает логическое значение "ЛОЖЬ". Функция
	FALSE() не требует аргументов.
IF(условие; тогда_значение;	Задает логическую проверку, которую требуется вы-	ЕСЛИ(условие; тогда_значение;
иначе_значение)	полнить. Условие - любое значение или выражение, ко-	иначе_значение)
	торое может иметь значение "ИСТИНА" или "ЛОЖЬ".
	Тогда_значение (необязательно) - значение, которое
	возвращается, если условие выполняется (т. е. возвра-
	щает значение "ИСТИНА"). Иначе_значение (необяза-
	тельно) - значение, которое возвращается, если условие
	не выполняется (т. е. возвращает значение "ЛОЖЬ").
NOT(логическое_значение)	Возвращает обратное логическое значение. Логиче-	НЕ(логическое_значение)
	ское_значение - любое значение для преобразования.
OR(логическое_значение_1; ло-	Возвращает значение "ИСТИНА", если хотя бы один из	ИЛИ(логическое_значение_1; ло-
	аргументов имеет значение "ИСТИНА". Возвращает	гическое_значение_2;...логиче-
ское_значение_30)	значение "ЛОЖЬ", если все аргументы имеют значение	ское_значение_30)
	ЛОЖЬ. Логическое_значение_1; логическое_зна-
	чение_2;...логическое_значение_30 - условия для
	проверки. Все условия могут иметь значение "ИСТИ-
	НА" или "ЛОЖЬ". Если в качестве параметра указан
	диапазон, функция использует значение из текущего
	столбца или строки диапазона.
	Функция TRUE() не требует аргументов и всегда
	возвращает логическое значение "ИСТИНА".

Информационные функции

Функция в OpenOffice Calc	Описание	Аналог в MS Excel
ISERROR(значение)	Возвращает значение "ИСТИНА", если значение ссылается	ЕОШИБКА(значение)
	на любое значение ошибки. Значение - любое значение или
	выражение для проверки наличия ошибки.

Функции для баз данных

Функция в OpenOffice Calc	Описание	Аналог в MS Excel
DAVERAGE(база данных;	Возвращает среднее значение для всех ячеек (полей) во	ДСРЗНАЧ(база_данных; поле;
поле базы данных; крите-	всех строках (записях базы данных), которые соответству-	критерий)
рий поиска)	ют указанному критерию поиска. Поиск предусматривает
	поддержку регулярных выражений.
DCOUNT(база данных;		БСЧЁТ(база_данных; поле;
поле базы данных; крите-		критерий)
рий поиска)
	вает поддержку регулярных выражений. В качестве пара-
	метра Поле базы данных, можно ввести адрес ячейки для
	обозначения столбца или цифру 0 для обозначения всей
	базы данных. Этот параметр не должен быть пустым.
DCOUNTA(база данных;	Служит для подсчета в базе данных количества строк (за-	БСЧЁТА(база_данных; поле;
поле базы данных; крите-	писей), которые соответствуют выбранному критерию по-	критерий)
рий поиска)
	ния. Поиск предусматривает поддержку регулярных выра-

DGET(база данных; поле	Возвращает содержимое ячейки, которая соответствует	БИЗВЛЕЧЬ(база_данных; поле;
базы данных; критерий	указанному критерию поиска. В случае возникновения	критерий)
	ошибки функция возвращает либо #ЗНАЧ! для ненайден-
	ных строк, либо Ошибка:502, если найдено несколько яче-

DMAX(база данных; поле	Возвращает максимальное значение ячейки (поля) в базе	ДМАКС(база_данных; поле;
базы данных; критерий		критерий)

	регулярных выражений.
DMIN(база данных; поле	Возвращает минимальное значение ячейки (поля) в базе	ДМИН(база_данных; поле;
базы данных; критерий	данных (во всех записях), которая соответствует указанно-	критерий)
	му критерию поиска. Поиск предусматривает поддержку
	регулярных выражений.
DPRODUCT(база данных;	Перемножает все ячейки диапазона данных, содержимое	БДПРОИЗВЕД(база_данных; поле;
поле базы данных; крите-	которых соответствует критерию поиска. Поиск преду-	критерий)
рий поиска)	сматривает поддержку регулярных выражений.
DSUM(база данных; поле	Возвращает общую сумму для всех ячеек всех строк (запи-	БДСУММ(база_данных; поле;
базы данных; критерий	сей) в поле базы данных, содержимое которых соответ-	критерий)
	ствует указанному критерию поиска. Поиск предусматри-
	вает поддержку регулярных выражений.

Функции для массивов

Функция в OpenOffice Calc	Описание	Аналог в MS Excel
HLOOKUP(условия поис-		ГПР(условия поис-
ка; массив; индекс; сорти-	ленной области. Эта функция проверяет первую строку	ка; массив; индекс; сорти-
	массива на наличие определенного значения. Функция воз-
	вращает значение в строку массива того же столбца, ука-
	занную в индексе. Поиск предусматривает поддержку ре-
	гулярных выражений.
LOOKUP(условие_поис-	Возвращает содержимое ячейки или для диапазона из од-	ПРОСМОТР(условие_поис-
ка; вектор_просмотра;	ной строки или одного столбца, либо из массива. При необ-	ка; вектор_просмотра;
вектор_результата)	ходимости, присвоенное значение (с таким же индексом)	вектор_результата)
	возвращается в другой столбец и строку. В отличие от
	функций VLOOKUP и HLOOKUP, векторы просмотра и
	результата могут иметь разные позиции и могут быть не
	смежными. Кроме того, вектор просмотра для функции
	LOOKUP должен быть отсортирован; в противном случае
	поиск не даст нужных результатов. Поиск предусматривает
	поддержку регулярных выражений. Условие_поиска –
	отыскиваемое значение, которое можно ввести вручную

	отдельной строки или столбца для поиска. Вектор_ре-
	зультата - второй диапазон из одной строки или одного
	столбца, откуда извлекается результат функции. Функция
	возвращает ячейку вектора результата с тем же индексом,
	что и экземпляр, найденный в векторе просмотра.
MATCH(условие_поиска;	Возвращает относительную позицию элемента в заданном	ПОИСКПОЗ(условие_поиска;
массив; тип)	массиве. Функция возвращает позицию значения, найден-	массив; тип)
	ную в массиве, в виде числа. Условие_поиска - значение
	для поиска в массиве с одной строкой или одним столбцом.

	или столбец, либо часть одной строки или столбца. Тип -
	может принимать значения 1, 0 или -1. Это соответствует
	аналогичной функции в Microsoft Excel. Поиск предусмат-
	ривает поддержку регулярных выражений.
VLOOKUP(условие_по-		ВПР(условие_по-
иска; массив; индекс; по-	ячейки справа. Если заданное значение содержится в пер-	иска; массив; индекс; по-
рядок_сортировки)	вом столбце массива, эта функция возвращает значение в	рядок_сортировки)
	строку столбца массива, заданного параметром индекс.
	Поиск предусматривает поддержку регулярных выраже-
	ний. Условие_поиска - значение для поиска в первом

	чать хотя бы два столбца. Индекс - номер столбца в масси-
	ве, который содержит возвращаемое значение. Номер пер-
	вого столбца - 1. Порядок_сортировки (необязательно) - , 1956. Быков К.М., Курцин И.Т. ... Федеральная целевая программа книгоиздания России Издательская программа «Учебники и учебные пособия для педагогических училищ и колледжей» Руководитель программы Программа ... специально приспособленном для точного проведения опыта, контроля всех ... психодиагностику: Учеб. пособие для студентов средних педагогических учебных заведений / Под... школьном и дошкольном возрасте. - Петрозаводск , 1992; Матюгин И.Ю. Тактильная память... Методические указания для студентов по дисциплине «История России (1796-1855 гг.)» Методические указания ... Петрозаводский государственный университет Кафедра истории дореволюционной России Методические указания для студентов ... упомянутых источников для специального анализа, ... при Александре I. Учебное пособие к спецкурсу. Петрозаводск , 2002. Кандаурова... Учебная программа дисциплины анализ художественного текста в русском и зарубежном литературоведении Направление подготовки: 050100. 68 Педагогическое образование, магистратура Программа дисциплины Грамотно оформить специальный текст, подготовить... предмету для всех ... М. Православие и русская литература: учебное пособие для студентов духовных академий и семинарий. В... И. А. Есаулов. – Петрозаводск : Издательство Петрозаводского университета, 1995. – ...

Использование имен ячеек и диапазонов в формулах

Если в формуле используются имена ячеек иди диапазонов, вы можете набрать имя вместо адреса или выбрать имя из списка, чтобы OpenOffice Calc вставила его автоматически.

Задание:

Перейдите в ячейку F8.

В строку формул методом указания ячеек введите формулу (суммируйте итоговые значение окладов и надбавок) . Убедитесь, что вместо адресов ячеек отображаются их имена рис. 16.

Рис. 16

Перейдите в ячейку F9.

В строке формул наберите выражение =SUM(.

Рис. 17

Выберите команду «Вставкаð Названиеð Вставить». В диалоговом окне «Вставка имени», в котором перечислены все определенные в данной рабочей книге имена, выберите имя «Суммы» и нажмите кнопку «ОК» рис. 17.

Закройте скобку.

Рис. 18.

Нажмите клавишу .

Сохраните рабочую книгу.

Логические функции. Функция IF (ЕСЛИ)

Логические функции выполняют условное вычисление по формулам и позволяют осуществить реализацию простых алгоритмов принятия решений.

Функции IF (ЕСЛИ) возвращает одно значение, если заданное условие при вычислении дает значение TRUE (ИСТИНА), и другое значение, если FALSE (ЛОЖЬ). Функция IF (ЕСЛИ) используется при проверке условий для значений и формул.

Синтаксис функции:

IF (ЕСЛИ) (Лог_выражение; Значение_если_истина; Значение_если_ложь)

Предположим, что вам нужно подсчитать в рабочей таблице комиссионные по результатам продаж какого-либо товара. Если продавец продал продукции более, чем на $100 000, то ставка его комиссионных составляет 7,5%, если меньше, то 5%. Без использования функции вам пришлось бы создавать две разные формулы и правильно употреблять их для каждого значения объема продаж. Ниже приведена функция, которая позволяет рассчитать комиссионные с учетом суммы продаж.

IF (А1<100000; A1*0,05; A1*0,075), где

IF (ЕСЛИ) – функция, которая проверяет, выполняется ли условие, и возвращает одно значение, если оно выполняется, и другое значение, если нет;

А1<100000 – условие;

A1*0,05 – значение, которое возвращается, если условие выполняется;

A1*0,075 – значение, которое возвращается, если условие не выполняется.

Таким образом, если значение ячейки А1 (сумма продаж) будет меньше 100000 (условие выполняется) программа умножит значение ячейки А1 на 0,05 (ставка комиссионных), если же сумма продаж будет больше 100000 (условие не выполняется), программа умножит значение ячейки А1 на 0,075.

Задание:

Откройте окно Мастер функций, выберите категорию Логические функции.

На Листе3 создайте таблицу табл.3.

Таблица 3

Норма продаж	150 000,00
Ставка комиссионных	0,055
Ставка премиальных	0,075

Менеджеры	Продажи	Комиссионные
Орлов	149 823,00
Воробьев	162 023,00
Скворцов	209 123,00
Синицын	122 354,00
Грачев	83 351,00
Петухов	204 861,00
Ганиев	150 000,00
Самойлов	110 500,00
Дроздов	220 120,00
Кротов	170 450,00

Отформатируйте таблицу согласно рисунку

Рис. 19.

Вычислите комиссионные от продаж, если ставка комиссионных составляет 5,5%, ставка премиальных – 7,5%, норма продаж – 150 000р. Для этого выполните следующие действия.

Активизируйте ячейку С6 (комиссионные Орлова), вызовите Мастер функций, найдите в нем функцию IF (ЕСЛИ).

В поле Логическое выражение введите условие B6<В1 (продажи Орлова меньше нормы продаж).

В поле Значение если true (истина) введите выражение B6*B2 (если продажи Орлова меньше нормы продаж, то для расчета комиссионных необходимо величину продаж умножить на ставку комиссионных).

В поле Значение если false (ложь) введите выражение B6*B3 (если продажи Орлова больше нормы продаж, то для расчета комиссионных необходимо величину продаж умножить на ставку премиальных).

Проверьте правильность ввода аргументов рис.20

Рис. 20

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

Рис. 21

Сохраните рабочую книгу.

Статистические функции. Функция COUNTIF (СЧЕТЕСЛИ) (СЧЕТЕСЛИ)

К категории статистических функций отнесено огромное количество функций – 80, позволяющих выполнить разнообразные расчеты. Многие из этих функций достаточно специализированные, но некоторые из них полезны и для тех, кто мало знаком со статистикой.

Функция COUNTIF (СЧЕТЕСЛИ) подсчитывает количество ячеек внутри диапазона, удовлетворяющих заданному критерию.

Синтаксис функции: COUNTIF (СЧЕТЕСЛИ) (Диапазон; Критерий).

Диапазон - диапазон, в котором нужно подсчитать ячейки.

Критерий - критерий в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать. Например, критерий может быть выражен следующим образом: 32, "32", ">32", "яблоки".

Задание:

Откройте окно Мастер функций, выберите категорию Статистические.

Просмотрите список функций, ознакомьтесь с их описанием.

Дополните электронную таблицу согласно рис. 22.

Рис. 22.

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

Перейдите в ячейку Е7. Вызовите Мастер функций, найдите в нем функцию COUNTIF (СЧЕТЕСЛИ).

В поле Диапазон укажите диапазон ячеек В6:В15 (в этом диапазоне ведется поиск значений, превышающих 150 000).

В поле Критерий введите ячейку Е6 (в этой ячейке содержится условие, выполнение которого проверяется в диапазоне В6:В15).

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

Рис. 23.

Вычислите количество менеджеров, которые имеют объем продаж, равный норме или превышающих норму.

Рис. 24

Сравните полученные результаты с рисунком.

Сохраните рабочую книгу.

Математические функции. Функция SUMIF (СУММЕСЛИ)

В программу OpenOffice Calc включены 50 функций данной категории, что вполне достаточно для выполнения сложных математических расчетов.

Функция SUMIF (СУММЕСЛИ) суммирует ячейки, заданные критерием.

Синтаксис функции: SUMIF (СУММЕСЛИ)(Диапазон;Критерий;Диапазон_суммирования).

Диапазон - диапазон вычисляемых ячеек.

Критерий - критерий в форме числа, выражения или текста, определяющего суммируемые ячейки. Например, критерий может быть выражен как 32, "32", ">32", "яблоки".

Диапазон_суммирования - фактические ячейки для суммирования.

Ячейки в Диапазон_суммирования суммируются, только если соответствующие им ячейки в аргументе Диапазон удовлетворяют критерию. Если Диапазон_суммирования опущен, то суммируются ячейки в аргументе Диапазон.

Задание:

Откройте окно Мастер функций, выберите категорию математических функций.

Просмотрите список функций, ознакомьтесь с их описанием.

На Листе3 создайте таблицу табл. 4. Верхняя левая ячейка таблицы соответствует ячейке А20.

Таблица 4

Месяц	Регион	Продажи	Итоги по регионам
Январь	Север		Север
Февраль	Юг		Юг
Январь	Запад		Запад
Февраль	Восток		Восток
Март	Север		ВСЕГО
Январь	Юг
Февраль	Запад
Март	Восток		Итоги по месяцам
Февраль	Север		Январь
Март	Юг		Февраль
Март	Запад		Март
Январь	Восток		ВСЕГО
	ИТОГО

Отформатируйте таблицу согласно рисунку.

Рис. 25.

Вычислите итоговые значения по регионам и по месяцам, а также суммарные значения продаж. Для этого выполните следующие действия.

Активизируйте ячейку F21 (итоги по северу), вызовите Мастер функций, найдите в нем функцию SUMIF (СУММЕСЛИ).

В поле Диапазон укажите диапазон ячеек В21:В32 (в этом диапазоне ведется поиск критерия «Север»).

В поле Критерий введите ячейку Е21 (с этим значением происходит сравнение содержимого диапазона В21:В32).

В поле Диапазон суммирования укажите диапазон С21:С32 (при нахождении в диапазоне В21:В32 значения, отвечающего критерию, происходит суммирование соответствующих значений из диапазона С21:С32).

Проверьте правильность ввода аргументов рис.26.

Рис. 26.

Рассчитайте итоги по остальным регионам и месяцам (используйте Автозаполнение и абсолютные ссылки).

Вычислите итоговые значения.

Сравните полученные результаты с рис. 27.

Рис. 27.

Сохраните рабочую книгу.

Задание для самостоятельной работы:

Вставьте новый лист, выполнив команду Вставкаð Лист, создайте таблицу табл. 5

Таблица 5

Фамилия	Имя	Отчество	Отдел	Оклад	Премия	Ставки
Андреева	Анна	Семеновна	Бухгалтерия			0,2
Бутаков	Андрей	Викторович	Сбыт			0,15
Горбатов	Иван	Андреевич	Склад
Ерохин	Иван	Олегович	Склад
Иванов	Сергей	Александрович	Бухгалтерия
Крылова	Ольга	Сергеевна	Кадров
Маметов	Иван	Алексеевич	Сбыт
Петрова	Мария	Павловна	Кадров
Чарушин	Семен	Максимович	Склад
Яровцева	Елена	Викторовна	Бухгалтерия
			ИТОГО

Кол-во сотрудников			Суммы окладов
Бухгалтерия			Бухгалтерия
Сбыт			Сбыт
Склад			Склад
Кадров			Кадров
			ИТОГО
>6000
Иван

Отформатируйте таблицу согласно рис. 28.

Рис. 28.

Определите общее количество сотрудников по каждому из отделов.

Подсчитайте количество сотрудников, имеющих оклад больше 6000 р.

Определите количество сотрудников с именем «Иван».

Подсчитайте общую сумму окладов сотрудников каждого отдела.

Рассчитайте величину премии: для сотрудников, имеющих оклад меньше 5000 р., премия равна 20% от оклада, для остальных сотрудников – 15% от оклада.

Сохраните рабочую книгу.

Функция электронной таблицы - это предопределенное вычисление.

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

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

В среде Openoffice.org Calc существует множество функций. Стоит разделить их на 12 разделов:

1. Математические функции

2. Текстовые функции

3. Логические функции

4. Функции даты и времени

5. Функции базы данных

6. Функции преобразования чисел

7. Информационные функции

8. Функции электронных таблиц

9. Функции комплексных чисел

10. Статические функции

11. Финансовые функции

12. Функции массивов

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

Ниже приведена таблица часто используемых математических функций Calc:

Имя функции	Описание
ABS	Функция ABS возвращает абсолютное значение числа
COUNTIF	Функция подсчитывает количество ячеек в диапазоне, которые удовлетворяют заданному условию
SIN	Функция возвращает синус заданного угла (в радианах)
COS	Функция возвращает косинус заданного угла (в радианах)
EXP	Функция возвращает математическую константу "e" возведенную в степень числа
RAND	Функция возвращает случайное число между 0 и 1
RANDBETWEEN	Функция возвращает целое случайное число в указанном диапазоне
SQRT	Функция возвращает положительное значение квадратного корня числа
SUM	Функция суммирует содержимое ячеек
SUMIF	Функция суммирует содержимое ячеек в диапазоне, которые удовлетворяют заданному условию
SUMSQ	Функция возвращает сумму квадратов аргументов

Так же, широким распространением пользуются логические функции.

Логические функции оперируют логическими («булевыми») значениями , то есть TRUE или FALSE. Спецификация OpenDocument упоминает «логические операторы»; это просто другое название для логических функций. Это не поразрядные операции, например, AND(12;10), возвращает TRUE, а не 8.

Ниже приведена таблица логических функций Calc:

Имя функции	Описание
AND	Функция возвращает TRUE, если все аргументы определяются как TRUE, и FALSE в противном случае
FALSE	Функция возвращает логическое значение FALSE
IF	Функция возвращает одно из двух значений, в зависимости от результатов проверки условия
NOT	Функция меняет на противоположное логическое значение своего аргумента. Возвращается TRUE, если аргумент FALSE, и FALSE, если аргумент TRUE
OR	Функция возвращает TRUE, если любой из аргументов определяется как TRUE, и FALSE в противном случае
TRUE	Функция возвращает возвращает логическое значение TRUE

Рассмотрим несколько примеров использования простых функций.

Для того, чтобы не писать функции в ячейки вручную, существует Мастер функций.

Рис. 9. Мастер функций в Openoffice.org Calc.

Рассмотрим пошаговое его применение на простом примере использования математической функции ABS.

Рис. 10. Окно ввода.

Рис. 11. Использование Мастера функций.

3. В любое из выделенных красным полей необходимо ввести число, адрес ячейки, или же просто выбрать ячейку/диапазон ячеек левой кнопкой мыши. Далее нажимаем кнопку OK (желтое выделение). В нашем случае в ячейке B1 будет выполняться сама функция, а значение будет браться из ячейки A1.

Рис. 12. Использование Мастера функций.

4. Результатом выполнения функции будет следующее окно:

Рис. 13. Результат работы функции ABS.

А теперь рассмотрим использование логических функций на примере такой функции, как IF.

Нам понадобится знание нескольких математических функций.

Для начала сделаем подписи к будущим колонкам:

Ячейка А1 - Число 1;

Ячейка В1 - Число 2;

Ячейка С1 - Сумма;

Ячейка D1 - Функция IF.

1. Ячейки А2:А12 - числа от -5 до 5;

2. Ячейки B2:B12 - случайные числа от 1 до 5 (для простоты воспользуемся математической функцией RANDBETWEEN, получится запись =RANDBETWEEN(1;5), после чего растянем формулу на оставшиеся ячейки );

В результате у нас получится следующая таблица:

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

О чем будем говорить

Файл с примерами

Прикидка суммы

Если вам нужно просто «прикинуть» сумму какого-то диапазона, то в LibreOffice Calc можно воспользоваться встроенной возможностью. В строке состояния программы есть поле, в котором по умолчанию написано «Сумма=0». Если выбрать ячейки на листе с числовыми значениями, то вместо ноля, в этом поле будет отражаться сумма этих чисел. Для того чтобы выбрать ячейки отстоящие друг от друга, или диапазоны ячеек, можно их выделять зажав клавишу Ctrl . Минус этого способа в том, что он считает сумму только с текущего листа. Щёлкнув на поле правой кнопкой мыши, вы увидите список, представленный на скриншоте.

Обсуждать список сейчас я не буду, если хотите, можете просто поэкспериментировать с ним, и я думаю вы во всём разберётесь.

Сумма

В LibreOffice Calc функция сумма имеет тот же вид, что и в других электронных таблицах, таких как Excel или Apache OpenOffice. Её синтаксис выглядит следующим образом:

SUM(Число1; Число2; ...; ЧислоN)

В этом случае мы просто перечисляем числа через точку с запятой. Перечисления чисел может быть явным (1;2;3;...), может быть ссылками на ячейки (A1;C6;AZ190), а может быть смешанным (1; A1; C6;3). Функция будет «вынимать» числовое значение и использовать его.
Можно использовать функцию так:

SUM(Начало_диапазона:Конец_диапазона)

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

SUM(Название_именованного_диапазона)

Третий способ я вижу очень редко, однако он часто удобен. Выделите диапазон, пройдите в главном меню Данные → Задать диапазон… В поле название введите название нового диапазона. Обратите внимание на флажки «Заголовки в первой строке» и «Содержит строку итогов», первый из них установлен по умолчанию. Не забудьте снять или проставить их соответственно вашей ситуации. Нажмите OK. Теперь можно подставлять это название в формулы как аргумент вместо того, чтобы выделять диапазон мышкой.
Ещё один способ это использование заголовков столбцов или строк:

SUM(Заголовок_столбца)

Этот способ я вижу ещё реже, может потому, что по умолчанию эта возможность отключена. Если вы хотите использовать её, пройдите в главном меню Сервис → Параметры и в разделе LibreOffice Calc → Вычисления поставьте галочку «Автоматически определять заголовки столбцов и строк». Этот способ удобно использовать, когда диапазон занимает один столбец или строку, но вы не знаете насколько большим он будет. Но нужно помнить, что диапазон не должен прерываться, для LibreOffice Calc пустой ряд на всю ширину или высоту диапазона показывает, что диапазон закончился, и всё что дальше следует — это уже другой диапазон. На сегодняшний день (версия 5.0.0) функционал поиска заголовков не разработан до конца, например, он не переносит перетаскивания диапазона.
Хотя способов много, никто не запрещает использовать все способы одновременно, только не забывайте, что при указании диапазона между ссылкой на первую и последнюю ячейку в русской локализации по умолчанию ставится двоеточие, а между аргументами функции ставится точка с запятой. И я думаю, нет смысла напоминать о том, что ссылки могут быть не только на диапазоны в текущем листе, но и на другие листы, и даже на другие файлы, не важно на локальном компьютере они находятся или где-нибудь в сети.

Автосумма в LibreOffice Calc

Автосумма в LibreOffice Calc может выполнять автоматическое суммирование только непрерывного диапазона столбца или строки. При этом она будет искать диапазон по вертикали вверх и по горизонтали влево. Но так как, по существу, она просто вставляет формулу SUM с диапазоном как аргумент и оставляет её открытой для редактирования, то эта функция довольно часто бывает удобной, особенно, если вы предпочитаете работать мышкой. Использование её такое же, как и в других современных программах электронных таблиц. На панели формул есть значок с изображением греческой буквы сигма (Σ). Нажатие на этот значок активизирует автосумму.
Давайте разберём пример с вертикальным диапазоном из двух столбцов:

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

SUM как формула массива и суммирование по условию

В справке к программе (пройдите в главном меню Справка → Справка по LibreOffice , или нажмите клавишу F1 ) или на сайте онлайн справки вы можете найти пример нестандартного использования функции SUM. На мой взгляд это очень удачный пример, и я хотел бы вам его пересказать своими словами.
Допустим у нас есть таблица: в первом её столбце идут даты, а во втором — наши расходы за этот день, ведь мы можем сделать такую таблицу, нам её хватит на 2845 лет:). Но мы хотим узнать сколько мы потратили за какой-то конкретный месяц, или неделю, или год. То есть нам нужно просуммировать расходы за определённый период. Я прилагаю файл с примером, чтобы вы смогли посмотреть как это всё устроено. А здесь я объясню всё словами. Так выглядит таблица:

В ячейке I2 написана формула

SUM((A3:A300>=F1)*(A3:A300<=F2)*B3:B300)

Первая часть A3:A300>=F1 — это условие выше какой даты будут суммироваться числа, вторая часть A3:A300<=F2 — ниже какой даты. A3 — это начало диапазона дат, A300 — это конец диапазона, для примера 300 даже много, в реальности это будет предполагаемая ячейка, докуда вы хотите вести свой диапазон. Перемножая эти две части мы получаем матрицу истинности, где те числа, которые соответствуют необходимым датам будут равны 1, а остальные 0. В третьей части мы имеем массив с расходами и естественно, если мы умножаем на ноль, то в ответе будет ноль, а если на единицу, то значение будет равно значению ячейки. Таким образом мы получаем матрицу с нулями и необходимыми значениям. То есть, если это всё представить в развернутом виде, то будет примерно так SUM(0;0;…;0;отобранные значения; 0; 0; …; 0) . Для того чтобы эта формула начала работать, мы должны её сделать формулой массива, для этого, вместо обычного нажатия Enter нам нужно нажать сочетание Ctrl+Shift+Enter . Знаком, что вы всё сделали правильно, будет заключение формулы в фигурные скобки
{=SUM((A3:A300>=F1)*(A3:A300<=F2)*B3:B300)}

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

SUM((("Дата")>=F1)*(("Дата")<=F2)*("Расходы"))

Завершив введение Ctrl+Shift+Enter , мы получим тот же результат, но нам не придётся заботиться о величине диапазона, а главное легко его менять и автоматически получать обновленный результат. Эффекта этого примера, поиска по нескольким условиям, можно также достичь при помощи формулы SUMIFS. Приведу ещё один пример. В начале 2011 года был открыт отчет об ошибке 35636 , связанный с тем, что формула SUMIF не принимала как условие пусто (""). Исправили эту ошибку только в апреле 2015 года. Одним из способов обхода этой проблемы было использование формулы SUM как формулы массива в таком виде:

SUM((Диапазон_для_суммирования)*(Диапазон_для_проверки=""))

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

Автоматическое расширение диапазона при добавлении ячейки в конец

LibreOffice Calc при добавлении ячейки в середину диапазона, автоматически расширяет его в параметре формулы. Но если мы сделаем сумму этих ячеек в конце диапазона, например, используя атосумму, а затем попытаемся в конец суммируемого диапазона добавить ячейку, то Calc не включит её в сумму. Чтобы обойти это ограничение можно использовать ссылку на конец диапазона функцией OFFSET . Я не буду останавливаться на этой функции подробно, приведу только её синтаксис и объяснения, необходимые для этого примера.

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

SUM(A2:OFFSET(A6;-1;0))

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

=SUM("Сумма")

Сумма накопленным итогом

Сумма накопленным итогом — это одна из самых частых решаемых задач, особенно в моделях инвестиционных проектов. Сделать её можно по разному, но на мой взгляд самый удобный способ — закрепить часть диапазона в функции SUM и протянуть её. Допустим у нас есть денежный поток — деньги, выданные на карманные расходы ребёнку (незапланированные операционные затраты:)

В ячейку B4 вписываем формулу, указывая границы диапазона B3:B3 , и закрепляем первую часть, нажав Shift+F4 на клавиатуре (когда курсор установлен на первой части): =SUM($B$3:B3) При протягивании, первая ячейка, указывающая на начало диапазона, останется на месте, а вторая (конец диапазона) будет автоматически изменяться, в каждой новой ячейке образуя сумму всех предыдущих значений.

Суммирование трехмерных диапазонов (сквозное суммирование)

Когда ряд листов нашей книги электронных таблиц имеет одинаковую разметку, мы можем использовать трёхмерный диапазон для суммирования, иногда на форумах эту операцию называют «сквозное суммирование». Для трёх листов сложение 3 ячеек A1 выглядит следующим образом:

SUM(Лист1.A1:Лист3.A1)

Если же мы складываем диапазоны, то это будет выглядеть так:

SUM(Лист10.A1:B2:Лист11.A1:B2)
Обратите внимание, что листы, для которых проводится суммирование, должны стоять рядом. Первым в формуле должен быть указан крайний левый лист, а последним крайний правый из диапазона листов для суммирования.

SUMIF — суммирование по условию

Если нам необходимо сложить числа, отвечающие какому-то одному заданному условию, то самый простой способ — это использовать функцию SUMIF(). Синтаксис функции следующий:

SUMIF(Диапазон_для_условия; Условие; Диапазон_суммирования)

Диапазон для условия — это диапазон, в котором мы будет искать значения и сравнивать их с условием. Условие — это само условие. Кстати, оно поддерживает регулярные выражения, что значительно расширяет возможности применения этой формулы. Диапазон суммирования — это диапазон, выборка из которого по заданному условию будет суммироваться. Простой пример, допустим есть диапазон с денежным потоком, положительные числа — прибыль, отрицательные — расходы, нужно узнать сколько пришло, то есть сложить только положительные числа из диапазона:

SUMIF("Денежный поток";">0";"Денежный поток")

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

SUMIF(Диапазон для поиска;"";Диапазон суммирования)

Как вы понимаете, диапазон мы можем задавать всеми возможными способами: поиск по заголовку, именованный диапазон, просто указав границы диапазона, даже если диапазон находится в другом файле где-нибудь в сети интернет.

Использование регулярных выражений в условии

Поиск по условию в LibreOffice Calc поддерживает регулярные выражения. Запись регулярных выражений отличается от записи их в Excel и немного отличается от привычной записи их в shell. Подробно о регулярных выражениях рассказано на странице справки , также я касался их, когда рассказывал о применении функций VLOOKUP и HLOOKUP .
Чаще всего приходится считать сумму чего-либо одного вида (например, остатки, приход или продажи карандашей, тетрадок). Сделаем простую таблицу, с заголовками Товар и Количество, для названия товаров и их количества соответственно.

Задача посчитать карандаши (27 штук, но в уме не считаем, считаем формулами:). Итак, из выше изложенного понятно, что нам нужно использовать регулярные выражения в условии. Общим фрагментом текста для всех названий карандашей является слово карандаш. И так как у нас есть символы до общего фрагмента и после, то мы должны это указать. Регулярное выражение будет выглядеть так: ".*карандаш.*" . Точка (.) тут будет означать любой символ, звёздочка (*) любое количество символов. Порядок точка-звёздочка обязательный, если вы забудете поставить точку, то будет выдана ошибка, такова специфика регулярных выражений в Calc. Формула для такой таблицы будет выглядеть:

SUMIF(A2:A6;".*карандаш.*";B2:B6)

Более продвинутым способом будет регулярное выражение со ссылкой на ячейку, в которой будет задаваться ключевое слово (вводится руками, или выбирается из списка). Допустим что искомое значение задаётся в ячейке D1:

SUMIF(A2:A6;".*"&D1&".*";B2:B6)

Одно замечание. На сегодняшний день существует ошибка 93510 , которая не позволяет использовать регулярные выражения совместно с функцией автоматического поиска заголовков в столбцах и строках, если диапазон состоит из слов. Независимо от значений в диапазоне суммирования, функция всегда возвращает 0. Скорее всего эта ситуация возникает из-за того, что LibreOffice Calc не правильно расставляет приоритеты для заголовков. Обойти эту ошибку можно, если между заголовком и началом значений будет пустая строка, или более элегантный метод, использовать для заголовков объединённые ячейки.

SUMIFS — поиск по многим условиям

В отличии от функции SUMIF функция SUMIFS позволяет задавать от одного до 30 условий. Её синтаксис:

SUMIFS(Диапазон_суммирования;Диапазон_для_условия1; Условие1;…;Диапазон_для_условия30; Условие30)

Я думаю вы понимаете, что в диапазоне суммирования будет стоять диапазон значений, из которого мы хотим складывать числа. Диапазон условия — это там, где мы будем искать. Диапазоны условия и суммирования могут быть одинаковые или разные. Условие — это то, что мы ищем (не забывайте заключать выражение в прямые кавычки(""). Формула предоставляет возможность избыточного количества условий, думаю, 30 диапазонов и условий, хватит на все случаи жизни. Пример, который я давал, когда рассказывал про SUM как формулу массива, можно переписать так:

SUMIFS("Расходы";"Дата";">="&F1;"Дата";"<="&F2)

SUBTOTAL — сумма видимых ячеек или промежуточные итоги

Иногда возникает необходимость посчитать сумму отобранных (видимых) ячеек автофильтром. Самый простой способ в LibreOffice Calc, на мой взгляд, использовать функцию SUBTOTAL(). Эта функция позволяет сделать намного больше, но в рамках этой статьи я покажу только сумму. Остальной функционал её можно посмотреть в справке . Допустим у нас есть большая таблица, в которой мы делаем выборку и хотим получить сумму. Для примера мы возьмём довольно простой вариант:

Конечно мы может воспользоваться SUMIF, SUMIFS или даже формулой массива, но если мы используем фильтры, то самый простой способ использовать SUBTOTAL. Её синтаксис выглядит так:

SUBTOTAL(Номер_функции; Диапазон)

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

SUBTOTAL(9;B2:B9)

Если вы добавляете строки в диапазон, то для автоматического расширения диапазона при добавлении ячейки в конец, можно воспользоваться советом приведённом выше для формулы SUM . Теперь, когда мы будем фильтровать необходимые строки, в ячейке с формулой будет появляться их сумма. Единственное замечание: не забывайте оставлять галочку на пункте «пусто», а то скроется и сам результат.
В справке не указано, но на самом деле это работает: когда мы используем SUBTOTAL без фильтра, то функция с кодами, указанными в справке, не исключает скрытые строки из расчета. Чтобы исключить их, добавьте перед кодом функции 10 . То есть для суммы код 9 не будет исключать скрытые строки, а 109 будет. После скрытия пересчитайте результат нажатием Ctrl+Shift+F9 . Обратите внимание, функция SUBTOTAL не включает в расчет ячейки, содержащие предварительные итоги (эту же функцию). Другим способом для решения этих задач может быть использование функции AGGREGATE .

AGGREGATE — суммирование с пропуском скрытых ячеек и ячеек с ошибками

В прошлом примере было показано как суммировать диапазон, исключая скрытые ячейки, но бывают ситуации, когда нужно исключить ещё и ячейки с ошибками или с функциями SUBTOTAL и самой AGGREGATE . Функция AGGREGATE имеет ещё больше возможностей чем SUBTOTAL . Ниже я даю описание её, но так как некоторые параметры этой функции к теме этой статьи не относится, я их не буду касаться. И так как она, к сожалению, до сих пор не описана в справке, если вам нужно уточнить что-то по ней, то лучше пока будет воспользоваться справкой Excel (ну, должны же мы хоть что-то полезное получать от корпорации MS:).
В рамках этой статьи нам интересен следующий синтаксис функции:

AGGREGATE(Номер_функции; Параметры; Диапазон)

Номер_функции — это номер, за которым закреплена функция, использующаяся для диапазона. Для задач этой статьи это номер 9 — сумма.
Параметр — числовое значение, определяющее какие значения при вычислении следует пропускать. Все параметры пронумерованы от 1 до 7. Следующий список показывает номер и краткое описание функции:

0 или опущен — пропускать вложенные функции SUBTOTAL и AGGREGATE .
1 — пропускать скрытые строки и вложенные функции SUBTOTAL и AGGREGATE .
2 — пропускать значение ошибок и вложенные функции SUBTOTAL и AGGREGATE .
3 — пропускать скрытые строки, значения ошибок и вложенные функции SUBTOTAL и AGGREGATE .
4 — ничего не пропускать.
5 — пропускать только скрытые строки.
6 — пропускать только значения ошибок.
7 — пропускать скрытые строки и значения ошибок.

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