Kodi mungathe kulemba ndondomeko bwanji mu Excel? Maphunziro Njira zofunikira kwambiri

Madzulo abwino

Kamodzi pa nthawi, kulemba ndondomeko mu Excel nokha kunali chinthu chosadabwitsa kwa ine. Ndipo ngakhale kuti nthawi zambiri ndimayenera kugwira ntchito pulogalamuyi, sindinapange kanthu koma ndemanga ...

Monga tawonera, ambiri mwa machitidwewo si ovuta ndipo ndi ovuta kugwira nawo ntchito, ngakhale kwa wosuta makompyuta. M'nkhaniyi, ndikungofuna kufotokoza zofunikira kwambiri, zomwe nthawi zambiri zimagwira ntchito ...

Ndipo kotero, tiyeni tiyambe ...

1. Zochitika zoyambira ndi zofunikira. Excel maphunziro.

Zochita zonse mu nkhaniyi zidzawonetsedwa mu Excel version 2007.

Pambuyo poyambitsa pulogalamu Excel - zenera likuwoneka ndi maselo ambiri - tebulo lathu. Mbali yaikulu ya pulogalamuyi ndi yakuti imatha kuwerenga (monga calculator) njira zomwe mumalemba. Mwa njira, mukhoza kuwonjezera ndondomeko ya selo iliyonse!

Fomuyi iyenera kuyamba ndi "=" chizindikiro. Izi ndizofunikira. Kenaka, lembani zomwe mukuyenera kuziwerengera: mwachitsanzo, "= 2 + 3" (popanda ndemanga) ndipo pezani Enter - chifukwa chake muwona kuti zotsatira zawoneka mu selo "5". Onani chithunzi pansipa.

Ndikofunikira! Ngakhale kuti chiwerengero cha "5" chinalembedwa mu selo A1, chiwerengedwa ndi chikhomo ("= = + +"). Ngati mu selo yotsatira mumangolemba "5" ndi mawu - ndiye pamene mutsegula chithunzithunzi mu seloyi - mu mkonzi wa mzere (mzere wapamwamba, Fx) - mudzawona nambala yaikulu "5".

Tsopano ganizirani kuti mu selo simungathe kulemba chabe 2 + 3, koma chiwerengero cha maselo omwe mumafuna kuwonjezera. Tiyerekeze choncho "= B2 + C2".

Mwachibadwa, payenera kukhala nambala zina mu B2 ndi C2, mwinamwake Excel atiwonetsera ife mu selo A1 zotsatira zomwe zikufanana ndi 0.

Ndipo cholemba china chofunika kwambiri ...

Mukamakopera selo yomwe ili ndi mayina, mwachitsanzo, A1 - ndiyiyike mu selo lina, osati mtengo "5" ukukopedwa, koma mawonekedwe okha!

Komanso, njirayi idzasintha mwachindunji: ngati A1 ikopedwa ku A2 - ndiye chiganizo mu selo A2 chidzakhala chofanana ndi "= B3 + C3". Excel imasintha mawonekedwe anu enieni: ngati A1 = B2 + C2, ndiye zomveka kuti A2 = B3 + C3 (nambala zonse zawonjezeka ndi 1).

Zotsatira, mwa njira, ndi A2 = 0, kuyambira maselo B3 ndi C3 sakukhazikitsidwa, choncho ndi ofanana ndi 0.

Mwanjira iyi, mukhoza kulembera fomu imodzi, ndikuikopera m'maselo onse a gawo lofunidwa - ndipo Excel ngokha idzawerengera mzere uliwonse wa tebulo lanu!

Ngati simukufuna kuti B2 ndi C2 zisinthe pamene mukujambula ndipo nthawizonse mumakhala nawo maselo awa, ingowonjezerani chizindikiro cha "$" kwa iwo. Chitsanzo pansipa.

Choncho, kulikonse kumene mungasunge selo A1, nthawi zonse likutanthauza maselo okhudzana.

2. Kuonjezerapo kwazomwe mumayendedwe (fomu SUM ndi SUMMESLIMN)

Mukhoza, ndithudi, kuwonjezera selo iliyonse, kupanga kapangidwe ka A1 + A2 + A3, ndi zina zotero. Koma pofuna kuti asamavutike kwambiri, mu Excel pali njira yapadera imene idzawonjezera mfundo zonse mu maselo amene mumasankha!

