KOEFFIĊJENT TA 'DETERMINAZZJONI F'EXCEL

Wieħed mill-indikaturi li jiddeskrivu l-kwalità tal-mudell mibni fl-istatistika huwa l-koeffiċjent ta 'determinazzjoni (R ^ 2), li jissejjaħ ukoll il-valur ta' kunfidenza ta 'approssimazzjoni. Biha, tista 'tiddetermina l-livell ta' preċiżjoni tal-previżjoni. Ejja nsibu kif tista 'tikkalkula dan l-indikatur billi tuża diversi għodod ta' l-Excel.

Kalkolu tal - koeffiċjent tad - determinazzjoni. T

Skond il-livell tal-koeffiċjent tad-determinazzjoni, huwa soltu li l-mudelli jinqasmu fi tliet gruppi:

0.8-1 - mudell ta 'kwalità tajba;
0.5 - 0.8 - mudell ta 'kwalità aċċettabbli;
0 - 0,5 - mudell ta 'kwalità fqira.

Fil-każ tal-aħħar, il-kwalità tal-mudell tindika l-impossibbiltà tal-użu tiegħu għall-previżjoni.

L-għażla ta 'kif tikkalkula l-valur speċifikat f'Excel tiddependi fuq jekk ir-rigressjoni hijiex lineari jew le. Fl-ewwel każ, tista 'tuża l-funzjoni KVPIRSON, u fit-tieni ser ikollok bżonn tuża għodda speċjali mill-pakkett ta 'analiżi.

Metodu 1: kalkolu tal-koeffiċjent ta 'determinazzjoni b'funzjoni lineari

L-ewwelnett, issib kif issib il-koeffiċjent ta 'determinazzjoni għal funzjoni lineari. F'dan il-każ, dan l-indikatur ikun ugwali għall-kwadru tal-koeffiċjent tal-korrelazzjoni. Aħna se nikkalkulawha billi nużaw il-funzjoni Excel mibnija bl-eżempju ta 'tabella speċifika, li tidher hawn taħt.

Agħżel iċ-ċellola fejn il-koeffiċjent tad-determinazzjoni se jintwera wara l-kalkolu tagħha, u kklikkja fuq l-ikona "Daħħal il-funzjoni".

Tibda Wizard tal-Funzjoni. Ċaqlaq għall-kategorija tiegħu "Statistiku" u immarka l-isem KVPIRSON. Sussegwentement, ikklikkja fuq il-buttuna "OK".

It-tieqa tal-argumenti tal-funzjoni tibda. KVPIRSON. Dan l-operatur mill-grupp statistiku huwa ddisinjat biex jikkalkula l-kwadru tal-koeffiċjent tal-korrelazzjoni tal-funzjoni Pearson, jiġifieri funzjoni lineari. U kif niftakru, b'funzjoni lineari, il-koeffiċjent ta 'determinazzjoni huwa biss ugwali għall-kwadru tal-koeffiċjent ta' korrelazzjoni.
Is-sintassi għal din id-dikjarazzjoni hija:
= KVPIRSON (known_y; magħruf sewwa x)
Għalhekk, funzjoni għandha żewġ operaturi, li waħda minnhom hija lista ta 'valuri tal-funzjoni, u t-tieni hija argument. L-operaturi jistgħu jiġu rrappreżentati direttament bħala valuri elenkati permezz ta ’punt t;), u fil-forma ta 'links għall-firxiet fejn jinsabu. Hija l-aħħar għażla li se nużaw minna f'dan l-eżempju.
Issettja l-cursor fil-qasam "Valuri M Magħrufa". Aħna nagħmlu l-ikklampjar tal-buttuna tax-xellug tal-maws u nagħżlu l-kontenut tal-kolonna. "Y" tabelli. Kif tistgħu taraw, l-indirizz tal-firxa speċifikata tad-dejta jidher immedjatament fit-tieqa.
Bl-istess mod imla l-għalqa "Magħruf x". Poġġi l-cursor f'dan il-field, iżda din id-darba agħżel il-valuri tal-kolonna "X".
Wara li d-data kollha tkun ġiet murija fit-tieqa ta ’l-argumenti KVPIRSONikklikkja l-buttuna "OK"jinsabu fil-qiegħ tagħha stess.

