Suština upita za odabir je odabir redaka iz izvorne tablice koji zadovoljavaju određene kriterije (slično korištenju standardnog). Odaberite vrijednost iz izvorne tablice koristeći. Za razliku od upotrebe ( CTRL + SHIFT + L ili Podaci / sortiranje i filtriranje / filtriranje) odabrani redovi bit će smješteni u zasebnu tablicu.
U ovom ćemo članku razmotriti najčešće upite, na primjer: odabir redaka tablice čija vrijednost iz numeričkog stupca spada u navedeni raspon (interval); odabir linija za koje datum pripada određenom periodu; zadaci sa 2 tekstualna kriterija i drugi. Počnimo s jednostavnim upitima.
1. Jedan numerički kriterij (odaberite one proizvode kojima je cijena viša od minimalne)
primjer datoteke, lista Jedan kriterij je broj ).
U zasebnoj tablici potrebno je prikazati samo one zapise (redove) iz izvorne tablice za koje je cijena viša od 25.
Ovaj i naredne zadatke možete lako riješiti uz pomoć. Da biste to učinili, odaberite zaglavlja izvorne tablice i pritisnite CTRL + SHIFT + L... Korištenjem padajućeg popisa u naslovu Odaberite cijene Filteri s brojevima ..., zatim postavite potrebne uvjete filtriranja i kliknite U redu.
Prikazat će se zapisi koji odgovaraju kriterijima odabira.
Drugi pristup je upotreba. Za razliku od odabranih redova, oni će biti smješteni u zasebnu tablicu - vrstu koja se, na primjer, može oblikovati u stilu koji se razlikuje od izvorne tablice ili napraviti druge promjene.
Stavite kriterij (minimalna cijena) u ćeliju E6 , tablica za filtrirane podatke - u rasponu D10: E19 .
Sada odaberite raspon D11: D19 (stupac Proizvod) i unesite:
INDEKS (A11: A19;
MALI (AKO ($ E $ 6<=B11:B19;СТРОКА(B11:B19);"");СТРОКА()-СТРОКА($B$10))
-LINE ($ 10 USD)
Umjesto toga ENTER pritisnite tastersku prečicu CTRL + SHIFT + ENTER(formula niza će biti).
E11: E19 (stupac Cijena) gdje ćemo unijeti sličan:
INDEKS (B11: B19;
MALI (AKO ($ E $ 6<=B11:B19;СТРОКА(B11:B19);"");СТРОКА()-СТРОКА($B$10))
-LINE ($ 10 USD)
Kao rezultat, dobit ćemo novu tablicu koja će sadržavati samo proizvode čija cijena nije niža od one navedene u ćeliji E6 .
Da bismo pokazali dinamičnost primljenog zahtjeva za uzorkom, uvodimo u E6 vrijednost 55. Samo će 2 zapisa ući u novu tablicu.
Ako u izvornu tablicu dodate novi proizvod s cijenom od 80, novi će se zapis automatski dodati u novu tablicu.
Bilješka... Takođe možete koristiti i za prikaz filtriranih podataka. Izbor određenog alata ovisi o zadatku s kojim se suočava korisnik.
Ako vam nije ugodno koristiti formula niza koji vraća više vrijednosti, možete koristiti drugačiji pristup, o čemu se govori u sljedećim odjeljcima: 5.a, 7, 10 i 11. U tim se slučajevima koriste.
2. Dva numerička kriterija (odaberite one proizvode za koje je cijena u rasponu)
Neka postoji početna tablica sa listom proizvoda i cijena (vidi. primjer datoteke, listaRaspon brojeva).
Stavite kriterije (donju i gornju granicu cijene) u raspon E5: E6 .
Oni. ako cijena robe padne unutar navedenog intervala, tada će se takav zapis pojaviti u novoj tablici filtriranih podataka.
Za razliku od prethodnog zadatka, mi ćemo stvoriti dva: Proizvodi i Cijene (možete i bez njih, ali prikladni su za pisanje formula). Odgovarajuće formule trebale bi izgledati u Upravitelju imena ( Formule / Definirana imena / Upravitelj imena) kako slijedi (vidi donju sliku).
Sada odaberite raspon D11: D19 i u uvodimo:
INDEKS (Roba;
NAJMANJE (
AKO (($ E $ 5<=Цены)*($E$6>= Cijene); LINE (Cijene); "");
Umjesto toga ENTER pritisnite tastersku prečicu CTRL + SHIFT + ENTER.
Izvršit ćemo iste manipulacije s dometom E11: E19 gdje ćemo predstaviti sličan:
INDEKS (cijene;
NAJMANJE (
AKO (($ E $ 5<=Цены)*($E$6>= Cijene); LINE (Cijene); "");
LINE (Cijene) -LINE (10 USD) - LINE (10 USD)
Kao rezultat, dobit ćemo novu tablicu koja će sadržavati samo proizvode za koje cijene padaju u intervalu navedenom u ćelijama E5 i E6 .
Da bismo pokazali dinamičnost primljenog Izvještaja (Zahtjev za izbor), ulazimo E6 vrijednost 65. U novu tablicu će se dodati još jedan zapis iz izvorne tablice, zadovoljavajući novi kriterij.
Ako u izvornu tablicu dodate novi proizvod s cijenom u rasponu od 25 do 65, novi će se zapis dodati u novu tablicu.
Uzorak datoteke također sadrži formule niza koje obrađuju pogreške kada stupac Cijena sadrži vrijednost greške, kao što je # DIV / 0! (vidi list Greška u obradi).
Sljedeći se zadaci rješavaju na sličan način, pa ih nećemo detaljno razmatrati.
3. Jedan kriterij Datum (odaberite one proizvode za koje se Datum isporuke podudara s navedenim)
primjer datoteke, listaJedan kriterij - datum).
Za odabir redaka koriste se formule niza, slične zadatku 1 (umjesto kriterija<= используется =):
=INDEKS (A12: A20; MALI (AKO ($ 6 = B12: B20; LINIJA (B12: B20); ""); LINIJA (B12: B20) -LINE ($ 11 $)) - LINIJA ($ B 11 USD))
KAZALO (B12: B20; MALO (AKO ($ 6 = B12: B20; LINIJA (B12: B20); ""); LINIJA (B12: B20) -LINE ($ 11 USD)) - LINIJA ($ B 11 USD))
4. Dva kriterija Datum (odaberite one proizvode za koje je datum isporuke u rasponu)
Pretpostavimo da postoji početna tablica sa popisom robe i datumima isporuke (vidi. primjer datoteke, listaDatumski raspon).
Imajte na umu da stupac Datum NIJE SORTIRAN.
Rješenje1: Možete koristiti za filtriranje redova.
Uđite u ćeliju D12 formula niza:
INDEKS (12 A $: 20 A $;
VELIKO (($ E $ 6<=$B$12:$B$20)*($E$7>= $ 12 $: $ 20 $) * (LINIJA ($ 12 $: $ 20 $) -LINE ($ 11 USD));
$ J $ 12-LINE (A12) + LINE ($ B $ 11) +1))
Bilješka: Nakon unosa formule, umjesto tipke ENTER, pritisnite kombinaciju tipki CTRL + SHIFT + ENTER. Ova tipkovna prečica koristi se za unos formula niza.
Kopirajte formulu niza na željeni broj ćelija. Formula će vratiti samo one vrijednosti proizvoda koji su isporučeni u navedenom datumskom rasponu. Preostale ćelije sadržavat će pogreške #NUM! Greške u primjer datoteke (datumski raspon lista 4) .
Sličnu formulu treba unijeti za datume u stupcu E.
U ćeliji J12 izračunao broj redaka izvorne tablice koji zadovoljavaju kriterije:
BROJENJA (B12: B20; "> =" & $ E $ 6; B12: B20; "<="&$E$7)
Izvorni redovi tablice koji zadovoljavaju kriterije su.
Rješenje2: Za odabir redaka možete koristiti formule niza slične Task2 (tj.):
=INDEKS (A12: A20; MALI (AKO (($ E $ 6<=B12:B20)*($E$7>= B12: B20); LINIJA (B12: B20); ""); LINIJA (B12: B20) -LINE ($ 11 USD)) - LINIJA ($ 11 USD))
INDEKS (B12: B20; MALI (AKO (($ 6 USD<=B12:B20)*($E$7>= B12: B20); LINIJA (B12: B20); ""); LINIJA (B12: B20) -LINE ($ 11 USD)) - LINIJA ($ 11 USD))
Da biste unijeli prvu formulu, odaberite raspon ćelija G12: G20 ... Nakon unosa formule, umjesto pritiska na ENTER, pritisnite CTRL + SHIFT + ENTER.
Rješenje3: Ako je stupac Datum SORTIRAN, tada ne morate koristiti formule niza.
Prvo morate izračunati prvu i zadnju poziciju redaka koji zadovoljavaju kriterije. Zatim ispišite redove.
Ovaj primjer još jednom jasno pokazuje koliko je lakše pisati formule.
5. Jedan kriterij Datum (Odaberite onu robu za koju Datum isporuke nije raniji / ne kasniji od navedenog)
Pretpostavimo da postoji početna tablica sa popisom robe i datumima isporuke (vidi. primjer datoteke, lista Jedan kriterij - datum (ne kasnije) ).
Da biste odabrali redove čiji datum nije raniji (uključujući i sam datum), upotrijebite formulu niza:
=INDEKS (A12: A20; MALI (AKO ($ 7 USD)<=B12:B20;СТРОКА(B12:B20);"");СТРОКА(B12:B20)-СТРОКА($B$11))-СТРОКА($B$11))
Takođe, datoteka primjera sadrži formule za uvjete: Ne ranije (ne uključujući); Ne kasnije (uključujući); Ne kasnije (ne uključujući).
7. Kriterij za jedan tekst (odaberi proizvode određene vrste)
Neka postoji početna tablica sa listom proizvoda i cijena (vidi. primjer datoteke, listaJedan kriterij - tekst).
8. Dva tekstualna kriterija (odaberite robu određene vrste koja se isporučuje u određenom mjesecu)
Neka postoji početna tablica sa listom proizvoda i cijena (vidi. primjer datoteke, lista 2 kriterija - tekst (I) ).
INDEKS ($ 11 A $: 19 A $;
MALI (AKO ((($ F $ 6 = $ A $ 11: $ A $ 19) * ($ F $ 7 = $ B $ 11: $ B $ 19); LINIJA ($ A $ 11: $ A $ 19) -LINE (10 USD); 30); LINIJA (INDIREKTNA ("A1: A" I REDOVI (11 USD: 19 USD)))))
Izraz ($ F $ 6 = $ A $ 11: $ A $ 19) * ($ F $ 7 = $ B $ 11: $ B $ 19) postavlja oba uvjeta (Stavka i Mjesec).
Izraz LINIJA (INDIREKTNA ("A1: A" I REDOVI (11 USD, 19 USD))) oblici (1: 2: 3: 4: 5: 6: 7: 8: 9), tj. brojevi redova u tablici.
9. Dva tekstualna kriterija (odabrani proizvodi određenih vrsta)
Neka postoji početna tablica sa listom proizvoda i cijena (vidi. primjer datoteke, lista2 kriterija - tekst (ILI)).
Za razliku od zadatka 7, odaberite redove sa robom od 2 vrste ().
Formula niza koristi se za odabir redaka:
INDEKS (11 USD: 19 $;
VELIKO ((($ E $ 6 = $ A $ 11: $ A $ 19) + ($ E $ 7 = $ A $ 11: $ A $ 19)) * (RED ($ A $ 11: $ A $ 19 ) -LINE ($ 10 A $)); COUNTIF ($ 11: $ 19: $ 19; $ E $ 6) + COUNTIF ($ 11 $: $ 19 $; $ 7 $) -ROWS ($ 11 USD: A11) +1))
Stanje ($ E $ 6 = $ A $ 11: $ A $ 19) + ($ E $ 7 = $ A $ 11: $ A $ 19) garantira da će se iz žutih ćelija (Product2 i Product3) odabrati samo određene vrste robe. Za zadatak se koristi znak + (dodavanje) (mora biti zadovoljen najmanje 1 kriterij).
Gornji izraz vratit će niz (0: 0: 0: 0: 1: 1: 1: 0: 0). Množeći ga izrazom LINIJA (11 USD): LINIJA (10 USD), tj. na (1: 2: 3: 4: 5: 6: 7: 8: 9), dobit ćemo 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 predstavljamo rješenja za sljedeći problem: Odaberite proizvode čija se cijena kreće 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 pošiljke robe su isporučene po cijeni od 1000 rubalja. do 2000r. (kriterij 1). Štoviše, moraju postojati najmanje 3 stranke s istom cijenom (kriterij 2).
Rješenje je formula niza:
MALI (RED ($ 14: $ 27 $) * ($ 14 $: 27 $> = $ 7 $) * ($ 14 $: 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 = SUMPRODUCT (($ 14 $: $ 27> = $ B $ 7) * ($ 14 $: $ 27<=$C$7)*($D$14:$D$27>= $ 10 $ 10)) broji broj redaka koji zadovoljavaju kriterije.
11. Koristimo vrijednost kriterija (Bilo) ili (Sve)
IN primjer datoteke na listu "11. Kriterij bilo koji ili (svi)" ova varijanta kriterija je primijenjena.
Formula u ovom slučaju mora sadržavati funkciju IF (). Ako je odabrana vrijednost (Sve), tada se formula koristi za prikaz vrijednosti bez uzimanja u obzir ovog kriterija. Ako je odabrana bilo koja druga vrijednost, tada kriterij djeluje na uobičajeni način.
AKO ($ C $ 8 = "(Sve)";
MALI ((LINIJA ($ 13 USD: $ 26 USD) -LINE (USD $ 12)) * ($ D $ 13: $ D $ 26> = $ D $ 8); F13 + ($ 6 USD - $ 7 $));
MALI ((LINIJA ($ 13 USD: $ 26 USD) -LINE (USD $ 12)) * ($ D $ 13: $ D $ 26> = $ D $ 8) * ($ 13 $: $ 26 C $ = 8 C $ 8); F13 + ($ G $ 6- $ G $ 7)))
Ostatak formule sličan je onima gore spomenutim.
Dohvaćanje podataka
Napravite izvještaj o uzorku iz lista 5 u koloni "Kvalitativni učinak, postoci". (iz lista 8, tabela 7)
Da biste uzeli uzorke podataka, morate izvršiti sljedeće korake:
Odredite broj elemenata novog polja prema zadanom stanju unošenjem varijable pomoću operatora InputBox
Izjavite i ponovo prijavite novi niz
Formirajte novi niz. Da biste to učinili, morate postaviti broj prvog elementa novog niza u = 1. Zatim se izvršava ciklus u kojem se upisuje uvjet odabira prema stupcu "Kvalitativne performanse, postoci". Ako je rezultat provjere istinit, tada element analiziranog niza postaje element novog niza.
Prikažite novi element na listu 8
Sub ReportSample ()
Tablice ("List8"). Odaberite
Zatamni A () kao varijantu
ReDim A (1 do n1, 1 do m)
VVOD "List5", A, n1, m, 4
C = InputBox ("Unesite uvjet")
Tablice ("List8"). Ćelije (5.11) = C
Za i = 1 do n1
Ako je A (i,
8) >
d = d + 1
Tablice ("List8"). Ć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)> Tablice ("List8"). Ćelije (5.11) Zatim
Za j = 1 do m
B (u, j) = A (i, j)
u = u + 1
Za i = 1 do d
Za j = 1 do m
Tablice ("List8"). Ćelije (i + 4, j) = B (i, j)
Slika 6. Podaci tablice nakon dohvaćanja
Stvaranje automatskog makronaredbe na odabiru
Uključujemo snimanje makronaredbe. Alati> Makro> Pokreni snimanje> U redu. Na mjestu gdje je tipka za zaustavljanje snimanja pojavit će se kvadrat. Na Sheet5 (izvještaj) odaberite tablicu bez zaglavlja i ukupnih vrijednosti, kopirajte je na Sheet10 (automatski odabir). Odaberite tablicu bez zaglavlja i u stavci izbornika odaberite Podaci> Filtriraj> Automatski filtar> odaberite uvjet> U redu. Označavamo stupac po kojem ćemo sortirati. Završavamo posao makronaredbe.
Sub Macro2Sample ()
"Macro2Setup Macro
Tablice ("Sheet5"). Odaberite
Izbor. Kopiraj
Tablice ("Sheet9"). Odaberite
ActiveSheet. Zalijepi
Doseg ("H5: H17"). Odaberite
Primjena. CutCopyMode = Netačno
Izbor. AutoFilter
ActiveSheet. Raspon ("$ H $ 5: $ H $ 17"). Polje automatskog filtra: = 1, Kriteriji1: = "> 80", _
Operator: = xlAnd
Domet ("G22"). Odaberite
Slika 7. Podaci tabele nakon automatskog odabira
Određivanje maksimalne i minimalne vrijednosti
Odredite maksimalne i minimalne vrijednosti za stupce "Ukupno", "Apsolutne performanse, postoci", "Kvalitativne performanse" (tablica 9, list 10)
Da biste odredili maksimalnu i minimalnu vrijednost, morate učiniti sljedeće:
Postavite referentnu varijablu, koja će biti trenutni minimum (maksimum)
Svaki element populacije uspoređuje se jedan po jedan sa trenutnim minimumom (maksimumom), a ako taj element ne zadovoljava uvjete pretraživanja (u slučaju minimuma, veći je, a u slučaju maksimuma, manji) , tada se referentna vrijednost zamjenjuje vrijednošću uspoređenog elementa
Nakon potpunog skeniranja svih elemenata u varijabli trenutnog minimuma (maksimuma), postoji važeći minimum (maksimum)
Vrijednost minimuma (maksimuma) prikazuje se u odgovarajućim ćelijama
Sub minmax ()
Zatamni A () kao varijantu
n1 = listovi ("list4"). Ćelije (5.12)
m = listovi ("list2"). Ćelije (5.12)
ReDim A (1 do n1, 1 do m)
VVOD "List5", A, n1, m, 4
VIVOD "List10", A, n1, m, 4
VVOD "List10", A, n1, m, 4
Za j = 3 do m
maxA = 0,00001
minA = 1.000.000
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)
Tablice ("Sheet10"). Ćelije (i + 4 + 2, j) = maxA
Tablice ("Sheet10"). Ćelije (i + 4 + 3, j) = minA
Makronaredba je slijed akcija koji je snimljen i spremljen za buduću upotrebu. Spremljeni makronaredbe mogu se reproducirati pomoću posebne naredbe. Drugim riječima, možete snimiti svoje radnje u makronaredbi, spremiti je, a zatim dopustiti drugim korisnicima da jednostavnim pritiskom tipke ponavljaju radnje spremljene u makronaredbi. Ovo je posebno korisno pri distribuciji izvještaja o zaokretnoj tablici.
Recimo da svojim kupcima želite pružiti mogućnost grupiranja izvještaja izvedenih tabela po mjesecima, kvartalima i godini. Tehnički, postupak grupiranja može obaviti bilo koji korisnik, ali neki od vaših klijenata možda neće smatrati potrebnim da ovo razumiju. U takvom slučaju možete snimiti jednu makronaredbu za grupiranje po mjesecima, drugu po tromjesečju i treću po godini. Zatim stvorite tri dugmeta - po jedan za svaki makronaredbe. Tada vaši kupci koji nisu izvedene tablice trebaju samo kliknuti gumb kako bi pravilno grupirali izvještaj izvedene tablice.
Glavna prednost korištenja makronaredbi u izvještajima zaokretne tablice je omogućiti kupcima brzo izvršavanje operacija na zaokretnim tablicama koje obično ne bi mogli učiniti. Ovo značajno povećava efikasnost analize dostavljenih podataka.
Preuzmite bilješku u formatu ili preuzmite s primjerima (unutra se nalazi Excel datoteka s makronaredbama; politika dobavljača ne dozvoljava vam da direktno prenesete datoteku ovog formata na web lokaciju).
Makro snimanje
Pogledajte pivot tablicu prikazanu na sl. 1. Ovu zaokretnu tablicu možete osvježiti desnim klikom unutar nje i odabirom Osvježiti... Ako ste zabilježili radnje kao makronaredbe tijekom ažuriranja zaokretne tablice, tada vi ili bilo koji drugi korisnik možete ponoviti te radnje i ažurirati zaokretnu tablicu kao rezultat izvođenja makronaredbe.
Sl. 1. Snimanje radnji tokom ažuriranja ove pivot 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 Makro snimanje... Idite na karticu Programer vrpce i kliknite gumb Makro snimanje... (Ako ne možete pronaći karticu na vrpci Programer, odaberite karticu File i kliknite na gumb Parametri... U dijaloškom okviru koji se pojavi Excel opcije izaberi kategoriju Prilagođavanje vrpce i na listi s desne strane potvrdite okvir Programer... Kao rezultat, na vrpci će se pojaviti kartica Programer.) Alternativni način započinjanja snimanja makronaredbe je klik na dugme (slika 2).
U dijaloškom okviru Makro snimanje unesite sljedeće informacije o makronaredbi (slika 3):
Imemakro... Ime bi trebalo opisivati radnje koje izvodi makronaredba. Ime mora počinjati slovom ili podvlakom; ne smije sadržavati razmake ili druge ilegalne znakove; ne smije biti isto kao ugrađeno Excel ime ili naziv drugog objekta u radnoj knjizi.
Kombinacijatipke... U ovo polje možete unijeti bilo koje slovo. Postat će dio prečice na tipkovnici koja će se koristiti za reprodukciju makronaredbe. Kombinacija tastera nije obavezna. Po defaultu se kao početak kombinacije nudi samo Ctrl. Ako želite da kombinacija uključuje i Shift, unesite slovo u prozor držeći pritisnutu tipku Shift
Saveu... Ovdje je pohranjena makronaredba. Ako ćete izvještaj o izvedenoj tablici distribuirati drugim korisnicima, odaberite opciju Ovoknjiga... Excel vam takođe omogućava da makro sačuvate u Nova knjiga ili u Lična makro knjiga.
Opis... Opis kreirane makronaredbe unosi se u ovo polje.
Sl. 3. Postavka prozora Makro snimanje
Budući da makronaredba ažurira zaokretnu tablicu, odaberite ime Ažuriranje podataka... Makronaredbi možete dodijeliti i prečicu na tastaturi Ctrl + Shift + Q. Ne zaboravite da ćete nakon kreiranja makronaredbe koristiti ovu prečicu na tastaturi da biste je pokrenuli. Odaberite opciju kao mjesto za pohranu makronaredbe Ova knjiga i kliknite uredu.
Nakon klika u dijaloškom okviru Makro snimanje na dugmetu uredu započinje makro snimanje. U ovom trenutku evidentirat će se sve radnje koje poduzmete u programu Excel.
Desnom tipkom miša kliknite područje PivotTable i odaberite Osvježiti(kao na slici 1, ali u režimu makro snimanja). Nakon ažuriranja zaokretne tablice, pomoću gumba možete zaustaviti postupak snimanja makronaredbi Prekini snimanje kartice Programer... Ili ponovo pritisnite dugme prikazano na sl. 2.
Dakle, upravo ste snimili svoj prvi makro. Sada makro možete izvršiti pomoću prečice na tastaturi Ctrl + Shift + Q.
Makro sigurnosno upozorenje. Treba napomenuti da će se, ako korisnik zabilježi makronaredbe, oni izvršiti bez ikakvih ograničenja na dijelu sigurnosnog podsistema. Međutim, ako distribuirate radnu knjigu koja sadrži makronaredbe, drugim korisnicima morate pružiti priliku da osiguraju da ne postoji rizik od otvaranja radnih datoteka i da pokretanje makronaredbi neće zaraziti sistem virusima. Konkretno, odmah ćete primijetiti da datoteka uzorka korištena u ovom poglavlju neće funkcionirati u potpunosti ako izričito ne dopustite Excelu da na njoj izvodi makronaredbe.
Najlakši način da zaštitite svoje makronaredbe je stvaranje Pouzdane lokacije - mape u koju će se nalaziti samo „Pouzdane“ radne knjige bez virusa. Pouzdana lokacija omogućava vama i vašim klijentima izvršavanje makronaredbi u radnim knjigama bez ikakvih sigurnosnih ograničenja (ovo ponašanje traje sve dok su radne knjige u pouzdanoj mapi).
Da biste postavili pouzdanu lokaciju, slijedite ove korake.
Odaberite karticu vrpce Programer i kliknite na dugme Makro sigurnost... Na ekranu će se pojaviti dijaloški okvir. Centar za povjerenje.
Kliknite na dugme Dodajte novu lokaciju.
Kliknite na dugme Pregled da odredite mapu za radne datoteke u koje imate povjerenja.
Jednom kada navedete pouzdanu lokaciju, proizvoljni makronaredbe će se prema zadanim postavkama pokretati za sve radne knjige u njemu.
Sigurnosni model poboljšan je u programu Excel 2013. Datoteke radnih knjiga koje su ranije bile "pouzdane" sada se pamte; nakon otvaranja Excel radne knjige i klika na gumb Uključi sadržaj Excel pamti ovo stanje. Kao rezultat, ova knjiga spada u kategoriju onih kojima se vjeruje i za vrijeme njenog otvaranja ne postavljaju se nepotrebna pitanja.
Stvaranje korisničkog sučelja pomoću kontrola obrasca
Pokretanje makronaredbe pomoću kombinacije tipki Ctrl + Shift + Q pomoći će kada je u izvještaju izvedene tablice samo jedan makronaredba. (Plus, korisnici moraju znati ovu kombinaciju.) Ali pretpostavimo da svojim klijentima želite pružiti više makronaredbi koje izvode različite radnje. U ovom slučaju, klijentima morate pružiti jasan i jednostavan način izvođenja svake makronaredbe, bez potrebe da pamte kombinacije tastera. Idealno rješenje je jednostavno korisničko sučelje u obliku kolekcije kontrola poput gumba, traka za pomicanje i drugih alata koji vam omogućavaju izvršavanje makronaredbi klikom miša.
Excel vam nudi set alata dizajniranih za stvaranje korisničkog sučelja direktno u proračunskoj tablici. Ti se alati nazivaju kontrolama obrazaca. Osnovna ideja je da u proračunsku tablicu možete staviti kontrolu obrasca i dodijeliti joj makro koji ste ranije snimili. Jednom dodijeljen kontroli, makronaredba će se pokrenuti klikom na tu kontrolu.
Kontrole obrasca mogu se naći u grupi Kontrole obrasca jezičci vrpce Programer... Da biste otvorili paletu kontrola, kliknite u ovoj grupi gumb Zalijepi(sl. 4).
Sl. 4. Kontrola obrasca Dugme
Napomena: pored kontrola obrasca, paleta sadrži i ActiveX kontrole... Iako su slični, programski su potpuno različiti objekti. Kontrole obrasca sa svojim ograničenim mogućnostima i jednostavnim postavkama, posebno dizajniranim za postavljanje na radne listove. U isto vrijeme ActiveX kontrole koristi se prvenstveno u prilagođenim oblicima. Neka vam bude pravilo da na svoje radne listove postavljate samo kontrole obrazaca.
Trebali biste odabrati kontrole koje najbolje odgovaraju zadatku. U ovom primjeru kupci trebaju biti u mogućnosti da ažuriraju izvedenu tablicu klikom na dugme. Kliknite na kontrolu Dugme, pomaknite pokazivač miša na položaj na radnom listu na kojem želite da se nalazi gumb i kliknite.
Nakon što stavite dugme u tablicu, otvorit će se dijaloški okvir Dodijelite makronaredbu objekt(sl. 5). Odaberite potrebnu makronaredbu (u našem slučaju - Ažuriranje podataka snimljeno ranije) i kliknite na uredu.
Sl. 5. Odaberite makronaredbu koja će se dodijeliti gumbu i kliknite gumb uredu... U ovom slučaju, trebali biste koristiti makronaredbu Ažuriranje podataka
Nakon postavljanja svih potrebnih kontrola u izvještaj izvedene tablice, možete formatirati tablicu da biste stvorili osnovno sučelje. Na sl. 6 prikazuje izvještaj izvedene tablice nakon formatiranja.
Izmjena snimljenog makronaredbe
Kao rezultat snimanja makronaredbe, Excel stvara modul koji čuva radnje koje ste izvršili. Sve snimljene radnje predstavljene su linijama VBA koda koji čine makronaredbu. Možete dodati razne funkcije u svoje izvještaje izvedene tablice prilagođavanjem VBA koda da biste dobili željene rezultate. Da bismo lakše razumjeli kako sve ovo funkcionira, stvorimo novi makronaredba koja prikazuje prvih pet zapisa kupaca. Idite na karticu Programer i kliknite na dugme Makro snimanje... Dijaloški okvir prikazan na sl. 7. Dajte naziv kreiranom makronaredbi PrviKorisnici i navedite lokaciju spremanja Ova knjiga... Kliknite uredu za početak snimanja makronaredbe.
Nakon što započnete snimanje, kliknite strelicu pored okvira Ime kupca, odaberite Filtriraj po vrijednosti i opcija Prvih 10(Slika 8a). U dijaloškom okviru koji se pojavi konfigurirajte postavke kao što je prikazano na sl. 8b. Ova podešavanja vam govore da prikažete podatke o pet najboljih kupaca u smislu prodaje. Kliknite uredu.
Sl. 8. Odaberite filtar (a) i prilagodite parametre (b) da biste prikazali pet najboljih kupaca po prodaji
Nakon što uspješno zabilježite sve korake potrebne za pronalaženje top 5 potencijalnih kupaca, idite na karticu Programer i kliknite na dugme Prekini snimanje.
Sada imate makronaredbu koja će filtrirati zaokretnu tablicu za dohvaćanje top 5 kupaca prodaje. Potrebno je natjerati makronaredbu da reagira na stanje trake za pomicanje, tj. pomoću trake za pomicanje morate makronaredbi naznačiti broj kupaca čiji će podaci biti prikazani u izvještaju pivot tablice. Tako će, pomoću trake za pomicanje, korisnik moći dohvatiti prvih pet, osam ili trideset i dva najbolja klijenta po svom nahođenju.
Da biste dodali traku za pomicanje u proračunsku tablicu, idite na karticu Programer, pritisnite gumb Zalijepi, odaberite kontrolu na paleti Traka za pomicanje i stavite ga na radni list. Desni klik na kontrolu Traka za pomicanje Format objekta... Otvorit će se dijaloški okvir Kontrolni format(sl. 9). U njemu napravite sljedeće promjene u postavkama: Minimalna vrijednost parametru dodijelite vrijednost 1 Maksimalna vrijednost- vrijednost 200, i na terenu Komunikacija putem ćelije unesite vrijednost $ M $ 2 za prikaz vrijednosti klizača u ćeliji M2. Kliknite na dugme uredu za primjenu prethodno navedenih postavki.
Sada trebate podudarati sa nedavno snimljenim makronaredbama PrviKorisnici sa kontrolom Traka za pomicanje na radnom listu. Desni klik na kontrolu Traka za pomicanje i u kontekstnom izborniku odaberite naredbu Dodijelite makronaredbu da biste otvorili dijaloški okvir Dodjela makronaredbi. Dodijelite snimljeni makronaredbu na klizaču FirstN kupci... Makronaredba će se pokrenuti svaki put kad se klikne na traku za pomicanje. Testirajte stvorenu traku za pomicanje. Nakon klika na traku, makronaredba će se pokrenuti FirstN kupci i broj u ćeliji M2 će se promijeniti kako bi označio status trake za pomicanje. Broj u ćeliji M2 važan je jer se koristi za vezanje makronaredbe na traku za pomicanje.
Preostalo je samo učiniti da makro obradi broj u ćeliji M2 povezivanjem s trakom za pomicanje. Da biste to učinili, morate otići na VBA kôd makronaredbe. Da biste to učinili, idite na karticu Programer i kliknite na dugme Makro... Otvorit će se dijaloški okvir Makro(slika 10). U njemu možete pokretati, brisati i uređivati odabrani makro. Da biste prikazali VBA kôd makronaredbe na ekranu, odaberite makronaredbu i kliknite gumb Uredi.
Sl. 10. Da biste pristupili VBA makro kodu PrviKorisnici, odaberite makronaredbu i kliknite gumb Uredi
Na ekranu će se pojaviti prozor uređivača Visual Basic sa VBA kodom makronaredbe (slika 11). Cilj vam je zamijeniti kodirani broj 5, koji je postavljen tijekom snimanja makronaredbi, vrijednošću u ćeliji M2 koja je vezana za traku za pomicanje. U početku je snimljen makronaredba koja prikazuje pet najboljih kupaca sa najvećim prihodom.
Uklonite broj 5 iz koda i umjesto njega unesite sljedeći izraz:
ActiveSheet.Range ("M2") .Vrijednost
Dodajte dva reda na početak makronaredbe da biste očistili filtere:
Raspon ("A4") .Odaberite
ActiveSheet.PivotTables ("PivotTable1") .PivotFields ("Ime kupca") .ClearAllFilters
Makro kod bi sada trebao izgledati kao onaj prikazan na sl. 12.
Zatvorite uređivač Visual Basic i vratite se u izvještaj izvedene tablice. Testirajte traku za pomicanje povlačenjem klizača do 11. Makronaredba bi se trebala pokrenuti i filtrirati 11 najboljih zapisa o prodaji.
Sinkronizirajte dvije pivot tabele s jednim padajućim padajućim menijem
Izvještaj prikazan na sl. 13 sadrži dvije stožerne tablice. Svaki od njih ima polje stranice koje vam omogućava da odaberete prodajni prostor. Problem je u tome što svaki put kada odaberete tržište u polju stranice jedne pivot tablice, morate odabrati isto tržište u polju stranice druge pivot tablice. Sinhronizacija filtara između dvije tablice tijekom faze analize podataka nije veliki problem, ali postoji šansa da ćete vi ili vaši kupci to ipak zaboraviti učiniti.
Sl. 13. Dvije zaokretne tablice sadrže polja stranica koja filtriraju podatke prema tržištu. Da biste analizirali podatke jedinstvenog tržišta, morate sinhronizirati obje pivot tabele
Jedan od načina da se ove zaokretne tablice sinhroniziraju je upotreba padajućeg popisa. Ideja je snimiti makro koji odabire željeno tržište sa polja Tržište prodaje u obje tablice. Zatim morate stvoriti padajući popis i popuniti ga imenima prodajnih tržišta iz dvije stožerne tablice. Konačno, snimljeni makronaredba mora se modificirati tako da filtrira obje zaokretne tablice koristeći vrijednosti s padajućeg popisa. Da biste riješili ovaj problem, morate izvršiti sljedeće korake.
1. Stvorite novi makronaredbu i dajte mu ime SynchMarkets... Kada započne snimanje, odaberite u polju Tržište za oboje pivot tabele prodaje Kalifornija i zaustavite snimanje makronaredbe.
2. Prikažite paletu kontrola obrasca i dodajte padajući popis na radni list.
3. Stvorite kodiranu listu svih tržišta zaokretnih tablica. Imajte na umu da je prva stavka na listi (Sve). Trebali biste omogućiti ovaj element ako želite s padajuće liste moći odabrati sva tržišta.
4. U ovom trenutku izvještaj o zaokretnoj tablici trebao bi izgledati kao onaj prikazan na sl. četrnaest.
Sl. 14. Na raspolaganju su vam svi potrebni alati: makronaredba koja mijenja polje Tržište prodaje obje pivot tablice, padajući popis i popis svih tržišta prodaje sadržanih u pivot tablici
5. Desnom tipkom miša kliknite padajući popis i odaberite naredbu Format objekta za prilagodbu kontrole.
6. Prvo, postavite početni raspon vrijednosti koji se koriste za popunjavanje padajućeg popisa, kao što je prikazano na slici 6-7. 15. U ovom slučaju govorimo o listi prodajnih tržišta koju ste kreirali u koraku 3. Zatim navedite ćeliju koja prikazuje serijski broj odabranog elementa (u ovom primjeru ovo je ćelija H1). Parametar Broj linija na listi određuje koliko će se redova istovremeno prikazati na padajućem popisu. Kliknite na dugme uredu.
Sl. 15. Postavke padajućeg popisa moraju ukazivati na listu prodajnih tržišta kao početni raspon vrijednosti i definirati ćeliju H1 kao sidrište
7. Sada imate priliku odabrati tržište prodaje sa padajućeg popisa, a također i definirati pridruženi serijski broj u ćeliji H1 (slika 16). Postavlja se pitanje: zašto se koristi vrijednost indeksa umjesto pravog imena tržišta? Jer padajući meni ne vraća ime, već broj. Na primjer, ako s padajućeg popisa odaberete Kalifornija, vrijednost 5 pojavljuje se u ćeliji H1, što znači da je Kalifornija peta stavka na popisu.
Sl. 16. Padajući popis sada je popunjen imenima tržišta, a serijski broj izabranog tržišta prikazan je u ćeliji H1
8. Da biste koristili serijski broj umjesto tržišnog naziva, morate ga proslijediti pomoću funkcije INDEX.
9. Unesite funkciju INDEX, koja pretvara serijski broj iz ćelije H1 u značajnu vrijednost.
10. INDEX funkcija uzima dva argumenta. Prvi argument predstavlja raspon vrijednosti na listi. U većini ćete slučajeva koristiti isti raspon koji popunjava padajući izbornik. Drugi argument je redni broj. Ako se u ćeliju unese serijski broj (na primjer, u ćeliju H1, kao na slici 17), tada se možete jednostavno pozvati na ovu ćeliju.
Sl. 17. Funkcija INDEX u ćeliji I1 pretvara sekvencijski broj pohranjen u ćeliji H1 u vrijednost. Vrijednost u ćeliji I1 upotrijebit ćete za promjenu makronaredbe
11. Uredite makronaredbu SynchMarkets koristeći vrijednost u ćeliji I1 umjesto tvrdo kodirane vrijednosti. Idite na karticu Programer i kliknite na dugme Makro... Na ekranu će se pojaviti dijaloški okvir, kao što je prikazano na sl. 18. Odaberite makronaredbu u njemu SynchMarkets i kliknite gumb Promijeni.
Sl. 18. Da biste pristupili VBA kodu makronaredbe, odaberite makronaredbu SynchMarkets i kliknite Uredi
12. Prilikom snimanja makronaredbe, iz polja u obje zaokretne tablice odabrali ste prodajno područje Kalifornije. Tržište prodaje... Kao što se vidi sa Sl. 19, tržište Kalifornije sada je čvrsto kodirano u VBA makro kodu.
13. Zamijenite "California" s Activesheet.Range ("I1") .Value, koja se odnosi na vrijednost u ćeliji I1. U ovom trenutku makro kod bi trebao izgledati poput prikazanog na sl. 20. Nakon izmjene makronaredbe, zatvorite Visual Basic Editor i vratite se u proračunsku tablicu.
Sl. 20. Zamijenite "California" s ActiveSheet.Range ("I1"). Vrijednost i zatvorite Visual Basic Editor
14. Preostaje samo osigurati izvršenje makronaredbe prilikom odabira prodajnog područja s padajuće liste. Desni klik na padajući meni i odaberite opciju Dodijelite makronaredbu... Odaberite makronaredbu SynchMarket i kliknite na dugme uredu.
15. Sakrijte retke i stupce s poljima stranica u pivot tablicama i popisu tržišta i formulama indeksa koje ste kreirali.
Na sl. 21 prikazuje konačni rezultat. Sada imate korisničko sučelje koje omogućava kupcima da odaberu svoje prodajno područje u obje zaokretne tablice pomoću jednog padajućeg popisa.
Kada s padajućeg popisa odaberete novu stavku, stupci se automatski mijenjaju kako bi se prilagodili svim podacima prikazanim u njima. Ovakvo ponašanje programa prilično je dosadno prilikom formatiranja predloška radnog lista. Možete to spriječiti desnim klikom na pivotTable i odabirom Opcije pivot tabele... Na ekranu će se pojaviti istoimeni dijaloški okvir u kojem trebate ukloniti potvrdni okvir Automatski promijeni veličinu stupaca pri osvježavanju.
Bilješka zasnovana na Jeleninoj knjizi, Alexander. ... Poglavlje 12.
Korištenjem Excel alata možete slučajnim redoslijedom, jednim uslovom ili više njih odabrati određene podatke iz raspona. Za rješavanje takvih problema, u pravilu se koriste formule niza ili makronaredbe. Pogledajmo neke primjere.
Kako izvršiti odabir u Excelu prema stanju
Kada se koriste formule polja, odabrani podaci prikazani su u zasebnoj tablici. Koja je prednost ove metode u odnosu na konvencionalni filter.
Izvorna tabela:
Prvo, naučimo kako napraviti odabir na osnovu jednog numeričkog kriterija. Zadatak je odabrati iz tablice proizvode s cijenom većom od 200 rubalja. Jedno od rješenja je primjena filtriranja. Kao rezultat, u izvornoj će tablici ostati samo oni proizvodi koji udovolje zahtjevu.
Drugo rješenje je upotreba formule niza. Redovi koji odgovaraju zahtjevu uklapaju se u zasebni izvještaj tabele.
Prvo, kreiramo praznu tablicu pored izvorne: duplikata zaglavlja, broj redaka i stupaca. Nova tablica zauzima opseg E1: G10. Sada odaberite E2: E10 (stupac "Datum") i unesite sljedeću formulu: ( }.
Da biste dobili formulu niza, pritisnite kombinaciju tipki Ctrl + Shift + Enter. U sljedeći stupac - "Proizvod" - unosimo sličnu formulu niza: ( ). Promijenio se samo prvi argument funkcije INDEX.
U stupac "Cijena" unesite istu formulu niza, mijenjajući prvi argument funkcije INDEX.
Kao rezultat, dobili smo izvještaj o robi s cijenom većom od 200 rubalja.
Takav je odabir dinamičan: kada se upit promijeni ili se novi proizvodi pojave u izvornoj tablici, izvještaj će se automatski promijeniti.
Zadatak broj 2 - odaberite iz originalne tabele robu koja je puštena u prodaju 20.09.2015. Odnosno, kriterij odabira je datum. Radi praktičnosti, željeni ćemo datum unijeti u zasebnu ćeliju, I2.
Slična se formula niza koristi za rješavanje problema. Samo umjesto kriterija).
Slične formule unose se u druge stupce (vidi gore navedeni princip).
Sada koristimo kriterij teksta. Umjesto datuma u ćeliju I2, unesite tekst "Proizvod 1". Promijenimo malo formulu niza: ( }.
Tako sjajna funkcija odabira u Excelu.
Izbor po više uslova u Excelu
Prvo, uzmimo dva numerička kriterija:
Zadatak je odabrati robu koja košta manje od 400 i više od 200 rubalja. Kombinirajmo uvjete sa znakom "*". Formula niza izgleda ovako: ( }.!}
Ovo je za prvu kolonu tablice izvještaja. Za drugi i treći - mijenjamo prvi argument funkcije INDEX. Rezultat:
Da bismo izvršili odabir prema nekoliko datuma ili numeričkim kriterijima, koristimo slične formule niza.
Slučajno uzorkovanje u Excelu
Kada korisnik radi s velikom količinom podataka, može biti potrebno slučajno uzorkovanje za naknadnu analizu. Svakom retku možete dodijeliti slučajni broj, a zatim primijeniti sortiranje na odabir.
Izvorni skup podataka:
Prvo umetnimo dva prazna stupca s lijeve strane. U ćeliju A2 zapisujemo formulu RAND (). Pomnožimo ga s cijelom kolonom:
Sada kopiramo stupac sa slučajnim brojevima i zalijepimo ga u stupac B. To je neophodno kako se ti brojevi ne bi mijenjali kada se novi podaci unesu u dokument.
Da biste umetnuli vrijednosti, a ne formulu, kliknite desnom tipkom miša na stupac B i odaberite alat za lijepljenje. U prozoru koji se otvori stavite kvačicu ispred stavke "Vrijednosti":
Sada možete sortirati podatke u stupcu B u rastućem ili silaznom redoslijedu. Redoslijed prikazivanja izvornih vrijednosti također će se promijeniti. Odaberemo bilo koji broj linija odozgo ili odozdo - dobit ćemo slučajni uzorak.
Razno (39)
Excel pogreške i poteškoće (3)
Kako mogu dobiti listu jedinstvenih (ne dupliciranih) vrijednosti?
Zamislite veliku listu različitih imena, imena, brojeva osoblja itd. I potrebno je ostaviti listu svih istih imena s ove liste, ali tako da se ne ponavljaju - tj. uklonite sve duplikate sa ove liste. Kako se to drugačije naziva: kreirajte listu jedinstvenih elemenata, listu neponovljivih, bez duplikata. Postoji nekoliko načina za to: ugrađeni Excel alati, ugrađene formule i, konačno, upotreba koda Visual Basic za aplikaciju (VBA) i pivot tabele. Ovaj članak razmotrit će svaku od opcija.
Korištenje ugrađenih funkcija u programu Excel 2007 i novijim verzijama
U programima Excel 2007 i 2010 ovo je lako poput ljuštenja krušaka - postoji posebna naredba koja se zove -. Nalazi se na kartici Podaci pododjeljak Alati za podatke
Kako se koristi ova naredba. Označite stupac (ili nekoliko) s podacima u kojima treba brisati dvostruke zapise. Idite na karticu Podaci -Uklonite duplikate.
Ako odaberete jedan stupac, ali pored njega bit će više stupaca s podacima (ili barem jednim stupcem), tada će Excel ponuditi da odaberete: proširite opseg odabira s ovim stupcem ili ostavite odabir kakav jest i samo izbrišite podatke u odabranom opsegu. Važno je zapamtiti da ako ne proširite raspon, podaci će se promijeniti u samo jednom stupcu, a podaci u susjednom stupcu ostat će nepromijenjeni.
Pojavit će se prozor s opcijama za uklanjanje duplikata 
Označite okvire ispred tih stupaca, duplikata u kojima želite izbrisati i kliknite U redu. Ako su i zaglavlja podataka smještena u odabranom opsegu, onda je bolje postaviti zastavicu Moji podaci sadrže zaglavlja kako bi se izbjeglo slučajno brisanje podataka u tablici (ako se iznenada potpuno podudaraju sa vrijednošću u naslovu).
1. metod: Napredni filter
U slučaju Excel 2003, stvari su složenije. Ne postoji takav alat kao Uklonite duplikate... Ali s druge strane, postoji tako divan alat kao Napredni filter... U 2003. godini ovaj alat se može naći u Podaci -Filter -Napredni filter... Ljepota ove metode je u tome što je možete koristiti za stvaranje liste u drugom rasponu, a ne za kvarenje izvornih podataka. U 2007-2010. Godini Excel je i tamo, ali pomalo skriven. Smješteno na kartici Podaci, Grupa Poredaj i filtriraj - Napredno
Kako ga koristiti: pokrenite navedeni alat - pojavit će se dijaloški okvir: 
- Liječenje: Mi biramo Kopirajte rezultat na drugo mjesto.
- Raspon liste: Odabir raspona s podacima (u našem slučaju to je A1: A51).
- Raspon kriterija: u ovom slučaju, ostavite ga praznim.
- Kopiraj u opseg: označavamo prvu ćeliju za prikaz podataka - bilo koju praznu (na slici - E2).
- Označite okvir Samo jedinstveni zapisi.
- Pritisnemo uredu.
Bilješka: ako želite smjestiti rezultat na drugi list, onda jednostavno ne možete odrediti drugi list. Moći ćete odrediti ćeliju na drugom listu, ali ... Jao i ja ... Excel će dati poruku da ne može kopirati podatke na druge listove. Ali i to se može zaobići, i to vrlo jednostavno. Samo trebaš trčati Napredni filter sa lista na koji želimo smjestiti rezultat. A kao početne podatke odabiremo podatke s bilo kojeg lista - to je dopušteno.
Rezultat također ne možete prenijeti u druge ćelije, ali podatke filtrirajte na mjestu. Na podatke to neće utjecati ni na koji način - to će biti uobičajeno filtriranje podataka.
Da biste to učinili, samo trebate odabrati u odjeljku Obrada Filtrirajte listu na mjestu.
Metod 2: Formule
Ovu je metodu teže razumjeti za neiskusne korisnike, ali ona stvara listu jedinstvenih vrijednosti bez promjene izvornih podataka. Pa, to je i dinamičnije: ako promijenite podatke u izvornoj tablici, rezultat će se također promijeniti. Ovo je ponekad korisno. Pokušat ću na prstima objasniti što i što: na primjer, imate listu s podacima u stupcu A (A1: A51, gdje je A1 naslov). Popis ćemo prikazati u stupcu C, počevši od ćelije C2. Formula u C2 bit će sljedeća:
(= INDEKS ($ A $ 2: $ A $ 51; MALO (AKO (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51) = 0; RED ($ A $ 1: $ A $) 50)); 1)))
(= INDEKS ($ A $ 2: $ A $ 51; MALO (AKO (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51) = 0; RED ($ A $ 1: $ A $) 50)); 1)))
Detaljna analiza rada ove formule data je u članku:
Treba napomenuti da je ova formula niz formula. To se može naznačiti vitičastim zagradama koje prilažu ovu formulu. I takva formula unosi se u ćeliju pomoću prečice na tastaturi - Ctrl+Shift+Enter... Nakon što smo ovu formulu unijeli u C2, moramo je kopirati i zalijepiti u nekoliko redaka kako bismo precizno prikazali sve jedinstvene elemente. Čim se vrati formula u donjim ćelijama #BROJ!- to znači da su prikazani svi elementi i nema smisla rastezati donju formulu. Da biste izbjegli pogrešku i učinili formulu univerzalnijom (bez istezanja svaki put dok se pogreška ne pojavi), možete koristiti jednostavnu provjeru:
za Excel 2007 i novije verzije:
(= IFERROR (INDEX ($ A $ 2: $ A $ 51; MALI (AKO (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51) = 0; STRING ($ A $ 1: $ 50 USD)); 1)); ""))
(= IFERROR (INDEKS ($ A $ 2: $ A $ 51; MALI (AKO (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51) = 0; RED ($ A $ 1: $) 50 USD)); 1)); ""))
za Excel 2003:
(= IF (ISH (SMALL (IF (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51) = 0; RED ($ A $ 1: $ A $ 50)); 1)); ""; INDEKS ($ A $ 2: $ A $ 51; MALO (AKO (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51) = 0; RED ($ A $ 1: $ A 50 USD)); 1))))
(= IF (ISERR (SMALL (IF (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51)) = 0; RED ($ A $ 1: $ A $ 50)); 1)); ""; INDEKS ($ A $ 2: $ A $ 51; MALO (AKO (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51) = 0; RED ($ A $ 1: $ A 50 USD)); 1))))
Tada umjesto greške #BROJ! (#NUM!) imat ćete prazne ćelije (ne potpuno prazan, naravno - s formulama :-)).
Malo više detalja o razlikama i nijansama formula ESLIOSHIBKA i IF (EOSH možete pročitati u ovom članku: Kako prikazati 0 u ćeliji s formulom umjesto greške
3. metod: VBA kôd
Ovaj pristup zahtijeva razlučivanje makronaredbi i osnovno znanje o radu s njima. Ako niste sigurni u svoje znanje, preporučujem da prvo pročitate ove članke:
- Šta je makro i gdje ga mogu pronaći? uz članak je priložen video vodič
- Šta je modul? Koji moduli postoje? morat će shvatiti gdje ubaciti donje kodove
Oba dolje navedena koda treba staviti u standardni modul... Makronaredbe moraju biti dozvoljene.
Ostavimo početne podatke istim redoslijedom - lista s podacima nalazi se u koloni "A" (A1: A51, gdje je A1 naslov)... Samo ćemo listu prikazati ne u koloni C, već u koloni E, počevši od ćelije E2:
Sub Extract_Unique () Dim vItem, avArr, li Long ReDim avArr (1 Do Rows.Count, 1 To 1) s novom kolekcijom o grešci Nastavi sljedeće za svaki vItem u rasponu ("A2", ćelije (Rows.Count, 1) . End (xlUp)). Vrijednost "(! LANG: Ćelije (retci. Broj, 1). End (xlUp) - definira zadnju popunjenu ćeliju u stupcu A. Dodaj vItem, CStr (vItem) Ako je Err = 0 Tada je li = li + 1: avArr (li, 1) = vItem Else: Err.Clear End If Next End s If li then .Resize (li) .Value = avArr End Sub
Pomoću ovog koda možete izvući jedinstvene vrijednosti ne samo iz jednog stupca, već i iz bilo kojeg raspona stupaca i redaka. Ako umjesto crte
Raspon ("A2", Cells(Rows.Count, 1).End(xlUp)).Value !}
navedite Selection.Value, tada će rezultat koda biti lista jedinstvenih elemenata iz raspona odabranog na aktivnom listu. Tek tada bi bilo lijepo promijeniti izlaznu ćeliju vrijednosti - umjesto
stavite onu u kojoj nema podataka.
Možete odrediti i određeni raspon:
Raspon ("C2", ćelije (retci.broj, 3). Kraj (xlUp)). Vrijednost
Univerzalni kod za odabir jedinstvenih vrijednosti
Donji kod se može primijeniti na bilo koji raspon. Dovoljno je pokrenuti ga, navesti raspon sa vrijednostima za odabir samo onih koji se ne ponavljaju (dopušteno je više stupaca) i ćeliju za prikaz rezultata. Navedene ćelije će se skenirati, iz njih će se odabrati samo jedinstvene vrijednosti (prazne ćelije se preskaču) i rezultirajuća lista će biti napisana počevši od navedene ćelije.
| Sub Extract_Unique () Dim x, avArr, li as Long Dim avVals Dim rVals As Range, rResultCell As Range On Error Resume Next "zatraži adresu ćelija za odabir jedinstvenih vrijednosti Postavi rVals = Application.InputBox ( "Navedite raspon ćelija za uzorkovanje jedinstvenih vrijednosti", "Zahtjev za podacima", "A2: A51", Tip: = 8) Ako rVals tada nije ništa "ako se klikne na gumb Odustani Izađi iz Sub End If "ako je navedena samo jedna ćelija, nema smisla birati Ako je rVals.Count = 1, onda MsgBox "Da biste filtrirali jedinstvene vrijednosti, morate navesti više od jedne ćelije", vbInformation, "www.site" Izlaz iz Sub End If "odrezati prazne redove i stupce izvan radnog opsega Postavi rVals = Preseci (rVals, rVals.Parent.UsedRange) "ako su navedene samo prazne ćelije izvan radnog opsega Ako rVals nije ništa, onda MsgBox "Nema dovoljno podataka za odabir vrijednosti", vbInformation, "www.site" Izlaz iz završetka ako je avVals = rVals.Value "zatraži ćeliju za prikaz rezultata Postavi rResultCell = Application.InputBox ( "Navedite ćeliju za umetanje odabranih jedinstvenih vrijednosti", "Zahtjev za podacima", "E2", Tip: = 8) Ako rResultCell tada nije ništa "ako se klikne na gumb Odustani Izađi iz Sub End If "definirajte maksimalnu moguću dimenziju niza za rezultat ReDim avArr (1 u redove.broj, 1 u 1) "pomoću objekta Collection "odaberite samo jedinstvene zapise, "jer zbirke ne mogu sadržavati dvostruke vrijednosti S novom kolekcijom o grešci Nastavite dalje za svaki x In avVals If Len (CStr (x)) Tada "preskoči prazne ćelije.Dodaj x, CStr (x) "ako dodani element već postoji u Zbirci, pojavit će se greška "ako nema greške, ova vrijednost još nije unesena, "dodaj u rezultirajući niz Ako je Err = 0 Tada je li = li + 1 avArr (li, 1) = x Else "obavezno obrišite objekt Greška Err.Clear End If End If Next End with "zapisati rezultat na list, počevši od navedene ćelije Ako li Tada je rResultCell.Cells (1, 1) .Resize (li) .Value = avArr Kraj Sub |
Sub Extract_Unique () Dim x, avArr, li Kao Long Dim avVals Dim rVals As Range, rResultCell As Range On Error Resume Next "traži adresu ćelija za odabir jedinstvenih vrijednosti Set rVals = Application.InputBox (" Navedite opseg ćelija za odabir jedinstvenih vrijednosti "," Zahtjev za podacima "," A2: A51 ", Tip: = 8) Ako je rVals Ništa onda" ako se klikne na gumb Odustani Izađi iz potkraj Ako "ako je navedena samo jedna ćelija , nema smisla birati If rVals.Count = 1 Zatim MsgBox "Da biste odabrali jedinstvene vrijednosti, trebate navesti više od jedne ćelije", vbInformation, "www.site" Exit Sub End If "odrežite prazne redove i stupce izvan radni opseg Postavite rVals = Intersect (rVals, rVals.Parent.UsedRange) "ako su van radnog opsega navedene samo prazne ćelije Ako je rVals Ništa, onda MsgBox" Nema dovoljno podataka za odabir vrijednosti ", vbInformation," www..Value " (! LANG: zatraži ćeliju za prikaz rezultata Set rResultCell = Application.InputBox ("Укажите ячейку для вставки отобранных уникальных значений", "Запрос данных", "E2", Type:=8) If rResultCell Is Nothing Then "если нажата кнопка Отмена Exit Sub End If "определяем максимально возможную размерность массива для результата ReDim avArr(1 To Rows.Count, 1 To 1) "при помощи объекта Коллекции(Collection) "отбираем только уникальные записи, "т.к. Коллекции не могут содержать повторяющиеся значения With New Collection On Error Resume Next For Each x In avVals If Len(CStr(x)) Then "пропускаем пустые ячейки.Add x, CStr(x) "если добавляемый элемент уже есть в Коллекции - возникнет ошибка "если же ошибки нет - такое значение еще не внесено, "добавляем в результирующий массив If Err = 0 Then li = li + 1 avArr(li, 1) = x Else "обязательно очищаем объект Ошибки Err.Clear End If End If Next End With "записываем результат на лист, начиная с указанной ячейки If li Then rResultCell.Cells(1, 1).Resize(li).Value = avArr End Sub!}
Metod 4: pivot tabele
Pomalo nestandardan način izdvajanja jedinstvenih vrijednosti.
- Odaberite jedan ili više stupaca u tablici, idite na karticu Umetni-Grupa Tabela -Izvedena tabela
- U dijaloškom okviru Napravite zaokretnu tablicu provjeriti ispravnost odabira raspona podataka (ili postaviti novi izvor podataka)
- naznačite mjesto pivot tablice:
- Novi radni list
- Postojeći radni list
- potvrdite stvaranje pritiskom na tipku uredu
Jer pivot tablice, prilikom obrade podataka koji su smješteni u područje redaka ili stupaca, iz njih odaberite samo jedinstvene vrijednosti za naknadnu analizu, tada se od nas ne traži apsolutno ništa osim stvaranja pivot tablice i postavljanja podataka željeni stupac u području redaka ili stupaca.
Koristeći datoteku priloženu uz članak kao primjer, I:
Koje su neugodnosti rada s pivot tablicama u ovom slučaju: ako se izvorni podaci promijene, pivot tablica će se morati ručno ažurirati: Odaberite bilo koju ćeliju pivot tablice - Desni gumb miša - Osvježiti ili tab Podaci -Osvježi sve -Osvježiti... A ako se izvorni podaci dinamički dopunjavaju i još gore, morat ćete ponovno odrediti opseg izvornih podataka. I još jedan nedostatak - podaci unutar pivot tabele ne mogu se mijenjati. Stoga, ako je ubuduće potrebno raditi s rezultirajućom listom, nakon stvaranja željene liste pomoću sažete liste mora se kopirati i zalijepiti na željeni list.
Da biste bolje razumjeli sve korake i naučili kako koristiti pivot tablice, toplo preporučujem da pročitate članak Opće informacije o pivot tablicama - uz njega je priložen video vodič u kojem jasno demonstriram jednostavnost i praktičnost rada s osnovnim karakteristike pivot tabela.
U priloženom primjeru, pored opisanih tehnika, napisana je i malo složenija varijacija izdvajanja jedinstvenih elemenata pomoću formule i koda, i to: vađenje jedinstvenih elemenata prema kriteriju... O čemu govorimo: ako u jednom stupcu prezimena, a u drugom (B) postoje neki podaci (u datoteci su to mjeseci) i želite izdvojiti jedinstvene vrijednosti stupca B samo za izabrano prezime. Primjeri takvih jedinstvenih izdvajanja nalaze se na listu Vađenje prema kriteriju.
Primjer preuzimanja:
(108,0 KiB, 14,152 preuzimanja)
Je li članak pomogao? Podijelite vezu sa svojim prijateljima! Video lekcije
