Как да направите диаграма от въртяща се таблица. Excel диаграми

Оставете коментар 6,950

Опорната диаграма в Excel е подобна на обобщена таблица. Режимите на дисплея са малко по-различни. Програмата поставя подробна информация в централната таблица. В диаграма - суми или обобщения. Вместо редове и колони има редове и категории. Всеки ред е цветно кодиран и представлява свързани точки от данни. Категорията е една точка от всеки ред (по оста X).

Как да създадете централна диаграма в Excel от множество таблици

На различни листа от една книга има отчети за продажби в няколко магазина:

Те имат същата структура и един и същ начин на представяне на данни.

Преди да направим централна диаграма в Excel, нека добавим бутона „Съветници за обобщена таблица и диаграми“ към лентата с инструменти за бърз достъп. През "Office" отидете на "Опции на Excel" - "Настройки". Избираме "Всички команди". Намерете инструмента „Съветник за обобщени таблици и диаграми“. Кликнете върху „Добавяне“.

Следната икона ще се появи на панела:

Отидете на нов лист. Ние наричаме "Господаря":

След като щракнете върху бутона "Край", получаваме централна диаграма и таблица в следния вид:

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

Пивотна диаграма в Excel на базата на готова въртяща таблица

Изберете въртящата се таблица, като щракнете навсякъде с мишката. След това отидете на раздела "Вмъкване-хистограма".

Избиране на проста хистограма. Резултатът е диаграма с данни от обобщената таблица и филтърна област. Нека зададем филтър за стойностите в колоните - показване на продажбите за 1-во тримесечие на 2013 г., 2014 г., 2015 г.

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

Прозорецът Обхват на филтъра ви позволява да манипулирате обобщената таблица и диаграмата.

Сравнение на продажбите по тримесечия за анализирания период:

Анализ на продажбите в магазин "Серебрянка":

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

В първия пример комбинирахме няколко диапазона наведнъж в една диаграма. Но можете също да изградите графика въз основа на една основна таблица. Изберете го - отидете в раздела "Вмъкване".

Програмата ще създаде централна таблица и диаграма едновременно. Персонализирайте полетата - и анализирайте.

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

Можете да изградите въртяща се диаграма, базирана както на проста, така и на опорна таблица.

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

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

Изберете необходимата въртяща се таблица, като щракнете върху нея.

В раздела Вмъкванев група диаграмиизберете типа диаграма, която искате, например обикновена лентова диаграма, и щракнете ОК.

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

По подразбиране се създава въртяща се диаграма на същия лист като централната таблица. Можете да преместите обобщената диаграма на нов лист с помощта на командата Преместване на диаграматаот контекстното меню.

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

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

Обобщена диаграма, изградена върху съществуваща обобщена таблица, е тясно свързана с нея. Това не винаги е удобно, така че често има смисъл незабавно да се изгради обобщена диаграма въз основа на основната таблица. Това изисква:

Изберете необходимия диапазон от данни (или щракнете върху която и да е клетка в диапазона от клетки, съдържащи данни - тогава Excel автоматично ще замени цялата таблица в диапазона от данни);

В раздела Вмъкванев група таблициизберете раздел Пивотна масаи след това параграф Пивотна диаграма.

В отворения прозорец Създайте въртяща таблица и въртяща диаграмазадайте обхвата или източника на данни и местоположението на таблицата и диаграмата и щракнете ОК.

Excel ще създаде нова обобщена таблица и обобщена диаграма. Остава само да конфигурирате полетата и условията на централната таблица с помощта на прозореца Списък с полета на обобщена таблица... Всички промени ще бъдат отразени и в диаграмата.

Анализиране на данни с помощта на таблици с данни

За анализиране на зависимостта на резултата от различни набори от първоначални данни в Excel се използват таблици с данни. .

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

Избор на артикул Таблица с даннив списъка с команди Анализ"какво ако" в група Инструменти за обработка на даннив раздела Данни, можете да изградите или таблица с един параметър за една или повече функции, или таблица с два параметъра, но за една функция.

Трябва да се отбележи, че не можете да изтриете отделни клетки от таблицата с данни, можете да изчистите цялата таблица като цяло. За да направите това, маркирайте изчислените стойности и натиснете клавиша Изтрий.

Таблица с данни с един параметър за една функция

Принцип на използване Таблици с данние както следва:

се задават начални данни на проблема, включително първоначалната стойност аргумент-параметър;

задава се диапазон от стойности на параметрите (под формата на колона или ред);

се разпределя пространство за поставяне на стойностите на функцията от дадените стойности на аргумента (колоната вдясно или редът по-долу);

е посочена формула, която определя зависимостта на функцията от изходните данни, включително от параметъра-аргумент.

За да получите желаните стойности, изберете блока на таблицата, съдържащ стойностите на посочения диапазон от параметри и формулата за изчисление, и в раздела Даннив група Работа с данниизберете отбор Анализ"какво ако" и след това изберете елемента Таблици с данни... В отворения диалогов прозорец Таблица с данние необходимо да се посочи в коя клетка да се заменят стойностите на параметъра-аргумент от диапазона от стойности на параметъра. Очевидно трябва да посочите адреса на клетката, съдържаща стойността на аргумента на параметъра. Таблицата с данни осигурява последователен избор на елементи от обхвата на стойностите на аргументите и заместването на всеки от тях в клетката с параметри, след което таблицата се преизчислява и стойността на функцията, определена от формулата, се въвежда в пространството, определено за го до съответната стойност на аргумента:

за всяка клетка в колоната към съседната клетка в реда, т.е. ред по ред ;

за всяка клетка в реда към съседната клетка в колоната, т.е. по колони .

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

Таблица с данни с един параметър за множество функции

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

