Uzorkovanje podataka

Kreirajte izvještaj o uzorku iz Sheet5 u koloni "Kvalitativni akademski učinak, posto". (iz tabele 8, tabele 7)

Za preuzimanje podataka uradite sljedeće:

Odredite broj elemenata novog niza prema datom uslovu unosom varijable koristeći InputBox operator

Deklarirajte i ponovo deklarirajte novi niz

Formirajte novi niz. Da biste to učinili, potrebno je postaviti broj prvog elementa novog niza u=1. Zatim se izvodi petlja u kojoj se upisuje uslov izbora za kolonu "Kvalitativna akademska uspješnost, posto". Ako je rezultat testa tačan, tada element raščlanjenog niza postaje element novog niza.

Prikaži novi element na listu 8

Sub ReportSelection()

Sheets("Sheet8"). Odaberite

Dim A () Kao varijanta

ReDim A (1 do n1, 1 do m)

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

C = InputBox("Unesite uslov")

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

Za i = 1 do n1

Ako je A(i,

8) >

d=d+1

Sheets("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) > Sheets ("Sheet8"). Ć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

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

Fig.6. Podaci tabele nakon odabira

Kreirajte automatski makro za odabir

Omogućite snimanje makroa. Alati>Makro>Pokreni snimanje>OK. Kvadrat će se pojaviti na mjestu gdje je dugme za zaustavljanje snimanja. Na Sheet5 (izvještaj), odaberite tabelu bez zaglavlja i ukupnih iznosa, kopirajte je u Sheet10 (automatski odabir). Odaberite tabelu bez zaglavlja i u stavci izbornika odaberite Podaci>Filter>Autofilter>odaberite stanje>OK. Označavamo kolonu po kojoj ćemo sortirati. Završavamo makro.

Sub Macro2Select()

" Macro2Selection Macro

Sheets("Sheet5"). Odaberite

izbor. Kopiraj

Sheets("Sheet9"). Odaberite

ActiveSheet. paste

Raspon ("H5:H17"). Odaberite

aplikacija. CutCopyMode=Netačno

izbor. AutoFilter

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

Operator:=xlAnd

Raspon ("G22"). Odaberite

Fig.7. Podaci tabele nakon automatskog odabira

Određivanje maksimalne i minimalne vrijednosti

Odredite maksimalne i minimalne vrijednosti za kolone "Ukupno", "Apsolutni napredak, procenat", "Kvalitativni napredak" (tabela 9, list 10)

Da biste odredili maksimalne i minimalne vrijednosti, učinite sljedeće:

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

Svaki element populacije se poredi redom sa trenutnim minimumom (maksimumom), a ako ovaj element ne zadovoljava uslove pretraživanja (u slučaju minimuma je veći, a u slučaju maksimuma manji), tada se vrijednost standarda zamjenjuje vrijednošću upoređivanog elementa

Poslije full view od svih elemenata u trenutnoj minimalnoj (maksimalnoj) varijabli je stvarni minimum (maksimum)

Vrijednost minimuma (maksimuma) se prikazuje u odgovarajućim ćelijama

subminmax()

Dim A () Kao varijanta

n1 = Listovi("Sheet4"). ćelije (5,12)

m = Listovi("Sheet2"). ćelije (5,12)

ReDim A (1 do n1, 1 do m)

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

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

VVOD "Lak10", A, n1, m, 4

Za j = 3 do m

maxA = 0,00001

minA = 1000000

Za i = 1 do n1

Ako je A (i, j) > maxA Onda

maxA = A(i, j)

Ako je A (i, j)< minA Then

minA = A(i, j)

Sheets("Sheet10"). Ćelije (i + 4 + 2, j) = maxA

Sheets("Sheet10"). Ćelije (i + 4 + 3, j) = minA

Uz pomoć Excel alata, možete odabrati određene podatke iz raspona nasumičnim redoslijedom, prema jednom ili više uvjeta. Za rješavanje takvih problema u pravilu se koriste formule niza ili makroi. Pogledajmo primjere.

Kako napraviti selekciju u Excelu po uslovu

Kada koristite formule niza, odabrani podaci se prikazuju u posebnoj tabeli. Koja je prednost ovu metodu u poređenju sa konvencionalnim filterom.

Izvor tabela:

Prvo, naučimo kako napraviti selekciju prema jednom numeričkom kriteriju. Zadatak je odabrati robu s cijenom iznad 200 rubalja iz tabele. Jedno rješenje je primjena filtriranja. Kao rezultat, samo oni proizvodi koji zadovoljavaju upit će ostati u izvornoj tabeli.

Drugo rješenje je korištenje formule niza. Redovi koji odgovaraju upitu bit će smješteni u poseban izvještaj tabele.

Prvo kreiramo praznu tabelu pored originalne: dupliramo naslove, broj redova i kolona. Nova tabela zauzima raspon E1:G10 Sada odaberite E2:E10 (kolona "Datum") i unesite sljedeću formulu: ( }.

Da biste dobili formulu niza, pritisnite kombinaciju tipki Ctrl + Shift + Enter. U sljedećoj koloni - "Proizvod" - unosimo sličnu formulu niza: ( ). Promijenjen je samo prvi argument funkcije INDEX.

U kolonu "Cijena" unesite istu formulu niza, mijenjajući prvi argument funkcije INDEX.

Kao rezultat toga, dobijamo izvještaj o robi s cijenom većom od 200 rubalja.

Takav odabir je dinamičan: kada se upit promijeni ili se novi proizvodi pojave u izvornoj tabeli, izvještaj će se automatski promijeniti.

Zadatak broj 2 je odabrati iz izvorne tabele robu koja je puštena u prodaju 20.09.2015. Odnosno, kriterijum izbora je datum. Radi praktičnosti, željeni datum ćemo unijeti u posebnu ćeliju, I2.

Za rješavanje problema koristi se slična formula niza. Samo umjesto kriterija).

