Hanyar ƙayyade a Microsoft Excel

Daga cikin hanyoyi daban-daban na yin la'akari ba zai yiwu bane don gane bambancin. Tare da taimakonsa, zaka iya yin kimanin lissafi kuma lissafta alamun tsarawa ta hanyar maye gurbin abubuwan asali tare da mafi sauki. A cikin Excel, akwai yiwuwar yin amfani da wannan hanyar don tsarawa da bincike. Bari mu dubi yadda za a iya amfani da wannan hanya ga shirin da aka kayyade tare da kayan aikin ginawa.

Ƙaddamar da kimantawa

Sunan wannan hanya ta fito ne daga kalmar Latin proxima - "mafi kusa". Yana da kimantawa ta hanyar sauƙaƙe da kuma fadada alamun da aka sani, gina su a cikin layi kuma shine tushensa. Amma wannan hanya ba za a iya amfani dashi ba kawai don yin bayani ba, amma har ma don bincika sakamakon da ake ciki. Bayan haka, kimantawa shine, a gaskiya, sauƙaƙe na asali na asali, kuma sauƙin sauƙaƙe ya ​​fi sauki don bincika.

Babban kayan aiki wanda smoothing yake aiwatarwa a Excel shine gina wani layi. Ƙarin ƙasa ita ce, bisa ga alamun da aka riga ya samo, an tsara jadawalin aikin don kwanakin nan gaba. Babban manufar tarin layi, wanda ba shi da wuyar tsammani, yana yin kaddamarwa ko kuma gano ainihin al'ada.

Amma za'a iya gina ta ta amfani da ɗaya daga cikin nau'in nau'i biyar:

• Layin;
• Musamman;
• Logarithmic;
• Tsarin mulki;
• Ikon.

Yi la'akari da kowanne ɗayan zaɓuɓɓuka a ƙarin daki-daki daban.

Darasi: Yadda za a gina salo a cikin Excel

Hanyar 1: Linear Smoothing

Da farko dai, bari muyi la'akari da kusanci mafi sauki, wato, yin amfani da aikin linzamin kwamfuta. Za mu ci gaba da kasancewa a kan dalla-dalla, tun da yake mun bayyana ra'ayoyin da ke da alaƙa ga sauran hanyoyi, wato, yin mãkirci da wasu ƙananan hanyoyi da ba za mu kasance a lokacin da za mu bincika zaɓuɓɓukan da za a bi ba.

Da farko, zamu gina hoto a kan abin da za mu gudanar da hanya mai tsabta. Don gina hoto, zamu ɗauki tebur wanda farashin ɗayan kayan aiki da aka samar ta hanyar sana'a da kuma amfaninsu daidai a cikin lokacin da aka ba da aka nuna kowane wata. Ayyukan aikin da muka gina za su nuna daidai da yawan karuwa a riba a kan rage yawan farashi.

1. Don gina hoto, da farko, zaɓi ginshiƙai "Kayan kuɗin samarwa" kuma "Riba". Bayan haka zuwa shafin "Saka". Kusa a kan rubutun a cikin toshe na "Shirye-shiryen" kayan aiki danna maballin "Hotuna". A cikin jerin da ya buɗe, zaɓi sunan "Dot tare da labule mai laushi da alamu". Wannan nau'in sigogi ne wanda ya fi dacewa don aiki tare da layi, don haka, don amfani da hanyar kimantawa a Excel.



2. Tsarin ginin.



3. Don ƙara jerin layi, zaɓi shi ta danna maɓallin linzamin linzamin dama. Yanayin mahallin yana bayyana. Zaɓi abu a ciki "Add trend line ...".

   

   Akwai wani zaɓi don ƙara shi. A cikin ƙarin ƙungiyar tabs a rubutun "Yin aiki tare da Sharuɗan" motsa zuwa shafin "Layout". Kusa a cikin akwatin kayan aiki "Analysis" danna maballin "Layin layi". Jerin yana buɗewa. Tun da yake muna bukatar mu yi amfani da kimanin linzamin linzamin, daga matsayi da muka zaɓa "Kimanin linzami".



