624

Ход работы Задание 1. Создайте на листе «База данных» названия

столбцов списка.

Так как текст этих названий уже имеется на листе Форма, можно содержимое ячеек В3 : В11 скопировать на лист База данных в ячейки А1 : I1.

По ря д ок р або т ы

1.Выделите на листе Форма ячейки В3 : В11. Нажмите сочетание клавиш Ctrl + C.

2.Перейдите на листе База данных в ячейку А1 и выпол-

ните команду Правка / Специальная вставка… (рис. 10).

Вдиалоге этой команды установите параметры:

а) Значения б) Транспонировать.

Рис. 10. Диалоговое окно команды «Специальная вставка»

3. К полученному в результате копирования тексту примените форматы:

а) выравнивание по горизонтали — по центру; б) выравнивание по вертикали — по центру; в) переносить по словам; г) шрифт — полужирный;

д) границы — внешние и внутренние.

Задание 2. Разработайте средства для просмотра записей базы данных с помощью созданной формы.

2 1

По ря д ок р або т ы 1. На листе База данных блоку А2 : А65536 присвойте имя

километры:

а) выделите на листе База данных блок А2 : А65536: для этого поставьте курсор в ячейку А2 и, удерживая клавишу

Shift, нажмите сначала клавишу End, а затем ;

б) выполните команду Вставка / Имя / Присвоить, введя

вдиалоге этой команды имя километры.

2.На листе «Форма» ячейке Н1 присвойте имя текущая_ запись и цвет шрифта — «белый».

3.Присвойте ячейке I1 имя всего_записей и цвет шрифта — «белый».

4.Введите в ячейку I1 формулу = СЧЕТЗ(километры) для определения количества введенных записей на листе База данных.

5.Введите в ячейку D3 формулу =текущая_запись & “ из ” & всего_записей.

6.Для ячейки D3 примените формат:

а) выравнивание по горизонтали — по центру; б) выравнивание по вертикали — по верхнему краю; в) цвет шрифта — серый 25 %.

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

(рис. 11).

Рис. 11. Вид формы с полосой прокрутки

2 2

8. Для внедренной полосы прокрутки необходимо назначить свойства (рис. 12):

а) минимальное значение = 1, б) максимальное значение должно быть равно числу запи-

сей в базе данных (это значение у нас хранится в ячейке с именем всего_записей),

в) шаг изменения = 1, г) связь с ячейкой, в которой отображается текущее значе-

ние — $H$1 (эта ячейка у нас носит имя текущая_запись).

Рис. 12. Формат полосы прокрутки

Максимальное значение в диалоговом окне команды Формат объекта можно указать только в виде числовой константы. Так как нам это значение нужно связать с ячейкой, то придется это сделать программным путем. К объекту «Полоса прокрутки»впроцедуреVBA можнообратиться так:Sheets(“Форма”). ScrollBars(1), где 1 — порядковый номер полосы прокрутки. Максимальное значение этого объекта назначается свойством

Max: Sheets(“Форма”).ScrollBars(1). Max = Range(“всего_

записей”).

9. Напишем текст процедуры для отображения в форме текущей записи, номер которой указан в ячейке Н1.

В этой процедуре используется стандартная функция рабо-

чего листа ПОИСКПОЗ (Application. WorksheetFunction.

2 3

Match), которая возвращает номер элемента в указанном диапазоне ячеек.

ПОИСКПОЗ (искомое_значение; просматриваемый_массив; тип_сопоставления)

Искомое_значение — этозначение, которое сопоставляется со значениямив аргументе просматриваемый_массив.Например, при поиске номера в телефонной книге имя абонента указывается в качестве искомого значения, а требуемым значением будет номер телефона. Искомое_значение может быть значением (числом, текстом или логической константой) или ссылкой на ячейку, содержащую число, текст или логическую константу.

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

Тип_сопоставления — число –1, 0 или 1. Тип_сопостав-

ления указывает, как Microsoft Excel сопоставляет искомое_

значение со значениями в аргументе просматриваемый_массив.

•Еслитип_сопоставленияравен1,тофункцияПОИСКПОЗ находит наибольшее значение, которое меньше либо равно аргументу искомое_значение. Просматриваемый_массив должен быть упорядочен по возрастанию.

•Еслитип_сопоставленияравен0,тофункцияПОИСКПОЗ находит первое значение, которое в точности равно аргументу искомое_значение. Просматриваемый_массив может быть не упорядочен.

•Если тип_сопоставленияравен –1, то функция ПОИСКПОЗ находит наименьшее значение, которое больше либо равно аргументу искомое_значение. Просматриваемый_массив должен быть упорядочен по убыванию.

•Если тип_сопоставления опущен, то предполагается, что он равен 1.

Модулю, в котором будем писать макрос “отобразить запись”, присвоим имя Формы.

Sub отобразить_запись() Dim k As Integer, type1

