Как сделать сумму в openoffice
Формулы OpenOffice Calc
При задании формул наряду с основными арифметическими операциями, OpenOffice Calc предоставляет множество специальных функций, которые можно ввести в интерактивном режиме при помощи Автопилота функций. OpenOffice Calc поддерживает, в частности, многие статистические методы: от регрессионных расчетов до доверительных интервалов. Особенно интересна возможность изменять отдельные параметры в вычислениях, зависящих от многих факторов, и прослеживать, как это влияет на результат. Это так называемые расчеты «что было бы, если». Например, при расчете кредита путем простого изменения периода, процентной ставки или сумм выплаты можно сразу же увидеть, как изменяются остальные факторы.
Практически все функции (за исключением математических и некоторых статистических) в программе OpenOffice Calc локализованы, т. е. используют русскоязычные имена. К таковым относятся и все функции из раздела Дата&Время (однако функция ПАСХАЛЬНОЕВОСКРЕСЕНЬЕ() определяет дату католической, а не православной Пасхи).
Для ввода функций в ячейку можно воспользоваться мастером функций. Выберите ячейку, в которую надо ввести функцию, и нажмите кнопку 
, которая находится на панели инструментов, или выберите команду Функция. из меню Вставка. Перед вами появится окно Автопилот функций, в котором надо выделить функцию и нажать кнопку Далее>> или OK, после чего появится окно ввода аргументов выбранной функции. Функцию можно вводить не только с помощью мастера функций, но и вручную, если вы помните, как она называется и сколько у нее параметров.
В программе OpenOffice Calc можно просто ввести даты в формате «месяц.число.год» (без указания кавычек, иначе ввод будет интерпретирован как текст), например, «5.17.2» для 17 мая 2002 года. В этом случае любой ввод со значениями, выходящими за границы допустимых, трактуется не как дата, а как текст. Функция ТДАТА() возвращает дату и время в соответствии с системным временем компьютера, которые обновляются при каждом пересчете документа.
Пример
Мощным инструментом при работе с электронными таблицами является подбор параметра. С помощью этого инструмента можно узнать значение, которое при подстановке в формулу дает желаемый результат. Для того чтобы воспользоваться подбором параметра нужно иметь формулу с несколькими постоянными значениями и одним переменным. Лучше всего можно объяснить подбор параметра на примере.
Ячейкам лучше всего дать имена, чтобы формула имела осмысленный вид. В примере сначала вычисляется, какова будет сумма процентов при вложенном капитале 150 000 р. и процентной ставке 7,5% за год.
Теперь, считая, что процентная ставка (7,5%) и количество лет (1) не изменяются, определим, какого размера должен быть капитал, чтобы давать определенные годовые проценты, скажем, 15 000 р. Выделите ячейку Проценты и выберите в меню Сервис пункт Подбор параметра. Появится одноименное окно, в поле Ячейка формул которого уже указана нужная ячейка.
Поместите курсор в поле диалога изменяемая ячейка. В таблице выделите ячейку, значение которой изменяется, т. е. B2. В текстовом поле Конечное значение диалога введите желаемый результат формулы и нажмите кнопку OK. Если параметр был успешно подобран, то отобразится результат, который можно занести в таблицу.
Информатика в экономике и управлении
Свободное программное обеспечение для бизнеса и дома.
Страницы блога
четверг, 27 августа 2015 г.
Сумма, автосумма и некоторые другие секреты LibreOffice Calc
О чем будем говорить
Прикидка суммы
Сумма
Автосумма в LibreOffice Calc
SUM как формула массива и суммирование по условию
В ячейке I2 написана формула
Не выставляйте скобки сами своими руками — это не поможет, необходимо правильно завершить ввод формулы. Если вы используете автоматический поиск заголовков в столбцах и строках, то эту формулу можно переписать следующим образом:
Завершив введение Ctrl+Shift+Enter, мы получим тот же результат, но нам не придётся заботиться о величине диапазона, а главное легко его менять и автоматически получать обновленный результат. Эффекта этого примера, поиска по нескольким условиям, можно также достичь при помощи формулы SUMIFS. Приведу ещё один пример. В начале 2011 года был открыт отчет об ошибке 35636, связанный с тем, что формула SUMIF не принимала как условие пусто («»). Исправили эту ошибку только в апреле 2015 года. Одним из способов обхода этой проблемы было использование формулы SUM как формулы массива в таком виде:
Я думаю, понятно, что во втором параметре будет создаваться матрица истинности, умножение на которую все ненужные значения превращает в ноль, и, в итоге, мы получаем сумму значений, соответствующих пустым ячейкам.
Автоматическое расширение диапазона при добавлении ячейки в конец
OFFSET(Ссылка; Строки; Столбцы; Высота; Ширина)
Ссылка — это ячейка или диапазон ячеек, от которого начинается отсчет для смещения. Строки — это смещение в строках от ссылки, положительные числа будут смещать вниз, отрицательные вверх. Столбцы — смещение по столбцам от ссылки, положительные в право, отрицательные влево. Остальные параметры нам не важны. В нашем примере ссылка — это та ячейка, в которой написана формула для суммирования. Смещение в строках мы указываем на последнюю ячейку в суммируемом диапазоне. Столбцы, в этом примере мы не трогаем. В итоге мы получает формулу такого вида:
Использование автоматического поиска по заголовкам столбцов или строк, работает не совсем так как ожидается. Ставить формулу вплотную к диапазону нельзя. Кроме того, добавлять строку или ячейку необходимо не со строки формулы, а с пустой ячейки между формулой и диапазоном. Как вы понимаете, это недоработка, которая не приносит ожидаемый результат и не позволяет использовать инструмент интуитивно. Для нашего примера формула будет выглядеть так:
Сумма накопленным итогом
Суммирование трехмерных диапазонов (сквозное суммирование)
Когда ряд листов нашей книги электронных таблиц имеет одинаковую разметку, мы можем использовать трёхмерный диапазон для суммирования, иногда на форумах эту операцию называют «сквозное суммирование». Для трёх листов сложение 3 ячеек A1 выглядит следующим образом:
Если же мы складываем диапазоны, то это будет выглядеть так:
=SUM(Лист10.A1:B2:Лист11.A1:B2)
Обратите внимание, что листы, для которых проводится суммирование, должны стоять рядом. Первым в формуле должен быть указан крайний левый лист, а последним крайний правый из диапазона листов для суммирования.
SUMIF — суммирование по условию
Если нам необходимо сложить числа, отвечающие какому-то одному заданному условию, то самый простой способ — это использовать функцию SUMIF(). Синтаксис функции следующий:
SUMIF(Диапазон_для_условия; Условие; Диапазон_суммирования)
Диапазон для условия — это диапазон, в котором мы будет искать значения и сравнивать их с условием. Условие — это само условие. Кстати, оно поддерживает регулярные выражения, что значительно расширяет возможности применения этой формулы. Диапазон суммирования — это диапазон, выборка из которого по заданному условию будет суммироваться. Простой пример, допустим есть диапазон с денежным потоком, положительные числа — прибыль, отрицательные — расходы, нужно узнать сколько пришло, то есть сложить только положительные числа из диапазона:
=SUMIF(‘Денежный поток’;»>0″;’Денежный поток’)
Или очень популярный вопрос «как сложить пустые ячейки» (меня этот вопрос всегда вводит в ступор :). Смысл в том, что мы в одном диапазоне ищем пустые ячейки, а во втором им соответствующие значения складываем:
=SUMIF(Диапазон для поиска;»»;Диапазон суммирования)
Как вы понимаете, диапазон мы можем задавать всеми возможными способами: поиск по заголовку, именованный диапазон, просто указав границы диапазона, даже если диапазон находится в другом файле где-нибудь в сети интернет.
Использование регулярных выражений в условии
Более продвинутым способом будет регулярное выражение со ссылкой на ячейку, в которой будет задаваться ключевое слово (вводится руками, или выбирается из списка). Допустим что искомое значение задаётся в ячейке D1:
Одно замечание. На сегодняшний день существует ошибка 93510, которая не позволяет использовать регулярные выражения совместно с функцией автоматического поиска заголовков в столбцах и строках, если диапазон состоит из слов. Независимо от значений в диапазоне суммирования, функция всегда возвращает 0. Скорее всего эта ситуация возникает из-за того, что LibreOffice Calc не правильно расставляет приоритеты для заголовков. Обойти эту ошибку можно, если между заголовком и началом значений будет пустая строка, или более элегантный метод, использовать для заголовков объединённые ячейки.
SUMIFS — поиск по многим условиям
В отличии от функции SUMIF функция SUMIFS позволяет задавать от одного до 30 условий. Её синтаксис:
SUMIFS(Диапазон_суммирования;Диапазон_для_условия1; Условие1;…;Диапазон_для_условия30; Условие30)
Я думаю вы понимаете, что в диапазоне суммирования будет стоять диапазон значений, из которого мы хотим складывать числа. Диапазон условия — это там, где мы будем искать. Диапазоны условия и суммирования могут быть одинаковые или разные. Условие — это то, что мы ищем (не забывайте заключать выражение в прямые кавычки(«»). Формула предоставляет возможность избыточного количества условий, думаю, 30 диапазонов и условий, хватит на все случаи жизни. Пример, который я давал, когда рассказывал про SUM как формулу массива, можно переписать так:
SUBTOTAL — сумма видимых ячеек или промежуточные итоги
Конечно мы может воспользоваться SUMIF, SUMIFS или даже формулой массива, но если мы используем фильтры, то самый простой способ использовать SUBTOTAL. Её синтаксис выглядит так:
AGGREGATE — суммирование с пропуском скрытых ячеек и ячеек с ошибками
AGGREGATE(Номер_функции; Параметры; Диапазон)
Математические расчеты в OpenOffice Writer
Расчеты выполняются в таблицах. В таблицах можно производить любые вычисления (арифметические, статистические, логические, тригонометрические и др.).
Создадим таблицу, состоящую из четырех столбцов и любого числа строк. Заполним любыми простыми числами первые три столбца, кроме первой строки. В первой строке запишем заголовки столбцов: 1-й столбец, 2-й столбец, 3-й столбец, Сумма. В таблицах можно выполнять суммирование по строкам или столбцам. Для этого необходимо поместить курсор в ту ячейку таблицы, где будет находиться сумма (в нашем примере — в четвертый столбец второй строки, так как в первой строке предполагаются заголовки столбцов строки с заголовками). Затем на инструментальной панели Таблица нужно нажать кнопку Сумма (Щ,)-
После этого над измерительной линейкой появится строка формул, в которой будет выведено приблизительно такое выражение:
В данном примере выполнялось сложение в строке. Программа автоматически предлагает суммировать значения в ячейках, находящихся либо слева либо сверху от ячейки, в которую помещен курсор. Так как курсор находится в четвертом столбце, то программа предлагает суммировать числа из первых трех столбцов, находящихся слева, т.е. столбцов А, В и С. Адреса ячеек заключены в угловые скобки (о). Каждая суммируемая ячейка разделена прямым слешем (|). Адресация ячеек определяется очень просто: первый столбец А, второй — В и т.д. Поэтому если программа правильно определила диапазон суммирования, то на полосе формул требуется нажать кнопку Применить или клавишу Enter на клавиатуре. Если программа определила диапазон неправильно, то следует выделить те ячейки, в которых нужно складывать значения, например первые два столбца. В этом случае формула может выглядеть так:
В этом примере складываются не все ячейки в строке, а только А2 и В2. После того как ячейки указаны вручную явно, также нужно нажать кнопку Применить (ч#).
Если формула была создана случайно (не только суммирование, но и любая другая формула), используется кнопка Отменить ($8) или клавиша Esc на клавиатуре.
К простейшим арифметическим выражениям относятся: сложение (+), вычитание (-), умножение (*) и деление (/).
Для расчета любых арифметических формул следует нажать клавишу F2 на клавиатуре или выполнить команду Таблица => Формула. После этого под измерительной линейкой откроется полоса формул и будет автоматически введен символ равно (=). Здесь нужно не вводить в формулу конкретные числа, а указывать адреса ячеек, в которых хранятся какие-то числа.
Зетем необходимо выполнить такие действия:
В арифметических формулах необязательно использовать только числа, которые находятся в ячейках. Можно также использовать числа, вводимые с клавиатуры. Например:
Программа позволяет выполнять более сложные выражения. Для этого после вызова формулы, т.е. нажатия клавиши F2, нужно нажать кнопку Формула (fx) в полосе формул. При этом все равно — нажимать эту кнопку или маленькую черную треугольную кнопку, которая находится справа от кнопки Формула.
Приведем примеры некоторых наиболее популярных выражений.
Сумма (SUM) — подсчитывает сумму выделенных ячеек. Например:
Округлить (ROUND) — округляет число до указанного числа знаков после запятой. Например:
В данном примере СЗ — это число, которое нужно округлить. А число после имени функции round — это число знаков после запятой, до которых нужно округлить исходное число, т.е. СЗ.
Проценты (PHD) — подсчитывает проценты от указанного числа. Например:
Квадратный корень (SQRT) — извлекает квадратный корень из указанного положительного числа. Например:
Степень (POW) — возводит одно число в степень другого числа. Например:
В разделе Статистические функции кнопки Функции (fx) предлагаются функции для расчета среднего, минимального и максимального значений.
Среднее значение (MEAN) — рассчитывает среднее значение в выделенных ячейках. Например:
Минимальное значение (MIN) — рассчитывает наименьшее значение в выделенных ячейках. Например:
Максимальное значение (МАХ) — рассчитывает наибольшее значение в выделенных ячейках. Например:
Тригонометрические функции вычисляются в радианах, а не в градусах (т.е. по стандартам США), поэтому их еше нужно переводить в градусы.
Кроме функций можно использовать некоторые константы:
Для форматирования числа выделяют те ячейки, в которых нужно произвести такое форматирование. Затем необходимо выполнить команду Таблица => Числовой формат. После этого откроется окно Числовой формат (рис. 3.3).
Рис. 3.3. Выбор формата числа
В списке Категория необходимо выбрать тип формата, указать число десятичных знаков (счетчик Дробная часть) и нажать кнопку ОК. Рассмотрим некоторые типы форматов (табл. 3.1).
Значения записываются в ячейку в том виде, в каком они вводятся. Другими словами, число воспринимается в процентном или денежном формате только тогда, когда вы вводите знак «процент» (%) или соответственно «рубль» (р.)
По умолчанию числа в этом формате не имеют десятичных разрядов. Отрицательные величины отображаются или со знаком «минус» (—), или красным цветом, или в круглых скобках
Позволяет выбрать число десятичных знаков, при этом символ процента всегда отображается
По умолчанию числа в этом формате не имеют десятичных знаков
Основы вычислений в электронных таблицах OpenOffice.org Calc
Описание разработки
Основы вычислений в электронных таблицах OpenOffice.org Calc. Пособие Основы вычислений в электронных таблицах OpenOffice.org Calc содерит подробную информацию для учителей и учеников, работающих с СПО по вычислениям в электронных таблицах, построению диаграмм и графиков, использованию математических и других функций.
Содержимое разработки
Комплекс средств, состоящий из программных модулей и реализующий некоторую совокупность операций над табличными данными, принято называть табличным процессором (электронной таблицей – ЭТ). Последний стал непременным элементом автоматизации учрежденческой и управленческой деятельности.
В данном пособии рассматриваются некоторые типовые задачи и даются методические указания, полезные для практической работы с электронными таблицами OOo Calc. Для успешного выполнения рассматриваемых задач нужно обладать начальными знаниями о технологии работы с графическим пользовательским интерфейсом.
2.ИНТЕРФЕЙС ЭЛЕКТРОННОЙ ТАБЛИЦЫ
В интерфейсе программ ЭТ различают понятия «окно программы» и «рабочее поле программы». Окно программы ЭТ устроено стандартным для графического интерфейса образом и содержит строку заголовка окна, строку главного меню, панели инструментов (как минимум, панели «стандартная» и «форматирование»), а также строку состояния (рис. 1). Нестандартным элементом окна, свойственным только программам ЭТ, является строка ввода, в которой отображается и редактируется помещенная в таблицу информация.
Слева от строки ввода расположены три важные кнопки: кнопка » =«, обеспечивающая переход в режим ввода формул, кнопка автосуммирования » S» и кнопка вызова Мастера функций » f(x)«.
Рис. 1. Элементы интерфейса электронной таблицы
Рабочее поле программы представляет из себя совокупность листов, каждый из которых состоит из ячеек. Каждая ячейка имеет адрес, определяемый строкой и столбцом, на пересечении которых находится ячейка. Столбцы, как правило, обозначаются буквами латинского алфавита (1 или 2 буквы), строки – числами. Сетку, которая “расчерчивает” лист на ячейки, при желании можно сделать невидимой. Всегда одна из ячеек является активной. Активная ячейка – это та ячейка, в которую в данный момент осуществляется ввод или содержание которой редактируется. Активная ячейка всегда выделена (обычно “жирной” рамкой). Перемещение указателя активной ячейки осуществляется клавишами-стрелками на клавиатуре или щелчком левой кнопкой мыши в любом месте листа. Ввод информации в позицию активной ячейки осуществляется либо полностью с помощью клавиатуры, либо (в случае построения формул с функциями) с помощью действий “вставка функции” («Вставка/Функция. «) из главного меню или панели инструментов.
По умолчанию в документе электронной таблицы листов немного (3 или 4). Однако щелчок правой кнопкой мыши по ярлычку листа вызывает контекстное меню, с помощью которого листы можно добавлять, переименовывать, копировать, перемещать и удалять.
Слева от ярлычков листов имеются кнопки перемещения по листам. Пока листов мало, они неактивны.
В каждой ячейке ЭТ могут быть записаны числа, текст или формулы. Особым случаем числа является календарная дата. Числа автоматически выравниваются по правому краю ячеек, а текст – по левому. Содержание активной ячейки отображается в строке ввода, а для редактирования содержания активной ячейки надо нажать клавишу [F2] и после этого редактировать содержание строки ввода как текст. Редактирование завершается нажатием на [ENTER]. Для удаления содержания ячейки (или блока ячеек) достаточно нажать клавишу [DEL].
Каждая ячейка ЭТ имеет адрес, который записывается как комбинация буквы столбца и номера строки (напр. A8, C67, X999). Для указания блока (диапазона) ячеек указываются адреса левой верхней и правой нижней ячеек через двоеточие (например, A3:C18, B1:B46, C3:C12).
Операции редактирования (копирования, перемещения, удаления и вставки) можно делать с ячейками или блоками ячеек (копирование, перемещение или удаление), со строками, столбцами, группами строк и столбцов (удаление или вставка). Перед операцией редактирования нужно выделить объект для выполнения операции. Выделение групп ячеек делается «протаскиванием» мыши с нажатой левой кнопкой от левого верхнего угла нужного блока до нижнего правого угла, а выделение строк и столбцов – щелчком левой кнопкой мыши по номеру строки (букве столбца). Для выделения группы строк или столбцов тоже надо «протащить» указатель мыши.
Каждый лист ЭТ OOo Calc может содержать до 245 столбцов и до 65000 строк (примерно), а в файле ЭТ может храниться до 256 таких листов. В каждую ячейку может быть записан текст или формула длиной до 256 символов.
Обратим внимание на нижний правый угол указателя активной ячейки. Этот угол выделен черным квадратиком. Если «зацепить» этот квадратик левой кнопкой мыши и протащить на нескольтко ячеек вбок или по вертикали, будет вызвана функция «Автозаполнение», которая во многих случаях упрощает создание последовательности данных или копирование формул.
3.ФОРМУЛЫ. АБСОЛЮТНАЯ И ОТНОСИТЕЛЬНАЯ АДРЕСАЦИЯ
Начнем рассмотрение вычислений в ЭТ с простой задачи. Пусть имеется список из 7 человек, для каждого из которых известны фамилия, инициалы, должность, оклад за день работы и число отработанных дней. Требуется вычислить заработок каждого лица.
Формируем таблицу, начиная с ячейки A3, в соответствии с рис. 2. При вводе исходных данных полезно отключить режим автоматической проверки орфографии. Для исправления ошибок в ячейках электронной таблицы используется режим редактироваия строки ввода, который включается клавишей [F2]. Завершение редактирования обеспечивается клавишами [ENTER] (с сохранением изменений) или [ESC] (без сохранения изменений).
Рис. 2. Исходные данные для примера вычислений
Если при вводе информации ширина ячейки представляется недостаточной, ее можно скорректировать после завершения ввода всех данных. В ЭТ есть возможность подобрать ширину столбца автоматически («Формат/Столбец/Оптимальная ширина. «).
Для вычисления заработка нужно просто перемножить попарно числа из третьей (столбец C) и четвертой (столбец D) колонок. Результаты вычислений должны быть в пятой колонке (столбец E). С учетом возможностей ЭТ, формулу (т.е. правила) для вычислений можно написать один раз, а потом скопировать. Формулу надо писать там, где должен появиться первый результат (в нашем примере – в ячейке E4, под заголовком «Заработок»). Переводим указатель активной ячейки в клетку E4 и нажимаем клавишу “=” (указание на начало ввода формулы). После этого щелкаем левой кнопкой по ячейке, в которой записан оклад за день (C4), нажимаем на клавиатуре знак операции (умножение – “*”) и щелкаем левой кнопкой по ячейке с количеством отработанных дней (D4), после чего нажимаем
Теперь скопируем эту формулу в оставшиеся ячейки. Поместив указатель активной ячейки на E4, в главном меню выберем команду копирования («Правка/Копировать»). После этого выделим ячейки E5:E11 и вызовем команду вставки («Правка/Вставить»). Результаты показаны на рис. 3.
Рис. 3. Результаты вычисления и копирования формулы.
Если изменить какие-то числа в столбцах C и D, то числа в столбце E будут автоматически пересчитываться.
Перемещая указатель активной ячейки по столбцу E, можно заметить, что адреса ячеек в расчетной формуле изменяются. Это происходит потому, что в нашей формуле использованы относительные адреса ячеек. Формула просто перемножает содержимое ячеек, находящихся слева от ячейки с результатом. Таким образом, формула «запомнила» взаимное расположение ячеек с данными и с результатом и при копировании это взаимное расположение сохраняется. Это очень полезное свойство ЭТ, избавляющее от необходимости писать одну и ту же формулу много раз.
Если в какой-либо ячейке расчетного столбца (столбца «Заработок») перейти в режим редактирования (
На следующем этапе посчитаем налог на доходы физических лиц, который будет начислен на рассчитанные ранее значения заработка. Пусть ставка налога фиксирована и составляет 13%. Тогда наша таблица дополняется в соответствии с рис. 4.
Рис. 4. Добавление параметра для вычислений.
Сумму налога легко сосчитать по правилу «Сумма налога = заработок*ставка_налога». Указав соответствующие адреса ячеек, в ячейке F4 записываем формулу =E4*D1 и копируем ее во все оставшиеся ячейки. При этом получается неожиданный результат (рис. 5а).
Рис. 5а. Неправильная организация вычислений с параметром.
В этом случае использование относительной адресации привело к ошибке – запомнив взаимное расположение ячеек результата и исходных данных (заработка первого в списке и ставки налога) программа ЭТ повторяет это взаимное расположение для остальных строк списка (в чем можно убедиться, войдя в режим редактирования, как показано на рис. 5а). Чтобы не создавать дополнительный столбец с одним и тем же значением ставки налога, в соответствующей формуле надо использовать абсолютный адрес ячейки, содержащей параметр (в данном случае – значение ставки налога). Для указания абсолютного адреса к букве столбца или номеру строки добавляется префикс «$» и формула для расчета суммы налога приобретает вид =E4*$D$1 (для добавления символов «$» при редактировании формулы можно использовать комбинацию клавиш
Рис. 5б. Правильная организация вычислений с параметром.
В режиме редактирования теперь видно, что во всех ячейках при вычислении суммы налога происходит обращение к ячейке, содержащей ставку налога, независимо от строки таблицы.
Нужно заметить, что формулы позволяют связывать между собой не только ячейки в пределах одного листа, но и ячейки на разных листах документа. В результате можно строить «трехмерные » электронные таблицы.
4.ВСТРОЕННЫЕ ФУНКЦИИ И «МАСТЕР ФУНКЦИЙ»
Программа электронной таблицы OOo Calc позволяет использовать несколько сотен встроенных функций различных категорий.
Каждая функция имеет скобки, в которых записываются аргументы функции (например SIN(A8), PRODUCT(число1;число2;…), PI() ). В электронной таблице в качестве аргумента может быть указано число, адрес ячейки, диапазон адресов ячеек, другая функция или не указано ничего (как в функциях PI() или TRUE() ).
Функции участвуют в формулах для вычислений. Для построения формул с функциями в ЭТ OOo Calc имеется система автоматизированного построения формул («Мастер функций»). Вызывается эта система через команды меню «Вставка/Функция. » или нажатием на кнопку f(x) на панели инструментов программы ЭТ. Вариант окна для выбора функций показан на рис. 6. Для упрощения выбора функции сгруппированы по категориям.
Рис. 6. Диалог выбора функции.
При выборе конкретной функции «Мастер функций» помогает правильно построить формулу в интерактивном режиме.
Рис. 7. Определение аргументов функции.
Кнопки с зеленой стрелочкой справа от полей ввода (кнопки «Уменьшить») позволяют сворачивать диалог определения функции и указывать адреса ячеек, содержащих аргументы функции с помощью щелчка мышью (или выделения диапазона мышью, если аргументо
м может быть диапазон ячеек).
При определении аргументов функции можно сразу же увидеть результат ее работы. Если в результате получается ошибка, это значит, что аргументы не определены или определены неправильно.
Знакомство со встроенными функциями начнем с категории математических функций.
Обычно имеется несколько десятков математических функций. Обязательно наличие тригонометрических функций (sin, cos, tg и т.д.) и функций преобразования чисел или результатов вычислений (квадратный корень, целая часть, остаток от деления и пр.). Особое место занимает функция суммирования (SUM() ).
Для выбора математических функций в списке категорий функций выбираем «Математические». В таблице ниже приведены некоторые наиболее часто используемые математические функции.
Более подробное и полное описание всех функций (не только математических) можно найти в документации по OpenOffice.org Calc (http://documentation.openoffice.org/manuals/oooauthors2/index.html ).
Функции COUNTIF() и SUMIF() занимают особое место, поскольку позволяют проводить вычисления при выполнении каких-то условий. Более подробно они будут подробно рассмотрены позже.
Для примера рассмотрим использование функции SUM(). В описанной выше задаче посчитаем общую сумму налогов. Для этого перейдем в ячейку под столбцом налогов (например, в F13) и вызовем «Мастер функций». Выберем категорию «Математические», среди математических найдем функцию SUM и на следующем шаге укажем аргумент в виде диапазона ячеек F4:F11. Диапазон можно либо ввести вручную, либо свернуть диалоговое окно и выделить ячейки с суммами налогов с помощью мыши. Завершается формула, как обычно, нажатием на клавишу
Однако в ЭТ для суммирования ячеек есть специальная кнопка «Автосуммирование» (см. описание интерфейса). Используем эту возможность для вычисления суммы всех заработков. Для этого поместим указатель активной ячейки в E13 и нажмем на кнопку «Автосуммирование». Получим результат, показанный на рис. 8.
Рис. 8. Использование автосуммирования.
При использовании автосуммирования автоматически выбирается непрерывный диапазон чисел выше или левее ячейки с результатом. Пустая ячейка считается нулем. При желании можно исправить адреса ячеек непосредственно в строке ввода. Нажатие на
Построим в OOo Calc двумерную и трехмерную диаграммы по результатам работы некоторых математических функций.
В качестве двумерной (2D) диаграммы построим график функции F(x)=exp(-x)cos(Nx), где N – натуральное число. Для построения графика сначала нужно построить таблицу данных. Пусть данные располагаются по столбцам, переменная x изменяется в диапазоне от 0 до 3 радиан, шаг по x составляет 0,1 радиана. Для получения значений x удобно к первому значению прибавить шаг, а потом скопировать формулу. Пример данных показан на рис. 9.
Рис. 9. Фрагмент таблицы данных для графика.
Для построения графика выделяем всю область данных, включая заголовки столбцов, а затем выбираем в меню команду «Вставка/Диаграмма. «.
Далее следует серия диалоговых окон, в которых можно выбрать данные для подписей, лист для диаграммы, тип и вид диаграммы. Выбираем для построения диаграммы новый лист, тип диаграммы – «XY», вариант графика – B-сплайн. Определяем заголовок диаграммы и подписи по осям X и Y. После нажатия на кнопку «ГОТОВО» диаграмма появляется на указанном листе. При необходимости можно изменить размер диаграммы с помощью мыши. Настройка вида диаграммы (графика) осуществляется выделением нужных объектов, для которых правой кнопкой мыши вызывается контекстное меню. Можно изменять вид объектов диаграммы, делать их видимыми или невидимыми. Окончательный вид графика показан на рис. 10. Попробуйте добиться такого же.
Рис. 10. График функции F(x)=exp(-x)cos(Nx), N=3.
В качестве примера трехмерной (3D) диаграммы построим график функции:
Пусть x изменяется сверху вниз, а y – слева направо. Сделаем сетку чисел 20×20 в соответствии с заданной функцией, учитывая, что формулы можно копировать не только по вертикали, но и по горизонтали. При копировании формул не надо забывать про возможность указания абсолютного адреса для строки или столбца.
В диалогах построения диаграммы выбираем опять-таки новый лист для диаграммы, тип диаграммы – трехмерные линии. Отказываемся от легенды, т.к. каждый столбец интерпретируется как отдельный ряд данных. Результат показан на рис. 11.
Рис. 11. Трехмерная диаграмма.
Видно, что построение 3D-диаграмм не является сильной стороной OOo Calc. Здесь диаграммы квази-трехмерные, глубина используется как средство придания выразительности. Для построения настоящих трехмерных диаграмм можно использовать свободные математические пакеты Maxima или SciLab, рассмотрение которых далеко выходит за рамки настоящего пособия.3.
7. ЛОГИЧЕСКИЕ ФУНКЦИИ
Логические функции используются для вычислений с условиями и формирования этих условий. Если условие выполняется, выполняется один набор команд, а если не выполняется – другой набор команд. Для формирования условий в формулах ЭТ используется функция IF(). Она имеет три аргумента. Первый аргумент – условие, второй аргумент – формула, которая работает при выполнении условия, третий аргумент – формула, которая работает если условие не выполняется.
Пусть, например, ячейка D5 содержит формулу «=IF(A1
В аргументах функции IF() могут использоваться другие логические функции – AND(), OR(), NOT().
Функция NOT (логическое отрицание) имеет формат:
NOT(условие).
Эта функция принимает значение «ИСТИНА», если условие не выполняется и наоборот.
В OOo Calc определены еще две логические функции – TRUE() и FALSE(). Они выдают значения соответственно ИСТИНА и ЛОЖЬ.
В ЭТ возможно использование более сложных логических конструкций с использованием вложенных функций IF(), когда IF() используется в качестве аргумента другой функции IF().
Например, сложная функция
выполняет следующие действия: если значение в ячейке A1 меньше 100, то выводится текстовое значение «ку-ку». В противном случае проверяется условие вложенной функции IF(). Если значение в ячейке A1 равно 100 выводится текстовое значение «ура», иначе выводится значение из ячейки C1. Toт же результат может быть получен с помощью выражения
В качестве примера использования математических и логических функций рассмотрим задачу о вычислении вещественных корней квадратного уравнения с произвольными коэффициентами. Если дискриминант отрицательный, выведем сообщение «Вещественных корней нет!». Вычислять будем во всех случаях оба корня.
Итак, заданы три коэффициента квадратного уравнения (A, B и C). Перед вычислением корней вычислим предварительно значение дискриминанта D и его будем использовать при формировании формул для вычисления корней. Таблица с данными и с формулами показана на рис. 12 (формулы показаны справа от ячеек, в которых они реально работают). Самостоятельно проверьте варианты с D=0 и D0.
Рис. 12. Задача о решении квадратного уравнения.
Календарные функции в программах ЭТ предназначены для работы с датами. Дата в программе ЭТ формируется с использованием системного таймера как количество дней, прошедших с какого-то начала отсчета. ЭТ OOo Calc позволяет задавать начальную дату («нулевой день»). Количество дней, прошедших от нулевого, преобразуется с помощью календаря операционной системы в структуру ДД/ММ/ГГГГ, где ДД – номер дня в месяце, ММ – номер месяца в году, ГГГГ – номер года нашей эры (например, 28/04/2003 означает 28 апреля 2003 года). В зависимости от формата ячейки, содержащей дату, представление может быть изменено (например, вместо 28/04/2003 получится 28 апр.)
Календарные функции обычно находятся в категории “Дата и время”, однако в настоящем пособии функции для работы со временем (т.е. часами, минутами, секундами) не рассматриваются.
Рассмотрим основные функции для работы с датами.
Функция TODAY() не имеет аргументов и выдает значение текущей даты в стандартном формате.
Функции DAY(адрес_ячейки), MONTH(адрес_ячейки) и YEAR( адрес_ячейки) выдают, соответственно, номер дня в месяце, номер месяца в году и номер года для даты, записанной в ячейке » адрес_ячейки «.
Функция WEEKDAY(адрес_ячейки;тип) выдает номер дня недели для даты, записанной в ячейке » адрес_ячейки». Обычно отсчет начинается с воскресенья («тип» не указан или «тип»=1), как это принято в США. Если указать «тип»=2, то первым днем недели будет считаться понедельник.
Даты можно сравнивать друг с другом и вычитать. Необходимо отметить, что разность дат выражается количеством дней. К дате можно прибавить нужное количество дней, в результате также получится дата. Более ранние даты (близкие к началу отсчета) меньше, чем более поздние.
В качестве примера использования календарных функций рассмотрим следующую задачу.
Дан список лиц, для которых известны фамилии, пол и даты рождения. Определить:
a.День недели, на который приходится день рождения каждого человека в текущем году. Если день рождения приходится на выходные дни, вывести текст «УРА!», в остальных случаях вывести текст «УВЫ. «.
b.Возраст на настоящий момент.
c.Дату выхода на пенсию для каждого человека.
Для этой задачи воспользуемся списком из задачи про доходы и налоги. Пол установим в соответствии с фамилиями, даты рождения введем произвольно (удобно вводить дату в виде 23/11/88, а программа приведет ее в нужный вид).
Для получения решения по пункту «a» необходимо проделать некоторые промежуточные вычисления. Сначала нужно для каждого лица сформировать дату рождения в текщем году на основании дня и месяца рождения, а также номера текщего года. Тогда первая формула (в ячейке D4 на рис. 13) будет иметь вид:
Соответственно, формула для окончательного результата по пункту «а» (в ячейке E4) будет иметь вид:
Для обработки всего списка просто копируем эти формулы вниз.
Для получения решения по пункту «b» воспользуемся функцией YEARS() и запишем формулу для возраста в следующем виде:
Поскольку возраст определяется как количество полных лет, прошедших с даты рождения, третий аргумент функции YEARS() нужно установить в 0. Если он будет установлен в 1, будет вычисляться просто разность годов (например, 2007-1956), что даст неверный результат.
Для получения решения по пункту «c» снова нужно формировать даты, используя значения параметров возраста выхода на пенсию. Эти возрасты на момент написания пособия составляют 60 лет для мужчин и 55 для женщин, однако они могут в любой момент измениться, поэтому конкретные числа в формулу записывать не будем. Дата выхода на пенсию формируется с использованием условия проверки пола. Итак, получаем формулу:
Рис. 13. Задача на календарные функции.
Если в ячейке, содержащей формулу, вызвать «Мастер функций», то будет показана структура формулы. Пример структуры формулы для вычисления даты выхода на пенсию показан на рис. 14. Структура формулы помогает найти ошибки при большом количестве вложенных функций.
Рис. 14. Структура формулы в «Мастере функций».
9.Выбор одного из многих вариантов. Ассоциативные массивы.
Ассоциативный массив – это структура данных, оформленная в виде таблицы, первый столбик которой содержит так называемые «ключи» – данные, которые участвуют в формировании условий. В следующих столбиках ассоциативного массива содержатся значения, соответствующие ключам. Таким образом, по значению ключа можно однозначно получить какие-то другие данные. (В языках программирования такие структуры называются «хэш-таблицы»).
В программах ЭТ ассоциативные массивы реализуются как блоки ячеек (справочные таблицы), содержащие минимум два столбца. Первыи столбец содержит ключи, второй – значения, соответствующие ключам. Функции для работы с ассциативными массивами – LOOKUP(), VLOOKUP() и HLOOKUP() – находятся в категории «Электронная таблица».
Для примера рассмотрим следующую задачу. В ралли участвуют гонщики на автомобилях, для которых известны марки и расход топлива в литрах на 100 км. Дан список, в котором указаны фамилии гонщиков и марки автомобилей. Известна протяженность трассы гонок в километрах L. Определить расход топлива для каждого участника гонок.
Для решения задачи составим справочную таблицу для 7-ми разных марок а/м, и расположим ее в ячейках F3:G10 (рис. 15). Полезно соблюдать алфавитный порядок текстовых значений в первом столбике справочной таблицы. Длину трассы L, которая является параметром, запишем в ячейку E1.
Рис. 15. Пример ассоциативного массива.
Данные (список участников и марки их а/м) запишем в ячейки A3:B10 (рис. 16). Для вычисления полного расхода топлива используем формулу с функцией LOOKUP().
Рис. 16. Исходные данные для задачи о расходе топлива.
Формула в ячейке C4 будет выглядеть следующим образом.
Функция LOOKUP() считывает содержание ячейки, указанной в первом аргументе, ищет это значение в столбце, указанном во втором аргументе и выдаёт соответствие этому значению в столбце, указанном в третьем аргументе. То есть, по названию а/м находим расход топлива на 100 км, а потом смотрим, сколько сотен километром проехали а/м и производим простое умножение. Принципиально важно указывать абсолютные адреса блоков ячеек справочной таблицы.
Итоговая таблица показана на рис. 17.
Рис. 17. Результат вычислений с использованием ассоциативного массива.
Ограничение функции LOOKUP() – только один столбец соответствий. Более «мощной» является функция VLOOKUP(), в которой второй аргумент определяет весь блок ячеек, содержащих ассоциативный массив, а третий аргумент указывает, в каком столбце ассоциативного массива нужно искать соответствие ключу. Четвертый (необязательный) аргумент определяет порядок сортировки первого («ключевого») столбца. Если он не указан или равен 1 (логическая ИСТИНА) то первый столбец ассоциативного массива для функции VLOOKUP() должен содержать числа, отсортированные по возрастанию, или текст, отсортированный в алфавитном порядке. Если значения в первом столбце не отсортированы, то четвертый аргумент должен быть установлен в 0. Еще одним большим достоинством функции VLOOKUP() является возможность работы с диапазонами значений ключа.
Рис. 18. Данные для задачи о вычислении налогов.
Справочную таблицу размещаем в диапазоне F3:G7 (рис. 19), формула в ячейке C4 с использованием функции VLOOKUP() выглядит следующим образом:
Рис. 19. Ассоциативный массив для функци VLOOKUP().
Во втором столбце ассоциативного массива находим ставку налога, соответствующую доходу, а затем получаем сумму налога, умножая ставку налога на величину дохода (рис 20).
Рис. 20. Использование VLOOKUP() для интервалов значений ключа.
Важно, что VLOOKUP() выбирает значения, соответствующие нижней границе диапазона значений ключей, и используется минимальное значение ключа в интервале.
Четвертый аргумент функции VLOOKUP() также влияет и на возможность интервального просмотра. Если этот аргумент имеет значение ИСТИНА (1) или опущен, то интервальный просмотр работает, как описано выше. Если этот аргумент имеет значение ЛОЖЬ (0), то функция VLOOKUP() ищет точное соответствие. Если таковое не найдено, то возвращается значение ошибки #N/A (#Н/Д – «нет данных»). Таким образом, для использования возможности работы с интервалами значений в VLOOKUP() первый столбец справочной таблицы (ассоциативного массива) обязательно должен быть отсортирован по возрастанию.
Функция HLOOKUP() работает аналогично VLOOKUP(), только порядок следования «ключей» – не сверху вниз, а слева направо.
LOOKUP(), VLOOKUP() и HLOOKUP() заменяют вложенные IF(), и в сочетании с логическими функциями составляют мощный инструмент обработки данных в электронных таблицах.
10.СТАТИСТИЧЕСКИЕ ФУНКЦИИ. ВЫЧИСЛЕНИЯ С КРИТЕРИЕМ.
Рассмотрим следующую задачу.
Дан список участников соревнования среди студентов по бегу на 100 метров и метанию мяча. В таблице указаны пол (юноша или девушка) и результаты. Определить места каждому участнику в каждом виде соревнований, минимальный, максимальный и средний результаты в каждом виде соревнований, на сколько юноши (в среднем) метают мяч дальше, чем девушки.
Для решения этой задачи нам понадобятся статистические функции MIN(), MAX() и AVERAGE(), а также функции для вычислений с условием COUNTIF() и SUMIF(), которые занимают особое место среди математических функций OOo Calc.
Сначала кратко изучим упомянутые функции.
MIN(число1;число2. ) и MAX(число1;число2. ) — позволяют найти минимальное и максимальное число среди аргументов, которые могут быть числами или ссылками на ячейки с числами. Может использоваться один аргумент – диапазон ячеек.
AVERAGE(число1;число2. ) — позволяет найти среднее арифметическое аргументов. Также может использоваться один аргумент – диапазон ячеек.
COUNTIF(диапазон; условие) — позволяет подсчитать количество ячеек в заданном диапазоне, значения которых удовлетворяют некоторому условию.
SUMIF(диапазон; условие; диапазон_суммирования) — позволяет подсчитать сумму ячеек по заданному условию.
Теперь перейдем непосредственно к решению задачи.
Заполняем таблицу исходными данными, как показано на рис. 21.
Рис 21. Исходные данные для задачи о соревнованиях
Сразу под списком в соответствующих столбцах подсчитываем минимальный, максимальный и средний результаты (по столбцу С =MIN(C2:C16), =MAX(C2:C16),
=AVERAGE(C2:C16) и аналогично по столбцу D).
Затем подсчитываем количество юношей и количество девушек. В ячейку B22 вносим формулу
а в ячейку B23 формулу
В ячейки C22 и C23 записываем формулы для подсчета суммы результатов по метанию для юношей и девушек соответственно
После чего подсчитываем среднее значение в ячейках D22 и D23, разделив сумму результатов на количество участников в каждой группе. Затем подсчитываем разницу средних значений (рис. 22).
Рис 22. Решение задачи о соревнованиях.
11.РАБОТА С МАССИВАМИ ПРИ АНАЛИЗЕ ДАННЫХ
Рассмотрим некоторые приемы анализа экономических данных на примере следующей задачи.
На основе эксперимента получена следующая зависимость посещаемости дискотеки от входной платы
Очевидно, что оптимуму соответствует максимальная выручка. Соответственно. решение задачи состоит в том, чтобы подсчитать выручку в каждом случае, найти максимальную и написать формулу, показывающую входную плату, соответствующую максимальной выручке. То есть нужно определить номер столбика, в котором получается максимальная выручка и вывести значение входной платы. Для решения задачи будем использовать функции INDEX() и MATCH() (категория «Электронная таблица»). Рассмотрим формат этих функций:
Функция INDEX() имеет две формы. Рассмотрим более простую:
INDEX(массив; номер_строки; номер_столбца) — находит значение элемента, находящегося в заданном массиве на пересечении заданных строки и столбца.
Определяем выручку в каждом случае (умножив входную плату на количество посетителей), затем функцией MAX() находим наибольшую выручку. После этого функцией MATCH() определяем, на каком месте в массиве она находится и функцией INDEX() смотрим, какая входная плата находится на этом месте (рис. 23).
Рис. 23. Решение задачи о дискотеке.
В заключение, рассмотрим еще одну задачу анализа данных и, соответственно, некоторые новые функции.
Итак, задача: на основе данных из задачи о соревнованиях (п. 10) распределить места в соревнованиях по бегу среди юношей, а также определить, сколько человек показали результаты ниже среднего (т.е. время больше среднего).
Для решения задачи потребуется статистическая функция RANK() для распределения участников по местам, а также уже знакомый COUNTIF() для вычисления с условием. Однако условие для COUNTIF() обязательно должно быть текстом, поэтому при формировании условия с вычисляемыми данными целесообразно использовать текстовую функцию CONCATENATE(). Результат использования этих функций показан на рис. 24, причем формула с функцией RANK() приведена один раз, чтобы не загромождать иллюстрацию. Отметим, что для обеспечения возможности копирования формулы использованы абсолютные адреса диапазона.
Рис. 24. Распределение по местам и вычисление с условием.
Как видно из рис. 24, функция RANK() имеет три аргумента. Первый – значение, место (ранг) которого определяется. Второй аргумент – диапазон, в котором происзодит распределение по местам. Наконец, третий аргумент – указатель порядка сортировки. Если третий аргумент 0 или не указан, места распределяются по убыванию значений (т.е. чем больше – тем лучше, 1-е место – максимальное знаение). Если же поставить 1, то места будут распределяться по возрастанию (т.е. чем меньше, тем лучше, как в рассмотренном случае).
Использование функции CONCATENATE() для формирования условия позволяет работать с вычисляемыми значениями, т.е. приведенную таблицу можно использовать для любых результатов. А если вынести знак сравнения также в отдельную ячейку, можно быстро менять условия анализа данных.