Prenos podatkov v programu Microsoft Excel

Pri delu s preglednicami Excel je pogosto potrebno izbirati glede na določen kriterij ali več pogojev. V programu lahko to storite na različne načine z uporabo številnih orodij. Spoznajmo, kako narediti izbor v Excelu z različnimi možnostmi.

Izvedba vzorca

Vzorčenje podatkov je sestavljeno iz postopka izbire rezultatov iz splošnega polja, ki izpolnjujejo navedene pogoje, nato pa jih izstavite na listu na ločenem seznamu ali v izvirnem območju.

1. način: uporabite napredni avtomatski filter

Najlažji način izbire je uporaba razširjenega samodejnega filtriranja. Poglejmo, kako to narediti na konkretnem primeru.

1. Izberite območje na listu, med katerim morate izbrati. Na zavihku »Domov« kliknite gumb »Razvrsti in filtriraj« . Postavljen je v nastavitveni blok "Uredi" . Na seznamu, ki se odpre po tem, kliknite gumb »Filter« .

   Obstaja možnost, da to storite na drugačen način. Če želite to narediti, potem ko izberete območje na listu, se premaknite na kartico »Podatki «. Kliknemo na gumb »Filter« , ki se nahaja na traku v skupini »Razvrsti in filtriraj« .

2. Po tem dejanju se v glavi tabele pojavijo ikone, da začnejo filtrirati v obliki majhnih obrnjenih trikotnikov na desnem robu celic. Kliknemo na to ikono v naslovu stolpca, za katerega želimo izbrati. V meniju, ki se odpre, odprite element »Filtri besedila« . Nato izberite element »Custom Filter ...« .
3. Vključeno je okno za filtriranje uporabnikov. V njem lahko določite omejitev, na podlagi katere bo izbira opravljena. V spustnem seznamu za stolpec, ki vsebuje celico numeričnega formata, ki ga uporabljamo za primer, lahko izberete eno od petih vrst pogojev:
   • enako;
   • ni enako;
   • več;
   • večja ali enaka;
   • manj.

   Postavimo pogoj kot primer, tako da bomo izbrali samo vrednosti, po katerih znesek prihodkov presega 10.000 rubljev. Stikalo nastavite na položaj »Več« . V desno polje vnesite vrednost "10000" . Če želite izvedeti dejanje, kliknite gumb »V redu« .

4. Kot vidite, po filtraciji je bilo samo vrstic, v katerih znesek prihodkov presega 10.000 rubljev.
5. Toda v istem stolpcu lahko dodamo drugi pogoj. Če želite to narediti, se ponovno vrnemo v okno za filtriranje uporabnikov. Kot vidite, v spodnjem delu je še eno stikalo stanja in njegovo ustrezno polje vnosa. Zdaj nastavimo zgornjo omejitveno omejitev na 15.000 rubljev. Če želite to narediti, stikalo postavite v položaj "Less" , v polju na desni pa vnesemo vrednost "15000" .

   Poleg tega obstaja tudi stikalo stanja. Ima dva položaja "I" in "ALI" . Privzeto je nastavljen na prvo mesto. To pomeni, da bodo v vzorcu obstajale samo črte, ki izpolnjujejo obe omejitvi. Če je nastavljen na položaj "ALI" , ostanejo vrednosti, ki so primerne za oba pogoja. V našem primeru morate stikalo nastaviti na položaj "AND" , to pomeni, da to nastavitev pustite privzeto. Ko so vnesene vse vrednosti, kliknite gumb "OK" .

