Как да изчислим R2 Excel

Във финансите и статистиката, коефициент на детерминация, наричан още R-квадрат (или Р²) е мярка за връзката между два набора от данни, използвани в математически модел. Той представлява съотношението на дисперсията в зависимата променлива, която може да бъде предвидена от независимата променлива в модела. Често се използва в регресионни анализи за оценка на прогнозите за бъдещи резултати въз основа на наблюдаваните резултати. Можете да изчислите R-квадрат в Excel с помощта на функцията RSQ.

В Microsoft Excel функцията RSQ се използва за определяне на стойността на R-квадрат за два набора от точки от данни. Функцията връща квадрата на коефициента на корелация на момента на Pearson, който измерва линейната корелация между променливите x и y. Коефициентът на корелация винаги попада в диапазона от -1 и +1. Стойността, връщана от RSQ в Excel, винаги е между 0 и 1 (тъй като се изчислява като квадрат на коефициент на корелация, никога не може да върне отрицателна стойност).

Функцията RSQ приема два набора от данни като аргументи, наричани известен_x и известен_y. Тези набори от данни могат да бъдат под формата на списък с числа или списък или диапазон от препратки към клетки. Например, да речем, че искате да направите регресионен анализ на парите, изразходвани за реклама, спрямо приходи от продажби, където месечните рекламни разходи са посочени в колона А, а месечният доход е посочен в колона Б. Можете да използвате функцията RSQ, като въведете RSQ(A1:A10,B1:B10), който използва стойности в редове от 1 до 10 от колони A (разходи за реклама) и B (приходи).

Excel също така предоставя начин за изчисляване на коефициента на корелация за два набора от данни с помощта на функциите CORREL и PEARSON. Подобно на функцията RSQ, както CORREL, така и PEARSON приемат два диапазона от стойности на клетките като аргументи. Вземането на резултата от CORREL или PEARSON за намиране на коефициента на корелация и квадратурата на резултата е същото като използването на функцията RSQ за определяне на коефициента на детерминация.

Функциите CORREL и PEARSON връщат стойности между -1 и 1. Това е безразмерна мярка за положителна или отрицателна корелация между двата набора данни, предоставени като аргументи. Връщаната стойност от функцията RSQ е между 0 и 1, понякога изразена като процент от 0 до 100. Много анализатори смятат, че по-високият резултат от RSQ показва по-точен математически модел, докато други да кажем, че е важно да се разгледат всички фактори, които биха могли да изкривят висок или нисък резултат преди тегленето заключения.

Експертите също така казват, че трябва да избягвате да сравнявате стойности на R-квадрат за различни модели и набори от данни. В случаите, когато са големи разлики между типа на сравняваните данни, резултатите могат да бъдат подвеждащи. Има по-сложни мерки за сравняване на модели от стойностите на R-квадрат, като F-тестове и информационни критерии.

Разсейваната диаграма на Excel най-често се използва за показване на връзки между набори от данни по време на регресионни анализи. Обхватът от стойности за един набор от данни е показан на хоризонталната ос x, а диапазонът за другия набор от вертикалната ос y. Точките от данни се съпоставят с пресечната точка на x и y стойности, като се използват двойки стойности от всеки набор от данни.

Използвайки примера за реклама и продажби, където рекламните разходи са изброени в колона A, а месечният доход в колона B, хоризонталната ос ще показва обхвата на месечния доход, а вертикалната ще показва обхвата на рекламата разходи. Точките от данни в диаграмата ще бъдат начертани чрез разглеждане на съседни клетки в колона A и колона B. Полученият модел от точки може да се използва за визуализиране на степента на корелация между променливите.