خانه › توابع › آماری › =LINEST()
آماری همه نسخه‌ها ◉ 34 بازدید

=LINEST()

تابع LINEST با استفاده از روش «حداقل مربعات» خطی را محاسبه می‌کند که بهترین برازش را با داده‌های شما دارد و سپس آرایه‌ای را برمی‌گرداند که خط را توصیف می‌کند. می‌توانید آن را با توابع دیگر ترکیب کنید تا آمار انواع مدل‌های خطی دیگر شامل چندجمله‌ای، لگاریتمی، نمایی و توانی را نیز محاسبه کنید. چون این تابع آرایه‌ای از مقادیر برمی‌گرداند، باید به‌صورت فرمول آرایه‌ای وارد شود. معادله خط به‌صورت y = mx + b یا y = m1x1 + m2x2 + ... + b در صورت وجود چند محدوده x است، که در آن y وابسته به xهاست و mها ضرایب هر x و b مقدار ثابت است. آرایه برگشتی به‌صورت {mn,mn-1,...,m1,b} است و در صورت درخواست، آمار رگرسیون اضافی نیز برمی‌گردد.

قابل استفاده در: Excel for Microsoft 365Excel for Microsoft 365 for MacExcel 2024Excel 2024 for MacExcel 2021Excel 2021 for MacExcel 2019Excel 2016
=LINEST(known_y's, [known_x's], [const], [stats])
برای توضیح هر ورودی، نشانگر را روی نام آن در جدول نگه دارید — یا بخش ورودی‌ها را ببینید.
نوع خروجیآرایه
محبوبیت20 / 100
Volatileخیر
Dynamic Arrayخیر
دانلود فایل نمونه اکسل (.xlsx) ▦ امتحان در Playground فایل اختصاصی این تابع — ایمن و کم‌حجم · شامل داده نمونه + فرمول فعال
اشتراک‌گذاری:

⬇ دانلود فایل نمونه اکسل

یک فایل .xlsx اختصاصی برای =LINEST() با داده‌های نمونه فارسی و فرمول فعال — مستقیم در اکسل باز کنید و تمرین کنید.

  • شیت «نمونه» — جدول داده + فرمول تابع در سلول مشخص
  • شیت «راهنما» — سینتکس، توضیح ورودی‌ها و نکات همین صفحه
  • تولید آنی و ایمن (توکن اعتبارسنجی + محدودیت دانلود)
دانلود LINEST.xlsx حجم ~ 8–12 KB · بدون ماکرو

⇪ اشتراک‌گذاری

این تابع را به دوستان بفرستید

کوییز این تابع

سوالات تستی — سینتکس، مثال و خطا — امتیازت را بسنج!

◧ ورودی‌های تابع

نام ورودینوعوضعیتتوضیح
known_y's محدوده الزامی مجموعه مقادیر y که قبلاً در رابطه y = mx + b می‌شناسید. اگر محدوده known_y's در یک ستون باشد، هر ستون از known_x's به‌عنوان متغیر جداگانه تفسیر می‌شود. اگر در یک سطر باشد، هر سطر از known_x's متغیر جداگانه محسوب می‌شود.
known_x's محدوده اختیاری مجموعه مقادیر x که ممکن است در رابطه y = mx + b بدانید. محدوده known_x's می‌تواند شامل یک یا چند مجموعه متغیر باشد. اگر تنها یک متغیر استفاده شود، known_y's و known_x's می‌توانند هر شکلی داشته باشند به شرط هم‌اندازه بودن. اگر چند متغیر استفاده شود، known_y's باید بردار باشد (یک سطر یا یک ستون). اگر known_x's حذف شود، آرایه {1,2,3,...} هم‌اندازه با known_y's فرض می‌شود.
const منطقی اختیاری مقدار منطقی که مشخص می‌کند آیا ثابت b برابر صفر اجبار شود یا خیر. اگر const مقدار TRUE داشته باشد یا حذف شود، b به‌صورت عادی محاسبه می‌شود. اگر FALSE باشد، b برابر صفر قرار می‌گیرد و مقادیر m برای برازش y = mx تنظیم می‌شوند.
stats منطقی اختیاری مقدار منطقی که مشخص می‌کند آیا آمار رگرسیون اضافی برگردانده شود یا خیر. اگر stats مقدار TRUE داشته باشد، LINEST آمار اضافی را برمی‌گرداند و آرایه برگشتی {mn,mn-1,...,m1,b;sen,sen-1,...,se1,seb;r2,sey;F,df;ssreg,ssresid} خواهد بود. اگر FALSE باشد یا حذف شود، فقط ضرایب m و ثابت b برگردانده می‌شود.