Slične formule se unose u druge kolone (pogledajte gore za princip).

Sada koristimo tekstualni kriterijum. Umjesto datuma u ćeliju I2 unesite tekst "Proizvod 1". Promenimo malo formulu niza: ( }.

Tako velika funkcija odabira u Excelu.

Odabir prema 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. Kombinujemo uslove sa znakom "*". Formula niza izgleda ovako: ( }.!}

Ovo je za prvu kolonu tabele izveštaja. Za drugi i treći, mijenjamo prvi argument funkcije INDEX. rezultat:

Za odabir po nekoliko datuma ili numeričkih kriterija koristimo slične formule niza.

Slučajni odabir u Excel-u

Kada korisnik radi s velikom količinom podataka, može biti potrebno nasumično uzorkovanje za naknadnu analizu. Svakom redu možete dodijeliti nasumični broj, a zatim sortirati odabir.

Početni skup podataka:

Prvo umetnite dvije prazne kolone s lijeve strane. U ćeliju A2 unesite formulu RAND (). Pomnožimo to sa cijelom kolonom:

Sada kopiramo kolonu sa slučajnim brojevima i zalijepimo je u kolonu B. Ovo je neophodno kako se ovi brojevi ne bi mijenjali kada se novi podaci unose u dokument.

Da biste umetnuli vrijednosti, a ne formulu, desnom tipkom miša kliknite stupac B i odaberite alat " Specijalni umetak". U prozoru koji se otvori stavite kvačicu ispred stavke "Vrijednosti":

Sada možete sortirati podatke u koloni B u rastućem ili opadajućem redoslijedu. Redoslijed u kojem su originalne vrijednosti predstavljene također će se promijeniti. Odaberemo bilo koji broj redova odozgo ili odozdo - dobivamo nasumični uzorak.

razno (39)
Greške i greške u Excelu (3)

Kako doći do liste jedinstvenih (neponovljivih) vrijednosti?

Zamislite veliki spisak raznih imena, punih imena, brojeva osoblja itd. I potrebno je ostaviti spisak svih istih imena sa ove liste, ali tako da se ne ponavljaju - tj. uklonite sve duple unose sa ove liste. Kako se inače zove: kreirajte listu jedinstvenih elemenata, listu neponavljajućih, bez duplikata. Postoji nekoliko načina da se to učini: korištenjem ugrađenih Excel alata, ugrađenih formula i konačno korištenjem koda. Visual basic za aplikaciju (VBA) i pivot tabele. U ovom članku ćemo pogledati svaku opciju.

Korištenje ugrađenih mogućnosti programa Excel 2007 i novijih
U Excelu 2007 i 2010 to je lako učiniti - postoji posebna komanda koja se zove -. Nalazi se na kartici Podaci pododjeljak Rad sa podacima (Alati za podatke)

Kako koristiti ovu naredbu. Označite kolonu (ili nekoliko) sa podacima u kojima želite da uklonite duple unose. Idi na karticu Podaci -Ukloni duplikate.

Ako odaberete jednu kolonu, ali pored nje postoji više kolona s podacima (ili barem jedna kolona), Excel će vam ponuditi izbor: proširite raspon odabira ovom kolonom ili ostavite odabir kakav jeste i izbrišite samo podatke u odabranom opsegu. Važno je zapamtiti da ako ne proširite raspon, podaci će se promijeniti samo u jednoj koloni, a podaci u susjednoj koloni će ostati nepromijenjeni.

Pojavit će se prozor s opcijama za uklanjanje duplikata.

Označite polja pored kolona u kojima želite da uklonite duplikate i kliknite na OK. Ako odabrani raspon sadrži i zaglavlja podataka, onda je bolje postaviti zastavicu Moji podaci sadrže zaglavlja kako ne bi slučajno obrisali podatke u tabeli (ako se odjednom potpuno poklapaju sa vrijednošću u zaglavlju).

Metoda 1: Napredni filter
U slučaju Excel 2003, sve je komplikovanije. Ne postoji takav alat kao Ukloni duplikate. Ali postoji tako divan alat kao Napredni filter. Godine 2003. ovaj alat se može naći u Podaci -Filter -Napredni filter. Ljepota ove metode je u tome što uz njenu pomoć ne možete pokvariti originalne podatke, već kreirati listu u drugom rasponu. U Excelu 2007-2010, takođe postoji, ali malo skriveno. Nalazi se na tab Podaci, grupa Sortiraj i filtriraj - Napredno
Kako ga koristiti: pokrenite navedeni alat - pojavljuje se dijaloški okvir:

