Реферат: Задание на лабораторную работу по дисциплине

Задание на лабораторную работу

по дисциплине


«Информационные системы»


для студентов специальности «Прикладная информатика в экономике»


к.э.н., доцент Карпухина Н.Н.


МГУПИ

кафедра ЭФ-2


Содержание



1

Модуль 1. «Общие сведения о VBA в Excel. Макросы»

3 стр.

2

Модуль 2. «Разработка ЭИС средствами VBA в Excel»

18 стр.

3

Задание на домашнюю работу

42 стр.



В результате выполнения лабораторной работы должны быть созданы 3 файла формата .xls:

1. Книга 1.xls с двумя листами: задание 1 и задание 2 модуля 1.

2. БДФИО студента.xls с пятью листами: задания 1-5 модуля 2.

3. Домашняя работа студента ФИО.xls с шестью листами.


^ МОДУЛЬ 1. «ОБЩИЕ СВЕДЕНИЯ О VBA. МАКРОСЫ»


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


^ Visual Basic for Application – это объектно-ориентированный язык программирования высокого уровня, являющийся одним из диалектов очень популярного языка программирования Visual Basic.

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

VBA является встроенным языком программирования приложений MS Office, его можно применить в Excel, Access, Word. Особенностью VBA является то, что при создании приложения за основу берется документ: нельзя создать приложения независимо от документа (рабочей книги в Excel, документа в Word).

VBA предоставляет возможность непосредственной работы с объектами MS Office. При открытии приложения MS Office автоматически активизируется объект Application, определяющий само приложение, и все встроенные в него объекты. Это позволяет эффективно использовать его для автоматизации деятельности, связанной с обработкой различных типов документов, в том числе таблиц Excel.


^ ИСПОЛЬЗОВАНИЕ VBA В MICROSOFT EXCEL

VBA позволяет существенно расширить возможности Excel. С помощью VBA можно легко и быстро создавать различные приложения, даже не являясь специалистом в области программирования. VBA имеет графическую инструментальную среду, позволяющую создавать экранные формы и управляющие элементы. С его помощью можно

создавать свои собственные функции для Excel, вызываемые мастером функций,

разрабатывать макросы,

создавать собственные меню и

многое другое.

Редактор VBA активизируется из Excel одним из следующих способов:

командой меню Сервис⇒Макрос⇒Редактор Visual Basic;

комбинацией клавиш ;

кнопкой панели инструментов Visual Basic .

Вернуться из редактора VBA в приложение можно по комбинации клавиш , по команде View⇒Microsoft Excel или через панель задач.

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


^ СТРУКТУРА ПРИЛОЖЕНИЯ EXCEL

При открытии рабочей книги автоматически активизируется объект Application, определяющий само приложение, и все встроенные в него объекты. Этот объект находится на вершине иерархической структуры. Приложение включает рабочие книги (Workbooks) и различные системы надстройки. В свою очередь, рабочие книги содержат рабочие листы (Sheets) и другие объекты. Наконец, рабочие листы включают самый важный объект – диапазон ячеек (Range).

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


^ РЕДАКТОР VISUAL BASIC APPLICATION

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

Главное окно редактора (рис. 1.1) включает следующие элементы:

окно проекта (Project),

окно формы (UserForm),

окно программного кода (Code),

окно просмотра (Object Browser).




Рис. 1.1. Главное окно редактора Visual Basic


^ СТРУКТУРА ПРОГРАММНОГО КОДА VBA

Программа VBA представляет собой совокупность процедур и функций, размещенных в зависимости от особенностей решаемой задачи в одном или нескольких модулях (это специальные разделы Excel). Добавление модуля осуществляется по команде Insert⇒Module (в этом случае программный код составляется пользователем) или при создании макроса (когда программа создается автоматически). Созданному модулю присваивается стандартное имя Module1, Module2 и т. д.

Каждый модуль отображается в окне, который имеет две области:

общую область и

область подпрограмм.

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

Для удаления модуля следует:

выделить имя модуля,

