Tsegulani mafayilo ndi extension XMCD

Pamene mukugwira ntchito ndi matebulo a Excel, kawirikawiri ndi kofunika kuti muzisankhe mogwirizana ndi nthawi inayake kapena pazinthu zingapo. Pulogalamuyi ikhoza kuchita izi m'njira zosiyanasiyana pogwiritsa ntchito zida zingapo. Tiyeni tione momwe tingaperekere mu Excel pogwiritsa ntchito njira zosiyanasiyana.

Sampling

Dongosolo la deta liri mu ndondomeko yosankhidwa kuchokera ku zotsatira zambiri zomwe zimakhutiritsa mikhalidweyo, ndi zotsatira zake zomwe zimatuluka pa pepala mu mndandanda wosiyana kapena muyeso loyamba.

Njira 1: gwiritsani ntchito maofesi apamwamba

Njira yosavuta yosankha ndi kugwiritsa ntchito autofilter yapamwamba. Lingalirani momwe mungachitire izi ndi chitsanzo chapadera.

1. Sankhani malo pa pepala, pakati pa deta yomwe mukufuna kufufuza. Mu tab "Kunyumba" dinani pa batani "Sankhani ndi kusefera". Imaikidwa pazomwe zimakonzedwa. Kusintha. M'ndandanda yomwe imatsegulira pambuyo pa izi, dinani pa batani. "Fyuluta".

   

   N'zotheka kuchita mosiyana. Kuti muchite izi, mutasankha malo pa pepala, pita ku tabu "Deta". Dinani pa batani "Fyuluta"zomwe zaikidwa pa tepi mu gulu "Sankhani ndi kusefera".



2. Zitatha izi, zizindikiro zikuwonekera patebulo lomwe likuyamba kuyamba mu mawonekedwe a zing'onozing'ono zing'onozing'ono zomwe zinatembenuzidwira kumbali yeniyeni ya maselo. Dinani pa chithunzi ichi pamutu wa ndime yomwe tikufuna kusankha. Poyambitsa menyu, dinani pa chinthucho "Zosintha Zamalemba". Kenako, sankhani malo "Fyuluta yamtundu ...".



3. Zowonetsera fayilo yowonekera. N'zotheka kukhazikitsa malire omwe kusankha kudzapangidwe. Mndandanda wotsika pansi pa mzere umene uli ndi maselo a ma nomero, omwe timagwiritsa ntchito monga chitsanzo, mungasankhe chimodzi mwa mitundu zisanu:
   • chofanana;
   • osati wofanana;
   • zambiri;
   • wamkulu kapena wofanana;
   • zochepa

   Tiyeni tiike chikhalidwe monga chitsanzo kotero kuti tikhoza kusankha zokhazo zomwe ndalamazo zimaposa ma ruble 10,000. Ikani kusinthana kuti mukhale malo "Zambiri". Lowani mtengo mu malire abwino "10000". Kuti muchite kanthu, dinani pa batani. "Chabwino".



4. Monga mukuonera, mutatha kufuta, pali mzere wokha womwe ndalama zopezera ndalama zoposa 10 rubles.



5. Koma mu ndime yomweyo tikhoza kuwonjezera chikhalidwe chachiwiri. Kuti muchite izi, bwererani kuwindo la fyuluta. Monga momwe mukuonera, m'munsi mwake pali kusintha kwina ndi malo omwe akutsatiridwa. Tiyeni tsopano tiike malire osankhidwa apamwamba a ruble 15,000. Kuti muchite izi, yesani kusinthasintha "Zochepa", ndi kumunda kuti mulowe muyeso "15000".

   Kuphatikizanso apo, pali kusintha kosinthika. Ali ndi malo awiri "Ndipo" ndi "OR". Mwachikhazikitso chayikidwa ku malo oyambirira. Izi zikutanthauza kuti mzere wokha umene umakhutitsa zovuta ziwirizo zidzakhalabe mu chisankho. Ngati atayikidwa "OR", pamenepo padzakhala zikhalidwe zomwe ziri zoyenera pazochitika ziwirizo. Kwa ife, muyenera kuyika kusintha "Ndipo", ndiko kuti, chotsani chosasintha ichi. Zonsezi zitatha, dinani pa batani. "Chabwino".



