Выполнение финансово-экономических расчетов с помощью Excel. Введение в процессор электронных таблиц MS Excel: Методические указания к выполнению лабораторных работ

3 Введение Методические указания “Выполнение финансово-экономических расчетов с помощью Excel” предназначены для использования студентами специальности 060800 – “Экономика и управление на предприятиях лесного комплекса” – и представлены двумя частями. Каждая часть включает материал 4-х лабораторных работ. Первая часть – “Введение в процессор электронных таблиц Microsoft Excel” – призвана обеспечить усвоение основных понятий, свойственных электронным таблицам, и приобретение начальных практических навыков работы с процессором электронных таблиц Microsoft Excel. Она ориентирована на использование студентами 2-го курса при изучении ими раздела “Основы современных информационных технологий” дисциплины “Информатика”. На первом лабораторном занятии студенты получают общие сведения об электронных таблицах, пользовательском интерфейсе Microsoft Excel, учатся управлять рабочими книгами и листами электронной таблицы. На втором лабораторном занятии студенты учатся управлять отдельными ячейками и блоками ячеек электронной таблицы, выполняют ввод различной информации (чисел, текста, формул) в ячейки, создают и используют окна и панели электронной таблицы для просмотра содержащейся в ней информации. На третьем лабораторном занятии студенты знакомятся с некоторыми вычислительными возможностями Microsoft Excel (на примере обработки матриц) и с набором его стандартных функций, а также создают несколько собственных функций с использованием встроенного языка Visual Basic for Applications (VBA). На четвертом лабораторном занятии студенты учатся выполнять несложные финансово-экономические расчеты, создавая электронную таблицу для бухгалтерского баланса. Вторая часть – “Финансовый анализ инвестиций в Excel” – призвана обеспечить знакомство с набором финансовых функций, встроенных в процессор электронных таблиц Microsoft Excel, и закрепление практических навыков их применения при выполнении экономических расчетов. Она ориентирована на использование студентами 4-го курса при изучении ими дисциплины “Компьютерные технологии экономических расчетов”. В каждой лабораторной работе содержатся вопросы и упражнения для самопроверки, позволяющие контролировать степень овладения представленным материалом. Для более углубленного изучения функциональных возможностей Microsoft Excel студентам могут быть предложены различные темы для написания рефератов (по выбору преподавателя).

4

Лабораторная работа № 1 (2 часа) Тема занятия: Общие сведения об электронных таблицах (ЭТ). Знакомство с основными понятиями и интерфейсом Microsoft Excel. Работа с рабочими книгами и рабочими листами. Общие сведения Процессоры электронных таблиц, называемые также табличными процессорами или просто электронными таблицами (ЭТ), образуют класс пакетов прикладных программ (ППП), чрезвычайно популярный у пользователей персональных компьютеров (ПК). В основе этих программ лежит “электронная” модель обычной таблицы, представленной столбцами (колонками) и строками (рядами), на пересечении которых образуются ячейки (клетки). Используя эту модель, можно вводить и редактировать табличные данные в соответствии с заданным форматом, производить выборку и просмотр необходимых ячеек, манипулировать участками таблиц, устанавливать логические взаимосвязи между различными таблицами, выполнять вычисления любой сложности путем задания соответствующих формул и т.д. Важнейшим свойством ЭТ является возможность сохранения полученных результатов работы на магнитных дисках ПК, т.е. однажды подготовленная таблица, представляющая собой форму для ввода исходных данных и содержащая необходимые для расчетов формулы, может использоваться многократно. Еще одним важным свойством ЭТ является возможность выполнения многовариантных расчетов и анализа проблем типа “Что будет, если ...?”. Наличие многочисленных встроенных операций и функций (математических, статистических, финансовых и других) для обработки таблиц в сочетании с развитыми графическими средствами вывода обеспечивают быстрое выполнение расчетов и приведение полученных результатов к виду, удобному для анализа и принятия решений. В настоящее время существуют десятки видов ЭТ, среди которых такие известные программные продукты, как Microsoft Excel, Lotus 1-2-3, Supercalc, Quattro Pro и другие. Любая из перечисленных ЭТ как минимум обеспечивает выполнение следующих операций [1]: • создание таблиц любой формы; • свободную манипуляцию столбцами и строками таблиц; • автоматический пересчет значений в таблицах в случае изменения исходных данных; • разнообразные математические вычисления с данными, представленными в таблицах; • подготовку и печать отчетов;

5 • объединение нескольких таблиц в одну; • сортировку, поиск и выборку данных по заданному критерию; • представление результатов обработки данных в графическом виде; • преобразование данных для обработки другими типами ЭТ и т.д. В дальнейшем будут рассматриваться возможности и приемы работы с одной из наиболее распространенных ЭТ − Microsoft Excel версий 5.0 и 7.0 [2, 3]. Запуск Microsoft Excel Для запуска Microsoft Excel версии 5.0 необходимо загрузить Windows 3.1, развернуть окно Microsoft Office (если оно представлено на экране в пиктографическом виде) и дважды щелкнуть левой кнопкой мыши на соответствующей пиктограмме, имеющей следующий вид:

Запуск Microsoft Excel версии 5.0 в Windows 3.1 можно также осуществить, используя команду Выполнить... в меню Файл Диспетчера Программ. В поле ввода Командная Строка диалогового окна Выполнить необходимо в этом случае указать полную спецификацию главного файла приложения (например, C:\MSOFFICE\EXCEL50\EXCEL.EXE). Для запуска Microsoft Excel версии 5.0 или 7.0 в Windows 95 необходимо дважды щелкнуть левой кнопкой мыши на соответствующем ярлыке, имеющем следующий вид:

Запуск Microsoft Excel версии 5.0 и 7.0 в Windows 95 также можно осуществить, используя команду Выполнить в стартовом командном меню (появляется при нажатии кнопки Пуск). В поле ввода диалогового окна Запуск программы необходимо указать полную спецификацию главного файла приложения (например, C:\MSOFFICE\EXCEL50\EXCEL.EXE). Упражнение 1. Определив версию Windows, установленную на компьютере, произвести запуск Microsoft Excel одним из описанных выше способов. Выход из Microsoft Excel Для выхода из Microsoft Excel версии 5.0 и возврата в Windows необходимо в главном меню выбрать пункт Файл, а затем в появившемся ниспадающем меню − команду Выход (можно также воспользоваться комбинацией клавиш Alt+F4).

6 Для выхода из Microsoft Excel версии 7.0 и возврата в Windows 95 можно использовать один из следующих способов: 1) в главном меню выбрать пункт Файл, а затем в появившемся ниспадающем меню − команду Выход; 2) в системном меню, которое появляется при щелчке левой кнопкой мыши на пиктограмме приложения в строке заголовка, выбрать команду Закрыть (можно также воспользоваться комбинацией клавиш Alt+F4). 3) щелкнуть левой кнопкой мыши на кнопке закрытия окна приложения ( ), расположенной справа в строке заголовка Microsoft Excel. Упражнение 2. Выполнить выход из Microsoft Excel одним из указанных выше способов. Окно Microsoft Excel Через несколько секунд после запуска Microsoft Excel версии 5.0 на экране появится окно, показанное на рис. 1. Поскольку, с точки зрения Windows, Microsoft Excel версии 5.0 является обычным приложением, его окну присущи все элементы, характерные для прикладного окна: строка заголовка, строка главного (операционного) меню, кнопка вызова системного меню, кнопки изменения размеров окна; линейки (полоски) вертикальной и горизонтальной прокрутки.

Строка заголовка

Кнопки изменения размеров окна

Главное меню И нструментальное меню Строка формулы Кнопка вызова системного меню

П оле рабочего листа

Линейки прокрутки

7 Рис.1. Окно Microsoft Excel версии 5.0 Кроме того, в окне Microsoft Excel версии 5.0 представлены: пиктографическое (инструментальное) меню, расположенное ниже строки главного меню и состоящее обычно из двух рядов кнопок с пиктограммами; строка формулы, расположенная под пиктографическим меню; поле рабочего листа, занимающее большую часть области окна и состоящее из множества ячеек; строка состояния, расположенная в нижней части окна. И, наконец, в окне Microsoft Excel версии 5.0 может присутствовать так называемое контекстное меню, появляющееся при щелчке правой кнопки мыши на ячейке, помеченной группе ячеек, ярлычке выделенного листа или на встроенном в таблицу объекте. Внешний вид окна Microsoft Excel версии 7.0 отличается от вида окна, представленного выше, в основном в эстетическом отношении. С функциональной точки зрения эти отличия не столь велики. Следует обратить внимание на группу экранных кнопок, расположенных справа в строке заголовка ( ). Эти кнопки в порядке их следования слева направо выполняют следующие функции: свернуть окно приложения до значка, помещаемого на панели задач; отобразить нормальное (не полноэкранное) окно; закрыть окно, т.е. завершить работу с приложением. Упражнение 3. Указать перечисленные выше элементы окна Microsoft Excel на экране монитора. Упражнение 4. Продемонстрировать свое умение работать с линейками прокрутки и кнопками изменения размеров окна Microsoft Excel. Справочная подсистема Microsoft Excel Microsoft Excel имеет справочную подсистему, которая обеспечивает оперативное получение информации (помощь) по его использованию. Различают помощь, зависящую от контекста, и помощь, не зависящую от контекста. Контекстно-зависимая помощь всегда соответствует конкретной ситуации. Например, она может относиться к помеченной в данный момент команде меню или к определенному, отмеченному курсором, элементу диалогового окна. В Microsoft Excel имеются два способа для получения контекстно-зависимой помощи: 1) по нажатию клавиши F1. Для этого помечается объект, о котором необходимо получить справку, и нажимается клавиша F1; 2) по нажатию комбинации клавиш Shift+F1 или крайней правой пиктограммы в верхнем ряду стандартного пиктографического меню. В этом случае курсор мыши видоизменяется − справа от него появляется жирный вопросительный знак ( ). Указав таким курсором на интересующий объект на