выбрать команду File⇒Remove Module, щелкнуть в окне диалога на кнопке "Нет".


^ МАКРОСЫ

Макрос – это последовательность команд и действий пользователя, записанная и хранимая внутри документа и исполняемая Excel так же, как пользователь производил эти действия. Последовательность действий пользователем выполняется один раз при записи макроса, затем автоматически производится Excel при каждом запуске макроса.

Большинство команд, которые можно выполнить с использованием меню, клавиатуры или мыши, можно записать в макрос и выполнить их при необходимости. С помощью макроса можно строить числовые ряды, копировать таблицы, выполнять переход на нужные листы, сохранять информацию, работать в режиме ввода и т.д. Макрос записывается в виде процедуры с заданным именем в модуль. Имя макроса может состоять из букв, цифр и символа подчеркивания (_).

Для записи макроса необходимо:

выполнить команду Сервис⇒Макрос⇒Начать запись,

в окне диалога "Запись макроса" задать имя макроса и сочетание клавиш для быстрого выполнения макроса (рис. 1.2),

затем выполнить нужные действия,

после чего закончить запись макроса по команде Сервис⇒Макрос⇒Остановить запись.




Рис. 1.2. Диалоговое окно Запись макроса


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

Рассмотрим создание макроса на конкретном примере.


Задание 1.

Необходимо создать макрос с именем "Ввод", выполняющий переход на Лист1 в открытой рабочей книге, ввод в диапазон ячеек А1:С1 заголовков столбцов, в ячейки А2 и В2 исходных данных, в ячейку С2 – результат перемножения значений ячеек А2 и В2 и возврат на Лист1.

Для записи макроса выполняем следующую последовательность действий:

1. Создаем Книга1.xls.

2. Выбираем команду меню Сервис⇒Макрос⇒Начать запись. Откроется диалоговое окно "Запись макроса".

3. Вводим с клавиатуры имя макроса "Ввод" и задаем сочетание клавиш нажатием на букву "в". В дальнейшем этот макрос будет выполняться при нажатии комбинации клавиш .

4. Щелкаем на кнопке "ОК". Если была активна панель инструментов "Visual Basic", на ней появится кнопка "Остановить запись", говорящая о том, что можно выполнять действия для макроса.

5. Переходим на Лист1, для этого щелкаем на ярлычке Лист1.

6. Выделяем ячейку А1 и вводим слово "Цена".

7. Выделяем ячейку В1 и вводим слово "Количество".

8. Выделяем ячейку С1 и вводим слово "Стоимость".

9. Выделяем ячейку А2 и вводим любое число.

10. Выделяем ячейку В2 и вводим любое число.

11. Выделяем ячейку С2 и вводим формулу =А2*В2. Щелкаем на кнопке "Остановить запись" или выбираем команду Сервис⇒Макрос⇒Остановить запись для завершения записи макроса. В результате наших действий формируется процедура.

Чтобы ее просмотреть, требуется выполнить команду Сервис⇒Макрос⇒Макросы, выбрать в окне диалога макрос "Ввод" и нажать кнопку "Изменить". После этого попадаем в режим редактора Visual Basic, где в специальном окне отображается программный код макроса (рис. 1.3).

Макрос представляет процедуру, начинающуюся с ключевого слова ^ Sub и заканчивающуюся словом End Sub. Первые строки процедуры являются комментариями, они содержат имя макроса, дату записи, автора и комбинацию клавиш.

Далее следуют команды, записанные по правилам VBA. Каждая команда соответствует выполненному действию.

Рассмотрим подробно команды макроса.

Sheets ("Лист1").Select ‘переход на Лист1.

Данная команда состоит из указания на объект Sheets ("Лист1") и метода Select для рабочего листа.




Рис. 1.3. Вид окна программного кода с макросом


Range("A1").Select ‘выделение ячейки А1.

ActiveCell.FormulaRlCl = "Цена" ‘ввод текста в выделенную ячейку.

Команда включает ссылку на активную ячейку, произведенную с помощью свойства ActiveCell, и свойство FormulaR1C1, которому присваивается значение Цена.