tretman: Izaberi Kopirajte rezultat na drugu lokaciju (Kopiraj na drugu lokaciju).
Raspon izvora (opseg liste): Odabir raspona s podacima (u našem slučaju to je A1:A51).
Raspon kriterija: u ovom slučaju ostavite prazno.
Stavite rezultat u raspon (Kopiraj u): navedite prvu ćeliju za izlaz podataka - bilo koja prazna (na slici - E2).
Stavite kvačicu Samo jedinstveni zapisi (samo jedinstveni zapisi).
Kliknite uredu.

Bilješka: ako želite staviti rezultat na drugi list, onda samo navođenje drugog lista neće raditi. Moći ćete odrediti ćeliju na drugom listu, ali... Jao i ah... Excel će prikazati poruku da ne može kopirati podatke na druge listove. Ali to se može zaobići, i to vrlo jednostavno. Samo treba da trčiš Napredni filter sa lista na koji želimo da postavimo rezultat. A kao početne podatke biramo podatke s bilo kojeg lista - to je dozvoljeno.

Također ne možete prenijeti rezultat u druge ćelije, već filtrirati podatke na mjestu. Podaci neće ni na koji način patiti od toga - to će biti normalno filtriranje podataka.

Da biste to učinili, jednostavno odaberite u odjeljku Obrada Filtrirajte listu, na mjestu.

Metoda 2: Formule
Ovu metodu je teže razumjeti neiskusnim korisnicima, ali stvara listu jedinstvenih vrijednosti bez promjene izvornih podataka. Pa, to je dinamičnije: ako promijenite podatke u izvornoj tablici, rezultat će se također promijeniti. Ponekad je ovo korisno. Pokušaću na prste da objasnim šta je šta: recimo da se vaša lista sa podacima nalazi u koloni A (A1: A51, gde je A1 naslov). Prikazat ćemo listu u koloni C, počevši od ćelije C2. Formula u C2 bi bila:
(=INDEX($A$2:$A$51 ;MALO(IF(COUNTIF($C$1:C1; $A$2:$A$51)=0;RED($A$1:$A$50));1)) )
(=INDEX($A$2:$A$51 ;MALO(IF(COUNTIF($C$1:C1; $A$2:$A$51)=0;RED($A$1:$A$50));1)) )
Detaljna analiza rada ove formule data je u članku:
Imajte na umu da je ova formula formula niza. To se može naznačiti vitičastim zagradama u kojima je ova formula zatvorena. I sljedeća formula se unosi u ćeliju prečicom na tastaturi - ctrl+Shift+Enter. Nakon što smo unijeli ovu formulu u C2, moramo je kopirati i zalijepiti u nekoliko redova kako bi svi jedinstveni elementi bili točno prikazani. Kada se formula u donjim ćelijama vrati #BROJ!- to znači da su svi elementi prikazani i da nema smisla protezati formulu ispod. Da biste izbjegli grešku i učinili formulu univerzalnijom (bez rastezanja svaki put dok se ne pojavi greška), možete koristiti jednostavnu provjeru:
za Excel 2007 i novije verzije:
(=IFGREŠKA(INDEX($A$2:$A$51 ,MALO(IF(BROJ($C$1:C1, $A$2:$A$51)=0,RED($A$1:$A$50)),1 ));""))
(=IFERROR(INDEX($A$2:$A$51 ;MALO(IF(BROJ($C$1:C1; $A$2:$A$51)=0;ROW($A$1:$A$50));1 ));""))
za Excel 2003:
(=IF(ISN(SMALL(IF(COUNTIF($C$1:C1, $A$2:$A$51)=0,RED($A$1:$A$50)),1)),"";INDEX( $A$2:$A$51 ;SMALL(IF(BROJ($C$1:C1; $A$2:$A$51)=0;RED($A$1:$A$50));1))))
(=IF(ISERR(SMALL(IF(COUNTIF($C$1:C1; $A$2:$A$51)=0;ROW($A$1:$A$50));1));"";INDEX( $A$2:$A$51 ;SMALL(IF(COUNTIF($C$1:C1; $A$2:$A$51)=0;ROW($A$1:$A$50));1))))
Onda umjesto greške #BROJ! (#BROJ!) imaćete prazne ćelije (nije potpuno prazno, naravno - sa formulama :-)).

Nešto više o razlikama i nijansama formula IFERROR i IF(EOSH) možete pronaći u ovom članku: Kako prikazati 0 umjesto greške u ćeliji s formulom

Metoda 3: VBA kod
Ovaj pristup će zahtijevati dozvolu makroa 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? video tutorial u prilogu članka
Šta je modul? Šta su moduli? potrebno je razumjeti gdje umetnuti kodove ispod

Treba staviti oba sljedeća koda standardni modul. Makroi moraju biti omogućeni.

