ОРГАНИЗАЦИЯ И МЕТОДИЧЕСКИЕ УКАЗАНИЯ ПО ВЫПОЛНЕНИЮ ЛАБОРАТОРНЫХ РАБОТ

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

Этапы выполнения лабораторных работ:

1.      Изучение теоретических положений.

2.      Выполнение работы.

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

3.      Сохранение результатов выполненной работы.

Результат выполненной работы необходимо сохранить на диске Student в своей папке под именем «ФИО_Работа №_» либо на своем съемном носителе информации.

4.      Представление результатов выполнения работы (файла на съемном носителе) для проверки.

5.      Защита выполненной работы Ответы на вопросы

Лабораторная работа № 1

РАБОТА С ФОРМУЛАМИ И ФУНКЦИЯМИ

1.1 Работа с формулами

Формула – это некое выражение, которое выполняет вычисление между операндами с помощью операторов.

Операнд – это элемент вычисления (константы, функции и ссылки).

Константа – постоянное (не вычисляемое ) значение.

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

Операторы – знак или символ, определяющий тип вычисления в формуле над операндами (математические, текстовые, операторы сравнения и операторы ссылок).

Ввод формулы всегда начинается со знака  равенства  (=), затем следуют операнды и операторы. Например:

= 25*3,  либо   = А1+В2*(С10-D8)/СУММ(L12:Н4), где :

25, 3 – константы; СУММ – функция; А1, В2, С10, D8, L12 и Н4 – ссылки; +, -, /, * - операторы.

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

Существуют следующие типы операторов:

Математические операторы – это операторы простых действий, а именно сложения, вычитания, умножения и т. д. Вводятся с помощью клавиатуры:

+ (плюс) – сложение;

– (минус) – вычитание или отрицание;

* (звездочка) – умножение;

/ (косая черта) – деление;

% (знак процента) – процент;

^ (знак крышки) – возведение в степень.

Операторы сравнения – это операторы сравнения разных значений, результатом которого является логическое выражение «ИСТИНА» или «ЛОЖЬ»:

= (знак равенства) – равно;

> (знак «больше») – больше;

< (знак «меньше») – меньше;

>= (знак «больше или равно») – больше или равно;

<= (знак «меньше или равно») – меньше или равно;

<> (знак «не равно») – не равно.

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

Оператор ссылок – это оператор ссылки на диапазон ячеек.

-   двоеточие (:) – используется между ссылками на первую и последнюю ячейки диапазона. Такое сочетание представляет собой ссылку на диапазон, например, A10:C20;

-   точка с запятой (;) – оператор объединения нескольких ссылок в одну, например, СУММ(С10:В15; Е45:I30);

-   знак пробела – оператор пересечения множеств, который используется для ссылки на общие ячейки двух диапазонов. Например, В10:D20 C15:C25.

 

Ссылки в формулах

 

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

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

Абсолютная ссылка – это неизменная ссылка в формуле на ячейку, расположенную в определенном месте. При перемещении ячейки с формулой адрес ячейки с абсолютной ссылкой не корректируется. Абсолютная ссылка указывается символом $. Например, абсолютная ссылка на ячейку $A$1 указывает на неизменность адреса ячейки А1 при копировании формулы вдоль столбца или строки.

Смешанная ссылка – это ссылка с использованием либо абсолютной ссылки на столбец и относительной – на строку ($A1), либо абсолютной ссылки на строку и относительной – на столбец (A$1).

При этом при изменении позиции ячейки с формулой относительная ссылка строки или столбца изменяется, а абсолютная часть ссылки остается прежней.

Трехмерные ссылки – это ссылки на одну и ту же ячейку или диапазон ячеек, расположенные на нескольких листах одной книги. При этом трехмерная ссылка включает в себя имя листа. Например, трехмерная ссылка Лист1:Лист5!А1 указывает на все ячейки А1, расположенные с Листа1 по Лист5. При добавлении или удалении листов, попадающих в диапазон листов трехмерной ссылки, автоматически происходит учет всех изменений. То есть новые данные, расположенные на ячейках вставляемых или удаляемых листов, прибавляются или вычитаются.

 

Формулы

 

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

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

1. В окне открытого листа выделите ячейку, где будет находиться формула;

2. Введите символ «=» c клавиатуры;

3. Наберите нужное число, а затем знак действия ( _, /, +, *);

4. Затем введите следующее число. Например: =198*6;

5. Нажмите клавишу  Enter для перехода вниз по столбцу;

 

Рис. 1.1 Ввод простой формулы

 

При создании формулы действуют стандартные правила математических вычислений.

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

1. В окне открытого листа выделите ячейку, где будет находиться формула;

2. Введите символ «=» c клавиатуры;

3. Затем введите адрес ячейки, содержащий нужные значения;

4. Вставьте в формулу оператор и введите адрес следующей ячейки;

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

6. Завершите создание формулы так же, как и в предыдущей инструкции.

 

 

Рис. 1.2 Ввод формулы с использованием ссылок

 

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

1. В окне открытого листа выделите ячейку, где будет находиться формула;

2. Введите символ «=»;

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

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

5. Нажмите на клавиатуре клавишу F4;

Примечание. В адрес ячейки перед именем столбца и номером строки добавится символ $.

6. Нажмите клавишу Enter.

Рис.1.3 Ввод формулы с абсолютной ссылкой

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

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

1 способ:

1. В окне открытого листа выделите ячейку с нужной формулой;

