Смесена диаграма в ексел. Комбиниране на различни видове диаграми в EXCEL

Изграждане на комбинирани диаграми

В случаите, когато трябва да покажете данни от различни мащаби на една диаграма, е полезно да използвате комбинирани диаграми. Типичен случай са абсолютни (рубли) и относителни (проценти) показатели. Например размерът на вземанията и техният дял от продажбите.

Комбиниран (или смесен ) е диаграма, която се състои от множество серии от данни и използва различни типове диаграми едновременно (например хистограма и графика). За да създадете комбинирани диаграми, са ви необходими поне две серии от данни. Примери за смесени диаграми са публикувани в книгата Комбиниран

Има някои ограничения за изграждане на диаграми с комбинации:

· невъзможно е смесването на типове диаграми с обемни типове;

· някои комбинации от типове диаграми изглеждат изключително незадоволителни (например комбинация от графика и радарна диаграма);

· смесена диаграма използва една област за начертаване, поради което е невъзможно да се създаде например комбинирана диаграма, състояща се от три кръгови диаграми;

· Комбинирането на графика и стълбовидна диаграма не се поддържа: оста на категорията на стълбовидна диаграма винаги е насочена вертикално, а оста на графика винаги е насочена хоризонтално.

Фигурата показва диаграма, изградена с помощта на три серии от данни. Освен това температурите на въздуха и водата са представени под формата на хистограма, а количеството на валежите е представено в графика.

Фигурата показва хистограма, изградена с помощта на две серии от данни:

Нека променим типа на диаграмата за втората поредица от данни ( Валежи) и използвайте отделна ос на стойността за тази серия.

1. Изберете серия от данни на диаграмата Валежии извикайте контекстното меню на реда.

2. Отворете прозореца Формат на серията данни (Форматиране на серия от данни)и на раздела Настроики ред (Опции за серията)инсталирайте превключвателя от спомагателни брадви(Вторична ос).

3. Без да премахвате селекцията от реда, изпълнете командата Работа с диаграми– Конструктор – Тип – промяна Тип диаграми(Диаграма Инструменти – Дизайн – Тип – промяна Диаграма Тип) .

4. В диалоговия прозорец Промяна на типа на диаграмата изберете типа График (Линия) и щракнете върху бутона Добре.

Данните за валежите вече се визуализират като сегменти с права линия, с нов отдясно Стойностна ос (VалуеАxis).

Важно е да се разбере, че екипът Работа с диаграми– Конструктор – Тип – промяна Тип диаграми (Диаграма Инструменти – Дизайн – Тип – промяна Диаграма Тип) работи по различен начин в зависимост от избраното. Ако е избрана поредица от диаграми, командата променя типа само на тази поредица. Ако е избран друг елемент от диаграмата, командата променя типа на цялата диаграма.

Наслагване диаграми (Оверлей° Ссърца)

Наслагването на диаграми се отнася за поставяне на диаграми като графични обекти: един върху друг. Горната диаграма обикновено има прозрачна област на диаграма и област за чертане. Той премахва всички елементи, освен например маркери и линии. Наслагването на диаграма е ръчна задача, която изисква прецизно позициониране на обекти, настройка на оси на стойност и категория.

За да подредите диаграмите „подредени“ в подходящ ред, те трябва да бъдат избрани като обекти. За да изберете обект, а не диаграма, натиснете Ctrlи щракнете върху диаграмата.

Нека да разгледаме прост пример (лист Наслагване_1книги Комбиниран). Известно е, че едно от ограниченията при изграждането на комбинирани диаграми е невъзможността да се смесват типове диаграми с обемни типове. Методът за наслагване на диаграма ви позволява да комбинирате графика и обемна хистограма на лист. Нека предприемем следните стъпки:

1. Изберете диапазон в таблицата с данни A2:B7и изградете обемна хистограма с групиране. Нека премахнем легендата на диаграмата.

2. Изберете несъседни диапазони A2:A7И C2:C7и изградете графика с маркери. Нека премахнем легендата на диаграмата.