Таблица с данни с два параметъра

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

За да се получат необходимите стойности на функцията, се избира блок на таблица, съдържащ клетка с формула и клетки със стойности на посочените диапазони от параметри. След това се отваря прозорец Таблица с даннив списъка с команди Анализ"какво ако" в група Работа с даннив раздела Данни. В прозореца трябва да посочите в коя клетка да замените стойностите на параметрите от диапазона от стойности на параметрите по колони и в коя - по редове. Очевидно трябва да посочите клетките, където се намират първоначалните стойности на променливия аргумент на изчислената функция.

Резултатите от изчислението след изпълнение на командата ще бъдат поставени в избрания блок от клетки в пресечната точка на колона и ред за всички стойности на аргументи от посочените диапазони.

Сортиране и групиране на елементи от централната таблица.

Форматиране на централни таблици.

Изчисления в централни таблици.

Работа с въртяща се маса.

Създайте въртяща се таблица въз основа на списъчни данни.

Когато създавате въртяща таблица, можете да използвате един от четирите типа източници на данни: списък с данни на Microsoft Excel, външен източник на данни, множество диапазони за консолидиране (отделни списъци на Microsoft Excel) и друга опорна таблица.

Нека създадем обобщена таблица въз основа на списък Поръчки на стокикниги Продажба на стоки(виж фиг. 4.1).

Ориз. 4.1. Списък Поръчки на стоки

За да създадете обобщена таблица въз основа на данните от списъка, трябва да следвате тези стъпки.

1. Поставете показалеца на клетката някъде в списъка, въз основа на който ще бъде създадена централната таблица.

2.Използване на менюто Данни, екипи Пивотни таблициизвикайте съветника за създаване на въртеливи таблици и диаграми (вижте фиг. 4.2).

Ориз. 4.2. Първа стъпка от съветника за обобщена таблица и диаграма

3. На първата стъпка трябва да посочите типа източник на данни - Създайте таблица въз основа на данни в списък или база данни на Excel,и вида на генерирания отчет - въртяща се маса.

4. На втората стъпка на съветника трябва да посочите абсолютния адрес на диапазона от клетки на изходната таблица, за която е създадена обобщената таблица, тоест списъка, въз основа на който е създадена обобщената таблица.

Ако преди избор на отбор Пивотна масасте избрали само една клетка в този списък, тогава съветникът ще попълни правилно това поле в диалоговия прозорец.

5. В последната стъпка на съветника трябва да посочите мястото, където искате да поставите централната таблица.

Най-сигурният начин да създадете таблица на нов лист е да зададете радио бутона "Поставете масата в..."На "Нов лист"... В противен случай този превключвател трябва да бъде настроен на "Съществуващ лист"и посочете диапазона (или абсолютния адрес на първата клетка от създаваната таблица) на текущия лист или всеки друг съществуващ лист.

6. Когато съветникът приключи, работният лист ще покаже:

§ празно оформление на таблицата,

§ списък с възможни полета на централната таблица,

§ лента с инструменти Пивотни таблици(виж фиг. 4.3).

Ориз. 4.3. Създайте въртяща се таблица.

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

Областите, които се използват за посочване на местоположението на полетата в обобщената таблица, се извикват оси(ос). Разпределете оста на страницата, оси на колони и редове.

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

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

Продължавайки с нашия пример (виж фиг. 4.1), плъзгайки полето Държава получателНа ос на колоната,поле КатегорияНа линейна оси полето Цената на поръчкитекъм региона елементи от данни, получаваме централната таблица, показана на фигура 4.4.

След като първоначалната структура на таблицата бъде създадена, можете да я реорганизирате въз основа на спешната нужда от анализ на данните. Реорганизирането на обобщена таблица означава промяна на ориентацията или позицията на едно или повече полета. Промяната на позицията на таблицата се използва за преглед на данните от различен ъгъл. В оригинала се извиква основната таблица Пивотна маса(въртяща се маса). Именно възможността за промяна на ориентацията на таблицата, като например транспониране на заглавия на колони в заглавия на редове (вижте Фигура 4.5) и обратно, направи централните таблици мощен аналитичен инструмент.

Ориз. 4.4. Обобщена таблица въз основа на списъчни данни Поръчки на стоки

Ориз. 4.5. Транспониране на поле Държава получателкъм заглавките на колоните

Можете да местите полетата на въртящата се таблица, като плъзнете заглавията на колоните с мишката. Като алтернатива можете напълно да промените цялото оформление на обобщената таблица с помощта на командата майстор, меню Пивотна масаленти с инструменти Пивотни таблиции ключа Оформление... Можете също да местите заглавките на полето в отворения диалогов прозорец (вижте фиг. 4.6) с помощта на мишката.

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

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

Ориз. 4.6. Прозорец за промяна на оформлението на съветника за обобщена таблица

Елементите, поставени върху оста на страницата, се показват само един по един. Чрез алтернативно избиране на елементи в списъка по оста на страницата, можем да видим отрезки от данни за всяка стойност на полето, поставена върху оста на страницата.

Стойностите на полетата по оста на страницата могат да се показват на отделни листове. Дори ако въртящата се таблица съдържа ос на страницата, цялата таблица се съхранява на един лист от работната книга. Екип Показване на страницина лентата с инструменти Пивотни таблици(бутон Пивотна маса) създава серия от свързани таблици на отделни листове, всяка от които показва един елемент от полето на страницата. На всеки лист се присвоява името на показания елемент на полето.

