KNOW INTUIT, Лекция, Excel за математици

Решаване на системи от линейни уравнения, умножение и обръщане на матрици

Задачите, изброени в заглавието, възникват доста често в различни области на дейност, които изискват използването на математически апарат. Поради тази причина библиотеката на Excel има вградени функции, които да ви помогнат да изпълните тези задачи. Вече споменах вградените функции за умножение на матрици MMULT (MMULT) и транспониране на матрици MTRANSP (MTRANSP), има и функция за намиране на обратната матрица - MINVERSE. Познавайки обратната матрица и знаейки как да умножавате матрици, не е трудно да намерите решение на системата от уравнения. Но тъй като способността за решаване на тези проблеми е част от основното образование на програмиста, смятам, че е уместно да обмислите създаването на свои собствени аналози на тези функции във VBA. В същото време това ще ви позволи да разгледате някои важни точки при създаването на дефинирани от потребителя функции, извиквани във формули на работен лист. Вече знаем много. Ние знаем как да напишем дефинирана от потребителя функция, какви ограничения са наложени на нейните параметри, така че да може да бъде извикана от формули на работен лист на Excel, като й предаваме масиви от работни листове като действителни параметри. Ние знаем как да анализираме вида на прехвърлените данни. Ние знаем как такава функция може да върне масив и да промени съдържанието на работния лист. В следващите примери ще засегна отново всички тези въпроси и ще има други въпроси, на които си струва да се обърне внимание.

Задача 11 Матричен продукт

Постановка на задача: Намерете произведение на правоъгълни матрициA*B

От това, което научихме по-рано, следва каква форма може да има заглавката на дефинирана от потребителя функция, която решава този проблем. Двата входни параметъра на функцията трябва да са от тип Variant. Върнатият тип трябва да е от същия тип.стойностна функция. Разбира се, това не е единственото възможно решение. Човек може да има един входен аргумент, използвайки спецификатора ParamArray. Такъв начин би бил единственият възможен, ако обобщим твърдението и се опитаме да създадем функция, която трябва да умножи произволен брой матрици. Но когато се умножават две матрици, е по-естествено да има два параметъра, съответстващи на тях. Така че заглавието е така:

Искам да ви покажа как да напишете обща функция, която е доста обща. Може да се извика във формули на масиви от работни листове, като му се подадат масиви от работни листове (обекти Range) като действителни параметри A и B. Но не само 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, която успешно работи като дефинирана от потребителя функция, може да изпълнява ролята и на обикновена функция със същия успех. Така че постигнах целта си, като създадох "универсална" функция. Но може да е за предпочитане да работите с MultMatr1 във VBA процедури, без да прибягвате до променливи от тип Variant. Разгледайте малката тестова функция ResArray, която написах, за да демонстрирам изрично как да върнете масив във функции на VBA.