Sheets(“Форма”).ScrollBars(1).Max = Range(“всего_записей”) k = Range(“текущая_запись”) номер текущей записи

2 4

If k = 0 Then Exit Sub если запись = 0, завершить работу процедуры

Range(“номер_км”) = Sheets(“База данных”).Cells(k + 1, 1) Range(“длина_км”) = Sheets(“База данных”).Cells(k + 1, 2)

выбор значения для переключателей

If Sheets(“База данных”).Cells(k + 1, 3) = “Р65” Then Range(“D5”) = 1

Else

Range(“D5”) = 2 End If

выбор значения для списка значений длин рельса

If Sheets(“База данных”).Cells(k + 1, 4) = “Б” Then Range(“D6”) = 1

ElseIf Sheets(“База данных”).Cells(k + 1, 4) = “12,5” Then Range(“D6”) = 2

Else

Range(“D6”) = 3 End If

Range(“число_дефектных_рельсов”) = Sheets(“База данных”). Cells(k + 1, 5) type1 = Sheets(“База данных”).Cells(k + 1, 6)

определение номера для поля со списком (тип скрепления)

Range(“D8”) = Application. WorksheetFunction.Match(type1, _ Range(“типы_скреплений”),0)

Range(“негодные_скрепления”) = Sheets(“База данных”).Cells(k + 1, 7) Range(“число_негодных_шпал”) = Sheets(“База данных”).Cells(k + 1, 8) Range(“загрязненность_балласта”) = Sheets(“База данных”).Cells(k + 1, 9) Range(“номер_км”).Select

End Sub

10.Назначим эту процедуру для полосы прокрутки, выбрав

вконтекстном меню команду Назначить макрос.

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

По ря д ок р або т ы

1.На листе Форма в ячейку С5 введите формулу =ЕСЛИ(D5=1;"Р65";"Р75"); в ячейку С6 введем формулу

=ЕСЛИ(D6=1;"Б";ЕСЛИ(D6=2;12,5;25)); вячейку С8 введем формулу =ИНДЕКС(типы_скреплений;D8).

2.Методом автозаписи напишите процедуру для сохранения текущей записи, отредактированной на листе Форма. Для этого выберите команду Сервис / Макрос / Начать запись,

задайте макросу имя сохранить_запись и выполните следующие действия:

а) выделите блок ячеек С3 : С11;

2 5

б) нажмите сочетание клавиш Ctrl + C (копировать); в) перейдите на лист База данных в ячейку А2;

г) выбравкоманду Правка /Специальная вставка…, установитепараметры: ЗначенияиТранспонировать(см. рис. 10);

д) перейдите на лист Форма в ячейку С3;

е) выберите команду Сервис / Макрос / Остановить запись.

3. Переключитесь в окно редактора VBA и в новом модуле откройте записанный макрос:

Sub сохранить_запись() Range(“C3:C11”).Select Selection.Copy Sheets(“База данных”).Select Range(“A2”).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _:=False, Transpose:=True

Sheets(“Форма”).Select Range(“C3”).Select Application.CutCopyMode = False

End Sub

4. Отредактируйте текст этого макроса:

а) добавьте после заголовка макроса оператор k = Range(“текущая_запись”);

б) замените оператор Range(“A2”).Select на Range(“A” &

k+ 1).Select.

5.Отредактированный макрос поместите в модуль Формы.

6.На листе Форма внедрите кнопку с текстом Сохранить записьиназначьтедлянее макрос“сохранить_запись”(рис.13).

7.В модуле “форма” напишите процедуру для добавления новой записи:

Sub новая_запись()

номер текущей записи на 1 больше количества записей в базе данных

Range(“текущая_запись”) = Range(“всего_записей”) + 1

номер километра на единицу больше номера последней записи

If Range(“всего_записей”) = 0 Then

Range(“номер_км”) = 3345 начальный номер км ПЧ

Else

Range(“номер_км”) = _

Sheets(“База данных”).Cells(Range(“всего_записей”) + 1, 1) + 1 End If

длина километра чаще всего равна 1000 метров

2 6

Range(“длина_км”) = 1000

тип рельса чаще используется Р75 (это 2-й переключатель)

Range(“D5”) = 2

длина рельса чаще используется 25 м (в списке это 2-й элемент)

Range(“D6”) = 2

обнуляем ячейку для ввода числа дефектных рельсов

Range(“число_дефектных_рельсов”) = 0

настраиваем список с типами скреплений на начало

Range(“D8”) = 1

обнуляем ячейку для ввода числа негодных скреплений

Range(“негодные_скрепления”) = 0

обнуляем ячейку для ввода числа негодных шпал

Range(“число_негодных_шпал”) = 0

обнуляем ячейку для ввода загрязненности балласта

Range(“загрязненность_балласта”) = 0

активизируем ячейку с именем “номер_км”

