close

Вход

Забыли?

вход по аккаунту

?

507.Инструментальные средства обработки информации (Word, Excel) [Электронный ресурс] курс лекций для студентов, обучающихся по направлению 09.03.03 (230700.62) Прикладная информатика в менеджменте С.В. Мистюкова . (1 файл 1236 Кб) . , 2014

код для вставкиСкачать
Министерство сельского хозяйства Российской Федерации
Федеральное государственное бюджетное образовательное
учреждение высшего профессионального образования
«Воронежский государственный аграрный университет
имени императора Петра I»
С.В. Мистюкова
ИНСТРУМЕНТАЛЬНЫЕ СРЕДСТВА
ОБРАБОТКИ ИНФОРМАЦИИ (WORD, EXCEL)
КУРС ЛЕКЦИЙ
для студентов, обучающихся по направлению
09.03.03 (230700.62) Прикладная информатика в менеджменте
Воронеж
2014
Печатается в соответствии с решением методической комиссии гуманитарно-правового
факультета ФГБОУ ВПО Воронежский ГАУ (протокол № 4 от 22 января 2014 г.).
УДК 007
ББК 32.81
М 57
Мистюкова С.В.
Инструментальные средства обработки информации: Курс лекций /
М 57
С.В. Мистюкова – Воронеж: ФГБОУ ВПО Воронежский ГАУ, 2014. – 66 с.
Табл. 4. Ил. 19
Издание предназначено для студентов, обучающихся по направлению
09.03.03 (230700.62) Прикладная информатика в менеджменте
Рецензенты:
Смагин Б.И., доктор экономических наук, профессор, заведующий кафедрой математического моделирования экономических систем Мичуринского государственного
аграрного университета;
Горланов С.А., кандидат экономических наук, доцент, заведующий кафедрой
экономики АПК Воронежского государственного аграрного университета имени
императора Петра I.
 С.В. Мистюкова, 2014
 ФГБОУ ВПО Воронежский ГАУ, 2014
2
Содержание
1 ИСПОЛЬЗОВАНИЕ ТЕКСТОВОГО ПРОЦЕССОРА MS WORD
ДЛЯ ОБРАБОТКИ ИНФОРМАЦИИ ......................................................... 4
1.1 ОБЩИЕ СВЕДЕНИЯ О СИСТЕМАХ ПОДГОТОВКИ ТЕКСТОВЫХ ДОКУМЕНТОВ .... 4
1.2 ПРИЕМЫ И СРЕДСТВА АВТОМАТИЗАЦИИ РАЗРАБОТКИ ДОКУМЕНТОВ............. 8
1.2.1
Макросы ........................................................................................ 8
1.2.2
Шаблоны ..................................................................................... 18
1.3 АВТОМАТИЗАЦИЯ КОМПЛЕКСНЫХ ТЕКСТОВЫХ ДОКУМЕНТОВ .................... 22
2 ИСПОЛЬЗОВАНИЕ ТАБЛИЧНОГО ПРОЦЕССОРА MS EXCEL
ДЛЯ ОБРАБОТКИ ИНФОРМАЦИИ ....................................................... 25
2.1 ОСНОВЫ VBA В MS EXCEL ........................................................................ 27
2.2 МЕТОДЫ АНАЛИЗА ДАННЫХ В MS EXCEL ................................................... 39
2.2.1
Средства анализа «что-если» ................................................... 39
2.2.2
Показ тенденций изменения данных с помощью спарклайнов
(инфокривых) ............................................................................................ 44
2.2.3
Анализ данных в Microsoft PowerPivot....................................... 46
2.3 РАБОТА С ЭЛЕКТРОННЫМИ ТАБЛИЦАМИ В РЕЖИМЕ БАЗ ДАННЫХ ................ 49
3 ТЕСТОВЫЕ ЗАДАНИЯ......................................................................... 59
СПИСОК ЛИТЕРАТУРЫ .......................................................................... 65
3
1 ИСПОЛЬЗОВАНИЕ ТЕКСТОВОГО ПРОЦЕССОРА
MS WORD ДЛЯ ОБРАБОТКИ ИНФОРМАЦИИ
1.1 Общие сведения о системах подготовки текстовых документов
Обработка текстов как направление развития техники возникло в
начале XX в. с появлением механической пишущей машинки. Затем более
полувека пишущая машинка оставалась единственным общедоступным
средством получения печатного текста на бумаге. Очевидно, что при
печатании на пишущей машинке наиболее трудоемким является процесс
внесения изменений в текст.
С появлением в 80-е гг. персональных компьютеров положение
кардинально изменилось. Подготовка документов, внесение в них
исправлений стали гораздо проще. Но для того, чтобы на компьютере
можно было набирать текст, на нем должны быть установлены
специальные программы. Вначале эти программы были довольно
простыми и позволяли только набирать тексты и вносить в них изменения.
В настоящее время существует множество программ для работы с
текстом. Ориентированных на разные области применения. В зависимости
от функциональных возможностей этих программ различают редакторы
для обработки документов общего вида, для создания научных
документов,
встроенные
редакторы
и
редакторы
систем
программирования. Впрочем. Такое разделение является во многом
условным, так как некоторые функции программ различных групп
дублируются, и постоянно появляются новые версии с более
совершенными и сложными процедурами обработки, что размывает
границы названных групп.
Наиболее важной характеристикой текстового редактора является
область профессиональной деятельности. В которой он обеспечивает
максимальные удобства для пользователя.
Редактор текстов (text editor) обеспечивает ввод, изменение и
сохранение любого символьного текста, но предназначен он в основном
для подготовки текстов программ, поскольку тексты программ не требуют
форматирования. Результатом работы текстового редактора становится
файл, в котором все знаки являются знаками кода ASCII. Такие файлы
4
называются ASCII-файлами. Использование для подготовки и печати
документа редактора текстов на качественном уровне соответствует
использованию пишущей машинки. Производительность в данном случае
обеспечивается легкостью получения большого числа печатных копий с
хранимой в электронной памяти заготовкой и возможностью, как
исправления опечаток, так и частичной переработки текста путем вставки
или исключения новых фрагментов. Одним из текстовых редакторов
является редактор Блокнот.
Встроенные редакторы отличает простота и определенный
примитивизм. Термин «встроенные» означает, что эти редакторы не
существуют в виде самостоятельных программ, а входят в качестве одной
из сотавляющих в более сложные программные системы.
Широко известен встроенный редактор файлового менеджера Total
Commander. Его можно использовать при наборе текста программ, для
оперативного внесения небольших изменений в тексты, ранее созданные
более мощными редакторами. Эти редакторы позволяют работать только с
ASCII-файлами.
Total
Commander
обладает
лишь
простейшими
возможностями по набору текста, не обеспечивая возможностей
форматирования. Тем не менее, в нем существуют возможность
перемещения курсора не слово влево и вправо, в начало и в конец файла,
удаления слова, части строки, целой строки. К дополнительным функциям
редактора можно отнести возможность поиска заданной подстроки
символов.
Редакторы систем программирования сущетвуют в виде отдельной
программы, но слабо поддерживают такие структуры текстовых
документов, как строка, абзац, страница, имеют ограниченные
возможности подготовки текста к печати. Вместе с тем они позволяют
набирать длинные строки и облегчают форматирование типовых структур
операторов алгоритмического языка. Наибольшие применение при
неписании программ получили так называемые Турбо-системы –
интегрированные
средства
для
создания,
компиляции,
отладки
и
выполнения программ.
Редакторы для создания научных документов имеют специальные
5
средства для набора математических, химических и других сложных
формул, содержащих надстрочные и подстрочные индексы нескольких
уровней, специальные знаки и т.д. С помощью таких программ типа TeX/
MathWord можно быстро и легко подготовить статью. Отчет, любой
другой научный текст.
Настольные издательские системы – это программы, специально
разработанные для профессиональной издательской деятельности,
наиболее известные из них Adobe inDesing, Ms Publisher. Они позволяют
осуществлять электронныу верстку и готовить оригинал-макеты книг,
брошюр, газет и других изданий. Тиражируемых на полиграфи6еском
оборудовании высокого класса.
Предусмотренные в таких программах средства позволяют
компоновать (верстать) текст и графику, использовать самые
разнообразные шрифты, корректирвать иллюстрации, подготовленные в
графических редакторах, создавать цифрове оригинал-макеты для вывода
на пленку на устройствах высокого разрешения.
Редакторы для обработки документов общего вида (текстовые
процессоры) позволяют эффективно работать с такими структурами как
слово, строка, предложение, абзац. Страница и т.д. Они предоставляют
широкие
возможности
форматирования
текста,
использования
разнообразных шрифтов, заголовков, нумерации страниц, вывода готового
документа на печать.
Текстовые процессоры – это общее название программных средств,
предназначенных для создания и обработки текстов. В отличие от
текстовых редакторов, позволяющих только набирать и исправлять
(редактировать) текст, текстовые процессоры имеют специальные
дополнительные функции, которые предназначены для облегчения ввода
текста и представления его в напечатанном виде. Среди этих функций
можно выделить следующие:
 ввод
текста
под
контролем
функций
форматирования,
обеспечивающих точное соответствие экранного образа документа его
печатной копии. Этот принцип называется WYSIWYG (What You See Is
What You Get – что Вы видите, то и получите);
6
 предварительное описание структуры будущего документа с
помощью специального языка; в этом описании задаются такие параметры,
как величина абзацных отступов, тип и размер шрифта для различных
элементов текста, расположение заголовков, межстрочные интервалы,
число колонок текста, расположение и способ нумерации сносок (в конце
текста или на той же странице) и т.д.;
 автоматическая проверка орфографии и получение подсказки при
выборе синонимов;
 ввод и редактирование таблиц и формул с изображением их на
экране в том виде, в котором они будут напечатаны;
 объединение документов в процессе подготовки текста к печати;
 автоматическое
составление
оглавления
и
алфавитного
справочника;
 возможность совместной работы над одним документом
нескольких соавторов с учетом исправлений, внесенных каждым из них.
Почти все текстовые процессоры имеют уникальную структуру
данных для представления текста, что объясняется необходимостью
включения в текст дополнительной информации, описывающей структуру
документа, шрифты и т.п., поскольку каждое слово или даже символ могут
иметь свои особенные характеристики. Поэтому текст, подготовленный с
помощью одного текстового процессора, как правило, не может быть
прочитан другими текстовыми процессорами и, следовательно, не может
быть отредактирован и распечатан.
В России наиболбшее распространение получил входящий в состав
пакета Microsoft Office текстовый процессор МS Ms Word.
Microsoft Word – многофункциональный текстовой процессор,
предназначенный для создания, редактирования и подготовки к печати
текстовых документов, с большими возможностями: от набора текста до
его красивого оформления с добавлением рисунков и таблиц, проверки
орфографии и печати на бумагу. По своим функциям Word вплотную
приближается к издательским системам (в Word можно полностью
подготовить к печати (сверстать) книгу, газету или журнал, а также создать
Web-страницу).
7
Word
позволяет
многие
процессы
создания
и
оформления
документов выполнять удобно с определенным уровнем автоматизации.
Имеются мастер и шаблоны документов, позволяющих в считанные
минуты создать деловое письмо, факс, автобиографию, расписание,
календарь и многое другое. Поддерживается автоматическая коррекция
текста по границам страницы, автоматическая разбивка на страницы и
расстановка их номеров, автоматический перенос и проверка правильности
написания слов, автосохранение документа. Использование стилей
позволяет быстро отформатировать текст документа. Эта программа
позволяет создавать тексты, используя различные шрифты большинства
языков мира.
Данная программа обеспечивает поиск заданного фрагмента текста,
замену, удаление, копирование во внутренний буфер. Можно также
автоматически включать в текст дату, время создания, обратный адрес и
имя написавшего текст, оглавление документа, перекрестные ссылки на
таблицы, рисунки и фрагменты текста. Word позволяет включать в
документ таблицы, рисунки, созданные имеющимися в программе
средствами, а также вставлять объекты созданные в других приложениях.
Для ограничения доступа к документу можно установить пароль. При
помощи макрокоманд можно
текстовых документах Word.
писать
программы,
выполняемые
в
Word позволяет открывать несколько окон с документами и
отображать на экране разные части одного документа для одновременной
работы. Word обладает широкими возможностями настройки интерфейса и
режимов работы программы под индивидуальные нужды пользователя.
1.2 Приемы и средства автоматизации разработки документов
1.2.1Макросы
Макрос представляет собой набор команд и инструкций
конкретного приложения, выполняемых как программа, предназначенный
для решения часто повторяющихся задач.
Макросы используются для следующих целей:
8
 ускорения часто выполняемых операций редактирования или
