Текст книги "Разработка учетных приложений в MS Office"

И. Х. Бикмухаметов, З. Ф. Исхаков, М. Ю. Лехмус
Разработка учетных приложений в MS Office

© Бикмухаметов И. Х., Исхаков З. Ф., Лехмус М. Ю., 2018

© Издательство «Прометей», 2018

* * *

Введение

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

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

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

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

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

Учебное пособие может быть использовано при изучении дисциплины «Разработка учетных приложений в среде MS Office» и рекомендовано всем желающим получить практические навыки работы в офисном пакете с элементами автоматизации своих приложений с использованием макросов и VBA.

1. Учетные приложения и автоматизация хозяйственной деятельности

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

Под «приложением» принято называть программный продукт, разработанный для выполнения конкретных задач в выбранной программной среде, содержащий конкретный алгоритм реализации и программный код. Часто можно услышать Windows-приложение, DOS-приложение, офисное приложение и т. д. Обыкновенная электронная таблица еще не является приложением. Необходимо действие, которое должно выполнять приложение, характеризующее приложение как приложение – иначе это просто документ. Другое дело, когда электронная таблица содержит в своих ячейках формулы или функции листа. В этом случае можно говорить об активной электронной таблице, которая производить различные вычисления, да к тому же автоматически корректирует красочную диаграмму на листе в зависимости от изменившихся исходных данных в ячейках таблицы. А если еще с данными электронной таблицей работают с помощью кода VBA и используют элементы управления, тогда свойства электронной таблицы приближаются к исполняемому файлу приложения.

Приведем отличия учетного приложения от простой электронной таблицы:

1. Учет – это динамический процесс, поэтому таблица постоянно меняет свой размер в зависимости от занесенных в нее данных.

2. Если необходимым условием для приложения является проведение каких-либо действий с данными таблицы, то для электронной таблицы это только возможное действие.

3. Данных действий должно быть более одного, т. е. отличительное свойство приложения это различная, контекстная работа с набором данных. Пользователь сам выбирает, какое действие необходимо выполнить с табличным набором данных в данный момент времени.

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

Основные теоретические вопросы разработки учетных, а точнее учетно-расчетных приложений рассмотрены в учебном пособии Лукьянова П. Б. [1]. Каждое учетно-расчетное приложение имеет большую расчетную составляющую в виде различных блоков анализа данных в базе, прогнозирования, и возможно элементов какого-то подхода к оптимизации. Его уже нельзя назвать просто учетным приложением. Это скорее расчетное приложение с таблицей исходных данных. Расчетный блок учетного приложения, прежде всего, зависит от предметной области использования данного приложения: экономика, менеджмент, образование, наука и др. Следовательно, у каждого учетного приложения он будет свой.

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

Для реализации учетного приложения «родной», инструментальной средой разработки, безусловно, является табличный процессор, хотя учетные и расчетные функции можно реализовать и в других офисных приложениях. При этом продукт, созданный в среде СУБД, правильно называть базой данных, хотя его также можно назвать и учетным приложением.

При разработке учетного приложения может возникнуть вопрос: какую среду использовать – среду электронных таблиц или базы данных, а может даже документа MS Word или комбинацию приложений? Все зависит от решаемой задачи и от объема исходных данных, с которыми предстоит работать. При этом не следует исключать из рассмотрения навыки пользователя по владению каким-либо офисным приложением. Опыт общения с различными структурами организации показывает, что выбор инструментария определяет, прежде всего, знания исходного приложения – инструментария. И часто это приложение бывает не лучшим для поставленной задачи. Поэтому можно сделать вывод, что для учетных приложений, когда объем информации небольшой (не более 200 записей), ограничены время и средства на разработку – лучше всего подходит среда электронных таблиц. При больших значения перечисленных показателей следует обратить на базы данных.

2. Базовые составные части учетного приложения

Независимо от выбранной среды работы учетные приложения могут иметь явно выраженные составные части:

1) Интерфейсная часть для работы с приложением в целом.

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

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

4) База (регистр) для хранения введенных данных в диалоговую форму.

5) Бланк формуляра-образца, шаблон текстового документа или другой стандартный, заранее подготовленный шаблон документа, в которые должны быть занесены данные в нужных местах. Обычно для поиска таких документов в сети Интернет в поисковую систему вбивают «Бланки документов» или обращаются к справочно-правовым системам, например, «КонсультантПлюс» и «Гарант».

6) Справочная (инструкция) система для работы с приложением.

Бланк формуляра-образца – это то, с чего начинается построение учетного приложения. Таблицы для хранения информации должны как минимум иметь такие же поля, как в бланке формуляра-образца. В качестве примера бланка формуляра-образца можно привести форму счета-фактуры.

3. Особенности использования MS Office для разработки учетных приложений

