Kuwerengera kwa mgwirizano wa kutsimikiza mu Microsoft Excel

Chimodzi mwazomwe zikufotokozera mtundu wa mtundu wopangidwira mu mawunikidwe ndi kuchuluka kwa kutsimikiza (R ^ 2), komwe kumatchulanso mtengo wotsimikizika wotsimikiza. Ndi iyo, mutha kudziwa mulingo wa kulosera. Tiyeni tiwone momwe mungawerengere chizindikiro ichi pogwiritsa ntchito zida zosiyanasiyana za Excel.

Kuwerengera kwa mgwirizano wolimba mtima

Kutengera ndi kuchuluka kwa kutsimikiza kwakukwanira, ndizachikhalidwe kugawa zitsanzozo m'magulu atatu:

• 0,8 - 1 - chitsanzo chabwino;
• 0,5 - 0,8 - chitsanzo cha zovomerezeka;
• 0 - 0,5 - mtundu wosakhala bwino.

Potsirizira pake, mawonekedwe amtunduwo akuwonetsa kuthekera kwa kugwiritsidwa ntchito kwake polosera.

Kusankha kwamomwe mungawerengere mtengo womwe mwatchulidwa mu Excel zimatengera kuti mawuwo ndiwofanana kapena ayi. Poyambirira, mutha kugwiritsa ntchito KVPIRSON, ndipo chachiwiri muyenera kugwiritsa ntchito chida chapadera kuchokera paziwonetsero.

Njira 1: kuwerengera mgwirizano wotsimikiza ndi ntchito ya mzere

Choyamba, tiona momwe tingapezere chidziwitso chotsimikizika chogwirira ntchito. Mwanjira iyi, chizindikirochi chidzakhala chofanana ndi mulingo wa cholumikizira. Tiziwerengera pogwiritsa ntchito ntchito yomanga ya Excel pazitsanzo za tebulo linalake, lomwe limaperekedwa pansipa.

1. Sankhani foni yomwe mgwirizano wotsimikiza udawonetsedwa pambuyo powerengera, ndikudina chizindikiro "Ikani ntchito".



2. Iyamba Fotokozerani Wizard. Kusamukira ku gulu lake "Zowerengera" lembani dzina KVPIRSON. Kenako dinani batani "Zabwino".



3. Ntchito yotsutsana ndi ntchito imayamba. KVPIRSON. Wogwiritsa ntchitoyu kuchokera ku gulu la mawerengero adapangidwa kuti awerengere kuchuluka kwa kuphatikiza kwa ntchito ya Pearson, ndiko kuti, ntchito yothandizira. Ndipo monga momwe timakumbukirira, ndi ntchito yapa mzere, chogwirira ntchito motsimikiza ndofanana ndendende ndi kuphatikiza kogwirizana.

   Mawu osakira akuti:

   = KVPIRSON (mitundu yodziwika_; odziwika_x)

   Chifukwa chake, ntchito ili ndi ogwiritsira ntchito awiri, imodzi ndi mndandanda wazikhalidwe zogwira ntchito, ndipo yachiwiri ndi mkangano. Ogwiritsira ntchito akhoza kuyimiridwa mwachindunji monga momwe zimakhalira ndi semicolon (;), komanso mawonekedwe amalumikizidwe kumigawo yomwe amapezeka. Iyi ndi njira yomaliza yomwe tidzagwiritse ntchito pa izi.

   Khazikitsani chotembezera m'munda Mfundo zodziwika bwino. Tili ndi batani lakumanzere ndikusankha zomwe zili patsamba "Y" matebulo. Monga mukuwonera, adilesi yamndandanda womwe wakonzedweratu amawonetsedwa pawindo.

   Mwanjira yomweyo, dzazani mundawo Makhalidwe Abwino a x. Ikani chidziwitso mu gawo ili, koma tsopano sankhani mfundo za mzere "X".

   Pambuyo pazidziwitso zonse zawonetsedwa pazenera la zotsutsa KVPIRSONdinani batani "Zabwino"ili pansi pake.



