Обработка табличных Excel данных в VBA

 

Цель работы:научиться обрабатывать табличные данные Excelв среде VBA.

 

Задание 1 Ввод данных в таблицуExcel

1. Откройте приложение Excel и создайте новую книгу. Сохраните ее с именем VBA_Excel.xlsm.

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

3. Введите имяформы и имена командных кнопок в соответствии с таблицей6.1.

 

Рис. 6.1 Вид формы Заголовок таблицы

 

Таблица 6.1 Имена элементов на форме Заголовок таблицы

Элемент	Наименование	Имя
Форма	Заголовок таблицы	frm_Fut
Кнопка	Ввод	cmd_Vvod
Кнопка	Выход	cmd_Exit

 

4. В окне кода в разделе формы frm_Fut введите следующий код:

Private Sub cmd_Vvod_Click()

'Формирование шапки таблицы на 1-м листе

Sheets(1).Activate

 With Range("A1:H1")

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlCenter

 .ReadingOrder = xlContext

.MergeCells = True

End With

Cells(1, 1) = "Расчетстипендиистудентамэнергофака"

Cells(2, 1) = "Группа"

Cells(2, 2) = "Ф.И.О."

Cells(2, 3) = "Экз.1"

Cells(2, 4) = "Экз.2"

Cells(2, 5) = "Экз.3"

Cells(2, 6) = "Экз.4"

Cells(2, 7) = "Экз.5"

Cells(2, 8) = "Стипендия"

With Range("A1:H1000")

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlCenter

 .ReadingOrder = xlContext

 End With

cmd_Vvod.Enabled = False

End Sub

 

Private Sub cmd_Exit_Click()

frm_Fut.Hide

End Sub

5. Запустите на выполнение форму frm_Fut и просмотрите результат на Лист1книги Excel. Возможный вариант шапки таблицы показан на  рис. 6.2. При необходимости вы можете внести изменения в заголовки столбцов таблицы или оформитьее на другом листе.

6. Обязательно сохраните  форму frm_Futв личную папку.

 

Рис. 6.2 Вид шапки таблицы

 

7. Вставьте новую форму для ввода данных. Расположите на ней элементы управления и оформите их внешний вид, как показано на рис. 6.2.

Рис. 6.2 Вид формы Ввод данных в режиме конструктора

 

8. Введите имя формы и имена элементов управления в соответствии с таблицей 6.2.

9. В окне редактора кода в разделе формы frm_Vvodвведите следующий код:

Dim i As Double

Private Sub cmd_Vvod_Click()

Cells(i, 1) = txt_Ind.Text

Cells(i, 2) = txt_FIO.Text

Cells(i, 3) = CInt(txt_M1.Text)

Cells(i, 4) = CInt(txt_M2.Text)

Cells(i, 5) = CInt(txt_M3.Text)

Cells(i, 6) = CInt(txt_M4.Text)

Cells(i, 7) = CInt(txt_M5.Text)

txt_N.Enabled = True

txt_N.Text = CStr(i)

txt_N.Enabled = False

i = i + 1

End Sub

Private Sub cmd_Clear_Click()

txt_Ind.Text = "": txt_FIO.Text = "": txt_M1.Text = ""

txt_M2.Text = "": txt_M3.Text = "": txt_M4.Text = ""

txt_M5.Text = ""

End Sub

Private Sub cmd_Exit_Click()

frm_Vvod.Hide

EndSub

PrivateSubUserForm_Initialize()

i = 1

Do While Cells(i, 1) > " "

i = i + 1

Loop

txt_N.Enabled = True

txt_N.Text = CStr(i - 1)

txt_N.Enabled = False

EndSub

Таблица 6.2 Имена элементов на форме Ввод данных