Использование программного комплекса MS Office для разработки учетных приложений имеет большую привлекательность и возможности. Это, прежде всего, связано с тем, что данный пакет имеется практически на всех персональных компьютерах организаций и граждан России.

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

В составе программного комплекса MS Office присутствуют программные приложения, специально ориентированные для работы с массивами данных в табличной форме. Это табличный процессор MS Excel и система управления базой данных (СУБД) MS Access. Данные приложения обладают достаточным функционалом для создания учетных приложений любой сложности. Более того, эти приложения специально созданы для учета и обработки больших массивов данных, что характерно для учетных приложений.

Другой важной чертой пакета MS Office является организация удобного обмена данных между объектами приложения и возможность управления ими средствами VBA.

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

Небольшие трудности возникли у поклонников продуктов фирмы Microsoft при появлении очередной версии пакета MS Office 2007. Внешний вид органов управления приложением и документами существенно изменились по сравнению с предыдущими версиями пакета. Измененный интерфейс программ пакета первоначально вызывает определенные трудности у пользователя. Это связано с привыканием пользователей к традиционному интерфейсу приложений, которое сформировалось в результате их многолетнего использования. Выход в данной ситуации только один: привыкнуть к новому веянию разработчика пакета.

4. Использование средств контроля данных при создании учетных приложений

Для разработки удобного в пользовании учетного приложения следует использовать различные элементы автоматизации процессов. Автоматизация, прежде всего, необходима для безошибочного ввода и переноса данных. Часто для этого используют контроль введенных данных. При работе в среде MS Excel имеются инструменты для выполнения данных функций (рис. 1, рис. 2, рис. 3):

Рис. 1. Позиция меню для контроля ввода данных

Рис. 2. Окно диалога для настройки параметров контроля введенных данных

Рис. 3. Параметры для ввода действительных чисел в заданном интервале

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

Рис. 4. Окно предупреждения о неправильном значении введенного числа

Для контроля уже введенных данных следует воспользоваться позицией меню «Обвести неверные данные», то результат будет выглядеть вот так (рис. 5):

Рис. 5. Неверные данные обведены овалом после применения позиции меню «Обвести неверные данные»

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

Следующим удобным методом контроля введенных и расчетных значений является условное форматирование ячеек с данными. Когда значения данных в ячейки выходят за пределы отведенного диапазона, то формат ячейки становится отличным от остальных ячеек листа. Для того, чтобы воспользоваться этим инструментом необходимо на вкладке «Главная» выбрать группу «Стили» и позицию меню «Условное форматирование» (рис. 6):

Рис. 6. Позиция меню условного форматирования ячеек листа

Введем правило выделения ячейки при превышении значения 500 (рис. 7, рис. 8):

Рис. 7. Позиции меню для отбора и ввода значений

Рис. 8. Диалоговое окно контроля введенного значения больше 500 и результата форматирования

Одним из способов безошибочного ввода данных является использование информации из заранее подготовленных электронных таблиц – справочных таблиц. Здесь следует отметить, что доступ к данным этих справочных таблиц может быть реализован различными способами:

1) Если необходимо просто вводить в ячейку информацию из определенного списка, то лучше всего для этой цели подойдет форматирование ячейки на проверку вводимых данных. Этот подход наиболее эффективен для заполнения ячейки списком постоянных данных. Для этого выберем позиции меню «Данные →Проверка данных →Проверка данных». Появится диалоговое окно «Проверка вводимых данных», в котором необходимо выбрать позицию «Список» (рис. 9, рис. 10):

Рис. 9. Позиция «Список» в диалоговом окне «Проверка вводимых данных»

Рис. 10. Диалоговое окно для указания источника заполнения списка

В поле «Источник» необходимо указать диапазон ячеек, в которых находится справочная информация. Разумеется, данные справочные таблицы должны быть на листе за пределами видимости экрана. К сожалению, данный подход не позволяет хранить справочную информацию на другом листе книги (рис. 11).

Рис. 11. Рабочая ячейка, окно диалога и справочный диапазон ячеек

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

Рис. 12. Рабочая ячейка с возможностью ввода данных из списка и исходная справочная таблица значений.

2) Использование справочных таблиц на листах книги совместно с функциями листа ВПР, ПРОСМОТР.

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

4.1. Функция лист ВПР для работы со ссылками и массивами

Используя функцию ВПР, вы фактически даете такую команду: «Вот значение. Нужно перейти в другое место, найти такое же значение и показать слова или числа в ячейке, соответствующей ему». Чтобы было проще, третье значение (номер столбца) можно рассматривать как результат поиска. Первые три аргумента функции ВПР являются обязательными. Последний аргумент необязателен, однако если его опустить, по умолчанию ему будет присвоено значение ИСТИНА[1]. Пример использования данной функции представлен на рис. 13. На листе «Протокол» содержится электронная таблица протокола проведения экзамена, а на листе «Исходные данные» – информация о студентах группы в соответствии со справочной таблицей (рис. 14).

