Kodi kulemba formula mu Excel? Kuphunzitsa. Njira zofunika kwambiri

Masana abwino

Panthawi ina, kulemba formula wekha ku Excel chinali chinthu chodabwitsa kwa ine. Ndipo ngakhale ndimakonda kugwira ntchito pulogalamuyi, sindinadzaze chilichonse kupatula zolemba ...

Monga momwe zidakhalira, njira zambiri sizinthu zovuta kuzichita ndipo mutha kugwira ntchito nawo mosavuta, ngakhale ndiogwiritsa ntchito kompyuta ya novice. Mu nkhaniyi, basi, ndikufuna kuwulula ma formula ofunikira kwambiri, omwe ndimakonda kugwira nawo ...

Chifukwa chake, tiyeni tiyambe ...

1. Ntchito zoyambira ndi zoyambira. Phunzirani zoyambira za Excel.

Zochita zonse zomwe zili m'nkhaniyi zikuwonetsedwa mu Excel mtundu 2007.

Pambuyo poyambitsa pulogalamu ya Excel - zenera limawoneka ndi maselo ambiri - tebulo lathu. Gawo lalikulu la pulogalamuyo ndikuti amatha kuwerengera (ngati chowerengera) mafomula anu omwe mumalemba. Mwa njira, mutha kuwonjezera njira iliyonse ku khungu lililonse!

Fomula iyenera kuyamba ndi chizindikiro "=". Izi ndizofunikira. Kenako mumalemba zomwe muyenera kuwerengera: mwachitsanzo, "= 2 + 3" (popanda zolemba) ndikanikizani batani la Enter - chifukwa chotsatira, muwona kuti zotsatira "5" zikuwonekera m'chipindacho. Onani chithunzi pansipa.

Zofunika! Ngakhale kuti nambala ya "5" yalembedwa mu cell A1, imawerengedwa ndi formula ("= 2 + 3"). Ngati mu cell yotsatira mungolembera "5" m'mawu - ndiye mukangodumphadumpha mu cell yanga (pamwambapa, Fx) - mudzawona chiwerengero chachikulu "5".

Tsopano tayerekezerani kuti mu cell mutha kulemba osati kuchuluka 2 + 3, koma manambala omwe maselo amafunikira kuwonjezera. Tiyeni tinene "= B2 + C2".

Mwachilengedwe, payenera kukhala manambala ena mu B2 ndi C2, apo ayi Excel idzatiwonetsa mu cell A1 zotsatira zake ndi 0.

Ndipo mfundo ina yofunika ...

Mukakopera khungu momwe mumakhala formula, mwachitsanzo A1 - ndikuiika mu cell ina - siyofunika "5" yomwe imakopedwa, koma mawonekedwe okha!

Kuphatikiza apo, mawonekedwe ake amasintha molunjika: i.e. ngati A1 imakoperedwa ku A2, ndiye kuti mawonekedwe mu cell A2 akhale "= B3 + C3". Excel imasinthira formula yanu yokha: ngati A1 = B2 + C2, ndiyetu ndizomveka kuti A2 = B3 + C3 (manambala onse amawonjezeka ndi 1).

Zotsatira zake, mwa njira, zili mu A2 = 0, chifukwa maselo B3 ndi C3 sakudziwika, chifukwa chake ndi ofanana 0.

Chifukwa chake, mutha kulemba formula kamodzi, ndikuyikopera kwa maselo onse a gawo lomwe mukufuna - ndipo Excel iwerengera mzere uliwonse wa tebulo lanu!

Ngati simukufuna kuti B2 ndi C2 zisinthe mukamakopera komanso kuphatikiza nthawi zonse ndi maselowo, onjezerani chithunzi cha "$" kwa iwo. Chitsanzo chili pansipa.

Mwanjira imeneyi, kulikonse komwe mungakope foni A1, imangotanthauza maselo olumikizidwa.

2. Kuphatikiza kwamalingaliro mumizere (SUMM and SUMMESLIMN formula)

Zachidziwikire, mutha kuwonjezera cell iliyonse ndikupanga formula A1 + A2 + A3, etc. Koma kuti musavutike, pali formula yapadera ku Excel yomwe imawonjezera zonse zomwe zili m'maselo omwe mumasankha!