Основната таблица не се актуализира автоматично, въпреки че е свързана с оригиналните данни. За да актуализирате централната таблица, трябва да изберете която и да е клетка в нея и след това да я използвате в менюто Данникоманда Актуализирайте данните(има подобна команда в контекстното меню или можете да използвате едноименния бутон в лентата с инструменти Пивотни таблици.

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

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

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

За числови полета, поставени в областта с данни на обобщена таблица, Excel прилага функцията Сума, а за всякакви нечислови полета - функцията Брой стойности.

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

1. Поставете показалеца на клетка върху която и да е клетка в областта на данните.

2. Използване на командата Параметри на полетона лентата с инструменти Пивотни таблици(бутон Пивотна маса), отворете диалогов прозорец (виж фиг. 4.7).

3. На полето Операцияот отворения диалогов прозорец, изберете желаната функция.

Можете да приложите произволен брой обобщаващи функции към поле, поставено в областта на данните, за това трябва да продължите както следва.

1. Плъзнете необходимия брой пъти бутона на това поле от панела Списък с полета на обобщена таблица(виж Фигура 4.3) в областта на данните. Ако горният панел не се показва на екрана, използвайте клавиша Показване на списък с полеталенти с инструменти Пивотни таблици.

Ориз. 4.7. Диалогов прозорец Изчисляване на поле на обобщена таблица

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

3. Повторете втората точка толкова пъти, колкото е необходимо.

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

Ориз. 4.8. Използване на обобщаващи функции Сумаи количество
за терена Цената на поръчката

В допълнение към стандартните функции за обобщение Сума, Брой стойности, Средното, Максимум, Минимум, Работетеи др. Excel ви позволява да използвате допълнителни изчисления. Например, в клетка в областта на данните на обобщена таблица, можете да покажете частта от общата сума на реда или колоната, в която се намира клетката, частта от общата сума.

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

Ориз. 4.9. Използване на разширени изчисления в обобщени таблици

Excel ви позволява да създавате свои собствени изчислени полета и членове. Изчислено поле – това е ново поле, получено чрез операции (главно аритметични преобразувания) върху съществуващи полета на централната таблица.

За да създадете свое собствено изчислено поле, продължете както следва.

1. Поставете показалеца на клетка върху която и да е клетка в въртящата се таблица.

2. Използване на командата Формули– Изчислено полена лентата с инструменти Пивотни таблици(бутон Пивотна маса) отворете диалогов прозорец Вмъкнете изчислено поле(виж фиг. 4.10).

3. На полето имевъведете името на създаденото поле.

4. На полето Формуласъздайте изчислена формула, използвайки аритметични операции, приложени към полетата на централната таблица. Полетата на таблицата се избират от списъка Полещракнете двукратно или клавиша Добавяне на поле.

За да изтриете изчислено поле, трябва да поставите показалеца на клетката някъде в централната таблица; след това чрез извикване на диалоговия прозорец Вмъкнете изчислено поле(меню Формула, команда Изчислено поле), в свит списък имеизберете името на полето за изтриване и натиснете клавиша Изтрий.

Промяната на формулата на изчислено поле се извършва по същия начин, само след като изберете името на полето, трябва да промените формулата в реда Формулаи натиснете клавиша Промяна.

Ориз. 4.10. Диалогов прозорец Вмъкнете изчислено поле

В допълнение към създаването на изчислени полета, Excel ви позволява да създавате изчислени членове в полета с данни на централната таблица, които вече съществуват. Изчислен член – това е нов елемент в съществуващо поле, получен чрез манипулиране на други елементи от това поле.

За да създадете изчислен член, продължете както следва.

1. Поставете показалеца на клетката върху името на полето или който и да е елемент от това поле върху оста на редовете или оста на колоните.

2. Използване на командата Формули– Изчислен обектна лентата с инструменти Пивотни таблици(бутон Пивотна маса), отворете диалогов прозорец Вмъкване на изчислен член в...(виж фиг. 4.11).

3. На полето имена горния диалогов прозорец се добавя името на новия елемент и в полето Формула- посочете формулата за нейното изчисление. Елементите се добавят към формулата чрез двукратно щракване в полето Елементитеили чрез натискане на бутона Добавете артикул.

Можете да изтриете или промените изчислен обект, като използвате същия диалогов прозорец Вмъкване на изчислен обект в...като изберете този обект в списъка имеи натискане на съответния клавиш Изтрийили Промяна, тогава Добре.

Ориз. 4.11. Добавете изчислен елемент към поле Категория

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

За разлика от обикновените таблици, обобщените таблици нямат условно форматиране и форматите на границите не се запазват, когато таблицата се реорганизира.

Excel предлага над 20 автоматични формата на централна таблица, които се избират с помощта на командата Формат на отчета, в лентата с инструменти Пивотни таблици, меню Пивотна маса.

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

1. Поставете показалеца на клетка върху която и да е клетка от даденото поле в областта на данните.

2. В лентата с инструменти Пивотни таблици, меню Пивотна маса, изберете отбор Параметри на полето.

3. В диалоговия прозорец Изчисление на полето на обобщената таблица (вижте фиг. 4.7) натиснете клавиша Форматиране.

4. В прозореца, който се отваря Формат на клеткатазадайте необходимия числов формат.

Етикетите на външните полета могат да бъдат центрирани спрямо етикетите на вътрешните полета. Например, на фигура 4.12, заглавките на полето Държава получателцентрирано вертикално по отношение на заглавките на полето Категория, надпис Австрия Общо, Бразилия Общо центрирано хоризонтално. За да форматирате обобщената таблица по този начин, трябва да влезете в диалоговия прозорец Опции за въртяща се таблица(команда Параметри на таблицатаменю Пивотна маса, лента с инструменти Пивотни таблици), активирайте квадратчето за отметка Обединяване на заглавни клетки.

Ориз. 4.12. Центриране на етикетите на външните полета спрямо
към етикетите на вътрешните полета

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

Структурното подчертаване е деактивирано по подразбиране; за да използвате този метод на форматиране, трябва да направите следното.

1.Използване на командата Маркирайте – Разрешаване на избор, меню Пивотна масана лентата с инструменти Пивотни таблицинаправи достъпно структурирано меню за избор.

2. В структурирано меню за избор, което се отваря, изберете елементите за избор: Маркиране - Само заглавия, Само данни, Данни и Заглавки.

Например на фигура 4.12 данните и заглавията на сумите по държави са подчертани ( Австрия Общо,Бразилия Общои др.).

При използване на нормалната команда Сортиранеменю Данни Excel сортира всички екземпляри на елементите на полето и поддържа този ред на сортиране при конвертиране на таблицата.

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

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

Този ред на сортиране има предимство пред нормалното сортиране и се нарича автоматично сортиране. За да използвате автоматично сортиране, трябва да направите следното.

1. Изберете клетката на всеки елемент от полето или бутона на полето, което трябва да бъде сортирано (в разглеждания пример на Фигура 4.13 - полето Категория).

2. Натиснете бутона Параметри на полетона лентата с инструменти Пивотни таблициили изберете подобна команда от менюто Пивотна маса.

3. В отворения диалогов прозорец Изчисляване на поле на обобщена таблица(виж фиг. 4.7) натиснете бутона Освен това.

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

Ориз. 4.14. Настройка на автоматично сортиране с помощта на диалогов прозорец
Разширени опции за полета на обобщена таблица

В този пример основата за сортиране на полето Категория ще бъдат стойностите на полето Сума по поле Стойност на поръчката.

Използвайки същия диалогов прозорец (вижте Фигура 4.14), можете да покажете само няколко от най-големите или най-малките елементи на полето въз основа на стойностите в областта на данните на обобщената таблица. За да направите това, като изберете клетката на елемента от необходимото поле, отворете диалоговия прозорец, както е описано по-рано Разширени опции за полета на обобщена таблица, включи Топ десет автоматичен дисплейи задайте броя на най-големите или най-малките елементи за показване.

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

Освен това, ако е необходимо, Excel ви позволява да групирате:

§ избрани елементи на полето по оста на ред или колона;

§ числови елементи на полета, разположени по оста на редове или колони;

§ по периодите от време на полето за дата, позиционирано върху оста на ред или колона.

За да групирате избраните елементи, трябва:

1) изберете елементите на полетата, разположени върху осите, които трябва да бъдат групирани;