Рис. 13. Таблица на листе «Протокол» с выделенной ячейкой, информацией в строке формул и отображенным результатом

Функция ВПР, введенная в ячейку I3, имеет следующую структуру заполнения:

=ВПР(Протокол! H12;’Исходные данные’!$C$7:$E$12;2;ЛОЖЬ).

Работу этой функции для ячейки I3 листа «Протокол» можно изобразить следующей последовательностью:

Протокол! H12→Исходные данные! С7→Исходные данные! D7

Особенностью функции ВПР является то, что ключевые данные должны находиться в крайнем левом столбце таблицы-справочника (рис. 14):

Рис. 14. Таблица-справочник номеров зачетных книжек студентов группы

4.2. Функция лист ПРОСМОТР для работы со ссылками и массивами

Функция ПРОСМОТР очень похожа на функцию ВПР. Различие заключается в том, что функция ВПР ищет соответствие в первом столбце, а функция ПРОСМОТР ищет в соответствии с размерностями массива. Это означает, что столбцом, в котором находятся ключевые значения, может быть любым из таблицы-справки, и соответственно, результатная информация также может быть выбрана из любого столбца по ключу. Ниже приведены заполненный бланк исходных данных функции ПРОСМОТР (рис. 15) и результат работы функции для Михайлова Михаила Михайловича (рис. 16).

Рис. 15. Бланк исходных данных функции ПРОСМОТР для ячейки I4

Рис. 16. Формула в ячейке I4 и результат работы функции ПРОСМОТР

4.3. Использование элементов управления на формах листа

При создании приложений с удобным интерфейсом в среде Excel не обойтись без использования элементов управления, которые являются основой любого окна диалога Windows-приложения. Данные элементы размещены едино на панели «Элементы управления» в меню «Разработчик» (рис. 17):

Рис. 17. Панель «Элементы управления» на вкладке «Разработчик»

Если вкладка «Разработчик» отсутствует на ленте, то для его вызова в меня пользователя следует в настройках табличного процессора выделить флажок «Показать вкладку «Разработчик» на ленте (рис. 18, рис. 19):

Рис. 18. Позиция меню для настройки параметров табличного процессора

Рис. 19. Выставление флажка для отображения вкладки «Разработчик» на ленте

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

• элементы управления формы;

• элементы ActiveX.

Главное отличие элементов управления формы от второй группы заключается в том, что пользователю предоставляется возможность работы над удобным интерфейсом приложения практически без знаний языка программирования VBA. Для использования элементов управления необходимо воспользоваться вкладкой Разработчик^Элементы управления^Вставить.

Эта вкладка содержит следующие элементы:

1) Подпись.

2) Группа элементов.

3) Кнопка.

4) Флажок.

5) Переключатель.

6) Список.

7) Поле со списком.

8) Полоса прокрутки.

9) Счетчик.

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

Элемент управления «Подпись»

В отличие от информации, занесенной в ячейки таблицы Excel, элемент «Подпись» (рис. 20) позволяет создавать надписи в любом месте рабочего листа, а не только в ячейках.

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

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

Элемент управления «Кнопка»

Наиболее распространенным элементом управления является элемент «Кнопка» (рис. 21). Он служит для запуска на выполнение программного кода при наступлении с ним какого-либо события.

Для создания кнопки необходимо выбрать данный элемент на панели инструментов. Маркер мыши примет вид маленького крестика. Этим крестиком следует указать местоположение левого верхнего угла будущего элемента управления на листе книги и далее, нажав левую клавишу, растянуть вниз и вправо кнопку до нужного размера. На кнопке возможно нанесение надписи, выражающие назначение кнопки. Поэтому при создании надписи требуется указать истинное назначение кнопки, а не абстрактные наименования (рис. 21):

Рис. 21. Элемент «Кнопка» на листе

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

Рис. 22. Окно диалога свойств элемента управления «Кнопка»

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

Для того чтобы настроить кнопки, необходимо выделить кнопку и, нажав правую клавишу мыши, выбрать позиции контекстного меню «Формат объекта…».

Макросы кнопкам можно присваивать как во время их создания, так и после редакции внешнего вида кнопки (рис. 23):

Рис. 23. Назначение макроса кнопке

Для отмены присвоения макроса кнопке во время ее создания необходимо нажать клавишу клавиатуры «ESC» или кнопку «Отмена» окна диалога.

Следует иметь в виду, что элемент управления по желанию может выводиться или не выводиться на печать. Для выбора варианта распечатки необходимо установить флажок в позиции «Выводить на печать» во вкладке «Свойства» позиции контекстного меню «Формат объекта…» (рис. 24):

Рис. 24. Вкладка настрой свойств элемента управления

Это относится ко всем рассматриваемым далее элементам управления.