4. Idan, duk da haka, ka zaba zaɓin farko na ayyuka tare da Bugu da ƙari ta hanyar menu mahallin, to, tsarin zaɓin zai bude.

   A cikin fasalin fasali "Gina wani layi mai layi (kimantawa da smoothing)" saita canzawa zuwa matsayi "Linear".
   Idan ana so, zaka iya saita kaska kusa da matsayi "Nuna matsala akan tashar". Bayan haka, zane zai nuna aikin daidaitawa na smoothing.

   Har ila yau, a yanayinmu, don kwatanta zaɓuɓɓukan dacewa daban, yana da muhimmanci a duba akwatin "Sanya jimlar darajar kimanin abin dogara (R ^ 2)". Wannan alamar zai iya bambanta daga 0 har zuwa 1. Mafi girma shi ne, mafi dacewa (mafi abin dogara). An yi imani cewa idan darajar wannan alamar 0,85 kuma za a iya la'akari da smoothing mafi girma, kuma idan adadi ya zama ƙasa, to - babu.

   Bayan da ka sami duk saitunan da ke sama. Muna danna maɓallin "Kusa"sanya a kasan taga.



5. Kamar yadda kake gani, an tsara zanen layi a kan zane. A cikin yanayin zangon haɗin linzamin kwamfuta, ana nuna shi ta hanyar launi na baki. Irin wannan kayan ƙanshi za a iya amfani da shi a cikin mafi sauƙi lokuta, lokacin da bayanai ke canzawa da sauri kuma dogara ga aikin darajar a kan gardama ya bayyane.

Abin da ake yin amfani da ita, wanda aka yi amfani dashi a wannan yanayin, an bayyana shi ta hanyar daftarin:

y = arba + b

A cikin yanayinmu na musamman, wannan tsari yana ɗaukar nauyin:

y = -0.1156x + 72.255

Girman daidaito na kimantawa daidai yake da mu 0,9418, wanda shine sakamakon da ya dace, yana kwatanta smoothing kamar abin dogara.

Hanyar 2: Ƙaddara Tsarin

Yanzu bari muyi la'akari da mahimmanci na kimanin kusan a Excel.

1. Domin canza yanayin layi, zaɓi shi ta danna maɓallin linzamin maɓallin dama kuma a cikin menu mai saukarwa zaɓi abu "Yanayin layi na yau da kullum ...".



2. Bayan wannan, tsarin da aka riga ya saba da mu an kaddamar. A cikin asalin don zaɓar nau'in kimanin, saita maɓallin zuwa "Exponential". Sauran saitunan sun kasance daidai da na farko. Danna maballin "Kusa".



3. Bayan wannan, za a yi la'akari da layi. Kamar yadda kake gani, lokacin yin amfani da wannan hanya, yana da siffar mai sauƙi. Matsayin amincewa shine 0,9592, wanda shine mafi girma fiye da lokacin yin amfani da kwatancin linzamin. Ana amfani da hanya mafi mahimmanci lokacin da dabi'u suka fara sauyawa sannan kuma su ɗauki nau'i mai kyau.

Babban ra'ayi na smoothing aiki shine kamar haka:

y = zama ^ x

inda e - wannan shi ne tushe na halitta logarithm.

A cikin yanayinmu na musamman, wannan tsari ya ɗauki nau'i na gaba:

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

Hanyar 3: Log Smoothing

Yanzu shine lokacin da za a yi la'akari da hanyar da aka kwatanta da logarithmic.

1. Kamar yadda a baya, ta hanyar mahallin menu, kaddamar da maɓallin tsarin layi. Saita canza zuwa matsayi "Logarithmic" kuma danna maballin "Kusa".



2. Akwai tsarin tsarin gine-ginen da ke tattare da kimanin kimanin logarithmic. Kamar yadda a cikin akwati na baya, ana amfani da wannan zaɓin lokacin da bayanai suka fara sauyawa, sa'an nan kuma su ɗauki idanu daidai. Kamar yadda kake gani, matakin amincewa shine 0.946. Wannan ya fi yadda za a yi amfani da hanyar haɗin linzamin, amma ƙananan fiye da ingancin layi tare da smoothing.

Gaba ɗaya, ƙudirin smoothing yana kama da wannan:

y = a * ln (x) + b

inda ln shi ne girma na halitta logarithm. Saboda haka sunan hanyar.

A cikin yanayinmu, wannan tsari yana ɗaukar nauyin:

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

Hanyar 4: Harshen gyare-gyare na polynomial

Lokaci ya yi da za a yi la'akari da hanyar da ake amfani da shi na polynomial smoothing.

