НОУ ІНТУІТ, Лекція, Excel для математиків
Розв'язання систем лінійних рівнянь, множення та обіг матриць
Завдання, перелічені у заголовку, виникають досить часто у різних сферах діяльності, що вимагають застосування математичного апарату. Тому в бібліотеці Excel є вбудовані функції, які дозволяють вирішити ці завдання. Про вбудовані функції множення матриць МУМНОЖ (MMULT) та транспонування матриць МТРАНСП (MTRANSP) я вже згадував, є й функція для знаходження зворотної матриці – МОБРАТ (MINVERSE). Знаючи зворотну матрицю і вміючи множити матриці, знайти рішення системи рівнянь не складно. Але оскільки вміння вирішувати ці завдання входить у коло початкової освіти програміста, то вважаю доречним розглянути створення власних аналогів цих функцій на VBA . Заодно це дозволить розглянути деякі важливі моменти у створенні функцій користувача, що викликаються у формулах робочого листа. Багато чого ми вже знаємо. Знаємо, як написати функцію користувача, які обмеження накладаються на її параметри з тим, щоб її можна було викликати з формул робочого листа Excel, передаючи їй як фактичні параметри масиви робочого листа. Знаємо, як аналізувати тип переданих даних. Знаємо, як така функція може повернути масив та змінити вміст робочого листа. У наступних прикладах я ще раз торкнуся всіх цих питань, а, крім того, з'являться й інші питання, на які варто звернути увагу.
Завдання 11 Твір матриць
Постановка задачі: Знайти добуток прямокутних матрицьA*B
З того, що ми дізналися раніше, випливає, який вид може мати заголовок функції користувача, що вирішує це завдання. Два вхідні параметри функції мають бути типу Variant. Цей же тип повинен бути у повертаєтьсяфункцією значення. Звичайно, це не єдине можливе рішення. Можна мати один вхідний параметр, використовуючи специфікатор ParamArray . Такий спосіб був би єдиним можливим, якщо узагальнити постановку і спробувати створити функцію, яка повинна перемножувати довільне число матриць. Але при множенні двох матриць природніше мати і два відповідних їм параметри. Тому заголовок вийшов таким:
Я хочу показати Вам, як написати загальну функцію досить широкого призначення. Її можна буде викликати у формулах над масивами робочого листа, передаючи їй як фактичні параметри A і B масиви робочого листа (об'єкти Range). Але не тільки об'єкти Range, а й масиви констант будуть допускатися як один або обидва аргументи. Результат роботи функції буде записаний масив, виділений в момент виклику формули над масивами. Більше того, я хочу, щоб цю ж функцію можна було викликати у звичайних функціях та процедурах VBA, передаючи в момент виклику масиви VBA як аргументи. Все це, природно, ускладнить нашу функцію, але дозволить мені обговорити відмінності "звичайних" і "користувацьких функцій. З урахуванням цих зауважень наша функція виглядає так:
Зробимо кілька зауважень.
- Через те, що фактичні параметри можуть мати різну природу, доводиться аналізувати тип параметра, використовуючи функцію TypeName .
- Залежно від того, чи масивом об'єктом Range є параметр, по-різному визначаються межі масивів.
- Якщо хоча б один із аргументів не належить жодному з перерахованих типів, обчислення перериваються з видачею попереджувального повідомлення.
- Ще одна перевірка, яку я вважав обов'язковою, - перевірка на коректність завдання розмірів матриць, що перемножуються.Кінцевий користувач може легко помилитися і не дотриматись обов'язкової вимоги при множенні матриць: "число стовпців матриці A повинно збігатися з числом рядків матриці B". У цьому випадку результат не буде отримано, і буде видано попереджувальне повідомлення. Якщо користувач невірно виділить область пам'яті під результуючу матрицю, обчислення будуть йти. Щоправда, якщо ця область урізана по відношенню до необхідної, частина результатів буде втрачена. Якщо область виділена з надлишком, виводяться "зайві" результати, отримані шляхом копіювання.
- Зауважте, що сам процес обчислення результуючої матриці виконується однаково для обох типів аргументів.
- Результат виходить у динамічному масиві, що на останньому етапі роботи і стає значенням функції.
- Функцію MultMatr я використав подвійно, - викликаючи її у формулах над масивами в робочому аркуші Excel і в звичайній процедурі VBA, коли мені знадобилося отримати добуток двох матриць, представлених звичайними масивами VBA.
Погляньте, як виглядають результати деяких експериментів щодо множення матриць на робочому аркуші Excel:
На робочому аркуші я розташував три матриці різної розмірності і дав їм імена MatrA, MatrB та MatrC відповідно. Потім, викликаючи MultMatr, я отримав твори MatrA*MatrB і MatrB*MatrC, - все виконалося коректно. Спроба використати MultMatr для множення масиву констант на матрицю - *MatrC закінчилася неуспіхом, оскільки, як я говорив раніше, для масивів констант некоректно працює функція Ubound. При спробі множення MatrA*MatrC , як і належить, видалося попереджувальне повідомлення про недотримання правила розмірності матриць, що перемножуються.
"Користувацькі" та "звичайні" функції VBA
Під користувальницькоюфункцією VBA я розумію функцію, яка може бути викликана у формулах робочого листа Excel. Звичайні функції VBA можуть викликатися у функціях та процедурах VBA. Виникає природне питання, чи може одна й та сама функція одночасно бути користувальницькою та звичайною? Це ж питання може бути сформульоване і по-іншому, чи є особлива специфіка в функціях користувача? Відповідь проста - особливої специфіки немає, і та сама функція може викликатися як і формулах, і у процедурах VBA. Практично не виникає проблем, коли аргументами функції та результатом є скалярні значення. Коли ж, як і з MultMatr , аргументами і результатом є масиви, виникають певні труднощі. Ці проблеми переборні, прикладом тому служить функція MultMatr. Спробуємо розібратися, що складаються ці труднощі. Коли функції потрібно зрадити масив, то в функціях користувача при викликі їм передаються об'єкти Range , звичайним функціям - змінні, описані, як масиви VBA. Тому для забезпечення універсального характеру функції в її тілі необхідно проводити аналіз випадків, визначаючи тип параметра. Через війну зростає обсяг функції, отже, ускладнюється її розуміння. Ще одна складність пов'язана з результатом обчислень. Жодних проблем немає для формули над масивами, що викликає функцію користувача, - результат, записується в область, виділену при виклику формули. Звичайні функції VBA, як правило, не повертають масив як результат. Якщо результатом роботи є масив, то за програмування на VBA створюється процедура, а чи не функція. Справа в тому, що VBA присвоювання над масивами заборонені, тому просто неможливо привласнити масиву значення звичайної функції, що повертає масив як свій результат. Як же, спитайте Ви,MultMatr може використовуватися як звичайна функція? Тільки за рахунок невеликих хитрощів і універсального типу Variant, який може бути чим завгодно, навіть масивом. При викликі MultMatr як звичайної функції у процедурі VBA результат виклику присвоюється змінній типу Variant, - це допустимо. Потім вже з цією змінною можна працювати як з масивом, - це теж припустимо, що я продемонструю трохи пізніше. Таким чином, завжди можна написати функцію так, щоб вона служила як користувальницька і як звичайна функція. Інше питання, чи це варто робити. У такому узагальненні є свій резон, оскільки в таких випадках при виклику функції користувача їй можна передавати як аргументи не тільки об'єкти Range, але і масиви констант, що було продемонстровано при розгляді функції IsMedianaForAll. Зауважте, однак, що у функцію MultMatr передати масиви констант неможливо. Причина цього в тому, що для двовимірних масивів констант функції UBound та LBound працюють некоректно.
Підсумовуючи, зауважу, що, коли доводиться працювати з масивами, розумніше мати два варіанти - користувальницьку та звичайну функцію. Щоб чіткіше продемонструвати різницю між звичайними та функціями користувача, я написав звичайну процедуру MultMatr1 , що виконує множення матриць. Ось її текст:
Від функції MultMatr вона відрізняється тим, що в ній опущений аналіз випадків і проводиться більш ретельна перевірка коректності розмірності аргументів. Звичайно, вона в жодному разі не може бути використана як функція користувача, але зате працювати з нею в процедурах і функціях VBA з нею не те щоб простіше, але природніше. Щоб відчути різницю, я продемонструю тестову процедуру, в якій викликаються як функція MultMatr так і процедураMultMatr1.
Як бачите, функція MultMatr , що успішно працює в ролі функції користувача, з тим же успіхом може виконувати і роль звичайної функції. Отже, я виконав поставлене завдання, створивши "універсальну" функцію. Але, можливо, краще в процедурах VBA працювати з MultMatr1, не вдаючись до змінних типу Variant. Зверніть увагу на невелику тестову функцію ResArray, яку я написав, щоб у явній формі продемонструвати спосіб повернення масиву у функціях VBA.