Последователно създаване на Excel, работа с данни, премахване

Excel е доста мощен инструмент, който ви позволява да работите с големи масиви за данни, извършвайки редица операции с тях. Може да се твърди, че таблиците на Excel са релационни бази данни, с които можете да работите, без да притежавате уменията на програмист. Но ако програмата се използва професионално, основните функции не са достатъчни за генериране на отчети за различни структури. За тези цели табличните процесори имат по -функционален и мощен инструмент - обобщени таблици.

Защо Excel се нуждае от обобщени таблици

В оригинала този термин звучи като въртяща се таблица, а преводът на фразата е донякъде мъглив: „Таблица, която може да бъде променена, усукана, демонстрирана в различни проекции“. Какво означава това? Тълкуване на табличните данни - Задачата не е лесна. Това обикновено се прави с помощта на формули, получаване на крайните резултати в края на таблицата.

Ако трябва да създадете сложни отчети, използвайки филтри и промяна на изходните данни в правилната последователност, направете това със стандартни средства на Excel, ако е възможно, е много трудно. Но благодарение на консолидираните таблици, подобни доклади са много по -лесни за създаване, като различни диаграми, позволяващи само с помощта на мишка към групови линии и колони, да ги сменя на места, използвайте различни контроли.

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

Да предположим, че сте мениджър на компанията и сте отговорни за поддържането на документацията за продажбите. Всички транзакции се въвеждат в таблицата, указващи всички подробности (продавач, купувач, количество, сума, дата на продажба, адрес на купувача и т.н. Д.). Лидерът иска доклад за продажбите по регион.

Как се справяме? Създаваме оформление на нова таблица, в капачката на която посочваме цялата гама от продадени стоки и в линиите - имената на регионите. За да направим това, ние копираме от колоната на източника на колона със стоки, изтривайки дубликати. Използвайки режима на специална вложка, ние прехвърляме тази колона в шапка на консолидираната страница. Ние идваме по същия начин с регионите, но тъй като те са разположени в консолидираната таблица вертикално, ние просто копираме без транспониране.

Във всяка клетка обобщаваме приходите, използвайки функцията на Summail. Накрая добавете крайните резултати на дъното и изпратете отчета на мениджъра. Лидерът моли регионите да се уговорят в горната част, а стоките - по линиите. Вие правите нов отчет за 5 минути и получавате нова задача: предоставете данни за печалбата, а не приходите, тоест, като се вземат предвид разходите. Е, ние променяме формулата малко и отново резултатът е готов за минути. И накрая, има искане за подготовка на такива отчети за всеки продавач, за да се оцени тяхната ефективност. И това не е проблем за обобщена таблица, ако знаете къде се намират необходимите първоначални данни!

Междувременно всеки потребител на Excel знае колко е трудно да се извършват такива манипулации в рамките на изходната таблица.

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

Да предположим, че имаме изходната таблица на продажбите:

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

Вторият, основен начин за създаване на консолидирани таблици е „от нулата“, за която натискаме първия бутон.

Отваря се нов прозорец, в който е необходимо да се задават два параметъра: началният обхват на клетката и местоположението на ST. Тъй. Така че ние не променяме нищо и щракнете върху OK. В резултат на това ще се отвори нов лист с непълно оформление.

За да го конфигурирате, използвайте десния панел, озаглавен „Поле на обобщената таблица“. Тук в горната единица е посочен списък с налични полета, съответстващи на колоната на изходната таблица. Можете да добавите полета по два начина: като поставите отметка срещу желаната стойност и след това тя автоматично ще влезе в оформлението. Но далеч от винаги до мястото, от което се нуждаете. В такива случаи просто трябва да плъзнете полето до необходимото положение на оформлението. Премахването на позиция в оформлението се извършва по подобен начин, премахване на отметката или плъзгане на десния панел.

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

• Областта на стойностите е основната част на ST, тъй като именно тук е посочено как да обработвате колони и линии. Обикновено това е сумиране - този метод на агрегиране ще бъде зададен по подразбиране, при условие че всички клетки от диапазона приемат числени стойности. В присъствието на поне една празна клетка или с данни във формат, който е различен от числения, вместо сумата от стойностите на клетките, техният брой ще бъде изчислен, така че този факт трябва да бъде взет предвид. За нашия пример ще се извърши сумиране за всеки продукт в съответния регион. В допълнение към сумирането, можете да зададете всеки друг метод на изчисления, които имат около две дузини (изчисляване на средната стойност, дял и т.н.). Най -лесният начин да направите това е директно в обобщената таблица, като щракнете върху PKM за всяка клетка и изберете необходимия метод на изчисления;
• Площта на линиите - се състои от уникални стойности на клетките, разположени в лявата колона. Ако вмъкнете няколко полета тук, обобщената таблица вече няма да е плоска, а многостепенна;
• Областта на колоната също съдържа стойности на полето, но вече спрямо горната линия на стартовата таблица;
• Площта на филтъра ви позволява да налагате ограничения върху условията на изчисляване на стойностите на клетките. Обикновено тук се поставя поле, според което проба, зададена в три други области, трябва да бъде филтрирана.