4. Monga mukuwonera, izi zitatha pulogalamuyi imawerengera zolondola kwambiri ndipo imawonetsa zotsatira mu foni yomwe idasankhidwa ngakhale kuitana Ogwira Ntchito. Mwa chitsanzo chathu, kufunika kwa chizindikiro chowerengedwa kunasandulika kukhala 1. Izi zikutanthauza kuti chitsanzo chomwe chatumizidwacho ndichodalirika, ndiye kuti chimachotsa cholakwikacho.

Phunziro: Wizard wa Zinthu mu Microsoft Excel

Njira 2: kuwerengera kuchuluka kwa kutsimikiza mu ntchito zosagwirizana

Koma njira yomwe ili pamwambapa pakuwerengera kufunika komwe ingagwiritsidwe ntchito kokha ku ntchito za mzere. Zoyenera kuchita kuti kuwerengetsa kukhala kosagwira ntchito? Ku Excel pali mwayi wotere. Itha kuchitika ndi chida. "Regression"yomwe ndi gawo la phukusi "Kusanthula Kwambiri".

1. Koma musanagwiritse ntchito chida chotsimikizidwa, muyenera kuchiyambitsa nokha Mapaketi Osanthula, yomwe imalemedwa ndi kusakhazikika ku Excel. Pitani ku tabu Fayilokenako pitani "Zosankha".



2. Pa zenera lomwe limatseguka, sinthani ku gawo "Zowonjezera" poyang'ana mndandanda wazolowera kumanzere. Pansi pazenera lamanja la zenera pali munda "Management". Kuchokera pamndandanda wamagawo omwe akupezeka pamenepo, sankhani dzinalo "Onjezani anthu ena ..."kenako dinani batani "Pita ..."ili kumanja kwa munda.



3. Iwindo lowonjezera lakhazikitsidwa. Chapakati pake pali mndandanda wazowonjezera. Ikani bokosi loyang'ana pafupi ndi pomwe pali udindo Mapaketi Osanthula. Kutsatira izi, dinani batani "Zabwino" kumanja kwa mawonekedwe a zenera.



4. Phukusi la chida "Kusanthula Kwambiri" mu mawonekedwe aposachedwa a Excel adzayambitsa. Kufikira kwa iyo kuli pa riboni pa tabu "Zambiri". Timasinthira kumtundu wololedwa ndikudina batani "Kusanthula Kwambiri" m'magulu azokonda "Kusanthula".



5. Zenera limayatsidwa "Kusanthula Kwambiri" ndi mndandanda wazida zopangira zida zapadera. Sankhani chinthu kuchokera pamndandanda "Regression" ndipo dinani batani "Zabwino".



6. Kenako zenera la chida limatseguka "Regression". Cholembera choyamba cha makonzedwe ndi "Zowonjezera". Apa m'magawo awiri muyenera kufotokoza ma adilesi amalo omwe malingaliro amakono ndi ntchito amapezeka. Ikani wolemba m'munda "Ikani zolowera Y" ndikusankha zomwe zili patsamba "Y". Pambuyo adilesi yakusonyezedwa iwonetsedwa pazenera "Regression"ikani otemberera m'munda "Ikani zolowera Y" ndikusankha maselamu omwe ali munjira yomweyo "X".

   About magawo "Label" ndi Konstant Zero osayika mbendera. Bokosi loyang'anira likhoza kukhazikitsidwa pafupi ndi paramayo. "Mulingo wodalirika" ndipo m'munda utayang'anani, sonyezani kufunikira kwa chizindikiro chofananira (95% mwakusoweka).

   Mu gululi Zosankha muyenera kutchulapo komwe kuwerengera kuzawonetsedwa. Pali njira zitatu:

   • Dera lomwe lili patsamba lino;
   • Tsamba lina;
   • Buku lina (fayilo yatsopano).

   Tiyeni tisankhe njira yoyamba kuti zomwe zikupezeka ndi zomwe zalembedwazi zikuyikidwa papepala lofanana. Tikuyika kusinthaku pafupi ndi gawo "Patuluka Pakatikati". M'munda moyang'anizana ndi chinthu ichi, ikani chotemberera. Dinani kumanzere pachinthu chopanda papepala, chomwe chidapangidwa kuti chikhale gawo lamanzere lamanzere patebulo. Adilesi ya chinthu ichi iyenera kuwonetsedwa pazenera la zenera "Regression".

   Magulu a Paramondi "Otsala" ndi "Mwinanso" Nyalanyaza, chifukwa sizofunikira pakuwongolera ntchitoyi. Pambuyo pake, dinani batani "Zabwino"ili pakona yakumanja ya zenera "Regression".



