ЛаборПрактикум ИНФОРМАЦИОННЫЕ ТЕХНОЛОГИИ В ЭКОНОМИКЕ 1

МИНИСТЕРСТВО ОБРАЗОВАНИЯ РОССИЙСКОЙ ФЕДЕРАЦИИ
__________________________________
КАЗАНСКИЙ ГОСУДАРСТВЕННЫЙ ЭНЕРГЕТИЧЕСКИЙ УНИВЕРСИТЕТ



В.В. АНДРЕЕВ


Утверждено
учебным управлением КГЭУ
в качестве учебного пособия
для студентов



ЭКОНОМИЧЕСКИЕ РАСЧЕТЫ
В EXCEL

ЛАБОРАТОРНЫЙ ПРАКТИКУМ
по курсам
Технологическая обработка экономической информации,
Информационные технологии в экономике,
Информационные технологии управления











Казань 2003

УДК 681.3.06
А28



Андреев В.В. Экономические расчеты в Excel. Лабораторный практикум по курсам «Технологическая обработка экономической информации», «Информационные технологии в экономике», «Информационные технологии управления». Казань: Казан. гос. энерг. ун-т, 2003.
В лабораторном практикуме приведены основные сведения о табличном процессоре Excel, содержания лабораторных работ по выполнению экономических расчетов в Excel. Рассмотрены конкретные примеры выполнения расчетов.
Предназначен для студентов специальностей 060801, 060802 и 061102 всех форм обучения.

_____________________________




















( Казанский государственный энергетический университет, 2003 г.







Предисловие

Успех в современном бизнесе и менеджменте во многом опирается на оперативный анализ экономической ситуации и выбор оптимального решения из множества возможных альтернатив. Решение этих задач часто осложняется огромным количеством информации и сложностью проблем. Перебор огромного количества вариантов и выбор из них наилучшего невозможен без использования вычислительной техники и соответствующего программного обеспечения. Одним из современных компьютерных средств является программа Microsoft Excel.
Этот класс программных продуктов является одним из самых распространенных. Электронный процессор Excel позволяет без специальных навыков создавать достаточно сложные приложения, которые удовлетворяют основные запросы пользователей.
В лабораторном практикуме на различных примерах рассматривается возможность Excel по выполнению экономических и финансовых расчетов. Практикум содержит необходимое количество материала, что делает его достаточным для начального освоения процессора Excel без привлечения дополнительной литературы.
Лабораторный практикум содержит описание восьми лабораторных работ.
Лабораторная работа №1 представляет собой введение в табличный процессор Excel. Целью работы является ознакомление с возможностями табличного процессора. Рассматриваются вопросы, связанные с вводом информации в рабочий лист и работой с ячейками.
В лабораторной работе №2 рассматриваются расчеты в таблицах и вычисления с помощью функций Excel. Целью работы является приобретение навыков вычисления по формулам, расчет сумм, средних, поиск минимумов и максимумов, ознакомление с математическими, логическими и статистическими функциями.
В третьей лабораторной работе рассматриваются вопросы построения диаграмм и графиков.
В лабораторных работах №№ 4 – 6 рассматриваются вопросы, связанные с базами данных. Рассмотрены примеры взаимодействия данных, расположенных на различных рабочих листах. Рассмотрены методы сортировки, фильтрации и упорядочения данных. Выполнен анализ данных с помощью консолидации данных и сводных таблиц
Лабораторные работы №№ 7 и 8 посвящены некоторым финансовым функциям пакета Excel. Рассматриваются финансовые функции для расчета амортизационных отчислений, рентных платежей, денежных потоков и операций с ценными бумагами.

Лабораторная работа №1. Введение в табличный процессор Excel

Цель работы: Ознакомление с возможностями табличного процессора Excel. Ввод информации в рабочий лист и работа с ячейками. (4 часа.)

Табличный процессор Excel является одним из приложений группы Microsoft Office. Excel предоставляет пользователю широкие возможности по составлению и обработке финансовых и экономических моделей и состоит из типичных для среды Windows элементов, поддерживаемых ее стандартным графическим интерфейсом.
Функциональные возможности Excel настолько широки, что его, в отличие от обычных табличных редакторов, называют табличным процессором. Он поддерживает, в частности, следующие функции:
– обеспечивает создание, обработку и расчет разнообразных таблиц;
– позволяет осуществлять их редактирование, форматирование, использовать различные шрифты Windows;
– предоставляет средства для создания деловой графики (различные типы и форматы диаграмм с логарифмическим представлением данных, погрешностью и т.п.);
– обеспечивает совместимость со всеми программными продуктами семейства Microsoft Office;
– позволяет осуществлять сложные расчеты над числовыми рядами, матрицами, комплексными числами;
– предоставляет возможность работы с базами данных как непосредственно, так и с помощью специального языка запросов;
– облегчает "связывание" различных таблиц для сложных и объемных вычислений;
– обладает большим набором специальных функций для автоматизации обработки и расчетов (финансовые, информационные, логические, статистические, текстовые, математические и др.);
– позволяет использовать для создания деловой документации как стандартные шаблоны, так и шаблоны пользователя;
– обеспечивает обмен данными как внутри Excel, так и с другими приложениями Windows через: Буфер [Clipboard], протоколы Динамический Обмен Данными [Dynamic Data Exchange] (DDE), Связь и Внедрение Объектов [Object Link Embedded] (OLE) и преобразование форматов (программы фильтрации и конвертирования);
– позволяет автоматизировать наиболее употребляемые процессы за счет использования макрокоманд. Причем поддерживаются способы как автоматического создания команд, так и программирования с помощью специального встроенного языка Visual Basic;
– обладает большим количеством элементов управления (панелями, командами и командными кнопками, пиктограммами, флажками, переключателями и т.п.), которые облегчают работу пользователя;
– позволяет создавать демонстрационные Слайд-Шоу [Slides-Show] для презентаций, семинаров, конференций;
– облегчает возможность анализа данных с помощью Диспетчера Сценариев [Scenario Manager];
– предоставляет пользователю широкий набор Мастеров Подсказок [Master Wizard] и простой доступ к справочной информации через специальное меню Помощь [Help].
Общая схема работы в Excel совпадает со стандартными правилами работы с приложениями Windows.
Приведем принятые в Excel расширения файлов:
– XLS – файл рабочей книги (Sheet);
– XLC – файл деловой графики (Chart);
– XLW – файл рабочей книги в Excel 4.0 (WorkSheet);
– XLM – файл макротаблицы (Macro);
– XLT – файл шаблона (Template);
– XLA – файл дополнительных макрокоманд (Add-Ins);
– XLB – файл описания пиктографического меню (Tools Bar);
– XLL – файл библиотеки (Library).
Как запустить Excel. Для этого достаточно воспользоваться одним из способов запуска приложений из Windows, например, дважды щелкнув мышью по пиктограмме.







На экране появится его рабочее окно со стандартными элементами интерфейса: полосами прокрутки, системным меню, полосой заголовка, строкой меню и пиктографическими панелями (см. рис. 1.1.)
В отличие от других приложений, например Word, рабочая область, называемая в Excel Рабочий Лист [Worksheet], представляет собой таблицу, разделенную на строки и столбцы.
Рассмотрим основные элементы рабочего листа:
– буквенная нумерация столбцов. Всего их в рабочем листе 256: A, B, C, ... , Z , AA, ..., AZ, BA, ..., BZ, ..., IR, ..., IV;
– цифровая нумерация строк. Всего в рабочем листе 65536 строки;
– ячейка таблицы является минимальной единицей таблицы. Перемножив количество столбцов и строк получаем, что в рабочем листе 16 777 216 ячеек;
– строка заголовка документа. По умолчанию устанавливается заголовок Книга [Book] с указанием текущего номера открытого документа;
– строка формул. Это поле для представления текущей формулы;
– указатель ячейки по сути является более яркой рамкой, выделяющей текущую ячейку;
– поле адреса ячейки. Всегда содержит поле адреса текущей ячейки;
– ярлычок рабочего листа служит для листания рабочей книги, т.е., щелкнув мышью по видимому ярлычку листа (стандартные имена Лист1, Лист2, ...) (Sheet1, Sheet2, Sheet3, ...) либо по стрелкам "Влево" или "Вправо" в горизонтальной строке, можно перейти к другому листу активной рабочей




Рис. 1.1. Рабочий стол пакета Excel

книги. Максимальное количество листов – 256. Правый щелчок мышью по ярлычку рабочего листа инициирует открытие соответствующего меню с командами:
– Вставка [Insert]. Позволяет вставлять новый рабочий лист;
– Удалить [Delete]. Служит для удаления текущего листа;
– Переименовать [Rename]. Предназначается для переименования рабочего листа;
– Переместить/скопировать [Move or Copy]. Позволяет передвинуть или скопировать лист, например в другую рабочую книгу;
– Выбрать все листы [Select All Sheets]. Предназначается для выделения всех листов рабочей книги.
Двойной щелчок мышью по ярлычку рабочего листа инициирует появление диалогового окна для его переименования.
Как определить адрес ячейки. Вы можете определить адрес (т. е. указать место расположения) любой ячейки таблицы. Для этого необходимо указать номера столбца и строки, на пересечении которых расположена требуемая ячейка. Существует два стиля определения адреса ячейки:
– Столбец-Строка. В этом случае первые символы определяют буквенный набор столбца (А, В, ..., IV), остальные – числовой номер строки (1, 2, ...). Например, запись А1 определяет ссылку на ячейку, стоящую на пересечении столбца А и строки 1;
– Строка-Столбец. Здесь ссылка на ячейку записывается следующим образом: символ R, за которым следует числовой номер строки, а затем символ С, за которым следует числовой номер столбца. Например, запись R1C1 означает ссылку на ячейку, стоящую на пересечении первой строки и первого столбца (эквивалент записи А1).
По умолчанию в Excel принят стиль ссылки Столбец-Строка. Для изменения обращения к ячейкам можно воспользоваться командой Параметры пункта меню Сервис. В открывшемся одноименном диалоговом окне вызовите опцию Общие [General] и в диалоговой области Стиль ссылок [Reference Style] отметьте требуемую круглую кнопку выбора (стиль А1 или R1C1).
Существуют следующие способы адресации ячеек.
Относительная адресация. В этом случае место расположения активной ячейки является начальным, адреса остальных ячеек указываются относительно активной. Например, в стиле R1C1: запись R[5]C определяет ссылку на ячейку, находящуюся в этом же столбце, но на 5 строк ниже относительно активной; запись RC[2] – ссылка на ячейку, расположенную в той же строке, но на два столбца правее активной; запись R[-1]C[-2] – ссылку на ячейку, находящуюся на одну строку выше активной и на два столбца левее ее. Относительный адрес активной ячейки – RC. Если используется для адресации стиль Столбец-Строка, то запись в стиле А1 адреса ячейки определяет ее относительную адресацию. Например, запись В2 указывает адрес ячейки, находящейся на один столбец правее исходной.
Относительную адресацию удобно использовать для гибкого изменения адресов ячеек при переносе одной и той же формулы в другую ячейку.
Абсолютная адресация. Такой способ позволяет однозначно определять в формулах адреса ячеек, которые не должны изменятся ни при каких условиях. Если вы работаете в стиле А1, то абсолютный адрес строки отмечается знаком доллара, т.е. запись $B$3 неизменно означает адрес ячейки, стоящей на пересечении столбца В и строки 3. При переходе в стиль R1C1 знак доллара не используется; например, запись R3C2 определяет абсолютный адрес той же самой ячейки ($B$3). В этом случае при копировании формулы никаких изменений не произойдет.
Комбинирование относительной и абсолютной адресации. Иногда бывает удобно, зафиксировав с помощью абсолютной адресации столбец, гибко изменять адрес строки в зависимости от места расположения активной ячейки. В этом случае рекомендуется комбинировать два способа адресации. Например, запись $C5 в стиле А1 означает, что номер столбца в копируемой формуле остается неизменным ($C), а номер строки будет изменятся в зависимости от места расположения активной ячейки (т.е. всегда на пять строк ниже ее). Запись C$5 означает обратное: неизменным остается номер строки

Таблица 1.1. Использование клавиатуры в Excel


п/п
Перемещение
Клавиши

1
Влево, вправо, вверх, вниз на одну ячейку
Влево, Вправо, Вверх, Вниз

2
В самый левый (первый) столбец строки
Home

3
В самый правый (последний) столбец строки
End

4
На страницу вниз
PgDn

5
На страницу вверх
PgUp

6
В начало таблицы
Ctrl + Home

7
В конец таблицы
Ctrl + End

8
В верхнюю (первую) ячейку столбца
Ctrl + Вверх

9
В нижнюю (последнюю) ячейку столбца
Сtrl + Вниз

10
В левую (первую) ячейку строки
Ctrl + Влево

11
В правую (последнюю) ячейку строки
Ctrl + Вправо

12
Вниз по рабочему листу
Ctrl + PgDn

13
Вверх по рабочему листу
Ctrl + PgUp

($5), а не столбца.
Для перехода из одной ячейки в другую достаточно установить указатель мыши на требуемую позицию. Excel предоставляет также возможности для перемещения по рабочему листу с помощью клавиатуры (табл. 1.1.).
Как выделить фрагмент в таблице. Выделение фрагмента осуществляется с помощью как мыши, так и клавиатуры (табл. 1.2.).
Для того чтобы выделить весь столбец или всю строку полностью нужно щелкнуть кнопкой мыши на заглавии столбца или строки.
Единичная ячейка, как и активная (текущая), выделяется на экране более яркой рамкой, фрагмент из двух и более ячеек – другим цветом. При этом активная ячейка в выделенном фрагменте обведена рамкой цвета выделения, но не закрашена.
Обратите внимание на то, что активной всегда считается одна ячейка!
Снять выделение можно, щелкнув вторично мышью по выделенному фрагменту или нажав любую, не указанную в табл. 1.2., клавишу.

Таблица 1.2. Способы выделения фрагментов в Excel


п/п
Вид выделяемого фрагмента
Выделение фрагмента с помощью манипулятора "мышь"
Выделение фрагмента с помощью клавиатуры

1
Столбец
Установив курсор мыши на верхнюю ячейку столбца и перетянуть мышь по способу Drag&Drop в конец столбца
Установив курсор на верхнюю ячейку столбца нажать клавишу Shift и установить курсор в конец выделяемого столбца

2
Строка
Установив курсор мыши на левую ячейку строки и перетянуть мышь по способу Drag&Drop в конец строки
Установить курсор на левую ячейку строки нажать клавишу Shift и установить курсор в конец выделяемой строки

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

4
Единичная ячейка
Установив курсор мыши в требуемую ячейку и щелкнуть
Установить курсор в требуемую ячейку и нажать клавишу Enter

5
Несколько фрагментов одновременно
1. Выделить первый фрагмент.
2. Нажать клавишу Ctrl и не отпуская ее, выделить фрагмент до конца.
3. Отпустить клавишу Ctrl. Повторить шаги 2-3 столько раз, сколько необходимо
1. Выделить первый фрагмент.
2. Нажать клавишу Ctrl и установить курсор в позицию следующего фрагмента.
3. Нажать клавишу Shift, а затем использовать клавиши перемещения по тексту для выделения фрагмента до конца.
4. Повторить шаги 2-4 столько раз, сколько необходимо

6
Весь рабочий лист
1. Установить курсор в самый верхний левый "пустой" прямоугольник и щелкнуть мышью
Нажать одновременно клавиши Ctrl+A


Какие операции можно осуществлять над выделенными фрагментами. Выделенные фрагменты можно удалять (клавиша Удалить [Del]), копировать в Буфер команда Копировать [Copy] меню Правка [Edit]), вырезать из рабочей книги и перемещать в Буфер (команда Вырезать [Cut] меню Правка), восстанавливать из Буфера в рабочий лист (команда Вставить [Paste] меню Правка), перемещать (принцип Drag&Drop) и др.
Любая таблица состоит из заголовков (вертикальных и (или) горизонтальных) столбцов и (или) строк, и информации, хранящейся в ячейках на пересечении этих строк и столбцов. К дополнительным элементам таблиц можно отнести способы ее оформления (например, графы: утверждение, наименование, подпись и т. п.).
Как создать заголовок строки или столбца. Под созданием такого заголовка будем понимать ввод соответствующего текста в самую верхнюю ячейку столбца или самую левую ячейку строки. Для ввода текста в требуемую ячейку достаточно установить в нее указатель мыши (ячейка становится активной) и осуществить ввод необходимых символов с помощью клавиатуры.
Как изменить размеры ячейки. Если вас не удовлетворяет стандартный размер ячейки, вы можете изменить его по своему усмотрению одним из следующих способов:
– с помощью мыши. Установите указатель мыши на правый край начальной ячейки столбца (самой верхней) и, в соответствии с принципом Drag&Drop передвигая его по горизонтали, измените ширину столбца. Для изменения высоты строки достаточно выполнить аналогичные действия, установив указатель мыши на нижний край ячейки строки и передвигая ее по вертикали;
– с помощью команд меню Excel. Выделите в строке меню команду Формат [Format], пункт Столбец [Column], и зафиксируйте параметр Ширина [Width]. На экране откроется диалоговое окно Ширина столбца [Column Width]. В поле введите размеры столбца в символах. Для строки в команде Формат существует аналогичный пункт Строка [Row] с параметром Высота строки [Height].
Если необходимо установить стандартный размер ячеек для рабочей книги в меню Формат – Столбец инициируется параметр Стандарт [Standart] для ширины ячейки.
Для задания оптимальной ширины и высоты ячейки, при которой они будут определяться по длине и высоте введенного заголовка, можно воспользоваться параметром Автоподбор ширины и Автоподбор высоты [AutoFitSelection] в меню Формат – Столбец и Формат – Строка.
Если заголовок столбца или строки не помещается в установленный формат ячейки, то набранный вами текст заголовка, хотя и выйдет за пределы ячейки, но будет относится только к текущей ячейке. Активизировав стоящую рядом ячейку, вы автоматически делаете невидимой часть заголовка, превысившего размеры предыдущей ячейки. Текст в ячейке сохраняется.
Иногда заголовок столбца или строки удобнее записать в несколько строк. Для этого прежде всего следует увеличить размеры (по высоте и (или) ширине) ячейки, затем вызвать диалоговое окно Формат Ячейки [Format Cells] и, выделив в нем опцию Выравнивание [Alignment], щелкнуть мышью по кнопке выбора С заполнением [Fill].
Как заполнить ячейку таблицы. Прежде, чем заполнять ячейку, рекомендуем определить характер и параметры вводимой информации. Для этого лучше всего воспользоваться опциями команды Формат ячейки . Рассмотрим возможности, предоставляемые данной командой:
– Число [Numbering]. Служит для определения формата данных и состоит из следующих полей выбора:
– Категория [Category]. Указывает категорию – тип выбранного формата (пользовательский, бухгалтерский, научный, текстовый, дробный, процентный и т.п.);
– Код Формата [Format Codes]. Определяет код – возможную структуру информации согласно выбранному вами формату. Например, при выборе категории Число в поле выбора Код Формата появятся следующие коды числа:
# ##0
# ##0,00
# ##0.\_P.;_# ##\_P_.
Выделив требуемую структуру, укажите необходимое представление информации в ячейке:
– Код [Code]. Служит для просмотра выбранного кода;
– Образец [Sample]. Предоставляет возможность предварительного просмотра выбранных формата и кода;
– Выравнивание [Alignment]. Указывает способ выравнивания информации в ячейке. Для этой цели предназначены следующие диалоговые области вывода:
– Горизонтальное [Horizontal]. Определяет вариант горизонтального выравнивания: Обычное [General], Слева [Left], Справа [Right], С заполнением [Fill], По обоим краям [Justify], Центрировать по выделению [Center across selection];
– Вертикальное [Vertical]. Указывает вариант вертикального выделения: По верхнему краю [Top], По центру [Center], По нижнему краю [Bottom], По обоим краям [Justify];
– Ориентация [Orientation]. Служит для определения ориентации информации в ячейке: по горизонтали слева направо; по вертикали сверху вниз; по вертикали слева направо, снизу вверх; по вертикали справа налево, сверху вниз;
– Переносить по словам [Wrap]. Определяет способ переноса текста внутри ячейки;
– Шрифт [Font]. Позволяет установить параметры шрифта вводимой информации;
– Рамка [Border]. Служит для выбора рамки таблицы и (или) ячейки;
– Вид [Pattern]. Предназначается для установки параметров изображения: закрашивание ячеек, цвет, узор, и т.п.;
– Защита [Protection]. Позволяет указать блокировку информации, которая будет хранится в ячейках. Для защиты можно выбрать пароль, без знания которого доступ к заблокированной информации невозможен.
После определения структуры вводимой информации можно осуществлять ее ввод в активную ячейку.
Заметим, что по умолчанию текстовая информация выравнивается по левому краю, а числовая – по правому.
Как сформировать название таблицы. Заголовок (название) таблицы обычно занимает одну или более строк и располагается в верхней части рабочего листа до заголовка столбцов и строк. Сформировать его можно с помощью следующих действий.
1. Установите указатель мыши в самую левую верхнюю ячейку таблицы.
2. Введите название таблицы, используя символ пробела для разделения строк.
3. Щелкните мышью по "галочке" в строке формул или просто нажмите клавишу Enter.
4. Если для названия таблицы требуется несколько строк, то необходимо повторить п. 1–3 для каждой новой строки.
Аналогичным образом можно ввести графы: утверждение, фирменная информация и т. п., в верхней части листа. Для изменения стиля оформления заголовка (шрифтов, размеров, расположения на бланке) можно выделить его одним из способов, предоставляемых Excel (см. табл. 1.2.) и отформатировать необходимым образом с помощью команд меню Формат.
Как сохранить документ на диске. Для этого достаточно воспользоваться стандартными командами Сохранить [Save] и Сохранить как [Save as] меню Файл [File].
Как прочитать ранее сохраненный документ с диска. Следует выполнить команду Открыть [Open] меню Файл.
Как снять сетку таблицы. Иногда удобнее работать с рабочим листом без координатной сетки. Для снятия ее можно воспользоваться командой Параметры [Options] меню Сервис [Tools]. На экране появится одноименное диалоговое окно с различными опциями. В данном случае нам необходима опция Вид [View]. В диалоговой области Параметры окна [Window Options] "выключите" кнопку выбора Сетка [Gridines] ("крестик" в этой прямоугольной кнопке пропадет). Выбрав кнопку OK, вы подтвердите свой выбор, после чего координатная сетка на рабочем листе исчезнет. Для ее восстановления достаточно снова "включить" данную кнопку выбора.
Как напечатать подготовленный документ. Для вывода на печать содержимого рабочего листа (книги) достаточно воспользоваться командой Печать [Print] меню Файл. Сначала с помощью команд этого же меню можно установить тип рабочего принтера (меню Выбор Принтера [Print Setup]), параметры страницы (меню Параметры страницы [Page Setup]). Для предварительного просмотра рабочего листа воспользуйтесь командой Предварительный просмотр [Print Preview]. При вызове ее на экране появится соответствующее диалоговое окно с опциями:
– Далее [Next] – служит для перехода к следующему фрагменту рабочего листа, если он не помещается в рабочем окне;
– Назад [Previous] – предназначается для вызова в рабочее окно предыдущего фрагмента листа;
– Масштаб [Zoom] – позволяет изменить размеры текущего изображения (увеличивать или уменьшать). Если щелкнуть мышью, то изображение увеличится ровно в два раза;
– Печать [Print] – посылает измененное изображение на печать;
– Страница [Setup] – вызывает диалоговое окно Параметры страницы;
– Поля [Margins] – определяет границы области печати, изменить которые можно, установив указатель мыши на рамку страницы и используя принцип Drag&Drop;
– Закрыть [Close] – закрывает данное диалоговое окно;
– Справка [Help] – позволяет просматривать справочную информацию.

