Indlela yokulinganisela ku-Microsoft Excel

Phakathi kwezindlela ezihlukahlukene zokubikezela akunakwenzeka ukuhlukanisa ukulinganisa. Ngesizo salo, ungenza izibalo ezilinganiselwe bese ubala izinkomba ezihleliwe ngokufaka izinto zangaphambili ngezinto ezilula kakhulu. Ku-Excel, kukhona futhi amathuba okusebenzisa le ndlela yokubikezela nokuhlaziywa. Ake sibheke ukuthi le ndlela ingasetshenziswa kanjani ohlelweni olucacisiwe ngamathuluzi akhiwe ngaphakathi.

Ukwenziwa kwesilinganiso

Igama lale ndlela livela ngegama lesiLatini elithi proxima - "eliseduzane". Ukulinganisa ngokunciphisa izinkomba ezilula futhi ezilulazayo, ukuzakhela ekuthambekeni futhi kuyisisekelo sayo. Kodwa le ndlela ingasetshenziselwa ukubikezela kuphela, kepha futhi nokucwaninga imiphumela ekhona. Phela, ukulinganisa empeleni, ukulula idatha yedatha, futhi inguqulo elula kulula ukuyihlola.

Ithuluzi eliyinhloko elithuthukisayo eyenziwa ku-Excel yilapho yokwakhiwa komzila wendabuko. Okubalulekile ukuthi, ngokusekelwe ezinkomba ezikhona kakade, isimiso somsebenzi sigcwaliswe ngezikhathi ezizayo. Inhloso eyinhloko yesitayela semikhuba, njengoba kunzima ukuqagela, yenza ukubikezela noma ukukhomba ukujwayela okujwayelekile.

Kodwa ingakhiwa usebenzisa enye yezinhlobo ezinhlanu zokulinganisa:

• Linear;
• I-Exponential;
• I-Logarithmic;
• I-polynomial;
• Amandla.

Cabanga ngalunye lwezinketho ngokuningiliziwe ngokuningiliziwe.

Isifundo: Indlela yokwakha umugqa wendabuko ku-Excel

Indlela 1: Ukumangalisa okulinganayo

Okokuqala, ake sicabangele ukulinganisa okulula, okungukuthi, ukusebenzisa umsebenzi ohambisanayo. Sizohlala sibheke kulo ngokuningiliziwe, njengoba sichaza amaphuzu ajwayelekile abonisa ezinye izindlela, okungukuthi, ukuhlela nokunye okungahambisani nawo lapho sicabanga ngezinketho ezilandelayo.

Okokuqala, sizokwakha igrafu ngesisekelo esizokwenza ngayo inqubo yokupholisa. Ukwakha igrafu, sithatha itafula lapho izindleko zeyunithi yokukhiqizwa ezikhiqizwa ibhizinisi kanye nenzuzo ehambisanayo esikhathini esinikeziwe kuboniswa njalo ngenyanga. Umsebenzi odwebayo ozokwakhiwa uzobonisa ukuncika kokwanda kwenzuzo ngokunciphisa izindleko zokukhiqiza.

1. Ukwakha igrafu, okokuqala, khetha amakholomu "Izindleko zeyunithi yokukhiqiza" futhi "Inzuzo". Ngemva kwalokho kuthutha kuthebhu "Faka". Okulandelayo kwibhoni ebhokisini le "Imidwebo" ibhokisi lesithuluzi chofoza inkinobho "Indawo". Kulolu hlu oluvula, khetha igama "I-Dot ene-curves ebushelelezi namaka". Yilolu hlobo lwamashadi alungele kakhulu ukusebenza ne-line line, ngakho-ke, ngokusebenzisa indlela yokulinganisa ku-Excel.



2. Uhlelo olwakhiwe.



3. Ukwengeza umugqa wendabuko, ukhethe ngokuchofoza inkinobho yesokudla yegundane. Imenyu yomongo ivela. Khetha into kuwo "Engeza umugqa wendabuko ...".

   

   Kunenye indlela ongayifaka. Kwelinye iqembu lamathebhu ku-Ribbon "Ukusebenza ngamashadi" thuthela kuthebhu "Ukuhlelwa". Okulandelayo ebhokisini lamathuluzi "Ukuhlaziywa" chofoza inkinobho "Umugqa wethenda". Uhlu luvuleka. Njengoba kudingeka sisebenzise ukulinganisa okulinganiselwe, kusuka ezikhundleni ezinikeziwe esizikhethayo "Ukulinganisa okulinganiselwe".