7. Pulogalamuyi imawerengera potengera deta yomwe idalowetsedwa kale ndikuwonetsa zotsatira zake pamitundu yomwe idafotokozedwayo. Monga mukuwonera, chida ichi chikuwonetsa zambiri pazotsatira zosiyanasiyana pamapepala. Koma potengera phunziroli, tili ndi chidwi ndi chisonyezo R lalikulu. Mwanjira iyi, ndi ofanana ndi 0.947664, omwe amakhala ndi mtundu wosankhidwa monga mtundu wa zabwino.

Njira 3: Kukwanira bwino pamzera wa zomwe zikuchitika

Kuphatikiza pazosankha pamwambapa, kukwaniritsa kutsimikiza kungawonetsedwe mwachindunji kwa mzere wazithunzi mu graph yomwe ili patsamba lapa Excel. Tiona momwe izi zingachitikire ndi zitsanzo zenizeni.

1. Tili ndi chithunzi chojambulidwa pa tebulo la zokutsutsana ndi zomwe zidagwiritsidwa ntchito pazitsanzo zam'mbuyomu. Tipanga mzere wofikira. Timadina pamalo aliwonse omanga omwe tchati mwaikidwapo, ndi batani lakumanzere. Nthawi yomweyo, ma tabu owonjezera amawonekera pa riboni - "Kugwira ntchito ndi ma chart". Pitani ku tabu "Kamangidwe". Dinani batani Chikhalidweyomwe ili mgululi "Kusanthula". Menyu imawoneka ndi kusankha mtundu wa mzere wosintha. Timayimitsa kusankha mtundu womwe ukufanana ndi ntchito inayake. Tiyeni tisankhe njira yachitsanzo chathu "Makamaka.



2. Excel amapanga mzere wozungulira ngati wopendekera chakuda chakuda pa tchati.



3. Tsopano ntchito yathu ndikuwonetsa chidziwitso chokwanira chotsimikiza chokha. Dinani kumanja pamzere wazomwe zikuchitika. Zosankha zamakina adatha. Timayimitsa kusankha kwake "Mawonekedwe a mzere ...".

   

   Kuti mukwaniritse kusintha pawindo lazithunzi, mutha kuchita china. Sankhani mzere wosintha ndikudina batani lakumanzere. Pitani ku tabu "Kamangidwe". Dinani batani Chikhalidwe mu block "Kusanthula". Pamndandanda womwe umatsegulira, dinani chinthu chomaliza pamndandanda wazinthu - "Zowonjezera mzere mzere ...".



4. Pambuyo pa zonse ziwiri zomwe tafotokozazi, pawindo la mawonekedwe limayambitsidwa momwe mungapangire zina. Makamaka, kuti tikwaniritse ntchito yathu, ndikofunikira kuyang'ana bokosi pafupi "Ikani chithunzi cholimba (R ^ 2) pazithunzi". Ili pamunsi pake pazenera. Ndiye kuti, mwanjira imeneyi timatha kuwonetsa kuwonetsedwa kwathunthu pakugwira ntchito yomanga. Kenako musaiwale kudina batani Tsekani pansi pazenera pano.



