Kugereranya muri Excel: inzira 5 zoroshye

Muburyo butandukanye bwo guhanura, ntibishoboka ko bidatanga urugero. Hamwe nacyo, urashobora kubyara ibipimo ngenderwaho no kubara ibipimo byateganijwe usimbuza inkomoko kuri simpler. Muri Excel, birashoboka, birashoboka gukoresha ubu buryo bwo guhanura no gusesengura. Reka turebe uburyo ubu buryo bushobora gukoreshwa muri gahunda yagenwe hamwe nibikoresho byubatswe.

Ishyirwa mu bikorwa ryagereranijwe

Izina ryubu buryo riva mu ijambo ry'ikilatini Proxima - "hafi" ririmo kugereranya no koroshya ibimenyetso bizwi, kubakayobora mu masoko kandi ni ishingiro ryayo. Ariko ubu buryo burashobora gukoreshwa muguhanga gusa, ahubwo no kwiga ibisubizo bisanzwe. Nyuma ya byose, kugereranya, mubyukuri, byorohereza amakuru yinkomoko, kandi verisiyo yoroshye biroroshye gushakisha.

Igikoresho nyamukuru gifite icyo cyoroshye gikorwa muri excel nukubaka umurongo. Ibyingenzi nuko hashingiwe ku kimenyetso kiriho, gahunda yimikorere ishushanyije mugihe kizaza. Intego nyamukuru yumurongo, uburyo ntabwo bigoye gukeka, iyi ni yo gutegura iteganyagihe cyangwa gutahura inzira rusange.

Ariko irashobora kubakwa hakoreshejwe bumwe muburyo butanu bwagereranijwe:

• Umurongo;
• Exponential;
• Logarithmic;
• Polinomial;
• Imbaraga.

Suzuma buri buryo muburyo burambuye ukundi.

Isomo: Uburyo bwo kubaka umurongo muri excel

Uburyo 1: umurongo woroshye

Mbere ya byose, reka dusuzume amahitamo yoroshye yo kugereranya, aribyo gukoresha imikorere yumurongo. Kubyo tuzibanda cyane muburyo burambuye, kubera ko tuzashyikiriza ibihe rusange biranga kandi kubundi buryo, mubyukuri kubaka gahunda nibindi, aho bisuzumye.

Mbere ya byose, tuzubaka gahunda, hashingiwe kuriyo tuzakora inzira yoroshye. Kubaka igishushanyo, dufata ameza aho ikiguzi cyigice cyakozwe nimihango ninyungu zijyanye muri iki gihe kiragereranijwe. Imikorere ishushanyije twubaka izerekana kwishingikiriza ku nyungu ziyongera zo kugabanya ikiguzi cyibicuruzwa.

1. Kubaka igishushanyo, mbere ya byose, tugaragaza inkingi "ikiguzi cyigice cyibicuruzwa" na "inyungu". Nyuma yibyo, twimukira kuri tab "shyiramo". Ibikurikira, kuri kaseti muri "igishushanyo" ibikoresho, kanda kuri buto "Ikibanza". Ku rutonde rufungura, hitamo izina "ahantu hamwe n'imirongo yoroshye n'ibimenyetso." Ubu bwoko bwibishushanyo bukwiye bwo gukorana numurongo ugaragara, bivuze ko gukoresha uburyo bwo kugereranya kuba indashyikirwa.



2. Gahunda yubatswe.



3. Kongeramo umurongo, hitamo kuri buto yimbeba iburyo. Ibikubiyemo biragaragara. Hitamo muriyo ikintu "ongeraho umurongo.".

   

   Hariho ubundi buryo bwo kongeramo. Mu itsinda ryinyongera rya tabs kuri "akazi hamwe nimbonerahamwe" lente, twimukira muri tab "imiterere". Ibikurikira, muri "Isesengura" Umwanyabikoresho, kanda kuri buto "umurongo. Urutonde. Kubera ko dukeneye gushyira mu bikorwa umurongo, noneho duhitamo "umurongo ugereranya" uhereye kumyanya yagenewe.



