Наряду с надстройкой Пакет анализа в практике статистической обработки могут широко применяться статистические функции Microsoft Excel. В состав Excel входит библиотека, содержащая 78 статистических функций, ориентированных на решение самых рааличных задач прикладного статистического анализа.
Причем одну часть статистических функций можно рассматривать как своего рода элементарные составляющие того или иного режима надстройки Пакет анализа , другую часть – как уникальные функции, не дублирующиеся в надстройке Пакет анализа .
Тем не менее, функции, входящие и в первую часть, и во вторую часть, имеют самостоятельное значение и могут применяться автономно при решении конкретных статистических задач.
Работать со статистическими функциями Excel, как, впрочем, и с функциями из других категорий, удобнее всего с помощью мастера функций. При работе с мастером функций необходимо сначала выбрать саму функцию, а затем задать ее отдельные аргументы. Запустить мастер функций можно командой Функция... из меню Вставка, или щелчком по кнопке вызова мастера функций f x , или активизацией комбинации клавиш Shift+F3
Для упрощения работы с мастером отдельные функции сгруппированы по тематическому признаку. Тематические категории представлены в области Категория (рис. 5). В категории Полный алфавитный перечень содержится список всех доступных Б программе функций, К категории 10 недавно использовавшихся относятся десять применявшихся последними функций. Поскольку пользователь во время работы применяет ограниченное число функций, то с помощью этой категории можно получить быстрый доступ к тем из них, которые необходимы в повседневной работе.
Чтобы задать статистическую функцию, сначала необходимо выбрать категорию Статистические , При перемещении строки выделения по списку функций под областями Категория и Функция будет представлен пример, иллюстрирующий способ задания выбранной статистической функции с краткой информацией о ней.
Если краткой информации недостаточно, щелкните в диалоговом окне по кнопке Справка . На экране появится помощник и предложит помощь. Щелкните по кнопке Справка по выделенной функции, и на экране будет представлена соответствующая страница справочной подсистемы.
После выбора функции щелкните по кнопке ОК для перехода в следующее диалоговое окно мастера функций, в котором должны быть заданы аргументы. В этом диалоговом окне мастер подсказывает пользователю, какие аргументы следует указать обязательно (обязательные аргументы), а какие - опционально (необязательные аргументы). Задать аргументы можно различными способами, наиболее удобные из них предлагает помощник. После задания всех аргументов функции щелкните по кнопке ОК, чтобы в ячейке появились результаты выполнения функции.
2. Определение характера распределения и формирование выборки
2.1. Теоретические основы группировки
Результаты сводки и группировки материалов статистического наблюдения оформляются в виде таблиц и статистических рядов распределения.
Группировка – объединение единиц статистической совокупности в количественные однородные группы в соответствии со значениями одного или нескольких признаков.
Статистический ряд распределения представляет собой упорядоченное распределение единиц изучаемой совокупности по определенному варьирующему признаку. Он характеризует состояние (структуру) исследуемого явления, позволяет судить об однородности совокупности, единицах ее изменения, закономерностях развития наблюдаемого объекта. Построение рядов распределения является составной частью сводной обработки статистической информации.
В зависимости от признака, положенного в основу образования ряда распределения, различают атрибутивные и вариационные ряды распределения. Последние, в свою очередь, в зависимости от характера вариации признака делятся на дискретные (прерывные) и интервальные (непрерывные) ряды распределения.
|
Пример дискретного ряда: Распределение медицинских халатов, реализованных магазином за месяц, по размерам.
|
Пример интервального ряда : Распределение покупок в аптеке по сумме. |
Группировка осуществляется поэтапно. Вначале определяется примерное число групп, затем величина интервала. Строится первый вариант группировки, который при необходимости уточняется. Для определения числа групп может применяться формула Стерджесса:
где N – численность совокупности, r – число групп.
Величина
интервала определяется по формуле:
,
где x max , x min – соответствующие максимальное и минимальное значения признаков совокупности, r – величина интервала. Полученный результат округляется.
Равные интервалы группировки применяются для однородных совокупностей, а для социально-экономических явлений чаще применяются неравноинтервальные группировки. Если крайнее значение единиц совокупности значительно отличается по величине от остальных, применяются группировки с открытыми границами интервалов.
Первый интервал с открытой нижней границей, последний интервал с открытой верхней границей. Величина первого интервала принимается равной величине следующего за ним интервала (не более чем). Величина последнего интервала с открытой верхней границей принимается равной величине предпоследнего интервала.
Различают абсолютные и относительные частотные характеристики.
Абсолютная характеристика – частота , показывает, сколько раз встречается в совокупности данный вариант ряда. Достоинство частоты – простота, недостаток – невозможность сравнительного анализа рядов распределения разной численности.
Для подобных сравнений применяют относительные частоты или частости , которые рассчитываются по формуле:
,
,
где N – численность совокупности.
Это относительная величина структуры (по форме).
Сумма частостей равна 1.
Если
частости выражены в процентах или в
промилях их суммы равны соответственно
100 или 1000.
В неравных интервальных рядах распределения частотные характеристики зависят не только от распределения вариантов ряда, но и от величины интервала при прочих равных условиях расширение границ интервала приводит к увеличению наполненности групп.
Для анализа рядов распределения с неравными интервалами используют показатели плотности:
Абсолютная
плотность
:
где f i – частота, c i - величина интервала – показывает, сколько единиц в совокупности приходится на единицу величины соответствующего интервала. Абсолютная плотность позволяет сопоставлять между собой насыщенность различных по величине интервалов ряда. Абсолютные плотности не позволяют, однако, сравнивать ряды распределения разной численности.
Для
подобных сравнений применяются
относительные
плотности
:
,
гдеd i
– частости (доли), c i
- величины соответствующих интервалов
– показывает, какая часть (доля)
совокупности приходится на единицу
величины соответствующего интервала.
Удобнее всего ряды распределения
анализировать с помощью их графического
изображения, позволяющего судить о
форме распределения. Наглядное
представление о характере изменения
частот вариационного ряда дают полигон
и гистограмма
.
Полигон используется для изображения дискретных вариационных рядов. При построении полигона в прямоугольной системе координат по оси абсцисс в одинаковом масштабе откладываются ранжированные значения варьирующего признака, а по оси ординат наносится шкала частот, т. е. число случаев, в которых встретилось то или иное значение признака. Полученные на пересечении абсцисс и ординат точки соединяют прямыми линиями, в результате чего получают ломаную линию, называемую полигоном частот. Например, на рис. 6. приведено распределение числа студентов по успеваемости и полигон частот для данного распределения. Для построения полигона воспользуемся мастером диаграмм Microsoft Excel (режим «График»).
Для изображения интервальных вариационных рядов распределений применяются гистограммы. При этом на оси абсцисс откладываются значения интервалов, а частоты изображаются прямоугольниками, построенными на соответствующих интервалах. В результате получается гистограмма – график, на котором ряд распределения представлен в виде смежных друг с другом областей. Для характеристики рядов распределения применяют так же графики накопленных частот или кумуляты .
Кумулята позволяет определить, какая часть совокупности обладает значениями изучаемого признака не превышающими заданного предела, а какая часть – наоборот – превышает этот предел.
Контрольная работа
По дисциплине
программные средства офисного назначения
Вариант 1
Выполнил:
Проверил:
Саратов 2004
АННОТАЦИЯ
Контрольная работа студента на тему "мастер функций, назначение и работа с ним" имеет объём 19 листов. Текст работы содержит 1 таблицу, 5 рисунков и 2 приложения.
При написании было использовано 7 источников.
Структура выглядит следующим образом. Работа состоит из введения, пяти глав и заключения.
В первой главе рассматриваются основные понятия, используемые при работе с Excel: рабочая книга, лист, ячейка.
Во второй главе даётся краткая характеристика самого понятия функция и происходит ознакомление с мастером функций.
В третьей главе рассматриваются основные принципы работы мастера функций.
В заключительных главах находится обзор некоторых формул, представленных в мастере формул, и описывается возможность их редактирования.
Заключение содержит выводы по контрольной работе.
План
Введение 4
1.Рабочая книга. Лист. Ячейка 5
2. Понятие функции. Мастер функций 6
3. Работа с мастером функций 7
4. Редактирование формул 10
5. Различные виды функций 10
Заключение 19
Список литературы 20 Вопрос 2. Расчет заработной платы 21
ВВЕДЕНИЕ
В современном мире персональных компьютеров электронные таблицы, подобные Excel 2002, стали таким же обычным явлением, как текстовые процессоры и компьютерные игры. Однако это не означает, что их хорошо знают и эффективно используют.
Как нам уже известно, Excel является организатором любого типа данных - числовых, текстовых и других - и поскольку в данной программе имеется множество вычислительных возможностей, то зачастую пользователи обращаются к ней, когда необходимо создать таблицы для финансовых расчетов.
Не меньшей популярностью пользуются и возможности Excel, которые служат для построения диаграмм, любых видов, и графиков. Excel без труда превращает скучные черно-белые стоки и столбцы в яркие и красочные графики и диаграммы.
Помимо всего выше сказанного, не следует упускать из виду тот факт, что Excel – превосходный хранитель баз данных и создатель таблиц. Именно по этому данная программа как нельзя лучше подходит для отслеживания информации о продаваемых товарах, обслуживаемых клиентах и т.п.
Как мы видим, возможности Excel 2002 очень велики и для их рассмотрения потребовалось бы много времени, но данная задача перед нами и не стоит: в этой работе мы обратим свое внимание на мастера функций, узнаем его назначение и порядок работы с ним.
1.Рабочая книга. Лист. Ячейка
Прежде, чем мы перейдем непосредственно к теме данной работы необходимо, на мой взгляд, вспомнить те понятия с которых, собственно и начинается работа с программой Excel. Итак, каждый файл Excel называется рабочей книгой. То есть, рабочая книга – это документ (файл), который мы открываем, сохраняем, копируем, удаляем… Каждая рабочая книга содержит три листа рабочих таблиц. Для того чтобы ориентироваться в них, в Excel предусмотрены ярлыки с именами рабочих листов от Лист1 до Лист3, похожие на закладки на обрезанных полях блокнота. Каждый лист в рабочей книге, в свою очередь, разбит приблизительно на 16 миллионов ячеек, в каждую из которых можно вводить данные.
|
На рисунке 1
Как мы видим, на рисунке 1 по краям рабочей таблицы Excel находится рамка с обозначениями строк и столбцов: столбцам (всего их 256) соответствуют буквы, а строкам – числа (от 1 до 65536). И столбцы и строки имеют большое значение, поскольку именно они составляют адрес ячейки, например А1. Подобная система адресации ячеек – это пережиток, унаследованный от VisiCalc. Но, кроме системы А1, Excel 2000 поддерживает еще более старую, но в тоже время более корректную систему адресации ячеек R1C1. В ней пронумерованы и строки (rows) и столбцы (columns) рабочей таблицы, причем номер строки предшествует номеру столбца.
Таким образом, когда мы открываем любой файл Excel, в нашем распоряжении оказывается 50331648 ячеек. Но если этого окажется мало, то к рабочей книге можно добавить дополнительные листы рабочих таблиц, в каждой из которых 16777216 ячеек.
2. Понятие функции. Мастер функций.
Итак, при помощи Excel можно анализировать большие массивы данных, создавать формулы из нескольких простых арифметических операций – сложения, вычитания, умножения и деления… Но вместо того, чтобы создавать с нуля сложные формулы с различными замысловатыми комбинациями этих операций, можно воспользоваться функциями Excel.
Функция – это заданная формула, выполняющая определенный тип вычислений. Для того чтобы воспользоваться функцией, необходимо предоставить ей все необходимые для вычисления величины – аргументы функции. Как и в случае с простыми функциями, аргументы большинства функций можно ввести тремя способами, а именно:
· Как числовое значение (например, 89 или – 5,76),
· Как координату ячейки (это наиболее распространенный вариант),
· Как диапазон ячеек (например, С3:F3).
Каждая функция должна начинаться со знака равенства (=), в противном случае Excel воспримет ее как текст. После знака равенства вводится имя функции, а затем вводятся все аргументы, необходимые для вычисления, причем все аргументы заключаются в круглые скобки.
После того, как введен знак равенства, имя функции и левая круглая скобка можно щелкнуть на ячейке (либо диапазоне ячеек), содержание которой будет использоваться в качестве первого аргумента функции, а не набирать координаты вручную.
Когда последний аргумент введен, следует набрать правую круглую скобку, чтобы обозначить конец списка аргументов. Затем необходимо щелкнуть на кнопке ВВОД в строке формул, или нажать
3. Работа с мастером функций
Безусловно, функцию можно ввести, набрав ее прямо в ячейке. Однако Excel предоставляет на стандартной панели инструментов кнопку Вставка функции . В открывшемся диалоговом окне (см. рис.2) Мастер функций – шаг 1 указывается нужная функция, затем Excel выводит диалоговое окно Аргументы функции , в котором необходимо ввести аргументы функции (рис. 3).
Вернемся вновь к первому диалоговому окну, рис. 2, здесь содержится три списка: Поиск функции, Категория и Функция. Причем Excel автоматически выбирает категорию функций 10 недавно использовавшихся, а в списке Функция отображает функции, к которым пользователь чаще всего обращается.
Если необходимой функции нет в списке, то необходимо выбрать соответствующую категорию из списка, а именно:
· 10 недавно использовавшихся,
· полный алфавитный перечень,
· финансовые,
· дата и время,
· математические,
· работа с базой данных,
· текстовые,
· логические,
· проверка свойств и значений.
После того, как выбор будет сделан в пользу одной из функций, в нижней части диалогового окна Выбор функций появится описание выбранной функции и ее аргументов. Итак, когда вы уже окончательно определились с выбором функции необходимо нажать ОК.
Происходящее далее рассмотрим на конкретном примере. Из списка функций мы выберем СУММ и как только мы это сделаем, программа внесет в ячейку =СУММ(), а в диалоговом окне Аргументы функции появятся поля, куда необходимо вести ее аргументы.
Чтобы выбрать аргументы, поместим точку вставки в поле Число1 и щелкнуть на ячейке электронной таблицы (или перетащить мышь, выделив нужный диапазон). После этого в текстовом поле Число1 появится адрес ячейки (либо адрес диапазона) и одновременно в окне справа – числовое значение, введенное в эту ячейку, а также внизу диалогового окна после слова Значение отразится итоговое значение функции.
В пользователя всегда есть возможность уменьшить диалоговое окно до размера поля Число1 и кнопки максимизации. Для этого достаточно щелкнуть по кнопке минимизации, находящейся справа от поля. Есть возможность и просто перетащить окно на другое место.
Если необходимо просуммировать содержимое нескольких ячеек, либо диапазонов, то нужно нажать клавишу
списка аргументов – появляется текстовое поле Число3 ). В поле Число2 указываем вторую ячейку (или диапазон).
Когда будут указаны все ячейки или диапазоны для суммирования, щелкаем кнопку ОК, чтобы закрыть диалоговое окно Аргументы функции и внести СУММ в текущую ячейку.
4. Редактирование формул
В Excel есть кнопка Изменить формулу , позволяющая редактировать формулы (прежде всего те, в которых содержаться функции) непосредственно в строке формул. Чтобы воспользоваться этой кнопкой (она обозначена знаком равенства и расположена непосредственно перед содержимым текущей ячейки, отображенным в строке формул), необходимо выбрать ячейку, содержащую формулу.
После щелчка на кнопке Изменить формулу
Excel открывает диалоговое окно Аргументы функции,
в котором можно отредактировать аргументы, указав новые ссылки на ячейки или диапазоны ячеек в текстовых полях Число1, Число2
и т.д. если необходимо заменить текущий аргумент, то его необходимо выделить, а затем удалить, нажав клавишу , и лишь после этого вводить новое значение аргумента.
Когда редактирование формулы будет завершено, щелкните на кнопке ОК
или нажмите
5. Различные виды функций
Завершая рассказ о функциях, мы рассмотрим некоторые из них более подробно. Итак, один из инструментов это Автосумма , он находится на стандартной панели инструментов (обозначен греческой буквой S ). Этот инструмент не только вводит функцию СУММ , но и выбирает диапазон ячеек текущего столбца или строки, содержимое которых, как он полагает, пользователь намерен просуммировать, и автоматически задает их в качестве аргументов функции. Но главное то, что в девяти случая из десяти Excel точно выбирает диапазон ячеек для суммирования.
Итак, по умолчанию Автосумма помещает в текущую ячейку функцию СУММ , однако это можно изменить. Чтобы с помощью этого инструмента можно было поместить другую функцию (СРЗНАЧ, МАКС или МИН ), необходимо щелкнуть на кнопке со стрелкой вниз справа от него и выбрать в раскрывающемся меню имя нужной функции. При щелчке на кнопке Дополнительно Excel откроет диалоговое окно Вставка функции.
Немаловажно, при работе с таблицами и то, что после вставки функции в ячейку, имеется возможность использовать средство Автозаполнение для копирования этой формулы в последующие ячейки, перетащив маркер заполнения вправо до конечной ячейки, а затем отпустив кнопку мыши.
Помимо автосуммирования пользователями часто используются и другие функции. Остановимся на некоторых из них.
Иногда требуется вычислить среднее значение. Здесь можно выделить несколько случаев:
1. Вычисление среднего значения ячеек, расположенных непрерывно в одной строке или одном столбце. Для этого необходимо сделать следующие шаги
· Выделите ячейку снизу или справа от чисел, среднее значение которых требуется найти.
· Нажмите на панели инструментов Стандартные стрелку рядом с кнопкой Автосумма , а затем выберите команду Среднее и нажмите клавишу ВВОД.
2. Вычисление среднего значения ячеек, расположенных вразброс. Для выполнения этой задачи используется функция СРЗНАЧ , которая возвращает среднее (арифметическое) своих аргументов. Причем аргументов может быть от 1 до 30, и они должны быть либо числами, либо именами, массивами или ссылками, содержащими числа.
3. Вычисление среднего взвешенного значения. Для этого используются функции СУММПРОИЗВ и СУММ. Итак, функция СУММПРОИЗВ перемножает соответствующие элементы заданных массивов и возвращает сумму произведений. Массивов, чьи компоненты нужно перемножить, а затем сложить может быть от 2 до 30 массивов.
Однако следует помнить, что аргументы, которые являются массивами, должны иметь одинаковые размерности. Если это не так, то функция СУММПРОИЗВ возвращает значение ошибки #ЗНАЧ!. А также то, что СУММПРОИЗВ трактует нечисловые элементы массивов как нулевые.
Функция СУММ , как уже упоминалось выше, суммирует все числа в интервале ячеек. Причем, учитываются числа, логические значения и текстовые представления чисел, которые непосредственно введены в список аргументов.
Аргументы, которые являются значениями ошибки или текстами, не преобразуемыми в числа, вызывают значения ошибок.
4. Вычисление среднего значения всех чисел, кроме нулевых (0). Для выполнения этой задачи используются функции СРЗНАЧ и ЕСЛИ .
Excel 2002 позволяет также производить действия и над матрицами. Для этого присутствуют функции МОБР, МОПРЕД, МУМНОЖ.
Функция МОБР возвращает обратную матрицу для матрицы, хранящейся в массиве. В строке формул она отражена как МОБР (массив ), где массив - это числовой массив с равным количеством строк и столбцов.
Причем массив может быть задан по разному: как диапазон ячеек, например A1:C3; как массив констант, например {1;2;3: 4;5;6: 7;8;9}; или как имя диапазона или массива.
Если какая-либо из ячеек в массиве пуста или содержит текст, то функция МОБР возвращает значение ошибки #ЗНАЧ!. МОБР также возвращает значение ошибки #ЗНАЧ!, если массив имеет неравное число строк и столбцов.
Обратные матрицы, как и определители, обычно используются для решения систем уравнений с несколькими неизвестными. Произведение матрицы на ее обратную - это единичная матрица, то есть квадратный массив, у которого диагональные элементы равны 1, а все остальные элементы равны 0.
В качестве примера того, как вычисляется обратная матрица, рассмотрим массив из двух строк и двух столбцов A1:B2, который содержит буквы a, b, c и d, представляющие любые четыре числа. В следующей таблице приведена обратная матрица для A1:B2:
Таблица 1
Обратная матрица для А1:В2
МОБР производит вычисления с точностью до 16 значащих цифр, что может привести к небольшим численным ошибкам округления.
МОПРЕД возвращает определитель матрицы (матрица хранится в массиве).
Определитель матрицы - это число, вычисляемое на основе значений элементов массива. Для массива A1:C3, состоящего из трех строк и трех столбцов, определитель вычисляется следующим образом:
МОПРЕД (A1:C3) равняется A1*(B2*C3-B3*C2) + A2*(B3*C1- -B1*C3) + A3*(B1*C2-B2*C1)
Определители матриц обычно используются при решении систем уравнений с несколькими неизвестными.
МОПРЕД производит вычисления с точностью примерно 16 значащих цифр, что может в некоторых случаях приводить к небольшим численным ошибкам. Например, определитель сингулярной матрицы отличается от нуля на 1E-16.
МУМНОЖ возвращает произведение матриц (матрицы хранятся в массивах). Результатом является массив с таким же числом строк, как массив1 и с таким же числом столбцов, как массив2.
Применяя эту функцию, следует помнить, что количество столбцов аргумента массив1 должно быть таким же, как количество сток аргумента массив2, и оба массива должны содержать только числа.
Причем, Массив1 и массив2 могут быть заданы как интервалы, массивы констант или ссылки.
Если хотя бы одна ячейка в аргументах пуста или содержит текст или если число столбцов в аргументе массив1 отличается от числа строк в аргументе массив2, то функция МУМНОЖ возвращает значение ошибки #ЗНАЧ!.
a ij = Sb ik c kjгде i - номер строки, а j - номер столбца.
Формулы, которые возвращают массивы, должны быть введены как формулы массива.
Ряд функций представленных в Excel 2002 позволяет рассчитывать амортизацию.
Функция ДДОБ возвращает значение амортизации актива за данный период, используя метод двойного уменьшения остатка или иной явно указанный метод. Выглядит она следующим образом: ДДОБ (нач_стоимость ;ост_стоимость ;время_эксплуатации ;период ;коэффициент), где
Нач_стоимость - это затраты на приобретение актива.
Ост_стоимость - это стоимость в конце периода амортизации (иногда называется остаточной стоимостью актива).
Время_эксплуатации - это количество периодов, за которые собственность амортизируется (иногда называется периодом амортизации).
Период - это период, для которого требуется вычислить амортизацию. Период должен быть измерен в тех же единицах, что и время_эксплуатации.
Коэффициент - процентная ставка снижающегося остатка. Если коэффициент опущен, то он полагается равным 2 (метод удвоенного процента со снижающегося остатка).
Причем, все пять аргументов должны быть положительными числами.
Метод двойного уменьшения остатка вычисляет амортизацию, используя увеличенный коэффициент. Амортизация максимальна в первый период, в последующие периоды уменьшается. Функция ДДОБ использует следующую формулу для вычисления амортизации за период:
((нач_стоимость - остаточная_стоимость) - суммарная амортизация за предшествующие периоды) * (коэффициент/время_эксплуатации).
Функция АСЧ возвращает величину амортизации актива за данный период, рассчитанную методом «суммы (годовых) чисел».
АСЧ (нач_стоимость ;ост_стоимость ;время_эксплуатации ;период ), где Нач_стоимость - затраты на приобретение актива.
Период - период (должен быть измерен в тех же единицах, что и время полной амортизации).
АСЧ вычисляется следующим образом:
АМГД = [(стоимость - остаточная_стоимость)*(время_эксплуатации – период +1)*2] : [время_эксплуатации *(время_эксплуатации +1)]
АПЛ возвращает величину амортизации актива за один период, рассчитанную линейным методом.
АПЛ (нач_стоимость ;ост_стоимость ;время_эксплуатации ), где
Нач_стоимость - затраты на приобретение актива.
Ост_стоимость - стоимость в конце периода амортизации (иногда называется остаточной стоимостью актива).
Время_эксплуатации - количество периодов, за которые актив амортизируется (иногда называется периодом амортизации).
Еще одна функция – ФУО – она возвращает величину амортизации актива для заданного периода, рассчитанную методом фиксированного уменьшения остатка.
Метод фиксированного уменьшения остатка вычисляет амортизацию, используя фиксированную процентную ставку. ФУО использует следующие формулы для вычисления амортизации за период:
(нач_стоимость - суммарная амортизация за предшествующие периоды) * ставка
ставка = 1 - ((ост_стоимость / нач_стоимость) ^ (1 / время_эксплуатации)), округленное до трех десятичных знаков после запятой
Особым случаем является амортизация за первый и последний периоды. Для первого периода ФУО использует такую формулу:
нач_стоимость * ставка * месяцы / 12
Для последнего периода ФУО использует такую формулу:
((нач_стоимость - суммарная амортизация за предшествующие периоды) * ставка * (12 - месяцы)) / 12
Excel представляет также множество других финансовых функций:
· БС возвращает будущую стоимость инвестиции на основе периодических постоянных (равных по величине сумм) платежей и постоянной процентной ставки.
· ВСД возвращает внутреннюю ставку доходности для ряда потоков денежных средств, представленных их численными значениями. Эти денежные потоки не обязательно должны быть равными по величине, как в случае аннуитета. Однако они должны иметь место через равные промежутки времени, например, ежемесячно или ежегодно. Внутренняя ставка доходности - это процентная ставка, принимаемая для инвестиции, состоящей из платежей (отрицательные величины) и доходов (положительные величины), которые осуществляются в последовательные и одинаковые по продолжительности периоды.
· КПЕР возвращает общее количество периодов выплаты для инвестиции на основе периодических постоянных выплат и постоянной процентной ставки.
· МВСД возвращает модифицированную внутреннюю ставку доходности для ряда периодических денежных потоков. МВСД учитывает как затраты на привлечение инвестиции, так и процент, получаемый от реинвестирования денежных средств.
· ОСПЛТ возвращает величину платежа в погашение основной суммы по инвестиции за данный период на основе постоянства периодических платежей и постоянства процентной ставки.
· ПЛТ возвращает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянства процентной ставки. ПРОЦПЛАТ вычисляет проценты, выплачиваемые за определенный инвестиционный период. Эта функция обеспечивает совместимость с Lotus 1-2-3.
· ПРПЛТ возвращает сумму платежей процентов по инвестиции за данный период на основе постоянства сумм периодических платежей и постоянства процентной ставки.
· ПС возвращает приведенную (к текущему моменту) стоимость инвестиции. Приведенная (нынешняя) стоимость представляет собой общую сумму, которая на настоящий момент равноценна ряду будущих выплат. Например, когда вы занимаете деньги, сумма займа является приведенной (нынешней) стоимостью для заимодавца.
· ПУО возвращает величину амортизации актива для любого выбранного периода, в том числе для частичных периодов, с использованием метода двойного уменьшения остатка или иного указанного метода.
· СТАВКА возвращает процентную ставку по аннуитету за один период. СТАВКА вычисляется путем итерации и может давать нулевое значение или несколько значений. Если последовательные результаты функции СТАВКА не сходятся с точностью 0,0000001 после 20-ти итераций, то СТАВКА возвращает сообщение об ошибке #ЧИСЛО!.
ЗАКЛЮЧЕНИЕ
Как мы видим, Excel – это не более изощренный текстовый редактор с сеткой, которая принуждает пользователя заносить информацию в небольшие отдельные ячейки, вместо того, чтобы предложить все пространство листа.
Огромная разница между ячейками рабочей таблицы и страницами текстового редактора состоит в том, что каждая ячейка позволяет не только редактировать и форматировать текст, но и выполнять вычисления. Эти вычисления основаны на формулах, которые пользователь создает в различных ячейках таблицы, зачастую пользуясь мастером функций, очень облегчающим работу.
Вычислительные возможности Excel и ее способности по редактированию и форматированию дают в итоге чудесную программу для создания любого документа, который может содержать текстовые и числовые данные и позволяет выполнять вычисления.
Поскольку таблицы содержат динамические формулы, их итоговые значения всегда будут актуальны. А это очень важно в современных условиях.
Список литературы
1. MS Office 2000/ шаг за шагом.: Практ. пособ./ Пер. с англ. – М.: Изд-во «Эком».2000. – 820 С.
2. Левин А. Самоучитель работы на компьютере. – 6-е изд./ М.: Изд-во «Нолидж», 1999, - 656 С.
3. Excel 2002 для «чайников».: Пер. с англ. – М.: Издательский дом «Вильямс», 2003. – 304 С.
 |
