Yin amfani da Sharuɗɗa a Microsoft Excel

Microsoft Excel ba edita kawai ba ne, har ma aikace-aikace ne mai ƙarfi don lissafin da yawa. A ƙarshe amma ba mafi ƙaranci ba, wannan damar ta bayyana da godiya ga ayyukan ginannun ayyuka. Tare da taimakon wasu ayyuka (masu aiki), zaku iya tantance yanayin lissafin, waɗanda ake kira ma'auni. Bari mu koya daki daki daki yadda zaka iya amfani dasu yayin aiki a Excel.

Ka'idojin Aikace-aikacen

Sharuɗɗan yanayi ne wanda shirin ya aiwatar da wasu ayyuka. Ana amfani da su da yawa cikin ayyukan ginannun. Sunayensu galibi suna dauke da magana IF. Ga wannan rukunin masu aiki, da farko, wajibi ne a san shi TAMBAYA, COUNTIMO, ZAMU CIGABA, SUMMESLIMN. Baya ga ginannun masu aiki, ana amfani da sharudda a cikin Excel don tsara yanayin. Yi la'akari da amfanirsu lokacin aiki tare da kayan aikin kayan aikin wannan tebur na kayan aiki a cikin dalla-dalla daki-daki.

TAMBAYA

Babban aikin mai aiki TAMBAYAmallakar kungiyar ƙididdigar ƙididdigewa shine ƙididdigar yawan ƙwayoyin sel waɗanda suka mamaye wani yanayin da aka bayar. Syntax kamar haka:

= COUNTIF (kewa; rarrabuwa)

Kamar yadda kake gani, wannan ma'aikacin yana da hujja biyu. "Range" wakiltar adireshin jerin abubuwan abubuwa akan takardar wanda za'a kirga.

"Sharhin" - wannan wata mahawara ce da ke nuna yanayin abin da daidai sel ƙwayoyin da aka ƙayyade dole su ƙunshi don a saka su cikin ƙidaya. A matsayin misali, magana lamba, rubutu, ko hanyar haɗi zuwa tantanin da ya ƙunshi cancanta. A wannan yanayin, don nuna alamar, zaku iya amfani da haruffan masu zuwa: "<" (kasa), ">" (ƙari), "=" (daidai), "" (ba daidai ba) Misali, idan ka ayyana magana "<50", sannan kawai abubuwanda aka ƙayyade da hujja zasuyi la'akari lokacin ƙididdige su "Range", a cikin abin da ƙididdigar ƙididdiga ta kasa da 50. Amfani da waɗannan alamun don nuna sigogi zai dace da duk sauran zaɓuɓɓuka, wanda za'a tattauna a wannan darasin da ke ƙasa.

Yanzu bari mu kalli wani ingantaccen misalin yadda wannan ma'aikaci yake aiki a aikace.

Don haka, akwai tebur inda aka gabatar da kudaden shiga daga kantuna guda biyar a mako guda. Muna buƙatar gano adadin kwanakin don wannan lokacin a cikin Shagon 2 kudin shiga daga tallace-tallace ya wuce 15,000 rubles.

1. Zaɓi kashi na cikin abin da mai aikin zai fitar da sakamakon lissafin. Bayan haka, danna kan gunkin "Saka aikin".



2. Farawa Wizards na Aiki. Mun matsa zuwa katangar "Na lissafi". A wurin ne muka samo kuma muka haskaka sunan "COUNTIF". Saika danna maballin. "Ok".



3. Ana kunna taga gardamar na bayanin da ke sama. A fagen "Range" wajibi ne a nuna yankin sel wanda za'a yi lissafin. A cikin yanayinmu, ya kamata mu haskaka abubuwan da ke cikin layin "Shagon 2", wanda acikinsa ake samun kudaden shigar da rana. Mun sanya siginan siginan kwamfuta a cikin ƙayyadadden filin kuma, riƙe maɓallin linzamin kwamfuta na hagu, zaɓi zaɓin tsararren da ke cikin teburin. Adireshin zaɓaɓɓun tsararren aka nuna a taga.

   A filin na gaba "Sharhin" kawai buƙatar saita sashin zaɓi na kai tsaye. A cikin batunmu, muna buƙatar ƙidaya waɗancan abubuwan na teburin wanda darajar ta wuce 15000. Saboda haka, ta amfani da maballin, muna fitar da magana cikin filin da aka ƙayyade. ">15000".

   Bayan an gama amfani da waɗannan maɓallan da ke sama, danna maballin "Ok".