4. Niba ukomeje guhitamo uburyo bwa mbere bwibikorwa hamwe no kongeramo ibikubiyemo, idirishya rifungura.

   Muri parameter guhagarika "kubaka umurongo (kugereranya no koroshya umwanya)", twashizeho umwanya wa "umurongo".

   Niba ubishaka, urashobora kwinjizamo amatiku hafi yumwanya "Erekana uburinganire ku gishushanyo". Nyuma yibyo, igishushanyo kizerekana imikorere yimikorere yoroshye.

   Kandi, kuri twe, ni ngombwa kugereranya amahitamo atandukanye yo kugereranya kugirango ushireho amatiku yerekeye ikintu "shyira agaciro kagereranijwe kemewe ku gishushanyo (R ^ 2)". Iki kimenyetso gishobora gutandukana kuva kuri 0 kugeza 1. Ibyo ari hejuru, ikigereranyo nibyiza (gikomeye). Byemezwa ko nubunini bwiki kimenyetso 0.85 nibindi byavuzwe haruguru, byoroshye birashobora gufatwa nkizewe, kandi niba ibipimo biri hasi, noneho oya.

   Nyuma yo gukoresha ibintu byose byavuzwe haruguru. Kanda ahanditse "gufunga", washyizwe munsi yidirishya.



5. Nkuko mubibona, umurongo wibintu byubatswe ku mbonerahamwe. Hamwe numurongo ugereranya, ugaragazwa numurongo ugororotse. Uburyo bwerekanwe bwo koroshya burashobora gukoreshwa mubihe byoroshye mugihe amakuru aratandukanye ahubwo byihuse kandi kwishingikiriza kubikorwa byimikorere yimikorere biragaragara.

Byoroheje, bikoreshwa muri uru rubanza, bisobanurwa na formula ikurikira:

Y = ax + b

Muri byumwihariko, formula yacu ifata ubwoko bwubu:

Y = -0.1156x + 72,255

Agaciro k'ukuri kwukuri kagereranijwe kangana na 0.9418, nikisubizo cyemewe, kiranga koroshya, nkuko wizewe.

Uburyo bwa 2: Kugereranya exponential

Noneho reka dusuzume ubwoko bwihariye bwo kugereranya muri Excel.

1. Kugirango uhindure ubwoko bwumurongo wigitabo, hitamo hamwe na buto yimbeba iburyo hanyuma uhitemo "imiterere yumurongo ..." Muri menu yo gufungura.



2. Nyuma yibyo, imiterere yidirishya ryari rimaze kumenyera ryatangijwe. Mu gice cyo gutoranya Ubwoko bwo gutoranya, twashyize ahagaragara umwanya wa "Exponential". Igenamiterere risigaye rizasiga kimwe no ku rubanza rwa mbere. Kanda kuri buto "Gufunga".



3. Nyuma yibyo, hazubakwa kumurongo. Nkuko tubibona, mugihe dukoresheje ubu buryo, bufite uburyo bumwe. Muri uru rubanza, urwego rwizewe rungana na 0.9592, ruri hejuru mugihe ukoresheje umurongo ugereranya umurongo. Uburyo bwerekana neza bukoreshwa neza murubanza mugihe indangagaciro zambere zahinduwe vuba, hanyuma ufate ifishi iringaniye.

Kurerekana muri rusange imikorere yonono ni nkibi:

y = kuba ^ x x

Aho e ari ishingiro rya logarithm karemano.

Muri byumwihariko, formula yacu yafashe ifishi ikurikira:

Y = 6282.7 * E ^ (- 0,012 * x)

Uburyo 3: Logarithmic

Noneho niyo magara gusuzuma uburyo bwa logarithmic.

1. Muri ubwo buryo, nkigihe cyambere binyuze muri menu ya menu, dutangira umurongo wumurongo. Dushiraho guhinduranya kumwanya "logarithmic" hanyuma ukande kuri buto "gufunga".



2. Inzira yo kubaka umurongo hamwe na logarithmic formication ibaho. Nko mu rubanza rwabanje, nibyiza gukoresha ubu buryo mugihe ubanza guhinduka, hanyuma ufate neza. Nkuko mubibona, urwego rwizewe ni 0.946. Ibi birarenze iyo ukoresheje uburyo bumwe, ariko munsi yubwiza bwumurongo mugihe cyerekana neza.

Muri rusange, formula yoroshye isa nkiyi:

y = a * ln (x) + b

Aho ln ari ubunini bwa logarithm karemano. Niyo mpamvu izina ryuburyo.

Ku bitureba, formula ifata urupapuro rukurikira:

y = -62,81ln (x) +404.96

Uburyo 4: Polynomial Yoroheje

Yaje gusuzuma uburyo bworoshye bwa polynomial.

