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

КУРСОВАЯ  РАБОТА

по  дисциплине  «Информатика»

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

Содержание

     1. Введение

2. Организация вычислений в среде MS Excel. Формулы и функции
3. Практическая часть. Вариант 12
4. Заключение
5. Список использованной литературы
6. Приложения

1. Введение

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

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

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

    Для раскрытия данной темы необходимо описать  основные составляющие  MS Excel. 
 
 
 

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

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

    Формулы - это выражение, начинающееся со знака равенства и состоящее из числовых величин, адресов ячеек, функций, имен, которые соединены знаками арифметических операций. К знакам  арифметических операций, которые используются в Excel относятся: сложение; вычитание; умножение; деление; возведение в степень.

    Ввод  формул.

    Адрес ячейки можно включить в формулу одним щелчком мыши. Например, вместо того, чтобы «вручную» набирать =C6+C7+..., можно сделать следующее:

• ввести «=»;
• щелкнуть мышью на ячейке C6 (ее адрес появится в формуле);
• ввести «+»;
• щелкнуть на C7 и т. д.

    Копирование формул.

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

    Например, выделить ячейку С10. Установите указатель мыши на черный квадратик в правом нижнем углу курсорной рамки (указатель примет форму черного крестика). Нажмите левую кнопку и смещайте указатель вправо по горизонтали, - так, чтобы смежные ячейки D10 и E10 были выделены пунктирной рамкой. Отпустите кнопку мыши.

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

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

    Этим  же способом можно копировать в смежные  ячейки числа и тексты.

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

      Функции  - в Excel используются для выполнения стандартных

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

вычисления  функций, называются аргументами. Значения, возвращаемые

функциями в качестве ответа, называются результатами. Помимо встроенных

функций вы можете использовать в вычислениях  пользовательские функции,

которые создаются при помощи средств Excel.

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

    В процессе конструирования выражений, содержащих формулы, существенную помощь пользователю оказывает Мастер функций. Вызов Мастера функций осуществляется либо из меню Вставка —> Функции, либо с помощью одноименной кнопки на панели инструментов Стандартная. Далее в появившемся окне диалога необходимо выбрать категорию функций и далее - нужную функцию из категории (рис. 1). Использование окна Мастер функций оказывает помощь при задании аргументов функций. В качестве аргументов функции могут использоваться другие функции и адреса ячеек.

Рис. 1.  Мастер функций 

    Работая с функциями, помните:

1. функция, записанная в формуле, как правило, возвращает уникальное значение: арифметическое, символьное или логическое;
2. существуют функции, которые не возвращают значение, а выполняют некоторые операции (например, объединяют текстовые строки);
3. существуют функции без аргументов (например, функция ПИ (возвращает число П = 3.1415...)).

        Использование функций

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

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

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

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

        Типы функций

       Для удобства работы функции в Excel разбиты  по категориям: функции управления базами данных и списками, функции  даты и времени, DDE/Внешние функции, инженерные функции, финансовые, информационные, логические, функции просмотра и  ссылок. Кроме того, присутствуют следующие  категории функций: статистические, текстовые и математические.

       Схема1. – Классификация функций 

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

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

       В Excel широко представлены математические функции. Например, можно выполнять  различные операции с матрицами: умножать, находить обратную, транспонировать.

       Функции просмотра и ссылок позволяет «просматривать» информацию, хранящуюся в списке или таблице, а также обрабатывать ссылки.

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

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

      Они используются всякий раз, когда необходимо выполнить те или иные действия в  зависимости от выполнения каких-либо условий. В Excel имеются следующие логические функции: ЕСЛИ, И, ИЛИ, НЕ, ЛОЖЬ, ИСТИНА.

      Функция ЕСЛИ (арг_лог, арг1, арг2) возвращает значение арг1, если арг_лог при вычислении приобретает значение ИСТИНА, и арг2, если арг_лог приобретает значение ЛОЖЬ.

      Если  арг_лог имеет значение ИСТИНА и значение арг1 опущено, то возвращается значение ИСТИНА. Если арг_лог имеет значение ЛОЖЬ и значение арг2 опущено, то возвращается значение ЛОЖЬ.

      При конструировании более сложных  проверок в качестве значений аргументов арг1и арг2 могут быть вложены до семи функций ЕСЛИ.

      Функция ЕСЛИ используется при проверке условий для значений и формул.

      Пример.

      1. Введите в ячейку Е3 формулу = ЕСЛИ(Е1= 5; «Правильный ответ»; «Ошибка»).

      2. Введите в ячейку Е1 число 4. В ячейке Е3 появилось слово «Ошибка».

      3. Введите в ячейку Е1 число 5. В ячейке Е3 появилась надпись «Правильный ответ». Такая конструкция позволяет создавать достаточно сложные тестовые программы, например, такие, в которых следует выбрать номер правильного варианта из многих.

      4. Результатом работы логических  функций ИСТИНА( ) и ЛОЖЬ( ) являются логические ИСТИНА или ЛОЖЬ.

      Функция НЕ меняет значение своего аргумента на противоположное логическое значение и обычно используется в сочетании с другими функциями. Эта функция возвращает логическое значение ИСТИНА, если аргумент имеет значение ЛОЖЬ, и логическое значение ЛОЖЬ, если аргумент имеет значение ИСТИНА. Например, следующая формула возвращает текст Прошел, если значение в ячейке А1 не равно 2: =ЕСЛИ(НЕ(А1=2);"Прошел";"Не прошел").

      Функция И возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.

      Хотя  функция ИЛИ имеет те же аргументы, что и И, результаты получаются совершенно различными. Например, следующая формула возвращает текст «Сдал», если средний балл больше 75 или если студент имеет меньше 5 пропусков занятий без уважительных причин: =ЕСЛИ(ИЛИ(С4<5;Р4>75%);"Сдал";"Не сдал"). Таким образом,  функция ИЛИ возвращает логическое значение ИСТИНА, если хотя бы одно из логических выражений истинно, а функция И возвращает логическое значение ИСТИНА, только если все логические выражения истинны.

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

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

       Функция ОКРУГЛ (число; число разрядов) округляет первый аргумент функции до числа знаков, заданного вторым аргументом. Число разрядов    — количество десятичных разрядов, до которого нужно округлить число. Если число разрядов больше 0, то число округляется до указанного количества десятичных разрядов справа от десятичной запятой. Если число разрядов равно 0, то число округляется до ближайшего целого. Если число разрядов меньше 0, то число округляется слева от десятичной запятой.

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

       Функция ОКРУГЛВВЕРХ, ОКРУГЛВНИЗ подобна функции ОКРУГЛ, за тем исключением, что ОКРУГЛВВЕРХ округление всегда производится с избытком. А при функции ОКРУГЛВНИЗ число всегда округляется с недостатком.

       Функция ПРОИЗВЕД перемножает все числа, задаваемые ее аргументами.   Excel   игнорирует любые пустые ячейки, текстовые и логические значения. Функция ПРОИЗВЕД  может иметь до  30   аргументов.

       Функция СУММ суммирует значение всех ячеек указанных диапазонов.

       Пример. Сумм (А10:С15; Е3:В8)

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

       Поскольку СУММ является очень популярной функцией, на стандартной панели инструментов имеется специальная кнопка для ввода этой функции.

       Функция КОРЕНЬ возвращает положительный квадратный корень числа.

       Аргумент  число должен быть положительным  числом. Например, следующая функция  возвращает значение 4: =КОРЕНЬ(16)

       Если   число   отрицательное,    КОРЕНЬ   возвращает   ошибочное   значение: =#ЧИСЛО!.

       Функция СТЕПЕНЬ(число; степень) возвращает результат возведения числа в степень. Число   - основание. Оно может быть любым вещественным числом. Степень  - показатель степени, в которую возводится основание.

       Например, следующая функция возвращает значение 25: =СТЕПЕНЬ(5;2)

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

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

       Функция ТЕКСТ(значение; формат) преобразует число в текстовую строку с заданным форматом. Аргумент значение может быть любым числом, формулой или ссылкой на ячейку. Аргумент формат определяет, в каком виде отображается возвращаемая строка. . Для задания необходимого формата можно использовать любой из символов форматирования ($, #, 0 и т. д.) за исключением звездочки (*).

      Функция ПОВТОР(текст; число повторений) повторяет текст заданное число раз. Функция ПОВТОР используется, чтобы заполнить ячейку некоторым количеством вхождений текстовой строки.

        Если «число повторений» равно  0 (нулю), то функция ПОВТОР возвращает "" (пустой текст).Если «число повторений» не целое, то оно усекается. Результат функции ПОВТОР не должен превышать 32 767 знаков, в противном случае ПОВТОР возвращает сообщение об ошибке #ЗНАЧ!.

      Функция СОВПАД сравнивает две строки текста и возвращает значение ИСТИНА, если они в точности совпадают, и ЛОЖЬ в противном случае. Функция СОВПАД учитывает регистр, но игнорирует различия в форматировании. Функция СОВПАД используется для того, чтобы проверить, входит ли некоторый текст в документ.

      Например, если ячейки С5 и А3 рабочего листа  содержат один и тот же текст. Тогда, следующая формула возвращает значение ИСТИНА: =СОВПАД(С5;А3)

      Функции ЕТЕКСТ и ЕНЕТЕКСТ проверяют, является ли значение текстовым.

       Предположим, вы хотите определить, является ли значение в ячейке А8 текстом. Если в ячейке А8 находится текст или формула, которая возвращает текст, можно  использовать формулу: =ЕТЕКСТ(А8). В этом случае Excel возвращает логическое значение ИСТИНА. Аналогично, если вы проверите ту же ячейку, используя формулу =ЕНЕТЕКСТ(А8) Excel возвращает логическое значение ЛОЖЬ.

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

       Excel поддерживает пять встроенных  логарифмических функций: LOG10, LOG, LN, ЕХР и СТЕПЕНЬ. В этом разделе  я расскажу только про функции  LOG, LN и ЕХР.

       Функция LOG(число; основание) возвращает логарифм положительного числа по заданному основанию.

       Например,  следующая  формула  возвращает значение 2,321928095,  то есть логарифм 5 по основанию 2: =LOG(5; 2). Если вы не укажете аргумент основание, Excel примет его равным 10.

       Функция LN возвращает натуральный (по основанию е) логарифм положительного числа, указанного в качестве аргумента.

       Например, следующая формула возвращает значение 0,693147181: =LN(2).

       Функция ЕХР вычисляет значение константы е (приблизительно 2,71828183), возведенной в заданную степень.

       Функция   ЕХР  является  обратной  по отношению  к  LN.   Например, пусть ячейка А1 содержит формулу =LN(8), тогда следующая формула возвращает значение 8: =ЕХР(А1).

    Дата  и время.

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

    Например, функция ВРЕМЯ

    ВРЕМЯ(часы,минуты,секунды)

    Результат: Значение времени в числовом формате, соответствующее введенным аргументам. Это десятичная дробь в интервале от 0 до 0,99999999, представляющая время суток от 0:00:00 (12:00:00 ночи) до 23:59:59 (11:59:59 вечера).

    Аргументы:

    часы - число от 1 до 24 (количество часов);

    минуты - число от 1 до 59 (количество минут);

    секунды - число от 1 до 59 (количество секунд).

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

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

3. Практическая  часть

 Предприятие ООО «Анаконда» сдает в аренду офисные помещения в центре города различным организациям. Данные для выполнения расчетов представлены на рис. 3.1 и 3.2.

 Для решения задачи необходимо следующее.

 1. Построить таблицы по данным, приведенным на рис. 3.1 и 3.2.

 2. Организовать межтабличные связи  с использованием функций ВПР  или ПРОСМОТР для автоматического  формирования общей стоимости  аренды помещений, включая дополнительные  услуги.

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

 4. Результаты расчетов доходов  от сдачи помещений в аренду  представить в графическом виде.

Арендуемые  площади организациями

Рис. 3.1. Данные об арендуемых площадях

Прайс-лист услуг, предоставляемых организациям, в месяц

Рис. 3.2. Данные о стоимости предоставляемых  услуг 
 

Доход от сдачи помещений в аренду

Рис. 3.3. Расчет дохода от сдачи помещений  в аренду

      Проанализировав вышеизложенное условие задачи, я сочла нужным выполнить ее при помощи табличного процессора MS Excel 2003.

     Его использование можно обосновать следующим:

     – в этом пакете есть все необходимые  для выполнения поставленной задачи средства расчетов;

     –     имеется развитая подсистема построения графиков и диаграмм;

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

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

    Современные табличные процессоры - универсальный  инструмент и менеджера, и экономиста, и бухгалтера.

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