Range("B1").Select ‘выделение ячейки В1.

ActiveCell.FormulaRlCl = "Количество" ‘ввод текста в активную ячейку.


Аналогично поступаем с ячейками С1, А2, B2.

В ячейку С2 введем формулу: ^ ActiveCell.FormulaRlCl = "=RC[-2]*RC[-1]"

Sheets("Лист2").Select ‘переход на Лист2.

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

Для запуска макроса в работу имеется несколько способов:

использовать комбинацию клавиши и выбранной буквы.

выполнить команду меню Сервис⇒Макрос⇒Макросы.

в окне диалога "Макрос" указать в списке имя нужного макроса и щелкнуть на кнопке "Выполнить". В окне редактора Visual Basic выбрать в списке процедур имя макроса и выполнить команду Run⇒Sub/UserForm (воспользоваться кнопкой панели инструментов или нажать клавишу ).


^ ФОРМЫ

Для создания диалоговых окон приложений VBA служат пользовательские формы, на которых размещают нужные элементы управления.

Для добавления формы в проект выполняется команда Insert⇒UserForm (в режиме редактора Visual Basic), на экране появится пустое окно редактирования формы и панель элементов (рис. 1.4). Редактор форм является основным инструментом визуального программирования.



Рис. 1.4. Пустое окно редактирования формы и панель элементов


^ ЯЗЫК ПРОГРАММИРОВАНИЯ VBA

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

В состав алфавита также входят пробелы для отделения слов друг от друга и знаки операций (+ - * / ^ = > < ( ) [ ] { } ‘ & @), в том числе составные ( >=, <=, <>).

^ Программный код VBA представляет собой последовательность команд, записанных по правилам языка Basic. Имена переменных, констант, процедур обозначают посредством идентификаторов, учитывая при этом, что имя должно быть содержательным, оно должно начинаться с буквы, не должно содержать точки, пробела, разделительных символов, зарезервированных слов.

^ Зарезервированные слова (As, Public, Then, All и др.) выводятся синим цветом.

Примеры правильных имен: strMyName, strФамилия.

Примеры неправильных имен: 2Week, Second.Week.


^ Каждая команда записывается в отдельной строке по определенному правилу.

В VBA принято команду называть оператором, правило записи команды – синтаксисом или форматом.


Операторы VBA имеют следующие форматы:

Объект.Свойство = Значение

Объект.Метод [Параметр1],[Параметр2]...,


где:

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

Например, ссылка Application.ActiveCell или просто ActiveCell – это обращение к активной ячейке. При работе с одной рабочей книгой объект WorkBook также можно опустить.

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

Значение – новое состояние объекта.

Метод – команда или процедура.

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

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

Если в командной строке нарушен синтаксис или сама команда неверна, она выделяется красным цветом.

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


Примеры:

Sheets("Лист1").Select – оператор выделения рабочего листа, где Sheets("Лuст1") – объект, Select – метод.

Sheets("Лист1").Range("A1").Value = Sheets ("Выпуск продукции").Range("D5") – оператор, присваивающий ячейке Лист1!А1 значение из ячейки Выпуск продукции!D5, Value – свойство.


^ ТИПЫ ДАННЫХ

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

Для числовых данных обычно применяют типы

Integer (целочисленные значения в диапазоне от -32768 до +32767),

Currency для хранения чисел с четырьмя знаками после десятичной точки,

Single – для вещественных чисел одинарной точности.


Для текстовых данных используется строковый тип String (содержит любые символы, строчные данные заключаются в кавычки).


^ ОБЪЯВЛЕНИЕ ПЕРЕМЕННЫХ

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


^ Перед использованием переменной в программе ее желательно объявить с помощью оператора Dim.

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

текстовая переменная имеет тип String,

переменная целого числа – Integer,

дата – Date.

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


Синтаксис:

Dim ИмяПеременной1 [As Тип], ИмяПеременной2 [As Тип]


где

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

ИмяПеременной – идентификатор, определяющий допустимое имя;

As – ключевое слово для определения типа.