4. Uma, noma kunjalo, ukhethe ukhetho lokuqala lwezenzo nge-kwalokho nge-menu yemongo, khona-ke ifasitrama ifomethi izovuleka.

   Ku-block parameter "Ukwakha umugqa wendabuko (ukulinganisa nokushelela)" setha inkinobho ukuma "Linear".
   Uma ufisa, ungasetha uphawu eduze kwesimo "Bonisa ukulingana eshadini". Ngemuva kwalokho, umdwebo uzobonisa ukulinganisa umsebenzi wokulinganisa.

   Kanti futhi kithi, ngokuqhathanisa izinketho ezihlukahlukene zokulinganisa, kubalulekile ukuhlola ibhokisi "Faka ishadi ukubaluleka kokulinganisa okunokwethenjelwa (R ^ 2)". Lesi sibonakaliso singahlukahluka 0 kuze kube 1. Okuphakeme, ukulinganisa okungcono (okuthembekile ngokwengeziwe). Kukholelwa ukuthi uma inani lale nkhomba likhona 0,85 futhi i-smoothing ephakeme ingabhekwa njengokwethenjelwa, futhi uma isibalo singaphansi, ngabe-cha.

   Ngemuva kokuthi ube nazo zonke izilungiselelo ezingenhla. Sicindezela inkinobho "Vala"efakwe ngaphansi kwefasitela.



5. Njengoba ubona, umugqa wesitayela uhlelwe eshadini. Uma kwenzeka ukulinganisa okulinganiselwe, kuboniswa ngomugqa omnyama. Lolu hlobo lwe-smoothing lungasetshenziswa ezimweni ezilula kakhulu, uma idatha ishintsha ngokushesha futhi ukuxhomeka kokusebenza komsebenzi kuxabano kuyabonakala.

Ukumangalisa, okusetshenziswe kulokhu, kuchazwa ngenqubo elandelayo:

y = ax + b

Esimweni sethu, ifomula ithatha leli fomu elilandelayo:

y = -0.1156x + 72.255

Ubukhulu bokunemba kwesilinganiso kufana nathi 0,9418, okuyinto imiphumela eyamukelekayo, echaza ukushelela njengokwethenjelwa.

Indlela yesi-2: Ukulinganisa okulinganiselwe

Manje ake sicabangele uhlobo lokuhlola lokulinganisa ku-Excel.

1. Ukuze ushintshe uhlobo lomugqa wendabuko, ukhethe ngokuchofoza inkinobho yegundane kwesokudla nasemenyu yokudonsela phansi khetha into "Ithebula lesikhangiso somugqa ...".



2. Ngemuva kwalokho, ifasitela lesifomethi esivele sijwayele kithi lisunguliwe. Esikhathini sokukhetha uhlobo lokulinganisa, setha ukushintshela "Exponential". Amasethingi asele ahlala efana nesimo sokuqala. Chofoza inkinobho "Vala".



3. Emva kwalokho, umugqa wendabuko uzohlelwa. Njengoba ungabona, uma usebenzisa le ndlela, inomumo ovalwe kancane. Izinga lokuzethemba liyi 0,9592, okuphakeme kunokuba uma usebenzisa ukulinganisa okulinganayo. Indlela yokuveza indlela yokusebenzisa kahle iyasetshenziswa kangcono uma amanani kuqala eshintsha ngokushesha bese ethatha ifomu elilinganisiwe.

Umbono jikelele wendlela yokukhwabanisa kanje:

y = be ^ x

kuphi e - yilona isisekelo se-logarithm yemvelo.

Esimweni sethu, ifomula ithatha ifomu elilandelayo:

y = 6282.7 * e ^ (- 0.012 * x)

Indlela ye-3: Faka i-Smoothing

Manje kuyithuba lokucabangela indlela yokulinganisa kwe-logarithmic.

1. Ngendlela efanayo nangesikhathi esidlule, ngokusebenzisa imenyu yokuqukethwe, qalisa iwindi lesitayela sefomethi yokuhamba. Setha inkinobho ukuma "I-Logarithmic" bese uchofoza inkinobho "Vala".



2. Kukhona inqubo yokwakha umugqa wendabuko ngokulinganisa kwe-logarithmic. Njengokwesikhashana esidlule, le ndlela ingcono ukuyisebenzisa uma idatha ishintsha ngokushesha ngokushesha, bese ubheka ngokulinganisela. Njengoba ubona, izinga lokuzethemba liyi-0.946. Lokhu kuphakeme kunokuba uma usebenzisa indlela ehambisanayo, kodwa ephansi kunekhwalithi yomugqa wendabuko ne-smoothing exponential.

Ngokuvamile, ifomula e-smoothing ibonakala kanje:

y = a * ln (x) + b

kuphi ln ubukhulu be-logarithm yemvelo. Ngakho igama lendlela.

Esimweni sethu, ifomula ithatha ifomu elilandelayo:

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

Indlela ye-4: i-polynomial smoothing

Sekuyisikhathi sokucabangela indlela yokupholisa i-polynomial.