Элемент	Наименование	Имя
Форма	Ввод данных	Frm_Vvod
Текстовое поле	Группа	txt_Ind
Текстовое поле	Количество записей	txt_N
Текстовое поле	Фамилия студента	txt_FIO
Текстовое поле	Экз.1	txt_M1
Текстовое поле	Экз.2	txt_M2
Текстовое поле	Экз.3	txt_M3
Текстовое поле	Экз.4	txt_M4
Текстовое поле	Экз.5	txt_M5
Кнопка	Ввод	cmd_Vvod
Кнопка	Очистить	cmd_Clear
Кнопка	Выход	cmd_Exit

10. Запустите frm_Vvod и заполните данными не менее 10 строк таблицы Расчет стипендии.

11. Сохраните форму frm_Vvod.

 

Задание2Организация вычислений в таблице

1. Вставьте новую форму для расчета стипендии. Расположите на ней элементы управления и оформите их внешний вид, как показано на рис. 6.3

.

Рис. 6.3 Вид формы Расчет стипендии

 

2. Введите имя формы и имена командных кнопок в соответствии с таблицей 6.3.

 

Таблица 6.3Имена элементов на форме Расчет стипендии

Элемент	Наименование	Имя
Форма	Заголовок таблицы	frm_Ras
Текстовое поле	Введите размер базовой стипендии	txt_St
Кнопка	Расчет	cmd_Ras
Кнопка	Выход	cmd_Exit

 

3. В окне кода в разделе формыfrm_ Rasвведите следующий код:

Dim Sb As Double

Dim Stip As Double

Private Sub cmd_Exit_Click()

frm_Ras.Hide

End Sub

Private Sub cmd_Ras_Click()

St = CSng(txt_St)

i = 3

Do While Sheets(1).Cells(i, 2) > ""

k0 = 0: k1 = 0: k3 = 0: k4 = 0: k5 = 0: k6 = 0: k7 = 0

k8 = 0: k9 = 0: k10 = 0: k2 = 0

For j = 1 To 5

If Cells(i, j + 2) = 0 Then k0 = k0 + 1

If Cells(i, j + 2) = 1 Then k1 = k1 + 1

If Cells(i, j + 2) = 2 Then k2 = k2 + 1

If Cells(i, j + 2) = 3 Then k3 = k3 + 1

If Cells(i, j + 2) = 4 Then k4 = k4 + 1

If Cells(i, j + 2) = 5 Then k5 = k5 + 1

If Cells(i, j + 2) = 6 Then k6 = k6 + 1

If Cells(i, j + 2) = 7 Then k7 = k7 + 1

If Cells(i, j + 2) = 8 Then k8 = k8 + 1

If Cells(i, j + 2) = 9 Then k9 = k9 + 1

If Cells(i, j + 2) = 10 Then k10 = k10 + 1

Nextj

Sb = (k1 * 1 + k2 * 2 + k3 * 3 + k4 * 4 + k5 * 5 _

+ k6 * 6 + k7 * 7 + k8 * 8 + k9 * 9 + k10 * 10) / 5

If (k0 + k1 + k2 + k3) > 0 Then

Stip = 0

      Else

If Sb >= 9 Then

Stip = St * 1.6

      Else

If (Sb >= 8 And Sb < 9) Then

Stip = St * 1.4

      Else

If (Sb >= 6 And Sb < 8) Then

Stip = St * 1.2

End If

End If

End If

 End If

Cells(i, 8) = Stip

i = i + 1

Loop

End Sub

4.  Запустите на выполнение формуfrm_ Ras. В результате на Лист1 будет заполнен столбец Стипендия значениями вычисленной стипендии.Возможный вариант вида заполненной таблицы приведен на рис. 6.4.

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

6. Сохраните формуfrm_ Ras.

 

Рис. 6.4 Вариант таблицы расчета стипендии

 

Задание 3 Сортировка данныхв таблице

1. Вставьте новую форму для сортировки данных в таблице.

2.  Выполните для нее действия, аналогичные п. п. 1, 2 предыдущего задания. Внешний вид новой формы показан на рис. 6.5, имена объектов на форме – в таблице 6.4.

 

