|
|
Составление списка в программеКроме считывания элементов списка из ячеек, заданных свойством
Пример 7.5 – На рабочем листе в столбце A указаны номера контрактов, в столбце B - названия городов, где выполняется данный контракт, в столбце C – стоимости контрактов. Каждый контракт выполняется в одном городе; при этом в одном городе может выполняться несколько контрактов. Требуется, чтобы названия городов отображались в списке на пользовательской форме (см. рисунок 7.7). Каждый город должен быть указан в списке только один раз. При щелчке по названию города должна вычисляться сумма стоимостей контрактов, выполняемых в этом городе. На пользовательской форме необходимо разместить список (с именем Spisok_gorodov), надпись Сумма, текстовое поле (с именем Pole_summa), а также кнопку для закрытия формы. Для инициализации формы можно использовать следующую процедуру: Private Sub Userform_Initialize() Dim nazv() As String Set d = Range("A1").CurrentRegion m = d.Rows.Count ReDim nazv(1 To m) kol = 1: nazv(1) = d.Cells(1, 2).Value For i = 2 To m nazvanie = d.Cells(i, 2).Value vklucheno = False For j = 1 To kol If nazvanie = nazv(j) Then vklucheno = True Next j If vklucheno = False Then kol = kol + 1 nazv(kol) = nazvanie End If Next i For k = 1 To kol Spisok_gorodov.AddItem (nazv(k)) Next k End Sub Здесь массив nazv будет использоваться для хранения названий городов. Так как их количество заранее неизвестно, массив nazv объявлен как динамический: его размерность будет указана в операторе ReDim после того, как будет определено возможное количество городов. В операторе Set d = Range("A1"). CurrentRegion переменная d связывается с диапазоном ячеек, где размещаются данные о контрактах. В операторе m = d.Rows.Count определяется количество строк в диапазоне ячеек с данными, то есть количество контрактов. В оператореReDim nazv(1 To m)размер массива nazv устанавливается в соответствии с количеством контрактов (т.е. с максимально возможным количеством городов). Группа операторов kol = 1 … Next i реализует сбор списка названий городов в массиве nazv. Сначала в массив включается название города, указанного в первой строке (для первого контракта). Для каждого последующего города выполняется проверка на его присутствие в массиве nazv; для этого его название (переменная nazvanie) сравнивается со всеми названиями, уже присутствующими в массиве nazv. Если город еще не включен в этот массив, то он добавляется в него, и количество городов в массиве (переменная kol) увеличивается на 1. В цикле For k = 1 To kol… Next kэлементы массива nazv последовательно добавляются в список Spisok_gorodov. Для добавления элементов в список используется метод AddItem. Процедура для обработки щелчка по списку городов (т.е. для вычисления суммы стоимостей контрактов по выбранному городу) может быть следующей: Private Sub Spisok_gorodov_Click() nazvanie = Spisok_gorodov.Text Set d = Range("A1").CurrentRegion m = d.Rows.Count sum = 0 For i = 1 To m If d.Cells(i, 2).Value = nazvanie Then sum = sum + d.Cells(i, 3).Value Next i Pole_summa.Value = sum End Sub Здесь в операторе nazvanie = Spisok_gorodov.Text переменной nazvanie присваивается выбранный элемент списка. Затем в диапазоне ячеек, заполненном данными, выполняется суммирование величин из столбца C (т.е. стоимостей контрактов), если соответствующая величина в столбце B (название города) совпадает со значением переменной nazvanie. Вычисленная сумма выводится в текстовое поле Pole_summa. Поля выбора ячеек Поле выбора ячеек (RefEdit) – элемент управления, позволяющий выбирать ячейки на рабочем листе Excel (аналогично тому, как это делается, например, при использовании встроенных функций в Excel). Пример 7.6 – Требуется разработать программу для сортировки наборов чисел. В программе используется форма, приведенная на рисунке 7.8. В поле выбора ячеек, имеющем подпись Диапазон, выбирается диапазон ячеек, заполненных числами, из произвольного числа строк и одного столбца. В поле выбора ячеек Вывод указывается ячейка, с которой должен начинаться вывод результатов (отсортированного набора чисел). Сортировка выполняется при нажатии кнопки Сортировать. На странице Параметры размещаются два переключателя для выбора способа сортировки: по возрастанию или по убыванию.
Для решения этой задачи следует разместить на пользовательской форме необходимые элементы управления. Сначала следует разместить элемент управления «Набор страниц» (MultiPage). Щелкнув правой кнопкой мыши по заголовку первой страницы (Page1), выбрать команду Rename и указать новое имя (в данном случае – Сортировка) в поле Caption. Аналогично переименовать вторую страницу (Page2). Разместить остальные элементы управления. На странице Сортировка разместить две надписи (Диапазон и Выход), два поля выбора ячеек (с именами Diap_sort и Diap_vyvod), две кнопки (с именами sort и vyhod). На странице Параметры разместить два переключателя (с именами Sort_po_vozr и Sort_po_ub) для выбора способа сортировки – по возрастанию или по убыванию. В процедуре инициализации формы предусмотреть, чтобы один из переключателей на странице Параметры был установлен, а другой – сброшен. Для обработки щелчка по кнопке Сортировать ввести следующую процедуру (здесь предполагается, что для этой кнопки установлено свойство Name – Sort): Private Sub Sort_click() Dim massiv() As Single Set d = Range(Diap_sort.Value) m = d.Rows.Count n = d.Columns.Count If n <> 1 Then MsgBox ("Неправильно выбран диапазон данных") Exit Sub End If Set dv = Range(diap_vyvod.Value) mm = dv.Rows.Count nn = dv.Columns.Count If (mm <> 1) Or (nn <> 1) Then MsgBox ("Неправильно указан адрес вывода") Exit Sub End If ReDim massiv(1 To m) For i = 1 To m massiv(i) = d.Cells(i).Value Next i If Sort_po_vozr.Value = True Then Call sortirovka_v(massiv, m) Else Call sortirovka_ub(massiv, m) End If For i = 1 To m dv.Cells(i, 1).Value = massiv(i) Next i End Sub Здесь в строке Dim massiv() As Single объявлен динамический массив. В операторе Set d = Range(Diap_sort.Value) переменная d связывается с диапазоном ячеек, заданном в поле выбора ячеек Diap_sort. В операторах m = d.Rows.Count и n = d.Columns.Count определяются размеры выбранного диапазона ячеек. Затем проверяется количество столбцов в выбранном диапазоне: оно должно быть равно 1. Если это не так, выводится сообщение о неправильно выбранном диапазоне данных, и выполняется выход из процедуры (Exit Sub). В операторе Set dv = Range(diap_vyvod.Value) переменная dv связывается с диапазоном, указанном в поле выбора ячеек diap_vyvod. Затем проверяются размеры этого диапазона. Он должен представлять собой в точности одну ячейку (с нее будет начинаться вывод отсортированного массива). Если это не так, то выводится сообщение о неправильно заданном адресе вывода. В оператореReDim massiv(1 To m)размер массива massiv устанавливается в соответствии с выбранным диапазоном. Затем в этот массив передается содержимое ячеек выбранного диапазона. В зависимости от того, какой из переключателей на странице Параметры установлен, вызывается одна из процедур сортировки: по возрастанию (Sortirovka_v) или по убыванию (Sortirovka_ub). В процедуру передается массив massiv, а также его размер (m). Затем в цикле содержимое отсортированного массива massiv выводится в столбец рабочего листа Excel, начиная с ячейки, указанной в полеdiap_vyvod. Процедура сортировки по возрастанию может быть следующей: Sub sortirovka_v(a, m) For i = 1 To m – 1 ‘ i-й элемент массива будет сравниваться со всеми последующими For j = i + 1 To m If a(i) > a(j) Then x = a(i): a(i) = a(j): a(j) = x ‘ Если i-й элемент оказался больше j-го, то они меняются местами End If Next j Next i End Sub Процедура сортировки по убыванию полностью аналогична приведенной процедуре и отличается от нее только знаком операции сравнения. Варианты заданий Примечание – Варианты 1-6 – простые, остальные – более сложные. Вариант 1 –Создать пользовательскую форму, содержащую следующие элементы управления: два поля выбора ячеек с надписями Диапазон и Копия; текстовое поле с надписью Столбец; счетчик; флажок Сохранить копию; список из двух элементов (Поменять местами и Заменить); кнопки Выполнить и Выход. При нажатии кнопки Выполнить в диапазоне, заданном в поле выбора ячеек Диапазон, первый столбец меняется местами со столбцом, номер которого указан в поле Столбец (если в списке выбран элемент Поменять местами), или заменяется на этот столбец (если выбран элемент Заменить). Номер столбца в текстовом поле Столбец задается с помощью счетчика. Если выполняется замена, и при этом установлен флажок Сохранить копию, то элементы первого столбца выбранной области копируются в ячейки рабочего листа, начиная с ячейки, выбранной в поле Копия. При нажатии кнопки Выход форма закрывается. Вариант 2 –Создать пользовательскую форму, содержащую следующие элементы управления: поле выбора ячеек с надписью Диапазон; два текстовых поля с надписями Число и Заменить на; два счетчика; список из двух элементов (Подсчет и Замена); кнопки Выполнить и Выход. При нажатии кнопки Выполнить в диапазоне, заданном в поле выбора ячеек Диапазон, выполняется подсчет количества вхождений числа, введенного в поле Число, или его замена на число, указанное в поле Заменить на. Числа в поля Число и Заменить на могут вводиться с клавиатуры или с помощью счетчиков. Выполняемая операция (подсчет или замена) выбирается из списка. Количество найденных или замененных чисел выводится на экран командой MsgBox. При нажатии кнопки Выход форма закрывается. Вариант 3 –Создать пользовательскую форму, содержащую следующие элементы управления: поле выбора ячеек с надписью Диапазон; два текстовых поля с надписями Строка 1 и Строка 2; два счетчика; список из двух элементов (Номера и Мин-Макс); флажок Вывод номеров; кнопки Выполнить и Выход. При нажатии кнопки Выполнить в диапазоне, заданном в поле выбора ячеек Диапазон, меняются местами две строки. Если выбран элемент списка Номера, то меняются местами строки, номера которых указаны в текстовых полях. Номера в текстовых полях устанавливаются с помощью счетчиков. Если выбран элемент Мин-Макс, то меняются местами строки, содержащие минимальный и максимальный элемент выбранной области. Если установлен флажок Вывод номеров, но номера строк, которые поменялись местами, выводятся на экран командой MsgBox. При нажатии кнопки Выход форма закрывается. Вариант 4 –Создать пользовательскую форму, содержащую следующие элементы управления: два поля выбора ячеек с надписями Исходные данные и Результаты; текстовое поле с надписью Число; счетчик; список из двух элементов (Строки и Всего); флажок Вывод номеров; кнопки Выполнить и Выход. При нажатии кнопки Выполнить в диапазоне, заданном в поле выбора ячеек Исходные данные, подсчитывается количество строк, содержащих число, указанное в поле Число, или общее количество вхождений этого числа. Значение поля Число вводится с клавиатуры или с помощью счетчика. Режим подсчета (подсчет строк или общего количества вхождений) выбирается из списка. Результат подсчета выводится на экран командой MsgBox. Кроме того, если выполняется подсчет строк, и при этом установлен флажок Вывод номеров, то в рабочий лист (начиная с ячейки, указанной в поле Результаты) выводятся номера строк, содержащих заданное число. При нажатии кнопки Выход форма закрывается. Вариант 5 –Создать пользовательскую форму, содержащую следующие элементы управления: поле выбора ячеек с надписью Диапазон; текстовое поле с надписью Строка; счетчик; флажок Вывести; список из двух элементов (По номеру и По максимуму); кнопки Выполнить и Выход. При нажатии кнопки Выполнить в диапазоне, заданном в поле выбора ячеек Диапазон, первая строка меняется местами со строкой, определяемой в зависимости от выбранного элемента списка: если выбран элемент По номеру, то используется строка, номер которой указан в поле Строка, а если выбран элемент По максимуму, то определяется строка, содержащая максимальный элемент выбранного диапазона. Номер строки в текстовом поле Строка задается с помощью счетчика. Если установлен флажок Вывести, то на экран выводятся номера и суммы строк, которые поменялись местами. При нажатии кнопки Выход форма закрывается. Вариант 6 –Создать пользовательскую форму, содержащую следующие элементы управления: два поля выбора ячеек с надписями Исходные данные и Результаты; текстовое поле с надписью Граница; счетчик; флажок Вывести средние; список из двух элементов (Граница и Общее среднее); кнопки Выполнить и Выход. При нажатии кнопки Выполнить по каждой строке вычисляется среднее. В рабочий лист (начиная с ячейки, выбранной в поле Результаты) выводятся номера строк, для которых среднее превышает заданную величину. В качестве такой величины используется число, заданное в поле Граница (если в списке выбран элемент Граница) или среднее, вычисленное по всему выбранному диапазону (если в списке выбран элемент Общее среднее). Значение поля Граница вводится с клавиатуры или с помощью счетчика. Если при этом установлен флажок Вывести средние, то вместе с номерами строк выводятся и их средние (в соседний столбец). При нажатии кнопки Выход форма закрывается. Вариант 7 –На рабочем листе Excel в столбце A введены фамилии студентов, в столбцах B-E – их оценки по четырем экзаменам. Эти данные отображаются в списке на пользовательской форме (см. рисунок 7.9). Должна быть предусмотрена возможность выбора нескольких студентов из списка. При нажатии на кнопку Вывести на рабочий лист Excel должны выводиться фамилии студентов, выбранные в списке, или фамилии студентов с максимальными значениями среднего балла (в зависимости от состояния переключателя). Если выбран вывод лучших студентов, то их количество задается в текстовом поле с помощью счетчика. Вывод выполняется на рабочий лист, начиная с ячейки, указанной в поле выбора ячеек Вывод.
Рисунок 7.9 – Пользовательская форма для варианта задания 7 Вариант 8 –На рабочем листе Excel в столбце A введены номера контрактов, в столбце B – названия товаров (поставляемых по контрактам), в столбце C – количество товара, в столбце D – цена товара (за единицу). В списке на пользовательской форме (см. рисунок 7.10) должен отображаться перечень товаров, поставляемых по контрактам, причем каждый товар должен быть указан в списке только один раз. Должна быть предусмотрена возможность выбора нескольких товаров из списка. При нажатии на кнопку Вывести на рабочий лист Excel должны выводиться суммы стоимостей контрактов по каждому из выбранных товаров, а также (в зависимости от настройки флажков на странице Настройки) количество контрактов и количество единиц товара (по каждому из выбранных товаров). Вывод выполняется на рабочий лист, начиная с ячейки, указанной в поле выбора ячеек Вывод.
Рисунок 7.10 – Пользовательская форма для варианта задания 8 Вариант 9 –На рабочем листе Excel в столбце A введены номера контрактов, в столбце B – названия поставляемых по контрактам товаров, в столбце C – стоимость контрактов. В списке на пользовательской форме (см. рисунок 7.11) должен отображаться перечень товаров, поставляемых по контрактам, причем каждый товар должен быть указан в списке только один раз.
Рисунок 7.11 – Пользовательская форма для варианта задания 9 Должна быть предусмотрена возможность выбора нескольких товаров из списка. При нажатии на кнопку Вывести на рабочий лист Excel должны выводиться суммы стоимостей контрактов по каждому из выбранных товаров или перечень этих контрактов (в зависимости от состояния переключателя). Вывод выполняется на рабочий лист, начиная с ячейки, указанной в поле выбора ячеек Вывод. Вариант 10 –На рабочем листе Excel в столбце A введены фамилии студентов, в столбцах B-E – их оценки по четырем экзаменам. Эти данные отображаются в списке на пользовательской форме (см. рисунок 7.12). Должна быть предусмотрена возможность выбора нескольких студентов из списка. При нажатии на кнопку Вывести на рабочий лист Excel должен выводиться перечень студентов, выбранных в списке, или студентов со средним баллом, превышающим величину в поле Минимальный балл(в зависимости от состояния переключателя). Вывод выполняется на рабочий лист, начиная с ячейки, указанной в поле выбора ячеек Вывод. Для размещения переключателей и поля выбора ячеек использовать элемент управления Рамка (Frame).
Рисунок 7.12 – Пользовательская форма для варианта задания 10 Вариант 11 –На рабочем листе Excel в столбце A введены номера контрактов, в столбце B – названия заказчиков (в каждом контракте – один заказчик), в столбце C – стоимости контрактов. В списке на пользовательской форме (см. рисунок 7.13) должен отображаться перечень заказчиков, причем каждый заказчик должен быть указан только один раз.
Рисунок 7.13 – Пользовательская форма для варианта задания 11 Должна быть предусмотрена возможность выбора нескольких заказчиков из списка. При нажатии на кнопку Вывести результаты на рабочий лист Excel должен выводиться перечень всех заказов выбранных заказчиков или крупнейший (по стоимости) заказ каждого из этих заказчиков (в зависимости от состояния переключателя). Кроме того, если установлен флажок Итоги, то для каждого из выбранных заказчиков должно выводиться общее количество заказов и их общая стоимость. Вывод выполняется на рабочий лист, начиная с ячейки, указанной в поле выбора ячеек Вывод. Вариант 12 –На рабочем листе Excel в столбце A введены фамилии работников, в столбце B – названия подразделений предприятия, в которых они работают, в столбце C – заработные платы. В списке на пользовательской форме (см. рисунок 7.14) должен отображаться перечень подразделений предприятия, причем каждое подразделение указывается только один раз.
Рисунок 7.14 – Пользовательская форма для варианта задания 12 Должна быть предусмотрена возможность выбора нескольких подразделений из списка. При нажатии на кнопку Выполнить на рабочий лист Excel должны выводиться данные о персонале подразделений предприятия: количество работников, а также средняя и максимальная зарплата по подразделению (если установлены соответствующие флажки). В зависимости от состояния переключателя выводятся данные по подразделениям, выбранным в списке, по подразделениям с количеством работников не меньше заданного (это количество устанавливается с помощью счетчика) или по всем подразделениям. Вывод выполняется на рабочий лист, начиная с ячейки, указанной в поле выбора ячеек Вывод. ЛАБОРАТОРНАЯ РАБОТА №8 программЫ на vba для работы Цель работы – Освоение разработки программ на VBA для работы со строковыми данными и с текстовыми файлами. 
Не нашли, что искали? Воспользуйтесь поиском по сайту: ©2015 - 2024 stydopedia.ru Все материалы защищены законодательством РФ.
|