Kif tistgħu taraw, wara dan, il-programm jikkalkula l-koeffiċjent tad-determinazzjoni u jirritorna r-riżultat liċ-ċellula magħżula qabel is-sejħa Funzjoni kaptani. Fl-eżempju tagħna, il-valur tal-indikatur ikkalkulat irriżulta li huwa 1. Dan ifisser li l-mudell ippreżentat huwa assolutament affidabbli, jiġifieri, jelimina l-iżball.

Lezzjoni: Funzjoni Wizard f'Microsoft Excel

Metodu 2: Kalkolu tal-koeffiċjent ta 'determinazzjoni f'funzjonijiet mhux lineari

Iżda l-għażla ta 'hawn fuq li tikkalkula l-valur mixtieq tista' tiġi applikata biss għal funzjonijiet lineari. X'għandek tagħmel biex tipproduċi l-kalkolu tagħha f’funzjoni mhux lineari? Fl-Excel hemm opportunità bħal din. Dan jista 'jsir b'għodda. "Regressjoni"li huwa parti mill-pakkett "Analiżi tad-Data".

Iżda qabel ma tuża din l-għodda, għandek tattivaha stess. "Pakkett ta 'Analiżi"li fil-kontumaċja huwa diżattivat fl-Excel. Mexxi għat-tab "Fajl"u mbagħad mur permezz tal-oġġett "Għażliet".

Fit-tieqa miftuħa nimxu lejn it-taqsima. Add-ons billi jinnavigaw fil-menu vertikali xellug. Fil-qiegħ tal-ħġieġa hija qasam "Ġestjoni". Mill-lista tas-subsezzjonijiet disponibbli hemm tagħżel l-isem "Excel add-ins ..."u mbagħad ikklikkja fuq il-buttuna "Mur ..."jinsabu fuq il-lemin tal-qasam.

It-tieqa tal-add-ons tibda. Fil-parti ċentrali hemm lista ta ’add-ins disponibbli. Iċċekkja l-kaxxa ħdejn il-pożizzjoni "Pakkett ta 'Analiżi". Wara dan, ikklikkja fuq il-buttuna. "OK" fuq in-naħa tal-lemin tat-tieqa ta ’l-interface.

Pakkett ta 'għodda "Analiżi tad-Data" fil-każ preżenti ta ’Excel se jiġi attivat. L-aċċess għaliha jinsab fuq it-tabella "Data". Ċaqlaq għat-tab speċifikat u kklikkja l-buttuna. "Analiżi tad-Data" fil-grupp tas-settings "Analiżi".

Tieqa attivata "Analiżi tad-Data" b’lista ta ’għodod speċjalizzati għall-ipproċessar ta’ l-informazzjoni. Agħżel minn din il-lista ta 'oġġetti "Regressjoni" u kklikkja fuq il-buttuna "OK".

Imbagħad tiftaħ it-tieqa tal-għodda. "Regressjoni". L-ewwel blokka ta 'settings - "Input". Hawn fiż-żewġ oqsma għandek bżonn tispeċifika l-indirizzi tal-meded fejn jinsabu l-valuri u l-funzjonijiet tal-argument. Poġġi l-cursor fil-qasam "L-intervall tad-dħul Y" u agħżel il-kontenut tal-kolonna fuq il-folja "Y". Wara l-firxa l-indirizz huwa muri fit-tieqa "Regressjoni"poġġi l-cursor fil-qasam "L-intervall tad-dħul Y" u eżattament bl-istess mod agħżel iċ-ċelloli tal-kolonna "X".
Dwar il-parametri "Tag" u "Kostanti-żero" checkboxes mhumiex issettjati. Il-kaxxa tal-kontroll tista 'tiġi ssettjata ħdejn il-parametru "Livell ta 'affidabbiltà" u fil-parti opposta, indika l-valur mixtieq ta 'l-indikatur korrispondenti (awtomatikament 95%).
Fil-grupp "Għażliet tal-Output" trid tispeċifika f'liema qasam ir-riżultat tal-kalkolu se jintwera. Hemm tliet għażliet:
- Żona fuq il-folja kurrenti;
- Folja oħra;
- Ktieb ieħor (fajl ġdid).
Ejja nwaqqfu l-għażla fuq l-ewwel għażla li d-data inizjali u r-riżultat tqiegħdu fuq folja tax-xogħol waħda. Poġġi s-swiċċ ħdejn il-parametru "Spazjar tal-Output". Fil-parti opposta din il-partita poġġi l-cursor. Nikklikkjaw il-buttuna tax-xellug tal-maws fuq l-element vojt fuq il-folja, li hija maħsuba biex issir iċ-ċellula ta 'fuq tax-xellug tat-tabella tar-riżultati tal-kalkolu. L-indirizz ta 'dan l-element għandu jintwera fit-tieqa "Regressjoni".
Gruppi ta 'parametri "Jibqa" u "Probabbiltà normali" jinjora, peress li mhumiex importanti għas-soluzzjoni tal-problema. Wara li nikklikkja fuq il-buttuna. "OK"li tinsab fil-kantuniera tal-lemin ta ’fuq tat-tieqa "Regressjoni".