Рис. 6.5 Внешний вид формы Сортировка данных

 

Таблица 6.4 Имена элементов на форме Сортировка данных

Элемент	Наименование	Имя
Форма	Сортировка данных	frm_Sort
Кнопка	Сортировать	cmd_Sort
Кнопка	Выход	cmd_Exit

 

3. В окне кода в разделе формы frm _ Sortвведите следующий код:

Private Sub UserForm_Activate()

' ЗаполнениеспискаListBox1

ListBox1.AddItem "Группа"

ListBox1.AddItem "ФИО"

ListBox1.AddItem "Экз.1"

ListBox1.AddItem " Экз.2"

ListBox1.AddItem " Экз.3"

ListBox1.AddItem "Экз.4"

ListBox1.AddItem "Экз.5"

EndSub

PrivateSubcmd_Sort_Click()

' Определение количества строк в таблице

n = 3

Do While Cells(n, 1) > ""

n = n + 1

Loop

n = n - 1

'пример выбранного критерия

k = ListBox1.ListIndex + 1

' Сортировка

i = 3

Do While i <= n

x = Cells(i, k)

kx = i: i = i + 1

Do While i <= n

y = Cells(i, k)

ky = i: i = i + 1

If y < x Then

For j = 1 To 8

r = Cells(kx, j)

Cells(kx, j) = Cells(ky, j)

Cells(ky, j) = r

Next j

x = y

End If

Loop

i = kx + 1

Loop

MsgBox "Сортировка "& ListBox1.Text & " завершена!", vbCritical, _ "Сортировка"

End Sub

Private Sub cmd_Exit_Click()

frm_Sort.Hide

End Sub

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

5. Сохраните форму frm_ Sort.

 

Задание 4 Формирование ведомости по данным таблицы

1. Вставьте новую форму для формирования ведомости выплаты стипендии. Внешний вид новой формы показан на рис. 6.6, имена объектов на форме – в таблице 6.5.

 

Рис. 6.6 Внешний вид формы Ведомость

 

Таблица 6.4 Имена элементов на форме Ведомость

Элемент	Наименование	Имя
Форма	Ведомость	frm_L2
Кнопка	Лист1	cmd_L1
Кнопка	Лист2	cmd_L2
Кнопка	Формировать	cmd_F
Кнопка	Очистить	cmd_Clear
Кнопка	Выход	cmd_Exit

 

2. В окне кода в разделе формы frm _ L 2 введите следующий код:

Private Sub cmd_F_Click()

Sheets(1).Activate

' Формирование шапки таблицы на втором листе

With Sheets(2).Range("A1:E1")

   .HorizontalAlignment = xlCenter

   .VerticalAlignment = xlCenter

   .ReadingOrder = xlContext

   .MergeCells = True

 End With

 With Sheets(2).Range("A3:G100")

   .HorizontalAlignment = xlCenter

   .VerticalAlignment = xlCenter

   .ReadingOrder = xlContext

 End With

Sheets(2).Cells(1, 1) = "Ведомостьвыдачистипендии"

Sheets(2).Cells(2, 2) = "ФИО"

Sheets(2).Cells(2, 3) = "Сумма"

'i- номер строки на  1-м листе; k–на 2-млисте

i = 3

k = 3

Do While Cells(i, 2) > ""

If Cells(i, 8) > 0 Then

Sheets(2).Cells(k, 2) = Sheets(1).Cells(i, 2)

Sheets(2).Cells(k, 3) = Sheets(1).Cells(i, 8)

k = k + 1

End If

i = i + 1

Loop

Sheets(2).Activate

Range("A20").Select

End Sub

Private Sub cmd_L1_Click()

Sheets(1).Activate

End Sub

Private Sub cmd_L2_Click()

Sheets(2).Activate

End Sub

Private Sub cmd_Clear_Click()

Sheets(2).Activate

Range("A1:Z100").Clear