4. Shirin yana kirgawa kuma yana nuna sakamako a cikin takardar da aka zaba kafin kunnawa Wizards na Aiki. Kamar yadda kake gani, a wannan yanayin, sakamakon ya kasance daidai da 5. Wannan yana nufin cewa a cikin zaɓaɓɓun tsararru a cikin sel biyar akwai dabi'u a cikin sama da 15,000. Wannan shine, zamu iya yanke hukunci cewa a cikin Shagon 2 cikin kwanaki biyar daga cikin binciken bakwai, kudaden shiga ya wuce 15,000 rubles.

Darasi: Ingantaccen fasalin

COUNTIMO

Ayyuka na gaba wanda ke aiki tare da ma'auni shine COUNTIMO. Hakanan yana cikin ƙungiyar ƙididdigar masu aiki. Aiki COUNTIMO yana kirga sel a cikin takamaiman tsari wanda ya gamsar da takamaiman tsarin yanayi. Gaskiya ne cewa zaku iya tantancewa ba ɗaya bane, amma awoyi da yawa, kuma ya bambanta wannan ma'aikacin daga wanda ya gabata. Gaskiyar magana kamar haka:

= COUNTIME (yanayi_range1; sharadin1; sharadin_range2; sharadin2; ...)

"Yanayin Yanayi" daidai yake da hujja ta farko ta sanarwa da ta gabata. Wato, haɗi ne zuwa yankin da za'a lissafta ƙwayoyin da suka gamsar da yanayin da aka ƙayyade. Wannan mai ba da izini zai baka damar ambaton wurare da yawa a lokaci guda.

"Yanayi" wakiltar wani sharudda wanda ke tantance abubuwanda za'ayi amfani dasu daga jerin bayanan da zasu dace kuma wanda ba zai. Kowane yanki na bayanan da aka bayar dole ne a ƙayyade shi daban, koda kuwa ya dace. Yana da matuƙar mahimmanci duk kayan aikin da aka yi amfani da su azaman yankuna suna da adadin lambobi da layuka guda.

Don saita sigogi iri-iri na yanki ɗaya na bayanai, alal misali, don kirga yawan ƙwayoyin da abin da dabi'u ya fi girma fiye da wani lamba, amma ƙasa da wata lambar, ya kamata a ɗauka azaman muhawara "Yanayin Yanayi" saka iri ɗaya tsararru sau da yawa. Amma a lokaci guda, kamar yadda muhawara mai dacewa "Yanayi" daban-daban sharudda ya kamata a nuna.

Ta amfani da misali na tebur guda tare da kudaden shiga na mako-mako, bari mu ga yadda yake aiki. Muna buƙatar gano adadin kwanakin mako yayin da kudin shiga a duk ƙayyadaddun kantin sayar da kayan da aka ƙayyade sun isa matsayin da aka kafa don su. Ka'idodin kudaden shiga sune kamar haka:

• Shagon 1 - 14,000 rubles;
• Shagon 2 - 15,000 rubles;
• Shagon 3 - 24,000 rubles;
• Shagon 4 - 11,000 rubles;
• Shagon 5 - 32,000 rubles.

1. Don cim ma aikin da ke sama, zaɓi ɓangaren takardar aiki tare da siginan kwamfuta, inda za a nuna sakamakon sarrafa bayanai. COUNTIMO. Danna alamar "Saka aikin".



2. Je zuwa Mayan fasalinmatsa zuwa wurin sake "Na lissafi". Jerin ya kamata su samo suna COUNTIMO kuma zaɓi shi. Bayan yin aikin da aka ƙayyade, kuna buƙatar danna maɓallin "Ok".



3. Bayan aiwatar da algorithm ɗin na sama na ayyuka, sai taga mahawara ta buɗe COUNTIMO.

   A fagen "Ranan Yanayi 1" shigar da adireshin layin inda bayanai kan kudaden shiga na Store 1 na mako ke wurin. Don yin wannan, sanya siginan kwamfuta a cikin filin kuma zaɓi jere masu dacewa a teburin. Ana nuna ayyukan daidaitawa a cikin taga.

   Idan akai la'akari da cewa don Store 1 farashin yau da kullun na kudaden shiga shine 14,000 rubles, to, a fagen "Yanayi 1" rubuta magana ">14000".

   A cikin filayen "Matsakaicin yanayi 2 (3,4,5)" Ya kamata a shigar da daidaitawar layin tare da kudaden shiga na mako-mako na Store 2, Adana 3, Store 4 da Store 5, bi da bi, ana yin aikin bisa ga algorithm guda ɗaya kamar na farkon gardamar wannan rukunin.

   A cikin filayen "Yanayi na 2", "Yanayi 3", "Yanayi4" da "Yanayi5" mun shigar da dabi'u daidai ">15000", ">24000", ">11000" da ">32000". Kamar yadda zaku iya tsammani, waɗannan dabi'un suna dacewa da tazara tazara wacce ta wuce al'ada don kantin sayar da daidai.

   Bayan kun shigar da dukkan mahimman bayanan (jimlar 10), danna maɓallin "Ok".



4. Shirin yana kirgawa kuma yana nuna sakamako akan allon. Kamar yadda kake gani, yayi daidai da lamba 3. Wannan yana nuna cewa a cikin kwana uku daga makon da aka bincika kudaden shiga a duk hanyoyin sun mamaye ka'idodin da aka shimfida musu.

Yanzu bari mu canza aikin. Ya kamata mu lissafa adadin kwanakin da Shagon 1 suka samu kudaden shiga sama da 14,000 rubles, amma ƙasa da 17,000 rubles.

1. Mun sanya siginan kwamfuta a cikin kashi inda za a samar da fitowar akan takaddar sakamako. Danna alamar "Saka aikin" kan aikin yanki na takardar.



2. Tunda kwanan nan munyi amfani da tsari COUNTIMO, yanzu ba lallai ne ku shiga rukunin ba "Na lissafi" Wizards na Aiki. Ana iya samun sunan wannan ma'aikacin a cikin rukunin "10 da aka yi amfani da kwanan nan". Zaɓi shi kuma danna maballin. "Ok".



3. Farashin muhawara da aka saba da shi yana buɗewa. COUNTIMO. Sanya siginan kwamfuta a cikin filin "Ranan Yanayi 1" kuma, rike maɓallin linzamin kwamfuta na hagu, zaɓi duk sel waɗanda suke ɗauke da kudaden shiga ta kwanakin Store 1. Suna nan a cikin layin, wanda ake kira "Shagon 1". Bayan haka, daidaitawar yankin da aka ƙayyade za a nuna a cikin taga.

   Na gaba, saita siginan kwamfuta a cikin filin "Yanayi 1". Anan muna buƙatar nuna ƙananan ƙimar dabi'u a cikin sel waɗanda zasu shiga cikin lissafin. Tace magana ">14000".

   A fagen "Yanayin Yanayi 2" shigar da adireshin iri ɗaya a cikin hanyar da aka shigar a fagen "Ranan Yanayi 1", wato, kuma mun sake shiga cikin daidaitawar sel tare da ƙimar kudaden shiga don mafita ta farko.

   A fagen "Yanayi na 2" nuna babba iyakar zaɓi: "<17000".

   Bayan duk ayyukan da aka ƙayyade sun gama, danna maɓallin "Ok".



4. Shirin yana ba da sakamakon lissafin. Kamar yadda kake gani, darajar ƙarshe shine 5. Wannan yana nuna cewa a cikin kwanaki 5 daga cikin bakwai da aka yi nazari, kudaden shiga cikin shagon farko sun kasance cikin kewayon daga 14,000 zuwa 17,000 rubles.

ZAMU CIGABA

Wani ma'aikacin da ke amfani da ma'auni shine ZAMU CIGABA. Ba kamar ayyukan da suka gabata ba, yana cikin tsarin lissafin masu aiki. Aikinta shine taƙaita bayanai a cikin sel waɗanda ke dacewa da takamaiman yanayin. Gaskiyar magana kamar haka:

= LABARI (kewa; tantancewa; [sum_range])

Hujja "Range" yana nuna yankin sel wanda za'a bincika don bin ka'idodin. A zahiri, an saita shi ta hanyar ƙa'ida ɗaya kamar matsayin hujja na aiki na wannan sunan TAMBAYA.

"Sharhin" - wata hujja ce da ake buqatar tantance zabin sel daga takamaiman bayanan bayanan da za a kara. Ka'idojin tantancewa daidai suke da irin wannan muhawara na wadanda suka gabata, wanda muka bincika a sama.

"Takaitaccen Tarihi" Wannan shine hujja ba na tilas ba. Yana nuna takamaiman yanki na tsarin da za'a aiwatar da abin tarawa. Idan kun ƙetare kuma ba ku ƙayyade shi ba, to ta hanyar tsoho ana ɗauka cewa daidai yake da ƙimar gardamar da ake buƙata "Range".

Yanzu, kamar yadda koyaushe, yi la'akari da aikace-aikacen wannan ma'aikacin a aikace. An kafa a kan tebur iri ɗaya, mun fuskanci aikin ƙididdige yawan kudaden shiga a Store 1 don farawa daga Maris 11, 2017.

1. Zaɓi wayar wanda sakamakon zai fito. Danna alamar. "Saka aikin".



