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

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

На тему: «Обзор встроенных функций MS Excel» 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Москва  – 2010

ОГЛАВЛЕНИЕ 
 
 
 
 
 
 
 
 

ВВЕДЕНИЕ

 

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

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

    Электронная таблица – это программа, моделирующая на экране двумерную таблицу, состоящую из строк и столбцов. Основной задачей электронной таблицы является ввод данных в ячейки и обработка их по формулам. [2, C. 270]

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

    Microsoft Excel – это программа для работы с электронными таблицами.

    Основными функциями программы MS Excel являются:

• ввод и редактирование данных, автоматизация ввода (включая автозаполнение, автозамену и проч.);
• форматирование табличных данных с использованием стандартных средств, стилей, шаблонов;
• выполнение вычислений по формулам (при этом может использоваться обширный набор встроенных функций);
• анализ табличных данных (вывод промежуточных и общих итогов, создание сводных таблиц, подбор параметров, прогнозирование решений);
• графическое представление данных (построение графиков, диаграмм; включение рисунков, видеоматериалов, географических карт);
• работа со списками (упорядочение и фильтрация записей, поиск данных);
• коллективная работа с таблицами (обмен файлами в локальной сети, совместное использование и защита данных, обмен данными через Интернет);
• разработка программных приложений, основанных на встроенном языке программирования VBA (Visual Basic for Applications). [2, C. 271]

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

1. ОБЩЕЕ ПРЕДСТАВЛЕНИЕ О ФУНКЦИЯХ MICROSOFT EXCEL

1.1. Понятие функции в MS Excel

 

    Функции Excel - это специальные, заранее созданные  формулы, которые позволяют легко  и быстро выполнять сложные вычисления. Их можно сравнить со специальными клавишами на калькуляторах, предназначенных для вычисления квадратных корней, логарифмов и проч. [1]

    Программа Excel предлагает широкий набор встроенных функций, которые существенно облегчают процесс обработки данных, избавляют от длинных записей формул и снижают вероятность ошибок. Некоторые функции являются эквивалентами длинных математических формул, которые можно сделать самому. А некоторые функции в виде формул реализовать невозможно. [2, C. 289]

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

