Mukati
Nezuro mumarathon 30 Excel inoshanda mumazuva makumi matatu isu takafunga ruzivo rwenzvimbo yedu yekushanda nebasa INFO (INFORM) ndokuona kuti haachakwanisa kutibatsira nenyaya dzendangariro. Kwete yedu, kana Excel ndangariro!
Pazuva rechishanu remarathon, tichadzidza basa racho SARUDZA (RUSARUDZO). Basa iri nderechikamu References uye arrays, inodzosa kukosha kubva pane zvakarongwa zvingasarudzwa maererano nenhamba yenhamba. Zvakakosha kuziva kuti kazhinji zviri nani kusarudza rimwe basa, semuenzaniso, INDEX (INDEX) uye MATCH (ZVIMWE ZVINOFUNDURWA) kana VLOOKUP (VPR). Tichabata zvinhu izvi gare gare mune ino marathon.
Saka, ngatitendeukire kune ruzivo rwatinarwo uye mienzaniso pane basa SARUDZA (Sarudzo), ngationei mukuita, uye zvakare cherechedza kusasimba. Kana iwe uine mamwe matipi uye mienzaniso yechinhu ichi, ndapota ugovane ivo mune zvakataurwa.
Basa 05: SARUDZA
basa SARUDZA (Sarudzo) inodzosa kukosha kubva pane rondedzero, ichisarudza zvinoenderana nenhamba index.
Unogona sei kushandisa iyo CHOOSE basa?
basa SARUDZA (SARUDZA) inogona kudzosa chinhu chiri murondedzero pane imwe nhamba, seizvi:
- Nenhamba yemwedzi, dzorera iyo fiscal quarter number.
- Kubva pazuva rekutanga, verenga zuva reMuvhuro unotevera.
- Nenhamba yechitoro, ratidza huwandu hwekutengesa.
Syntax sarudza
basa SARUDZA (SELECT) ine chirevo chinotevera:
CHOOSE(index_num,value1,value2,…)
ВЫБОР(номер_индекса;значение1;значение2;…)
- index_num (index_number) inofanira kuva pakati pe1 ne254 (kana 1 kusvika 29 muExcel 2003 uye mberi).
- index_num (index_number) inogona kuiswa mubasa senhamba, fomula, kana chirevo kune imwe sero.
- index_num (index_number) ichadzokororwa kusvika pairi pedyo nhamba yakazara.
- nharo ukoshi (value) inogona kunge iri manhamba, maseru mareferensi, mazita emazita, mabasa, kana zvinyorwa.
SARUDZA Misungo (Sarudzo)
MuExcel 2003 uye pakutanga, basa racho SARUDZA (Sarudzo) yakatsigira nharo makumi maviri nepfumbamwe chete ukoshi (zvinoreva).
Zviri nyore kutsvaga rondedzero pane pepa rekushanda pane kuisa zvinhu zvese mufomula. Nemabasa VLOOKUP (VLOOKUP) kana MATCH (MATCH) Unogona kutarisa kune rondedzero yezvakakosha zviri muExcel worksheets.
Muenzaniso 1: Fiscal quarter by month number
basa SARUDZA (Sarudzo) inoshanda zvakanaka nemanyorerwo akareruka enhamba sehunhu. Semuenzaniso, kana cell B2 ine nhamba yemwedzi, basa racho SARUDZA (Sarudzo) inogona kuverenga kuti ndeyapi chikamu chemari. Mumuenzaniso unotevera, gore rezvemari rinotanga muna Chikunguru.
Iyo fomula inonyora gumi nemaviri tsika dzinoenderana nemwedzi 12 kusvika 1. Gore rezvemari rinotanga muna Chikunguru, saka mwedzi 12, 7, uye 8 inowira mukota yekutanga. Mutafura iri pazasi, unogona kuona nhamba yekota yemari pasi penhamba yemwedzi wega wega.
Mukushanda SARUDZA (SARUDZA) Nhamba yekota inofanira kuiswa muhurongwa hwavanooneka patafura. Semuenzaniso, mune runyorwa rwemaitiro emabasa SARUDZA (SARUDZA) muzvinzvimbo 7, 8 uye 9 (Chikunguru, Nyamavhuvhu naGunyana) inofanira kunge iri nhamba 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)
Isa nhamba yemwedzi muchitokisi C2, uye basa SARUDZA (SARUDZA) ichaverenga nhamba yekota yemari muchitokisi C3.
Muenzaniso 2: Verenga zuva reMuvhuro unotevera
basa SARUDZA (SELECT) inogona kushanda pamwe chete nebasa SVONDO (DAYWEEK) kuverenga mazuva emangwana. Semuenzaniso, kana iwe uri nhengo yekirabhu inosangana manheru eMuvhuro wega wega, saka nekuziva zuva ranhasi, unogona kuverenga zuva reMuvhuro unotevera.
Mufananidzo uri pasi apa unoratidza nhamba dzinoverengeka dzezuva rega rega revhiki. Column H yezuva rega rega revhiki ine nhamba yemazuva ekuwedzera kune yazvino zuva kuti uwane Muvhuro unotevera. Somuenzaniso, unofanira kuwedzera zuva rimwe chete kuSvondo. Uye kana nhasi uri Muvhuro, saka kuchine mazuva manomwe kusvika Muvhuro unotevera.
Kana zuva razvino riri muchitokisi C2, ipapo fomula muchitokisi C3 inoshandisa mabasa SVONDO (ZUVA) uye SARUDZA (SARUDZA) kuverenga zuva reMuvhuro unotevera.
=C2+CHOOSE(WEEKDAY(C2),1,7,6,5,4,3,2)
=C2+ВЫБОР(ДЕНЬНЕД(C2);1;7;6;5;4;3;2)
Muenzaniso 3: Ratidza huwandu hwekutengesa kwechitoro chakasarudzwa
Unogona kushandisa basa SARUDZA (SARUDZA) musanganiswa nemamwe mabasa akadai SUM (SUM). Mumuenzaniso uyu, tichawana huwandu hwekutengesa kwechimwe chitoro nekutsanangura nhamba yacho mukuita SARUDZA (SARUDZA) senharo, pamwe nekunyora dhata rechitoro chega chega kuti riverenge hwerengedzo.
Mumuenzaniso wedu, nhamba yechitoro (101, 102, kana 103) inopindirwa muchitokisi C2. Kuti uwane kukosha kweindex senge 1, 2, kana 3 pane 101, 102, kana 103, shandisa fomula: =C2-100.
Iyo data yekutengesa yechitoro chega chega iri mumutsara wakasiyana sezvakaratidzwa pasi apa.
Mukati mechiitiko SUM (SUM) basa richaitwa kutanga SARUDZA (SARUDZA), iyo inodzosa yaidiwa musanganiswa inoenderana nechitoro chakasarudzwa.
=SUM(CHOOSE(C2-100,C7:C9,D7:D9,E7:E9))
=СУММ(ВЫБОР(C2-100;C7:C9;D7:D9;E7:E9))
Uyu muenzaniso wemamiriro ezvinhu apo inonyanya kushanda kushandisa mamwe mabasa akadai INDEX (INDEX) uye MATCH (TSVAGA). Gare gare mumarathon edu, tichaona kuti vanoshanda sei.