2) изберете отбор Групав подменюто Група и структура, меню Данни.

В резултат на това ще бъде създадено ново поле, в което избраните елементи ще бъдат групирани в група с име Група 1(виж фиг. 4.15).

Ориз. 4.15. Създаване на група от елементи Сладкарски изделия,
Напиткии Плодове

Можете да скриете членовете на групата, като щракнете двукратно върху името на групата ( Група 1); за да покажете отново елементите на групата на екрана, трябва да щракнете двукратно върху заглавката на групата отново. Освен това, за да скриете или покажете групови елементи, използвайте командите Скриване на подробностии Покажи детайлименю Данни, подменю Група и структура.

Можете напълно да премахнете полето, съдържащо елементите на групата от таблицата, като плъзнете заглавката на полето извън таблицата, на Фигура 4.15 това поле Категория.

Ако е необходимо, можете да промените името на групата, като промените стойността на всяка клетка, съдържаща името на тази група. В примера, показан на Фигура 4.15, това е клетката A3.

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

Ориз. 4.16. Групиране на елементи от числово поле

Елементите се групират по времеви диапазони, ако поле на оста на ред или страница съдържа данни от типа Време за среща... Този тип данни се групират подобно на числовите елементи, само в изхода на екрана, в резултат на използването на командата Група, диалогов прозорец Групиране(виж фиг. 4.17) е необходимо да се изберат интервалите от време за групирането от секунда до година.

Ориз. 4.17. Групиране на полета за дата

За да изтриете всички групи и да върнете полето към негрупираното, трябва да поставите показалеца на клетка върху всеки групиран елемент и да изберете командата Разгрупиранев менюто Данни, подменю Група и структура.

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

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

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

В допълнение към такива елементи на обикновените диаграми на Microsoft Excel като серии, стойности, оси, централните диаграми имат специализирани елементи:

§ поле на страницатаизползва се за филтриране на данни за конкретен елемент, съответства на оста на страницата на централната таблица;

§ поле за даннипредоставя стойности за сравнение или модификация, съответства на областта с данни на централната таблица, в зависимост от източника на данни, в това поле можете да промените функцията за обобщение;

§ ред поле, който отразява възможните серии, елементите в полето съдържат отделни серии от данни, съответства на оста на колоната на въртящата се таблица.

Полетата на страницата се показват в горния ляв ъгъл на диаграмата, полето за категория съответства на оста на категорията, а полето за сериите съответства на оста на стойността. В диаграмата, показана на Фигура 4.18, в полето на реда е поставено поле Държава получател, в полето категория - полето Категория, в полето за данни - Сума в полето Цена на поръчката,полето за страници е оставено празно.

Ориз. 4.18. Пивотна диаграма

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

Въпроси за самоконтрол.

1. Дефинирайте въртяща се таблица.

2. Обяснете термините, използвани за създаване на централни таблици: изходни данни, крайна функция, елемент на поле, ос на редове, ос на колони, ос на страница, област с данни.

3. Особености на работа с елементи на поле, поставени върху оста на страницата.

4. Какво се разбира под реорганизацията на пивотната таблица, какви са начините за нейното изпълнение?

5. Обновяване на въртящата се таблица.

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

7. Промяна на сумарните функции на полетата, поставени в областта на данните.

