Midna waxaad akhrinaysaa tan sababtoo ah waxaad raadisay sida loogu daro khadadka dib-u-celinta tooska ah ee qaabka kala firdhisan ee Excel^{(Excel scatter plot )} ama waxaad aragtay cinwaanka oo aad fekertay, " Erayadani ma xitaa ^(Are)Ingiriisi^(English) ?!" Waanu kaa caawin doonaa labadaba.

Waa maxay Regression Linear?^{(What Is Linear Regression?)}

Haddii aad ogtahay waxa uu yahay khadadka dib u noqoshada toosan, horay u dhaaf. Hagaag, hadda markay neerfayaashu meesha ka baxeen, waxaan sharxi doonaa dib u noqoshada toosan. Linear macnaheedu waa xariiq. Waad garanaysay taas. Dib-u- noqosho^(Regression) , xagga xisaabta, macneheedu waa in la ogaado inta shay uu ku xiran yahay shay kale. Labadan shay waxaan ugu yeeri doonaa  X iyo Y.

Aan tusaale u soo qaadano la socoshada qiimaha hal saami ee suuqa saamiyada sanadihii la soo dhaafay. X waxay noqon doontaa wakhti sannado ah Y waxay noqon doontaa qiimaha dollarka. 

Waxaan ognahay in qiimaha kaydka lagu beddelo waqti dhaaf, iyo waxyaabo kale. Ma xakameyn karno waxyaalahaas kale, laakiin waan xakameyn karnaa marka aan iibineyno saamiyada, markaa waxaan xakameyneynaa doorsoomiyaha waqtiga. Laakiin intee ayay ku xidhan tahay qiimaha kaydka ee wakhtiga la dhaafay?

Haddii aan ku iibsanay kayd $1 oo hal sano gudaheed qiimihiisu kor u kacday $100, taas macnaheedu miyay sanad walba qiimuhu kor u kacayaa $100 kale? Taas macnaheedu ma 25 sano gudahood waxaa lagu qiimeeyaa $2500? Ma naqaano.

Waxaan ku ogaanay markaan eegno inta ay le'eg tahay kaydka soo galay dhowr sano. Taasi waa wax fudud^(simple) sababtoo ah waxaan kaliya cabbireynaa inta aan bedeleyno hal shay ama hal doorsoome. Kadibna waxaan ku dhejineynaa cabbirada garaaf ama goob^(plot) . Dhibcuhu waxay noqon karaan kuwo meel walba yaal ama kala firdhisan^(scattered) . 

Miyaynu ka sawiri karnaa xariiq dhibcaha muujin kara isbeddel? Aynu taas ugu yeerno khad toosan^(trendline) . Haa, xaqiiqdii waan isku dayi karnaa. Xariiqdaasi waa khad toosan oo dib u socod ah oo fudud iyada oo loo marayo goob kala firdhisan^{(simple linear regression trendline through a scatter plot)} . Hadda waxaan ognahay in erayadaas ay dhab ahaantii yihiin Ingiriisi iyo waxa ay ula jeedaan. Aan ku abuurno mid Excel .

Sida Loo Sameeyo Shirqoolkii Faafinta Excel oo leh Khadka Dib-u-celinta Toosan^{(How To Create An Excel Scatter Plot With Linear Regression Trendline)}

Aynu ka soo qaadno inaadan weli baran dhammaan ku saabsan Excel^{(learned all about Excel)} . Talaabada ugu horeysa waa in la abuuro goob kala firdhisan. Markaa waxaan samayn karnaa khariidadda isbeddelka. Markaa waxaan samayn karnaa waxyaabo nadiif ah oo la socda khadadka isbeddelka oo aan aragno waxay ka dhigan tahay.

• Ku samee 2^{(Create 2)} tiir oo xog ah gudaha Excel . Tusaalahayagu wuxuu lahaan doonaa Waqti^(Time) sannado ah iyo Qiimaha saamiyada^{(Stock Value)} ee dollarka. Waxaad koobi ka dhigi kartaa oo ku dhejin kartaa xogta Excel^{(copy and paste the data into Excel)} si aad ula ciyaarto. Kadibna, gudaha Excel , dooro labada tiir ee xogta adiga oo dooranaya oo haysta lambarka bidix ee sare oo u jiid hoos lambarka ugu hooseeya ee tiirka midig.

• Dooro Gelida^{(Insert )} aaladda ugu weyn.
• Raadi astaanta garaafka oo dhibco keliya ay ku yaalliin. Dooro falaarta hoose ee ku xigta.
• Dooro garaafka kala firdhisan ee ugu horreeya oo leh dhibco kaliya oo aan lahayn khad.