▣ مثال‌های کاربردی

▦ داده‌های مثال
سلولمقدار
A2 Known y
B2 Known x
A3 1
B3 0
A4 9
B4 4
A5 5
B5 2
A6 7
B6 3
A7 Result (slope)
B7 Result (y-intercept)
A8 2
B8 1
A9 =LINEST(A2:A5,B2:B5,,FALSE)
A10 Month
B10 Sales
A11 1
B11 $3,100
A12 2
B12 $4,500
A13 3
B13 $4,400
A14 4
B14 $5,400
A15 5
B15 $7,500
A16 6
B16 $8,100
A17 Floor space (x1)
B17 Offices (x2)
C17 Entrances (x3)
D17 Age (x4)
E17 Assessed value (y)
A18 2310
B18 2
C18 2
D18 20
E18 $142,000
A19 2333
B19 2
C19 2
D19 12
E19 $144,000
A20 2356
B20 3
C20 1.5
D20 33
E20 $151,000
A21 2379
B21 3
C21 2
D21 43
E21 $150,000
A22 2402
B22 2
C22 3
D22 53
E22 $139,000
A23 2425
B23 4
C23 2
D23 23
E23 $169,000
A24 2448
B24 2
C24 1.5
D24 99
E24 $126,000
A25 2471
B25 2
C25 2
D25 34
E25 $142,900
A26 2494
B26 3
C26 3
D26 23
E26 $163,000
A27 2517
B27 4
C27 4
D27 55
E27 $169,000
A28 2540
B28 2
C28 3
D28 22
E28 $149,000
A29 -234.2371645
A30 13.26801148
A31 0.996747993
A32 459.7536742
A33 1732393319
A34 =LINEST(E2:E12,A2:D12,TRUE,TRUE)
A35 Variable
B35 t-observed value
A36 Floor space
B36 5.1
A37 Number of offices
B37 31.3
A38 Number of entrances
B38 4.8
A39 Age
B39 17.7
A40 Known y
B40 Known x
A41 1
B41 0
A42 9
B42 4
A43 5
B43 2
A44 7
B44 3
A45 Result (slope)
B45 Result (y-intercept)
A46 2
B46 1
A47 =LINEST(A2:A5,B2:B5,,FALSE)
A48 Month
B48 Sales
A49 1
B49 $3,100
A50 2
B50 $4,500
A51 3
B51 $4,400
A52 4
B52 $5,400
A53 5
B53 $7,500
A54 6
B54 $8,100
A55 Floor space (x1)
B55 Offices (x2)
C55 Entrances (x3)
D55 Age (x4)
E55 Assessed value (y)
A56 2310
B56 2
C56 2
D56 20
E56 $142,000
A57 2333
B57 2
C57 2
D57 12
E57 $144,000
A58 2356
B58 3
C58 1.5
D58 33
E58 $151,000
A59 2379
B59 3
C59 2
D59 43
E59 $150,000
A60 2402
B60 2
C60 3
D60 53
E60 $139,000
A61 2425
B61 4
C61 2
D61 23
E61 $169,000
A62 2448
B62 2
C62 1.5
D62 99
E62 $126,000
A63 2471
B63 2
C63 2
D63 34
E63 $142,900
A64 2494
B64 3
C64 3
D64 23
E64 $163,000
A65 2517
B65 4
C65 4
D65 55
E65 $169,000
A66 2540
B66 2
C66 3
D66 22
E66 $149,000
A67 -234.2371645
A68 13.26801148
A69 0.996747993
A70 459.7536742
A71 1732393319
A72 =LINEST(E2:E12,A2:D12,TRUE,TRUE)
A73 Variable
B73 t-observed value
A74 Floor space
B74 5.1
A75 Number of offices
B75 31.3
A76 Number of entrances
B76 4.8
A77 Age
B77 17.7
محاسبه شیب و عرض از مبدأ با LINEST =SUM(LINEST(B1:B6, A1:A6)*{9,1})

