Конферентните таблици са една от най-мощните функции на Microsoft Excel. Те позволяват да се анализират големи количества данни и да се обобщят само с няколко кликвания на мишката. В тази статия разглеждаме конфигурационните среди, разбираме какво представляват и научете как да ги създавате и персонализирате.
Забележка: Тази статия е написана с помощта на Excel 2010 (бета). Концепцията за обобщена таблица се е променила малко през годините, но методът за създаване на една се е променила в почти всяка итерация на Excel. Ако използвате версия на Excel, която не е 2010, очаквайте различни екрани от тези, които виждате в тази статия.
Малка история
В ранните дни на програмите за електронни таблици Lotus 1-2-3 управлява коридора. Нейната доминация беше толкова пълна, че хората смятаха, че това е загуба на време за Microsoft да се притеснява да разработва свой собствен софтуер за електронни таблици (Excel), за да се конкурира с Lotus. Flash-напред до 2010 г. и доминирането на Excel на пазара на електронни таблици е по-голямо от това на Lotus някога, докато броят на потребителите, които все още вървят Lotus 1-2-3, се приближава до нула. Как се случи това? Какво причини такова драматично обръщане на съдбата?
Промишлените анализатори го сведоха до два фактора: Първо, Lotus реши, че тази фантастична нова GUI платформа, наречена "Windows", е преходна мода, която никога няма да излети. Те отказаха да създадат версията на Lotus 1-2-3 на Windows (за няколко години, така или иначе), като прогнозираха, че версията им на софтуера на DOS е всичко, което някога ще има нужда. Microsoft, естествено, разработи Excel изключително за Windows. На второ място, Microsoft разработи функция за Excel, която Lotus не предоставя в 1-2-3, а именно PivotTables, Функцията "Съвместими таблици", изключително за Excel, се считаше за толкова изумително полезна, че хората са склонни да научат изцяло нов софтуер (Excel), вместо да се придържат към програма (1-2-3), която не го е имала. Тази функция, заедно с неправилното решение за успеха на Windows, беше мъртвият звън за Lotus 1-2-3 и началото на успеха на Microsoft Excel.
Разбиране на обобщените таблици
И така, какво точно е PivotTable?
Просто казано, PivotTable е обобщение на някои данни, създадени, за да позволят лесен анализ на споменатите данни. Но за разлика от ръчно създаденото резюме, Excel PivotTables са интерактивни. След като създадете такъв, можете лесно да го промените, ако не предлага точните прозрения в данните ви, които се надявахте. При няколко кликвания обобщението може да бъде "завъртяно" - завъртяно по такъв начин, че заглавията на колоните да станат заглавки на редове и обратно. Има още много неща, които могат да бъдат направени. Вместо да се опитаме да опишем всички функции на конфигурацията, ще ги покажем ...
Данните, които анализирате с помощта на обобщена таблица, не могат да бъдат просто който и да е данни - това трябва да бъде суров данни, които преди това са били необработени (неомама) - обикновено списък с някакъв вид. Пример за това може да е списъкът на продажбите в компанията за последните шест месеца.
Проучете данните, показани по-долу:
Забележете, че това е така не необработени данни. Всъщност това вече е резюме на някакъв вид. В клетка B3 можем да видим $ 30,000, което очевидно е съвкупността от продажбите на Джеймс Кук за месец януари. И така, къде са суровите данни? Как стигнахме до сумата от 30 000 долара? Къде е първоначалният списък на сделките за продажба, от които е генерирана тази цифра? Ясно е, че някъде някой трябва да е претърпял неприятностите да събере всички сделки за продажби през последните шест месеца в резюмето, което виждаме по-горе. Колко време предполагате, че това отнема? Час? Десет?
Най-вероятно, да. Виждате ли, горепосочената електронна таблица е всъщност не обобщена таблица. Той е създаден ръчно от суровите данни, съхранявани на друго място, и наистина е нужно няколко часа, за да се съберат. Въпреки това, това е точно такава обобщеност бих могъл да се създаде чрез конфигурационни таблици, в който случай щеше да отнеме само няколко секунди. Нека да разберем как ...
Ако трябваше да проследим първоначалния списък с продажбите, може да изглежда така:
Може да се изненадате да научите, че с помощта на функцията за обобщена таблица на Excel можем да създадем месечно обобщение на продажбите, подобно на горното, след няколко секунди, само с няколко кликвания на мишката. Можем да направим това - и много повече!
Как да създадете обобщена таблица
Първо, уверете се, че имате някои необработени данни в работен лист в Excel. Списъкът на финансовите транзакции е типичен, но може да бъде списък с почти всичко: Данни за връзка с служителя, колекция от компактдискове или данни за разхода на гориво за автомобилния Ви флот.
Затова стартираме Excel ... и зареждаме такъв списък ...
След като списъкът е отворен в Excel, ние сме готови да започнем да създаваме PivotTable.
Кликнете върху която и да е отделна клетка в списъка:
След това, от Insert , кликнете върху обобщена таблица икона:
Най- Създаване на обобщена таблица се появява, като ви задават два въпроса: Какви данни трябва да се базира на новата ви конфигурация и къде трябва да бъде създадена? Тъй като вече кликнахме върху клетка в списъка (в стъпката по-горе), целият списък около тази клетка вече е избран за нас ($ A $ 1: $ G $ 88 на Плащания лист, в този пример). Имайте предвид, че можем да изберем списък в който и да е друг регион на всеки друг работен лист или дори външен източник на данни, като таблица на базата данни на Access или дори таблица на база данни на MS-SQL Server. Също така трябва да изберем дали искаме нашата нова конфигурация да бъде създадена на a нов работен лист или на съществуващ един. В този пример ще изберем a нов едно:
Новият работен лист е създаден за нас и на този работен лист е създаден празен контекст:
Появява се и друго поле: Списък на полетата за обобщена таблица, Този списък с полета ще се показва всеки път, когато щракнете върху някоя клетка в контекста (по-горе):
Списъкът с полетата в горната част на кутията всъщност е събирането на заглавията на колоните от първоначалния работен лист на необработените данни. Четирите празни полета в долната част на екрана ни позволяват да избираме начина, по който бихме искали нашата обобщена таблица да обобщи суровите данни. Досега няма нищо в тези полета, така че "Пасивната таблица" е празна. Всичко, което трябва да направите, е да преместите полетата от списъка по-горе и да ги пуснете в долните полета. След това автоматично се създава обобщена таблица, която отговаря на нашите указания. Ако се объркаме, трябва само да преместим полетата обратно, откъде идват и / или да се влачат нов за да ги замени.
Най- Стойности кутията е може би най-важната от четирите. Полето, което е преместено в това поле, представлява данните, които трябва да бъдат обобщени по някакъв начин (чрез сумиране, осредняване, намиране на максимален, минимален и т.н.). Това е почти винаги числен данни. Перфектният кандидат за тази клетка в нашите примерни данни е полето "Количество". Нека проследим това поле в Стойности кутия:
Забележете, че (а) полето "Сума" в списъка с полета вече е отметнато и "Сума на сумата" е добавена към Стойности , което показва, че сумата на колоната е сумирана.
Ако разгледаме самата тактова таблица, действително ще намерим сумата от всички стойности "Сума" от работния лист за необработени данни:
Създадохме първата си конфигурация! Полезен, но не особено впечатляващ. Вероятно ни е нужно малко повече вникване в нашите данни от това.
Позовавайки се на нашите примерни данни, трябва да идентифицираме една или повече заглавия на колони, които евентуално бихме могли да използваме за разделяне на тази сума. Например, може да решим, че бихме искали да видим обобщена информация за нашите данни, където имаме заглавието на реда за всеки от различните продавачи в нашата компания и общо за всеки. За да постигнете това, трябва само да преместите полето "Продавач" в полето Редове етикети кутия:
Сеганакрая, нещата започват да стават интересни! Нашият пулт започва да се оформя ....
С няколко кликвания създадохме таблица, която щеше да отнеме много време, за да се направи ръчно.
И какво още можем да направим? Е, в известен смисъл нашата обобщена таблица е пълна. Създадохме полезно обобщение на изходните ни данни. Важното е вече научено! За останалата част от статията ще разгледаме някои начини, по които може да бъде създадена по-сложна конфигурация, както и начините, по които тези обобщени таблици могат да бъдат персонализирани.
Първо, можем да създадем дву-таблица с размери. Нека да го направим като използвате "Метод на плащане" като заглавие на графа. Просто изтеглете заглавието "Метод на плащане" в Етикетите на колоните кутия:
Което изглежда така:
Започва да стига много готино!
Нека да го направим три-измерна таблица. Какво би могло да изглежда такава маса? Е, да видим ...
Преместете "Колона" / заглавието " Отчет за филтъра кутия:
Забележете къде свършва ....
Това ни позволява да филтрираме нашия отчет въз основа на който се купува "ваканционен пакет". Например можем да видим разбивката на продавача срещу начина на плащане за всичко пакети или с няколко кликвания го смените, за да покажете същата разбивка за пакета "Sunseekers":
И така, ако мислите за него по правилния начин, нашият обем е вече триизмерен. Нека да продължим да персонализираме ...
Ако се окаже, да речем, че искаме само да видим чек и кредитна карта транзакции (т.е. никакви парични транзакции), тогава можем да отменим елемента "Парични средства" от заглавията на колоните. Кликнете върху стрелката за падащо меню до Етикетите на колоните, и untick "Cash":
Да видим как изглежда ... Както виждате, "Кеш" е изчезнало.
форматиране
Това очевидно е много мощна система, но досега резултатите изглеждат много ясни и скучни. За начало числата, които сумираме, не приличат на суми в долари - просто стари номера. Нека да поправим това.
Едно изкушение може да е да направим това, което сме свикнали да правим при такива обстоятелства, и просто да изберете цялата таблица (или целия работен лист) и да използвате стандартните бутони за форматиране на номера от лентата с инструменти, за да завършите форматирането. Проблемът с този подход е, че ако в бъдеще промените структурата на общата таблица (което е 99% вероятно), тогава тези формати на числа ще бъдат загубени. Нуждаем се от начин, който да ги направи (полу-) постоянни.
Първо откриваме записа "сума на сумата" в Стойности и кликнете върху него. Появява се меню. Избираме Настройки на поле за стойност ... от менюто:
Най- Настройки на поле за стойност полето се появява.
Кликнете върху Формат на числата бутон и стандарта Форматиране клетка клетка появява се:
От категория списък, изберете (да речем) Счетоводство, и намалете броя на десетичните числа на 0. Кликнете върху Добре няколко пъти, за да се върнете към общата таблица ...
Както можете да видите, номерата са правилно форматирани като суми в долари.
Докато се занимаваме с форматирането, нека форматираме цялото обобщено табло. Има няколко начина да направите това. Да използваме проста ...
Кликнете върху Концентрирани инструменти / дизайн раздел:
След това отпуснете стрелката в долната дясна част на Стилове на обобщена таблица за да видите огромна колекция от вградени стилове:
Изберете някой, който обжалва, и погледнете резултата в обобщената таблица:
Други възможности
Можем да работим и с дати. Сега обикновено има много, много дати в списък с транзакции, като този, с който започнахме. Но Excel предоставя възможност за групиране на елементите от данни заедно по ден, седмица, месец, година и т.н. Нека видим как се прави това.
Първо, премахнете колоната "Метод на плащане" от Етикетите на колоните (просто го изтеглете обратно в списъка с полета) и го заменете с графата "Дата на резервиране":
Както можете да видите, това прави нашата конфигурация незабавно безполезна, като ни дава една колона за всяка дата, на която е извършена сделка - много широка маса!
За да коригирате това, щракнете с десния бутон върху която и да е дата и изберете Група ... от контекстното меню:
Появява се полето за групиране. Избираме Месеци и кликнете върху OK:
Voila! А много по-полезна таблица:
(Между другото, тази таблица е почти идентична с тази, показана в началото на тази статия - оригиналното резюме на продажбите, което е създадено ръчно.)
Друго готино нещо е да знаете, че можете да имате повече от един набор от заглавия на редове (или заглавия на колони):
... което изглежда така ....
Можете да направите подобно нещо с заглавията на колоните (или дори с отчетните филтри).
Запазвайки отново нещата, нека видим как да парцел осреднена стойности, а не сумирани стойности.
Първо кликнете върху "Сума на сумата" и изберете Настройки на поле за стойност ... от контекстното меню, което се показва:
В Обобщете полето за стойност по списък в Настройки на поле за стойност , изберете Средно аритметично:
Докато сме тук, да променим Персонализирано име, от "Средна сума" до нещо малко по-кратко. Въведете нещо като "Ср.":
Кликнете Добре, и виж как изглежда. Забележете, че всички стойности се променят от обобщените суми към средните стойности и заглавието на таблицата (горна лява клетка) е променено на "Ср.":
Ако ни харесате, можем да имаме дори суми, средни стойности и бройки (броевете = колко продажби имаше), всички на един и същ отчет!
Ето стъпките, за да получите нещо подобно на мястото си (като се започне от празен контекст):
поле от Етикетите на колоните да се Редове етикетиЕто какво стигаме:
Общо, средно и на едно и също обобщено табло!
заключение
Има много, много повече функции и опции за Сгъваеми таблици, създадени от Microsoft Excel - твърде много, за да се покаже в статия като тази. За да покриете напълно потенциала на обобщените таблици, ще е необходима малка книга (или голям уебсайт). Смелодените и / или развълнувани читатели могат да разгледат още много спокойно таблиците: Просто кликнете с десния бутон на мишката върху почти всичко и вижте какви опции са достъпни за вас. Съществуват и двата ленти: Инструменти / Опции за PivotTable и Дизайн, Няма значение дали правите грешка - лесно е да изтриете контакта и да започнете отново - възможността за старите потребители на DOS на Lotus 1-2-3 никога не е имала.
Ако работите в Office 2007, може да поискате да разгледате статията ни за това как да създадете обобщена таблица в Excel 2007.
Включихме работна книга на Excel, която можете да изтеглите, за да практикувате уменията си за обобщени таблици. Тя трябва да работи с всички версии на Excel от 97 нататък.
Изтеглете нашата работна книга