Početne podatke ostavićemo istim redosledom - lista sa podacima nalazi se u koloni "A" (A1:A51 gdje je A1 naslov). Samo ćemo mi prikazati listu ne u koloni C, već u koloni E, počevši od ćelije E2:

Sub Extract_Unique() Dim vItem, avArr, li As Long ReDim avArr(1 do redova.broj, 1 do 1) sa novom kolekcijom u slučaju greške Nastavi sljedeći za svaku vItem u rasponu("A2", ćelije (broj redova, 1) .End(xlUp)).Value "Cells(Rows.Count, 1).End(xlUp) - definira posljednju popunjenu ćeliju u koloni A. Dodajte vItem, CStr(vItem) Ako je Err = 0 Onda li = li + 1: avArr(li, 1) = vItem Else: Err.Clear End If Next End With If li Onda .Resize(li).Value = avArr End Sub

Koristeći ovaj kod, možete izdvojiti jedinstvene ne samo iz jedne kolone, već i iz bilo kojeg raspona stupaca i redova. Ako umjesto linije
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 vrijednost izlazne ćelije - umjesto stavi onaj u kome nema podataka.
Također možete odrediti određeni raspon:

Raspon("C2", Ćelije(Redovi.Broj, 3).Kraj(xlUp)).Vrijednost

Generički kod za odabir jedinstvenih vrijednosti
Donji kod se može primijeniti na bilo koji raspon. Dovoljno je da ga pokrenete, navedete raspon sa vrijednostima za odabir samo onih koji se ne ponavljaju (dozvoljeno je više od jedne kolone) i ćeliju za prikaz rezultata. Navedene ćelije će biti skenirane, od njih će biti odabrane samo jedinstvene vrijednosti (prazne ćelije se preskaču) i rezultirajuća lista će se pisati 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 Nastavi dalje "zatražite adresu ćelija za odabir jedinstvenih vrijednosti Postavite rVals = Application.InputBox( "Odredite raspon ćelija za uzorkovanje jedinstvenih vrijednosti", "Podaci upita" , "A2:A51" , Tip :=8) Ako rVals nije ništa onda "ako se pritisne dugme Odustani Exit Sub End If "ako je navedena samo jedna ćelija, nema smisla birati Ako je rVals.Count = 1 Tada MsgBox "Potrebno je više od jedne ćelije za odabir jedinstvenih vrijednosti", vbInformation, "www.site" Exit Sub End If "odseći prazne redove i kolone izvan radnog opsega Postavite rVals = Intersect(rVals, rVals.Parent.UsedRange) "ako su navedene samo prazne ćelije izvan radnog opsega Ako rVals nije ništa onda MsgBox "Nedovoljno podataka za odabir vrijednosti", vbInformation, "www.site" Exit Sub End Ako je avVals = rVals.Value "zatražite ćeliju za prikaz rezultata Postavite rResultCell = Application.InputBox( "Odredite ćeliju za umetanje odabranih jedinstvenih vrijednosti", "Podaci upita" , "E2" , Tip :=8) Ako je rResultCell ništa onda "ako se pritisne dugme Odustani Exit Sub End If "definirajte maksimalnu moguću dimenziju niza za rezultat ReDim avArr(1 do redova.broj, 1 do 1) "koristeći objekt Collection "odaberite samo jedinstvene zapise, "jer kolekcije ne mogu sadržavati duple vrijednosti Sa novom kolekcijom u slučaju greške Nastavi sljedeći za svaki x u avVals Ako Len(CStr(x)) Onda "preskoči prazne ćelije.Dodaj x, CStr(x) "ako je element koji se dodaje već u kolekciji, doći će do greške "ako nema greške - takva vrijednost još nije unesena, "dodajte u rezultirajući niz Ako je Err = 0 Tada je li = li + 1 avArr(li, 1) = x Drugo "Obavezno obrišite objekat Errors Err.Clear End If End If Next End With "upišite rezultat na list, počevši od navedene ćelije Ako je li Onda rResultCell.Cells(1, 1).Resize(li).Value = avArr End Sub

Sub Extract_Unique() Dim x, avArr, li As Long Dim avVals Dim rVals As Range, rResultCell As Range On Error Nastavi dalje , "Podaci upita", "A2:A51", Tip:=8) Ako rVals nije ništa onda "ako pritisnuto je dugme Cancel Exit Sub End Ako "ako je navedena samo jedna ćelija, nema smisla odabrati If rVals.Count = 1 Then MsgBox " Da biste odabrali jedinstvene vrijednosti, morate navesti više od jedne ćelije", vbInformation, " www.site" Exit Sub End Ako "odrežite prazne redove i kolone izvan radnog opsega Postavite rVals = Intersect(rVals, rVals.Parent.UsedRange) "ako su samo prazne ćelije navedene izvan opsega Ako rVals nije ništa, onda MsgBox "Nedovoljno podaci za odabir vrijednosti", vbInformation, "www..Value "zahtjev za ć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!}

Metoda 4: Pivot tabele
Pomalo nestandardan način izdvajanja jedinstvenih vrijednosti.