Il-programm jikkalkula fuq il-bażi ta 'data mdaħħla qabel u juri r-riżultat fil-medda speċifikata. Kif tistgħu taraw, din l-għodda turi fuq il-folja numru pjuttost kbir ta 'riżultati fuq diversi parametri. Iżda fil-kuntest tal-lezzjoni kurrenti aħna huma interessati fl-indikatur "R-kwadru". F'dan il-każ, huwa ugwali għal 0.947664, li jikkaratterizza l-mudell magħżul bħala mudell ta 'kwalità tajba.

Metodu 3: il-koeffiċjent tad-determinazzjoni għal-linja tat-tendenza

Minbarra l-għażliet ta 'hawn fuq, il-koeffiċjent ta' determinazzjoni jista 'jintwera direttament għal-linja ta' tendenza fi graff mibni fuq folja Excel. Ahna nsibu kif dan jista 'jsir b'eżempju konkret.

Għandna graff ibbażata fuq it-tabella tal-argumenti u l-valuri tal-funzjoni li ntużat għall-eżempju preċedenti. Ejja nagħmlu linja ta 'tendenza għaliha. Nikklikkjaw fuq kwalunkwe post fiż-żona tal-kostruzzjoni li fuqha titqiegħed il-graff bil-buttuna tax-xellug tal-maws. Fl-istess ħin, sett addizzjonali ta ’tabs jidher fuq iż-żigarella - "Ħidma ma 'Ċarts". Mur fit-tab "Tqassim". Nikklikkja fuq il-buttuna "Trend line"li tinsab fil-blokka ta ’l-għodda "Analiżi". Jidher menu b'għażla ta 'tip ta' linja ta 'tendenza. Aħna nwaqqfu l-għażla fuq it-tip li jikkorrispondi għal kompitu speċifiku. Għall-eżempju tagħna, ejja nagħżlu "Approssimazzjoni esponenzjali".

Excel qed tibni linja ta 'tendenza fil-forma ta' kurva sewda addizzjonali fuq il-pjan taċ-ċart.

Issa l-kompitu tagħna huwa li nuru l-koeffiċjent tad-determinazzjoni nnifsu. Nikklikkjaw il-lemin fuq il-linja tat-tendenza. Il-menu tal-kuntest huwa attivat. Waqqaf l-għażla fiha fuq l-oġġett "Format tal-linja tax-xejra ...".
Biex tagħmel transizzjoni għat-tieqa tal-format tal-linja tat-tendenza, tista 'twettaq azzjoni alternattiva. Agħżel il-linja tat-tendenza billi tikklikkja fuqha bil-buttuna tax-xellug tal-maws. Mexxi għat-tab "Tqassim". Nikklikkja fuq il-buttuna "Trend line" fi blokk "Analiżi". Fil-lista li tiftaħ, nikklikkja fuq l-aħħar punt fil-lista ta ’azzjonijiet - "Għażliet Avvanzati tat-Trend tal-Linja ...".

Wara kwalunkwe waħda miż-żewġ azzjonijiet ta 'hawn fuq, titnieda tieqa tal-format li fiha tista' tagħmel settings addizzjonali. B'mod partikolari, biex twettaq il-kompitu tagħna, huwa neċessarju li tiċċekkja l-kaxxa ħdejn "Qiegħed fuq il-grafika l-valur tal-preċiżjoni tal-approssimazzjoni (R ^ 2)". Hija tinsab fil-qiegħ nett tat-tieqa. Jiġifieri, b'dan il-mod aħna nuru l-wiri tal-koeffiċjent ta 'determinazzjoni fuq iż-żona tal-kostruzzjoni. Imbagħad tinsiex li tagħfas il-buttuna "Agħlaq" fil-qiegħ tat-tieqa kurrenti.