8 экране и, нажав левую кнопку мыши, можно получить справочную информацию об этом объекте. Упражнение 5. Используя поочередно оба способа для получения контекстно-зависимой помощи, вывести на экран справочную информацию обо всех командах меню Файл. Для получения контекстно-независимой помощи предназначен пункт ? в главном меню. Выбор этого пункта приводит к появлению ниспадающего меню, в котором содержатся различные команды, обеспечивающие доступ ко всей имеющейся в Microsoft Excel справочной информации. Упражнение 6. Используя описанный выше способ получения контекстно-независимой помощи, вывести на экран, а затем и на принтер, справочную информацию об управлении листами рабочих книг. Для этого необходимо в ниспадающем меню ? выбрать команду Содержание; далее в окне справки следует последовательно выбрать темы: Использование Microsoft Excel, Основные навыки, Рабочие книги, Общие сведения об управлении листами рабочих книг. Управление рабочими книгами С точки зрения файловой системы, рабочая книга − это файл с расширением имени .xls (используется по умолчанию). Новая рабочая книга в Microsoft Excel может быть создана одним из следующих способов: 1) с помощью команды Создать ниспадающего меню Файл; 2) с помощью кнопки Создание Рабочей Книги, расположенной в левом верхнем углу стандартного пиктографического меню (пиктограмма с изображением листа бумаги); 3) с помощью нажатия комбинации клавиш Ctrl+N. Упражнение 7. Создать новую рабочую книгу одним из описанных выше способов. Рабочие книги в Microsoft Excel автоматически именуются Book1 (Книга 1), Book2 (Книга 2), Book3 (Книга 3) и т.д. Можно присвоить рабочей книге уникальное название, воспользовавшись диалоговым окном Сохранить как, которое вызывается по одноименной команде в меню Файл (рис. 2). К операциям сохранения файлов можно также получить доступ с помощью клавиш F12 (Сохранить как...) и Shift+F12 (Сохранить) или щелчком левой кнопки мыши на пиктограмме с изображением дискеты в стандартном пиктографическом меню. Упражнение 8. Переименовать вновь созданную рабочую книгу (используйте для этой цели некоторое имя, например Proba_1). Можно открыть уже существующую рабочую книгу, воспользовавшись кнопкой Открыть, расположенной второй слева в стандартном пиктографическом меню (пиктограмма с изображением раскрытой папки),

9 комбинацией клавиш Ctrl+O или командой Открыть... в ниспадающем меню Файл. Упражнение 9. Открыть одним из перечисленных выше способов рабочую книгу с именем Proba_1.xls.

Рис. 2. Диалоговое окно “Сохранить как” Управление рабочими листами Рабочая книга состоит из некоторого количества листов. По умолчанию в новой книге содержатся 16 листов. Это значение по умолчанию может быть изменено с помощью диалогового окна Параметры (вкладка Основные), вызываемого командой Параметры... в ниспадающем меню Сервис. Упражнение 10. Изменить установленное по умолчанию значение для количества листов в новой рабочей книге на некоторое другое (например, на 12). В ходе работы с книгой из нее можно удалить часть листов или добавить новые листы. Удаление листов из рабочей книги можно выполнить одним из следующих двух способов: 1. Выделить удаляемый лист, щелкнув левой кнопкой мыши на ярлычке листа. Затем, удерживая курсор мыши на ярлычке листа, щелкнуть правой кнопкой мыши и в появившемся контекстном меню выбрать команду Удалить. В появившемся окне подтверждения с текстом "Выделенные листы будут удалены навсегда. Продолжать?" нажать кнопку ОК. 2. Выделить удаляемый лист, щелкнув левой кнопкой мыши на ярлычке листа. Затем выбрать пункт Правка в главном меню и в появившемся ниспадающем меню − команду Удалить лист. Далее нажать кнопку OK в появившемся окне подтверждения. Одновременно можно выделить несколько подряд расположенных рабочих листов. Для этого следует щелкнуть левой кнопкой мыши на ярлычке первого листа из рассматриваемого диапазона, нажать клавишу Shift и, не

10 отпуская ее, щелкнуть левой кнопкой мыши на ярлычке последнего листа диапазона. Упражнение 11. Удалить в текущей рабочей книге листы с 13-го по 16-й одним из описанных выше способов. Для того, чтобы одновременно выделить несколько несмежных листов, необходимо нажать клавишу Ctrl и, не отпуская ее, щелкнуть левой кнопкой мыши на ярлычке каждого выделяемого листа. Упражнение 12. Одновременно выделить в текущей рабочей книге 1-й, 3-й и 5-й листы. Добавление новых листов в книгу также можно выполнить одним из следующих двух способов: 1. Выделить лист, перед которым в книгу должен быть вставлен новый лист. Щелкнуть правой кнопкой мыши и в появившемся контекстном меню выбрать команду Вставка. В открывшемся окне списка Вставить выбрать Рабочий лист и нажать кнопку OK. 2. Выделить лист, перед которым в книгу должен быть вставлен новый лист. Затем в главном меню выбрать пункт Вставка и в появившемся ниспадающем меню − команду Рабочий лист. Упражнение 13. Вставить новый лист в рабочую книгу, расположив его между 6-м и 7-м листами. Используйте для этого один из описанных выше способов. По умолчанию листы в рабочей книге именуются как Лист1, Лист2, Лист3 и т.д. Переименование любого листа рабочей книги можно выполнить одним из следующих двух способов: 1. Дважды щелкнуть левой кнопкой мыши на ярлычке листа, который требуется переименовать. Затем в появившемся диалоговом окне Переименовать лист в поле ввода Имя листа ввести новое имя листа и нажать кнопку OK. 2. Выделить лист и щелкнуть правой кнопкой мыши. В появившемся контекстном меню выбрать команду Переименовать... и в диалоговом окне Переименовать лист в поле ввода Имя листа ввести новое имя листа и нажать кнопку OK. Упражнение 14. Переименовать Лист1 текущей рабочей книги, используя один из описанных выше способов. Выяснить при этом максимально допустимую длину имени листа. Рабочие листы можно перемещать и копировать как в пределах одной книги, так и между двумя различными рабочими книгами. Для этого в Microsoft Excel обеспечиваются следующие два способа: 1. Выделить копируемый или перемещаемый лист, а затем выбрать команду Переместить/скопировать... в ниспадающем меню Правка. В открывшемся диалоговом окне Переместить или скопировать следует выбрать книгу, в которую нужно переместить или скопировать лист, а также лист, перед которым будет помещен лист.

11 2. Выделить копируемый или перемещаемый лист, а затем выбрать команду Переместить/скопировать... в контекстном меню. В открывшемся диалоговом окне Переместить или скопировать следует выбрать книгу, в которую нужно переместить или скопировать лист, а также лист, перед которым будет помещен лист. Упражнение 15. Переместить 1-й лист из рабочей книги Proba_1 в новую рабочую книгу, поместив его перед 5-м листом. Упражнение 16. Скопировать 1-й лист рабочей книги Proba_1 в конец этой книги. Вопросы для самопроверки 1. Сформулировать определение для такого понятия как “электронная таблица”. 2. Какие основные возможности для обработки табличных данных предоставляют пользователю электронные таблицы? 3. Как выполнить a) запуск Microsoft Excel; б) выход из Microsoft Excel? 4. Что такое контекстно-зависимая помощь? Как получить контекстнозависимую помощь по интересующей теме в Microsoft Excel? 5. Что такое контекстно-независимая помощь? Как воспользоваться контекстнонезависимой помощью для получения справочной информации по интересующей теме? 6. Что такое рабочая книга и рабочий лист в Microsoft Excel? 7. Какие возможности предоставляет пользователю Microsoft Excel для управления рабочими книгами и рабочими листами? 8. Как выполнить удаление а) одного рабочего листа; б) нескольких смежных рабочих листов; в) нескольких несмежных рабочих листов? 9. Как выполнить добавление новых листов в существующую рабочую книгу? 10. Как выполнить копирование и перемещение рабочего листа а) в пределах одной книги; б) между различными книгами?