• Marka garaafku la dhiso oo aad habayso jaantuska Excel^{(customized the Excel chart)} si aad u eegto qaabka aad rabto, midig-guji hal dhibic xog ah. Tani waxay noqon kartaa wax yar oo dhib badan, markaa sii wad inaad isku daydo haddii aadan ku helin isku daygii ugu horreeyay. Markaad sameyso, menu-hoosaad ayaa furmi doona.
• Dooro Kudar Trendline^{(Add Trendline)} .

• Liiska Format Trendline waxa uu ka furmi doonaa dhanka midig. Xulashada khadka toosan^{(Linear )} mar hore ayaa la dooran doonaa. Taas iska daa. Dareen^(Feel) xor u ah inaad la shaqeyso qaabaynta Excel^{(Excel formatting)} si aad khadka uga dhigto mid wanaagsan. Hadda waxaad haysataa khad toosan oo dib u noqosho ah kaas oo ku tusaya kobaca guud ee qiimaha saamiyada in ka badan 20 sano.

• Ma doonaysaa^(Want) inaad ogaato waxa ay noqon doonto 2030? Waxaad ku heli kartaa fikrad qallafsan adigoo ku daraya 10 xilli, ama sanado, qaabka isbeddelka ee saadaasha Forward^{(Forecast Forward)} -ka. Waxay tusi doontaa in lagu qiimeeyo meel ku dhow $2500 dhibicda goobada cagaaran.

• Haddii aad rabto in jaantuskaagu kaa dhigo mid caqli badan, hoos ugu dhaadhac liiska Format Trendline oo hubi isla'egta Muujinta jaantuska^{(Display Equation on chart)} iyo Muuji qiimaha R-squared ee shaxanka^{(Display R-squared value on chart)} . Waxaad arki doontaa wax la mid ah waxa ku jira sanduuqa cas ee hoose oo ka muuqda shaxdaaga.

Waa maxay macnaha isla'egta iyo R-squared Value?^{(What Does The Equation and R-squared Value Mean?)}

Kuwani waa ku anfacaya in la haysto. Qiimaha R-squared wuxuu kuu sheegayaa sida uu u fiican yahay habka isbeddelku u habboon yahay. Inkasta oo qiimaha R-squared ee ka sarreeya 0.8 uu ku habboon yahay, 0.69 maaha mid xun. Ka fakar adiga oo 69% ku kalsoon in khadkani uu ku siin doono aragti wanaagsan sida kaydkani u janjeero inuu wax u qabto.

Isla'egta ayaa kuu sahlaysa inaad samayso xisaabin degdeg ah si aad u ogaatid waxa uu yahay qiimaha saamiga ee khadka isbeddelku waa wakhti kasta. Xataa ka hor inta uusan line-ku bilaabmin iyo ka dib marka uu dhammaado. Maxaa laga yaabaa in saamiyadu ay qiimo ku yeeshaan 2030? Aynu ku xidhno isla'egta.

Y = 80.468 x X – 160136 - halka X uu yahay 2030

Y = 80.468 x 2030 – 160136

Y = 163,350.04 - 160136

Y = 3,214.04

Haa, haddii isbeddelku sii socdo, qiimaha saamigu wuxuu leeyahay fursad wanaagsan oo ah inuu ku kacayo $3214.04 sanadka 2030.

Maxaad ku samayn doontaa Khadka Dib-u-celinta Toosan?^{(What Will You Do With A Linear Regression Trendline?)}

Waxaan ku tusnay tusaale ah sida dib-u-celinta tooska ah ee Excel ay kaaga caawin karto inaad gaarto go'aan maaliyadeed. Laakiin siyaabo kale ayaad u isticmaali kartaa? Ma^(Are) la imanaysaa fikrado? Aan ogaano.

Add a Linear Regression Trendline to an Excel Scatter Plot

You’re either reading this beсause you searched for how to add a linear rеgression trendline to an Excel scatter plot or you saw the title and thought, “Are these words even English?!” We’ll help you with both.

What Is Linear Regression?

If you know what a linear regression trendline is, skip ahead. Ok, now that the nerds are gone we’ll explain linear regression. Linear means in a line. You knew that. Regression, in math, means figuring out how much one thing depends on another thing. We’ll call these two things X and Y. 

Let’s use the example of tracking the value of a single share in the stock market over the years. X will be time in years and Y will be the value in dollars. 

We know that the value of a stock is changed by time passing, among other things. We can’t control those other things, but we can control when we sell the stock, so we control the time variable. But how dependent is the value of a stock on time passed?

