Excel идва с толкова много мощни инструменти и функции за анализ на данни, че може да се чудите защо трябва да научите още една: обобщената таблица. Краткият отговор е, че обобщената таблица е полезно оръжие за добавяне към вашия арсенал за анализ на данни. Дългият отговор е, че обобщените таблици си струва да се научат, защото идват не само с една или две, а с дълъг списък от предимства.
Нека преброим начините: обобщените таблици на Excel са лесни за изграждане и поддръжка; извършват големи и сложни изчисления невероятно бързо; можете бързо и лесно да ги актуализирате, за да отчитате нови данни; Обобщените таблици са динамични, така че компонентите могат лесно да бъдат премествани, филтрирани и добавяни към; и накрая, обобщените таблици могат да използват повечето от опциите за форматиране, които можете да приложите към обикновени диапазони и клетки на Excel.
О, чакайте, има още едно: обобщените таблици са напълно адаптивни, така че можете да изградите всеки отчет по начина, който искате. Ето десет техники, които ще ви превърнат в професионалист на обобщена таблица.
Научете как да създадете обобщена таблица с помощта на инструмента за бърз анализ на Excel .
По подразбиране, когато щракнете вътре в обобщената таблица, Excel показва прозореца на задачите „Полета на обобщена таблица“ и след това скрива отново панела на задачи „Полета на обобщена таблица“, когато щракнете извън отчета на обобщената таблица.
На пръв поглед няма нищо лошо в това. Ако обаче искате да работите с командите в контекстния раздел Инструменти за обобщена таблица на лентата, трябва да имате избрана поне една клетка в отчета за обобщена таблица. Но изборът на която и да е клетка на обобщена таблица означава, че имате и панела на задачите с полета на обобщената таблица, който заема ценно недвижимо имущество на екрана.
За щастие, Excel също така ви позволява да изключите и включите ръчно панела със задачи „Полета на обобщена таблица“, което ви дава повече място за показване на отчета на обобщената таблица. След това можете да включите отново панела със задачи „Полета на обобщена таблица“, когато трябва да добавите, преместите или изтриете полета.
За да изключите и включите панела със задачи „Полета на обобщена таблица“, следвайте тези стъпки (и двете!):
Щракнете вътре в обобщената таблица.
Изберете Анализ → Покажи → Списък с полета.
Бърз начин да скриете прозореца на задачите с полета на обобщената таблица е да щракнете върху бутона Затваряне в горния десен ъгъл на екрана.
По подразбиране прозорецът на задачите на обобщената таблица е разделен на две секции: секцията Полета изброява наличните полета на източника на данни и се появява в горната част на екрана, а секцията Areas съдържа областите на обобщената таблица — филтри, колони, редове и стойности — и се появява в долната част на прозореца. Можете да персонализирате това оформление, за да отговаря на начина, по който работите. Ето и възможностите:
Ето стъпките, които трябва да следвате, за да промените оформлението на панела на задачите на полетата на обобщената таблица:
Щракнете върху която и да е клетка в обобщената таблица.
Щракнете върху Инструменти.
Бутонът Инструменти е този с иконата на зъбно колело.
Щракнете върху Инструменти, за да видите опциите за оформление на панела на задачи Полета на обобщена таблица.
Excel показва инструментите на панела на задачите с полета на обобщена таблица.
Щракнете върху оформлението, което искате да използвате.
Excel променя оформлението на панела на задачите на полетата на обобщената таблица въз основа на вашия избор.
Докато имате показани инструменти на панела на задачите с полета на обобщената таблица, имайте предвид, че можете също да сортирате списъка с полета . По подразбиране е Сортиране в реда на източника на данни, което означава, че Excel показва полетата в същия ред, в който се появяват в източника на данни. Ако предпочитате да сортирате полетата по азбучен ред, щракнете върху Сортиране от А до Я.
Основното предимство на използването на обобщени таблици е, че те ви дават лесен метод за обобщаване на големи количества данни в кратък отчет за анализ на данни. Накратко, обобщените таблици ви показват гората вместо дърветата. Понякога обаче трябва да видите някои от тези дървета. Например, ако изучавате резултатите от маркетингова кампания, вашата обобщена таблица може да ви покаже общия брой на продадените слушалки в резултат на промоция 1 безплатно с 10. Но какво ще стане, ако искате да видите подробностите, лежащи в основата на това число? Ако вашите изходни данни съдържат стотици или хиляди записи, трябва да филтрирате данните по някакъв начин, за да видите само записите, които искате.
Продадохте 792 слушалки, но какви са подробностите зад този номер?
За щастие Excel ви дава по-лесен начин да видите записите, които искате, като ви позволява директно да преглеждате подробностите, които са в основата на конкретна стойност на данните. Това се нарича пробиване до детайлите. Когато направите разбивка на конкретна стойност на данни в обобщена таблица, Excel се връща към изходните данни, извлича записите, които съдържат стойността на данните, и след това показва записите в нов работен лист. За обобщена таблица, базирана на диапазон или таблица, това извличане отнема само секунда или две, в зависимост от това колко записа съдържат изходните данни.
За да разгледате подробностите, лежащи в основата на точка от данни на обобщена таблица, използвайте един от следните методи:
Excel показва основните данни в нов работен лист. Например, това изображение показва детайлите зад 792 слушалки, продавани с промоцията 1 Free with 10, показана на изображението по-горе.
Подробностите зад продажбите на слушалки.
Когато се опитате да направите разбивка до основните детайли на стойността на данните, Excel може да покаже съобщението за грешка Не може да се промени тази част от отчета за обобщена таблица. Тази грешка означава, че функцията, която обикновено ви позволява да извършвате разбивка, е изключена. За да включите отново тази функция, щракнете върху която и да е клетка в обобщената таблица и след това щракнете върху Анализ → Обобщена таблица → Опции, за да се покаже диалоговият прозорец Опции на обобщената таблица. Щракнете върху раздела Данни, поставете отметка в квадратчето Разрешаване на показване на подробности и след това щракнете върху OK.
Обратната ситуация се случва, когато разпространявате работната книга, съдържаща обобщената таблица, и не искате други потребители да раздълбават и затрупват работната книга с подробни работни листове. В този случай щракнете върху Анализ → Обобщена таблица → Опции, щракнете върху раздела Данни, премахнете избора от Разрешаване на показване на подробности и след това щракнете върху OK.
Понякога може да искате да видите всички основни изходни данни на обобщена таблица. Ако изходните данни са диапазон или таблица в друг работен лист, можете да видите основните данни, като покажете този работен лист. Ако обаче изходните данни не са толкова лесно достъпни, Excel ви дава бърз начин да прегледате всички основни данни. Щракнете с десния бутон върху общата клетка на обобщената таблица (тоест клетката в долния десен ъгъл на обобщената таблица) и след това щракнете върху Покажи подробности. (Можете също да щракнете двукратно върху тази клетка.) Excel показва всички основни данни на обобщената таблица в нов работен лист.
Едно от хубавите неща за обобщената таблица е, че тя се намира в обикновен работен лист на Excel, което означава, че можете да прилагате опции за форматиране като подравняване и шрифтове към части от обобщената таблица. Това работи добре, особено ако имате изисквания за персонализирано форматиране.
Например, може да имате вътрешни насоки за стил, които трябва да следвате. За съжаление, прилагането на форматиране може да отнеме време, особено ако прилагате няколко различни опции за форматиране. А общото време за форматиране може да стане обременително, ако трябва да приложите различни опции за форматиране към различни части на обобщената таблица. Можете значително да намалите времето, което прекарвате във форматиране на обобщените таблици, ако вместо това приложите стил.
А стил е колекция от опциите на форматиране - шрифтове, граници, както и цвета на фона - че Excel определя за различни области на една обобщена таблица. Например, стил може да използва удебелен бял текст на черен фон за етикети и общи суми и бял текст на тъмно син фон за елементи и данни. Ръчното дефиниране на всички тези формати може да отнеме половин час до час. Но с функцията за стил вие избирате този, който искате да използвате за обобщената таблица като цяло, а Excel автоматично прилага отделните опции за форматиране.
Excel дефинира повече от 80 стила, разделени в три категории: Светъл, Среден и Тъмен. Категорията Light включва Pivot Style Light 16, форматирането по подразбиране, приложено към отчетите на обобщената таблица, които създавате; и None, което премахва цялото форматиране от обобщената таблица. Можете също така да създадете свои собствени стилови формати.
Ето стъпките, които трябва да следвате, за да приложите стил към обобщена таблица на Excel:
Щракнете върху която и да е клетка в обобщената таблица, която искате да форматирате.
Щракнете върху раздела Дизайн.
В групата Стилове на обобщена таблица щракнете върху бутона Още.
Появява се галерията със стилове.
Щракнете върху стила, който искате да приложите.
Excel прилага стила.
Може да откриете, че нито един от предварително дефинираните стилове на обобщена таблица не ви дава точния вид, който искате. В този случай можете сами да дефинирате този външен вид, като създадете персонализиран стил на обобщена таблица от нулата.
Excel ви предлага огромна гъвкавост, когато създавате персонализирани стилове на обобщена таблица. Можете да форматирате 25 отделни елемента на обобщена таблица. Тези елементи включват цялата таблица, етикетите и стойностите на полетата на страницата, първата колона, заглавният ред, редът за общата сума и колоната за общата сума. Можете също да дефинирате ивици, които са отделни формати, приложени към редуващи се редове или колони. Например, ивицата на първия ред прилага форматиране към редове 1, 3, 5 и т.н.; ивицата на втория ред прилага форматиране към редове 2, 4, 6 и т.н. Ивици могат да направят дълъг или широк доклад по-лесен за четене.
Контролът върху толкова много елементи ви позволява да създадете персонализиран стил, който да отговаря на вашите нужди. Например, може да се нуждаете от вашата обобщена таблица, за да съответства на вашите корпоративни цветове. По същия начин, ако обобщената таблица ще се появи като част от по-голям отчет, може да ви е необходимо форматирането на обобщената таблица, за да съответства на темата, използвана в по-големия отчет.
Единственият недостатък при създаването на персонализиран стил на обобщена таблица е, че трябва да го направите от нулата, защото Excel не ви позволява да персонализирате съществуващ стил. Бу, Excel! Така че, ако трябва да дефинирате форматиране за всички 25 елемента на обобщена таблица, създаването на персонализиран стил може да отнеме време.
Ако все още сте готови за това, ето стъпките, които трябва да направите, за да създадете свой собствен стил на обобщена таблица в Excel:
Щракнете върху раздела Дизайн.
В групата Стилове на обобщена таблица щракнете върху Още.
Появява се галерията със стилове.
Щракнете върху Нов стил на обобщена таблица.
Появява се диалоговият прозорец за нов стил на обобщена таблица.
Новият диалогов прозорец Стил на обобщена таблица.
Въведете име за вашия персонализиран стил.
Използвайте списъка с елементи на таблица, за да изберете функцията за обобщена таблица, която искате да форматирате.
Щракнете върху Форматиране.
Появява се диалоговият прозорец Форматиране на клетки.
Използвайте опциите в раздела Шрифт, за да форматирате текста на елемента.
Можете да изберете шрифт, стил на шрифта (като получер или курсив) и размер на шрифта. Можете също да изберете подчертаване, цвят и ефект на зачертаване.
Използвайте опциите в раздела Border, за да форматирате границата на елемента.
Можете да изберете стил на рамката, цвят и местоположение (като левия край, горния ръб или и двете).
Използвайте опциите в раздела Запълване, за да форматирате цвета на фона на елемента.
Можете да изберете плътен цвят или шарка. Можете също да щракнете върху бутоните Fill Effects, за да посочите градиент, който се променя от един цвят в друг.
Щракнете върху OK.
Excel ви връща в диалоговия прозорец Нов стил на обобщена таблица.
Повторете стъпки от 5 до 10, за да форматирате други елементи на таблицата.
За удобство диалоговият прозорец Нов стил на обобщена таблица включва раздел за преглед, който ви показва как ще изглежда стилът, когато бъде приложен към обобщена таблица. Ако особено се гордеете с новия си стил, може да искате да го използвате за всичките си обобщени таблици. Защо не? За да кажете на Excel да използва вашия нов стил като стандартен за всяка бъдеща обобщена таблица, която фалшифицирате, поставете отметка в квадратчето Задаване като стил на обобщена таблица по подразбиране за този документ.
Когато най-накрая сте готови, щракнете върху OK.
Excel запазва персонализирания стил на обобщена таблица.
Странно, след като затворите диалоговия прозорец за нов стил на обобщена таблица, Excel не прилага новия стил към текущата обобщена таблица. тъпо! За да приложите стила сами, изберете която и да е клетка в обобщената таблица, щракнете върху Дизайн, щракнете върху бутона Още в групата Стилове на обобщена таблица, за да отворите галерията със стилове, и след това щракнете върху стила си в секцията Персонализирана, която сега се показва в горната част на галерията.
Ако трябва да направите промени в персонализирания си стил, отворете галерията със стилове, щракнете с десния бутон върху персонализирания стил и след това щракнете върху Промяна. Използвайте диалоговия прозорец Промяна на стил на обобщена таблица, за да направите промените си, след което щракнете върху OK.
Ако установите, че трябва да създадете друг персонализиран стил, който е подобен на съществуващ персонализиран стил, не се притеснявайте да създавате новия стил от нулата. Вместо това отворете галерията със стилове, щракнете с десния бутон върху съществуващия персонализиран стил и след това щракнете върху Дублиране. В диалоговия прозорец Промяна на стил на обобщена таблица коригирайте името на стила и форматирането и след това щракнете върху OK.
Ако вече не се нуждаете от персонализиран стил, трябва да го изтриете, за да намалите претрупването в галерията със стилове. Щракнете върху раздела Дизайн, отворете галерията Стилове на обобщена таблица, щракнете с десния бутон върху персонализирания стил, от който вече не се нуждаете, и след това щракнете върху Изтриване. Когато Excel ви помоли да потвърдите, щракнете върху OK.
Excel има лошия навик понякога да не запазва персонализираното ви форматиране, когато опреснявате или изграждате отново обобщената таблица. Например, ако сте приложили удебелен шрифт към някои етикети, тези етикети може да се върнат към обикновен текст след опресняване. Excel има функция, наречена Запазване на форматиране, която ви позволява да запазите такова форматиране по време на опресняване; можете да запазите персонализираното си форматиране, като го активирате.
Функцията за запазване на форматиране винаги е активирана в обобщените таблици по подразбиране. Въпреки това, друг потребител би могъл да деактивира тази функция. Например, може да работите с обобщена таблица, създадена от друго лице, и той или тя може да са деактивирали функцията за запазване на форматирането.
Имайте предвид обаче, че когато опреснявате или изграждате отново обобщена таблица, Excel отново прилага текущото стилово форматиране на отчета. Ако не сте посочили стил, Excel отново прилага стила на обобщената таблица по подразбиране (наречен Pivot Style Light 16); ако сте посочили стил, Excel прилага отново този стил.
Ето стъпките, които трябва да следвате, за да конфигурирате обобщена таблица на Excel за запазване на форматирането:
Щракнете върху която и да е клетка в обобщената таблица, с която искате да работите.
Изберете Анализ → Обобщена таблица → Опции.
Появява се диалоговият прозорец Опции на обобщената таблица с показан раздел Оформление и формат.
Премахнете отметката от квадратчето Autofit Column Widths при актуализиране.
Премахването на тази опция не позволява на Excel автоматично да форматира неща, като ширини на колоните, когато завъртате полета.
Поставете отметка в квадратчето Запазване на форматирането на клетките при актуализиране.
Щракнете върху OK.
Excel запазва вашето персонализирано форматиране всеки път, когато опресните обобщената таблица.
Когато създадете първата обобщена таблица в работна книга, Excel й дава невдъхновяващото име PivotTable1. Следващите обобщени таблици са наименувани последователно (и също толкова невъодушевяващо): PivotTable2, PivotTable3 и т.н. Въпреки това, Excel също повтаря тези имена, когато създавате нови обобщени таблици въз основа на различни източници на данни. Ако вашата работна книга съдържа определен брой обобщени таблици, можете да ги улесните за разграничаване, като дадете на всяка една уникално и описателно име. Ето как:
Щракнете върху която и да е клетка в обобщената таблица, с която искате да работите.
Щракнете върху Анализ → обобщена таблица.
Използвайте текстовото поле Име на обобщената таблица, за да въведете новото име за обобщената таблица.
Максималната дължина за име на обобщена таблица е 255 знака.
Щракнете извън текстовото поле.
Excel преименува обобщената таблица.
Обобщена таблица по подразбиране, която има поне едно поле за ред, съдържа допълнителен ред в долната част на таблицата. Този ред е обозначен с обща сума и включва общата сума на стойностите, свързани с елементите на полето на реда. Въпреки това, стойността в реда Общия сбор може всъщност да не е сума. Например, ако сумираното изчисление е Средно, редът Обща сума включва средната стойност на стойностите, свързани с елементите на полето на реда.
По подобен начин обобщена таблица, която има поне едно поле за колона, съдържа допълнителна колона в най-дясната част на таблицата. Тази колона също е означена като „Обща сума“ и включва общата сума на стойностите, свързани с елементите на полето в колоната. Ако обобщената таблица съдържа както ред, така и поле за колона, редът Обща сума също съдържа сумите за всеки елемент от колоната, а колоната Обща обща сума също има сумите за всеки елемент от ред.
Освен че заемат място в обобщената таблица, тези общи суми често не са необходими за анализ на данни. Например, да предположим, че искате да проучите тримесечните продажби за вашите търговци, за да видите кои суми са над определена стойност за бонус цели. Тъй като единствената ви грижа са индивидуалните обобщени стойности за всеки служител, общите суми са безполезни. В такъв случай можете да кажете на Excel да не показва общите суми, като следвате тези стъпки:
Щракнете върху която и да е клетка в обобщената таблица, с която искате да работите.
Щракнете върху Дизайн → Общи суми.
Excel показва меню с опции за показване на общите суми.
Щракнете върху опцията, която предпочитате.
Менюто съдържа четири елемента:
Excel въвежда избраната опция за общ сбор в сила.
Заглавките на полетата, които се появяват в отчета, са друга често досадна функция на обобщена таблица. Тези заглавки включват бутони за сортиране и филтриране, но ако не използвате тези бутони, заглавките на полето просто претрупват обобщената таблица. За да изключите заглавките на полето, щракнете вътре в обобщената таблица и след това изберете Анализ → Покажи → Заглавки на полета.
Обобщените таблици често водят до големи работни книги, тъй като Excel трябва да следи много допълнителна информация, за да поддържа ефективността на обобщената таблица приемлива. Например, за да се гарантира, че преизчисляването, включено в завъртането, се случва бързо и ефективно, Excel поддържа копие на изходните данни в специална област на паметта, наречена централна кеш.
Ако изградите обобщена таблица от данни, които се намират в друга работна книга или във външен източник на данни, Excel съхранява изходните данни в кеша на централната част. Това значително намалява времето, необходимо на Excel за опресняване и преизчисляване на обобщената таблица. Недостатъкът е, че може да увеличи както размера на работната книга, така и времето, необходимо на Excel, за да запази работната книга. Ако работната ви книга е станала твърде голяма или запазването й отнема твърде много време, следвайте тези стъпки, за да кажете на Excel да не запазва изходните данни в кеша на централната част:
Щракнете върху която и да е клетка в обобщената таблица.
Щракнете върху Анализ → обобщена таблица → Опции.
Появява се диалоговият прозорец Опции на обобщената таблица.
Щракнете върху раздела Данни.
Премахнете отметката от квадратчето Запазване на изходните данни с файл.
Щракнете върху OK.
Excel вече не записва външните изходни данни в централния кеш.
Може да се наложи да използвате стойност на обобщена таблица във формула на работен лист. Обикновено препращате към клетка във формула, като използвате адреса на клетката. Това обаче няма да работи с обобщени таблици, тъй като адресите на стойностите на отчета се променят, докато завъртате, филтрирате, групирате и опреснявате обобщената таблица.
За да осигурите точни препратки към обобщената таблица, използвайте функцията GETPIVOTDATA на Excel. Тази функция използва полето за данни, местоположението на обобщената таблица и една или повече (ред или колона) двойки поле/елемент, които определят точната стойност, която искате да използвате. По този начин, без значение какво е оформлението на обобщената таблица, докато стойността остава видима в отчета, вашата препратка към формулата остава точна.
Ето синтаксиса на функцията GETPIVOTDATA:
GETPIVOTDATA( поле_данни , централна_таблица , [ , поле1 , елемент1 ][, …]
Двете задължителни полета са data_field , което е името на полето, което използвате в областта на стойностите на обобщената таблица, и pivot_table , което определя адреса на клетката в горния ляв ъгъл на обобщената таблица. Останалите аргументи идват по двойки: име на поле и елемент в това поле.
Например, ето формула GETPIVOTDATA, която връща стойността на обобщената таблица, където елементът от полето на продукта е Earbuds, а елементът от полето за промоция е 1 безплатно с 10:
=GETPIVOTDATA("Количество", $A$3, "Продукт", "Слушалки", "Промоция", "1 безплатно с 10")
Функцията GETPIVOTDATA си върши работата.
GETPIVOTDATA е малко сложно, но не се притеснявайте. Почти никога няма да ви се налага да изваждате тази функция и всички нейни аргументи на ръка. Вместо това Excel удобно обработва всичко вместо вас, когато щракнете върху стойността на обобщената таблица, която искате да използвате във формулата си. фу!
Как да блокирам Microsoft Word да отваря файлове в режим само за четене в Windows Microsoft Word отваря файлове в режим само за четене, което прави невъзможно редактирането им? Не се притеснявайте, методите са по-долу
Как да коригирате грешки при отпечатването на неправилни документи на Microsoft Word Грешките при отпечатването на документи на Word с променени шрифтове, разхвърляни абзаци, липсващ текст или изгубено съдържание са доста чести. Въпреки това недейте
Ако сте използвали писалката или маркера, за да рисувате върху слайдовете на PowerPoint по време на презентация, можете да запазите чертежите за следващата презентация или да ги изтриете, така че следващия път, когато го покажете, да започнете с чисти слайдове на PowerPoint. Следвайте тези инструкции, за да изтриете чертежи с писалка и маркери: Изтриване на линии една в […]
Библиотеката със стилове съдържа CSS файлове, файлове с разширяем език на стиловия език (XSL) и изображения, използвани от предварително дефинирани главни страници, оформления на страници и контроли в SharePoint 2010. За да намерите CSS файлове в библиотеката със стилове на сайт за публикуване: Изберете Действия на сайта→Преглед Цялото съдържание на сайта. Появява се съдържанието на сайта. Библиотеката Style се намира в […]
Не затрупвайте аудиторията си с огромни числа. В Microsoft Excel можете да подобрите четливостта на вашите табла за управление и отчети, като форматирате числата си така, че да се показват в хиляди или милиони.
Научете как да използвате инструменти за социални мрежи на SharePoint, които позволяват на индивиди и групи да общуват, да си сътрудничат, споделят и да се свързват.
Юлианските дати често се използват в производствени среди като времеви печат и бърза справка за партиден номер. Този тип кодиране на дата позволява на търговците на дребно, потребителите и обслужващите агенти да идентифицират кога е произведен продуктът и по този начин възрастта на продукта. Юлианските дати се използват и в програмирането, военните и астрономията. Различно […]
Можете да създадете уеб приложение в Access 2016. И така, какво всъщност е уеб приложение? Е, уеб означава, че е онлайн, а приложението е просто съкращение от „приложение“. Персонализирано уеб приложение е онлайн приложение за база данни, достъпно от облака с помощта на браузър. Вие създавате и поддържате уеб приложението в настолната версия […]
Повечето страници в SharePoint 2010 показват списък с връзки за навигация в лентата за бързо стартиране в лявата част на страницата. Лентата за бързо стартиране показва връзки към представено съдържание на сайта, като списъци, библиотеки, сайтове и страници за публикуване. Лентата за бързо стартиране включва две много важни връзки: Връзка към цялото съдържание на сайта: […]
За прости проблеми Solver в Excel обикновено бързо намира оптималните стойности на променливата Solver за целевата функция. Но в някои случаи Solver има проблеми с намирането на стойностите на променливата Solver, които оптимизират целевата функция. В тези случаи Solver обикновено показва съобщение или съобщение за грешка, което описва или обсъжда проблема, който […]
