Kā izveidot paraugu Excel: 4 darba modes

Strādājot ar Excel tabulām, bieži vien ir nepieciešams veikt izvēli uz konkrētu kritēriju vai vairākos apstākļos. Programma to var izdarīt dažādos veidos, izmantojot virkni rīku. Let's uzzināt, kā padarīt paraugu Excele, izmantojot dažādas iespējas.

Paraugu ņemšana

Datu paraugu veido atlases procedūru no kopējā šo rezultātu klāstu, kas atbilst noteiktajiem nosacījumiem, kam seko to produkcija uz atsevišķa saraksta vai avota diapazonā.

1. metode: Uzklājiet pagarinātu autofilt

Vienkāršākais veids, kā izvēlēties atlasi, ir paplašināta autofiltera izmantošana. Apsveriet, kā to izdarīt konkrētā piemērā.

1. Atlasiet apgabalu uz lapas, kuru dati, kuru vēlaties veikt paraugu. Cilnē Sākums noklikšķiniet uz pogas "Kārtot un filtrēt". Tas ir ievietots rediģēšanas iestatījumu blokā. Sarakstā, kas atvērts pēc šī saraksta, noklikšķiniet uz pogas "Filter".

   

   Ir iespēja to darīt un atšķirīgi. Lai to izdarītu, pēc tam, kad izvēlaties apgabalu uz lapas, mēs pāriet uz cilni "Dati". Noklikšķiniet uz pogas "Filter", kas tiek novietots uz lentes kārtošanas un filtru grupā.



2. Pēc šīs darbības tabulas galvenē, piktogrammas parādās, lai sāktu filtrēšanu formā malām mazo trīsstūri labajā malā šūnu. Noklikšķiniet uz šīs ikonas nosaukumā šajā kolonnā, saskaņā ar kuru mēs vēlamies veikt paraugu. Izvēlnē Running izvēlnē iet cauri "Teksta filtri" vienumu. Pēc tam izvēlieties pozīciju "Pielāgojams filtrs ...".



3. Tiek aktivizēts lietotāja filtrēšanas logs. Tajā var iestatīt ierobežojumu, uz kuru tiks veikta izvēle. Nolaižamajā sarakstā skaitliskās formāta šūnas, ko mēs izmantojam, piemēram, jūs varat izvēlēties vienu no pieciem apstākļu veidiem:
   • vienāds;
   • nav vienāds;
   • vairāk;
   • vairāk vai vienlīdzīgāks;
   • mazāks.

   Iestatīsim nosacījumu kā piemēru, lai izvēlētos tikai vērtības, par kurām ieņēmumu summa pārsniedz 10 000 rubļu. Mēs izveidojam pāreju uz "vairāk" pozīciju. Labajā laukā atbilst vērtībai "10 000". Lai veiktu darbību, noklikšķiniet uz pogas "OK".



4. Kā mēs redzam, pēc filtrēšanas, tikai līnijas palika, kurās summa ieņēmumu pārsniedz 10 000 rubļu.



5. Bet tajā pašā slejā mēs varam pievienot otro stāvokli. Lai to izdarītu, atgriezieties pie pielāgotā filtrācijas loga. Kā mēs redzam, tās apakšējā daļā ir vēl viens slēdža stāvoklis un atbilstošais ievades lauks. Ļaujiet mums tagad instalēt augšējo atlases robežu 15 000 rubļu. Lai to izdarītu, iestatiet slēdzi "mazāk" pozīcijā un laukā pa labi, atbilstu vērtībai "15000".

   Turklāt joprojām ir pārslēgs nosacījumus. Viņam ir divi noteikumi "un" un "vai". Pēc noklusējuma tas ir uzstādīts pirmajā pozīcijā. Tas nozīmē, ka tikai līnijas paliks izlasē, kas atbilst abiem ierobežojumiem. Ja tas ir iestatīts uz "vai" pozīciju, tad vērtības, kas ir piemērotas jebkuram no diviem nosacījumiem paliks. Mūsu gadījumā jums ir nepieciešams iestatīt slēdzi uz "un" pozīciju, tas ir, atstājiet šo noklusējuma iestatījumu. Pēc visām vērtībām tiek ievadītas, noklikšķiniet uz pogas OK.



6. Tagad tikai līnijas palikušas tabulā, kurā ieņēmumu summa nav mazāka par 10 000 rubļu, bet nepārsniedz 15 000 rubļu.



7. Tāpat jūs varat konfigurēt filtrus citās kolonnās. Filmows ir iespējams saglabāt filtrēšanu un iepriekšējos apstākļus, kas tika noteikti kolonnās. Tātad, pieņemsim redzēt, kā izvēle tiek veikta, izmantojot filtru datumu formātā. Noklikšķiniet uz filtrēšanas ikonas attiecīgajā kolonnā. Konsekventi, noklikšķinot uz saraksta "filtru pēc datuma" un filtru.



