12. Дайте команду Формат > Выделенный ряд. Откройте вкладку Вид.

13. На панели Линия откройте палитру Цвет и выберите красный цвет. В списке Тип линии выберите пунктир.

14. На панели Маркер выберите в списке Тип маркера треугольный маркер. В палитрах Цвет и Фон выберите зеленый цвет.

15. Щелкните на кнопке ОК, снимите выделение с ряда данных и посмотрите, как изменился вид графика.

16. Сохраните рабочую книгу.

2.1 Построение графиков функций

y=x^2/8, y=sin(x), y=ln(1+x).

Построим их на одном рисунке (рис. 8).

Рис. 8 Графики элементарных функций.

2.2. Решить задачу о падении снарядов

Задача

Снаряд

Даны уравнения движения снаряда

Х=Vo*Cos(Alfa)*t; Y=Vo*Sin(Alfa)*t

где V-начальная скорость снаряда, V=50

alfa-угол между вектором V и линией горизонта

Требуется

1 Определить координаты снаряда и построить его траекторию с учетом того, что координаты неотрицательны

2 Изменяя значения угла alfa определить максимальную дальность полета, максимальную высоту полета, угол, при котором снаряд попадет в точку с координатой 100

Вид формул таблицы

Начальная скорость	v	50
Угол		Alfa	1,1	рад
t	X	Y расч	Y
0	=v*COS(alfa)*t	=v*SIN(alfa)*t-9,81*t^2/2	=ЕСЛИ(C4>0;C4;0)
0,5	=v*COS(alfa)*t	=v*SIN(alfa)*t-9,81*t^2/2	=ЕСЛИ(C5>0;C5;0)
9,5	=v*COS(alfa)*t	=v*SIN(alfa)*t-9,81*t^2/2	=ЕСЛИ(C23>0;C23;0)
10	=v*COS(alfa)*t	=v*SIN(alfa)*t-9,81*t^2/2	=ЕСЛИ(C24>0;C24;0)



3. Содержание отчета

Отчет по работе должен содержать:

3.1. Цель работы;

3.2. Краткие теоретические сведения;

3.3. Постановку задачи и исходные данные;

3.4. Результаты работы;

3.5. Выводы по работе

4. Контрольные вопросы

4.1 Как построить диаграмму?

4.2 Как задать диапазон данных?

4.3 Как редактировать легенду диаграмм?

Лабораторная работа № 14

Электронная таблица excel. Создание баз данных. Форма. Фильтр. Условия отбора

ЦЕЛЬ РАБОТЫ: Изучение принципов работы с базами данных в электронной таблице EXCEL.

Объем работы 2 часа.

1. Методические указания

1.1 База данных

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

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

После ввода строки с именами полей можно начать ввод информации в базу данных, размещая её под соответствующими именами полей. Каждая колонка в базе данных содержит информацию определенного типа. Столбец известен в базах данных под именем ПОЛЕ. Данные, записанные в строках, называют записями.

1.2 Форма для данных

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

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

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

После выбора этой команды EXCEL анализирует строку с именами полей и элементов первой записи и создает форму с данными, в которой перечислены все имена полей в левой части и элементы первой записи в текстовых окнах, следом за ними. Форма также имеет ряд командных кнопок в правой части, которые используются для добавления или поиска каких-либо записей. Форма отображает номер текущей записи, за которым следует число, показывающее общее количество записей в базе.

1.3 Создание новых записей

После создания формы данных можно использовать эту форму для ввода последующих записей в базу данных. Процесс прост: при нажатии на кнопку ДОБАВИТЬ высвечиваются чистая форма данных (обозначенная как новая запись), которую нужно заполнить. После ввода значения первого поля нажмите клавишу <Tab> для перехода к следующему полю. При нажатии <Enter> добавляется заполненная запись в базу данных.

Каждому имени поля EXCEL автоматически назначает оперативную клавишу, позволяющую быстро перейти в требуемое поле. Символ в имени поля, соответствующий оперативной клавише, отображается подчеркиванием.

1.4 Сортировка базы данных.

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

Команда Данные/сортировка устанавливает порядок строк в таблице в соответствии с содержимым конкретных столбцов.

Сортировка по возрастанию предполагает следующий порядок:

Числа

Текст, включая текст с числами

Логические значения

Значения ошибок

Пустые ячейки

Сортировка по убыванию происходит в обратном порядке. Исключением являются пустые ячейки, которые всегда располагаются в конце списка.

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

При выборе ДАННЫЕ/СОРТИРОВКА открывается окно диалога "сортировка диапазона", которое позволяет вам указать поля сортировки и определить критерий сортировки. С помощью раскрывающегося списка СОРТИРОВАТЬ ПО вы можете выбрать столбец для сортировки. Порядок сортировки устанавливается переключателями по возрастанию или убыванию.

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

Два дополнительных раздела ЗАТЕМ и В ПОСЛЕДНЮЮ ОЧЕРЕДЬ, позволяет определить порядок вторичной сортировки записей, в которой имеются совпадающие значения.

Окно диалога содержит кнопку ПАРАМЕТРЫ, в результате нажатия которой открывается окно диалога «ПАРАМЕТРЫ СОРТИРОВКИ» с помощью этого окна можно:

Определить пользовательский порядок сортировки для столбца, указанного в раскрывающемся списке СОРТИРОВАТЬ ПО.

Сделать сортировку чувствительной к использованию прописных и строчных букв.

Изменить направление сортировки (вместо сортировки сверху вниз установить сортировку слева на право).

1.5 Фильтрация данных в списке

В EXCEL, списком, называется снабженная метками последовательность строк рабочего листа, содержащих в одинаковых столбцах данные одного типа.

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

В EXCEL для фильтрации данных используются команды АВТОФИЛЬТР и РАСШИРЕННЫЙ ФИЛЬТР. В случае простых критериев для выборке нужной информации достаточно команды автофильтр. При использовании сложных критериев следует применять команду расширенный фильтр.

Обе команды вызываются в результате выбора команды ДAННЫЕ/ФИЛЬТР.

При выборе команды ДАННЫЕ/ФИЛЬТР/АВТОФИЛЬТР EXCEL добавляет раскрывающийся список к каждой клетке с именем поля в строке заголовка.

При фильтрации списка используется два типа критериев: множественные и вычисляемые. Команда РАСШИРЕННЫЙ ФИЛЬТР использует для фильтрации данных интервал критериев. На экран выводятся только те строки, которые удовлетворяют всем критериям, записанным в интервале критериев. После выполнения команды на экране появляется окно диалога «РАСШИРЕННЫЙ ФИЛЬТР», в котором определяются параметры фильтра.

В поле ввода ДИАПАЗОН КРИТЕРИЕВ указывается интервал ячеек на рабочем листе, который содержит ваши критерии. В поле ввода ПОМЕСТИТЬ РЕЗУЛЬТАТ В ДИАПАЗОН указывается интервал ячеек в которые копируются строки. Это поле доступно только тогда, когда выбран переключатель СКОПИРОВАТЬ НА ДРУГОЕ МЕСТО.

2. Порядок выполнения работы

Создать адресно-телефонную книгу по следующим полям:

Фамилия

Имя

Отчество

Год рождения

Улица

Дом

Квартира

Телефон

Внести первую запись по всем полям.

Используя встроенную форму данных внести не менее 20 записей.

Отсортировать базу данных по алфавиту, по году рождения.

Установить автофильтр на поля базы данных и ознакомиться с выборкой по критерию.

Используя команду расширенный фильтр сделать выборку по следующему критерию:

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

3. Отчет по работе

3.1. Цель работы;

3.2. Краткие теоретические сведения;

3.3. Постановку задачи и исходные данные;

3.4. Результаты работы;

3.5. Выводы по работе

4. Контрольные вопросы

4.1. Как упростить работу с базой данных?

4. 2. Как создать форму данных для новой базы?

4.3. Для чего используется сортировка базы данных?

4.5. Какие команды используются для фильтрации данных?

4.6. В каких случаях применяется команда расширенный фильтр?

Лабораторная работа № 15

Электронная таблица excel. Решение уравнений и систем уравнений. Подбор параметра. Поиск решения

Цель работы : Изучить «Поиск решения» и «Подбор параметра» в электронной таблице Excel.

Объем работы 2 часа.

1. Методические указания

1.1 Решение уравнений средствами программы Excel

Задача. Найти решение уравнения

x³ - 3x² + х = -1.

1. Запустите программу Excel (Пуск > Программы > Microsoft Excel) и откройте новую рабочую книгу или созданную ранее.

2. Создайте новый рабочий лист (Вставка > Лист), дважды щелкните на его ярлычке и присвойте ему имя Уравнение.

3. Занесите в ячейку А1 значение 0.

4. Занесите в ячейку В 1 левую часть уравнения, используя в качестве переменной x ссылку на ячейку А1. Соответствующая формула может, например, иметь вид

=А1 ^3-3*A1 ^2+А1.

5. Дайте команду Сервис > Подбор параметра.

6. В поле Установить в ячейке укажите В1, в поле Значение задайте -1, в поле Изменяя значение ячейки укажите А1.

7. Щелкните на кнопке ОК и посмотрите на результат подбора, отображаемый в диалоговом окне Результат подбора параметра. Щелкните на кнопке ОК, чтобы сохранить полученные значения ячеек, участвовавших в операции.

8. Повторите расчет, задавая в ячейке А1 другие начальные значения, например 0,5 или 2. Совпали ли результаты вычислений? Чем можно объяснить различия?

9. Сохраните рабочую книгу под своей фамилией.

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

1.2 Решение задач оптимизации

Задача. Завод производит электронные приборы трех видов (прибор А, прибор В и прибор С), используя при сборке микросхемы трех видов (тип 1, тип 2 и тип 3). Расход микросхем задается следующей таблицей:

	Прибор А	Прибор В	ПриборС
Тип 1	2	5	1
Тип 2	2	0	4
Тип З	2	1	1

Стоимость изготовленных приборов одинакова.

Ежедневно на склад завода поступает 500 микросхем типа 1 и по 400 микросхем типов 2 и 3. Каково оптимальное соотношение дневного производства приборов различного типа, если производственные мощности завода позволяют использовать запас поступивших микросхем полностью?

