Информационный центр "Центральный Дом Знаний"
Онлайн всего: 1 Гостей: 1 Пользователей: 0
|
Обзор встроенных функций MS Excel Оглавление Введение…………………………………………………………………стр. 3 1. Синтаксис функций…………………………………………………..стр. 4 2. Ввод функций…………………………………………………………стр. 5-6 3. Статистические функции…………………………………………….стр. 7-9 4. Функции прогнозирования…………………………………………..стр. 10-11 5. Финансовые функции………………………………………………...стр. 12-16 Заключение………………………………………………………………стр. 17 Практическая часть…………………… ………………………………стр. 18-23 Список литературы……………………………………………………...стр. 24 Введение Программным продуктом, незаменимым в офисной работе, является электронная таблица Microsoft Excel. ее использование, а в частности встроенных функций, облегчает работу многих специалистов. Функции дополняются и модернизируются, поэтому работать становится легче. При помощи этого продукта можно анализировать большие массивы данных. В Excel можно использовать более 400 математических, статистических, финансовых и других специализированных функций, связывать различные таблицы между собой, выбирать произвольные форматы представления данных, создавать иерархические структуры. Воистину безграничны методы графического представления данных: помимо нескольких десятков встроенных типов диаграмм, можно создавать свои, настраиваемые типы, помогающие наглядно отразить тематику диаграммы. Таким образом, мною выбрана именно эта тема курсовой работы, так как она не теряет своей актуальности и в будущей профессиональной деятельности работа с функциями MS Excel не мало важна. В практической части своей курсовой работы я пользовалась пакетом прикладной программы MS Excel. Цель данной курсовой работы - изучение характеристик встроенных функций MS Excel, их синтаксис и использование, основной задачей является более подробное рассмотрение функций, облегчающих экономическую деятельность. Синтаксис функций Формулы, которые вы записываете в ячейки электронных таблиц, могут включать в себя встроенные функции. К встроенным функциям относится, в частности, и функция суммирования СУММ. Вообще, встроенные функции Ехсеl широко применяются при простых и сложных вычислениях, облегчая процесс ввода и обработки данных. Кроме того, использование встроенных функций избавляет от длинных записей и снижает вероятность ошибок. Модуль встроенных функций в Ехсеl обладает чрезвычайно дружественным интерфейсом. При обращении к той или иной функции вам предлагается некий шаблон, который нужно заполнить. Причем правила записи функций вы можете представлять себе лишь в общих чертах. Остановимся на том, как должно выглядеть обращение к функции в Ехсеl. Запись функции начинается с указания имени функции, затем следует список аргументов, заключенный в скобки. Аргументы — это величины, которые используются для вычисления значения функции. Результат вычисления функции называется возвращаемым значением. Скобки, ограничивающие список аргументов, всегда должны быть парными; пробелы перед скобками или после них не допускаются. Список аргументов может состоять из чисел, ссылок, текста, массивов, логических величин (ИСТИНА или ЛОЖЬ), значений ошибок (например, #ДЕЛ/0). Отдельные аргументы в списке должны быть разделены запятыми. Некоторые функции могут иметь до 30 аргументов. Ехсеl допускает вложение функций друг в друга, то есть использование функции в качестве аргумента для другой функции, Функция, которая является аргументом другой функции, называется вложенной. Уровень вложенности функций в формулах Ехсеl не должен превышать семи. Ввод функции Функции вставляются в формулы, а последние начинаются со знака =. Если формулу начать с названия функции, то знак равенства будет подставлен автоматически. Функция включается в формулу с помощью мастера функций, который упрощает процесс вставки функции в строку формул. Вызывается мастер через меню Вставка • Функция. Диалог Мастер функций (Рис. 1.1) содержит два списка: Категория и Функция, в первом списке нужно выбрать категорию, в которой находится интересующая вас функция, а во втором - саму функцию. Всего в наборе Ехсеl имеется 10 категорий функций: финансовые инженерные, дата и время, математические, статистические ссылки и массивы, работа с базой данных, текстовые, логические, проверка свойств и значений. Кроме того, список категорий дополнен строкой 10 недавно использовавшихся, которая Рис. 1.1 . Диалог Мастер функций содержит список функций чаще всего использовавшихся в последнее время. Встроенных функций, которые производят различные типы вычислений, насчитывается более 200. В диалоге Мастер функций под списками находится строка описания синтаксиса и дается комментарий о назначении выделенной функции. В строке синтаксиса аргументы, выделенные полужирным шрифтом, являются обязательными. Если обязательные для функции аргументы не будут указаны, то ее невозможно будет ввести в ячейку. Когда вы отмечаете функцию в списке, автоматически появляются названия аргументов функции с указанием правильного расположения скобок, запятых и точек с запятой в строке формул. Выбрав нужную функцию, щелкните по кнопке ОК, после чего откроется следующий диалог мастера функций, называемый палитрой формул или панелью формул (см. диалог на переднем плане Рис. 1.2). Отметим, что другим способом вызова палитры формул является щелчок по кнопке «=» в строке формул и выбор нужной функции из списка в левой части строки формул. Рис. 1.2. Палитра формул в случае вычисления даты В палитре формул вам будет предложено ввести аргументы именно в том формате, который предусмотрен для данной функции. Аргументы можно вводить с клавиатуры, но желательно при вводе ссылок использовать мышь, что ускорит процесс ввода и гарантирует от ошибок. При вводе одинаковых или похожих функций в различные ячейки можно использовать возможности копирования в Ехсеl. Выделите ячейку, содержащую копируемую функцию, щелкните по кнопке Копировать и затем, поместив курсор в ячейку назначения, нажмите Enter. Скопированная функция будет представлять собой формулу с относительными адресами. Если это вас не устраивает, поменяйте относительные адреса на абсолютные с помощью клавиши F4.1 _______________ 1 [1, С. 527-529] Классификация встроенных функций MS Excel: - финансовые; - дата и время; - математические; - статистические; - ссылки и массивы; - работа с базой данных; - логические; - текстовые; - проверка свойств и значений. Статистические функции 1). Подсчет количества значений в диапазоне. Для подсчета количества числовых значений в диапазоне: СЧЕТ (ЗНАЧЕНИЕ1;ЗНАЧЕНИЕ2;...) Пример1. В ячейки В1,В2,В3,В4,В5 введена информация: В1=«Текст», В2=2000, В3= , В4=800, В5= 1500. Требуется подсчитать количество ячеек с числовыми значениями в диапазоне В1:В5. Результат должен быть получен в ячейке В6. Пошаговыми действиями Мастера функций в ячейку В6 следует ввести формулу: =СЧЕТ (В1:В5). В ячейке В6 получится число 3. 2). Подсчет количества пустых ячеек в диапазоне. СЧИТАТЬПУСТОТЫ (ДИАПАЗОН). Пример2. В ячейки С1,С2,С3,С4,С5 скопируйте информацию из соответст-вующих ячеек столбца А, оставив пустой ячейку С3: С1=1000, С2=2000, С3=, С4=800, С5== 1500. Требуется подсчитать количество пустых ячеек в диапазонеС1:С5. Результат должен быть получен в ячейке С6. Пошаговыми действиям Мастера функций в ячейку С6 следует ввести формулу: =СЧИТАТЬПУСТОТЫ(С1:С5). В ячейке С6 получится число 1. 3). Подсчет количества непустых ячеек в диапазоне, удовлетворяющих заданному условию. СЧЕТЕСЛИ (ДИАПАЗОН; УСЛОВИЕ). ДИАПАЗОН - это диапазон, в котором определяется критерий; УСЛОВИЕ - указывается в виде числа, выражения или текста и определяет какие ячейки надо подсчитывать. Пример3.Подсчитать количество непустых ячеек в диапазоне С1:С5, значение которых больше или равно 1000. Результат должен быть получен в ячейке С7. Пошаговыми действиями Мастера функций в ячейку С7 следует ввести формулу: =СЧЕТЕСЛИ(С1:С5;”>=1000”). В ячейке С7 получится число 3. 4). Расчет среднего значения. СРЗНАЧ(ДИАПА3ОН1; ДИАПАЗОН2;...) В текущую ячейку возвращается среднее значение для чисел указанного диапазона. Пример4. В ячейки А1,А2,А3,А4,А5 введена информация: А1=1000, А2=2000, А3=900, А4=800, А5=1500. Определить среднее значение в диапазоне ячеек А1:А5. Результат должен быть получен в ячейке А7. Пошаговыми действиями Мастера функций в ячейку А7 следует ввести формулу: =СРЗНАЧ (А1:А5). 5). Определение максимального значения. МАКС(ДИАПАЗОН1; ДИАПАЗОН2;...). В текущую ячейку возвращается максимальное число из данного диапазона. Пример5. В диапазоне ячеек А1:А5 из примера 4 определить максимальное значение. Результат должен быть получен в ячейке А8. Пошаговыми действиями Мастера функций в ячейку А8 следует ввести формулу: =МАКС (А1:А5). В ячейке А8 получится число 2000. 6). Определение минимального значения. МИН(ДИАПАЗОН1; ДИАПАЗОН2;...). В текущую ячейку возвращается минимальное число из данного диапазона. Пример6. В диапазоне ячеек В1:В5 из примера 1 определить минимальное значение. Результат должен быть получен в ячейке В8. Пошаговыми действиями Мастера функций в ячейку В8 следует ввести формулу: =МИН(В1:В5). В ячейке В8 получится число 800. 7.) Определение ранга числа. РАНГ(АДРЕС ЯЧЕЙКИ; ДИАПАЗОН). В текущую ячейку возвращается величина, соответствующая положению (рангу) числа, заданного адресом ячейки, в указанном диапазоне. Пример7. В ячейки D1,D2,D3,D4,D5 скопируйте информацию из соответст- вующих ячеек столбца А. Для каждой ячейки из диапазона D1:D5 определить ранг числа. Результат должен быть получен в ячейках Е1:Е5. Функция ранга вводится сначала в ячейку Е1, затем копируется для всех ячеек до Е5. Пошаговыми действиями Мастера функций в ячейку Е1 вводим формулу: =РАНГ(D1;1:5) - знак $ устанавливает абсолютные адреса, чтобы диапазон ячеек не менялся при копировании. После копирования формулы вниз для всех ячеек до Е5 получим ранги для каждого значения диапазона. Ранг числа с максимальным значением в диапазоне D1:D5 будет равен 1, а с минимальным - 5. 8). Определение процентной нормы числа. ПРОЦЕНТРАНГ(ДИАПАЗОН; АДРЕС ЯЧЕЙКИ). В текущую ячейку возвращается величина, определяющая процентную долю числа, заданного адресом ячейки, от максимального значения в указанном диапазоне. Действие функции аналогично функции ранг, только ранг определяется в процентном отношении (максимальное число принимается за 100%; минимальное — за 0%). Пример8. Для значений ячеек D1,D2,D3,D4,D5 определить процентную долю каждого числа от максимального значения в диапазоне. Результат должен быть получен в ячейках F1:F5. Для этих ячеек задайте процентный формат. Функции прогнозирования 1). Функция ПРЕДСКАЗ (Х; Известные значения Y; Известные значения Х). Функция ПРЕДСКАЗ вычисляет или предсказывает будущее значение по существующим значениям. Х-это точка данных, для которой предсказывается значение. Известные значения Y-это зависимый массив или интервал данных. Известные значения Х-это независимый массив или интервал данных. Пример9.1. В качестве примера выполнить расчет ожидаемой прибыли на 2000 год на основе данных о полученной прибыли за 6 мес. за1995—2000 гг. и в целом за год за 1995—1999 гг., а также рассчитать ожидаемую прибыль на 2001 год, используя функцию ПРЕДСКАЗ. Оформить и заполнить таблицу, приведенную на Рис. 1.3. Результат прогнозирования на 2000 год должен быть получен в ячейке С9. Установить курсор в ячейку С9, задать команду Вставка>Функция> ПРЕДСКАЗ. И в появившемся диалоговом окне заполнить поля. В ячейке С9 должна быть формула =ПРЕДСКАЗ (В9;С4:С8;В4:В8). В ячейке С9 получится число 1690,4797. Результат прогнозирования на 2001 год должен быть получен в ячейке С10. В ячейке С10 должна быть формула =ПРЕДСКАЗ(А10;С4:С9;А4:А9). В ячейке С10 получится число 1783,6531. Рис. 1.3. Расчет ожидаемой прибыли на 2001 год с помощью функции ПРЕДСКАЗ 2). Функция РОСТ(Известные значения Y; Известные значения Х; Новые значения Х; КОНСТ). Функция РОСТ рассчитывает прогнозируемый экспоненциальный рост на основании имеющихся данных. Функция РОСТ возвращает значения Y для последовательности новых значений Х, задаваемых с помощью существующих Х- и Y-значений. Функция рабочего значения РОСТ может применяться также для аппроксимации существующих Х- и Y-значений экспоненциальной кривой. Известные значения Y-это множество значений Y, которые уже известны для соотношения Y=b*m^Х. Известные значения Х-это необязательное множество значений Х, которые уже известны для соотношения Y=b*m^Х. Новые значения Х-это новые значения Х, для которых РОСТ возвращает соответствующие значения Y. КОНСТ — это логическое значение, которое указывает, требуется ли, чтобы константа b была равна 1. Пример9.2. Выполнить расчет ожидаемой прибыли на 2001 год на основе данных, приведенных на Рис. 1.3. Установить курсор в ячейку С11, задать команду Вставка>Функция>РОСТ и в появившемся диалоговом окне заполнить поля. В ячейке С11 должна быть формула РОСТ (С4:С9;А4:А9;А11). В ячейке С11 получится число 1801,0942 3). Функция ТЕНДЕНЦИЯ(Известные значения Y; Известные значения Х; Новое значение Х; КОНСТ). В текущую ячейку возвращается новое значение Х, рассчитанное на основании известных значений. Выполняется линейная аппроксимация. Пример9.3. В качестве примера выполнить расчет ожидаемой прибыли на 2001 год на основе данных, приведенных на рисунке 1.3. Результат прогнозирования должен быть получен в ячейке С12. Установить курсор в ячейку С12, задать команду Вставка>Функция>ТЕНДЕНЦИЯ, и в появившемся диалоговом окне заполнить поля. В ячейке С12 должна быть формула =ТЕНДЕНЦИЯ(С4:С9;А4:А9;А12), получится число1783, 6531. Финансовые функции Оценка ежемесячных выплат. ППЛАТ(НОРМА;КПЕР;НЗ;БС;ТИП). • НОРМА — норма прибыли за период займа; • КПЕР — общее число периодов выплат годовой ренты; • НЗ — текущая стоимость: общая сумма всех будущих платежей с настоящего момента; • БС — будущая стоимость или баланс наличности, которую нужно достичь после последующей выплаты; • ТИП — логическое значение(0 или 1), обозначающее, должна ли производиться выплата в конце периода (0) или в начале периода (1). Функция ППЛАТ может быть использована для анализа всевозможных ссуд. Необходимым условием является непротиворечивость аргументов функции. Пример10. Предположим, что нужно воспользоваться 9-процентной 15-летней ссудой. Объем ссуды составляет 150000000 рублей. С помощью Мастера функций можно определить величины ежемесячных выплат. Предварительно следует привести все другие значения к месячной норме. Ввести таблицу (Рис. 1.4), начиная с ячейки А15: Рис. 1.4. Определение величины ежемесячных выплат В ячейки В16 и В17 ввести соответствующие формулы. Процентная ставка (НОРМА)-годовая, поэтому для получения месячной ставки(Удельная ставка) соответствующее значение делится на 12 (0,09/12). Срок действия ссуды — 15 лет, поэтому с учетом 12 платежей в год общее количество месячных выплат (КПЕР) составит 12×15. Для ячейки В20 пошаговыми действиями Мастера функций выполните настройку функции ППЛАТ. После этого в поле Значение диалогового окна Мастера функций вы увидите сумму ежемесячного взноса. А после нажатия на кнопку Готово результат отобразится в ячейке. Пример11. Определить какими будут выплаты по ссуде при меняющейся процентной ставке. В ячейки А22:В26 введите следующие значения, оставив пустой строку перед числовыми значениями (Рис. 1.5): Рис.1.5.Определение величины ежемесячных выплат с использованием таблицы подстановки В ячейку В23 скопировать формулу для расчета ежемесячных выплат. Для расчета выплат по каждой из ставок воспользуйтесь возможностью автоматической подстановки значений в нужную ячейку (в нашем случае в В15). Для этого нужно: 1. Выделить диапазон А23:В26, включив в него значения процентных ставок и расчетную формулу (формула должна находиться в ячейке, расположенной правее и выше заданных значений). 2. В меню данные выбрать команду Таблица подстановки. 3. В поле «Подставлять значения по строкам в:» указать ячейку В15. Рядом с каждой процентной ставкой появится соответствующий результат. Функция БЗ Функция БЗ предназначена для расчета будущей стоимости периодических постоянных платежей и единой суммы вклада или займа на основе постоянной процентной ставки. Б3 — будущее значение, возвращает будущее значение вклада на основе периодических постоянных платежей и постоянной процентной ставки. Синтаксис: Б3 (СТАВКА; КПЕР; ПЛАТА; Н3; ТИП). СТАВКА — это процентная ставка за период. КПЕР — это общее число периодов выплат годовой ренты. ПЛАТА — это выплата, производимая в каждый период; это значение не может меняться в течение всего периода выплат. Обычно плата состоит из основного платежа и платежа по процентам, но не включает других налогов и сборов. Н3 — это текущая стоимость, или общая сумма всех будущих платежей с настоящего момента. Если аргумент Н3 опущен, то он полагается равным 0. ТИП — это число 0 или 1, обозначающее, когда должна производиться выплата: 0-в конце периода, 1-в начале периода. Если аргумент опущен, то он полагается равным 0. Для аргументов СТАВКА и КПЕР используются согласованные единицы измерения. Если производятся ежемесячные платежи по четырехгодичному займу из расчета 12% годовых, то СТАВКА должна быть 12%/12, а КПЕР должно быть 4*12. Если производятся ежегодные платежи по тому же займу, то СТАВКА должна быть 12%, а КПЕР должно быть 4.(......) |
Loading
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||