6. Tsopano tebulo ili ndi mizere yokha yomwe ndalamazo sizinapitirira 10,000 rubles, koma siziposa 15,000 rubles.



7. Mofananamo, mungathe kukonza zojambulidwa muzitsulo zina. Panthawi imodzimodziyo, ndizotheka kusunga fyuluta ndi zochitika zomwe zafotokozedwa muzitsulo. Kotero, tiyeni tiwone momwe kusankha kumapangidwira kupyolera mu fyuluta ya maselo mu mtundu wa tsiku. Dinani pa chithunzi cha fyuluta muzolumikizana. Sakanizani sequentially pa zinthu zomwe zili m'ndandanda. "Sakanizani ndi tsiku" ndi "Fyuluta Yokonda".



8. Fayilo loyendetsa maulendo akuyambanso. Chitani zotsatira pazomwezi kuchokera pa 4 mpaka 6 May 2016 kuphatikizapo. Mukasankha kusinthana, monga momwe mukuonera, pali zowonjezereka kuposa maonekedwe a chiwerengero. Sankhani malo "Pambuyo Pena". Kumunda kumanja, ikani mtengo "04.05.2016". M'munsimu, perekani kusinthana kumalo "Kwa kapena wofanana ndi". Lowani mtengo mu malo abwino "06.05.2016". Mkhalidwe wotsatizana wotsalira umasiyidwa mu malo osasinthika - "Ndipo". Kuti mugwiritse ntchito kusinthasintha, dinani pa batani "Chabwino".



9. Monga momwe mukuonera, mndandanda wathu wawonongeka kwambiri. Tsopano mzere wokha umasiyidwa mmenemo, momwe ndalamazo zimakhala zosiyana kuchokera ku 10,000 mpaka 15,000 rubles kuyambira nthawi ya 04.05 mpaka 06.05.2016 kuphatikizapo.



10. Tikhoza kukhazikitsanso fyuluta imodzi mwazitsulo. Chitani izi pamtengo wapatali. Dinani pa chithunzi cha autofilter mumphindi yofanana. M'ndandanda wotsika pansi, dinani pa chinthucho. "Chotsani Fyuluta".



11. Monga mukuonera, mutatha zotsatirazi, zitsanzo za ndalamazo zidzakhumudwa, ndipo chisankho chokha chidzatsala (kuyambira 04.05.2016 mpaka 06.05.2016).



12. Tebulo ili liri ndi mzere wina - "Dzina". Lili ndi deta m'mafomu. Tiyeni tiwone momwe tingapangire chitsanzo pogwiritsa ntchito mfundo izi.

    Dinani pa chithunzi cha fyuluta mu dzina lachindunji. Squentially kudutsa mndandanda "Zosintha Zamalemba" ndi "Fyuluta yamtundu ...".



13. Fayilo loyendetsa wothandizira limatsegulanso. Tiyeni tichite chitsanzo mwa dzina. "Mbatata" ndi "Nyama". Pachiyambi choyamba, mawonekedwe akusintha "Ofanana". Kumunda mpaka kumanja kwa iye kulowa mawu "Mbatata". Kusintha kwa m'munsimu kumaikapo malo "Ofanana". M'munda moyang'anizana naye timapanga zolowera - "Nyama". Ndiyeno timachita zomwe sitinachitepo kale: timayika mawonekedwe oyenerera kumalo "OR". Tsopano mzere umene uli ndi zikhalidwe zilizonse zomwe zafotokozedwa zidzawonetsedwa pawindo. Dinani pa batani "Chabwino".



14. Monga mukuonera, mu zitsanzo zatsopano pali zolephera pa tsiku (kuyambira 04/05/2016 mpaka 05/06/2016) ndi dzina (mbatata ndi nyama). Palibe malire pa kuchuluka kwa ndalama.



