Taron lissafi a Microsoft Excel

Wasu lokuta tambaya ga masu amfani da Excel shine yadda za a kara jimlar adadin ƙididdigar lambobi da yawa? Aikin zai zama mafi rikitarwa idan waɗannan rukunin ba su cikin raka'a ɗaya ba, amma an rarrabu. Bari mu nemo yadda zamu takaita su ta hanyoyi daban-daban.

Bugu da kari

Taron abubuwan ginshiƙai a cikin Excel na faruwa ne bisa ƙa'idodin ƙara bayanai a cikin wannan shirin. Tabbas, wannan hanya tana da wasu fasalulluka, amma su bangare ne kawai na tsarin gabaɗaya. Kamar kowane ɗan tara bayanai a cikin wannan tebur mai aiki, ƙari na ginshiƙai za'a iya yin ta amfani da dabara mai sauƙi na lissafi, ta amfani da aikin ginanniyar Excel SAURARA ko adadin mota.

Darasi: Lissafa adadin a cikin Excel

Hanyar 1: amfani da wadatarwa na atomatik

Da farko dai, bari mu kalli yadda ake tattara layuka a cikin Excel ta amfani da kayan aiki kamar auto-sum.

Misali, ɗauki tebur wanda ke nuna yawan kuɗin yau da kullun na kantuna guda biyar a cikin kwana bakwai. Bayanai ga kowane kantin yana iske a cikin wani shafi daban. Aikinmu zai kasance shine gano jimlar kudaden shiga daga wadannan kantuna na zamani. A saboda wannan dalili, kawai buƙatar ninka layuka.

1. Domin gano jimlar kudaden shiga na kwana 7 ga kowane shago daban-daban, muna amfani da adadin kudin mota. Zaɓi tare da siginan kwamfuta yayin riƙe maɓallin linzamin kwamfuta na hagu a cikin shafi "Shagon 1" dukkan abubuwanda suke dauke da dabi'un lambobi. To, kasance a cikin shafin "Gida"danna maballin "Autosum"located a kan kintinkiri a cikin saitunan rukuni "Gyara".



2. Kamar yadda kake gani, jimlar kudaden shiga na kwanaki 7 don mafita ta farko za a nuna a cikin tantanin a ƙarƙashin layin tebur.



3. Muna aiwatar da irin wannan aiki ta amfani da adadin atomatik zuwa duk sauran ginshiƙai waɗanda ke da bayanai akan kudaden shiga ga shagunan.

   Idan akwai layuka da yawa, to ba za ku iya lissafta daban-daban ba kowane ɗayansu. Za mu yi amfani da alamar cikawa don kwafa dabarar da ke kunshe da adadin atomatik na farkon fitarwa zuwa raguna. Zaɓi ɓangaren inda fom ɗin yake. Tsaya a saman kusurwar dama ta dama. Ya kamata a canza shi da alamar cikewa, wanda yayi kama da gicciye. Sannan mun riƙe maɓallin linzamin kwamfuta na hagu kuma ja mai alamar mai a layi ɗaya da sunan shafi a ƙarshen teburin.



4. Kamar yadda kake gani, ƙimar kuɗin shiga na kwana 7 ga kowane kanti a ƙididdige shi.



5. Yanzu zamu buƙaci ƙara tare da sakamakon da aka samo don kowane kanti. Za'a iya yin wannan ta hanyar kuɗin ɗaya. Muna yin zaɓi tare da siginan kwamfuta tare da maɓallin linzamin kwamfuta na hagu wanda aka matse duk sel wanda darajar kuɗin shiga ga shagunan mutum yake, kuma ƙari da haka mun kama wani ɓoyayyen tantanin halitta zuwa dama daga gare su. Sannan mun danna maballin auto-sum akan kintinkiri wanda ya saba mana.



6. Kamar yadda kake gani, jimlar kudaden shiga na dukkan kantuna na kwana 7 za'a nuna a cikin wannan kwaron, wanda ya kasance gefen hagu na tebur.

Hanyar 2: yi amfani da tsari mai sauƙi na lissafi

