1. Uvod
Za epitet "čudesne" iz naslova zaslužan je John Walkenbach, ekspert MS Excel-a koga su ne bez razloga prozvali Mr Spreadsheet (Gospodin Tabela ili, pre, Gospodin Tabelarni Proračun). Da bismo se upoznali sa osnovnim pojmovima pogledajmo dakle jednu tabelu (to je ovo dole ružno što liči na Excel):
[dispmath]\begin{array}{|c|c|c|c|} \hline
\mbox{Excel} & A & B & C & D & E & F \\\hline
1 & 1 & 4 & 9 \\\hline
2 & 16 & 25 & 36 \\\hline
3 & 49 & 64 & 81 \\\hline
4 & & & \\\hline
\end{array}[/dispmath]
Priznajem, nije naročito lepa ova moja tabela, ali će koristiti za upoznavanje sa osnovnim pojmovima. Uostalom, i ubavic je pohvalio moj napredak u Latex-u, okarakterisan suptilnom lakoćom elegantnog ispisivanja razlomaka i eksponenata... Do kraja posta zvaćete me Mr Latex!
Dakle, koristiću termine polje (to je jedna ćelija kako neki kažu, od engleskog "cell", na primer oznaka ili adresa [inlmath]B3[/inlmath] je polje gde sam upisao vrednost [inlmath]64[/inlmath]), zatim niz (to je jedna vrsta ili jedna kolona polja, u matematici bi se to zvalo vektor, na primer opseg u oznaci [inlmath]A2:C2[/inlmath] je niz sa upisanim brojevima [inlmath]16[/inlmath], [inlmath]25[/inlmath] i [inlmath]36[/inlmath]) i matrica (to je pravougaona šema brojeva, na primer kvadratni opseg (i to je pravougaonik, zar ne) u oznaci [inlmath]A1:C3[/inlmath] je matrica sa upisanih svih devet brojeva u gornjoj tabeli).
Najbolje će biti da već u uvodu krenem sa jednim laganim primerom. Recimo da je potrebno da nađemo sumu korenova svih brojeva iz gornje tabele. Uradićemo to na dva načina:
[inlmath]\qquad[/inlmath]a) Preko "običnih" Excel formula to bi izgledalo otprilike ovako: u polje npr. [inlmath]E1[/inlmath] upiše se formula [inlmath]=\mathrm{SQRT}(A1)[/inlmath] i posle pritiska na [inlmath]\mathtt{ENTER}[/inlmath] dobijamo rezultat [inlmath]1[/inlmath] u tom polju. Onda "razvučemo" formulu po vrsti, sve do [inlmath]G1[/inlmath]. Nadam se da znate kako se to radi (pokazivač miša se dovede do desnog donjeg ugla polja [inlmath]E1[/inlmath], dok ne poprimi oblik krstića i onda, uz pritisnut levi taster miša, povlači sve do [inlmath]G1[/inlmath]. Sada otpustite levi taster. Niz [inlmath]E1:G1[/inlmath] ostaće obeležen, tj. uokviren crnom linijom. Sada razvlačimo ceo niz tako što "napipamo" krstić u desnom donjem uglu niza, tj. polja [inlmath]G1[/inlmath] i uz ponovo pritisnut levi taster povlačimo na dole, sve dok ne "zahvatimo" ceo opseg [inlmath]E1:G3[/inlmath]. Otpuštamo taster i dobijamo novu matricu [inlmath]3\times3[/inlmath], sa izračunatim korenovima. Sada ih još treba sabrati. To sigurno znate, sumira se opseg na taj način što npr. u polje [inlmath]D4[/inlmath] upišemo formulu [inlmath]=\mathrm{SUM}(E1:G3)[/inlmath]. Posle [inlmath]\mathtt{ENTER}[/inlmath] imamo traženi rezultat u polju [inlmath]D4[/inlmath]:
[dispmath]\begin{array}{|c|c|c|c|} \hline
\mbox{Excel} & A & B & C & D & E & F & G \\\hline
1 & 1 & 4 & 9 & & 1 & 2 & 3 \\\hline
2 & 16 & 25 & 36 & & 4 & 5 & 6 \\\hline
3 & 49 & 64 & 81 & & 7 & 8 & 9 \\\hline
4 & & & & 45 \\\hline
\end{array}[/dispmath]
[inlmath]\qquad[/inlmath]b) Preko matrične Excel formule to izgleda ovako: u polje [inlmath]D4[/inlmath] upišite [inlmath]=\mathrm{SUM}(\mathrm{SQRT}(A1:C3))[/inlmath] i pritisnite [inlmath]\mathtt{CONTROL+SHIFT+ENTER}[/inlmath] i... Gotovo!
[dispmath]\begin{array}{|c|c|c|c|} \hline
\mbox{Excel} & A & B & C & D & E & F & G \\\hline
1 & 1 & 4 & 9 & \\\hline
2 & 16 & 25 & 36 & \\\hline
3 & 49 & 64 & 81 & \\\hline
4 & & & & 45 \\\hline
\end{array}[/dispmath]
Mislim da je jasno zašto je Walkenbach ove formule nazvao čudesnim. Dobro, ovaj primer je lagan, pokazni, može se uraditi u glavi za tren oka, ali zamislite da treba nešto slično izračunati sa tabelom [inlmath]250\times1000[/inlmath]. Ja sam davno bio u toj situaciji, nisam znao za matrične formule i "razvlačio" sam do besvesti na onaj prvi način... A kako ovo zapravo radi uz još originalnih primera videćemo u sledeća dva poglavlja.
2. Matrične formule u jednom polju
[inlmath]\qquad[/inlmath]Za primenu matričnih formula najvažnije je shvatiti kako one funkcionišu. Pre svega, argument matrične formule (to je kao nezavisna promenljiva [inlmath]x[/inlmath] u matematici) nisu pojedinačna polja i vrednosti u njima, već nizovi i matrice. Najzanimljivije u svemu tome je to što ti nizovi i matrice ne moraju čak ni biti upisani u polja Excel-ovog radnog lista, već samo uskladišteni u memoriji računara. Rezultat koji daje matrična formula (kao promenljiva [inlmath]y[/inlmath] u matematici) može biti prikazan u jednom polju, nizu polja ili u matrici. U ovom poglavlju tema su nam matrične formule koje rezultat daju u jednom polju, bez ikakvih posrednih ispisivanja, proračuna i pomenutog "razvlačenja". Pogledajmo par primera:
[inlmath]\qquad[/inlmath]a) Naći sumu kvadrata odstupanja elemenata datog niza od aritmetičke sredine niza i rezultat prikazati u polju [inlmath]D4[/inlmath], uz korišćenje matrične formule:
[dispmath]\begin{array}{|c|c|c|c|} \hline
\mbox{Excel} & A & B & C & D & E & F & G \\\hline
1 & 5 & 8 & 9 & 6 & 5 & 2&7 \\\hline
2 & \\\hline
3 & \\\hline
4 \\\hline
\end{array}[/dispmath]
[inlmath]\qquad[/inlmath]Da pokažemo i "pešački" račun (sa [inlmath]\mathrm{ARSR}[/inlmath] označena je aritmetička sredina, sa [inlmath]\mathrm{REZ}[/inlmath] traženi rezultat):
[dispmath]\mbox{ARSR}=\frac{5+8+9+6+5+2+7}{7}=6;\\
\mbox{REZ}=(5-6)^2+(8-6)^2+(9-6)^2+(6-6)^2+(5-6)^2+(2-6)^2+(7-6)^2=32[/dispmath]
[inlmath]\qquad[/inlmath]U Excel-u je formula za aritmetičku sredinu [inlmath]\mathrm{AVERAGE}[/inlmath](opseg polja) i to nema veze sa matričnim formulama, ali ćemo je koristiti unutar matrične formule koju upisujemo u zahtevano polje [inlmath]D4:[/inlmath] [inlmath]=\mathrm{SUM}((A1:G1-\mathrm{AVERAGE}(A1:G1))\text{^}2)[/inlmath] uz obavezno [inlmath]\mathtt{CONTROL+SHIFT+ENTER}[/inlmath] pri čemu naravno sva tri tastera pritisnete istovremeno. Napominjem da pri tome Excel sam ubacuje vitičaste zagrade oko formule, a ne vi. Dakle, nemojte se iznenaditi kada vam posle otpuštanja sva tri tastera ispiše gore u formula-baru: [inlmath]\{=\mathrm{SUM}((A1:G1-\mathrm{AVERAGE}(A1:G1))\text{^}2)\}[/inlmath]. Dobićete rezultat:
[dispmath]\begin{array}{|c|c|c|c|} \hline
\mbox{Excel} & A & B & C & D & E & F & G \\\hline
1 & 5 & 8 & 9 & 6 & 5 & 2 & 7 \\\hline
2 & \\\hline
3 & \\\hline
4 & & & & 32 \\\hline
\end{array}[/dispmath]
[inlmath]\qquad[/inlmath]b) Naći proizvod kvadrata elemenata prethodnog niza i rezultat prikazati u polju [inlmath]D4[/inlmath], uz korišćenje matrične formule.
Ovo je sasvim lako, uz korišćenje Excelove formule za proizvod: [inlmath]=\mathrm{PRODUCT}(A1:G1\text{^}2)[/inlmath] Dobićete naravno ogroman broj, konkretno [inlmath]22861440000[/inlmath] ali nije u tome poenta. Bitno je da iz jednostavnog zapisa, evo još jednom: [inlmath]=\mathrm{PRODUCT}(A1:G1\text{^}2)[/inlmath] shvatite ogromnu prednost ovako jednostavnog i efektnog načina računanja, kao i samu suštinu matričnih formula, to jest rad sa nizovima i matricama kao argumentima. I naravno, nemojte zaboraviti [inlmath]\mathtt{CONTROL+SHIFT+ENTER}[/inlmath]
[inlmath]\qquad[/inlmath]c) Naći sumu kubova prvih [inlmath]n[/inlmath] prirodnih brojeva, pri čemu korisnik zadaje [inlmath]n[/inlmath] u polje [inlmath]A1[/inlmath].
E, ovo vas je verovatno malo iznenadilo. Sada nikakav niz ili matrica nisu zadati, radni list je prazan, osim polja [inlmath]A1[/inlmath] gde korisnik može upisati šta god želi. Dobro, ne baš sve što poželi, trebalo bi mu zabraniti da unosi nekakva slova ili negativne brojeve, ili uopšte nešto što nije prirodan broj. Ali to nam ovde nije tema. Nego da ja odmah dam formulu, pa nek' ide život:[dispmath]=\text{SUM(ROW(INDIRECT("1:"&A1))^3)}[/dispmath]
Pretpostavljam da je ovde potrebno pojašnjenje. Funkcija [inlmath]\mathrm{ROW}[/inlmath] koja zapravo daje brojeve redova na radnom listu jako je pogodna za formiranje niza prirodnih brojeva, pri čemu je taj niz samo u memoriji računara, nema ga na radnom listu. Funcija [inlmath]\mathrm{INDIRECT}[/inlmath] je neophodna jer ona radi sa tekstom pa Excel ne prilagođava reference, tj. adrese koji čine argumenti ove funkcije. Tako npr. matrična formula [inlmath]=\mathrm{ROW}(\mathrm{INDIRECT}("1:12"))[/inlmath] uvek daje prvih [inlmath]12[/inlmath] prirodnih brojeva. Znak konkatencije ([inlmath]\&[/inlmath]) je neophodan da bi se preciziralo do kog broja se "ide", a taj broj zadaje korisnik u polju [inlmath]A1[/inlmath]. Ostalo je jasno. Na primer, ako korisnik unese broj [inlmath]12[/inlmath] u polje [inlmath]A1[/inlmath] a matrična formula je smeštena u [inlmath]D4[/inlmath], imamo:
[dispmath]\begin{array}{|c|c|c|c|} \hline
\mbox{Excel} & A & B & C & D & E & F & G \\\hline
1 & 12 & \\\hline
2 & \\\hline
3 & \\\hline
4 & & & & 6084 \\\hline
\end{array}[/dispmath]
3. Matrične formule za više polja
[inlmath]\qquad[/inlmath]Sada matrične formule kao rezultat daju matricu ili niz. Ovo je jako efektno, pošto jednom jedinom formulom dobijamo puno ispisanih vrednosti. Zamislite, jedna formula ispiše npr. [inlmath]10000[/inlmath] vrednosti (matricu [inlmath]100\times100[/inlmath]). Ovo će biti verovatno najlakše od svega do sada. Pri tome morate voditi računa da se formula upisuje u obeleženi opseg polja. Naravno, upisujete je samo jednom a ne [inlmath]10000[/inlmath] puta, ali prethodno mišem obeležite opseg u kome želite prikaz rezultata. Recimo da je data matrica:
[dispmath]\begin{array}{|c|c|c|c|} \hline
\mbox{Excel} & A & B & C & D & E & F & G \\\hline
1 & 2 & 7 & 3 & & & & \\\hline
2 & 3 & 9 & 4 & & & & \\\hline
3 & 1 & 5 & 0 & & & & \\\hline
4 & & & & & & & \\\hline
\end{array}[/dispmath]
Potrebno je naći:
a) transponovanu matricu date matrice
b) kvadrat ove matrice, tj. pomnožiti je samu sa sobom
c) inverznu matricu date matrice
Najpre je za svaki zahtevani zadatak prethodno obeležen opseg [inlmath]E1:G3[/inlmath] a zatim:
a) Transponovana matrica ([inlmath]=\mathrm{TRANSPOSE}(A1:C3)[/inlmath])
[dispmath]\begin{array}{|c|c|c|c|} \hline
\mbox{Excel} & A & B & C & D & E & F & G \\\hline
1 & 2 & 7 & 3 & & 2 & 3 & 1 \\\hline
2 & 3 & 9 & 4 & & 7 & 9 & 5 \\\hline
3 & 1 & 5 & 0 & & 3 & 4 & 0 \\\hline
4 & & & & & & & \\\hline
\end{array}[/dispmath]
b) Kvadrat matrice ([inlmath]=\mathrm{MMULT}(A1:C3,A1:C3)[/inlmath])
[dispmath]\begin{array}{|c|c|c|c|} \hline
\mbox{Excel} & A & B & C & D & E & F & G \\\hline
1 & 2 & 7 & 3 & & 28 & 92 & 34 \\\hline
2 & 3 & 9 & 4 & & 37 & 122 & 45 \\\hline
3 & 1 & 5 & 0 & & 17 & 52 & 23 \\\hline
4 & & & & & & & \\\hline
\end{array}[/dispmath]
c) Inverzna matrica ([inlmath]=\mathrm{MINVERSE}(A1:C3)[/inlmath])
[dispmath]\begin{array}{|c|c|c|c|} \hline
\mbox{Excel} & A & B & C & D & E & F & G \\\hline
1 & 2 & 7 & 3 & & -\frac{10}{3} & \frac{5}{2} & \frac{1}{6} \\\hline
2 & 3 & 9 & 4 & & \frac{2}{3} & -\frac{1}{2} & \frac{1}{6} \\\hline
3 & 1 & 5 & 0 & & 1 &-\frac{1}{2} & -\frac{1}{2} \\\hline
4 & & & & & & & \\\hline
\end{array}[/dispmath]
Posle svake unesene formule, po ko zna koji put naglašavam, morate pritisnuti [inlmath]\mathtt{CONTROL+SHIFT+ENTER}[/inlmath] da bi je Excel shvatio kao matričnu. Na kraju evo i jedne preporuke. Vežbajte, eksperimentišite, računajte... Igrajte se brojkama i matričnim formulama. To je najbolji način da njima ovladate. I naravno, pitajte bilo šta u okviru ove teme. Tu sam da odgovorim.