close

Вход

Забыли?

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

?

8860.1856.Технологии электронных таблиц

код для вставкиСкачать
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Федеральное агентство связи
Федеральное государственное образовательное бюджетное учреждение
высшего профессионального образования
ПОВОЛЖСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ
ТЕЛЕКОММУНИКАЦИЙ И ИНФОРМАТИКИ
ЭЛЕКТРОННАЯ
БИБЛИОТЕЧНАЯ СИСТЕМА
Самара
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Федеральное агентство связи
Федеральное государственное образовательное бюджетное учреждение
высшего профессионального образования
«Поволжский государственный университет телекоммуникаций и
информатики»
___________________________________________________
Кафедра информационных систем и технологий
А. А. Салмин
ТЕХНОЛОГИИ ЭЛЕКТРОННЫХ ТАБЛИЦ
УЧЕБНОЕ ПОСОБИЕ
Самара – 2013
2
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
УДК 004.67
Салмин А.А.
Технологии электронных таблиц. Учебное пособие. – Самара.: ФГОБУ ВПО
«ПГУТИ», 2013. – 162 с.
Рассматриваются основные элементы работы в электронной таблице на
примере MS Excel 2003. Приводятся как базовые возможности работы с
табличными данными, такие как: редактирование, форматирование данных,
работа с формулами, так и функции, выполняющие сложные расчеты или
решающие различные оптимизационные задачи. Также рассматриваются
базовые элементы среды разработки программ, основанные на конструкциях
алгоритмического языка Visual Basic for Application.
Рецензент:
Тарасов В.Н. – д.т.н., профессор, зав. кафедрой «Программного обеспечения
и управления в технических системах» ПГУТИ
Федеральное государственное образовательное бюджетное учреждение
высшего профессионального образования
«Поволжский государственный университет телекоммуникаций и
информатики»
© Салмин А.А., 2013
3
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Содержание
ВВЕДЕНИЕ
1. ОСНОВНЫЕ ПОНЯТИЯ ЭЛЕКТРОННЫХ
ТАБЛИЦ
1.1. Технологии работы в электронной таблице
1.2. Формирование структуры таблицы
1.3. Ввод, редактирование и форматирование данных
2. ФОРМУЛЫ И ФУНКЦИИ
2.1. Ввод и редактирование формул
2.2. Использование функций
2.3. Использование ссылок и имен
3. РАБОТА СО СПИСКАМИ И БАЗАМИ ДАННЫХ
В ЭЛЕКТРОННОЙ ТАБЛИЦЕ
3.1. Построение графиков и диаграмм
3.2. Сводные таблицы
3.3. Работа со списками и базами данных
3.3.1. Формирование списка
3.3.2. Сортировка записей
3.3.3. Фильтрация записей
3.3.4. Импорт данных с помощью Microsoft Query
4. АНАЛИЗ ДАННЫХ
4.1. Подбор параметра
4.2. Таблицы подстановки
4.3. Сценарии
4.4. Консолидация данных
4.5. Поиск решения
5. РАБОТА С МАКРОСАМИ
5.1. Макросы
5.1.1. Макрорекордер
5.1.2. Панель инструментов для работы
с макросами
5.1.3. Редактирование макроса
5.2. Элементы управления на рабочем листе
5.2.1. Элементы управления ActiveX
5.2.2. Элементы управления панели инструментов Формы
5.2.3. Создание выпадающего списка при помощи меню Проверка
6. ОСНОВЫ VBA (VISUAL BASIC FOR
APPLICATION)
6.1. Объекты, методы, свойства
6.2. Структура, используемая в VBA
6.3. Типы данных, используемые в VBA
6.3.1. Переменные
6.3.2. Константы
6
8
10
11
15
17
20
223
26
30
39
40
443
44
45
50
55
62
63
65
4
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
6.4. Использование стандартных окон операционной системы Windows
6.4.1. Функция MsgBox
6.4.2. Функция InputBox
6.4.3. Совместное использование функций
MsgBox и InputBox
6.5. Управляющие конструкции VBA
6.5.1. Ветвление
6.5.2. Циклы
6.6. Использование элементов управления для запуска макроса и ввода
данных
6.7. Пользовательские формы, создаваемые в VBA
7. ОБМЕН ДАННЫМИ В ЭЛЕКТРОННОЙ ТАБЛИЦЕ
7.1. Использование буфера обмена
7.2. Связь Excel c Access
7.3. Экспорт документов в HTML-формат
8. ИНТЕРНЕТ - ТЕХНОЛОГИИ В ЭЛЕКТРОННОЙ ТАБЛИЦЕ
8.1. Маршрутизация сообщения
8.2. Работа с файлами в сети
ЗАДАНИЯ НА ЗАКРЕПЛЕНИЕ ЗНАНИЙ ПО ТЕХНОЛОГИЯМ
ЭЛЕКТРОННЫХ ТАБЛИЦ
Задание 1. Форматирование таблицы, ввод и редактирование данных,
применение простых формул в Microsoft Excel
Задание 2. Работа со списками и базами данных
Задание 3. Анализ данных средствами Microsoft Excel
Задание 4. Создание элементов управления на рабочем листе. Работа с
макросами
Задание 5. Основы программирования на языке Visual Basic
Задание 6. Обмен данными и интернет - технологии в ЭТ
68
72
76
80
87
87
90
92
92
95
5
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
ВВЕДЕНИЕ
Предлагаемое учебное пособие по дисциплине «Технологии электронных
таблиц» обеспечивает подготовку студентов к эффективному использованию
современных компьютерных средств обработки табличных данных.
Предлагаются основные темы, посвященные работе с табличными
процессорами на примере MS Excel, после изучения которых студенты смогут
работать с электронными таблицами, строить диаграммы, графики,
анализировать информацию с помощью встроенных в Excel функций, создавать
макрокоманды, пользовательские формы и др. В рамках конспекта лекций по
дисциплине рассматриваются теоретические основы работы с табличными
процессорами, а также практические вопросы создания и работа с данными не
только со стандартными функциями, созданных средствами приложения, но и
использование комплексных возможностей для решения сложных задач
оптимизации и макропрограммирования. Таким образом, у будущих
специалистов осуществляется формирование основ теоретических знаний и
практических навыков работы в области создания, функционирования и
использования электронных таблиц.
Дисциплина «Электронные таблицы» базируется на знании предмета
«Основы информатики и информационных технологий», изучаемого в
образовательных учреждениях среднего (полного) образования. Также данная
дисциплина требует предварительного изучения курсов «Информатика»,
«Информационные технологии», изучаемых в образовательных учреждениях
высшего образования. В связи с этим в курсе опускается освещение таких
вопросов как: открытие и сохранение документа, использование справки и
другие вопросы, которые должны быть освящены в рамках изученного
материала.
Элементы курса «Технологии электронных таблиц» используются при
изучении курсов «Моделирование систем», «Статистический и регрессионный
анализ».
Задача материала данного конспекта лекций в том, чтобы:
- предоставить студентам общие сведения о принципах построения и
функционирования электронных таблиц данных;
- показать методы, средства и технологии электронных таблиц: ввода и
обработки данных; решения индивидуальных задач и задач оптимизации
при помощи табличных процессоров;
- показать разнообразие возможностей, имеющихся в табличных
процессорах, для решения разнообразных задач.
Знания и навыки, полученные в результате изучения данной дисциплины,
могут быть применены:
1. при самостоятельном создании информационных систем с использованием
технологии электронных таблиц для выполнения своих профессиональных
функций;
6
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
2. для создания документов различной формы на основе приложений
электронных таблиц;
3. при формулировании технического задания при создании ИС силами
профессиональных разработчиков.
7
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
1. ОСНОВНЫЕ ПОНЯТИЯ ЭЛЕКТРОННЫХ ТАБЛИЦ
1.1. Технологии работы в электронной таблице
К категории табличных процессоров относят интерактивные компьютерные
программы, предназначенные для создания и обработки документов в виде
таблиц данных.
Электронными
таблицами
называют
программы
обработки
крупноформатных электронных динамических таблиц.
Достоинство табличных процессоров:
упрощение выполнения финансовых, научных и других видов расчета;
возможность ввода простых баз данных для учета материалов, товаров,
денег, времени и т.д.;
возможность оформления всех видов документов, а также возможность
быстрого анализа данных и представление их в графическом виде.
Основу программы составляют: вычислительно – калькуляционный модуль,
модуль диаграмм, доступ к внешним базам данных, модуль программирования
индивидуальных задач. Каждый из этих модулей отвечает за определенные
действия, проводимые над данными. Так вычислительно-калькуляционный
модуль отвечает за все вычислительные действия, производимые в
приложении, такие как: расчеты, связь данных, использование стандартных
функций и мн.др. Модуль диаграмм отвечает за построение различных типов
гистограмм, графиков и их связь с исходными данными. Модуль доступа к
внешним базам данных позволяет связать внешние базы данных с приложением
электронных таблиц, импортировать данные, добавлять поля по средствам
устанавливаемым связей, а также производить экспорт в другие приложения.
Модуль программирования индивидуальных задач, в отличие от трех
предыдущих, решает пользовательскую задачу на уровне программирования,
что позволяет рассматривать электронные таблицы как средство,
автоматизирующее
выполнение каких-либо действий, представленных в
программном коде.
На рисунке 1.1 представлена обобщенная схема технологии работы в
электронной таблице.
8
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
I
Формирование структуры
электронной таблицы
Ввод заголовка и шапки таблицы
Ввод исходных данных
II
Ввод формул
III
Представление
входных
данных в
графическом
виде
Работа с данными
II
I
1. Моделирование
2. Работа с базами
данных
II
IV
I
Печать
3. Одновременная работа с
несколькими таблицами
Рис.1.1. Технология работы в электронной таблице
На первом этапе происходит формирование структуры электронной таблицы,
на выходе которого образуется таблица с исходными данными и формулами,
позволяющими производить математические действия над значениями, а также
получить общую статистику изменения данных. Отличительной особенностью
первого этапа является тот факт, что все действия в нем должны производиться
в строгой последовательности. Так, например, без ввода исходных данных нет
смысла в воде формул.
На втором этапе производится работа с данными, под которой
подразумевается моделирование, работа с базами данных, решение
оптимизационных и индивидуальных задач пользователя. Выделенные на
данном этапе математические модели помогают пользователю на основе
имеющейся таблицы получить новую информацию при решении типовых задач
компьютерного моделирования, таких как:
Что будет, если …?
Как сделать, чтобы …?
Анализ чувствительности
Статистический анализ и прогноз
Анализ с помощью встроенных функций
Оптимизационные задачи.
Решение задачи «Что будет, если…?» дает возможность узнать, как
изменяются выходные параметры при изменении одной или нескольких
входных величин или условий.
9
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Задачи «как сделать, чтобы…?» (обратная «что будет, если…?») возникает в
случае, если цель состоит в достижение определенных значений модели и
определении значений входных параметров, обеспечивающих достижение этой
цели.
Задачи анализа чувствительности позволяют определить, как изменяется
решении при изменении одной или нескольких входных величин с заданным
шагом изменения в определенном диапазоне значений.
Статистический анализ и прогноз применим для предсказания и оценки
будущих значений.
Различные виды анализов данных, содержащиеся в исходной таблице, на
данном этапе можно проводить с использованием встроенных функций и
процедур. Кроме того, можно отметить, что в отличие от предыдущего этапа,
на данном этапе не соблюдается последовательность решения задачи. Так
может быть реализован только один из подэтапов или группа, состоящая из
двух или всех трех.
Третий этап позволяет представить данные числового типа графически с
помощью различных диаграмм, причем данные для построения графиков могут
быть взяты как из первого, так и из второго этапа. Кроме того, при изменении
исходных данных автоматически изменяются изображение элементов диаграмм
по размеру или месту положения. Существует 2 вида диаграмм:
1) внедренные диаграммы (сохраненные на рабочем листе вместе с
числовыми данными);
2) диаграммы-листы (размещены на отдельных листах).
Четвертый этап работы в электронной таблице является заключительным и
отвечает за сохранения документа в виде, удобном для пользователя:
электроном или бумажном.
1.2. Формирование структуры таблицы
Программы электронных таблиц (например, Microsoft Excel, Calc)
предназначены для работы с таблицами данных,
содержащими
преимущественно числовую информацию. При формировании таблицы
выполняют ввод, редактирование и форматирование текстовых и числовых
данных, а также формул. В дальнейшем в качестве примера будет использовано
одно из самых распространенных приложений электронных таблиц Microsoft
Excel 2003.
Документ электронной таблицы называется рабочей книгой, которая
представляет собой набор рабочих листов, каждый из которых имеет
табличную структуру.
Рабочий лист состоит из строк и столбцов. Столбцы озаглавлены
прописными латинскими буквами и, далее, двухбуквенными комбинациями.
Всего рабочий лист может содержать до 256 столбцов, пронумерованных от A
до IV. Строки последовательно нумеруются цифрами, от 1 до 65536.
На пересечении строк и столбцов образуются ячейки таблицы, которые
являются минимальными элементами для хранения данных. Отдельная ячейка
10
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
может содержать данные, относящиеся к одному из трех типов текст, число или
формула, а также оставаться пустой.
На рисунке 1.2 представлена структура ячейки электронной таблицы.
Рис. 1.2. Структура ячейки
Структура ячейки:
1-й уровень содержит видимое на экране изображение (т.е.
отформатированный текст) или результат вычисления формулы).
2-й уровень содержит форматы ячейки (формат чисел, шрифты, выключатель
(включатель) признак показывать или нет ячейку, вид рамки, защита ячейки).
3-й уровень содержит формулу, которая может состоять из текста, числа или
встроенных функций.
4-й уровень содержит имя ячейки, это имя может использоваться в формулах
других ячеек, при этом обеспечивается абсолютная адресация данной ячейки.
5-й уровень содержит примечания данной ячейки (произвольный текст). Если
ячейка содержит примечание, то в правом верхнем углу появляется красный
квадратик (точка).
Каждая ячейка имеет адрес - обозначается именем столбца и именем строки.
Например, А2. Для работы с несколькими ячейками сразу необходимо
выделить блок ячеек.
1.3. Ввод, редактирование и форматирование данных
Ввод данных осуществляется непосредственно в текущую ячейку или в
строку формул.
Существует три типа данных: текст, число, дата и время.
ТЕКСТОМ является любая последовательность, состоящая из цифр,
пробелов и нецифровых символов. Веденный текст автоматически
выравнивается в ячейке по левому краю.
ЧИСЛА, введенные в ячейку, интерпретируются как константы. Все другие
сочетания клавиш, а исключением «+», «-», «,», состоящие из цифр и
нецифровых символов, рассматриваются как текст. Веденный текст
автоматически выравнивается в ячейке по правому краю.
В ячейки таблицы можно вводить даты и время суток, которые
интерпретируются как число. Для отображения времени суток в 12-часовом
формате следует вводить букву а или р, отделенную пробелом от значения
времени, пример 9:00 р. Иначе время будет интерпретировано в 24-часовом
формате. Для определения текущей даты используется функция СЕГОДНЯ().
Для ввода даты используется функция ДАТА(год, месяц, дата).
11
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Вычисления в таблицах осуществляется при помощи формул, которые могут
содержать числовые константы, ссылки на ячейки и функции, соединенные
знаками математических операций.
Для выбора формата данных ячейки используется диалоговое окно Формат
ячеек, вызываемое с помощью команды Формат – Ячейки (или Ctrl+1) (рис.1.3).
Рис. 1.3. Диалоговые окна изменения формата ячейки
Помимо стандартного приема форматирования, Excel позволяет производить
форматирование ячейки с учетом хранящейся в ней данных. Такое
форматирование называется условным, так как выполняется на основании
результатов проверки определенных условий. В условии могут проверяться как
числовые данные, так и текстовые. Для выполнения условного форматирования
необходимо: Формат – Условное форматирование.
Рис. 1.4. Условное форматирование данных
При работе с большими таблицами появляется необходимость в скрытии
некоторых столбцов или строк. Для этого используется команда меню Формат
– Строка (Столбец) – Скрыть. Если нужно запретить отображение всего листа:
Формат – Лист – Скрыть. Для восстановления отображения строки или столбца
нужно выделить две смежные ячейки соседних строк или столбцов и выбрать
команду Формат – Строка (Столбец) – Отобразить.
Кроме того, возникает необходимость использовать иерархическую
структуру таблицы.
Под термином «структурирования» подразумевается распределение данных
по уровням иерархической структуры. Путем отмены (восстановления)
отображения отдельных уровней можно добиться представления на экране
только необходимой информации. Структурирование позволяет значительно
упростить работу с содержащимися в таблице данными.
12
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Разбивка на уровни структуры может выполняться как по горизонтали, так и
по вертикали. Существует автоматическое структурирование и группирование
данных.
При автоматическом структурировании уровни структуры таблицы должны
быть «заложены» изначально при построении таблицы (например, исходные
данные (один уровень) и результаты суммирования (второй уровень)). Для
автоматического структурирования таблицы необходимо: разместить указатель
ячейки в области таблицы, а затем произвести: команда Данные – Группа и
структура – Создание структуры.
Для удаления структуры вызвать команду Данные – Группа и структуры –
Удалить структуру.
Кроме автоматического структурирования представляется возможность
самостоятельно группировать данные по уровням структуры, вставлять и
удалять отдельные уровни: Данные – Группа и структура – Группировать.
Пример иерархического представления данных представлено на рисунке 1.5.
Рис. 1.5. Пример структурирования
Для более удобного представления информации, а также ее использования
другими пользователями к содержимому ячеек можно добавить
дополнительные пояснения. Чтобы не нарушить структуру таблицы,
дополнительную информацию следует представлять в виде примечаний. Для
этого необходимо: выделить ячейку – Вставка – Примечание.
Рис. 1.6. Пример примечания
13
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Существует три режима отображения примечаний: только индикатор,
примечание и индикатор и не отображать. Эти режимы устанавливаются на
вкладке Вид диалогового окна Параметры.
Вопросы для самоконтроля
Дайте определение электронной таблице.
Какие модули используется в электронной таблице?
Перечислите основные элементы технологии хранения информации.
Что является минимальным элементом хранения информации в электронной
таблице?
5. Что подразумевается под текстом в электронной таблице?
6. Что подразумевается под структурированием?
7. Для чего используется примечание?
1.
2.
3.
4.
ГЛОССАРИЙ
Электронная таблица (табличный процессор) - программы обработки
крупноформатных электронных динамических таблиц.
Форматирование – преобразование внешнего вида таблицы без изменения
содержащихся в ней данных.
Редактирование – изменение данных таблицы
Ячейка – минимальный элемент хранения данных в электронной таблице. По
умолчанию в случае отсутствия в ней данных установлено значение, равное
нулю.
Структурирование - распределение данных по уровням иерархической
структуры.
14
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
2. ФОРМУЛЫ И ФУНКЦИИ
Основным средством анализа и обработки, вводимых в таблицу данных,
являются формулы. С их помощью можно складывать, умножать и сравнивать,
производить над ними другие операции. Для выполнения стандартных
вычислений в электронной таблице предлагают большое количество
встроенных функций, которые можно вызывать в формулах.
Помимо чисто вычислительных действий с отдельными числами, в
электронных таблицах есть возможность обрабатывать отдельные строки или
столбцы таблицы, а также целые блоки ячеек. В частности, находить среднее
арифметическое, максимальное и минимальное значение, средне-квадратичное
отклонение, наиболее вероятное значение, доверительный интервал и многое
другое.
Для удобства работы функции разбиты по категориям: функции управления
базами данных и списками, функции даты и времени, DDE/Внешние функции,
инженерные функции, финансовые, информационные, логические, функции
просмотра и ссылок. Кроме того, присутствуют следующие категории функций:
статистические, текстовые и математические. При помощи текстовых функций
обрабатывается текст: извлекаются символы, находятся нужные, записываются
символы в строго определенное место текста и многое другое. С помощью
функций даты и времени можно решить практически любые задачи, связанные
с учетом даты или времени (например, определить возраст, вычислить стаж
работы, определить число рабочих дней на любом промежутке времени).
Логические функции позволяют создавать сложные формулы, которые в
зависимости от выполнения тех или иных условий будут совершать различные
виды обработки данных. Также широко представлены математические
функции. Например, можно выполнить различные операции с матрицами:
умножать, находить обратную, транспонировать.
В распоряжении пользователя находится библиотека статистических
функций, при помощи которой можно проводить статистическое
моделирование, в том числе и при помощи факторного и регрессионного
анализа.
2.1. Ввод и редактирование формул
Под формулой понимается выражение, которое обязательно начинается
знаком равенства и определяет, какие расчеты нужно произвести на рабочем
листе. Результатом работы формулы является новое значение, которое
выводится как результат вычисления формулы по уже имеющимся данным.
Если значения в ячейках, на которые есть ссылки в формулах, меняются, то
результат изменится автоматически.
Формула в ячейке может включать следующие элементы: числовые и
текстовые значения, ссылки на другие ячейки, знаки математических и
15
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
логических операций, а также обращения к функциям. При вычислении
формулы используется порядок действий, принятый в математике.
Операторами обозначаются операции, которые следует выполнять над
операндами формулы. Используются 4 вида операторов: арифметические,
текстовые, адресные и операторы сравнения.
Арифметические операторы используются для выполнения основных
математических вычислений над числами. Используемые операции: + – * /
%.
Операторы сравнения используются для обозначения операций сравнения
двух чисел. Результатом выполнения операций сравнения является логическое
значение ИСТИНА или ЛОЖЬ.
Текстовые операторы используются при работе с текстом. Например, для
обозначения операций объединения последовательностей символов в единую
последовательность используется текстовый оператор « » (амперсант).
Пример: «Северный» «ветер» результат «Северный ветер».
Рис. 2.1. Пример использования текстового оператора
Адресные операторы объединяют диапазоны ячеек для осуществления
вычислений. В таблице 2.1 представлены адресные операторы.
Таблица 2.1. Адресные операторы
Пример
Адресный
оператор
Значение
:
(двоеточие)
Оператор
диапазона,
ссылающийся на ячейки между
границами
диапазона
включительно
Оператор объединения, который
ссылается на объединение ячеек
диапазонов
Оператор пересечения, который
ссылается на общие ячейки
диапазонов
,
(запятая)
(пробел)
В5:В15
СУММ(В5:В15,D5:D15)
СУММ(В5:В15 А7:D7)
Ячейка
В7
является
общей
для
двух
диапазонов
16
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
2.2. Использование функций
В формулах в качестве операндов могут выступать не только данные и
адреса или имена ячеек, но и функции. Функции могут быть встроенными или
определяться пользователем.
В электронных таблицах содержится большое количество стандартных
формул, называемых функциями. Функции используются для простых или
сложных вычислений. Для использования функций в формуле нужно указать ее
имя и аргументы, отделяясь друг от друга точкой с запятой. Максимальное
количество аргументов функции не должно превышать 30, а длина формулы не
может быть более 1024 символов.
В качестве аргумента можно задать числовое или текстовое значение, адрес
ячейки, адрес или имя диапазона. В одной формуле может быть несколько
функций, объединенных различными символами операций. На формулы с
функциями не накладываются никакие ограничения. Допускается
использование в качестве аргументов ссылок на диапазоны из других листов и
книг.
Чтобы использовать функцию, нужно ввести ее как часть формулы в ячейку
рабочего листа. Последовательность, в которой должны располагаться
используемые в формуле символы, называется синтаксисом функции. Все
функции используют одинаковые основные правила синтаксиса. Если
нарушаются правила синтаксиса, выдается сообщение о том, что в формуле
имеется ошибка.
Если функция появляется в самом начале формулы, ей должен
предшествовать знак равенства, как и во всякой другой формуле.
Аргументы функции записываются в круглых скобках сразу за названием
функции и отделяются друг от друга символом точка с запятой ―;‖. Скобки
позволяют определить, где начинается и где заканчивается список аргументов.
Внутри скобок должны располагаться аргументы. Помните о том, что при
записи функции должны присутствовать открывающая и закрывающая скобки,
при этом не следует вставлять пробелы между названием функции и скобками.
В качестве аргументов можно использовать числа, текст, логические
значения, массивы, значения ошибок или ссылки. Аргументы могут быть как
константами, так и формулами. В свою очередь эти формулы могут содержать
другие функции. Функции, являющиеся аргументом другой функции,
называются вложенными. В формулах можно использовать до семи уровней
вложенности
функций.
Пример
вложенной
функции:
=ЕСЛИ(ИЛИ(L8>=4;СРЗНАЧ(L6:L9)>=3,5);"Тест пройден";"Тест не пройден")
Задаваемые входные параметры должны иметь допустимые для данного
аргумента значения. Некоторые функции могут иметь необязательные
аргументы, которые могут отсутствовать при вычислении значения функции.
В таблице 2.2 представлены стандартные функции электронной таблицы на
примере MS Excel.
17
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Таблица 2.2. Стандартные функции
Категория
Назначение функций
Финансовые
Вычисляют
процентные
ставки,
ежемесячные
отчисления, амортизационные отчисления
Дата и время
Возвращают в различных форматах день недели, время и
дату
Ссылки и массивы Вычисляют и возвращают значения из диапазона;
создают гиперссылки для веб-документов
Статистические
Вычисляют средние значения, наибольшее или
наименьшее числа в диапазоне, тестируют на предмет
независимости выборок
Математические
Определяют абсолютные величины, косинусы и
логарифмы
Работа с базой Выполняют
различного
рода
анализ
данных,
данных
находящихся в списках или базах данных
Текстовые
Преобразуют регистр текста, обрезают символы с
правого или левого края текстовой строки, объединяют
текстовые строки
Логические
Вычисляют выражения и возвращают значения
ИСТИНА или ЛОЖЬ, которые используются при
выполнении другого действия либо форматирования
Проверка свойств Возвращают в Windows информацию о текущем статусе
и значений
ячейки, объекта или среды
Инженерные
Выполняют операции с комплексными переменными,
преобразования из одной системы счисления в другую и
т.д
В таблице 2.3 представлены типы ошибок, возникаемые при работе с
функциями и меры по их устранению.
Таблица 2.3. Типы ошибок
Обозначен
ие ошибки
######
В каком случае появляется
Ширина столбца недостаточна
для размещения результатов
вычисления формулы и когда
полученные значения даты или
времени являются
отрицательными числами
#ССЫЛКА! Формула содержит ссылку на
несуществующие ячейки
#Дел/0
Деление на 0 (возможно
осуществляется деление на
пустую ячейку)
#Число!
Нарушение правил, принятых
Меры по устранению
Необходимо
увеличить
ширину ячейки или изменить
числовой формат
Необходимо
проверить
правильность ссылки
Проверить
содержимое
влияющей ячейки. Если ячейка
пуста, введите значение
Уточнить значение аргументов
18
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
#ИМЯ?
#ПУСТО!
#Н/Д
#ЗНАЧ!
в
математике,
или
некорректное
определение
аргументов функции, значение
которых может выходить за
допустимые пределы Excel
При вводе имени допущена
ошибка, и программа не может
найти нужное имя ни среди
функций, ни среди имен
диапазонов
Неверное
указание
пересечения диапазонов
Не задан один или несколько
аргументов
функции
или
происходит
обращение
к
недоступной пользовательской
функции.
Ошибку
могут
генерировать
макросы,
вызывающие функцию
Ввод аргумента или операнда
недопустимого типа
Проверить
правильность
написания имени функции или
ввести функцию заново
Проверить
правильность
указания диапазонов
Проверить содержимое ячеек,
также
пользовательские
функции и макросы
Проверить допустимость типа
операнда и аргумента
Для поиска ошибок предоставляется в распоряжение пользователей
вспомогательная функция, с помощью которой можно графически представить
связи между влияющими и зависимыми ячейками. Влияющими называют
ячейки, данные которых оказывают влияние на значение текущей ячейки.
Зависимой является ячейка с формулой, результат вычисления которой зависит
от данных, находящихся в других ячейках.
Команды для отслеживания таких зависимостей вызываются из подменю
Зависимости формул меню Сервис, а также с помощью панели инструментов
Зависимости. На рисунке 2.2 представлен пример отслеживания зависимостей.
Рис. 2.2. Пример отслеживания зависимостей в данных
19
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
2.3. Использование ссылок и имен
Ссылкой (адресом) определяется ячейка или группа ячеек, используемых в
формуле. С помощью ссылок можно использовать значение одной и той же
ячейки в нескольких формулах, также можно ссылаться на ячейки,
находящиеся на других листах книги, в другой книге (внешние ссылки) или в
другом приложении (удаленные ссылки).
Для задания адреса ячейки можно использовать два режима адресации: в
формате А1 (используется по умолчанию) или R1C1 (строка, столбец)
(Сервис – Параметры - вкладка Общие – Стиль ссылок).
Адресация может быть абсолютной, относительной и смешанной.
При копировании формулы, содержащие относительные ссылки, и вставке ее
в другое место, ссылки будут меняться, настраиваясь на новое местоположение
(рис. 2.3 (а)).
Абсолютные ссылки задают фиксированную позицию на рабочем листе
(рис. 2.3 (б)) и не изменяются при копировании. Признаком абсолютной ссылки
является наличие двух знаков доллара ($) – перед именем столбца и перед
номером строки. Смешанные ссылки фиксируют либо имя столбца, либо номер
строки (имеется один знак доллара).
Рис. 2.3. Пример адресации:
а) относительная, б) абсолютная
В электронных таблицах существует возможность присваивать ячейкам,
диапазонам ячеек, формулам или константам имена и использовать их в
качестве абсолютных ссылок. Имя может содержать до 255 знаков и состоять
из букв, цифр, символов точки и подчеркивания. Начинаться оно должно с
буквы или со знака подчеркивания. Использование имен обеспечивает
следующие преимущества:
Формулы, использующие имена, легче воспринимаются и запоминаются,
чем формулы, использующие ссылки на ячейки. Например, формула
“=Активы-Пассивы” гораздо понятнее, чем формула “=F6-D6”.
При изменении структуры рабочего листа достаточно обновить ссылки лишь
в одном месте – в определении имен, и все формулы, использующие эти
имена, будут использовать корректные ссылки.
20
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
После того как имя определено, оно может использоваться в любом месте
рабочей книги. Доступ ко всем именам из любого рабочего листа можно
получить с помощью окна имени в левой части строки формул.
Есть возможность определить специальные имена, диапазон действия
которых ограничивается текущим рабочим листом. Это означает, что эти
имена можно использовать лишь на том рабочем листе, на котором они
определены. Такие имена не отображаются в окне имени строки формул или
окне диалога ―Присвоить имя‖, если активен другой рабочий лист книги.
В таблицах Excel автоматически создаются имена на основе заголовков
строк и столбцов рабочего листа.
Быстро перейти на поименованную ссылку, заменить ссылки, вставить
ссылку в формулу с помощью окна имени в строке формул и мн. др.
Чтобы присвоить имя ячейке или диапазону ячеек необходимо: Выделить
ячейку или группу ячеек, Вставка – Имя – Присвоить
Также с помощью диалогового окна Присвоение имени можно присваивать
имена константам и формулам. Для этого необходимо: в поле Имя ввести
нужное имя, а в поле Формула – константу или формулу – Добавить.
На рис. 2.4 представлен интерфейс диалогового окна Присвоение имени.
Рис. 2. 4. Интерфейс диалогового окна Присвоение имени и пример имен
В качестве имени диапазона можно использовать содержимое нескольких
входящих в его состав ячеек, что упрощает работу с таблицей. Необходимо:
Выделить диапазон ячеек и команда Вставка – Имя – Создать. Появится
диалоговое окно, в котором нужно указать, где в выделенном диапазоне
расположены ячейки, текст которых должен использоваться в качестве имен.
Для ввода имени в формулу предназначена команда Вставка – Имя –
Вставить.
Вопросы для самоконтроля
1.
2.
3.
4.
5.
6.
7.
Дайте определение понятие формула.
Какой порядок действий используется при вычислении формулы?
Перечислите основные операторы, используемые в формуле.
Что подразумевается под функцией и из чего она состоит?
Что такое вложенная функция?
Какие стандартные функции используются в электронной таблице?
Какие ошибки могут возникнуть при работе с функциями?
21
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
8. Какие типы адресации используются при работе с таблицей и в чем их
достоинства?
9. В чем заключается достоинство использования имен в формуле?
ГЛОССАРИЙ
Формула - выражение, которое обязательно начинается знаком равенства и
определяет, какие расчеты нужно произвести на рабочем листе.
Функция – стандартная формула, прописанная в таблице и используемая для
простых или сложных вычислений.
Аргумент функции – значения в функции: числа, текст, логические значения,
массивы, значения ошибок или ссылки, необходимые для получения искомого
результата.
Вложенные функции - функции, являющиеся аргументом другой функции.
Ссылка (адрес) – местоположение ячейки или группы ячеек, используемых в
формуле.
Внешняя ссылка - ссылка на ячейки, находящиеся на других листах книги, в
другой книге.
Удаленные ссылки - ссылка на ячейки, находящиеся в другом приложении
22
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
3. РАБОТА СО СПИСКАМИ И БАЗАМИ ДАННЫХ В
ЭЛЕКТРОННОЙ ТАБЛИЦЕ
3.1. Построение графиков и диаграмм
Графики и диаграммы в электронной таблице позволяют наглядно
представить, в виде графической зависимости, числовые и другие данные
таблицы. На графиках легко просматривается тенденция к изменению. Можно
даже определять скорость изменения тенденции. Различные соотношения,
прирост, взаимосвязь различных процессов — все это легко можно увидеть на
графиках.
Диаграммы связаны с данными листа, на основе которых они были созданы,
и автоматически изменяются каждый раз, когда изменяются данные на листе.
В электронных таблицах можно создавать диаграммы двух видов:
внедренную диаграмму;
лист диаграммы.
Внедренная диаграмма – это диаграмма, расположенная на листе рядом с
таблицей и сохраняемая вместе с листом при сохранении книги. Внедренные
диаграммы также связаны с данными и автоматически обновляются при
изменении исходных данных.
Лист диаграммы – это лист книги, содержащий только диаграмму. Листы
диаграммы связаны с данными таблиц и обновляются при изменении данных в
таблице.
Элементы диаграмм:
- имена категорий;
- ряд данных (данные диаграммы). Значение одной ячейки отображается в
виде маркера.
Ряд данных
Ось
значений
Значения
Имена категорий
Ряд 1
Ряд 2
Легенда
Ось категорий
Маркер данных
Рис. 3.1. Структура диаграммы
23
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Применительно к MS Excel предлагается 9 типов плоских диаграмм и 6 типов
объемных. Эти 15 типов включают 102 формата. Кроме того, имеется
возможность создания собственного пользовательского формата диаграммы.
Для построения диаграммы выполните команду Вставка-Диаграмма или
нажмите кнопку Мастер диаграмм. Заголовками строк и столбцов называются
метки. Метки столбцов являются текстом легенды. Легенда представляет собой
прямоугольник, в котором указывается каким цветом или типом линий
выводятся на графике или диаграмме данные из той или иной строки. Для
редактирования диаграммы необходимо дважды нажать мышью в любом месте
диаграммы.
При создании диаграммы смешанного типа возникают две группы рядов.
Одна группа - гистограмма, а другая может быть графиком, с областями или
XY-точечной. После создания диаграммы можно использовать для каждого
ряда данных любой тип плоской диаграммы. Одной из наиболее подходящих
типов диаграмм для обработки результатов лабораторных исследований
является XY-точечная диаграмма.
Линии тренда можно проводить на гистограммах, графиках, линейчатых и
XY-точечных диаграммах.
Для отображения диаграмм можно использовать не только столбцы, линии и
точки, но и произвольные рисунки.
Типы диаграмм:
1. Гистограмма (столбиковая диаграмма)
Гистограмма показывает изменение данных за определенный период времени
и иллюстрирует соотношение отдельных значений данных.
ось x – категория
ось y – значение
уделяется внимание изменениям во времени
Рис. 3.2. Пример гистограммы
2. Линейчатая диаграмма
Линейчатая диаграмма отражает соотношение отдельных компонентов.
ось x – значение
ось y – категория
уделяется внимание сопоставлению значений
Рис. 3.3. Пример линейчатой диаграммы
3. График
График отражает тенденции в равные промежутки времени. Маркеры
выполнены в виде линии, которые интерполируют определенные значения.
24
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Рис. 3.4. Пример графика
4. Круговая диаграмма
Круговая диаграмма показывает как абсолютную величину каждого элемента
ряда данных, так и его вклад общую сумму. На круговой диаграмме может быть
представлен только один ряд данных. Маркеры отображаются в виде
сегментов. Сумма всех значений должна принимать 100%.
Рис. 3.5. Пример круговой диаграммы
5. Кольцевая диаграмма
Как и круговая диаграмма, кольцевая диаграмма показывает вклад каждого
элемента в общую сумму, но в отличие от круговой диаграммы она может
содержать несколько рядов данных.
Рис. 3.6. Пример кольцевой диаграммы
6. Точечная диаграмма
Маркерами являются точки в декартовой системе координат.
Рис. 3.7. Пример точечной диаграммы
7. Пузырьковая диаграмма
Маркеры характеризуются 2-мя значениями:
1 – положение маркера на плоскости;
2 – размер маркера в зависимости от числа.
Рис. 3.8. Пример пузырьковой диаграммы
8. Диаграмма с областями
Маркеры – области в трехмерной системе координат
25
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Рис. 3.9. Пример диаграммы с областями
9. Лепестковая диаграмма
В лепестковой диаграмме каждая категория имеет собственную ось
координат, исходящую из начала координат. Линиями соединяются все
значения из определенной серии. Лепестковая диаграмма позволяет сравнить
общие значения из нескольких наборов данных.
Маркеры – лучи, выходящие из одной точки. Из нулевой точки выходят
категории (могут быть и отрицательные значения).
Рис. 3.10. Пример лепестковой диаграммы
10. Объемные поверхностные диаграммы
Коническая, цилиндрическая, пирамидальная
3.2. Сводные таблицы
Сводная таблица – динамическая таблица итоговых данных, извлеченных
или рассчитанных на основе информации, содержащейся в списках.
Сводные таблицы электронных таблиц являются интерактивными, т.е. могут
автоматически обновляться при изменении исходных данных. Для просмотра
данных разными способами можно поворачивать таблицу для отображения
данных по строками или по столбцам.
Достоинства применения сводных таблиц:
позволяют создавать обобщающие таблицы, которые предоставляют
возможность группировки однотипных данных, подведения итогов,
нахождения статических характеристик записей;
легко преобразуются;
разрешают выполнять автоматический отбор информации;
на основе сводных таблиц строятся диаграммы, которые динамически
перестраиваются вместе с изменением сводной таблицы.
Для более наглядного представления рассмотрим формирования сводной
таблицы на примере данных, в которых собрана информация по типу
использования оборудования на различных РАТС (рис. 3.11).
26
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Рис. 3.11. Исходные данные для формирования сводной таблицы
На рисунке 3.12 представлен мастер сводных таблиц, при помощи которого
исходные данные преобразуются в вид сводной таблицы: команда меню
Данные Сводная таблица.
а)
б)
в)
Рис. 3.12. Мастер сводных таблиц
На рис. 3.13 представлен макет сводной таблицы, при помощи которого
формируются ее заголовки.
27
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Рис. 3.13. Макет мастера сводных таблиц
Страница – используются в качестве заголовков страниц сводной таблицы.
Строка – используется в качестве заголовков строк сводной таблицы.
Данные – поле (поля), для которого подводится того в сводной таблице.
Столбец - используется в качестве заголовков столбцов сводной таблицы.
В каждую область можно перетащить любое количество полей, причем все
поля использовать не обязательно.
Результатом является сводная
представленная на рис. 3.14.
таблица
с
перечнем
оборудования,
Рис. 3.14. Сводная таблица с перечнем оборудования
В данном примере нас не интересует количество отсутствующих данных,
поэтому данную категорию можно удалить из сводной таблицы. Для этого
необходимо убрать один из флажков на разворачивающимся списке сводной
таблицы (рис. 3.15 а). Кроме того, в сводной таблице полученные данные
можно преобразовать в необходимый вид, например, представить данные в
процентной доле (от суммы по столбцу) выбирается в Параметрах поля (рис.
3.15 б). Результатом является таблица с преобразованными данными (рис. 3.15
в).
28
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
а)
б)
в)
Рис. 3.15. Преобразование сводной таблицы
В общем случае для управления сводной таблицей используется диалоговое
окно и соответствующее меню (рис. 3.16), при помощи которых можно
добавить новую переменную или группу переменных в таблицу или
преобразовать данные в необходимый вид. Кроме того, возможно управление
сводной таблицей через диаграмму.
Рис. 3.16. Меню сводной таблицы
29
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
3.3. Работа со списками и базами данных
Большие массивы информации обычно хранятся в виде базы данных и
обрабатываются специальными средствами, позволяющие осуществлять
следующие операции:
введение баз данных (ввод, коррекция, и удаление записей),
фильтрация (отбор) записей по задаваемому критерию поиска,
сортировка записей по одному или несколькими полям,
получение итогов по группе записей и всей базе данных.
База данных в электронной таблице – это список связанных данных, в
котором строки данных являются записями, а столбцы полями.
В электронной таблице аналогом простой базы данных является список,
который представляет собой группу строк, содержащих связанные данные.
Отличительной особенностью списка заключается в том, что каждый его
столбец содержит однотипные данные.
Примером списка может быть служить список группы.
3.3.1. Формирование списка
Создание связанного списка производится с помощью команды Данные Список – Создать список. После задания диапазона (рис. 3.17) списка
формируется область таблицы, которая задает базу данных в электронной
таблице (рис. 3.18 а). После заполнения данных таблица примет вид, подобный
рис. 3.18 (б).
Рис. 3.17. Создание списка
а)
б)
Рис. 3.18. Формирование списка
Достоинством создания списка заключается в том, что при записи новых
данных размер списка автоматически увеличивается. Управление списком
происходит через специальное меню (рис. 3.19).
30
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Рис. 3.19. Управление списком
Для упрощения работы со списками можно использовать диалоговое окно
формы данных, предназначенное для ввода и обработки информации. Данное
окно открывается с помощью команды Данные – Форма (рис. 3.20).
Рис.3.20. Форма Данные
При помощи Формы и определенных критериев поиска можно из базы
данных определить искомый компонент (рис. 3.21). Диалоговое окно Форма –
Критерий – Далее.
Рис.3.21. Поиск через форму Данных
31
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
3.3.2. Сортировка записей
Сортировка по одному полю:
Осуществляется через соответствующую кнопку на панели инструментов
.
Общая сортировка (как по одному полю, так и более):
Осуществляется через операцию: Данные – Сортировка – Выбрать поля
сортировки (рис. 3.22).
Рис. 3.22. Общая сортировка записей
3.3.3. Фильтрация записей
Процессы ввода записей в список и удаление из списка, а также процесс
поиска информации может упроститься за счет использования фильтров, такие
как: автофильтр и расширенный фильтр.
Функция автофильтра позволяет с помощью определения ряда критериев
производить отбор непосредственно на рабочем листе. Вызывается при помощи
команды Данные – Фильтр – Автофильтр.
В качестве критерия можно использовать содержимое любой ячейки, выбрав
его из выпадающего списка (рис. 3.23 (а)). Также можно использовать
интерфейс пользовательского автофильтра (рис. 3.23 (б)), при помощи которого
можно указать комплекс условий для фильтрации. Результат автофильтра рис.
3.23 (в).
а)
б)
в)
Рис. 3.23. Автофильтр
Для отмены всех критериев: Данные – Фильтр – Отобразить все.
32
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
При расширенном фильтре критерии отбора задаются в отдельной области,
которая должна содержать заголовки столбцов списка.
Задавая критерии отбора записей для столбцов, содержащих текстовые
данные, в качестве символов подстановки можно использовать звездочку (*),
которая заменяет любое количество символа, и знак вопроса (?),
соответствующее одному символу.
Для вызова: команда Данные – Фильтр – Расширенный фильтр (рис. 3.24).
Отличительным признаком расширенного фильтра является написание условий
вне исходного списка, причем заголовки условия должны в точности повторять
заголовки исходной таблицы.
Рис. 3.24. Расширенный фильтр
Кроме того, имеется 12 функций рабочего листа, реализуемых для анализа
данных из списков или БД. Каждая из функций использует три аргумента:
БД Функция (база данных; поле; критерий), где указываются:
• база данных, которая будет рассматриваться для поиска решения;
• поле значений, по которому производится отбор;
• критерии отбора.
На рис. 3.25 представлен пример использования функции баз данных, в
котором отображается максимальный роста у женщин ( ДМАКС() ). Для этого
предварительно в ячейке, находящейся вне базы данных, необходимо указать
условие, по которому будет производиться отбор. В этом случае используется
формула вида: =ДМАКС(A1:D10;D1;F1:F2).
Рис. 3.25. Пример использования функций баз данных
3.3.4. Импорт данных с помощью Microsoft Query
Доступ к информации, хранящейся в базах данных, которые созданы
другими приложениями, в том числе Microsoft Access, dBASE, Microsoft SQL
Server может быть реализован через интегрированную программу Microsoft
Query.
33
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Для доступа к внешним базам данных программа Microsoft Query использует
драйвер ODBC (Open DataBase Connectivity – Открытое взаимодействие с
базами данных).
Чтобы создать запрос, необходимо активизировать команду Данные –
Импорт внешних данных – Создать запрос (диалоговое окно Выбор источника
данных и выбрать один из источников данных (Рис.3.26)).
Рис. 3.26. Выбор источника данных
В окне Выбор источника данных можно указать один из существующих
источников или создать новый источник данных, щелкнув на элементе «Новый
источник данных» появится диалоговое окно Создание нового источника
данных (рис. 3.27).
Рис. 3.27. Создание нового источника данных
В поле № 1 указывается имя источника данных, в поле № 2 – необходимый
драйвер. Поле № 3 «Связь» предназначено для установки драйвера ODBC,
содержимое которого зависит от выбранного драйвера (рис.3.28). Далее
выбирается файл, из которого в последующем будет импортирована
информация (рис. 3.29). После того, как все установки выполнены в списке
диалогового окна Выбор базы данных будет дополнен именем нового
источника данных.
Рис. 3.28. Установка драйвера
34
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Рис. 3.29. Выбор базы данных
После выбора запроса открывается первое окно мастера создания запроса
Создание запроса: выбор столбцов (Рис. 3.30).
Рис. 3.30. Выбор столбцов в базе данных
Далее в окне Создание запроса: отбор данных указать критерии отбора записей
(Рис. 3.31).
Рис. 3.31. Отбор данных
Извлекаемый из базы данных набор записей обычно сортируют. Сортировка
может осуществляться в 4 этапа, которая осуществляется в окне Создание
запроса: порядок сортировки (рис. 3.32).
В окне Создания запроса: заключительный шаг – три переключателя
(рис.3.33). При выборе переключателя вернуть данные в Microsoft Office Excel
данные, полученные в результате выполнения запроса, будут переданы в Excel,
а при выборе переключателя Просмотр или изменение данных в Microsoft
Query – в Microsoft Query.
35
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Рис. 3.32 Сортировка
Рис. 3.33. Импорт данных
При выборе переключателя Вернуть в Microsoft Excel данные будут
переданы в рабочую книгу либо на текущий лист, либо на новый лист.
При выборе Microsoft Query откроется одноименное окно, разделенное на три
области – таблиц. Условий, данных. В области таблиц отображаются название и
перечни полей базы данных, которые упоминаются в запросе. Условия отбора
записи указываются в следующей области, а под ними, в области данных,
находится таблица результатов. В ее столбцах представлены выбранные
пользователем поля базы данных.
Рис. 3.34. Работа с данными в Microsoft Query
36
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Microsoft Query позволяет изменять содержимое таблицы данных, создавать
критерии отбора записей из базы данных и изменять их.
Для определения условий отбора команда Добавить условие меню Условия и
в открывшемся диалоговом окне необходимо сделать соответствующие
установки. Также окно Условие рабочего окна можно дополнить необходим
условием.
Данные таблицы после всех необходимых преобразований необходимо
передать в Excel. Для этого необходимо вызвать команду Файл – вернуть
данные в Microsoft Excel. Откроется диалоговое окно (рис.3.35), при котором
данные будут переданы в рабочую книгу либо на текущий лист, либо на новый
лист.
Рис. 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
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
ГЛОССАРИЙ
Внедренная диаграмма – это диаграмма, расположенная на листе рядом с
таблицей и сохраняемая вместе с листом при сохранении книги.
Лист диаграммы – это лист книги, содержащий только диаграмму.
Маркер – значение одной ячейки на диаграмме.
Метки - заголовки строк и столбцов таблицы на диаграмме.
Легенда - прямоугольник, в котором указывается каким цветом или типом
линий выводятся на графике или диаграмме данные из той или иной строки.
Сводная таблица – динамическая таблица итоговых данных, извлеченных или
рассчитанных на основе информации, содержащейся в списках.
База данных в электронной таблице – это список связанных данных, в котором
строки данных являются записями, а столбцы полями
38
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
4. АНАЛИЗ ДАННЫХ
Электронные таблицы располагают средствами для анализа данных – от
разрешения простых вопросов типа «что - если» (например, что будет, если
увеличится процентная ставка по кредиту») до решения сложных задач
оптимизации. Эти средства – «Подбор параметра», «Таблица подстановки»,
«Поиск решения».
Целевая ячейка – ячейка, для которой нужно найти заданное значение. Она
должна содержать формулу, зависящую от изменяемых ячеек. Пока не будет
найдено решение или пообобран определенный параметр, программа подбирает
значения изменяемых ячеек.
Изменяемые ячейки – ячейки, от которых зависит значение целевой ячейки.
Средства решения задач оптимизации:
• Подбор параметра,
• Таблица подстановки,
• Поиск решения
Средства представления данных:
• Сценарий,
• Консолидация
4.1. Подбор параметра
Средство для прогноза на основе Подбора параметра используется при
наличии зависимости между данными в двух ячейках. Оно предназначено для
определения значения, которое следует ввести в одну ячейку, чтобы получить
требуемое значение в другой. Последняя должна содержать формулу,
использующую адрес ячейки, значение которой подбирается. Подбор параметра
можно осуществить как в таблице, так и на плоской гистограмме или графике.
Для использования Подбора параметра необходимо предварительно задать
формулу, которая ссылается на любое значение подбираемого параметра, затем
это значение при помощи данной опции автоматически подберется.
Например, необходимо определить количество периодов выплаты займа в
500’000 р., если месячный платеж должен составлять 110’000 р. при
процентной ставке, равной 50%.
На рис. 4.1. представлена реализация процедуры подбора параметра. Так рис.
4.1-а иллюстрирует запись исходных данных в таблицу. Кроме того в целевой
ячейке (В6) предварительно произведен расчет платежа без удовлетворения
поставленного условия. На рис. 4.1-б представлено диалоговое окно Подбора
параметра, в котором устанавливаются: целевая ячейка, значение, которое
необходимо подобрать, а также изменяемая ячейка. Рис. 4.1-в демонстрирует
результат подбора параметра в соответствии с условием.
39
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
а)
б)
в)
Рис. 4.1. Процедура подбора параметра
Процедура подбора параметра:
1. Выберите целевую ячейку.
2. Команда Сервис – Подбор параметра. В поле Установить в ячейке должен
быть отражен адрес целевой ячейки.
3. Задайте в поле «Значение» значение, которое должна содержать целевая
ячейка.
4. В поле «Изменяя значение ячейки» укажите адрес ячейки, хранящей
параметр, значение которого необходимо установить таким, чтобы в целевой
ячейке получить необходимое значение.
Подбор параметра также можно провести при работе с диаграммами, изменяя
соответствующий маркер (рис. 4.2).
Рис. 4.2. Подбор параметра на диаграмме
4.2. Таблицы подстановки
Таблицы подстановки позволяют оценить влияние параметров на некоторую
величину. Таблица подстановки создается на основе ячейки с формулой,
содержащей ссылку на ячейку, определенную как поле ввода (ячейка ввода), и
списка исходных значений (они последовательно представляются в ячейку
ввода с целью создания списка результатов).
Существует две разновидности таблиц подстановки, а именно: с одной
переменной и двумя переменными.
40
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Таблицы подстановки с одной переменной
Данная таблица используется для вычисления результатов, которые можно
поместить в одну строку или столбец. В таблице используется список исходных
значений в первой строке (или столбце), которые последовательно
подставляются в формулу, а результаты помещаются в следующую строку (или
столбец). Одни и те же исходные значения могут быть подставлены в несколько
формул.
Рис. 4.3. Структура таблицы подстановки с одной переменной
Формула обязательно должна содержать ссылку на ячейку ввода, в качестве
которой может выступать любая ячейка рабочего листа.
Пример: необходимо проследить, как изменяется накапливаемая сумма и
ежемесячная прибавка (рис. 4.4) в зависимости от периода накопления (от 15 до
30 лет).
Рис. 4.4. Исходные данные для таблицы подстановки
Создание таблицы подстановки с одной переменной:
1. Создайте список исходных значений и формулы для расчета данных
таблицы в соответствии правилом, учитывая, что формула должна содержать
ссылку на ячейку ввода (рис. 4.5).
2. Выделите диапазон, включающий исходные значения и формулы.
3. Вызовите команду Данные – Таблица подстановки.
4. В диалоговом окне Таблица подстановки задайте ссылку на ячейку ввода.
Если исходные значения образуют столбец, введите ссылку в поле Подставлять
значение по строкам в, если они образуют строку – в поле Подставлять
значения по столбцам
Применительно к исходным данным, ячейка С2 является ячейкой ввода.
Результатом действия является заполненная таблица для указанного диапазона
(рис. 4.6).
41
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Рис. 4.5 Список исходных значений
Рис. 4.6. Результат таблицы подстановки
Таблицы подстановки двумя переменными
Если результаты вычисления должны зависеть от двух параметров,
необходимо использовать таблицу подстановки с двумя переменными.
Рис. 4.7. Структура таблицы подстановки с двумя переменными
Такая таблица может содержать только одну формулу, которая помещается в
ячейку, расположенную в левом верхнем углу. По аналогии с предыдущем
случаем сначала формируется сама таблица с изменяемыми значениями (рис.
4.8), потом для нее производится процедура «таблицы подстановки» (рис. 4.9).
42
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Рис. 4.8. Список исходных значений
Рис. 4.9. Результат таблицы подстановки
4.3. Сценарии
Сценарий представляет собой набор сохраненных электронной таблицей
значений, которые она может автоматически подставить в ячейки рабочего
листа. Сценарии можно использовать для прогноза результатов моделирования
и расчетов.
Добавления сценария
1. Создайте рабочий лист с данными и формулами, которые
устанавливают связи между ячейками.
2. Команда Сервис – Сценарий
3. Кнопка Добавить. В окне Добавления сценария задайте имя сценария в
поле Название сценария (рис. 4.10), а адреса изменяемых ячеек – в поле
Изменяемые ячейки (рис. 4.11).
Рис. 4.10. Диспетчер сценариев: изменение сценария
43
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Рис. 4.11. Диспетчер сценария: изменения значений
Для создания отчета необходимо в диалоговом окне Диспетчер сценариев
выбрать пункт Отчет, в котором можно установить вид отчета: либо как
структура, либо как сводная таблица. Также в отчете можно отразить
необходимые результаты.
Рис. 4.12. Результат Сценария
4.4. Консолидация данных
Если таблицы имеют одинаковые столбцы с одинаковыми типами данных в
них, то они называются однотипными. Консолидация данных позволяет
объединить несколько однотипных таблиц в одну.
Консолидация данных – процедура получения итогов для данных в
различных частях таблицы. Подлежащие консолидации диапазоны ячеек могут
располагаться как на одном рабочем листе, так и на разных, а также в
различных книгах.
Данные для консолидации можно задавать тремя способами:
С помощью формул, содержащих ссылки. Для расчета итоговых данных
составляются трехмерные формулы, т.е. формулы, которые содержат ссылки на
диапазоны, включающие ячейки разных листов.
По расположению ячеек. Исходные данные для функции консолидации
задаются в диалоговом окне в виде ссылок на диапазоны.
По заголовкам строк и столбцов.
44
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Рис. 4.13. Интерфейс консолидации
Если необходимо можно создать связи с исходными данными, причем в
пределах одной рабочей книги данные будут обновляться автоматически.
4.5. Поиск решения
С помощью Поиска решения можно определить, при каких значениях
указанных влияющих ячеек формула в целевой ячейке принимает нужное
значение (минимальное, максимальное или равное какой-либо величине).
Любой фактор в процессе поиска решения обычно интерпретируется как
некоторый ресурс, выраженный в количественном измерении (минуты, штуки,
рубли и т.д.). Для процедуры поиска можно также задавать ограничения,
причем необязательно, чтобы при этом использовались те же влияющие ячейки.
Для расчета заданного значения применяются различные математические
методы поиска.
Цель поиска решения состоит в том, чтобы найти такие значения входных
факторов, которые обеспечат получение целевых значений целевых факторов.
На первом этапе необходимо выявить главную проблему соответствующей
ситуации, для которой необходимо подобрать такие значения целевой функции,
которые обеспечат переход системы из реального состояния в желаемое. Для
этого необходимо выделить целевую функцию, которая является основой для
процесса поиска решения. Кроме того, необходимо наложить ограничения на
соответствующие элементы целевой функции для верного принятия решения,
устанавливаемые экспертом, исходя из статистических данных, основанных на
предыдущих знаниях о проблеме.
Выделив целевую функцию и ограничения, эксперт запускает процесс поиска
решения, при котором вычисления осуществляются в обратном порядке от
целевого фактора к входным факторам. При этом система должна выполнить
проверочный счет, подстановкой результатов обратной задачи, и получить
прогноз развития ситуации.
Процедура поиска решения
1. Создайте новую таблицу с формулами, которые устанавливают связи
между ячейками рабочего листа.
2. Выделите целевую ячейку, которая должна принять необходимое
значение, и выберете команду Сервис – Поиск решения. Поле Установить
45
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
целевую ячейку диалогового окна настройки Поиск решения будет содержать
адрес целевой ячейки.
3. Установить требуемый переключатель в поле Равной.
4. В поле Изменяя ячейки укажите, в каких ячейках программа должна
изменять значения в поисках оптимального результата.
5. Создайте ограничения, если необходимо.
6. Щелкнув на кнопке Выполнить, запустите процесс поиска решения.
7. Если данные удовлетворяют требованиям, сохраните найденное решение
и создайте отчет.
Обобщенная схема процесса поиска решения представлена на рис. 4.14.
Рис. 4.14. Процесс поиска решения
Перед запуском процедуры поиска решения исходные данные должны быть
представлены в виде таблицы, которая содержит формулы, отражающие
зависимости между данными таблицы. Пример процедуры поиска решения
представлен на рис. 5.15.
Рис. 4.15. Поиск решения
46
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Рис. 4.16. Результат поиска решения
Параметры процедуры поиска решения (рис. 4.17)
Задать параметры, определяющие способ выполнения вычислений, можно в
диалоговом окне Параметры поиска решения. Основные параметры:
Максимальное время – максимальное время в секундах, отведенное на
поиск решения задачи.
Предельное число итераций – максимальное количество итераций,
возможных в течение времени, необходимого для поиска конечного
результата.
Относительная погрешность – точность результата.
Допустимое отклонение – величина допуска на отклонение от
оптимального решения, если множество значений влияющей ячейки
ограничено множеством целых чисел. Если значение допуска увеличить,
поиск решения завершится быстрее, однако полученный результат будет
менее точным.
Сходимость – значение относительного изменения, при достижении
которого в последних пяти итерациях поиск решения прекращается.
Линейная модель - служит для ускорения поиска решения путем
применения к задаче оптимизации линейной модели. Нелинейные модели
предполагают использование нелинейных функций, фактора роста и
экспоненциального сглаживания, что замедляет вычисления.
Неотрицательные значения - позволяет установить нулевую нижнюю
границу для тех влияющих ячеек, для которых не было задано
соответствующее ограничение в диалоговом окне Добавить ограничение.
Автоматическое масштабирование - используется, когда числа в
изменяемых ячейках и в целевой ячейке существенно различаются.
Показывать результаты итераций - приостанавливает поиск решения
для просмотра результатов отдельных итераций.
Загрузить модель - после щелчка на этой кнопке отрывается
одноименное диалоговое окно, в котором можно ввести ссылку на
диапазон ячеек, содержащих модель оптимизации.
47
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Сохранить модель - служит для отображения на экране одноименного
диалогового окна, в котором можно ввести ссылку на диапазон ячеек,
предназначенный для хранения модели оптимизации.
Оценка линейная - выберите этот переключатель для работы с линейной
моделью.
Оценка квадратичная - выберите этот переключатель для работы с
нелинейной моделью.
Разности прямые - используется в большинстве задач, где скорость
изменения ограничений относительно невысока. Увеличивает скорость
работы средства Поиск решения.
Разности центральные - используется для функций, имеющих
разрывную производную. Данный способ требует больше вычислений,
однако его применение может быть оправданным, если выдано
сообщение о том, что получить более точное решение не удается.
Метод поиска Ньютона - требует больше памяти, но выполняет меньше
итераций, чем в методе сопряженных градиентов.
Метод поиска сопряженных градиентов - реализует метод сопряженных
градиентов, для которого требуется меньше памяти, но выполняется
больше итераций, чем в методе Ньютона. Данный метод следует
использовать, если задача достаточно большая и необходимо экономить
память или если итерации дают слишком малое отличие в
последовательных приближениях.
Рис. 4.17. Параметры поиска решения
Совокупность установленных параметров и ограничений можно сохранить на
рабочем листе в качестве модели.
Вопросы для самоконтроля
1.
2.
3.
4.
5.
Перечислите средства оптимизации в электронных таблицах.
Что такое целевая ячейка?
Что такое Подбор параметра и без чего оно не возможно?
Можно ли производить подбор параметра на диаграмме?
Что такое ячейка ввода?
48
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
6. Виды таблицы подстановки?
7. Что такое сценарий?
8. Что такое консолидация?
9. В чем заключается цель поиска решения?
10.Перечислите параметры поиска решения.
ГЛОССАРИЙ
Целевая ячейка – ячейка, для которой нужно найти заданное значение.
Изменяемые ячейки – ячейки, от которых зависит значение целевой ячейки.
Подбора параметра – средство оптимизации, которое позволяет определить
значение, которое следует ввести в одну ячейку, чтобы получить требуемое
значение в другой.
Ячейка ввода - ячейки с формулой, содержащая ссылку на ячейку,
определенную как поле ввода.
Сценарий - набор сохраненных электронной таблицей значений, которые она
может автоматически подставить в ячейки рабочего листа.
Консолидация данных – процедура получения итогов для данных в различных
частях таблицы.
Поиска решения – средство, которое позволяет определить, при каких
значениях указанных влияющих ячеек формула в целевой ячейке принимает
нужное значение (минимальное, максимальное или равное какой-либо
величине).
Сходимость – значение относительного изменения, при достижении которого в
последних пяти итерациях поиск решения прекращается.
49
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
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.
Процесс работы макрорекордера можно условно разделить на четыре этапа:
- запуск макрорекордера;
- присвоение макросу имени;
- выполнение записываемых операций;
- завершение записи.
Начало записи
При записи макроса фиксируются все действия, в том числе и ошибочные.
Для начала записи макроса необходимо активизировать чистый рабочий
лист, выбрав в меню Сервис команду Макрос, затем – Начать запись. На экране
появится диалоговое окно Запись макроса, в котором следует указать имя
50
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
макроса, ввести его краткое описание, задать комбинацию клавиш для запуска
создаваемого макроса и определить его место хранения.
Примечание:
- Имя макроса может состоять максимум из 255 символов. Оно не должно
содержать пробелов и специальных символов. Между фрагментами
длинных имен макросов вместо пробелов вставляются символы
подчеркивания (_).
- При назначении комбинации клавиш для запуска макроса учитывается
регистр символов.
Рис. 5.1. Запись макроса
После щелчка на кнопке ОК в диалоговом окне запись макроса появится
панель инструментов.
– Остановить запись – остановить процесс записи макроса.
– относительная ссылка – определяется режим адресации при записи
макроса. Если кнопка не активизирована, по умолчанию применяются
абсолютные ссылки.
В процессе записи макроса программа напоминает пользователю, что все
выполняемые им действия записываются макрорекордером.
Рис. 5.2. Строка состояния
ПРИМЕР (рис. 5.3):
- Отменить отображение сетки (рис. 5.3-а);
- Задать для всех ячеек денежный формат (рис. 5.3-б);
- Установить вид и размер шрифта текста ячеек (рис. 5.3-в).
Выполнив перечисленные действия, необходимо остановить запись (рис. 5.3г).
Рис. 5.3-е иллюстрирует результат выполнения макроса.
51
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
а)
в)
б)
г)
д)
е)
Рис. 5.3. Создание макроса при помощи макрорекордера
Запуск макроса осуществляется активизацией команды Сервис – Макрос –
Макросы, где отмечается нужный макрос, или нажав сочетание клавиш Ctrl и
любой другой установленной.
Рис. 5.4. Окно макроса
5.1.2. Панель инструментов для работы с макросами
Для отображения панели работы с макросами (рис. 5.5) необходимо:
Вид – Панель инструментов – Visual Basic.
Рис. 5.5. Панель VBA
52
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Кнопка
Таблица 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. Данная процедура
создается на основе событий (например, изменение размера окна или щелчок на
53
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
кнопке) и методов. Каждому событию подставлены в соответствии методы,
которые задают необходимость выполнения определенных операций при
наступлении данного события (пример, изменение активного листа, с
изменением которого может измениться команда меню). В VBA пользователь
может задавать собственные события и ставить им в соответствии методы.
Макрос должен начинаться с оператора Sub и заканчиваться End Sub, между
которыми размещаются имя макроса для идентификации, текст макроса,
который можно редактировать, а также снабжать комментариями.
Для активизации режима отладки можно воспользоваться пошаговым
выполнением. Для этого меню Debug (Отладка) – Step Info (Пошагово) или
клавиши F8. Кроме того, можно устанавливать точки для остановки
выполнения макроса и его редактирования.
Рис. 5.7. Пошаговая отладка макроса
5.1.4. Создание панели инструментов для запуска макросов
Можно создавать собственные панели инструментов. Меню Сервис –
Настройка – на вкладке Панели Инструментов кнопка Создать (рис. 5.8).
Рис. 5.8. Создание панели инструментов
Для вставки кнопок необходимо перейти на вкладку Команды, где
представлены все доступные элементы панелей инструментов и все команды,
которые объединены в категории.
54
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Рис. 5.9. Настройка панели инструментов: назначение макроса
Таким образом, на рабочий лист можно добавить либо новую кнопку в
главное меню или новую панель инструментов созданную сами пользователем.
5.2. Элементы управления на рабочем листе
Элементами управления называются размещаемые на рабочих листах и в
диалоговых окнах объекты, предназначенные для отображения, ввода и
вычисления данных. К их числу относятся надписи, поля со списками,
переключатели, флажки, кнопки и некоторые другие объекты. С помощью
элементов управления можно запустить макросы и веб-сценарии.
Виды элементов управления: ActiveX и элементы управления панели
инструментов Формы.
Элементы управления ActiveX способны работать с макросами VBA и вебсценариями. Они являются независимыми программными компонентами.
Кнопки для вставки этих элементов управления рассоложены на панели
инструментов Элементы управления.
Элементы управления панели инструментов Формы пакета M.Office
обеспечивают совместимость с ранними версиями Excel и могут
использоваться на листах макросов XLM.
Рис. 5.10. Элементы управления
55
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
5.2.1. Элементы управления ActiveX
Для открытия панели инструментов необходимо правой кнопкой мыши
щелкнуть на любой другой панели и в появившемся списке выбрать пункт
Элемент управления или команда Вид – панели инструментов – Элементы
управления.
Рис. 5.11. Панель элементов управления
Таблица 5.1. Элементы управления
Значок Название
Режим
конструктора
Свойства
Исходный
текст
Флажок
Назначение
Переводит Excel в режим конструктора и
отключение всех элементов на рабочем листе
Активизация окна Свойства с перечнем свойств
листа, активного на данный момент
Открытие окна редактора Microsoft Visual Basic
Позволяет задать или отменить определенный
параметр
Поле
Добавление области, в которой можно вводить и
редактировать текст
Кнопка
Создание кнопки, предназначенной для запуска
Visual Basic
Переключатель Для выбора одного или нескольких параметров
Список
Размещение списка, где можно выбрать один или
несколько элементов
Поле
со Создание поля со списком, предоставляющего
списком
пользователю возможность ввести текст либо
выбрать элемент из списка
Выключатель
Добавление на рабочее поле выключателя
Счетчик
Полоса
прокрутки
Используется для увеличения или уменьшения
числовых значений
Создание вертикальной полосы прокрутки
Надпись
Надпись в виде текста
Рисунок
Изображении
Другие
элементы
Добавление дополнительных элементов управления
56
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Для создания элемента управления необходимо щелкнуть на нужном
элементе на панели. Затем выбранный элемент автоматически переходит в
режим конструктора, при котором можно изменять параметры и свойства
элемента. Для работы с элементом необходимо выйти из режима конструктора.
Имена элементам даются автоматически в зависимости от типа объекта
(Label1, label2 и т.д.). Данные имена используются для работы с макросами и
несут смысловую нагрузку. Для переименования необходимо открыть окно
Свойства (Properties) на панели инструментов Элементы управления – задать
новое имя в строке Name – Enter.
Для переименования самого элемента необходимо в режиме конструктора
выделить элемент правой кнопкой мыши – Объект – Edit или Caption в
Свойствах.
Для форматирования элемента управления ActiveX можно воспользоваться
двумя окнами:
– с помощью окна Формат элемента управления определяются цвет и вид
линий контура, размеры, позиция и др. (рис. 5.12).
Рис. 5.12. Окно Формат элемента управления
– в окне Properties (Свойства) задаются свойства элементов управления,
определяющие как общие характеристики объекта (размер, цвет и др.), так и
поведение элемента управления.
Диалоговое окно Properties
Свойства изменяются в режиме конструктора, устанавливая нужные
значения в окне Properties
57
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Рис. 5.13. Вид окна Properties
Основные свойства окна Properties:
Name – определяет имя, под которым объект фигурирует в программе.
Accelerator – предназначен для быстрого доступа к объекту с помощью
клавиатуры.
AutoSize – автоматически изменяет размеры элемента управления,
чтобы на экране был виден весь его заголовок.
BackColor – определяет цвет фона.
BackStyle – указывает, будет ли метка прозрачной.
BarderStyle – определяет, выводится ли рамка на экран. Если это так, то
ее цвет зависит от свойства BorderColor.
Caption – выводит текст заголовка.
Enabled – определяет, можно ли выделить объект (по отношению к
тени).
Font – открывает диалоговое окно Шрифт для изменения стиля и
размера шрифта.
ForeColor – определяет цвет текста заголовка.
Height – устанавливает высоту элемента управления, которую также
можно изменить изменяя ее мышью.
Left – устанавливает расстояние между левым краем элемента
управления и левым краем рабочего листа.
MousePointer – определяет вид указателя мыши, когда он находится на
элементе управления.
58
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Picture – выбор рисунка.
Shadow – принимает значение Истина или Ложь в зависимости от того,
имеет ли объект тень.
SpecialEffect – определяет вид объекта.
Top – указывает расстояние между верхними краями элемента
управления и рабочего листа.
Visible – определяет, является ли элемент видимым.
Width – задает ширину объекта.
WordWrap – предназначен для оптимального размещения заголовка на
объекте.
5.2.2. Элементы управления панели инструментов Формы
Предназначены для обеспечения совместимости с документами старых
версий Excel, использующими только эти элементы управления. Функции
аналогичны функциям элементов управления ActiveX, однако обладают
меньшими возможностями.
Для открытия панели инструментов необходимо правой кнопкой мыши
щелкнуть на любой другой панели и в появившемся списке выбрать пункт
Элемент управления или команда Вид – Панели инструментов – Формы.
Рис. 5.14. Панель инструментов Формы
Таблица 5.2. Дополнительные элементы управления панели Формы
Значок Название Назначение
Группа
Для создания на рабочем листе рамки для группы
элементов управления
Свойство Открывается диалоговое окно Формат элемента
элемента управления, в котором задаются параметры элемента
управления
Сетка
Сокрытие или отображение на рабочем листе сетки
(разлинованных ячеек)
Для форматирования элемента управления Форма используется диалоговое
окно Формат элемента управления, которое открывается нажатием правой
кнопкой мыши на элемент – команда Формат объекта.
5.2.3. Создание выпадающего списка при помощи меню Проверка
Пример использования выпадающего списка через ЭУ Список:
1) Простой
Сочетание клавиш Alt + стрелка вниз позволят из введенных уже данных
выбрать один вид в текущую ячейку:
59
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Рис. 5.15. Простое задние выпадающего списка
2) Стандартный
а) В свободных ячейках вне рабочей области создается диапазон данных,
которые должны быть в дальнейшем «выпадать» через Список. Для удобства
диапазону этих данных может быть присвоено Имя (Вставка – Имя –
Присвоить).
б) Выделяются ячейки, в которых необходимо получить выпадающий список,
Данные – Проверка. На вкладке Параметры: Тип данных – Список, Источник –
диапазон данных или Присвоенное имя.
а)
б)
в)
г)
д)
е)
ж)
60
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Рис. 5.16. Стандартное задание выпадающего списка
Вопросы для самоконтроля
1.
2.
3.
4.
5.
6.
7.
8.
В чем достоинство создания макроса?
Какими способами можно создать макрос?
В чем заключаются достоинства и недостатки макрорекордера?
Как можно провести редактирование макроса?
Что такое элемент управления?
Виды элементов управления?
Для чего необходим режим конструктора?
Как можно произвести форматирование элемента управления?
ГЛОССАРИЙ
VBA (Visual Basic for Applications) - это объектно-ориентированный язык
макропрограммирования высокого уровня, встроенный во все программы
пакета Microsoft Office.
Макрос (или макрокоманда) - последовательность команд и функций,
записанных в модуле VBA, позволяющая автоматизировать выполнение
основных операций.
Макрорекордер - наиболее простой метод создания макрокоманды, при котором
протоколируются все выполняемые пользователем действия и представляется в
виде программного кода VBA.
Элемент управления - размещаемые на рабочих листах и в диалоговых окнах
объекты, предназначенные для отображения, ввода и вычисления данных.
Режим конструктора переводит Excel в режим отключения всех элементов
управления на рабочем листе
61
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
6. ОСНОВЫ VBA (VISUAL BASIC FOR APPLICATION)
6.1. Объекты, методы, свойства
Visual Basic позволяет создавать программные продукты, способные с
достаточной полнотой автоматизировать решение конкретных задач
пользователя.
VBA является объектно-ориентированной средой, содержащей большой
набор объектов, каждый из которых обладает множеством свойств и методов.
Объекты и инструменты относятся к определенному классу (например, класс
TextBox).
Свойства и методы также являются членами класса. С помощью свойств
описывается, как выглядит объект, в частности дается информация о приемах
форматирования текста, цвете и размере шрифта. Методы являются
процедурами, которые могут быть выполнены для объекта (процедуры
создания и удаления объекта, процедуры событий, определяющие принцип
взаимодействия объекта с пользователем и т.д.).
Объекты (аналог существительному)
Объектом считается любой элемент приложения ─ ячейка, лист, рабочая
книга, диаграмма. Фактически объектом является и само приложение Excel.
Объекты могут включать области ячеек, рамки ячеек, окна, сценарии, стили и
т.д. Каждый класс объектов имеет свое множество свойств, функций и событий.
Методы (аналог глаголу)
Метод ─ это действие, которое может быть выполнено над объектом.
Методы реализуются посредством выполнения процедуры, которая является
членом класса объектов.
Синтаксис вызова: Объект.Метод – указываются имя вызывающего метод
объекта и имя самого метода, разделенные точкой.
Пример: Ball.Kick или Мяч.Ударить; Вода.Пить
Комбинация двоеточия и знака равенства в коде всегда указывают на
параметр метода, т.е. как выполняется действие.
Методы могут иметь много параметров как обязательные, так и нет.
Пример 1: защита листа Лист1 от внесения изменения (метод Protect)
Sheets(―Лист1‖).Protect
Пример 2: добавление нового рабочего листа
Worksheet.Add Before:=Worksheets(1)
Свойство (аналог прилагательному)
Свойство является атрибутом объекта, описывающим, как объект выглядит
(его цвет, размер и местоположение) и как он действует (является ли видимым,
ссылается ли на другой объект). При создании объекта выполняется процедура
создания экземпляра этого объекта.
62
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Для присваивания свойству нового значения, необходимо создать оператор
присваивания, в котором слева от знака равенства будут указаны имя и
свойство объекта (разделенные точкой), а справа ─ новое значение.
Синтаксис вызова: Объект.Свойство
Свойство всегда присутствует в левой или правой части выражений,
связанных с присвоением значения. Отсутствует двоеточие перед знаком
равенства.
Пример 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
На высшем уровне иерархии стоит приложение, далее идут проекты,
связанные с фактическими документами этого приложения. На третьем уровне
находятся модули (модули приложения, модули пользователя, модули класса,
модули форм и модули ссылок), а на последнем – их процедуры и функции.
63
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Иерархия, используемая в VBA, представлена на рис. 6.1.
Проект
Приложение
Проект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 используются процедуры без параметров, которые могут
выступать в роли командных макросов и процедур обработки событий.
64
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
На рис. 6.2 представлен интерфейс VBA.
Рис. 6.2. Интерфейс 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 (логический) логические переменные, принимающие одно из двух
значений: Истина или Ложь
Byte
(короткий целое число из диапазона от 0 до 255
целый беззнаковый)
65
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Integer (целый)
Long
(длинный
целый)
Currency (денежный)
целые числа из диапазона от -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 10324
до 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).
66
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Приложение
Проект
Модуль
Процедура
Рис. 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
67
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
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()
68
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
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 ("первое число")
69
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Рис. 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.
70
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Рис. 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.
71
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Рис. 6.9. Пример процедуры 4
6.5. Управляющие конструкции VBA
Управляющие конструкции языка программирования – это инструкции и
группы инструкций, применение которых позволяет изменять по мере
необходимости
последовательность
выполнения
других
инструкций
программы. Данные конструкции подразделяются на ветвления и циклы.
Ветвлением называется управляющая конструкция, позволяющая пропускать
при выполнении те или иные группы инструкций в зависимости от значения
условия. Цикл – это управляющая конструкция, представляющая возможность
многократно выполнять группы инструкций до наступления какого-либо
события.
6.5.1. Ветвление
Конструкция If…Then
Конструкция If…Then дает VBA указание принять простейшее из решений:
если условие, идущие после оператора If, истинно, нужно выполнить
следующие за ними оператор (или операторы); если же условие ложно, нужно
перейти к строке, расположенной непосредственно за условной конструкцией.
Однострочная конструкция:
72
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
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.
73
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Пример:
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 для более сжатого вида программы.
74
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Такую конструкцию рекомендуется использовать, если решение, которое
необходимо принять в программе, зависит от одной переменной или от
выражения, имеющего не менее трех-четырех значений. Такая переменная (или
выражение) называется тестируемым случаем.
Повторяемое выражение сравнивается с выражениями, находящимися после
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
75
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
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.
76
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Для записи программного кода элемента управления необходимо дважды
щелкнуть мышью на этом элементе (рис. 6.14 а) и он автоматически перейдет в
режим VBA и сформирует «операторные скобки» с учетом события
пользовательской формы (рис. 6.14 б).
а)
б)
Рис. 6.14. Формирование операторных скобок элемента управления
События пользовательской формы
Событие - это сигнал, подаваемый, если с объектом что-то происходит.
Например, кнопка может генерировать событие в ответ на щелчок мышкой по
ней, строка ввода – в ответ на ввод чего-то, на щелчок мыши по ней, и т.д.
Рис. 6.15 События пользовательской формы
Некоторые виды событий:
События мыши — одинарное (двойное) щелканье левой кнопкой мыши на
объекте; нажатие (отпускание) кнопки мыши; передвижение курсора мыши
по элементу управления.
События клавиатуры — нажатие простого символа, функциональных
клавиш или какого-то сочетания символов на клавиатуре.
События формы — загрузка (Load), выгрузка (Unload) формы и пр.
Событие Click происходит, когда пользователь нажимает и отпускает кнопку
мыши, в то время, когда ее указатель находится на объекте. Событие может
произойти при изменении значения элемента управления.
Формат процедуры-обработчика события
77
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Private Sub Form_Click()
Private Sub object_Click([index As Integer])
где object – имя объекта, к которому относится этот обработчик.
Пример:
Private Sub Form_Click()
MsgBox “Click”
End Sub
Рис. 6.17. Пример создания кнопки
Написание кода программы для ЭУ
Программирование элемента управления происходит по тем же принципам,
что программирование макроса.
Программирование кнопки:
Пример 1: Написание Привет после нажатия кнопки:
Private
Sub
MsgBox "Привет :)"
End Sub
Рис. 6.18.
управления
Кнопка_Click()
Пример
1
создания
элемента
Пример 2: Проверка условий: пересчет таблицы
Рис. 6.19. Исходная таблица
78
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Создаем кнопку (рис. 6.20)
Рис. 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.
79
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Рис. 6.22. Реализация рассматриваемого примера
6.7. Пользовательские формы, создаваемые в VBA
Пользовательские формы необходимы для создания более эффективного
интерфейса взаимодействия с пользователем.
Например, вместо ввода личной информации непосредственно на рабочем
листе можно создать пользовательскую форму, запрашивающую ввод данных,
или форма для программы расчета данных.
Формы являются объектами, которые могут вызываться другими модулями
приложения.
К формам можно добавлять пользовательские методы и свойства. Для
создания в форме нового метода надо добавить процедуру, объявленную со
словом Public:
Public Sub UserMethod()
операторы
End Sub
Стили интерфейса:
1) однодокументный (SDI) – можно открыть только один документ, при этом
нужно закрыть активный документ, чтобы открыть другой;
2) многодокументный (MDI) – поддерживает несколько форм внутри
основной формы-контейнера; имеет в меню Window элементы для
переключения между окнами или документами;
3) стиль explorer – окно, имеющее две панели, или области, обычно,
состоящие из иерархического представления уровней информации слева и
области отображения справа.
80
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Этапы создания формы:
1. На форму помещаются нужные элементы управления и красиво
размещаются.
Результат: имеем форму с элементами управления, но надписи на них
стандартные: Command1, Label1, и т.д.
2. Задаются свойства формы и элементов управления
Результат: форма имеет нужный вид, на всех ЭУ понятные надписи, нужные
картинки и т.д. Запуск ее невозможен в связи отсутствия кода программы.
3. Пишутся обработчики событий элементов управления.
Результат: выполнение действий в соответствии с заданием.
Для вставки пользовательской формы необходимо Insert (Вставка) –
UserForm (Пользовательская форма).
При отсутствии окна свойства его можно открыть, выполнив: View (Вид)
Properties Window (Окно свойств).
Рис. 6.23. Интерфейс создания формы
Пример 1: Создать форму для расчета функции y
a2
место для вывода (из программы) результата расчета;
текстовое поле для ввода исходных данных;
кнопку для запуска программы и отмены.
b2 , содержащую:
После задания вида формы (рис. 6.24) следует задание кода программы.
81
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Рис. 6.24. Создание формы для примера 1
Двойной щелчок по кнопке «Вычислить» переведет форму в редактирование
кода.
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
82
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Private Sub VSch_Click()
UserForm1.Show
End Sub
Рис. 6.26. Использование метода Show
Пример 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
83
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Пример 3. Создание формы для ввода пользователем данных и вывода их в
строки таблицы
Рис. 6.29. Создание формы для примера 3
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 ?
84
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
3. Опишите иерархию в VBA?
4. Что такое процедура?
5. Что такое элемент управления?
6. Опишите синтаксис переменной?
7. Какие типы констант используются в VBA?
8. Для чего используются функция MsgBox и InputBox?
9. Какие управляющие конструкции используются в VBA?
10.Что такое событие пользовательской формы?
11.Что такое пользовательская форма?
12.Какие стили интерфейса используются при создании пользовательской
формы?
ГЛОССАРИЙ
VBA (Visual Basic for Applications) - это объектно-ориентированный язык
макропрограммирования высокого уровня, встроенный во все программы
пакета Microsoft Office.
Макрос (или макрокоманда) - последовательность команд и функций,
записанных в модуле VBA, позволяющая автоматизировать выполнение
основных операций.
Элемент управления - размещаемые на рабочих листах и в диалоговых окнах
объекты, предназначенные для отображения, ввода и вычисления данных.
Режим конструктора переводит Excel в режим отключения всех элементов
управления на рабочем листе.
Объектом считается любой элемент приложения ─ ячейка, лист, рабочая книга,
диаграмма. Объекты могут включать области ячеек, рамки ячеек, окна,
сценарии, стили и т.д. Каждый класс объектов имеет свое множество свойств,
функций и событий
Метод ─ это действие, которое может быть выполнено над объектом.
Свойство является атрибутом объекта, описывающим, как объект выглядит
(его цвет, размер и местоположение) и как он действует (является ли видимым,
ссылается ли на другой объект).
Событие – взаимодействие пользователя с определенным объектом на рабочем
листе.
Модуль – это часть программы, оформленная в таком виде, при котором
допускается ее независимая трансляция.
Процедурой называется фрагмент кода (минимальная семантически
законченная программная конструкция), заключенный между операторами Sub
и End Sub.
Переменная – это поименованная область памяти, используемая для хранения
данных в течение работы процедуры
Область видимости переменной – это область программ, где имя переменной
считается допустимым (видимым), а, следовательно, возможен доступ к ее
значению.
85
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
MsgBox - эта функция отображает диалоговое окно, содержащее сообщение
длиной до 1024 символов, в которое с помощью операции конкатенации можно
включить значение переменных, а также (необязательно) кнопки для реакции
на отображения окна (по умолчанию только кнопка ОК).
InputBox - функция, применяемая для ввода значений переменных в программу.
Оператор If - это оператор позволяющий задавать выполнение тех или иных
действий в зависимости от заданных условий.
Управляющие конструкции языка программирования – это инструкции и
группы инструкций, применение которых позволяет изменять по мере
необходимости последовательность выполнения других инструкций программы
Ветвлением называется управляющая конструкция, позволяющая пропускать
при выполнении те или иные группы инструкций в зависимости от значения
условия.
Цикл – это управляющая конструкция, представляющая возможность
многократно выполнять группы инструкций до наступления какого-либо
события.
Событие элемента управления - это сигнал, подаваемый, если с объектом чтото происходит.
86
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
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
87
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
исходном документе, а обращаться к ним можно из других связанных
документов. Таким образом, связывание позволяет при работе с одним
источником данных использовать возможности разных приложений.
Предположим, вы работаете с данными в 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).
Рис. 7.3. Перенос данных в Access
88
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
С помощью этих кнопок можно повторно вызывать созданные в 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.
Для установки связи базы данных с внешней таблицей необходимо
выполнить команду меню Файл
Внешние данные
Связь с таблицами.
Запустится мастер установки связи, шаги которого аналогичны действиям
мастера импорта. В результате будет установлена динамическая связь с
данными из Excel, а в списке таблиц в окне базы данных Access появится новая
таблица, помеченная специальной пиктограммой в виде стрелочки, которая
обозначает, что данная таблица является связанной (рис. 7.5).
89
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Рис. 7.5. Подтверждение связи с электронной таблицей
После установки связи данные, хранящиеся в связанной таблице, можно
обрабатывать как в Excel, так и в базе данных Access.
Если в дальнейшем файл — источник данных будет переименован или
перемещен в другую папку, то связь будет нарушена. Для восстановления связи
нужно будет указать новое местоположение исходного файла с помощью
команды меню Сервис
Служебные программы
Диспетчер связанных
таблиц.
7.3. Экспорт документов в HTML-формат
Чтобы быстро подготовить информацию из офисных программ для
публикации в Web, не нужно знать тэги языка HTML – достаточно сохранить ее
в формате гипертекстового документа. Для этой цели в Excel добавлена
специальная команда меню Файл Сохранить как Web-страницу.
После выполнения этой команды откроется окно «Сохранение документа», в
котором можно изменить заголовок документа (кнопка «Изменить»), а также
параметры HTML-документа (кнопка «Сервис» Параметры Web-документа)
(рис. 7.6). Затем нажмите кнопку «Сохранить», и вы получите готовую Webстраничку.
Рис. 7.6. Сохранение документа как веб-страница
При сохранении документа в формате HTML неизбежно утрачиваются
некоторые элементы форматирования, поэтому, прежде чем выполнять экспорт,
воспользуйтесь командой Файл
Предварительный просмотр Webдокумента для оценки результата.
Полученный в результате Web-документ можно будет разместить в сети
Internet и просматривать с помощью программ-браузеров.
Вставка таблицы в сообщение программы Microsoft Outlook
Если требуется отображать содержимое в сообщении, используя формат
строк и столбцов, можно вставить таблицу непосредственно в сообщение.
90
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Необходимо использовать 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?
91
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
8. ИНТЕРНЕТ - ТЕХНОЛОГИИ В ЭЛЕКТРОННОЙ ТАБЛИЦЕ
8.1. Маршрутизация сообщения
Продукты электронных таблиц поддерживают функции электронного
документооборота, в частности маршрутизацию. Предположим, что требуется
создать шаблон сводки о деятельности фирмы за определенный период, после
чего этот шаблон должны по очереди заполнить представители всех отделов, а
затем его необходимо передать директору. При решении такой задачи можно
использовать средства Microsoft Excel.
План выполнения задачи включает следующие пункты: создание шаблона,
определение списка рассылки, отправка рабочей книги, содержащей шаблон, по
электронной почте. Допустим, что первый пункт плана вами уже выполнен.
Теперь необходимо задать список рассылки.
Маршрутизация сообщения: определение списка рассылки
Команда По маршруту предоставляет больше возможностей по сравнению с
командой Сообщение, т.к. позволяет задать последовательный маршрут.
Другими словами, если вы создадите список получателей, используя кнопку
Адрес(Address), и установите переключатель По очереди(One After Another),
книга будет отправлена первому получателю из списка. Когда этот член группы
ответит, книга автоматически будет передана следующему адресату из списка.
Список Кому (То) определяет последовательность рассылки, которую вы
можете изменить, выделив имя в списке и нажав одну из кнопок Порядок
(Моvе). В качестве альтернативы можно выбрать одновременную рассылку
книги всем получателям, установив переключатель Всем сразу (Аll At Once).
Чтобы отправить сообщение по назначенному маршруту, нажмите кнопку
Отправить (Route). Если вам нужно просто присоединить маршрутный лист к
книге, нажмите кнопку Добавить маршрут (Add Slip). При использовании
последнего способа вы можете продолжить работу с книгой. Когда вы будете
готовы послать сообщение, выберите команду Следующий адресат (Next
Routing Recipient) (которая заменяет собой команду Сообщение, если открыта
рассылаемая книга) в подчиненном меню Отправить.
8.2. Работа с файлами в сети
Сохранение и открытие файлов на удаленном компьютере
Используя раскрывающийся список Папка в окне диалога Сохранение
документа, вы можете сохранить книгу на любом доступном сетевом
устройстве, узле FTP или папке Web в Интернете.
Совместное использование книг в сети
Excel позволяет нескольким пользователям работать с одной и той же книгой
одновременно благодаря команде Доступ к книге меню Сервис.
Если на вкладке Правка установить флажок Разрешить совместный доступ и
нажать кнопку ОК откроется окно диалога Сохранение документа,
предлагающее сохранить книгу. Это действительно необходимо, т.к. книга в
этом случае должна быть сохранена как «общая» прежде, чем другой
92
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
пользователь сможет открыть ее. После такого сохранения в строке заголовка
окна будет выводиться в квадратных скобках слово [Общий] при всех
последующих, открытиях книги до тех пор, пока она снова не будет сохранена
при снятом флажке Разрешить совместный доступ.
Чтобы задать имя, которое будут видеть другие пользователи при работе с
общей книгой, в меню Сервис выберите команду Параметры и затем на вкладке
Общие открывшегося окна диалога в поле Имя пользователя введите имя.
Можно увидеть имена всех пользователей, открывших книгу на данный момент
времени, выбрав команду Доступ к книге и просмотрев список Файл открыт
следующими пользователями.
Если необходимо отключить, некоторого пользователя от общей книги в
текущем сеансе работы, выделите его в списке и нажмите кнопку Удалить. Но
этот пользователь может снова вернуться к работе с общей книгой в текущем
сеансе, просто открыв книгу еще раз.
При сохранении общей книги Excel проверяет все изменения и определяет,
нужно ли «посредничество» для разрешения конфликтов. В большинстве
случаев окно диалога, появляющееся после сохранения, просто информирует,
что изменения, сделанные другими пользователями, объединены с вашими
правками. Однако если чужие исправления касаются тех же самых ячеек,
которые изменяли вы, появляется «посредник» в виде окна диалога Возник
конфликт доступа.
Для каждого обнаруженного конфликта в окне диалога Возник конфликт
доступа указываются ячейки, измененные двумя или несколькими
пользователями, и, необходимо решить, чьи правки оставить. Можно
разрешить конфликты индивидуально, либо, нажав соответствующую кнопку
внизу окна диалога, отдать предпочтение чужим исправлениям или оставить
только свои изменения.
Также для более корректной работы с книгами, к которым разрешен общий
доступ, появляется возможность изменять некоторые аспекты поведения общих
книг. Для этого в меню Сервис выберите команду Доступ к книге и затем в
окне диалога Управление доступом к файлу перейдите на вкладку Подробнее.
Каждый пользователь общей книги может устанавливать свои параметры
режима совместного доступа.
Просмотр изменений
Можно в любое время просмотреть все изменения, сделанные в общей книге,
при условии, что при первом сохранении книги для общего пользования в окне
диалога Исправления был установлен флажок Отслеживать исправления (книга
становится общей). Если в меню Сервис выбрать команду Исправления и затем
— Принять/отклонить исправления, то откроется окно диалога Просмотр
исправлений.
После нажатия кнопки OK появится окно диалога в рабочем листе. Будет
выделено первое изменение, удовлетворяющее условиям, заданным в окне
диалога Просмотр исправлений. Это окно диалога описывает изменение, а
также показывает, кем и когда это изменение было сделано. Можно принять
или отвергнуть текущее изменение, либо принять или отвергнуть сразу все
93
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
изменения. Принятые или отвергнутые изменения исключаются из списка и не
могут быть просмотрены снова, но можно просмотреть журнал изменений.
Собрания и обсуждения по сети
Подчиненное меню Совместная работа в меню Сервис содержит команды,
обеспечивающие совместную работу с информацией по сети. Это команды
Начать собрание, Назначить собрание и Web-обсуждения, и всем им для своей
работы требуются дополнительные подключения и интерактивные службы.
Чтобы начать собрание по сети в меню Сервис, выберите команду
Совместная работа и затем — Начать собрание. Excel выведет на экран окно
диалога Microsoft NetMeeting. Затем необходимо задать сервер каталогов,
предоставляющий список адресов электронной почты, с которым можно
соединиться, узнав имя локального сервера каталогов или общедоступные
серверы каталогов в Интернете.
Команда Назначить собрание позволяет заранее запланировать собрание с
несколькими участниками, используя для этого соответствующие средства
Microsoft Outlook. При этом это приложение должно быть установлено в
системе основного пользователя, а также на компьютерах членов рабочей
группы.
После подсоединения можно проводить собрание по сети в реальном
времени. Кроме того, можно использовать встроенные инструменты
NetMeeting, позволяющие создавать эскизы и транслировать их на «доски» всех
участников собрания. Проводить собрание можно как из Excel, так и из
Microsoft NetMeeting. Web-обсуждения подобны другим интерактивным
«разговорным» системам с тем отличием, что комментарии каждого участника
отображаются в специальном окне в Excel, браузере или другом приложении
Microsoft Office.
О порядке заполнения полей этого окна мы расскажем ниже, а пока отметим особенности)
Вопросы для самоконтроля
1.
2.
3.
4.
5.
6.
Что такое интернет-технологии применительно к электронным таблицам?
Как отправить документ по определенному списку рассылки?
Что такое список рассылки?
Работа с файлами в сети?
Что такое собрание в сети?
Удаленный доступ к электронной таблице?
94
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
ЗАДАНИЯ НА ЗАКРЕПЛЕНИЕ ЗНАНИЙ ПО ТЕХНОЛОГИЯМ
ЭЛЕКТРОННЫХ ТАБЛИЦ
Задание 1. Форматирование таблицы, ввод и редактирование данных,
применение простых формул в Microsoft Excel
Цель работы: ознакомиться с основными возможностями работы в
электронной таблице, на примере, MS Excel. Произвести ввод, форматирование
и редактирование данных. Ознакомиться с надстройкой электронной таблицы.
Задание на выполнение работы
1. Начиная с клетки А1, создайте электронную таблицу по образцу:
Таблица 1. Исходные данные для выполнения задания
Сначала заполните строки 1, 2 и колонку А. Все заголовки и фамилии вводите с
первой позиции клетки. Колонку «Зарплата» заполните значениями в пределах
от 5000 руб. до 20000 руб.
2. При заполнении клеток СЗ, D3 используются формулы:
СЗ = ВЗ * С$2
DЗ = ВЗ - СЗ
3. Диапазоны клеток СЗ:C9 и D3:D9 заполняются путем копирования
соответствующих формул. Для этого нужно выделить блок ячеек и вызвать
операцию Правка — Заполнить — Вниз. Или с использованием мыши путем
«протаскивания».
4. Просмотрите формулы для всех сотрудников. Обратите внимание на
автоматическое изменение некоторых адресов ячеек. Адрес какой ячейки не
изменился? Почему?
5. В ячейке B11 рассчитайте общую сумму заработной платы по всем
работникам. В клетки С11, D11 эта формула копируется.
6. Произведите копирование полученной таблицы на следующий лист в ячейку
А1. Установите налог 15%. Сравните полученные итоговые данные с
предыдущими значениями.
7. На первоначальном листе (где налог 12%) вставьте новые графы «Премия» и
«Всего начислено» после графы «Зарплата». Самостоятельно задайте формулы
для их вычисления, исходя из того, что премия составляет 3% от заработной
95
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
платы, а «Всего начислено» — это «Зарплата» + «Премия». Отредактируйте все
остальные формулы, руководствуясь задачей.
8. Удалите одну строку из таблицы (сотрудник уволен). Проверьте формулы
итоговой строки. Что изменилось в формуле?
9. Дополните таблицу новыми записями в зависимости от числа человек в
подгруппе, включив их, например, между 5 и 6 строками (приняты новые
сотрудники). Заполните эти строки. Фамилии должны совпадать с фамилиями
студентов в подгруппе, зарплата любая в указанном диапазоне, формулы скопируйте.
10. Вставьте перед колонкой «Налог» еще две колонки «Пенсионный фонд» и
«Налогооблагаемая база». Установите, что в пенсионный фонд удерживается в
размере 1% от начисленной зарплаты и премии. Отчисления в пенсионный
фонд не входят в налогооблагаемую базу, т.е. «Налогооблагаемая база»
вычисляется как «Зарплата» + «Премия» - «Пенсионный фонд». Внесите все
необходимые изменения в формулы.
11. Измените алгоритм расчета подоходного налога (графа «Налог») с учетом
прогрессивной шкалы налогообложения. Если налогооблагаемая база меньше
12000, то принимается ставка 12%, если больше — 20%. Формула должна
использовать функцию ЕСЛИ. Как изменится формула, если задать диапазоны
изменения налога?
12. Проанализируйте полученные результаты.
13. Вставьте перед колонкой «Фамилия» новую графу «Табельный номер» и
заполните ее значениями: 100,101,102 и т.д. (Правка — Заполнить —
Прогрессия).
14. Под строкой «Итого» вставьте еще две строки для вычисления среднего и
максимального значения начисленной и выданной зарплаты. Для этого
воспользуйтесь встроенными статистическими функциями МАКС и СРЗНАЧ,
укажите диапазон для работы этих функций.
15. Задайте следующие имена для диапазонов ячеек (Вставка – Имя –
Присвоить):
Зарплата – для столбца с начисленными зарплатами;
Премия – для столбца с премиями;
Налог – для столбца с налогами;
Пенсионный фонд – для столбца с отчислениями в пенсионный фонд.
16. Выделите всю таблицу и выполните команду Вставка - Имя – Применить.
Укажите: Применить все имена из списка. Проверьте изменения в формулах.
17. Вместо адресов ячеек в формулах должны появиться имена диапазонов,
например, формулы в столбце «Всего Начислено» должны иметь вид: Зарплата
+ Премия.
18. Произведите защиту листа с учѐтом того, чтобы данные по «Зарплате» и
величину ставки налогообложения (ячейка С2) можно было изменить, все
остальные ячейки книги должны быть заблокированы для изменения.
96
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Контрольные вопросы
1. Состав электронной таблицы.
2. Понятие форматирование электронной таблицы.
3. Как изменить размер ячейки?
4. Для чего необходима функция Автоподбор?
5. Как писать наклонным шрифтом?
6. Как осуществить защиту ячеек?
7. Какие типы данных используются в Excel?
8. Вставка нескольких строк и столбцов.
9. Возможность Перетаскивания.
10. Команда Специальная вставка.
11. Что такое Формула?
12. Назначение и элементы формул.
13. Какие операторы сравнения и связи поддерживает Excel?
14. Функция Автосуммирование.
15. Абсолютные, относительные, смешанные ссылки.
16. Тиражирование формул при помощи маркера заполнения.
Задание 2. Работа со списками и базами данных
Цель работы: ознакомиться с основными возможностями работы с базой
данных в электронной таблице, на примере, MS Excel. Рассмотреть проверку
условий при помощи логических функций. Произвести импортирование
данных с возможностью отбора необходимых данных. Рассмотреть работу со
сводными таблицами. Ознакомиться с гиперссылками в электронной таблице.
Задание на выполнение работы
Работа выполняется в соответствии с вариантом, представленном в таблице
1.
№
вар.
1
2
3
4
5
6
7
8
9
0
Задание 1, выбирается номер листа в
соответствующем приложении
1
2
3
4
5
6
7
8
9
10
Таблица 1
Задание 2, критерий
импорта
0.07
0
0.11
0.32
0.21
0.39
0.42
0.29
0.28
0.22
97
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Задание 1. Использование функций
Используя Приложение 1 к лабораторной работе, с учетом варианта (таблица
1) выполните:
1. Рассчитайте сумму выручки от аренды каналов за 2005-2008 гг.
2. Рассчитайте проценты к общей выручке, используя абсолютную адресацию
(абсолютная адресация может быть создана, выделив ячейку и нажав клавишу
F4).
3. Присвоить ячейке G8 имя «Всего» и рассчитать проценты, используя
присвоенное имя.
4. Сгруппируйте данные по строкам и столбцам для просмотра строк и
столбцов «Итого» и «Проценты».
5. При помощи панели инструментов Зависимости установите Влияющие и
Зависимые ячейки.
6. Определите среднее значение, максимальное и минимальное значения
выручки от аренды каналов за 2005-2008 гг.
7. Определите величину «Налога» в размере 8,5 %, если сумма по каждому из
пунктов выручки превышает 3000, иначе налог равен 0. Для вычисления
используйте функцию ЕСЛИ.
8. Определите величину «Комиссия» при условии: выдается сообщение
«Комиссия не начисляется», если сумма по каждому пункту выручки меньше
2000 или величина процента меньше 25%; взимается комиссия в 15% от итого,
если пункт итого изменяется от 2000 – 2500 включительно; взимается комиссия
в 17,5% от итого, если пункт итого изменяется от 2500 – 3000; взимается
комиссия в 20% от итого, если пункт итого больше 3000 (включительно).
9. Постройте внедренную гистограмму, отражающую итоговую выручку от
аренды каналов за 2005-2008 гг.
10. Постройте лепестковую диаграмму, выполненную в виде отдельного листа,
отражающую изменение выручки от аренды каналов за 2005-2008 гг. по
месяцам.
11. Постройте внедренную круговую диаграмму, отражающую процентную
долю выручки от аренды каналов за 2005-2008 гг. без легенды, но с подписями
данных.
В гистограммах/диаграммах все подписи должны быть представлены в
соответствии с данными.
Задание 2. Работа c базой данных и списками
2.1. Импорт данных при помощи Microsoft Query:
а) Импортируйте в приложение Microsoft Query из базы данных Абоненты.mdb
информацию об абонентах телекоммуникационной компании, из которой
извлеките все столбцы.
б) В области условий создайте критерий отбора записей по сумме согласно
варианту, указанному в таблице 1.
в) Отсортируйте записи таблицы результатов по категории.
98
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
г) Перенесите данные, содержащиеся в таблице результатов, на отдельный лист
рабочей книги Excel.
2.2. Работа с импортированной базой данных
а) Для полученного ранее списка установите автофильтр, с помощью которого
выберите записи с месяцем 1.
б) Найдите среди данных записи с отчеством Андреевич и Александровна.
в) Отмените критерии, заданные в предыдущих пунктах, и создайте фильтр,
отбирающий записи об абонентах с полом 1, месяца которых приходятся на
январь и июнь одновременно.
г) Применив расширенный фильтр, отберите записи об абонентах вне
исходных данных, которые живут на Улице и в квартире № (укажите
самостоятельно).
д) При помощи диалогового окна формы данных добавьте новые записи о
себе.
Задание 3. Исследование данных при помощи сводных таблиц
а) На основе импортированных данных, полученных в п. 2.1 создайте сводную
таблицу, отражающую количество жителей проживающих на определенной
улице в определенном доме. В строках таблицы закладывается название
улицы, в столбцах – номер дома, в данных – номер квартиры. В «параметре
поля» укажите «количество чисел».
б) При помощи автофильтра в исходной таблице и созданной сводной таблицы
сопоставьте: количество человек, проживающих на улице Мяги; количество
человек проживающих на улице Физкультурная. В сводной таблице
отразить фамилии этих людей.
в) Найти в сводной таблице, кто из абонентов проживает в домах 17, 37, 75.
Отразить фамилии этих абонентов и улицу, на которой они проживают.
г) По сводной таблице построить сводную диаграмму, отражающую те же
данные. Уметь ориентироваться в сводной диаграмме.
Задание 4. Создание гиперссылок
а) Создайте в книге, содержащей импортированную базу данных, новый лист.
б) на данном листе создайте следующие гиперссылки:
1) гиперссылка на выполненное Задание №1 (п 1), находящееся в другой
книге, с названием «Использование функций»;
2) гиперссылка на импортированную в соответствии с вариантом базу
данных, находящуюся в этой же книге, но на другом листе (задание 2.1);
3) гиперссылка на данные, отфильтрованные при помощи расширенного
фильтра (задание 2.2.г);
4) гиперссылка на полученную сводную таблицу (задание 3);
5) гиперссылки на веб-страницы ПГУТИ (главная страница); кафедры ИСТ;
Студенческий ИТ-Клуб.
99
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Контрольные вопросы
1. Состав электронной таблицы.
2. Как осуществить защиту ячеек?
3. Понятие форматирование электронной таблицы.
4. Что такое иерархическая структура таблицы?
5. Назначение и элементы формул.
6. Виды адресных операторов.
7. Виды адресации.
8. Назначение функции ЕСЛИ.
9. Назначение приложения Microsoft Query.
10. При помощи чего осуществляется доступ к внешним данным?
11. Что такое список?
12. Виды фильтрации списка.
13. Для чего необходимо диалоговое окно Форма данных?
14. Виды диаграмм.
15. Сводные таблицы. Сводные диаграммы.
16. Создание и использование гиперссылок.
Задание 3. Анализ данных средствами Microsoft Excel
Цель работы: ознакомиться с возможностью решения оптимизационных
задач в электронной таблице на примере MS Excel. Рассмотреть такие функции
электронных таблиц как: таблица подстановки, подбор параметры, сценарии.
Произвести процесс поиска решения для данных, представленных в задании,
который позволяет найти наиболее подходящее решение в соответствии с
заданными критериями.
Задание на выполнение работы
Работа выполняется в соответствии с вариантом, представленном в таблице 1.
Таблица 1
№
X N
D
Н
Задание 2.1
Условия задания 2.2
вар.
1 12
5
1,4
1,12
Пр.1
A
2 11
4
1,5
1,13
Пр.2
B
3 10
5
1,6
0,99
Пр.4
С
4
9
6
1,2
1,22
Пр.5
B
5
8
6
1,3
1,45
Пр.1
C
6
7
7
1,7
1,5
Пр.2
A
7
6
8
1,8
1,33
Пр.4
C
8
5
5
1,4
1
Пр.5
B
9
4
9
1,3
1,222
Пр.1
A
0
3
10
1,5
0,998
Пр.2
B
100
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Задание 1. Прогнозирование результатов на основе подбора параметра
и таблицы подстановки
1) Согласно варианту (таблица 1) путем Подбора параметра определите:
1.1.какую сумму надо положить в банк под X% годовых, чтобы через N лет
иметь вклад 100000 руб. Для расчета используйте функцию БС().
1.2.величину ежемесячной выплаты займа в 100000 руб., если он взят при
процентной ставке, равной (X+3)% в год. Для расчета используйте
функцию ПЛТ(). Осуществите подбор такого количества ежемесячных
выплат, при котором каждая выплата составит 3500 руб.
2) Для задачи 1.2 составьте таблицу подстановки с одной переменной и
установите с ее помощью, как различные процентные ставки влияют на
величину выплат процентов по кредиту (не менее 10 значений, включая
заданное).
3) Для той же задачи 1.2 составьте таблицу подстановки с двумя
переменными и рассчитайте с ее помощью варианты с различными
процентными ставками и сроками платежей (значения ставки должны быть
такими же, как и в п. 2; сроки платежей от 10 до 50 лет, включая подобранное
значение в п. 1.2).
4) Создайте сценарий в виде структуры для сравнения ежемесячных выплат
по займу (задания 1.2). В отчете структуры одновременно должны быть
отражены не менее 5 значений ежемесячных выплат для разных сроков
кредита.
5) В таблице 2 приведены бюджетные назначения некоторой организации. В
соответствии с этими данными постройте гистограмму, выполненную в виде
отдельного листа. В этой диаграмме при помощи процедуры Подбора
параметра подберите значение в D млн. руб., изменяя значение рекламы.
Таблица 2
Зар/плата
Коммунальные расходы
Офисные расходы
Командировки
Представит расходы
Телефон
Обучение
Реклама
Аренда
Инвестиции
Итого
433100
14150
2480
13650
1675
5100
49000
600000
450845
58000
101
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Задание 2. Поиск решения
1) Для выполнения данного задания необходимо воспользоваться файлом
[Основная папка MS Office]\Office\Samples\Solvsamp.xls. В данной книге
выберите задание в соответствии с вариантом (Таблица 1).
1.1. Разберитесь, как с помощью функции Поиска решения можно найти
оптимальное значение для решения поставленной задачи. По какому
принципу заданы ограничения.
1.2. Создайте отчет о результатах поиска решения.
1.3. Измените, исходные значения параметров поиска решения (например,
относительную погрешность) и создайте отчет с новыми результатами.
2) На новом листе создайте таблицу 2. Известно организация не может
позволить себе тратить в год свыше Н млн. руб. При помощи Поиска решения
подберите такие значения офисных расходов, расходов на зар./плату, аренду и
рекламу, чтобы сумма всех расходов была равна Н млн. руб., в соответствии со
следующими условиями (выбираются по варианту):
A – Зар./плата = 450 000; офисные расходы: от 15 000 до 40 000; аренда =
зар./плате; затраты на рекламу должны быть больше офисных расходов, но
меньше аренды.
B – Офисные расходы = 35 000; зар./плата = 500 000; затраты на рекламу
должны быть больше офисных расходов, но меньше зар./платы; аренда не
больше 500 000.
C – Аренда не больше 450 000; офисные расходы не больше 35 000; зар./плата =
450 000; затраты на рекламу должны быть больше затрат на телефон, но
меньше затрат на аренду.
Контрольные вопросы
1. В чем суть метода подбора параметра?
2. Как осуществить подбор параметра при работе с диаграммой?
3. Функция БС()?
4. Функция ПЛТ()?
5. Что такое таблица подстановки?
6. Виды таблицы подстановки.
7. Из чего формируются таблицы подстановки?
8. Назначение сценариев.
9. Как наложить ограничения на Поиск решения?
10. Цели использования процедуры Поиска решения?
11. Что такое сходимость применительно к Поиску решения?
12. Параметры программы Поиск решения? Виды моделей.
102
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Задание 4. Создание элементов управления на рабочем листе. Работа с
макросами
Цель работы: ознакомиться с возможностью создания макропрограмм в
электронной таблице на примере, MS Excel. Рассмотреть особенности
программы Visual Basic for Applications (VBA), которая позволяет решать
пользовательские задачи.
Задание на выполнение работы
Работа выполняется в соответствии с вариантом, представленном в таблице 1:
Таблица 1
№
А
Б
В
Г
Д
Формула
Вар
1
Times
Зеленый
начертание
Центр
14 a=b+c*2
New
Roman
2
Arial
Черный
Начертание Левый край
12 f=n-m-2
3
Monotype Красный
начертание
Центр
10 a=b*c
Corsiva
4
Elephant
Желтый
Начертание
Правый 14 c=cos(a*v)
край
5
Script MT Черный
Начертание Левый край
13 n=sin(2*a)/
Bold
b
6
Forte
Лиловый
Начертание Левый край
12 m=a-b+c
7
Georgia
Синий
Начертание
Центр
11 n=2*a
8
Calisto
Серый
Начертание
Правый 13 m=6+b-c
MT
край
9
Engavers
Коричневый Начертание
Центр
16 f=z*9
MT
0
Impact
Оливковый
начертание Левый край
14 n=a+b*x
Задание 1.
Создание макроса, используя макрорекордер
Создайте таблицу (рис. 1), для которой с помощью макрорекордера запишите
макрос, выполняющий:
1. форматирование фрагмента текста следующим образом: шрифт – А, цвет
текста – Б, начертание – В, выравнивание – Г. [Значение А, Б, В, Г согласно
варианту из таблицы 1];
2. следующие действия: отменить отображение сетки, задать для всех ячеек
денежный формат, установить вид и размер шрифта текста ячеек (Д);
3. следующие действия: отображение влияющих ячеек при помощи Панели
зависимости для строки Сумма; вставка примечания для каждого из пункта
Суммы, например, «Сумма прибыли по месяцу Январь», выделение строки
Суммы цветом Б (согласно варианту);
4. все действия пп. 2.1.1-2.1.3 одновременно.
103
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
5. Ознакомьтесь с тестом макроса в редакторе Visual Basic. Активизируйте
режим отладки и выполните пошаговое выполнение макроса. При
необходимости установите точку остановки.
6. Создайте новую панель инструментов. Поместите на эту панель кнопки
для запуска созданных с помощью макрорекордера макросов. Кнопки должна
иметь запись, информирующую об их назначении.
Рис. 1. Исходные данные для п. 1 задания1
Задание 2. Создание макросов, используя редактор VBA
1. В окне VBA создайте процедуру, выступающую в качестве макроса.
Данный макрос должен производить запрос данных (используя функцию
InputBox() ) и пересчет данных при помощи созданной формулы (таблица 1).
Вывод результата необходимо осуществить в ячейку An, где n – номер варианта
(Range() ) и в диалоговое окно (MsgBox() ).
2. Создайте макрос аналогичный предыдущему (п. 2.2.1), но с учетом того,
что данные вводятся не через функцию InputBox(), а непосредственно в
некоторые ячейки таблицы Excel, например, столбца В. Процедура должна
считать значения переменных из этих ячеек, и вывести результат пересчета по
формуле в ячейку Bn+1 и в диалоговое окно (MsgBox() ).
3. Создайте макрос в VBA, при котором: вводятся три произвольных числа.
Затем задаѐтся вопрос «Вы уверены, что хотите их сложить?» и варианты
ответов: «да», «нет», «отмена». Если ответ «да» – то числа складываются; если
«нет» – числа перемножаются; если «отмена», то выдается сообщение
«Действие прервано пользователем».
4. Самостоятельно сформулируйте задание для создания макроса, используя
функции If, InputBox, MsgBox (с реакцией последействия) (макросы не должны
повторяться).
5. Добавьте полученные макросы (пп. 1-4 задания 4.2) на созданную в п. 6
(задания 4.1) панель инструментов.
Контрольные вопросы
1. Что такое макрос?
2. К какому классу языков относится VBA?
3. Чем характеризуются объекты в VBA?
4. Что такое макрорекордер? Чем отличается создание макроса через
макрорекордер от написания макроса в VBA?
104
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
5. Как можно отредактировать уже созданный макрос? Как осуществить
пошаговую отладку макроса? Что такое точка остановки?
6. Структура, используемая в VBA.
7. Что такое модуль?
8. Синтаксис определения процедуры.
9. Синтаксис оператора описания переменной.
10. Области видимости переменной.
11. Методы просмотра результатов в VBA.
12. Функции MsgBox и InputBox.
Задание 5. Основы программирования на языке Visual Basic
Цель работы: ознакомиться с возможностью создания макропрограмм в
редакторе VBA, на примере, MS Excel. Получить практические навыки
программирования индивидуальных пользовательских задач в электронных
таблицах.
Задание на выполнение работы
В окне кода VBA создайте следующие программы:
Задание 1:
Через макрос создайте команду, которая будет проверять введенное через
InputBox число на положительное и отрицательное с выводом окна сообщения
и этого числа в какую-либо ячейку. Ячейку также отформатировать: придать ей
рамку и цвет.
Задание 2:
Используя элемент управления «Кнопка» и управляющую конструкцию
(оператор ElseIf), выполните следующее:
В компьютер вводится целое число a
1. Если a<0 то компьютер должен сказать: ―число отрицательное‖
2. Если a=0 то: ―вы ввели нуль‖
3. Если a>100 то компьютер должен сказать: ―число большое‖
4. В остальных случаях компьютер ничего не должен говорить, а только
вычислить и напечатать в какой-нибудь ячейке квадрат этого числа.
Задание 3:
Создать кнопку, по которой в компьютер вводятся данные о цвете глаз и
росте человека. Если рост < 180 см, а глаза – голубые, выдать сообщение.
Примечание: для обозначения цвета глаз тип переменной String, для
объединения данных оператор AND.
Задание 4:
105
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Компьютер спрашивает: «Какую отметку ты получил?» и реагирует на неѐ
следующим восклицаниями:
Если 1,2 – выдается текст с предупреждением (vbCritical) «Кошмар»
Если 3 – «Неважно»
Если 4 – «Неплохо»
Если 5 – «Почти гений» и восклицательный знак (vbInformation)
Иначе «Таких отметок не бывает» и переход к первому вопросу «Какую
отметку ты получил?».
Выполняется в виде кнопки. (Используется оператор Goto)
Задание 5:
Необходимо напечатать слово «Привет» 10 раз (Использовать циклическую
структуру и оператор For).
Задание 6:
Создайте кнопку на рабочем листе, при нажатии которой происходит
пересчет итоговой суммы (Таблица 1). Кроме того, после пересчета суммы
должен задаваться вопрос: "Хотите ли выделить итоговую сумму?" с
вопросительным уведомлением и кнопками «Да», «Нет». Если «Да», то
происходит форматирование ячейки итоговой суммы (окрашивается в желтый
цвет, выделяется жирным), Если «Нет» - никаких действий по форматированию
не производится.
На этом же листе создать кнопку, отменяющую все действия.
Таблица 1
наименование
товара
стоимость количество сумма
стол
1000
1
стул
200
15
диван
3000
1
кресло
800
2
софа
2800
0
тумба
1500
2
кровать
5000
0
Итого
Задание 7:
Создайте пользовательскую форму, в которой будет производится расчет по
трем вводимым в нее переменных. Расчет производится по одному из 4-х
действий: сложение, вычитание, умножение, деление. Действие выбирается
через один из переключателей. Результат должен выводится в этой же форме,
после нажатия кнопки "Вычислить".
106
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Форма должна вызываться из приложения VBA через кнопку на рабочем
листе.
Задание 8:
Постройте простой список с использованием пользовательского диалога
вида:
ПОЛЬЗОВАТЕЛЬСКИЙ ДИАЛОГ СОЗДАНИЯ СПИСКА
_
X
Список группы
Фамилия
Имя
Отчество
Ввести
Очистить
Окончить
Для перехода в другое поле Tab
Ввод записи в список осуществляется щелчком на кнопке Ввести после
заполнения соответствующих полей (Фамилия, Имя и Отчество); перед
набором следующей записи щелкнуть на кнопке Очистить; для завершения
ввода записей щелкнуть на кнопке Окончить. Результаты ввода данных
должны быть поочередно помещены на рабочий лист в одноименные поля.
Контрольные вопросы
1. Что такое макрос?
2. К какому классу языков относится VBA?
3. Чем характеризуются объекты в VBA?
4. Что такое управляющая конструкция?
5. Виды управляющих конструкций.
6. Как происходит создание элемента управления на рабочем листе?
7. Ветвление.
8. Циклы. Циклы со счетчиком.
9. События пользовательской формы.
10. Функции MsgBox и InputBox.
Задание 6. Обмен данными и интернет - технологии в ЭТ
Цель работы: ознакомиться с возможностью по обмену табличными данными
с другими пользовательскими приложениями. Рассмотреть особенности работы
с электронными таблицами на основе интернет - технологий. Получить
практические навыки работы с табличными данными в сети.
107
Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис»
Задание на выполнение работы
1. Создайте рабочую книгу с таблицами и диаграммами (или откройте отчет
по 2 лабораторной работе).
2. Отправьте первый рабочий лист книги как часть сообщения электронной
почты одному адресату.
3. Перешлите эту рабочую книгу как вложение по электронной почте другому
адресату. Укажите получателей для рассылки копий сообщения.
4. Откройте домашнюю страницу веб-узла Microsoft SharePoint Team Services
в окне браузера.
5. Создайте новую библиотеку документов в формате .xls.
6. Находясь в приложении Excel, сохраните в библиотеке документов
созданную в рабочей книги таблицу. Используя область задач Общая
рабочая область, создайте новую задачу для рабочей группы.
Работа в ЭТ облачных технологий
1. При помощи Google документы создайте документ «Таблицы», в котором
выполните все задания лабораторной работы № 1. Обратите внимание на
тот факт, что все функции должны быть представлены в английской
формулировке.
2. Предоставьте доступ нескольким пользователям, совместно с которыми
произведите редактирование информации.
Контрольные вопросы
Что такое Microsoft SharePoint Team Services?
Что такое гиперссылка?
Чем характеризуются маршрутизация в ЭТ?
Что такое Журнал изменений?
В чем основное достоинство Google документы? Приведите его
альтернативы.
6. Как происходит коллективная работа в ЭТ?
1.
2.
3.
4.
5.
108
Документ
Категория
Без категории
Просмотров
69
Размер файла
3 215 Кб
Теги
8860, технология, 1856, таблица, электронные
1/--страниц
Пожаловаться на содержимое документа