Yadda ake lissafin adadin a jere a cikin Excel. Hanyoyi 3 don Kirga Jimillar Lambobi a Layin Excel

Yin aiki tare da bayanan tebur, masu amfani galibi suna buƙatar ƙididdige adadin mai nuna alama. Sau da yawa waɗannan alamun sunaye ne na layin da ake buƙata don taƙaita duk bayanan da ke cikin sel. Daga labarin za ku koyi duk hanyoyin da ke ba ku damar aiwatar da wannan hanya.

Jimlar ƙimar a jere

Kuna iya sake fasalin tsarin tattara ƙimar a jere ta amfani da hanyoyi masu zuwa:

  • dabarar lissafi;
  • ta atomatik;
  • ayyuka daban-daban.

Kowace waɗannan hanyoyin an raba su zuwa ƙarin hanyoyin. Bari mu yi magana da su dalla-dalla.

Hanyar 1: dabarar lissafi

Da farko, bari mu gano yadda, ta amfani da dabarar lissafi, yana yiwuwa a taƙaita a jere. Bari mu bincika komai tare da takamaiman misali. Bari mu ce muna da tebur da ke nuna kudaden shiga na shaguna 5 akan wasu kwanakin. Sunayen kantuna sunayen layukan ne. Kwanan wata sunaye ne.

Yadda ake lissafin adadin a jere a cikin Excel. Hanyoyi 3 don Kirga Jimillar Lambobi a Layin Excel
1

Manufar: don ƙididdige yawan adadin kuɗin shiga na farkon kanti na kowane lokaci. Don cimma wannan burin, ya zama dole don ƙara duk sel na jere masu alaƙa da wannan kantin. Tafiya tayi kama da haka:

  1. Mun zaɓi tantanin halitta inda sakamakon zai bayyana a nan gaba. Shigar da alamar "=" a cikin tantanin halitta. Muna danna LMB akan tantanin halitta na farko a wannan layin mai dauke da alamomin lambobi. Mun lura cewa bayan danna maballin tantanin halitta an nuna su a cikin tantanin halitta don ƙididdige sakamakon. Shigar da alamar "+" kuma danna kan tantanin halitta na gaba a jere. Muna ci gaba da musanya alamar "+" tare da daidaitawar sel na layin layin farko. A sakamakon haka, muna samun dabara: =B3+C3+D3+E3+F3+G3+H3.
Yadda ake lissafin adadin a jere a cikin Excel. Hanyoyi 3 don Kirga Jimillar Lambobi a Layin Excel
2
  1. Bayan aiwatar da duk magudi, danna "Shigar".
  2. Shirya! An nuna sakamakon a cikin tantanin halitta wanda muka shigar da dabara don ƙididdige adadin.
Yadda ake lissafin adadin a jere a cikin Excel. Hanyoyi 3 don Kirga Jimillar Lambobi a Layin Excel
3

Kula! Kamar yadda kake gani, wannan hanyar a bayyane take kuma mai sauƙi, amma tana da babban lahani. Aiwatar da wannan hanya yana ɗaukar lokaci mai yawa. Bari mu yi la'akari da saurin bambance-bambancen taƙaitawa.

Hanyar 2: AutoSum

Amfani da autosum hanya ce da ta fi wacce aka tattauna a sama da sauri. Tafiya tayi kama da haka:

  1. Amfani da LMB da aka danna, muna zaɓar duk sel na jere na farko waɗanda ke da bayanan lamba. Mun matsa zuwa sashin "Gida", wanda yake a saman maƙunsar bayanai. Mun sami toshe na umarni "Editing" kuma danna kan abin da ake kira "Editing".
    Yadda ake lissafin adadin a jere a cikin Excel. Hanyoyi 3 don Kirga Jimillar Lambobi a Layin Excel
    4

Shawara! Wani zaɓi shine zuwa sashin "Formulas" kuma danna maɓallin "AutoSum" da ke cikin "Labarun Ayyuka" toshe. Zabi na uku shine a yi amfani da haɗin maɓalli "Alt" + "=" bayan zaɓin tantanin halitta.