Yanzu bari mu ga yadda zaku iya taƙaita ginshiƙan tebur ta amfani da dabarar lissafi kawai don waɗannan manufofin. Misali, zamu yi amfani da tebur daya wanda aka yi amfani dashi don bayyana hanyar farko.

1. Kamar lokacin ƙarshe, da farko, muna buƙatar ƙididdige kudaden shiga don kwana 7 don kowane shagon daban. Amma zamuyi wannan ta wata hanyar dabam. Zaɓi tantanin farko na wofi, wanda ke ƙarƙashin layin "Shagon 1", kuma saita alamar a wurin "=". Bayan haka, danna kan ainihin farkon wannan shafi. Kamar yadda kake gani, adireshinsa yana nuna kai tsaye a cikin tantanin don adadin. Bayan haka mun sanya alama "+" daga keyboard. Na gaba, danna kan sel na gaba a cikin wannan shafi. Don haka, canza hanyoyin haɗi zuwa abubuwan da ke cikin takardar tare da alama "+", aiwatar da dukkanin sel a cikin akwati.

   A cikin halinmu na musamman, an samar da tsari mai zuwa:

   = B2 + B3 + B4 + B5 + B6 + B7 + B8

   Tabbas, a kowane yanayi, yana iya bambanta dangane da wurin teburin akan takarda da adadin ƙwayoyin sel a cikin shafi.



2. Bayan an shigar da adreshin dukkanin abubuwan da ke cikin shafin, don nuna sakamakon taƙaita kudin shiga na kwana 7 a farkon fitarwa, danna maɓallin. Shigar.



3. Bayan haka zaka iya yin hanya iri ɗaya don sauran kantuna guda huɗu, amma zai kasance mafi sauƙi da sauri don tara bayanan a cikin sauran ginshiƙai ta amfani da alamar cikawa daidai yadda muka yi a cikin hanyar da ta gabata.



4. Yanzu muna buƙatar nemo adadin jimlar. Don yin wannan, zaɓi kowane ɓoyayyen abu a kan takardar da muke shirin nuna sakamakon, kuma sanya alama a ciki "=". Abu na gaba, a madadin haka muna kara sel wadanda adadin abubuwan lissafin da muka lissafta a baya suna can.

   Muna da tsari mai zuwa:

   = B9 + C9 + D9 + E9 + F9

   Amma wannan dabara ma mutum ne ga kowanne bangare.



5. Don samun sakamakon gabaɗaya na ƙara ginshiƙai, danna maballin Shigar a kan keyboard.

Ba shi yiwuwa a lura cewa wannan hanyar tana ɗaukar lokaci mai tsawo kuma tana buƙatar ƙarin ƙoƙari fiye da wacce ta gabata, tunda tana ƙunshe da danna kowane kwayar da ke buƙatar ɗaukar hoto don nuna adadin kuɗin shiga. Idan teburin yana da layuka da yawa, to wannan hanyar na iya zama mai ban tsoro. A lokaci guda, wannan hanyar tana da fa'ida ɗaya da ba za a iya tantancewa ba: ana iya nuna sakamakon a cikin kowane ɓoyayyen tantanin halitta akan takardar da mai amfani ya zaɓa. Lokacin amfani da tara kudi, babu irin wannan damar.

A aikace, ana iya hada wadannan hanyoyin guda biyu. Misali, don taƙaita sakamako a cikin kowane shafi kowane ɗaya ta amfani da abubuwan tara kuɗi, da kuma nuna jimlar darajar ta hanyar amfani da tsari na lissafi a cikin tantanin da mai amfani ya zaɓa.

Hanyar 3: amfani da aikin SUM

Rashin lalacewar hanyoyin biyu da ta gabata za a iya kawar da ita ta amfani da aikin ginanniyar aikin da ake kira SAURARA. Dalilin wannan ma'aikacin shine ainihin tattara lambobi. Tana cikin rukuni na ayyukan lissafi kuma yana da daidaitaccen tsarin magana:

= SUM (lamba1; lamba2; ...)

Muhawara, adadin wanda zai kai 255, lambobin ne taƙaitaccen adireshi ko adireshin sel a inda suke.

Bari mu ga yadda ake amfani da wannan aikin Excel a aikace, ta amfani da teburin kudaden shiga guda ɗaya don kantunan tallace-tallace guda biyar a cikin kwanaki 7 a matsayin misali.

