close

Вход

Забыли?

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

?

Tehnologii elektron tablic konspekt lekcij

код для вставкиСкачать
Федеральное агентство связи
Федеральное государственное образовательное бюджетное учреждение
высшего профессионального образования
ПОВОЛЖСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ
ТЕЛЕКОММУНИКАЦИЙ И ИНФОРМАТИКИ
ЭЛЕКТРОННАЯ
БИБЛИОТЕЧНАЯ СИСТЕМА
Самара
Федеральное агентство связи
Федеральное государственное образовательное бюджетное учреждение
высшего профессионального образования
«Поволжский государственный университет телекоммуникаций и
информатики»
___________________________________________________
Кафедра информационных систем и технологий
КОНСПЕКТ ЛЕКЦИЙ
ПО УЧЕБНОЙ ДИСЦИПЛИНЕ
«ТЕХНОЛОГИИ
ЭЛЕКТРОННЫХ ТАБЛИЦ»
по специальности (направлению подготовки):
Информационные системы и технологии
Самара
2013
2
УДК 004.67
Салмин А.А.
Технологии электронных таблиц. Конспект лекций. – Самара.: ФГОБУ ВПО
«ПГУТИ», 2013. – 136 с.
Рассматриваются основные элементы работы в электронной таблице на
примере MS Excel 2003. Приводятся как базовые возможности работы с
табличными данными, такие как: редактирование, форматирование данных,
работа с формулами, так и функции, выполняющие сложные расчеты или
решающие различные оптимизационные задачи. Также рассматриваются
базовые элементы среды разработки программ, основанные на конструкциях
алгоритмического языка Visual Basic for Application.
Рецензент:
Тарасов В.Н. – д.т.н., профессор, зав. кафедрой «Программного обеспечения
и управления в технических системах» ПГУТИ
Федеральное государственное образовательное бюджетное учреждение
высшего профессионального образования
«Поволжский государственный университет телекоммуникаций и
информатики»
© Салмин А.А., 2013
3
Содержание
Введение ……………………………………………….. 5
Лекция 1
Раздел 1. Основные понятия электронных таблиц …..
1.1. Технологии работы в электронной таблице…....
1.2. Формирование структуры таблицы……...……...
1.3. Ввод, редактирование и форматирование
данных…………………………………………….…...
7
7
11
12
Лекция 2
Раздел 2. Формулы и функции ………...……………...
2.1. Ввод и редактирование формул………..……….
2.2. Использование функций…………………..…….
2.3. Использование ссылок и имен…………..……...
17
18
20
24
Лекция 3
Раздел 3. Работа со списками и базами данных в
электронных таблицах …………………………………
3.1. Построение графиков и диаграмм..…………….
3.2. Сводные таблицы…………………...……………
3.3. Работа со списками и базами данных……...…...
28
28
33
37
Лекция 4
Раздел 4. Анализ табличных данных средствами
электронной таблицы …………………………………..
4.1. Подбор параметра…………...…………………...
4.2. Таблицы подстановки………...………………….
4.3. Сценарии…………………...……………………..
4.4. Консолидация данных……...……………………
4.5. Поиск решения…………………………………...
51
51
53
57
59
60
Лекция 5
Раздел 5. Работа с макросами ……………………….... 67
5.1. Макросы……………..…………………………... 67
5.2. Элементы управления на рабочем листе…...….. 75
Лекция 6
Раздел 6. Основы VBA (Visual Basic for
Application)………………………………………………
Тема: Основы VBA……………………………………..
6.1. Объекты, методы, свойства……………………..
6.2. Структура, используемая в VBA………………..
84
84
84
87
4
Лекция 7
Тема: Работа с данными в VBA …………………...….. 89
6.3. Типы данных, используемые в VBA…………… 89
6.4. Использование
стандартных
окон
операционной системы Windows……………………. 93
Лекция 8
Тема: Управляющие конструкции VBA ..……………. 100
6.5. Управляющие конструкции VBA………………. 100
6.6. Использование элементов управления для
запуска макроса и ввода данных……………………. 107
Лекция 9
Тема: Пользовательские формы, создаваемые в VBA. 112
Лекция 10
Раздел 7. Обмен данными в электронных таблицах…. 120
Раздел 8. Интернет - технологии
в
электронных
таблицах ………………………………………………... 127
Глоссарий ……………………………………………… 133
5
Введение
Предлагаемый конспект лекций по дисциплине «Технологии электронных
таблиц» обеспечивает подготовку студентов к эффективному использованию
современных компьютерных средств обработки табличных данных.
Предлагаются основные темы, посвященные работе с табличными
процессорами на примере MS Excel, после изучения которых студенты смогут
работать с электронными таблицами, строить диаграммы, графики,
анализировать информацию с помощью встроенных в Excel функций, создавать
макрокоманды, пользовательские формы и др. В рамках конспекта лекций по
дисциплине рассматриваются теоретические основы работы с табличными
процессорами, а также практические вопросы создания и работа с данными не
только со стандартными функциями, созданных средствами приложения, но и
использование комплексных возможностей для решения сложных задач
оптимизации и макропрограммирования. Таким образом, у будущих
специалистов осуществляется формирование основ теоретических знаний и
практических навыков работы в области создания, функционирования и
использования электронных таблиц.
Дисциплина «Технологии электронных таблиц» базируется на знании
предмета «Основы информатики и информационных технологий», изучаемого
в образовательных учреждениях среднего (полного) образования. Также данная
дисциплина требует предварительного изучения курсов «Информатика»,
«Информационные технологии», изучаемых в образовательных учреждениях
высшего образования. В связи с этим в курсе опускается освещение таких
вопросов как: открытие и сохранение документа, использование справки и
другие вопросы, которые должны быть освящены в рамках изученного
материала.
Элементы курса «Технологии электронных таблиц» используются при
изучении курсов «Моделирование систем», «Статистический и регрессионный
анализ».
Задача материала данного конспекта лекций в том, чтобы:
- предоставить студентам общие сведения о принципах построения и
функционирования электронных таблиц данных;
- показать методы, средства и технологии электронных таблиц: ввода и
обработки данных; решения индивидуальных задач и задач оптимизации
при помощи табличных процессоров;
- показать разнообразие возможностей, имеющихся в табличных
процессорах, для решения разнообразных задач.
Знания и навыки, полученные в результате изучения данной дисциплины,
могут быть применены:
1. при самостоятельном создании информационных систем с использованием
технологии электронных таблиц для выполнения своих профессиональных
функций;
2. для создания документов различной формы на основе приложений
электронных таблиц;
6
3. при формулировании технического задания при создании ИС силами
профессиональных разработчиков.
7
Лекция 1
Раздел 1. Основные понятия электронных таблиц
1.1. Технологии работы в электронной таблице
К категории табличных процессоров относят интерактивные компьютерные
программы, предназначенные для создания и обработки документов в виде
таблиц данных.
Электронными таблицами (ЭТ) называют программы обработки
крупноформатных электронных динамических таблиц.
Достоинство табличных процессоров:
упрощение выполнения финансовых, научных и других видов расчета;
возможность ввода простых баз данных для учета материалов, товаров,
денег, времени и т.д.;
возможность оформления всех видов документов, а также возможность
быстрого анализа данных и представление их в графическом виде.
Основу программы составляют: вычислительно – калькуляционный модуль,
модуль диаграмм, доступ к внешним базам данных, модуль программирования
индивидуальных задач. Каждый из этих модулей отвечает за определенные
действия, проводимые над данными. Так вычислительно-калькуляционный
модуль отвечает за все вычислительные действия, производимые в
приложении, такие как: расчеты, связь данных, использование стандартных
функций и мн.др. Модуль диаграмм отвечает за построение различных типов
гистограмм, графиков и их связь с исходными данными. Модуль доступа к
внешним базам данных позволяет связать внешние базы данных с приложением
электронных таблиц, импортировать данные, добавлять поля по средствам
устанавливаемым связей, а также производить экспорт в другие приложения.
Модуль программирования индивидуальных задач, в отличие от трех
предыдущих, решает пользовательскую задачу на уровне программирования,
что позволяет рассматривать электронные таблицы как средство,
автоматизирующее
выполнение каких-либо действий, представленных в
программном коде.
На рисунке 1.1 представлена обобщенная схема технологии работы в
электронной таблице.
На первом этапе происходит формирование структуры электронной таблицы,
на выходе которого образуется таблица с исходными данными и формулами,
позволяющими производить математические действия над значениями, а также
получить общую статистику изменения данных. Отличительной особенностью
первого этапа является тот факт, что все действия в нем должны производиться
в строгой последовательности. Так, например, без ввода исходных данных нет
смысла в воде формул.
На втором этапе производится работа с данными, под которой
подразумевается моделирование, работа с базами данных, решение
оптимизационных и индивидуальных задач пользователя. Выделенные на
8
данном этапе математические модели помогают пользователю на основе
имеющейся таблицы получить новую информацию при решении типовых задач
компьютерного моделирования, таких как:
Что будет, если …?
Как сделать, чтобы …?
Анализ чувствительности
Статистический анализ и прогноз
Анализ с помощью встроенных функций
Оптимизационные задачи.
I
Формирование структуры
электронной таблицы
Ввод заголовка и шапки таблицы
Ввод исходных данных
II
Ввод формул
III
Представление
входных
данных в
графическом
виде
Работа с данными
II
I
1. Моделирование
2. Работа с базами
данных
II
IV
I
Печать
3. Одновременная работа с
несколькими таблицами
Рис.1.1. Технология работы в электронной таблице
Решение задачи «Что будет, если…?» дает возможность узнать, как
изменяются выходные параметры при изменении одной или нескольких
входных величин или условий.
Задачи «как сделать, чтобы…?» (обратная «что будет, если…?») возникает в
случае, если цель состоит в достижение определенных значений модели и
определении значений входных параметров, обеспечивающих достижение этой
цели.
Задачи анализа чувствительности позволяют определить, как изменяется
решении при изменении одной или нескольких входных величин с заданным
шагом изменения в определенном диапазоне значений.
9
Статистический анализ и прогноз применим для предсказания и оценки
будущих значений.
Различные виды анализов данных, содержащиеся в исходной таблице, на
данном этапе можно проводить с использованием встроенных функций и
процедур. Кроме того, можно отметить, что в отличие от предыдущего этапа,
на данном этапе не соблюдается последовательность решения задачи. Так
может быть реализован только один из подэтапов или группа, состоящая из
двух или всех трех.
Третий этап позволяет представить данные числового типа графически с
помощью различных диаграмм, причем данные для построения графиков могут
быть взяты как из первого, так и из второго этапа. Кроме того, при изменении
исходных данных автоматически изменяются изображение элементов диаграмм
по размеру или месту положения. Существует 2 вида диаграмм:
1) внедренные диаграммы (сохраненные на рабочем листе вместе с
числовыми данными);
2) диаграммы-листы (размещены на отдельных листах).
Четвертый этап работы в электронной таблице является заключительным и
отвечает за сохранения документа в виде, удобном для пользователя:
электроном или бумажном.
1.2. Формирование структуры таблицы
Программы электронных таблиц (например, Microsoft Excel, Calc)
предназначены для работы с таблицами данных,
содержащими
преимущественно числовую информацию. При формировании таблицы
выполняют ввод, редактирование и форматирование текстовых и числовых
данных, а также формул. В дальнейшем в качестве примера будет использовано
одно из самых распространенных приложений электронных таблиц Microsoft
Excel 2003.
Документ электронной таблицы называется рабочей книгой, которая
представляет собой набор рабочих листов, каждый из которых имеет
табличную структуру.
Рабочий лист состоит из строк и столбцов. Столбцы озаглавлены
прописными латинскими буквами и, далее, двухбуквенными комбинациями.
Всего рабочий лист может содержать до 256 столбцов, пронумерованных от A
до IV. Строки последовательно нумеруются цифрами, от 1 до 65536.
На пересечении строк и столбцов образуются ячейки таблицы, которые
являются минимальными элементами для хранения данных. Отдельная ячейка
может содержать данные, относящиеся к одному из трех типов текст, число или
формула, а также оставаться пустой.
На рисунке 1.2 представлена структура ячейки электронной таблицы.
10
Рис. 1.2. Структура ячейки
Структура ячейки:
1-й уровень содержит видимое на экране изображение (т.е.
отформатированный текст) или результат вычисления формулы).
2-й уровень содержит форматы ячейки (формат чисел, шрифты, выключатель
(включатель) признак показывать или нет ячейку, вид рамки, защита ячейки).
3-й уровень содержит формулу, которая может состоять из текста, числа или
встроенных функций.
4-й уровень содержит имя ячейки, это имя может использоваться в формулах
других ячеек, при этом обеспечивается абсолютная адресация данной ячейки.
5-й уровень содержит примечания данной ячейки (произвольный текст). Если
ячейка содержит примечание, то в правом верхнем углу появляется красный
квадратик (точка).
Каждая ячейка имеет адрес - обозначается именем столбца и именем строки.
Например, А2. Для работы с несколькими ячейками сразу необходимо
выделить блок ячеек.
1.3. Ввод, редактирование и форматирование данных
Ввод данных осуществляется непосредственно в текущую ячейку или в
строку формул.
Существует три типа данных: текст, число, дата и время.
ТЕКСТОМ является любая последовательность, состоящая из цифр,
пробелов и нецифровых символов. Веденный текст автоматически
выравнивается в ячейке по левому краю.
ЧИСЛА, введенные в ячейку, интерпретируются как константы. Все другие
сочетания клавиш, а исключением «+», «-», «,», состоящие из цифр и
нецифровых символов, рассматриваются как текст. Веденный текст
автоматически выравнивается в ячейке по правому краю.
В ячейки таблицы можно вводить даты и время суток, которые
интерпретируются как число. Для отображения времени суток в 12-часовом
формате следует вводить букву а или р, отделенную пробелом от значения
времени, пример 9:00 р. Иначе время будет интерпретировано в 24-часовом
формате. Для определения текущей даты используется функция СЕГОДНЯ().
Для ввода даты используется функция ДАТА(год, месяц, дата).
Вычисления в таблицах осуществляется при помощи формул, которые могут
содержать числовые константы, ссылки на ячейки и функции, соединенные
знаками математических операций.
11
Для выбора формата данных ячейки используется диалоговое окно Формат
ячеек, вызываемое с помощью команды Формат – Ячейки (или Ctrl+1) (рис.1.3).
Рис. 1.3. Диалоговые окна изменения формата ячейки
Помимо стандартного приема форматирования, Excel позволяет производить
форматирование ячейки с учетом хранящейся в ней данных. Такое
форматирование называется условным, так как выполняется на основании
результатов проверки определенных условий. В условии могут проверяться как
числовые данные, так и текстовые. Для выполнения условного форматирования
необходимо: Формат – Условное форматирование.
Рис. 1.4. Условное форматирование данных
При работе с большими таблицами появляется необходимость в скрытии
некоторых столбцов или строк. Для этого используется команда меню Формат
– Строка (Столбец) – Скрыть. Если нужно запретить отображение всего листа:
Формат – Лист – Скрыть. Для восстановления отображения строки или столбца
нужно выделить две смежные ячейки соседних строк или столбцов и выбрать
команду Формат – Строка (Столбец) – Отобразить.
Кроме того, возникает необходимость использовать иерархическую
структуру таблицы.
Под термином «структурирования» подразумевается распределение данных
по уровням иерархической структуры. Путем отмены (восстановления)
отображения отдельных уровней можно добиться представления на экране
только необходимой информации. Структурирование позволяет значительно
упростить работу с содержащимися в таблице данными.
Разбивка на уровни структуры может выполняться как по горизонтали, так и
по вертикали. Существует автоматическое структурирование и группирование
данных.
При автоматическом структурировании уровни структуры таблицы должны
быть «заложены» изначально при построении таблицы (например, исходные
12
данные (один уровень) и результаты суммирования (второй уровень)). Для
автоматического структурирования таблицы необходимо: разместить указатель
ячейки в области таблицы, а затем произвести: команда Данные – Группа и
структура – Создание структуры.
Для удаления структуры вызвать команду Данные – Группа и структуры –
Удалить структуру.
Кроме автоматического структурирования представляется возможность
самостоятельно группировать данные по уровням структуры, вставлять и
удалять отдельные уровни: Данные – Группа и структура – Группировать.
Пример иерархического представления данных представлено на рисунке 1.5.
Рис. 1.5. Пример структурирования
Для более удобного представления информации, а также ее использования
другими пользователями к содержимому ячеек можно добавить
дополнительные пояснения. Чтобы не нарушить структуру таблицы,
дополнительную информацию следует представлять в виде примечаний. Для
этого необходимо: выделить ячейку – Вставка – Примечание.
Рис. 1.6. Пример примечания
Существует три режима отображения примечаний: только индикатор,
примечание и индикатор и не отображать. Эти режимы устанавливаются на
вкладке Вид диалогового окна Параметры.
Вопросы для самоконтроля
1. Дайте определение электронной таблице.
2. Какие модули используется в электронной таблице?
3. Перечислите основные элементы технологии хранения информации.
13
4. Что является минимальным элементом хранения информации в электронной
таблице?
5. Что подразумевается под текстом в электронной таблице?
6. Что подразумевается под структурированием?
7. Для чего используется примечание?
14
Лекция 2
Раздел 2. Формулы и функции
Основным средством анализа и обработки, вводимых в таблицу данных,
являются формулы. С их помощью можно складывать, умножать и сравнивать,
производить над ними другие операции. Для выполнения стандартных
вычислений в электронной таблице предлагают большое количество
встроенных функций, которые можно вызывать в формулах.
Помимо чисто вычислительных действий с отдельными числами, в
электронных таблицах есть возможность обрабатывать отдельные строки или
столбцы таблицы, а также целые блоки ячеек. В частности, находить среднее
арифметическое, максимальное и минимальное значение, средне-квадратичное
отклонение, наиболее вероятное значение, доверительный интервал и многое
другое.
Для удобства работы функции разбиты по категориям: функции управления
базами данных и списками, функции даты и времени, DDE/Внешние функции,
инженерные функции, финансовые, информационные, логические, функции
просмотра и ссылок. Кроме того, присутствуют следующие категории функций:
статистические, текстовые и математические. При помощи текстовых функций
обрабатывается текст: извлекаются символы, находятся нужные, записываются
символы в строго определенное место текста и многое другое. С помощью
функций даты и времени можно решить практически любые задачи, связанные
с учетом даты или времени (например, определить возраст, вычислить стаж
работы, определить число рабочих дней на любом промежутке времени).
Логические функции позволяют создавать сложные формулы, которые в
зависимости от выполнения тех или иных условий будут совершать различные
виды обработки данных. Также широко представлены математические
функции. Например, можно выполнить различные операции с матрицами:
умножать, находить обратную, транспонировать.
В распоряжении пользователя находится библиотека статистических
функций, при помощи которой можно проводить статистическое
моделирование, в том числе и при помощи факторного и регрессионного
анализа.
2.1. Ввод и редактирование формул
Под формулой понимается выражение, которое обязательно начинается
знаком равенства и определяет, какие расчеты нужно произвести на рабочем
листе. Результатом работы формулы является новое значение, которое
выводится как результат вычисления формулы по уже имеющимся данным.
Если значения в ячейках, на которые есть ссылки в формулах, меняются, то
результат изменится автоматически.
Формула в ячейке может включать следующие элементы: числовые и
текстовые значения, ссылки на другие ячейки, знаки математических и
15
логических операций, а также обращения к функциям. При вычислении
формулы используется порядок действий, принятый в математике.
Операторами обозначаются операции, которые следует выполнять над
операндами формулы. Используются 4 вида операторов: арифметические,
текстовые, адресные и операторы сравнения.
Арифметические операторы используются для выполнения основных
математических вычислений над числами. Используемые операции: + – * /
%.
Операторы сравнения используются для обозначения операций сравнения
двух чисел. Результатом выполнения операций сравнения является логическое
значение ИСТИНА или ЛОЖЬ.
Текстовые операторы используются при работе с текстом. Например, для
обозначения операций объединения последовательностей символов в единую
последовательность используется текстовый оператор « » (амперсант).
Пример: «Северный» «ветер» результат «Северный ветер».
Рис. 2.1. Пример использования текстового оператора
Адресные операторы объединяют диапазоны ячеек для осуществления
вычислений. В таблице 2.1 представлены адресные операторы.
Таблица 2.1. Адресные операторы
Пример
Адресный
оператор
Значение
:
(двоеточие)
Оператор диапазона, ссылающийся на
ячейки между границами диапазона
включительно
Оператор
объединения,
который
ссылается на объединение ячеек
диапазонов
Оператор
пересечения,
который
ссылается на общие ячейки диапазонов
,
(запятая)
(пробел)
В5:В15
СУММ(В5:В15,D5:D
15)
СУММ(В5:В15
А7:D7)
Ячейка В7 является
общей
для
двух
диапазонов
16
2.2. Использование функций
В формулах в качестве операндов могут выступать не только данные и
адреса или имена ячеек, но и функции. Функции могут быть встроенными или
определяться пользователем.
В электронных таблицах содержится большое количество стандартных
формул, называемых функциями. Функции используются для простых или
сложных вычислений. Для использования функций в формуле нужно указать ее
имя и аргументы, отделяясь друг от друга точкой с запятой. Максимальное
количество аргументов функции не должно превышать 30, а длина формулы не
может быть более 1024 символов.
В качестве аргумента можно задать числовое или текстовое значение, адрес
ячейки, адрес или имя диапазона. В одной формуле может быть несколько
функций, объединенных различными символами операций. На формулы с
функциями не накладываются никакие ограничения. Допускается
использование в качестве аргументов ссылок на диапазоны из других листов и
книг.
Чтобы использовать функцию, нужно ввести ее как часть формулы в ячейку
рабочего листа. Последовательность, в которой должны располагаться
используемые в формуле символы, называется синтаксисом функции. Все
функции используют одинаковые основные правила синтаксиса. Если
нарушаются правила синтаксиса, выдается сообщение о том, что в формуле
имеется ошибка.
Если функция появляется в самом начале формулы, ей должен
предшествовать знак равенства, как и во всякой другой формуле.
Аргументы функции записываются в круглых скобках сразу за названием
функции и отделяются друг от друга символом точка с запятой ―;‖. Скобки
позволяют определить, где начинается и где заканчивается список аргументов.
Внутри скобок должны располагаться аргументы. Помните о том, что при
записи функции должны присутствовать открывающая и закрывающая скобки,
при этом не следует вставлять пробелы между названием функции и скобками.
В качестве аргументов можно использовать числа, текст, логические
значения, массивы, значения ошибок или ссылки. Аргументы могут быть как
константами, так и формулами. В свою очередь эти формулы могут содержать
другие функции. Функции, являющиеся аргументом другой функции,
называются вложенными. В формулах можно использовать до семи уровней
вложенности
функций.
Пример
вложенной
функции:
=ЕСЛИ(ИЛИ(L8>=4;СРЗНАЧ(L6:L9)>=3,5);"Тест пройден";"Тест не пройден")
Задаваемые входные параметры должны иметь допустимые для данного
аргумента значения. Некоторые функции могут иметь необязательные
аргументы, которые могут отсутствовать при вычислении значения функции.
В таблице 2.2 представлены стандартные функции электронной таблицы на
примере MS Excel.
17
Таблица 2.2. Стандартные функции
Категория
Финансовые
Дата и время
Ссылки
и
массивы
Статистические
Математические
Работа с базой
данных
Текстовые
Логические
Проверка
свойств
значений
Инженерные
и
Назначение функций
Вычисляют процентные ставки, ежемесячные отчисления,
амортизационные отчисления
Возвращают в различных форматах день недели, время и
дату
Вычисляют и возвращают значения из диапазона; создают
гиперссылки для веб-документов
Вычисляют
средние
значения,
наибольшее
или
наименьшее числа в диапазоне, тестируют на предмет
независимости выборок
Определяют абсолютные величины, косинусы и
логарифмы
Выполняют различного рода анализ данных, находящихся
в списках или базах данных
Преобразуют регистр текста, обрезают символы с правого
или левого края текстовой строки, объединяют текстовые
строки
Вычисляют выражения и возвращают значения ИСТИНА
или ЛОЖЬ, которые используются при выполнении
другого действия либо форматирования
Возвращают в Windows информацию о текущем статусе
ячейки, объекта или среды
Выполняют операции с комплексными переменными,
преобразования из одной системы счисления в другую и
т.д
В таблице 2.3 представлены типы ошибок, возникаемые при работе с
функциями и меры по их устранению.
Таблица 2.3. Типы ошибок
Обозначени
е ошибки
######
#ССЫЛКА!
#Дел/0
В каком случае появляется
Меры по устранению
Ширина столбца
недостаточна для размещения
результатов вычисления
формулы и когда полученные
значения даты или времени
являются отрицательными
числами
Формула содержит ссылку на
несуществующие ячейки
Деление на 0 (возможно
осуществляется деление на
Необходимо
увеличить
ширину ячейки или изменить
числовой формат
Необходимо
проверить
правильность ссылки
Проверить
содержимое
влияющей
ячейки.
Если
18
пустую ячейку)
#Число!
#ИМЯ?
#ПУСТО!
#Н/Д
#ЗНАЧ!
ячейка
пуста,
введите
значение
Нарушение правил, принятых Уточнить
значение
в
математике,
или аргументов
некорректное
определение
аргументов
функции,
значение которых может
выходить за допустимые
пределы Excel
При вводе имени допущена Проверить
правильность
ошибка, и программа не написания имени функции
может найти нужное имя ни или ввести функцию заново
среди функций, ни среди
имен диапазонов
Неверное
указание Проверить
правильность
пересечения диапазонов
указания диапазонов
Не задан один или несколько Проверить содержимое ячеек,
аргументов функции или также
пользовательские
происходит обращение к функции и макросы
недоступной
пользовательской функции.
Ошибку могут генерировать
макросы,
вызывающие
функцию
Ввод аргумента или операнда Проверить
допустимость
недопустимого типа
типа операнда и аргумента
Для поиска ошибок предоставляется в распоряжение пользователей
вспомогательная функция, с помощью которой можно графически представить
связи между влияющими и зависимыми ячейками. Влияющими называют
ячейки, данные которых оказывают влияние на значение текущей ячейки.
Зависимой является ячейка с формулой, результат вычисления которой зависит
от данных, находящихся в других ячейках.
Команды для отслеживания таких зависимостей вызываются из подменю
Зависимости формул меню Сервис, а также с помощью панели инструментов
Зависимости. На рисунке 2.2 представлен пример отслеживания зависимостей.
Рис. 2.2. Пример отслеживания зависимостей в данных
19
2.3. Использование ссылок и имен
Ссылкой (адресом) определяется ячейка или группа ячеек, используемых в
формуле. С помощью ссылок можно использовать значение одной и той же
ячейки в нескольких формулах, также можно ссылаться на ячейки,
находящиеся на других листах книги, в другой книге (внешние ссылки) или в
другом приложении (удаленные ссылки).
Для задания адреса ячейки можно использовать два режима адресации: в
формате А1 (используется по умолчанию) или R1C1 (строка, столбец)
(Сервис – Параметры - вкладка Общие – Стиль ссылок).
Адресация может быть абсолютной, относительной и смешанной.
При копировании формулы, содержащие относительные ссылки, и вставке ее
в другое место, ссылки будут меняться, настраиваясь на новое местоположение
(рис. 2.3 (а)).
Абсолютные ссылки задают фиксированную позицию на рабочем листе
(рис. 2.3 (б)) и не изменяются при копировании. Признаком абсолютной ссылки
является наличие двух знаков доллара ($) – перед именем столбца и перед
номером строки. Смешанные ссылки фиксируют либо имя столбца, либо номер
строки (имеется один знак доллара).
Рис. 2.3. Пример адресации:
а) относительная, б) абсолютная
В электронных таблицах существует возможность присваивать ячейкам,
диапазонам ячеек, формулам или константам имена и использовать их в
качестве абсолютных ссылок. Имя может содержать до 255 знаков и состоять
из букв, цифр, символов точки и подчеркивания. Начинаться оно должно с
буквы или со знака подчеркивания. Использование имен обеспечивает
следующие преимущества:
Формулы, использующие имена, легче воспринимаются и запоминаются,
чем формулы, использующие ссылки на ячейки. Например, формула
“=Активы-Пассивы” гораздо понятнее, чем формула “=F6-D6”.
При изменении структуры рабочего листа достаточно обновить ссылки лишь
в одном месте – в определении имен, и все формулы, использующие эти
имена, будут использовать корректные ссылки.
20
После того как имя определено, оно может использоваться в любом месте
рабочей книги. Доступ ко всем именам из любого рабочего листа можно
получить с помощью окна имени в левой части строки формул.
Есть возможность определить специальные имена, диапазон действия
которых ограничивается текущим рабочим листом. Это означает, что эти
имена можно использовать лишь на том рабочем листе, на котором они
определены. Такие имена не отображаются в окне имени строки формул или
окне диалога ―Присвоить имя‖, если активен другой рабочий лист книги.
В таблицах Excel автоматически создаются имена на основе заголовков
строк и столбцов рабочего листа.
Быстро перейти на поименованную ссылку, заменить ссылки, вставить
ссылку в формулу с помощью окна имени в строке формул и мн. др.
Чтобы присвоить имя ячейке или диапазону ячеек необходимо: Выделить
ячейку или группу ячеек, Вставка – Имя – Присвоить
Также с помощью диалогового окна Присвоение имени можно присваивать
имена константам и формулам. Для этого необходимо: в поле Имя ввести
нужное имя, а в поле Формула – константу или формулу – Добавить.
На рис. 2.4 представлен интерфейс диалогового окна Присвоение имени.
Рис. 2.4. Интерфейс диалогового окна Присвоение имени и пример имен
В качестве имени диапазона можно использовать содержимое нескольких
входящих в его состав ячеек, что упрощает работу с таблицей. Необходимо:
Выделить диапазон ячеек и команда Вставка – Имя – Создать. Появится
диалоговое окно, в котором нужно указать, где в выделенном диапазоне
расположены ячейки, текст которых должен использоваться в качестве имен.
Для ввода имени в формулу предназначена команда Вставка – Имя –
Вставить.
Вопросы для самоконтроля
1.
2.
3.
4.
5.
6.
7.
Дайте определение понятие формула.
Какой порядок действий используется при вычислении формулы?
Перечислите основные операторы, используемые в формуле.
Что подразумевается под функцией и из чего она состоит?
Что такое вложенная функция?
Какие стандартные функции используются в электронной таблице?
Какие ошибки могут возникнуть при работе с функциями?
21
8. Какие типы адресации используются при работе с таблицей и в чем их
достоинства?
9. В чем заключается достоинство использования имен в формуле?
22
Лекция 3
Раздел 3. Работа со списками и базами данных в электронной таблице
3.1. Построение графиков и диаграмм
Графики и диаграммы в электронной таблице позволяют наглядно
представить, в виде графической зависимости, числовые и другие данные
таблицы. На графиках легко просматривается тенденция к изменению. Можно
даже определять скорость изменения тенденции. Различные соотношения,
прирост, взаимосвязь различных процессов — все это легко можно увидеть на
графиках.
Диаграммы связаны с данными листа, на основе которых они были созданы,
и автоматически изменяются каждый раз, когда изменяются данные на листе.
В электронных таблицах можно создавать диаграммы двух видов:
внедренную диаграмму;
лист диаграммы.
Внедренная диаграмма – это диаграмма, расположенная на листе рядом с
таблицей и сохраняемая вместе с листом при сохранении книги. Внедренные
диаграммы также связаны с данными и автоматически обновляются при
изменении исходных данных.
Лист диаграммы – это лист книги, содержащий только диаграмму. Листы
диаграммы связаны с данными таблиц и обновляются при изменении данных в
таблице.
Элементы диаграмм:
- имена категорий;
- ряд данных (данные диаграммы). Значение одной ячейки отображается в
виде маркера.
Ряд данных
Ось
значений
Значения
Имена категорий
Ряд 1
Ряд 2
Легенда
Ось категорий
Маркер данных
Рис. 3.1. Структура диаграммы
23
Применительно к MS Excel предлагается 9 типов плоских диаграмм и 6 типов
объемных. Эти 15 типов включают 102 формата. Кроме того, имеется
возможность создания собственного пользовательского формата диаграммы.
Для построения диаграммы выполните команду Вставка-Диаграмма или
нажмите кнопку Мастер диаграмм. Заголовками строк и столбцов называются
метки. Метки столбцов являются текстом легенды. Легенда представляет собой
прямоугольник, в котором указывается каким цветом или типом линий
выводятся на графике или диаграмме данные из той или иной строки. Для
редактирования диаграммы необходимо дважды нажать мышью в любом месте
диаграммы.
При создании диаграммы смешанного типа возникают две группы рядов.
Одна группа - гистограмма, а другая может быть графиком, с областями или
XY-точечной. После создания диаграммы можно использовать для каждого
ряда данных любой тип плоской диаграммы. Одной из наиболее подходящих
типов диаграмм для обработки результатов лабораторных исследований
является XY-точечная диаграмма.
Линии тренда можно проводить на гистограммах, графиках, линейчатых и
XY-точечных диаграммах.
Для отображения диаграмм можно использовать не только столбцы, линии и
точки, но и произвольные рисунки.
Типы диаграмм:
1. Гистограмма (столбиковая диаграмма)
Гистограмма показывает изменение данных за определенный период времени
и иллюстрирует соотношение отдельных значений данных.
ось x – категория
ось y – значение
уделяется внимание изменениям во времени
Рис. 3.2. Пример гистограммы
2. Линейчатая диаграмма
Линейчатая диаграмма отражает соотношение отдельных компонентов.
ось x – значение
ось y – категория
уделяется внимание сопоставлению значений
Рис. 3.3. Пример линейчатой диаграммы
3. График
График отражает тенденции в равные промежутки времени. Маркеры
выполнены в виде линии, которые интерполируют определенные значения.
24
Рис. 3.4. Пример графика
4. Круговая диаграмма
Круговая диаграмма показывает как абсолютную величину каждого элемента
ряда данных, так и его вклад общую сумму. На круговой диаграмме может быть
представлен только один ряд данных. Маркеры отображаются в виде
сегментов. Сумма всех значений должна принимать 100%.
Рис. 3.5. Пример круговой диаграммы
5. Кольцевая диаграмма
Как и круговая диаграмма, кольцевая диаграмма показывает вклад каждого
элемента в общую сумму, но в отличие от круговой диаграммы она может
содержать несколько рядов данных.
Рис. 3.6. Пример кольцевой диаграммы
6. Точечная диаграмма
Маркерами являются точки в декартовой системе координат.
Рис. 3.7. Пример точечной диаграммы
7. Пузырьковая диаграмма
Маркеры характеризуются 2-мя значениями:
1 – положение маркера на плоскости;
2 – размер маркера в зависимости от числа.
Рис. 3.8. Пример пузырьковой диаграммы
25
8. Диаграмма с областями
Маркеры – области в трехмерной системе координат
Рис. 3.9. Пример диаграммы с областями
9. Лепестковая диаграмма
В лепестковой диаграмме каждая категория имеет собственную ось
координат, исходящую из начала координат. Линиями соединяются все
значения из определенной серии. Лепестковая диаграмма позволяет сравнить
общие значения из нескольких наборов данных.
Маркеры – лучи, выходящие из одной точки. Из нулевой точки выходят
категории (могут быть и отрицательные значения).
Рис. 3.10. Пример лепестковой диаграммы
10. Объемные поверхностные диаграммы
Коническая, цилиндрическая, пирамидальная
3.2. Сводные таблицы
Сводная таблица – динамическая таблица итоговых данных, извлеченных
или рассчитанных на основе информации, содержащейся в списках.
Сводные таблицы электронных таблиц являются интерактивными, т.е. могут
автоматически обновляться при изменении исходных данных. Для просмотра
данных разными способами можно поворачивать таблицу для отображения
данных по строками или по столбцам.
Достоинства применения сводных таблиц:
позволяют создавать обобщающие таблицы, которые предоставляют
возможность группировки однотипных данных, подведения итогов,
нахождения статических характеристик записей;
легко преобразуются;
разрешают выполнять автоматический отбор информации;
на основе сводных таблиц строятся диаграммы, которые динамически
перестраиваются вместе с изменением сводной таблицы.
Для более наглядного представления рассмотрим формирования сводной
таблицы на примере данных, в которых собрана информация по типу
использования оборудования на различных РАТС (рис. 3.11).
26
Рис. 3.11. Исходные данные для формирования сводной таблицы
На рисунке 3.12 представлен мастер сводных таблиц, при помощи которого
исходные данные преобразуются в вид сводной таблицы: команда меню
Данные Сводная таблица.
а)
б)
в)
Рис. 3.12. Мастер сводных таблиц
На рис. 3.13 представлен макет сводной таблицы, при помощи которого
формируются ее заголовки.
27
Рис. 3.13. Макет мастера сводных таблиц
Страница – используются в качестве заголовков страниц сводной таблицы.
Строка – используется в качестве заголовков строк сводной таблицы.
Данные – поле (поля), для которого подводится того в сводной таблице.
Столбец - используется в качестве заголовков столбцов сводной таблицы.
В каждую область можно перетащить любое количество полей, причем все
поля использовать не обязательно.
Результатом является сводная
представленная на рис. 3.14.
таблица
с
перечнем
оборудования,
Рис. 3.14. Сводная таблица с перечнем оборудования
В данном примере нас не интересует количество отсутствующих данных,
поэтому данную категорию можно удалить из сводной таблицы. Для этого
необходимо убрать один из флажков на разворачивающимся списке сводной
таблицы (рис. 3.15 а). Кроме того, в сводной таблице полученные данные
можно преобразовать в необходимый вид, например, представить данные в
процентной доле (от суммы по столбцу) выбирается в Параметрах поля (рис.
3.15 б). Результатом является таблица с преобразованными данными (рис. 3.15
в).
а)
б)
28
в)
Рис. 3.15. Преобразование сводной таблицы
В общем случае для управления сводной таблицей используется диалоговое
окно и соответствующее меню (рис. 3.16), при помощи которых можно
добавить новую переменную или группу переменных в таблицу или
преобразовать данные в необходимый вид. Кроме того, возможно управление
сводной таблицей через диаграмму.
Рис. 3.16. Меню сводной таблицы
3.3. Работа со списками и базами данных
Большие массивы информации обычно хранятся в виде базы данных и
обрабатываются специальными средствами, позволяющие осуществлять
следующие операции:
введение баз данных (ввод, коррекция, и удаление записей),
фильтрация (отбор) записей по задаваемому критерию поиска,
сортировка записей по одному или несколькими полям,
получение итогов по группе записей и всей базе данных.
База данных в электронной таблице – это список связанных данных, в
котором строки данных являются записями, а столбцы полями.
В электронной таблице аналогом простой базы данных является список,
который представляет собой группу строк, содержащих связанные данные.
Отличительной особенностью списка заключается в том, что каждый его
столбец содержит однотипные данные.
Примером списка может быть служить список группы.
3.3.1. Формирование списка
Создание связанного списка производится с помощью команды Данные Список – Создать список. После задания диапазона (рис. 3.17) списка
формируется область таблицы, которая задает базу данных в электронной
29
таблице (рис. 3.18 а). После заполнения данных таблица примет вид, подобный
рис. 3.18 (б).
Рис. 3.17. Создание списка
а)
Рис. 3.18. Формирование списка
б)
Достоинством создания списка заключается в том, что при записи новых
данных размер списка автоматически увеличивается. Управление списком
происходит через специальное меню (рис. 3.19).
Рис. 3.19. Управление списком
Для упрощения работы со списками можно использовать диалоговое окно
формы данных, предназначенное для ввода и обработки информации. Данное
окно открывается с помощью команды Данные – Форма (рис. 3.20).
30
Рис.3.20. Форма Данные
При помощи Формы и определенных критериев поиска можно из базы
данных определить искомый компонент (рис. 3.21). Диалоговое окно Форма –
Критерий – Далее.
Рис.3.21. Поиск через форму Данных
3.3.2. Сортировка записей
Сортировка по одному полю:
Осуществляется через соответствующую кнопку на панели инструментов
.
Общая сортировка (как по одному полю, так и более):
Осуществляется через операцию: Данные – Сортировка – Выбрать поля
сортировки (рис. 3.22).
Рис. 3.22. Общая сортировка записей
31
3.3.3. Фильтрация записей
Процессы ввода записей в список и удаление из списка, а также процесс
поиска информации может упроститься за счет использования фильтров, такие
как: автофильтр и расширенный фильтр.
Функция автофильтра позволяет с помощью определения ряда критериев
производить отбор непосредственно на рабочем листе. Вызывается при помощи
команды Данные – Фильтр – Автофильтр.
В качестве критерия можно использовать содержимое любой ячейки, выбрав
его из выпадающего списка (рис. 3.23 (а)). Также можно использовать
интерфейс пользовательского автофильтра (рис. 3.23 (б)), при помощи которого
можно указать комплекс условий для фильтрации. Результат автофильтра рис.
3.23 (в).
а)
б)
в)
Рис. 3.23. Автофильтр
Для отмены всех критериев: Данные – Фильтр – Отобразить все.
При расширенном фильтре критерии отбора задаются в отдельной области,
которая должна содержать заголовки столбцов списка.
Задавая критерии отбора записей для столбцов, содержащих текстовые
данные, в качестве символов подстановки можно использовать звездочку (*),
которая заменяет любое количество символа, и знак вопроса (?),
соответствующее одному символу.
Для вызова: команда Данные – Фильтр – Расширенный фильтр (рис. 3.24).
Отличительным признаком расширенного фильтра является написание условий
вне исходного списка, причем заголовки условия должны в точности повторять
заголовки исходной таблицы.
Рис. 3.24. Расширенный фильтр
32
Кроме того, имеется 12 функций рабочего листа, реализуемых для анализа
данных из списков или БД. Каждая из функций использует три аргумента:
БД Функция (база данных; поле; критерий), где указываются:
• база данных, которая будет рассматриваться для поиска решения;
• поле значений, по которому производится отбор;
• критерии отбора.
На рис. 3.25 представлен пример использования функции баз данных, в
котором отображается максимальный роста у женщин ( ДМАКС() ). Для этого
предварительно в ячейке, находящейся вне базы данных, необходимо указать
условие, по которому будет производиться отбор. В этом случае используется
формула вида: =ДМАКС(A1:D10;D1;F1:F2).
Рис. 3.25. Пример использования функций баз данных
3.3.4. Импорт данных с помощью Microsoft Query
Доступ к информации, хранящейся в базах данных, которые созданы
другими приложениями, в том числе Microsoft Access, dBASE, Microsoft SQL
Server может быть реализован через интегрированную программу Microsoft
Query.
Для доступа к внешним базам данных программа Microsoft Query использует
драйвер ODBC (Open DataBase Connectivity – Открытое взаимодействие с
базами данных).
Чтобы создать запрос, необходимо активизировать команду Данные –
Импорт внешних данных – Создать запрос (диалоговое окно Выбор источника
данных и выбрать один из источников данных (Рис.3.26)).
Рис. 3.26. Выбор источника данных
В окне Выбор источника данных можно указать один из существующих
источников или создать новый источник данных, щелкнув на элементе «Новый
источник данных» появится диалоговое окно Создание нового источника
данных (рис. 3.27).
33
Рис. 3.27. Создание нового источника данных
В поле № 1 указывается имя источника данных, в поле № 2 – необходимый
драйвер. Поле № 3 «Связь» предназначено для установки драйвера ODBC,
содержимое которого зависит от выбранного драйвера (рис.3.28). Далее
выбирается файл, из которого в последующем будет импортирована
информация (рис. 3.29). После того, как все установки выполнены в списке
диалогового окна Выбор базы данных будет дополнен именем нового
источника данных.
Рис. 3.28. Установка драйвера
Рис. 3.29. Выбор базы данных
После выбора запроса открывается первое окно мастера создания запроса
Создание запроса: выбор столбцов (Рис. 3.30).
34
Рис. 3.30. Выбор столбцов в базе данных
Далее в окне Создание запроса: отбор данных указать критерии отбора записей
(Рис. 3.31).
Рис. 3.31. Отбор данных
Извлекаемые из базы данных набор записей обычно сортируют. Сортировка
может осуществляться в 4 этапа, которая осуществляется в окне Создание
запроса: порядок сортировки (рис. 3.32).
В окне Создания запроса: заключительный шаг – три переключателя
(рис.3.33). При выборе переключателя вернуть данные в Microsoft Office Excel
данные, полученные в результате выполнения запроса, будут переданы в Excel,
а при выборе переключателя Просмотр или изменение данных в Microsoft
Query – в Microsoft Query.
Рис. 3.32 Сортировка
35
Рис. 3.33. Импорт данных
При выборе переключателя Вернуть в Microsoft Excel данные будут
переданы в рабочую книгу либо на текущий лист, либо на новый лист.
При выборе Microsoft Query откроется одноименное окно, разделенное на три
области – таблиц. Условий, данных. В области таблиц отображаются название и
перечни полей базы данных, которые упоминаются в запросе. Условия отбора
записи указываются в следующей области, а под ними, в области данных,
находится таблица результатов. В ее столбцах представлены выбранные
пользователем поля базы данных.
Рис. 3.34. Работа с данными в Microsoft Query
Microsoft Query позволяет изменять содержимое таблицы данных, создавать
критерии отбора записей из базы данных и изменять их.
Для определения условий отбора команда Добавить условие меню Условия и
в открывшемся диалоговом окне необходимо сделать соответствующие
установки. Также окно Условие рабочего окна можно дополнить необходим
условием.
Данные таблицы после всех необходимых преобразований необходимо
передать в Excel. Для этого необходимо вызвать команду Файл – вернуть
данные в Microsoft Excel. Откроется диалоговое окно (рис.3.35), при котором
данные будут переданы в рабочую книгу либо на текущий лист, либо на новый
лист.
36
Рис. 3.35. Возвращение данных в таблицу
После отражения выбранных данных из базы данных, связь с базой данных
теряется и для их обновления необходимо вновь выполнять запрос в программе
Microsoft Query.
Вопросы для самоконтроля
1. Типы диаграмм, используемые в электронных таблицах.
2. Из чего состоит диаграмма?
3. Что такое маркер?
4. Чем гистограмма отличается от линейчатой диаграммы?
5. Какие типы диаграмм используются в электронных таблицах?
6. Что такое сводная таблица?
7. Как происходит управление данными в сводной таблице?
8. Что такое список?
9. Достоинства создания списка.
10. Опишите виды сортировки.
11. Что такое расширенный фильтр?
12. Чем автофильтр отличается от расширенного фильтра?
13. Из каких аргументов состоят функции баз данных?
14. Что такое Microsoft Query?
15. Чем импортирование данных отличается от импортирования при помощи
Microsoft Query?
16. Этапы импорта данных при помощи Microsoft Query.
17. Какой драйвер использует Microsoft Query?
37
Лекция 4
Раздел 4. Анализ данных средствами ЭТ
Электронные таблицы располагают средствами для анализа данных – от
разрешения простых вопросов типа «что - если» (например, что будет, если
увеличится процентная ставка по кредиту») до решения сложных задач
оптимизации. Эти средства – «Подбор параметра», «Таблица подстановки»,
«Поиск решения».
Целевая ячейка – ячейка, для которой нужно найти заданное значение. Она
должна содержать формулу, зависящую от изменяемых ячеек. Пока не будет
найдено решение или пообобран определенный параметр, программа подбирает
значения изменяемых ячеек.
Изменяемые ячейки – ячейки, от которых зависит значение целевой ячейки.
Средства решения задач оптимизации:
• Подбор параметра,
• Таблица подстановки,
• Поиск решения
Средства представления данных:
• Сценарий,
• Консолидация
4.1. Подбор параметра
Средство для прогноза на основе Подбора параметра используется при
наличии зависимости между данными в двух ячейках. Оно предназначено для
определения значения, которое следует ввести в одну ячейку, чтобы получить
требуемое значение в другой. Последняя должна содержать формулу,
использующую адрес ячейки, значение которой подбирается. Подбор параметра
можно осуществить как в таблице, так и на плоской гистограмме или графике.
Для использования Подбора параметра необходимо предварительно задать
формулу, которая ссылается на любое значение подбираемого параметра, затем
это значение при помощи данной опции автоматически подберется.
Например, необходимо определить количество периодов выплаты займа в
500’000 р., если месячный платеж должен составлять 110’000 р. при
процентной ставке, равной 50%.
На рис. 4.1. представлена реализация процедуры подбора параметра. Так рис.
4.1-а иллюстрирует запись исходных данных в таблицу. Кроме того в целевой
ячейке (В6) предварительно произведен расчет платежа без удовлетворения
поставленного условия. На рис. 4.1-б представлено диалоговое окно Подбора
параметра, в котором устанавливаются: целевая ячейка, значение, которое
необходимо подобрать, а также изменяемая ячейка. Рис. 4.1-в демонстрирует
результат подбора параметра в соответствии с условием.
38
а)
б)
в)
Рис. 4.1. Процедура подбора параметра
Процедура подбора параметра:
1. Выберите целевую ячейку.
2. Команда Сервис – Подбор параметра. В поле Установить в ячейке должен
быть отражен адрес целевой ячейки.
3. Задайте в поле «Значение» значение, которое должна содержать целевая
ячейка.
4. В поле «Изменяя значение ячейки» укажите адрес ячейки, хранящей
параметр, значение которого необходимо установить таким, чтобы в целевой
ячейке получить необходимое значение.
Подбор параметра также можно провести при работе с диаграммами, изменяя
соответствующий маркер (рис. 4.2).
Рис. 4.2. Подбор параметра на диаграмме
4.2. Таблицы подстановки
Таблицы подстановки позволяют оценить влияние параметров на некоторую
величину. Таблица подстановки создается на основе ячейки с формулой,
содержащей ссылку на ячейку, определенную как поле ввода (ячейка ввода), и
списка исходных значений (они последовательно представляются в ячейку
ввода с целью создания списка результатов).
Существует две разновидности таблиц подстановки, а именно: с одной
переменной и двумя переменными.
Таблицы подстановки с одной переменной
39
Данная таблица используется для вычисления результатов, которые можно
поместить в одну строку или столбец. В таблице используется список исходных
значений в первой строке (или столбце), которые последовательно
подставляются в формулу, а результаты помещаются в следующую строку (или
столбец). Одни и те же исходные значения могут быть подставлены в несколько
формул.
Рис. 4.3. Структура таблицы подстановки с одной переменной
Формула обязательно должна содержать ссылку на ячейку ввода, в качестве
которой может выступать любая ячейка рабочего листа.
Пример: необходимо проследить, как изменяется накапливаемая сумма и
ежемесячная прибавка (рис. 4.4) в зависимости от периода накопления (от 15 до
30 лет).
Рис. 4.4. Исходные данные для таблицы подстановки
Создание таблицы подстановки с одной переменной:
1. Создайте список исходных значений и формулы для расчета данных
таблицы в соответствии правилом, учитывая, что формула должна содержать
ссылку на ячейку ввода (рис. 4.5).
2. Выделите диапазон, включающий исходные значения и формулы.
3. Вызовите команду Данные – Таблица подстановки.
4. В диалоговом окне Таблица подстановки задайте ссылку на ячейку ввода.
Если исходные значения образуют столбец, введите ссылку в поле Подставлять
значение по строкам в, если они образуют строку – в поле Подставлять
значения по столбцам
Применительно к исходным данным, ячейка С2 является ячейкой ввода.
Результатом действия является заполненная таблица для указанного диапазона
(рис. 4.6).
40
Рис. 4.5 Список исходных значений
Рис. 4.6. Результат таблицы подстановки
Таблицы подстановки двумя переменными
Если результаты вычисления должны зависеть от двух параметров,
необходимо использовать таблицу подстановки с двумя переменными.
Рис. 4.7. Структура таблицы подстановки с двумя переменными
Такая таблица может содержать только одну формулу, которая помещается в
ячейку, расположенную в левом верхнем углу. По аналогии с предыдущем
случаем сначала формируется сама таблица с изменяемыми значениями (рис.
4.8), потом для нее производится процедура «таблицы подстановки» (рис. 4.9).
Рис. 4.8. Список исходных значений
41
Рис. 4.9. Результат таблицы подстановки
4.3. Сценарии
Сценарий представляет собой набор сохраненных электронной таблицей
значений, которые она может автоматически подставить в ячейки рабочего
листа. Сценарии можно использовать для прогноза результатов моделирования
и расчетов.
Добавления сценария
1. Создайте рабочий лист с данными и формулами, которые
устанавливают связи между ячейками.
2. Команда Сервис – Сценарий
3. Кнопка Добавить. В окне Добавления сценария задайте имя сценария в
поле Название сценария (рис. 4.10), а адреса изменяемых ячеек – в поле
Изменяемые ячейки (рис. 4.11).
Рис. 4.10. Диспетчер сценариев: изменение сценария
Рис. 4.11. Диспетчер сценария: изменения значений
Для создания отчета необходимо в диалоговом окне Диспетчер сценариев
выбрать пункт Отчет, в котором можно установить вид отчета: либо как
42
структура, либо как сводная таблица. Также в отчете можно отразить
необходимые результаты.
Рис. 4.12. Результат Сценария
4.4. Консолидация данных
Если таблицы имеют одинаковые столбцы с одинаковыми типами данных в
них, то они называются однотипными. Консолидация данных позволяет
объединить несколько однотипных таблиц в одну.
Консолидация данных – процедура получения итогов для данных в
различных частях таблицы. Подлежащие консолидации диапазоны ячеек могут
располагаться как на одном рабочем листе, так и на разных, а также в
различных книгах.
Данные для консолидации можно задавать тремя способами:
С помощью формул, содержащих ссылки. Для расчета итоговых данных
составляются трехмерные формулы, т.е. формулы, которые содержат ссылки на
диапазоны, включающие ячейки разных листов.
По расположению ячеек. Исходные данные для функции консолидации
задаются в диалоговом окне в виде ссылок на диапазоны.
По заголовкам строк и столбцов.
Рис. 4.13. Интерфейс консолидации
Если необходимо можно создать связи с исходными данными, причем в
пределах одной рабочей книги данные будут обновляться автоматически.
43
4.5. Поиск решения
С помощью Поиска решения можно определить, при каких значениях
указанных влияющих ячеек формула в целевой ячейке принимает нужное
значение (минимальное, максимальное или равное какой-либо величине).
Любой фактор в процессе поиска решения обычно интерпретируется как
некоторый ресурс, выраженный в количественном измерении (минуты, штуки,
рубли и т.д.). Для процедуры поиска можно также задавать ограничения,
причем необязательно, чтобы при этом использовались те же влияющие ячейки.
Для расчета заданного значения применяются различные математические
методы поиска.
Цель поиска решения состоит в том, чтобы найти такие значения входных
факторов, которые обеспечат получение целевых значений целевых факторов.
На первом этапе необходимо выявить главную проблему соответствующей
ситуации, для которой необходимо подобрать такие значения целевой функции,
которые обеспечат переход системы из реального состояния в желаемое. Для
этого необходимо выделить целевую функцию, которая является основой для
процесса поиска решения. Кроме того, необходимо наложить ограничения на
соответствующие элементы целевой функции для верного принятия решения,
устанавливаемые экспертом, исходя из статистических данных, основанных на
предыдущих знаниях о проблеме.
Выделив целевую функцию и ограничения, эксперт запускает процесс поиска
решения, при котором вычисления осуществляются в обратном порядке от
целевого фактора к входным факторам. При этом система должна выполнить
проверочный счет, подстановкой результатов обратной задачи, и получить
прогноз развития ситуации.
Процедура поиска решения
1. Создайте новую таблицу с формулами, которые устанавливают связи
между ячейками рабочего листа.
2. Выделите целевую ячейку, которая должна принять необходимое
значение, и выберете команду Сервис – Поиск решения. Поле Установить
целевую ячейку диалогового окна настройки Поиск решения будет содержать
адрес целевой ячейки.
3. Установить требуемый переключатель в поле Равной.
4. В поле Изменяя ячейки укажите, в каких ячейках программа должна
изменять значения в поисках оптимального результата.
5. Создайте ограничения, если необходимо.
6. Щелкнув на кнопке Выполнить, запустите процесс поиска решения.
7. Если данные удовлетворяют требованиям, сохраните найденное решение
и создайте отчет.
Обобщенная схема процесса поиска решения представлена на рис. 4.14.
44
Рис. 4.14. Процесс поиска решения
Перед запуском процедуры поиска решения исходные данные должны быть
представлены в виде таблицы, которая содержит формулы, отражающие
зависимости между данными таблицы. Пример процедуры поиска решения
представлен на рис. 5.15.
Рис. 4.15. Поиск решения
Рис. 4.16. Результат поиска решения
Параметры процедуры поиска решения (рис. 4.17)
Задать параметры, определяющие способ выполнения вычислений, можно в
диалоговом окне Параметры поиска решения. Основные параметры:
45
Максимальное время – максимальное время в секундах, отведенное на
поиск решения задачи.
Предельное число итераций – максимальное количество итераций,
возможных в течение времени, необходимого для поиска конечного
результата.
Относительная погрешность – точность результата.
Допустимое отклонение – величина допуска на отклонение от
оптимального решения, если множество значений влияющей ячейки
ограничено множеством целых чисел. Если значение допуска увеличить,
поиск решения завершится быстрее, однако полученный результат будет
менее точным.
Сходимость – значение относительного изменения, при достижении
которого в последних пяти итерациях поиск решения прекращается.
Линейная модель - служит для ускорения поиска решения путем
применения к задаче оптимизации линейной модели. Нелинейные модели
предполагают использование нелинейных функций, фактора роста и
экспоненциального сглаживания, что замедляет вычисления.
Неотрицательные значения - позволяет установить нулевую нижнюю
границу для тех влияющих ячеек, для которых не было задано
соответствующее ограничение в диалоговом окне Добавить ограничение.
Автоматическое масштабирование - используется, когда числа в
изменяемых ячейках и в целевой ячейке существенно различаются.
Показывать результаты итераций - приостанавливает поиск решения
для просмотра результатов отдельных итераций.
Загрузить модель - после щелчка на этой кнопке отрывается
одноименное диалоговое окно, в котором можно ввести ссылку на
диапазон ячеек, содержащих модель оптимизации.
Сохранить модель - служит для отображения на экране одноименного
диалогового окна, в котором можно ввести ссылку на диапазон ячеек,
предназначенный для хранения модели оптимизации.
Оценка линейная - выберите этот переключатель для работы с линейной
моделью.
Оценка квадратичная - выберите этот переключатель для работы с
нелинейной моделью.
Разности прямые - используется в большинстве задач, где скорость
изменения ограничений относительно невысока. Увеличивает скорость
работы средства Поиск решения.
Разности центральные - используется для функций, имеющих
разрывную производную. Данный способ требует больше вычислений,
однако его применение может быть оправданным, если выдано
сообщение о том, что получить более точное решение не удается.
Метод поиска Ньютона - требует больше памяти, но выполняет меньше
итераций, чем в методе сопряженных градиентов.
46
Метод поиска сопряженных градиентов - реализует метод сопряженных
градиентов, для которого требуется меньше памяти, но выполняется
больше итераций, чем в методе Ньютона. Данный метод следует
использовать, если задача достаточно большая и необходимо экономить
память или если итерации дают слишком малое отличие в
последовательных приближениях.
Рис. 4.17. Параметры поиска решения
Совокупность установленных параметров и ограничений можно сохранить на
рабочем листе в качестве модели.
Вопросы для самоконтроля
1. Перечислите средства оптимизации в электронных таблицах.
2. Что такое целевая ячейка?
3. Что такое Подбор параметра и без чего оно не возможно?
4. Можно ли производить подбор параметра на диаграмме?
5. Что такое ячейка ввода?
6. Виды таблицы подстановки?
7. Что такое сценарий?
8. Что такое консолидация?
9. В чем заключается цель поиска решения?
10.Перечислите параметры поиска решения.
47
Лекция 5
Раздел 5. Работа с макросами
5.1. Макросы
VBA (Visual Basic for Applications) - это объектно-ориентированный язык
макропрограммирования высокого уровня, встроенный во все программы
пакета Microsoft Office.
Преимущества:
1 – с помощью VBA можно записывать последовательности повторяющихся
программ, характерных для какого-либо приложения, и назначать правила
(условия), в соответствии с которыми эти последовательности (процедуры
VBA) будут вызываться в приложении. Например, после щелчка на некоторой
кнопке, рабой лист будет отформатирован по вашим требованиям,
записанным в макросах.
2 – применив средства VBA, можно обеспечить нестандартный диалог с
пользователем, создавая диалоговые формы и обрабатывая события, которые
генерируются в ответ на действия пользователя.
Язык VBA является расширением Visual Basic.
Макрос (или макрокоманда) - последовательность команд и функций,
записанных в модуле VBA, позволяющая автоматизировать выполнение
основных операций.
Существует два способа создания макроса:
записывать свои действия во время работы с рабочим листом;
написать макрос, используя алгоритмический язык Visual Basic.
Наиболее простым методом создания макрокоманды является ее запись с
помощью макрорекордера, который протоколирует все выполняемые
пользователем действия и представляет их в виде программного кода VBA.
5.1.1. Макрорекордер
Макрорекордер протоколирует операции, выполняемые пользователем, и
преобразует их в последовательность операторов VBA.
Процесс работы макрорекордера можно условно разделить на четыре этапа:
- запуск макрорекордера;
- присвоение макросу имени;
- выполнение записываемых операций;
- завершение записи.
Начало записи
При записи макроса фиксируются все действия, в том числе и ошибочные.
Для начала записи макроса необходимо активизировать чистый рабочий
лист, выбрав в меню Сервис команду Макрос, затем – Начать запись. На экране
48
появится диалоговое окно Запись макроса, в котором следует указать имя
макроса, ввести его краткое описание, задать комбинацию клавиш для запуска
создаваемого макроса и определить его место хранения.
Примечание:
- Имя макроса может состоять максимум из 255 символов. Оно не должно
содержать пробелов и специальных символов. Между фрагментами
длинных имен макросов вместо пробелов вставляются символы
подчеркивания (_).
- При назначении комбинации клавиш для запуска макроса учитывается
регистр символов.
Рис. 5.1. Запись макроса
После щелчка на кнопке ОК в диалоговом окне запись макроса появится
панель инструментов.
– Остановить запись – остановить процесс записи макроса.
– относительная ссылка – определяется режим адресации при записи
макроса. Если кнопка не активизирована, по умолчанию применяются
абсолютные ссылки.
В процессе записи макроса программа напоминает пользователю, что все
выполняемые им действия записываются макрорекордером.
Рис. 5.2. Строка состояния
ПРИМЕР (рис. 5.3):
- Отменить отображение сетки (рис. 5.3-а);
- Задать для всех ячеек денежный формат (рис. 5.3-б);
- Установить вид и размер шрифта текста ячеек (рис. 5.3-в).
Выполнив перечисленные действия, необходимо остановить запись (рис. 5.3г).
Рис. 5.3-е иллюстрирует результат выполнения макроса.
49
а)
в)
б)
г)
д)
е)
Рис. 5.3. Создание макроса при помощи макрорекордера
Запуск макроса осуществляется активизацией команды Сервис – Макрос –
Макросы, где отмечается нужный макрос, или нажав сочетание клавиш Ctrl и
любой другой установленной.
Рис. 5.4. Окно макроса
5.1.2. Панель инструментов для работы с макросами
Для отображения панели работы с макросами (рис. 5.5) необходимо:
Вид – Панель инструментов – Visual Basic.
Рис. 5.5. Панель VBA
50
Кнопка
Таблица 5.1. Основные кнопки панели инструментов VBA
Название
Описание
Выполнить
Открывается диалоговое окно Макрос, где
макрос
имеется возможность выбора необходимого
макроса для записи или редактирования
Записать
Запись макроса (остановка записи макроса)
макрос
Безопасность Задается уровень защиты от макровирусов и
указываются надежные разработчики макросов
Редактор
Запускается редактор или активизируется
Visual Basic
Элементы
Выводи панель Элементы управления для
управления
вставки необходимых элементов
Режим
Кнопка действует как выключатель
конструктора
Режим
Запускает редактор Microsoft Script Editor для
сценариев
создания HTML- и XML-страниц
5.1.3. Редактирование макроса
Записанные макросы можно редактировать команда Сервис – Макрос –
Реактор Visual Basic, можно также воспользоваться кнопкой редактор Visual
Basic на панели инструментов или клавишами Alt+F11.
После активизации указанных элементов открывается окно Microsoft Visual
Basic. Для редактирования макроса необходимо в окне Project (Проект) открыть
папку Modules (Модули) и выбрать имя модуля.
Рис. 5.6. Окно Microsoft Visual Basic с примером кода макроса рис. 5.3 е
В VBA текст макроса оформляется в виде процедуры Sub. Данная процедура
создается на основе событий (например, изменение размера окна или щелчок на
кнопке) и методов. Каждому событию подставлены в соответствии методы,
которые задают необходимость выполнения определенных операций при
51
наступлении данного события (пример, изменение активного листа, с
изменением которого может измениться команда меню). В VBA пользователь
может задавать собственные события и ставить им в соответствии методы.
Макрос должен начинаться с оператора Sub и заканчиваться End Sub, между
которыми размещаются имя макроса для идентификации, текст макроса,
который можно редактировать, а также снабжать комментариями.
Для активизации режима отладки можно воспользоваться пошаговым
выполнением. Для этого меню Debug (Отладка) – Step Info (Пошагово) или
клавиши F8. Кроме того, можно устанавливать точки для остановки
выполнения макроса и его редактирования.
Рис. 5.7. Пошаговая отладка макроса
5.1.4. Создание панели инструментов для запуска макросов
Можно создавать собственные панели инструментов. Меню Сервис –
Настройка – на вкладке Панели Инструментов кнопка Создать (рис. 5.8).
Рис. 5.8. Создание панели инструментов
Для вставки кнопок необходимо перейти на вкладку Команды, где
представлены все доступные элементы панелей инструментов и все команды,
которые объединены в категории.
52
Рис. 5.9. Настройка панели инструментов: назначение макроса
Таким образом, на рабочий лист можно добавить либо новую кнопку в
главное меню или новую панель инструментов созданную сами пользователем.
5.2. Элементы управления на рабочем листе
Элементами управления называются размещаемые на рабочих листах и в
диалоговых окнах объекты, предназначенные для отображения, ввода и
вычисления данных. К их числу относятся надписи, поля со списками,
переключатели, флажки, кнопки и некоторые другие объекты. С помощью
элементов управления можно запустить макросы и веб-сценарии.
Виды элементов управления: ActiveX и элементы управления панели
инструментов Формы.
Элементы управления ActiveX способны работать с макросами VBA и вебсценариями. Они являются независимыми программными компонентами.
Кнопки для вставки этих элементов управления рассоложены на панели
инструментов Элементы управления.
Элементы управления панели инструментов Формы пакета M.Office
обеспечивают совместимость с ранними версиями Excel и могут
использоваться на листах макросов XLM.
Рис. 5.10. Элементы управления
5.2.1. Элементы управления ActiveX
Для открытия панели инструментов необходимо правой кнопкой мыши
щелкнуть на любой другой панели и в появившемся списке выбрать пункт
53
Элемент управления или команда Вид – панели инструментов – Элементы
управления.
Рис. 5.11. Панель элементов управления
Таблица 5.1. Элементы управления
Значок Название
Режим
конструктора
Свойства
Исходный
текст
Флажок
Назначение
Переводит Excel в режим конструктора и
отключение всех элементов на рабочем листе
Активизация окна Свойства с перечнем свойств
листа, активного на данный момент
Открытие окна редактора Microsoft Visual Basic
Позволяет задать или отменить определенный
параметр
Поле
Добавление области, в которой можно вводить и
редактировать текст
Кнопка
Создание кнопки, предназначенной для запуска
Visual Basic
Переключатель Для выбора одного или нескольких параметров
Список
Размещение списка, где можно выбрать один или
несколько элементов
Поле
со Создание поля со списком, предоставляющего
списком
пользователю возможность ввести текст либо
выбрать элемент из списка
Выключатель
Добавление на рабочее поле выключателя
Счетчик
Полоса
прокрутки
Используется для увеличения или уменьшения
числовых значений
Создание вертикальной полосы прокрутки
Надпись
Надпись в виде текста
Рисунок
Изображении
Другие
элементы
Добавление дополнительных элементов управления
54
Для создания элемента управления необходимо щелкнуть на нужном
элементе на панели. Затем выбранный элемент автоматически переходит в
режим конструктора, при котором можно изменять параметры и свойства
элемента. Для работы с элементом необходимо выйти из режима конструктора.
Имена элементам даются автоматически в зависимости от типа объекта
(Label1, label2 и т.д.). Данные имена используются для работы с макросами и
несут смысловую нагрузку. Для переименования необходимо открыть окно
Свойства (Properties) на панели инструментов Элементы управления – задать
новое имя в строке Name – Enter.
Для переименования самого элемента необходимо в режиме конструктора
выделить элемент правой кнопкой мыши – Объект – Edit или Caption в
Свойствах.
Для форматирования элемента управления ActiveX можно воспользоваться
двумя окнами:
– с помощью окна Формат элемента управления определяются цвет и вид
линий контура, размеры, позиция и др. (рис. 5.12).
Рис. 5.12. Окно Формат элемента управления
– в окне Properties (Свойства) задаются свойства элементов управления,
определяющие как общие характеристики объекта (размер, цвет и др.), так и
поведение элемента управления.
Диалоговое окно Properties
Свойства изменяются в режиме конструктора, устанавливая нужные
значения в окне Properties
55
Рис. 5.13. Вид окна Properties
Основные свойства окна Properties:
Name – определяет имя, под которым объект фигурирует в программе.
Accelerator – предназначен для быстрого доступа к объекту с помощью
клавиатуры.
AutoSize – автоматически изменяет размеры элемента управления,
чтобы на экране был виден весь его заголовок.
BackColor – определяет цвет фона.
BackStyle – указывает, будет ли метка прозрачной.
BarderStyle – определяет, выводится ли рамка на экран. Если это так, то
ее цвет зависит от свойства BorderColor.
Caption – выводит текст заголовка.
Enabled – определяет, можно ли выделить объект (по отношению к
тени).
Font – открывает диалоговое окно Шрифт для изменения стиля и
размера шрифта.
ForeColor – определяет цвет текста заголовка.
Height – устанавливает высоту элемента управления, которую также
можно изменить изменяя ее мышью.
Left – устанавливает расстояние между левым краем элемента
управления и левым краем рабочего листа.
MousePointer – определяет вид указателя мыши, когда он находится на
элементе управления.
Picture – выбор рисунка.
56
Shadow – принимает значение Истина или Ложь в зависимости от того,
имеет ли объект тень.
SpecialEffect – определяет вид объекта.
Top – указывает расстояние между верхними краями элемента
управления и рабочего листа.
Visible – определяет, является ли элемент видимым.
Width – задает ширину объекта.
WordWrap – предназначен для оптимального размещения заголовка на
объекте.
5.2.2. Элементы управления панели инструментов Формы
Предназначены для обеспечения совместимости с документами старых
версий Excel, использующими только эти элементы управления. Функции
аналогичны функциям элементов управления ActiveX, однако обладают
меньшими возможностями.
Для открытия панели инструментов необходимо правой кнопкой мыши
щелкнуть на любой другой панели и в появившемся списке выбрать пункт
Элемент управления или команда Вид – Панели инструментов – Формы.
Рис. 5.14. Панель инструментов Формы
Таблица 5.2. Дополнительные элементы управления панели Формы
Значок Название Назначение
Группа
Для создания на рабочем листе рамки для группы
элементов управления
Свойство Открывается диалоговое окно Формат элемента
элемента управления, в котором задаются параметры элемента
управления
Сетка
Сокрытие или отображение на рабочем листе сетки
(разлинованных ячеек)
Для форматирования элемента управления Форма используется диалоговое
окно Формат элемента управления, которое открывается нажатием правой
кнопкой мыши на элемент – команда Формат объекта.
5.2.3. Создание выпадающего списка при помощи меню Проверка
Пример использования выпадающего списка через ЭУ Список:
1) Простой
Сочетание клавиш Alt + стрелка вниз позволят из введенных уже данных
выбрать один вид в текущую ячейку:
57
Рис. 5.15. Простое задние выпадающего списка
2) Стандартный
а) В свободных ячейках вне рабочей области создается диапазон данных,
которые должны быть в дальнейшем «выпадать» через Список. Для удобства
диапазону этих данных может быть присвоено Имя (Вставка – Имя –
Присвоить).
б) Выделяются ячейки, в которых необходимо получить выпадающий список,
Данные – Проверка. На вкладке Параметры: Тип данных – Список, Источник –
диапазон данных или Присвоенное имя.
а)
б)
в)
г)
д)
е)
ж)
58
Рис. 5.16. Стандартное задание выпадающего списка
Вопросы для самоконтроля
1.
2.
3.
4.
5.
6.
7.
8.
В чем достоинство создания макроса?
Какими способами можно создать макрос?
В чем заключаются достоинства и недостатки макрорекордера?
Как можно провести редактирование макроса?
Что такое элемент управления?
Виды элементов управления?
Для чего необходим режим конструктора?
Как можно произвести форматирование элемента управления?
59
Лекция 6
Раздел 6. Основы VBA (Visual Basic for Application)
Тема: Основы VBA
6.1. Объекты, методы, свойства
Visual Basic позволяет создавать программные продукты, способные с
достаточной полнотой автоматизировать решение конкретных задач
пользователя.
VBA является объектно-ориентированной средой, содержащей большой
набор объектов, каждый из которых обладает множеством свойств и методов.
Объекты и инструменты относятся к определенному классу (например, класс
TextBox).
Свойства и методы также являются членами класса. С помощью свойств
описывается, как выглядит объект, в частности дается информация о приемах
форматирования текста, цвете и размере шрифта. Методы являются
процедурами, которые могут быть выполнены для объекта (процедуры
создания и удаления объекта, процедуры событий, определяющие принцип
взаимодействия объекта с пользователем и т.д.).
Объекты (аналог существительному)
Объектом считается любой элемент приложения ─ ячейка, лист, рабочая
книга, диаграмма. Фактически объектом является и само приложение Excel.
Объекты могут включать области ячеек, рамки ячеек, окна, сценарии, стили и
т.д. Каждый класс объектов имеет свое множество свойств, функций и событий.
Методы (аналог глаголу)
Метод ─ это действие, которое может быть выполнено над объектом.
Методы реализуются посредством выполнения процедуры, которая является
членом класса объектов.
Синтаксис вызова: Объект.Метод – указываются имя вызывающего метод
объекта и имя самого метода, разделенные точкой.
Пример: Ball.Kick или Мяч.Ударить; Вода.Пить
Комбинация двоеточия и знака равенства в коде всегда указывают на
параметр метода, т.е. как выполняется действие.
Методы могут иметь много параметров как обязательные, так и нет.
Пример 1: защита листа Лист1 от внесения изменения (метод Protect)
Sheets(―Лист1‖).Protect
Пример 2: добавление нового рабочего листа
Worksheet.Add Before:=Worksheets(1)
Свойство (аналог прилагательному)
Свойство является атрибутом объекта, описывающим, как объект выглядит
(его цвет, размер и местоположение) и как он действует (является ли видимым,
ссылается ли на другой объект). При создании объекта выполняется процедура
создания экземпляра этого объекта.
60
Для присваивания свойству нового значения, необходимо создать оператор
присваивания, в котором слева от знака равенства будут указаны имя и
свойство объекта (разделенные точкой), а справа ─ новое значение.
Синтаксис вызова: Объект.Свойство
Свойство всегда присутствует в левой или правой части выражений,
связанных с присвоением значения. Отсутствует двоеточие перед знаком
равенства.
Пример 1: переименование Лист1 на Счета:
Sheets(―Лист1‖).Name =‖Счета‖
События
Событие – взаимодействие пользователя с определенным объектом на
рабочем листе. Каждый класс объектов имеет собственную группу событий, на
которые объекты данного класса реагируют.
Аргументы
Аргументы используются для передачи методам значений, необходимых им
при решении тех задач, для реализации которых они запрограммированы.
Задача выполняется корректно только в том случае, если каждый элемент имеет
тип, который указан для данного метода. В качестве аргументов могут
передаваться числа, текст и логические значения (истина и ложь).
Предоставляется два способа передачи аргументов методу:
─ внутренний, при котором аргументы должны быть указаны в
определенном порядке;
Пример: ActiveCell.BorderAround LineStyle. Weight. ColorIndex. Color
Действие метода BorderAround объекта Range задает новые атрибуты рамки
вокруг указанной области. При его использовании требуется задать аргументы
для определения стиля линии, ее толщины и цвета. Причем свойство ColorIndex
позволяет определить цвет с помощью числа, а свойство Color ─ с помощью
константы VisualBasic.
─ внешний, при котором следования аргументов произвольно.
Пример: Range(―A1:C7‖).Border Around Color Index:=3, Weight:=xlThick
Тип аргументов:
– обязательные аргументы (для редактирования параметров ЭТ)
– необязательные аргументы (Для редактирования объектов (изменение
цвета, размера, рамки))
6.2. Структура, используемая в VBA
На высшем уровне иерархии стоит приложение, далее идут проекты,
связанные с фактическими документами этого приложения. На третьем уровне
находятся модули (модули приложения, модули пользователя, модули класса,
модули форм и модули ссылок), а на последнем – их процедуры и функции.
Иерархия, используемая в VBA, представлена на рис. 6.1.
61
Проект
Приложение
Проект2
…
ПроектN
Модуль
Модуль2
Объявления
…
Процедура1
МодульN
…
ПроцедураN
Рис. 6.1. Иерархия, используемая в VBA
Модуль – это часть программы, оформленная в таком виде, при котором
допускается ее независимая трансляция. Модуль состоит из двух разделов:
раздела объявлений (Declaration) и раздела процедур и функций. В первом
разделе описываются глобальные переменные, типы, определенные
пользователем, и перечисляемые типы, во втором – процедуры и функции.
Процедурой называется фрагмент кода (минимальная семантически
законченная программная конструкция), заключенный между операторами Sub
и End Sub.
Синтаксис определения процедуры:
[Private Public Friend] Sub
аргумент_n)
оператор VBA
оператор VBA
…
оператор VBA
End Sub
имя_процедуры(аргумент_1,
аргумент_2,_,
Private – задает область видимости для процедуры – модуль, в котором она
описана. Ее могут вызывать только процедуры этого же модуля
Public – процедура становится доступной для всех модулей
(устанавливается по умолчанию)
Friend – процедура видима только в том проекте, где описан класс, членом
которого она является.
Также в VBA используются процедуры без параметров, которые могут
выступать в роли командных макросов и процедур обработки событий.
На рис. 6.2 представлен интерфейс VBA.
62
Рис. 6.2. Интерфейс VBA
Лекция 7
Тема: Работа с данными в VBA
6.3. Типы данных, используемые в VBA
6.3.1. Переменные
Переменная – это поименованная область памяти, используемая для
хранения данных в течение работы процедуры.
Для использования переменной ее необходимо описать (объявить).
Синтаксис оператора описания переменной:
Dim переменная [As тип]
Где:
Dim –
ключевое слово, свидетельствующая о том, что объявляется
переменная (dimension – размер);
Переменная – имя объявляемой переменной;
As – ключевое слово, используемое при задании типа данных (as – как);
Тип – тип данных для объявляемой переменной
Одним оператором Dim можно описать несколько переменных, перечислив
их через запятую.
Dim i As Byte, j As Integer, k As Integer
В таблице 6.1. представлены основные типы данных, используемые для
задания переменных.
Таблица 6.1. Типы данных
Тип данных
Boolean
(логический)
Значения переменной
логические переменные, принимающие одно из двух
значений: Истина или Ложь
63
Byte (короткий
целый
беззнаковый)
Integer (целый)
Long (длинный
целый)
Currency
(денежный)
целое число из диапазона от 0 до 255
целые числа из диапазона от -32 768 до 32 767
целые числа из диапазона от -2 147 483 648 до
2 147 483 647
переменные для денежных вычислений с фиксированным
количеством разрядов после десятичной запятой;
позволяют избежать накопления погрешностей при
округлении
Date (дата)
переменные для хранения даты и времени
Single
числа с дробной частью от -3,402823 1038 до -1,401298 10-45
(с
плавающей для отрицательных чисел и от 1,401298 10-45 до
точкой
3,402823 1038 для положительных чисел
одинарной
точности)
Double
числа с дробной частью от -1,79769313486231 10308 до
(с
плавающей -4,94065645841247 10-324
точкой двойной для отрицательных чисел и от 4,94065645841247 10-324до
точности)
1,79769313486231 10308 для положительных чисел
String
переменные для хранения строк символов длиной от 0 до
(строковой
64 Кбайт
переменной
длины)
Variant
Автоматическое подстраивание под данные
(универсальный)
Object (объект)
переменные для хранения ссылок на объекты
Если при описании переменных не указывается их тип, то им автоматически
присваивается Variant. Это означает, что в ячейке, соответствующей этой
переменной, может храниться информация любого вида (аналог формату
«Общий» в ЭТ).
Пример: Dim i, j As Integer
Это эквивалентно следующей записи: Dim i As Variant, j As Integer
Для записи одинакового формата необходимо:
Dim i As Integer, j As Integer
Для использования тех или иных переменных в разных частях программы
используется так называемая область видимости.
Области видимости переменной – это область программ, где имя переменной
считается допустимым (видимым), а, следовательно, возможен доступ к ее
значению (рис. 6.3).
64
Приложение
Проект
Модуль
Процедура
Рис. 6.3. Области видимости переменной VBA
Существуют три уровня видимости переменной и пять способов ее
объявления.
1Ур. – Процедура (областью видимости является процедура, в которой
переменная объявлена).
*** Оператор Dim объявляет переменную в любом месте процедуры, но
всегда предшествует операторам, использующим ее. Такая переменная может
существовать только во время выполнения процедуры, после окончания
которой значение этой переменной теряется, а память освобождается.
*** Static (аналогичен Dim) – но! объявляет статическую переменную.
После выхода из процедуры память не освобождается и значение не теряется.
2 Ур. – Модуль
*** оператор Private объявляет переменную в разделе описаний Declaration
(вне процедур модуля)
*** оператор Dim (в данном случае) полностью аналогичен оператору
Private
3 Ур. – Приложение
*** оператор Public объявляет переменную в разделе описаний Declaration
6.3.2. Константы
Разделяются на пользовательские и встроенные.
Пользовательские константы требуют объявления. Для этого используется
оператор вида:
Const константа [As тип] = значение
Где:
Const – ключевое слово, которое показывает, что объявляется константа;
As – ключевое слово, с которого начинается задание типа данных;
Константа – имя объявляемой константы;
Тип – тип данных для константы;
Значение – значение, присваиваемое константе.
Пример:
Const pi As Double = 3.141592654
65
Const e As Double = 2.718281828
Const Message = ―Завершение работы‖
Можно объявлять несколько констант через запятую:
Const min = 0, max = 1000
Встроенные константы не требуют объявления. Имена встроенных констант
начинаются с префикса vb, например, vbFriday.
6.4. Использование стандартных окон операционной системы Windows
В VB имеется большое количество встроенных процедур, которые
отличаются от пользовательских процедур тем, что их описание были
запрограммированы разработчиками VBA.
Для ввода пользователем информации в код программы и вывод данных из
него, а также для создания пользовательских диалоговых окон используются
функции MsgBox и InputBox.
6.4.1. Функция MsgBox
MsgBox (―сообщение‖, [кнопки, заголовок]) - эта функция отображает
диалоговое окно, содержащее сообщение длиной до 1024 символов, в которое с
помощью операции конкатенации можно включить значение переменных, а
также (необязательно) кнопки для реакции на отображения окна (по умолчанию
только кнопка ОК).
При задании сложного диалогового окна, при помощи функции MsgBox
используются следующие константы:
1) Для задания внешнего вида окна сообщения (рис. 6.4):
vbCritical, vbQuestion, vbExclamation, vbInformation.
Рис. 6.4. Внешний вид окон
2) Для задания кнопок в окне сообщения:
vbOkCancel, vbAbortRetryIgnore, vbYesNOCancel, vbYesNO, vbRetryCancel.
3) Для задания дальнейших действий после нажатия на соответствующую
кнопку:
vbOk, vbCancel, vbAbort, vbRetry, vbIgnore, vbYes, vbNO.
Пример процедуры 1:
Private Sub Пример_1()
66
y = MsgBox("Закрыть окно", vbQuestion + vbYesNoCancel, "Сообщение
Windows")
End Sub
Код процедуры 1 в VBA и результат выполнения программы представлен
на рис. 6.5.
Рис. 6.5. Пример процедуры 1
Пример процедуры 2:
Sub Привет()
y = MsgBox("Закрыть окно", vbQuestion + vbYesNoCancel, "Сообщение
Windows")
If y = vbYes Then MsgBox ("VVVVVVVVVVVVVVVVVVVV") Else
If y = vbNo Then ActiveCell = "Привет"
End Sub
Код процедуры 2 в VBA и результат выполнения программы представлен на
рис. 6.6.
Рис. 6.6. Пример процедуры 2
6.4.2. Функция InputBox
InputBox (―сообщение‖[, заголовок] [, значение по умолчанию] [, координата
x] [, координата y]) - функция, применяемая для ввода значений переменных в
программу. Эта функция отображает диалоговое окно, содержащее окно ввода,
кнопки ОК и Отмена, сообщение (подсказку для ввода) и (необязательно)
заголовок окна, значение, вводимое по умолчанию, координаты окна по
горизонтали и вертикали.
Так ввод числа можно задать командой:
a = InputBox ("первое число")
67
Рис. 6.7. Вид функции InputBox
6.4.3. Совместное использование функций MsgBox и InputBox
На практике для создания процедур функции MsgBox
и InputBox
используются совместно. Кроме того, в дополнение к ним может
использоваться условный оператор If, который позволяет проверять введенные
пользователем условия и на основе его выводов выдавать результат.
Условный оператор If - это оператор позволяющий задавать выполнение тех
или иных действий в зависимости от заданных условий. Основными
составляющими для этого служат:
1) if (если)
2) then (тогда)
3) else (иначе)
Так выражение - если a>1 то b= a+1 иначе b=a-1 будет иметь вид
If a>1 then b= a+1 else b=a-1.
Пример процедуры 3:
Компьютер должен перемножить два числа, результат вывести в одну из
ячеек таблицы. Если их произведение больше 2000, то компьютер должен
выдать дополнительное сообщение "Полученное значение больше 2000".
Sub пример_2()
Dim a, b, y As Long
a = InputBox("первое число")
b = InputBox("второе число")
Range("A1") = a
Range("A2") = b
y=a*b
Range("A4") = y
If y < 2000 Then Range("A4") = y Else MsgBox ("Полученное значение
больше 2000")
End Sub
Код процедуры 3 в VBA и результат выполнения программы с разными
условиями представлен на рис. 6.8.
68
Рис. 6.8. Пример процедуры 3
Рассмотрим программу, которая включает в себя сложную функцию MsgBox
и оператор If.
Пример процедуры 4:
Вводятся два произвольных числа. Затем задаѐтся вопрос ―Вы уверены что
хотите их перемножить?‖ и варианты ответов: ―да‖, ―нет‖. Если ответ ―да‖ - то
числа перемножаются, и выдается сообщение с результатом, иначе действие не
производится.
Sub Пример()
Dim a, b, d As Double
a = InputBox("первое число")
b = InputBox("второе число")
y = MsgBox("Вы уверены, что хотите их перемножить? ", vbCritical +
vbYesNo, "Вопрос")
If y = vbYes Then d = a * b Else MsgBox ("Действие отменено")
If y = vbYes Then MsgBox (d)
ActiveCell = d
End Sub
Код процедуры 3 в VBA и результат выполнения программы с разными
условиями представлен на рис. 6.9.
69
Рис. 6.9. Пример процедуры 4
Лекция 8
Тема: Управляющие конструкции VBA
6.5. Управляющие конструкции VBA
Управляющие конструкции языка программирования – это инструкции и
группы инструкций, применение которых позволяет изменять по мере
необходимости
последовательность
выполнения
других
инструкций
программы. Данные конструкции подразделяются на ветвления и циклы.
Ветвлением называется управляющая конструкция, позволяющая пропускать
при выполнении те или иные группы инструкций в зависимости от значения
условия. Цикл – это управляющая конструкция, представляющая возможность
многократно выполнять группы инструкций до наступления какого-либо
события.
6.5.1. Ветвление
Конструкция If…Then
Конструкция If…Then дает VBA указание принять простейшее из решений:
если условие, идущие после оператора If, истинно, нужно выполнить
следующие за ними оператор (или операторы); если же условие ложно, нужно
перейти к строке, расположенной непосредственно за условной конструкцией.
Однострочная конструкция:
70
If условие Then оператор[ы]
При использовании нескольких операторов (блок If):
If условие Then
оператор
[операторы]
End If
Пример однострочной конструкции:
Sub vozrast()
Age = InputBox("укажите свой возраст.", "Возраст")
If Age < 21 Then MsgBox "Вы не можете покупать алкогольные напитки.", ,
"несовершеннолетний"
End Sub
Рис. 6.10 Пример конструкции If then
Конструкция If…Then…Else
С помощью данной конструкции можно выбрать одно направление
деятельности, если условие истинно, и другое – если оно ложно. Например,
можно использовать для работы с окнами, содержащими две кнопки.
Синтаксис конструкции:
If условие Then
операторы_1
Else
операторы_2
End If
Если условие истинно, VBA выполняет первую группу операторов –
операторы_1, если же оно ложно, осуществляется переход к строке Else, а затем
выполняется вторая группа операторов – оператор_2.
71
Пример:
Sub vozrast()
Age = InputBox("укажите свой возраст.", "Возраст")
If Age < 21 Then
MsgBox "Вы не можете покупать алкогольные
"несовершеннолетний"
Else
Vkus = InputBox("что Вы хотите приобрести?", "Вкус")
End If
End Sub
напитки.",
,
Рис. 6.11. Пример конструкции If Then Else
Конструкция If…Then…ElseIf…Else
С помощью данной конструкции можно выбрать одно из нескольких
направлений действий. Можно использовать любое количество строк с ElseIf в
зависимости от сложности программы.
Синтаксис конструкции:
If условие_1 Then
операторы_1
ElseIf условие_2 Then
операторы_2
ElseIf условие_3 Then
операторы_3
Else
операторы_4
End If
Целесообразно использовать данную конструкцию с числом операторов
ElseIf не больше 5. В этом случае лучше использовать конструкцию Select Case.
Конструкция Select Case
Вместо нескольких операторов ElseIf можно применить конструкцию Select
Case для более сжатого вида программы.
72
Такую конструкцию рекомендуется использовать, если решение, которое
необходимо принять в программе, зависит от одной переменной или от
выражения, имеющего не менее трех-четырех значений. Такая переменная (или
выражение) называется тестируемым случаем.
Повторяемое выражение сравнивается с выражениями, находящимися после
Case. Если они совпадают, то выполняется соответствующий оператор, иначе
проверяются другие выражения. В случае несовпадения ни одного из
выражений, выполняются операторы, следующие за Case Else.
Синтаксис конструкции:
Select Case повторяемое_выражение
Case выражение_1
операторы_1
Case выражение_2
операторы_2
Case Else
операторы
End Select
6.5.2. Циклы
Существует три типа циклов: с управляющим условием, со счетчиком и
циклы по структуре данных.
В циклах с управляющим условием оператор или группа операторов
повторяется, пока условие не будет выполнено. Такие циклы называются
циклами Do.
Если заранее известно количество повторений тела цикла, проверять
управляющее условие нет необходимости. Такие циклы называются циклами с
повторяющим перечислением. Циклы такого типа подразделяются на две
группы: циклы со счетчиком и циклы по структуре данных.
В циклах со счетчиком используется специальная переменная – счетчик,
значение которой при каждом повторении тела цикла увеличивается или
уменьшается на заданную величину – шаг цикла. Цикл завершается после того,
как значение счетчика достигнет (или превысит) конечное значение счетчика
цикла.
Синтаксис:
For счетчик = начальное_значение To конечное_значение Step шаг_цикла
<тело цикла>
Next счетчик
Пример вычисления суммы значений, содержащихся в нечетных ячейках
первого столбца первого листа (в пределах первых 10 ячеек):
Sub Loop_For()
Dim I As Integer
73
Dim S As Long
S=0
For I = 1 To 10 Step 2
S = S + Application.Worksheets(1).Cells(I, 1).Value
Next I
MsgBox (S)
End Sub
Рис. 6.12. Пример цикла 1
В циклах по структуре данных тело цикла поочередно повторяется для всех
однородных объектов, составляющих массив или семейство. В этом случае в
роли счетчика выступает объектная переменная.
Синтаксис:
For Each элемент In структура_данных
<тело цикла>
Next элемент
Пример последовательного вывода на экран окон сообщений с именами
всех рабочих листов текущей книги:
Sub loop_date()
Dim S As Worksheet
For Each S In Application.Worksheets
MsgBox (S.Name)
Next S
End Sub
Рис. 6.13. Пример цикла 2
6.6. Использование элементов управления для запуска макроса и ввода
данных
Для использования элементов управления на рабочем листе необходимо для
каждого из элементов написать код в VBA. Для этого предварительно
необходимо перейти в режим конструктора. Также в этом режиме изменяются
свойства объекта в окне Properties.
74
Для записи программного кода элемента управления необходимо дважды
щелкнуть мышью на этом элементе (рис. 6.14 а) и он автоматически перейдет в
режим VBA и сформирует «операторные скобки» с учетом события
пользовательской формы (рис. 6.14 б).
а)
б)
Рис. 6.14. Формирование операторных скобок элемента управления
События пользовательской формы
Событие - это сигнал, подаваемый, если с объектом что-то происходит.
Например, кнопка может генерировать событие в ответ на щелчок мышкой по
ней, строка ввода – в ответ на ввод чего-то, на щелчок мыши по ней, и т.д.
Рис. 6.15 События пользовательской формы
Некоторые виды событий:
События мыши — одинарное (двойное) щелканье левой кнопкой мыши на
объекте; нажатие (отпускание) кнопки мыши; передвижение курсора мыши
по элементу управления.
События клавиатуры — нажатие простого символа, функциональных
клавиш или какого-то сочетания символов на клавиатуре.
События формы — загрузка (Load), выгрузка (Unload) формы и пр.
Событие Click происходит, когда пользователь нажимает и отпускает кнопку
мыши, в то время, когда ее указатель находится на объекте. Событие может
произойти при изменении значения элемента управления.
Формат процедуры-обработчика события
Private Sub Form_Click()
Private Sub object_Click([index As Integer])
75
где object – имя объекта, к которому относится этот обработчик.
Пример:
Private Sub Form_Click()
MsgBox “Click”
End Sub
Рис. 6.17. Пример создания кнопки
Написание кода программы для ЭУ
Программирование элемента управления происходит по тем же принципам,
что программирование макроса.
Программирование кнопки:
Пример 1: Написание Привет после нажатия кнопки:
Private
Sub
MsgBox "Привет :)"
End Sub
Рис. 6.18.
управления
Кнопка_Click()
Пример
1
создания
элемента
Пример 2: Проверка условий: пересчет таблицы
Рис. 6.19. Исходная таблица
Создаем кнопку (рис. 6.20)
76
Рис. 6.20. Создание кнопки для реализации примера 2
Двойной щелчок переводит в окно создания процедуры (рис. 6.21):
Рис. 6.21. Окно создания процедуры
Создаем код:
Private Sub sum_Click()
Dim I As Integer
Dim N As Long
Dim S As Long
S=0
N=0
For I = 2 To 8 Step 1
N = Cells(I, 2) * Cells(I, 3)
Cells(I, 4) = N
S = S + Cells(I, 4).Value
Next I
Cells(I + 2, 4) = S
MsgBox (S)
End Sub
Где Cells(I, 4) – номер ячейки, где I – строка, 4 – столбец (D).
Код рассматриваемого примера в VBA и результат выполнения программы
с разными условиями представлен на рис. 6.22.
77
Рис. 6.22. Реализация рассматриваемого примера
Лекция 9
6.7. Пользовательские формы, создаваемые в VBA
Пользовательские формы необходимы для создания более эффективного
интерфейса взаимодействия с пользователем.
Например, вместо ввода личной информации непосредственно на рабочем
листе можно создать пользовательскую форму, запрашивающую ввод данных,
или форма для программы расчета данных.
Формы являются объектами, которые могут вызываться другими модулями
приложения.
К формам можно добавлять пользовательские методы и свойства. Для
создания в форме нового метода надо добавить процедуру, объявленную со
словом Public:
Public Sub UserMethod()
операторы
End Sub
Стили интерфейса:
1) однодокументный (SDI) – можно открыть только один документ, при этом
нужно закрыть активный документ, чтобы открыть другой;
2) многодокументный (MDI) – поддерживает несколько форм внутри
основной формы-контейнера; имеет в меню Window элементы для
переключения между окнами или документами;
3) стиль explorer – окно, имеющее две панели, или области, обычно,
состоящие из иерархического представления уровней информации слева и
области отображения справа.
78
Этапы создания формы:
1. На форму помещаются нужные элементы управления и красиво
размещаются.
Результат: имеем форму с элементами управления, но надписи на них
стандартные: Command1, Label1, и т.д.
2. Задаются свойства формы и элементов управления
Результат: форма имеет нужный вид, на всех ЭУ понятные надписи, нужные
картинки и т.д. Запуск ее невозможен в связи отсутствия кода программы.
3. Пишутся обработчики событий элементов управления.
Результат: выполнение действий в соответствии с заданием.
Для вставки пользовательской формы необходимо Insert (Вставка) –
UserForm (Пользовательская форма).
При отсутствии окна свойства его можно открыть, выполнив: View (Вид)
Properties Window (Окно свойств).
Рис. 6.23. Интерфейс создания формы
Пример 1: Создать форму для расчета функции y
a2
место для вывода (из программы) результата расчета;
текстовое поле для ввода исходных данных;
кнопку для запуска программы и отмены.
b2 , содержащую:
После задания вида формы (рис. 6.24) следует задание кода программы.
Рис. 6.24. Создание формы для примера 1
79
Двойной щелчок по кнопке «Вычислить» переведет форму в редактирование
кода.
Private Sub Calc_Click()
Dim a As Single
Dim b As Single
Dim c As Single
1: a = TextBox_a.Value
2: b = TextBox_b.Value
3: c = Sqr(a ^ 2 + b ^ 2)
4: Label1.Caption = "c = " & Str(c)
End Sub
Private Sub Cancel_Click()
End
End Sub
Рис. 6.25. Иллюстрация работы формы примера 1
Пользовательская форма может быть выведена из любого модуля. Для ее
вывода используется метод Show. Name.Show
Private Sub VSch_Click()
UserForm1.Show
End Sub
Рис. 6.26. Использование метода Show
80
Пример 2. Создание формы для ввода пользователем переменных и
управляющих выключателей с суммированием и вычитанием с отображение
результата.
Рис. 6.27. Создание формы и ее код в VBA для примера 2
Private Sub CommandButton1_Click()
Dim first As Long, second As Long
first = tb1.Value
second = tb2.Value
'Если выбрана первая кнопка, складываем переменные
If ob1.Value = True Then
lab4.Caption = first + second
End If
'Если выбрана вторая кнопка, вычитаем переменные
If ob2.Value = True Then
lab4.Caption = first - second
End If
End Sub
Рис. 6.28. Иллюстрация работы формы примера 2
Пример 3. Создание формы для ввода пользователем данных и вывода их в
строки таблицы
Рис. 6.29. Создание формы для примера 3
81
Private Sub CB_Cancel_Click()
End
End Sub
Private Sub CB_ok_Click()
Dim LastRow As Long
LastRow = Worksheets("Лист3").Range("A65536").End(xlUp).Row + 1
Cells(LastRow, 1).Value = tb1.Value
Cells(LastRow, 2).Value = tb2.Value
Cells(LastRow, 3).Value = tb3.Value
End Sub
Рис. 6.30. Иллюстрация работы формы примера 3
Вопросы для самоконтроля
1. Что такое VBA?
2. Что такое объекты, методы и свойства в VBA ?
3. Опишите иерархию в вVBA?
4. Что такое процедура?
5. Что такое элемент управления?
6. Опишите синтаксис переменной?
7. Какие типы констант используются в VBA?
8. Для чего используются функция MsgBox и InputBox?
9. Какие управляющие конструкции используются в VBA?
10.Что такое событие пользовательской формы?
11.Что такое пользовательская форма?
12.Какие стили интерфейса используются при создании пользовательской
формы?
82
Лекция 10
Раздел 7. Обмен данными в электронной таблице
7.1. Использование буфера обмена
Самый простой способ переноса данных из одной офисной программы в
другую – воспользоваться буфером обмена, в который можно поместить до 12
различных фрагментов. Для просмотра содержимого буфера обмена нужно
вызвать специальную панель «Буфер обмена» (меню Вид
Панели
инструментов Буфер обмена).
Если программа не позволяет редактировать данные, то их нужно вставить в
виде внедренного объекта, который можно редактировать в исходной
программе, открыв объект двойным щелчком мыши. Например, таким образом,
ведет себя диаграмма, созданная в Excel, если вставить ее в документ Word.
Если данные не могут быть вставлены в виде внедренного объекта (например,
при копировании данных из программы, не поддерживающей механизм ОLE 1),
то они должны быть помещены в виде неизменяемого рисунка.
Для самостоятельного управления форматом данных, вставляемых из буфера
обмена, следует вместо команды меню Правка
Вставить использовать
команду Правка
Специальная вставка… В этом случае в открывшемся окне
«Специальная вставка» (рис. 1) можно выбрать из списка вариант конечного
результата вставки.
Рис. 7.1. Специальная вставка
Например, с помощью Специальной вставки таблицу, скопированную из
Excel, можно вставить в Word не только в виде отформатированной таблицы
Word, но и как внедренный лист Microsoft Excel, неформатированный текст, а
также в виде точечного или векторного рисунка.
7.2. Связь Excel c Access
В приложениях MS Office для обмена данными предусмотрены три типа
действий: экспорт, импорт и связывание. При операциях экспорта и импорта
OLE (Object Linking and Embedding) – технология связывание и внедрение объектов, являющаяся
одним из наиболее ярких примеров реализации концепции обмена данными между приложениями
Microsoft Office.
1
83
полученный результат не связан с исходными данными, а является их копией. В
свою очередь, при операции связывания данные продолжают храниться в
исходном документе, а обращаться к ним можно из других связанных
документов. Таким образом, связывание позволяет при работе с одним
источником данных использовать возможности разных приложений.
Предположим, вы работаете с данными в Excel и хотите воспользоваться
возможностями MS Access. Для этого вам потребуется установить связь с
Access. В Excel в меню Сервис
Надстройки нужно включить надстройку
AccessLinks (рис. 7.2) (возможно, для ее установки требуется дистрибутив
MS Office).
Рис. 7.2. Надстройка AccessLinks
Надстройка AccessLinks позволяет использовать формы и отчеты MS Access
при работе с данными MS Excel. В меню Данные в Excel будут добавлены три
команды:
Форма MS Access…;
Отчет MS Access…;
Перенести в MS Access… .
При выборе команды Данные Форма MS Access произойдет следующее:
по выбору будет создана новая или открыта существующая БД;
в этой БД автоматически будет установлена связь с исходной таблицей
Excel и запущен мастер форм Access, предлагающий создать новую
форму;
выполнив все шаги мастера форм, вы получите экранную форму Access
для работы с данными Excel.
Аналогично работает команда Данные
Отчет MS Access, только в
результате будет создан стандартный отчет Access.
Команда Данные
Перенести в MS Access… запустит Access и выполнит
экспорт данных из Excel в таблицу новой или существующей БД Access.
На рабочем листе Excel появляются кнопки для открытия формы (или отчета)
в Access, а после переноса данных появляется надпись об успешном его
окончании (рис. 7.3).
84
Рис. 7.3. Перенос данных в Access
С помощью этих кнопок можно повторно вызывать созданные в Access
формы или отчеты, связанные с хранящимися на рабочем листе данными.
Можно достичь подобного результата, работая в MS Access с данными из
электронных таблиц Excel.
При операции Импорта данных из Excel в MS Access подразумевается
получение извне и сохранение копии данных «приемлемого» формата. При
этом связь с источником исходных данных поддерживаться не будет (то есть
данные становятся «родными» для Access и «забывают» о своем предыдущем
местонахождении).
Для переноса данных из рабочей книги Excel в Access необходимо выбрать
команду Внешние данные Импорт.
В открывшемся диалоговом окне «Импорт» выберем тип файлов «Microsoft
Excel», укажем исходный файл Excel и нажмем кнопку Импорт. После этого
начнет работу мастер, который позволяет импортировать в Access содержимое
выбранного рабочего листа или именованного диапазона ячеек (рис. 7.4).
Рис. 7.4. Экспорт электронной таблицы
Из книги Excel можно импортировать в таблицу Access отобранные столбцы
из указанного диапазона или рабочего листа. Тип данных, хранящихся в полях
(столбцах) значений, распознается по содержимому нескольких первых записей
(строк), поэтому после импорта часто требуется его корректировка.
Иногда бывает удобно работать с одними и теми же данными одновременно
то из Excel, то из Access (то есть редактировать и добавлять данные в таблицу
как при работе в Excel, так и при обработке информации в базе данных MS
Access). В этом случае нужно в базе данных Access создать Связи с таблицами
Excel.
Для установки связи базы данных с внешней таблицей необходимо
выполнить команду меню Файл
Внешние данные
Связь с таблицами.
85
Запустится мастер установки связи, шаги которого аналогичны действиям
мастера импорта. В результате будет установлена динамическая связь с
данными из Excel, а в списке таблиц в окне базы данных Access появится новая
таблица, помеченная специальной пиктограммой в виде стрелочки, которая
обозначает, что данная таблица является связанной (рис. 7.5).
Рис. 7.5. Подтверждение связи с электронной таблицей
После установки связи данные, хранящиеся в связанной таблице, можно
обрабатывать как в Excel, так и в базе данных Access.
Если в дальнейшем файл — источник данных будет переименован или
перемещен в другую папку, то связь будет нарушена. Для восстановления связи
нужно будет указать новое местоположение исходного файла с помощью
команды меню Сервис
Служебные программы
Диспетчер связанных
таблиц.
7.3. Экспорт документов в HTML-формат
Чтобы быстро подготовить информацию из офисных программ для
публикации в Web, не нужно знать тэги языка HTML – достаточно сохранить ее
в формате гипертекстового документа. Для этой цели в Excel добавлена
специальная команда меню Файл Сохранить как Web-страницу.
После выполнения этой команды откроется окно «Сохранение документа», в
котором можно изменить заголовок документа (кнопка «Изменить»), а также
параметры HTML-документа (кнопка «Сервис» Параметры Web-документа)
(рис. 7.6). Затем нажмите кнопку «Сохранить», и вы получите готовую Webстраничку.
Рис. 7.6. Сохранение документа как веб-страница
При сохранении документа в формате HTML неизбежно утрачиваются
некоторые элементы форматирования, поэтому, прежде чем выполнять экспорт,
воспользуйтесь командой Файл
Предварительный просмотр Webдокумента для оценки результата.
86
Полученный в результате Web-документ можно будет разместить в сети
Internet и просматривать с помощью программ-браузеров.
Вставка таблицы в сообщение программы Microsoft Outlook
Если требуется отображать содержимое в сообщении, используя формат
строк и столбцов, можно вставить таблицу непосредственно в сообщение.
Необходимо использовать HTML в качестве формата сообщения и программу
Microsoft Word в качестве редактора электронной почты.
Вставка таблицы в сообщение
1. Если программа Microsoft Word не используется в качестве редактора
электронной почты, а HTML в качестве формата сообщения, настройки,
используемые по умолчанию, можно изменить.
2. В главном окне программы Microsoft Outlook в меню Сервис выберите
команду Параметры, а затем перейдите на вкладку Сообщение.
3. В списке поля Создать сообщение в формате выберите формат HTML.
4. Установите флажок Использовать Microsoft Word как редактор
сообщений.
5. Создайте новое сообщение.
6. Щелкните место, куда следует вставить таблицу, а затем в меню Таблица
укажите на команду Вставить и выберите подкоманду Таблица.
7. Укажите число столбцов и строк, режим изменения ширины столбцов и
другие параметры.
Можно также создать сложную таблицу (например, таблицу, содержащую
ячейки различной высоты или различное число столбцов в строке), нарисовав
ее.
Вопросы для самоконтроля
1. Что такое OLE технология?
2. Как может быть использован буфер обмена при работе с электронной
таблицей?
3. Как Excel и Access связаны между собой?
4. Как происходит экспорт электронной таблицы?
5. Как происходит экспорт документов в HTML-формат?
6. Опишите связь электронной таблицы с почтовой программой Microsoft
Outlook?
Раздел 8. Интернет - технологии в электронной таблице
8.1. Маршрутизация сообщения
Продукты электронных таблиц поддерживают функции электронного
документооборота, в частности маршрутизацию. Предположим, что требуется
создать шаблон сводки о деятельности фирмы за определенный период, после
87
чего этот шаблон должны по очереди заполнить представители всех отделов, а
затем его необходимо передать директору. При решении такой задачи можно
использовать средства Microsoft Excel.
План выполнения задачи включает следующие пункты: создание шаблона,
определение списка рассылки, отправка рабочей книги, содержащей шаблон, по
электронной почте. Допустим, что первый пункт плана вами уже выполнен.
Теперь необходимо задать список рассылки.
Маршрутизация сообщения: определение списка рассылки
Команда По маршруту предоставляет больше возможностей по сравнению с
командой Сообщение, т.к. позволяет задать последовательный маршрут.
Другими словами, если вы создадите список получателей, используя кнопку
Адрес(Address), и установите переключатель По очереди(One After Another),
книга будет отправлена первому получателю из списка. Когда этот член группы
ответит, книга автоматически будет передана следующему адресату из списка.
Список Кому (То) определяет последовательность рассылки, которую вы
можете изменить, выделив имя в списке и нажав одну из кнопок Порядок
(Моvе). В качестве альтернативы можно выбрать одновременную рассылку
книги всем получателям, установив переключатель Всем сразу (Аll At Once).
Чтобы отправить сообщение по назначенному маршруту, нажмите кнопку
Отправить (Route). Если вам нужно просто присоединить маршрутный лист к
книге, нажмите кнопку Добавить маршрут (Add Slip). При использовании
последнего способа вы можете продолжить работу с книгой. Когда вы будете
готовы послать сообщение, выберите команду Следующий адресат (Next
Routing Recipient) (которая заменяет собой команду Сообщение, если открыта
рассылаемая книга) в подчиненном меню Отправить.
8.2. Работа с файлами в сети
Сохранение и открытие файлов на удаленном компьютере
Используя раскрывающийся список Папка в окне диалога Сохранение
документа, вы можете сохранить книгу на любом доступном сетевом
устройстве, узле FTP или папке Web в Интернете.
Совместное использование книг в сети
Excel позволяет нескольким пользователям работать с одной и той же книгой
одновременно благодаря команде Доступ к книге меню Сервис.
Если на вкладке Правка установить флажок Разрешить совместный доступ и
нажать кнопку ОК откроется окно диалога Сохранение документа,
предлагающее сохранить книгу. Это действительно необходимо, т.к. книга в
этом случае должна быть сохранена как «общая» прежде, чем другой
пользователь сможет открыть ее. После такого сохранения в строке заголовка
окна будет выводиться в квадратных скобках слово [Общий] при всех
последующих, открытиях книги до тех пор, пока она снова не будет сохранена
при снятом флажке Разрешить совместный доступ.
Чтобы задать имя, которое будут видеть другие пользователи при работе с
общей книгой, в меню Сервис выберите команду Параметры и затем на вкладке
88
Общие открывшегося окна диалога в поле Имя пользователя введите имя.
Можно увидеть имена всех пользователей, открывших книгу на данный момент
времени, выбрав команду Доступ к книге и просмотрев список Файл открыт
следующими пользователями.
Если необходимо отключить, некоторого пользователя от общей книги в
текущем сеансе работы, выделите его в списке и нажмите кнопку Удалить. Но
этот пользователь может снова вернуться к работе с общей книгой в текущем
сеансе, просто открыв книгу еще раз.
При сохранении общей книги Excel проверяет все изменения и определяет,
нужно ли «посредничество» для разрешения конфликтов. В большинстве
случаев окно диалога, появляющееся после сохранения, просто информирует,
что изменения, сделанные другими пользователями, объединены с вашими
правками. Однако если чужие исправления касаются тех же самых ячеек,
которые изменяли вы, появляется «посредник» в виде окна диалога Возник
конфликт доступа.
Для каждого обнаруженного конфликта в окне диалога Возник конфликт
доступа указываются ячейки, измененные двумя или несколькими
пользователями, и, необходимо решить, чьи правки оставить. Можно
разрешить конфликты индивидуально, либо, нажав соответствующую кнопку
внизу окна диалога, отдать предпочтение чужим исправлениям или оставить
только свои изменения.
Также для более корректной работы с книгами, к которым разрешен общий
доступ, появляется возможность изменять некоторые аспекты поведения общих
книг. Для этого в меню Сервис выберите команду Доступ к книге и затем в
окне диалога Управление доступом к файлу перейдите на вкладку Подробнее.
Каждый пользователь общей книги может устанавливать свои параметры
режима совместного доступа.
Просмотр изменений
Можно в любое время просмотреть все изменения, сделанные в общей книге,
при условии, что при первом сохранении книги для общего пользования в окне
диалога Исправления был установлен флажок Отслеживать исправления (книга
становится общей). Если в меню Сервис выбрать команду Исправления и затем
— Принять/отклонить исправления, то откроется окно диалога Просмотр
исправлений.
После нажатия кнопки OK появится окно диалога в рабочем листе. Будет
выделено первое изменение, удовлетворяющее условиям, заданным в окне
диалога Просмотр исправлений. Это окно диалога описывает изменение, а
также показывает, кем и когда это изменение было сделано. Можно принять
или отвергнуть текущее изменение, либо принять или отвергнуть сразу все
изменения. Принятые или отвергнутые изменения исключаются из списка и не
могут быть просмотрены снова, но можно просмотреть журнал изменений.
Собрания и обсуждения по сети
Подчиненное меню Совместная работа в меню Сервис содержит команды,
обеспечивающие совместную работу с информацией по сети. Это команды
89
Начать собрание, Назначить собрание и Web-обсуждения, и всем им для своей
работы требуются дополнительные подключения и интерактивные службы.
Чтобы начать собрание по сети в меню Сервис, выберите команду
Совместная работа и затем — Начать собрание. Excel выведет на экран окно
диалога Microsoft NetMeeting. Затем необходимо задать сервер каталогов,
предоставляющий список адресов электронной почты, с которым можно
соединиться, узнав имя локального сервера каталогов или общедоступные
серверы каталогов в Интернете.
Команда Назначить собрание позволяет заранее запланировать собрание с
несколькими участниками, используя для этого соответствующие средства
Microsoft Outlook. При этом это приложение должно быть установлено в
системе основного пользователя, а также на компьютерах членов рабочей
группы.
После подсоединения можно проводить собрание по сети в реальном
времени. Кроме того, можно использовать встроенные инструменты
NetMeeting, позволяющие создавать эскизы и транслировать их на «доски» всех
участников собрания. Проводить собрание можно как из Excel, так и из
Microsoft NetMeeting. Web-обсуждения подобны другим интерактивным
«разговорным» системам с тем отличием, что комментарии каждого участника
отображаются в специальном окне в Excel, браузере или другом приложении
Microsoft Office.
О порядке заполнения полей этого окна мы расскажем ниже, а пока отметим особенности)
Вопросы для самоконтроля
1.
2.
3.
4.
5.
6.
Что такое интернет-технологии применительно к электронным таблицам?
Как отправить документ по определенному списку рассылки?
Что такое список рассылки?
Работа с файлами в сети?
Что такое собрание в сети?
Удаленный доступ к электронной таблице?
90
Глоссарий
VBA (Visual Basic for Applications) - это объектно-ориентированный язык
макропрограммирования высокого уровня, встроенный во все программы
пакета Microsoft Office.
Аргумент функции – значения в функции: числа, текст, логические
значения, массивы, значения ошибок или ссылки, необходимые для получения
искомого результата.
База данных в электронной таблице – это список связанных данных, в
котором строки данных являются записями, а столбцы полями
Ветвлением называется управляющая конструкция, позволяющая
пропускать при выполнении те или иные группы инструкций в зависимости от
значения условия.
Вложенные функции - функции, являющиеся аргументом другой функции.
Внедренная диаграмма – это диаграмма, расположенная на листе рядом с
таблицей и сохраняемая вместе с листом при сохранении книги.
Изменяемые ячейки – ячейки, от которых зависит значение целевой
ячейки.
Консолидация данных – процедура получения итогов для данных в
различных частях таблицы.
Легенда - прямоугольник, в котором указывается каким цветом или типом
линий выводятся на графике или диаграмме данные из той или иной строки.
Лист диаграммы – это лист книги, содержащий только диаграмму.
Макрорекордер - наиболее простой метод создания макрокоманды, при
котором протоколируются все выполняемые пользователем действия и
представляется в виде программного кода VBA.
Макрос (или макрокоманда) - последовательность команд и функций,
записанных в модуле VBA, позволяющая автоматизировать выполнение
основных операций.
Маркер – значение одной ячейки на диаграмме.
Метки - заголовки строк и столбцов таблицы на диаграмме.
Модуль – это часть программы, оформленная в таком виде, при котором
допускается ее независимая трансляция.
Область видимости переменной – это область программ, где имя
переменной считается допустимым (видимым), а, следовательно, возможен
доступ к ее значению.
Переменная – это поименованная область памяти, используемая для
хранения данных в течение работы процедуры
Подбора параметра – средство оптимизации, которое позволяет определить
значение, которое следует ввести в одну ячейку, чтобы получить требуемое
значение в другой.
Поиска решения – средство, которое позволяет определить, при каких
значениях указанных влияющих ячеек формула в целевой ячейке принимает
91
нужное значение (минимальное, максимальное или равное какой-либо
величине).
Процедурой называется фрагмент кода (минимальная семантически
законченная программная конструкция), заключенный между операторами Sub
и End Sub.
Редактирование – изменение данных таблицы
Режим конструктора переводит Excel в режим отключения всех элементов
управления на рабочем листе.
Сводная таблица – динамическая таблица итоговых данных, извлеченных
или рассчитанных на основе информации, содержащейся в списках.
Ссылка (адрес) – местоположение ячейки или группы ячеек, используемых
в формуле.
Ссылка внешняя - ссылка на ячейки, находящиеся на других листах книги,
в другой книге.
Ссылка удаленная - ссылка на ячейки, находящиеся в другом приложении.
Структурирование - распределение данных по уровням иерархической
структуры.
Сходимость – значение относительного изменения, при достижении
которого в последних пяти итерациях поиск решения прекращается.
Сценарий - набор сохраненных электронной таблицей значений, которые
она может автоматически подставить в ячейки рабочего листа.
Управляющие конструкции языка программирования – это инструкции
и группы инструкций, применение которых позволяет изменять по мере
необходимости последовательность выполнения других инструкций программы
Форматирование – преобразование внешнего вида таблицы без изменения
содержащихся в ней данных.
Формула - выражение, которое обязательно начинается знаком равенства и
определяет, какие расчеты нужно произвести на рабочем листе.
Функция – стандартная формула, прописанная в таблице и используемая
для простых или сложных вычислений.
Целевая ячейка – ячейка, для которой нужно найти заданное значение.
Цикл – это управляющая конструкция, представляющая возможность
многократно выполнять группы инструкций до наступления какого-либо
события.
Электронная таблица (табличный процессор) - программы обработки
крупноформатных электронных динамических таблиц.
Элемент управления - размещаемые на рабочих листах и в диалоговых
окнах объекты, предназначенные для отображения, ввода и вычисления
данных.
Ячейка – минимальный элемент хранения данных в электронной таблице.
По умолчанию в случае отсутствия в ней данных установлено значение, равное
нулю.
Ячейка ввода - ячейки с формулой, содержащая ссылку на ячейку,
определенную как поле ввода.
92
Документ
Категория
Без категории
Просмотров
3
Размер файла
3 169 Кб
Теги
konspekt, tehnologii, lekcii, elektron, tablicy
1/--страниц
Пожаловаться на содержимое документа