В този раздел на дадения урок стъпка по стъпка инструкцияКак да създадете разширена обобщена таблица в съвременни версии на Excel (2007 и по-нови). За тези, които работят в по-ранни версии на Excel, препоръчваме тази статия: Как да създадете разширена централна таблица в Excel 2003?
Като изходни данни за изграждане на въртящата таблица използваме таблицата с данни за продажбите на компанията през първото тримесечие на 2016 г.
| А | Б | ° С | д | Е | |
|---|---|---|---|---|---|
| 1 | Дата | Фактура Реф | Количество | Търговски представител. | регион |
| 2 | 01/01/2016 | 2016-0001 | $819 | Барнс | север |
| 3 | 01/01/2016 | 2016-0002 | $456 | Кафяв | юг |
| 4 | 01/01/2016 | 2016-0003 | $538 | Джоунс | юг |
| 5 | 01/01/2016 | 2016-0004 | $1,009 | Барнс | север |
| 6 | 01/02/2016 | 2016-0005 | $486 | Джоунс | юг |
| 7 | 01/02/2016 | 2016-0006 | $948 | Смит | север |
| 8 | 01/02/2016 | 2016-0007 | $740 | Барнс | север |
| 9 | 01/03/2016 | 2016-0008 | $543 | Смит | север |
| 10 | 01/03/2016 | 2016-0009 | $820 | Кафяв | юг |
| 11 | ... | ... | ... | ... | ... |
В следващия пример ще създадем обобщена таблица, която показва общите продажби на месец за годината, разбити по регион и по продавач. Процесът за създаване на тази централна таблица е описан по-долу.
Както е необходимо, нашата централна таблица (вижте изображението по-долу) вече показва общите продажби по месеци, разбити по регион и по продавач.
За да подобрите външния вид на обобщената таблица, трябва да персонализирате форматирането. Например, ако за стойности в колони Б – гнастройте паричния формат, тогава централната таблица ще стане много по-лесна за четене.
Филтри в въртяща се таблица
Филтрите в обобщена таблица ви позволяват да показвате информация за една стойност или избирателно за множество стойности от наличните полета с данни. Например, в обобщената таблица, показана по-горе, ще можем да преглеждаме данни само за региона на продажби. северили само за региона юг.
За показване на данни само за региона на продажби север, в панела Полета на обобщена таблица(Списък с полета на обобщена таблица) плъзнете полето регионкъм региона Филтри(Филтри за отчети).
Поле регионсе появява в горната част на въртящата се таблица. Отворете падащия списък в това поле и изберете регион в него север... Основната таблица (както е показано на снимката по-долу) ще покаже стойностите само за региона север.
Можете бързо да превключите към изглед на данни само за регион юг- за това ви трябва в падащия списък в полето регионизберете юг.
Пивотните таблици са едни от най-много ефективни инструментив MS Excel. С тяхна помощ можете да конвертирате милион реда данни в кратък отчет за броени секунди. В допълнение към бързото обобщаване на резултатите, централните таблици ви позволяват буквално да промените начина, по който се извършва анализът в движение, като плъзгате и пускате полета от една област на отчета в друга.
Пивотните таблици също са едни от най-подценяваните инструменти на Excel. Повечето потребители не са наясно какви възможности са в ръцете им. Нека си представим, че опорните таблици все още не са измислени. Работите за компания, която продава продуктите си на различни клиенти. За простота, в гамата има само 4 артикула. Продуктите се купуват редовно от няколко десетки клиенти, които са в различни региони... Всяка транзакция се въвежда в базата данни и представлява отделен ред.
Вашият директор ви инструктира да направите кратък отчет за продажбите на всички продукти по регион (област). Проблемът може да бъде решен по следния начин.
Първо, нека създадем оформление на таблицата, тоест заглавка, състояща се от уникални стойности на продукта и региона. Нека направим копие на колоната с продукта и да премахнем дубликатите. След това използвайте Paste Special, за да транспонирате колоната в ред. Правим същото с областите, само без транспониране. Получаваме заглавието на отчета.
Тази табела трябва да се попълни, т.е. обобщете приходите за съответните стоки и региони. Това не е трудно да се направи с функцията SUMIF. Ще добавим и суми. Ще получите обобщен отчет за продажбите в контекста на района-производство.
Изпълнихте задачата и показвате отчета на директора. Гледайки електронната таблица, той генерира няколко страхотни идеи наведнъж.
- Възможно ли е да се направи отчет не за приходите, а за печалбата?
- Възможно ли е да се показват продукти по редове, а региони по колони?
- Може ли да се направят такива таблици за всеки мениджър поотделно?
Дори ако ти опитен потребител Excel, създаването на нови отчети ще отнеме много време. Това да не говорим за възможните грешки. Въпреки това, ако притежавате централни таблици, отговорът е да, трябват ми 5 минути, може би по-малко.
Ето как да го направите. Отваряме изходните данни. Основната таблица може да бъде изградена според обичайния диапазон, но би било по-правилно да се преобразува в таблица на Excel. Това незабавно ще разреши проблема с автоматично улавяне на нови данни. Изберете произволна клетка и отидете на раздела Вмъкване... От лявата страна на лентата има два бутона: и Препоръчителни централни таблици.
Ако не знаете как да организирате наличните данни, можете да използвате командата Препоръчителни централни таблици... Excel ще покаже миниатюри на възможни оформления въз основа на вашите данни.
Щракнете върху подходящата опция и централната таблица е готова. Остава само да го напомним, т.к. малко вероятно е стандартната заготовка напълно да отговаря на вашите желания. Ако трябва да изградите обобщена таблица от нулата или имате стара версия на програмата, натиснете бутона. Ще се появи прозорец, в който трябва да посочите диапазона на източника (ако активирате която и да е клетка в таблицата на Excel, тя ще бъде определена сама) и местоположението на бъдещата централна таблица (по подразбиране, нов лист).
Много често тук нищо не трябва да се променя. След като щракнете върху OK, ще бъде създаден нов лист на Excel с празно оформление на централна таблица.
Преди да преминем към настройките, нека се запознаем с интерфейса и основните понятия. Оформлението на таблицата може да се конфигурира в панела Полета на обобщена таблица, който се намира от дясната страна на листа.
В горната част на панела има списък с всички налични полета, тоест колони в изходните данни. Ако трябва да добавите ново поле към оформлението, тогава можете да поставите квадратче за отметка срещу него - Excel сам ще определи къде трябва да бъде поставено това поле. Въпреки това, далеч не винаги е възможно да се отгатне, така че е по-добре да го плъзнете с мишката на правилното място в оформлението. Изтрива и полета: премахнете отметката от квадратчето или плъзнете обратно.
Основната таблица се състои от 4 области, които са разположени в долната част на панела: стойности, редове, колони, филтри. Нека разгледаме по-отблизо предназначението им.
Диапазон от стойности- това е централната част на обобщената таблица със стойности, които се получават чрез агрегиране на изходните данни по избрания начин.
В повечето случаи агрегирането става от Сумиране... Ако всички данни в избраното поле са в числов формат, Excel ще присвои сумиране по подразбиране. Ако има поне един текст или празна клетка в оригиналните данни, тогава вместо сумата, тя ще бъде изчислена количествоклетки. В нашия пример всяка клетка е сумата от всички съвпадащи елементи в съответния регион.
Можете да използвате други методи за изчисление в клетките на обобщената таблица. Има около 20 вида от тях (средна, минимална стойност, пропорция и т.н.). Има няколко начина за промяна на метода на изчисление. Най-простият е да щракнете с десния бутон върху която и да е клетка от желаното поле в самата обобщена таблица и да изберете различен метод на агрегиране.
Площ на линията- имената на редовете, които се намират в най-лявата колона. Това са всички уникални стойности на избраното поле (колона). В областта на реда може да има няколко полета, тогава таблицата се оказва многостепенна. Тук обикновено се поставят качествени променливи като имена на продукти, месеци, региони и т.н.
Площ на колоната- подобно на редовете, показва уникалните стойности на избраното поле, само по колони. Имената на колоните също обикновено са качествени. Например години и месеци, продуктови групи.
Филтърна зона- се използва, както подсказва името, за филтриране. Например самият отчет показва продукти по региони. Трябва да ограничите обобщената таблица до конкретна индустрия, конкретен период или мениджър. След това в областта на филтъра се поставя поле за филтриране и необходимата стойност се избира от падащия списък.
Чрез добавяне и премахване на полета в посочените области можете да персонализирате всяка част от вашите данни за броени секунди.
Нека да видим как работи това в действие. Нека засега създадем същата таблица, която вече беше създадена с помощта на функцията SUMIFS. За да направите това, плъзнете до областта Стойноститеполе "Приходи", в обл Струниплъзнете полето "Регион" (регион на продажби) в колони- "Продукт".
В резултат на това получаваме истинска централна маса.
Изграждането му отне буквално 5-10 секунди. Промяната на съществуваща въртяща се таблица също е лесна. Да видим как желанията на режисьора лесно се превръщат в реалност.
Нека заменим приходите с печалба.
Продуктите и областите също се разменят чрез плъзгане на мишката.
Има няколко инструмента за филтриране на централни таблици. В този случай просто ще поставим полето „Мениджър“ в областта на филтрите.
Всичко отне няколко секунди. Ето колко лесно е наистина да се работи с централни таблици. Разбира се, не всички задачи са толкова тривиални. Има и моменти, когато трябва да използвате по-усъвършенстван метод за агрегиране, да добавите изчислени полета, условно форматиране и т.н. Но това вече е по-усъвършенствано използване на централни таблици.
Първоначални данни
За да работите успешно с обобщени таблици, изходните данни трябва да отговарят на редица изисквания. Предпоставка е наличието на имена над всяко поле (колона), чрез които тези полета ще бъдат идентифицирани. Сега няколко полезни съвета.
1. Най-добрият формат за данни е Excel Table. Добре е, че всяко поле има име и когато се добавят нови редове, те автоматично се включват в обобщената таблица.
2. Избягвайте повтарянето на групи в колони. Например, всички дати трябва да бъдат в едно поле, а не разбити, например, по месеци в отделни колони.
3. Премахнете пропуските и празните клетки, в противен случай тази линия може да изпадне от анализа.
4. Приложете правилно форматиране към полетата. Числата трябва да са в числов формат, датите трябва да са дати. В противен случай ще има проблеми с групирането и математическата обработка. Но тук Excel ще ви помогне, т.к. той доста добре дефинира самия формат на данните.
Като цяло изискванията са малко, но трябва да сте наясно с тях.
Ако направите промени в източника (например добавите нови редове), централната таблица няма да се промени, докато не я обновите с десния бутон на мишката
или
чрез командата в раздела Данни - Обнови всички.
Това се прави нарочно поради факта, че въртящата се таблица заема много място в RAM. Следователно, за да се използват по-икономично компютърните ресурси, работата не върви директно с източника, а с кеша, където се намира моментната снимка на изходните данни.
Използвайки централни таблици, дори на такова основно ниво, можете драстично да увеличите скоростта и качеството на обработка на големи количества данни. Ако нещо остане неясно, пишете в коментарите.
По-долу е даден видео урок за това как да създадете проста обобщена таблица в Excel.
P.S. Задълбочени познания за осевите таблици можете да получите на
За да разберем по-добре как да създаваме централни таблици в програмата, трябва да се научим как да ги правим в Excel.
Обобщени таблици в Excel
Формулярът на централната таблица съдържа три области за плъзгане и пускане на полета в тях: за колони, за редове и за данни.
От диалог Списък с полета на обобщена таблицаплъзнете кутията Фамилиякъм региона Плъзнете полетата на ред тук... Формата на въртящата се маса се е променила леко.
Excel избра всички уникални стойности в колоната Фамилия и ги постави в редовете на нашия бъдещ отчет.
Сега плъзнете полето Сума v област с данни.
Excel изчислява общите продажби за всеки продавач. Основното нещо е бързо и без грешки. Докладът може да се счита за готов.
Но за по-добро разбиране на възможностите на централната таблица ще посочим и областта на колоните. Тази област сега изглежда скрита, но всъщност е видима. Поле за преместване датана горния десен ъгъл на отчета, тук:
Веднага след като пуснете бутона на мишката, отчетът веднага ще се промени, показвайки динамиката на продажбите по ден за всеки продавач.
Докладът се оказа твърде подробен. Нека ги групираме по месеци.
- Щракнете с десния бутон върху полето за отчет дата, в менюто, което се показва, изберете Група и структура, тогава Група...
- В следващия диалогов прозорец Excel ни моли да посочим как точно да групираме и веднага предлага по месеци (маркирани). Не променяйте нищо и натиснете Добре.
Датата на продажба вече е групирана по месеци в отчета.
Промяна на обобщен отчет на Excel
За да промените отчета, например да замените поле на ред, просто плъзнете полето извън отчета. И след това преместете там друго поле, например полето Продукт.
Получихме отчет за продажбите на продукти по месеци.
Но какво ще стане, ако заменим полето датана Фамилия.
Възможностите за въртящи се таблици изглеждат безкрайни. Как ти харесва това?
Резюме
Взехме кратка таблица за продажби като първоначални данни за изграждане на отчета. Но може да са хиляди редове и десетки колони. Начинът на конструиране на отчета ще остане същият: посочихте колони, редове и данни.
Получихте основни знанияотносно обобщените таблици на Excel. Това е достатъчно, за да разберете принципа на техните настройки и ползите от тях. Сега да видим
Обобщените таблици Excel е специален тип конструкция, която предполага наличието на функцията за незабавно генериране на отчет за документ. С тяхна помощ можете лесно да обобщите някои от същия тип данни.
В Excel 2007 (MS Excel 2010 | 2013) централната таблица се използва основно за съставяне на математически или икономически анализ на данни.
Как да направите въртяща таблица в Excel
Анализът на данните от документа допринася за по-бързото и правилно решениевъзложени задачи. Процесорът за електронни таблици Excel може да се справи дори с най-обемните и сложни документи. Няма да е трудно за програмата да състави отчет и да обобщи данните.
За да създадете самата проста въртяща се таблица, следвайте стъпките по-долу:
- С помощта на раздела "вмъкване", който се намира в главното меню на лентата с инструменти на програмата, изберете бутона за създаване на препоръчани обобщени таблици;
- В отворения диалогов прозорец на програмата щракнете върху рамката на плочата, която харесвате. Това ще направи оформлението достъпно за потребителски преглед.
Изберете най-подходящата опция, която може да покаже напълно цялата информация;
Съвет!Допълнителни оформления на обобщена таблица могат да бъдат изтеглени от официалния уебсайт на Microsoft.».
- Натиснете бутона OK и програмата веднага ще добави избраната таблица (или празно оформление) към отворения лист на документа. Също така програмата автоматично ще определи реда на редовете, според предоставената информация;
- За да изберете елементи на таблицата и да ги подредите ръчно, сортирайте съдържанието. Можете също да филтрирате данните. По принцип осевата таблица е прототип на малка база данни.
Филтрирането е от съществено значение, когато трябва бързо да преглеждате само определени колони и редове. Следва пример за централна таблица за продажби след филтриране на съдържанието.
По този начин можете бързо да видите обемите на продажбите в отделни региони (в нашия случай Запад и Юг);
Препоръчително е да използвате вече създадените оформления, но потребителят може да избере празна таблица и да я попълни сам. Трябва да добавите полета, формули за изчисление, филтри към празен шаблон.
Можете също да създавате свързани централни таблици на няколко листа от документа едновременно. По този начин можете да анализирате данните на цял документ или няколко документа / листа наведнъж.
Можете също да анализирате външни данни с помощта на централни таблици.
Пивотни изчисления в Microsoft Excel – Формули
Когато отчитате генерираната обобщена таблица, можете да използвате голям бройстатистически функции и формули с цел анализиране на получените стойности и основните входни данни.
Можете да добавите към таблицата например елемент за изчисляване на комисионна за плащане или друг вид елемент за сетълмент. Всички формули в колони и редове се добавят с помощта на полето "Вмъкване".
С помощта на този раздел можете да създадете графика, да изчислите елементи по формула и да изградите хистограма на зависимостта.
Пивотна масаExcel- незаменим инструмент за работа с големи количества данни. С централна таблица можете да извличате и работите само с конкретни данни от целия масив. Например нивото на продажби на конкретен продукт от целия асортимент, колко продукта е продал конкретен мениджър или в кой магазин е закупен най-вече този или онзи продукт и т.н.
Създаване на централни таблици вExcel
Етап 1.Изберете общата таблица с мишката, така че да „стане синя“.
Стъпка 2.Без да премахвате селекцията, изберете менюто "Вмъкване", в него кликнете върху бутона "Опорна таблица".
Стъпка 3.Пред вас ще се отвори нов прозорец „Създаване на обобщена таблица“, в който трябва да кликнете върху бутона „OK“.
Стъпка 4.На вашия лист ще се появи редактор на централна таблица, с който можете да създадете своя собствена обобщена таблица само с данните, които ви интересуват. Просто плъзнете и пуснете полетата, от които се нуждаете, в една от четирите области.
Въпреки факта, че много от нас намират Excel за много сложна и неразбираема програма, тя има огромен брой полезни функциии възможности за всеки потребител. Обобщена таблица в Excel значително ще улесни работата ви с данни, като бързо сортира и избира само стойностите, от които се нуждаете. Благодарение на нашите съвети вече знаете как да направите централна таблица в Excel само с няколко щраквания. Късмет!