Порядок выполнения работы




Содержание

Создание макросов Excel и взаимодействие Excel и VBA.. 5

Лабораторная работа №1. Разработка пользовательской таблицы средствами процессора Excel, создание и выполнение макросов Excel 5

1. Методические указания. 5

2. Задание. 9

3. Порядок выполнения работы.. 11

4. Порядок оформления отчета. 13

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

Лабораторная работа №2. Отладка и выполнение программы в среде VBA.. 14

1. Методические указания. 14

2. Задание. 16

3. Порядок выполнения работы.. 17

4. Порядок оформления отчета. 18

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

Лабораторная работа №3. Обмен данными между Excel и VBA.. 19

1. Методические указания. 19

2. Задание. 29

3. Порядок выполнения работы.. 29

4. Порядок оформления отчета. 30

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

Программирование на VBA.. 31

Лабораторная работа №4. Операции и операторы VBA.. 31

1. Методические указания. 31

2. Задание. 46

3. Порядок выполнения работы.. 47

4. Порядок оформления отчета. 47

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

Лабораторная работа №5. Функции и процедуры. Создание пользовательской функции Excel 48

1. Методические указания. 48

2. Задание. 56

3. Порядок выполнения работы.. 56

4. Порядок оформления отчета. 58

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

Лабораторная работа №6. Классы и объекты.. 59

1. Методические указания. 59

2. Задание. 67

3. Порядок выполнения работы.. 67

4. Порядок оформления отчета. 68

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

Лабораторная работа №7. Базовые операторы ввода-вывода VBA и работа с файлами.. 69

1. Методические указания. 69

2. Задание. 74

3. Порядок выполнения работы.. 74

4. Порядок оформления отчета. 76

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

Лабораторная работа №8. Ввод с клавиатуры и вывод на экран в VBA.. 76

1. Методические указания. 76

2. Задание. 79

3. Порядок выполнения работы.. 80

4. Порядок оформления отчета. 81

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

Принципы программирования на VBA применительно к другим программам пакета Microsoft Office.. 83

Лабораторная работа №9. Особенности работы совместно с Microsoft Word. 83

1. Методические указания. 83

2. Задание. 86

3. Порядок выполнения работы.. 86

4. Порядок оформления отчета. 86

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

Рекомендуемая литература.. 88

Предметный указатель.. 89

 

Создание макросов Excel и взаимодействие Excel и VBA

Лабораторная работа №1. Разработка пользовательской таблицы средствами процессора Excel, создание и выполнение макросов Excel

Методические указания

Мы предполагаем, что у вас уже есть начальные знания по работе с табличным процессором Excel. Мы считаем, что вы знакомы с понятиями рабочей книги, рабочего листа Excel и что вы умеете ими пользоваться (создавать, удалять переименовывать, вставлять и т.п.). Мы исходим из того, что вы знакомы с относительным и абсолютным способами адресации ячеек рабочего листа Excel, умеете задавать и осознанно выбирать формат ячейки, знакомы со способами ее оформления (шрифт, фон, рамки). Мы считаем, что вы умеете программировать формулы в Excel и пользоваться встроенными функциями Excel. Наконец, мы предполагаем, что вы в состоянии придумать собственную пользовательскую таблицу, данные в которой организованы по строкам и столбцам, имеют вполне определенный практический смысл и требуют некой обработки, в частности, вычислений. Вы также в состоянии набрать ее на рабочем листе и задать форматы ячеек ФОРМАТ, Ячейки…, в том числе тип данных (вкладка Число), выполнить Выравнивание в ячейке, задать Шрифт, сделать обрамление ячейки (вкладка Граница), сделать заливку ячеек (вкладка Вид).

Термином макрос обычно называют файл, хранящий последовательность действий, заданных пользователем системы. Каждый макрос должен иметь собственное имя. С помощью макроса можно автоматизировать типовые технологические этапы при работе с системой. Если макрос создан, то после его запуска хранящаяся в нем последовательность действий (команд) будет автоматически исполнена. По своей сути макрос представляет собой программу и может быть создан автоматически в специальном режиме работы программной системы (в том числе и Excel) или как результат программирования в терминах языка системы. Если пользователь владеет языком задания макроса, то созданный любым способом макрос может быть подвергнут редактированию с целью изменения его возможностей или устранения ошибок. В пакете Microsoft Office таким языком является язык VBA.

