Chimodzi mwa zizindikiro zomwe zikufotokozera ubwino wa chitsanzo chogwiritsidwa ntchito pa chiwerengero ndi coefficient of determination (R ^ 2), yomwe imatchedwanso kuyerekezera chidziwitso. Ndicho, mungathe kuzindikira kuchuluka kwake kwa chiwonongeko. Tiyeni tione momwe mungathe kuwerengetsera chizindikiro ichi pogwiritsa ntchito zipangizo zosiyanasiyana za Excel.
Kuwerengera kwa coefficient of determination
Malingana ndi mlingo wa coefficient of determination, ndizozoloŵera kugawa mitundu kukhala magulu atatu:
- 0.8 - 1 - chitsanzo cha khalidwe labwino;
- 0.5 - 0.8 - chitsanzo cha khalidwe lovomerezeka;
- 0 - 0,5 - chitsanzo cha khalidwe losauka.
Pachifukwachi, khalidwe la chitsanzo limasonyeza kuti sitingagwiritse ntchito ntchitoyi.
Kusankha momwe mungadziŵerengere mtengo wotchulidwa mu Excel kumadalira ngati regression ndi yeniyeni kapena ayi. Poyamba, mungagwiritse ntchito ntchitoyi KVPIRSON, ndipo m'chiwiri muyenera kugwiritsa ntchito chida chapadera pa phukusi lofufuza.
Njira 1: kuwerengera kwa coefficient of determination ndi ntchito yaying'ono
Choyamba, fufuzani momwe mungapezere coefficient ya kutsimikiza kwa ntchito yeniyeni. Pachifukwa ichi, chizindikiro ichi chidzakhala chofanana ndi chiwerengero cha coefficient yolumikizana. Tidzachiwerengera pogwiritsira ntchito ntchito yopangidwa mu Excel pogwiritsa ntchito chitsanzo cha tebulo lapadera, zomwe zikuwonetsedwa pansipa.
- Sankhani selo momwe mpweya wokwanira udzasonyezedwe utatha kuwerengera, ndipo dinani pazithunzi "Ikani ntchito".
- Iyamba Mlaliki Wachipangizo. Pitani ku gulu lake "Zotsatira" ndipo lembani dzina KVPIRSON. Kenako, dinani pakani "Chabwino".
- Ntchitoyi zenera zowonekera. KVPIRSON. Wogwira ntchitoyi kuchokera ku gulu la chiwerengero chakonzedwa kuti awerengetse kokhala ya coefficient yolumikizana ya Pearson ntchito, ndiko, ntchito yeniyeni. Ndipo pamene tikukumbukira, ndi ntchito yowonjezera, coefficient of determination ndi ofanana ndi lalikulu ya coefficient coefficient.
Chidule cha mawu awa ndi:
= KVPIRSON (wodziwika_y; odziwika bwino_x)Choncho, ntchito ili ndi otsogolera awiri, imodzi mwa iwo ndi mndandanda wa zoyenera za ntchitoyi, ndipo yachiwiri ndi mkangano. Ogwira ntchito akhoza kuimiridwa monga mwachindunji monga malingaliro omwe adatchulidwa kupyolera mu semicolon (;), ndi mawonekedwe a zigawo zomwe zilipo. Ndi njira yotsiriza imene idzagwiritsidwe ntchito ndi ife mu chitsanzo ichi.
Ikani cholozera mmunda "Zomwe Amadziwika Y". Timagwiritsa ntchito batani lamanzere ndikusankha zomwe zili m'ndandanda. "Y" magome. Monga mukuonera, adiresi ya deta yolongosokayo imapezeka nthawi yomweyo pazenera.
Mofananamo mudzaze munda "Zodziwika x". Ikani cholozera mmunda uno, koma nthawi ino sankhani zamtengo wapatali "X".
Pambuyo pa deta yonse yasonyezedwa pazenera zotsutsana KVPIRSONdinani batani "Chabwino"ili pamunsi pake.
- Monga mukuonera, patatha izi, pulogalamuyi ikulinganiza coefficient of determination ndi kubwezera zotsatira kwa selo amene anasankhidwa pamaso pa kuyitana Oyang'anira ntchito. Mu chitsanzo chathu, kufunika kwa chizindikiro chowerengedwa kunakhala 1. Izi zikutanthauza kuti chitsanzo choperekedwacho ndi chodalirika, ndiko kuti, chimachotsa zolakwikazo.
PHUNZIRO: Wofalitsa Wothandizira ku Microsoft Excel
Njira 2: Kuwerengera kwa coefficient of determination mu ntchito nonlinear
Koma njirayi yapamwamba yowerengera mtengo wofunikila ingagwiritsidwe ntchito pazinthu zogwirizana. Kodi tingachite chiyani kuti tipeze chiwerengero chake? Mu Excel pali mwayi woterewu. Zingatheke ndi chida. "Kugonjetsa"yomwe ili gawo la phukusi "Kusanthula Deta".
- Koma musanagwiritse ntchito chida ichi, muyenera kuchisankha nokha. "Analysis Package"zomwe mosasintha zimaletsedwa ku Excel. Pitani ku tabu "Foni"ndikudutsamo "Zosankha".
- Muwindo lotseguka timasunthira ku gawo. Zowonjezera poyenda kupyola mndandanda wamanzere. Pansi pa malo oyenera pali munda "Management". Kuchokera pandandanda wa zigawo zomwe zilipo apo, sankhani dzina "Excel add-ins ..."kenako dinani pa batani "Pitani ..."ili kumanja kwa munda.
- Wowonjezera mawindo ayamba. Gawo lapakati ndi mndandanda wa zowonjezera zomwe zilipo. Onani bokosi pafupi ndi malo "Analysis Package". Pambuyo pa izi, dinani pa batani. "Chabwino" kumanja kwa mawonekedwe a mawindo.
- Pulogalamu yamagulu "Kusanthula Deta" pakali pano ya Excel idzatsegulidwa. Kufikira kwake kuli pavoni mu tab "Deta". Pitani ku tabu yeniyeniyo ndipo dinani batani. "Kusanthula Deta" mu gulu la zosankha "Kusanthula".
- Yatsegula zenera "Kusanthula Deta" ndi mndandanda wa zipangizo zamakono zothandizira zida. Sankhani pazinthu zamndandanda "Kugonjetsa" ndipo dinani pa batani "Chabwino".
- Kenaka chida chowonekera chimatsegulira. "Kugonjetsa". Chigawo choyamba cha zochitika - "Ikani". Pano m'minda iwiri muyenera kufotokoza maadiresi a mndandanda momwe malingaliro ndi ntchito zilipo. Ikani cholozera mmunda "Nthawi Yowonjezera Y" ndipo sankhani zomwe zili m'ndandanda pa pepala "Y". Pambuyo pa adiresi yowonjezera ikuwonetsedwa pawindo "Kugonjetsa"ikani malonda mmunda "Nthawi Yowonjezera Y" ndipo mwa njira chimodzimodzi sankhani maselo a mzere "X".
Pafupi magawo "Tag" ndi "Constant-zero" Makalata ochezera osankhidwa. Bokosi lochezera limatha kukhala pafupi ndi parameter "Kukhulupilika" ndi kumunda kutsutsana, tisonyezani kufunika kwa mtengo wofanana (mwachisawawa 95%).
Mu gulu "Zosankha Zotsatsa" muyenera kufotokoza kuti ndi chigawo chiti chomwe chiwerengero cha mawerengedwe chidzawonetsedwa. Pali njira zitatu:
- Chigawo pa tsamba laposachedwapa;
- Tsamba lina;
- Bukhu lina (fayilo yatsopano).
Tiyeni tiyimitse kusankha pa njira yoyamba yomwe deta yoyamba ndi zotsatira zinaikidwa pa tsamba limodzi. Ikani kusinthana pafupi ndi parameter "Kugawa Malo". M'munda moyang'anizana ndi chinthu ichi, ikani malonda. Timasindikiza batani lamanzere lachinsinsi pa chinthu chopanda kanthu pa pepala, chomwe chikulingalira kukhala khungu lakumwamba la gome la zotsatira za kuwerengera. Adilesi ya chinthu ichi iyenera kuwonetsedwa pawindo "Kugonjetsa".
Magulu apakati "Zotsala" ndi "Zowoneka" samanyalanyaza, chifukwa iwo si ofunika kuthetsa vuto. Pambuyo pake timatsegula batani. "Chabwino"yomwe ili pamwamba pa ngodya yapamwamba yawindo "Kugonjetsa".
- Pulogalamuyi ikuwerengera pa maziko a deta yomwe inalowetsedweratu ndipo imawonetsa zotsatira muzinthu zomwe zafotokozedwa. Monga mukuonera, chida ichi chikuwonetsera pa pepala chiwerengero chachikulu cha zotsatira pazigawo zosiyanasiyana. Koma mu phunziro la phunziro la tsopano tikukhudzidwa ndi chizindikiro "R-square". Pankhani iyi, ndi ofanana ndi 0.947664, omwe amasonyeza chitsanzo chosankhidwa monga chitsanzo cha khalidwe labwino.
Njira 3: Mzere wokwanira wa mzerewu
Kuphatikiza pa zosankha pamwambapa, coefficient of determination angasonyezedwe mwachindunji kwa mzere mzere mu graph yomangidwa pa Excel pepala. Tidzapeza momwe izi zingakhalire ndi chitsanzo cha konkire.
- Tili ndi galasi lochokera pa gome la mfundo ndi ziyeso za ntchito zomwe zinagwiritsidwa ntchito pa chitsanzo choyambirira. Tiyeni tipange njira yolowera. Timangodula pamalo alionse kumanga kumene grayi imaikidwa ndi batani lamanzere. Pa nthawi yomweyi, ma tebulo ena amawonekera pa ndodo - "Kugwira Ntchito ndi Mphatso". Pitani ku tabu "Kuyika". Timasankha pa batani "Mzere wazotsatira"yomwe ili mu chida chogwiritsa ntchito "Kusanthula". Menyu ikuwonekera ndi kusankha mtundu wamtundu wamtundu. Timasiya kusankha pa mtundu umene umagwirizana ndi ntchito inayake. Kwa chitsanzo chathu, tiyeni tizisankha "Zonenedwa Zophatikiza".
- Excel ndikumanga msewu wofanana ndi mtundu wina wamdima wakuda ku ndege yosanja.
- Tsopano ntchito yathu ndi kuwonetsa coefficient of determination palokha. Dinani molondola pa mzere wa mzere. Menyu ya nkhaniyi yatsegulidwa. Lekani kusankha mmenemo pa chinthucho "Mndandanda wamakono ...".
Kuti mutembenukire kuwindo lawonekedwe lazithunzi, mukhoza kuchita chinthu china. Sankhani ndondomeko yamtunduwu powasindikiza ndi batani lamanzere. Pitani ku tabu "Kuyika". Timasankha pa batani "Mzere wazotsatira" mu block "Kusanthula". Mndandanda umene umatsegulira, ife timasindikiza pa chinthu chotsiriza chomwecho m'ndandanda wa zochita - "Advanced Trend Line Options ...".
- Pambuyo pa zochitika ziwirizi zili pamwambazi, mawindo a mawonekedwe amayambika momwe mungapangire zochitika zina. Makamaka, kuti tichite ntchito yathu, m'pofunika kuyang'ana bokosi pafupi "Ikani pa tchati mtengo wa kulondola kwa kuyerekezera (R ^ 2)". Ili pamunsi pazenera. Izi zikutanthauza kuti mwa njirayi timaphatikizapo kuwonetsera kwa coefficient of determination pa malo omanga. Ndiye musaiwale kusindikiza batani "Yandikirani" pansi pawindo la tsopano.
- Kukhulupirira kwa kulingalira, ndiko kuti, mtengo wa coefficient of determination, udzawonetsedwa pa pepala m'deralo. Pachifukwa ichi, mtengo umenewu, monga momwe tikuonera, uli wofanana ndi 0.9242, womwe umayimira kuyerekezera, monga chitsanzo cha khalidwe labwino.
- Mwamtheradi kuti mutha kukhazikitsa chiwonetsero cha coefficient of determination kwa mtundu uliwonse wa mzere mzere. Mukhoza kusintha mtundu wa mzerewu mwa kusintha mwa batani pamtambo kapena mndandanda wazenera pazenera zake, monga momwe tawonetsera pamwambapa. Kenaka kale pawindo pa gululo "Kumanga mzere wazinthu" akhoza kusintha ku mtundu wina. Musaiwale kulamulira kuti pafupi ndi mfundoyo "Ikani pa chithunzi chofunika cha kulondola kwa kuyerekezera" adafufuzidwa. Mukamaliza masitepewa, dinani pa batani. "Yandikirani" kumbali ya kumanja yazenera yawindo.
- Pankhani ya mtundu wamtunduwu, mzere wamakono uli ndi mtengo wokwanira wokwanira wa 0.9477, umene umapereka chitsanzo ichi molimbika kwambiri kuposa mzere wa mtundu wa mtundu wawonetseredwe womwe tawunika kale.
- Choncho, mutasintha pakati pa mitundu yosiyanasiyana ya mizere yosiyana ndi kuyerekeza chikhalidwe chawo cha kulingalira kwake (coefficient chotsimikizirika), mungapeze kusiyana, chitsanzo chomwe chimamveka molondola galasi lomwe laperekedwa. Kusiyanitsa ndi ndondomeko yapamwamba ya kutsimikiza kudzakhala yodalirika kwambiri. Pachifukwa chake, mungathe kumanga maumboni olondola kwambiri.
Mwachitsanzo, chifukwa chathu, poyesera, tinatha kutsimikizira kuti msinkhu wodalirika ndi wa mtundu wa polynomial wa mzere wa chiwerengero chachiwiri. Coefficient of determination pankhaniyi ndi ofanana ndi 1. Izi zikusonyeza kuti chitsanzo ichi ndi chodalirika, chomwe chimatanthauza kuthetsa zolakwa zonse.
Koma panthawi yomweyi, izi sizikutanthauza kuti mtundu uwu wa mzerewu udzakhalanso wodalirika pa tchati china. Chisankho choyenera cha mtundu wa mzere wamakono chimadalira mtundu wa ntchito pogwiritsa ntchito yomwe graph inamangidwa. Ngati wogwiritsa ntchito alibe chidziwitso chokwanira kulingalira njira yabwino kwambiri, ndiye njira yokhayo yodziwira zowonongeka bwino ndikuyerekezera coefficients of determination, monga tawonetsedwa mu chitsanzo pamwambapa.
Onaninso:
Kumanga mizere yamtundu ku Excel
Kuchokera kwapafupi
Mu Excel pali njira ziwiri zofunika pakuwerengera coefficient of determination: pogwiritsa ntchito woyendetsa KVPIRSON ndi chida chogwiritsa ntchito "Kugonjetsa" kuchokera phukusi la zipangizo "Kusanthula Deta". Pachifukwa ichi, choyamba mwazimenezo ndizofunika kugwiritsidwa ntchito pokhapokha pakukonzekera ntchito yowonjezera, ndipo njira ina ingagwiritsidwe ntchito pafupifupi nthawi zonse. Kuwonjezera pamenepo, n'zotheka kuwonetsa coefficient of determination kuti mzere mzere wa graph monga kulingalira kugwirizana mtengo. Pogwiritsira ntchito chizindikiro ichi, n'zotheka kudziwa mtundu wa mzere womwe uli ndi chikhulupiliro chachikulu pa ntchito inayake.