Range(“номер_км”).Select

End Sub

8.На листе Форма внедрите кнопку и назначьте для нее макрос “новая_запись” (см. рис. 13).

9.В модуле “форма” напишите процедуру для удаления новой записи:

Sub удалить_запись() Dim v

v = MsgBox(“Удалить километр “ & Range(“номер_км”), vbYesNo + vbQuestion)

If v = 6 Then

Sheets(“База данных”).Rows(Range(“текущая_запись”) + 1).Delete If Range(“текущая_запись”) > Range(“всего_записей”) Then

Range(“текущая_запись”) = Range(“всего_записей”)

End If

отобразить_запись вызов процедуры отображения записи в форме

End If

End Sub

10.На листе Форма внедрите кнопку с текстом «Удалить»

иназначьте для нее макрос “удалить_запись” (см. рис. 13).

11.Продублируйте работу полосы прокрутки двумя кнопками «Назад» и «Далее» для отображения предыдущей и последующей записей соответственно. Для этого в модуле “форма” напишите две процедуры:

Sub назад()

if Range(“текущая_запись”) > 0 Then

2 7

Range(“текущая_запись”) = Range(“текущая_запись”) - 1 отобразить_запись

End If

End Sub Sub вперед()

If Range(“Текущая_запись”) < Range(“всего_записей”) Then Range(“Текущая_запись”) = Range(“Текущая_запись”) + 1

отобразить_запись

End If

End Sub

12. Для завершения сеанса редактирования базы данных напишитепроцедуру переходана листГлавное меню.Модулю, где будет написана эта процедура, присвойте имя “переходы”.

Sub главное_меню()

Sheets(“Главное меню”).Select

End Sub

13.На листе Форма внедрите кнопку с текстом “Закрыть”

иназначьте для нее макрос “главное_меню” (см. рис. 13).

14.Для редактирования базы данных напишите в модуле “переходы” процедуру активизации листа Форма:

Sub база_данных() Sheets(“Форма”).Select Range(“Текущая_запись”) = 1 отобразить_запись

End Sub

Рис. 13. Общий вид формы

2 8

15.На листе Главное меню внедрите кнопку с текстом База данных и назначьте для нее макрос “база_данных”.

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

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

1.Назначение команды Специальная вставка из меню «Данные».

2.Как определить количество записей в списке MS Excel?

3.Как организовать просмотр записей списка MS Excel в форме на рабочем листе?

4.Назначение стандартной функции ПОИСКПОЗ.

2. СОЗДАНИЕ ОТЧЕТОВ

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

В нашем примере необходимо получить несколько видов отчетов. Кнопки для выбора желаемого отчета поместим на лист Отчеты и назовем «Расчетная таблица», «Сортировка по проценту негодных шпал», «Гистограмма», «Круговая диаграмма», «Кольцевая диаграмма», «Фильтр1», «Фильтр2», «Фильтр3».

Для получения отчетов будем использовать макросы. Есть два способа создания макроса:

Автоматически записать последовательность действий, выполняемых пользователем в Excel.

Вручную ввести инструкции на особом листе Excel, называ-

емом модулем.

Для вводаинструкцийвмодулеиспользуется языкпрограм-

мирования Visual Basic.

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

2.1. Создание расчетной таблицы

Цели работы:

•закрепить навыки проектирования и форматирования таблиц;

•закрепить навыки получения макросов автозаписью и ручным способом.

2 9

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

Ход работы Задание 1. Создайте под автозапись шапку таблицы и ее

название на листе «Расчетная таблица». По ря д ок р або т ы

1.После листа База данных добавьте лист и присвойте ему имя “Расчетная таблица».

2.Для формирования макроса по выполняемым далее действиям включите автозапись командой Сервис Макрос

Начать запись, задайте макросу имя «Шапка».

3.Введите названия граф в ячейки:

A2 — “Номер километра”;

B2 — “Длина километра, м”;

C2 — “Тип рельса”;

D2 — “Длина рельса, м”;

E2 — “Число дефектных рельсов в километре”;

F2 — “Тип скрепления”;

G2 — “Негодные скрепления, %”;

H2 — “Тип шпал”;

I2 — “Материал шпал”;

J2 — “Общее число шпал”;

K2 — “Число негодных шпал”;

L2 — “% негодных шпал”;

M2 — “Загрязненность балласта”, %;

N2 — “Потребность в среднем ремонте”.

4.Отформатируйте блок A2 : N2, для этого выделите его и назначьте параметры:

Шрифт — полужирный;

Размер — 8; Выравнивание — по горизонтали и вертикали — «по

центру», «переносить по словам».

5.Введите в ячейку A1 название таблицы «Сведения о состоянии пути ПЧ».

6.Задайте на название таблицы полужирный шрифт размером 14 пунктов и разместите по центру таблицы, используя объединение ячеек.

3 0