1. Muna alama da abu akan takarda wanda za a nuna darajar kudin shiga ga shafi na farko. Danna alamar "Saka aikin", wanda yake gefen hagu na masarar dabara.



2. Kunna cigaba Wizards na Aiki. Kasancewa cikin rukuni "Ilmin lissafi"neman suna SAURARA, zaɓi shi kuma danna maballin "Ok" a kasan wannan taga.



3. An kunna aikin muhawara na ayyuka. Zai iya samun filayen har 255 tare da sunan "Lambar". Waɗannan layukan suna ƙunsar muhawara na mai aiki. Amma don yanayinmu, filin daya zai isa sosai.

   A fagen "Lambar1" so ku sanya masu tsara kewayon wanda ya ƙunshi ƙwayoyin shafi "Shagon 1". Wannan ana yin shi kawai. Mun sanya siginan kwamfuta a cikin akwatin taga muhawara. Na gaba, ta danna maɓallin linzamin kwamfuta na hagu, zaɓi duk ƙwayoyin da ke cikin akwati "Shagon 1"wanda ya ƙunshi dabi'un lambobi. Adireshin yana nan da nan aka nuna shi a cikin akwatin muhawara na taga a cikin hanyar daidaitawar hanyoyin da aka tsara. Latsa maballin "Ok" a kasan taga.



4. Revenueimar kuɗin shiga na kwana bakwai don kantin farko za a nuna shi nan da nan a cikin tantanin halitta wanda ya ƙunshi aikin.



5. Sannan zaku iya yin irin wannan aiki tare da aikin SAURARA kuma ga ragunan tebur, suna kirgawa a cikinsu adadin kuɗin shiga na kwana 7 don shagunan daban-daban. Algorithm na aiki zai zama daidai daidai kamar yadda aka bayyana a sama.

   Amma akwai zaɓi don sauƙaƙe aikin. Don yin wannan, yi amfani da alamar cika guda. Zaɓi tantanin da ya riga ya ƙunshi aiki SAURARA, kuma ja alamomin a layi ɗaya da kan layi na layi a ƙarshen tebur. Kamar yadda kake gani, a wannan yanayin, aikin SAURARA kofe daidai kamar yadda muka yi kwafin kwafin lissafi mai sauƙi.



6. Bayan haka, zaɓi tantanin sel a kan takarda wanda muke nufin nuna sakamakon lissafin gabaɗaya na duka shagunan. Kamar yadda a cikin hanyar da ta gabata, wannan na iya zama kowane ɓangaren takardar kyauta. Bayan wannan, a cikin sananniyar hanya, muna kira Mayan fasalin kuma matsa zuwa taga muhawara na aiki SAURARA. Dole ne mu cika filin "Lambar1". Kamar yadda yake a cikin maganar da ta gabata, mun saita siginan kwamfuta a cikin fagen, amma wannan lokacin tare da maɓallin linzamin kwamfuta na hagu, mun zaɓi ɗaukacin layukan kuɗin shiga na shagunan mutum. Bayan an shigar da adireshin wannan layin a cikin hanyar hada hanyar tattaunawa a cikin filin taga mai muhawara, danna maballin "Ok".



7. Kamar yadda kake gani, jimlar kudaden shiga ga dukkanin shagunan godiya ga aikin SAURARA An nuna shi a cikin tantanin da aka zaba a baya.

Amma wani lokacin akwai lokuta idan kuna buƙatar nuna duka sakamakon duka kantuna ba tare da taƙaita sakamakon matsakaici na ɗakunan ajiya ba. Sai dai itace cewa afareta SAURARA kuma zai iya, kuma magance wannan matsalar ta fi sauki fiye da sanya sigar da ta gabata ta wannan hanyar.

1. Kamar koyaushe, zaɓi wayar akan takarda inda sakamakon karshe zai kasance. Muna kira Mayan fasalin danna kan gunkin "Saka aikin".