Tengani chitsanzo chophweka. Pali zinthu zingapo zomwe zili mu katundu, ndipo tikudziwa kuchuluka kwa chinthu chilichonse mu kg. ili mu chikhomo. Tiyeni tiyese kuwerengera kuchuluka kwa makilogalamu. katundu mkati.

Kuti muchite izi, pitani ku selo komwe zotsatirazo ziwonetsedwe ndikulemba fomu: "= SUM (C2: C5)". Onani chithunzi pansipa.

Zotsatira zake, maselo onse omwe ali osankhidwa adzatchulidwa, ndipo muwona zotsatira.

2.1. Kuwonjezera pa chikhalidwe (ndi zikhalidwe)

Tsopano taganizirani kuti tili ndi zikhalidwe zina, mwachitsanzo, Sikofunika kuwonjezera malingaliro onse mu maselo (Kg, mu Stock), koma omwewo atchulidwa, amati, ndi mtengo (1 makilogalamu) osakwana 100.

Kwa ichi pali njira yabwino kwambiri "SUMMESLIMN"Nthawi yomweyo chitsanzo, ndiyeno tsatanetsatane wa chizindikiro chirichonse mu njirayi.

= SUMMESLIMN (C2: C5; B2: B5; "<100")kumene:

C2: C5 - Chigawo chimenecho (maselo amenewo), omwe adzawonjezeredwa;

B2: B5 - chigawo chimene chikhalidwecho chidzayang'aniridwa (mwachitsanzo mtengo, mwachitsanzo, zosakwana 100);

"<100" - chikhalidwe chomwecho, zindikirani kuti chikhalidwecho chalembedwa mu ndemanga.

Palibe chophweka mu njirayi, chinthu chachikulu ndikuwona kuchuluka: C2: C5; B2: B5 ndi yolondola; C2: C6; B2: B5 ndi yolakwika. I Mndandanda wa mndandanda wa mndandanda wa mndandanda wa chiwerengerocho uyenera kukhala wofanana, mwinamwake chiwerengerocho chidzabwezera cholakwika

Ndikofunikira! Pakhoza kukhala zinthu zambiri za ndalamazo, mwachitsanzo, Mukhoza kuyang'ana osati pa ndime yoyamba, koma ndi 10 mwakamodzi, pofotokozera zikhazikitso.

3. Kuwerenga chiwerengero cha mizere yokhutiritsa mikhalidwe (njira COUNTIFSLIMN)

Ntchito yowonongeka ndi kuwerengera osati chiwerengero cha zikhulupiliro mu maselo, koma chiwerengero cha maselo omwe amakwaniritsa zikhalidwe zina. Nthawi zina, zinthu zambiri.

Ndipo kotero ^ tiyeni tiyambe.

Mu chitsanzo chomwechi, tidzayesa kuwerengera kuchuluka kwa dzina la mankhwala ndi mtengo wopitirira 90 (ngati mukuyang'ana, munganene kuti pali zinthu ziwiri zotere: zitsamba zam'madzi ndi malalanje).

Kuti tiwerenge katundu mu selo lofunidwa, tilembera njira yotsatirayi (onani pamwambapa):

= DZIKO (B2: B5; "> 90")kumene:

B2: B5 - Mtundu umene angayang'ane malinga ndi momwe timakhalira;

">90" - chikhalidwe chomwecho chiri mu ndemanga.

Tsopano tiyesera kupondereza chitsanzo chathu pang'ono, ndi kuwonjezera chikhomo molingana ndi chikhalidwe chimodzi: ndi mtengo woposa 90 + kuchuluka kwa katundu kulibe makilogalamu 20.

Fomuyi imatenga mawonekedwe:

= COUNTIFS (B2: B6; "> 90"; C2: C6; "<20")

Apa chirichonse chikhala chimodzimodzi, kupatula pa chikhalidwe chimodzi chokha (C2: C6; "<20"). Mwa njira, pangakhale zinthu zambiri zoterezi!

N'zoonekeratu kuti pa tebulo laling'ono, palibe amene angalembedwe malembawo, koma pa tebulo la mizere mazana angapo - ichi ndi nkhani ina. Mwachitsanzo, tebulo ili ndilosavuta.

4. Fufuzani ndikugwiritsanso ntchito malingaliro kuchokera ku gome limodzi kupita ku lina (Fomu ya CDF)