15. Mukhoza kuchotsa zonsezo fyuluta pogwiritsira ntchito njira zomwezo zomwe zinagwiritsidwa ntchito kuti ziyike. Ndipo ziribe kanthu njira yomwe imagwiritsidwira ntchito. Kuti muwezeretsenso kusinthasintha, pokhala mu tab "Deta" dinani pa batani "Fyuluta"zomwe zimagwidwa mu gulu "Sankhani ndi kusefera".

    

    Njira yachiwiri imaphatikizapo kusinthasintha pa tabu "Kunyumba". Kumeneko timakanikiza pa riboni pa batani. "Sankhani ndi kusefera" mu block Kusintha. Mu mndandanda womwe wasinthidwa dinani pa batani. "Fyuluta".

Mukamagwiritsa ntchito njira ziwirizi pamwambazi, kufutukula kudzachotsedwa, ndipo zotsatira za chitsanzozo zidzasulidwa. Ndiko kuti, tebulo liwonetseratu zonse zomwe zilipo.

Phunziro: Kusakaniza koyendetsa ntchito mu Excel

Njira 2: Gwiritsani ntchito Mzere Wolemba

Mukhozanso kusankha kusankha kugwiritsa ntchito njira yowonjezereka. Mosiyana ndi Baibulo lapitalo, njira iyi imapereka zotsatira za zotsatirapo mu tebulo lapadera.

1. Pa pepala lomwelo, pangani tebulo lopanda kanthu ndi malemba omwewo pamutu monga chikhombo.



2. Sankhani maselo opanda kanthu omwe ali m'ndandanda yoyamba ya tebulo latsopano. Ikani cholozera mu bar. Pano ndondomekoyi idzalembedwera, sampuli molingana ndi ndondomeko yoyenera. Tidzasankha mizere, kuchuluka kwa ndalama zomwe zimapitirira 15,000 rubles. Mu chitsanzo chathu chokha, njira yomwe mumalowayo idzawoneka ngati iyi:

   = INDEX (A2: A29; LOWEST (IF (15000 <= C2: C29; STRING (C2: C29); ""); STRING () - STRING ($ C $ 1)) - STRING ($ C $ 1))

   Mwachidziwikire, pambali iliyonse adiresi ya maselo ndi mitsinje adzakhala osiyana. Mu chitsanzo ichi, mukhoza kufanizitsa ndondomekoyi ndi zigawo zomwe zili m'fanizoli ndikuzisintha mogwirizana ndi zosowa zanu.



3. Popeza ichi ndi ndondomeko yambiri, kuti muigwiritse ntchito, simukulimbana ndi batani Lowanindi njira yachinsinsi Ctrl + Shift + Lowani. Ife timachita izo.



4. Kusankha ndime yachiwiri ndi tanthauzo ndikuyika cholozera mu barra yolozera, lowetsani mawu awa:

   = INDEX (B2: B29; LOWEST (IF (15000 <= C2: C29; STRING (C2: C29); ""); STRING () - STRING ($ C $ 1)) - STRING ($ C $ 1))

   Ikani njira yomasulira Ctrl + Shift + Lowani.



5. Mofananamo, mu gawoli ndi ndalama zomwe timalowa mu njirayi:

   = INDEX (C2: C29; LOWEST (IF (15000 <= C2: C29; STRING (C2: C29); ""); STRING () - STRING ($ C $ 1)) - STRING ($ C $ 1))

   Apanso, timayimitsa njirayo Ctrl + Shift + Lowani.

   Pazochitika zonse zitatu, chofunika choyamba cha ma coordination chimasintha, ndipo zina zonsezi ndizofanana.



6. Monga mukuonera, tebulo ili ndi deta, koma maonekedwe ake sali okongola, pambali pake, chiwerengero cha tsiku chimadzazidwa molakwika. Ndikofunika kukonza zolephera izi. Tsiku losavomerezeka likuchitika chifukwa chakuti mawonekedwe a maselo omwe ali pamtundu wofanana ndi wamba, ndipo tifunika kukhazikitsa tsikulo. Sankhani maulendo onse, kuphatikizapo maselo omwe ali ndi zolakwika, ndipo dinani kusankha ndi batani labwino la mouse. Mndandanda umene umapezeka pa chinthucho "Maselo ...".