Лабораторная работа № 2 (2 часа) Тема занятия: Управление ячейками таблицы: выбор активной ячейки, перемещение по ячейкам таблицы, ввод данных (чисел, текста и формул) в ячейки таблицы. Создание панелей и окон таблицы. Пример простой таблицы, демонстрирующей некоторые возможности процессора электронных таблиц Microsoft Excel. Управление ячейками таблицы Любая таблица строится в поле рабочего листа. Базовыми “строительными” элементами таблицы являются ячейки. Каждая ячейка рабочего листа выделяется пунктиром и имеет уникальный адрес,

12 образованный номером столбца и номером строки, на пересечении которых ячейка находится. Столбцы нумеруются с помощью одиночных и парных букв латинского алфавита (A, B, C, ..., Z, AA, AB, AC, ..., AZ, BA, BB, BC, ..., BZ, ... IV); всего в таблице 256 столбцов (26+8×26+22). Строки нумеруются с 1-й по 16384-ю. Таким образом, общее количество ячеек в поле рабочего листа составляет 4194304 (256×16384) ячейки − более чем достаточно для построения любой мыслимой таблицы. Адресоваться к каждой ячейке таблицы можно с помощью имени (координат) ячейки (например, A1, С15, AB100 и т.д.). Ввод табличных данных осуществляется в ячейки таблицы. В каждый момент времени только одна ячейка таблицы является активной. Активная ячейка выделена с помощью специального указателя − рамки, окаймляющей эту ячейку. Первоначально активной ячейкой является верхняя левая ячейка таблицы с адресом A1. В дальнейшем можно выбрать другую активную ячейку. Смена активной ячейки выполняется либо переводом курсора мыши в область новой ячейки и последующим щелчком левой кнопки мыши, либо использованием клавиш управления курсором (←, →, ↑, ↓). Ускоренное перемещение по ячейкам таблицы выполняется с помощью следующих комбинаций клавиш: Ctrl+Home − в начало таблицы; Ctrl+End − в последнюю использованную ячейку таблицы; Ctrl+PgUp − на страницу вправо; Ctrl+PgDn − на страницу влево; PgUp − на страницу вверх; PgDn − на страницу вниз. Упражнение 1. Произвести смену активной ячейки, используя а) манипулятор “мышь”; б) клавиши управления курсором. Какой способ более удобен? Данные, помещаемые в ячейки таблицы, могут иметь различный характер (тексты, числа, формулы). Для ввода данных в ячейку необходимо выполнить следующие два шага: 1. Переместить указатель поля в ячейку, в которую будет производиться ввод данных (при этом в левой части строки формулы появятся координаты этой ячейки). 2. Ввести с клавиатуры необходимые данные (при этом автоматически активизируется строка формулы, в которой будут отображаться вводимые с клавиатуры данные). Ввод чисел Ввод числа в ячейку обычно заканчивается нажатием клавиши Enter или переходом в следующую ячейку путем нажатия одной из клавиш управления курсором. Введенные числа в ячейках выравниваются по правому краю. При вводе чисел, размер которых превышает ширину ячейки, Microsoft Excel отображает в ячейке последовательность символов #. Чтобы восстановить нормальное отображение числа, необходимо увеличить ширину столбца. Это

13 можно выполнить с помощью последовательного выбора следующих команд меню: Формат, Столбец и Подгон ширины. Числа в ячейки можно также вводить в научном (экспоненциальном) формате, имеющем следующий вид: <мантисса> E <порядок> Например, число 1500 в экспоненциальном формате запишется как 1,5Е3. Вид разделителей целой и дробной части числа, а также тысяч и элементов списка задается в Windows с помощью Панели Управления (элемент Стандарты). Для вывода чисел в научном формате необходимо установить категорию Научный на вкладке Число диалогового окна, появляющегося при последовательном выборе команд Формат и Ячейки. Упражнение 2. Осуществить ввод нескольких произвольных чисел в ячейки таблицы. Сколько цифр умещается в ячейке? Что происходит, если количество цифр числа превышает ширину ячейку, заданную по умолчанию? Ввод текста Ввод текста в отдельную ячейку выполняется аналогично вводу чисел. В отличие от чисел, введенный текст выравнивается по левому краю ячейки. При вводе длинного текста он либо отображается поверх ячеек, следующих за активной (если они свободны), либо отображаются в ячейке в “усеченном” виде. Чтобы восстановить нормальное отображение текста, следует увеличить ширину столбца таблицы (см. выше). Упражнение 3. Осуществите ввод нескольких слов в ячейки таблицы. Что происходит, если размер введенного текста превышает размеры отдельной ячейки? Ввод числовых рядов и списков При решении множества задач приходится оперировать числовыми рядами. Microsoft Excel упрощает ввод числовых рядов в таблицы. Для ввода элементов ряда достаточно записать два первых его элемента один под другим и пометить их. Затем следует захватить курсором мыши маленький черный квадрат и транспортировать его вниз до тех пор, пока не получится числовой ряд нужной длины. Аналогичным образом можно создать ряд и в горизонтальном направлении. Microsoft Excel позволяет получать с помощью описанного выше способа не только числовые ряды, но и списки, содержащие текстовые элементы. На рис. 3 показаны примеры некоторых рядов и списков, которые можно создавать и использовать в Microsoft Excel. Для формирования нескольких видов списков, состоящих, например, из названий месяцев года или дней недели, достаточно указать только первый элемент. Microsoft Excel позволяет пользователю самому создавать необходимые типы списков. Для этого необходимо в главном меню выполнить

14 команду Сервис, а затем в выпадающем меню − команду Параметры. Далее в открывшемся диалоговом окне следует выбрать вкладку Списки (рис. 4). Если в окне Списки выбрать строку НОВЫЙ СПИСОК, то имена элементов списка можно вводить в окне Элементы списка. Если нужный список уже существует на рабочем листе, то его достаточно пометить и перенести в это окно, нажав кнопку Импорт.

Рис. 3. Примеры рядов и списков в Microsoft Excel версии 7.0

15

Рис. 4. Вкладка Списки диалогового окна Параметры Упражнение 4. Воспользовавшись приведенной выше информацией, создать несколько собственных видов списков (например, фамилий и инициалов русских классиков, названий российских городов и т.д.). Упражнение 5. Создать какой-либо список на рабочем листе, а затем “импортировать” его в окно Списки. Использовать затем созданный список для быстрого ввода данных в таблицу. Ввод формул Формулы, вводимые в ячейки таблицы, всегда начинаются со знака равенства (=). Справа от знака равенства должно стоять выражение, в состав которого могут входить числа, тексты, абсолютные и относительные адреса ячеек таблицы, обращения к функциям и знаки операций. Упражнение 6. Ввести в активную ячейку следующую простейшую формулу: =1+2. Какой результат будет записан в эту ячейку после нажатия клавиши Enter? Упражнение 7. Ввести в выбранную активную ячейку выражение 1+2. Что будет отображено в этой ячейке после нажатия клавиши Enter? Почему этот результат отличается от результата, полученного в Упражнении 6? Упражнение 8. Используя возможности Microsoft Excel по вводу формул в ячейки таблицы, рассчитать сумму чисел, содержащихся в ячейках A1 и A2, и занесите результат в ячейку С1. Упражнение 9. Используя возможности Microsoft Excel, рассчитать значение с=(a2+b2)/2 при различных значениях a и b. Упражнение 10. Как отмечалось выше, в состав формул может входить текст и знак операции конкатенации текстовых строк (&). Ввести в ячейки,

16 начиная с активной, следующую формулу: = “Фамилия ” & “Имя ” & “Отчество”. Что будет отображено в этих ячейках после нажатия клавиши Enter? Microsoft Excel позволяет выполнять достаточно сложные расчеты, предоставляя в распоряжение пользователя мощный арсенал стандартных (встроенных) функций: финансовых, математических, статистических, текстовых, логических и других. Упражнение 11. Используя возможности для получения контекстнозависимой и/или контекстно-независимой помощи в Microsoft Excel, получить информацию о следующих функциях: логическая функция ЕСЛИ, математические функции КОРЕНЬ и СТЕПЕНЬ. Упражнение 12. Используя возможности Microsoft Excel, ввести в ячейки X1 и X2 формулы для вычисления действительных корней квадратного уравнения ax2+bx+c=0, где значения для коэффициентов a, b и c хранятся соответственно в ячейках A1, B1 и C1. Решение данного упражнения приведено ниже. Известно, что квадратное уравнение имеет действительные корни x1 и x2, равные соответственно x1 =

−b + D −b − D , x2 = , 2a 2a

тогда и только тогда, когда величина D=b2−4ac, называемая дискриминантом уравнения, превышает 0. Таким образом, для упрощения выражения следует ввести в ячейку D1 следующую формулу: =СТЕПЕНЬ(B1;2)-4*A1*C1

Далее в ячейки X1 и X2 таблицы следует ввести соответственно следующие формулы: =ЕСЛИ (D1>=0; (-B1+КОРЕНЬ(D1))/(2*A1);"Действительных корней нет") =ЕСЛИ (D1>=0; (-B1-КОРЕНЬ(D1))/(2*A1); "Действительных корней нет")