2. Je zuwa Mayan fasalin a toshe "Ilmin lissafi" Nemo da kuma nuna sunan SUMMS. Latsa maballin "Ok".



3. Farashin muhawara na aiki yana farawa ZAMU CIGABA. Tana da filaye guda uku wadanda zasu dace da muhawara na mai aikin da aka ƙayyade.

   A fagen "Range" shigar da yankin teburin wanda za a bincika abubuwan da za a bincika don bin ka'idodin yanayin. A cikin lamarinmu, zai zama jerin lokutan kwanan wata. Sanya siginan kwamfuta a cikin wannan filin kuma zaɓi duk ƙwayoyin da ke ɗauke da kwanakin.

   Tunda muna buƙatar ƙara adadin kawai daga farawa daga Maris 11, a cikin filin "Sharhin" fitar da darajar ">10.03.2017".

   A fagen "Takaitaccen Tarihi" kuna buƙatar tantance yankin wanda ƙididdigar su da suka dace da ƙayyadaddun halaye za a taƙaita su. A cikin yanayinmu, waɗannan sune ƙimar kudaden shiga "Shagon1". Zaɓi jerin abubuwan da aka haɗa su.

   Bayan an shigar da bayanan da aka ƙayyade, danna kan maɓallin "Ok".



4. Bayan haka, sakamakon sarrafa bayanai ta hanyar aikin za'a nuna shi a cikin bayanan da aka ayyana a baya. ZAMU CIGABA. A cikin lamarinmu, daidai yake da 47921.53. Wannan yana nufin cewa farawa daga Maris 11, 2017, kuma har zuwa ƙarshen lokacin binciken, jimlar kuɗin ga Shagon 1 ya kai 47,921.53 rubles.

SUMMESLIMN

Mun ƙare nazarin masu aiki waɗanda ke amfani da ma'auni, suna mai da hankali kan ayyuka SUMMESLIMN. Manufar wannan aikin ilimin lissafi shine taƙaita ƙimar wuraren da aka nuna teburin, zaɓa bisa ga sigogi da yawa. Gaskiyar ma'anar mai aiki da aka ƙaddara ita ce:

= SUMMER (sum_range; condition_range1; condition1; condition_range2; condition2; ...)

"Takaitaccen Tarihi" - wannan ita ce gardama, wacce ita ce adireshin jigon da a ciki waɗanda sel waɗanda ke haɗuwa da wani ƙuduri za a ƙara.

"Yanayin Yanayi" - wata mahawara, wacce take tarin bayanai, aka bincika don dacewa da yanayin;

"Yanayi" - gardamar dake wakiltar zaɓin zaɓi don ƙari.

Wannan aikin yana ɗaukar nauyin aiki tare da wasu kamfanoni masu kama iri ɗaya lokaci ɗaya.

Bari mu ga yadda wannan ma'aikacin aikin ya dace da warware matsaloli a cikin mahallin teburin kuɗin shiga kasuwancinmu a kantuna. Muna buƙatar lissafin kuɗin da Shagon 1 ya kawo na lokacin daga Maris 09 zuwa Maris 13, 2017. A wannan yanayin, lokacin da ake tattara kudaden shiga, waɗannan kwanakin kawai ya kamata a la'akari dasu, wanda kudaden shiga ya wuce 14,000 rubles.

1. Hakanan, zaɓi tantanin don nuna jimlar kuma danna kan gunkin "Saka aikin".



2. A Mayen aikiDa farko dai, mun matsa zuwa katangar "Ilmin lissafi", kuma a nan ne za mu zabi wani abu da ake kira SUMMESLIMN. Latsa maballin. "Ok".



