Lokacin aiki tare da Tables na Excel, sau da yawa wajibi ne a zabi su bisa ga wani takaddama ko a kan yanayi da yawa. Shirin zai iya yin hakan a hanyoyi masu yawa ta amfani da kayan aikin da dama. Bari mu kwatanta irin yadda za a samo asali a cikin Excel ta amfani da zabin iri-iri.
Samfur
Samfurin samfurori ya ƙunshi hanyar zaɓi daga jerin tsararren waɗanda sakamakon da ya dace da yanayin da aka ƙayyade, tare da samfurin su na ƙarshe a kan takarda a jerin da aka raba ko a cikin farko.
Hanyar 1: amfani da ci gaba autofilter
Hanyar da ta fi dacewa don yin zaɓin shine don amfani da ci gaba autofilter. Yi la'akari da yadda za a yi haka tare da misali.
- Zaɓi yankin a kan takardar, tsakanin bayanan da kake son samfurin. A cikin shafin "Gida" danna maballin "Tsara da tace". An sanya shi a cikin saitunan toshe. Ana gyara. A cikin jerin da ya buɗe bayan wannan, danna kan maballin. "Filter".
Yana yiwuwa a yi daban. Don yin wannan, bayan zaɓar yankin a kan takardar, matsa zuwa shafin "Bayanan". Danna maballin "Filter"wanda aka buga a kan tef a cikin rukuni "Tsara da tace".
- Bayan wannan aikin, gumakan suna bayyana a cikin maɓallin kewayawa don fara farawa a cikin nau'i na ƙananan triangles waɗanda suka juya a gefen dama na sel. Danna kan wannan icon a cikin taken na shafi wanda muke son yin wani zaɓi. A cikin fara menu, danna kan abu "Fassarar rubutun". Kusa, zaɓi matsayi "Tace tace ...".
- An kunna al'ada ta gyaran taga. Zai yiwu a saita iyaka wanda za'a zaɓa. A cikin jerin saukewa don shafi wanda ya ƙunshi lambobin tsarin lambobi, wanda muke amfani da su azaman misali, za ka iya zaɓar ɗaya daga cikin nau'ikan yanayi guda biyar:
- daidai;
- ba daidai ba;
- karin;
- mafi girma ko daidai;
- m
Bari mu sanya yanayin a matsayin misali don mu iya zaɓar dabi'u wanda yawan kudin shiga ya wuce 10,000 rubles. Saita canza zuwa matsayi "Ƙari". Shigar da darajar a gefen dama "10000". Don yin wani aiki, danna maballin. "Ok".
- Kamar yadda ka gani, bayan da aka gyara, akwai layuka wanda yawan kudin shiga ya wuce 10,000 rubles.
- Amma a cikin wannan shafi zamu iya ƙara yanayin na biyu. Don yin wannan, komawa zuwa ga takarda ta al'ada. Kamar yadda ka gani, a cikin ƙananan ƙananan akwai wani canza yanayin kuma filin shigar da ya dace. Bari mu saita yanzu zafin iyaka na 15,000 rubles. Don yin wannan, saita sauyawa zuwa matsayi "Kadan", kuma a filin zuwa dama ya shiga darajar "15000".
Bugu da kari, akwai yanayin canzawa. Yana da matsayi guda biyu "Kuma" kuma "OR". By tsoho an saita shi zuwa matsayi na farko. Wannan yana nufin cewa kawai layin da za ta gamsar da matsaloli guda biyu za su kasance cikin zabin. Idan an sanya shi cikin matsayi "OR", to, akwai lambobin da suka dace da ko dai daga cikin yanayin biyu. A yanayinmu, kana buƙatar saita sauyawa zuwa "Kuma", wato, bar wannan tsoho saitin. Bayan duk abubuwan kirki sun shiga, danna kan maballin. "Ok".
- Yanzu tebur yana da hanyoyi ne kawai wanda adadin kudin shiga ba kasa da 10,000 rubles ba, amma ba ya wuce mita 15,000.
- Hakazalika, za ka iya saita maɓuɓɓuka a wasu ginshiƙai. A lokaci guda kuma, yana yiwuwa ya adana tsaftacewa ta yanayin da aka ƙayyade a cikin ginshiƙai. Don haka, bari mu ga yadda aka yi zaɓin ta ta yin amfani da tacewa don sel a cikin tsarin kwanan wata. Danna kan maɓallin tace a cikin shafi na daidai. Ka danna kan abubuwan da ke jerin. "Buga ta hanyar kwanan wata" kuma "Filin Jiki".
- Halin da aka saba amfani da ita yana sake farawa. Yi wani zaɓi na sakamakon a cikin teburin daga 4 zuwa 6 ga Mayu 2016. A cikin yanayin zaɓin zaɓi, kamar yadda kake gani, akwai wasu zaɓuɓɓuka fiye da tsarin lambobi. Zaɓi matsayi "Bayan ko Daidai". A cikin filin a dama, saita darajar "04.05.2016". A cikin ƙananan ƙananan, saita maɓallin zuwa matsayi "Don ko daidai da". Shigar da darajar a filin dace "06.05.2016". An bar yanayin haɓaka dacewa a cikin matsayi na asali - "Kuma". Domin yin amfani da gyare-gyaren aiki, danna kan maballin "Ok".
- Kamar yadda kake gani, lissafinmu ya ƙara ƙari. Yanzu ana barin rassa a ciki, inda yawan kudaden shiga ya bambanta daga 10,000 zuwa 15,000 rubles na tsawon lokaci daga 04.05 zuwa 06.05.2016.
- Za mu iya sake saitawa a cikin ɗayan ginshiƙan. Yi wannan don dabi'un kuɗi. Danna kan icon autofilter a cikin shafi na daidai. A cikin jerin layi, danna kan abu. "Cire Filter".
- Kamar yadda ka gani, bayan wadannan ayyukan, samfurin da yawan kudaden shiga za a kashe, kuma zaɓin zaɓaɓɓen kwanakin zai kasance (daga 04.05.2016 zuwa 06.05.2016).
- Wannan tebur yana da wani shafi - "Sunan". Ya ƙunshi bayanai cikin tsarin rubutu. Bari mu ga yadda za mu samo samfurin ta amfani da tacewa ta waɗannan dabi'u.
Danna kan maɓallin tacewa a cikin sunan shafi. Ta hanyar shiga cikin jerin "Fassarar rubutun" kuma "Tace tace ...".
- Mai amfani autofilter taga ya sake buɗewa. Bari mu yi samfurin da sunan. "Dankali" kuma "Naman". A cikin farko toshe, an saita canza yanayin zuwa "Daidaicin". A cikin filin zuwa dama na shi shigar da kalma "Dankali". Canji na ƙananan ƙananan kuma ya sa a matsayi "Daidaicin". A filin da ke gaban shi muna yin shigarwa - "Naman". Kuma sai muka yi abin da ba mu yi ba a baya: mun sanya daidaitawa canza zuwa matsayin "OR". Yanzu layin da ke dauke da duk wani yanayin da aka ƙayyade za'a nuna a allon. Danna maballin "Ok".
- Kamar yadda kake gani, a cikin sabon samfurin akwai iyakoki a ranar (daga 04 zuwa 05 zuwa 05/06/2016) da sunan (dankalin turawa da nama). Babu iyaka akan adadin kudaden shiga.
- Kuna iya cire takarda ta atomatik ta hanyar amfani da hanyoyin da aka yi amfani dashi don shigar da shi. Kuma ko ta yaya aka yi amfani da hanya. Don sake sake gyarawa, zama a cikin shafin "Bayanan" danna maballin "Filter"wanda aka shirya a cikin rukuni "Tsara da tace".
Hanya na biyu ya kunshi sauyawa zuwa shafin "Gida". A nan muna yin danna kan rubutun a kan maballin. "Tsara da tace" a cikin shinge Ana gyara. A cikin jerin kunnawa kunna danna. "Filter".
Lokacin yin amfani da ko wane daga cikin hanyoyi guda biyu da suka gabata, za a cire samfurin, kuma za a share sakamakon sakamakon samfurin. Wato, tebur zai nuna dukkanin bayanan bayanan da yake da ita.
Darasi: Taimako ta atomatik a Excel
Hanyar 2: Yi amfani da Formats Array
Hakanan zaka iya yin zaɓi ta hanyar yin amfani da matakan mahalli. Ba kamar yadda aka riga aka buga ba, wannan hanya ta samar da sakamako na sakamakon a cikin tebur daban.
- A kan wannan takardar, ƙirƙirar tebur mai mahimmanci tare da sunayen mahallin guda a cikin maɓallin kai azaman lambar source.
- Zaɓi dukkan nau'ikan kullun na farko shafi na sabon launi. Saita siginan kwamfuta a cikin tsari. Kamar dai wannan tsari zai shiga, samfuri bisa ga ka'idodi da aka ƙayyade. Za mu zaɓa Lines, adadin kudaden shiga wanda ya zarce 15,000 rubles. A cikin misali na musamman, hanyar da kuka shigar zai yi kama da wannan:
= INDEX (A2: A29; LOWEST (IF (15000 <= C2: C29; STRING () - STRING ($ C $ 1)A halin yanzu, a kowane hali adreshin sel da jeri zasu zama daban. A cikin wannan misali, zaku iya kwatanta dabarun tare da haɗin kai a cikin hoto kuma ku daidaita shi don bukatunku.
- Tun da wannan wata jumlar tsari, don amfani da ita a cikin aikin, kana buƙatar danna maɓallin ba Shigarda gajerun hanyar keyboard Ctrl + Shigar + Shigar. Muna yin hakan.
- Zaɓin shafi na biyu tare da kwanakin da kuma sanya siginan kwamfuta a cikin ma'auni, shigar da waɗannan kalmomi:
= INDEX (B2: B29; LOWEST (IF (15000 <= C2: C29; STRING (C2: C29); ""); STRING () - STRING ($ C $ 1) - STRING ($ C $ 1)Kashe gajeren hanya na keyboard Ctrl + Shigar + Shigar.
- Hakazalika, a cikin shafi tare da kudaden shiga mun shigar da wannan tsari:
= INDEX (C2: C29; LOWEST (IF (15000 <= C2: C29; STRING (C2: C29); ""); STRING () - STRING ($ C $ 1) - STRING ($ C $ 1)Bugu da ƙari, muna rubuta gajeren hanya Ctrl + Shigar + Shigar.
A cikin dukkan lokuta uku, kawai ƙimar farko na jagororin sun canza, kuma sauran sauran ƙidodi sun kasance daidai.
- Kamar yadda kake gani, tebur ya cika da bayanai, amma bayyanar ba ta da kyau sosai, banda haka, kwanan wata sun cika a cikin kuskure. Dole ne a gyara waɗannan gazarorin. Kwanan wata ba daidai ba ne saboda gaskiyar cewa tsarin cikin sel a cikin shafi daidai yake na kowa, kuma muna buƙatar saita tsarin kwanan wata. Zaɓi duk shafi, ciki har da sassan da kurakurai, kuma danna maɓallin zaɓi tare da maɓallin linzamin kwamfuta na dama. A cikin jerin da ke bayyana akan abu "Tsarin tsarin ...".
- A cikin tsarin tsarawa wanda ya buɗe, buɗe shafin "Lambar". A cikin toshe "Formats Matsala" zaɓi darajar "Kwanan wata". A cikin ɓangaren dama na taga, za ka iya zaɓar nau'in alamar kwanan wata. Bayan an saita saitunan, danna maballin. "Ok".
- Yanzu kwanan wata ya nuna daidai. Amma, kamar yadda kake gani, duk kasan tebur yana cike da sel wanda ya ƙunshi nauyin kuskure. "#NUM!". A gaskiya ma, waɗannan su ne sel waɗanda ba su da isasshen bayanai daga samfurin. Zai zama mafi kyau idan an nuna su a komai. Ga waɗannan dalilai, zamu yi amfani da tsari na yanayin. Zaɓi duk sel a cikin tebur sai dai rubutun. Da yake cikin shafin "Gida" danna maballin "Tsarin Yanayin"wanda yake a cikin asalin kayan aiki "Sanya". A cikin jerin da aka bayyana, zaɓi abu "Ƙirƙiri wata doka ...".
- A cikin taga da ke buɗewa, zaɓi irin mulkin "Shirye kawai kwayoyin halitta waɗanda ke dauke da". A filin farko a ƙarƙashin rubutun "Shirye kawai kwayoyin halitta wanda aka haɗu da yanayin da ke gaba" zabi matsayi "Kurakurai". Kusa, danna maballin "Tsarin ...".
- A cikin tsarin tsarawa wanda ya buɗe, je zuwa shafin "Font" kuma zaɓi launin fararen launi a filin dace. Bayan wadannan ayyukan, danna kan maballin. "Ok".
- Danna maballin tare da ainihin sunan daya bayan komawa taga.
Yanzu muna da samfurin da aka shirya don ƙayyade ƙayyadewa a cikin tebur da aka shirya da kyau.
Darasi: Tsarin Yanayi a Excel
Hanyar 3: samfurin da dama yanayi ta amfani da tsari
Kamar dai lokacin amfani da tace, ta yin amfani da tsari, zaka iya samfurin da yawa yanayi. Alal misali, bari mu dauki ma'anar wannan maɓallin tushe, kazalika da tebur mai laushi inda za a nuna sakamakon, tare da yin fasali da ƙaddarar yanayin. Sanya iyakar iyaka zuwa iyakar ƙananan zaɓi na kudaden shiga na rubles 15,000, kuma yanayin na biyu shine iyakar ƙananan rubles 20,000.
- Mun shiga a cikin wani shafi na musamman don yanayin samfurin.
- Kamar yadda a cikin hanyar da aka rigaya, a zabi wani zaɓi ginshiƙai na sabon launi kuma shigar da matakai guda uku a cikinsu. A cikin shafi na farko shigar da fadin haka:
= INDEX (A2: A29; LOWEST (IF ((D $ D $ 2 = C2: C29); STRING (C2: C29); ""; STRING (C2: C29) - STRING ($ C $ 1) - STRING ($ C $ 1))A cikin ginshiƙai na gaba mun shigar da daidai wannan tsari, kawai ta hanyar canja canje-canje nan da nan bayan sunan mai aiki. INDEX zuwa ginshiƙai masu dacewa da muke bukata, ta hanyar kwatanta da hanyar da ta gabata.
Kowace lokaci bayan shigarwa kar ka manta da su rubuta maɓallan gajeren hanya Ctrl + Shigar + Shigar.
- Amfani da wannan hanya a kan abin da ya gabata shine cewa idan muna so mu canza samfurin ƙaddamarwa, to, ba za mu buƙaci canza canjin lissafi ba, wanda a kanta shi ne matsala. Ya isa ya canza lambobin iyaka a cikin shafi na yanayi a kan takardar zuwa ga waɗanda ake buƙatar mai amfani. Sakamakon zaɓin zai canza ta atomatik.
Hanyar 4: samfur samfurin
A cikin Excel tare da tsari na musamman SLCIS Za'a iya amfani da zaɓin zaɓi na asali. Ana buƙata a yi a wasu lokuta yayin yin aiki tare da bayanai masu yawa, lokacin da kake buƙatar gabatar da cikakken hoto ba tare da cikakken nazarin dukan bayanan da aka tsara ba.
- A gefen hagu na teburin, tsalle ɗaya shafi. A cikin tantanin sallar shafi na gaba, wanda yake akasin cell na farko tare da bayanan da ke cikin tebur, shigar da dabara:
= RAND ()Wannan aikin yana nuna lambar bazuwar. Domin kunna shi, danna kan maballin Shigar.
- Don yin cikakken shafi na lambobi bazuwar, saita siginan kwamfuta a cikin kusurwar dama na tantanin halitta, wanda ya riga ya ƙunshi wannan tsari. Alamar cika alama ta bayyana. Ɗauke shi tare da maɓallin linzamin hagu na gungume a layi daya tare da teburin tare da bayanai har zuwa ƙarshe.
- Yanzu muna da kewayon kwayoyin da aka cika da lambobi bazuwar. Amma, yana dauke da ma'anar SLCIS. Muna buƙatar aiki tare da dabi'u masu tsarki. Don yin wannan, kwafi zuwa shafi mara kyau a dama. Zaži kewayon Kwayoyin da lambobi bazuwar. Da yake cikin shafin "Gida", danna kan gunkin "Kwafi" a kan tef.
- Zaɓi maɓallin kyauta kuma danna tare da maɓallin linzamin linzamin dama, yana kira cikin menu mahallin. A cikin ƙungiyar kayan aiki "Zaɓuɓɓukan Zaɓuka" zabi abu "Darajar"wanda aka nuna a matsayin hoton da lambobi.
- Bayan haka, kasancewa a cikin shafin "Gida", danna kan gunkin da aka riga ya sani "Tsara da tace". A cikin jerin layi, dakatar da zaɓi a kan abu "Yanki na Custom".
- An kunna maɓallin saitunan saiti. Tabbatar duba akwatin kusa da saitin. "Bayanan na yana ƙunshe da maɓallai"idan akwai tafiya, amma babu alamar bincike. A cikin filin "Tsara ta" saka sunan mahaɗin da ya ƙunshi dabi'un da aka kofe na lambobi bazuwar. A cikin filin "A ware" bar tsarin saitunan. A cikin filin "Dokar" za ka iya zaɓar zaɓin a matsayin "Hawan"haka kuma "Saukowa". Don samfurin bazuwar, wannan ba kome ba ne. Bayan an yi saitunan, danna maɓallin. "Ok".
- Bayan haka, duk dabi'u na teburin an shirya su a hawan hawa ko saukarwa na lambobin bazuwar. Zaka iya ɗaukar wasu layi na farko daga tebur (5, 10, 12, 15, da dai sauransu) kuma ana iya la'akari da sakamakon samfurin bazuwar.
Darasi: Kashe da tace bayanai a Excel
Kamar yadda kake gani, ana iya yin samfurin a cikin zane na Excel, tare da taimakon tace ta atomatik, da kuma yin amfani da tsari na musamman. A cikin akwati na farko, za a nuna sakamakon a cikin tebur na asali, kuma a na biyu - a cikin wani wuri dabam. Akwai damar da za a zabi, duka a kan yanayin daya, da kuma a da yawa. Bugu da ƙari, za ka iya yin samfur samfurin ta hanyar amfani da aikin SLCIS.