В заключение − ввести в ячейки A1, B1 и C1 различные значения и наблюдать в ячейках X1 и X2 появление значений корней уравнения или строки "Действительных корней нет". Примечание. Ячейки, выбранные для хранения значений коэффициентов (A1, B1 и C1), дискриминанта (D1) и корней уравнения (X1 и X2), достаточно далеко отстоят друг от друга и поэтому приходится выполнять "прокрутку" (скроллинг) содержимого экрана, чтобы просмотреть значения корней уравнения после введения новых значений для коэффициентов уравнения. Microsoft Excel предоставляет возможности для разбиения рабочего поля таблицы на части (по горизонтали и по вертикали), называемыми панелями, и независимой “прокрутки” содержимого в каждой из панелей (см. ниже рис. 6). Блоки ячеек Многие операции в таблицах выполняются над блоками. Блок − это любая непрерывная прямоугольная область ячеек таблицы. Блок обычно задается указанием его начальной (верхней левой) и конечной (нижней правой)

17 ячеек, разделенных двоеточием. Например, A1:C3 − прямоугольный блок ячеек; A1:F1 − блок ячеек строки 1-й строки таблицы, A1:A5 − блок ячеек столбца A таблицы. Отдельную ячейку таблицы можно рассматривать как вырожденный блок (т.е. блок, состоящий из одной ячейки). Блоки часто используются в качестве параметров команд и встроенных функций Microsoft Excel для выполнения однотипных операций над несколькими ячейками таблицы одновременно. Чтобы указать Microsoft Excel на необходимость работы с блоком ячеек, выполняют его маркировку (пометку). Маркировка прямоугольного блока ячеек выполняется следующим образом: поместить курсор мыши в начальную ячейку блока, нажать левую кнопку мыши и, не отпуская ее, переместить курсор в конечную ячейку блока. Маркированный блок выделяется черным цветом. Кроме того, быстрое маркирование блока ячеек, содержащихся в одной какой-либо строке таблицы, выполняется с помощью переноса курсора мыши в координатный столбец таблицы и нажатия левой кнопки мыши. Если одновременно нужно маркировать несколько строк, то щелчок левой кнопки мыши выполняется в комбинации с нажатием клавиши Ctrl или Shift. Быстрое маркирование блока ячеек, содержащихся в одном каком-либо столбце таблицы выполняется щелчком левой кнопки мыши в координатной строке таблицы. Если одновременно нужно маркировать несколько столбцов, то щелчок левой кнопки мыши выполняется в комбинации с нажатием клавиши Ctrl или Shift. Быстрое маркирование всей таблицы выполняется щелчком левой кнопки мыши в месте пересечения координатного столбца и координатной строки (верхний левый угол таблицы). Для маркирования несмежных областей таблицы (т.е. для выполнения множественного выбора) используется один из описанных выше способов в комбинации с нажатой клавишей Ctrl. Маркирование блока ячеек таблицы можно выполнить и с помощью клавиатуры. Для этого производится отметка начальной ячейки блока нажатием клавиши F8, затем курсор перемещается в конечную ячейку блока и повторно нажимается клавиша F8. Упражнение 13. Потренироваться в маркировании различных блоков ячеек, используя описанные выше способы маркирования.

18

Рис.5. Пример таблицы, созданной с помощью Microsoft Excel версии 5.0 Упражнение 14. Создать с помощью Microsoft Excel таблицу, подобную той, что приведена на рис. 5. Заполнить ячейки таблицы, образующие блок С4:E6, данными и выполнить суммирование чисел сначала по столбцам, а затем по строкам таблицы. Для этого необходимо маркировать блок ячеек C4:E7 и нажать в пиктографическом меню кнопку со значком Σ (автосуммирование). При этом автоматически выполняется суммирование чисел, содержащихся в ячейках маркированного блока, и полученное значение заносится в ячейку C7. Аналогичную процедуру следует проделать и для блоков ячеек D4:D6 и E4:E6. Затем операцию суммирования нужно выполнить для блоков C4:E4, C5:E5 и C6:E6. В заключение − выполнить суммирование чисел, содержащихся в блоке F4:F6 и занести суммарное значение в ячейку F7. Далее можно продемонстрировать возможности Microsoft Excel по автоматическому перерасчету суммы в случае изменения одного из слагаемых. Для этого достаточно изменить одно из суммируемых значений и нажать клавишу Enter. При этом в ячейках, содержащих итоговые значения, можно наблюдать смену первоначальных значений. Создание панелей таблицы Для создания двух горизонтальных панелей необходимо выполнить следующие действия: • поместить курсор мыши на черный прямоугольник, находящийся над верхней кнопкой вертикальной линейки прокрутки (курсор при этом видоизменяет свою форму, превращаясь в два горизонтальных отрезка с направленными от них стрелками);

19 • нажав левую кнопку мыши и не отпуская ее, отбуксировать видоизмененный курсор в нужную позицию по вертикали (одновременно переместится и горизонтальная разграничительная линия); • отпустив левую кнопку мыши, разделить поле таблицы на две горизонтальные панели. Для создания двух вертикальных панелей необходимо выполнить следующие действия: • поместить курсор мыши на черный прямоугольник, находящийся справа от правой кнопки линейки прокрутки (курсор при этом видоизменяет свою форму, превращаясь в два вертикальных отрезка с направленными от них стрелками); • нажав левую кнопку мыши и не отпуская ее, отбуксировать видоизмененный курсор в нужную позицию по горизонтали (одновременно переместится и вертикальная разграничительная линия); • отпустив левую кнопку мыши, разделить поле таблицы на две вертикальные панели. Необходимость в разбиении таблицы на две вертикальные панели может возникнуть при работе с таблицами, содержащими большое количество колонок (широкие таблицы). В этом случае в левой панели размещают одну или несколько первых колонок таблицы, а в правой − остальную часть таблицы. Затем “прокручивают” содержимое правой панели относительно содержимого левой панели. Еще один способ разбиения окна таблицы на панели заключается в использовании команды Разбить в ниспадающем меню Окно. Разграничительные линии проводятся относительно левого верхнего угла активной ячейки таблицы. Отменить разбиение окна таблицы на панели можно путем буксировки разграничительных полос в места их первоначального пребывания или воспользовавшись командой Удалить разбиение в ниспадающем меню Окно. Окно таблицы, содержащей формулы для вычисления действительных корней квадратного уравнения, с разбивкой на четыре панели представлено на рис. 6.

20

Рис.6. Окно таблицы, разделенное на четыре панели Упражнение 15. Произвести разбивку окна, которое содержит таблицу, созданную в результате выполнения Упражнения 14, на четыре панели любым из описанных выше способов. Создание окон таблицы Вместо того, чтобы делить окно таблицы на панели, можно создать для этой же таблицы одно или несколько дополнительных рабочих окон. Использование окон имеют определенные преимущества перед использованием панелей, поскольку для каждого окна можно устанавливать размеры независимо и производить несвязанную прокрутку изображения. Для создания еще одного рабочего окна для текущей таблицы следует в ниспадающем меню Окно выбрать команду Новое окно. В поле рабочего листа появится новое окно таблицы. Имеющиеся теперь окна таблицы можно упорядочить, например, как показано на рис. 7, воспользовавшись командой Упорядочить... в ниспадающем меню Окно.

21

Рис. 7. Два окна одной таблицы Упражнение 16. Создать для таблицы, полученной в результате выполнения Упражнения 14, еще одно рабочее окно. Упорядочить полученные окна на экране следующим образом: а) в виде горизонтальной мозаики; б) в виде вертикальной мозаики; в) в виде каскада (стопки) окон. Переключение между окнами таблицы может выполняться либо с помощью мыши, либо с помощью комбинаций клавиш: Ctrl+F6 или Ctrl+Tab (следующее окно) и Shift+Ctrl+F6 или Shift+Ctrl+Tab (предыдущее окно). Кроме того, для переключения между окнами таблицы можно воспользоваться списком окон, представленном в ниспадающем меню Окно. Упражнение 17. Создать для таблицы, полученной в ходе результате выполнения Упражнения 14, несколько рабочих окон и осуществить переключения между этими окнами, используя: а) мышь; б) описанные выше комбинации клавиш.

Вопросы для самопроверки 1. Каким образом нумеруются: а) строки таблицы? б) столбцы таблицы? в) ячейки таблицы?

22 2. Каким образом из множества ячеек, представленных в таблице, выбирается та, в которую должны вводиться данные? 3. Данные какого вида могут вводиться в ячейки таблицы? 4. Как задаются блоки ячеек? Привести примеры задания блоков ячеек: а) одной строки; б) одного столбца; в) прямоугольной области таблицы. 5. Как реагирует Microsoft Excel на ситуацию, когда не помещается в ячейку а) число? б) текст? в) формула? 6. Что представляет собой формула в Microsoft Excel? 7. Что такое панель окна Microsoft Excel? Каким образом окно можно разделить на панели? 8. Каким образом на экране можно создать несколько окон и поместить в них содержимое одной и той же таблицы? Разных таблиц? 9. В чем заключается преимущество окон перед панелями? Как выполнить переключение между окнами таблицы? Каким образом можно скрыть окно?

