Excel-da qisqacha chiziqli regressiya modelini qanday yaratish kerak

Excel statistika bilan yaxshi ish qiladi yoki ular uchinchi tomon o'zlarining qo'shimchalarini bepul yozishlari mumkin. Ushbu maqolada siz Excel va Linear Regression Analysis-dan foydalanib trendni qanday loyihalashni o'rganasiz.

Qo'llanma

Qo'llanma
Excel-da yangi ishchi daftarni oching va 3 ish varag'ini yarating: Ma'lumotlar, jadval va saqlash. Ishchi daftarni chiziqli regressiya - Qisqa dars yoki shunga o'xshash narsalarni mantiqiy fayl papkasida saqlang.
Afzalliklarni o‘rnatish: Excel menyusida "Afzalliklar" ni oching. Tavsiya etilgan sozlamalar: R1C1-ni "O'chirish" ga sozlang va eng so'nggi 10 ta hujjatni ko'rsating; Tartibga solish - Avtomatik ravishda Konvertatsiya qilish Sistemasi tizimidan tashqari barcha top variantlarni tekshiring. O'nli kasrlar sonini ko'rsatish = bo'sh (afzal butun sonlar uchun), sanalarni aks ettirish va 21-asrning 30-sonini belgilash; Ko'rish - Formula paneli va Holat panelini ko'rsating, izohlarni va barcha Ob'ektlarni belgilang, avtomatik yoki belgilangan pastdagi katakchalar va panjara chiziqlarini ko'rsating; Grafik - hover-da grafik nomlari va ma'lumotlar markerlarini ko'rsatish. Dam olishni hozircha tekshirilmagan holda qoldiring; Hisoblash - Avtomatik ravishda va saqlashdan oldin hisoblang, maksimal o'zgarish .000.000.000.000,01 / vergul bilan, vergul qo'yish juda ko'p amalga oshirildi va tashqi ulanish qiymatlarini tejang va 1904 tizimidan foydalaning; Tekshirishda xato - barchasini tekshiring; Saqlash - oldindan ko'rish rasmini yangi fayllar bilan saqlang va 5 daqiqadan so'ng Autorecover-ni saqlang; Tasma - guruh sarlavhalarini va dasturchini berkitishdan tashqari hamma tekshirildi.
Butun varaqni belgilash uchun chap va ishchi varaqning yuqori burchagidagi 1 va A oralig'ini tanlang va Format Cells Alignment Horizontal Center va Shrift hajmi 9 yoki 10 yoki siz ko'rishingiz mumkin bo'lgan hamma narsani bajaring.
1-qatorga ustun sarlavhalarini kiriting: B1: X; C1: Y; D1: X. E1: Y F1: Y. 2-qatorning ustun sarlavhalarini kiriting: A2: Student; B2 SAT; C2: GPA; D2: VARIANCE; E2: VARIANCE; F2: TREND, G2: o'zgartirish.
A3 uyalar qatoriga o'tish: A22, 1 raqamini kiriting va to'ldiring. To'ldirish seriyasining ustunlarini tahrirlash Linear Step Value 1 OK, 20 ta talaba yangi kollejga o'qishga kirishda SAT imtihonining mustaqil o'zgaruvchisiga qarab o'zlarining qaramlik darajasi (GPA) tendentsiyasini bilishni istaydilar, ya'ni ularning baholari. ko'tarilish, tushish yoki taxminan bir xil bo'lib qolishi mumkinmi?
Bu savolga javob berish uchun ma'lumotlarni kiriting. 22-rasmda ko'rsatilgandek .
A23 katakchani tanlang va MEAN kiriting. B / 23 "= AVERAGE (B3: B22)" katakchasiga w / o qo'shtirnoq kiriting va uni C23 katakchaga joylashtiring. C23 katakchani tanlang va $ C $ 23 uchun Y_Bar ismini aniqlang. B23 uyani tanlang va $ B $ 23 uyali uchun Ismni qo'shish X_Bar nomini tanlang. B23: C23 katakchalarini tanlang va qizil, quyuq rangda harflar formatini belgilang.
D3: D22 katakchasiga va D3 katakchaga faol va yuqori nurli katakchani tahrirlash uchun "= (B3-X_BAR) ^ 2" formulasini kiriting va Fill Down-ni tahrirlang. E3: E22 katakchalariga va E3 katakchasiga faol va yuqori yoritilgan katakchaga "= (C3-Y_BAR) ^ 2" formulasini kiriting va pastga to'ldiring. D3: D23 katakchani tanlang va Hujayralar sonini raqamini o'nli joylarga ko'chiring 4.
D23 katakchani tanlang va "= SUM (D3: D22) / (20-1)" formulasini kiriting va nusxa ko'chiring va E23 katakchaga joylashtiring. E3: E23 katakchalarini tanlang va hujayralar sonini o'nli joylarga formatlashni bajaring. A24 katakchani tanlang va VARIANCE kiriting va B24 katakchani tanlang va "= D23" formulasini kiriting va nusxa oling va C24 katakchasiga joylashtiring. B24: D25 katakchalarini tanlang va katakchalar sonini raqamini o'nli qilib joylashtiring 6.
A25 uyani tanlang va STD DEV kiriting. (standart og'ish uchun) va B25 katakchani tanlang va "= STDEVPA (B3: B22)" formulasini kiriting va C25 uyasiga nusxa ko'chiring va qo'ying. B25: C25 tanlangan hujayralar oralig'i uchun hujayralar sonining o'nlik joylarini 7 formatlang.
A27 katakchani tanlang va FORECAST ni kiriting va B27 katakchani tanlang va massiv formulasi bo'lgan "= STDEVPA (B3: B22)" formulasini kiriting va shu bilan qavs formulasini to'g'ri kiritish uchun CONTROL + SHIFT + ENTER tugmalarini bosing. Ya'ni, bitta talabaning GPA 1290 SAT va qolgan aholisi yoki ma'lumotlari haqidagi proektsiyasi. Hujayralar sonining o'nli joylari 9 ni formatlashingiz bilan (u keyinroq mos keladi) u GPAni 3.170409192 ga kutishi mumkin. Bunday aniq GPA zarur emasligi emas, balki bu formulani isbotlashdir va bu aniqlikni talab qiladi.
F3: F22 uyali qatoriga o'ting va massiv formulasi bo'lgan "= TREND (C3: C22, B3: B22,, TRUE)" tirnoqli / "tirnoq" formulasini kiriting, shuning uchun qatorga to'g'ri kirish uchun CONTROL + SHIFT + ENTER tugmalarini bosishingiz kerak. bu qavslar bilan formulalar. Bu talabalar populyatsiyasining GPA oldingi natijalarini hisobga olgan holda proektsiyasi.
G3: G22 katakchalariga tahrirlash va G3 bilan faol nurli katakchaga "= F3-C3" tirnoq formulasini kiriting va "Fill Down" -ni tahrirlang. Formatlash katakchalar raqami raqami Custom +0.0; -0.0; +0.0.

Tushuntirish jadvallari, diagrammalar, rasmlar

Grafik yarating (yuqoridagi dars ma'lumotlariga bog'liq). B3: C23 katakchalarini tanlang va Tasma (yoki Chizma ustasini bajaring) ga o'ting va Grafiklar, Hammasini tanlang, pastga siljiting, Belgilangan Scatter .. So'ngra kesilgan joyni tahrirlang yoki jadvalni ish jadvaliga ko'chiring. Chizma sxemasi jadvalining sarlavhasini - jadvalning ustidagi sarlavha bilan to'ldiring va "Line Regression - SATs vs GPAs" (tirnoqsiz) bilan to'ldiring. Gridlineslarni tanlang Vertikal panjara chiziqlari Belgilangan asosiy chizmalar. Eksa sarlavhalarini gorizontal o'qi sarlavhasini tanlang, sarlavha ostidagi sarlavha sarlavhasini tanlang va "SAT Score" da (qo'shtirnoqsiz) tahrirlang. Axis sarlavhalari Vertikal o'qlar sarlavhasini, gorizontal sarlavhani tanlang va "GPA" da (tirnoqsiz) tahrirlang. Konvensiya bo'yicha, mustaqil x o'zgaruvchisi pastki gorizontal o'qda va Mustaqil y o'zgaruvchisi chap vertikal o'qda davom etadi.
O'rtacha yoki bosganingizda "11-seriya 1143.75" (1143.75, 2.8) o'qiladigan O'rtacha qiymatni belgilaydigan ni belgilang. Unga bosing va bajaring menyu Format, Data Point marker Style Avtomatik o'lchov 9, so'ng Marker Fill Rangi qizil.
Y '= mX + b regressiya chizig'ini aniqlash uchun ma'lumotlar sahifasiga qayting, bu erda m = qiyalik va b = y kesishish. Chapdagi 10 dan past qiymatlari va pastki qismida 1000 atrofida qiymatlarga ega bo'lgan grafik ma'lumotlarini ko'rib chiqsak, juda oz sonli o'nlik qiyalik va 0 ga yaqin y-kesishishni kutish mumkin. Masshtablangan jadvallar ba'zan qiyalik kabi aldayotgan bo'lishi mumkin.
G1 katakchani tanlang va Y kiriting. H1 katakchani tanlang va m kiriting va H1-ni nusxa ko'chiring va C buyrug'i orqali I1-ga joylashtiring, I1-ni va v-ni tanlang. H2 katakchani tanlang va Numeratorni kiriting, I2 katakchani tanlang va Denominatorni kiriting. H3: H22 katakchani tanlang va faol ajratilgan katak sifatida H3 bilan, "= (B3-X_BAR) * (C3-Y_BAR)" formulasini kiriting. To'ldirishni pastga tahrirlash. I3: I22 va I3 katakchani tanlang, faol ajratilgan katak sifatida "= (B3-X_BAR) ^ 2" formulasini kiriting. To'ldirishni pastga tahrirlash. H va I ustunlarini tanlang va katakchalar sonini o'nli joylarga ajrating 1.
H23 katakchani tanlang va "= SUM (H3: H22)" tirnoqli formulani kiriting va Format Hujayralar chegarasi qora qalin konturini kiriting va uni I23-ga ko'chiring.
H24 katakchasini tanlang va m kiriting va Format Cells Font Color Red-ni bajaring. Uni H25 katakka ko'chiring va H25 katakchaga b kiriting. I24 katakchani tanlang va "N H / / I23" formulasini kiriting, bu qiyalik m, keyin I25 katakchani tanlang va "= Y_BAR-I24 * X_BAR" formulasini kiriting, ya'ni y-kesishish b = Y_Mean-m * X_Mean.
H25 katakchadan nusxa oling va uni I26 katakka joylashtiring va I26 Y '- mX + b ichiga kiriting. H27 katakchani tanlang va "Prognoz" ni kiriting, so'ng formulani I27 / tirnoq ichiga kiriting "= I24 * 1290 + I25". Format katakchalar sonining o'nli joylari 9 ga ega bo'lgandan so'ng, sizning javobingiz B27 da FORECAST javobiga to'g'ri kelishi kerak.
I26 nusxa ko'chirish katakchasini H29: I29 katakchaga tahrir qiling. H29 katakchaga X satr va I29 katakchaga Y satrini kiriting. H30 katakka 800 va H31 katakchaga 1600 kiriting. I 27 katakchani tanlang va formulani formulalar qatoriga ko'chiring - katakchani ko'chirmang va joylashtirmang - bu ishlamaydi. I30 katakchani tanlang va hozirgina ko'chirilgan formulani tahrirlash uchun Formula paneliga qo'ying. I31 uyasi uchun ham xuddi shunday qiling. "= I24 * H31 + I25" ni o'qish uchun I31 formulasini tahrirlang va Enter tugmachasini bosing, so'ngra "= I24 * H30 + I25" ni o'qish uchun Enter tugmachasini va I30 katakchani formulasini tahrirlang va Enter ni bosing. I30'ning natijasi 1.7786108729206 va I31 natijasi 4.05093465957812 bo'lishi kerak (men bu 4.0 GPA dan yuqori ekanligini tushunaman, ammo biz shu qadar muhim emaski, regressiya chizig'ini yaratmoqdamiz).
Grafik ishchi varag'ini faollashtiring va diagramma va menyuni bosing, Ma'lumotlar qo'shish jadvali va diapazon so'roviga javoban, ma'lumotlar varag'iga qaytib H30: I31 katakchasini tanlang. Endi meniki noto'g'ri chiqdi va men seriallarni tahrirlashim kerak. Katta emas. Ma'lumotlar markerini va formulalar satrini tanlang, "= SERIES (, 1-varaq! $ H $ 30: $ H $ 31, Varaq1! chiziqni qizil va og'irligi 0.75 pt qilib, marker bilan to'ldirish rangi qizil, keyin Marker Style Round nuqta o'lchovi 5 ni tanlang.
Eksponensial egri chiziqning xususiyatlari qanday?
Ekspansional egri P (0 | 1) nuqtadan o'tadi va X o'qiga ko'ra asemptomatikdir.
XATOLAR: Agar sizda xatolar yoki xatolar bo'lsa, varaq to'liq emas va qo'shimcha kiritish talab etiladi yoki tanqidiy o'zgaruvchilar uchun qidiruv jadvallari yoki ehtimol siz chiziq bo'ylab biron bir xatoga yo'l qo'ygansiz. Agar ko'rsatmalar bajarilgan bo'lsa va xatolar hali ham mavjud bo'lsa, avval xato qiymati chap va yuqorisida bo'lgan katakchani tanlang. Yozuvni formuladan yoki tengsiz qavsdan qidiring. Ehtimol, Aniqlangan nom noto'g'ri bo'lishi mumkin - ular aniqlangandek formulalarga kiritilishi kerak. Tekshirish uchun Ismni belgilashni bajaring. Agar sizda DIV / 0! Bo'lsa, men buni bilmayman, ehtimol biron bir qiymat bilan to'ldirilmagan o'zgaruvchini qidirib toping. Qanday bo'lmasin, siz xato bilan katakchani tanlang va ushbu odatiy xatolarning barchasini tekshirib ko'rgandan so'ng, Trec Precedents va / yoki Trace Error Auditing asboblarini tekshiring. Agar chap tomondagi eng yuqori xatolarni tuzatish ish xatolaringizdagi qolgan xatolaringizni tuzatmasa, siz buni pastdan yuqoridan yuqoriga, keyin chapga yo'naltirishingiz kerak; bu barcha xatolarni tuzatishning sekin, ammo ishonchli usuli.
Shuningdek, sxemadagi ma'lumotlardagi xatolar, nolga o'xshash bo'lishi mumkin. Bu hatto maqbul yoki maqbul bo'lishi mumkin. Ammo, agar chiziqlar (yoki egri chiziqlar) 0 ga qaytayotgan bo'lsa, bu ma'lumotlardagi mantiqiy nuqsonni ko'rsatishi mumkin - yoki juda kichik qiymatlar va keyin gorizontal va vertikal o'qlarni tekshirish va ularni o'zgartirish orqali sxemani o'chirish kerak bo'ladi. muammoga nol kiring. Serial syujetidagi ma'lumotlar markerini ustiga bosing yoki ustiga bosing, so'ngra qiymat uchun tegishli ustundan qidiruvni amalga oshiring va uning avvalgilarini aniqlang.
benumesasports.com © 2020