форматирования;
 объединения нескольких команд, например, для вставки таблицы
с указанными размерами и границами и определенным числом строк и
столбцов;
 упрощения доступа к параметрам в диалоговых окнах;
 автоматизации обработки сложных последовательных действий в
задачах;
 получения требуемого результата в тех случаях, когда действие
стандартных функций ограничено.
Для создания макроса в Microsoft Office существует два метода:
использование средства для записи макросов (Макрорекордер) и прямое
программирование в редакторе Visual Basic. На практике обычно
используются оба метода сразу: записывается часть шагов, а затем макрос
редактируется с помощью программного кода.
1.Макрорекордер
Наиболее простым способом создания макрокоманды является ее
запись с помощью макрорекордера. Макрорекордер записывает все
действия пользователя, включая ошибки и неправильные запуски. Когда
программа воспроизводит макрос, она выполняет каждую записанную
рекордером команду точно в такой последовательности, в которой её
выполнял пользователь во время записи. Удобством этого способа
является простота и наглядность - для использования этого способа вовсе
не нужны глубокие знания по программированию. Работа макрорекордера
во многом напоминает работу обычного магнитофона, осуществляющего
запись и воспроизведение звука.
Процесс записи макросов можно разбить на следующее этапы:
1. подготовка к записи – планирование всех шагов;
2. запуск макрорекордера и определение начальных параметров
макроса;
3. выполнение записываемых операций;
4. завершение записи.
При записи макроса можно:
9
 задать имя макроса (если новому макросу задать имя встроенной
команды Microsoft Ms Word, то встроенный макрос будет заменен новым.
Например, если записать новый макрос и задать для него имя FileClose,
этот макрос будет связан с командой Закрыть. После этого при выборе
команды Закрыть Microsoft Ms Word будет выполнять действия, заданные
в новом макросе);
 назначить для быстрого доступа к нему панель задач или
сочетание клавиш;
 указать место, где макрос будет храниться;
 добавить описание макроса для справки (описание макроса
включает дату создания и имя пользователя, записавшего макрос).
При необходимости все эти шаги можно пропустить и
непосредственно перейти к зиписи макроса.
2.Создание макроса программными средствами
В приложении Ms Word с помощью программных средств можно
написать очень мощные и сложные макросы, которые не могут быть
записаны
первым
способом.
Для
этого
используют
языки
программирования.
Языки – условное название разнообразной группы программ,
предназначенных для создания программ.
Все языки прграммирования можно разделить на категории:
 языки высокого уровня, имеющие понятный синтаксис и удобные
в работе, но создающие довольно громоздкие и медленные программы
(примером могут служить языки семейства Basic, Java, Delphi);
 языки низкого уровня, продуцирующие быстрые и компактные
продукты, но сложные для изучения и работы (отчасти C/C++, ассемблер).
Среди множества языков программирования семейство языков Basic
выделяется своей простотой и доступностью для программиста.
Язык Basic (Beginner’s All-purposes Symbolic Instruction Code –
Всецелевой Символический Код для Начинающих, буквальный перевод
сокращения – Базовый, Основной) был создан в 1963 г. профессорами
Дартмутского университета Дж. Кемени и Т. Курцем для обучения
студентов. Благодаря своим преимуществам язык Basic быстро завоевал
10
широчайшее
признание
в
среде
профессиональных
и
полупрофессиональных программистов.
Языки семейства Basic отличаются простотой составления программ,
быстротой и легкостью отладки и гибкостью в использовании.
Современное поколение языков Basic имеют в названии слово Visual
(Визуальный, Видимый). Начиная с версии языка №1, появившейся в 1991
году, в него встроена очень важная особенность – так называемое
визуальное редактирование, то есть возможность видеть внешний вид
будущей программы еще на этапе разработки и изменять его простыми,
интуитивно понятными средствами
Язык Visual Basic специально создан для программирования
приложений для популярной и потенциально мощной операционной
системы Windows фирмы Microsoft.
Семейство Visual Basic состоит из трех сходных между собой
диалектов языка: собственно Visual Basic, Visual Basic for Application язык, встроенный в приложения MS Office и Visual Basic Script,
применяемый для программирования в Internet.
VBA непосредственно связан с языком Visual Basic (VB). Основное
различие между ними формулируется следующим образом: проекты VBA
выполняются только с помощью приложения, поддерживающего VBA, в
то время как Visual Basic позволяет создавать полностью автономные
приложения. С другой стороны, синтаксис языков VBA и VB практически
одинаков. Оба языка имеют почти одинаковые интегрированные среды
разработки.
Visual Basic for Application (VBA) является единой средой
разработки приложений во всех программах Microsoft Office.
Преимущества VBA:
 простота и скорость построения маленьких утилит-макросов;
 простота отладки и использования.
Недостатки языка VBA: программа на VBA не может существовать
отдельно от документа приложения Office.
Современные языки имеют сложные и мощные средства,
предназначенные для создания, отладки, документирования и
11
проектирования
больших
приложений,
над
которыми
работают
коллективы программистов. Эти программные комплексы сокращенно
называются IDE – Integrated Development Environment, – Интегрированная
Среда Разработки. Иначе, интегрированной средой разработки является
сам язык программирования VBA.
Основные элементы интегрированной среды Visual Basic:
 Строка
меню.
В ее
состав
входят все
меню,
которые
используются при создании программы. Среди меню есть стандартные,
присущие многим окнам Windows: File (Файл), Edit (Правка), View (Вид),
Tools (Сервис), Help (Помощь) и др.Имеются также меню, при помощи
которых пользователь может создавать, запускать и отлаживать
программу: Insert (Вставка), Run (Запуск), Debug (Отладка) и др.
 Контекстное меню. Служит для удобства выполнения действий,
относящихся к той области окна VBА, в которой находится указатель
мышки.
 Панели инструментов: Standard {Стандартная), Edit {Правка),
Debug {Отладка) и др.
Visual Basic For Applications – это объектно-ориентированный язык
макропрограммирования высокого уровня, интегрированный во все
программы пакета MS Office и предоставляющим возможности
визуального программирования. Основное отличие программ на языке
VBA от программ, написанных на других языках программирования
(например, Basic, Pascal), состоит в том, что наряду с обычными
переменными и константами, эти программы манипулируют готовыми
объектами приложений Microsoft Office, такими, например, как документы,
абзацы, строки и слова Ms Word; или рабочие книги, рабочие листы и
диапазоны ячеек Ms Excel.
VBA содержит иерархию объектов, каждому из которых
соответствует свой набор методов и свойств.
Объект - это то, над чем может совершаться какое-либо действие
или то, что имеет определенные характеристики. К примеру, открытый
документ Ms Word - это объект, первая буква в этом документе - тоже
объект, тридцатое слово, десятое предложение, второй рисунок - это все
12
объекты. Объектами также являются запущенная программа, файл на
диске, программа Ms Ms Word - это тоже объект.
Почти каждый объект внутри себя имеет подобъекты, которые, в
свою очередь, являются полноценными объектами и могут иметь свои
подобъекты. Например, у объекта "Документ" есть подобъект "Десятое
предложение", у которого есть подобъект "Второе слово", у которого есть
подобъект "Третья буква".
С помощью объектно-ориентированного языка Visual Basic for
Applications можно обратиться к любому объекту Microsoft Office и
произвести с этим объектом какое-либо действие или узнать какие-либо
его характеристики.
Свойство - это любая характеристика объекта. Например, у объекта первой буквы документа есть свойства: выделение жирным, выделение
цветом, подчеркивание, выделение курсивом, регистр и т.д. У объекта документ есть свойства: наличие автоматической расстановки переносов,
наличие автоматической проверки орфографии и др.
Большинство свойств объектов Visual Basic for Applications можно
задавать программно, однако есть свойства не допускающие изменений.
Многие свойства объектов Ms Word также задаются через стандартные
диалоговые окна Ms Word, однако их всегда можно задать и программно, а
соответствующие диалоговые окна использовать при записи макроса для
того, чтобы посмотреть синтаксис команды задания того или иного
свойства.
Метод - это какое-либо действие над объектом. Например, печать
текста или поиск текста в документе. У многих методов есть параметры
метода, позволяющие задать параметры действия.
Событие - это то, что "происходит с объектом помимо его воли".
Это "все, что случается" с объектами по милости пользователя или какойлибо программы. Нажатие кнопки в окне программы или клавиши на
клавиатуре, набор буквы, клик мыши, открытие и закрытие окна
программы или документа, завершение работы любой другой программы
или ее запуск - все это события. События - основа работы любой
программы.
13
Так, когда пользователь нажимает кнопку на форме, происходит
событие нажатия кнопки.
В Visual Basic for Applications для каждого события можно написать
программу, которая будет срабатывать именно тогда, когда событие
произойдет, и выполнять определенные действия, которые должны быть
выполнены, когда это событие совершается.
Объектная модель Ms Word представляет собой иерархию. В эту
иерархию входят все компоненты Ms Word, которые можно использовать в
макросах.
На рисунке 1 показано одно представление этих объектов в иерархии
объектной модели Word
Рис.1. Объектная модель Ms Word
На первый взгляд, объекты перекрываются. Например объекты
Document и Selection члены объекта Application, но объект Document также
член объекта Selection. Оба объекта, Document и Selection, содержат
объекты Bookmark и Range. Перекрытие существует, поскольку
существует множество способов доступа к одному и тому же типу объекта.
Для решения большинства программных задач достаточно знать
всего лишь пять объектов:
 объект Application;
 объект Document (с коллекцией Documents);
 объект Selection;
 объект Range;
 объект Bookmark (с коллекцией Bookmarks).
14
Объект Application — это само приложение Microsoft Ms Word. Все
остальные объекты Ms Word "вложены" в этот объект. Создать этот объект
— значит запустить Ms Word на компьютере.
Объект Application это один из самых объемных объектов, в него
встроены объекты, задающие различные компоненты приложения Ms
Word. В программном проекте любого из открытых документов Ms Word
доступен корневой объект Application, определяющий само приложение.
Одновременно становятся доступными и все встроенные в него объекты. В
частности, становится доступной коллекция Documents всех открытых
документов Ms Word.
Объект Ms Word.Application имеет более сотни элементов: свойств,
методов и событий.
Объект Application, задающий приложение, естественно, определяет
свойства и поведение приложения в целом.
Объект Document. На одну ступень ниже объекта Application в
объектной модели Ms Word (и по логике использования в приложениях)
находятся коллекция Documents и объекты Document, из которых она
состоит. При программировании в Ms Word без коллекции Documents и
объекта Document обычно не обойтись
Когда открывается приложение, создается коллекция документов
Documents, содержащая открытые документы.
Объект Selection и Range. Основной частью документа Ms Word
является текст этого документа.
Объект Selection представляет выделенную область. Поскольку в
каждом окне может быть только одна выделенная область, то
одновременно может существовать лишь несколько объектов Selection по
одному на каждое существующее окно или подокно. Объект Selection
всегда существует в окне, даже если и не сделано явного выделения
некоторой области, в последнем случае объект Selection задает точку
вставки, определенную позицией курсора.
Объект Range представляет непрерывную область документа,
определенную начальным и конечным символами. Он характеризуется
следующими свойствами:
15
 Range
может
представлять
текущую
позицию
ввода,
непрерывную область текста или документ целиком;
 Range включает управляющие символы - отступы, табуляции,
разрывы срок, границы абзацев и т.д.;
 Range может представлять текущее выделение или другой
участок документа;
 участок
документа,
представляемый
объектом
Range
не
выделяется визуально;
 если новый текст вводится в конец диапазона, представленного
объектом Range, то он включается в состав объекта;
 объекты Range не сохраняются вместе с документом. Возможно