2. Используйте любой известный способ копирования (кнопка «Копировать» на вкладке «Главная», Ctrl+C и т.д.);

3. Выделите ячейку, куда необходимо вставить формулу;

4. Используйте любой известный способ вставки (кнопка «Вставить» на вкладке «Главная», Ctrl+V и т.д.).

5. Завершите копирование нажатием клавиши Enter.

 

2 способ:

1. В окне открытого листа выделите ячейку с нужной формулой;

2. Используйте любой известный способ копирования (кнопка «Копировать» на вкладке «Главная», Ctrl+C и т.д.).

3. Выделите ячейку, куда необходимо вставить формулу.

4. Перейдите к вкладке «Главная» и в группе «Буфер обмена» раскройте меню кнопки «Вставить».

5. В списке команд выберите пункт «Вставить значения»

6. Закрепите результат нажатием клавиши Esc.

 

Массив

 

Массив – это несколько наборов значений, объединенных общими вычислениями.

Формула массива – это формула, в которой производится несколько вычислений над одним или несколькими наборами значений (аргументами массива), а затем отображающая один или несколько результатов. Формула массива создается по тем же правилам, что и обычная формула, только помещается в фигурные скобки { } и закрепляется сочетанием клавиш Ctrl+Shift+Enter. При этом если ввести данные скобки вручную, то формула будет преобразована в текстовую строку и перестанет работать.

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

Одномерный горизонтальный массив – когда значения находятся в отдельной строке.

Одномерный вертикальный массив – когда значения находятся в отдельном столбце.

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

 

Рассмотрим создание формулы массива с несколькими ячейками на следующем примере: имеются следующие диапазоны данных – А2:А5 и В2:В5. Необходимо умножить значения диапазона А2:А5 на значения В2:В5.

1.    В окне открытого листа выделите диапазон ячеек, где будет располагаться формула массива и введите знак (=);

2.    В нашем примере пусть это будут ячейки С2:С5;

3.    Введите первый нужный диапазон ячеек А2:А5;

4.    Введите оператор умножения (*), а затем следующий диапазон ячеек В2:В5;

5.    Завершите операцию сочетанием клавиш Ctrl+Shift+Enter;

6.    Созданная формула примет вид {=А2:А5*В2:В5}.

 

 

Рис. 1.4 Формула массива с несколькими ячейками

 

Рассмотрим создание формулы массива с одной ячейкой:

Имеется следующие диапазоны данных – А2:А6, В2:В6, D2:D6 и Е2:Е6. Необходимо умножить значения диапазона А2:А6 на значения В2:В6, затем значения диапазона D2:D6 на значения Е2:Е6. Полученные результаты следует сложить. Для этого необходимо:

1.    В окне открытого листа выделите ячейку, где будет располагаться формула массива и введите знак (=);

2.    Пусть в нашем примере это будет ячейка F2;

3.    Введите название функции автосуммы СУММ;

4.    Откройте круглые скобки и введите первый нужный диапазон ячеек А2:А6;

5.    Введите оператор умножения (*), а затем следующий диапазон ячеек В2:В6;

6.    Введите оператор перечисления (;), а затем следующий диапазон ячеек D2:D6;

7.    Введите оператор умножения (*), а затем последний диапазон ячеек Е2:Е6;

8.    Закройте круглые скобки и нажмите сочетание клавиш Ctrl+Shift+Enter;

9.     Созданная формула преобразится и примет вид:

 

{=СУММ(А2:А6*В3:В6;D2:D6*E2:E6)}.

 

 

Рис. 1.5 Формула массива с одной ячейкой

 

Способы изменения формулы массива:

1 способ (изменение формулы с одной ячейкой):

1.      В окне открытого листа выделите ячейку с формулой массив;

2.      Щелкните курсором в окошке строки формул и внесите нужные изменения;

3.      Завершите операцию сочетанием клавиш Ctrl+Shift+Enter;

 2 способ (изменение формулы с несколькими ячейками):

1.      В окне открытого листа выделите весь диапазон ячеек с формулой массива;

2.      Щелкните курсором в окошке строки формул и внесите нужные изменения;

3.      Завершите операцию сочетанием клавиш Ctrl+Shift+Enter.

 

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

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

2. Щелкните курсором в окошке строки формул и внесите нужные изменения в ссылках на ячейки;

3. Завершите операцию сочетанием клавиш Ctrl+Shift+Enter.

 

 

Константы массива

 

Константы массива – это компоненты формул массива, которые создаются путем ввода списка элементов внутри фигурных скобок ({ }). Скобки при этом вводятся вручную, например:

={1;2;3;4;5}.

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

При горизонтальном массиве (строка) элементы списка разделены точкой с запятой.

При вертикальном массиве (столбец) элементы списка разделены двоеточием.

При двумерном массиве элементы строк разделены точкой с запятой запятыми, а столбцы – двоеточием.

Между собой элементы отделяются пробелом.

 

Горизонтальная константа

 

1.      В окне открытого листа выделите горизонтальный ряд ячеек с числами. Например, ячейки А1_С1 со значениями 1,2,3;

2.      В окошке строки формул введите знак (=) и откройте фигурную скобку;

3.      Введите числа, содержащиеся в выделенном ряде ячеек, разделяя их точкой с запятой:   ={1;2;3}

4.       Закройте фигурные скобки и нажмите сочетание клавиш Ctrl+Shift+Enter.

Формула примет следующий вид:   {={1;2;3}}.

 

 

Рис. 1.6 Горизонтальная константа

 

Вертикальная константа

 

