Momwe mungawerengere kuchuluka kwa mzere mu Excel. Njira 3 Zowerengera Chiwerengero cha Nambala mumzere wa Excel

Pogwira ntchito ndi chidziwitso cha tabular, ogwiritsa ntchito nthawi zambiri amafunika kuwerengera kuchuluka kwa chizindikiro. Nthawi zambiri zizindikirozi ndi mayina a mizere yomwe imafunika kufotokozera zonse zomwe zili m'maselo. Kuchokera m'nkhaniyi muphunzira njira zonse zomwe zimakulolani kuti mugwiritse ntchito njirayi.

Kuwerengera ndalama motsatizana

Mutha kutulutsanso njira zowerengera mwachidule motsatira njira zotsatirazi:

  • masamu chilinganizo;
  • auto summary;
  • ntchito zosiyanasiyana.

Iliyonse mwa njirazi imagawidwa m'njira zowonjezera. Tiyeni tithane nawo mwatsatanetsatane.

Njira 1: masamu a masamu

Choyamba, tiyeni tiwone momwe, pogwiritsa ntchito masamu a masamu, ndizotheka kuwerengera motsatira. Tiyeni tifufuze chirichonse ndi chitsanzo chapadera. Tiyerekeze kuti tili ndi tebulo lomwe likuwonetsa ndalama za masitolo 5 pamasiku ena. Mayina a malo ogulitsa ndi mayina a mizere. Madeti ndi mayina a magawo.

Momwe mungawerengere kuchuluka kwa mzere mu Excel. Njira 3 Zowerengera Chiwerengero cha Nambala mumzere wa Excel
1

Cholinga: kuwerengera kuchuluka kwa ndalama zomwe zimagulitsidwa nthawi zonse. Kuti mukwaniritse cholinga ichi, ndikofunikira kuwonjezera ma cell onse a mzere wokhudzana ndi sitolo iyi. Njirayi ikuwoneka motere:

  1. Timasankha selo lomwe zotsatira zake zidzawonekera mtsogolomu. Lowetsani chizindikiro "=" mu selo. Timasindikiza LMB pa cell yoyamba pamzerewu wokhala ndi manambala. Timazindikira kuti pambuyo kuwonekera ma cell coordinates adawonetsedwa mu cell kuti awerengere zotsatira zake. Lowetsani chizindikiro cha "+" ndikudina pa selo lotsatira pamzerewu. Timapitiriza kusintha chizindikiro "+" ndi makonzedwe a maselo a mzere woyamba. Chifukwa chake, timapeza formula: =B3+C3+D3+E3+F3+G3+H3.
Momwe mungawerengere kuchuluka kwa mzere mu Excel. Njira 3 Zowerengera Chiwerengero cha Nambala mumzere wa Excel
2
  1. Pambuyo pochita zosintha zonse, dinani "Enter".
  2. Okonzeka! Chotsatiracho chinawonetsedwa mu selo lomwe tinalowetsamo ndondomeko yowerengera kuchuluka kwake.
Momwe mungawerengere kuchuluka kwa mzere mu Excel. Njira 3 Zowerengera Chiwerengero cha Nambala mumzere wa Excel
3

Tcherani khutu! Monga mukuonera, njirayi ndi yomveka komanso yosavuta, koma ili ndi drawback imodzi yonyansa. Kukhazikitsa njira imeneyi kumatenga nthawi yambiri. Tiyeni tiganizire zamitundu yothamanga kwambiri ya summation.

Njira 2: AutoSum

Kugwiritsa ntchito autosum ndi njira yothamanga kwambiri kuposa yomwe takambirana pamwambapa. Njirayi ikuwoneka motere:

  1. Pogwiritsa ntchito LMB yopanikizidwa, timasankha ma cell onse a mzere woyamba omwe ali ndi manambala. Timasunthira ku gawo la "Home", lomwe lili pamwamba pa mawonekedwe a spreadsheet. Timapeza chipika cha malamulo "Editing" ndikudina chinthucho "Editing".
    Momwe mungawerengere kuchuluka kwa mzere mu Excel. Njira 3 Zowerengera Chiwerengero cha Nambala mumzere wa Excel
    4

Malangizo! Njira ina ndikupita kugawo la "Mafomula" ndikudina batani la "AutoSum" lomwe lili mu block ya "Function Library". Njira yachitatu ndikugwiritsa ntchito kiyi kuphatikiza "Alt" + "=" mutasankha selo.