3. На втората диаграма задайте прозрачно запълване за областта на диаграмата и областта за чертане на диаграмата и изтрийте осите. Нека форматираме линиите и маркерите на графиката.

4. Комбинирайте ръчно графиката и обемната хистограма. Чрез промяна на размера на обекта „Graph“ ще гарантираме, че маркерите на графиката съвпадат със средата на колоните на хистограмата.

5. След окончателното форматиране ще получим насложена диаграма.

6. Ако след като изберете две диаграми като графични обекти, ги групирате, получената насложена диаграма ще бъде копирана и преместена като един обект. Имайте предвид, че опитът да поставите диаграмата на отделен лист ще унищожи наслагването.

Следващата илюстрация показва наслагване на 3-D кръгова диаграма и 3-D хистограма. Цялостната рамка създава впечатлението, че е една диаграма. Това обаче са две отделни диаграми. Рамката не принадлежи на тях, а на клетките на работния лист. Заглавието е свободно подвижен надпис. Данните за такова наслагване са дадени на листа Циркуляр _обеменкниги Комбиниран.

Обемните хистограми на Excel не ви позволяват да показвате допълнителни редове в дълбочина. Наслагването на диаграми преодолява това ограничение. Достатъчно е да се построят три обемни хистограми с натрупване (за всяка година поотделно) и да се комбинират така, че да се „създаде” трета ос в дълбочина. Графиката ви позволява визуално да сравнявате резултатите по три параметъра - региони, месеци и години. Оригиналната таблица с данни е в работния лист Три_томкниги Комбиниран.

Стандартните инструменти на Excel за кръгови диаграми ви позволяват да използвате само един набор от данни. Тази публикация ще ви покаже как да създадете кръгова диаграма въз основа на два набора от стойности.

Като пример взех населението на Земята по континенти през 1950 и 2000 г. (вижте листа „Население“ във файла на Excel; премахнах Австралия, защото нейният дял е незначителен и диаграмата става трудна за четене :)). Първо създайте основна кръгова диаграма: изберете диапазона A1:C6, отидете на Insert → Pie → Pie.

Ориз. 1. Създайте обикновена кръгова диаграма

Изтеглете бележката във формат, примери във формат

Щракнете с десния бутон върху диаграмата и изберете Форматиране на серия от данни от контекстното меню. Изберете „По малка ос“ и след това преместете плъзгача към „Разделение“ до нещо като 70% (Фигура 2). Секторите от един и същ ред ще се „раздалечат“.

Ориз. 2. По малката ос

Изберете последователно отделни сектори (чрез бавно двойно щракване) и променете тяхното запълване и местоположение, като свържете всички сектори в центъра (фиг. 3).

Ориз. 3. Форматиране на точки на ред (индивидуални сектори)

Форматирайте всички сектори така, че цветовете, съответстващи на един и същи континент в различни редове, да са от една и съща гама, но с различен интензитет. Попълнете диаграмата с етикети на данни, легенда и заглавие (Фигура 4).

Ориз. 4. Кръгова диаграма с два набора от данни

Диаграмата ясно показва например, че делът на Азия е нараснал от 55,8% на 60,9 за 50 години, докато делът на Европа е намалял от 21,8% на 12,1% за същото време.

Ако не сте фен на кръговите диаграми, можете да използвате поничковата диаграма, която в стандарта на Excel работи с множество набори от данни (фиг. 5); вижте също листа „Пръстен“ във файла на Excel. Изберете областта с данни (в нашия пример това е A1:C6) и отидете в менюто Вмъкване – Графики – Други диаграми – Поничка:

Ориз. 5. Създайте кръгла диаграма

Всичко, което трябва да направите, е да редактирате малко диаграмата, за да я направите по-визуална (фиг. 6)

Ориз. 6. Понична диаграма

Идеята се вижда в книгата на D. Kholey, R. Kholey “Excel 2007. Трикове”.

Хистограмата в Excel е начин за създаване на визуална диаграма, която показва промените в няколко типа данни за определен период от време.

Хистограмата е полезна за илюстриране и сравняване на различни параметри. Нека да разгледаме най-популярните видове хистограми и да научим как да ги изградим.