5. Mtengo wa kudalirika kwa kuyandikira, ndiye kuti, phindu la chitsimikizo cha kutsimikiza, likuwonetsedwa pa pepala m'deralo lomanga. Mwanjira iyi, mtengo wake, monga momwe tikuwonera, ndi 0,9242, womwe umawonetsera kuyandikira kwake ngati chitsanzo chabwino.



6. Mwamtheradi mwanjira iyi mutha kukhazikitsa chiwonetsero cha kutsimikiza kwa kutsimikiza kwa mtundu wina uliwonse wa mzere. Mutha kusintha mtundu wa mzere wopanga kusintha mwa kusintha batani pa riboni kapena menyu wazenera pazenera la magawo ake, monga tawonera pamwambapa. Kenako pazenera lokha pagulu "Kupanga mzere" Mutha kusinthira ku mtundu wina. Nthawi yomweyo, musaiwale kuwongolera pamenepo "Ikani mtengo woyenera wotsimikizira pazithunzi" bokosi linayendera. Mukamaliza kuchita izi pamwambapa, dinani batani Tsekani m'makona akumunsi a zenera.



7. Ndi mtundu wa mzere, mzere wotsogola uli kale ndi chitsimikiziro chamlingo wofanana ndi 0.9477, womwe umadziwika kuti ndi wodalirika kwambiri kuposa mzere wazomwe tikufanizira kale.



8. Chifukwa chake, kusintha pakati pa mitundu yosiyanasiyana ya magwiritsidwe ndi kuyerekeza momwe muliri otsimikiza Kusankha kokhala ndi kogwirizira kopitilira muyeso kotsimikizika ndi kofunika kwambiri. Kutengera ndi izi, mutha kupanga zolosera zolondola kwambiri.

   Mwachitsanzo, kwa ife kunali kotheka kuyesa kuti mtundu wa polynomial ofanana ndi digiri yachiwiri ukhale wolimba mtima kwambiri. Kukwanira kotsimikiza pankhani iyi ndi 1. Izi zikusonyeza kuti njirayi ndiyodalirika, zomwe zikutanthauza kuti kupatula zolakwika zonse.

   

   Koma, nthawi yomweyo, izi sizitanthauza kuti pa tchati china mtundu wamtunduwu uzikhala wodalirika kwambiri. Kusankha mwadongosolo kwamtundu wa zomwe zikuchitika kumatengera mtundu wa ntchito pamaziko omwe tchati adapangidwapo. Ngati wogwiritsa ntchito alibe chidziwitso chokwanira kuti athe kuyerekezera zabwino kwambiri zomwe zingachitike ndi maso, ndiye njira yokhayo yolinganizira bwino ndikufanizira ma coefficients, monga zikuwonekera pachithunzi pamwambapa.

Werengani komanso:
Kupanga mzere wotsogola ku Excel
Kuyandikira kwa Excel

Pali njira ziwiri zazikulu zowerengera mgwirizano wopezeka mu Excel: kugwiritsa ntchito wothandizira KVPIRSON ndi kugwiritsa ntchito zida "Regression" kuchokera m'bokosi lothandizira "Kusanthula Kwambiri". Kuphatikiza apo, yoyambayo mwa njirazi idapangidwa kuti idzagwiritsidwa ntchito pokhapokha pochita ntchito ya mzere, ndipo njira inayo ingagwiritsidwe ntchito pafupifupi nthawi zonse. Kuphatikiza apo, ndizotheka kuwonetsa kokwanira kutsimikiza kwa mzere wazithunzi monga kufunika kwa chidaliro cha kuyandikira. Pogwiritsa ntchito chizindikiro ichi, ndizotheka kudziwa mtundu wa mzere womwe umakhala wolimba mtima kwambiri pantchito inayake.