Odaberite jednu ili više kolona u tabeli, idite na karticu Insert-grupa Table -Zaokretna tabela
U dijaloškom okviru Kreiranje zaokretne tabele (Kreirajte zaokretnu tabelu) provjerite ispravnost odabira raspona podataka (ili instalirajte novi izvor podataka)
navedite lokaciju zaokretne tablice:
- Na novi list (Novi radni list)
- Na postojeći radni list
potvrdite kreiranje pritiskom na dugme uredu

Jer Zaokretne tabele, kada obrađuju podatke koji su smešteni u redove ili kolone, iz njih biramo samo jedinstvene vrednosti za dalju analizu, tada se od nas ne traži apsolutno ništa osim da kreiramo stožernu tabelu i postavimo podatke iz željenu kolonu u području redova ili kolona.
Koristeći primjer datoteke priložene uz članak, ja:

Koja je neugodnost rada sa zaokretnim tablicama u ovom slučaju: ako se izvorni podaci promijene, pivot tablica će se morati ažurirati ručno: Odaberite bilo koju ćeliju zaokretne tablice - Desno dugme miševi - Osvježiti ili tab Podaci -Osvježi sve -Osvježiti. A ako se početni podaci nadopunjuju dinamički i još gore - bit će potrebno ponovno specificirati raspon početnih podataka. I još jedan minus - podaci unutar pivot tabele se ne mogu menjati. Stoga, ako će u budućnosti biti potrebno raditi s rezultirajućom listom, nakon kreiranja željene liste pomoću sažetka, mora se kopirati i zalijepiti na željeni list.

Kako biste bolje razumjeli sve radnje i naučili kako rukovati zaokretnim tablicama, toplo preporučujem da pročitate članak Pregled zaokretnih tablica - uz njega je priložen video vodič u kojem jasno pokazujem jednostavnost i praktičnost rada sa glavne karakteristike pivot tabela.

U priloženom primjeru, pored opisanih tehnika, zabilježena je i nešto složenija varijacija izdvajanja jedinstvenih elemenata formulom i kodom, i to: izdvajanje jedinstvenih elemenata po kriterijumima. O čemu se radi: ako se u jednoj koloni prezimena, au drugoj (B) nalaze neki podaci (u datoteci su to mjeseci) i potrebno je izdvojiti jedinstvene vrijednosti stupca B samo za odabrane prezime. Primjeri takvih jedinstvenih ekstrakcija nalaze se na listu Izdvoj po kriterijumima.

Preuzmi primjer:

(108,0 KiB, 14.152 preuzimanja)

Da li je članak pomogao? Podijelite link sa svojim prijateljima! Video lekcije

Ako morate da radite sa veliki stolovi sigurno ćete u njima pronaći duple sume razbacane po cijeloj koloni. U isto vrijeme, možda ćete morati odabrati podatke iz tablice s prvom najmanjom numeričkom vrijednošću koja ima svoje duplikate. Potreban nam je automatski odabir podataka prema uslovima. U Excelu možete uspješno koristiti formulu niza za ovu svrhu.

Kako napraviti selekciju u Excelu po uslovu

Da bi se odredila odgovarajuća vrijednost za prvi najmanji broj, potreban je izbor iz tabele prema uslovu. Recimo da želimo saznati prvi najjeftiniji proizvod na tržištu iz datog cjenika:

Automatsko uzorkovanje je implementirano formulom koja će imati sljedeću strukturu:

INDEX(opseg_podataka za_izbor, MIN(IF(opseg=MIN(opseg),RED(opseg)-ROW(zaglavlje_kolone),"")))

Na mjestu "data_range_for_selection" treba navesti raspon vrijednosti A6:A18 za odabir iz tabele (na primjer, tekst), iz koje će funkcija INDEX odabrati jednu rezultirajuću vrijednost. Argument "raspon" označava područje ćelija sa numeričkim vrijednostima iz kojih treba odabrati prvi najmanji broj. U argumentu "column_header" za drugu funkciju ROW, morate navesti referencu ćelije sa zaglavljem stupca koje sadrži raspon numeričkih vrijednosti.

Naravno, ovu formulu treba izvesti u nizu. Stoga, da biste potvrdili njegov unos, trebate pritisnuti ne samo tipku Enter, već cijelu kombinaciju tipki CTRL + SHIFT + Enter. Ako je sve urađeno ispravno, vitičaste zagrade će se pojaviti u traci formule.

Obratite pažnju na sliku ispod, gdje je ova formula u nizu unesena u ćeliju B3:

Odabir odgovarajuće vrijednosti s prvim najmanjim brojem:

Pomoću ove formule mogli smo odabrati minimalnu vrijednost u odnosu na brojeve. Zatim ćemo analizirati princip formule i analizirati cijeli redoslijed svih proračuna korak po korak.

Kako funkcionira odabir prema uvjetima

Funkcija INDEX ovdje igra ključnu ulogu. Njegov nominalni zadatak je da odabere iz izvorne tablice (navedene u prvom argumentu - A6:A18) vrijednosti koje odgovaraju određenim brojevima. INDEX radi prema kriterijima definiranim u drugom (broj reda unutar tabele) i trećem (broj kolone u tabeli) argumentima. Budući da naša izvorna tablica A6:A18 ima samo 1 stupac, ne navodimo treći argument u funkciji INDEX.

