Информатика VBA. Учебное пособие по дисциплинам Информатика иИнформационные технологии
 Скачать 1.05 Mb.
|
|
6.3 Лабораторная работа №2 Обработка массивов данных на VBA В лабораторной работе №2 одно задание. Задание для лабораторной работы №2. Составить программу для определения среднего арифмети- ческого положительных чисел, расположенных в первых пяти строках и первых трех столбцах рабочего листа Excel. Вывести положительные числа в файл с именем «Res2.txt». Записать по- ложительные числа в одномерный массив, отсортировать их по возрастанию и вывести полученный массив данных в файл «Res2.txt » и в первый столбец рабочего листа Excel, начиная с 11- ой строки. Сохранить рабочую книгу Excel в файле с названием Lab 2, а программу в файле Prog2. Последовательность действий для выполнения лабораторной работы. 1 , 0 ; 2 2 0 при 24 1 0 1 при 1 1 при 1 3 3 = ∆ ≤ ≤ − > ≤ ≤ − + − < − − = x x x x x x x x S 0 0,2 0,4 0,6 0,8 1 1,2 -2 -1,6 -1,2 -0,8 -0,4 0 0,4 0,8 1,2 1,6 2 1 , 0 ; 2 2 0 при ) 2 ( sin 0 1 при 1 при 2 2 2 3 = ∆ ≤ ≤ − > ≤ ≤ − − < + = x x x x x x x x V π 0 0,2 0,4 0,6 0,8 1 1,2 -2 -1,6 -1,2 -0,8 -0,4 0 0,4 0,8 1,2 1,6 2 102 1. Запустите Excel. На первом листе рабочей книге наберите тестовые данные для отладки программы (например, так как показано на ри- сунке 6.4). Сохраните рабочую книгу Excel в своей папке под име- нем Lab2. Рисунок 6.4 - Пример тестовых данных для отладки программы 2. Вызвать редактор макроса VBA (комбинация клавиш Alt+Fl1) и набрать текст программы: Sub Prog2() Dim Mas() As Variant ' переменная Mas - одномерный массив Dim S As Single ' Объявляем рабочие переменные Dim Kol As Integer, i As Integer, j As Integer S = 0 ' переменная для вычисления суммы положительных чисел Kol = 0 'переменная для вычисления количества положительных чисел For i = 1 To 5 Step 1 ' цикл для перебора первых пяти строк листа Excel For j = 1 To 3 Step 1 ' цикл для перебора первых трех столбцов листа If Cells(i, j) > 0 Then ' если число в ячейке положительное, то S = S + Cells(i, j) : Kol = Kol + 1 ' добавляем его к сумме End If ' и увеличиваем количество положительных чисел на единицу Next j ' заканчиваем цикл по столбцам Next i ' заканчиваем цикл по строкам ' выводим на лист ответ 103 Cells (1,5)="Сумма положительных чисел": Cells(1, 10) = S Cells (2, 5)="Количество положительных чисел": Cells(2, 10) = Kol If Kol = 0 Then ' если на листе нет положительных Cells (3, 5) = "Ошибка в данных" ' чисел - сообщаем об ошибке и Exit Sub ' завершаем работу программы End If ' завершаем If Kol = 0 Then 'на листе есть положительные числа - продолжаем работу Cells(3, 5) = "C реднее арифметическое" Cells(3, 10) = S / Kol ' выводим значение среднего арифметического Open "Res.txt" For Output As #1 ' открываем файл для вывода ' Примечание. ' Так как в имени файла путь не указан, то он будет ' сохраняться в папке "Мои документы" ' Чтобы файл сохранился в папке с книгой Excel, необходимо ' перед запуском программы выполнить сохранение ' файла с книгой Excel в свою папку ' вывод поясняющего текста в файл Res.txt #1, "Положительные числа первых пяти строк и первых трех столбцов" ' резервируем место в оперативной памяти для массива Mas ReDim Mas(Kol – 1) ' первый элемент массива в VBA имеет индекс 0 k = -1 For i = 1 To 5 Step 1 ' просматриваем строки и столбцы заново For j = 1 To 3 Step 1 If Cells(i, j) > 0 Then ' если число положительное, k = k + 1: Mas(k) = Cells(i, j) ' записываем его в массив Print #1, Mas(k) ' и выводим в файл Res.txt End If Next j Next i 104 ' Сортировка массива Mas по возрастанию For i = 0 To k - 1 Step 1 For j = 1 To k - i Step 1 If Mas(j - 1) > Mas(j) Then ' если слева число больше чем справа, r = Mas(j) ' меняем их местами Mas(j) = Mas(j - 1) Mas(j - 1) = r End If Next j Next i ' Вывод массива Mas в файл и на текущий рабочий лист #1, "Отсортированный массив положительных чисел" Cells (10, 1) = "Отсортированный массив положительных чисел" For i = 0 To k Step 1 Print #1, Mas(i) ' вывод элемента массива в файл Cells(i + 11, 1) = Mas(i) ' вывод элемента массива в столбец Excel Next i Close ' закрываем файл вывода End Sub 3. Сохраните рабочую книгу Excel в своей папке под тем же именем - Lab2. 4. Запустите макрос «Prog2» на выполнение. Вид экрана после вы- полнения программы приведен на рисунке 6.5. Содержание тексто- вого файла «Res.txt» приведено на рисунке 6.6. Примечание: чтобы файл «Res.txt» был доступен для просмотра, необходимо закрыть книгу Excel с макросом, или в редакторе макро- сов выполнить команду «Reset» меню «Run» (можно использовать панель инструментов). 105 Рисунок 6.5 - Вид рабочего листа Excel после выполнения программы Положительные числа первых пяти строк и первых трех столб- цов 5 5 5 1,5 8,5 Отсортированный массив положительных чисел 1,5 5 5 5 8,5 Рисунок 6.6 - Текстовый файл «Res.txt», полученный после выполнения программы Prog2 5. В тестовых данных на рабочем листе замените положительные числа нулями и заново запустите макрос на выполнение. Вид экра- на после выполнения программы с такими числами приведен на рисунке 6.7. 106 Рисунок 6.7 - Вид рабочего листа Excel после выполнения программы, если в исходных данных нет положительных чисел 6.4 Задание к расчетно-графической работе №2 Подготовка к выполнению к расчетно-графической работы: - ознакомиться с заданием для выполнения работы в Excel. Соста- вить тестовые данные для отладки программы и записать их в от- чет. Продумать структуру входных и выходных данных; - написать макрос для задания своего варианта; - выполнить расчетно-графическую работу на компьютере, соблю- дая последовательность действий, аналогичную выполнению лабо- раторной работы 2. Файлу Excel дать имя «RGR_2», макрос с про- граммой сохранить в файле «RGR_Prog_2»; - в отчет перенести вид экрана с тестовыми данными и результатом работы программы. Варианты заданий 1-15. Подготовить в соответствии с вариантом задания таблицу со списком наименований (фамилий) и значений необходимых полей. Подготовить макрос, в котором для указанного (среди его операто- 107 ров) наименования (фамилии) вычислить и записать в ячейки значе- ния требуемых параметров. 1. В списке (с колонками: наименование товара, число упаковок, вес упаковки) товаров, поступивших на склад за месяц, по наимено- ванию указанного товара определить: общее число упаковок и общий вес данного товара на складе. 2. В таблице заказов для продукта, заданного своим наименованием, определить: общую потребность (объём требуемого продукта) и число заказов (заявок) на данный продукт. 3. В таблице-распечатке телефонных вызовов по номеру абонента определить: число его исходящих звонков, общую продолжитель- ность разговоров и среднюю продолжительность разговора. 4. В библиотечном списке методической литературы по названию учебной дисциплины определить: число методичек и средний объем (в страницах) одной методички по указанной дисциплине. 5. В таблице с аттестационными оценками школьников по пройден- ным предметам определить: среднюю оценку выбранного школь- ника и число предметов с аттестацией на «5». 6. В таблице с аттестационными оценками школьников по пройден- ным предметам определить: среднюю оценку по выбранному предмету и число отличником по данному предмету. 7. В списке поставок (за неделю) товаров со склада в магазины опре- делить: число магазинов, в которые поставляется указанный то- вар, а также общий объем поставляемого товара. 8. По списку повторяющихся фамилий сотрудников и часов сверх- урочной работы (в указанные дни) определить количество дней и общую сумму часов сверхурочной работы сотрудника с фамилией, указанной в этом списке. 108 9. Для указанной фамилии в месячной распечатке (по дням) сотруд- ников, опоздавших на работу, определить число дней с опозда- ниями и общее время опозданий. 10. В списке с названиями фирм и количеств легковых автомобилей этих фирм, проехавших по участку дороги в каждый из несколь- ких дней недели определить: процент всех автомобилей выбран- ной фирмы от общего числа автомобилей. 11. В списке (с колонками: наименование товара, вес, сорт) товаров, поступивших на склад за месяц, по наименованию указанного то- вара данного сорта, определить: общий вес указанного товара данного сорта (1-го или 2-го). 12. Для указанной фамилии в списке (по дням месяца) водителей ав- топредприятия, оштрафованных ГИБДД, определить: общее число нарушений правил и общую сумму штрафов. 13. Для указанной фамилии в списке спонсорских пожертвований (за год) определить: число пожертвований и среднюю сумму одного пожертвования. 14. Для указанной фамилии в распечатке (по дням) вложений (вкла- дов) на общий банковский счёт определить: общую сумму вкладов и среднюю сумму одного вклада. 15. В списке (по матчам сезона) хоккеистов, имеющих удаления в матчах, для выбранного хоккеиста определить: общее время уда- лений и общее число матчей с удалениями. Варианты заданий 16-30. 16. Составить программу для определения суммы минимального по- ложительного и максимального отрицательного чисел, располо- 109 женных в первых десяти строках и первых трех столбцах рабочего листа Excel. 17. Составить программу для определения суммы максимального и минимального элементов массива. Числа массива расположены в первых десяти строках и первых трех столбцах рабочего листа Ex- cel. 18. Определить число элементов массива, превышающих среднее арифметическое его элементов. Числа массива расположены в первых десяти строках и первых трех столбцах рабочего листа Ex- cel. 19. Определить среднее арифметическое элементов массива, имею- щих четное значение. Числа массива расположены в первых деся- ти строках и первых трех столбцах рабочего листа Excel. 20. Определить среднее арифметическое элементов массива, имею- щих нечетное значение. Числа массива расположены в первых де- сяти строках и первых трех столбцах рабочего листа Excel. 21. Определить значение индекса минимального положительного элемента массива. Числа массива расположены в первых десяти строках и первых трех столбцах рабочего листа Excel. 22. Определить количество четных элементов массива. Числа массива расположены в первых десяти строках и первых трех столбцах ра- бочего листа Excel. 23. Поменять местами элементы массива, имеющие минимальное и максимальное значения. Числа массива расположены в первых де- сяти строках и первых трех столбцах рабочего листа Excel. 110 24. Определить индексы минимального положительного и макси- мального отрицательного элементов массива. Числа массива рас- положены в первых десяти строках и первых трех столбцах рабо- чего листа Excel. 25. Определить число и сумму элементов массива, имеющих четное значе- ние. Числа массива расположены в первых десяти строках и пер- вых трех столбцах рабочего листа Excel. 26. Определить разность минимального и максимального положи- тельного элементов массива. Числа массива расположены в пер- вых десяти строках и первых трех столбцах рабочего листа Excel. 27. В массиве определить сумму индексов максимального и мини- мального элементов. Числа массива расположены в первых десяти строках и первых трех столбцах рабочего листа Excel. 28. Поменять местами минимальный положительный и максималь- ный отрицательный элементы массива. Числа массива расположе- ны в первых десяти строках и первых трех столбцах рабочего лис- та Excel. 29. Определить сумму элементов массива, которые имеют дробную часть меньше 0,5. Числа массива расположены в первых десяти строках и первых трех столбцах рабочего листа Excel. 30. Определить среднее арифметическое элементов массива, имею- щих положительные значения. Числа массива расположены в пер- вых десяти строках и первых трех столбцах рабочего листа Excel. 111 ЛИТЕРАТУРА 1. Информатика. Базовый курс: учеб. пособие для студентов втузов / [С. В. Симонович. Г. А. Евсеев. В. И. Мураховский. С. И. Бобров- ский] ; под ред. С. В. Симоновича. - Москва; Санкт-Петербург; Нижний Новгород [и др.]: Питер. 2010. - 640 с. 2. Калядин В.И., Макаров А.И. Основы работы на персональном ком- пьютере: сборник лабораторных работ. - М.;МГТУ «МАМИ», 2010. - 85с.. -58с. 3. Калядин В.И. Решение задач в Excel на МВ: учебное пособие. - М.;МГТУ «МАМИ», 201. - 58с. 4. Кузьменко В.Г. VBA эффективное использование. М. БИНОМ 2009. - 617с. 5. Антомони В.И., Архипов В.Н., Любин А.Н., Тихомиров В.Н. ПРО- ГРАММИРОВАНИЕ НА VBA В MICROSOFT OFFICE: сборник лабораторных работ по дисциплине «Информатика» для студентов всех специальностей. - М.:МГТУ «МАМИ». 2011, 160 с. 6. Антомони В.И., Архипов В.Н., Любин А.Н., Тихомиров В.Н. Осно- вы программирование на VBA в Microsoft Office: учебное пособие по дисциплинам «Информатика» и «Информационные технологии» для студентов всех направлений и специальностей. - М.:МГТУ «МАМИ». 2011. - 142 с. 7. Любин А.Н. Создание пользовательских форм в Visual Basic for Application : учебное пособие по дисциплинам «Информатика» и «Информационные технологии» для студентов всех направлений и специальностей. - М.:МГТУ «МАМИ». 2012. - 60 с. 8. Лобанов А.С., Туманова М.Б. Решение задач на языке Visual Basic for Application : учебное пособие. - М.;МГТУ «МАМИ», 2009. - 90с. 9. Лобанов А.С., Туманова М.Б. Решение задач на языке Visual Basic for Applications : учебное пособие. М. МГТУ «МАМИ», 2010. 10. Туманова М.Б. Применение Excel в решении специализирован- ных задач: учебное пособие. М. МГТУ «МАМИ», 2010. 112 Приложение А Описание интегрированной среды разработки VBA Разрабатываемое на языке Visual Basic for Application приложе- ние называется проектом. Проект включает в себя не только форму с размещенными на ней управляющими элементами, но и программные модули событийных процедур, которые описывают поведение объек- тов приложения и взаимодействие объектов между собой. Вход в интегрированную среду разработки (IDE) VBA осущест- вляется в приложении Excel командой Вид Макрос, либо «горячей» клавишей [Alt]+[F11]. Редактор VBA имеет вид, представленный на рисунке А.1 Рисунок А.1 Окно интегрированной среды разработки VBA Окно IDE VBA имеет определенное сходство с другими прило- жениями Windows. Есть заголовок, в котором, помимо имени прило- жения, видно название открытого документа, строка главного меню, строка панели инструментов (Toolbar). Область редактора исходного кода Окно отладки (Ctrl_G) Окно проекта 113 Под панелью инструментов с левой стороны - специальное окно, называемое Project Explorer – Проводник Проекта (на рисунке его за- головок Project-VBAProject). Справа – большая область, где будет происходить основная ра- бота – окно кода. Окно проекта В окне проекта (VBAProject) представлена иерархическая струк- тура файлов форм и модулей текущего проекта. В проекте автоматически создается модуль для каждого рабоче- го листа и для всей книги. Кроме того, модули создаются для каждой пользовательской формы, макросов и классов. По своему предназна- чению модули делятся на два типа: модули объектов и стандартные. К стандартным модулям относятся те, которые содержат макросы. Такие модули добавляются в проект командой Вставка, Модуль (Insert, Module). К модулям объектов относятся модули, связанные с рабочей книгой, рабочими листами, формами, и модули класса. Формы создаются командой Вставка, UserForm (Insert, UserForm), а модули класса — командой Вставка, Модуль класса (Insert, Class Module). По мере создания, добавления и удаления фай- лов из проекта эти изменения отображаются в окне проекта. Отме- тим, что удаление файла из окна проекта производится выбором значка файла с последующим выполнением команды Файл, Удалить (File, Delete). В окне проекта выводится проект всех открытых рабочих книг. Это позволяет легко копировать формы и коды из одного проекта в другой, что убыстряет процесс создания новых приложений. Главное меню Главное меню, как и во всех приложениях Microsoft, представ- ляет собой линейку раскрывающихся меню. Оно содержит следую- щие основные команды: File (Файл), Edit (Правка), View (Вид), 114 Format (Формат), Debug (Отладка), Run (Запуск), Tools (Сервис), Add- Ins (Надстройки), Window (Окно), Help (Справка). Наиболее часто используемые команды инструментальной сре- ды VBA отображены в виде кнопок со значками на панели инстру- ментов. Основные команды главного меню. |