Citat:
Kako funkcionišu ove formule?
Nije baš lako za objašnjenje, zato sam izbegao da je navedem i objasnim na početku, ali pošto je bio još jedan slučaj gde se koristi ova formula, ajde da probamo.
Reč je o matričnim formulama koje funkcionišu sa nizovima (serijom) podataka. Evo jedostavan primer (preuzet sa
http://www.cpearson.com/excel/ArrayFormulas.aspx) kako bi se objasnila razlika "klasičnih" i matričnih formula:
Formula
Code:
=ROW(A1)
vraća broj reda u kojoj se nalazi ćelija A1 tj. 1.
Formula
Code:
=ROW(A1:A10)
uzeće samo prvu ćeliju iz opsega A1:A10 i vratiće takođe 1. Međutim ako tu forumulu unesemo kao matričnu, rezultat neće biti jedna vrednost nego niz vrednosti {1,2,3,4,5,6,7,8,9,10}. Unos matrične forumule završava se sa CTRL+Shift+Enter. Sistem će nakon toga dodati viticaste zagrade {} oko formule. Vitičaste zagrade su uobičajen način da se predstavi niz (serija) podataka.
Kad u matričnu forumulu uključimo neku agregatnu funkciju (SUM, COUNT i sl). ona će formirati zbirni rezultat na osnovu svih vrednosti - kao da se prolazi kroz petlju u algoritmu. Dakle, ako se sledeća formula unese kao matrična
Code:
=SUM(ROW(A1:A10))
Rezultat će biti 55 ( = 1 + 2 + 3 + ... + 10). Sada da pređemo na naš primer.
Da bi formirali matričnu formulu, prvo smo u pomoćnoj ćeliji prebrojali koliko ima klijenata neke banke na listu baza. To je standardno COUNTIF:
Code:
=COUNTIF(Baza!$H$4:$H$39;A2)
A2 je naziv banke, a na listu Baza u koloni H su nazivi banaka za osobe sa spiska.
Ovu formulu smo stavili u F2. Znači ako na spisku imamo 7 osoba za Banku1 to će biti rezultat u F2 i to je broj redova koje treba da prikažemo na listi. Sve više od tih 7 redova je prazno pa možemo da koristimo klasičnu formulu (Unosimo u C7)
Code:
=IF(B7>$F$2;"";“prikazi nesto“)
Pošto u B koloni imamo redni broj, kad ovu formulu iskopiramo na dole, formula će u svim redovima posle rednog broja 7 (B13) prikazati prazno . Ostaje teži deo da umesto teksta „prikaži nešto“ prikažemo podatke iz baze.
Za to koristimo funkciju INDEX. Funkcija Index u osnovnom obliku vraća vrednost stavke iz liste za zadatu listu i redni broj stavke
Npr.= INDEX((Baza!B4:B100;3) vratiće treću vrednost iz listeimena tj. „Dejan“
Da bi dobili ono što želimo treba funkciji INDEX prosledimo niz vrednosti – sve redne brojeve gde se u koloni H baze nalazi Banka 1: {1,3,5,8,11,12,14} (ili Banka 2 itd) i zato koristimo matrični oblik..
Redne brojeve koje treba proslediti funkciji INDEX su redini brojevi iz kolone A lista baza i to za one redove kod kojih je u koloni H dobijamo kao rezultat true Baza!$H$4:$H$100=$A$2. Dakle koristimo IF funkciju u kojoj nas interesuje samo grana True
Code:
IF(Baza!$H$4:$H$100=$A$2;Baza!$A$4:$A$100)
Na ovaj način u matričnoj formuli dobićemo ceo skup vrednosti koje zadovoljava postavljeni uslov (npr Banka1: {1,3,5,8,11,12,14} ) Iz skupa rednosti treba da izvučemo jednu po jednu vrednost kako bi u svakom redu prikazali samo jedno ime. To možemo ostvariti koristeći funkciju SMALL. Drugi argument ove funkcije definiše redni broj koji se vraća iz niza vrednosti . Ako kao drugi argument koristimo redni broj iz specifikcije za odredjenu banku – kolona B dobijemo to što smo želeli
Code:
=IF(B7>$F$2;"";INDEX(Baza!B$4:B$100;SMALL(IF(Baza!$H$4:$H$100=$A$2;Baza!$A$4:$A$100);B7)))
Sada ovu formulu iskopiramo za ceo opseg u specifikaciji i dobili imena radnika za Banku 1.
Dodatni posao je potreban kad nemamo gotove redne brojeve. U tom slučaju ih formiramo pomoću ROW i ROWS funkcija
Da dobijemo redni broj reda koji zadovoljava uslov u listu baza umesto Baza!$A$4:$A$100 koristimo
Code:
ROW(Baza!$H$4:$H$100)-ROW(Baza!$H$4)+1
Da dobijemo redni broj elementa koji treba prikazati na specifikaciji umesto B7 koristimo
Code:
ROWS($C$7:C7)
Kad se kopira na dole ova funkcija će brojati redove od C7 pa do tekućeg reda, pa će vraćati 1 za prvi red, 2 za sledeći itd. Ovu zamenu treba izvršimo na oba mesta u formuli gde je B7.
Izmenio sam prethodno okačen prilog Razvrstavanje.xls da se na listu Banka1 koriste jednostavnije formule sa rednim brojevima, a na listu Banka2 redni brojevi se računaju prema gornijm formulama.
[Ovu poruku je menjao Jpeca dana 18.08.2010. u 15:53 GMT+1]
Nije to loše Rembrante, samo što ne bi dodao još malo boje?