6. Zdaj je tabela pustila le vrstice, v katerih znesek prihodkov ni manjši od 10.000 rubljev, vendar ne presega 15.000 rubljev.
7. Podobno lahko filtre konfigurirate tudi v drugih stolpcih. V tem primeru je mogoče tudi shraniti tudi filtriranje pod prejšnjimi pogoji, ki so bili določeni v stolpcih. Torej, poglejmo, kako je filter izdelan za celice v formatu datuma. V ustreznem stolpcu kliknite ikono filtra. Natančno kliknite na elemente v seznamu »Filter po datumu« in » Seznam po meri« .
8. Ponovno se zažene okno Custom AutoFilter. Izbor rezultatov bomo izvedli v tabeli od 4. do 6. maja 2016. V izbirnem stikalu stanja, kot vidite, obstaja še več možnosti kot za numerično obliko. Izberite položaj »Po ali enako«. V polju na desni nastavite vrednost na "04/05/2016" . V spodnjem bloku nastavite stikalo v položaj "Pred ali enako" . V desnem polju vnesemo vrednost »06.05.2016« . Stikalo za združljivost stanja je v privzetem položaju - "AND" . Če želite uporabiti filtriranje v akciji, kliknite gumb »V redu« .
9. Kot lahko vidite, se je naš seznam še bolj zmanjšal. V njej ostanejo samo vrstice, v katerih se znesek prihodkov giblje od 10.000 do 15.000 rubljev za obdobje od 04.05 do vključno 06.05.2016.
10. Ponastavimo lahko filtriranje v enem od stolpcev. Naredimo to za podatke o prihodkih. V ustreznem stolpcu kliknite ikono samodejnega filtriranja. Na spustnem seznamu kliknite element »Izbriši filter« .
11. Kot lahko vidite, bo po teh ukrepih vzorec za znesek prihodkov onemogočen in na voljo bo le izbor po datumih (od 04.05.2016 do 06.05.2016).
12. V tej tabeli je še en stolpec - »Ime« . Vsebuje podatke v besedilnem formatu. Poglejmo, kako ustvariti vzorec s filtriranjem na te vrednosti.

    Kliknite ikono filtra v imenu stolpca. Gremo skozi imena seznama "besedilni filtri" in "filter po meri ..." .

13. Ponovno se odpre okno Custom AutoFilter. Naredimo izbor na imena "Krompir" in "Meso" . V prvem bloku nastavite stikalo stanja v položaj "Equal" . V polju desno od nje vnesemo besedo "Krompir" . Stikalo spodnjega bloka je postavljeno tudi v položaj "Equal" . V polju nasproti nanj napišemo - "Meso" . In potem opravljamo tisto, česar nismo storili prej: stikalo za združljivost stanja nastavite na položaj "ALI" . Zdaj se prikaže vrstica, ki vsebuje kateri koli od navedenih pogojev. Kliknemo na gumb "OK" .
14. Kot vidite, v novem vzorcu so omejitve datuma (od 04.05.2016 do 06.05.2016) in po imenu (krompir in meso). Znesek prihodkov ni omejen.
15. Popolnoma odstranite filter lahko isti načini, ki so bili uporabljeni za namestitev. In ni pomembno, katera metoda je bila uporabljena. Če želite ponastaviti filtriranje, na kartici »Podatki« kliknite gumb »Filter« , ki se nahaja v skupini »Razvrsti in filtriraj« .

    Druga možnost je prehod na kartico Domov . Tam kliknemo na traku na "Razvrsti in filtriramo" gumb v "Uredi" blok. Na aktiviranem seznamu kliknite gumb »Filter« .

Če uporabljate eno od zgornjih dveh metod, bo filtriranje odstranjeno in rezultati izbire bodo izbrisani. To pomeni, da bo miza prikazala celoten niz podatkov, ki jih ima.

Lekcija: Funkcija AutoFilter v Excelu

2. način: uporabite matrično formulo

Izbirate lahko tudi z uporabo kompleksne matrike. Za razliko od prejšnje različice ta metoda zagotavlja rezultat rezultata v ločeni tabeli.

