Формула - это некое выражение, которое выполняет вычисление между операндами с помощью операторов.
Операнд - это элемент вычисления (константы, функции и ссылки). Константа - постоянное (не вычисляемое) значение. Может быть числом или текстом.
Функция - заранее созданная формула, выполняющая сложные вычисления по введенным значениям (аргументам) в строго определенном порядке. Функции бывают математическими, финансовыми, статистическими и т. п.
Операторы - это знак или символ, определяющий тип вычисления в формуле над операндами.
В Calc используются следующие операторы: математические, текстовые, операторы сравнения и операторы ссылок. Формула всегда начинается со знака равенства (=), а затем следуют операнды и операторы, например =24*3, или =А1+В2*(С10-D8)/ SUM(L12:H4), где 24, 3 - константы; SUM - функция автосуммирования; А1, В2, С10, D8, L12 и H4 - ссылки на адреса ячеек; +, -, / и * - операторы. Формулу можно вводить непосредственно в ячейку или же в окно ввода на строке формул. После вычисления в ячейке отображается полученный результат, а на строке формул в окне ввода - созданная формула.
Если выделить ячейку с формулой, то в самой ячейке будет отображаться результат вычисления, а в строке формул - ее формула. Операторы Математические операторы Математические операторы - это операторы простых действий: сложения, вычитания, умножения и т. д. Вводятся с помощью клавиатуры.
+ (плюс) - сложение;
– (минус) - вычитание или отрицание;
* (звездочка) - умножение;
/ (косая черта) - деление;
% (знак процента) - процент;
^ (знак крышки) - возведение в степень. Операторы сравнения Операторы сравнения - это операторы сравнения разных значений, результатом которого является логическое выражение ИСТИНА или ЛОЖЬ.
= (знак равенства) - показывает значения, равные заданному;
> (знак «больше») - показывает значения больше заданного;
< (знак «меньше») - показывает значения меньше заданного;
>= (знак «больше или равно») - показывает значения, которые больше или равны заданного;
<= (знак «меньше или равно») - показывает значения, которые меньше или равны заданному;
? (знак «не равно») - показывает значения, не равные заданному. текстовый оператор текстовый оператор амперсанд (&) - это оператор объединения нескольких текстовых отрывков в одну строку, например двух последовательностей знаков в одну последовательность. операторы ссылок оператор ссылок - это оператор ссылки на диапазон ячеек.
; (точка с запятой) - оператор объединения нескольких ссылок в одну, например sum(с10:в15; е45:т30);
() знак пробела - оператор пересечения множеств, который используется для ссылки на общие ячейки двух диапазонов. например, в10:d20 c15:c25.
каков приоритет операторов в случае использования в формуле нескольких операторов они выполняются в следующем порядке:
1. операторы ссылок (двоеточие, точка с запятой, пробел).
3. процент.
4. возведение в степень.
5. умножение и деление.
6. сложение и вычитание.
7. объединения нескольких текстовых отрывков в одну строку.
8. операторы сравнения.
Если формула содержит операторы с одинаковым приоритетом, то они исполняются слева направо. для изменения порядка вычисления в формулах можно использовать круглые скобки.
Функция электронной таблицы - это предопределенное вычисление.
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), после чего растянем формулу на оставшиеся ячейки );
Логические функции
|
Функция в 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. – ... |
Одно из назначений электронных таблиц - это вычисления, поэтому сейчас будут рассмотрены основные правила написания формул.
Как уже отмечалось, ввод формулы начинается со знака равенства, далее пишется сама формула. К примеру: =4+16 . Записав такую формулу и нажав Enter , мы увидим в ячейке число 20. Конечно, формулы без переменных обычно не имеют особого смысла, поэтому теперь посмотрим, как использовать переменные, в качестве которых в OpenOffice.org Calc служат адреса ячеек. К примеру, если в A1 мы записали число 20, то после записи в B1 формулы =A1^2 и нажатия Enter в ячейке B1 появится число 400.
Основные арифметические операции, доступные OpenOffice.org Calc :
- "+" - сложение;
- "-" - вычитание;
- "*" - умножение;
- "/" - деление;
- "^" - возведение в степень;
- ":" - задание диапазона.
Кроме этих операций, в OpenOffice.org Calc доступен обширный набор функций следующих категорий:
- работа с базами данных;
- обработка времени и дат;
- финансовые;
- информационные;
- логические;
- математические;
- работа с массивами;
- статистические;
- текстовые;
- дополнительные.
Для удобства написания формул в OpenOffice.org Calc разработан автопилот функций.
В окне автопилота можно набирать функции и проверять правильность их набора; в зависимости от выбора категории список доступных функций будет изменяться. Кроме перечисленных выше категорий, для удобства добавлены Все и Недавно использованные .
В поле редактирования "Формула" отображается текущая формула, которую можно непосредственно редактировать - а можно, поставив курсор в необходимую позицию, дважды щёлкнуть по имени функции из списка, и выбранная функция вставится в окно ввода. Останется только либо ввести аргумент с клавиатуры, либо нажать кнопку:
В закладке Структура , набранная формула развернута в дерево, что очень помогает в редактировании формул, позволяя отслеживать порядок вычисления формулы.
Для случая, когда формула достаточно проста (содержит знаки "+" , "-" , "*" , "/" , "^" ), но состоит из относительно большого числа переменных, рассмотрим следующий пример:
Пусть требуется вычислить A1+C5*B4 ; для этого:
Нажмите "=", после чего выберите с помощью стрелок управления курсором ячейку A1 (при первом же нажатии на клавишу управления курсором появится красный прямоугольник-курсор). Затем нажмите "+" и выберите C5 , нажмите "*" и, наконец, выберите B4 . Таким способом с помощью клавиатуры можно быстро формировать формулы (ячейки можно выбирать и указателем мыши).
После ввода "=" и какой-либо буквы OpenOffice.org Calc автоматически высвечивает имя функции, начинающейся на эту букву. Эта возможность позволяет набирать не всю формулу, а только первые её буквы, а дальше, если предложенная функция является именно той, которая нужна, останется только нажать "Enter" .
Бывает так, что при вводе формул в качестве их аргументов требуется передавать не адрес ячейки, а целую область - к примеру, необходимо просуммировать все значения в столбце A , начиная с адреса A2 по адрес A11. Конечно, можно написать =A2+A3+...+A10+A11 - но гораздо проще и в любом случае правильнее будет написать "=Су", затем воспользоваться подсказкой (Сумм ) и, нажав "Enter", в скобках вписать диапазон "A2:A11" .
Область рабочего листа задается указанием адреса левой верхней ячейки, далее ставится двоеточие и указывается правая нижняя ячейка. Область можно задать и с помощью мыши.
В 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 |