Благодарение на наличието на тези области можете да конфигурирате проба от стартовата таблица с почти всяка сложност, изразходвайки няколко минути.

За да стане по -ясно, обясняваме горното, използвайки пример, описан в предишния раздел.

И така, Step -By -Step Инструкции за това как да направите консолидирана таблица в Excel:

• От списъка с полета в обобщената таблица влачим полето „приходи“ в областта на стойностите;
• Полето "регион" (регионът на купувача) се влачи в линиите;
• Полето "продукт", поставено в областта на колоната.

Само три действия - и обобщена таблица, подходяща за изпращане на властите, е готова! Отне ни около 10 секунди, за да го компилираме.

Как да изпълним другите желания на режисьора?

За да спечелите печалба вместо приходи, е достатъчно да се постави съответното поле в областта на стойностите вместо приходите, съответните изчисления в резюмето ще бъдат направени автоматично.

Втората задача се изпълнява и за секунди - за да промените регионите и стоките, просто ги плъзнете от една област в друга (сменете местата).

За да получите резултатите за всеки мениджър, е достатъчно да поставите полето „Manager“ в областта на филтъра, след това в горната част ще се появи селектор, в който трябва да посочите желаното фамилно име, за да получите отчет. Има възможност за комбиниране на доклад за няколко продавачи.

Разбира се, ние не сме донесли най -трудните, а много индикативен пример, който ви позволява да разберете как да създавате обобщени таблици в Excel. Методът за изчисляване на стойностите в клетките може да бъде по -сложен, може да се наложи да използвате изчислените полета, както и да приложите условно форматиране - възможностите в това отношение в Excel са впечатляващи.

За източника на данни

Вече споменахме, че определени изисквания са наложени на първоначалните данни, използвани за правилно създаване на консолидирани таблици в програмата Excel. Нека ги изброим:

• Основното ограничение е свързано със задължителното присъствие на имена над колони, участващи в изчисленията. Такива идентификатори са необходими за формирането на получените отчети - при добавяне на нови записи (низове) към стартовата таблица, форматът не е необходим за промяна на формата ST и резултатите ще бъдат преизчистени автоматично;
• Уверете се, че в клетките на линиите и колоните, участващи в пробата, се въвеждат числени параметри. Ако са празни или съдържат текстови стойности, тези редове ще изпаднат от изчисленията, което ще изкриви резултатите от изчисленията;
• Следвайте съответствието на формати на линиите и съдържанието на клетките. Ако тя е дефинирана като дата, всички стойности в колоната трябва да имат един и същ формат, в противен случай филтрирането и грешките ще грешат.

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

Актуализиране на данните в таблицата на Excel Set

Въпреки че говорихме за автоматичното преизчисляване на консолидираните таблици, когато правим корекции на изходните данни или на структурата на самата СТ, всъщност поради причини за запазване на компютърните ресурси, трябва да извършите определени действия.

Можете да актуализирате данните в нашата таблица за експозиция Excel по два начина:

• Щракване с правилния бутон на мишката на всяка клетка и избор на елемента „Актуализиране“ в контекстното меню;
• Използване на раздела "Данни" и бутона "Актуализиране на всичко" ".

Кеширането на таблици е особено оправдано, ако те са с големи размери, в този случай скоростта на програмата ще се увеличи значително, особено ако редовно влизате в нови линии и с висока честота.

Добавяне на колона или линия към Excel

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

Често обобщената таблица няма съвсем удобни видове за визуализация. Например, когато в редовете има няколко полета. Тогава единият от тях се показва изцяло, а стойностите на другия трябва да бъдат посочени в горната част на таблицата. Ако има малко такива стойности (да речем, в нашия пример само за региони на продажбите само 6), тогава има смисъл да добавяте колона „региони“ на въртящата се таблица към съответната област, прехвърляйки я от района на линии.

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

Формиране на обобщена таблица от няколко файла (таблици) Excel