1.2. Синтаксис функций

 

    Рассмотрим, как должно выглядеть обращение  к функции в Excel.

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

    МАКС(число 1, число 2 … )

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

    Скобки, ограничивающие список аргументов, всегда должны быть парными. Пробелы перед  скобками или после них не допускаются. При использовании в функции нескольких аргументов они отделяются один от другого точкой с запятой. Список аргументов может состоять из чисел, ссылок, текста, массивов, логических величин (Истина или Ложь), значений ошибок (например, #ДЕЛ/0).

    Некоторые функции могут иметь до 30 аргументов. Некоторые функции не имеют аргументов.

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

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

    СУММ(1, 10, В2:В8)

    Эта формула возвращает значение, равное сумме чисел 1, 10 и чисел в ячейках от В2 до В8.

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

    СУММ(А1:С5, МАКС(D1:Е3))

функция МАКС, которая возвращает максимальное значение в диапазоне D1:Е3, является вложенной. [2, C. 284-285]

1.3. Ввод функций

 

    Функции вставляются в формулы, а формулы  начинаются со знака =. Если формулу  начать с названия функции, то знак равенства будет подставлен автоматически. [3]

    Знак  равенства в начале формулы означает, что введена именно формула, а не текст. Если знак равенства будет отсутствовать, то Excel воспримет ввод просто как текст.

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

    Вызывается мастер через меню ВСТАВКА – ФУНКЦИЯ. Диалог Мастер функций содержит два списка: КАТЕГОРИЯ и ФУНКЦИЯ. В первом списке нужно выбрать категорию, в которой находится интересующая вас функция, а во втором – саму функцию.

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

    Выбрав  нужную функцию и щелкнув по кнопке ОК, откроется следующее диалоговое окно мастера функций, называемое палитрой формул. Аргументы можно вводить  с клавиатуры, но желательно при  воде ссылок использовать мышь, что  ускорит процесс ввода и гарантирует от ошибок. [2, C. 290-291]

1.4. Диагностика ошибок в формулах

 

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

    Приведем  список возможных значений ошибок:

• ##### - означает, что ширина ячейки недостаточна для размещения в ней числа, даты или времени;
• #ИМЯ? – невозможность распознать используемое имя. Эта ошибка возникает, когда неправильно указано имя объекта или имеется ссылка на имя, которое было удалено, когда неверно записана функция и т.д.;
• #ЗНАЧ! – попытка некорректного использования функции;
• #ЧИСЛО! – появляется при неправильном представлении или использовании чисел;
• #ССЫЛКА! – означает неправильное употребление ссылок, имеющихся в формуле;
• #ДЕЛ/0! – попытка деления на нуль;
• #ПУСТО! – значение ошибки, появляющееся при задании в ссылке пустого множества ячеек;
• #Н/Д! – неопределенные данные. Это значение ошибки обычно специально вводят в ячейки, чтобы предотвратить вычисления в этих ячейках. [2, C. 293]

1.5. Классификация функций в MS Excel

 

    Функции в MS Excel можно разделить на несколько категорий:

• математические – СУММ, ПРОИЗВЕД, COS, SIN и т.д.;
• финансовые (осуществляют такие расчеты, как вычисление суммы платежа по ссуде, величину выплаты прибыли на вложения и др.);
• статистические – МАКС, МИН, СРЗНАЧ и т.д.;
• текстовые – ТЕКСТ, РУБЛЬ;
• ссылки и массивы (позволяют осуществить поиск данных в списках или таблицах, найти ссылку на ячейку в массиве);
• логические – функции для записи условия (ЕСЛИ, И, ИЛИ, НЕ и т.д.);
• дата и время.

    Рассмотрим  подробнее каждый из видов функций. 
 
 

2. МАТЕМАТИЧЕСКИЕ ФУНКЦИИ

 

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

• Функция СУММ (SUM) суммирует множество чисел. Эта функция имеет следующий синтаксис:

    =СУММ(числа)

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

• Функция ОКРУГЛ (ROUND) округляет число, задаваемое ее аргументом, до указанного количества десятичных разрядов и имеет следующий синтаксис:

    =ОКРУГЛ(число;количество_цифр)

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

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

• Для выполнения операций округления можно использовать функции ЧЁТН (EVEN) и НЕЧЁТ (ODD). Функция ЧЁТН округляет число вверх до ближайшего четного целого числа. Функция НЕЧЁТ округляет число вверх до ближайшего нечетного целого числа. Отрицательные числа округляются не вверх, а вниз. Функции имеют следующий синтаксис:

    =ЧЁТН(число)

    =НЕЧЁТ(число)

• Функции ОКРВНИЗ (FLOOR) и ОКРВВЕРХ (CEILING) тоже можно использовать для выполнения операций округления. Функция ОКРВНИЗ округляет число вниз до ближайшего кратного для заданного множителя, а функция ОКРВВЕРХ округляет число вверх до ближайшего кратного для заданного множителя. Эти функции имеют следующий синтаксис:

    =ОКРВНИЗ(число;множитель)

    =ОКРВВЕРХ(число;множитель)

    Значения  число и множитель должны быть числовыми и иметь один и тот  же знак. Если они имеют различные  знаки, то будет выдана ошибка.

• Функция ЦЕЛОЕ (INT) округляет число вниз до ближайшего целого и имеет следующий синтаксис:

    =ЦЕЛОЕ(число)

    Аргумент - число - это число, для которого надо найти следующее наименьшее целое число.

• Функция ОТБР (TRUNC) отбрасывает все цифры справа от десятичной запятой независимо от знака числа. Необязательный аргумент количество_цифр задает позицию, после которой производится усечение. Функция имеет следующий синтаксис:

    =ОТБР(число;количество_цифр)

    Если  второй аргумент опущен, он принимается  равным нулю.

• Функция СЛЧИС (RAND) генерирует случайные числа, равномерно распределенные между 0 и 1, и имеет следующий синтаксис:

    =СЛЧИС()

    Функция СЛЧИС является одной из функций EXCEL, которые не имеют аргументов. Как и для всех функций, у которых  отсутствуют аргументы, после имени  функции необходимо вводить круглые  скобки.

    Значение  функции СЛЧИС изменяется при  каждом пересчете листа. Если установлено  автоматическое обновление вычислений, значение функции СЛЧИС изменяется каждый раз при воде данных в этом листе.

• Функция ПРОИЗВЕД (PRODUCT) перемножает все числа, задаваемые ее аргументами, и имеет следующий синтаксис:

    =ПРОИЗВЕД(число1;число2...)

• Функция ОСТАТ (MOD) возвращает остаток от деления и имеет следующий синтаксис:

    =ОСТАТ(число;делитель)

    Значение  функции ОСТАТ - это остаток, получаемый при делении аргумента число  на делитель. Если число меньше чем делитель, то значение функции равно аргументу число. Если число точно делится на делитель, функция возвращает 0. Если делитель равен 0, функция ОСТАТ возвращает ошибочное значение.

• Функция КОРЕНЬ (SQRT) возвращает положительный квадратный корень из числа и имеет следующий синтаксис:

    =КОРЕНЬ(число)

    Аргумент  число должен быть положительным  числом. Если число отрицательное, КОРЕНЬ возвращает ошибочное значение.

• Функция ЧИСЛОКОМБ (COMBIN) определяет количество возможных комбинаций или групп для заданного числа элементов. Эта функция имеет следующий синтаксис:

    =ЧИСЛОКОМБ(число;число_выбранных)

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

• Функция ЕЧИСЛО (ISNUMBER) определяет, является ли значение числом, и имеет следующий синтаксис:

    =ЕЧИСЛО(значение)

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

    =LOG(число;основание)

    Если  аргумент основание не указан, то Excel примет его равным 10.

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

    =LN(число)

• Функция ПИ (PI) возвращает значение константы пи с точностью до 14 десятичных знаков. Синтаксис:

    =ПИ()

• Функция SIN возвращает синус угла и имеет следующий синтаксис:

    =SIN(число)

    Здесь число - угол в радианах.

• Функция COS возвращает косинус угла и имеет следующий синтаксис:

    =COS(число)

    Здесь число - угол в радианах. [1] 
 
 
 
 

3. СТАТИСТИЧЕСКИЕ ФУНКЦИИ

 

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

• ВЕРОЯТНОСТЬ - значение вероятности того, что значение из интервала находится внутри заданных пределов.

    Синтаксис: ВЕРОЯТНОСТЬ(х_интервал, интервал_вероятностей, нижний_предел, верхний_предел)

    Если аргумент верхний_предел не задан, то возвращается значение вероятности того, что значения в аргументе х_интервал равны значению аргумента нижний_предел.

• ДИСП - дисперсия выборки. Аргументы рассматриваются как выборка из генеральной совокупности.

    Синтаксис: ДИСП(число1, число2, ...)

• КОВАР - ковариация (среднее произведений отклонений для каждой пары точек данных). Ковариация используется для определения связи между двумя множествами данных.

    Синтаксис: КОВАР(массив1, массив2)

• КОРЕЛ - коэффициент корреляции между интервалами ячеек аргументов массив1 и массив2. Коэффициент корреляции используется для определения наличия взаимосвязи между двумя свойствами.

    Синтаксис: КОРЕЛ(массив1, массив2)

• МАКС - наибольшее значение в списке аргументов.

    Синтаксис: МАКС(число1, число2, ...)

• МЕДИАНА - медиана заданного множества чисел (число, которое является серединой множества чисел: половина чисел больше, чем медиана, а половина чисел меньше, чем медиана).

    Синтаксис: МEДИАНА(число1, число2, ...)

• МИН - Наименьшее значение в списке аргументов.

    Синтаксис: МИН(число1, число2, ...)

• РАНГ - Ранг числа в списке чисел. Ранг числа - это показатель его величины относительно других значений в списке. (Если список отсортировать, то ранг числа будет его позицией.)

    Синтаксис: РАНГ(число, ссылка, порядок)

• СРЗНАЧ - Среднее значение (среднее арифметическое) аргументов.

    Синтаксис: СРЗНАЧ(число1, число2, ...) 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

4. ТЕКСТОВЫЕ ФУНКЦИИ

 

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

• Функция ТЕКСТ (TEXT) преобразует значение в текст в заданном числовом формате.

    Синтаксис: =ТЕКСТ(значение;формат)

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

• Функция РУБЛЬ (DOLLAR) преобразует число в текстовый формат, используя денежный формат с округлением до заданного числа десятичных знаков. Применяется следующий формат: #.##0,00р; -#.##0,00р.

    Синтаксис: =РУБЛЬ(число;число_знаков)

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

• Функция ДЛСТР (LEN) возвращает количество символов в текстовой строке и имеет следующий синтаксис:

    =ДЛСТР(текст)

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

• Функция СИМВОЛ - символ, соответствующий ASCII-коду числа, заданного аргументом число. Функция CHAR используется для преобразования текстовых файлов в текстовый формат данного компьютера.

    Синтаксис: =СИМВОЛ(число)

    Аргумент - число от 1 до 255, представляющее собой ASCII-код символа.

• Функция СОВПАД

    Синтаксис: =СОВПАД(текст1,текст2)

    Значение  будет ИСТИНА, если аргументы текст1 и текст2 абсолютно одинаковы, в противном случае - значение ЛОЖЬ.

• Функции ПРОПИСН, СТРОЧН и ПРОПНАЧ

    В Excel имеются три функции, позволяющие  изменять регистр букв в текстовых  строках: ПРОПИСН (UPPER), СТРОЧН (LOWER) и ПРОПНАЧ (PROPER).

    Функция ПРОПИСН преобразует все буквы  текстовой строки в прописные, а СТРОЧН - в строчные. [1]

    Функция ПРОПНАЧ заменяет прописными первую букву в каждом слове и все  буквы, следующие непосредственно  за символами, отличными от букв; все  остальные буквы преобразуются  в строчные. Эти функции имеют  следующий синтаксис:

    =ПРОПИСН(текст)

    =СТРОЧН(текст)

    =ПРОПНАЧ(текст) 
 
 
 
 
 
 
 
 
 

5. ЛОГИЧЕСКИЕ ФУНКЦИИ

 

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

• Функция ЕСЛИ - возвращает одно значение, если аргумент лог_выражение при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ. Синтаксис:

    =ЕСЛИ(лог_выражение, значение_если_истина, значение_если_ложь)

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

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

    Синтаксис: =И(логическое_значение1,логическое_значение2,...)

    Аргументы должны быть логическими значениями, массивами или ссылками, которые  содержат логические значения. Если аргумент, который является ссылкой или  массивом, содержит тексты или пустые ячейки, то такие значения игнорируются. Если указанный интервал не содержит логических значений, то функция И возвращает значение ошибки #ЗНАЧ!.

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

    Синтаксис: =ИЛИ(логическое_значение1,логическое значение2,...)

• Функция ИСТИНА - Логическое значение ИСТИНА. Аргумента нет.

    Синтаксис: =ИСТИНА()

• Функция ЛОЖЬ - Логическое значение ЛОЖЬ. Аргумента нет.

    Синтаксис: =ЛОЖЬ()

• Функция НЕ - заменяет логическое значение аргумента на противоположное. Функция НЕ используется в тех случаях, когда необходимо иметь уверенность в том, что значение не равно некоторой конкретной величине.

    Синтаксис: =НЕ(логическое_значение)

    Если аргумент логическое_значение имеет значение ЛОЖЬ, то функция НЕ возвращает значение ИСТИНА; если аргумент логическое_значение имеет значение ИСТИНА, то функция НЕ возвращает значение ЛОЖЬ. [1] 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

6. ДАТА И ВРЕМЯ

 

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

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

• Функция ВРЕМЗНАЧ