При определении переменной после ввода ключевого слова As появится список-подсказка для выбора нужного типа.


Примеры:

Dim tovar As String ‘текстовая переменная,

Dim Cost As Integer ‘переменная типа целое число.


^ АДРЕСАЦИЯ ЯЧЕЕК В EXCEL

В Excel ячейка, строка, столбец и диапазон ячеек рассматриваются как один объект Range.

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

Range – свойство, определяющее диапазон ячеек;

Cells – свойство, позволяющее выбирать ячейки рабочего листа;

ActiveCell – свойство, возвращающее одну активную ячейку;

Selection – метод, возвращающий выделенный объект.


Для доступа к диапазону ячеек чаще всего используются свойства Range и Cells.


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

Формат А1 (обычный). Ссылка состоит из имени столбца и номера строки. Например, D4. Ссылка может быть относительной (D4) и абсолютной ($D$4). Ссылка может указывать диапазон ячеек. Полный адрес диапазона может включать имя рабочего листа.

^ Формат R1C1. В данном формате R задает номер строки, С – номер столбца, ссылка является абсолютной. Для указания относительной ссылки задается смещение по отношению к активной ячейке. Смещение указывается в квадратных скобках, знак указывает направление смещения. Например, R[-2]C – ссылка на ячейку, расположенную на 2 строки выше в том же столбце. R[2]C[3] – ссылка на ячейку, расположенную на 2 строки ниже и 3 столбца правее.


Ссылка на одиночную ячейку имеет вид:

[Объект.] Range ("адрес ячейки"). Для свойства Range в качестве аргумента используется любая ссылка в формате А1.

Например, WorkSheets("Лист1").Range("A7")=34, или для текущего листа Range("A7")=34 – запись в ячейку А7 числа 34.

[Объект.] Cells(<номер строки>, <номер столбца>). Свойство используется для доступа к отдельной ячейке. В качестве аргументов указываются номер строки и столбца. Например, для ввода числа 34 в ячейку Лист1!А7 необходима команда: Sheets(1).Cells(7, 1).Value=34.


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

Пример. Нужно поместить число 3500 в ячейку Прейскурант!D3. Для этого напишем процедуру с неявным объявлением переменных:

‘i – для номера строки и j – для номера столбца.

Sub Ввод()

i=3

j=4

Sheets("Прейскурант").Сеlls(i, j). Value=3500

End Sub

^ Для указания диапазона можно использовать разные способы.

Например,

для диапазона A1:D7 применимы ссылки: Range(Cells(1, 1), Cells(7, 4)) или Range("A1:D7"),

для отдельно стоящих ячеек можно задать перечисление: Range("A1, B2, C3, D4") или Range("A2:C4, D4:D8").

^ ОПЕРАТОРЫ, ВЫРАЖЕНИЯ И ОПЕРАЦИИ

Операторы в VBA являются основными элементами кода.

Последовательность операторов образует процедуру.

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

Различают простые операторы, записываемые в одну строку, и сложные, называемые "структурами" (операторы условия и циклов).


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

^ Назначение любого выражения – получение некоторого значения. Это значение является результатом и используется командой.

Ко1>15 – логическое выражение,

Str*31/100 – арифметическое выражение.


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

Например,

Fam = "Крючков"

lntl=6

StrName=Range("D4")


^ ОПЕРАТОРЫ ВЫБОРА

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

В VBA есть два основных оператора выбора:

операторы условия If ... Then;

переключатели Select Case.


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

Синтаксическая конструкция этого оператора может иметь две формы:

безальтернативную: If условие Then выражение;

альтернативную: If условие Then выражение_1 Else выражение_2.


Если условие принимает значение True (Истина), то выполняется выражение 1, если False (Ложь), то выражение 2. Ветвь Else является необязательной. Например, если скидка в 5% применяется только к суммам >1000, то в VBA это можно записать так:

If Money > 1000 Then Discont = 0.05 Else Discont = 0

или, что равносильно

If Money > 1000 Then Discont = 0.05


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

If Money > 1000 Then