Лабораторная работа № 3 (4 часа) Тема занятия: Решение некоторых вычислительных задач с помощью Microsoft Excel. Знакомство с набором стандартных (встроенных) функций Microsoft Excel. Создание новых функций с использованием языка Visual Basic. Матрицы В состав стандартных функций Microsoft Excel входит несколько функций для работы с матрицами. Эти функции находятся в категории Математические. Рассмотрим использование функции МУМНОЖ для перемножения двух матриц. Умножение матриц только тогда имеет смысл, когда количество столбцов первой матрицы равно количеству строк второй матрицы. Формула для умножения матриц А и B может быть записана следующим образом: C = A× B, где элемент, стоящий на пересечении j-й строки и k-го столбца результирующей матрицы C вычисляется как cjk= ∑ a jl blk . l

Прежде всего следует ввести в ячейки рабочего листа элементы перемножаемых матриц, а затем пометить область в которую будет помещен результат (рис. 8). Эта область должна, в соответствии с правилами умножения матриц, содержать столько же строк, сколько их в первой матрице (в данном случае − 3), и столько столбцов, сколько их во второй (в данном случае − 1).

23

Рис. 8. Пример задания элементов перемножаемых матриц Затем, используя Мастер функций, следует вызвать функцию МУМНОЖ и указать координаты перемножаемых матриц, задав координаты левого верхнего и правого нижнего углов для первой и второй матриц соответственно, разделенные с помощью двоеточия ( рис. 9).

Рис. 9. Диалоговое окно для задания координат матриц После того, как результат (в данном случае − одно число) будет получен на экране, снова активизировать строку формул, щелкнув в ней левой кнопкой мыши, а затем нажать клавиши Ctrl+Shift+Enter. В строке формул появятся фигурные скобки, а на рабочем листе − окончательный результат перемножения матриц, показанный на рис. 10.

Рис. 10. Результат умножения двух матриц

24 Упражнение 3. Используя функцию МУМНОЖ, перемножить две произвольные матрицы подходящих размерностей (например, 3×2 и 2×4). Какой размер должна иметь результирующая матрица? При работе с матрицами иногда требуется получить матрицу, обратную к данной, т.е. такую матрицу, которая, будучи умноженной на данную, даст в результате единичную матрицу. Единичная матрица − это матрица, на главной диагонали которой находятся единицы, а во всех других позициях − нули. Для получения матрицы, обратной к данной, в Microsoft Excel имеется стандартная функция МОБР. На рис. 11 показан пример вычисления матрицы, обратной по отношению к заданной. Результат перемножения исходной и обратной матриц представлен в ячейках I1:K3.

Рис. 11. Пример вычисления обратной матрицы Упражнение 4. Ввести произвольную матрицу и получить обратную ей матрицу. Для того чтобы убедиться, что обратная матрица вычислена верно, перемножить ее с исходной матрицей. Каков результат умножения матриц? Довольно часто приходится вычислять определитель (детерминант) квадратной матрицы. Для этой цели в Microsoft Excel имеется стандартная функция МОПРЕД. Упражнение 5. Используя функцию МОПРЕД, вычислить определитель следующей матрицы: {1, 2, 3; 4, 5, 6; 7, 8, 9}. Вызов стандартной функции Microsoft Excel имеет более двухсот стандартных (встроенных) функций, автоматизирующих выполнение наиболее часто встречающихся вычислений в различных сферах человеческой деятельности. Для удобства использования все стандартные функции разделены на несколько категорий (классов): финансовые, математические, статистические, текстовые, логические и другие.

25 Все стандартные функции Microsoft Excel имеют единый формат вызова (обращения), состоящий из имени функции и списка аргументов, заключенного в круглые скобки: имя-функции (список-аргументов ) Список аргументов может быть пустым в случае, когда функция не имеет аргументов. Если в списке указывается несколько аргументов, то все они отделяются друг от друга символом “точка с запятой”. В качестве аргументов функции могут быть заданы: • числа; • текст; • адреса ячеек; • блоки ячеек; • другие функции. Упражнение 6. Используя средства получения помощи Microsoft Excel, ознакомиться с информацией о работе со стандартными функциями. Использование Мастера функций Для упрощения обращения к стандартным функциям в Microsoft Excel имеется специальное средство, называемое Мастером функций и активизируемое нажатием кнопки в инструментальном меню. Диалоговое окно, появляющееся при нажатии этой клавиши, показано на рис. 12. В левой части окна Мастера функций перечислены все категории стандартных функций Microsoft Excel версии 7.0. В правой части − список стандартных функций, относящихся к данной категории, в алфавитном порядке.

Рис. 12. Диалоговое окно Мастера функций

26 После выбора нужной категории и функции (например, категория “математические”, функция СУММ), можно щелкнуть левой кнопкой мыши на кнопке Далее>. В результате в области рабочего листа появится диалоговое окно для ввода аргументов функции, показанное на рис. 13.

Рис. 13. Диалоговое окно для ввода аргументов стандартной функции СУММ После ввода значений аргументов в поля ввода (для функции СУММ эти поля озаглавлены как число1, число 2, ..., число30) следует нажать кнопку Готово. В результате в активную ячейку таблицы будет вставлена формула, содержащая вызов выбранной стандартной функции с заданными аргументами. Упражнение 7. Используя стандартную функцию СУММ, просуммировать все целые числа, лежащие в диапазоне от 1 до 30, и поместить результат вычислений в ячейку A1. Вложенные вызовы стандартных функций В формулах допускается вложенные вызовы функций. Например, следующая формула, помещенная в ячейку C1, дает текстовое (словесное) описание операции сложения содержимого ячеек A1 и B1: = СЦЕПИТЬ(ФИКСИРОВАННЫЙ(A1);” плюс “;ФИКСИРОВАННЫЙ(B1); “ равно “; ФИКСИРОВАННЫЙ(СУММ(A1;B1)))

Максимальный уровень вложенности вызовов стандартных функций в данной формуле равняется 3. Упражнение 8. Решить задачу, рассмотренную в Упражнении 10 Лабораторной работы № 2, для общего случая, когда квадратное уравнение может иметь не только действительные, но и комплексные корни. Вывод значений комплексных корней выполнить с использованием обозначения мнимой единицы (i). Решение данного упражнения приведено ниже. Если дискриминант квадратного уравнения отрицательный, то уравнение имеет комплексные корни вида а+b*i, где a и b − действительные числа, а i − мнимая единица. Таким образом, текст “Действительных корней нет” в формулах, приведенных в

27 Упражнении 10 Лабораторной работы № 2, должен быть заменен выражением для вычисления комплексных корней. При формировании данного выражения следует иметь в виду, что результат должен объединять в себе два числа (a и b) и текст (*i), т.е. потребуется преобразовать действительное число в текст (стандартная функция ФИКСИРОВАННЫЙ), а затем выполнить сцепление двух текстовых строк (стандартная функция СЦЕПИТЬ). Комплексные корни квадратного уравнения находятся по следующим формулам: x1 =

−b + 2a

D *i 2a

и

x2 =

−b − 2a

D *i 2a

,

где величина D=b2-4ac, называемая дискриминантом уравнения определяет, будут ли корни квадратного уравнения действительными (D≥0) или комплексными (D<0). Для упрощения выражения следует ввести в ячейку D1 следующую формулу: =СТЕПЕНЬ(B1;2)-4*A1*C1

Далее в ячейки X1 и X2 таблицы следует ввести соответственно следующие формулы: =ЕСЛИ (D1>=0; (-B1+ КОРЕНЬ(D1))/(2*A1); СЦЕПИТЬ ( ФИКСИРОВАННЫЙ( -B1/(2*A1)); ”+”; ФИКСИРОВАННЫЙ( КОРЕНЬ(ABS(D1))/(2*A1)); ”*i”)) =ЕСЛИ (D1>=0; (-B1-КОРЕНЬ(D1))/(2*A1); СЦЕПИТЬ ( ФИКСИРОВАННЫЙ( -B1/(2*A1)); ”−”; ФИКСИРОВАННЫЙ( КОРЕНЬ(ABS(D1))/(2*A1)); ”*i”))

Внимание! Чтобы уменьшить вероятность появления синтаксических ошибок в формуле, используйте Мастер функций для ввода обращений к стандартным функциям Microsoft Excel. В заключение − ввести в ячейки A1, B1 и C1 различные значения и наблюдать в ячейках X1 и X2 появление значений вещественных или комплексных корней уравнения − в зависимости от знака значения, содержащегося в ячейке D1. Создание пользовательских функций в Microsoft Excel В Microsoft Excel “встроен” язык программирования Visual Basic (Визуальный Бейсик), позволяющий создавать макрокоманды (или макросы), автоматизирующие выполнение различных операций [4, 5]. В частности, Visual Basic позволяет создавать новые функции и помещать их в категорию “Пользовательские”, а затем использовать их в дальнейшей работе c Microsoft Excel. Создадим две новые пользовательские функции − СпецНалог(Сумма), вычисляющую значение специального налога, и НДС(Сумма), вычисляющую значение налога на добавленную стоимость, входящие в состав заданной суммы. Для этого прежде всего создадим новую рабочую книгу и вставим в нее лист-модуль.