Как да изградя актуализирана хистограма?

Разполагаме с данни за продажби на различни видове млечни продукти за всеки месец за 2015г.

Нека изградим актуализирана хистограма, която ще отговаря на промените, направени в таблицата. Изберете целия масив, включително заглавката, и щракнете върху раздела INSERT. Нека намерим ДИАГРАМИ - ХИСТОГРАМА и изберем първия тип. Нарича се ХИСТОГРАМА С ГРУПИРАНЕ.

Получихме хистограма, чийто размер на полето може да се променя. На тази диаграма ясно се вижда например, че най-големи са продажбите на мляко през ноември, а най-малко на сметана през юни.

Ако направим промени в таблицата, външен видхистограмата също ще се промени. Например, вместо 1400 през януари за кефир, нека поставим 4000. Виждаме как зелената колона излетя нагоре.

Подредена хистограма

Сега нека да разгледаме как да изградим подредена хистограма в Excel. Друг тип хистограма, която ви позволява да показвате данни като процент. Изгражда се по абсолютно същия начин, но се избира друг вид.

Получаваме диаграма, където можете да видите, че например през януари е продадено повече мляко, отколкото кефир или сметана. И през август, в сравнение с други млечни продукти, се продава малко мляко. И така нататък.

Хистограмите в Excel могат да се променят. Така че, ако щракнете с десния бутон върху празна област на диаграмата и изберете ПРОМЯНА НА ТИПА, можем да го променим донякъде. Нека променим типа на нашата хистограма от подредена на нормализирана. Резултатът ще бъде същата диаграма, но оста Y ще показва съотношенията в проценти.

По същия начин можете да направите други промени в хистограмата, което направихме ние:

• промени шрифта на Arial и промени цвета му на лилав;
• направи подчертаване с пунктирана линия;
• премести легендата малко по-високо;
• добави надписи към колони.

Как да комбинирам хистограма и графика в Excel?

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

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

Сега нека модифицираме съществуващата диаграма. Щракнете с десния бутон върху празно място и изберете ИЗБОР НА ДАННИ. Ще се появи поле с молба да изберете различен интервал. Селектираме отново цялата таблица, но този път включително реда с приходите.

Excel автоматично разшири диапазона от стойности по оста Y, така че данните за броя на продажбите останаха в самото дъно под формата на невидими колони.

Но такава хистограма е неправилна, тъй като на една диаграма имаме числа в рубли и количествени еквиваленти (рубли и литри). Следователно трябва да се направят промени. Нека преместим данните за приходите от дясната страна. Щракнете с десния бутон върху лилавите колони, изберете ФОРМАТ НА ПОРЕДИЦАТА ДАННИ и изберете СПОМАГАТЕЛНА ОС.

Виждаме, че графикът веднага се промени. Сега лилавата лента за приходи има своя собствена област на стойността (вдясно).

Но това все още не е много удобно, защото колоните почти се сливат. Затова ще извършим още едно допълнително действие: щракнете с десния бутон върху лилавите колони и изберете ПРОМЯНА НА ТИПА НА ГРАФИКАТА ЗА СЕРИИ. Ще се появи прозорец, в който избираме диаграма, първият тип.

Получаваме много ясна диаграма, която е комбинация от хистограма и графика. Виждаме, че максималните приходи са през януари и ноември, а минималните през август.

В Excel можете да покажете резултатите от изчисленията под формата на диаграма или графика, като им придадете по-голяма яснота, а за сравнение понякога трябва да изградите две графики една до друга. Ще разгледаме по-нататък как да изградим две графики в Excel в едно поле.

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

Избираме две колони с резултати от изчисленията и изграждаме правилна хистограма.

Сега нека се опитаме да добавим друга хистограма към съществуващите със същия брой резултати от изчисленията. За да добавите графика в Excel, направете съществуващата графика активна, като я изберете и в раздела, който се показва "Конструктор"избирам „Избор на данни“. В прозореца, който се появява в раздела "Елементи на легендата"щракнете върху добавяне и посочете клетки „Име на ред:“И "Стойности:"на листа, който ще бъде стойностите за изчисление на функцията "j".

