Kapag nagtatrabaho sa mga talahanayan ng Excel, madalas na kinakailangan upang piliin ang mga ito ayon sa isang tiyak na pamantayan o sa ilang mga kundisyon. Ang programa ay maaaring gawin ito sa iba't ibang paraan gamit ang isang bilang ng mga tool. Tingnan natin kung paano mag-sample sa Excel gamit ang iba't ibang mga pagpipilian.
Sampling
Ang sampling ng data ay binubuo sa pamamaraan ng pagpili mula sa pangkalahatang hanay ng mga resulta na nakakatugon sa tinukoy na mga kondisyon, kasama ang kanilang kasunod na output sa isang sheet sa isang hiwalay na listahan o sa unang hanay.
Paraan 1: gumamit ng advanced autofilter
Ang pinakamadaling paraan upang gumawa ng isang pagpili ay ang paggamit ng mga advanced na autofilter. Isaalang-alang kung paano gawin ito sa isang partikular na halimbawa.
- Piliin ang lugar sa sheet, kasama ang data na nais mong sample. Sa tab "Home" mag-click sa pindutan "Pagsunud-sunurin at filter". Ito ay inilagay sa block ng mga setting. Pag-edit. Sa listahan na bubukas pagkatapos nito, mag-click sa pindutan. "Filter".
Posible na gawin nang iba. Upang gawin ito, pagkatapos piliin ang lugar sa sheet, lumipat sa tab "Data". Mag-click sa pindutan "Filter"na naka-post sa isang tape sa isang grupo "Pagsunud-sunurin at filter".
- Pagkatapos ng pagkilos na ito, lumilitaw ang mga icon sa pamagat ng talahanayan upang simulan ang pag-filter sa anyo ng mga maliliit na triangulo na nakabaligtad sa kanang gilid ng mga selula. Mag-click sa icon na ito sa pamagat ng haligi kung saan gusto naming pumili. Sa start menu, mag-click sa item "Mga Filter ng Teksto". Susunod, piliin ang posisyon "Pasadyang filter ...".
- Isinaaktibo ang custom na filter na window. Posibleng magtakda ng limitasyon kung saan gagawin ang pagpili. Sa listahan ng drop-down para sa haligi na naglalaman ng mga selyong numero ng format, na ginagamit namin bilang halimbawa, maaari kang pumili ng isa sa limang uri ng mga kondisyon:
- katumbas;
- hindi pantay;
- higit pa;
- mas malaki o pantay;
- mas mababa
Itakda natin ang kondisyon bilang isang halimbawa upang maaari lamang nating piliin ang mga halaga kung saan ang halaga ng kita ay lumampas sa 10,000 rubles. Itakda ang switch sa posisyon "Higit pa". Ipasok ang halaga sa tamang margin "10000". Upang magsagawa ng pagkilos, mag-click sa pindutan. "OK".
- Tulad ng makikita mo, pagkatapos ng pag-filter, may mga linya lamang kung saan ang halaga ng kita ay lumampas sa 10,000 rubles.
- Ngunit sa parehong hanay maaari naming idagdag ang pangalawang kondisyon. Upang gawin ito, bumalik sa custom filter window. Tulad ng makikita mo, sa mas mababang bahagi nito ay may ibang kondisyon na lumipat at ang kaukulang input field. Itakda na ngayon ang itaas na limitasyon ng pagpili ng 15,000 rubles. Upang gawin ito, itakda ang paglipat sa posisyon "Less", at sa patlang sa kanan ipasok ang halaga "15000".
Bilang karagdagan, mayroong mga kondisyon ng switch. Mayroon siyang dalawang posisyon "At" at "OR". Sa pamamagitan ng default ito ay naka-set sa unang posisyon. Nangangahulugan ito na ang mga linya lamang na nakakatugon sa parehong mga hadlang ay mananatili sa pagpili. Kung siya ay ilagay sa posisyon "OR", pagkatapos magkakaroon ng mga halaga na angkop para sa alinman sa dalawang kondisyon. Sa aming kaso, kailangan mong itakda ang switch sa "At", ibig sabihin, iwan ang default na setting na ito. Pagkatapos na maipasok ang lahat ng mga halaga, mag-click sa pindutan. "OK".
- Ngayon ang talahanayan ay may mga linya na kung saan ang halaga ng kita ay hindi mas mababa sa 10,000 rubles, ngunit hindi hihigit sa 15,000 rubles.
- Katulad nito, maaari mong i-configure ang mga filter sa ibang mga haligi. Kasabay nito, posible ring i-save ang pag-filter sa pamamagitan ng mga nakaraang kondisyon na tinukoy sa mga haligi. Kaya, tingnan natin kung paano ginagawa ang pagpili gamit ang filter para sa mga cell sa format ng petsa. Mag-click sa icon ng filter sa nararapat na haligi. I-klik ang mga item sa listahan. "I-filter ayon sa petsa" at "Pasadyang Filter".
- Nagsisimula muli ang custom na window ng autofilter. Magsagawa ng seleksyon ng mga resulta sa talahanayan mula 4 hanggang 6 Mayo 2016 kasama. Sa kondisyon ng switch sa kondisyon, tulad ng nakikita mo, mayroong higit pang mga opsyon kaysa para sa format na numero. Pumili ng posisyon "Pagkatapos o Pantay". Sa patlang sa kanan, itakda ang halaga "04.05.2016". Sa mas mababang bloke, itakda ang switch sa posisyon "Upang o katumbas ng". Ipasok ang halaga sa tamang field "06.05.2016". Ang kondisyon sa pagkakasunod-sunod switch ay naiwan sa default na posisyon - "At". Upang mailapat ang pag-filter sa pagkilos, mag-click sa pindutan "OK".
- Tulad ng iyong nakikita, ang aming listahan ay lumalaki pa. Ngayon lamang ang mga linya ay naiwan dito, kung saan ang halaga ng kita ay nag-iiba mula sa 10,000 hanggang 15,000 rubles para sa panahon mula 04.05 hanggang 06.05.2016 kasama.
- Maaari naming i-reset ang pag-filter sa isa sa mga haligi. Gawin ito para sa mga halaga ng kita. Mag-click sa icon ng autofilter sa nararapat na haligi. Sa drop-down list, mag-click sa item. "Alisin ang Filter".
- Tulad ng makikita mo, pagkatapos ng mga pagkilos na ito, ang sample sa pamamagitan ng halaga ng kita ay hindi pinagana, at ang pagpili lamang ng mga petsa ay mananatili (mula 04.05.2016 hanggang 06.05.2016).
- Ang talahanayan na ito ay may isa pang haligi - "Pangalan". Naglalaman ito ng data sa format ng teksto. Tingnan natin kung paano lumikha ng isang sample gamit ang pag-filter sa pamamagitan ng mga halagang ito.
Mag-click sa icon ng filter sa pangalan ng hanay. Magkakaiba sa listahan "Mga Filter ng Teksto" at "Pasadyang filter ...".
- Magbubukas muli ang window ng autofilter ng gumagamit. Let's do a sample by name. "Patatas" at "Karne". Sa unang bloke, ang kondisyon switch ay nakatakda sa "Katumbas sa". Sa patlang sa kanan niya ipasok ang salita "Patatas". Ang paglipat ng mas mababang bloke ay inilalagay din sa posisyon "Katumbas sa". Sa patlang na kabaligtaran sa kanya gumawa kami ng isang entry - "Karne". At pagkatapos ay ginagawa namin ang hindi pa namin ginawa bago: itinakda namin ang switch sa pagiging tugma sa posisyon "OR". Ngayon ang linya na naglalaman ng alinman sa mga tinukoy na kundisyon ay ipapakita sa screen. Mag-click sa pindutan "OK".
- Tulad ng makikita mo, sa bagong sample ay may mga limitasyon sa petsa (mula 04/05/2016 hanggang 05/06/2016) at sa pamamagitan ng pangalan (patatas at karne). Walang limitasyon sa halaga ng kita.
- Maaari mong ganap na alisin ang filter gamit ang parehong mga pamamaraan na ginamit upang i-install ito. At hindi mahalaga kung anong paraan ang ginamit. Upang i-reset ang pag-filter, na nasa tab "Data" mag-click sa pindutan "Filter"na naka-host sa isang grupo "Pagsunud-sunurin at filter".
Ang ikalawang opsyon ay nagsasangkot ng paglipat sa tab "Home". Doon ay nagsasagawa kami ng pag-click sa laso sa pindutan. "Pagsunud-sunurin at filter" sa bloke Pag-edit. Sa activate list, mag-click sa pindutan. "Filter".
Kapag ginagamit ang alinman sa dalawang paraan sa itaas, ang pag-filter ay aalisin, at ang mga resulta ng sample ay malinis. Iyon ay, ang talahanayan ay magpapakita ng buong hanay ng data na mayroon ito.
Aralin: Pag-andar ng auto filter sa Excel
Paraan 2: Gumamit ng Formula ng Array
Maaari ka ring gumawa ng isang pagpili sa pamamagitan ng paglalapat ng isang kumplikadong array formula. Hindi tulad ng nakaraang bersyon, ang pamamaraan na ito ay nagbibigay para sa output ng resulta sa isang hiwalay na table.
- Sa parehong sheet, lumikha ng isang walang laman na talahanayan na may parehong mga pangalan ng haligi sa header bilang source code.
- Piliin ang lahat ng mga walang laman na selula sa unang hanay ng bagong talahanayan. Itakda ang cursor sa bar ng formula. Lamang dito ang formula ay ipinasok, sampling ayon sa tinukoy na pamantayan. Pipili namin ang mga linya, ang halaga ng kita kung saan lumalampas sa 15,000 rubles. Sa aming partikular na halimbawa, ang formula na iyong ipinasok ay magiging ganito:
= INDEX (A2: A29; LOWEST (KUNG (15000 <= C2: C29; STRING (C2: C29); ""); STRING () - STRING ($ C $ 1)) - STRING ($ C $ 1))Naturally, sa bawat kaso ang address ng mga cell at mga saklaw ay magkakaiba. Sa halimbawang ito, maaari mong ihambing ang formula sa mga coordinate sa ilustrasyon at iakma ito sa iyong mga pangangailangan.
- Dahil ito ay isang array formula, upang mailapat ito sa pagkilos, kailangan mong hindi pindutin ang pindutan Ipasokat shortcut sa keyboard Ctrl + Shift + Enter. Ginagawa namin ito.
- Ang pagpili ng ikalawang hanay na may mga petsa at pagtatakda ng cursor sa formula bar, ipasok ang sumusunod na expression:
= INDEX (B2: B29; LOWEST (KUNG (15000 <= C2: C29; STRING (C2: C29); ""); STRING () - STRING ($ C $ 1)) - STRING ($ C $ 1))Pindutin ang shortcut ng keyboard Ctrl + Shift + Enter.
- Katulad nito, sa hanay na may kita na ipinasok namin ang sumusunod na formula:
= INDEX (C2: C29; LOWEST (KUNG (15000 <= C2: C29; STRING (C2: C29); ""); STRING () - STRING ($ C $ 1)) - STRING ($ C $ 1))Muli, nai-type namin ang shortcut Ctrl + Shift + Enter.
Sa lahat ng tatlong mga kaso, tanging ang unang halaga ng mga coordinate ang nagbabago, at ang iba pang mga formula ay ganap na magkapareho.
- Tulad ng makikita mo, ang talahanayan ay puno ng data, ngunit ang hitsura nito ay hindi kaakit-akit, maliban sa mga halaga ng petsa ay napunan ito nang hindi tama. Kinakailangang itama ang mga pagkukulang na ito. Ang maling petsa ay dahil sa ang katunayan na ang format ng mga cell sa kaukulang hanay ay karaniwan, at kailangan naming itakda ang format ng petsa. Piliin ang buong hanay, kabilang ang mga cell na may mga error, at mag-click sa pagpili gamit ang kanang pindutan ng mouse. Sa listahan na lumilitaw sa item "Cell format ...".
- Sa window ng pag-format na bubukas, buksan ang tab "Numero". Sa block "Mga Format ng Numero" piliin ang halaga "Petsa". Sa kanang bahagi ng window, maaari mong piliin ang nais na uri ng display ng petsa. Pagkatapos maitakda ang mga setting, mag-click sa pindutan. "OK".
- Ngayon ang petsa ay ipinapakita nang tama. Ngunit, tulad ng makikita mo, ang buong ilalim ng talahanayan ay puno ng mga selula na naglalaman ng maling halaga. "#NUM!". Sa katunayan, ito ang mga selula na walang sapat na data mula sa sample. Ito ay magiging mas kaakit-akit kung sila ay ipinapakita sa lahat ng walang laman. Para sa mga layuning ito, gumagamit kami ng conditional formatting. Piliin ang lahat ng mga cell sa talahanayan maliban sa header. Ang pagiging sa tab "Home" mag-click sa pindutan "Conditional Formatting"na kung saan ay sa bloke ng mga tool "Estilo". Sa listahan na lilitaw, piliin ang item "Gumawa ng panuntunan ...".
- Sa window na bubukas, piliin ang uri ng panuntunan "I-format lamang ang mga cell na naglalaman ng". Sa unang field sa ilalim ng inskripsiyon "I-format lamang ang mga cell kung saan natutugunan ang sumusunod na kalagayan" pumili ng isang posisyon "Mga Mali". Susunod, mag-click sa pindutan "Format ...".
- Sa window ng pag-format na bubukas, pumunta sa tab "Font" at piliin ang puting kulay sa nararapat na larangan. Pagkatapos ng mga pagkilos na ito, mag-click sa pindutan. "OK".
- Mag-click sa pindutan na may eksaktong parehong pangalan pagkatapos bumalik sa conditioning window.
Ngayon ay mayroon kaming isang nakahandang sample para sa tinukoy na paghihigpit sa isang hiwalay na maayos na isinaayos na talahanayan.
Aralin: Conditional Formatting sa Excel
Paraan 3: halimbawa ng ilang mga kondisyon gamit ang formula
Tulad ng kapag gumagamit ng isang filter, gamit ang formula, maaari mong i-sample sa pamamagitan ng maraming mga kondisyon. Halimbawa, kumuha ng lahat ng parehong talahanayan ng pinagmulan, pati na rin ang isang walang laman na talahanayan kung saan ang mga resulta ay ipapakita, na may ginagampanan na numeric at kondisyong pag-format. Itakda ang unang limitasyon sa mas mababang limitasyon ng pagpili para sa kita ng 15,000 rubles, at ang ikalawang kondisyon ay ang upper limit ng 20,000 rubles.
- Ipasok namin sa isang hiwalay na hanay ang mga kondisyon ng hangganan para sa sample.
- Tulad ng sa nakaraang pamamaraan, halili na piliin ang mga walang laman na haligi ng bagong talahanayan at ipasok ang nararapat na tatlong formula sa kanila. Sa unang hanay ay ipasok ang sumusunod na pananalita:
= INDEX (A2: A29; LOWEST (KUNG ($ D $ 2 = C2: C29); STRING (C2: C29); ""); STRING (C2: C29) - STRING ($ C $ 1) C $ 1))Sa kasunod na mga haligi ay ipinapasok namin nang eksakto ang parehong mga formula, tanging sa pamamagitan ng pagbabago ng mga coordinate kaagad pagkatapos ng pangalan ng operator. INDEX sa nararapat na mga haligi na kailangan namin, sa pamamagitan ng pagkakatulad sa nakaraang pamamaraan.
Sa bawat oras pagkatapos ng pagpasok huwag kalimutang i-type ang mga shortcut key Ctrl + Shift + Enter.
- Ang bentahe ng pamamaraang ito sa nakaraang isa ay kung nais nating baguhin ang mga hangganan ng sampling, hindi na natin kailangang baguhin ang array formula mismo, na kung saan mismo ay medyo may problema. Ito ay sapat na upang baguhin ang mga numero ng hangganan sa hanay ng mga kondisyon sa sheet sa mga na ang mga pangangailangan ng gumagamit. Ang mga resulta ng pagpili ay awtomatikong mababago agad.
Paraan 4: random sampling
Sa Excel na may espesyal na formula SLCIS maaari ring ilapat ang random na pagpili. Ito ay kinakailangan na gawin sa ilang mga kaso kapag nagtatrabaho sa mga malalaking halaga ng data, kapag kailangan mo upang ipakita ang isang pangkalahatang larawan na walang isang komprehensibong pagsusuri ng lahat ng mga data sa array.
- Sa kaliwa ng talahanayan, laktawan ang isang haligi. Sa cell ng susunod na hanay, na tapat sa unang cell na may data sa talahanayan, ipasok ang formula:
= RAND ()Ang function na ito ay nagpapakita ng isang random na numero. Upang maisaaktibo ito, mag-click sa pindutan ENTER.
- Upang makagawa ng isang buong hanay ng mga random na numero, itakda ang cursor sa kanang sulok sa ibaba ng cell, na naglalaman ng formula. Lumilitaw ang marker ng fill. Hilahin ito gamit ang kaliwang pindutan ng mouse na pinindot parallel sa table na may data sa pagtatapos nito.
- Ngayon kami ay may isang hanay ng mga cell na puno ng mga random na numero. Ngunit, naglalaman ito ng pormula SLCIS. Kailangan nating magtrabaho kasama ang mga dalisay na halaga. Upang gawin ito, kopyahin sa walang laman na haligi sa kanan. Piliin ang hanay ng mga cell na may mga random na numero. Matatagpuan sa tab "Home", mag-click sa icon "Kopyahin" sa tape.
- Piliin ang walang laman na haligi at i-click gamit ang kanang pindutan ng mouse, na ipinapaskil ang menu ng konteksto. Sa isang pangkat ng mga tool "Mga Pagpipilian sa Insertion" pumili ng isang item "Mga Halaga"itinatanghal bilang isang pictogram na may mga numero.
- Pagkatapos nito, nasa tab "Home", mag-click sa pamilyar na icon "Pagsunud-sunurin at filter". Sa listahan ng drop-down, ihinto ang pagpili sa item "Custom Sort".
- Isinaaktibo ang window ng pag-uuri ng pag-uuri. Tiyaking suriin ang kahon sa tabi ng parameter. "Ang aking data ay naglalaman ng mga header"kung may takip, ngunit walang tsek. Sa larangan "Ayusin ayon sa" tukuyin ang pangalan ng hanay na naglalaman ng mga kinopyang halaga ng mga random na numero. Sa larangan "Pag-uri-uriin" iwan ang mga default na setting. Sa larangan "Order" maaari mong piliin ang pagpipilian bilang "Pataas"kaya at "Pababa". Para sa isang random na sample, hindi mahalaga ito. Matapos ang mga setting ay ginawa, mag-click sa pindutan. "OK".
- Pagkatapos nito, ang lahat ng mga halaga ng talahanayan ay nakaayos sa pataas o pababang pagkakasunud-sunod ng mga random na numero. Maaari kang gumawa ng anumang bilang ng mga unang linya mula sa talahanayan (5, 10, 12, 15, atbp.) At maaaring isaalang-alang ang resulta ng isang random na sample.
Aralin: Pagsunud-sunurin at i-filter ang data sa Excel
Tulad ng makikita mo, ang sample sa spreadsheet ng Excel ay maaaring gawin, tulad ng sa tulong ng isang auto filter, at sa pamamagitan ng paglalapat ng mga espesyal na formula. Sa unang kaso, ang resulta ay ipapakita sa orihinal na talahanayan, at sa pangalawang - sa isang hiwalay na lugar. May pagkakataon na gumawa ng isang pagpili, kapwa sa isang kondisyon, at sa ilang. Bilang karagdagan, maaari kang magsagawa ng random sampling sa pamamagitan ng paggamit ng function SLCIS.