Yadda ake lissafin adadin a jere a cikin Excel. Hanyoyi 3 don Kirga Jimillar Lambobi a Layin Excel
5
  1. Ko da wane zaɓi kuka nema, ƙimar lamba ta bayyana a hannun dama na sel da aka zaɓa. Wannan lambar ita ce jimillar makin jere.
Yadda ake lissafin adadin a jere a cikin Excel. Hanyoyi 3 don Kirga Jimillar Lambobi a Layin Excel
6

Kamar yadda kuke gani, wannan hanyar tana yin taƙaitawa a cikin layi da sauri fiye da na sama. Babban koma baya shine cewa sakamakon yana nunawa kawai zuwa dama na kewayon da aka zaɓa. Domin a nuna sakamakon a kowane wuri da aka zaɓa, ya zama dole a yi amfani da wasu hanyoyi.

Hanyar 3: aikin SUM

Yin amfani da haɗe-haɗen aikin maƙunsar bayanai da ake kira SUM ba shi da lahani na hanyoyin da aka tattauna a baya. SUM aikin lissafi ne. Ayyukan mai aiki shine taƙaita ƙimar lambobi. Gabaɗaya hangen mai aiki: = SUM (lamba1, lamba2,…).

Muhimmin! Hujja ga wannan aikin na iya zama ko dai ƙimar lambobi ko daidaitawar tantanin halitta. Matsakaicin adadin mahawara shine 255.

Koyarwar mataki-mataki tayi kama da haka:

  1. Muna yin zaɓi na kowane tantanin halitta mara komai akan takardar aiki. A ciki za mu nuna sakamakon jimlar. Yana da mahimmanci a lura cewa ana iya kasancewa har ma a kan takardar aikin daban na takaddar. Bayan yin zaɓin, danna maɓallin "Saka Aiki", wanda yake kusa da layin don shigar da dabaru.
Yadda ake lissafin adadin a jere a cikin Excel. Hanyoyi 3 don Kirga Jimillar Lambobi a Layin Excel
7
  1. An nuna ƙaramin taga mai suna "Wizard Aiki" akan allon. Fadada jeri kusa da rubutun "Kategori:" kuma zaɓi kashi "Mathematical". Ƙananan ƙasa a cikin jerin "Zaɓi aiki:" mun sami afaretan SUM kuma danna kan shi. Bayan aiwatar da duk magudi, danna maɓallin "Ok" wanda yake a ƙasan taga.
Yadda ake lissafin adadin a jere a cikin Excel. Hanyoyi 3 don Kirga Jimillar Lambobi a Layin Excel
8
  1. Wani taga mai suna "Ayyukan Hujja" ya bayyana akan nunin. A cikin fanko filin "Lambar 1" shigar da adireshin layin, ƙimar da kuke son ƙarawa. Don aiwatar da wannan hanya, mun sanya mai nuni a cikin wannan layin, sannan, ta amfani da LMB, za mu zaɓi gabaɗayan kewayon tare da ƙimar lambobi. Bayan aiwatar da duk manipulations, danna kan "Ok".
Yadda ake lissafin adadin a jere a cikin Excel. Hanyoyi 3 don Kirga Jimillar Lambobi a Layin Excel
9
  1. Shirya! An nuna sakamakon taƙaitawar a cikin tantanin halitta da aka zaɓa da farko.
Yadda ake lissafin adadin a jere a cikin Excel. Hanyoyi 3 don Kirga Jimillar Lambobi a Layin Excel
10

Ayyukan SUM baya aiki

Wani lokaci yakan faru cewa mai aikin SUM baya aiki. Babban abubuwan da ke haifar da rashin aiki:

  • Tsarin lamba mara daidai (rubutu) lokacin shigo da bayanai;
  • kasancewar ɓoyayyun haruffa da sarari a cikin sel masu ƙimar lambobi.

Yana da kyau a lura! Ƙimar lambobi koyaushe suna daidai daidai kuma bayanan rubutu koyaushe suna da barata-hagu.

Yadda ake nemo jimlar mafi girma (ƙananan) ƙima

Bari mu gano yadda za a lissafta jimlar mafi ƙanƙanta ko mafi girma dabi'u. Misali, muna buƙatar taƙaita mafi ƙarancin ƙima uku ko uku.