1. Запустите программу Excel (Пуск > Программы > Microsoft Excel) и откройте новую рабочую книгу или созданную ранее.

2. Создайте новый рабочий лист (Вставка ^ Лист), дважды щелкните на его ярлычке и присвойте ему имя Организация производства.

Рис 1.

3. В ячейки B2, B3 и B4 занесите дневной запас комплектующих -- числа 500,400 и 400, соответственно.

4. В ячейки D5, E5 и F5 занесите нули -- в дальнейшем значения этих ячеек будут подобраны автоматически.

5. В ячейках диапазона D2:F4 разместите таблицу расхода комплектующих.

6. В ячейках A2: A4 нужно указать формулы для расчета расхода комплектующих по типам. В ячейке A2 формула будет иметь вид

=$D$5*D2+$E$5*E2+$F$5*F2,

а остальные формулы можно получить методом автозаполнения (обратите внимание на использование абсолютных и относительных ссылок).

7. В ячейку G5 занесите формулу, вычисляющую общее число произведенных приборов: для этого выделите диапазон D5 :F5 и щелкните на кнопке Автосумма на стандартной панели инструментов.

8. Дайте команду Сервис > Поиск решения -- откроется диалоговое окно Поиск решения.

9. В поле Установить целевую укажите ячейку, содержащую оптимизируемое значение (G5). Установите переключатель Равной максимальному значению (требуется максимальный объем производства).

10. В поле Изменяя ячейки задайте диапазон подбираемых параметров -- D5 :F5.

11. Чтобы определить набор ограничений, щелкните на кнопке Добавить. В диалоговом окне Добавление ограничения в поле Ссылка на ячейку укажите диапазон A2:A4. В качестве условия задайте <=. В поле Ограничение задайте диапазон B2:B4. Это условие указывает, что дневной расход комплектующих не должен превосходить запасов. Щелкните на кнопке ОК.

12. Снова щелкните на кнопке Добавить. В поле Ссылка на ячейку укажите диапазон D5 :F5. В качестве условия задайте >=. В поле Ограничение задайте число 0. Это условие указывает, что число производимых приборов неотрицательно. Щелкните на кнопке ОК.

13. Снова щелкните на кнопке Добавить. В поле Ссылка на ячейку укажите диапазон D5 :F5. В качестве условия выберите пункт цел. Это условие не позволяет производить доли приборов. Щелкните на кнопке ОК.

14. Щелкните на кнопке Выполнить. По завершении оптимизации откроется диалоговое окно Результаты поиска решения.

15. Установите переключатель Сохранить найденное решение, после чего щелкните на кнопке ОК.

16. Проанализируйте полученное решение. Кажется ли оно очевидным? Проверьте его оптимальность, экспериментируя со значениями ячеек D5 :F5. Чтобы восстановить оптимальные значения, можно в любой момент повторить операцию поиска решения.

17. Сохраните рабочую книгу под своей фамилией.

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

3. Порядок выполнения работы

Решить уравнения

1) x³ + 8x - 15=0 4) 3^x - 3 ^x-2 = 72

2) 9*5 ^x+1 - 5^x = 5500 5) x³ + 8x - 9 =0

3) 3 ^3x+1 - 2*3^3x = 27 6) 2x² - 8x+ 8 =0

Решить системы уравнений.

3 x₁ - 5 x₂ =13 3 x₁ - 4x₂ = -6

2 x₁+ 7x₂ =81 3 x₁ + 4x₂ =81

x²+y²=5

x^2-y²=1

x(0;4)



5 x₁ + 8x₂+ x₃=2 2x₁ - 3x₂ + x₃= -7

3x₁ - 2x₂+6x₃= -7 x₁ + 4x₂ + 2x₃= -1

2 x₁ + x₂ - x₃= -5 x₁ - 4x₂ = -5

2 x - 4 y + 9=28 5x₁ + 8x₂ + x₃= 2

7 x+3 y - 6= -1 3x₁ - 2x₂ + 6x₃= -7

7 x+9 y - 9=5 2x₁ + x₂ - x₃= -5

2 x - 7 y + z = - 4 2 x - 4 y + 9 z = 28

3 x + y - z = 17 7 x + 3 y - 6z = - 1

x - y +3z = 3 7x + 9y - 9z = 5

4. Отчет по работе

1. Цель работы;

2. Краткие теоретические сведения;

3. Постановку задачи и исходные данные;

4. Результаты работы;

5. Выводы по работе

5. Контрольные вопросы

1. Как решить уравнение?

2. Как решить систему уравнений?

Лабораторная работа № 16

Электронная таблица excel. Макросы

Цель работы : Научиться работать с макросами в электронной таблице Excel.

Объем работы 2 часа.

1. Методические указания

В качестве первоначального знакомства с VBА попытаемся решить следующую задачу. Допустим, вы решили вести учет своих расходов, и с этой целью в конце каждого месяца намерены составлять таблицу (рис. I) и строить диаграмму для более наглядного отображения доли каждой статьи расходов вашего бюджета. Составлять ежемесячно одну и ту же таблицу с одновременным построением диаграммы довольно непроизводительная трата времени. Более разумно один раз научить компьютер создавать таблицу, а потом по мере необходимости лишь отдавать команду подготовки таблицы, чтобы осталось только внести в нее данные.

