Процедуры и функции

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

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

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

Public Function IsMediana(M As Variant, Cand As Variant) As Integer
	'Дан массив M и элемент Cand. В качестве результата возвращается
	'разность между числом элементов массива M, больших и меньших Cand.
	Dim i As Integer, j As Integer
	Dim Pos As Integer, Neg As Integer
	Pos = 0: Neg = 0
'Анализ типа параметра M
	If TypeName(M) = "Range" Then
		For i = 1 To M.Rows.Count
			For j = 1 To M.Columns.Count
				If M.Cells(i, j) > Cand Then
					Pos = Pos + 1
				ElseIf M.Cells(i, j) < Cand Then
					Neg = Neg + 1
				End If
			Next j
		Next i
		IsMediana = Pos - Neg
	ElseIf TypeName(M) = "Variant()" Then
		'TypeName is "Variant()"
		'Это массив, но не совсем настоящий, для него не определены,
		'например, функции границ: LBound, UBound.
		Dim Val As Variant
		For Each Val In M
			If Val > Cand Then
				Pos = Pos + 1
			ElseIf Val < Cand Then
				Neg = Neg + 1
			End If
		Next Val
		IsMediana = Pos - Neg
	ElseIf TypeName(M) = "Integer()" Then
		'Это настоящий массив целых VBA, для которого
		'определены функции границ.
		For i = LBound(M) To UBound(M)
			If M(i) > Cand Then
				Pos = Pos + 1
			ElseIf M(i) < Cand Then
				Neg = Neg + 1
			End If
		Next i
		IsMediana = Pos - Neg
	Else
		MsgBox ("При вызове функции:IsMediana(M,Cand)" _
			& "- M не является массивом или объектом Range!")
	End If
End Function

Прокомментируем работу функции 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 приводит к четвертому варианту и появлению на экране окна сообщений.