Yadda ake lissafin adadin a jere a cikin Excel. Hanyoyi 3 don Kirga Jimillar Lambobi a Layin Excel
11

GREATEST afaretan yana ba ku damar dawo da matsakaicin maki daga bayanan da aka zaɓa. Hujja ta 2 ta fayyace wace awo za a dawo. A cikin misalinmu na musamman, dabarar ta yi kama da haka: =СУММ(НАИБОЛЬШИЙ(B2:D13;{1;2;3})).

Neman mafi ƙarancin ƙima yana aiki ta hanya ɗaya, kawai SMALL aikin ana amfani dashi maimakon GREATEST afareta. Tsarin tsari yayi kama da haka: =СУММ(НАИМЕНЬШИЙ(B2:D13;{1;2;3})).

Miqewa dabara/aiki zuwa wasu layuka

Mun gano yadda ake ƙididdige adadin jimlar sel a layi ɗaya. Bari mu gano yadda ake aiwatar da tsarin taƙaitawa akan duk layuka na tebur. Rubutun ƙididdiga da hannu da shigar da ma'aikacin SUM dogayen hanyoyi ne marasa inganci. Mafi kyawun bayani shine a shimfiɗa aikin ko dabara zuwa adadin layin da ake so. Tafiya tayi kama da haka:

  1. Muna lissafin adadin ta amfani da ɗayan hanyoyin da ke sama. Matsar da alamar linzamin kwamfuta zuwa ƙananan firam ɗin dama na tantanin halitta tare da nunin sakamako. Siginan kwamfuta zai ɗauki siffar ƙaramar alamar duhu da ƙari. Riƙe LMB kuma ja dabarar zuwa ƙasan farantin.
Yadda ake lissafin adadin a jere a cikin Excel. Hanyoyi 3 don Kirga Jimillar Lambobi a Layin Excel
12
  1. Shirya! Mun taƙaita sakamakon duk lakabi. Mun sami wannan sakamakon saboda gaskiyar cewa lokacin yin kwafin dabarar, ana canza adireshin. Matsalolin daidaitawa shine saboda gaskiyar cewa adireshi dangi ne.
Yadda ake lissafin adadin a jere a cikin Excel. Hanyoyi 3 don Kirga Jimillar Lambobi a Layin Excel
13
  1. Don layi na 3, dabarar tayi kama da: =B3+C3+D3+E3+F3+G3+H3.
Yadda ake lissafin adadin a jere a cikin Excel. Hanyoyi 3 don Kirga Jimillar Lambobi a Layin Excel
14

Yadda ake lissafin jimlar kowane sahu na Nth.

A kan takamaiman misali, za mu bincika yadda ake ƙididdige jimlar kowane sahu na Nth. Misali, muna da tebur wanda ke nuna ribar yau da kullun na kanti na wani ɗan lokaci.

Yadda ake lissafin adadin a jere a cikin Excel. Hanyoyi 3 don Kirga Jimillar Lambobi a Layin Excel
15

Aiki: don ƙididdige ribar mako-mako na kowane mako. Mai ba da sabis na SUM yana ba ka damar tara bayanai ba kawai a cikin kewayo ba, har ma a cikin jeri. Anan ya zama dole a yi amfani da ma'aikacin taimako OFFSET. Ma'aikacin OFFSET yana ƙayyadad da gardama da yawa:

  1. Batu na farko. An shigar da Cell C2 azaman cikakken tunani.
  2. Yawan matakan ƙasa.
  3. Yawan matakai zuwa dama.
  4. Yawan matakan ƙasa.
  5. Adadin ginshiƙai a cikin tsararru. Buga batu na ƙarshe na tsararrun alamomi.

Mun ƙare da dabara mai zuwa na makon farko: =СУММ(СМЕЩ($C$2;(СТРОКА()-2)*5;0;5;1)). A sakamakon haka, ma'aikacin jimlar zai tara duk ƙimar lambobi biyar.

Jimlar 3-D, ko aiki tare da zanen gado da yawa na littafin aikin Excel

