Momwe mungayikitsire fomula mu cell ya Excel

Ogwiritsa ntchito ambiri a novice Excel nthawi zambiri amakhala ndi funso: Kodi fomula ya Excel ndi chiyani komanso momwe mungalowetse mu cell. Ambiri amaganiza kuti n’chifukwa chiyani kuli kofunika. Kwa iwo, Excel ndi spreadsheet. Koma kwenikweni, ichi ndi chowerengera chachikulu chochita ntchito zambiri komanso, pamlingo wina, malo opangira mapulogalamu.

Lingaliro la formula ndi ntchito

Ndipo ntchito zonse mu Excel zimachokera pamapangidwe, omwe ali ndi chiwerengero chachikulu. Pamtima pa fomula iliyonse pali ntchito. Ndi chida chofunikira chowerengera chomwe chimabwezera mtengo kutengera zomwe zatumizidwa pambuyo zitakonzedwa kale.

Fomula ndi gulu la ogwira ntchito zomveka, masamu ndi ntchito. Sikuti nthawi zonse imakhala ndi zinthu zonsezi. Kuwerengera kungaphatikizepo, mwachitsanzo, masamu okha.

M'mawu atsiku ndi tsiku, ogwiritsa ntchito Excel nthawi zambiri amasokoneza malingaliro awa. M'malo mwake, mzere pakati pawo umakhala wosasinthasintha, ndipo mawu onsewa amagwiritsidwa ntchito nthawi zambiri. Komabe, kuti mumvetsetse bwino ntchito ndi Excel, ndikofunikira kudziwa zolondola. 

Mawu okhudzana ndi ma formula

M'malo mwake, zida zamawu ndi zotakata kwambiri ndipo zimaphatikizanso mfundo zina zambiri zomwe ziyenera kuganiziridwa mwatsatanetsatane.

1. Nthawi zonse. Uwu ndi mtengo womwe umakhalabe womwewo ndipo sungathe kusinthidwa. Izi zitha kukhala, mwachitsanzo, nambala ya Pi.
2. Othandizira. Ichi ndi gawo lofunikira kuti mugwire ntchito zina. Excel imapereka mitundu itatu ya ogwiritsa ntchito:
   1. Masamu. Zofunika kuwonjezera, kuchotsa, kugawa ndi kuchulukitsa manambala angapo. 
   2. Wogwirizanitsa ntchito. Zofunikira kuti muwone ngati deta ikukumana ndi chikhalidwe china. Itha kubweza mtengo umodzi: woona kapena wabodza.
   3. Wogwiritsa ntchito mawu. Ndi imodzi yokha, ndipo ndiyofunika kulumikiza deta - &.
3. Lumikizani. Ili ndiye adilesi ya cell yomwe deta idzatengedwa, mkati mwa fomula. Pali mitundu iwiri ya maulalo: mtheradi ndi wachibale. Zoyamba sizisintha ngati chilinganizocho chisamutsidwira kumalo ena. Achibale, motero, amasintha selo kukhala loyandikana kapena lofanana. Mwachitsanzo, ngati mungatchule ulalo wa selo B2 mu selo linalake, ndiyeno kukopera fomulayi ku yoyandikana nayo kumanja, adilesiyo imangosintha kukhala C2. Ulalo ukhoza kukhala wamkati kapena wakunja. Poyamba, Excel imapeza cell yomwe ili m'buku lomwelo. Mu chachiwiri - mwa ena. Ndiye kuti, Excel imatha kugwiritsa ntchito zomwe zili muzolemba zina m'ma formula. 

Momwe mungalowetse deta mu cell

Imodzi mwa njira zosavuta zoyika fomu yomwe ili ndi ntchito ndiyo kugwiritsa ntchito Function Wizard. Kuti muyitchule, muyenera dinani chizindikiro cha fx pang'ono kumanzere kwa formula bar (ili pamwamba pa tebulo, ndipo zomwe zili m'selo zimabwerezedwa mmenemo ngati mulibe formula mmenemo kapena ndondomekoyi ndi kuwonetsedwa ngati kuli.Bokosi loterolo lidzawonekera.

Kumeneko mukhoza kusankha gulu la ntchito ndi mwachindunji kuchokera pamndandanda womwe mukufuna kugwiritsa ntchito mu selo linalake. Kumeneko simungathe kuwona mndandanda wokha, komanso zomwe ntchito iliyonse imachita. 

Njira yachiwiri yolowera mafomu ndikugwiritsa ntchito tabu yofananira pa riboni ya Excel.

