Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
Лабораторная работа 6
Табличный процессор Excel-2007 как инструмент для создания простых информационных систем
Цель занятия: 1) ознакомиться с возможностями интегрированного
применения инструментальных средств Excel 2007;
2) ознакомиться с приемами создания макросов;
3) разработать простую информационную систему для заполнения бланка о расходе воды по показаниям водомеров.
MS Excel – известная и широко применяемая инструментальная платформа. Наиболее часто MS Excel используют как табличный процессор, однако помимо вычислений и простых операций с данными в нем существует еще целый ряд возможностей, необходимых для построения информационных систем (ИС) (рис. 1).
Рис. 1. Основные возможности MS Excel
Инструментарий, используемый в MS Excel, включает многие распространенные средства автоматизации, вполне применимые и для ИС, особенно для разработки пользовательского интерфейса и последующей работы с ним. Обладая основными функциями баз данных, MS Excel позволяет создавать простые приложения, где необходимы сортировка и поиск данных в таблице. Средства доступа к внешним базам данных посредством SQL-запросов дают возможность обрабатывать в MS Excel информацию из других приложений, в том числе построенных на базе профессиональных СУБД. Механизм создания макросов и встроенный язык программирования высокого уровня VBA позволяют автоматизировать повторяющиеся действия и разрабатывать собственные приложения, автоматически наделенные всеми достоинствами MS Excel-программ. Развитая система экспорта/импорта данных и связывания с другими приложениями вместе с возможностями VBA позволяют в полной мере использовать инструментарий автоматизации и технологии ActiveX, OLE и COM, в том числе при создании ИС.
Рабочие листы MS Excel позволяют отображать свыше 30 типов простых данных в отдельных ячейках, локальных и взаимосвязанных таблицах, встраивать функции преобразования в виде формул и более 300 готовых функций, а при подключении VBA становятся доступными около 500 типов данных, объектов, ссылок и др.
На листы MS Excel можно внедрять рисунки и объекты из нескольких десятков других приложений. Для связываемых объектов автоматически подключается инструментарий программы-источника. Обмен между работающими приложениями дополняется механизмом экспорта/импорта данных через файлы. MS Excel способен сохранять информацию в файлы более 30-ти типов, при этом данные преобразуются в десятки форматов.
MS Excel предоставляет средства разработки объектно-ориентированных приложений. Понятия об объектах и их параметрах дает рис. 2.
Рис. 2. Объекты – что они включают.
Примечание: в версии Excel-2007 разработчики урезали возможности работы с рисованными объектами (прямоугольниками, овалами, надписями и т.п.), оставив лишь поддержку для таких объектов, созданных в версии Excel-2003. В версии Excel-2007 остается возможность полноценно работать с элементами управления, доступные как объекты Active-X (см. ниже).
Для введения в пользовательскую программу элемента управления оказывается достаточным (рис. 3) выбрать заготовку объекта, задать ему определенные свойства, и описать реакцию объекта на возможные события. Фрагменты диалога с пользователем можно располагать прямо на рабочих листах MS Excel, либо в специальных пользовательских формах VBA.
Рис. 3. Автоматизация разработки фрагментов программ
Такие существенные факторы, как удобство интерфейса и диалоговые возможности программной среды, снижают трудозатраты и повышают качество работы профессиональных разработчиков. В то же время для пользователей-непрограммистов средства автоматизации снижают остроту проблемы взаимодействия с инструментальной средой на пути создания нового программного продукта. Здесь Excel использует Visual Basic for Applications (VBA) – мощный современный язык визуального программирования, овладение которым очень изящно поддерживается в MS Excel встроенным механизмом записи макросов и развитой, частично русифицированной системой помощи и контекстных подсказок. Богатый инструментарий, табличная форма данных, широкие возможности визуализации информации – все это делает MS Excel незаменимым помощником при создании простых информационных систем.
2. Макросы
Макрос – специальная подпрограмма, которую пользователь записывает или разрабатывает для расширения функций того программного комплекса, на котором он работает.
Общее представление о подпрограммах дает рис. 4.
Рис. 4. Подпрограммы – общая структура
В Excel (как и во всем MS Office) макросы записываются на языке Visual Basic for Application (VBA).
Excel предоставляет возможность запомнить цепочку действий пользователя, записав их на языке VBA. Это позволяет :
• Быстро и безошибочно воспроизвести записанную цепочку действий, вызвав макрос для исполнения;
• Прочесть текст макроса, чтобы посмотреть, как Excel записал действия пользователя в макрос на языке VBA .
• Модифицировать записанный макрос.
Записывая в макрос короткую цепочку действий, пользователь не только сможет увидеть саму структуру макроса, но и легко опознать обозначения объектов и их параметров на языке VBA. Более того, в Excel и VBA встроена мощная справочная система, обеспечивающая вывод контекстной справки – достаточно установить курсор на интересующем Вас слове и нажать клавишу F1.
4. ПРАКТИЧЕСКАЯ ЧАСТЬ.
4.1. Макросы – первое знакомство
Для начала работы с макросами достаточно обратиться на ЛЕНТЕ к вкладке ВИД и использовать (самую правую) группу с именем МАКРОСЫ (рис. 5).
Рис. 5. К макросам – через вкладку ВИД.
Для более полноценной работы нам потребуется (чуть позже) вызвать дополнительную вкладку РАЗРАБОТЧИК (см. ниже).
Однако, начнем с простых действий.
4.1.1. Создадим в открытой рабочей книге Excel новый рабочий лист и дадим ему имя ПРОБА (можно переименовать любой свободный лист).
4.1.2. Для определенности установим табличный курсор на ячейку «G20».
4.1.3. Попробуем создать макрос, «попросив» Excel запомнить наши действия. Для этого (см. рис. 6):
Рис. 6. Вход в режим «Запись макроса».
На этой форме есть несколько полей и кнопок, но для нас пока что важно, что Excel предлагает нам записать наши действия в виде макроса с именем «Макрос1», и немедленно приступить к записи наших действий при нажатии кнопки ОК.
Имя «Макрос1» Excel предлагает по умолчанию, и заранее согласен с пользователем, если он даст макросу другое имя (даже на русском языке). Только в тексте имени не должно быть пробелов (их лучше всего заменять символом «_» (нижнего подчеркивания); здесь не допускаются и некоторые запрещенные символы (но даже попытка их использования будет Excel’ом пресечена).
4.1.4. Согласимся с предложением, и нажмем кнопку ОК. Форма, указанная на рис. 6, исчезнет, и теперь Excel начнет «молча» записывать все наши действия в макрос с указанным именем, незримо, но тщательно фиксируя все (даже ошибочные!) манипуляции, пока запись макроса не будет остановлена. Саму остановку записи можно осуществить через вкладку ВИД – группа МАКРОСЫ – строка «Остановить запись», или (что проще) нажав кнопку , которая появляется в левом нижнем углу на строке состояния. (Если навести на нее курсор, всплывет подсказка «Остановить запись»).
Но вернемся к созданию макроса, и пусть все наши действия, которые мы хотим запомнить, состоят в записи какого-то текста в ячейку, которая сейчас выделена табличным курсором (активную ячейку). Итак:
4.1.5. Наберем с клавиатуры текст: «Ура!», завершив ввод нажатием клавиши Enter.
4.1.6. Остановим запись макроса, нажав кнопку .
Что же и в каком виде запомнил Excel? Список всех макросов, имеющихся в открытой книге, можно вызвать по сочетанию клавиш ALT + F8. Давайте, заглянем в этот список, а из него перейдем к просмотру записанного нами макроса (см. рис. 7).
Рис. 7. Вход в режим просмотра и редактирования макроса
4.1.7. Наберем с клавиатуры ALT + F8 и в открывшемся окне выберем действие «Войти».
Заметим, что в списке возможных действий (в левом окне) на первом месте стоит «Выполнить» (нам это еще понадобится), но пока нас интересует, как же устроен наш макрос, и мы выбираем пункт «Войти» или «Изменить», после чего автоматически переходим в окно Visual Basic. В этом окне, как обычно, есть строка заголовка, строка меню, строка (строки) со своими инструментальными линейками, а ниже – два поля: поле VBA-проекта (очень напоминающее по своему виду «Проводник» из ОС Windows) и поле с текстами макросов. Именно последнее поле нас и интересует.
Прежде всего отметим, что напротив имени макроса стоит желтая стрелка, и строка «Sub Макрос1( )» тоже имеет желтый фон. Это не только привлекает наше внимание к исследуемому макросу (что существенно, когда уже записано много макросов), но и сигнализирует, что мы ВОШЛИ В ОСОБЫЙ РЕЖИМ РАБОТЫ, который блокирует многие обычные операции не только в VBA, но и в Excel-программе. Поэтому эту блокировку лучше сразу же снять, нажав кнопку ( Reset) на инструментальной линейке Standard в окне Visual Basic. Желтая стрелка и желтый фон исчезнут, но это никак не помешает нашим исследованиям и дальнейшей работе.
Рассмотрим теперь сам макрос. Он обрамлен строками:
Sub Макрос1 ( )
End Sub
Для англоязычных пользователей легко распознается:
Sub – это сокращение от «The subroutine» (подпрограмма) и End Sub – это «Конец подпрограммы». В первой строке после «Sub» стоит «Макрос1 ( )» – так ведь «Макрос1» – это то имя, которое было дано нашему макросу при его создании, и остается домыслить, что в круглых скобках могли бы стоять параметры, передаваемые макросу – у нас их просто нет, но скобки нужны для соблюдения формы!
Так, с границами разобрались, а что внутри? Три следующие строки начинаются с апострофа и окрашены в зеленый цвет:
'
' Макрос1 Макрос
'
Зеленым цветом в VBA отмечают комментарии (вводимые для лучшего понимания кода программы человеком и игнорируемые VBA при исполнении). Комментарием в VBA считаются все символы от апострофа и до конца текущей строки. Пустые строки комментариев, как нетрудно видеть, зрительно расширяют пространство, структурируя текст макроса и облегчая его восприятие. Заполненные строки комментария обычно несут определенную смысловую нагрузку – в данном случае, об имени и назначении нижележащего текста. (При более глубоком изучении VBA можно увидеть, что в окне с макросами могут появляться описания переменных, дополнительные определения функций и проч.).
Осталось разобрать самую «сердцевину» макроса, его две строки:
ActiveCell.FormulaR1C1 = "Ура!"
Range("G21").Select
Что нам здесь знакомо? Конечно, текст "Ура!" – мы его сами и набирали, а что еще? Для англоязычных пользователей легко распознается:
ActiveCell – «активная ячейка», а затем стоит точка и что-то еще. Здесь следует указать, что для доступа к какому-либо параметру объекта указывают Имя_объекта, а затем – через точку – Имя_параметра, причем в программах на VBA эти имена чаще всего оказываются в английской нотации. Значит, здесь объектом является активная ячейка, а используемым параметром (FormulaR1C1) – формула в формате «R1C1». Не вникая пока в понятие «в формате R1C1», обратимся к строке
Range("G21").Select
Ключевое слово Select легко распознается как команда «Выбрать», но к чему она относится и как вся строка появилась в нашем макросе?
Здесь надо указать, что в VBA есть два способа адресования к ячейкам: по методу Range, и по методу Cells (рис. 8).
Рис. 8. Адресование к ячейке по методу Range и по методу Cells
Метод Range позволяет указать прямо адрес ячейки в привычных обозначениях Excel’а (при более глубоком изучении VBA можно видеть, что этот метод в общем случае позволяет указать диапазон ячеек). В методе Cells аргументами служат два численных выражения (в частном случае - константы, а чаще - переменные). Это делает метод Cells удобным для использования в макросах, в том числе, когда при программировании требуется вычислять адреса ячеек.
Возвращаясь к нашему макросу, мы видим, что он закончился выбором ячейки с адресом ("G21"). Как это произошло? Оказывается, что для того, чтобы закончить ввод нашего победного «Ура!» в ранее выбранной (см. выше п. 4.1.2) ячейке "G20", мы нажали клавишу Enter – и автоматически попали в ячейку с адресом ("G21") – именно это и зафиксировано в последней строке макроса.
Опробуем теперь работу нашего макроса.
4.1.8. Вернуться на рабочий лист «ПРОБА» и установить курсор в другую ячейку, например, «В2».
4.1.9. Наберем с клавиатуры ALT + F8 и в открывшемся окне выберем Макрос1 и действие «Выполнить».
В результате в ячейке «В2» (теперь уже не вручную, а с помощью макроса!) будет занесено «Ура!», а табличный курсор вновь окажется в ячейке с адресом ("G21").
Нетрудно видеть, что Excel вместе с VBA помнят и отслеживают многие нюансы, о которых мы может и забыть или на которые не обращаем внимания. Но теперь, разобравшись в деталях, мы даже можем сообразить, что переход в в ячейку с адресом ("G21") на не очень-то и нужен. Мы можем на свой страх и риск закомментировать последнюю строку макроса и проверить на практике, что при работе макроса «Ура!» будет занесено в активную ячейку, а табличный курсор из нее теперь не будет смещаться!
Вот только уж очень неудобно каждый раз запускать макрос, набирая с клавиатуры ALT + F8, выбирая в открывшемся окне Макрос1 и инициируя действие «Выполнить». Нельзя ли автоматизировать этот процесс? Можно!
4.1.10. Вставим на рабочий лист фигуру «Надпись», и в ее поле напишем «Исполнить!». Щелкнем в стороне по любой ячейке.
4.1.11. Щелкнем по полю фигуры правой клавишей мыши, и в открывшемся контекстном меню выберем пункт «Назначить макрос».
4.1.12. В выпавшем списке укажем макрос «Макрос1», то-есть «привяжем» наш макрос к фигуре. Теперь при подведении курсора мыши к границе фигуры изменение формы курсора укажет нам на возможное изменение режима работы, а щелчок по только что созданной надписи теперь будет инициировать работу макроса. Проверьте это!
Примечание. При сохранении программы с макросами необходимо использовать специальный режим «Сохранить рабочую книгу с макросами», предусмотренный в Excel 2007.
4.2. Запишем макрос сами
Теперь попробуем создать макрос сами, разработав его по образу и подобию «Макроса1». А чтобы осмысленно использовать результат, рассмотрим следующую ситуацию.
4.2.1. На листе ПРОБА занесите в ячейку «F1» текст «Сегодняшняя дата» и выровняйте его по правому краю.
4.2.2. В ячейку «G1» занесите формулу «=СЕГОДНЯ()».
4.2.3. Ячейку «G1» с датой, выведенной туда компьютером, подкрасьте в желтый цвет (просто для привлечения внимания).
4.2.4. С помощью контекстного меню установите для ячейки «G1» формат даты, как показано на рис.9.
Рис. 9. Выбор формата даты, удобного для документов
Обратите внимание, что в ячейке «G1» отображается текст, удобный для документов, а в строке формул – сама формула «=СЕГОДНЯ()».
4.2.5. Попытаемся скопировать содержимое ячейки «G1» в другую ячейку (например, в «G3»), и мы увидим, что и для этой ячейки в строке формул остается «=СЕГОДНЯ()», хотя зрительно мы видим, что в поле таблицы отображается текст сегодняшней даты.
А нельзя ли «вытащить» в другую ячейку сам текст «в чистом виде»? Попробуем воспользоваться Специальной вставкой, для чего:
4.2.6. Скопируем содержимое ячейки «G1» (в Буфер обмена).
4.2.7. Щелкнем правой клавишей по ячейке «G4», в выпавшем контекстном меню выберем пункт «Специальная вставка», а в появившемся окне укажем пункт «Значение» (рис. 10).
Рис. 10. Попытка скопировать значение.
Увы! После нажатия ОК в ячейке «G4» мы увидим, скорее всего, пятизначное число или текст, отличающийся от того, что отображается в ячейке «G1». (Напомним, что все даты в MS Office хранятся в формате вещественных чисел с удвоенной точностью, и целая часть такого числа соответствует целому числу дней, прошедших с 1 января 1900 г.). Да и текст в строке формул не соответствует тому, что отображается в ячейке «G1».
Как же быть? Вот тут нам на помощь и приходит знание того, что ячейка на рабочем листе – это объект, и у этого объекта есть параметр «Текст», а добраться до этого параметра можно через макрос. Вот его-то мы и сконструируем сами!
4.2.8. Наберем с клавиатуры ALT + F8 укажем макрос «Макрос1» и в открывшемся окне укажем макрос «Макрос1» и выберем действие «Войти». Сбросим блокировку от VBA, нажав кнопку ( Reset) на инструментальной линейке Standard в окне Visual Basic. Желтая стрелка и желтый фон исчезнут, и мы попали «в гости» к редактору VBA.
4.2.9. Наберем на свободном поле после макроса «Макрос1» ключевое слово Sub, затем через пробел – имя нашего нового макроса (в самом имени вместо пробелов используем символ нижнего подчеркивания!)
Проба_копирования_даты_в_виде_текста
и нажмем клавишу Enter.
Мы увидим, что поле макроса «Макрос1» автоматически отделится горизонтальной чертой, а редактор VBA любезно завершит наш первый шаг, дополнив строку с именем макроса круглыми скобками (для последующего указания в них параметров макроса – если они будут нужны), введя пустую строку для наших команд и обрамив весь созданный фрагмент строкой окончания макроса End Sub, так что в результате мы увидим:
_________________________________________
Sub Проба_копирования_даты_в_виде_текста()
End Sub
Остается вписать нужную строку, и пусть она должна перенести в ячейку «G2» текст, который отображается в ячейке «G1».
Как обратиться к ячейке «G2»? Проще всего – по методу Range: Range("G2"). А что присвоить этой ячейке? Текст из ячейки «G1», а для этого надо указать ячейку «G1 как объект с требуемым параметром, то-есть Range("G1").Text. В результате весь макрос приобретает вид:
Sub Проба_копирования_даты_в_виде_текста()
Range("G2") = Range("G1").Text
End Sub
4.2.10. Перейдите на рабочий лист Excel, с помощью ALT + F8 вызовите созданный макрос «Проба_копирования_даты_в_виде_текста» и выполните его.
Убедитесь, что информация в строке формул относительно ячейки «G2» совпадает с текстом, отображаемым в самой ячейке.
4.3. Используем метод Cells и знание VBA о положении курсора
Напомним (см. выше рис.8), что при обращении к ячейке по методу Cells аргументами служат два численных выражения (в частном случае - константы, а чаще - переменные). Это делает метод Cells удобным для использования в макросах, в том числе, когда при программировании требуется вычислять адреса ячеек.
Дополнительные возможности предоставляет знание VBA о положении курсора, а ведь он указывает на активную ячейку! Остается «вытащить» эту информацию, а для этого – использовать у активной ячейки ее параметры .Row («Ряд» или номер строки) и .Column («Колонка» или номер столбца).
Сделаем еще одно полезное отступление. Очень хорошо в процессе разработки программы (и ее отладки) визуализировать данные, выводя соответствующие сообщения. В VBA для этого используется оператор MsgBox.
Формат этого оператора:
MsgBox <выражение >
Выполните следующее задание.
4.3.1. Создайте новый макрос:
Sub Проба_знаний_о_курсоре()
i = ActiveCell.Row '№ строки активной ячейки
j = ActiveCell.Column '№ столбца активной ячейки
MsgBox "Вычислим разность показаний"
Cells(i, j + 3) = Cells(i, j) - Cells(i, j - 1)
End Sub
4.3.2. На листе «ПРОБА» заполните несколько ячеек в одном столбце произвольными числами.
4.3.3. Установите курсор на второе число в заполненной области данных.
4.3.4. Вызовите и исполните макрос Проба_знаний_о_курсоре.
4.3.5. Переместите курсор на одну ячейку вниз и повторите п. 4.3.4.
4.3.6. Повторите действия п.п. 4.3.4 и 4.3.5 несколько раз. Объясните результаты.
4.4. Создание простой информационной системы для заполнения бланка о расходе воды по показаниям водомеров
В жизни граждан есть такие действия, как платежи службам ЖКХ. Для их осуществления используются разные формы, в том числе оплата за израсходованную воду по показаниям водомеров, установленных в квартирах граждан. При этом исходные данные предоставляются в Единый информационно-расчетный центр (ЕИРЦ) на заполненных бланках, имеющих вид, показанный на рис. 11.
Рис. 11. Вид бланка
Требуется разработать простую информационную систему (ИС), автоматизирующую с помощью макросов заполнение этого бланка, используя только накапливаемые данные о показаниях водомеров.
4.4.1. Методические указания по разработке ИС
Рис. 12. Бланк с размеченными полями и вспомогательными обрамлениями
Рис. 13. Таблица с исходными данными
4.4.2. Работа с текстовыми полями, входящими в состав элементов управления
4.4.2.1. Прежде всего необходимо вызвать на ЛЕНТУ дополнительную вкладку РАЗРАБОТЧИК (см. рис. 14).
Рис.14. Вызов вкладки РАЗРАБОТЧИК
4.4.2.2. Установить Режим Конструктора (рис.15).
Рис.15. Режим Конструктора
4.4.2.3. Вид действий для ввода текстового поля на рабочий лист Excel показан на рис.16.
Рис.16. Вход в режим рисования поля
4.4.2.4. После щелчка по указанной пиктограмме за указателем мыши потянется перекрестье, Его надо подвести к нужному месту рабочего листа, и удерживая левую клавишу мыши, растянуть прямоугольник до нужного размера. Появившийся объект обладает необходимыми нам параметрами, из которых дя данной задачи важнейшими являются пока Name (Имя поля) и Text (Значение, т.е. отображаемое содержимое поля) – см. рис.17.
Рекомендуется сразу изменить имя, созданное VBA по умолчанию для вновь созданного поля, установив то осмысленное имя, которое необходимо для дальнейшей работы (именно оно и будет использоваться в макросах).
Например, если созданному полю было присвоено имя «Дата_заполнения», то строка в любом макросе меняет значение параметра Text на указанное:
ActiveSheet.Дата_заполнения.Text = "17 ноября 2011 г."
Рис.17. Параметры поля и автоматически созданный макрос (пока пустой), поддерживающий действия при любом изменении содержимого поля (в нашей задаче такой макрос не нужен, зато список параметров слева очень полезен)
4.4.2.5. В списке параметров поля можно найти также параметры, отвечающие за выравнивание текста по полю, размер и тип шрифта, окраску фона и символов и др. (Это может оказаться полезным при полной разработке рассматриваемой задачи).
КОНТРОЛЬНЫЕ ВОПРОСЫ
PAGE 16