Momwe mungawerengere kuchuluka kwa mzere mu Excel. Njira 3 Zowerengera Chiwerengero cha Nambala mumzere wa Excel
5
  1. Kaya mwasankha njira iti, manambala amawoneka kumanja kwa maselo osankhidwa. Nambala iyi ndi kuchuluka kwa zigoli za mizere.
Momwe mungawerengere kuchuluka kwa mzere mu Excel. Njira 3 Zowerengera Chiwerengero cha Nambala mumzere wa Excel
6

Monga mukuwonera, njira iyi imagwira ntchito pamzere mwachangu kwambiri kuposa pamwambapa. Chotsalira chachikulu ndi chakuti zotsatira zake zikuwonetsedwa kumanja kwa mtundu wosankhidwa. Kuti zotsatira ziwonetsedwe pamalo aliwonse osankhidwa, m'pofunika kugwiritsa ntchito njira zina.

Njira 3: Ntchito ya SUM

Kugwiritsa ntchito pulogalamu yophatikizika ya spreadsheet yotchedwa SUM ilibe kuipa kwa njira zomwe takambirana kale. SUM ndi ntchito ya masamu. Ntchito ya woyendetsa ndikuphatikiza manambala. Maonedwe onse a wogwiritsa ntchito: = SUM(nambala1,nambala2,…).

Zofunika! Zotsutsana pa ntchitoyi zitha kukhala manambala kapena ma cell coordinates. Chiwerengero chachikulu cha mikangano ndi 255.

Maphunziro a tsatane-tsatane akuwoneka motere:

  1. Timasankha cell iliyonse yopanda kanthu patsamba logwirira ntchito. Mmenemo tidzawonetsera zotsatira za kufupikitsa. Ndikoyenera kudziwa kuti ikhozanso kupezeka patsamba lina lachikalatacho. Mukasankha, dinani batani la "Insert Function", lomwe lili pafupi ndi mzere wolowetsa mafomu.
Momwe mungawerengere kuchuluka kwa mzere mu Excel. Njira 3 Zowerengera Chiwerengero cha Nambala mumzere wa Excel
7
  1. Zenera laling'ono lotchedwa "Function Wizard" linawonetsedwa pazenera. Wonjezerani mndandanda pafupi ndi mawu akuti "Category:" ndikusankha chinthu "Masamu". Pang'ono pang'ono pamndandanda "Sankhani ntchito:" timapeza woyendetsa SUM ndikudina. Pambuyo pochita zosintha zonse, dinani batani la "Chabwino" lomwe lili pansi pawindo.
Momwe mungawerengere kuchuluka kwa mzere mu Excel. Njira 3 Zowerengera Chiwerengero cha Nambala mumzere wa Excel
8
  1. Zenera lotchedwa "Function Arguments" linawonekera pachiwonetsero. M'munda wopanda kanthu "Number1" lowetsani adilesi ya mzere, zomwe mukufuna kuwonjezera. Kuti tichite izi, timayika cholozera pamzerewu, ndiyeno, pogwiritsa ntchito LMB, timasankha mtundu wonsewo wokhala ndi manambala. Pambuyo pochita zosintha zonse, dinani "Chabwino".
Momwe mungawerengere kuchuluka kwa mzere mu Excel. Njira 3 Zowerengera Chiwerengero cha Nambala mumzere wa Excel
9
  1. Okonzeka! Zotsatira zakuphatikiza zidawonetsedwa mu cell yomwe idasankhidwa koyambirira.
Momwe mungawerengere kuchuluka kwa mzere mu Excel. Njira 3 Zowerengera Chiwerengero cha Nambala mumzere wa Excel
10

Ntchito ya SUM sikugwira ntchito

Nthawi zina zimachitika kuti woyendetsa SUM sagwira ntchito. Zifukwa zazikulu za kusagwira ntchito bwino:

  • mtundu wolakwika wa nambala (zolemba) potumiza deta;
  • kukhalapo kwa zilembo zobisika ndi malo m'maselo okhala ndi manambala.

Ndikoyenera kuzindikira! Miyezo ya manambala nthawi zonse imakhala yolondola ndipo zidziwitso zamawu nthawi zonse zimasiyidwa.

Momwe mungapezere kuchuluka kwazinthu zazikulu kwambiri (zochepa kwambiri).

Tiyeni tiwone momwe tingawerengere zing'onozing'ono kapena zazikulu kwambiri. Mwachitsanzo, tifunika kuwerengera ziwerengero zitatu zochepera kapena zitatu.

