Основы офисного программирования и язык VBA
         

Задача о медиане


Для массива M и элемента Cand вычислить разность между числом элементов массива M, больших и меньших Cand.

Это вариация задачи о медиане - "среднем" элементе - массива. Медиану можно определить, например, таким алгоритмом: упорядочив массив, взять элемент, находящийся в середине. Есть и более эффективные алгоритмы. Но мы решили ограничиться более простой задачей - проверкой на "медианность". Заметим: если все элементы массива M различны и число их нечетно, то для медианы искомая в задаче разность равна 0. В общем случае, значение разности является мерой близости параметра Cand к медиане массива M. Но займемся программистскими аспектами этой задачи. У функции, ее реализующей, на входе - массив, а на выходе - скаляр. Мы хотели бы, чтобы эта функция могла вызываться в формулах рабочего листа, а в качестве фактического параметра ей могли быть переданы как объект Range, так и массив Visual Basic. Вот как мы реализовали эту функцию, назвав ее IsMediana:

Пример 9.1.

(html, txt)

Прокомментируем работу функции IsMediana.

  • Функция IsMediana может (и будет) вызываться как из процедур VBA, так и из рабочих формул листа Excel. Обратите внимание, она работает с объектами Office 2000 - Range, Cells, Rows и другими.
  • Функции, чьи аргументы имеют универсальный тип Variant, целесообразно строить по принципу разбора случаев. Алгоритм обработки зависит от типа фактического параметра, задаваемого в момент вызова.
  • Стандартная функция TypeName(V) возвращает в качестве результата конкретный тип параметра V.
  • Работа функции IsMediana(M,Cand) начинается с вызова TypeName(M). Далее разбираются четыре возможных случая: M - объект Range, M - массив типа Variant(), M - настоящий целочисленный массив VBA, M имеет любой другой тип.
  • В первом случае функция IsMediana вызывается в формуле рабочего листа Excel и в качестве фактического параметра ей передается объект Range - интервал ячеек этого листа. Следовательно, функция TypeName возвратит строку "Range" в качестве результата.
    При обработке этого случая организуется цикл по числу строк и столбцов объекта Range, используя свойство Cells этого объекта.
  • Во втором случае обработка основана на том, что функции передан массив типа Variant(). Это возможно, когда при вызове нашей функции в формуле рабочего листа ей передается константа, задающая массив. Ниже мы приведем примеры подобного вызова. Для таких массивов не определены функции границ UBound и LBound. Поэтому обработка в этом случае основана на использовании цикла For Each.
  • В третьем случае функция получает при вызове обычный массив VBA и обработка идет стандартным для массивов способом. Мы приведем пример вызова нашей функции из обычной процедуры VBA, передающей в момент вызова целочисленный массив.
  • В четвертом случае, когда наш параметр не является ни массивом, ни объектом Range, в качестве результата по умолчанию выдается 0. Но выдается также и окно сообщений с предупреждением о возникшей ситуации.

Начнем с того, что приведем процедуру VBA, вызывающую нашу функцию. Вот ее текст:
Public Sub TestIsMediana() Const Size = 7 Dim Mas(1 To Size) As Integer Dim Cand As Integer Dim i As Integer Dim Res As Integer 'Инициализация массива целыми в интервале 1-20 Debug.Print TypeName(Mas) Randomize For i = 1 To Size Mas(i) = Int(Rnd * 21) Next i Cand = Int(Rnd * 21) Res = IsMediana(Mas, Cand) Debug.Print "Массив:" For i = 1 To Size Debug.Print Mas(i) Next i Debug.Print "Кандидат:", Cand Debug.Print "Результат:", Res End Sub
Вот результаты ее работы:
Массив: 3 8 14 0 3 8 2 Кандидат: 2 Результат: 4
В данном варианте вызове анализ типа переданного параметра показал, что он является обычным массивом, соответственно был выбран третий вариант обработки, не требующий работы с объектами Office 2000.
Теперь покажем, что эту же функцию можно вызывать в формулах рабочего листа Excel, передавая ей в момент вызова объекты Range в разной форме, а также массивы, заданные константой - массивом. Посмотрим, как это выглядит на экране, и разберем примеры нескольких различных вызовов функции IsMediana в формулах рабочего листа:



увеличить изображение
Рис. 9.1.  Вызов функции IsMediana в формулах рабочего листа
На рабочем листе мы сформировали два массива: вектор M, вытянутый в виде столбца, и прямоугольную матрицу N. Вектор M записан в ячейках C6:C11, матрица N - в F5:I6. В ячейки E8:E15 мы поместили формулы, вызывающие функцию IsMediana. Они не являются формулами над массивами, несмотря на то, что параметром может быть массив рабочего листа. Важно, что результат - скаляр. Если бы результат, возвращаемый функцией, был массивом, формулу следовало бы вызывать как формулу над массивами. Для скалярного результата это не так.
В двух первых вызовах функции IsMediana (в ячейках E8, E9) передается в качестве параметров имя массива рабочего листа "M" и разные кандидаты: 7 и 8. Они оба годятся на роль медианы этого массива. В следующих двух вызовах проверяются кандидаты на медиану массива N. Как видите, оба кандидата 4 и 3 одинаково близки к медиане. Следующие два вызова в ячейках E12 и E13 демонстрируют возможность указания непосредственно диапазона ячеек в момент вызова, что позволяет, например, работать с частью массива. В следующем вызове вообще не используются в качестве входных данных элементы рабочего листа. Входным параметром M является массив - константа, заключенный в фигурные скобки, а его элементы разделяются символом ";". В этих случаях фактический параметр уже не является объектом Range, а имеет тип массива с элементами Variant. Поэтому и функция IsMediana будет работать по-другому, в отличие от предыдущих вызовов. Разбор случаев в зависимости от результата, возвращаемого функцией TypeName, приведет к выбору второго варианта. Наконец, вызов, записанный в формуле из ячейки E15, демонстрирует случай, когда входной параметр M - обычное число и, следовательно, не является ни объектом Range, ни массивом. Как следствие, разбор случаев в функции IsMediana приводит к четвертому варианту и появлению на экране окна сообщений.


Следующие два вызова в ячейках E12 и E13 демонстрируют возможность указания непосредственно диапазона ячеек в момент вызова, что позволяет, например, работать с частью массива. В следующем вызове вообще не используются в качестве входных данных элементы рабочего листа. Входным параметром M является массив - константа, заключенный в фигурные скобки, а его элементы разделяются символом ";". В этих случаях фактический параметр уже не является объектом Range, а имеет тип массива с элементами Variant. Поэтому и функция IsMediana будет работать по-другому, в отличие от предыдущих вызовов. Разбор случаев в зависимости от результата, возвращаемого функцией TypeName, приведет к выбору второго варианта. Наконец, вызов, записанный в формуле из ячейки E15, демонстрирует случай, когда входной параметр M - обычное число и, следовательно, не является ни объектом Range, ни массивом. Как следствие, разбор случаев в функции IsMediana приводит к четвертому варианту и появлению на экране окна сообщений.

Содержание раздела