Для обучения компьютера отлично подходит MacroRecorder -- транслятор, создающий программу (макрос) на языке VBA, которая является результатом перевода на язык VBA действий пользователя с момента запуска MacroRecorder до окончания записи макроса.

1.1 Активация MacroRecorder

Итак, для активизации MacroRecorder выберите команду Сервис, Макрос, Начать запись. Появится диалоговое окно Запись макроса. Это диалоговое окно позволяет задать параметры макроса.

В диалоговом окне Запись макроса (Record Macro) в поле Имя макроса (Macro Name) введем Расходы, а в поле Описание (Description) -- Расчет месячных расходов

Присвоим макросу комбинацию клавиш быстрого вызова:

сочетание клавиш в расположенном рядом поле введем букву, например, r (вызов макроса будет осуществляться при нажатии клавиш Ctrl+r).

Сохраним макрос в текущей рабочей книге, установим в разделе Сохранить в Эта книга.

Нажмем кнопку ОК. Появится плавающая панель инструментов с кнопкой Остановить запись (Stop Recording). Теперь все производимые действия будут записываться до тех пор, пока не будет нажата эта кнопка. В строке состояния будет сообщение Запись. Построим шаблон таблицы расходов по следующему алгоритму:

1. Активизируем ячейку B1 и введем в нее Расходы.

2. Активизируем ячейку А2 и введем в нее Транспорт.

3. Активизируем ячейку A3 и введем в нее Коммунальные.

4. Активизируем ячейку А4 и введем в нее Еда.

5. Активизируем ячейку А5 и введем в нее Развлечения.

6. Активизируем ячейку Аб и введем в нее Одежда.

7. Активизируем ячейку А7 и введем в нее Компьютер.

8. Активизируем ячейку А8 и введем в нее Машина.

9. Активизируем ячейку А9 и введем в нее Прочие.

10. Активизируем ячейку а10 и введем в нее Итого.

11. Активизируем ячейку B10 и введем в нее формулу =сумм(B2:B9), вычисляющую суммарные расходы.

12. Выберем диапазон B2:B9 и при помощи раскрывающегося списка Границы (Borders) панели инструментов Форматирование (Formatting) создадим рамку, окаймляющую этот диапазон.

13. Выберем диапазон а10:B10 и при помощи раскрывающегося списка Цвет заливки (Fill Color) панели инструментов Форматирование (Formatting) окрасим этот диапазон в желтый цвет.

14. Выберем ячейку B1 и при помощи раскрывающегося списка Цвет заливки (Fill Color) панели инструментов Форматирование (Formatting) окрасим эту ячейку в желтый цвет.

15. Выберем диапазон А2:А9 и при помощи раскрывающегося списка Цвет заливки (Fill Color) панели инструментов Форматирование (Formatting) окрасим этот диапазон в светло-бирюзовый цвет.

16. Выберем столбец а, изменим его ширину так, чтобы введенный в диапазон А2:А9 текст помещался в этом столбце.

17. Выберем диапазон А2:B9 и при помощи мастера диаграмм, вызываемого кнопкой Мастер диаграмм (Chart Wizard) панели инструментов Стандартная (Standard), создадим диаграмму.

Рабочий лист теперь будет выглядеть так, как показано на рис.I. Остановим запись макроса, нажав кнопку Остановить запись (Stop Recording). Заполним ячейки таблицы исходными данными, расчет суммарных расходов и построение диаграммы теперь будет происходить автоматически.

Для просмотра записанной процедуры необходимо выбрать команду Сервис, Макрос, Макросы (Tools, Macro, Macros), которая вызовет диалоговое окно Макрос (Macro).

В этом диалоговом окне в списке выделим макрос и нажмем кнопку Изменить (Edit). Это вызовет появление главного окна редактора VBA .

1.2 Запуск макроса

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

Также для запуска созданного макроса можно нажать комбинацию клавиш Ctrl+r. Excel запустит макрос, который последовательно выполнит все записанные действия.

Пользователю предоставляется возможность отредактировать существующий макрос. Для изменения макроса используются команды Сервис-Макрос-Макросы-Изменить непосредственно на листе модуля.

3. настройка макроса

Любому макросу можно назначить отдельную кнопку на панели инструментов, что позволяет обратиться к нему на любом листе рабочей книги. Для назначения кнопки необходимо:

1. Поместить указатель мыши в любом месте панелей инструментов и щелкнуть правой кнопкой. В появившемся контекстном меню выбрать команду Настройка.

2. В диалоговом окне Настройка выбрать Команды затем в окне Категории выбрать Макросы, выбрать Настраиваемая кнопка.

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

4. Выбрать в списке имя нужного макроса и щелкнуть на ОК.

_{4. Доступ}

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

1. Поместить указатель мыши на любой панели инструментов, щелкнуть правой кнопкой мыши и выбрать в контекстном меню команду Форма для вызова на экран одноименной панели инструментов.