3. Ana buɗe ƙaddamar da mahawara na mai aiki, sunan wanda aka nuna a sama.

   Sanya siginan kwamfuta a cikin filin "Takaitaccen Tarihi". Ba kamar waɗannan muhawara masu zuwa ba, wannan nau'in ma yana nuni zuwa ga ɗimbin kyawawan halaye inda za a taƙaita bayanan da suka dace da ƙayyadaddun halaye. Sannan zaɓi yankin jere "Shagon1", wanda acikin hanyoyin samun kudaden shiga yake kasancewa.

   Bayan an nuna adreshin a taga, je filin "Ranan Yanayi 1". Anan za mu buƙaci nuna alamun haɗakar murfin tare da kwanakin. Matsa maɓallin linzamin kwamfuta na hagu ka zaɓi duk kwanakin da ke cikin teburin.

   Sanya siginan kwamfuta a cikin filin "Yanayi 1". Sharadi na farko shi ne cewa zamu takaita bayanan ne ba da wuri ba sai a ranar 09 ga Maris. Sabili da haka, shigar da ƙimar ">08.03.2017".

   Mun matsa zuwa ga hujja "Yanayin Yanayi 2". Anan kuna buƙatar shigar da daidaitawa iri ɗaya da aka rubuta a filin "Ranan Yanayi 1". Muna yin wannan a daidai wannan hanya, shine, ta hanyar haskaka layin tare da kwanakin.

   Sanya siginan kwamfuta a cikin filin "Yanayi na 2". Sharadi na biyu shine cewa ranakun da za a kara yawan abin da zasu kawo dole ne su kasance ba sai bayan 13 ga Maris. Sabili da haka, muna rubuta wannan magana: "<14.03.2017".

   Ku tafi filin "Yanayin Yanayi 2". A wannan yanayin, muna buƙatar zaɓar tsararru ɗaya waɗanda aka shigar da adireshin su a matsayin tsarin tattarawa.

   Bayan da adireshin ƙayyadadden tsararren aka nuna a taga, je zuwa filin "Yanayi 3". Ganin cewa dabi'u kawai waɗanda darajar su ta wuce 14,000 rubles zasu shiga cikin tarawar, muna yin shigarwa na yanayin da ke gaba: ">14000".

   Bayan kammala aikin da ya gabata, danna maballin "Ok".



4. Shirin yana nuna sakamakon a kan takardar. Ya yi daidai da 62491,38. Wannan yana nufin cewa tsawon lokacin daga 9 ga Maris zuwa 13, Maris, 2017, yawan kuɗin shiga lokacin da aka ƙara shi a cikin kwanakin da ya wuce 14,000 rubles wanda ya kai 62,491.38 rubles.

Tsarin sharadi

Kayan aiki na karshe da muka bayyana, lokacin aiki tare da ka'idoji, tsarin tsari ne. Yana aiwatar da ƙayyadaddun nau'in Tsarin Kwayoyin da suka dace da yanayin da aka ƙayyade. Dubi misalin yin aiki tare da keɓaɓɓen tsari.

Mun zabi waɗancan sel a cikin tebur a shuɗi, inda ƙimar yau da kullun ta wuce 14,000 rubles.

1. Muna zaɓar ɗaukacin abubuwan abubuwa a cikin teburin, wanda ke nuna kudaden shiga a rana.



2. Matsa zuwa shafin "Gida". Danna alamar Tsarin Yanayisanya a cikin toshe Salo a kan tef. Lissafin ayyuka ya buɗe. Latsa shi a wuri "Airƙiri doka ...".



3. Ana kunna taga don ƙirƙirar tsarin tsarawa. A cikin zaɓi na nau'in mulkin, zaɓi "Haɗi kawai sel waɗanda suke ɗauke da". A cikin farkon filin toshe yanayin, daga jerin zaɓuka masu yiwuwa, zaɓi "Darajar tantanin halitta". A filin na gaba, zaɓi matsayin .Ari. A ƙarshe - saka ƙimar kanta, fiye da wanda kake son tsara abubuwan tebur. Muna da shi 14000. Don zaɓar nau'in tsarawa, danna maɓallin "Tsarin ...".



4. Ana kunna taga tsarawa. Matsa zuwa shafin "Cika". Daga zaɓuɓɓukan da aka gabatar don cike launuka, zaɓi shuɗi ta latsa hagu. Bayan an nuna launi da aka zaɓa a yankin Samfurodidanna maballin "Ok".



5. Tsarin mulkin tsara tsarawa zai dawo ta atomatik. A ciki kuma a cikin filin Samfurodi launin shuɗi yana nunawa. Anan muna buƙatar aiwatar da aiki guda ɗaya: danna maɓallin "Ok".



6. Bayan aikin ƙarshe, duk sel na zaɓaɓɓun tsararru, waɗanda suka ƙunshi lamba mafi girma fiye da 14000, za a cika su da shuɗi.

Discussedarin bayani game da damar tsara yanayin sharaɗi an tattauna a cikin labarin daban.

Darasi: Tsarin yanayi a Excel

Kamar yadda kake gani, ta amfani da kayan aikin da ke amfani da ƙa'idodi a cikin aikin su, Excel na iya magance matsalolin bambance bambancen. Wannan na iya zama, kamar yadda lissafin adadin da ƙimantawa, da tsara tsari, da aiwatar da wasu ayyuka da yawa. Babban kayan aikin da ke aiki a cikin wannan shirin tare da sharuɗa, wato, tare da wasu yanayi waɗanda wannan aikin ke gudana, wannan tsarin sahiban ayyuka ne, gami da tsara yanayin aiki.