Сега нека видим как ще изглежда нашата диаграма, ако добавим още една към съществуващите хистограми, която има почти два пъти повече стойности. Нека добавим стойностите на функцията към графиката "к".

Както можете да видите, има много по-скоро добавени стойности и те са толкова малки, че са практически невидими на хистограмата.

Ако променим типа на диаграмата от хистограма на обикновена графика, резултатът в нашия случай ще бъде по-ясен.

Ако използвате точкова диаграма за изграждане на графики в Excel, тогава получените графики ще вземат предвид не само резултатите от изчислението, но и изходните данни, т.е. ще има ясна зависимост между количествата.

За да създадете точкова графика, изберете колона с начални стойности и двойка колони с резултати за две различни функции. В раздела "Вмъкване"изберете точкова диаграма с гладки криви.

За да добавите друга графика, изберете съществуващите и в раздела "Конструктор"Натиснете „Избор на данни“.

В нов прозорец в колоната "Елементи на легендата"Натиснете "Добави"и посочете клетките за „Име на ред:“, „Стойности X:“И „Y Стойности:“. Нека добавим функцията по този начин "j"към графиката.

Обикновено диаграмите използват един стил, като колонна, лентова или кръгова диаграма. Ако вашата диаграма има повече от една поредица от данни, можете да покажете различни стилове в диаграмата си, създавайки смесена диаграма.

На фиг. Фигура 136.1 показва диаграма, която е много подходяща за преобразуване в смесени. Графиката илюстрира средната температура и валежите по месеци. Но има проблем. Двата диапазона от данни ясно се различават по големина, така че лентите за валежите са едва видими в диаграмата.

На фиг. Фигура 136.2 е диаграмата, след като я преобразувах в смесена диаграма. Стойностите на валежите вече се нанасят като линия и се използва втората вертикална ос отдясно. Повечето от вас ще се съгласят, че тази диаграма е значително подобрение спрямо оригинала. Можете да го използвате, когато анализирате цената на стоките в салон за бижута във Вологда.

Можете да търсите командата Mixed Charts на лентата на Excel цял ден и пак няма да я намерите. Ако вече сте създали вашата диаграма (която има поне две серии от данни), можете лесно да я конвертирате в смесена диаграма. Следвай тези стъпки:

1. Кликнете върху редовете, които искате да промените.
2. Изберете Работа с диаграми Designer Type Промяна на типа на диаграмата.
3. В диалоговия прозорец Промяна на типа на диаграмата изберете иконата, която представлява типа на диаграмата за избраната серия.
4. Ако искате да използвате втора вертикална ос за преобразуваната серия, изберете серията и щракнете Ctrl+1за да отворите диалогов прозорец Формат на серията данни; отидете на раздел Параметри на сериятаи настройте превключвателя Изградете редна позиция По малката ос.

Диаграма на фиг. 136.1 беше стандартна стълбовидна диаграма с два реда. Няколко кликвания и една от сериите (валежи в този пример) се преобразува от колони в ред. Добавих също втора ос и маркирах осите, така че да е ясно коя скала се използва за коя серия. За да добавите заглавия на оси, изпълнете командата Работа със заглавия на оста на оформлението на диаграми.

На фиг. Фигура 136.3 показва друга смесена диаграма. Той съчетава колонна и стълбовидна диаграма, като и двете серии използват една и съща вертикална ос. В този случай показването на месечните цели за продажби като лентова диаграма улеснява намирането на месеците, в които целта е била постигната.

На фиг. Фигура 136.4 демонстрира докъде можете да стигнете със смесени диаграми. Тази диаграма съчетава пет различни типа диаграми: кръгова диаграма, диаграма с площ, стълбова (лента), стълбова и точкова (XY). Не мога да си представя в каква ситуация би била подходяща такава диаграма, но е доста интересна демонстрация.

В някои случаи не можете да смесвате типове диаграми. Например не можете да създадете смесена диаграма, която включва балонна и повърхностна (3D) диаграма. Ако изберете несъвместим тип диаграма за серия, Excel ще ви уведоми.