определение нескольких экземпляров объекта Range в одном и том же
документе.
Элемент управления Bookmark представляет собой закладку с
уникальным именем, событиями и возможностью привязки к данным.
Объект Bookmark — это просто закладка. На практике - это самый
удобный способ навигации по документам, созданных при помощи
шаблонов. Принципиальное отличие его от объектов Selection и Range
заключается в том, что все выделения и диапазоны теряются при закрытии
документа Если документ создан на основе шаблона, то все закладки,
которые были определены в шаблоне, будут определены и в созданном на
основе этого шаблона документе.
Функциональность объекта Bookmark невелика. Свойств и методов у
этого объекта намного меньше, чем у объектов Selection и Range.
Объект Bookmark аналогичен объектам Range и Selection в том
смысле, что он представляет непрерывную область в документе, заданную
начальной и конечной позициями. Закладки (bookmarks) используются,
чтобы помечать какое-либо место в документе. Объект Bookmark может
состоять хоть из одного курсора ввода, хоть из всего документа.
Допускается определение нескольких закладок в одном документе.
Макрос имеет определенную структура:
 Ключевое слово Sub. Этим обозначается начало макроса.
Слово Sub – сокращение от англ. SubRoutine – ПодПрограмма.
16
Подпрограмма – есть логически завершенная часть программы,
выполняющая какие-то определенные действия.
Другое название подпрограммы, принятое в терминологии VBA –
процедура.
 Имя макроса. После ключевого слова Sub добавляет имя макроса,
за которым следует открывающаяся и закрывающаяся скобки.
Именно по имени язык VBA определяет, с каким объектом
производится работа. Внутри проекта не должно быть макросов, с
одинаковыми именами.
Имя макроса в VBA всегда представлено одним словом. Составлять
имя могут буквы (предпочтительно английские), цифры и знак
подчеркивания. Начинаться имя всегда должно с буквы.
Язык VBA не производит различия между регистром символов. То
есть, имена СтильСноска, стильсноска и СТИЛЬсноска для VBA
совершенно одинаковы.
 Комментарии. Первые несколько строк кода начинаются с
апострофа "'", которые говорят редактору VBA, что эти строки являются
комментариями. Комментарием считается любая строка, начинающаяся с
апострофа – и до конца строки. Комментарии отображаются только в окне
редактора, при выполнении макроса они не обрабатываются. В каждом
записанном макросе в комментариях указывается имя макроса, а также
описание, которые пользователь ввел в диалоговом окне "Запись макроса".
 Макрооператоры.
Основное
тело макроса
(строки между
ключевыми словами Sub и End Sub, исключая комментарии в начале
макроса) состоит из последовательности операторов. Они являются
интерпретацией действий, которые пользователь выполнил во время
записи макроса.
Строки макроса в модуле VBA являются обычным текстом, который
можно изменять также, как, это делается в любом текстовом редакторе.
Каждая строка кода VBA описывает какое-то одно действие,
производимое языком. Иногда строки объединяются (для этого
используется символ : – двоеточие), но это ухудшает читаемость кода, не
давая никаких преимуществ.
17
1.2.2Шаблоны
Для автоматизации рутинной работы часто используют шаблоны.
Шаблоны подобны бланкам, в которые нужно вписать текст, который
меняется от документа к документу, остальные составляющие остаются
неизменными. В учебном учреждении с помощью шаблонов удобно
создавать титульные листы, ведомости, отчёты успеваемости и др., в офисе
с помощью шаблонов создаются приказы, отчёты, деловые письма,
приглашения и т.д.
Шаблон — это документ Microsoft Office, оформленный с
использованием уже существующих тем, стилей и макетов и содержащий
замещающий текст вместо фактического содержимого.
Отличительной особенностью шаблонов является то, что в них
можно хранить локальные настройки, которые будут действовать только
при использовании именно данного шаблона.
Шаблон — это тип документа, при открытии которого создается его
копия.
Ms Word поставляется с большим числом встроенных стандартных
шаблонов. Просмотреть коллекцию шаблонов можно открыв вкладку Файл
и выбрав команду Создать.
В диалоговом окне отображаются шаблоны, хранящиеся на жестком
диске, и шаблоны с веб-сайта Office.com. Они представлены в виде списка,
разделенного по категориям.
18
Рис.2. Диалоговое окно Создание шаблона
 Новый документ - это шаблон документа по умолчанию, к
которому не применялись дополнительное редактирование и настройки.
 Запись блога – с помощью этого шаблона, можно быстро и
эффективно создавать новые записи блогов.
 Последние шаблоны – недавно использовавшиеся шаблоны.
Список удобен при работе с часто используемым набором шаблонов.
 Образцы шаблонов – набор шаблонов, часто используемых при
работе с документами. Факс, отчет, стандартное письмо и шаблон
документа образца Word 2003 – основные позиции по которым
разработаны шаблоны.
 Мои шаблоны – набор созданных пользователем шаблонов.
 Из существующего документа – загружает документ по
требованию
пользователя,
с
соответствующими
настройками
форматирования, что удобно использовать при работе с одним и тем же
типом документов.
 Дополнительные шаблоны или шаблоны Office.com позволяют
найти практически любой шаблон, если недостаточно коллекции из
основных шаблонов.
19
Пользователь может редактировать шаблон по своему усмотрению,
т.е. изменять практически все настройки. При выборе какого-либо объекта
на шаблоне, для внесения изменений, на ленте отоброжается
дополнительная вкладка по работе с выбранным объектом (рисунок 3).
Рис.3. Отображение на ленте дополнительных вкладок по работе с
выбранным объеком
Важно помнить назначение шаблона – это готовая конструкция
документа, в который пользователь затем вносит свои коррективы,
экономя время на введении стилей, форматировании и создании
изображений.
Дополнительную гибкость шаблону можно придать, добавив и
настроив элементы управления содержимым, такие как поля
форматированного текста, рисунки, раскрывающиеся списки или выбор
даты (рисунок 4).
Рис.4. Раздел Элементы управления на вкладке Разрабтчик
 Элемент управления содержимым «форматированный текст» 20
позволяет применять к тексту различные параметры форматирования,
например выделение полужирным шрифтом или курсивом, а также
вводить несколько абзацев.
 Элемент управления «обычный текст»
возможность добавления параметров форматирования.
 Элемент управления содержимым
добавлять графические объекты.
-
«рисунок»
ограничивает
-
позволяет
 Элемент управления содержимым «поле со списком» - можно
выбирать элементы из заполненного списка, а также есть возможность его
редактирования.
 Элемент управления содержимым «раскрывающийся список» позволяет только выбирать варианты из списка.
 Элемент управления содержимым «дата».
 Элемент управления содержимым «флажок» - отображает одно
из двух состояний: выделено или пусто.
 Элемент управления содержимым «коллекция стандартных
блоков».
Стандартные блоки — это многократно используемые элементы
содержимого или другие части документа, которые хранятся в коллекциях
и в любое время доступны для повторного использования. Стандартные
блоки также можно хранить и распространять с помощью шаблонов.
Можно использовать элементы управления стандартными блоками
для предоставления возможности выбора определенного блока текста.
Например, элементы управления стандартными блоками полезны,
когда необходимо настроить шаблон контракта и добавлять различный
стандартный текст в зависимости от конкретных требований контракта.
Для каждой версии стандартного текста можно создать элементы
управления содержимым форматированного текста, а затем использовать
элемент управления коллекцией стандартных блоков как контейнер
элементов управления содержимым форматированного текста.
К отдельным элементам управления содержимым в шаблоне можно
добавить защиту, чтобы запретить удаление или редактирование
определенных элементов управления содержимым или группы элементов
21
управления. Можно также защитить все содержимое шаблона паролем.
1.3 Автоматизация комплексных текстовых документов
Слияние применяется в тех случаях, когда необходимо создать набор
однотипных документов, каждый из которых содержит уникальные
элементы. Например, берем образец письма и таблицу с фамилиями и
адресами людей, которым нужно разослать это письмо, а затем,
автоматически объединяя образец с таблицей, генерируем набор писем.
Все копии исходного письма наследуют общий текст, но содержат разную
информацию в области адреса и фамилии получателя, которая заполняется
на основе таблицы данных.
Метод слияние документов использует два вида документов:
Основной документ - документ, содержащий данные, которые
остаются при слиянии неизменными во всех производных документах,
например обратный адрес или текст письма.
Источник данных - файл, содержащий сведения, предназначенные
для объединения с документом. Например, список имен и адресов,
которые требуется использовать при слиянии.
С помощью слияния можно создавать следующие типы документов:
 Комплект конвертов. Обратные адреса на всех конвертах
одинаковы,
однако
адрес
получателя
в
каждом
случае
является
уникальным.
 Комплект наклеек с адресами. На каждой наклейке приводятся
фамилия и адрес, которые являются уникальными.
 Комплект документов на бланке или сообщений электронной
почты. Основное содержимое всех писем или сообщений является
одинаковым, но каждое из них содержит сведения, предназначенные для
определенного получателя, например, фамилию, адрес или какую-либо
другую информацию.
 Каталог или папка. Для каждого элемента отображаются
сведения одного и того же типа, например имя или описание, однако для
каждого элемента они уникальны.
Процесс слияния состоит из нескольких общих действий:
22
1. Создание основного документа. На этом этапе определяется тип
основного документа и ему присваивается статус основного документа.
Уважаемый
Сообщаем, что вами не сдан
по дисциплине .
Деканат
Рис.5. Образец оформления Основного документа
2. Создание или выбор существующего источника данных.
Источником
данных
может
быть
практически
любая
структурированная в таблицу информация, включая таблицу Microsoft
Word, список контактов Microsoft Outlook, лист Microsoft Excel, базу
данных Microsoft Access и текстовый файл.
Таблицу Ms Word рекомендуется использовать при наличии
небольшого списка данных (63 или менее полей данных). Если источник
данных Ms Word содержит более 63 полей данных, следует преобразовать
его в текстовый файл с разделителями.
Фамилия
Имя
Отчество
Адрес
Иванов
Иван
Петрович
г. Воронеж, ул.
Плехановская, д.23, кв.12
Петрова
Анна
Ивановна
г. Воронеж, ул. Мира, д.43,
кв.1
Форма
контроля
Наименование
зачет
математика
экзамен
информатика
Рис.6. Образец оформления Источника данных
3. Вставка полей слияния - поля, в которые переносится переменная
информация из источника данных в основной документ перед выводом
готового документа на печать.
Чтобы переменная информация из источника данных правильно
переносилась в основной документ перед выводом готового документа на
печать, необходимо расставить в основном документе поля слияния.
Поля в приложении Ms Word соответствуют выделенным заголовкам
столбцов в файле данных.
23
Для
правильного
отображения
данных
слияния
необходимо
вставлять пробелы и знаки пунктуации между полями, до и после знаков
поля слияния (« »).
Для выделения данных слияния можно отформатировать поля
слияния. Например, можно сделать адрес полужирным или выделить
название продукта другим шрифтом
«Адрес»
Уважаемый «Фамилия Имя Отчество»
Сообщаем, что вами не сдан «Форма контроля» по дисциплине «Наименование».
Деканат
Рис.7. Образец вставки полей слияния
4. Вывод стандартного документа. При этом каждая строка (или
запись) источника данных порождает отдельный документ на бланке,
почтовую наклейку, конверт или элемент каталога. При этом составные
документы могут быть объединены в новом документе для последующего
просмотра и печати.
г. Воронеж, ул. Плехановская,
д.23, кв.12
Уважаемый Иванов Иван Петрович
Сообщаем, что вами не сдан зачет по дисциплине математика.
Деканат
г. Воронеж, ул. Мира,
д.43, кв.1
Уважаемый Петрова Анна Ивановна
Сообщаем, что вами не сдан экзамен по дисциплине информатика
Деканат
Рис.8. Результат слияния документов
24
2 ИСПОЛЬЗОВАНИЕ ТАБЛИЧНОГО ПРОЦЕССОРА
MS EXCEL ДЛЯ ОБРАБОТКИ ИНФОРМАЦИИ
Электронная таблица – это широко распространенная и мощная информационная технология, предназначенная для профессиональной работы с данными. Это – компьютерный эквивалент обычной таблицы, в ячейках которой записаны данные различных типов: тексты, даты, формулы,
числа.
Для управления электронной таблицей используется специальный
комплекс программ – табличный процессор.
Табличные процессор на сегодняшний день являются одними из самых распространенных программных продуктов, используемых во всем
мире. С помощью них создаются достаточно сложные приложения, которые удовлетворяют до 90% запросов средних пользователей. Область применения табличных процессоров обширна: начиная от бухгалтерских и
складских задач и заканчивая расчетами энергетики спутниковых линий.
Первым табличным процессором, получившим широкое распространение, стал Lotus 1-2-3, ставший стандартом для табличных процессоров и
определивший их основную логику:
 структура таблицы (пересечения строк и столбцов создают
ячейки, куда заносятся данные);
 стандартный набор математических и бухгалтерских функций;
 возможности сортировки данных;
 наличие средств визуального отображения данных (диаграмм).