Задание по лабораторной работе. Введите следующую таблицу:

Амортизация по остаточной стоимости


Годы
Остаточная стоимость
Амортизация
Остаточная стоимость


(на начало периода)
30%
(на конец периода)






1
600000
180000
420000

2
420000
126000
294000

3
294000
88200
205800

4
205800
61740
144060

5
144060
43218
100842


Выполнение задания.
1. Переместите указатель мыши на ячейку C1 и щелкните левой кнопкой мыши. Наберите слова: Амортизация по остаточной стоимости
2. Нажмите клавишу Enter
3. В ячейку A3 введите слово: Годы
4. В ячейку B3 введите слова: Остаточная стоимость
5. В ячейку B4 введите слова: (на начало периода)
6. В ячейку E3 введите слова: Амортизация
7. В ячейку E4 введите слова: 30 %
8. В ячейку G3 введите слова: Остаточная стоимость
9. В ячейку G4 введите слова: (на конец периода)
10. Введите числа в ячейки:
в ячейку B6 – 600000; в ячейку E9 – 61740;
в ячейку B6 – 600000; в ячейку E9 – 61740;
в ячейку B7 – 420000; в ячейку E10 – 43218;
в ячейку B8 – 294000; в ячейку G6 – 420000;
в ячейку B9 – 205800; в ячейку G7 – 294000;
в ячейку B10 – 144060; в ячейку G8 – 205800;
в ячейку E6 – 180000; в ячейку G9 – 144060;
в ячейку E7 – 126000; в ячейку G10 – 100842.
в ячейку E8 – 88200.
После ввода всей информации и данных получается таблица, приведенная на рис. 1.2.



Рис. 1.2

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

Величина амортизацииi = Остаточная стоимость (на начало периода)i *Коэффициент амортизации,

Остаточная стоимость (на конец периода)i = Остаточная стоимость (на начало периода)i – Величина амортизацииi,

Остаточная стоимость (на начало периода)i+1 = Остаточная стоимость (на конец периода)i.
Поместим в ячейку B13 число 600000. В ячейку E13 введем команду

=B13*E4.

В ячейке E13 появится число 180000. В ячейку G13 введем команду

= B13-E13.

После этого в ячейке появится число 420000. И, наконец, в ячейку B14 введем команду

= G13.

В результате в этой ячейке получим число 420000.
Оставшуюся часть таблицы получим копированием содержимого ячеек. Так как процент амортизации у нас постоянный, то следует сделать так, чтобы адрес ячейки, содержащий значение процента амортизации, в процессе копирования не менялся. Для этого отредактируем команду в ячейке E13. Сделаем адрес ячейки E4 абсолютным. Это осуществляется путем постановки перед номером столбца и строки знака «$». Редактирование осуществляется в командной строке. Таким образом, команда примет следующий вид

= B13*$E$4.

Затем пометим диапазон ячеек E13:G13. Для копирования поместим указатель мыши на квадратик копирования в правом нижнем углу ячейки и, не отпуская нажатую левую кнопку мыши, протащим указатель мыши на четыре строчки вниз. Осталось только скопировать аналогичным образом команду из ячейки B14. После копирования получим таблицу аналогичную ранее полученной.
Рассмотрим случай зависимости коэффициента амортизации от времени. Предположим, что коэффициент амортизации увеличивается с течением времени. Пусть этот коэффициент увеличивается каждый год на 10% (этот пример имеет только демонстрационный характер).
Поместим в ячейки A13:A14 цифры 1, 2. Это будут номера первого и второго годов. Дальнейшие номера введем в режиме автозаполнения. Для этого пометим ячейки A13:A14 и затем, поместив указатель мыши на квадратик копирования, протащим указатель на три ячейки вниз. В результате этих действий в ячейках A13:A17 получим номера годов, на которые рассчитывается амортизация.
Отредактируем команду в ячейке E13 следующим образом

= B13*($E$4+(A13-1)*0,1).