Nekoliko funkcija izračuna se koristi za izračunavanje broja reda tablice nasuprot najmanjeg broja u susjednom rasponu B6:B18 i koristi ga kao vrijednost za drugi argument.

Funkcija IF vam omogućava da odaberete vrijednost sa liste prema uslovu. Njegov prvi argument određuje gdje se svaka ćelija u rasponu B6:B18 provjerava za najmanju numeričku vrijednost: IFB6:B18=MINB6:B18. Na taj način se u programskoj memoriji kreira niz od logičkih vrijednosti TRUE i FALSE. U našem slučaju, 3 elementa niza će sadržavati TRUE, pošto minimalna vrijednost od 8 sadrži još 2 duplikata u koloni B6:B18.

Sljedeći korak je odrediti koji redovi u rasponu sadrže svaku minimalnu vrijednost. Ovo nam je potrebno zbog definicije prve najmanje vrijednosti. Implementirano dati zadatak koristeći funkciju ROW, popunjava elemente niza u programskoj memoriji brojevima reda na listu. Ali prvo, od svih ovih brojeva, broj se oduzima od prvog reda tabele - B5, odnosno broja 5. To je učinjeno jer funkcija INDEX radi sa brojevima unutar tabele, a ne sa brojevima Excel radnog lista. Istovremeno, funkcija ROW može vratiti samo brojeve redova na listu. Da ne bi došlo do pomaka, potrebno je porediti redosled brojeva listova i redova tabele oduzimanjem razlike. Na primjer, ako se tabela nalazi u 5. redu lista, tada će svaki red tabele biti 5 manji od odgovarajućeg reda na listu.

Nakon što su odabrane sve minimalne vrijednosti i upareni svi brojevi redova u tabeli, funkcija MIN će odabrati najmanji broj reda. Isti red će sadržavati prvi najmanji broj koji se pojavljuje u koloni B6:B18. Na osnovu ovog broja reda, funkcija INDEX će odabrati odgovarajuću vrijednost iz tablice A6:A18. Kao rezultat, formula vraća ovu vrijednost u ćeliju B3 kao rezultat izračuna.

Kako odabrati vrijednost s najvećim brojem u Excelu

Pošto ste razumjeli princip formule, sada je možete lako modificirati i prilagoditi drugim uvjetima. Na primjer, formula se može modificirati tako da se izabere prva maksimalna vrijednost u Excelu:

Ako trebate promijeniti uvjete formule tako da u Excelu možete odabrati prvi maksimum, ali manji od 70:

Kako odabrati prvu minimalnu vrijednost osim nule u Excelu:

Kao što možete lako vidjeti, ove formule se razlikuju samo po funkcijama MIN i MAX i njihovim argumentima.

Sada vas ništa ne ograničava. Jednom kada shvatite principe rada formula u nizu, možete ih lako modificirati za mnoge uvjete i brzo riješiti mnoge računske probleme.

Kada radite sa Excel tabelama, često ih morate odabrati prema određenom kriterijumu ili nekoliko uslova. Možete to učiniti u programu Različiti putevi koristeći niz alata. Hajde da saznamo kako da uzorkujemo u Excelu koristeći razne opcije.

Uzorkovanje podataka se sastoji u postupku odabira iz opšteg niza onih rezultata koji zadovoljavaju date uslove, sa njihovim naknadnim prikazom na listu u posebnoj listi ili u originalnom opsegu.

Metoda 1: Primijenite napredni automatski filter

Većina na jednostavan način da biste izvršili odabir je primjena naprednog autofiltra. Hajde da vidimo kako to uraditi na konkretan primjer.

Odabiremo područje na listu, među podacima koje želite odabrati. U kartici "Dom" kliknite na dugme "Sortiraj i filtriraj". Nalazi se u bloku postavki. "Uređivanje". Na listi koja se otvori nakon ovoga kliknite na dugme "filter".

Postoji prilika da se to uradi drugačije. Da biste to učinili, nakon odabira područja na listu, prijeđite na karticu "Podaci". Klikom na dugme "filter", koji se nalazi na vrpci u grupi "Sortiraj i filtriraj".

Nakon ove akcije, ikone za početak filtriranja pojavljuju se u zaglavlju tabele u obliku malih trokuta okrenutih naopako na desnom rubu ćelija. Kliknemo na ovu ikonu u zaglavlju kolone po kojoj želimo da izvršimo selekciju. U meniju koji se otvori idite na stavku "Filteri teksta". Zatim odaberite poziciju "Prilagođeni filter...".

Prozor prilagođenog filtriranja je aktiviran. U njemu možete postaviti ograničenje po kojem će se izvršiti odabir. Na padajućoj listi za kolonu koja sadrži ćelije formata brojeva koje koristimo za primjer, možete odabrati jedan od pet tipova uslova:
- jednaki;
- nije jednako;
- više;
- više ili jednako;
- manje.
Postavimo, na primjer, uvjet tako da odaberemo samo vrijednosti za koje iznos prihoda prelazi 10.000 rubalja. Postavite prekidač u položaj "više". Unesite vrijednost u desno polje "10000". Da izvršite radnju, kliknite na dugme. uredu.