Discont = 0.05

End if

Else

Discont = 0


Если дерево условий гораздо сложнее, чем просто проверка одного условия, используется оператор ^ If ... Then ... Elseif, который позволяет проверять несколько условий:

If условие 1 Then

<группа операторов 1>

Elseif условие 2 Then

<группа операторов 2>

Else

<группа операторов n>

End if

Первым проверяется условие 1. Если оно имеет значение True, то выполняется группа операторов 1 и далее работа программы продолжится со строки, которая следует за ключевым словом End if. Если условие 1 возвращает значение False, то проверяется условие 2 и т.д., пока не встретится условие, возвращающее значение True, или не встретится ключевое слово Else.

Если нужно проверить достаточно много условий, то оператор If ... Then может стать слишком сложным. В этом случае применяется оператор выбора, или переключатель Select Case, который выполняет одну из нескольких групп операторов в зависимости от значения выражения.


^ ОПЕРАТОРЫ ЦИКЛОВ

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

В Visual Basic существуют два основных типа циклов:

циклы со счетчиком,

циклы с условием.

Циклы со счетчиком (с известным числом повторений) называют еще циклами For ... Next. Синтаксис цикла со счетчиком таков:


For Счетчик цикла = НачальноеЗначение То КонечноеЗначение [Step (Шаг)]

Операторы: [Exit For], Next [Счетчик цикла].

где

Step (Шаг) – число, задающее шаг цикла, т.е. значение, на которое увеличивается или уменьшается значение счетчика на каждом шаге. Это число может быть отрицательным. Если ключевое слово Step отсутствует, то значение шага равно 1.

Exit For – оператор досрочного выхода из цикла (необязательный).


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

Программный код реализации этой задачи может выглядеть так:

Sub sum()

Dim f As Integer, sum As Integer ‘объявляются переменные типа целое число

sum = 0

For f=2 То 50 Step 2 ‘переменной, являющейся счетчиком, присваивается начальное и конечное значение

sum = sum + f ‘переменная счетчик увеличивается на величину шага Step 2 и проверяется на достижение конечного значения

^ Next f ‘команда передает управление команде For

Range("A1").Value = sum ‘вычисленная сумма помещается в ячейку А1 активного листа

End Sub


Главной особенностью циклов с условием является условие, которое может быть любым выражением, принимающим значение Истина (True) или Ложь (False).

В Visual Basic есть два основных цикла с условием

цикл Do While ... Loop,

цикл ^ Do Until ... Loop.

Оба они могут быть с предусловием или с постусловием.


Циклы Do While ... Loop и Do Until ... Loop имеют следующий синтаксис:

^ Цикл с предусловием:

Do While/Until Выражение

Операторы

[Exit Do]

Loop

Цикл с постусловием:

Do

Операторы

[Exit Do]

Loop While/Until Выражение


В первом случае условие задается в операторе начала цикла, во втором – в операторе конца цикла.

DO, LOOP – ключевые слова, обозначающие начало и окончание цикла;

^ WHILE и UNTIL – ключевые слова, определяющие тип цикла. Цикл DO WHILE выполняется до тех пор, пока условие имеет значение True (т.е. пока условие выполняется). Цикл DO UNTIL выполняется до тех пор, пока условие имеет значение False.

^ EXIT DO – принудительный выход из цикла.


Задание 2.

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


Для реализации этого примера:

1. Создайте новый файл под именем «Штат сотрудников.xls». На Лист 1 создайте таблицу с данными приведенными в ячейках A1:H7 (рис. 1.5).




Рис. 1.5. Данные исходной таблицы "Штат сотрудников"


2. На Лист 1 в режиме редактора Visual Basic необходимо поместить программу, подсчитывающую число сотрудников с высшим образованием и суммирующую их годовой доход.

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

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


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

^ Public Sub Доходы_ВО()

Dim k As Integer, s As Long ‘объявляется переменная k целого типа для подсчета сотрудников, переменная s (длинное целое число) для суммирования доходов

i = 3 ‘неявно объявляем переменную для номера строки