Това е по -сложна, но доста пълна задача. Използвайте Master St, който трябва да се добави към панела за бърз достъп.

И така, отляво, близо до елемента „Файл“, щракнете върху Насочената стрелка надолу, изберете елемента „Други команди“. Ще се отвори нов прозорец, в средния панел щракнем върху опциите „Всички екипи“. Намираме в списъка на „Master of Consolidated Tables“ в списъка, който се отваря, изберете и щракнете върху бутона „Добавяне“. Съответната икона ще се появи в горния панел.

Стартираме майстора. Да предположим, че трябва да комбинираме данни от два листа, май и през юни. Основното условие е, че те трябва да съвпадат със структурата. Ако е така, тогава в първия прозорец на главния избираме опцията „Таблица на вещества“ и следващия параметър - „Няколко диапазона на консолидация“.

На втората стъпка посочваме на Учителя, че се нуждаем от няколко полета. На третия - изберете обхвата на клетките за сумиране на първия лист, щракнете върху „Добавяне“ и направете същото за втория лист Excel. Сега трябва да дадете имената на две полета. Първо правим това за първото поле (щракнете върху селектора "1", в колоната "1 поле" ние въвеждаме "май"), след това, по аналогия, за втория. В този случай ще е необходимо да се посочи в списъка на диапазоните съответната линия, първата или втората.

След като щракнете върху бутона „Готово“, се отваря нов прозорец, в който посочваме „Новото листо“ и отново натиснете бутона „Готово“.

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

Как да премахнем изкуството

Най -лесният случай - когато сте създали консолидирана маса, изпратихте резултатите на готвача и вече нямате нужда. Ако сте сигурни в това, просто изберете таблицата и щракнете върху клавиша „Изтриване“. Просто и ефективно.

Но изведнъж може да е необходима структурата на таблицата? Excel има възможност да изтрие само резултатите или данните от клетките. Помислете как се прави това.

За да изтрием резултатите от изчислението, изпълняваме следните стъпки:

• Ние маркираме всяка клетка от ST;
• Изберете раздела "Анализ" на горния панел;
• От списъка на наличните действия изберете „Clear“;
• Кликнете върху точката „Изчистете всичко“.

Но какво да направите, ако искате да запазите резултатите, но сами не се нуждаете от данни, тоест искате да освободите таблицата? Тази ситуация често възниква, ако ръководството се нуждае само от резултати. Алгоритъмът на действията:

• Отново изберете всяка клетка, щракнете върху раздела "Анализ";
• Изберете елемента от менюто "Действие", щракнете върху "Изберете", маркирайте цялата консолидирана таблица с мишката;
• Щракнете върху PCM вътре в избраната зона;
• От контекстното меню изберете елемента "Копиране";
• Отиваме в раздела "Начало", отново щракнете върху PKM и изберете "Вмъкване";
• Изберете раздела "Вмъкване на стойност", в него отбелязваме параметъра "вмъкване като стойност".

В резултат на това обобщената таблица ще бъде изтрита, като същевременно се поддържа резултатите.

Съвет. Можете да ускорите процедурата, като използвате комбинацията от ключове. За да изберете таблицата, използвайте Ctrl + A, за копиране - Ctrl + C. След това натиснете Alt + E, Alt + S, Alt + V и завършете процедурата, като натиснете Enter.

За да премахнете консолидираните таблици в Excel 2007/2010, трябва да използвате друг алгоритъм:

• Изберете St, предназначен за премахване;
• Щракнете върху раздела "Параметри";
• В „Групата от действия“ изберете „чистия“ елемент и след това - „Почистете всичко“.

Ако шефът ви обича визуализацията на данните, очевидно е, че ще трябва да използвате консолидирани диаграми. Тъй като заемат много място в таблицата, след употреба обикновено се отстраняват.

В старите версии на програмата за това трябва да изберете диаграма, щракнете върху раздела "Анализ", изберете група данни и щракнете върху последователно "почистете" и "почистете всичко".

Освен това, ако диаграмата е свързана с най -обобщената таблица, след неговото изтриване ще загубите всички настройки на таблицата, неговите полета и форматиране.

За версии, по -стари от Excel 2010, трябва да изберете диаграма, да изберете „Действия“ в раздела „Анализ“ и щракнете върху „Чисти“ и „Почистете всичко“. Резултатът ще бъде подобен.

Надяваме се, че нашите уроци по консолидирани таблици ще ви позволят да откриете тази достатъчно мощна функционалност. Ако все още имате въпроси, попитайте ги в коментарите, ще се опитаме да им отговорим.