Zamkatimu
Dzulo pa marathon 30 Excel imagwira ntchito m'masiku 30 tinapeza tsatanetsatane wa malo athu ogwirira ntchito ndi ntchitoyi Info (INFORM) ndipo adapeza kuti sangathenso kutithandiza pazovuta zamakumbukiro. Ngakhale zathu, kapena kukumbukira kwa Excel!
Pa tsiku lachisanu la marathon, tidzaphunzira ntchitoyi SANKHA (KUSANKHA). Ntchitoyi ndi ya gulu References ndi magulu, imabwezera mtengo kuchokera pamndandanda wa zosankha zomwe zingatheke malinga ndi ndondomeko ya manambala. Dziwani kuti nthawi zambiri ndi bwino kusankha ntchito ina, mwachitsanzo, INDEX (INDEX) ndi GANIZANI (Zavumbulutsidwa ZAMBIRI) kapena VLOOKUP (VPR). Tikambirana izi pambuyo pake mu mpikisano wothamangawu.
Kotero, tiyeni titembenuzire ku zambiri zomwe tiri nazo ndi zitsanzo za ntchitoyi SANKHA (KUSANKHA), tiyeni tichiwone mukuchita, komanso tizindikire zofooka zake. Ngati muli ndi maupangiri ena ndi zitsanzo za izi, chonde gawanani nawo mu ndemanga.
Ntchito 05: SKHANI
ntchito SANKHA (SAKHANI) imabweretsa mtengo kuchokera pamndandanda, ndikusankha molingana ndi index ya manambala.
Kodi mungagwiritse ntchito bwanji ntchito ya CHOOSE?
ntchito SANKHA (SAKHANI) ikhoza kubweza chinthucho pamndandanda pa nambala inayake, monga chonchi:
- Potengera nambala ya mwezi, bweretsani nambala ya kotala yandalama.
- Kutengera tsiku loyambira, werengerani tsiku la Lolemba lotsatira.
- Ndi nambala ya sitolo, onetsani kuchuluka kwa malonda.
Syntax SINANI
ntchito SANKHA (SELECT) ili ndi mawu awa:
CHOOSE(index_num,value1,value2,…)
ВЫБОР(номер_индекса;значение1;значение2;…)
- index_nambala (index_nambala) iyenera kukhala pakati pa 1 ndi 254 (kapena 1 mpaka 29 mu Excel 2003 ndi koyambirira).
- index_nambala (index_nambala) ikhoza kulowetsedwa mu ntchito ngati nambala, chilinganizo, kapena kutchula selo lina.
- index_nambala (index_nambala) idzafupikitsidwa mpaka nambala yapafupi.
- mfundo mtengo (mtengo) ukhoza kukhala manambala, maumboni a ma cell, magawo otchulidwa, ntchito, kapena zolemba.
Misampha SAKHANI (KUSANKHA)
Mu Excel 2003 ndi kale, ntchito SANKHA (SAKHANI) idathandizira mfundo 29 zokha mtengo (kutanthauza).
Ndikwabwino kwambiri kufufuza mndandanda womwe uli patsamba lantchito kusiyana ndi kulemba zinthu zonse mu fomula. Ndi ntchito VLOOKUP (VLOOKUP) kapena GANIZANI (MATCH) Mutha kuloza pamndandanda wamakhalidwe omwe ali mu Excel worksheets.
Chitsanzo 1: Malipiro a mwezi uliwonse
ntchito SANKHA (SAKHANI) imagwira ntchito bwino ndi mindandanda yosavuta ya manambala ngati ma mtengo. Mwachitsanzo, ngati selo B2 ili ndi nambala ya mwezi, ntchitoyo SANKHA (SAKHANI) akhoza kuwerengera kuti ndi gawo liti lachuma. Mu chitsanzo chotsatira, chaka chachuma chimayamba mu Julayi.
Fomuyi imatchula mfundo za 12 zomwe zimagwirizana ndi miyezi 1 mpaka 12. Chaka chachuma chimayamba mu July, kotero miyezi 7, 8, ndi 9 imagwera m'gawo loyamba. Pa tebulo ili m'munsimu, mukhoza kuwona nambala ya kotala ya ndalama pansi pa nambala ya mwezi uliwonse.
Kugwira ntchito SANKHA (Sankhani) Nambala ya kotala iyenera kulowetsedwa mu dongosolo lomwe likuwonekera patebulo. Mwachitsanzo, mu mndandanda wa ntchito zofunika SANKHA (SANKHAni) m'malo 7, 8 ndi 9 (July, August ndi September) ayenera kukhala nambala 1.
=CHOOSE(C2,3,3,3,4,4,4,1,1,1,2,2,2)
=ВЫБОР(C2;2;3;3;3;4;4;4;1;1;1;2;2;2)
Lowetsani nambala ya mwezi mu selo C2, ndi ntchito SANKHA (Sankhani) idzawerengera nambala ya kotala yachuma mu selo C3.
Chitsanzo 2: Werengani tsiku Lolemba likubwerali
ntchito SANKHA (SAKHANI) imatha kugwira ntchito limodzi ndi ntchitoyi MASabata (DAYWEEK) kuti muwerengere masiku amtsogolo. Mwachitsanzo, ngati ndinu membala wa kalabu yomwe imakumana Lolemba lililonse madzulo, ndiye podziwa tsiku la lero, mutha kuwerengera tsiku Lolemba lotsatira.
Chithunzi chomwe chili pansipa chikuwonetsa manambala amtundu wa tsiku lililonse la sabata. Mzere H wa tsiku lililonse la sabata uli ndi chiwerengero cha masiku oti muwonjezere ku tsiku lomwe liripo kuti mupeze Lolemba lotsatira. Mwachitsanzo, muyenera kuwonjezera tsiku limodzi ku Lamlungu. Ndipo ngati lero ndi Lolemba, ndiye kuti patsala masiku asanu ndi awiri kufikira Lolemba lotsatira.
Ngati tsiku lapano lili mu selo C2, ndiye kuti fomula mu selo C3 imagwiritsa ntchito ntchitoyi MASabata (TSIKU) ndi SANKHA (SANKHANI) kuti muwerenge tsiku la Lolemba lotsatira.
=C2+CHOOSE(WEEKDAY(C2),1,7,6,5,4,3,2)
=C2+ВЫБОР(ДЕНЬНЕД(C2);1;7;6;5;4;3;2)
Chitsanzo 3: Onetsani kuchuluka kwa malonda a sitolo yosankhidwa
Mutha kugwiritsa ntchito SANKHA (SAKHANI) kuphatikiza ndi ntchito zina monga SUM (SUM). Muchitsanzo ichi, tipeza ndalama zogulira sitolo inayake pofotokoza nambala yake muntchitoyo SANKHA (SANKHAni) monga mkangano, komanso kundandalikidwa kwa sitolo iliyonse kuti iwerengere ziwopsezo.
Mu chitsanzo chathu, nambala ya sitolo (101, 102, kapena 103) imalowetsedwa mu selo C2. Kuti mupeze mtengo wolozera monga 1, 2, kapena 3 m'malo mwa 101, 102, kapena 103, gwiritsani ntchito ndondomekoyi: =C2-100.
Zogulitsa za sitolo iliyonse zili mugawo lapadera monga momwe zilili pansipa.
Mkati mwa ntchito SUM (SUM) ntchito idzachitidwa poyamba SANKHA (SAKHANI), yomwe idzabweretsenso mtundu womwe mukufuna wofanana ndi sitolo yosankhidwa.
=SUM(CHOOSE(C2-100,C7:C9,D7:D9,E7:E9))
=СУММ(ВЫБОР(C2-100;C7:C9;D7:D9;E7:E9))
Ichi ndi chitsanzo cha nthawi yomwe imakhala yothandiza kwambiri kugwiritsa ntchito ntchito zina monga INDEX (INDEX) ndi GANIZANI (FUFUZANI). Kenako mu marathon athu, tiwona momwe amagwirira ntchito.