Tangoganizani kuti tebulo latsopano lafika kwa ife, ndi zida zatsopano zamtengo wapatali. Eya, ngati maina a 10-20 - ndipo mungathe "kuwaiwala" onsewo. Ndipo ngati pali maina ambirimbiri? Mofulumira ngati Excel adapeza mowirikiza maina akufanana kuchokera pa tebulo kupita kwa wina, ndipo kenako amakopera ma tepi atsopano ku tebulo lathu lakale.

Pa ntchitoyi, njirayi imagwiritsidwa ntchito Vpr. Panthawi ina, iye mwini "mwanzeru" ndi mawonekedwe oyenera "IF" anali asanakumanepo ndi chinthu chodabwitsa ichi!

Ndipo kotero, tiyeni tiyambe ...

Pano pali chitsanzo chathu + tebulo latsopano lokhala ndi ma mtengo. Tsopano tikuyenera kuti titha kulowetsa malonda atsopano kuchokera ku tebulo latsopano kupita ku wakale (ma tepi atsopano ndi ofiira).

Ikani cholozera mu selo B2 - i.e. mu selo yoyamba kumene tifunika kusintha tanthauzo la mtengo pokhapokha. Kenaka, timalemba fomuyi monga chithunzi pansipa (pambuyo pa chithunzicho padzakhala tsatanetsatane).

= CDF (A2; $ D $ 2: $ E $ 5; 2)kumene

A2 - mtengo womwe tidzakhala tikuwufuna kuti tipeze mtengo wamtengo wapatali. Kwa ife, ife tikuyang'ana mawu oti "maapulo" mu tebulo latsopano.

$ D $ 2: $ E $ 5 - timasankha tebulo lathu latsopano (D2: E5, kusankha kumachokera kumtunda kumanzere kupita kumunsi kumanja mozungulira), e.g. komwe kufufuza kudzachitika. Chizindikiro cha "$" mu ndondomekoyi ndi chofunika kuti pamene mukujambula fomu iyi ku maselo ena - D2: E5 sikusintha!

Ndikofunikira! Kufufuza kwa mawu oti "maapulo" kudzachitika mu gawo loyamba la tebulo lanu losankhidwa; mwachitsanzo, "maapulo" adzafufuzidwa mu ndondomeko D.

2 - Pamene mawu akuti "maapulo" amapezeka, ntchitoyo iyenera kudziwa kuchokera m'ndandanda wa tebulo yosankhidwa (D2: E5) kuti ikopetse mtengo wofunikila. Mu chitsanzo chathu, koperani kuchokera ku ndime 2 (E), kuyambira mu chigawo choyamba (D) chomwe tafufuza. Ngati tebulo lanu losankhidwa likhoza kukhala ndi ndondomeko 10, ndiye ndime yoyamba idzafufuza, ndipo kuyambira pa 2 mpaka 10 - mukhoza kusankha nambala kuti ikopike.

Kuti fomu = CDF (A2; $ D $ 2: $ E $ 5; 2) malingaliro atsopano othandizira maina ena a mankhwala - ingosungirani izo kwa maselo ena a chigawocho ndi zizindikiro za mtengo wa mankhwala (mwachitsanzo chathu, kopani ku maselo B3: B5). Fomuyi idzafufuza ndi kutengera phindu kuchokera ku gawo la tebulo latsopano lomwe mukufuna.

5. Mapeto

M'nkhaniyi, tinayang'ana zofunikira zogwirira ntchito ndi Excel kuchokera momwe tingayambitsire malemba. Anapereka zitsanzo za machitidwe ambiri omwe nthawi zambiri amagwira ntchito ndi ambiri omwe amagwira ntchito ku Excel.

Ndikukhulupirira kuti zitsanzo zomwe zasanthuledwa zidzakhala zothandiza kwa wina ndipo zingakuthandizeni kufulumira ntchito yake. Zotsatira zopambana!

PS

Ndipo mumagwiritsa ntchito njira zotani, kodi n'zotheka kuti mwa njira zina mukhale zosavuta kufotokozera zomwe mwalemba? Mwachitsanzo, pa makompyuta ofooka, pamene zikhalidwe zina zimasintha m'matawuni akulu, momwe mawerengero amachitidwa pokhapokha, makompyuta amawombera kwa masekondi angapo, amawongolera ndikuwonetsa zotsatira zatsopano ...