1.      В окне открытого листа выделите вертикальный ряд ячеек с числами. Например, ячейки А2_А4 со значениями 4,5,6;

2.      В окошке строки формул введите знак (=) и откройте фигурную скобку;

3.      Введите числа, содержащиеся в выделенном ряде ячеек, разделяя их двоеточиями:

={4:5:6}

4.      Закройте фигурные скобки и нажмите сочетание клавиш Ctrl+Shift+Enter.

Формула примет следующий вид: {={4:5:6}}.

 

 

Рис. 1.7 Вертикальная константа

 

Двумерная константа

 

1.      В окне открытого листа выделите прямоугольный диапазон ячеек с числами. Например, ячейки А1_С3 со значениями 1,2,3,4,5,6,7,8,9;

2.      В окошке строки формул введите знак (=) и откройте фигурную скобку;

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

={1;2;3: 4;5;6: 7,8,9}

4.       Закройте фигурные скобки и нажмите сочетание клавиш Ctrl+Shift+Enter.

{={1;2;3: 4;5;6: 7,8,9}}

 

 

Рис. 1.8 Двумерная константа

 

Использование константы массива

 

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

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

2. Введите нужную формулу. В нашем случае это: =СУММА(A1:А3*{1;2;3}),

где:

-   СУММА – функция автосуммирования;

-   A1:А3 – диапазон ячеек с исходными значениями 10,20,30;

-   * – оператор умножения;

-   {1;2;3} – константа массива.

3. Используйте сочетание клавиш Ctrl+Shift+Enter.

Формула примет следующий вид: {=СУММА(A1:А3*{1;2;3})}

 В ячейке В1 отобразится значение 360.

Данная формула соответствует следующей формуле: =СУММА(A1*1,А2*2,А3*3).

 

 

Рис. 1.9 Использование константы массива

 

                                  

1.2 Работа с функциями

 

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

-   знак равенства (=);

-   собственное имя, например СУММ;

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

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

-   закрывающая скобка.

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

В Excel существуют математические, финансовые, статистические и другие функции. Имя функции можно вводить вручную с клавиатуры, а можно выбирать в окне «Мастер функций».

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

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

1 способ:

1.      В окне открытого листа выделите ячейку, где будет располагаться функция;

2.      Перейдите к вкладке «Формулы» и в группе «Библиотека функций» щелкните по кнопке «Вставить функцию»;

3.      В окне «Мастер функций – шаг 1 из 2» в графе «Категории» раскройте список существующих типов функций и выберите нужную категорию: например, «Математические»;

4.      В группе «Выберите функцию» просмотрите список функций заданной категории и выберите нужную, например, «ОКРУГЛВВЕРХ»;

5.      Перейдите к следующему шагу щелчком по кнопке «ОК»;

6.      В окне «Аргументы функции» введите в поля аргументов адреса ячеек для вычисления.

Примечание. Для каждой функции окно «Аргументы функции» имеет собственный вид. Например, в окне «Аргументы функции ОКРУГЛВВЕРХ» необходимо в графе «Число» ввести адрес ячейки с числом, которое необходимо округлить, а в графе «Число разрядов» задать количество разрядов после запятой, до которых необходимо округлить число.

7.      Для получения развернутой информации по используемой функции нажмите кнопку «Справка по этой функции.»

8.      Закройте окно кнопкой «ОК».

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

 

 

Рис. 1.10 Вставка функции

 

2 способ:

 

1.      В окне открытого листа выделите ячейку, где будет располагаться функция;

2.      Используйте сочетание клавиш Shift+F3;

3.      Далее действуйте, как в первом способе данной инструкции.

3 способ:

 

1.      В окне открытого листа выделите ячейку, где будет располагаться функция;

2.      На строке формул щелкните по кнопке «Вставить функцию»;

3.      Далее действуйте, как в первом способе данной инструкции.

 

 

 

Примеры операций с функциями

Таблица 1.1

 

Ф.И. О. сотрудника

Должность

Оклад

1

Антипенко Г.И.

Директор

18 000,00

2

Беликова Н.А.

Главный бухгалтер

14 500,00

3

Джиоева О.Л.

Бухгалтер

10 000,00

4

Петрова И.Н.

Бухгалтер

10 000,00

5

Макаренко И.Е.

Кладовщик

  6 800,00

6

Анисимов Р.Г.

Электрик

  5 400,00

7

Купеев Р.В.

Механик

  3 000,00

8

Азиева И.К.

Продавец

  2 700,00

9

Иванов И.Р.

Грузчик

  8 000,00

10

Куравлев. В.Ш.

Грузчик

  8 000,00

 

Необходимо определить:

1.      Фонд оплаты труда по предприятию; максимальный и минимальный оклад;

2.      Среднюю заработную плату;

3.      Число сотрудников, имеющих оклад менее определенной суммы;

4.      ФОТ сотрудников, имеющих оклад менее определенной суммы;

5.      Доля работников имеющих оклад менее определенной суммы, в общем ФОТ.

Введем исходные данные в Excel.

 

Рис. 1.11 Исходные данные

 

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

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

 

 

Рис. 1.12 Результат выполнения расчета (режим отображения формул)

 

 

Рис. 1.13Результат выполнения расчета

 

 

1.3 Контрольные вопросы

1.      Дайте определение формулы, функции, константы, оператора?

2.      Какие типы операторов вы знаете?

3.      Какие виды ссылок вы знаете?

4.      Дайте определение массива, формулы массива?

5.      Какие виды массивов вы знаете?

