Kapag nagtatrabaho sa mga talahanayan ng Excel, madalas na kailangan mong piliin ang mga ito ayon sa isang tiyak na criterion o ayon sa ilang mga kondisyon. Magagawa ito ng programa sa iba't ibang paraan gamit ang isang bilang ng mga tool. Alamin natin kung paano mag-sample sa Excel gamit ang iba't ibang mga pagpipilian.
Sampling
Ang pagpili ng data ay binubuo sa pamamaraan ng pagpili mula sa pangkalahatang hanay ng mga resulta na masiyahan ang ibinigay na mga kondisyon, kasama ang kanilang kasunod na output sa isang sheet bilang isang hiwalay na listahan o sa orihinal na saklaw.
Paraan 1: gumamit ng advanced na autofilter
Ang pinakamadaling paraan upang makagawa ng isang pagpipilian ay ang paggamit ng isang advanced na autofilter. Isaalang-alang kung paano ito gagawin sa isang tiyak na halimbawa.
- Piliin ang lugar sa sheet, bukod sa data na nais mong gumawa ng isang pagpipilian. Sa tab "Home" mag-click sa pindutan Pagsunud-sunurin at Filter. Matatagpuan ito sa block ng mga setting. "Pag-edit". Sa listahan na bubukas pagkatapos nito, mag-click sa pindutan "Filter".
May isang pagkakataon na kumilos nang iba. Upang gawin ito, pagkatapos piliin ang lugar sa sheet, lumipat sa tab "Data". Mag-click sa pindutan "Filter"na naka-post sa tape sa pangkat Pagsunud-sunurin at Filter.
- Matapos ang pagkilos na ito, lumilitaw ang mga pictograms sa header ng talahanayan upang simulan ang pag-filter sa anyo ng mga maliit na tatsulok na nakabaligtad sa kanang gilid ng mga cell. Nag-click kami sa icon na ito sa heading ng haligi kung saan nais naming gumawa ng isang pagpipilian. Sa menu na bubukas, pumunta sa item "Mga Filter ng Teksto". Susunod, piliin ang posisyon "Pasadyang filter ...".
- Ang window ng pag-filter ng gumagamit ay isinaaktibo. Sa loob nito, maaari mong itakda ang limitasyon kung saan gagawin ang pagpili. Sa listahan ng drop-down para sa haligi na naglalaman ng mga cell ng format ng numero na ginagamit namin bilang isang halimbawa, maaari kang pumili ng isa sa limang uri ng mga kondisyon:
- katumbas ng;
- hindi pantay;
- higit pa;
- higit pa o pantay;
- mas kaunti.
Magtakda tayo ng isang halimbawa bilang isang kondisyon sa paraan na piliin lamang ang mga halaga na kung saan ang halaga ng kita ay lumampas sa 10,000 rubles. Itakda ang switch sa posisyon Marami pa. Ipasok ang halaga sa tamang patlang "10000". Upang maisagawa ang isang pagkilos, mag-click sa pindutan "OK".
- Tulad ng nakikita mo, pagkatapos ng pag-filter mayroong mga linya lamang kung saan ang halaga ng kita ay lumampas sa 10,000 rubles.
- Ngunit sa parehong haligi, maaari naming idagdag ang pangalawang kondisyon. Upang gawin ito, muli kaming bumalik sa window ng pag-filter ng gumagamit. Tulad ng nakikita mo, sa ibabang bahagi nito ay may isa pang switch ng kondisyon at ang kaukulang larangan ng pag-input. Itakda natin ngayon ang itaas na limitasyon ng pagpili sa 15,000 rubles. Upang gawin ito, ilagay ang switch sa posisyon Mas kaunti, at sa bukid sa kanan pinapasok namin ang halaga "15000".
Bilang karagdagan, mayroon ding kondisyon ng switch. May dalawang posisyon siya "At" at "O". Bilang default, nakatakda ito sa unang posisyon. Nangangahulugan ito na ang mga hilera lamang na masiyahan ang parehong mga paghihigpit ay mananatili sa sample. Kung mailalagay ito sa posisyon "O"pagkatapos ay magkakaroon ng mga halaga na magkasya sa alinman sa dalawang kundisyon. Sa aming kaso, kailangan mong itakda ang switch sa "At", iyon ay, iwanan ang setting na ito bilang default. Matapos ipasok ang lahat ng mga halaga, mag-click sa pindutan "OK".
- Ngayon sa talahanayan mayroon lamang mga linya kung saan ang halaga ng kita ay hindi mas mababa sa 10,000 rubles, ngunit hindi lalampas sa 15,000 rubles.
- Katulad nito, maaari mong i-configure ang mga filter sa iba pang mga haligi. Kasabay nito, posible na mai-save ang pag-filter alinsunod sa nakaraang mga kondisyon na naitakda sa mga haligi. Kaya, tingnan natin kung paano ginanap ang pag-filter para sa mga cell sa format ng petsa. Mag-click sa icon ng filter sa kaukulang haligi. Sequentially mag-click sa mga item sa listahan "Filter sa pamamagitan ng petsa" at Pasadyang Filter.
- Ang window ng autofilter ng gumagamit ay nagsisimula muli. Ginagawa namin ang pagpili ng mga resulta sa talahanayan mula Mayo 4 hanggang Mayo 6, 2016 kasama. Sa switch ng pagpili ng kondisyon, tulad ng nakikita natin, mayroong higit pang mga pagpipilian kaysa sa format ng numero. Pumili ng isang posisyon "Pagkatapos o katumbas". Sa patlang sa kanan, itakda ang halaga "04.05.2016". Sa ibabang bloke, itakda ang switch sa posisyon "Para o katumbas ng". Ipasok ang halaga sa tamang patlang "06.05.2016". Iniwan namin ang switch ng pagiging tugma ng kondisyon sa default na posisyon - "At". Upang mailapat ang pag-filter sa pagkilos, mag-click sa pindutan "OK".
- Tulad ng nakikita mo, ang aming listahan ay higit pang nabawasan. Ngayon lamang ang mga linya ay naiwan dito, kung saan ang halaga ng kita ay nag-iiba mula 10,000 hanggang 15,000 rubles para sa panahon mula Mayo 4 hanggang Mayo 6, 2016 kasama.
- Maaari naming i-reset ang pag-filter sa isa sa mga haligi. Gagawin namin ito para sa mga halaga ng kita. Mag-click sa icon ng autofilter sa kaukulang haligi. Sa listahan ng drop-down, mag-click sa item Alisin ang Filter.
- Tulad ng nakikita mo, pagkatapos ng mga aksyon na ito, ang pagpili sa pamamagitan ng halaga ng kita ay hindi pinagana, at ang pagpili lamang sa mga petsa ay mananatili (mula 05/04/2016 hanggang 05/06/2016).
- May isa pang haligi sa talahanayan na ito - "Pangalan". Naglalaman ito ng data sa format ng teksto. Tingnan natin kung paano lumikha ng isang seleksyon gamit ang pag-filter ng mga halagang ito.
Mag-click sa icon ng filter sa pangalan ng haligi. Dumadaan kami sa mga pangalan ng listahan "Mga Filter ng Teksto" at "Pasadyang filter ...".
- Bubukas muli ang window ng autofilter window. Gumawa tayo ng isang pagpipilian sa pamamagitan ng mga item "Patatas" at Karne. Sa unang bloke, itakda ang kondisyon lumipat sa "Pantay". Sa bukid sa kanan nito pinapasok natin ang salita "Patatas". Ang mas mababang block switch ay inilalagay din sa posisyon "Pantay". Sa bukid sa tapat nito, gumawa ng isang talaan - Karne. At pagkatapos ay ginagawa namin kung ano ang hindi namin ginawa bago: itakda ang mga kundisyon sa pagiging tugma "O". Ngayon ang isang linya na naglalaman ng anuman sa tinukoy na mga kondisyon ay ipapakita sa screen. Mag-click sa pindutan "OK".
- Tulad ng nakikita mo, sa bagong sample ay may mga paghihigpit sa petsa (mula 05/04/2016 hanggang 05/06/2016) at sa pamamagitan ng pangalan (patatas at karne). Walang mga paghihigpit sa dami ng kita.
- Maaari mong ganap na alisin ang filter sa parehong mga paraan na ginamit mo upang mai-install ito. Bukod dito, hindi mahalaga kung aling pamamaraan ang ginamit. Upang i-reset ang pag-filter, nasa tab "Data" mag-click sa pindutan "Filter"na nakalagay sa isang pangkat Pagsunud-sunurin at Filter.
Ang pangalawang pagpipilian ay nagsasangkot sa pagpunta sa tab "Home". Doon kami nag-click sa pindutan sa laso Pagsunud-sunurin at Filter sa block "Pag-edit". Sa aktibong listahan, mag-click sa pindutan "Filter".
Gamit ang alinman sa dalawang pamamaraan sa itaas, ang pag-filter ay tatanggalin, at ang mga resulta ng pagpili ay aalisin. Iyon ay, ang talahanayan ay magpapakita sa buong hanay ng data na mayroon ito.
Aralin: Pag-andar ng Autofilter sa Excel
Paraan 2: pag-apply ng isang formula ng array
Maaari ka ring gumawa ng isang pagpipilian sa pamamagitan ng pag-apply ng isang kumplikadong formula ng array. Hindi tulad ng nakaraang bersyon, ang pamamaraang ito ay nagbibigay para sa output ng resulta sa isang hiwalay na talahanayan.
- Sa parehong sheet, lumikha ng isang walang laman na mesa na may parehong mga pangalan ng haligi sa header bilang pinagmulan.
- Piliin ang lahat ng mga walang laman na cell sa unang haligi ng bagong talahanayan. Inilalagay namin ang cursor sa linya ng mga formula. Dito lamang ipapasok ang isang pormula na gumagawa ng isang pagpili ayon sa tinukoy na pamantayan. Piliin namin ang mga linya kung saan ang halaga ng kita ay lumampas sa 15,000 rubles. Sa aming tukoy na halimbawa, ang hitsura ng formula ay magiging ganito:
= INDEX (A2: A29; LOW (KUNG (15000 <= C2: C29; STRING (C2: C29); ""); STRING () - STRING ($ C $ 1)) - STRING ($ C $ 1))Naturally, sa bawat kaso, ang address ng mga cell at saklaw ay magkakaiba. Sa halimbawang ito, maaari mong ihambing ang pormula sa mga coordinate sa ilustrasyon at iakma ito sa iyong mga pangangailangan.
- Dahil ito ay isang pormula ng array, upang mailapat ito sa pagkilos, kailangan mong pindutin hindi ang pindutan Ipasok, at ang shortcut sa keyboard Ctrl + Shift + Ipasok. Ginagawa namin ito.
- Ang pagpili ng pangalawang haligi na may mga petsa at paglalagay ng cursor sa formula bar, ipinakilala namin ang sumusunod na expression:
= INDEX (B2: B29; LOW (KUNG (15000 <= C2: C29; STRING (C2: C29); ""); STRING () - STRING ($ C $ 1)) - STRING ($ C $ 1))Pindutin ang keyboard shortcut Ctrl + Shift + Ipasok.
- Katulad nito, sa haligi na may kita ay pinapasok namin ang formula tulad ng sumusunod:
= INDEX (C2: C29; LOW (KUNG (15000 <= C2: C29; STRING (C2: C29); ""); STRING () - STRING ($ C $ 1)) - STRING ($ C $ 1))Muli, ang pag-type ng isang shortcut sa keyboard Ctrl + Shift + Ipasok.
Sa lahat ng tatlong mga kaso, tanging ang mga unang pagbabago ng halaga ng coordinate, at ang natitirang bahagi ng formula ay ganap na magkapareho.
- Tulad ng nakikita mo, ang talahanayan ay puno ng data, ngunit ang hitsura nito ay hindi ganap na kaakit-akit, bilang karagdagan, ang mga halaga ng petsa ay napuno nang hindi tama. Kailangang ayusin ang mga bahid na ito. Ang petsa ay hindi tama dahil ang format ng cell ng kaukulang haligi ay karaniwan, at kailangan nating itakda ang format ng petsa. Piliin ang buong haligi, kabilang ang mga cell na may mga error, at mag-click sa pagpili gamit ang kanang pindutan ng mouse. Sa listahan na lilitaw, pumunta sa "Format ng cell ...".
- Sa window ng pag-format na bubukas, buksan ang tab "Bilang". Sa block "Mga Format ng Numero" i-highlight ang halaga Petsa. Sa kanang bahagi ng window, maaari mong piliin ang nais na uri ng pagpapakita ng petsa. Matapos maitakda ang mga setting, mag-click sa pindutan "OK".
- Ngayon ang petsa ay ipinakita nang tama. Ngunit, tulad ng nakikita natin, ang buong ibabang bahagi ng talahanayan ay puno ng mga cell na naglalaman ng isang maling halaga "# NUMBER!". Sa katunayan, ito ang mga cell na kung saan walang sapat na data mula sa sample. Mas magiging kaakit-akit kung sila ay ipinapakita nang walang laman. Para sa mga layuning ito gagamitin namin ang pag-format ng kondisyon. Piliin ang lahat ng mga cell sa talahanayan maliban sa header. Ang pagiging sa tab "Home" mag-click sa pindutan Pag-format ng Kondisyonmatatagpuan sa tool block Mga Estilo. Sa listahan na lilitaw, piliin ang "Gumawa ng isang patakaran ...".
- Sa window na bubukas, piliin ang uri ng panuntunan "I-format lamang ang mga cell na naglalaman ng". Sa unang kahon sa ilalim ng inskripsiyon "I-format lamang ang mga cell kung saan totoo ang sumusunod na kondisyon" piliin ang posisyon "Mga Mali". Susunod, mag-click sa pindutan "Format ...".
- Sa window ng pag-format na nagsisimula, pumunta sa tab Font at sa kaukulang patlang, piliin ang puti. Matapos ang mga pagkilos na ito, mag-click sa pindutan "OK".
- Mag-click sa pindutan na may eksaktong parehong pangalan pagkatapos bumalik sa window para sa paglikha ng mga kondisyon.
Ngayon mayroon kaming isang yari na sample para sa tinukoy na paghihigpit sa isang hiwalay na maayos na dinisenyo na talahanayan.
Aralin: Pag-format ng kondisyon sa Excel
Pamamaraan 3: sampling ayon sa maraming mga kondisyon gamit ang pormula
Tulad ng kapag gumagamit ng isang filter, gamit ang pormula, maaari kang pumili ayon sa maraming mga kondisyon. Para sa isang halimbawa ay kukuha kami ng lahat ng parehong talahanayan ng mapagkukunan, at din ng isang walang laman na talahanayan kung saan ang mga resulta ay ipapakita, na naisakatuparan ang pag-format ng pang-numero at kondisyon. Itinakda namin ang unang limitasyon sa mas mababang limitasyon ng pagpili para sa kita ng 15,000 rubles, at ang pangalawang kondisyon sa itaas na limitasyon ng 20,000 rubles.
- Pumasok kami sa mga kondisyon ng hangganan para sa pagpili sa isang hiwalay na haligi.
- Tulad ng sa nakaraang pamamaraan, pipiliin namin ang mga walang laman na mga haligi ng bagong talahanayan nang paisa-isa at ipasok ang kaukulang tatlong mga formula sa kanila. Sa unang haligi, idagdag ang sumusunod na expression:
= INDEX (A2: A29; LOW (KUNG ((($ D $ 2 = C2: C29); LINE (C2: C29); ""); LINE (C2: C29) -LINE ($ C $ 1)) - LINE ($ C $ 1))Sa mga sumusunod na mga haligi, pinapasok namin nang eksakto ang parehong mga formula, na binabago lamang ang mga coordinate pagkatapos ng pangalan ng operator INDEX sa kaukulang mga haligi na kailangan namin, sa pamamagitan ng pagkakatulad sa nakaraang pamamaraan.
Sa tuwing makakapasok, huwag kalimutang mag-type ng isang pangunahing kumbinasyon Ctrl + Shift + Ipasok.
- Ang bentahe ng pamamaraang ito sa nakaraan ay kung nais nating baguhin ang mga hangganan ng sample, kung gayon hindi natin kailangang baguhin ang formula ng sarili, na medyo may problema sa sarili. Ito ay sapat na sa haligi ng mga kondisyon sa sheet upang mabago ang mga hangganan na numero sa mga kinakailangan ng gumagamit. Ang mga resulta ng pagpili ay awtomatikong magbabago kaagad.
Paraan 4: random sampling
Sa Excel gamit ang isang espesyal na formula NAKAKAPALIT Maaari ring mailapat ang random na pagpili. Kinakailangan na magawa sa ilang mga kaso kapag nagtatrabaho sa isang malaking halaga ng data, kung kinakailangan upang ipakita ang pangkalahatang larawan nang walang isang komprehensibong pagsusuri ng lahat ng data sa hanay.
- Sa kaliwa ng talahanayan laktawan namin ang isang haligi. Sa cell ng susunod na haligi, na matatagpuan sa tapat ng unang cell na may data ng talahanayan, pinapasok namin ang formula:
= RAND ()Ang pagpapaandar na ito ay nagpapakita ng isang random na numero. Upang maisaaktibo ito, mag-click sa pindutan ENTER.
- Upang makagawa ng isang buong haligi ng mga random na numero, ilagay ang cursor sa ibabang kanang sulok ng cell na naglalaman ng formula. Lumilitaw ang isang fill marker. I-drag namin ito pababa gamit ang kaliwang pindutan ng mouse na pinindot kahanay sa talahanayan ng data hanggang sa dulo.
- Ngayon mayroon kaming isang hanay ng mga cell na puno ng mga random na numero. Ngunit, naglalaman ito ng isang pormula NAKAKAPALIT. Kailangan nating magtrabaho kasama ang mga purong halaga. Upang gawin ito, kopyahin sa walang laman na haligi sa kanan. Pumili ng isang hanay ng mga cell na may mga random na numero. Matatagpuan sa tab "Home"mag-click sa icon Kopyahin sa tape.
- Pumili ng isang walang laman na haligi at pag-click sa kanan, pagtawag sa menu ng konteksto. Sa pangkat ng tool Ipasok ang Mga Pagpipilian piliin ang item "Mga Pinahahalagahan"isinalarawan bilang isang pictogram na may mga numero.
- Pagkatapos nito, nasa tab "Home", mag-click sa icon na alam na natin Pagsunud-sunurin at Filter. Sa listahan ng drop-down, itigil ang pagpili sa Pasadyang Pagbukud-bukurin.
- Ang window ng mga setting ng pag-aayos ay isinaaktibo. Siguraduhing suriin ang kahon sa tabi ng parameter "Ang aking data ay naglalaman ng mga header"kung may isang sumbrero ngunit walang checkmark. Sa bukid Pagsunud-sunurin ayon ipahiwatig ang pangalan ng haligi na naglalaman ng mga kinopyang mga halaga ng mga random na numero. Sa bukid "Pagsunud-sunurin" iwanan ang mga default na setting. Sa bukid "Order" maaari mong piliin ang parameter bilang "Umakyat"kaya at "Descending". Para sa random sampling, hindi mahalaga. Matapos magawa ang mga setting, 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 kumuha ng anumang bilang ng mga unang linya mula sa talahanayan (5, 10, 12, 15, atbp.) At maaari silang isaalang-alang na resulta ng random sampling.
Aralin: Pagsunud-sunurin at i-filter ang data sa Excel
Tulad ng nakikita mo, ang pagpili sa spreadsheet ng Excel ay maaaring gawin alinman sa paggamit ng autofilter o pag-apply ng mga espesyal na formula. Sa unang kaso, ang resulta ay ipapakita sa orihinal na talahanayan, at sa pangalawa - sa isang hiwalay na lugar. Posible na gumawa ng isang pagpipilian, kapwa sa isang kondisyon at sa marami. Maaari mo ring piliin ang random na gamit ang function NAKAKAPALIT.
