Организация вычислений в среде MS Excel. Формулы и функции

 

 

Введение

 

Microsoft Office - семейство программных продуктов  Microsoft, которое объединяет самые  популярные в мире приложения  в единую среду, идеальную для  работы с информацией. В Microsoft Office входят текстовый процессор Microsoft Word, электронные таблицы Microsoft Excel, средство подготовки и демонстрации презентаций Microsoft PowerPoint и новое приложение Microsoft Outlook. Все эти приложения составляют Стандартную редакцию Microsoft Office. В Профессиональную редакцию входит также СУБД Microsoft Access.

Microsoft Excel – программа предназначенная для организации данных в таблице для документирования и графического представления информации.

Программа MS Excel, являясь лидером  на рынке программ обработки электронных  таблиц, определяет тенденции развития в этой области. Вплоть до версии 4.0 программа Excel представляла собой фактический  стандарт с точки зрения функциональных возможностей и удобства работы. Теперь на рынке появилась версия 5.0, которая  содержит много улучшений и приятных неожиданностей.

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

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

 

 

 

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

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

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

Основным  достоинством электронной таблицы Excel является наличие мощного аппарата формул и функций.

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

Цель  работы – вычисления в MS Excel.

Для достижения цели были поставлены ряд задач:

- рассмотреть  формулы и операторы в MS Excel;

- изучить  разновидности функций в MS Excel.

 

 

 

 

 

 

 

 

 

1. Формулы в MS Excel

1.1.1Операторы в MS Excel

 

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

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

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

В Microsoft Excel включено четыре вида операторов: арифметические, текстовые, операторы сравнения  и операторы ссылок.

Служат  для выполнения арифметических операций, таких как сложение, вычитание, умножение. Операции выполняются над числами. Используются следующие арифметические операторы (Приложение 1). [1, с. 303]. 

 Операторы сравнения.   Используются для сравнения двух значений. Результатом сравнения является логическое значение: либо ИСТИНА, либо ЛОЖЬ. (Приложение 2).

 Текстовый оператор конкатенации.   Амперсанд (&) используется для объединения нескольких текстовых строк в одну строку. (Приложение 3)

Операторы ссылки.   Для описания ссылок на диапазоны ячеек используются следующие операторы. (Приложение 4) .

Порядок выполнения формул. 

Формулы вычисляют значения в определенном порядке. Формула в Microsoft Excel всегда начинается со знака равенства (=). Знак равенства свидетельствует о  том, что последующие знаки составляют формулу. Элементы, следующие за знаком равенства, являются операндами, разделяемыми операторами вычислений. Формула

 

 

 вычисляется  слева направо, в соответствии  с определенным порядком для  каждого оператора в формуле.

Если  в одной формуле используется несколько операторов, Microsoft Excel выполняет  операции в порядке, показанном в  следующей таблице 1. Если формула содержит операторы с одинаковым приоритетом — например операторы деления и умножения — они выполняются слева направо.

Оператор	Описание
: (двоеточие)    (один пробел) , (запятая)	Операторы ссылок.
–	Знак «минус»
%	Процент
^	Возведение в степень
* и /	Умножение и деление
+ и –	Сложение и вычитание
&	Объединение двух текстовых строк  в одну
= < > <= >= <>	Сравнение

Таблица 1 

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

1.1.2Ссылки, относительные и абсолютные

 

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

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

 

 

Имя - это легко запоминающийся идентификатор, который можно использовать для ссылки на ячейку, группу ячеек, значение или формулу. Создать имя для ячейки можно в поле имени, или через меню Вставка | Имя. | Присвоить... Использование имен обеспечивает следующие преимущества:

Формулы, использующие имена, легче  воспринимаются и запоминаются, чем  формулы, использующие ссылки на ячейки[2, с. 202].

Например, формула “=Активы-Пассивы” гораздо понятнее, чем формула  “=F6-D6”.