Apa mawonekedwe ndi osiyana, koma zimango ndi chimodzimodzi. Ntchito zonse zimagawidwa m'magulu, ndipo wogwiritsa ntchito amatha kusankha yomwe ili yoyenera kwa iye. Kuti muwone zomwe ntchito iliyonse imachita, muyenera kuyimitsa ndi cholozera cha mbewa ndikudikirira masekondi awiri.

Mukhozanso kulowa ntchito mwachindunji mu selo. Kuti muchite izi, muyenera kuyamba kulemba chizindikiro cholowetsamo (= =) ndikulowetsamo dzina la ntchitoyo pamanja. Njirayi ndi yoyenera kwa ogwiritsa ntchito odziwa zambiri omwe amadziwa ndi mtima. Zimakuthandizani kuti musunge nthawi yambiri.

Mukalowa zilembo zoyambirira, mndandanda udzawonetsedwa, momwe mungathenso kusankha ntchito yomwe mukufuna ndikuyiyika. Ngati sizingatheke kugwiritsa ntchito mbewa, ndiye kuti mutha kuyenda pamndandandawu pogwiritsa ntchito kiyi ya TAB. Ngati ndi choncho, kungodina kawiri pa fomula yofananira ndikokwanira. Ntchitoyo ikasankhidwa, chidziwitso chidzawoneka chokulolani kuti mulowetse deta motsatira ndondomeko yoyenera. Deta iyi imatchedwa mfundo za ntchito.

Ngati mukugwiritsabe ntchito mtundu wa Excel 2003, ndiye kuti sichimapereka mndandanda wotsitsa, chifukwa chake muyenera kukumbukira dzina lenileni la ntchitoyi ndikulowetsamo data kuchokera pamtima. Zomwezo zimagwiranso ntchito pazokangana zonse. Mwamwayi, kwa wogwiritsa ntchito wodziwa zambiri, ili si vuto. 

Ndikofunikira nthawi zonse kuyambitsa chilinganizo chokhala ndi chizindikiro chofanana, apo ayi Excel angaganize kuti seloyo ili ndi zolemba. 

Pachifukwa ichi, deta yomwe imayamba ndi chizindikiro chowonjezera kapena kuchotsera idzatengedwanso ngati fomula. Ngati pambuyo pake pali zolemba mu cell, ndiye Excel ipereka cholakwika #NAME?. Ngati manambala kapena manambala aperekedwa, ndiye kuti Excel idzayesa kuchita masamu oyenera (kuwonjezera, kuchotsa, kuchulukitsa, kugawa). Mulimonsemo, tikulimbikitsidwa kuti tiyambe kulowetsa ndondomeko ndi = chizindikiro, monga mwachizolowezi.

Mofananamo, mukhoza kuyamba kulemba ntchito ndi @ sign, yomwe idzasinthidwa zokha. Njira yolowetsayi imatengedwa kuti ndi yachikale ndipo ndiyofunikira kuti zolemba zakale zisamataye magwiridwe antchito. 

Lingaliro la mikangano yantchito

Pafupifupi ntchito zonse zimakhala ndi zotsutsana, zomwe zimatha kukhala mawu a cell, zolemba, nambala, ngakhalenso ntchito ina. Choncho, ngati mugwiritsa ntchito ENECHET, muyenera kutchula manambala omwe adzafufuzidwe. Mtengo wa boolean udzabwezedwa. Ngati ili nambala yosamvetseka, TRUE ibwezedwa. Chifukwa chake, ngati zili choncho, ndiye "BODZA". Zotsutsana, monga mukuwonera pazithunzi pamwambapa, zimalowetsedwa m'mabokosi, ndipo zimasiyanitsidwa ndi semicolon. Pamenepa, ngati Baibulo la Chingelezi la pulogalamuyi likugwiritsidwa ntchito, ndiye kuti koma nthawi zonse imakhala ngati olekanitsa. 

Mtsutso wolowetsa umatchedwa parameter. Ntchito zina zilibe nkomwe. Mwachitsanzo, kuti mupeze nthawi ndi tsiku lomwe lili muselo, muyenera kulemba fomula =TATA (). Monga mukuonera, ngati ntchitoyo sikufunika kuyikapo mfundo, mabatani amafunikabe kufotokozedwa. 

Zina mwa mafomu ndi ntchito

Ngati data yomwe ili mu cell yomwe yatchulidwa ndi fomula yasinthidwa, idzawerengeranso datayo moyenera. Tiyerekeze kuti tili ndi selo A1, yomwe yalembedwa m'njira yosavuta yokhala ndi ma cell anthawi zonse =D1. Ngati mutasintha zomwe zili mmenemo, ndiye kuti mtengo womwewo udzawonetsedwa mu selo A1. Momwemonso, pamapangidwe ovuta kwambiri omwe amatenga deta kuchokera ku maselo enieni.