6.      Дайте определение константы массива?

7.      Какие константы вы знаете?

 

Лабораторная работа № 2

 

РАБОТА СО СВОДНЫМИ ТАБЛИЦАМИ

 

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

С помощью отчета сводной таблицы можно:

-   организовать запрос к большим массивам данных;

-   подвести промежуточный итог в таблице;

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

-   фильтровать, сортировать, группировать и форматировать подмножества данных.

Основные понятия отчета сводной таблицы:

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

Поле строки – поле, которому соответствуют строка. Элементы, связанные с полем строки, обозначаются как подписи строк.

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

Кнопка поля – это кнопка, задающая поле в отчете сводной таблицы. Ее можно перетаскивать для изменения макета отчета, а также использовать стрелки рядом с ними для раскрытия данных.

Итоговая функция – вычисление при объединении данных в отчете сводной таблицы, в таблице консолидации или же при вычислении итогов в списках или базах данных. Например, СУММ, СЧЕТ и СРЕДНЕЕ.

Исходные данные – это диапазон данных или таблица, которые используются для создания отчета сводной таблицы. Кроме этого они могут быть взяты из внешней базы данных (например, Microsoft Access).

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

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

Элемент – категория поля в отчетах сводной таблицы и сводной диаграммы. Например, поле «Месяц» будет включать такие элементы, как «Январь», «Февраль» и т. п.

Запрос – средство поиска записей, отвечающих на определенный вопрос о данных, хранящихся в источнике данных.

Список – набор строк, содержащий взаимосвязанные данные.

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

 

2.1. Создание отчета сводной таблицы

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

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

2.      Перейдите к вкладке «Вставка» и в группе «Таблицы» раскройте меню кнопки «Сводная таблица»;

 

Рис. 2.1 Меню «Вставка – «Сводная таблица»

 

3.      В поле «Укажите, куда следует поместить отчет сводной таблицы» выберите пункт:

-   «на новый лист» – для размещения отчета сводной таблицы на дополнительном листе в книге;

-   «на существующий лист» – для размещения отчета сводной таблицы в том же листе, где находятся и исходные данные.

4.      В поле «Диапазон» необходимо указать место расположения отчета сводной таблицы;

5.      Закройте окно кнопкой «ОК».

 

 

Рис. 2.2 Создание сводной таблицы

6.      На листе (новом или существующем) появится пустая сводная таблица в виде контура макета, а справа листа откроется область «Список полей сводной таблицы» для создания отчета.  В нем можно вставлять в сводную таблицу новые поля, создавать макет и настраивать отчет;

 

Создание отчета сводной таблицы, используя таблицу данных.

Таблица 2.1.

Исходные данные

 

Дата операции

Наименование товара

Ед.изм.

Приход, кол-во

01.04.2012

Ручки шариковые

шт.

200

06.04.2012

Блокнот

шт.

150

15.04.2012

Ручки шариковые

шт.

300

05.05.2012

Дырокол

шт.

50

15.05.2012

Ножницы канцелярские

шт.

30

20.05.2012

Ручки шариковые

шт.

100

 

1.      В окне открытого листа Excel выделите таблицу данных;

2.      Перейдите к вкладке «Вставка» и в группе «Таблицы» раскройте меню кнопки «Сводная таблица»;

3.      В списке команд выберите пункт «Сводная таблица»;

4.      В окне «Создание сводной таблицы» в графе «Таблица или диапазон» отобразится имя используемой таблицы.

 

 

Рис. 2.3. Выбор диапазона

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

 

 

Рис. 2.4 Шаблон сводной таблицы

 

Примечание: В открывшемся окне вы видите поле отчета сводной таблицы и список полей сводной таблицы (справа).

6.      Далее необходимо сформировать сводную таблицу. Для этого в списке полей сводной таблицы нужно выбрать необходимые параметры. В нашем примере нужно выбрать наименование товара и приход. Результат виден на рис. 2.5.

 

 

Рис. 2.5. Сводная таблица

 

Рассмотрим создание сводной таблицы еще на одном примере.

 

 

 

 

 

 

Таблица 2.2.

Исходные данные

 

Дата

Группа

Наименование

Ед. изм.

Кол-во

Цена

Сумма

01.05.2011

Стройматериалы

Лист 3мм

тн.

10

25 000,00

?

01.05.2011

Стройматериалы

Труба стальная

пм.

300

2 000,00

?

05.05.2011

Стройматериалы

Кафель, 30х30

кв.м.

80

560,00

?

08.05.2012

Стройматериалы

Краска белая

бан.

48

300,00

?

15.05.2012

Бытовая химия

Порошок стир.

уп.

50

80,00

?

01.06.2012

Бытовая химия

Шампунь

шт.

86

105,00

?

20.08.2012

Мебель

Диван угл.

шт.

5

35 000,00

?

25.06.2012

Мебель

Стол кух.

шт.

8

15 000,00

?

01.07.2012

Продукты питания

Сахар

кг.

300

35,00

?

08.07.2012

Продукты питания

Мука

кг.

500

30,00

?

05.08.2012

Продукты питания

Картофель

кг.

180

20,00

?

28.08.2012

Продукты питания

Масло слив.

кг.

30

220,00

?

 

В связи с тем, что итоги должны быть рассчитаны за месяц и за год необходимо ввести дополнительные графы «Месяц» и «Год», рассчитываемые по формулам:

 

-   Графа «Месяц» = МЕСЯЦ(Графа “Дата”);