28 Лист-модуль создается щелчком правой кнопки мыши на ярлычке рабочего листа, выбором сначала команды Вставка в появившемся контекстном меню, а затем строки Модуль в списке Создать диалогового окна Вставить. В поле листа-модуля необходимо ввести тексты следующих двух функций: Function СпецНалог (sum) СпецНалог = sum * 1,5 / 121,5 End Function Function НДС (sum) sum = sum - sum * 1,5 / 121,5 НДС = sum * 20 / 120 End Function После ввода функций следует нажать клавишу F2. В поле Методы/Свойства появившегося диалогового окна Просмотр Объектов выбрать название функции НДС и нажать кнопку Параметры. В появившемся диалоговом окне Параметры макроса в поле Описание ввести строку “Вычисляет значение налога на добавленную стоимость в составе суммы платежа. Вызов НДС(Сумма)”. В списке Категория функции выбрать строку Пользовательская, если она еще не выбрана. Затем следует закрыть диалоговое окно Параметры Макроса нажатием кнопки OK. Аналогичные действия следует проделать и для функции СпецНалог. Регистрация новых функций для Мастера функций заканчивается закрытием диалогового окна Просмотр Объектов с помощью кнопки OK. Для проверки созданных функций НДС и СпецНалог активизировать чистый рабочий лист текущей книги. Ввести в ячейку A1 строку “Сумма: “, в ячейку С1 − “СпецНалог: ”, в ячейку E1 − “НДС: ”. Ввести в ячейку B1 число 121,5 и нажать клавишу F2. Далее сделать активной ячейкой ячейку D1 и в инструментальном меню нажать кнопку fx для вызова диалогового окна Мастер функций. В списке Категория функции выбрать строку Пользовательские. Затем в списке Имя функции выбрать строку СпецНалог и нажать кнопку Шаг. В поле ввода диалогового окна Мастер функций − шаг 2 из 2 ввести B1 и нажать кнопку Закончить. Наблюдать вывод в ячейке D1 значения 1,5 − величины спецналога для суммы 121,5. Аналогичным образом вставить в ячейку F1 обращение к функции НДС и наблюдать вывод в этой ячейке значения 20 − величины НДС для заданной суммы 121,5. Упражнение 9. Ввести в ячейку B1 значение, отличное от 121,5, и наблюдать вывод в ячейках D1 и F1 значений спецналога и НДС, отличных от первоначальных (см. выше). Упражнение 10. Используя приведенный ниже текст функции СуммаПрописью, создать пользовательскую функцию для вывода указанной

29 денежной суммы прописью, т.е. в виде строки текста. Тестовый пример для этой функции представлен на рис. 14. № п/п Наименование

Ед. изм. Кол-во Цена (руб.) Сумма (руб.)

1 Стол обеденный 2 Табурет

шт. шт.

1 4

450 100 Итого:

Торговая наценка ( 3% ) НДС ( 20% ) Налог с продаж (5%) Всего к оплате

одна тысяча сто три рубля

450 400 850 25,5 175,1 52,53 1103,13

руб. руб. руб. руб.

13 коп.

Рис. 14. Тестовый пример для функции СуммаПрописью '************************************* '*** Вывод денежной суммы прописью *** '************************************* Function СуммаПрописью(Summa) Dim SumString, SumString1 SumString = "" ' Результирующая строка вывода Rubel = False Kopeiki = Summa - Int(Summa) ' Выделим копейки Kopeiki = Int(Kopeiki * 100 + 0.5) ' Округлим значение If Summa >= 10000 Then СуммаПрописью = "Ошибка: превышен предел (9999 руб. 99 коп.)!" Exit Function End If If Summa >= 1000 Then Number = Int(Summa / 1000) ' Сколько тысяч? Remaind = Summa - Number * 1000 Select Case Number Case 1 SumString1 = SumString & "одна тысяча " Case 2 SumString1 = SumString & "две тысячи " Case 3 SumString1 = SumString & "три тысячи " Case 4 SumString1 = SumString & "четыре тысячи " Case 5 SumString1 = SumString & "пять тысяч " Case 6 SumString1 = SumString & "шесть тысяч " Case 7 SumString1 = SumString & "семь тысяч " Case 8 SumString1 = SumString & "восемь тысяч " Case 9 SumString1 = SumString & "девять тысяч " End Select Summa = Remaind SumString = SumString1

30 End If If (Summa >= 100) And (Summa < 1000) Then Number = Int(Summa / 100) ' Сколько сотен? Remaind = Summa - Number * 100 Select Case Number Case 1 SumString1 = SumString & "сто " Case 2 SumString1 = SumString & "двести " Case 3 SumString1 = SumString & "триста " Case 4 SumString1 = SumString & "четыреста " Case 5 SumString1 = SumString & "пятьсот " Case 6 SumString1 = SumString & "шестьсот " Case 7 SumString1 = SumString & "семьсот " Case 8 SumString1 = SumString & "восемьсот " Case 9 SumString1 = SumString & "девятьсот " End Select Summa = Remaind SumString = SumString1 End If If (Summa >= 20) And (Summa < 100) Then Number = Int(Summa / 10) ' Сколько десятков? Remaind = Summa - Number * 10 Select Case Number Case 2 SumString1 = SumString & "двадцать " Case 3 SumString1 = SumString & "тридцать " Case 4 SumString1 = SumString & "сорок " Case 5 SumString1 = SumString & "пятьдесят " Case 6 SumString1 = SumString & "шестьдесят " Case 7 SumString1 = SumString & "семьдесят " Case 8 SumString1 = SumString & "восемьдесят " Case 9 SumString1 = SumString & "девяносто " End Select Summa = Remaind SumString = SumString1 End If If (Summa >= 10) And (Summa < 20) Then Number = Int(Summa / 10) Remaind = Summa - Number * 10 Select Case Number Case 0 SumString1 = SumString & "десять " Case 1

31 SumString1 = SumString & "одиннадцать " Case 2 SumString1 = SumString & "двенадцать " Case 3 SumString1 = SumString & "тринадцать " Case 4 SumString1 = SumString & "четырнадцать " Case 5 SumString1 = SumString & "пятнадцать " Case 6 SumString1 = SumString & "шестнадцать " Case 7 SumString1 = SumString & "семнадцать " Case 8 SumString1 = SumString & "восемнадцать " Case 9 SumString1 = SumString & "девятнадцать " End Select Summa = Remaind SumString = SumString1 & "рублей " Rubel = True End If If (Summa >= 1) And (Summa < 10) Number = Int(Summa) Select Case Number Case 1 SumString1 = SumString Case 2 SumString1 = SumString Case 3 SumString1 = SumString Case 4 SumString1 = SumString Case 5 SumString1 = SumString Case 6 SumString1 = SumString Case 7 SumString1 = SumString Case 8 SumString1 = SumString Case 9 SumString1 = SumString End Select SumString = SumString1 Rubel = True End If

Then

& "один рубль " & "два рубля " & "три рубля " & "четыре рубля " & "пять рублей " & "шесть рублей " & "семь рублей " & "восемь рублей " & "девять рублей "

If (Rubel = False) And (Summa > 1) Then SumString1 = SumString & "рублей " SumString = SumString1 End If If Kopeiki > 0 Then SumString1 = SumString & Str(Kopeiki) & " коп." SumString = SumString1 End If СуммаПрописью = SumString End Function