В нашей стране в свое время получили широкое распространение два
табличных редактора SuperCalc и Quattro Pro. С появлением Microsoft
Windows и его приложений стандартом для работы с электронными таблицами стал табличный процессор Microsoft Excel.
Среди ключевых преимуществ, которые обеспечивают лидерство
Microsoft Excel на рынке, программ, предназначенных для создания и обработки электронных таблиц, можно выделить следующие.
 Эффективный анализ и обработка данных. Использование
мастера сводных таблиц и других позволяют быстро и удобно
обрабатывать большие массивы данных и получать итоговые результаты в
25
подходящем виде. Мощнейший аппарат стандартных функций и формул, а
также средства встроенного языка программирования Visual Basic For
Application позволяют решить и реализовать практически любую
расчетную задачу - от статистических до задач имитационного и
оптимизационного моделирования. Использование естественного языка и
механизм автокоррекции формул позволяют автоматически распознавать и
исправлять ошибки при введении и написании формул.
 Эффективные средства форматирования и отображения данных.
Средства форматирования делают оформление таблиц более отчетливым и
понятным (возможности слияния ячеек в электронной таблице, поворот
текста в ячейке на любой угол, выделение ячейки и текста в ней
различным цветом, шрифтом и т.д.). Мастер создания диаграмм
представляет пользователю широкий набор стандартных и нестандартных
диаграмм (графиков, гистограмм и т.д.), что позволяет сделать
представление данных в таблицах более наглядным. Microsoft Excel
предоставляет возможность задать точное расположение информации на
каждой печатной странице.
 Широкими
возможностями настройки пользовательского
интерфейса, который по многим режимам работы удовлетворяет
индивидуальные нужды пользователя.
 Совместное использование данных и работа над документами.
Для
пользователей
Microsoft
Excel
доступен
режим
многопользовательской работы - несколько пользователей могут
одновременно работать с одной и той же таблицей. Аналогично режиму
исправлений в Microsoft Word, можно оставлять комментарии о том, кто и
когда внес изменения в данную ячейку.
 Обмен данными и информацией через Internet и внутренние
Intranet-сети. Microsoft Excel позволяет импортировать данные из HTMLдокументов, найденных на Web-сервере, восстанавливая при этом формат
и оформление таблицы. После импорта данные доступны для выполнения
любых операций в Microsoft Excel. Кроме этого имеются встроенные
функции, позволяющие легко помещать на Web-сервер документы,
созданные в среде Microsoft Excel. К их числу относятся: мастер
26
сохранения документа в формате HTML и ряд других.
 Интеграция с другими приложениями входящими в пакет
Microsoft Office. Microsoft Excel является составной частью офисного
пакета, тесно интегрированный с другими приложениями входящими в
пакет, что позволяет импортировать и экспортировать данные из других
приложений, создавать связанные документы и сохранять единый стиль в
них.
Наиболее распространенными областями применения Ms Excel являются:
 Учет.
Можно
возможности Ms
документами.
использовать
Excel для
работы с
мощные
вычислительные
различными
финансовыми
 Бюджетирование - можно создавать как личный бюджет, так и
бюджеты компании.
 Выставление счетов и продажи. Приложение Ms Excel также
полезно применять для управления данными о выставлении счетов и
продажах. Можно легко создавать нужные формы.
 Создание отчетов. В Ms Excel можно создавать отчеты различных
типов, в которых анализируются или обобщаются данные.
 Планирование. Ms Excel прекрасно подходит для создания
профессиональных планов или удобных планировщиков, таких как
расписание занятий на неделю, план маркетинговых исследований и т.д.
 Отслеживание. С помощью Ms Excel можно отслеживать данные
в листах учета или списках.
 Работа с календарями. Рабочая область Ms Excel, имеющая вид