-   Графа «Год»      = ГОД(графа “Дата”).

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

Рис. 2.6. Реализация товаров

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

Графы «Сумма по полю Кол-во» и «Сумма по полю Сумма» необходимо переименовать в «Количество» и «Сумма», соответственно.

Далее необходимо сформировать фильтр отчета сводной таблицы, который позволяет получать сводные данные по определенным показателям. Для этого в списке полей сводной таблицы необходимо переместить кнопки «Месяц», «Год», «Группа» в «Фильтр отчета». Результат отображен на рис. 2.8.

 

 

 

 

Рис. 2.7. Готовая сводная таблица

 

 

 

Рис. 2.8. Фильтр сводной таблицы

 

Например, необходимо определить выручку за май месяц 2011г. по группе стройматериалы. Для этого в верхней части сводной таблицы из списка необходимо выбрать соответствующие параметры. Результат представлен на рисунке 2.9. Нам видно, что за май месяц 2011 года было реализовано товара по группе «Стройматериалы» на сумму 894 800руб.

 

Рис. 2.9 Выручка по группе «Стройматериалы» за май 2011г.

 

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

Для этого:

1.      На вкладке «Работа со сводными таблицами» необходимо перейти в меню «Параметры»;

2.      Нажать на кнопку «Изменить источник данных»;

 

 

Рис. 2.10. Изменение источника данных в сводной таблице

 

3.      В появившемся окне в области диапазона изменить номер последней строки диапазона на больший. В нашем примере 250 (рис.2.11).

 

Рис. 2.11. Изменение источника данных сводной таблицы

 

4.      Завершить операцию нажатием клавиши Enter;

5.      После ввода новых данных необходимо перейти на вкладку «Работа со сводными таблицами» в меню «Параметры» и нажать кнопку «Обновить».

На практике исходная таблица дополняется ежедневно, поэтому будет нецелесообразным менять диапазон исходных данных так часто. Поэтому, вместо того, чтобы изменять диапазон на 50 или 100 строк, можно увеличить его на 2000 строк. Это позволяет избежать возможных ошибок при работе со сводными таблицами, так как несвоевременное увеличение диапазона исходных данных может привести к принятию ошибочного решения, и привести к прямым убыткам.

 

 

2.2 Контрольные вопросы

1.      Что такое сводная таблица? Опишите область ее применения.

2.      Перечислите основные составляющие отчета сводной таблицы;

3.      Как при создании сводной таблицы нужно учесть возможность увеличения объема исходных данных?

4.      Как производится увеличение объема диапазона?

5.      Как производится выбор критериев отбора сводной таблицы?

 

Лабораторная Работа № 3

ЗАДАЧА О ВЗАИМНЫХ РАСЧЕТАХ

Цель работы: Используя технологию сводных таблиц решить задачу о взаимных расчетах.

Предположим, что имеется N участников совместной деятельности (А1…АN) – юридические и физические лица. В процессе совместной деятельности они поставляют друг другу материалы, оказывают услуги и т.д.

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

Таблица 3.1

Журнал хозяйственных операций

№ опер.

Дата операции

Наименование услуги

Кем оказана услуга

Кому оказана услуга

Сумма, руб.

1

2

3

4

5

6

1

15.03.2011г.

У1

ОАО «Исток»

ООО «Иртекс»

2 000,00

2

16.03.2011г.

У2

ООО «Иртекс»

ОАО «Исток»

50 000,00

3

16.03.2011г.

У3

ОАО «Альянс»

ОАО «Исток»

12 500,00

4

17.03.2011г.

У4

ООО «Иртекс»

ОАО «Альянс»

11 000,00

5

17.03.2011г.

У5

ОАО «Строй-инвест»

ООО «Иртекс»

7 000,00

6

20.03.2011г.

У6

ЗАО «Оазис»

ЗАО «Мираж»

47 000,00

7

21.03.2011г.

У7

ООО «Мебельщик»

ООО «Иртекс»

14 200,00

8

25.03.2011г.

У8

ОАО «Альянс»

ООО «Мебельщик»

3 000,00

9

26.03.2011г.

У9

ООО «Иртекс»

ЗАО «Оазис»

2 400,00

 

где: У1- У9 – какая – либо услуга (поставка материалов, готовой продукции и т.д.)

Необходимо определить:

1.      Кто кому и сколько должен.

2.      Общий объем оказанных взаимных услуг.

3.      Определить баланс каждого участника.

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

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

1.      Создать книгу Excel, на отдельном листе набрать журнал хозяйственных операций;

Рис. 3.1 Журнал хозяйственных операций

 

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

-   выделить таблицу  → Вставка  → Сводная таблица  → выполнить необходимые действия;

-   перенести графу 4 в область строк таблицы;

-   перенести графу 5 в область столбцов таблицы;

-   перенести графу 6 в область данных таблицы.

В результате получится таблица, приведенная на рис. 3.2.

Рис. 3.2 Сводная таблица

На пересечении строк и столбцов отражено количество хозяйственных операций, а нам необходимо получить сумму. Для этого  в области данных вызываем контекстное меню, выбираем пункт «Параметры полей значений». Далее в появившемся диалоге выбираем категорию «Сумма» (рис. 3.3).

Рис. 3.3. Контекстное меню – «Параметры полей значений»

 Получаем следующий результат:

Рис. 3.4 Готовая сводная таблица

По сумме значений строки (графа «Общий итог») определяем, сколько должны каждому из участников. Так, долг перед  ЗАО «Оазис» составляет  47 000 руб.