32 Упражнения для самопроверки 1. Используя Microsoft Excel, вычислить следующие выражения: (a+b)2, если a > 0; a2 + b2, если a < 0; нуль, если a = 0 и занести результат в ячейку С1. 2. Используя Microsoft Excel, вычислить следующие выражения: x2/2, если x < 0; x , если x > 0; 1, если x = 0 и занести результат в ячейку Y1. 3. Модифицировать тексты функций СпецНалог и НДС, приведенные выше, так, чтобы помимо суммы в вызове этих функций можно было задавать величины спецналога и налога на добавленную стоимость (в 4. Модифицировать текст функции СуммаПрописью, приведенный выше, с целью увеличения верхнего предела, обрабатываемого функцией (до 1 млн. рублей).

Лабораторная работа № 4 (2 часа) Тема занятия: Пример выполнения несложных финансово-экономических расчетов с помощью Microsoft Excel: создание таблицы бухгалтерского баланса. Общие сведения о финансовой отчетности предприятий Основным средством представления информации, характеризующей финансово-экономическое положение предприятия, является финансовая отчетность. Согласно требованиям IASC (International Accounting Standards Committee − Международный комитет по стандартам бухгалтерского учета), в финансовые отчеты крупных и средних предприятий должны включаться следующие документы [1]: • балансовый отчет; • отчет о прибылях и убытках; • отчет об изменениях в финансовом состоянии (отчет об источниках и использовании фондов). Кроме того, в настоящее время IASC рекомендует включать в состав отчетной финансовой документации отчет о денежных потоках. Для более полного отражения финансовых результатов деятельности предприятия отчеты должны содержать также показатели предшествующих

33 периодов (как правило, предшествующего года). Финансовые отчеты составляются, по крайней мере, один раз в год. Они подводят итог деятельности предприятия за период в 12 месяцев, который называется календарным (или финансовым) годом. Многие предприятия составляют также промежуточные финансовые отчеты (за полугодие и/или за квартал). Каждый финансовый отчет, независимо от длительности учетного периода, имеет фиксированную структуру и помимо содержательной части должен включать следующую сопроводительную информацию: • название предприятия; • страна регистрации; • дата составления и период, покрываемый отчетными документами; • краткое описание характера деятельности предприятия; • валюта, в которой приведены отчетные показатели; • юридическая форма, в которой осуществляется деятельность предприятия (единоличное владение, товарищество, корпорация). Основным документом финансовой отчетности предприятия является балансовый отчет, дающий детализированное представление активов, обязательств и собственного капитала предприятия в денежном выражении на конкретный момент времени. Балансовый отчет представляет собой таблицу, состоящую из двух равновеликих или уравновешенных (“balance” переводится с французского как “весы”) и взаимосвязанных частей − актива и пассива. Актив показывает состав и размещение хозяйственных средств предприятия, а пассив − источники их образования и их целевое назначение. В активе отражается в стоимостном выражении имущество, которым владеет предприятие на указанную в отчете дату. Для того чтобы это имущество могло считаться активом необходимо выполнение следующих двух условий: 1) оно должно находиться во владении предприятия; 2) оно должно иметь стоимость в денежном выражении. Согласно требованиям IASC активы аккумулируются в двух разделах, один из которых представляет текущие или краткосрочные активы (оборотные средства), другой − долгосрочные или недвижимые активы (основные средства). В раздел “Текущие активы” обычно включаются следующие группы статей: • денежные средства; • ценные бумаги; • дебиторская задолженность; • товарно-материальные запасы; • прочие виды текущих активов. В раздел “Долгосрочные активы” могут включаться: строения, станки и машины, транспортные средства, конторское оборудование, долгосрочные инвестиции, отсроченные налоги и другие.

34 Пассив в балансовом отчете представлен следующими основными группами статей: • краткосрочные обязательства; • долгосрочные обязательства; • собственный капитал. Пассив отражает финансовую структуру предприятия, показывая источники, из которых были получены средства для функционирования предприятия. Краткосрочные (текущие) обязательства выполняются в течение года или текущего отчетного периода. К ним могут быть отнесены статьи, отражающие краткосрочные банковские кредиты, обязательства предприятия по расчетам с поставщиками сырья, продукции и услуг, отчисления на социальное страхование, задолженность по заработной плате и другие. Долгосрочные обязательства могут быть представлены такими статьями, как банковские долгосрочные кредиты (ссуды), выплата по которым производится в течение срока, превышающего один год, задолженность акционерам по дивидендам и другие. Содержание последней группы статей пассива (Собственный капитал) зависит от юридической формы предприятия. В эту группу могут быть включены следующие обязательства: • собственный капитал владельца (владельцев) предприятия; • акционерный капитал (стоимость уставных акций; неоплаченная часть акционерного капитала; права, привилегии и ограничения в отношении выплаты дивидендов; акции, предназначенные для выпуска, и другие); • нераспределенная прибыль (прибыль, реинвестированная в дальнейшее развитие предприятия); • излишки от переоценки активов; • резервы на техническую реконструкцию и другие. Ниже приведен ряд важнейших соотношений, использующихся при составлении балансового отчета [1]. Актив = Пассив Актив = ТАкт + ДАкт ТАкт = Касса +Долг + Запас + Прочие Пассив = КраткО + ДолгО + Капитал где ТАкт − тeкущие активы (оборотные средства); ДАкт − долгосрочные активы (основные средства); Касса − денежные средства и приравненные к ним виды текущих активов (например, ценные бумаги, которыми владеет предприятие); Долг − дебиторская задолженность; Запас − товарные запасы; Прочие − прочие виды текущих активов; КраткО − краткосрочные обязательства; ДолгО − долгосрочные обязательства; Капитал − собственные средства.

35 Создание таблицы-шаблона для бухгалтерского баланса с помощью Microsoft Excel Прежде всего необходимо определить количество граф будущей таблицы. Как следует из информации, представленной выше, балансовый отчет состоит из трех граф: наименования статей отчета, а также данных на начало и конец отчетного периода. Поэтому часть таблицы-шаблона, предназначенного для ввода исходных данных, будет содержать эти три графы. Кроме того, понадобятся еще две графы для вывода данных, отражающих изменения показателей, произошедшие за анализируемый период, в абсолютной и относительной формах. Далее следует определить тип данных, которые будут содержаться в каждой из перечисленных выше пяти граф, и ширину каждой графы. Эту информацию можно представить так, как показано в табл. 5.1. Таблица 5.1. Планировка граф таблицы-шаблона Столбец ЭТ Назначение Тип данных Ширина A Наименование статей отчета Текстовый 30 B Данные на конец года Денежный 12 C Данные на начало года Денежный 12 D Изменение (абсолютное) Денежный 12 E Изменение (относительное) Процентный 10 Затем следует запустить Microsoft Excel и создать “шапку” таблицышаблона. Для этого необходимо в ячейки A1, B1 , C1, D1 и E1 ввести текстовые строки “Активы”, “Предыдущий”, “Текущий” и “Изменение” соответственно, а в ячейки B2, C2, D2 и E2 − текстовые строки “период”, “период”, “(абсолют)” и “(относит)” соответственно. Используя команду Ячейки... меню Формат, определить ширину ячеек и формат данных, которые будут храниться в ячейках таблицы, в учетом сделанных выше допущений (см. табл. 5.1). Далее следует поместить в ряд ячеек таблицы-шаблона формулы для вычисления итоговых значений для каждого из разделов баланса, а также для расчета величин изменения показателей (в абсолютной и относительных формах). Формулы, необходимые для обработки данных баланса (актив), приведены в табл. 5.2. Для формул, требующих пояснения, в таблице указаны номера соответствующих примечаний. Поскольку формулы, помещаемые в ячейки таблицы, однотипны (по крайней мере, в пределах столбцов), объем выполняемых действий можно существенно сократить, если воспользоваться возможностью копирования содержимого из одной ячейки в другую. Копирование содержимого ячейки (блока ячеек) можно выполнить следующим образом. Пометить копируемую ячейку (блок ячеек) и, поместив курсор мыши на помеченную область таблицы, вызвать контекстное меню, щелкнув правой кнопкой мыши. В меню выбрать команду Копировать и,

36 указав курсором мыши ячейку, в которую должна быть скопирована информация, нажать клавишу Enter (можно также выбрать в контекстном меню команду Вставить). Аналогичный результат достигается путем использования соответствующих команд ниспадающего меню, появляющегося при выборе команды Правка в главном меню Microsoft Excel. Таблица 5.2 Формулы, помещаемые в ячейки таблицы-шаблона Ячейка ЭТ Формула Номер прим. D4 =C4-B4 E4 =ЕСЛИ(D4=0;0;ЕСЛИ(B4=0;1;D4/B4)) 1 D5 =C5-B5 2 E5 =ЕСЛИ(D5=0;0;ЕСЛИ(B5=0;1;D5/B5)) 3 B6 =СУММ(B4:B5) C6 =СУММ(C4:C5) 4 D6 =C6-B6 2 E6 =ЕСЛИ(D6=0;0;ЕСЛИ(B6=0;1;D6/B6)) 3 D8 =C8-B8 2 E8 =ЕСЛИ(D8=0;0;ЕСЛИ(B8=0;1;D8/B8)) 3 D9 =C9-B9 2 E9 =ЕСЛИ(D9=0;0;ЕСЛИ(B9=0;1;D9/B9)) 3 D10 =C10-B10 2 E10 =ЕСЛИ(D10=0;0;ЕСЛИ(B10=0;1;D10/B10)) 3 D11 =C11-B11 2 E11 =ЕСЛИ(D11=0;0;ЕСЛИ(B11=0;1;D11/B11)) 3 B12 =СУММ(B8:B10)-B11 C12 =СУММ(C8:C10)-C11 4 D12 =C12-B12 2 E12 =ЕСЛИ(D12=0;0;ЕСЛИ(B12=0;1;D12/B12)) 3 D14 =C14-B14 2 E14 =ЕСЛИ(D14=0;0;ЕСЛИ(B14=0;1;D14/B14)) 3 D15 =C15-B15 2 E15 =ЕСЛИ(D15=0;0;ЕСЛИ(B15=0;1;D15/B15)) 3 D16 =C16-B16 2 E16 =ЕСЛИ(D16=0;0;ЕСЛИ(B16=0;1;D16/B16)) 3 D17 =C17-B17 2 E17 =ЕСЛИ(D17=0;0;ЕСЛИ(B17=0;1;D17/B17)) 3 D18 =C18-B18 2 E18 =ЕСЛИ(D18=0;0;ЕСЛИ(B18=0;1;D18/B18)) 3 B19 =СУММ(B14:B17)-B18 C19 =СУММ(C14:C17)-C18 4 D19 =C19-B19 2 E19 =ЕСЛИ(D19=0;0;ЕСЛИ(B19=0;1;D19/B19)) 3

