Анализ на данни в Excel: Създаване на обобщена таблица на Excel

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

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

Създаване на обобщени таблици на Excel по препоръка

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

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

Изберете командния бутон Препоръчани обобщени таблици в раздела Вмъкване на лентата или натиснете Alt+NSP.
Excel показва диалогов прозорец с препоръчани обобщени таблици. Този диалогов прозорец съдържа списъчен прозорец от лявата страна, който показва мостри от всички предложени централни таблици, които Excel 2019 може да създаде от данните във вашия списък.

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

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

Веднага след като щракнете върху OK, Excel създава нова въртяща се таблица след избраната извадка в собствения си работен лист (Sheet1), вмъкнат пред останалите във вашата работна книга. Тази въртяща се таблица е избрана в новия лист, така че панелът със задачи „Полета на обобщена таблица“ да се показва от дясната страна на прозореца на работния лист на Excel и контекстният раздел „Инструменти за обобщена таблица“ да се показва на лентата. Можете да използвате опциите в този панел на задачите и контекстния раздел, за да персонализирате новата си централна таблица в Excel.

Ръчно създаване на обобщени таблици на Excel

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

За да създадете ръчно нова централна таблица от работния лист с данните, които трябва да се анализират, позиционирайте показалеца на клетка някъде в клетките на този списък и след това щракнете върху командния бутон на обобщена таблица в раздела Вмъкване на лентата или натиснете Alt+NV.

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

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

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

Ако източникът на данни за вашата въртяща таблица е външна таблица на база данни, създадена с отделна програма за управление на база данни, като Access, трябва да щракнете върху бутона Използване на външен източник на данни, щракнете върху бутона Избор на връзка и след това щракнете върху името на връзка в диалоговия прозорец Съществуващи връзки. Също така, Excel 2019 поддържа анализиране на данни от множество свързани таблици в работен лист (наричан модел на данни). Ако данните в новата обобщена таблица на Excel, която създавате, трябва да бъдат анализирани заедно с друга съществуваща обобщена таблица, не забравяйте да поставите отметка в квадратчето Добавяне на тези данни към модела на данни.

Ако посочите нов работен лист като местоположение за новата обобщена таблица в диалоговия прозорец Създаване на обобщена таблица, когато щракнете върху OK, програмата вмъква нов работен лист в предната част на работната книга с празна мрежа за новата обобщена таблица. Той също така отваря прозореца на задачите „Полета на обобщена таблица“ от дясната страна на областта на работния лист и добавя контекстния раздел „Инструменти за обобщена таблица“ към лентата. Екранът на задачите „Полета на обобщена таблица“ е разделен на две области: списъчното поле „Избор на полета за добавяне към отчет“ с имената на всички полета в списъка с данни, които можете да изберете като източник на таблицата, предшествани от празни квадратчета за отметка, и плъзгане на полета между Области Под секцията, разделена на четири зони за изпускане (ФИЛТРИ, КОЛОНИ, РЕДОВЕ и СТОЙНОСТИ).

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

Можете също така да вмъкнете нов работен лист с празната решетка на обобщена таблица, като изберете бутона Празна обобщена таблица в диалоговия прозорец Препоръчителна обобщена таблица или палитрата с опции на инструмента за бърз анализ (показва се само когато Quick Analysis не може да предложи централни таблици за вашите данни). Само имайте предвид, че когато изберете бутона Празна обобщена таблица в този диалогов прозорец или палитра, Excel 2019 не отваря първо диалоговия прозорец Създаване на обобщена таблица. Ако трябва да използвате някоя от опциите, предлагани в този диалогов прозорец, при създаването на вашата нова обобщена таблица, трябва да създадете обобщената таблица с командния бутон на обобщена таблица, а не с командния бутон Препоръчани обобщени таблици в раздела Вмъкване.

За да завършите новата обобщена таблица на Excel, всичко, което трябва да направите, е да присвоите полетата в панела на задачите „Полета на обобщена таблица“ към различните части на таблицата. Можете да направите това, като плъзнете име на поле от списъчното поле Избор на полета за добавяне към отчет и го пуснете в една от четирите области по-долу, наречени зони за изпускане:

• ФИЛТРИ: Тази област съдържа полетата, които ви позволяват да преглеждате обобщените данни, показани в действителната централна таблица, като филтрирате набори от данни – те действат като филтри за отчета. Например, ако зададете полето Година от списък с данни като филтър за отчет, можете да покажете обобщения на данни в обобщената таблица за отделни години или за всички години, представени в списъка с данни.
• КОЛОНИ: Тази област съдържа полетата, които определят подредбата на данните, показани в колоните на обобщената таблица.
• РЕДОВЕ: Тази област съдържа полетата, които определят подредбата на данните, показани в редовете на централната таблица.
• СТОЙНОСТИ: Тази област съдържа полетата, които определят кои данни са представени в клетките на обобщената таблица — те са стойностите, които са обобщени в последната й колона (общо по подразбиране).

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

За тази обобщена таблица вие присвоявате полето Пол от списъка с данни (поле, което съдържа F (за жени) или M (за мъже), за да посочите пола на служителя в падащата зона ФИЛТРИ. Също така присвоявате полето Dept (което съдържа имената на различните отдели в компанията) в зоната за отпадане КОЛОНИ, полето Местоположение (което съдържа имената на различните градове с корпоративни офиси) в зоната за отпадане РЕДОВЕ и полето Заплата в зоната за отпадане СТОЙНОСТИ. резултат, тази въртяща таблица вече показва сумата на заплатите както за служителите мъже, така и за жени във всеки отдел (във колоните) и след това представя тези суми по тяхното корпоративно местоположение (на всеки ред).

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

• Списък с полета, за да скриете и покажете отново панела със задачи със списъка с полета на обобщената таблица от дясната страна на областта на работния лист
• +/– Бутони за скриване и повторно показване на бутоните за разширяване (+) и свиване (-) пред определени полета на колони или полета на редове, които ви позволяват временно да премахнете и след това да покажете отново техните конкретни обобщени стойности в обобщената таблица
• Заглавки на полета за скриване и повторно показване на полетата, присвоени на етикетите на колоните и етикетите на редовете в обобщената таблица