По сумме значений столбца (Строка «Общий итог») определяем, сколько должен каждый участник. Так долг ЗАО «Оазис» перед ООО «Иртекс» составляет  2 400 руб.

3.      Далее необходимо определить баланс каждого участника. Для этого нужно получить сводную таблицу,  транспонированную  по отношению к таблице, приведенной на рис. 3.4 (т.е. поменять местами строки и столбцы). Для этого выделяем и копируем сводную таблицу. Далее переносим управляющую ячейку графы 4 в область столбцов, а управляющую ячейку графы 5 в область строк. Таким образом, получим транспонированную таблицу:

Рис. 3.5. Сводная и транспонированная таблицы

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

1.      Скопировать сводную таблицу в буфер обмена (Выделить ячейки таблицы → Копировать);

2.      Перейти на необходимую ячейку для вставки;

3.      Вставить таблицу значений сводной таблицы: Контекстное меню    Специальная вставка    Значения;

4.      Скопировать транспонированную таблицу и вставить ее ниже исходной таблицы значений (Контекстное меню    Специальная вставка    Значения);

Рис. 3.6. Значения сводной и транспонированной таблицы

 

5.      Скопировать транспонированную таблицу значений    перейти на верхнюю левую ячейку исходной таблицы значений → Выполнить «Специальная вставка»    Вычесть → ОК (рис. 3.7.).

Рис. 3.7. Меню «Специальная вставка»

            Результатом работы будет таблица окончательных расчетов:

Рис. 3.8. Таблица окончательных расчетов

 

Контрольные вопросы

1.      В чем состоит задача о взаимных расчетах?

2.      Опишите последовательность решения данной задачи.

3.      Что является результатом решения задачи о взаимных расчетах?

Лабораторная работа № 4

АНАЛИЗ ОПЕРАЦИЙ С ЦЕННЫМИ БУМАГАМИ

Цель работы: изучение технологии проведения анализа операций с ценными бумагами

 

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

Все ценные бумаги делятся на виды и типы.

Тип образует сочетание различных комбинаций видов ценных бумаг, объединяемых каким-либо общим признаком. Тип ценных бумаг подразделяется на их виды.

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

Выделяют также срочные и бессрочные ценные бумаги. Последние представляют собой ценные бумаги, срок обращения которых ничем не регламентирован. Среди срочных ценных бумаг, т.е. имеющих установленный срок существования при их выпуске, выделяют краткосрочные (срок обращения до одного года); среднесрочные (срок обращения свыше одного года в пределах до 5—10 лет) и долгосрочные (срок обращения до 20—30 лет).

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

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

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

Доходность - предусматривающая отношение дохода, полученного от ценной бумаги (дивиденда, процента, премии), к инвестициям в нее.

Номинал  - это стоимость ценной бумаги, которая указана на ней.

Курс  - это цена, по которой ценные бумаги продаются и/или покупаются на фондовом рынке.

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

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

4.1.Финансовые функции для работы с ценными бумагами

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

Таблица 4.1

Назначение и форматы финансовых функций для анализа ценных бумаг

Формат

Назначение

ДАТАКУПОНПОСЛЕ (дата_согл; дата_вступл_в_силу; частота; базис)

Возвращает число, представляющее дату следующего купона от даты соглашения

ДАТАКУПОНДО(дата_согл; дата_вступл_в_силу; частота; базис)

Возвращает число, представляющее дату предыдущего купона до даты соглашения

ДЛИТ(дата_согл; дата_вступл_в_силу; купон; доход; частота; базис)

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

ДНЕЙКУПОН(дата_согл; дата_вступл_в_силу; частота; базис)

Возвращает число дней в периоде купона, который содержит дату расчета

ДНЕЙКУПОНДО(дата_согл; дата_вступл_в_силу; частота; базис)

Возвращает количество дней от начала действия купона до даты соглашения

ДНЕЙКУПОНПОСЛЕ (дата_согл; дата_вступл_в_силу; частота; базис)

Возвращает число дней от даты расчета до срока следующего купона

ДОХОД(дата_согл ; дата_вступл_в_силу; ставка; цена; погашение; частота; базис)

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

ДОХОДКЧЕК(дата_согл; дата_вступл_в_силу; цена)

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

ДОХОДПЕРВНЕРЕГ(дата_согл; дата_вступл_в_силу; дата_выпуска; первый_купон; ставка; цена; погашение; частота; базис)

Возвращает доход по ценным бумагам с нерегулярным (коротким или длинным) первым периодом

ДОХОДПОГАШ(дата_согл; дата_вступл_в_силу; дата_выпуска; ставка; цена; базис)

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

ДОХОДПОСЛНЕРЕГ(дата_согл; дата_вступл_в_силу; последняя_выплата; ставка; цена; погашение; частота; базис)

Возвращает доход по ценным бумагам с нерегулярным (коротким или длинным) последним периодом

ДОХОДСКИДКА(дата_согл; дата_вступл_в_силу; цена; погашение; базис)

Возвращает годовую доходность

по ценным бумагам, на которые сделана скидка

ИНОРМА(дата_согл; дата_вступл_в_силу; инвестиция; погашение; базис)

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

М ДЛ ИТ( дата_согл; дата_вступл_в_силу; купон; доход; частота; базис)

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

НАКОПДОХОД(дата_выпуска; первый_доход; дата_согл; ставка; номинал; частота; базис)

Возвращает накопленный процент по ценным бумагам с периодической выплатой процентов