1. Jya kumurongo wumurongo widirishya, nkuko bimaze gukorwa inshuro zirenze imwe. Muri "umurongo wubaka ubwubatsi" blok, twashyize ahagaragara umwanya "Polynomial". Iburyo bwiki kintu ni "impamyabumenyi". Mugihe uhitamo agaciro "Polynomial" Ihinduka. Hano urashobora kwerekana agaciro k'amashanyarazi kuri 2 (yashyizweho na missious) kugeza 6. Iki kimenyetso kigena umubare wimikorere ya Maxima na MINIMA. Mugihe ushyiraho polinomial kurwego rwa kabiri, ntarengwa gusa, kandi mugihe Polinomial ya gatandatu yashizwemo, kugeza kuri exima eshanu irashobora gusobanurwa. Gutangira, usige igenamiterere risanzwe, ni ukuvuga, tuzagaragaza urwego rwa kabiri. Igenamiterere risigaye risimbuka nkuko tubishyira muburyo bwabanje. Kanda ahanditse "gufunga".



2. Umurongo ukoresheje ubu buryo wubatswe. Nkuko mubibona, biratandukanye cyane kuruta gukoresha icyerekezo cyerekana. Urwego rwo kwizerwa ruri hejuru cyane muburyo bumwe bwakoreshejwe, kandi ni 0.9724.

   

   Ubu buryo bukoreshwa cyane mugihe amakuru ahora ahinduka. Umurimo usobanura ubu bwoko bworoshye busa:

   y = a1 + a1 * x + a2 * x ^ 2 + ... + an * x ^ n

   Ku bitureba, formula yafashe ubu bwoko:

   Y = 0.0015 * x ^ 2-1,7202 * x + 507.01

3. Noneho reka duhindure urwego rwa Polynomial kugirango turebe niba ibisubizo bizaba bitandukanye. Garuka ku idirishya. Ubwoko bwo kugereranya busigaye na Polynomial, ariko bitandukanye murwego rwidirishya, twashizeho agaciro gashoboka - 6.



4. Nkuko tubibona, nyuma yibyo, umurongo ukomoka kumurongo wavuzaga umurongo uvuga, aho umubare munini uhwanye na batandatu. Urwego rwo kwizerwa rwazamutse cyane, rugera 0.9844.

Formula isobanura ubu bwoko bwo koroshya, yafashe urupapuro rukurikira:

Y = 8e-08x ^ 6-0.0003x ^ 5 + 0,3725x ^ 4-269,33x ^ 3 + 10-2e + 07x + 0e + 0e + 09

Uburyo 5: Imbaraga zoroshye

Kurangiza, tekereza uburyo bwo kugereranya imbaraga muri excel.

1. Twimukiye muri "Imiterere yumurongo". Shyiramo ubwoko bworoshye kuri "imbaraga". Kwerekana ikigereranyo hamwe nurwego rwo kwizerwa, nkuko bisanzwe, usige. Kanda ahanditse "gufunga".



2. Porogaramu ikora umurongo. Nkuko mubibona, kuri twe, numurongo ufite urusenda ruto. Urwego rwo kwizerwa rungana na 0.9618, nicyo kimenyetso cyerekezo. Muburyo bwose bwasobanuwe haruguru, urwego rwizewe rwari rwinshi gusa mugihe ukoresheje uburyo bwa polynomial.

Ubu buryo bugira ingaruka mubikorwa byimpinduka zikomeye muriyi mikorere. Ni ngombwa gusuzuma ko ubu buryo bukurikizwa gusa niba imikorere n'impaka bidafata indangagaciro mbi cyangwa zeru.

Inzira nyabagendwa isobanura ubu buryo ifite ubu bwoko:

y = bx ^ n

By'umwihariko, kuberako ibibazo byacu bisa nkibi:

y = 6e + 18x ^ (- 6.512)

Nkuko dushobora kubibona, gukoresha amakuru yihariye twakoreshaga urugero, urwego runini rwo kwizerwa rwerekanye uburyo bwo kugereranya polynomial hamwe na polinomial ku rugero rwa gatandatu (0.9844), urwego ruto rwo kwizerwa mu buryo bumwe (0.9418). Ariko ibi ntibisobanura ko icyerekezo kimwe kizakoresha izindi ngero. Oya, urwego rwibikorwa muburyo bwavuzwe haruguru burashobora gutandukana cyane, bitewe nuburyo bwihariye bwimikorere yumurongo uzubakwa. Kubwibyo, niba kubwiki gikorwa uburyo bwatoranijwe bugira akamaro cyane, ntibisobanura ko aricyo nazo nanone ubwacyo nicyiciro mubindi bihe.

Niba udashobora guhita uzimye icyarimwe, ukurikije ibyifuzo byavuzwe haruguru, ni ubuhe buryo bwo kugereranya bukwiye mu buryo bwawe, ni ukuvuga, byumvikana kugerageza uburyo bwose. Nyuma yo kubaka umurongo no kureba urwego rwarwo rwo kwizerwa, urashobora guhitamo amahitamo meza.