Практикум по vba для Microsoft Excel Учебное пособие
 Скачать 2.32 Mb.
|
|
Вариант 11 1. Дан диапазон чисел. Найти в каждой строке сумму чисел, значение которых меньше последнего числа в данной строке и записать в конце каждой строки эту сумму. 75 2. В заданном диапазоне найти число по модулю максимально близкое к 0 и заменить его на 0. Вариант 12 1. Дан диапазон чисел. Найти в каждом ряду минимальное и максимальное значение и записать в конце каждого разность между этими значениями. 2. В заданном диапазоне найти количество чисел, равных по модулю 1 и заменить эти единицы на вычисленное количество. 76 Лабораторная работа № 9 Массивы Массив - это коллекция переменных, которые имеют общие имя и базовый тип. Все элементы данных, сохраняемых в массиве, должны иметь один и тот же тип. Информация, сохраненная в массиве, может быть доступна в любом порядке. Массив позволяет сохранять и манипулировать многими элементами данных посредством единственной переменной. Обработку массивов значительно упрощает использование циклов. Одномерные массивы Одномерный массив - это самый простой вариант массива, использующий обыкновенный список данных. Например: Вася, Петя, Коля, Миша, Ваня, Слава, Игорь, Юра, Саша, Вова Это строковый массив, состоящий из 10 элементов. Дадим ему название My_Array. Нумерация элементов в массиве начинается с 0. Такая система нумерации довольно распространена в программировании и называется нумерацией с нулевой базой. Для доступа к данным, хранящимся в определенном элементе массива, следует указывать имя массива с последующим числом, называемым индексом элемента. Индекс всегда заключается в круглые скобки. Например: My_Array(3) - этому элементу нашего массива соответствует "Миша" (не забывайте, что по умолчанию нумерация элементов массива начинается с 0). Поскольку нумерация с нулевой базой не очень удобна (т.к. мы привыкли считать с 1, а не с 0), то в VBA имеется директива компилятора, позволяющая исправить это "неудобство": Option Base. Директива компилятора имеет два варианта написания: Option Base 0 - индексы массивов начинаются с 0 (установка по умолчанию) Option Base 1 - индексы массивов начинаются с 1 Данная директива компилятора помещается в область объявлений модуля перед объявлениями любых переменных, констант или процедур. Нельзя помещать Option Base внутри процедуры. Можно иметь только один оператор Option Base в модуле, который влияет на все массивы, объявляемые в модуле. Многомерные массивы Одномерные массивы хорошо подходят для представления простых списков данных. Однако часто бывает необходимо представить таблицы данных в программах с организацией данных в формате строк и столбцов, подобно ячейкам в рабочих листах Excel. Для этого необходимо использовать многомерные массивы. Так адрес каждой ячейки листа состоит 77 из двух чисел, одно из которых (номер строки) является первым индексом, а второе (номер столбца) - вторым индексом массива. Такой массив называется двумерным массивом. Добавив еще номер листа, получим трехмерный массив. VBA позволяет создавать массивы, имеющие до 60 измерений. Статические и динамические массивы Массивы, не меняющие число своих элементов, называются статическими массивами. Примером такого массива может служить вышеприведенный массив My_Array, содержащий 10 элементов. Однако бывают ситуации, когда изначально неизвестно количество элементов в массиве, или же, в процессе работы это количество может изменяться. Такие массивы называются динамическими массивами. Динамический массив может увеличиваться или сжиматься, чтобы вмещать точно необходимое число элементов без напрасного расходования памяти. Объявление массивов Объявление массива с использованием оператора Dim имеет следующий синтаксис: Dim VarName ([ Subscripts ]) [As Type ] VarName - любое имя массива, использующее допустимый идентификатор имени; Subscripts - измерение массива. Если размерность массива больше единицы, то Subscripts разделяются запятыми. Оператор Subscripts имеет следующий синтаксис: [ lower To] upper [,[ lower To] upper ].. lower - определяет нижний диапазон допустимых индексов для массива (необязательный аргумент); upper - определяет верхний предел для индексов массива (обязательный аргумент). Примеры правильного объявления массивов: Dim Array_Str (1 To 10) As String - одномерный статический строковый массив, включающий 10 элементов; Dim Array_Var() - динамический массив; Dim Array_Mult (0 To 5, 0 To 7) As Integer - двумерный статический массив целых чисел, включающий 6*8=48 элементов. При объявлении массивов следует помнить, что включение оператора Subscripts в объявлении массива создает статический массив с фиксированным числом элементов, пропуск оператора Subscripts в объявлении массива создает динамический массив, а установка директивы компилятора Option Base влияет на общее число элементов в массиве. 78 Изменение размерности динамического массива Могут сложиться обстоятельства, при которых точно неизвестно, сколько элементов потребуется в массиве. В VBA имеется возможность при помощи оператора ReDim переопределять размерность массива, а во время объявления не указывать его размерность. Синтаксис ReDim: ReDim [Preserve] varname ( subscripts ) [As Type ] [, varname ( subscripts ) [As Type ]] varname - имя существующего массива; subscripts - размерность существующего массива; Type - любой тип VBA. Необходимо использовать отдельный оператор As Type для каждого массива, который определяется; Preserve - необязательный аргумент. Его использование приводит к тому, что данные уже имеющиеся в массиве, сохраняются после изменения его размерности. Примеры правильного использования оператора ReDim: Dim Array_Month() As String - одномерный строковый динамический массив ReDim Array_Month(29) - устанавливет размерность динамического массива равную 29 элементам ReDim Array_Month(1 To 30) - изменяет размер массива до 30 элемента ReDim Preserve Array_Month(1 To 31) - изменяет размер массива до 31 элемента, сохраняя содержимое Dim Array_DBL() As Single - объявляет динамический массив ReDim Array_DBL(2, 9) - делает массив двумерным ReDim Array_DBL(3, 7) - изменяет размер двумерного массива ReDim Preserve Array_DBL(1 To 3, 1 To 5) - изменяет последний размер массива, сохраняя содержимое ВАЖНО Можно изменять только последнее измерение многомерного массива, когда используется ключевое слово Preserve. Задание 1 Получить массив из 40 элементов. Элементами массива являются целые числа из интервала от 1 до 40, причем числа в массиве не повторяются. В приведенном коде массив состоит из 100 элементов. 79 Задание 2 Даны температуры за неделю. Найти день с самой низкой температурой. Задание 4 Имеется список участников кастинга с указанием полученных баллов. Получить список допущенных к конкурсу. Это участники набравшие баллы, больше среднего арифметического от всех баллов. 80 Задания для самостоятельной работы Вариант 1 1. Дан список дневных и ночных температур за неделю. Определить день, с максимальной разницей дневных и ночных температур. 2. Дан список студентов с отметками по информатике. Получить список отличников. Вариант 2 1. Дано количество абитуриентов, поступавший последние 10 лет определить год с самым большим количеством абитуриентов. 2. Дан список поваров с указанием их цены. Получить список товаров с ценой ниже средней арифметической цены. 81 Вариант 3 1. Даны среднемесячные температуры за год. Определить, сколько месяцев среднемесячная температура была отрицательной. 2. Дан список учащихся с указанием их роста. Получить список учащихся, которых рост отличается от максимального роста не более чем на 5 сантиметров. Вариант 4 1. Дано количество выпускников с красным дипломом за последние 10 лет. Определить год с самым низким количеством красных дипломов. 2. Дан список городов и указанием количества их жителей. Получить список городов с населением более миллиона. Вариант 5 1. В группе 25 студентов. Дан возраст каждого студента. Определить, сколько в группе студентов моложе 21 года. 2. Дан список коробок конфет с указанием их цены. Получить список конфет, которые отличаются от самой дорогой коробки на 50 руб. Вариант 6 1. Дано количество осадков выпавших за сутки в неделю. Найти день с максимальным количеством осадков. 2. Дан список автомобилей с указанием автопробега. Получить список с автопробегом менее 1000 км. Вариант 7 1. Даны прибыли магазина в течение года. Найти месяц, в котором прибыль была минимальна. 2. Дан список пациентов с указанием их возраста. Получить список детей поциентов. Вариант 8 1. Дано количество комнат в квартирах 20-квартирного дома. Найти количество однокомнатных квартир в этом доме. 2. Дан список блюд с указанием их калорийности. Найти блюда, у которых калорийности отличается от блюда с минимальной калорийностью на 20. 82 Вариант 9 1. Даны среднемесячные температуры за год. Определить в какое полугодие средняя температура за полугодие была больше. 2. Дан список дней с указанием температуры. Найти дни с температурой равной минимальной температурой за этот период. Вариант 10 1. Дано количество отличников в классе за каждый год обучения. Найти в какой год обучения количество отличников было максимально. 2. Дан список товаров с указанием первоначального количества и количества проданного товара. Получить список товаров, у которых количество оставшегося товара равно 0. Вариант 11 1. Даны результаты соревнований для 10 спортсменов. Определить результат спортсмена, занявшего второе место. 2. Дан список спектаклей с указанием количества проданных билетов. Подучить список спектаклей, у которых количество проданных билетов превышает 300 штук. Вариант 12 1. Дано количество осадков, выпавших за сутки во второй декаде. Найти день с максимальным количеством осадков. 2. Дан список спортсменов. участвующих в соревнованиях. Получить список спортсменов женщин. 83 Лабораторная работа № 10 Объекты и коллекции VBA является идеальным инструментом для изучения основ объектно- ориентированного программирования, так как имеет встроенные объекты (рабочие книги и листы, ячейки, документы и выделенные фрагменты текста), их свойства и методы. Объектно-ориентированное приложение организует данные и выполняемые операторы программного кода в связанные объекты, что облегчает разработку, организацию и работу со сложными структурами данных и действиями, выполняемыми над этими данными (или с ними). Каждый объект в программном приложении содержит данные и операторы, связанные вместе и образующие единый элемент. Большинство приложений содержат много различных типов объектов. В Excel объектами являются рабочие книги, листы, диапазоны данных, таблицы, графические объекты, диалоговые окна и само приложение Excel. Как и объекты реального мира, объекты VBA имеют различные присущие им качества или свойства (properties). Объекты в Excel имеют свойства, определяющие их вид и поведение: рабочий лист Excel может быть видимым или нет, строки в рабочем листе или таблице имеют свойство высоты, столбцы имеют свойство ширины и так далее. Свойства регулируют вид и поведение объекта. Чтобы изменить вид и поведение объекта, необходимо изменить его свойства. В Excel можно изменить поведение рабочего листа, изменяя свойство вычисления с автоматического на ручное, или можно изменить вид рабочего листа, задав новый цвет для текста или графики в листе. Чтобы узнать о текущем виде и поведении объекта, необходимо узнать о его свойствах. Вы можете определить диск, папку и имя рабочей книги Excel рассмотрев свойство FullName этой рабочей книги или документа. Некоторые свойства объекта можно изменять, некоторые — нет. Например, в рабочей книге Excel можно изменить имя автора книги, изменяя свойство Author, но нельзя изменить свойство Name рабочей книги. (Свойство Name рабочей книги содержит имя файла на диске и не может быть изменено без создания нового дискового файла или переименования файла рабочей книги вне Excel). Некоторые объекты имеют свойства с одними и теми же или подобными именами: объекты Application, Workbook и Worksheet в Excel — все имеют свойство Name. Каждый объект рабочего листа сохраняет данные для своих свойств внутри самого рабочего листа вместе с пользовательскими данными. [Пользовательские данные (user data) — это любые данные, сохраняемые объектом, которые поступают непосредственно от пользователя подобно данным, содержащимся в ячейках рабочего листа.] Объекты реального мира почти всегда имеют тот тип присущего им поведения или действия, который они могут выполнить. Объекты VBA также 84 имеют поведение и возможности, называемые методами (methods). Объект рабочей книги Excel, например, имеет встроенную способность добавлять к себе новый рабочий лист — метод добавления рабочих листов (называемый Add). Другой пример: документ Word имеет встроенную способность проверять орфографию текста в документе — метод проверки орфографии (называемый, как вы могли догадаться, CheckSpelling). Методы изменяют значения свойств объектов; методы выполняют также действия с данными (или над данными), сохраняемыми объектом. Методы во многом похожи на процедуры VBA, с которыми вы уже знакомы, но связаны с объектом; к методам объекта можно обратиться, только используя объект. Это может казаться сложным, но на деле все проще. Для вызова метода объекта (функции обработки данных объекта) указывается не только наименование метода, но и объект (имя), которому принадлежит метод. Одной из причин того, что объектно-ориентированное программирование стало популярной техникой проектирования, является то, что оно позволяет разработчикам программного обеспечения создавать более эффективные программы с совместно используемым кодом. Вместо сохранения отдельной копии кода для каждого метода каждого объекта VBA-объекты одного и того же типа совместно используют код своих методов. Несмотря на то, что объекты одного и того же типа совместно используют код для своих методов, метод считается частью объекта; когда вы вызываете определенный метод для конкретного объекта, метод воздействует только на объект, посредством которого вы обращаетесь к этому методу. После того как объект создан (если это — не встроенный объект), нет необходимости беспокоиться о том, как работают его методы и как объект сохраняет или манипулирует данными. Все, что нужно знать, — как указать объект и как указать метод, который необходимо использовать (или определенное свойство, которое нужно выбрать и изменить). Встроенный код для объекта VBA выполняет остальную работу автоматически. Операторы программ VBA, использующие объекты, обычно выполняют одно или несколько из следующих действий: определяют текущее состояние или статус объекта путем выборки значения, сохраняемого в определенном свойстве; изменяют состояние или статус объекта установкой значения, сохраненного в определенном свойстве; используют один из методов объекта, обеспечивая выполнение объектом одной из его встроенных задач. Например, можно определить имя активного в данный момент рабочего листа в Excel, выполняя выборку строки, сохраняемой в свойстве Name рабочего листа. (Свойство Name рабочего листа содержит имя рабочего листа, как показано на ярлычке листа.) Чтобы изменить имя рабочего листа, необходимо присвоить новую строку свойству Name этого рабочего листа. 85 Для добавления рабочего листа в рабочую книгу используется метод Add рабочей книги. Прежде чем применять свойства и методы некоторого объекта, их следует сначала определить. В операторах VBA используется следующий общий синтаксис для определения свойства или метода объекта: <Имя обьекта>.<свойство или метод> В таблице 1 приведены несколько из наиболее важных объектов (с точки зрения программиста VBA) в Excel. В таблице показано имя объекта и краткое описание этого объекта. (Excel содержит намного больше объектов, чем перечислено в табл 1.) Таблица 1 Объект Описание Application Само приложение Excel (host-приложение) Chart Диаграмма в рабочей книге Font Этот объект содержит атрибуты шрифта и стиля для текста, отображаемого в рабочем листе Name Заданное имя для диапазона ячеек рабочего листа Range Диапазон ячеек (одна или более) или именованный диапазон в рабочем листе Window Любое окно в Excel; окна используются для отображения рабочих листов, диаграмм и т.д. Workbook Открытая рабочая книга Worksheet Рабочая таблица в книге Использование свойств объектов Свойства объектов можно использовать только двумя способами: получать значение свойства или устанавливать его. Следует отметить, что не все свойства объекта изменяемы. Свойства объектов, которые нельзя из- менять, называют свойствами, доступными только на чтение (read-only); свойства, которые можно устанавливать, называют свойствами, доступными на чтение/запись (read-write). Свойства обычно содержат численные, строковые, значения типа Boolean, хотя некоторые свойства могут возвращать значения типа Object или другие типы данных. Свойства используются в выражениях так же, как любое другое значение переменной или константы. Можно присваивать значение свойства переменной, использовать свойства объектов в выражениях как аргументы к функциям и процедурам или как аргументы для методов какого-либо объекта. Чтобы присвоить некоторой переменной значение свойства объекта, используйте следующий синтаксис: Переменная=Обьект.Свойство |