2. Щелкнуть на Кнопка и указатель мыши превратится в крестик. Поместить его в нужном месте листа или диаграммы и, не отпуская кнопки мыши,очертить прямоугольник желаемого размера (это и будут размеры кнопки).

3. В появившемся на экране диалоговом окне Назначить макрос объекту выбрать из списка тот макрос, который нужно назначить кнопке и щелкнуть ОК.

4. После создания кнопки можно отредактировать надпись на ней, выбрав подходящий стиль и шрифт.

В принципе макрос можно назначить любому графическому объекту. Процедура назначения макроса в этом случае аналогична процедуре назначения макроса кнопке:

2. Порядок выполнения работы

1. Выполнить разобранный выше пример.

2. Проанализируйте поставленную задачу (см. варианты) и определите, данные какого типа содержит данная таблица.

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

4. Присвойте макросу кнопку.

5. Рассмотрите различные варианты возможностей вызова макроса.

Варианты заданий

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

Фамилия студента, номер зачетной книжки, дата рождения, адрес, телефон.

Заболевание, код заболевания, количество заболевших в текущем году, количество заболевших в прошедшем году, процент роста.

Фамилия абонента, телефон, дата разговора, тариф, сумма в рублях.

Наименование товара, код товара (страна-код), дата использования, цена в $.

Город, номер телефона, дата разговора, время разговора (часы и минуты).

Наименование товара, дата поставки, стоимость в рублях, стоимость в $.

Наименование детали, код детали, код цеха-производителя, количество.

Наименование строительных работ, объем, расценка в рублях, стоимость, телефон подрядчика.

Марка автомобиля, регистрационный номер, стоимость в $, телефон владельца.

Фамилия сотрудника, табельный номер, дата поступления, телефон.

12. Фамилия студента, название предметов и оценка по предмету, средний балл, % хороших и отличных оценок.

Наименование товара, цена в $, дата поставки, курс доллара, цена в рублях.

3. Отчет по работе:

3.1. Цель работы;

3.2. Краткие теоретические сведения;

3.3. Постановку задачи и исходные данные;

3.4. Результаты работы;

3.5. Выводы по работе

4. Контрольные вопросы

4.1. Что такое макрос?

4.2. Назначение MacroRecorder?

4.3. Как начать запись макроса?

4.4. Можно ли просмотреть или изменить записанный макрос?

4.5. Перечислите способы запуска макроса.

4.6. Какие параметры макроса позволяет задать диалоговое окно

Запись макроса?

4.7. Как назначить макросу кнопку на панели инструментов?

4.8. Как на рабочем листе назначить макросу кнопку?

Лабораторная работа №17

Vba. Элементы управления

Цель работы - ознакомиться с интерфейсом VBA, некоторыми его объектами, их свойствами и методами.

VBA относится к языкам объектно-ориентированного программирования (ООП). ООП можно описать как методику анализа, проектирования и написания приложений с помощью объектов. Объект - комбинация кода и данных , которая может рассматриваться как единое целое, например элемент управления, форма и компонент приложения. Каждый объект определяется по принадлежности к классу. Все визуальные объекты , такие как рабочий лист (Worksheet), диапазон (Range), диаграмма (Chart), форма (UserForm), являются объектами.

Доступ к данному языку программирования можно осуществлять практически из любого приложения Windows. Мы будем работать вместе с Microsoft Excel, который будет являться основным приложением для проекта VBA.

Редактор VBA активизируется командой Сервис, Макрос, Редактор Visual

Basic. Возвратиться из редактора VBA в рабочую книгу можно нажатием кнопки Вид Microsoft Excel. Интерфейс VBA состоит из следующих основных компонентов: окно проекта, окно свойств, окно редактирования кода, окна форм, меню и панели инструментов.

Окно проекта

Окно проекта в редакторе VBA активизируется выбором команды Вид, окно проекта или нажатием кнопки Окно проекта

В окне проекта представлена иерархическая структура файлов форм и модулей текущего проекта

В проекте автоматически создается модуль для каждого рабочего листа и для всей книги. Кроме того, модули создаются для каждой пользовательской формы макросов и классов. По своему предназначению модули делятся на два типа- модули объектов и стандартные. К стандартным модулям относятся те, которые содержат макросы. Такие модули добавляются в проект командой Вставка Модуль. К модулям объектов относятся модули, связанные с рабочей книгой, рабочими листами, формами, и модули класса.

Формы создаются командой Вставка, UserForm, а модули класса - командой Вставка, Модуль класса. По мере создания добавления и удаления файлов из проекта эти изменения отображаются в окне проекта. Отметим, что удаление файла из окна проекта производится выбором значка файла с последующим выполнением команды Файл, Удалить.

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

Для создания диалоговых окон, разрабатываемых приложений в VBA, используются формы. Редактор форм является одним из основных инструментов визуального программирования. Форма в проект добавляется с помощью команды Вставка, Форма (Insert, Form) или нажатием кнопки Вставить UserForm

В результате на экран выводится незаполненная форма с панелью инструментов Панель элементов (рис. 1).

Рисунок 1

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