7. Muwindo lokhazikitsa lomwe limatsegula, tsegula tabu "Nambala". Mu chipika "Maofomu Owerengeka" sankhani mtengo "Tsiku". Kumanja komwe pawindo, mungasankhe mtundu wa tsiku lomwe mukufuna. Pambuyo pokonzekera, panikizani pa batani. "Chabwino".



8. Tsopano tsikulo likuwonetsedwa molondola. Koma, monga momwe mukuonera, pansi ponse pa tebulo ili ndi maselo omwe ali ndi phindu lolakwika. "#NUM!". Ndipotu, awa ndiwo maselo omwe alibe deta yokwanira kuchokera ku chitsanzo. Zingakhale zokopa ngati ziwonetsedweratu zopanda kanthu. Pa zolinga izi, timagwiritsa ntchito maonekedwe ovomerezeka. Sankhani maselo onse patebulo kupatula mutu. Kukhala mu tab "Kunyumba" dinani pa batani "Mafomu Okhazikika"zomwe ziri mu chida cha zipangizo "Masitala". Mundandanda womwe ukuwonekera, sankhani chinthucho "Pangani malamulo ...".



9. Pawindo limene limatsegulira, sankhani mtundu wa ulamuliro "Pangani maselo okhawo omwe ali". M'munda woyamba pansi pa kulembedwa "Pangani maselo okha omwe chikhalidwechi chikutsatiridwa" sankhani malo "Zolakwika". Kenako, dinani pakani "Format ...".



10. Muwindo lokhazikitsa lomwe limatsegula, pitani ku tabu "Mawu" ndipo sankhani mtundu woyera mu malo omwewo. Zitatha izi, dinani pa batani. "Chabwino".



11. Dinani pa batani ndi dzina lenileni lomwe mutabwerera ku zenera.

Tsopano ife tiri ndi chokonzekera chokonzekera kwachitsulo choyikidwa mu tebulo lopangidwa bwino lomwe.

Phunziro: Mafomu omvera mu Excel

Njira 3: Zitsanzo ndi zochitika zambiri pogwiritsa ntchito njirayi

Monga momwe mukugwiritsira ntchito fyuluta, pogwiritsira ntchito ndondomekoyi, mukhoza kuyesa ndi zifukwa zingapo. Mwachitsanzo, tiyeni titenge tebulo limodzi lokha, komanso tebulo lopanda kanthu komwe zotsatira zidzasonyezedwe, ndi zolemba zomwe zilipo kale ndi zovomerezeka. Ikani malire oyamba kumapeto kwazomwe mwasankhidwa kuti mupeze ndalama zoposa 15,000 rubles, ndipo chachiwiri ndikumapeto kwake kwa ma ruble 20,000.

1. Ife timalowa mu gawo losiyana gawo la chitsanzo.



2. Mofanana ndi njira yapitayi, musankhe mosasankha mazenera opanda kanthu a tebulo latsopano ndi kulowetsani mafomu atatu ofanana nawo. Mu ndime yoyamba alowetsani mawu otsatirawa:

   = INDEX (A2: A29; LOWEST (IF (($ D $ 2 = C2: C29); STRING (C2: C29); ""); STRING (C2: C29) - STRING ($ C $ 1)) - STRING ($ C $ 1))

   Muzitsulo zomwe timatsatira timalowa momwemonso, pokhapokha mutasintha makonzedwe mwamsanga pambuyo pa dzina la woyendetsa. INDEX ku mazenera omwe tikufunikira, pofanana ndi njira yapitayi.

   Nthawi iliyonse mutalowa musaiwale kuti muyimitse makiyi afupikitsidwe Ctrl + Shift + Lowani.



3. Kupindula kwa njira iyi kuposa kale lomwe ndikuti ngati tikufuna kusintha malire a zitsanzo, ndiye kuti sitidzasintha ndondomeko yowonjezera yomweyi, yomwe ili yovuta kwambiri. Zokwanira kusintha nambala ya malire m'ndondomeko ya zinthu pa pepala kwa zomwe wogwiritsa ntchito amafunikira. Zotsatira zosankha zidzasintha mosavuta.