Il-valur ta 'kunfidenza ta' l-approssimazzjoni, jiġifieri, il-valur tal-koeffiċjent ta 'determinazzjoni, se jintwerew fuq il-folja fl-erja tal-plott. F'dan il-każ, dan il-valur, kif naraw, huwa 0.9242, li jikkaratterizza l-approssimazzjoni, bħala mudell ta 'kwalità tajba.

Assolutament eżattament sabiex inti tista 'tissettja l-wiri tal-koeffiċjent ta' determinazzjoni għal kwalunkwe tip ieħor ta 'linja ta' tendenza. Tista 'tbiddel it-tip ta' linja ta 'tendenza billi tagħmel transizzjoni mill-buttuna fuq iż-żigarella jew il-kuntest tal-menu fit-tieqa tal-parametri tagħha, kif muri hawn fuq. Imbagħad diġà fit-tieqa fil-grupp "Nibnu linja ta 'tendenza" jistgħu jaqilbu għal tip ieħor. Tinsiex tikkontrolla hekk ħdejn il-punt "Qiegħed fuq il-grafika l-valur tal-preċiżjoni tal-approssimazzjoni" ġiet iċċekkjata. Wara li tlesti l-passi ta 'hawn fuq, ikklikkja fuq il-buttuna. "Agħlaq" fil-kantuniera tal-lemin t'isfel tat-tieqa.

Fil-każ ta 'tip lineari, il-linja tat-tendenza diġà għandha approssimazzjoni ta' valur ta 'kunfidenza ta' 0.9477, li jikkaratterizza dan il-mudell bħala saħansitra aktar affidabbli mil-linja ta 'tendenza tat-tip esponenzjali li aħna ikkunsidraw qabel.

Għalhekk, billi taqleb bejn tipi differenti ta 'linji ta' tendenza u tqabbel il-valuri tagħhom ta 'approssimazzjoni ta' fiduċja (koeffiċjent ta 'determinazzjoni), tista' ssib il-varjant, li l-mudell tiegħu jiddeskrivi l-iktar preċiż il-graff ippreżentat. Il-varjant bl-ogħla indiċi ta 'determinazzjoni jkun l-aktar affidabbli. Fuq il-bażi tagħha, tista 'tibni t-tbassir l-aktar preċiż.
Pereżempju, għall-każ tagħna, b'esperiment, irnexxielna nistabbilixxu li l-ogħla livell ta 'kunfidenza huwa t-tip polinomjali tal-linja tat-tendenza tat-tieni grad. Il-koeffiċjent ta 'determinazzjoni f'dan il-każ huwa ugwali għal 1. Dan jissuġġerixxi li dan il-mudell huwa assolutament affidabbli, li jfisser l-eliminazzjoni kompleta ta' l-iżbalji.
Iżda fl-istess ħin, dan ma jfissirx li din it-tip ta 'linja ta' tendenza tkun ukoll l-iktar affidabbli għal ċart ieħor. L-aħjar għażla tat-tip ta 'linja ta' tendenza tiddependi fuq it-tip ta 'funzjoni li fuq il-bażi tagħha nbniet il-graff. Jekk l-utent ma jkollux biżżejjed għarfien biex jistma l-għażla ta 'l-ogħla kwalità, allura l-uniku mod biex tiġi ddeterminata l-aħjar tbassir huwa biss paragun tal-koeffiċjenti ta' determinazzjoni, kif intwera fl-eżempju hawn fuq.

Ara wkoll:
Nibnu linji ta ’tendenzi f'Excel
Approssimazzjoni Excel

F'Excel hemm żewġ għażliet ewlenin għall-kalkolu tal-koeffiċjent ta 'determinazzjoni: l-użu ta' l-operatur KVPIRSON u għodda ta ’applikazzjoni "Regressjoni" mill-pakkett ta ’għodod "Analiżi tad-Data". F'dan il-każ, l-ewwel waħda minn dawn l-għażliet hija maħsuba għall-użu biss fl-ipproċessar ta 'funzjoni lineari, u għażla oħra tista' tintuża fi kważi s-sitwazzjonijiet kollha. Barra minn hekk, huwa possibbli li jintwera l-koeffiċjent ta 'determinazzjoni għal-linja ta' tendenza tal-graffs bħala valur ta 'kunfidenza ta' approssimazzjoni. Bl-użu ta 'dan l-indikatur, huwa possibbli li jiġi ddeterminat it-tip ta' linja ta 'tendenza li għandha l-ogħla livell ta' kunfidenza għal funzjoni partikolari.