1. Щелкните значок того элемента, который вы собираетесь разместить в форме.

2. Поместите указатель мыши на то место, где будет располагаться управляющий элемент.

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

4. Отпустите кнопку мыши. Элемент управления на нужном место создан.

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

Любой управляющий элемент, который вы разместили на форме, обладает рядом свойств

Окно свойств

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

Рисунок 2

Окно свойств состоит из двух составных частей: верхней и рабочей. В верхней части окна свойств располагается раскрывающийся список, из которого можно выбрать любой элемент управления текущей формы или саму форму. Рабочая часть состоит из двух вкладок: По алфавиту (Alphabetic) и По категориям (Categorized), отображающие набор свойств в алфавитном порядке или по категориям. В обоих вкладках свойство Name (имя элемента управления) будет первым. Изменяются значения свойств одним из следующих способов:

Вводом с клавиатуры значения свойства в соответствующее поле.

Значения большинства свойств можно выбрать из раскрывающегося списка. Раскрывающийся список активизируется щелчком в соответствующем поле окна свойств.

Задание к выполнению

Расположите на форме следующие элементы:

Label1;

TextBox1;

CommandButton1.

Активизируйте Label1, в окне свойств найдите свойство Caption и измените Label1 на название вашего факультета.

Те же действия произведите с CommandButton1, изменяя Caption на - "ок".

Активизируйте TextBox1 и измените свойство Text, набрав аббревиатуру своей группы.

С помощью элемента Выбор объекта выделите все элементы на форме. В окне свойств хорошо видно какие свойства одинаковы для всех элементов. Изменяя свойства Font (полужирный, курсив, размер шрифта 15), BackColor (светлая тень для кнопки), Visible(False), проследите все изменения элементов управления на форме.

Осуществить запуск программы с помощью кнопки Запуск на панели инструментов или меню Запуск/ Запуск программы.

Вернуться в режим конструктора VBA для этого нажмите крестик на форме.

Выделите опять все объекты и поменяйте только свойство Visible (True) и снова произведите запуск программы .

Самостоятельно изменяйте другие свойства элементов данной формы и пронаблюдайте их изменения.

Лабораторная работа n 18

Vba. Линейная программа

Программа (код программы) записывается в окне кода.

Окно кода используется при написании любой программы VBA, будь это код макроса, запуск которого осуществляется при нажатии кнопки в созданной пользователем форме, или подпрограмма.

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

Код программы вводится непосредственно в окно кода, так же как текст в любом текстовом редакторе.

Код программы может быть связан с формой -UserForm-(программа пишется для соответствующей формы) так и не связан с ней (пример программ на Паскале). В последнем случае программу пишут в окне модуля.

Для того, чтобы получить окно модуля необходимо выполнить следующие действия:

Вставка Модуль

Чаще в VBA имеют дело с формой (UserForm). Чтобы получить окно формы необходимо произвести такие действия:

Вставка UserForm

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

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

В VBA, формы можно создать самостоятельно. Форма - это тоже самое, что и любое диалоговое окно. Панель элементов позволяет разместить ряд элементов управления в форме.

Инструменты панели элементов

Допускается настройка панели элементов путем добавления к ней страниц или элементов управления с помощью команды Дополнительные элементы... из меню Сервис.

При добавлении страницы в нее автоматически вставляется инструмент Выбор объектов.

Стандартные элементы управления панели элементов:

Выбор объектов

Это единственный инструмент на панели элементов, не создающий никаких элементов управления. Он служит для изменения размеров и положения элементов формы.

Надпись

Позволяет отобразить в форме неизменяемый текст, например подпись к рисунку.

Поле

Содержит вводимый и изменяемый пользователем текст.

Поле со списком

Вставляет объект, являющийся сочетанием списка и поля. Пользователь может либо выбрать нужное значение из списка, либо ввести его в поле.

Список

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

Флажок

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

Переключатель

Используется для предоставления выбора одного варианта из многих.

Выключатель

Создает кнопку, имеющую два состояния: включено и выключено.

Группа

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

Кнопка

Создает кнопку, при нажатии которой выполняется команда.

Набор вкладок

Позволяет создать несколько станиц в одной и той же области окна или окна диалога.

MultiPage

Служит для представления нескольких экранов информации в виде единого набора.

Полоса прокрутки

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

SpinButton

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

Рисунок

Отображает в форме точечный рисунок, значок или метафайл.

После размещения элементов управления на форме необходимо связать объект на форме с кодом.

В VBA очень просто связать объект с кодом. Для выполнения данной операции:

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

Вызвать контекстное меню необходимого объекта правой клавишей мыши и нажать поле Программа.

Решение любой задачи имеет три части:

Ввод данных

Обработка данных

Вывод результата

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

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

Вывод результата - это код программы, который позволяет отобразить полученный результат в необходимом виде: на экране (лист excel, форма), на принтере и т.д.

Сохранение программы

Если программу сохраняется первый раз или вас устраивает уже существующее имя, то сохранять можно как в VBA, так и в Excel;

Если вас не устраивает уже существующее имя, то сохранять необходимо таким образом: выйти в Excel, выбрать пункт меню Файл, а в нем пункт - Сохранить как.

