ʻO kahi hiʻohiʻona o ka hana ʻana i kahi loiloi correlation ma Excel

ʻO kekahi o nā ʻano hana maʻamau i hoʻohana ʻia i ka ʻikepili no ke aʻo ʻana i ka ʻikepili, ʻo ia ka loiloi correlation, hiki ke hoʻohana ʻia e hoʻoholo i ka mana o kekahi nui ma luna o kekahi. E ʻike kākou pehea e hana ʻia ai kēia loiloi ma Excel.

Ke kumu o ka hoʻopili ʻana

Hiki iā ʻoe ke ʻike i ka hilinaʻi o kekahi hōʻailona ma kekahi, a inā ʻike ʻia, e helu coefficient pilina (degree of relation), hiki ke lawe i nā waiwai mai -1 a +1:

• inā he ʻino ka coefficient, he ʻokoʻa ka hilinaʻi, ʻo ia hoʻi, ʻo ka piʻi ʻana o kekahi waiwai e alakaʻi i ka emi ʻana o kekahi a me ka hope.
• inā maikaʻi ka coefficient, pololei ka hilinaʻi, ʻo ia ka piʻi ʻana o kahi hōʻailona e alakaʻi i ka piʻi ʻana o ka lua a me ka hope.

Hoʻoholo ʻia ka ikaika o ka hilinaʻi e ka modulus o ka coefficient correlation. ʻO ka nui o ka waiwai, ʻoi aku ka ikaika o ka hoʻololi ʻana i kekahi waiwai e pili ana i kekahi. Ma muli o kēia, me ka zero coefficient, hiki ke hoʻopaʻapaʻa ʻaʻohe pilina.

Hana ana i ka hoʻopili ʻana

No ke aʻo ʻana a hoʻomaopopo maikaʻi i ka hoʻopili ʻana, e hoʻāʻo kākou no ka papa ma lalo.

Eia ka ʻikepili ma ka awelika o ka mahana o kēlā me kēia lā a me ka haʻahaʻa haʻahaʻa no nā mahina o ka makahiki. ʻO kā mākou hana e ʻike inā he pilina ma waena o kēia mau ʻāpana a, inā pēlā, pehea ka ikaika.

Ka Papa Hana 1: E hoʻohana i ka hana CORREL

Hāʻawi ʻo Excel i kahi hana kūikawā e hiki ai iā ʻoe ke hana i kahi loiloi correlation - CORREL. Penei kona syntax:

КОРРЕЛ(массив1;массив2).

ʻO ke kaʻina hana no ka hana ʻana me kēia mea hana penei:

1. Kū mākou i loko o kahi keena manuahi o ka papaʻaina kahi e hoʻolālā ai mākou e helu i ka coefficient correlation. A laila kaomi i ka ikona “fx (hana hoʻokomo)” ma ka hema o ka papa formula.
2. Ma ka puka makani hoʻokomo hana i wehe ʻia, koho i kahi ʻano “Heluhelu” (ai ole “Ka papa inoa pīʻāpā piha”), i waena o nā koho i manaʻo ʻia e ʻike mākou “CORREL” a kaomi OK.
3. E hōʻike ʻia ka puka aniani hoʻopaʻapaʻa hana ma ka pale me ka cursor ma ke kahua mua e kū pono ana “Hui 1”. Maanei mākou e hōʻike ai i nā hoʻonohonoho o nā cell o ke kolamu mua (me ka ʻole o ka papa kuhikuhi), pono e nānā ʻia ka ʻikepili (i kā mākou hihia, B2:B13). Hiki iā ʻoe ke hana i kēia me ka lima ma ke kākau ʻana i nā huaʻōlelo i makemake ʻia me ka hoʻohana ʻana i ka kī. Hiki iā ʻoe ke koho pololei i ka laulā i makemake ʻia ma ka pākaukau ponoʻī ma ka hoʻopaʻa ʻana i ka pihi ʻiole hema. A laila neʻe mākou i ka hoʻopaʻapaʻa ʻelua “Hui 2”, ma ke kaomi wale ʻana i loko o ke kahua kūpono a i ʻole ke kaomi ʻana i ke kī Kuhi. Ma aneʻi mākou e hōʻike nei i nā koordinat o ka laulā o nā cell o ke kolamu i hoʻopaʻa ʻia ʻelua (ma kā mākou papaʻaina, ʻo ia C2:C13). Kaomi inā mākaukau OK.
4. Loaʻa iā mākou ka coefficient correlation i loko o ke kelepona me ka hana. ʻO ke ʻano "-0,63" hōʻike i kahi pilina hoʻololi ikaika ma waena o ka ʻikepili i kālailai ʻia.

