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

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

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

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

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

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

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

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

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

48. В окне кода в разделе формы 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

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

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

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

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

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

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

53. В окне редактора кода в разделе формы 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

End Sub

Private Sub UserForm_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

End Sub

Таблица 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

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

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

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

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

.

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

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

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

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

57. В окне кода в разделе формы 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

Next j

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

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

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

60. Сохраните форму 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"

End Sub

Private Sub cmd_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

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

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

62. Протестируйте и сохраните форму frm_L2.

63. Подготовьте отчет по лабораторной работе и закройте книгу 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