37 D20 =C20-B20 2 E20 =ЕСЛИ(D20=0;0;ЕСЛИ(B20=0;1;D20/B20)) 3 B21 =B6+B12+B19+B20 5 C21 =C6+C12+C19+C20 4 D21 =C21-B21 2 E21 =ЕСЛИ(D21=0;0;ЕСЛИ(B21=0;1;D21/B21)) 3 D23 =C23-B23 2 E23 =ЕСЛИ(D23=0;0;ЕСЛИ(B23=0;1;D23/B23)) 3 D24 =C24-B24 2 E24 =ЕСЛИ(D24=0;0;ЕСЛИ(B24=0;1;D24/B24)) 3 D25 =C25-B25 2 E25 =ЕСЛИ(D25=0;0;ЕСЛИ(B25=0;1;D25/B25)) 3 D26 =C26-B26 2 E26 =ЕСЛИ(D26=0;0;ЕСЛИ(B26=0;1;D26/B26)) 3 D27 =C27-B27 2 E27 =ЕСЛИ(D27=0;0;ЕСЛИ(B27=0;1;D27/B27)) 3 D28 =C28-B28 2 E28 =ЕСЛИ(D28=0;0;ЕСЛИ(B28=0;1;D28/B28)) 3 B29 =СУММ(B23:B27)-B28 C29 =СУММ(C23:C27)-C28 4 D29 =C29-B29 2 E29 =ЕСЛИ(D29=0;0;ЕСЛИ(B29=0;1;D29/B29)) 3 B30 =B21+B29 5 C30 =C21+C29 4 D30 =C30-B30 2 E30 =ЕСЛИ(D30=0;0;ЕСЛИ(B30=0;1;D30/B30)) 3 Примечания к табл. 5.2: 1. В формуле используется вложенная логическая функция ЕСЛИ, чтобы предотвратить ситуацию деления на 0 при нулевом значении ячейки B4. Относительное изменение анализируемого показателя при нулевом значении ячейки B4 составит 100%. Поскольку для представления данных, помещаемых в ячейки столбца E, принят процентный формат, значение 1 означает в данном случае 100%. Если для данных в ячейке E4 был принят числовой формат, то формула для расчета относительного изменения показателя имела бы следующий вид: =ЕСЛИ(D4=0;0;ЕСЛИ(B4=0;100;(D4/B4)*100)). 2. Данную формулу можно скопировать из ячейки, расположенной выше в столбце D. 3. Данную формулу можно скопировать из ячейки, расположенной выше в столбце E. 4. Данную формулу можно скопировать из ячейки, расположенной слева в столбце B.

38 5. Так как обрабатывается не блок ячеек, а несколько разрозненных ячеек, используются операции сложения (а не стандартная функция СУММ). После того, как ввод формул, представленных в табл. 5.2, завершен, следует заключить ячейки таблицы-шаблона в рамки. Для этого можно либо воспользоваться одним из имеющихся в Microsoft Excel стилей оформления (форматов) таблиц (команда Автоформат... меню Формат), либо выполнить обрамление таблицы “вручную” (кнопка “Линии рамки” инструментального меню). Окончательно таблица-шаблон может иметь вид, как показано ниже (см. табл. 5.3). Упражнение 1. Используя Microsoft Excel, разработать таблицу-шаблон для актива бухгалтерского баланса (см. табл. 5.3). Сохранить созданную таблицу-шаблон на магнитном диске в книге с именем BALANCE (файл BALANCE.XLS). Таблица 5.3 Пример таблицы-шаблона для бухгалтерского баланса (актив) Активы Денежные средства Наличность в банках Наличность в кассе Итого денежные средства Дебиторская задолженность Счета к получению Счета работников Прочие дебиторы Резерв на неплатежи Итого дебиторы Товарные запасы Готовая продукция Незавершенное производство Основные материалы Прочие материалы Резерв на потери Итого товарные запасы Прочие виды активов Всего текущие активы Долгосрочные активы Здания и сооружения Средства производства Инструменты Транспортные средства Офисное оборудование Амортизация Итого долгосрочные

Предыдущий Период

Текущий период

0р.

0р.

Изменение (абсолют) (относит)

0р.

0р. 0р. 0р.

0,00 % 0,00 % 0,00 %

0р.

0р. 0р. 0р. 0р. 0р.

0,00 % 0,00 % 0,00 % 0,00 % 0,00 %

0р. 0р.

0,00 % 0,00 % 0,00 % 0,00 % 0,00 % 0,00 % 0,00 % 0,00 % 0,00 % 0,00 % 0,00 % 0,00 % 0,00 % 0,00 % 0,00 %

0р.

0р.

0р.

0р.

0р. 0р. 0р. 0р. 0р. 0р.

0р.

0р. 0р. 0р. 0р. 0р. 0р. 0р.

0р.

39 активы Всего активы

0р.

0р.

0р.

0,00 %

Упражнение 2. Открыть новую рабочую книгу и произвести загрузку шаблона для обработки данных баланса (см. Упражнение 1). Указать предыдущий и текущий (расчетный) календарный год. Ввести исходные данные баланса (ячейки, содержащие формулы заполнять не следует). Наблюдать автоматическое заполнение ячеек с формулами в процессе ввода данных. Окончательно таблица, содержащая данные актива бухгалтерского баланса, будет иметь вид, подобный тому, что приведен ниже (см. табл. 5.4). Таблица 5.4 Таблица для бухгалтерского баланса (актив) после ввода данных Активы

1995

1996

Изменение (абсолют)

Денежные средства Наличность в банках Наличность в кассе Итого денежные средства Дебиторская задолженность Счета к получению Счета работников Прочие дебиторы Резерв на неплатежи Итого дебиторы Товарные запасы Готовая продукция Незавершенное производство Основные материалы Прочие материалы Резерв на потери Итого товарные запасы Прочие виды активов Всего текущие активы Долгосрочные активы Здания и сооружения Средства производства Инструменты Транспортные средства Офисное оборудование Амортизация Итого долгосрочные активы

10 000 000р. 8 500 000р. 2 500 000р. 3 000 000р. 12 500 000р. 11 500 000р.

(относит)

-1 500 000р. 500 000р. -1 000 000р.

-15,00% 20,00% -8,00%

3500000 2130000 1200000 3000000 3 830 000р.

4250000 2550000 1750000 3500000 5 050 000р.

750 000р. 420 000р. 550 000р. 500 000р. 1 220 000р.

21,43% 19,72% 45,83% 16,67% 31,85%

17500000 5000000

12700000 3750000

-4 800 000р. -1 250 000р.

-27,43% -25,00%

25175000 27500000 1234000 2340000 1750000 1200000 47 159 000р. 45 090 000р. 63 489 000р. 61 640 000р.

2 325 000р. 1 106 000р. -550 000р. -2 069 000р. 0р. -1 849 000р.

9,24% 89,63% -31,43% -4,39% 000 % -2,91%

30000000 30000000 12500000 14500000 5755000 6575000 25000000 27500000 5758000 5758000 460000 722000 78 553 000р. 83 611 000р.

0р. 2 000 000р. 820 000р. 2 500 000р. 0р. 262 000р. 5 058 000р.

000 % 16,00% 14,25% 10,25% 000 % 56,96% 6,44%

40 Всего активы

142042000р. 145251000р.

3 209 000р.

2,26%

Упражнение 3. Используя Microsoft Excel, разработать таблицу-шаблон для пассива бухгалтерского баланса на одном из листов рабочей книги BALANCE. При необходимости заимствовать информацию из [1]. Упражнение 4. Присоединить таблицу-шаблон для пассива к таблице для вычисления актива бухгалтерского баланса в (см. выше Упражнение 2). Ввести необходимые данные для получения полного бухгалтерского баланса. Упражнения для самопроверки 1. Дать определение такому важнейшему понятию бухгалтерского учета как баланс. Что составляет актив бухгалтерского баланса? Пассив? 2. Привести важнейшие соотношения, использующиеся при составлении бухгалтерского баланса. 3. Как скопировать содержимое ячейки (блока ячеек) электронной таблицы? 4. Исследовать результат копирования ячеек, содержащих адреса других ячеек (например, в формулах). Удалось обнаружить что-либо любопытное? Библиографический список 1. Романов А.Н., Лукасевич И.Я., Титоренко Г.А. Компьютеризация финансово-экономического анализа коммерческой деятельности предприятий, корпораций, фирм: Учебное пособие для ВУЗов. − М.: Интерпакс, 1994.− 297 с. 2. Николь Н., Альбрехт Р. Электронные таблицы Excel 5.0 для квалифицированных пользователей: Практическое пособие / Пер. с нем. − М.: ЭКОМ, 1995. − 304 с. 3. Долголаптев В.Г. Работа в Excel 7.0 для Windows 95 на примерах: М.: БИНОМ. − 384 с. 4. Комягин В.Б. Программирование в Excel 5.0 и Excel 7.0 на языке Visual Basic. − M: Радио и связь, 1996. − 319 с. 5. Гарнаев А.Ю. Использование MS Excel и VBA в экономике и финансах. − СПб.: БХВ − Санкт-Петербург, 1999. − 336 c.