Don ƙidaya lambobi daga sifar kewayo iri ɗaya a cikin adadin takaddun aiki, dole ne a yi amfani da maƙasudi na musamman da ake kira "Reference 3D". Bari mu ce a kan duk takardun aikin littafin akwai faranti tare da bayanai na mako. Muna buƙatar haɗa shi duka tare da kawo shi zuwa adadi na kowane wata. Don farawa, kuna buƙatar kallon bidiyo mai zuwa:

Muna da faranti guda huɗu iri ɗaya. Hanyar da aka saba lissafin riba tayi kama da haka: =СУММ(неделя1!B2:B8;неделя2!B2:B8;неделя3!B2:B8;неделя4!B2:B8). Anan, kewayon sel suna aiki azaman muhawara.

Tsarin jimlar 3D yayi kama da haka: =SUM(mako1:week4!B2:B8). Ya ce a nan an yi taƙaice a cikin jeri B2: B8, waɗanda ke kan takaddun aiki: mako (daga 1 zuwa 4). Akwai haɓaka mataki-mataki a cikin adadin takardar aikin da ɗaya.

Jimlar tare da yanayi da yawa

Akwai lokutan da mai amfani ke buƙatar magance matsala wanda ke ƙayyadaddun sharuɗɗa biyu ko fiye kuma yana buƙatar ƙididdige jimlar ƙimar lambobi bisa ga ma'auni daban-daban. Don aiwatar da wannan hanya, yi amfani da aikin "= SUMMESLIMN".

Yadda ake lissafin adadin a jere a cikin Excel. Hanyoyi 3 don Kirga Jimillar Lambobi a Layin Excel
16

Koyarwar mataki-mataki tayi kama da haka:

  1. Da farko, an kafa tebur.
  2. Yana zaɓar tantanin halitta a cikinsa za a nuna sakamakon taƙaitawa.
  3. Matsar zuwa layi don shigar da dabaru.
  4. Muna shigar da ma'aikacin: =SUMMAESLIMN.
  5. Mataki zuwa mataki, muna shigar da kewayon ƙari, kewayon yanayin1, yanayin1 da sauransu.
  6. Bayan aiwatar da duk magudi, danna "Shigar". Shirya! An yi lissafin.

Yana da kyau a lura! Dole ne a sami mai rarrabawa a cikin nau'in semicolon ";" tsakanin muhawarar mai aiki. Idan ba a yi amfani da wannan iyakance ba, to, maƙunsar za ta haifar da kuskure da ke nuna cewa an shigar da aikin ba daidai ba.

Yadda ake lissafin adadin adadin

Yanzu bari muyi magana game da yadda za a lissafta daidai adadin adadin. Hanya mafi sauƙi, wadda duk masu amfani za su fahimta, ita ce amfani da ka'idar rabo ko "square". Ana iya fahimtar ma'anar daga hoton da ke ƙasa:

Yadda ake lissafin adadin a jere a cikin Excel. Hanyoyi 3 don Kirga Jimillar Lambobi a Layin Excel
17

An nuna jimlar adadin a cikin tantanin halitta F8 kuma yana da darajar 3060. A takaice dai, wannan shine kashi dari bisa dari, kuma muna buƙatar gano yawan ribar da Sasha ta samu. Don ƙididdigewa, muna amfani da tsari na musamman na rabo, wanda yayi kama da haka: =F10*G8/F8.

Muhimmin! Da farko, 2 sanannun ƙimar lambobi u3buXNUMXbare sun ninka diagonally, sa'an nan kuma an raba su ta sauran darajar XNUMXrd.

Yin amfani da wannan ƙa'ida mai sauƙi, zaka iya ƙididdige adadin adadin cikin sauƙi da sauƙi.

Kammalawa

Labarin ya tattauna hanyoyi da yawa don samun jimlar bayanan jeri a cikin ma'auni na Excel. Kowace hanya tana da nata amfani da rashin amfani. Yin amfani da dabarar lissafi ita ce hanya mafi sauƙi don amfani, amma ya fi dacewa a yi amfani da shi yayin aiki da ƙananan bayanai. Don aiki tare da adadi mai yawa na bayanai, ƙaddamarwa ta atomatik ya dace sosai, da kuma aikin SUM.

Leave a Reply