s = 0 ‘обнуляем первоначальную сумму

k = 0 ‘присваиваем первоначальное значение переменной, считающей сотрудников

^ Do While Cells(i, 2) <> "" ‘выполняем цикл, пока не встретится пустая ячейка в столбце В (т.е. конец списка фамилий: <> ""означает не равно пустой ячейке).

If Cells(i, 6) = "высшее" Then ‘проверяем условие

s = s + Cells(i, 8) ‘суммируем доходы при выполнении условия

k = k + 1 ‘подсчитываем число сотрудников при выполнении условия

^ End If ‘окончание блока оператора If

i = i + 1 ‘увеличиваем переменную смещения строк для изменения адресов ячеек

Loop 'окончание цикла

Range("I1") = "Сумма доходов сотрудников с ВО" ‘вводим в ячейку I1 поясняющую надпись

Range("I2") = s ‘присваиваем вычисленную сумму ячейке I2

Range("J1") = "Количество сотрудников с ВО" ‘вводим в ячейку J1 поясняющую надпись

Range("J2") = k ‘присваиваем результат счета ячейке J2

End Sub


Перенесите данную процедуру в редактор Visual Basic на Лист 1 (рис. 1.6).



Рис. 1.6. Фрагмент программного кода


3. Для запуска данной программы нажмите на кнопку запустить макрос на панели инструментов.

Результат выполнения программы представлен на рис. 1.7.



Рис. 1.7. Результат выполнения программы (макроса) представлен в ячейках I1:J2


4. Для быстрого и удобного запуска программ и макросов на рабочем листе можно поместить кнопку (или любой графический объект). Назначенные кнопке программа или макрос в этом случае запускаются автоматически при щелчке кнопки или графического объекта. Для добавления кнопки на лист необходимо вывести на экран панель инструментов "Формы", нажать на этой панели элемент управления "Кнопка", с помощью мыши придать элементу управления на листе нужный размер и в диалоговом окне "Назначить макрос объекту" выбрать нужный макрос или программу в поле Имя макроса. При добавлении другого элемента управления щелкните его правой кнопкой мыши и выберите команду " Назначить макрос в контекстном меню ".

Для этого выполните следующую команду: Сервис⇒Настройка⇒Вкладка Команды⇒Элемент управления⇒Кнопка. Мышью вынесете элемент управления Кнопка на панель инструментов на верх. Щелкните правой кнопкой мыши по кнопке и из раскрывающегося списка выберете Назначить макрос



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

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


^ МОДУЛЬ 2. «РАЗРАБОТКА ЭИС СРЕДСТВАМИ VBA В EXCEL»


Цель работы: получить некоторые навыки программирования на Visual Basic for Application.

В ходе работы рассматриваются следующие вопросы:

1. создание кнопочного интерфейса.

2. создание БД.

3. создание пользовательской формы для заполнения БД.

4. заполнение шаблона документа при помощи форм.

5. математические вычисления.

6. создание диаграмм для анализа.


В итоге выполнения работы файл с ЭИС должен содержать 5 листов:

Интерфейс,

Склад,

Клиенты,

Счет,

Диаграмма по продажам.


ЗАДАНИЕ 1. СОЗДАНИЕ ИНТЕРФЕЙСА

1.1. Создадим новый файл в Excel и сохраним его как БДФИО студента.xls.

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

Переименуем листы:

Интерфейс

Склад

Клиенты

Счет


1.2.Создание кнопочного интерфейса


На листе «Интерфейс» создадим 3 кнопки. Для этого необходимо сделать активной панель инструментов «Элементы управления» (рис. 2.1). Данная панель будет активной, если выполнить следующие действия: в меню Вид выберите пункт Панель инструментов, а затем команду – Элементы управления . На экране появиться панель инструментов «Элементы управления», содержащая элемент управления – Кнопка.



Рис. 2.1. Панель инструментов «Элементы управления»

Переходим далее в режим Конструктор. Для этого на панели «Элементы управления» щелкнем на значок .

