Pogwira ntchito ndi matebulo a Excel, nthawi zambiri mumayenera kuwasankha malinga ndi mtundu wina kapena malinga ndi machitidwe ena. Pulogalamuyi itha kuchita izi m'njira zosiyanasiyana pogwiritsa ntchito zida zingapo. Tiyeni tiwone momwe mungasinthire ku Excel pogwiritsa ntchito njira zosiyanasiyana.
Kusintha
Kusankhidwa kwa deta kumakhala mumachitidwe osankhidwa kuchokera pazotsatira zingapo zomwe zimakwaniritsa zomwe zapatsidwa, ndikutulutsa kwawo pamapepala ngati mndandanda wapadera kapena mumtundu woyambira.
Njira 1: gwiritsani ntchito autofilter yapamwamba
Njira yosavuta yopangira kusankha ndi kugwiritsa ntchito autofilter yapamwamba. Ganizirani momwe mungachitire izi ndi chitsanzo china.
- Sankhani malo omwe ali patsamba, pakati pa zomwe mukufuna kusankha. Pa tabu "Pofikira" dinani batani Sanjani ndi Fyuluta. Imapezeka m'malo osungirako. "Kusintha". Pamndandanda womwe umatseguka pambuyo pa izi, dinani batani "Zosefera".
Pali mwayi wochita mosiyana. Kuti muchite izi, mutasankha m'deralo pepala, sinthani ku tabu "Zambiri". Dinani batani "Zosefera"yomwe imayikidwa pa tepi mgululi Sanjani ndi Fyuluta.
- Pambuyo pa izi, zithunzi-thunzi zimawonekera kumutu kwa tebulo kuyamba kupanga zosefera mwanjira zazing'onoting'ono zazing'ono zomwe zimatembenuzidwa mozungulira kumanja kwa maselo. Timadulira chithunzichi pamutu wakomwe tikufuna kusankha. Pazosankha zomwe zimatseguka, pitani ku chinthucho "Zosefera Zolemba". Kenako, sankhani "Zosefera mwachikhalidwe ...".
- Zosefera la wogwiritsa ntchito limayambitsa. Mmenemo, mutha kukhazikitsa malire omwe angasankhidwe. Pamndandanda wotsika pamzere womwe uli ndi maselo amitundu yomwe timagwiritsa ntchito mwachitsanzo, mutha kusankha imodzi mwazinthu zisanu:
- wofanana ndi;
- osofanana;
- zambiri;
- ochulukirapo kapena ofanana;
- zochepa.
Tikhazikitse chitsanzo ngati momwe zingakhalire kuti tisankhe zokhazo zomwe ndalama zomwe zimaposa 10,000 ruble. Khazikitsani kusintha Zambiri. Lowetsani mtengo m'munda woyenera "10000". Kuti muchite kanthu, dinani batani "Zabwino".
- Monga mukuwonera, mutatha kusefa panali mizere yokha momwe kuchuluka kwa ndalama kumapitilira ma ruble 10,000.
- Koma mu mzere womwewo, titha kuwonjezera mkhalidwe wachiwiri. Kuti tichite izi, tibwereranso ku zosefera la wosuta. Monga mukuwonera, m'munsi mwake pamakhala kusintha kwina ndi gawo lolowera lolowera. Tiyeni tsopano tiike malire apamwamba osankhidwa pa ma ruble 15,000. Kuti muchite izi, ikani kusintha kosinthika Zochepa, ndipo m'munda kumanja timalowetsa mtengo "15000".
Kuphatikiza apo, palinso kusintha kwa zinthu. Ali ndi maudindo awiri "Ndipo" ndi "KAPENA". Mwachisawawa, imayikidwa pamalo oyamba. Izi zikutanthauza kuti mizere yokhayo yomwe ikukwaniritse ziletso zonsezo ndi yomwe ingatsalirebe muzolembedwayo. Ngati adzaikidwe "KAPENA"ndiye kuti padzakhala mfundo zomwe zingagwirizane ndi zonsezi. M'malo mwathu, muyenera kukhazikitsa switch "Ndipo", ndiye kuti, kusiya izi ndikusowa. Pambuyo kuti mfundo zonse zalowetsedwa, dinani batani "Zabwino".
- Tsopano patebulopo pali mizere yokha momwe kuchuluka kwa zoperekera sikumapitilira ma ruble 10,000, koma osapitilira ma ruble 15,000.
- Momwemonso, mutha kukhazikitsa zosefera mzere zina. Nthawi yomweyo, ndizotheka kusunga kusefa molingana ndi momwe zimakhalira m'mbuyomu. Chifukwa chake, tiwone momwe kusefukira kumachitikira kwa maselo mu mtundu wa deti. Dinani pazikwangwani zosewerera. Dinani mosiyanasiyana pazinthu zomwe zalembedwa "Zosefera patsiku" ndi Zosefera Mwambo.
- Windo laofayilo la ogwiritsa ntchito limayambiranso. Timapanga zosankha zotsatira mu tebulo kuyambira Meyi 4 mpaka Meyi 6, 2016 kuphatikiza. Pakusintha kwa masinthidwe, monga tikuonera, pali zosankha zambiri kuposa mtundu wamitundu. Sankhani malo "Pambuyo kapena zofanana". M'munda kumanja, ikani mtengo wake "04.05.2016". Pakatikati, ikani kusintha "Kwa kapena wofanana ndi". Lowetsani mtengo m'munda woyenera "06.05.2016". Tisiyira mawonekedwe osinthasintha - "Ndipo". Kuti mugwiritse ntchito kusefa pochita, dinani batani "Zabwino".
- Monga mukuwonera, mndandanda wathu wayambanso kuchepetsedwa. Tsopano mizere yatsala yokha mmenemu, momwe kuchuluka kwa zoperekera kumasiyana kuchokera ku 10,000 mpaka 15,000 rubles pazaka kuyambira Meyi 4 mpaka Meyi 6, 2016 kuphatikiza.
- Titha kubwezeretsa kusefa mu umodzi mwa mizati. Tichita izi pazotsatira zamalipiro. Dinani pa chithunzi cha autofilter pamzera wofanana. Pamndandanda wotsitsa, dinani chinthucho Chotsani Fyuluta.
- Monga mukuwonera, izi zitatha, kusankha pazosankha kudzakhala kolemala, ndipo zosankhidwa zokha ndizomwe zidzatsalira (kuyambira 05/04/2016 mpaka 05/06/2016).
- Pali gawo lina pagome lino - "Dzinalo". Ili ndi deta pamawonekedwe. Tiyeni tiwone momwe mungapangire kusankha pogwiritsa ntchito kusefera kwa mfundo izi.
Dinani pa chithunzi chojambula mu dzina la chipilala. Timadutsa mayina a mndandandandawo "Zosefera Zolemba" ndi "Zosefera mwachikhalidwe ...".
- Windo la wogwiritsa ntchito limatsegulanso. Tiyeni tisankhe pazinthu "Mbatata" ndi Nyama. Pachidutswa choyamba, sinthani kuti musinthe "Zofanana". M'munda kumanja kwake timalowa mawu "Mbatata". Chozungulira cha block block chimayikidwanso "Zofanana". M'munda wotsutsana nawo, lembani - Nyama. Ndipo timachita zomwe sitinachite m'mbuyomu: kukhazikitsa zikhalidwe "KAPENA". Tsopano chingwe chomwe chili ndi chilichonse mwatsatanetsatane chiziwonetsedwa pazenera. Dinani batani "Zabwino".
- Monga mukuwonera, patsamba latsopanolo muli malamulo oletsedwa (kuyambira pa 5/04/2016 mpaka pa 05/06/2006) ndi dzina (mbatata ndi nyama). Palibe zoletsa pazachuma.
- Mutha kuchotsa kwathunthu mu zosefera momwe mudakhazikitsira. Komanso, zilibe kanthu kuti ndi njira iti yomwe idagwiritsidwa ntchito. Kuyambitsanso kusefa, kukhala tabu "Zambiri" dinani batani "Zosefera"lomwe limayikidwa mgulu Sanjani ndi Fyuluta.
Njira yachiwiri ikuphatikizira kupita ku tabu "Pofikira". Pamenepo timadina batani pa riboni Sanjani ndi Fyuluta mu block "Kusintha". Pamndandanda wokhazikitsa, dinani batani "Zosefera".
Pogwiritsa ntchito imodzi mwanjira ziwiri zomwe zili pamwambapa, kusefa kumachotsedwa, ndipo zotsatira zake zidzasankhidwa. Ndiye kuti, tebulo likuwonetsa kuchuluka konse kwa deta yomwe ili nayo.
Phunziro: Ntchito ya Autofilter ku Excel
Njira 2: kutsatira njira zingapo
Mutha kupanga zosankha pogwiritsa ntchito njira zingapo. Mosiyana ndi zomwe zidachitika kale, njirayi imapereka chotsatiracho pazotsatira zawo.
- Pa pepala lomweli, pangani tebulo lopanda kanthu lomwe lili ndi mayina ammitu yomweyo ngati mutuwo.
- Sankhani maselo onse opanda kanthu patsamba loyamba la tebulo latsopano. Timaika tulo mu mzere wa njira. Pano pali chilinganizo chokhazikitsidwa chomwe chimapanga kusankha malinga ndi njira zomwe zidanenedwa. Timasankha mizere momwe kuchuluka kwa ndalama kumapitilira ma ruble 15,000. Pachitsanzo chathu, makulidwe amomwe mungawonekere awa:
= INDEX (A2: A29; LOW (IF (15000 <= C2: C29; STRING (C2: C29); ""); STRING () - STRING ($ C $ 1)) - STRING ($ C $ 1)Mwachilengedwe, m'malo onsewo, ma adilesi amaselo ndi magulu ake azikhala osiyanasiyana. Mu chitsanzo ichi, mutha kuyerekezera kachitidwe ndi zolumikizana mu fanizoli ndikuzisintha kuti zikwaniritse zosowa zanu.
- Popeza iyi ndi njira yosiyanasiyana, kuti muigwiritse ntchito, muyenera kukanikiza osati batani Lowani, ndi njira yachidule Ctrl + Shift + Lowani. Timachita.
- Kusankha mzere wachiwiri wokhala ndi madeti ndikuyika chidziwitso mu barula yokhazikitsira, timayambitsa mawu awa:
= INDEX (B2: B29; LOW (IF (15000 <= C2: C29; STRING (C2: C29); ""); STRING () - STRING ($ C $ 1)) - STRING ($ C $ 1)Kanikizani njira yachidule Ctrl + Shift + Lowani.
- Chimodzimodzinso, mzere wokhala ndi ndalama timalowa formula motere:
= INDEX (C2: C29; LOW (IF (15000 <= C2: C29; STRING (C2: C29); ""); STRING () - STRING ($ C $ 1)) - STRING ($ C $ 1)Apanso, kuyimira njira yaying'ono Ctrl + Shift + Lowani.
M'njira zonse zitatuzi, njira yoyamba yokha imagwirizira kusintha, ndipo mafomula ena onse ndi ofanana.
- Monga mukuwonera, tebulo limadzaza ndi deta, koma mawonekedwe ake siwokongola kwathunthu, kuwonjezera apo, matchulidwe a deti amadzazidwa molakwika. Muyenera kukonza zolakwika izi. Tsikuli silinali lolondola chifukwa mawonekedwe amtundu wa masanjidwewo ndiofala, ndipo tifunika kukhazikitsa mtundu wa deti. Sankhani gawo lonse, kuphatikiza maselo omwe ali ndi zolakwika, ndikudina kusankha ndikusankha mbewa. Pamndandanda womwe umawonekera, pitani "Mtundu wamtundu ...".
- Pazenera lopakika lomwe limatsegulira, tsegulani tabu "Chiwerengero". Mu block "Mawerengero Amanambala" sonyezani phindu Tsiku. Mu gawo loyenera la zenera, mutha kusankha mtundu womwe mukufuna. Pambuyo pazikhazikitsidwa, dinani batani "Zabwino".
- Tsopano tsikulo likuwonetsedwa molondola. Koma, monga tikuwona, gawo lonse lakumunsi kwa tebulo limadzaza ndi maselo omwe ali ndi mtengo wolakwika "# NUMBER!". M'malo mwake, awa ndi maselo omwe sanakhale ndi deta yokwanira kuchokera pachitsanzo. Zingakhale zokongola kwambiri ngati ziwonetsedwa zopanda kanthu. Pazifukwa izi tidzagwiritsa ntchito mitundu yozikika. Sankhani maselo onse patebulopo kupatula mutu. Kukhala mu tabu "Pofikira" dinani batani Njira Zakukonzeraniili mu chipangizo Masitaelo. Pamndandanda womwe umawonekera, sankhani "Pangani lamulo ...".
- Pazenera lomwe limatsegulira, sankhani mtundu wa ulamuliro "Maselo okhawo omwe ali ndi". Mu bokosi loyamba lolemba "Maselo okhazikika omwe zotsatirazi ndi zoona" sankhani "Zolakwa". Kenako, dinani batani "Fomu ...".
- Pazenera loyambitsa lomwe limayamba, pitani tabu Font m'munda wolingana, sankhani zoyera. Pambuyo pa izi, dinani batani "Zabwino".
- Dinani batani ndi dzina lomweli mutatha kubwerera pazenera kuti mupange zinthu.
Tsopano tili ndi zitsanzo zopangidwa zokhazikitsidwa ndi tebulo lina lopangidwa bwino.
Phunziro: Zowongolera mu Excel
Njira 3: zitsanzo molingana ndi machitidwe angapo pogwiritsa ntchito kachitidwe
Monga momwe mumagwiritsira ntchito fyuluta, pogwiritsa ntchito fomula, mutha kusankha molingana ndi machitidwe angapo. Mwachitsanzo titenga tebulo lonse lomwelo, komanso gome lopanda pake pomwe zotsatira zikuwonetsedwa, ndikuyika kale mawonekedwe ndi mawonekedwe. Takhazikitsa malire oyamba osankha osankha ndalama za ma ruble 15,000, ndipo mkhalidwe wachiwiri mpaka malire apamwamba a ma ruble 20,000.
- Timalowetsa malire a kusankha kwa gawo lina.
- Monga momwe tinapangira m'mbuyomu, timasankha mizati yopanda kanthu ya thebulo latsopano limodzi ndikulowetsa mitundu itatu mwa iyo. M'danga loyamba, onjezani mawu otsatirawa:
= INDEX (A2: A29; LOW (IF (($ D $ 2 = C2: C29); LINE (C2: C29); ""); LINE (C2: C29) -LINE ($ C $ 1) - LINE ($ C $ 1))M'mizere yotsatirayi, timalemba zofananira ndendende, tikungosintha magwirizanowu kutangotchulidwa dzina la wothandizira INDEX kwa mizere yolingana yomwe tikufuna, mwa kufanizira ndi njira yapita.
Nthawi iliyonse mutalowa, musaiwale kuphatikiza kiyi Ctrl + Shift + Lowani.
- Ubwino wa njirayi pamwambapa ndiwakuti ngati tikufuna kusintha malire, sitingafunike kusintha njira yokhayokha, yomwe imakhala yovuta palokha. Ndikokwanira pamndandanda wa zinthu papepala kuti musinthe manambala a malire ndi omwe wosuta afunikira. Zotsatira zake zidzasinthidwa nthawi yomweyo.
Njira 4: zosintha mwachisawawa
Mu Excel pogwiritsa ntchito njira yapadera ZINTHA kusankha mwachisawawa kungagwiritsidwenso ntchito. Iyenera kupangidwa nthawi zina pogwira ntchito ndi kuchuluka kwa deta, pakakhala kofunikira kupereka chithunzithunzi popanda kusanthula kwathunthu kwa deta yonseyo pamndandanda.
- Kumanzere kwa tebulo timadumphadumpha chipilala chimodzi. Mu chipinda chotsatira, chomwe chiri moyang'anizana ndi chipinda choyamba ndi zomwe zili patebulopo, tikuyika chilinganizo:
= RAND ()Ntchitoyi imawonetsa nambala yosasokoneza. Pofuna kutsegula, dinani batani ENG.
- Kuti mupange gulu lonse la manambala osasinthika, ikani pomwepo pakona ya kumunsi kwa foni yomwe ili ndi fomuloli. Chizindikiro Timakokera pansi ndi batani lakumanzere lomwe limakanikizidwa limodzi ndi tebulo la deta mpaka kumapeto.
- Tsopano tili ndi maselo osiyanasiyana okhala ndi manambala osasintha. Koma, ili ndi kachitidwe ZINTHA. Tiyenera kugwira ntchito ndi malingaliro abwino. Kuti muchite izi, koperani kumizere yopanda kumanja. Sankhani maselo osiyanasiyana ndi nambala zosasankha. Ali pa tabu "Pofikira"dinani pachizindikiro Copy pa tepi.
- Sankhani mzere wopanda kanthu ndikudina kumanja, ndikusintha menyu yankhaniyo. Mu gulu lazida Ikani Zosankha sankhani "Makhalidwe"chikuwonetsedwa ngati chithunzi ndi manambala.
- Pambuyo pake, kukhala mu tabu "Pofikira", dinani chizindikiro chomwe tikudziwa kale Sanjani ndi Fyuluta. Pamndandanda wotsitsa, siyani kusankha pa Makonda.
- Zenera losintha limayambitsidwa. Onetsetsani kuti mwayang'ana bokosi pafupi ndi gawo "Zambiri zanga zimakhala ndi mutu"ngati pali chipewa koma palibe cheke. M'munda Longosolani sonyezani dzina la mzere womwe uli ndi ziwonetsero zomwe zatsatidwa za manambala osasinthika. M'munda "Sinthani" siyani makonda osasintha. M'munda "Order" mutha kusankha parameta ngati "Ndikukwera"choncho ndi "Kuchotsa". Kwa zitsanzo zosasinthika, izi zilibe kanthu. Masanjidwewo atapangidwa, dinani batani "Zabwino".
- Zitatha izi, mfundo zonse za patebulopo zimakonzedwa kuti zikwere kapena kutsika kwa manambala. Mutha kutenga chiwerengero chilichonse cha mizere yoyambirira kuchokera pagome (5, 10, 12, 15, etc.) ndipo zitha kuonedwa ngati zotulukapo zosasinthika.
Phunziro: Sanjani ndi kusefa deta mu Excel
Monga mukuwonera, kusankhidwa mu pulogalamu yowonjezera ya Excel kutha kuchitika pogwiritsa ntchito autofilter kapena kugwiritsa ntchito njira zapadera. Poyambirira, zotsatira zake zidzawonetsedwa mu tebulo loyambirira, ndipo lachiwiri - m'malo ena. Ndizotheka kusankha, pa chinthu chimodzi komanso zingapo. Muthanso kusankha mwachisawawa pogwiritsa ntchito ntchitoyo ZINTHA.
