Lokacin aiki tare da tebur na Excel, sau da yawa sau ɗaya dole ne ka zaɓa su gwargwadon takamaiman sananniya ko bisa ga yanayi da yawa. Shirin na iya yin wannan ta hanyoyi da dama ta amfani da kayan aikin da yawa. Bari mu gano yadda za a yi samfuri a cikin Excel ta amfani da zaɓuɓɓuka da yawa.
Samfurowa
Zaɓin bayanai ya ƙunshi tsarin zaɓi daga janar na waɗannan sakamakon waɗanda ke gamsar da yanayin da aka bayar, tare da fitowar abubuwan da suka biyo baya a kan takarda azaman keɓance daban ko cikin ainihin asali.
Hanyar 1: yi amfani da autofilter mai ci gaba
Hanya mafi sauƙaƙa don zaɓin ita ce amfani da na'urar sarrafa kansa. Yi la'akari da yadda ake yin wannan tare da takamaiman misali.
- Zaɓi yanki a kan takardar, tsakanin bayanan da kake son zaɓar. A cikin shafin "Gida" danna maballin Dadi da kuma Matatarwa. An iske a cikin shinge na saiti. "Gyara". A lissafin da zai buɗe bayan wannan, danna maballin "Tace".
Akwai damar aiki daban. Don yin wannan, bayan zaɓin yankin akan takardar, matsa zuwa shafin "Bayanai". Latsa maballin "Tace"wanda aka lika a jikin tef a cikin rukunin Dadi da kuma Matatarwa.
- Bayan wannan matakin, hotonta sun bayyana a saman teburin don fara tacewa a cikin ƙananan ƙananan dutsen da aka juya a gefen dama na sel. Mun danna wannan alamar a cikin taken shafi wanda muke so mu zaba. A cikin menu wanda yake buɗe, je zuwa abun "Matattarar rubutu". Na gaba, zaɓi wuri "Matatar tace ...".
- Ana kunna taga mai amfani. A ciki, zaku iya saita iyaka ta wacce za'ayi. A cikin jerin zaɓi ƙasa don shafi wanda ke ɗauke da sel nau'in lambar da muke amfani da shi azaman misali, zaka iya zaɓar ɗayan yanayi biyar:
- daidai yake da;
- ba daidai ba;
- ƙari;
- fiye ko daidai;
- kasa.
Bari mu kafa misali a matsayin yanayi a cikin irin wannan hanyar don zaɓar kawai dabi'u waɗanda adadin kudaden shiga ya wuce 10,000 rubles. Saita canji zuwa wuri .Ari. Shigar da darajar a cikin filin dama "10000". Don aiwatar da aiki, danna maɓallin "Ok".
- Kamar yadda kake gani, bayan tace akwai layuka kawai waɗanda adadin kudaden shiga ya wuce 10,000 rubles.
- Amma a cikin shafi guda, zamu iya ƙara yanayin na biyu. Don yin wannan, muna sake komawa taga mai tace mai amfani. Kamar yadda kake gani, a kasan sa akwai wani yanayin canzawa da kuma filin shigarwarsa. Bari yanzu mu kafa iyakar zaɓin a 15,000 rubles. Don yin wannan, sanya madaidaiciyar a wuri Kadan, kuma a filin daga hannun dama mun shiga ƙimar "15000".
Bugu da kari, akwai kuma yanayin canzawa. Yana da matsayi biyu "Kuma" da "KO". Ta hanyar tsoho, an saita shi zuwa matsayi na farko. Wannan yana nufin cewa layuka kawai waɗanda suka gamsar da hane-hane zasu zauna a samfurin. Idan za'a sa shi a wuri "KO"sannan akwai kyawawan dabi'u wadanda zasu dace da kowane ɗayan biyun. A cikin yanayinmu, kuna buƙatar saita canjin zuwa "Kuma", wato, bar wannan saitin azaman tsoho. Bayan an shigar da dukkan dabi'u, danna maballin "Ok".
- Yanzu a cikin teburin akwai layin kawai wanda adadin kudaden shiga ba ya ƙarancin 10,000 rubles, amma bai wuce 15,000 rubles ba.
- Hakanan, zaku iya saita matattara a cikin wasu ginshiƙai. A lokaci guda, yana yiwuwa a adana mai tace gwargwadon yanayin da aka saita a cikin ginshiƙan. Don haka, bari mu ga yadda ake yin matatar don sel a tsarin kwanan wata. Danna kan matatar mai tace a shafi mai dacewa. Mafi muni a jerin abubuwan "Tace ta kwanan wata" da Filin Kaya.
- Mai amfani da windowofofter mai amfani yana fara sake. Mun gudanar da zabin sakamako a cikin tebur daga 4 ga Mayu zuwa 6 ga Mayu, 6, 2016. A cikin canjin yanayin, kamar yadda muke gani, akwai ƙarin zaɓuɓɓuka fiye da yadda aka tsara lamba. Zabi wani matsayi "Bayan ko daidai". A filin daga hannun dama, saita ƙimar "04.05.2016". A cikin ƙananan toshe, saita sauyawa zuwa matsayi "To, kuma yayi daidai da". Shigar da darajar a cikin filin dama "06.05.2016". Mun bar yanayin karfin gwiwa canji a cikin tsoho wuri - "Kuma". Domin aiwatar da tacewa a aikace, danna maballin "Ok".
- Kamar yadda kake gani, an kara rage jerin sunayen namu. Yanzu layuka ne kawai suka rage a ciki, wanda adadin kudaden shiga ya bambanta daga 10,000 zuwa 15,000 rubles na tsawon lokacin daga 4 ga Mayu zuwa 6 ga Mayu, 6, 2016.
- Zamu iya sake saita tata a ɗayan ɓangarorin. Za mu yi wannan don ƙimar kuɗin shiga. Danna kan alamar autofilter a cikin shafi mai dacewa. A cikin jerin zaɓi, danna kan kayan Cire Filter.
- Kamar yadda kake gani, bayan waɗannan ayyuka, zaɓi ta hanyar yawan kudaden shiga zai zama naƙasasshe, kuma zaɓin kawai ta kwanan wata zai ragu (daga 05/04/2016 zuwa 05/06/2016).
- Akwai wani shafi a wannan tebur - "Suna". Ya ƙunshi bayanai a tsarin rubutu. Bari mu ga yadda za a ƙirƙiri zaɓi ta amfani da tacewa ta waɗannan dabi'un.
Danna kan alamar tacewa a sunan shafi. Za mu shiga cikin jerin sunayen "Matattarar rubutu" da "Matatar tace ...".
- Takaitaccen mai amfani da window na sake buɗewa. Bari mu zaba abubuwa ta abubuwa "Dankali" da Nama. A farkon toshe, saita yanayin canzawa zuwa "Daidaita". A fagen daga hannun dama muna shigar da kalmar "Dankali". Hakanan an sanya ƙananan jujjuyawa a cikin matsakaici "Daidaita". A fagen gaba da shi, yi rikodin - Nama. Kuma a sa'an nan za mu yi abin da ba mu aikata ba kafin: saita yanayin karfin gwiwa canza zuwa "KO". Yanzu layin da ke ɗauke da kowane yanayi da aka ƙayyade za a nuna shi akan allon. Latsa maballin "Ok".
- Kamar yadda kake gani, a cikin sabon samfurin akwai ƙuntatawa akan kwanan wata (daga 05/04/2016 zuwa 05/06/2016) da kuma suna (dankali da nama). Babu hani akan yawan kudaden shiga.
- Za ka iya cire matatar gabaɗaya a cikin hanyoyin da ka yi amfani da shi. Hakanan, bashi da mahimmanci wacce hanya akayi amfani dashi. Don sake saita tacewa, kasancewa a cikin shafin "Bayanai" danna maballin "Tace"wanda aka sanya a cikin rukuni Dadi da kuma Matatarwa.
Na biyu zaɓi ya ƙunshi zuwa shafin "Gida". A nan muna danna maballin akan kintinkiri Dadi da kuma Matatarwa a toshe "Gyara". A cikin jerin kunnawa, danna maballin "Tace".
Ta amfani da ɗayan hanyoyin biyu da ke sama, za a share matatar, kuma za a share sakamakon zaɓin. Wato, teburin zai nuna makaɗan bayanan da yake dashi.
Darasi: Aikin Autofilter a cikin Excel
Hanyar 2: amfani da tsararren tsari
Hakanan zaka iya yin zaɓi ta amfani da mahimmin tsari. Ba kamar sigar da ta gabata ba, wannan hanyar tana ba da damar fitar da sakamakon a tebur daban.
- A kan takardar iri ɗaya, ƙirƙirar tebur mara komai tare da suna ɗaya shafi a cikin taken a matsayin tushen.
- Zaɓi duk ƙwayoyin komai a cikin farkon shafi na sabon tebur. Mun sanya siginan kwamfuta a cikin layin tsari. Kawai anan za a shigar da dabara wanda zai samar da zabi gwargwadon ka'idodin da aka ƙayyade. Mun zaɓi layin da adadin kudaden shiga ya wuce 15,000 rubles. A cikin ƙayikanmu na musamman, tsarin shigarwar zai yi kama da wannan:
= INDEX (A2: A29; LOW (IF (15000 <= C2: C29; STRING (C2: C29); ""); STRING)) (STRING - $ C $ 1)) - STRING ($ C $ 1))A zahiri, a kowane yanayi, adireshin sel da jeri zai bambanta. A cikin wannan misalin, zaku iya kwatanta dabara tare da daidaitawa a cikin hoton kuma daidaita shi zuwa ga bukatun ku.
- Tunda wannan tsari ne na tsari, don aiwatar dashi a aikace, kuna buƙatar danna ba maɓallin Shigar, da gajeriyar hanya Ctrl + Shift + Shigar. Muna yi.
- Zabi na biyu shafi tare da kwanakin da sanya siginan kwamfuta a cikin masarar dabara, muna gabatar da magana kamar haka:
= INDEX (B2: B29; LOW (IF (15000 <= C2: C29; STRING (C2: C29); ""); STRING)) (STRING - $ C $ 1)) - STRING ($ C $ 1))Latsa gajerar hanya Ctrl + Shift + Shigar.
- Hakanan, a cikin shafi tare da kudaden shiga muna shigar da dabara kamar haka:
= INDEX (C2: C29; LOW (IF (15000 <= C2: C29; STRING (C2: C29); ""); STRING)) (STRING - $ C $ 1)) - STRING ($ C $ 1))Sake kuma, buga gajerar hanyar rubutu Ctrl + Shift + Shigar.
A duk abubuwan ukun, kawai canjin darajar farko ya canza, sauran sikelin kuma cikakku ne.
- Kamar yadda kake gani, teburin ya cika da bayanai, amma bayyanar tasa ba ta da kyau gaba ɗaya, a ƙari, ƙimar kwanan wata tana cike da kuskure. Buƙatar gyara waɗannan aiyukan. Ranar ba daidai ba ce saboda tsarin sigar wayar da ta dace shine na kowa, kuma muna buƙatar saita tsarin kwanan wata. Zaɓi gaba ɗaya shafi, gami da sel tare da kurakurai, kuma danna kan zaɓi tare da maɓallin linzamin kwamfuta na dama. A lissafin da ya bayyana, je zuwa "Tsarin kwayar halitta ...".
- A cikin taga tsarawa wanda zai buɗe, buɗe shafin "Lambar". A toshe "Lambobin adadi" haskaka darajar Kwanan Wata. A hannun dama na taga, zaka iya zaɓar nau'in nuni kwanan wata da ake so. Bayan an saita saiti, danna maballin "Ok".
- Yanzu an nuna kwanan wata daidai. Amma, kamar yadda muke gani, duk ƙananan ɓangarorin teburin suna cike da sel waɗanda ke ɗauke da ƙimar kuskure "# LAMBAR!". A zahiri, waɗannan sel waɗancan ne waɗanda babu isasshen data daga samfurin. Zai zama mafi kyawu idan aka nuna su babu komai. Don waɗannan dalilai zamu yi amfani da Tsarin yanayi. Zaɓi duk ƙwayoyin da ke cikin teburi banda kan. Kasancewa a cikin shafin "Gida" danna maballin Tsarin Yanayilocated a cikin toshe kayan aiki Salo. Cikin jeri wanda ya bayyana, zaɓi "Airƙiri doka ...".
- A cikin taga da ke buɗe, zaɓi nau'in mulkin "Haɗi kawai sel waɗanda suke ɗauke da". A cikin akwatin farko a ƙarƙashin rubutu "Saka ƙwayoyin sel kawai waɗanda yanayin masu gaskiya gaskiya ne" zaɓi matsayi "Kurakurai". Bayan haka, danna maballin "Tsarin ...".
- A cikin taga tsara wanda zai fara, je zuwa shafin Harafi kuma a cikin filin m, zaɓi fari. Bayan waɗannan ayyukan, danna maballin "Ok".
- Danna maballin tare da ainihin sunan daidai bayan komawa zuwa taga don ƙirƙirar yanayi.
Yanzu muna da samfurin da aka shirya don ƙayyadaddun ƙuntatawa a cikin teburin da aka tsara daidai.
Darasi: Tsarin yanayi a cikin Excel
Hanyar 3: samfuri bisa ga yanayi da yawa ta amfani da dabara
Kamar dai lokacin amfani da matattara, ta amfani da dabara, zaku iya zaɓar gwargwadon yanayi da yawa. Misali zamu dauki dukkanin teburin tushe iri daya, da kuma tebur mara komai inda za'a nuna sakamako, tare da lambobi da aka riga aka aiwatar dasu. Mun sanya iyaka ta farko zuwa ƙarancin zaɓi na zaɓi don kudaden shiga na 15,000 rubles, kuma yanayi na biyu zuwa iyakar babba na 20,000 rubles.
- Mun shigar da yanayin iyaka don zaɓin a cikin wani shafi daban.
- Kamar yadda yake a cikin hanyar da ta gabata, muna zaɓar ginshiƙan sabon tebur ɗaya bayan ɗaya kuma shigar da dabarun uku da ke ciki. A cikin farko shafi, ƙara da wadannan magana:
= INDEX (A2: A29; LOW (IF (($ D $ 2 = C2: C29); LINE (C2: C29); ""); LINE (C2: C29) -NAYE ($ C $ 1)))) - LINE ($ C $ 1))A cikin ginshiƙan da ke biye, muna shigar da tsarin guda ɗaya daidai, kawai canza masu daidaitawa kai tsaye bayan sunan mai aiki INDEX zuwa madaidaitan ginshiƙai waɗanda muke buƙata, ta hanyar kwatanta tare da hanyar da ta gabata.
Kowane lokaci bayan shigar, kar a manta don buga hade hade Ctrl + Shift + Shigar.
- Amfanin wannan hanyar fiye da wanda ya gabata shine idan muna son sauya iyakokin samfuran, to bazamu buƙatar sauya dabara na kanta ba, wanda yake matsala ce kanta. Ya isa a cikin shafi na yanayi a kan takardar don canza lambobin iyaka zuwa waɗanda mai amfani yake buƙata. Sakamakon zaɓin zai canza kai tsaye.
Hanyar 4: Sample bazuwar
A Excel ta amfani da tsari na musamman SAURARA Hakanan za'a iya amfani da zaɓi na bazuwar. Ana buƙatar samar da shi a wasu yanayi yayin aiki tare da adadi mai yawa na bayanai, lokacin da ya zama dole gabatar da hoto na gaba ɗaya ba tare da cikakken nazarin abubuwan data gaba ba.
- A hagu na tebur mun tsallake layi ɗaya. A cikin sashin layi na gaba, wanda yake a gaban sel na farko tare da bayanan tebur, mun shigar da dabara:
= RAND ()Wannan aikin yana nuna lambar bazuwar. Domin kunna shi, danna maballin Shiga.
- Don yin kowane rukuni na lambobin bazuwar, sanya siginan kwamfuta a cikin ƙananan kusurwar dama na tantanin halitta wanda ya riga ya ƙunshi tsarin. Alamar cike take bayyana. Mun ja shi ƙasa tare da maɓallin linzamin kwamfuta na hagu da aka matse a layi daya zuwa tebur ɗin data zuwa ƙarshen.
- Yanzu muna da kewayon sel cike da lambobin bazuwar. Amma, ya ƙunshi dabara SAURARA. Muna buƙatar aiki tare da kyawawan dabi'u. Don yin wannan, kwafa zuwa labulen fanko na dama. Zaɓi kewayon sel tare da lambobin bazuwar. Ana zaune a cikin shafin "Gida"danna alamar Kwafa a kan tef.
- Zaɓi shafi mara lahani da dannawar dama, suna kiran menu. A cikin rukunin kayan aiki Saka Zabi zaɓi abu "Dabi'u"wanda aka nuna azaman hotonto tare da lambobi.
- Bayan haka, kasancewa a cikin shafin "Gida", danna alamar da muka riga muka sani Dadi da kuma Matatarwa. A cikin jerin zaɓi, dakatar da zaɓi a Tsarin Kasuwanci.
- Ana kunna taga tsarin saiti. Tabbatar duba akwatin kusa da sigogi "My bayanai yana dauke da taken"idan akwai hat amma babu alamar. A fagen A ware ta nuna sunan shafi wanda ya ƙunshi lambobin da aka kwafa na lambobin bazuwar. A fagen "Tace" barin tsoffin saitunan. A fagen "Oda" zaka iya zaɓar sigar kamar yadda "Hawan zuwa"haka kuma "Cancanci". Don samfuran bazuwar, wannan ba matsala. Bayan an saita saiti, danna maballin "Ok".
- Bayan haka, duk halayen tebur an shirya su ta hawa ko sauka zuwa lambobin lambobi ba daidai ba. Kuna iya ɗaukar kowane lamba na layin farko daga tebur (5, 10, 12, 15, da dai sauransu) kuma ana iya la'akari dasu sakamakon samfuran samfuran bazuwar.
Darasi: A ware da kuma tace bayanai a cikin Excel
Kamar yadda kake gani, zabin a cikin babbar hanyar fasali mai kyau ana iya yin shi ko dai ta amfani da atomatik ko kuma amfani da tsari na musamman. A cikin yanayin farko, za a nuna sakamakon a cikin tebur na asali, kuma a karo na biyu - a wani yanki daban. Yana yiwuwa a zaɓi, duka a yanayi ɗaya kuma da yawa. Hakanan zaka iya za usingar amfani da aikin SAURARA.