8. Създайте изчислени полета и изчислени членове.

9. Характеристики на форматиране на централни таблици.

10. Избройте елементите на структурната селекция на осевите таблици.

11. Автоматично сортиране на елементи от полето на основната таблица.

12. Показване на няколко от най-големите или най-малките елементи на поле на обобщена таблица.

13. Групиране на избрани елементи на полето по оста на редовете или по оста на колоните.

14. Групиране на полетата на обобщените таблици по времеви диапазони.

15. Характеристики на изграждане на централни диаграми.

16. Специални елементи на опорни диаграми и тяхното съответствие с елементи на свързани опорни таблици.

Въпроси и задачи за самостоятелна работа.

1. Създайте с помощта на командата Резултати, структурата на таблицата със списък Поръчки на стокикниги Продажба на стокикойто отразява продажбите за всяка продуктова категория за всички държави. Държава получателдо зоната на линията, преди полето Категория.

2. Поставете полето Държава получателкъм зоната на линията и полето Категория- в зоната на колоните.

3. Сравнете данни в обобщена таблица и в структурирана таблица, която съдържа междинни суми. За по-голяма яснота използвайте валутния формат Кой от видовете обобщени таблици напълно съвпада със структурата на таблицата с помощта на междинни суми.

4. Поставете информация за поръчки за различни категории стоки на отделни листове от книгата.

5. Към списъка Поръчки на стокикниги Продажба на стокидобавете 20 000 000 към първата поръчка за сладкарски изделия, поръчани в Австрия.

Върнете се към централната таблица и опреснете данните. Проверете промените си.

Изтрийте добавените 20 000 000 за първата поръчка в Австрия (сладкарски изделия) и актуализирайте отново централната таблица.

6. Създайте нова обобщена таблица, която дефинира броя на поръчките за всяка страна. Поставете върху оста на линията Държава получател, а в областта на данните полето Категория... Обърнете внимание на функцията за обобщение по подразбиране.

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

8. Използване на свит списък Данни,показва само сумата за поле Цената на поръчките.

9. Добавете изчислено поле към обобщената таблица Намалена цена, изчислено като намаление на общите разходи с 15%.

11. За получената обобщена таблица използвайте командите за автоматично форматиране, за да зададете формата Таблица 10 , и тогава Класическа шарнирна маса.

12. Добавете към съществуващата обобщена таблица поле, в което се изчислява общата цена на артикула Цена * Количество * (1 – Отстъпка) . Задайте паричния формат за това поле, UAH, само цялата част. Сума по брой единици стокипремахнете от областта с данни.

13. Според списъка Продажба на стокисъздайте въртяща се таблица, като поставите поле върху оста на реда Държава получател, по оста на колоната - поле Дата на подаване на поръчки, в областта на данните - поле Цената на поръчката... Елементи на полето Дата на подаване на поръчкигрупа по година и тримесечие.

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

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

16. В създадената диаграма плъзнете полето Дата на подаване на поръчкатав полето страници и вижте зависимостите за отделни месеци и години.

Лабораторни задачи 4.1.

1. Копирайте файла във вашата папка Лаборатория 4... Изпълнете всички следващи задачи от лабораторната работа въз основа на списъка Изпращане на стокикнижен файл Лаборатория 4.xlsсъздаване на независим отчет за всяка работа.

2. На нов лист с име Обобщена таблица_1, създайте осева таблица със структура, подобна на финалната таблица на четвърта задача от лабораторна работа 3.1.

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

4. Преименувайте листа, съдържащ обобщената таблица за всички методи за доставка на Всички методи за доставка.

5. Към централната маса, разположена на листа Всички методи за доставка, добавете поле, което отчита броя на поръчките за всяка продуктова категория от позицията на служителя. Дайте име на новото поле Броят на поръчките.

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

7. Добавете изчислено поле към същата въртяща се таблица, в която общата обща стойност на поръчката ще бъде изчислена като произведение на цената и броя на единиците на поръчаните стоки.

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

9. На полето Категория стокидобавете изчислен елемент Вегетариански продукти, което ще отчита броя на поръчките за всички категории стоки с изключение Рибни продукти, Млечни продукти, Месо и домашни птици.

Лабораторни задачи 4.2.

1. Използване на списък във файл Лаборатория 4_2.xls, създайте централна диаграма, показваща зависимостта на средната цена на доставяния продукт от неговата категория и града на доставка . Показване на данни в диаграмата само за поръчки, доставени по въздух.

2. Преименувайте листа, на който е създадена централната таблица, свързана с диаграмата Обобщена таблица_1.

3. Използване на данни от работния лист Обобщена таблица_1, показва на отделен лист подробна информация за цените на рибните продукти, изпратени до Ню Орлиънс по въздух.

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

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

6. Преименувайте листа на Мениджъри.

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

8. Премахнете от таблицата полето, съдържащо елементите на групата Мениджъри.

9. Създайте въртяща се диаграма въз основа на данните от обобщената таблица, разположена на страницата Мениджъри.

10. На нов лист Кварталисъздайте въртяща диаграма (създайте диаграма не въз основа на съществуващ отчет, а независим), показваща броя на поръчките за всяка продуктова категория на тримесечна база, показвайте само информация за 2003 г. в диаграмата.

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

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

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

Когато създавате диаграма за обобщена таблица, не я попълвате с отделни части от данни, представени от отделни клетки. Вместо това използвате цялото оформление на обобщената таблица, за да изградите диаграмата си. PivotChart е диаграма, която използва обекта PivotLayout на обобщената таблица като вход. Използването на обект PivotLayout ви позволява интерактивно да добавяте, премахвате, филтрирате и актуализирате полета с данни в обобщена диаграма, точно както бихте направили в обобщена таблица. В резултат на всички тези стъпки ще получите интерактивно графично представяне на данните от обобщената таблица.