таблицы, позволяет легко создавать календари различных.
2.1 Основы VBA в Ms Excel
Visual Basic для приложений является объектно-ориентированным
языком, предоставляющим возможности визуального программирования.
VBA содержит иерархию объектов, каждому из которых соответствует свой набор методов и свойств. Объекты представляют собой фунда-
27
ментальные «строительные» блоки – почти все, что делается в среде VBA,
включает модификацию объектов
Объектом VBA считается некоторый элемент, который можно отобразить в окне приложения и на который можно воздействовать некоторым образом, изменяя его состояние. Например, Рабочая книга, Рабочий
лист, Активная ячейка, Диапазон являются объектами. К числу наиболее
значимых объектов Excel следует отнести следующие.
Таблица 1. Объекты Excel
Объект
Application
(объект «Приложение»)
Windows
(семейство «Окна»)
Workbooks
(семейство
«Рабочие книги»)
Worksheets
(семейство
«Рабочие листы»)
Range
(объект «Диапазон»)
Краткая характеристика
Этот объект представляет собой само приложение Excel в целом. Он включает в себя глобальные устанавливаемые параметры, такие, например, как используемый стиль ссылок на
ячейки или режим проведения вычислений. Кроме того, он
включает в себя встроенные функции Excel.
Объекты этого семейства используются при управлении окном
(сворачивании, разворачивании, разбиении его на части и т.д.)
Принадлежащий к этому семейству объект ActiveWindow представляет собой активное в настоящий момент окно.
Объекты этого семейства определяют состояние рабочей книги,
например: не является ли она доступной только для чтения; или
какой из листов рабочей книги активен в настоящий момент.
Принадлежащий к этому семейству объект ActiveWorkbook —
это объект, который представляет собой активную в настоящий
момент рабочую книгу.
Объекты этого семейства используются при копировании или
удалении абочих листов, их скрытии или отображении, проведении вычислений для формул рабочего листа. Принадлежащий к этому семейству объект ActiveWorksheet — это объект,
который представляет собой активный в настоящий момент рабочий лист.
Этот объект позволяет изменять свойства диапазона ячеек, например, используемый шрифт, проверять или изменять содержимое ячеек, вырезать или копировать указанный диапазон, и
многое другое. Это наиболее часто используемый в Excel объект. Принадлежащий к этому же классу объектов объект
ActiveCell представляет собой активную в настоящий момент
ячейку. Следует обратить внимание на то, что не существует
такого объекта, как Cell (ячейка)— отдельно взятая ячейка
представляет собой частный случай объекта Range (Диапозон).
Существуют сотни самых разнообразных объектов VBA, многие из
которых объединяются в Семейства объектов. Семейством (Collection) в
VBA называется совокупность однотипных объектов. Например, в Excel
семейство Worksheets является совокупностью всех рабочих листов —
28
объектов Worksheet — в данной рабочей книге, а семейство Lines — совокупностью прямых линий, нарисованных на данном рабочем листе. Составляющие семейство отдельные объекты называются элементами семейства. Можно ссылаться на отдельные элементы семейства, указывая в
скобках имя конкретного объекта.
Хотя семейства представляют собой группы объектов, сами семейства также являются одиночными объектами. Такой собирательный объектсемейство представляет собой всю совокупность входящих в семейство
объектов, и имеет собственные свойства и методы, с помощью которых
можно изменять разом состояние всех объектов семейства.
Как правило, индивидуальные объекты, являющиеся элементами семейств, имеют гораздо больше свойств и методов, чем соответствующий
собирательный объект-семейство. Например, объект-семейство Workbooks
в Excel имеет всего пять свойств (Application, Count, Creator, Item, Parent) и
четыре метода (Add, Close, Open, OpenText), в то время как объект
Workbook имеет 59 свойств и 42 метода.
Не все объекты приложений могут группироваться в семейства —
для некоторых индивидуальных объектов не существует соответствующих
семейств.
Приведем краткий список наиболее часто используемых семейств
объектов Excel.
Таблица 2. Семейства объектов Excel
Семейство
Workbooks
Sheets
Worksheets
Описание
Все открытые в настоящий момент рабочие книги. С помощью метода Open можно открыть еще одну рабочую книгу. Метод Add создает новую рабочую книгу.
Включает в себя все листы рабочей книги — как обычные рабочие
листы, так и листы диаграмм. Наиболее часто используемые методы
— Add, Copy, Delete, Select.
Все рабочие листы рабочей книги. Используются те же методы, что
и для семейства Sheets.
Важнейшим понятием объектно-ориентированного программирования является класс, который определяется как проект, на основе которого
впоследствии будет создан конкретный объект, т.е. класс определяет имя
объекта, его свойства и действия, выполняемые над объектом. В свою очередь объект является экземпляром класса.
29
В каждом из приложений Office взаимодействие объектов образует
многоуровневую структуру. Удобно представлять эту иерархию объектов в
виде своеобразного "родословного древа". Для того чтобы правильно обращаться к объектам, нужно понимать эту иерархию, хорошо представлять
себе, на каком уровне иерархии находится конкретный объект. Некоторые
объекты могут располагаться в разных местах иерархического дерева объектов, на разных его уровнях и тем самым выполняют различные функции.
Полное описание всей иерархической структуры имеющихся объектов в каждом из приложений Office можно получить используя встроенную справку по VBA.
.
Рис.9. Первый уровень иерархического дерева объектов Excel
На данном рисунке имеются следующие обозначения элементов иерархии:
Object and collection – объекты и коллекции;
Object only – только объекты.
Click arrow to expand chart – переход на один уровень ниже (от объекта к подобъекту). Во встроенной справке на иерархическом дереве объ-
30
ектов простые объекты окрашены бирюзовым цветом, а объекты, являющиеся также и семействами — желтым.
Полная ссылка на объект состоит из ряда имен вложенных последовательно друг друга объектов. Разделителями имен объектов в этом ряду
являются точки. Ряд начинается с объекта Application (Приложение) и заканчивается именем самого объекта. Например, полная ссылка на ячейку
A1 рабочего листа Урожай рабочей книги с именем Сельхоз_культуры
имеет вид:
Application.Workbooks(«Сельхоз_культуры»).Worksheets(«Урожай»).
Range(«А1»)
Приводить каждый раз полную ссылку на объект не обязательно.
Обычно достаточно ограничиться неявной ссылкой на объект. В неявной
ссылке, в отличие от полной, объекты, которые активны в данный момент,
можно опускать. Например, в рассмотренном выше случае, если ссылка на
ячейку А1 дана в программе, выполняемой в среде Excel, то ссылка на
объект Application, может быть опущена, т.е. достаточно привести относительную ссылку:
Workbooks(«Сельхоз_культуры»).Worksheets(«Урожай»).Range(«А1»)
Если и рабочая книга Сельхоз_культуры и рабочий лист Урожай активны, то в относительной ссылке достаточно ограничиться упоминанием
только диапозона А1: Range(«А1»)
В VBA каждый объект обладает определенными свойствами
(Properties), описывающими этот объект или его состояние.
Например, в приложении Excel объект Range (диапазон ячеек рабочего листа) имеет такие свойства, как:
Font — используемый шрифт;
Formula — определяет содержимое ячейки или диапазона ячеек;
Value — определяет значение ячейки и многие, многие другие свойства;
При ссылке на свойство объекта используется тот же самый синтаксис, что и при уточнении иерархического соподчинения объектов:
Объект.Свойство
31
Например, для ссылки на значение активной ячейки используется
запись: ActiveCell.Value
К некоторой путанице может привести то, что некоторые свойства
объектов сами в свою очередь могут выступать в качестве объектов. Так,
например, объект Application имеет свойство ActiveWindow, которое показывает, какое окно является активным в настоящий момент. В то же время
ActiveWindow является объектом семейства Windows, который может
иметь свойство ActiveCell, в свою очередь являющееся объектом класса
Range. Кроме этого, ActiveCell имеет свойство Font, также являющееся
объектом, имеющим такие свойства, как Name, Size или Bold.
Часто используемые свойства объектов Excel приведены в таблице 3.
Таблица 3. Свойства объектов Excel
Объект
Описание
Объект Application
ActiveWindow
Активное окно
ActiveWorkbook Активная рабочая книга
ScreenUpdating Режим обновления экрана
StandardFont
Имя стандартного шрифта для новых рабочих листов
Объект Workbook (элемент из семейства Workbooks)
ActiveSheet
Активный рабочий лист
FullName
Полное имя рабочей книги, включая путь
Name
Имя рабочей книги
Saved
Признак того, что состояние рабочей книги сохранено на диске
Объект Worksheet
Name
Имя рабочего листа
OnSheetActivate Имя процедуры, вызываемой при активизации рабочего листа
Previous
Предыдущий рабочий лист
ProtectContents Режим защиты содержимого ячеек рабочего листа
Visible
Режим видимости рабочего листа (скрыт или показан)
Объект Window
ActiveCell
Активная ячейка
DisplayGridlines Режим отображения линий сетки
Selection
Текущий выделенный объект
Visible
Режим видимости окна
WindowState
Режим отображения окна
Объект Range
Column
Первый столбец диапазона
Font
Используемый в диапазоне шрифт
Formula
Формула диапазона
Name
Имя диапазона
Row
Первая строка диапазона
Value
Значение ячейки
Worksheet
Рабочий лист, содержащий диапазон
32
Существует два способа использования свойств объектов: можно
либо получить текущее значение свойства, либо изменить свойство (то
есть задать новое значение для этого свойства). При изменении свойства
необходимо указать сначала имя объекта, а затем имя свойства, использовав в качестве разделителя точку, затем следует оператор присваивания (=)
и новое значение:
Объект.Свойство = НовоеЗначение
В этом выражении новое значение может быть константой или формулой, возвращающей нужное значение, и принадлежать к одному из трех
типов:
Числовое значение. Например, при установлении размера шрифта
может использоваться следующий оператор:
ActiveCell.Font.Size = 14
Строка символов. Строковые значения заключаются в двойные кавычки. Вот пример изменения используемого шрифта:
ActiveCell.Font.Name = "Courier New Cyr"
Логическое значение. В VBA используется обозначение True и False,
в отличие от функций рабочего листа, где используется обозначение ИСТИНА и ЛОЖЬ. Вот пример изменения начертания шрифта (применение
курсива):
ActiveCell.Font.Italic = True
Методы
Объект сам по себе не представляет большого значения. намного
важнее то какие действия можно совершать над объектом. Метод
(Methods) как раз и представляет собой действие, выполняемое над объектом. Синтаксис применения метода:
Объект.Метод
Например, при помощи метода Save (Сохранить) сохраняется рабочая книга (объект Workbook):
Workbook(«Сельхоз_культуры».Save
Синтаксис вызова метода отличается от синтаксиса ссылки на свойство объекта. Методам не присваиваются значения, они не сохраняются в
какой-либо переменной. Для того, чтобы вызвать метод сначала необходи-
33
мо указать имя объекта, поставить точку в качестве разделителя, потом
следует имя метода, а затем при необходимости могут следовать аргументы, указывающие, как должен выполняться метод.
К наиболее распространенным методам Excel можно отнести следующие.
Таблица 4. Методы объектов Excel
Метод
Quit
Undo
Activate
Close
Save
Save As
Activate
Calculate
Delete
Protect
Unprotect
Activate
Close
Clear
ClearContents
ClearFormats
Copy
Offset
Paste
Select
Sort
Описание
Объект Application (Приложение)
Завершает Excel
Отменяет последнее выполненное действие
Объект Workbook (Рабочая книга)
Активизирует рабочую книгу
Закрывает рабочую книгу
Сохраняет рабочую книгу
Сохраняет рабочую книгу под другим именем
Объект Worksheet (Рабочий лист)
Активизирует рабочий лист
Заново вычисляет значения в ячейках рабочего листа
Удаляет рабочий лист
Защищает рабочий лист
Отменяет защиту рабочего листа
Объект Window (Окно)
Активизирует окно
Закрывает окно
Объект Range (Диапазон)
Полностью очищает диапазон (в том числе и форматирование ячеек)
Очищает содержимое ячеек диапазона
Очищает форматирование ячеек диапазона
Копирует диапазон в буфер обмена
Возвращает диапазон с указанным смещением относительно первоначального диапазона
Вставляет содержимое буфера обмена в диапазон
Выделяет диапазон
Сортирует значения в диапазоне
Описанные в процедурах VBA действия выполняются над переменными или объектами. Переменная — это поименованная область памяти,
которая используется для хранения данных в течение работы процедуры.
Переменные VBA аналогичны переменным других языков программирования.
Использованию переменной обычно предшествует ее объявление
(обозначение свойств и возможностей) посредством оператора Dim, в котором указываются имя переменной и тип данных, для хранения значений
34
которого она предназначена. Хотя такой оператор не является обязательным, тем не менее, хороший стиль программирования требует предварительного описания всех используемых переменных. Имена переменных
могут иметь длину до 255 символов (используются и символы кириллицы)
и не должны содержать пробелов, знаков препинания, специальных символов. Они не могут совпадать с ключевыми словами VBA и именами стандартных объектов.
В процедурах и функциях VBA могут использоваться переменные
следующих типов данных:
 Boolean — логические переменные, принимающие одно из двух
значений: True или False;
 Byte — целое число из диапазона от 0 до 255;
 Currency
—
переменные
для
денежных
вычислений
фиксированным количеством разрядов после десятичной
позволяют избежать накопления погрешностей при округлении;
с
запятой;
 Date — переменные для хранения даты и времени;
 Double — переменные, использующиеся для хранения значений с
плавающей точкой двойной точности;
 Integer — целые числа из диапазона от -32 768 до 32 767;
 Long - целые числа из диапазона от -2 147 483 648 до 2 147 483
647;
 Object — переменные для хранения ссылок на объекты;
 Single — переменные для хранения значений одинарной точности
с плавающей точкой;
 String — переменные для хранения строк символов длиной от 0
до 64 Кбайт;
 Variant — переменные, тип которых определяется типом
последнего присвоенного им значения,
Типы данных описываются не только для переменных, но и для
формальных параметров процедур и функций, а также для самих функций
(тип возвращаемого функцией значения).
35
Область видимости переменной — это область программы, где имя
переменной считается доступным (видимым), а значит, возможен доступ к
ее значению.
Существуют три уровня видимости переменной.
 Процедура (областью видимости является только та процедура, в
которой переменная объявлена).
 Модуль (область видимости — все процедуры модуля, в котором
переменная объявлена).
 Приложение (область видимости — все процедуры всех модулей
активного приложения).
Область переменных VBA определяется местом их описания. Если
переменная описана внутри процедуры или функции, ее область видимости ограничивается этой процедурой или функцией. Любая попытка использовать имя данной переменной вне процедуры или функции, содержащей ее описание, приведет либо к ошибке, либо к созданию новой переменной — с тем же именем, но с другой областью видимости. Если переменная описана на уровне модуля (то есть соответствующий ей оператор
Dim помещен непосредственно в модуль, перед описанием процедур и
функций), то ее можно использовать в любой процедуре или функции,
описанной в данном модуле. Но если предполагается, что переменная будет применяться в процедурах и функциях, описанных в различных модулях проекта, то ее следует описать в одном из этих модулей, воспользовавшись вместо оператора Dim совпадающим с ним по синтаксису оператором Public.
В VBA поддерживается следующая программная структура. На
высшем уровне иерархии стоит приложение, далее идут проекты, связанные с фактическими документами этого приложения. На третьем уровне
находятся модули (модули приложения, модули пользователя, модули
класса, модули форм и модули ссылок), а на последнем — их процедуры и
функции. Схематически такая иерархия может быть представлена следующим образом.
Модуль — это часть программы, оформленная в виде, допускающем
ее независимую трансляцию. Модуль состоит из двух разделов: раздела
36
объявлений (Declaration) и раздела процедур и функций. В первом разделе
описываются глобальные переменные, типы, определенные пользователем,
и перечисляемые типы, во втором — процедуры и функции. Совокупность
модулей образуют проект.
Проект
Приложение
Модуль
Проект2
Объявление
…….
Модуль2
Проект N
…….
Процедура1
…….
Модуль N
ПроцедураN
Рис.101. Структура программы VBA
Процедурой называется фрагмент кода (минимальная семантически
законченная программная конструкция), заключенный между операторами
Sub и End Sub. Синтаксис процедуры следующий:
[Private | Public | Friend] [Static] Sub имя_процедуры (аргумент _1, аргумент_2. ...аргумент_n)
<оператор VBA>
<оператор VBA>
<оператор VBA >
End Sub
Синтаксис функций несколько отличается от синтаксиса процедур,
так как для функции необходимо указать тип возвращаемого (определяемого) ею значения:
[Private | Public | Friend] [Static] Function имя_функции (аргумент_1,
аргумент __2, ...аргумент_n) As тип_возвращаемого_значения
<оператор VBA>
<оператор VBA>
имя_функции= возвращаемое_значение
End Function
Ключевое слово Private задает следующую область видимости для
процедуры - модуль, в котором она описана. Значит, ее могут вызывать
37
только процедуры этого же модуля. Ключевое слово Publ ic, наоборот,
объявляет процедуру доступной для всех модулей проекта. С помощью
ключевого слова Friend процедура или функция становится видимой только в том проекте, где был описан класс, членом которого она является. По
умолчанию любая процедура общедоступна, то есть имеет статус Public.
За операторами объявления следует необязательное ключевое слово
Static, объявляющее все локальные переменные статическими.
Выполнение программного кода процедуры или функции происходит при передаче ей управления. Передача управления (вызов) может осуществляться различными способами. В общем случае подпрограмма вызывается из программного кода с помощью специального оператора Са11 , в
котором кроме данного ключевого слова указываются имя процедуры и
фактические параметры вызова, список которых заключается в круглые
скобки.
Очень важную роль в программировании на VBA играет такая разновидность процедуры, как процедура без параметров. Процедуры данного
типа могут выступать в роли командных макросов и процедур обработки
событий. Схема работы с командными макросами не отличается от традиционной схемы работы с макросами.
После создания подобного макроса с помощью редактора VBA ему
можно назначить комбинацию клавиш для вызова, сопоставить его с командой меню или кнопкой на панели инструментов.
Процедуры обработки событий представляют собой основу механизма связи событий с программным кодом для их обработки.
Функции в VBA, как правило, содержат списки параметров. Они не
могут использоваться для обработки событий или, скажем, в качестве программных макросов. Вызывать же их можно как с помощью оператора
Са11 , так и непосредственно в процессе расчета значения выражения VBA
(из кода других процедур или функций) или формулы рабочего листа
Excel.
Как правило, программа на языке VBA состоит из нескольких процедур и функций. Описание этих программных единиц хранится в модулях. Модули, в свою очередь, как составные части входят в проекты. Про-
38
екты сохраняются в файлах рабочих книг Excel 2003, но работать с ними
можно лишь при наличии отдельного приложения — редактора Visual
Basic. Если нужно вызвать процедуру или функцию, описание которой находится в другом модуле, ее имя при вызове указывается следующим образом: имя_модуля.иня_процедуры. Но если описание процедуры или
функции содержится в другом проекте, при ее вызове следует использовать другой синтаксис:
имя_проекта.имя_модуля.имя_процедуры.
2.2 Методы анализа данных в Ms Excel
2.2.1Средства анализа «что-если»
С помощью в Microsoft Excel можно экспериментировать с различными наборами значений в одной или нескольких формулах для изучения
всех возможных результатов.
Анализ «что-если» предполагает изменение значений ячеек для выяснения того, как эти изменения повлияют на результаты выполнения
формул на листе.
В Ms Excel предусмотрены средства анализа «что-если» трех типов:
 сценарии;
 таблицы данных;
 подбор параметров.
Сценарии и таблицы данных принимают наборы входных значений и
определяют возможные результаты.
Средство подбора параметров принимает результат и определяет
возможные входные значения, обеспечивающие получение этого результата.
Запустить средство анализа данных «что-если» можно с вкладки
Данные Ms Excel.
Кроме этих трех средств можно установить дополнительные надстройки, помогающие выполнять анализ «что-если», например «Поиск решения». Надстройка «Поиск решения» похожа на средство подбора параметров, но поддерживает большее число переменных.
39
Рис.11. Вкладки Данные, содержащей средство анализа «что-если»
Также можно составлять прогнозы, используя маркер заполнения и
различные команды, встроенные в Ms Excel. Для реализации более сложных моделей можно использовать надстройку «Пакет анализа».
Использование сценариев для анализа данных
Сценарий — это набор значений, которые сохраняются в приложении Ms Excel и могут автоматически подставляться в ячейки листа.
Сценарий – это модель «что – если», в которую входят переменные
ячейки, связанные одной или несколькими формулами.
Сценарий можно создать для любой таблицы. Для каждого сценария
обязательно указываются изменяемые ячейки и используемые значения.
При переключении между сценариями, Ms Excel модифицирует значения
только в изменяемых ячейках. Соответственно, значение в ячейке результата будет изменяться, отражая различные значения изменяющихся ячеек.
Можно создавать новые и просматривать существующие сценарии
для решения задач, и отображать консолидированные отчеты.
Ms Excel сохраняет сценарии вместе с листом текущей книги, и их
просмотр возможен только при открытии данного листа.
Сравнивать различные сценарии можно, переходя от сценария к сценарию, но иногда возникает необходимость в создании отчета с обобщенной информацией о различных сценариях листа.
Отчет может быть двух видов:
 отчет типа структура - представляет собой форматированную
таблицу, которая выводится на отдельном листе.
 отчет сводная таблица - является специальной таблицей, которую
можно настраивать за счет перестановки столбцов и строк.
40
Анализ данных с использованием Таблицы данных
Неотъемлемой процедурой обработки статистической, финансовоэкономической информации, результатов опытов является многократное
повторение расчетов по одним и тем же формулам для серий изменяющихся значений. При обработке такой информации в среде электронных таблиц Excel обычно используется операция копирования формул с учетом
относительной и абсолютной адресации ячеек рабочей книги.
Одним из средств повышающих эффективность обработки данных
являются таблицы данных.
В Ms Excel таблицой данных выполняются множественные пересчеты книги, каждый управляется различными значениями в таблице. В Ms
Excel книга сначала рассчитывается обычным образом. Затем для каждой
пары значений строки и столбца значения подставляются и пересчитываются и результаты сохраняются в таблице данных.
Таблицы данных удобны для вычисления нескольких вариантов,
просмотра и сравнения результатов этих вариантов.
Каждый раз, когда создается формула, можно получить единственный результат.
Таблицы данных позволяют изучить набор возможных результатов.
В отличие от сценариев, в Таблицах данных все результаты отображаются
в одной таблице на одном листе. Применение Таблиц данных облегчает и
ускоряет изучение возможных вариантов. Поскольку внимание сосредоточено только на одной или двух переменных, результаты легко воспринимаются, ими просто обмениваться в табличной форме.
Таблица данных не может принимать более двух переменных. Если
необходимо проанализировать больше переменных, вместо таблиц данных
лучше использовать сценарии.
Несмотря на то, что работа с Таблицей данных ограничена только
одной или двумя переменными (одна для подстановки значений по столбцам, а другая — по строкам), при этом можно использовать любое количество различных значений переменных. Сценарий же поддерживает только
32 различных значения, но количество сценариев может быть любым.
41
В зависимости от числа переменных и формул, которые нужно протестировать, различают два вида Таблиц данных:
Таблицы данных с одной переменной. Такую таблицу рекомендуется
использовать для просмотра влияния различных значений одной переменной в одной или нескольких формулах на результаты этих формул. Значения переменной вводятся в одном столбце или строке, а результаты отображаются в соседнем столбце или смежной строке.
Таблицы данных с двумя переменными. Такую таблицу можно использовать для просмотра влияния различных значений двух переменных в
одной формуле на результаты этой формулы.
Проведение расчетов методом Подбора параметра
Если результат, который необходимо получить при вычислении
формулы, известен, но неясно, какое входное значение формулы требуется
для получения этого результата, можно использовать средство Подбора
параметров.
Средство Подбора параметров поддерживает только одно входное
значение переменной.
Чтобы использовать функцию Подбор параметра, необходимо предоставить Ms Excel три блока информации:
 Целевая
ячейка.
Содержит
формулу
для
вычисления
необходимой информации.
 Значение. Задает числовое значение цели, которая вычисляется в
целевой ячейке.
 Переменная ячейка. Задает исходную ячейку, которая изменяется
в Ms Excel, пока в значение целевой ячейке не станет равно цели,
указанной в ячейке значения.
При подборе параметра Ms Excel использует итерационный процесс.
Он проверяет для изменяемой ячейки одно значение за другим, пока не получит нужное решение.
По умолчанию команда Подбор параметра прекращает вычисления,
когда выполняется 100 итераций, или при получении результата, который
находится в пределах 0,001 от заданного целевого значения.
42
Команда Подбор параметра находит только одно решение, даже если
задача имеет несколько решений.
Решение задач оптимизации с использованием Поиска решения
Поиск решений может применяться для решения задач, которые
включают много изменяемых ячеек, и помогает найти комбинацию переменных, которые максимизируют или минимизируют значение в целевой
ячейке. Он также позволяет задать одно или несколько ограничений условий, которые должны выполняться при поиске решений.
Общие свойства, которые характерны для задач, решаемых с помощью надстройки Поиск решения:
 существует единственная целевая ячейка, содержащая формулу,
значение которой должно быть сделано максимальным, минимальным или
же равным, какому-то конкретному значению;
 формула в целевой ячейке содержит ссылки на ряд изменяемых
ячеек. Поиск решения заключается в том, чтобы подобрать такие значения
переменных в изменяемых ячейках, которые бы обеспечили оптимальное
значение для формулы в целевой ячейке;
 может быть задано некоторое количество ограничений —
условий или соотношений, которым должны удовлетворять некоторые из
изменяемых ячеек.
Поиск решений может представлять свои результаты в виде трех отчетов: Результаты, Устойчивость и Пределы. Каждый отчет сохраняется на
отдельном листе текущей книги, а имена отчетов отображаются на ярлычках.
Отчет по устойчивости содержит информацию о том, насколько
целевая ячейка чувствительна к изменениям ограничений и переменных. В
отчете показывается, как целевая функция реагирует на увеличение значений изменяемых ячеек на единицу и на увеличение значения ограничения
на единицу.
Отчет по результатам содержит целевую ячейку, список изменяемых ячеек и ограничений. В этом отчете для каждого ограничения выводятся «статус» и «разница». Разница — это разность между значением, выводимым в ячейке ограничения при получении решения, и числом, задан-
43
ным в правой части формулы ограничения. Статус может принимать три
состояния: «Связанное» (ограничение, для которого значение разницы
равно 0), «Не связанное» (ограничение, которое было выполнено с ненулевым значением разницы) и «Не выполненное».
Отчет по пределам сообщает о том, в каких пределах значения изменяемых ячеек могут быть увеличены или уменьшены без нарушения ограничений задачи. Для каждой изменяемой ячейки этот отчет содержит
оптимальное значение, а также наименьшее и наибольшее значения, которые ячейка может принимать без нарушения ограничений.
Поиск решений может решить не каждую предложенную задачу. Если оптимальное решение не найдено, в окне диалога Результаты поиска
решений выводится сообщение о неуспешном завершении.
2.2.2Показ тенденций изменения данных с помощью спарклайнов
(инфокривых)
Спарклайны (инфокривые) – это небольшие диаграммы, размещающиеся в одной ячейке, которые позволяют визуально отобразить изменение значений непосредственно рядом с данными.
Таким образом, занимая совсем немного места, спарклайны призваны продемонстрировать тенденцию изменения данных в компактном графическом виде.
Рис.12. Спарклайны – графики
Поскольку спарклайны показывают тренды на ограниченном пространстве, с их помощью удобно создавать панели мониторинга и другие
аналогичные компоненты, демонстрирующие текущее состояние процесса
в понятном и наглядном виде.
44
Преимущество использования спарклайнов заключается в том, что:
 спарклайны, в отличие от диаграмм, можно распечатать при
печати листа, на котором они представлены;
 при использовании больших объемов данных усложняется
процесс построения диаграмм, при этом спарклайны позволяют легко
отобразить данные в графическом виде.
Чтобы добавить спарклайн, необходимо перейти на вкладку Вставка,
где на ленте располагается раздел Спарклайны.
Рис 13. Внешний вид раздела Спарклайны
Создание спаркайна включает три этапа:
1. выбор пустой ячейки или группы пустых ячеек, в которые необходимо вставить одну или несколько инфокривых;
2. выбор типа создаваемой инфокривой: График, Столбец или Выигрыш/проигрыш.
3. выбор диапазона ячеек с данными, на основе которых будут созданы спарклайны.
Так же как и диаграммы, спарклайны можно редактировать и настраивать. Для этого предназначена вкладка Работа со спарклайнами.
Рис.14. Вкладка Работа со спарклайнами
С помощью команд расположенных на данной вкладке можно:
 изменить данные спарклайна;
 изменить
тип
инфокривой
45
(график,
гистограмма,
выигрыш/проигрыш);
 управлять показом точек данных, т.е. выделять отдельные, либо
все маркеры (значения) данных, задавая их видимость. Данная функция
позволяет отображать на инфокривой максимальные, минимальные,
открывающиеся (первая точка), закрывающиеся (последняя точка),
отрицательные значения;
 изменить стиль спарклайна;
 задать собственные параметры форматирования спарклайна;
 управлять отображением и настройкой параметров оси. Можно
установить следующие параметры оси:
Тип оси дат - изменяет форму диаграммы в спарклайне в соответствии с неодинаковыми периодами на базе используемых данных.
Минимальное и максимальное значения для вертикальной оси спарклайна или группы спарклайнов. Это позволяет управлять масштабом,
представляя связь между значениями в более осмысленном виде.
Отобразить данные справа налево – меняет направление, в котором
располагаются данные на одном или нескольких спарклайнах.
 управлять отображением на инфокривой пустых и нулевых
значений.
2.2.3Анализ данных в Microsoft PowerPivot
PowerPivot — надстройка, с помощью которой можно собирать, хранить, моделировать и анализировать большие объемы данных в Ms Excel.
Рис. 15. Окно PowerPivot для Excel
46
Основные функции PowerPivot для Ms Excel:
 импорт больших объемов данных из различных источников;
 быстрые расчеты и анализ;
 практически неограниченная поддержка источников данных.
PowerPivot предоставляет возможность импортировать и комбинировать
источники данных из любого расположения для анализа больших объемов
данных, включая реляционные базы данных, многомерные источники, вебканалы данных, файлы Ms Excel, текстовые файлы и данные из Интернета;
 выражения анализа данных (DAX). DAX — это новый язык
формул, который расширяет возможности работы с данными в Ms Excel,
позволяя выполнять более сложные группировки, вычисления и анализ.
PowerPivot для Excel представляет собой отдельное Win32приложение (отдельное окно), предназначенное для работы с данными.
В это приложение можно импортировать данные из произвольных
источников, создать связи между колонками загруженных таблиц, создать
дополнительные расчетные колонки. Таким образом, в PowerPivot создается требуемая модель предметной области.
При сохранении файла Excel загруженные данные внедряются непосредственно в книгу Excel.
Объемы данных, с которыми можно работать в PowerPivot для Excel,
ограничены только объемом оперативной памяти, поэтому ее можно расширять по необходимости.
Процесс работы с PowerPivot состоит из трех основных шагов и одного дополнительного.
Загрузка и подготовка данных.
В первую очередь необходимо загрузить данные в PowerPivot и подготовить их для создания отчетов. В том числе необходимо создать связи
между таблицами из различных источников и при необходимости определить расчетные колонки с помощью языка DAX.
Интерфейс системы позволяет выбрать загрузку из широкого набора
баз данных, потоков данных, Excel-таблиц и буфера обмена.
47
Для того, чтобы можно было анализировать данные в разрезе справочников, интерфейс предоставляет возможности создания связей, как в
автоматическом, так и в ручном режиме.
В интерфейсе окна PowerPivot можно создать дополнительные расчетные колонки, что позволяет фильтровать данные по связанным колонкам и извлекать данные из связанных таблиц.
Разработка отчетов PowerPivot.
Отчеты PowerPivot могут быть созданы на основании построенной
модели PowerPivot с помощью трех инструментов:
 PivotTables (используются для создания сводных таблиц);
 PivotCharts (используются для создания сводных диаграмм);
 функции CUBE
произвольной форме).
(используются
для
создания
отчетов
в
Модели PowerPivot, на основании которых строятся отчеты, могут
быть локальными (то есть располагаться непосредственно в книге Excel),
либо можно использовать подсоединение к книге Excel, опубликованной
на портале SharePoint.
Опубликованные книги PowerPivot также могут стать источниками
данных для отчетов.
Форматирование отчетов PowerPivot.
После того как отчеты созданы, их можно отформатировать, т.е.
оформить в соответствии с требованиями, предъявляемыми к оформлению
документов организации.
Отчеты PivotTables и PivotCharts могут быть отформатированы с помощью:
 стандартных стилей;
 настроек форматирования.
Создание вычислений с помощью DAX
В модель PowerPivot можно добавлять сложные расчеты данных.
Для этого используется язык выражений DAX.
Расчеты в модель PowerPivot можно добавлять двумя способами:
 создавая вычисляемые колонки в окне PowerPivot. При этом
вычисления производятся для каждой строки в таблице.
48
 создавая меры, которые определяют новые поля в Панели задач
PowerPivot. В данном случае вычисления производятся когда поле
помещено в область значений объекта PivotTable или PivotChart.
2.3 Работа с электронными таблицами в режиме баз данных
База данных (date base) – это совокупность хранимых в памяти компьютера данных, которые отображают состояние некоторой предметной
области. Данные при этом взаимосвязаны и специальным образом организованы.
Ms Excel позволяет автоматизировать не только расчеты как таковые, но позволяет создавать и работать с разнообразными картотеками,
системами учета, базами данных и т.п.
Вопросы сбора данных, их хранения, учета и обработки можно решить, имея систему управления списками. Термин список используется в
Ms Excel для обозначения базы данных.
База данных – это особый тип рабочей таблицы, в которой не столько вычисляются новые значения, сколько размещаются большие объемы
информации в связанном виде.
База данных представляет собой последовательность записей, содержащую однозначно определенную по категориям и последовательности
информацию. Под каждую категорию данных в записи отводится отдельное поле, которому присваивается имя и отводится столбец.
Электронная таблица, оформленная в Ms Excel в виде списка, т.е.
таблицы, строки которой содержат однородную информацию, представляет собой простейшую базу данных.
Программа Ms Excel включает набор средств и функций, позволяющих выполнять все основные операции, присущие базам данных.
База данных в Ms Excel состоит из строк и столбцов. Строки таблицы, оформленной в виде списка, называются записями, а столбцы – полями
записей. Столбцам присваиваются уникальные имена полей, которые заносятся в первую строку списка – строку заголовка.
49
Создание базы данных обеспечивает интеграцию данных и возможность централизованного управления данными, снабжая информацией определенных пользователей.
Создание базы данных (БД) начинается с проектирования, т.е. с определения ее структуры: количества полей, их имен, типа каждого поля
(символьный, числовой, дата и т.д.), длины каждого поля (максимального
количества символов), типа данных (исходные, т.е. неизменяемые, или вычисляемые). Возможность использовать вычисляемые поля - основная особенность баз данных в Ms Excel.
Чтобы содержимое рабочего листа рассматривалось как база данных
в Ms Excel, необходимо придерживаться строгих правил:
 каждому полю записи соответствует один столбец рабочего
листа;
 столбцы базы данных должны идти подряд, без промежутков
между ними;
 в первой строке каждого столбца должен быть указан заголовок
соответствующего поля;
 заголовок поля должен занимать не более одной ячейки;
 содержимое ячейки заголовка должно быть уникально в пределах
рабочего листа;
 записи базы данных должны идти непосредственно ниже строки
заголовков;
 пустые строки не допускаются (признак окончания базы данных),
т.е. записи должны идти подряд, без промежутков между ними.
На рабочем листе, содержащем список, выделяют следующие области:
 диапазон данных – область, где хранятся данные списка;
 диапазон критериев – область на рабочем листе, в которой
задаются критерии для поиска информации. В диапазоне критериев
указываются имена полей и отводится область для записи условий отбора;
 диапазон для извлечения – область, в которую Ms Excel копирует
выбранные данные из списка.
Существуют следующие способы ввода данных в список:
50
 использование формы данных, которая автоматически создается
после определения заголовка списка с помощью команды Данные →
Форма;
 ввод данных во вставляемые в список пустые строки, т.е.
непосредственно ввод данных;
 использование средства Автоввод из списка для ускорения
работы;
 использование форм Ms Access и дальнейший перенос данных на
лист Ms Excel;
 использование мастера шаблонов для преобразования рабочего
листа в Ms Excel в форму;
 применение VBA – соответствующая программа будет
предоставлять форму или окно диалога для ввода данных и их
последующего помещения в определенные ячейки рабочего листа Ms
Excel.
К наиболее часто используемым способам ввода данных в базу данных относятся первые три способа.
Работа с подготовленным списком в Ms Excel может осуществляться
по следующим направлениям:
1. Сортировка.
Любая информация, в том числе и база данных, должна быть упорядочена. Каждая база данных имеет некоторый предпочтительный порядок
поддержания и просмотра записей. Процесс упорядочения базы данных
называется сортировкой. Порядок сортировки записей определяется конкретной задачей. При сортировке изменяется порядок следования записей
в базе данных или таблице. Таким образом, происходит изменение базы
данных.
Однако необходимо иметь возможность восстановить исходный порядок следования записей. Универсальным средством для этого является
введение порядковых номеров записей. В сочетании со средствами Ms
Excel по восстановлению данных это полностью защитит базу от потерь
при случайных сбоях в работе.
51
Команда Сортировка устанавливает порядок строк в таблице в соответствии с содержимым конкретных столбцов.
Сортировка по возрастанию предполагает следующий порядок:
 числа
 текст, включая текст с числами (почтовые индексы, номера
автомашин)
 логические значения
 значения ошибок
 пустые ячейки
Сортировка по убыванию происходит в обратном порядке. Исключением являются пустые ячейки, которые всегда располагаются в конце
списка.
При сортировке по возрастанию текстовые данные упорядочиваются
в алфавитном порядке от А до Я. Числовые данные упорядочиваются по
возрастанию значений от минимального к максимальному. Даты упорядочиваются от наиболее ранней даты к наиболее поздней. При выборе переключателя по убыванию порядок сортировки изменяется на противоположный.
Рис.16. Диалоговое окно Сортировка
Дополнительные параметры Сортировки Затем и В последнюю очередь, позволяют определить порядок вторичной сортировки для записей, в
которых имеются совпадающие значения.
Функция Параметры Сортировки позволяет:
52
 определить пользовательский порядок сортировки для столбца;
 сделать сортировку чувствительной к использованию прописных
и строчных букв;
 изменить направление сортировки (вместо сортировки сверху
вниз установить сортировку слева направо).
Пример использования функции Сортировки приведен на рисунке17.
Рис. 17. Пример использования Сортировки списка
2. Фильтрация (отбор данных).
Фильтрация – извлечение записей данных из списка в соответствии с
некоторыми требованиями (критериями).
Фильтр обрабатывает весь список в электронной таблице и отображает на экране только те данные, которые требуется. Преимущество этого
подхода заключается в том, что с такой выборкой можно работать автономно как с новой таблицей, не загружая экран избыточной информацией.
Для фильтрации данных в Ms Excel существует 2 средства: автофильтр и расширенный фильтр.
При использовании Автофильтра фильтрация осуществляется непосредственно на исходных данных.
С помощью Автофильтра можно создать фильтры трех типов: по
списку значений, по формату или по условиям. Все они являются взаимоисключающими в пределах диапазона ячеек или столбца таблицы.
53
Критерии расширенной фильтрации списка можно определить, непосредственно задав их на рабочем листе. Преимущество этого способа состоит в том, что пользователь всегда имеет четкое представление о применяемых критериях и при необходимости может их изменять. При использовании расширенного фильтра критерии задаются в отдельной области.
Данные базы данных могут быть подвергнуты анализу. Ms Excel
предоставляет широкие возможности для проведения анализа данных, к
которым относятся:
1. Структуризация рабочих листов, цель которой заключается в
разбиении данных, содержащихся на рабочем листе, на определенные
уровни детализации. Используя структуру, легче проводить анализ и сравнение данных, поскольку в таком случае при необходимости группы уровня могут быть скрыты, а затем снова отображены.
На рисунке 18 приведен пример таблицы, сформированной для
группировки данных.
Рис.18. Пример таблицы для группировки данных
На рисунке 17 приведена таблица данных после группировки. В левой верхней части окна можно увидеть кнопки 1 и 2, появившиеся после
группировки. Они служат для группового отображения или скрытия данных.
Каждый из уровня группировки может содержать подуровень.
Ранее сгруппированные строки всегда можно разгруппировать.
54
Рис.19. Структура таблицы со сгруппированными строками
2. Автоматическое подведение итогов: итоги необходимы для создания разнообразных отчетов и для обобщения большого количества однотипной информации. Один из способов обработки и анализа базы данных
состоит в подведении различных итогов. С помощью команды Итоги можно вставить строки итогов в список, осуществив суммирование данных
нужным способом.
Команда Итоги может выполнять следующие операции:
 выбрать одну или несколько групп для автоматического
подведения итогов по этим группам
 выбрать функцию для подведения итогов
 выбрать данные, по которым нужно подвести итоги
Кроме подведения итогов по одному столбцу, автоматическое подведение итогов позволяет:
 выводить одну строку итогов по нескольким столбцам
 выводить многоуровневые,
нескольким столбцам
 выводить
многоуровневые
вложенные
строки
строки
итогов
с
итогов
различными
способами вычисления для каждой строки
 скрывать или показывать детальные данные в этом списке
55
по
3. Консолидация данных - предназначается для обобщения однородных данных и выполняется в том случае, когда нужно подытожить
данные, хранящиеся на разных листах или в различных книгах. При помощи функции консолидации для значений из несмежных диапазонов можно
выполнить те же операции, что и при автоматическом определении промежуточных итогов: вычислить сумму, минимальное, максимальное или
среднее значение и т. д.
В зависимости от способа организации исходные данные для Консолидации можно задавать тремя способами: с помощью формул, содержащих ссылки, по расположению ячеек и по заголовкам строк или столбцов.
Способ расчета с помощью формул, содержащих ссылки, следует
использовать в тех случаях, когда исходные данные расположены бессистемно.
В этом случае для расчета итоговых данных составляются обычные
или так называемые, трехмерные формулы — ссылки вида 'Лист1:Лист3'
!D3, которые содержат ссылки на диапазоны, включающие ячейки разных
листов.
Методы определения исходных данных для консолидации — по расположению ячеек и по заголовкам строк или столбцов — реализуются в
диалоговом окне Консолидация.
В способе, использующем расположение ячеек, исходные данные
для функции консолидации задаются в диалоговом окне в виде ссылок на
диапазоны. При этом требуется, чтобы исходные данные в каждом диапазоне имели одинаковую структуру.
В способе, использующем заголовки строк или столбцов, ячейки, содержащие исходные данные для функции консолидации, идентифицируются по заголовкам, а не путем указания их адресов. В этом случае не обязательно, чтобы исходные данные на разных листах были расположены
одинаково.
4. Сводные таблицы - представляют собой средства для группировки, обобщения и анализа данных, находящихся в списках Ms Excel или
таблицах, созданных в других приложениях.
56
Сводная таблица является интерактивным средством, позволяющим
получить данные из некоторого источника (список, таблица, база данных)
и выполнить их анализ и просмотр различными способами. С помощью
сводной таблицы можно просмотреть необходимую для анализа часть
имеющейся информации, отфильтровать данные и разместить результаты
на различных рабочих страницах, отсортировать данные, автоматически
сгенерировать итоги.
Сводную таблицу можно создать на базе области таблицы, целой
таблицы или нескольких таблиц. Исходные данные для сводных таблиц
должны быть организованы в виде списка, каждая строка которого содержит одни и те же категории информации, приведенные в определенном
порядке. В первой строке списка должны находиться заголовки столбцов.
Они используются для создания полей сводной таблицы и идентификации
отдельных элементов данных, то есть уникальных значений полей, взятых
из списка. Несмотря на то, что поля создаются на основе столбцов, содержащиеся в них данные можно размещать как в столбцах, так и в строках
сводной таблицы.
Сводная таблица включает следующие поля:
 области столбцов и строк - содержат поля, по которым
производится сравнение или анализ;
 область элементов данных - отображает поля, предназначенные
для расчетов итоговых показателей;
 область страниц - используется, в том случае, когда нужно
создать несколько однотипных отчетов, задаваемых полями в других
областях.
Размещение полей непосредственно в рабочем листе дает определенные преимущества, обеспечивая наглядность построения при наличии
небольшого объема данных. Однако при работе с большим количеством
данных более удобным и быстрым является режим макета. Если при перетаскивании поля непосредственно в область данных рабочего листа туда
перемещаются также все данные, связанные с этим полем, то в режиме макета выполняется лишь размещение кнопок полей, а относящиеся к ним
данные не затрагиваются.
57
После создания сводной таблицы справа от поля страницы и в остальных полях сводной таблицы появляются кнопки co стрелками. После
щелчка на такой кнопке появляется окно со списком элементов данных соответствующего поля. Устанавливая и сбрасывая флажки в этом окне,
можно управлять отображением элементов поля.
Сводная таблица динамически связана с базой данных, использовавшейся при ее создании. Если значения в базе данных изменились, при
выполнении функции обновления, Ms Excel обновляет сводную таблицу с
учетом всех произведенных изменений.
58
3
ТЕСТОВЫЕ ЗАДАНИЯ
1. Какая программа обеспечивает ввод, редактирование и сохранение любого
символьного текста и предназначена в основном для подготовки текстов программ:
МS Word
Блокнот
OpenOffice Writer
2. Документ, оформленный с использованием уже существующих тем, стилей и
макетов, содержащий замещающий текст называется
шаблон
макрос
документ
3. При запуске МS Word первый автоматически создаваемый документ базируется на шаблоне, который называется:
Обычный
Стандартный
Новый документ
4. Вкладка главного меню МS Word, позволяющая работать с элементами
управления Шаблона носит название:
Разметка страницы
Рецензирование
Разработчик
5. Элементами управления Шаблоном являются:
Флажок
Форматированный текст
Список
Обычный текст
Таблица
Раскрывающийся список
Рисунок
Рамка
Дата
Коллекция стандартных блоков
6. Набор команд, с помощью которых можно автоматизировать выполнение
повторяющейся задачи называется
макрос
шаблон
язык программирования
59
7. Для создания макросов, используемых в приложениях MS Office, применяется язык:
Delphi
Visual Basic
Microsoft Visual Basic for Applications
8. Каким способом можно создать макрос
записав последовательность необходимых действий с помощью макрорекордера
написать код программы, при помощи программных средств
9. Отметьте недостатки языка программирования Microsoft Visual Basic for
Applications (VBA)
программа на VBA не может существовать отдельно от документа приложения Office
возможность видеть внешний вид будущей программы еще на этапе разработки и изменять его
создающие довольно громоздкие и медленные программы
10. Основными элементами интегрированной среды Visual Basic являются:
строка меню
строка состояния
контекстное меню
панели инструментов
11. Основные понятия, которыми оперирует язык программирования Microsoft
Visual Basic for Applications (VBA):
предмет
объект
свойство
действие
метод
результат
событие
12. Объектом Visual Basic for Applications является?
открытый документ Word
слово
подчеркивание
предложение
регистр букв
рисунок
13. Свойством объекта Visual Basic for Applications является:
выделение жирным
60
наличие рисунка
выделение цветом
наличие автоматической расстановки переносов
наличие таблицы
наличие автоматической проверки орфографии
14. Методом Visual Basic for Applications является:
печать текста
поиск текста в документе
замена объекта
ввод текста
15. Структура макроса включает:
ключевое слово Sub
имя макроса
комментарии
тело макроса
макрооператоры
ключевое слово End Sub
16. Объектная модель МS Word включает пять основных объектов:
объект Application
объект Document
объект Selection
объект Range
объект Bookmark
объект Sub
17. Если необходимо создать набор однотипных документов, каждый из которых содержит уникальные элементы, следует использовать:
шаблоны
метод Слияния документов
копирование документов
18. Метод Слияния документов использует следующие виды документов
основной документ
дополнительный документ
источник данных
19. Процесс слияния состоит из нескольких этапов:
создание основного документа
создание или выбор существующего источника данных
создание документа с дополнительной информацией
вставка полей слияния
вывод стандартного документа
61
20. Какие средства анализа данных в Ms Excel способны принимать наборы
входных значений и определять возможные результаты
сценарии
поиск решения
таблицы данных
средство подбора параметров
21. Какие средства анализа данных в Ms Excel способны принимать результат и
определять возможные входные значения, обеспечивающие получение этого результата
сценарии
поиск решения
таблицы данных
средство подбора параметров
22. Небольшие диаграммы, размещающиеся в одной ячейке, которые позволяют
визуально отобразить изменение значений непосредственно рядом с данными называются
спарклайны
графики
диаграммы
23. В Ms Excel использует три типа инфокривых:
график
диаграмма
гистограмма
выигрыш/проигрыш
24. Надстройка, с помощью которой можно собирать, хранить, моделировать и
анализировать большие объемы данных:
Data Mining
PowerPivot
Structured Query Language (SQL)
25. Основные функции PowerPivot для Excel включают:
импорт больших объемов данных
быстрые расчеты и анализ
легкое отображение данных в графическом виде
неограниченная поддержка источников данных
выражения анализа данных (DAX)
26. Последовательность записей, содержащая однозначно определенную по категориям и последовательности информацию называется
список Ms Excel
электронная таблица Ms Excel
база данных Ms Excel
62
27. Содержимое рабочего листа рассматривается как база данных в MS Excel
при соблюдении правил:
каждому полю записи соответствует один столбец рабочего листа
столбцы базы данных должны идти подряд, без промежутков между ними
все строки обязательно должны иметь порядковый номер
в первой строке каждого столбца должен быть указан заголовок соответствующего поля
заголовок поля должен занимать не менее одной ячейки
содержимое ячейки заголовка должно быть уникально в пределах рабочего
листа
пустые строки не допускаются
28. Работа с подготовленным списком в MS Excel может осуществляться по следующим направлениям:
Сортировка
Консолидация
Подсчет промежуточных итогов
Фильтрация
29. На рабочем листе MS Excel, содержащем базу данных, выделяют следующие
области:
диапазон области страниц
диапазон данных
диапазон критериев
диапазон области столбцов
диапазон для извлечения
30. Выделяют следующие типы сортировки:
по возрастанию
по убыванию
слева направо
31. Для фильтрации данных в Ms Excel существуют следующие средства:
настраиваемый фильтр
автофильтр
расширенный фильтр
32. Для того чтобы подытожить данные, хранящиеся на разных листах или в
различных книгах, используют средство:
промежуточные итоги
консолидация
группировка
33. Какие существуют методы определения исходных данных для консолидации:
63
с помощью формул, содержащих ссылки
с помощью формул, содержащих функции
по расположению ячеек
по заголовкам строк или столбцов
34. Сводная таблица включает следующие поля:
области столбцов и строк
область критериев
область элементов данных
область страниц
область для отображения данных
35. Средство для группировки, обобщения и анализа данных, находящихся в базах данных Ms Excel или таблицах, созданных в других приложениях, называется
сводная таблица
таблица данных
средство консолидации
36. Вкладка главного меню МS Word, позволяющая работать с элементами
управления Шаблона:
Разметка страницы
Рецензирование
Разработчик
37. Какие действия необходимо выполнить в Excel 2010, чтобы открыть средства
анализа «что-если»?
Сервис/Надстройка
Данные/Работа с данными
Данные/Анализ данных
38. Чтобы открыть шаблон в МS Word 2010 следует проделать путь:
Файл/Создать/Шаблоны
Сервис/Шаблоны и надстройки
Разработчик/Элементы управления
39. С помощью этой надстройки можно собирать, хранить, моделировать и анализировать большие объемы данных в Ms Excel
PowerPivot
Спарклайны
Pivot table
64
СПИСОК ЛИТЕРАТУРЫ
1. Гаврилов, М.В. Информатика и информационные технологии :
учеб. для бакалавров. для студентов вузов, обучающихся по юрид. специальностям / М.В. Гаврилов, В.А. Климов .— 2-е изд., испр. и доп. — М. :
Юрайт, 2012 .— 350 с.
2. Информатика : базовый курс : учеб. пособие для студентов высш.
техн. учеб. заведений / под ред. С. В. Симоновича .— 2-е изд. — М. ; СПб.
: Питер, 2009 .— 640 с.
3. Информатика : учеб. пособие для студентов вузов, обучающихся
по специальности 060800 "Экономика и управление на предприятии АПК"
/ А. П. Курносов (и др.); под ред. А.П. Курносова.— М. : КолосС, 2006 .—
272 с.
4. Информатика: учеб. пособие / под ред. Б.Е. Одинцова, А.Н. Романова .— 2-е изд., перераб. и доп. — М. : Вузовский учебник : ИНФРАМ, 2012 .— 409 с.
5. Курбатова Е.А. Microsoft Office Excel 2007. Самоучитель / Е.А.
Курбатова. - Киев: Диалектика-Вильямс, 2008. - 384 с.
6. Новожилов О.П. Информатика : учебное пособие для бакалавров.
для студентов вузов, обучающихся по специальностям группы "Экономика
и управление" и направлению "Информатика и вычислительная техника" /
О.П. Новожилов .— 2-е изд., испр. и доп. — Москва: Юрайт, 2012.— 564 с.
7. Рудикова Л.В. Microsoft Excel для студента / Л.В. Рудикова. –
СПб.: БХВ-Петербург, 2007. - 368 с.
8. Сергеев А.П. Использование Microsoft Office Excel 2007 / А.П.
Сергеев. – Киев: Диалектика-Вильямс, 2007. - 288 с.
9. Тихомиров А.Н. Самоучитель Microsoft Office 2007. Все программы пакета / А.Н. Тихомиров. - СПб.: Наука и техника, 2008. –
608 С.
10. Microsoft. – Режим доступа: [URL: http://office.microsoft.com/ruru/support/results.aspx?ctags=CH010369467]
11. Microsoft. – Режим доступа: [URL: http://office.microsoft.com/ruru/word-help/RZ102673170.aspx]
65
Курс лекций
Мистюкова Светлана Васильевна
Инструментальные средства обработки информации
Издается в авторской редакции.
Подписано в печать 26.02.2014 г. Формат 60х801/16
Бумага кн.-журн. П.л. 4,13 Гарнитура Таймс.
Тираж 35 экз. Заказ № 9438
Федеральное государственное бюджетное образовательное учреждение
высшего профессионального образования
«Воронежский государственный аграрный университет имени императора Петра I»
Типография ФГБОУ ВПО Воронежский ГАУ 394087, Воронеж, ул. Мичурина, 1
Информационная поддержка: http://tipograf.vsau.ru
Отпечатано с оригинал-макета заказчика. Ответственность за содержание
предоставленного оригинал-макета типография не несет.
Требования и пожелания направлять авторам данного издания.
66
1/--страниц
Пожаловаться на содержимое документа