После этого щелкните на элемент управления Кнопка левой кнопкой мыши и, перетаскивая указатель мыши, нарисуйте рамку кнопки на рабочем листе. В дальнейшем размер кнопки и ее расположение можно будет изменить. На рабочем листе появилась Кнопка с названием Command Button1. Далее подведите к ней курсор мыши и щелкните правую кнопку. Из раскрывающегося списка выберите Объект Command Button→Edit. Теперь имеется возможность поменять название кнопки. Сотрите старое название и введите новое название – ПросмотрБД-Склад. Данную кнопку будем использовать для того, чтобы при нажатии на нее автоматически открывался лист Склад.

Поменяем цвет кнопки и размер шрифта. Для этого подведите к ней курсор мыши и щелкните правую кнопку. Из раскрывающегося списка выберите Свойства. На экране появится окно Properties (Свойства) (рис. 2.2). Измените следующие свойства:

BackColor (Цвет кнопки): бледно розовый;

Font (Шрифт): Courier New, полужирный, 12 размер.



Рис. 2.2. Окно Properties


Далее два раза мышкой нажимаем на кнопку ПросмотрБД-Склад в режиме конструктор или на кнопку на панели инструментов . Откроется окно редактора Visual Basic. Оно имеет вид (рис. 2.3):



Рис. 2.3. Окно редактора Visual Basic


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


Как определялось выше, при нажатии на кнопку «ПросмотрБД-Склад» на листе Интерфейс должен автоматически открываться лист Склад с таблицей Товаров (таблицу создадим позже). Для этого в проекте Visual Basic в объектах на листе 1 должна быть следующая команда


'Кнопка ПросмотрБД-Склад открывает Список

^ Private Sub CommandButton1_Click()

Лист2.Activate

End Sub


Данная процедура означает следующее:

Private Sub – начало подпрограммы;

CommandButton1_Click() – при нажатии на кнопку CommandButton1 на экране открывается Лист2 – Лист2.Activate;

End Sub – конец подпрограммы.


Таким образом Окна редактора кода Лист1 должно содержать следующую команду (рис. 2.4):




Рис. 2.4. Окна редактора кода Лист1


Вторая и четвертая строка появляются автоматически при первом нажатие на кнопку (в примере на кнопку «Просмотр БД-Склад»). Первая строка служит пояснением, поэтому начинать ее следует с ‘. Третья строка определяет, что при нажатие на кнопку1 будет активен Лист2. В Окне проекта видно, что Лист2 – лист Склад.


1.3. Самостоятельно на листе Интерфейс создайте кнопку ПросмотрБД-Клиенты (CommandButton2), при нажатии которой будет открываться лист Клиенты (Лист3). Кнопка ПросмотрБД-Клиенты создается аналогично кнопке ПросмотрБД-Склад.


Еще на листе Интерфейс создадим кнопку «Выход» (CommandButton3). В Окне редактора кода Лист1 необходимо ввести следующую команду:


‘ Кнопка Выход

^ Private Sub CommandButton3_Click()

Workbooks.Close

End Sub


Дадим пояснение. Workbooks.Close означает закрыть рабочую книгу (Workbooks).


Сохраните изменения в файле (нажмите на кнопку сохранить на панели инструментов или воспользуйтесь командой Файл⇒Сохранить) .


^ 1.4. Фон интерфейса

Выделите всю видимую на экране монитора часть листа Интерфейса (ячейки таблицы) и примените к ним цвет заливки ячеек «Желтый» (меню Формат, Ячейки, вкладка Вид).

Введите следующий текст: Техно+. Примените форматирование ячеек: шрифт Courier, начертание полужирный, размер 48 и расположите правее на верху листа.

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




Рис. 2.5. Внешний вид Лист Интерфейс


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


ЗАДАНИЕ 2. ЗАПОЛНЕНИЕ БД ИНФОРМАЦИЕЙ


^ 2.1. Заполнение БД Склад

На листе Склад создайте следующую таблицу (таблица 2.1):


Таблица 2.1. БД Склад

Категория товара

Марка

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

