Da biste to učinili, morate otvoriti uređivač Visual Basic Uređivač (kombinacija tipki “Alt+F11” ili kliknite desnom tipkom miša na prečac bilo kojeg lista i odaberite “Izvorni tekst” ili u grupi “Kod” na kartici izbornika “Razvojni programer” kliknite na stavku “Visual Basic”) i umetnite standardni modul u projekt. I ovom modulu dodajte dva programska koda (vidi sliku 8.) - i .

Kako optimizirati odabir višestrukih vrijednosti pretraživanja iz različitih tablica u Excelu

Prilikom izrade izvješća o iznosima i uvjetima plaćanja za više pojedinačnih dobavljača često je potrebno prelistati značajnu količinu podataka (tablica) prije nego što se dođe do tražene informacije.

Možete optimizirati odabir nekoliko željenih vrijednosti (iznosi plaćanja za različite dobavljače) prema jednom parametru (na primjer, po datumu) iz malog broja tablica koristeći gotovo istu shemu kao s jedinom razlikom što će list “Sadržaj knjige” prikazati popis imena tablica koje sudjeluju u izboru i njegove rezultate, a dodatno će se koristiti nešto drugačija formula za pretraživanje tablica:

“=VLOOKUP($C$1,INDIRECT(A2),2,FALSE)”, gdje:

• ćelija C1 (broj četvrtine) – postavlja vrijednost parametra;
• “INDIRECT(A2)” – definira tekstualnu vezu na imenovani raspon, čiji se naziv nalazi u ćeliji A2;
• “2” je broj stupca izvornih tablica dobavljača u kojima se nalaze iznosi plaćanja koji su nam potrebni;
• “FALSE” (može se zamijeniti s 0) – označava funkciji VLOOKUP da je potrebno točno podudaranje.

Bit selekcijskog upita je odabir redaka iz izvorne tablice koji zadovoljavaju određene kriterije (slično korištenju standardnog). Odaberimo vrijednosti iz izvorne tablice pomoću . Za razliku od upotrebe ( CTRL+SHIFT+L ili Podaci/ Sortiraj i filtriraj/ Filtriraj) odabrani redovi bit će smješteni u posebnu tablicu.

U ovom ćemo članku pogledati najčešće upite, na primjer: odabir redaka tablice čija je vrijednost iz numeričkog stupca unutar određenog raspona (intervala); odabir redaka čiji datum pripada određenom razdoblju; zadaci s 2 tekstualna kriterija i dr. Počnimo s jednostavnim upitima.

1. Jedan numerički kriterij (odaberite one proizvode čija je cijena viša od minimalne)

primjer datoteke, lista Jedan kriterij - broj ).

U posebnoj tablici potrebno je prikazati samo one zapise (redove) iz Izvorne tablice čija je cijena veća od 25.

Ovaj i naknadne probleme možete jednostavno riješiti pomoću . Da biste to učinili, odaberite zaglavlja izvorne tablice i kliknite CTRL+SHIFT+L. Odaberite s padajućeg popisa pokraj zaglavlja Cijene Numerički filteri..., zatim postavite potrebne uvjete filtriranja i kliknite U redu.

Prikazat će se zapisi koji zadovoljavaju uvjete odabira.

Drugi pristup je korištenje . Nasuprot tome, odabrani retci bit će smješteni u zasebnu tablicu - jedinstvenu, koja se, na primjer, može oblikovati u stilu različitom od izvorne tablice ili se mogu napraviti druge izmjene.

Kriterij ( minimalna cijena) bit će smješteni u ćeliju E6 , tablica za filtrirane podatke - u rasponu D10:E19 .

Sada odaberimo raspon D11:D19 (Proizvod u stupcu) i unesite:

INDEKS(A11:A19;
SMALL(IF($E$6<=B11:B19;СТРОКА(B11:B19);"");СТРОКА()-СТРОКА($B$10))
-REDAK ($B$10))

Umjesto UNESI pritisnite tipkovni prečac CTRL+SHIFT+ENTER(formula polja bit će ).

E11:E19 (stupac Cijena) gdje ćemo unijeti sličnu:

INDEKS(B11:B19;
SMALL(IF($E$6<=B11:B19;СТРОКА(B11:B19);"");СТРОКА()-СТРОКА($B$10))
-REDAK ($B$10))

Kao rezultat toga, dobit ćemo novu tablicu koja će sadržavati samo proizvode čije cijene nisu niže od onih navedenih u ćeliji E6 .

Kako bismo pokazali dinamičnost primljenog Zahtjeva za uzorak, ulazimo E6 vrijednost 55. Samo 2 zapisa bit će uključena u novu tablicu.

Ako dodate novi proizvod s cijenom od 80 u izvornu tablicu, tada će se novi zapis automatski dodati u novu tablicu.

Bilješka. Također možete koristiti i za prikaz filtriranih podataka. Odabir određenog alata ovisi o zadatku s kojim se korisnik suočava.

Ako vam nije ugodno koristiti formula polja, koji vraća višestruke vrijednosti, tada možete koristiti drugi pristup, o kojem se govori u odjeljcima u nastavku: 5.a, 7, 10 i 11. U tim slučajevima, .

2. Dva brojčana kriterija (Odaberite one proizvode čija je cijena unutar raspona)

Neka postoji početna tablica s popisom proizvoda i cijena (vidi. primjer datoteke, listaRaspon brojeva).

Kriterije (donja i gornja granica cijene) smjestit ćemo u raspon E5:E6 .

Oni. ako je cijena proizvoda unutar navedenog intervala, tada će se takav zapis pojaviti u novoj tablici Filtriranih podataka.

Za razliku od prethodnog zadatka, izradit ćemo dva: Proizvode i Cijene (možete i bez njih, ali su zgodni za pisanje formula). Odgovarajuće formule trebale bi izgledati kao u upravitelju naziva ( Formule/ Definirana imena/ Upravitelj imena) kako slijedi (vidi sliku u nastavku).

Sada odaberimo raspon D11:D19 i upisujemo:

INDEX(Proizvodi;
NAJMANJE(
IF(($E$5<=Цены)*($E$6>=Cijene);ROW(Cijene);"");

Umjesto UNESI pritisnite tipkovni prečac CTRL+SHIFT+ENTER.

Izvršit ćemo iste manipulacije s rasponom E11:E19 gdje ćemo predstaviti sličan:

INDEX(Cijene;
NAJMANJE(
IF(($E$5<=Цены)*($E$6>=Cijene);ROW(Cijene);"");
LINE(Cijene)-RED($B$10))-RED($B$10))

Kao rezultat, dobit ćemo novu tablicu koja će sadržavati samo proizvode čije su cijene unutar intervala navedenog u ćelijama E5 I E6 .

Kako bismo prikazali dinamičnost primljenog izvješća (Zahtjeva za uzorkovanje), ulazimo u E6 vrijednost 65. Još jedan zapis iz izvorne tablice koji zadovoljava novi kriterij bit će dodan u novu tablicu.

Ako dodate novi proizvod s cijenom u rasponu od 25 do 65 u izvornu tablicu, tada će novi zapis biti dodan u novu tablicu.

Datoteka s primjerom također sadrži formule polja s obradom pogrešaka kada stupac Cijena sadrži vrijednost pogreške, na primjer #DIV/0! (vidi list Obrada pogreške).

Sljedeći problemi rješavaju se na sličan način, pa ih nećemo tako detaljno razmatrati.

3. Datum jednog kriterija (Odaberite one proizvode čiji datum isporuke odgovara navedenom)

primjer datoteke, listaJedan kriterij - Datum).

Za odabir redaka koriste se formule polja slične Zadatku 1 (umjesto kriterija<= используется =):

=INDEKS(A12:A20,MALO(AKO($E$6=B12:B20,RED(B12:B20),"");RED(B12:B20)-RED($B$11))-RED($B$11) )

INDEKS(B12:B20,MALO(AKO($E$6=B12:B20,RED(B12:B20),"");RED(B12:B20)-RED($B$11))-RED($B$11) )

4. Dva kriterija datuma (odaberite one proizvode čiji je datum isporuke unutar raspona)

Neka postoji tablica izvora s popisom proizvoda i datumima isporuke (pogledajte. primjer datoteke, listaRaspon datuma).

Imajte na umu da stupac Datum NIJE SORTIRAN.

Rješenje1: Možeš koristiti .

Upišite ćeliju D12 formula polja:

INDEKS(12 A$:20 A$;
NAJVEĆI(($E$6<=$B$12:$B$20)*($E$7>=$B$12:$B$20)*(RED($B$12:$B$20)-RED($B$11));
$J$12-RED(A12)+RED($B$11)+1))

Bilješka: Nakon unosa formule, umjesto tipke ENTER potrebno je pritisnuti kombinaciju tipki CTRL+SHIFT+ENTER. Ovaj tipkovnički prečac koristi se za unos formula polja.

Kopirajte formulu polja na željeni broj ćelija. Formula će vratiti samo one vrijednosti za proizvode koji su isporučeni unutar navedenog datumskog raspona. Preostale ćelije sadržavat će #NUM! pogrešaka. Pogreške u primjer datoteke (list 4. Raspon datuma) .

Sličnu formulu potrebno je unijeti za datume u stupcu E.

U ćeliji J12 Izračunava se broj redaka izvorne tablice koji zadovoljavaju kriterije:

COUNTIFS(B12:B20;">="&$E$6;B12:B20;"<="&$E$7)

Redci u izvornoj tablici koji zadovoljavaju kriterije su .

Rješenje2: Za odabir redaka možete koristiti formule polja slične Zadatku 2 (tj.):

=INDEX(A12:A20,SMALL(IF(($E$6<=B12:B20)*($E$7>=B12:B20);RED(B12:B20);"");RED(B12:B20)-RED($B$11))-RED($B$11))

INDEX(B12:B20,SMALL(IF(($E$6<=B12:B20)*($E$7>=B12:B20);RED(B12:B20);"");RED(B12:B20)-RED($B$11))-RED($B$11))

Za unos prve formule odaberite raspon ćelija G12:G20 . Nakon unosa formule, umjesto tipke ENTER potrebno je pritisnuti kombinaciju tipki CTRL+SHIFT+ENTER.

Rješenje3: Ako je stupac Datum SORTED, ne morate koristiti formule polja.

Najprije morate izračunati prvu i posljednju poziciju redaka koji zadovoljavaju kriterije. Zatim ispišite retke.

Ovaj primjer još jednom jasno pokazuje kako je lako pisati formule.

5. Datum po jednom kriteriju (Odaberite one proizvode čiji datum isporuke nije raniji/ne kasniji od navedenog)

Neka postoji tablica izvora s popisom proizvoda i datumima isporuke (pogledajte. primjer datoteke, lista Jedan kriterij - datum (ne kasnije) ).

Za odabir redaka čiji datum nije raniji (uključujući i sam datum), koristi se formula polja:

=INDEKS(A12:A20,MALO(IF($E$7<=B12:B20;СТРОКА(B12:B20);"");СТРОКА(B12:B20)-СТРОКА($B$11))-СТРОКА($B$11))

Također u datoteci primjera nalaze se formule za uvjete: Ne prije (ne uključujući); Ne kasnije (uključujući); Ne kasnije (ne uključujući).

7. Jedan tekstualni kriterij (odaberite proizvode određene vrste)

Neka postoji početna tablica s popisom proizvoda i cijena (vidi. primjer datoteke, listaJedan kriterij - Tekst).

8. Dva tekstualna kriterija (Odaberite proizvode određene vrste, isporučene u određenom mjesecu)

Neka postoji početna tablica s popisom proizvoda i cijena (vidi. primjer datoteke, lista 2 kriterija - tekst (I) ).

INDEKS($A$11:$A$19;
SMALL(IF(($F$6=$A$11:$A$19)*($F$7=$B$11:$B$19);ROW($A$11:$A$19)-ROW($A$10); 30);ROW(INDIRECT("A1:A"&ROW($A$11:$A$19)))))

Izraz ($F$6=$A$11:$A$19)*($F$7=$B$11:$B$19) navodi oba uvjeta (proizvod i mjesec).

Izraz ROW(INDIRECT("A1:A"&ROW($A$11:$A$19))) oblici (1:2:3:4:5:6:7:8:9), tj. brojevi redaka u tablici.

9. Dva tekstualna kriterija (odaberite proizvode određenih vrsta)

Neka postoji početna tablica s popisom proizvoda i cijena (vidi. primjer datoteke, lista2 kriterija - tekst (ILI)).

Za razliku od problema 7, mi ćemo odabrati retke s 2 vrste robe ().

Za odabir redaka koristi se formula polja:

INDEKS(A$11:A$19;
VELIKI((($E$6=$A$11:$A$19)+($E$7=$A$11:$A$19))*(RED($A$11:$A$19)-RED($A$10) ); COUNTIF($A$11:$A$19,$E$6)+COUNTIF($A$11:$A$19,$E$7)-ROW($A$11:A11)+1))

Stanje ($E$6=$A$11:$A$19)+($E$7=$A$11:$A$19) jamči da će iz žutih polja (Proizvod2 i Proizvod3) biti odabrani samo proizvodi navedenih vrsta. Znak + (dodatak) koristi se za specificiranje (najmanje 1 kriterij mora biti zadovoljen).

Gornji izraz će vratiti niz (0:0:0:0:1:1:1:0:0). Množeći ga izrazom ROW($A$11:$A$19)-ROW($A$10), tj. do (1:2:3:4:5:6:7:8:9), dobivamo niz pozicija (brojevi redaka tablice) koji zadovoljavaju kriterije. U našem slučaju to će biti niz (0:0:0:0:5:6:7:0:0).

Kao primjer, dajemo rješenja za sljedeći problem: Odaberite proizvode čija je cijena u određenom rasponu i ponavlja se određeni broj puta ili više.

Uzmimo tablicu pošiljki robe kao početnu.

Pretpostavimo da nas zanima koliko i koje serije robe su isporučene po cijeni od 1000 rubalja. do 2000 rub. (kriterij 1). Štoviše, moraju postojati najmanje 3 lota s istom cijenom (kriterij 2).

Rješenje je formula niza:

MALI(RED($A$14:$A$27)*($C$14:$C$27>=$B$7)*($C$14:$C$27<=$C$7)*($D$14:$D$27>=$B$10);F14+($G$8-$G$9))

Ova formula vraća brojeve redaka koji zadovoljavaju oba kriterija.

Formula =SUMPROIZVOD(($C$14:$C$27>=$B$7)*($C$14:$C$27<=$C$7)*($D$14:$D$27>=$B$10)) broji broj redaka koji zadovoljavaju kriterije.

11. Koristite vrijednost kriterija (Bilo koje) ili (Sve)

U primjer datoteke na listu "11. Kriterij Bilo koji ili (Svi)" Ova verzija kriterija je implementirana.

Formula u ovom slučaju mora sadržavati funkciju IF(). Ako je odabrana vrijednost (Sve), tada se koristi formula za prikaz vrijednosti bez uzimanja u obzir ovog kriterija. Ako je odabrana bilo koja druga vrijednost, kriterij radi kao i obično.

IF($C$8="(Sve)";
MALO((RED($B$13:$B$26)-RED($B$12))*($D$13:$D$26>=$D$8);F13+($G$6-$G$7));
MALO((RED($B$13:$B$26)-RED($B$12))*($D$13:$D$26>=$D$8)*($C$13:$C$26=$C$8) ;F13+($G$6-$G$7)))

Ostatak formule sličan je gore spomenutom.

Uzorkovanje podataka

Izradite izvješće o uzorku iz Sheet5 pomoću stupca "Kvalitativna izvedba, postotak." (iz lista 8, tabela 7)

Da biste uzorkovali podatke, morate izvršiti sljedeće korake:

Odredite broj elemenata novog niza prema zadanom uvjetu unosom varijable pomoću operatora InputBox

Deklarirajte i ponovno deklarirajte novi niz

Napravite novi niz. Da biste to učinili, potrebno je postaviti broj prvog elementa novog niza u=1. Zatim se izvršava ciklus u kojem je uvjet uzorkovanja zapisan u stupcu "Kvalitativna izvedba, postotak". Ako je rezultat testa istinit, tada element analiziranog niza postaje element novog niza.

Povući novi element na listu 8

PodizvješćeSelection()

Listovi ("Sheet8"). Izaberi

Dim A() kao varijanta

ReDim A (1 na n1, 1 na m)

VVOD "List5", A, n1, m, 4

C = InputBox("Unesite uvjet")

Listovi ("Sheet8"). Ćelije (5,11) = C

Za i = 1 do n1

Ako je A(i,

8) >

d = d + 1

Listovi ("Sheet8"). Ćelije (5,10) = d

Dim B() kao varijanta

ReDim B (1 do d, 1 do m)

Za i = 1 do n1

Ako je A(i,

8) > Listovi ("Sheet8"). Ćelije (5,11) Zatim

Za j = 1 To m

B(u, j) = A(i, j)

u = u + 1

Za i = 1 do d

Za j = 1 To m

Listovi ("Sheet8"). Ćelije (i + 4, j) = B (i, j)

sl.6. Podaci tablice nakon dohvaćanja

Stvaranje automatske makronaredbe na temelju odabira

Omogući makro snimanje. Alati>Makro>Pokreni snimanje>U redu. Pojavit će se kvadrat s gumbom za zaustavljanje snimanja. Na Sheet5 (izvješće) odaberite tablicu bez zaglavlja i zbrojeva, kopirajte je na Sheet10 (automatski odabir). Odaberite tablicu bez zaglavlja iu stavci izbornika odaberite Data>Filter>AutoFilter>select condition>OK. Označavamo stupac po kojem ćemo sortirati. Dovršavamo makro.

SubMacro2Selection()

" Macro2Selection Makro

Listovi ("Sheet5"). Izaberi

Izbor. Kopirati

Listovi ("Sheet9"). Izaberi

ActiveSheet. Zalijepiti

Raspon ("H5:H17"). Izaberi

Primjena. CutCopyMode = False

Izbor. AutoFilter

ActiveSheet. Raspon ("$H$5: $H$17"). Polje automatskog filtra: =1, Kriterij1: =">80", _

Operator: =xlAnd

Raspon ("G22"). Izaberi

sl.7. Podaci tablice nakon automatskog dohvaćanja

Određivanje maksimalne i minimalne vrijednosti

Odredite maksimalne i minimalne vrijednosti u stupcima "Ukupno", "Apsolutna izvedba, postotak", "Kvalitativna izvedba" (Tablica 9, list 10)

Da biste odredili maksimalnu i minimalnu vrijednost, morate izvršiti sljedeće korake:

Postavite referentnu varijablu koja će biti trenutni minimum (maksimum)

Svaki element zbirke uspoređuje se redom s trenutnim minimumom (maksimumom), a ako taj element ne zadovoljava uvjete pretraživanja (kod minimuma je veći, a kod maksimuma manji), tada se vrijednost standarda zamjenjuje vrijednošću elementa koji se uspoređuje

Nakon potpunog pregleda svih elemenata, trenutna minimalna (maksimalna) varijabla pronalazi stvarni minimum (maksimum)

Minimalna (maksimalna) vrijednost prikazana je u odgovarajućim ćelijama

Subminmax()

Dim A() kao varijanta

n1 = Listovi ("Lista4"). Ćelije (5.12)

m = Listovi ("List2"). Ćelije (5.12)

ReDim A (1 na n1, 1 na m)

VVOD "List5", A, n1, m, 4

VIVOD "Lak10", A, n1, m, 4

VVOD "List10", A, n1, m, 4

Za j = 3 Tom

maxA = 0,00001

minA = 1000000

Za i = 1 do n1

Ako je A (i, j) > maxA Tada

maxA = A (i, j)

Ako je A (i, j)< minA Then

minA = A (i, j)

Listovi ("Lista10"). Ćelije (i + 4 + 2, j) = maxA

Listovi ("Lista10"). Ćelije (i + 4 + 3, j) = minA

Prva metoda: korištenje naprednog automatskog filtra

Na Excel list potrebno je među podacima odabrati područje koje je potrebno uzorkovati. Na kartici "Početna" kliknite "Razvrstavanje i filtriranje" (nalazi se u bloku postavki "Uređivanje"). Zatim kliknite na filtar.

Možete to učiniti drugačije: nakon odabira područja idite na karticu "Podaci" i kliknite na "Filtar", koji se nalazi u grupi "Razvrstavanje i filtriranje".

Kada se ovi koraci dovrše, ikone bi se trebale pojaviti u zaglavlju tablice za početak filtriranja. Oni će biti prikazani usmjereni prema dolje kao mali trokutići na desnom rubu ćelija. Kliknite na ovu ikonu na početku stupca za koji ćete napraviti odabir. Pokrenite izbornik, idite na "Filtri teksta" i odaberite "Prilagođeni filtar...".

Prozor prilagođenog filtriranja sada bi trebao biti aktiviran. U njemu postavljate ograničenje po kojem će se vršiti odabir. Možete odabrati jednu od pet predloženih vrsta uvjeta: jednako, nije jednako, veće od, veće od ili jednako, manje od.

Nakon filtriranja ostaju samo one linije u kojima iznos prihoda prelazi 10.000 (kao primjer).

U istom stupcu možete dodati drugi uvjet. Morate se ponovno vratiti u prozor za prilagođeno filtriranje i postaviti drugačije ograničenje odabira u njegovom donjem dijelu. Postavite prekidač na položaj "Manje", au polje s desne strane unesite "15000".

Tablica će sadržavati samo one retke u kojima iznos prihoda nije manji od 10.000, ali ne veći od 15.000.

U ostalim stupcima odabir je konfiguriran na sličan način. U željenom stupcu kliknite ikonu filtra, a zatim uzastopno kliknite stavke popisa "Filtriraj po datumu" i "Prilagođeni filtar".

Trebao bi se otvoriti prozor Custom AutoFilter. Na primjer, izvršite izbor rezultata u tablici od 4. svibnja do uključivo 6. svibnja 2016. Pritisnite “Poslije ili jednako” i u polje s desne strane unesite vrijednost “05/04/2016”. U donjem bloku stavite prekidač u položaj "Prije ili jednako", au desnom polju unesite "06.05.2016". Ostavite prekidač kompatibilnosti uvjeta u zadanom položaju, to jest "I". Za primjenu filtriranja kliknite U redu.

Listu bi sada trebalo još više smanjiti, jer će ostati samo redovi u kojima iznos prihoda varira od 10.000 do 15.000, a to je za razdoblje od 04.05.-06.05.2016.

U jednom od stupaca po želji možete resetirati filtriranje. Na primjer, to možete učiniti za vrijednosti prihoda. Kliknite na ikonu automatskog filtra u odgovarajućem stupcu. Odaberite "Ukloni filter".

Odabir po visini prihoda bit će onemogućen i ostat će samo odabir po datumima (od 04.05.2016. do 06.05.2016.).

Postoji još jedan stupac u tablici pod nazivom "Ime". Sadrži podatke u tekstualnom obliku. Koristeći ove vrijednosti, također možete formirati uzorak. U nazivu stupca kliknite na ikonu filtra. Idite na "Filtri teksta", a zatim na "Prilagođeni filtar...".

Ponovno će se otvoriti prozor prilagođenog filtra u kojem možete napraviti odabir, na primjer, prema nazivima "Meso" i "Krompir". U prvom bloku trebate postaviti prekidač u položaj "Jednako" i upisati "Krompir" u polje desno od njega. Postavite prekidač donjeg bloka u položaj "Jednako", a u polju nasuprot - "Meso". Sada biste trebali postaviti prekidač kompatibilnosti uvjeta u položaj "ILI". Pritisnite OK.

U novom uzorku ograničenja su postavljena po datumu (od 04.05.2016. do 06.05.2016.) i po nazivu (meso i krumpir). Nema ograničenja samo u visini prihoda.

Možete potpuno ukloniti filtar i to se radi istim metodama koje su korištene za njegovo postavljanje. Za poništavanje filtriranja, na kartici "Podaci" kliknite na "Filtar" u grupi "Razvrstavanje i filtriranje".

U drugoj opciji možete otići na karticu "Početna" i kliknuti na "Sortiranje i filtriranje" u "Uređivanje". Zatim kliknite na "Filter".

Ako koristite bilo koju od ovih metoda, tablica će biti izbrisana, a rezultati odabira izbrisani. Odnosno, tablica će prikazati sve podatke koji su prethodno uneseni u nju.

Druga metoda: korištenje formule polja

Na istom Excel listu stvorite praznu tablicu s istim nazivima stupaca u zaglavlju koji su u izvoru.

Sve prazne ćelije moraju biti označene u prvom stupcu novi stol. Postavite pokazivač na traku formule za unos formule - =INDEX(A2:A29,SMALL(IF(15000)

Za primjenu formule pritisnite tipke Ctrl+Shift+Enter.

Odaberite drugi stupac s datumima i postavite kursor u traku formule za unos - =INDEX(B2:B29,SMALL(IF(15000)

Na isti način unesite sljedeću formulu u stupac prihoda - =INDEX(C2:C29,SMALL(IF(15000)

Otvorit će se prozor za oblikovanje u kojem trebate odabrati karticu "Broj". U "Formati brojeva" odaberite "Datum". U desnom dijelu prozora, po želji, možete odabrati vrstu prikazanog datuma, a kada su sva podešavanja napravljena, kliknite na OK.

Sada će sve biti lijepo, a datum će biti ispravno prikazan. Ako ćelije prikazuju vrijednost "#BROJ!", tada trebate primijeniti uvjetno oblikovanje. Treba odabrati sve ćelije tablice (osim zaglavlja) i, na kartici "Početna", kliknite na "Uvjetno oblikovanje" (u bloku alata "Stilovi"). Pojavit će se popis u kojem trebate odabrati "Stvori pravilo...".

Odaberite pravila “Formatiraj samo ćelije koje sadrže”, au prvom polju, koje se nalazi ispod retka “Formatiraj samo ćelije za koje je ispunjen sljedeći uvjet”, odaberite “Pogreške” i kliknite “Format...”.

Otvorit će se prozor za formatiranje u kojem idite na “Font” i odaberite bijelu boju. Kliknite OK.

Pred vama će se pojaviti gotov uzorak prema navedenom ograničenju, a sve će to biti u zasebnoj tablici.

Treća metoda: Uzorkovanje prema nekoliko uvjeta pomoću formule

Rubne uvjete za uzorak treba unijeti u poseban stupac.

Jedan po jedan odaberite prazne stupce nove tablice kako biste u njih unijeli tražene tri formule. U prvi stupac unesite - =INDEX(A2:A29,SMALL(IF(($D$2=C2:C29),ROW(C2:C29);"");ROW(C2:C29)-ROW($C$1 ) )-RED($C$1)). Zatim unesite iste formule u stupce, samo promijenite koordinate iza naziva operatora INDEX na one koje su potrebne i odgovaraju određenim stupcima. Sve je slično prethodnoj metodi. Svaki put kada uđete, ne zaboravite pritisnuti kombinaciju tipki Ctrl+Shift+Enter.

Ako trebate promijeniti granice uzorkovanja, možete jednostavno promijeniti granične brojeve u stupcu uvjeta i tada će se rezultat odabira automatski promijeniti.

Četvrta metoda: Nasumično uzorkovanje

Na lijevoj strani tablice trebate preskočiti jedan stupac, au ćeliju sljedećeg upisati formulu - =RAND() za prikaz slučajnog broja. Za aktiviranje pritisnite ENTER.

Ako trebate napraviti cijeli stupac nasumičnih brojeva, postavite kursor u donji desni kut ćelije koja sadrži formulu. Trebao bi se pojaviti marker za ispunu koji treba povući prema dolje držeći lijevu tipku miša. To se radi paralelno s tablicom s podacima i do kraja.

Raspon ćelija sadržavat će formulu RAND, ali ne morate raditi s čistim vrijednostima. Kopirajte u prazan stupac s desne strane i odaberite raspon ćelija s nasumičnim brojevima. Na kartici "Početna" kliknite na "Kopiraj".

Odaberite prazan stupac i desnom tipkom miša otvorite kontekstni izbornik. U grupi alata "Opcije umetanja" odaberite "Vrijednosti" (prikazano kao ikona s brojevima).

Na kartici "Početna" kliknite na "Sortiranje i filtriranje", a zatim na "Prilagođeno sortiranje".

Označite okvir pokraj opcije "Moji podaci sadrže zaglavlja". U retku "Sortiraj po" označite naziv stupca u kojem se nalaze kopirane vrijednosti slučajnog broja. U retku "Razvrstavanje" postavke ostaju na zadanim postavkama. U retku "Redoslijed" odaberite opciju "Uzlazno" ili "Silazno". Kliknite OK.

Vrijednosti u tablici moraju biti poredane u uzlaznom ili silaznom redoslijedu slučajnih brojeva. Bilo koji broj prvih redaka iz tablice može se uzeti i smatrati rezultatom slučajnog uzorka.

Makro je slijed radnji koji se bilježi i sprema za kasniju upotrebu. Spremljeni makro može se reproducirati pomoću posebne naredbe. Drugim riječima, možete snimiti svoje radnje u makronaredbu, spremiti je, a zatim dopustiti drugim korisnicima da reproduciraju radnje spremljene u makronaredbi jednostavnim pritiskom na tipku. Ovo je posebno korisno pri distribuciji izvješća zaokretne tablice.

Recimo da želite svojim klijentima dati mogućnost grupiranja izvješća zaokretne tablice prema mjesecu, kvartalu i godini. Tehnički, svatko može napraviti proces grupiranja, ali neki od vaših klijenata neće imati potrebu da to razumiju. U tom slučaju možete snimiti jedan makro za grupiranje po mjesecu, drugi po kvartalu i treći po godini. Zatim izradite tri gumba - po jedan za svaku makronaredbu. Tada će vaši klijenti koji su novi u zaokretnim tablicama morati samo kliknuti gumb za pravilno grupiranje izvješća stožerna tablica.

Glavna prednost korištenja makronaredbi u izvješćima zaokretne tablice je omogućiti klijentima brzo izvođenje operacija na zaokretnim tablicama koje inače ne bi mogli izvesti. Zahvaljujući tome, učinkovitost analize dostavljenih podataka značajno se povećava.

Preuzmite bilješku u ili formatu, preuzmite s primjerima (unutar Excel datoteka s makronaredbama; Pravila pružatelja usluga ne dopuštaju izravno učitavanje datoteke ovog formata na stranicu).

Snimite makro

Pogledajte tablicu sažetka prikazanu na sl. 1. Ovu zaokretnu tablicu možete osvježiti tako da desnom tipkom miša kliknete unutar nje i odaberete Ažuriraj. Ako ste tijekom ažuriranja zaokretne tablice snimili radnje kao makronaredbu, vi ili bilo tko drugi možete ponovno reproducirati te radnje i ažurirati zaokretnu tablicu kao rezultat pokretanja makronaredbe.

Riža. 1. Snimanje radnji tijekom ažuriranja ove zaokretne tablice omogućit će vam ažuriranje podataka u budućnosti kao rezultat pokretanja makronaredbe

Prvi korak u snimanju makronaredbe je pozivanje dijaloškog okvira Snimite makro. Idi na karticu Developer vrpcu i kliknite gumb Snimite makro. (Ako ne možete pronaći karticu na vrpci Developer, odaberite karticu Datoteka, i kliknite na gumb Mogućnosti. U dijaloškom okviru koji se pojavi Excel opcije Izaberi kategoriju Prilagodite vrpcu i na popisu s desne strane potvrdite okvir Developer. Kao rezultat toga, na vrpci će se pojaviti kartica Developer.) Alternativni način započeti snimanje makronaredbe - kliknite na gumb (slika 2).

U dijaloškom okviru Snimite makro Unesite sljedeće informacije o makronaredbi (slika 3):

Imemakro. Naziv bi trebao opisivati ​​radnje koje makronaredba izvodi. Ime mora započeti slovom ili podvlakom; ne smije sadržavati razmak ili druge nedopuštene znakove; ne smije biti isto kao Excelov ugrađeni naziv ili naziv drugog objekta u radnoj knjizi.

Kombinacijaključevi. U ovo polje možete unijeti bilo koje slovo. Postat će dio tipkovničkog prečaca koji će se koristiti za reprodukciju makronaredbe. Nije potrebno navesti kombinaciju tipki. Prema zadanim postavkama samo se Ctrl nudi kao početak kombinacije. Ako želite da kombinacija uključuje i Shift, unesite slovo u prozor dok držite pritisnutu tipku Shift

UštedjetiV. Ovdje se pohranjuje makronaredba. Ako izvješće zaokretne tablice namjeravate distribuirati drugim korisnicima, odaberite opciju Ovajknjiga. Excel također omogućuje spremanje makronaredbe u Nova knjiga ili u Osobna makro knjiga.

Opis. U ovo polje unosi se opis makronaredbe koju treba izraditi.

Riža. 3. Prilagodba prozora Snimite makro

Budući da makronaredba ažurira zaokretnu tablicu, odaberite naziv Ažuriranje podataka. Također možete dodijeliti makronaredbi tipkovni prečac Ctrl+Shift+Q. Imajte na umu da ćete, nakon što izradite makronaredbu, koristiti ovaj prečac na tipkovnici za njegovo pokretanje. Odaberite opciju za mjesto pohrane makroa Ova knjiga i kliknite u redu.

Nakon klika u dijaloškom okviru Snimite makro na gumbu u redu Započinje makro snimanje. U ovom trenutku sve što radite u Excelu bit će zabilježeno.

Desnom tipkom miša kliknite područje zaokretne tablice i odaberite Ažuriraj(kao na slici 1, ali u načinu makro snimanja). Nakon ažuriranja zaokretne tablice, možete zaustaviti proces snimanja makroa pomoću gumba Zaustavi snimanje kartice Developer. Ili ponovno kliknite gumb prikazan na sl. 2.

Dakle, upravo ste snimili svoj prvi makro. Sada možete izvršiti makronaredbu pomoću kombinacije tipki Ctrl+Shift+Q.

Makro sigurnosno upozorenje. Treba imati na umu da će se makronaredbe, ako ih snimi korisnik, izvršiti bez ikakvih ograničenja od strane sigurnosnog podsustava. Međutim, pri širenju radna bilježnica koji sadrže makronaredbe, potrebno je omogućiti drugim korisnicima da se uvjere da nema rizika pri otvaranju radnih datoteka, te da izvršavanje makronaredbi neće dovesti do infekcije sustava virusima. Konkretno, odmah ćete primijetiti da datoteka primjera korištena u ovom poglavlju neće u potpunosti funkcionirati ako izričito ne dopustite Excelu da u njoj pokreće makronaredbe.

Najlakši način da osigurate makro sigurnost je stvoriti pouzdanu lokaciju - mapu u koju će se smjestiti samo "pouzdane" radne knjige koje ne sadrže viruse. Pouzdana lokacija omogućuje vama i vašim klijentima pokretanje makronaredbi na radnim knjigama bez ikakvih sigurnosnih ograničenja (ovo ponašanje postoji sve dok su radne knjige na pouzdanoj lokaciji).

Da biste postavili pouzdanu lokaciju, slijedite ove korake:

Odaberite karticu vrpce Developer i kliknite na gumb Sigurnost makronaredbi. Na ekranu će se pojaviti dijaloški okvir Centar za povjerenje.

Pritisnite gumb Dodajte novu lokaciju.

Pritisnite gumb Pregled da odredite mapu za radne datoteke kojima vjerujete.

Nakon što navedete pouzdanu lokaciju, sve radne knjige na toj lokaciji pokrenut će proizvoljne makronaredbe prema zadanim postavkama.

U programu Excel 2013 sigurnosni model je poboljšan. Sada se pamte datoteke radne knjige koje su prethodno bile "pouzdane", tj. nakon otvaranja Excel radne knjige i kliknite na gumb Uključi sadržaj Excel pamti ovo stanje. Kao rezultat toga, ova knjiga spada u kategoriju pouzdanih, a nepotrebna pitanja se ne postavljaju prilikom sljedećeg otvaranja.

Stvaranje korisničkog sučelja pomoću kontrola obrasca

Pokretanje makronaredbe pomoću kombinacije tipki Ctrl+Shift+Q pomoći će kada postoji samo jedna makronaredba u izvješću zaokretne tablice. (Također, korisnici moraju znati ovu kombinaciju.) Ali pretpostavimo da svojim klijentima želite pružiti nekoliko makronaredbi koje izvode različite radnje. U ovom slučaju morate kupcima pružiti razumljiv i na jednostavan način pokrenite svaki makro bez potrebe za pamćenjem kombinacija tipki. Idealno rješenje je jednostavno korisničko sučelje kao skup kontrola kao što su gumbi, trake za pomicanje i druge kontrole koje vam omogućuju izvršavanje makronaredbi klikovima mišem.

Excel vam nudi skup alata dizajniranih za stvaranje korisničkih sučelja izravno u vašoj proračunskoj tablici. Ti se alati nazivaju kontrolama obrazaca. Osnovna ideja je da možete postaviti kontrolu obrasca u proračunsku tablicu i dodijeliti joj makronaredbu koja je prethodno snimljena. Nakon što se dodijeli kontroli, makro će se pokrenuti klikom na taj element.

Kontrole obrazaca mogu se pronaći u grupi Kontrole obrazaca jezičci na vrpci Developer. Da biste otvorili kontrolnu paletu, kliknite gumb u ovoj grupi Umetnuti(slika 4).

Riža. 4. Kontrola obrasca Dugme

Napomena: osim kontrola obrasca, paleta također sadrži ActiveX kontrole . Iako su slični, programski su potpuno različiti objekti. Kontrole obrazaca sa svojim nedostacima i jednostavne postavke posebno dizajniran za postavljanje na radne listove. U isto vrijeme ActiveX kontrole prvenstveno se koristi u prilagođenim oblicima. Neka vam postane navika da na svoje radne listove postavljate samo kontrole obrazaca.

Trebali biste odabrati kontrole koje najbolje odgovaraju zadatku koji imate. U ovom primjeru, klijenti moraju moći ažurirati zaokretnu tablicu klikom na gumb. Kliknite na kontrolu Dugme, pomaknite pokazivač miša na mjesto na radnom listu gdje želite da se nalazi gumb i kliknite.

Nakon što postavite gumb u tablicu, otvorit će se dijaloški okvir Dodijeli makro objekt(slika 5). Odaberite željeni makro (u našem slučaju - Ažuriranje podataka, snimljeno ranije) i kliknite na gumb u redu.

Riža. 5. Odaberite makronaredbu koju želite dodijeliti gumbu i kliknite na gumb u redu. U ovom slučaju, trebali biste koristiti makro Ažuriranje podataka

Nakon što postavite sve potrebne kontrole u svoje izvješće zaokretne tablice, možete formatirati tablicu za stvaranje osnovnog sučelja. Na sl. Slika 6 prikazuje izvješće zaokretne tablice nakon formatiranja.

Uređivanje snimljene makronaredbe

Kao rezultat snimanja makronaredbe Excel program stvara modul koji pohranjuje radnje koje ste izvršili. Sve snimljene radnje predstavljene su linijama VBA koda koji čine makronaredbu. Svojim izvješćima zaokretne tablice možete dodati različite vrste podataka. funkcionalnost, prilagođavanje VBA koda za postizanje željenih rezultata. Da bismo lakše razumjeli kako sve to funkcionira, kreirajmo novu makronaredbu koja prikazuje prvih pet zapisa kupaca. Idi na karticu Developer i kliknite na gumb Snimite makro. Otvorit će se dijaloški okvir prikazan na slici. 7. Imenujte makronaredbu koju stvarate PrviNkupci i označite mjesto spremanja Ova knjiga. Klik u redu za početak snimanja makronaredbe.

Nakon što započnete snimanje, kliknite na strelicu pored polja Ime kupca, Izaberi Filtriraj po vrijednosti i opcija Prvih 10(Slika 8a). U dijaloškom okviru koji se pojavi postavite postavke kao što je prikazano na sl. 8b. Ove postavke označavaju prikaz podataka pet klijenata s najboljim obujmom prodaje. Klik u redu.

Riža. 8. Odaberite filtar (a) i prilagodite opcije (b) za prikaz prvih pet kupaca po prodaji

Nakon uspješnog snimanja svih koraka potrebnih za izdvajanje pet najvećih prodajnih potencijalnih kupaca, idite na karticu Developer i kliknite na gumb Zaustavi snimanje.

Sada imate makronaredbu koja će filtrirati zaokretnu tablicu kako bi izdvojila prvih pet prodajnih računa. Potrebno je natjerati makro da reagira na stanje trake za pomicanje, tj. Pomoću trake za pomicanje morate moći reći makrou broj klijenata čiji će podaci biti prikazani u izvješću zaokretne tablice. Stoga, korištenjem trake za pomicanje, korisnik će moći dohvatiti prvih pet, prvih osam ili trideset i dva najbolja klijenta po vlastitom nahođenju.

Da biste svojoj proračunskoj tablici dodali traku za pomicanje, kliknite Developer, kliknite na gumb Umetnuti, odaberite kontrolu u paleti Traka za pomicanje i stavite ga na svoj radni list. Desni klik na kontrolu Traka za pomicanje Format objekta. Otvorit će se dijaloški okvir Kontrolni format(slika 9). U njemu napravite sljedeće promjene postavki: parametar Minimalna vrijednost dodijeliti vrijednost 1 parametru Maksimalna vrijednost- vrijednost 200, a u polj Stanična komunikacija Unesite $M$2 za prikaz vrijednosti trake za pomicanje u ćeliji M2. Pritisnite gumb u redu za primjenu prethodno navedenih postavki.

Sada morate uskladiti nedavno snimljenu makronaredbu PrviNkupci s kontrolnim elementom Traka za pomicanje koji se nalazi na radnom listu. Desni klik na kontrolu Traka za pomicanje i u kontekstni izbornik odaberite tim Dodijeli makro da biste otvorili dijaloški okvir za dodjelu makronaredbi. Dodijelite snimljenu makronaredbu traci za pomicanje FirstN kupci. Makronaredba će se izvršiti svaki put kada kliknete na traku za pomicanje. Testirajte stvorenu traku za pomicanje. Klikom na traku pokrenut ćete makro FirstN kupci a broj u ćeliji M2 će se promijeniti kako bi pokazao stanje trake za pomicanje. Broj u ćeliji M2 važan je jer se koristi za povezivanje makronaredbe s trakom za pomicanje.

Jedino što preostaje je natjerati makro da obradi broj u ćeliji M2 tako da ga poveže s trakom za pomicanje. Da biste to učinili, morate otići na VBA kod makronaredbe. Da biste to učinili, idite na karticu Developer i kliknite na gumb Makronaredbe. Otvorit će se dijaloški okvir Makro(slika 10). U njemu možete pokretati, brisati i uređivati ​​odabranu makronaredbu. Za prikaz VBA koda makronaredbe na zaslonu odaberite makronaredbu i kliknite gumb Promijeniti.

Riža. 10. Za pristup VBA kodu makronaredbe PrviNkupci, odaberite makronaredbu i kliknite gumb Promijeniti

Na ekranu će se pojaviti prozor Visual Basic editora s VBA makro kodom (slika 11). Vaš cilj je zamijeniti tvrdo kodirani broj 5, koji je postavljen kada se makronaredba snimi, s vrijednošću u ćeliji M2, koja je vezana na traku za pomicanje. U početku je snimljen makro za prikaz prvih pet kupaca s najvećim prihodom.

Uklonite broj 5 iz koda i umjesto njega unesite sljedeći izraz:

ActiveSheet.Range(" M2 ").Vrijednost

Dodajte dva retka na početak makronaredbe da biste očistili filtre:

Raspon (" A4 "). Odaberi
ActiveSheet.PivotTables(" PivotTable1 ").PivotFields(" Ime kupca ").ClearAllFilters

Sada bi makro kod trebao izgledati kao što je prikazano na sl. 12.

Zatvorite Visual Basic Editor i vratite se na izvješće zaokretne tablice. Testirajte traku za pomicanje povlačenjem klizača na 11. Makro bi se trebao pokrenuti i filtrirati prvih 11 prodajnih računa.

Sinkronizirajte dvije zaokretne tablice pomoću jednog padajućeg popisa

Izvješće prikazano na sl. 13 sadrži dvije zbirne tablice. Svaki od njih ima polje stranice koje vam omogućuje odabir prodajnog tržišta. Problem je u tome što svaki put kada odaberete tržište u polju stranica jedne zaokretne tablice, morate odabrati isto tržište u polju stranica druge zaokretne tablice. Sinkronizacija filtara između dvije tablice tijekom faze analize podataka nije veliki problem, ali postoji mogućnost da ćete vi ili vaši klijenti zaboraviti to učiniti.

Riža. 13. Dvije zaokretne tablice sadrže polja stranica koja filtriraju podatke prema tržištu. Za analizu podataka za jedno tržište morate sinkronizirati obje zaokretne tablice

Jedan od načina da ove zaokretne tablice budu sinkronizirane jest korištenje padajućeg popisa. Ideja je snimiti makro koji iz polja odabire željeno tržište Tržište prodaje u obje tablice. Zatim trebate izraditi padajući popis i ispuniti ga nazivima tržišta iz dvije zaokretne tablice. Konačno, snimljena makronaredba mora se modificirati kako bi se filtrirale obje zaokretne tablice pomoću vrijednosti s padajućeg popisa. Da biste riješili ovaj problem, morate učiniti sljedeće:

1. Napravite novu makronaredbu i dodijelite joj ime SynchMarkets. Kada snimanje započne, odaberite u polju Tržište prodaje za oboje sales market sumarne tablice Kalifornija i zaustavite snimanje makronaredbe.

2. Prikažite paletu Form Controls i dodajte padajući popis na radni list.

3. Napravite tvrdo kodirani popis svih tržišta u zaokretnoj tablici. Imajte na umu da je prvi element popisa vrijednost (Sve). Trebali biste omogućiti ovaj element ako želite moći odabrati sva tržišta s padajućeg popisa.

4. U ovom trenutku izvješće zaokretne tablice trebalo bi izgledati kao ono prikazano na slici. 14.

Riža. 14. Na raspolaganju su vam svi potrebni alati: makronaredba koja mijenja polje Tržište prodaje obje zaokretne tablice, padajući popis i popis svih prodajnih tržišta sadržanih u zaokretnoj tablici

5. Kliknite desnom tipkom miša na padajući popis i odaberite naredbu iz kontekstnog izbornika Format objekta za konfiguriranje kontrole.

6. Prvo postavite početni raspon vrijednosti koje se koriste za popunjavanje padajućeg popisa, kao što je prikazano na slici. 15. U ovom slučaju govorimo o popisu prodajnih tržišta koji ste kreirali u koraku 3. Zatim označite ćeliju koja prikazuje redni broj odabranog elementa (u u ovom primjeru ovo je ćelija H1). Parametar Broj redaka popisa određuje koliko će redaka biti prikazano istovremeno na padajućem popisu. Pritisnite gumb u redu.

Riža. 15. Postavke padajućeg popisa trebale bi upućivati ​​na popis prodajnih tržišta kao početni raspon vrijednosti i definirati ćeliju H1 kao točku sidrišta

7. Sada imate priliku odabrati prodajno tržište na padajućem popisu, te također odrediti serijski broj povezan s njim u ćeliji H1 (slika 16). Postavlja se pitanje zašto se umjesto pravog naziva tržišta koristi njegova vrijednost indeksa? Budući da padajući popis ne vraća ime, već broj. Na primjer, ako s padajućeg popisa odaberete Kaliforniju, u ćeliji H1 pojavljuje se vrijednost 5. To znači da je Kalifornija peta stavka na popisu.

Riža. 16. Padajući popis sada je ispunjen nazivima tržišta, a redni broj odabranog tržišta prikazan je u ćeliji H1

8. Da biste koristili redni broj umjesto naziva tržišta, morate ga proslijediti pomoću funkcije INDEX.

9. Unesite funkciju INDEX koja pretvara serijski broj iz ćelije H1 u smislenu vrijednost.

10. Funkcija INDEX uzima dva argumenta. Prvi argument predstavlja raspon vrijednosti popisa. U većini slučajeva koristit ćete isti raspon koji popunjava padajući izbornik. Drugi argument je redni broj. Ako je serijski broj unesen u ćeliju (na primjer, u ćeliju H1, kao na slici 17), tada se možete jednostavno pozvati na ovu ćeliju.

Riža. 17. Funkcija INDEX u ćeliji I1 pretvara redni broj pohranjen u ćeliji H1 u vrijednost. Koristit ćete vrijednost u ćeliji I1 za promjenu makronaredbe

11. Uredite makronaredbu SynchMarkets, koristeći vrijednost u ćeliji I1 umjesto tvrdo kodirane vrijednosti. Idi na karticu Developer i kliknite na gumb Makronaredbe. Na ekranu će se pojaviti dijaloški okvir prikazan na sl. 1. 18. Odaberite makro u njemu SynchMarkets i kliknite gumb Promijeni.

Riža. 18. Za pristup VBA kodu makronaredbe, odaberite makronaredbu SynchMarkets i kliknite Promijeniti

12. Prilikom snimanja makronaredbe, odabrali ste prodajno tržište Kalifornije iz polja u obje zaokretne tablice Tržište prodaje. Kao što se može vidjeti sa Sl. 19, tržište Kalifornije sada je kodirano u VBA makro kodu.

13. Zamijenite vrijednost "California" izrazom Activesheet.Range("I1").Value, koji upućuje na vrijednost u ćeliji I1. U ovoj fazi, makro kod bi trebao izgledati kao što je prikazano na sl. 20. Nakon izmjene makronaredbe, zatvorite Visual Basic Editor i vratite se na proračunsku tablicu.

Riža. 20. Zamijenite vrijednost "California" s ActiveSheet.Range("I1").Value i zatvorite Visual Basic Editor

14. Sve što preostaje je osigurati izvršavanje makronaredbe pri odabiru prodajnog tržišta na padajućem popisu. Desnom tipkom miša kliknite padajući izbornik i odaberite opciju Dodijeli makro. Odaberite makro SynchMarket i kliknite na gumb u redu.

15. Sakrijte retke i stupce polja stranice u zaokretnim tablicama, kao i popis tržišta i formule indeksa koje ste stvorili.

Na sl. Slika 21 prikazuje konačni rezultat. Sada imate korisničko sučelje koje korisnicima omogućuje odabir tržišta u obje zaokretne tablice pomoću jednog padajućeg popisa.

Kada odaberete novu stavku s padajućeg popisa, stupci automatski mijenjaju veličinu kako bi se prilagodili svim podacima koje prikazuju. Ovakvo ponašanje programa prilično smeta prilikom oblikovanja predloška radnog lista. To možete spriječiti tako da desnom tipkom miša kliknete zaokretnu tablicu i odaberete Mogućnosti zaokretne tablice. Na ekranu će se pojaviti istoimeni dijaloški okvir u kojem trebate resetirati potvrdni okvir Automatski promijenite širine stupaca pri ažuriranju.

Bilješka je napisana prema knjizi Jelen, Alexander. . Poglavlje 12.