При работе с Excel, как, впрочем, и с другими программами пакета Microsoft Office, для создания макроса легче всего использовать автоматический режим его создания, вызываемый из главного меню системы командами СЕРВИС, Макрос. При первоначальном запуске системы макросы отсутствуют, поэтому диалоговое окно <<Макрос>>, вызываемое пунктом Макросы… показывает пустой список. Пункт меню Безопасность… открывает дополнительное меню, позволяющее задавать уровень безопасности при использовании макросов. Известен ряд компьютерных вирусов, маскирующихся под макросы, в связи с чем разработчиками Excel предпринят ряд дополнительных мер защиты. Так, например, может быть задан высокий, средний и низкий уровни безопасности при работе с макросами (по умолчанию средний и рекомендуемый уровень безопасности). Если он используется, то при загрузке файла с диска система попросит разрешение на подключение макросов к программе. Если такое разрешение будет дано, то макросбудет доступен в загружаемой таблице. Пункты меню Редактор Visual Basic и Редактор сценариев вызывают соответствующие программы (они должны быть установлены на компьютер отдельно с инсталляционных дискет и подключены к операционной системе).

Если в меню СЕРВИС, Макрос выбрать пункт Начать запись…, то откроется диалоговое окно, позволяющее задать имя макроса и, при желании, комбинацию клавиш, с помощью которой он также может вызван в обход пункта меню Макросы…. По умолчанию система предлагает стандартное имя Макрос#. Во избежание недоразумений старайтесь задавать собственные имена макросов, отличные от стандартных. Начиная с этого момента все действия с рабочей книгой дополнительно записываются в файл макроса. Остановить запись макроса можно кнопкой Остановить запись дополнительно открывшейся панели инструментов или через аналогичный пункт главное меню СЕРВИС, Макрос. Записанный макросможет быть сохранен в текущей рабочей книге и тогда он доступен в ней и других книгах в том случае, когда она открыта или в личной книге макросов. В последнем случае он может быть доступен в любой открытой книге.

Удалить макрос, созданный в текущей рабочей книге, можно кнопкой <Удалить> диалогового окна <<Макросы>>. Если макрос создан в личной книге макросов, то для его удаления потребуются более сложные действия, о которых будет рассказано позднее. Поэтому старайтесь в первое время не пользоваться макросами личной книги.

Если макроссоздан в личной книге макросов, то для его удаления необходимо запустить Редактор Visual Basic. В запустившейся оболочке надо открыть окно проектов командами VIEW, Project Explorer (если оно не открылось автоматически). После этого надо раскрыть содержимое проекта VBAProject (PERSONAL.XLS) и раскрыть ветвь Modules. В ответ на эти действия откроется список модулей проекта. Активируя каждый модуль двойным щелчком, просматривается его содержимое в окне редактора VBA. После того, как интересующий макрос найден, его текст выделяется в окне и удаляется. При необходимости можно удалить весь модуль, щелкнув его правой клавишей мышки, и воспользовавшись пунктом открывшегося меню, например, <Remove Module1>.

Необходимо принять во внимание существование двух возможных типов записи ссылок на ячейки в Excel: A1 и R1C1. По умолчанию при программировании формул используется стиль A1, для которого адрес каждой ячейки представляет собой строку символов, содержащую имя столбца и номер строки. Использование этого стиля позволяют организовать относительную и абсолютную адресацию к ячейкам таблицы (за счет введения в строку символа $). Тем не менее, при записи макросов Excel использует тип ссылки R1C1. В обозначении типа присутствуют первые буквы английских слов Row (строка) и Column (колонка). В первую очередь обратите внимание на то, что, в отличие от типа A1, при использовании типа ссылок R1C1 сначала записывается строка, а потом столбец. При использовании абсолютной адресации после символов R и C указывается собственно номер строки и столбца. Так, например, ячейка $B$3 имеет адрес R3C2. При использовании относительной адресации в стиле R1C1 после обозначения строки или колонки в квадратных скобках указывается смещение по отношению к текущей ячейке. Так, например, если данные находятся в ячейке B3, а ссылка на нее программируется в ячейке А5, то в формуле она запишется как R[-2]C[1]. Эта запись может интерпретироваться как обращение к ячейке, находящейся на две строки выше и одну колонку правее текущей. Соответственно запись R[2]C[-1] означает обращение к ячейке на две строки ниже и одну колонку левее (по отношению к активной ячейке A5 такая ячейка не существует).

Пример 1. Рассмотрим таблицу, показанную на рис. 1. В ней необходимо рассчитать сумму подоходного налога (с учетом используемой ставки налога), сумму к выдаче для каждого сотрудника, а также общие суммы уплачиваемых налогов и выплаченной заработной платы. Записывался макросс именем Расчет_заработной_платы. Текст макроса имеет вид:

Sub Расчет_заработной_платы()

' Расчет _заработной_платы Макрос

' Макрос записан 01.12.2005 (Администратор)

Range("C2").Select

ActiveCell.FormulaR1C1 = "=RC[-1]*R7C3"

