Excel для математиков

Задача 10 Нахождение медианы

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

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

Public Function IsMediana(M As Variant, Cand As Variant) As Integer
	'Дан массив M и элемент Cand. В качестве результата возвращается
	'разность между числом элементов массива M, больших и меньших Cand.
	'Тем самым можно определить близость Cand к медиане массива M.
	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
	Else
		MsgBox ("При вызове функции: IsMediana(M,Cand)" & _
		vbCrLf & "M не является массивом или объектом Range!")
	End If

End Function

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

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

Посмотрим, как это выглядит на экране, и разберем примеры нескольких различных вызовов функции IsMediana в формулах рабочего листа:

увеличить изображение
Рис. 2.5. Вызов функции IsMediana в формулах рабочего листа

На рабочем листе сформированы два массива: вектор M, вытянутый в виде столбца, и прямоугольная матрица N. Вектор M записан в ячейках C6:C11, матрица N - в F5:I6. В ячейки E8:E16 я поместил формулы, вызывающие функцию IsMediana. Они не являются формулами над массивами, несмотря на то, что параметром может быть массив рабочего листа. Важно, что результат - скаляр. Если бы результат, возвращаемый функцией, был массивом, формулу следовало бы вызывать как формулу над массивами. Для скалярного результата это не так.

Хочу обратить внимание на то, что Excel не последователен в этом вопросе. Пользовательская функция, написанная на VBA, аргументы которой являются массивами, но результат которой есть скаляр, может вызываться, как я сказал чуть выше, в обычных формулах рабочего листа. Что касается вызова встроенных функций, то тут ситуация сложнее. Если функции, возвращающей скаляр, передается один массив, то она может вызываться как обычная функция. Если же ей передается несколько массивов, то такой вызов уже должен быть формулой над массивами. Вот классический пример двух вызовов:

=СУММ(A1:D1)
{=СУММ(A1:D1 +A2:D2)}

Первый вызов работает нормально, а второй должен быть обязательно формулой над массивами, чтобы результатом действительно была сумма всех элементов двух объектов Range.

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

Пользовательские функции, принимающие сложный объект Range

Известно, что объект Range может представлять несмежную область и являться объединением нескольких интервалов ячеек. Иначе говоря, один объект Range может задавать несколько массивов рабочего листа. Можно ли такой объект передать пользовательской функции и, если да, то как его обрабатывать? Ответ: "можно", хотя соответствующий формальный параметр следует описывать особым образом. Процедуры и функции VBA допускают произвольное число параметров, это достигается за счет того, что один, последний по счету формальный параметр может иметь спецификатор ParamArray. В этом случае данный параметр задает фактически массив параметров с произвольным числом элементов. Именно эта техника и применяется для передачи в пользовательскую функцию сложного объекта Range, представляющего не один, а произвольное число массивов. У такой функции последний параметр должен иметь спецификатор ParamArray и быть массивом типа Variant.

Рассмотрим предыдущую задачу, немного усложнив ее, полагая, что при проверке кандидата на "медианность" используется произвольное число массивов. Вот как выглядит функция, решающая эту задачу:

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

Комментируя работу этой функции, отмечу:

• Формально эта функция по-прежнему имеет два параметра Cand и M. Правда, теперь они поменялись местами, и параметр M стал последним. Фактически у этой функции теперь произвольное число параметров, поскольку параметр M, сохранив тип Variant, стал массивом параметров. Спецификатор ParamArray подчеркивает, что это специальный массив с произвольным числом элементов.
• Для работы с массивом M используется цикл типа For Each. В цикле выделяется очередной элемент Elem типа Variant, а дальше используется уже знакомый по функции IsMediana алгоритм проверки элемента Cand.
• Разбор случаев делается независимо для каждого из элементов массива M. Это значит, что на входе могут быть заданы одновременно как массивы рабочего листа, так и массивы констант Visual Basic.

На том же рабочем листе, где проводились эксперименты с формулами, вызывающими функцию IsMediana, я записал еще несколько формул, вызывающих функцию IsMedianaForAll. В этих формулах аргумент может иметь сложный вид, допуская, по существу, сколь угодно большое число параметров.

увеличить изображение
Рис. 2.6. Вызов функции IsMedianaForAll, допускающей сложные объекты Range

Проанализируем четыре сделанных вызова:

• =IsMedianaForAll(7;M;N). В этом вызове наш кандидат - число 7 - проверяется по отношению к объединению двух массивов рабочего листа, заданных своими именами M и N. Формальных параметров у функции два, а фактических при вызове задается три. Два последних можно рассматривать как сложный объект Range, представляющий несмежную область ячеек, задающую объединение вектора M и матрицы N. С программистской точки зрения, можно полагать, что передается массив с произвольным числом элементов, где каждый из них в свою очередь является массивом. Такой фактический параметр является допустимым значением формального параметра нашей функции, имеющего спецификатор ParamArray.
• =IsMedianaForAll(4,5;N;M). В этом вызове мало нового в сравнении с предыдущим. Изменен порядок следования массивов N и M, изменен кандидат, - им стало число 4.5, не входящее ни в один из массивов. Как показывает результат, это число является медианой объединенных массивов.
• =IsMedianaForAll(7; {4;7;2}; {9;12;5}). Здесь в роли аргументов выступают массивы Visual Basic, заданные в виде констант, заключенных в фигурные скобки. Фактическое значение параметра M в этом случае представляет массив из двух элементов, каждый из которых в свою очередь является массивом.
• =IsMedianaForAll(7; {4;7;2}; {9;12;5}; M). Ситуация в этом вызове сложнее, так как число аргументов возросло, но, что более важно, среди них есть как массивы Visual Basic, так и массив рабочего листа - вектор M. Тем не менее, все работает правильно.