Вопрос 2. Расчет заработной платы
Рис. 4. Макет таблицы заработной платы
Для расчета заработной платы мы ввели исходные данные:
· Постоянные данные: плановое количество рабочих дней, налоговый вычет, налоговый вычет на детей, ставка налога на доходы для физических лиц.
· Персональную информацию: список работников, должностные оклады, число детей, количество дней месяца, отработанных каждым работником.
Причем, персональную информацию мы записали в виде таблицы в соответствующие графы. Оставшиеся ячейки мы заполнили расчетными формулами:
Начислено= Оклад* Число отработанных дней/ Плановое число рабочих дней месяца
Удержано= (Начислено – Налоговый вычет – Вычет на детей * Число детей) * Ставка НДФЛ
К выдаче= Начислено – Удержано
После этого было произведено форматирование чисел в полученной таблице заданным образом. С помощью пункта меню Формат - Ячейки, вкладки Граница, мы задали необходимую рамку. После чего мы распечатали результат (см. Приложение 1).
Вопрос 3. Расчет квартплаты
Рис. 5 Макет таблицы по расчету квартплаты
Для расчета квартирной платы мы использовали исходные данные:
· Нормы расхода на человека
· Персональную информацию.
Во второй части работы мы производили непосредственно расчет квартирной платы по нашим данным. Для этого мы использовали следующие формулы:
Лишняя площадь = Площадь – Членов семьи * Норма жилплощади на одного человека
Расчет квартплаты и отопления производился по формуле: Площадь* Тариф за кв.м.
Для расчета оплаты за горячую воду, газ, воду и канализацию была использована формула: Норма на 1 человека* Тариф за 1 куб.м.* Членов семьи.
Оплата за лишнюю площадь рассчитана по формуле: Лишняя площадь* Тариф за 1 кв.м.
После того, как все формулы были занесены в таблицу, с помощью автосуммы мы вычислили оплату всего за месяц.
Затем было произведено форматирование чисел в полученной таблице заданным образом. С помощью пункта меню Формат - Ячейки, вкладки Граница, задана необходимая рамка. После чего мы распечатали результат (см. Приложение 2).
При первом обращении к Мастеру функций во время набора формулы эту программу можно вызвать либо командой Вставка ® Функция…, либо кнопкой с надписью f x на стандартной панели инструментов. Если формула начинается с функции, то знак "=" набирать необязательно, Мастер функций вставит его сам.
Для второй и последующих функций в той же формуле Мастер функций вызывают через адресное поле в строке формул. Этим способом можно пользоваться сразу после ввода символа "=", с которого начинается формула (см. ниже).
Работа Мастера разбита на два шага.
На первом в правой части окна выделяется нужная функция. Для того чтобы облегчить ее поиск, в левой части в поле "Категория" можно выделить нужный тип функций. По умолчанию всегда устанавливается тип "10 недавно использовавшихся". Пояснения по смыслу выбранной функции размещаются в нижней части окна и, если нужно, вызываются через справку. После того, как функция найдена, нажимают <ОК>.
На втором шаге Мастер выдает окно с полями для ввода аргументов. В них можно вводить константы, ссылки на ячейки или блоки, арифметические или логические выражения. Способы ввода аргументов те же, что и при наборе формулы, но знак "=" в аргумент вводить уже не надо. Для удобства ввода можно зацепить мышкой окно аргументов и переместить его в другую часть экрана так, чтобы оно не загораживало ячейки с нужными данными. Справа от каждого поля выдается значение набранного аргумента, под полями всех аргументов – значение функции, внизу окна – значение всей формулы в целом.
Если функция заканчивает формулу, то можно нажать <ОК> или клавишу
Если в аргумент одной функции входит другая функция, то она называется вложенной . Такую функцию можно вызвать только через Адресное поле Строки формул. По умолчанию в нем высвечивается последняя функция, с которой работал Excel. Если нужно вставить другую функцию, то ее поиск также начинается с Адресного поля. Раскрывающийся список в нем содержит десять последних функций, с которыми работал Мастер. Если среди них нет нужной, то заказывают строку "Другие функции…", которая вызывает первое окно Мастера функций с каталогом всей библиотеки. Для того чтобы окончить работу с вложенной функцией и продолжать набор аргументов первой, следует сделать щелчок левой кнопкой мышки по названию первой функции в Информационном поле Строки формул.
Мастер функций допускает использование до семи вложенных функций.
Задание
Введите в ячейки А1:А10 и В5:В10 какие-либо числа. В ячейку С1 с помощью Мастера функций введите формулу
СУММ(МАКС(А1:А10);МАКС(В5:В10);
МИН(А1:А10);МИН(В5:В10))
Правка информации
Самый простой способ правки – повторно ввести в ячейку новую информацию. Если это нежелательно, то можно перейти в режим правки с помощью двойного щелчка на нужной ячейке. Можно также установить курсор на эту ячейку и нажать клавишу
Для ввода одинаковых исправлений в несколько ячеек удобно пользоваться командой Правка ® Заменить… Эта команда аналогична такой же команде Word. Перед вызовом команды следует выделить блок, в котором надо сделать одинаковые исправления. Параметры окна Заменить, вызываемого командой, понятны по здравому смыслу.
Для очистки ячеек выделяют нужный блок и нажимают или выполняют команду Правка ® Очистить, в которой можно уточнить, какую часть информации, связанной с данным блоком, следует удалить.
Отменить неверные изменения до выхода из режима правки можно клавишей
В Excel автоматизирован вызов функций, вычисляющих итоговые значения в диапазонах ячеек с числовыми данными, Такие функции выбирают из раскрывающегося списка кнопки Автосумма в панели инструментов.
Наименование функции в списке |
Имя функции |
Операция |
Суммировать |
Вычисление суммы чисел. |
|
Вычисление среднего арифметического значения. |
||
СЧЁТ() |
Подсчет количества чисел. |
|
Максимум |
Выбор максимального значения. |
|
Выбор минимального значения. |
Применение функций автовычисления
1 способ:A |
B |
|
1 |
Количество |
|
2 |
||
3 |
||
4 |
||
5 |
||
6 |
Здесь результат |
A |
B |
|
1 |
Количество |
|
2 |
||
3 |
||
4 |
||
5 |
||
6 |
A |
B |
C |
D |
|
1 |
||||
2 |
||||
3 |
||||
4 |
||||
5 |
||||
6 |
||||
7 |
Если выделить прямоугольный диапазон, включающий пустые ячейки ниже и правее ячеек с числами, то вычисления будут выполнены для каждого столбца и каждой строки.
3 способ Этот способ позволяет выбрать любой, даже несвязанный диапазон для вычисления:A |
B |
|
1 |
Количество |
|
2 |
||
3 |
||
4 |
||
5 |
||
6 |
СУММ (А2:А5 ) |
Использование Мастера функций
Для вызова Мастера функций предназначена кнопка Вставка функции в строке формул или в панели инструментов. Примечание . Мастер функций можно также вызвать: Диалоговое окно Мастера функций (рисунок 5.4) содержит два списка: раскрывающийся список Категория и список функций . При выборе категории отображается соответствующий список функций.
Категория "10 недавно использовавшихся" постоянно обновляется, в зависимости от того, какие функции использовались в последнее время. Категория "Полный алфавитный перечень" содержит список всех функций Excel. При выборе функции в нижней части окна появляется ее краткое описание.
После щелчка на кнопке Ok (или нажатия клавиши <Enter >) имя выбранной функции заносится в строку формул вместе со скобками, ограничивающими список аргументов, и одновременно открывается окно Аргументы функции .
Пример такого окна функции показан на рисунке 5.5.
- В заголовке окна отображается имя функции.
- В верхней части располагаются поля ввода аргументов. Наименования обязательных аргументов выделены жирным шрифтом.
- Под полями приводится краткое описание функции.
- Ниже описывается назначение аргументов (это описание обновляется при переходе к очередному полю аргумента).
- В нижней части – результат вычисления и кнопки подтверждения и отмены ввода функции.
Выделив ячейку (диапазон), окно функции можно восстановить, щелкнув на кнопке разворачивания в конце строки.
Кнопка разворачивания
После ввода аргумента справа от поля указывается его значение.
Выбор недавно использовавшихся функций
Пункт "Другие функции… " предназначен для вызова Мастера функций.Цель работы:
· приобрести и закрепить практические навыки по созданию электронной таблицы с использованием возможностей автозаполнения, автосуммирования и копирования;
· приобрести и закрепить практические навыки по применению функций категории Статистические с использованием Мастера функций.
Теоретический материал
Формулы представляют собой выражения, по которым выполняются вычисления. Формула всегда начинается со знака равно (= ). Формула может включать функции, ссылки, операторы и константы.
Функция – стандартная формула, которая обеспечивает выполнение определенных действий над значениями, выступающими в качестве аргументов. Функции позволяют упростить формулы, особенно если они длинные или сложные.
Ссылка указывает на ячейку или диапазон ячеек листа, которые требуется использовать в формуле. Можно задавать ссылки на ячейки других листов той же книги и на другие книги. Ссылки на ячейки других книг называются связями.
Оператором называют знак или символ, задающий тип вычисления в формуле. Существуют математические, логические операторы, операторы сравнения и ссылок.
Константой называют постоянное (не вычисляемое) значение. Формула и результат вычисления формулы константами не являются.
Ввод формул с клавиатуры
Формулы можно вводить с использованием клавиатуры и мыши. С использованием клавиатуры вводят операторы (знаки действий), константы (преимущественно числа) и иногда функции. С использованием мыши выделяют ячейки, включаемые в формулу. Адреса ячеек (ссылки) также можно вводить с клавиатуры, обязательно в английской раскладке.
Операторы (знаки действий) вводятся с использованием следующих клавиш:
· сложение – клавиша клавиатуры + (плюс);
· вычитание – клавиша клавиатуры – (минус или дефис);
· умножение – клавиша клавиатуры * (звездочка);
· деление – клавиша клавиатуры / (дробь);
· возведение в степень – клавиша клавиатуры ^ (крышка).
Например, при создании формулы для расчета стоимости товара Баунти в ячейке D2 таблицы на рис. 2.1 необходимо выделить ячейку D2 , ввести с клавиатуры знак = В2 , ввести с клавиатуры знак * , щелкнуть левой кнопкой мыши по ячейке С2 .
Рис. 2.1. Ввод формулы с клавиатуры
При вводе с клавиатуры формула отображается как в строке формул, так и непосредственно в ячейке (рис. 1). Ячейки, использованные в формуле, выделены цветной рамкой, а ссылки на эти ячейки в формуле – шрифтом того же цвета.
Для подтверждения ввода формулы в ячейку следует нажать клавишу клавиатуры Enter или нажать кнопку Ввод (зеленая галочка) в строке формул.
Создание формул с использованием мастера функций
Функции используются не только для непосредственных вычислений, но и для преобразования чисел, например для округления, для поиска значений, сравнения и т. д.
Для создания формул с функциями обычно используют мастер функций, но при желании функции можно вводить и с клавиатуры.
Для создания формулы следует выделить ячейку и нажать кнопку Вставка функции в строке формул. Можно также нажать комбинацию клавиш клавиатуры Shift + F3 .
Например, для создания в ячейке А11 формулы для округления значения в ячейке А10 таблицы на рис. 2.2, следует выделить ячейку А11 .
В диалоговом окне Мастер функций: шаг 1 из 2 (рис. 2.2) в раскрывающемся списке Категория необходимо выбрать категорию функции, затем в списке Выберите функцию следует выбрать функцию и нажать кнопку ОК или дважды щелкнуть левой кнопкой мыши по названию выбранной функции.
Рис. 2.2. Выбор функции
Например, для округления числа следует выбрать категорию Математические , а функцию ОКРУГЛ .
Если название нужной функции неизвестно, можно попробовать найти ее по ключевым словам. Для этого после запуска мастера функций в поле Поиск функции диалогового окна Мастер функций: шаг 1 из 2 (рис. 2.3) следует ввести примерное содержание искомой функции и нажать кнопку Найти .
Рис. 2.3. Поиск функции
Найденные функции будут отображены в списке Выберите функцию . Выделив название функции, в нижней части диалогового окна можно увидеть ее краткое описание. Для получения более подробной справки о функции следует щелкнуть по ссылке Справка по этой функции .
После выбора функции появляется диалоговое окно Аргументы функции (рис. 4). В поля аргументов диалогового окна следует ввести аргументы функции. Аргументами могут быть ссылки на ячейки, числа, текст, логические выражения и т. д. Вид диалогового окна Аргументы функции , количество и характер аргументов зависят от используемой функции.
Ссылки на ячейки можно вводить с клавиатуры, но удобнее пользоваться выделением ячеек мышью. Для этого следует поставить курсор в соответствующее поле, а на листе выделить необходимую ячейку или диапазон ячеек. Для удобства выделения ячеек на листе диалоговое окно Аргументы функции можно сдвинуть или свернуть.
Рис. 2.4. Задание аргументов функции
Текст, числа и логические выражения в качестве аргументов обычно вводят с клавиатуры.
Аргументы в поля можно вводить в любом порядке. Например, в таблице на рис. 2.4 округляемое значение находится в ячейке А10 , следовательно, в поле Число диалогового окна Аргументы функции указана ссылка на эту ячейку. А в поле Число разрядов аргумент 2 введен с клавиатуры.
В качестве подсказки в диалоговом окне отображается назначение функции, а в нижней части окна отображается описание аргумента, в поле которого в данный момент находится курсор.
Следует иметь в виду, что некоторые функции не имеют аргументов.
По окончании создания функции следует нажать кнопку ОК или клавишу клавиатуры Enter .