Range("D2").Select

ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]"

Range("C2:D2").Select

Selection.AutoFill Destination:=Range("C2:D5"), Type:=xlFillDefault

Range("C6").Select

ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"

Range("D6").Select

ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"

End Sub

Рис. 1. Пример таблицы

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

Первый исполняемый оператор программы Range("C2").Select создан системой в виде выражения, которое содержит в терминологии VBA свойство Range в сочетании с методом Select. Обратите внимание на то, что свойство имеет записанный в круглых скобках аргумент в виде строки символов и отделяется от метода точкой. В нашем примере аргумент свойства представляет собой ссылку на ячейку в стиле A1, с которой началось программирование макроса.

С помощью Help-системы разберитесь с назначением свойства Range. Для этого установите в окне модуля маркер на текст Range и нажмите клавишу F1. Если вы испытываете затруднения с чтением текста на английском языке, который используется Help-системой, воспользуйтесь дополнительной русскоязычной литературой, посвященной описанию языка VBA. В этом случае удобно составлять собственное описание встречающихся англоязычных терминов и хранить его в удобном месте (например, в виде отдельного файла Excel).

Аналогично изучите назначение метода Select.

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

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

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

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

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

Задание

Согласуйте с преподавателем выбранный вами вариант задания (табл. 1). Предполагается, что в рамках одной учебной группы варианты заданий не повторяются. Разработайте и заполните таблицу и запрограммируйте в ней необходимые вычисления. При необходимости воспользуйтесь функциями. Убедитесь в правильности вычислений. Оформите таблицу, задайте шрифты, границы и т.п. В качестве примера будет рассматриваться таблица, предназначенная для расчета налогов и определения суммы заработной платы.

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

Таблица 1. Варианты заданий для выполнения лабораторной работы

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

Порядок выполнения работы

1. Создайте новую рабочую книгу Excel. Сделайте ее настройку:

§ выполните команду СЕРВИС, Параметры и в диалоговом окне выберите вкладку Общие, установив следующие параметры:

Стиль ссылокR1C1: выключено.

Листов в новой книге: 3.

Стандартный шрифт: Arial Cyr, размер 10.

Выберите рабочий каталог для сохранения новых книг.

Введите имя пользователя.

§ выберите вкладку Вид, установив флажки следующих параметров:

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

Примечания: только индикатор.

Объекты: отображать.

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

§ выберите вкладку Вычисления, установив флажки следующих параметров:

Вычисления: автоматически.

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

2. Переименуйте рабочий лист, выполнив следующие действия:

§ установите указатель мыши на вкладку с именем листа (Лист 1) и вызовите контекстное меню, щелкнув правой клавишей мыши;

§ выберите в текстовом меню параметр Переименовать;

§ введите в диалоговом меню новое имя листа, придуманное вами.

3. Сохраните созданную рабочую книгу с новым, придуманным вами именем, выполнив команду ФАЙЛ, Сохранить как…

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

5. Задайте наименования полей головки таблицы. При необходимости укажите в них единицы измерения.

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

7. Скопируйте созданную таблицу на новый рабочий лист. Удалите в ней все формулы.

8. В меню СЕРВИС, Макрос выберите пункт Начать запись…. Задайте имя макроса.

9. Повторно запрограммируйте формулы таблицы Excel и остановите запись макроса.

10. Командой Сервис, Макрос, Редактор Visual Basic запустите редактор Visual Basic. В окне проектов (Project-VBAProject) (рис. 2) раскройте содержимое проекта VBAProject (PERSONAL.XLS) и ветвь Modules. В ее составе должен быть один (например, Module1) или несколько модулей. Дважды щелкните левой клавишей мышки по имени модуля. В ответ в правом верхнем окне должен появится его текст. Просмотрите содержимое модулей и найдите записанный вами макрос.

11. Изучите текст макроса.

12. Удалите формулы из таблицы рабочего листа Excel и выполните макроскомандой Сервис, Макрос, Макросы. Убедитесь, что в результате его работы содержимое таблицы восстанавливается.

13. Снова удалите формулы из таблицы рабочего листа Excel. Перейдите в окно VBA, установите маркер на первом операторе макроса. Выберите пункт Run, Run Sub/User Form и запустите модуль на выполнение. Перейдите в таблицу Excel и убедитесь, что в результате работы макроса формулы в ней восстановились.

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



Поделиться:




Поиск по сайту

©2015-2024 poisk-ru.ru
Все права принадлежать их авторам. Данный сайт не претендует на авторства, а предоставляет бесплатное использование.
Дата создания страницы: 2017-04-01 Нарушение авторских прав и Нарушение персональных данных


Поиск по сайту: