Zida Zotsogolera ku Microsoft Excel

Kulosera zamtsogolo ndi gawo lofunikira kwambiri pafupifupi gawo lililonse lazinthu, kuyambira zachuma mpaka zomangamanga. Pali chiwerengero chachikulu cha mapulogalamu omwe amapanga malowa. Tsoka ilo, si onse ogwiritsa ntchito omwe akudziwa kuti phukusi loyambira la Excel spreadsheet lili ndi zida zake zankhondo yolosera, zomwe sizotsika kwambiri pamapulogalamu ogwira ntchito mwaukadaulo wawo. Tiyeni tiwone zomwe zida izi zili komanso momwe mungapangire kuneneraku.

Njira Yotsogolera

Cholinga cha kulosera kulikonse ndikuzindikira zomwe zikuchitika, ndikuzindikira zotsatira zomwe zikuyembekezeka molingana ndi chinthu chophunziridwa panthawi inayake mtsogolo.

Njira 1: mzere wosintha

Mtundu wodziwika bwino kwambiri wolosera zam'masiku ku Excel ndikusintha kwanyumba pomanga mzere wowonera.

Tiyeni tiyese kuneneratu kuchuluka kwa phindu la bizinesiyo pazaka 3 pamasamba a chidziwitso ichi pazazaka 12 zapitazi.

1. Timapanga chodalira chozikidwa pa data la tabular lomwe limakhala ndi mikangano ndi zofunikira pazantchito. Kuti muchite izi, sankhani malo patebulo, kenako, ndikukhala Ikani, dinani pazithunzi zamtundu womwe mukufuna, womwe uli pabowo Ma chart. Kenako timasankha mtundu woyenera zochitika zinazake. Ndi bwino kusankha tchati chomwaza. Mutha kusankha mawonekedwe ena, koma kenako, kuti tsambalo liwonetsedwa molondola, muyenera kusintha, makamaka, chotsani mzere wotsutsana ndikusankha gawo lina lakumanzere.



2. Tsopano tikuyenera kupanga chingwe chowongolera zochitika. Timadina kumanja pa mfundo iliyonse yomwe ili patsamba. Pazosankha zomwe zayambitsidwa, siyani kusankha pazomwe zili Onjezani Zotsatira.



3. Windo la masanjidwewo likutseguka. Mmenemo mungasankhe imodzi mwa mitundu isanu ndi umodzi yoyandikira:
   • Chingwe;
   • Logarithmic;
   • Zofunikira;
   • Mphamvu;
   • Chipolopolo;
   • Zosefera zozungulira.

   Tiyeni tiyambe posankha kuyandikira kwa mzere.

   Mu makatani "Zonenedweratu" m'munda "Pitani ku" khazikitsani nambala "3,0", popeza tifunikira kulosera zaka zitatu tisanachitike. Kuphatikiza apo, mutha kuyang'ana bokosi pafupi ndi zoikamo. "Onetsani equation muzojambula" ndi "Ikani chithunzi cholimba (R ^ 2) pazithunzi". Chizindikiro chomaliza chikuwonetsa mtundu wa mzere wapanjira. Masanjidwewo atapangidwa, dinani batani Tsekani.



4. Chingwe cholumikiziracho chimamangidwa ndipo kuchokera pamenepo titha kudziwa kuchuluka kwake kopindulitsa pazaka zitatu. Monga momwe tikuonera, pofika nthawi imeneyi zizikhala zopitilira 4500,000 ma ruble. Zokwanira R2Monga tafotokozera pamwambapa, akuwonetsa mtundu wa mzere wosinthasintha. Kwa ife, mtengo R2 amapanga 0,89. Kwambiri kukhathamira, kukwera kudalirika kwa mzere. Mtengo wake wokwanira ukhoza kukhala wofanana 1. Nthawi zambiri zimavomerezedwa kuti ndi mgwirizano pamwambapa 0,85 mzere wotsogola ndi wodalirika.



5. Ngati mulingo wotsimikiza sugwirizana ndi inu, ndiye kuti mutha kubwerera pazenera la mtundu wazithunzi ndikusankha mtundu wina uliwonse. Mutha kuyesa njira zonse zomwe zilipo kuti mupeze zolondola kwambiri.

   

   Tiyenera kudziwa kuti zoneneratu pogwiritsa ntchito njira zowonjezera zina zitha kukhala zothandiza ngati nthawi yolosera siyidutsa 30% ya magawo osanthula. Ndiye kuti, tikasanthula nyengo ya zaka 12, sitingathe kuneneratu zaka zoposa 3-4. Koma ngakhale zili choncho, zikhala zodalirika ngati panthawiyi sipadzakhala mphamvu zochitira kapena, m'malo mwake, zinthu zabwino kwambiri, zomwe sizinali m'mbuyomu.

Phunziro: Momwe mungapangire mzere wotsogolera ku Excel

Njira 2: wogwiritsa ntchito wa PAECAST

Zosintha zamtundu wa ma tabular zitha kuchitika kudzera mu ntchito wamba ya Excel CHITSANZO. Kutsutsana uku ndi kwa gulu la zida zowerengera ndipo ili ndi mawonekedwe awa:

= PREDICT (X; odziwika_yawo - odziwika; odziwika_x_values)

"X" ndi mkangano womwe kufunika kwa ntchitoyo kuyenera kutsimikiziridwa. M'malo mwathu, mkanganowu udzakhala chaka chomwe kuneneraku kuyenera kuchitikira.

Mfundo zodziwika bwino - maziko a ntchito zodziwika bwino. M'malo mwathu, gawo lake limaseweredwa ndi kuchuluka kwa phindu la nthawi zam'mbuyomu.

Makhalidwe Abwino a x ndi mfundo zomwe zomwe zimadziwika zantchitoyi zikugwirizana. M'malo awo, tili ndi kuchuluka kwa zaka komwe zidziwitso zakale zidapezedwa.

Mwachilengedwe, kukangana sikuyenera kukhala kwakanthawi. Mwachitsanzo, amatha kutentha, ndipo kufunika kwa ntchitoyo kungakhale mulingo wokulira kwamadzi mukamawotha.

Mukawerengera njirayi, njira yolembetsa mzere imagwiritsidwa ntchito.

Tiyeni tiwone zovuta zomwe wogwiritsa ntchito amakhala nazo CHITSANZO pa chitsanzo cha konkriti. Tengani tebulo lonse. Tidzafunika kudziwa zomwe zidzachitike mu 2018.

1. Sankhani selo yopanda pepala pomwe mukufuna kuwonetsa zotsatira zake. Dinani batani "Ikani ntchito".



2. Kutsegula Fotokozerani Wizard. Gulu "Zowerengera" sankhani dzinalo "PREDICTION"kenako dinani batani "Zabwino".



3. Windo la mkangano liyamba. M'munda "X" sonyezani mtengo wotsutsa womwe mukufuna kupeza phindu la ntchitoyo. M'malo mwathu, iyi ndi 2018. Chifukwa chake, timalemba "2018". Koma ndibwino kuwonetsera ichi mufoni pachidacho, komanso kumunda "X" ingopatsani ulalo kwa icho. Izi zimalola mtsogolomo kusinthanitsa mawerengeredwe ndipo, ngati kuli kotheka, musinthe chaka.

   M'munda Mfundo zodziwika bwino tchulani zogwirizanitsa ndi mzati "Phindu la bizinesi". Izi zitha kuchitika ndikuyika tchire m'munda, kenako ndikusiya batani lakumanzere ndikuwunikiranso mzati wolingana ndi pepalalo.

   Momwemonso m'munda Makhalidwe Abwino a x lowani adilesi "Chaka" ndi zambiri za nthawi yapita.

   Pambuyo pazidziwitso zonse zalowetsedwa, dinani batani "Zabwino".



4. Wogwiritsa ntchito amawerengera kutengera ndi zomwe adalowetsazo ndikuwonetsa zotsatira zake pazenera. Kwa chaka cha 2018, lakonzedwa kuti lipindule m'chigawo cha rubles 4,564.7,000. Kutengera ndi tebulo lomwe lotsatira, titha kupanga graph pogwiritsa ntchito zida zomwe takambirana pamwambapa.



5. Mukasintha chaka mu cell yomwe idagwiritsidwa ntchito kuti mupeze mkangano, zotsatira zake zidzasintha motero, ndipo ndandandayo imangosintha zokha. Mwachitsanzo, malinga ndi kulosera kwa chaka cha 2019, kuchuluka kwa phindu kudzakhala ma ruble 4637.8,000.

Koma musaiwale kuti, monga momwe zimamangidwira mzere wogwiritsa ntchito, nthawi yomwe nthawi yolosera isanachitike sayenera kupitirira 30% ya nthawi yonse yomwe yosunga nkhokwe inasungidwa.

Phunziro: Kupitilira mu Excel

Njira 3: Wogwiritsa ntchito TREND

Zoneneratu, mutha kugwiritsa ntchito ntchito ina - TREND. Ilinso m'gulu la ogwiritsa ntchito manambala. Kapangidwe kake kamafanana ndi syntax chida CHITSANZO ndipo zikuwoneka ngati:

= TREND (Makhalidwe odziwika_yake; maadili odziwika_x; atsopano_values_x; [const])

Monga mukuwonera, zotsutsana Mfundo zodziwika bwino ndi Makhalidwe Abwino a x zimagwirizana kwathunthu ndi zinthu zofananira za opereshoni CHITSANZO, ndi mkangano "Mitundu yatsopano ya x" machesi otsutsana "X" chida cham'mbuyomu. Kuphatikiza apo, TREND pali mkangano wowonjezera "Konstant", koma ndiyosankha ndipo imagwiritsidwa ntchito pokhapokha ngati pali zina zomwe zikuchitika.

Wogwiritsa ntchitoyu amagwiritsidwa ntchito bwino kwambiri pakakhala kudalirika kwa ntchitoyo.

Tiyeni tiwone momwe chida ichi chidzagwirira ntchito ndi ndandanda yomweyo ya data. Kuti tiyerekeze zotsatirazi, tikufotokozera zomwe zidzachitike mu 2019.

1. Timasankha khungu kuti liwonetse zotsatira ndikuyendetsa Fotokozerani Wizard monga mwa masiku onse. Gulu "Zowerengera" pezani ndikuwonetsa dzinalo "TREND". Dinani batani "Zabwino".



2. Window Yogwiritsa Ntchito Itsegulidwa TREND. M'munda Mfundo zodziwika bwino ndi momwe tafotokozera pamwambapa timalowa m'malo mwa mgwirizanowu "Phindu la bizinesi". M'munda Makhalidwe Abwino a x lowani adilesi "Chaka". M'munda "Mitundu yatsopano ya x" timalowa cholumikizira ku foni komwe nambala ya chaka imayimiridwako M'malo mwathu, iyi ndi 2019. Mundawo "Konstant" siyani kanthu. Dinani batani "Zabwino".



3. Wogwiritsa ntchitoyo amawunikira ndikuwonetsa zotsatira zake pazenera. Monga mukuwonera, kuchuluka kwa ndalama zomwe akuyerekeza za 2019, zowerengedwa ndi njira yodalira mzere, zidzakhala, monga momwe mawerengero apitilira, ma ruble 4637.8,000.

Njira 4: wothandizira wa GROWTH

Ntchito ina yomwe ingagwiritsidwe ntchito kulosera ku Excel ndi opanga ma GROWTH. Ilinso ya gulu la zida, koma, mosiyana ndi momwe zidapangidwira, mukaziwerengera, sizigwiritsa ntchito njira yodalira mzere, koma yowonjezera. Kuphatikizika kwa chida ichi ndi motere:

= GROWTH (Makhalidwe odziwika_yake; odziwika odziwika_x; atsopano_values_x; [const])

Monga mukuwonera, mfundo za ntchitoyi zimangobwereza mawu a wothandizira TREND, chifukwa sitikhala pamafotokozedwe awo kachiwirinso, koma pitilizani momwe mungagwiritsire ntchito chida ichi.

1. Timasankha foni potulutsa zotsatirazo ndikuyitcha monga momwe zimakhalira Fotokozerani Wizard. Pamndandanda wa ogwiritsa ntchito manambala, yang'anani chinthucho ROST, sankhani ndikudina batani "Zabwino".



2. Tsamba lazotsutsana la ntchito ili pamwambapa limayambitsa. Lowetsani zosankha m'mazenera awa momwemo momwe tidawalembera pawindo la otsutsa TREND. Pambuyo pazidziwitsozo zalowetsedwa, dinani batani "Zabwino".



3. Zotsatira zakusintha kwa deta zikuwonetsedwa pa pilo mu foni yomwe idasonyezedwapo kale. Monga mukuwonera, nthawi ino zotsatira zake ndi 4682.1 zikwi rubles. Zosiyana kuchokera pazotsatira za kusanthula deta TREND zazing'ono, koma zilipo. Izi ndichifukwa choti zidazi zimagwiritsa ntchito njira zosiyanasiyana zowerengera: njira yodalira mzere ndi njira yodalira.

Njira 5: Wothandizira LINEAR

Wogwiritsa ntchito LERO powerengera amagwiritsa ntchito njira yochezera mzere. Siyenera kusokonezedwa ndi njira yodalira mzere yomwe imagwiritsidwa ntchito ndi chida. TREND. Matchulidwe ake ndi awa:

= LINE (Zodziwika_yabwino; odziwika odziwika_x; zatsopano_values_x; [const]; [ziwerengero])

Mikangano iwiri yomaliza ndiyosankha. Ndi awiri oyambayo, tikudziwa njira zam'mbuyomu. Koma mwina mwazindikira kuti palibe mkangano m'ntchitoyi womwe umalozera pazinthu zatsopano. Chowonadi ndi chakuti chida ichi chimangosintha kusintha kwa ndalama pakadutsa nyengo iliyonse, yomwe ife tili nayo chaka chimodzi, koma tikuyenera kuwerengera zonse payekhapayekha, ndikuwonjezera zotsatira za owerenga pamalonda ake omaliza LEROkuchuluka kwa zaka.

1. Timasankha foni yomwe kuwerengera kukachitika ndikuyendetsa Ntchito Wizard. Sankhani dzinalo LINEIN m'gulu "Zowerengera" ndipo dinani batani "Zabwino".



2. M'munda Mfundo zodziwika bwino, zenera lotseguka lotsutsa, lowetsani zolumikizana "Phindu la bizinesi". M'munda Makhalidwe Abwino a x lowani adilesi "Chaka". Minda yotsalira siyosowa. Kenako dinani batani "Zabwino".



3. Pulogalamuyi imawerengera ndikuwonetsa mtengo wolozera mu khungu losankhidwa.



4. Tsopano tikuyenera kudziwa kukula kwa phindu lomwe akuyembekezeralo la 2019. Khazikitsani chikwangwani "=" ku khungu lililonse lopanda chilichonse. Timadula khungu lomwe limakhala ndi phindu lenileni la chaka chomaliza chophunzira (2016). Timayika chikwangwani "+". Kenako, dinani pa foni yomwe ili ndi njira zomwe zidawerengedwa kale. Timayika chikwangwani "*". Popeza pakati pa chaka chatha cha kafukufuku (2016) ndi chaka chomwe mukufuna kunena zolosera (2019), nthawi ya zaka zitatu chagona, tinayika nambala m'chipindacho "3". Kuti muwerengere dinani batani Lowani.

Monga mukuwonera, malire akuyerekeza phindu omwe amawerengedwa ndi njira yolumikizira mzere mu 2019 adzafika ma ruble 4,614.9,000.

Njira 6: Wogwiritsa ntchito LGRFPPRIBLE

Chida chomaliza chomwe tidzayang'ana chidzakhala LGRFPPRIBLE. Wogwiritsa ntchito iyi amawerengera potengera njira yowonjezera yowonjezera. Kapangidwe kake kamakhala ndi mawonekedwe awa:

= LGRFPRIBLE (maadili odziwika_yonse; odziwika odziwika_x; zatsopano_values_x; [const]; [ziwerengero])

Monga mukuwonera, mfundo zonse zimangobwereza zomwe zikugwirizana ndi ntchito yapita. Chiwonetsero chowerengera cha algorithm chidzasintha pang'ono. Ntchitoyi imawerengera momwe zimawonekera, zomwe zikuwonetsa kuchuluka kwandalama zomwe zingasinthe kwa nthawi imodzi, ndiye kuti, kwa chaka chimodzi. Tidzafunika kupeza kusiyanasiyana pakati pa nthawi yotsiriza komanso yoyamba yomwe tinakonzekereratu, kuchulukitsa ndi kuchuluka kwa nthawi zomwe takonzekera (3) onjezani pazotsatira zake kuchuluka kwa nthawi yotsiriza.

1. Pamndandanda wa omwe akugwira ntchito ya Wizard Yogwira Ntchito, sankhani dzinalo LGRFPPRIBL. Dinani batani "Zabwino".



2. Windo la mkangano liyamba. Mmenemo, timalowetsa ndendende momwe timapangira, pogwiritsa ntchito ntchitoyo LERO. Dinani batani "Zabwino".



3. Zotsatira zakuwonekera zimawerengeredwa ndikuwonetsedwa mu khungu losankhidwa.



4. Timayika chikwangwani "=" m'chipinda chopanda kanthu. Tsegulani mabatani ndikusankha khungu lomwe lili ndi mtengo wolipirira nyengo yotsiriza. Timayika chikwangwani "*" ndikusankha khungu lomwe lili ndi mawonekedwe ofotokozera. Tikuyika chizindikiro chochotsera ndikudinanso pazinthu zomwe phindu la ndalama zomaliza limakhalapo. Tsekani bulaketi ndikuyendetsa mu zilembo "*3+" opanda mawu. Ndiponso, dinani pa foni yomweyo yomwe idasankhidwa komaliza. Kuti muchite kuwerengera, dinani batani Lowani.

Kuchuluka kwa phindu mu 2019, komwe kuwerengeredwa ndi njira yowonetsera exponential, kudzakhala ma ruble 4,639.2, zomwe sizimasiyananso kwambiri pazotsatira zomwe zidawerengedwa kale.

Phunziro: Ntchito zina zowerengera mu Excel

Tidazindikira momwe tingapangire zolosera mu pulogalamu ya Excel. Izi zitha kuchitika modabwitsa pogwiritsa ntchito chingwe chowongolera, komanso kugwiritsa ntchito njira zowerengera zingapo. Chifukwa chofufuza zofanana ndi zomwe ogwiritsira ntchito awa, zotsatira zosiyana zitha kupezeka. Koma izi sizosadabwitsa, chifukwa onse amagwiritsa ntchito njira zosiyanasiyana zowerengera. Ngati kusinthaku ndikocheperako, ndiye kuti zosankha zonsezi zomwe zikugwirizana ndi vuto linalake zitha kuonedwa ngati zodalirika.