После этого скопируем содержимое ячейки E13 в диапазон ячеек E14: E17. Таким образом, получаем таблицу учета амортизации с переменным коэффициентом амортизации.



Рис.1.3
Лабораторная работа №2. Расчеты в таблицах и вычисления с помощью функций Excel

Цель работы: вычисления по формулам, расчет сумм, средних, поиск минимумов и максимумов, функции математические, логические, статистические. (4 часа.)

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


Рассмотрим схему применения данной пиктограммы.
1. Выделите фрагмент с информацией, которую необходимо просуммировать.
2. Установите указатель мыши на место вывода суммы и щелкните по нему.
3. Щелкните мышью по пиктограмме автосуммирования. В ячейке, определенной в п. 2, появится наименование процедуры суммирования (СУММ) с указанием диапазона ячеек, значения которых предназначены для суммирования.
4. Для подтверждения правильности суммирования щелкните мышью по этой пиктограмме еще раз.
Если вы пропустите первый шаг (выделение), то после первого щелчка активная ячейка будет выделена пульсирующей пунктирной рамкой. Расширить выделение суммируемой информации можно любым из способов, описанных в табл. 1.2.
После выполнения вышеописанных действий в ячейке, выделенной для суммирования, появится результат суммирования выделенной информации.
Как ввести формулу. В Excel существует несколько способов создания формул расчетов. Рассмотрим некоторые из них. Определим некоторые операции, операнды и операторы, которые могут использоваться в формулах. В табл. 2.1. приведены основные операции Excel.

Табл. 2.1. Операции Excel


п/п
Операции
Действия
Приоритет выполнения операций

1
Арифметические
+
- (двухместный)
*
/
%
^

- (одноместный)

Сложение
Вычитание
Умножение
Деление
Процент
Возведение в степень
Инвертирование

7
7
6
6
4
5
3

2
Сравнение
<
>
=
<=
>=
<>

Меньше
Больше
Равно
Меньше или равно
Больше или равно
Не равно

10
12
9
11
13
14

3
Адресные (ссылки)
;
&
Пробел

Объединение адресов
Объединение текстов
Пересечение фрагментов

2
8
1


Операндами в формуле могут быть:
– относительные или абсолютные адреса ячеек;
– определенные имена фрагментов;
– константы текстовые или числовые (например, 2, -3.5, строка символов "abc" и т. п.);
– имена и аргументы специальных функций.
Excel обладает обширным набором стандартных функций (финансовых, даты и времени, базы данных, информационных, логических, математических, просмотра и ссылок, статистических, текстовых), которые упрощают расчеты в таблицах.
Для ввода формулы выполните следующие действия.
1. Установите указатель мыши в ячейку результата.
2. Введите первый символ формулы – знак "=".
3. Введите операнды и операции формулы таким образом, чтобы между операндами не было пробелов (см. табл. 2.1., символ пробела сам по себе является операцией).
Если в операнде-функции есть несколько аргументов, они должны разделятся знаком "," и помещаться в круглые скобки. В случае отсутствия аргументов скобки все равно должны присутствовать, но между ними уже не будет пробела (т. е. 0). Если в качестве операнда используется текстовая информация, она должна быть заключена в кавычки.

Таблица 2.2. Примеры записи в формулах в Excel


п/п
Формула
Пояснение

1
А1+В2*(C1-D1)/4
В предположении, что в отмеченных ячейках хранится следующая информация: A1=5, B2=4, C1=40, D1=24, результат вычислений этой формулы 5+4*(40-24)/4=21

2
СУММ (A1:B3;C1:D4)

Функция выполняет операцию суммирования. Суммируется содержимое всех ячеек двух фрагментов: A1, B1, C1, D1, A2, B2, C2, D2, A3, B3, C3, D3, C4, D4

