Kugwiritsa ntchito zoyipa mu Microsoft Excel

Microsoft Excel sikuti ndi mkonzi wamasamba, komanso chida champhamvu pakuwerengera kosiyanasiyana. Pomaliza, mwayiwu udawoneka ngati wothokoza. Mothandizidwa ndi ntchito zina (oyendetsa ntchito), mutha kutchulanso kawerengero, komwe kumatchedwa njira. Tiphunzire mwatsatanetsatane momwe mungazigwiritsire ntchito mukamagwira ntchito ku Excel.

Ntchito Makhalidwe

Makhalidwe ndi momwe pulogalamu imagwiranso ntchito zina. Amagwiritsidwa ntchito pazinthu zingapo zopangidwa. Mayina awo nthawi zambiri amakhala ndi mawuwo NGATI. Kwa gulu la ogwiritsira ntchito, choyambirira, ndikofunikira kutchulapo KULIMA, COUNTIMO, SUMU, SUMMESLIMN. Kuphatikiza pa ogwiritsira ntchito opangidwa, njira mu Excel zimagwiritsidwanso ntchito polemba mitundu. Ganizirani momwe amagwirira ntchito pamene mukugwiritsa ntchito zida zosiyanasiyana za purosesa tebulo ili mwatsatanetsatane.

KULIMA

Ntchito yayikulu ya wothandizira KULIMAKukhala m'gulu lowerengera kumatengera kuchuluka kwa maselo omwe amakwaniritsa gawo linalake. Matchulidwe ake ndi awa:

= COUNTIF (mtundu; chitsimikiziro)

Monga mukuwonera, wogulitsayo ali ndi mfundo ziwiri. "Zosintha" ikuyimira adilesi yamndandanda wazinthu zambiri zomwe zili papepala momwe mungawerengere.

"Mundende" Uwu ndi mkangano womwe umakhazikitsa momwe maselo a malo omwe akufotokozedwawo ayenera kukhala nawo kuti awerengeredwe. Monga paramende, mawu owerengera, zolemba, kapena kulumikizana ndi selo komwe chikhazikitsochi chitha kugwiritsidwa ntchito. Potere, kuwonetsa chitsimikiziro, mutha kugwiritsa ntchito zilembo zotsatirazi: "<" (zochepa), ">" (zambiri), "=" (zofanana), "" (wosofanana) Mwachitsanzo, ngati mutatanthauzira mawu "<50", ndiye zinthu zokhazo zomwe zafotokozedwazo zimatsatiridwa pakuwerengera "Zosintha", momwe mawerengero amakhala ocheperako 50. Kugwiritsa ntchito kwa zizindikirazi posonyeza kuti magawo azikhala oyenera pazinthu zina zonse, zomwe tikambirane phunziroli.

Tsopano tiyeni tiwone chitsanzo chokhazikika cha momwe operekera ntchitoyi amagwirira ntchito.

Chifukwa chake, pali tebulo pomwe ndalama kuchokera ku masitolo asanu pa sabata zimayambitsidwa. Tiyenera kudziwa kuchuluka kwa masiku panthawiyi pomwe mu Store 2 ndalama kuchokera kugulitsa zidapitilira ma ruble 15,000.

1. Sankhani pepala lomwe wothandizira azigwiritsa ntchito powerengera. Pambuyo pake, dinani chizindikiro "Ikani ntchito".



2. Kuyambira Ogwira Ntchito. Timasamukira kumalo "Zowerengera". Pamenepo timapeza ndikuwonetsa dzinalo "COUNTIF". Kenako dinani batani. "Zabwino".



3. Tsamba lokangana la mawu ali pamwambapa limayambitsa. M'munda "Zosintha" ndikofunikira kuwonetsa madera omwe maselo adzapangidwe. M'malo mwathu, tiyenera kuwonetsa zomwe zili pamzerewu "Gulani 2", momwe mitengo yamalipiro imakhalira masana. Timayika cholozera m'munda womwe watchulidwa ndipo titalemba batani lakumanzere, ndikusankha mndandanda womwe ukugwirizana nawo. Adilesi yamagulu osankhidwa amawonetsedwa pazenera.

   M'munda wotsatira "Mundende" ingofunika kukhazikitsa gawo posankha. M'malo mwathu, tikuyenera kuwerengera zinthu zomwe zili patebulopo pomwe mtengo wake umaposa 15000. Chifukwa chake, pogwiritsa ntchito kiyibodiyo, timayendetsa mawuwo mu gawo lomwe talikhazikitsidwa ">15000".

   Mukamaliza kupanga pamanja chilichonse, dinani batani "Zabwino".



4. Pulogalamuyi imawerengera ndikuwonetsa zotsatira zake mu pepala lomwe lidasankhidwa musanayambe ntchito Ogwira Ntchito. Monga mukuwonera, pankhaniyi, zotsatira zake ndi zofanana ndi 5. Izi zikutanthauza kuti m'magulu asanu omwe asankhidwa mumagulu asanu mulinso zofunikira zopitilira 15,000. Izi zikutanthauza kuti, mu Shopu 2 ​​m'masiku asanu mwa zisanu ndi ziwirizi, ndalama zidaposa ma ruble 15,000.

Phunziro: Mfiti ya Excel Wizard

COUNTIMO

Ntchito yotsatira yomwe ikugwira ntchito ndi zomwe COUNTIMO. Ilinso m'gulu la ochita ntchito. Ntchito COUNTIMO ikuwerengera maselo munthawi yomwe ikukwaniritsa machitidwe ake. Ndizakuti mungatchule chimodzi, koma magawo angapo, ndikusiyanitsa wogwirawo ndi woyamba uja. Syntax ndi motere:

= COUNTIME (condition_range1; condition1; condition_range2; condition2; ...)

"Zowongolera Zinthu" ndizofanana ndi mkangano woyamba wam'mbuyomu. Ndiye kuti, ndi cholumikizira ku malo omwe maselo amawerengedwa omwe amakwaniritsa zomwe zanenedwa. Wogwiritsa ntchito iyi amakupatsani mwayi kuti mufotokoze madera angapo nthawi imodzi.

"Mkhalidwe" ikuyimira chitsimikiziro chomwe chimasankha kuti ndi ziti zomwe zikuchokera munsi yolingana mwazomwe zidzawerengedwa ndipo zomwe sizingachitike. Dera lililonse lopatsidwa liyenera kufotokozedwa mosiyana, ngakhale lingafanane. Ndikofunikira kuti makulidwe onse omwe amagwiritsidwa ntchito ngati madera azikhala ndi mizere yofanana.

Pofuna kukhazikitsa magawo angapo a malo amodzi omwewo, mwachitsanzo, kuwerengera kuchuluka kwa maselo momwe mfundo zake zimakhala zazikulupo kuposa nambala inayake, koma ochepera chiwerengero china, ziyenera kutengedwa ngati mkangano "Zowongolera Zinthu" tchulani kangapo kangapo. Koma nthawi yomweyo, ngati zifukwa zoyenera "Mkhalidwe" njira zosiyanasiyana ziyenera kuwonetsedwa.

Pogwiritsa ntchito tebulo lomweli ndi ndalama zogulitsira sabata iliyonse, tiwone momwe imagwirira ntchito. Tiyenera kudziwa kuchuluka kwa masiku sabata yomwe ndalama zomwe amagulitsa zimafikiridwa. Misonkho ndi izi:

• Gulani 1 - 14,000 ma ruble;
• Gulani 2 - ma ruble 15,000;
• Gulani 3 - ma ruble 24,000;
• Gulani 4 - ma ruble 11,000;
• Gulani 5 - 32,000 ma ruble.

1. Kuti mukwaniritse ntchito yomwe ili pamwambapa, sankhani gawo la worksheet ndi cholozera, pomwe zotsatira za kusaka deta zikuwonetsedwa COUNTIMO. Dinani pachizindikiro "Ikani ntchito".



2. Kupita ku Fotokozerani Wizardpitani ku block kachiwiri "Zowerengera". Mndandandawu uyenera kupeza dzinali COUNTIMO ndikusankha. Pambuyo pochita zomwe mwatchulazi, muyenera kukanikiza batani "Zabwino".



3. Kutsatira kuperekedwa kwa fanizo lazomwe lili pamwambapa, zenera la mkangano limatsegulidwa COUNTIMO.

   M'munda "Zowongolera Zoyambira 1" lowetsani adilesi ya mzere momwe deta yomwe ili pa 1 Store 1 ya sabatayo. Kuti muchite izi, ikani cholozera m'munda ndikusankha mzere wolingana pagome. Ogwirizanitsa akuwonetsedwa pazenera.

   Kuganizira kuti posungira 1 la ndalama za tsiku ndi tsiku ndi ruble 14,000, ndiye kumunda "Khalidwe 1" lembani mawuwo ">14000".

   Kulowa m'minda "Zabwino 2 (3,4,5)" zogwirizira za mizereyi ndi ndalama zomwe zimachitika sabata limodzi la Store 2, Store 3, Store 4 ndi Store 5, motsatana, ziyenera kuyikidwa.

   Kulowa m'minda "Mkhalidwe 2", "Mkhalidwe 3", "Khalid44" ndi "Khalidwe5" timalowa zofunikira ">15000", ">24000", ">11000" ndi ">32000". Momwe mungaganizire, izi zimagwirizana ndi gawo lazopezera ndalama lomwe limapitilira muyeso yogulitsa.

   Mukalowetsa zofunikira zonse (minda yonse 10), dinani batani "Zabwino".



4. Pulogalamuyi imawerengera ndikuwonetsa zotsatira zake pazenera. Monga mukuwonera, ndi zofanana ndi nambala 3. Izi zikutanthauza kuti m'masiku atatu kuchokera sabata yowerengedwa ndalama zonse zomwe zidagulidwa zidapitilira zomwe zidakhazikitsidwa.

Tsopano tiyeni tisinthe ntchitoyi. Tiwerenge kuchuluka kwa masiku omwe shopu 1 idalandira ndalama zochulukirapo ma ruble 14,000, koma ruble 17,000.

1. Tikuyika cholozera mupake pomwe zotsatira zake zidzapangidwe papepala lazotsatira. Dinani pachizindikiro "Ikani ntchito" pamalo ogwirira ntchito pepala.



2. Popeza tidagwiritsa ntchito formula posachedwapa COUNTIMO, tsopano simuyenera kupita pagululo "Zowerengera" Ogwira Ntchito. Dzinalo la opaleshoni likupezeka pagululi "10 Zomwe Zagwiritsidwa Ntchito Posachedwa". Sankhani ndikudina batani. "Zabwino".



3. Yenera yotsutsana ndi wothandizira COUNTIMO. Ikani wolemba m'munda "Zowongolera Zoyambira 1" ndipo, pogwirizira batani la mbewa yakumanzere, sankhani maselo onse omwe ali ndi ndalama pofika masiku a Store 1. Amapezeka pamzere, womwe umatchedwa "Gulani 1". Pambuyo pake, zogwirizanitsa za m'deralo zidzawonetsedwa pazenera.

   Kenako, ikani cholozera m'munda "Khalidwe 1". Apa tikufunika kuwonetsa malire am'munsi mwa mfundo zomwe zili m'maselo omwe atenge nawo gawo lowerengera. Tchulani mawu ">14000".

   M'munda "Zowonjezera 2" lowetsani adilesi yomweyo momwemo yomwe idalowetsedwa kumunda "Zowongolera Zoyambira 1", ndiye kuti, timalowetsananso ma cell ndimagawo azomwe amapangira koyamba.

   M'munda "Mkhalidwe 2" onetsani malire osankhidwa: "<17000".

   Pambuyo pazomwe zachitidwa zachitika, dinani batani "Zabwino".



4. Pulogalamuyi imapereka zomwe amawerengera. Monga mukuwonera, mtengo womaliza ndi 5. Izi zikutanthauza kuti m'masiku asanu mwa asanu ndi awiri omwe adawerengedwa, ndalama zomwe zidagulitsidwa m'sitolo yoyamba zidali 14,000 mpaka 17,000 rubles.

SUMU

Wothandizira wina yemwe amagwiritsa ntchito ndi SUMU. Mosiyana ndi ntchito zam'mbuyomu, ndi ya chipika cha ogwiritsa ntchito. Ntchito yake ndikuwunikira mwachidule ma cell omwe amafanana ndi vuto linalake. Syntax ndi motere:

= SUMMES (mtundu; zotsimikizira; [sum_range])

Kukangana "Zosintha" ikuwonetsa madera omwe maselo omwe amayang'aniridwa kuti agwirizane ndi vutoli. M'malo mwake, amakhazikitsidwa ndi mfundo zomwezo ngati mfundo yantchito ya dzina lomweli KULIMA.

"Mundende" - ndi mfundo yofunika kufotokoza kusankhidwa kwa maselo kuchokera kudera linalake kuti liwonjezedwe. Mfundo zakukhazikika ndizofanana ndi zomwe zimafanana ndi omwe adachita m'mbuyomu, zomwe tidawerenga pamwambapa.

"Zowerengera Mwachidule" Uwu ndi malingaliro osankha. Ikuwonetsa malo enieni omwe adapangidwira omwe akupangira. Ngati mumachokapo ndipo osachinena, ndiye kuti pamasulidwa zimawerengedwa kuti ndi zofanana ndi kufunika kwa mfundo yofunika "Zosintha".

Tsopano, monga nthawi zonse, lingalirani za kugwiritsa ntchito kwa opareshoni iyi. Kutengera ndi tebulo lomweli, tikukumana ndi ntchito kuwerengetsa ndalama zomwe zimapezeka mu Store 1 kwa nthawi yoyambira pa Marichi 11, 2017.

1. Sankhani khungu lomwe zotsatira zake zizikhala. Dinani pachizindikiro. "Ikani ntchito".



2. Kupita ku Fotokozerani Wizard mu block "Masamu" pezani ndikuwonetsa dzinalo SUMMS. Dinani batani "Zabwino".



3. Tsamba la mkangano wa ntchito likuyamba SUMU. Ili ndi magawo atatu ofanana ndi malingaliro a wothandizira omwe adanenedwa.

   M'munda "Zosintha" lowetsani m'dera la thebulo momwe mfundo zofunika kuzisanthula kuti zigwirizane ndi zomwe zikupezekazi. M'malo mwathu, zidzakhala mndandanda wazaka. Ikani chidziwitso mu gawo ili ndikusankha maselo onse omwe ali ndi masiku ake.

   Popeza tikuyenera kuwonjezera ndalama zonse kuyambira pa Marichi 11, m'munda "Mundende" yendetsani mtengo wake ">10.03.2017".

   M'munda "Zowerengera Mwachidule" muyenera kufotokozera madera omwe mfundo zomwe zikukwaniritsidwa zidzafotokozedwa mwachidule. M'malo mwathu, awa ndi mitengo yazolowa pamzere "Shop1". Sankhani makonda ofanana a zinthu.

   Pambuyo polemba zonse zomwe zatulutsidwa, dinani batani "Zabwino".



4. Zitatha izi, zotsatira za kusanjidwa kwazinthu ndi ntchitoyo zikuwonetsedwa pazomwe zidasindikizidwa kale patsamba la works. SUMU. M'malo mwathu, ndiwofanana 47921.53. Izi zikutanthauza kuti kuyambira pa Marichi 11, 2017, mpaka kumapeto kwa nthawi yowunikirayi, ndalama zonse za mu shopu 1 zidakwana 47,921.53 rubles.

SUMMESLIMN

Timamaliza kuwerenga kwa ogwiritsa ntchito omwe amagwiritsa ntchito njira, poyang'ana ntchito SUMMESLIMN. Cholinga cha ntchito iyi ya masamu ndikubwereza mwachidule zofunikira za magawo omwe tawonetsedwa patebulopo, omwe asankhidwa malinga ndi magawo angapo. Kuphatikizika kwa wothandizirayo kuli motere:

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

"Zowerengera Mwachidule" Uwu ndiye mkangano, ndiye adilesi ya gulu lomwe maselo omwe amakumana ndi chitsimikiziro awonjezedwa.

"Zowongolera Zinthu" - kutsutsana, komwe ndi kuchuluka kwa deta, kufufuzidwa kuti agwirizane ndi vutoli;

"Mkhalidwe" - mkangano woyimira chitsimikizo chosankha chowonjezera.

Ntchitoyi imatanthawuza kugwira ntchito ndi ma seti angapo ogwira ntchito omwewo nthawi imodzi.

Tiyeni tiwone momwe opaleshoniyi amagwirira ntchito pakuthana ndi mavuto pamavuto athu pazogulitsa ndalama m'malo ogulitsa. Tifunikira kuwerengera ndalama zomwe shopu yoyamba idabweretsa kuyambira pa Marichi 9 mpaka pa Marichi 13, 2017. Pankhaniyi, pakuwuza ndalama, ndi masiku okhawo omwe ayenera kukumbukiridwa, momwe ndalama zimapitilira ma ruble 14,000.

1. Apanso, sankhani foni kuti muwonetse yonse ndikudina pazizindikiro "Ikani ntchito".



2. Mu Ntchito wizChoyamba, timasamukira kumalo osungirako "Masamu", ndipo timasankha chinthu chotchedwa SUMMESLIMN. Dinani batani. "Zabwino".



3. Windo lotsutsana ndi woyambitsa limayambitsidwa, dzina lomwe linanenedwa pamwambapa.

   Khazikitsani chotembezera m'munda "Zowerengera Mwachidule". Mosiyana ndi mfundo zotsatirazi, iyi ya mtundu imalozeranso kuzinthu zamtengo wapatali komwe deta yomwe ikugwirizana ndi zomwe zanenedwazo idzawonetsedwa. Kenako sankhani mzere "Shop1", momwe mitengo yolipira yogwirizanirana imakhalira.

   Adilesi ikawonetsedwa pazenera, pitani kumunda "Zowongolera Zoyambira 1". Apa tifunikira kuwonetsa zogwirizira za chingwecho ndi masiku ake. Tsitsani batani lakumanzere ndikusankha masiku onse omwe ali patebulopo.

   Ikani wolemba m'munda "Khalidwe 1". Mkhalidwe woyamba ndikuti tifotokozere mwachidule zomwe tanena kale osati pa Marichi 9. Chifukwa chake, lowetsani mtengo wake ">08.03.2017".

   Timasamukira kutsutsano "Zowonjezera 2". Apa mukuyenera kuyika maulalo omwe adalembedwa m'munda "Zowongolera Zoyambira 1". Timachita izi mwanjira yomweyo, ndiye kuti, pakuwunikira mzerewo ndi madeti.

   Khazikitsani chotembezera m'munda "Mkhalidwe 2". Mkhalidwe wachiwiri ndiwakuti masiku omwe zoperekazo zidzawonjezeredwa sayenera kutha pa Marichi 13. Chifukwa chake, timalemba mawu otsatirawa: "<14.03.2017".

   Pitani kumunda "Zowonjezera 2". Poterepa, tifunika kusankha gulu lomwelo lomwe adilesi yake idalowetsedwa ngati mndandanda wanthawi zonse.

   Pambuyo adilesi ya womwe wakonzedwayo akuwonekera pazenera, pitani kumunda "Mkhalidwe 3". Poganizira kuti mitengo yomwe phindu lake limaposa 14,000 rubles lomwe lingatenge nawo gawo, timapanga izi: ">14000".

   Mukamaliza kuchitapo kanthu komaliza, dinani batani "Zabwino".



4. Pulogalamuyi ikuwonetsa zotsatira zake papepala. Ndi ofanana ndi 62491,38. Izi zikutanthauza kuti kwanthawi yoyamba kuyambira pa Marichi 9 mpaka pa Marichi 13, 2017, ndalama zonse zikawonjezeredwa masiku omwe amapitilira ma ruble 14,000 zimakwana ruble 62,491.38.

Kukonza mitundu

Chida chomaliza chomwe tafotokozera, pogwira ntchito ndi njira, ndizokonzedwa mwanjira iliyonse. Imagwira mtundu womwe wakhazikitsidwa wa maselo omwe akukwaniritsa zomwe zimafotokozedwa. Onani chitsanzo chogwira ntchito ndi mitundu yozikika.

Timasankha maselowo patebulo mu buluu, komwe mitengo ya tsiku ndi tsiku imaposa ma ruble 14,000.

1. Timasankha zinthu zonse zomwe zili patebulopo, zomwe zimawonetsa ndalama zomwe zimagulitsidwa tsiku lililonse.



2. Pitani ku tabu "Pofikira". Dinani pachizindikiro Njira Zakukonzeraniitayikidwa Masitaelo pa tepi. Mndandanda wa zochita umatseguka. Dinani pa icho mu udindo "Pangani lamulo ...".



3. Iwindo loti lipange mtundu wa makonzedwe limayatsidwa. Pazosankha mtundu wamalamulo, sankhani dzinalo "Maselo okhawo omwe ali ndi". M'munda woyamba wa block block, kuchokera mndandanda wa zosankha zomwe zingatheke, sankhani "Mtengo wam'manja". M'munda wotsatira, sankhani malo Zambiri. Pomaliza - fotokozerani phindu lokha, kuposa lomwe mukufuna kulongedza patebulo. Tili nayo 14000. Kusankha mtundu wamtundu, dinani batani "Fomu ...".



4. Tsamba losintha limayambitsa. Pitani ku tabu "Dzazani". Kuchokera pamitundu yomwe mukufuna kutsaka, sankhani buluu mwa kuwonekera kumanzere. Mtundu wosankhidwa utawonetsedwa m'deralo Zitsanzodinani batani "Zabwino".



5. Tsambali lolamulira la m'badwo limangobwera lokha. Mulinso m'munda Zitsanzo mtundu wamtambo ukuwonetsedwa. Apa tikufunika kuchita chinthu chimodzi: dinani batani "Zabwino".



6. Pambuyo pazochita zomaliza, maselo onse omwe asankhidwa, omwe ali ndi chiwerengero choposa 14000, adzadzidwa ndi buluu.

Zambiri pazakukula kwa mawonekedwe azokambirana zakambidwa munkhani ina.

Phunziro: Makongoletsedwe azikhalidwe mu Excel

Monga mukuwonera, pogwiritsa ntchito zida zomwe zimagwiritsa ntchito ntchito zawo, Excel imatha kuthana ndi mavuto osiyanasiyana. Izi zitha kukhala, monga kuwerengera kuchuluka kwa zinthu ndi mtengo, ndi kusanjidwa, komanso kukhazikitsa ntchito zina zambiri. Zida zazikuluzikulu zomwe zikugwiritsidwa ntchito mu pulogalamuyi ndi zofunikira, ndiye kuti, ndi zochitika zina momwe izi zimayendetsedwera, ndizogwira ntchito zomangidwa, komanso mtundu wa mawonekedwe.