Momwe mungawerengere kuchuluka kwa mzere mu Excel. Njira 3 Zowerengera Chiwerengero cha Nambala mumzere wa Excel
11

Wothandizira wamkulu amakulolani kuti mubweze zigoli zambiri kuchokera pa data yomwe mwasankha. Mtsutso wachiwiri umatchula metric yoyenera kubwerera. Pachitsanzo chathu chenicheni, fomula ikuwoneka motere: =СУММ(НАИБОЛЬШИЙ(B2:D13;{1;2;3})).

Kufufuza kwa mtengo wochepa kwambiri kumagwira ntchito mofananamo, ntchito ya SMALL yokha ndiyo imagwiritsidwa ntchito m'malo mwa GREATEST operator. Fomula ikuwoneka motere: =СУММ(НАИМЕНЬШИЙ(B2:D13;{1;2;3})).

Kutambasula chilinganizo/ntchito ku mizere ina

Tinapeza momwe ndalama zonse zimawerengedwera pamaselo a mzere umodzi. Tiyeni tiwone momwe tingagwiritsire ntchito njira yophatikizira pamizere yonse ya tebulo. Njira zolembera pamanja ndikuyika woyendetsa SUM ndi njira zazitali komanso zosathandiza. Njira yabwino kwambiri ndiyo kutambasula ntchito kapena chilinganizo ku chiwerengero chomwe mukufuna mizere. Njirayi ikuwoneka motere:

  1. Timawerengera ndalamazo pogwiritsa ntchito imodzi mwa njira zomwe zili pamwambazi. Sunthani cholozera cha mbewa kumunsi kumanja kwa selo ndi zotsatira zowonetsedwa. Cholozeracho chidzakhala ngati chizindikiro chaching'ono chamdima chophatikiza. Gwirani LMB ndikukokera fomula mpaka pansi pa mbaleyo.
Momwe mungawerengere kuchuluka kwa mzere mu Excel. Njira 3 Zowerengera Chiwerengero cha Nambala mumzere wa Excel
12
  1. Okonzeka! Tafotokoza mwachidule zotsatira za mitu yonse. Tapeza izi chifukwa chakuti pokopera fomula, ma adilesi amasinthidwa. Kuchepa kwa ma coordinates ndichifukwa choti ma adilesi ndi ofanana.
Momwe mungawerengere kuchuluka kwa mzere mu Excel. Njira 3 Zowerengera Chiwerengero cha Nambala mumzere wa Excel
13
  1. Pa mzere wa 3, ndondomekoyi ikuwoneka motere: =B3+C3+D3+E3+F3+G3+H3.
Momwe mungawerengere kuchuluka kwa mzere mu Excel. Njira 3 Zowerengera Chiwerengero cha Nambala mumzere wa Excel
14

Momwe mungawerengere kuchuluka kwa mzere uliwonse wa Nth.

Pachitsanzo china, tiwona momwe tingawerengere kuchuluka kwa mzere uliwonse wa Nth. Mwachitsanzo, tili ndi tebulo lomwe limawonetsa phindu latsiku ndi tsiku la malo ogulitsira kwa nthawi inayake.

Momwe mungawerengere kuchuluka kwa mzere mu Excel. Njira 3 Zowerengera Chiwerengero cha Nambala mumzere wa Excel
15

Ntchito: kuwerengera phindu la sabata sabata iliyonse. Ogwiritsa ntchito a SUM amakulolani kuti muwerenge deta osati mumndandanda wokha, komanso mndandanda. Apa m'pofunika kugwiritsa ntchito wothandizira OFFSET. Woyendetsa OFFSET amatchula zifukwa zingapo:

  1. Mfundo yoyamba. Cell C2 imalowetsedwa ngati chiwongolero chamtheradi.
  2. Chiwerengero cha masitepe otsika.
  3. Chiwerengero cha masitepe kumanja.
  4. Chiwerengero cha masitepe pansi.
  5. Chiwerengero cha mizati mu gulu. Kugunda mfundo yomaliza ya mndandanda wa zizindikiro.

Timamaliza ndi njira zotsatirazi sabata yoyamba: =СУММ(СМЕЩ($C$2;(СТРОКА()-2)*5;0;5;1)). Zotsatira zake, wowerengera ndalama aziwerengera manambala onse asanu.

3-D sum, kapena kugwira ntchito ndi mapepala angapo a Excel workbook