Ndikofunika kumvetsetsa kuti njira zokhazikika za Excel sizingapangitse selo kubweza mtengo wake ku cell ina. Nthawi yomweyo, ntchitoyi imatha kutheka pogwiritsa ntchito ma macros - ma subroutines omwe amachita zinthu zina muzolemba za Excel. Koma iyi ndi mutu wosiyana kotheratu, womwe mwachidziwikire siwongoyamba kumene, chifukwa umafunikira luso la mapulogalamu.

Lingaliro la array formula

Ichi ndi chimodzi mwazosiyana za chilinganizo, chomwe chimalowetsedwa m'njira yosiyana pang'ono. Koma ambiri sadziwa kuti ndi chiyani. Choncho tiyeni tiyambe kumvetsa tanthauzo la mawu amenewa. Ndikosavuta kumvetsetsa izi ndi chitsanzo. 

Tiyerekeze kuti tili ndi ndondomeko SUM, zomwe zimabweretsanso kuchuluka kwamtengo mumitundu ina. 

Tiyeni tipange mtundu wosavuta wotere polemba manambala kuyambira wani mpaka asanu m'maselo A1:A5. Kenako timatchula ntchitoyo =KUCHULUKA(A1:A5) mu cell B1. Zotsatira zake, nambala 15 idzawonekera pamenepo. 

Kodi iyi ndi njira yotsatirika kale? Ayi, ngakhale imagwira ntchito ndi dataset ndipo imatha kutchedwa imodzi. Tiyeni tisinthe zina. Tiyerekeze kuti tifunika kuwonjezera chimodzi pa mkangano uliwonse. Kuti muchite izi, muyenera kuchita zotsatirazi:

=KUCHULUKA(A1:A5+1). Zikuwonekeratu kuti tikufuna kuwonjezera chimodzi pamitundu ingapo tisanawerenge kuchuluka kwake. Koma ngakhale mu mawonekedwe awa, Excel safuna kuchita izi. Ayenera kusonyeza izi pogwiritsa ntchito njira Ctrl + Shift + Enter. Njirayi imasiyana mosiyanasiyana ndipo imawoneka motere:

{=KUCHULUKA(A1:A5+1)}

Pambuyo pake, kwa ife, zotsatira 20 zidzalowetsedwa. 

Palibe chifukwa cholowetsa ma curly braces pamanja. Izo sizingachite kalikonse. M'malo mwake, Excel sangaganize kuti iyi ndi ntchito komanso zolemba m'malo mwa fomula. 

Mkati mwa ntchitoyi, panthawiyi, zotsatirazi zinachitidwa. Choyamba, pulogalamuyi imawola izi kukhala zigawo. Kwa ife, ndi 1,2,3,4,5. Pambuyo pake, Excel imangowonjezera imodzi mwazokha. Kenako manambala otsatiridwawo amawonjezedwa.

Palinso vuto lina pomwe fomula yophatikizika imatha kuchita zinthu zomwe fomula yokhazikika silingathe. Mwachitsanzo, tili ndi deta yomwe yalembedwa mumtundu wa A1:A10. Munthawi yokhazikika, zero idzabwezeredwa. Koma tiyerekeze kuti tili ndi vuto loti ziro sizingaganizidwe.

Tiyeni tiyike chilinganizo chomwe chimayang'ana kuchulukana kuti tiwone ngati sichofanana ndi mtengowu.

=МИН(ЕСЛИ(A1:A10<>0;A1:A10))

Pano pali kumverera kwabodza kuti zotsatira zomwe mukufuna zidzakwaniritsidwa. Koma izi sizili choncho, chifukwa apa muyenera kugwiritsa ntchito njira zingapo. Mu ndondomeko yomwe ili pamwambayi, chinthu choyamba chokha chidzafufuzidwa, chomwe, ndithudi, sichikugwirizana ndi ife. 

Koma ngati mutembenuza kukhala ndondomeko yotsatizana, kuyanjanitsa kungasinthe mwamsanga. Tsopano mtengo wocheperako udzakhala 1.

Fomula yofananira ilinso ndi mwayi woti imatha kubweza zinthu zingapo. Mwachitsanzo, mukhoza transpose tebulo. 

Choncho, pali mitundu yosiyanasiyana ya ma formula. Zina mwa izo zimafuna njira zosavuta, zina zovuta kwambiri. Mafayilo osiyanasiyana amatha kukhala ovuta makamaka kwa oyamba kumene kuti amvetsetse, koma ndi othandiza kwambiri.