Решим задачу: найдем сумму

а + в = с

Порядок выполнения работы:

Выполнить команду СЕРВИС /МАКРОС/РЕДАКТОР VBA

Выполнить команду ВСТАВКА/USER FORM

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

Изменить свойства объектов на форме c помощью окна свойств.

Свойство	Значение
Label1.Caption	A
Label2.Caption	В
Label3.Caption	С
СоmmandButton1	Результат
CheckBox1.Caption	Очистка окон
Для всех объектов свойство .BackColor	По своему вкусу выбрать цвет Из палитры цветов
Для Label1, Label2 ,Label3 Свойство Font	В диалоговом окне “Шрифт”, которое появится после щелчка по Кнопке с изображением трех маленьких точек, расположенной напротив свойства Font в окне свойств, выбрать размер 16

Написать программный код. Для этого рекомендуется выполнить двойной щелчок по кнопке результат и перейти в окно программы, где набрать текст процедуры обработки события Click() для кнопки и для флажка(CheckBox1):

Private Sub CheckBox1_Click()

TextBox1.Text = ""

TextBox2.Text = ""

TextBox3.Text = ""

TextBox3.Visible = False

TextBox1.SetFocus

CheckBox1.Value = False

End Sub

Private Sub CommandButton1_Click()

Dim a As Integer

Dim b As Integer

Dim c As Integer

a = CInt(TextBox1.Text)

b = CInt(TextBox2.Text)

c = a + b

MsgBox "результат смотри в TextBox3"

TextBox3.Visible = True

TextBox3.Text = c

End Sub

Пояснения к программе:

1) Dim а As Integer

Эта инструкция описывает переменные как Integer -- целые числа от -32768 и до 32767. При попытке присвоить а число, выходящее за пределы этого диапазона, возникает ошибка. При присваивании а дробного числа, выполняется округление.

Инструкция Dim - Описывает переменные и выделяет для них память.

2) CInt - функция преобразования типов данных (преобразовывает выражение в скобках к типу Integer).

Синтаксис CInt(выражение)

3) c=a+b

Оператор присваивания ( = )-вычисляется значение выражения, стоящего справа от знака присваивания, и присваивается переменной, стоящей слева от знака присваивания.

4) MsgBox "результат смотри в TextBox3"

Появляется на экране окно сообщений MsgBox, в котором отображается сообщение, записанное в кавычках, и выполнение программы останавливается до тех пор пока не будет нажата кнопка "OK".

5) TextBox3.Text = c

Результат выполнения программы (с) выводится на форму в TextBox3

6) TextBox1.Text = "", TextBox2.Text = "", TextBox3.Text = ""

Производится очистка полей TextBox1, TextBox2, TextBox3.

7) TextBox1.SetFocus

Устанавливается фокус (курсор) в TextBox1.

8) CheckBox1.Value = False

Исчезает галочка у флажка CheckBox1.

Блок-схема к программе

Задания для выполнения

f(x,yёz) = (x^2-y^2)/(1+z+x^2).

2.f(x,y,z) = (x+y+z)/(x^2+y^2+z^2).

f(x,y)=x./(1+y)+y/(1+x)+1/(x+y).

f(x,y,z)=(x+y+z)/(x*y*z).

f(a,b,c,x)=a*x^2+b*x+c.

Вычисления процентного отношения двух чисел(сколько процентов составляет величина первого от величины второго).

Вычисления дискриминанта квадратного уравнения.

f(x,y)=(x+y)(x^2+y^2)(x^3+y^3).

f(x,y,z)=(x*y*z)/(x+y^2+z^3).

f(x,y,z)=x/(y+z)+y/(x+z)+z/(x+y).

f(x,y,z)=(x+1)/y/z+sin(z)/y/x+1n(y)/x/z.

f(x,y,z)=x^3+y^4+z^5.

f(x,y,z)=x*y/z+y*z/x+z*x/y.

f(x,y,z)=(x^3+1)/(y+z^2)

f(x,y)=(x^2+y^2)/(5*x*y)

f(x,y)=3*(x+y)/x*(x+y)

Лабораторная работа N19

Vba. Условный оператор

Цель работы: научиться разрабатывать алгоритмы и программы разветвляющейся структуры с условным оператором IF . Познакомиться с некоторыми объектами VBA и с их свойствами

Краткие теоретические сведения

1. В схемах алгоритмов выбор условия обозначается с помощью символа,

от которого отходит ветвление для указания дальнейших действий в зависимости от выбора “ДА” или “НЕТ”. Выбор хода алгоритма, как правило, зависит от значения переменной или выражения, состояния объекта. Например,

2. В программном коде, чтобы реализовать ветвление применяется условный оператор IF THEN

Условный оператор позволяет выбирать и выполнять действия в зависимости от истинности некоторого условия. Имеется два варианта синтаксиса: В первом случае он имеет вид:

IF <условие> Then [операторы 1] [Else [операторы 2]]

Во втором случае оператор расположен на нескольких строках:

IF <условие> Then

[операторы]

[ElseIf <условие - n> Then

[операторы-n ]…

[Else]

[ИначеОператоры]]