1. Na istem listu ustvarimo prazno tabelo z enakimi imeni stolpcev v glavi kot vir.
2. Izberite vse prazne celice prvega stolpca nove tabele. Nastavite kazalko v vrstici s formulo. Tukaj se vnese formula, ki vzame vzorec v skladu z določenimi merili. Izberemo linije, znesek prihodkov v katerem presega 15.000 rubljev. V našem posebnem primeru bo vhodna formula naslednja:

   =ИНДЕКС(A2:A29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

   Seveda bo naslov celic in območij v vsakem primeru enak. V tem primeru lahko formulo uskladite s koordinatami na ilustraciji in jo prilagodite svojim potrebam.

3. Ker gre za formulo matrike, da jo uporabite v akciji, morate pritisniti tipko Enter in kombinacijo tipk Ctrl + Shift + Enter . To počnemo.
4. Izberite drugi stolpec z datumi in postavite kurzor v vrstico s formulo, vnesite naslednji izraz:

   =ИНДЕКС(B2:B29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

   Pritisnite kombinacijo tipk Ctrl + Shift + Enter .

5. Podobno v stolpcu s prihodki vnesemo naslednjo formulo:

   =ИНДЕКС(C2:C29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

   Spet vnesite Ctrl + Shift + Enter .

   V vseh treh primerih se spremeni samo prva koordinatna vrednost, sicer so formule popolnoma identične.

6. Kot lahko vidite, je tabela napolnjena s podatki, vendar njen videz ni zelo privlačen, poleg tega pa so datumske vrednosti napačno napolnjene. Te pomanjkljivosti je treba odpraviti. Nepravilen datum je posledica dejstva, da je oblika celic ustreznega stolpca splošna, zato moramo nastaviti obliko datuma. Izberite celoten stolpec, vključno s celicami z napakami, in kliknite na izbiro z desnim gumbom miške. Na seznamu, ki se prikaže, odprite element »Oblika celice ...« .
7. V oknu odprtega formata odprite kartico »Številka «. V bloku "Numerični zapisi" izberite vrednost "Datum" . V desnem delu okna lahko izberete želeno vrsto prikaza datuma. Ko so nastavitve nastavljene, kliknite gumb "OK" .
8. Zdaj je datum pravilno prikazan. Toda, kot si lahko ogledate, je celoten spodnji del tabele napolnjen s celicami, ki vsebujejo napačno vrednost »# NUMBER!« . Dejansko so to celice, podatki iz vzorca, za katere ni bilo dovolj. Bilo bi bolj privlačno, če bi bili sploh prazni. Za te namene uporabimo pogojno oblikovanje. Izberite vse celice v tabeli, razen pokrovčka. Na zavihku »Domov« kliknite gumb »Pogojno oblikovanje« , ki se nahaja v orodni vrstici »Slogi« . Na seznamu, ki se prikaže, izberite element »Ustvari pravilo ...« .
9. V oknu, ki se odpre, izberite vrsto pravila »Oblikujte samo celice, ki vsebujejo« . V prvem polju pod besedilom "Oblikujte samo celice, za katere je izpolnjen naslednji pogoj", izberite element "Napake" . Nato kliknite gumb »Format ...« .
10. V oknu oblikovanja, ki se odpre, pojdite na zavihek "Pisava" in v ustreznem polju izberite belo barvo. Po teh ukrepih kliknite gumb »V redu« .
11. Na gumbu z enakim imenom kliknite, ko se vrnete v okno Create Condition.

Zdaj imamo pripravljen vzorec za navedeno omejitev v ločeni pravilno oblikovani tabeli.

Lekcija: Pogojno oblikovanje v Excelu

Metoda 3: Več pogojev z uporabo formule

Tako kot z uporabo filtra lahko uporabite tudi formulo za vzorec z več pogoji. Na primer, vzemite isto izvorno tabelo in prazno tabelo, kjer bodo rezultati prikazani, z že izvedenim številskim in pogojnim oblikovanjem. Ugotavljamo prvo omejitev nižje izbirne meje za prihodke 15.000 rubljev, drugi pogoj pa zgornjo mejo 20.000 rubljev.

1. V ločeni stolpec vnesemo robne pogoje za vzorec.
2. Kot v prejšnji metodi, posameznik izbere prazne stolpce nove tabele in v njih vnese ustrezne tri formule. V prvem stolpcu vnesemo naslednji izraz:

   =ИНДЕКС(A2:A29;НАИМЕНЬШИЙ(ЕСЛИ(($D$2=C2:C29);СТРОКА(C2:C29);"");СТРОКА(C2:C29)-СТРОКА($C$1))-СТРОКА($C$1))

   V naslednjih stolpcih vnesemo natančno enake formule, samo po spremembi koordinat takoj po imenu operaterja INDEX na stolpce, ki ustrezajo stolpcem, ki jih potrebujemo, analogno prejšnji metodi.

   Vsakič po vnosu ne pozabite vnesti Ctrl + Shift + Enter .

3. Prednost te metode nad prejšnjim je, da če želimo spremeniti vzorčne meje, ni treba spreminjati formule matrike, ki je sama po sebi precej problematična. V stolpcu pogojev na listu je dovolj, da spremenite številke omejitev na tiste, ki jih potrebuje uporabnik. Rezultati izbora se nato samodejno spremenijo.

Metoda 4: naključno vzorčenje

V Excelu s pomočjo posebne formule RAND lahko uporabite tudi naključno izbiranje. V nekaterih primerih ga je treba izdelati, ko delate z veliko količino podatkov, ko morate predstaviti splošno sliko brez kompleksne analize vseh podatkov matrike.

1. Na levi strani tabele poteka en stolpec. V celici naslednjega stolpca, ki je nasprotna prvi celici s podatki v tabeli, vnesemo formulo:

   =СЛЧИС()

   Ta funkcija prikazuje naključno številko. Če ga želite aktivirati, pritisnite gumb ENTER .

2. Če želite narediti cel stolpec naključnih števil, nastavite kazalec na spodnji desni kot celice, ki že vsebuje formulo. Pojavi se polnilni ročaj. Potegnite navzdol z levo miškino tipko, pritisnjeno vzporedno s tabelo s podatki do konca.
3. Zdaj imamo vrsto celic, napolnjenih z naključnimi številkami. Ampak, vsebuje formulo RAND . Prav tako moramo delati s čistimi vrednostmi. Če želite to narediti, kopirajte v prazen stolpec na desni. Izberemo paleto celic z naključnimi številkami. Ko se nahaja na zavihku »Domov «, kliknite na ikono »Kopiraj« na traku.
4. Izberite prazen stolpec in kliknite desni gumb miške in pokličite kontekstni meni. V skupini orodij "Vstavljanje parametrov" izberite element "Vrednosti" , ki je predstavljen kot piktogram s številkami.
5. Po tem v zavihku »Domov« kliknite znano ikono »Razvrsti in filtriraj« . V spustnem seznamu zaustavite izbor na elementu »Razvrščanje po meri« .
6. Okno za razvrščanje je aktivirano. Ne pozabite potrditi polja zraven »Moji podatki vsebujejo glave« , če obstaja glava , in ni nobenega kljukica. V polju »Razvrsti po« navedite ime stolpca, v katerem so shranjene kopirane vrednosti naključnih številk. V polju »Razvrsti« pustimo privzete nastavitve. V polju »Naročilo« lahko izberete možnost »Naraščajoče« ali »Padajoče« . Za naključno vzorčenje to ni pomembno. Po nastavitvah kliknite na gumb »V redu« .
7. Po tem so vse vrednosti tabele razporejene po vrstnem redu povečanja ali zmanjševanja naključnih števil. Iz tabele lahko izberete poljubno število prvih vrstic (5, 10, 12, 15 itd.) In jih lahko štejemo kot rezultat naključnega vzorčenja.

Lekcija: Razvrščanje in filtriranje podatkov v Excelu

Kot vidite, lahko izbiranje v preglednici Excel izvedete s pomočjo samega filtra ali z uporabo posebnih formul. V prvem primeru bo rezultat prikazan v izvorni tabeli, v drugem pa na drugem področju. Možno je izbrati eno ali več pogojev. Poleg tega lahko naključno izberete z uporabo funkcije RANDOM .

• ← Uporaba interpolacije v programu Microsoft Excel
• Uporaba funkcije za tabeliranje v programu Microsoft Excel →