Microsoft Excel ba kawai wani editan rubutu ba ne, amma kuma aikace-aikacen mafi girma ga ƙididdiga daban-daban. A ƙarshe amma ba kalla ba, wannan fasalin ya zo tare da fasalullura. Tare da taimakon wasu ayyuka (masu aiki), yana yiwuwa a saka ko da yanayin yanayin lissafi, wanda ake kira dakalai. Bari mu ƙara koyo game da yadda zaka iya amfani da su lokacin aiki a Excel.
Aiwatar da ma'auni
Sha'idodin su ne yanayin da shirin ke aiwatar da wasu ayyuka. An yi amfani da su a cikin ayyuka masu yawa da aka gina. Suna suna yawancin suna ƙunshi bayanin "IF". Wannan rukuni na aiki, da farko, ya kamata a dangana COUNTES, COUNTERSILN, Kundin, SUMMESLIMN. Bugu da ƙari ga masu sarrafawa, ana amfani da ma'auni a cikin Excel a yanayin tsarawa. Ka yi la'akari da amfani da su yayin aiki tare da kayan aiki daban-daban na wannan na'ura mai sarrafawa cikin ƙarin daki-daki.
COUNTES
Babban aikin mai aiki COUNTESna ƙungiyar lissafi, shine ƙididdigar shagaltar da ƙwayoyin daban-daban na sel waɗanda suka gamsu da wasu ƙayyadadden yanayin. Sakamakonsa kamar haka:
= COUNTRES (iyakar, ma'auni)
Kamar yadda ka gani, wannan mai aiki yana da muhawara biyu. "Range" shi ne adreshin mahaɗin abubuwa a kan takardar da za'a yi a lissafi.
"Criterion" - wannan ita ce gardamar da ta kafa yanayin cewa sassan yankin da aka ƙayyade dole su ƙunshi domin a haɗa su cikin ƙidaya. Dangantaka, zaɓin lamba, rubutu, ko tunani akan tantanin halitta wanda ke dauke da ma'auni za a iya amfani. A wannan yanayin, don nuna alamar, za ka iya amfani da waɗannan haruffa: "<" ("ƙasa da"), ">" ("mafi"), "=" (daidai), "" ("ba daidai"). Alal misali, idan ka saka bayanin "<50", to, lissafi zai la'akari ne kawai abubuwan da aka ƙayyade ta gardama "Range"wanda akwai ƙididdigar lambobi ƙasa da 50. Amfani da waɗannan haruffan don ƙayyade sigogi zasu zama masu dacewa da dukan sauran zaɓuɓɓuka, wanda za'a tattauna a wannan darasi a ƙasa.
Kuma yanzu bari mu dubi wani misali na yadda wannan mai aiki yana aiki a aikin.
Don haka, akwai tebur wanda ya nuna kudaden ajiyar kuɗi biyar a kowane mako. Muna buƙatar gano yawan kwanakin a wannan lokacin, inda tallace-tallace na tallace-tallace na 2 ya wuce 15,000 rubles.
- Zaɓi nau'in takardar shaidar da mai aiki zai fitar da sakamakon sakamakon lissafi. Bayan wannan danna kan gunkin "Saka aiki".
- Kaddamarwa Ma'aikata masu aiki. Matsa zuwa toshe "Labarin lissafi". A nan muna samowa kuma zaɓi sunan "COUNTES". Sa'an nan kuma ya kamata ka latsa maballin "Ok".
- A kunna jigilar gardama na mai aiki na sama. A cikin filin "Range" nuna yankin da kwayoyin halitta wanda za'a iya lissafin. A cikin yanayinmu, zaɓi abubuwan da ke cikin layin. "Shop 2"inda ake da dabi'u na kudaden shiga da rana. Sanya siginan kwamfuta a filin da aka kayyade, kuma, riƙe da maɓallin linzamin hagu, zaɓi jerin daidaito a teburin. Adireshin mahaɗin da aka zaba ya bayyana a taga.
A filin gaba "Criterion" kawai buƙatar ka saita zabin zabin da aka zaɓa a nan gaba. A cikin yanayinmu, kana buƙatar ka ƙidaya waɗannan abubuwa na tebur wanda darajar ta wuce 15,000. Saboda haka, ta amfani da keyboard, za mu shigar da magana a cikin filin da aka kayyade ">15000".
Bayan an yi amfani da manipulations a sama, danna maballin. "Ok".
- Shirin ya lissafa kuma ya nuna sakamakon a cikin kashi na takardar da aka zaba kafin kunnawa. Ma'aikata masu aiki. Kamar yadda kake gani, a cikin wannan yanayin sakamakon yana daidai da lambar 5. Wannan na nufin cewa a cikin tsararrakin da aka zaɓa a cikin kwayoyin biyar akwai dabi'u da suka wuce 15,000. Wannan shine, zamu iya cewa cewa a cikin ajiyar 2 a cikin kwanaki biyar daga nazarin bakwai, kudaden shiga ya wuce 15,000 rubles.
Darasi: Jagora na ayyuka a Excel
COUNTERSILN
Ayyukan gaba da ke ɗaukar ma'auni shine COUNTERSILN. Har ila yau, ya kasance ne ga ƙungiyar masu lissafi. Task COUNTERSILN shi ne ƙididdige kwayoyin halitta a cikin ƙayyadadden lissafin da za su gamsar da wani yanayi na yanayi. Gaskiyar cewa ba za ka iya ƙayyade ɗaya ba, amma da yawa sigogi, kuma ya bambanta wannan afaretan daga baya. Haɗin yana kamar haka:
= COUNTRY (yanayin_range1; condition1; condition_range2; condition2; ...)
"Yanayin Yanayi" yana kama da gardama na farko da bayanin da ya gabata. Wato, yana da hanyar haɗi zuwa yanki inda za a kidaya kwayoyin da suka dace da yanayin da aka ƙayyade. Wannan afaretan yana ba ka izini ka siffanta yawancin wurare a lokaci guda.
"Yanayin" wani ma'auni ne wanda ke ƙayyade abin da aka samo daga asusun da aka daidaita daidai za a ƙidaya, kuma waɗanda ba za su iya ba. Kowace bayanan da aka ba da bayanai dole ne ya sanya yanayin daban, ko da idan ya dace. Yana da mahimmanci cewa dukkanin kayan da aka yi amfani dashi a matsayin yankuna masu kwakwalwa suna da lambar adadin layuka da ginshiƙai.
Don saita sigogi da yawa na wannan yanki na bayanai, misali, don ƙidaya yawan adadin kwayoyin da ƙimar sun fi girma fiye da wani lambar amma ƙasa da sauran adadin, ana amfani da gardama mai zuwa: "Yanayin Yanayi" sau da yawa saka irin wannan tsararren. Amma a lokaci guda kamar yadda muhawarar ta dace "Yanayin" ya kamata a nuna nau'ukan daban-daban.
Yin amfani da misali na teburin guda tare da tallace-tallace na kasuwa a mako-mako, bari mu ga yadda yake aiki. Muna buƙatar gano adadin kwanaki na mako a lokacin da kudin shiga a duk kayyadaddun kayyade ya isa ka'idar da aka kafa a gare su. Ƙididdigar kudaden shiga kamar haka:
- Ajiye 1 - 14,000 rubles;
- Store 2 - 15,000 rubles;
- Store 3 - 24,000 rubles;
- Store 4 - 11,000 rubles;
- Store 5 - 32,000 rubles.
- Don yin aikin da ke sama, zaɓi rabuwa na takarda aiki tare da siginan kwamfuta inda za a nuna sakamakon aikin sarrafa bayanai. COUNTERSILN. Muna danna kan gunkin "Saka aiki".
- Samun zuwa Wizard aikin, tafiya don sake toshewa "Labarin lissafi". Jerin ya kamata ya sami sunan COUNTERSILN da kuma sanya shi zaɓi. Bayan yin aikin da aka ƙayyade, dole ne ka latsa maballin. "Ok".
- Bayan da aka aiwatar da aikin algorithm na sama, matsala ta buɗewa. COUNTERSILN.
A cikin filin "Yanayin Range1" dole ne ku shigar da adreshin layin da abin da ke cikin kudaden ajiya na 1 ga mako. Don yin wannan, sanya siginan kwamfuta a filin kuma zaɓi jimlar daidai a teburin. Ana nuna alamun a cikin taga.
Tunanin cewa ga Store 1 kudi na yau da kullum yana da ruba dubu 14, sannan a filin "Yanayin 1" shigar da magana ">14000".
A cikin filayen "Yanayin Yanayi2 (3,4,5)" Za a shigar da halayen layin tare da biyan kuɗi a kowane mako na Store 2, Store 3, Store 4 da Store 5. Mun yi aikin bisa ga wannan algorithm kamar gardama na farko na wannan rukuni.
A cikin filayen "Yanayi2", "Yanayin3", "Yanayin4" kuma "Yanayin5" mun kawo dabi'u masu daraja ">15000", ">24000", ">11000" kuma ">32000". Kamar yadda zaku iya tsammani, waɗannan dabi'un sun dace da tsakaita kudaden shiga, wanda ya fi daidaituwa ga kantin sayar da.
Bayan ka shigar da dukkan bayanan da suka dace (10 filaye a duka), danna kan maballin "Ok".
- Shirin ya lissafa kuma ya nuna sakamakon akan allon. Kamar yadda muka gani, daidai yake da lambar 3. Wannan yana nufin cewa a cikin kwana uku daga makon da aka bincikar, kudaden shiga cikin ɗakunan kima ya wuce jimlar da aka kafa a gare su.
Yanzu bari mu sauya aikin a bit. Dole ne mu ƙidaya adadin kwanakin da Shop 1 ya samu kudaden shiga fiye da rubles 14,000, amma kasa da rubles 17,000.
- Saka siginan kwamfuta a cikin kashi inda za'a fitar da fitarwa a kan takardar lissafin sakamakon. Muna danna kan gunkin "Saka aiki" a kan wurin aiki na takardar.
- Tun da kwanan nan mun yi amfani da wannan tsari COUNTERSILN, yanzu ba lallai ba ne don shiga kungiyar "Labarin lissafi" Ma'aikata masu aiki. Ana iya samun sunan wannan afaretan a cikin rukuni "10 Kwanan nan Amfani". Zaɓi shi kuma danna maballin. "Ok".
- Gilashin muhawarar masu aiki da suka saba da mu sun buɗe. COUNTERSILN. Sa siginan kwamfuta a filin "Yanayin Range1" kuma, tare da kulle maɓallin linzamin hagu, zaɓi dukkanin kwayoyin da ke dauke da kudaden shiga ta kwanakin shagon 1. Suna cikin jere, wanda aka kira "Shop 1". Bayan haka, za a nuna haɗin yankin da aka keɓa a cikin taga.
Next, saita siginan kwamfuta a filin "Yanayin1". A nan muna buƙatar saka ƙayyadaddun iyakokin dabi'u a cikin kwayoyin da zasu shiga cikin lissafi. Saka bayanin ">14000".
A cikin filin "Yanayin Yanayi 2" mun shigar da wannan adireshin daidai yadda muka shiga cikin filin "Yanayin Range1", wato, za mu sake shigar da haɗin ƙananan kwayoyin tare da dukiyar da aka samu daga farawa na farko.
A cikin filin "Yanayi2" saka ainihin iyakar zaɓin zaɓi: "<17000".
Bayan duk ayyukan da aka ƙayyade, an latsa maɓallin. "Ok".
- Shirin ya bada sakamakon sakamakon lissafi. Kamar yadda muka gani, yawan kuɗin yana da 5. Wannan yana nufin cewa a cikin kwanaki 5 daga cikin bakwai na binciken, kudaden shiga cikin kantin sayar da kantin sayar da kaya shine a cikin kewayon daga 14,000 zuwa 17,000 rubles.
Kundin
Wani mai aiki wanda yayi amfani da ma'auni shi ne Kundin. Ba kamar ayyukan da suka gabata ba, yana nufin ɓangaren lissafi na masu aiki. Ayyukanta shi ne don ƙara bayanai a cikin kwayoyin da ke haɗu da wani yanayi. Haɗin aikin shine:
= SUMMERS (iyakar, zane; [sum_range]]
Magana "Range" yana nuna wurin sassan jikin da za'a bincika don biyan kuɗi. A gaskiya ma, an saita shi a kan wannan ka'idar kamar yadda ake magana akan wannan sunan. COUNTES.
"Criterion" - wata hujja ce mai dacewa wadda ta kafa saiti don zaɓar sel daga yankin da aka ƙayyade. Ka'idodin ƙaddamar da wannan maƙasudin irin waɗannan maganganu na masu aiki na baya, waɗanda muka ɗauka a sama.
"Ranar Kira" - Wannan wata hujja ce ta dace. Yana nuna yanki na musamman na tsararren da za a yi taron. Idan ka bar shi kuma ba a saka ba, to, ta hanyar tsoho ana la'akari da cewa yana daidai da darajar da aka buƙata "Range".
Yanzu, kamar kullum, la'akari da aikace-aikacen wannan afaretan a cikin aikin. Bisa ga wannan teburin, muna fuskantar aiki na ƙidaya adadin kudaden shiga a Shop 1 don lokacin da ya fara daga 11.03.2017.
- Zaɓi tantanin halitta wanda za'a nuna sakamakon. Danna kan gunkin "Saka aiki".
- Samun zuwa Wizard aikin a cikin shinge "Ilmin lissafi" sami kuma zaɓi sunan "SUMMESLI". Mun danna kan maɓallin "Ok".
- Gidan gwajin aikin ya fara. Kundin. Yana da filayen guda uku daidai da muhawarar mai aikin afareta.
A cikin filin "Range" mun shiga yankin na teburin wanda aka sanya dabi'un da aka bari don biyan kuɗin da yanayin zai kasance. A cikin yanayinmu zai zama layi na kwanakin. Saka siginan kwamfuta a cikin wannan filin kuma zaɓi duk sassan da ke dauke da kwanakin.
Tun da yake kawai muna bukatar mu ƙara kuɗin daga ranar 11 ga Maris, to a filin "Criterion" muna fitar da darajar ">10.03.2017".
A cikin filin "Ranar Kira" Dole ne ku ƙayyade yanki, waɗanda za a daidaita dabi'un da suka dace da ka'idodin da aka ƙayyade. A halinmu, wadannan sune dabi'un kuɗin shiga na layin. "Shop1". Zaɓi jimlar daidaitattun kayan takarda.
Bayan gabatarwar duk bayanan da aka ƙayyade, danna kan maballin "Ok".
- Bayan haka, sakamakon aikin sarrafa bayanai ta hanyar aikin za a nuna a cikin takaddun da aka ƙayyade a cikin takarda. Kundin. A cikin yanayinmu, yana da daidai da 47921.53. Wannan yana nufin cewa tun daga 11.03.2017, har zuwa ƙarshen lokacin bincike, yawan kudin shiga na Shop 1 ya kai 47,921.53 rubles.
SUMMESLIMN
Za mu kammala binciken da masu aiki suka yi amfani da ma'auni, suna mai da hankali akan aikin SUMMESLIMN. Ayyukan wannan aikin ilmin lissafi shi ne haɓaka dabi'u na wuraren da aka nuna a cikin teburin, wanda aka zaba bisa ga wasu sigogi. Haɗin aikin wannan afaretan shine:
= SUMMESLIMN (sum_range_range; condition_range1; condition1; condition_range2; condition2; ...)
"Ranar Kira" - Wannan wata hujja ce da take da adireshin tsararren, jinsunan da aka dace da wasu ƙididdiga, za a kara.
"Yanayin Yanayi" - hujja da ke wakiltar wani jigon bayanan, bincika don biyan kuɗin da yanayin;
"Yanayin" - Magana da ke wakiltar ma'auni na zaɓin don ƙarawa.
Wannan aikin ya hada da aiki tare da wasu samfurori na masu aiki irin wannan lokaci.
Bari mu ga yadda wannan mai aiki ya dace don warware matsaloli a cikin mahallin tallace-tallace a tallace-tallace. Muna buƙatar lissafin kudaden da Shop 1 ya samar don lokaci daga Maris 09 zuwa Maris 13, 2017. A wannan yanayin, yawancin kudin shiga ya kamata a la'akari da waɗannan kwanakin, wanda aka samu wanda ya wuce 14,000 rubles.
- Zaɓi tantanin halitta don sake nuna jimlar kuma danna gunkin. "Saka aiki".
- A cikin Mai sarrafa aikiDa farko, muna motsawa zuwa toshe. "Ilmin lissafi", kuma a nan za mu zaɓi abu da ake kira "SUMMESLIMN". Danna maballin "Ok".
- Wurin taga na masu aiki, wanda sunan da aka ƙayyade a sama, an kaddamar.
Saita siginan kwamfuta a filin "Ranar Kira". Ba kamar sauran muhawarar na gaba ba, wannan yana daga cikin nau'i kuma yana nuna matakan dabi'un inda za'a tara bayanan da ya dace da ka'idodi da aka ƙayyade. Sa'an nan kuma zaɓi yankin na layi "Shop1"An sanya dabi'un kudaden shiga ga ma'auni daidai.
Bayan an nuna adireshin a cikin taga, je filin "Yanayin Range1". A nan za mu buƙaci nuna bayanan layi da kwanakin. Muna yin shirin na maɓallin linzamin hagu kuma zaɓi duk kwanakin da ke cikin tebur.
Sa siginan kwamfuta a filin "Yanayin1". Halin farko shi ne cewa za mu taƙaita bayanai ba a baya ba sai Maris 09. Saboda haka, mun shigar da darajar ">08.03.2017".
Matsa zuwa gardama "Yanayin Yanayi 2". Anan kuna buƙatar shigar da daidaitattun abubuwan da aka rubuta a filin "Yanayin Range1". Muna yin haka a cikin wannan hanya, wato, ta hanyar nuna alama da layi tare da kwanakin.
Saita siginan kwamfuta a filin "Yanayi2". Halin na biyu shi ne cewa kwanakin da za a kara yawan kudin shiga ba zai kasance ba bayan Maris 13. Saboda haka, rubuta bayanan nan: "<14.03.2017".
Jeka filin "Yanayin Yanayi 2". A wannan yanayin, muna buƙatar zaɓar irin wannan madaidaicin wanda aka shigar da adireshin a matsayin tsararru.
Bayan adreshin mahaɗin da aka ƙayyade ya nuna a taga, je filin "Yanayin3". Yin la'akari cewa kawai dabi'u da suka wuce 14,000 rubles zasu shiga cikin summation, muna yin shigarwa mai zuwa: ">14000".
Bayan aikin karshe, danna maballin "Ok".
- Shirin ya nuna sakamakon a kan takardar. Ya daidaita da 62491.38. Wannan na nufin cewa tun daga ranar 09 ga 13 Maris na 2017, adadin kudaden kuɗi idan kuka kara shi domin kwanakin da ya wuce 14,000 rubles ya kai 62,491.38 rubles.
Tsarin Yanayin
Na ƙarshe, wanda muka bayyana, kayan aiki, lokacin aiki tare da wane ma'auni aka yi amfani da shi, ƙaddamar yanayin. Yana aiwatar da nau'in tsara tsarin da ya dace da yanayin da aka ƙayyade. Dubi misali na aiki tare da tsara yanayin.
Yi amfani da waɗannan nau'in a cikin tebur a cikin blue, inda dabi'u don rana ya wuce 14,000 rubles.
- Zaɓi dukkanin tsararren abubuwa a cikin tebur, wanda ya nuna kudaden shiga na kantin sayar da kaya a rana.
- Matsa zuwa shafin "Gida". Muna danna kan gunkin "Tsarin Yanayin"sanya a cikin wani toshe "Sanya" a kan tef. Jerin ayyukan ya buɗe. Muna klatsat a cikin wani matsayi "Ƙirƙiri wata doka ...".
- An kunna rukunin tsara tsara tsarin tsarawa. A cikin nau'in nau'in zaɓi na zaɓi zaɓi sunan "Shirye kawai kwayoyin halitta waɗanda ke dauke da". A cikin filin farko na shinge yanayin daga lissafin zaɓuɓɓukan zaɓin zaɓi "Adadin Yara". A filin gaba, zaɓi matsayi "Ƙari". A karshen, muna nuna darajar kanta, mafi girman shi shine tsara abubuwan da ke cikin tebur. Muna da 14,000. Don zaɓar nau'in tsarawa, danna maballin. "Tsarin ...".
- An kunna maɓallin tsarawa. Matsa zuwa shafin "Cika". Daga zaɓuɓɓukan launi na cika, zaɓi blue ta danna kan shi tare da maɓallin linzamin hagu. Bayan da aka zaɓa aka nuna a cikin "Samfurin"danna maballin "Ok".
- Yana ta atomatik komawa ga tsarin tsara tsarin tsarawa. Haka ma a yankin "Samfurin" nuna a blue. A nan muna bukatar muyi aiki ɗaya: danna kan maballin "Ok".
- Bayan aikin karshe, dukkanin jinsunan da aka zaɓa, wanda ya ƙunshi lamba fiye da 14000, za a cika da launi mai launi.
Don ƙarin bayani game da yiwuwar tsara yanayin yanayi an bayyana a cikin wani labarin dabam.
Darasi: Tsarin Magana a Excel
Kamar yadda muka gani, tare da taimakon kayayyakin aikin da suke amfani da ma'auni a cikin aikin su, a cikin Excel wanda zai iya warware matsaloli daban-daban. Wannan yana iya ƙididdiga yawa da kuma dabi'u, da tsarawa, da kuma yin wasu ayyuka. Ayyukan da suke aiki a cikin wannan shirin tare da ma'auni, wato, tare da wasu sharuɗɗan da ake aiwatar da wannan aikin, sune ɗayan ayyukan ayyuka, da tsarin tsarawa.