НАКОПДОХОДПОГАШ(дата_вы-пуска; дата_согл; ставка; номинал; базис)

Возвращает накопленный процент по ценным бумагам, процент по которым выплачивается в срок погашения

ПОЛУЧЕНО(дата_согл; дата_вступл_в_силу; инвестиция; скидка; базис)

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

РАВНОКЧЕК(дата_согл; дата_вступл_в_силу; скидка)

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

СКИДКА(дата_согл; дата_вступл_в_силу; цена; погашение; базис)

Возвращает ставку дисконтирования для ценных бумаг

ЦЕНА(дата_согл; дата_вступл_в_силу; ставка; доход; погашение; частота; базис)

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

ЦЕНАКЧЕК(дата_согл; дата_вступл_в_силу; скидка)

Возвращает цену на 100 руб. номинальной стоимости для бумаг краткосрочного действия (казначейского чека или векселя)

ЦЕНАПЕРВНЕРЕГ(дата_согл; дата_вступл_в_силу; дата_выпуска; первый_купон; ставка; доход; погашение; частота; базис)

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

ЦЕНАПОГАШ(дата_согл; дата_вступл_в_силу; дата_выпуска; ставка; доходность; базис)

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

ЦЕНАПОСЛНЕРЕГ(дата_согл; дата_вступл_в_силу; послед-няя_выплата; ставка; доход; погашение; частота; базис)

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

ЦЕНАСКИДКА(дата_согл; дата_вступл_в_силу; скидка; погашение; базис)

Возвращает цену за 100 руб. номинальной стоимости ценных бумаг, на которые сделана скидка вместо выплаты процентов

ЧИСЛКУПОН(дата_согл; дата_вступл_в_силу; частота; базис)

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

 

Таблица 4.2

Аргументы финансовых функций Excel анализа ценных бумаг

Аргумент

Назначение аргумента

Базис

Используемый способ вычисления дня

Дата_вступл_в_силу

Дата погашения ценной бумаги

Дата_выпуска

Дата выпуска ценных бумаг

Дата_согл

Дата приобретения ценной бумаги, дата инвестиций в ценные бумаги (более поздняя, чем дата выпуска)

Доход, доходность

Годовой доход по ценным бумагам

Инвестиция

Объем инвестиции в ценные бумаги (цена приобретения)

Купон

Годовая ставка процента для купонов по ценным бумагам

Номинал

Номинальная стоимость ценной бумаги (по умолчанию—1000 руб.)

Первый_доход

Дата окончания первого периода (дата первой выплаты процентов по ценной бумаге)

Первый_купон

Дата первого купона для ценных бумаг в числовом формате

Погашение

Выкупная стоимость ценных бумаг за 100 руб. номинальной стоимости

Последняя_выплата

Дата последнего купона для ценных бумаг (последней выплаты процентов)

Скидка

Скидка на казначейский вексель, учетная ставка в процентах к цене погашения

Ставка

Годовая ставка процента на момент выпуска ценных бумаг

Цена

Цена ценных бумаг за 100 руб. номинальной стоимости

Частота

Количество выплат по купонам за год

 

Примечания:

1.      Аргумент Частота (Периодичность) задается как число, принимающее следующие значения в зависимости от количества выплат по купонам за год:

-   один раз в год (ежегодная выплата);

-   два раза в год (полугодовая выплата);

-   четыре раза в год (ежеквартальная выплата).

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

0 -US(NASD) — американский стандарт, месяц равен 30, а год — 360 дням; принимается по умолчанию;

1 - фактический/фактический — фактическая длина месяца и года;

2 - фактический/360 — фактическая длина месяца, год равен 360 дням;

3 - фактический/365 — фактическая длина месяца, год равен 365 дням;

4 -европейский 30/360 — европейский стандарт, длина месяца равна 30 дням, длина года принимается 360 дней.

Следует отметить, что все даты должны быть выражены в числовом формате.

4.2 Примеры решения задач

Пример 1. Рассмотрим возможность приобретения облигаций трех типов, каждая из которых с номиналом в 100 руб. и сроком погашения 09.10.2012. Курсовая стоимость этих облигаций на дату 25.07.2010 составила соответственно 90, 80 и 85 руб.

Годовая процентная ставка по купонам (размер купонных выплат) составляет:

для первой облигации — 8% при полугодовой периодичности выплат;

для второй облигации — 5% при ежеквартальной периодичности выплат;

для третьей облигации — 10% с выплатой один раз в год.

Расчеты ведутся в базисе фактический/фактический.

Необходимо провести анализ эффективности вложений в покупку этих облигаций, если требуемая норма доходности составляет 15%.

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

ДОХОД(дата_согл; дата_вступл_в_силу; ставка; цена; погашение; частота; базис)

Для решения задачи составим на листе Excel таблицу с исходными данными и формулами расчета требуемых величин (рис.4.1).

Рис. 4.1. Применение функции ДОХОД для оценки доходности облигаций (режим отображения формул)

Рис. 4.2.  Применение функции ДОХОД для оценки доходности облигаций

Примечание: в ячейках В11, С11, D11 (строка «доход») необходимо применить формат «процентный»).

Выполним также расчет доходности, непосредственно задавая значения аргументов в функции ДОХОД.

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

Облигация первого типа:

=ДОХОД(ДАТА(2005;7;25);ДАТА(2007;10;9); 8%;90;100;2;1)=13,36%

Облигация второго типа:

=ДОХОД(ДАТА(2005;7;25);ДАТА(2007;10;9); 5%;80;10;4;1)=15,93%