Създаване на централни диаграми

За да се запознаете с това как да създадете диаграма за обикновена въртяща се таблица, погледнете централната таблица, показана на фиг. 1. Съдържа информация за размера на продажбите за всяка търговска зона. Поле Бизнес сегментразположен в областта на филтъра на отчета, което ви позволява да анализирате данни по бизнес области на компанията.

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

Както се вижда на фиг. 3, след като изберете типа диаграма, тя ще се покаже в прозореца на програмата.

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

Диаграмата показва бутоните на полетата на въртящата се таблица (виж фиг. 3). Тези бутони са разположени на обобщената диаграма, оцветени в сиво и имат падащи списъци. Използвайте тези бутони, за да пренаредите диаграмата или да приложите филтри към основната обобщена таблица. Бутоните на полето на обобщена таблица се появяват, когато отпечатате обобщена таблица. Ако искате да скриете бутоните, които се показват в областта на обобщената диаграма, просто ги премахнете. Щракнете върху централната диаграма и отидете на контекстния раздел Анализирам... Щракнете върху бутона на падащото меню Бутони за полетаза да скриете някои или всички бутони на полето на обобщената диаграма (Фигура 4).

Ориз. 4. Щракнете върху Скрий всичкотака че бутоните на полето да не развалят външния вид на диаграмата при печат

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

Имате диаграма, която е визуално представяне на числовите данни в обобщена таблица. Освен това, тъй като такава диаграма се основава на обобщена таблица, всички промени в обобщената диаграма незабавно ще задействат съответните промени в самата обобщена таблица и обратно. Например, на фиг. 6 показва, че след добавяне на полетата към централната таблица региондиаграмата ще покаже друго измерение, показващо разпределението на продажбите по региони. Моля, обърнете внимание: на диаграмата, показана на фиг. 6, не се показват междинни суми. Excel напълно игнорира клетки с всякакви обобщени данни при създаване на обобщени диаграми. Ако изберете Бизнес сегмент като филтър на полето на страницата, той ще бъде приложен не само към обобщената таблица, но и към обобщената диаграма. Това поведение на програмата се основава на факта, че въртящата се диаграма и въртящата таблица са създадени въз основа на данни, взети от един и същ кеш. По този начин, ако добавите нова информация към източника на данни и след това опресните обобщената таблица, обобщената диаграма ще бъде обновена автоматично.

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

Правила за работа с централни диаграми

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

Не всички полета в обобщена таблица се показват в обобщена диаграма.Една от най-често срещаните грешки, които хората правят при създаването на обобщени таблици и диаграми, е предположението, че полетата от областта на колоните се начертават по оста X в обобщената диаграма. 7, има много удобен формат за анализ на данни. Полето е поставено в областта на колоните на таблицата Фискален период, а в областта на линията - полето регион... Тази структура от данни е най-подходяща за работа с централни таблици.

Да приемем, че искате да създадете диаграма въз основа на обобщена таблица. Интуитивно си представяте, че фискалните периоди ще бъдат нанесени по оста X, а бизнес сегментите ще бъдат посочени по оста Y. Но, както е показано на фиг. 8, в получената централна диаграма, стойностите на полето са нанесени по оста X регион, а по оста Y са стойностите на полето Фискален период.

Така че защо структурата на централната таблица не се запазва, когато е представена като въртяща се диаграма? Да, защото областите в въртящата се диаграма зависят от строго програмираните области на въртящата се таблица:

Y-ос.Съответства на областта на колоната на обобщената таблица и образува вертикалната ос на обобщената диаграма.
ос X.Съответства на областта на реда на обобщената таблица и образува хоризонталната ос на обобщената диаграма.

Имайки предвид тази информация, погледнете още веднъж фиг. 7. В централна диаграма полето Фискален периодще се показва по оста y, защото се намира в областта на колоните. В същото време полето регионще бъде позициониран върху оста x, тъй като е в областта на реда. Сега да предположим, че областта на редовете на обобщената таблица съдържа фискални периоди и регионите са представени в областта на колоните. Промяната на структурата ще генерира нова централна диаграма (Фигура 9).

Ограничения за форматиране за обобщени диаграми в Excel 2013.В наследените версии на Excel (преди Excel 2007) потребителите избягваха да използват обобщени диаграми, тъй като имаха много ограничения за форматиране. Не беше възможно да се преместят или променят ключови елементи на обобщената диаграма и ако оригиналната обобщена таблица е променена, форматирането на диаграмата се губи. Също така беше невъзможно да се прилагат определени видове диаграми. Поради тези ограничения много потребители разглеждат обобщените диаграми като тромави и непрактични.

Започвайки от Excel 2007, възможностите на обобщените диаграми са значително подобрени. Потребителите вече могат да форматират почти всеки компонент. В допълнение към това обобщените диаграми в Excel 2007 вече не губят форматиране, когато промените обобщените таблици, на които са базирани. Нещо повече, както вече знаете, обобщените диаграми вече са на същия работен лист като оригиналната обобщена таблица. Има допълнителни подобрения в Excel 2013 (бутони за полета и резци).

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

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

Алтернатива на въртящите се диаграми

Има две причини, поради които трябва да имате алтернатива на обобщените диаграми. Първо, не искате да се справяте с допълнителните разходи, свързани с използването на обобщени диаграми. Второ, искате да избегнете определени ограничения за форматиране, присъщи на обобщените диаграми. Обобщените таблици понякога се използват само за обобщаване и преглед на данни при изготвяне на диаграма. В такива ситуации не е необходимо да съхранявате оригиналните данни и да заделяте памет и място на твърдия диск за кеша на централната таблица. В примера на фиг. 10 показва обобщена таблица, показваща тримесечните отчети за всяка услуга.

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