Kaʻina 2: E hoʻohana i ka "Analysis Toolkit"

ʻO kahi ala ʻē aʻe e hana ai i ka loiloi correlation e hoʻohana “Ka helu ʻana o ka pūʻolo”, pono e hana mua. No kēia:

1. E hele i ka papa kuhikuhi “Faile”.
2. E koho i kekahi mea mai ka papa inoa ma ka hema “Nā palena”.
3. Ma ka puka aniani e kū mai ana, e kaomi i ka ʻāpana "Nā mea hoʻohui". A laila ma ka ʻaoʻao ʻākau o ka puka makani ma lalo loa no ka ʻāpana "Mākaha" koho "Hoʻohui Excel" a kaomi "Hele".
4. Ma ka puka aniani e wehe ana, e kaha “Poloke Analyse” a hōʻoia i ka hana ma ke kaomi ʻana i ke pihi OK.

Ua mākaukau nā mea a pau, “Poloke Analyse” hoʻāla ʻia. I kēia manawa hiki iā mākou ke neʻe i kā mākou hana nui:

1. Pahu i ka pihi “Ka helu ʻikepili”, aia ma ka pā “ʻIkepili”.
2. E hōʻike ʻia kahi puka aniani me kahi papa inoa o nā koho loiloi i loaʻa. Hoʻolauleʻa mākou “Hoʻoponopono” a kaomi OK.
3. E hōʻike ʻia kahi puka aniani ma ka ʻaoʻao kahi e pono ai ʻoe e kuhikuhi i kēia mau ʻāpana:
   • “Wā hookomo”. Koho mākou i ka laulā holoʻokoʻa o nā cell i kālailai ʻia (ʻo ia hoʻi, nā kolamu ʻelua i ka manawa hoʻokahi, ʻaʻole hoʻokahi i ka manawa, e like me ke ʻano i hōʻike ʻia ma luna).
   • “Ka hui pū ʻana”. ʻElua mau koho e koho ai: ma nā kolamu a me nā lālani. I kā mākou hihia, kūpono ka koho mua, no ka mea. ʻo ia ke ʻano o ka ʻikepili i kālailai ʻia ma ka pākaukau. Inā hoʻokomo ʻia nā poʻomanaʻo i ka laulā i koho ʻia, e nānā i ka pahu ma hope “Lepa ma ka laina mua”.
   • “Nā Koho Huakaʻi”. Hiki iā ʻoe ke koho i kahi koho “Waiho i waho”, i kēia hihia e hoʻokomo ʻia nā hopena o ka loiloi ma ka pepa o kēia manawa (pono ʻoe e kuhikuhi i ka helu o ke kelepona kahi e hōʻike ʻia ai nā hopena). Manaʻo ʻia e hōʻike i nā hopena ma kahi pepa hou a i ʻole ma kahi puke hou (e hoʻokomo ʻia ka ʻikepili ma ka hoʻomaka ʻana, ʻo ia hoʻi e hoʻomaka ana mai ke kelepona. (A1). Ma keʻano he laʻana, haʻalele mākou “Pepa Haʻawina Hou” (koho ʻia e ka paʻamau).
   • Ke mākaukau nā mea a pau, kaomi OK.
4. Loaʻa iā mākou ka coefficient correlation e like me ke ʻano mua. Hōʻike kēia i nā hihia ʻelua ua hana mākou i nā mea āpau.

Panina

No laila, ʻo ka hana ʻana i ka loiloi correlation ma Excel kahi kaʻina hana maʻalahi a maʻalahi ke aʻo. ʻO nā mea a pau āu e pono ai e ʻike i kahi e loaʻa ai a pehea e hoʻonohonoho ai i ka mea hana pono, a ma ke ʻano o “ pūʻolo hoʻonā”, pehea e ho'ā ai, inā ma mua ʻaʻole i hoʻohana ʻia i nā hoʻonohonoho papahana.