Microsoft Excel: Pivot таблици
Таблиците с резюме на Excel предоставят възможност на потребителите на едно място да групират значително количество информация, съдържаща се в тромавите таблици, както и да съставят сложни отчети. В този случай стойностите на обобщените таблици се актуализират автоматично, когато стойността на всяка свързана таблица се промени. Нека да разберем как да създадем конфигурация в Microsoft Excel.
съдържание
Създайте конфигурация по обичайния начин
Въпреки това, ще разгледаме процеса на създаване на конфигурационна таблица, използвайки примера на Microsoft Excel 2010, но този алгоритъм е приложим и за други съвременни версии на това приложение.
На база ще вземем таблицата на заплатите на работниците на предприятието. Той посочва имената на служителите, пола, категорията, датата на плащане и размера на плащането. Това означава, че всеки епизод на плащане на отделен служител съответства на отделен ред на таблицата. Ние ще групираме хаотично разположените данни в тази таблица в една обобщаваща таблица. В този случай данните ще бъдат взети само за третото тримесечие на 2016 г. Нека видим как да направите това на конкретен пример.
Първо, преобразуваме оригиналната таблица в динамична таблица. Това е необходимо, така че ако добавите редове и други данни, те ще бъдат автоматично изтеглени в обобщената таблица. За да направим това, ние ставаме курсор на всяка клетка на масата. След това в блока "Стилове" на лентата кликнете върху бутона "Форматирай като таблица". Изберете всеки стил, който ви харесва.
След това се отваря диалогов прозорец, който ни подканва да посочим координатите на местоположението на таблицата. По подразбиране обаче координатите, които програмата предлага, покриват цялата таблица. Така че можем само да се съгласим и кликнете върху бутона "OK". Но потребителите трябва да знаят, че ако искат, те могат да променят параметрите за покритие на зоната на таблицата.
След това масата се превръща в динамична и автомагистрала. Той също получава име, което, ако желае, потребителят може да се промени към каквото е удобно за него. Можете да прегледате или промените името на таблицата в раздела "Дизайнер".
За да започнете директно да създавате директна таблица, отидете в раздела "Вмъкване". Кликнете върху първия бутон в лентата, който се нарича "Pivot Table". След това се отваря меню, в което трябва да изберете какво ще създадем, таблица или диаграма. Кликнете върху бутона "Обобщена таблица".
Отваря се прозорец, в който отново трябва да изберете диапазона или името на таблицата. Както можете да видите, програмата вече е затегнала името на нашата маса, така че няма какво друго да се направи. В долната част на диалоговия прозорец можете да изберете къде да създадете обобщената таблица: на новия лист (по подразбиране) или на същия лист. Разбира се, в повечето случаи е много по-удобно да се използва обобщена таблица на отделен лист. Но това е индивидуален въпрос за всеки потребител, който зависи от неговите предпочитания и задачи. Просто кликнете върху бутона "OK".
След това формата на създаване на осова таблица се отваря в новия лист.
Както можете да видите, в дясната част на прозореца има списък с полета за таблици и под четири области:
- Имената на линиите;
- Имена на колони;
- стойност;
- Отчет за филтриране.
Просто плъзгаме с мишката полетата на масата, от която се нуждаем, в зоните, отговарящи на нашите нужди. Няма ясно правило за това кои полета да се движат, защото всичко зависи от таблицата на източника и от конкретни задачи, които могат да се променят.
Така в този конкретен случай пренасочихме полетата "Пол" и "Дата" в полето "Филтър за отчети", полето "Категория на персонала" в полето "Име на колоната", полето "Име" в полето "Линии за имена" заплатите "в областта" Стойности ". Трябва да се отбележи, че всички аритметични изчисления на данни, извлечени от друга таблица, са възможни само в последната област. Както можете да видите, по време на извършването на тези манипулации с прехвърлянето на полета в района, самата таблица в лявата част на прозореца също се промени.
Това е обобщената таблица. Над таблицата филтрите се показват по пол и дата.
Конфигуриране на обобщената таблица
Но, както си спомняме, в таблицата трябва да останат данни само за третото тримесечие. Междувременно се показват данните за целия период. За да донесете таблицата в желаната форма, кликнете върху бутона до филтъра "Дата". В показалия се прозорец проверете квадратчето срещу надписа "Изберете няколко елемента". След това премахнете отметката от всички дати, които не са подходящи за третото тримесечие. В нашия случай това е само една дата. Кликнете върху бутона "OK".
По същия начин можем да използваме филтъра на пода и да изберем за отчета например само някои мъже.
След това обобщената таблица е придобила този формуляр.
За да демонстрирате, че можете да управлявате данните в дадена таблица, както искате, ние отново отваряме формата на списъка с полета. За да направите това, отворете раздела "Параметри" и кликнете върху бутона "Списък на полетата". След това преместете полето "Дата" от полето "Филтър за отчети" до "Име на линията", а между полетата "Персонала" и "Пол" обменните области. Всички операции се извършват само чрез плъзгане на елементите.
Сега масата има съвсем различен облик. Колоните са разделени по пол, линиите са разделени на месеци и таблицата вече може да бъде филтрирана от категорията на персонала.
Ако преместите името на редовете в списъка с полета и зададете дата, по-висока от името, тогава датите за плащане ще бъдат разделени на имена на служители.
Също така можете да покажете цифровите стойности на таблицата като хистограма. За да направите това, изберете клетката с цифрова стойност в таблицата, отидете в раздела "Начало", кликнете върху бутона "Условно форматиране", отидете на "Хистограма" и изберете вида хистограма, която харесвате.
Както можете да видите, хистограмата се появява само в една клетка. За да приложим правилото за хистограма на всички клетки в таблицата, кликваме върху бутона, който се появи до хистограмата, а в отворения прозорец превключваме превключвателя на позиция "Всички клетки".
Сега нашата обобщаваща таблица е придобила представителен поглед.
Създайте обобщена таблица, като използвате съветника за обобщени таблици
Можете да създадете обобщена таблица, като приложите съветника за обобщени таблици. Но за това просто трябва да донесете този инструмент в лентата с инструменти за бърз достъп. Отидете в менюто "Файл" и кликнете върху бутона "Опции".
В прозореца за отворени параметри отидете в "Бърз достъп до лентата с инструменти". Изберете командите от командите на лентата. В списъка с елементи, потърсете съветника "Графичен и табличен график". Изберете го, кликнете върху бутона "Добави" и след това кликнете върху бутона "OK" в долния десен ъгъл на прозореца.
Както можете да видите, след действията ни се появи нова икона в лентата с инструменти за бърз достъп. Кликваме върху него.
След това се отваря съветникът за обобщена таблица. Както виждате, имаме четири варианта на източника на данни, от които ще се формира обобщената таблица:
- в списъка или в базата данни на Microsoft Excel;
- във външен източник на данни (друг файл);
- в няколко обхвати на консолидацията;
- в друга обобщаваща таблица или в обобщена таблица.
По-долу изберете какво ще създадем, обобщена таблица или диаграма. Направете избор и кликнете върху бутона "Напред".
След това се появява прозорец с диапазона на таблицата с данните, които можете да промените, ако искате, но не е нужно да правим това. Просто кликнете върху бутона "Напред".
След това съветникът за обобщена таблица Ви предлага да изберете мястото, където ще бъде поставена новата таблица на същия лист или на новата. Направете избор и кликнете върху бутона "Готово".
След това се отваря нов лист със същата форма като обичайния начин за създаване на конфигурация. Следователно, няма смисъл да се спира отделно.
Всички други действия се извършват съгласно същия алгоритъм, както е описано по-горе.
Както можете да видите, можете да създадете обобщаваща таблица в Microsoft Excel по два начина: по обичайния начин чрез бутон на лентата и като използвате съветника за обобщени таблици. Вторият метод предоставя повече допълнителни опции, но в повечето случаи функционалността на първата опция е достатъчна, за да изпълни задачите. Обобщените таблици могат да генерират данни в отчети за почти всички критерии, които потребителят ще определи в настройките.