Kao što vidite, nakon filtriranja, ostale su samo linije u kojima iznos prihoda prelazi 10.000 rubalja.

Ali u istoj koloni možemo dodati drugi uslov. Da bismo to učinili, ponovo se vraćamo na prozor prilagođenog filtriranja. Kao što vidite, u njegovom donjem dijelu nalazi se još jedan prekidač stanja i polje za unos koji mu odgovara. Postavimo sada gornju granicu odabira na 15.000 rubalja. Da biste to učinili, postavite prekidač u položaj "manje", a u polje sa desne strane unesite vrijednost "15000".
Osim toga, tu je i prekidač stanja. Ima dvije pozicije "I" I "ILI". Podrazumevano je postavljen na prvu poziciju. To znači da će samo redovi koji zadovoljavaju oba ograničenja ostati u uzorku. Ako je postavljen na poziciju "ILI", tada će postojati vrijednosti koje odgovaraju jednom od ova dva uslova. U našem slučaju, morate postaviti prekidač u položaj "I", odnosno otići ovo podešavanje default. Nakon što unesete sve vrijednosti, kliknite na dugme uredu.

Sada u tabeli postoje samo redovi u kojima iznos prihoda nije manji od 10.000 rubalja, ali ne prelazi 15.000 rubalja.

Slično, možete postaviti filtere u drugim kolonama. Istovremeno, moguće je sačuvati i filtriranje prema prethodnim uslovima koji su navedeni u kolonama. Dakle, da vidimo kako se odabir vrši pomoću filtera za ćelije u formatu datuma. Kliknite na ikonu filtera u odgovarajućoj koloni. Uzastopno kliknite na stavke na listi "Filtriraj po datumu" I "Prilagođeni filter".

Ponovo se pokreće prozor prilagođenog automatskog filtera. Odabir rezultata u tabeli izvršimo od 4. do 6. maja 2016. godine. U prekidaču za izbor uslova, kao što vidimo, postoji čak više opcija nego za format broja. Odaberite poziciju "Nakon ili jednako". Postavite vrijednost u polje na desnoj strani "04.05.2016". U donjem bloku postavite prekidač u položaj "prije ili jednako". Unesite vrijednost u desno polje "06.05.2016". Ostavite prekidač za kompatibilnost uslova u zadanom položaju - "I". Za primjenu filtriranja u akciji, kliknite na dugme uredu.

Kao što vidite, naša lista je dodatno smanjena. Sada su u njemu ostale samo linije u kojima iznos prihoda varira od 10.000 do 15.000 rubalja za period od 04.05. do 06.05.2016.

Možemo resetirati filtriranje na jednoj od kolona. Uradimo to za vrijednosti prihoda. Kliknite na ikonu autofiltra u odgovarajućoj koloni. U padajućoj listi kliknite na stavku "Ukloni filter".

Kao što vidite, nakon ovih radnji, odabir po visini prihoda će biti onemogućen, a ostaje samo odabir po datumima (od 04.05.2016. do 06.05.2016.).

Ova tabela ima još jednu kolonu - "ime". Sadrži podatke u tekstualni format. Pogledajmo kako formirati selekciju filtriranjem ovih vrijednosti.
Kliknite na ikonu filtera u nazivu kolone. Uzastopno prođite kroz imena liste "Filteri teksta" I "Prilagođeni filter...".

Ponovo se otvara prozor prilagođenog automatskog filtera. Hajde da pogledamo imena "krompir" I "meso". U prvom bloku postavite prekidač stanja u položaj "jednako". U polje desno od njega unesite riječ "krompir". Prekidač donjeg bloka je također postavljen u položaj "jednako". U polju naspram njega unosimo - "meso". A onda radimo nešto što nismo radili ranije: postavljamo prekidač kompatibilnosti uslova na položaj "ILI". Sada će na ekranu biti prikazana linija koja sadrži bilo koji od navedenih uslova. Klikom na dugme uredu.

Kao što vidite, u novom uzorku postoje ograničenja po datumu (od 04.05.2016. do 05.06.2016.) i po nazivu (krompir i meso). Nema ograničenja u visini prihoda.

Filter možete potpuno ukloniti na isti način na koji ste ga ugradili. Nije bitno koja je metoda korištena. Da biste resetirali filtriranje, nalazite se na kartici "Podaci" kliknite na dugme "filter", koji se nalazi u grupi "Sortiraj i filtriraj".

Druga opcija uključuje odlazak na karticu "Dom". Tamo kliknemo na dugme na traci "Sortiraj i filtriraj" u bloku "Uređivanje". U aktiviranoj listi kliknite na dugme "filter".

Kada koristite bilo koji od gore navedenih metoda, filtriranje će biti uklonjeno i rezultati odabira će biti obrisani. Odnosno, tabela će prikazati cijeli niz podataka koje ima.