If we bought a stock for $1 and in one year its value went up to $100, does that mean every year the value will go up another $100? Does that mean in 25 years it will be valued at $2500? We don’t know.

We figure it out by looking at how much the stock earned over several years. That’s fairly simple because we’re only measuring how much we change one thing or one variable. Then we put those measurements on a graph or plot. The dots could be all over the place or scattered. 

Could we draw a line through the dots that would show a trend? Let’s call that a trendline. Yes, we can certainly try. That line is a simple linear regression trendline through a scatter plot. Now we know those words are actually English and what they mean. Let’s create one in Excel.

How To Create An Excel Scatter Plot With Linear Regression Trendline

Let’s assume you haven’t learned all about Excel yet. The first step is to create a scatter plot. Then we can create the trendline. Then we can do some neat things with the trendline and see what it means.

• Create 2 columns of data in Excel. Our example will have Time in years and Stock Value in dollars. You can copy and paste the data into Excel so you can play along. Then, in Excel, select both columns of data by selecting and holding on the top-left number and dragging down to the bottom-most number in the right column.

• Select Insert in the main toolbar.
• Look for the icon of a graph with just dots on it. Select the down arrow next to it.
• Select the first scatter graph with just dots and no lines.

• Once the graph is built and you’ve customized the Excel chart to look the way you want it, right-click on a single data point. This can be a little tricky, so keep trying if you don’t get it on the first try. When you do, a sub-menu will open.
• Select Add Trendline.

• The Format Trendline menu will open on the right. The Linear trendline option will already be selected. Leave that as it is. Feel free to work with the Excel formatting to make the line look nice. Now you have a linear regression trendline that shows you the general growth of the stock value over 20 years.

• Want to know what it will be worth in 2030? You can get a rough idea by adding 10 periods, or years, to the trendline in the Forecast Forward field. It will show that it’s valued at somewhere around $2500 by the dot in the green circle.

• If you want your chart to make you look smart, scroll down in the Format Trendline menu and check Display Equation on chart and Display R-squared value on chart. You’ll see something like what’s in the red box below appear on your chart.

What Does The Equation and R-squared Value Mean?

These are handy to have. The R-squared value tells you just how good the trendline fit is. Although an R-squared value above 0.8 is ideal, 0.69 isn’t bad though. Think of it as you being 69% confident that this line will give you a good insight into how this stock tends to perform.

The equation makes it easier for you to do quick calculations to figure out what the stock value on the trendline is at any point in time. Even before the line begins and after it ends. What might the stock be worth in 2030? Let’s plug it into the equation.

Y = 80.468 x X – 160136 – where X is 2030

Y = 80.468 x 2030 – 160136

Y = 163,350.04 – 160136

Y = 3,214.04

Yes, if the trend holds, the value of the stock has a decent chance of being worth $3214.04 in 2030.

What Will You Do With A Linear Regression Trendline?

We’ve shown you an example of how a linear regression trendline in Excel might help you make a financial decision. But what other ways could you use it? Are you coming up with ideas? Let us know.

Related posts

• Sida loo sameeyo Histogram gudaha Excel

• Sida loo xafido erayga sirta ah ee Excel File

• Sida Loo Sameeyo Jadwalka socodka gudaha Word iyo Excel

• Sida Loo Sameeyo Calaamadaha Erayga laga bilaabo xaashida faafinta ee Excel

• Sida loo duubo Macro gudaha Excel

• Casharrada Aasaasiga ah ee Microsoft Excel - Barashada Sida loo Isticmaalo Excel

• 40 ka ugu wanaagsan ee Keyboard-ka Microsoft Excel

• Sida loo shaandheeyo xogta gudaha Excel

• Sida loo abuuro Gantt Charts gudaha Microsoft Excel

• Sida Loo Abuuro Liiska Qaybinta Muuqaalka

• Sida Taariikhaha Looga Jaro Excel

• Sida loo sameeyo garaafka fudud ama jaantuska gudaha Excel

• Sida loo barbar dhigo labada faylal ee Excel oo loo muujiyo faraqa u dhexeeya

• Sida loo qariyo xaashiyaha, unugyada, tiirarka, iyo qaaciidooyinka gudaha Excel

• Adeegso Excel Mobile's cusub ee shaqada "Ka Geli Xogta Sawirka".

• Balacaha Saarka AutoFit iyo Dhismayaasha Saf ee Excel

• Sida Looga Daro Baararka Cilada ee Excel

• Sida loo xisaabiyo kala duwanaanshaha Excel

• Sida loola socdo isbedelada Excel

• Jadwalka xogtaada Excel