3
ЕСЛИ (A1Логическая функция ЕСЛИ проверяет значение логического выражения в скобках на истинность. Если содержимое ячейки А1 меньше содержимого ячейки С2, в ячейку результата записывается информация, хранящаяся в ячейке D1, иначе – в ячейке В4

4
СЦЕПИТЬ("abc"; "def")

Функция СЦЕПИТЬ объединяет текстовые аргументы в одну строку. После выполнения этой функции в ячейку результата запишется текст "abcdef"

При отсутствии скобок первой выполняется операция с наименьшим приоритетом.
В табл. 2.2. приведены примеры записи формул в Excel.
Как создать формулу с помощью Мастера Функций. Для упрощения ввода формулы и (или) функции можно воспользоваться Мастером Функций.



Рис. 2.1

Вызвать Мастер функций можно или из меню Вставка или щелкнув мышью по пиктограмме
13 EMBED Word.Picture.6 1415
На экране появится первое диалоговое окно Мастера Функций (рис. 2.1).
Затем в списке Категория необходимо выбрать категорию, в которой предположительно может находится необходимая функция. После выбора подходящей категории из списка Функции следует выбрать нужную функцию. При этом в нижней части диалогового окна программа выдаст краткое описание данной функции, а именно – информацию о результате, который будет получен вследствие применения этой функции. Если же этой информации будет недостаточно, то нажатие кнопки Справка дает возможность получить более подробные сведения о выбранной функции.
После нажатия кнопки Далее на экране появится второе диалоговое окно Мастера функций (рис. 2.2.), в котором следует задать аргументы функции.
Для ввода каждого аргумента в диалоговом окне предусмотрены отдельные поля ввода. Переход от одного поля ввода к другому осуществляется с помощью клавиши [Tab]. При помещении курсора в поле ввода в диалоговом окне будет представлена краткая информация об аргументе. В поле Значение в правом верхнем углу окна отображается текущее значение функции. Задать аргумент функции можно и путем выделения ячейки или диапазона ячеек непосредственно в рабочем листе. Для этого следует поместить курсор ввода в поле задания соответствующего аргумента и выделить нужную ячейку или диапазон.



Рис. 2.2

В случае необходимости задания в качестве аргумента другой функции (вложенная функция) следует нажать кнопку в строке формул, расположенную перед полем ввода аргумента. После ее нажатия на экране появится первое диалоговое окно Мастера функций, в котором следует выбрать функцию, выступающую в качестве аргумента. Закончив задание аргументов вложенной функции, следует нажать кнопку Готово, чтобы вернуться в диалоговое окно задания других аргументов основной функции. После ввода всех аргументов вновь нажимается кнопка Готово – и функция будет вставлена в рабочий лист.
Если результат вычисления слишком велик, можно представить его в виде десятичного числа либо установить разделитель (запятую или пробел) между любыми тройками цифр получившегося результата, что значительно облегчает его чтение. Для этого служит специальная кнопка в виде запятой в пиктографическом меню (повторный щелчок по ней снимает установку разделения).
Многие функции становятся доступными только после установки дополнения Пакет анализа. Если это дополнение не установлено, то следует выбрать команду Надстройки меню Сервис, активизировать Диспетчер надстроек и в появившемся диалоговом окне выделить элемент Пакет анализа. При описании функции указывается, является ли используемая функция доступной без установки дополнения (надстройки) Пакет анализа.
Наиболее часто в Excel используются следующие стандартные математические функции:

Функция
Результат

ABS(аргумент)
Абсолютная величина

ACOS(аргумент)
Арккосинус

ASIN(аргумент)
Арксинус

ATAN(аргумент)
Арктангенс

COS(аргумент)
Косинус

EXP(аргумент)
Экспонента

LN(аргумент)
Натуральный логарифм

LOG10(аргумент)
Десятичный логарифм

LOG(аргумент; основание)
Логарифм аргумента по данному основанию (если основание опущено, то оно полагается равным 10)

ПИ( )
Число пи

SIN(аргумент)
Синус

TAN(аргумент)
Тангенс


Как автоматически сформировать списки в таблицах. При заполнении таблиц может возникнуть ситуация, при которой необходимо заполнить столбец или строку по заданному закону, где каждое последующее значение завист от предыдущего (например, составить столбец чисел по формуле A0 =1; A1 = A0 +5; A2 = A1+5; ...; AN = AN-1+5, т. е. получить следующий столбец: 1, 6, 11, 16, 21, ...). Excel позволяет автоматизировать данный процесс. Для этого достаточно выделить в таблице столбец или строку для заполнения, в меню Правка [Edit] отметить пункт Заполнить [Fill] и инициализировать команду Прогрессия [Series]. На экране появится диалоговое окно с опциями:
– Прогрессия. Содержит кнопки выбора для указания элементов упорядочения: По столбцам [Columns], По строкам [Rows]. Это значит, что ряд чисел будет расположен в столбце (строке), где находится первый элемент прогрессии;
– Тип [Types]. Указывает тип прогрессии: Арифметическая [Linear], Геометрическая [Growth], Даты [Date], Автозаполнение [Autofill];
– Единицы [Date Unit]. Определяет минимальную единицу, на которую будет изменятся значение даты: День [Day], День недели [Week Day], Месяц [Month], Год [Year];
– Шаг [Step Value]. Служит для установки шага приращения; по умолчанию шаг равен единице. Если необходимо установить собственное приращение, введите в поле ввода необходимое значение;

Таблица 2.3. Основные функции над данными

№ п/п
Действие
Функция
Аргументы функции (не более 14)

1
Вычислить среднее значение всех данных
СРЗНАЧ (N1;N2;...)
Числовые

2
Определить количество чисел в указанных аргументах
СЧЕТ(V1;V2;...)
Ячейки, числа, текстовые представления чисел, матрицы, ссылки на область

3
Подсчитать количество непустых значений (любого типа) в указанных аргументах
СЧЕТЗ(N1;N2;...)
Любые значения

4
Найти наибольшее значение
МАКС(N1;N2;...)
Числовые, либо преобразуемые в числовые, значения

5
Определить минимальное значение
МИН(N1;N2;...)
Числовые

6
Вычислить произведение данных
ПРОИЗВЕД(N1;N2;...)
Числовые

7
Найти сумму данных
СУММ(N1;N2;...)
Числовые


– Предельное значение [Stop Value]. Устанавливает, до какого конечного значения заполнять столбец или строку. Если это значение не указано, то заполнение ограничивается количеством ячеек в выделенном столбце или строке.
Как использовать функции над данными при работе Excel. Некоторые функции пакета описаны в табл. 2.3.

Лабораторная работа №3. Построение диаграмм и графиков

Цель работы: ознакомление с возможностями Excel по построению графиков. (4 часа.)

Как строить диаграммы и графики по таблицам. Для этой цели служит так называемый Мастер Диаграмм [Chart Wizard], вызвать который можно с помощью команды Диаграмма [Chart] меню Вставка [Insert]. На экране появится диалоговое окно – Мастер Диаграмм – шаг 1 из 5 [Chart Wizard – Step 1 of 5]. Вызвать Мастера Диаграмм можно и из пиктографического меню.

После вызова (любым способом) Мастера Диаграмм на экране появится уменьшенная копия его пиктограммы. Используя принцип Drag&Drop, установите ее в место размещения диаграммы и щелкните мышью.
Если вы предварительно выделили некоторый фрагмент таблицы, то эта выделенная область будет указана в поле Диапазон [Range]. Если нет, то введите в это поле диапазоны фрагментов таблицы для построения диаграммы.
Если вы включите параметр выделения заголовка строк и столбцов, то на диаграмме эти заголовки будут присутствовать в качестве координат. Щелкнув мышью по кнопке Далее [Next], вы перейдете ко второму диалоговому окну Мастера Диаграмм – шаг 2 из 5, на котором указаны 15 типов различных диаграмм. Выделив требуемый вам тип, перейдите к третьему шагу Мастера Диаграмм, который позволяет выбрать необходимый формат диаграммы. На экране появится окно четвертого шага Мастера Диаграмм с предварительным изображением построенной диаграммы. Справа от изображения указаны диалоговые области, позволяющие установить его базовые параметры:
– Ряды данных находятся в [Data Series in]. Указывает размещение данных: Строках [Rows] или Столбцах [Columns];
– Считать ... столб. метками оси X (Категорий) [User First ... Column[s] for category [X] Axis Labels]. Служит для указания количества позиций, которые следует выделить для наименования градаций оси X;
– Считать ... стр. метками легенды [User First ... Row[s] for Legend Text]. Представляет возможность выделить определенное количество строк, в которых будет расположена надпись к диаграмме (легенда). Перейдя к пятому и последнему шагу Мастера Диаграмм, вы увидите окончательное изображение построенной диаграммы. Диалоговые области этого окна позволяют добавить надпись к диаграмме – Добавить легенду [Add a Legend] и определить содержимое этой надписи – Название диаграммы [Chart Title], заголовки координатных осей – Название осей [Axis Titles] (Категорий [Category] X, Значений [Value] Y, Вторая [Second] Y). Для изменения диаграммы вернитесь к предыдущему шагу, иначе – щелкните мышью на кнопке Готово [Finish].
После фиксации команды Готово в указанном месте рабочего листа появится построенная диаграмма.



Рис. 3.1

На рис. 3.1 проиллюстрированы понятия, введенные выше.
Введите следующую таблицу:


Рис. 3.2
После выделения ячеек B3:E7, входим в меню Вставка в появляющемся подменю выбираем пункт Диаграмма и На этом листе. Мышью выбираем место на рабочем листе и щелкаем клавишей. Появляется диалоговое окно Мастер Диаграмм – шаг 1 из 5. Если диапазон ячеек указан правильно, то

13 EMBED Word.Picture.6 1415Рис.3.3

нажимаем на кнопке Далее. Появляется окно Мастер Диаграмм – шаг 2 из 5, предлагающее выбрать тип диаграммы. Щелчком мыши выбираем Круговая (в виде пирога). В окне Мастер Диаграмм – шаг 3 из 5 выбираем вид диаграммы 7. В диалоговом окне Мастер Диаграмм – шаг 4 из 5 нажимаем на кнопку Далее. И, наконец, в окне Мастер Диаграмм – шаг 5 из 5 нажимаем на кнопку Готово. В результате получаем диаграмму, приведенную выше (рис. 3.3.) и иллюстрирующую процентный вклад в общий доход каждого члена семьи.
Как отредактировать построенную диаграмму. Для этого достаточно дважды щелкнуть мышью внутри области диаграммы. Появившаяся рамка с отмеченными узлами позволяет изменять размеры изображения и перемещать его по рабочей книге. Теперь щелчок на любом элементе диаграммы приведет к его выделению, а в меню Формат появятся команды, которые можно использовать для форматирования выделенного элемента.
Многие элементы диаграммы объединены в группы.
Для того чтобы выделить элемент группы, нужно в активизированной диаграмме один раз щелкнуть мышью по группе, второй раз щелкнуть по отдельному элементу группы. Выделенный элемент диаграммы можно переместить, изменить его размер, отредактировать содержание, изменить формат.


Рис. 3.4

Для того чтобы ввести в диаграмму метки данных, нужно после активизации диаграммы выбрать в меню Вставка команду Метки значений. На экране появится диалоговое окно Метки значений. Выбрать в диалоговом меню нужные параметры и нажать кнопку Ok (рис. 3.4.).
Для того чтобы изменить формат меток данных нужно дважды щелкнуть мышью на произвольной метке данных любого ряда. На экране появится диалоговое окно Форматирование меток данных. Далее задаем все необходимые изменения формата на вкладках Вид, Шрифт, Число, Выравнивание. Для того чтобы задать такой же формат для других меток данных, нужно их выделить и нажать клавишу F4. Нажать кнопку Ok.
Если возникает необходимость дополнить диаграмму другими рядами данных, то следует выделить построенную диаграмму (дважды щелкнуть в области диаграммы мышью). Затем выбрать в меню Вставка команду Новые данные для представления на экране одноименного диалогового окна, выделить диапазон ячеек, содержащий новые данные (включая метки столбцов и строк, если те должны быть отображены в диаграмме), и нажать кнопку ОК. Новые данные будут отображены на диаграмме.
В том случае, если был создан вариант диаграммы со многими установками, отличающимися от первоначальных, то может оказаться желательным сохранить этот вариант диаграммы в качестве нового типа (автоформат). Для этого необходимо активизировать в меню Формат команду Автоформат и в появившемся на экране одноименном диалоговом окне в группе Типы диаграмм установите опцию Дополнительные. В левой части окна появится список дополнительных типов диаграмм. Нажмите кнопку Настроить для перехода в диалоговое окно управления дополнительными типами диаграмм. Чтобы добавить новый тип, нажмите кнопку Добавить и укажите в диалого-


Рис. 3.5

вом окне название и краткое описание типа, затем нажатием кнопки Ok закройте диалоговое окно Добавление нового типа диаграммы. В дальнейшем для применения созданного типа диаграммы следует активизировать нужную диаграмму, выбрать команду Автоформат меню Формат и в одноименном диалоговом окне после установки опции Дополнительные выбрать созданный тип. В результате нажатия кнопки Ok данный тип будет применен к активной диаграмме.

Задание по лабораторной работе. Ввести данные по магазину «Одежда» (рис.3.1) построить диаграмму и осуществить редактирование элементов диаграммы (рис. 3.4 – 3.5).
Лабораторная работа № 4. Базы данных

Цель работы: рассмотрение вопросов, связанных с базой данных и взаимодействием данных, расположенных на различных рабочих листах. (4 часа.)

Использование Excel в качестве базы данных значительно облегчает управление кадрами, логистическими процессами и т.п. В качестве базы данных будем использовать список, где строки соответствуют записям в базе данных, а столбцы – полям. При выполнении основных операций со списком он обрабатывается как база данных.
Удобство работы со списками заключается в том, что список можно создавать непосредственно в рабочем листе. Преимуществом использования функций списка является возможность комбинирования этих функций с другими функциями программы.
Создание списка клиентов. Воспользуемся для создания списка обычным рабочим листом. Согласно содержанию листа назовем его Клиенты. Первый рабочий лист будет содержать общие данные о клиентах, второй – данные о предлагаемых товарах, в третьем будет организован учет заказов.
Введем в строку 1 название полей первого списка. В ячейках A1 – I1 следующие названия:



Рис. 4.1

Название фирмы
Код
Контактная персона
Индекс
Город
Улица
Факс Телефон
Скидка (%)
Во втором поле с целью автоматизации выполнения в будущем некоторых операций указывается код клиента, который может иметь произвольный вид, например 101, 102, 103 и т.д. Для некоторых клиентов возможно представление скидки. Это обстоятельство отражается в последнем поле списка. После ввода названий полей необходимо изменить ширину столбцов и отформатировать последнее поле с помощью процентного стиля.
После ввода данных о клиентах получаем таблицу, представленную на рис. 4.1.
Ввод данных. Можно вводить данные непосредственно в ячейки таблицы, однако, лучше воспользоваться специальным диалоговым окном – формой данных, в котором легко можно выполнить все операции над записями: ввод, редактирование, поиск. Чтобы открыть на экране диалоговое окно формы данных, необходимо выбрать в меню Данные команду Форма.
После нажатия кнопки Ok в окне запроса на экране появится диалоговое окно формы данных с именем Клиенты (под этим именем был запомнен первый рабочий лист файла). В диалоговом окне формы данных рядом с названием полей создаваемого списка находится поле ввода, в которое и будут вводится данные. Введите в соответствующие поля данные о клиентах, завершая ввод каждой записи нажатием кнопки Добавить. Переход между отдельными полями ввода диалогового окна осуществляется посредством щелчка кнопкой мыши или с помощью нажатия клавиши [Tab]. После ввода последней записи щелкните на кнопке Закрыть.
Диалоговое окно формы данных можно использовать и для обработки записей. Функциональные кнопки Назад и Далее позволяют посмотреть все записи. С помощью кнопки Удалить ненужная запись может быть удалена. После редактирования записи становится доступной кнопка Вернуть. Ее нажатие приводит к восстановлению прежнего вида записи.
С помощью диалогового окна формы данных можно выполнить поиск нужных записей, задавая различные критерии поиска. После нажатия кнопки Критерии на экране появится незаполненная форма с текущими названиями полей. Укажите в соответствующем поле ввода значение, которое должно выступать в качестве критерия и запустите поиск нажатием кнопки Далее. При задании критерия можно также использовать символы подстановки, если точное написание значения неизвестно. Символ (*) служит для обозначения любого количества неизвестных символов, а символ (?) – для обозначения одного символа.
Чтобы завершить работу со списком, отсортируем его (по возрастанию) по коду фирмы-клиента. Кроме того, большое значение в последующей работе могут иметь имена, присваиваемые диапазонам ячеек. (С их помощью задавать аргументы функций гораздо проще, и в этом случае можно ввести любое количество данных). Поэтому выделим столбец A с помощью щелчка на заголовке столбца и в поле имени укажем имя Фирма. Затем столбцу B присвоим имя Код, а столбцу C – Скидка. Если при вводе имени была сделана ошибка и следует неправильно введенное имя удалить, то воспользуйтесь командой Имя/Присвоить меню Вставка. В появившемся диалоговом окне выберите имя и нажмите кнопку Удалить.
Создание списка товаров. Второй список примера будет содержать данные о предлагаемых фирмой товарах. Каждому товару следует присвоить определенный номер, что в последующем поможет автоматизировать выполнение определенных операций. Создаваемый список в рабочем листе с названием Товары должен состоять из трех полей: Номер, Наименование товара и Цена.


Рис. 4.2

Введем указанные названия полей в ячейки A1 – C1 и сразу же присвоим имена ячейкам столбцов A, B и C – Номер, Наименование товара и Цена соответственно. Затем вводим данные. При этом желательно, чтобы номера были расположены в порядке возрастания. В противном случае после ввода данных отсортируйте список по номеру товара. После ввода данных получаем таблицу, показанную на рис. 4.2.
Создание списка заказов. Этот список должен помочь в управлении данными обо всех выполненных с начала текущего года заказах. При этом будет сделана попытка максимальной автоматизации процедуры ввода данных. Затем представленные в списке данные будут проанализированы с помощью мастера сводных таблиц.
Создадим структуру списка. Для этого в ячейках A1–L1 надлежит указать следующие названия полей:
Месяц
Дата
Номер заказа
Номер товара
Наименование товара
Количество
Цена за ед.
Код заказчика
Название фирмы
Сумма заказа
Скидка
Уплачено
Затем выделите строку с названиями полей, выберите нужные параметры шрифта, а также задайте для названия полей центрирование (для этого выполните щелчок на кнопке По центру в панели инструментов Форматирование) и разрешите перенос по словам в пределах одной ячейки (выберите команду Ячейки меню Формат и активизируйте в разделе Выравнивание появившегося на экране диалогового окна опцию Переносить по словам). Присвоим ячейкам некоторых столбцов имена. Выделим по очереди столбцы B,



Рис. 4.3

C, D, E, F, G, H, I, J, K, L и введем в поле имени имена: Дата, Заказ, Номер2, Товар, Количество, Цена2, Код2, Фирма2, Сумма, Скидка2 и Оплата. До ввода данных в список определим нужные форматы и то, какие значения в каких полях должны быть указаны. В ячейках поля Месяц будем указывать названия месяцев. Добиться автоматического определения месяца на основе введенной даты с одной стороны достаточно просто, однако, часто вследствие применения различных форматов даты используемая для этого функция МЕСЯЦ будет возвращать не совсем корректный результат. Поэтому на этом этапе будем вводить название месяца. Столбец B предполагается использовать для даты выполнения заказов. До ввода дат выделим столбец B с помощью команды Ячейка меню Формат, в открывшемся на экране одноименном диалоговом окне активизируем раздел Число и выберем в категории Числовые форматы/Дата желаемый формат даты.
Третий столбец должен содержать номер заказа. Затем переходим в ячейку D2. Во всех ячейках четвертого столбца должен быть указан номер заказываемого товара в соответствии с нумерацией, используемой в рабочем листе Товары. В ячейке H2 следует указать код фирмы-заказчика. Код придется ввести с клавиатуры. Таблица, полученная после ввода данных и имеющая название Заказы, приведена на рис. 4.3.
Теперь необходимо задать формулы. В столбце E должно быть представлено наименование товара, при этом целесообразно выполнять это автоматически с помощью формулы. Для этого укажем в ячейке E2 формулу:

= ЕСЛИ($D2=«»; «»; ПРОСМОТР($D2; Товары!A$2:A$12;Товары! B$2: B$12)).

Формулы целесообразно вводить в «русском регистре», а для ввода адресов ячеек следует указать мышкой на соответствующий рабочий лист и затем нужную ячейку. Для ввода знака доллара следует поставить указатель мышки в строке формул перед соответствующим адресом ячейки и нажать клавишу F4.
Функция ЕСЛИ проверяет содержимое ячейки D2. Если в ячейке D2 данные отсутствуют, то ячейка E2 также остается незаполненной. Если же в ячейке D2 уже введен номер товара, то будет выполняться поиск номера товара в диапазоне Товары!A$2:A$12 и в ячейку E2 возвратится соответствующее значение из диапазона Товары!B$2:B$12. Для этого используется функция ПРОСМОТР. Вставим эту формулу в ячейку E2 с помощью мастера функций. Заметим, что для ячейки D2 задана комбинированная ссылка, при копировании формулы это приведет только к изменению номера строки.
В ячейки столбца F следует ввести заказываемое клиентом количество того или иного товара. В столбце G (поле Цена за ед.) надо указать цену единицы товара. Поскольку цена уже встречалась, то ее вставку можно задать с помощью формулы, аналогичной вставленной в ячейку E2. Формула в ячейке
G2 должна иметь вид:

=ЕСЛИ($D2=«»; «»; ПРОСМОТР($D2; Товары!A$2:A$12; Товары! C$2: C$12)).

Зададим автоматическое заполнение ячеек полей Название фирмы и Скидка с помощью формул. Но теперь в качестве отправного пункта будет выступать значение в ячейке H2. Введем в ячейку I2 формулу:

= ЕСЛИ($H2 = «»; «»; ПРОСМОТР($H2; Клиенты!B$2:B$14;
Клиенты! A$2:A$14)).

В поле Сумма заказа укажите общую стоимость заказа без учета скидок. Для этого следует перемножить значения в полях Количество и Цена. Можно также с помощью логической функции ЕСЛИ задать не заполнение ячеек в том случае, когда запись не введена, что позволит избежать появления ошибки. Поэтому формула в ячейке J2 должна иметь вид:

= ЕСЛИ(F2= «»; «»; F2*G2).

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



Рис. 4.4

Величину скидки (поле Скидка) также можно определить автоматически. Для этого достаточно ввести в ячейку K2 формулу:

= ЕСЛИ($H2= «»; «»; ПРОСМОТР($H2; Клиенты!B$2:B$14; Клиенты! I$2:I$14)).

В завершение осталось только определить сумму, подлежащую оплате. Для этого укажем в ячейке L2 формулу:

= ЕСЛИ(J2= «»; «»; J2-J2*K2).
В результате получаем окончательную таблицу заказов, представленную на рисунке рис.4.4.
Рассмотрим пример составления бланка заказа с использованием составленной базы данных. Назовем лист, следующий за листом Заказы, Бл.заказа.
При составлении бланка заказа предусматривается его автоматическое формирование и возможность печати бланка.
Поместим указатель ячейки на ячейку D3 и введем название бланка Заказ N. Номер заказа следует указать в ячейке Е3, при желании его можно подчеркнуть. Для этого в списке Линии рамки надо установить обрамление ячейки линией снизу. Не забывайте во время работы при необходимости изменять ширину столбцов. В ячейке F3 введем от и уменьшим ширину столбца. В ячейке G3 будет поставлена дата заказа, которую вставим с помощью формулы:

=ЕСЛИ($E$3= « »; « »;ПРОСМОТР($E$3;Заказ;Дата)).

Вид этой формулы аналогичен использовавшимся ранее. Подчеркнем вставляемое с помощью формулы значение, проведя нижнюю линию обрамления. В дальнейшем при составлении бланка все области, предназначенные для ввода переменного текста, следует подчеркивать, используя список Линии рамки панели инструментов Форматирование. Значения в строке 3 должны иметь полужирное начертание и шрифт размером в 14 пунктов.
Перейдем к оформлению второй строки бланка (строка 5 листа). В ячейку C5 введем текст Название фирмы-заказчика. При этом необходимо вводить текст таким образом, чтобы он заполнил ячейки C5 и D5. Для названия фирмы были отведены ячейки E5, F5, G5. Чтобы при заполнении заказа название фирмы вставлялось автоматически, поместим в ячейку E5 формулу:

=ЕСЛИ ($E$3= « »;« »;ПРОСМОТР($E$3;Заказ;Фирма2)).

Ранее диапазонам ячеек базы данных Заказы были присвоены имена. Подчеркнем название фирмы и расположим его по центру диапазона из трех ячеек. Для этого выделим ячейки E5, F5 и G5 и нажмем в панели инструментов Форматирование кнопку Центрировать по столбцам. В ячейку H5 введем слово Код, а в ячейку I5 поместим формулу:

=ЕСЛИ ($E$3= « »; « »;ПРОСМОТР($E$3;Заказ;Код2)).

Для оформления третьей строки бланка в ячейку C7 введем текст Наименование товара, а для ячеек E7, F7 и G7 применим подчеркивание и центрирование. Ячейка E7 должна содержать формулу:

=ЕСЛИ ($E$3= « »; « »; ПРОСМОТР ($E$3;Заказ;Товар2)).

В ячейку H7 введем № (символ номера), а в ячейке I7 укажем формулу:

=ЕСЛИ ($E$3= « »;« »;ПРОСМОТР($E$3;Заказ;Номер2))

и применим подчеркивание для помещаемого в ячейку I7 значения. Четвертая строка бланка должна содержать сведения о количестве и цене заказываемого товара. Следовательно, в ячейку C9 введем текст: Заказываемое количество. Для самого значения следует зарезервировать ячейку E9. Значение будет вставляться автоматически, если в ячейку E9 ввести формулу:

=ЕСЛИ ($E$3= « »; « »;ПРОСМОТР($E$3;Заказ;Количество)).

Так же как и для всех полей бланка, предназначенных для ввода переменной информации, проведем под этой ячейкой нижнюю линию обрамления. В ячейку F9 введем с клавиатуры: ед. по цене и выровняем введенный текст по центру столбцов F и G. Ячейка H9 должна содержать формулу:

=ЕСЛИ ($E$3= « »; « »;ПРОСМОТР($E$3;Заказ;Цена2)).

К этой ячейке следует применить подчеркивание и денежный стиль. В заключении над четвертой строкой бланка в ячейку I9 поместите текст за ед. В ячейку C11 введем текст: Общая стоимость заказа, а в ячейку E11 поместим формулу:

=ЕСЛИ ($E$3= « »; « »; ПРОСМОТР ($E$3; Заказ; Сумма))

и зададим для ячейки параметры форматирования: нижняя линия обрамления и денежный стиль. В ячейку F11 введем: Скидка (%), выделим ячейки F11, G11 и H11 и выполним щелчок на кнопке Центрировать по столбцам. В ячейку I11 поместим формулу:

=ЕСЛИ ($E$3= « »; « »; ПРОСМОТР ($E$3; Заказ; Скидка2))

и зададим для ячейки параметры форматирования: обрамление рамкой снизу и процентный стиль.
В последней 13 строке бланка введем в ячейку C13 текст: К оплате, а в ячейку D13 поместим формулу:

=ЕСЛИ ($E$3= « »; « »; ПРОСМОТР ($E$3; Заказ; Оплата))



Рис. 4.5

и вновь зададим для ячейки следующие параметры форматирования: обрамление рамкой снизу и денежный стиль. В заключение следует указать фамилию лица, оформлявшего заказ. В ячейке E13 введем: Оформил, выделим ячейки E13, F13 и зададим центрирование текста по столбцам. Затем выделим ячейки G13, H13 и I13, зададим для них центрирование текста по столбцам и обрамление рамкой снизу.
И, наконец, закончим форматирование, улучшим внешний вид документа и обеспечим его однородное оформление. В частности, текст и значения, расположенные в одной ячейке (а не в нескольких), должны быть центрированы. Кроме того, ячейки, предназначенные для ввода данных, можно выделить голубым цветом, а ячейки, заполняемые автоматически (с помощью формул) – желтым цветом. Однако при печати бланков на монохромном принтере это не всегда целесообразно. Затем установим ширину у столбцов B и J, равную 1,57 (ориентируясь по значению в поле левой части строки формул), выделим диапазон ячеек B2 – J14 и зададим обрамление всего диапазона (выберите последний вариант в «палитре» рамок панели инструментов Форматирование). В результате получаем таблицу, представленную на рис. 4.5.
Теперь осталось только проверить процедуру автоматического заполнения бланка. Поместим в ячейке E3 номер заказа – и останется только ввести свою фамилию перед печатью бланка (см. рис. 4.6.)
Если программа не совсем корректно справилась с поставленной задачей, проверьте, отсортированы ли номера заказов в списке в рабочем листе Заказы в возрастающей последовательности или измените ширину столбцов (при возникновении значения ошибки).



Рис. 4.6

Задание по лабораторной работе: во введенной базе данных произвести варьирование номенклатуры товаров, цены, количества заказов и составить бланк заказов.
Лабораторная работа № 5. Базы данных – 2

Цель работы: ознакомление с методами сортировки, фильтрации и упорядочения данных. (4 часа.)

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



Рис. 5.1

В этом окне можно задать несколько критериев (ключей) сортировки. Записи можно расположить в алфавитном и цифровом порядке или отсортировать по дате и т.д. Если сортируемый столбец содержит алфавитно-числовые данные, сначала выполняется сортировка по числам. Если первая строка списка содержит названия полей, она может быть исключена из сортировки: для этого следует установить в группе Идентифицировать поля по опцию Подписям (первая строка диапазона). Если же список не содержит заголовков столбцов, то надлежит активизировать опцию Обозначениям столбцов листа, чтобы первая строка диапазона была включена в сортируемый список. Список можно отсортировать не более чем по трем столбцам одновременно.
На рис. 5.2 показана таблица, отсортированная по полю Город.


Рис. 5.2

На Рис 5.3 приведены результаты сортировки по двум критериям «Город» и «Код». Причем, по критерию «Код» сортировка произведена в порядке убывания.



Рис. 5.3

Нажатием кнопки Параметры можно открыть диалоговое окно, в котором будет предоставлена возможность задать дополнительные параметры сортировки. Если при сортировке следует учитывать различие между строчными и прописными буквами, необходимо активизировать опцию Учитывать регистр.
По умолчанию Excel выполняет сортировку строк (активизирована опция Строки диапазона). Существует возможность сортировки столбцов в соответствии со значениями ячеек отдельной строки (или строк). Задать ее можно с помощью включения опции Столбцы диапазона команды Сортировать. После нажатия кнопки Ok и возвращения в диалоговое окно Сортировка диапазона в качестве критерия можно выбрать строки, по которым будет происходить сортировка столбцов.
В диалоговом окне Параметры сортировки можно определить также и пользовательский порядок сортировки. Его целесообразно устанавливать в том случае, когда сортировка по алфавиту не имеет смысла (например, сортировка названий месяцев). Однако задание пользовательского порядка сортировки возможно только в определенных пользователем (или встроенных) списках. Для задания пользовательской сортировки по первому ключу следует выбрать желаемый элемент из списка Порядок сортировки по первому ключу в диалоговом окне Параметры сортировки.
Применение фильтров. Поиск данных, соответствующих определенному критерию, возможен не только с помощью формы данных. В том случае, когда необходимо задать комплексные критерии поиска, можно воспользоваться функцией фильтрации. Применение фильтров дает возможность копирования и отдельной обработки выбранных с помощью фильтров записей. Фильтрация отличается от сортировки: при фильтрации записи не переупорядочиваются, а отображаются только те из них, которые соответствуют заданным критериям.
Из всех средств фильтрации данных наиболее простой и удобной является функция Автофильтр. После применения данной функции на экране отображаются только те записи, которые удовлетворяют заданным критериям отбора. При вызове этой функции некоторые операции выполняются автоматически. Для применения автофильтра к нашему списку воспользуемся командой Фильтр/Автофильтр меню Данные. После щелчка на имени этой команды в первой строке рядом с названием каждого поля появится кнопка со стрелкой (Рис. 5.4). С ее помощью можно открыть ниспадающий список, содержащий все встречающиеся в столбце значения полей.


Рис. 5.4
Если выбрать в списке нужное значение, то на экране будут видны только те записи, которые соответствуют заданному критерию отбора. На Рис. 5.5 приведен результат применения в качестве фильтра название города «Киев»



Рис. 5.5

Кроме значений полей, каждый из списков содержит еще три элемента: Все, Первые 10, Условие. Элемент Все следует использовать для восстановления отображения на экране всех записей после применения фильтра. Функция автоматического представления на экране первых десяти записей списка активизируется выбором элемента Первые 10. Элемент Условие используется для формирования более сложного критерия отбора, в котором можно применять условные операторы И и ИЛИ. Выберем из нашего списка киевских клиентов только тех, у кого скидка больше или равна 10%. Для того чтобы «отсеять» ненужные записи с помощью автофильтра выберем элемент Условие из ниспадающего списка в поле Скидка. На экране появится диалоговое окно пользовательского автофильтра. (Рис 5.6)



Рис. 5.6

Теперь можно задать критерии, которым должны соответствовать записи. В первом списке выберем оператор сравнения «больше или равно" и выберем в поле ввода значение «10%». После нажатия кнопки Ok в списке будут представлены только значения, удовлетворяющие заданному критерию отбора (Рис. 5.7).



Рис. 5.7

Для восстановления на экране всех записей следует воспользоваться командой Фильтр/Показать все.
Использование расширенного (усиленного) фильтра позволяет задавать более сложные критерии отбора. Эти критерии задаются непосредственно в рабочем листе. Для этого следует вставить в рабочий лист выше списка дополнительные строки (диапазон условий), которые и будут содержать критерии отбора. В пустую строку требуется ввести или скопировать строку заголовка списка. Затем в расположенные ниже строки надлежит ввести критерии отбора. Введению условий отбора для нескольких столбцов одновременно соответствует указание критериев в одной строке диапазона условий. Например, для диапазона условий Код > 3000 и Город Киев, будут отобраны строки, содержащие Киев в столбце Город и имеющие код, превышающий 3000 (Рис. 5.8).



Рис. 5.8

Чтобы указать разные критерии отбора для разных столбцов, следует вводить условия отбора в ячейки, расположенные в разных строках диапазона условий. При задании критериев можно использовать также операторы сравнения, однако, нельзя применять знак равенства для указания точного соответствия поля записи заданному критерию. В этом случае (при вводе знака равенства) значение критерия будет интерпретировано программой как формула, что приведет к появлению в ячейке значения ошибки. Между диапазоном условий и фильтруемым списком необходимо оставить не менее одной пустой строки.
После задания критериев можно активизировать действие расширенного фильтра. Для этого следует выбрать команду Фильтр/Расширенный фильтр меню Данные. На экране появится диалоговое окно Расширенный фильтр, в котором следует задать основные параметры расширенного фильтра. В частности, указать диапазон фильтруемых ячеек (поле Исходный диапазон) и ячеек с критериями (поле Диапазон критериев). Указывать адрес диапазона критериев следует с помощью выделения нужного диапазона после помещения курсора в поле ввода Диапазон критериев (Рис. 5.9).



Рис. 5.9


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



Рис. 5.10

При включении опции Скопировать результат в другое место выбранные после применения расширенного фильтра записи будут скопированы в диапазон ячеек, указываемый в поле ввода Поместить результат в диапазон. Если необходимо, чтобы при фильтрации не учитывались записи с одинаковыми данными, следует включить опцию Только уникальные записи. После нажатия кнопки Ok и запуска расширенного фильтра не отвечающие критериям записи будут скрыты.
Лабораторная работа № 6. Базы данных – 3

Цель работы: осуществление анализа данных с помощью консолидации данных и сводных таблиц.(4 часа.)

Консолидация данных. С помощью консолидации данных можно объединить данные из одной или более областей-источников и выводить их в таблице в области назначения.
Область-источник – это интервалы, содержащие данные, которые нужно консолидировать. Эти области-источники могут находится на одном рабочем листе, на нескольких рабочих листах или даже в разных рабочих книгах.
Область назначения – это интервал, который будет содержать консолидированные данные. Она может находится на том же рабочем листе, что и данные источников или на другом рабочем листе, или даже в другой рабочей книге.
В качестве области-источника будем использовать рабочую книгу Фирма, составленную ранее.




Рис. 6.1

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



Рис. 6.2

A1. Таблицы представлены на рис. 6.2 и 6.3 соответственно.



Рис. 6.3

Перейдем в четвертый рабочий лист и поместим указатель ячейки на крайнюю левую ячейку таблицы, которая будет содержать консолидированные данные – ячейку A3. Поскольку номер и название товара остаются неизменными и не должны быть подвергнуты операции сложения, то можно скопировать эту часть таблицы из рабочего листа, например, Киев. Для этого выделим в рабочем листе Киев диапазон ячеек A3 – B14, выберем команду Копировать меню Правка и перейдем в четвертый рабочий лист (присвоим ему имя Итог), поместим указатель ячейки на ячейку A3 и выберем команду Вставка меню Правка. Скопированный фрагмент будет вставлен в новом месте. Для того, чтобы приступить к операции консолидации данных поместим указатель ячейки на ячейку C3 и выберем в меню Данные команду Консолидация. На экране появится диалоговое окно, представленное на рис. 6.4.



Рис 6.4

В списке Функция следует выбрать операцию, которая будет выполняться над консолидированными данными. Поскольку предлагаемая программой операция сложения (элемент Сумма) подходит для нашей цели, перейдем к полю ввода Ссылка. Именно в нем следует указать диапазоны ячеек, данные их которых должны быть подвергнуты процессу консолидации. Диапазон ячеек проще всего можно указать, используя мышь. Поместим курсор ввода в поле Ссылка и выполним щелчок мышью на ярлычке листа Киев, затем выделим диапазон ячеек D3:F14 и нажмем в диалоговом окне Консолидация кнопку Добавить. Указанный диапазон ячеек тотчас же будет представлен в поле Список диапазонов. Нажатием кнопки Добавить можно включить в область консолидации, нажатием кнопки Удалить ненужный диапазон может быть из этой области удален (перед нажатием кнопки ненужный диапазон следует выделить в поле Список диапазонов).
После включения в область консолидации первого диапазона ячеек можно приступить к выполнению аналогичной операции над другими диапазонами. С помощью щелчка мышью на ярлычке перейдем в рабочий лист с данными для следующего филиала. Excel автоматически выделит тот же диапазон ячеек, что и в предыдущем листе. Нажмем кнопку Добавить и аналогичным образом укажем в поле Список диапазонов диапазоны других рабочих листов файла, данные из которых должны быть консолидированы.



Рис. 6.5

Если верхняя строка и/или левый столбец содержат заголовки столбцов или строк, которые необходимо скопировать в итоговую таблицу, следует включить соответствующие опции в группе Использовать метки. Поскольку в нашем примере верхняя строка содержит заголовки столбцов, то следует активизировать опцию В верхней строке. Если между данными консолидированной таблицы и исходными данными должна быть установлена динамическая связь, то следует активизировать опцию Создать связи с исходными данными. Вследствие активизации этой опции при изменении данных в исходном диапазоне будут изменяться значения и в итоговой таблице. Кнопка Обзор используется для выбора файла, который содержит консолидируемые данные. После включения всех необходимых диапазонов, а также задания желаемых параметров консолидации нажимается кнопка ОК для начала выполнения операции. Результат выполнения операции представлен на рис. 6.5.
В ячейку A1 введем название таблицы Итоговые данные. Для более наглядного представления данных в столбце F приведем значения долей отдельных товаров в общем объеме продаж. Для этого зададим в ячейке F9 формулу:

= E9/$E$48

и скопируем ее в остальные строки столбца F (вплоть до ячейки F48) предварительно сформатированного процентным стилем.



Рис. 6.7

Необычные адреса ячеек консолидированной таблицы объясняются тем, что при консолидации данных программа записывает в итоговой таблице каждый элемент и автоматически создает структуру документа. Структурирование (создание структуры) документа позволяет добиться представления на экране только необходимой информации и скрыть ненужные детали. Слева от таблицы представлены символы структуры. Цифрами обозначены уровни структуры (в нашем примере – 1 и 2). Нажатие кнопки со знаком плюс позволит расшифровать данные высшего уровня структуры. Этого же можно добиться путем исполнения команды Структура/Показать детали меню Данные. На рис. 6.6 показана расшифровка структуры для строк 7, 11 и 15. Скрыть детали можно посредством щелчка на кнопке с изображением минуса или путем выбора команды Структура/Скрыть детали меню Данные.
Применение функции консолидации имеет смысл при подведении итогов по нескольким структурным подразделениям, данные о которых хранятся в отдельных файлах. В месте с этим эта функция значительно уступает сводным таблицам, особенно по части возможностей представления данных.
Анализ данных с помощью сводной таблицы. Сводными называются вспомогательные таблицы, которые содержат часть данных анализируемой таблицы, отобранных так, чтобы зависимости между ними отображались наилучшим образом.
Сводные таблицы создаются из отдельных списков или базы данных. Таблицы, на основе которых строится сводная таблица, должны содержать заголовки строк или столбцов, которые необходимы для создания полей данных.
На рабочем листе сводная таблица конструируется с помощью команды Данные, Сводная таблица (Data, Pivot Table and Pivot Chart Report).
Свободную таблицу будем создавать для отчета по продажам компьютеров сети из трех магазинов (рис. 6.8).



Рис. 6.8. Отчет о продаже компьютеров сети из трех магазинов

Процесс создания сводной таблицы состоит из нескольких шагов.



Рис. 6.9
Шаг 1.
Выберете команду Данные, Сводная таблица (Data, Pivot Table and Pivot Chart Report). Появится первое диалоговое окно мастера сводных таблиц (рис. 6.9). Выбор переключателей, расположенных под заголовком Создать таблицу на основе данных, находящихся: позволяет установить источник данных для сводной таблицы.
Выберем вариант в списке или базе данных Microsoft Excel, т.к. сводная таблица будет создаваться на основе одного списка активного рабочего листа.
Шаг 2.
На экране появится второе диалоговое окно мастера сводных таблиц. В


Рис. 6.10

поле Диапазон введите ссылку, например А1:Е16, на диапазон, по которому будет строится сводная таблица (рис 6.10). Нажмите кнопку Далее >.




Рис. 6.11
На экране появится третье диалоговое окно мастера сводных таблиц (рис. 6.11), в котором предлагается сделать выбор места, где будет расположена сводная таблица. Выберем новый лист.
Шаг 3.
На новом рабочем листе появляется форма для создания структуры сводной таблицы (рис. 6.12).



Рис. 6.12


Рис. 6.13

Выберете поля, которые будут образовывать строки СТ и перетащите кнопки, соответствующие этим полям в область Поля строк. В данном случае строки СТ будут соответствовать магазинам. Поэтому перетащим кнопку Магазин в область Поля строк.
Выберете поля, которые будут образовывать столбцы СТ и перетащите кнопки, соответствующие этим полям в область Поля столбцов. В данном случае столбцы СТ будут соответствовать месяцам. Поэтому перетащим кнопку Месяц в область Поля столбцов.
Выберете поле, по которому будут подводится итоги СТ и перетащите кнопку, соответствующую этому полю в область. В данном случае перетащим кнопку Стоимость в область Данные. В результате получим таблицу, приведенную на рис. 6.13.
Лабораторная работа № 7. Финансовые функции Excel – 1

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

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

= ИМЯФУНКЦИИ(Аргументы).

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

Амортизационные отчисления – это отчисления, предназначенные для возмещения износа имущества. Всякое имущество имеет определенную цену (стоимость) в начале периода амортизации – цену приобретения, начальную стоимость, которая должна быть указана в функциях, используемых при расчете амортизационных отчислений, в аргументе Стоимость.
Приобретенное имущество амортизируется на протяжении определенного периода, который называется сроком амортизации. В программе он должен быть указан в аргументе, для обозначения которого используются названия Время_эксплуатации, Жизнь, Время_амортизации. Стоимость в конце срока амортизации – это остаточная (ликвидационная) стоимость. Хотя эта стоимость и не является ликвидной, ее следует указывать в аргументе Ликвидная_стоимость, Остаточная_стоимость, Ост_стоимость. Расчет амортизационных отчислений производится для определенного периода, задаваемого аргументом Период.
Рассмотрим пример составления плана амортизации с использованием функций АМР и ДОБ. Более подробно финансовые функции рассмотрены в учебном пособии [1].
Аргументы функций имеют следующий смысл:
Стоимость – начальная стоимость имущества.
Остаточная_стоимость – остаточная стоимость в конце периода амортизации (иногда называется остаточной стоимостью имущества).
Время_эксплуатации – количество периодов, за которые собственность амортизируется (иногда называется периодом амортизации).
Остаток – остаточная стоимость в конце амортизации (иногда называется ликвидной стоимостью имущества).
Нач_стоимость – начальная стоимость имущества.
Период – период, для которого требуется вычислить амортизацию. Период должен быть измерен в тех же единицах, что и время_эксплуатации.
Месяц – количество месяцев в первом году. Если аргумент месяц опущен, то предполагается, что он равен 12.

АМР

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

Синтаксис:

АМР(стоимость; остаток; период)

Пример.

Предположим Вы купили за 30 000 руб. грузовик, который имеет срок эксплуатации 10 лет, после чего оценивается в 7 500 руб. Снижение стоимости для каждого года эксплуатации составит:
АМР(30000; 7500; 10) равняется 2 250 руб.

ДОБ
Возвращает амортизацию имущества на заданный период, используя метод постоянного учета амортизации (геометрически-дегрессивный метод).

Синтаксис:

ДОБ(нач_стоимость; ост_стоимость; время_эксплуатации; период; месяц)

Замечания.

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

(нач_стоимость – суммарная амортизация за предшествующие периоды) * ставка

где: ставка = 1 – ((ост_стоимость/нач_стоимость) ^ (1 / время_эксплуатации)), округленное до трех десятичных знаков после запятой.
Особыми случаями являются амортизация за первый и последний периоды. Для первого периода ДОБ использует такую формулу:

нач_стоимость * ставка * месяц / 12.

Для последнего периода ДОБ использует такую формулу:

((нач_стоимость - суммарная амортизация за предшествующие периоды) * ставка * (12 - месяц)) / 12.

Примеры.

Предположим, что предприятие приобрело новое оборудование. Оборудование стоит 1000000 руб. и имеет срок эксплуатации шесть лет. Остаточная стоимость оборудования 100000 руб. Следующие примеры показывают величину амортизации за время эксплуатации оборудования. Результаты округлены до целых.
ДОБ(1000000;100000;6;1;7) равняется 186 083 руб.
ДОБ(1000000;100000;6;2;7) равняется 259 639 руб.
ДОБ(1000000;100000;6;3;7) равняется 176 814 руб.
ДОБ(1000000;100000;6;4;7) равняется 120 411 руб.
ДОБ(1000000;100000;6;5;7) равняется 82 000 руб.
ДОБ(1000000;100000;6;6;7) равняется 55 842 руб.
ДОБ(1000000;100000;6;7;7) равняется 15 845 руб.

Допустим, что предприятие приобрело оборудование стоимостью 120000 руб. Срок амортизации оборудования 15 лет, после чего его ликвидационная стоимость будет составлять 1000 руб.
Введите в верхней части рабочего листа название таблицы – Начисление амортизации – и следующие текстовые метки строк в ячейки A3 – A6:
Начальная стоимость
Срок амортизации
Максимальная норма списания
Остаточная стоимость после.
На следующем этапе уменьшите ширину столбца B до 2,00, после чего задайте в ячейке B6 формулу:

=$D$4

и в заключение введите в ячейке C6 слово лет. Вышеописанная операция поз-


Рис. 7.1

воляет «автоматизировать» вставку значения количества лет после указания цифры в ячейке D4. Максимальная норма списания для оборудования, срок амортизации которого составляет 15 лет, равняется 20%. Поэтому укажите в ячейке D5 значение 0,2 и сформатируйте ячейку процентным стилем. Присвойте рабочему листу имя Данные. После ввода данных задачи получаем таблицу, представленную на рис. 7.1. Далее займемся расчетом амортизационных отчислений с помощью метода линейного списания и геометрически-дегрес- сивного метода, а также попробуем определить оптимальный план начисления износа.
Второму рабочему листу присвоим имя Расчет. В столбце B будет находится год, для которого следует определить амортизацию, поэтому в ячейке B5 укажите слово Год, в первых двух ячейках введите значения 1 и 2 для двух первых лет, выделите обе ячейки, поместите курсор мыши на маркере заполнения и используйте функцию автозаполнения для ввода остальных значений (до 15). В ячейках D5, F5, H5, J5 введите текстовые метки столбцов:
Балансовая стоимость
Линейное списание
Дегрессивный метод
Износ
Столбцы C, E, G и I будут служить своеобразным «декоративным» оформлением, для этого уменьшите их ширину до 1,43, ориентируясь по содержимому поля в левой части строки формул.
Перейдем к заданию балансовой стоимости оборудования. Значение в ячейке D6 соответствует первоначальной стоимости оборудования, поэтому в этой ячейке можно указать формулу:

=Данные!$D$3.

Тем самым значение из ячейки D3 рабочего листа Данные будет представлено в ячейке D6. В дальнейшем, при изменении исходных данных примера, необходимые изменения следует выполнять только в листе Данные.
Далее, поместим указатель ячейки на ячейку F6 и активизируем Мастера функций (команда Функция меню Вставка или Мастер функций панели инструментов Стандартная). Выберете функцию АМР и нажмите кнопку Далее для перехода в диалоговое окно задания аргументов.


Рис. 7.2

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

=АМР(D6;Данные!$D$6;Данные!$D$4-B6+1).

В качестве первого аргумента следует указать значение для соответствующего года из столбца Балансовая стоимость, остаточная стоимость будет взята из листа Данные. Срок амортизации оборудования, уменьшающийся каждый раз на один год по мере начисления износа, следует записать в виде Данные!$D$4-B6+1.

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

=ЕСЛИ(ДОБ(D6;Данные!$D$6;Данные!$D$4-B6+1;1)/D6 < Данные! $D$5;ДОБ(D6;Данные!$D$6;Данные!$D$4-B6+1;1);D6*Данные! $D$5).

В логической функции ЕСЛИ необходимо проверить на истинность выражение: превышает ли результат вычисления функции ДОБ 20% балансовой стоимости или нет. Для образования вложенной функции следует нажать кнопку с литерой fx рядом с полем ввода аргумента. Если выражение истинно, то в ячейке должен быть представлен результат вычисления функции ДОБ. В противном случае следует указать максимально возможное значение, которое равняется произведению балансовой стоимости и максимальной нормы списания. Первые три аргумента соответствуют трем аргументам функции АМР.
Таким образом, функции для расчета амортизации с помощью метода линейного списания и геометрически-дегрессивного метода введены. Теперь следует в столбце Износ представить большую величину амортизации. Для этого следует воспользоваться логической функцией ЕСЛИ и вставить ее в ячейку J6 в виде:

=ЕСЛИ(F6>H6;F6;H6).

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

=D6-J6.

Итак, ввод необходимых для вычислений формул завершен. Осталось только с помощью функции автоматического заполнения скопировать формулы в расположенные ниже ячейки. Для этого помечаем ячейки D7 – J7, помещаем мышь в квадратик копирования и протягиваем мышь до 20-ой строки. В ячейке D21 укажем остаточную стоимость оборудования:

=Данные!$D$6.

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


Рис. 7.3

Для этого выделим ячейки B5 – B20, F5 – F20, H5 – H20, J5 – J20 и активизируем Мастер диаграмм посредством выбора команды Диаграмма/На новом листе меню Вставка. Программа автоматически вставит в рабочую книгу новый лист под названием Диаграмма 1 (лист диаграмм), а на экране появится первое диалоговое окно Мастера диаграмм. Подтвердите предлагаемый программой выбор ячеек содержащих данные для построения диаграммы (это ячейки, которые были выбраны нами), нажатием кнопки Далее.
Во втором диалоговом окне Мастера диаграмм выберете тип диаграммы График и нажмите кнопку Далее.
В следующем диалоговом окне выбора автоформатов выберете автоформат под номером 2 и снова нажмите кнопку Далее для перехода в следующее диалоговое окно. В этом диалоговом окне представляется возможность получить примерное представление о том, как будет выглядеть будущая диаграмма, а также изменить ориентацию данных. Подтвердите установленные в диалоговом окне данные нажатием кнопки Далее.
В последнем диалоговом окне введите название диаграммы – Расчет амортизации, а также названия по оси категорий (X) – Год, по оси (Y) – Величина амортизации. Нажатием кнопки Готово завершается процесс построения диаграммы (рис. 7.3).
Рассмотрим пример расчета рентных платежей. Под рентным платежом понимают регулярные платежи одинакового размера. При использовании функций денежных потоков в качестве аргументов следует указывать, как правило, массивы данных, в которых содержатся сведения о денежных потоках. Выплаты при этом должны быть обозначены как отрицательные значения, а поступления – как положительные значения.
Аргументы ниже приведенных функций имеют следующий смысл.
Бз – будущая стоимость или баланс наличности, который нужно достичь после последней выплаты. Если бз опущено, оно полагается равным 0 (будущая стоимость займа, например, равна 0). Например, если Вы хотите накопить 50000 руб. для оплаты специального проекта в течение 18 лет, то 50 000 руб. это и есть будущая стоимость. Вы можете сделать предположение о сохранении заданной процентной ставки и определить, сколько нужно откладывать каждый месяц.
Значение1, значение2, ... – от 1 до 29 аргументов, представляющих расходы и доходы. Значение1, значение2, ... должны быть равномерно распределены по времени и осуществляться в конце каждого периода.
Кпер – общее число периодов выплат годовой ренты. Например, если Вы получили ссуду на 4 года под автомобиль и делаете ежемесячные платежи, то Ваша ссуда имеет 4*12 (или 48) периодов. Вы должны ввести число 48 в формулу в качестве значения аргумента кпер.
Нз – текущая стоимость, или общая сумма всех будущих платежей с настоящего момента. Если аргумент нз опущен, то он полагается равным 0.
Ставка – процентная ставка за период. Например, если Вы получили ссуду под автомобиль под 10% годовых и делаете ежемесячные выплаты, то процентная ставка за месяц составит 10%/12, или 0,83%. В качестве значения аргумента ставка нужно ввести в формулу 10%/12 или 0,83% или 0,0083.
Тип – число 0 или 1, обозначающее, когда должна производится выплата. Если аргумент тип опущен, то он полагается равным 0.

Тип Когда нужно платить

0 В конце периода
1 В начале периода

НПЗ

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

Синтаксис:

НПЗ(ставка; значение1; значение2; ...)

НПЗ использует порядок аргументов значение1, значение2, ... для определения порядка поступлений и платежей. Убедитесь в том, что Ваши платежи и поступления введены в правильном порядке.
Аргументы, которые являются числами, пустыми ячейками, логическими значениями или текстовыми представлениями чисел учитываются; аргументы, которые являются значениями ошибки или текстами, которые не могут быть преобразованы в числа, игнорируются.
Если аргумент является массивом или ссылкой, то учитываются только числа. Пустые ячейки, логические значения, тексты или значения ошибок в массиве или ссылке игнорируются.

Замечания.

Считается, что инвестиция, значение которой вычисляет функция НПЗ, начинается за один период до даты денежного взноса значение1 и заканчивается с последним денежным взносом в списке. Вычисления функции НПЗ базируются на будущих денежных взносах. Если первый денежный взнос приходится на начало первого периода, то первое значение следует добавить к результату функции НПЗ, но не включать в список аргументов. Для получения более подробной информации, см. примеры ниже.
Если n – это количество денежных взносов в списке значений, то формула для функции НПЗ имеет вид:

НПЗ =13 EMBED Equation.2 1415.

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

Рассмотрим инвестицию, при которой Вы выплачиваете 10 000 рублей через год после сегодняшнего дня и получаете годовые доходы 3 000 руб., 4200 руб., 6 800 руб. в последующие три года. Предположим, что учетная ставка составляет 10 процентов, в таком случае чистый текущий объем инвестиции составит:
НПЗ(10%; –10000; 3000; 4200; 6800) равняется 1188,44 руб.
В предыдущем примере начальный платеж 10 000 руб. был включен как одно из значений, поскольку выплата производилась в конце первого периода.
Рассмотрим инвестиции, которые начинаются в начале первого периода. Допустим, Вы интересуетесь покупкой обувного магазина. Стоимость предприятия – 40 000 руб. и Вы ожидаете получить следующие доходы за первые пять лет: 8 000 руб., 9 200 руб., 10 000 руб., 12 000 руб. и 14 500 руб. Годовая учетная ставка равна 8%. Она может представлять степень инфляции или учетную ставку конкурирующих инвестиций.
Если стоимость и доходы от обувного магазина введены в ячейки от B1 до B6 соответственно, то чистый текущий объем инвестиции в обувной магазин составит:
НПЗ(8%; B2:B6)+B1, что равняется 1922,06.
В предыдущем примере начальная цена 40 000 руб. не включалась в список значений, поскольку выплата пришлась на начало периода.
Предположим, что на шестой год Ваш магазин потерпел крах, и Вы предполагаете убыток в 9 000 руб. для шестого года. Чистый текущий объем инвестиции в обувной магазин после шести лет составит:
НПЗ(8%; B2:B6; -9000)+B1, что равняется – 3749,47.
Рассмотрим пример использования функции НПЗ.
Предположим, что стоит задача наладить производство товаров, на которые имеется спрос. При этом предстоит выбрать один из трех альтернативных проектов. Первоначальные инвестиции при реализации первого проекта составляют 500000 руб., второго – 400000 руб. И третьего – 700000 руб.; при этом сроки службы оборудования (период инвестиции) также различаются. Все эти значения будут введены в таблицу с исходными данными. Задача заключается в определении наиболее выгодного инвестиционного проекта.
Решим эту задачу с помощью метода чистой текущей стоимости, который является одним из методов так называемого динамического расчета рентабельности инвестиции. Сущность метода заключается в том, что все будущие поступления и выплаты, происходящие в течение периода инвестиций, дисконтируются (сводятся к настоящему значению). При этом предполагается, что денежные потоки имеют место в конце каждого периода (то есть фактически учитывается сальдо денежных потоков за период). В качестве процентной ставки используется ставка финансирования (если для финансирования проекта используются заемные средства) или ставка альтернативной возможности вложения капитала (при использовании для финансирования проекта собственных средств). Если чистая текущая стоимость инвестиции больше нуля, то инвестиция рентабельна. В этом случае нас интересует только абсолютная величина чистой текущей стоимости инвестиции (поскольку будущие платежи дисконтированы и предполагается, что возможности финансирования при данной процентной ставке не ограничены, относительная рентабельность инвестиции не имеет значения). То есть, чем больше значение чистой текущей стоимости инвестиции, тем лучше.
Начнем рассмотрение примера с создания таблицы для помещения исходных данных. Откроем новую рабочую книгу и введем в первом рабочем листе в ячейке A1 название таблицы – Выбор проекта. В ней будут представлены не только исходные данные, но и результаты расчетов, на основании которых будет возможно принять решение о выгодности того или иного проекта.
По строкам представим исходные данные и итоги для отдельных проектов: в ячейках B5, B7 и B9 следует указать текстовые метки строк Проект 1, Проект 2 и Проект 3. В строке 3 будут отображены метки столбцов, поэтому введем в ячейки D3, E3, F3, H3 и I3 соответственно:
Инвестиция
Срок (лет)
Ставка (%)
Текущая стоимость
Чистая стоимость


Рис. 7.4

В столбце D укажите размер первоначальной инвестиции. Введите значения 500000, 400000 и 700000 для трех проектов и отформатируйте ячейки денежным стилем. Дополнительные инвестиции будут учитываться в балансе денежных потоков. Столбец Срок (лет) должен содержать данные о периоде инвестиции – 5, 5 и 8 лет соответственно. Зададим в столбце Ставка (%) для первых двух проектов процентную ставку 13%, а для третьего – 11%. Отформатируйте ячейки процентным стилем. Основные исходные данные введены, присвоим первому рабочему листу имя Выбор проекта. В результате получаем таблицу, приведенную на рис. 7.4.
Присвоим второму рабочему листу имя Проект 1. Одноименный заголовок таблицы введите и в ячейку A1. В строке 3 укажите в ячейках B3, D3, F3 и E3 текстовые метки столбцов:
Год
Баланс денежных потоков
Текущая стоимость
Текущая стоимость (Итог).
Текущую стоимость в рабочих листах втором и четвертом будем определять отдельно от первого рабочего листа. Если в первом рабочем листе для расчета текущей стоимости будем использовать функцию НПЗ, то в других листах текущую стоимость денежных потоков для каждого года рассчитаем с помощью формулы, а затем сложим полученные значения.



Рис. 7.5

В ячейках B4 – B8 укажите значения от 1 до 5. Далее введите предполагаемые значения для денежных потоков в ячейки D4 – D8. Следует помнить, что отрицательное сальдо денежных потоков (выплаты в течение периода превышают поступления) следует указывать со знаком минус.
Теперь определим текущую стоимость денежных потоков для каждого года. Для этого в ячейку E4 введите формулу:

=D4*(1 + Выбор проекта!$F$5)^(–B4)

и скопируйте ее в ячейки E5 – E8. В столбце F значение текущей стоимости инвестиции будет представлено нарастающим итогом. В ячейке F4 с помощью формулы

= E4

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

= F4 + E5

и скопируйте ее в ячейки F6 – F8. В ячейке F8 представлено значение текущей стоимости инвестиций, которое должно совпасть со значением в ячейке H5 в рабочем листе Выбор проекта, рассчитанном с помощью функции НПЗ. В результате все этих действий получаем таблицу, представленную на рис. 7.5.



Рис. 7.6



Рис. 7.7

Перейдем в третий рабочий лист и назовем его Проект 2. Чтобы не повторять всех аналогичных действий, скопируем содержимое рабочего листа Проект 1 и выполним необходимую редакцию содержимого. Отредактируем название таблицы (ячейка A1) и формулу в ячейке E4 (ту ее часть, которая касается процентной ставки (Выбор проекта!$F$7)). В результате получаем таблицу, представленную на рис. 7.6.
Те же самые операции следует выполнить для четвертого рабочего листа, который называется Проект 3. Здесь только необходимо добавить данные для трех дополнительных лет (см. рис. 7.7). Следует не забыть указать правильный адрес ячейки для значения процентной ставки (Выбор проекта!$F$9).
Перейдем к рабочему листу Выбор проекта для завершения расчетов и определения наиболее выгодного проекта. В этом рабочем листе текущее значение инвестиции будем определять с помощью функции НПЗ. Поместим указатель ячейки на ячейку H5 и активизируем Мастер функций, выберем функцию НПЗ и зададим ее аргументы следующим образом:

= НПЗ (F5; Проект1!D4:D8).

В ячейках H7 и H9 укажите формулы:

= НПЗ (F7; Проект1!D4:D8)
и

= НПЗ (F9; Проект1!D4:D11).



Рис. 7.8

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

= H5 – D5

и скопируем ее в ячейки I7 и I9. Результаты вычислений приведены в таблице на рис. 7.8.
Анализ столбца Чистая стоимость показывает, что чистая стоимость третьего проекта больше, чем у двух оставшихся. Следовательно, наиболее перспективным является реализация третьего проекта.
В качестве примера, в котором будет использоваться финансовая функция ППЛАТ, рассмотрим задачу выбора банка, предоставляющего кредит, и составления плана погашения кредита.
ППЛАТ – возвращает величину выплаты за один период годовой ренты на основе постоянных выплат и постоянной процентной ставки.

Синтаксис:

ППЛАТ(ставка; кпер; нз; бз; тип)

Более подробное описание аргументов функции ППЛАТ см. в описании функции ПЗ.

Замечания.

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

Примеры.

Следующая макроформула возвращает ежемесячные выплаты по займу в 10000 руб. и годовой процентной ставке 8%, которые можно выплачивать в течение 10 месяцев:
ППЛАТ(8%/12; 10; 10000) равняется – 1037,03 руб.
Для того же займа, если выплаты должны делаться в начале периода, то выплата составит:
ППЛАТ(8%/12; 10; 10000; 0; 1) равняется – 1030,16 руб.
Следующая макроформула возвращает сумму, которую необходимо выплачивать Вам каждый месяц, если Вы дали взаймы 5000 руб. под 12% годовых и хотите получить назад деньги за пять месяцев:
ППЛАТ(12%/12; 5; -5000) равняется 1030,20 руб.
Предположим, что Вы хотите накопить 50000 руб. за 18 лет, накапливая постоянную сумму каждый месяц. Если предположить, что Вы сможете обеспечить 6% годовых на Ваши накопления, то можно использовать функцию ППЛАТ, чтобы определить, сколько нужно откладывать каждый месяц:
ППЛАТ(6%/12; 18*12; 0; 50000) равняется – 129,08 руб.
Если Вы платите 129,08 руб. с 6% накоплением в течение 18 лет, Вы получите 50000 руб.
Предположим, что был взят кредит размером в 200000 р. сроком на пять лет, погашать который (основной долг и проценты) предполагается равномерными платежами в конце каждого года. Запросы на финансирование были направлены в три банка, из которых пришли ответы с соответствующими условиями. Необходимо сравнить условия, определив эффективную процентную ставку, а также составить план погашения кредитов по годам.
В ячейках B7, B9, B11 рабочей книги вводится соответственно Банк 1, Банк 2 и Банк 3. В строке 5 будут представлены текстовые метки столбцов. Введите в ячейки C5, D5, E5, F5, G5, I5, J5, K5, L5 следующие текстовые метки столбцов: Объем кредита, Выдача (%), Плата за оформление, Ставка (%), Срок (лет), Получено, Дизажио, Выплата (год), Выплата/Получено.
В первом столбце будет представлен объем кредита. Укажем во всех трех случаях объем кредита в 200000 р. Однако представление кредита еще не означает, что будет получена вся сумма. Кое-что (дизажио) необходимо оставить в банке, как плату за обработку и за повышенный риск, на который идет банк. Укажем в ячейках D7, D9 и D11 значения 0,95, 0,96 и 0,965 и сформатируем ячейки процентным стилем. Кроме того, следует оплатить издержки, возникающие при оформлении кредита, которые в нашем примере составляют 300, 250 и 350 р. В столбце Ставка следует ввести значения процентных ставок, по которым банки готовы предоставить кредит: 0,12, 0,135 и 0,142, – а затем сформатировать ячейки столбца процентным стилем. Столбец Срок должен содержать значения количества лет, на которые предоставлен кредит – 5 лет. Этим завершается ввод исходных данных в таблицу. В результате получаем таблицу, приведенную на рис. 7.6.
Теперь необходимо произвести расчеты.
Сначала определим в столбце J7 размер дизажио для первого варианта с помощью формулы:

=C7*(1 – D7).

Для определения полученной суммы следует вычесть из объема кредита дизажио и плату за оформление, поэтому в ячейке I7 следует задать формулу:

= C7 – J7 – E7.



Рис. 7.9

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

=ППЛАТ(F7; G7; -C7).

Остается определить в ячейке L7 (предварительно сформатированной в процентном стиле) отношение годовой выплаты к полученной сумме с помощью формулы:

= K7/I7.

Скопируем формулы из I7, J7, K7, L7 в расположенные ниже ячейки, в которых будут произведены вычисления для предложений кредита второго и третьего банков. Все результаты имеются на рис. 7.9. Присвоим этому рабочему листу имя Кредит.
Предположим, что было принято решение остановится на кредите, предложенном первым банком (более низкий годовой процент). В пользу первого банка говорит также более выгодное отношение Выплата/Получено (столбец L). Посмотрим во втором рабочем листе, как будет протекать погашение кредита.
Перейдем во второй рабочий лист и присвоим ему имя Погашение. Вводим в ячейки B4,C4, D4 и E4 следующие текстовые метки столбцов: Год, Погашение долга, Проценты и Остаток. В первом столбе в ячейках B6 – B10 будут представлены значения периодов выплаты – от 1 до 5. Ячейки C6 – C10 должны содержать суммы – части годового платежа, которые будут идти на погашение основного долга, ячейки D6 – D10 – значения выплачиваемых процентов, а ячейки E6 – E10 – значения остатка основного долга.
Определим размер выплачиваемых в первый год процентов. Поместим указатель ячейки на ячейку D6 и зададим в ней формулу:

=Кредит!$C$7*Кредит!$F$7.



Рис 7.10

Часть годового платежа, которая в первый год уйдет на погашение основного долга, составит (ячейка C6):

=Кредит!$K$7–D6.

Остаток долга в конце первого года рассчитаем по формуле (ячейка E6):
= Кредит!$C$7-$C$6.

Выплачиваемые по долгу проценты для второго года определите в ячейке D7 с помощью формулы:

=E6*Кредит!$F$7.

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

=E6-C7

и скопировать эту формулу в ячейки E8 – E10. В итоге получаем таблицу, приведенную на рис. 7.10.

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

Лабораторная работа № 8. Финансовые функции Excel – 2

Цель работы: ознакомление с финансовыми функциями, связанными с ценными бумагами (4 часа)

Многие функции могут использоваться для вычисления при операциях с ценными бумагами. Причем речь идет о двух видах ценных бумаг: с твердым процентом и беспроцентных. Твердопроцентные ценные бумаги приносят одинаковый доход на протяжении определенного периода времени. Беспроцентные ценные бумаги эмитируются со скидкой (дисконтом), то есть курс при эмиссии ценных бумаг этого вида меньше номинальной стоимости, по которой происходит выкуп ценных бумаг в конце срока обращения. Более подробно о финансовых функциях, предназначенных для работы с ценными бумагами, см. в учебном пособии [1].
Все ниже рассматриваемые функции становятся доступными после установки дополнения Пакет Анализа.
Аргументы функций имеют следующий смысл.
Дата_вступления_в_силу – дата вступления в силу ценных бумаг, выраженная как дата в числовом формате.
Дата_соглашения – дата соглашения для ценных бумаг, выраженная как дата в числовом формате.
Погашение – цена при погашении за 100 руб. нарицательной стоимости ценных бумаг.
Ставка – годовая процентная ставка по купонам для ценных бумаг.
Цена – цена ценных бумаг на 100 руб. нарицательной стоимости.
Базис – это используемый способ вычисления дня.

Базис Способ вычисления дня

0 или опущено US (NASD) 30/360
1 Фактический/фактический
2 Фактический/360
3 Фактический/365
4 Европейский 30/360

Ко всем функциям применимы следующие замечания:
Все аргументы усекаются до целых.
Если любой из аргументов не число, то рассматриваемая функция возвращает значение ошибки #ЗНАЧ!.
Если дата_соглашения или дата_вступления_в_силу не являются допустимой датой в числовом формате, то функция возвращает значение ошибки #ЧИСЛО!.
Если базис < 0 или если базис > 4, то функция возвращает значение ошибки #ЧИСЛО!.
Если дата_соглашения >= дата_вступления_в_силу, то функция возвращает значение ошибки #ЧИСЛО!.

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

Синтаксис:

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

Замечания.
Дата_соглашения, дата_вступления_в_силу, частота и базис усекаются до целых.

Если только один или менее периодов купона укладываются до даты погашения, то функция ДОХОД вычисляется по формуле, приведенной в справочном материале пакета Excel.
Если до погашения укладывается более одного периода купона, то функция ДОХОД вычисляется итерационным методом (не более 100 итераций). Используется метод Ньютона на основе формулы для функции ЦЕНА. Доход меняется до тех пор, пока вычисляемая цена для данного дохода не станет близкой к значению аргумента цена.

Пример.

Облигации выпущены на следующих условиях:
Дата соглашения 15 февраля 1991 года.
Дата вступления в силу 15 ноября 1999 года.
Купон 5,75%.
Цена 95,04287 руб.
Цена при погашении 100 руб.
Полугодовая частота.
Базис 30/360.
Доход от облигаций (в Системе дат 1900) составит:
ДОХОД(33284;36479;0,0575;95,04287;100;2;0) равняется 0,065 или 6,5%

ДОХОДСКИДКА – возвращает годовой доход по ценным бумагам, на которые сделана скидка.

Синтаксис:

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

Пример.

Облигации выпущены на следующих условиях:
Дата соглашения 15 февраля 1993 года.
Дата вступления в силу 1 марта 1993 года.
Цена 99,795 руб.
Цена при погашении 100 руб.
Базис фактический/360.
Доход по облигации (в Системе дат 1900) составит:
ДОХОДСКИДКА(34015;34029;99,795;100;2) равняется 5,2823%.

Рассмотрим пример временного размещения свободных денег таким образом, чтобы они приносили максимальный доход.
Попытаемся определить, каким ценным бумагам отдать предпочтение с точки зрения годовой доходности до истечения срока обращения, который наступает в момент решения задачи. При этом будут созданы таблицы для расчета доходности твердопроцентных и дисконтных ценных бумаг.
Создадим рабочую книгу и введем в ячейку A1 первого рабочего листа название нашего примера, например, Доходность ЦБ, а в ячейке A3 название нашей первой таблицы – Твердопроцентные ЦБ. Соответствующее имя, например, ТПЦБ, следует присвоить и рабочему листу. Затем укажем дату, на которую производится расчет доходности ценных бумаг. Введем в ячейку A2 текст Дата, а в ячейку B2 вставим функцию, с помощью которой в этой ячейке всегда будет представлено текущее значение даты. Укажем в ячейке B2 формулу: =СЕГОДНЯ(). Во второй строке для информации укажем рыночную процентную ставку процента на текущую дату. Она вводится с клавиатуры. Укажем в ячейке D2 значение Ставка, а в ячейке E2 введем значение текущей рыночной процентной ставки и сформатируем ячейку процентным стилем с двумя десятичными знаками. В качестве текущей рыночной процентной ставки будем указывать дисконтную или ломбардную ставки центрального банка.
В первом столбце таблицы укажем код ценных бумаг. В рассматриваемом примере этот код совершенно произволен. В остальных столбцах будут представлены другие важные данные для ценных бумаг – данные, которые в последующем будут использованы при определении годовой доходности. Введем в ячейки A5 – I5 заголовки столбцов:
Код ЦБ
Курс
Дата выпуска
Дата погашения
Купон
Погашение (%)
Периодичность
Базис
Доход (Рендита).
Ценные бумаги в примере будут расположены (как это часто практикуется) в порядке убывания номинальной доходности. Укажем в ячейке A6 код первой ценной бумаги, а в ячейке B6 – ее текущий курс. Данные в столбце B необходимо обновлять каждый день (или хотя бы в те дни, в которые меняется курс ценных бумаг). В данном примере для нас не имеет значения номинал ценной бумаги, поскольку и текущий курс, и курс при погашении указыва-



Рис. 8.1

ся в процентах от номинальной стоимости.
Введем в ячейки C6 и D6 даты выпуска и погашения ценных бумаг. Для простоты в примере представлены бумаги со сроком обращения 5 – 10 лет. Столбец E будет содержать данные о номинальной процентной ставке. Выделим этот столбец с помощью щелчка на его заголовке, выберем команду Ячейки меню Формат и в разделе Число выберите из списка Числовые форматы элемент Процентный, а также задайте отображение двух десятичных знаков. Теперь для задания процентной ставки 7% достаточно ввести с клавиатуры просто 7, а не 0,07. В ячейке F6 укажем число 100 (как правило, большинство рентных ценных бумаг выкупается по их номинальной стоимости).
Столбец G будет содержать сведения о периодичности выплат процентов. Если выплата производится раз в год, то следует указать 1, если раз в полгода – 2, если ежеквартально – 4. В столбце H поставим 1, так как будет считаться фактическое количество дней. В результате получаем таблицу, приведенную на рис. 8.1.



Рис. 8.2

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


Рис. 8.3



Рис. 8.4

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

= ЕСЛИ(B6« »; « »;ДОХОД($B$2;D6;E6;B6;F6;G6;H6)).
Скопируем формулу из ячейки I6 в остальные ячейки столбца I. В результате получает таблицу (рис. 8.2.).
Рассмотрим пример вложения свободных денег в дисконтные ценные бумаги.
Перейдем во второй рабочий лист, присвоим ему имя ДЦБ, а в ячейку A3 введем название примера Дисконтные ЦБ. Таблица, полученная после заполнения исходных данных, приведена на рис. 8.3.
Дисконтные ценные бумаги эмитируются со скидкой (дисконтом) и выкупаются, как правило, по истечению срока обращения по номинальной стоимости. Тем самым данные о размере купона и периодичности выплат по нему являются излишними. Хотя срок обращения дисконтных ценных бумаг довольно часто не превышает одного года, расчет их доходности производится исходя из календарного года. В этой таблице, также как и в предыдущей, значение текущей даты выступает в качестве даты приобретения ценных бумаг (Дата_соглашения).
После ввода исходных данных в ячейке G6 зададим формулу

= ЕСЛИ(B6« »; « »;ДОХОДСКИДКА($B$2;D6;B6;E6;F6))

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

Литература

1. Андреев В.В. Табличный процессор Excel в экономических расчетах. Учебное пособие по курсу «Технологическая обработка экономической информации». Казань: КГЭУ, 2002.
2. Буза М.К., Певзнер Л.В., Хижняк И.А. Операционная система Windows и ее приложения. Минск: Вышэйшая школа, 1997.
3. Гарнаев А.Ю. Использование MS Excel и VBA в экономике и финансах. СПб.: BHV – Санкт-Петербург, 2000.
4. Лавренов С.М. Excel: Сборник примеров и задач. М.: Финансы и статистика, 2002.
5. Пасько В. Microsoft Office 97 (русифицированная версия). Киев: Издательская группа BHV, 1998.
6. Пробитюк А. Excel 7.0 для Windows 95 в бюро. Киев: Торгово-издательское бюро BHV, 1996.
7. Справочные материалы пакета Excel 2000.
Содержание

Предисловие
3

Лабораторная работа №1. Введение в табличный процессор Excel
5

Лабораторная работа №2. Расчеты в таблицах и вычисления с помощью функций Excel

18

Лабораторная работа №3. Построение диаграмм и графиков
25

Лабораторная работа № 4. Базы данных – 1
30

Лабораторная работа № 5. Базы данных – 2
40

Лабораторная работа № 6. Базы данных – 3
47

Лабораторная работа № 7. Финансовые функции Excel – 1
56

Лабораторная работа № 8. Финансовые функции Excel – 2
75

Литература
82

















Приложенные файлы

  • doc 8906544
    Размер файла: 997 kB Загрузок: 0

Добавить комментарий