8. Pielāgots autofilt palaišanas logs sākas vēlreiz. Veikt izvēli rezultātus tabulā no 4. līdz 6. maijam 2016 ieskaitot. Atlases slēdzī, kā jūs varat redzēt, vēl vairāk iespēju nekā skaitliskam formātam. Izvēlieties pozīciju "pēc vai vienlīdzīgas". Laukā pa labi, iestatiet vērtību "04.05.2016". Apakšējā blokā mēs iestatījām slēdzi uz "līdz vai vienādai" pozīcijā. Pareizajā laukā ievadiet vērtību "06.05.2016". Stāvoklis saderības slēdzis Atstājiet noklusējuma pozīciju - "un". Lai piemērotu filtrēšanu darbībā, nospiediet pogu "OK".



9. Kā redzat, mūsu saraksts ir samazinājies vēl vairāk. Tagad tajā ir palikušas tikai līnijas, kurās ieņēmumu apjoms svārstās no 10 000 līdz 15 000 rubļu periodā no 04.05 līdz 06.05.2016 ieskaitot.



10. Mēs varam atiestatīt filtrēšanu vienā no kolonnām. Darīsim to ieņēmumu vērtībām. Noklikšķiniet uz ikonas AutoFilter attiecīgajā kolonnā. Nolaižamajā sarakstā noklikšķiniet uz "Dzēst filtru" vienumu.



11. Kā jūs varat redzēt, pēc šīm darbībām, paraugs ar ieņēmumu apjomu tiks atspējots, bet atlasīs tikai atlasi pēc datuma (no 04/05/2016 līdz 06.05.2016).



12. Šajā tabulā ir cita kolonna - "nosaukums". Tā satur datus teksta formātā. Let's redzēt, kā veidot paraugu, izmantojot filtrēšanu caur šīm vērtībām.

    Kolonnas nosaukumā noklikšķiniet uz filtra ikonas. Konsekventi dodieties uz saraksta nosaukumiem "Teksta filtri" un "pielāgojamu filtru ...".



13. Lietotāja autofilter logs atveras vēlreiz. Padarīsim paraugu ar nosaukumiem "kartupeļu" un "gaļas". Pirmajā blokā apstākļi pārslēdzas uz pozīciju "EQUAL". Šajā jomā pa labi no tā atbilst vārdam "kartupeļi". Apakšējās vienības slēdzis nodos arī "vienlīdzīgu" pozīciju. Šajā jomā pretī tam, es ierakstu - "gaļa". Un tagad mēs veicam to, ko viņi iepriekš nebija izdarījuši: iestatiet saderības pārslēgšanos uz pozīciju "vai". Tagad ekrānā tiks parādīts līnija, kas satur kādu no norādītajiem nosacījumiem. Noklikšķiniet uz pogas "OK".



14. Kā redzat, jaunajā paraugā ir ierobežojumi (no 04.05.2016. Līdz 06.05.2016) un pēc nosaukuma (kartupeļi un gaļa). Ieņēmumu apjomam nav nekādu ierobežojumu.



15. Jūs varat pilnībā noņemt filtru ar tādām pašām metodēm, kas tika izmantotas, lai to instalētu. Un tas nav svarīgi, kāda metode tika izmantota. Lai atiestatītu filtrēšanu, bet cilnē "Dati" noklikšķiniet uz pogas "Filter", kas atrodas "Kārtot un filtru" grupā.

    

    Otrā iespēja ietver pāreju uz cilni "Mājas". Rediģēšanas vienībā mēs veicam klikšķi uz lentes uz pogas "Kārtot un filtrēt". Aktivizētajā sarakstā noklikšķiniet uz pogas "Filter".

Lietojot kādu no divām iepriekš minētajām metodēm, filtrēšana tiks dzēsta, un izlases rezultāti tiek iztīrīti. Tas ir, tabulā tiks parādīta visu to datu masīvu.

Nodarbība: Automātiskā filtra funkcija programmā Excel

2. metode: masīva formulas pielietošana

Padarīt izvēli var izmantot arī komplekso masīva formulu. Atšķirībā no iepriekšējās versijas, šī metode nodrošina rezultāta izlaidi atsevišķā tabulā.

1. Tajā pašā lapā mēs izveidojam tukšu galdu ar tādiem pašiem kolonnu nosaukumiem galvenē kā avots.