При изменении структуры рабочего листа достаточно обновить ссылки лишь в одном месте - в определении  имен, и все формулы, использующие эти имена, будут использовать корректные ссылки.

После того как имя определено, оно может использоваться в любом  месте рабочей книги. Доступ ко всем именам из любого рабочего листа можно  получить с помощью окна имени  в левой части строки формул.

Вы можете также определить специальные  имена, диапазон действия которых ограничивается текущим рабочим листом. Это означает, что эти имена можно использовать лишь на том рабочем листе, на котором  они определены. Такие имена не отображаются в окне имени строки формул или окне диалога “Присвоить имя”, если активен другой рабочий лист книги.

Excel автоматически создает имена  на основе заголовков строк  и столбцов рабочего листа.  Подробной информация о создании  таких имен содержится в главе  “Базы данных”.

После того, как имя определено, вы можете:

Заменить все соответствующие  ссылки этим именем во всех местах рабочего листа[3, с. 145].

Например, определив имя “Прибыль”  как “=$F$12”, можно заменить все  ссылки на ячейку $F$12 именем “Прибыль”.

Быстро перейти на поименованную  ссылку, заменить ссылки, вставить ссылку в формулу с помощью окна имени  в строке формул.

 

 

 

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

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

При копировании формул возникает необходимость  управлять изменением адресов ячеек  или ссылок. Для этого перед  символами адреса ячейки или ссылки устанавливаются символы “$”. Изменяться только те атрибуты адреса ячейки, перед  которыми не стоит символ “$”. Если перед всеми атрибутами адреса ячейки поставить символ “$”, то при копировании  формулы ссылка не изменится.

Например, если в записи формулы ссылку на ячейку D7 записать в виде $D7, то при  перемещении формулы будет изменяться только номер строки “7”. Запись D$7 означает, что при перемещении  будет изменяться только символ столбца  “D”. Если же записать адрес в виде $D$7, то ссылка при перемещении формулы  на этот адрес не изменится, и в  расчетах будут участвовать данные из ячейки D7. Если в формуле указан интервал ячеек G3:L9, то управлять можно  каждым из четырех символов: “G”, “3”, “L” и “9”, помещая перед ними символ “$”.

Если  в ссылке используются символы $, то она называется абсолютной, если символов $ в ссылке нет - относительной. Адреса таких ссылок называются абсолютными и относительными, соответственно.

Абсолютные  адреса при перемещении формул не изменяются, а в относительных  адресах происходит смещение на величину переноса[3, с. 302]

1.2.1  Функции в MS Excel

1.2.1 Понятие функций в MS Excel

 

 

Для выполнения табличных вычислений нужны формулы. Поскольку некоторые  формулы и их комбинации встречаются  очень часто, то программа Excel

 

 

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

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

В программе имеется упорядоченный  по алфавиту полный список всех функций, в котором можно легко найти  функцию, если известно ее имя; в противном  случае следует производить поиск  по категориям. Многие функции различаются  очень незначительно, поэтому при  поиске по категориям полезно воспользоваться  краткими описаниями функций, которые  предлагает Конструктор функций. Функция  оперирует некоторыми данными, которые  называются ее аргументами. Аргумент функции  может занимать одну ячейку или размещаться  в целой группе ячеек. Конструктор  функций оказывает помощь в задании  любых типов аргументов[5, с. 99]. 

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

Последовательность, в которой должны располагаться  используемые в формуле символы, называется синтаксисом функции. Все функции используют одинаковые основные правила синтаксиса. Если вы нарушите правила синтаксиса, Excel выдаст сообщение о том, что в формуле имеется ошибка. Если функция появляется в самом начале формулы, ей должен предшествовать знак равенства, как и во всякой другой формуле. Аргументы функции  записываются в круглых скобках сразу за названием функции и отделяются друг от друга символом точка с запятой “;”. 
Скобки позволяют Excel определить, где начинается и где заканчивается список