2. Yana buɗewa Mayan fasalin. Kuna iya matsawa zuwa rukuni "Ilmin lissafi"amma idan kayi amfani da sanarwa kwanan nan SAURARAkamar yadda muka yi, za ku iya kasancewa a cikin rukuni "10 da aka yi amfani da kwanan nan" kuma zaɓi sunan da ake so. Dole ne ya kasance a wurin. Latsa maballin "Ok".



3. Da muhawara taga yana fara sake. Sanya siginan kwamfuta a cikin filin "Lambar1". Amma wannan lokacin, muna riƙe maɓallin linzamin kwamfuta na hagu kuma zaɓi ɗayan teburin gabaɗaya, wanda ya ƙunshi kudaden shiga ga duk kantuna masu talla gaba ɗaya. Saboda haka, adireshin duk kewayon tebur ya kamata ya kasance a fagen. A yanayinmu, yana da tsari mai zuwa:

   B2: F8

   Amma, tabbas, a kowane yanayi, adireshin zai bambanta. Ka'idodi na yau da kullun shine cewa daidaitawar hagu na hagu na tsararru zai zama na farko a cikin wannan adireshin, ƙananan sifa na dama kuma za su kasance na ƙarshe. Za a raba waɗannan masu gudanarwar:).

   Bayan an shigar da adireshin tsararru, danna maballin "Ok".



4. Bayan waɗannan ayyuka, sakamakon ƙari na bayanai za a nuna shi a cikin sel daban.

Idan muka yi la’akari da wannan hanyar daga zance na zahiri na fasaha, to ba za mu tara duniyoyi ba, amma duka tsararru ne. Amma sakamakon ya kasance iri ɗaya ne kamar an ɗora kowane shafi daban.

Amma akwai yanayi lokacin da kuke buƙatar ƙara sama ba duk ginshiƙan tebur ba, amma takamaiman wasu. Aikin zai zama mafi rikitarwa idan ba su yi kan juna ba. Bari mu kalli yadda ake yin wannan nau'in ƙarin ta amfani da mai ba da sabis na SUM ta amfani da misalin tebur ɗaya. Da ace kawai muna buƙatar ƙara ƙimar shafi "Shagon 1", "Shagon 3" da "Shagon 5". An buƙaci cewa an ƙididdige sakamakon ba tare da samar da ƙananan subtotals a cikin ginshiƙan ba.

1. Mun sanya siginan kwamfuta a cikin tantanin halitta inda za'a nuna sakamakon. Muna kiran taga muhawara na aiki SAURARA kamar yadda muka aikata shi a da.

   A cikin taga yana buɗewa a filin "Lambar1" shigar da adireshin kewayon bayanai a cikin shafi "Shagon 1". Muna yin wannan daidai kamar yadda ya gabata: saita siginan kwamfuta a cikin filin kuma zaɓi kewayon teburin da ya dace. A cikin filayen "Lambar2" da "Lamba 3" bi da bi, mun shigar da adreshin bayanan bayanai a cikin ginshiƙai "Shagon 3" da "Shagon 5". A cikin lamarinmu, ƙungiyar daidaitawar tana kamar haka:

   B2: B8
D2: D8
F2: F8

   To, kamar yadda koyaushe, danna maɓallin "Ok".



2. Bayan aiwatar da waɗannan ayyukan, sakamakon ƙara darajar kudaden shiga na shagunan uku cikin biyar za a nuna su a cikin maƙasudin manufa.

Darasi: Amfani da Misalin Wizard a Microsoft Excel

Kamar yadda kake gani, akwai manyan hanyoyi guda uku da za'a hada ginshikai a Excel: ta amfani da tara kudi, tsarin lissafi, da aiki SAURARA. Mafi sauki kuma mafi saurin zaɓi shine amfani da adadin kuɗi. Amma mafi ƙarancin sassauƙa kuma bai dace ba a duk halayen. Zaɓin mafi sauƙin sassauci shine amfani da dabarun lissafi, amma mafi ƙarancin sarrafa kansa kuma a wasu lokuta, tare da adadi mai yawa, aiwatarwa a aikace na iya ɗaukar lokaci mai yawa. Amfani da aiki SAURARA ana iya kiranta tsakiyar ƙasa "zinare" tsakanin waɗannan hanyoyi biyu. Wannan zaɓi yana da sauƙin sassauƙa da sauri.