1. Yiya efasiteleni lefomethi yefomethi yesitayela, njengoba usuvele wenze okungaphezu kwesisodwa. Ku-block "Ukwakha umugqa womthamo" setha inkinobho ukuma "Polynomial". Ngakwesokudla sale nto yinsimu "Isiqu". Uma ukhetha "Polynomial" isebenza. Lapha ungacacisa noma yiliphi inani lamandla kusuka 2 (isethwe ngokuzenzakalelayo) kuya 6. Le nkomba inquma inombolo yama-maxima kanye ne-minima yomsebenzi. Uma ufaka i-polynomial yesibili, izinga elilodwa kuphela elichazwe, futhi uma kufakwe i-polynomial yesithupha, ama-maximum angama-5 angachazwa. Ukuqala, sishiya izilungiselelo ezizenzakalelayo, okungukuthi, sicacisa i-degree yesibili. Izilungiselelo ezisele zihlala zifana nalokho esizibeka ezindleleni zangaphambilini. Sicindezela inkinobho "Vala".



2. Umzila wethrendidi usebenzisa le ndlela yakhiwe. Njengoba ubona, ijika kakhulu nakakhulu uma usebenzisa ukulinganisa kokucacisa. Izinga lokuzethemba liphakeme kunezinye izindlela ezisetshenziswe ngaphambilini, futhi 0,9724.

   

   Le ndlela ingasetshenziswa ngokuphumelelayo kakhulu uma idatha ishintsha njalo. Umsebenzi ochaza lolu hlobo lokuphunga kubonakala kanje:

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

   Esimweni sethu, ifomula ithatha ifomu elilandelayo:

   y = 0.0015 * x ^ 2-1.7202 * x + 507.01

3. Manje ake sishintshe izinga lezipolynomials ukubona ukuthi umphumela uzohluka yini. Sibuyela efasiteleni lesifomethi. Uhlobo lokulinganisa lusele polynomial, kodwa phambi kwalo efasiteleni lesilinganiso sabeka inani eliphezulu kakhulu - 6.



4. Njengoba ungabona, emva kwalokhu, umzila wethu womthamo uthathe isimo sekhasi eliveziwe, lapho inani eliphezulu liyizinyanga eziyisithupha. Izinga lokuzethemba landa kakhulu, okwenzayo 0,9844.

Ifomula echaza lolu hlobo lokuphunga, lithatha ifomu elilandelayo:

y = 8E-08x ^ 6-0,0003x ^ 5 + 0.3725x ^ 4-269.33x ^ 3 + 109525x ^ 2-2E + 07x + 2E + 09

Indlela yesi-5: Amandla agabisayo

Ekuphetheni, cabanga ngendlela yokulinganisa amandla ku-Excel.

1. Hambisa ewindini "I-Trend Line Format". Setha inkinobho yokubukeka eshisayo endaweni "Amandla". Kubonisa i-equation kanye nezinga lokuzethemba, njengoba kunjalo, shiya. Sicindezela inkinobho "Vala".



2. Uhlelo ludala umugqa wendabuko. Njengoba ungabona, kithi, umzila onobhontshisi obuncane. Izinga lokuzethemba liyi 0,9618okuyinto ephezulu kakhulu. Kuzo zonke izindlela ezichazwe ngenhla, izinga lokuzethemba liphakeme kuphela uma usebenzisa indlela ye-polynomial.

Le ndlela isebenziseka ngokuphumelelayo ezimweni zenguquko ezinzulu kumsebenzi wokusebenza. Kubalulekile ukuthi uqaphele ukuthi le nketho isebenza kuphela uma umsebenzi nokuphikisana kungakwamukeli amanani amabi noma amanani.

Ifomula jikelele echaza le ndlela kanje:

y = bx ^ n

Esimweni sethu, kubonakala kanje:

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

Njengoba ungabona, uma usebenzisa idatha ethize esetshenziselwa isibonelo, indlela yokulinganisa ye-polynomial ne-polynomial ngezinga lesithupha (0,9844), izinga eliphansi kakhulu lokuzethemba endleleni ehambisanayo (0,9418). Kodwa lokhu akusho neze ukuthi ukuthambekela okufanayo kuyoba lapho usebenzisa ezinye izibonelo. Cha, izinga lokusebenza kahle lezindlela ezingenhla lingase lihluke kakhulu, kuye ngokuthi uhlobo oluthile lomsebenzi okuzokwakhiwa kuwo umzila wendabuko. Ngakho-ke, uma indlela ekhethiwe iphumelela kakhulu kulo msebenzi, lokhu akusho nakancane ukuthi kuzoba ngcono nakwezinye izimo.

Uma ungeke ukwazi ukubona ngokushesha, ngokusekelwe ezincomo ezingenhla, yikuphi uhlobo lokulinganisa oluzofanelana ngqo kulokho, ngakho-ke kuwukuhlakanipha ukuzama zonke izindlela. Ngemva kokwakha umugqa wendabuko futhi ubuka izinga lokuzethemba, ungakhetha okukhethwa kukho.