Tengani chitsanzo chosavuta. Pali mitundu ingapo ya katundu mu katundu, ndipo tikudziwa kuchuluka kwake kwa malonda aliwonse payekhapayekha. ili ndi katundu. Tiyeni tiyesere kuwerengera, koma zonsezo ndizotani. kunyamula katundu.

Kuti muchite izi, pitani ku foni yomwe zotsatirazo zikuwonetsedwa ndikulemba formula: "= SUM (C2: C5)". Onani chithunzi pansipa.

Zotsatira zake, maselo onse m'magawo osankhidwa adzafotokozedwa, ndipo muwona zotsatira.

2.1. Zowonjezera pamkhalidwewo (ndi mikhalidwe)

Tsopano tayerekezerani kuti tili ndi machitidwe ena, i.e. musawonjezere zinthu zonse zomwe zili m'maselo (Kg, mu katundu), koma zokhazokha, ndikuti, ndi mtengo (1 kg.) wochepera 100.

Pali njira yabwino yopezera izi. "SUMMESLIMN"Mwachitsanzo mwachitsanzo, kenako malongosoledwe amtundu uliwonse wa kapangidwe kake.

= SUMMES (C2: C5; B2: B5; "<100")pati:

C2: C5 - mzerewu (maselo amenewo) omwe adzawonjezedwa;

B2: B5 - mzere womwe umayesedwa (mtengo, mwachitsanzo, zosakwana 100);

"<100" - zofunikira zomwezo, zindikirani kuti zomwe zalembedwazo zidalembedwa.

Palibe chovuta mu formula iyi, chinthu chachikulu ndikuwona kuchuluka: C2: C5; B2: B5 - kumanja; C2: C6; B2: B5 - yolakwika. Ine.e. magawo a mitundu ndi mndandanda wa zinthu ziyenera kukhala zogwirizana, apo ayi fomulomo imabweza cholakwika.

Zofunika! Pakhoza kukhala mikhalidwe yambiri ya kuchuluka, i.e. Mutha kuyang'ana osati ndi gawo loyamba, koma mwa 10 nthawi yomweyo, kukhazikitsa mikhalidwe yambiri.

3. Kuwerengera kuchuluka kwa mizere yomwe ikukwaniritsa zomwe (chilinganizo ndi CHAKUDYA)

Ntchito wamba: kuwerengetsa kuchuluka kwa zinthu zomwe zili m'maselo, koma kuchuluka kwa maselo omwe amakwaniritsa mikhalidwe ina. Nthawi zina, pamakhala zochitika zambiri.

Ndipo kotero ... tiyeni tiyambe.