2. Piešķirt visas jaunās tabulas pirmās slejas tukšās šūnas. Uzstādiet kursoru formulas virknē. Tieši šeit tiks ievadīti ar formulu, kas rada paraugu uz norādītajiem kritērijiem. Mēs izvēlēsimies līniju, ieņēmumu summu, kas pārsniedz 15 000 rubļu. Mūsu konkrētajā piemērā ieviestā formula izskatīsies šādi:

   = Indekss (A2: A29; mazākais (ja (15000)

   Protams, katrā konkrētā gadījumā šūnu un diapazonu adrese būs jūsu. Šajā piemērā jūs varat salīdzināt formulu ar ilustrācijas koordinātām un pielāgot to savām vajadzībām.



3. Tā kā šī ir masīva formula, lai to piemērotu darbībā, jums ir nepieciešams nospiest taustiņu Enter, bet Ctrl + Shift + ievadiet taustiņu kombināciju. Mēs to darām.



4. Ņemot iezīmējot otro sleju ar datumiem un uzstādot kursoru formulas virknē, mēs ieviešam šādu izteiksmi:

   = Indekss (b2: B29; mazākais (ja (15000)

   Noklikšķiniet uz Ctrl + Shift + ievadiet taustiņu kombināciju.



5. Līdzīgi, kolonnā ar ieņēmumiem, ievadiet formulu šādā saturā:

   = Indekss (C2: C29; mazākais (ja (15000)

   Vēlreiz ierakstiet taustiņu kombināciju Ctrl + Shift +.

   Visos trijos gadījumos mainās tikai koordinātu pirmā vērtība, un pārējā formula ir pilnīgi identiska.



6. Kā redzams, tabula ir piepildīta ar datiem, bet tā izskats nav pilnīgi pievilcīgs, turklāt datumu vērtības ir aizpildītas nepareizi. Ir nepieciešams labot šos trūkumus. Datuma nepareizība ir saistīta ar to, ka attiecīgās kolonnas šūnu formāts ir kopīgs, un mums ir jānosaka datuma formāts. Mēs izcelt visu kolonnu, ieskaitot šūnas ar kļūdām, un noklikšķiniet uz izceļot peles labo pogu. Sarakstā, kas parādās, iet cauri "šūnu formāts ...".



7. Formatēšanas logā, kas atveras, atveriet cilni "Number". "Ciparu formātos" blokā piešķiriet "Date" vērtību. Loga labajā pusē varat izvēlēties vēlamo datuma displeja veidu. Kad iestatījumi ir izstādīti, noklikšķiniet uz pogas "OK".



8. Tagad datums tiek parādīts pareizi. Bet, kā mēs redzam, viss galda apakšdaļa ir piepildīta ar šūnām, kas satur kļūdainu vērtību "# numurs!". Būtībā tās ir tās šūnas, dati no parauga, par kuriem viņiem nebija pietiekami daudz. Tas būtu pievilcīgāks, ja tie tika parādīti vispār tukši. Šiem mērķiem mēs izmantojam nosacītu formatējumu. Mēs izcelt visas tabulas šūnas, izņemot galveni. Kamēr cilnē Sākums noklikšķiniet uz pogas "Nosacījuma formatēšanas", kas atrodas rīku blokā "Stili". Sarakstā, kas parādās, izvēlieties "Izveidot noteikumu ..." vienumu.



9. Logā, kas atveras, izvēlieties noteikuma veidu "formātā tikai šūnas, kas satur". Pirmajā laukā ar uzrakstu "format tikai šūnas, kurām šādu stāvokli," izvēlieties "kļūdu" pozīciju. Pēc tam noklikšķiniet uz pogas "Format ...".



10. Formatēšanas logā darbojas, dodieties uz cilni "Fonts" un izvēlieties White attiecīgajā laukā. Pēc šīm darbībām noklikšķiniet uz pogas "OK".



11. Uz pogas ar tieši tādu pašu nosaukumu, noklikšķiniet pēc atgriešanās radīšanai uzvedības logā.

Tagad mums ir gatavs paraugs uz norādītā limita atsevišķā pareizi dekorētā tabulā.

Nodarbība: Nosacījuma formatēšana Excel

3. metode: Paraugu ņemšana vairākos apstākļos, izmantojot formulu

Tāpat kā, izmantojot filtru, izmantojot formulu, varat izvēlēties vairākos apstākļos. Piemēram, ņemiet to pašu avota tabulu, kā arī tukšu tabulu, kur rezultāti būs izvadi, ar jau veikto skaitlisko un nosacītu formatējumu. Mēs izveidosim pirmreizējo atlases apakšējās robežas ierobežojumu ar ieņēmumiem 15 000 rubļos, kā arī otro stāvokli augšējā robežas 20 000 rubļu.

1. Ievadiet atsevišķā kolonnā, par paraugu ņemšanas robežnosacījumos.



2. Tāpat kā iepriekšējā metodē, pārmaiņus sadaliet jaunās tabulas tukšās kolonnas un ievadiet atbilstošās trīs formulas. Pirmajā slejā mēs ieviest šādu izteiksmi:

   = Indekss (A2: A29; mazākais (ja ($ D $ 2 = C2: C29); līnija (C2: C29); "); virkne (C2: C29) -strkoņa ($ C $ 1)) - līnija ($ C $ 1))

   Turpmākajās kolonnās, kas atbilst tieši tādām pašām formulām, tikai mainot koordinātas tūlīt pēc operatora nosaukuma, indekss uz attiecīgajām kolonnām, kas mums ir nepieciešama, pēc analoģijas ar iepriekšējo metodi.

   Katru reizi pēc ievadīšanas neaizmirstiet iegūt CTRL + Shift + ievadiet taustiņu kombināciju.



3. Šīs metodes priekšrocība pirms iepriekšējā ir tā, ka, ja mēs vēlamies mainīt parauga robežas, nebūs nepieciešams mainīt cieto formulu, kas pats par sevi ir diezgan problemātiska. Tas ir pietiekami slejā nosacījumiem uz lapas, lai mainītu robežu skaitu tiem, kas ir nepieciešami lietotājam. Atlases rezultāti nekavējoties mainīsies.

4. metode: izlases veida paraugu ņemšana

Trylā, izmantojot īpašu formulu, ir iespējams izmantot arī izlases izvēli. Dažos gadījumos tas ir nepieciešams, strādājot ar lielu datu apjomu, kad jums ir nepieciešams iesniegt kopīgu attēlu bez visaptverošas visu masīva datu analīzi.

1. Pa kreisi no galda izlaidiet vienu kolonnu. Nākamās slejas šūnā, kas atrodas pretī pirmajai šūnai ar tabulas datiem, ievadiet formulu:

   = Līme ()

   Šī funkcija parāda nejaušu numuru. Lai to aktivizētu, noklikšķiniet uz pogas Enter.



2. Lai veiktu visu izlases numuru kolonnu, iestatiet kursoru uz šūnas apakšējo labo stūri, kas jau satur formulu. Parādās uzpildes marķieris. Es izstiept to ar kreiso peles pogu paralēli tabulai ar datiem līdz tā beigām.



3. Tagad mums ir virkne šūnu piepildīta ar izlases numuriem. Bet viņš satur formulu Calc. Mums ir jāstrādā ar tīrām vērtībām. Lai to izdarītu, kopēt tukšā kolonnā pa labi. Izvēlieties klāstu šūnu ar izlases numuriem. Atrodas cilnē "Mājas", noklikšķiniet uz "Copy" ikonas uz lentes.



4. Mēs izcelt tukšu kolonnu un noklikšķiniet uz Labu klikšķi, zvanot konteksta izvēlnē. In "Ievietot parametrus" rīkjoslā, atlasiet "Vērtības" klauzulu, kas attēlota kā piktogrammas ar cipariem.



5. Pēc tam, bet cilnē "Mājas" noklikšķiniet uz jau pazīstamā ikonas "Kārtot un filtru" ikonas. Nolaižamajā sarakstā pārtrauciet izvēli pie "Custom šķirošanas" vienumu.



6. Šķirošanas iestatījumu logs ir aktivizēts. Noteikti uzstādiet atzīmi pretī parametram "Mani dati satur virsrakstus", ja vāciņš ir pieejams, bet nav ērces. Laukā "Kārtot", norādiet tās slejas nosaukumu, kurā ir ietverti izlases numuru kopētie vērtības. Laukā "Kārtot" atstāj noklusējuma iestatījumus. Laukā "Pasūtījums", jūs varat izvēlēties parametru kā "augošā" un "dilstošā secībā". Par izlases paraugu, šī vērtība nav. Kad iestatījumi ir izgatavoti, noklikšķiniet uz pogas "OK".



7. Pēc tam visas tabulas vērtības ir būvētas augošā secībā vai izlases numuru samazināšanā. Jūs varat veikt jebkuru skaitu pirmās līnijas no galda (5, 10, 12, 15, uc), un tos var uzskatīt par izlases parauga rezultātu.

Nodarbība: Datu šķirošana un filtrēšana uz Excel

Kā redzat, paraugu Excel tabulā var ražot, gan izmantojot autofiltru un piemērojot īpašas formulas. Pirmajā gadījumā rezultāts tiks parādīts avota tabulā un otrajā - atsevišķā vietā. Ir iespējams iegūt izvēli gan vienu stāvokli, gan vairākus. Turklāt jūs varat veikt nejaušu paraugu, izmantojot līmi.