В този практически урок се обяснява какво представлява диаграмата Pivot и как да я създадем и персонализираме. Ще видим и разликата между диаграмата Pivot и таблицата:
Графиките се считат за един от най-добрите начини за представяне на отчета. Те ни помагат да разберем и анализираме данните по един по-прост начин. Графиките на пивотите в Excel ни дават възможност за визуално представяне на данните по различни начини.
В този урок ще научим всички подробности, които са необходими за работа с въртящи се диаграми в Excel. Създаване на диаграми от различни типове, форматиране на оформлението им, добавяне на филтри, добавяне на потребителски формули и използване на формата на една диаграма към друга диаграма, принадлежаща към различни въртящи се таблици.
Какво представлява диаграмата на въртене в Excel
Графиката на въртене в Excel е визуално представяне на данните. Тя ви дава цялостна картина на необработените данни. Тя ви позволява да анализирате данните, като използвате различни видове графики и оформления. Счита се, че е най-добрата графика по време на бизнес презентация, която включва огромни данни.
Pivot Chart Vs Table
Таблица за завъртане ни предоставя начин за обобщаване на големи данни в матрица, подобна на мрежа. Можете да изберете полетата, които искате да използвате в таблицата за редове и колони. Графиката на въртене ни предоставя графично представяне на таблицата на въртене. Можете да избирате от множество оформления и типове графики.
Тази диаграма също така обобщава данните. Можете да създадете както въртяща се таблица, така и диаграма за даден източник на данни и да ги обработвате едновременно. Това означава, че промените, направени в въртящата се таблица, ще се отразят в диаграмата и обратно.
Източник на данни
По-долу е дадена извадката на източника на данни, която ще бъде използвана в този урок. Щракнете върху връзката, за да изтеглите Sample_Data Pivot Chart
| Идентификатор на поръчката | Дата на поръчката | Име на продукта | Регион | Град | Количество | Обща цена |
|---|---|---|---|---|---|---|
| 1 | 03-01-2020 | Обикновени бисквитки | Северна | Ню Йорк | 33 | 444.66 |
| 2 | 04-02-2012 | Захарни бисквитки | Южна | Лима | 432 | 346.33 |
| 3 | 05-04-2018 | Вафли | Изток | Бостън | 33 | 32.54 |
| 4 | 06-05-2019 | Шоколад | West | Дъбова земя | 245 | 543.43 |
| 5 | 07-07-2020 | Сладолед | Северна | Чикаго | 324 | 223.56 |
| 7 | 09-09-2020 | Обикновени бисквитки | Изток | Вашингтон | 32 | 34.4 |
| 8 | 10-11-2020 | Захарни бисквитки | West | Сиатъл | 12 | 56.54 |
| 9 | 11-12-2017 | Вафли | Северна | Торонто | 323 | 878.54 |
| 10 | 12-14-2020 | Шоколад | Южна | Лима | 232 | 864.74 |
| 11 | 01-15-2020 | Сладолед | Изток | Бостън | 445 | 457.54 |
| 13 | 03-18-2018 | Бисквитки със сол | Северна | Ню Йорк | 5454 | 34546 |
| 14 | 04-18-2017 | Бисквитки със сирене | Южна | Лима | 5653 | 3456.34 |
| 15 | 05-19-2016 | Бисквитки със сол | Изток | Вашингтон | 4 | 74.4 |
| 16 | 06-20-2015 | Бисквитки със сирене | West | Дъбова земя | 545 | 876.67 |
Създаване на диаграма на завъртане
Има 2 начина за създаване на диаграма на въртене в Excel.
#1) Създаване от източник на данни
Можем да създадем диаграма директно от листа с данни, без да използваме въртяща се таблица.
За да постигнете това, следвайте следните стъпки.
#1) Изберете която и да е клетка в таблицата.
#2) Отидете на Вмъкване -> Pivot Chart
#3) Можете да изберете да създадете нов лист или да споменете диапазона на таблицата, в който искате да поставите диаграмата, под Съществуващ работен лист.
#4) Натиснете OK
Това ще създаде празна диаграма на въртене и свързаната с нея таблица на въртене. Можете да добавите желаните полета, за да генерирате отчет и диаграма.
#2) Създаване от PivotTable
Ако вече сте създали въртяща се таблица, можете да използвате същата, за да генерирате въртяща се диаграма. Създадохме примерна въртяща се таблица, както е показано по-долу.
За да създадете диаграма.
#1) Изберете която и да е клетка в PivotTable.
#2) Отидете на Insert-> Графика на завъртане
#3) Ще се появи списък с наличните диаграми, изберете желаната диаграма.
#4) Щракнете върху ОК.
Това ще генерира диаграма с данни, взети от таблицата на въртене. Примерът за диаграма на въртене е показан по-долу.
Забележка: Алтернативно можете да използвате бързия клавиш F11. Щракнете върху въртящата се таблица и натиснете F11 на клавиатурата.
Персонализиране на диаграмата
Можете да персонализирате диаграмата, като използвате иконата за + и иконата за боя в дясната част на диаграмата.
Бутон + - Той ви помага да добавяте или премахвате елементи на диаграмата, като заглавия, линии на решетката, легенди и др., и да определяте техните позиции.
Можете да добавите заглавието на диаграмата, да споменете заглавията на осите и т.н. Добавихме заглавието на диаграмата и заглавието на оста като пример.
Стил на диаграмата - Можете да промените стила и цвета на диаграмата, като щракнете върху иконата на четка за рисуване.
Можете също така да променяте цвета на диаграмата, както желаете, от раздела за цвят.
Препоръчителни диаграми
Excel ни предоставя препоръчителни диаграми Pivot, които ни позволяват бързо да изберем типа диаграма Pivot, който отговаря на вашите бизнес изисквания.
#1) Изберете таблицата с източника на данни.
#2) Отидете на Вмъкване -> Препоръчителни диаграми .
#3) Щракнете върху Препоръчителни диаграми.
#4) Щракнете върху диаграмата, която ви е необходима.
#5) Натиснете OK
Получените въртяща се таблица и диаграма ще бъдат създадени в нов лист и можете да ги персонализирате допълнително според нуждите.
Полета на диаграмата на завъртане
Той има 4 полета, както е показано по-долу.
1. Филтри: Полетата под това поле ни дават възможност да добавяме филтри за отчети.
2. Легенди (серия) : Полетата под това поле представляват заглавията на колоните в таблицата за завъртане.
3. Ос (категории): Това представлява редовете в таблицата Pivot Table. Тези полета са показани в лентата на оста на диаграмата.
4. Стойности: Използва се за показване на обобщените цифрови стойности.
Инструменти за диаграми на завъртане
Анализирайте: Има различни опции, които улесняват работата с диаграмата.
Име на графиката: Това е името на диаграмата. Използва се при писане на VBA код и също така е в панела за избор. Наличен е в Excel 2010 и по-нови версии.
Опции: Ще бъде показан диалоговият прозорец Опции на PivotTable, в който можете да зададете оформление & Формат, да покажете/скриете голямата сума, да зададете опции за сортиране, опции за показване и др.
Активно поле: Можете да промените името на колоната в таблицата. Например , Обща сума към Окончателна сума и т.н., и същите ще бъдат актуализирани в таблицата и диаграмата.
Разширяване на полето: Това се използва за автоматично разширяване на всички стойности.
Ако имате няколко полета, например Години, Квартали и Дата, вместо да разширявате всяко поотделно, можете да щракнете върху Разширяване на полето.
Свиване на полето: Това е противоположно на Разширяване на полето. Това ще свие разширените полета и ще представи компактна диаграма.
Разширяване на примера
Пример за срив
Забележка: Да предположим, че имате само едно поле в Редове, тогава като щракнете върху Разширяване на полето, ще получите диалогов прозорец с всички полета и можете да изберете желаното поле. Избраното поле ще бъде добавено към раздела Редове и диаграмата ще бъде актуализирана автоматично.
Вмъкване на резачка
Можете да вмъкнете слайсер в диаграмата точно както в таблицата с въртящи се елементи.
За да интегрирате слайсера с диаграма, следвайте следните стъпки.
- Щракнете върху диаграмата на въртене.
- Отидете в Анализиране на раздела -> Вмъкване на резба .
- В диалоговия прозорец Изберете полета трябва да създадете резетата.
- Натиснете OK
Това ще вмъкне полето за нарязване, както е показано по-долу. Видяхме как да използваме нарязване в предишния ни урок.
Вмъкване на времева линия
Можете да вмъкнете времева линия в диаграмата, както при таблицата с въртящи се елементи.
За да интегрирате Timeline с диаграмата, следвайте следните стъпки.
- Щракнете върху диаграмата на въртене.
- Отидете в Таб "Анализиране" -> Вмъкване на времева линия.
- Изберете необходимото поле Дата.
- Натиснете OK
Това ще вмъкне времевата линия, както е показано по-долу. Видяхме как да използваме времевата линия в предишния ни урок.
Резултатът, базиран на времевата линия, се актуализира както в таблицата Pivot, така и в диаграмата.
Свързване на филтъра
Можете да свързване на слайсера или времевата линия към няколко диаграми на въртене. Например, създадохме 2 таблици Pivot и 1 Slicer. Прилагате Slicer и към двете диаграми.
- Щракнете върху диаграмата на въртене, към която в момента не е свързан слайсерът.
- Отидете на Анализиране -> Връзка за филтриране
- Изберете устройството за нарязване, което искате да свържете.
- Кликнете върху ОК
Сега можете да работите и с двете диаграми с един слайсер.
Изчисления
Ако искате да добавите персонализирани формули, можете да го направите чрез полето за изчисления.
Пример:
#1) Изберете диаграмата Pivot, към която искате да добавите персонализираните формули.
#2) Отидете на Анализиране -> Полета ->Елементи -> Набори
#3) Изберете Изчислени полета.
#4) В полето Name (Име) въведете желаното от вас име.
#5) В полето Formula (Формула) добавете потребителската си формула. Ако давате 10% отстъпка от общата сума, можете да добавите формулата, както е показано по-долу.
#6) Таблицата на въртене, полетата на въртене и диаграмата ще бъдат съответно актуализирани.
Обновяване
При всяка промяна на стойностите в източника на данни щракнете където и да е върху диаграмата на въртене и щракнете с десния бутон на мишката и изберете Refresh (Обнови) или отидете в Analyze -> Refresh (Анализирай). Обновяването на таблицата на въртене също ще обнови диаграмата.
Промяна на източника на данни
Всеки път, когато добавите повече редове към източника на данни, диаграмата няма да приеме добавените редове, тъй като сме определили обхвата при създаването на диаграмата.
За да включите новите редове:
- Щракнете навсякъде върху диаграмата на завъртане.
- Отидете на Анализиране -> Промяна на източника на данни
- Ще се появи диалогов прозорец Change PivotTable Data Source и можете да въведете новия диапазон от данни.
- Кликнете върху ОК
Уверете се, че сте изпълнили горните стъпки за всички диаграми поотделно.
Clear
С помощта на Clear (Изчистване) можете да изчистите цялата диаграма Pivot Chart. Тя ще бъде празна диаграма и таблица.
- Кликнете върху диаграмата на завъртане
- Анализиране -> Изчистване -> Изчистване на всички
Можете също така да изтриете всички приложени филтри, като Анализиране -> Clear-> Изчистване на филтри
Диаграма на движението
След като създадете диаграма, можете да я преместите на желаното място.
Следвайте следните стъпки:
- Щракнете върху диаграмата на въртене.
- Отидете на Анализирайте -> Преместване Chart
- Изберете желаната опция от диалоговия прозорец:
- Нов лист: Листът ще бъде създаден автоматично и диаграмата ще бъде показана.
- Обект в: Можете да изберете един от наличните листове и диаграмата ще бъде преместена в избрания лист.
Списък на полетата: Можете да показвате/скривате панела Полета на PivotChart.
Бутони за полета: Можете да показвате/скривате полето на легендата, полето на оста, полето на стойността, филтъра на отчета и т.н. на диаграмата.
Дизайн
В този раздел са налични няколко опции за проектиране на диаграмата.
Добавяне на елемент от диаграмата: Това ни дава същите опции, които получихме, когато щракнахме върху бутона + до диаграмата на въртене. Те ни помагат да добавяме елементи към диаграмата, като заглавие, барда за грешки и др.
Бързо оформление: Можете да промените оформлението по подразбиране и да изберете едно от наличните предварително дефинирани оформления. Например, преместихме оформлението на региона в горната част, вместо в дясната страна.
Промяна на цветовете: Изберете различни цветове за диаграмата.
Стил на диаграмата: Изберете стил за вашата диаграма от тези налични диаграми.
Превключване на ред/колона: Можете лесно да превключвате редове и колони само с едно щракване на мишката и въртящата се таблица и диаграмата ще се актуализират автоматично.
Преди превключване на ред/колона
След превключване на ред/колона
Изберете Данни: Да предположим, че сте прекарали много време в оформяне на диаграма на въртене в съответствие със стандартите на вашата компания и всички ваши диаграми трябва да бъдат в един и същи формат. Тогава тази опция е полезна. Не можете директно да копирате диаграмата на въртене и да промените източника на данни. Трябва да се извършат няколко стъпки.
#1) Изберете желаната диаграма на въртене и копирайте областта на диаграмата.
#2) Отворете нова работна книга. Файл -> Нова работна книга
#3) Вмъкнете копираната диаграма. В лентата с менюта можете да забележите, че е написано Chart Tools (Инструменти за диаграми), а не PivotChart Tools (Инструменти за диаграми).
#4) Сега изберете областта на диаграмата и натиснете опцията Изрежи.
#5) Отидете в работната книга, в която искате да използвате тази диаграма.
#6) Забележка: Вече трябва да имате създадена таблица на въртене.
#7) Поставете диаграмата от стъпка 4.
#8) Отидете в Design present (Дизайн) под Chart Tools (Инструменти за диаграми). Щракнете върху Select Data Tab (Избор на данни).
#9) Щракнете върху която и да е клетка в таблицата с въртящи се елементи.
Ще бъде създадена диаграма на завъртане с данните, налични в новата таблица на завъртане, но форматът остава същият като преди. Можете да промените осите и легендата, както е необходимо за новата таблица.
Получената диаграма за новата въртяща се таблица е показана по-долу.
Промяна на типа диаграма: Можете да промените типа на диаграмата по подразбиране с желания тип, както е показано по-долу.
Диаграмата ще се актуализира автоматично въз основа на избора.
Кръгова диаграма
Бар диаграма
Формат
Те се използват основно за персонализирано форматиране на текста в диаграмата.
Текуща селекция: Това ще покаже всички елементи в таблицата и можете да изберете този, в който искате да промените стила на форматиране. Например, Ще изберем Chart Title и ще променим стила му.
#1) Изберете Chart Title (Заглавие на диаграма) от падащото меню.
#2) Щракнете върху Format Selection (Избор на формат).
#3) Format Chart Title ще се отвори в десния прозорец.
#4) Изберете цвят, стил, рамка и др. по ваше желание.
След няколко основни форматирания заглавието на диаграмата ще изглежда, както е показано по-долу.
Нулиране на стила: Това ще нулира всички промени и ще върне стила по подразбиране.
Вмъкване на форми: Можете да вмъквате фигури като линии, стрелки, както и текстово поле за по-добро обяснение.
Стил на формата: Можете да изберете различни стилове за областта на парцела. Изберете областта, на която искате да промените стила, и щракнете върху стила.
След като приложите стиловете към цялата диаграма, колоните и редовете са показани по-долу.
Организирайте: Ако има няколко диаграми на въртене и те се припокриват една с друга в тези опции.
Пренасяне напред
- Изберете диаграмата, която искате да изведете на преден план.
- Щракнете върху опцията Bring Forward, за да преместите диаграмата с една стъпка напред.
Донесете на фронта: Тази опция ще изведе диаграмата ви над всички останали диаграми.
Изпращане в обратна посока
- Изберете диаграмата, която искате да изпратите обратно.
- Щракнете върху опцията за изпращане назад, за да изпратите диаграмата едно ниво назад.
Изпращане към Назад: Това се използва за изпращане на избраната диаграма обратно към всички останали диаграми.
Панел за избор
Можете да решите видимостта на диаграмата, като използвате панела за избор. Тази страница ви показва всички налични диаграми и слайсери и можете да щракнете върху иконата на окото, за да решите дали този конкретен елемент трябва да бъде видим в работния лист или не.
Размер: Това се използва за персонализиране на височината на диаграмата на въртене, ширината, височината на скалата, ширината на скалата и т.н.
Често задавани въпроси
В #1) Как се създава диаграма на въртене в Excel?
Отговор: Има 2 начина за създаване на диаграми на въртене.
#1) Създаване от източник на данни
- Изберете която и да е клетка от таблицата на източника на данни.
- Отидете на Вмъкване -> Pivot Chart
- Изберете обхвата.
Това ще създаде празна таблица на въртене и диаграма на въртене.
#2) Създаване от PivotTable
Ако вече сте създали въртяща се таблица:
- Изберете която и да е клетка в таблицата Pivot.
- Отидете на Вмъкване -> Диаграма на завъртане
- Ще се появи списък с наличните диаграми, изберете желаната диаграма.
Това ще създаде диаграма с данни, свързани с таблицата за завъртане.
В #2) Защо използваме диаграма на въртене в Excel?
Отговор:
Използването на диаграми на въртене има много предимства:
- Той дава ефективен и лесен начин за представяне на данни в графичен вид.
- Можете лесно да обобщавате данни, като плъзгате желаните полета към някой от 4-те налични раздела на таблицата.
- Предоставя ефективен начин за промяна на необработени данни в организиран формат чрез манипулиране с лесно филтриране, подравняване, персонализиране, изчисления и др.
В #3) Как да форматирам диаграма на завъртане?
Отговор: Можете да форматирате диаграмата, като използвате различни опции, налични в раздела Инструменти за диаграмата на въртене. Той ви предоставя възможности за добавяне на нови полета, промяна на цвета, шрифта, фона и т.н., за да направите диаграмата си по-интерактивна и представителна. Щракнете навсякъде върху диаграмата на въртене, за да отворите раздела Инструменти.
Въпрос #4) Мога ли да добавя слайсер към диаграмите на завъртанията?
Отговор: Да, към диаграмите на въртене могат да се добавят слайсери и времеви линии. Това ще ни помогне да филтрираме едновременно диаграмата и свързаната таблица на въртене.
- Щракнете върху диаграмата на въртене.
- Отидете в Анализиране на раздела -> Вмъкване на резба .
- В диалоговия прозорец Изберете полетата, които искате да създадете.
- Натиснете OK
След това можете да добавите връзка с филтър, за да свържете един слайсер с няколко диаграми.
Заключение
В този урок научихме за диаграмите на въртене в Excel. Това е визуално представяне на таблица на въртене или източник на данни. То ни помага да разглеждаме обобщените данни в графичен формат с различни видове диаграми.
Налични са множество опции за филтриране, форматиране, персонализиране на диаграми и добавяне на различни оформления по ваше желание. Графиката с въртящи се оси в Excel е полезна при работа с огромно количество данни. Тя е много полезна по време на бизнес презентация с филтриране с едно щракване, филтриране по време, персонализирани изчисления и др.