Тверской государственный университет
Опубликован: 13.09.2006 | Доступ: свободный | Студентов: 5132 / 387 | Оценка: 4.23 / 3.83 | Длительность: 28:12:00
Специальности: Программист, Менеджер
Лекция 2:

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

Задачи с массивами и программирование

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

Задача 9 Транспонирование матриц

Постановка задачи: Транспонировать матрицу A: B= AT

Наша конкретная задача - транспонирование матрицы на новое место - решается сравнительно просто, поскольку для этого есть стандартная функция ТРАНСП(X). В английской версии и при программировании на VBA она называется Transpose. Можно, например:

  • ввести прямоугольную матрицу в область рабочего листа, скажем, в ячейки B34:D35;
  • дать имя этой области, например, " MatrixA ";
  • выделить область ячеек с другой ориентацией для транспонированной матрицы, например A37:B39;
  • ввести в эту область формулу над массивами: " {=ТРАНСП(MatrixA)} "

Можете убедиться, эти действия приводят к желаемому результату:

Два решения в задаче транспонирования матрицы

Рис. 2.4. Два решения в задаче транспонирования матрицы

Хорошо, что есть функция ТРАНСП. Но можно ли справиться с этой классической задачей, не прибегая к вызову стандартной функции? Эта задача имеет различные вариации: матрица может быть квадратной или прямоугольной, транспонировать ее можно на месте (для квадратных матриц) или получать результат, как новую матрицу. При транспонировании на месте возникают циклические ссылки, для прямоугольных матриц появляется проблема различной ориентации исходной матрицы и результата. Но даже в самом простом случае транспонирования квадратной матрицы на новое место при построении собственного решения написать одну или несколько формул в ячейках рабочего листа, решающих эту задачу для матриц произвольного размера, не удается. В отличие от приведенного в предыдущей главе примера с сигнальной матрицей здесь требуется оперировать с индексами элементов, а формулы над массивами этого не допускают. Выход в том, чтобы написать подобную функцию на VBA, вызов которой приведет к желаемому эффекту. Давайте создадим собственную функцию Trans, работающую, как и стандартная функция ТРАНСП. У функции Trans один аргумент. При вызове в качестве фактического параметра ей передается массив рабочего листа (объект Range ), заданный в виде диапазона ячеек или своим именем. Этот массив (прямоугольная матрица) транспонируется, и новая матрица возвращается как результат. Она должна быть записана в выделенную в момент вызова область ячеек, отведенных для транспонированной матрицы. Функция Trans, как и функция ТРАНСП, должна вызываться в формуле над массивами и, следовательно, связывается с каждой ячейкой выделенной области.

Итак, с программистской точки зрения, нам нужно написать функцию от одного параметра, тип которого должен допускать при вызове массивы рабочего листа (объекты Range ) и такой же массив возвращать как результат функции. Каким же должен быть тип параметра и тип функции? Если Вы внимательно следили за предыдущими примерами, то понимаете, что таковым является тип Variant - этот универсальный тип успешно работает в данной ситуации. Остальное - дело техники. Вот текст функции Trans, решающий нашу задачу:

Public Function Trans(A As Variant) As Variant
	'Функция предназначена для транспонирования массивов
	'рабочего листа Excel - объектов Range.
	'При работе с переменной A используются свойства объекта Range

	Dim B() As Variant
	Dim i As Integer, j As Integer
	'Динамическому массиву В выделяется память
	ReDim B(1 To A.Columns.Count, 1 To A.Rows.Count) As Variant
	For i = 1 To A.Rows.Count
	For j = 1 To A.Columns.Count
		B(j, i) = A.Cells(i, j)
	Next j
	Next i
	'Массив B возвращается в качестве результата функции Trans.
	Trans = B
End Function

Нам остается сделать несколько замечаний:

  • Параметр и функция имеют тип Variant, согласуемый как с массивом, так и объектом Range.
  • Чтобы получить транспонированную матрицу, нам понадобился динамический массив B. Оператор ReDim в процедуре Trans позволяет массиву получить память, - его размеры можно определить, зная свойства объекта Range, задающего входной массив A.
  • Операция транспонирования реализуется двойным циклом.
  • На последнем шаге работы функции ее значением становится сформированный массив B. Он и будет записан в выделенную область памяти рабочего листа.

Заметьте, на предыдущем рисунке показаны оба варианта решения задачи транспонирования матрицы с использованием стандартной функции ТРАНСП и собственной функции Trans.

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

ТрансТакНельзя(A As Variant, B As Variant) As Boolean

Эта функция получала бы два массива A и B - входной и результирующий. Транспонированная матрица (массив B ) получалась бы как побочный эффект. Результатом работы функции могло бы быть значение True в случае успеха работы и False в противном случае. Такая схема, возможная в других языках и в том числе на VBA недопустима, когда функция вызывается в Excel. Пользовательские функции, вызываемые в формулах ячеек рабочего листа, не допускают побочного эффекта, приводящего к изменению массивов на рабочем листе. Единственная возможность - передать массив как результат пользовательской функции, вызываемой в формуле над массивами. Об этой особенности Excel я уже говорил ранее, но не грех напомнить еще раз об этой важной специфике машины вычислений Excel.

Пользовательские функции и массивы рабочего листа

Учитывая важность проблемы передачи массивов рабочего листа в пользовательскую функцию, кратко сформулируем основные выводы еще раз:

  • Пользовательская функция, вызываемая в формуле рабочего листа, может принимать один или несколько массивов в качестве своих входных параметров. Эти параметры должны иметь тип Variant.
  • На выходе функция может сформировать только один массив, который можно передать в рабочий лист. Этот массив не может быть значением одного из параметров функции - только результатом этой функции, т. е. побочный эффект не допускается. Всякая попытка вызвать функцию с побочным эффектом обнаруживается динамически (но не в период трансляции) и приводит к ошибке при вычислении результата.
  • Пользовательская функция, возвращающая массив рабочего листа, должна иметь тип Variant.

Ряд важных вопросов требует специального рассмотрения. Вот лишь некоторые:

  • Что собой представляют формулы, вызывающие пользовательские функции, когда те получают массивы рабочего листа как параметры, но их результатом является скаляр? Вызываются ли они как формулы с массивами или как обычные формулы?
  • Я уже сказал, что в пользовательскую функцию можно передать массив рабочего листа, для чего необходимо, чтобы соответствующий формальный параметр имел тип Variant. Но Variant является универсальным типом, а посему можно передавать не только отдельные ячейки и массивы. Что можно передавать в качестве фактического параметра, как при этом построить обработку? Ответы на эти вопросы я дам при рассмотрении следующей задачи.
Ольга Гафарова
Ольга Гафарова

Добрый день. Подскажите формулы при решении задачи на рис. 2.2 в лекции №2. Закон Ома, какие должны использоваться формулы для I и R

Курс: Основы офисного программирования и документы Excel

Серегй Лушников
Серегй Лушников