1. Jeka zuwa tsarin zane, kamar yadda ka riga ya aikata fiye da sau ɗaya. A cikin toshe "Gina hanyar layi" saita canzawa zuwa matsayi "Polynomial". A dama na wannan abu abu ne na filin "Degree". Lokacin zaɓar "Polynomial" ya zama aiki. A nan za ka iya tantance kowane darajar wutar lantarki daga 2 (saita ta tsoho) zuwa 6. Wannan alamar yana ƙayyade yawan maxima da minima na aikin. Lokacin da kake shigar da digirin digiri na biyu, an kwatanta iyakar ɗaya kawai, kuma lokacin da aka shigar dashi na digiri na shida, har zuwa iyakar biyar za'a iya bayyana. Da farko, mun bar saitunan tsoho, wato, mun saka digiri na biyu. Sauran sauran saituna sun kasance daidai kamar yadda muka sanya su a cikin hanyoyin da suka gabata. Muna danna maɓallin "Kusa".



2. Hanyar layi ta amfani da wannan hanyar an gina. Kamar yadda kake gani, ya fi maida hankali fiye da lokacin yin amfani da kimanin kimanin. Ƙarin amincewa ya fi kowane irin hanyoyin da aka saba amfani dashi, kuma yana da 0,9724.

   

   Wannan hanyar za a iya amfani da shi sosai idan an canza bayanan. Ayyukan da ke kwatanta wannan nau'in smoothing kamar wannan:

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

   A cikin yanayinmu, wannan tsari ya ɗauki nau'i na gaba:

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

3. Yanzu bari mu canza matsayi na polynomials don ganin idan sakamakon zai zama daban. Muna komawa zuwa tsari na tsari. Misalin kimantawa yana hagu na polynomial, amma a gabansa a cikin digiri mataki mun saita matsakaicin iyaka - 6.



4. Kamar yadda ka gani, bayan haka, layinmu na yau da kullum ya ɗauki nau'i na ƙirar da aka bayyana, wanda yawan adadin ya kasance shida. Ƙarin amincewa ya ƙara ƙãra, yin 0,9844.

Ma'anar da ta bayyana irin wannan kayan ƙanshi, ya ɗauki nau'i mai biyowa:

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

Hanyar 5: Ginin Smoothing

A ƙarshe, la'akari da hanyar ikon kusa a Excel.

1. Matsar zuwa taga "Tsarin Layin Layi". Saita smoothing view canzawa zuwa matsayi "Ikon". Nuna matakan da matakin amincewa, kamar kullum, bar shi. Muna danna maɓallin "Kusa".



2. Shirin yana samar da layi. Kamar yadda ka gani, a cikin yanayinmu, yana da layi tare da ƙaramin lanƙwasawa. Matsayin amincewa shine 0,9618wanda shine babban adadi. Daga duk hanyoyin da aka bayyana a sama, matakin ƙwaƙwalwar ya fi girma ne kawai a lokacin amfani da hanyar bin hanyar fasaha.

Ana amfani da wannan hanya ta hanyar amfani da matsaloli mai mahimmanci a bayanan aikin. Yana da mahimmanci a lura cewa wannan zaɓin ya dace ne kawai idan aikin da jayayya ba su yarda da mummunan ko ƙira ba.

Maganar da aka kwatanta wannan hanya ita ce:

y = bx ^ n

A cikin yanayinmu na musamman, yana kama da wannan:

y = 6E + 18x ^ (- 6,512)

Kamar yadda muka gani, lokacin amfani da bayanan da muka yi amfani dashi don misalin, hanyar da za a iya gwadawa ta polynomial tare da manzo a mataki na shida (0,9844), mafi ƙasƙanci na amincewa cikin hanya na layi (0,9418). Amma wannan ba yana nufi a kowane lokaci cewa irin wannan hali zai kasance a lokacin amfani da wasu misalai. A'a, dacewar matakan da ke sama za su iya bambanta da muhimmanci, dangane da nau'in aikin da za a gina. Sabili da haka, idan hanyar da aka zaɓa ya fi tasiri ga wannan aikin, wannan ba yana nufin a kullun cewa zai zama mafi kyau a wani yanayi.

Idan ba za ka iya yanke shawara ba, bisa ga shawarwarin da ke sama, wane nau'in kimantawa ya dace a cikin yanayinka, to, yana da mahimmanci don gwada dukkan hanyoyin. Bayan kafa wata layi da kuma duba matakin amincewa, za ka iya zaɓar zaɓi mafi kyau.