End If

Здесь условие обязательно в обоих вариантах. Оно может быть числовым или строковым выражением со значениями TRUE или FALSE. Операторы 1 и операторы 2 это последовательности из одного или нескольких разделенных двоеточием операторов. По крайней мере, одна из этих последовательностей должна быть непустой. Если условие истинно (TRUE), выполняется последовательность «операторы 1», если ложно, “операторы 2”.

Пример записи оператора:

`Условный оператор в виде одной строки:

IF A>10 Then A=A+1: B=B+A: C=C+B ELSE C=A*B: A=C+2

тот же условный оператор в виде блока:

IF A>10 Then

A=A+1: B=B+A : C=C+B

ELSE

C=A*B: A=C+2

End If

Примеры использования оператора условия

Пример1.

Если точка М(x1,y1) лежит:

выше прямой

y=kx+b,

то выдать сообщение “Точка М лежит выше прямой”;

ниже прямой, то выдать сообщение “Точка М лежит ниже прямой”;

на прямой, то выдать сообщение “Точка М лежит на прямой”.

Решение задачи

1 этап. Продумать задачу и уяснить, что будет входными данными, что выходными и есть ли все необходимое для решения задачи.

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

Рис.Интерфейс приложения

3 этап. Определить ход решения задачи и составить алгоритм программы. В нашей задаче, чтобы определить положение точки относительно прямой, нужно подставить координату точки X1 в уравнение прямой и сравнить полученное значение Y c координатой точки Y1. Если значение Y=Y1, точка лежит на прямой; если полученное значение Y >Y1, точка -ниже прямой, и ,наконец, если Y<Y1, точка - выше прямой.

Схема алгоритма программы (См. Ниже)

4 этап. Загрузить редактор VBA и разработать приложение.

Порядок выполнения работы

1. Выполнить команду СЕРВИС /МАКРОС/РЕДАКТОР VBA

2. Выполнить команду ВСТАВКА/USER FORM

3. Поместить на форму элементы с панели элементов и расположить их нужным образом. Требуемые элементы:

4 элемента Label (текст)

4 элемента TextBox (поле ввода)

2 элемента CommandButton(кнопка)

1 элемент CheckBox(флажок)



4 .Изменить свойства объектов на форме c помощью окна свойств следующим образом

Свойство	Значение
Label1.Caption	Точка M(
Label2.Caption
Label3.Caption	Прямая y =
Label4.Caption	X +
UserForm1.Caption	Принадлежность точки прямой
TextBox1.Text	X1
TextBox2.Text	Y1
TextBox3.Text	K
TextBox1.Text	B
СоmmandButton1	СТАРТ
СоmmandButton2	ВЫХОД
CheckBox1.Caption	Очистка полей ввода
Для всех объектов свойство BackColor	По своему вкусу выбрать цвет Из палитры цветов
Для Label1, Label2 ,Label3 Label4, TextBox1, TextBox2, TextBox3, TextBox4 свойство Font	В диалоговом окне “Шрифт”, которое появится после щелчка по Кнопке с изображением трех маленьких точек, расположенной напортив свойства Font в окне свойств, выбрать размер 16

Рекомендуется сопоставить рисунки “Интерфес приложения” и “Окно формы, чтобы сделать вывод о назначении использованных свойств объектов”.

5. Написать программный код. Для этого рекомендуется выполнить двойной щелчок по кнопке СТАРТ и перейти в окно программы, где набрать текст процедуры обработки события Click() для кнопки:

Private Sub CommandButton1_Click()

Dim x1, y1, k, b As Integer

x1 = CInt(TextBox1.Text)

y1 = CInt(TextBox2.Text)

k = CInt(TextBox3.Text)

b = CInt(TextBox4.Text)

y = k * x1 + b

If y = y1 Then

MsgBox "Точка лежит на прямой"

ElseIf y > y1 Then

MsgBox "Точка лежит ниже прямой"

Else

MsgBox "Точка лежит выше прямой"

End If

CheckBox1.Value = False

End Sub

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

Аналогичным образом создаем процедуру для кнопки ВЫХОД:

Private Sub CommandButton2_Click ()

Unload UserForm1

End Sub

И для объкта Флажок:

Private Sub CheckBox1_Click()

CheckBox1.Value = True

TextBox1.Text = ""

TextBox2.Text = ""

TextBox3.Text = ""

TextBox4.Text = ""

TextBox1.SetFocus

CheckBox1.Value = False

End Sub

Пример2.

Ввести Х, вычислить F по формуле:

X/2, если Х>0

F= (X+1)/2,

если Х<0

Рис. Интерфейс приложения

Код программы

Private Sub CommandButton1_Click()

Dim x, F As Single

x = TextBox1.Value

If x = 0 Then MsgBox "Функция не определена", vbCritical, _

"Расчет функции"

If x > 0 Then F = x / 2 Else F = (x + 1) / 2

TextBox2.Value = F

End Sub

Private Sub CommandButton2_Click()

Unload Me

End Sub

Выполнения самостоятельного задания

1. Выбрать для решения одну из задач

2. Нарисовать интерфейс приложения.

3. Разработать схему алгоритма.