Цена (руб.)

Остаток

НАСТОЛЬНЫЕ КОМПЬЮТЕРЫ

ASUS

Компьютер ASUS Eee Box B202

14500,00

5

^ НАСТОЛЬНЫЕ КОМПЬЮТЕРЫ

ASUS

Компьютер ASUS Eee Box B202

12200,00

10

^ НАСТОЛЬНЫЕ КОМПЬЮТЕРЫ

TC «Офис»

Компьютер TC «Офис» 30208

9525,00

15

^ НАСТОЛЬНЫЕ КОМПЬЮТЕРЫ

TC «Офис»

Компьютер TC «Офис» 30209

15595,00

7

^ НАСТОЛЬНЫЕ КОМПЬЮТЕРЫ

TC «Престиж»

Компьютер TC «Престиж» 30122

39141,00

12

^ НАСТОЛЬНЫЕ КОМПЬЮТЕРЫ

TC «Студент»

Компьютер TC «Студент» 30125

20619,00

6

^ НАСТОЛЬНЫЕ КОМПЬЮТЕРЫ

TC «Студент»

Компьютер TC «Студент» 30127

26525,00

8

^ НАСТОЛЬНЫЕ КОМПЬЮТЕРЫ

TC «Студент»

Компьютер TC «Студент» 3015

24072,00

9

^ НАСТОЛЬНЫЕ КОМПЬЮТЕРЫ

TC «Студент»

Компьютер TC «Студент» 30154

31912,00

7

^ НАСТОЛЬНЫЕ КОМПЬЮТЕРЫ

TC «Студент»

Компьютер TC «Студент» 30436

25661,00

19

НОУТБУКИ

Acer

Ноутбук Acer Aspire 6920G-6A4G25Mi

47800,00

25

НОУТБУКИ

ASUS

Ноутбук ASUS X58L

21610,00

12

НОУТБУКИ

Dell

Ноутбук Dell 500

16900,00

15

НОУТБУКИ

Dell

Ноутбук Dell Studio 1535

31650,00

21

НОУТБУКИ

HP

Ноутбук HP Compaq 6735b

33470,00

20

НОУТБУКИ

HP

Ноутбук HP Compaq Presario C791ER

21428,75

14

НОУТБУКИ

HP

Ноутбук HP Pavilion dv4-1050er

43394,64

17

НОУТБУКИ

HP

Ноутбук HP Pavilion dv5-1030er

38524,44

16

НОУТБУКИ

Samsung

Ноутбук Samsung R410-XB02

19740,00

10

НОУТБУКИ

Samsung

Ноутбук Samsung R510-XA02

18700,00

7

НОУТБУКИ

Samsung

Ноутбук Samsung R610-FS03

30710,00

2

^ LCD МОНИТОРЫ

Acer

Монитор LCD 19" Acer AL1917Ns

6180,00

1

LCD МОНИТОРЫ

Acer

Монитор LCD 19" Acer V193WAb

5380,00

5

^ LCD МОНИТОРЫ

Samsung

Монитор LCD 19" Samsung T190 TWHSU2

7980,00

6

LCD МОНИТОРЫ

Acer

Монитор LCD 20" Acer V203WAb

6210,00

9

^ LCD МОНИТОРЫ

Samsung

Монитор LCD 20" Samsung T200GN TWUSU черный+бордовый

8720,00

10

^ LCD МОНИТОРЫ

Samsung

Монитор LCD 20" Samsung T200GN TWUSV черный+синий

8720,00

15

^ LCD МОНИТОРЫ

Samsung

Монитор LCD 22" Samsung T220 TWHSU2 черный+бордовый

10990,00

2

^ LCD МОНИТОРЫ

Samsung

Монитор LCD 24" Samsung T240N TWASU черный+бордовый

14500,00

3

^ Лазерные МФУ

Canon

Canon i-SENSYS MF4018

7050,00

5

Лазерные МФУ

Canon

Canon i-SENSYS MF4120

9685,00

5

^ Лазерные МФУ

Samsung

Samsung SCX-4200