Sheets(1).Activate

End Sub

Private Sub cmd_Exit_Click()

Sheets(1).Activate

frm_L2.Hide

End Sub

3. Протестируйте и сохраните форму frm_ L2.

4. Подготовьте отчет по лабораторной работе и закройте книгу VBA_Excel.xlsm с сохранением изменений.

 

Задание для самостоятельной работы

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

2. Опишите словесно алгоритм сортировки данных по выбранному столбцу. Поясните, как достигается при сортировке целостность данных в таблице.

 

Методические материалы

 

Наиболее часто используемый объект при работе с таблицами Excel - это объект Range. Этот объект может представлять одну ячейку, несколько ячеек (в том числе несмежные ячейки или наборы несмежных ячеек) или целый лист.

Самый простой способ получить объект Range - воспользоваться свойством Range. Это свойство предусмотрено для объектов Application, Worksheet и самого объекта Range (если нужно создать новый диапазон на основе уже существующего).

Например, получить ссылку на объект Range, представляющий ячейку A1, можно так:

Dim oRange As Range

Set oRange = Worksheets("Лист1").Range("A1").

Код для получения ссылкина диапазон ячеек A1:D10может быть таким:

Dim oRange As Range

Set oRange = Worksheets("Лист1").Range("A1:D10").

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

Dim oRange As Range

Set oRange = Worksheets("Лист1").Cells(1, 4).

Чтобы получить диапазон, состоящий из нескольких ячеек, удобно применять свойства Range и Cells вместе:

Dim oRange

Set oRange = Range(Cells(1, 1), Cells(5, 3)).

Третий способ - воспользоваться многочисленными свойствами объекта Range, которые позволяют изменить текущий диапазон или создать на основе его новый.

Обычно после того, как нужная ячейка найдена, в нее нужно что-то записать. Для этой цели используется свойство Value, например:

oRange.Value = "Мое значение" .

Важные свойстваобъектаRange:

· Address - позволяет вернуть адрес текущего диапазона;

· Cells–позволяет сделать ссылку на отдельную ячейку диапазона;

· Count - возвращает количество ячеек в диапазоне;

· Interior - позволяет установить цвет ячейки диапазона;

· Item - позволяет получить еще один объект Range, который определяется путем смещения исходного диапазона;

· Name - возможность получить ссылку на специальный объект именованного диапазона Name;

· Orientation- позволяет сориентировать текст в ячейках. Указывается угол наклона в градусах;

· Range - позволяет создать новый диапазон на основе уже существующего;

· Value -позволяет получить или назначить значение (числовое, текстовое или какое-либо другое) ячейкам диапазона;

Наиболее важные методы объекта Range:

· Activate() - выделяет текущий диапазон и устанавливает курсор ввода на его первую ячейку;

· AutoFill() - возможность использовать автозаполнение для диапазона;

· методы Clear… позволяют очистить содержимое диапазона - от значений, форматирования, комментарий;

· Copy() - возможность скопировать диапазон в другое место. Если место назначения не указано, он копируется в буфер обмена. Аналогично работает метод Cut(), при котором данные исходного диапазона вырезаются;

· Delete() - удаляет данные текущего диапазона;

· метод Insert() позволяет вставить ячейки в диапазон, сдвинув остальные с выбором направления - вправо или вниз;

· метод Justify() позволяет равномерно распределить текст по диапазону. Если в данный диапазон он не помещается, он будет распространен на соседние ячейки (с перезаписью их значений);

· метод Merge() позволяет объединить все ячейки диапазона в одну. Разбить обратно такую ячейку на несколько обычных можно при помощи метода UnMerge();

· Select() - возможность выделить указанный диапазон. Объекта Selection в Excel нет - вместо него есть возможность получить объект Range, представляющий выделенную область;

· Show() - экран будет проскроллирован таким образом, чтобы показать указанный диапазон;

· Sort() - возможность произвести сортировку ячеек в диапазоне.

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