Metoda 2: Primijenite formulu niza

Također možete napraviti izbor koristeći kompleksna formula niz. Za razliku od prethodne verzije, ovu metodu obezbeđuje izlaz rezultata u zasebnoj tabeli.

Na istom listu kreiramo praznu tabelu sa istim imenima kolona u zaglavlju kao u izvoru.

Odaberite sve prazne ćelije u prvoj koloni nove tabele. Postavite kursor u traku formule. Ovdje će biti upisana formula koja bira prema navedenim kriterijima. Odabrat ćemo linije u kojima iznos prihoda prelazi 15.000 rubalja. U našem konkretnom primjeru, unesena formula će izgledati ovako:
INDEX(A2:A29,NISKO(IF(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

Naravno, u svakom slučaju, adrese ćelija i rasponi će biti različiti. U ovom primjeru možete usporediti formulu s koordinatama na ilustraciji i prilagoditi je svojim potrebama.

Pošto je ovo formula niza, da biste je primenili u akciji, ne morate da kliknete na dugme Enter, i prečicu na tastaturi Ctrl+Shift+Enter. Hajde da to uradimo.

Odabirom druge kolone s datumima i postavljanjem kursora u traku formule, unesite sljedeći izraz:
INDEX(B2:B29,MALO(IF(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

Pritisnite prečicu na tastaturi Ctrl+Shift+Enter.

Slično, u kolonu sa prihodima unesite sljedeću formulu:
INDEX(C2:C29,MALO(IF(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

Ponovo otkucajte prečicu na tastaturi Ctrl+Shift+Enter.

U sva tri slučaja mijenja se samo prva vrijednost koordinata, ali su inače formule potpuno identične.

Kao što vidite, tabela je ispunjena podacima, ali njen izgled nije sasvim atraktivan, štoviše, vrijednosti datuma su pogrešno popunjene. Ove nedostatke je potrebno ispraviti. Neispravnost datuma je zbog činjenice da je format ćelija odgovarajuće kolone uobičajen, te moramo podesiti format datuma. Odaberite cijeli stupac, uključujući ćelije s greškama, i kliknite desnim tasterom miša na odabir. Na listi koja se pojavi idite na stavku "Format ćelije...".

U prozoru za oblikovanje koji se otvori otvorite karticu "Broj". U bloku "Formati brojeva" označite vrijednost "Datum". U desnom dijelu prozora možete odabrati željeni tip prikaza datuma. Nakon što su postavke postavljene, kliknite na dugme uredu.

Sada je datum ispravno prikazan. Ali, kao što vidite, cijeli donji dio tabele je ispunjen ćelijama koje sadrže pogrešnu vrijednost "#BROJ!". U stvari, to su ćelije za koje nije bilo dovoljno podataka iz uzorka. Bilo bi atraktivnije kada bi bili izloženi potpuno prazni. U tu svrhu koristićemo uslovno formatiranje. Odaberite sve ćelije tabele osim zaglavlja. Biti na kartici "Dom" kliknite na dugme "Uslovno formatiranje", koji se nalazi u alatnoj kutiji "stilovi". Na listi koja se pojavi odaberite stavku "Kreiraj pravilo...".

U prozoru koji se otvori odaberite vrstu pravila "Formatirajte samo ćelije koje sadrže". U prvom polju ispod natpisa "Formatirajte samo ćelije koje ispunjavaju sljedeći uvjet" izaberite poziciju "greške". Zatim kliknite na dugme "Formatiraj...".

U prozoru za oblikovanje koji se otvori idite na karticu "Font" i u odgovarajućem polju odaberite bijelu boju. Nakon ovih koraka kliknite na dugme uredu.

Kliknite na dugme sa potpuno istim imenom nakon što se vratite u prozor za kreiranje uslova.

Sada imamo gotov uzorak za navedeno ograničenje u zasebnoj, pravilno formatiranoj tabeli.

Metoda 3: Odabir više uslova pomoću formule

Baš kao i korištenjem filtera, pomoću formule možete birati prema nekoliko uvjeta. Na primjer, uzmimo istu izvornu tabelu, kao i praznu tabelu u kojoj će biti prikazani rezultati, sa već urađenim numeričkim i uslovnim formatiranjem. Postavimo donju granicu odabira po prihodu na 15.000 rubalja kao prvo ograničenje, a gornju granicu na 20.000 rubalja kao drugi uslov.

Metoda 4: nasumično uzorkovanje

U Excelu pomoću posebne formule RAND može se koristiti i slučajni odabir. Neophodan je u nekim slučajevima kada se radi sa velikom količinom podataka, kada treba da prikažete širu sliku bez sveobuhvatne analize svih podataka u nizu.

Kao što vidite, odabir u Excel tablici može se izvršiti i korištenjem autofiltera i pomoću posebnih formula. U prvom slučaju, rezultat će biti prikazan u originalnoj tabeli, au drugom - u zasebnom području. Moguće je izvršiti selekciju, kako po jednom uslovu, tako i po nekoliko. Osim toga, pomoću funkcije možete izvršiti nasumično uzorkovanje RAND.