§ توضیحات تکمیلی

  • هر خط مستقیم را می‌توان با شیب و عرض از مبدأ توصیف کرد: شیب (m): با دو نقطه (x1,y1) و (x2,y2) روی خط، شیب برابر (y2 - y1)/(x2 - x1) است. عرض از مبدأ (b): مقدار y در نقطه‌ای که خط محور y را قطع می‌کند. معادله خط مستقیم y = mx + b است. با داشتن m و b می‌توانید هر نقطه روی خط را با جای‌گذاری مقدار y یا x به دست آورید. همچنین می‌توانید از تابع TREND استفاده کنید.
  • وقتی تنها یک متغیر مستقل x دارید، می‌توانید شیب و عرض از مبدأ را مستقیماً با فرمول‌های زیر به دست آورید: شیب: =INDEX(LINEST(known_y's,known_x's),1) عرض از مبدأ: =INDEX(LINEST(known_y's,known_x's),2)
  • دقت خط محاسبه‌شده توسط LINEST به میزان پراکندگی داده‌ها بستگی دارد. هرچه داده‌ها خطی‌تر باشند، مدل LINEST دقیق‌تر است. LINEST از روش حداقل مربعات برای یافتن بهترین برازش استفاده می‌کند. وقتی تنها یک متغیر مستقل x دارید، محاسبات m و b بر اساس فرمول‌هایی است که x و y در آن‌ها میانگین نمونه هستند؛ یعنی x = AVERAGE(known x's) و y = AVERAGE(known_y's).
  • توابع برازش خط و منحنی LINEST و LOGEST می‌توانند بهترین خط مستقیم یا منحنی نمایی منطبق با داده‌ها را محاسبه کنند. با این حال باید تصمیم بگیرید کدام نتیجه بهتر با داده‌های شما سازگار است. می‌توانید TREND(known_y's,known_x's) را برای خط مستقیم یا GROWTH(known_y's, known_x's) را برای منحنی نمایی محاسبه کنید. این توابع بدون آرگومان new_x's، آرایه‌ای از مقادیر y پیش‌بینی‌شده در نقاط واقعی داده‌ها را برمی‌گردانند و می‌توانید مقادیر پیش‌بینی‌شده را با مقادیر واقعی مقایسه کنید یا هر دو را نمودار کنید.
  • در تحلیل رگرسیون، اکسل برای هر نقطه مربع اختلاف بین مقدار y برآوردشده و مقدار واقعی y را محاسبه می‌کند. مجموع این مربعات را مجموع مربعات باقی‌مانده (ssresid) می‌نامند. سپس اکسل مجموع کل مربعات (sstotal) را حساب می‌کند. وقتی آرگومان const برابر TRUE باشد یا حذف شود، مجموع کل مربعات برابر مجموع مربعات اختلاف مقادیر واقعی y با میانگین آن‌هاست. وقتی const برابر FALSE باشد، مجموع کل مربعات برابر مجموع مربعات مقادیر واقعی y است. سپس ssreg = sstotal - ssresid و ضریب تعیین r2 برابر ssreg/sstotal است.
  • در برخی موارد، یک یا چند ستون X ممکن است ارزش پیش‌بینی اضافی نداشته باشند و حذف آن‌ها دقت پیش‌بینی Y را کاهش ندهد. این پدیده «هم‌خطی» نام دارد. تابع LINEST هم‌خطی را بررسی و ستون‌های زائد را حذف می‌کند. ستون‌های حذف‌شده با ضرایب صفر و se صفر مشخص می‌شوند. در صورت حذف، مقدار df و به تبع آن sey و F تغییر می‌کنند.
  • مقدار df به این صورت محاسبه می‌شود وقتی هیچ ستون X به دلیل هم‌خطی حذف نشده باشد: اگر k ستون known_x's وجود داشته باشد و const برابر TRUE باشد یا حذف شود، df = n – k – 1 و اگر const برابر FALSE باشد، df = n - k. هر ستون X حذف‌شده به دلیل هم‌خطی، مقدار df را یک واحد افزایش می‌دهد.
  • هنگام وارد کردن ثابت آرایه‌ای (مانند known_x's) به‌عنوان آرگومان، از کاما برای جداسازی مقادیر در یک سطر و از نقطه‌ویرگول برای جداسازی سطرها استفاده کنید. نویسه‌های جداکننده ممکن است بسته به تنظیمات منطقه‌ای متفاوت باشند.
  • توجه داشته باشید که مقادیر y پیش‌بینی‌شده توسط معادله رگرسیون ممکن است در خارج از محدوده مقادیر y استفاده‌شده برای تعیین معادله معتبر نباشند.
  • الگوریتم زیربنایی تابع LINEST با الگوریتم توابع SLOPE و INTERCEPT متفاوت است. این تفاوت می‌تواند در داده‌های نامعین و هم‌خط به نتایج متفاوت منجر شود. برای مثال اگر نقاط known_y's برابر صفر و نقاط known_x's برابر ۱ باشند، LINEST مقدار صفر برمی‌گرداند در حالی که SLOPE و INTERCEPT خطای #DIV/0! می‌دهند. الگوریتم LINEST برای داده‌های هم‌خط نتایج معقولی برمی‌گرداند و حداقل یک پاسخ می‌یابد، اما الگوریتم SLOPE و INTERCEPT تنها به دنبال یک پاسخ است.
  • علاوه بر استفاده از LOGEST برای محاسبه آمار سایر انواع رگرسیون، می‌توانید با وارد کردن توابعی از متغیرهای x و y به‌عنوان سری‌های x و y برای LINEST، انواع دیگری از رگرسیون را محاسبه کنید. برای مثال =LINEST(yvalues, xvalues^COLUMN($A:$C)) تقریب مکعبی به شکل y = m1*x + m2*x^2 + m3*x^3 + b را محاسبه می‌کند.
  • مقدار F که تابع LINEST برمی‌گرداند با مقدار F که تابع FTEST برمی‌گرداند متفاوت است. LINEST آماره F را برمی‌گرداند در حالی که FTEST احتمال را برمی‌گرداند.

✦ نکات مهم

  • هر خط مستقیم را می‌توان با شیب و عرض از مبدأ توصیف کرد: شیب (m): با دو نقطه (x1,y1) و (x2,y2) روی خط، شیب برابر (y2 - y1)/(x2 - x1) است. عرض از مبدأ (b): مقدار y در نقطه‌ای که خط محور y را قطع می‌کند. معادله خط مستقیم y = mx + b است. با داشتن m و b می‌توانید هر نقطه روی خط را با جای‌گذاری مقدار y یا x به دست آورید. همچنین می‌توانید از تابع TREND استفاده کنید.
  • وقتی تنها یک متغیر مستقل x دارید، می‌توانید شیب و عرض از مبدأ را مستقیماً با فرمول‌های زیر به دست آورید: شیب: =INDEX(LINEST(known_y's,known_x's),1) عرض از مبدأ: =INDEX(LINEST(known_y's,known_x's),2)
  • دقت خط محاسبه‌شده توسط LINEST به میزان پراکندگی داده‌ها بستگی دارد. هرچه داده‌ها خطی‌تر باشند، مدل LINEST دقیق‌تر است. LINEST از روش حداقل مربعات برای یافتن بهترین برازش استفاده می‌کند. وقتی تنها یک متغیر مستقل x دارید، محاسبات m و b بر اساس فرمول‌هایی است که x و y در آن‌ها میانگین نمونه هستند؛ یعنی x = AVERAGE(known x's) و y = AVERAGE(known_y's).
  • توابع برازش خط و منحنی LINEST و LOGEST می‌توانند بهترین خط مستقیم یا منحنی نمایی منطبق با داده‌ها را محاسبه کنند. با این حال باید تصمیم بگیرید کدام نتیجه بهتر با داده‌های شما سازگار است. می‌توانید TREND(known_y's,known_x's) را برای خط مستقیم یا GROWTH(known_y's, known_x's) را برای منحنی نمایی محاسبه کنید. این توابع بدون آرگومان new_x's، آرایه‌ای از مقادیر y پیش‌بینی‌شده در نقاط واقعی داده‌ها را برمی‌گردانند و می‌توانید مقادیر پیش‌بینی‌شده را با مقادیر واقعی مقایسه کنید یا هر دو را نمودار کنید.
  • در تحلیل رگرسیون، اکسل برای هر نقطه مربع اختلاف بین مقدار y برآوردشده و مقدار واقعی y را محاسبه می‌کند. مجموع این مربعات را مجموع مربعات باقی‌مانده (ssresid) می‌نامند. سپس اکسل مجموع کل مربعات (sstotal) را حساب می‌کند. وقتی آرگومان const برابر TRUE باشد یا حذف شود، مجموع کل مربعات برابر مجموع مربعات اختلاف مقادیر واقعی y با میانگین آن‌هاست. وقتی const برابر FALSE باشد، مجموع کل مربعات برابر مجموع مربعات مقادیر واقعی y (بدون کم کردن میانگین) است. سپس مجموع مربعات رگرسیون (ssreg) از رابطه ssreg = sstotal - ssresid به دست می‌آید. هرچه مجموع مربعات باقی‌مانده در مقایسه با مجموع کل کوچک‌تر باشد، ضریب تعیین r2 که نشان‌دهنده میزان تبیین رابطه متغیرها توسط معادله رگرسیون است بزرگ‌تر خواهد بود و مقدار r2 برابر ssreg/sstotal است.
  • در برخی موارد، یک یا چند ستون X (فرض کنید Y و X در ستون‌ها هستند) ممکن است در حضور سایر ستون‌های X ارزش پیش‌بینی اضافی نداشته باشند. به عبارت دیگر حذف یک یا چند ستون X ممکن است مقادیر پیش‌بینی‌شده Y را به همان دقت حفظ کند. در این صورت این ستون‌های زائد باید از مدل رگرسیون حذف شوند. این پدیده «هم‌خطی» (collinearity) نامیده می‌شود، زیرا هر ستون X زائد را می‌توان به‌صورت مجموع مضاربی از ستون‌های X غیرزائد بیان کرد. تابع LINEST هم‌خطی را بررسی و ستون‌های X زائد را هنگام تشخیص حذف می‌کند. ستون‌های حذف‌شده در خروجی LINEST با ضرایب صفر و مقادیر se صفر قابل تشخیص هستند. اگر ستونی به‌عنوان زائد حذف شود، مقدار df تغییر می‌کند زیرا df به تعداد ستون‌های X واقعاً استفاده‌شده بستگی دارد. در صورت تغییر df، مقادیر sey و F نیز تغییر می‌کنند. هم‌خطی در عمل نسبتاً نادر است، اما زمانی که برخی ستون‌های X فقط مقادیر ۰ و ۱ به‌عنوان شاخص عضویت در گروه داشته باشند محتمل‌تر است. اگر const برابر TRUE باشد یا حذف شود، LINEST عملاً ستون اضافی از مقادیر ۱ برای مدل‌سازی عرض از مبدأ اضافه می‌کند. اگر ستونی برای مرد بودن (۱) یا نبودن (۰) و ستون دیگری برای زن بودن داشته باشید، ستون دوم زائد است زیرا می‌توان آن را از تفریق ستون شاخص مرد از ستون تماماً ۱ به دست آورد.
  • هنگام وارد کردن ثابت آرایه‌ای (مانند known_x's) به‌عنوان آرگومان، از کاما برای جداسازی مقادیر در یک سطر و از نقطه‌ویرگول برای جداسازی سطرها استفاده کنید. نویسه‌های جداکننده ممکن است بسته به تنظیمات منطقه‌ای متفاوت باشند.
  • توجه داشته باشید که مقادیر y پیش‌بینی‌شده توسط معادله رگرسیون ممکن است در خارج از محدوده مقادیر y استفاده‌شده برای تعیین معادله معتبر نباشند.
  • علاوه بر استفاده از LOGEST برای محاسبه آمار سایر انواع رگرسیون، می‌توانید با وارد کردن توابعی از متغیرهای x و y به‌عنوان سری‌های x و y برای LINEST، انواع دیگری از رگرسیون را محاسبه کنید. برای مثال فرمول =LINEST(yvalues, xvalues^COLUMN($A:$C)) وقتی یک ستون از مقادیر y و یک ستون از مقادیر x دارید، تقریب مکعبی (چندجمله‌ای درجه ۳) به شکل y = m1*x + m2*x^2 + m3*x^3 + b را محاسبه می‌کند. می‌توانید این فرمول را برای انواع دیگر رگرسیون تنظیم کنید، اما در برخی موارد نیاز به تعدیل مقادیر خروجی و سایر آمار است.
  • مقدار F که تابع LINEST برمی‌گرداند با مقدار F که تابع FTEST برمی‌گرداند متفاوت است. LINEST آماره F را برمی‌گرداند در حالی که FTEST احتمال را برمی‌گرداند.

⚠ خطاهای رایج

#DIV/0! توابع SLOPE و INTERCEPT در داده‌های هم‌خط خطای #DIV/0! برمی‌گردانند، در حالی که LINEST مقدار صفر برمی‌گرداند.
راه‌حل: اگر با SLOPE یا INTERCEPT خطای #DIV/0! گرفتید، بررسی کنید داده‌ها هم‌خط یا نامعین نباشند و در صورت نیاز از LINEST استفاده کنید.

⇄ توابع مرتبط

=INDEX() مقدار یک عنصر در جدول یا آرایه را که با شاخص سطر و ستون انتخاب شده است برمی‌گرداند. اگر آرگومان اول ثابت آرایه باشد از شکل آرایه‌ای استفاده کنید. همچنین ارجاع سلول واقع در تقاطع یک سطر و ستون مشخص را برمی‌گرداند و اگر ارجاع از چند ناحیه ناپیوسته تشکیل شده باشد می‌توانید ناحیه موردنظر را انتخاب کنید. =INTERCEPT() نقطه تقاطع خط رگرسیون با محور y را با استفاده از مقادیر x و y موجود محاسبه می‌کند. این نقطه بر اساس بهترین خط برازش‌شده از روی نقاط داده شناخته‌شده به‌دست می‌آید. وقتی می‌خواهید مقدار متغیر وابسته را زمانی که متغیر مستقل صفر است پیش‌بینی کنید از INTERCEPT استفاده کنید؛ برای مثال پیش‌بینی مقاومت الکتریکی یک فلز در دمای صفر درجه وقتی داده‌ها در دمای اتاق و بالاتر ثبت شده‌اند. =LOGEST() معادله منحنی نمایی به‌صورت y = b*m^x یا y = (b*(m1^x1)*(m2^x2)*...) در صورت وجود چند مقدار x است، که در آن مقدار y وابسته تابعی از مقادیر x مستقل است. مقادیر m پایه‌های متناظر با هر توان x هستند و b مقدار ثابت است. توجه داشته باشید که y و x و m می‌توانند بردار باشند. آرایه‌ای که LOGEST برمی‌گرداند {mn,mn-1,...,m1,b} است. =SLOPE() شیب خط رگرسیون خطی عبوری از نقاط داده موجود در known_y's و known_x's را برمی‌گرداند. شیب برابر است با فاصله عمودی تقسیم بر فاصله افقی بین هر دو نقطه روی خط، که همان نرخ تغییر در طول خط رگرسیون است. =TREND() تابع TREND مقادیر را در امتداد یک روند خطی برمی‌گرداند. این تابع با روش حداقل مربعات، خطی مستقیم را بر داده‌های known_y's و known_x's برازش می‌کند و سپس مقادیر y متناظر با آرایه new_x's که مشخص می‌کنید را روی همان خط برمی‌گرداند.

💬 نظرات و پرسش و پاسخ 0 نظر تاییدشده

🔒 نظر شما پس از تایید مدیر منتشر می‌شود.

دیدگاه خود را بنویسید — پس از تایید مدیر نمایش داده می‌شود

هنوز نظری ثبت نشده — اولین نظر را شما بنویسید!

✍️ ارسال نظر / سوال

captcha
🔒 نظر شما پس از تایید مدیر منتشر می‌شود.