Pachitsanzo chomwechi, tiyeni tiyese kuwerengetsa zinthu zomwe zili ndi mtengo woposa 90 (ngati mungayang'ane, mutha kunena kuti pali zinthu ziwiri izi: ma tangerine ndi malalanje).

Kuwerengera zomwe zili mu foni yomwe tikufuna, tidalemba njira iyi (onani pamwambapa):

= ACCOUNTING (B2: B5; "> 90")pati:

B2: B5 mtundu omwe adzafufuzidwa, malinga ndi momwe ife tikhazikitsire;

">90" - mkhalidwewo wokhazikika m'zigawo zokumbukira.

Tsopano tiyeni tiyesere kudalitsa zitsanzo zathu pang'ono, ndikuwonjezera akaunti malinga ndi chinthu china: ndi mtengo woposa 90 + kuchuluka kwa nyumba yosungiramo katundu ndizosakwana 20 kg.

Momwe tsikuli limapangidwira:

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

Zonsezi zimakhala zofanana, kupatula mkhalidwe umodzi wokha (C2: C6; "<20") Mwa njira, pali zinthu zambiri zotere!

Zikuwonekeratu kuti palibe amene angalembe mitundu ya tebulo laling'ono chotere, koma patebulo la mizere mazana angapo, iyi ndi nkhani yosiyananso. Mwachitsanzo, gome ili ndiloposa zowoneka.

4. Kusaka ndi kusintha kwa zofunikira kuchokera pagome limodzi kupita ku lina (mawonekedwe a VLOOKUP)

Ingoganizirani kuti tebulo latsopano labwera kwa ife, lomwe lili ndi mitengo yatsopano ya malonda. Ngati zinthuzo ndi 10-20, mutha kuzikonza zonse pamanja. Ndipo ngati pali mazana a zinthu zotere? Ndizofulumira kwambiri ngati Excel idapezeka palokha mwa mayina ofananikira kuchokera pagome limodzi kupita ku lina, ndikulemba ma tag a mtengo watsopano pagome lathu lakale.

Pantchito yotere, fomula imagwiritsidwa ntchito VPR. Panthawi ina, anali "wanzeru" ndi mayankho omveka a "IF" mpaka atakumana ndi zinthu zabwinozi!

Chifukwa chake, tiyeni tiyambe ...

Nachi zitsanzo chathu + tebulo latsopano lomwe lili ndi ma tag amtengo. Tsopano tikuyenera kulowetsa m'malo mwa mitengo yatsopano kuchokera pa thebulo latsopano kukhala lakale (mayikidwe atsopanowo ndi ofiira).

Ikani tambala mu cell B2 - i.e. mu foni yoyamba, pomwe tikufunika kusintha mtengo wamtengo zokha. Kenako, timalemba formula, monga pazenera pansipa (pachithunzichi pakhala kufotokozedwa mwatsatanetsatane).

= VLOOKUP (A2; $ D $ 2: $ E $ 5; 2)pati

A2 - mtengo womwe tiyembekezere kuti titenge mtengo wamtengo watsopano. M'malo mwathu, tikuyang'ana mawu oti "maapulo" pagome latsopano.

$ D $ 2: $ E $ 5 - sankhani kwathunthu tebulo lathu latsopano (D2: E5, masankhidwewo amachokera kumakona akumanzere kupita kumanzere kumanzere kumanja), i.e. komwe kusaka kudzachitidwa. Chizindikiro cha "$" mu formula iyi ndi chofunikira kuti mukamakopera fomuloli muma cell ena - D2: E5 isasinthe!

Zofunika! Kusaka kwa mawu oti "maapulo" kudzachitika kokha mu gawo loyambirira la tebulo lomwe mwasankha, mchitsanzo ichi, "maapulo" adzafufuzidwa mzati D.

2 - Mawu oti "maapulo" akapezeka, ntchitoyo iyenera kudziwa kuchokera pagawo lililonse la tebulo yosankhidwa (D2: E5) kuti mukope zomwe mukufuna. Pachitsanzo chathu, koperani kuchokera pagawo 2 (E), chifukwa mzere woyamba (D) tidafufuza. Ngati tebulo lanu lomwe mwasankha likhala ndi mizati 10, ndiye kuti chipilala choyamba chifufuze, ndipo kuchokera pazipilala 2 mpaka 10 - mutha kusankha nambala yomwe mungalembe.

Kuti kachitidwe = VLOOKUP (A2; $ D $ 2: $ E $ 5; 2) m'malo mwatsopano maina ena apazinthu zina - ingolandani ku maselo ena omwe ali mgulawo ndi ma tag a mtengo wake (mwachitsanzo, ikani ma cell B3: B5). Fomalo lidzasaka ndikusintha mtengo kuchokera pagome latsamba lomwe mukufuna.

5. Mapeto

M'nkhaniyi, tapenda zoyambira zakugwira ntchito ndi Excel, momwe mungayambire kulemba mitundu. Adapereka zitsanzo zamawonekedwe omwe amafala kwambiri omwe anthu ambiri omwe amagwira ntchito ku Excel nthawi zambiri amagwira nawo ntchito.

Ndikukhulupirira kuti zitsanzo zosakanizikazo zidzakhala zothandiza kwa wina ndipo zithandizira kufulumizitsa ntchito yake. Khalani ndi kuyesa kwabwino!

PS

Ndipo mumagwiritsa ntchito njira ziti? Kodi ndizotheka mwanjira yosavuta njira zomwe zaperekedwa munkhaniyi? Mwachitsanzo, pamakompyuta ofooka, pomwe mfundo zina zimasintha pamatafura akulu pomwe amawerengera amangochitika, kompyuta imasuntha masekondi angapo, kuwerengera ndikuwonetsa zotsatira zatsopano ...