Njira 4: Sampulumu yosasintha

Mu Excel ndi machitidwe apadera SLCIS Kusankhidwa kosasankhidwa kungagwiritsidwe ntchito. Zimayenera kupangidwa nthawi zina pamene mukugwira ntchito ndi deta yambiri, pamene mukufunikira kufotokoza chithunzi chonse popanda kufufuza bwinobwino deta yonseyi.

1. Kumanzere kwa gome, tambani mzere umodzi. Mu selo la ndime yotsatira, yomwe ili moyang'anizana ndi selo yoyamba ndi data mu tebulo, lowetsani ndondomekoyi:

   = RAND ()

   Ntchitoyi ikuwonetsa nambala yosasintha. Kuti mutsegule, dinani pa batani ENTER.



2. Kuti mupange chiwerengero chonse cha manambala osasinthasintha, ikani cholozera kumbali ya kumanja ya selo, yomwe ili ndi kalembedwe. Chizindikiro chodzaza chikuwonekera. Lembani pansi ndi batani lamanzere lomwe likugwirizanitsidwa ku tebulo ndi deta mpaka kumapeto kwake.



3. Tsopano tili ndi maselo osiyanasiyana omwe amadzala ndi manambala osasintha. Koma, liri ndi ndondomeko SLCIS. Tiyenera kugwira ntchito ndi zoyenera. Kuti muchite izi, lembani ku gawo lopanda kanthu kumanja. Sankhani magulu osiyanasiyana a maselo omwe ali ndi manambala osasintha. Ili pa tabu "Kunyumba", dinani pazithunzi "Kopani" pa tepi.



4. Sankhani ndondomeko yopanda kanthu ndipo dinani ndi batani labwino la mouse, ndikulowetsa mndandanda wa masewera. Mu gulu la zida "Njira Zowonjezera" sankhani chinthu "Makhalidwe"imasonyezedwa ngati pictogram ndi manambala.



5. Pambuyo pake, pokhala pa tab "Kunyumba", dinani pachithunzi chodziwika kale "Sankhani ndi kusefera". Mundandanda wotsika pansi, lekani kusankha pa chinthucho "Yambani Mwambo".



6. Mawindo okonzekera akusinthidwa. Onetsetsani kuti muyang'ane bokosi pafupi ndi parameter. "Deta yanga ili ndi mutu"ngati pali kapu, koma palibe chizindikiro. Kumunda "Sankhani" tchulani dzina la chigawo chomwe chili ndi ziwerengero zokopera za nambala zopanda pake. Kumunda "Sungani" chotsani zosinthika zosasinthika. Kumunda "Dongosolo" mukhoza kusankha njira monga "Akukwera"kotero ndi "Akukwera". Chifukwa cha chitsanzo chosavuta, izi sizilibe kanthu. Pambuyo mapangidwe apangidwa, dinani pa batani. "Chabwino".



7. Pambuyo pake, malingaliro onse a tebulo akukonzekera kukwera kapena kutsika kwa mawerengedwe osawerengeka. Mukhoza kutenga mizere yoyamba kuchokera pa tebulo (5, 10, 12, 15, ndi zina zotero) ndipo iwo akhoza kuonedwa kuti ndi zotsatira za zitsanzo zosadziwika.

Phunziro: Sakanizani ndi kusinkhira deta mu Excel

Monga momwe mukuonera, chitsanzo mu Excel spreadsheet chikhoza kupangidwa, monga ndi chithandizo cha galimoto yosungira, ndi kugwiritsa ntchito mayina apadera. Pachiyambi choyamba, zotsatira zake zidzawonetsedwa mu tebulo lapachiyambi, ndipo lachiwiri - m'dera losiyana. Pali mwayi wopanga zosankha, zonse pa chikhalidwe chimodzi, ndi angapo. Kuphatikizanso apo, mukhoza kuchita sampuli zosasintha pogwiritsa ntchito ntchitoyi SLCIS.