Kuti muwerenge manambala kuchokera pamawonekedwe ofanana pamasamba angapo, muyenera kugwiritsa ntchito mawu apadera otchedwa "3D reference". Tinene kuti pamasamba onse a bukhuli pali mbale yokhala ndi chidziwitso cha sabata. Tiyenera kuziyika zonse pamodzi ndikuzibweretsa ku chiwerengero cha mwezi uliwonse. Kuti muyambe, muyenera kuwona vidiyo iyi:

Tili ndi mbale zinayi zofanana. Njira yanthawi zonse yowerengera phindu imawoneka motere: =СУММ(неделя1!B2:B8;неделя2!B2:B8;неделя3!B2:B8;неделя4!B2:B8). Apa, ma cell angapo amakhala ngati mikangano.

Chidule cha 3D chikuwoneka motere: =SUM(sabata1:week4!B2:B8). Ikunena pano kuti kuphatikizika kumapangidwa m'magawo a B2:B8, omwe ali pamasamba: sabata (kuyambira 1 mpaka 4). Pali kuwonjezeka kwapang'onopang'ono kwa chiwerengero cha pepala limodzi ndi limodzi.

Kuwerengera ndi zinthu zingapo

Pali nthawi zina pomwe wogwiritsa amayenera kuthana ndi vuto lomwe limatchula zinthu ziwiri kapena zingapo ndipo amayenera kuwerengera kuchuluka kwa manambala molingana ndi njira zosiyanasiyana. Kuti mugwiritse ntchito ndondomekoyi, gwiritsani ntchito «=SUMMESLIMN".

Momwe mungawerengere kuchuluka kwa mzere mu Excel. Njira 3 Zowerengera Chiwerengero cha Nambala mumzere wa Excel
16

Maphunziro a tsatane-tsatane akuwoneka motere:

  1. Poyamba, tebulo limapangidwa.
  2. Imasankha selo lomwe zotsatira zake zidzawonetsedwa.
  3. Pitani pamzere kuti mulowetse mafomu.
  4. Timalowetsa opareta: =SUMMAESLIMN.
  5. Pang'onopang'ono, timalowa mumtundu wowonjezera, kuchuluka kwa condition1, condition1 ndi zina zotero.
  6. Pambuyo pochita zosintha zonse, dinani "Enter". Okonzeka! Kuwerengera kwapangidwa.

Ndikoyenera kudziwa! Payenera kukhala cholekanitsa mwa mawonekedwe a semicolon ";" pakati pa mikangano ya woyendetsa. Ngati delimiter iyi sinagwiritsidwe ntchito, ndiye kuti spreadsheet ipanga cholakwika chosonyeza kuti ntchitoyi idalembedwa molakwika.

Momwe mungawerengere kuchuluka kwa ndalamazo

Tsopano tiyeni tikambirane mmene kuwerengera molondola kuchuluka kwa ndalama. Njira yosavuta, yomwe idzamvetsetsedwe ndi ogwiritsa ntchito onse, ndiyo kugwiritsa ntchito gawo kapena lamulo la "square". Chofunikiracho chikhoza kumveka kuchokera pa chithunzi chili pansipa:

Momwe mungawerengere kuchuluka kwa mzere mu Excel. Njira 3 Zowerengera Chiwerengero cha Nambala mumzere wa Excel
17

Ndalama zonse zikuwonetsedwa mu selo F8 ndipo ili ndi mtengo wa 3060. Mwa kuyankhula kwina, izi ndizopeza zana limodzi, ndipo tifunika kupeza kuchuluka kwa phindu lomwe Sasha anapanga. Kuti tiwerenge, timagwiritsa ntchito njira yapadera yowerengera, yomwe imawoneka motere: =F10*G8/F8.

Zofunika! Choyamba, 2 manambala odziwika u3buXNUMXbare amachulukirachulukira, kenako ndikugawidwa ndi XNUMX yotsalira.

Pogwiritsa ntchito lamulo losavutali, mukhoza mosavuta komanso mophweka kuwerengera kuchuluka kwa ndalamazo.

Kutsiliza

Nkhaniyi idakambirana njira zingapo zopezera kuchuluka kwa mizere mu Excel spreadsheet. Njira iliyonse ili ndi ubwino wake ndi zovuta zake. Kugwiritsa ntchito njira ya masamu ndiyo njira yosavuta kugwiritsa ntchito, koma ndiyoyenera kuigwiritsa ntchito pogwira ntchito ndi chidziwitso chochepa. Pogwira ntchito ndi kuchuluka kwa deta, kumasulira kwachangu ndikoyenera, komanso ntchito ya SUM.

Siyani Mumakonda