Облигация третьего типа:

=ДОХОД(ДАТА(2005;7;25);ДАТА(2007;10;9); 10%;85;100;1;1)=18,83%

Результаты, полученные различными способами, совпадают.

Вывод: доходность по второй и третьей облигациям (15,93% и 18,83% соответственно) выше заданной нормы (15%), а по первой облигации (13,36%) — ниже. Следовательно, целесообразно покупать облигации второго и третьего типов.

Пример 2.  Коммерческий банк предлагает свои сберегательные сертификаты номиналом 100 000 руб. сроком на восемь месяцев. Дата соглашения — 10.01.2012. Цена продажи составляет 85 000 руб. Способ вычисления дня — фактический/360. Необходимо определить доход за этот период.

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

ИНОРМА(дата_согл; дата_вступл_в_силу; инвестиция; погашение; базис)

Рис. 4.3. Вычисление доходности сберегательного сертификата

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

Для проверки правильности результата в функцию ИНОРМА введем значения аргументов в непосредственном виде:

=ИНОРМА( ДАТА(2012;1;10);ДАТА(2012;9;10);85000;100000;2)=26,14%

Пример 3. Облигация номиналом в 10 000 руб. и сроком погашения 20.07.2012 приобретена 05.05.2010. Выплаты по купонам осуществляются каждые полгода при способе вычисления дня - фактический/365. Необходимо определить:

-   количество предстоящих купонных выплат;

-   дату предшествующей купонной выплаты;

-   дату следующей купонной выплаты;

-   длительность купонного периода;

-   количество дней от начала действия периода до даты соглашения;

-   количество дней от даты соглашения до даты следующего периода.

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

-   ДНЕЙКУПОНДО(дата_согл; дата_вступление_в_силу; частота; базис);

-   ДНЕЙКУПОН(дата_согл; дата_вступл_в_силу; частота; базис);

-   ДНЕЙКУПОНПОСЛЕ(дата_согл; дата_вступл_в_силу; частота; базис);

-   ДАТАКУПОНДО(дата_согл; дата_вступл_в_силу; частота; базис);

-   ДАТАКУПОНПОСЛЕ(дата_согл; дата_вступл_в_силу; частота; базис);

-   ЧИСЛКУПОН(дата_согл; дата_вступл_в_силу; частота; базис).

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

 

Рис. 4.4. Расчет параметров купонных выплат облигаций (режим отображения формул)

Результат вычислений представлен на рис. 4.5.

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

Пример 4. Вексель выдан 12.07.2009 с датой погашения 25.12.2009. Цена векселя составляет 200 000 руб., а выкупная цена  250 000 руб. При расчетах используется базис фактический/фактический. Необходимо определить величину учетной ставки. Необходимо определить величину учетной ставки можно с помощью функции СКИДКА:

СКИДКА(дата_согл; дата_вступл_в_силу; цена; погашение; базис)

Решение: Представим данные задачи в виде таблицы. В соответствующую ячейку введем формулу, обеспечивающую вычисление учетной ставки (рис. 4.6.).

Рис. 4.6. Применение функции СКИДКА для вычисления учетной ставки векселя (режим отображения формул).

Окончательный вариант решения данного примера приведен на рис. 4.7.  

Рис. 4.7. Применение функции СКИДКА для вычисления учетной ставки векселя

Для проверки правильности результата в функцию СКИДКА введем значения аргументов в непосредственном виде:

=СКИДКА(ДАТА(2009;7;12);ДАТА(2009;12;25);200000;250000;1 )=43,98%

Оба результата совпадают.


Функция СКИДКА реализует следующую формулу:

где :

Цена - цена ценных бумаг за 100 руб. номинальной стоимости;

Погашение - выкупная стоимость ценных бумаг за 100 руб. номинальной стоимости;

Длительность_года - число дней в году (зависит от выбранного Базиса, 360 или 365 дней);

Срок - число дней между датой расчета за ценные бумаги (аргументом Дата_согл) и датой их погашения (аргументом Дата_вступл_в_силу).

Расчет по формуле дает тот же результат:

Пример 5. Определить стоимость ценной бумаги номиналом 1000 руб. На ценную бумагу установлена скидка размером 11,5%. Дата приобретения ценной бумаги — 27.01.2006, дата погашения — 10.01.2007. Расчеты выполнить в базисе Европейский 30/360. Необходимо определить стоимость ценной бумаги на дату покупки с учетом действующей скидки. Это возможно с помощью встроенной функции ЦЕНАСКИДКА имеющей следующий формат:

=ЦЕНАСКИДКА(дата_согл; дата_вступл_в_силу; скидка; погашение; базис)

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

Решение: Введем данные в таблицу. Используя функцию, найдем решение задачи. Как видно из рисунка 4.8, на дату покупки стоимость ценной бумаги номиналом 1000 руб. равна 890 руб. 43 коп. Различные варианты применения функции, а также формула приведенная ниже дают один и тот же результат:

Рис. 4.8. Использование функции ЦЕНАСКИДКА

Окончательный вариант расчета приведен на рис. 4.9.

Рис.4.9. Окончательный расчет стоимости ценной бумаги с учетом скидки

4.3 Контрольные вопросы

1.      Дайте определение ценной бумаги.

2.      Дайте определение доходности, ликвидности, номинала.

3.      Какие функции используются для работы с ценными бумагами?

4.      Перечислите и охарактеризуйте аргументы финансовых функций Excel.