Метод 1. Преобразуване на обобщената таблица в статични стойности.След като създадете основна таблица и настроите нейната структура, изберете цялата таблица и я копирайте в клипборда (за да изберете цялата таблица, можете да използвате клавишната комбинация Ctrl + Shift + *). Отидете в раздела У домаи щракнете върху бутона Вмъкване и след това изберете командата от падащото меню Поставете стойности... Това ще изтрие обобщената таблица и ще я замени със статични стойности, базирани на последното състояние на обобщената таблица. Получените стойности стават основа за създадената впоследствие диаграма. Тази техника се използва за премахване на интерактивни елементи от обобщена таблица. По този начин вие преобразувате централната таблица в стандартна таблица и създавате не въртяща се диаграма, а обикновена диаграма, която не може да бъде филтрирана и реорганизирана. Тази забележка се отнася и за методи 2 и 3.

Метод 2. Изтриване на оригиналната централна таблица.Ако вече сте създали централна диаграма, можете да я конвертирате в обикновена диаграма. За да направите това, просто изтрийте оригиналната обобщена таблица, на която се основава обобщената диаграма. Маркирайте цялата въртяща се таблица и натиснете клавиша ... Не забравяйте, че този метод, за разлика от предишния, дори не ви позволява да работите с първоначалните статични стойности, въз основа на които се изгражда въртящата диаграма. С други думи, ако бъдете помолени да покажете необработените данни на диаграмата, няма да можете да го направите. Ако сте изправени пред ситуация, в която диаграмата няма изходни данни, покажете таблицата с данни на диаграмата. Таблицата с данни ви позволява да видите данните, показани на самата диаграма.

Метод 3. Създаване на картина на базата на централна таблица.Може да изглежда странно за мнозина, че самата идея за представяне на централна таблица под формата на фигура може да изглежда странна, но това е напълно адекватен начин за прехвърляне на анализираните данни, който не изисква използването на специални мерки за защита . В допълнение към малкия размер на крайния файл, винаги имате пълен контрол върху това каква информация може да види потребителят на екрана. За да използвате този метод, копирайте цялата централна таблица в клипборда. Отворете нова работна книга, щракнете с десния бутон навсякъде върху нея и изберете Залепете специално... Посочете необходимия формат на вмъкнатите данни (напр. Рисуване). Моментна снимка на обобщената диаграма се вмъква в работния лист.

Метод 4: Използвайте клетките, свързани с обобщената таблица, като източник на данни на обобщената диаграма.Много потребители на Excel, които са далеч от създаването на обобщени диаграми, се страхуват от ограниченията на техните инструменти за форматиране на диаграми и управление на данни. Много често потребителите отказват да използват обобщени таблици, защото не знаят как да заобиколят съществуващите ограничения. Въпреки това, ако трябва да запазите способността да филтрирате данни и да създавате списъци с топ 10, можете да свържете стандартна диаграма с обобщена таблица, без да се налага да създавате самата обобщена таблица.

В централната таблица, показана на фиг. Представени са 11, 10 най-добри пазари за продажби, определени от индикатора Период на продажби (в часове)... Имайте предвид, че областта за филтриране на отчета ви позволява да филтрирате данни по бизнес.

Ориз. 11. Пивотната таблица ви позволява да филтрирате данните от първите десет пазара, определени по период и обем на продажби, както и по бизнес

Да предположим, че искате да начертаете показаните данни, за да покажете връзката между времето за изпълнение и приходите. В същото време е необходимо да се запази възможността за филтриране на първите десет пазара на продажби. За съжаление, въртящата диаграма не е приложима в този случай. По принцип е невъзможно да се представи обобщена таблица под формата на графика. Методи 1–3 също не са подходящи, тъй като всички интерактивни елементи се губят. Какво е решението? Използвайте клетките около обобщената таблица, за да персонализирате връзката между изходните данни и диаграмата, която се генерира от тях. С други думи, трябва да създадете блок от данни, който ще служи като източник на информация за обикновена диаграма. Този блок от данни е свързан с елементи от централната таблица. Това означава, че ако обобщената таблица се промени, оригиналният блок от данни на диаграмата също се променя.

Поставете курсора в клетката до обобщената таблица, както е показано на фиг. 12. Задайте връзка за първия елемент от данни, като посочите в него диапазона, който ще бъде използван в стандартната диаграма (ако, когато се опитате да се обърнете към клетка от основната таблица, функцията GET DATA.PIVOTTABLE.TABLES " изскача" във вашата формула, тогава няма да можете да " плъзнете "формулата. За да преодолеете тази трудност, прочетете бележката).

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

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

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

Ориз. 14. Тази техника ви позволява да използвате функционалността на централната таблица, но запазва възможността за свободно форматиране на диаграмата.

Бележка по книгата на Джелен, Александър. ... Глава 6.

ВИЖТЕ УКРАИНСКАТА ВЕРСИЯ НА СТАТИЯТА

В тази статия ще разгледаме техниките за създаване и използване на обобщена диаграма.

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

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

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

1. Изберете основната таблица, от която се нуждаете, като щракнете върху нея;

2. В раздела Вмъкванев група диаграмиизберете желания тип диаграма.

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

Забележете прозореца Обхват на филтъра на обобщена таблицане ви позволява да променяте условията за начертаване на диаграмата - тоест не можете да изградите диаграма по колоните на основната таблица (например по колоната Обем на продажбите, бр.), които не са включени в обобщената таблица. Обратно, включването на данни в обобщената таблица се отразява в обобщената диаграма едновременно:

Прозорец Обхват на филтъра на обобщена таблицае предназначен за удобно управление на централна таблица и диаграма, изградена на нейната база:

Чрез промяна на стойностите на филтрите и полетата на осите можете да покажете интересния сегмент от данни за конкретни стойности на координатните оси. Например, много е удобно да анализирате данните за продажбите в отделни магазини за последната седмица:

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

Както вече разбрахте, обобщена диаграма, изградена на базата на съществуваща обобщена таблица, е тясно свързана с нея. Това не винаги е удобно, така че често има смисъл незабавно да се изгради обобщена диаграма въз основа на основната таблица. Това изисква:

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

2. В раздела Вмъкванев група таблициизберете раздел Пивотна масаи след това параграф Пивотна диаграма.

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

Excel ще създаде нова въртяща се таблица и въртяща диаграма:

Просто трябва да конфигурирате полетата и условията на централната таблица с помощта на прозореца Списък с полета на обобщена таблица(как да го направим). Всички промени ще бъдат отразени и в диаграмата.

В горната част на страницата

Как да създадете централна диаграма в Excel от множество таблици

На различни листа от една книга има отчети за продажби в няколко магазина:

Те имат същата структура и един и същ начин на представяне на данни.

Следната икона ще се появи на панела:

Отидете на нов лист. Ние наричаме "Господаря":

Първа стъпка. Посочваме, че ще създадем „обобщена диаграма с обобщена таблица“ въз основа на данни, които са „в множество диапазони на консолидация“.
Втора стъпка. Променете настройката по подразбиране - "създаване на полета за страница". Ако оставите опцията „Създаване на поле за една страница“, данните ще изглеждат объркани.
Стъпка трета. Формираме списък с диапазони, въз основа на които ще бъде изградена въртящата диаграма. Първо отидете на листа с първата таблица - изберете го - щракнете върху Добавяне. Преминаваме към листа с втората таблица - повтаряме всички същите процедури. Посочваме броя на полетата - 1. Прозорецът "Първо поле" става активен. Даваме му името - "2013". Повтаряме същата операция за всеки диапазон - имената "2014", "2015".
Четвърта стъпка. Отидете до листа, където ще бъде разположена централната диаграма. Поставяме курсора в първата клетка на диапазона за централната таблица (Excel ще формира целия диапазон сам).

След като щракнете върху бутона "Край", получаваме централна диаграма и таблица в следния вид:

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

Пивотна диаграма в Excel на базата на готова въртяща таблица

Прозорецът Обхват на филтъра ви позволява да манипулирате обобщената таблица и диаграмата.

Сравнение на продажбите по тримесечия за анализирания период:

Анализ на продажбите в магазин "Серебрянка":

проблем:Има данни за няколко хиляди дарители и техните годишни дарения. Обобщена таблица, базирана на тези данни, няма да може да даде ясна картина кой донор допринася най-много или колко дарители даряват в една или друга категория.

Решение:Трябва да изградите централна диаграма. Графичното представяне на информацията, която е събрана в обобщена таблица, може да бъде полезна за презентация на PowerPoint, използване в среща, в отчет или за бърз анализ. Опорната диаграма ви дава моментна снимка на данните, които ви интересуват (точно като обикновена диаграма), но освен това има интерактивни филтри директно от централната таблица, които ви позволяват бързо да анализирате различни части от данните.

Как да създадете централна диаграма

Има два начина за създаване на обобщена диаграма в Excel 2013. В първия случай използваме предимствата на инструмента „ Препоръчителни диаграми„В Excel. Работейки с този инструмент, не е необходимо първо да създаваме въртяща се таблица, за да изградим въртяща диаграма от нея по-късно.

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

Вариант 1: Създайте централна диаграма с помощта на инструмента за представени диаграми

Изберете данните, които искате да покажете в диаграмата.
В раздела Вмъкване(Вмъкване) в раздела диаграми(Графики) щракнете върху бутона Препоръчителни диаграми(Препоръчителни диаграми), за да отворите диалогов прозорец Вмъкнете диаграма(Вмъкнете диаграма).
В раздел ще се отвори диалогов прозорец Препоръчителни диаграми(Препоръчани диаграми), който изброява подходящи шаблони на диаграми в менюто вляво. В горния десен ъгъл на миниизображението на всеки шаблон има икона на въртяща се диаграма:
Щракнете върху която и да е диаграма от препоръчания списък, за да видите резултата в областта за визуализация.
Изберете подходящ (или почти подходящ) тип диаграма и щракнете Добре.

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

Вариант 2: Създайте обобщена диаграма от съществуваща обобщена таблица

Щракнете някъде в обобщената таблица, за да накарате група от раздели да се появи на лентата с менюта Работа с централни таблици(Инструменти за обобщена таблица).
В раздела Анализ(Анализ) щракнете върху бутона Пивотна диаграма(Опорна диаграма), това ще отвори диалогов прозорец Вмъкнете диаграма(Вмъкнете диаграма).
От лявата страна на диалоговия прозорец изберете подходящия тип диаграма. След това изберете подтип диаграма в горната част на прозореца. Бъдещата централна диаграма ще бъде показана в областта за предварителен преглед.
Кликнете върху Добреза да вмъкнете обобщената диаграма в същия лист като оригиналната обобщена таблица.
След като е създадена централната диаграма, можете да започнете да персонализирате нейните елементи и цветове, като използвате списъка с полета на лентата на менюто или иконите Елементи на диаграмата(Елементи на диаграмата) и Стилове на диаграми(Стилове на диаграми).
Обърнете внимание на получената централна диаграма. Можете да манипулирате филтрите направо на диаграмата, за да видите различни части от вашите данни. Страхотно, наистина!