Как да създадете диаграма на разсейване в Excel

Една от най-интересните и полезни форми на анализ на данни, които можете да извършите в Excel, е регресионният анализ. В регресионния анализ вие изследвате връзката между два набора от стойности, като търсите асоциация. Например, можете да използвате регресионен анализ, за ​​да определите дали разходите за реклама са свързани с продажбите, дали пушенето на цигари е свързано със сърдечни заболявания или дали упражненията са свързани с дълголетието.

Често първата ви стъпка във всеки регресионен анализ е да създадете диаграма на разсейване, която ви позволява визуално да изследвате връзката между два набора от стойности. В Excel правите това с помощта на XY (разсейваща) диаграма.

Например, да предположим, че искате да разгледате или анализирате тези стойности. Диапазонът на работния лист A1:A11 показва броя на рекламите. Диапазонът на работния лист B1:B11 показва получените продажби. С тези събрани данни можете да изследвате ефекта на рекламите върху продажбите или липсата на ефект.

За да създадете диаграма на разсейване на тази информация, направете следните стъпки:

Изберете диапазона на работния лист A1:B11.

В раздела Вмъкване щракнете върху командния бутон на диаграмата XY (Разсейване).

Изберете подтипа диаграма, който не включва никакви линии.

Excel показва вашите данни в XY (разсейваща) диаграма.

Потвърдете организацията на данните на диаграмата.

Потвърдете, че Excel всъщност е подредил правилно вашите данни, като погледнете диаграмата.

Ако не сте доволни от организацията на данните на диаграмата – може би данните изглеждат обратни или преобърнати – щракнете върху командния бутон Превключване на ред/колона в раздела Дизайн на инструменти за диаграма. (Можете дори да експериментирате с командата Switch Row/Column, така че опитайте, ако смятате, че може да помогне.) Имайте предвид, че тук данните са правилно организирани. Диаграмата показва резултата от здравия разум, че увеличената реклама изглежда се свързва с увеличени продажби.

Анотирайте диаграмата, ако е подходящо.

Добавете тези малки цветя към вашата диаграма, които ще я направят по-привлекателна и четлива. Например, можете да използвате бутоните Заглавие на диаграма и Заглавия на оси, за да анотирате диаграмата със заглавие и с описания на осите, използвани в диаграмата.

Добавете линия на тренда, като щракнете върху командния бутон Trendline на менюто Добавяне на елемент на диаграма.

За да покажете менюто Добавяне на елемент на диаграма, щракнете върху раздела Дизайн и след това щракнете върху командата Добавяне на елемент на диаграма. За да се покаже разделът Дизайн, трябва първо да сте избрали вграден обект на диаграма или да сте показали лист с диаграма.

Excel показва менюто Trendline. Изберете типа изчисление на линията на тренда или регресията, което искате, като щракнете върху една от наличните опции за линията на тренда. Например, за да извършите проста линейна регресия, щракнете върху бутона Линейна.

В Excel 2007 добавяте линия на тренда, като щракнете върху командата Линия на тренда в раздела Инструменти за диаграма Оформление.

Добавете регресионното уравнение към диаграмата на разсейване.

За да покажете уравнението за линията на тренда, която използва диаграмата на разсейване, изберете командата More Trendline Options от менюто Trendline.

След това поставете отметка както в квадратчетата за показване на уравнението на диаграмата, така и за отметките на Показване на R-квадрат стойност на диаграмата. Това казва на Excel да добави информацията за прост регресионен анализ, необходима за линия на тренд към вашата диаграма. Имайте предвид, че може да се наложи да превъртите надолу по екрана, за да видите тези квадратчета за отметка.

В Excel 2007 и Excel 2010 щракнете върху бутона Линия на тренда в раздела Оформление на диаграмата и изберете опцията Още линии на тренда, за да покажете диалоговия прозорец Форматиране на линията на тренда.

Използвайте бутоните за избор и текстовите полета в панела Форматиране на тренда, за да контролирате как се изчислява трендовата линия на регресионния анализ. Например, можете да използвате квадратчето за отметка Set Intercept = и текстовото поле, за да принудите линията на тренда да пресече оста x в определена точка, като нула.

Можете също да използвате текстовите полета Прогноза напред и назад, за да посочите, че линията на тренда трябва да бъде удължена назад или напред извън съществуващите данни или преди тях.

Щракнете върху OK.

Едва можете да видите данните за регресията, така че това е форматирано, за да стане по-четливо.