Otomatiki mutsara manhamba muExcel. 3 nzira dzekugadzira otomatiki mutsara manhamba muExcel

Paunenge uchishanda netafura, kuverenga kungave kuri madikanwa. Iyo inogadzirisa, inobvumidza iwe kukurumidza kufamba mairi uye kutsvaga iyo inodiwa data. Pakutanga, purogiramu yatova nenhamba, asi yakamira uye haigoni kuchinjwa. Nzira yekunyora manhamba inopiwa, iyo iri nyore, asi kwete seyakavimbika, zvakaoma kushandisa kana uchishanda nematafura makuru. Nokudaro, munyaya ino, tichatarisa nzira nhatu dzinobatsira uye dziri nyore-kushandisa dzekuverenga matafura muExcel.

Nzira 1: Kuverenga Mushure mekuzadza Mitsetse Yokutanga

Iyi nzira ndiyo yakareruka uye inowanzoshandiswa pakushanda nematafura maduku uye epakati. Zvinotora shoma yenguva uye zvinovimbisa kubviswa kwechero kukanganisa mukuverengera. Mirayiridzo yavo nhanho-nhanho ndeiyi inotevera:

1. Kutanga iwe unofanirwa kugadzira imwe koramu mutafura, iyo ichashandiswa kune imwezve manhamba.
2. Kana koramu yagadzirwa, isa nhamba 1 pamutsara wekutanga, uye isa nhamba 2 pamutsara wechipiri.

3. Sarudza masero maviri akazadzwa uye fambisa pamusoro pekona yekurudyi yenzvimbo yakasarudzwa.
4. Pangosvika iyo dema yemuchinjikwa icon, bata LMB uye dhonza nzvimbo yacho kumagumo etafura.

Kana zvese zvikaitwa nemazvo, ipapo iyo nhamba yakaverengerwa inozadzwa otomatiki. Izvi zvichava zvakakwana kuti uwane chigumisiro chinodiwa.

Nzira 2: "ROW" mushandisi

Zvino ngatiendererei kune inotevera nzira yekuverengera, iyo inosanganisira kushandiswa kweiyo yakakosha "STRING" basa:

1. Chekutanga, gadzira koramu yekuverengera, kana imwe isipo.
2. Mumutsara wekutanga wekoramu iyi, isa fomula inotevera: =MUTSARA(A1).

3. Mushure mekupinda fomula, ita shuwa yekudzvanya "Enter" kiyi, iyo inomutsa basa, uye iwe uchaona nhamba 1.

4. Iye zvino inoramba, zvakafanana nenzira yekutanga, kufambisa chitubu kukona yekurudyi yenzvimbo yakasarudzwa, kumirira kuti muchinjikwa wakasviba uoneke uye utambanudze nzvimbo kusvika kumagumo etafura yako.
5. Kana zvese zvikaitwa nemazvo, koramu ichazadzwa nenhamba uye inogona kushandiswa kutsvagisa ruzivo.

Pane imwe nzira, kuwedzera kune yakatsanangurwa nzira. Ichokwadi, zvinoda kushandiswa kwe "Function Wizard" module:

1. Saizvozvo gadzira koramu yekuverengera.
2. Dzvanya pasero rekutanga mumutsara wekutanga.
3. Pamusoro padhuze nebhawa yekutsvaga, tinya pane "fx" icon.

4. Iyo "Function Wizard" inogadziriswa, iyo yaunoda kudzvanya pane "Chikamu" chinhu uye sarudza "References uye Arrays".

5. Kubva pane zvakarongwa mabasa, zvinosara kusarudza sarudzo "ROW".

6. Iwindo rekuwedzera rekupinda ruzivo richaonekwa. Iwe unofanirwa kuisa chitubu mu "Link" chinhu uye mumunda ratidza kero yesero yekutanga yekoramu yenhamba (mune yedu, iyi ndiyo kukosha A1).

7. Nekuda kwezviito zvakaitwa, nhamba 1 ichaonekwa musero rekutanga risina chinhu. Inosara kushandisa kona yezasi yekona yenzvimbo yakasarudzwa zvakare kuti uikwevera kutafura yose.

Izvi zviito zvinokubatsira iwe kuwana ese anodiwa manhamba uye kukubatsira kuti usatsauswe nezvidiki zvakadaro uchishanda netafura.

Nzira 3: kushandisa kufambira mberi

Iyi nzira yakasiyana nedzimwe mune izvozvo inobvisa kudiwa kwevashandisi kushandisa autofill token. Mubvunzo uyu unonyanya kukosha, sezvo kushandiswa kwayo kusina kunaka kana uchishanda nematafura makuru.

1. Isu tinogadzira mbiru yekuverengera tomaka nhamba 1 musero rekutanga.

2. Tinoenda kubhokisi rekushandisa uye tinoshandisa chikamu che "Kumba", kwatinoenda kune "Editing" chikamu uye tinotarisa chiratidzo muchimiro chemuseve wepasi (paunofamba pamusoro, ichapa zita rokuti "Zadza").

3. Mune inodonha-pasi menyu, unofanirwa kushandisa "Kufambira mberi" basa.
4. Muhwindo rinoonekwa, ita zvinotevera:
   • maka kukosha "Nemakoramu";
   • sarudza arithmetic type;
   • mundima ye "Nhanho", maka nhamba 1;
   • mundima "Limit value" unofanira kucherechedza kuti mitsetse mingani yaunoronga kuverenga.

5. Kana zvese zvikaitwa nemazvo, ipapo iwe uchaona mhedzisiro ye otomatiki manhamba.

Pane imwe nzira yekuita iyi manhamba, inoita seizvi:

1. Dzokorora matanho ekugadzira mbiru womaka musero rekutanga.
2. Sarudza huwandu hwese hwetafura yaunoronga kuverenga.

3. Enda kuchikamu che "Kumba" uye sarudza chikamu che "Editing".
4. Tiri kutsvaga chinhu "Zadza" uye sarudza "Kufambira mberi".
5. Muhwindo rinoonekwa, tinocherechedza data yakafanana, kunyange iye zvino isu tisingazadzi chinhu che "Limit value".

6. Dzvanya pakanzi "OK".

Sarudzo iyi ndeyepasi rose, nekuti haidi kuverengerwa kwemitsara inoda kuverengerwa. Ichokwadi, mune chero mamiriro ezvinhu, iwe uchafanirwa kusarudza huwandu hunoda kuverengerwa.

Teerera! Kuitira kuti zvive nyore kusarudza huwandu hwetafura inoteverwa nenhamba, unogona kungosarudza koramu nekudzvanya pane iyo Excel musoro. Zvadaro shandisa nzira yechitatu yekuverengera uye wokopa tafura pabepa idzva. Izvi zvinorerutsa nhamba yematafura makuru.

mhedziso

Mitsetse nhamba inogona kuita kuti zvive nyore kushanda netafura inoda kugara ichivandudzwa kana kuti inoda kuwana ruzivo rwaunoda. Kutenda kune yakadzama mirairo iri pamusoro, iwe unozogona kusarudza yakanyanya kunaka mhinduro yebasa riripo.